Request Set - Running details

SELECT /*+ 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.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;

Query to know the Attributes(DFF Fields) Actual Column Names in Oracle tables

SELECT b.APPLICATION_TABLE_NAME,

       b.DESCRIPTIVE_FLEXFIELD_NAME,

       a.DESCRIPTIVE_FLEX_CONTEXT_CODE,

       a.APPLICATION_COLUMN_NAME,

       a.END_USER_COLUMN_NAME

  FROM FND_DESCR_FLEX_COLUMN_USAGES a,

       FND_DESCRIPTIVE_FLEXS_vl b

 WHERE   
b.APPLICATION_TABLE_NAME IN (UPPER ('<table_name>'))       
AND b.DESCRIPTIVE_FLEXFIELD_NAME = a.DESCRIPTIVE_FLEXFIELD_NAME

AND b.APPLICATION_ID = a.APPLICATION_ID

Concurrent program attached request Group - Query

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 =

       'PO Changes'

 ORDER BY 1, 2, 3, 4

Concurrent Program Parameters - include ValueSets Query

           SELECT DISTINCT fcpl.user_concurrent_program_name "Concurrent Program Name",

                fcp.concurrent_program_name "Short Name"                                 ,

                fat.application_name                                                     ,

                fl.meaning execution_method                                              ,

                fe.execution_file_name                                                   ,

                fcp.output_file_type                                                     ,

                fdfcuv.column_seq_num "Column Seq Number"                                ,

                fdfcuv.end_user_column_name "Parameter Name"                             ,

                fdfcuv.form_left_prompt "Prompt"                                         ,

                fdfcuv.enabled_flag " Enabled Flag"                                      ,

                fdfcuv.required_flag "Required Flag"                                     ,

                fdfcuv.display_flag "Display Flag"                                       ,

                fdfcuv.flex_value_set_id "Value Set Id"                                  ,

                ffvs.flex_value_set_name "Value Set Name"                                ,

                flv.meaning "Default Type"                                               ,

                fdfcuv.default_value "Default Value"

                 FROM fnd_concurrent_programs fcp ,

                fnd_concurrent_programs_tl fcpl   ,

                fnd_descr_flex_col_usage_vl fdfcuv,

                fnd_flex_value_sets ffvs          ,

                fnd_lookup_values flv             ,

                fnd_lookups fl                    ,

                fnd_executables fe                ,

                fnd_executables_tl fet            ,

                fnd_application_tl fat

                WHERE 1                     = 1

              AND fcp.concurrent_program_id = fcpl.concurrent_program_id

              AND fcp.enabled_flag          = 'Y'

              AND fdfcuv.descriptive_flexfield_name = '$SRS$.'||fcp.concurrent_program_name

              AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id

              AND flv.lookup_type(+)     = 'FLEX_DEFAULT_TYPE'

              AND flv.lookup_code(+)     = fdfcuv.default_type

              AND fcpl.LANGUAGE          = 'US'

              AND flv.LANGUAGE(+)        = 'US'

              AND fl.lookup_type         ='CP_EXECUTION_METHOD_CODE'

              AND fl.lookup_code         =fcp.execution_method_code

              AND fe.executable_id       = fcp.executable_id

              AND fe.executable_id       =fet.executable_id

              AND fet.LANGUAGE           = 'US'

              AND fat.application_id     =fcp.application_id

              AND fat.LANGUAGE           = 'US'

              and upper(fcpl.user_concurrent_program_name) like upper('Vertex Recon - AR to O Series Tax Reconciliation')

              --and upper(fe.execution_file_name) like 'PO_CHNG_PKG%'

              --and fcp.concurrent_program_name=''

              ORDER BY fdfcuv.column_seq_num;

Long Running Program and SQL Query

LONG RUNNING PROGRAM

SELECT a.request_id

,a.oracle_process_id "SPID"

,frt.responsibility_name

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

,a.description

,a.ARGUMENT_TEXT

,b.node_name

,b.db_instance

,a.logfile_name

,a.logfile_node_name

,a.outfile_name

,q.concurrent_queue_name

,a.phase_code,a.status_code, a.completion_text

, actual_start_date

, actual_completion_date

, fu.user_name

,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins

,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time

FROM APPLSYS.fnd_Concurrent_requests a2,

APPLSYS.fnd_concurrent_programs c2

WHERE c2.concurrent_program_id = c.concurrent_program_id

AND a2.concurrent_program_id = c2.concurrent_program_id

AND a2.program_application_id = c2.application_id

AND a2.phase_code || '' = 'C') avg_mins

,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours

FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b

,applsys.fnd_concurrent_queues q

,APPLSYS.fnd_concurrent_programs c

,APPLSYS.fnd_concurrent_programs_tl ctl

,apps.fnd_user fu

,apps.FND_RESPONSIBILITY_TL frt

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.phase_code = 'R'

AND a.status_code = 'R'

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 a.requested_by = fu.user_id

AND a.responsibility_id = frt.responsibility_id

ORDER BY a.actual_start_date DESC

GET THE SESSION ID FROM PREVIOUS QUERY

SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,

b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time, ROUND((SYSDATE - b.logon_time) * 1440, 2)   "Runtime (in Minutes)"  

FROM v$process a, v$session b, v$sqltext c

WHERE a.addr=b.paddr

AND b.sql_hash_value = c.hash_value

AND b.STATUS = 'ACTIVE'

AND a.spid = 'SESSION ID PUT'

ORDER BY a.spid, c.piece

PROGRAM CONSUMING TIME

SELECT /*+ rule */

       rq.parent_request_id                   "Parent Req. ID",

       rq.request_id                          "Req. ID",

       tl.user_concurrent_program_name        "Program Name",

       rq.actual_start_date                   "Start Date",

       rq.actual_completion_date              "Completion Date",

       ROUND((rq.actual_completion_date - rq.actual_start_date) * 1440, 2)   "Runtime (in Minutes)"   ,  

       ROUND((sysdate - rq.actual_start_date) * 1440, 2)   "Runing (in Minutes)" ,

       ROUND((sysdate - rq.actual_start_date) * 1440/60, 2)   "Runing (in Hours)"

  FROM applsys.fnd_concurrent_programs_tl  tl,

       applsys.fnd_concurrent_requests     rq

 WHERE tl.application_id        = rq.program_application_id

   AND tl.concurrent_program_id = rq.concurrent_program_id

   AND tl.LANGUAGE              = USERENV('LANG')

--   AND rq.actual_start_date IS NOT NULL

--   AND rq.actual_completion_date IS NOT NULL

  and  request_id=202319707