Results for “paybv_us_zip_codes_v”

15 results




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

Overview

PAYBV_US_ZIP_CODES_V is a read-only database view owned by the APPS schema within the PAY (Payroll) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. The view exposes United States postal ZIP code reference data that supports payroll processing, tax jurisdiction determination, and address validation routines. It is documented in ETRM with a status of VALID and is implemented strictly as a projection view, defined with the WITH READ ONLY clause to guarantee that no DML can be issued against it. Its naming convention (the _V suffix) confirms its role as a reporting and integration-facing object rather than a transactional entity.

The view is characteristically narrow in scope: it does not derive, aggregate, or transform data. Instead, it presents a filtered column set from a single base synonym, making it a lightweight, stable access point for ZIP-code-driven logic in reports, concurrent programs, and custom extensions.

Underlying Base Objects

The view is defined over a single referenced base object: the synonym PAY_US_ZIP_CODES, which resolves to the underlying Payroll ZIP code table (internally aliased as PUZC in the view text). The view definition explicitly selects five columns from that source and applies WITH READ ONLY:

Because the view is a direct, one-to-one projection with no joins, aggregation, or filtering predicates, it inherits the row granularity of the base table. Each row in the view corresponds to a ZIP code range record for a given state, county, and city combination. The absence of joins also means the view carries no performance overhead beyond synonym resolution, and it remains free of the integrity risks associated with multi-table views. Any change to the underlying table's structure will propagate directly to the view.

Key Columns

The view exposes the following five columns, each representing a component of U.S. postal geography used in payroll and tax processing:

  • ZIP_START — The low end of a ZIP code range. This is the column referenced by users searching for "zip_end" in a complementary capacity, as it forms the lower bound of the same range.
  • ZIP_END — The high end of the ZIP code range. Together with ZIP_START, it defines the inclusive span of postal codes covered by the record. This is the primary term in the user's search.
  • STATE_CODE — The state abbreviation associated with the ZIP range, used for state-level tax jurisdiction and reporting determination.
  • COUNTY_CODE — The county identifier, significant for county-level tax rates and local jurisdiction reporting.
  • CITY_CODE — The city code linked to the postal range, supporting municipal tax and address-level payroll validation.

Because the view is read only, these columns serve exclusively for query and lookup purposes.

Common Use Cases and Queries

This view is typically used for ZIP-to-jurisdiction lookups, address validation, and payroll tax reporting. A common query resolves a specific ZIP code to its state, county, and city by testing against the range boundaries:

  • SELECT zip_start, zip_end, state_code, county_code, city_code FROM apps.paybv_us_zip_codes_v WHERE :p_zip BETWEEN zip_start AND zip_end;
  • SELECT * FROM apps.paybv_us_zip_codes_v WHERE zip_end = :p_zip_end AND state_code = :p_state;
  • SELECT DISTINCT state_code, county_code FROM apps.paybv_us_zip_codes_v WHERE zip_start <= :p_zip AND zip_end >= :p_zip;

Typical scenarios include validating employee addresses during payroll entry, deriving withholding jurisdiction for a given postal range, and feeding tax reporting extracts. Because the object is read only, no INSERT, UPDATE, or DELETE statements are permitted; consumers requiring maintenance of ZIP data must operate against the base table itself under appropriate privileges.