PO状态为“处理中”的处理方法

EBS中经常会出现PO提交审批后状态为“处理中”的情况，此时PO创建人无法打开，审批人也无法打开，工作流等查看也无异常，可以使用一下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 = '14681' --'&po_number'
AND ORG_ID = 81--&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;

转载于:https://www.cnblogs.com/huak/p/3756515.html