How to extract the City, County, State, Zip Combination Geo Codes from Vertex?

In this article, we will extract the City, County, State, Zip Combination Geo Code from vertex data.

As part of the implementation, the team should have ran the “Load Geographies for US” to load the geography details from Vertex. The loaded details will be used to validate the addresses loaded for the employees and the system won’t allow us to add any other combinations which are not present in vertex.

For more info on how to load the geographies, please check the Oracle Document Oracle Cloud Human Capital Management for the United States: Load Geographies for US and Load Payroll Tax Information for US (Doc ID 1582323.1)

SQL Query to get the information loaded from Vertex:

SELECT  country_code
       ,geography_element2 state
       ,geography_element3 county
       ,geography_element4 city
       ,geography_name zip
FROM    hz_geographies gwo
WHERE   geography_type = 'POSTAL_CODE'
AND     country_code = 'US'
AND     length (geography_element2) = 2
ORDER BY 1,2,3,4,5

Sample data will look like:

If you like the content, please follow us on LinkedIn, Facebook, and Twitter to get updated with the latest content.