Friday, 11 January 2013

Concurrent Request

 SELECT  fcrs.request_id, fcrs.user_concurrent_program_name,
         fcrs.actual_start_date, fcrs.actual_completion_date,
         FLOOR(((fcrs.actual_completion_date-fcrs.actual_start_date)*24*60*60)/3600)||':'||
         FLOOR((((fcrs.actual_completion_date-fcrs.actual_start_date)*24*60*60) -
         FLOOR(((fcrs.actual_completion_date-fcrs.actual_start_date)*24*60*60)/3600)*3600)/60)||':'||
         round((((fcrs.actual_completion_date-fcrs.actual_start_date)*24*60*60) -
         FLOOR(((fcrs.actual_completion_date-fcrs.actual_start_date)*24*60*60)/3600)*3600 -
         (FLOOR((((fcrs.actual_completion_date-fcrs.actual_start_date)*24*60*60) -
         FLOOR(((fcrs.actual_completion_date-fcrs.actual_start_date)*24*60*60)/3600)*3600)/60)*60) )) "HOURS:MINUTES:SECONDS",
         fcrs.argument_text, fcrs.requestor,
         DECODE (fcrs.status_code,
                'A', 'Waiting',
                'B', 'Resuming',
                'C', 'Normal',
                'D', 'Cancelled',
                'E', 'Errored',
                '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',
                 fcrs.status_code
                ) "Status",
         decode(fcrs.phase_code,
                'C','Completed',
                'I','Inactive',
                'R','Running',
                'A','Active',
                fcrs.phase_code) "Phase Code",  fcrs.completion_text,
         fcrs.responsibility_application_id, frt.responsibility_name,
         fcrs.save_output_flag, fcrs.request_date ,
         decode (fcrs.execution_method_code,
                 'B', 'Request Set Stage Function',
                 'Q', 'SQL*Plus',
                 'H', 'Host',
                 'L', 'SQL*Loader',
                 'A', 'Spawned',
                 'I', 'PL/SQL Stored Procedure',
                 'P', 'Oracle Reports',
                 'S', 'Immediate',
                 fcrs.execution_method_code
               ) execution_method , fcrs.concurrent_program_id, fcrs.program_short_name, fcrs.printer,
               fcrs.parent_request_id
    FROM fnd_conc_req_summary_v fcrs,
         fnd_responsibility_tl frt
   WHERE 1 = 1
AND user_concurrent_program_name LIKE '%PRC: Generate Draft Revenue for a Single Project%'
--and argument_text LIKE '%'
--and requestor not in ('SYSADMIN','INVADMIN')
--and request_id = 9686914
AND frt.LANGUAGE = 'US' AND fcrs.responsibility_id = frt.responsibility_id
--and fcrs.actual_start_date < sysdate
--and actual_start_date between to_date('10-JAN-2019 17:00:00', 'DD-MON-YYYY HH24:MI:SS') and to_date('10-JAN-2019 17:30:00', 'DD-MON-YYYY HH24:MI:SS')
--and fcrs.phase_code = 'R'
--and fcrs.status_code = 'X'
--and fcrs.status_code not in ('P','D','Q','C')
--and trunc(fcrs.actual_start_date) =trunc(sysdate)
--and trunc(fcrs.actual_completion_date) = trunc(sysdate)
ORDER BY fcrs.actual_start_date DESC;


OPP log file:

 SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
  FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
 WHERE fcpp.processor_id = fcp.concurrent_process_id
   AND fcpp.action_type = 6
   AND fcpp.concurrent_request_id = 10711139;

Script for the about

echo "Enter Apps password: "
read apps
echo "Enter request_id: "
read request_id
OPPLOG=`sqlplus -s "apps/$apps" <<EOF
       set heading off feedback off verify off
set line 200
       SELECT fcp.logfile_name
  FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
 WHERE fcpp.processor_id = fcp.concurrent_process_id
   AND fcpp.action_type = 6
   AND fcpp.concurrent_request_id = $request_id;
       exit
EOF
`
vi $OPPLOG


List Responsibilities That Can Run a Given Concurrent Program

SELECT          responsibility_name RN
FROM            fnd_request_groups frg,
                fnd_request_group_units frgu,
                fnd_concurrent_programs_vl fcpv,
                fnd_responsibility_vl frv
WHERE           frgu.request_unit_type = 'P'
AND             (UPPER(fcpv.concurrent_program_name) = UPPER('&1')
                OR
                UPPER(fcpv.user_concurrent_program_name) = UPPER('&1'))
AND             frgu.request_group_id = frg.request_group_id
AND             frgu.request_unit_id = fcpv.concurrent_program_id
AND             frv.request_group_id = frg.request_group_id
ORDER BY        responsibility_name  ;

No comments:

Post a Comment