1. Tables and Views for HCM
  2. IRC_GEOHIERARCHY_IDENTIFIERS_V

IRC_GEOHIERARCHY_IDENTIFIERS_V

Details

  • Schema: FUSION

  • Object owner: IRC

  • Object type: VIEW

Columns

Name

GEOGRAPHY_TYPE

GEOGRAPHY_ID

IDENTIFIER_VALUE

COUNTRY_CODE

LANGUAGE

GEOGRAPHY_ELEMENT1

GEOGRAPHY_ELEMENT2

GEOGRAPHY_ELEMENT3

GEOGRAPHY_ELEMENT4

GEOGRAPHY_ELEMENT5

GEOGRAPHY_ELEMENT6

GEOGRAPHY_ELEMENT7

GEOGRAPHY_ELEMENT8

GEOGRAPHY_ELEMENT9

GEOGRAPHY_ELEMENT10

Query

SQL_Statement

SELECT GEOGRAPHYPEO.GEOGRAPHY_TYPE,

GEOGRAPHYPEO.GEOGRAPHY_ID,

NVL((SELECT GEO1.TERRITORY_SHORT_NAME

FROM FND_TERRITORIES_TL GEO1

WHERE GEOGRAPHYPEO.GEOGRAPHY_TYPE='COUNTRY'

AND GEO1.TERRITORY_CODE = GEOGRAPHYPEO.COUNTRY_CODE

AND GEO1.LANGUAGE= FNDLANG.LANGUAGE_CODE), GEOGRAPHYIDENTIFIERPEO.IDENTIFIER_VALUE) IDENTIFIER_VALUE,

GEOGRAPHYPEO.COUNTRY_CODE,

FNDLANG.LANGUAGE_CODE LANGUAGE,

NVL((SELECT GEO1.TERRITORY_SHORT_NAME

FROM FND_TERRITORIES_TL GEO1

WHERE GEO1.TERRITORY_CODE = GEOGRAPHYPEO.GEOGRAPHY_ELEMENT1_CODE

AND GEO1.LANGUAGE= FNDLANG.LANGUAGE_CODE

FETCH FIRST 1 ROW ONLY), GEOGRAPHYPEO.GEOGRAPHY_ELEMENT1) GEOGRAPHY_ELEMENT1,

NVL((SELECT DECODE(GEOGRAPHYPEO.GEOGRAPHY_TYPE, GEO2.GEOGRAPHY_TYPE, GEOGRAPHYIDENTIFIERPEO.IDENTIFIER_VALUE,GEO2.IDENTIFIER_VALUE)

FROM HZ_GEOGRAPHY_IDENTIFIERS GEO2

WHERE GEO2.GEOGRAPHY_ID = GEOGRAPHYPEO.GEOGRAPHY_ELEMENT2_ID

AND GEO2.IDENTIFIER_SUBTYPE='STANDARD_NAME'

AND GEO2.LANGUAGE_CODE= FNDLANG.LANGUAGE_CODE

ORDER BY GEO2.PRIMARY_FLAG DESC, GEO2.CREATION_DATE FETCH FIRST 1 ROW ONLY), GEOGRAPHYPEO.GEOGRAPHY_ELEMENT2) GEOGRAPHY_ELEMENT2,

NVL((SELECT DECODE(GEOGRAPHYPEO.GEOGRAPHY_TYPE, GEO3.GEOGRAPHY_TYPE, GEOGRAPHYIDENTIFIERPEO.IDENTIFIER_VALUE,GEO3.IDENTIFIER_VALUE)

FROM HZ_GEOGRAPHY_IDENTIFIERS GEO3

WHERE GEO3.GEOGRAPHY_ID = GEOGRAPHYPEO.GEOGRAPHY_ELEMENT3_ID

AND GEO3.IDENTIFIER_SUBTYPE='STANDARD_NAME'

AND GEO3.LANGUAGE_CODE= FNDLANG.LANGUAGE_CODE

ORDER BY GEO3.PRIMARY_FLAG DESC, GEO3.CREATION_DATE FETCH FIRST 1 ROW ONLY), GEOGRAPHYPEO.GEOGRAPHY_ELEMENT3) GEOGRAPHY_ELEMENT3,

NVL((SELECT DECODE(GEOGRAPHYPEO.GEOGRAPHY_TYPE, GEO4.GEOGRAPHY_TYPE, GEOGRAPHYIDENTIFIERPEO.IDENTIFIER_VALUE,GEO4.IDENTIFIER_VALUE)

FROM HZ_GEOGRAPHY_IDENTIFIERS GEO4

WHERE GEO4.GEOGRAPHY_ID = GEOGRAPHYPEO.GEOGRAPHY_ELEMENT4_ID

AND GEO4.IDENTIFIER_SUBTYPE='STANDARD_NAME'

AND GEO4.LANGUAGE_CODE= FNDLANG.LANGUAGE_CODE

ORDER BY GEO4.PRIMARY_FLAG DESC, GEO4.CREATION_DATE FETCH FIRST 1 ROW ONLY), GEOGRAPHYPEO.GEOGRAPHY_ELEMENT4) GEOGRAPHY_ELEMENT4,

NVL((SELECT DECODE(GEOGRAPHYPEO.GEOGRAPHY_TYPE, GEO5.GEOGRAPHY_TYPE, GEOGRAPHYIDENTIFIERPEO.IDENTIFIER_VALUE,GEO5.IDENTIFIER_VALUE)

FROM HZ_GEOGRAPHY_IDENTIFIERS GEO5

WHERE GEO5.GEOGRAPHY_ID = GEOGRAPHYPEO.GEOGRAPHY_ELEMENT5_ID

AND GEO5.IDENTIFIER_SUBTYPE='STANDARD_NAME'

AND GEO5.LANGUAGE_CODE= FNDLANG.LANGUAGE_CODE

ORDER BY GEO5.PRIMARY_FLAG DESC, GEO5.CREATION_DATE FETCH FIRST 1 ROW ONLY), GEOGRAPHYPEO.GEOGRAPHY_ELEMENT5) GEOGRAPHY_ELEMENT5,

NVL((SELECT DECODE(GEOGRAPHYPEO.GEOGRAPHY_TYPE, GEO6.GEOGRAPHY_TYPE, GEOGRAPHYIDENTIFIERPEO.IDENTIFIER_VALUE,GEO6.IDENTIFIER_VALUE)

FROM HZ_GEOGRAPHY_IDENTIFIERS GEO6

WHERE GEO6.GEOGRAPHY_ID = GEOGRAPHYPEO.GEOGRAPHY_ELEMENT6_ID

AND GEO6.IDENTIFIER_SUBTYPE='STANDARD_NAME'

AND GEO6.LANGUAGE_CODE= FNDLANG.LANGUAGE_CODE

ORDER BY GEO6.PRIMARY_FLAG DESC, GEO6.CREATION_DATE FETCH FIRST 1 ROW ONLY), GEOGRAPHYPEO.GEOGRAPHY_ELEMENT6) GEOGRAPHY_ELEMENT6,

NVL((SELECT DECODE(GEOGRAPHYPEO.GEOGRAPHY_TYPE, GEO7.GEOGRAPHY_TYPE, GEOGRAPHYIDENTIFIERPEO.IDENTIFIER_VALUE,GEO7.IDENTIFIER_VALUE)

FROM HZ_GEOGRAPHY_IDENTIFIERS GEO7

WHERE GEO7.GEOGRAPHY_ID = GEOGRAPHYPEO.GEOGRAPHY_ELEMENT7_ID

AND GEO7.IDENTIFIER_SUBTYPE='STANDARD_NAME'

AND GEO7.LANGUAGE_CODE= FNDLANG.LANGUAGE_CODE

ORDER BY GEO7.PRIMARY_FLAG DESC, GEO7.CREATION_DATE FETCH FIRST 1 ROW ONLY), GEOGRAPHYPEO.GEOGRAPHY_ELEMENT7) GEOGRAPHY_ELEMENT7,

NVL((SELECT DECODE(GEOGRAPHYPEO.GEOGRAPHY_TYPE, GEO8.GEOGRAPHY_TYPE, GEOGRAPHYIDENTIFIERPEO.IDENTIFIER_VALUE,GEO8.IDENTIFIER_VALUE)

FROM HZ_GEOGRAPHY_IDENTIFIERS GEO8

WHERE GEO8.GEOGRAPHY_ID = GEOGRAPHYPEO.GEOGRAPHY_ELEMENT8_ID

AND GEO8.IDENTIFIER_SUBTYPE='STANDARD_NAME'

AND GEO8.LANGUAGE_CODE= FNDLANG.LANGUAGE_CODE

ORDER BY GEO8.PRIMARY_FLAG DESC, GEO8.CREATION_DATE FETCH FIRST 1 ROW ONLY), GEOGRAPHYPEO.GEOGRAPHY_ELEMENT8) GEOGRAPHY_ELEMENT8,

NVL((SELECT DECODE(GEOGRAPHYPEO.GEOGRAPHY_TYPE, GEO9.GEOGRAPHY_TYPE, GEOGRAPHYIDENTIFIERPEO.IDENTIFIER_VALUE,GEO9.IDENTIFIER_VALUE)

FROM HZ_GEOGRAPHY_IDENTIFIERS GEO9

WHERE GEO9.GEOGRAPHY_ID = GEOGRAPHYPEO.GEOGRAPHY_ELEMENT9_ID

AND GEO9.IDENTIFIER_SUBTYPE='STANDARD_NAME'

AND GEO9.LANGUAGE_CODE= FNDLANG.LANGUAGE_CODE

ORDER BY GEO9.PRIMARY_FLAG DESC, GEO9.CREATION_DATE FETCH FIRST 1 ROW ONLY), GEOGRAPHYPEO.GEOGRAPHY_ELEMENT9) GEOGRAPHY_ELEMENT9,

NVL((SELECT DECODE(GEOGRAPHYPEO.GEOGRAPHY_TYPE, GEO10.GEOGRAPHY_TYPE, GEOGRAPHYIDENTIFIERPEO.IDENTIFIER_VALUE,GEO10.IDENTIFIER_VALUE)

FROM HZ_GEOGRAPHY_IDENTIFIERS GEO10

WHERE GEO10.GEOGRAPHY_ID = GEOGRAPHYPEO.GEOGRAPHY_ELEMENT10_ID

AND GEO10.IDENTIFIER_SUBTYPE='STANDARD_NAME'

AND GEO10.LANGUAGE_CODE= FNDLANG.LANGUAGE_CODE

ORDER BY GEO10.PRIMARY_FLAG DESC, GEO10.CREATION_DATE FETCH FIRST 1 ROW ONLY), GEOGRAPHYPEO.GEOGRAPHY_ELEMENT10) GEOGRAPHY_ELEMENT10

FROM HZ_GEOGRAPHIES GEOGRAPHYPEO,

HZ_GEOGRAPHY_IDENTIFIERS GEOGRAPHYIDENTIFIERPEO,

FND_LANGUAGES_B FNDLANG

WHERE GEOGRAPHYPEO.GEOGRAPHY_ID=GEOGRAPHYIDENTIFIERPEO.GEOGRAPHY_ID

AND GEOGRAPHYIDENTIFIERPEO.IDENTIFIER_SUBTYPE='STANDARD_NAME'

AND GEOGRAPHYIDENTIFIERPEO.IDENTIFIER_TYPE='NAME'

AND GEOGRAPHYPEO.GEOGRAPHY_USE='MASTER_REF'

AND FNDLANG.ACTIVATION_STATUS = 'ACTIVE'

AND TRUNC(SYSDATE) BETWEEN GEOGRAPHYPEO.START_DATE AND GEOGRAPHYPEO.END_DATE

AND (

(GEOGRAPHYIDENTIFIERPEO.PRIMARY_FLAG='Y'

AND NOT EXISTS (SELECT 1 FROM HZ_GEOGRAPHY_IDENTIFIERS I

WHERE I.IDENTIFIER_TYPE = GEOGRAPHYIDENTIFIERPEO.IDENTIFIER_TYPE AND I.GEOGRAPHY_ID = GEOGRAPHYIDENTIFIERPEO.GEOGRAPHY_ID AND I.LANGUAGE_CODE = FNDLANG.LANGUAGE_CODE)

)

OR

(GEOGRAPHYIDENTIFIERPEO.LANGUAGE_CODE = FNDLANG.LANGUAGE_CODE

AND NOT EXISTS (SELECT 1 FROM HZ_GEOGRAPHY_IDENTIFIERS I

WHERE I.IDENTIFIER_TYPE = GEOGRAPHYIDENTIFIERPEO.IDENTIFIER_TYPE AND I.GEOGRAPHY_ID = GEOGRAPHYIDENTIFIERPEO.GEOGRAPHY_ID AND I.LANGUAGE_CODE = FNDLANG.LANGUAGE_CODE AND I.PRIMARY_FLAG='Y')

AND GEOGRAPHYIDENTIFIERPEO.GEOGRAPHY_IDENTIFIER_ID = (SELECT GEOGRAPHY_IDENTIFIER_ID FROM HZ_GEOGRAPHY_IDENTIFIERS GI

WHERE GI.IDENTIFIER_TYPE = GEOGRAPHYIDENTIFIERPEO.IDENTIFIER_TYPE AND GI.GEOGRAPHY_ID = GEOGRAPHYIDENTIFIERPEO.GEOGRAPHY_ID AND GI.LANGUAGE_CODE = FNDLANG.LANGUAGE_CODE

ORDER BY GI.CREATION_DATE FETCH FIRST 1 ROW ONLY)

)

OR

(GEOGRAPHYIDENTIFIERPEO.PRIMARY_FLAG='Y'

AND GEOGRAPHYIDENTIFIERPEO.LANGUAGE_CODE = FNDLANG.LANGUAGE_CODE

)

)

AND ( GEOGRAPHYPEO.COUNTRY_CODE <> 'US'

OR

GEOGRAPHYPEO.GEOGRAPHY_TYPE <> 'CITY'

OR

'Y' = (CASE

WHEN NOT EXISTS (SELECT 1 FROM

(SELECT STYL.STYLE_FORMAT_CODE

FROM HZ_STYLE_FMT_LOCALES STYL,

PAY_INSTALLED_LEGISLATIONS PAYINST

WHERE PAYINST.LEGISLATION_CODE='US'

AND PAYINST.ADDRESS_STYLE_CODE=STYL.STYLE_CODE

AND PAYINST.LEGISLATION_CODE=STYL.TERRITORY_CODE

AND STYL.STATUS_FLAG='A') S1,

(SELECT STYL2.STYLE_FORMAT_CODE

FROM HZ_STYLE_FMT_LAYOUTS_B STYL2,

HZ_GEO_STRUCT_MAP M,

HZ_GEO_STRUCT_MAP_DTL MD

WHERE STYL2.ATTRIBUTE_CODE='POSTAL_CODE'

AND STYL2.VARIATION_NUMBER=1

AND STYL2.STATUS_FLAG='A'

AND M.COUNTRY_CODE='US'

AND M.LOC_TBL_NAME = 'HZ_LOCATIONS'

AND M.MAP_ID=MD.MAP_ID

AND MD.ENABLE_LOV_FLAG='Y'

AND MD.LOC_SEQ_NUM IS NOT NULL

AND MD.LOC_COMPONENT = 'POSTAL_CODE'

AND STYL2.STYLE_FORMAT_CODE=M.ADDRESS_STYLE) S2

WHERE S1.STYLE_FORMAT_CODE=S2.STYLE_FORMAT_CODE)

THEN 'Y'

ELSE

(SELECT 'Y' FROM HZ_GEOGRAPHIES HZGEO

WHERE HZGEO.GEOGRAPHY_ELEMENT4_ID=GEOGRAPHYPEO.GEOGRAPHY_ID

AND HZGEO.GEOGRAPHY_TYPE ='POSTAL_CODE'

AND TRUNC(SYSDATE) BETWEEN HZGEO.START_DATE AND HZGEO.END_DATE

AND ROWNUM = 1)

END)

)