-- For a particulare concurrent program, list concurrent
-- requests (and their submitters) having a status of
-- Warning or Error.  Current as of Oracle Apps 11.i.10.
VARIABLE application_short_name VARCHAR2(50)
VARIABLE concurrent_pgm_name    VARCHAR2(240)
EXEC :application_short_name := 'PA'; :concurrent_pgm_name := 'PRC: Transaction Import';
SELECT   papf.employee_number                       submitter_employee_number
,        papf.full_name                             submitter_employee_name
,        fcr.request_id
,        frv.responsibility_name                    submitted_using_responsibility
,        TO_CHAR(fcr.request_date
         ,      'Dy fmMM/DD/YYYY HH:fmMI:SS AM')    date_scheduled
,        TO_CHAR(fcr.requested_start_date
         ,      'Dy fmMM/DD/YYYY HH:fmMI:SS AM')    requested_run_date
,        DECODE(fcr.actual_completion_date
         ,      TO_DATE(NULL), NULL
         ,      TO_CHAR(TRUNC((fcr.actual_completion_date
                               - fcr.actual_start_date) * 24)
                ,       'fm9900')
                || ':'
                || TO_CHAR(MOD(FLOOR((fcr.actual_completion_date
                                      - fcr.actual_start_date) * 24 * 60)
                           ,   60)
                   ,       'fm00')
                || ':'
                || TO_CHAR(MOD(((fcr.actual_completion_date
                                 - fcr.actual_start_date) * 24 * 60 * 60)
                           ,     60)
                   ,       'fm00')
         )                                          "RUN DURATION HH:MM:SS"
,        fl_phs.meaning                             phase_of_request
,        fl_stat.meaning                            status_of_request
FROM     applsys.fnd_application             fa
,        applsys.fnd_application_tl          fat
,        applsys.fnd_concurrent_programs     fcp
,        applsys.fnd_concurrent_programs_tl  fcpt
,        applsys.fnd_concurrent_requests     fcr
,        applsys.fnd_user                    fu_req
,        hr.per_all_people_f                 papf
,        apps.fnd_lookups                    fl_phs
,        apps.fnd_lookups                    fl_stat
,        apps.fnd_responsibility_vl          frv
WHERE    USERENV('LANG')                   = fat.language
AND      fat.application_id                = fa.application_id
AND      fa.application_short_name         = :application_short_name
AND      fa.application_id                 = fcpt.application_id
AND      fcpt.user_concurrent_program_name = :concurrent_pgm_name
AND      USERENV('LANG')                   = fcpt.language
AND      fcpt.concurrent_program_id        = fcp.concurrent_program_id
AND      fcpt.application_id               = fcp.application_id
AND      fcp.concurrent_program_id         = fcr.concurrent_program_id
AND      fcp.application_id                = fcr.program_application_id
AND      'CP_PHASE_CODE'                   = fl_phs.lookup_type 
AND      fcr.phase_code                    = fl_phs.lookup_code 
AND      'CP_STATUS_CODE'                  = fl_stat.lookup_type 
AND      fcr.status_code                   = fl_stat.lookup_code 
AND      fl_stat.meaning                   IN ('Warning','Error')
AND      fcr.requested_by                  = fu_req.user_id
AND      fu_req.employee_id                = papf.person_id (+)
AND      TRUNC(SYSDATE) BETWEEN papf.effective_start_date (+)
                            AND papf.effective_end_date (+)
AND      fcr.responsibility_id = frv.responsibility_id
AND      fcr.responsibility_application_id = frv.application_id
ORDER BY fcr.request_date DESC
/