Useful Oracle E-Business Suite Scripts Part - 2

Various scripts used with Concurrent Manager/Requests
a.     To list all running programs
SELECT FCR.REQUEST_ID REQUEST_ID
       ,FCPT.USER_CONCURRENT_PROGRAM_NAME REQUEST_NAME
       ,FCR.ACTUAL_START_DATE START_DATE
       ,DECODE(FCR.PHASE_CODE, 'C', 'Completed',
                               'I', 'Inactive',
                               'P', 'Pending',
                               'R', 'Running') PHASE
       ,DECODE(FCR.STATUS_CODE, 'A', 'Waiting',
                               'B', 'Resuming',
                               'C', 'Normal',
                               'D', 'Cancelled',
                               'E', 'Error',
                               'F', 'Scheduled',
                               'G', 'Warning',
                               'H', 'On Hold',
                               'I', 'Normal',
                               'M', 'No Manager',
                               'Q', 'Standby',
                               'R', 'Normal',
                               'S', 'Suspended',
                               'T', 'Terminating',
                               'U', 'Disabled',
                               'W', 'Paused',
                               'X', 'Terminated',
                               'Z', 'Waiting') STATUS
      ,FU.USER_NAME REQUESTED_BY
FROM  FND_CONCURRENT_PROGRAMS FCP,
      FND_CONCURRENT_PROGRAMS_TL FCPT,
      FND_CONCURRENT_REQUESTS FCR,
      FND_USER FU
WHERE    --TRUNC(((FCR.ACTUAL_COMPLETION_DATE-FCR.ACTUAL_START_DATE)/(1/24))*60) > NVL('&MIN',45)
       FCR.CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND    FCR.PROGRAM_APPLICATION_ID = FCP.APPLICATION_ID
AND    FCR.CONCURRENT_PROGRAM_ID = FCPT.CONCURRENT_PROGRAM_ID
AND    FCR.PROGRAM_APPLICATION_ID = FCPT.APPLICATION_ID
AND    FU.USER_ID = FCR.REQUESTED_BY
AND    FCPT.LANGUAGE = USERENV('Lang')
AND    FCR.PHASE_CODE = 'R'
ORDER BY FCR.ACTUAL_START_DATE DESC

b. To list all pending programs
SELECT FCR.REQUEST_ID REQUEST_ID
       ,FCPT.USER_CONCURRENT_PROGRAM_NAME REQUEST_NAME
       ,FCR.ACTUAL_START_DATE START_DATE
       ,DECODE(FCR.PHASE_CODE, 'C', 'Completed',
                               'I', 'Inactive',
                               'P', 'Pending',
                               'R', 'Running') PHASE
       ,DECODE(FCR.STATUS_CODE, 'A', 'Waiting',
                               'B', 'Resuming',
                               'C', 'Normal',
                               'D', 'Cancelled',
                               'E', 'Error',
                               'F', 'Scheduled',
                               'G', 'Warning',
                               'H', 'On Hold',
                               'I', 'Normal',
                               'M', 'No Manager',
                               'Q', 'Standby',
                               'R', 'Normal',
                               'S', 'Suspended',
                               'T', 'Terminating',
                               'U', 'Disabled',
                               'W', 'Paused',
                               'X', 'Terminated',
                               'Z', 'Waiting') STATUS
      ,FU.USER_NAME REQUESTED_BY
FROM  FND_CONCURRENT_PROGRAMS FCP,
      FND_CONCURRENT_PROGRAMS_TL FCPT,
      FND_CONCURRENT_REQUESTS FCR,
      FND_USER FU
WHERE    --TRUNC(((FCR.ACTUAL_COMPLETION_DATE-FCR.ACTUAL_START_DATE)/(1/24))*60) > NVL('&MIN',45)
       FCR.CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND    FCR.PROGRAM_APPLICATION_ID = FCP.APPLICATION_ID
AND    FCR.CONCURRENT_PROGRAM_ID = FCPT.CONCURRENT_PROGRAM_ID
AND    FCR.PROGRAM_APPLICATION_ID = FCPT.APPLICATION_ID
AND    FU.USER_ID = FCR.REQUESTED_BY
AND    FCPT.LANGUAGE = USERENV('Lang')
AND    FCR.PHASE_CODE = 'P'
ORDER BY FCR.ACTUAL_START_DATE DESC

c.  To list all programs that are scheduled and yet to run
SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
) schedule_type,
DECODE (NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
),
'PERIODICALLY', 'EVERY '
|| cr.resubmit_interval
|| ' '
|| cr.resubmit_interval_unit_code
|| ' FROM '
|| cr.resubmit_interval_type_code
|| ' OF PREV RUN',
'ONCE', 'AT :'
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcr.class_info
) schedule,
fu.user_name, requested_start_date, cr.PHASE_CODE, cr.STATUS_CODE
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
--AND trunc(cr.requested_start_date) >= trunc(SYSDATE)
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id
AND cr.release_class_id is not null
order by name, requested_start_date;

d.    To list avg / min / max Execution time / Wait time and number of executions for a given program for a given date range

select c.CONCURRENT_PROGRAM_ID,
       q.concurrent_queue_name qname,
       c.concurrent_program_name||' - '||
       ctl.user_concurrent_program_name "Program"
      ,to_char(min(actual_start_date),'mm/dd/rr') earlieststart
      ,sum(nvl(actual_completion_date-actual_start_date,0))*1440 "Time(tot)"
      ,avg(nvl(actual_completion_date-actual_start_date,0))*1440 "Time(avg)"
      ,min(nvl(actual_completion_date-actual_start_date,0))*1440 "Time(min)"
      ,max(nvl(actual_completion_date-actual_start_date,0))*1440 "Time(max)"
      ,count(*) "Jobs"
      ,ls.meaning lsm
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_programs c,
      APPLSYS.fnd_concurrent_programs_tl ctl,
      APPLSYS.fnd_lookup_values ls,
      APPLSYS.fnd_concurrent_processes b,
      applsys.fnd_concurrent_queues q    
where c.concurrent_program_id = &cmprogid
  and a.concurrent_program_id = c.concurrent_program_id
  and a.controlling_manager = b.concurrent_process_id
  and b.queue_application_id = q.application_id
  and b.concurrent_queue_id = q.concurrent_queue_id
  and ctl.concurrent_program_id = c.concurrent_program_id
  and ctl.language = 'US'
  and a.program_application_id = c.application_id
  and ctl.application_id = c.application_id
  and ls.lookup_type = 'CP_STATUS_CODE'
  and ls.language = 'US'
  and ls.enabled_flag = 'Y'
  and (ls.start_date_active <= sysdate and ls.start_date_active is not null)
  and (ls.end_date_active > sysdate or ls.end_date_active is null)
  and a.status_code || '' = ls.lookup_code
  and a.phase_code || '' = 'C'
group by c.CONCURRENT_PROGRAM_ID ,q.concurrent_queue_name, c.concurrent_program_name
        ,ctl.user_concurrent_program_name
        ,ls.meaning
order by 4 desc

e.   To get the input the Concurrent request number and if it is running then give the Oracle Sid, serial# , unix process id, along with program or module it is linked with
SELECT d.sid, d.serial# ,d.process , c.SPID, fcpv.user_concurrent_program_name, a.REQUEST_ID, fa.APPLICATION_NAME,
d.osuser, replace(d.machine,'HEADLANDS\',null) user_srvr, a.REQUESTED_START_DATE
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d,
fnd_concurrent_programs_vl fcpv,
fnd_application_tl  fa
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R'
AND fcpv.concurrent_program_id = a.CONCURRENT_PROGRAM_ID
AND a.RESPONSIBILITY_APPLICATION_ID = fa.APPLICATION_ID

f.     List all programs that can be run by a given userSELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name,
SUBSTR (r.responsibility_name, 1, 60) responsiblity,
SUBSTR (a.application_name, 1, 50) application,
FCPT.USER_CONCURRENT_PROGRAM_NAME
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r,
FND_CONCURRENT_PROGRAMS FCP,
FND_CONCURRENT_PROGRAMS_TL FCPT
WHERE g.user_id(+) = u.user_id
AND u.USER_NAME = '&USER_NAME'
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
AND FCP.APPLICATION_ID = A.APPLICATION_ID
AND FCP.APPLICATION_ID = FCPT.APPLICATION_ID
ORDER BY responsiblity, application, FCPT.USER_CONCURRENT_PROGRAM_NAME

g.     List all programs that are defined as incompatible for a given programSELECT FAL.APPLICATION_NAME INCOMPATIBLE_APPLICATION , FCPT.USER_CONCURRENT_PROGRAM_NAME INCOMPATIBLE_PROGRAM
FROM FND_CONCURRENT_PROGRAM_SERIAL FCPS,
FND_CONCURRENT_PROGRAMS FCP,
FND_APPLICATION_TL FAL,
FND_CONCURRENT_PROGRAMS_TL FCPT
WHERE FCPS.TO_RUN_APPLICATION_ID = FCP.APPLICATION_ID
AND FCPS.TO_RUN_CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND FCPS.RUNNING_CONCURRENT_PROGRAM_ID IN (SELECT FCPT.CONCURRENT_PROGRAM_ID  FROM FND_CONCURRENT_PROGRAMS_TL FCPT
WHERE FCPT.USER_CONCURRENT_PROGRAM_NAME = :USER_CONCURRENT_PROGRAM_NAME)
AND FAL.APPLICATION_ID = FCPS.TO_RUN_APPLICATION_ID
AND FCPT.APPLICATION_ID = FCPS.TO_RUN_APPLICATION_ID
AND FCPT.CONCURRENT_PROGRAM_ID = FCPS.TO_RUN_CONCURRENT_PROGRAM_ID

h.      Given a program name in which manager it will runselect  ptl.user_concurrent_program_name,qtl.user_concurrent_queue_name,t.request_id
  from Fnd_Concurrent_Requests t,
       FND_CONCURRENT_PROCESSES k,
       Fnd_Concurrent_Queues_TL QTL,
       Fnd_Concurrent_Programs_TL PTL
  where k.concurrent_process_id = t.controlling_manager
    and QTL.Concurrent_Queue_Id = k.concurrent_queue_id
    and ptl.concurrent_program_id=t.concurrent_program_id
    and qtl.language='US'
    and PTL.USER_CONCURRENT_PROGRAM_NAME = :CONCURRENT_PROGRAM_NAME
ORDER BY ptl.user_concurrent_program_name DESC



To Identify the manager for a given concurrent program The script given below identifies which manager is going to execute a given Concurrent program:

SELECT user_concurrent_program_name, user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl fcp, apps.fnd_concurrent_queue_content fcqc, apps.fnd_concurrent_queues_tl cq
WHERE
fcqc.type_application_id(+) = fcp.application_id AND
fcqc.type_id(+) = fcp.concurrent_program_id AND
fcqc.type_code(+) = 'P' AND
fcqc.include_flag(+) = 'I' AND
fcp.LANGUAGE = 'US' AND
fcp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME' AND
NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id AND
NVL (cqc.queue_application_id, 0) = cq.application_id AND
cq.LANGUAGE = 'US'

To identify long running concurrent requests for more than 1 hour.


set line 200
set pagesize 500
col PROGRAM_NAME format a30
col concreq format a8
col Username format a10
col opid format a4
col dbuser format a6
SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,
           fcp.USER_CONCURRENT_PROGRAM_NAME "Program_Name",
                fu.user_name "Username",
               round((sysdate - actual_start_date) * 24 ,2) "Running_Hrs",
           SUBSTR(proc.os_process_id,1,15) clproc,
           SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
           SUBSTR(vsess.username,1,10) dbuser,
           SUBSTR(vproc.spid,1,10) svrproc,
           vsess.sid sid,
           vsess.serial# serial#
    FROM   fnd_concurrent_requests req,
           fnd_concurrent_processes proc,
           fnd_lookups look,
           fnd_lookups look1,
           V\$process vproc,
           V\$session vsess,
           fnd_concurrent_programs_vl fcp,
        fnd_user fu
    WHERE  req.controlling_manager = proc.concurrent_process_id(+)
    AND    req.status_code = look.lookup_code
    AND    look.lookup_type = 'CP_STATUS_CODE'
    AND    req.phase_code = look1.lookup_code
    AND    look1.lookup_type = 'CP_PHASE_CODE'
    AND    look1.meaning = 'Running'
    and    req.CONCURRENT_PROGRAM_ID = fcp.CONCURRENT_PROGRAM_ID
    AND    proc.oracle_process_id = vproc.pid(+)
    AND    vproc.addr = vsess.paddr(+)
    AND    fu.user_id = req.requested_by
    AND    round((sysdate - actual_start_date) * 24) > 1;
 

Useful Oracle E-Business Suite Scripts Part - 1

Below are the various Oracle R12 E-Business Suite scripts that can be handy during support,upgrade,etc.,

Query for Scheduled reqeuts start on prior run less than 5 mins
select cs.request_id,cs.concurrent_program_id,cs.program,cs.argument_text,cr.resubmit_interval_type_code,cs.requestor,resubmit_interval,resubmit_interval_unit_code from fnd_concurrent_requests cr,fnd_conc_req_summary_v cs where
 cs.phase_code='P'
and cs.status_code in ('I','Q')
and
resubmit_interval_type_code='START' and cr.request_id=cs.request_id
and resubmit_interval_unit_code='MINUTES' and resubmit_interval<5

Script to disable all scheduled requests - After cloning
update fnd_concurrent_requests
set phase_code='C',
status_code='D'
where phase_code = 'P'
and (status_code = 'I' OR status_code = 'Q');
and requested_start_date >= SYSDATE
and hold_flag = 'N';

Number of times Purge Concurrent Request execution was run in last 30 days
set pages 1000
set line 132
set head on
select  r.requested_start_date,r.actual_start_date,r.phase_code,r.status_code,r.actual_completion_date from
apps.fnd_concurrent_programs_vl p, apps.fnd_concurrent_requests r
where p.concurrent_program_id = r.concurrent_program_id
and p.application_id = r.program_application_id
and p.user_concurrent_program_name like 'Purge Concurrent Request and/or Manager Data'
and  r.actual_start_date >= sysdate-30 order by r.requested_start_date;

Number of times gather schema stats was run in last 30 days
set pages 1000
set line 132
set head on
select  r.request_id,r.actual_start_date,r.phase_code,r.status_code,r.actual_completion_date,argument_text from
apps.fnd_concurrent_programs_vl p, apps.fnd_concurrent_requests r
where p.concurrent_program_id = r.concurrent_program_id
and p.application_id = r.program_application_id
and p.user_concurrent_program_name like '%Gather Schema Statistics%'
and  r.actual_start_date >= sysdate-30 order by r.requested_start_date;

Concurrent Requests Average/Max/Min hours runtime details
set linesize 200
col username for a10
col status for a10
col phase  for a10
col PNAME for a70
col request_id for 99999999999
col PNAME  for a40
select
b.user_name username,
a.USER_CONCURRENT_PROGRAM_NAME  as PNAME,
avg((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) avg_Hrs_running,  
max((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) Max_Hrs_running,
min((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) Min_Hrs_running
from
apps.fnd_conc_req_summary_v a,
apps.fnd_user  b
where
phase_code = 'C' and status_code = 'C' and
a.REQUESTED_START_DATE > sysdate-30 and
upper(a.USER_CONCURRENT_PROGRAM_NAME) like upper('%&str%') and
a.REQUESTED_BY=b.user_id
group by b.user_name,a.USER_CONCURRENT_PROGRAM_NAME;  


Oracle EBS user level scripts
Given a user name list all the responsibilities assigned to this user
SELECT UNIQUE u.user_id, u.user_name,
SUBSTR (r.responsibility_name, 1, 60) responsiblity,
SUBSTR (a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE u.USER_NAME = :USER_NAME
AND g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60)


 This is to list the  Apps User who are assigned a particular responsibility
select a.user_name,b.responsibility_name 
from  fnd_user a,fnd_responsibility_vl b ,FND_USER_RESP_GROUPS c
where a.user_id = c.user_id
and c.RESPONSIBILITY_ID = b.RESPONSIBILITY_ID
and lower(b.responsibility_name) like lower('&Responsibility_name%')
and a.END_DATE is null
and (c.END_DATE is null
or c.end_date > sysdate)
group by a.USER_NAME,b.RESPONSIBILITY_NAME

List Responsibilities That Can Run a Given Concurrent Program
This script helps in identifying the list of responsibilities that can run a given concurrent request

SELECT responsibility_name RN
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'P'
AND (UPPER(fcpv.concurrent_program_name) = UPPER('&1')
OR
UPPER(fcpv.user_concurrent_program_name) = UPPER('&1'))
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name
/


How to create to Oracle DB User which is like APPS but has only SELECT privileges
(this user should not have to use schema name.objectname and all SELECT qeries run from APPS user should work from this DB User as well)
a.       Create a user using
Create user ‘username’ identified by ‘password’;
b.      Then execute the following command in the sql prompt as the sys user,
spool create_grants.sql
set echo off
set pagesize 0
set linesize 300
set feedback off
SELECT 'Grant select on APPS.'||object_name||' to A;' FROM user_objects WHERE object_type IN ('TABLE','VIEW');
spool off;
exit;
c.       Executing the script  would create a sql file name create_grants.sql that has all the ‘SELECT priveleges’ of all the objects that the APPS user has access to..
d.      Finally run the create_grants.sql script.
 

Autoconfig failed with error AC-50480: Internal error occurred:

Autoconfig failed with error AC-50480: Internal error occurred: java.lang.Exception: Error while generating listener.ora.

Issue:
Running autoconfig failed during execution of adgentns.pl file.
below is the error message seen in adconfig.log file.

##########################################################################
Logfile:  /u01/orcl/inst/apps/orcl_oradb/admin/log/08201702/NetServiceHandler.log
        Classpath                   : /u02/orcl/apps/apps_st/comn/java/lib/appsborg2.zip:/u02/orcl/apps/apps_st/comn/java/classes
Updating s_tnsmode to 'generateTNS'
UpdateContext exited with status: 0
AC-50480: Internal error occurred: java.lang.Exception: Error while generating listener.ora.
Error generating tnsnames.ora from the database, temporary tnsnames.ora will be generated using templates
Instantiating Tools tnsnames.ora
Tools tnsnames.ora instantiated
Web tnsnames.ora instantiated
adgentns.pl exiting with status 2....
....
[CVM Error Report]
The following report lists errors encountered during CVM Phase
      <filename>  <return code where appropriate>
  /u02/orcl/apps/apps_st/appl/ad/12.0.0/bin/adgentns.pl  2
No of scripts failed in CVM phase: 1

--> Verifying NetServiceHandler.log file it showed below errors while running script admk80ln.sh which is used to create tnsnames.ora and listener.ora file by retrieving node details from database.

Enter value for 1: Enter value for 2: ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Unable to generate listener.ora from database
Using default listener.ora file
Error while generating listener.ora.

StackTrace:
java.lang.Exception: Error while generating listener.ora.

Cause:

When queried FND_NODES table only applicaiton tier node information is retrieved. Database tier information is missing.
Upon investigation it is identified that node clean is performed on the ebs database and autoconfig on database tier is not done.

Solution:
1) Run autoconfig on database tier
2) Once autoconfig on database tier is successfull run again on application tier and this time it should be successful.