To Get the Request Id, Concurent Program and Requestor Informations

-- -------------------------------------------------------------------------------------------------------------
-- Purpose : To Get the Request Id, Concurent Program and Requestor Informations.
-- Date       : 14.May.2009
-- Created by : Chaitanya
-- .....
-- -------------------------------------------------------------------------------------------------------------



SELECT fcr.request_id,
fcp.user_concurrent_program_name Conc_prog_name,
fcp.CONCURRENT_PROGRAM_NAME Short_name,
fe.EXECUTION_FILE_NAME execution_file,
(select meaning from fnd_lookups
where lookup_type = 'CP_EXECUTION_METHOD_CODE'
and lookup_code = fe.EXECUTION_METHOD_CODE) Execution_Method,
fr.RESPONSIBILITY_KEY Responsibility,
(select meaning from fnd_lookups
where lookup_type = 'CP_STATUS_CODE'
and lookup_code = fcr.STATUS_CODE) Status_code,
(select meaning from fnd_lookups
where lookup_type = 'CP_PHASE_CODE'
and lookup_code = fcr.PHASE_CODE) Phase_Code,
fu.USER_NAME Requested_By
from fnd_concurrent_requests fcr,
fnd_user fu,
fnd_concurrent_programs_vl fcp,
fnd_responsibility fr,
fnd_executables fe
where fcr.REQUESTED_BY = fu.USER_ID
and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID
and fr.RESPONSIBILITY_ID = fcr.RESPONSIBILITY_ID
and fcp.executable_ID = fe.executable_ID
and fcr.REQUEST_ID = :request_id;

-- ----------------------------------------------------------------------------------