The following lines contain the word 'select', 'insert', 'update' or 'delete':
select lot_number
into l_lot_number
from mtl_lot_numbers
where organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and lot_number = p_lot_number;
SELECT lot_attribute_category
INTO l_user_context
FROM mtl_lot_numbers
WHERE organization_id = p_organization_id
AND inventory_item_id = p_reference_inventory_item_id
AND lot_number = p_reference_lot_number;
SELECT vendor_id ,
grade_code,
origination_date,
date_code,
status_id,
change_date,
age,
retest_date,
maturity_date,
lot_attribute_category,
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,
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_attribute10,
supplier_lot_number,
n_attribute9,
territory_code,
vendor_name,
description,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
INTO
l_in_lot_rec.vendor_id ,
l_in_lot_rec.grade_code,
l_in_lot_rec.origination_date,
l_in_lot_rec.date_code,
l_in_lot_rec.status_id,
l_in_lot_rec.change_date,
l_in_lot_rec.age,
l_in_lot_rec.retest_date,
l_in_lot_rec.maturity_date,
l_in_lot_rec.lot_attribute_category,
l_in_lot_rec.item_size,
l_in_lot_rec.color,
l_in_lot_rec.volume,
l_in_lot_rec.volume_uom,
l_in_lot_rec.place_of_origin,
l_in_lot_rec.best_by_date,
l_in_lot_rec.LENGTH,
l_in_lot_rec.length_uom,
l_in_lot_rec.recycled_content,
l_in_lot_rec.thickness,
l_in_lot_rec.thickness_uom,
l_in_lot_rec.width,
l_in_lot_rec.width_uom,
l_in_lot_rec.curl_wrinkle_fold,
l_in_lot_rec.c_attribute1,
l_in_lot_rec.c_attribute2,
l_in_lot_rec.c_attribute3,
l_in_lot_rec.c_attribute4,
l_in_lot_rec.c_attribute5,
l_in_lot_rec.c_attribute6,
l_in_lot_rec.c_attribute7,
l_in_lot_rec.c_attribute8,
l_in_lot_rec.c_attribute9,
l_in_lot_rec.c_attribute10,
l_in_lot_rec.c_attribute11,
l_in_lot_rec.c_attribute12,
l_in_lot_rec.c_attribute13,
l_in_lot_rec.c_attribute14,
l_in_lot_rec.c_attribute15,
l_in_lot_rec.c_attribute16,
l_in_lot_rec.c_attribute17,
l_in_lot_rec.c_attribute18,
l_in_lot_rec.c_attribute19,
l_in_lot_rec.c_attribute20,
l_in_lot_rec.d_attribute1,
l_in_lot_rec.d_attribute2,
l_in_lot_rec.d_attribute3,
l_in_lot_rec.d_attribute4,
l_in_lot_rec.d_attribute5,
l_in_lot_rec.d_attribute6,
l_in_lot_rec.d_attribute7,
l_in_lot_rec.d_attribute8,
l_in_lot_rec.d_attribute9,
l_in_lot_rec.d_attribute10,
l_in_lot_rec.n_attribute1,
l_in_lot_rec.n_attribute2,
l_in_lot_rec.n_attribute3,
l_in_lot_rec.n_attribute4,
l_in_lot_rec.n_attribute5,
l_in_lot_rec.n_attribute6,
l_in_lot_rec.n_attribute7,
l_in_lot_rec.n_attribute8,
l_in_lot_rec.n_attribute10,
l_in_lot_rec.supplier_lot_number,
l_in_lot_rec.n_attribute9,
l_in_lot_rec.territory_code,
l_in_lot_rec.vendor_name,
l_in_lot_rec.description,
l_in_lot_rec.attribute_category,
l_in_lot_rec.attribute1,
l_in_lot_rec.attribute2,
l_in_lot_rec.attribute3,
l_in_lot_rec.attribute4,
l_in_lot_rec.attribute5,
l_in_lot_rec.attribute6,
l_in_lot_rec.attribute7,
l_in_lot_rec.attribute8,
l_in_lot_rec.attribute9,
l_in_lot_rec.attribute10,
l_in_lot_rec.attribute11,
l_in_lot_rec.attribute12,
l_in_lot_rec.attribute13,
l_in_lot_rec.attribute14,
l_in_lot_rec.attribute15
FROM mtl_lot_numbers
WHERE organization_id = p_organization_id
AND inventory_item_id = p_reference_inventory_item_id
AND lot_number = p_reference_lot_number;
SELECT vendor_id ,
grade_code,
origination_date,
date_code,
status_id,
change_date,
age,
retest_date,
maturity_date,
supplier_lot_number,
territory_code,
vendor_name,
description,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
INTO
l_in_lot_rec.vendor_id ,
l_in_lot_rec.grade_code,
l_in_lot_rec.origination_date,
l_in_lot_rec.date_code,
l_in_lot_rec.status_id,
l_in_lot_rec.change_date,
l_in_lot_rec.age,
l_in_lot_rec.retest_date,
l_in_lot_rec.maturity_date,
l_in_lot_rec.supplier_lot_number,
l_in_lot_rec.territory_code,
l_in_lot_rec.vendor_name,
l_in_lot_rec.description,
l_in_lot_rec.attribute_category,
l_in_lot_rec.attribute1,
l_in_lot_rec.attribute2,
l_in_lot_rec.attribute3,
l_in_lot_rec.attribute4,
l_in_lot_rec.attribute5,
l_in_lot_rec.attribute6,
l_in_lot_rec.attribute7,
l_in_lot_rec.attribute8,
l_in_lot_rec.attribute9,
l_in_lot_rec.attribute10,
l_in_lot_rec.attribute11,
l_in_lot_rec.attribute12,
l_in_lot_rec.attribute13,
l_in_lot_rec.attribute14,
l_in_lot_rec.attribute15
FROM mtl_lot_numbers
WHERE organization_id = p_organization_id
AND inventory_item_id = p_reference_inventory_item_id
AND lot_number = p_reference_lot_number;
procedure delete_lot(x_return_status out nocopy VARCHAR2
, x_msg_count out nocopy NUMBER
, x_msg_data out nocopy VARCHAR2
, p_inventory_item_id NUMBER
, p_organization_id NUMBER
, p_lot_number VARCHAR2
)
IS
BEGIN
delete from mtl_lot_numbers
where organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and lot_number = p_lot_number;
END delete_lot;
procedure update_inv_lot(x_return_status out nocopy VARCHAR2
, x_msg_count out nocopy NUMBER
, x_msg_data out nocopy VARCHAR2
, p_inventory_item_id NUMBER
, p_organization_id NUMBER
, p_lot_number VARCHAR2
, p_expiration_date DATE
, p_disable_flag NUMBER
, p_attribute_category VARCHAR2
, p_lot_attribute_category VARCHAR2
, p_attributes_tbl JTF_VARCHAR2_TABLE_1000
, p_c_attributes_tbl JTF_VARCHAR2_TABLE_1000
, p_n_attributes_tbl JTF_NUMBER_TABLE
, p_d_attributes_tbl JTF_DATE_TABLE
, p_grade_code VARCHAR2
, p_origination_date DATE
, p_date_code VARCHAR2
, p_status_id NUMBER
, p_change_date DATE
, p_age NUMBER
, p_retest_date DATE
, p_maturity_date DATE
, p_item_size NUMBER
, p_color VARCHAR2
, p_volume NUMBER
, p_volume_uom VARCHAR2
, p_place_of_origin VARCHAR2
, p_best_by_date DATE
, p_length NUMBER
, p_length_uom VARCHAR2
, p_recycled_content NUMBER
, p_thickness NUMBER
, p_thickness_uom VARCHAR2
, p_width NUMBER
, p_width_uom VARCHAR2
, p_territory_code VARCHAR2
, p_supplier_lot_number VARCHAR2
, p_vendor_name VARCHAR2
, p_source NUMBER
)
as
ddp_attributes_tbl inv_lot_api_pub.char_tbl;
mydebug('calling inv_lot_api_pub.update_inv_lot with:');
inv_lot_api_pub.update_inv_lot(x_return_status,
x_msg_count,
x_msg_data,
p_inventory_item_id,
p_organization_id,
p_lot_number,
p_expiration_date,
p_disable_flag,
p_attribute_category,
p_lot_attribute_category,
ddp_attributes_tbl,
ddp_c_attributes_tbl,
ddp_n_attributes_tbl,
ddp_d_attributes_tbl,
p_grade_code,
p_origination_date,
p_date_code,
p_status_id,
p_change_date,
p_age,
p_retest_date,
p_maturity_date,
p_item_size,
p_color,
p_volume,
p_volume_uom,
p_place_of_origin,
p_best_by_date,
p_length,
p_length_uom,
p_recycled_content,
p_thickness,
p_thickness_uom,
p_width,
p_width_uom,
p_territory_code,
p_supplier_lot_number,
p_vendor_name,
p_source);