SELECT 0 RN , 0 LEVEL1 , HR_GENERAL.DECODE_ORGANIZATION (POS.BUSINESS_GROUP_ID) , POS.BUSINESS_GROUP_ID , HR_GENERAL.DECODE_ORGANIZATION (A.ORGANIZATION_ID_PARENT) , A.ORGANIZATION_ID_PARENT ORGANIZATION_ID , PQH_PSF_BUS.POSITION_CONTROL_ENABLED(A.ORGANIZATION_ID_PARENT) FROM PER_ORGANIZATION_STRUCTURES POS , PER_ORG_STRUCTURE_VERSIONS VER , PER_ORG_STRUCTURE_ELEMENTS A WHERE POS.ORGANIZATION_STRUCTURE_ID = VER.ORGANIZATION_STRUCTURE_ID AND VER.ORG_STRUCTURE_VERSION_ID = A.ORG_STRUCTURE_VERSION_ID AND NVL(POS.POSITION_CONTROL_STRUCTURE_FLG , 'N') = 'Y' AND (SELECT SS.EFFECTIVE_DATE FROM FND_SESSIONS SS WHERE SS.SESSION_ID = USERENV('SESSIONID') ) BETWEEN VER.DATE_FROM AND NVL(VER.DATE_TO , TO_DATE('31-DEC-4712' , 'DD-MON-YYYY')) AND A.ORGANIZATION_ID_PARENT = ( SELECT A.ORGANIZATION_ID_PARENT FROM PER_ORGANIZATION_STRUCTURES POS , PER_ORG_STRUCTURE_VERSIONS VER , PER_ORG_STRUCTURE_ELEMENTS A WHERE POS.ORGANIZATION_STRUCTURE_ID = VER.ORGANIZATION_STRUCTURE_ID AND VER.ORG_STRUCTURE_VERSION_ID = A.ORG_STRUCTURE_VERSION_ID AND (SELECT SS.EFFECTIVE_DATE FROM FND_SESSIONS SS WHERE SS.SESSION_ID = USERENV('SESSIONID') ) BETWEEN VER.DATE_FROM AND NVL(VER.DATE_TO , TO_DATE('31-DEC-4712' , 'DD-MON-YYYY')) AND POS.POSITION_CONTROL_STRUCTURE_FLG = 'Y' MINUS SELECT A.ORGANIZATION_ID_CHILD FROM PER_ORGANIZATION_STRUCTURES POS , PER_ORG_STRUCTURE_VERSIONS VER , PER_ORG_STRUCTURE_ELEMENTS A WHERE POS.ORGANIZATION_STRUCTURE_ID = VER.ORGANIZATION_STRUCTURE_ID AND VER.ORG_STRUCTURE_VERSION_ID = A.ORG_STRUCTURE_VERSION_ID AND (SELECT SS.EFFECTIVE_DATE FROM FND_SESSIONS SS WHERE SS.SESSION_ID = USERENV('SESSIONID') ) BETWEEN VER.DATE_FROM AND NVL(VER.DATE_TO , TO_DATE('31-DEC-4712' , 'DD-MON-YYYY')) AND POS.POSITION_CONTROL_STRUCTURE_FLG = 'Y' ) UNION SELECT ROWNUM RN , LEVEL LEVEL1 , HR_GENERAL.DECODE_ORGANIZATION (A.BUSINESS_GROUP_ID) , A.BUSINESS_GROUP_ID , HR_GENERAL.DECODE_ORGANIZATION (A.ORGANIZATION_ID_CHILD ) , A.ORGANIZATION_ID_CHILD ORGANIZATION_ID , PQH_PSF_BUS.POSITION_CONTROL_ENABLED(A.ORGANIZATION_ID_CHILD) FROM PER_ORG_STRUCTURE_ELEMENTS A START WITH ORGANIZATION_ID_PARENT = ( SELECT A.ORGANIZATION_ID_PARENT FROM PER_ORGANIZATION_STRUCTURES POS , PER_ORG_STRUCTURE_VERSIONS VER , PER_ORG_STRUCTURE_ELEMENTS A WHERE POS.ORGANIZATION_STRUCTURE_ID = VER.ORGANIZATION_STRUCTURE_ID AND VER.ORG_STRUCTURE_VERSION_ID = A.ORG_STRUCTURE_VERSION_ID AND (SELECT SS.EFFECTIVE_DATE FROM FND_SESSIONS SS WHERE SS.SESSION_ID = USERENV('SESSIONID') ) BETWEEN VER.DATE_FROM AND NVL(VER.DATE_TO , TO_DATE('31-DEC-4712' , 'DD-MON-YYYY')) AND POS.POSITION_CONTROL_STRUCTURE_FLG = 'Y' MINUS SELECT A.ORGANIZATION_ID_CHILD FROM PER_ORGANIZATION_STRUCTURES POS , PER_ORG_STRUCTURE_VERSIONS VER , PER_ORG_STRUCTURE_ELEMENTS A WHERE POS.ORGANIZATION_STRUCTURE_ID = VER.ORGANIZATION_STRUCTURE_ID AND VER.ORG_STRUCTURE_VERSION_ID = A.ORG_STRUCTURE_VERSION_ID AND (SELECT SS.EFFECTIVE_DATE FROM FND_SESSIONS SS WHERE SS.SESSION_ID = USERENV('SESSIONID') ) BETWEEN VER.DATE_FROM AND NVL(VER.DATE_TO , TO_DATE('31-DEC-4712' , 'DD-MON-YYYY')) AND POS.POSITION_CONTROL_STRUCTURE_FLG = 'Y' ) AND ORG_STRUCTURE_VERSION_ID = (SELECT ORG_STRUCTURE_VERSION_ID FROM PER_ORGANIZATION_STRUCTURES POS , PER_ORG_STRUCTURE_VERSIONS VER WHERE POS.ORGANIZATION_STRUCTURE_ID = VER.ORGANIZATION_STRUCTURE_ID AND (SELECT SS.EFFECTIVE_DATE FROM FND_SESSIONS SS WHERE SS.SESSION_ID = USERENV('SESSIONID') ) BETWEEN VER.DATE_FROM AND NVL(VER.DATE_TO , TO_DATE('31-DEC-4712' , 'DD-MON-YYYY')) AND POS.POSITION_CONTROL_STRUCTURE_FLG = 'Y' ) CONNECT BY A.ORGANIZATION_ID_PARENT = PRIOR A.ORGANIZATION_ID_CHILD AND A.ORG_STRUCTURE_VERSION_ID = PRIOR A.ORG_STRUCTURE_VERSION_ID