Query:
select name,argument_text from
(SELECT prog.user_concurrent_program_name
|| DECODE (req.description, NULL, NULL, ‘:’ || req.description)
name,
req.request_id,
req.parent_request_id,
req.request_date,
DECODE (req.request_type, ‘P’, actual_start_date, requested_start_date),
req.actual_completion_date,
FLOOR( ( (actual_completion_date
– DECODE (req.request_type,
‘P’, actual_start_date,
requested_start_date))
* 24))
|| ‘:’
|| FLOOR( ( (actual_completion_date
– DECODE (req.request_type,
‘P’, actual_start_date,
requested_start_date))
* 24
– FLOOR( (actual_completion_date
– DECODE (req.request_type,
‘P’, actual_start_date,
requested_start_date))
* 24))
* 60)
|| ‘:’
|| FLOOR( ( (actual_completion_date
– DECODE (req.request_type,
‘P’, actual_start_date,
requested_start_date))
* 24
* 60
– FLOOR( (actual_completion_date
– DECODE (req.request_type,
‘P’, actual_start_date,
requested_start_date))
* 24
* 60))
* 60)
duration,
req.phase_code,
req.status_code,
req.argument_text,
usr.user_name,
resp.responsibility_name
FROM apps.fnd_concurrent_requests req,
apps.fnd_concurrent_programs_vl prog,
apps.fnd_responsibility_vl resp,
apps.fnd_user usr,
(SELECT req.request_id
FROM apps.fnd_concurrent_requests req, apps.fnd_request_sets_vl rset
WHERE req.argument1 = TO_CHAR (rset.application_id)
AND req.argument2 = TO_CHAR (rset.request_set_id)
AND (rset.request_set_name LIKE :req_set_name
OR UPPER (rset.user_request_set_name) LIKE
UPPER (:user_req_set_name)
OR req.request_id = :request_id)– AND req.parent_request_id = -1
) a
WHERE req.priority_request_id = a.request_id
AND req.program_application_id = prog.application_id
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.responsibility_id = resp.responsibility_id
AND req.responsibility_application_id = resp.application_id
AND req.requested_by = usr.user_id
AND prog.user_concurrent_program_name NOT LIKE ‘Request Set Stage%’
ORDER BY req.request_id);
