Generation of Lot number and associating with an Inventory item and organization involves two steps
· Generation of Next Lot Number using the api INV_LOT_API_PUB.AUTO_GEN_LOT
· Association/Insertion of the Lot Number to a inventory item and organization (For
Script:
DECLARE
v_object_id NUMBER;
v_return_status VARCHAR2(1) := NULL;
v_msg_count NUMBER := 0;
v_msg_data VARCHAR2 (2000);
v_lot_number VARCHAR2 (50);
v_expire_date DATE := SYSDATE+180;
v_organization_id NUMBER :=124;
v_inventory_item_id NUMBER :=27839;
FUNCTION set_context( i_user_name IN VARCHAR2
,i_resp_name IN VARCHAR2
,i_org_id IN NUMBER)
RETURN VARCHAR2
IS
/* Inorder to reduce the content of the post I moved the implementation part of this function to another post and it is available here */
END set_context;
BEGIN
-- Setting the context ----
v_context := set_context('&V_USER_NAME','&V_RESPONSIBILITY',82);
IF v_context = 'F'
THEN
DBMS_OUTPUT.PUT_LINE('Error while setting the context');
END IF;
dbms_output.put_line ('Calling API to Create Lot Number');
v_lot_number := inv_lot_api_pub.auto_gen_lot (
p_org_id => v_organization_id,
p_inventory_item_id => v_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 => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);
IF v_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.put_line ('The Auto generation of Lot Number is Sucessful: '||v_lot_number);
ELSE
DBMS_OUTPUT.put_line ('The Auto generation of Lot Number Failed');
ROLLBACK;
FOR i IN 1 .. v_msg_count
LOOP
v_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
dbms_output.put_line( i|| ') '|| v_msg_data);
END LOOP;
END IF;
IF v_lot_number IS NOT NULL THEN
dbms_output.put_line ('Calling API to Create/Insert a Lot Numbers');
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 => v_inventory_item_id,
p_organization_id => v_organization_id,
p_lot_number => v_lot_number,
p_expiration_date => v_expire_date,
x_object_id => v_object_id,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);
IF v_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.put_line ('The Creation of Lot Number is Sucessful: '||v_object_id);
ELSE
DBMS_OUTPUT.put_line ('The Creation of Lot Number Failed');
ROLLBACK;
FOR i IN 1 .. v_msg_count
LOOP
v_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
dbms_output.put_line( i|| ') '|| v_msg_data);
END LOOP;
END IF;
END IF;
END;Ref : http://www.shareoracleapps.com/2010/10/generation-of-lot-number-for-inventory.html
