----------------------------------------------------------------------------------------
--
-- analyzepending.sql
-- Analyze all pending requests--
----------------------------------------------------------------------------------------
set serveroutput on size 100000
set feedback off
set verify off
set heading off
set timing off
DECLARE
FUNCTION chkwaiting(rid number) return varchar2 AS
parent_id number(15);
BEGIN
SELECT nvl(parent_request_id, -1)
INTO parent_id
FROM fnd_conc_req_summary_v
WHERE request_id = rid;
IF parent_id = -1 THEN
return('Waiting, but unable to find a parent request for this request');
ELSE
return('Waiting on parent request: ' || to_char(parent_id));
END IF;
END chkwaiting;
PROCEDURE manager_check (req_id in number,
mgr_defined out boolean,
mgr_active out boolean,
mgr_workshift out boolean,
mgr_running 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)));
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;
END manager_check;
FUNCTION chknormal(rid number) return varchar2 AS
mgr_defined boolean;
mgr_active boolean;
mgr_workshift boolean;
mgr_running boolean;
BEGIN
manager_check(rid, mgr_defined, mgr_active, mgr_workshift, mgr_running);
IF mgr_defined = FALSE OR mgr_active = FALSE OR mgr_workshift = FALSE OR mgr_running = FALSE THEN
return('No managers are running that can run this request');
END IF;
return('Pending Normal');
END chknormal;
FUNCTION analyzereq(rid number) return varchar2 AS
reqinfo fnd_concurrent_requests%ROWTYPE;
qcf fnd_concurrent_programs.queue_control_flag%TYPE;
v_enabled_flag fnd_concurrent_programs.enabled_flag%TYPE;
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;
traid fnd_concurrent_requests.program_application_id%TYPE;
trcpid fnd_concurrent_requests.concurrent_program_id%TYPE;
ireqid fnd_concurrent_requests.request_id%TYPE;
pcode fnd_concurrent_requests.phase_code%TYPE;
scode fnd_concurrent_requests.status_code%TYPE;
run_alone_flag varchar2(1);
r varchar2(100);
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;
BEGIN
SELECT *
INTO reqinfo
FROM fnd_concurrent_requests
WHERE request_id = rid;
-- 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
return('Queue control request. Will be run by the ICM on its next sleep cycle');
END IF;
-- could be scheduled
IF reqinfo.requested_start_date > sysdate THEN
return('Scheduled to run on ' || to_char(reqinfo.requested_start_date, 'DD-MON-RR HH24:MI:SS'));
END IF;
-- could be on hold
IF reqinfo.hold_flag = 'Y' THEN
return('On hold');
END IF;
-- could be disabled
select enabled_flag into v_enabled_flag
from fnd_concurrent_programs
where concurrent_program_id = reqinfo.concurrent_program_id
and application_id = reqinfo.program_application_id;
IF v_enabled_flag = 'N' THEN
return('Concurrent program is disabled');
END IF;
-- advanced schedule
IF reqinfo.status_code = 'P' THEN
return('Scheduled to be run by the Advanced Scheduler');
END IF;
-- check queue_method_code
IF reqinfo.queue_method_code NOT IN ('I','B') THEN
return('Bad queue_method_code of: ' || reqinfo.queue_method_code);
END IF;
-- waiting status
IF reqinfo.status_code IN ('A', 'Z') THEN
return chkwaiting(reqinfo.request_id);
END IF;
-- check for runalones
SELECT runalone_flag
into run_alone_flag
from fnd_conflicts_domain d
where d.cd_id = reqinfo.cd_id;
IF (run_alone_flag = 'Y') THEN
return('Waiting on a run-alone request');
END IF;
-- Normal status
IF reqinfo.status_code = 'I' THEN
return chknormal(reqinfo.request_id);
END IF;
-- unconstrained requests
IF reqinfo.queue_method_code = 'I' THEN
-- bad status
IF reqinfo.status_code = 'Q' THEN
return('Unconstrained Standby request. Will not be run');
END IF;
return('Odd status of: ' || reqinfo.status_code);
END IF;
-- constrained requests
IF reqinfo.queue_method_code = 'B' THEN
-- standby, check reasons for waiting
IF reqinfo.status_code = 'Q' THEN
-- 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 = reqinfo.request_id;
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;
return('Waiting on incompatible request ' || ireqid || ' phase=' || pcode || ' status=' || scode);
END LOOP;
CLOSE c_ireqs;
END LOOP;
-- single threaded
IF reqinfo.single_thread_flag = 'Y' THEN
return('Single-threaded request. Waiting on other requests for this user.');
END IF;
-- request limit
IF reqinfo.request_limit = 'Y' THEN
return('Concurrent: Active Request Limit is set. Waiting on other requests for this user.');
END IF;
END IF;
-- well, could be released, but waiting on a manager
r := chknormal(reqinfo.request_id);
IF substr(r, 1) = 'N' THEN
return r;
END IF;
-- could be just waiting on the CRM
return('Pending Standby, probably waiting on the CRM');
END IF;
-- give up
return('No idea');
END analyzereq;
PROCEDURE analyzeall AS
cnt number := 1;
CURSOR c_reqs IS
SELECT request_id FROM fnd_concurrent_requests
WHERE phase_code = 'P'
ORDER BY request_id;
BEGIN
FOR rid in c_reqs LOOP
DBMS_OUTPUT.PUT_LINE(cnt || ') ' || rid.request_id || ': ' || analyzereq(rid.request_id));
cnt := cnt + 1;
END LOOP;
END analyzeall;
BEGIN
dbms_output.enable(2000000);
DBMS_OUTPUT.PUT_LINE('Analyzing all Pending requests');
DBMS_OUTPUT.PUT_LINE('-----------------------------------------');
analyzeall;
END;
/
No comments:
Post a Comment