VIEW: APPS.HRBV_POSITION_DESCENDENTS

Source

View Text - Preformatted

SELECT str.business_group_id business_group_id ,str.parent_position_id parent_position_id ,str.subordinate_position_id position_id ,str.pos_structure_version_id pos_structure_version_id ,level pos_level FROM per_pos_structure_elements str WHERE str.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,str.business_group_id) START WITH str.parent_position_id IN ( SELECT DISTINCT pe.parent_position_id FROM per_pos_structure_elements pe WHERE pe.parent_position_id NOT IN ( SELECT po.subordinate_position_id FROM per_pos_structure_elements po WHERE po.pos_structure_version_id = str.pos_structure_version_id ) ) CONNECT BY PRIOR str.subordinate_position_id = str.parent_position_id AND PRIOR str.pos_structure_version_id = str.pos_structure_version_id AND PRIOR str.business_group_id = str.business_group_id WITH READ ONLY

View Text - HTML Formatted

SELECT STR.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, STR.PARENT_POSITION_ID PARENT_POSITION_ID
, STR.SUBORDINATE_POSITION_ID POSITION_ID
, STR.POS_STRUCTURE_VERSION_ID POS_STRUCTURE_VERSION_ID
, LEVEL POS_LEVEL
FROM PER_POS_STRUCTURE_ELEMENTS STR
WHERE STR.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, STR.BUSINESS_GROUP_ID) START WITH STR.PARENT_POSITION_ID IN ( SELECT DISTINCT PE.PARENT_POSITION_ID
FROM PER_POS_STRUCTURE_ELEMENTS PE
WHERE PE.PARENT_POSITION_ID NOT IN ( SELECT PO.SUBORDINATE_POSITION_ID
FROM PER_POS_STRUCTURE_ELEMENTS PO
WHERE PO.POS_STRUCTURE_VERSION_ID = STR.POS_STRUCTURE_VERSION_ID ) ) CONNECT BY PRIOR STR.SUBORDINATE_POSITION_ID = STR.PARENT_POSITION_ID
AND PRIOR STR.POS_STRUCTURE_VERSION_ID = STR.POS_STRUCTURE_VERSION_ID
AND PRIOR STR.BUSINESS_GROUP_ID = STR.BUSINESS_GROUP_ID WITH READ ONLY