View: MTL_ONHAND_NET

Product:	INV - Inventory
Description:	The view shows the net onhand quantity and checks the validity of lot number and locator.
Implementation/DBA Data:	APPS.MTL_ONHAND_NET

View Text

SELECT MOQ.INVENTORY_ITEM_ID
, MOQ.ORGANIZATION_ID
, MOQ.DATE_RECEIVED
, MOQ.PRIMARY_TRANSACTION_QUANTITY
, MOQ.SECONDARY_TRANSACTION_QUANTITY
, MOQ.SUBINVENTORY_CODE
, MOQ.REVISION
, MOQ.LOCATOR_ID
, MOQ.LOT_NUMBER
, MOQ.LPN_ID
, MOQ.PLANNING_TP_TYPE
, MOQ.PLANNING_ORGANIZATION_ID
FROM MTL_ONHAND_QUANTITIES_DETAIL MOQ
, MTL_SECONDARY_INVENTORIES MSI
, MTL_PARAMETERS MP
WHERE ((MP.DEFAULT_STATUS_ID IS NULL
AND MOQ.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND ((MOQ.LOT_NUMBER IS NOT NULL
AND EXISTS (SELECT 1
FROM MTL_LOT_NUMBERS MLN
WHERE MLN.LOT_NUMBER = MOQ.LOT_NUMBER
AND MLN.ORGANIZATION_ID = MOQ.ORGANIZATION_ID
AND NVL(MLN.AVAILABILITY_TYPE
, 1) = 1)) OR MOQ.LOT_NUMBER IS NULL) AND((MOQ.LOCATOR_ID IS NOT NULL
AND EXISTS (SELECT 1
FROM MTL_ITEM_LOCATIONS MIL
WHERE MIL.INVENTORY_LOCATION_ID = MOQ.LOCATOR_ID
AND MIL.ORGANIZATION_ID = MOQ.ORGANIZATION_ID
AND NVL(MIL.AVAILABILITY_TYPE
, 1) = 1)) OR MOQ.LOCATOR_ID IS NULL) AND(MSI.SECONDARY_INVENTORY_NAME = MOQ.SUBINVENTORY_CODE
AND MSI.ORGANIZATION_ID = MOQ.ORGANIZATION_ID
AND MSI.AVAILABILITY_TYPE = 1) ) OR (MP.DEFAULT_STATUS_ID IS NOT NULL
AND MOQ.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MOQ.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MOQ.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
AND ((MOQ.STATUS_ID IS NOT NULL
AND EXISTS (SELECT 1
FROM MTL_MATERIAL_STATUSES MMS
WHERE STATUS_ID = MOQ.STATUS_ID
AND MMS.AVAILABILITY_TYPE = 1 )) OR MOQ.STATUS_ID IS NULL) ))

Columns