Avaialble to Reserve and Availble to Transact quantities using inv_quantity_tree_pub.query_quantities API

There is much to mention about this API which makes life easy to get available to Reserve/Transact quantities, since we cannot perform a quantity transaction based on On hand quantity.

Use inv_quantity_tree_pub.query_quantities API to get the exact quantities.

Things to consider.

1. Set the value TRUE for lot /serial controlled only if your item is controlled by those attributes else set to FALSE.
2. Advice to call inv_quantity_tree_grp.clear_quantity_cache to delete all the tree quantities in the memory.
3. Most importantly if you are here because you are not getting right quantity by directly calling this function please follow the note after API.

FUNCTION available_to_reserve (
   p_inv_item_id       NUMBER
  ,p_subinv_code       VARCHAR2
  ,p_locator_id        NUMBER
  ,p_lot_number        VARCHAR2
  ,p_organization_id   NUMBER
)
   RETURN NUMBER
IS
   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
   /* Used to clear cache*/
   inv_quantity_tree_grp.clear_quantity_cache;
  
   -- Initialize apps by passing correct values
   fnd_global.apps_initialize (user_id,resp_id,resp_appl_id);
  
   -- Set the variable values
   l_item_id := p_inv_item_id;
   l_organization_id := p_organization_id;
   l_lot_control_code := TRUE;   --Only When Lot number is passed  TRUE else FALSE
   l_serial_control_code := FALSE;   --if item is serial controlled pass  TRUE else FALSE
   -- 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          => l_organization_id
         ,p_inventory_item_id        => l_item_id
         ,p_tree_mode                => apps.inv_quantity_tree_pub.g_transaction_mode   -- or 3
         ,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               => p_lot_number   -- p_lot_number,
         ,p_lot_expiration_date      => SYSDATE
         ,p_subinventory_code        => p_subinv_code   -- p_subinventory,
         ,p_locator_id               => p_locator_id
         ,p_cost_group_id            => NULL
         ,p_onhand_source            => 3   -- Can also pass 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 ('Avaialble to Reserve : ' || l_avail_to_reserve);
   RETURN NVL (l_avail_to_reserve, 0);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);
      RETURN 0;
END;

Note: For some reason i am not able to achieve right results even after passing the correct values and initializing correctly through SQL/Concurrent Prgoram. After hours of debug i found that this API cannot be run directly from SQL/Toad just by declare begin and end. It has to be created as function/procedure. Also function created cannot be called directly . Its advisable to create a main function which in turn calls the quantity API. This was i was able to get right quantity. 

Here is example :

function quantity_call (arguments)
RETURN NUMBER IS
l_avail_qty NUMBER;
BEGIN
---This condition is to clear the quantity with incorrect inputs by hardcoding
         ln_avaialble_qty :=
              avail_to_reserve (1234567890, 'AAAA', 12345000, 'AAA.3.0', organization_id);
---Then pass correct values         
ln_avaialble_qty := avail_to_reserve (arguments);

 ---This condition is to clear the quantity with  incorrect inputs and try again
     IF ln_avaialble_qty = 0
    THEN
      ---This condition is to clear the quantity with incorrect inputs by hardcoding
         ln_avaialble_qty :=
              avail_to_reserve (1234567890, 'AAAA', 12345000, 'AAA.3.0', organization_id);
     ---Then pass correct values  
            ln_avaialble_qty := avail_to_reserve (arguments);
         END IF;

l_avail_qty := availalbe_to_reserve (arguments);
RETURN  l_avail_qty;
END;

Use quantity_call (arguments) where ever needed.