Search This Blog

Friday, March 7, 2008

Find Running, Pending, On Hold and Scheduled Requests

There are several instances where we need to get the list of scheduled concurrent request. Below is another handy query that displays all the concurrent request that are in running, pending or scheduled status.

SELECT fcr.request_id,
DECODE (fcr.phase_code,
'P', DECODE (fcr.hold_flag, 'Y', 'Inactive', 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',
) 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 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

PS: The query below is not fully tested, hence please provide your feedback incase you find any unexpected result.

1 Comment:

Anonymous said...

As the other information that I found in your blog, this is a wonderful query!
Thank you very much for sharing your knowledge.

Best Regards,
Dilek Keller

Copyright (c) All rights reserved. Presented by Suresh Vaishya