Various scripts used with Concurrent Manager/ Concurrent Requests

on Thursday, 3 May 2012

Various scripts used with Concurrent Manager/ Concurrent 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 user


SELECT 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 program


SELECT 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 run

select  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


0 comments:

Post a Comment