To Find Latest Query Executing/Running in Database for Specific Concurrent Request

Find Latest Query Executing/Running in Database for Specific Concurrent Request

If one concurrent request is running  and you want to find currently which query of that request is running in session then you can run below query in toad or sql developer by putting request ifd of that concurrent request-

select

   to_char(inst_id) rac_inst_id,

   to_char(sid) sid,

   to_char(serial#) serial#,

   sql_text

from

   apps.fnd_concurrent_requests fcr,

   gv$session vs,

   v$sqltext vq

where

   vs.process = fcr.os_process_id

   and vs.sql_address = vq.address

   and fcr.status_code = 'R'

   and fcr.phase_code = 'R'

   and request_id in

  (select fcr.request_id

  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.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 Serial#;

This will be very useful for debugging purposes means we can derive what part of code is taking time or where program got stuck and for other purposes.

SQL Query to Find Scheduled Concurrent Programs in System

SQL Query to Find Scheduled Concurrent Programs in System

SELECT fcr.request_id
     , fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL) conc_prog
     , fu.user_name requestor
     , fu.description requested_by
     , fu.email_address
     , frt.responsibility_name requested_by_resp
     , trim(fl.meaning) status
     , fcr.phase_code
     , fcr.status_code
     , fcr.argument_text "PARAMETERS"
      , TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested
     , TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start   
     , TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time
     , DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold
     , CASE
          WHEN fcr.hold_flag = 'Y'
             Then Substr(
                    fu.description
                  , 0
                  , 40
                 )
       END last_update_by
     , CASE
          WHEN fcr.hold_flag = 'Y'
             THEN fcr.last_update_date
       END last_update_date
     , fcr.increment_dates
     , CASE WHEN fcrc.CLASS_INFO IS NULL THEN
        'Yes: ' || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
       ELSE
        'n/a'
       END run_once
     , CASE WHEN fcrc.class_type = 'P' THEN
        'Repeat every ' ||
        substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
        decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1),
               'N', ' minutes',
               'M', ' months',
               'H', ' hours',
               'D', ' days') ||
        decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
               'S', ' from the start of the prior run',
               'C', ' from the completion of the prior run')
       ELSE
         'n/a'
       END set_days_of_week
       , CASE WHEN fcrc.class_type = 'S' AND instr(substr(fcrc.class_info, 33),'1',1) > 0 THEN
          'Days of week: ' ||
                  decode(substr(fcrc.class_info, 33, 1), '1', 'Sun, ') ||
                  decode(substr(fcrc.class_info, 34, 1), '1', 'Mon, ') ||
                  decode(substr(fcrc.class_info, 35, 1), '1', 'Tue, ') ||
                  decode(substr(fcrc.class_info, 36, 1), '1', 'Wed, ') ||
                  decode(substr(fcrc.class_info, 37, 1), '1', 'Thu, ') ||
                  decode(substr(fcrc.class_info, 38, 1), '1', 'Fri, ') ||
                  decode(substr(fcrc.class_info, 39, 1), '1', 'Sat ')
         ELSE
           'n/a'
         end  days_of_week 
  FROM fnd_concurrent_requests fcr
     , fnd_user fu
     , fnd_concurrent_programs fcp
     , fnd_concurrent_programs_tl fcpt
     , fnd_printer_styles_tl fpst
     , fnd_conc_release_classes fcrc
     , fnd_responsibility_tl frt
     , fnd_lookups fl
 WHERE fcp.application_id = fcpt.application_id
   AND fcr.requested_by = fu.user_id
   AND fcr.concurrent_program_id = fcp.concurrent_program_id
   AND fcr.program_application_id = fcp.application_id
   AND fcr.concurrent_program_id = fcpt.concurrent_program_id
   AND fcr.responsibility_id = frt.responsibility_id
   AND fcr.print_style = fpst.printer_style_name(+)
   AND fcr.release_class_id = fcrc.release_class_id(+)
   AND fcr.status_code = fl.lookup_code
   AND fl.lookup_type = 'CP_STATUS_CODE'
   AND fcr.phase_code = 'P'
   AND 1=1
Order By Fu.Description, Fcr.Requested_Start_Date Asc;

WFLOAD – Oracle Workflow Loader utility

WFLOAD Command to download Oracle Workflow

Syntax-

WFLOAD <apps/pwd>@<connectstring> 0 Y DOWNLOAD FILE_NAME.wft item_type

Modes-

UPGRADE – Protection and Customization levels of data

UPLOAD  - Only protection level of data.Not supporting customisation

FORCE   - Force upload, protection or customization not supported

For example-

WFLOAD apps/apps@VIS 0 Y DOWNLOAD POAPPRV_V1.wft POAPPRV

WFLOAD Command to upload Oracle Workflow

Syntax-

WFLOAD <apps/pwd>@<connectstring> 0 Y {UPLOAD | UPGRADE | FORCE} FILE_NAME.wft

For Example-

WFLOAD apps/apps@VIS 0 Y UPLOAD POAPPRV_V1.wft

Seeded Workflow Files Location

You can also download seeded workflow definition files directly from below directory-

$<Application_TOP>/patch/115/import/<LANG>

For example- Account Payables workflow file can be found at-

$AP_TOP/patch/115/import/US