VIEW: APPS.WMS_COST_GROUPS_INPUT_V

Source

View Text - Preformatted

SELECT MTRL.LINE_ID LINE_ID, MTRL.HEADER_ID HEADER_ID, MTRL.LINE_NUMBER LINE_NUMBER, MTRL.ORGANIZATION_ID ORGANIZATION_ID, MTRL.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, MTRL.REVISION REVISION, MTRL.FROM_SUBINVENTORY_ID FROM_SUBINVENTORY_ID, MTRL.FROM_SUBINVENTORY_CODE FROM_SUBINVENTORY_CODE, MTRL.FROM_LOCATOR_ID FROM_LOCATOR_ID, MTRL.TO_SUBINVENTORY_CODE TO_SUBINVENTORY_CODE, MTRL.TO_SUBINVENTORY_ID TO_SUBINVENTORY_ID, MTRL.TO_LOCATOR_ID TO_LOCATOR_ID, MTRL.TO_ACCOUNT_ID TO_ACCOUNT_ID , MTRL.LOT_NUMBER LOT_NUMBER, MTRL.SERIAL_NUMBER_START SERIAL_NUMBER_START, MTRL.SERIAL_NUMBER_END SERIAL_NUMBER_END, MTRL.UOM_CODE UOM_CODE, MTRL.QUANTITY QUANTITY, MTRL.QUANTITY_DELIVERED QUANTITY_DELIVERED, MTRL.QUANTITY_DETAILED QUANTITY_DETAILED, MTRL.DATE_REQUIRED DATE_REQUIRED, MTRL.REASON_ID REASON_ID, MTRL.REFERENCE REFERENCE, MTRL.REFERENCE_TYPE_CODE REFERENCE_TYPE_CODE, MTRL.REFERENCE_ID REFERENCE_ID, MTRL.PROJECT_ID PROJECT_ID, MTRL.TASK_ID TASK_ID, MTRL.TRANSACTION_HEADER_ID TRANSACTION_HEADER_ID, MTRL.LINE_STATUS LINE_STATUS, MTRL.STATUS_DATE STATUS_DATE, MTRL.LAST_UPDATED_BY LAST_UPDATED_BY, MTRL.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, MTRL.LAST_UPDATE_DATE LAST_UPDATE_DATE, MTRL.CREATED_BY CREATED_BY, MTRL.CREATION_DATE CREATION_DATE, MTRL.REQUEST_ID REQUEST_ID, MTRL.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID, MTRL.PROGRAM_ID PROGRAM_ID, MTRL.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE, MTRL.ATTRIBUTE1 ATTRIBUTE1, MTRL.ATTRIBUTE2 ATTRIBUTE2, MTRL.ATTRIBUTE3 ATTRIBUTE3, MTRL.ATTRIBUTE4 ATTRIBUTE4, MTRL.ATTRIBUTE5 ATTRIBUTE5, MTRL.ATTRIBUTE6 ATTRIBUTE6, MTRL.ATTRIBUTE7 ATTRIBUTE7, MTRL.ATTRIBUTE8 ATTRIBUTE8, MTRL.ATTRIBUTE9 ATTRIBUTE9, MTRL.ATTRIBUTE10 ATTRIBUTE10, MTRL.ATTRIBUTE11 ATTRIBUTE11, MTRL.ATTRIBUTE12 ATTRIBUTE12, MTRL.ATTRIBUTE13 ATTRIBUTE13, MTRL.ATTRIBUTE14 ATTRIBUTE14, MTRL.ATTRIBUTE15 ATTRIBUTE15, MTRL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY, MTRL.TXN_SOURCE_ID TXN_SOURCE_ID, MTRL.TXN_SOURCE_LINE_ID TXN_SOURCE_LINE_ID, MTRL.TXN_SOURCE_LINE_DETAIL_ID TXN_SOURCE_LINE_DETAIL_ID, MTRL.TRANSACTION_TYPE_ID TRANSACTION_TYPE_ID, MTRL.TRANSACTION_SOURCE_TYPE_ID TRANSACTION_SOURCE_TYPE_ID, MTRL.PRIMARY_QUANTITY PRIMARY_QUANTITY, MTRL.ORGANIZATION_ID TO_ORGANIZATION_ID, MTRL.PUT_AWAY_STRATEGY_ID PUT_AWAY_STRATEGY_ID, MTRL.PICK_STRATEGY_ID PICK_STRATEGY_ID, MTRL.UNIT_NUMBER UNIT_NUMBER, MTRL.ship_TO_location_id ship_to_location_id, MTRL.from_cost_group_id from_cost_group_id, MTRL.to_cost_group_id to_cost_group_id, MTRL.lpn_id lpn_id, MTRL.to_lpn_id to_lpn_id, null source_code, to_number(null) source_line_id, to_number(null) transaction_mode, null lock_flag, to_number(null) transaction_cost, to_number(null) transaction_action_id, to_number(null) transaction_source_id, null transaction_source_name, to_date(null) transaction_date, to_number(null) acct_period_id, to_number(null) distribution_account_id, to_number(null) requisition_line_id, to_number(null) requisition_distribution_id, to_date(null) lot_expiration_date, null serial_number, null receiving_document, to_number(null) demand_id, to_number(null) rcv_transaction_id, to_number(null) move_transaction_id, to_number(null) completion_transaction_id, to_number(null) wip_entity_type, to_number(null) schedule_id, to_number(null) repetitive_line_id, null employee_code, to_number(null) primary_switch, to_number(null) schedule_update_code, to_number(null) setup_teardown_code, to_number(null) item_ordering, to_number(null) negative_req_flag, to_number(null) operation_seq_num, to_number(null) picking_line_id, to_number(null) trx_source_delivery_id, to_number(null) physical_adjustment_id, to_number(null) cycle_count_id, to_number(null) rma_line_id, to_number(null) customer_ship_id, null currency_code, to_number(null) currency_conversion_rate, null currency_conversion_type, to_date(null) currency_conversion_date, null ussgl_transaction_code, null vendor_lot_number, to_number(null) encumbrance_account, to_number(null) encumbrance_amount, null shipment_number, to_number(null) transfer_cost, to_number(null) transportation_cost, to_number(null) transportation_account, null freight_code, to_number(null) containers, null waybill_airbill, to_date(null) expected_arrival_date, to_number(null) new_average_cost, to_number(null) value_change, to_number(null) percentage_change, to_number(null) material_allocation_temp_id, to_number(null) demand_source_header_id, null demand_source_line, null demand_source_delivery, null item_segments, null item_description, null item_trx_enabled_flag, to_number(null) item_location_control_code, to_number(null) item_restrict_subinv_code, to_number(null) item_restrict_locators_code, to_number(null) item_revision_qty_control_code, null item_primary_uom_code, null item_uom_class, to_number(null) item_shelf_life_code, to_number(null) item_shelf_life_days, to_number(null) item_lot_control_code, to_number(null) item_serial_control_code, null item_inventory_asset_flag, to_number(null) allowed_units_lookup_code, to_number( null) department_id, null department_code, to_number(null) wip_supply_type, null supply_subinventory, to_number(null) supply_locator_id, null valid_subinventory_flag, null valid_locator_flag, null locator_segments, to_number(null) current_locator_control_code, to_number(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, null error_code, null error_explanation, to_number(null) movement_id, to_number(null) reservation_quantity, to_number(null) shipped_quantity, to_number(null) transaction_line_number, to_number(null) to_task_id, to_number(null) source_task_id, to_number(null) source_project_id, to_number(null) pa_expenditure_org_id, to_number(null) to_project_id, null expenditure_type, null final_completion_flag, to_number(null) transfer_percentage, to_number(null) transaction_sequence_id, to_number(null) material_account, to_number(null) material_overhead_account, to_number(null) resource_account, to_number(null) outside_processing_account, to_number(null) overhead_account, null flow_schedule, null demand_class, to_number(null) qa_collection_id, to_number(null) kanban_card_id, to_number(null) overcompletion_transaction_id, to_number(null) overcompletion_primary_qty, to_number(null) overcompletion_transaction_qty, to_date(null) scheduled_payback_date, to_number(null) line_type_code, to_number(null) parent_transaction_temp_id, to_number(null) put_away_rule_id, to_number(null) pick_rule_id, to_number(null) common_bom_seq_id, to_number(null) common_routing_seq_id, to_number(null) cost_type_id, to_number(null) org_cost_group_id, to_number(null) move_order_line_id, to_number(null) task_group_id, to_number(null) pick_slip_number, to_number(null) reservation_id, to_number(null) transaction_status, to_number(null) standard_operation_id, to_number(null) task_priority, to_number(null) wms_task_type, to_number(null) parent_line_id, mtrl.inspection_status inspection_status, decode(mtt.transaction_action_id, 12, mtrl.to_organization_id, mtrl.organization_id) source_organization_id FROM MTL_TXN_REQUEST_LINES MTRL, MTL_TRANSACTION_TYPES MTT WHERE wms_costgroupengine_pvt.getcurrentinputtype = 2 AND mtt.transaction_type_id = mtrl.transaction_type_id UNION ALL SELECT mmtt.transaction_temp_id, to_number(NULL), to_number(NULL), DECODE(mmtt.transaction_action_id, 3, mmtt.organization_id, 21, DECODE(mip.fob_point, 1, mmtt.transfer_organization, mmtt.organization_id), mmtt.organization_id), mmtt.inventory_item_id, mmtt.revision, to_number(NULL), DECODE(transaction_action_id, 1, subinventory_code, 2, subinventory_code, 3, subinventory_code,21, subinventory_code,28, subinventory_code, 29, subinventory_code,32, subinventory_code, 34, subinventory_code, NULL) from_subinventory_code, DECODE(transaction_action_id, 1, locator_id, 2, locator_id,3, locator_id, 21, locator_id, 28, locator_id,29, locator_id, 32, locator_id, 34, locator_id, to_number(NULL)) from_locator_id, DECODE(transaction_action_id, 1, NULL, 2, transfer_subinventory, 3, transfer_subinventory, 21, NULL, 28, transfer_subinventory, 29, NULL, 32, NULL,34, NULL, subinventory_code) to_subinventory_code, to_number(NULL), DECODE(transaction_action_id, 1, to_number(NULL), 2, transfer_to_location, 3, transfer_to_location, 21, to_number(NULL), 28, transfer_to_location, 29, to_number(NULL), 32, to_number(NULL), 34, to_number(NULL), locator_id) to_locator_id, to_number(NULL), NVL(mmtt.lot_number,mtlt.lot_number), NULL, NULL, mmtt.transaction_uom, mmtt.transaction_quantity, to_number(NULL), to_number(NULL), to_date(NULL), mmtt.reason_id, mmtt.transaction_reference, to_number(NULL), DECODE(source_code,'RCV', rcv_transaction_id,to_number(NULL)), mmtt.project_id, mmtt.task_id, mmtt.transaction_header_id, to_number(NULL), to_date(NULL), mmtt.last_updated_by, mmtt.last_update_login, mmtt.last_update_date, mmtt.created_by, mmtt.creation_date, mmtt.request_id, mmtt.program_application_id, mmtt.program_id, mmtt.program_update_date, 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.attribute_category, to_number(NULL), mmtt.trx_source_line_id, to_number(NULL), mmtt.transaction_type_id, mmtt.transaction_source_type_id, mmtt.primary_quantity, DECODE(transaction_action_id, 3, mmtt.transfer_organization, 21, mmtt.transfer_organization, mmtt.organization_id), mmtt.put_away_strategy_id, mmtt.pick_strategy_id, mmtt.end_item_unit_number, mmtt.ship_to_location, DECODE(mmtt.transaction_action_id, 1, mmtt.cost_group_id, 2, mmtt.cost_group_id,3, mmtt.cost_group_id, 21, mmtt.cost_group_id,28, mmtt.cost_group_id, 29, mmtt.cost_group_id, 32, mmtt.cost_group_id, 34, mmtt.cost_group_id, to_number(NULL)) from_cost_group_id, DECODE(mmtt.transaction_action_id, 1, to_number(NULL), 2, mmtt.transfer_cost_group_id, 3, mmtt.transfer_cost_group_id, 21, to_number(NULL), 28, mmtt.transfer_cost_group_id, 29, to_number(NULL), 32, to_number(NULL), 34, to_number(NULL), mmtt.cost_group_id) to_cost_group_id, mmtt.lpn_id, mmtt.transfer_lpn_id, mmtt.source_code, mmtt.source_line_id, mmtt.transaction_mode, mmtt.lock_flag, mmtt.transaction_cost, mmtt.transaction_action_id, mmtt.transaction_source_id, mmtt.transaction_source_name, mmtt.transaction_date, mmtt.acct_period_id, mmtt.distribution_account_id, mmtt.requisition_line_id, mmtt.requisition_distribution_id, NVL(mmtt. lot_expiration_date, mtlt.lot_expiration_date), NVL(mmtt.serial_number,msn.serial_number), mmtt.receiving_document, mmtt.demand_id, mmtt.rcv_transaction_id, mmtt.move_transaction_id, mmtt.completion_transaction_id, mmtt.wip_entity_type, mmtt.schedule_id, mmtt.repetitive_line_id, mmtt.employee_code, mmtt.primary_switch, mmtt.schedule_update_code, mmtt.setup_teardown_code, mmtt.item_ordering, mmtt.negative_req_flag, mmtt.operation_seq_num, mmtt.picking_line_id, mmtt.trx_source_delivery_id, mmtt.physical_adjustment_id, mmtt.cycle_count_id, mmtt.rma_line_id, mmtt.customer_ship_id, mmtt.currency_code, mmtt.currency_conversion_rate, mmtt.currency_conversion_type, mmtt.currency_conversion_date, mmtt.ussgl_transaction_code, mmtt.vendor_lot_number, mmtt.encumbrance_account, mmtt.encumbrance_amount, mmtt.shipment_number, mmtt.transfer_cost, mmtt.transportation_cost, mmtt.transportation_account, mmtt.freight_code, mmtt.containers, mmtt.waybill_airbill, mmtt.expected_arrival_date, mmtt.new_average_cost, mmtt.value_change, mmtt.percentage_change, mmtt.material_allocation_temp_id, mmtt.demand_source_header_id, mmtt.demand_source_line, mmtt.demand_source_delivery, mmtt.item_segments, mmtt.item_description, mmtt.item_trx_enabled_flag, mmtt.item_location_control_code, mmtt.item_restrict_subinv_code, mmtt.item_restrict_locators_code, mmtt.item_revision_qty_control_code, mmtt.item_primary_uom_code, mmtt.item_uom_class, mmtt.item_shelf_life_code, mmtt.item_shelf_life_days, mmtt.item_lot_control_code, mmtt.item_serial_control_code, mmtt.item_inventory_asset_flag, mmtt.allowed_units_lookup_code, mmtt.department_id, mmtt.department_code, mmtt.wip_supply_type, mmtt.supply_subinventory, mmtt.supply_locator_id, mmtt.valid_subinventory_flag, mmtt.valid_locator_flag, mmtt.locator_segments, mmtt.current_locator_control_code, mmtt.number_of_lots_entered, mmtt.wip_commit_flag, mmtt.next_lot_number, mmtt.lot_alpha_prefix, mmtt.next_serial_number, mmtt.serial_alpha_prefix, mmtt.shippable_flag, mmtt.posting_flag, mmtt.required_flag, mmtt.process_flag, mmtt.error_code, mmtt.error_explanation, mmtt.movement_id, mmtt.reservation_quantity, mmtt.shipped_quantity, mmtt.transaction_line_number, mmtt.to_task_id, mmtt.source_task_id, mmtt.source_project_id, mmtt.pa_expenditure_org_id, mmtt.to_project_id, mmtt.expenditure_type, mmtt.final_completion_flag, mmtt.transfer_percentage, mmtt.transaction_sequence_id, mmtt.material_account, mmtt.material_overhead_account, mmtt.resource_account, mmtt.outside_processing_account, mmtt.overhead_account, mmtt.flow_schedule, mmtt.demand_class, mmtt.qa_collection_id, mmtt.kanban_card_id, mmtt.overcompletion_transaction_id, mmtt.overcompletion_primary_qty, mmtt.overcompletion_transaction_qty, mmtt.scheduled_payback_date, mmtt.line_type_code, mmtt.parent_transaction_temp_id, mmtt.put_away_rule_id, mmtt.pick_rule_id, mmtt.common_bom_seq_id, mmtt.common_routing_seq_id, mmtt.cost_type_id, mmtt.org_cost_group_id, mmtt.move_order_line_id, mmtt.task_group_id, mmtt.pick_slip_number, mmtt.reservation_id, mmtt.transaction_status, mmtt.standard_operation_id, mmtt.task_priority, mmtt.wms_task_type, mmtt.parent_line_id, to_number(NULL) inspection_status, DECODE(mmtt.transaction_action_id , 12, mmtt.transfer_organization, mmtt.organization_id) FROM MTL_MATERIAL_TRANSACTIONS_TEMP mmtt, MTL_TRANSACTION_LOTS_TEMP mtlt, MTL_SERIAL_NUMBERS msn, mtl_serial_numbers_temp msnt, MTL_INTERORG_PARAMETERS mip WHERE wms_costgroupengine_pvt.getcurrentinputtype = 1 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id (+) AND msnt.transaction_temp_id = NVL (mtlt.serial_transaction_temp_id ,mmtt.transaction_temp_id ) AND msnt.fm_serial_number <= msn.serial_number AND msnt.to_serial_number >= msn.serial_number AND mmtt.inventory_item_id = msn.inventory_item_id AND NVL(mtlt.lot_number,NVL(mmtt.lot_number,'9999')) = NVL(msn.lot_number ,'9999') AND NVL(mmtt.revision,'9999') = NVL(msn.revision ,'9999') AND mip.from_organization_id (+) = mmtt.organization_id AND mip.to_organization_id (+) = mmtt. transfer_organization AND mmtt.organization_id = msn.current_organization_id UNION SELECT mmtt.transaction_temp_id, to_number(NULL), to_number(NULL), DECODE(mmtt.transaction_action_id, 3, mmtt.organization_id, 21, DECODE(mip.fob_point, 1, mmtt.transfer_organization, mmtt.organization_id), mmtt.organization_id), mmtt.inventory_item_id, mmtt.revision, to_number(NULL), DECODE(transaction_action_id, 1, subinventory_code, 2, subinventory_code, 3, subinventory_code,21, subinventory_code,28, subinventory_code, 29, subinventory_code,32, subinventory_code, 34, subinventory_code, NULL) from_subinventory_code, DECODE(transaction_action_id, 1, locator_id, 2, locator_id,3, locator_id, 21, locator_id, 28, locator_id,29, locator_id, 32, locator_id, 34, locator_id, to_number(NULL)) from_locator_id, DECODE(transaction_action_id, 1, NULL, 2, transfer_subinventory, 3, transfer_subinventory, 21, NULL, 28, transfer_subinventory, 29, subinventory_code, 32, NULL,34, NULL, subinventory_code) to_subinventory_code, to_number(NULL), DECODE(transaction_action_id, 1, to_number(NULL), 2, transfer_to_location, 3, transfer_to_location, 21, to_number(NULL), 28, transfer_to_location, 29, to_number(NULL), 32, to_number(NULL), 34, to_number(NULL), locator_id) to_locator_id, to_number(NULL), NVL(mmtt.lot_number,mtlt.lot_number), NULL, NULL, mmtt.transaction_uom, mmtt.transaction_quantity, to_number(NULL), to_number(NULL), to_date(NULL), mmtt.reason_id, mmtt.transaction_reference, to_number(NULL), DECODE(source_code,'RCV', rcv_transaction_id,to_number(NULL)), mmtt.project_id, mmtt.task_id, mmtt.transaction_header_id, to_number(NULL), to_date(NULL), mmtt.last_updated_by, mmtt.last_update_login, mmtt.last_update_date, mmtt.created_by, mmtt.creation_date, mmtt.request_id, mmtt.program_application_id, mmtt.program_id, mmtt.program_update_date, 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.attribute_category, to_number(NULL), mmtt.trx_source_line_id, to_number(NULL), mmtt.transaction_type_id, mmtt.transaction_source_type_id, mmtt.primary_quantity, DECODE(transaction_action_id, 3, mmtt.transfer_organization, 21, mmtt.transfer_organization, mmtt.organization_id), mmtt.put_away_strategy_id, mmtt.pick_strategy_id, mmtt.end_item_unit_number, mmtt.ship_to_location, DECODE(mmtt.transaction_action_id, 1, mmtt.cost_group_id, 2, mmtt.cost_group_id,3, mmtt.cost_group_id, 21, mmtt.cost_group_id,28, mmtt.cost_group_id, 29, mmtt.cost_group_id, 32, mmtt.cost_group_id, 34, mmtt.cost_group_id, to_number(NULL)) from_cost_group_id, DECODE(mmtt.transaction_action_id, 1, to_number(NULL), 2, mmtt.transfer_cost_group_id, 3, mmtt.transfer_cost_group_id, 21, to_number(NULL), 28, mmtt.transfer_cost_group_id, 29, to_number(NULL), 32, to_number(NULL), 34, to_number(NULL), mmtt.cost_group_id) to_cost_group_id, mmtt.lpn_id, mmtt.transfer_lpn_id, mmtt.source_code, mmtt.source_line_id, mmtt.transaction_mode, mmtt.lock_flag, mmtt.transaction_cost, mmtt.transaction_action_id, mmtt.transaction_source_id, mmtt.transaction_source_name, mmtt.transaction_date, mmtt.acct_period_id, mmtt.distribution_account_id, mmtt.requisition_line_id, mmtt.requisition_distribution_id, NVL(mmtt. lot_expiration_date, mtlt.lot_expiration_date), mmtt.serial_number, mmtt.receiving_document, mmtt.demand_id, mmtt.rcv_transaction_id, mmtt.move_transaction_id, mmtt.completion_transaction_id, mmtt.wip_entity_type, mmtt.schedule_id, mmtt.repetitive_line_id, mmtt.employee_code, mmtt.primary_switch, mmtt.schedule_update_code, mmtt.setup_teardown_code, mmtt.item_ordering, mmtt.negative_req_flag, mmtt.operation_seq_num, mmtt.picking_line_id, mmtt.trx_source_delivery_id, mmtt.physical_adjustment_id, mmtt.cycle_count_id, mmtt.rma_line_id, mmtt.customer_ship_id, mmtt.currency_code, mmtt.currency_conversion_rate, mmtt.currency_conversion_type, mmtt.currency_conversion_date, mmtt.ussgl_transaction_code, mmtt.vendor_lot_number, mmtt.encumbrance_account, mmtt.encumbrance_amount, mmtt.shipment_number, mmtt.transfer_cost, mmtt.transportation_cost, mmtt.transportation_account, mmtt.freight_code, mmtt.containers, mmtt.waybill_airbill, mmtt.expected_arrival_date, mmtt.new_average_cost, mmtt.value_change, mmtt.percentage_change, mmtt.material_allocation_temp_id, mmtt.demand_source_header_id, mmtt.demand_source_line, mmtt.demand_source_delivery, mmtt.item_segments, mmtt.item_description, mmtt.item_trx_enabled_flag, mmtt.item_location_control_code, mmtt.item_restrict_subinv_code, mmtt.item_restrict_locators_code, mmtt.item_revision_qty_control_code, mmtt.item_primary_uom_code, mmtt.item_uom_class, mmtt.item_shelf_life_code, mmtt.item_shelf_life_days, mmtt.item_lot_control_code, mmtt.item_serial_control_code, mmtt.item_inventory_asset_flag, mmtt.allowed_units_lookup_code, mmtt.department_id, mmtt.department_code, mmtt.wip_supply_type, mmtt.supply_subinventory, mmtt.supply_locator_id, mmtt.valid_subinventory_flag, mmtt.valid_locator_flag, mmtt.locator_segments, mmtt.current_locator_control_code, mmtt.number_of_lots_entered, mmtt.wip_commit_flag, mmtt.next_lot_number, mmtt.lot_alpha_prefix, mmtt.next_serial_number, mmtt.serial_alpha_prefix, mmtt.shippable_flag, mmtt.posting_flag, mmtt.required_flag, mmtt.process_flag, mmtt.error_code, mmtt.error_explanation, mmtt.movement_id, mmtt.reservation_quantity, mmtt.shipped_quantity, mmtt.transaction_line_number, mmtt.to_task_id, mmtt.source_task_id, mmtt.source_project_id, mmtt.pa_expenditure_org_id, mmtt.to_project_id, mmtt.expenditure_type, mmtt.final_completion_flag, mmtt.transfer_percentage, mmtt.transaction_sequence_id, mmtt.material_account, mmtt.material_overhead_account, mmtt.resource_account, mmtt.outside_processing_account, mmtt.overhead_account, mmtt.flow_schedule, mmtt.demand_class, mmtt.qa_collection_id, mmtt.kanban_card_id, mmtt.overcompletion_transaction_id, mmtt.overcompletion_primary_qty, mmtt.overcompletion_transaction_qty, mmtt.scheduled_payback_date, mmtt.line_type_code, mmtt.parent_transaction_temp_id, mmtt.put_away_rule_id, mmtt.pick_rule_id, mmtt.common_bom_seq_id, mmtt.common_routing_seq_id, mmtt.cost_type_id, mmtt.org_cost_group_id, mmtt.move_order_line_id, mmtt.task_group_id, mmtt.pick_slip_number, mmtt.reservation_id, mmtt.transaction_status, mmtt.standard_operation_id, mmtt.task_priority, mmtt.wms_task_type, mmtt.parent_line_id, to_number(NULL) inspection_status, DECODE(mmtt.transaction_action_id , 12, mmtt.transfer_organization, mmtt.organization_id) FROM MTL_MATERIAL_TRANSACTIONS_TEMP mmtt, MTL_TRANSACTION_LOTS_TEMP mtlt, MTL_INTERORG_PARAMETERS mip WHERE wms_costgroupengine_pvt.getcurrentinputtype = 1 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id (+) AND mip.from_organization_id (+) = mmtt.organization_id AND mip.to_organization_id (+) = mmtt.transfer_organization

View Text - HTML Formatted

SELECT MTRL.LINE_ID LINE_ID
, MTRL.HEADER_ID HEADER_ID
, MTRL.LINE_NUMBER LINE_NUMBER
, MTRL.ORGANIZATION_ID ORGANIZATION_ID
, MTRL.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MTRL.REVISION REVISION
, MTRL.FROM_SUBINVENTORY_ID FROM_SUBINVENTORY_ID
, MTRL.FROM_SUBINVENTORY_CODE FROM_SUBINVENTORY_CODE
, MTRL.FROM_LOCATOR_ID FROM_LOCATOR_ID
, MTRL.TO_SUBINVENTORY_CODE TO_SUBINVENTORY_CODE
, MTRL.TO_SUBINVENTORY_ID TO_SUBINVENTORY_ID
, MTRL.TO_LOCATOR_ID TO_LOCATOR_ID
, MTRL.TO_ACCOUNT_ID TO_ACCOUNT_ID
, MTRL.LOT_NUMBER LOT_NUMBER
, MTRL.SERIAL_NUMBER_START SERIAL_NUMBER_START
, MTRL.SERIAL_NUMBER_END SERIAL_NUMBER_END
, MTRL.UOM_CODE UOM_CODE
, MTRL.QUANTITY QUANTITY
, MTRL.QUANTITY_DELIVERED QUANTITY_DELIVERED
, MTRL.QUANTITY_DETAILED QUANTITY_DETAILED
, MTRL.DATE_REQUIRED DATE_REQUIRED
, MTRL.REASON_ID REASON_ID
, MTRL.REFERENCE REFERENCE
, MTRL.REFERENCE_TYPE_CODE REFERENCE_TYPE_CODE
, MTRL.REFERENCE_ID REFERENCE_ID
, MTRL.PROJECT_ID PROJECT_ID
, MTRL.TASK_ID TASK_ID
, MTRL.TRANSACTION_HEADER_ID TRANSACTION_HEADER_ID
, MTRL.LINE_STATUS LINE_STATUS
, MTRL.STATUS_DATE STATUS_DATE
, MTRL.LAST_UPDATED_BY LAST_UPDATED_BY
, MTRL.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, MTRL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, MTRL.CREATED_BY CREATED_BY
, MTRL.CREATION_DATE CREATION_DATE
, MTRL.REQUEST_ID REQUEST_ID
, MTRL.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, MTRL.PROGRAM_ID PROGRAM_ID
, MTRL.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, MTRL.ATTRIBUTE1 ATTRIBUTE1
, MTRL.ATTRIBUTE2 ATTRIBUTE2
, MTRL.ATTRIBUTE3 ATTRIBUTE3
, MTRL.ATTRIBUTE4 ATTRIBUTE4
, MTRL.ATTRIBUTE5 ATTRIBUTE5
, MTRL.ATTRIBUTE6 ATTRIBUTE6
, MTRL.ATTRIBUTE7 ATTRIBUTE7
, MTRL.ATTRIBUTE8 ATTRIBUTE8
, MTRL.ATTRIBUTE9 ATTRIBUTE9
, MTRL.ATTRIBUTE10 ATTRIBUTE10
, MTRL.ATTRIBUTE11 ATTRIBUTE11
, MTRL.ATTRIBUTE12 ATTRIBUTE12
, MTRL.ATTRIBUTE13 ATTRIBUTE13
, MTRL.ATTRIBUTE14 ATTRIBUTE14
, MTRL.ATTRIBUTE15 ATTRIBUTE15
, MTRL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, MTRL.TXN_SOURCE_ID TXN_SOURCE_ID
, MTRL.TXN_SOURCE_LINE_ID TXN_SOURCE_LINE_ID
, MTRL.TXN_SOURCE_LINE_DETAIL_ID TXN_SOURCE_LINE_DETAIL_ID
, MTRL.TRANSACTION_TYPE_ID TRANSACTION_TYPE_ID
, MTRL.TRANSACTION_SOURCE_TYPE_ID TRANSACTION_SOURCE_TYPE_ID
, MTRL.PRIMARY_QUANTITY PRIMARY_QUANTITY
, MTRL.ORGANIZATION_ID TO_ORGANIZATION_ID
, MTRL.PUT_AWAY_STRATEGY_ID PUT_AWAY_STRATEGY_ID
, MTRL.PICK_STRATEGY_ID PICK_STRATEGY_ID
, MTRL.UNIT_NUMBER UNIT_NUMBER
, MTRL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID
, MTRL.FROM_COST_GROUP_ID FROM_COST_GROUP_ID
, MTRL.TO_COST_GROUP_ID TO_COST_GROUP_ID
, MTRL.LPN_ID LPN_ID
, MTRL.TO_LPN_ID TO_LPN_ID
, NULL SOURCE_CODE
, TO_NUMBER(NULL) SOURCE_LINE_ID
, TO_NUMBER(NULL) TRANSACTION_MODE
, NULL LOCK_FLAG
, TO_NUMBER(NULL) TRANSACTION_COST
, TO_NUMBER(NULL) TRANSACTION_ACTION_ID
, TO_NUMBER(NULL) TRANSACTION_SOURCE_ID
, NULL TRANSACTION_SOURCE_NAME
, TO_DATE(NULL) TRANSACTION_DATE
, TO_NUMBER(NULL) ACCT_PERIOD_ID
, TO_NUMBER(NULL) DISTRIBUTION_ACCOUNT_ID
, TO_NUMBER(NULL) REQUISITION_LINE_ID
, TO_NUMBER(NULL) REQUISITION_DISTRIBUTION_ID
, TO_DATE(NULL) LOT_EXPIRATION_DATE
, NULL SERIAL_NUMBER
, NULL RECEIVING_DOCUMENT
, TO_NUMBER(NULL) DEMAND_ID
, TO_NUMBER(NULL) RCV_TRANSACTION_ID
, TO_NUMBER(NULL) MOVE_TRANSACTION_ID
, TO_NUMBER(NULL) COMPLETION_TRANSACTION_ID
, TO_NUMBER(NULL) WIP_ENTITY_TYPE
, TO_NUMBER(NULL) SCHEDULE_ID
, TO_NUMBER(NULL) REPETITIVE_LINE_ID
, NULL EMPLOYEE_CODE
, TO_NUMBER(NULL) PRIMARY_SWITCH
, TO_NUMBER(NULL) SCHEDULE_UPDATE_CODE
, TO_NUMBER(NULL) SETUP_TEARDOWN_CODE
, TO_NUMBER(NULL) ITEM_ORDERING
, TO_NUMBER(NULL) NEGATIVE_REQ_FLAG
, TO_NUMBER(NULL) OPERATION_SEQ_NUM
, TO_NUMBER(NULL) PICKING_LINE_ID
, TO_NUMBER(NULL) TRX_SOURCE_DELIVERY_ID
, TO_NUMBER(NULL) PHYSICAL_ADJUSTMENT_ID
, TO_NUMBER(NULL) CYCLE_COUNT_ID
, TO_NUMBER(NULL) RMA_LINE_ID
, TO_NUMBER(NULL) CUSTOMER_SHIP_ID
, NULL CURRENCY_CODE
, TO_NUMBER(NULL) CURRENCY_CONVERSION_RATE
, NULL CURRENCY_CONVERSION_TYPE
, TO_DATE(NULL) CURRENCY_CONVERSION_DATE
, NULL USSGL_TRANSACTION_CODE
, NULL VENDOR_LOT_NUMBER
, TO_NUMBER(NULL) ENCUMBRANCE_ACCOUNT
, TO_NUMBER(NULL) ENCUMBRANCE_AMOUNT
, NULL SHIPMENT_NUMBER
, TO_NUMBER(NULL) TRANSFER_COST
, TO_NUMBER(NULL) TRANSPORTATION_COST
, TO_NUMBER(NULL) TRANSPORTATION_ACCOUNT
, NULL FREIGHT_CODE
, TO_NUMBER(NULL) CONTAINERS
, NULL WAYBILL_AIRBILL
, TO_DATE(NULL) EXPECTED_ARRIVAL_DATE
, TO_NUMBER(NULL) NEW_AVERAGE_COST
, TO_NUMBER(NULL) VALUE_CHANGE
, TO_NUMBER(NULL) PERCENTAGE_CHANGE
, TO_NUMBER(NULL) MATERIAL_ALLOCATION_TEMP_ID
, TO_NUMBER(NULL) DEMAND_SOURCE_HEADER_ID
, NULL DEMAND_SOURCE_LINE
, NULL DEMAND_SOURCE_DELIVERY
, NULL ITEM_SEGMENTS
, NULL ITEM_DESCRIPTION
, NULL ITEM_TRX_ENABLED_FLAG
, TO_NUMBER(NULL) ITEM_LOCATION_CONTROL_CODE
, TO_NUMBER(NULL) ITEM_RESTRICT_SUBINV_CODE
, TO_NUMBER(NULL) ITEM_RESTRICT_LOCATORS_CODE
, TO_NUMBER(NULL) ITEM_REVISION_QTY_CONTROL_CODE
, NULL ITEM_PRIMARY_UOM_CODE
, NULL ITEM_UOM_CLASS
, TO_NUMBER(NULL) ITEM_SHELF_LIFE_CODE
, TO_NUMBER(NULL) ITEM_SHELF_LIFE_DAYS
, TO_NUMBER(NULL) ITEM_LOT_CONTROL_CODE
, TO_NUMBER(NULL) ITEM_SERIAL_CONTROL_CODE
, NULL ITEM_INVENTORY_ASSET_FLAG
, TO_NUMBER(NULL) ALLOWED_UNITS_LOOKUP_CODE
, TO_NUMBER( NULL) DEPARTMENT_ID
, NULL DEPARTMENT_CODE
, TO_NUMBER(NULL) WIP_SUPPLY_TYPE
, NULL SUPPLY_SUBINVENTORY
, TO_NUMBER(NULL) SUPPLY_LOCATOR_ID
, NULL VALID_SUBINVENTORY_FLAG
, NULL VALID_LOCATOR_FLAG
, NULL LOCATOR_SEGMENTS
, TO_NUMBER(NULL) CURRENT_LOCATOR_CONTROL_CODE
, TO_NUMBER(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
, NULL ERROR_CODE
, NULL ERROR_EXPLANATION
, TO_NUMBER(NULL) MOVEMENT_ID
, TO_NUMBER(NULL) RESERVATION_QUANTITY
, TO_NUMBER(NULL) SHIPPED_QUANTITY
, TO_NUMBER(NULL) TRANSACTION_LINE_NUMBER
, TO_NUMBER(NULL) TO_TASK_ID
, TO_NUMBER(NULL) SOURCE_TASK_ID
, TO_NUMBER(NULL) SOURCE_PROJECT_ID
, TO_NUMBER(NULL) PA_EXPENDITURE_ORG_ID
, TO_NUMBER(NULL) TO_PROJECT_ID
, NULL EXPENDITURE_TYPE
, NULL FINAL_COMPLETION_FLAG
, TO_NUMBER(NULL) TRANSFER_PERCENTAGE
, TO_NUMBER(NULL) TRANSACTION_SEQUENCE_ID
, TO_NUMBER(NULL) MATERIAL_ACCOUNT
, TO_NUMBER(NULL) MATERIAL_OVERHEAD_ACCOUNT
, TO_NUMBER(NULL) RESOURCE_ACCOUNT
, TO_NUMBER(NULL) OUTSIDE_PROCESSING_ACCOUNT
, TO_NUMBER(NULL) OVERHEAD_ACCOUNT
, NULL FLOW_SCHEDULE
, NULL DEMAND_CLASS
, TO_NUMBER(NULL) QA_COLLECTION_ID
, TO_NUMBER(NULL) KANBAN_CARD_ID
, TO_NUMBER(NULL) OVERCOMPLETION_TRANSACTION_ID
, TO_NUMBER(NULL) OVERCOMPLETION_PRIMARY_QTY
, TO_NUMBER(NULL) OVERCOMPLETION_TRANSACTION_QTY
, TO_DATE(NULL) SCHEDULED_PAYBACK_DATE
, TO_NUMBER(NULL) LINE_TYPE_CODE
, TO_NUMBER(NULL) PARENT_TRANSACTION_TEMP_ID
, TO_NUMBER(NULL) PUT_AWAY_RULE_ID
, TO_NUMBER(NULL) PICK_RULE_ID
, TO_NUMBER(NULL) COMMON_BOM_SEQ_ID
, TO_NUMBER(NULL) COMMON_ROUTING_SEQ_ID
, TO_NUMBER(NULL) COST_TYPE_ID
, TO_NUMBER(NULL) ORG_COST_GROUP_ID
, TO_NUMBER(NULL) MOVE_ORDER_LINE_ID
, TO_NUMBER(NULL) TASK_GROUP_ID
, TO_NUMBER(NULL) PICK_SLIP_NUMBER
, TO_NUMBER(NULL) RESERVATION_ID
, TO_NUMBER(NULL) TRANSACTION_STATUS
, TO_NUMBER(NULL) STANDARD_OPERATION_ID
, TO_NUMBER(NULL) TASK_PRIORITY
, TO_NUMBER(NULL) WMS_TASK_TYPE
, TO_NUMBER(NULL) PARENT_LINE_ID
, MTRL.INSPECTION_STATUS INSPECTION_STATUS
, DECODE(MTT.TRANSACTION_ACTION_ID
, 12
, MTRL.TO_ORGANIZATION_ID
, MTRL.ORGANIZATION_ID) SOURCE_ORGANIZATION_ID
FROM MTL_TXN_REQUEST_LINES MTRL
, MTL_TRANSACTION_TYPES MTT
WHERE WMS_COSTGROUPENGINE_PVT.GETCURRENTINPUTTYPE = 2
AND MTT.TRANSACTION_TYPE_ID = MTRL.TRANSACTION_TYPE_ID UNION ALL SELECT MMTT.TRANSACTION_TEMP_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(MMTT.TRANSACTION_ACTION_ID
, 3
, MMTT.ORGANIZATION_ID
, 21
, DECODE(MIP.FOB_POINT
, 1
, MMTT.TRANSFER_ORGANIZATION
, MMTT.ORGANIZATION_ID)
, MMTT.ORGANIZATION_ID)
, MMTT.INVENTORY_ITEM_ID
, MMTT.REVISION
, TO_NUMBER(NULL)
, DECODE(TRANSACTION_ACTION_ID
, 1
, SUBINVENTORY_CODE
, 2
, SUBINVENTORY_CODE
, 3
, SUBINVENTORY_CODE
, 21
, SUBINVENTORY_CODE
, 28
, SUBINVENTORY_CODE
, 29
, SUBINVENTORY_CODE
, 32
, SUBINVENTORY_CODE
, 34
, SUBINVENTORY_CODE
, NULL) FROM_SUBINVENTORY_CODE
, DECODE(TRANSACTION_ACTION_ID
, 1
, LOCATOR_ID
, 2
, LOCATOR_ID
, 3
, LOCATOR_ID
, 21
, LOCATOR_ID
, 28
, LOCATOR_ID
, 29
, LOCATOR_ID
, 32
, LOCATOR_ID
, 34
, LOCATOR_ID
, TO_NUMBER(NULL)) FROM_LOCATOR_ID
, DECODE(TRANSACTION_ACTION_ID
, 1
, NULL
, 2
, TRANSFER_SUBINVENTORY
, 3
, TRANSFER_SUBINVENTORY
, 21
, NULL
, 28
, TRANSFER_SUBINVENTORY
, 29
, NULL
, 32
, NULL
, 34
, NULL
, SUBINVENTORY_CODE) TO_SUBINVENTORY_CODE
, TO_NUMBER(NULL)
, DECODE(TRANSACTION_ACTION_ID
, 1
, TO_NUMBER(NULL)
, 2
, TRANSFER_TO_LOCATION
, 3
, TRANSFER_TO_LOCATION
, 21
, TO_NUMBER(NULL)
, 28
, TRANSFER_TO_LOCATION
, 29
, TO_NUMBER(NULL)
, 32
, TO_NUMBER(NULL)
, 34
, TO_NUMBER(NULL)
, LOCATOR_ID) TO_LOCATOR_ID
, TO_NUMBER(NULL)
, NVL(MMTT.LOT_NUMBER
, MTLT.LOT_NUMBER)
, NULL
, NULL
, MMTT.TRANSACTION_UOM
, MMTT.TRANSACTION_QUANTITY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, MMTT.REASON_ID
, MMTT.TRANSACTION_REFERENCE
, TO_NUMBER(NULL)
, DECODE(SOURCE_CODE
, 'RCV'
, RCV_TRANSACTION_ID
, TO_NUMBER(NULL))
, MMTT.PROJECT_ID
, MMTT.TASK_ID
, MMTT.TRANSACTION_HEADER_ID
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, MMTT.LAST_UPDATED_BY
, MMTT.LAST_UPDATE_LOGIN
, MMTT.LAST_UPDATE_DATE
, MMTT.CREATED_BY
, MMTT.CREATION_DATE
, MMTT.REQUEST_ID
, MMTT.PROGRAM_APPLICATION_ID
, MMTT.PROGRAM_ID
, MMTT.PROGRAM_UPDATE_DATE
, 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.ATTRIBUTE_CATEGORY
, TO_NUMBER(NULL)
, MMTT.TRX_SOURCE_LINE_ID
, TO_NUMBER(NULL)
, MMTT.TRANSACTION_TYPE_ID
, MMTT.TRANSACTION_SOURCE_TYPE_ID
, MMTT.PRIMARY_QUANTITY
, DECODE(TRANSACTION_ACTION_ID
, 3
, MMTT.TRANSFER_ORGANIZATION
, 21
, MMTT.TRANSFER_ORGANIZATION
, MMTT.ORGANIZATION_ID)
, MMTT.PUT_AWAY_STRATEGY_ID
, MMTT.PICK_STRATEGY_ID
, MMTT.END_ITEM_UNIT_NUMBER
, MMTT.SHIP_TO_LOCATION
, DECODE(MMTT.TRANSACTION_ACTION_ID
, 1
, MMTT.COST_GROUP_ID
, 2
, MMTT.COST_GROUP_ID
, 3
, MMTT.COST_GROUP_ID
, 21
, MMTT.COST_GROUP_ID
, 28
, MMTT.COST_GROUP_ID
, 29
, MMTT.COST_GROUP_ID
, 32
, MMTT.COST_GROUP_ID
, 34
, MMTT.COST_GROUP_ID
, TO_NUMBER(NULL)) FROM_COST_GROUP_ID
, DECODE(MMTT.TRANSACTION_ACTION_ID
, 1
, TO_NUMBER(NULL)
, 2
, MMTT.TRANSFER_COST_GROUP_ID
, 3
, MMTT.TRANSFER_COST_GROUP_ID
, 21
, TO_NUMBER(NULL)
, 28
, MMTT.TRANSFER_COST_GROUP_ID
, 29
, TO_NUMBER(NULL)
, 32
, TO_NUMBER(NULL)
, 34
, TO_NUMBER(NULL)
, MMTT.COST_GROUP_ID) TO_COST_GROUP_ID
, MMTT.LPN_ID
, MMTT.TRANSFER_LPN_ID
, MMTT.SOURCE_CODE
, MMTT.SOURCE_LINE_ID
, MMTT.TRANSACTION_MODE
, MMTT.LOCK_FLAG
, MMTT.TRANSACTION_COST
, MMTT.TRANSACTION_ACTION_ID
, MMTT.TRANSACTION_SOURCE_ID
, MMTT.TRANSACTION_SOURCE_NAME
, MMTT.TRANSACTION_DATE
, MMTT.ACCT_PERIOD_ID
, MMTT.DISTRIBUTION_ACCOUNT_ID
, MMTT.REQUISITION_LINE_ID
, MMTT.REQUISITION_DISTRIBUTION_ID
, NVL(MMTT. LOT_EXPIRATION_DATE
, MTLT.LOT_EXPIRATION_DATE)
, NVL(MMTT.SERIAL_NUMBER
, MSN.SERIAL_NUMBER)
, MMTT.RECEIVING_DOCUMENT
, MMTT.DEMAND_ID
, MMTT.RCV_TRANSACTION_ID
, MMTT.MOVE_TRANSACTION_ID
, MMTT.COMPLETION_TRANSACTION_ID
, MMTT.WIP_ENTITY_TYPE
, MMTT.SCHEDULE_ID
, MMTT.REPETITIVE_LINE_ID
, MMTT.EMPLOYEE_CODE
, MMTT.PRIMARY_SWITCH
, MMTT.SCHEDULE_UPDATE_CODE
, MMTT.SETUP_TEARDOWN_CODE
, MMTT.ITEM_ORDERING
, MMTT.NEGATIVE_REQ_FLAG
, MMTT.OPERATION_SEQ_NUM
, MMTT.PICKING_LINE_ID
, MMTT.TRX_SOURCE_DELIVERY_ID
, MMTT.PHYSICAL_ADJUSTMENT_ID
, MMTT.CYCLE_COUNT_ID
, MMTT.RMA_LINE_ID
, MMTT.CUSTOMER_SHIP_ID
, MMTT.CURRENCY_CODE
, MMTT.CURRENCY_CONVERSION_RATE
, MMTT.CURRENCY_CONVERSION_TYPE
, MMTT.CURRENCY_CONVERSION_DATE
, MMTT.USSGL_TRANSACTION_CODE
, MMTT.VENDOR_LOT_NUMBER
, MMTT.ENCUMBRANCE_ACCOUNT
, MMTT.ENCUMBRANCE_AMOUNT
, MMTT.SHIPMENT_NUMBER
, MMTT.TRANSFER_COST
, MMTT.TRANSPORTATION_COST
, MMTT.TRANSPORTATION_ACCOUNT
, MMTT.FREIGHT_CODE
, MMTT.CONTAINERS
, MMTT.WAYBILL_AIRBILL
, MMTT.EXPECTED_ARRIVAL_DATE
, MMTT.NEW_AVERAGE_COST
, MMTT.VALUE_CHANGE
, MMTT.PERCENTAGE_CHANGE
, MMTT.MATERIAL_ALLOCATION_TEMP_ID
, MMTT.DEMAND_SOURCE_HEADER_ID
, MMTT.DEMAND_SOURCE_LINE
, MMTT.DEMAND_SOURCE_DELIVERY
, MMTT.ITEM_SEGMENTS
, MMTT.ITEM_DESCRIPTION
, MMTT.ITEM_TRX_ENABLED_FLAG
, MMTT.ITEM_LOCATION_CONTROL_CODE
, MMTT.ITEM_RESTRICT_SUBINV_CODE
, MMTT.ITEM_RESTRICT_LOCATORS_CODE
, MMTT.ITEM_REVISION_QTY_CONTROL_CODE
, MMTT.ITEM_PRIMARY_UOM_CODE
, MMTT.ITEM_UOM_CLASS
, MMTT.ITEM_SHELF_LIFE_CODE
, MMTT.ITEM_SHELF_LIFE_DAYS
, MMTT.ITEM_LOT_CONTROL_CODE
, MMTT.ITEM_SERIAL_CONTROL_CODE
, MMTT.ITEM_INVENTORY_ASSET_FLAG
, MMTT.ALLOWED_UNITS_LOOKUP_CODE
, MMTT.DEPARTMENT_ID
, MMTT.DEPARTMENT_CODE
, MMTT.WIP_SUPPLY_TYPE
, MMTT.SUPPLY_SUBINVENTORY
, MMTT.SUPPLY_LOCATOR_ID
, MMTT.VALID_SUBINVENTORY_FLAG
, MMTT.VALID_LOCATOR_FLAG
, MMTT.LOCATOR_SEGMENTS
, MMTT.CURRENT_LOCATOR_CONTROL_CODE
, MMTT.NUMBER_OF_LOTS_ENTERED
, MMTT.WIP_COMMIT_FLAG
, MMTT.NEXT_LOT_NUMBER
, MMTT.LOT_ALPHA_PREFIX
, MMTT.NEXT_SERIAL_NUMBER
, MMTT.SERIAL_ALPHA_PREFIX
, MMTT.SHIPPABLE_FLAG
, MMTT.POSTING_FLAG
, MMTT.REQUIRED_FLAG
, MMTT.PROCESS_FLAG
, MMTT.ERROR_CODE
, MMTT.ERROR_EXPLANATION
, MMTT.MOVEMENT_ID
, MMTT.RESERVATION_QUANTITY
, MMTT.SHIPPED_QUANTITY
, MMTT.TRANSACTION_LINE_NUMBER
, MMTT.TO_TASK_ID
, MMTT.SOURCE_TASK_ID
, MMTT.SOURCE_PROJECT_ID
, MMTT.PA_EXPENDITURE_ORG_ID
, MMTT.TO_PROJECT_ID
, MMTT.EXPENDITURE_TYPE
, MMTT.FINAL_COMPLETION_FLAG
, MMTT.TRANSFER_PERCENTAGE
, MMTT.TRANSACTION_SEQUENCE_ID
, MMTT.MATERIAL_ACCOUNT
, MMTT.MATERIAL_OVERHEAD_ACCOUNT
, MMTT.RESOURCE_ACCOUNT
, MMTT.OUTSIDE_PROCESSING_ACCOUNT
, MMTT.OVERHEAD_ACCOUNT
, MMTT.FLOW_SCHEDULE
, MMTT.DEMAND_CLASS
, MMTT.QA_COLLECTION_ID
, MMTT.KANBAN_CARD_ID
, MMTT.OVERCOMPLETION_TRANSACTION_ID
, MMTT.OVERCOMPLETION_PRIMARY_QTY
, MMTT.OVERCOMPLETION_TRANSACTION_QTY
, MMTT.SCHEDULED_PAYBACK_DATE
, MMTT.LINE_TYPE_CODE
, MMTT.PARENT_TRANSACTION_TEMP_ID
, MMTT.PUT_AWAY_RULE_ID
, MMTT.PICK_RULE_ID
, MMTT.COMMON_BOM_SEQ_ID
, MMTT.COMMON_ROUTING_SEQ_ID
, MMTT.COST_TYPE_ID
, MMTT.ORG_COST_GROUP_ID
, MMTT.MOVE_ORDER_LINE_ID
, MMTT.TASK_GROUP_ID
, MMTT.PICK_SLIP_NUMBER
, MMTT.RESERVATION_ID
, MMTT.TRANSACTION_STATUS
, MMTT.STANDARD_OPERATION_ID
, MMTT.TASK_PRIORITY
, MMTT.WMS_TASK_TYPE
, MMTT.PARENT_LINE_ID
, TO_NUMBER(NULL) INSPECTION_STATUS
, DECODE(MMTT.TRANSACTION_ACTION_ID
, 12
, MMTT.TRANSFER_ORGANIZATION
, MMTT.ORGANIZATION_ID)
FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, MTL_TRANSACTION_LOTS_TEMP MTLT
, MTL_SERIAL_NUMBERS MSN
, MTL_SERIAL_NUMBERS_TEMP MSNT
, MTL_INTERORG_PARAMETERS MIP
WHERE WMS_COSTGROUPENGINE_PVT.GETCURRENTINPUTTYPE = 1
AND MMTT.TRANSACTION_TEMP_ID = MTLT.TRANSACTION_TEMP_ID (+)
AND MSNT.TRANSACTION_TEMP_ID = NVL (MTLT.SERIAL_TRANSACTION_TEMP_ID
, MMTT.TRANSACTION_TEMP_ID )
AND MSNT.FM_SERIAL_NUMBER <= MSN.SERIAL_NUMBER
AND MSNT.TO_SERIAL_NUMBER >= MSN.SERIAL_NUMBER
AND MMTT.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND NVL(MTLT.LOT_NUMBER
, NVL(MMTT.LOT_NUMBER
, '9999')) = NVL(MSN.LOT_NUMBER
, '9999')
AND NVL(MMTT.REVISION
, '9999') = NVL(MSN.REVISION
, '9999')
AND MIP.FROM_ORGANIZATION_ID (+) = MMTT.ORGANIZATION_ID
AND MIP.TO_ORGANIZATION_ID (+) = MMTT. TRANSFER_ORGANIZATION
AND MMTT.ORGANIZATION_ID = MSN.CURRENT_ORGANIZATION_ID UNION SELECT MMTT.TRANSACTION_TEMP_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(MMTT.TRANSACTION_ACTION_ID
, 3
, MMTT.ORGANIZATION_ID
, 21
, DECODE(MIP.FOB_POINT
, 1
, MMTT.TRANSFER_ORGANIZATION
, MMTT.ORGANIZATION_ID)
, MMTT.ORGANIZATION_ID)
, MMTT.INVENTORY_ITEM_ID
, MMTT.REVISION
, TO_NUMBER(NULL)
, DECODE(TRANSACTION_ACTION_ID
, 1
, SUBINVENTORY_CODE
, 2
, SUBINVENTORY_CODE
, 3
, SUBINVENTORY_CODE
, 21
, SUBINVENTORY_CODE
, 28
, SUBINVENTORY_CODE
, 29
, SUBINVENTORY_CODE
, 32
, SUBINVENTORY_CODE
, 34
, SUBINVENTORY_CODE
, NULL) FROM_SUBINVENTORY_CODE
, DECODE(TRANSACTION_ACTION_ID
, 1
, LOCATOR_ID
, 2
, LOCATOR_ID
, 3
, LOCATOR_ID
, 21
, LOCATOR_ID
, 28
, LOCATOR_ID
, 29
, LOCATOR_ID
, 32
, LOCATOR_ID
, 34
, LOCATOR_ID
, TO_NUMBER(NULL)) FROM_LOCATOR_ID
, DECODE(TRANSACTION_ACTION_ID
, 1
, NULL
, 2
, TRANSFER_SUBINVENTORY
, 3
, TRANSFER_SUBINVENTORY
, 21
, NULL
, 28
, TRANSFER_SUBINVENTORY
, 29
, SUBINVENTORY_CODE
, 32
, NULL
, 34
, NULL
, SUBINVENTORY_CODE) TO_SUBINVENTORY_CODE
, TO_NUMBER(NULL)
, DECODE(TRANSACTION_ACTION_ID
, 1
, TO_NUMBER(NULL)
, 2
, TRANSFER_TO_LOCATION
, 3
, TRANSFER_TO_LOCATION
, 21
, TO_NUMBER(NULL)
, 28
, TRANSFER_TO_LOCATION
, 29
, TO_NUMBER(NULL)
, 32
, TO_NUMBER(NULL)
, 34
, TO_NUMBER(NULL)
, LOCATOR_ID) TO_LOCATOR_ID
, TO_NUMBER(NULL)
, NVL(MMTT.LOT_NUMBER
, MTLT.LOT_NUMBER)
, NULL
, NULL
, MMTT.TRANSACTION_UOM
, MMTT.TRANSACTION_QUANTITY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, MMTT.REASON_ID
, MMTT.TRANSACTION_REFERENCE
, TO_NUMBER(NULL)
, DECODE(SOURCE_CODE
, 'RCV'
, RCV_TRANSACTION_ID
, TO_NUMBER(NULL))
, MMTT.PROJECT_ID
, MMTT.TASK_ID
, MMTT.TRANSACTION_HEADER_ID
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, MMTT.LAST_UPDATED_BY
, MMTT.LAST_UPDATE_LOGIN
, MMTT.LAST_UPDATE_DATE
, MMTT.CREATED_BY
, MMTT.CREATION_DATE
, MMTT.REQUEST_ID
, MMTT.PROGRAM_APPLICATION_ID
, MMTT.PROGRAM_ID
, MMTT.PROGRAM_UPDATE_DATE
, 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.ATTRIBUTE_CATEGORY
, TO_NUMBER(NULL)
, MMTT.TRX_SOURCE_LINE_ID
, TO_NUMBER(NULL)
, MMTT.TRANSACTION_TYPE_ID
, MMTT.TRANSACTION_SOURCE_TYPE_ID
, MMTT.PRIMARY_QUANTITY
, DECODE(TRANSACTION_ACTION_ID
, 3
, MMTT.TRANSFER_ORGANIZATION
, 21
, MMTT.TRANSFER_ORGANIZATION
, MMTT.ORGANIZATION_ID)
, MMTT.PUT_AWAY_STRATEGY_ID
, MMTT.PICK_STRATEGY_ID
, MMTT.END_ITEM_UNIT_NUMBER
, MMTT.SHIP_TO_LOCATION
, DECODE(MMTT.TRANSACTION_ACTION_ID
, 1
, MMTT.COST_GROUP_ID
, 2
, MMTT.COST_GROUP_ID
, 3
, MMTT.COST_GROUP_ID
, 21
, MMTT.COST_GROUP_ID
, 28
, MMTT.COST_GROUP_ID
, 29
, MMTT.COST_GROUP_ID
, 32
, MMTT.COST_GROUP_ID
, 34
, MMTT.COST_GROUP_ID
, TO_NUMBER(NULL)) FROM_COST_GROUP_ID
, DECODE(MMTT.TRANSACTION_ACTION_ID
, 1
, TO_NUMBER(NULL)
, 2
, MMTT.TRANSFER_COST_GROUP_ID
, 3
, MMTT.TRANSFER_COST_GROUP_ID
, 21
, TO_NUMBER(NULL)
, 28
, MMTT.TRANSFER_COST_GROUP_ID
, 29
, TO_NUMBER(NULL)
, 32
, TO_NUMBER(NULL)
, 34
, TO_NUMBER(NULL)
, MMTT.COST_GROUP_ID) TO_COST_GROUP_ID
, MMTT.LPN_ID
, MMTT.TRANSFER_LPN_ID
, MMTT.SOURCE_CODE
, MMTT.SOURCE_LINE_ID
, MMTT.TRANSACTION_MODE
, MMTT.LOCK_FLAG
, MMTT.TRANSACTION_COST
, MMTT.TRANSACTION_ACTION_ID
, MMTT.TRANSACTION_SOURCE_ID
, MMTT.TRANSACTION_SOURCE_NAME
, MMTT.TRANSACTION_DATE
, MMTT.ACCT_PERIOD_ID
, MMTT.DISTRIBUTION_ACCOUNT_ID
, MMTT.REQUISITION_LINE_ID
, MMTT.REQUISITION_DISTRIBUTION_ID
, NVL(MMTT. LOT_EXPIRATION_DATE
, MTLT.LOT_EXPIRATION_DATE)
, MMTT.SERIAL_NUMBER
, MMTT.RECEIVING_DOCUMENT
, MMTT.DEMAND_ID
, MMTT.RCV_TRANSACTION_ID
, MMTT.MOVE_TRANSACTION_ID
, MMTT.COMPLETION_TRANSACTION_ID
, MMTT.WIP_ENTITY_TYPE
, MMTT.SCHEDULE_ID
, MMTT.REPETITIVE_LINE_ID
, MMTT.EMPLOYEE_CODE
, MMTT.PRIMARY_SWITCH
, MMTT.SCHEDULE_UPDATE_CODE
, MMTT.SETUP_TEARDOWN_CODE
, MMTT.ITEM_ORDERING
, MMTT.NEGATIVE_REQ_FLAG
, MMTT.OPERATION_SEQ_NUM
, MMTT.PICKING_LINE_ID
, MMTT.TRX_SOURCE_DELIVERY_ID
, MMTT.PHYSICAL_ADJUSTMENT_ID
, MMTT.CYCLE_COUNT_ID
, MMTT.RMA_LINE_ID
, MMTT.CUSTOMER_SHIP_ID
, MMTT.CURRENCY_CODE
, MMTT.CURRENCY_CONVERSION_RATE
, MMTT.CURRENCY_CONVERSION_TYPE
, MMTT.CURRENCY_CONVERSION_DATE
, MMTT.USSGL_TRANSACTION_CODE
, MMTT.VENDOR_LOT_NUMBER
, MMTT.ENCUMBRANCE_ACCOUNT
, MMTT.ENCUMBRANCE_AMOUNT
, MMTT.SHIPMENT_NUMBER
, MMTT.TRANSFER_COST
, MMTT.TRANSPORTATION_COST
, MMTT.TRANSPORTATION_ACCOUNT
, MMTT.FREIGHT_CODE
, MMTT.CONTAINERS
, MMTT.WAYBILL_AIRBILL
, MMTT.EXPECTED_ARRIVAL_DATE
, MMTT.NEW_AVERAGE_COST
, MMTT.VALUE_CHANGE
, MMTT.PERCENTAGE_CHANGE
, MMTT.MATERIAL_ALLOCATION_TEMP_ID
, MMTT.DEMAND_SOURCE_HEADER_ID
, MMTT.DEMAND_SOURCE_LINE
, MMTT.DEMAND_SOURCE_DELIVERY
, MMTT.ITEM_SEGMENTS
, MMTT.ITEM_DESCRIPTION
, MMTT.ITEM_TRX_ENABLED_FLAG
, MMTT.ITEM_LOCATION_CONTROL_CODE
, MMTT.ITEM_RESTRICT_SUBINV_CODE
, MMTT.ITEM_RESTRICT_LOCATORS_CODE
, MMTT.ITEM_REVISION_QTY_CONTROL_CODE
, MMTT.ITEM_PRIMARY_UOM_CODE
, MMTT.ITEM_UOM_CLASS
, MMTT.ITEM_SHELF_LIFE_CODE
, MMTT.ITEM_SHELF_LIFE_DAYS
, MMTT.ITEM_LOT_CONTROL_CODE
, MMTT.ITEM_SERIAL_CONTROL_CODE
, MMTT.ITEM_INVENTORY_ASSET_FLAG
, MMTT.ALLOWED_UNITS_LOOKUP_CODE
, MMTT.DEPARTMENT_ID
, MMTT.DEPARTMENT_CODE
, MMTT.WIP_SUPPLY_TYPE
, MMTT.SUPPLY_SUBINVENTORY
, MMTT.SUPPLY_LOCATOR_ID
, MMTT.VALID_SUBINVENTORY_FLAG
, MMTT.VALID_LOCATOR_FLAG
, MMTT.LOCATOR_SEGMENTS
, MMTT.CURRENT_LOCATOR_CONTROL_CODE
, MMTT.NUMBER_OF_LOTS_ENTERED
, MMTT.WIP_COMMIT_FLAG
, MMTT.NEXT_LOT_NUMBER
, MMTT.LOT_ALPHA_PREFIX
, MMTT.NEXT_SERIAL_NUMBER
, MMTT.SERIAL_ALPHA_PREFIX
, MMTT.SHIPPABLE_FLAG
, MMTT.POSTING_FLAG
, MMTT.REQUIRED_FLAG
, MMTT.PROCESS_FLAG
, MMTT.ERROR_CODE
, MMTT.ERROR_EXPLANATION
, MMTT.MOVEMENT_ID
, MMTT.RESERVATION_QUANTITY
, MMTT.SHIPPED_QUANTITY
, MMTT.TRANSACTION_LINE_NUMBER
, MMTT.TO_TASK_ID
, MMTT.SOURCE_TASK_ID
, MMTT.SOURCE_PROJECT_ID
, MMTT.PA_EXPENDITURE_ORG_ID
, MMTT.TO_PROJECT_ID
, MMTT.EXPENDITURE_TYPE
, MMTT.FINAL_COMPLETION_FLAG
, MMTT.TRANSFER_PERCENTAGE
, MMTT.TRANSACTION_SEQUENCE_ID
, MMTT.MATERIAL_ACCOUNT
, MMTT.MATERIAL_OVERHEAD_ACCOUNT
, MMTT.RESOURCE_ACCOUNT
, MMTT.OUTSIDE_PROCESSING_ACCOUNT
, MMTT.OVERHEAD_ACCOUNT
, MMTT.FLOW_SCHEDULE
, MMTT.DEMAND_CLASS
, MMTT.QA_COLLECTION_ID
, MMTT.KANBAN_CARD_ID
, MMTT.OVERCOMPLETION_TRANSACTION_ID
, MMTT.OVERCOMPLETION_PRIMARY_QTY
, MMTT.OVERCOMPLETION_TRANSACTION_QTY
, MMTT.SCHEDULED_PAYBACK_DATE
, MMTT.LINE_TYPE_CODE
, MMTT.PARENT_TRANSACTION_TEMP_ID
, MMTT.PUT_AWAY_RULE_ID
, MMTT.PICK_RULE_ID
, MMTT.COMMON_BOM_SEQ_ID
, MMTT.COMMON_ROUTING_SEQ_ID
, MMTT.COST_TYPE_ID
, MMTT.ORG_COST_GROUP_ID
, MMTT.MOVE_ORDER_LINE_ID
, MMTT.TASK_GROUP_ID
, MMTT.PICK_SLIP_NUMBER
, MMTT.RESERVATION_ID
, MMTT.TRANSACTION_STATUS
, MMTT.STANDARD_OPERATION_ID
, MMTT.TASK_PRIORITY
, MMTT.WMS_TASK_TYPE
, MMTT.PARENT_LINE_ID
, TO_NUMBER(NULL) INSPECTION_STATUS
, DECODE(MMTT.TRANSACTION_ACTION_ID
, 12
, MMTT.TRANSFER_ORGANIZATION
, MMTT.ORGANIZATION_ID)
FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, MTL_TRANSACTION_LOTS_TEMP MTLT
, MTL_INTERORG_PARAMETERS MIP
WHERE WMS_COSTGROUPENGINE_PVT.GETCURRENTINPUTTYPE = 1
AND MMTT.TRANSACTION_TEMP_ID = MTLT.TRANSACTION_TEMP_ID (+)
AND MIP.FROM_ORGANIZATION_ID (+) = MMTT.ORGANIZATION_ID
AND MIP.TO_ORGANIZATION_ID (+) = MMTT.TRANSFER_ORGANIZATION