View: MTL_MATERIAL_STATUS_HIST_ERV

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

View Text

SELECT 'SUBINVENTORY' STATUS_CHANGE_ENTITY
, MSH.STATUS_UPDATE_ID STATUS_UPDATE_ID
, MSH.ORGANIZATION_ID ORGANIZATION_ID
, NULL INVENTORY_ITEM_ID
, NULL ITEM_NUMBER
, NULL LOT_NUMBER
, NULL SERIAL_NUMBER
, MP.ORGANIZATION_CODE ORGANIZATION_CODE
, H.NAME ORGANIZATION_NAME
, MSH.ZONE_CODE SUBINVENTORY
, NULL LOCATOR_ID
, NULL LOCATOR
, INV_STATUS_PKG.GET_FROM_STATUS_CODE (MSH.ORGANIZATION_ID
, NULL
, MSH.ZONE_CODE
, NULL
, NULL
, NULL) FROM_STATUS_CODE
, NULL FROM_STATUS_DESCRIPTION
, NULL FROM_STATUS_ID
, MMS.STATUS_CODE TO_STATUS_CODE
, MMS.DESCRIPTION TO_STATUS_DESCRIPTION
, MSH.STATUS_ID TO_STATUS_ID
, NULL PRIMARY_QUANTITY
, NULL PRIMARY_UOM_CODE
, NULL SECONDARY_QUANTITY
, NULL SECONDARY_UOM_CODE
, MR.REASON_NAME REASON_NAME
, MSH.CREATION_DATE CREATION_DATE
FROM MTL_MATERIAL_STATUS_HISTORY MSH
, HR_ALL_ORGANIZATION_UNITS H
, MTL_PARAMETERS MP
, MTL_TRANSACTION_REASONS MR
, MTL_MATERIAL_STATUSES MMS
WHERE MSH.ORGANIZATION_ID = H.ORGANIZATION_ID
AND H.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSH.UPDATE_REASON_ID = MR.REASON_ID(+)
AND MSH.STATUS_ID = MMS.STATUS_ID
AND MSH.LOCATOR_ID IS NULL
AND MSH.LOT_NUMBER IS NULL
AND MSH.SERIAL_NUMBER IS NULL UNION SELECT 'LOCATOR' STATUS_CHANGE_ENTITY
, MSH.STATUS_UPDATE_ID STATUS_UPDATE_ID
, MSH.ORGANIZATION_ID ORGANIZATION_ID
, NULL INVENTORY_ITEM_ID
, NULL ITEM_NUMBER
, NULL LOT_NUMBER
, NULL SERIAL_NUMBER
, MP.ORGANIZATION_CODE ORGANIZATION_CODE
, H.NAME ORGANIZATION_NAME
, MSH.ZONE_CODE SUBINVENTORY
, MSH.LOCATOR_ID LOCATOR_ID
, MIL.CONCATENATED_SEGMENTS LOCATOR
, INV_STATUS_PKG.GET_FROM_STATUS_CODE (MSH.ORGANIZATION_ID
, NULL
, NULL
, MSH.LOCATOR_ID
, NULL
, NULL) FROM_STATUS_CODE
, NULL FROM_STATUS_DESCRIPTION
, NULL FROM_STATUS_ID
, MMS.STATUS_CODE TO_STATUS_CODE
, MMS.DESCRIPTION TO_STATUS_DESCRIPTION
, MSH.STATUS_ID TO_STATUS_ID
, NULL PRIMARY_QUANTITY
, NULL PRIMARY_UOM_CODE
, NULL SECONDARY_QUANTITY
, NULL SECONDARY_UOM_CODE
, MR.REASON_NAME REASON_NAME
, MSH.CREATION_DATE CREATION_DATE
FROM MTL_MATERIAL_STATUS_HISTORY MSH
, HR_ALL_ORGANIZATION_UNITS H
, MTL_PARAMETERS MP
, MTL_TRANSACTION_REASONS MR
, MTL_ITEM_LOCATIONS_KFV MIL
, MTL_MATERIAL_STATUSES MMS
WHERE MSH.ORGANIZATION_ID = H.ORGANIZATION_ID
AND H.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSH.UPDATE_REASON_ID = MR.REASON_ID(+)
AND MSH.STATUS_ID = MMS.STATUS_ID
AND MSH.ORGANIZATION_ID = MIL.ORGANIZATION_ID
AND MSH.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID
AND MSH.LOT_NUMBER IS NULL
AND MSH.SERIAL_NUMBER IS NULL UNION SELECT 'LOT' STATUS_CHANGE_ENTITY
, MSH.STATUS_UPDATE_ID STATUS_UPDATE_ID
, MSH.ORGANIZATION_ID ORGANIZATION_ID
, MSH.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSH.LOT_NUMBER LOT_NUMBER
, NULL SERIAL_NUMBER
, MP.ORGANIZATION_CODE ORGANIZATION_CODE
, H.NAME ORGANIZATION_NAME
, NULL SUBINVENTORY
, NULL LOCATOR_ID
, NULL LOCATOR
, INV_STATUS_PKG.GET_FROM_STATUS_CODE (MSH.ORGANIZATION_ID
, MSH.INVENTORY_ITEM_ID
, NULL
, NULL
, MSH.LOT_NUMBER
, NULL) FROM_STATUS_CODE
, NULL FROM_STATUS_DESCRIPTION
, NULL FROM_STATUS_ID
, MMS.STATUS_CODE TO_STATUS_CODE
, MMS.DESCRIPTION TO_STATUS_DESCRIPTION
, MSH.STATUS_ID TO_STATUS_ID
, MSH.PRIMARY_ONHAND PRIMARY_QUANTITY
, MSI.PRIMARY_UNIT_OF_MEASURE PRIMARY_UOM_CODE
, MSH.SECONDARY_ONHAND SECONDARY_QUANTITY
, MSI.SECONDARY_UOM_CODE SECONDARY_UOM_CODE
, MR.REASON_NAME REASON_NAME
, MSH.CREATION_DATE CREATION_DATE
FROM MTL_MATERIAL_STATUS_HISTORY MSH
, HR_ALL_ORGANIZATION_UNITS H
, MTL_PARAMETERS MP
, MTL_TRANSACTION_REASONS MR
, MTL_MATERIAL_STATUSES MMS
, MTL_SYSTEM_ITEMS_KFV MSI
WHERE MSH.ORGANIZATION_ID = H.ORGANIZATION_ID
AND H.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSH.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSH.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSH.UPDATE_REASON_ID = MR.REASON_ID(+)
AND MSH.STATUS_ID = MMS.STATUS_ID
AND MSH.SERIAL_NUMBER IS NULL UNION SELECT 'SERIAL' STATUS_CHANGE_ENTITY
, MSH.STATUS_UPDATE_ID STATUS_UPDATE_ID
, MSH.ORGANIZATION_ID ORGANIZATION_ID
, MSH.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSN.LOT_NUMBER LOT_NUMBER
, MSH.SERIAL_NUMBER SERIAL_NUMBER
, MP.ORGANIZATION_CODE ORGANIZATION_CODE
, H.NAME ORGANIZATION_NAME
, NULL SUBINVENTORY
, NULL LOCATOR_ID
, NULL LOCATOR
, INV_STATUS_PKG.GET_FROM_STATUS_CODE (MSH.ORGANIZATION_ID
, NULL
, NULL
, NULL
, MSN.LOT_NUMBER
, MSH.SERIAL_NUMBER) FROM_STATUS_CODE
, NULL FROM_STATUS_DESCRIPTION
, NULL FROM_STATUS_ID
, MMS.STATUS_CODE TO_STATUS_CODE
, MMS.DESCRIPTION TO_STATUS_DESCRIPTION
, MSH.STATUS_ID TO_STATUS_ID
, MSH.PRIMARY_ONHAND PRIMARY_QUANTITY
, MSI.PRIMARY_UNIT_OF_MEASURE PRIMARY_UOM_CODE
, MSH.SECONDARY_ONHAND SECONDARY_QUANTITY
, MSI.SECONDARY_UOM_CODE SECONDARY_UOM_CODE
, MR.REASON_NAME REASON_NAME
, MSH.CREATION_DATE CREATION_DATE
FROM MTL_MATERIAL_STATUS_HISTORY MSH
, HR_ALL_ORGANIZATION_UNITS H
, MTL_PARAMETERS MP
, MTL_TRANSACTION_REASONS MR
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_MATERIAL_STATUSES MMS
, MTL_SERIAL_NUMBERS MSN
WHERE MSH.ORGANIZATION_ID = H.ORGANIZATION_ID
AND H.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSH.UPDATE_REASON_ID = MR.REASON_ID(+)
AND MSH.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSH.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSH.STATUS_ID = MMS.STATUS_ID
AND MSH.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND MSH.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND MSN.STATUS_ID = MMS.STATUS_ID

Columns