API to Create a New User,Update User, Create Responsibility, Add Responsibility and Create Menu

CREATE OR REPLACE PACKAGE user_menu_resp
AS
   PROCEDURE createuser (v_user_name VARCHAR2, v_password VARCHAR2);

   PROCEDURE createresp;

   PROCEDURE addresp (v_user_name              VARCHAR2,
                      v_responsibility_name    VARCHAR2,
                      v_application_name       VARCHAR2,
                      v_responsibility_key     VARCHAR2,
                      v_security_group         VARCHAR2,
                      v_description            VARCHAR2);

   PROCEDURE updateuser (v_user_name VARCHAR2, v_start_date DATE);

   PROCEDURE createmenu;
END;




CREATE OR REPLACE PACKAGE BODY user_menu_resp
IS
   --Create Newuser

   PROCEDURE createuser (v_user_name VARCHAR2, v_password VARCHAR2)
   IS
      vl_user_name   VARCHAR2 (30) := v_user_name;

      vl_password    VARCHAR2 (30) := v_password;
   BEGIN
      fnd_user_pkg.
      createuser (
         x_user_name              => vl_user_name,
         x_unencrypted_password   => vl_password,
         x_owner                  => NULL,
         x_start_date             => TO_DATE ('8/2/2014', 'mm/dd/yyyy'));

      COMMIT;

      DBMS_OUTPUT.put_line ('user:' || v_user_name || 'created successfully');
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.
         put_line (
               'unable to create user due to'
            || SQLCODE
            || ' '
            || SUBSTR (SQLERRM, 1, 100));

         ROLLBACK;
   END;

   --Create Responsibility

   PROCEDURE createresp
   IS
      v_rowid               VARCHAR2 (500);

      v_web_host_name       VARCHAR2 (500);

      v_web_agent_name      VARCHAR2 (500);

      v_version             VARCHAR2 (500);

      v_responsibility_id   NUMBER;

      -- some variables

      v_resp_name           VARCHAR2 (100) := 'Apps Developer Info';

      v_application         VARCHAR2 (100) := 'XML Publisher';

      v_resp_key            VARCHAR2 (100) := 'Apps Developer';

      v_menu_name           VARCHAR2 (100) := 'GL_SUPERUSER';

      v_data_group          VARCHAR2 (100) := 'Standard';

      v_req_group           VARCHAR2 (100) := 'XML Publisher Request Set';

      -- ids and other crap used by the API

      v_application_id      NUMBER;

      v_data_group_id       NUMBER;

      v_menu_id             NUMBER;

      v_request_group_id    NUMBER;
   BEGIN
      -- get application_id

      SELECT application_id
        INTO v_application_id
        FROM fnd_application_vl
       WHERE application_name = v_application;

      -- get data group id

      SELECT data_group_id
        INTO v_data_group_id
        FROM fnd_data_groups
       WHERE data_group_name = v_data_group;

      -- get the menu_id

      SELECT menu_id
        INTO v_menu_id
        FROM fnd_menus
       WHERE menu_name = v_menu_name;

      -- get the request_group_id

      SELECT request_group_id
        INTO v_request_group_id
        FROM fnd_request_groups
       WHERE request_group_name = v_req_group;

      -- get current responsibility_id

      SELECT FND_RESPONSIBILITY_S.NEXTVAL INTO v_responsibility_id FROM DUAL;

      -- run API

      fnd_responsibility_pkg.
      insert_row (                                               -- out params
                  x_rowid                       => v_rowid,
                  -- in params

                  x_responsibility_id           => v_responsibility_id,
                  x_application_id              => v_application_id,
                  x_web_host_name               => v_web_host_name,
                  x_web_agent_name              => v_web_agent_name,
                  x_data_group_application_id   => v_application_id,
                  x_data_group_id               => v_data_group_id,
                  x_menu_id                     => v_menu_id,
                  x_start_date                  => TO_DATE ('01-JAN-1951'),
                  x_end_date                    => NULL,
                  x_group_application_id        => v_application_id,
                  x_request_group_id            => v_request_group_id,
                  x_version                     => v_version,
                  x_responsibility_key          => v_resp_key,
                  x_responsibility_name         => v_resp_name,
                  x_description                 => ”,
                  x_creation_date               => SYSDATE,
                  x_created_by                  => -1,
                  x_last_update_date            => SYSDATE,
                  x_last_updated_by             => -1,
                  x_last_update_login           => 0);
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('failed to add resp' || SQLERRM);
   END;

   --Add Responsibility To user

   PROCEDURE addresp (v_user_name              VARCHAR2,
                      v_responsibility_name    VARCHAR2,
                      v_application_name       VARCHAR2,
                      v_responsibility_key     VARCHAR2,
                      v_security_group         VARCHAR2,
                      v_description            VARCHAR2)
   IS
      vl_user_name             VARCHAR2 (30) := v_user_name;

      vl_responsibility_name   VARCHAR2 (30) := v_responsibility_name;

      vl_application_name      VARCHAR2 (30) := v_application_name;

      vl_responsibility_key    VARCHAR2 (30) := v_responsibility_key;

      vl_security_group        VARCHAR2 (30) := v_security_group;

      vl_description           VARCHAR2 (30) := v_description;
   BEGIN
      SELECT fa.application_short_name,
             fr.responsibility_key,
             fsg.security_group_key,
             frt.description
        INTO vl_application_name,
             vl_responsibility_key,
             vl_security_group,
             vl_description
        FROM apps.fnd_responsibility fr,
             fnd_application fa,
             fnd_Security_groups fsg,
             fnd_responsibility_tl frt
       WHERE     frt.responsibility_name = vl_responsibility_name
             AND frt.language = USERENV ('lang')
             AND frt.responsibility_id = fr.responsibility_id
             AND fr.application_id = fa.application_id
             AND fr.data_group_id = fsg.security_group_id;

      fnd_user_pkg.
      addresp (username         => vl_user_name,
               resp_app         => vl_application_name,
               resp_key         => vl_responsibility_key,
               security_group   => vl_security_group,
               description      => vl_description,
               start_date       => TO_DATE ('8/2/2012', 'mm/dd/yyyy'),
               end_date         => NULL);

      COMMIT;

      DBMS_OUTPUT.
      put_line (
            'responsibility'
         || vl_responsibility_name
         || 'is attached to the user'
         || vl_user_name
         || ' successfully');
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.
         put_line (
               'unable to create user due to'
            || SQLCODE
            || ' '
            || SUBSTR (SQLERRM, 1, 100));

         ROLLBACK;
   END;

   --Update User Infomation

   PROCEDURE updateuser (v_user_name VARCHAR2, v_start_date DATE)
   IS
      vl_user_name    VARCHAR2 (30) := v_user_name;

      vl_start_date   DATE := v_start_date;
   BEGIN
      fnd_user_pkg.updateuser (x_user_name    => vl_user_name,
                               x_owner        => NULL,
                               x_end_date     => NULL,
                               x_start_date   => vl_start_date);

      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.
         put_line (
               'unable to create password due to'
            || SQLCODE
            || ' '
            || SUBSTR (SQLERRM, 1, 100));

         ROLLBACK;
   END;

   --Create Menu

   PROCEDURE createmenu
   IS
      v_rowid           VARCHAR2 (20);

      v_menu_id         NUMBER (20);

      v_menu_name       VARCHAR2 (200) := 'first tax account Menu';

      v_function_name   VARCHAR2 (200) := 'ZX_TAX_ACCOUNT_VIEW';

      vl_menu_id        VARCHAR2 (20);
   BEGIN
      SELECT FND_MENUS_S.NEXTVAL INTO vl_menu_id FROM DUAL;

      fnd_menus_pkg.INSERT_ROW (x_rowid               => v_rowid,
                                x_menu_id             => vl_menu_id,
                                x_menu_name           => v_menu_name,
                                x_user_menu_name      => v_menu_name,
                                x_menu_type           => NULL,
                                x_description         => NULL,
                                x_creation_Date       => SYSDATE,
                                x_created_by          => fnd_global.user_id,
                                x_last_update_date    => SYSDATE,
                                x_last_updated_by     => fnd_global.user_id,
                                x_last_update_login   => fnd_global.login_id);

      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('failed to create menu' || SQLERRM);
   END;
END user_menu_resp;

Updating Item Category for an Item using API INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_ASSIGNMENT

REREQUISITE
-----------------
Following are prerequisite before updating category for an Item.

  1. Organization must be defined in Oracle.
  2. Item must be defined.
  3. Category set name and Category Combination Should already exists for the Item.
  4. New Category that needs to be assigned to the Item Should be the part of already existing category set name.

In Below example Item 'NewTestItem' is considered. Its inventory item id is 83456 and belong to 'Vision operation' Organization with Org id 106.
Category Set name 'Inventory' with category Set id '1' and Category 'NONE' with category id '5' is already assigned to the Item.
New Category 'TestCategory' with category id 1198 needs to be Updated to the Item.
New Category is part of the Category Set 'Inventory'.
Below Code can be used for Updating the Category set and Category Combination for the item 'NewTestItem'.
Code is tested in 11.5.10


DECLARE

X_RETURN_STATUS  VARCHAR2(1000);
X_ERRORCODE         NUMBER;
X_MSG_COUNT        NUMBER;
X_MSG_DATA          VARCHAR2(1000);
l_error_message       VARCHAR2 (1000) := NULL;
x_msg_index_out      NUMBER;
BEGIN
  --Apps Initialization is available in another section. Use the below link to know in detail
        Apps_Initialize;


   INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_ASSIGNMENT
                                                (  P_API_VERSION            => 1.0, 
                                                   P_INIT_MSG_LIST         => FND_API.G_FALSE, 
                                                   P_COMMIT                   => FND_API.G_FALSE,
                                                   P_CATEGORY_ID           => 5,
                                                   P_OLD_CATEGORY_ID    => 1198, 
                                                   P_CATEGORY_SET_ID    => 1, 
                                                   P_INVENTORY_ITEM_ID  => 83456, 
                                                   P_ORGANIZATION_ID     => 106,
                                                   X_RETURN_STATUS       => X_RETURN_STATUS, 
                                                   X_ERRORCODE              => X_ERRORCODE, 
                                                   X_MSG_COUNT             => X_MSG_COUNT, 
                                                   X_MSG_DATA               => X_MSG_DATA);

    IF x_return_status = fnd_api.g_ret_sts_success
   THEN
      COMMIT;
      DBMS_OUTPUT.put_line ('Item Category Assignment using API is Successful');
   ELSE
      BEGIN
         IF (fnd_msg_pub.count_msg > 1)
         THEN
            FOR k IN 1 .. fnd_msg_pub.count_msg
            LOOP
               fnd_msg_pub.get (p_msg_index            => k,
                                             p_encoded            => 'F',
                                             p_data                 => x_msg_data,
                                             p_msg_index_out   => x_msg_index_out
                                            );

               DBMS_OUTPUT.PUT_LINE('x_msg_data:= ' || x_msg_data);
               IF x_msg_data IS NOT NULL
               THEN
                  l_error_message := l_error_message || '-' || x_msg_data;
               END IF;
            END LOOP;
         ELSE
            --Only one error
            fnd_msg_pub.get (p_msg_index             => 1,
                                           p_encoded            => 'F',
                                           p_data                 => x_msg_data,
                                           p_msg_index_out   => x_msg_index_out
                                         );
            l_error_message := x_msg_data;
         END IF;

         DBMS_OUTPUT.put_line (   'Error encountered by the API is '
                               || l_error_message
                              );
         ROLLBACK;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_error_message := SQLERRM;
            DBMS_OUTPUT.put_line (   'Error encountered by the API is '
                                  || l_error_message
                                 );
      END;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line
                   (   'Error in Assigning Category to an Item and error is '
                    || SUBSTR (SQLERRM, 1, 200)
                   );
END;
/


ref: http://techoracleapps.blogspot.co.uk/2010/05/updating-item-category-for-item-using.html

API - Assign Categries to Inventory Items

DECLARE

X_RETURN_STATUS  VARCHAR2(1000);
X_ERRORCODE         NUMBER;
X_MSG_COUNT        NUMBER;
X_MSG_DATA          VARCHAR2(1000);
X_MSG_INDEX_OUT VARCHAR2(2000);
L_ERROR_MESSAGE VARCHAR2(2000);

BEGIN

  --Apps Initialization is available in another section. Use the below link to know in detail
   --     Apps_Initialize;

INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY_ASSIGNMENT
                                                (  P_API_VERSION           => 1.0,
                                                   P_INIT_MSG_LIST         => FND_API.G_FALSE,
                                                   P_COMMIT                => FND_API.G_FALSE,
                                                   X_RETURN_STATUS         => X_RETURN_STATUS,
                                                   X_ERRORCODE             => X_ERRORCODE,
                                                   X_MSG_COUNT             => X_MSG_COUNT,
                                                   X_MSG_DATA              => X_MSG_DATA,
                                                   P_CATEGORY_ID           => 1355,                                          -- Category id to be assigned
                                                   P_CATEGORY_SET_ID       => 1100000043,                            -- Category Set id
                                                   P_INVENTORY_ITEM_ID     => 25803,                                    -- Inventory Item Id
                                                   P_ORGANIZATION_ID       => 755);                                       -- Warehouse

   IF x_return_status = fnd_api.g_ret_sts_success
   THEN
      COMMIT;
      DBMS_OUTPUT.put_line ('Item Category Assignment using API is Successful');
  
   ELSE
      BEGIN
         IF (fnd_msg_pub.count_msg > 1)
         THEN
            FOR k IN 1 .. fnd_msg_pub.count_msg
            LOOP
               fnd_msg_pub.get (p_msg_index            => k,
                                             p_encoded            => 'F',
                                             p_data                 => x_msg_data,
                                             p_msg_index_out   => x_msg_index_out
                                            );

               DBMS_OUTPUT.PUT_LINE('x_msg_data:= ' || x_msg_data);
               IF x_msg_data IS NOT NULL
               THEN
                  l_error_message := l_error_message || '-' || x_msg_data;
               END IF;
            END LOOP;
         ELSE
            --Only one error
            fnd_msg_pub.get (p_msg_index             => 1,
                                           p_encoded            => 'F',
                                           p_data                 => x_msg_data,
                                           p_msg_index_out   => x_msg_index_out
                                         );
            l_error_message := x_msg_data;
         END IF;

         DBMS_OUTPUT.put_line (   'Error encountered by the API is '
                               || l_error_message
                              );
         ROLLBACK;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_error_message := SQLERRM;
            DBMS_OUTPUT.put_line (   'Error encountered by the API is '
                                  || l_error_message
                                 );
      END;
   END IF;

EXCEPTION
  WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('Error in Assigning Category to an Item and error is '||SUBSTR(SQLERRM,1,200));
END;

FND_PROFILE and FND_GLOBAL values

Following are the FND_PROFILE values that can be used in the PL/SQL code:

   fnd_profile.value('PROFILEOPTION');

   fnd_profile.value('MFG_ORGANIZATION_ID');

   fnd_profile.value('ORG_ID');

   fnd_profile.value('LOGIN_ID');

   fnd_profile.value('USER_ID');

   fnd_profile.value('USERNAME');

   fnd_profile.value('CONCURRENT_REQUEST_ID');

   fnd_profile.value('GL_SET_OF_BKS_ID');

   fnd_profile.value('SO_ORGANIZATION_ID');

   fnd_profile.value('APPL_SHRT_NAME');

   fnd_profile.value('RESP_NAME');

   fnd_profile.value('RESP_ID');

Following are the FND_GLOBAL values that can be used in the PL/SQL code:

   FND_GLOBAL.USER_ID;

   FND_GLOBAL.APPS_INTIALIZE;

   FND_GLOBAL.LOGIN_ID;

   FND_GLOBAL.CONC_LOGIN_ID;

   FND_GLOBAL.PROG_APPL_ID;

   FND_GLOBAL.CONC_PROGRAM_ID;

   FND_GLOBAL.CONC_REQUEST_ID;

For example, I almost always use the following global variable assignments in my package specification to use throughout the entire package body:

   g_user_id      PLS_INTEGER  :=  fnd_global.user_id;

   g_login_id     PLS_INTEGER  :=  fnd_global.login_id;

   g_conc_req_id  PLS_INTEGER  :=  fnd_global.conc_request_id;

   g_org_id       PLS_INTEGER  :=  fnd_profile.value('ORG_ID');

   g_sob_id       PLS_INTEGER  :=  fnd_profile.value('GL_SET_OF_BKS_ID');

And initialize the application environment as follows:

   v_resp_appl_id  := fnd_global.resp_appl_id;

   v_resp_id       := fnd_global.resp_id;

   v_user_id       := fnd_global.user_id;

     

   FND_GLOBAL.APPS_INITIALIZE(v_user_id,v_resp_id, v_resp_appl_id);

 

For more information, please visit http://docs.oracle.com/cd/E18727_01/doc.121/e12897/T302934T462356.htm