View: HRBV_ORGANIZATION_DESCENDENTS

Product:	PER - Human Resources
Description:	- Retrofitted
Implementation/DBA Data:	APPS.HRBV_ORGANIZATION_DESCENDENTS

View Text

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

Columns