Oracle EBS:自动创建发货

procedure SHIPCONFIRM(P_ORG_ID number, P_SO_NUMBER VARCHAR) IS
    X_ORG_ID        NUMBER := P_ORG_ID;
    X_SO_NUMBER     VARCHAR2(1000) := P_SO_NUMBER;
    X_RETURN_STATUS VARCHAR2(1000);
    X_MSG_COUNT     NUMBER;
    X_MSG_DATA      VARCHAR2(1000);
    X_DELIVERY_ID   NUMBER;
  
    CURSOR C_D_DETAIL(P_ORG_ID NUMBER, P_SO_NUMBER VARCHAR2) IS
      Select WDD.DELIVERY_DETAIL_ID
        FROM OE_ORDER_LINES_ALL   OOL,
             OE_ORDER_HEADERS_ALL OOH,
             WSH_DELIVERY_DETAILS WDD
       Where OOL.LINE_ID = WDD.SOURCE_LINE_ID
         AND OOH.HEADER_ID = OOL.HEADER_ID
         AND OOH.ORG_ID = OOL.ORG_ID
         AND OOH.ORG_ID = P_ORG_ID
         AND OOH.ORDER_NUMBER = P_SO_NUMBER
         AND RELEASED_STATUS = 'R' -- READY TO RELEASE
       orDER BY WDD.DELIVERY_DETAIL_ID;
  
    V_COUNTER NUMBER;
    -- STANDARD PARAMETERS.
    P_API_VERSION_NUMBER NUMBER;
    INIT_MSG_LIST        VARCHAR2(30);
    X_MSG_DETAILS        VARCHAR2(3000);
    X_MSG_SUMMARY        VARCHAR2(3000);
    P_VALIDATION_LEVEL   NUMBER;
    COMMIT               VARCHAR2(30);
    -- PARAMETERS FOR WSH_DELIVERIES_PUB.Create_Update_DELIVERY
    ACTION_CODE   VARCHAR2(15);
    DELIVERY_INFO WSH_DELIVERIES_PUB.DELIVERY_PUB_REC_TYPE;
    NAME          VARCHAR2(30);
    -- PARAMETERS FOR WSH_DELIVERY_DETAILS_PUB.DETAIL_TO_DELIVERY
    P_DELIVERY_ID  NUMBER;
    DELIVERY_NAME  VARCHAR2(30);
    P_TABOFDELDETS WSH_DELIVERY_DETAILS_PUB.ID_TAB_TYPE;
    P_ACTION       VARCHAR2(30);
    -- PARAMETERS FOR WSH_DELIVERIES_PUB.DELIVERY_ACTION.
    P_ACTION_CODE VARCHAR2(15);
  
    FAIL_API EXCEPTION;
  BEGIN
    -- INITIALIZE RETURN STATUS
    X_RETURN_STATUS := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
  
    --初始化设置
    fnd_global.apps_initialize(520247, 1293, 2011);
  
    -- GET VALUES FOR WSH_DELIVERIES_PUB.Create_Update_DELIVERY
    -- Create ANEW DELIVERY BASED ON SALES orDER NUMBER
    DBMS_OUTPUT.PUT_LINE('START1');
  
    BEGIN
      Select OOH.SOLD_TO_ORG_ID --CUSTOMER_ID
            ,
             OOL.SHIP_FROM_ORG_ID --WAREHOUSE_ID
            ,
             HOU.LOCATION_ID --SHIP_FROM_LOC_ID
            ,
             HPS.LOCATION_ID --SHIP_TO_LOC_ID
            ,
             OOH.FOB_POINT_CODE --FOB
            ,
             OOH.SHIPPING_METHOD_CODE --SHIPPING_METHOD
        INTO DELIVERY_INFO.CUSTOMER_ID,
             DELIVERY_INFO.ORGANIZATION_ID,
             DELIVERY_INFO.INITIAL_PICKUP_LOCATION_ID,
             DELIVERY_INFO.ULTIMATE_DropOFF_LOCATION_ID,
             DELIVERY_INFO.FOB_CODE,
             DELIVERY_INFO.SHIP_METHOD_CODE
        FROM OE_ORDER_LINES_ALL       OOL,
             OE_ORDER_HEADERS_ALL     OOH,
             WSH_DELIVERY_DETAILS     WDD,
             WSH_DELIVERY_ASSIGNMENTS WDA,
             HR_ORGANIZATION_UNITS_V  HOU,
             HZ_CUST_SITE_USES_ALL    HCS,
             HZ_CUST_ACCT_SITES_ALL   HCA,
             HZ_PARTY_SITES           HPS
       Where OOL.LINE_ID = WDD.SOURCE_LINE_ID
         AND OOH.HEADER_ID = OOL.HEADER_ID
         AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID(+)
         AND WDA.DELIVERY_ID IS NULL
         AND OOL.SHIP_FROM_ORG_ID = HOU.ORGANIZATION_ID
         AND OOH.SHIP_TO_ORG_ID = HCS.SITE_USE_ID
         AND HCS.CUST_ACCT_SITE_ID = HCA.CUST_ACCT_SITE_ID
         AND HCA.PARTY_SITE_ID = HPS.PARTY_SITE_ID
         AND OOH.ORG_ID = OOL.ORG_ID
         AND OOH.ORG_ID = X_ORG_ID
         AND OOH.ORDER_NUMBER = X_SO_NUMBER
         AND ROWNUM = 1;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('SQL ERROR WHEN GET DELIVERY INFORMATION!');
        RAISE FAIL_API;
    END;
  
    --   DELIVERY_INFO.FOB_CODE := 'FOB';
    --   DELIVERY_INFO.WEIGHT_UOM_CODE := 'GM';
    --   DELIVERY_INFO.VOLUME_UOM_CODE := 'L';
    --   DELIVERY_INFO.INITIAL_PICKUP_LOCATION_ID := 228;  -- SHIP FROM (PMD)
    --   DELIVERY_INFO.ULTIMATE_DropOFF_LOCATION_ID := 416; --SHIP_TO MIC
    --   DELIVERY_INFO.GROSS_WEIGHT := 10;
    --   DELIVERY_INFO.SHIP_METHOD_CODE := NULL;
    --   DELIVERY_INFO.ORGANIZATION_ID := 169;
    --   DELIVERY_INFO.CUSTOMER_ID := 16049; --MIC
    --   DELIVERY_INFO.ATTRIBUTE10 := 'D2PS';
    P_ACTION_CODE := 'Create';
    -- CALL TO WSH_DELIVERIES_PUB.Create_Update_DELIVERY
    DBMS_OUTPUT.PUT_LINE('BEGIN Create DELIVERY.');
    WSH_DELIVERIES_PUB.Create_Update_DELIVERY(P_API_VERSION_NUMBER => 1.0,
                                              P_INIT_MSG_LIST      => INIT_MSG_LIST,
                                              X_RETURN_STATUS      => X_RETURN_STATUS,
                                              X_MSG_COUNT          => X_MSG_COUNT,
                                              X_MSG_DATA           => X_MSG_DATA,
                                              P_ACTION_CODE        => P_ACTION_CODE,
                                              P_DELIVERY_INFO      => DELIVERY_INFO,
                                              P_DELIVERY_NAME      => DELIVERY_NAME,
                                              X_DELIVERY_ID        => X_DELIVERY_ID,
                                              X_NAME               => NAME);
  
    IF (X_RETURN_STATUS <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
      RAISE FAIL_API;
    END IF;
  
    --   DBMS_OUTPUT.PUT_LINE ('X_RETURN_STATUS: '||X_RETURN_STATUS);
    --   DBMS_OUTPUT.PUT_LINE ('DELIVERY: '||X_DELIVERY_ID);
    -- VALUES FOR WSH_DELIVERY_DETAILS_PUB.DETAIL_TO_DELIVERY
    P_DELIVERY_ID := X_DELIVERY_ID;
    DBMS_OUTPUT.PUT_LINE(' P_DELIVERY_ID ' || P_DELIVERY_ID);
  
    BEGIN
      V_COUNTER := 1;
    
      --   FOR C1 IN C_D_DETAIL(X_SO_NUMBER) LOOP
      FOR C1 IN C_D_DETAIL(X_ORG_ID, X_SO_NUMBER) LOOP
        --     DBMS_OUTPUT.PUT_LINE ('P_TABOFDELDETS('||V_COUNTER||') = '||C1.DELIVERY_DETAIL_ID);
        P_TABOFDELDETS(V_COUNTER) := C1.DELIVERY_DETAIL_ID;
        V_COUNTER := V_COUNTER + 1;
      END LOOP;
    END;
  
    P_ACTION := 'ASSIGN';
    -- CALL TO WSH_DELIVERY_DETAILS_PUB.DETAIL_TO_DELIVERY.
    WSH_DELIVERY_DETAILS_PUB.DETAIL_TO_DELIVERY(P_API_VERSION      => 1.0,
                                                P_INIT_MSG_LIST    => INIT_MSG_LIST,
                                                P_COMMIT           => COMMIT,
                                                P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
                                                X_RETURN_STATUS    => X_RETURN_STATUS,
                                                X_MSG_COUNT        => X_MSG_COUNT,
                                                X_MSG_DATA         => X_MSG_DATA,
                                                P_TABOFDELDETS     => P_TABOFDELDETS,
                                                P_ACTION           => P_ACTION,
                                                P_DELIVERY_ID      => P_DELIVERY_ID,
                                                P_DELIVERY_NAME    => DELIVERY_NAME);
  
    IF (X_RETURN_STATUS <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
      RAISE FAIL_API;
    END IF;
  
    OE_DEBUG_PUB.ADD('END DETAIL LINES ASSIGNMENT.', 1);
    COMMIT;
  EXCEPTION
    WHEN FAIL_API THEN
      WSH_UTIL_CORE.GET_MESSAGES('Y',
                                 X_MSG_SUMMARY,
                                 X_MSG_DETAILS,
                                 X_MSG_COUNT);
    
      IF X_MSG_COUNT > 1 THEN
        X_MSG_DATA := X_MSG_SUMMARY || X_MSG_DETAILS;
      ELSE
        X_MSG_DATA := X_MSG_SUMMARY;
      END IF;
    
      DBMS_OUTPUT.PUT_LINE(' X_MSG_DATA ' || X_MSG_DATA);
    
  END SHIPCONFIRM;