Concurrent requests Errors report for the week

-- -------------------------------------------------------------------------------------------------------------
-- Purpose : Get the detials of errors and warnings for the concurrent requests during the week.
-- Date      : 22.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,
fa.BASEPATH Application_Top,
fr.RESPONSIBILITY_KEY Responsibility,
(select meaning from fnd_lookups
where lookup_type = 'CP_PHASE_CODE'
and lookup_code = fcr.PHASE_CODE) Phase_Code,
(select meaning from fnd_lookups
where lookup_type = 'CP_STATUS_CODE'
and lookup_code = fcr.STATUS_CODE) Status_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,
FND_APPLICATION fa
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 fa.APPLICATION_ID = fe.APPLICATION_ID
and fcr.STATUS_CODE in('E','X','G','D')
and fcr.ACTUAL_COMPLETION_DATE > sysdate-7
order by 9;

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