Oracle Apps Scripts

    Concurrent Request 

• Long Running Requests
• Find Manager of a Request
• Find Running node of a request
• Find Concurrent Requests submitted by a particular user
• Request completed with Error
• Request to check dumplicate

  Concurrent Manager 

• Concurrent Manager Status
• Concurrent Queue Status
• Concurrent Manager Log location
• Find log file count

  

 Schema and User Related   

• schemas-register-under-erp-all
• query-to-check-sysadmin-respo
• respo-assigned-to-user

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;

Query to find Concurrent Requests submitted by a particular user

SELECT
    user_concurrent_program_name,
    responsibility_name,
    request_date,
    argument_text,
    request_id,
    phase_code,
    status_code,
    logfile_name,
    outfile_name,
    output_file_type
FROM
    fnd_concurrent_requests fcr,
    fnd_concurrent_programs_tl fcp,
    fnd_responsibility_tl fr,
    fnd_user fu
WHERE
    fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
    and fcr.responsibility_id = fr.responsibility_id
    and fcr.requested_by = fu.user_id
    and user_name = upper('username')
ORDER BY REQUEST_DATE DESC;

Long Running Concurrent requests

1.) Get the list of  long running concurrent request from below queries .

Long Running concurrent request -1

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_min

        ,ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 60 * 24), 2)runtime_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 apps.fnd_concurrent_requests fcr

        ,apps.fnd_user fu

        ,apps.fnd_responsibility_tl fr

        ,apps.fnd_concurrent_programs_tl fcp

   WHERE fcr.status_code LIKE '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) > 1

ORDER BY fcr.concurrent_program_id

        ,request_id DESC;

Long Running concurrent request -1 ( more than 30 minutes )

select r.request_id "Request ID",

       round((sysdate-r.actual_start_date)*1440,0) "Runtime(Mins)",

       p.concurrent_program_name "Conc Program Short Name",

       p.user_concurrent_program_name "Concurrent Program Name",

       to_char(r.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') "Requested Start Date",

       to_char(r.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') "Actual Start Date",

       TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') "System Date",

       u.user_name,

       u.description,

       argument_text,

       s.sid,

       r.os_process_id,

       r.oracle_process_id

  from applsys.fnd_concurrent_requests r,

       apps.fnd_concurrent_programs_vl p,

       v$session s,

       applsys.fnd_user u

 where r.program_application_id = p.application_id

   and r.concurrent_program_id = p.concurrent_program_id

   and r.requested_by = u.user_id

   and r.phase_code = 'R'

   and r.status_code = 'R'

   and to_char(r.os_process_id) = s.process(+)

   and s.action(+) = 'Concurrent Request'

   and p.concurrent_class_id is null

   and round((sysdate-r.actual_start_date)*1440,0) > 30

 order by "Runtime(Mins)" desc

/

---------------------------

2.) SQL will give you all request ID’s related to Request

  -- Get the run history of the request 

SELECT f.request_id,

         pt.user_concurrent_program_name user_conc_program_name,

         f.actual_start_date start_on,

         f.actual_completion_date end_on,

            FLOOR (

                 (  (f.actual_completion_date - f.actual_start_date)

                  * 24

                  * 60

                  * 60)

               / 3600)

         || ' HOURS '

         || FLOOR (

                 (  (  (f.actual_completion_date - f.actual_start_date)

                     * 24

                     * 60

                     * 60)

                  -   FLOOR (

                           (  (f.actual_completion_date - f.actual_start_date)

                            * 24

                            * 60

                            * 60)

                         / 3600)

                    * 3600)

               / 60)

         || ' MINUTES '

         || ROUND (

               (  (  (f.actual_completion_date - f.actual_start_date)

                   * 24

                   * 60

                   * 60)

                -   FLOOR (

                         (  (f.actual_completion_date - f.actual_start_date)

                          * 24

                          * 60

                          * 60)

                       / 3600)

                  * 3600

                - (  FLOOR (

                          (  (  (f.actual_completion_date - f.actual_start_date)

                              * 24

                              * 60

                              * 60)

                           -   FLOOR (

                                    (  (  f.actual_completion_date

                                        - f.actual_start_date)

                                     * 24

                                     * 60

                                     * 60)

                                  / 3600)

                             * 3600)

                        / 60)

                   * 60)))

         || ' SECS '

            time_difference,

         p.concurrent_program_name concurrent_program_name,

         DECODE (f.phase_code,

                 'R', 'Running',

                 'C', 'Complete',

                 f.phase_code)

            Phase,

         f.status_code,

         f.Argument_text

    FROM apps.fnd_concurrent_programs p,

         apps.fnd_concurrent_programs_tl pt,

         apps.fnd_concurrent_requests f

   WHERE     f.concurrent_program_id = p.concurrent_program_id

         AND f.program_application_id = p.application_id

         AND f.concurrent_program_id = pt.concurrent_program_id

         AND f.program_application_id = pt.application_id

         AND pt.USER_CONCURRENT_PROGRAM_NAME like '%LOG%'

         AND pt.language = USERENV ('Lang')

         AND f.actual_start_date IS NOT NULL

ORDER BY f.actual_start_date DESC;

------------------------------

3.)-- Need request id to find out child requests

-- Find all child requests for a request set (OR if no children, just get details on an individual request)

-- REQUIRED VALUE - Enter the Request ID that launched the Request set being investigated

select /*+ ORDERED USE_NL(x fcr fcp fcptl)*/

       fcr.request_id    "Request ID",

       fcr.requested_by "User",

       substr(DECODE (FCR.DESCRIPTION,  NULL,

       FCPTL.USER_CONCURRENT_PROGRAM_NAME,

       FCR.DESCRIPTION||' ('||FCPTL.USER_CONCURRENT_PROGRAM_NAME||')'),1,80)"Program Name",

       (fcr.actual_completion_date - fcr.actual_start_date)*1440 "Elapsed Time",

       oracle_process_id "Trace File ID" ,

       fcr.phase_code "Phase",

       fcr.status_code "Status",

       to_char(fcr.request_date,'DD-MON-YYYY HH24:MI:SS')   "Submitted",

       (fcr.actual_start_date - fcr.request_date)*1440 "Delay",

       to_char(fcr.actual_start_date,'DD-MON-YYYY HH24:MI:SS')  "Start Time",

       to_char(fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') "End Time",

       fcr.argument_text "Parameters"

  from (select /*+ index (fcr1 FND_CONCURRENT_REQUESTS_N3) */

               fcr1.request_id

          from fnd_concurrent_requests fcr1

         where 1=1

         start with fcr1.request_id = &parent_request_id

       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;

--------------------------------

4.)  SQL Need request id to find out SQLID

SELECT a.request_id,

       d.sid,

       d.serial#,

       d.status,

       d.osuser,

       d.process

  FROM apps.fnd_concurrent_requests a,

       apps.fnd_concurrent_processes b,

       gv$process c,

       gv$session d

WHERE     a.controlling_manager = b.concurrent_process_id

       AND c.pid = b.oracle_process_id

       AND b.session_id = d.audsid

       AND a.request_id = &Request_ID

       AND a.phase_code = 'R';

    ************

 SELECT fcr.REQUEST_ID,

       fcr.PHASE_CODE,

       fcr.STATUS_CODE,

       fcr.OS_PROCESS_ID,

       vs.sid,

       fcr.USER_CONCURRENT_PROGRAM_NAME,

       vs.SQL_ID

  FROM apps.fnd_conc_requests_form_v fcr, gv$session vs

 WHERE  fcr.REQUEST_ID = '128335339'

 ---fcr.USER_CONCURRENT_PROGRAM_NAME = 'Workflow Background Process'

       AND fcr.OS_PROCESS_ID = VS.PROCESS;