Tasks Query

select
wdth.transaction_temp_id,
wdth.task_id,
oeh.order_number,
oel.line_number,
msi.concatenated_segments item_number,
'Pick',
'Completed',
fu1.user_name assigned_to,
wdth.person_id,
mmtt.transaction_quantity,
mmtt.transaction_uom

from
apps.oe_order_headers_all oeh,
apps.wms_dispatched_tasks_history wdth,
apps.mtl_material_transactions mmtt,
apps.oe_order_lines_all oel,
apps.fnd_user fu1,
apps.mtl_system_items_kfv msi

where
msi.organization_id = mmtt.organization_id
and msi.inventory_item_id = wdth.inventory_item_id
and oel.line_id = mmtt.trx_source_line_id
and mmtt.transaction_source_type_id = wdth.transaction_source_type_id
and mmtt.transaction_type_id = wdth.transaction_type_id
and mmtt.subinventory_code = wdth.dest_subinventory_code
and mmtt.move_order_line_id = wdth.move_order_line_id
and oeh.header_id = oel.header_id
and fu1.employee_id(+) = wdth.person_id
and wdth.transaction_id = mmtt.transaction_batch_id

union

select
mmtt.transaction_temp_id,
wdt.task_id,
oeh.order_number,
oel.line_number,
msi.concatenated_segments item_number,
decode(mmtt.wms_task_type,1,'Pick',2,'Putaway',3,'Cycle Count',4,'Replenish',5,'Move Order Transfer',6,'Move Order Issue',7,'Staging Move',8,'Inspection') task_type,
flv.meaning task_status,
fu1.user_name assigned_to,
wdt.person_id,
mmtt.transaction_quantity,
mmtt.transaction_uom

from
apps.oe_order_headers_all oeh,
apps.mtl_material_transactions_temp mmtt,
apps.wms_dispatched_tasks wdt,
apps.oe_order_lines_all oel,
apps.fnd_lookup_values flv,
apps.fnd_user fu1,
apps.mtl_system_items_kfv msi

where
msi.organization_id = mmtt.organization_id
and msi.inventory_item_id = mmtt.inventory_item_id
and oel.line_id = mmtt.trx_source_line_id
and flv.lookup_type = 'WMS_TASK_STATUS'
and flv.lookup_code = nvl(wdt.status,nvl(mmtt.wms_task_status,1))
and flv.language = 'US'
and mmtt.transaction_source_type_id = 2
and wdt.transaction_temp_id(+) = mmtt.transaction_temp_id
and oeh.header_id = oel.header_id
and fu1.employee_id(+) = wdt.person_id