DECLARE
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(100) := fnd_api.g_false; -- bug 7513308;
l_commit VARCHAR2(100) := fnd_api.g_false;
l_validation_level NUMBER := fnd_api.g_valid_level_full;
l_origin_txn_id NUMBER := NULL;
l_source NUMBER := 2;
l_return_status VARCHAR2(1);
l_msg_data VARCHAR2(3000);
l_msg_count NUMBER;
l_row_id ROWID;
l_count NUMBER;
x_mtl_lot_numbers mtl_lot_numbers%ROWTYPE;
l_mtl_lot_numbers mtl_lot_numbers%ROWTYPE;
CURSOR cur_mtl IS
SELECT l.inventory_item_id,
l.organization_id,
l.lot_number,
l.origination_date,
to_char(l.origination_date, 'YYYY-MM-DD'),
msib.segment1,
msib.segment2,
msib.description,
ood.organization_code,
ood.organization_name,
msib.shelf_life_days
FROM mtl_lot_numbers_all_v l,
org_organization_definitions ood,
mtl_system_items_b msib
WHERE l.organization_id = ood.organization_id
AND l.organization_id = msib.organization_id
AND l.inventory_item_id = msib.inventory_item_id
--AND l.lot_number LIKE '%2012B%'
/*AND to_char(l.origination_date, 'YYYY-MM-DD') <> '2012-07-01'*/
/*AND l.row_id = 'AABCXiAGIAAAWRsAAA'*/
;
BEGIN
fnd_global.apps_initialize(user_id => 1170,
resp_id => 50717,
resp_appl_id => 20005);
--MO_GLOBAL.set_policy_context(p_access_mode => 'S',p_org_id => 83);
-- fnd_profile.PUT(NAME =>'MFG_ORGANIZATION_ID' ,VAL => 83);
FOR rec_mtl IN cur_mtl LOOP
l_mtl_lot_numbers.inventory_item_id := rec_mtl.inventory_item_id;
l_mtl_lot_numbers.organization_id := rec_mtl.organization_id;
l_mtl_lot_numbers.lot_number := rec_mtl.lot_number;
l_mtl_lot_numbers.expiration_date := rec_mtl.origination_date +
rec_mtl.shelf_life_days;
/*l_mtl_lot_numbers.origination_date := to_date('2012-07-01',
'YYYY-MM-DD');*/
inv_lot_api_pub.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_mtl_lot_numbers,
p_lot_rec => l_mtl_lot_numbers,
p_source => l_source,
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('---' || SQLERRM);
END;