Wednesday 28 March 2012

Sql query to display Concurrent program names or requests which are submitted in submit request

SELECT  FCR.request_id Request#,
 SUBSTR(TO_CHAR(FCR.actual_start_date,'DD-MON-RR HH24:MI'),1,15) StartDate,
 SUBSTR(TO_CHAR(FCR.actual_completion_date,'DD-MON-RR HH24:MI'),1,15) EndDATE,
 substr((FCR.requested_by||'-'||FU.user_name),1,20) UserName,
 decode(FCP.concurrent_program_name,'Report Set', substr(FCR.description,1,30),
   substr(FCP.concurrent_program_name,1,30)) Program_Name,
 FCR.phase_code||'-'||substr(FL1.meaning,1,10) Phase,
 FCR.status_code||'-'||substr(FL2.meaning,1,10) Status,
 FCR.hold_flag Hold
FROM  fnd_concurrent_requests FCR,
 fnd_concurrent_programs FCP,
 fnd_lookups FL1,
 fnd_lookups FL2,
 fnd_user FU
WHERE FCR.phase_code = FL1.lookup_code
AND FCR.status_code = FL2.lookup_code
AND FCR.requested_by=FU.user_id
AND FCR.program_application_id = FCP.application_id
AND FCR.concurrent_program_id = FCP.concurrent_program_id
AND FL1.lookup_type = 'CP_PHASE_CODE'
AND FL2.lookup_type = 'CP_STATUS_CODE'
AND TRUNC(FCR.requested_start_date) = '&start_date' -- Concurrent Program Start Date
ORDER BY FCR.request_id,
 FCR.requested_start_date,
 FCR.phase_code,
 FCR.status_code

No comments:

Post a Comment