Available to Transact/Reserve quantity API

Api  inv_quantity_tree_pub.query_quantities can be used in oracle applications to find Available to transact and reserve quantity. You can even find the available quantity by lots / subinventory.

Finding quantities by lots can be only achieved by setting the p_is_lot_control flag to TRUE along with lot_number else set it to FALSE.


set serveroutput on
DECLARE
   x_return_status         VARCHAR2 (50);
   x_msg_count             VARCHAR2 (50);
   x_msg_data              VARCHAR2 (50);
   l_item_id               NUMBER;
   l_organization_id       NUMBER;
   l_qty_on_hand           NUMBER;
   l_res_qty_on_hand       NUMBER;
   l_avail_to_tnsct        NUMBER;
   l_avail_to_reserve      NUMBER;
   l_qty_reserved          NUMBER;
   l_qty_suggested         NUMBER;
   l_lot_control_code      BOOLEAN;
   l_serial_control_code   BOOLEAN;
BEGIN
   -- Set the org context
    FND_GLOBAL.apps_initialize ( user_id => 1, resp_id => 64521, resp_appl_id => 401,security_group_id => 0);
   inv_quantity_tree_grp.clear_quantity_cache; -- Clear Quantity cache    
   -- Set the variable values
   l_item_id := 15141562;
   l_organization_id := 96;
   l_lot_control_code := FALSE;  --Only When Lot number is passed  TRUE else FALSE
   l_serial_control_code := FALSE;
   -- Call API
   inv_quantity_tree_pub.query_quantities
               (p_api_version_number       => 1.0
               ,p_init_msg_lst             => NULL
               ,x_return_status            => x_return_status
               ,x_msg_count                => x_msg_count
               ,x_msg_data                 => x_msg_data
               ,p_organization_id          => l_organization_id
               ,p_inventory_item_id        => l_item_id
               ,p_tree_mode                => apps.inv_quantity_tree_pub.g_transaction_mode
               ,p_is_revision_control      => FALSE
               ,p_is_lot_control           => l_lot_control_code-- is_lot_control,
               ,p_is_serial_control        => l_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          
               --,p_onhand_source            => NULL
               ,x_qoh                      => l_qty_on_hand     -- Quantity on-hand
               ,x_rqoh                     => l_res_qty_on_hand --reservable quantity on-hand
               ,x_qr                       => l_qty_reserved
               ,x_qs                       => l_qty_suggested
               ,x_att                      => l_avail_to_tnsct  -- available to transact
               ,x_atr                      => l_avail_to_reserve-- available to reserve
               );
   DBMS_OUTPUT.put_line ('On-Hand Quantity: ' || l_qty_on_hand);
   DBMS_OUTPUT.put_line ('Quantity Reserved: ' || l_qty_reserved);
   DBMS_OUTPUT.put_line ('Quantity Suggested: ' || l_qty_suggested);
   DBMS_OUTPUT.put_line ('Available to Transact: ' || l_avail_to_tnsct);
   DBMS_OUTPUT.put_line ('Available to Reserve: ' || l_avail_to_reserve);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);
END;