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
--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