Query to get the quantities on reservation


SELECT SUM(transaction_quantity) A 
FROM 
MTL_ONHAND_QUANTITIES_DETAIL 
WHERE 
inventory_item_id = :INVENTORY_ITEM_ID 
AND organization_id =:WH_ID; 

QTY Reserved :--- 

SELECT SUM(RESERVATION_QUANTITY) B 
FROM MTL_RESERVATIONS 
WHERE inventory_item_id = :INVENTORY_ITEM_ID 
AND organization_id = :WH_ID; 


SELECT SUM(transaction_quantity) C 
FROM 
MTL_ONHAND_QUANTITIES_DETAIL 
WHERE inventory_item_id = :INVENTORY_ITEM_ID 
AND organization_id = :WH_ID 
AND subinventory_code IN (SELECT SECONDARY_INVENTORY_NAME 
FROM 
MTL_SECONDARY_INVENTORIES 
WHERE organization_id =:WH_ID 
AND reservable_type = '2'); 


Qty Available :--- 


QTY_AV_BR:=nvl(A,0)- (nvl(B,0)+ nvl(C,0));