Search Results pay_us_modified_geocodes_pk

Overview

PAY_US_MODIFIED_GEOCODES is a payroll reference table owned by the HR schema within the Oracle E-Business Suite Payroll (PAY) module. It stores US city and county geocode information, specifically the VERTEX geocodes associated with US jurisdictions, and is used to display modified geocodes when the underlying tax geography data has been adjusted by a VERTEX or Oracle patch. VERTEX is the third-party provider of US tax jurisdiction and geocode data that Oracle Payroll relies upon for statutory tax calculations, so this table functions as a change-tracking and correction repository for city-level geocode assignments.

The heuristic Data Vault classification mined from the foreign key structure identifies this object as standalone. This modeling suggestion indicates that the table is a self-contained reference or correction lookup with no foreign key dependencies in the documented schema. In Data Vault terms it behaves more like a reference satellite recording descriptive attributes (old versus new geocode values, patch identifiers, and edition names) than a hub or link, and it is typically treated as a static lookup rather than a transactional entity.

Key Information Stored

The table is defined with ten documented columns. Its primary key index, PAY_US_MODIFIED_GEOCODES_PK, is documented as a composite unique key spanning CITY_NAME, STATE_CODE, COUNTY_CODE, NEW_CITY_CODE, OLD_CITY_CODE, PATCH_NAME, and ZD_EDITION_NAME, which collectively act as the business-key candidate for a geocode modification record. The most significant columns include:

• CITY_NAME and STATE_CODE — the US city and state to which the geocode modification applies.
• COUNTY_CODE and NEW_COUNTY_CODE — the original county identifier and the corrected county identifier produced by the patch or edition.
• OLD_CITY_CODE and NEW_CITY_CODE — the pre-modification and post-modification city geocode values, enabling before-and-after comparison.
• PRIMARY_FLAG — indicates whether the represented geocode is the primary jurisdiction for that city and county combination.
• PROCESS_TYPE — identifies the nature of the modification process applied to the geocode record.
• PATCH_NAME — the patch or data update under which the modified geocode was delivered.
• ZD_EDITION_NAME — the VERTEX data edition associated with the geocode values, used for version reconciliation across tax updates.

Common Use Cases and Queries

The primary use case is diagnostic: payroll and tax administrators query this table to determine why a particular city or county is being taxed under a changed geocode after a VERTEX patch or Oracle payroll update. It also supports reconciliation between the geo data shipped in different editions and audits of jurisdiction changes affecting employee tax withholding.

A representative query retrieving the modified geocode for a specific city and state is:

• SELECT city_name, state_code, county_code, old_city_code, new_city_code, primary_flag FROM pay_us_modified_geocodes WHERE city_name = :city AND state_code = :state;
• SELECT patch_name, zd_edition_name, process_type, COUNT(*) FROM pay_us_modified_geocodes GROUP BY patch_name, zd_edition_name, process_type ORDER BY patch_name;

These patterns support troubleshooting of tax calculation discrepancies, generation of geocode change reports, and impact analysis before applying a geocode update.

Related Objects

Although the mined relationship data classifies this table as standalone with no documented foreign keys, it operates alongside other VERTEX-derived payroll geocode objects in the HR and PAY schemas. Significant related objects include:

• PAY_US_CITY_GEOCODES — the authoritative US city geocode table whose values this object modifies.
• PAY_US_COUNTY_GEOCODES — the county-level geocode reference for the county and NEW_COUNTY_CODE values.
• PAY_US_STATE_GEOCODES — the state-level geocode reference keyed by STATE_CODE.
• PAY_US_TAX_GEOCODES and related VERTEX tax geography tables that consume corrected geocodes.
• PAY_US_EMPLOYEE_TAX_RULES and PAY_US_TAX_AREAS — downstream objects where modified geocodes influence employee tax jurisdiction.

Joins between PAY_US_MODIFIED_GEOCODES and PAY_US_CITY_GEOCODES are naturally performed on CITY_NAME, STATE_CODE, and COUNTY_CODE.