SELECT fcr.request_id,
DECODE (fcr.phase_code,
'P', DECODE (fcr.hold_flag, 'Y', 'Inactive', fl_pend.meaning),
fl_pend.meaning
) phase,
DECODE (fcr.phase_code,
'P', DECODE (fcr.hold_flag,
'Y', 'On Hold',
DECODE (SIGN (fcr.requested_start_date - SYSDATE),
1, 'Scheduled',
fl_stat.meaning
)
),
fl_stat.meaning
) status,
fcpt.user_concurrent_program_name, fcr.increment_dates,
fcr.resubmit_interval, fcr.resubmit_interval_unit_code,
fcr.resubmit_interval_type_code, parent_request_id,
fcr.requested_start_date, fu.user_name requested_by
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcpt,
fnd_lookups fl_pend,
fnd_lookups fl_stat,
fnd_user fu
WHERE 1 = 1
AND fcpt.concurrent_program_id = fcr.concurrent_program_id
AND fcpt.LANGUAGE = USERENV ('LANG')
AND fcr.phase_code = fl_pend.lookup_code
AND fl_pend.lookup_type = 'CP_PHASE_CODE'
AND fcr.status_code = fl_stat.lookup_code
AND fl_stat.lookup_type = 'CP_STATUS_CODE'
AND fl_pend.meaning != 'Completed'
AND fu.user_id = fcr.requested_by
ORDER BY fcr.request_id DESC