Respo assigned to a user

SELECT fu.user_name                "User Name",

       frt.responsibility_name     "Responsibility Name",

       furg.start_date             "Start Date",

       furg.end_date               "End Date",     

       fr.responsibility_key       "Responsibility Key",

       fa.application_short_name   "Application Short Name"

  FROM fnd_user_resp_groups_direct        furg,

       applsys.fnd_user                   fu,

       applsys.fnd_responsibility_tl      frt,

       applsys.fnd_responsibility         fr,

       applsys.fnd_application_tl         fat,

       applsys.fnd_application            fa

WHERE furg.user_id             =  fu.user_id

   AND furg.responsibility_id   =  frt.responsibility_id

   AND fr.responsibility_id     =  frt.responsibility_id

   AND fa.application_id        =  fat.application_id

   AND fr.application_id        =  fat.application_id

   AND frt.language             =  USERENV('LANG')

   AND UPPER(fu.user_name)      =  UPPER('username')  -- <change it>

   -- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))

ORDER BY frt.responsibility_name;

Query to check Sysadmin respo

SELECT fu.*

FROM fnd_user_resp_groups_direct furgd, fnd_responsibility_vl frvl, fnd_user fu

WHERE furgd.responsibility_id = frvl.responsibility_id

AND fu.user_id = furgd.user_id

AND(to_char(furgd.end_date) is null

OR furgd.end_date > sysdate)

AND frvl.end_date is null

AND frvl.responsibility_name = 'System Administrator';

Schemas register under ERP "ALL"

select distinct upper(oracle_username) sname

from fnd_oracle_userid a,

fnd_product_installations b

where a.oracle_id = b.oracle_id

order by sname;

Request completed with error 

SELECT f.user_name, r.request_id, prg.concurrent_program_name Module, p.USER_CONCURRENT_PROGRAM_NAME,
decode(r.phase_code,
'C' , 'Completed',
'I' , 'Inactive',
'P' , 'Pending',
'R' , 'Running',r.phase_code) Phase_Desc,
  decode( r.status_code,
'A' , 'Waiting'
,'B' , 'Resuming'
,'C' , 'Normal'
,'D' , 'Cancelled'
,'E' , 'Error'
,'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',r.status_code)
 Status_Desc, r.actual_start_date, r.actual_completion_date
FROM fnd_concurrent_requests r,fnd_concurrent_programs_tl p,fnd_concurrent_programs prg, fnd_user f
WHERE r.program_APPLICATION_ID=p.APPLICATION_ID and
r.CONCURRENT_PROGRAM_ID =p.CONCURRENT_PROGRAM_ID and
r.requested_by = f.user_id and
r.program_APPLICATION_ID=prg.APPLICATION_ID and
r.CONCURRENT_PROGRAM_ID =prg.CONCURRENT_PROGRAM_ID
and r.status_code in ('E','G')
and trunc(r.actual_completion_date) = trunc(sysdate - 1)
-- and f.user_name in ('SYSADMIN','TKMINFYADMIN','TKMP2POUTBOUND','TKMINTERFACE');

Concurrent Manager Status 

select decode(CONCURRENT_QUEUE_NAME,'FNDICM','Internal Manager','FNDCRM','Conflict Resolution Manager','AMSDMIN','Marketing Data Mining Manager','C_AQCT_SVC','C AQCART Service','FFTM','FastFormula Transaction Manager','FNDCPOPP','Output Post Processor','FNDSCH','Scheduler/Prereleaser Manager','FNDSM_AQHERP','Service Manager: AQHERP','FTE_TXN_MANAGER','Transportation Manager','IEU_SH_CS','Session History Cleanup','IEU_WL_CS','UWQ Worklist Items Release for Crashed session','INVMGR','Inventory Manager','INVTMRPM','INV Remote Procedure Manager','OAMCOLMGR','OAM Metrics Collection Manager','PASMGR','PA Streamline Manager','PODAMGR','PO Document Approval Manager','RCVOLTM','Receiving Transaction Manager','STANDARD','Standard Manager','WFALSNRSVC','Workflow Agent Listener Service','WFMLRSVC','Workflow Mailer Service','WFWSSVC','Workflow Document Web Services Service','WMSTAMGR','WMS Task Archiving Manager','XDP_APPL_SVC','SFM Application Monitoring Service','XDP_CTRL_SVC','SFM Controller Service','XDP_Q_EVENT_SVC','SFM Event Manager Queue Service','XDP_Q_FA_SVC','SFM Fulfillment Actions Queue Service','XDP_Q_FE_READY_SVC','SFM Fulfillment Element Ready Queue Service','XDP_Q_IN_MSG_SVC','SFM Inbound Messages Queue Service','XDP_Q_ORDER_SVC','SFM Order Queue Service','XDP_Q_TIMER_SVC','SFM Timer Queue Service','XDP_Q_WI_SVC','SFM Work Item Queue Service','XDP_SMIT_SVC','SFM SM Interface Test Service') as "Concurrent Manager's Name", max_processes as "TARGET Processes", running_processes as "ACTUAL Processes" from apps.fnd_concurrent_queues where CONCURRENT_QUEUE_NAME in ('FNDICM','FNDCRM','AMSDMIN','C_AQCT_SVC','FFTM','FNDCPOPP','FNDSCH','FNDSM_AQHERP','FTE_TXN_MANAGER','IEU_SH_CS','IEU_WL_CS','INVMGR','INVTMRPM','OAMCOLMGR','PASMGR','PODAMGR','RCVOLTM','STANDARD','WFALSNRSVC','WFMLRSVC','WFWSSVC','WMSTAMGR','XDP_APPL_SVC','XDP_CTRL_SVC','XDP_Q_EVENT_SVC','XDP_Q_FA_SVC','XDP_Q_FE_READY_SVC','XDP_Q_IN_MSG_SVC','XDP_Q_ORDER_SVC','XDP_Q_TIMER_SVC','XDP_Q_WI_SVC','XDP_SMIT_SVC');

Concurrent Manager log location

select fcp.node_name,
TO_CHAR(fcp.last_update_date, 'DD-MON-RR HH24:MI:SS'),
fcp.logfile_name
FROM fnd_concurrent_processes fcp, fnd_concurrent_queues fcq
WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND fcp.queue_application_id = fcq.application_id
AND fcq.concurrent_queue_name = 'FNDCPOPP'
order by 2 desc; 

Concurrent Queue Status

SELECT a.concurrent_queue_name, SUBSTR(b.os_process_id,0,10) "OS Proc", b.oracle_process_id "Oracle ID",  b.process_status_code
FROM fnd_concurrent_queues a, fnd_concurrent_processes b
WHERE a.concurrent_queue_id=b.concurrent_queue_id
AND b.process_status_code='A'
ORDER BY b.process_status_code;

Manger of a request ID

select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,

fnd_concurrent_queues_vl b, fnd_concurrent_requests c

where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID

and a.CONCURRENT_PROCESS_ID = c.controlling_manager

and c.request_id = XXXXX;

Request to check duplicate

SELECT fcr.request_id

       ,fcr.root_request_id "Schedule Request ID"

       ,fu.user_name "Submitted By"

       ,fct.user_concurrent_program_name

       ,fcr.argument_text

       ,fcr.RESUBMIT_INTERVAL

       ,fcr.RESUBMIT_INTERVAL_UNIT_CODE

FROM apps.fnd_concurrent_requests fcr

     ,apps.FND_concurrent_programs_tl fct

     ,apps.fnd_user fu

WHERE fcr.concurrent_program_id = fct.concurrent_program_id

and fcr.phase_code    <>'C'

and fcr.requested_by = fu.user_id

AND fcr.root_request_id IN

  (SELECT root_request_id

  FROM apps.fnd_concurrent_requests

  WHERE phase_code    <>'C'

  AND root_request_id IS NOT NULL

  GROUP BY root_request_id

  HAVING COUNT(*)> 1

  )

  order by fcr.root_request_id asc;

Query to find log file count

SELECT 

         fcr.logfile_node_name,count(1)

    FROM apps.fnd_concurrent_requests fcr,

         apps.fnd_concurrent_programs fcp,

         apps.fnd_concurrent_programs_tl fcpt,

         apps.fnd_user fu,

         apps.fnd_responsibility_tl fr

   WHERE 1 = 1

     AND fcr.actual_start_date >= to_date('2/16/2017 10:10','MM/DD/RRRR hh24:mi')

     AND fcr.concurrent_program_id = fcp.concurrent_program_id

     AND fcp.concurrent_program_id = fcpt.concurrent_program_id

     AND fcr.program_application_id = fcp.application_id

     AND fcp.application_id = fcpt.application_id

     AND fcr.requested_by = fu.user_id

     AND fcpt.LANGUAGE = 'US'

     AND fcr.responsibility_id = fr.responsibility_id

     AND fr.LANGUAGE = 'US'

group by fcr.logfile_node_name;

Finding the running node of a concurrent request

SELECT outfile_node_name,          

       a.actual_start_date,

      a.actual_completion_date ,

      a.*

   FROM fnd_concurrent_requests a

WHERE concurrent_program_id =

        (select concurrent_program_id from fnd_concurrent_programs_tl where       user_concurrent_program_name like 'Programme Name' )

 --and argument1 = 'CONCUR' and argument2 = 'GET'

order by REQUESTED_START_DATE desc;

 EBS monitoring script

• Run Away Process
• Frmweb process