Sales Order Line Update API (OE_ORDER_PUB.PROCESS_ORDER)－Aloz 的 Oracle ERP 天地

直接就貼 Sample Code 上來 (以 Update "Schedule Ship Date" 為例)

DECLARE
  L_USER_ID NUMBER;
  L_RESP_ID NUMBER;
  L_APPL_ID NUMBER;
  L_HEADER_REC_IN OE_ORDER_PUB.HEADER_REC_TYPE;
  L_LINE_TBL_IN OE_ORDER_PUB.LINE_TBL_TYPE;
  L_ACTION_REQUEST_TBL_IN OE_ORDER_PUB.REQUEST_TBL_TYPE;
  L_HEADER_REC_OUT OE_ORDER_PUB.HEADER_REC_TYPE;
  L_LINE_TBL_OUT OE_ORDER_PUB.LINE_TBL_TYPE;
  L_HEADER_VAL_REC_OUT OE_ORDER_PUB.HEADER_VAL_REC_TYPE;
  L_HEADER_ADJ_TBL_OUT OE_ORDER_PUB.HEADER_ADJ_TBL_TYPE;
  L_HEADER_ADJ_VAL_TBL_OUT OE_ORDER_PUB.HEADER_ADJ_VAL_TBL_TYPE;
  L_HEADER_PRICE_ATT_TBL_OUT OE_ORDER_PUB.HEADER_PRICE_ATT_TBL_TYPE;
  L_HEADER_ADJ_ATT_TBL_OUT OE_ORDER_PUB.HEADER_ADJ_ATT_TBL_TYPE;
  L_HEADER_ADJ_ASSOC_TBL_OUT OE_ORDER_PUB.HEADER_ADJ_ASSOC_TBL_TYPE;
  L_HEADER_SCREDIT_TBL_OUT OE_ORDER_PUB.HEADER_SCREDIT_TBL_TYPE;
  L_HEADER_SCREDIT_VAL_TBL_OUT OE_ORDER_PUB.HEADER_SCREDIT_VAL_TBL_TYPE;
  L_LINE_VAL_TBL_OUT OE_ORDER_PUB.LINE_VAL_TBL_TYPE;
  L_LINE_ADJ_TBL_OUT OE_ORDER_PUB.LINE_ADJ_TBL_TYPE;
  L_LINE_ADJ_VAL_TBL_OUT OE_ORDER_PUB.LINE_ADJ_VAL_TBL_TYPE;
  L_LINE_PRICE_ATT_TBL_OUT OE_ORDER_PUB.LINE_PRICE_ATT_TBL_TYPE;
  L_LINE_ADJ_ATT_TBL_OUT OE_ORDER_PUB.LINE_ADJ_ATT_TBL_TYPE;
  L_LINE_ADJ_ASSOC_TBL_OUT OE_ORDER_PUB.LINE_ADJ_ASSOC_TBL_TYPE;
  L_LINE_SCREDIT_TBL_OUT OE_ORDER_PUB.LINE_SCREDIT_TBL_TYPE;
  L_LINE_SCREDIT_VAL_TBL_OUT OE_ORDER_PUB.LINE_SCREDIT_VAL_TBL_TYPE;
  L_LOT_SERIAL_TBL_OUT OE_ORDER_PUB.LOT_SERIAL_TBL_TYPE;
  L_LOT_SERIAL_VAL_TBL_OUT OE_ORDER_PUB.LOT_SERIAL_VAL_TBL_TYPE;
  L_ACTION_REQUEST_TBL_OUT OE_ORDER_PUB.REQUEST_TBL_TYPE;
  L_CHR_PROGRAM_UNIT_NAME VARCHAR2 (100);
  L_CHR_RET_STATUS        VARCHAR2 (1000) := NULL;
  L_MSG_COUNT             NUMBER := 0;
  L_MSG_DATA              VARCHAR2 (2000);
  L_NUM_API_VERSION       NUMBER := 1.0;
  
  CURSOR C_SO_DETAILS IS
    SELECT  OH.HEADER_ID
          , OL.LINE_ID
          , OH.ORDER_NUMBER AS SO_NUMBER
          , MSI.SEGMENT1
          , OL.LINE_NUMBER || '.' || OL.SHIPMENT_NUMBER AS LINE
          , OL.INVENTORY_ITEM_ID
    FROM    OE_ORDER_HEADERS_ALL OH
          , OE_ORDER_LINES_ALL OL
          , MTL_SYSTEM_ITEMS MSI
    WHERE   0 = 0
    AND     OH.HEADER_ID = OL.HEADER_ID
    AND     OL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
    AND     MSI.ORGANIZATION_ID = OL.SHIP_FROM_ORG_ID
    AND     OL.LINE_ID = 1
    ;
BEGIN
  SELECT  USER_ID 
  INTO    L_USER_ID 
  FROM    FND_USER 
  WHERE   USER_NAME = 'ABC'
  ;
  
  SELECT  RESPONSIBILITY_ID
        , APPLICATION_ID
  INTO    L_RESP_ID
        , L_APPL_ID
  FROM    FND_RESPONSIBILITY_VL
  WHERE   RESPONSIBILITY_NAME LIKE 'Order Management Super User'
  ;
  
  FND_GLOBAL.APPS_INITIALIZE (L_USER_ID, L_RESP_ID, L_APPL_ID);
  
  FOR ISO_REC IN C_SO_DETAILS LOOP
    L_LINE_TBL_IN (1) := OE_ORDER_PUB.G_MISS_LINE_REC;
    
    L_LINE_TBL_IN (1).LINE_ID := ISO_REC.LINE_ID;
    L_LINE_TBL_IN (1).SCHEDULE_SHIP_DATE := SYSDATE; --TO_DATE(ISO_REC.UPDATE_SSD,'DD-MON-RRRR HH24:MI:SS');
    L_LINE_TBL_IN (1).OPERATION := OE_GLOBALS.G_OPR_UPDATE;
    
    OE_MSG_PUB.DELETE_MSG;
    
    OE_ORDER_PUB.PROCESS_ORDER (
        P_API_VERSION_NUMBER => L_NUM_API_VERSION
      , P_INIT_MSG_LIST => FND_API.G_FALSE
      , P_RETURN_VALUES => FND_API.G_FALSE
      , P_ACTION_COMMIT => FND_API.G_FALSE
      , P_LINE_TBL => L_LINE_TBL_IN
      , X_HEADER_REC => L_HEADER_REC_OUT
      , X_HEADER_VAL_REC => L_HEADER_VAL_REC_OUT
      , X_HEADER_ADJ_TBL => L_HEADER_ADJ_TBL_OUT
      , X_HEADER_ADJ_VAL_TBL => L_HEADER_ADJ_VAL_TBL_OUT
      , X_HEADER_PRICE_ATT_TBL => L_HEADER_PRICE_ATT_TBL_OUT
      , X_HEADER_ADJ_ATT_TBL => L_HEADER_ADJ_ATT_TBL_OUT
      , X_HEADER_ADJ_ASSOC_TBL => L_HEADER_ADJ_ASSOC_TBL_OUT
      , X_HEADER_SCREDIT_TBL => L_HEADER_SCREDIT_TBL_OUT
      , X_HEADER_SCREDIT_VAL_TBL => L_HEADER_SCREDIT_VAL_TBL_OUT
      , X_LINE_TBL => L_LINE_TBL_OUT
      , X_LINE_VAL_TBL => L_LINE_VAL_TBL_OUT
      , X_LINE_ADJ_TBL => L_LINE_ADJ_TBL_OUT
      , X_LINE_ADJ_VAL_TBL => L_LINE_ADJ_VAL_TBL_OUT
      , X_LINE_PRICE_ATT_TBL => L_LINE_PRICE_ATT_TBL_OUT
      , X_LINE_ADJ_ATT_TBL => L_LINE_ADJ_ATT_TBL_OUT
      , X_LINE_ADJ_ASSOC_TBL => L_LINE_ADJ_ASSOC_TBL_OUT
      , X_LINE_SCREDIT_TBL => L_LINE_SCREDIT_TBL_OUT
      , X_LINE_SCREDIT_VAL_TBL => L_LINE_SCREDIT_VAL_TBL_OUT
      , X_LOT_SERIAL_TBL => L_LOT_SERIAL_TBL_OUT
      , X_LOT_SERIAL_VAL_TBL => L_LOT_SERIAL_VAL_TBL_OUT
      , X_ACTION_REQUEST_TBL => L_ACTION_REQUEST_TBL_OUT
      , X_RETURN_STATUS => L_CHR_RET_STATUS
      , X_MSG_COUNT => L_MSG_COUNT
      , X_MSG_DATA => L_MSG_DATA
    );
    
    L_MSG_DATA := NULL;
    
    IF L_CHR_RET_STATUS <> 'S' THEN
      FOR IINDX IN 1 .. L_MSG_COUNT LOOP
        L_MSG_DATA := L_MSG_DATA || '  ' || OE_MSG_PUB.GET (IINDX);
      END LOOP;
    END IF;
    
    DBMS_OUTPUT.ENABLE (10000);
    DBMS_OUTPUT.PUT_LINE ('Sales Order => ' || ISO_REC.SO_NUMBER || ' - Line Number => ' || ISO_REC.LINE);
    DBMS_OUTPUT.PUT_LINE ('Return Status: ' || L_CHR_RET_STATUS);
    DBMS_OUTPUT.PUT_LINE ('Error Message: ' || L_MSG_DATA);
  END LOOP;
  
  COMMIT;
END;