VIEW: APPS.HRIFV_US_FEDREP_GEN_HRCHY

Source

View Text - Preformatted

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 , houT.name node_name , houT.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 , aT.location_code sub_node_name , aT.location_code||decode(aT.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 , '_DF:PER:PER_GEN_HIERARCHY:pgh' , '_DF:PER:PER_GEN_HIERARCHY_VERSIONS:pgv' , '_DF:PER:PER_GEN_HIERARCHY_NODES:ghn' , '_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_tl aT , hr_location_extra_info b , hr_all_organization_units_tl houT , 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 pgh.business_group_id = ghs.business_group_id and pgv.hierarchy_version_id = ghs.hierarchy_version_id and ghn.hierarchy_node_id = ghs.sub_hierarchy_node_id and ghn.hierarchy_version_id = ghs.hierarchy_version_id and ghn2.hierarchy_node_id = ghs.hierarchy_node_id and ghn2.hierarchy_version_id = ghs.hierarchy_version_id and ghs.sub_entity_id = a.location_id and a.location_id = aT.location_id and aT.language = USERENV('LANG') 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 houT.organization_id = ghs.entity_id and houT.language = USERENV('LANG') and houT.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 , cT.location_code node_name , cT.location_code||decode(cT.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 , aT.location_code sub_node_name , aT.location_code||decode(aT.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 , '_DF:PER:PER_GEN_HIERARCHY:pgh' , '_DF:PER:PER_GEN_HIERARCHY_VERSIONS:pgv' , '_DF:PER:PER_GEN_HIERARCHY_NODES:ghn' , '_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_tl aT , hr_locations_all c , hr_locations_all_tl cT , 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 pgh.business_group_id = ghs.business_group_id and pgv.hierarchy_version_id = ghs.hierarchy_version_id and ghn.hierarchy_node_id = ghs.sub_hierarchy_node_id and ghn.hierarchy_version_id = ghs.hierarchy_version_id and ghn2.hierarchy_node_id = ghs.hierarchy_node_id and ghn2.hierarchy_version_id = ghs.hierarchy_version_id and ghs.sub_entity_id = a.location_id and a.location_id = aT.location_id and aT.language = USERENV('LANG') and ghs.sub_node_type in ( 'LOC', 'EST') and ghs.node_type = 'EST' and ghs.entity_id = c.location_id and c.location_id = cT.location_id and cT.language = USERENV('LANG') 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 , houT.name node_name , houT.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 , aT.location_code sub_node_name , aT.location_code||decode(aT.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 , '_DF:PER:PER_GEN_HIERARCHY:pgh' , '_DF:PER:PER_GEN_HIERARCHY_VERSIONS:pgv' , '_DF:PER:PER_GEN_HIERARCHY_NODES:ghn' , '_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_tl aT , hr_all_organization_units_tl houT , 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 pgh.business_group_id = ghs.business_group_id and pgv.hierarchy_version_id = ghs.hierarchy_version_id and ghn.hierarchy_node_id = ghs.sub_hierarchy_node_id and ghn.hierarchy_version_id = ghs.hierarchy_version_id and ghn2.hierarchy_node_id = ghs.hierarchy_node_id and ghn2.hierarchy_version_id = ghs.hierarchy_version_id and ghs.sub_entity_id = a.location_id and a.location_id = aT.location_id and aT.language = USERENV('LANG') and ghs.sub_node_type = 'LOC' and ghs.node_type = 'PAR' and houT.organization_id = ghs.entity_id and houT.language = USERENV('LANG') and houT.organization_id = hoi.organization_id and hoi.org_information_context = 'CLASS' and hoi.org_information1 = 'PAR_ENT' WITH READ ONLY

View Text - HTML Formatted

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
, HOUT.NAME NODE_NAME
, HOUT.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
, AT.LOCATION_CODE SUB_NODE_NAME
, AT.LOCATION_CODE||DECODE(AT.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
, '_DF:PER:PER_GEN_HIERARCHY:PGH'
, '_DF:PER:PER_GEN_HIERARCHY_VERSIONS:PGV'
, '_DF:PER:PER_GEN_HIERARCHY_NODES:GHN'
, '_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_TL AT
, HR_LOCATION_EXTRA_INFO B
, HR_ALL_ORGANIZATION_UNITS_TL HOUT
, 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 PGH.BUSINESS_GROUP_ID = GHS.BUSINESS_GROUP_ID
AND PGV.HIERARCHY_VERSION_ID = GHS.HIERARCHY_VERSION_ID
AND GHN.HIERARCHY_NODE_ID = GHS.SUB_HIERARCHY_NODE_ID
AND GHN.HIERARCHY_VERSION_ID = GHS.HIERARCHY_VERSION_ID
AND GHN2.HIERARCHY_NODE_ID = GHS.HIERARCHY_NODE_ID
AND GHN2.HIERARCHY_VERSION_ID = GHS.HIERARCHY_VERSION_ID
AND GHS.SUB_ENTITY_ID = A.LOCATION_ID
AND A.LOCATION_ID = AT.LOCATION_ID
AND AT.LANGUAGE = USERENV('LANG')
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 HOUT.ORGANIZATION_ID = GHS.ENTITY_ID
AND HOUT.LANGUAGE = USERENV('LANG')
AND HOUT.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
, CT.LOCATION_CODE NODE_NAME
, CT.LOCATION_CODE||DECODE(CT.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
, AT.LOCATION_CODE SUB_NODE_NAME
, AT.LOCATION_CODE||DECODE(AT.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
, '_DF:PER:PER_GEN_HIERARCHY:PGH'
, '_DF:PER:PER_GEN_HIERARCHY_VERSIONS:PGV'
, '_DF:PER:PER_GEN_HIERARCHY_NODES:GHN'
, '_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_TL AT
, HR_LOCATIONS_ALL C
, HR_LOCATIONS_ALL_TL CT
, 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 PGH.BUSINESS_GROUP_ID = GHS.BUSINESS_GROUP_ID
AND PGV.HIERARCHY_VERSION_ID = GHS.HIERARCHY_VERSION_ID
AND GHN.HIERARCHY_NODE_ID = GHS.SUB_HIERARCHY_NODE_ID
AND GHN.HIERARCHY_VERSION_ID = GHS.HIERARCHY_VERSION_ID
AND GHN2.HIERARCHY_NODE_ID = GHS.HIERARCHY_NODE_ID
AND GHN2.HIERARCHY_VERSION_ID = GHS.HIERARCHY_VERSION_ID
AND GHS.SUB_ENTITY_ID = A.LOCATION_ID
AND A.LOCATION_ID = AT.LOCATION_ID
AND AT.LANGUAGE = USERENV('LANG')
AND GHS.SUB_NODE_TYPE IN ( 'LOC'
, 'EST')
AND GHS.NODE_TYPE = 'EST'
AND GHS.ENTITY_ID = C.LOCATION_ID
AND C.LOCATION_ID = CT.LOCATION_ID
AND CT.LANGUAGE = USERENV('LANG')
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
, HOUT.NAME NODE_NAME
, HOUT.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
, AT.LOCATION_CODE SUB_NODE_NAME
, AT.LOCATION_CODE||DECODE(AT.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
, '_DF:PER:PER_GEN_HIERARCHY:PGH'
, '_DF:PER:PER_GEN_HIERARCHY_VERSIONS:PGV'
, '_DF:PER:PER_GEN_HIERARCHY_NODES:GHN'
, '_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_TL AT
, HR_ALL_ORGANIZATION_UNITS_TL HOUT
, 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 PGH.BUSINESS_GROUP_ID = GHS.BUSINESS_GROUP_ID
AND PGV.HIERARCHY_VERSION_ID = GHS.HIERARCHY_VERSION_ID
AND GHN.HIERARCHY_NODE_ID = GHS.SUB_HIERARCHY_NODE_ID
AND GHN.HIERARCHY_VERSION_ID = GHS.HIERARCHY_VERSION_ID
AND GHN2.HIERARCHY_NODE_ID = GHS.HIERARCHY_NODE_ID
AND GHN2.HIERARCHY_VERSION_ID = GHS.HIERARCHY_VERSION_ID
AND GHS.SUB_ENTITY_ID = A.LOCATION_ID
AND A.LOCATION_ID = AT.LOCATION_ID
AND AT.LANGUAGE = USERENV('LANG')
AND GHS.SUB_NODE_TYPE = 'LOC'
AND GHS.NODE_TYPE = 'PAR'
AND HOUT.ORGANIZATION_ID = GHS.ENTITY_ID
AND HOUT.LANGUAGE = USERENV('LANG')
AND HOUT.ORGANIZATION_ID = HOI.ORGANIZATION_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'CLASS'
AND HOI.ORG_INFORMATION1 = 'PAR_ENT' WITH READ ONLY