NaturalOracle DataGaurd tutorial Series Coming Soon!! Natural
For Any queries, please mail us at support@funoracleapps.com

Query to locate file name and location of the current Workflow Mailer Service log

No comments
We can use below script to find the workflow service log file name and location.


Script:

set linesize 100; 
set pagesize 200; 
set verify off; 
column MANAGER format a15; 
column MEANING format a15; 
SELECT concurrent_queue_name manager, fcp.last_update_date, fcp.concurrent_process_id pid, meaning, fcp.logfile_name 
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup 
WHERE concurrent_queue_name in ('WFMLRSVC') 
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id 
AND fcq.application_id = fcp.queue_application_id 
AND flkup.lookup_code=fcp.process_status_code 
AND lookup_type ='CP_PROCESS_STATUS_CODE' 
AND meaning='Active'; 

No comments :

Post a Comment

Script to find Archive log Generation per hours on each day

No comments
We can use below script for finding the archive log generation per hour on each day.

Script:

set pagesize 120; 
set linesize 200; 
col day for a8; 
spool archivelog.lst
PROMPT Archive log distribution per hours on each day
  

select 
  to_char(first_time,'YY-MM-DD') day, 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') "07", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') "08", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') "09", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') "10", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') "11", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') "12", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') "13", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') "14", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') "15", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') "16", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') "17", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') "18", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') "19", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') "20", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') "21", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') "22", 
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') "23", 
  COUNT(*) "TOTAL GENERATED"
from v$log_history 
group by to_char(first_time,'YY-MM-DD') 
order by day desc ;

No comments :

Post a Comment

Useful Script to Analyze Concurrent Request ID -- ANALYZEREQ.SQL

No comments
Oracle provides a very useful script for analyzing the Concurrent Request Id and get detailed Information. The script can also be downloaded from Oracle support Doc ID 134035.1.

USAGE: 
sqlplus apps_user/apps_passwd @analyzereq request_id

EXAMPLE:    
sqlplus apps/apps @analyzereq 123456


Script:

set serveroutput on
set feedback off
set verify off
set heading off
set timing off


prompt

DECLARE

req_id number(15) := &1;



FUNCTION  get_status(p_status_code varchar2) return varchar2 AS

c_status fnd_lookups.meaning%TYPE;

BEGIN
SELECT nvl(meaning, 'UNKNOWN') 
   INTO c_status
   FROM fnd_lookups
   WHERE LOOKUP_TYPE = 'CP_STATUS_CODE'
   AND LOOKUP_CODE = p_status_code;

return rtrim(c_status);

END get_status;



FUNCTION  get_phase(p_phase_code varchar2) return varchar2 AS

c_phase fnd_lookups.meaning%TYPE;

BEGIN
SELECT nvl(meaning, 'UNKNOWN') 
   INTO c_phase
   FROM fnd_lookups
   WHERE LOOKUP_TYPE = 'CP_PHASE_CODE'
   AND LOOKUP_CODE = p_phase_code;

return rtrim(c_phase);

END get_phase;



PROCEDURE manager_check  (req_id        in  number,
                          cd_id         in  number,
                          mgr_defined   out boolean,
                          mgr_active    out boolean,
                          mgr_workshift out boolean,
                          mgr_running   out boolean,
                          run_alone     out boolean) is

    cursor mgr_cursor (rid number) is
      select running_processes, max_processes,
             decode(control_code,
                    'T','N',       -- Abort
                    'X','N',       -- Aborted
                    'D','N',       -- Deactivate
                    'E','N',       -- Deactivated
                        'Y') active
        from fnd_concurrent_worker_requests
        where request_id = rid
          and not((queue_application_id = 0)
                  and (concurrent_queue_id in (1,4)));

    run_alone_flag  varchar2(1);

  begin
    mgr_defined := FALSE;
    mgr_active := FALSE;
    mgr_workshift := FALSE;
    mgr_running := FALSE;

    for mgr_rec in mgr_cursor(req_id) loop
      mgr_defined := TRUE;
      if (mgr_rec.active = 'Y') then
        mgr_active := TRUE;
        if (mgr_rec.max_processes > 0) then
          mgr_workshift := TRUE;
        end if;
        if (mgr_rec.running_processes > 0) then
          mgr_running := TRUE;
        end if;
      end if;
    end loop;

    if (cd_id is null) then    
      run_alone_flag := 'N';   
    else
      select runalone_flag
        into run_alone_flag
        from fnd_conflicts_domain d
        where d.cd_id = manager_check.cd_id;
    end if;
    if (run_alone_flag = 'Y') then
      run_alone := TRUE;
    else
      run_alone := FALSE;
    end if;

  end manager_check;


PROCEDURE print_mgrs(p_req_id number) AS

CURSOR  c_mgrs(rid number) IS
SELECT user_concurrent_queue_name name, fcwr.running_processes active,
        decode(fcwr.control_code,        'A', fl.meaning,
    'D', fl.meaning,
    'E', fl.meaning, 
    'N', fl.meaning,
    'R', fl.meaning,
    'T', fl.meaning,
    'U', fl.meaning,
    'V', fl.meaning,
    'X', fl.meaning,
    NULL, 'Running',
    '** Unknown Status **') status
        FROM fnd_concurrent_queues_vl fcqv, fnd_concurrent_worker_requests fcwr, fnd_lookups fl
        WHERE fcwr.request_id = rid
        AND fcwr.concurrent_queue_id = fcqv.concurrent_queue_id
        AND fcwr.concurrent_queue_id not in (1, 4)
        AND fl.lookup_code (+) = fcwr.control_code
        AND fl.lookup_type (+) = 'CP_CONTROL_CODE';

BEGIN

for mgr_rec in c_mgrs(p_req_id) loop
    DBMS_OUTPUT.PUT_LINE('- ' || mgr_rec.name || ' | Status: ' || mgr_rec.status || ' (' || mgr_rec.active || ' active processes)');
        end loop;

END print_mgrs;


PROCEDURE analyze_request(p_req_id number) AS

reqinfo         fnd_concurrent_requests%ROWTYPE;
proginfo        fnd_concurrent_programs_vl%ROWTYPE;

c_status fnd_lookups.meaning%TYPE;
m_buf           fnd_lookups.meaning%TYPE;
conc_prog_name fnd_concurrent_programs.concurrent_program_name%TYPE;
exe_method_code fnd_concurrent_programs_vl.execution_method_code%TYPE;
conc_app_name   fnd_application_vl.application_name%TYPE;
tmp_id number(15);
tmp_status      fnd_concurrent_requests.status_code%TYPE;
tmp_date        date;
conc_app_id fnd_concurrent_requests.program_application_id%TYPE;
conc_id fnd_concurrent_requests.concurrent_program_id%TYPE;
conc_cd_id fnd_concurrent_requests.cd_id%TYPE;
v_enabled_flag  fnd_concurrent_programs.enabled_flag%TYPE;
conflict_domain fnd_conflicts_domain.user_cd_name%TYPE;
parent_id       number(15);
resp_name       varchar2(100);
rclass_name     fnd_concurrent_request_class.request_class_name%TYPE;
exe_file_name   fnd_executables.execution_file_name%TYPE;

c_user fnd_user.user_name%TYPE;
last_user fnd_user.user_name%TYPE;

fcd_phase varchar2(48);
fcd_status varchar2(48);

traid fnd_concurrent_requests.program_application_id%TYPE;
trcpid fnd_concurrent_requests.concurrent_program_id%TYPE;

icount number;
ireqid fnd_concurrent_requests.request_id%TYPE;
pcode fnd_concurrent_requests.phase_code%TYPE;
scode fnd_concurrent_requests.status_code%TYPE;

live_child      boolean;
mgr_defined boolean;
mgr_active boolean;
mgr_workshift   boolean;
mgr_running     boolean;
run_alone       boolean;
reqlimit        boolean := false;

mgrname         fnd_concurrent_queues_vl.user_concurrent_queue_name%TYPE;
filename        varchar2(255);

qcf             fnd_concurrent_programs.queue_control_flag%TYPE;

sep varchar2(200) := '------------------------------------------------------';

REQ_NOTFOUND    exception;


CURSOR c_wait IS
SELECT request_id, phase_code, status_code
FROM fnd_concurrent_requests
WHERE parent_request_id = p_req_id;

CURSOR c_inc IS
SELECT to_run_application_id, to_run_concurrent_program_id
FROM fnd_concurrent_program_serial
WHERE running_application_id = conc_app_id
AND running_concurrent_program_id = conc_id;

CURSOR  c_ireqs IS
SELECT request_id, phase_code, status_code
FROM   fnd_concurrent_requests
WHERE  phase_code = 'R'
AND    program_application_id = traid
AND    concurrent_program_id = trcpid
AND    cd_id = conc_cd_id;

CURSOR c_userreqs(uid number, s date) IS
       SELECT request_id, to_char(requested_start_date, 'DD-MON-RR HH24:MI:SS') start_date,
              phase_code, status_code
       FROM fnd_concurrent_requests
       WHERE phase_code IN ('R', 'P')
       AND requested_by = uid
       AND requested_start_date < s
       AND hold_flag = 'N';

BEGIN

        BEGIN
            SELECT *
    INTO   reqinfo
    FROM   fnd_concurrent_requests
    WHERE  request_id = p_req_id;
EXCEPTION
            WHEN NO_DATA_FOUND THEN
                raise REQ_NOTFOUND;
        END;

DBMS_OUTPUT.PUT_LINE('Analyzing request '||req_id||':');
DBMS_OUTPUT.PUT_LINE(sep);


-- Program information
DBMS_OUTPUT.PUT_LINE('Program information:');

SELECT fvl.*
INTO proginfo
FROM fnd_concurrent_programs_vl fvl, fnd_concurrent_requests fcr
WHERE fcr.request_id = p_req_id
AND fcr.concurrent_program_id = fvl.concurrent_program_id
AND fcr.program_application_id = fvl.application_id;

DBMS_OUTPUT.PUT_LINE('Program: '|| proginfo.user_concurrent_program_name || '  (' || proginfo.concurrent_program_name || ')');

SELECT nvl(application_name, '-- UNKNOWN APPLICATION --')
INTO conc_app_name
FROM fnd_application_vl fvl, fnd_concurrent_requests fcr
WHERE fcr.request_id = p_req_id
AND fcr.program_application_id = fvl.application_id;

DBMS_OUTPUT.PUT_LINE('Application: '||conc_app_name);

SELECT nvl(meaning, 'UNKNOWN')
INTO  m_buf 
FROM fnd_lookups
WHERE lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND lookup_code = proginfo.execution_method_code;

SELECT nvl(execution_file_name, 'NONE')
INTO exe_file_name
FROM fnd_executables
WHERE application_id = proginfo.executable_application_id
AND executable_id = proginfo.executable_id;

DBMS_OUTPUT.PUT_LINE('Executable type: ' || m_buf || '  (' || proginfo.execution_method_code || ')');
DBMS_OUTPUT.PUT_LINE('Executable file name or procedure: ' || exe_file_name);
DBMS_OUTPUT.PUT_LINE('Run alone flag: ' || proginfo.run_alone_flag);
        DBMS_OUTPUT.PUT_LINE('SRS flag: ' || proginfo.srs_flag);
DBMS_OUTPUT.PUT_LINE('NLS compliant: ' || proginfo.nls_compliant);
DBMS_OUTPUT.PUT_LINE('Output file type: ' || proginfo.output_file_type);

if proginfo.concurrent_class_id is not null then
select request_class_name
into rclass_name
from fnd_concurrent_request_class
where application_id = proginfo.class_application_id
and request_class_id = proginfo.concurrent_class_id;

DBMS_OUTPUT.PUT_LINE('Request type: ' || rclass_name);
end if;

if proginfo.execution_options is not null then
DBMS_OUTPUT.PUT_LINE('Execution options: ' || proginfo.execution_options);
end if;

if proginfo.enable_trace = 'Y' then
DBMS_OUTPUT.PUT_LINE('SQL Trace has been enabled for this program.');
end if;

        DBMS_OUTPUT.PUT_LINE(sep);
        DBMS_OUTPUT.PUT_LINE('
                             ');
        DBMS_OUTPUT.PUT_LINE(sep);

-- Submission information
DBMS_OUTPUT.PUT_LINE('Submission information:');

begin
SELECT user_name into c_user from fnd_user
        where user_id = reqinfo.requested_by;
exception
when no_data_found then
c_user := '-- UNKNOWN USER --';
end;

begin
SELECT user_name into last_user from fnd_user
WHERE user_id = reqinfo.last_updated_by;
exception
when no_data_found then
last_user := '-- UNKNOWN USER --';
end;

DBMS_OUTPUT.PUT_LINE('It was submitted by user: '||c_user);
        SELECT responsibility_name
        INTO   resp_name
        FROM   fnd_responsibility_vl
        WHERE  responsibility_id = reqinfo.responsibility_id
        AND    application_id = reqinfo.responsibility_application_id;

        DBMS_OUTPUT.PUT_LINE('Using responsibility: ' || resp_name);
        DBMS_OUTPUT.PUT_LINE('It was submitted on: ' || to_char(reqinfo.request_date, 'DD-MON-RR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('It was requested to start on: '||
to_char(reqinfo.requested_start_date, 'DD-MON-RR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('Parent request id: ' || reqinfo.parent_request_id);
DBMS_OUTPUT.PUT_LINE('Language: ' || reqinfo.nls_language);
DBMS_OUTPUT.PUT_LINE('Territory: ' || reqinfo.nls_territory);
        DBMS_OUTPUT.PUT_LINE('Priority: ' || to_char(reqinfo.priority));

DBMS_OUTPUT.PUT_LINE('Arguments (' || reqinfo.number_of_arguments || '): ' || reqinfo.argument_text);

c_status := get_status(reqinfo.status_code);

DBMS_OUTPUT.PUT_LINE(sep);
        DBMS_OUTPUT.PUT_LINE('
                             ');
        DBMS_OUTPUT.PUT_LINE(sep);

-- Analysis
DBMS_OUTPUT.PUT_LINE('Analysis:');


-- Completed Requests
-------------------------------------------------------------------------------------------------------------
IF reqinfo.phase_code = 'C' THEN
   
      

      DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' has completed with status "'||c_status||'".');
      DBMS_OUTPUT.PUT_LINE('It began running on: '||
   nvl(to_char(reqinfo.actual_start_date, 'DD-MON-RR HH24:MI:SS'), 
  '-- NO START DATE --'));
      DBMS_OUTPUT.PUT_LINE('It completed on: '||
   nvl(to_char(reqinfo.actual_completion_date, 'DD-MON-RR HH24:MI:SS'), 
       '-- NO COMPLETION DATE --'));
      
              BEGIN
              SELECT user_concurrent_queue_name
              INTO   mgrname
              FROM   fnd_concurrent_queues_vl
              WHERE  concurrent_queue_id = reqinfo.controlling_manager;
              DBMS_OUTPUT.PUT_LINE('It was run by manager: ' || mgrname);
              EXCEPTION
                WHEN NO_DATA_FOUND THEN
                   SELECT queue_control_flag
                   INTO   qcf
                   FROM   fnd_concurrent_programs
                   WHERE  concurrent_program_id = reqinfo.concurrent_program_id
           AND    application_id = reqinfo.program_application_id;
              
                   IF qcf = 'Y' THEN
     DBMS_OUTPUT.PUT_LINE('This request is a queue control request, it was run by the ICM');
                   ELSE
                     DBMS_OUTPUT.PUT_LINE('It was run by an unknown manager.');
                   END IF;
                   
              END;
             
              SELECT nvl(reqinfo.logfile_name, '-- No logfile --')
              INTO   filename
              FROM   dual;
              DBMS_OUTPUT.PUT_LINE('Logfile: ' || filename);
              SELECT nvl(reqinfo.outfile_name, '-- No output file --')
              INTO   filename
              FROM   dual;
              DBMS_OUTPUT.PUT_LINE('Output file: ' || filename);

              DBMS_OUTPUT.PUT_LINE('It produced completion message: ');
      DBMS_OUTPUT.PUT_LINE(nvl(reqinfo.completion_text, '-- NO COMPLETION MESSAGE --'));
      
              


-- Running Requests
-------------------------------------------------------------------------------------------------------------
ELSIF reqinfo.phase_code = 'R' THEN
      


DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' is currently running with status "'||c_status||'".');
DBMS_OUTPUT.PUT_LINE('It began running on: '||
nvl(to_char(reqinfo.actual_start_date, 'DD-MON-RR HH24:MI:SS'), 
'-- NO START DATE --'));
                BEGIN
                SELECT user_concurrent_queue_name
                INTO   mgrname
                FROM   fnd_concurrent_queues_vl
                WHERE  concurrent_queue_id = reqinfo.controlling_manager;
                DBMS_OUTPUT.PUT_LINE('It is being run by manager: ' || mgrname);
                EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     null;
                END;

                SELECT nvl(reqinfo.logfile_name, '-- No logfile --')
                INTO   filename
                FROM   dual;
                DBMS_OUTPUT.PUT_LINE('Logfile: ' || filename);
                SELECT nvl(reqinfo.outfile_name, '-- No output file --')
                INTO   filename
                FROM   dual;
                DBMS_OUTPUT.PUT_LINE('Output file: ' || filename);
                
                IF reqinfo.os_process_id is not null THEN
                        DBMS_OUTPUT.PUT_LINE('OS process id: ' || reqinfo.os_process_id);
                END IF;

IF reqinfo.status_code = 'Z' THEN

-- Waiting request, See what it is waiting on
FOR child in c_wait LOOP

DBMS_OUTPUT.PUT_LINE('It is waiting on request '||
child.request_id||' phase = '||get_phase(child.phase_code)||
' status = '||get_status(child.status_code));
END LOOP;

ELSIF reqinfo.status_code = 'W' THEN

-- Paused, check and see if it is a request set, and if its children are running
SELECT nvl(concurrent_program_name, 'UNKNOWN')
INTO conc_prog_name
FROM fnd_concurrent_programs
WHERE concurrent_program_id = reqinfo.concurrent_program_id;


DBMS_OUTPUT.PUT_LINE('A Running/Paused request is waiting on one or more child requests to complete.');
IF conc_prog_name = 'FNDRSSTG' THEN
DBMS_OUTPUT.PUT_LINE('This program appears to be a Request Set Stage.');
END IF;

IF instr(conc_prog_name, 'RSSUB') > 0  THEN
DBMS_OUTPUT.PUT_LINE('This program appears to be a Request Set parent program.');
END IF;

live_child := FALSE;
FOR child in c_wait LOOP

DBMS_OUTPUT.PUT_LINE('It has a child request: '||
child.request_id||' (phase = '||get_phase(child.phase_code)||
' - status = '||get_status(child.status_code)||')');
                                IF child.phase_code != 'C' THEN
live_child := TRUE;
END IF;

END LOOP;

IF live_child = FALSE THEN
DBMS_OUTPUT.PUT_LINE('This request has no child requests that are still running. You may need to wake this request up manually.');
END IF;
END IF;



-- Pending Requests
-------------------------------------------------------------------------------------------------------------
ELSIF reqinfo.phase_code = 'P' THEN
      
              
      DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' is in phase "Pending" with status "'||c_status||'".');
      DBMS_OUTPUT.PUT_LINE('                           (phase_code = P)   (status_code = '||reqinfo.status_code||')');

              -- could be a queue control request
              SELECT queue_control_flag
              INTO   qcf
              FROM   fnd_concurrent_programs
              WHERE  concurrent_program_id = reqinfo.concurrent_program_id
      AND    application_id = reqinfo.program_application_id;
              
              IF qcf = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is a queue control request');
                DBMS_OUTPUT.PUT_LINE('It will be run by the ICM on its next sleep cycle');
                GOTO diagnose;
              END IF;

      -- why is it pending?

              -- could be scheduled
      IF reqinfo.requested_start_date > sysdate or reqinfo.status_code = 'P' THEN
                 DBMS_OUTPUT.PUT_LINE('This is a scheduled request.');
DBMS_OUTPUT.PUT_LINE('It is currently scheduled to start running on '||
                                            to_char(reqinfo.requested_start_date, 'DD-MON-RR HH24:MI:SS'));
                 DBMS_OUTPUT.PUT_LINE('This should show on the form as Pending/Scheduled');
                 GOTO diagnose;
              END IF;

      -- could be on hold
      IF reqinfo.hold_flag = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is currently on hold. It will not run until the hold is released.');
DBMS_OUTPUT.PUT_LINE('It was placed on hold by: '||last_user||' on '||to_char(reqinfo.last_update_date, 'DD-MON-RR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/On Hold');
                GOTO diagnose;
      END IF;

      -- could be disabled
              IF proginfo.enabled_flag = 'N' THEN
DBMS_OUTPUT.PUT_LINE('This request is currently disabled.');
         DBMS_OUTPUT.PUT_LINE('The concurrent_program '|| proginfo.user_concurrent_program_name ||' needs to be enabled for this request to run.');
         DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/Disabled');
                 GOTO diagnose;
      END IF;

      
              -- check queue_method_code
              -- unconstrained requests
      IF reqinfo.queue_method_code = 'I' THEN
                  DBMS_OUTPUT.PUT_LINE('This request is an unconstrained request. (queue_method_code = I)');
                  IF reqinfo.status_code = 'I' THEN
                      DBMS_OUTPUT.PUT_LINE('It is in a "Pending/Normal" status, ready to be run by the next available manager.');
                  ELSIF reqinfo.status_code = 'Q' THEN
                      DBMS_OUTPUT.PUT_LINE('It has a status of "Standby" even though it is unconstrained. It will not be run by any manager.');
                  ELSIF reqinfo.status_code IN ('A', 'Z') THEN
                      DBMS_OUTPUT.PUT_LINE('It is in a "Waiting" status. This usually indicates a child request waiting for the parent to release it.');
                      SELECT nvl(parent_request_id, -1)
                      INTO   parent_id
                      FROM   fnd_conc_req_summary_v
                      WHERE  request_id = p_req_id;
                      IF parent_id = -1 THEN
                          DBMS_OUTPUT.PUT_LINE('** Unable to find a parent request for this request');
                      ELSE
                          DBMS_OUTPUT.PUT_LINE('It''s parent request id is: ' || to_char(parent_id));
                      END IF;
                  ELSE
                      DBMS_OUTPUT.PUT_LINE('Hmmm. A status of ' || reqinfo.status_code || '. I was not really expecting to see this status.');
                  END IF;

              -- constrained requests
      ELSIF reqinfo.queue_method_code = 'B' THEN
                  DBMS_OUTPUT.PUT_LINE('This request is a constrained request. (queue_method_code = B)');
                  IF reqinfo.status_code = 'I' THEN
                      DBMS_OUTPUT.PUT_LINE('The Conflict Resolution manager has released this request, and it is in a "Pending/Normal" status.');
      DBMS_OUTPUT.PUT_LINE('It is ready to be run by the next available manager.');
                  ELSIF reqinfo.status_code = 'Q' THEN
                      DBMS_OUTPUT.PUT_LINE('It is in a "Pending/Standby" status. The Conflict Resolution manager will need to release it before it can be run.');
                  ELSIF reqinfo.status_code IN ('A', 'Z') THEN
                      DBMS_OUTPUT.PUT_LINE('It is in a "Waiting" status. This usually indicates a child request waiting for the parent to release it.');
                      SELECT nvl(parent_request_id, -1)
                      INTO   parent_id
                      FROM   fnd_conc_req_summary_v
                      WHERE  request_id = p_req_id;
                      IF parent_id = -1 THEN
                          DBMS_OUTPUT.PUT_LINE('** Unable to find a parent request for this request');
                      ELSE
                          DBMS_OUTPUT.PUT_LINE('It''s parent request id is: ' || to_char(parent_id));
                      END IF;
                  ELSE
                      DBMS_OUTPUT.PUT_LINE('Hmmm. A status of ' || reqinfo.status_code || '. I was not really expecting to see this status.');
                  END IF;


                  -- incompatible programs
  SELECT program_application_id, concurrent_program_id, cd_id
  INTO   conc_app_id, conc_id, conc_cd_id
  FROM   fnd_concurrent_requests
  WHERE  request_id = p_req_id;

  icount := 0;
  FOR progs in c_inc LOOP

traid :=  progs.to_run_application_id;
trcpid := progs.to_run_concurrent_program_id;

OPEN c_ireqs;
LOOP

FETCH c_ireqs INTO ireqid, pcode, scode;
EXIT WHEN c_ireqs%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Request '|| p_req_id ||' is waiting, or will have to wait, on an incompatible request: '|| ireqid );
DBMS_OUTPUT.PUT_LINE('which has phase = '|| pcode ||' and status = '|| scode);
icount := icount + 1;


END LOOP;
CLOSE c_ireqs;


  END LOOP;

  IF icount = 0 THEN
DBMS_OUTPUT.PUT_LINE('No running incompatible requests were found for request '||p_req_id);
  END IF;

  -- could be a runalone itself
  IF proginfo.run_alone_flag = 'Y' THEN
      DBMS_OUTPUT.PUT_LINE('This request is constrained because it is a runalone request.');
  END IF;

          -- single threaded
                  IF reqinfo.single_thread_flag = 'Y' THEN
                      DBMS_OUTPUT.PUT_LINE('This request is constrained because the profile option Concurrent: Sequential Requests is set.');
                      reqlimit := true;
                  END IF;

          -- request limit
                  IF reqinfo.request_limit = 'Y' THEN
                      DBMS_OUTPUT.PUT_LINE('This request is constrained because the profile option Concurrent: Active Request Limit is set.');
                      reqlimit := true;
                  END IF;

                  IF reqlimit = true THEN
                     DBMS_OUTPUT.PUT_LINE('This request may have to wait on these requests:');
                     FOR progs in c_userreqs(reqinfo.requested_by, reqinfo.requested_start_date) LOOP
                      DBMS_OUTPUT.PUT_LINE('Request id: ' || progs.request_id || ' Requested start date: ' || progs.start_date);
                        DBMS_OUTPUT.PUT_LINE('     Phase: ' || get_phase(progs.phase_code) || '   Status: ' || get_status(progs.status_code));
                     END LOOP;
                  END IF;

              -- error, invalid queue_method_code
              ELSE
                  DBMS_OUTPUT.PUT_LINE('** This request has an invalid queue_method_code of '||reqinfo.queue_method_code);
                  DBMS_OUTPUT.PUT_LINE('** This request will not be run. You may need to apply patch 739644.');
                  GOTO diagnose;
      END IF;


              DBMS_OUTPUT.PUT_LINE(sep);
      DBMS_OUTPUT.PUT_LINE('Checking managers available to run this request...');

      -- check the managers
      manager_check(p_req_id, reqinfo.cd_id, mgr_defined, mgr_active, mgr_workshift, mgr_running, run_alone);

              -- could be a runalone ahead of it
      IF run_alone = TRUE THEN
                  DBMS_OUTPUT.PUT_LINE('There is a runalone request running ahead of this request');
                    DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
                    
                    select user_cd_name into conflict_domain from fnd_conflicts_domain
                    where cd_id = reqinfo.cd_id;

                    DBMS_OUTPUT.PUT_LINE('Conflict domain = '||conflict_domain);

                    -- see what is running
                    begin
                    select request_id, status_code, actual_start_date
                    into tmp_id, tmp_status, tmp_date
                    from fnd_concurrent_requests fcr, fnd_concurrent_programs fcp
                    where fcp.run_alone_flag = 'Y'
                    and fcp.concurrent_program_id = fcr.concurrent_program_id
                    and fcr.phase_code = 'R'
                    and fcr.cd_id = reqinfo.cd_id;

DBMS_OUTPUT.PUT_LINE('This request is waiting for request '||tmp_id||
                                                 ', which is running with status '||get_status(tmp_status));
                        DBMS_OUTPUT.PUT_LINE('It has been running since: '||
nvl(to_char(tmp_date, 'DD-MON-RR HH24:MI:SS'), '-- NO START DATE --'));
                    exception
                       when NO_DATA_FOUND then
                           DBMS_OUTPUT.PUT_LINE('** The runalone flag is set for conflict domain '||conflict_domain||
                                                        ', but there is no runalone request running');
                    end;
              
      ELSIF mgr_defined = FALSE THEN
                    DBMS_OUTPUT.PUT_LINE('There is no manager defined that can run this request');
                    DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
                    DBMS_OUTPUT.PUT_LINE('Check the specialization rules for each manager to make sure they are defined correctly.');
              ELSIF mgr_active = FALSE THEN
                    DBMS_OUTPUT.PUT_LINE('There are one or more managers defined that can run this request, but none of them are currently active');
                    DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
    -- print out which managers can run it and their status
                    DBMS_OUTPUT.PUT_LINE('These managers are defined to run this request:');
    print_mgrs(p_req_id);
              ELSIF mgr_workshift = FALSE THEN
                    DBMS_OUTPUT.PUT_LINE('Right now, there is no manager running in an active workshift that can run this request');
                    DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
    -- display details about the workshifts
              ELSIF mgr_running = FALSE THEN
                    DBMS_OUTPUT.PUT_LINE('There is one or more managers available to run this request, but none of them are running');
                    DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
                    -- print out which managers can run it and their status
                    print_mgrs(p_req_id);
              ELSE
                    -- print out the managers available to run it
                    DBMS_OUTPUT.PUT_LINE('These managers are available to run this request:');
                    print_mgrs(p_req_id);
    
              END IF;

     
        -- invalid phase code
ELSE 
     DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' has an invalid phase_code of "'||reqinfo.phase_code||'"');

END IF;

<>

DBMS_OUTPUT.PUT_LINE(sep);

EXCEPTION
WHEN REQ_NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' not found.');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error number ' || sqlcode || ' has occurred.');
            DBMS_OUTPUT.PUT_LINE('Cause: ' || sqlerrm);

END analyze_request;


BEGIN

analyze_request(req_id);



END;
/

prompt

exit

/



Reference:



No comments :

Post a Comment