Query to find the Future start time of the program between certain time and previous run time of the same program

on
Please find the query to find the requests scheduled to run between certain time.You can use this query to find the request scheduled to run between certain hours and also find how much the program might run using the previous run time details. 
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 
	   AND r.requested_start_date BETWEEN 
               To_date ('07-OCT-2020 00:30:00', 'DD_MON_YYYY HH24:MI:SS') AND 
               To_date( 
               '07-OCT-2020 23:30:00', 'DD_MON_YYYY HH24:MI:SS') 
ORDER  BY previous_req_runtime DESC;

Comments

Post a Comment