The following lines contain the word 'select', 'insert', 'update' or 'delete':
, p_update_reservation => fnd_api.g_false
, success => l_success
);
p_update_reservation VARCHAR2 DEFAULT fnd_api.g_true, /*** {{ R12 Enhanced reservations code changes }} ***/
success IN OUT NOCOPY NUMBER)
IS
l_debug NUMBER := 0;
l_update_count NUMBER:= 0; /*** {{ R12 Enhanced reservations code changes ***/
SELECT group_mark_id
FROM mtl_serial_numbers
WHERE current_organization_id = org_id
AND inventory_item_id = item_id
AND serial_number BETWEEN from_serial_number
AND to_serial_number
AND LENGTH(serial_number) = Length(from_serial_number)
FOR UPDATE OF group_mark_id NOWAIT;
SELECT group_mark_id
FROM mtl_serial_numbers
WHERE current_organization_id = org_id
AND inventory_item_id = item_id
AND serial_number = from_serial_number
FOR UPDATE OF group_mark_id NOWAIT;
SELECT COUNT(group_mark_id)
INTO marked_numbers_found
FROM mtl_serial_numbers
WHERE inventory_item_id = item_id
AND group_mark_id > 0
AND serial_number between from_serial_number
AND to_serial_number
AND LENGTH(serial_number) = LENGTH(from_serial_number) ;
-- Delete the condition of current_organization_id = org_id
-- Here is the scenario why we should not consider the current_organization_id = org_id in the
-- where clause.
-- Item Current Organization Serial_Status Serial Number Serial Type
-- ABC M1 4 (Issued from Stores) S1 to S10 Unique in Org
-- ABC M2 Unique within Item
-- Since Serial Number S1 to S10 has status 4 (issued from Stores), user should be able
-- to chose S1 to S10 for receipt transaction to organization M2, even though the current
-- organization of serial S1 to S10 is M1.
*/
UPDATE mtl_serial_numbers
SET lot_line_mark_id = lot_temp_id,
line_mark_id = temp_id,
group_mark_id = l_hdr_id,
reservation_id = nvl(p_reservation_id, reservation_id) /*** {{ R12 Enhanced reservations code changes ***/
WHERE inventory_item_id = item_id
AND serial_number between from_serial_number
AND to_serial_number
AND LENGTH(serial_number) = LENGTH(from_serial_number) ;
l_update_count := SQL%ROWCOUNT; /*** {{ R12 Enhanced reservations code changes ***/
SELECT COUNT(group_mark_id)
INTO marked_numbers_found
FROM mtl_serial_numbers
WHERE inventory_item_id = item_id
AND group_mark_id > 0
AND serial_number = from_serial_number ;
-- Delete the condition of current_organization_id = org_id
-- Here is the scenario why we should not consider the current_organization_id = org_id in the
-- where clause.
-- Item Current Organization Serial_Status Serial Number Serial Type
-- ABC M1 4 (Issued from Stores) S1 to S10 Unique in Org
-- ABC M2 Unique within Item
-- Since Serial Number S1 to S10 has status 4 (issued from Stores), user should be able
-- to chose S1 to S10 for receipt transaction to organization M2, even though the current
-- organization of serial S1 to S10 is M1.
*/
UPDATE mtl_serial_numbers
SET lot_line_mark_id = lot_temp_id,
line_mark_id = temp_id,
group_mark_id = l_hdr_id,
reservation_id = nvl(p_reservation_id, reservation_id) /*** {{ R12 Enhanced reservations code changes ***/
WHERE inventory_item_id = item_id
AND serial_number = from_serial_number ;
l_update_count := 1; /*** {{ R12 Enhanced reservations code changes ***/
IF (p_update_reservation = fnd_api.g_true and p_reservation_id is not null) THEN
BEGIN
update mtl_reservations
set serial_reservation_quantity = serial_reservation_quantity + l_update_count
where reservation_id = p_reservation_id;
, p_update_reservation => fnd_api.g_false
);
, p_update_reservation IN VARCHAR2 DEFAULT fnd_api.g_true
) IS /*** {{ R12 Enhanced reservations code changes ***/
unmarked_value NUMBER := -1;
l_update_count NUMBER;
SELECT reservation_id
, COUNT(reservation_id)
FROM mtl_serial_numbers
WHERE serial_number = from_serial_number AND
inventory_item_id = p_inventory_item_id AND
reservation_id IS NOT NULL
GROUP BY reservation_id;
SELECT reservation_id
, COUNT(reservation_id)
FROM mtl_serial_numbers
WHERE serial_number >= NVL(from_serial_number, serial_number) AND
serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
inventory_item_id = p_inventory_item_id AND
reservation_id IS NOT NULL
GROUP BY reservation_id;
SELECT reservation_id
, COUNT(reservation_id)
FROM mtl_serial_numbers
WHERE group_mark_id IN(hdr_id, lot_temp_id, temp_id) AND
(
line_mark_id = temp_id OR
line_mark_id IS NULL OR
line_mark_id = -1
) AND
(
lot_line_mark_id = lot_temp_id OR
lot_line_mark_id IS NULL
OR lot_line_mark_id = -1
) AND
serial_number >= NVL(from_serial_number, serial_number) AND
serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) ) AND
reservation_id IS NOT NULL
GROUP BY reservation_id;
SELECT reservation_id
, COUNT(reservation_id)
FROM mtl_serial_numbers
WHERE group_mark_id IN(hdr_id, temp_id) AND
(
line_mark_id = temp_id OR
line_mark_id IS NULL OR
line_mark_id = -1
) AND
serial_number >= NVL(from_serial_number, serial_number) AND
serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) ) AND
reservation_id IS NOT NULL
GROUP BY reservation_id;
SELECT reservation_id
, COUNT(reservation_id)
FROM mtl_serial_numbers
WHERE group_mark_id = hdr_id AND
serial_number >= NVL(from_serial_number, serial_number) AND
serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) ) AND
reservation_id IS NOT NULL
GROUP BY reservation_id;
|| ' p_update_reservation = ' || p_update_reservation , 'SERIAL_CHECK');
inv_log_util.TRACE('Update msn with serial_number= from_serial_number and '
|| 'inventory_item_id = ' || p_inventory_item_id, 'SERIAL_CHECK');
IF (p_update_reservation = fnd_api.g_true) THEN
OPEN serial_rsv1;
IF (p_update_reservation = fnd_api.g_true) THEN
IF (l_debug = 1) THEN
inv_log_util.TRACE('Case 1 and p_update_reservation is T', 'inv_unmark_rsv_serial');
UPDATE mtl_serial_numbers
SET line_mark_id = unmarked_value
, group_mark_id = unmarked_value
, lot_line_mark_id = unmarked_value
, reservation_id = NULL /*** {{ R12 Enhanced reservations code changes ***/
WHERE serial_number = from_serial_number AND inventory_item_id = p_inventory_item_id;
inv_log_util.TRACE('Case 1 and p_update_reservation it F', 'inv_unmark_rsv_serial');
UPDATE mtl_serial_numbers
SET line_mark_id = unmarked_value
, group_mark_id = unmarked_value
, lot_line_mark_id = unmarked_value
WHERE serial_number = from_serial_number AND inventory_item_id = p_inventory_item_id;
inv_log_util.TRACE('Update msn with serial_number >= '
|| ' nvl(from_serial_number, serial_number) '
|| ' AND serial_number <= nvl(to_serial_number, '
|| 'nvl(from_serial_number, serial_number)) AND inventory_item_id= '
|| p_inventory_item_id, 'SERIAL_CHECK');
IF (p_update_reservation = fnd_api.g_true) THEN
OPEN serial_rsv2;
IF (p_update_reservation = fnd_api.g_true) THEN
IF (l_debug = 1) THEN
inv_log_util.TRACE('Case 2 and p_update_reservation it T', 'inv_unmark_rsv_serial');
UPDATE mtl_serial_numbers
SET line_mark_id = unmarked_value
, group_mark_id = unmarked_value
, lot_line_mark_id = unmarked_value
, reservation_id = NULL /*** {{ R12 Enhanced reservations code changes ***/
WHERE serial_number >= NVL(from_serial_number, serial_number) AND
serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
inventory_item_id = p_inventory_item_id;
inv_log_util.TRACE('Case 2 and p_update_reservation it F', 'inv_unmark_rsv_serial');
UPDATE mtl_serial_numbers
SET line_mark_id = unmarked_value
, group_mark_id = unmarked_value
, lot_line_mark_id = unmarked_value
WHERE serial_number >= NVL(from_serial_number, serial_number) AND
serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
inventory_item_id = p_inventory_item_id;
END IF; --END IF p_update_reservation is T
inv_log_util.TRACE('Update msn with group_mark_id in ('
|| hdr_id || ', ' || lot_temp_id || ', ' || temp_id
|| 'AND (line_mark_id = ' || temp_id
|| ' OR line_mark_id is NULL OR line_mark_id = -1)', 'SERIAL_CHECK');
IF (p_update_reservation = fnd_api.g_true) THEN
OPEN serial_rsv3;
IF (p_update_reservation = fnd_api.g_true) THEN
IF (l_debug = 1) THEN
inv_log_util.TRACE('Case 3 and p_update_reservation it T', 'inv_unmark_rsv_serial');
UPDATE mtl_serial_numbers
SET line_mark_id = unmarked_value
, group_mark_id = unmarked_value
, lot_line_mark_id = unmarked_value
, reservation_id = NULL /*** {{ R12 Enhanced reservations code changes ***/
WHERE group_mark_id IN(hdr_id, lot_temp_id, temp_id) -- Bug 2491094: Added Temp ID also
AND
(
line_mark_id = temp_id OR line_mark_id IS NULL OR line_mark_id = -1
) AND
(
lot_line_mark_id = lot_temp_id OR lot_line_mark_id IS NULL OR lot_line_mark_id = -1
) AND
serial_number >= NVL(from_serial_number, serial_number) AND
serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) );
inv_log_util.TRACE('Case 3 and p_update_reservation it F', 'inv_unmark_rsv_serial');
UPDATE mtl_serial_numbers
SET line_mark_id = unmarked_value
, group_mark_id = unmarked_value
, lot_line_mark_id = unmarked_value
WHERE group_mark_id IN(hdr_id, lot_temp_id, temp_id) -- Bug 2491094: Added Temp ID also
AND
(
line_mark_id = temp_id OR line_mark_id IS NULL OR line_mark_id = -1
) AND
(
lot_line_mark_id = lot_temp_id OR lot_line_mark_id IS NULL OR lot_line_mark_id = -1
) AND
serial_number >= NVL(from_serial_number, serial_number) AND
serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) );
'update msn with group_mark_id in ('|| hdr_id || ', '|| temp_id
|| ' AND (line_mark_id = '|| temp_id || ' OR line_mark_id in (select SERIAL_TRANSACTION_TEMP_ID'
|| ' from mtl_transaction_lots_temp where group_header_id = '|| hdr_id||' and transaction_temp_id = '||temp_id
|| ') OR line_mark_id is NULL or line_mark_id = -1 ) AND '
|| ' serial_number >= nvl(from_serial_number, serial_number) '
|| ' AND serial_number <= nvl(to_serial_number, nvl(from_serial_number, serial_number)) '
|| ' AND length(serial_number) = length(nvl(from_serial_number, serial_number))', 'SERIAL_CHECK');
IF (p_update_reservation = fnd_api.g_true) THEN
OPEN serial_rsv4;
IF (p_update_reservation = fnd_api.g_true) THEN
IF (l_debug = 1) THEN
inv_log_util.TRACE('Case 4 and p_update_reservation it T', 'inv_unmark_rsv_serial');
UPDATE mtl_serial_numbers
SET line_mark_id = unmarked_value
, group_mark_id = unmarked_value
, lot_line_mark_id = unmarked_value
, reservation_id = NULL /*** {{ R12 Enhanced reservations code changes ***/
WHERE group_mark_id IN(hdr_id, temp_id) AND
(line_mark_id = temp_id OR
--Added code for bug 11820774,as for both lot and serial
--controlled item line_mark_id != temp_id
line_mark_id in (select SERIAL_TRANSACTION_TEMP_ID
from mtl_transaction_lots_temp
where group_header_id = hdr_id
and transaction_temp_id = temp_id)
--end fix for bug 11820774
OR line_mark_id IS NULL OR line_mark_id = -1
) AND
serial_number >= NVL(from_serial_number, serial_number) AND
serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) );
inv_log_util.TRACE('Case 4 and p_update_reservation it F', 'inv_unmark_rsv_serial');
UPDATE mtl_serial_numbers
SET line_mark_id = unmarked_value
, group_mark_id = unmarked_value
, lot_line_mark_id = unmarked_value
WHERE group_mark_id IN(hdr_id, temp_id) AND
(line_mark_id = temp_id OR
--Added code for bug 11820774,as for both lot and serial
--controlled item line_mark_id != temp_id
line_mark_id in (select SERIAL_TRANSACTION_TEMP_ID
from mtl_transaction_lots_temp
where group_header_id = hdr_id
and transaction_temp_id = temp_id)
--end fix for bug 11820774
OR line_mark_id IS NULL OR line_mark_id = -1
) AND
serial_number >= NVL(from_serial_number, serial_number) AND
serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) );
inv_log_util.TRACE('update msn with group_mark_id = '
|| hdr_id || ' AND serial_number >= nvl(from_serial_number, serial_number) '
|| ' AND serial_number <= nvl(to_serial_number, nvl(from_serial_number, serial_number)) '
|| ' AND length(serial_number) = length(nvl(from_serial_number, serial_number))', 'SERIAL_CHECK');
IF (p_update_reservation = fnd_api.g_true) THEN
OPEN serial_rsv5;
IF (p_update_reservation = fnd_api.g_true) THEN
IF (l_debug = 1) THEN
inv_log_util.TRACE('Case 5 and p_update_reservation it T', 'inv_unmark_rsv_serial');
UPDATE mtl_serial_numbers
SET line_mark_id = unmarked_value
, group_mark_id = unmarked_value
, lot_line_mark_id = unmarked_value
, reservation_id = NULL /*** {{ R12 Enhanced reservations code changes ***/
WHERE group_mark_id = hdr_id AND
serial_number >= NVL(from_serial_number, serial_number) AND
serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) );
inv_log_util.TRACE('Case 5 and p_update_reservation it F', 'inv_unmark_rsv_serial');
UPDATE mtl_serial_numbers
SET line_mark_id = unmarked_value
, group_mark_id = unmarked_value
, lot_line_mark_id = unmarked_value
WHERE group_mark_id = hdr_id AND
serial_number >= NVL(from_serial_number, serial_number) AND
serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) );
END IF; --END IF p_update_reservation = F
IF (p_update_reservation = fnd_api.g_true) THEN
FOR i IN 1 .. l_rsv_id_tbl.COUNT LOOP
l_update_count := l_rsv_count_tbl(i) * -1;
UPDATE mtl_reservations
SET serial_reservation_quantity = serial_reservation_quantity + l_update_count
WHERE reservation_id = l_rsv_id_tbl(i);
PROCEDURE inv_update_marked_serial
( from_serial_number IN VARCHAR2,
to_serial_number IN VARCHAR2 DEFAULT NULL,
item_id IN NUMBER,
org_id IN NUMBER,
temp_id IN NUMBER DEFAULT NULL,
hdr_id IN NUMBER DEFAULT NULL,
lot_temp_id IN NUMBER DEFAULT NULL,
success OUT NOCOPY BOOLEAN ) IS
l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
inv_log_util.trace('Inside inv_update_marked_serial','SERIAL_CHECK');
UPDATE mtl_serial_numbers
--SET group_mark_id = nvl(temp_id, hdr_id)
--rtv enhancement
SET group_mark_id = Nvl(hdr_id,temp_id)
,line_mark_id = Decode(hdr_id,NULL,NULL,temp_id)
,lot_line_mark_id = Decode(hdr_id,NULL,NULL,lot_temp_id)
WHERE inventory_item_id = item_id
AND current_organization_id = org_id
AND serial_number = from_serial_number ;
UPDATE mtl_serial_numbers
--SET group_mark_id = nvl(temp_id, hdr_id)
--rtv enhancement
SET group_mark_id = Nvl(hdr_id,temp_id)
,line_mark_id = Decode(hdr_id,NULL,NULL,temp_id)
,lot_line_mark_id = Decode(hdr_id,NULL,NULL,lot_temp_id)
WHERE inventory_item_id = item_id
AND current_organization_id = org_id
AND serial_number between from_serial_number AND to_serial_number
AND LENGTH(serial_number) = LENGTH(from_serial_number) ;
END inv_update_marked_serial;