[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT lot_number, from_serial_number, to_serial_number
FROM oe_lot_serial_numbers
WHERE line_id = p_line_id;
select count(*)
into l_temp_count
from mtl_rma_serial_temp
where line_id = p_rma_line_id;
/* Commented the below code and added the new select statement to get
* correct value in l_orig_line_id i.e to get always line_id of parent RMA line
* from which subsequent lines got split. For bug 5144099 */
/*
SELECT split_from_line_id, line_set_id
INTO l_split_from_line_id, l_line_set_id
FROM oe_order_lines_all
WHERE line_id = p_rma_line_id;
SELECT min(line_id)
INTO l_orig_line_id
FROM oe_order_lines_all
START WITH line_id = p_rma_line_id
CONNECT BY PRIOR split_from_line_id = line_id;
SELECT count(*)
INTO l_count
FROM oe_lot_serial_numbers
WHERE line_id = l_orig_line_id;
--one at a time and insert into temp table.
--Bug#4411411: l_number_part will be null if the serial has no numeric part.
-- Need to do NVL for the length(l_number_part). Prefix will
-- be null when serial has ONLY numbers or if serial ends in alphabet.
-- Need to do NVL for l_prefix for this case.
WHILE (l_counter <= l_qty) LOOP
l_padded_length := l_length - nvl(length(l_number_part),0);
--Insert serial into temp table
INSERT INTO mtl_rma_serial_temp
(organization_id,
inventory_item_id,
lot_number,
serial_number,
line_id) VALUES
(p_org_id,
p_item_id,
l_lot_number,
l_serial_number,
p_rma_line_id);