Assign Responsibility using database

Requirement – To assign any responsibility to a fnd user using database. This is useful if we does not have sysadmin responsibility.

Solution  - Following API can be used to assign any responsibility to a user

  fnd_user_resp_groups_api.Insert_Assignment

                                    ( user_id                                   =>ln_user_id,

                                    responsibility_id                      => c1.responsibility_id,

                                    responsibility_application_id =>c1.responsibility_application_id,

                                    start_date                               =>SYSDATE,

                                    end_date                                 =>SYSDATE+1,

                                    description                              =>'null');

Example – Assign system administrator responsibility to user sandeep

DECLARE
  ln_user_id      NUMBER;

  CURSOR curr_add_resp
  is
    SELECT distinct responsibility_id,
      responsibility_application_id,
      security_group_id
    from fnd_user_resp_groups
    WHERE responsibility_id IN
      (SELECT responsibility_id
      from fnd_responsibility_vl
      WHERE responsibility_name IN ('System Administrator','Functional Administrator')
      )  
  ;
BEGIN  
              /* Fetch user id for fnd user */
              SELECT user_id
              INTO ln_user_id
              FROM fnd_user
              WHERE user_name='SKP';
             
              /*Responsibility Assignments */
              FOR c1 IN  curr_add_resp LOOP
                        BEGIN
                          fnd_user_resp_groups_api.Insert_Assignment
                                                            ( user_id =>ln_user_id,
                                                            responsibility_id => c1.responsibility_id,
                                                            responsibility_application_id =>c1.responsibility_application_id,
                                                            start_date =>sysdate,
                                                            end_date =>SYSDATE+100,
                                                            description =>'null');
                        EXCEPTION
                        WHEN OTHERS THEN
                          dbms_output.put_line('Error in Responsibility Assignment'||SQLERRM);
                        END;
               END LOOP;
EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('Error in Responsibility Assignment'||SQLERRM);
END;