SELECT DISTINCT QT.TASK_ID TASK_NUM, QT.PICK_SLIP, WDT.TASK_TYPE WMS_TASK_TYPE_ID, NVL(WDT.PRIORITY, 0) TASK_PRIORITY, TO_NUMBER (NULL) BATCH_ID, SUB.PICKING_ORDER SUB_PICKING_ORDER, LOC.PICKING_ORDER LOC_PICKING_ORDER, ((NVL(LOC.X_COORDINATE, 0) - :B10 ) *(NVL(LOC.X_COORDINATE, 0) - :B10 ) + (NVL(LOC.Y_COORDINATE, 0) - :B9 ) *(NVL(LOC.Y_COORDINATE, 0) - :B9 ) + (NVL(LOC.Z_COORDINATE, 0) - :B8 ) *(NVL(LOC.Z_COORDINATE, 0) - :B8 )) DISTANCE, WDT.STATUS TASK_STATUS, QT.TRANSACTION_TYPE_ID, QT.TRANSACTION_ACTION_ID, QT.TRANSACTION_SOURCE_ID, QT.TRANSACTION_SOURCE_TYPE_ID FROM WMS_DISPATCHED_TASKS WDT, (SELECT MIN(MCCE.CYCLE_COUNT_ENTRY_ID) TASK_ID, TO_NUMBER(NULL) PICK_SLIP, TO_NUMBER(NULL) CARTONIZATION_ID, MCCE.ORGANIZATION_ID, MCCE.SUBINVENTORY SUBINVENTORY_CODE, MCCE.LOCATOR_ID, TO_NUMBER(NULL) MOVE_ORDER_LINE_ID, 4 TRANSACTION_TYPE_ID, 4 TRANSACTION_ACTION_ID, MCCE.CYCLE_COUNT_HEADER_ID TRANSACTION_SOURCE_ID, 9 TRANSACTION_SOURCE_TYPE_ID FROM MTL_CYCLE_COUNT_ENTRIES MCCE, MTL_CYCLE_COUNT_HEADERS MCCH WHERE MCCE.ENTRY_STATUS_CODE IN(1, 3) AND NVL(MCCE.EXPORT_FLAG, 2) = 2 AND MCCE.CYCLE_COUNT_HEADER_ID = MCCH.CYCLE_COUNT_HEADER_ID AND MCCE.ORGANIZATION_ID = MCCH.ORGANIZATION_ID AND MCCH.ORGANIZATION_ID = :B1 AND NVL(MCCH.DISABLE_DATE,SYSDATE+1)> SYSDATE AND MCCE.CYCLE_COUNT_ENTRY_ID NOT IN (SELECT WSTE.TASK_ID FROM WMS_SKIP_TASK_EXCEPTIONS WSTE WHERE WSTE.ORGANIZATION_ID = :B1 AND WSTE.WMS_TASK_TYPE = :B3 AND ((SYSDATE - WSTE.CREATION_DATE) * 24 * 60) < :B2 ) GROUP BY MCCE.CYCLE_COUNT_HEADER_ID, MCCE.ORGANIZATION_ID, MCCE.SUBINVENTORY, MCCE.LOCATOR_ID, MCCE.INVENTORY_ITEM_ID, MCCE.REVISION) QT, MTL_SECONDARY_INVENTORIES SUB, MTL_ITEM_LOCATIONS LOC, (SELECT BSOR.STANDARD_OPERATION_ID, BRE.RESOURCE_ID, BRE.INVENTORY_ITEM_ID EQUIPMENT_ID FROM BOM_STD_OP_RESOURCES BSOR, BOM_RESOURCES BR, BOM_RESOURCE_EQUIPMENTS BRE WHERE BSOR.RESOURCE_ID = BRE.RESOURCE_ID AND BR.RESOURCE_TYPE = 1 AND BSOR.RESOURCE_ID = BR.RESOURCE_ID) E WHERE WDT.TRANSACTION_TEMP_ID = QT.TASK_ID AND WDT.STATUS IN (2,3) AND WDT.PERSON_ID = :B7 AND WDT.ORGANIZATION_ID = :B1 AND NVL(QT.CARTONIZATION_ID, -999) = NVL(:B6 , NVL(QT.CARTONIZATION_ID, -999)) AND NVL(E.EQUIPMENT_ID, -999) = NVL(:B5 , NVL(E.EQUIPMENT_ID, -999)) AND WDT.USER_TASK_TYPE = E.STANDARD_OPERATION_ID(+) AND QT.ORGANIZATION_ID = SUB.ORGANIZATION_ID AND QT.SUBINVENTORY_CODE = SUB.SECONDARY_INVENTORY_NAME AND QT.ORGANIZATION_ID = LOC.ORGANIZATION_ID AND QT.LOCATOR_ID = LOC.INVENTORY_LOCATION_ID AND NVL(:B4 , '@@@') <> 'INBOUND' UNION ALL SELECT /*+ leading(V.UTT_EMP.X_EMP_R) use_nl(v.UTT_EMP.X_UTT_RES1, WDTV.MMTT) index(WDTV.MMTT MTL_MATERIAL_TRANS_TEMP_N16) */ DISTINCT WDTV.TASK_ID TASK_NUM, NVL(WDTV.PICK_SLIP_NUMBER, -1) PICK_SLIP, WDTV.WMS_TASK_TYPE_ID, NVL(WDTV.TASK_PRIORITY, 0) TASK_PRIORITY, TO_NUMBER(NULL) BATCH_ID, SUB.PICKING_ORDER SUB_PICKING_ORDER, LOC.PICKING_ORDER LOC_PICKING_ORDER, ((NVL(LOC.X_COORDINATE, 0) - :B10 ) *(NVL(LOC.X_COORDINATE, 0) - :B10 ) + (NVL(LOC.Y_COORDINATE, 0) - :B9 ) *(NVL(LOC.Y_COORDINATE, 0) - :B9 ) + (NVL(LOC.Z_COORDINATE, 0) - :B8 ) *(NVL(LOC.Z_COORDINATE, 0) - :B8 )) DISTANCE, TASK_STATUS, WDTV.TRANSACTION_TYPE_ID, WDTV.TRANSACTION_ACTION_ID, WDTV.TRANSACTION_SOURCE_ID, WDTV.TRANSACTION_SOURCE_TYPE_ID FROM (SELECT MIN(MCCE.CYCLE_COUNT_ENTRY_ID) TASK_ID, MIN(MCCE.STANDARD_OPERATION_ID) USER_TASK_TYPE_ID, 3 WMS_TASK_TYPE_ID, MCCE.ORGANIZATION_ID, MCCE.SUBINVENTORY ZONE, MCCE.LOCATOR_ID, MIN(MCCE.TASK_PRIORITY) TASK_PRIORITY, MCCE.REVISION REVISION, MIN(MCCE.LOT_NUMBER) LOT_NUMBER, '' TRANSACTION_UOM, TO_NUMBER(NULL) TRANSACTION_QUANTITY, TO_NUMBER(NULL) PICK_RULE_ID, TO_NUMBER(NULL) PICK_SLIP_NUMBER, TO_NUMBER(NULL) CARTONIZATION_ID, MCCE.INVENTORY_ITEM_ID, TO_NUMBER(NULL) MOVE_ORDER_LINE_ID, 1 TASK_STATUS, 4 TRANSACTION_TYPE_ID, 4 TRANSACTION_ACTION_ID, MCCE.CYCLE_COUNT_HEADER_ID TRANSACTION_SOURCE_ID, 9 TRANSACTION_SOURCE_TYPE_ID FROM MTL_CYCLE_COUNT_ENTRIES MCCE, MTL_CYCLE_COUNT_HEADERS MCCH WHERE MCCE.ENTRY_STATUS_CODE IN(1, 3) AND NVL(MCCE.EXPORT_FLAG, 2) = 2 AND MCCE.CYCLE_COUNT_HEADER_ID = MCCH.CYCLE_COUNT_HEADER_ID AND MCCE.ORGANIZATION_ID = MCCH.ORGANIZATION_ID AND MCCH.ORGANIZATION_ID = :B1 AND NVL(MCCH.DISABLE_DATE,SYSDATE+1)> SYSDATE AND MCCE.CYCLE_COUNT_ENTRY_ID NOT IN (SELECT WSTE.TASK_ID FROM WMS_SKIP_TASK_EXCEPTIONS WSTE WHERE WSTE.ORGANIZATION_ID = :B1 AND WSTE.WMS_TASK_TYPE = :B3 AND ((SYSDATE - WSTE.CREATION_DATE) * 24 * 60) < :B2 ) GROUP BY MCCE.CYCLE_COUNT_HEADER_ID, MCCE.ORGANIZATION_ID, MCCE.SUBINVENTORY, MCCE.LOCATOR_ID, MCCE.INVENTORY_ITEM_ID, MCCE.REVISION) WDTV, (SELECT UTT_EMP.STANDARD_OPERATION_ID STANDARD_OPERATION_ID, UTT_EMP.RESOURCE_ID ROLE, UTT_EQP.RESOURCE_ID EQUIPMENT, UTT_EMP.PERSON_ID EMP_ID, UTT_EQP.INVENTORY_ITEM_ID EQP_ID, NULL EQP_SRL FROM (SELECT X_UTT_RES1.STANDARD_OPERATION_ID STANDARD_OPERATION_ID, X_UTT_RES1.RESOURCE_ID RESOURCE_ID, X_EMP_R.PERSON_ID FROM BOM_STD_OP_RESOURCES X_UTT_RES1, BOM_RESOURCES R1, BOM_RESOURCE_EMPLOYEES X_EMP_R WHERE X_UTT_RES1.RESOURCE_ID = R1.RESOURCE_ID AND R1.RESOURCE_TYPE = 2 AND X_UTT_RES1.RESOURCE_ID = X_EMP_R.RESOURCE_ID) UTT_EMP, (SELECT X_UTT_RES2.STANDARD_OPERATION_ID STANDARD_OPERATION_ID, X_UTT_RES2.RESOURCE_ID, X_EQP_R.INVENTORY_ITEM_ID FROM BOM_STD_OP_RESOURCES X_UTT_RES2, BOM_RESOURCES R2, BOM_RESOURCE_EQUIPMENTS X_EQP_R WHERE X_UTT_RES2.RESOURCE_ID = R2.RESOURCE_ID AND R2.RESOURCE_TYPE = 1 AND X_UTT_RES2.RESOURCE_ID = X_EQP_R.RESOURCE_ID) UTT_EQP WHERE UTT_EMP.STANDARD_OPERATION_ID = UTT_EQP.STANDARD_OPERATION_ID(+)) V, MTL_ITEM_LOCATIONS LOC, MTL_SECONDARY_INVENTORIES SUB WHERE V.EMP_ID = :B7 AND NVL(:B4 , '@@@') <> 'INBOUND' AND WDTV.ORGANIZATION_ID = :B1 AND WDTV.USER_TASK_TYPE_ID = V.STANDARD_OPERATION_ID AND NVL(WDTV.CARTONIZATION_ID, -999) = NVL(:B6 , NVL(WDTV.CARTONIZATION_ID, -999)) AND NVL(V.EQP_ID, -999) = NVL(:B5 , NVL(V.EQP_ID, -999)) AND WDTV.ORGANIZATION_ID = LOC.ORGANIZATION_ID(+) AND WDTV.LOCATOR_ID = LOC.INVENTORY_LOCATION_ID(+) AND WDTV.ZONE = SUB.SECONDARY_INVENTORY_NAME AND WDTV.ORGANIZATION_ID = SUB.ORGANIZATION_ID AND NOT EXISTS ( SELECT 1 FROM WMS_DISPATCH_TASKS_GTMP WHERE (:B4 = 'DISCRETE' AND GROUPING_DOCUMENT_NUMBER = WDTV.PICK_SLIP_NUMBER ) OR ( :B4 = 'ORDERPICK' AND GROUPING_DOCUMENT_NUMBER = WDTV.TRANSACTION_SOURCE_ID AND GROUPING_SOURCE_TYPE_ID = WDTV.TRANSACTION_SOURCE_TYPE_ID ) OR (:B4 = 'PICKBYLABEL' AND GROUPING_DOCUMENT_NUMBER = WDTV.CARTONIZATION_ID ) ) AND NOT EXISTS (SELECT NULL FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT WHERE MMTT.TRANSACTION_TEMP_ID = WDTV.TASK_ID AND MMTT.PARENT_LINE_ID IS NOT NULL AND MMTT.PARENT_LINE_ID <> MMTT.TRANSACTION_TEMP_ID AND MMTT.WMS_TASK_TYPE = WDTV.WMS_TASK_TYPE_ID) AND NOT EXISTS (SELECT NULL FROM WMS_DISPATCHED_TASKS WDT1 WHERE WDT1.TRANSACTION_TEMP_ID = WDTV.TASK_ID AND WDT1.TASK_TYPE = WDTV.WMS_TASK_TYPE_ID) AND WDTV.TASK_ID NOT IN (SELECT WDTV.TASK_ID FROM WMS_SKIP_TASK_EXCEPTIONS WSTE, MTL_PARAMETERS MP WHERE ((SYSDATE - WSTE.CREATION_DATE) * 24 * 60) < MP.SKIP_TASK_WAITING_MINUTES AND WSTE.TASK_ID = WDTV.TASK_ID AND WSTE.ORGANIZATION_ID = MP.ORGANIZATION_ID) AND WDTV.ZONE NOT IN ( SELECT WD.SUBINVENTORY_CODE FROM WMS_DEVICES_B WD , WMS_BUS_EVENT_DEVICES WBED WHERE 1 = 1 AND WD.DEVICE_ID = WBED.DEVICE_ID AND WBED.ORGANIZATION_ID = WD.ORGANIZATION_ID AND WD.ENABLED_FLAG = 'Y' AND WBED.ENABLED_FLAG = 'Y' AND WBED.BUSINESS_EVENT_ID = 10 AND WD.SUBINVENTORY_CODE IS NOT NULL AND WD.FORCE_SIGN_ON_FLAG = 'Y' AND WD.DEVICE_ID NOT IN (SELECT DEVICE_ID FROM WMS_DEVICE_ASSIGNMENT_TEMP WHERE EMPLOYEE_ID = :B7 ) ) ORDER BY TASK_PRIORITY DESC, TASK_STATUS DESC, SUB_PICKING_ORDER, LOC_PICKING_ORDER, DISTANCE, TASK_NUM