Introduction
This blog explains to get the On hand quantity if a item in inventory for a specific date
#Code#
SELECT SUM (TRANSACTION_QUANTITY) TARGET_QTY
FROM MTL_ONHAND_QTY_COST_V MOQV
WHERE MOQV.ORGANIZATION_ID = I.ORGANIZATION_ID
AND MOQV.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
UNION
SELECT -SUM (PRIMARY_QUANTITY) TARGET_QTY
FROM MTL_MATERIAL_TRANSACTIONS MMT
,APPS.MTL_TRANSACTION_TYPES MTT
WHERE MMT.ORGANIZATION_ID = I.ORGANIZATION_ID
AND TRANSACTION_DATE > ((TRUNC (I.TDAYS)) – 1 / 86400)
AND MMT.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND ( LOGICAL_TRANSACTION = 2
OR LOGICAL_TRANSACTION IS NULL)
AND MMT.TRANSACTION_ACTION_ID NOT IN (24, 30)
AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
AND MTT.ZD_EDITION_NAME = ‘SET1’
AND TRANSACTION_TYPE_NAME NOT IN
(‘Miscellaneous Indirect Material Cost Receipt’, ‘Subinventory Transfer’, ‘Sales Order Pick’, ‘Internal Order Pick’, ‘Move Order Transfer’
,’Miscellaneous Recpt(SCNE)’, ‘Miscellaneous Recpt(SCEX)’, ‘Miscellaneous Cyl Cost Receipt’, ‘Miscellaneous Packing Cost Receipt’
,’COGS Recognition’, ‘WIP Byproduct Completion’, ‘Residual Qty Issue’, ‘Residual Qty Receipt’, ‘Miscellaneous Copper Scrap Receipt’
,’WIP Byproduct Return’)
Conclusion
This query will be useful fetching Onhand as on date for an item