Simple API to reset password and add/end responsibility.

Introduction:-

In this article, we will learn about API to reset password and add/end responsibility to user in oracle apps. To reset password and responsibility below API will is very useful. Put the username once and it will reuse till all task done.

Here I am adding the API to reset the password for EBS user.

DECLARE

    lb_status BOOLEAN;

    v_username varchar2(100):='&&Username';




    v_password  varchar2(200);

BEGIN

select user_password into v_password from ace_users where user_code=v_username ;

if v_password is not null then

    lb_status := FND_USER_PKG.CHANGEPASSWORD

                                                            (

                                                                username           => v_username,

                                                               newpassword     => v_password

                                                           );

  end if ;




    IF lb_status

    THEN

        DBMS_OUTPUT.PUT_LINE('Request processed sucessfully');

    ELSE

        DBMS_OUTPUT.PUT_LINE('Error while processing the request');

    END IF;

END;

/

Now will see how API to end date user responsibility in oracle apps.

DECLARE

   v_user_name                   VARCHAR2 (100) := '&&Username';

   v_responsibility_name   VARCHAR2 (100) := 'Sourcing Supplier';

   v_application_name        VARCHAR2 (100) := NULL;

   v_responsibility_key        VARCHAR2 (100) := NULL;

   v_security_group              VARCHAR2 (100) := NULL;





BEGIN

   SELECT  fa.application_short_name,

                     fr.responsibility_key,

                     frg.security_group_key

    INTO        v_application_name,

                      v_responsibility_key,

                      v_security_group

    FROM      fnd_responsibility       fr,

                     fnd_application            fa,

                     fnd_security_groups  frg,

                     fnd_responsibility_tl   frt

    WHERE  fr.application_id               = fa.application_id

     AND        fr.data_group_id             = frg.security_group_id

     AND        fr.responsibility_id          = frt.responsibility_id

     AND        frt.LANGUAGE                 = USERENV ('LANG')

     AND        frt.responsibility_name  = v_responsibility_name;




      fnd_user_pkg.delresp

      (    username           => v_user_name,

           resp_app            => v_application_name,

           resp_key             => v_responsibility_key,

           security_group   => v_security_group

      );




     COMMIT;




     DBMS_OUTPUT.put_line (   'Responsiblity '

        || v_responsibility_name

        || ' is removed from the user '

        || v_user_name

        || ' Successfully'

        );



EXCEPTION

     WHEN OTHERS THEN

              DBMS_OUTPUT.put_line

               (   'Error encountered while deleting responsibilty from the user and the error is '

                    || SQLERRM

                );

END;

/

Next API is to add responsibility to user in oracle apps.

DECLARE

   v_user_name             VARCHAR2 (30)  := '&&Username';

   v_responsibility_name   VARCHAR2 (100) := 'ACE TA/DA Users';

   v_application_name      VARCHAR2 (100) := NULL;

   v_responsibility_key    VARCHAR2 (100) := NULL;

   v_security_group        VARCHAR2 (100) := NULL;

   v_description           VARCHAR2 (100) := NULL;

BEGIN

   SELECT fa.application_short_name, fr.responsibility_key,

          fsg.security_group_key, frt.description

     INTO v_application_name, v_responsibility_key,

          v_security_group, v_description

     FROM apps.fnd_responsibility fr,

          fnd_application fa,

          fnd_security_groups fsg,

          fnd_responsibility_tl frt

    WHERE frt.responsibility_name = v_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            => v_user_name,

                         resp_app            => v_application_name,

                         resp_key            => v_responsibility_key,

                         security_group      => v_security_group,

                         description         => v_description,

                         start_date          => SYSDATE,

                         end_date            => NULL

                        );

   COMMIT;

   DBMS_OUTPUT.put_line(   'Responsiblity '

                         || v_responsibility_name

                         || ' is attached to the user '

                         || v_user_name

                         || ' Successfully'

                        );

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line

                         (   'Unable to attach responsibility to user due to'

                          || SQLCODE

                          || ' '

                          || SUBSTR (SQLERRM, 1, 100)

                         );

END;

/

SHOW ERR;

exit;

                 

Leave a Comment