Standard API to assign and Revoke Role/Responsibilities

Standard API to assign and Revoke Role/Responsibilities to a Use

Using a Standard API to assign and Revoke Role/Responsibilities to a User.

col user_name for a15
col responsibility_name for a50
col user_guid for a35
select a.user_name,to_char(a.end_date,'DD-MON-YYYY')"User End",to_char(b.start_date,'DD-MON-YYYY')"Resp St",to_char(b.end_date,'DD-MON-YYYY')"Resp End",c.RESPONSIBILITY_NAME
from apps.fnd_user a,apps.fnd_USER_RESP_GROUPS b,apps.FND_RESPONSIBILITY_TL c
where a.USER_ID=b.USER_ID and b.RESPONSIBILITY_ID=c.RESPONSIBILITY_ID
and b.RESPONSIBILITY_APPLICATION_ID=c.APPLICATION_ID and a.user_name=upper('&fnd_user') order by 5;

set lines 200 pages 999;
col ROLE_NAME for a60;
select USER_NAME,ROLE_NAME,USER_START_DATE,USER_END_DATE,ROLE_START_DATE,ROLE_END_DATE,EFFECTIVE_END_DATE from apps.WF_LOCAL_USER_ROLES
where user_name = upper('&fnd_user');

select USER_NAME,ROLE_NAME,USER_START_DATE,USER_END_DATE,ROLE_START_DATE,ROLE_END_DATE,EFFECTIVE_END_DATE,END_DATE from apps.WF_USER_ROLE_ASSIGNMENTS
where user_name = upper('&fnd_user');

package to delete the responsibility:

SQL> Begin
fnd_user_pkg.delresp(
'&User_Name',
'&Res_Short_Name',
'&Responsibility_Key',
'&Security_Group');
commit;
End;


select * from fnd_security_groups
where security_group_key = 'STANDARD';

go to security user - > define -> enter the username
go to tools -> diagonise and enable the trace
and u can monitor waht is happining to that user when u are running anything backend to disable the user or delete the user.


############

1. Assign role to a user using an API

To assign role to a user using APIs, use the following API wf_local_synch.PropagateUserRole.
Example:

Begin
wf_local_synch.PropagateUserRole(
p_user_name => '&USER_NAME',
p_role_name => '&ROLE_KEY');
commit;
end;

2. Add a responsibility to a user using API fnd_user_resp_groups_api.Insert_Assignment

To add a responsibility to a user using and API, use the following API fnd_user_resp_groups_api.Insert_Assignment:

Example.

begin
fnd_user_resp_groups_api.Insert_Assignment (
user_id =>&USER_ID ,
responsibility_id => &RESP_ID,
responsibility_application_id =>$APPL_ID ,
start_date => &START_DATE,
end_date => &END_DATE,
description =>'Sample
example' );
commit;
end;

This shall raise an error if the responsibility is assigned to a user,
but if needed to update the responsibility assignment in case of responsibility existence,
use the following API:

begin
fnd_user_pkg.addresp(
'&User_Name',
'&Responsablity_Application_Short_Name',
'&Responsibility_Key',
'&Security_Group',
'&Description',
'&Start_Date',
'&End_Date' );
commit;
end;

3. Revoke a responsibility assignment to a user using fnd_user_pkg.delresp

To revoke a responsibility assignment to a user using an API, use fnd_user_pkg.delresp.
Example:

Begin
fnd_user_pkg.delresp(
'&User_Name',
'&Responsibility_application_short_name',
'&Responsibility_Key',
'&Security_Group');
commit;
End;

This simply end date the responsibility assignment to a user by the current system date.

4. Revoke an Indirect Responsibility

To revoke an indirect responsiblity (roles assigned using UMX) assignment to a user using APIs, use the following API Wf_local_synch.PropagateUserRole.
Example:

Begin
Wf_local_synch.PropagateUserRole(
p_user_name => '&USER_NAME',
p_role_name => '&ROLE_KEY',
p_start_date=>'&Start_Date',
p_expiration_date=>'&End_Date');
commit;
End;

End date the parent Role and it shall end date the remaining Roles.

Query to List All User Role in EBS as it shows in Application

The following query list all the Role and responsibilities as they appears in the Application security screen, it remove all the duplicates


--select ur.*, wr.*  --from   APPLSYS.wf_user_role_assignments ur, apps.WF_ROLES wr
select distinct UR.USER_NAME, WR.DISPLAY_NAME, WR.description
--, WR.STATUS,ROLE_ORIG_SYSTEM
from   APPLSYS.wf_user_role_assignments ur, apps.WF_ROLES wr
where  1=1
--AND ROLE_NAME = ASSIGNING_ROLE
AND ROLE_ORIG_SYSTEM in ( 'FND_RESP', 'UMX')
AND wr.name=ur.role_name
--AND ur.user_name like 'USER%';

Oracle R12 - Security Users, Roles, Responsibilities, Menu and Functions

Oracle R12 -- List of Users
select * from applsys.fnd_user;

Oracle R12 -- List of Roles
SELECT wf.NAME code,    wf.orig_system,  wf.display_name griddata,
    DECODE(SIGN(SYSDATE - NVL(wf.start_date,SYSDATE - 1)),1,DECODE(SIGN(NVL(wf.expiration_date,SYSDATE + 1) - SYSDATE),1,'ACTIVE', 'INACTIVE'), -1,'INACTIVE', 'ACTIVE') status,
    cat.category_lookup_code category_code,
    DECODE(umx_access_roles_pvt.Hierarchyenabled(wf.orig_system),'Y','AddChildRoleAction', '') add_switcher,
    fa.application_name,
    wf.owner_tag,
    wf.partition_id
  FROM wf_all_role_lov_vl wf,  umx_role_categories_v cat,  fnd_application_vl fa
  WHERE wf.NAME      = cat.wf_role_name (+)
  AND wf.owner_tag   = fa.application_short_name (+)
  AND ( (orig_system  = '')
  OR (orig_system     = 'UMX'))--'UMX') )
  AND ( partition_id IN (2,13))
order by 3;

Oracle R12 -- List of Responsibilities
SELECT wf.NAME code,  wf.orig_system,   wf.display_name griddata,
    DECODE(SIGN(SYSDATE - NVL(wf.start_date,SYSDATE - 1)),1,DECODE(SIGN(NVL(wf.expiration_date,SYSDATE + 1) - SYSDATE),1,'ACTIVE', 'INACTIVE'), -1,'INACTIVE', 'ACTIVE') status,
    cat.category_lookup_code category_code,
    DECODE(umx_access_roles_pvt.Hierarchyenabled(wf.orig_system),'Y','AddChildRoleAction', '') add_switcher,
    fa.application_name,
    wf.owner_tag,
    wf.partition_id
  FROM wf_all_role_lov_vl wf,  umx_role_categories_v cat,  fnd_application_vl fa
  WHERE wf.NAME      = cat.wf_role_name (+)
  AND wf.owner_tag   = fa.application_short_name (+)
AND ( (orig_system  = 'FND_RESP')
OR (orig_system     = ''))--'UMX') )
AND ( partition_id IN (2,13))
order by 3;

Oracle R12 -- List of Menus
SELECT menu_id, menu_name, user_menu_name,  description, last_update_date, type
  FROM apps.fnd_menus_vl
  WHERE (type IS NULL)
  OR (type    <>'SECURITY')
ORDER BY USER_MENU_NAME ASC;


Oracle R12 -- List of --Functions/Entitlements
SELECT FunctionEO.function_id,
  FunctionEO.function_name,
  FunctionEO.user_function_name,
  FunctionEO.type,
  FunctionEO.description,
  FunctionEO.creation_date,
  FunctionEO.last_update_date,
  FunctionEO.web_host_name,
  FunctionEO.web_agent_name,
  FunctionEO.web_html_call,
  FunctionEO.web_encrypt_parameters,
  FunctionEO.web_secured,
  FunctionEO.web_icon,
  FunctionEO.application_id,
  FunctionEO.form_id,
  FunctionEO.parameters,
  FunctionEO.object_id,
  FunctionEO.region_application_id,
  FunctionEO.region_code,
  FunctionEO.maintenance_mode_support,
  FunctionEO.context_dependence,
  FunctionEO.jrad_ref_path,
  a.application_name,
  f.user_form_name,
  o.display_name,
  a2.application_name AS RegionApplication
FROM apps.fnd_form_functions_vl FunctionEO,
  apps.fnd_application_vl a, apps.fnd_application_vl a2,
  apps.fnd_form_vl f,  apps.fnd_objects_vl o
WHERE 1=1
AND FunctionEO.application_id        = a.application_id(+)
AND FunctionEO.region_application_id = a2.application_id(+)
AND FunctionEO.form_id               = f.form_id(+)
AND FunctionEO.object_id             = o.object_id(+);

Oracle R12 -- List of --Users to Roles
select * from   APPLSYS.wf_user_role_assignments
where  1=1
AND ROLE_NAME = ASSIGNING_ROLE
AND ROLE_ORIG_SYSTEM = 'UMX'

Oracle R12 -- List of --Users to Responsibilities
select * from   APPLSYS.wf_user_role_assignments
where  1=1
AND ROLE_NAME = ASSIGNING_ROLE
AND ROLE_ORIG_SYSTEM = 'FND_RESP';

Oracle R12 -- List of --Roles to Responsibilities


Oracle R12 -- List of --Responsibilities to Menus
SELECT * FROM APPLSYS.FND_RESPONSIBILITY;

Oracle R12 -- List of --Menus to Functions
select * from apps.FND_MENU_ENTRIES_VL ;

R12 Role, Responsibilities and Assignments Queries

select pu.username, prdt.role_name, prd.role_common_name, pur.active_flag

from apps.per_users pu, apps.per_user_roles pur, apps.per_roles_dn prd, apps.per_roles_dn_tl prdt

where pu.user_id = pur.user_id

and pur.role_id = prd.role_id

and prd.role_id = prdt.role_id

and prdt.language = 'US'

and pur.role_guid = prd.role_guid

and pu.username =  'USER_NAME'

group by pu.username, pur.active_flag, prd.role_common_name, prdt.role_name;

select * from apps.wf_local_roles  where name like 'UMX%EBS_WF%' order by 1;;

select * from apps.wf_local_user_roles where role_name like 'UMX%EBS_WF%'order by 1;

select * from apps.wf_user_role_assignments where user_name  ='USERNAME' order by 1; --'role_name like 'UMX%EBS_WF%' order by 1;

select * from apps.wf_user_role_assignments where assigning_role  like 'UMX%EBS%FIN%' order by 1;

The following query I wrote in an effort to list all the responsibilities that are assigned to the Role I create under UMX. I could not find any simpler query that would define relationship directly

select distinct role_name, assigning_role from apps.wf_user_role_assignments where assigning_role  like 'UMX%' order by 1;

The following Query also works better

select WRH.SUPER_NAME, WRH.SUB_NAME,

--apps.FND_LOAD_UTIL.Owner_Name(WRH.LAST_UPDATED_BY) OWNER,

to_char(WRH.LAST_UPDATE_DATE, 'YYYY/MM/DD') LAST_UPDATE_DATE,

WRH.ENABLED_FLAG

from apps.WF_ROLE_HIERARCHIES WRH, apps.WF_ROLES WR

where WRH.SUB_NAME = WR.NAME

--and SUB_NAME like 'UMX%WF%'

and WR.ORIG_SYSTEM in ('FND_RESP', 'UMX')

connect by wrh.super_name = wrh.sub_name

--start with WRH.SUB_NAME = 'UMX|XXX AP INVOICE ENTRY';

start with WRH.SUB_NAME = 'UMX|XXX WF ADMIN';

select * from  apps.FND_RESPONSIBILITY_VL where responsibility_key like '%WF%';

select *  from  apps.FND_USER_RESP_GROUPS;

select * from apps.WF_ROLES where name like '%' order by orig_system_id;

select * from apps.WF_ROLES where name like 'UMX%XXX_WF%' order by orig_system_id;

select * from  apps.FND_RESPONSIBILITY;

select * from apps.umx_role_assignments_v where role_name like 'UMX%';

select * from apps.umx_current_roles_v;

Query to check Modules Licenses

SELECT fat.application_name
      ,fa.application_id
      ,fpi.patch_level
      ,decode(fpi.STATUS,'I','Licensed', 'N','Not Licensed','S','Shared','Undetermined') STATUS
  FROM apps.fnd_product_installations fpi
      ,apps.fnd_application fa
      ,apps.fnd_application_tl fat
 WHERE fpi.application_id = fa.application_id
   AND fat.application_id = fa.application_id --AND fpi.STATUS ='I'
   AND fat.LANGUAGE = 'US';