View: PQH_PC_ORG_HIERARCHIES_V

Product:	PQH - Public Sector HR
Description:	Position Control Organization Hierarchy view for reports
Implementation/DBA Data:	APPS.PQH_PC_ORG_HIERARCHIES_V

View Text

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

Columns