Results for “emp_fed_tax_inf_id”

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

Overview

PAY_CA_EMP_FED_TAX_INFO_F is a datetracked payroll table owned by the HR schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It stores Canadian federal tax information for employee assignments processed by the Oracle Payroll (PAY) module. Each row represents the federal tax attributes that govern how payroll calculates statutory deductions, tax credits, and exemptions for a Canadian employee at a given point in time. The table is date-effective via EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, allowing historical tax elections to be preserved while a new effective-dated row captures a change in employee circumstances. With 62 documented columns, it is one of the wider employee-level tax information structures in the Canadian payroll schema.

From a heuristic Data Vault perspective, the mined relationship data classifies this object as standalone — no foreign keys were detected linking it definitively to other entities. In Data Vault modeling terms, this suggests the table behaves most like a satellite: it carries descriptive, changing attributes keyed by a parent identity (the employee federal tax information identifier). A hub would be expected to hold only the business key, and a link would resolve many-to-many relationships; neither pattern is evidenced here, so modeling this as a satellite attached to an employee/assignment hub is the appropriate suggestion.

Key Information Stored

The primary key is PAY_CA_EMP_FED_TAX_INFO_PK, composed of EMP_FED_TAX_INF_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. EMP_FED_TAX_INF_ID is the surrogate identifier; the effective date pair supplies the datetrack component. No separate business-key unique index was documented beyond this composite primary key.

• ASSIGNMENT_ID and BUSINESS_GROUP_ID — link the tax record to the employee assignment and the operating business group.
• LEGISLATION_CODE and EMPLOYMENT_PROVINCE — identify the Canadian tax jurisdiction governing calculation.
• TAX_CREDIT_AMOUNT and CLAIM_CODE — federal personal tax credit entitlement and the associated claim designation.
• BASIC_EXEMPTION_FLAG, FED_EXEMPT_FLAG, EI_EXEMPT_FLAG, CPP_QPP_EXEMPT_FLAG — exemption indicators for federal tax, Employment Insurance, and CPP/QPP.
• ADDITIONAL_TAX and FED_OVERRIDE_AMOUNT / FED_OVERRIDE_RATE — voluntary additional withholding and manual override values.
• TAX_CALC_METHOD — determines how federal tax is computed.
• ANNUAL_DEDN, TOTAL_EXPENSE_BY_COMMISSION, TOTAL_REMNRTN_BY_COMMISSION, PRESCRIBED_ZONE_DEDN_AMT, OTHER_FEDTAX_CREDITS — deductible and credit-eligible amounts.
• FED_LSF_AMOUNT, CPP_ELECTION_DATE, CPP_REVOCATION_DATE — federal labour-sponsored fund amount and CPP election/revocation dates.
• CA_TAX_INFORMATION_CATEGORY with CA_TAX_INFORMATION1 through 30 — a flexible descriptive-flexfield block for extensible federal tax attributes.
• Standard audit columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE, OBJECT_VERSION_NUMBER.

Common Use Cases and Queries

Payroll administrators and integration developers query this table to verify an employee's current federal tax elections, debug withholding discrepancies, and extract historical tax data for T4 reconciliation. Typical patterns join to assignment and person tables and filter on the current effective date.

• Current elections: SELECT * FROM pay_ca_emp_fed_tax_info_f WHERE assignment_id = :p_assignment AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date.
• Audit history: query all rows for a given EMP_FED_TAX_INF_ID ordered by EFFECTIVE_START_DATE to trace election changes.
• Withholding review: select TAX_CREDIT_AMOUNT, CLAIM_CODE, ADDITIONAL_TAX, and TAX_CALC_METHOD to validate federal tax results against payroll run output.
• Exemption reporting: count records where FED_EXEMPT_FLAG, EI_EXEMPT_FLAG, or CPP_QPP_EXEMPT_FLAG is set, for compliance monitoring.
• Extensibility: read CA_TAX_INFORMATION_CATEGORY and the numbered CA_TAX_INFORMATION columns for client-specific attributes.

Related Objects

Although the heuristic classification is standalone, functional dependencies exist through shared business identifiers rather than enforced foreign keys. The most significant related objects include:

• PER_ALL_ASSIGNMENTS_F — joined on ASSIGNMENT_ID to resolve the employee assignment.
• HR_ALL_ORGANIZATION_UNITS (via BUSINESS_GROUP_ID) — identifies the business group context.
• PAY_CA_EMP_TAX_INFO_F — parent provincial/employee tax information entity in the Canadian payroll tax family.
• PAY_EMPLOYEE_TAX_RULES / payroll tax calculation routines — consume the stored elections during payroll processing.
• PAY_PAYROLL_ACTIONS and PAY_ASSIGNMENT_ACTIONS — provide the run context in which tax data is evaluated.
• PAY_CA_TAX_INFORMATION flexfield definitions — resolve CA_TAX_INFORMATION_CATEGORY descriptive columns.

Because the table is datetracked and versioned, any querying tool or interface must respect the effective date window to avoid returning obsolete elections.