[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
, 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
, 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
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
, 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
, 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
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, 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);
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
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
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
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
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
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
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
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
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
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, transaction_temp_id
from mtl_allocations_gtmp
group by 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;
IF (l_debug = 1) THEN mydebug('inserting the records to the tmp table',l_api_name); END IF;
IF (l_debug = 1) THEN mydebug('Inserting from c_parent_mmtt_shorpick_lots v1',l_api_name); END IF;
insert into mtl_allocations_gtmp
( TRANSACTION_TEMP_ID
, TRANSFER_LPN_ID
, LOT_NUMBER
, TRANSACTION_QUANTITY
, PRIMARY_QUANTITY
, SEARCH_SEQUENCE)
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);
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)
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);
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;
IF (l_debug = 1) THEN mydebug( l_search_sequence||' parent lines are inserted',l_api_name); END IF;
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
, 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;
IF (l_debug = 1) THEN 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;
IF (l_debug = 1) THEN 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;
IF (l_debug = 1) THEN 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
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;
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.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;