Saturday, October 18, 2014

API to remove responsibilities from a user


DECLARE

 

v_user_name VARCHAR2 (100) := 'SHAHANI';

v_responsibility_name VARCHAR2 (100) := 'US Super HRMS Manager';

v_application_name VARCHAR2 (100) := NULL;

v_responsibility_key VARCHAR2 (100) := NULL;

v_security_group VARCHAR2 (100) := NULL;

v_description VARCHAR2 (100) := NULL;




BEGIN
 
 

SELECT fa.application_short_name,

fr.responsibility_key,

frg.security_group_key,

frt.description,

frt.responsibility_name

INTO v_application_name,

v_responsibility_key,

v_security_group,

v_description,



V_responsibility_name
 
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 = v_responsibility_name;

fnd_user_pkg.delresp (username => v_user_name,

resp_app => v_application_name,

resp_key => v_responsibility_key,

security_group => v_security_group

);

COMMIT;

  
DBMS_OUTPUT.put_line ( 'Responsiblity '



|| v_responsibility_name
 
|| ' is removed from the user '



|| v_user_name
 
|| ' Successfully'

);




EXCEPTION
 
 

WHEN OTHERS

THEN



DBMS_OUTPUT.put_line
 
( 'Error encountered while deleting responsibilty from the user and the error is '

|| SQLERRM

);




END;
 
 


 
SELECT count(1)

FROM fnd_user_resp_groups furg,

fnd_user fu,



fnd_responsibility_tl frt
 
WHERE furg.user_id = fu.user_id

AND furg.responsibility_id = frt.responsibility_id

AND fu.user_name = 'SHAHANI'

AND frt.responsibility_name = 'US Super HRMS Manager'