Tuesday, October 19, 2010

Generation of a LOT number for an Inventory item via API in R12


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 

Test Instance: R12.1.1
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;

0 Responses to “Generation of a LOT number for an Inventory item via API in R12”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.