How to create new Responsibility in EBS R12

Recently I was asked by the customer to create 3custom  responsibilities from currently existing one responsibility since they were splitting the single OU instance into 3 OU in R12.1.3

DECLARE
   v_rowid               VARCHAR2 (500);
   v_web_host_name       VARCHAR2 (500);
   v_web_agent_name      VARCHAR2 (500);
   v_version             VARCHAR2 (10)  := '4';
   v_responsibility_id   NUMBER;
-- some variables
   v_resp_name           VARCHAR2 (100);
   v_application         VARCHAR2 (100);
   v_resp_key            VARCHAR2 (100);
   v_menu_name           VARCHAR2 (100);
   v_data_group          VARCHAR2 (100);
   v_req_group           VARCHAR2 (100);
-- ids and other **** used by the API
   v_application_id      NUMBER;
   v_data_group_id       NUMBER;
   v_menu_id             NUMBER;
   v_request_group_id    NUMBER;
   lb_round2             BOOLEAN        := TRUE;
   lb_round1             BOOLEAN        := TRUE;
   new_resp1             VARCHAR2 (100);
BEGIN
   FOR x IN (SELECT   frt.responsibility_id, frt.application_id,
                      fr.goup_application_id, fr.responsibility_key,
                      frt.responsibility_name, fr.data_group_id, fr.menu_id,
                      fr.request_group_id
                 FROM fnd_responsibility_tl frt, fnd_responsibility fr
                WHERE responsibility_name LIKE 'XXJP%'
                  AND frt.responsibility_id = fr.responsibility_id
             UNION ALL
             SELECT   frt.responsibility_id, frt.application_id,
                      fr.responsibility_key, frt.responsibility_name,
                      fr.data_group_id, fr.menu_id, fr.request_group_id
                 FROM fnd_responsibility_tl frt, fnd_responsibility fr
                WHERE responsibility_name LIKE 'XXJP%'
                  AND frt.responsibility_id = fr.responsibility_id
             UNION ALL
             SELECT   frt.responsibility_id, frt.application_id,
                      fr.responsibility_key, frt.responsibility_name,
                      fr.data_group_id, fr.menu_id, fr.request_group_id
                 FROM fnd_responsibility_tl frt, fnd_responsibility fr
                WHERE responsibility_name LIKE 'XXJP%'
                  AND frt.responsibility_id = fr.responsibility_id
             ORDER BY 1 DESC)
   LOOP
      new_resp1 := NULL;
      BEGIN
         SELECT DISTINCT new_resp
                    INTO new_resp1
                    FROM xxresp
                   WHERE old_resp = x.responsibility_name;

-- get responsibility name
         IF lb_round2 AND lb_round1
         THEN
            v_resp_name := 'XXJP_STI ' || new_resp1;
            v_resp_key := 'XXJP_STII_' || new_resp1;

--         DBMS_OUTPUT.put_line ('INSIDE STI ' || x.responsibility_name);
            IF LENGTH (v_resp_key) > 30
            THEN
               v_resp_key := SUBSTR (v_resp_key, 1, 30);
            END IF;

            lb_round2 := FALSE;
            lb_round1 := TRUE;
         ELSIF lb_round1 AND NOT lb_round2
         THEN
            v_resp_name := 'XXJP_LATAM ' || new_resp1;
            v_resp_key := 'XXJP_LATAM_' || new_resp1;

            IF LENGTH (v_resp_key) > 30
            THEN
               v_resp_key := SUBSTR (v_resp_key, 1, 30);
            END IF;

            lb_round2 := FALSE;
            lb_round1 := FALSE;
         ELSE
            v_resp_name := 'XXJP US ' || new_resp1;
            v_resp_key := 'XXJP_USS_' || new_resp1;

            IF LENGTH (v_resp_key) > 30
            THEN
               v_resp_key := SUBSTR (v_resp_key, 1, 30);
            END IF;

            lb_round2 := TRUE;
            lb_round1 := TRUE;
         END IF;

-- get application_id
         v_application_id := x.application_id;
-- get data group id
         v_data_group_id := x.data_group_id;
-- get the menu_id
         v_menu_id := x.menu_id;
         v_request_group_id := x.request_group_id;

-- get current responsibility_id
         SELECT fnd_responsibility_s.NEXTVAL
           INTO v_responsibility_id
           FROM DUAL;

         BEGIN
-- 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           => k.group_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
                           );

            INSERT INTO fnd_resp_functions
                        (application_id, responsibility_id, action_id,
                         rule_type, last_update_date, last_updated_by,
                         last_update_login, creation_date, created_by)
               SELECT ff.application_id, v_responsibility_id, ff.action_id,
                      ff.rule_type, ff.last_update_date, ff.last_updated_by,
                      ff.last_update_login, ff.creation_date, ff.created_by
                 FROM fnd_resp_functions ff
                WHERE responsibility_id = x.responsibility_id;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line (   'failed to add new resp'
                                     || x.responsibility_name
                                    );
         END;

         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   'failed in creeating custom responsibility: '|| x.responsibility_name
                                 );
      END;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('failed to create responsibility: ' || SQLERRM);
END;

How to change user password in oracle apps

If it is required to change the user password using a API rather than accessing the
fron end, Here is how one can change the password.

DECLARE
   tmp_res      VARCHAR2 (1)   DEFAULT 'N';
   ln_updated   PLS_INTEGER  DEFAULT 0;
   ln_failed    PLS_INTEGER     DEFAULT 0;
   v_failed     VARCHAR2 (400) DEFAULT '';
   v_end_date   DATE;
   b_ok            BOOLEAN;
BEGIN
   FOR apps_users IN (SELECT *
                        FROM fnd_user
                       WHERE user_name = 'STEPHAN')
   LOOP
      SELECT end_date
        INTO v_end_date
        FROM fnd_user
       WHERE user_name = apps_users.user_name;

      IF v_end_date IS NOT NULL
      THEN
         UPDATE apps.fnd_user
            SET end_date = NULL
          WHERE user_name = apps_users.user_name;
      END IF;

      tmp_res :=
         apps.fnd_web_sec.change_password (apps_users.user_name,
                                           'We1c0me@user',
                                           FALSE
                                          );

      IF tmp_res = 'Y'
      THEN
         DBMS_OUTPUT.put_line ('Password updated');
         ln_updated := ln_updated + 1;
      ELSE
         v_failed := v_failed || '''' || apps_users.user_name || '''' || ', ';
         ln_failed := ln_failed + 1;
         DBMS_OUTPUT.put_line ('Password not updated');
      END IF;
   END LOOP;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
         DBMS_OUTPUT.put_line ('Error while updating Password: '||sqlerrm);
END;

How to Delete Users from FND_USER in oracle apps

Sometimes it is required to purge a user or set of users from EBS and manual task can be tedious or laborious. Here is a simple way of purging such users from the system.

DECLARE BEGIN FOR X IN (select * from fnd_user where user_name in ('DUMMY_USER') LOOP DELETE FROM fnd_user where user_name=X.user_name;     fnd_function_security_cache.delete_user(X.user_id); END LOOP; END;