UMX/ Roles Security Queries

 -- Who all Users a particular Role/Responsibility Is Assigned

Select wlur.USER_NAME,

wfr.display_name,

wlur.ROLE_NAME,

wrh.super_name,

wfr.description,

wlur.ASSIGNMENT_REASON,

wrh.relationship_id,

wlur.START_DATE,

wlur.EXPIRATION_DATE,

wlur.EFFECTIVE_START_DATE,

wlur.EFFECTIVE_END_DATE

from APPS.WF_LOCAL_USER_ROLES wlur,

APPS.WF_ROLE_HIERARCHIES  wrh,

apps.UMX_ALL_ROLE_VL wfr

WHERE 1=1

AND wlur.role_name = wrh.sub_name (+)

and wfr.name =  wlur.role_name 

and wlur.USER_NAME = 'XXXXXX'  -- USER NAME

and sysdate between wlur.EFFECTIVE_START_DATE and wlur.EFFECTIVE_END_DATE

--and wfr.display_name like 'Receivables - Limited Access' ---- Responsibility Name

--and role_name ='FND_RESP|SQLGL|AMZ_GL_LOOK_CODE_MGR|STANDARD' -- Role Name

order by wlur.ROLE_NAME 

-- Responsibility and Role Name Join

SELECT --asn.LOOKUP_ASSIGNMENT_ID, 

uar.display_name "Responsbility Name",

uar.description "Responsiblity Description",

flv.MEANING "Role Category" ,

     fat.application_name,

     flv.description "Role Description",

asn.LOOKUP_TYPE CATEGORY_LOOKUP_TYPE, 

asn.LOOKUP_CODE CATEGORY_LOOKUP_CODE, 

asn.INSTANCE_PK1_VALUE WF_ROLE_NAME, 

uar.orig_system

FROM apps.fnd_lookup_assignments asn, 

apps.fnd_lookup_values_vl flv ,

apps.umx_all_role_vl uar,

apps.fnd_application fa,

apps.fnd_application_tl fat

WHERE flv.lookup_type = asn.LOOKUP_TYPE 

AND flv.lookup_code = asn.LOOKUP_CODE 

and uar.name = asn.INSTANCE_PK1_VALUE

and fa.application_short_name  = uar.owner_tag

and fa.application_id = fat.application_id

and fat.source_lang ='US'

AND flv.lookup_type = 'UMX_CATEGORY_LOOKUP' 

AND asn.obj_name = 'UMX_ACCESS_ROLE'

AND uar.status ='ACTIVE'

-- and asn.LOOKUP_CODE like '%AR%'

-- AND FA.APPLICATION_SHORT_NAME = 'SQLAP'

--and flv.MEANING = 'General Ledger Closer' ----- Role Description from Request Access Page

--and asn.INSTANCE_PK1_VALUE = 'FND_RESP|SQLAP|AMZ_AP_CN_ACCESS|STANDARD'

and uar.DISPLAY_NAME  = 'NZ - Receivables Inquiry' -- Responsibiity Name

--Assign a Security Admin Role to a User 

Begin

wf_local_synch.PropagateUserRole(

     p_user_name => 'XXXXXXX',

     p_role_name => 'UMX|SECURITY_ADMIN',

     p_start_date=> SYSDATE);

commit;

end;