View: MTL_ONHAND_SYNC_V

Product:	INV - Inventory
Description:
Implementation/DBA Data:	APPS.MTL_ONHAND_SYNC_V

View Text

SELECT MCK.CONCATENATED_SEGMENTS CATEGORY
, ONH.ORGANIZATION_ID WAREHOUSE_ID
, MP.ORGANIZATION_CODE WAREHOUSE
, ONH.INVENTORY_ITEM_ID ITEM_ID
, MSIBK.CONCATENATED_SEGMENTS ITEM
, MSIBK.DESCRIPTION ITEM_DESCRIPTION
, ONH.REVISION REVISION
, ONH.SUBINVENTORY_CODE SUBINVENTORY
, MILK.CONCATENATED_SEGMENTS LOCATOR
, ONH.LOT_NUMBER LOT
, WLPN1.LICENSE_PLATE_NUMBER LPN
, WLPN2.LICENSE_PLATE_NUMBER PARENT_LPN
, ONH.SERIAL_NUMBER SERIAL
, DECODE(ONH.LPN_ID
, NULL
, 'NO'
, 'YES') CONTAINERIZED_FLAG
, ONH.PRIMARY_QUANTITY PRIMARY_QUANTITY
, MUOM1.UNIT_OF_MEASURE_TL PRIMARY_UOM
, DECODE(MSIBK.TRACKING_QUANTITY_IND
, 'PS'
, ONH.SECONDARY_QUANTITY
, NULL) SECONDARY_QUANTITY
, DECODE(MSIBK.TRACKING_QUANTITY_IND
, 'PS'
, MUOM2.UNIT_OF_MEASURE_TL
, NULL) SECONDARY_UOM
, SYSDATE SNAPSHOT_DATE
, MMS.STATUS_CODE ONHAND_STATUS
FROM (SELECT MIC.CATEGORY_ID CATEGORY_ID
, MOQ.ORGANIZATION_ID ORGANIZATION_ID
, MOQ.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MOQ.REVISION REVISION
, MOQ.SUBINVENTORY_CODE SUBINVENTORY_CODE
, MOQ.LOCATOR_ID LOCATOR_ID
, MOQ.LOT_NUMBER LOT_NUMBER
, MOQ.LPN_ID LPN_ID
, NULL SERIAL_NUMBER
, DECODE(MOQ.STATUS_ID
, NULL
, INV_MATERIAL_STATUS_GRP.GET_DEFAULT_STATUS_CONC(MOQ.ORGANIZATION_ID
, MOQ.INVENTORY_ITEM_ID
, MOQ.SUBINVENTORY_CODE
, MOQ.LOCATOR_ID
, MOQ.LOT_NUMBER
, MOQ.LPN_ID)
, MOQ.STATUS_ID) STATUS_ID
, SUM(MOQ.PRIMARY_TRANSACTION_QUANTITY) PRIMARY_QUANTITY
, SUM(MOQ.SECONDARY_TRANSACTION_QUANTITY) SECONDARY_QUANTITY
FROM MTL_ONHAND_QUANTITIES_DETAIL MOQ
, MTL_SYSTEM_ITEMS_B MSI
, MTL_ITEM_CATEGORIES MIC
WHERE MSI.INVENTORY_ITEM_ID = MOQ.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MOQ.ORGANIZATION_ID
AND MSI.SERIAL_NUMBER_CONTROL_CODE IN (1
, 6)
AND MIC.INVENTORY_ITEM_ID = MOQ.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID = MOQ.ORGANIZATION_ID GROUP BY MIC.CATEGORY_ID
, MOQ.ORGANIZATION_ID
, MOQ.INVENTORY_ITEM_ID
, MOQ.REVISION
, MOQ.SUBINVENTORY_CODE
, MOQ.LOCATOR_ID
, MOQ.LOT_NUMBER
, MOQ.LPN_ID
, MOQ.STATUS_ID UNION ALL SELECT MIC.CATEGORY_ID CATEGORY_ID
, MSN.CURRENT_ORGANIZATION_ID ORGANIZATION_ID
, MSN.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSN.REVISION REVISION
, MSN.CURRENT_SUBINVENTORY_CODE SUBINVENTORY_CODE
, MSN.CURRENT_LOCATOR_ID LOCATOR_ID
, MSN.LOT_NUMBER LOT_NUMBER
, MSN.LPN_ID LPN_ID
, MSN.SERIAL_NUMBER SERIAL_NUMBER
, MSN.STATUS_ID STATUS_ID
, 1 PRIMARY_QUANTITY
, NULL SECONDARY_QUANTITY
FROM MTL_SERIAL_NUMBERS MSN
, MTL_SYSTEM_ITEMS_B MSI
, MTL_ITEM_CATEGORIES MIC
WHERE MSN.CURRENT_STATUS = 3
AND MSI.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MSN.CURRENT_ORGANIZATION_ID
AND MSI.SERIAL_NUMBER_CONTROL_CODE NOT IN (1
, 6)
AND MIC.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID = MSN.CURRENT_ORGANIZATION_ID ) ONH
, MTL_CATEGORIES_KFV MCK
, MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, MTL_ITEM_LOCATIONS_KFV MILK
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
, MTL_UNITS_OF_MEASURE MUOM1
, MTL_UNITS_OF_MEASURE MUOM2
, MTL_MATERIAL_STATUSES MMS
WHERE MCK.CATEGORY_ID = ONH.CATEGORY_ID
AND MP.ORGANIZATION_ID = ONH.ORGANIZATION_ID
AND MSIBK.ORGANIZATION_ID = ONH.ORGANIZATION_ID
AND MSIBK.INVENTORY_ITEM_ID = ONH.INVENTORY_ITEM_ID
AND MILK.ORGANIZATION_ID (+) = ONH.ORGANIZATION_ID
AND MILK.INVENTORY_LOCATION_ID (+) = ONH.LOCATOR_ID
AND MMS.STATUS_ID (+) = ONH.STATUS_ID
AND WLPN1.LPN_ID (+) = ONH.LPN_ID
AND WLPN2.LPN_ID (+) = WLPN1.PARENT_LPN_ID
AND MUOM1.UOM_CODE = MSIBK.PRIMARY_UOM_CODE
AND MUOM2.UOM_CODE (+) = MSIBK.SECONDARY_UOM_CODE

Columns