Results for “filing_status_code”

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

Overview

HR_H2PI_CITY_TAX_RULES is a staging table in the HR schema belonging to the PER (Human Resources) product family within Oracle E-Business Suite 12.1.1 and 12.2.2. It supports the Oracle HR to Oracle Payroll Interface (H2PI), which moves employee tax and assignment data collected in HRMS into Oracle Payroll for processing. Specifically, this table stores United States employee city tax rules while a data upload is in progress, acting as an intermediate repository between the source data load and the final payroll tax records.

Because the table is a transient staging structure, rows are typically inserted during a batch upload, validated or transformed by the H2PI process, and then purged once the corresponding payroll tax rule records are created. The documented schema contains 24 columns. The metadata classifies this object heuristically as a standalone Data Vault model — meaning it does not resolve cleanly to a hub, link, or satellite. In practice, this pattern is common for staging tables, which combine descriptive attributes and business identifiers in a single physical structure rather than normalizing them across hub and satellite entities.

Key Information Stored

The primary key, HR_H2PI_CITY_TAX_RULES_PK, is defined across five columns: EMP_CITY_TAX_RULE_ID, CLIENT_ID, EFFECTIVE_START_DATE, BUSINESS_GROUP_ID, and EFFECTIVE_END_DATE. EMP_CITY_TAX_RULE_ID (the column the user searched for) is the surrogate identifier for each city tax rule row; combined with CLIENT_ID and the effective-dating columns, it forms the uniqueness constraint.

• EMP_CITY_TAX_RULE_ID — surrogate unique identifier for the staged city tax rule record.
• PERSON_ID — the employee to whom the city tax rule applies; the main business-key candidate linking to PER_ALL_PEOPLE_F.
• ASSIGNMENT_ID — the assignment context for the rule, tying the tax rule to a specific employment relationship.
• BUSINESS_GROUP_ID — the HR business group owning the record; part of the primary key.
• CLIENT_ID — client/tenant discriminator; part of the primary key.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-effective bounds of the rule; part of the primary key.
• STATE_CODE, COUNTY_CODE, CITY_CODE, JURISDICTION_CODE — geographic tax jurisdiction identifiers used to determine the applicable city tax.
• SCHOOL_DISTRICT_CODE — school district for jurisdictions that levy such taxes.
• FILING_STATUS_CODE — employee's filing status, affecting withholding calculation.
• WITHHOLDING_ALLOWANCES — number of allowances claimed for withholding purposes.
• ADDITIONAL_WA_RATE, LIT_OVERRIDE_RATE, LIT_OVERRIDE_AMOUNT — rate and amount overrides for additional and local income tax withholding.
• LIT_EXEMPT, SD_EXEMPT, HT_EXEMPT — exemption flags for local income tax, school district tax, and head tax.
• STATUS, OBJECT_VERSION_NUMBER, LAST_UPD_DATE — row status, optimistic-locking version, and audit timing.

Common Use Cases and Queries

Typical scenarios include auditing in-flight city tax uploads, diagnosing why an employee's city tax was not applied in payroll, and reconciling staged rules against the resulting payroll records.

To inspect staged rules for a given employee:

• SELECT emp_city_tax_rule_id, person_id, assignment_id, city_code, jurisdiction_code, effective_start_date, effective_end_date, status FROM hr.hr_h2pi_city_tax_rules WHERE person_id = :p_person_id ORDER BY effective_start_date DESC;

To find active, unprocessed rows awaiting upload:

• SELECT * FROM hr.hr_h2pi_city_tax_rules WHERE business_group_id = :p_bg AND status IS NULL AND effective_end_date = hr.hr_h2pi_city_tax_rules.effective_start_date; (date-effective style filter)

Reporting is usually limited to reconciliation dashboards by jurisdiction, exemption flag, or filing status. Because rows are transient, queries should filter on STATUS and effective dates to avoid counting stale or superseded upload attempts.

Related Objects

The table's key relationships center on the employee and assignment identifiers. The most significant related objects include:

• PER_ALL_PEOPLE_F — joined via PERSON_ID to resolve employee identity.
• PER_ALL_ASSIGNMENTS_F — joined via ASSIGNMENT_ID to resolve the assignment and effective dating.
• HR_H2PI_AIR_TAX_RULES — a sibling staging table in the H2PI family for other tax rule types.
• HR_H2PI_STG_TABLES / H2PI batch control tables — drive the upload lifecycle that consumes and purges this staging data.
• PAY_US_CITY_TAX_RULES (or equivalent PAY city tax rule entity) — the destination payroll structure receiving the transformed records.
• HR_ORGANIZATION_UNITS / BUSINESS_GROUP_ID reference — resolves the owning business group.

Because the heuristic classification is standalone, no formal foreign-key links are documented beyond the primary key itself; joins to the above objects are driven by shared business columns such as PERSON_ID and ASSIGNMENT_ID rather than enforced constraints.