Script to Remove Connections of IFS User

Hi 

I would like to share the script to remove connections of IFS user. Using this script is very risky. It is not possible to restore the data in the personal profile. Please do not use it for  system users. If you want to add something new or make it better which is not in the script. Please share on the comments. To use it just input user name.

The operations of the script for IFS User;

• Revoking Roles
• Removing Document Groups
• Removing General Ledger Auth Classes
• Removing Client Profile Values
• Blocking Purchase Requisitioner
• Removing Finance User Groups
• Removing Sites
• Removing Project Teams
• Removing Purchase Requestion Approval Rules
• Removing Purchase Order Approval Rules

DECLARE
	username_   VARCHAR2(100) := upper('&User_Name');
	person_id_  VARCHAR2(100);
	info_       VARCHAR2(32000);
	objid_      VARCHAR2(32000) := '';
	objversion_ VARCHAR2(32000) := '';
	attr_       VARCHAR2(32000) := NULL;
BEGIN
	SELECT person_id INTO person_id_ FROM ifsapp.person_info_all WHERE user_id = username_;
	dbms_output.put_line(username_ || ' - ' || person_id_);
	dbms_output.put_line('');
	--PERMISSION SETS
	dbms_output.put_line('PERMISSION SETS');
	FOR rec_ IN (SELECT ur.identity,
						ur.role
				 FROM   fnd_user_role  ur,
						fnd_user       u,
						oracle_account t
				 WHERE  u.identity = ur.identity
						AND t.username = u.identity
						AND t.profile = 'DEFAULT'
						AND t.username NOT LIKE '%IFS%'
						AND u.identity = username_)
	LOOP
		security_sys.revoke_role(rec_.role, rec_.identity);
		dbms_output.put_line(rec_.role || ' role has been revoked!');
	END LOOP;
	dbms_output.put_line('');

	--DOCUMENT GROUPS
	dbms_output.put_line('DOCUMENT GROUPS');
	FOR rec_ IN (SELECT dgm.objid,
						dgm.objversion,
						dgm.group_id,
						document_group_api.get_group_description(dgm.group_id) desciption
				 FROM   document_group_members dgm
				 WHERE  dgm.person_id = person_id_)
	LOOP
		document_group_members_api.remove__(info_, rec_.objid, rec_.objversion, 'DO');
		dbms_output.put_line(rec_.group_id || ' - ' || rec_.desciption || ' document group member has been removed!');
	END LOOP;
	dbms_output.put_line('');

	--GENERAL LEDGER AUTH CLASSES
	dbms_output.put_line('GENERAL LEDGER AUTH CLASSES');
	FOR rec_ IN (SELECT glu.objid,
						glu.objversion,
						glu.company,
						glu.auth_class
				 FROM   gen_led_user glu
				 WHERE  glu.userid = username_)
	LOOP
		gen_led_user_api.remove__(info_, rec_.objid, rec_.objversion, 'DO');
		dbms_output.put_line(rec_.company || ' - ' || rec_.auth_class || ' auth class has been removed!');
	END LOOP;
	dbms_output.put_line('');

	--CLIENT PROFILE
	dbms_output.put_line('CLIENT PROFILE');
	FOR rec_ IN (SELECT cpv.objid,
						cpv.objversion
				 FROM   fndrr_client_profile_value cpv
				 WHERE  cpv.profile_id = (SELECT t.profile_id FROM fndrr_client_profile t WHERE t.owner = username_))
	LOOP
		fndrr_client_profile_value_api.remove__(info_, rec_.objid, rec_.objversion, 'DO');
	END LOOP;
	dbms_output.put_line('Client Profile has been cleaned!');
	dbms_output.put_line('');

	--PURCHASE REQUISITIONER (Blocks Only Active Users')
	dbms_output.put_line('PURCHASE REQUISITIONER');
	FOR rec_ IN (SELECT pr.objid,
						pr.objversion
				 FROM   purchase_requisitioner pr
				 WHERE  pr.objstate = 'Active'
						AND pr.requisitioner_code = person_id_)
	LOOP
		client_sys.clear_attr(attr_);
		purchase_requisitioner_api.set_blocked__(info_, rec_.objid, rec_.objversion, attr_, 'DO');
		dbms_output.put_line('Purchase Requisitioner has been blocked!');
	END LOOP;
	dbms_output.put_line('');

	--FINANCE USER GROUPS
	dbms_output.put_line('FINANCE USER GROUPS');
	FOR rec_ IN (SELECT ugmf.objid,
						ugmf.objversion,
						ugmf.company,
						ugmf.user_group
				 FROM   user_group_member_finance ugmf
				 WHERE  ugmf.userid = username_)
	LOOP
		user_group_member_finance_api.remove__(info_, rec_.objid, rec_.objversion, 'DO');
		dbms_output.put_line(rec_.company || ' - ' || rec_.user_group || ' finance user group has been removed!');
	END LOOP;
	dbms_output.put_line('');

	--SITES (NOT DEFAULT SITES)
	dbms_output.put_line('SITES');
	FOR rec_ IN (SELECT uas.objid,
						uas.objversion,
						uas.contract,
						site_api.get_description(uas.contract) site_desc
				 FROM   user_allowed_site uas
				 WHERE  uas.user_site_type_db != 'DEFAULT SITE'
						AND uas.userid = username_)
	LOOP
		user_allowed_site_api.remove__(info_, rec_.objid, rec_.objversion, 'DO');
		dbms_output.put_line(rec_.contract || ' - ' || rec_.site_desc || ' site has been removed!');
	END LOOP;
	dbms_output.put_line('');

	--SITES (DEFAULT SITE)
	dbms_output.put_line('DEFAULT SITE');
	FOR rec_ IN (SELECT uas.objid,
						uas.objversion,
						uas.contract,
						site_api.get_description(uas.contract) site_desc
				 FROM   user_allowed_site uas
				 WHERE  uas.userid = username_)
	LOOP
		user_allowed_site_api.remove__(info_, rec_.objid, rec_.objversion, 'DO');
		dbms_output.put_line(rec_.contract || ' - ' || rec_.site_desc || ' site has been removed!');
	END LOOP;
	dbms_output.put_line('');

	--PROJECT TEAM
	dbms_output.put_line('PROJECT TEAM');
	FOR rec_ IN (SELECT tm.objid,
						tm.objversion,
						tm.team_id,
						team_api.get_team_name(tm.team_id) team_name
				 FROM   team_member tm
				 WHERE  tm.employee_id = person_id_)
	LOOP
		team_member_api.remove__(info_, rec_.objid, rec_.objversion, 'DO');
		dbms_output.put_line(rec_.team_id || ' - ' || rec_.team_name || ' team has been removed!');
	END LOOP;
	dbms_output.put_line('');

	--PURCHASE REQUESTION APPROVAL RULES (Removes only Released Rules)
	dbms_output.put_line('PURCHASE REQUESTION APPROVAL RULES');
	FOR rec_ IN (SELECT rrr.approval_rule,
						pur_req_approval_rule_api.get_description(rrr.approval_rule) rule_desc,
						rrr.objid,
						rrr.objversion
				 FROM   ifsapp.req_rule_requisitioner rrr
				 WHERE  pur_req_approval_rule_api.get_objstate(rrr.approval_rule) = 'Released'
						AND rrr.requisitioner_code = person_id_)
	LOOP
		SELECT t.objid INTO objid_ FROM ifsapp.pur_req_approval_rule t WHERE t.approval_rule = rec_.approval_rule;
		SELECT t.objversion INTO objversion_ FROM ifsapp.pur_req_approval_rule t WHERE t.approval_rule = rec_.approval_rule;
		ifsapp.pur_req_approval_rule_api.change_to_plan__(info_, objid_, objversion_, attr_, 'DO');

		req_rule_requisitioner_api.remove__(info_, rec_.objid, rec_.objversion, 'DO');
		dbms_output.put_line(rec_.approval_rule || ' - ' || rec_.rule_desc || ' rule has been removed!');

		SELECT t.objid INTO objid_ FROM ifsapp.pur_req_approval_rule t WHERE t.approval_rule = rec_.approval_rule;
		SELECT t.objversion INTO objversion_ FROM ifsapp.pur_req_approval_rule t WHERE t.approval_rule = rec_.approval_rule;
		ifsapp.pur_req_approval_rule_api.release__(info_, objid_, objversion_, attr_, 'DO');
	END LOOP;
	dbms_output.put_line('');

	--PURCHASE ORDER APPROVAL RULES (Removes only Released Rules)
	dbms_output.put_line('PURCHASE ORDER APPROVAL RULES');
	FOR rec_ IN (SELECT porb.approval_rule,
						pur_approval_rule_api.get_description(porb.approval_rule) rule_desc,
						porb.objid,
						porb.objversion
				 FROM   pur_ord_rule_buyer porb
				 WHERE  pur_approval_rule_api.get_objstate(porb.approval_rule) = 'Released'
						AND porb.buyer_code = person_id_)
	LOOP
		SELECT t.objid INTO objid_ FROM pur_approval_rule t WHERE t.approval_rule = rec_.approval_rule;
		SELECT t.objversion INTO objversion_ FROM pur_approval_rule t WHERE t.approval_rule = rec_.approval_rule;
		pur_approval_rule_api.change_to_plan__(info_, objid_, objversion_, attr_, 'DO');

		pur_ord_rule_buyer_api.remove__(info_, rec_.objid, rec_.objversion, 'DO');
		dbms_output.put_line(rec_.approval_rule || ' - ' || rec_.rule_desc || ' rule has been removed!');

		SELECT t.objid INTO objid_ FROM pur_approval_rule t WHERE t.approval_rule = rec_.approval_rule;
		SELECT t.objversion INTO objversion_ FROM pur_approval_rule t WHERE t.approval_rule = rec_.approval_rule;
		pur_approval_rule_api.release__(info_, objid_, objversion_, attr_, 'DO');
	END LOOP;
END;