SELECT fcr.request_id,
fcpt.user_concurrent_program_name ||
NVL2(fcr.description, ' (' || fcr.description || ')', NULL) conc_prog,
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",
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,
DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold,
CASE
WHEN fcr.hold_flag = 'Y' Then
Substr(fu.description, 0, 40)
END last_update_by,
CASE
WHEN fcr.hold_flag = 'Y' THEN
fcr.last_update_date
END last_update_date,
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 = '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,
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
FROM fnd_concurrent_requests fcr,
fnd_user fu,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_printer_styles_tl fpst,
fnd_conc_release_classes fcrc,
fnd_responsibility_tl frt,
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.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 fcr.phase_code = 'P'
AND 1 = 1
Order By Fu.Description, Fcr.Requested_Start_Date Asc;
fcpt.user_concurrent_program_name ||
NVL2(fcr.description, ' (' || fcr.description || ')', NULL) conc_prog,
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",
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,
DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold,
CASE
WHEN fcr.hold_flag = 'Y' Then
Substr(fu.description, 0, 40)
END last_update_by,
CASE
WHEN fcr.hold_flag = 'Y' THEN
fcr.last_update_date
END last_update_date,
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 = '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,
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
FROM fnd_concurrent_requests fcr,
fnd_user fu,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_printer_styles_tl fpst,
fnd_conc_release_classes fcrc,
fnd_responsibility_tl frt,
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.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 fcr.phase_code = 'P'
AND 1 = 1
Order By Fu.Description, Fcr.Requested_Start_Date Asc;
No comments:
Post a Comment