Payroll_TaxWithholding

Purpose

The Payroll.TaxWithholding table stores comprehensive tax withholding information for employees. It tracks federal, state, and local tax elections, including filing status, allowances, additional withholding amounts, and tax exemptions. This table is essential for accurate payroll tax calculations, tax reporting, and compliance with tax regulations.

Table Structure

Field Name	Data Type	Nullable	Description	Source Table	Source Field	Transformation Notes
TaxWithholdingID	INT	NO	Primary key, unique identifier	TAX_WITHHOLDING	WITHHOLDING_ID	Direct mapping
EmployeeID	INT	NO	Foreign key to Core.Employee	TAX_WITHHOLDING	EMPL_ID	Direct mapping
TaxYear	INT	NO	Tax year	TAX_WITHHOLDING	TAX_YEAR	Direct mapping
TaxType	VARCHAR(20)	NO	Type of tax	TAX_WITHHOLDING	TAX_TYPE	Mapped from codes
FilingStatus	VARCHAR(20)	NO	Tax filing status	TAX_WITHHOLDING	FILING_STATUS	Mapped from codes
Allowances	INT	YES	Number of allowances	TAX_WITHHOLDING	ALLOWANCES	Direct mapping
AdditionalAmount	DECIMAL(12,2)	YES	Additional withholding amount	TAX_WITHHOLDING	ADDITIONAL_AMOUNT	Direct mapping
ExemptFlag	BIT	NO	Tax exemption indicator	TAX_WITHHOLDING	EXEMPT_FLAG	Direct mapping
ExemptionReason	VARCHAR(100)	YES	Reason for exemption	TAX_WITHHOLDING	EXEMPTION_REASON	Direct mapping
StateCode	VARCHAR(2)	YES	State code	TAX_WITHHOLDING	STATE_CODE	Direct mapping
LocalCode	VARCHAR(20)	YES	Local tax code	TAX_WITHHOLDING	LOCAL_CODE	Direct mapping
EffectiveDate	DATE	NO	Election effective date	TAX_WITHHOLDING	EFFECTIVE_DATE	Direct mapping
EndDate	DATE	YES	Election end date	TAX_WITHHOLDING	END_DATE	Direct mapping
IsActive	BIT	NO	Active status indicator	TAX_WITHHOLDING	IS_ACTIVE	Derived
CreatedDate	DATETIME	NO	Record creation date	N/A	N/A	System generated
ModifiedDate	DATETIME	NO	Last modification date	N/A	N/A	System generated

Source System Mapping

Primary Source: TAX_WITHHOLDING Table

Contains employee tax withholding information
Updated when tax elections change
Key fields mapped directly to Payroll.TaxWithholding

Secondary Sources:

TAX_CODES Table
- Tax type definitions
- Used for tax validation
- Updated when tax codes change
STATE_TAXES Table
- State tax information
- Used for state tax validation
- Updated when state tax rules change

Data Transformation Rules

Tax Type Mapping

Source Code -> Target Type
'FED' -> 'Federal'
'STATE' -> 'State'
'LOCAL' -> 'Local'
'FICA' -> 'FICA'
'MED' -> 'Medicare'

Filing Status Mapping

Source Code -> Target Status
'S' -> 'Single'
'M' -> 'Married'
'H' -> 'Head of Household'
'MFS' -> 'Married Filing Separately'

Active Status Derivation

IsActive = CASE
  WHEN EndDate IS NULL OR EndDate > GETDATE() THEN 1
  ELSE 0
END

Exemption Validation

ExemptFlag = CASE
  WHEN ExemptionReason IS NOT NULL THEN 1
  ELSE 0
END

Data Quality Rules

Required Fields
- TaxWithholdingID
- EmployeeID
- TaxYear
- TaxType
- FilingStatus
- EffectiveDate
- IsActive
Validation Rules
- TaxYear must be current or future year
- Allowances must be non-negative if present
- AdditionalAmount must be non-negative if present
- StateCode must be valid if present
- LocalCode must be valid if present
- EndDate must be after EffectiveDate if present
Business Rules
- Only one active record per tax type per employee
- Federal tax must be present for all employees
- State tax must match employee's work state
- Exemptions must have valid reasons
- Tax elections must be effective dated

Usage Notes

Reporting Considerations
- Use TaxType for tax analysis
- Consider FilingStatus for tax calculations
- Track tax changes over time
- Monitor exemption status
Performance Considerations
- Indexed on TaxWithholdingID, EmployeeID, and TaxType
- Consider filtering on IsActive for current elections
- Use CreatedDate/ModifiedDate for incremental loads
Integration Points
- Links to Payroll.PayrollTransaction via EmployeeID
- Used in payroll tax calculations
- Source for tax reporting
- Used in tax compliance views

Maintenance

Refresh Schedule
- Daily updates for tax changes
- Full refresh monthly
- Emergency updates for corrections
Archive Strategy
- Historical elections maintained in table
- Use EffectiveDate/EndDate for historical filtering
- Tax history tracked for compliance
Monitoring
- Track tax changes by employee
- Monitor tax validation
- Alert on validation rule violations
- Track exemption usage

Payroll.TaxWithholding Table Documentation