VIEW: APPS.PAY_US_TAX_LOCALITY_NAMES_V

Source

View Text - Preformatted

SELECT pus.state_abbrev ,pus.state_code ,puc.county_name ,NULL school_district_name ,NULL city_name ,pus.state_code||'-'||puc.county_code || '-0000' jurisdiction_code ,'COUNTY' locality_type from pay_us_counties puc, pay_us_states pus where pus.state_code = puc.state_code and exists (select 1 from pay_us_county_tax_info_f puct where puct.jurisdiction_code = pus.state_code||'-'||puc.county_code || '-0000' and (nvl(puct.head_tax, 'N') = 'Y' or nvl(puct.county_tax,' N') = 'Y')) union all select pus.state_abbrev ,pus.state_code ,puc.county_name ,NULL ,pucn.city_name City ,pus.state_code ||'-'||puc.county_code ||'-' || pucn.city_code ,'CITY' locality_type from pay_us_states pus, pay_us_counties puc, pay_us_city_names pucn where pus.state_code = puc.state_code and pus.state_code = pucn.state_code and pucn.county_code = puc.county_code and pus.state_code < 60 and exists (select 1 from pay_us_city_tax_info_f pucn where pucn.jurisdiction_code = pus.state_code ||'-'||puc.county_code ||'-' || pucn.city_code and (nvl(pucn.head_tax, 'N') = 'Y' or nvl(pucn.city_tax,' N') = 'Y')) union all select distinct pus.state_abbrev state ,pus.state_code ,NULL ,psd.school_dst_name ,NULL ,pus.state_code || '-' || psd.school_dst_code ,'SCHOOL_DISTRICT' from pay_us_school_dsts psd, pay_us_states pus where psd.state_code = pus.state_code

View Text - HTML Formatted

SELECT PUS.STATE_ABBREV
, PUS.STATE_CODE
, PUC.COUNTY_NAME
, NULL SCHOOL_DISTRICT_NAME
, NULL CITY_NAME
, PUS.STATE_CODE||'-'||PUC.COUNTY_CODE || '-0000' JURISDICTION_CODE
, 'COUNTY' LOCALITY_TYPE
FROM PAY_US_COUNTIES PUC
, PAY_US_STATES PUS
WHERE PUS.STATE_CODE = PUC.STATE_CODE
AND EXISTS (SELECT 1
FROM PAY_US_COUNTY_TAX_INFO_F PUCT
WHERE PUCT.JURISDICTION_CODE = PUS.STATE_CODE||'-'||PUC.COUNTY_CODE || '-0000'
AND (NVL(PUCT.HEAD_TAX
, 'N') = 'Y' OR NVL(PUCT.COUNTY_TAX
, ' N') = 'Y')) UNION ALL SELECT PUS.STATE_ABBREV
, PUS.STATE_CODE
, PUC.COUNTY_NAME
, NULL
, PUCN.CITY_NAME CITY
, PUS.STATE_CODE ||'-'||PUC.COUNTY_CODE ||'-' || PUCN.CITY_CODE
, 'CITY' LOCALITY_TYPE
FROM PAY_US_STATES PUS
, PAY_US_COUNTIES PUC
, PAY_US_CITY_NAMES PUCN
WHERE PUS.STATE_CODE = PUC.STATE_CODE
AND PUS.STATE_CODE = PUCN.STATE_CODE
AND PUCN.COUNTY_CODE = PUC.COUNTY_CODE
AND PUS.STATE_CODE < 60
AND EXISTS (SELECT 1
FROM PAY_US_CITY_TAX_INFO_F PUCN
WHERE PUCN.JURISDICTION_CODE = PUS.STATE_CODE ||'-'||PUC.COUNTY_CODE ||'-' || PUCN.CITY_CODE
AND (NVL(PUCN.HEAD_TAX
, 'N') = 'Y' OR NVL(PUCN.CITY_TAX
, ' N') = 'Y')) UNION ALL SELECT DISTINCT PUS.STATE_ABBREV STATE
, PUS.STATE_CODE
, NULL
, PSD.SCHOOL_DST_NAME
, NULL
, PUS.STATE_CODE || '-' || PSD.SCHOOL_DST_CODE
, 'SCHOOL_DISTRICT'
FROM PAY_US_SCHOOL_DSTS PSD
, PAY_US_STATES PUS
WHERE PSD.STATE_CODE = PUS.STATE_CODE