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;