TCA Basic Queries: Country, State, Zip

 Introduction:

Basic SQLs to get list of Countries, States and Zips (Postal Codes) from Oracle EBS TCA (Trading Community Architecture) Setup.

 

SQLs:

List of Countries:

-- get all countries
select * from FND_TERRITORIES;
 

 

List of City / State / Zip Combinations:

-- get all state/zip/city combinations
SELECT GEOGRAPHY_ELEMENT4 CITY, GEOGRAPHY_ELEMENT3 COUNTY,
    GEOGRAPHY_ELEMENT2_CODE STATE, GEOGRAPHY_ELEMENT5 ZIP_CODE,
    GEOGRAPHY_ELEMENT1_CODE COUNTRY
FROM HZ_GEOGRAPHIES
WHERE GEOGRAPHY_ELEMENT5_ID = GEOGRAPHY_ID AND GEOGRAPHY_ELEMENT1_CODE = 'US';

 

Note: Based on Address Validation profile option, the system will validate and give error, if the City / State / Zip Combination mismatch while address input.

 

List of State Codes (Eg: AL, NY, MO, IL, etc):

-- get all state codes
SELECT DISTINCT GEOGRAPHY_ELEMENT2_CODE STATE
FROM HZ_GEOGRAPHIES
WHERE GEOGRAPHY_ELEMENT5_ID = GEOGRAPHY_ID AND GEOGRAPHY_ELEMENT1_CODE = 'US';

 

Keywords:

TCA, Customer Address, Bill To Address, Ship To Address, Trading Community Architecture, FND_TERRITORIES, HZ_GEOGRAPHIES, HZ_PARTIES, HZ_PARTY_SITES, HZ_LOCATIONS, EBS, R12, Query, SQL, SQLPLUS