[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Bug 8198497 - Added this procedure to validate and update lot status correctly */
PROCEDURE validate_lot_status(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_lot_number IN VARCHAR2
, p_status_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR
)
IS
l_old_status_id NUMBER;
Select nvl(lot_status_enabled,'N')
into l_lot_status_enabled
from mtl_system_items
where organization_id= p_organization_id
and inventory_item_id= p_inventory_item_id;
select status_id
into l_old_status_id
from mtl_lot_numbers
where organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and lot_number = p_lot_number;
print_debug('validate_lot_status: Before calling inv_material_status_grp.update_status', 9);
INV_MATERIAL_STATUS_GRP.update_status
( p_api_version
, p_init_msg_list
, x_return_status
, x_msg_count
, x_msg_data
, 2
, p_status_id
, p_organization_id
, p_inventory_item_id
, NULL
, NULL
, p_lot_number
, NULL
, NULL
, 'O'
, NULL
) ;
print_debug('validate_lot_status: Return status from inv_material_status_grp.update_status: '||x_return_status, 9);
PROCEDURE insertlot (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER
:= fnd_api.g_valid_level_full,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_lot_number IN VARCHAR2,
p_expiration_date IN OUT NOCOPY DATE,
p_transaction_temp_id IN NUMBER DEFAULT NULL,
p_transaction_action_id IN NUMBER DEFAULT NULL,
p_transfer_organization_id IN NUMBER DEFAULT NULL,
x_object_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_parent_lot_number IN VARCHAR2 DEFAULT NULL, --bug 10176719 - inserting parent lot number
p_origination_type IN NUMBER DEFAULT NULL --bug 15896641 - inserting origination type
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2 ( 30 ) := 'insertLot';
l_status_rec inv_material_status_pub.mtl_status_update_rec_type;
SELECT TO_CHAR ( vendor_id ),
grade_code,
fnd_date.date_to_canonical ( origination_date ),
date_code,
TO_CHAR ( status_id ),
fnd_date.date_to_canonical ( change_date ),
TO_NUMBER ( age ),
fnd_date.date_to_canonical ( retest_date ),
fnd_date.date_to_canonical ( maturity_date ),
lot_attribute_category,
TO_CHAR ( item_size ),
color,
TO_CHAR ( volume ),
volume_uom,
place_of_origin,
fnd_date.date_to_canonical ( best_by_date ),
TO_CHAR ( LENGTH ),
length_uom,
TO_CHAR ( recycled_content ),
TO_CHAR ( thickness ),
thickness_uom,
TO_CHAR ( 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,
fnd_date.date_to_canonical ( d_attribute1 ),
fnd_date.date_to_canonical ( d_attribute2 ),
fnd_date.date_to_canonical ( d_attribute3 ),
fnd_date.date_to_canonical ( d_attribute4 ),
fnd_date.date_to_canonical ( d_attribute5 ),
fnd_date.date_to_canonical ( d_attribute6 ),
fnd_date.date_to_canonical ( d_attribute7 ),
fnd_date.date_to_canonical ( d_attribute8 ),
fnd_date.date_to_canonical ( d_attribute9 ),
fnd_date.date_to_canonical ( d_attribute10 ),
TO_CHAR ( n_attribute1 ),
TO_CHAR ( n_attribute2 ),
TO_CHAR ( n_attribute3 ),
TO_CHAR ( n_attribute4 ),
TO_CHAR ( n_attribute5 ),
TO_CHAR ( n_attribute6 ),
TO_CHAR ( n_attribute7 ),
TO_CHAR ( n_attribute8 ),
TO_CHAR ( n_attribute10 ),
supplier_lot_number,
TO_CHAR ( n_attribute9 ),
territory_code,
vendor_name,
description
FROM mtl_transaction_lots_temp
WHERE lot_number = p_lot_number
AND transaction_temp_id = p_trx_temp_id;
inv_pick_wave_pick_confirm_pub.tracelog ( 'Inside InsertLot API' , 'INV_LOT_API_PUB');
SAVEPOINT apiinsertlot_apipub;
SELECT lot_control_code
INTO l_lot_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT lot_number_uniqueness
INTO l_lotunique
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT lot_status_enabled --Added select for bug4066234
INTO l_dest_status_enabled
FROM mtl_system_items
WHERE
inventory_item_id=p_inventory_item_id and
organization_id=p_organization_id;
SELECT COUNT ( 1 )
INTO l_lotcount
FROM mtl_lot_numbers
WHERE inventory_item_id <> p_inventory_item_id
AND lot_number = p_lot_number
AND NOT EXISTS ( SELECT NULL
FROM mtl_lot_numbers lot
WHERE lot.lot_number = p_lot_number
AND lot.organization_id = p_organization_id
AND lot.inventory_item_id = p_inventory_item_id );
SELECT COUNT ( 1 )
INTO l_lotcount
FROM mtl_lot_numbers
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND lot_number = p_lot_number;
SELECT shelf_life_code,
shelf_life_days
INTO l_shelf_life_code,
l_shelf_life_days
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT SYSDATE + l_shelf_life_days
INTO p_expiration_date
FROM DUAL;
SELECT mtl_gen_object_id_s.NEXTVAL
INTO x_object_id
FROM DUAL;
inv_pick_wave_pick_confirm_pub.tracelog ( 'Before Insert action_id=3' , 'INV_LOT_API_PUB');
select count(*)
into l_lotcount
From mtl_lot_numbers
where organization_id = p_transfer_organization_id
and inventory_item_id = p_inventory_item_id
and lot_number = p_lot_number;
INSERT INTO mtl_lot_numbers
(
inventory_item_id,
organization_id,
lot_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
expiration_date,
disable_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
request_id,
program_application_id,
program_id,
program_update_date,
gen_object_id,
description,
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_attribute9,
supplier_lot_number,
n_attribute10,
territory_code,
vendor_name,
parent_lot_number -- bug 10176719 - inserting parent lot number
)
SELECT inventory_item_id,
p_organization_id,
p_lot_number,
SYSDATE,
l_userid,
creation_date,
created_by,
last_update_login,
expiration_date,
disable_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
request_id,
program_application_id,
program_id,
program_update_date,
x_object_id,
description,
vendor_id,
grade_code,
origination_date,
date_code,
decode(l_dest_status_enabled,'Y',status_id,1), --Added bug4066234,
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_attribute9,
supplier_lot_number,
n_attribute10,
territory_code,
vendor_name,
p_parent_lot_number -- bug 10176719 - inserting parent lot number
FROM mtl_lot_numbers
WHERE organization_id = p_transfer_organization_id
AND inventory_item_id = p_inventory_item_id
AND lot_number = p_lot_number
AND NOT EXISTS ( SELECT NULL
FROM mtl_lot_numbers lot
WHERE lot.lot_number = p_lot_number
AND lot.organization_id = p_organization_id
AND lot.inventory_item_id = p_inventory_item_id );
INSERT INTO mtl_lot_numbers
(
inventory_item_id,
organization_id,
lot_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
expiration_date,
request_id,
program_application_id,
program_id,
program_update_date,
gen_object_id,
description,
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_attribute9,
supplier_lot_number,
n_attribute10,
territory_code,
vendor_name,
parent_lot_number -- bug 10176719 - inserting parent lot number
)
SELECT mmtt.inventory_item_id,
p_organization_id,
p_lot_number,
SYSDATE,
l_userid,
mtlt.creation_date,
mtlt.created_by,
mtlt.last_update_login,
mtlt.lot_expiration_date,
mtlt.request_id,
mtlt.program_application_id,
mtlt.program_id,
mtlt.program_update_date,
x_object_id,
mtlt.description,
mtlt.vendor_id,
mtlt.grade_code,
mtlt.origination_date,
mtlt.date_code,
decode(l_dest_status_enabled,'Y',mtlt.status_id,1), --Added bug4066234,
mtlt.change_date,
mtlt.age,
mtlt.retest_date,
mtlt.maturity_date,
mtlt.lot_attribute_category,
mtlt.item_size,
mtlt.color,
mtlt.volume,
mtlt.volume_uom,
mtlt.place_of_origin,
mtlt.best_by_date,
mtlt.LENGTH,
mtlt.length_uom,
mtlt.recycled_content,
mtlt.thickness,
mtlt.thickness_uom,
mtlt.width,
mtlt.width_uom,
mtlt.curl_wrinkle_fold,
mtlt.c_attribute1,
mtlt.c_attribute2,
mtlt.c_attribute3,
mtlt.c_attribute4,
mtlt.c_attribute5,
mtlt.c_attribute6,
mtlt.c_attribute7,
mtlt.c_attribute8,
mtlt.c_attribute9,
mtlt.c_attribute10,
mtlt.c_attribute11,
mtlt.c_attribute12,
mtlt.c_attribute13,
mtlt.c_attribute14,
mtlt.c_attribute15,
mtlt.c_attribute16,
mtlt.c_attribute17,
mtlt.c_attribute18,
mtlt.c_attribute19,
mtlt.c_attribute20,
mtlt.d_attribute1,
mtlt.d_attribute2,
mtlt.d_attribute3,
mtlt.d_attribute4,
mtlt.d_attribute5,
mtlt.d_attribute6,
mtlt.d_attribute7,
mtlt.d_attribute8,
mtlt.d_attribute9,
mtlt.d_attribute10,
mtlt.n_attribute1,
mtlt.n_attribute2,
mtlt.n_attribute3,
mtlt.n_attribute4,
mtlt.n_attribute5,
mtlt.n_attribute6,
mtlt.n_attribute7,
mtlt.n_attribute8,
mtlt.n_attribute9,
mtlt.supplier_lot_number,
mtlt.n_attribute10,
mtlt.territory_code,
mtlt.vendor_name,
p_parent_lot_number -- bug 10176719 - inserting parent_lot_number
FROM mtl_transaction_lots_temp mtlt, mtl_material_transactions_temp mmtt
WHERE mtlt.transaction_temp_id = p_transaction_temp_id
AND mtlt.lot_number = p_lot_number
AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND NOT EXISTS ( SELECT NULL
FROM mtl_lot_numbers lot
WHERE lot.lot_number = p_lot_number
AND lot.organization_id = p_organization_id
AND lot.inventory_item_id = p_inventory_item_id );
inv_log_util.trace('Error in insertLot : ', 'INV_LOT_API_PUB','9');
inv_log_util.trace('Error in insertLot : ', 'INV_LOT_API_PUB','9');
SELECT status_id
INTO l_default_lot_status_id
FROM mtl_lot_numbers
WHERE lot_number = p_lot_number
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
inv_pick_wave_pick_confirm_pub.tracelog ( 'After Insert action_id=3' , 'INV_LOT_API_PUB');
inv_pick_wave_pick_confirm_pub.tracelog ( 'before inserting into mtl_lot_numbers' , 'INV_LOT_API_PUB');
INSERT INTO mtl_lot_numbers
(
inventory_item_id,
organization_id,
lot_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
expiration_date,
disable_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
request_id,
program_application_id,
program_id,
program_update_date,
gen_object_id,
description,
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,
parent_lot_number, -- bug 10176719 - inserting parent lot number
origination_type -- bug 15896641 - inserting origination type
)
VALUES (
p_inventory_item_id,
p_organization_id,
p_lot_number,
SYSDATE,
l_userid,
SYSDATE,
l_userid,
l_loginid,
p_expiration_date,
NULL,
l_source_non_wms_lot_att_rec.attribute_category,
l_source_non_wms_lot_att_rec.attribute1,
l_source_non_wms_lot_att_rec.attribute2,
l_source_non_wms_lot_att_rec.attribute3,
l_source_non_wms_lot_att_rec.attribute4,
l_source_non_wms_lot_att_rec.attribute5,
l_source_non_wms_lot_att_rec.attribute6,
l_source_non_wms_lot_att_rec.attribute7,
l_source_non_wms_lot_att_rec.attribute8,
l_source_non_wms_lot_att_rec.attribute9,
l_source_non_wms_lot_att_rec.attribute10,
l_source_non_wms_lot_att_rec.attribute11,
l_source_non_wms_lot_att_rec.attribute12,
l_source_non_wms_lot_att_rec.attribute13,
l_source_non_wms_lot_att_rec.attribute14,
l_source_non_wms_lot_att_rec.attribute15,
NULL,
NULL,
NULL,
NULL,
x_object_id,
g_lot_attributes_tbl(68).column_value,
TO_NUMBER(g_lot_attributes_tbl(1).column_value),
g_lot_attributes_tbl(2).column_value,
fnd_date.canonical_to_date(g_lot_attributes_tbl(3).column_value),
g_lot_attributes_tbl(4).column_value,
decode(l_dest_status_enabled,'Y',TO_NUMBER(g_lot_attributes_tbl(5).column_value),1), --Added bug 4066234
fnd_date.canonical_to_date (g_lot_attributes_tbl(6).column_value),
TO_NUMBER(g_lot_attributes_tbl(7).column_value),
fnd_date.canonical_to_date(g_lot_attributes_tbl(8).column_value),
fnd_date.canonical_to_date(g_lot_attributes_tbl(9).column_value),
g_lot_attributes_tbl(10).column_value,
TO_NUMBER(g_lot_attributes_tbl(11).column_value),
g_lot_attributes_tbl(12).column_value,
TO_NUMBER(g_lot_attributes_tbl(13).column_value),
g_lot_attributes_tbl(14).column_value,
g_lot_attributes_tbl(15).column_value,
fnd_date.canonical_to_date(g_lot_attributes_tbl(16).column_value),
TO_NUMBER(g_lot_attributes_tbl(17).column_value),
g_lot_attributes_tbl(18).column_value,
TO_NUMBER(g_lot_attributes_tbl(19).column_value),
TO_NUMBER(g_lot_attributes_tbl(20).column_value),
g_lot_attributes_tbl(21).column_value,
TO_NUMBER(g_lot_attributes_tbl(22).column_value),
g_lot_attributes_tbl(23).column_value,
g_lot_attributes_tbl(24).column_value,
g_lot_attributes_tbl(25).column_value,
g_lot_attributes_tbl(26).column_value,
g_lot_attributes_tbl(27).column_value,
g_lot_attributes_tbl(28).column_value,
g_lot_attributes_tbl(29).column_value,
g_lot_attributes_tbl(30).column_value,
g_lot_attributes_tbl(31).column_value,
g_lot_attributes_tbl(32).column_value,
g_lot_attributes_tbl(33).column_value,
g_lot_attributes_tbl(34).column_value,
g_lot_attributes_tbl(35).column_value,
g_lot_attributes_tbl(36).column_value,
g_lot_attributes_tbl(37).column_value,
g_lot_attributes_tbl(38).column_value,
g_lot_attributes_tbl(39).column_value,
g_lot_attributes_tbl(40).column_value,
g_lot_attributes_tbl(41).column_value,
g_lot_attributes_tbl(42).column_value,
g_lot_attributes_tbl(43).column_value,
g_lot_attributes_tbl(44).column_value,
fnd_date.canonical_to_date(g_lot_attributes_tbl(45).column_value),
fnd_date.canonical_to_date(g_lot_attributes_tbl(46).column_value),
fnd_date.canonical_to_date(g_lot_attributes_tbl(47).column_value),
fnd_date.canonical_to_date(g_lot_attributes_tbl(48).column_value),
fnd_date.canonical_to_date(g_lot_attributes_tbl(49).column_value),
fnd_date.canonical_to_date(g_lot_attributes_tbl(50).column_value),
fnd_date.canonical_to_date(g_lot_attributes_tbl(51).column_value),
fnd_date.canonical_to_date(g_lot_attributes_tbl(52).column_value),
fnd_date.canonical_to_date(g_lot_attributes_tbl(53).column_value),
fnd_date.canonical_to_date(g_lot_attributes_tbl(54).column_value),
TO_NUMBER(g_lot_attributes_tbl(55).column_value),
TO_NUMBER(g_lot_attributes_tbl(56).column_value),
TO_NUMBER(g_lot_attributes_tbl(57).column_value),
TO_NUMBER(g_lot_attributes_tbl(58).column_value),
TO_NUMBER(g_lot_attributes_tbl(59).column_value),
TO_NUMBER(g_lot_attributes_tbl(60).column_value),
TO_NUMBER(g_lot_attributes_tbl(61).column_value),
TO_NUMBER(g_lot_attributes_tbl(62).column_value),
TO_NUMBER(g_lot_attributes_tbl(63).column_value),
g_lot_attributes_tbl(64).column_value,
TO_NUMBER(g_lot_attributes_tbl(65).column_value),
g_lot_attributes_tbl(66).column_value,
g_lot_attributes_tbl(67).column_value,
p_parent_lot_number, -- bug 10176719 - inserting parent lot number
p_origination_type -- bug 15896641 - inserting origination type
);
inv_pick_wave_pick_confirm_pub.tracelog ( 'After inserting the lot' , 'INV_LOT_API_PUB');
l_status_rec.update_method := inv_material_status_pub.g_update_method_auto;
inv_material_status_pkg.insert_status_history ( l_status_rec);
inv_pick_wave_pick_confirm_pub.tracelog ( 'after calling insert_status_history' , 'INV_LOT_API_PUB');
inv_pick_wave_pick_confirm_pub.tracelog ('Update the expiration date', 'INV_LOT_API_PUB');
UPDATE mtl_lot_numbers
SET expiration_date = p_expiration_date,
last_update_date = SYSDATE,
last_updated_by = l_userid
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND lot_number = p_lot_number
AND expiration_date IS NULL ;
inv_pick_wave_pick_confirm_pub.tracelog ( 'Inserted the Lot ' || p_lot_number, 'INV_LOT_API_PUB');
ROLLBACK TO apiinsertlot_apipub;
fnd_msg_pub.add_exc_msg ('INV_LOT_API_PUB' , 'insertLot' );
END insertlot;
PROCEDURE inserttrxlot (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER
:= fnd_api.g_valid_level_full,
p_primary_quantity IN NUMBER DEFAULT NULL,
p_transaction_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_transaction_date IN DATE,
p_transaction_source_id IN NUMBER,
p_transaction_source_name IN VARCHAR2,
p_transaction_source_type_id IN NUMBER,
p_transaction_temp_id IN NUMBER,
p_transaction_action_id IN NUMBER,
p_serial_transaction_id IN NUMBER,
p_lot_number IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_attributes_default inv_lot_sel_attr.lot_sel_attributes_tbl_type;
l_api_name CONSTANT VARCHAR2 ( 30 ) := 'insertTrxLot';
SELECT TO_CHAR ( vendor_id ),
grade_code,
fnd_date.date_to_canonical ( origination_date ),
date_code,
TO_CHAR ( status_id ),
fnd_date.date_to_canonical ( change_date ),
TO_NUMBER ( age ),
fnd_date.date_to_canonical ( retest_date ),
fnd_date.date_to_canonical ( maturity_date ),
lot_attribute_category,
TO_CHAR ( item_size ),
color,
TO_CHAR ( volume ),
volume_uom,
place_of_origin,
fnd_date.date_to_canonical ( best_by_date ),
TO_CHAR ( LENGTH ),
length_uom,
TO_CHAR ( recycled_content ),
TO_CHAR ( thickness ),
thickness_uom,
TO_CHAR ( 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,
fnd_date.date_to_canonical ( d_attribute1 ),
fnd_date.date_to_canonical ( d_attribute2 ),
fnd_date.date_to_canonical ( d_attribute3 ),
fnd_date.date_to_canonical ( d_attribute4 ),
fnd_date.date_to_canonical ( d_attribute5 ),
fnd_date.date_to_canonical ( d_attribute6 ),
fnd_date.date_to_canonical ( d_attribute7 ),
fnd_date.date_to_canonical ( d_attribute8 ),
fnd_date.date_to_canonical ( d_attribute9 ),
fnd_date.date_to_canonical ( d_attribute10 ),
TO_CHAR ( n_attribute1 ),
TO_CHAR ( n_attribute2 ),
TO_CHAR ( n_attribute3 ),
TO_CHAR ( n_attribute4 ),
TO_CHAR ( n_attribute5 ),
TO_CHAR ( n_attribute6 ),
TO_CHAR ( n_attribute7 ),
TO_CHAR ( n_attribute8 ),
TO_CHAR ( n_attribute10 ),
supplier_lot_number,
TO_CHAR ( n_attribute9 ),
territory_code,
vendor_name
FROM mtl_transaction_lots_temp
WHERE lot_number = p_lot_number
AND transaction_temp_id = p_trx_temp_id;
SAVEPOINT apiinsertlot_apipub;
/* SELECT transaction_quantity,
primary_quantity
INTO l_transaction_quantity,
l_primary_quantity
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id
AND lot_number = p_lot_number;
INSERT INTO mtl_transaction_lot_numbers
(
transaction_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
inventory_item_id,
organization_id,
transaction_date,
transaction_source_id,
transaction_source_type_id,
transaction_source_name,
transaction_quantity,
primary_quantity,
lot_number,
serial_transaction_id,
description,
supplier_lot_number,
origination_date,
date_code,
grade_code,
change_date,
maturity_date,
status_id,
retest_date,
age,
item_size,
color,
volume,
volume_uom,
place_of_origin,
best_by_date,
LENGTH,
length_uom,
width,
width_uom,
recycled_content,
thickness,
thickness_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_id,
territory_code,
vendor_name,
product_code,
product_transaction_id
)
SELECT p_transaction_id,
SYSDATE,
l_userid,
MLN.creation_date,
MLN.created_by,
MLN.last_update_login,
p_inventory_item_id,
p_organization_id,
p_transaction_date,
p_transaction_source_id,
p_transaction_source_type_id,
p_transaction_source_name,
ROUND ( ABS ( MTLT.transaction_quantity ), 5 ),
ROUND ( ABS ( MTLT.primary_quantity ), 5 ),
MLN.lot_number,
p_serial_transaction_id,
MLN.description,
MLN.supplier_lot_number,
MLN.origination_date,
MLN.date_code,
MLN.grade_code,
MLN.change_date,
MLN.maturity_date,
MLN.status_id,
MLN.retest_date,
MLN.age,
MLN.item_size,
MLN.color,
MLN.volume,
MLN.volume_uom,
MLN.place_of_origin,
MLN.best_by_date,
MLN.LENGTH,
MLN.length_uom,
MLN.width,
MLN.width_uom,
MLN.recycled_content,
MLN.thickness,
MLN.thickness_uom,
MLN.curl_wrinkle_fold,
MLN.lot_attribute_category,
MLN.c_attribute1,
MLN.c_attribute2,
MLN.c_attribute3,
MLN.c_attribute4,
MLN.c_attribute5,
MLN.c_attribute6,
MLN.c_attribute7,
MLN.c_attribute8,
MLN.c_attribute9,
MLN.c_attribute10,
MLN.c_attribute11,
MLN.c_attribute12,
MLN.c_attribute13,
MLN.c_attribute14,
MLN.c_attribute15,
MLN.c_attribute16,
MLN.c_attribute17,
MLN.c_attribute18,
MLN.c_attribute19,
MLN.c_attribute20,
MLN.d_attribute1,
MLN.d_attribute2,
MLN.d_attribute3,
MLN.d_attribute4,
MLN.d_attribute5,
MLN.d_attribute6,
MLN.d_attribute7,
MLN.d_attribute8,
MLN.d_attribute9,
MLN.d_attribute10,
MLN.n_attribute1,
MLN.n_attribute2,
MLN.n_attribute3,
MLN.n_attribute4,
MLN.n_attribute5,
MLN.n_attribute6,
MLN.n_attribute7,
MLN.n_attribute8,
MLN.n_attribute9,
MLN.n_attribute10,
MLN.vendor_id,
MLN.territory_code,
MLN.vendor_name,
mtlt.product_code,
mtlt.product_transaction_id
FROM mtl_lot_numbers MLN,
mtl_transaction_lots_temp MTLT
WHERE MLN.organization_id = p_organization_id
AND MLN.inventory_item_id = p_inventory_item_id
AND MLN.lot_number = p_lot_number
AND MTLT.lot_number = MLN.lot_number
AND MTLT.transaction_temp_id = p_transaction_temp_id
-- AND MTLT.LOT_NUMBER = p_lot_number
and not exists (
select null
from mtl_transaction_lot_numbers mtln
where mtln.lot_number = p_lot_number
and mtln.primary_quantity = round(abs(MTLT.PRIMARY_QUANTITY),5)
and mtln.inventory_item_id = p_inventory_item_id
and mtln.organization_id = p_organization_id
and mtln.transaction_date = p_transaction_date
and nvl(mtln.transaction_source_id,-1) = nvl(p_transaction_source_id,-1)
and nvl(mtln.transaction_source_type_id,-1) = nvl(p_transaction_source_type_id,-1)
and nvl(mtln.transaction_source_name,'$$$') = nvl(p_transaction_source_name,'$$$')
and mtln.transaction_id = p_transaction_id
);
(select NULL
from MTL_LOT_NUMBERS LOT
where LOT.lot_number = p_lot_number
and LOT.organization_id = p_organization_id
and LOT.inventory_item_id = p_inventory_item_id);
INSERT INTO mtl_transaction_lot_numbers
(
transaction_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
inventory_item_id,
organization_id,
transaction_date,
transaction_source_id,
transaction_source_type_id,
transaction_source_name,
transaction_quantity,
primary_quantity,
lot_number,
serial_transaction_id,
description,
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,
product_code,
product_transaction_id
)
SELECT p_transaction_id,
SYSDATE,
l_userid,
SYSDATE,
l_userid,
l_loginid,
p_inventory_item_id,
p_organization_id,
p_transaction_date,
p_transaction_source_id,
p_transaction_source_type_id,
p_transaction_source_name,
DECODE (
l_mmtt_pri_quantity,
1, ROUND (
( ABS ( transaction_quantity ) * -1 ),
5
),
ROUND ( ABS ( transaction_quantity ), 5 )
),
DECODE (
l_mmtt_pri_quantity,
1, ROUND ( ( ABS ( primary_quantity ) * -1 ), 5 ),
ROUND ( ABS ( primary_quantity ), 5 )
),
p_lot_number,
p_serial_transaction_id,
description,
TO_NUMBER ( g_lot_attributes_tbl ( 1 ).column_value ),
g_lot_attributes_tbl ( 2 ).column_value,
fnd_date.canonical_to_date (
g_lot_attributes_tbl ( 3 ).column_value
),
g_lot_attributes_tbl ( 4 ).column_value,
TO_NUMBER ( g_lot_attributes_tbl ( 5 ).column_value ),
fnd_date.canonical_to_date (
g_lot_attributes_tbl ( 6 ).column_value
),
TO_NUMBER ( g_lot_attributes_tbl ( 7 ).column_value ),
fnd_date.canonical_to_date (
g_lot_attributes_tbl ( 8 ).column_value
),
fnd_date.canonical_to_date (
g_lot_attributes_tbl ( 9 ).column_value
),
g_lot_attributes_tbl ( 10 ).column_value,
TO_NUMBER ( g_lot_attributes_tbl ( 11 ).column_value ),
g_lot_attributes_tbl ( 12 ).column_value,
TO_NUMBER ( g_lot_attributes_tbl ( 13 ).column_value ),
g_lot_attributes_tbl ( 14 ).column_value,
g_lot_attributes_tbl ( 15 ).column_value,
fnd_date.canonical_to_date (
g_lot_attributes_tbl ( 16 ).column_value
),
TO_NUMBER ( g_lot_attributes_tbl ( 17 ).column_value ),
g_lot_attributes_tbl ( 18 ).column_value,
TO_NUMBER ( g_lot_attributes_tbl ( 19 ).column_value ),
TO_NUMBER ( g_lot_attributes_tbl ( 20 ).column_value ),
g_lot_attributes_tbl ( 21 ).column_value,
TO_NUMBER ( g_lot_attributes_tbl ( 22 ).column_value ),
g_lot_attributes_tbl ( 23 ).column_value,
g_lot_attributes_tbl ( 24 ).column_value,
g_lot_attributes_tbl ( 25 ).column_value,
g_lot_attributes_tbl ( 26 ).column_value,
g_lot_attributes_tbl ( 27 ).column_value,
g_lot_attributes_tbl ( 28 ).column_value,
g_lot_attributes_tbl ( 29 ).column_value,
g_lot_attributes_tbl ( 30 ).column_value,
g_lot_attributes_tbl ( 31 ).column_value,
g_lot_attributes_tbl ( 32 ).column_value,
g_lot_attributes_tbl ( 33 ).column_value,
g_lot_attributes_tbl ( 34 ).column_value,
g_lot_attributes_tbl ( 35 ).column_value,
g_lot_attributes_tbl ( 36 ).column_value,
g_lot_attributes_tbl ( 37 ).column_value,
g_lot_attributes_tbl ( 38 ).column_value,
g_lot_attributes_tbl ( 39 ).column_value,
g_lot_attributes_tbl ( 40 ).column_value,
g_lot_attributes_tbl ( 41 ).column_value,
g_lot_attributes_tbl ( 42 ).column_value,
g_lot_attributes_tbl ( 43 ).column_value,
g_lot_attributes_tbl ( 44 ).column_value,
fnd_date.canonical_to_date (
g_lot_attributes_tbl ( 45 ).column_value
),
fnd_date.canonical_to_date (
g_lot_attributes_tbl ( 46 ).column_value
),
fnd_date.canonical_to_date (
g_lot_attributes_tbl ( 47 ).column_value
),
fnd_date.canonical_to_date (
g_lot_attributes_tbl ( 48 ).column_value
),
fnd_date.canonical_to_date (
g_lot_attributes_tbl ( 49 ).column_value
),
fnd_date.canonical_to_date (
g_lot_attributes_tbl ( 50 ).column_value
),
fnd_date.canonical_to_date (
g_lot_attributes_tbl ( 51 ).column_value
),
fnd_date.canonical_to_date (
g_lot_attributes_tbl ( 52 ).column_value
),
fnd_date.canonical_to_date (
g_lot_attributes_tbl ( 53 ).column_value
),
fnd_date.canonical_to_date (
g_lot_attributes_tbl ( 54 ).column_value
),
TO_NUMBER ( g_lot_attributes_tbl ( 55 ).column_value ),
TO_NUMBER ( g_lot_attributes_tbl ( 56 ).column_value ),
TO_NUMBER ( g_lot_attributes_tbl ( 57 ).column_value ),
TO_NUMBER ( g_lot_attributes_tbl ( 58 ).column_value ),
TO_NUMBER ( g_lot_attributes_tbl ( 59 ).column_value ),
TO_NUMBER ( g_lot_attributes_tbl ( 60 ).column_value ),
TO_NUMBER ( g_lot_attributes_tbl ( 61 ).column_value ),
TO_NUMBER ( g_lot_attributes_tbl ( 62 ).column_value ),
TO_NUMBER ( g_lot_attributes_tbl ( 63 ).column_value ),
g_lot_attributes_tbl ( 64 ).column_value,
TO_NUMBER ( g_lot_attributes_tbl ( 65 ).column_value ),
g_lot_attributes_tbl ( 66 ).column_value,
g_lot_attributes_tbl ( 67 ).column_value,
mtlt.product_code,
mtlt.product_transaction_id
FROM mtl_transaction_lots_temp mtlt
WHERE transaction_temp_id = p_transaction_temp_id
AND lot_number = p_lot_number
-- Hack for bug 2130268
-- What if any transaction has the same lot 'p_lot_number' twice !!
-- The insert statement then inserts two records instead of one,
-- hence the following 'not exists' clause
AND NOT EXISTS (
SELECT NULL
FROM mtl_transaction_lot_numbers mtln
WHERE mtln.lot_number = p_lot_number
AND mtln.primary_quantity =
DECODE (
l_mmtt_pri_quantity,
1, ROUND (
( ABS (
mtlt.primary_quantity
)
* -1
),
5
),
ROUND (
ABS (
mtlt.primary_quantity
),
5
)
)
AND mtln.inventory_item_id =
p_inventory_item_id
AND mtln.organization_id = p_organization_id
AND mtln.transaction_date =
p_transaction_date
AND NVL ( mtln.transaction_source_id, -1 ) =
NVL ( p_transaction_source_id, -1 )
AND NVL (
mtln.transaction_source_type_id,
-1
) = NVL (
p_transaction_source_type_id,
-1
)
AND NVL (
mtln.transaction_source_name,
'$$$'
) = NVL (
p_transaction_source_name,
'$$$'
)
AND mtln.transaction_id = p_transaction_id );
ROLLBACK TO apiinsertlot_apipub;
fnd_msg_pub.add_exc_msg ('INV_LOT_API_PUB' , 'insertLot' );
END inserttrxlot;
SELECT lot_number
FROM mtl_lot_numbers
WHERE lot_number = p_auto_lot_number
AND inventory_item_id <> p_inventory_item_id;
SELECT lot_number
FROM mtl_transaction_lot_numbers
WHERE lot_number = p_auto_lot_number
AND inventory_item_id <> p_inventory_item_id;
SELECT lot.lot_number
FROM mtl_transaction_lots_temp lot,
mtl_material_transactions_temp mmtt
WHERE lot.transaction_temp_id = mmtt.transaction_temp_id
AND lot.lot_number = p_auto_lot_number
AND mmtt.inventory_item_id <> p_inventory_item_id;
SELECT lot_number
FROM mtl_material_transactions_temp lot
WHERE lot_number = p_auto_lot_number
AND inventory_item_id <> p_inventory_item_id;
SELECT lot_number_uniqueness
INTO l_lot_uniqueness
FROM mtl_parameters
WHERE organization_id = p_org_id;
PROCEDURE update_msi (
p_org_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_new_suffix IN VARCHAR2,
p_lot_uniqueness IN NUMBER DEFAULT NULL,
p_lot_generation IN NUMBER DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2,
p_lot_prefix IN VARCHAR2 -- Bug# 7298723
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_update_count number := 0; -- Bug# 7298723
UPDATE mtl_system_items_b
SET start_auto_lot_number = p_new_suffix
-- Bug# 7298723
-- WHERE organization_id = p_org_id
WHERE auto_lot_alpha_prefix = p_lot_prefix
AND lot_control_code = 2;
l_update_count := SQL%ROWCOUNT; -- Bug# 7298723
UPDATE mtl_system_items
SET start_auto_lot_number = p_new_suffix
WHERE organization_id = p_org_id
AND inventory_item_id = p_inventory_item_id;
l_update_count := SQL%ROWCOUNT; -- Bug# 7298723
IF l_update_count > 0 THEN -- Bug# 7298723
IF (l_debug =1 ) THEN
print_debug('commiting',9);
Autonomous function to insert to mtl_child_lot_numbers.
Added by Joe DiIorio for OPM Convergence. 05/18/2004
=============================================================*/
FUNCTION ins_mtl_child_lot_num (
p_org_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_parent_lot_number IN VARCHAR2,
p_last_child_lot_seq IN NUMBER
)
RETURN NUMBER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
INSERT into mtl_child_lot_numbers
(organization_id, inventory_item_id,
parent_lot_number, last_child_lot_number_seq,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login)
VALUES (p_org_id, p_inventory_item_id, p_parent_lot_number,
p_last_child_lot_seq, SYSDATE, l_userid,
SYSDATE, l_userid, l_loginid);
Autonomous function to update to mtl_child_lot_numbers.
Added by Joe DiIorio for OPM Convergence. 05/18/2004
=============================================================*/
FUNCTION upd_mtl_child_lot_num (
p_org_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_parent_lot_number IN VARCHAR2,
p_last_child_lot_seq IN NUMBER
)
RETURN NUMBER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE mtl_child_lot_numbers
SET last_child_lot_number_seq = p_last_child_lot_seq,
last_updated_by = l_userid,
last_update_date = SYSDATE,
last_update_login = l_loginid
WHERE organization_id = p_org_id
AND inventory_item_id = p_inventory_item_id
AND parent_lot_number = p_parent_lot_number;
SELECT lot.lot_number
FROM mtl_transaction_lots_temp lot,
mtl_material_transactions_temp mmtt
WHERE lot.transaction_temp_id = mmtt.transaction_temp_id
AND lot.lot_number = p_parent_lot_number
AND inventory_item_id = p_inventory_item_id;
SELECT lot_number
FROM mtl_lot_numbers
WHERE lot_number = p_parent_lot_number
AND organization_id = p_org_id
AND inventory_item_id = p_inventory_item_id;
SELECT child_lot_flag, parent_child_generation_flag,
child_lot_prefix, child_lot_starting_number
FROM mtl_system_items_b -- NSRIVAST, Changed the name to MTL_SYSTEM_ITEMS_B as per review comments by Shelly
WHERE organization_id = p_org_id
AND inventory_item_id = p_inventory_item_id;
SELECT lot_number_generation,
parent_child_generation_flag,
child_lot_zero_padding_flag,
child_lot_alpha_prefix, NVL(child_lot_number_length,80)
FROM mtl_parameters
WHERE organization_id = p_org_id;
SELECT count( 1 )
FROM mtl_lot_numbers
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_org_id
AND lot_number = interim_child_lot_number;
to insert/update child lot table.
====================================*/
l_ret NUMBER;
SELECT last_child_lot_number_seq
FROM mtl_child_lot_numbers
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_org_id
AND parent_lot_number = p_parent_lot_number;
Insert mtl_child_lot_numbers record.
=======================================*/
l_ret := ins_mtl_child_lot_num(p_org_id,
p_inventory_item_id,
p_parent_lot_number,
x_last_child_seq);
Update mtl_child_lot_numbers record.
=======================================*/
l_ret := upd_mtl_child_lot_num(p_org_id,
p_inventory_item_id,
p_parent_lot_number,
x_last_child_seq);
Insert mtl_child_lot_numbers record.
=======================================*/
l_ret := ins_mtl_child_lot_num(p_org_id,
p_inventory_item_id,
p_parent_lot_number,
x_last_child_seq);
Update mtl_child_lot_numbers record.
=======================================*/
l_ret := upd_mtl_child_lot_num(p_org_id,
p_inventory_item_id,
p_parent_lot_number,
x_last_child_seq);
SELECT lot_number_generation,
lot_number_uniqueness,
NVL ( lot_number_zero_padding, 2 ),
NVL ( lot_number_length, 80 ),
auto_lot_alpha_prefix
INTO lot_generation,
lot_uniqueness,
zero_pad,
lot_length,
lot_prefix
FROM mtl_parameters
WHERE organization_id = p_org_id;
SELECT lot_control_code
INTO l_lot_control_code
FROM mtl_system_items
WHERE organization_id = p_org_id
AND inventory_item_id = p_inventory_item_id;
SELECT auto_lot_alpha_prefix,
start_auto_lot_number
INTO l_lot_prefix,
l_lot_suffix
FROM mtl_system_items
WHERE organization_id = p_org_id
AND inventory_item_id = p_inventory_item_id;
print_debug('setting new lot number..calling update_msi with params' ||
p_org_id || ' ' || p_inventory_item_id || ' ' || l_new_suffix,9);
update_msi (
p_org_id,
p_inventory_item_id,
l_new_suffix,
lot_uniqueness,
lot_generation,
x_return_status,
l_lot_prefix -- Bug# 7298723
);
SELECT ORGANIZATION_CODE, CONCATENATED_SEGMENTS
INTO v_org_code, v_item_name
FROM MTL_PARAMETERS ORG, MTL_SYSTEM_ITEMS_KFV ITEM
WHERE ORG.ORGANIZATION_ID = p_org_id
AND INVENTORY_ITEM_ID = p_inventory_item_id
AND ITEM.ORGANIZATION_ID = p_org_id;
SELECT TO_CHAR ( mtl_lot_numeric_suffix_s.NEXTVAL )
INTO l_lot_suffix
FROM sys.DUAL;
/* SELECT COUNT ( 1 )
INTO l_lotcount
FROM mtl_lot_numbers
WHERE
inventory_item_id = p_inventory_item_id AND
organization_id = p_org_id AND
lot_number = auto_lot_number;
SELECT lot_number
FROM mtl_lot_numbers
WHERE lot_number = p_auto_lot_number
AND inventory_item_id = p_inventory_item_id;
SELECT lot_number
FROM mtl_transaction_lot_numbers
WHERE lot_number = p_auto_lot_number
AND inventory_item_id = p_inventory_item_id;
SELECT lot.lot_number
FROM mtl_transaction_lots_temp lot,
mtl_material_transactions_temp mmtt
WHERE lot.transaction_temp_id = mmtt.transaction_temp_id
AND lot.lot_number = p_auto_lot_number
AND mmtt.inventory_item_id = p_inventory_item_id;
SELECT lot_number
FROM mtl_material_transactions_temp lot
WHERE lot_number = p_auto_lot_number
AND inventory_item_id = p_inventory_item_id;
SELECT lot_number_uniqueness
INTO l_lot_uniqueness
FROM mtl_parameters
WHERE organization_id = p_org_id;
SELECT TO_CHAR ( vendor_id ),
grade_code,
fnd_date.date_to_canonical ( origination_date ),
date_code,
TO_CHAR ( status_id ),
fnd_date.date_to_canonical ( change_date ),
TO_NUMBER ( age ),
fnd_date.date_to_canonical ( retest_date ),
fnd_date.date_to_canonical ( maturity_date ),
lot_attribute_category,
TO_CHAR ( item_size ),
color,
TO_CHAR ( volume ),
volume_uom,
place_of_origin,
fnd_date.date_to_canonical ( best_by_date ),
TO_CHAR ( LENGTH ),
length_uom,
TO_CHAR ( recycled_content ),
TO_CHAR ( thickness ),
thickness_uom,
TO_CHAR ( 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,
fnd_date.date_to_canonical ( d_attribute1 ),
fnd_date.date_to_canonical ( d_attribute2 ),
fnd_date.date_to_canonical ( d_attribute3 ),
fnd_date.date_to_canonical ( d_attribute4 ),
fnd_date.date_to_canonical ( d_attribute5 ),
fnd_date.date_to_canonical ( d_attribute6 ),
fnd_date.date_to_canonical ( d_attribute7 ),
fnd_date.date_to_canonical ( d_attribute8 ),
fnd_date.date_to_canonical ( d_attribute9 ),
fnd_date.date_to_canonical ( d_attribute10 ),
TO_CHAR ( n_attribute1 ),
TO_CHAR ( n_attribute2 ),
TO_CHAR ( n_attribute3 ),
TO_CHAR ( n_attribute4 ),
TO_CHAR ( n_attribute5 ),
TO_CHAR ( n_attribute6 ),
TO_CHAR ( n_attribute7 ),
TO_CHAR ( n_attribute8 ),
TO_CHAR ( n_attribute10 ),
supplier_lot_number,
TO_CHAR ( 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 temp_tbl ( 1 ).column_value,
temp_tbl ( 2 ).column_value,
temp_tbl ( 3 ).column_value,
temp_tbl ( 4 ).column_value,
temp_tbl ( 5 ).column_value,
temp_tbl ( 6 ).column_value,
temp_tbl ( 7 ).column_value,
temp_tbl ( 8 ).column_value,
temp_tbl ( 9 ).column_value,
temp_tbl ( 10 ).column_value,
temp_tbl ( 11 ).column_value,
temp_tbl ( 12 ).column_value,
temp_tbl ( 13 ).column_value,
temp_tbl ( 14 ).column_value,
temp_tbl ( 15 ).column_value,
temp_tbl ( 16 ).column_value,
temp_tbl ( 17 ).column_value,
temp_tbl ( 18 ).column_value,
temp_tbl ( 19 ).column_value,
temp_tbl ( 20 ).column_value,
temp_tbl ( 21 ).column_value,
temp_tbl ( 22 ).column_value,
temp_tbl ( 23 ).column_value,
temp_tbl ( 24 ).column_value,
temp_tbl ( 25 ).column_value,
temp_tbl ( 26 ).column_value,
temp_tbl ( 27 ).column_value,
temp_tbl ( 28 ).column_value,
temp_tbl ( 29 ).column_value,
temp_tbl ( 30 ).column_value,
temp_tbl ( 31 ).column_value,
temp_tbl ( 32 ).column_value,
temp_tbl ( 33 ).column_value,
temp_tbl ( 34 ).column_value,
temp_tbl ( 35 ).column_value,
temp_tbl ( 36 ).column_value,
temp_tbl ( 37 ).column_value,
temp_tbl ( 38 ).column_value,
temp_tbl ( 39 ).column_value,
temp_tbl ( 40 ).column_value,
temp_tbl ( 41 ).column_value,
temp_tbl ( 42 ).column_value,
temp_tbl ( 43 ).column_value,
temp_tbl ( 44 ).column_value,
temp_tbl ( 45 ).column_value,
temp_tbl ( 46 ).column_value,
temp_tbl ( 47 ).column_value,
temp_tbl ( 48 ).column_value,
temp_tbl ( 49 ).column_value,
temp_tbl ( 50 ).column_value,
temp_tbl ( 51 ).column_value,
temp_tbl ( 52 ).column_value,
temp_tbl ( 53 ).column_value,
temp_tbl ( 54 ).column_value,
temp_tbl ( 55 ).column_value,
temp_tbl ( 56 ).column_value,
temp_tbl ( 57 ).column_value,
temp_tbl ( 58 ).column_value,
temp_tbl ( 59 ).column_value,
temp_tbl ( 60 ).column_value,
temp_tbl ( 61 ).column_value,
temp_tbl ( 62 ).column_value,
temp_tbl ( 63 ).column_value,
temp_tbl ( 64 ).column_value,
temp_tbl ( 65 ).column_value,
temp_tbl ( 66 ).column_value,
temp_tbl ( 67 ).column_value,
temp_tbl ( 68 ).column_value,
x_source_non_wms_lot_att_rec.attribute_category,
x_source_non_wms_lot_att_rec.attribute1,
x_source_non_wms_lot_att_rec.attribute2,
x_source_non_wms_lot_att_rec.attribute3,
x_source_non_wms_lot_att_rec.attribute4,
x_source_non_wms_lot_att_rec.attribute5,
x_source_non_wms_lot_att_rec.attribute6,
x_source_non_wms_lot_att_rec.attribute7,
x_source_non_wms_lot_att_rec.attribute8,
x_source_non_wms_lot_att_rec.attribute9,
x_source_non_wms_lot_att_rec.attribute10,
x_source_non_wms_lot_att_rec.attribute11,
x_source_non_wms_lot_att_rec.attribute12,
x_source_non_wms_lot_att_rec.attribute13,
x_source_non_wms_lot_att_rec.attribute14,
x_source_non_wms_lot_att_rec.attribute15
FROM mtl_lot_numbers
WHERE organization_id = p_from_organization_id
AND inventory_item_id = p_inventory_item_id
AND lot_number = p_lot_number;
print_debug('The selected lot_number, organization, item combination doesnt exist in MLN',4);
SELECT lot_control_code
INTO l_lot_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT lot_number_uniqueness
INTO l_lot_number_uniqueness
FROM mtl_parameters
WHERE organization_id = p_organization_id;
PROCEDURE update_inv_lot(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, x_lot_rec OUT NOCOPY MTL_LOT_NUMBERS%ROWTYPE
, p_lot_rec IN MTL_LOT_NUMBERS%ROWTYPE
, p_source IN NUMBER
, p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := fnd_api.g_false
, p_commit IN VARCHAR2 := fnd_api.g_false
)
IS
CURSOR inv_attributes_cur IS
SELECT attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
FROM mtl_lot_numbers
WHERE inventory_item_id = p_lot_rec.inventory_item_id
AND organization_id = p_lot_rec.organization_id
AND lot_number = p_lot_rec.lot_number;
SELECT 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
FROM mtl_lot_numbers
WHERE inventory_item_id = p_lot_rec.inventory_item_id
AND organization_id = p_lot_rec.organization_id
AND lot_number = p_lot_rec.lot_number;
SELECT n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
FROM mtl_lot_numbers
WHERE inventory_item_id = p_lot_rec.inventory_item_id
AND organization_id = p_lot_rec.organization_id
AND lot_number = p_lot_rec.lot_number;
SELECT d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
FROM mtl_lot_numbers
WHERE inventory_item_id = p_lot_rec.inventory_item_id
AND organization_id = p_lot_rec.organization_id
AND lot_number = p_lot_rec.lot_number;
SELECT attribute_category
, lot_attribute_category
FROM mtl_lot_numbers
WHERE inventory_item_id = p_lot_rec.inventory_item_id
AND organization_id = p_lot_rec.organization_id
AND lot_number = p_lot_rec.lot_number;
SELECT grade_code
, DISABLE_FLAG
, origination_date
, date_code
, change_date
, age
, retest_date
, maturity_date
, item_size
, color
, volume
, volume_uom
, place_of_origin
, best_by_date
, length
, length_uom
, recycled_content
, thickness
, thickness_uom
, width
, width_uom
, territory_code
, supplier_lot_number
, VENDOR_NAME
FROM MTL_LOT_NUMBERS
WHERE inventory_item_id = p_lot_rec.inventory_item_id
AND organization_id = p_lot_rec.organization_id
AND lot_number = p_lot_rec.lot_number;
print_debug(p_err_msg => 'Update Lot Attr: The value of the input parametsrs are :', p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of the INVENTORY_ITEM_ID : ' || p_lot_rec.inventory_item_id, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of ORGANIZATION_ID :' || p_lot_rec.organization_id, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of LOT_NUMBER :' || p_lot_rec.lot_number, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of PARENT LOT_NUMBER :' || p_lot_rec.parent_lot_number, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of the EXPIRATION_DATE :' || p_lot_rec.expiration_date, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of the DISABLE_FLAG :' || p_lot_rec.disable_flag, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of the ATTRIBUTE_CATEGORY :' || p_lot_rec.attribute_category, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of the LOT_ATTRIBUTE_CATEGORY :' || p_lot_rec.lot_attribute_category, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of the GRADE_CODE :' || p_lot_rec.grade_code, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of the ORIGINATION_DATE :' || p_lot_rec.origination_date, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of the DATE_CODE :' || p_lot_rec.date_code, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of the STATUS_ID :' || p_lot_rec.status_id, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of the CHANGE_DATE :' || p_lot_rec.change_date, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of the AGE :' || p_lot_rec.age, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of the RETEST_DATE :' || p_lot_rec.retest_date, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of the MATURITY_DATE :' || p_lot_rec.maturity_date, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of the ITEM_SIZE :' || p_lot_rec.item_size, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of COLOR :' || p_lot_rec.color, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of VOLUME :' || p_lot_rec.volume, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of VOLUME_UOM :' || p_lot_rec.volume_uom, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of PLACE_OF_ORIGIN :' || p_lot_rec.place_of_origin, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of BEST_BY_DATE :' || p_lot_rec.best_by_date, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of LENGTH :' || p_lot_rec.length, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of LENGTH_UOM:' || p_lot_rec.length_uom, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of RECYCLED_CONTENT :' || p_lot_rec.recycled_content, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of THICKNESS :' || p_lot_rec.thickness, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of THICKNESS_UOM :' || p_lot_rec.thickness_uom, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of WIDTH :' || p_lot_rec.width, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of WIDTH_UOM :' || p_lot_rec.width_uom, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of Territory Code :' || p_lot_rec.territory_code, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of VENDOR_NAME :' || p_lot_rec.vendor_name, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of SUPPLIER_LOT_NUMBER :' || p_lot_rec.supplier_lot_number, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of SUPPLIER_LOT_NUMBER :' || p_lot_rec.supplier_lot_number, p_level => 9);
print_debug(p_err_msg => 'Update Lot Attr: The value of P_SOURCE :' || p_source, p_level => 9);
SELECT 1
INTO l_chk
FROM mtl_lot_numbers
WHERE inventory_item_id = p_lot_rec.inventory_item_id
AND organization_id = p_lot_rec.organization_id
AND lot_number = p_lot_rec.lot_number;
SELECT shelf_life_days
, shelf_life_code
INTO l_shelf_life_days
, l_shelf_life_code
FROM mtl_system_items
WHERE inventory_item_id = p_lot_rec.inventory_item_id
AND organization_id = p_lot_rec.organization_id;
print_debug('Update Lot Attr: Program validate_lot_attr_in_param has failed with a user defined exception', 9);
print_debug('Update Lot Attr: Program validate_lot_attr_in_param has failed with a Unexpected exception', 9);
UPDATE mtl_lot_numbers
SET expiration_date =
DECODE(l_expiration_date, NULL, expiration_date, l_expiration_date )
, disable_flag =
DECODE(
p_source
, 2, DECODE(p_lot_rec.disable_flag, g_miss_num, NULL, NULL, disable_flag, p_lot_rec.disable_flag) /*Bug 14835444 changed g_miss_char to g_miss_num */
, DECODE(p_lot_rec.disable_flag, g_miss_num, disable_flag, NULL, NULL, p_lot_rec.disable_flag)
)
, attribute_category =
DECODE(
p_source
, 2, DECODE(p_lot_rec.attribute_category, g_miss_char, NULL, NULL, attribute_category, p_lot_rec.attribute_category)
, DECODE(p_lot_rec.attribute_category, g_miss_char, attribute_category, NULL, NULL, p_lot_rec.attribute_category)
)
, lot_attribute_category =
DECODE(
p_source
, 2, DECODE(p_lot_rec.lot_attribute_category, g_miss_char, NULL, NULL, lot_attribute_category, p_lot_rec.lot_attribute_category)
, DECODE(p_lot_rec.lot_attribute_category, g_miss_char, lot_attribute_category, NULL, NULL, p_lot_rec.lot_attribute_category)
)
/*, grade_code =
DECODE(
p_source
, 2, DECODE(p_lot_rec.grade_code, g_miss_char, NULL, NULL, grade_code, p_lot_rec.grade_code)
, DECODE(p_lot_rec.grade_code, g_miss_char, grade_code, NULL, NULL, p_lot_rec.grade_code)
)*/ -- Commented for bug 13780398
, origination_date =
DECODE(
p_source
, 2, DECODE(p_lot_rec.origination_date, g_miss_date,To_Date(NULL), NULL, origination_date, p_lot_rec.origination_date)
, DECODE(p_lot_rec.origination_date, g_miss_date, origination_date, NULL, To_Date(NULL), p_lot_rec.origination_date)
)
, date_code =
DECODE(
p_source
, 2, DECODE(p_lot_rec.date_code, g_miss_char, NULL, NULL, date_code, p_lot_rec.date_code)
, DECODE(p_lot_rec.date_code, g_miss_char, date_code, NULL, NULL, p_lot_rec.date_code)
)
/* Bug 8198497- Removed the code as we are updating the status by calling validate_lot_status */
, change_date =
DECODE(
p_source
, 2, DECODE(p_lot_rec.change_date, g_miss_date, To_Date(NULL), NULL, change_date, p_lot_rec.change_date)
, DECODE(p_lot_rec.change_date, g_miss_date, change_date, NULL, To_Date(NULL), p_lot_rec.change_date)
)
, age = DECODE(
p_source
, 2, DECODE(p_lot_rec.age, g_miss_num, NULL, NULL, age, p_lot_rec.age)
, DECODE(p_lot_rec.age, g_miss_num, age, NULL, NULL, p_lot_rec.age)
)
, retest_date =
DECODE(
p_source
, 2, DECODE(p_lot_rec.retest_date, g_miss_date, To_Date(NULL), NULL, retest_date, p_lot_rec.retest_date)
, DECODE(p_lot_rec.retest_date, g_miss_date, retest_date, NULL, To_Date(NULL), p_lot_rec.retest_date)
)
, maturity_date =
DECODE(
p_source
, 2, DECODE(p_lot_rec.maturity_date, g_miss_date, To_Date(NULL), NULL, maturity_date, p_lot_rec.maturity_date)
, DECODE(p_lot_rec.maturity_date, g_miss_date, maturity_date, NULL, To_Date(NULL), p_lot_rec.maturity_date)
)
, item_size =
DECODE(
p_source
, 2, DECODE(p_lot_rec.item_size, g_miss_num, NULL, NULL, item_size, p_lot_rec.item_size)
, DECODE(p_lot_rec.item_size, g_miss_num, item_size, NULL, NULL, p_lot_rec.item_size)
)
, color =
DECODE(
p_source
, 2, DECODE(p_lot_rec.color, g_miss_char, NULL, NULL, color, p_lot_rec.color)
, DECODE(p_lot_rec.color, g_miss_char, color, NULL, NULL, p_lot_rec.color)
)
, volume =
DECODE(
p_source
, 2, DECODE(p_lot_rec.volume, g_miss_num, NULL, NULL, volume, p_lot_rec.volume)
, DECODE(p_lot_rec.volume, g_miss_num, volume, NULL, NULL, p_lot_rec.volume)
)
, volume_uom =
DECODE(
p_source
, 2, DECODE(p_lot_rec.volume_uom, g_miss_char, NULL, NULL, volume_uom, p_lot_rec.volume_uom)
, DECODE(p_lot_rec.volume_uom, g_miss_char, volume_uom, NULL, NULL, p_lot_rec.volume_uom)
)
, place_of_origin =
DECODE(
p_source
, 2, DECODE(p_lot_rec.place_of_origin, g_miss_char, NULL, NULL, place_of_origin, p_lot_rec.place_of_origin)
, DECODE(p_lot_rec.place_of_origin, g_miss_char, place_of_origin, NULL, place_of_origin, p_lot_rec.place_of_origin)
)
, best_by_date =
DECODE(
p_source
, 2, DECODE(p_lot_rec.best_by_date, g_miss_date, To_Date(NULL), NULL, best_by_date, p_lot_rec.best_by_date)
, DECODE(p_lot_rec.best_by_date, g_miss_date, best_by_date, NULL, To_Date(NULL), p_lot_rec.best_by_date)
)
, LENGTH =
DECODE(
p_source
, 2, DECODE(p_lot_rec.length, g_miss_num, NULL, NULL, LENGTH, p_lot_rec.length)
, DECODE(p_lot_rec.length, g_miss_num, LENGTH, NULL, NULL, p_lot_rec.length)
)
, length_uom =
DECODE(
p_source
, 2, DECODE(p_lot_rec.length_uom, g_miss_char, NULL, NULL, length_uom, p_lot_rec.length_uom)
, DECODE(p_lot_rec.length_uom, g_miss_char, length_uom, NULL, NULL, p_lot_rec.length_uom)
)
, recycled_content =
DECODE(
p_source
, 2, DECODE(p_lot_rec.recycled_content, g_miss_num, NULL, NULL, recycled_content, p_lot_rec.recycled_content)
, DECODE(p_lot_rec.recycled_content, g_miss_num, recycled_content, NULL, NULL, p_lot_rec.recycled_content)
)
, thickness =
DECODE(
p_source
, 2, DECODE(p_lot_rec.thickness, g_miss_num, NULL, NULL, thickness, p_lot_rec.thickness)
, DECODE(p_lot_rec.thickness, g_miss_num, thickness, NULL, NULL, p_lot_rec.thickness)
)
, thickness_uom =
DECODE(
p_source
, 2, DECODE(p_lot_rec.thickness_uom, g_miss_char, NULL, NULL, thickness_uom, p_lot_rec.thickness_uom)
, DECODE(p_lot_rec.thickness_uom, g_miss_char, thickness_uom, NULL, NULL, p_lot_rec.thickness_uom)
)
, width =
DECODE(
p_source
, 2, DECODE(p_lot_rec.width, g_miss_num, NULL, NULL, width, p_lot_rec.width)
, DECODE(p_lot_rec.width, g_miss_num, width, NULL, NULL, p_lot_rec.width)
)
, width_uom =
DECODE(
p_source
, 2, DECODE(p_lot_rec.width_uom, g_miss_char, NULL, NULL, width_uom, p_lot_rec.width_uom)
, DECODE(p_lot_rec.width_uom, g_miss_char, width_uom, NULL, NULL, p_lot_rec.width_uom)
)
, territory_code =
DECODE(
p_source
, 2, DECODE(p_lot_rec.territory_code, g_miss_char, NULL, NULL, territory_code, p_lot_rec.territory_code)
, DECODE(p_lot_rec.territory_code, g_miss_char, territory_code, NULL, NULL, p_lot_rec.territory_code)
)
, supplier_lot_number =
DECODE(
p_source
, 2, DECODE(p_lot_rec.supplier_lot_number, g_miss_char, NULL, NULL, supplier_lot_number, p_lot_rec.supplier_lot_number)
, DECODE(p_lot_rec.supplier_lot_number, g_miss_char, supplier_lot_number, NULL, NULL, p_lot_rec.supplier_lot_number)
)
, vendor_name =
DECODE(
p_source
, 2, DECODE(p_lot_rec.vendor_name, g_miss_char, NULL, NULL, vendor_name, p_lot_rec.vendor_name)
, DECODE(p_lot_rec.vendor_name, g_miss_char, vendor_name, NULL, NULL, p_lot_rec.vendor_name)
)
-- Bug 6983527 - Parent lot number should never be updated.
/*Bug 8311729 Uncommenting the below code as we should be able to
update the mistakenly entered parent lot information */
-- nsinghi bug#5209065. Update new lot attributes
, parent_lot_number =
DECODE(
p_source
, 2, DECODE(p_lot_rec.parent_lot_number, g_miss_char, NULL, NULL, parent_lot_number, p_lot_rec.parent_lot_number)
, DECODE(p_lot_rec.parent_lot_number, g_miss_char, parent_lot_number, NULL, NULL, p_lot_rec.parent_lot_number)
)
-- Bug 13553990 keeping the value of columns without getting updated to NULL which are not passed.
, origination_type =
DECODE(
p_source
, 2, DECODE(p_lot_rec.origination_type, g_miss_num, NULL, NULL, origination_type, p_lot_rec.origination_type)
, DECODE(p_lot_rec.origination_type, -99, origination_type, g_miss_num, origination_type, NULL, NULL, p_lot_rec.origination_type)
)
, availability_type =
DECODE(
p_source
, 2, DECODE(p_lot_rec.availability_type, g_miss_num, NULL, NULL, availability_type, p_lot_rec.availability_type)
, DECODE(p_lot_rec.availability_type, g_miss_num, availability_type, NULL, NULL, p_lot_rec.availability_type)
)
, expiration_action_code =
DECODE(
p_source
, 2, DECODE(p_lot_rec.expiration_action_code, g_miss_char, NULL, NULL, expiration_action_code, p_lot_rec.expiration_action_code)
, DECODE(p_lot_rec.expiration_action_code, g_miss_char, expiration_action_code, NULL, NULL, p_lot_rec.expiration_action_code)
)
, expiration_action_date =
DECODE(
p_source
, 2, DECODE(p_lot_rec.expiration_action_date, g_miss_date, To_Date(NULL), NULL, expiration_action_date, p_lot_rec.expiration_action_date)
, DECODE(p_lot_rec.expiration_action_date, g_miss_date, expiration_action_date, NULL, To_Date(NULL), p_lot_rec.expiration_action_date)
)
, hold_date =
DECODE(
p_source
, 2, DECODE(p_lot_rec.hold_date, g_miss_date, To_Date(NULL), NULL, hold_date, p_lot_rec.hold_date)
, DECODE(p_lot_rec.hold_date, g_miss_date, hold_date, NULL, To_Date(NULL), p_lot_rec.hold_date)
)
, inventory_atp_code =
DECODE(
p_source
, 2, DECODE(p_lot_rec.inventory_atp_code, g_miss_num, NULL, NULL, inventory_atp_code, p_lot_rec.inventory_atp_code)
, DECODE(p_lot_rec.inventory_atp_code, g_miss_num, inventory_atp_code, NULL, NULL, p_lot_rec.inventory_atp_code)
)
, reservable_type =
DECODE(
p_source
, 2, DECODE(p_lot_rec.reservable_type, g_miss_num, NULL, NULL, reservable_type, p_lot_rec.reservable_type)
, DECODE(p_lot_rec.reservable_type, g_miss_num, reservable_type, NULL, NULL, p_lot_rec.reservable_type)
)
, sampling_event_id =
DECODE(
p_source
, 2, DECODE(p_lot_rec.sampling_event_id, g_miss_num, NULL, NULL, sampling_event_id, p_lot_rec.sampling_event_id)
, DECODE(p_lot_rec.sampling_event_id, g_miss_num, sampling_event_id, NULL, NULL, p_lot_rec.sampling_event_id)
)
-- nsinghi bug#5209065. End.
, attribute1 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(1), g_miss_char, NULL, NULL, attribute1, l_inv_attributes_tbl(1))
, DECODE(l_inv_attributes_tbl(1), g_miss_char, attribute1, NULL, NULL, l_inv_attributes_tbl(1))
)
, attribute2 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(2), g_miss_char, NULL, NULL, attribute2, l_inv_attributes_tbl(2))
, DECODE(l_inv_attributes_tbl(2), g_miss_char, attribute2, NULL, NULL, l_inv_attributes_tbl(2))
)
, attribute3 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(3), g_miss_char, NULL, NULL, attribute3, l_inv_attributes_tbl(3))
, DECODE(l_inv_attributes_tbl(3), g_miss_char, attribute3, NULL, NULL, l_inv_attributes_tbl(3))
)
, attribute4 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(4), g_miss_char, NULL, NULL, attribute4, l_inv_attributes_tbl(4))
, DECODE(l_inv_attributes_tbl(4), g_miss_char, attribute4, NULL, NULL, l_inv_attributes_tbl(4))
)
, attribute5 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(5), g_miss_char, NULL, NULL, attribute5, l_inv_attributes_tbl(5))
, DECODE(l_inv_attributes_tbl(5), g_miss_char, attribute5, NULL, NULL, l_inv_attributes_tbl(5))
)
, attribute6 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(6), g_miss_char, NULL, NULL, attribute6, l_inv_attributes_tbl(6))
, DECODE(l_inv_attributes_tbl(6), g_miss_char, attribute6, NULL, NULL, l_inv_attributes_tbl(6))
)
, attribute7 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(7), g_miss_char, NULL, NULL, attribute7, l_inv_attributes_tbl(7))
, DECODE(l_inv_attributes_tbl(7), g_miss_char, attribute7, NULL, NULL, l_inv_attributes_tbl(7))
)
, attribute8 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(8), g_miss_char, NULL, NULL, attribute8, l_inv_attributes_tbl(8))
, DECODE(l_inv_attributes_tbl(8), g_miss_char, attribute8, NULL, NULL, l_inv_attributes_tbl(8))
)
, attribute9 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(9), g_miss_char, NULL, NULL, attribute9, l_inv_attributes_tbl(9))
, DECODE(l_inv_attributes_tbl(9), g_miss_char, attribute9, NULL, NULL, l_inv_attributes_tbl(9))
)
, attribute10 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(10), g_miss_char, NULL, NULL, attribute10, l_inv_attributes_tbl(10))
, DECODE(l_inv_attributes_tbl(10), g_miss_char, attribute10, NULL, NULL, l_inv_attributes_tbl(10))
)
, attribute11 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(11), g_miss_char, NULL, NULL, attribute11, l_inv_attributes_tbl(11))
, DECODE(l_inv_attributes_tbl(11), g_miss_char, attribute11, NULL, NULL, l_inv_attributes_tbl(11))
)
, attribute12 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(12), g_miss_char, NULL, NULL, attribute12, l_inv_attributes_tbl(12))
, DECODE(l_inv_attributes_tbl(12), g_miss_char, attribute12, NULL, NULL, l_inv_attributes_tbl(12))
)
, attribute13 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(13), g_miss_char, NULL, NULL, attribute13, l_inv_attributes_tbl(13))
, DECODE(l_inv_attributes_tbl(13), g_miss_char, attribute13, NULL, NULL, l_inv_attributes_tbl(13))
)
, attribute14 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(14), g_miss_char, NULL, NULL, attribute14, l_inv_attributes_tbl(14))
, DECODE(l_inv_attributes_tbl(14), g_miss_char, attribute14, NULL, NULL, l_inv_attributes_tbl(14))
)
, attribute15 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(15), g_miss_char, NULL, NULL, attribute15, l_inv_attributes_tbl(15))
, DECODE(l_inv_attributes_tbl(15), g_miss_char, attribute15, NULL, NULL, l_inv_attributes_tbl(15))
)
, c_attribute1 =
DECODE(
p_source
, 2, DECODE(l_c_attributes_tbl(1), g_miss_char, NULL, NULL, c_attribute1, l_c_attributes_tbl(1))
, DECODE(l_c_attributes_tbl(1), g_miss_char, c_attribute1, NULL, NULL, l_c_attributes_tbl(1))
)
, c_attribute2 =
DECODE(
p_source
, 2, DECODE(l_c_attributes_tbl(2), g_miss_char, NULL, NULL, c_attribute2, l_c_attributes_tbl(2))
, DECODE(l_c_attributes_tbl(2), g_miss_char, c_attribute2, NULL, NULL, l_c_attributes_tbl(2))
)
, c_attribute3 =
DECODE(
p_source
, 2, DECODE(l_c_attributes_tbl(3), g_miss_char, NULL, NULL, c_attribute3, l_c_attributes_tbl(3))
, DECODE(l_c_attributes_tbl(3), g_miss_char, c_attribute3, NULL, NULL, l_c_attributes_tbl(3))
)
, c_attribute4 =
DECODE(
p_source
, 2, DECODE(l_c_attributes_tbl(4), g_miss_char, NULL, NULL, c_attribute4, l_c_attributes_tbl(4))
, DECODE(l_c_attributes_tbl(4), g_miss_char, c_attribute4, NULL, NULL, l_c_attributes_tbl(4))
)
, c_attribute5 =
DECODE(
p_source
, 2, DECODE(l_c_attributes_tbl(5), g_miss_char, NULL, NULL, c_attribute5, l_c_attributes_tbl(5))
, DECODE(l_c_attributes_tbl(5), g_miss_char, c_attribute5, NULL, NULL, l_c_attributes_tbl(5))
)
, c_attribute6 =
DECODE(
p_source
, 2, DECODE(l_c_attributes_tbl(6), g_miss_char, NULL, NULL, c_attribute6, l_c_attributes_tbl(6))
, DECODE(l_c_attributes_tbl(6), g_miss_char, c_attribute6, NULL, NULL, l_c_attributes_tbl(6))
)
, c_attribute7 =
DECODE(
p_source
, 2, DECODE(l_c_attributes_tbl(7), g_miss_char, NULL, NULL, c_attribute7, l_c_attributes_tbl(7))
, DECODE(l_c_attributes_tbl(7), g_miss_char, c_attribute7, NULL, NULL, l_c_attributes_tbl(7))
)
, c_attribute8 =
DECODE(
p_source
, 2, DECODE(l_c_attributes_tbl(8), g_miss_char, NULL, NULL, c_attribute8, l_c_attributes_tbl(8))
, DECODE(l_c_attributes_tbl(8), g_miss_char, c_attribute8, NULL, NULL, l_c_attributes_tbl(8))
)
, c_attribute9 =
DECODE(
p_source
, 2, DECODE(l_c_attributes_tbl(9), g_miss_char, NULL, NULL, c_attribute9, l_c_attributes_tbl(9))
, DECODE(l_c_attributes_tbl(9), g_miss_char, c_attribute9, NULL, NULL, l_c_attributes_tbl(9))
)
, c_attribute10 =
DECODE(
p_source
, 2, DECODE(l_c_attributes_tbl(10), g_miss_char, NULL, NULL, c_attribute10, l_c_attributes_tbl(10))
, DECODE(l_c_attributes_tbl(10), g_miss_char, c_attribute10, NULL, NULL, l_c_attributes_tbl(10))
)
, c_attribute11 =
DECODE(
p_source
, 2, DECODE(l_c_attributes_tbl(11), g_miss_char, NULL, NULL, c_attribute11, l_c_attributes_tbl(11))
, DECODE(l_c_attributes_tbl(11), g_miss_char, c_attribute11, NULL, NULL, l_c_attributes_tbl(11))
)
, c_attribute12 =
DECODE(
p_source
, 2, DECODE(l_c_attributes_tbl(12), g_miss_char, NULL, NULL, c_attribute12, l_c_attributes_tbl(12))
, DECODE(l_c_attributes_tbl(12), g_miss_char, c_attribute12, NULL, NULL, l_c_attributes_tbl(12))
)
, c_attribute13 =
DECODE(
p_source
, 2, DECODE(l_c_attributes_tbl(13), g_miss_char, NULL, NULL, c_attribute13, l_c_attributes_tbl(13))
, DECODE(l_c_attributes_tbl(13), g_miss_char, c_attribute13, NULL, NULL, l_c_attributes_tbl(13))
)
, c_attribute14 =
DECODE(
p_source
, 2, DECODE(l_c_attributes_tbl(14), g_miss_char, NULL, NULL, c_attribute14, l_c_attributes_tbl(14))
, DECODE(l_c_attributes_tbl(14), g_miss_char, c_attribute14, NULL, NULL, l_c_attributes_tbl(14))
)
, c_attribute15 =
DECODE(
p_source
, 2, DECODE(l_c_attributes_tbl(15), g_miss_char, NULL, NULL, c_attribute15, l_c_attributes_tbl(15))
, DECODE(l_c_attributes_tbl(15), g_miss_char, c_attribute15, NULL, NULL, l_c_attributes_tbl(15))
)
, c_attribute16 =
DECODE(
p_source
, 2, DECODE(l_c_attributes_tbl(16), g_miss_char, NULL, NULL, c_attribute16, l_c_attributes_tbl(16))
, DECODE(l_c_attributes_tbl(16), g_miss_char, c_attribute16, NULL, NULL, l_c_attributes_tbl(16))
)
, c_attribute17 =
DECODE(
p_source
, 2, DECODE(l_c_attributes_tbl(17), g_miss_char, NULL, NULL, c_attribute17, l_c_attributes_tbl(17))
, DECODE(l_c_attributes_tbl(17), g_miss_char, c_attribute17, NULL, NULL, l_c_attributes_tbl(17))
)
, c_attribute18 =
DECODE(
p_source
, 2, DECODE(l_c_attributes_tbl(18), g_miss_char, NULL, NULL, c_attribute18, l_c_attributes_tbl(18))
, DECODE(l_c_attributes_tbl(18), g_miss_char, c_attribute18, NULL, NULL, l_c_attributes_tbl(18))
)
, c_attribute19 =
DECODE(
p_source
, 2, DECODE(l_c_attributes_tbl(19), g_miss_char, NULL, NULL, c_attribute19, l_c_attributes_tbl(19))
, DECODE(l_c_attributes_tbl(19), g_miss_char, c_attribute19, NULL, NULL, l_c_attributes_tbl(19))
)
, c_attribute20 =
DECODE(
p_source
, 2, DECODE(l_c_attributes_tbl(20), g_miss_char, NULL, NULL, c_attribute20, l_c_attributes_tbl(20))
, DECODE(l_c_attributes_tbl(20), g_miss_char, c_attribute20, NULL, NULL, l_c_attributes_tbl(20))
)
, n_attribute1 =
DECODE(
p_source
, 2, DECODE(l_n_attributes_tbl(1), g_miss_num, NULL, NULL, n_attribute1, l_n_attributes_tbl(1))
, DECODE(l_n_attributes_tbl(1), g_miss_num, n_attribute1, NULL, NULL, l_n_attributes_tbl(1))
)
, n_attribute2 =
DECODE(
p_source
, 2, DECODE(l_n_attributes_tbl(2), g_miss_num, NULL, NULL, n_attribute2, l_n_attributes_tbl(2))
, DECODE(l_n_attributes_tbl(2), g_miss_num, n_attribute2, NULL, NULL, l_n_attributes_tbl(2))
)
, n_attribute3 =
DECODE(
p_source
, 2, DECODE(l_n_attributes_tbl(3), g_miss_num, NULL, NULL, n_attribute3, l_n_attributes_tbl(3))
, DECODE(l_n_attributes_tbl(3), g_miss_num, n_attribute3, NULL, NULL, l_n_attributes_tbl(3))
)
, n_attribute4 =
DECODE(
p_source
, 2, DECODE(l_n_attributes_tbl(4), g_miss_num, NULL, NULL, n_attribute4, l_n_attributes_tbl(4))
, DECODE(l_n_attributes_tbl(4), g_miss_num, n_attribute4, NULL, NULL, l_n_attributes_tbl(4))
)
, n_attribute5 =
DECODE(
p_source
, 2, DECODE(l_n_attributes_tbl(5), g_miss_num, NULL, NULL, n_attribute5, l_n_attributes_tbl(5))
, DECODE(l_n_attributes_tbl(5), g_miss_num, n_attribute5, NULL, NULL, l_n_attributes_tbl(5))
)
, n_attribute6 =
DECODE(
p_source
, 2, DECODE(l_n_attributes_tbl(6), g_miss_num, NULL, NULL, n_attribute6, l_n_attributes_tbl(6))
, DECODE(l_n_attributes_tbl(6), g_miss_num, n_attribute6, NULL, NULL, l_n_attributes_tbl(6))
)
, n_attribute7 =
DECODE(
p_source
, 2, DECODE(l_n_attributes_tbl(7), g_miss_num, NULL, NULL, n_attribute7, l_n_attributes_tbl(7))
, DECODE(l_n_attributes_tbl(7), g_miss_num, n_attribute7, NULL, NULL, l_n_attributes_tbl(7))
)
, n_attribute8 =
DECODE(
p_source
, 2, DECODE(l_n_attributes_tbl(8), g_miss_num, NULL, NULL, n_attribute8, l_n_attributes_tbl(8))
, DECODE(l_n_attributes_tbl(8), g_miss_num, n_attribute8, NULL, NULL, l_n_attributes_tbl(8))
)
, n_attribute9 =
DECODE(
p_source
, 2, DECODE(l_n_attributes_tbl(9), g_miss_num, NULL, NULL, n_attribute9, l_n_attributes_tbl(9))
, DECODE(l_n_attributes_tbl(9), g_miss_num, n_attribute9, NULL, NULL, l_n_attributes_tbl(9))
)
, n_attribute10 =
DECODE(
p_source
, 2, DECODE(l_n_attributes_tbl(10), g_miss_num, NULL, NULL, n_attribute10, l_n_attributes_tbl(10))
, DECODE(l_n_attributes_tbl(10), g_miss_num, n_attribute10, NULL, NULL, l_n_attributes_tbl(10))
)
, d_attribute1 =
DECODE(
p_source
, 2, DECODE(l_d_attributes_tbl(1), g_miss_date, NULL, NULL, d_attribute1, l_d_attributes_tbl(1))
, DECODE(l_d_attributes_tbl(1), g_miss_date, d_attribute1, NULL, NULL, l_d_attributes_tbl(1))
)
, d_attribute2 =
DECODE(
p_source
, 2, DECODE(l_d_attributes_tbl(2), g_miss_date, NULL, NULL, d_attribute2, l_d_attributes_tbl(2))
, DECODE(l_d_attributes_tbl(2), g_miss_date, d_attribute2, NULL, NULL, l_d_attributes_tbl(2))
)
, d_attribute3 =
DECODE(
p_source
, 2, DECODE(l_d_attributes_tbl(3), g_miss_date, NULL, NULL, d_attribute3, l_d_attributes_tbl(3))
, DECODE(l_d_attributes_tbl(3), g_miss_date, d_attribute3, NULL, NULL, l_d_attributes_tbl(3))
)
, d_attribute4 =
DECODE(
p_source
, 2, DECODE(l_d_attributes_tbl(4), g_miss_date, NULL, NULL, d_attribute4, l_d_attributes_tbl(4))
, DECODE(l_d_attributes_tbl(4), g_miss_date, d_attribute4, NULL, NULL, l_d_attributes_tbl(4))
)
, d_attribute5 =
DECODE(
p_source
, 2, DECODE(l_d_attributes_tbl(5), g_miss_date, NULL, NULL, d_attribute5, l_d_attributes_tbl(5))
, DECODE(l_d_attributes_tbl(5), g_miss_date, d_attribute5, NULL, NULL, l_d_attributes_tbl(5))
)
, d_attribute6 =
DECODE(
p_source
, 2, DECODE(l_d_attributes_tbl(6), g_miss_date, NULL, NULL, d_attribute6, l_d_attributes_tbl(6))
, DECODE(l_d_attributes_tbl(6), g_miss_date, d_attribute6, NULL, NULL, l_d_attributes_tbl(6))
)
, d_attribute7 =
DECODE(
p_source
, 2, DECODE(l_d_attributes_tbl(7), g_miss_date, NULL, NULL, d_attribute7, l_d_attributes_tbl(7))
, DECODE(l_d_attributes_tbl(7), g_miss_date, d_attribute7, NULL, NULL, l_d_attributes_tbl(7))
)
, d_attribute8 =
DECODE(
p_source
, 2, DECODE(l_d_attributes_tbl(8), g_miss_date, NULL, NULL, d_attribute8, l_d_attributes_tbl(8))
, DECODE(l_d_attributes_tbl(8), g_miss_date, d_attribute8, NULL, NULL, l_d_attributes_tbl(8))
)
, d_attribute9 =
DECODE(
p_source
, 2, DECODE(l_d_attributes_tbl(9), g_miss_date, NULL, NULL, d_attribute9, l_d_attributes_tbl(9))
, DECODE(l_d_attributes_tbl(9), g_miss_date, d_attribute9, NULL, NULL, l_d_attributes_tbl(9))
)
, d_attribute10 =
DECODE(
p_source
, 2, DECODE(l_d_attributes_tbl(10), g_miss_date, NULL, NULL, d_attribute10, l_d_attributes_tbl(10))
, DECODE(l_d_attributes_tbl(10), g_miss_date, d_attribute10, NULL, NULL, l_d_attributes_tbl(10))
)
,LAST_UPDATE_DATE = sysdate
,LAST_UPDATED_BY = fnd_global.user_id
WHERE inventory_item_id = p_lot_rec.inventory_item_id
AND organization_id = p_lot_rec.organization_id
AND lot_number = p_lot_rec.lot_number;
UPDATE mtl_lot_numbers
SET expiration_date =
DECODE(l_expiration_date, NULL, expiration_date, l_expiration_date )
, disable_flag =
DECODE(
p_source
, 2, DECODE(p_lot_rec.disable_flag, g_miss_num, NULL, NULL, disable_flag, p_lot_rec.disable_flag) /*Bug 14835444 changed g_miss_char to g_miss_num */
, DECODE(p_lot_rec.disable_flag, g_miss_num, disable_flag, NULL, NULL, p_lot_rec.disable_flag)
)
, attribute_category =
DECODE(
p_source
, 2, DECODE(p_lot_rec.attribute_category, g_miss_char, NULL, NULL, attribute_category, p_lot_rec.attribute_category)
, DECODE(p_lot_rec.attribute_category, g_miss_char, attribute_category, NULL, NULL, p_lot_rec.attribute_category)
)
, lot_attribute_category =
DECODE(
p_source
, 2, DECODE(p_lot_rec.lot_attribute_category, g_miss_char, NULL, NULL, lot_attribute_category, p_lot_rec.lot_attribute_category)
, DECODE(p_lot_rec.lot_attribute_category, g_miss_char, lot_attribute_category, NULL, NULL, p_lot_rec.lot_attribute_category)
)
/*, grade_code =
DECODE(
p_source
, 2, DECODE(p_lot_rec.grade_code, g_miss_char, NULL, NULL, grade_code, p_lot_rec.grade_code)
, DECODE(p_lot_rec.grade_code, g_miss_char, grade_code, NULL, NULL, p_lot_rec.grade_code)
)*/ -- Commented for bug 13780398
, origination_date =
DECODE(
p_source
, 2, DECODE(p_lot_rec.origination_date, g_miss_date, To_Date(NULL), NULL, origination_date, p_lot_rec.origination_date)
, DECODE(p_lot_rec.origination_date, g_miss_date, origination_date, NULL, To_Date(NULL), p_lot_rec.origination_date)
)
, date_code =
DECODE(
p_source
, 2, DECODE(p_lot_rec.date_code, g_miss_char, NULL, NULL, date_code, p_lot_rec.date_code)
, DECODE(p_lot_rec.date_code, g_miss_char, date_code, NULL, NULL, p_lot_rec.date_code)
)
/* Bug 8198497 - Removed the code as we are updating the status by calling validate_lot_status */
, change_date =
DECODE(
p_source
, 2, DECODE(p_lot_rec.change_date, g_miss_date, To_Date(NULL), NULL, change_date, p_lot_rec.change_date)
, DECODE(p_lot_rec.change_date, g_miss_date, change_date, NULL, To_Date(NULL), p_lot_rec.change_date)
)
, age = DECODE(
p_source
, 2, DECODE(p_lot_rec.age, g_miss_num, NULL, NULL, age, p_lot_rec.age)
, DECODE(p_lot_rec.age, g_miss_num, age, NULL, NULL, p_lot_rec.age)
)
, retest_date =
DECODE(
p_source
, 2, DECODE(p_lot_rec.retest_date, g_miss_date, To_Date(NULL), NULL, retest_date, p_lot_rec.retest_date)
, DECODE(p_lot_rec.retest_date, g_miss_date, retest_date, NULL, To_Date(NULL), p_lot_rec.retest_date)
)
, maturity_date =
DECODE(
p_source
, 2, DECODE(p_lot_rec.maturity_date, g_miss_date, To_Date(NULL), NULL, maturity_date, p_lot_rec.maturity_date)
, DECODE(p_lot_rec.maturity_date, g_miss_date, maturity_date, NULL, To_Date(NULL), p_lot_rec.maturity_date)
)
, item_size =
DECODE(
p_source
, 2, DECODE(p_lot_rec.item_size, g_miss_num, NULL, NULL, item_size, p_lot_rec.item_size)
, DECODE(p_lot_rec.item_size, g_miss_num, item_size, NULL, NULL, p_lot_rec.item_size)
)
, color =
DECODE(
p_source
, 2, DECODE(p_lot_rec.color, g_miss_char, NULL, NULL, color, p_lot_rec.color)
, DECODE(p_lot_rec.color, g_miss_char, color, NULL, NULL, p_lot_rec.color)
)
, volume =
DECODE(
p_source
, 2, DECODE(p_lot_rec.volume, g_miss_num, NULL, NULL, volume, p_lot_rec.volume)
, DECODE(p_lot_rec.volume, g_miss_num, volume, NULL, NULL, p_lot_rec.volume)
)
, volume_uom =
DECODE(
p_source
, 2, DECODE(p_lot_rec.volume_uom, g_miss_char, NULL, NULL, volume_uom, p_lot_rec.volume_uom)
, DECODE(p_lot_rec.volume_uom, g_miss_char, volume_uom, NULL, NULL, p_lot_rec.volume_uom)
)
, place_of_origin =
DECODE(
p_source
, 2, DECODE(p_lot_rec.place_of_origin, g_miss_char, NULL, NULL, place_of_origin, p_lot_rec.place_of_origin)
, DECODE(p_lot_rec.place_of_origin, g_miss_char, place_of_origin, NULL, place_of_origin, p_lot_rec.place_of_origin)
)
, best_by_date =
DECODE(
p_source
, 2, DECODE(p_lot_rec.best_by_date, g_miss_date, To_Date(NULL), NULL, best_by_date, p_lot_rec.best_by_date)
, DECODE(p_lot_rec.best_by_date, g_miss_date, best_by_date, NULL, To_Date(NULL), p_lot_rec.best_by_date)
)
, LENGTH =
DECODE(
p_source
, 2, DECODE(p_lot_rec.length, g_miss_num, NULL, NULL, LENGTH, p_lot_rec.length)
, DECODE(p_lot_rec.length, g_miss_num, LENGTH, NULL, NULL, p_lot_rec.length)
)
, length_uom =
DECODE(
p_source
, 2, DECODE(p_lot_rec.length_uom, g_miss_char, NULL, NULL, length_uom, p_lot_rec.length_uom)
, DECODE(p_lot_rec.length_uom, g_miss_char, length_uom, NULL, NULL, p_lot_rec.length_uom)
)
, recycled_content =
DECODE(
p_source
, 2, DECODE(p_lot_rec.recycled_content, g_miss_num, NULL, NULL, recycled_content, p_lot_rec.recycled_content)
, DECODE(p_lot_rec.recycled_content, g_miss_num, recycled_content, NULL, NULL, p_lot_rec.recycled_content)
)
, thickness =
DECODE(
p_source
, 2, DECODE(p_lot_rec.thickness, g_miss_num, NULL, NULL, thickness, p_lot_rec.thickness)
, DECODE(p_lot_rec.thickness, g_miss_num, thickness, NULL, NULL, p_lot_rec.thickness)
)
, thickness_uom =
DECODE(
p_source
, 2, DECODE(p_lot_rec.thickness_uom, g_miss_char, NULL, NULL, thickness_uom, p_lot_rec.thickness_uom)
, DECODE(p_lot_rec.thickness_uom, g_miss_char, thickness_uom, NULL, NULL, p_lot_rec.thickness_uom)
)
, width =
DECODE(
p_source
, 2, DECODE(p_lot_rec.width, g_miss_num, NULL, NULL, width, p_lot_rec.width)
, DECODE(p_lot_rec.width, g_miss_num, width, NULL, NULL, p_lot_rec.width)
)
, width_uom =
DECODE(
p_source
, 2, DECODE(p_lot_rec.width_uom, g_miss_char, NULL, NULL, width_uom, p_lot_rec.width_uom)
, DECODE(p_lot_rec.width_uom, g_miss_char, width_uom, NULL, NULL, p_lot_rec.width_uom)
)
, territory_code =
DECODE(
p_source
, 2, DECODE(p_lot_rec.territory_code, g_miss_char, NULL, NULL, territory_code, p_lot_rec.territory_code)
, DECODE(p_lot_rec.territory_code, g_miss_char, territory_code, NULL, NULL, p_lot_rec.territory_code)
)
, supplier_lot_number =
DECODE(
p_source
, 2, DECODE(p_lot_rec.supplier_lot_number, g_miss_char, NULL, NULL, supplier_lot_number, p_lot_rec.supplier_lot_number)
, DECODE(p_lot_rec.supplier_lot_number, g_miss_char, supplier_lot_number, NULL, NULL, p_lot_rec.supplier_lot_number)
)
-- Bug 6983527 - Parent lot number should never be updated.
/*Bug 8311729 Uncommenting the below code as we should be able to
update the mistakenly entered parent lot information */
-- nsinghi bug#5209065. Update new lot attributes
, parent_lot_number =
DECODE(
p_source
, 2, DECODE(p_lot_rec.parent_lot_number, g_miss_char, NULL, NULL, parent_lot_number, p_lot_rec.parent_lot_number)
, DECODE(p_lot_rec.parent_lot_number, g_miss_char, parent_lot_number, NULL, NULL, p_lot_rec.parent_lot_number)
)
-- Bug 13553990 keeping the value of columns without getting updated to NULL which are not passed.
, origination_type =
DECODE(
p_source
, 2, DECODE(p_lot_rec.origination_type, g_miss_num, NULL, NULL, origination_type, p_lot_rec.origination_type)
, DECODE(p_lot_rec.origination_type, -99, origination_type, g_miss_num, origination_type, NULL, NULL, p_lot_rec.origination_type)
)
, availability_type =
DECODE(
p_source
, 2, DECODE(p_lot_rec.availability_type, g_miss_num, NULL, NULL, availability_type, p_lot_rec.availability_type)
, DECODE(p_lot_rec.availability_type, g_miss_num, availability_type, NULL, NULL, p_lot_rec.availability_type)
)
, expiration_action_code =
DECODE(
p_source
, 2, DECODE(p_lot_rec.expiration_action_code, g_miss_char, NULL, NULL, expiration_action_code, p_lot_rec.expiration_action_code)
, DECODE(p_lot_rec.expiration_action_code, g_miss_char, expiration_action_code, NULL, NULL, p_lot_rec.expiration_action_code)
)
, expiration_action_date =
DECODE(
p_source
, 2, DECODE(p_lot_rec.expiration_action_date, g_miss_date, To_Date(NULL), NULL, expiration_action_date, p_lot_rec.expiration_action_date)
, DECODE(p_lot_rec.expiration_action_date, g_miss_date, expiration_action_date, NULL, To_Date(NULL), p_lot_rec.expiration_action_date)
)
, hold_date =
DECODE(
p_source
, 2, DECODE(p_lot_rec.hold_date, g_miss_date, To_Date(NULL), NULL, hold_date, p_lot_rec.hold_date)
, DECODE(p_lot_rec.hold_date, g_miss_date, hold_date, NULL, To_Date(NULL), p_lot_rec.hold_date)
)
, inventory_atp_code =
DECODE(
p_source
, 2, DECODE(p_lot_rec.inventory_atp_code, g_miss_num, NULL, NULL, inventory_atp_code, p_lot_rec.inventory_atp_code)
, DECODE(p_lot_rec.inventory_atp_code, g_miss_num, inventory_atp_code, NULL, NULL, p_lot_rec.inventory_atp_code)
)
, reservable_type =
DECODE(
p_source
, 2, DECODE(p_lot_rec.reservable_type, g_miss_num, NULL, NULL, reservable_type, p_lot_rec.reservable_type)
, DECODE(p_lot_rec.reservable_type, g_miss_num, reservable_type, NULL, NULL, p_lot_rec.reservable_type)
)
, sampling_event_id =
DECODE(
p_source
, 2, DECODE(p_lot_rec.sampling_event_id, g_miss_num, NULL, NULL, sampling_event_id, p_lot_rec.sampling_event_id)
, DECODE(p_lot_rec.sampling_event_id, g_miss_num, sampling_event_id, NULL, NULL, p_lot_rec.sampling_event_id)
)
-- nsinghi bug#5209065. End.
, vendor_name =
DECODE(
p_source
, 2, DECODE(p_lot_rec.vendor_name, g_miss_char, NULL, NULL, vendor_name, p_lot_rec.vendor_name)
, DECODE(p_lot_rec.vendor_name, g_miss_char, vendor_name, NULL, NULL, p_lot_rec.vendor_name)
)
, attribute1 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(1), g_miss_char, NULL, NULL, attribute1, l_inv_attributes_tbl(1))
, DECODE(l_inv_attributes_tbl(1), g_miss_char, attribute1, NULL, NULL, l_inv_attributes_tbl(1))
)
, attribute2 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(2), g_miss_char, NULL, NULL, attribute2, l_inv_attributes_tbl(2))
, DECODE(l_inv_attributes_tbl(2), g_miss_char, attribute2, NULL, NULL, l_inv_attributes_tbl(2))
)
, attribute3 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(3), g_miss_char, NULL, NULL, attribute3, l_inv_attributes_tbl(3))
, DECODE(l_inv_attributes_tbl(3), g_miss_char, attribute3, NULL, NULL, l_inv_attributes_tbl(3))
)
, attribute4 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(4), g_miss_char, NULL, NULL, attribute4, l_inv_attributes_tbl(4))
, DECODE(l_inv_attributes_tbl(4), g_miss_char, attribute4, NULL, NULL, l_inv_attributes_tbl(4))
)
, attribute5 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(5), g_miss_char, NULL, NULL, attribute5, l_inv_attributes_tbl(5))
, DECODE(l_inv_attributes_tbl(5), g_miss_char, attribute5, NULL, NULL, l_inv_attributes_tbl(5))
)
, attribute6 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(6), g_miss_char, NULL, NULL, attribute6, l_inv_attributes_tbl(6))
, DECODE(l_inv_attributes_tbl(6), g_miss_char, attribute6, NULL, NULL, l_inv_attributes_tbl(6))
)
, attribute7 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(7), g_miss_char, NULL, NULL, attribute7, l_inv_attributes_tbl(7))
, DECODE(l_inv_attributes_tbl(7), g_miss_char, attribute7, NULL, NULL, l_inv_attributes_tbl(7))
)
, attribute8 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(8), g_miss_char, NULL, NULL, attribute8, l_inv_attributes_tbl(8))
, DECODE(l_inv_attributes_tbl(8), g_miss_char, attribute8, NULL, NULL, l_inv_attributes_tbl(8))
)
, attribute9 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(9), g_miss_char, NULL, NULL, attribute9, l_inv_attributes_tbl(9))
, DECODE(l_inv_attributes_tbl(9), g_miss_char, attribute9, NULL, NULL, l_inv_attributes_tbl(9))
)
, attribute10 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(10), g_miss_char, NULL, NULL, attribute10, l_inv_attributes_tbl(10))
, DECODE(l_inv_attributes_tbl(10), g_miss_char, attribute10, NULL, NULL, l_inv_attributes_tbl(10))
)
, attribute11 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(11), g_miss_char, NULL, NULL, attribute11, l_inv_attributes_tbl(11))
, DECODE(l_inv_attributes_tbl(11), g_miss_char, attribute11, NULL, NULL, l_inv_attributes_tbl(11))
)
, attribute12 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(12), g_miss_char, NULL, NULL, attribute12, l_inv_attributes_tbl(12))
, DECODE(l_inv_attributes_tbl(12), g_miss_char, attribute12, NULL, NULL, l_inv_attributes_tbl(12))
)
, attribute13 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(13), g_miss_char, NULL, NULL, attribute13, l_inv_attributes_tbl(13))
, DECODE(l_inv_attributes_tbl(13), g_miss_char, attribute13, NULL, NULL, l_inv_attributes_tbl(13))
)
, attribute14 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(14), g_miss_char, NULL, NULL, attribute14, l_inv_attributes_tbl(14))
, DECODE(l_inv_attributes_tbl(14), g_miss_char, attribute14, NULL, NULL, l_inv_attributes_tbl(14))
)
, attribute15 =
DECODE(
p_source
, 2, DECODE(l_inv_attributes_tbl(15), g_miss_char, NULL, NULL, attribute15, l_inv_attributes_tbl(15))
, DECODE(l_inv_attributes_tbl(15), g_miss_char, attribute15, NULL, NULL, l_inv_attributes_tbl(15))
)
/*, c_attribute1 = decode(p_source, 2,
DECODE(l_c_attributes_tbl(1), g_miss_char, NULL, NULL, c_attribute1, l_c_attributes_tbl(1))
,DECODE(l_c_attributes_tbl(1), g_miss_char, c_attribute1, NULL, null, l_c_attributes_tbl(1)))
, c_attribute2 = decode(p_source, 2,
DECODE(l_c_attributes_tbl(2), g_miss_char, NULL, NULL, c_attribute2, l_c_attributes_tbl(2))
,DECODE(l_c_attributes_tbl(2), g_miss_char, c_attribute2, NULL, null, l_c_attributes_tbl(2)))
, c_attribute3 = decode(p_source, 2,
DECODE(l_c_attributes_tbl(3), g_miss_char, NULL, NULL, c_attribute3, l_c_attributes_tbl(3))
,DECODE(l_c_attributes_tbl(3), g_miss_char, c_attribute3, NULL, null, l_c_attributes_tbl(3)))
, c_attribute4 = decode(p_source, 2,
DECODE(l_c_attributes_tbl(4), g_miss_char, NULL, NULL, c_attribute4, l_c_attributes_tbl(4))
,DECODE(l_c_attributes_tbl(4), g_miss_char, c_attribute4, NULL, null, l_c_attributes_tbl(4)))
, c_attribute5 = decode(p_source, 2,
DECODE(l_c_attributes_tbl(5), g_miss_char, NULL, NULL, c_attribute5, l_c_attributes_tbl(5))
,DECODE(l_c_attributes_tbl(5), g_miss_char, c_attribute5, NULL, null, l_c_attributes_tbl(5)))
, c_attribute6 = decode(p_source, 2,
DECODE(l_c_attributes_tbl(6), g_miss_char, NULL, NULL, c_attribute6, l_c_attributes_tbl(6))
,DECODE(l_c_attributes_tbl(6), g_miss_char, c_attribute6, NULL, null, l_c_attributes_tbl(6)))
, c_attribute7 = decode(p_source, 2,
DECODE(l_c_attributes_tbl(7), g_miss_char, NULL, NULL, c_attribute7, l_c_attributes_tbl(7))
,DECODE(l_c_attributes_tbl(7), g_miss_char, c_attribute7, NULL, null, l_c_attributes_tbl(7)))
, c_attribute8 = decode(p_source, 2,
DECODE(l_c_attributes_tbl(8), g_miss_char, NULL, NULL, c_attribute8, l_c_attributes_tbl(8))
,DECODE(l_c_attributes_tbl(8), g_miss_char, c_attribute8, NULL, null, l_c_attributes_tbl(8)))
, c_attribute9 = decode(p_source, 2,
DECODE(l_c_attributes_tbl(9), g_miss_char, NULL, NULL, c_attribute9, l_c_attributes_tbl(9))
,DECODE(l_c_attributes_tbl(9), g_miss_char, c_attribute9, NULL, null, l_c_attributes_tbl(9)))
, c_attribute10 = decode(p_source, 2,
DECODE(l_c_attributes_tbl(10), g_miss_char, NULL, NULL, c_attribute10, l_c_attributes_tbl(10))
,DECODE(l_c_attributes_tbl(10), g_miss_char, c_attribute10, NULL, null, l_c_attributes_tbl(10)))
, c_attribute11 = decode(p_source, 2,
DECODE(l_c_attributes_tbl(11), g_miss_char, NULL, NULL, c_attribute11, l_c_attributes_tbl(11))
,DECODE(l_c_attributes_tbl(11), g_miss_char, c_attribute11, NULL, null, l_c_attributes_tbl(11)))
, c_attribute12 = decode(p_source, 2,
DECODE(l_c_attributes_tbl(12), g_miss_char, NULL, NULL, c_attribute12, l_c_attributes_tbl(12))
,DECODE(l_c_attributes_tbl(12), g_miss_char, c_attribute12, NULL, null, l_c_attributes_tbl(12)))
, c_attribute13 = decode(p_source, 2,
DECODE(l_c_attributes_tbl(13), g_miss_char, NULL, NULL, c_attribute13, l_c_attributes_tbl(13))
,DECODE(l_c_attributes_tbl(13), g_miss_char, c_attribute13, NULL, null, l_c_attributes_tbl(13)))
, c_attribute14 = decode(p_source, 2,
DECODE(l_c_attributes_tbl(14), g_miss_char, NULL, NULL, c_attribute14, l_c_attributes_tbl(14))
,DECODE(l_c_attributes_tbl(14), g_miss_char, c_attribute14, NULL, null, l_c_attributes_tbl(14)))
, c_attribute15 = decode(p_source, 2,
DECODE(l_c_attributes_tbl(15), g_miss_char, NULL, NULL, c_attribute15, l_c_attributes_tbl(15))
,DECODE(l_c_attributes_tbl(15), g_miss_char, c_attribute15, NULL, null, l_c_attributes_tbl(15)))
, c_attribute16 = decode(p_source, 2,
DECODE(l_c_attributes_tbl(16), g_miss_char, NULL, NULL, c_attribute16, l_c_attributes_tbl(16))
,DECODE(l_c_attributes_tbl(16), g_miss_char, c_attribute16, NULL, null, l_c_attributes_tbl(16)))
, c_attribute17 = decode(p_source, 2,
DECODE(l_c_attributes_tbl(17), g_miss_char, NULL, NULL, c_attribute17, l_c_attributes_tbl(17))
,DECODE(l_c_attributes_tbl(17), g_miss_char, c_attribute17, NULL, null, l_c_attributes_tbl(17)))
, c_attribute18 = decode(p_source, 2,
DECODE(l_c_attributes_tbl(18), g_miss_char, NULL, NULL, c_attribute18, l_c_attributes_tbl(18))
,DECODE(l_c_attributes_tbl(18), g_miss_char, c_attribute18, NULL, null, l_c_attributes_tbl(18)))
, c_attribute19 = decode(p_source, 2,
DECODE(l_c_attributes_tbl(19), g_miss_char, NULL, NULL, c_attribute19, l_c_attributes_tbl(19))
,DECODE(l_c_attributes_tbl(19), g_miss_char, c_attribute19, NULL, null, l_c_attributes_tbl(19)))
, c_attribute20 = decode(p_source, 2,
DECODE(l_c_attributes_tbl(20), g_miss_char, NULL, NULL, c_attribute20, l_c_attributes_tbl(20))
,DECODE(l_c_attributes_tbl(20), g_miss_char, c_attribute20, NULL, null, l_c_attributes_tbl(20)))
, n_attribute1 = decode(p_source, 2,
DECODE(l_n_attributes_tbl(1), g_miss_num, NULL, NULL, n_attribute1, l_n_attributes_tbl(1))
, DECODE(l_n_attributes_tbl(1), g_miss_num, N_attribute1, NULL, null, l_n_attributes_tbl(1)))
, n_attribute2 = decode(p_source, 2,
DECODE(l_n_attributes_tbl(2), g_miss_num, NULL, NULL, n_attribute2, l_n_attributes_tbl(2))
, DECODE(l_n_attributes_tbl(2), g_miss_num, N_attribute2, NULL, null, l_n_attributes_tbl(2)))
, n_attribute3 = decode(p_source, 2,
DECODE(l_n_attributes_tbl(3), g_miss_num, NULL, NULL, n_attribute3, l_n_attributes_tbl(3))
, DECODE(l_n_attributes_tbl(3), g_miss_num, N_attribute3, NULL, null, l_n_attributes_tbl(3)))
, n_attribute4 = decode(p_source, 2,
DECODE(l_n_attributes_tbl(4), g_miss_num, NULL, NULL, n_attribute4, l_n_attributes_tbl(4))
, DECODE(l_n_attributes_tbl(4), g_miss_num, N_attribute4, NULL, null, l_n_attributes_tbl(4)))
, n_attribute5 = decode(p_source, 2,
DECODE(l_n_attributes_tbl(5), g_miss_num, NULL, NULL, n_attribute5, l_n_attributes_tbl(5))
, DECODE(l_n_attributes_tbl(5), g_miss_num, N_attribute5, NULL, null, l_n_attributes_tbl(5)))
, n_attribute6 = decode(p_source, 2,
DECODE(l_n_attributes_tbl(6), g_miss_num, NULL, NULL, n_attribute6, l_n_attributes_tbl(6))
, DECODE(l_n_attributes_tbl(6), g_miss_num, N_attribute6, NULL, null, l_n_attributes_tbl(6)))
, n_attribute7 = decode(p_source, 2,
DECODE(l_n_attributes_tbl(7), g_miss_num, NULL, NULL, n_attribute7, l_n_attributes_tbl(7))
, DECODE(l_n_attributes_tbl(7), g_miss_num, N_attribute7, NULL, null, l_n_attributes_tbl(7)))
, n_attribute8 = decode(p_source, 2,
DECODE(l_n_attributes_tbl(8), g_miss_num, NULL, NULL, n_attribute8, l_n_attributes_tbl(8))
, DECODE(l_n_attributes_tbl(8), g_miss_num, N_attribute8, NULL, null, l_n_attributes_tbl(8)))
, n_attribute9 = decode(p_source, 2,
DECODE(l_n_attributes_tbl(9), g_miss_num, NULL, NULL, n_attribute9, l_n_attributes_tbl(9))
,DECODE(l_n_attributes_tbl(9), g_miss_num, N_attribute9, NULL, null, l_n_attributes_tbl(9)))
, n_attribute10 = decode(p_source, 2,
DECODE(l_n_attributes_tbl(10), g_miss_num, NULL, NULL, n_attribute10, l_n_attributes_tbl(10))
,DECODE(l_n_attributes_tbl(10), g_miss_num, n_attribute10, NULL, null, l_n_attributes_tbl(10)))
, d_attribute1 = decode(p_source, 2,
DECODE(l_d_attributes_tbl(1), g_miss_date, NULL, NULL, d_attribute1, l_d_attributes_tbl(1))
, DECODE(l_d_attributes_tbl(1), g_miss_date, d_attribute1, NULL, null, l_d_attributes_tbl(1)))
, d_attribute2 = decode(p_source, 2,
DECODE(l_d_attributes_tbl(2), g_miss_date, NULL, NULL, d_attribute2, l_d_attributes_tbl(2))
, DECODE(l_d_attributes_tbl(2), g_miss_date, d_attribute2, NULL, null, l_d_attributes_tbl(2)))
, d_attribute3 = decode(p_source, 2,
DECODE(l_d_attributes_tbl(3), g_miss_date, NULL, NULL, d_attribute3, l_d_attributes_tbl(3))
, DECODE(l_d_attributes_tbl(3), g_miss_date, d_attribute3, NULL, null, l_d_attributes_tbl(3)))
, d_attribute4 = decode(p_source, 2,
DECODE(l_d_attributes_tbl(4), g_miss_date, NULL, NULL, d_attribute4, l_d_attributes_tbl(4))
, DECODE(l_d_attributes_tbl(4), g_miss_date, d_attribute4, NULL, null, l_d_attributes_tbl(4)))
, d_attribute5 = decode(p_source, 2,
DECODE(l_d_attributes_tbl(5), g_miss_date, NULL, NULL, d_attribute5, l_d_attributes_tbl(5))
, DECODE(l_d_attributes_tbl(5), g_miss_date, d_attribute5, NULL, null, l_d_attributes_tbl(5)))
, d_attribute6 = decode(p_source, 2,
DECODE(l_d_attributes_tbl(6), g_miss_date, NULL, NULL, d_attribute6, l_d_attributes_tbl(6))
,DECODE(l_d_attributes_tbl(6), g_miss_date, d_attribute6, NULL, null, l_d_attributes_tbl(6)))
, d_attribute7 = decode(p_source, 2,
DECODE(l_d_attributes_tbl(7), g_miss_date, NULL, NULL, d_attribute7, l_d_attributes_tbl(7))
, DECODE(l_d_attributes_tbl(7), g_miss_date, d_attribute7, NULL, null, l_d_attributes_tbl(7)))
, d_attribute8 = decode(p_source, 2,
DECODE(l_d_attributes_tbl(8), g_miss_date, NULL, NULL, d_attribute8, l_d_attributes_tbl(8))
, DECODE(l_d_attributes_tbl(8), g_miss_date, d_attribute8, NULL, null, l_d_attributes_tbl(8)))
, d_attribute9 = decode(p_source, 2,
DECODE(l_d_attributes_tbl(9), g_miss_date, NULL, NULL, d_attribute9, l_d_attributes_tbl(9))
, DECODE(l_d_attributes_tbl(9), g_miss_date, d_attribute9, NULL, null, l_d_attributes_tbl(9)))
, d_attribute10 = decode(p_source, 2,
DECODE(l_d_attributes_tbl(10), g_miss_date, NULL, NULL, d_attribute10, l_d_attributes_tbl(10))
,DECODE(l_d_attributes_tbl(10), g_miss_date, d_attribute10, NULL, null, l_d_attributes_tbl(10)))*/
,LAST_UPDATE_DATE = sysdate
,LAST_UPDATED_BY = fnd_global.user_id
WHERE inventory_item_id = p_lot_rec.inventory_item_id
AND organization_id = p_lot_rec.organization_id
AND lot_number = p_lot_rec.lot_number;
print_debug('Upd Lot Attr:Update successfully completed', 9);
/* Bug 8198497: Calling the API to validate status of the lot and update it */
validate_lot_status(
p_api_version
, p_init_msg_list
, p_lot_rec.organization_id
, p_lot_rec.inventory_item_id
, p_lot_rec.lot_number
, p_lot_rec.status_id
, x_return_status
, x_msg_count
, x_msg_data
);
select grade_code
into l_lot_grade
from mtl_lot_numbers
where organization_id=p_lot_rec.organization_id
and inventory_item_id=p_lot_rec.inventory_item_id
and lot_number=p_lot_rec.lot_number;
Select nvl(GRADE_CONTROL_FLAG,'N')
into l_grade_enabled
from mtl_system_items
where organization_id= p_lot_rec.organization_id
and inventory_item_id= p_lot_rec.inventory_item_id;
INV_GRADE_PKG.UPDATE_GRADE(
p_organization_id => p_lot_rec.organization_id
, p_update_method => 2
, p_inventory_item_id => p_lot_rec.inventory_item_id
, p_from_grade_code => l_lot_grade
, p_to_grade_code => p_lot_rec.grade_code
, p_reason_id => NULL
, p_lot_number => p_lot_rec.lot_number
, x_Status => x_return_status
, x_Message => x_msg_data
, p_update_from_mobile => 'N'
, p_primary_quantity => NULL
, p_secondary_quantity => NULL);
END update_inv_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 IN NUMBER
, p_organization_id IN NUMBER
, p_lot_number IN VARCHAR2
, p_expiration_date IN DATE
, p_disable_flag IN NUMBER
, p_attribute_category IN VARCHAR2
, p_lot_attribute_category IN VARCHAR2
, p_attributes_tbl IN inv_lot_api_pub.char_tbl
, p_c_attributes_tbl IN inv_lot_api_pub.char_tbl
, p_n_attributes_tbl IN inv_lot_api_pub.number_tbl
, p_d_attributes_tbl IN inv_lot_api_pub.date_tbl
, p_grade_code IN VARCHAR2
, p_origination_date IN DATE
, p_date_code IN VARCHAR2
, p_status_id IN NUMBER
, p_change_date IN DATE
, p_age IN NUMBER
, p_retest_date IN DATE
, p_maturity_date IN DATE
, p_item_size IN NUMBER
, p_color IN VARCHAR2
, p_volume IN NUMBER
, p_volume_uom IN VARCHAR2
, p_place_of_origin IN VARCHAR2
, p_best_by_date IN DATE
, p_length IN NUMBER
, p_length_uom IN VARCHAR2
, p_recycled_content IN NUMBER
, p_thickness IN NUMBER
, p_thickness_uom IN VARCHAR2
, p_width IN NUMBER
, p_width_uom IN VARCHAR2
, p_territory_code IN VARCHAR2
, p_supplier_lot_number IN VARCHAR2
, p_vendor_name IN VARCHAR2
, p_source IN NUMBER
) IS
l_in_lot_rec MTL_LOT_NUMBERS%ROWTYPE;
l_in_lot_rec.last_update_date := SYSDATE ;
l_in_lot_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_in_lot_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
--Bug 13553990 keeping the value of columns without getting updated to NULL which are not passed.
if (p_source <> 2) then
l_in_lot_rec.origination_type := -99;
Update_Inv_lot(
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_lot_rec => x_lot_rec
, p_lot_rec => l_in_lot_rec
, p_source => p_source
, p_api_version => l_api_version
, p_init_msg_list => l_init_msg_list
, p_commit => l_commit
);
print_debug('Program Update_Inv_lot return ' || l_return_status, 9);
print_debug('Program Update_Inv_lot has failed with a user defined exception', 9);
print_debug('Program Update_Inv_lot has failed with a Unexpected exception', 9);
FND_MESSAGE.SET_TOKEN('PROG_NAME','Update_Inv_lot');
print_debug('End of the program Update_Inv_lot. Program has completed successfully ', 9);
END update_inv_lot;
SELECT child_lot_flag, parent_child_generation_flag,
child_lot_prefix, child_lot_validation_flag,
child_lot_starting_number
FROM mtl_system_items_b -- NSRIVAST, Changed the name to MTL_SYSTEM_ITEMS_B as per review comments by Shelly
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT lot_number_generation,
parent_child_generation_flag,
child_lot_alpha_prefix, child_lot_number_length,
child_lot_validation_flag, child_lot_zero_padding_flag
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT NVL( lot_divisible_flag, 'Y')
,lot_control_code
,tracking_quantity_ind
FROM mtl_system_items_b -- NSRIVAST, Changed the name to MTL_SYSTEM_ITEMS_B as per review comments by Shelly
WHERE organization_id = org_id
AND inventory_item_id = item_id;
SELECT transaction_action_id
, transaction_source_type_id
FROM mtl_transaction_types
WHERE transaction_type_id = trx_type_id;
SELECT lot_number
FROM mtl_lot_numbers
WHERE lot_number = lot_no
AND inventory_item_id = item_id
AND organization_id = org_id; */
SELECT 1
FROM mtl_transaction_lot_numbers mtln, mtl_material_transactions mmt
WHERE mtln.organization_id = p_organization_id
AND mtln.inventory_item_id = p_inventory_item_id
AND mtln.lot_number = p_lot_number
AND mtln.transaction_id = mmt.transaction_id
AND (mmt.transaction_action_id = 1 and mmt.transaction_type_id not in (1,32));
SELECT Nvl(revision_qty_control_code,1)
INTO l_revision_qty_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
/* Update MTI table with error code/explanation */
--errupdate(p_rowid);
/* Update MTI table with error code/explanation */
--errupdate(p_rowid);
/* Update MTI table with error code/explanation */
--errupdate(p_rowid);
/* Update MTI table with error code/explanation */
--errupdate(p_rowid);
l_in_lot_rec.last_update_date := SYSDATE ;
l_in_lot_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_in_lot_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
SELECT lot_control_code,
child_lot_flag,
copy_lot_attribute_flag,
shelf_life_code,
grade_control_flag
FROM mtl_system_items_b -- NSRIVAST, Changed the name to MTL_SYSTEM_ITEMS_B as per review comments by Shelly
WHERE inventory_item_id = cp_inventory_item_id
AND organization_id = cp_organization_id;
SELECT lot_number
FROM mtl_lot_numbers
WHERE lot_number = cp_lot_number AND
inventory_item_id = cp_inventory_item_id AND
organization_id = cp_organization_id;
SELECT copy_lot_attribute_flag,
lot_number_generation
FROM mtl_parameters
WHERE organization_id = cp_organization_id;
SELECT gen_object_id, ROWID
FROM mtl_lot_numbers
WHERE lot_number = cp_lot_number AND
inventory_item_id = cp_inventory_item_id AND
organization_id = cp_organization_id;
Mtl_Lot_Numbers_Pkg.Insert_Row(
x_inventory_item_id => l_child_lot_rec.inventory_item_id
, x_organization_id => l_child_lot_rec.organization_id
, x_lot_number => l_child_lot_rec.lot_number
, x_parent_lot_number => l_child_lot_rec.parent_lot_number
, x_supplier_lot_number => l_child_lot_rec.supplier_lot_number
, x_grade_code => l_child_lot_rec.grade_code
, x_origination_date => l_child_lot_rec.origination_date
, x_date_code => l_child_lot_rec.date_code
, x_status_id => l_child_lot_rec.status_id
, x_change_date => l_child_lot_rec.change_date
, x_age => l_child_lot_rec.age
, x_retest_date => l_child_lot_rec.retest_date
, x_maturity_date => l_child_lot_rec.maturity_date
, x_lot_attribute_category => l_child_lot_rec.lot_attribute_category
, x_item_size => l_child_lot_rec.item_size
, x_color => l_child_lot_rec.color
, x_volume => l_child_lot_rec.volume
, x_volume_uom => l_child_lot_rec.volume_uom
, x_place_of_origin => l_child_lot_rec.place_of_origin
, x_best_by_date => l_child_lot_rec.best_by_date
, x_length => l_child_lot_rec.length
, x_length_uom => l_child_lot_rec.length_uom
, x_recycled_content => l_child_lot_rec.recycled_content
, x_thickness => l_child_lot_rec.thickness
, x_thickness_uom => l_child_lot_rec.thickness_uom
, x_width => l_child_lot_rec.width
, x_width_uom => l_child_lot_rec.width_uom
, x_territory_code => l_child_lot_rec.territory_code
, x_expiration_date => l_child_lot_rec.expiration_date
, x_disable_flag => l_child_lot_rec.disable_flag
, x_attribute_category => l_child_lot_rec.attribute_category
, x_origination_type => l_child_lot_rec.origination_type
, x_expiration_action_date => l_child_lot_rec.expiration_action_date
, x_expiration_action_code => l_child_lot_rec.expiration_action_code
, x_hold_date => l_child_lot_rec.hold_date
, x_last_update_date => SYSDATE
, x_last_updated_by => FND_GLOBAL.USER_ID
, x_creation_date => SYSDATE
, x_created_by => FND_GLOBAL.USER_ID
, x_last_update_login => FND_GLOBAL.LOGIN_ID
, x_attribute1 => l_child_lot_rec.attribute1
, x_attribute2 => l_child_lot_rec.attribute2
, x_attribute3 => l_child_lot_rec.attribute3
, x_attribute4 => l_child_lot_rec.attribute4
, x_attribute5 => l_child_lot_rec.attribute5
, x_attribute6 => l_child_lot_rec.attribute6
, x_attribute7 => l_child_lot_rec.attribute7
, x_attribute8 => l_child_lot_rec.attribute8
, x_attribute9 => l_child_lot_rec.attribute9
, x_attribute10 => l_child_lot_rec.attribute10
, x_attribute11 => l_child_lot_rec.attribute11
, x_attribute12 => l_child_lot_rec.attribute12
, x_attribute13 => l_child_lot_rec.attribute13
, x_attribute14 => l_child_lot_rec.attribute14
, x_attribute15 => l_child_lot_rec.attribute15
, x_c_attribute1 => l_child_lot_rec.c_attribute1
, x_c_attribute2 => l_child_lot_rec.c_attribute2
, x_c_attribute3 => l_child_lot_rec.c_attribute3
, x_c_attribute4 => l_child_lot_rec.c_attribute4
, x_c_attribute5 => l_child_lot_rec.c_attribute5
, x_c_attribute6 => l_child_lot_rec.c_attribute6
, x_c_attribute7 => l_child_lot_rec.c_attribute7
, x_c_attribute8 => l_child_lot_rec.c_attribute8
, x_c_attribute9 => l_child_lot_rec.c_attribute9
, x_c_attribute10 => l_child_lot_rec.c_attribute10
, x_c_attribute11 => l_child_lot_rec.c_attribute11
, x_c_attribute12 => l_child_lot_rec.c_attribute12
, x_c_attribute13 => l_child_lot_rec.c_attribute13
, x_c_attribute14 => l_child_lot_rec.c_attribute14
, x_c_attribute15 => l_child_lot_rec.c_attribute15
, x_c_attribute16 => l_child_lot_rec.c_attribute16
, x_c_attribute17 => l_child_lot_rec.c_attribute17
, x_c_attribute18 => l_child_lot_rec.c_attribute18
, x_c_attribute19 => l_child_lot_rec.c_attribute19
, x_c_attribute20 => l_child_lot_rec.c_attribute20
, x_d_attribute1 => l_child_lot_rec.d_attribute1
, x_d_attribute2 => l_child_lot_rec.d_attribute2
, x_d_attribute3 => l_child_lot_rec.d_attribute3
, x_d_attribute4 => l_child_lot_rec.d_attribute4
, x_d_attribute5 => l_child_lot_rec.d_attribute5
, x_d_attribute6 => l_child_lot_rec.d_attribute6
, x_d_attribute7 => l_child_lot_rec.d_attribute7
, x_d_attribute8 => l_child_lot_rec.d_attribute8
, x_d_attribute9 => l_child_lot_rec.d_attribute9
, x_d_attribute10 => l_child_lot_rec.d_attribute10
, x_n_attribute1 => l_child_lot_rec.n_attribute1
, x_n_attribute2 => l_child_lot_rec.n_attribute2
, x_n_attribute3 => l_child_lot_rec.n_attribute3
, x_n_attribute4 => l_child_lot_rec.n_attribute4
, x_n_attribute5 => l_child_lot_rec.n_attribute5
, x_n_attribute6 => l_child_lot_rec.n_attribute6
, x_n_attribute7 => l_child_lot_rec.n_attribute7
, x_n_attribute8 => l_child_lot_rec.n_attribute8
, x_n_attribute9 => l_child_lot_rec.n_attribute9
, x_n_attribute10 => l_child_lot_rec.n_attribute10
, x_request_id => NULL
, x_program_application_id => NULL
, x_program_id => NULL
, x_program_update_date => NULL
, x_curl_wrinkle_fold => l_child_lot_rec.curl_wrinkle_fold
, x_description => l_child_lot_rec.description
, x_vendor_id => l_child_lot_rec.vendor_id
, x_sampling_event_id => l_child_lot_rec.sampling_event_id
);
SELECT TO_ORGANIZATION_ID
,SUBINVENTORY
,locator_id
,item_id
,PARENT_TRANSACTION_ID
,UNIT_OF_MEASURE
FROM RCV_TRANSACTIONS_INTERFACE
WHERE INTERFACE_TRANSACTION_ID = l_rti_id;
SELECT primary_quantity , primary_unit_of_measure
FROM rcv_transactions
WHERE transaction_id = l_rt_id ;
SELECT lot_divisible_flag
, tracking_quantity_ind
, secondary_default_ind
, secondary_uom_code
, primary_uom_code
FROM mtl_system_items
WHERE organization_id = org_id
AND inventory_item_id = item_id;
SELECT 1
FROM mtl_lot_uom_class_conversions
WHERE organization_id = p_org_id
AND lot_number = p_lot_number
AND inventory_item_id = p_inventory_item_id
AND TO_UOM_CODE=p_to_UOM
AND FROM_UOM_CODE=p_from_UOM;
SELECT LOT_NUMBER, EXPIRATION_DATE FROM MTL_LOT_NUMBERS
WHERE ORGANIZATION_ID =p_org_id
AND INVENTORY_ITEM_ID = p_inventory_item_id
AND LOT_NUMBER =p_lot_number;
select transaction_action_id INTO l_transaction_action_id
FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE transaction_temp_id=p_transaction_temp_id;
SELECT create_lot_uom_conversion INTO l_create_lot_uom_conversion
FROM mtl_parameters
WHERE organization_id =p_organization_id ;
SELECT uom_code INTO l_to_UOM_code
FROM mtl_units_of_measure
WHERE uom_class IN (SELECT uom_class
FROM mtl_units_of_measure
WHERE uom_code = p_to_UOM_code)
AND base_uom_flag = 'Y';
SELECT uom_code INTO l_from_UOM_code
FROM mtl_units_of_measure
WHERE uom_class IN (SELECT uom_class
FROM mtl_units_of_measure
WHERE uom_code = p_from_UOM_code)
AND base_uom_flag = 'Y';
SELECT EXPIRATION_DATE INTO l_expiration_date FROM MTL_LOT_NUMBERS
WHERE ORGANIZATION_ID =p_xfr_organization_id
AND INVENTORY_ITEM_ID = p_inventory_item_id
AND LOT_NUMBER =p_lot_number;
inv_lot_api_pub.insertlot(
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_commit => fnd_api.g_false
, p_validation_level => fnd_api.g_valid_level_full
, p_inventory_item_id => p_inventory_item_id
, p_organization_id => p_organization_id
, p_lot_number => p_lot_number
, p_expiration_date => l_expiration_date
, p_transaction_temp_id => p_transaction_temp_id
, p_transaction_action_id => l_transaction_action_id
, p_transfer_organization_id => p_xfr_organization_id
, x_object_id => l_transaction_action_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
inv_log_util.trace('Lot insertion failed. ',g_pkg_name,9);
SELECT MTL_CONVERSION_ID_S.NEXTVAL
FROM FND_DUAL;
INSERT INTO MTL_LOT_UOM_CLASS_CONVERSIONS(
CONVERSION_ID,
LOT_NUMBER,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
FROM_UNIT_OF_MEASURE,
FROM_UOM_CODE,
FROM_UOM_CLASS,
TO_UNIT_OF_MEASURE,
TO_UOM_CODE,
TO_UOM_CLASS,
CONVERSION_RATE,
DISABLE_DATE,
EVENT_SPEC_DISP_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
SELECT
l_conv_seq
, LOT_NUMBER
, p_organization_id
, INVENTORY_ITEM_ID
, FROM_UNIT_OF_MEASURE
, FROM_UOM_CODE
, FROM_UOM_CLASS
, TO_UNIT_OF_MEASURE
, TO_UOM_CODE
, TO_UOM_CLASS
, CONVERSION_RATE
, DISABLE_DATE
, EVENT_SPEC_DISP_ID
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
FROM MTL_LOT_UOM_CLASS_CONVERSIONS
where LOT_NUMBER=p_lot_number
and ORGANIZATION_ID=p_xfr_organization_id
and INVENTORY_ITEM_ID=p_inventory_item_id
AND TO_UOM_CODE=p_to_UOM_code
AND FROM_UOM_CODE=p_from_UOM_code ;