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;