Results for “pay_us_emp_fed_tax_rules_f”

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

Overview

The PAY_US_EMP_FED_TAX_RULES_F table resides in the HR schema of Oracle E-Business Suite and belongs to the Payroll (PAY) product family. It stores federal-level tax details associated with employee assignments for United States payroll processing. Each row captures the federal withholding configuration — filing status, allowances, overrides, exemptions, and supporting statutory attributes — that Oracle Payroll uses when calculating federal income tax (FIT), FUTA, Medicare, and Social Security liabilities during a payroll run.

The table is date-tracked (effective-dated), meaning multiple rows may exist per assignment, each valid for a distinct date range. The _F suffix indicates this is the "frozen" or datetracked variant of the underlying entity, and updates are managed through Oracle HRMS date-tracked DML rather than direct SQL where possible.

Based on its foreign-key structure, the ETRM heuristic classifies this object as a link table in Data Vault terms. It connects an employee assignment (business entity) to a federal tax rule configuration and to a state/jurisdiction reference, with effective dating and descriptive tax attributes layered on top. This is a modeling suggestion rather than a physical declaration.

Key Information Stored

Among the 92 documented columns, the following are most significant:

• EMP_FED_TAX_RULE_ID — surrogate primary key component identifying each federal tax rule row. Combined with the effective dates it forms the composite primary key PAY_US_EMP_FED_TAX_RULES_PK (EMP_FED_TAX_RULE_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE), which is also the only documented unique index (business-key candidate).
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — define the date-tracked validity window of the rule; both participate in the primary key.
• ASSIGNMENT_ID — links the rule to a specific employee assignment.
• BUSINESS_GROUP_ID — foreign key to HR_ALL_ORGANIZATION_UNITS, scoping the record to a business group.
• FILING_STATUS_CODE — the employee's federal filing status (e.g., single, married) used to drive withholding tables.
• WITHHOLDING_ALLOWANCES — number of federal allowances claimed.
• FIT_OVERRIDE_AMOUNT and FIT_OVERRIDE_RATE — user-specified overrides that supersede calculated federal income tax.
• FIT_ADDITIONAL_TAX — additional flat federal tax withheld per period.
• FIT_EXEMPT, FUTA_TAX_EXEMPT, MEDICARE_TAX_EXEMPT, SS_TAX_EXEMPT, WAGE_EXEMPT — exemption flags controlling which federal taxes apply to the assignment.
• EIC_FILING_STATUS_CODE — Earned Income Credit filing status.
• CUMULATIVE_TAXATION — indicates whether cumulative tax calculation applies.
• SUI_STATE_CODE / SUI_JURISDICTION_CODE — state unemployment insurance reference; SUI_STATE_CODE is a foreign key to PAY_US_STATES.
• W2_FILED_YEAR — tracking year for W-2 reporting.

The remaining columns consist of the standard WHO audit columns, OBJECT_VERSION_NUMBER, 30 generic ATTRIBUTE flexfields, and 30 FED_INFORMATION descriptive flexfield segments, providing extensibility without schema change.

Common Use Cases and Queries

Typical uses include retrieving an employee's current federal withholding setup, auditing exemption flags before a payroll run, and reporting override amounts for tax reconciliation.

To fetch the active federal tax rule for an assignment as of a given date:

SELECT assignment_id, filing_status_code, withholding_allowances,
       fit_override_amount, fit_addditional_tax, fit_exempt
FROM   hr.pay_us_emp_fed_tax_rules_f
WHERE  assignment_id = :p_assignment_id
AND    TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

To list assignments claiming federal exemptions within a business group:

SELECT assignment_id, filing_status_code, fut_tax_exempt, ss_tax_exempt
FROM   hr.pay_us_emp_fed_tax_rules_f
WHERE  business_group_id = :p_bg_id
AND    (fit_exempt = 'Y' OR fut_tax_exempt = 'Y')
AND    TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

Reporting use cases include W-2 year-end validation (via W2_FILED_YEAR), override reconciliation for payroll balancing, and feeding downstream tax extracts.

Related Objects

• HR_ALL_ORGANIZATION_UNITS — referenced by BUSINESS_GROUP_ID; defines the business group owning each tax rule.
• PAY_US_STATES — referenced by SUI_STATE_CODE; supplies state unemployment insurance jurisdiction data.
• PAY_ASSIGNMENT_ACTIONS / PER_ASSIGNMENTS_F — the assignment to which ASSIGNMENT_ID resolves.
• PAY_US_EMP_STATE_TAX_RULES_F — the parallel state-level tax rules table, used alongside federal rules during tax calculation.
• PAY_PAYROLL_ACTIONS / PAY_RUN_RESULTS — consume the federal rule during payroll processing to derive withholding results.
• PAY_US_EMP_FED_TAX_RULES_PK — the primary key constraint/index enforcing uniqueness on the rule ID and effective dates.