PO單處於“處理中”狀態

gsslww

原帖由 comtony 于 2006-9-9 23:43 发表
如查处理工作流还不能审批通过，那可以考虑将原来的工作流清掉，再让PO的提交者从新提交审批，下面这个SQL可以实现，这个SQL应该在这个坛子里有的，我之前下载过，现在重新贴上来

set serveroutput on size 100000
DECLARE

CURSOR potoreset is
SELECT wf_item_type, wf_item_key, po_header_id, segment1,
revision_num, type_lookup_code
FROM po_headers_all
WHERE segment1 = '&po_number'
and org_id = &org_id
and authorization_status IN ('IN PROCESS', 'PRE-APPROVED')
and NVL(cancel_flag, 'N') = 'N'
and NVL(closed_code, 'OPEN') != 'FINALLY_CLOSED';

CURSOR maxseq(id number, subtype po_action_history.object_sub_type_code%type) is
SELECT nvl(max(sequence_num), 0)
FROM po_action_history
WHERE object_type_code IN ('PO', 'PA')
AND object_sub_type_code = subtype
AND object_id = id
AND action_code is NULL;

CURSOR poaction(id number, subtype po_action_history.object_sub_type_code%type) is
SELECT nvl(max(sequence_num), 0)
FROM po_action_history
WHERE object_type_code IN ('PO', 'PA')
AND object_sub_type_code = subtype
AND object_id = id
AND action_code = 'SUBMIT';

submitseq po_action_history.sequence_num%type;
nullseq po_action_history.sequence_num%type;

BEGIN
FOR pos in potoreset LOOP
dbms_output.put_line('Processing '||pos.type_lookup_code
||' PO Number: '
||pos.segment1);
dbms_output.put_line('......................................');

dbms_output.put_line('Closing Notifications...');
BEGIN

UPDATE wf_notifications set status = 'CANCELED'
WHERE notification_id in (
select ias.notification_id
from wf_item_activity_statuses ias,
wf_notifications ntf
where ias.item_type = pos.wf_item_type
and ias.item_key = pos.wf_item_key
and ntf.notification_id = ias.notification_id)
AND NVL(status, 'OPEN') = 'OPEN';

EXCEPTION
WHEN OTHERS THEN
null;
END;

dbms_output.put_line('Aborting Workflow...');
BEGIN
WF_Engine.AbortProcess(pos.wf_item_type, pos.wf_item_key);
EXCEPTION
WHEN OTHERS THEN
null;
END;

dbms_output.put_line('Updating PO Status...');
UPDATE po_headers_all
set authorization_status = decode(pos.revision_num, 0, 'INCOMPLETE',
'REQUIRES REAPPROVAL'),
wf_item_type = NULL,
wf_item_key = NULL
where po_header_id = pos.po_header_id;

OPEN maxseq(pos.po_header_id, pos.type_lookup_code);
FETCH maxseq into nullseq;
CLOSE maxseq;

OPEN poaction(pos.po_header_id, pos.type_lookup_code);
FETCH poaction into submitseq;
CLOSE poaction;

IF nullseq > submitseq THEN

dbms_output.put_line('Deleting PO Action History...');

DELETE FROM po_action_history
WHERE object_id = pos.po_header_id
AND object_type_code IN ('PO', 'PA')
AND object_sub_type_code = pos.type_lookup_code
AND sequence_num >= submitseq;
END IF;

dbms_output.put_line('Done Processing.');
dbms_output.put_line('................');
dbms_output.put_line('Please issue commit, if no errors found.');

END LOOP;
END;
/

您好，能解釋下為什麽closed_code 會為NULL嗎，trm中似乎為提及NULL的狀態，謝謝指教，學習

xxzhaobb

以前秀才专门开过这个帖子。