1. Tables and Views for HCM
  2. PAY_AREA_DETAILS_V

PAY_AREA_DETAILS_V

Details

  • Schema: FUSION

  • Object owner: PAY

  • Object type: VIEW

Columns

Name

LANGUAGE_CODE

COUNTRY_CODE

AREA1

AREA2

AREA3

AREA4

AREA1_MEANING

AREA2_MEANING

AREA3_MEANING

AREA4_MEANING

LAST_UPDATE_DATE

Query

SQL_Statement

Select language_code,country_code,Area1,Area2,Area3,Area4

,max(Area1_Meaning) Area1_Meaning,max(Area2_Meaning) Area2_Meaning,

max(Area3_Meaning) Area3_Meaning,max(Area4_Meaning) Area4_Meaning,max(last_update_date) last_update_date

FROM

(

Select

language_code,country_code,

state_code Area1,county_code Area2, city_code Area3, school_code Area4

,state_meaning Area1_Meaning,county_meaning Area2_Meaning,city_meaning Area3_Meaning,school_meaning Area4_Meaning

,last_update_date from

(SELECT

code.language_code,

geo.country_code,

substr(code.identifier_value, 1,(instr(code.identifier_value, '-') - 1)) state_code,geo.geography_code state_meaning,

NULL county_code,NULL county_meaning, NULL city_code, NULL city_meaning, NULL school_code, NULL school_meaning,

trunc(code.last_update_date) last_update_date

FROM

HZ_GEOGRAPHY_IDENTIFIERS code,

HZ_GEOGRAPHIES geo

WHERE

code.identifier_subtype = 'GEO_CODE'

AND geo.geography_id = code.geography_id

AND geo.geography_type = 'STATE'

AND geo.geography_use = 'MASTER_REF'

AND code.geo_data_provider = 'VERTEX'

AND geo.country_code = 'US'

AND code.language_code = userenv('LANG')

UNION ALL

SELECT

hzgi.language_code,hzg.country_code,

substr(hzgi.identifier_value, 1, instr(hzgi.identifier_value, '-', 1, 1) - 1) state_code,

geo.geography_code state_meaning,

substr(hzgi.identifier_value, instr(hzgi.identifier_value, '-', 1, 1) + 1, instr(hzgi.identifier_value, '-', 1, 2) - 1 - instr(hzgi.identifier_value, '-', 1, 1)) county_code,

initcap(hzgiv.identifier_value) county_meaning,

NULL city_code,NULL city_meaning, NULL school_code,NULL school_meaning, trunc(hzgi.last_update_date) last_update_date

FROM

HZ_GEOGRAPHY_IDENTIFIERS code,

HZ_GEOGRAPHIES geo,

HZ_GEOGRAPHIES hzg,

HZ_GEOGRAPHY_IDENTIFIERS hzgi,

HZ_GEOGRAPHY_IDENTIFIERS hzgiv

WHERE

hzg.country_code = 'US'

AND hzg.geography_type = 'COUNTY'

AND hzg.geography_use = 'MASTER_REF'

AND hzg.geography_element1 = 'United States'

AND hzg.geography_element1_code = 'US'

AND hzg.geography_id = hzgi.geography_id

AND hzgi.identifier_type = 'CODE'

AND hzgi.identifier_subtype = 'GEO_CODE'

AND hzgi.primary_flag = 'N'

AND hzgi.geography_use = 'MASTER_REF'

AND hzgi.geography_type = 'COUNTY'

AND hzgi.language_code = userenv('LANG')

AND hzgi.geography_id = hzgiv.geography_id

AND hzgiv.identifier_type = 'NAME'

AND hzgiv.identifier_subtype = 'STANDARD_NAME'

AND hzgiv.primary_flag = 'Y'

AND hzgiv.geography_use = 'MASTER_REF'

AND hzgiv.geography_type = 'COUNTY'

AND hzgiv.language_code = userenv('LANG')

AND code.identifier_subtype = 'GEO_CODE'

AND geo.geography_id = code.geography_id

AND geo.geography_type = 'STATE'

AND geo.geography_use = 'MASTER_REF'

AND code.geo_data_provider = 'VERTEX'

AND geo.country_code = 'US'

AND substr(code.identifier_value,1,(instr(code.identifier_value,'-')-1)) = substr(hzgi.identifier_value,1,instr(hzgi.identifier_value,'-',1,1)- 1)

UNION ALL

SELECT

hzgicounty.language_code,hzg.country_code,

substr(hzgi.identifier_value, 1, instr(hzgi.identifier_value, '-', 1, 1) - 1) state_code,

geo.geography_code state_meaning,

substr(hzgi.identifier_value, instr(hzgi.identifier_value, '-', 1, 1) + 1, instr(hzgi.identifier_value, '-', 1, 2) - 1 - instr(hzgi.identifier_value, '-', 1, 1)) county_code,

initcap(hzgivcounty.identifier_value) county_meaning,

substr(hzgi.identifier_value, instr(hzgi.identifier_value, '-', 1, 2) + 1, length(hzgi.identifier_value) - instr(hzgi.identifier_value,'-', 1, 2)) city_code,

initcap(hzg.geography_name) city_Meaning,NULL school_code, NULL school_meaning,

trunc(hzgicounty.last_update_date) last_update_date

FROM

HZ_GEOGRAPHY_IDENTIFIERS code,

HZ_GEOGRAPHIES geo,

HZ_GEOGRAPHIES hzgcounty,

HZ_GEOGRAPHY_IDENTIFIERS hzgicounty,

HZ_GEOGRAPHY_IDENTIFIERS hzgivcounty,

HZ_GEOGRAPHIES hzg,

HZ_GEOGRAPHY_IDENTIFIERS hzgi,

HZ_GEOGRAPHY_IDENTIFIERS hzgivp

WHERE

hzg.country_code = 'US'

AND hzg.geography_type = 'CITY'

AND hzg.geography_use = 'MASTER_REF'

AND hzg.geography_element1 = 'United States'

AND hzg.geography_element1_code = 'US'

AND hzg.geography_id = hzgi.geography_id

AND hzgi.identifier_type = 'CODE'

AND hzgi.identifier_subtype = 'GEO_CODE'

AND hzgi.primary_flag = 'N'

AND hzgi.geography_use = 'MASTER_REF'

AND hzgi.geography_type = 'CITY'

AND hzgi.language_code = userenv('LANG')

AND hzgi.geography_id = hzgivp.geography_id

AND hzgivp.identifier_type = 'CODE'

AND hzgivp.identifier_subtype = 'PRIMARY_CITY'

AND hzgivp.geography_use = 'MASTER_REF'

AND hzgivp.geography_type = 'CITY'

AND code.identifier_subtype = 'GEO_CODE'

AND geo.geography_id = code.geography_id

AND geo.geography_type = 'STATE'

AND geo.geography_use = 'MASTER_REF'

AND code.geo_data_provider = 'VERTEX'

AND geo.country_code = 'US'

AND substr(code.identifier_value,1,(instr(code.identifier_value,'-')-1)) = substr(hzgi.identifier_value, 1, instr(hzgi.identifier_value, '-', 1, 1) - 1)

AND hzgcounty.country_code = 'US'

AND hzgcounty.geography_type = 'COUNTY'

AND hzgcounty.geography_use = 'MASTER_REF'

AND hzgcounty.geography_element1 = 'United States'

AND hzgcounty.geography_element1_code = 'US'

AND hzgcounty.geography_id = hzgicounty.geography_id

AND hzgicounty.identifier_type = 'CODE'

AND hzgicounty.identifier_subtype = 'GEO_CODE'

AND hzgicounty.primary_flag = 'N'

AND hzgicounty.geography_use = 'MASTER_REF'

AND hzgicounty.geography_type = 'COUNTY'

AND hzgicounty.language_code = userenv('LANG')

AND hzgicounty.geography_id = hzgivcounty.geography_id

AND hzgivcounty.identifier_type = 'NAME'

AND hzgivcounty.identifier_subtype = 'STANDARD_NAME'

AND hzgivcounty.primary_flag = 'Y'

AND hzgivcounty.geography_use = 'MASTER_REF'

AND hzgivcounty.geography_type = 'COUNTY'

AND hzgivcounty.language_code = userenv('LANG')

AND substr(hzgicounty.identifier_value, instr(hzgicounty.identifier_value, '-', 1, 1) + 1, instr(hzgicounty.identifier_value, '-', 1, 2) - 1 - instr(hzgicounty.identifier_value, '-', 1, 1))

=substr(hzgi.identifier_value, instr(hzgi.identifier_value, '-', 1, 1) + 1, instr(hzgi.identifier_value, '-', 1, 2) - 1 - instr(hzgi.identifier_value, '-', 1, 1))

AND substr(hzgicounty.identifier_value, 1, instr(hzgicounty.identifier_value, '-', 1, 1) - 1) = substr(hzgi.identifier_value, 1, instr(hzgi.identifier_value, '-', 1, 1) - 1)

UNION ALL

SELECT

hzgicounty.language_code,geo.country_code,to_char(pag.area1) state_code,geo.geography_code state_meaning,

to_char(pag.area2) county_code, initcap(hzgivcounty.identifier_value) county_meaning,

to_char(pag.area3) city_code,pag.geography_name city_Meaning,NULL school_code, NULL school_meaning,trunc(hzgicounty.last_update_date) last_update_date

FROM

HZ_GEOGRAPHY_IDENTIFIERS code,

HZ_GEOGRAPHIES geo,

HZ_GEOGRAPHIES hzgcounty,

HZ_GEOGRAPHY_IDENTIFIERS hzgicounty,

HZ_GEOGRAPHY_IDENTIFIERS hzgivcounty,

PAY_AMER_GEOGRAPHIES pag

WHERE pag.geography_type = 'TOWNSHIP'

AND pag.primary_flag = 'Y'

AND code.identifier_subtype = 'GEO_CODE'

AND geo.geography_id = code.geography_id

AND geo.geography_type = 'STATE'

AND geo.geography_use = 'MASTER_REF'

AND code.geo_data_provider = 'VERTEX'

AND geo.country_code = 'US'

AND substr(code.identifier_value,1,(instr(code.identifier_value,'-')-1)) = to_char(pag.area1)

AND hzgcounty.country_code = 'US'

AND hzgcounty.geography_type = 'COUNTY'

AND hzgcounty.geography_use = 'MASTER_REF'

AND hzgcounty.geography_element1 = 'United States'

AND hzgcounty.geography_element1_code = 'US'

AND hzgcounty.geography_id = hzgicounty.geography_id

AND hzgicounty.identifier_type = 'CODE'

AND hzgicounty.identifier_subtype = 'GEO_CODE'

AND hzgicounty.primary_flag = 'N'

AND hzgicounty.geography_use = 'MASTER_REF'

AND hzgicounty.geography_type = 'COUNTY'

AND hzgicounty.language_code = userenv('LANG')

AND hzgicounty.geography_id = hzgivcounty.geography_id

AND hzgivcounty.identifier_type = 'NAME'

AND hzgivcounty.identifier_subtype = 'STANDARD_NAME'

AND hzgivcounty.primary_flag = 'Y'

AND hzgivcounty.geography_use = 'MASTER_REF'

AND hzgivcounty.geography_type = 'COUNTY'

AND hzgivcounty.language_code = userenv('LANG')

AND substr(hzgicounty.identifier_value, instr(hzgicounty.identifier_value, '-', 1, 1) + 1, instr(hzgicounty.identifier_value, '-', 1, 2) - 1 - instr(hzgicounty.identifier_value, '-', 1, 1)) = to_char(pag.area2)

AND substr(hzgicounty.identifier_value, 1, instr(hzgicounty.identifier_value, '-', 1, 1) - 1) = to_char(pag.area1)

UNION ALL

SELECT DISTINCT

code.language_code,

geo.country_code,

substr(code.identifier_value, 1,(instr(code.identifier_value, '-') - 1)) state_code,geo.geography_code state_meaning,

NULL county_code,NULL county_meaning, NULL city_code, NULL city_meaning,

to_char(pag.area4) school_code, Initcap(pag.GEOGRAPHY_NAME) school_meaning,

trunc(code.last_update_date) last_update_date

FROM

HZ_GEOGRAPHY_IDENTIFIERS code,

HZ_GEOGRAPHIES geo,

PAY_AMER_GEOGRAPHIES pag

WHERE

code.identifier_subtype = 'GEO_CODE'

AND geo.geography_id = code.geography_id

AND geo.geography_type = 'STATE'

AND geo.geography_use = 'MASTER_REF'

AND code.geo_data_provider = 'VERTEX'

AND geo.country_code = 'US'

AND code.language_code = userenv('LANG')

AND substr(code.identifier_value, 1,(instr(code.identifier_value, '-') - 1)) = to_char(pag.area1)

AND pag.geography_type='SCHOOL_DISTRICT'

UNION ALL

SELECT

hzgicounty.language_code,geo.country_code,to_char(pag.area1) state_code,geo.geography_code state_meaning,

to_char(pag.area2) county_code, initcap(hzgivcounty.identifier_value) county_meaning,

to_char(pag.area3) city_code,Initcap(pag.geography_name) city_Meaning,to_char(pagsc.area4) school_code, InitCap(pagsc.GEOGRAPHY_NAME) school_meaning,

trunc(hzgicounty.last_update_date) last_update_date

FROM

HZ_GEOGRAPHY_IDENTIFIERS code,

HZ_GEOGRAPHIES geo,

HZ_GEOGRAPHIES hzgcounty,

HZ_GEOGRAPHY_IDENTIFIERS hzgicounty,

HZ_GEOGRAPHY_IDENTIFIERS hzgivcounty,

PAY_AMER_GEOGRAPHIES pag,

PAY_AMER_GEOGRAPHIES pagsc

WHERE pag.geography_type = 'TOWNSHIP'

AND pag.primary_flag = 'Y'

AND code.identifier_subtype = 'GEO_CODE'

AND geo.geography_id = code.geography_id

AND geo.geography_type = 'STATE'

AND geo.geography_use = 'MASTER_REF'

AND code.geo_data_provider = 'VERTEX'

AND geo.country_code = 'US'

AND substr(code.identifier_value,1,(instr(code.identifier_value,'-')-1)) = to_char(pag.area1)

AND hzgcounty.country_code = 'US'

AND hzgcounty.geography_type = 'COUNTY'

AND hzgcounty.geography_use = 'MASTER_REF'

AND hzgcounty.geography_element1 = 'United States'

AND hzgcounty.geography_element1_code = 'US'

AND hzgcounty.geography_id = hzgicounty.geography_id

AND hzgicounty.identifier_type = 'CODE'

AND hzgicounty.identifier_subtype = 'GEO_CODE'

AND hzgicounty.primary_flag = 'N'

AND hzgicounty.geography_use = 'MASTER_REF'

AND hzgicounty.geography_type = 'COUNTY'

AND hzgicounty.language_code = userenv('LANG')

AND hzgicounty.geography_id = hzgivcounty.geography_id

AND hzgivcounty.identifier_type = 'NAME'

AND hzgivcounty.identifier_subtype = 'STANDARD_NAME'

AND hzgivcounty.primary_flag = 'Y'

AND hzgivcounty.geography_use = 'MASTER_REF'

AND hzgivcounty.geography_type = 'COUNTY'

AND hzgivcounty.language_code = userenv('LANG')

AND substr(hzgicounty.identifier_value, instr(hzgicounty.identifier_value, '-', 1, 1) + 1, instr(hzgicounty.identifier_value, '-', 1, 2) - 1 - instr(hzgicounty.identifier_value, '-', 1, 1)) = to_char(pag.area2)

AND substr(hzgicounty.identifier_value, 1, instr(hzgicounty.identifier_value, '-', 1, 1) - 1) = to_char(pag.area1)

AND pagsc.geography_type='SCHOOL_DISTRICT'

AND to_char(pag.area1) = to_char(pagsc.area1)

AND to_char(pag.area2) = to_char(pagsc.area2)

AND to_char(pag.area3) = to_char(pagsc.area3)

UNION ALL

SELECT

hzgicounty.language_code,hzg.country_code,

substr(hzgi.identifier_value, 1, instr(hzgi.identifier_value, '-', 1, 1) - 1) state_code,

geo.geography_code state_meaning,

substr(hzgi.identifier_value, instr(hzgi.identifier_value, '-', 1, 1) + 1, instr(hzgi.identifier_value, '-', 1, 2) - 1 - instr(hzgi.identifier_value, '-', 1, 1)) county_code,

initcap(hzgivcounty.identifier_value) county_meaning,

substr(hzgi.identifier_value, instr(hzgi.identifier_value, '-', 1, 2) + 1, length(hzgi.identifier_value) - instr(hzgi.identifier_value,'-', 1, 2)) city_code,

initcap(hzg.geography_name) city_Meaning,

to_char(pag.area4) school_code, InitCap(pag.GEOGRAPHY_NAME) school_meaning,

trunc(hzgicounty.last_update_date) last_update_date

FROM

HZ_GEOGRAPHY_IDENTIFIERS code,

HZ_GEOGRAPHIES geo,

HZ_GEOGRAPHIES hzgcounty,

HZ_GEOGRAPHY_IDENTIFIERS hzgicounty,

HZ_GEOGRAPHY_IDENTIFIERS hzgivcounty,

HZ_GEOGRAPHIES hzg,

HZ_GEOGRAPHY_IDENTIFIERS hzgi,

HZ_GEOGRAPHY_IDENTIFIERS hzgivp,

PAY_AMER_GEOGRAPHIES pag

WHERE

hzg.country_code = 'US'

AND hzg.geography_type = 'CITY'

AND hzg.geography_use = 'MASTER_REF'

AND hzg.geography_element1 = 'United States'

AND hzg.geography_element1_code = 'US'

AND hzg.geography_id = hzgi.geography_id

AND hzgi.identifier_type = 'CODE'

AND hzgi.identifier_subtype = 'GEO_CODE'

AND hzgi.primary_flag = 'N'

AND hzgi.geography_use = 'MASTER_REF'

AND hzgi.geography_type = 'CITY'

AND hzgi.language_code = userenv('LANG')

AND hzgi.geography_id = hzgivp.geography_id

AND hzgivp.identifier_type = 'CODE'

AND hzgivp.identifier_subtype = 'PRIMARY_CITY'

AND hzgivp.geography_use = 'MASTER_REF'

AND hzgivp.geography_type = 'CITY'

AND code.identifier_subtype = 'GEO_CODE'

AND geo.geography_id = code.geography_id

AND geo.geography_type = 'STATE'

AND geo.geography_use = 'MASTER_REF'

AND code.geo_data_provider = 'VERTEX'

AND geo.country_code = 'US'

AND substr(code.identifier_value,1,(instr(code.identifier_value,'-')-1)) = substr(hzgi.identifier_value, 1, instr(hzgi.identifier_value, '-', 1, 1) - 1)

AND hzgcounty.country_code = 'US'

AND hzgcounty.geography_type = 'COUNTY'

AND hzgcounty.geography_use = 'MASTER_REF'

AND hzgcounty.geography_element1 = 'United States'

AND hzgcounty.geography_element1_code = 'US'

AND hzgcounty.geography_id = hzgicounty.geography_id

AND hzgicounty.identifier_type = 'CODE'

AND hzgicounty.identifier_subtype = 'GEO_CODE'

AND hzgicounty.primary_flag = 'N'

AND hzgicounty.geography_use = 'MASTER_REF'

AND hzgicounty.geography_type = 'COUNTY'

AND hzgicounty.language_code = userenv('LANG')

AND hzgicounty.geography_id = hzgivcounty.geography_id

AND hzgivcounty.identifier_type = 'NAME'

AND hzgivcounty.identifier_subtype = 'STANDARD_NAME'

AND hzgivcounty.primary_flag = 'Y'

AND hzgivcounty.geography_use = 'MASTER_REF'

AND hzgivcounty.geography_type = 'COUNTY'

AND hzgivcounty.language_code = userenv('LANG')

AND substr(hzgicounty.identifier_value, instr(hzgicounty.identifier_value, '-', 1, 1) + 1, instr(hzgicounty.identifier_value, '-', 1, 2) - 1 - instr(hzgicounty.identifier_value, '-', 1, 1))

=substr(hzgi.identifier_value, instr(hzgi.identifier_value, '-', 1, 1) + 1, instr(hzgi.identifier_value, '-', 1, 2) - 1 - instr(hzgi.identifier_value, '-', 1, 1))

AND substr(hzgicounty.identifier_value, 1, instr(hzgicounty.identifier_value, '-', 1, 1) - 1) = substr(hzgi.identifier_value, 1, instr(hzgi.identifier_value, '-', 1, 1) - 1)

AND to_char(pag.area1) = substr(hzgi.identifier_value, 1, instr(hzgi.identifier_value, '-', 1, 1) - 1)

AND to_char(pag.area2) = substr(hzgi.identifier_value, instr(hzgi.identifier_value, '-', 1, 1) + 1, instr(hzgi.identifier_value, '-', 1, 2) - 1 - instr(hzgi.identifier_value, '-', 1, 1))

AND to_char(pag.area3) = substr(hzgi.identifier_value, instr(hzgi.identifier_value, '-', 1, 2) + 1, length(hzgi.identifier_value) - instr(hzgi.identifier_value,'-', 1, 2))

AND pag.geography_type='SCHOOL_DISTRICT'

)

UNION ALL

select hzgi.language_code,'MX' country_code,

to_char(hzgi.identifier_value) Area1,null Area2, null Area3, null Area4,

hzgiv.identifier_value Area1_Meaning,null Area2_Meaning,null Area3_Meaning,null Area4_Meaning,

trunc(hzgi.last_update_date) last_update_date

from

HZ_GEOGRAPHIES hzg ,

HZ_GEOGRAPHY_IDENTIFIERS hzgi,

HZ_GEOGRAPHY_IDENTIFIERS hzgiv

where hzg.country_code='MX'

and hzg.geography_type='STATE'

and hzg.geography_use='MASTER_REF'

and hzg.geography_element1='Mexico'

and hzg.geography_element1_code='MX'

and hzg.geography_id=hzgi.geography_id

and hzgi.identifier_type='CODE'

and hzgi.identifier_subtype='GEO_CODE'

and hzgi.primary_flag='N'

and hzgi.geography_use='MASTER_REF'

and hzgi.geography_type='STATE'

and hzgi.language_code in ('E', 'US')

and hzgi.language_code = userenv('LANG')

and hzgi.geography_id= hzgiv.geography_id

and hzgiv.identifier_type='NAME'

and hzgiv.identifier_subtype='STANDARD_NAME'

and hzgiv.primary_flag='Y'

and hzgiv.geography_use='MASTER_REF'

and hzgiv.geography_type='STATE'

and hzgiv.language_code in ('E', 'US')

UNION ALL

select /*+ USE_NL(C G I T) */

Distinct

i.language_code,

'CN' country_code,to_char(g.geography_id) Area1,null Area2, null Area3, null Area4,

to_char(nvl(i.identifier_value, g.geography_name)) Area1_Meaning,

null Area2_Meaning,null Area3_Meaning,null Area4_Meaning,trunc(i.last_update_date) last_update_date

from HZ_GEOGRAPHIES c,

HZ_GEOGRAPHIES g,

HZ_GEOGRAPHY_IDENTIFIERS i,

HZ_GEOGRAPHY_TYPES_TL t

where c.geography_code = 'CN'

and c.geography_type = 'COUNTRY'

and c.geography_use = 'MASTER_REF'

and g.geography_element1_id = c.geography_id

and g.geography_use = 'MASTER_REF'

and g.geography_type <> 'POSTAL_CODE'

and i.geography_id(+) = g.geography_id

and i.identifier_type(+) = 'NAME'

and i.identifier_subtype(+) = 'STANDARD_NAME'

and i.language_code(+) = userenv('LANG')

and t.geography_type = g.geography_type

and t.language = userenv('LANG')

UNION ALL

(SELECT

fr.language_code,

'CA' country_code,

substr(geo_code.IDENTIFIER_VALUE,instr(geo_code.IDENTIFIER_VALUE,'-',1)+1,(instr(geo_code.IDENTIFIER_VALUE,'-',-1)-(instr(geo_code.IDENTIFIER_VALUE,'-',1)))-1) Area1,

null Area2, null Area3, null Area4,

nvl(fr.IDENTIFIER_VALUE,us.IDENTIFIER_VALUE) Area1_Meaning,

null Area2_Meaning,null Area3_Meaning,null Area4_Meaning,trunc(fr.last_update_date) last_update_date

FROM HZ_GEOGRAPHY_IDENTIFIERS us,

HZ_GEOGRAPHY_IDENTIFIERS fr,

HZ_GEOGRAPHY_IDENTIFIERS geo_code

WHERE us.GEOGRAPHY_ID=fr.GEOGRAPHY_ID (+)

AND geo_code.GEOGRAPHY_ID = us.GEOGRAPHY_ID

AND us.GEOGRAPHY_ID IN (select GEOGRAPHY_ID from HZ_GEOGRAPHIES

where COUNTRY_CODE = 'CA'

AND GEOGRAPHY_TYPE = 'PROVINCE'

AND GEOGRAPHY_USE = 'MASTER_REF')

AND fr.IDENTIFIER_SUBTYPE(+) = 'STANDARD_NAME'

AND fr.IDENTIFIER_TYPE(+) = 'NAME'

AND us.IDENTIFIER_SUBTYPE = 'STANDARD_NAME'

AND us.IDENTIFIER_TYPE = 'NAME'

AND us.LANGUAGE_CODE = 'US'

AND fr.LANGUAGE_CODE(+) = USERENV('LANG')

AND fr.PRIMARY_FLAG(+) = 'Y'

AND us.PRIMARY_FLAG = 'Y'

AND geo_code.IDENTIFIER_TYPE = 'CODE'

AND geo_code.GEOGRAPHY_USE = 'MASTER_REF'

AND geo_code.IDENTIFIER_SUBTYPE = 'GEO_CODE'

AND geo_code.GEOGRAPHY_TYPE = 'PROVINCE'

UNION ALL

SELECT 'US' language_code,

'CA' country_code,

to_char(area2) Area1,null Area2, null Area3, null Area4,

geography_name Area1_Meaning,null Area2_Meaning,null Area3_Meaning,null Area4_Meaning,

trunc(last_update_date) last_update_date

FROM PAY_AMER_GEOGRAPHIES

WHERE geography_type ='INTERNATIONAL'

AND legislation_code ='CA'

AND 'US' = USERENV('LANG'))

)

group by language_code,country_code,Area1,Area2,Area3,Area4