[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rowid INTO l_row_id
FROM wms_pr_workers
WHERE batch_id = p_batch_id
AND worker_mode = p_mode
AND processed_flag = 'N'
AND rownum < 2
FOR UPDATE NOWAIT;
UPDATE wms_pr_workers
SET processed_flag = 'Y'
WHERE rowid = l_row_id
RETURNING batch_id
, worker_mode
, processed_flag
, organization_id
, mo_header_id
, transaction_batch_id
, cartonization_id
, detailed_count
INTO l_wpr_rec;
SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_batch_id = p_txn_batch_id;
SELECT rules.rule_id, rules.type_hdr_id BULK COLLECT
INTO g_t_opa_rule_id, g_t_opa_type_hdr_id
FROM wms_rules rules
, wms_op_plans_b wop
WHERE rules.organization_id IN (g_opa_org_id,-1)
AND rules.type_code = 7
AND rules.enabled_flag = 'Y'
AND rules.type_hdr_id = wop.operation_plan_id
AND wop.activity_type_id = 2 -- Outbound
AND wop.enabled_flag = 'Y'
ORDER BY rules.rule_weight DESC, rules.creation_date;
UPDATE mtl_material_transactions_temp
SET operation_plan_id = l_mmtt_hid(kk)
WHERE transaction_temp_id = l_mmtt_temp_id(kk);
UPDATE mtl_material_transactions_temp
SET operation_plan_id = l_op_plan_id
WHERE transaction_batch_id = p_transaction_batch_id;
UPDATE wms_pr_workers
SET worker_mode = 'TTA', processed_flag = 'N'
WHERE transaction_batch_id = p_transaction_batch_id;
INSERT INTO wms_cartonization_temp(
transaction_header_id
, transaction_temp_id
, source_code
, source_line_id
, transaction_mode
, lock_flag
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, inventory_item_id
, revision
, organization_id
, subinventory_code
, locator_id
, transaction_quantity
, primary_quantity
, transaction_uom
, transaction_cost
, transaction_type_id
, transaction_action_id
, transaction_source_type_id
, transaction_source_id
, transaction_source_name
, transaction_date
, acct_period_id
, distribution_account_id
, transaction_reference
, requisition_line_id
, requisition_distribution_id
, reason_id
, lot_number
, lot_expiration_date
, serial_number
, 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
, 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
, currency_code
, currency_conversion_rate
, currency_conversion_type
, currency_conversion_date
, ussgl_transaction_code
, vendor_lot_number
, encumbrance_account
, encumbrance_amount
, ship_to_location
, shipment_number
, transfer_cost
, transportation_cost
, transportation_account
, freight_code
, containers
, waybill_airbill
, expected_arrival_date
, transfer_subinventory
, 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
, 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
, wip_supply_type
, supply_subinventory
, 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
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, movement_id
, reservation_quantity
, shipped_quantity
, transaction_line_number
, task_id
, to_task_id
, source_task_id
, project_id
, source_project_id
, pa_expenditure_org_id
, to_project_id
, expenditure_type
, final_completion_flag
, transfer_percentage
, transaction_sequence_id
, material_account
, material_overhead_account
, resource_account
, outside_processing_account
, overhead_account
, flow_schedule
, cost_group_id
, demand_class
, qa_collection_id
, kanban_card_id
, overcompletion_txn_qty
, overcompletion_primary_qty
, overcompletion_transaction_id
, end_item_unit_number
, scheduled_payback_date
, line_type_code
, parent_transaction_temp_id
, put_away_strategy_id
, put_away_rule_id
, pick_strategy_id
, pick_rule_id
, move_order_line_id
, task_group_id
, pick_slip_number
, reservation_id
, common_bom_seq_id
, common_routing_seq_id
, org_cost_group_id
, cost_type_id
, transaction_status
, standard_operation_id
, task_priority
, wms_task_type
, parent_line_id
, source_lot_number
, transfer_cost_group_id
, lpn_id
, transfer_lpn_id
, wms_task_status
, content_lpn_id
, container_item_id
, cartonization_id
, pick_slip_date
, rebuild_item_id
, rebuild_serial_number
, rebuild_activity_id
, rebuild_job_name
, organization_type
, transfer_organization_type
, owning_organization_id
, owning_tp_type
, xfr_owning_organization_id
, transfer_owning_tp_type
, planning_organization_id
, planning_tp_type
, xfr_planning_organization_id
, transfer_planning_tp_type
, secondary_uom_code
, secondary_transaction_quantity
, allocated_lpn_id
, schedule_number
, scheduled_flag
, class_code
, schedule_group
, build_sequence
, bom_revision
, routing_revision
, bom_revision_date
, routing_revision_date
, alternate_bom_designator
, alternate_routing_designator
, transaction_batch_id
, transaction_batch_seq
, operation_plan_id
, intransit_account
, fob_point
, logical_trx_type_code
, move_order_header_id
, original_transaction_temp_id
, serial_allocated_flag
, trx_flow_header_id
, fulfillment_base
)
SELECT mmtt.transaction_header_id
, mmtt.transaction_temp_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.program_application_id
, mmtt.program_id
, mmtt.program_update_date
, mmtt.inventory_item_id
, mmtt.revision
, mmtt.organization_id
, mmtt.subinventory_code
, mmtt.locator_id
, mmtt.transaction_quantity
, mmtt.primary_quantity
, mmtt.transaction_uom
, mmtt.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
, mmtt.acct_period_id
, mmtt.distribution_account_id
, mmtt.transaction_reference
, mmtt.requisition_line_id
, mmtt.requisition_distribution_id
, mmtt.reason_id
, mmtt.lot_number
, mmtt.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_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.ship_to_location
, 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.transfer_subinventory
, mmtt.transfer_organization
, mmtt.transfer_to_location
, 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.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.movement_id
, mmtt.reservation_quantity
, mmtt.shipped_quantity
, mmtt.transaction_line_number
, mmtt.task_id
, mmtt.to_task_id
, mmtt.source_task_id
, mmtt.project_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.cost_group_id
, mmtt.demand_class
, mmtt.qa_collection_id
, mmtt.kanban_card_id
, mmtt.overcompletion_transaction_qty
, mmtt.overcompletion_primary_qty
, mmtt.overcompletion_transaction_id
, mmtt.end_item_unit_number
, mmtt.scheduled_payback_date
, mmtt.line_type_code
, mmtt.parent_transaction_temp_id
, mmtt.put_away_strategy_id
, mmtt.put_away_rule_id
, mmtt.pick_strategy_id
, mmtt.pick_rule_id
, mmtt.move_order_line_id
, mmtt.task_group_id
, mmtt.pick_slip_number
, mmtt.reservation_id
, mmtt.common_bom_seq_id
, mmtt.common_routing_seq_id
, mmtt.org_cost_group_id
, mmtt.cost_type_id
, mmtt.transaction_status
, mmtt.standard_operation_id
, mmtt.task_priority
, mmtt.wms_task_type
, mmtt.parent_line_id
, mmtt.source_lot_number
, mmtt.transfer_cost_group_id
, mmtt.lpn_id
, mmtt.transfer_lpn_id
, mmtt.wms_task_status
, mmtt.content_lpn_id
, decode( g_pick_group_rule,'Y',decode(mmtt.container_item_id,-1,null,mmtt.container_item_id),mmtt.container_item_id)
, mmtt.cartonization_id
, mmtt.pick_slip_date
, mmtt.rebuild_item_id
, mmtt.rebuild_serial_number
, mmtt.rebuild_activity_id
, mmtt.rebuild_job_name
, mmtt.organization_type
, mmtt.transfer_organization_type
, mmtt.owning_organization_id
, mmtt.owning_tp_type
, mmtt.xfr_owning_organization_id
, mmtt.transfer_owning_tp_type
, mmtt.planning_organization_id
, mmtt.planning_tp_type
, mmtt.xfr_planning_organization_id
, mmtt.transfer_planning_tp_type
, mmtt.secondary_uom_code
, mmtt.secondary_transaction_quantity
, mmtt.allocated_lpn_id
, mmtt.schedule_number
, mmtt.scheduled_flag
, mmtt.class_code
, mmtt.schedule_group
, mmtt.build_sequence
, mmtt.bom_revision
, mmtt.routing_revision
, mmtt.bom_revision_date
, mmtt.routing_revision_date
, mmtt.alternate_bom_designator
, mmtt.alternate_routing_designator
, mmtt.transaction_batch_id
, mmtt.transaction_batch_seq
, mmtt.operation_plan_id
, mmtt.intransit_account
, mmtt.fob_point
, mmtt.logical_trx_type_code
, mmtt.move_order_header_id
, mmtt.original_transaction_temp_id
, mmtt.serial_allocated_flag
, mmtt.trx_flow_header_id
, mmtt.fulfillment_base
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_header_id = p_move_order_header_id;
INSERT INTO wms_cartonization_temp(
transaction_header_id
, transaction_temp_id
, source_code
, source_line_id
, transaction_mode
, lock_flag
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, inventory_item_id
, revision
, organization_id
, subinventory_code
, locator_id
, transaction_quantity
, primary_quantity
, transaction_uom
, transaction_cost
, transaction_type_id
, transaction_action_id
, transaction_source_type_id
, transaction_source_id
, transaction_source_name
, transaction_date
, acct_period_id
, distribution_account_id
, transaction_reference
, requisition_line_id
, requisition_distribution_id
, reason_id
, lot_number
, lot_expiration_date
, serial_number
, 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
, 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
, currency_code
, currency_conversion_rate
, currency_conversion_type
, currency_conversion_date
, ussgl_transaction_code
, vendor_lot_number
, encumbrance_account
, encumbrance_amount
, ship_to_location
, shipment_number
, transfer_cost
, transportation_cost
, transportation_account
, freight_code
, containers
, waybill_airbill
, expected_arrival_date
, transfer_subinventory
, 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
, 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
, wip_supply_type
, supply_subinventory
, 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
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, movement_id
, reservation_quantity
, shipped_quantity
, transaction_line_number
, task_id
, to_task_id
, source_task_id
, project_id
, source_project_id
, pa_expenditure_org_id
, to_project_id
, expenditure_type
, final_completion_flag
, transfer_percentage
, transaction_sequence_id
, material_account
, material_overhead_account
, resource_account
, outside_processing_account
, overhead_account
, flow_schedule
, cost_group_id
, demand_class
, qa_collection_id
, kanban_card_id
, overcompletion_txn_qty
, overcompletion_primary_qty
, overcompletion_transaction_id
, end_item_unit_number
, scheduled_payback_date
, line_type_code
, parent_transaction_temp_id
, put_away_strategy_id
, put_away_rule_id
, pick_strategy_id
, pick_rule_id
, move_order_line_id
, task_group_id
, pick_slip_number
, reservation_id
, common_bom_seq_id
, common_routing_seq_id
, org_cost_group_id
, cost_type_id
, transaction_status
, standard_operation_id
, task_priority
, wms_task_type
, parent_line_id
, source_lot_number
, transfer_cost_group_id
, lpn_id
, transfer_lpn_id
, wms_task_status
, content_lpn_id
, container_item_id
, cartonization_id
, pick_slip_date
, rebuild_item_id
, rebuild_serial_number
, rebuild_activity_id
, rebuild_job_name
, organization_type
, transfer_organization_type
, owning_organization_id
, owning_tp_type
, xfr_owning_organization_id
, transfer_owning_tp_type
, planning_organization_id
, planning_tp_type
, xfr_planning_organization_id
, transfer_planning_tp_type
, secondary_uom_code
, secondary_transaction_quantity
, allocated_lpn_id
, schedule_number
, scheduled_flag
, class_code
, schedule_group
, build_sequence
, bom_revision
, routing_revision
, bom_revision_date
, routing_revision_date
, alternate_bom_designator
, alternate_routing_designator
, transaction_batch_id
, transaction_batch_seq
, operation_plan_id
, intransit_account
, fob_point
, logical_trx_type_code
, move_order_header_id
, original_transaction_temp_id
, serial_allocated_flag
, trx_flow_header_id
, fulfillment_base
)
SELECT mmtt.transaction_header_id
, mmtt.transaction_temp_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.program_application_id
, mmtt.program_id
, mmtt.program_update_date
, mmtt.inventory_item_id
, mmtt.revision
, mmtt.organization_id
, mmtt.subinventory_code
, mmtt.locator_id
, mmtt.transaction_quantity
, mmtt.primary_quantity
, mmtt.transaction_uom
, mmtt.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
, mmtt.acct_period_id
, mmtt.distribution_account_id
, mmtt.transaction_reference
, mmtt.requisition_line_id
, mmtt.requisition_distribution_id
, mmtt.reason_id
, mmtt.lot_number
, mmtt.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_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.ship_to_location
, 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.transfer_subinventory
, mmtt.transfer_organization
, mmtt.transfer_to_location
, 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.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.movement_id
, mmtt.reservation_quantity
, mmtt.shipped_quantity
, mmtt.transaction_line_number
, mmtt.task_id
, mmtt.to_task_id
, mmtt.source_task_id
, mmtt.project_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.cost_group_id
, mmtt.demand_class
, mmtt.qa_collection_id
, mmtt.kanban_card_id
, mmtt.overcompletion_transaction_qty
, mmtt.overcompletion_primary_qty
, mmtt.overcompletion_transaction_id
, mmtt.end_item_unit_number
, mmtt.scheduled_payback_date
, mmtt.line_type_code
, mmtt.parent_transaction_temp_id
, mmtt.put_away_strategy_id
, mmtt.put_away_rule_id
, mmtt.pick_strategy_id
, mmtt.pick_rule_id
, mmtt.move_order_line_id
, mmtt.task_group_id
, mmtt.pick_slip_number
, mmtt.reservation_id
, mmtt.common_bom_seq_id
, mmtt.common_routing_seq_id
, mmtt.org_cost_group_id
, mmtt.cost_type_id
, mmtt.transaction_status
, mmtt.standard_operation_id
, mmtt.task_priority
, mmtt.wms_task_type
, mmtt.parent_line_id
, mmtt.source_lot_number
, mmtt.transfer_cost_group_id
, mmtt.lpn_id
, mmtt.transfer_lpn_id
, mmtt.wms_task_status
, mmtt.content_lpn_id
, mmtt.container_item_id
, mmtt.cartonization_id
, mmtt.pick_slip_date
, mmtt.rebuild_item_id
, mmtt.rebuild_serial_number
, mmtt.rebuild_activity_id
, mmtt.rebuild_job_name
, mmtt.organization_type
, mmtt.transfer_organization_type
, mmtt.owning_organization_id
, mmtt.owning_tp_type
, mmtt.xfr_owning_organization_id
, mmtt.transfer_owning_tp_type
, mmtt.planning_organization_id
, mmtt.planning_tp_type
, mmtt.xfr_planning_organization_id
, mmtt.transfer_planning_tp_type
, mmtt.secondary_uom_code
, mmtt.secondary_transaction_quantity
, mmtt.allocated_lpn_id
, mmtt.schedule_number
, mmtt.scheduled_flag
, mmtt.class_code
, mmtt.schedule_group
, mmtt.build_sequence
, mmtt.bom_revision
, mmtt.routing_revision
, mmtt.bom_revision_date
, mmtt.routing_revision_date
, mmtt.alternate_bom_designator
, mmtt.alternate_routing_designator
, mmtt.transaction_batch_id
, mmtt.transaction_batch_seq
, mmtt.operation_plan_id
, mmtt.intransit_account
, mmtt.fob_point
, mmtt.logical_trx_type_code
, mmtt.move_order_header_id
, mmtt.original_transaction_temp_id
, mmtt.serial_allocated_flag
, mmtt.trx_flow_header_id
, mmtt.fulfillment_base
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_header_id = p_move_order_header_id
AND mmtt.container_item_id IS NULL;
print_debug ('Number of rows inserted into WCT: ' || SQL%ROWCOUNT, l_api_name);
USING ( SELECT * FROM wms_cartonization_temp ) wct
ON ( mmtt.transaction_temp_id = wct.transaction_temp_id )
WHEN MATCHED THEN
UPDATE SET
mmtt.primary_quantity = wct.primary_quantity
, mmtt.transaction_quantity = wct.transaction_quantity
, mmtt.secondary_transaction_quantity = wct.secondary_transaction_quantity
, mmtt.cartonization_id = wct.cartonization_id
, mmtt.container_item_id = NVL(wct.container_item_id,-1)
, mmtt.transaction_batch_id = wct.transaction_batch_id
, mmtt.last_update_date = SYSDATE
, mmtt.last_updated_by = g_user_id
WHEN NOT MATCHED THEN
INSERT ( mmtt.transaction_header_id
, mmtt.transaction_temp_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.program_application_id
, mmtt.program_id
, mmtt.program_update_date
, mmtt.inventory_item_id
, mmtt.revision
, mmtt.organization_id
, mmtt.subinventory_code
, mmtt.locator_id
, mmtt.transaction_quantity
, mmtt.primary_quantity
, mmtt.transaction_uom
, mmtt.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
, mmtt.acct_period_id
, mmtt.distribution_account_id
, mmtt.transaction_reference
, mmtt.requisition_line_id
, mmtt.requisition_distribution_id
, mmtt.reason_id
, mmtt.lot_number
, mmtt.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_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.ship_to_location
, 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.transfer_subinventory
, mmtt.transfer_organization
, mmtt.transfer_to_location
, 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.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.movement_id
, mmtt.reservation_quantity
, mmtt.shipped_quantity
, mmtt.transaction_line_number
, mmtt.task_id
, mmtt.to_task_id
, mmtt.source_task_id
, mmtt.project_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.cost_group_id
, mmtt.transfer_cost_group_id
, mmtt.demand_class
, mmtt.qa_collection_id
, mmtt.kanban_card_id
, mmtt.overcompletion_transaction_qty
, mmtt.overcompletion_primary_qty
, mmtt.overcompletion_transaction_id
, mmtt.end_item_unit_number
, mmtt.scheduled_payback_date
, mmtt.line_type_code
, mmtt.parent_transaction_temp_id
, mmtt.put_away_strategy_id
, mmtt.put_away_rule_id
, mmtt.pick_strategy_id
, mmtt.pick_rule_id
, mmtt.move_order_line_id
, mmtt.task_group_id
, mmtt.pick_slip_number
, mmtt.reservation_id
, mmtt.common_bom_seq_id
, mmtt.common_routing_seq_id
, mmtt.org_cost_group_id
, mmtt.cost_type_id
, mmtt.transaction_status
, mmtt.standard_operation_id
, mmtt.task_priority
, mmtt.wms_task_type
, mmtt.parent_line_id
, mmtt.source_lot_number
, mmtt.lpn_id
, mmtt.transfer_lpn_id
, mmtt.wms_task_status
, mmtt.content_lpn_id
, mmtt.container_item_id
, mmtt.cartonization_id
, mmtt.pick_slip_date
, mmtt.rebuild_item_id
, mmtt.rebuild_serial_number
, mmtt.rebuild_activity_id
, mmtt.rebuild_job_name
, mmtt.organization_type
, mmtt.transfer_organization_type
, mmtt.owning_organization_id
, mmtt.owning_tp_type
, mmtt.xfr_owning_organization_id
, mmtt.transfer_owning_tp_type
, mmtt.planning_organization_id
, mmtt.planning_tp_type
, mmtt.xfr_planning_organization_id
, mmtt.transfer_planning_tp_type
, mmtt.secondary_uom_code
, mmtt.secondary_transaction_quantity
, mmtt.transaction_batch_id
, mmtt.transaction_batch_seq
, mmtt.allocated_lpn_id
, mmtt.schedule_number
, mmtt.scheduled_flag
, mmtt.class_code
, mmtt.schedule_group
, mmtt.build_sequence
, mmtt.bom_revision
, mmtt.routing_revision
, mmtt.bom_revision_date
, mmtt.routing_revision_date
, mmtt.alternate_bom_designator
, mmtt.alternate_routing_designator
, mmtt.operation_plan_id
, mmtt.intransit_account
, mmtt.fob_point
, mmtt.logical_trx_type_code
, mmtt.original_transaction_temp_id
, mmtt.trx_flow_header_id
, mmtt.serial_allocated_flag
, mmtt.move_order_header_id
, mmtt.fulfillment_base
)
VALUES ( wct.transaction_header_id
, wct.transaction_temp_id
, wct.source_code
, wct.source_line_id
, wct.transaction_mode
, wct.lock_flag
, SYSDATE
, g_user_id
, SYSDATE
, g_user_id
, wct.last_update_login
, wct.request_id
, wct.program_application_id
, wct.program_id
, wct.program_update_date
, wct.inventory_item_id
, wct.revision
, wct.organization_id
, wct.subinventory_code
, wct.locator_id
, wct.transaction_quantity
, wct.primary_quantity
, wct.transaction_uom
, wct.transaction_cost
, wct.transaction_type_id
, wct.transaction_action_id
, wct.transaction_source_type_id
, wct.transaction_source_id
, wct.transaction_source_name
, wct.transaction_date
, wct.acct_period_id
, wct.distribution_account_id
, wct.transaction_reference
, wct.requisition_line_id
, wct.requisition_distribution_id
, wct.reason_id
, wct.lot_number
, wct.lot_expiration_date
, wct.serial_number
, wct.receiving_document
, wct.demand_id
, wct.rcv_transaction_id
, wct.move_transaction_id
, wct.completion_transaction_id
, wct.wip_entity_type
, wct.schedule_id
, wct.repetitive_line_id
, wct.employee_code
, wct.primary_switch
, wct.schedule_update_code
, wct.setup_teardown_code
, wct.item_ordering
, wct.negative_req_flag
, wct.operation_seq_num
, wct.picking_line_id
, wct.trx_source_line_id
, wct.trx_source_delivery_id
, wct.physical_adjustment_id
, wct.cycle_count_id
, wct.rma_line_id
, wct.customer_ship_id
, wct.currency_code
, wct.currency_conversion_rate
, wct.currency_conversion_type
, wct.currency_conversion_date
, wct.ussgl_transaction_code
, wct.vendor_lot_number
, wct.encumbrance_account
, wct.encumbrance_amount
, wct.ship_to_location
, wct.shipment_number
, wct.transfer_cost
, wct.transportation_cost
, wct.transportation_account
, wct.freight_code
, wct.containers
, wct.waybill_airbill
, wct.expected_arrival_date
, wct.transfer_subinventory
, wct.transfer_organization
, wct.transfer_to_location
, wct.new_average_cost
, wct.value_change
, wct.percentage_change
, wct.material_allocation_temp_id
, wct.demand_source_header_id
, wct.demand_source_line
, wct.demand_source_delivery
, wct.item_segments
, wct.item_description
, wct.item_trx_enabled_flag
, wct.item_location_control_code
, wct.item_restrict_subinv_code
, wct.item_restrict_locators_code
, wct.item_revision_qty_control_code
, wct.item_primary_uom_code
, wct.item_uom_class
, wct.item_shelf_life_code
, wct.item_shelf_life_days
, wct.item_lot_control_code
, wct.item_serial_control_code
, wct.item_inventory_asset_flag
, wct.allowed_units_lookup_code
, wct.department_id
, wct.department_code
, wct.wip_supply_type
, wct.supply_subinventory
, wct.supply_locator_id
, wct.valid_subinventory_flag
, wct.valid_locator_flag
, wct.locator_segments
, wct.current_locator_control_code
, wct.number_of_lots_entered
, wct.wip_commit_flag
, wct.next_lot_number
, wct.lot_alpha_prefix
, wct.next_serial_number
, wct.serial_alpha_prefix
, wct.shippable_flag
, wct.posting_flag
, wct.required_flag
, wct.process_flag
, wct.error_code
, wct.error_explanation
, wct.attribute_category
, wct.attribute1
, wct.attribute2
, wct.attribute3
, wct.attribute4
, wct.attribute5
, wct.attribute6
, wct.attribute7
, wct.attribute8
, wct.attribute9
, wct.attribute10
, wct.attribute11
, wct.attribute12
, wct.attribute13
, wct.attribute14
, wct.attribute15
, wct.movement_id
, wct.reservation_quantity
, wct.shipped_quantity
, wct.transaction_line_number
, wct.task_id
, wct.to_task_id
, wct.source_task_id
, wct.project_id
, wct.source_project_id
, wct.pa_expenditure_org_id
, wct.to_project_id
, wct.expenditure_type
, wct.final_completion_flag
, wct.transfer_percentage
, wct.transaction_sequence_id
, wct.material_account
, wct.material_overhead_account
, wct.resource_account
, wct.outside_processing_account
, wct.overhead_account
, wct.flow_schedule
, wct.cost_group_id
, wct.transfer_cost_group_id
, wct.demand_class
, wct.qa_collection_id
, wct.kanban_card_id
, wct.overcompletion_txn_qty
, wct.overcompletion_primary_qty
, wct.overcompletion_transaction_id
, wct.end_item_unit_number
, wct.scheduled_payback_date
, wct.line_type_code
, wct.parent_transaction_temp_id
, wct.put_away_strategy_id
, wct.put_away_rule_id
, wct.pick_strategy_id
, wct.pick_rule_id
, wct.move_order_line_id
, wct.task_group_id
, wct.pick_slip_number
, wct.reservation_id
, wct.common_bom_seq_id
, wct.common_routing_seq_id
, wct.org_cost_group_id
, wct.cost_type_id
, wct.transaction_status
, wct.standard_operation_id
, wct.task_priority
, wct.wms_task_type
, wct.parent_line_id
, wct.source_lot_number
, wct.lpn_id
, wct.transfer_lpn_id
, wct.wms_task_status
, wct.content_lpn_id
, NVL(wct.container_item_id,-1)
, wct.cartonization_id
, wct.pick_slip_date
, wct.rebuild_item_id
, wct.rebuild_serial_number
, wct.rebuild_activity_id
, wct.rebuild_job_name
, wct.organization_type
, wct.transfer_organization_type
, wct.owning_organization_id
, wct.owning_tp_type
, wct.xfr_owning_organization_id
, wct.transfer_owning_tp_type
, wct.planning_organization_id
, wct.planning_tp_type
, wct.xfr_planning_organization_id
, wct.transfer_planning_tp_type
, wct.secondary_uom_code
, wct.secondary_transaction_quantity
, wct.transaction_batch_id
, wct.transaction_batch_seq
, wct.allocated_lpn_id
, wct.schedule_number
, wct.scheduled_flag
, wct.class_code
, wct.schedule_group
, wct.build_sequence
, wct.bom_revision
, wct.routing_revision
, wct.bom_revision_date
, wct.routing_revision_date
, wct.alternate_bom_designator
, wct.alternate_routing_designator
, wct.operation_plan_id
, wct.intransit_account
, wct.fob_point
, wct.logical_trx_type_code
, wct.original_transaction_temp_id
, wct.trx_flow_header_id
, wct.serial_allocated_flag
, wct.move_order_header_id
, wct.fulfillment_base
);
TYPE rules_table_type IS TABLE OF wms_selection_criteria_txn%ROWTYPE;
SELECT * BULK COLLECT
INTO rules_table1
FROM wms_selection_criteria_txn
WHERE rule_type_code = 12
AND from_organization_id = p_org_id
AND enabled_flag = 1
ORDER BY sequence_number;
SELECT * BULK COLLECT
INTO rules_table
FROM wms_selection_criteria_txn
WHERE rule_type_code = 12
AND from_organization_id = p_org_id
AND enabled_flag = 1
ORDER BY sequence_number;
SELECT count(1) INTO l_count
FROM wms_cartonization_temp
WHERE cartonization_id IS NULL
AND transaction_header_id >= 0;
print_debug( 'Done with FIRST LEVEL OF CARTONIZATION, inserting packaging history'
, l_api_name);
WMS_CARTNZN_PUB.insert_ph
( p_move_order_header_id => p_move_order_header_id
, p_current_header_id => p_move_order_header_id
, x_return_status => l_api_return_status
);
print_debug( 'Done with WMS_CARTNZN_PUB.insert_ph', l_api_name);
DELETE wms_cartonization_temp
WHERE transaction_header_id < 0;
SELECT wct.*
FROM wms_cartonization_temp wct
, mtl_txn_request_lines mtrl
, mtl_secondary_inventories sub
, mtl_parameters mtlp
WHERE wct.move_order_line_id = mtrl.line_id
AND mtrl.header_id = p_move_order_header_id
AND wct.cartonization_id IS NULL
AND mtlp.organization_id = wct.organization_id
AND sub.organization_id = wct.organization_id
AND wct.transfer_lpn_id IS NULL
AND sub.secondary_inventory_name = wct.subinventory_code
AND ((Nvl(mtlp.cartonization_flag,-1) = 1)
OR (Nvl(mtlp.cartonization_flag,-1) = 3 AND sub.cartonization_flag = 1)
OR (NVL(mtlp.cartonization_flag,-1) = 4)
OR (NVL(mtlp.cartonization_flag,-1) = 5 AND sub.cartonization_flag = 1)
)
ORDER BY wct.move_order_line_id
, wct.inventory_item_id
, ABS(wct.transaction_temp_id);
SELECT *
FROM wms_cartonization_temp
WHERE transaction_header_id = p_hdr_id
AND cartonization_id IS NULL
AND transfer_lpn_id IS NULL
ORDER BY move_order_line_id
, DECODE( content_lpn_id
, NULL, inventory_item_id
, DECODE( SIGN(p_hdr_id)
, -1, inventory_item_id
, wms_cartnzn_pub.get_lpn_itemid (content_lpn_id)
)
)
, ABS(transaction_temp_id);
SELECT transaction_temp_id
, inventory_item_id
, primary_quantity
, transaction_quantity
, secondary_transaction_quantity
, content_lpn_id
, container_item_id
, cartonization_id
FROM wms_cartonization_temp
WHERE transaction_header_id = p_hdr_id
ORDER BY cartonization_id;
SELECT wct.transaction_temp_id
, wct.inventory_item_id
, wct.primary_quantity
, wct.transaction_quantity
, wct.secondary_transaction_quantity
, wct.content_lpn_id
, wct.container_item_id
, wct.cartonization_id
FROM wms_cartonization_temp wct
, mtl_txn_request_lines mtrl
WHERE wct.move_order_line_id = mtrl.line_id
AND mtrl.header_id = p_hdr_id
ORDER BY wct.cartonization_id;
DELETE wms_cartonization_temp;
WMS_CARTNZN_PUB.pkg_attr_table.DELETE;
WMS_CARTNZN_PUB.lpn_attr_table.DELETE;
WMS_CARTNZN_PUB.lpns_generated_tb.DELETE;
SELECT NVL(cartonization_flag,-1)
, NVL(cartonize_sales_orders,'Y')
, NVL(allocate_serial_flag,'N')
INTO v_cart_value
, l_cartonize_sales_orders
, WMS_CARTNZN_PUB.g_allocate_serial_flag
FROM mtl_parameters
WHERE organization_id = p_org_id;
SELECT percent_fill_basis_flag
INTO percent_fill_basis
FROM wsh_shipping_parameters
WHERE organization_id = p_org_id AND
ROWNUM < 2;
SELECT count(1)
INTO l_rulebased_setup_exists
FROM wms_selection_criteria_txn_v
WHERE rule_type_code = 12
AND enabled_flag = 1
AND from_organization_id = p_org_id
AND ROWNUM = 1;
print_debug('Inserting mmtt rows of this header into wms_cartonization_temp '
|| p_move_order_header_id , l_api_name);
SELECT revision_qty_control_code
, lot_control_code
, serial_number_control_code
INTO l_revision_code
, l_lot_code
, l_serial_code
FROM mtl_system_items
WHERE organization_id = v1.organization_id
AND inventory_item_id = v1.inventory_item_id;
SELECT NVL(SUM(primary_transaction_quantity),0)
INTO l_qoh FROM mtl_onhand_quantities_detail
WHERE organization_id = v1.organization_id
AND subinventory_code = v1.subinventory_code
AND locator_id = v1.locator_id
AND lpn_id = v1.allocated_lpn_id;
SELECT v1.transaction_temp_id, 'Y'
INTO WMS_CARTNZN_PUB.t_lpn_alloc_flag_table(v1.transaction_temp_id)
FROM DUAL;
SELECT v1.transaction_temp_id, 'N'
INTO WMS_CARTNZN_PUB.t_lpn_alloc_flag_table(v1.transaction_temp_id)
FROM DUAL;
SELECT v1.transaction_temp_id, 'Y'
INTO WMS_CARTNZN_PUB.t_lpn_alloc_flag_table(v1.transaction_temp_id)
FROM DUAL;
SELECT v1.transaction_temp_id, NULL
INTO WMS_CARTNZN_PUB.t_lpn_alloc_flag_table(v1.transaction_temp_id)
FROM DUAL;
SELECT primary_uom_code
INTO v_primary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = v1.inventory_item_id
AND organization_id = v1.ORGANIZATION_ID;
SELECT max_load_quantity
INTO v_qty_per_cont
FROM wsh_container_items
WHERE load_item_id = v1.inventory_item_id
AND master_organization_id = v1.organization_id
AND container_item_id = v_container_item_id;
SELECT msi.serial_number_control_code
INTO v_serial_control_code
FROM mtl_system_items msi
WHERE msi.inventory_item_id = v1.inventory_item_id
AND msi.organization_id = v1.organization_id;
WMS_CARTNZN_PUB.update_mmtt
( p_transaction_temp_id => v1.transaction_temp_id
, p_primary_quantity => v1.primary_quantity
, p_transaction_quantity => v1.transaction_quantity
, p_secondary_quantity => v1.secondary_transaction_quantity
, p_lpn_id => v_lpn_id
, p_container_item_id => v_container_item_id
, p_parent_line_id => NULL
, p_upd_qty_flag => l_upd_qty_flag
, x_return_status => l_api_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
WMS_CARTNZN_PUB.insert_mmtt
( p_transaction_temp_id => v1.transaction_temp_id
, p_primary_quantity => space_avail_for
, p_transaction_quantity => tr_space_avail_for
, p_secondary_quantity => sec_tr_space_avail_for
, p_lpn_id => v_lpn_id
, p_container_item_id => v_container_item_id
, x_return_status => l_api_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
WMS_CARTNZN_PUB.update_mmtt
( p_transaction_temp_id => v1.transaction_temp_id
, p_primary_quantity => v1.primary_quantity
, p_transaction_quantity => v1.transaction_quantity
, p_secondary_quantity => v1.secondary_transaction_quantity
, p_lpn_id => v_lpn_id
, p_container_item_id => v_container_item_id
, p_parent_line_id => NULL
, p_upd_qty_flag => l_upd_qty_flag
, x_return_status => l_api_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
print_debug('Calling update_mmtt', l_api_name);
WMS_CARTNZN_PUB.update_mmtt
( p_transaction_temp_id => v1.transaction_temp_id
, p_primary_quantity => v_qty_per_cont
, p_transaction_quantity => v_tr_qty_per_cont
, p_secondary_quantity => v_sec_tr_qty_per_cont
, p_lpn_id => v_lpn_id
, p_container_item_id => v_container_item_id
, p_parent_line_id => NULL
, p_upd_qty_flag => l_upd_qty_flag
, x_return_status => l_api_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
print_debug('Calling insert mmtt', l_api_name);
WMS_CARTNZN_PUB.insert_mmtt
( p_transaction_temp_id => v1.transaction_temp_id
, p_primary_quantity => v_qty_per_cont
, p_transaction_quantity => v_tr_qty_per_cont
, p_secondary_quantity => v_sec_tr_qty_per_cont
, p_lpn_id => v_lpn_id
, p_container_item_id => v_container_item_id
, x_return_status => l_api_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
print_debug('called insert mmtt', l_api_name);
print_debug('calling insert mmtt', l_api_name);
WMS_CARTNZN_PUB.insert_mmtt
( p_transaction_temp_id => v1.transaction_temp_id
, p_primary_quantity => v_left_prim_quant
, p_transaction_quantity => v_left_tr_quant
, p_secondary_quantity => v_sec_left_tr_quant
, p_lpn_id => v_lpn_id
, p_container_item_id => v_container_item_id
, x_return_status => l_api_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
WMS_CARTNZN_PUB.t_lpn_alloc_flag_table.DELETE;
print_debug( 'Inserting a new row for package ' || l_package_id
, l_api_name);
WMS_CARTNZN_PUB.insert_mmtt
( p_transaction_temp_id => l_temp_id
, p_primary_quantity => l_qty
, p_transaction_quantity => l_tr_qty
, p_secondary_quantity => l_sec_tr_qty
, p_new_txn_hdr_id => l_current_header_id
, p_new_txn_tmp_id => WMS_CARTNZN_PUB.get_next_temp_id
, p_clpn_id => l_package_id
, p_item_id => l_citem_id
, x_return_status => l_api_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
print_debug('Calling InsertPH for temp_id ' || l_temp_id, l_api_name);
WMS_CARTNZN_PUB.insert_ph (p_move_order_header_id, l_temp_id);
DELETE wms_cartonization_temp
WHERE transaction_header_id < 0;
l_last_update_date tbl_date;
l_last_updated_by tbl_num;
SELECT SYSDATE last_update_date
, g_user_id last_updated_by
, SYSDATE creation_date
, g_user_id created_by
, mmtt.inventory_item_id
, mmtt.revision
, mmtt.organization_id
, mmtt.subinventory_code
, mmtt.locator_id
, SUM(mmtt.transaction_quantity) transaction_quantity
, SUM(mmtt.primary_quantity) primary_quantity
, mmtt.transaction_uom
, mmtt.transaction_type_id
, mmtt.transaction_action_id
, mmtt.transaction_source_type_id
, MAX(mmtt.transaction_date) transaction_date
, MAX(mmtt.acct_period_id) acct_period_id
, mmtt.transfer_organization
, mmtt.wms_task_type
, MAX(mmtt.task_priority) task_priority
, mmtt.cost_group_id
, MAX(mmtt.transaction_header_id) transaction_header_id
, mmtt.container_item_id
, mmtt.operation_plan_id
, mmtt.wms_task_status -- carry forward task status also for unreleased/pending statuses
, NVL(wda.delivery_id, mol.carton_grouping_id) carton_grouping_id
, mmtt.item_primary_uom_code
, mmtt.item_lot_control_code
, mmtt.item_serial_control_code
, mmtt.serial_allocated_flag
, mmtt.transaction_batch_id
FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mol
, wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda
WHERE mmtt.move_order_line_id = mol.line_id
AND mol.header_id = p_move_order_header_id
AND mol.line_id = wdd.move_order_line_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND mmtt.wms_task_type NOT IN(5, 6)
AND mmtt.allocated_lpn_id IS NULL -- if lpn allocated, no need to do consolidation
AND mmtt.cartonization_id IS NULL -- only bulk non_cartonized lines
AND ( mmtt.serial_allocated_flag = 'N' -- do not bulk serial allocated lines
OR mmtt.serial_allocated_flag IS NULL )
AND NVL(mmtt.fulfillment_base,'P') = 'P'
AND ( l_bulk_pick_control = WMS_GLOBALS.BULK_PICK_ENTIRE_WAVE
-- If bulk picking is not disabled and not pick entire wave only the honor sub/item is left,
-- so no need to check l_bulk_pick_control, only need to check the sub/item flag
OR EXISTS( SELECT 1 -- sub is bulk picking enabled
FROM mtl_secondary_inventories msi
WHERE msi.secondary_inventory_name = mmtt.subinventory_code
AND msi.organization_id = mmtt.organization_id
AND msi.enable_bulk_pick= 'Y' )
OR EXISTS( SELECT 1 -- item is bulk picking enabled
FROM mtl_system_items msi
WHERE msi.inventory_item_id = mmtt.inventory_item_id
AND msi.organization_id = mmtt.organization_id
AND msi.bulk_picked_flag = 'Y' )
)
GROUP BY mmtt.inventory_item_id
, mmtt.revision
, mmtt.organization_id
, mmtt.subinventory_code
, mmtt.locator_id
, mmtt.transaction_uom
, mmtt.transaction_type_id
, mmtt.transaction_action_id
, mmtt.transaction_source_type_id
, mmtt.transfer_organization
, mmtt.wms_task_type
, mmtt.cost_group_id
, mmtt.container_item_id
, mmtt.operation_plan_id
, NVL(wda.delivery_id, mol.carton_grouping_id) -- only consolidate tasks with the same carton_grouping_id
-- (hense delivery) if the delivery is checked in the rule
, mmtt.wms_task_status
, mmtt.item_primary_uom_code
, mmtt.item_lot_control_code
, mmtt.item_serial_control_code
, mmtt.serial_allocated_flag
, mmtt.transaction_batch_id
HAVING SUM(mmtt.transaction_quantity) <> MIN(mmtt.transaction_quantity); -- make sure one line will not get consolidated
SELECT SYSDATE last_update_date
, g_user_id last_updated_by
, SYSDATE creation_date
, g_user_id created_by
, mmtt.inventory_item_id
, mmtt.revision
, mmtt.organization_id
, mmtt.subinventory_code
, mmtt.locator_id
, SUM(mmtt.transaction_quantity) transaction_quantity
, SUM(mmtt.primary_quantity) primary_quantity
, mmtt.transaction_uom
, mmtt.transaction_type_id
, mmtt.transaction_action_id
, mmtt.transaction_source_type_id
, MAX(mmtt.transaction_date) transaction_date
, MAX(mmtt.acct_period_id) acct_period_id
, mmtt.transfer_organization
, mmtt.wms_task_type
, MAX(mmtt.task_priority) task_priority
, mmtt.cost_group_id
, MAX(mmtt.transaction_header_id) transaction_header_id
, mmtt.container_item_id
, mmtt.operation_plan_id
, mmtt.wms_task_status -- carry forward task status also for unreleased/pending statuses
, mmtt.item_primary_uom_code
, mmtt.item_lot_control_code
, mmtt.item_serial_control_code
, mmtt.serial_allocated_flag
, mmtt.transaction_batch_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.wms_task_type NOT IN(5, 6)
AND mmtt.allocated_lpn_id IS NULL -- if lpn allocated, no need to do consolidation
AND mmtt.cartonization_id IS NULL -- only bulk non_cartonized lines
AND mmtt.move_order_header_id = p_move_order_header_id
AND ( mmtt.serial_allocated_flag = 'N' -- do not bulk serial allocated lines
or mmtt.serial_allocated_flag IS NULL )
AND NVL(mmtt.fulfillment_base,'P') = 'P'
AND ( l_bulk_pick_control = WMS_GLOBALS.BULK_PICK_ENTIRE_WAVE
-- if bulk picking is not disabled and not pick entire wave only the honor sub/item is left,
-- so no need to check l_bulk_pick_control, only need to check the sub/item flag
OR EXISTS( SELECT 1 -- sub is bulk picking enabled
FROM mtl_secondary_inventories msi
WHERE msi.secondary_inventory_name = mmtt.subinventory_code
AND msi.organization_id = mmtt.organization_id
AND msi.enable_bulk_pick= 'Y' )
OR EXISTS( SELECT 1 -- item is bulk picking enabled
FROM mtl_system_items msi
WHERE msi.inventory_item_id = mmtt.inventory_item_id
AND msi.organization_id = mmtt.organization_id
AND msi.bulk_picked_flag = 'Y' )
)
GROUP BY mmtt.inventory_item_id
, mmtt.revision
, mmtt.organization_id
, mmtt.subinventory_code
, mmtt.locator_id
, mmtt.transaction_uom
, mmtt.transaction_type_id
, mmtt.transaction_action_id
, mmtt.transaction_source_type_id
, mmtt.transfer_organization
, mmtt.wms_task_type
, mmtt.cost_group_id
, mmtt.container_item_id
, mmtt.operation_plan_id
, mmtt.wms_task_status
, mmtt.item_primary_uom_code
, mmtt.item_lot_control_code
, mmtt.item_serial_control_code
, mmtt.serial_allocated_flag
, mmtt.transaction_batch_id
HAVING SUM(mmtt.transaction_quantity) <> MIN(mmtt.transaction_quantity); -- make sure one line will not get consolidated
SELECT spg.bulk_pick_control
INTO l_bulk_pick_control
FROM wsh_pick_grouping_rules spg
WHERE spg.pick_grouping_rule_id = INV_CACHE.mtrh_rec.grouping_rule_id;
SELECT DELIVERY_FLAG
INTO l_delivery_flag
FROM WSH_PICK_GROUPING_RULES
WHERE pick_method=WMS_GLOBALS.PICK_METHOD_BULK
AND user_defined_flag = 'N' -- bulk picking default rule
AND ROWNUM < 2; -- In case of pseudo translation, multiple records are inserted for the seeded rule
INTO l_last_update_date
, l_last_updated_by
, l_creation_date
, l_created_by
, l_inventory_item_id
, l_revision
, l_organization_id
, l_subinventory_code
, l_locator_id
, l_transaction_quantity
, l_primary_quantity
, l_transaction_uom
, l_transaction_type_id
, l_transaction_action_id
, l_transaction_source_type_id
, l_transaction_date
, l_acct_period_id
, l_to_org_id
, l_wms_task_type
, l_task_priority
, l_cost_group_id
, l_transaction_header_id
, l_container_item_id
, l_operation_plan_id
, l_wms_task_status
, l_carton_grouping_id
, l_primary_uom_code
, l_lot_control_code
, l_serial_control_code
, l_serial_allocated_flag
, l_transaction_batch_id LIMIT g_bulk_fetch_limit;
INTO l_last_update_date
, l_last_updated_by
, l_creation_date
, l_created_by
, l_inventory_item_id
, l_revision
, l_organization_id
, l_subinventory_code
, l_locator_id
, l_transaction_quantity
, l_primary_quantity
, l_transaction_uom
, l_transaction_type_id
, l_transaction_action_id
, l_transaction_source_type_id
, l_transaction_date
, l_acct_period_id
, l_to_org_id
, l_wms_task_type
, l_task_priority
, l_cost_group_id
, l_transaction_header_id
, l_container_item_id
, l_operation_plan_id
, l_wms_task_status
, l_primary_uom_code
, l_lot_control_code
, l_serial_control_code
, l_serial_allocated_flag
, l_transaction_batch_id LIMIT g_bulk_fetch_limit;
INSERT INTO mtl_material_transactions_temp
( transaction_header_id
, transaction_temp_id
, posting_flag
, transaction_status
, last_update_date
, last_updated_by
, creation_date
, created_by
, transaction_type_id
, transaction_action_id
, transaction_source_type_id
, organization_id
, inventory_item_id
, revision
, subinventory_code
, locator_id
, transfer_organization
, transaction_quantity
, primary_quantity
, transaction_uom
, transaction_date
, acct_period_id
, cost_group_id
, wms_task_type
, task_priority
, container_item_id
, operation_plan_id
, wms_task_status
, parent_line_id
, item_primary_uom_code
, item_lot_control_code
, item_serial_control_code
, serial_allocated_flag
, transaction_batch_id
, pick_slip_number
, lock_flag ) --bug 9130704
VALUES ( l_transaction_header_id(jj)
, mtl_material_transactions_s.NEXTVAL
, 'N'
, 2
, l_last_update_date(jj)
, l_last_updated_by(jj)
, l_creation_date(jj)
, l_created_by(jj)
, l_transaction_type_id(jj)
, l_transaction_action_id(jj)
, l_transaction_source_type_id(jj)
, l_organization_id(jj)
, l_inventory_item_id(jj)
, l_revision(jj)
, l_subinventory_code(jj)
, l_locator_id(jj)
, l_to_org_id(jj)
, l_transaction_quantity(jj)
, l_primary_quantity(jj)
, l_transaction_uom(jj)
, l_transaction_date(jj)
, l_acct_period_id(jj)
, l_cost_group_id(jj)
, l_wms_task_type(jj)
, l_task_priority(jj)
, l_container_item_id(jj)
, l_operation_plan_id(jj)
, l_wms_task_status(jj)
, mtl_material_transactions_s.CURRVAL
, l_primary_uom_code(jj)
, l_lot_control_code(jj)
, l_serial_control_code(jj)
, l_serial_allocated_flag(jj)
, l_transaction_batch_id(jj)
, wsh_pick_slip_numbers_s.NEXTVAL
, 'Y')
RETURNING transaction_temp_id BULK COLLECT INTO tbl_transaction_temp_id;
print_debug( 'Inserted ' || l_transaction_header_id.COUNT || ' parent MMTTs'
, l_api_name);
UPDATE mtl_material_transactions_temp mmtt
SET parent_line_id = tbl_transaction_temp_id(kk)
, transaction_batch_id = NULL
, pick_slip_number = NULL
WHERE transaction_temp_id <> tbl_transaction_temp_id(kk)
AND move_order_header_id = p_mo_header_id
AND inventory_item_id = l_inventory_item_id(kk)
AND NVL(revision, '#$%') = NVL(l_revision(kk), NVL(revision, '#$%'))
AND organization_id = l_organization_id(kk)
AND subinventory_code = l_subinventory_code(kk)
AND transaction_uom = l_transaction_uom(kk)
AND NVL(cost_group_id, -1) = NVL(l_cost_group_id(kk), NVL(cost_group_id, -1))
AND NVL(locator_id, -1) = NVL(l_locator_id(kk), NVL(locator_id, -1))
AND NVL(transfer_organization, -1) = NVL(l_to_org_id(kk), NVL(transfer_organization, -1))
AND NVL(transaction_type_id, -1) = NVL(l_transaction_type_id(kk), NVL(transaction_type_id, -1))
AND NVL(transaction_action_id, -1) = NVL(l_transaction_action_id(kk), NVL(transaction_action_id, -1))
AND NVL(transaction_source_type_id, -1) = NVL(l_transaction_source_type_id(kk), NVL(transaction_source_type_id, -1))
AND EXISTS( SELECT 1
FROM mtl_txn_request_lines mol,wsh_delivery_details_ob_grp_v wdd,wsh_delivery_assignments_v wda
WHERE mol.line_id = mmtt.move_order_line_id
AND mol.line_id = wdd.move_order_line_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND NVL(wda.delivery_id,mol.carton_grouping_id) = l_carton_grouping_id(kk) )
AND allocated_lpn_id IS NULL -- Bug: 9309619 Added below condition
AND cartonization_id is NULL;--added for bug 9446937
UPDATE mtl_material_transactions_temp
SET parent_line_id = tbl_transaction_temp_id(kk)
, transaction_batch_id = NULL
, pick_slip_number = NULL --newly added
WHERE transaction_temp_id <> tbl_transaction_temp_id(kk)
AND move_order_header_id = p_mo_header_id
AND inventory_item_id = l_inventory_item_id(kk)
AND NVL(revision, '#$%') = NVL(l_revision(kk), NVL(revision, '#$%'))
AND organization_id = l_organization_id(kk)
AND subinventory_code = l_subinventory_code(kk)
AND transaction_uom = l_transaction_uom(kk)
AND NVL(locator_id, -1) = NVL(l_locator_id(kk), NVL(locator_id, -1))
AND NVL(cost_group_id, -1) = NVL(l_cost_group_id(kk), NVL(cost_group_id, -1))
AND NVL(transfer_organization, -1) = NVL(l_to_org_id(kk), NVL(transfer_organization, -1))
AND NVL(transaction_type_id, -1) = NVL(l_transaction_type_id(kk), NVL(transaction_type_id, -1))
AND NVL(transaction_action_id, -1) = NVL(l_transaction_action_id(kk), NVL(transaction_action_id, -1))
AND NVL(transaction_source_type_id, -1) = NVL(l_transaction_source_type_id(kk), NVL(transaction_source_type_id, -1))
AND allocated_lpn_id IS NULL -- Bug: 9309619 Added below condition
AND cartonization_id is NULL;--added for bug 9446937
print_debug('Updated child MMTTs', l_api_name);
INSERT INTO mtl_transaction_lots_temp
( transaction_temp_id
, lot_number
, transaction_quantity
, primary_quantity
, lot_expiration_date
, last_update_date
, last_updated_by
, creation_date
, created_by
, serial_transaction_temp_id) -- always set to null since we don't bulk
-- lines with allocated serial numbers
( SELECT tbl_transaction_temp_id(ii) -- transaction_temp_id of parent line
, mtlt.lot_number
, SUM(mtlt.transaction_quantity) transaction_quantity
, SUM(mtlt.primary_quantity) primary_quantity
, mtlt.lot_expiration_date
, SYSDATE
, g_user_id
, SYSDATE
, g_user_id
, NULL
FROM mtl_transaction_lots_temp mtlt, mtl_material_transactions_temp mmtt
WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mmtt.parent_line_id = tbl_transaction_temp_id(ii) -- child task
AND mmtt.transaction_temp_id <> tbl_transaction_temp_id(ii) -- not parent task
AND l_lot_control_code(ii) = 2
GROUP BY mtlt.lot_number,mtlt.lot_expiration_date );
print_debug('Inserted parent MTLT records', l_api_name);
SELECT mmtt.transaction_temp_id
, mmtt.transaction_quantity
FROM mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mol
, wsh_delivery_details_ob_grp_v wdd
, wsh_delivery_assignments_v wda
WHERE mmtt.parent_line_id = p_parent_line_id
AND mol.line_id = mmtt.move_order_line_id
AND mol.line_id = wdd.move_order_line_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND mmtt.transaction_temp_id <> p_parent_line_id
ORDER BY nvl(wda.delivery_id,mol.carton_grouping_id)
, mmtt.transaction_quantity DESC;
SELECT res_equip.inventory_item_id
FROM mtl_material_transactions_temp mmtt
, bom_std_op_resources tt_x_res
, bom_resources res
, bom_resource_equipments res_equip
WHERE mmtt.transaction_temp_id = p_task_id
AND mmtt.standard_operation_id = tt_x_res.standard_operation_id
AND tt_x_res.resource_id = res.resource_id
AND res.organization_id = mmtt.organization_id
AND res.resource_type = 1
AND res_equip.resource_id = res.resource_id
AND res_equip.organization_id = res.organization_id;
SELECT NVL(mil.pick_uom_code, msi.pick_uom_code)
, mmtt.organization_id
, mmtt.parent_line_id
, mmtt.inventory_item_id
, mmtt.standard_operation_id
, mmtt.transaction_quantity
, mmtt.transaction_uom
, mmtt.secondary_transaction_quantity
, mmtt.secondary_uom_code
, item.primary_uom_code
, item.lot_control_code
, item.serial_number_control_code
INTO l_loc_uom_code
, l_organization_id
, l_parent_line_id
, l_item_id
, l_std_op_id
, l_init_qty
, l_txn_uom_code
, l_sec_txn_qty
, l_sec_uom_code
, l_item_prim_uom_code
, l_lot_control_code
, l_serial_number_control_code
FROM mtl_material_transactions_temp mmtt
, mtl_item_locations mil
, mtl_secondary_inventories msi
, mtl_system_items item
WHERE mmtt.transaction_temp_id = p_mmtt_temp_id
AND mmtt.locator_id = mil.inventory_location_id(+)
AND mmtt.organization_id = mil.organization_id(+)
AND mmtt.subinventory_code = msi.secondary_inventory_name
AND mmtt.organization_id = msi.organization_id
AND mmtt.inventory_item_id = item.inventory_item_id
AND mmtt.organization_id = item.organization_id;
SELECT msi.volume_uom_code
, msi.weight_uom_code
, msi.maximum_load_weight
, msi.internal_volume
INTO l_equip_v_uom
, l_equip_w_uom
, l_equip_weight
, l_equip_vol
FROM mtl_system_items msi
WHERE msi.organization_id = l_organization_id
AND msi.inventory_item_id = l_eqp_id;
SELECT *
INTO l_task_new_rec
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_mmtt_temp_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_temp_id
FROM DUAL;
wms_task_dispatch_engine.insert_mmtt(l_task_new_rec);
DELETE FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_mmtt_temp_id;
UPDATE mtl_material_transactions_temp
SET parent_line_id = l_new_temp_id
WHERE transaction_temp_id = l_child_rec.transaction_temp_id;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = l_child_remaining_qty
, primary_quantity = l_child_remaining_qty * l_txn_pri_uom_ratio
, secondary_transaction_quantity
= DECODE( secondary_uom_code
, NULL, NULL
, l_child_remaining_qty * l_txn_sec_uom_ratio
)
WHERE transaction_temp_id = l_child_rec.transaction_temp_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_child_temp_id
FROM DUAL;
SELECT *
INTO l_child_new_rec
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_child_rec.transaction_temp_id;
wms_task_dispatch_engine.insert_mmtt(l_child_new_rec);
print_debug('Update original MMTT row with remaining qty: '
|| l_init_qty, l_api_name);
UPDATE mtl_material_transactions_temp
SET transaction_quantity = l_init_qty
, primary_quantity = l_init_qty * l_txn_pri_uom_ratio
, secondary_transaction_quantity
= DECODE( secondary_uom_code
, NULL, NULL
, l_init_qty * l_txn_sec_uom_ratio
)
WHERE transaction_temp_id = p_mmtt_temp_id;
SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_batch_id = p_txn_batch_id
AND standard_operation_id IS NOT NULL; -- skip rows without task types
SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_batch_id = p_batch_id;
SELECT rules.rule_id, rules.type_hdr_id BULK COLLECT
INTO g_t_tta_rule_id, g_t_tta_type_hdr_id
FROM wms_rules_b rules
, bom_standard_operations bso
WHERE rules.organization_id IN (g_tta_org_id,-1)
AND rules.type_code = 3
AND rules.enabled_flag = 'Y'
AND rules.type_hdr_id = bso.standard_operation_id
AND bso.organization_id = g_tta_org_id
AND bso.wms_task_type = 1
ORDER BY rules.rule_weight DESC, rules.creation_date;
UPDATE mtl_material_transactions_temp
SET standard_operation_id = l_mmtt_hid(kk)
WHERE transaction_temp_id = l_mmtt_temp_id(kk);
UPDATE mtl_material_transactions_temp
SET standard_operation_id = l_task_type_id
WHERE transaction_batch_id = p_transaction_batch_id;
UPDATE wms_pr_workers
SET worker_mode = 'TSPLIT', processed_flag = 'N'
WHERE transaction_batch_id = p_transaction_batch_id;
SELECT device_id
INTO l_ret_dev_id
FROM ( SELECT wbed.device_id
FROM wms_bus_event_devices wbed
, wms_devices_b wd
WHERE wd.device_id = wbed.device_id
AND wbed.organization_id = wd.organization_id
AND wd.ENABLED_FLAG = 'Y'
AND wbed.ENABLED_FLAG = 'Y'
AND DECODE( level_type
, g_device_level_sub, p_subinventory_code
, level_value) = DECODE( level_type
, g_device_level_sub, p_subinventory_code
, g_device_level_org, p_organization_id
, g_device_level_locator, p_locator_id
, g_device_level_user, g_user_id
, level_value
)
AND NVL(wbed.organization_id,-1)
= NVL(p_organization_id,NVL(wbed.organization_id,-1))
AND wbed.auto_enabled_flag = 'Y'
AND wbed.business_event_id = g_wms_be_pick_release
ORDER BY level_type DESC )
WHERE ROWNUM < 2;
SELECT force_sign_on_flag
INTO l_force_sign_on_flag
FROM wms_devices_b
WHERE device_id = l_ret_dev_id;
SELECT device_id
INTO l_temp_dev_id
FROM wms_device_assignment_temp
WHERE device_id = l_ret_dev_id
AND created_by = g_user_id;
PROCEDURE insert_device_requests
( p_organization_id IN NUMBER
, p_mo_header_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
) IS
l_api_name VARCHAR2(30) := 'insert_device_requests';
t_last_update_date tbl_date;
t_last_updated_by tbl_num;
t_last_update_login tbl_num;
SELECT x.transaction_temp_id
, x.organization_id
, x.subinventory_code
, x.locator_id
, x.transfer_organization
, x.inventory_item_id
, x.revision
, x.transaction_uom
, x.allocated_lpn_id
, x.cartonization_id
, x.transaction_quantity
, x.transfer_subinventory
, x.transfer_to_location
, x.last_update_date
, x.last_updated_by
, x.last_update_login
, x.dev_id
, NVL(dev.lot_serial_capable,'N') lot_serial_capable_flag
FROM ( SELECT mmtt.transaction_temp_id
, mmtt.organization_id
, mmtt.subinventory_code
, mmtt.locator_id
, mmtt.transfer_organization
, mmtt.inventory_item_id
, mmtt.revision
, mmtt.transaction_uom
, mmtt.allocated_lpn_id
, mmtt.cartonization_id
, mmtt.transaction_quantity
, mmtt.transfer_subinventory
, mmtt.transfer_to_location
, mmtt.last_update_date
, mmtt.last_updated_by
, mmtt.last_update_login
, get_device_id( mmtt.organization_id
, mmtt.subinventory_code
, mmtt.locator_id
) dev_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_header_id = p_mo_hdr_id
AND mmtt.parent_line_id IS NULL
) x
, wms_devices_b dev
WHERE x.dev_id = dev.device_id(+);
SELECT transaction_temp_id
, organization_id
, subinventory_code
, locator_id
, transfer_organization
, inventory_item_id
, revision
, transaction_uom
, allocated_lpn_id
, cartonization_id
, transaction_quantity
, transfer_subinventory
, transfer_to_location
, last_update_date
, last_updated_by
, last_update_login
, get_device_id( mmtt.organization_id
, mmtt.subinventory_code
, mmtt.locator_id
) dev_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_header_id = p_mo_hdr_id
AND mmtt.parent_line_id IS NOT NULL;
SELECT x.transaction_temp_id
, x.organization_id
, x.subinventory_code
, x.locator_id
, x.transfer_organization
, x.inventory_item_id
, x.revision
, x.transaction_uom
, x.allocated_lpn_id
, x.cartonization_id
, x.transaction_quantity
, x.transfer_subinventory
, x.transfer_to_location
, x.last_update_date
, x.last_updated_by
, x.last_update_login
, x.dev_id
, NVL(dev.lot_serial_capable,'N') lot_serial_capable_flag
FROM ( SELECT mmtt.transaction_temp_id
, mmtt.organization_id
, mmtt.subinventory_code
, mmtt.locator_id
, mmtt.transfer_organization
, mmtt.inventory_item_id
, mmtt.revision
, mmtt.transaction_uom
, mmtt.allocated_lpn_id
, mmtt.cartonization_id
, mmtt.transaction_quantity
, mmtt.transfer_subinventory
, mmtt.transfer_to_location
, mmtt.last_update_date
, mmtt.last_updated_by
, mmtt.last_update_login
, get_device_id( mmtt.organization_id
, mmtt.subinventory_code
, mmtt.locator_id
) dev_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id IN
( SELECT DISTINCT parent_line_id
FROM mtl_material_transactions_temp mmtt1
WHERE mmtt1.move_order_header_id = p_mo_hdr_id
AND mmtt1.parent_line_id IS NOT NULL
)
) x,
wms_devices_b dev
WHERE x.dev_id = dev.device_id(+);
, t_last_update_date
, t_last_updated_by
, t_last_update_login
, t_dev_id
, t_lot_serial_capable_flag LIMIT g_bulk_fetch_limit;
INSERT INTO wms_device_requests( request_id
, task_id
, task_summary
, task_type_id
, business_event_id
, organization_id
, subinventory_code
, locator_id
, transfer_org_id
, transfer_sub_code
, transfer_loc_id
, inventory_item_id
, revision
, uom
, lpn_id
, xfer_lpn_id
, transaction_quantity
, last_update_date
, last_updated_by
, device_id )
VALUES( wms_device_integration_pvt.wms_pkRel_dev_req_id --global var same for all pick release lines
, t_transaction_temp_id(ii)
, 'Y'
, 1 --"LOAD"
, l_bus_event_id
, t_organization_id(ii)
, t_subinventory_code(ii)
, t_locator_id(ii)
, t_transfer_organization(ii)
, NULL
, NULL
, t_inventory_item_id(ii)
, t_revision(ii)
, t_transaction_uom(ii)
, t_allocated_lpn_id(ii)
, t_cartonization_id(ii)
, t_transaction_quantity(ii)
, SYSDATE
, g_user_id
, t_dev_id(ii) );
INSERT INTO wms_device_requests( request_id
, task_id
, task_summary
, task_type_id
, business_event_id
, organization_id
, subinventory_code
, locator_id
, transfer_org_id
, transfer_sub_code
, transfer_loc_id
, inventory_item_id
, revision
, uom
, lpn_id
, xfer_lpn_id
, transaction_quantity
, last_update_date
, last_updated_by
, device_id )
VALUES( wms_device_integration_pvt.wms_pkRel_dev_req_id
, t_transaction_temp_id(jj)
, 'Y'
, 2 --"DROP"
, l_bus_event_id
, t_organization_id(jj)
, NULL
, NULL
, t_transfer_organization(jj)
, t_transfer_subinventory(jj)
, t_transfer_to_location(jj)
, t_inventory_item_id(jj)
, t_revision(jj)
, t_transaction_uom(jj)
, t_allocated_lpn_id(jj)
, t_cartonization_id(jj)
, t_transaction_quantity(jj)
, SYSDATE
, g_user_id
, t_dev_id(jj) );
INSERT INTO wms_device_requests( request_id
, task_id
, relation_id
, sequence_id
, task_summary
, task_type_id
, business_event_id
, organization_id
, subinventory_code
, locator_id
, transfer_org_id
, transfer_sub_code
, transfer_loc_id
, inventory_item_id
, revision
, uom
, lot_number
, lot_qty
, serial_number
, lpn_id
, xfer_lpn_id
, transaction_quantity
, device_id
, status_code
, last_update_date
, last_updated_by
, last_update_login )
( SELECT wms_device_integration_pvt.wms_pkRel_dev_req_id
, t_transaction_temp_id(kk)
, NULL
, NULL
, 'N'
, NULL
, l_bus_event_id
, t_organization_id(kk)
, t_subinventory_code(kk)
, t_locator_id(kk)
, t_transfer_organization(kk)
, t_transfer_subinventory(kk)
, t_transfer_to_location(kk)
, t_inventory_item_id(kk)
, t_revision(kk)
, t_transaction_uom(kk)
, mtlt.lot_number
, mtlt.transaction_quantity
, NULL
, t_allocated_lpn_id(kk)
, t_cartonization_id(kk)
, mtlt.transaction_quantity
, t_dev_id(kk)
, 'S'
, t_last_update_date(kk)
, t_last_updated_by(kk)
, t_last_update_login(kk)
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = t_transaction_temp_id(kk)
AND t_dev_id(kk) IS NOT NULL
AND t_lot_serial_capable_flag(kk) <> 'N'
AND mtlt.serial_transaction_temp_id IS NULL
UNION ALL
SELECT wms_device_integration_pvt.wms_pkRel_dev_req_id
, t_transaction_temp_id(kk)
, NULL
, NULL
, 'N'
, NULL
, l_bus_event_id
, t_organization_id(kk)
, t_subinventory_code(kk)
, t_locator_id(kk)
, t_transfer_organization(kk)
, t_transfer_subinventory(kk)
, t_transfer_to_location(kk)
, t_inventory_item_id(kk)
, t_revision(kk)
, t_transaction_uom(kk)
, NULL
, NULL
, msnt.fm_serial_number
, t_allocated_lpn_id(kk)
, t_cartonization_id(kk)
, 1
, t_dev_id(kk)
, 'S'
, t_last_update_date(kk)
, t_last_updated_by(kk)
, t_last_update_login(kk)
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = t_transaction_temp_id(kk)
AND t_dev_id(kk) IS NOT NULL
AND t_lot_serial_capable_flag(kk) <> 'N'
UNION ALL
SELECT wms_device_integration_pvt.wms_pkRel_dev_req_id
, t_transaction_temp_id(kk)
, NULL
, NULL
, 'N'
, NULL
, l_bus_event_id
, t_organization_id(kk)
, t_subinventory_code(kk)
, t_locator_id(kk)
, t_transfer_organization(kk)
, t_transfer_subinventory(kk)
, t_transfer_to_location(kk)
, t_inventory_item_id(kk)
, t_revision(kk)
, t_transaction_uom(kk)
, mtlt.lot_number
, mtlt.transaction_quantity
, msnt.fm_serial_number
, t_allocated_lpn_id(kk)
, t_cartonization_id(kk)
, 1
, t_dev_id(kk)
, 'S'
, t_last_update_date(kk)
, t_last_updated_by(kk)
, t_last_update_login(kk)
FROM mtl_transaction_lots_temp mtlt, mtl_serial_numbers_temp msnt
WHERE mtlt.transaction_temp_id = t_transaction_temp_id(kk)
AND mtlt.serial_transaction_temp_id = msnt.transaction_temp_id
AND t_dev_id(kk) IS NOT NULL
AND t_lot_serial_capable_flag(kk) <> 'N' );
t_transaction_temp_id.DELETE;
t_organization_id.DELETE;
t_subinventory_code.DELETE;
t_locator_id.DELETE;
t_transfer_organization.DELETE;
t_inventory_item_id.DELETE;
t_revision.DELETE;
t_transaction_uom.DELETE;
t_allocated_lpn_id.DELETE;
t_cartonization_id.DELETE;
t_transaction_quantity.DELETE;
t_dev_id.DELETE;
t_lot_serial_capable_flag.DELETE;
t_transfer_subinventory.DELETE;
t_transfer_to_location.DELETE;
t_last_update_date.DELETE;
t_last_updated_by.DELETE;
t_last_update_login.DELETE;
, t_last_update_date
, t_last_updated_by
, t_last_update_login
, t_dev_id LIMIT g_bulk_fetch_limit;
INSERT INTO wms_device_requests( request_id
, task_id
, task_summary
, task_type_id
, business_event_id
, organization_id
, subinventory_code
, locator_id
, transfer_org_id
, transfer_sub_code
, transfer_loc_id
, inventory_item_id
, revision
, uom
, lpn_id
, xfer_lpn_id
, transaction_quantity
, last_update_date
, last_updated_by
, device_id )
VALUES( wms_device_integration_pvt.wms_pkRel_dev_req_id
, t_transaction_temp_id(ll)
, 'Y'
, 2 --"DROP"
, l_bus_event_id
, t_organization_id(ll)
, NULL
, NULL
, t_transfer_organization(ll)
, t_transfer_subinventory(ll)
, t_transfer_to_location(ll)
, t_inventory_item_id(ll)
, t_revision(ll)
, t_transaction_uom(ll)
, t_allocated_lpn_id(ll)
, t_cartonization_id(ll)
, t_transaction_quantity(ll)
, SYSDATE
, g_user_id
, t_dev_id(ll) );
t_transaction_temp_id.DELETE;
t_organization_id.DELETE;
t_subinventory_code.DELETE;
t_locator_id.DELETE;
t_transfer_organization.DELETE;
t_inventory_item_id.DELETE;
t_revision.DELETE;
t_transaction_uom.DELETE;
t_allocated_lpn_id.DELETE;
t_cartonization_id.DELETE;
t_transaction_quantity.DELETE;
t_dev_id.DELETE;
t_lot_serial_capable_flag.DELETE;
t_transfer_subinventory.DELETE;
t_transfer_to_location.DELETE;
t_last_update_date.DELETE;
t_last_updated_by.DELETE;
t_last_update_login.DELETE;
, t_last_update_date
, t_last_updated_by
, t_last_update_login
, t_dev_id
, t_lot_serial_capable_flag LIMIT g_bulk_fetch_limit;
INSERT INTO wms_device_requests( request_id
, task_id
, task_summary
, task_type_id
, business_event_id
, organization_id
, subinventory_code
, locator_id
, transfer_org_id
, transfer_sub_code
, transfer_loc_id
, inventory_item_id
, revision
, uom
, lpn_id
, xfer_lpn_id
, transaction_quantity
, last_update_date
, last_updated_by
, device_id )
VALUES( wms_device_integration_pvt.wms_pkRel_dev_req_id
, t_transaction_temp_id(mm)
, 'Y'
, 1 --"LOAD"
, l_bus_event_id
, t_organization_id(mm)
, t_subinventory_code(mm)
, t_locator_id(mm)
, t_transfer_organization(mm)
, NULL
, NULL
, t_inventory_item_id(mm)
, t_revision(mm)
, t_transaction_uom(mm)
, t_allocated_lpn_id(mm)
, t_cartonization_id(mm)
, t_transaction_quantity(mm)
, SYSDATE
, g_user_id
, t_dev_id(mm) );
print_debug ('Done inserting LOAD records for parent tasks', l_api_name);
INSERT INTO wms_device_requests( request_id
, task_id
, relation_id
, sequence_id
, task_summary
, task_type_id
, business_event_id
, organization_id
, subinventory_code
, locator_id
, transfer_org_id
, transfer_sub_code
, transfer_loc_id
, inventory_item_id
, revision
, uom
, lot_number
, lot_qty
, serial_number
, lpn_id
, xfer_lpn_id
, transaction_quantity
, device_id
, status_code
, last_update_date
, last_updated_by
, last_update_login )
( SELECT wms_device_integration_pvt.wms_pkRel_dev_req_id
, t_transaction_temp_id(nn)
, NULL
, NULL
, 'N'
, NULL
, l_bus_event_id
, t_organization_id(nn)
, t_subinventory_code(nn)
, t_locator_id(nn)
, t_transfer_organization(nn)
, t_transfer_subinventory(nn)
, t_transfer_to_location(nn)
, t_inventory_item_id(nn)
, t_revision(nn)
, t_transaction_uom(nn)
, mtlt.lot_number
, mtlt.transaction_quantity
, NULL
, t_allocated_lpn_id(nn)
, t_cartonization_id(nn)
, mtlt.transaction_quantity
, t_dev_id(nn)
, 'S'
, t_last_update_date(nn)
, t_last_updated_by(nn)
, t_last_update_login(nn)
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = t_transaction_temp_id(nn)
AND t_dev_id(nn) IS NOT NULL
AND t_lot_serial_capable_flag(nn) <> 'N'
AND mtlt.serial_transaction_temp_id IS NULL
UNION ALL
SELECT wms_device_integration_pvt.wms_pkRel_dev_req_id
, t_transaction_temp_id(nn)
, NULL
, NULL
, 'N'
, NULL
, l_bus_event_id
, t_organization_id(nn)
, t_subinventory_code(nn)
, t_locator_id(nn)
, t_transfer_organization(nn)
, t_transfer_subinventory(nn)
, t_transfer_to_location(nn)
, t_inventory_item_id(nn)
, t_revision(nn)
, t_transaction_uom(nn)
, NULL
, NULL
, msnt.fm_serial_number
, t_allocated_lpn_id(nn)
, t_cartonization_id(nn)
, 1
, t_dev_id(nn)
, 'S'
, t_last_update_date(nn)
, t_last_updated_by(nn)
, t_last_update_login(nn)
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = t_transaction_temp_id(nn)
AND t_dev_id(nn) IS NOT NULL
AND t_lot_serial_capable_flag(nn) <> 'N'
UNION ALL
SELECT wms_device_integration_pvt.wms_pkRel_dev_req_id
, t_transaction_temp_id(nn)
, NULL
, NULL
, 'N'
, NULL
, l_bus_event_id
, t_organization_id(nn)
, t_subinventory_code(nn)
, t_locator_id(nn)
, t_transfer_organization(nn)
, t_transfer_subinventory(nn)
, t_transfer_to_location(nn)
, t_inventory_item_id(nn)
, t_revision(nn)
, t_transaction_uom(nn)
, mtlt.lot_number
, mtlt.transaction_quantity
, msnt.fm_serial_number
, t_allocated_lpn_id(nn)
, t_cartonization_id(nn)
, 1
, t_dev_id(nn)
, 'S'
, t_last_update_date(nn)
, t_last_updated_by(nn)
, t_last_update_login(nn)
FROM mtl_transaction_lots_temp mtlt, mtl_serial_numbers_temp msnt
WHERE mtlt.transaction_temp_id = t_transaction_temp_id(nn)
AND mtlt.serial_transaction_temp_id = msnt.transaction_temp_id
AND t_dev_id(nn) IS NOT NULL
AND t_lot_serial_capable_flag(nn) <> 'N' );
DELETE FROM wms_device_requests
WHERE request_id = wms_device_integration_pvt.wms_pkRel_dev_req_id;
DELETE FROM wms_device_requests
WHERE request_id = wms_device_integration_pvt.wms_pkRel_dev_req_id;
END insert_device_requests;
l_pick_slip_sql VARCHAR2(2000) := 'SELECT ';
l_mmtt_sql_base VARCHAR2(2000) := 'SELECT mmtt.transaction_temp_id'
|| ' , inv_salesorder.get_salesorder_for_oeheader(wdd.oe_header_id)'
|| ' , wdd.oe_line_id'
|| ' FROM mtl_material_transactions_temp mmtt'
|| ' , mtl_txn_request_lines mtrl'
|| ' , wsh_inv_delivery_details_v wdd '
|| ' WHERE mtrl.header_id = :mo_header_id'
|| ' AND mmtt.move_order_line_id = mtrl.line_id'
|| ' AND wdd.move_order_line_id = mtrl.line_id'
|| ' AND mmtt.parent_line_id IS NULL'; -- non-bulk tasks
SELECT NVL(order_number_flag, 'N')
, NVL(customer_flag, 'N')
, NVL(ship_to_flag, 'N')
, NVL(carrier_flag, 'N')
, NVL(shipment_priority_flag, 'N')
, NVL(trip_stop_flag, 'N')
, NVL(delivery_flag, 'N')
, NVL(subinventory_flag, 'N')
, NVL(locator_flag, 'N')
, NVL(dest_sub_flag, 'N')
, NVL(dest_loc_flag, 'N')
, NVL(project_flag, 'N')
, NVL(task_flag, 'N')
, NVL(item_flag, 'N')
, NVL(revision_flag, 'N')
, NVL(lot_flag, 'N')
, NVL(pick_method, '-99')
FROM wsh_pick_grouping_rules
WHERE pick_grouping_rule_id = p_pgr_id;
SELECT mmtt.transaction_temp_id
, inv_salesorder.get_salesorder_for_oeheader(wdd.oe_header_id)
, wdd.oe_line_id
FROM mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mol
, wsh_inv_delivery_details_v wdd
WHERE mmtt.move_order_line_id = mol.line_id
AND mol.header_id = p_mo_header_id
AND wdd.move_order_line_id = mol.line_id
AND mmtt.parent_line_id IS NULL; -- exclude bulk pick child tasks
SELECT 'x' INTO l_dummy
FROM dual
WHERE EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp mmtt1
WHERE mmtt1.move_order_header_id = p_mo_header_id
AND mmtt1.parent_line_id IS NULL
AND mmtt1.pick_slip_number IS NULL
);
SELECT grouping_rule_id, organization_id
INTO l_grouping_rule_id, l_organization_id
FROM mtl_txn_request_headers
WHERE header_id = p_mo_header_id;
SELECT pick_grouping_rule_id
INTO l_grouping_rule_id
FROM wsh_shipping_parameters
WHERE organization_id = p_organization_id;
SELECT wsh_pick_slip_numbers_s.NEXTVAL
INTO l_pick_slip_number
FROM DUAL;
UPDATE mtl_material_transactions_temp
SET pick_slip_number = l_pick_slip_number
, transaction_source_id = tbl_sales_order_id(jj)
, trx_source_line_id = tbl_oe_line_id(jj)
, demand_source_header_id = tbl_sales_order_id(jj)
, demand_source_line = tbl_oe_line_id(jj)
WHERE transaction_temp_id = tbl_transaction_temp_id(jj);
UPDATE mtl_material_transactions_temp
SET pick_slip_number = wsh_pick_slip_numbers_s.NEXTVAL
, transaction_source_id = tbl_sales_order_id(jj)
, trx_source_line_id = tbl_oe_line_id(jj)
, demand_source_header_id = tbl_sales_order_id(jj)
, demand_source_line = tbl_oe_line_id(jj)
WHERE transaction_temp_id = tbl_transaction_temp_id(jj);
SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_batch_id = p_batch_id;
DELETE wms_pr_workers
WHERE cartonization_id = t_carton_id(ii);
t_carton_id.DELETE;
DELETE wms_pr_workers
WHERE cartonization_id = t_carton_id(jj);
t_carton_id.DELETE;
t_cspk_temp_id.DELETE;
UPDATE mtl_material_transactions_temp
SET transaction_batch_id = NULL
WHERE transaction_batch_id = t_txn_batch_id(kk);
DELETE FROM wms_pr_workers
WHERE transaction_batch_id = t_txn_batch_id(mm);
DELETE wms_pr_workers
WHERE cartonization_id = t_carton_id(jj);
UPDATE mtl_material_transactions_temp
SET transaction_batch_id = NULL
WHERE transaction_batch_id = t_txn_batch_id(kk);
DELETE FROM wms_pr_workers
WHERE transaction_batch_id = t_txn_batch_id(mm);
DELETE wms_pr_workers
WHERE cartonization_id = t_carton_id(jj);
UPDATE mtl_material_transactions_temp
SET transaction_batch_id = NULL
WHERE transaction_batch_id = t_txn_batch_id(kk);
DELETE FROM wms_pr_workers
WHERE transaction_batch_id = t_txn_batch_id(mm);