Below is the query.
Please not Responsibilities cannot be deleted in Oracle Apps from a User. It can only be Disabled or End Dated.
BEGIN
FOR I
IN (SELECT fu.user_id,
fu.user_name,
furg.RESPONSIBILITY_APPLICATION_ID,
frv.RESPONSIBILITY_NAME,
frv.RESPONSIBILITY_KEY,
fa.application_short_name,
frg.security_group_key
FROM FND_USER fu,
FND_USER_RESP_GROUPS furg,
FND_RESPONSIBILITY_VL frv,
fnd_application fa,
fnd_security_groups frg
WHERE fu.user_id = furg.user_id
AND furg.responsibility_id = frv.responsibility_id
AND frv.application_id = fa.application_id
AND frv.data_group_id = frg.security_group_id
AND fu.user_name = 'ABC'
AND frv.RESPONSIBILITY_NAME IN ('Receivables Support'))
LOOP
BEGIN
DBMS_OUTPUT.put_line (
i.user_name
|| i.application_short_name
|| i.responsibility_key
|| i.security_group_key);
fnd_user_pkg.delresp (username => i.user_name,
resp_app => i.application_short_name,
resp_key => i.responsibility_key,
security_group => i.security_group_key);
DBMS_OUTPUT.put_line (
i.RESPONSIBILITY_NAME || ' End Dated Successfully ');
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Error encountered while deleting responsibilty from the user and the error is '
|| SQLERRM);
END;
/
Please not Responsibilities cannot be deleted in Oracle Apps from a User. It can only be Disabled or End Dated.
BEGIN
FOR I
IN (SELECT fu.user_id,
fu.user_name,
furg.RESPONSIBILITY_APPLICATION_ID,
frv.RESPONSIBILITY_NAME,
frv.RESPONSIBILITY_KEY,
fa.application_short_name,
frg.security_group_key
FROM FND_USER fu,
FND_USER_RESP_GROUPS furg,
FND_RESPONSIBILITY_VL frv,
fnd_application fa,
fnd_security_groups frg
WHERE fu.user_id = furg.user_id
AND furg.responsibility_id = frv.responsibility_id
AND frv.application_id = fa.application_id
AND frv.data_group_id = frg.security_group_id
AND fu.user_name = 'ABC'
AND frv.RESPONSIBILITY_NAME IN ('Receivables Support'))
LOOP
BEGIN
DBMS_OUTPUT.put_line (
i.user_name
|| i.application_short_name
|| i.responsibility_key
|| i.security_group_key);
fnd_user_pkg.delresp (username => i.user_name,
resp_app => i.application_short_name,
resp_key => i.responsibility_key,
security_group => i.security_group_key);
DBMS_OUTPUT.put_line (
i.RESPONSIBILITY_NAME || ' End Dated Successfully ');
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Error encountered while deleting responsibilty from the user and the error is '
|| SQLERRM);
END;
/
No comments:
Post a Comment