Payroll_Earnings

Purpose

The Payroll.Earnings table stores comprehensive employee earnings and compensation details. It tracks various types of earnings including regular pay, overtime, bonuses, commissions, and other compensation components. This table is essential for compensation analysis, earnings history tracking, and payroll reporting.

Table Structure

Field Name	Data Type	Nullable	Description	Source Table	Source Field	Transformation Notes
EarningsID	INT	NO	Primary key, unique identifier	EARNINGS	EARNINGS_ID	Direct mapping
EmployeeID	INT	NO	Foreign key to Core.Employee	EARNINGS	EMPL_ID	Direct mapping
EarningCode	VARCHAR(20)	NO	Earnings type code	EARNINGS	EARNING_CODE	Direct mapping
EarningType	VARCHAR(50)	NO	Type of earnings	EARNINGS	EARNING_TYPE	Mapped from codes
Description	VARCHAR(100)	NO	Earnings description	EARNINGS	DESCRIPTION	Direct mapping
RateType	VARCHAR(20)	NO	Rate calculation type	EARNINGS	RATE_TYPE	Mapped from codes
BaseRate	DECIMAL(10,2)	YES	Base pay rate	EARNINGS	BASE_RATE	Direct mapping
OvertimeRate	DECIMAL(10,2)	YES	Overtime pay rate	EARNINGS	OT_RATE	Calculated
DoubleTimeRate	DECIMAL(10,2)	YES	Double time pay rate	EARNINGS	DT_RATE	Calculated
MinimumRate	DECIMAL(10,2)	YES	Minimum pay rate	EARNINGS	MIN_RATE	Direct mapping
MaximumRate	DECIMAL(10,2)	YES	Maximum pay rate	EARNINGS	MAX_RATE	Direct mapping
EffectiveDate	DATE	NO	Rate effective date	EARNINGS	EFFECTIVE_DATE	Direct mapping
EndDate	DATE	YES	Rate end date	EARNINGS	END_DATE	Direct mapping
IsActive	BIT	NO	Active status indicator	EARNINGS	IS_ACTIVE	Derived
TaxableFlag	BIT	NO	Taxable earnings indicator	EARNINGS	TAXABLE_FLAG	Direct mapping
BenefitFlag	BIT	NO	Benefit-eligible earnings indicator	EARNINGS	BENEFIT_FLAG	Direct mapping
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: EARNINGS Table

Contains employee earnings information
Updated when rates change
Key fields mapped directly to Payroll.Earnings

Secondary Sources:

EMPLOYEE_RATES Table
- Current employee pay rates
- Used for rate validation
- Updated when rates change
EARNING_CODES Table
- Earnings type definitions
- Used for validation and categorization
- Updated when new earning types are added

Data Transformation Rules

Earning Type Mapping

Source Code -> Target Type
'REG' -> 'Regular'
'OT' -> 'Overtime'
'DT' -> 'Double Time'
'BON' -> 'Bonus'
'COM' -> 'Commission'
'VAC' -> 'Vacation'
'SICK' -> 'Sick'
'HOL' -> 'Holiday'

Rate Type Mapping

Source Code -> Target Type
'HOURLY' -> 'Hourly'
'SALARY' -> 'Salary'
'COMM' -> 'Commission'
'BONUS' -> 'Bonus'

Overtime Rate Calculation

OvertimeRate = CASE
  WHEN RateType = 'Hourly' THEN BaseRate * 1.5
  ELSE NULL
END

Double Time Rate Calculation

DoubleTimeRate = CASE
  WHEN RateType = 'Hourly' THEN BaseRate * 2.0
  ELSE NULL
END

Active Status Derivation

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

Data Quality Rules

Required Fields
- EarningsID
- EmployeeID
- EarningCode
- EarningType
- Description
- RateType
- BaseRate
- EffectiveDate
- IsActive
- TaxableFlag
- BenefitFlag
Validation Rules
- BaseRate must be positive
- OvertimeRate must be 1.5x BaseRate for hourly
- DoubleTimeRate must be 2.0x BaseRate for hourly
- MinimumRate must be less than or equal to MaximumRate
- EndDate must be after EffectiveDate if present
Business Rules
- Only one active rate per earning type per employee
- Rate changes must be effective dated
- Overtime rates only apply to hourly employees
- Salary rates must have null overtime rates

Usage Notes

Reporting Considerations
- Use EarningType for earnings analysis
- Consider RateType for compensation planning
- Track rate changes over time
- Monitor rate compliance
Performance Considerations
- Indexed on EarningsID, EmployeeID, and EarningCode
- Consider filtering on IsActive for current rates
- Use CreatedDate/ModifiedDate for incremental loads
Integration Points
- Links to Payroll.PayrollDetail via EarningCode
- Links to Payroll.PayrollTransaction via EmployeeID
- Used in compensation analysis views
- Source for earnings dimension in data warehouse

Maintenance

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

Payroll.Earnings Table Documentation