Use of inv_lot_api_pub API for Inserting & Auto Generating the Lot Numbers

A lot number is a combination of an alphanumeric prefix and a numeric suffix. When we define an item, we can specify the starting lot prefix and the starting lot number. Oracle Inventory uses this information to generate defaults during transaction entry.

1. Establish lot control for an item.

We can establish lot control for an item when We define it. We can choose from No control or Full control. If We choose lot control We must assign lot numbers when We receive the item into inventory. Thereafter, when We transact this item, We must provide a lot number We specified when We received the item.We can update lot control options for an item if it has zero on-hand quantity.
2 . Establish lot number uniqueness.

We use the Organization Parameters window to specify whether lot numbers should be unique for an item. If We do not establish lot number uniqueness, We can assign the same lot number to multiple items in the same organization and across organizations.

If We control lot number uniqueness at the Master level, We can assign a specific lot number only to one item in the same organization and across organizations. When We perform transactions, Oracle Inventory checks the lot number uniqueness control to generate lot number defaults.

3. Optionally, determine whether to automatically generate lot number defaults.

We use the Organization Parameters window to specify how to generate lot number defaults. We can choose to generate sequential lot numbers based on an alphanumeric prefix We specify when We define an item. Oracle Inventory can also generate lot number defaults for the entire organization. In this case, We must define a lot number prefix at the Organization level in the Organization Parameters window.

How to generate/insert lot numbers using inv_lot_api_pub.auto_gen_lot API?================================================================

-- R12 - INV - Sample Script to Generate Lot Number using inv_lot_api_pub

DECLARE

l_chr_lot_number VARCHAR2 (50);
l_chr_return_status VARCHAR2 (2000);
l_num_msg_count NUMBER;
l_chr_msg_data VARCHAR2 (50);

Cursor c_item_info is

select * from mtl_system_items_kfv
where concatenated_segments = 'TSTITEM^3M' -- Enter the item for which Lot Number needs to be created
and organization_id = 381; -- Enter the organization_id

BEGIN

-- initialization required for R12
mo_global.set_policy_context ('S', 308);
mo_global.init('INV');

-- Initialization for Organization_id
inv_globals.set_org_id (381);

-- initialize environment
fnd_global.apps_initialize (user_id => 2083,
resp_id => 20634,
resp_appl_id => 401);

For i in c_item_info

LOOP
dbms_output.put_line ('Calling inv_lot_api_pub.auto_gen_lot API to Create Lot Numbers');
dbms_output.put_line ('*********************************************');

l_chr_lot_number := inv_lot_api_pub.auto_gen_lot (
p_org_id => i.organization_id,
p_inventory_item_id => i.inventory_item_id,
p_parent_lot_number => NULL,
p_subinventory_code => NULL,
p_locator_id => NULL,
p_api_version => 1.0,
p_init_msg_list => 'F',
p_commit => 'T',
p_validation_level => 100,
x_return_status => l_chr_return_status,
x_msg_count => l_num_msg_count,
x_msg_data => l_chr_msg_data);

dbms_output.put_line ('The Status Returned by the API is => ' l_chr_return_status);

IF l_chr_return_status = 'S'
THEN
COMMIT;
ELSE
ROLLBACK;
END IF;

dbms_output.put_line ('The Message Count Returned by the API is => ' l_num_msg_count);
dbms_output.put_line ('The Message Returned by the API is => ' l_chr_return_status);
dbms_output.put_line ('Lot Number Created for the item ' i.concatenated_segments ' is => ' l_chr_lot_number);

END LOOP;

END;

-- R12 - INV - Sample Script to Insert Lot Number using inv_lot_api_pub


DECLARE
x_object_id NUMBER;
x_return_status VARCHAR2 (1);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (4000);
x_expire_date DATE;

Cursor c_item_info is

LOOP

select * from mtl_system_items_kfv
where concatenated_segments = 'TSTITEM^3M' -- Enter the item for which Lot Number needs to be created
and organization_id = 381; -- Enter the organization_id

BEGIN

-- initialization required for R12
mo_global.set_policy_context ('S', 308);
mo_global.init('INV');

-- Initialization for Organization_id
inv_globals.set_org_id (381);

-- initialize environment
fnd_global.apps_initialize (user_id => 2083,
resp_id => 20634,
resp_appl_id => 401);

For i in c_item_info

dbms_output.put_line ('Calling inv_lot_api_pub.auto_gen_lot API to Create Lot Numbers');
dbms_output.put_line ('*********************************************');

inv_lot_api_pub.insertlot
(p_api_version => 1,
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 => i.inventory_item_id,
p_organization_id => i.organization_id,
p_lot_number => 'A6644001',
p_expiration_date => x_expire_date,
x_object_id => x_object_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);

dbms_output.put_line ('The Status Returned by the API is => 'x_return_status);

IF x_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
ELSE
ROLLBACK;
END IF;

DBMS_OUTPUT.put_line ('x_object_id :' x_object_id);
DBMS_OUTPUT.put_line ('x_msg_count :' x_msg_count);
DBMS_OUTPUT.put_line ('x_msg_data :' x_msg_data);

END LOOP;
END;