Introduction/ Issue: This query will help us to get item wise lot quantity.
Why we need to do / Cause of the issue: When we need to validate the lot quantity item wise, we can make use of this query.
How do we solve: The below sql query is used to derive the Item wise lot quantity.
select msi.segment1 item_num,
sum(mtln.primary_quantity) lot_quantity,
ood.organization_code,
(SELECT EXPIRATION_DATE FROM MTL_LOT_NUMBERS WHERE LOT_NUMBER=moqd.LOT_NUMBER
AND inventory_item_id =MOQD.inventory_item_id AND organization_id =MOQD.organization_id )LOT_EXP_DATE
from mtl_system_items_b msi,
mtl_transaction_lot_numbers mtln,
mtl_onhand_quantities_detail moqd,
org_organization_definitions ood
where msi.inventory_item_id = mtln.inventory_item_id
and msi.organization_id = mtln.organization_id
and msi.organization_id = ood.organization_id
and msi.inventory_item_id = moqd.inventory_item_id
and msi.organization_id = moqd.organization_id
group by msi.segment1,ood.organization_code,moqd.LOT_NUMBER,MOQD.inventory_item_id,MOQD.organization_id
Conclusion: This code will help to get item wise lot quantity.
