View: MTL_LOT_ONHAND_SUM_V

Product:	INV - Inventory
Description:	The view is used to get total onhand of a lot number.
Implementation/DBA Data:	APPS.MTL_LOT_ONHAND_SUM_V

View Text

SELECT MOQ.ORGANIZATION_ID ORGANIZATION_ID
, MP.ORGANIZATION_CODE ORGANIZATION_CODE
, MOQ.SUBINVENTORY_CODE SUBINVENTORY_CODE
, MOQ.LOCATOR_ID LOCATOR_ID
, MIL.CONCATENATED_SEGMENTS LOCATOR
, MOQ.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSIV.DESCRIPTION ITEM_DESCRIPTION
, MSIV.CONCATENATED_SEGMENTS ITEM
, MOQ.REVISION REVISION
, MSIV.PRIMARY_UOM_CODE UOM
, SUM(MOQ.PRIMARY_TRANSACTION_QUANTITY) ON_HAND
, MOQ.LPN_ID
, WLPN.LICENSE_PLATE_NUMBER LPN
, MOQ.LOT_NUMBER LOT_NUMBER
, MSI.STATUS_ID SUBINVENTORY_STATUS_ID
, MIL.STATUS_ID LOCATOR_STATUS_ID
, MLN.STATUS_ID LOT_STATUS_ID
, MOQ.SECONDARY_UOM_CODE SECONDARY_UOM_CODE
, SUM(MOQ.SECONDARY_TRANSACTION_QUANTITY) SECONDARY_ONHAND
, MLN.GRADE_CODE
FROM MTL_PARAMETERS MP
, MTL_ITEM_LOCATIONS_KFV MIL
, MTL_SECONDARY_INVENTORIES MSI
, MTL_LOT_NUMBERS MLN
, MTL_SYSTEM_ITEMS_VL MSIV
, MTL_ONHAND_QUANTITIES_DETAIL MOQ
, WMS_LICENSE_PLATE_NUMBERS WLPN
WHERE MOQ.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MOQ.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MOQ.LPN_ID = WLPN.LPN_ID(+)
AND MOQ.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND MOQ.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MOQ.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
AND MOQ.ORGANIZATION_ID = MLN.ORGANIZATION_ID(+)
AND MOQ.INVENTORY_ITEM_ID = MLN.INVENTORY_ITEM_ID(+)
AND MOQ.LOT_NUMBER = MLN.LOT_NUMBER(+)
AND MOQ.ORGANIZATION_ID = MSIV.ORGANIZATION_ID
AND MOQ.INVENTORY_ITEM_ID = MSIV.INVENTORY_ITEM_ID GROUP BY MOQ.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, MOQ.INVENTORY_ITEM_ID
, MOQ.REVISION
, MOQ.LPN_ID
, MSIV.DESCRIPTION
, MSIV.PRIMARY_UOM_CODE
, MOQ.SECONDARY_UOM_CODE
, MLN.GRADE_CODE
, WLPN.LICENSE_PLATE_NUMBER
, MIL.STATUS_ID
, MLN.STATUS_ID
, MSI.STATUS_ID
, MIL.CONCATENATED_SEGMENTS
, MSIV.CONCATENATED_SEGMENTS
, MOQ.SUBINVENTORY_CODE
, MOQ.LOCATOR_ID
, MOQ.LOT_NUMBER

Columns