[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO mtl_material_transactions_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
, secondary_transaction_quantity -- BUG 8197506
, secondary_uom_code -- bug 8197506
, 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_transaction_id
, overcompletion_primary_qty
, overcompletion_transaction_qty
, 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
, common_bom_seq_id
, common_routing_seq_id
, cost_type_id
, org_cost_group_id
, move_order_line_id
, task_group_id
, pick_slip_number
, reservation_id
, transaction_status
, transfer_cost_group_id
, lpn_id
, transfer_lpn_id
, content_lpn_id
, cartonization_id
, standard_operation_id
, wms_task_type
, task_priority
, container_item_id
, operation_plan_id
, parent_line_id
, serial_allocated_flag
, move_order_header_id
, wms_task_status -- Bug# 4185621
)
(SELECT transaction_header_id
, p_new_child_temp_id
, source_code
, source_line_id
, transaction_mode
, lock_flag
, SYSDATE
, last_updated_by
, SYSDATE
, 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
, p_split_pri_qty
, p_split_pri_qty -- only the primary UOM is used
, p_split_sec_qty -- BUG 8197506
, secondary_uom_code -- BUG 8197506
, item_primary_uom_code -- 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
, 'Y' --posting_flag Bug#4185621: make sure new child mmtt is posting
, 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_transaction_id
, overcompletion_primary_qty
, overcompletion_transaction_qty
, 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
, common_bom_seq_id
, common_routing_seq_id
, cost_type_id
, org_cost_group_id
, move_order_line_id
, task_group_id
, pick_slip_number
, reservation_id
, transaction_status
, transfer_cost_group_id
, lpn_id
, transfer_lpn_id
, content_lpn_id
, cartonization_id
, standard_operation_id
, wms_task_type
, task_priority
, container_item_id
, operation_plan_id
, parent_line_id
, serial_allocated_flag
, move_order_line_id
, l_g_task_loaded -- Bug# 4185621: loaded status
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_child_temp_id);
update mtl_material_transactions_temp
set primary_quantity = primary_quantity-p_split_pri_qty, -- UOM
secondary_transaction_quantity = decode(secondary_transaction_quantity,NULL,NULL,secondary_transaction_quantity-p_split_sec_qty), --bug 8197506
transaction_quantity = transaction_quantity-p_split_pri_qty
where transaction_temp_id = p_child_temp_id;
INSERT INTO mtl_transaction_lots_temp
(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, transaction_quantity
, primary_quantity
, secondary_quantity -- BUG 8197506
, secondary_unit_of_measure -- bug 8197506
, lot_number
, lot_expiration_date
, ERROR_CODE
, serial_transaction_temp_id
, group_header_id
, put_away_rule_id
, pick_rule_id
, description
, vendor_id
, supplier_lot_number
, territory_code
, --country_of_origin,
origination_date
, date_code
, grade_code
, change_date
, maturity_date
, status_id
, retest_date
, age
, item_size
, color
, volume
, volume_uom
, place_of_origin
, --kill_date,
best_by_date
, LENGTH
, length_uom
, recycled_content
, thickness
, thickness_uom
, width
, width_uom
, curl_wrinkle_fold
, lot_attribute_category
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, vendor_name
)
(SELECT
p_new_child_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, p_split_pri_qty
, p_split_pri_qty
, p_split_sec_qty -- BUG 8197506
, secondary_unit_of_measure -- BUG 8197506
, lot_number
, lot_expiration_date
, ERROR_CODE
, p_new_serial_temp_id
, group_header_id
, put_away_rule_id
, pick_rule_id
, description
, vendor_id
, supplier_lot_number
, territory_code
, --country_of_origin,
origination_date
, date_code
, grade_code
, change_date
, maturity_date
, status_id
, retest_date
, age
, item_size
, color
, volume
, volume_uom
, place_of_origin
, --kill_date,
best_by_date
, LENGTH
, length_uom
, recycled_content
, thickness
, thickness_uom
, width
, width_uom
, curl_wrinkle_fold
, lot_attribute_category
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, vendor_name
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_child_temp_id
AND lot_number = p_child_lot_number); --v1 Bug 3902766
update mtl_transaction_lots_temp
set primary_quantity = primary_quantity-p_split_pri_qty, -- UOM
secondary_quantity = decode(secondary_quantity,NULL,NULL,secondary_quantity-p_split_sec_qty), --bug 8197506
transaction_quantity = transaction_quantity-p_split_pri_qty -- UOM
where transaction_temp_id = p_child_temp_id
and lot_number = p_child_lot_number; --v1 Bug 3902766
PROCEDURE update_child(p_child_temp_id NUMBER,
p_parent_temp_id NUMBER,
p_new_txn_hdr_id NUMBER) IS
l_parent_uom VARCHAR2(10);
l_api_name VARCHAR2(32):= 'update_child';
select transfer_lpn_id,nvl(lpn_id,content_lpn_id),subinventory_code,locator_id, transaction_uom
into l_transfer_lpn_id,l_lpn_id,l_parent_sub_code,l_parent_loc_id,l_parent_uom
from mtl_material_transactions_temp
where transaction_temp_id = p_parent_temp_id;
mydebug('update child line '||p_child_temp_id||' with parent info and new header id '||p_new_txn_hdr_id,l_api_name);
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.transaction_header_id = p_new_txn_hdr_id
, mmtt.transfer_lpn_id = l_transfer_lpn_id
, mmtt.lpn_id = l_lpn_id
, mmtt.parent_line_id = p_parent_temp_id
, mmtt.subinventory_code = l_parent_sub_code
, mmtt.locator_id = l_parent_loc_id
, mmtt.transaction_uom = mmtt.item_primary_uom_code
, mmtt.transaction_quantity = mmtt.primary_quantity
, mmtt.last_update_date = SYSDATE
, mmtt.last_updated_by = FND_GLOBAL.USER_ID
, mmtt.posting_flag = 'Y' -- Bug# 4185621: make sure child line mmtt is now posting
, mmtt.wms_task_status = l_g_task_loaded -- Bug# 4185621: make sure child line mmtt task status is loaded
WHERE mmtt.transaction_temp_id = p_child_temp_id;
mydebug('update all child lines with parent info and new header id '||p_new_txn_hdr_id,l_api_name);
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.transaction_header_id = p_new_txn_hdr_id
, mmtt.transfer_lpn_id = l_transfer_lpn_id
, mmtt.lpn_id = l_lpn_id
, mmtt.parent_line_id = p_parent_temp_id
, mmtt.subinventory_code = l_parent_sub_code
, mmtt.locator_id = l_parent_loc_id
, mmtt.transaction_uom = mmtt.item_primary_uom_code
, mmtt.transaction_quantity = mmtt.primary_quantity
, mmtt.last_update_date = SYSDATE
, mmtt.last_updated_by = FND_GLOBAL.USER_ID
, mmtt.posting_flag = 'Y' -- Bug# 4185621: make sure child line mmtt is now posting
, mmtt.wms_task_status = l_g_task_loaded -- Bug# 4185621: make sure child line mmtt task status is loaded
WHERE mmtt.transaction_temp_id <> p_parent_temp_id
and mmtt.parent_line_id = p_parent_temp_id;
END update_child;
select sum(transaction_quantity) transaction_quantity,
sum(primary_quantity) primary_quantity,
sum(secondary_quantity) secondary_quantity, --12747184
lot_number
from mtl_allocations_gtmp
where transaction_temp_id = p_temp_id
group by lot_number;
select *
into l_mmtt_rec
from mtl_material_transactions_temp
where transaction_temp_id <> p_from_temp_id
and parent_line_id = p_from_temp_id
and rownum <2;
select mtl_material_transactions_s.NEXTVAL
into l_new_temp_id
from dual;
wms_task_dispatch_engine.insert_mmtt(l_mmtt_rec);
select *
into l_mtlt_rec
from mtl_transaction_lots_temp
where transaction_temp_id = p_from_temp_id
and lot_number = lot_line.lot_number;
inv_rcv_common_apis.insert_mtlt(l_mtlt_rec);
PROCEDURE insert_mtlt (
p_new_temp_id IN NUMBER
, p_serial_temp_id IN NUMBER := NULL
, p_pri_att_qty IN NUMBER
, p_trx_att_qty IN NUMBER
, p_secondary_trx_qty IN NUMBER
, p_lot_number IN VARCHAR2
, p_item_id IN NUMBER
, p_organization_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name VARCHAR2(30) := 'bulk_pick';
mydebug('FlexibleLotAlloc: Inside insert mtlt',l_api_name );
INSERT INTO mtl_transaction_lots_temp
(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, transaction_quantity
, primary_quantity
, lot_number
, lot_expiration_date
, serial_transaction_temp_id
, description
, vendor_name
, supplier_lot_number
, origination_date
, date_code
, grade_code
, change_date
, maturity_date
, retest_date
, age
, item_size
, color
, volume
, volume_uom
, place_of_origin
, best_by_date
, LENGTH
, length_uom
, recycled_content
, thickness
, thickness_uom
, width
, width_uom
, curl_wrinkle_fold
, lot_attribute_category
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, vendor_id
, territory_code
, secondary_quantity
)
(SELECT p_new_temp_id
, sysdate
, -9999
, sysdate
, -9999
, p_trx_att_qty
, p_pri_att_qty
, p_lot_number
, mln.expiration_date
, p_serial_temp_id
, mln.description
, mln.vendor_name
, mln.supplier_lot_number
, mln.origination_date
, mln.date_code
, mln.grade_code
, mln.change_date
, mln.maturity_date
, mln.retest_date
, mln.age
, mln.item_size
, mln.color
, mln.volume
, mln.volume_uom
, mln.place_of_origin
, mln.best_by_date
, mln.LENGTH
, mln.length_uom
, mln.recycled_content
, mln.thickness
, mln.thickness_uom
, mln.width
, mln.width_uom
, mln.curl_wrinkle_fold
, mln.lot_attribute_category
, mln.c_attribute1
, mln.c_attribute2
, mln.c_attribute3
, mln.c_attribute4
, mln.c_attribute5
, mln.c_attribute6
, mln.c_attribute7
, mln.c_attribute8
, mln.c_attribute9
, mln.c_attribute10
, mln.c_attribute11
, mln.c_attribute12
, mln.c_attribute13
, mln.c_attribute14
, mln.c_attribute15
, mln.c_attribute16
, mln.c_attribute17
, mln.c_attribute18
, mln.c_attribute19
, mln.c_attribute20
, mln.d_attribute1
, mln.d_attribute2
, mln.d_attribute3
, mln.d_attribute4
, mln.d_attribute5
, mln.d_attribute6
, mln.d_attribute7
, mln.d_attribute8
, mln.d_attribute9
, mln.d_attribute10
, mln.n_attribute1
, mln.n_attribute2
, mln.n_attribute3
, mln.n_attribute4
, mln.n_attribute5
, mln.n_attribute6
, mln.n_attribute7
, mln.n_attribute8
, mln.n_attribute9
, mln.n_attribute10
, mln.vendor_id
, mln.territory_code
, p_secondary_trx_qty
FROM mtl_lot_numbers mln
WHERE mln.lot_number = p_lot_number
AND mln.inventory_item_id = p_item_id
AND mln.organization_id = p_organization_id);
mydebug(' Insert mtlt returns exception' ,l_api_name);
END insert_mtlt;
SELECT mmtt.transaction_temp_id
, mmtt.inventory_item_id
, mmtt.subinventory_code
, mmtt.locator_id
, NVL(mmtt.content_lpn_id, mmtt.lpn_id)
, mmtt.transfer_lpn_id
, mmtt.transaction_uom
, mmtt.transaction_quantity
, mmtt.primary_quantity
, mmtt.secondary_transaction_quantity --bug 8197506
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = p_txn_hdr_id
AND mmtt.organization_id = p_org_id
AND mmtt.transaction_quantity > 0
AND mmtt.parent_line_id = mmtt.transaction_temp_id -- make sure it is only parent lines
ORDER BY mmtt.transaction_quantity DESC;
SELECT mmtt.transaction_temp_id
, mmtt.inventory_item_id
, mmtt.subinventory_code
, mmtt.locator_id
, NVL(mmtt.content_lpn_id, mmtt.lpn_id)
, mmtt.transfer_lpn_id
, mmtt.transaction_uom
, mmtt.transaction_quantity
, mmtt.primary_quantity
, mmtt.secondary_transaction_quantity --bug 8197506
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = p_txn_hdr_id
AND mmtt.organization_id = p_org_id
AND mmtt.transaction_quantity > 0
AND mmtt.parent_line_id <> p_temp_id
AND mmtt.parent_line_id = mmtt.transaction_temp_id -- make sure it is only parent lines
ORDER BY mmtt.transaction_quantity DESC;
SELECT mmtt.transaction_temp_id
, mmtt.transfer_lpn_id
, mtlt.lot_number
, mtlt.transaction_quantity lot_trx_qty
, mtlt.primary_quantity lot_primary_qty
, mtlt.secondary_quantity lot_sec_qty --bug 8197506
FROM mtl_material_transactions_temp mmtt,mtl_transaction_lots_temp mtlt
WHERE mmtt.transaction_header_id = p_txn_hdr_id
AND mmtt.organization_id = p_org_id
AND mmtt.transaction_quantity > 0
AND mmtt.parent_line_id = mmtt.transaction_temp_id
AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
ORDER BY mmtt.transaction_quantity DESC;
SELECT mmtt.transaction_temp_id
, mmtt.transfer_lpn_id
, mtlt.lot_number
, mtlt.transaction_quantity lot_trx_qty
, mtlt.primary_quantity lot_primary_qty
, mtlt.secondary_quantity lot_sec_qty --bug 8197506
FROM mtl_material_transactions_temp mmtt,mtl_transaction_lots_temp mtlt
WHERE mmtt.transaction_header_id = p_txn_hdr_id
AND mmtt.organization_id = p_org_id
AND mmtt.transaction_quantity > 0
AND mmtt.parent_line_id <> p_temp_id
AND mmtt.parent_line_id = mmtt.transaction_temp_id
AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
ORDER BY mmtt.transaction_quantity DESC;
SELECT mmtt.transaction_temp_id
, mmtt.transaction_uom
, mmtt.transaction_quantity
, mmtt.primary_quantity
, mmtt.secondary_transaction_quantity --bug 8197506
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.parent_line_id = p_temp_id
AND mmtt.parent_line_id <> mmtt.transaction_temp_id -- exclude the parent line
AND mmtt.organization_id = p_org_id
ORDER BY mmtt.transaction_quantity DESC;
SELECT mmtt.transaction_temp_id
, mmtt.transaction_uom
, mmtt.transaction_quantity
, mmtt.primary_quantity
, mmtt.secondary_transaction_quantity --bug 8197506
FROM mtl_material_transactions_temp mmtt,mtl_txn_request_lines mol,
wsh_delivery_details wdd,wsh_delivery_assignments_v wda
WHERE mmtt.parent_line_id = p_temp_id
AND mmtt.parent_line_id <> mmtt.transaction_temp_id -- exclude the parent line
AND mmtt.organization_id = p_org_id
AND mol.line_id = mmtt.move_order_line_id
AND mol.line_id = wdd.move_order_line_id
AND wdd.released_status = 'S' --Bug#6848907
AND wda.delivery_detail_id = wdd.delivery_detail_id
ORDER BY nvl(wda.delivery_id,mol.carton_grouping_id), mmtt.transaction_quantity DESC;
SELECT mmtt.transaction_temp_id
, mmtt.transaction_uom
, mmtt.transaction_quantity
, mmtt.primary_quantity
, mmtt.secondary_transaction_quantity --bug 8197506
FROM mtl_material_transactions_temp mmtt,mtl_txn_request_lines mol,
wsh_delivery_details wdd,wsh_delivery_assignments_v wda,
oe_order_lines_all ol,mtl_txn_request_headers moh
WHERE mmtt.parent_line_id = p_temp_id
AND mmtt.parent_line_id <> mmtt.transaction_temp_id -- exclude the parent line
AND mmtt.organization_id = p_org_id
AND mol.line_id = mmtt.move_order_line_id
AND mol.line_id = wdd.move_order_line_id
AND wdd.released_status = 'S' --Bug#6848907
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND ol.line_id = wdd.source_line_id
AND mol.header_id = moh.header_id
ORDER BY ol. SCHEDULE_SHIP_DATE,
nvl(wda.delivery_id,mol.carton_grouping_id),
moh.creation_date, -- mmtt creation date is changed for splitting, so moh date will be better
mmtt.transaction_quantity DESC;
SELECT
mtlt.lot_number
, mtlt.transaction_quantity
, mtlt.primary_quantity
, mtlt.secondary_quantity -- bug 8197506
FROM mtl_transaction_lots_temp mtlt
WHERE
mtlt.transaction_temp_id = p_child_transaction_temp_id;
SELECT mag.transaction_temp_id,
mag.primary_quantity,
mag.transaction_quantity,
mag.secondary_quantity --bug 8197506
FROM mtl_allocations_gtmp mag
WHERE lot_number = p_lot_number
ORDER BY search_sequence;
SELECT sum(transaction_quantity) transaction_quantity,
sum(primary_quantity) primary_quantity,
sum(secondary_quantity) secondary_quantity , --12747184
transaction_temp_id
from mtl_allocations_gtmp
group by transaction_temp_id;
SELECT mmtt.transaction_temp_id,
mmtt.transaction_quantity,
mmtt.primary_quantity,
mmtt.parent_line_id,
mmtt.inventory_item_id,
mmtt.secondary_transaction_quantity
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id <> mmtt.parent_line_id
AND mmtt.parent_line_id = p_temp_id
AND mmtt.transfer_lpn_id IS NULL
ORDER BY mmtt.transaction_quantity;
SELECT mmtt.transaction_temp_id,
mag.transaction_quantity,
mag.primary_quantity,
mmtt.lpn_id,
mmtt.transfer_lpn_id,
mmtt.content_lpn_id,
mmtt.parent_line_id,
mag.lot_number,
mag.secondary_quantity,
mmtt.inventory_item_id
FROM mtl_material_transactions_temp mmtt , mtl_allocations_gtmp mag
WHERE mmtt.transaction_temp_id = mag.transaction_temp_id
ORDER BY mag.transaction_quantity, mag.primary_quantity desc;
SELECT 'Y'
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id IN (SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.parent_line_id = p_temp_id
AND mmtt.parent_line_id <> mmtt.transaction_temp_id);
SELECT mtl_material_transactions_s.NEXTVAL
INTO x_new_txn_hdr_id
FROM DUAL;
SELECT transaction_action_id
INTO l_transaction_action_id
FROM mtl_material_transactions_temp mmtt
WHERE
mmtt.transaction_temp_id = p_temp_id;
SELECT transaction_action_id,transaction_temp_id
INTO l_transaction_action_id,l_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE transaction_header_id = p_txn_hdr_id
and rownum<2;
update_child(null,l_temp_id,x_new_txn_hdr_id);
UPDATE mtl_material_transactions_temp
SET posting_flag = 'N'
WHERE transaction_temp_id = l_temp_id
AND parent_line_id = transaction_temp_id;
delete mtl_allocations_gtmp;
mydebug('inserting the records to the tmp table',l_api_name);
mydebug('Inserting from c_parent_mmtt_shorpick_lots v1',l_api_name);
INSERT
INTO mtl_allocations_gtmp
(
TRANSACTION_TEMP_ID ,
TRANSFER_LPN_ID ,
LOT_NUMBER ,
TRANSACTION_QUANTITY ,
PRIMARY_QUANTITY ,
SEARCH_SEQUENCE ,
SECONDARY_QUANTITY --bug 8197506
)
VALUES
(
c_parent_lines_rec.transaction_temp_id ,
c_parent_lines_rec.transfer_lpn_id ,
c_parent_lines_rec.lot_number ,
c_parent_lines_rec.lot_trx_qty ,
c_parent_lines_rec.lot_primary_qty ,
l_search_sequence ,
c_parent_lines_rec.lot_sec_qty
); --bug 8197506
UPDATE mtl_material_transactions_temp
SET posting_flag = 'N'
WHERE transaction_temp_id = c_parent_lines_rec.transaction_temp_id
AND parent_line_id = transaction_temp_id;
INSERT
INTO mtl_allocations_gtmp
(
TRANSACTION_TEMP_ID ,
TRANSFER_LPN_ID ,
LOT_NUMBER ,
TRANSACTION_QUANTITY ,
PRIMARY_QUANTITY ,
SEARCH_SEQUENCE ,
SECONDARY_QUANTITY --bug 8197506
)
VALUES
(
c_parent_lines_rec.transaction_temp_id ,
c_parent_lines_rec.transfer_lpn_id ,
c_parent_lines_rec.lot_number ,
c_parent_lines_rec.lot_trx_qty ,
c_parent_lines_rec.lot_primary_qty ,
l_search_sequence ,
c_parent_lines_rec.lot_sec_qty --bug 8197506
);
UPDATE mtl_material_transactions_temp
SET posting_flag = 'N'
WHERE transaction_temp_id = c_parent_lines_rec.transaction_temp_id
AND parent_line_id = transaction_temp_id;
||' parent lines are inserted',l_api_name);
SELECT child_transaction_temp_id
INTO l_child_txn_temp_id
FROM mtl_allocations_gtmp
WHERE transaction_temp_id = c_lot_parents_rec.transaction_temp_id
AND child_transaction_temp_id IS NOT NULL
AND rownum = 1;
UPDATE mtl_material_transactions_temp
SET primary_quantity = primary_quantity +l_process_qty ,
secondary_transaction_quantity = DECODE(secondary_transaction_quantity,NULL,NULL,secondary_transaction_quantity + l_process_sec_qty), --bug 8197506
transaction_quantity = transaction_quantity+l_process_qty ,
posting_flag = 'Y' , -- Bug# 4185621: change child line posting flag back to 'Y'
wms_task_status = l_g_task_loaded -- Bug# 4185621: make sure child line task status is loaded
WHERE transaction_temp_id = l_child_txn_temp_id;
UPDATE mtl_material_transactions_temp
SET primary_quantity = primary_quantity - l_process_qty ,
secondary_transaction_quantity = DECODE(secondary_transaction_quantity,NULL,NULL,secondary_transaction_quantity - l_process_sec_qty),
transaction_quantity = transaction_quantity - l_process_qty
WHERE transaction_temp_id = c_mmtt_line.transaction_temp_id;
mydebug('updated the chld line with the new processed qty',l_api_name);
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_temp_id
FROM dual;
mydebug('update the processed qty for the new child line',l_api_name);
UPDATE mtl_allocations_gtmp
SET child_transaction_temp_id = l_new_temp_id
WHERE transaction_temp_id = c_lot_parents_rec.transaction_temp_id;
mydebug('update the lot qty for temp id:'
||c_mmtt_line.transaction_temp_id, l_api_name);
UPDATE mtl_transaction_lots_temp
SET transaction_Temp_id = l_child_txn_temp_id
WHERE transaction_temp_id = c_mmtt_line.transaction_temp_id
AND lot_number = c_child_lots_rec.lot_number;
update_child(l_child_txn_temp_id,c_lot_parents_rec.transaction_temp_id, x_new_txn_hdr_id);
UPDATE mtl_allocations_gtmp
SET primary_quantity = primary_quantity -l_process_qty, -- only primary UOM will be used
secondary_quantity = DECODE(secondary_quantity,NULL,NULL,secondary_quantity-l_process_sec_qty) --bug 8197506
WHERE transaction_temp_id = c_lot_parents_rec.transaction_temp_id
AND lot_number = c_child_lots_rec.lot_number;
DELETE mtl_allocations_gtmp
WHERE transaction_temp_id = c_lot_parents_rec.transaction_temp_id
AND primary_quantity = 0;
DELETE
FROM mtl_material_transactions_temp mmtt
WHERE transaction_temp_id = c_mmtt_line.transaction_temp_id
AND primary_quantity = 0;
UPDATE mtl_allocations_gtmp
SET child_transaction_temp_id = NULL;
-- Insert the mtlt and update mmtt for child
IF l_debug = 1 THEN
mydebug('mmtt qty = child ' , l_api_name);
insert_mtlt (
p_new_temp_id => rec_child_mmtts.transaction_temp_id
, p_serial_temp_id => NULL
, p_pri_att_qty => rec_child_mmtts.primary_quantity
, p_trx_att_qty => rec_child_mmtts.transaction_quantity
, p_secondary_trx_qty => rec_child_mmtts.secondary_transaction_quantity
, p_lot_number => rec_parent_mmtt_line.lot_number
, p_item_id => rec_parent_mmtt_line.inventory_item_id
, p_organization_id => p_org_id
, x_return_status => l_ret_sts) ;
mydebug('Inserting into MTLT - l_ret_status-' || l_ret_sts,l_api_name);
mydebug('Update the Child MMTT ' , l_api_name);
mydebug('Inserted the Child MTLT ' , l_api_name);
mydebug('Update the Child MMTT with LPN_ID ' ||rec_parent_mmtt_line.lpn_id, l_api_name);
mydebug('Update the Child MMTT with Xfer LPN_ID ' ||rec_parent_mmtt_line.transfer_lpn_id, l_api_name);
mydebug('Update the Child MMTT with content LPN_ID' ||rec_parent_mmtt_line.content_lpn_id, l_api_name);
mydebug('Update the Child MMTT having tran_temp_id' ||rec_child_mmtts.transaction_temp_id, l_api_name);
update_child(rec_child_mmtts.transaction_temp_id,
rec_parent_mmtt_line.transaction_temp_id,
x_new_txn_hdr_id
);
mydebug('Since the full Child is consumed, delete from the GTEMP ' , l_api_name);
DELETE FROM mtl_allocations_gtmp
WHERE transaction_temp_id = rec_parent_mmtt_line.transaction_temp_id
AND lot_number = rec_parent_mmtt_line.lot_number;
mydebug('Insert the Child MTLT ' , l_api_name);
insert_mtlt (
p_new_temp_id => rec_child_mmtts.transaction_temp_id
, p_serial_temp_id => NULL
, p_pri_att_qty => rec_child_mmtts.primary_quantity
, p_trx_att_qty => rec_child_mmtts.transaction_quantity
, p_secondary_trx_qty => rec_child_mmtts.secondary_transaction_quantity
, p_lot_number => rec_parent_mmtt_line.lot_number
, p_item_id => rec_parent_mmtt_line.inventory_item_id
, p_organization_id => p_org_id
, x_return_status => l_ret_sts) ;
mydebug('Inserting into MTLT - l_ret_status-' || l_ret_sts,l_api_name);
mydebug('Inserted the Child MMTT ' , l_api_name);
update_child(rec_child_mmtts.transaction_temp_id,
rec_parent_mmtt_line.transaction_temp_id,
x_new_txn_hdr_id
);
mydebug('Update the GTMP TABLE, decrement with qty ' ||rec_child_mmtts.transaction_quantity, l_api_name);
UPDATE mtl_allocations_gtmp
SET transaction_quantity = transaction_quantity - rec_child_mmtts.transaction_quantity,
primary_quantity = primary_quantity - rec_child_mmtts.primary_quantity,
secondary_quantity = secondary_quantity - rec_child_mmtts.secondary_transaction_quantity
WHERE transaction_temp_id = rec_parent_mmtt_line.transaction_temp_id
AND lot_number = rec_parent_mmtt_line.lot_number;
-- Split the Child mmtt and insert mtlt.
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_temp_id
FROM dual;
mydebug('Inserting the mtlt line with mmtts temp id '|| rec_child_mmtts.transaction_temp_id,l_api_name);
insert_mtlt (
p_new_temp_id => rec_child_mmtts.transaction_temp_id
, p_serial_temp_id => NULL
, p_pri_att_qty => l_primary_qty
, p_trx_att_qty => l_process_qty
, p_secondary_trx_qty => l_process_sec_qty
, p_lot_number => rec_parent_mmtt_line.lot_number
, p_item_id => rec_parent_mmtt_line.inventory_item_id
, p_organization_id => p_org_id
, x_return_status => l_ret_sts) ;
mydebug('Inserting into MTLT - l_ret_status-' || l_ret_sts,l_api_name);
mydebug('Done Inserting MTLT with temp id '|| rec_child_mmtts.transaction_temp_id,l_api_name);
update_child(rec_child_mmtts.transaction_temp_id,
rec_parent_mmtt_line.transaction_temp_id,
x_new_txn_hdr_id
);
mydebug('Update the GTMP TABLE, decrement with qty ' ||l_process_qty, l_api_name);
UPDATE mtl_allocations_gtmp
SET transaction_quantity = transaction_quantity - l_process_qty,
primary_quantity = primary_quantity - l_primary_qty,
secondary_quantity = secondary_quantity - l_process_sec_qty
WHERE transaction_temp_id = rec_parent_mmtt_line.transaction_temp_id
AND lot_number = rec_parent_mmtt_line.lot_number;
DELETE FROM mtl_allocations_gtmp
WHERE transaction_quantity = 0
AND transaction_temp_id = rec_parent_mmtt_line.transaction_temp_id
AND lot_number = rec_parent_mmtt_line.lot_number;
UPDATE mtl_material_transactions_temp
SET posting_flag = 'N'
WHERE transaction_temp_id = l_parent_txn_temp_id
AND parent_line_id = transaction_temp_id;
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.transaction_header_id = x_new_txn_hdr_id
, mmtt.transfer_lpn_id = l_transfer_lpn_id
, mmtt.lpn_id = l_lpn_id
, mmtt.parent_line_id = l_parent_txn_temp_id
, mmtt.subinventory_code = l_parent_sub_code
, mmtt.locator_id = l_parent_loc_id
, mmtt.transaction_uom = mmtt.item_primary_uom_code
, mmtt.transaction_quantity = mmtt.primary_quantity
, mmtt.last_update_date = SYSDATE
, mmtt.last_updated_by = p_user_id
, mmtt.posting_flag = 'Y' -- Bug# 4185621: change child line posting flag back to 'Y'
, mmtt.wms_task_status = l_g_task_loaded -- Bug# 4185621: make sure child line task status is loaded
WHERE mmtt.transaction_temp_id = l_child_txn_temp_id;
select mtl_material_transactions_s.NEXTVAL
into l_new_temp_id
from dual;
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.transaction_header_id = x_new_txn_hdr_id
, mmtt.primary_quantity = l_parent_pri_qty
, mmtt.secondary_transaction_quantity = l_parent_sec_qty --bug 8197506
, mmtt.parent_line_id = l_parent_txn_temp_id
, mmtt.transfer_lpn_id = l_transfer_lpn_id
, mmtt.lpn_id = l_lpn_id
, mmtt.subinventory_code = l_parent_sub_code
, mmtt.locator_id = l_parent_loc_id
, mmtt.transaction_uom = mmtt.item_primary_uom_code
, mmtt.transaction_quantity = mmtt.primary_quantity
, mmtt.last_update_date = SYSDATE
, mmtt.last_updated_by = p_user_id
, mmtt.posting_flag = 'Y' -- Bug# 4185621: change child line posting flag back to 'Y'
, mmtt.wms_task_status = l_g_task_loaded -- Bug# 4185621: make sure task status is loaded
WHERE mmtt.transaction_temp_id = l_child_txn_temp_id;