1. Tables and Views for HCM
  2. CMP_CWB_HRCHY_CF_DN_V

CMP_CWB_HRCHY_CF_DN_V

Details

  • Schema: FUSION

  • Object owner: CMP

  • Object type: VIEW

Columns

Name

TOP_MGR_PERSON_EVENT_ID

TOP_MGR_PERSON_ID

PERSON_ID

TOP_PLAN_ID

LEVEL1_PERSON_EVENT_ID

LEVEL1_MGR_PERSON_ID

LEVEL2_PERSON_EVENT_ID

LEVEL2_MGR_PERSON_ID

LEVEL3_PERSON_EVENT_ID

LEVEL3_MGR_PERSON_ID

LEVEL4_PERSON_EVENT_ID

LEVEL4_MGR_PERSON_ID

LEVEL5_PERSON_EVENT_ID

LEVEL5_MGR_PERSON_ID

LEVEL6_PERSON_EVENT_ID

LEVEL6_MGR_PERSON_ID

LEVEL7_PERSON_EVENT_ID

LEVEL7_MGR_PERSON_ID

LEVEL8_PERSON_EVENT_ID

LEVEL8_MGR_PERSON_ID

LEVEL9_PERSON_EVENT_ID

LEVEL9_MGR_PERSON_ID

LEVEL10_PERSON_EVENT_ID

LEVEL10_MGR_PERSON_ID

LEVEL11_PERSON_EVENT_ID

LEVEL11_MGR_PERSON_ID

LEVEL12_PERSON_EVENT_ID

LEVEL12_MGR_PERSON_ID

LEVEL13_PERSON_EVENT_ID

LEVEL13_MGR_PERSON_ID

LEVEL14_PERSON_EVENT_ID

LEVEL14_MGR_PERSON_ID

LEVEL15_PERSON_EVENT_ID

LEVEL15_MGR_PERSON_ID

Query

SQL_Statement

SELECT

TOP_MGR_PERSON_EVENT_ID,

TOP_MGR_PERSON_ID,

top_mgr_person_id person_id,

TOP_PLAN_ID,

nvl(MAX(DECODE(ldiff, 1,

mid_mgr_person_event_id)),top_person_event_id) level1_person_event_id,

nvl(MAX(DECODE(ldiff, 1, mid_mgr_person_id)), top_person_id)

level1_mgr_person_id,

nvl(MAX(DECODE(ldiff, 2,

mid_mgr_person_event_id)),top_person_event_id) level2_person_event_id,

nvl(MAX(DECODE(ldiff, 2, mid_mgr_person_id)),top_person_id)

level2_mgr_person_id,

nvl(MAX(DECODE(ldiff, 3,

mid_mgr_person_event_id)),top_person_event_id) level3_person_event_id,

nvl(MAX(DECODE(ldiff, 3, mid_mgr_person_id)),top_person_id)

level3_mgr_person_id,

nvl(MAX(DECODE(ldiff, 4,

mid_mgr_person_event_id)),top_person_event_id) level4_person_event_id,

nvl(MAX(DECODE(ldiff, 4, mid_mgr_person_id)),top_person_id)

level4_mgr_person_id,

nvl(MAX(DECODE(ldiff, 5,

mid_mgr_person_event_id)),top_person_event_id) level5_person_event_id,

nvl(MAX(DECODE(ldiff, 5, mid_mgr_person_id)),top_person_id)

level5_mgr_person_id,

nvl(MAX(DECODE(ldiff, 6,

mid_mgr_person_event_id)),top_person_event_id) level6_person_event_id,

nvl(MAX(DECODE(ldiff, 6, mid_mgr_person_id)),top_person_id)

level6_mgr_person_id,

nvl(MAX(DECODE(ldiff, 7,

mid_mgr_person_event_id)),top_person_event_id) level7_person_event_id,

nvl(MAX(DECODE(ldiff, 7, mid_mgr_person_id)),top_person_id)

level7_mgr_person_id,

nvl(MAX(DECODE(ldiff, 8,

mid_mgr_person_event_id)),top_person_event_id) level8_person_event_id,

nvl(MAX(DECODE(ldiff, 8, mid_mgr_person_id)),top_person_id)

level8_mgr_person_id,

nvl(MAX(DECODE(ldiff, 9,

mid_mgr_person_event_id)),top_person_event_id) level9_person_event_id,

nvl(MAX(DECODE(ldiff, 9, mid_mgr_person_id)),top_person_id)

level9_mgr_person_id,

nvl(MAX(DECODE(ldiff,10,

mid_mgr_person_event_id)),top_person_event_id) level10_person_event_id,

nvl(MAX(DECODE(ldiff,10, mid_mgr_person_id)),top_person_id)

level10_mgr_person_id,

nvl(MAX(DECODE(ldiff,11,

mid_mgr_person_event_id)),top_person_event_id) level11_person_event_id,

nvl(MAX(DECODE(ldiff,11, mid_mgr_person_id)),top_person_id)

level11_mgr_person_id,

nvl(MAX(DECODE(ldiff,12,

mid_mgr_person_event_id)),top_person_event_id) level12_person_event_id,

nvl(MAX(DECODE(ldiff,12, mid_mgr_person_id)),top_person_id)

level12_mgr_person_id,

nvl(MAX(DECODE(ldiff,13,

mid_mgr_person_event_id)),top_person_event_id) level13_person_event_id,

nvl(MAX(DECODE(ldiff,13, mid_mgr_person_id)),top_person_id)

level13_mgr_person_id,

nvl(MAX(DECODE(ldiff,14,

mid_mgr_person_event_id)),top_person_event_id) level14_person_event_id,

nvl(MAX(DECODE(ldiff,14, mid_mgr_person_id)),top_person_id)

level14_mgr_person_id,

nvl(MAX(DECODE(ldiff,15,

mid_mgr_person_event_id)),top_person_event_id) level15_person_event_id,

nvl(MAX(DECODE(ldiff,15, mid_mgr_person_id)),top_person_id)

level15_mgr_person_id

FROM

(SELECT

topmanager.mgr_person_event_id top_mgr_person_event_id,

topmanager.mgr_person_id top_mgr_person_id,

topmanager.emp_person_event_id top_person_event_id,

topmanager.emp_person_id top_person_id,

topmanager.lvl_num top_manager_level,

topmanager.hrchy_plan_id top_plan_id,

middlemanager.mgr_person_event_id mid_mgr_person_event_id,

middlemanager.mgr_person_id mid_mgr_person_id,

middlemanager.lvl_num middle_manager_level,

(topmanager.lvl_num - middlemanager.lvl_num) ldiff

FROM cmp_cwb_hrchy topmanager,

cmp_cwb_hrchy middlemanager

WHERE middlemanager.emp_person_event_id = topmanager.emp_person_event_id

AND topmanager.lvl_num > 0

AND middlemanager.lvl_num <= topmanager.lvl_num

AND middlemanager.lvl_num > 0

)

GROUP BY top_mgr_person_event_id, top_mgr_person_id,

top_person_event_id,top_person_id, top_plan_id