Query to fetch on hand quantity of an item for historic date

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