Monday, May 9, 2011

Single responsibility assign to bulk of users

How to assign a responsiblity to bulk users

Declare
  v_responsibility_id fnd_responsibility.responsibility_id%type;
  v_application_id    fnd_responsibility.application_id%type;
  v_resp_key  fnd_responsibility.responsibility_key%type;
  Cursor c_fnd_Cur Is
         Select a.user_id
         From fnd_user a
             ,per_all_people_f b
             ,per_all_assignments_f c
         Where a.employee_id = b.person_id
         And sysdate between b.effective_start_date and b.effective_end_date
         And sysdate between c.effective_start_date and c.effective_end_date;
  Type v_fnd_rec Is Table Of c_fnd_Cur%Rowtype;
  v_fnd_table v_fnd_rec;
  v_found               boolean := false;
Begin
  Open c_fnd_Cur;
  Fetch c_fnd_Cur Bulk Collect Into v_fnd_table;
  Close c_fnd_Cur;
  For idx In v_fnd_table.first..v_fnd_table.last
  Loop
     -- get responsiblity details
     v_resp_key := 'XXHR_EMPLOYEE_SELF_SERVICE';
     fnd_oid_subscriptions.get_resp_app_id(p_resp_key => v_resp_key
                                         ,x_responsibility_id=> v_responsibility_id
                                         ,x_application_id=> v_application_id      );
     -- verify assignments existing with user or not
     v_found := fnd_user_resp_groups_api.assignment_exists( user_id => v_fnd_table(idx).user_id
                                                          , responsibility_id   => v_responsibility_id
                                                          , responsibility_application_id => v_application_id
                                                          , security_group_id             => null);
     if (not v_found)  then
            fnd_user_resp_groups_api.insert_assignment( user_id  => v_fnd_table(idx).user_id
                                                      , responsibility_id => v_responsibility_id
                                                      , responsibility_application_id => v_application_id
                                                      , security_group_id  => null
                                                      , start_date  => sysdate
                                                      , end_date    => null
                                                      , description   => 'Employee Self Service'    );
     end if;
  End Loop;
  commit;
End;

No comments: