Search Results patch_name




Overview
Explore in AI Chat

Overview

PAY_US_GEOCODE_ENGINE_PKG is a United States–specific Oracle Payroll engine package whose primary responsibility is to drive the upgrade and reconciliation of geocode data used by US payroll tax processing. In Oracle EBS 12.1.1 and 12.2.2, the package orchestrates the migration and validation of validated city, county, and state geocode combinations that feed the US Payroll city tax rules. The single documented procedure, GEOCODE_UPGRADE, performs a historical upgrade of geocode records while tracking the state of each upgrade run through the PAY_PATCH_STATUS table, ensuring the upgrade is idempotent and can be resumed or re-run safely by release 12i administrators.

The package is notable for the way it derives a version label from the patch_name column. Both its driving cursors parse the four-character year segment following the last _1 or _2 delimiter inside a patch name, and only process records whose year is 2001 or later. This convention allows the engine to distinguish newer geocode data sets from legacy ones and to sequence them correctly, which is central to the upgrade logic rather than to routine geocoding at runtime.

Key Procedures and Functions

  • GEOCODE_UPGRADE — The package's only documented entry point. It accepts standard concurrent program errbuf and retcode OUT parameters together with a p_mode input that controls its behavior mode. The procedure validates which geocode patch names exist in PAY_PATCH_STATUS and PAY_US_MODIFIED_GEOCODES, checks their status, and verifies that the affected assignments actually exist before applying the upgrade. It uses internal cursors such as c_max_geocode_patch_name, c_geocode_patch_name, c_geocode_patch_status, c_geo_patch_comp_stat, c_geo_check_us_assignments, and c_geo_check_ca_assignments to reconcile patch metadata against active assignment records. Because the parameter list is fixed as (errbuf OUT, retcode OUT, p_mode IN), it is designed to be called from a concurrent program with the standard submission signature.

Tables Accessed

The engine reads and writes several payroll, HR, and core foundation tables through APPS synonyms:

  • PAY_PATCH_STATUS and PAY_PATCH_STATUS_S — track whether each geocode patch has been applied, providing the status that GEOCODE_UPGRADE inspects and updates.
  • PAY_US_MODIFIED_GEOCODES — the staging table holding the modified US geocode rows (patch_name, state_code, county_code, old_city_code) that are compared against actual data.
  • PAY_US_EMP_CITY_TAX_RULES_F and PAY_US_STATES — validated city tax rules and state reference data used to confirm that a geocode change maps to a legal jurisdiction.
  • PER_ALL_ASSIGNMENTS_F, PER_ADDRESSES, and PAY_ACTION_CONTEXTS — assignment and action-context records that the assignment-validation cursors join to determine which employees are affected.
  • HR_LOCATIONS_ALL, FF_CONTEXTS, FND_CONCURRENT_REQUESTS, and DUAL — supporting HR location data, fast-formula context data, concurrent request tracking, and a singleton query source.

Usage Notes

GEOCODE_UPGRADE is normally invoked as an Oracle Payroll concurrent program during a US geocode patch or upgrade cycle, most commonly as part of a patch driver script. The p_mode parameter allows the same routine to run in different operational modes (for example, audit or apply) without changing its signature, and the standard errbuf/retcode parameters let the concurrent manager capture success or failure. Because the procedure writes to PAY_PATCH_STATUS, it should not be scheduled casually; it is intended for controlled upgrade scenarios. The package is not referenced by any other documented package, so its exposure is limited to direct concurrent program invocation or custom upgrade scripts rather than to a broader public API surface within Oracle Payroll.