Search Results pay_us_city_school_dsts_fk1

Overview

The HR.PAY_US_CITY_SCHOOL_DSTS table is a critical reference data table within the Oracle E-Business Suite (EBS) Human Resources (HR) and Payroll modules, specifically for US localization. Its primary role is to maintain a master list of VERTEX-assigned school district geocodes that exist within specific cities. This table is essential for accurate payroll tax calculations and reporting, as school district codes are a key component for determining local tax jurisdictions for employees. It functions as a child table, providing detailed school district information that is linked to higher-level geographic entities defined within the system.

Key Information Stored

The table stores a composite identifier for each school district and its descriptive name. The structure is designed to enforce geographic hierarchy and data integrity through primary and foreign keys.

• SCHOOL_DST_CODE: The core VERTEX geocode that uniquely identifies the school district within the context of a specific city, county, and state.
• STATE_CODE, COUNTY_CODE, CITY_CODE: These columns form the geographic hierarchy and are foreign keys to the PAY_US_COUNTIES and PAY_US_CITY_GEOCODES tables. They define the precise location of the school district.
• SCHOOL_DST_NAME: The descriptive name of the school district (e.g., "Anytown School District").

The primary key constraint PAY_US_CITY_SCHOOL_DSTS_PK enforces uniqueness on the combination of SCHOOL_DST_CODE, STATE_CODE, COUNTY_CODE, and CITY_CODE, ensuring no duplicate district definitions exist for a given location.

Common Use Cases and Queries

This table is primarily accessed during payroll processing and for maintaining tax setup data. A common operational use case is validating and assigning the correct school district code to an employee's tax record based on their work or residence address. For reporting and data validation, queries often join this table with related geographic tables.

Sample Query 1: Retrieve all school districts for a specific city.

SELECT school_dst_code, school_dst_name
FROM hr.pay_us_city_school_dsts
WHERE state_code = 'CA'
  AND county_code = '001'
  AND city_code = 'SAN_FRAN'
ORDER BY school_dst_name;

Sample Query 2: Validate geographic hierarchy for a district code.

SELECT sd.school_dst_code, sd.school_dst_name,
       cg.city_name, co.county_name, co.state_code
FROM hr.pay_us_city_school_dsts sd,
     hr.pay_us_city_geocodes cg,
     hr.pay_us_counties co
WHERE sd.state_code = cg.state_code
  AND sd.county_code = cg.county_code
  AND sd.city_code = cg.city_code
  AND sd.state_code = co.state_code
  AND sd.county_code = co.county_code
  AND sd.school_dst_code = '12345';

Related Objects

The HR.PAY_US_CITY_SCHOOL_DSTS table is centrally positioned within the US geographic hierarchy in EBS and has defined relationships with other key reference tables.

• PAY_US_CITY_GEOCODES: The table is linked via a foreign key (PAY_US_CITY_SCHOOL_DSTS_FK1) on STATE_CODE, COUNTY_CODE, and CITY_CODE. This ensures every school district record is associated with a valid city geocode.
• PAY_US_COUNTIES: The STATE_CODE and COUNTY_CODE columns reference this table, maintaining referential integrity at the county level.
• Dependent Objects: While the provided metadata does not list specific application tables that reference PAY_US_CITY_SCHOOL_DSTS, it is logically referenced by payroll tax assignment and calculation engines, employee tax information forms (like the W-4), and potentially by localization data upload programs.