VIEW: APPS.HRBV_ORGANIZATION_DESCENDENTS

Source

View Text - Preformatted

SELECT str.business_group_id business_group_id ,str.organization_id_child organization_id ,str.org_structure_version_id org_structure_version_id ,str.organization_id_parent parent_organization_id ,level org_level FROM per_org_structure_elements str WHERE str.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,str.business_group_id) START WITH str.organization_id_parent IN ( SELECT DISTINCT pe.organization_id_parent FROM per_org_structure_elements pe WHERE pe.organization_id_parent NOT IN ( SELECT po.organization_id_child FROM per_org_structure_elements po WHERE po.org_structure_version_id = str.org_structure_version_id ) ) CONNECT BY PRIOR str.organization_id_child = str.organization_id_parent AND PRIOR str.org_structure_version_id = str.org_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.ORGANIZATION_ID_CHILD ORGANIZATION_ID
, STR.ORG_STRUCTURE_VERSION_ID ORG_STRUCTURE_VERSION_ID
, STR.ORGANIZATION_ID_PARENT PARENT_ORGANIZATION_ID
, LEVEL ORG_LEVEL
FROM PER_ORG_STRUCTURE_ELEMENTS STR
WHERE STR.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, STR.BUSINESS_GROUP_ID) START WITH STR.ORGANIZATION_ID_PARENT IN ( SELECT DISTINCT PE.ORGANIZATION_ID_PARENT
FROM PER_ORG_STRUCTURE_ELEMENTS PE
WHERE PE.ORGANIZATION_ID_PARENT NOT IN ( SELECT PO.ORGANIZATION_ID_CHILD
FROM PER_ORG_STRUCTURE_ELEMENTS PO
WHERE PO.ORG_STRUCTURE_VERSION_ID = STR.ORG_STRUCTURE_VERSION_ID ) ) CONNECT BY PRIOR STR.ORGANIZATION_ID_CHILD = STR.ORGANIZATION_ID_PARENT
AND PRIOR STR.ORG_STRUCTURE_VERSION_ID = STR.ORG_STRUCTURE_VERSION_ID
AND PRIOR STR.BUSINESS_GROUP_ID = STR.BUSINESS_GROUP_ID WITH READ ONLY