PER_TOP_DOWN_POS_HRCHY

Details

Schema: FUSION
Object owner: PER
Object type: VIEW

Columns

Name
POSITION_CODE_OR_ID_INDENTED BUSINESS_UNIT POSITION_ID_INDENTED BUSINESS_GROUP_ID NODE_LEVEL EFFECTIVE_START_DATE EFFECTIVE_END_DATE POSITION_CODE POSITION_CODE_UPPER POSITION_ID TOP_POSITION_ID ORDER1 IS_CYCLE

Name

POSITION_CODE_OR_ID_INDENTED

BUSINESS_UNIT

POSITION_ID_INDENTED

BUSINESS_GROUP_ID

NODE_LEVEL

EFFECTIVE_START_DATE

EFFECTIVE_END_DATE

POSITION_CODE

POSITION_CODE_UPPER

POSITION_ID

TOP_POSITION_ID

ORDER1

IS_CYCLE

Query

SQL_Statement
WITH level1(ancestor_position_id,position_id,effective_start_date,effective_end_date,node_level,business_group_id) AS ( SELECT /+ INDEX(p HR_ALL_POSITIONS_F_N7)/ hp.parent_position_id pos_id_start, hp.parent_position_id,to_date(null),to_date(null) ,1,hp.business_group_id from fusion.PER_POSITION_HIERARCHY_F hp, fusion.HR_ALL_POSITIONS_F p where nvl(to_date(sys_context('apex_ctx','p2'),'DD-MON-YYYY'),sysdate) between hp.effective_start_date and hp.effective_end_date and p.position_id=hp.parent_position_id AND nvl(to_date(sys_context('apex_ctx','p2'),'DD-MON-YYYY'),sysdate) between p.effective_start_date and p.effective_end_date AND upper(p.position_code) like upper(nvl(sys_context('apex_ctx','p1'),'%$#@'))\|\| '%' and rownum = 1 UNION ALL SELECT p.ancestor_position_id,c.position_id,greatest(c.effective_start_date,nvl(p.effective_start_date,c.effective_start_date)) ,least(c.effective_end_date, nvl(p.effective_end_date,c.effective_end_date)), node_level+1 /provides recursion/,c.business_group_id FROM fusion.PER_POSITION_HIERARCHY_F c, level1 p WHERE p.position_id = c.parent_position_id AND p.business_group_id=c.business_group_id AND nvl(to_date(sys_context('apex_ctx','p2'),'DD-MON-YYYY'),sysdate) between c.effective_start_date and c.effective_end_date ) SEARCH DEPTH FIRST BY position_id desc SET order1 cycle position_id set is_cycle to '1' default '0' select /+ FIRST_ROWS(20) USE_NL(h p b)/ lpad('-',node_level2,'-')\|\| node_level\|\|'. '\|\| nvl(p.position_code,h.position_id\|\|' (ID)') position_code_or_id_indented, b.bu_name business_unit,lpad(' ',node_level2,' ')\|\| case when h.ancestor_position_id is not null then node_level\|\|'. '\|\| h.ancestor_position_id\|\|' - ' end \|\| case when h.ancestor_position_id is null and h.position_id is not null then node_level \|\| '. ' end \|\| h.position_id position_id_indented, h.business_group_id,h.node_level,h.effective_start_date,h.effective_end_date,p.position_code,upper(p.position_code) position_code_upper, h.position_id,h.ancestor_position_id top_position_id,h.order1,h.is_cycle from level1 h, hr_all_positions_f p, FUN_ALL_BUSINESS_UNITS_V b where h.position_id=p.position_id(+) and h.business_group_id=p.business_group_id(+) and nvl(to_date(sys_context('apex_ctx','p2'),'DD-MON-YYYY'),sysdate) between p.effective_start_date(+) and p.effective_end_date(+) and p.business_unit_id = b.bu_id and p.business_group_id = b.business_group_id order by order1

SQL_Statement

WITH level1(ancestor_position_id,position_id,effective_start_date,effective_end_date,node_level,business_group_id) AS

(

SELECT /*+ INDEX(p HR_ALL_POSITIONS_F_N7)*/ hp.parent_position_id pos_id_start, hp.parent_position_id,to_date(null),to_date(null)

,1,hp.business_group_id from fusion.PER_POSITION_HIERARCHY_F hp, fusion.HR_ALL_POSITIONS_F p

where nvl(to_date(sys_context('apex_ctx','p2'),'DD-MON-YYYY'),sysdate) between hp.effective_start_date and hp.effective_end_date

and p.position_id=hp.parent_position_id

AND nvl(to_date(sys_context('apex_ctx','p2'),'DD-MON-YYYY'),sysdate) between p.effective_start_date and p.effective_end_date

AND upper(p.position_code) like upper(nvl(sys_context('apex_ctx','p1'),'%$#@'))|| '%' and rownum = 1

UNION ALL

SELECT p.ancestor_position_id,c.position_id,greatest(c.effective_start_date,nvl(p.effective_start_date,c.effective_start_date))

,least(c.effective_end_date, nvl(p.effective_end_date,c.effective_end_date)), node_level+1 /*provides recursion*/,c.business_group_id

FROM fusion.PER_POSITION_HIERARCHY_F c, level1 p

WHERE p.position_id = c.parent_position_id

AND p.business_group_id=c.business_group_id

AND nvl(to_date(sys_context('apex_ctx','p2'),'DD-MON-YYYY'),sysdate) between c.effective_start_date and c.effective_end_date

)

SEARCH DEPTH FIRST BY position_id desc SET order1

cycle position_id set is_cycle to '1' default '0'

select /*+ FIRST_ROWS(20) USE_NL(h p b)*/ lpad('-',node_level*2,'-')|| node_level||'. '|| nvl(p.position_code,h.position_id||' (ID)') position_code_or_id_indented,

b.bu_name business_unit,lpad(' ',node_level*2,' ')|| case when h.ancestor_position_id is not null then node_level||'. '|| h.ancestor_position_id||' - ' end ||

case when h.ancestor_position_id is null and h.position_id is not null then node_level || '. ' end || h.position_id position_id_indented,

h.business_group_id,h.node_level,h.effective_start_date,h.effective_end_date,p.position_code,upper(p.position_code) position_code_upper,

h.position_id,h.ancestor_position_id top_position_id,h.order1,h.is_cycle

from level1 h, hr_all_positions_f p, FUN_ALL_BUSINESS_UNITS_V b

where h.position_id=p.position_id(+) and h.business_group_id=p.business_group_id(+)

and nvl(to_date(sys_context('apex_ctx','p2'),'DD-MON-YYYY'),sysdate) between p.effective_start_date(+) and p.effective_end_date(+)

and p.business_unit_id = b.bu_id and p.business_group_id = b.business_group_id

order by order1