Aloz 的 Oracle ERP 天地

最近都在做 IR/ISO Automation 的程式, 因為 Pick Release 很難去要求系統 Pick 到指定的 Lot (就目前的了解, 最多就是指定到 Subinv. 與 Locator, 如果裡面有多筆資料, 就沒辦法了), 所以就來做 Item Reservation 吧...

注意事項先寫在前面:

1. 下面看到的所有欄位都要放, 就算是給 NULL 也要放 @@ 因為 API 會一個一個檢查

2. INV_RESERVATION_GLOBAL 有設定 Supply Source Type ID 與 Demand Source Type ID 的常數, 沒什麼特別的情況, 就使用標準的方式給值, 而不要 Hard Code

INV_RESERVATION_GLOBAL 常數參考

G_SOURCE_TYPE_PO CONSTANT NUMBER := 1 ;
G_SOURCE_TYPE_OE CONSTANT NUMBER := 2 ;
G_SOURCE_TYPE_ACCOUNT CONSTANT NUMBER := 3 ;
G_SOURCE_TYPE_TRANS_ORDER CONSTANT NUMBER := 4 ;
G_SOURCE_TYPE_WIP CONSTANT NUMBER := 5 ;
G_SOURCE_TYPE_ACCOUNT_ALIAS CONSTANT NUMBER := 6 ;
G_SOURCE_TYPE_INTERNAL_REQ CONSTANT NUMBER := 7 ; ==> Internal Requisition
G_SOURCE_TYPE_INTERNAL_ORD CONSTANT NUMBER := 8 ; ==> Internal Order
G_SOURCE_TYPE_CYCLE_COUNT CONSTANT NUMBER := 9 ;
G_SOURCE_TYPE_PHYSICAL_INV CONSTANT NUMBER := 10;
G_SOURCE_TYPE_STANDARD_COST CONSTANT NUMBER := 11;
G_SOURCE_TYPE_RMA CONSTANT NUMBER := 12;
G_SOURCE_TYPE_INV CONSTANT NUMBER := 13;
G_SOURCE_TYPE_REQ CONSTANT NUMBER := 17;

3. DEMAND_SOURCE_HEADER_ID 並不是 OE_ORDER_LINES_ALL 中的 HEADER_ID, 而是 MTL_SALES_ORDERS 裡的 SALES_ORDER_ID, 可以用 SEGMENT1 = [Sales Order Number] 來查詢. DEMAND_SOURCE_LINE_ID 是 OE_ORDER_LINES_ALL 中的 LINE_ID 沒錯

程式碼:

DECLARE
  X_MSG_COUNT NUMBER;
  X_MSG_DATA VARCHAR2 (2000);
  P_RSV INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
  P_DUMMY_SN INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
  X_RSV_ID NUMBER;
  X_DUMMY_SN INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
  X_STATUS VARCHAR2 (1);
  X_QTY NUMBER;
  V_MSG_INDEX_OUT VARCHAR2 (20); 
BEGIN
  P_RSV.REQUIREMENT_DATE := SYSDATE;
  P_RSV.ORGANIZATION_ID := 116;
  P_RSV.SUPPLY_SOURCE_TYPE_ID := INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_INV;
  P_RSV.DEMAND_SOURCE_TYPE_ID := INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_INTERNAL_ORD; -- Internal Order
  P_RSV.DEMAND_SOURCE_NAME := NULL; --'XXAMB WIP Interface';
  P_RSV.DEMAND_SOURCE_HEADER_ID := 41004; -- sales_order_id of mtl_sales_orders table
  P_RSV.DEMAND_SOURCE_LINE_ID := 74023; -- line_id of oe_order_lines_all table
  P_RSV.SUBINVENTORY_CODE := 'FGI';
  P_RSV.LOCATOR_ID := NULL; 
  P_RSV.INVENTORY_ITEM_ID := 1090;
  P_RSV.PRIMARY_UOM_CODE := 'Ea';
  P_RSV.PRIMARY_UOM_ID := NULL;
  P_RSV.RESERVATION_UOM_CODE := 'Ea';
  P_RSV.RESERVATION_UOM_ID := NULL;
  P_RSV.LOT_NUMBER := 'AAA';
  P_RSV.RESERVATION_QUANTITY := 140;
  P_RSV.PRIMARY_RESERVATION_QUANTITY := 140;
  P_RSV.SHIP_READY_FLAG := NULL; 
  P_RSV.DEMAND_SOURCE_DELIVERY := NULL; 
  P_RSV.attribute_category := NULL; 
  P_RSV.attribute1 := NULL; 
  P_RSV.attribute2 := NULL; 
  P_RSV.attribute3 := NULL; 
  P_RSV.attribute4 := NULL; 
  P_RSV.attribute5 := NULL; 
  P_RSV.attribute6 := NULL; 
  P_RSV.attribute7 := NULL; 
  P_RSV.attribute8 := NULL; 
  P_RSV.attribute9 := NULL; 
  P_RSV.attribute10 := NULL; 
  P_RSV.attribute11 := NULL; 
  P_RSV.attribute12 := NULL; 
  P_RSV.attribute13 := NULL; 
  P_RSV.attribute14 := NULL; 
  P_RSV.ATTRIBUTE15 := NULL; 
  P_RSV.LPN_ID := NULL; 
  P_RSV.PICK_SLIP_NUMBER := NULL; 
  P_RSV.LOT_NUMBER_ID := NULL; 
  P_RSV.SUBINVENTORY_ID := NULL; 
  P_RSV.REVISION := NULL;
  p_rsv.supply_source_header_id := NULL; 
  p_RSV.SUPPLY_SOURCE_LINE_ID := NULL; 
  p_rsv.supply_source_name := NULL; 
  p_rsv.supply_source_line_detail := NULL; 
  P_RSV.AUTODETAIL_GROUP_ID := NULL; 
  P_RSV.EXTERNAL_SOURCE_CODE := NULL; 
  p_rsv.external_source_line_id := NULL; 
  
  INV_RESERVATION_PUB.CREATE_RESERVATION(
      P_API_VERSION_NUMBER        => 1.0
    , P_RSV_REC                   => P_RSV
    , P_SERIAL_NUMBER             => P_DUMMY_SN
    , P_PARTIAL_RESERVATION_FLAG  => FND_API.G_TRUE 
    , P_FORCE_RESERVATION_FLAG    => FND_API.G_FALSE 
    , P_VALIDATION_FLAG           => FND_API.G_TRUE 
    , X_RETURN_STATUS             => X_STATUS
    , X_MSG_COUNT                 => X_MSG_COUNT
    , X_MSG_DATA                  => X_MSG_DATA
    , X_SERIAL_NUMBER             => X_DUMMY_SN
    , X_QUANTITY_RESERVED         => X_QTY
    , X_RESERVATION_ID            => X_RSV_ID
  );
  --
  dbms_output.put_line('** Item Reservation Return Status = ' || X_STATUS || ' **');
  --
  IF (X_STATUS <> FND_API.G_RET_STS_SUCCESS) THEN
    dbms_output.put_line('XXXXX Failed to item reservation XXXXX');
    --
    for J in 1 .. FND_MSG_PUB.COUNT_MSG LOOP
      FND_MSG_PUB.GET (P_MSG_INDEX => J , P_ENCODED => 'F' , P_DATA => X_MSG_DATA , P_MSG_INDEX_OUT => V_MSG_INDEX_OUT );
    end LOOP;
    
    dbms_output.put_line('The Required Quantity is not allocated: ' || X_MSG_DATA);
    --
    --RAISE EXEP_API;
  ELSE
    dbms_output.put_line('VVV Item Reservation Action has successfully completed, X_RESERVATION_ID: ' || X_RSV_ID || ' VVV');
  END IF;
END;