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_EXCEPTIONS_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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