cm queries

To check the request type and Programs for the concurrent Managers

　

column action format a10

column manager format a20

column object format a20

column type format a10

set pages 0

set lines 400

select q.application_id, q.concurrent_queue_name,

q.user_concurrent_queue_name "Manager", l1.meaning "Action",

l2.meaning "Type", p.user_concurrent_program_name "Object"

from apps.fnd_concurrent_queue_content c, apps.fnd_concurrent_queues_vl q,

apps.fnd_concurrent_programs_vl p, apps.fnd_lookups l1, apps.fnd_lookups l2

where q.concurrent_queue_id = c.concurrent_queue_id

and q.application_id = c.queue_application_id

and c.type_code = 'P'

and c.type_id = p.concurrent_program_id

and c.type_application_id = p.application_id

and l1.lookup_code = c.include_flag

and l1.lookup_type = 'INCLUDE_EXCLUDE'

and l2.lookup_code = 'P' and l2.lookup_type = 'CP_SPECIAL_RULES'

UNION ALL

select q.application_id, q.concurrent_queue_name,

q.user_concurrent_queue_name "Manager", l1.meaning "Action",

'Application' "Type", a.application_name "Object"

from apps.fnd_concurrent_queue_content c, apps.fnd_concurrent_queues_vl q,

apps.fnd_application_vl a, apps.fnd_lookups l1

where q.concurrent_queue_id = c.concurrent_queue_id

and q.application_id = c.queue_application_id

and c.type_code = 'P'

and c.type_id is null

and c.type_application_id = a.application_id

and l1.lookup_code = c.include_flag

and l1.lookup_type = 'INCLUDE_EXCLUDE'

UNION ALL

select q.application_id, q.concurrent_queue_name,

q.user_concurrent_queue_name "Manager", l1.meaning "Action",

l2.meaning "Type", x.complex_rule_name "Object"

from apps.fnd_concurrent_queue_content c, apps.fnd_concurrent_queues_vl q,

apps.fnd_concurrent_complex_rules x,

apps.fnd_lookups l1, apps.fnd_lookups l2

where q.concurrent_queue_id = c.concurrent_queue_id

and q.application_id = c.queue_application_id

and c.type_code = 'C'

and c.type_id = x.complex_rule_id

and c.type_application_id = x.application_id

and l1.lookup_code = c.include_flag and l1.lookup_type = 'INCLUDE_EXCLUDE'

and l2.lookup_code = 'C'

and l2.lookup_type = 'CP_SPECIAL_RULES'

UNION ALL

select q.application_id, q.concurrent_queue_name,

q.user_concurrent_queue_name "Manager", l1.meaning "Action", l2.meaning "Type", r.request_class_name "Object"

from apps.fnd_concurrent_queue_content c, apps.fnd_concurrent_queues_vl q, apps.fnd_concurrent_request_class r,

apps.fnd_lookups l1, apps.fnd_lookups l2

where q.concurrent_queue_id = c.concurrent_queue_id

and q.application_id = c.queue_application_id

and c.type_code = 'R'

and c.type_id = r.request_class_id

and c.type_application_id = r.application_id

and l1.lookup_code = c.include_flag and l1.lookup_type = 'INCLUDE_EXCLUDE'

and l2.lookup_code = 'R'

and l2.lookup_type = 'CP_SPECIAL_RULES'

UNION ALL

select q.application_id, q.concurrent_queue_name,q.user_concurrent_queue_name "Manager", l1.meaning "Action", l2.meaning "Type", o.oracle_username "Object"

from apps.fnd_concurrent_queue_content c, apps.fnd_concurrent_queues_vl q, apps.fnd_oracle_userid o,

apps.fnd_lookups l1, apps.fnd_lookups l2

where q.concurrent_queue_id = c.concurrent_queue_id

and q.application_id = c.queue_application_id

and c.type_code = 'O'

and c.type_id = o.oracle_id

and l1.lookup_code = c.include_flag and l1.lookup_type = 'INCLUDE_EXCLUDE'

and l2.lookup_code = 'O'

and l2.lookup_type = 'CP_SPECIAL_RULES'

UNION ALL

select q.application_id, q.concurrent_queue_name,q.user_concurrent_queue_name "Manager", l1.meaning "Action", l2.meaning "Type", u.user_name "Object"

from apps.fnd_concurrent_queue_content c, apps.fnd_concurrent_queues_vl q, apps.FND_OAM_FNDUSER_VL u,

apps.fnd_lookups l1, apps.fnd_lookups l2

where q.concurrent_queue_id = c.concurrent_queue_id

and q.application_id = c.queue_application_id

and c.type_code = 'U'

and c.type_id = u.user_id

and l1.lookup_code = c.include_flag and l1.lookup_type = 'INCLUDE_EXCLUDE'

and l2.lookup_code = 'U'

and l2.lookup_type = 'CP_SPECIAL_RULES'

UNION ALL

select q.application_id, q.concurrent_queue_name,q.user_concurrent_queue_name "Manager", l1.meaning "Action", l2.meaning "Type", to_char(c.type_id) "Object"

from apps.fnd_concurrent_queue_content c, apps.fnd_concurrent_queues_vl q,

apps.fnd_lookups l1, apps.fnd_lookups l2

where q.concurrent_queue_id = c.concurrent_queue_id

and q.application_id = c.queue_application_id

and c.type_code not in ('C','P','O','R', 'U')

and l1.lookup_code = c.include_flag and l1.lookup_type = 'INCLUDE_EXCLUDE'

and l2.lookup_code = c.type_code and l2.lookup_type = 'CP_SPECIAL_RULES';

　

To check the shift/max/min for All the concurrent Manager

　

select fcq.application_id,

fcq.concurrent_queue_name,

fcq.user_concurrent_queue_name,

ftp.application_id,

ftp.concurrent_time_period_name,

fa.application_short_name,

ftp.description,

fcqs.min_processes,

fcqs.max_processes,

fcqs.sleep_seconds,

fcqs.service_parameters

from apps.fnd_concurrent_queues_vl fcq,

apps.fnd_concurrent_queue_size fcqs,

apps.fnd_concurrent_time_periods ftp,

apps.fnd_application fa

where fcq.application_id = fcqs.queue_application_id

and fcq.concurrent_queue_id = fcqs.concurrent_queue_id

and fcqs.period_application_id = ftp.application_id

and fcqs.concurrent_time_period_id = ftp.concurrent_time_period_id

and ftp.application_id = fa.application_id;

　

To check all the values of Concurrent Manager related Site level profiles and there lookup

　

SELECT fpo.profile_option_name,

fpo.profile_option_id,

fpov.profile_option_value,

fpov.level_id,

fa.application_short_name,

fpo.user_profile_option_name,

fpo.sql_validation,

fpo.description

FROM apps.FND_PROFILE_OPTIONS_VL fpo,

apps.FND_PROFILE_OPTION_VALUES fpov,

apps.fnd_application fa

where fpo.application_id = 0

and fpo.site_enabled_flag = 'Y'

and (fpo.profile_option_name like 'CONC_%'

or fpo.profile_option_name like 'FS_%'

or fpo.profile_option_name like 'PRINTER%'

or fpo.profile_option_name in ('EDITOR_CHAR', 'FNDCPVWR_FONT_SIZE', 'MAX_PAGE_LENGTH', 'APPLWRK'))

and fpo.profile_option_id = fpov.profile_option_id

and fpo.application_id = fpov.application_id

and fpo.application_id = fa.application_id

and fpov.level_id = 10001;

　

To check the status all the manager in the system

　

select q.user_concurrent_queue_name service_name,

a.application_name srvc_app_name,

a.application_short_name srvc_app_short_name,

q.concurrent_queue_name service_short_name,

decode( ( select count(*)

from apps.fnd_concurrent_processes fcp1

where fcp1.concurrent_queue_id = q.concurrent_queue_id

and fcp1.queue_application_id = q.application_id

and ( fcp1.process_status_code in ('C','M')

or ( fcp1.process_status_code in ('A', 'D', 'T')

and exists (select 1

from gv$session

where fcp1.session_id = audsid )

)

)

)/*actual_processes */, 0, decode(q.max_processes, 0,'NOT_STARTED', 'DOWN'),

q.max_processes, 'UP', 'WARNING' ) service_status,

q.max_processes target_processes,

(select count(*)

from apps.fnd_concurrent_processes fcp2

where fcp2.concurrent_queue_id = q.concurrent_queue_id

and fcp2.queue_application_id = q.application_id

and ( fcp2.process_status_code in ('C','M') /* Connecting or Migrating */

or ( fcp2.process_status_code in ('A', 'D', 'T')

and exists (select 1 from gv$session

where fcp2.session_id = audsid)

)

)

) actual_processes,

" message, s.service_handle srvc_handle

from apps.fnd_concurrent_queues_vl q, apps.fnd_application_vl a,

apps.fnd_cp_services s

where q.application_id = a.application_id

and s.service_id = q.manager_type

UNION

/* Need to cover the case where a manager has no rows in FND_CONCURRENT_PROCESSES. Outer joins won't cut it. */

select q.user_concurrent_queue_name service_name,

a.application_name srvc_app_name,

a.application_short_name srvc_app_short_name,

q.concurrent_queue_name srvc_short_name,

decode( q.max_processes, 0, 'NOT_STARTED', 'DOWN') service_status,

q.max_processes target_processes,

0 actual_processes,

" message, s.service_handle srvc_handle

from apps.fnd_concurrent_queues_vl q, apps.fnd_application_vl a,

apps.fnd_cp_services s

where q.application_id = a.application_id

and s.service_id = q.manager_type

and not exists (select 1

from apps.fnd_concurrent_processes p

where process_status_code in ('C','M','A','D','T')

and q.concurrent_queue_id = p.concurrent_queue_id

and q.application_id = p.queue_application_id);

　

To check All the running jobs with DB session details on the current DB node

　

set lines 200

set pages 200

column PHASE heading 'Phase' format A8

column STATUS heading 'Status' format A8

column PROGRAM heading 'Program Name' format A25

column REQUESTOR heading 'Requestor' format A9

column START_TIME heading 'Start Time' format A15

column RUN_TIME justify left heading 'Runtime(m)' format 9999.99

column OSPID heading 'OSPID' format a5

column SID heading 'DBSID' format 99999

spool crrunning.lst

select fcrv.request_id REQUEST,

decode(fcrv.phase_code,'P','Pending','R','Running','I','Inactive','Completed')PHASE,

decode(fcrv.status_code, 'A','Waiting', 'B','Resuming', 'C','Normal', 'F','Scheduled',

'G','Warning', 'H','On Hold', 'I','Normal', 'M','No Manager',

'Q','Standby', 'R','Normal', 'S','Suspended', 'T','Terminating',

'U','Disabled', 'W','Paused', 'X','Terminated',

'Z','Waiting',fcrv.status_code)STATUS,

substr(fcrv.program,1,25)PROGRAM,

substr(fcrv.requestor,1,9)REQUESTOR,

to_char(fcrv.actual_start_date,'MM/DD/RR HH24:MI')START_TIME,
round(((sysdate – fcrv.actual_start_date)*1440),2)RUN_TIME,



substr(fcr.oracle_process_id,1,7)OSPID,

vs.sid SID

–substr(fcr.os_process_id,1,7)OS_PID

from apps.fnd_conc_req_summary_v fcrv,

apps.fnd_concurrent_requests fcr,

v$session vs,

v$process vp

where fcrv.phase_code = 'R'

and fcrv.request_id = fcr.request_id

and fcr.oracle_process_id = vp.spid

and vs.paddr = vp.addr

order by PHASE, STATUS, REQUEST desc

/

　

To find the Trace file for particular concurrent request

　

column traceid format a8

column tracename format a80

column user_concurrent_program_name format a40

column execname format a15

column enable_trace format a12

set lines 80

set pages 22

set head offSELECT '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 apps.fnd_concurrent_requests req, v$session ses, v$process proc,

v$parameter dest, v$parameter dbnm, apps.fnd_concurrent_programs_vl prog,

apps.fnd_executables execname

where req.request_id = &request

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;

　



　

To find the overall Request set activity and its various request completion time

　

set linesize 300

col "Program Name" format a50

col Elapsed format 9999.99

col "Process ID" format a10

col REQUEST_DATE format a15

col ACTUAL_START_DATE format a15

col REQUEST format 999999999

col PARENT format 999999999

col argument_text format a50SELECT /*+ ORDERED USE_NL(x fcr fcp fcptl)*/

fcr.request_id "REQUEST", fcr.parent_request_id "PARENT",

fcr.oracle_process_id "Process ID",

fcptl.user_concurrent_program_name "Program Name",

fcr.argument_text,

DECODE(fcr.phase_code

,'X', 'Terminated'

,'E', 'Error'

,'C','Completed'

,'P','Pending'

,'R','Running'

,phase_code) "Phase",

DECODE(fcr.status_code

,'X','Terminated'

,'C','Normal'

,'D','Cancelled'

,'E','Error'

,'G','Warning'

,'Q','Scheduled'

,'R','Normal'

,'W','Paused'

,'Not Sure') "Status",

–fcr.phase_code,

–fcr.status_code,

fcr.request_date,

fcr.actual_start_date,

fcr.actual_completion_date,

(fcr.actual_completion_date – fcr.actual_start_date)*1440 "Elapsed"

FROM (SELECT /*+ index (fcr1 FND_CONCURRENT_REQUESTS_N3) */

fcr1.request_id

FROM fnd_concurrent_requests fcr1

WHERE 1=1

START WITH fcr1.request_id = &request_id

–CONNECT BY PRIOR fcr1.parent_request_id = fcr1.request_id) x,

CONNECT BY PRIOR fcr1.request_id = fcr1.parent_request_id) x,

fnd_concurrent_requests fcr,

fnd_concurrent_programs fcp,

fnd_concurrent_programs_tl fcptl

WHERE fcr.request_id = x.request_id

AND fcr.concurrent_program_id = fcp.concurrent_program_id

AND fcr.program_application_id = fcp.application_id

AND fcp.application_id = fcptl.application_id

AND fcp.concurrent_program_id = fcptl.concurrent_program_id

AND fcptl.LANGUAGE = 'US'

ORDER BY 1

　

To find the sid from the request id

　

select s.inst_id, fcr.request_id, fv.requestor, fv.Program cmgr_job,

p.PID,

p.SERIAL#,

p.USERNAME p_user,

p.SPID,

to_char(s.logon_time,'DD-MON-YY HH24:MI:SS') Logon_Time,

s.program,

s.command,

s.sid,

s.serial#,

s.username,

s.process,

s.machine,

s.action,

s.module

from apps.fnd_concurrent_requests fcr,

apps.FND_CONC_REQ_SUMMARY_V fv,

gv$session s,

gv$process p

where fcr.request_id = &request_id

and p.SPID = fcr.oracle_process_id

and s.process = fcr.OS_PROCESS_ID

and s.inst_id = p.inst_id

and p.addr = s.paddr

and fv.request_id = fcr.request_id

;

　

To find all the things in detail from the particular request id

　



　

set pages 100;

col request_class_application_id format 99999999 heading 'REQUEST_CLASS|APP_ID'

col os_process_id format a8

col application_short_name format a5

col requested_start_date format a18 heading 'REQUEST DATE'

col actual_start_date format a18 heading 'START DATE'

col actual_completion_date format a18 heading 'COMPLETE DATE'

—

select r.REQUEST_ID

,u.user_name

, decode(r.PHASE_CODE,'C', 'Complete','P', 'Pending', 'R', 'Running', r.PHASE_CODE) phase

,decode(r.STATUS_CODE, 'C', 'Normal','I' ,'Normal','R' ,'Normal','Q', 'Standby', 'E' ,'Error', 'X' ,'Terminated','W', 'Paused' , r.STATUS_CODE) status

,r.PRIORITY

,decode(r.HOLD_FLAG,'N','"NOT on hold"','Y','"ON HOLD"',r.HOLD_FLAG) Hold_flag

, p.application_short_name

,f.user_concurrent_program_name,f.concurrent_program_name,e.EXECUTABLE_NAME,e.EXECUTION_FILE_NAME,

e.EXECUTION_METHOD_CODE,e.EXECUTION_FILE_PATH

, v.RESPONSIBILITY_name

, r.REQUEST_CLASS_APPLICATION_ID

, q.request_class_name

, r.PARENT_REQUEST_ID

,to_char(r.REQUESTED_START_DATE,'DD-MON-YYYY HH:MI') requested_start_date

,to_char(r.ACTUAL_START_DATE,'DD-MON-YYYY HH:MI') actual_start_date

,decode(r.ACTUAL_COMPLETION_DATE,null,'"Still Running"',to_char(r.ACTUAL_COMPLETION_DATE,'DD-MON-YYYY HH:MI')) actual_completion_date

,to_char(decode(r.ACTUAL_COMPLETION_DATE,null,SYSDATE,r.ACTUAL_COMPLETION_DATE)-decode(r.ACTUAL_START_DATE,null,SYSDATE,r.ACTUAL_START_DATE),'MI') "Time Running"

,r.CPU_SECONDS

,r.LOGICAL_IOS

,r.PHYSICAL_IOS

,r.ORACLE_PROCESS_ID

,r.ORACLE_SESSION_ID

,r.OS_PROCESS_ID

,r.CD_ID

,decode(r.ENABLE_TRACE,'N', '"NOT Tracing"', 'Y', '"TRACING"', r.ENABLE_TRACE) Trace

,decode(f.run_alone_flag, 'N', '"NOT Alone"', 'Y', '"RUN ALONE"', f.run_alone_flag) Alone

,r.ARGUMENT_TEXT Parameters

,r.LOGFILE_NAME

from fnd_concurrent_requests r

, fnd_user u

, fnd_application p

, fnd_concurrent_programs_vl f

,fnd_EXECUTABLEs e

, fnd_responsibility_vl v

, fnd_concurrent_request_class q

where u.user_id = r.requested_by

and p.application_id = r.PROGRAM_APPLICATION_ID

and r.CONCURRENT_PROGRAM_ID = f.CONCURRENT_PROGRAM_ID

and f.EXECUTABLE_ID=e.EXECUTABLE_ID

and v.responsibility_id = r.responsibility_id

and q.request_class_id (+)= r.concurrent_request_class_id

and r.request_id = &reqid;

　





　

sql to find Pending request in all Concurrent Manager

　

select request_id,

b.user_concurrent_queue_name

from apps.fnd_concurrent_worker_requests a,

apps.fnd_concurrent_queues_vl b

where a.phase_code = 'P'

and a.status_code = 'I'

and a.hold_flag != 'Y'

and a.requested_start_date <= sysdate

and a.concurrent_queue_id = b.concurrent_queue_id

and a.control_code is null

–and a.concurrent_queue_name != 'FNDCRM'

and a.concurrent_queue_name not in ('FNDCRM')

order by

request_id, b.user_concurrent_queue_name

/

　

How to find which manager runs your request ID

　

Column OsId Format A7

Column Oracle_Process_ID Format 99999

Column Concurrent_Queue_Name Format A20

Column Log Format A25

Column Started_At Format A20Set Head Off

Set Verify Off

Set Echo OffSelect

'The ' || Concurrent_Queue_Name ||

' concurrent manager ran your request from',

to_char(Actual_Start_date, ' MON-DD-YY HH:MI:SS AM') || ' – to – ' ||

to_char(Actual_COMPLETION_date, 'MON-DD-YY HH:MI:SS AM'),

'The ' || Concurrent_Queue_Name ||

' concurrent manager log file is ' || P.Logfile_Name,

'Request log file is ' || R.Logfile_Name

From Fnd_Concurrent_Queues Q,

Fnd_Concurrent_requests R,

Fnd_Concurrent_Processes P

Where

(P.Concurrent_Queue_ID = Q.Concurrent_Queue_ID And

Queue_Application_ID = Q.Application_ID )

And R.Controlling_Manager = P.Concurrent_Process_ID

And R.Phase_Code = 'C'

And Request_ID = &Request_ID

;

　

set head on



　

To find History of the Request ID program

　



　

set linesize 250

col request_date format a15

col requested_start_date format a15

col actual_start_date format a15

col actual_completion_date format a15

col argument_text format a70

col Elapsed format 9999.99

select * from (select a.request_id, a.parent_request_id,

DECODE(a.phase_code,

'C','Completed',

'I','Inactive',

'P','Pending',

'R','Running') || ' ' ||

DECODE(a.status_code,

'A','Waiting',

'B','Resuming',

'C','Normal',

'D','Cancelled',

'E','Error',

'G','Warning',

'H','On Hold',

'I',' Normal',

'M','No Manager',

'P','Scheduled',

'Q','Standby',

'R',' Normal',

'S','Suspended',

'T','Terminating',

'U','Disabled',

'W','Paused',

'X','Terminated',

'Z',' Waiting') "PHASE_STATUS ", a.request_date, a.requested_start_date, a.actual_start_date, a.actual_completion_date ,

(nvl(actual_completion_date,sysdate) – actual_start_date)*1440 "Elapsed", a.argument_text ,a.USER_CONCURRENT_PROGRAM_NAME

from apps.FND_CONC_REQ_SUMMARY_V a where a.concurrent_program_id=(select concurrent_program_id from fnd_concurrent_requests where request_id=&1)

—(nvl(actual_completion_date,sysdate) – actual_start_date)*1440 > 10

order by a.request_id desc) where rownum < 100;

　

All the concurrent program history in specfic time window

　



　

ttitle off

　

set linesize 180

set pagesize 60

set newpage 0

set pause off

set termout on

ttitle 'CM Analysis Report' skip1

　

col conc_que format a15 heading "Conc Queue"

col user_name format a12 heading "Requestor"

col reqid format 99999999 heading "Req ID"

col sdate format a9 heading "Date"

col astart format a8 heading "ActSt|Time"

col acomp format a8 heading "ActEnd|Time"

col rtime format 99,999 heading "ExTme|(Sec)"

col wtime format 99,999 heading "WtTme|(Sec)"

col pname1 format a40 heading "Short|Name"

col pname2 format a65 heading "Prog Name"

col args format a25 heading "Arguments"

select

trunc(actual_start_date) sdate,

request_id reqid,

user_name,

to_char(actual_start_date,'HH24:MI:SS') astart,

to_char(actual_completion_date,'HH24:MI:SS') acomp,

((actual_start_date – requested_start_date)*60*60*24) wtime,

((actual_completion_date – actual_start_date)*60*60*24) rtime,

que.concurrent_queue_name conc_que,

prog.user_concurrent_program_name pname2,

req.argument_text args

from

applsys.fnd_concurrent_queues que,

applsys.fnd_user usr,

applsys.fnd_concurrent_programs prog,

applsys.fnd_concurrent_requests req,

applsys.fnd_concurrent_processes proc

where

(actual_start_date between to_date('&start_date', 'DD-MON-YYYY HH24:MI:SS')

and to_date('&end_date', 'DD-MON-YYYY HH24:MI:SS')

or

actual_completion_date between to_date('&start_dte', 'DD-MON-YYYY HH24:MI:SS')

and to_date('&end_date', 'DD-MON-YYYY HH24:MI:SS'))

and

user_concurrent_program_name like '&program_name%'

and

que.application_id= proc.queue_application_id

and

que.concurrent_queue_id = proc.concurrent_queue_id

and

req.controlling_manager= proc.concurrent_process_id

and

usr.user_id = req.requested_by

and

prog.concurrent_program_id = req.concurrent_program_id

and

prog.application_id = req.program_application_id

and prog.concurrent_program_name not in

('ACTIVATE','ABORT','DEACTIVATE','VERIFY')

order by

actual_start_date;



　

Query to find Running request in Concurrent Manager

　

set pages 58

set linesize 79Column Reqst Format 999999

Column Requestor Format A10

Column Orcl Format A7

Column Program Format A10

Column Started Format A14

Column Manager Format A11

Column LN Format a10Column Reqst HEADING 'Request|ID '

Column Requestor HEADING 'Requestor'

Column Orcl HEADING 'Oracle|Name'

Column Started HEADING 'Started at'

Column MANAGER HEADING 'Controlling|Manager'

Column LN HEADING 'Logfile|name'

Column Program HEADING 'Program'

　

select Request_Id Reqst, User_Name Requestor, Oracle_Username Orcl,

Fcr.Logfile_Name LN,

Concurrent_Queue_Name Manager,

Concurrent_Program_Name Program,

To_Char(Actual_Start_Date, 'Mm-Dd-Yy Hh24:Mi') Started,

Run_Alone_Flag, Single_Thread_Flag

From Fnd_Concurrent_Requests Fcr, Fnd_Concurrent_Programs Fcp,

Fnd_Oracle_Userid O, Fnd_Concurrent_Processes P,

Fnd_Concurrent_Queues Q, Fnd_User

Where

Controlling_Manager = Concurrent_Process_ID

And ( P.Concurrent_Queue_ID = Q.Concurrent_Queue_ID AND

P.Queue_Application_ID = Q.Application_ID )

And O.Oracle_Id = Fcr.Oracle_Id

And ( Fcr.Program_Application_Id = Fcp.Application_Id

And Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id )

And Requested_By = User_Id

And Phase_Code = 'R' and Status_Code = 'R'

Order By Actual_Start_Date, Request_Id

/

　





　



　

Query to find Terminating Requests in Concurrent Manager

　

set pages 58

set linesize 79Column Reqst Format 999999

Column Requestor Format A10

Column Orcl Format A7

Column Program Format A10

Column Started Format A14

Column Manager Format A11

Column LN Format a10Column Reqst HEADING 'Request|ID '

Column Requestor HEADING 'Requestor'

Column Orcl HEADING 'Oracle|Name'

Column Started HEADING 'Started at'

Column MANAGER HEADING 'Controlling|Manager'

Column LN HEADING 'Logfile|name'

Column Program HEADING 'Program'

　

Select Request_Id Reqst, User_Name Requestor, Oracle_Username Orcl,

Fcr.Logfile_Name LN,

Concurrent_Queue_Name Manager,

Concurrent_Program_Name Program,

To_Char(Actual_Start_Date, 'Mm-Dd-Yy Hh24:Mi') Started,

Run_Alone_Flag, Single_Thread_Flag

From Fnd_Concurrent_Requests Fcr, Fnd_Concurrent_Programs Fcp,

Fnd_Oracle_Userid O, Fnd_Concurrent_Processes P,

Fnd_Concurrent_Queues Q, Fnd_User

Where

Controlling_Manager = Concurrent_Process_ID

And ( P.Concurrent_Queue_ID = Q.Concurrent_Queue_ID AND

P.Queue_Application_ID = Q.Application_ID )

And O.Oracle_Id = Fcr.Oracle_Id

And ( Fcr.Program_Application_Id = Fcp.Application_Id

And Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id )

And Requested_By = User_Id

And Phase_Code = 'R' and Status_Code = 'T'

Order By Actual_Start_Date, Request_Id

/



　

Query to find Paused Requests in Concurrent Manager

　

set pages 58

set linesize 79Column Reqst Format 999999

Column Requestor Format A10

Column Orcl Format A7

Column Program Format A10

Column Started Format A14

Column Manager Format A11

Column LN Format a10Column Reqst HEADING 'Request|ID '

Column Requestor HEADING 'Requestor'

Column Orcl HEADING 'Oracle|Name'

Column Started HEADING 'Started at'

Column MANAGER HEADING 'Controlling|Manager'

Column LN HEADING 'Logfile|name'

Column Program HEADING 'Program'

　

Select Request_Id Reqst, User_Name Requestor, Oracle_Username Orcl,

Fcr.Logfile_Name LN,

Concurrent_Program_Name Program,

To_Char(Actual_Start_Date, 'Mm-Dd-Yy Hh24:Mi') Started,

Run_Alone_Flag, Single_Thread_Flag

From Fnd_Concurrent_Requests Fcr, Fnd_Concurrent_Programs Fcp,

Fnd_Oracle_Userid O, Fnd_User

Where Status_Code = 'W'

And Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id

And Fcr.Program_Application_Id = Fcp.Application_Id

And Requested_By = User_Id

And O.Oracle_Id = Fcr.Oracle_Id

Order By Actual_Start_Date, Request_Id

/



　



　

Check the log and outfile created from certain time period

　

select outfile_node_name,outfile_name, logfile_node_name,logfile_name

　

from fnd_concurrent_requests

　

where phase_code = 'C' and

　

actual_completion_date < SYSDATE – &age;



　

Run the following query to check whether any specialization rule defined for any concurrent manager that includes/excludes the concurrent program in question. Query returns 'no rows selected' when there are no Include/Exclude specialization rules of Program type for the given concurrent program.

　

select 'Concurrent program '||fcp.concurrent_program_name||' is ' ||decode(fcqc.include_flag,'I','included in ','E','excluded from ')||fcqv.user_concurrent_queue_name specialization_rule_details from fnd_concurrent_queues_vl fcqv,fnd_concurrent_queue_content fcqc,fnd_concurrent_programs fcp where fcqv.concurrent_queue_id=fcqc.concurrent_queue_id and fcqc.type_id=fcp.concurrent_program_id and fcp.concurrent_program_name='<PROGRAM_SHORT_NAME>';

　

Query to find the concurrent program id from concurrent request

　

select concurrent_program_id from fnd_concurrent_requests where request_id=&1;

　

Last 100 execution detail of concurrent program

　

set linesize 250

　

col request_date format a15

　

col requested_start_date format a15

　

col actual_start_date format a15

　

col actual_completion_date format a15

　

col argument_text format a70

　

col Elapsed format 9999.99

　

select * from (select a.request_id, a.parent_request_id,

　

DECODE(a.phase_code,

　

'C','Completed',

　

'I','Inactive',

　

'P','Pending',

　

'R','Running') || ' ' ||

　

DECODE(a.status_code,

　

'A','Waiting',



　

'B','Resuming',

　

'C','Normal',

　

'D','Cancelled',

　

'E','Error',

　

'G','Warning',

　

'H','On Hold',

　

'I',' Normal',

　

'M','No Manager',

　

'P','Scheduled',

　

'Q','Standby',

　

'R',' Normal',

　

'S','Suspended',

　

'T','Terminating',

　

'U','Disabled',

　

'W','Paused',

　

'X','Terminated',

　

'Z',' Waiting') "PHASE_STATUS ", a.request_date, a.requested_start_date, a.actual_start_date, a.actual_completion_date ,

　

(nvl(actual_completion_date,sysdate) – actual_start_date)*1440 "Elapsed", a.argument_text ,a.USER_CONCURRENT_PROGRAM_NAME

　

from apps.FND_CONC_REQ_SUMMARY_V a where a.concurrent_program_id=(select concurrent_program_id from fnd_concurrent_requests where request_id=&1)

　

order by a.request_id desc) where rownum < 100;



　

To find the logfile /outfile/node name for the particular request

　

SELECT 'LOGFILE_NAME=' || logfile_name

　

FROM fnd_concurrent_requests

　

WHERE request_id = &req

　

/

　

SELECT 'LOGFILE_NODE_NAME=' || logfile_node_name

　

FROM fnd_concurrent_requests

　

WHERE request_id = &req

　

/

　

SELECT 'OUTFILE_NAME=' || outfile_name

　

FROM fnd_concurrent_requests

　

WHERE request_id = &req

　

/

　

SELECT 'OUTFILE_NODE_NAME=' || outfile_node_name

　

FROM fnd_concurrent_requests

　

WHERE request_id = &req



　

Check ICM is running on what node

　

select a.concurrent_queue_name,a.target_node

, substr(b.os_process_id,0,10) "OS Proc"

, b.oracle_process_id "Oracle ID"

, b.process_status_code

from apps.fnd_concurrent_queues a

, apps.fnd_concurrent_processes b

where a.concurrent_queue_id=b.concurrent_queue_id

and a.concurrent_queue_name='FNDICM'

and b.process_status_code='A'

order by b.process_status_code

/

　

Check OS process ID/Target node for CM

　

Select User_Concurrent_Queue_Name Manager, Q.Target_Node Node, Os_Process_ID

from apps.fnd_concurrent_queues_vl Q,apps.Fnd_Concurrent_Processes P

where Q.Application_Id = Queue_Application_ID

And (Q.Concurrent_Queue_ID = P.Concurrent_Queue_ID);

　

To know the correct status of the Concurrent request from the backend

　

The view FND_AMP_REQUESTS_V shows the proper phase and status

select request_id, phase, status

from fnd_amp_requests_v;

　

Check the completion text for the concurrent request

　

select COMPLETION_TEXT from apps.fnd_concurrent_requests where REQUEST_ID=&n;

　

To Check Actual and Target Processes for Internal Manager

　

select MAX_PROCESSES,RUNNING_PROCESSES

　

from FND_CONCURRENT_QUEUES

　

where CONCURRENT_QUEUE_NAME='FNDICM';



　

To check the Actual and Target Processes for Standard Manager

　

select MAX_PROCESSES,RUNNING_PROCESSES

　

from FND_CONCURRENT_QUEUES

　

where CONCURRENT_QUEUE_NAME='STANDARD';



　

To check the invalid objects related to FND

　

select substr(owner,1, 12) owner, substr(object_type,1,12) type,

　

substr(status,1,8) status, substr(object_name, 1, 25) name

　

from dba_objects

　

where object_name like 'FND_%'

　

and status='INVALID';



　

Related Articles

　

Oracle concurrent Manager

　

Concurrent Request Phase and status

　

Concurrent Manager secrets Part -I

　

Core files in Applications Concurrent manager

　

Priority for concurrent Program