SELECT a.request_id,
b.User_concurrent_program_name,
c.user_name submitted_by,
TO_CHAR (a.requested_start_date, 'DD-MON-YYYY HH24:MM:SS'),
'Every '
|| DECODE (
LENGTH (d.class_info),
39, d.class_info,
SUBSTR (d.class_info, 1, INSTR (d.class_info, ':', 1) - 1)
|| ' '
|| DECODE (
SUBSTR (d.class_info, INSTR (d.class_info, ':', 1) + 1, 1),
'N', 'Minute(s) ',
'D', 'Day(s) ',
'H', 'Hour(s) ',
'M', 'Month(s) ')
|| 'after '
|| DECODE (SUBSTR (d.class_info,
INSTR (d.class_info,
':',
1,
2)
+ 1,
1),
'S', 'Start ',
'C', 'Completion ')
|| 'of prior request')
SCHED,
NVL (TO_CHAR (d.end_date_active, 'DD-MON-YYYY'), 'forever')
"Ending On",
RTRIM (RESUBMIT_INTERVAL)
|| ' '
|| RTRIM (RESUBMIT_INTERVAL_UNIT_CODE)
"Interval",
DECODE (
RTRIM (RESUBMIT_INTERVAL)
|| ' '
|| RTRIM (RESUBMIT_INTERVAL_UNIT_CODE),
'14 DAYS', TO_CHAR (REQUESTED_START_DATE, 'DY'),
'7 DAYS', TO_CHAR (REQUESTED_START_DATE, 'DY'))
"DOW",
TRANSLATE (SUBSTR (sch_curr_values, 33, 7), '01', 'nY') "SMTWTFS",
TO_CHAR (
INSTR (
TRANSLATE (
REPLACE (
REPLACE (
SUBSTR (
DECODE (RESUBMIT_INTERVAL_UNIT_CODE,
NULL, sch_curr_values),
1,
31),
'1:',
' '),
'14',
' '),
'01YN47DSP:',
'nY '),
'Y',
1),
'B99')
AS Day_of_Mth,
TRANSLATE (SUBSTR (sch_curr_values, 32, 1), '01', 'nY') AS LAST_DAY
FROM applsys.fnd_concurrent_requests a,
applsys.fnd_concurrent_programs_tl b,
applsys.fnd_user c,
applsys.FND_CONC_RELEASE_CLASSES d
WHERE a.phase_code = 'P'
AND a.status_code IN ('I', 'Q')
AND a.program_application_id = b.application_id
AND a.concurrent_program_id = b.concurrent_program_id
AND b.language = 'US'
AND a.requested_by = c.user_id
AND a.release_class_app_id = d.application_id
AND a.release_class_id = d.release_class_id
ORDER BY a.requested_start_date
No comments:
Post a Comment