Monday 25 January 2016

Current running concurrent request details in oracle apps R12

Below script to know the current running concurrent request details in oracle apps R12.

Script:

set echo off pages 100 lines 202
column REQUEST heading 'Request' format a9
column PHASE heading 'Phase' format A8
column STATUS heading 'Status' format A8
column PROGRAM heading 'Program Name' format A40
column SHORT heading 'Short Name' format A15
column REQUESTOR heading 'Requestor' format A15
column START_TIME heading 'Start Time' format A15
column RUN_TIME justify left heading 'Time(m)' format 999999.9
column OSPID heading 'OSPID' format a5
column OS_PIDa heading 'OSPIDA' format a6
column SID heading 'SID' format 99999
column serial# heading 'Serial#' format 99999
column sidser heading 'SidSer#' format a10
select  substr(fcrv.request_id,1,9)REQUEST,
        decode(fcrv.phase_code,'P','Pending','R','Running','I','Inactive','Completed')PHASE,
        decode(fcrv.status_code,
        'A','Waiting',
        'B','Resuming',
        'C','Normal',
        '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',fcrv.status_code)STATUS,
        substr(fcrv.program,1,40)PROGRAM,
        substr(fcrv.PROGRAM_SHORT_NAME,1,15)SHORT,
        substr(fcrv.requestor,1,15)REQUESTOR,
--        to_char(fcrv.actual_start_date,'MM/DD/RR HH24:MI')START_TIME,
        round(((sysdate - fcrv.actual_start_date)*1440),1)RUN_TIME,
        substr(fcr.oracle_process_id,1,7)OSPID,
        s.sid||','||s.serial# sidser
--        substr(fcr.os_process_id,1,7)OS_PIDa
from    apps.fnd_conc_req_summary_v fcrv,
        apps.fnd_concurrent_requests fcr,
        v$session s,
        v$process p
where   fcrv.phase_code = 'R'
and     fcrv.request_id = fcr.request_id
and     s.paddr(+) = p.addr
and     fcr.oracle_process_id = p.spid(+)
and     fcrv.concurrent_program_id not in ('40112','40113','36887')
--and   trunc(fcrv.actual_start_date) like trunc(sysdate)
order by PHASE, STATUS,REQUEST desc
/

Output:


No comments:

Post a Comment