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

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;