Query to find all the scheduled concurrent program

on
We might be in a situation where we need to get the list of concurrent programs scheduled in the Oracle E-Business Suite. Please use the below query to find scheduled concurrent program with its next scheduled start time ,frequency and previous run time of the same concurrent program. 
col REQUESTOR for a13
col REQUESTED_BY for a13
col JOB_NAME for a45
col REQUESTED_BY_RESP for a23
col PARAMETERS for a30
set lines 999
set pagesize 999
SELECT DISTINCT r.request_id, 
       r.parent_request_id 
       PREVIOUS_REQ_ID, 
       Round (( viv.actual_completion_date - viv.actual_start_date ) *
       ( 60 * 24 ), 2)  Previous_REQ_runtime, 
       u.user_name    requestor, 
       CASE 
         WHEN pt.user_concurrent_program_name = 'Report Set' THEN 
         Decode(r.description, NULL, pt.user_concurrent_program_name, 
                               r.description 
                               || ' (' 
                               || pt.user_concurrent_program_name 
                               || ')') 
         ELSE pt.user_concurrent_program_name 
       END   job_name, 
       frt.responsibility_name   requested_by_resp, 
       To_char(( r.request_date), 'DD_MON_YYYY HH24:MI:SS ') DATE_SUBMITTED, 
       Decode(r.hold_flag, 'Y', 'Yes', 
                           'N', 'No')  on_hold, 
       r.argument_text     PARAMETERS, 
       r.resubmit_interval   resubmit_every, 
       r.resubmit_interval_unit_code     resubmit_time_period, 
       To_char(( r.requested_start_date ), 'DD_MON_YYYY HH24:MI:SS ') start_time , 
       Nvl2(r.resubmit_interval, 'Periodically', 
       Nvl2(r.release_class_id, 'On specific days', 'Once')) AS 
       schedule_type 
FROM   apps.fnd_user u, 
       apps.fnd_printer_styles_tl s, 
       apps.fnd_concurrent_requests r, 
       apps.fnd_responsibility_tl frt, 
       apps.fnd_concurrent_programs_tl pt, 
       apps.fnd_concurrent_programs pb, 
       apps.fnd_concurrent_requests viv 
WHERE  pb.application_id = r.program_application_id 
       AND r.responsibility_id = frt.responsibility_id 
       AND pb.concurrent_program_id = pt.concurrent_program_id 
       AND u.user_id = r.requested_by 
       AND s.printer_style_name( + ) = r.print_style 
       AND r.phase_code = 'P' 
       AND pb.concurrent_program_id = r.concurrent_program_id 
       AND pb.application_id = pt.application_id 
       AND viv.request_id = r.parent_request_id 
	  ORDER  BY previous_req_runtime DESC;
The above query retuns all the scheduled programs including the programs which are on hold. If you want you get the list scheduled concurrent program which is not on hold. Please use the below query. 
col REQUESTOR for a13
col REQUESTED_BY for a13
col JOB_NAME for a45
col REQUESTED_BY_RESP for a23
col PARAMETERS for a30
set lines 999
set pagesize 999
SELECT DISTINCT r.request_id, 
     r.parent_request_id 
     PREVIOUS_REQ_ID, 
     Round (( viv.actual_completion_date - viv.actual_start_date ) *
     ( 60 * 24 ), 2)  Previous_REQ_runtime, 
     u.user_name    requestor, 
     CASE 
       WHEN pt.user_concurrent_program_name = 'Report Set' THEN 
       Decode(r.description, NULL, pt.user_concurrent_program_name, 
                             r.description 
                             || ' (' 
                             || pt.user_concurrent_program_name 
                             || ')') 
       ELSE pt.user_concurrent_program_name 
     END   job_name, 
     frt.responsibility_name   requested_by_resp, 
     To_char(( r.request_date), 'DD_MON_YYYY HH24:MI:SS ') DATE_SUBMITTED, 
     Decode(r.hold_flag, 'Y', 'Yes', 
                         'N', 'No')  on_hold, 
     r.argument_text     PARAMETERS, 
     r.resubmit_interval   resubmit_every, 
     r.resubmit_interval_unit_code     resubmit_time_period, 
     To_char(( r.requested_start_date ), 'DD_MON_YYYY HH24:MI:SS ') start_time , 
     Nvl2(r.resubmit_interval, 'Periodically', 
     Nvl2(r.release_class_id, 'On specific days', 'Once')) AS 
     schedule_type 
FROM   apps.fnd_user u, 
       apps.fnd_printer_styles_tl s, 
       apps.fnd_concurrent_requests r, 
       apps.fnd_responsibility_tl frt, 
       apps.fnd_concurrent_programs_tl pt, 
       apps.fnd_concurrent_programs pb, 
       apps.fnd_concurrent_requests viv 
WHERE  pb.application_id = r.program_application_id 
       AND r.responsibility_id = frt.responsibility_id 
       AND pb.concurrent_program_id = pt.concurrent_program_id 
       AND u.user_id = r.requested_by 
       AND s.printer_style_name( + ) = r.print_style 
       AND r.phase_code = 'P' 
       AND r.hold_flag = 'N'
       AND pb.concurrent_program_id = r.concurrent_program_id 
       AND pb.application_id = pt.application_id 
       AND viv.request_id = r.parent_request_id 
	  ORDER  BY previous_req_runtime DESC;

Comments

Post a Comment