SELECT distinct fcr.request_id,
fcpt.user_concurrent_program_name
|| NVL2 (fcr.description, ' (' || fcr.description || ')', NULL)
conc_prog,
CASE
WHEN fcrc.class_type = 'P'
THEN
'Repeat every '
|| SUBSTR (fcrc.class_info,
1,
INSTR (fcrc.class_info, ':') - 1)
|| DECODE (SUBSTR (fcrc.class_info, INSTR (fcrc.class_info,
':',
1,
1)
+ 1, 1),
'N',
' minutes',
'M',
' months',
'H',
' hours',
'D',
' days')
|| DECODE (SUBSTR (fcrc.class_info, INSTR (fcrc.class_info,
':',
1,
2)
+ 1, 1),
'S',
' from the start of the prior run',
'C',
' from the completion of the prior run')
ELSE
'n/a'
END
set_days_of_week,
fu.user_name requestor,
fu.description requested_by,
fu.email_address,
frt.responsibility_name requested_by_resp,
TRIM (fl.meaning) status,
fcr.phase_code,
fcr.status_code,
fcr.argument_text "PARAMETERS",
'------>' dates,
TO_CHAR (fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested,
TO_CHAR (fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
requested_start,
TO_CHAR ( (fcr.requested_start_date), 'HH24:MI:SS') start_time,
'------>' holds,
DECODE (fcr.hold_flag,
'Y',
'Yes',
'N',
'No')
on_hold,
CASE
WHEN fcr.hold_flag = 'Y' THEN SUBSTR (u2.description, 0, 40)
END
last_update_by,
CASE WHEN fcr.hold_flag = 'Y' THEN fcr.last_update_date END
last_update_date,
'------>' prints,
fcr.number_of_copies print_count,
fcr.printer,
fcr.print_style,
'------>' schedule,
fcr.increment_dates,
CASE
WHEN fcrc.class_info IS NULL
THEN
'Yes: '
|| TO_CHAR (fcr.requested_start_date,
'DD-MON-YYYY HH24:MI:SS')
ELSE
'n/a'
END
run_once,
CASE
WHEN fcrc.class_type = 'S'
AND INSTR (SUBSTR (fcrc.class_info, 33), '1', 1) > 0
THEN
'Days of week: '
|| DECODE (SUBSTR (fcrc.class_info, 33, 1), '1', 'Sun, ')
|| DECODE (SUBSTR (fcrc.class_info, 34, 1), '1', 'Mon, ')
|| DECODE (SUBSTR (fcrc.class_info, 35, 1), '1', 'Tue, ')
|| DECODE (SUBSTR (fcrc.class_info, 36, 1), '1', 'Wed, ')
|| DECODE (SUBSTR (fcrc.class_info, 37, 1), '1', 'Thu, ')
|| DECODE (SUBSTR (fcrc.class_info, 38, 1), '1', 'Fri, ')
|| DECODE (SUBSTR (fcrc.class_info, 39, 1), '1', 'Sat ')
ELSE
'n/a'
END
days_of_week,
CASE
WHEN fcrc.class_type = 'S'
AND INSTR (SUBSTR (fcrc.class_info, 1, 31), '1', 1) > 0
THEN
'Set Days of Month: '
|| DECODE (SUBSTR (fcrc.class_info, 1, 1), '1', '1st, ')
|| DECODE (SUBSTR (fcrc.class_info, 2, 1), '1', '2nd, ')
|| DECODE (SUBSTR (fcrc.class_info, 3, 1), '1', '3rd, ')
|| DECODE (SUBSTR (fcrc.class_info, 4, 1), '1', '4th, ')
|| DECODE (SUBSTR (fcrc.class_info, 5, 1), '1', '5th, ')
|| DECODE (SUBSTR (fcrc.class_info, 6, 1), '1', '6th, ')
|| DECODE (SUBSTR (fcrc.class_info, 7, 1), '1', '7th, ')
|| DECODE (SUBSTR (fcrc.class_info, 8, 1), '1', '8th, ')
|| DECODE (SUBSTR (fcrc.class_info, 9, 1), '1', '9th, ')
|| DECODE (SUBSTR (fcrc.class_info, 10, 1), '1', '10th, ')
|| DECODE (SUBSTR (fcrc.class_info, 11, 1), '1', '11th, ')
|| DECODE (SUBSTR (fcrc.class_info, 12, 1), '1', '12th, ')
|| DECODE (SUBSTR (fcrc.class_info, 13, 1), '1', '13th, ')
|| DECODE (SUBSTR (fcrc.class_info, 14, 1), '1', '14th, ')
|| DECODE (SUBSTR (fcrc.class_info, 15, 1), '1', '15th, ')
|| DECODE (SUBSTR (fcrc.class_info, 16, 1), '1', '16th, ')
|| DECODE (SUBSTR (fcrc.class_info, 17, 1), '1', '17th, ')
|| DECODE (SUBSTR (fcrc.class_info, 18, 1), '1', '18th, ')
|| DECODE (SUBSTR (fcrc.class_info, 19, 1), '1', '19th, ')
|| DECODE (SUBSTR (fcrc.class_info, 20, 1), '1', '20th, ')
|| DECODE (SUBSTR (fcrc.class_info, 21, 1), '1', '21st, ')
|| DECODE (SUBSTR (fcrc.class_info, 22, 1), '1', '22nd, ')
|| DECODE (SUBSTR (fcrc.class_info, 23, 1), '1', '23rd,')
|| DECODE (SUBSTR (fcrc.class_info, 24, 1), '1', '24th, ')
|| DECODE (SUBSTR (fcrc.class_info, 25, 1), '1', '25th, ')
|| DECODE (SUBSTR (fcrc.class_info, 26, 1), '1', '26th, ')
|| DECODE (SUBSTR (fcrc.class_info, 27, 1), '1', '27th, ')
|| DECODE (SUBSTR (fcrc.class_info, 28, 1), '1', '28th, ')
|| DECODE (SUBSTR (fcrc.class_info, 29, 1), '1', '29th, ')
|| DECODE (SUBSTR (fcrc.class_info, 30, 1), '1', '30th, ')
|| DECODE (SUBSTR (fcrc.class_info, 31, 1), '1', '31st. ')
ELSE
'n/a'
END
days_of_month,
CASE
WHEN fcrc.class_type = 'S'
AND SUBSTR (fcrc.class_info, 32, 1) = '1'
THEN
'Yes'
ELSE
'n/a'
END
last_day_of_month_ticked,
fcrc.class_info
FROM apps.fnd_concurrent_requests fcr,
apps.fnd_user fu,
apps.fnd_user u2,
apps.fnd_concurrent_programs fcp,
apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_printer_styles_tl fpst,
apps.fnd_conc_release_classes fcrc,
apps.fnd_responsibility_tl frt,
apps.fnd_lookups fl
WHERE fcp.application_id = fcpt.application_id
AND fcr.requested_by = fu.user_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.responsibility_id = frt.responsibility_id
AND fcr.last_updated_by = u2.user_id
AND fcr.print_style = fpst.printer_style_name(+)
AND fcr.release_class_id = fcrc.release_class_id(+)
AND fcr.status_code = fl.lookup_code
AND fl.lookup_type = 'CP_STATUS_CODE'
and frt.language = 'US'
and fcpt.language = 'US'
AND (fcr.concurrent_program_id, fcr.actual_start_date) IN
( SELECT concurrent_program_id, MAX (last_run)
FROM ( SELECT concurrent_program_id,
MAX (actual_start_date) last_run
FROM apps.fnd_concurrent_requests
WHERE release_class_id IS NOT NULL
GROUP BY concurrent_program_id,
release_class_id)
GROUP BY concurrent_program_id)
No comments:
Post a Comment