eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.WMS_WAVEPLAN_TASK_EXCEPTIONS_V

Source

View Text - Preformatted


SELECT DISTINCT we.organization_id organization_id, we.task_id task_id, we.transaction_id transaction_id, we.reason_id reason_id, we.reason_name reason_name, we.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, mtr.reason_name, mtr.description, 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, mtl_transaction_reasons mtr 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)))) AND we.reason_id = mtr.reason_id AND mtr.reason_context_code !='PN' 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, mtr.reason_name, mtr.description, 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, mtl_transaction_reasons mtr 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)))) AND we.reason_id = mtr.reason_id AND mtr.reason_context_code !='PN' 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, mtr.reason_name, mtr.description, 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, mtl_transaction_reasons mtr 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 (+) AND we.reason_id = mtr.reason_id AND mtr.reason_context_code !='PN' UNION ALL SELECT we.organization_id, wdt.task_id, wdt.transaction_temp_id, we.reason_id, mtr.reason_name, mtr.description, 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, mtl_transaction_reasons mtr 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 AND we.reason_id = mtr.reason_id AND mtr.reason_context_code !='PN' UNION ALL SELECT we.organization_id, we.task_id, wwtt.transaction_temp_id, we.reason_id, mtr.reason_name, mtr.description , we.creation_date exception_time, we.person_id, we.subinventory_code, we.inventory_location_id locator_id, we.inventory_item_id, NULL, NULL move_order_line_number, NULL mtrl_header_id FROM wms_exceptions we, wms_waveplan_tasks_temp wwtt , mtl_transaction_reasons mtr WHERE wwtt.transaction_temp_id = we.task_id AND wwtt.organization_id = we.organization_id AND wwtt.STATUS_ID =13 AND we.reason_id = mtr.reason_id AND mtr.Reason_Context_Code ='PN' ) we, per_all_people_f pap, mtl_item_locations_kfv milv, mtl_system_items_kfv msiv, mtl_txn_request_headers mtrh WHERE 1 = 1 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(+)

View Text - HTML Formatted

SELECT DISTINCT WE.ORGANIZATION_ID ORGANIZATION_ID
, WE.TASK_ID TASK_ID
, WE.TRANSACTION_ID TRANSACTION_ID
, WE.REASON_ID REASON_ID
, WE.REASON_NAME REASON_NAME
, WE.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
, MTR.REASON_NAME
, MTR.DESCRIPTION
, 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
, MTL_TRANSACTION_REASONS MTR
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))))
AND WE.REASON_ID = MTR.REASON_ID
AND MTR.REASON_CONTEXT_CODE !='PN' 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
, MTR.REASON_NAME
, MTR.DESCRIPTION
, 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
, MTL_TRANSACTION_REASONS MTR
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))))
AND WE.REASON_ID = MTR.REASON_ID
AND MTR.REASON_CONTEXT_CODE !='PN' 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
, MTR.REASON_NAME
, MTR.DESCRIPTION
, 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
, MTL_TRANSACTION_REASONS MTR
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 (+)
AND WE.REASON_ID = MTR.REASON_ID
AND MTR.REASON_CONTEXT_CODE !='PN' UNION ALL SELECT WE.ORGANIZATION_ID
, WDT.TASK_ID
, WDT.TRANSACTION_TEMP_ID
, WE.REASON_ID
, MTR.REASON_NAME
, MTR.DESCRIPTION
, 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
, MTL_TRANSACTION_REASONS MTR
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
AND WE.REASON_ID = MTR.REASON_ID
AND MTR.REASON_CONTEXT_CODE !='PN' UNION ALL SELECT WE.ORGANIZATION_ID
, WE.TASK_ID
, WWTT.TRANSACTION_TEMP_ID
, WE.REASON_ID
, MTR.REASON_NAME
, MTR.DESCRIPTION
, WE.CREATION_DATE EXCEPTION_TIME
, WE.PERSON_ID
, WE.SUBINVENTORY_CODE
, WE.INVENTORY_LOCATION_ID LOCATOR_ID
, WE.INVENTORY_ITEM_ID
, NULL
, NULL MOVE_ORDER_LINE_NUMBER
, NULL MTRL_HEADER_ID
FROM WMS_EXCEPTIONS WE
, WMS_WAVEPLAN_TASKS_TEMP WWTT
, MTL_TRANSACTION_REASONS MTR
WHERE WWTT.TRANSACTION_TEMP_ID = WE.TASK_ID
AND WWTT.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WWTT.STATUS_ID =13
AND WE.REASON_ID = MTR.REASON_ID
AND MTR.REASON_CONTEXT_CODE ='PN' ) WE
, PER_ALL_PEOPLE_F PAP
, MTL_ITEM_LOCATIONS_KFV MILV
, MTL_SYSTEM_ITEMS_KFV MSIV
, MTL_TXN_REQUEST_HEADERS MTRH
WHERE 1 = 1
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(+)