Search This Blog

Wednesday, January 18, 2017

Oracle Apps R12 Adding Responsibilities to Users using API

DECLARE
--v_description VARCHAR2 (100) := 'SSHR User XX';
 v_app_short_name VARCHAR2 (100);
 v_resp_key VARCHAR2 (100);
 V_ERROR VARCHAR2(2000);
 V_COUNT NUMBER ;
 V_COUNT_ALL NUMBER := 0;
 CURSOR user_cur IS

 select a.application_short_name, r.responsibility_key
 from fnd_responsibility_vl r, fnd_application_vl a where
 r.application_id =a.application_id
 and R.responsibility_name = 'Employee Self Service xxComp'

 ;

 CURSOR get_emp is

 SELECT EMPLOYEE_ID PERSON_ID, USER_NAME
 FROM
 FND_USER FU
 WHERE
TRUNC(CREATION_DATE) = TRUNC(SYSDATE) --Created Users today
 AND END_DATE IS NULL
 AND EMPLOYEE_ID IS NOT NULL

 ;

user_rec user_cur%ROWTYPE;
get_emp_rec get_emp%ROWTYPE; 
BEGIN
OPEN get_emp;
loop
FETCH get_emp INTO get_emp_rec;
 EXIT WHEN get_emp%NOTFOUND;

 OPEN user_cur;
 LOOP
 FETCH user_cur INTO user_rec;
 EXIT WHEN user_cur%NOTFOUND;

 fnd_user_pkg.addresp(username => UPPER(get_emp_rec.user_name)
 ,resp_app => user_rec.application_short_name
 ,resp_key => user_rec.responsibility_key
 ,security_group => 'STANDARD'
 ,description => NULL
 ,start_date => SYSDATE
 ,end_date => null);
/*
 fnd_user_pkg.DelResp(username => UPPER(get_emp_rec.user_name)
,resp_app =>user_rec.application_short_name
,resp_key => user_rec.responsibility_key
,security_group => 'STANDARD'
);
 */
         
 END LOOP;
 CLOSE user_cur;
commit;


                --commit;
                V_COUNT_ALL := V_COUNT_ALL +1;
 
  END LOOP;
CLOSE get_emp;
DBMS_OUTPUT.PUT_LINE(V_COUNT_ALL);
EXCEPTION WHEN OTHERS THEN
V_ERROR := SQLERRM;

END;
/



Testing:


 Select b.user_name, c.responsibility_name, a.START_DATE, a.END_DATE
from fnd_user_resp_groups_direct a, fnd_user b, fnd_responsibility_tl c
where a.user_id = b.user_id
and a.responsibility_id = c.responsibility_id
and b.user_name LIKE 'TA%'
AND c.responsibility_name in ('Employee Self Service Fujairah')
order by 1

No comments:

Post a Comment