OM LINE_BOOKED 改成awaiting Shipping(往回推workflow)

来源：互联网发布：怎么联系淘宝小二编辑：程序博客网时间：2024/05/18 05:24
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 ISSELECT   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;
0 0