Reserved Quantity & Available Quantity Query in Fusion

Reserved quantity is nothing but the quantity of an item already reserved( i.e. it can not be allocated to any purpose anymore). Reserved qty is an important factor for business to maintain the quantity at a level deemed fit for business continuation. Once you have deducted reserved qty from Onhand Quantity, you’ll get the Available Quantity or Available Quantity to Reserve. in short:

Available Quantity = Onhand Quantity – Reserved Quantity

For example, you have 100 quantity in your inventory org, out of which 20 are already reserved for one purpose or another, then your available quantity is 100-20 = 80. These 80 quantity is something which you can use for any other business requirement.

By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...

in this post, let me share the query to fetch the reserved qty:

select sum(NVL(Res.PRIMARY_RESERVATION_QUANTITY,0)) Reserved_qty
from inv_reservations res ,
inv_txn_source_types_tl src,
egp_system_items_b esi,
inv_org_parameters iop
WHERE res.inventory_item_id = esi.inventory_item_id
AND res.organization_id= iop.organization_id
AND src.TRANSACTION_SOURCE_TYPE_ID = res.supply_source_type_id
AND src.TRANSACTION_SOURCE_TYPE_NAME = ‘Inventory’
AND src.language = USERENV(‘LANG’)
AND esi.item_number = :item_number
AND upper(iop.organization_code) = :Org

As you can see, the query is taking item number and inventory organization as input parameter. It means that these quantity calculation is for each inventory org and the item. Or in other words, reserved qty will vary from one inventory org to another. You can alter the above given query to meet your requirement.

Related Posts

Onhand Quantity calculation in Oracle Fusion Finance
How to Query the details of an Item
Table Structure of item table in Oracle Fusion Finance
Fusion LoV Queries: Legal Entity, Business Unit, Inventory, Sub Inventory

Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.

Reference:
support.oracle.com

Check out the Amazon Deals of the day based on your interests..