Friday, July 17, 2020

How to delete responsibility assignment of a User (FND_USER_PKG.DELRESP)?











Tested: R12.2.4

API: FND_USER_PKG.DELRESP

Script:

CREATE OR REPLACE PROCEDURE xxsh_delete_resp_assignment (piv_user_name IN VARCHAR2, piv_resp_name IN VARCHAR2, pov_status OUT VARCHAR2, pov_error_dtls OUT VARCHAR2 ) AS lv_application_name VARCHAR2 (100) := NULL; lv_responsibility_key VARCHAR2 (100) := NULL; lv_security_group VARCHAR2 (100) := NULL; ld_dummy_date DATE; ln_count NUMBER; le_stop_program EXCEPTION; BEGIN -- Whether user exists BEGIN SELECT end_date INTO ld_dummy_date FROM fnd_user WHERE user_name = piv_user_name; IF ld_dummy_date < SYSDATE THEN pov_status := 'ERROR'; pov_error_dtls := 'Username already end dated'; RAISE le_stop_program; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN pov_status := 'ERROR'; pov_error_dtls := 'Username not found'; RAISE le_stop_program; END; -- Whether responsibility iexists or not s already assigned to user BEGIN SELECT fa.application_short_name, fr.responsibility_key, frg.security_group_key INTO lv_application_name, lv_responsibility_key, lv_security_group FROM fnd_responsibility fr, fnd_application fa, fnd_security_groups frg, fnd_responsibility_tl frt WHERE fr.application_id = fa.application_id AND fr.data_group_id = frg.security_group_id AND fr.responsibility_id = frt.responsibility_id AND frt.LANGUAGE = USERENV ('LANG') AND frt.responsibility_name = piv_resp_name; EXCEPTION WHEN NO_DATA_FOUND THEN pov_status := 'ERROR'; pov_error_dtls := 'REsponsibility does not exists'; RAISE le_stop_program; END; -- Whether responsibility iexists or not s already assigned to user BEGIN SELECT a.end_date INTO ld_dummy_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 c.language = USERENV('LANG') AND user_name = piv_user_name AND responsibility_name = piv_resp_name; IF ld_dummy_date < SYSDATE THEN pov_status := 'ERROR'; pov_error_dtls := 'Responsibility Assignment Already ended'; RAISE le_stop_program; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN pov_status := 'ERROR'; pov_error_dtls := 'REsponsibility is not assigned to User'; RAISE le_stop_program; END; -- Call the API to end date responsibility assignment FND_USER_PKG.DELRESP (username => piv_user_name, resp_app => lv_application_name, resp_key => lv_responsibility_key, security_group => lv_security_group ); COMMIT; DBMS_OUTPUT.put_line ( 'Responsiblity ' || piv_resp_name || ' is removed from the user ' || piv_user_name || ' Successfully' ); EXCEPTION WHEN le_stop_program THEN DBMS_OUTPUT.put_line ('Custom Error: '||pov_error_dtls); WHEN OTHERS THEN DBMS_OUTPUT.put_line ('Unhandled Error: '||SQLERRM); END xxsh_delete_resp_assignment; / SHOW ERRORS;
Execution Script:
SET SERVEROUTPUT ON; DECLARE lv_status VARCHAR2(200); lv_error_message VARCHAR2(2000); BEGIN xxsh_delete_resp_assignment('SYSADMIN', 'CRM ETF Administration', lv_status, lv_error_message ); END;

1 Responses to “How to delete responsibility assignment of a User (FND_USER_PKG.DELRESP)?”

Dominick said...
February 15, 2021 at 7:05 PM

best


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.