View: WMS_EXCEPTIONS_V

Product:	WMS - Warehouse Management
Description:	View to capture task exceptions for control board.
Implementation/DBA Data:	APPS.WMS_EXCEPTIONS_V

View Text

SELECT MMTT.ROWID
, MMTT.TRANSACTION_TEMP_ID
, MMTT.INVENTORY_ITEM_ID
, MSI.DESCRIPTION
, MSI.CONCATENATED_SEGMENTS
, MMTT.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, HAOU.NAME
, MMTT.REVISION
, MTLT.LOT_NUMBER
, MMTT.SUBINVENTORY_CODE
, MMTT.LOCATOR_ID
, MIL.DESCRIPTION
, MIL.CONCATENATED_SEGMENTS
, MMTT.TRANSACTION_TYPE_ID
, MTT.DESCRIPTION
, MMTT.TRANSACTION_ACTION_ID
, ML1.MEANING
, MMTT.TRANSACTION_SOURCE_TYPE_ID
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, MMTT.TRANSACTION_SOURCE_ID
, MMTT.TRX_SOURCE_LINE_ID
, MMTT.TRANSACTION_SOURCE_NAME
, MMTT.TRANSFER_ORGANIZATION
, MP1.ORGANIZATION_CODE
, HAOU1.NAME
, MMTT.TRANSFER_SUBINVENTORY
, MMTT.TRANSFER_TO_LOCATION
, MIL1.DESCRIPTION
, MIL1.CONCATENATED_SEGMENTS
, MMTT.TRANSACTION_UOM
, DECODE(MTLT.TRANSACTION_QUANTITY
, NULL
, MMTT.TRANSACTION_QUANTITY
, MTLT.TRANSACTION_QUANTITY)
, WDT.TASK_ID
, WDT.USER_TASK_TYPE
, BSO.OPERATION_CODE
, WDT.PERSON_ID
, PAPF.LAST_NAME
, PAPF.FIRST_NAME
, PAPF.FULL_NAME
, WDT.EFFECTIVE_START_DATE
, WDT.EFFECTIVE_END_DATE
, WDT.EQUIPMENT_ID
, MSI1.CONCATENATED_SEGMENTS
, MSI1.DESCRIPTION
, WDT.EQUIPMENT_INSTANCE
, WDT.PERSON_RESOURCE_ID
, BR1.RESOURCE_CODE
, WDT.MACHINE_RESOURCE_ID
, BR2.RESOURCE_CODE
, WDT.STATUS
, ML2.MEANING
, WDT.DISPATCHED_TIME
, WDT.LOADED_TIME
, WDT.DROP_OFF_TIME
, MMTT.LAST_UPDATE_DATE
, MMTT.LAST_UPDATED_BY
, MMTT.CREATION_DATE
, MMTT.CREATED_BY
, MMTT.LAST_UPDATE_LOGIN
, WDT.ATTRIBUTE_CATEGORY
, WDT.ATTRIBUTE1
, WDT.ATTRIBUTE2
, WDT.ATTRIBUTE3
, WDT.ATTRIBUTE4
, WDT.ATTRIBUTE5
, WDT.ATTRIBUTE6
, WDT.ATTRIBUTE7
, WDT.ATTRIBUTE8
, WDT.ATTRIBUTE9
, WDT.ATTRIBUTE10
, WDT.ATTRIBUTE11
, WDT.ATTRIBUTE12
, WDT.ATTRIBUTE13
, WDT.ATTRIBUTE14
, WDT.ATTRIBUTE15
, WE.REASON_ID
, WDT.PRIORITY
, BSO.OPERATION_DESCRIPTION
, WDT.TASK_TYPE
, ML3.MEANING
, MTR.REASON_NAME
FROM MTL_TRANSACTION_LOTS_TEMP MTLT
, MTL_PARAMETERS MP
, HR_ALL_ORGANIZATION_UNITS HAOU
, MTL_PARAMETERS MP1
, HR_ALL_ORGANIZATION_UNITS HAOU1
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_SYSTEM_ITEMS_KFV MSI1
, MTL_ITEM_LOCATIONS_KFV MIL
, MTL_ITEM_LOCATIONS_KFV MIL1
, MTL_TRANSACTION_TYPES MTT
, MTL_TXN_SOURCE_TYPES MTST
, MFG_LOOKUPS ML1
, BOM_STANDARD_OPERATIONS BSO
, PER_ALL_PEOPLE_F PAPF
, BOM_RESOURCES BR1
, BOM_RESOURCES BR2
, WMS_DISPATCHED_TASKS WDT
, WMS_EXCEPTIONS WE
, MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, MFG_LOOKUPS ML2
, MFG_LOOKUPS ML3
, MTL_TRANSACTION_REASONS MTR
WHERE MP.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND HAOU.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MP1.ORGANIZATION_ID (+)= MMTT.TRANSFER_ORGANIZATION
AND HAOU1.ORGANIZATION_ID (+)= MMTT.TRANSFER_ORGANIZATION
AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MIL.ORGANIZATION_ID (+)= MMTT.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID (+)= MMTT.LOCATOR_ID
AND MIL1.ORGANIZATION_ID (+)= MMTT.TRANSFER_ORGANIZATION
AND MIL1.INVENTORY_LOCATION_ID (+)= MMTT.TRANSFER_TO_LOCATION
AND MTLT.TRANSACTION_TEMP_ID (+)= MMTT.TRANSACTION_TEMP_ID
AND MTT.TRANSACTION_TYPE_ID = MMTT.TRANSACTION_TYPE_ID
AND ML1.LOOKUP_TYPE = 'MTL_TRANSACTION_ACTION'
AND ML1.LOOKUP_CODE = MMTT.TRANSACTION_ACTION_ID
AND MTST.TRANSACTION_SOURCE_TYPE_ID = MMTT.TRANSACTION_SOURCE_TYPE_ID
AND WDT.TRANSACTION_TEMP_ID (+) = MMTT.TRANSACTION_TEMP_ID
AND WE.TRANSACTION_HEADER_ID = MMTT.TRANSACTION_HEADER_ID
AND WE.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND NVL(WE.REVISION
, '###') = NVL(MMTT.REVISION
, '###')
AND NVL(WE.SUBINVENTORY_CODE
, '###') = NVL(MMTT.SUBINVENTORY_CODE
, '###')
AND NVL(WE.INVENTORY_LOCATION_ID
, -999) = NVL(MMTT.LOCATOR_ID
, -999)
AND BSO.STANDARD_OPERATION_ID (+) = WDT.USER_TASK_TYPE
AND MSI1.ORGANIZATION_ID (+)= WDT.ORGANIZATION_ID
AND MSI1.INVENTORY_ITEM_ID (+)= WDT.EQUIPMENT_ID
AND PAPF.PERSON_ID (+)= WDT.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN NVL(PAPF.EFFECTIVE_START_DATE
, TRUNC(SYSDATE))
AND NVL(PAPF.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
AND BR1.RESOURCE_ID (+) = WDT.PERSON_RESOURCE_ID
AND BR1.RESOURCE_TYPE (+)=2
AND BR2.RESOURCE_ID (+)= WDT.MACHINE_RESOURCE_ID
AND BR2.RESOURCE_TYPE (+)=1
AND ML2.LOOKUP_TYPE (+)= 'WMS_TASK_STATUS'
AND ML2.LOOKUP_CODE (+)= NVL(WDT.STATUS
, 1)
AND ML3.LOOKUP_TYPE (+) = 'WMS_TASK_TYPES'
AND ML3.LOOKUP_CODE (+) = WDT.TASK_TYPE
AND MTR.REASON_ID (+) = WE.REASON_ID UNION SELECT MMT.ROWID
, MMT.TRANSACTION_ID
, MMT.INVENTORY_ITEM_ID
, MSI.DESCRIPTION
, MSI.CONCATENATED_SEGMENTS
, MMT.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, HAOU.NAME
, MMT.REVISION
, MTLT.LOT_NUMBER
, MMT.SUBINVENTORY_CODE
, MMT.LOCATOR_ID
, MIL.DESCRIPTION
, MIL.CONCATENATED_SEGMENTS
, MMT.TRANSACTION_TYPE_ID
, MTT.DESCRIPTION
, MMT.TRANSACTION_ACTION_ID
, ML1.MEANING
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, MMT.TRANSACTION_SOURCE_ID
, MMT.TRX_SOURCE_LINE_ID
, MMT.TRANSACTION_SOURCE_NAME
, MMT.TRANSFER_ORGANIZATION_ID
, MP1.ORGANIZATION_CODE
, HAOU1.NAME
, MMT.TRANSFER_SUBINVENTORY
, MMT.TRANSFER_LOCATOR_ID
, MIL1.DESCRIPTION
, MIL1.CONCATENATED_SEGMENTS
, MMT.TRANSACTION_UOM
, ABS(DECODE(MTLT.TRANSACTION_QUANTITY
, NULL
, MMT.TRANSACTION_QUANTITY
, MTLT.TRANSACTION_QUANTITY))
, WDTH.TASK_ID
, WDTH.USER_TASK_TYPE
, BSO.OPERATION_CODE
, WDTH.PERSON_ID
, PAPF.LAST_NAME
, PAPF.FIRST_NAME
, PAPF.FULL_NAME
, WDTH.EFFECTIVE_START_DATE
, WDTH.EFFECTIVE_END_DATE
, WDTH.EQUIPMENT_ID
, MSI1.CONCATENATED_SEGMENTS
, MSI1.DESCRIPTION
, WDTH.EQUIPMENT_INSTANCE
, WDTH.PERSON_RESOURCE_ID
, BR1.RESOURCE_CODE
, WDTH.MACHINE_RESOURCE_ID
, BR2.RESOURCE_CODE
, WDTH.STATUS
, ML2.MEANING
, WDTH.DISPATCHED_TIME
, WDTH.LOADED_TIME
, WDTH.DROP_OFF_TIME
, MMT.LAST_UPDATE_DATE
, MMT.LAST_UPDATED_BY
, MMT.CREATION_DATE
, MMT.CREATED_BY
, MMT.LAST_UPDATE_LOGIN
, WDTH.ATTRIBUTE_CATEGORY
, WDTH.ATTRIBUTE1
, WDTH.ATTRIBUTE2
, WDTH.ATTRIBUTE3
, WDTH.ATTRIBUTE4
, WDTH.ATTRIBUTE5
, WDTH.ATTRIBUTE6
, WDTH.ATTRIBUTE7
, WDTH.ATTRIBUTE8
, WDTH.ATTRIBUTE9
, WDTH.ATTRIBUTE10
, WDTH.ATTRIBUTE11
, WDTH.ATTRIBUTE12
, WDTH.ATTRIBUTE13
, WDTH.ATTRIBUTE14
, WDTH.ATTRIBUTE15
, WE.REASON_ID
, WDTH.PRIORITY
, BSO.OPERATION_DESCRIPTION
, WDTH.TASK_TYPE
, ML3.MEANING
, MTR.REASON_NAME
FROM MTL_TRANSACTION_LOT_NUMBERS MTLT
, MTL_PARAMETERS MP
, HR_ALL_ORGANIZATION_UNITS HAOU
, MTL_PARAMETERS MP1
, HR_ALL_ORGANIZATION_UNITS HAOU1
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_SYSTEM_ITEMS_KFV MSI1
, MTL_ITEM_LOCATIONS_KFV MIL
, MTL_ITEM_LOCATIONS_KFV MIL1
, MTL_TRANSACTION_TYPES MTT
, MTL_TXN_SOURCE_TYPES MTST
, MFG_LOOKUPS ML1
, BOM_STANDARD_OPERATIONS BSO
, PER_ALL_PEOPLE_F PAPF
, BOM_RESOURCES BR1
, BOM_RESOURCES BR2
, WMS_DISPATCHED_TASKS_HISTORY WDTH
, WMS_EXCEPTIONS WE
, MTL_MATERIAL_TRANSACTIONS MMT
, MFG_LOOKUPS ML2
, MFG_LOOKUPS ML3
, MTL_TRANSACTION_REASONS MTR
WHERE MP.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND HAOU.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MP1.ORGANIZATION_ID (+)= MMT.TRANSFER_ORGANIZATION_ID
AND HAOU1.ORGANIZATION_ID (+)= MMT.TRANSFER_ORGANIZATION_ID
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MIL.ORGANIZATION_ID (+)= MMT.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID (+)= MMT.LOCATOR_ID
AND MIL1.ORGANIZATION_ID (+)= MMT.TRANSFER_ORGANIZATION_ID
AND MIL1.INVENTORY_LOCATION_ID (+)= MMT.TRANSFER_LOCATOR_ID
AND MTLT.TRANSACTION_ID (+)= MMT.TRANSACTION_ID
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
AND ML1.LOOKUP_TYPE = 'MTL_TRANSACTION_ACTION'
AND ML1.LOOKUP_CODE = MMT.TRANSACTION_ACTION_ID
AND MTST.TRANSACTION_SOURCE_TYPE_ID = MMT.TRANSACTION_SOURCE_TYPE_ID
AND WDTH.TRANSACTION_ID = MMT.TRANSACTION_SET_ID
AND WE.TRANSACTION_HEADER_ID = MMT.TRANSACTION_SET_ID
AND WE.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND NVL(WE.REVISION
, '###') = NVL(MMT.REVISION
, '###')
AND NVL(WE.SUBINVENTORY_CODE
, '###') = NVL(MMT.SUBINVENTORY_CODE
, '###')
AND NVL(WE.INVENTORY_LOCATION_ID
, -999) = NVL(MMT.LOCATOR_ID
, -999)
AND MMT.TRANSACTION_QUANTITY <= DECODE(MMT.TRANSFER_TRANSACTION_ID
, NULL
, MMT.TRANSACTION_QUANTITY
, 0)
AND BSO.STANDARD_OPERATION_ID (+) = WDTH.USER_TASK_TYPE
AND MSI1.ORGANIZATION_ID (+)= WDTH.ORGANIZATION_ID
AND MSI1.INVENTORY_ITEM_ID (+)= WDTH.EQUIPMENT_ID
AND PAPF.PERSON_ID = WDTH.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN NVL(PAPF.EFFECTIVE_START_DATE
, TRUNC(SYSDATE))
AND NVL(PAPF.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
AND BR1.RESOURCE_ID (+)= WDTH.PERSON_RESOURCE_ID
AND BR1.RESOURCE_TYPE (+)=2
AND BR2.RESOURCE_ID (+)= WDTH.MACHINE_RESOURCE_ID
AND BR2.RESOURCE_TYPE (+)=1
AND ML2.LOOKUP_TYPE = 'WMS_TASK_STATUS'
AND ML2.LOOKUP_CODE = 6
AND ML3.LOOKUP_TYPE (+)= 'WMS_TASK_TYPES'
AND ML3.LOOKUP_CODE (+)=WDTH.TASK_TYPE
AND MTR.REASON_ID (+)= WE.REASON_ID

Columns