VIEW: APPS.MTL_MATERIAL_STATUS_HIST_ERV

Source

View Text - Preformatted

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

View Text - HTML Formatted

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