Saturday 18 March 2017

How to find Onhand Quantity of a Lot Controlled Item in a locator Controlled Subinventory ??

Had a requirement to get total Available to Transact (ATT) quantity of a lot controlled item in a locator controlled subinventory. Searched extensively in MOS and google to find API. We knew INV_Quantity_Tree_PUB.Query_Quantities API does that but could not find out appropriate parameter values. As the item was lot controlled, we were passing p_is_lot_control parameter as TRUE. API was returning 0 as ATT because we were not passing lot number.

We needed to find ATT of all lots in a locator inside a subinventory. After playing with the parameter values I realized that to get total ATT of the item we actually needed to pass FALSE to p_is_lot_control parameter. And to our surprise it worked like a magic. Here is a sample script which can be used.

DECLARE
        L_api_return_status  VARCHAR2(1);
        l_qty_oh             NUMBER;
        l_qty_res_oh         NUMBER;
        l_qty_res            NUMBER;
        l_qty_sug            NUMBER;
        l_qty_att            NUMBER;
        l_qty_atr            NUMBER;
        l_msg_count          NUMBER;
        l_msg_data           VARCHAR2(1000);
        l_item_id            NUMBER :=3917224;
        l_org_id             NUMBER :=284;
        BEGIN
       
        inv_quantity_tree_grp.clear_quantity_cache;
        dbms_output.put_line('Transaction Mode');
          apps.INV_Quantity_Tree_PUB.Query_Quantities (
            p_api_version_number => 1.0
          , p_init_msg_lst       => apps.fnd_api.g_false
          , x_return_status      => L_api_return_status
          , x_msg_count          => l_msg_count
          , x_msg_data           => l_msg_data
          , p_organization_id    => l_org_id
          , p_inventory_item_id  => l_item_id
          , p_tree_mode          => apps.INV_Quantity_Tree_PUB.g_transaction_mode
          , p_onhand_source      => 3
          , p_is_revision_control=> false
          , p_is_lot_control     => FALSE
          , p_is_serial_control  => FALSE
          , p_revision           => NULL
          , p_lot_number         => NULL
          , p_subinventory_code  => 'SSTOCK'
          , p_locator_id         => 11285
          , x_qoh                => l_qty_oh
          , x_rqoh               => l_qty_res_oh
          , x_qr                 => l_qty_res
          , x_qs                 => l_qty_sug
          , x_att                => l_qty_att
          , x_atr                => l_qty_atr );

dbms_output.put_line('Quantity ATT'||to_char(l_qty_att));
dbms_output.put_line('Quantity ATR'||to_char(l_qty_atr));
end;
/

It may be known to others but I did not know this. So thought of sharing this for people like me.
Happy reading.