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.
- Set the value TRUE for lot /serial controlled only if your item is controlled by those attributes else set to FALSE.
- Advice to call inv_quantity_tree_grp.clear_quantity_cache to delete all the tree quantities in the memory.
- 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.
