Query to find the concurrent program Schedule

on
Use the below query to find the schedule detail of the concurrent program using the request ID.
col requestor FOR a13
col requested_by FOR a13
col job_name FOR a30
col requested_by_resp FOR a23
col resubmit_time_period FOR a21
col parameters FOR a35
col start_time FOR a21
set lines 999
set pagesize 999
SELECT DISTINCT r.request_id,
                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,
                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
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 pb.concurrent_program_id = r.concurrent_program_id
       AND pb.application_id = pt.application_id
       AND pt.LANGUAGE = 'US'
       AND r.request_id LIKE '%&REQUEST_ID%'
ORDER  BY r.request_id DESC;

Output of the above query will be similar to below. 
REQUEST_ID JOB_NAME                       PARAMETERS                          RESUBMIT_EVERY RESUBMIT_TIME_PERIOD  START_TIME            SCHEDULE_TYPE
---------- ------------------------------ ----------------------------------- -------------- --------------------- --------------------- ----------------
  45945984 Close WFERROR Workflow Items   (None)                                           7 DAYS                  22_JUL_2023 00:00:00  Periodically
  45945983 Invoice Approval Workflow      , , , ,                                          6 HOURS                 18_JUL_2023 00:00:00  Periodically
Use the below query to find the schedule detail of the concurrent program using the Concurrent Program Name.
col requestor FOR a13
col requested_by FOR a13
col job_name FOR a30
col requested_by_resp FOR a23
col resubmit_time_period FOR a21
col parameters FOR a35
col start_time FOR a21
set lines 999
set pagesize 999
SELECT DISTINCT r.request_id,
                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,
                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
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 pb.concurrent_program_id = r.concurrent_program_id
       AND pb.application_id = pt.application_id
       AND pt.LANGUAGE = 'US'
       AND r.actual_completion_date IS NULL
       AND pt.user_concurrent_program_name LIKE '%&CONCURRENT_PROGRAM_NAME%'
ORDER  BY job_name DESC;

Output of the above query will be similar to below. 
Enter value for concurrent_program_name: Workflow Background Process
old  38:        AND pt.user_concurrent_program_name LIKE '%&CONCURRENT_PROGRAM_NAME%'
new  38:        AND pt.user_concurrent_program_name LIKE '%Workflow Background Process%'

REQUEST_ID JOB_NAME                       PARAMETERS                          RESUBMIT_EVERY RESUBMIT_TIME_PERIOD  START_TIME            SCHEDULE_TYPE
---------- ------------------------------ ----------------------------------- -------------- --------------------- --------------------- ----------------
  45921139 Workflow Background Process    , , , N, Y, N,                                   1 DAYS                  19_MAY_2023 05:00:00  Periodically
  45927021 Workflow Background Process    HXCEMP, , , Y, Y, ,                             15 MINUTES               18_MAY_2023 12:26:53  Periodically
  45927109 Workflow Background Process    HXCEMP, , , Y, N, ,                              5 MINUTES               18_MAY_2023 12:23:54  Periodically
  45927116 Workflow Background Process    PORPOCHA, , , Y, N, ,                            5 MINUTES               18_MAY_2023 12:25:00  Periodically
  45927117 Workflow Background Process    APINVAPR, , , Y, N, ,                            5 MINUTES               18_MAY_2023 12:25:00  Periodically
  45927118 Workflow Background Process    WCAPPRV, , , Y, N, ,                             5 MINUTES               18_MAY_2023 12:25:00  Periodically
  45927119 Workflow Background Process    APEXP, , , Y, N, ,                               5 MINUTES               18_MAY_2023 12:25:00  Periodically
  45927120 Workflow Background Process    POREQCHA, , , Y, N, ,                            5 MINUTES               18_MAY_2023 12:25:00  Periodically
  45927121 Workflow Background Process    CREATEPO, , , Y, N, ,                            5 MINUTES               18_MAY_2023 12:25:00  Periodically
  45927122 Workflow Background Process    XXKRTPTV, , , Y, N, ,                            5 MINUTES               18_MAY_2023 12:25:00  Periodically

Comments

Post a Comment