The following lines contain the word 'select', 'insert', 'update' or 'delete':
IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.last_updated_by,p_old_Lot_Serial_rec.last_updated_by)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_LAST_UPDATED_BY;
IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.last_update_date,p_old_Lot_Serial_rec.last_update_date)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_LAST_UPDATE_DATE;
IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.last_update_login,p_old_Lot_Serial_rec.last_update_login)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_LAST_UPDATE_LOGIN;
ELSIF p_attr_id = G_LAST_UPDATED_BY THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_LAST_UPDATED_BY;
ELSIF p_attr_id = G_LAST_UPDATE_DATE THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_LAST_UPDATE_DATE;
ELSIF p_attr_id = G_LAST_UPDATE_LOGIN THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_LAST_UPDATE_LOGIN;
IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.last_updated_by,p_old_Lot_Serial_rec.last_updated_by)
THEN
NULL;
IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.last_update_date,p_old_Lot_Serial_rec.last_update_date)
THEN
NULL;
IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.last_update_login,p_old_Lot_Serial_rec.last_update_login)
THEN
NULL;
IF p_x_Lot_Serial_rec.last_updated_by = FND_API.G_MISS_NUM THEN
p_x_Lot_Serial_rec.last_updated_by := p_old_Lot_Serial_rec.last_updated_by;
IF p_x_Lot_Serial_rec.last_update_date = FND_API.G_MISS_DATE THEN
p_x_Lot_Serial_rec.last_update_date := p_old_Lot_Serial_rec.last_update_date;
IF p_x_Lot_Serial_rec.last_update_login = FND_API.G_MISS_NUM THEN
p_x_Lot_Serial_rec.last_update_login := p_old_Lot_Serial_rec.last_update_login;
IF p_x_Lot_Serial_rec.last_updated_by = FND_API.G_MISS_NUM THEN
p_x_Lot_Serial_rec.last_updated_by := NULL;
IF p_x_Lot_Serial_rec.last_update_date = FND_API.G_MISS_DATE THEN
p_x_Lot_Serial_rec.last_update_date := NULL;
IF p_x_Lot_Serial_rec.last_update_login = FND_API.G_MISS_NUM THEN
p_x_Lot_Serial_rec.last_update_login := NULL;
UPDATE OE_LOT_SERIAL_NUMBERS
SET LINE_SET_ID = p_Line_Set_ID
WHERE LINE_ID = p_Line_ID;
PROCEDURE Update_Row
( p_Lot_Serial_rec IN OUT NOCOPY OE_Order_PUB.Lot_Serial_Rec_Type
)
IS
l_lock_control NUMBER;
SELECT lock_control
INTO l_lock_control
FROM OE_LOT_SERIAL_NUMBERS
WHERE lot_serial_id = p_Lot_Serial_rec.lot_serial_id;
oe_debug_pub.add('before update, lot_serial_id= '|| l_lot_serial_rec.lot_serial_id, 1);
UPDATE OE_LOT_SERIAL_NUMBERS
SET ATTRIBUTE1 = p_Lot_Serial_rec.attribute1
, ATTRIBUTE10 = p_Lot_Serial_rec.attribute10
, ATTRIBUTE11 = p_Lot_Serial_rec.attribute11
, ATTRIBUTE12 = p_Lot_Serial_rec.attribute12
, ATTRIBUTE13 = p_Lot_Serial_rec.attribute13
, ATTRIBUTE14 = p_Lot_Serial_rec.attribute14
, ATTRIBUTE15 = p_Lot_Serial_rec.attribute15
, ATTRIBUTE2 = p_Lot_Serial_rec.attribute2
, ATTRIBUTE3 = p_Lot_Serial_rec.attribute3
, ATTRIBUTE4 = p_Lot_Serial_rec.attribute4
, ATTRIBUTE5 = p_Lot_Serial_rec.attribute5
, ATTRIBUTE6 = p_Lot_Serial_rec.attribute6
, ATTRIBUTE7 = p_Lot_Serial_rec.attribute7
, ATTRIBUTE8 = p_Lot_Serial_rec.attribute8
, ATTRIBUTE9 = p_Lot_Serial_rec.attribute9
, CONTEXT = p_Lot_Serial_rec.context
, CREATED_BY = p_Lot_Serial_rec.created_by
, CREATION_DATE = p_Lot_Serial_rec.creation_date
, FROM_SERIAL_NUMBER = p_Lot_Serial_rec.from_serial_number
, LAST_UPDATED_BY = p_Lot_Serial_rec.last_updated_by
, LAST_UPDATE_DATE = p_Lot_Serial_rec.last_update_date
, LAST_UPDATE_LOGIN = p_Lot_Serial_rec.last_update_login
, LINE_ID = p_Lot_Serial_rec.line_id
, LOT_NUMBER = p_Lot_Serial_rec.lot_number
-- , SUBLOT_NUMBER = p_Lot_Serial_rec.sublot_number --OPM 2380194 INVCONV
, LOT_SERIAL_ID = p_Lot_Serial_rec.lot_serial_id
, QUANTITY = p_Lot_Serial_rec.quantity
, QUANTITY2 = p_Lot_Serial_rec.quantity2 --OPM 2380194
, TO_SERIAL_NUMBER = p_Lot_Serial_rec.to_serial_number
, ORIG_SYS_LOTSERIAL_REF = p_Lot_Serial_rec.orig_sys_lotserial_ref
, LINE_SET_ID = p_Lot_Serial_rec.line_set_id
, LOCK_CONTROL = p_Lot_Serial_rec.lock_control
WHERE LOT_SERIAL_ID = p_Lot_Serial_rec.lot_serial_id
;
oe_debug_pub.add('after update, old lot serial Id= ' || l_lot_serial_rec.lot_serial_id);
oe_debug_pub.add('after update, new lot serial Id= ' || p_lot_serial_rec.lot_serial_id);
OE_ORDER_UTIL.Update_Global_Picture(
p_Upd_New_Rec_If_Exists => True,
p_lot_serial_rec => p_lot_serial_rec,
p_old_lot_serial_rec => l_lot_serial_rec,
p_lot_serial_id => p_lot_serial_rec.lot_serial_id,
x_index => l_index,
x_return_status => l_return_status);
OE_DEBUG_PUB.ADD('Update_Global Return Status from OE_LOT_SERIAL_UTIL.update_row is: ' || l_return_status);
OE_DEBUG_PUB.ADD('Exiting OE_LOT_SERIAL_UTIL.Update_ROW', 1);
OE_DEBUG_PUB.ADD('Update_Global_Picture Error in OE_LOT_SERIAL_UTIL.Update_row');
OE_DEBUG_PUB.ADD('Exiting OE_LOT_SERIAL_UTIL.Update_ROW', 1);
, 'Update_Row'
);
END Update_Row;
PROCEDURE Insert_Row
( p_Lot_Serial_rec IN OUT NOCOPY OE_Order_PUB.Lot_Serial_Rec_Type
)
IS
l_lock_control NUMBER := 1;
INSERT INTO OE_LOT_SERIAL_NUMBERS
( ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, CONTEXT
, CREATED_BY
, CREATION_DATE
, FROM_SERIAL_NUMBER
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LINE_ID
, LOT_NUMBER
-- , SUBLOT_NUMBER --OPM 2380194 INVCONV
, LOT_SERIAL_ID
, QUANTITY
, QUANTITY2 --OPM 2380194
, TO_SERIAL_NUMBER
, ORIG_SYS_LOTSERIAL_REF
, LINE_SET_ID
, LOCK_CONTROL
)
VALUES
( p_Lot_Serial_rec.attribute1
, p_Lot_Serial_rec.attribute10
, p_Lot_Serial_rec.attribute11
, p_Lot_Serial_rec.attribute12
, p_Lot_Serial_rec.attribute13
, p_Lot_Serial_rec.attribute14
, p_Lot_Serial_rec.attribute15
, p_Lot_Serial_rec.attribute2
, p_Lot_Serial_rec.attribute3
, p_Lot_Serial_rec.attribute4
, p_Lot_Serial_rec.attribute5
, p_Lot_Serial_rec.attribute6
, p_Lot_Serial_rec.attribute7
, p_Lot_Serial_rec.attribute8
, p_Lot_Serial_rec.attribute9
, p_Lot_Serial_rec.context
, p_Lot_Serial_rec.created_by
, p_Lot_Serial_rec.creation_date
, p_Lot_Serial_rec.from_serial_number
, p_Lot_Serial_rec.last_updated_by
, p_Lot_Serial_rec.last_update_date
, p_Lot_Serial_rec.last_update_login
, p_Lot_Serial_rec.line_id
, p_Lot_Serial_rec.lot_number
-- , p_Lot_Serial_rec.sublot_number --OPM 2380194 INVCONV
, p_Lot_Serial_rec.lot_serial_id
, p_Lot_Serial_rec.quantity
, p_Lot_Serial_rec.quantity2 --OPM 2380194
, p_Lot_Serial_rec.to_serial_number
, p_Lot_Serial_rec.orig_sys_lotserial_ref
, p_Lot_Serial_rec.line_set_id
, p_Lot_Serial_rec.lock_control
);
OE_ORDER_UTIL.Update_Global_Picture(
p_Upd_New_Rec_If_Exists => True,
p_old_lot_serial_rec => NULL,
p_lot_serial_rec => p_lot_serial_rec,
p_lot_serial_id => p_lot_serial_rec.lot_serial_id,
x_index => l_index,
x_return_status => l_return_status);
OE_DEBUG_PUB.ADD('Update_Global Return Status from OE_LOT_SERIAL_UTIL.insert_row is: ' || l_return_status);
OE_DEBUG_PUB.ADD('Exiting OE_LOT_SERIAL_UTIL.INSERT_ROW', 1);
OE_DEBUG_PUB.ADD('Update_Global_Picture Error in OE_LOT_SERIAL_UTIL.Insert_row');
OE_DEBUG_PUB.ADD('Exiting OE_LOT_SERIAL_UTIL.INSERT_ROW', 1);
, 'Insert_Row'
);
END Insert_Row;
SELECT 1 INTO dummy
FROM oe_order_lines
WHERE line_set_id = l_line_set_id
AND line_id <> p_line_id;
PROCEDURE Delete_Row
( p_lot_serial_id IN NUMBER := fnd_api.g_miss_num
, p_line_id IN NUMBER := fnd_api.g_miss_num
)
IS
l_line_rec OE_ORDER_PUB.line_rec_type;
SELECT lot_serial_id
FROM OE_LOT_SERIAL_NUMBERS
WHERE line_set_id = l_line_rec.line_set_id;
SELECT lot_serial_id
FROM OE_LOT_SERIAL_NUMBERS
WHERE line_id = p_line_id;
oe_debug_pub.add('Entering OE_LOT_SERIAL_UTIL.DELETE_ROW', 1);
--added for notification framework to update global picture for lot serials for this line_id
oe_debug_pub.add('JPN: Line ID' || p_line_id);
--query lot serial record, then call notification framework to update global picture.
OE_LOT_SERIAL_UTIL.Query_Row(p_lot_serial_id => l_lots.lot_serial_id,
x_lot_serial_rec =>l_old_lot_serial_rec);
oe_debug_pub.add('in delete row, lot_serial_id= '|| l_lots.lot_serial_id , 1);
/* Set the operation on the record so that globals are updated as well */
l_new_lot_serial_rec.operation := OE_GLOBALS.G_OPR_DELETE;
OE_ORDER_UTIL.Update_Global_Picture(
p_Upd_New_Rec_If_Exists => True,
p_lot_serial_rec => l_new_lot_serial_rec,
p_old_lot_serial_rec => l_old_lot_serial_rec,
p_lot_serial_id => l_lots.lot_serial_id,
x_index => l_index,
x_return_status => l_return_status);
OE_DEBUG_PUB.ADD('Update_Global Return Status from OE_LOT_SERIAL_UTIL.delete_row' ||
' for deleting line set lot_serial line is: ' || l_return_status);
OE_DEBUG_PUB.ADD('Exiting OE_LOT_SERIAL_UTIL.DELETE_ROW', 1);
OE_DEBUG_PUB.ADD('Update_Global_Picture Error in OE_LOT_SERIAL_UTIL.Delete_row');
OE_DEBUG_PUB.ADD('Exiting OE_LOT_SERIAL_UTIL.DELETE_ROW', 1);
DELETE FROM oe_lot_serial_numbers
WHERE line_id = p_line_id;
--added for notification framework to update global picture for lot serials in line set
FOR l_set IN line_set
LOOP
--query lot serial record, then call notification framework to update global picture.
OE_LOT_SERIAL_UTIL.Query_Row(p_lot_serial_id => l_set.lot_serial_id,
x_lot_serial_rec =>l_old_lot_serial_rec);
oe_debug_pub.add('in delete row, lot_serial_id= '|| l_set.lot_serial_id , 1);
/* Set the operation on the record so that globals are updated as well */
l_new_lot_serial_rec.operation := OE_GLOBALS.G_OPR_DELETE;
OE_ORDER_UTIL.Update_Global_Picture(
p_Upd_New_Rec_If_Exists => True,
p_lot_serial_rec => l_new_lot_serial_rec,
p_old_lot_serial_rec => l_old_lot_serial_rec,
p_lot_serial_id => l_set.lot_serial_id,
x_index => l_index,
x_return_status => l_return_status);
OE_DEBUG_PUB.ADD('Update_Global Return Status from OE_LOT_SERIAL_UTIL.delete_row' ||
' for deleting line set lot_serial line is: ' || l_return_status);
OE_DEBUG_PUB.ADD('Exiting OE_LOT_SERIAL_UTIL.DELETE_ROW', 1);
OE_DEBUG_PUB.ADD('Update_Global_Picture Error in OE_LOT_SERIAL_UTIL.Delete_row');
OE_DEBUG_PUB.ADD('Exiting OE_LOT_SERIAL_UTIL.DELETE_ROW', 1);
DELETE FROM oe_lot_serial_numbers
WHERE line_set_id = l_line_rec.line_set_id;
DELETE FROM OE_LOT_SERIAL_NUMBERS
WHERE LOT_SERIAL_ID = p_lot_serial_id;
, 'Delete_Row'
);
END Delete_Row;
SELECT ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, CONTEXT
, CREATED_BY
, CREATION_DATE
, FROM_SERIAL_NUMBER
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LINE_ID
, LOT_NUMBER
-- , SUBLOT_NUMBER --OPM 2380194 INVCONV
, LOT_SERIAL_ID
, QUANTITY
, QUANTITY2 --OPM 2380194
, TO_SERIAL_NUMBER
, LINE_SET_ID
, LOCK_CONTROL
FROM OE_LOT_SERIAL_NUMBERS
WHERE ( LOT_SERIAL_ID = p_lot_serial_id
)
OR ( LINE_ID = l_line_id
)
OR ( LINE_SET_ID = l_line_set_id
);
l_Lot_Serial_rec.last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
l_Lot_Serial_rec.last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
l_Lot_Serial_rec.last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
x_Lot_Serial_tbl(l_count).last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
x_Lot_Serial_tbl(l_count).last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
x_Lot_Serial_tbl(l_count).last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
SELECT lot_serial_id
INTO l_lot_serial_id
FROM oe_lot_serial_numbers
WHERE lot_serial_id = l_lot_serial_id
FOR UPDATE NOWAIT;
oe_debug_pub.add('selected for update', 1);
FND_MESSAGE.SET_NAME('OE','OE_LOCK_ROW_DELETED');
SELECT lot_serial_id
FROM oe_lot_serial_numbers
WHERE line_id = p_line_id
FOR UPDATE NOWAIT;
SELECT lot_serial_id
INTO l_lot_serial_id
FROM OE_LOT_SERIAL_NUMBERS
WHERE lot_serial_id = p_lot_serial_id
FOR UPDATE NOWAIT;
fnd_message.set_name('ONT','OE_LOCK_ROW_DELETED');