link between mtl_onhand_quantities and mtl_serial_numbers
In this post, We will discuss about sql link between mtl_onhand_quantities and mtl_serial_numbers tables. Some times we need to find out the Available Onhand Items with Detail Serial Number information's availability. Here below I will share the SQL query using SQL links between the mtl_onhand_quantities and mtl_serial_numbers tables which will help to extract the Inventory Onhand in Oracle Apps.
Example of SQL Query using link between mtl_onhand_quantities and mtl_serial_numbers
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
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
1 comments:
Hello your ads are covering your content and its discouraging people from coming back to your site
Post a Comment