CREATE OR REPLACE PROCEDURE CUX_OM_LINE_BOOKEDFIX_P(ERRBUF OUT VARCHAR2, RETCODE OUT NUMBER) IS
/* $Header: cux_om_line_bookedfix_p 2012-10-23 11:40:01 JD ship $ */
/**************************************************************************
REM Copyright (c) 2012 JD Corporation China.
REM All rights reserved.
REM ***********************************************************************
REM File name : cux_om_line_bookedfix_p
REM Doc Ref(s) :
REM Project :
REM Description : It is use for fix the om line which the line status is BOOOKED.
REM
REM Change History Information
REM --------------------------
REM Version Date Author Change Reference / Description
REM ------- ----------- ------------------- ------------------------------------
REM **************************************************************************/
CURSOR ROWS_TO_FIX IS
SELECT H.ORDER_NUMBER,
H.HEADER_ID,
L.LINE_NUMBER || '.' || L.SHIPMENT_NUMBER LINE_NUMBER,
L.LINE_ID
FROM OE_ORDER_HEADERS_ALL H, OE_ORDER_LINES_ALL L
WHERE H.HEADER_ID = L.HEADER_ID
AND NVL (L.SHIPPED_QUANTITY, 0) = 0
AND NVL (L.SHIPPING_QUANTITY, 0) = 0
AND L.BOOKED_FLAG = 'Y'
AND L.OPEN_FLAG = 'Y'
AND L.SHIPPABLE_FLAG = 'Y'
AND L.FLOW_STATUS_CODE = 'BOOKED';
L_STATUS VARCHAR2(60);
L_ACTIVITY_ID NUMBER;
L_RESULT VARCHAR2(1000);
L_FILE_NAME VARCHAR2(1000);
L_DB_NAME VARCHAR2(1000);
L_HOLD_FLAG NUMBER;
L_WF_FLAG NUMBER;
L_DEBUG_LEVEL CONSTANT NUMBER := OE_DEBUG_PUB.G_DEBUG_LEVEL;
BEGIN
-- Setup debugging
IF L_DEBUG_LEVEL > 0
THEN
OE_DEBUG_PUB.DEBUG_ON;
OE_DEBUG_PUB.INITIALIZE;
OE_DEBUG_PUB.SETDEBUGLEVEL(L_DEBUG_LEVEL);
L_FILE_NAME := OE_DEBUG_PUB.SET_DEBUG_MODE('FILE');
/*dbms_output.put_line('Debug log is located at: ' || oe_debug_pub.g_dir || '/' ||
oe_debug_pub.g_file);*/
FND_FILE.PUT_LINE(FND_FILE.LOG,
'Debug log is located at: ' || OE_DEBUG_PUB.G_DIR || '/' || OE_DEBUG_PUB.G_FILE);
SELECT NAME INTO L_DB_NAME FROM V$DATABASE;
OE_DEBUG_PUB.ADD('Running for databse: ' || L_DB_NAME);
END IF;
-- Perform data correction
FOR I IN ROWS_TO_FIX LOOP
IF L_DEBUG_LEVEL > 0
THEN
OE_DEBUG_PUB.ADD('Fetching the error l_line_id:' || I.LINE_ID || ' ' || I.ORDER_NUMBER);
OE_DEBUG_PUB.ADD('Setting Context for line:' || I.LINE_ID || ' ' || I.LINE_NUMBER);
END IF;
--check hold
SELECT COUNT(1)
INTO L_HOLD_FLAG
FROM OE_ORDER_HOLDS_ALL
WHERE HEADER_ID = I.HEADER_ID
AND LINE_ID = I.LINE_ID
AND HOLD_RELEASE_ID IS NULL;
IF L_DEBUG_LEVEL > 0
THEN
OE_DEBUG_PUB.ADD('line holded: ' || L_HOLD_FLAG);
END IF;
IF L_HOLD_FLAG = 0
THEN
SELECT COUNT(1)
INTO L_HOLD_FLAG
FROM OE_ORDER_HOLDS_ALL
WHERE HEADER_ID = I.HEADER_ID
AND LINE_ID IS NULL
AND HOLD_RELEASE_ID IS NULL;
IF L_DEBUG_LEVEL > 0
THEN
OE_DEBUG_PUB.ADD('header holded: ' || L_HOLD_FLAG);
END IF;
END IF;
--check wf
SELECT COUNT(1)
INTO L_WF_FLAG
FROM WF_ITEMS
WHERE ITEM_TYPE = 'OEOL'
AND ITEM_KEY = TO_CHAR(I.LINE_ID);
IF L_DEBUG_LEVEL > 0
THEN
OE_DEBUG_PUB.ADD('wf line flag: ' || L_WF_FLAG);
END IF;
IF L_HOLD_FLAG = 0 AND L_WF_FLAG > 0
THEN
OE_STANDARD_WF.OEOL_SELECTOR(P_ITEMTYPE => 'OEOL',
P_ITEMKEY => TO_CHAR(I.LINE_ID),
P_ACTID => 12345,
P_FUNCMODE => 'SET_CTX',
P_RESULT => L_RESULT);
IF L_DEBUG_LEVEL > 0
THEN
OE_DEBUG_PUB.ADD('Result: ' || L_RESULT);
OE_DEBUG_PUB.ADD('Calling handleerror for line...');
END IF;
WF_ENGINE.HANDLEERROR(ITEMTYPE => 'OEOL',
ITEMKEY => TO_CHAR(I.LINE_ID),
ACTIVITY => 'SHIP_LINE',
COMMAND => 'RETRY',
RESULT => NULL);
IF L_DEBUG_LEVEL > 0
THEN
OE_DEBUG_PUB.ADD('SHIP_LINE Retried...');
END IF;
SELECT WFS.ACTIVITY_STATUS,
WFS.PROCESS_ACTIVITY
INTO L_STATUS,
L_ACTIVITY_ID
FROM WF_PROCESS_ACTIVITIES WFA,
WF_ITEM_ACTIVITY_STATUSES WFS
WHERE WFA.INSTANCE_ID = WFS.PROCESS_ACTIVITY
AND WFS.ITEM_KEY = TO_CHAR(I.LINE_ID)
AND WFA.ACTIVITY_NAME = 'SHIP_LINE';
IF (L_STATUS <> 'NOTIFIED')
THEN
OE_STANDARD_WF.OEOL_SELECTOR(P_ITEMTYPE => 'OEOL',
P_ITEMKEY => TO_CHAR(I.LINE_ID),
P_ACTID => 12345,
P_FUNCMODE => 'SET_CTX',
P_RESULT => L_RESULT);
IF L_DEBUG_LEVEL > 0
THEN
OE_DEBUG_PUB.ADD('Create status ..');
END IF;
WF_ITEM_ACTIVITY_STATUS.CREATE_STATUS('OEOL',
TO_CHAR(I.LINE_ID),
L_ACTIVITY_ID,
WF_ENGINE.ENG_NOTIFIED,
WF_ENGINE.ENG_NULL,
SYSDATE,
NULL);
IF L_DEBUG_LEVEL > 0
THEN
OE_DEBUG_PUB.ADD('Status Created ..');
OE_DEBUG_PUB.ADD('Next line ..');
END IF;
END IF;
COMMIT;
ELSE
IF L_DEBUG_LEVEL > 0
THEN
OE_DEBUG_PUB.ADD('Line can' || CHR(39) || 't shipped ..');
OE_DEBUG_PUB.ADD('Next line ..');
END IF;
END IF;
END LOOP;
-- Finishing script with success
IF L_DEBUG_LEVEL > 0
THEN
OE_DEBUG_PUB.ADD('Script succesfully executed.');
OE_DEBUG_PUB.DEBUG_OFF;
END IF;
EXCEPTION
WHEN OTHERS THEN
IF L_DEBUG_LEVEL > 0
THEN
OE_DEBUG_PUB.ADD('Error: ...' || SQLERRM);
END IF;
/*dbms_output.put_line('Error: ...' || sqlerrm);*/
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: ...' || SQLERRM);
ROLLBACK;
END CUX_OM_LINE_BOOKEDFIX_P;
OM LINE_BOOKED 改成awaiting Shipping(往回推workflow)
最新推荐文章于 2024-01-30 11:11:47 发布