DECLARE
CURSOR User_end is
select user_name from fnd_user where user_name in (SELECT user_name FROM irm_empl_issue
WHERE 1 =1
AND attribute1 IS NULL
AND attribute2 IS NULL
AND attribute3 IS NULL
AND attribute4 IS NULL
AND color ='R');
CURSOR c1(C_USER_NAME VARCHAR2)
IS
SELECT fu.user_name,
fa.application_short_name,
frt.responsibility_name,
fr.responsibility_key,
fsg.security_group_key
FROM fnd_user_resp_groups_all ful,
fnd_user fu,
fnd_responsibility_tl frt,
fnd_responsibility fr,
fnd_security_groups fsg,
fnd_application fa
WHERE fu.user_id = ful.user_id
AND frt.responsibility_id = ful.responsibility_id
AND fr.responsibility_id = frt.responsibility_id
AND fsg.security_group_id = ful.security_group_id
AND fa.application_id = ful.responsibility_application_id
AND frt.language = 'US'
AND fu.user_name = C_USER_NAME;
BEGIN
For i in user_end loop
For j in c1 (i.USER_NAME)
loop
BEGIN
fnd_user_pkg.delresp (username => j.user_name,
resp_app => j.application_short_name,
resp_key => j.responsibility_key,
security_group => j.security_group_key);
COMMIT;
DBMS_OUTPUT.
put_line (
j.responsibility_name || ' has been End Dated Successfully !!!');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.
put_line (
'Inner Exception: '
|| ' - '
|| j.responsibility_key
|| ' - '
|| SQLERRM);
END;
END LOOP;
begin
fnd_user_pkg.updateuser
( x_user_name => i.user_name,
x_owner => NULL,
x_unencrypted_password => NULL,
x_start_date => NULL,
x_end_date => SYSDATE,
x_description =>'Duplicate User or User No Longer log on',
x_password_date => NULL,
x_password_lifespan_days => NULL,
x_employee_id => NULL,
x_email_address => NULL
);
DBMS_OUTPUT.put_line('User'||i.user_name||'End Dated');
UPDATE irm_empl_issue SET ATTRIBUTE5='User End dated' where user_name=i.user_name;
commit;
exception when others then
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
UPDATE irm_empl_issue SET ATTRIBUTE5='ERROR' where user_name=i.user_name;
commit;
end ;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
commit;
END;
/
CURSOR User_end is
select user_name from fnd_user where user_name in (SELECT user_name FROM irm_empl_issue
WHERE 1 =1
AND attribute1 IS NULL
AND attribute2 IS NULL
AND attribute3 IS NULL
AND attribute4 IS NULL
AND color ='R');
CURSOR c1(C_USER_NAME VARCHAR2)
IS
SELECT fu.user_name,
fa.application_short_name,
frt.responsibility_name,
fr.responsibility_key,
fsg.security_group_key
FROM fnd_user_resp_groups_all ful,
fnd_user fu,
fnd_responsibility_tl frt,
fnd_responsibility fr,
fnd_security_groups fsg,
fnd_application fa
WHERE fu.user_id = ful.user_id
AND frt.responsibility_id = ful.responsibility_id
AND fr.responsibility_id = frt.responsibility_id
AND fsg.security_group_id = ful.security_group_id
AND fa.application_id = ful.responsibility_application_id
AND frt.language = 'US'
AND fu.user_name = C_USER_NAME;
BEGIN
For i in user_end loop
For j in c1 (i.USER_NAME)
loop
BEGIN
fnd_user_pkg.delresp (username => j.user_name,
resp_app => j.application_short_name,
resp_key => j.responsibility_key,
security_group => j.security_group_key);
COMMIT;
DBMS_OUTPUT.
put_line (
j.responsibility_name || ' has been End Dated Successfully !!!');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.
put_line (
'Inner Exception: '
|| ' - '
|| j.responsibility_key
|| ' - '
|| SQLERRM);
END;
END LOOP;
begin
fnd_user_pkg.updateuser
( x_user_name => i.user_name,
x_owner => NULL,
x_unencrypted_password => NULL,
x_start_date => NULL,
x_end_date => SYSDATE,
x_description =>'Duplicate User or User No Longer log on',
x_password_date => NULL,
x_password_lifespan_days => NULL,
x_employee_id => NULL,
x_email_address => NULL
);
DBMS_OUTPUT.put_line('User'||i.user_name||'End Dated');
UPDATE irm_empl_issue SET ATTRIBUTE5='User End dated' where user_name=i.user_name;
commit;
exception when others then
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
UPDATE irm_empl_issue SET ATTRIBUTE5='ERROR' where user_name=i.user_name;
commit;
end ;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
commit;
END;
/
No comments:
Post a Comment