fonte: http://appselangovan.blogspot.com/2014/ ... grams.html
List of Scheduled Concurrent Programs Query
/* Formatted on 7/17/2014 12:58:47 PM (QP5 v5.115.810.9015) */
SELECT r.request_id,
p.user_concurrent_program_name
|| CASE
WHEN p.user_concurrent_program_name = 'Report Set'
THEN
(SELECT ' - ' || s.user_request_set_name
FROM apps.fnd_request_sets_tl s
WHERE s.application_id = r.argument1
AND s.request_set_id = r.argument2
AND language = 'US')
WHEN p.user_concurrent_program_name = 'Check Periodic Alert'
THEN
(SELECT ' - ' || a.alert_name
FROM apps.alr_alerts a
WHERE a.application_id = r.argument1
AND a.alert_id = r.argument2
AND language = 'US')
END
concurrent_program_name,
DECODE (c.class_type,
'P', 'Periodic',
'S', 'On Specific Days',
'X', 'Advanced',
c.class_type)
schedule_type,
CASE
WHEN c.class_type = 'P'
THEN
'Repeat every '
|| SUBSTR (c.class_info, 1, INSTR (c.class_info, ':') - 1)
|| DECODE (SUBSTR (c.class_info,
INSTR (c.class_info, ':', 1, 1) + 1,
1
),
'N',
' minutes',
'M',
' months',
'H',
' hours',
'D',
' days'
)
|| DECODE (SUBSTR (c.class_info,
INSTR (c.class_info, ':', 1, 2) + 1,
1
),
'S',
' from the start of the prior run',
'C',
' from the completion of the prior run'
)
WHEN c.class_type = 'S'
THEN
NVL2 (dates.dates, 'Dates: ' || dates.dates || '. ', NULL)
|| DECODE (SUBSTR (c.class_info, 32, 1),
'1',
'Last day of month '
)
|| DECODE (SIGN (TO_NUMBER (SUBSTR (c.class_info, 33))),
'1',
'Days of week: '
|| DECODE (SUBSTR (c.class_info, 33, 1), '1', 'Su ')
|| DECODE (SUBSTR (c.class_info, 34, 1), '1', 'Mo ')
|| DECODE (SUBSTR (c.class_info, 35, 1), '1', 'Tu ')
|| DECODE (SUBSTR (c.class_info, 36, 1), '1', 'We ')
|| DECODE (SUBSTR (c.class_info, 37, 1), '1', 'Th ')
|| DECODE (SUBSTR (c.class_info, 38, 1), '1', 'Fr ')
|| DECODE (SUBSTR (c.class_info, 39, 1), '1', 'Sa ')
)
END
schedule,
r.requested_start_date next_run,
CASE
WHEN p.user_concurrent_program_name != 'Report Set'
AND p.user_concurrent_program_name != 'Check Periodic Alert'
THEN
r.argument_text
END
argument_text,
r.hold_flag on_hold,
c.date1 start_date,
c.date2 end_date,
c.class_info,
user_name
FROM apps.fnd_concurrent_requests r,
applsys.fnd_conc_release_classes c,
apps.fnd_concurrent_programs_tl p,
apps.fnd_user usr,
(SELECT release_class_id,
SUBSTR (MAX (SYS_CONNECT_BY_PATH (s, ' ')), 2) dates,
a
FROM (SELECT release_class_id,
RANK () OVER (PARTITION BY release_class_id ORDER BY s) a,
s
FROM (SELECT c.class_info,
l,
c.release_class_id,
DECODE (SUBSTR (c.class_info, l, 1), '1', TO_CHAR (l))
s
FROM (SELECT LEVEL l
FROM DUAL
CONNECT BY LEVEL <= 31),
apps.fnd_conc_release_classes c
WHERE c.class_type = 'S')
WHERE s IS NOT NULL)
CONNECT BY PRIOR (a || release_class_id) = (a - 1) || release_class_id
GROUP BY release_class_id, a) dates
WHERE r.phase_code = 'P'
AND c.application_id = r.release_class_app_id
AND c.release_class_id = r.release_class_id
AND NVL (c.date2, SYSDATE + 1) > SYSDATE
AND c.class_type IS NOT NULL
AND p.concurrent_program_id = r.concurrent_program_id
AND p.application_id = r.program_application_id
AND p.language = 'US'
AND dates.release_class_id(+) = r.release_class_id
AND usr.user_id = requested_by
ORDER BY requested_by, on_hold, next_run;