OKC_USER_BU_MAP

Details

Schema: FUSION
Object owner: OKC
Object type: VIEW

Columns

Name
USER_ID USERNAME USER_GUID PERSON_ID PARTY_ID RESOURCE_ID ROLE_ID ROLE_CODE ROLE_TYPE_CODE BU_ID BU_NAME

Name

USER_ID

USERNAME

USER_GUID

PERSON_ID

PARTY_ID

RESOURCE_ID

ROLE_ID

ROLE_CODE

ROLE_TYPE_CODE

BU_ID

BU_NAME

Query

SQL_Statement
select u.user_id, u.username, u.user_guid, u.person_id, p.party_id as party_id, rsg.resource_id as resource_id, rrr.role_id as role_id, rol.role_code, rol.role_type_code, bu.business_unit_id as bu_id, f.bu_name as bu_name from per_users u, hz_parties p, jtf_rs_group_members rsg , jtf_rs_role_relations rrr, JTF_RS_RESOURCE_ORG_BU bu, fun_all_business_units_v f, 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 = 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 rsg.group_id = bu.organization_id(+) and bu.business_unit_id = f.bu_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, rsg.resource_id as resource_id, rrr.role_id as role_id, rol.role_code, rol.role_type_code, bu.business_unit_id as bu_id, f.bu_name as bu_name from per_users u, JTF_RS_RESOURCE_PROFILES r, jtf_rs_group_members rsg , jtf_rs_role_relations rrr, JTF_RS_RESOURCE_ORG_BU bu, fun_all_business_units_v f , 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 rsg.group_id = bu.organization_id(+) and bu.business_unit_id = f.bu_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,

rsg.resource_id as resource_id,

rrr.role_id as role_id,

rol.role_code,

rol.role_type_code,

bu.business_unit_id as bu_id,

f.bu_name as bu_name

from per_users u, hz_parties p, jtf_rs_group_members rsg , jtf_rs_role_relations rrr, JTF_RS_RESOURCE_ORG_BU bu, fun_all_business_units_v f, 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 = 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 rsg.group_id = bu.organization_id(+)

and bu.business_unit_id = f.bu_id(+)

and rrr.role_id = rol.role_id(+)

UNION ALL