SELECT /*+ index(mmtt MTL_MATERIAL_TRANS_TEMP_N13) */
'MMTT_RECORD' record_source
,mmtt.pick_slip_line_number pick_slip_line -- use a temporary holder for Pick Slip Line Number
,mol.LINE_NUMBER
,mol.QUANTITY mol_quantity -- quantity requested
,mol.REQUIRED_QUANTITY
,mol.QUANTITY_DELIVERED
,mol.QUANTITY_DETAILED
,mol.UOM_CODE mol_uom_code
,mol.SECONDARY_QUANTITY mol_secondary_quantity
,mol.SECONDARY_QUANTITY_DELIVERED
,mol.SECONDARY_QUANTITY_DETAILED
,mol.SECONDARY_REQUIRED_QUANTITY
,mol. SECONDARY_UOM_CODE mol_secondary_uom_code
,moh.REQUEST_NUMBER
,mol.DATE_REQUIRED mol_date_required
,moh.MOVE_ORDER_TYPE -- pick wage, repleneshment or request
,moh.DATE_REQUIRED moh_date_required
,moh.CREATED_BY moh_created_by
,moh.TRANSACTION_TYPE_ID moh_transaction_type_id
,wdd.source_header_number
,wdd.source_line_number
,wdd.ship_to_location_id -- need to join to TCA
,wdd.ship_to_party_id -- need to join to TCA
,wdd.shipment_priority_code -- need to join to FND_LOOKUPS
,wdd.date_requested
,wdd.ship_from_location_id
,wdd.ship_method_code -- need to join to FND_LOOKUPS
,wdd.ship_to_party_id customer_id -- need to join to TCA
,wnd.delivery_id wnd_delivery_id
,wnd.delivery_name delivery_number
,wpb.batch_id
,wpb.picking_batch_name wave_number
, mmtt.transaction_header_id
, mmtt.transaction_temp_id transaction_id
, mmtt.source_code
, mmtt.source_line_id
, mmtt.transaction_mode
, mmtt.lock_flag
, mmtt.last_update_date
, mmtt.last_updated_by
, mmtt.creation_date
, mmtt.created_by
, mmtt.last_update_login
, mmtt.request_id
, mmtt.inventory_item_id
, mmtt.revision
, mmtt.organization_id
, mmtt.subinventory_code
, mmtt.locator_id
, transaction_quantity
, mmtt.primary_quantity
, transaction_uom
, transaction_cost
, mmtt.transaction_type_id
, mmtt.transaction_action_id
, mmtt.transaction_source_type_id
, mmtt.transaction_source_id
, mmtt.transaction_source_name
, mmtt.transaction_date
, distribution_account_id
, transaction_reference
, mmtt.requisition_line_id
, mmtt.requisition_distribution_id
, mmtt.reason_id
, receiving_document
, demand_id
, rcv_transaction_id
, move_transaction_id
, completion_transaction_id
, wip_entity_type
, schedule_id
, repetitive_line_id
, employee_code
, primary_switch
, schedule_update_code
, setup_teardown_code
, item_ordering
, negative_req_flag
, mmtt.operation_seq_num
, picking_line_id
, trx_source_line_id
, trx_source_delivery_id
, physical_adjustment_id
, cycle_count_id
, rma_line_id
, customer_ship_id
, mmtt.currency_code
, currency_conversion_rate
, currency_conversion_type
, currency_conversion_date
, ussgl_transaction_code
, vendor_lot_number
, ship_to_location
, shipment_number
, transfer_cost
, transportation_cost
, containers
, waybill_airbill
, expected_arrival_date
, transfer_subinventory
, nvl(transfer_organization, mmtt.organization_id) transfer_organization
, transfer_to_location
, new_average_cost
, value_change
, percentage_change
, material_allocation_temp_id
, demand_source_header_id
, demand_source_line
, demand_source_delivery
, item_segments
, mmtt.item_description
, item_trx_enabled_flag
, item_location_control_code
, item_restrict_subinv_code
, item_restrict_locators_code
, item_revision_qty_control_code
, item_primary_uom_code
, item_uom_class
, item_shelf_life_code
, item_shelf_life_days
, item_lot_control_code
, item_serial_control_code
, item_inventory_asset_flag
, allowed_units_lookup_code
, department_id
, department_code
, mmtt.wip_supply_type
, mmtt.supply_subinventory
, mmtt.supply_locator_id
, valid_subinventory_flag
, valid_locator_flag
, locator_segments
, current_locator_control_code
, number_of_lots_entered
, wip_commit_flag
, next_lot_number
, lot_alpha_prefix
, next_serial_number
, serial_alpha_prefix
, shippable_flag
, posting_flag
, required_flag
, process_flag
, ERROR_CODE
, error_explanation
, mmtt.movement_id
, reservation_quantity
, mmtt.shipped_quantity
, transaction_line_number
, mmtt.task_id
, mmtt.to_task_id
, mmtt.project_id
, mmtt.to_project_id
, final_completion_flag
, transfer_percentage
, flow_schedule
, overcompletion_transaction_id
, overcompletion_primary_qty
, overcompletion_transaction_qty
, line_type_code
, parent_transaction_temp_id parent_transaction_id
, mmtt.put_away_strategy_id
, mmtt.put_away_rule_id
, mmtt.pick_strategy_id
, mmtt.pick_rule_id
, common_bom_seq_id
, common_routing_seq_id
, mmtt.move_order_line_id
, task_group_id
, mmtt.pick_slip_number
, reservation_id
, transaction_status
, mmtt.pick_slip_date
, secondary_transaction_quantity -- INVCONV change
, mmtt.secondary_uom_code -- INVCONV change
, mmtt.picked_quantity
, mmtt.secondary_picked_quantity
, mmtt.lot_number
, nvl(mmtt.orig_transaction_quantity, mmtt.transaction_quantity) orig_transaction_quantity
, nvl(mmtt.orig_subinventory_code, mmtt.subinventory_code) orig_subinventory_code
, nvl(mmtt.orig_locator_id, mmtt.locator_id) orig_locator_id
, nvl(mmtt.orig_lot_number, mmtt.lot_number) orig_lot_number
, mmtt.PJC_CONTEXT_CATEGORY
, mmtt.PJC_PROJECT_ID
, mmtt.PJC_TASK_ID
, mmtt.PJC_EXPENDITURE_TYPE_ID
, mmtt.PJC_EXPENDITURE_ITEM_DATE
, mmtt.PJC_BILLABLE_FLAG
, mmtt.PJC_CAPITALIZABLE_FLAG
, mmtt.PJC_WORK_TYPE_ID
, mmtt.PJC_CONTRACT_ID
, mmtt.PJC_CONTRACT_LINE_ID
, mmtt.PJC_FUNDING_ALLOCATION_ID
, mmtt.PJC_RESERVED_ATTRIBUTE1
, mmtt.PJC_RESERVED_ATTRIBUTE2
, mmtt.PJC_RESERVED_ATTRIBUTE3
, mmtt.PJC_RESERVED_ATTRIBUTE4
, mmtt.PJC_RESERVED_ATTRIBUTE5
, mmtt.PJC_RESERVED_ATTRIBUTE6
, mmtt.PJC_RESERVED_ATTRIBUTE7
, mmtt.PJC_RESERVED_ATTRIBUTE8
, mmtt.PJC_RESERVED_ATTRIBUTE9
, mmtt.PJC_RESERVED_ATTRIBUTE10
, mmtt.PJC_USER_DEF_ATTRIBUTE1
, mmtt.PJC_USER_DEF_ATTRIBUTE2
, mmtt.PJC_USER_DEF_ATTRIBUTE3
, mmtt.PJC_USER_DEF_ATTRIBUTE4
, mmtt.PJC_USER_DEF_ATTRIBUTE5
, mmtt.PJC_USER_DEF_ATTRIBUTE6
, mmtt.PJC_USER_DEF_ATTRIBUTE7
, mmtt.PJC_USER_DEF_ATTRIBUTE8
, mmtt.PJC_USER_DEF_ATTRIBUTE9
, mmtt.PJC_USER_DEF_ATTRIBUTE10
, mmtt.PJC_ORGANIZATION_ID
, mmtt.ATTRIBUTE_CATEGORY
, mmtt.ATTRIBUTE1
, mmtt.ATTRIBUTE2
, mmtt.ATTRIBUTE3
, mmtt.ATTRIBUTE4
, mmtt.ATTRIBUTE5
, mmtt.ATTRIBUTE6
, mmtt.ATTRIBUTE7
, mmtt.ATTRIBUTE8
, mmtt.ATTRIBUTE9
, mmtt.ATTRIBUTE10
, mmtt.ATTRIBUTE11
, mmtt.ATTRIBUTE12
, mmtt.ATTRIBUTE13
, mmtt.ATTRIBUTE14
, mmtt.ATTRIBUTE15
, mmtt.ATTRIBUTE16
, mmtt.ATTRIBUTE17
, mmtt.ATTRIBUTE18
, mmtt.ATTRIBUTE19
, mmtt.ATTRIBUTE20
, mmtt.ATTRIBUTE_NUMBER1
, mmtt.ATTRIBUTE_NUMBER2
, mmtt.ATTRIBUTE_NUMBER3
, mmtt.ATTRIBUTE_NUMBER4
, mmtt.ATTRIBUTE_NUMBER5
, mmtt.ATTRIBUTE_NUMBER6
, mmtt.ATTRIBUTE_NUMBER7
, mmtt.ATTRIBUTE_NUMBER8
, mmtt.ATTRIBUTE_NUMBER9
, mmtt.ATTRIBUTE_NUMBER10
, mmtt.ATTRIBUTE_DATE1
, mmtt.ATTRIBUTE_DATE2
, mmtt.ATTRIBUTE_DATE3
, mmtt.ATTRIBUTE_DATE4
, mmtt.ATTRIBUTE_DATE5
, mmtt.ATTRIBUTE_TIMESTAMP1
, mmtt.ATTRIBUTE_TIMESTAMP2
, mmtt.ATTRIBUTE_TIMESTAMP3
, mmtt.ATTRIBUTE_TIMESTAMP4
, mmtt.ATTRIBUTE_TIMESTAMP5
, mmtt.transaction_temp_id transaction_temp_id
, wdd.SALES_ORDER_NUMBER
, wdd.SALES_ORDER_LINE_NUMBER
, wdd.SALES_ORDER_SHIPMENT_NUMBER
, wdd.ship_set_name ship_set
, wdd.SOURCE_SHIPMENT_NUMBER
, wsp.OVERPICK_ENABLED
,moh.REQUEST_NUMBER newN,
 moh.HEADER_STATUS,
 moh.HEADER_ID
FROM inv_material_txns_temp mmtt
,inv_txn_request_lines mol
,inv_txn_request_headers moh
,wsh_delivery_details wdd
,wsh_delivery_assignments wda
,wsh_new_deliveries wnd
,wsh_picking_batches wpb
,wsh_shipping_parameters wsp
where mmtt.move_order_line_id = mol.line_id and
mol.header_id = moh.header_id and
mmtt.trx_source_line_id = wdd.delivery_detail_id (+) and
wdd.batch_id = wpb.batch_id (+) and
wdd.delivery_detail_id = wda.delivery_detail_id (+) and
wda.delivery_id = wnd.delivery_id (+) and
mmtt.organization_id = wsp.organization_id (+)
--and mmtt.PICK_SLIP_NUMBER=275039
union all
SELECT /*+ index(mmt MTL_MATERIAL_TRANSACTIONS_N16) */
'MMT_RECORD' record_source
,mmt.pick_slip_line_number pick_slip_line
,mol.LINE_NUMBER
,mol.QUANTITY -- quantity requested
,mol.REQUIRED_QUANTITY
,mol.QUANTITY_DELIVERED
,mol.QUANTITY_DETAILED
,mol.UOM_CODE
,mol.SECONDARY_QUANTITY
,mol.SECONDARY_QUANTITY_DELIVERED
,mol.SECONDARY_QUANTITY_DETAILED
,mol.SECONDARY_REQUIRED_QUANTITY
,mol. SECONDARY_UOM_CODE
,moh.REQUEST_NUMBER
,mol.DATE_REQUIRED mol_date_required
,moh.MOVE_ORDER_TYPE -- pick wage, repleneshment or request
,moh.DATE_REQUIRED moh_date_required
,moh.CREATED_BY moh_created_by
,moh.TRANSACTION_TYPE_ID moh_transaction_type_id
,wdd.source_header_number
,wdd.source_line_number
,wdd.ship_to_location_id -- need to join to TCA
,wdd.ship_to_party_id -- need to join to TCA
,wdd.shipment_priority_code -- need to join to FND_LOOKUPS
,wdd.date_requested
,wdd.ship_from_location_id
,wdd.ship_method_code -- need to join to FND_LOOKUPS
,wdd.ship_to_party_id customer_id -- need to join to TCA
,wnd.delivery_id wnd_delivery_id
,wnd.delivery_name delivery_number
,wpb.batch_id
,wpb.picking_batch_name wave_number
,mmt.parent_transaction_id transaction_header_id
, mmt.transaction_id
, mmt.source_code
, mmt.source_line_id
,null transaction_mode -- transaction mode stored as string in MMTT
, null lock_flag
, mmt.last_update_date
, mmt.last_updated_by
, mmt.creation_date
, mmt.created_by
, mmt.last_update_login
, mmt.request_id
, mmt.inventory_item_id
, mmt.revision
, mmt.organization_id
, mmt.subinventory_code
, mmt.locator_id
, ABS(mmt.transaction_quantity) transaction_quantity
, ABS(mmt.primary_quantity) primary_quantity
, transaction_uom
, transaction_cost
, mmt.transaction_type_id
, null transaction_action_id -- transaction action stored as string in MMTT
, mmt.transaction_source_type_id
, mmt.transaction_source_id
, mmt.transaction_source_name
, mmt.transaction_date
, distribution_account_id
, transaction_reference
, null requisition_line_id
, null requisition_distribution_id
, mmt.reason_id
, receiving_document
, null demand_id
, rcv_transaction_id
, move_transaction_id
, completion_transaction_id
, null wip_entity_type
, null schedule_id
, repetitive_line_id
, employee_code
, null primary_switch
, null schedule_update_code --master_schedule_update_code
, null setup_teardown_code
, null item_ordering
, null negative_req_flag
, mmt.operation_seq_num
, picking_line_id
, trx_source_line_id
, trx_source_delivery_id
, physical_adjustment_id
, cycle_count_id
, rma_line_id
, null customer_ship_id
, mmt.currency_code
, currency_conversion_rate
, currency_conversion_type
, currency_conversion_date
, ussgl_transaction_code
, vendor_lot_number
, mmt.ship_to_location_id ship_to_location
, shipment_number
, transfer_cost
, transportation_cost
, null containers
, waybill_airbill
, null expected_arrival_date
, transfer_subinventory
, transfer_organization_id transfer_organization
, transfer_locator_id transfer_to_location
, null new_average_cost
, value_change
, percentage_change
, null material_allocation_temp_id
, null demand_source_header_id -- may be replaced by transaction_source_id
, null demand_source_line -- may be replaced by trx_source_line_id
, null demand_source_delivery -- may be replaced by trx_source_delivery_id
, null item_segments
, null item_description
, null item_trx_enabled_flag
, null item_location_control_code
, null item_restrict_subinv_code
, null item_restrict_locators_code
, null item_revision_qty_control_code
, null item_primary_uom_code
, null item_uom_class
, null item_shelf_life_code
, null item_shelf_life_days
, null item_lot_control_code
, null item_serial_control_code
, null item_inventory_asset_flag
, null allowed_units_lookup_code
, department_id
, department_code
, null wip_supply_type
, null supply_subinventory
, null supply_locator_id
, null valid_subinventory_flag
, null valid_locator_flag
, null locator_segments
, null current_locator_control_code
, null number_of_lots_entered
, null wip_commit_flag
, null next_lot_number
, null lot_alpha_prefix
, null next_serial_number
, null serial_alpha_prefix
, null shippable_flag
, null posting_flag
, null required_flag
, null process_flag
, ERROR_CODE
, error_explanation
, mmt.movement_id
, null reservation_quantity
, null shipped_quantity
, null transaction_line_number
, mmt.task_id
, mmt.to_task_id
, mmt.project_id
, mmt.to_project_id
, final_completion_flag
, transfer_percentage
, flow_schedule
, overcompletion_transaction_id
, overcompletion_primary_qty
, overcompletion_transaction_qty
, null line_type_code
, parent_transaction_id
, mmt.put_away_strategy_id
, mmt.put_away_rule_id
, mmt.pick_strategy_id
, mmt.pick_rule_id
, common_bom_seq_id
, common_routing_seq_id
, mmt.move_order_line_id
, task_group_id
, mmt.pick_slip_number
, reservation_id
, null transaction_status
, mmt.pick_slip_date
, ABS(secondary_transaction_quantity) secondary_transaction_quantity -- INVCONV change
, mmt.secondary_uom_code -- INVCONV change
, ABS(mmt.transaction_quantity) picked_quantity
, ABS(mmt.secondary_transaction_quantity) secondary_picked_quantity
, null lot_number
, mmt.orig_transaction_quantity
, mmt.orig_subinventory_code
, mmt.orig_locator_id
, mmt.orig_lot_number
, mmt.PJC_CONTEXT_CATEGORY
, mmt.PJC_PROJECT_ID
, mmt.PJC_TASK_ID
, mmt.PJC_EXPENDITURE_TYPE_ID
, mmt.PJC_EXPENDITURE_ITEM_DATE
, mmt.PJC_BILLABLE_FLAG
, mmt.PJC_CAPITALIZABLE_FLAG
, mmt.PJC_WORK_TYPE_ID
, mmt.PJC_CONTRACT_ID
, mmt.PJC_CONTRACT_LINE_ID
, mmt.PJC_FUNDING_ALLOCATION_ID
, mmt.PJC_RESERVED_ATTRIBUTE1
, mmt.PJC_RESERVED_ATTRIBUTE2
, mmt.PJC_RESERVED_ATTRIBUTE3
, mmt.PJC_RESERVED_ATTRIBUTE4
, mmt.PJC_RESERVED_ATTRIBUTE5
, mmt.PJC_RESERVED_ATTRIBUTE6
, mmt.PJC_RESERVED_ATTRIBUTE7
, mmt.PJC_RESERVED_ATTRIBUTE8
, mmt.PJC_RESERVED_ATTRIBUTE9
, mmt.PJC_RESERVED_ATTRIBUTE10
, mmt.PJC_USER_DEF_ATTRIBUTE1
, mmt.PJC_USER_DEF_ATTRIBUTE2
, mmt.PJC_USER_DEF_ATTRIBUTE3
, mmt.PJC_USER_DEF_ATTRIBUTE4
, mmt.PJC_USER_DEF_ATTRIBUTE5
, mmt.PJC_USER_DEF_ATTRIBUTE6
, mmt.PJC_USER_DEF_ATTRIBUTE7
, mmt.PJC_USER_DEF_ATTRIBUTE8
, mmt.PJC_USER_DEF_ATTRIBUTE9
, mmt.PJC_USER_DEF_ATTRIBUTE10
, mmt.PJC_ORGANIZATION_ID
, mmt.ATTRIBUTE_CATEGORY
, mmt.ATTRIBUTE1
, mmt.ATTRIBUTE2
, mmt.ATTRIBUTE3
, mmt.ATTRIBUTE4
, mmt.ATTRIBUTE5
, mmt.ATTRIBUTE6
, mmt.ATTRIBUTE7
, mmt.ATTRIBUTE8
, mmt.ATTRIBUTE9
, mmt.ATTRIBUTE10
, mmt.ATTRIBUTE11
, mmt.ATTRIBUTE12
, mmt.ATTRIBUTE13
, mmt.ATTRIBUTE14
, mmt.ATTRIBUTE15
, mmt.ATTRIBUTE16
, mmt.ATTRIBUTE17
, mmt.ATTRIBUTE18
, mmt.ATTRIBUTE19
, mmt.ATTRIBUTE20
, mmt.ATTRIBUTE_NUMBER1
, mmt.ATTRIBUTE_NUMBER2
, mmt.ATTRIBUTE_NUMBER3
, mmt.ATTRIBUTE_NUMBER4
, mmt.ATTRIBUTE_NUMBER5
, mmt.ATTRIBUTE_NUMBER6
, mmt.ATTRIBUTE_NUMBER7
, mmt.ATTRIBUTE_NUMBER8
, mmt.ATTRIBUTE_NUMBER9
, mmt.ATTRIBUTE_NUMBER10
, mmt.ATTRIBUTE_DATE1
, mmt.ATTRIBUTE_DATE2
, mmt.ATTRIBUTE_DATE3
, mmt.ATTRIBUTE_DATE4
, mmt.ATTRIBUTE_DATE5
, mmt.ATTRIBUTE_TIMESTAMP1
, mmt.ATTRIBUTE_TIMESTAMP2
, mmt.ATTRIBUTE_TIMESTAMP3
, mmt.ATTRIBUTE_TIMESTAMP4
, mmt.ATTRIBUTE_TIMESTAMP5
, mmt.original_transaction_temp_id transaction_temp_id
, wdd.SALES_ORDER_NUMBER
, wdd.SALES_ORDER_LINE_NUMBER
, wdd.SALES_ORDER_SHIPMENT_NUMBER
, wdd.ship_set_name ship_set
, wdd.SOURCE_SHIPMENT_NUMBER
, wsp.OVERPICK_ENABLED
,moh.REQUEST_NUMBER newN
,moh.HEADER_STATUS,
moh.HEADER_ID
FROM inv_material_txns mmt
,inv_txn_request_lines mol
,inv_txn_request_headers moh
,wsh_delivery_details wdd
,wsh_delivery_assignments wda
,wsh_new_deliveries wnd
,wsh_picking_batches wpb
,wsh_shipping_parameters wsp
where mmt.move_order_line_id = mol.line_id and
mol.header_id = moh.header_id and
mmt.trx_source_line_id = wdd.delivery_detail_id (+) and
wdd.batch_id = wpb.batch_id (+) and
wdd.delivery_detail_id = wda.delivery_detail_id (+) and
wda.delivery_id = wnd.delivery_id (+) and
mmt.organization_id = wsp.organization_id (+)
--and mmt.PICK_SLIP_NUMBER=275039
and mmt.transaction_quantity < 0