View: HRIFV_US_FEDREP_GEN_HRCHY

Product:	HRI - Human Resources Intelligence (Obsolete)
Description:
Implementation/DBA Data:	APPS.HRIFV_US_FEDREP_GEN_HRCHY

View Text

SELECT /* $HEADER: HRIVIEW.LDT 115.3 2001/09/18 10:51:35 PKM SHIP $ */ PGH.NAME HIERARCHY_NAME
, HR_BIS.BIS_DECODE_LOOKUP('HIERARCHY_TYPE'
, PGH.TYPE) HIERARCHY_TYPE
, PGH.TYPE HIERARCHY_TYPE_CODE
, PGV.VERSION_NUMBER HIERARCHY_VERSION_NUMBER
, PGV.DATE_FROM HIERARCHY_VERSION_DATE_FROM
, PGV.DATE_TO HIERARCHY_VERSION_DATE_TO
, NVL(PGV.DATE_TO
, HR_GENERAL.END_OF_TIME) HIER_VERSION_DATE_TO_NN
, PGV.STATUS HIER_VERSION_STATUS_CODE
, HR_BIS.BIS_DECODE_LOOKUP('ACTIVE_INACTIVE'
, PGV.STATUS) HIER_VERSION_STATUS
, GHS.ENTITY_LEVEL ENTITY_LEVEL
, GHS.NODE_TYPE NODE_TYPE_CODE
, HR_BIS.BIS_DECODE_LOOKUP('HIERARCHY_NODE_TYPE'
, GHS.NODE_TYPE) NODE_TYPE
, HOU.NAME NODE_NAME
, HOU.NAME NODE_LONG_NAME
, GHS.SUB_ENTITY_LEVEL SUB_ENTITY_LEVEL
, GHS.SUB_NODE_TYPE SUB_NODE_TYPE_CODE
, HR_BIS.BIS_DECODE_LOOKUP('HIERARCHY_NODE_TYPE'
, GHS.SUB_NODE_TYPE) SUB_NODE_TYPE
, A.LOCATION_CODE SUB_NODE_NAME
, A.LOCATION_CODE||DECODE(A.LOCATION_CODE
, NULL
, ''
, '
, ')|| A.ADDRESS_LINE_1||DECODE(A.ADDRESS_LINE_1
, NULL
, ''
, '
, ')|| A.ADDRESS_LINE_2||DECODE(A.ADDRESS_LINE_2
, NULL
, ''
, '
, ')|| A.TOWN_OR_CITY||DECODE(A.TOWN_OR_CITY
, NULL
, ''
, '
, ')|| A.REGION_2||DECODE(A.REGION_2
, NULL
, ''
, '
, ')|| A.COUNTRY SUB_NODE_LONG_NAME /* DESCRIPTIVE FLEXFIELDS */
, '_DF:PER:PER_GEN_HIERARCHY:PGH'
, '_DF:PER:PER_GEN_HIERARCHY_VERSIONS:PGV'
, '_DF:PER:PER_GEN_HIERARCHY_NODES:PGN'
, '_DF:PER:PER_GEN_HIERARCHY_NODES:PGN2'
, '_DF:PER:HR_LOCATIONS:A'
, GHS.HIERARCHY_ID HIERARCHY_ID
, GHS.HIERARCHY_VERSION_ID HIERARCHY_VERSION_ID
, GHS.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, GHS.ENTITY_ID ENTITY_ID
, GHS.HIERARCHY_NODE_ID HIERARCHY_NODE_ID
, GHS.SUB_ENTITY_BG_ID SUB_ENTITY_BG_ID
, GHS.SUB_ENTITY_ID SUB_ENTITY_ID
, GHS.SUB_HIERARCHY_NODE_ID SUB_HIERARCHY_NODE_ID
FROM HRI_GEN_HRCHY_SUMMARY GHS
, PER_GEN_HIERARCHY PGH
, HR_LOCATIONS_ALL A
, HR_LOCATION_EXTRA_INFO B
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_ORGANIZATION_INFORMATION HOI
, PER_GEN_HIERARCHY_VERSIONS PGV
, PER_GEN_HIERARCHY_NODES GHN
, PER_GEN_HIERARCHY_NODES GHN2
WHERE PGH.HIERARCHY_ID = GHS.HIERARCHY_ID
AND PGV.HIERARCHY_VERSION_ID = GHS.HIERARCHY_VERSION_ID
AND GHN.HIERARCHY_NODE_ID = GHS.SUB_HIERARCHY_NODE_ID
AND GHN2.HIERARCHY_NODE_ID = GHS.HIERARCHY_NODE_ID
AND GHS.SUB_ENTITY_ID = A.LOCATION_ID
AND A.LOCATION_ID = B.LOCATION_ID
AND B.INFORMATION_TYPE IN ('ESTABLISHMENT INFORMATION'
, 'VETS-100 SPECIFIC INFORMATION'
, 'EEO-1 SPECIFIC INFORMATION'
, 'MULTI WORK SITE INFORMATION')
AND GHS.SUB_NODE_TYPE = 'EST'
AND HOU.ORGANIZATION_ID = GHS.ENTITY_ID
AND HOU.ORGANIZATION_ID = HOI.ORGANIZATION_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'CLASS'
AND HOI.ORG_INFORMATION1 = 'PAR_ENT' UNION SELECT PGH.NAME HIERARCHY_NAME
, HR_BIS.BIS_DECODE_LOOKUP('HIERARCHY_TYPE'
, PGH.TYPE) HIERARCHY_TYPE
, PGH.TYPE HIERARCHY_TYPE_CODE
, PGV.VERSION_NUMBER HIERARCHY_VERSION_NUMBER
, PGV.DATE_FROM HIERARCHY_VERSION_DATE_FROM
, PGV.DATE_TO HIERARCHY_VERSION_DATE_TO
, NVL(PGV.DATE_TO
, HR_GENERAL.END_OF_TIME) HIER_VERSION_DATE_TO_NN
, PGV.STATUS HIER_VERSION_STATUS_DESC
, HR_BIS.BIS_DECODE_LOOKUP('ACTIVE_INACTIVE'
, PGV.STATUS) HIER_VERSION_STATUS
, GHS.ENTITY_LEVEL ENTITY_LEVEL
, GHS.NODE_TYPE NODE_TYPE
, HR_BIS.BIS_DECODE_LOOKUP('HIERARCHY_NODE_TYPE'
, GHS.NODE_TYPE) NODE_TYPE_DESC
, C.LOCATION_CODE NODE_NAME
, C.LOCATION_CODE||DECODE(C.LOCATION_CODE
, NULL
, ''
, '
, ')|| C.ADDRESS_LINE_1||DECODE(C.ADDRESS_LINE_1
, NULL
, ''
, '
, ')|| C.ADDRESS_LINE_2||DECODE(C.ADDRESS_LINE_2
, NULL
, ''
, '
, ')|| C.TOWN_OR_CITY||DECODE(C.TOWN_OR_CITY
, NULL
, ''
, '
, ')|| C.REGION_2||DECODE(C.REGION_2
, NULL
, ''
, '
, ')|| C.COUNTRY NODE_LONG_NAME
, GHS.SUB_ENTITY_LEVEL SUB_ENTITY_LEVEL
, GHS.SUB_NODE_TYPE SUB_NODE_TYPE_CODE
, HR_BIS.BIS_DECODE_LOOKUP('HIERARCHY_NODE_TYPE'
, GHS.SUB_NODE_TYPE) SUB_NODE_TYPE
, C.LOCATION_CODE SUB_NODE_NAME
, A.LOCATION_CODE||DECODE(A.LOCATION_CODE
, NULL
, ''
, '
, ')|| A.ADDRESS_LINE_1||DECODE(A.ADDRESS_LINE_1
, NULL
, ''
, '
, ')|| A.ADDRESS_LINE_2||DECODE(A.ADDRESS_LINE_2
, NULL
, ''
, '
, ')|| A.TOWN_OR_CITY||DECODE(A.TOWN_OR_CITY
, NULL
, ''
, '
, ')|| A.REGION_2||DECODE(A.REGION_2
, NULL
, ''
, '
, ')|| A.COUNTRY SUB_NODE_LONG_NAME /* DESCRIPTIVE FLEXFIELDS */
, '_DF:PER:PER_GEN_HIERARCHY:PGH'
, '_DF:PER:PER_GEN_HIERARCHY_VERSIONS:PGV'
, '_DF:PER:PER_GEN_HIERARCHY_NODES:PGN'
, '_DF:PER:PER_GEN_HIERARCHY_NODES:PGN2'
, '_DF:PER:HR_LOCATIONS:A'
, GHS.HIERARCHY_ID HIERARCHY_ID
, GHS.HIERARCHY_VERSION_ID HIERARCHY_VERSION_ID
, GHS.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, GHS.ENTITY_ID ENTITY_ID
, GHS.HIERARCHY_NODE_ID HIERARCHY_NODE_ID
, GHS.SUB_ENTITY_BG_ID SUB_ENTITY_BG_ID
, GHS.SUB_ENTITY_ID SUB_ENTITY_ID
, GHS.SUB_HIERARCHY_NODE_ID SUB_HIERARCHY_NODE_ID
FROM HRI_GEN_HRCHY_SUMMARY GHS
, PER_GEN_HIERARCHY PGH
, HR_LOCATIONS_ALL A
, HR_LOCATIONS_ALL C
, HR_LOCATION_EXTRA_INFO B
, PER_GEN_HIERARCHY_VERSIONS PGV
, PER_GEN_HIERARCHY_NODES GHN
, PER_GEN_HIERARCHY_NODES GHN2
WHERE PGH.HIERARCHY_ID = GHS.HIERARCHY_ID
AND PGV.HIERARCHY_VERSION_ID = GHS.HIERARCHY_VERSION_ID
AND GHN.HIERARCHY_NODE_ID = GHS.SUB_HIERARCHY_NODE_ID
AND GHN2.HIERARCHY_NODE_ID = GHS.HIERARCHY_NODE_ID
AND GHS.SUB_ENTITY_ID = A.LOCATION_ID
AND GHS.SUB_NODE_TYPE = 'LOC'
AND GHS.NODE_TYPE = 'EST'
AND GHS.ENTITY_ID = C.LOCATION_ID
AND C.LOCATION_ID = B.LOCATION_ID
AND B.INFORMATION_TYPE IN ('ESTABLISHMENT INFORMATION'
, 'VETS-100 SPECIFIC INFORMATION'
, 'EEO-1 SPECIFIC INFORMATION'
, 'MULTI WORK SITE INFORMATION') UNION SELECT PGH.NAME HIERARCHY_NAME
, HR_BIS.BIS_DECODE_LOOKUP('HIERARCHY_TYPE'
, PGH.TYPE) HIERARCHY_TYPE
, PGH.TYPE HIERARCHY_TYPE_CODE
, PGV.VERSION_NUMBER HIERARCHY_VERSION_NUMBER
, PGV.DATE_FROM HIERARCHY_VERSION_DATE_FROM
, PGV.DATE_TO HIERARCHY_VERSION_DATE_TO
, NVL(PGV.DATE_TO
, HR_GENERAL.END_OF_TIME) HIER_VERSION_DATE_TO_NN
, PGV.STATUS HIER_VERSION_STATUS_CODE
, HR_BIS.BIS_DECODE_LOOKUP('ACTIVE_INACTIVE'
, PGV.STATUS) HIER_VERSION_STATUS
, GHS.ENTITY_LEVEL ENTITY_LEVEL
, GHS.NODE_TYPE NODE_TYPE_CODE
, HR_BIS.BIS_DECODE_LOOKUP('HIERARCHY_NODE_TYPE'
, GHS.NODE_TYPE) NODE_TYPE
, HOU.NAME NODE_NAME
, HOU.NAME NODE_LONG_NAME
, GHS.SUB_ENTITY_LEVEL SUB_ENTITY_LEVEL
, GHS.SUB_NODE_TYPE SUB_NODE_TYPE_CODE
, HR_BIS.BIS_DECODE_LOOKUP('HIERARCHY_NODE_TYPE'
, GHS.SUB_NODE_TYPE) SUB_NODE_TYPE
, A.LOCATION_CODE SUB_NODE_NAME
, A.LOCATION_CODE||DECODE(A.LOCATION_CODE
, NULL
, ''
, '
, ')|| A.ADDRESS_LINE_1||DECODE(A.ADDRESS_LINE_1
, NULL
, ''
, '
, ')|| A.ADDRESS_LINE_2||DECODE(A.ADDRESS_LINE_2
, NULL
, ''
, '
, ')|| A.TOWN_OR_CITY||DECODE(A.TOWN_OR_CITY
, NULL
, ''
, '
, ')|| A.REGION_2||DECODE(A.REGION_2
, NULL
, ''
, '
, ')|| A.COUNTRY SUB_NODE_LONG_NAME /* DESCRIPTIVE FLEXFIELDS */
, '_DF:PER:PER_GEN_HIERARCHY:PGH'
, '_DF:PER:PER_GEN_HIERARCHY_VERSIONS:PGV'
, '_DF:PER:PER_GEN_HIERARCHY_NODES:PGN'
, '_DF:PER:PER_GEN_HIERARCHY_NODES:PGN2'
, '_DF:PER:HR_LOCATIONS:A'
, GHS.HIERARCHY_ID HIERARCHY_ID
, GHS.HIERARCHY_VERSION_ID HIERARCHY_VERSION_ID
, GHS.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, GHS.ENTITY_ID ENTITY_ID
, GHS.HIERARCHY_NODE_ID HIERARCHY_NODE_ID
, GHS.SUB_ENTITY_BG_ID SUB_ENTITY_BG_ID
, GHS.SUB_ENTITY_ID SUB_ENTITY_ID
, GHS.SUB_HIERARCHY_NODE_ID SUB_HIERARCHY_NODE_ID
FROM HRI_GEN_HRCHY_SUMMARY GHS
, PER_GEN_HIERARCHY PGH
, HR_LOCATIONS_ALL A
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_ORGANIZATION_INFORMATION HOI
, PER_GEN_HIERARCHY_VERSIONS PGV
, PER_GEN_HIERARCHY_NODES GHN
, PER_GEN_HIERARCHY_NODES GHN2
WHERE PGH.HIERARCHY_ID = GHS.HIERARCHY_ID
AND PGV.HIERARCHY_VERSION_ID = GHS.HIERARCHY_VERSION_ID
AND GHN.HIERARCHY_NODE_ID = GHS.SUB_HIERARCHY_NODE_ID
AND GHN2.HIERARCHY_NODE_ID = GHS.HIERARCHY_NODE_ID
AND GHS.SUB_ENTITY_ID = A.LOCATION_ID
AND GHS.SUB_NODE_TYPE = 'LOC'
AND GHS.NODE_TYPE = 'PAR'
AND HOU.ORGANIZATION_ID = GHS.ENTITY_ID
AND HOU.ORGANIZATION_ID = HOI.ORGANIZATION_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'CLASS'
AND HOI.ORG_INFORMATION1 = 'PAR_ENT' WITH READ ONLY

Columns