-- 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;