Saturday, March 28, 2015

Oracle Apps Script to Add Responsibility to User

Below is Simple script that one can use to Add responsibility to Oracle Apps User

 DECLARE
   v_appl_shrt_name   VARCHAR2 (20);
   v_appl_name           VARCHAR2 (50);
   v_resp_key              VARCHAR2 (50);
   v_user_name            VARCHAR2 (20) := 'DUMUSER';
   v_req_resp_name    VARCHAR2 (50) := 'DUMRESP';
   v_description            VARCHAR2 (100) :='DUMSCRIPT';
BEGIN
   SELECT fav.application_short_name,
                  fav.application_name,
                  frv.responsibility_key
     INTO   v_appl_shrt_name,
                 v_appl_name,
                 v_resp_key
     FROM FND_APPLICATION_VL fav,
                 FND_RESPONSIBILITY_VL frv
    WHERE frv.application_id = fav.application_id
     AND     frv.responsibility_name = v_req_resp_name;

   fnd_user_pkg.addresp
                         (username         => v_user_name,
                          resp_app         => v_appl_shrt_name,
                         resp_key          => v_resp_key,
                         security_group  => 'STANDARD',
                         description        => v_description,
                         start_date          => SYSDATE,
                         end_date           => NULL);
   COMMIT;
   DBMS_OUTPUT.put_line ('Responsibility ' || v_req_resp_name || ' is added to the user ' || v_user_name);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line ('Responsibility addition Failed: ' || SQLCODE || '; '
                                            || SUBSTR (SQLERRM, 1, 250));
      ROLLBACK;
END;

No comments: