Generation of Lot number and associating with an Inventory item and organization

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