1. Tables and Views for Sales and Fusion Service
  2. JTF_RS_REP_MANAGERS_V

JTF_RS_REP_MANAGERS_V

Details

  • Schema: FUSION

  • Object owner: HZ

  • Object type: VIEW

Columns

Name

RESOURCE_ID

PARENT_RESOURCE_ID

GROUP_ID

PARENT_ORG_ID

HIERARCHY_TYPE

DENORM_LEVEL

REPORTS_TO_FLAG

START_DATE_ACTIVE

END_DATE_ACTIVE

CHILD_ROLE_RELATE_ID

DENORM_MGR_ID

ROLE_RELATE_ID

ROLE_NAME

ROLE_ID

ROLE_LANGUAGE

NAME

ORGANIZATION_ID

EFFECTIVE_START_DATE

EFFECTIVE_END_DATE

ORGANIZATION_LANGUAGE

TREE_CODE

TREE_STRUCTURE_CODE

TREE_VERSION_ID

TREE_VERSION_NAME

TREE_STATUS

TREE_START_DATE

TREE_END_DATE

PARTY_ID

MANAGER_FIRST_NAME

MANAGER_LAST_NAME

MANAGER_NAME

MANAGER_EMAIL_ADDRESS

PARENT_ORG_NAME

Query

SQL_Statement

SELECT jrm.resource_id ,

jrm.parent_resource_id,

jrm.group_id ,

jrm.parent_org_id,

jrm.hierarchy_type ,

jrm.denorm_level ,

jrm.reports_to_flag ,

jrm.start_date_active ,

jrm.end_date_active ,

jrm.child_role_relate_id,

jrm.denorm_mgr_id,

jrr.role_relate_id,

jrt.role_name,

jrt.role_id,

jrt.language as role_language,

hout.name,

hout.organization_id,

hout.effective_start_date,

hout.effective_end_date,

hout.language as organization_language,

ftv.tree_code,

ftv.tree_structure_code,

ftv.tree_version_id,

ftv.tree_version_name,

ftv.status as tree_status,

ftv.effective_start_date as tree_start_date,

ftv.effective_end_date as tree_end_date,

hp.party_id,

hp.person_first_name as manager_first_name,

hp.person_last_name as manager_last_name,

hp.party_name as manager_name,

hp.EMAIL_ADDRESS as manager_email_address,

parentOrg.name as parent_org_name

FROM JTF_RS_REP_MANAGERS jrm,

JTF_RS_ROLE_RELATIONS jrr,

JTF_RS_ROLES_TL jrt,

HR_ORGANIZATION_UNITS_F_TL hout,

FND_TREE_VERSION_VL ftv,

HZ_PARTIES hp ,

HR_ORGANIZATION_UNITS_F_TL parentOrg

WHERE jrm.TREE_CODE = ftv.TREE_CODE

AND jrm.TREE_STRUCTURE_CODE = ftv.TREE_STRUCTURE_CODE

AND jrm.TREE_VERSION_ID = ftv.TREE_VERSION_ID

AND jrr.ROLE_RELATE_ID = jrm.CHILD_ROLE_RELATE_ID

AND jrr.delete_flag = 'N'

AND jrr.ROLE_ID = jrt.ROLE_ID

AND jrm.GROUP_ID = hout.ORGANIZATION_ID

AND jrm.PARENT_ORG_ID = parentOrg.ORGANIZATION_ID(+)

AND jrm.PARENT_RESOURCE_ID = hp.PARTY_ID

AND trunc(sysdate) BETWEEN hout.EFFECTIVE_START_DATE AND hout.EFFECTIVE_END_DATE

AND trunc(sysdate) BETWEEN parentOrg.EFFECTIVE_START_DATE(+) AND parentOrg.EFFECTIVE_END_DATE(+)

AND jrt.LANGUAGE = USERENV('LANG')

AND hout.LANGUAGE = USERENV('LANG')

AND parentOrg.LANGUAGE(+) = USERENV('LANG')

AND ftv.TREE_STRUCTURE_CODE in ('RESOURCE_ORG_TREE_STRUCTURE', 'PARTNER_ORG_TREE_STRUCTURE')

AND trunc(SYSDATE) between jrm.start_date_active AND jrm.end_date_active

AND hp.party_type = 'PERSON'

AND ftv.status = 'ACTIVE'

AND trunc(sysdate) between ftv.effective_start_date and ftv.effective_end_date