User and Responsibilty creation through API in Oracle R12.2.X

API to create users in Oracle APPS 

SET SERVEROUTPUT ON;

DECLARE

   lc_user_name                      NUMBER    := 000;

   v_user_name varchar2(200);

   lc_user_password                  VARCHAR2(100)   := 'Welcome123';

   ld_user_start_date                DATE := sysdate;

   ld_user_end_date                  VARCHAR2(100)   := NULL;

   ld_password_date                  VARCHAR2(100)   := sysdate;

   ld_password_lifespan_days  NUMBER          := 90;

   ln_person_id                      NUMBER          := 3164;    --This is the employee id which will get assigned, please change this.

   lc_email_address                  VARCHAR2(100) := null;

BEGIN

LOOP 

v_user_name :='PRANAY';

  fnd_user_pkg.createuser

(x_user_name                => v_user_name,

x_owner                    => NULL,

x_unencrypted_password     => lc_user_password,

x_start_date => ld_user_start_date,

x_end_date                 => ld_user_end_date,

x_password_date            => ld_password_date,

x_password_lifespan_days   => ld_password_lifespan_days,

x_employee_id              => ln_person_id,

x_email_address            => lc_email_address);

 

COMMIT;

lc_user_name := lc_user_name +1;

EXIT WHEN lc_user_name > 50;

END LOOP;

EXCEPTION

       WHEN OTHERS THEN

                       ROLLBACK;

                       DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

/

Sample code to assign responsibilities for the users

DECLARE

  lc_security_group_key VARCHAR2(100) := 'STANDARD';

  CURSOR cur_resp

  IS

    SELECT fr.RESPONSIBILITY_KEY ,

      fa.APPLICATION_SHORT_NAME

    FROM apps.FND_RESPONSIBILITY_VL fr,

      apps.FND_APPLICATION fa

    WHERE fa.APPLICATION_ID     = fr.APPLICATION_ID

    AND fr.RESPONSIBILITY_NAME IN (<ENTER THE RESPONISIBILTIES NAME >);

  CURSOR CUR_USER

  IS

    SELECT USER_NAME FROM FND_USER WHERE USER_NAME LIKE 'PRANAY%';

BEGIN

  FOR i IN cur_resp

  LOOP

    BEGIN

      FOR j IN cuR_user

      LOOP

        BEGIN

          fnd_user_pkg.addresp ( username => j.user_name, resp_app => i.APPLICATION_SHORT_NAME, resp_key => i.RESPONSIBILITY_KEY, security_group => lc_security_group_key, description => NULL, start_date => SYSDATE, end_date => NULL );

          COMMIT;

          DBMS_OUTPUT.put_line ('Responsibility ' || i.RESPONSIBILITY_KEY || ' is Assigned to User');

        EXCEPTION

        WHEN OTHERS THEN

          DBMS_OUTPUT.put_line ('Exception inside for loop: ' || SQLERRM);

        END;

      END LOOP;

    END;

  END LOOP;

EXCEPTION

WHEN OTHERS THEN

  DBMS_OUTPUT.put_line ('Main Exception ' || SQLERRM);

END;