View: WMS_WAVEPLAN_TASK_EXCEPTIONS_V

Product:	WMS - Warehouse Management
Description:	WMS Wave Planning Task Exceptions View
Implementation/DBA Data:	APPS.WMS_WAVEPLAN_TASK_EXCEPTIONS_V

View Text

SELECT DISTINCT WE.ORGANIZATION_ID ORGANIZATION_ID
, WE.TASK_ID TASK_ID
, WE.TRANSACTION_ID TRANSACTION_ID
, MTR.REASON_ID REASON_ID
, MTR.REASON_NAME REASON_NAME
, MTR.DESCRIPTION REASON_DESCRIPTION
, WE.EXCEPTION_TIME EXCEPTION_TIME
, WE.PERSON_ID PERSON_ID
, PAP.FULL_NAME FULL_NAME
, WE.SUBINVENTORY_CODE SUBINVENTORY_CODE
, WE.LOCATOR_ID LOCATOR_ID
, MILV.CONCATENATED_SEGMENTS LOCATOR
, WE.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSIV.CONCATENATED_SEGMENTS ITEM
, WE.LINE_ID LINE_ID
, WE.MOVE_ORDER_LINE_NUMBER MOVE_ORDER_LINE_NUMBER
, MTRH.REQUEST_NUMBER MOVE_ORDER_NUMBER
FROM ( SELECT WE.ORGANIZATION_ID
, WDTH.TASK_ID
, TO_NUMBER(DECODE(WDTH.TRANSACTION_ID
, 0
, NULL
, NVL(MMT.TRANSACTION_ID
, WDTH.TRANSACTION_ID))) TRANSACTION_ID
, WE.REASON_ID
, WE.CREATION_DATE EXCEPTION_TIME
, WE.PERSON_ID
, WE.SUBINVENTORY_CODE
, WE.INVENTORY_LOCATION_ID LOCATOR_ID
, WE.INVENTORY_ITEM_ID
, MTRL.LINE_ID
, MTRL.LINE_NUMBER MOVE_ORDER_LINE_NUMBER
, MTRL.HEADER_ID MTRL_HEADER_ID
FROM WMS_EXCEPTIONS WE
, WMS_DISPATCHED_TASKS_HISTORY WDTH
, MTL_TXN_REQUEST_LINES MTRL
, WMS_WAVEPLAN_TASKS_TEMP WWTT
, MTL_MATERIAL_TRANSACTIONS MMT
WHERE WDTH.TRANSACTION_TEMP_ID = WE.TASK_ID
AND WDTH.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WDTH.MOVE_ORDER_LINE_ID = MTRL.LINE_ID
AND WDTH.TRANSACTION_ID = MMT.TRANSACTION_SET_ID (+)
AND WDTH.TASK_TYPE <> 2
AND (WWTT.MOVE_ORDER_LINE_ID = MTRL.LINE_ID OR (WWTT.MOVE_ORDER_LINE_ID IS NULL
AND WDTH.TASK_TYPE = 8
AND WDTH.TRANSACTION_ID = WWTT.TRANSACTION_TEMP_ID))
AND WDTH.SOURCE_SUBINVENTORY_CODE = MMT.SUBINVENTORY_CODE (+)
AND WDTH.SOURCE_LOCATOR_ID = MMT.LOCATOR_ID (+)
AND NVL(MMT.TRANSACTION_BATCH_ID
, -1) = DECODE(WDTH.TASK_TYPE
, 3
, NVL(MMT.TRANSACTION_BATCH_ID
, -1)
, DECODE(WDTH.TRANSACTION_BATCH_ID
, -999
, NVL(MMT.TRANSACTION_BATCH_ID
, -1)
, NVL(WDTH.TRANSACTION_BATCH_ID
, NVL(MMT.TRANSACTION_BATCH_ID
, -1))))
AND NVL(MMT.TRANSACTION_BATCH_SEQ
, -1) = DECODE(WDTH.TASK_TYPE
, 3
, NVL(MMT.TRANSACTION_BATCH_SEQ
, -1)
, DECODE(WDTH.TRANSACTION_BATCH_SEQ
, -999
, NVL(MMT.TRANSACTION_BATCH_SEQ
, -1)
, NVL(WDTH.TRANSACTION_BATCH_SEQ
, NVL(MMT.TRANSACTION_BATCH_SEQ
, -1)))) UNION ALL SELECT WE.ORGANIZATION_ID
, WDTH.TASK_ID
, TO_NUMBER(DECODE(WDTH.TRANSACTION_ID
, 0
, NULL
, NVL(MMT.TRANSACTION_ID
, WDTH.TRANSACTION_ID))) TRANSACTION_ID
, WE.REASON_ID
, WE.CREATION_DATE EXCEPTION_TIME
, WE.PERSON_ID
, WE.SUBINVENTORY_CODE
, WE.INVENTORY_LOCATION_ID LOCATOR_ID
, WE.INVENTORY_ITEM_ID
, MTRL1.LINE_ID
, MTRL1.LINE_NUMBER MOVE_ORDER_LINE_NUMBER
, MTRL1.HEADER_ID MTRL_HEADER_ID
FROM WMS_EXCEPTIONS WE
, WMS_DISPATCHED_TASKS_HISTORY WDTH
, MTL_TXN_REQUEST_LINES MTRL1
, MTL_TXN_REQUEST_LINES MTRL
, WMS_WAVEPLAN_TASKS_TEMP WWTT
, MTL_MATERIAL_TRANSACTIONS MMT
WHERE WDTH.TRANSACTION_TEMP_ID = WE.TASK_ID
AND WDTH.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WDTH.MOVE_ORDER_LINE_ID = MTRL1.LINE_ID
AND MTRL1.TXN_SOURCE_ID = MTRL.TXN_SOURCE_ID
AND MTRL1.ORGANIZATION_ID = MTRL.ORGANIZATION_ID
AND MTRL1.INVENTORY_ITEM_ID = MTRL.INVENTORY_ITEM_ID
AND NVL(MTRL1.TXN_SOURCE_LINE_ID
, -1) = NVL(MTRL.TXN_SOURCE_LINE_ID
, -1)
AND WWTT.MOVE_ORDER_LINE_ID = MTRL.LINE_ID
AND MTRL.QUANTITY > 0
AND WDTH.TRANSACTION_ID = MMT.TRANSACTION_SET_ID (+)
AND WDTH.TASK_TYPE <> 2
AND WDTH.SOURCE_SUBINVENTORY_CODE = MMT.SUBINVENTORY_CODE (+)
AND WDTH.SOURCE_LOCATOR_ID = MMT.LOCATOR_ID (+)
AND NVL(MMT.TRANSACTION_BATCH_ID
, -1) = DECODE(WDTH.TASK_TYPE
, 3
, NVL(MMT.TRANSACTION_BATCH_ID
, -1)
, DECODE(WDTH.TRANSACTION_BATCH_ID
, -999
, NVL(MMT.TRANSACTION_BATCH_ID
, -1)
, NVL(WDTH.TRANSACTION_BATCH_ID
, NVL(MMT.TRANSACTION_BATCH_ID
, -1))))
AND NVL(MMT.TRANSACTION_BATCH_SEQ
, -1) = DECODE(WDTH.TASK_TYPE
, 3
, NVL(MMT.TRANSACTION_BATCH_SEQ
, -1)
, DECODE(WDTH.TRANSACTION_BATCH_SEQ
, -999
, NVL(MMT.TRANSACTION_BATCH_SEQ
, -1)
, NVL(WDTH.TRANSACTION_BATCH_SEQ
, NVL(MMT.TRANSACTION_BATCH_SEQ
, -1)))) UNION ALL SELECT WE.ORGANIZATION_ID
, WDTH.TASK_ID
, TO_NUMBER(DECODE(WDTH.TRANSACTION_ID
, 0
, NULL
, NVL(MMT.TRANSACTION_ID
, WDTH.TRANSACTION_ID))) TRANSACTION_ID
, WE.REASON_ID
, WE.CREATION_DATE EXCEPTION_TIME
, WE.PERSON_ID
, WE.SUBINVENTORY_CODE
, WE.INVENTORY_LOCATION_ID LOCATOR_ID
, WE.INVENTORY_ITEM_ID
, MTRL.LINE_ID
, MTRL.LINE_NUMBER MOVE_ORDER_LINE_NUMBER
, MTRL.HEADER_ID MTRL_HEADER_ID
FROM WMS_EXCEPTIONS WE
, WMS_DISPATCHED_TASKS_HISTORY WDTH
, MTL_TXN_REQUEST_LINES MTRL
, WMS_WAVEPLAN_TASKS_TEMP WWTT
, MTL_MATERIAL_TRANSACTIONS MMT
WHERE WDTH.TRANSACTION_TEMP_ID = WE.TASK_ID
AND WDTH.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WDTH.MOVE_ORDER_LINE_ID = MTRL.LINE_ID
AND NVL(WDTH.SOURCE_DOCUMENT_ID
, WDTH.TRANSACTION_ID) = MMT.TRANSACTION_SET_ID (+)
AND WDTH.TASK_TYPE = 2
AND (WWTT.MOVE_ORDER_LINE_ID = MTRL.LINE_ID OR (WWTT.MOVE_ORDER_LINE_ID IS NULL
AND WDTH.TRANSACTION_ID = WWTT.TRANSACTION_TEMP_ID))
AND WDTH.SOURCE_SUBINVENTORY_CODE = MMT.SUBINVENTORY_CODE (+)
AND WDTH.SOURCE_LOCATOR_ID = MMT.LOCATOR_ID (+) UNION ALL SELECT WE.ORGANIZATION_ID
, WDT.TASK_ID
, WDT.TRANSACTION_TEMP_ID
, WE.REASON_ID
, WE.CREATION_DATE EXCEPTION_TIME
, WE.PERSON_ID
, WE.SUBINVENTORY_CODE
, WE.INVENTORY_LOCATION_ID LOCATOR_ID
, WE.INVENTORY_ITEM_ID
, WDT.MOVE_ORDER_LINE_ID
, MTRL.LINE_NUMBER MOVE_ORDER_LINE_NUMBER
, MTRL.HEADER_ID MTRL_HEADER_ID
FROM WMS_EXCEPTIONS WE
, WMS_DISPATCHED_TASKS WDT
, WMS_WAVEPLAN_TASKS_TEMP WWTT
, MTL_TXN_REQUEST_LINES MTRL
WHERE WDT.TRANSACTION_TEMP_ID = WE.TASK_ID
AND WDT.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WDT.TRANSACTION_TEMP_ID = WWTT.TRANSACTION_TEMP_ID
AND WDT.MOVE_ORDER_LINE_ID = MTRL.LINE_ID) WE
, MTL_TRANSACTION_REASONS MTR
, PER_ALL_PEOPLE_F PAP
, MTL_ITEM_LOCATIONS_KFV MILV
, MTL_SYSTEM_ITEMS_KFV MSIV
, MTL_TXN_REQUEST_HEADERS MTRH
WHERE WE.REASON_ID = MTR.REASON_ID
AND WE.LOCATOR_ID = MILV.INVENTORY_LOCATION_ID (+)
AND WE.ORGANIZATION_ID = MILV.ORGANIZATION_ID (+)
AND PAP.PERSON_ID = WE.PERSON_ID
AND PAP.EFFECTIVE_START_DATE <= WE.EXCEPTION_TIME
AND PAP.EFFECTIVE_END_DATE >= WE.EXCEPTION_TIME
AND MSIV.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND MSIV.INVENTORY_ITEM_ID = WE.INVENTORY_ITEM_ID
AND WE.MTRL_HEADER_ID=MTRH.HEADER_ID

Columns