Query to get Errored or Warning Concurrent Program Request details in R12

SELECT fu.user_name

      ,fcpt.user_concurrent_program_name

  ,fcpt.description

  ,fcp.concurrent_program_name

  ,fcr.request_id

  ,((fcr.actual_completion_date

         -DECODE (TRUNC(fcr.request_date)

         ,fcr.requested_start_date

,fcr.request_date

,fcr.requested_start_date

))*60*24) wait_time_min

      ,DECODE(fcr.phase_code,'C','Completed'

                        ,'R','Running'

,fcr.phase_code

) phase_code

  ,DECODE(fcr.status_code,'C','Completed'

                         ,'R','Running'

,'W','Paused'

,'E','Error'

,'G','Warning'

,fcr.status_code

) status_code

  ,TO_CHAR(fcr.request_date,'DD/MM/YYYY HH24:MI:SS') request_date

  ,TO_CHAR(fcr.requested_start_date,'DD/MM/YYYY HH24:MI:SS') start_time

  ,TO_CHAR(fcr.actual_completion_date,'DD/MM/YYYY HH24:MI:SS') complete_time

  FROM fnd_concurrent_requests fcr

      ,fnd_concurrent_programs fcp

  ,fnd_concurrent_programs_tl fcpt

  ,fnd_user fu

 WHERE 1=1

   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

   AND TRUNC(fcr.request_date) BETWEEN SYSDATE - 5 AND SYSDATE

   AND fcr.status_code IN ('E','G')

ORDER BY fcr.status_code

        ,fcp.concurrent_program_name

,fcr.request_id

fnd_concurrent_requests.status_code in Oracle APPS R12(CP_STATUS_CODE)

SELECT lookup_code

      ,meaning 

  FROM fnd_lookups 

 WHERE lookup_type = 'CP_STATUS_CODE'

ORDER BY lookup_code

SQL Query to find Request Set details in R12

SELECT frt.responsibility_name

      ,frg.request_group_name

  ,frgu.request_unit_type

  ,frgu.request_unit_id

  ,fcpt.user_request_set_name

 FROM apps.fnd_Responsibility fr

     ,apps.fnd_responsibility_tl frt

,apps.fnd_request_groups frg

,apps.fnd_request_group_units frgu

,apps.fnd_request_Sets_tl fcpt

 WHERE frt.responsibility_id = fr.responsibility_id

   AND frg.request_group_id = fr.request_group_id

   AND frgu.request_group_id = frg.request_group_id

   AND fcpt.request_set_id = frgu.request_unit_id

   AND frt.language = USERENV('LANG')

   AND fcpt.language = USERENV('LANG')

   AND fcpt.user_request_set_name = :p_request_set_name

order by frt.responsibility_name

      ,frg.request_group_name

  ,frgu.request_unit_type

SQL Query to check Concurrent Program details in R12

SELECT frt.responsibility_name

      ,frg.request_group_name

  ,frgu.request_unit_type

  ,frgu.request_unit_id

  ,fcpt.user_concurrent_program_name

  FROM fnd_Responsibility fr

      ,fnd_responsibility_tl frt

  ,fnd_request_groups frg

  ,fnd_request_group_units frgu

  ,fnd_concurrent_programs_tl fcpt

 WHERE frt.responsibility_id = fr.responsibility_id

   AND frg.request_group_id = fr.request_group_id

   AND frgu.request_group_id = frg.request_group_id

   AND fcpt.concurrent_program_id = frgu.request_unit_id

   AND frt.language = USERENV('LANG')

   AND fcpt.language = USERENV('LANG')

   AND fcpt.user_concurrent_program_name = :p_conc_program_name

ORDER BY frt.responsibility_name

      ,frg.request_group_name

  ,frgu.request_unit_type

SQL Query to check Concurrent Requests SID & OS Process Details for given Request ID in R12

SELECT fcr.request_id

      ,fcr.argument_text

      ,fcr.phase_code

  ,fcr.status_code

  ,fcr.oracle_process_id

  ,fcr.os_process_id

  ,f.user_name

  FROM fnd_concurrent_requests fcr

      ,fnd_user f 

WHERE fcr.request_id = :p_request_id

  AND fcr.requested_by=f.user_id

SQL Query to Check Currently Running Concurrent Requests in Oracle APPS R12

SELECT fcr.request_id

      ,fcr.oracle_process_id

  ,fcr.os_process_id

  ,fcp.user_concurrent_program_name

  ,fu.user_name

  ,fcr.status_code

  ,fcr.phase_code

  ,vsp.username

  ,vsp.sid

  ,vsp.serial#

  ,vsp.program

  ,vsw.event

  ,TO_CHAR(fcr.actual_start_date,'MON-DD-HH-MI-SS') actual_start_date

  ,TO_CHAR(fcr.actual_completion_date,'MON-DD-HH-MI-SS') actual_completion_date

  FROM fnd_concurrent_requests fcr

      ,(SELECT c.username

          ,c.sid

  ,c.serial#

  ,c.program

  ,d.spid 

  FROM v$session c

      ,v$process d

         WHERE c.paddr=d.addr

) vsp

  ,fnd_concurrent_programs_tl fcp

  ,fnd_user fu

  ,v$session_wait vsw

WHERE fcr.oracle_process_id=vsp.spid

  AND fcr.concurrent_program_id=fcp.concurrent_program_id

  AND fcp.language=USERENV('LANG')

  AND fcr.requested_by=fu.user_id

  AND vsp.sid=vsw.sid

  AND fcr.status_code='R'

  AND fcr.phase_code='R'

Query to get History of Concurrent Requests which are errored out in Oracle APPS R12

SELECT a.request_id request_id

      ,a.phase_code

  ,a.status_code

      ,actual_start_date

      ,actual_completion_date

      ,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name concurrent_program_name

  FROM fnd_Concurrent_requests a

      ,fnd_concurrent_processes b

      ,fnd_concurrent_queues q

      ,fnd_concurrent_programs c

      ,fnd_concurrent_programs_tl ctl

 WHERE a.controlling_manager = b.concurrent_process_id

   AND a.concurrent_program_id = c.concurrent_program_id

   AND a.program_application_id = c.application_id

   AND a.status_code = 'E'

   AND a.phase_code = 'C'

   AND actual_start_date > SYSDATE - 10

   AND b.queue_application_id = q.application_id

   AND b.concurrent_queue_id = q.concurrent_queue_id

   AND ctl.concurrent_program_id = c.concurrent_program_id

   AND ctl.LANGUAGE = USERENV('LANG')

ORDER BY actual_completion_date

Query to get the Running History of Concurrent Programs in Oracle APPS R12

SELECT fcr.oracle_session_id o_sid

      ,ROUND (( ( NVL (fcr.actual_completion_date, SYSDATE)- fcr.actual_start_date)* 60* 24),2) runtime_min

      ,fcr.request_id request_id

      ,fcr.requested_by requested_by

      ,fu.user_name user_name

      ,fr.responsibility_name

      ,fcr.concurrent_program_id concurrent_program_id

      ,fcp.user_concurrent_program_name concurrent_program_name

      ,TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') start_time

      ,DECODE (fcr.status_code ,'C', 'C:Completed','G', 'G:Warning','E', 'E:Error','Q','Q:Queued',fcr.status_code) status

      ,TO_CHAR (fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') end_time

      ,ROUND (( ( NVL (fcr.actual_completion_date, SYSDATE)- fcr.actual_start_date)* 60* 60* 24),2) run_time_in_sec

      ,fcr.oracle_process_id "oracle_pid/SPID"

      ,fcr.os_process_id os_pid

      ,fcr.argument_text

      ,fcr.outfile_name

      ,fcr.logfile_name

      ,fcr.enable_trace

  FROM fnd_concurrent_requests fcr

      ,fnd_user fu

      ,fnd_responsibility_tl fr

      ,fnd_concurrent_programs_tl fcp

 WHERE fcp.user_concurrent_program_name LIKE 'XX Concurrent Program Name%'

    AND fr.responsibility_id = fcr.responsibility_id

   AND fcr.concurrent_program_id = fcp.concurrent_program_id

   AND fcr.program_application_id = fcp.application_id

   AND fu.user_id = fcr.requested_by

   --AND fcr.request_id = 1234567890

ORDER BY fcr.concurrent_program_id

        ,request_id DESC

Query to get Long Running Concurrent Programs in R12

SELECT fcr.oracle_session_id

      ,fcr.request_id rqst_id

      ,fcr.requested_by rqst_by

      ,fu.user_name

      ,fr.responsibility_name

      ,fcr.concurrent_program_id cp_id

      ,fcp.user_concurrent_program_name cp_name

      ,TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') act_start_datetime

      ,DECODE (fcr.status_code, 'R', 'R:Running', fcr.status_code) status

      ,ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) runtime_in_min

      ,ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 60 * 24), 2)runtime_in_sec

      ,fcr.oracle_process_id "oracle_pid/SPID"

      ,fcr.os_process_id os_pid

      ,fcr.argument_text

      ,fcr.outfile_name

      ,fcr.logfile_name

      ,fcr.enable_trace

  FROM fnd_concurrent_requests fcr

      ,fnd_user fu

      ,fnd_responsibility_tl fr

      ,fnd_concurrent_programs_tl fcp

 WHERE fcr.status_code = 'R'

   AND fu.user_id = fcr.requested_by

   AND fr.responsibility_id = fcr.responsibility_id

   AND fcr.concurrent_program_id = fcp.concurrent_program_id

   AND fcr.program_application_id = fcp.application_id

   AND ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) > 60

   ORDER BY fcr.concurrent_program_id

           ,request_id DESC

Query to get Trace file Including SID in Oracle APPS R12

SELECT 'Request id: '||request_id 

      ,'Trace id: '||oracle_Process_id

  ,'Trace Flag: '||req.enable_trace

  ,'Trace Name: '||dest.value||'/'||LOWER(dbnm.value)

                 ||'_ora_'||oracle_process_id||'.trc', 'Prog. Name: '

                     ||prog.user_concurrent_program_name

  ,'File Name: '||execname.execution_file_name|| execname.subroutine_name 

  ,'Status : '||DECODE(phase_code,'R','Running') ||'-'|| DECODE(status_code,'R','Normal')

  ,'SID Serial: '||ses.sid||','|| ses.serial#, 'Module : '||ses.module 

  FROM fnd_concurrent_requests req

      ,v$session ses

  ,v$process proc

  ,v$parameter dest

  ,v$parameter dbnm

  ,fnd_concurrent_programs_vl prog

  ,fnd_executables execname 

 WHERE req.request_id = :p_request_id

   AND req.oracle_process_id=proc.spid(+)

   AND proc.addr = ses.paddr(+) 

   AND dest.name='user_dump_dest' 

   AND dbnm.name='db_name'

   AND req.concurrent_program_id = prog.concurrent_program_id 

   AND req.program_application_id = prog.application_id

   AND prog.application_id = execname.application_id 

   AND prog.executable_id=execname.executable_id

SQL to find out the Raw Trace Name and Location for the Concurrent Program

SELECT req.request_id

      ,req.logfile_node_name node

      ,req.oracle_Process_id

      ,req.enable_trace

      ,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename

      ,prog.user_concurrent_program_name

      ,execname.execution_file_name

      ,execname.subroutine_name

      ,phase_code

      ,status_code

      ,ses.SID

      ,ses.serial#

      ,ses.module

      ,ses.machine

  FROM fnd_concurrent_requests req

      ,v$session ses

      ,v$process proc

      ,v$parameter dest

      ,v$parameter dbnm

      ,fnd_concurrent_programs_vl prog

      ,fnd_executables execname

 WHERE 1=1

   AND req.request_id = :p_request_id

   AND req.oracle_process_id = proc.spid(+)

   AND proc.addr = ses.paddr(+)

   AND dest.name='user_dump_dest'

   AND dbnm.name='db_name'

   AND req.concurrent_program_id = prog.concurrent_program_id

   AND req.program_application_id = prog.application_id

   AND prog.application_id = execname.application_id

   AND prog.executable_id=execname.executable_id

Query to get Concurrent Manager Status in R12

SELECT q.concurrent_queue_id

      ,q.concurrent_queue_name

  ,q.user_concurrent_queue_name

  ,q.target_node

  ,q.max_processes

  ,q.running_processes

  ,running.run running

  ,pending.pend

  ,DECODE(q.control_code, 'D', 'Deactivating'

, 'E', 'Deactivated'

, 'N', 'Node unavai'

, 'A', 'Activating'

, 'X', 'Terminated'

, 'T', 'Terminating'

, 'V', 'Verifying'

, 'O', 'Suspending'

, 'P', 'Suspended'

, 'Q', 'Resuming'

, 'R', 'Restarting') status

  FROM (SELECT concurrent_queue_name

              ,COUNT(phase_code) run

          FROM fnd_concurrent_worker_requests

         WHERE phase_code = 'R'

           AND hold_flag != 'Y'

           AND requested_start_date <= SYSDATE GROUP BY concurrent_queue_name

) running

   ,(SELECT concurrent_queue_name

           ,COUNT(phase_code) pend 

   FROM fnd_concurrent_worker_requests 

  WHERE phase_code = 'P' 

    AND hold_flag != 'Y' 

AND requested_start_date <= SYSDATE GROUP BY concurrent_queue_name

    ) pending

   ,apps.fnd_concurrent_queues_vl q

 WHERE q.concurrent_queue_name = running.concurrent_queue_name(+) 

   AND q.concurrent_queue_name = pending.concurrent_queue_name(+) 

   AND q.enabled_flag = 'Y' 

ORDER BY DECODE(q.application_id, 0, DECODE(q.concurrent_queue_id, 1, 1,4, 2))

     ,SIGN(q.max_processes) DESC

,q.concurrent_queue_name

,q.application_id

SQL Query to get the Responsibility/Request Group attached for a given Concurrent Program

SELECT DISTINCT frt.responsibility_name ,frg.request_group_name ,frgu.request_unit_type ,frgu.request_unit_id ,fcpt.user_concurrent_program_name FROM fnd_responsibility fr ,fnd_responsibility_tl frt ,fnd_request_groups frg ,fnd_request_group_units frgu ,fnd_concurrent_programs_tl fcpt WHERE frt.responsibility_id = fr.responsibility_id AND frg.request_group_id = fr.request_group_id AND frgu.request_group_id = frg.request_group_id AND fcpt.concurrent_program_id = frgu.request_unit_id AND UPPER(fcpt.user_concurrent_program_name) = UPPER(:p_concurrent_program) ORDER BY frt.responsibility_name