Results for “pay_us_city_pkg”

32 results




AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PAY_US_CITY_PKG is a PL/SQL package owned by the APPS schema in Oracle E-Business Suite and classified under the ETRM metadata as an OTHER API. It encapsulates the maintenance and validation logic for United States city data used throughout Oracle Payroll and related HRMS modules. Because Payroll processing, tax reporting, and address validation in the US legislative environment depend on accurate city, county, state, and ZIP code relationships, this package provides the programmatic foundation for inserting, updating, locking, and deleting city records while also performing geographic consistency checks against the address tables. The package is documented as VALID in both 12.1.1 and 12.2.2, indicating continuity of the object across release boundaries. Its dependency footprint (SYS.STANDARD) is intentionally minimal, with functional dependencies resolved through APPS synonyms rather than direct schema references, consistent with standard EBS API design.

Key Procedures and Functions

  • INSERT_ROW — Creates a new city record in the US city tables, applying the standard validation and defaulting logic required for a valid geographic entry.
  • LOCK_ROW — Acquires a row-level lock on an existing city record so that concurrent updates cannot corrupt the data during maintenance operations.
  • UPDATE_ROW — Modifies an existing city record, preserving referential integrity with county, state, and ZIP code data.
  • DELETE_ROW — Removes a city record, typically after the caller has verified that no dependent references remain.
  • CHK_CITY_IN_ADDR — Validates whether a supplied city value is consistent with, or present within, the address data held in PER_ADDRESSES; used as a cross-reference rule during address verification.
  • CREATE_NEW_GEOCODE — Generates a new geocode entry associated with a city, supporting downstream geography identification.
  • CREATE_UNKN_CITY — Creates a placeholder or "unknown" city record, which allows payroll and address processing to proceed when a definitive city cannot be determined.

Seven procedures are documented in total. Parameter lists are not defined in the ETRM metadata and are therefore not reproduced here.

Tables Accessed

  • PAY_US_CITY_GEOCODES — Stores geocode assignments for US cities; written by CREATE_NEW_GEOCODE.
  • PAY_US_CITY_NAMES — Master list of US city names; the primary target of INSERT_ROW, UPDATE_ROW, and DELETE_ROW.
  • PAY_US_COUNTIES — County reference data used to validate city-to-county relationships.
  • PAY_US_STATES — State reference data supporting state/city consistency checks.
  • PAY_US_ZIP_CODES — ZIP code reference data used to correlate cities with postal codes.
  • PER_ADDRESSES — The HRMS address table; read by CHK_CITY_IN_ADDR to confirm that a city value matches recorded addresses.
  • DUAL — Used for single-row PL/SQL evaluations and sequence or constant retrieval.

All tables are referenced through APPS synonyms, which is the standard pattern for seeded EBS APIs.

Usage Notes

PAY_US_CITY_PKG is not referenced by any other documented package, so it is typically invoked directly from Oracle Forms, concurrent programs, or custom PL/SQL extensions rather than through a higher-level API chain. It is most relevant during US Payroll implementation and maintenance activities, address validation routines, and any customization that must keep city, county, state, and ZIP code data synchronized. The presence of LOCK_ROW indicates that callers are expected to follow a lock-then-update pattern, and DELETE_ROW should be called only after dependent geocode and address references have been reconciled. Because the package is documented as VALID, it is safe to reference in custom code, though Oracle recommends using the documented procedures rather than direct DML against the underlying PAY_US tables. In 12.2.2 the object remains part of the standard APPS schema and should be treated as a supported, non-public API for geographic maintenance.