/*
code:pending_normal.sql
Description: This script will all the pending normal jobs running (oracle 11i) in oracle applications
Backend query is always faster than front end query
Author:      Jay Gopal / Jiltin
Input:       none
*/

select
fcr.REQUEST_ID,
fu.user_name,
fcr.PHASE_CODE,
fcr.STATUS_CODE,
(fcr.ACTUAL_COMPLETION_DATE -   fcr.ACTUAL_START_DATE) * 24 * 60 TIME_MINS,
fcpt.USER_CONCURRENT_PROGRAM_NAME,
fcp.CONCURRENT_PROGRAM_NAME,
fcr.ACTUAL_START_DATE,
fcr.ACTUAL_COMPLETION_DATE,
fcp.CONCURRENT_PROGRAM_NAME
FROM
apps.FND_CONCURRENT_REQUESTS FCR,
apps.FND_CONCURRENT_PROGRAMS FCP,
apps.FND_CONCURRENT_PROGRAMS_TL FCPT,
apps.FND_USER FU
where  1=1
and    fcr.PHASE_CODE = ‘P’  — Pending
and    fcr.STATUS_CODE = ‘I’ — Normal
and    fcp.CONCURRENT_PROGRAM_ID=fcr.CONCURRENT_PROGRAM_ID
and    fcpt.CONCURRENT_PROGRAM_ID=fcp.CONCURRENT_PROGRAM_ID
AND    FCR.REQUESTED_BY = FU.USER_ID
order by fcr.request_id desc;