The SQL Query to Find the On Hand Quantity :-
SELECT
MOQ.ORGANIZATION_ID,
MSI.SEGMENT1 "ITEM NAME #",
MSI.DESCRIPTION "ITEM DESC #",
SUM (MOQ.TRANSACTION_QUANTITY)
FROM APPS.MTL_ONHAND_QUANTITIES MOQ,
INV.MTL_SYSTEM_ITEMS_B MSI
WHERE
MSI.INVENTORY_ITEM_ID =
MOQ.INVENTORY_ITEM_ID (+)
AND
MSI.ORGANIZATION_ID =
MOQ.ORGANIZATION_ID (+)
AND
MOQ.ORGANIZATION_ID = 122
AND
MSI.SEGMENT1 IN (
'AIRSET-P',
'AIRSETMTGKIT-P')
GROUP BY MOQ.ORGANIZATION_ID, MSI.SEGMENT1
, MSI.DESCRIPTION
UNION
SELECT
MMT.ORGANIZATION_ID,
MSI.SEGMENT1 "ITEM NAME #",
MSI.DESCRIPTION "ITEM DESC #",
SUM (MMT.TRANSACTION_QUANTITY)
FROM
APPS.MTL_MATERIAL_TRANSACTIONS MMT,
INV.MTL_SYSTEM_ITEMS_B MSI
WHERE
MSI.INVENTORY_ITEM_ID =
MMT.INVENTORY_ITEM_ID (+)
AND
MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
(+)
AND
MMT.ORGANIZATION_ID = 122
AND
MSI.SEGMENT1 IN (
'AIRSET-P',
'AIRSETMTGKIT-P'
)
GROUP BY MMT.ORGANIZATION_ID, MSI.SEGMENT1
, MSI.DESCRIPTION
