Oracle eBS經驗分享

資料來源：http://erpschools.com/Apps/oracle-applications/scripts/Supply-Chain-Management/Inventory/Get-On-Hand-Quantities-through-API/index.aspx#

Get On Hand Quantities through API

This script can be used to get the below quantities. 1. On-hand Quantity 2. Available to Reserve 3. Quantity Reserved 4. Quantity Suggested 5. Available to Transact 6. Available to Reserve You can also get the On-hand quantities from the table mtl_onhand_quantities

DECLARE
x_return_status      VARCHAR2 (50);
x_msg_count          VARCHAR2 (50);
x_msg_data            VARCHAR2 (50);
v_item_id               NUMBER;
v_org_id                NUMBER; 
v_qoh                   NUMBER;
v_rqoh                  NUMBER;
v_atr                   NUMBER;
v_att                   NUMBER;
v_qr                    NUMBER;
v_qs                    NUMBER;
v_lot_control_code      BOOLEAN;
v_serial_control_code   BOOLEAN;
BEGIN 
  -- Set the variable values 
  v_item_id := 12758; 
  v_org_id := 91; 
  v_qoh := NULL; 
  v_rqoh := NULL; 
  v_atr := NULL; 
  v_lot_control_code := FALSE; 
  v_serial_control_code := FALSE; 
  -- Set the org context 
  fnd_client_info.set_org_context (1); 
  -- Call API 
  inv_quantity_tree_pub.query_quantities (p_api_version_number = 1.0
   , p_init_msg_lst             ='F' 
   , x_return_status            =x_return_status 
   , x_msg_count                =x_msg_count 
   , x_msg_data                 =x_msg_data 
   , p_organization_id          =v_org_id 
   , p_inventory_item_id        =v_item_id 
   , p_tree_mode                =apps.inv_quantity_tree_pub.g_transaction_mode -- or 3 
   , p_is_revision_control      =FALSE 
   , p_is_lot_control           =v_lot_control_code-- is_lot_control, 
   , p_is_serial_control        =v_serial_control_code 
   , p_revision                 =NULL   -- p_revision, 
   , p_lot_number               =NULL   -- p_lot_number, 
   , p_lot_expiration_date      =SYSDATE 
   , p_subinventory_code        =NULL   -- p_subinventory_code, 
   , p_locator_id               =NULL   -- p_locator_id, 
   -- p_cost_group_id           =NULL,  -- cg_id, 
   , p_onhand_source            =3 
   , x_qoh                      =v_qoh  -- Quantity on-hand 
   , x_rqoh                     =v_rqoh --reservable quantity on-hand 
   , x_qr                       =v_qr 
   , x_qs                       =v_qs 
   , x_att                      =v_att  -- available to transact 
   , x_atr                      =v_atr   -- available to reserve 
     ); 
  DBMS_OUTPUT.put_line ('On-Hand Quantity: ' || v_qoh); 
  DBMS_OUTPUT.put_line ('Available to reserve: ' || v_atr); 
  DBMS_OUTPUT.put_line ('Quantity Reserved: ' || v_qr); 
  DBMS_OUTPUT.put_line ('Quantity Suggested: ' || v_qs); 
  DBMS_OUTPUT.put_line ('Available to Transact: ' || v_att); 
  DBMS_OUTPUT.put_line ('Available to Reserve: ' || v_atr); 
EXCEPTION 
  WHEN OTHERS 
  THEN
    DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);
END;