View: WMS_PENDING_EXCEPTIONS_V

Product:	WMS - Warehouse Management
Description:
Implementation/DBA Data:	APPS.WMS_PENDING_EXCEPTIONS_V

View Text

SELECT MMTT.ROWID
, MMTT.TRANSACTION_TEMP_ID
, MMTT.INVENTORY_ITEM_ID
, NULL
, NULL
, MMTT.ORGANIZATION_ID
, NULL
, NULL
, MMTT.REVISION
, NULL
, MMTT.SUBINVENTORY_CODE
, MMTT.LOCATOR_ID
, NULL
, NULL
, MMTT.TRANSACTION_TYPE_ID
, NULL
, MMTT.TRANSACTION_ACTION_ID
, NULL
, MMTT.TRANSACTION_SOURCE_TYPE_ID
, NULL
, MMTT.TRANSACTION_SOURCE_ID
, MMTT.TRX_SOURCE_LINE_ID
, MMTT.TRANSACTION_SOURCE_NAME
, MMTT.TRANSFER_ORGANIZATION
, NULL
, NULL
, MMTT.TRANSFER_SUBINVENTORY
, MMTT.TRANSFER_TO_LOCATION
, NULL
, NULL
, MMTT.TRANSACTION_UOM
, MMTT.TRANSACTION_QUANTITY
, TO_NUMBER(NULL)
, MMTT.STANDARD_OPERATION_ID
, NULL
, WE.PERSON_ID
, NULL
, NULL
, NULL
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, 1
, NULL
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, MMTT.LAST_UPDATE_DATE
, MMTT.LAST_UPDATED_BY
, MMTT.CREATION_DATE
, MMTT.CREATED_BY
, MMTT.LAST_UPDATE_LOGIN
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, MMTT.TASK_PRIORITY
, NULL
, MMTT.WMS_TASK_TYPE
, NULL
, MMTT.MOVE_ORDER_LINE_ID
, MMTT.PICK_SLIP_NUMBER
, WE.REASON_ID
, NULL
, MMTT.CARTONIZATION_ID
, MMTT.ALLOCATED_LPN_ID
, MMTT.CONTAINER_ITEM_ID
FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, WMS_EXCEPTIONS WE
WHERE MMTT.WMS_TASK_TYPE IN (1
, 2
, 4
, 5
, 6)
AND NOT EXISTS (SELECT 'TASK_DISPATCHED' FROM WMS_DISPATCHED_TASKS WDT WHERE WDT.TRANSACTION_TEMP_ID = MMTT.TRANSACTION_TEMP_ID AND WDT.TASK_TYPE = MMTT.WMS_TASK_TYPE)
AND MMTT.PARENT_LINE_ID IS NULL
AND WE.TRANSACTION_HEADER_ID = MMTT.TRANSACTION_HEADER_ID
AND NVL(WE.WMS_TASK_TYPE
, 1) = MMTT.WMS_TASK_TYPE UNION ALL SELECT MIN(MCCE.ROWID)
, MIN(MCCE.CYCLE_COUNT_ENTRY_ID)
, MCCE.INVENTORY_ITEM_ID
, NULL
, NULL
, MCCE.ORGANIZATION_ID
, NULL
, NULL
, MCCE.REVISION
, NULL
, MCCE.SUBINVENTORY
, MCCE.LOCATOR_ID
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, 9
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, MIN(MCCH.CYCLE_COUNT_HEADER_NAME)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, MIN(MCCE.STANDARD_OPERATION_ID)
, NULL
, MIN(WE.PERSON_ID)
, NULL
, NULL
, NULL
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, 1
, NULL
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, MIN(MCCE.LAST_UPDATE_DATE)
, MIN(MCCE.LAST_UPDATED_BY)
, MIN(MCCE.CREATION_DATE)
, MIN(MCCE.CREATED_BY)
, MIN(MCCE.LAST_UPDATE_LOGIN)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, MIN(MCCE.TASK_PRIORITY)
, NULL
, 3
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, MIN(WE.REASON_ID)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MTL_CYCLE_COUNT_ENTRIES MCCE
, WMS_EXCEPTIONS WE
, MTL_CYCLE_COUNT_HEADERS MCCH
WHERE MCCE.ENTRY_STATUS_CODE IN (1
, 3)
AND NVL(MCCE.EXPORT_FLAG
, 2) = 2
AND NVL(TRUNC(MCCE.COUNT_DUE_DATE
, 'DD')
, TRUNC(SYSDATE
, 'DD')) >= TRUNC(SYSDATE
, 'DD')
AND WE.TASK_ID = MCCE.CYCLE_COUNT_ENTRY_ID
AND WE.WMS_TASK_TYPE = 3
AND MCCE.CYCLE_COUNT_HEADER_ID = MCCH.CYCLE_COUNT_HEADER_ID GROUP BY MCCE.CYCLE_COUNT_HEADER_ID
, MCCE.ORGANIZATION_ID
, MCCE.SUBINVENTORY
, MCCE.LOCATOR_ID
, MCCE.INVENTORY_ITEM_ID
, MCCE.REVISION HAVING MIN(MCCE.CYCLE_COUNT_ENTRY_ID) NOT IN (SELECT TRANSACTION_TEMP_ID FROM WMS_DISPATCHED_TASKS WHERE TASK_TYPE = 3)

Columns