Payroll_PayrollDetail

Purpose

The Payroll.PayrollDetail table stores line-item level details for payroll transactions. It provides granular information about each component of a payroll transaction, including earnings types, hours, rates, and amounts. This table is essential for detailed payroll analysis, audit trails, and compensation breakdowns.

Table Structure

Field Name	Data Type	Nullable	Description	Source Table	Source Field	Transformation Notes
PayrollDetailID	INT	NO	Primary key, unique identifier	PAYROLL_DETAIL	DETAIL_ID	Direct mapping
PayrollTransactionID	INT	NO	Foreign key to Payroll.PayrollTransaction	PAYROLL_DETAIL	TRANSACTION_ID	Direct mapping
EmployeeID	INT	NO	Foreign key to Core.Employee	PAYROLL_DETAIL	EMPL_ID	Direct mapping
EarningCode	VARCHAR(20)	NO	Earnings type code	PAYROLL_DETAIL	EARNING_CODE	Direct mapping
EarningType	VARCHAR(50)	NO	Type of earnings	PAYROLL_DETAIL	EARNING_TYPE	Mapped from codes
Hours	DECIMAL(5,2)	YES	Hours worked	PAYROLL_DETAIL	HOURS	Direct mapping
Rate	DECIMAL(10,2)	YES	Pay rate	PAYROLL_DETAIL	RATE	Direct mapping
Amount	DECIMAL(12,2)	NO	Earnings amount	PAYROLL_DETAIL	AMOUNT	Direct mapping
TaxableFlag	BIT	NO	Taxable earnings indicator	PAYROLL_DETAIL	TAXABLE_FLAG	Direct mapping
BenefitFlag	BIT	NO	Benefit-eligible earnings indicator	PAYROLL_DETAIL	BENEFIT_FLAG	Direct mapping
DepartmentCode	VARCHAR(20)	YES	Department code	PAYROLL_DETAIL	DEPT_CODE	Direct mapping
JobCode	VARCHAR(20)	YES	Job code	PAYROLL_DETAIL	JOB_CODE	Direct mapping
ProjectCode	VARCHAR(20)	YES	Project code	PAYROLL_DETAIL	PROJECT_CODE	Direct mapping
WorkLocation	VARCHAR(50)	YES	Work location	PAYROLL_DETAIL	WORK_LOCATION	Direct mapping
ShiftCode	VARCHAR(20)	YES	Shift code	PAYROLL_DETAIL	SHIFT_CODE	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: PAYROLL_DETAIL Table

Contains line-item payroll details
Updated with each payroll cycle
Key fields mapped directly to Payroll.PayrollDetail

Secondary Sources:

EARNING_CODES Table
- Earnings type definitions
- Used for validation and categorization
- Updated when new earning types are added
DEPARTMENT_CODES Table
- Department information
- Used for department validation
- Updated when department structure changes

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'

Amount Calculation

Amount = CASE
  WHEN Hours IS NOT NULL AND Rate IS NOT NULL THEN Hours * Rate
  ELSE Amount
END

Taxable Flag Derivation

TaxableFlag = CASE
  WHEN EarningType IN ('Regular', 'Overtime', 'Bonus', 'Commission') THEN 1
  ELSE 0
END

Benefit Flag Derivation

BenefitFlag = CASE
  WHEN EarningType IN ('Regular', 'Overtime', 'Vacation', 'Sick', 'Holiday') THEN 1
  ELSE 0
END

Data Quality Rules

Required Fields
- PayrollDetailID
- PayrollTransactionID
- EmployeeID
- EarningCode
- EarningType
- Amount
- TaxableFlag
- BenefitFlag
Validation Rules
- Hours must be non-negative if present
- Rate must be positive if present
- Amount must be non-negative
- EarningCode must be valid
- DepartmentCode must be valid if present
Business Rules
- Regular earnings must have hours and rate
- Overtime rate must be 1.5x regular rate
- Bonus and commission should not have hours
- Department code must match employee's department

Usage Notes

Reporting Considerations
- Use EarningType for earnings breakdown
- Consider TaxableFlag for tax reporting
- Use BenefitFlag for benefit calculations
- Track hours by department and job code
Performance Considerations
- Indexed on PayrollDetailID, PayrollTransactionID, and EmployeeID
- Consider filtering on EarningType for specific earnings
- Use CreatedDate/ModifiedDate for incremental loads
Integration Points
- Links to Payroll.PayrollTransaction via PayrollTransactionID
- Links to Payroll.Earnings via EarningCode
- Used in detailed payroll analysis views
- Source for earnings dimension in data warehouse

Maintenance

Refresh Schedule
- Bi-weekly updates for new payroll cycles
- Full refresh monthly
- Emergency updates for corrections
Archive Strategy
- Historical records maintained in table
- Use PayrollTransactionID for historical filtering
- Detail history tracked for audit purposes
Monitoring
- Track detail counts by earning type
- Monitor hours and rates
- Alert on validation rule violations
- Track department and job code usage

Payroll.PayrollDetail Table Documentation