Results for “pay_us_county_school_dsts”

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

Overview

The PAY_US_COUNTY_SCHOOL_DSTS table is a reference data object owned by the HR schema and delivered as part of the Oracle E-Business Suite Payroll (PAY) product family. Its documented purpose is to store VERTEX geocodes for school districts that span more than one county. In the United States, school district taxation and reporting authorities are frequently divided along county boundaries, and a single school district may collect or remit taxes across several counties. Oracle Payroll relies on VERTEX-supplied geocode reference data to resolve the correct school district for a given employee address so that the appropriate local tax rules are applied during payroll processing.

Because this object is populated from a third-party geocode source and is not transaction-generating, its heuristic Data Vault classification is satellite-leaning. In Data Vault modeling terms, this means the table behaves as a descriptive satellite attached to a hub/link combination of school district, state, and county — it holds attribute context (the school district name and geocode edition) rather than functioning as an independent business hub or an associative link. The modeling suggestion is to treat the composite of school district, state, and county as the driving key, with the remaining descriptive columns as satellite payload.

Key Information Stored

The documented physical schema in ETRM 12.2.2 consists of five columns. The most significant are:

• SCHOOL_DST_CODE — The VERTEX-assigned code identifying the school district. This is a leading component of the primary key.
• STATE_CODE — The state in which the school district and county reside. Also a primary key component and a foreign key to PAY_US_COUNTIES.
• COUNTY_CODE — The county within the state that the school district spans. This is the third primary key component and the second foreign key column to PAY_US_COUNTIES.
• SCHOOL_DST_NAME — The descriptive name of the school district, used for reporting and display.
• ZD_EDITION_NAME — The VERTEX geocode edition (version) identifier. This column appears in the unique index PAY_US_COUNTY_SCHOOL_DSTS_PK as the fourth component, supporting the versioned nature of the geocode data.

The surrogate primary key is PAY_US_COUNTY_SCHOOL_DSTS_PK, defined across SCHOOL_DST_CODE, STATE_CODE, COUNTY_CODE, and ZD_EDITION_NAME. The combination of SCHOOL_DST_CODE, STATE_CODE, and COUNTY_CODE constitutes the business-key candidate that ties a school district to each county it spans. Because ZD_EDITION_NAME is part of the unique index, multiple editions of the same district-county relationship can coexist, allowing VERTEX geocode updates to be tracked without overwriting prior reference data.

Common Use Cases and Queries

The primary use case is payroll tax determination. When an employee's home or work address falls within a school district that crosses county lines, Oracle Payroll joins this table to resolve the correct school district code and name for the applicable state and county. A typical query pattern is:

• Listing all school districts within a state, filtered by COUNTY_CODE, ordered by SCHOOL_DST_NAME.
• Joining to PAY_US_COUNTIES on STATE_CODE and COUNTY_CODE to validate that the county is recognized for the district.
• Filtering by a specific ZD_EDITION_NAME to isolate the currently active geocode edition for tax reporting periods.
• Reconciling school district tax withholdings by aggregating employee-level payroll results and mapping them through SCHOOL_DST_CODE.

Reporting use cases include generating school district tax liability summaries by state and county, auditing geocode edition changes across payroll periods, and validating that address data on employee records resolves to a valid school district in multi-county scenarios.

Related Objects

The following objects are most significant in relation to this table, grounded in the documented foreign key relationship:

• PAY_US_COUNTIES — Referenced via STATE_CODE and COUNTY_CODE. This is the primary parent table validating that a school district's state and county combination exists.
• PAY_US_SCHOOL_DISTRICTS — The companion reference table holding school district master data that this table supplements for multi-county districts.
• PAY_US_CITY_SCHOOL_DSTS — A parallel VERTEX geocode table for city districts that behave analogously.
• PAY_US_STATES — Provides state-level reference data validating STATE_CODE.
• PAY_US_ZIP_CODES — Supports address-to-geocode resolution feeding the school district determination logic.
• HR_LOCATIONS and employee address tables — Consumed during payroll tax processing to match addresses against these geocodes.
• PAY_US_TAX_RULES / tax rule APIs — Downstream consumers that apply the correct school district tax once the geocode is resolved.

Together these objects form the VERTEX-driven geocode layer that enables accurate US school district tax determination in Oracle EBS Payroll 12.1.1 and 12.2.2.