eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PQH_PC_ORG_HIERARCHIES_V

Source

View Text - Preformatted


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, hr_general.end_of_time) 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, hr_general.end_of_time) 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, hr_general.end_of_time) 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, hr_general.end_of_time) 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, hr_general.end_of_time) 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, hr_general.end_of_time) 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

View Text - HTML Formatted

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
, HR_GENERAL.END_OF_TIME)
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
, HR_GENERAL.END_OF_TIME)
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
, HR_GENERAL.END_OF_TIME)
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
, HR_GENERAL.END_OF_TIME)
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
, HR_GENERAL.END_OF_TIME)
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
, HR_GENERAL.END_OF_TIME)
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