We have a common requirement from most of the businesses to get inventory on-hand balances and available to reserve quantities by item and sub-inventory for their operation(s). This will help them to understand the current inventory to fulfill the sales order(s)/work order(s) demand, commiting the delivery dates with the customers and planning to source the buy parts/manufacturing the make parts.
Here is the query Query to get on-hand and available to reserve quantity by Item and Sub-Inventory in a specific inventory org.
SELECT esi.item_number, org.organization_code, oh.subinventory_code, DECODE(sub.reservable_type,1, ‘Reservable’,’Non-Reservable’) Reservable,
SUM(NVL(oh.transaction_quantity,0)) onhand_qty,
SUM(NVL(ir.primary_reservation_quantity,0)) reserved_qty,
SUM(NVL(oh.transaction_quantity,0))- SUM(NVL(ir.primary_reservation_quantity,0)) Available_To_Reserve
FROM egp_system_items esi,
inv_org_parameters org,
inv_onhand_quantities_detail oh,
inv_secondary_inventories sub,
inv_reservations ir,
inv_txn_source_types_vl source
WHERE esi.organization_id = org.organization_id
AND esi.inventory_item_id = oh.inventory_item_id
AND esi.organization_id = oh.organization_id
AND oh.subinventory_code = sub.secondary_inventory_name
AND oh.organization_id = sub.organization_id
— AND sub.reservable_type = ‘1’ — Reservable quantities
AND oh.inventory_item_id = ir.inventory_item_id (+)
AND oh.organization_id = ir.organization_id (+)
AND oh.subinventory_code = ir.subinventory_code (+)
AND ir.supply_source_type_id = source.transaction_source_type_id (+)
AND source.transaction_source_type_name (+) = ‘Inventory’
— AND esi.item_number =’ABC123′
AND org.organization_code = :P_ORG_CODE
GROUP BY esi.item_number, org.organization_code, oh.subinventory_code, DECODE(sub.reservable_type,1, ‘Reservable’,’Non-Reservable’)
ORDER BY 2,1,3
Sample Report output:
Business(es) has a common requirement