Assign/Deassign Responsibility to User

Create Stage Table:

CREATE TABLE XXX.XXXX_USER_RESP_TBL
(USERNAME VARCHAR2(50),
 RESPONSIBILITY_KEY VARCHAR2(240),
 END_DATE_FLAG VARCHAR2(1),
 ERROR_FLAG VARCHAR2(10),
 ERROR_MESSAGE VARCHAR2(2000),
 CREATED_BY VARCHAR2(20),
 CREATION_DATE DATE,
 LAST_UPDATED_BY VARCHAR2(20),
 LAST_UPDATE_DATE DATE);

CREATE SYNONYM XXXX_USER_RESP_TBL  FOR XXX.XXXX_USER_RESP_TBL ;

Create Control File to Load Data into Stage Table:

LOAD DATA
APPEND
PRESERVE BLANKS
INTO TABLE XXXX_USER_RESP_TBL
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
 USERNAME                        "LTRIM(RTRIM(:USERNAME))"    
,RESPONSIBILITY_KEY              "LTRIM(RTRIM(:RESPONSIBILITY_KEY))"
,END_DATE_FLAG                   "LTRIM(RTRIM(:END_DATE_FLAG))"
,ERROR_FLAG                      "LTRIM(RTRIM(:ERROR_FLAG))"
,ERROR_MESSAGE                   "LTRIM(RTRIM(:ERROR_MESSAGE))"
,CREATED_BY                       CONSTANT "-1"
,CREATION_DATE                    SYSDATE
,LAST_UPDATED_BY                  CONSTANT "-1"
,LAST_UPDATE_DATE                 SYSDATE

)

Sample Data File:

a) First Column: Employee Number
b) Second Column: Responsibility Key
c) Third Column: End Date Flag to End Date the Responsibility Assignment

432543|SYSADMIN|Y

654757|SYSTEM_ADMINISTRATOR|

Script to Assign/Deassign Responsibilities to User:

DECLARE
  CURSOR c_resp_cursor IS
    SELECT gfui.username user_name,
           fu.user_id,
           (SELECT fa.application_short_name
              FROM fnd_application fa
             WHERE fr.application_id = fa.application_id) application_short_name,
           gfui.responsibility_key,
           fr.responsibility_id,
           fr.application_id,
           SYSDATE start_date,
           decode(end_date_flag, 'Y', SYSDATE, '') end_date,
           NULL error_flag,
           NULL error_msg
      FROM xxxx_user_resp_tbl gfui, fnd_responsibility fr, fnd_user fu
     WHERE nvl(gfui.error_flag, 'ERROR') = 'ERROR'
       AND gfui.responsibility_key = fr.responsibility_key(+)
       AND gfui.username = fu.user_name(+);
  --
  TYPE v_resp_type IS TABLE OF c_resp_cursor%ROWTYPE INDEX BY PLS_INTEGER;
  v_resp_tab v_resp_type;
  --
  v_row_proc NUMBER := 0;
  v_count    NUMBER := 0;
  --
BEGIN
  --
  v_resp_tab.DELETE;
  --
  OPEN c_resp_cursor;
  LOOP
    FETCH c_resp_cursor BULK COLLECT
      INTO v_resp_tab LIMIT 5000;
    EXIT WHEN v_resp_tab.COUNT = 0;
    --
    FOR i IN v_resp_tab.FIRST .. v_resp_tab.COUNT
    LOOP
      v_count := 0;
      v_row_proc := 0;
      v_resp_tab(i).error_flag := NULL;
      v_resp_tab(i).error_msg := NULL;
      BEGIN
        SELECT COUNT(*)
          INTO v_count
          FROM fnd_user_resp_groups_direct
         WHERE v_resp_tab(i).user_id = user_id
           AND v_resp_tab(i).responsibility_id = responsibility_id;
      EXCEPTION
        WHEN OTHERS THEN
          v_count := 0;
      END;
      --
      IF v_resp_tab(i).user_id IS NULL
      THEN
        --
        v_resp_tab(i).error_flag := 'ERROR';
        v_resp_tab(i).error_msg := 'User does not exists ' || v_resp_tab(i).user_name;
        --
      END IF;
      --
      IF v_resp_tab(i).responsibility_id IS NULL
      THEN
        --
        v_resp_tab(i).error_flag := 'ERROR';
        v_resp_tab(i).error_msg := v_resp_tab(i).error_msg ||
                                   ' Responsibility does not exists for the Responsibility Key : ' ||
                                   v_resp_tab(i).responsibility_key;
        --
      END IF;
      --
      IF v_count = 0
      THEN
        IF nvl(v_resp_tab(i).error_flag, 'SUCCESS') != 'ERROR'
        THEN
          --
          BEGIN
            --
            v_resp_tab(i).error_flag := 'SUCCESS';
            v_resp_tab(i).error_msg := NULL;
            --
            fnd_user_pkg.addresp(username       => v_resp_tab(i).user_name,
                                 resp_app       => v_resp_tab(i).application_short_name,
                                 resp_key       => v_resp_tab(i).responsibility_key,
                                 security_group => 'STANDARD',
                                 description    => NULL,
                                 start_date     => SYSDATE,
                                 end_date       => NULL);
          EXCEPTION
            WHEN OTHERS THEN
              v_resp_tab(i).error_flag := 'ERROR';
              v_resp_tab(i).error_msg := SQLCODE || '-' || SQLERRM ||
                                         'Issue while adding responsibility ' ||
                                         v_resp_tab(i).responsibility_key || ' for User ' ||
                                         v_resp_tab(i).user_name;
          END;
          --
        END IF;
      ELSE
        IF nvl(v_resp_tab(i).error_flag, 'SUCCESS') != 'ERROR'
        THEN
          --
          BEGIN
            fnd_user_resp_groups_api.update_assignment(user_id                       => v_resp_tab(i).user_id,
                                                       responsibility_id             => v_resp_tab(i).responsibility_id,
                                                       responsibility_application_id => v_resp_tab(i).application_id,
                                                       security_group_id             => 0,
                                                       start_date                    => v_resp_tab(i).start_date,
                                                       end_date                      => v_resp_tab(i).end_date,
                                                       description                   => v_resp_tab(i).responsibility_key);
          EXCEPTION
            WHEN OTHERS THEN
              v_resp_tab(i).error_flag := 'ERROR';
              v_resp_tab(i).error_msg := SQLCODE || '-' || SQLERRM ||
                                         'Issue while updating assignment ' ||
                                         v_resp_tab(i)
                                        .responsibility_key || ' for User ' ||
                                         v_resp_tab(i).user_name;
          END;
          --
        END IF;
        --
      END IF;
      v_row_proc := v_row_proc + 1;
      IF (MOD(v_row_proc, 100) = 0)
      THEN
        COMMIT;
      END IF;
      --
      UPDATE xxxx_user_resp_tbl
         SET error_flag    = nvl(v_resp_tab(i).error_flag, 'SUCCESS'),
             error_message = v_resp_tab(i).error_msg
       WHERE username = v_resp_tab(i).user_name;
      --
      COMMIT;
      --
    END LOOP;
    --
  END LOOP;
  --
  COMMIT;
  --
  CLOSE c_resp_cursor;
  --
EXCEPTION
  WHEN OTHERS THEN
    raise_application_error(-21001, SQLCODE || '-' || SQLERRM);
END;

/

Migration Steps:

<!--[if !supportLists]-->1.       1.Run the Script to create table ‘XXXX_USER_RESP_TBL.sql’

<!--[if !supportLists]-->2.      2.  <!--[endif]-->HR_USER_RESP_ASSIGN.dat (File to load data to table XXXX_USER_RESP_TBL)

Data File Name:- HR_USER_RESP_ASSIGN.dat   Control File:-  XXXX_USER_RESP_LOAD.ctl

Command: sqlldr apps/appspwd control= XXXX_USER_RESP_LOAD.ctl data= HR_USER_RESP_ASSIGN.dat log= HR_USER_RESP_ASSIGN_LOG.log bad= HR_USER_RESP_ASSIGN.bad

<!--[if !supportLists]-->3.       3.<!--[endif]-->After loading data into the table XXXX_USER_RESP_TBL, Run the insert script ‘XXXX_USER_RESP_LOAD.sql’ to assign/deassign responsibilities to user.