Onhand with Lot and serial number SQL query in oracle apps

Onhand with Lot and serial number SQL query in oracle apps

In this post , We will be discuss about Onhand with Lot and serial number SQL query in oracle apps. This query helps to extract the Item Onhand Information's with Lot and Serial number in oracle apps.

 

 

 

2 Important table for Onhand with Lot and serial number SQL query in oracle apps

 

1. MTL_ONHAND_QUANTITIES

2.MTL_SERIAL_NUMBERS

 

 

SQL query to extract Onhand with Lot and serial number in oracle apps

select DISTINCT a2.segment1 item_code,a2.ORGANIZATION_ID,A1.LOT_NUMBER,SERIAL_NUMBER,SUBINVENTORY_CODE,1 Onhand_Qty
from apps.mtl_onhand_quantities A1,
apps.MTL_SYSTEM_ITEMS_B A2,
apps.mtl_serial_numbers a3
where a1.organization_id=:P_ORG_ID
and a2.segment1=:P_ITEM_CODE
AND A1.organization_iD=A2.organization_id
AND A1.INVENTORY_ITEM_ID=A2.INVENTORY_ITEM_ID
and A3.CURRENT_ORGANIZATION_ID=A2.ORGANIZATION_ID
AND A1.SUBINVENTORY_CODE=A3.CURRENT_SUBINVENTORY_CODE
AND A3.CURRENT_STATUS=3
AND A2.INVENTORY_ITEM_ID=A3.INVENTORY_ITEM_ID
AND A1.LOT_NUMBER=A3.LOT_NUMBER
GROUP BY a2.segment1,a2.ORGANIZATION_ID,A1.LOT_NUMBER,SERIAL_NUMBER,SUBINVENTORY_CODE