Search Results pay_us_geo_update




Overview
Explore in AI Chat

Overview

PAY_US_GEO_UPDATE is a Payroll (PAY) module table residing in the HR schema of Oracle E-Business Suite, documented as VALID in both 12.1.1 and 12.2.2. Its stated purpose is to keep track of changes made to user data by the geocode patch — that is, it functions as an audit and reconciliation ledger for the U.S. payroll geocoding utility, which resolves employee address data into jurisdiction codes used for tax calculation and reporting. Rather than holding live payroll results, the table records the before-and-after state of each affected attribute so that DBAs and payroll administrators can verify, troubleshoot, or reverse the patch's effect on individual assignment and person records.

The ETRM relationship metadata classifies this table, on a heuristic basis, as satellite-leaning within a Data Vault style model. In that reading, PAY_US_GEO_UPDATE is best understood as a descriptive, change-oriented satellite attached to the payroll patch process, capturing attribute-level history rather than defining a business entity in its own right.

Key Information Stored

The table exposes 12 documented columns in the HR schema. The most significant are:

  • ID — the surrogate primary key and, per the foreign key metadata, the column that also participates in the relationship to PAY_PATCH_STATUS. It is the primary identifier for each audit row.
  • ASSIGNMENT_ID and PERSON_ID — the two principal business-key candidates, identifying the assignment and the person whose data was modified. These are the columns most commonly used to trace a change back to a specific employee.
  • TABLE_NAME and TABLE_VALUE_ID — identify which underlying table and which row within it were affected, making the audit generic across multiple geocode-affected sources.
  • OLD_JURI_CODE and NEW_JURI_CODE — the prior and resulting jurisdiction codes, the core evidence of what the geocode patch actually changed.
  • PROCESS_TYPE, PROCESS_MODE, and PROCESS_DATE — describe the nature of the run, the mode in which it executed, and when the change was applied.
  • STATUS and DESCRIPTION — the outcome of the change and any accompanying free-text explanation, supporting exception analysis.

The surrogate ID is distinct from the business identifiers (ASSIGNMENT_ID, PERSON_ID) used to locate real payroll entities; the latter are the natural candidates for a unique index on change records.

Common Use Cases and Queries

Typical uses include auditing a geocode patch run, verifying that jurisdiction codes changed as expected, and isolating exceptions where a change failed or produced an unexpected code. A common query pattern retrieves all changes for a person or assignment over a date range:

  • SELECT person_id, assignment_id, old_juri_code, new_juri_code, process_date, status FROM pay_us_geo_update WHERE person_id = :person_id ORDER BY process_date DESC;
  • Filtering by process mode or status to review failed changes: WHERE status = :status AND process_mode = :mode.
  • Joining to PAY_PATCH_STATUS on ID to correlate individual change rows with the parent patch run.

Related Objects

The documented foreign key establishes the primary relationship: PAY_US_GEO_UPDATE.ID references PAY_PATCH_STATUS, tying each change record to its originating patch status entry. Beyond that, PAY_US_GEO_UPDATE logically relates to the payroll entities it audits — principally PER_ALL_ASSIGNMENTS_F and PER_ALL_PEOPLE_F, keyed via ASSIGNMENT_ID and PERSON_ID, and to the geocode-affected tax and jurisdiction tables referenced by TABLE_NAME and TABLE_VALUE_ID. Together these objects form the audit trail surrounding the U.S. geocode patch process.