OKC_USER_RESOURCE_MAP

Details

Schema: FUSION
Object owner: OKC
Object type: VIEW

Columns

Name
USER_ID USERNAME USER_GUID PERSON_ID PARTY_ID RESOURCE_ID RESOURCE_ORG_ID ROLE_ID ROLE_CODE ROLE_TYPE_CODE RESOURCE_ORG_NAME

Name

USER_ID

USERNAME

USER_GUID

PERSON_ID

PARTY_ID

RESOURCE_ID

RESOURCE_ORG_ID

ROLE_ID

ROLE_CODE

ROLE_TYPE_CODE

RESOURCE_ORG_NAME

Query

SQL_Statement
select u.user_id, u.username, u.user_guid, u.person_id, p.party_id as party_id, r.party_id as resource_id, rsg.group_id as resource_org_id, rrr.role_id as role_id, rol.role_code as role_code, rol.role_type_code as role_type_code, h.name as resource_org_name from per_users u, hz_parties p, JTF_RS_RESOURCE_PROFILES r, jtf_rs_group_members rsg , jtf_rs_role_relations rrr, hr_organization_units h , jtf_rs_roles_b rol where u.user_guid = p.user_guid and u.person_id is not null and u.active_flag = 'Y' and trunc(sysdate) between trunc(u.start_date) and nvl(trunc(u.end_date), trunc(sysdate) + 1) and p.party_id = r.party_id(+) and trunc(sysdate) between trunc(r.start_date_active(+)) and NVL(trunc(r.end_date_active(+)), trunc(sysdate) +1) and p.party_id = rsg.resource_id(+) and rsg.delete_flag(+) = 'N' and rsg.group_member_id = rrr.role_resource_id(+) and rrr.role_resource_type(+) = 'RS_GROUP_MEMBER' and trunc(sysdate) between trunc(rrr.start_date_active(+)) and NVL(trunc(rrr.end_date_active(+)), trunc(sysdate) +1) and h.organization_id(+) = rsg.group_id and rrr.role_id = rol.role_id(+) UNION ALL select u.user_id, u.username, u.user_guid, null as person_id, u.party_id, r.party_id as resource_id, rsg.group_id as resource_org_id, rrr.role_id as role_id, rol.role_code as role_code, rol.role_type_code as role_type_code, h.name as resource_org_name from per_users u, JTF_RS_RESOURCE_PROFILES r, jtf_rs_group_members rsg , jtf_rs_role_relations rrr, hr_organization_units h, jtf_rs_roles_b rol where u.party_id = r.party_id(+) and u.party_id is not null and u.active_flag = 'Y' and trunc(sysdate) between trunc(u.start_date) and nvl(trunc(u.end_date), trunc(sysdate) + 1) and u.party_id = rsg.resource_id(+) and trunc(sysdate) between trunc(r.start_date_active(+)) and NVL(trunc(r.end_date_active(+)), trunc(sysdate) +1) and rsg.delete_flag(+) = 'N' and rsg.group_member_id = rrr.role_resource_id(+) and rrr.role_resource_type(+) = 'RS_GROUP_MEMBER' and trunc(sysdate) between trunc(rrr.start_date_active(+)) and NVL(trunc(rrr.end_date_active(+)), trunc(sysdate) +1) and h.organization_id(+) = rsg.group_id and rrr.role_id = rol.role_id(+)

SQL_Statement

select

u.user_id,

u.username,

u.user_guid,

u.person_id,

p.party_id as party_id,

r.party_id as resource_id,

rsg.group_id as resource_org_id,

rrr.role_id as role_id,

rol.role_code as role_code,

rol.role_type_code as role_type_code,

h.name as resource_org_name

from per_users u, hz_parties p, JTF_RS_RESOURCE_PROFILES r, jtf_rs_group_members rsg , jtf_rs_role_relations rrr, hr_organization_units h , jtf_rs_roles_b rol

where u.user_guid = p.user_guid

and u.person_id is not null

and u.active_flag = 'Y'

and trunc(sysdate) between trunc(u.start_date) and nvl(trunc(u.end_date), trunc(sysdate) + 1)

and p.party_id = r.party_id(+)

and trunc(sysdate) between trunc(r.start_date_active(+)) and NVL(trunc(r.end_date_active(+)), trunc(sysdate) +1)

and p.party_id = rsg.resource_id(+)

and rsg.delete_flag(+) = 'N'

and rsg.group_member_id = rrr.role_resource_id(+)

and rrr.role_resource_type(+) = 'RS_GROUP_MEMBER'

and trunc(sysdate) between trunc(rrr.start_date_active(+)) and NVL(trunc(rrr.end_date_active(+)), trunc(sysdate) +1)

and h.organization_id(+) = rsg.group_id

and rrr.role_id = rol.role_id(+)

UNION ALL