Payroll_PayrollTransaction

Purpose

The Payroll.PayrollTransaction table stores detailed payroll information for each pay period. It serves as the central repository for payroll transactions, including hours worked, earnings, taxes, and deductions. This table is essential for payroll processing, financial reporting, and compensation analysis.

Table Structure

Field Name	Data Type	Nullable	Description	Source Table	Source Field	Transformation Notes
PayrollTransactionID	INT	NO	Primary key, unique identifier	PAYROLL_TRANSACTIONS	TRANSACTION_ID	Direct mapping
EmployeeID	INT	NO	Foreign key to Core.Employee	PAYROLL_TRANSACTIONS	EMPL_ID	Direct mapping
PayDate	DATE	NO	Pay date	PAYROLL_TRANSACTIONS	PAY_DATE	Direct mapping
PayPeriodStart	DATE	NO	Pay period start date	PAYROLL_TRANSACTIONS	PERIOD_START	Direct mapping
PayPeriodEnd	DATE	NO	Pay period end date	PAYROLL_TRANSACTIONS	PERIOD_END	Direct mapping
PayFrequency	VARCHAR(20)	NO	Pay frequency	PAYROLL_TRANSACTIONS	PAY_FREQUENCY	Mapped from codes
GrossPay	DECIMAL(12,2)	NO	Gross pay amount	PAYROLL_TRANSACTIONS	GROSS_PAY	Direct mapping
NetPay	DECIMAL(12,2)	NO	Net pay amount	PAYROLL_TRANSACTIONS	NET_PAY	Direct mapping
RegularHours	DECIMAL(5,2)	YES	Regular hours worked	PAYROLL_TRANSACTIONS	REG_HOURS	Direct mapping
OvertimeHours	DECIMAL(5,2)	YES	Overtime hours worked	PAYROLL_TRANSACTIONS	OT_HOURS	Direct mapping
RegularRate	DECIMAL(10,2)	YES	Regular pay rate	PAYROLL_TRANSACTIONS	REG_RATE	Direct mapping
OvertimeRate	DECIMAL(10,2)	YES	Overtime pay rate	PAYROLL_TRANSACTIONS	OT_RATE	Direct mapping
YTDGrossPay	DECIMAL(12,2)	NO	Year-to-date gross pay	PAYROLL_TRANSACTIONS	YTD_GROSS	Calculated
YTDNetPay	DECIMAL(12,2)	NO	Year-to-date net pay	PAYROLL_TRANSACTIONS	YTD_NET	Calculated
YTDTaxes	DECIMAL(12,2)	NO	Year-to-date taxes	PAYROLL_TRANSACTIONS	YTD_TAXES	Calculated
YTDDeductions	DECIMAL(12,2)	NO	Year-to-date deductions	PAYROLL_TRANSACTIONS	YTD_DEDUCTIONS	Calculated
CheckNumber	VARCHAR(20)	YES	Check number	PAYROLL_TRANSACTIONS	CHECK_NUMBER	Direct mapping
PaymentMethod	VARCHAR(50)	NO	Payment method	PAYROLL_TRANSACTIONS	PAYMENT_METHOD	Mapped from codes
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_TRANSACTIONS Table

Contains payroll transaction information
Updated with each payroll cycle
Key fields mapped directly to Payroll.PayrollTransaction

Secondary Sources:

PAYROLL_DETAIL Table
- Line-item level transaction details
- Used for validation and verification
- Updated with each payroll cycle
EMPLOYEE_RATES Table
- Employee pay rates and schedules
- Used for rate validation
- Updated when rates change

Data Transformation Rules

Pay Frequency Mapping

Source Code -> Target Frequency
'WEEKLY' -> 'Weekly'
'BIWEEKLY' -> 'Bi-weekly'
'SEMIMONTHLY' -> 'Semi-monthly'
'MONTHLY' -> 'Monthly'

Payment Method Mapping

Source Code -> Target Method
'DD' -> 'Direct Deposit'
'CHK' -> 'Paper Check'
'PCHK' -> 'Pay Card'

YTD Calculations

YTDGrossPay = Previous YTDGrossPay + Current GrossPay
YTDNetPay = Previous YTDNetPay + Current NetPay
YTDTaxes = Previous YTDTaxes + Current Tax Amounts
YTDDeductions = Previous YTDDeductions + Current Deduction Amounts

Data Quality Rules

Required Fields
- PayrollTransactionID
- EmployeeID
- PayDate
- PayPeriodStart
- PayPeriodEnd
- PayFrequency
- GrossPay
- NetPay
- YTDGrossPay
- YTDNetPay
- YTDTaxes
- YTDDeductions
- PaymentMethod
Validation Rules
- PayPeriodEnd must be after PayPeriodStart
- PayDate must be after PayPeriodEnd
- GrossPay must be greater than or equal to NetPay
- YTD values must be non-decreasing
- RegularRate must be positive if RegularHours present
Business Rules
- Active employees must have valid transactions
- Pay frequency must match employee's schedule
- Overtime rate must be 1.5x regular rate
- YTD values must reset at calendar year end

Usage Notes

Reporting Considerations
- Use PayDate for payroll period reporting
- Consider PayFrequency for trend analysis
- Use YTD values for annual reporting
- Track payment methods for distribution analysis
Performance Considerations
- Indexed on PayrollTransactionID, EmployeeID, and PayDate
- Consider filtering on PayDate for period reporting
- Use CreatedDate/ModifiedDate for incremental loads
Integration Points
- Links to Payroll.PayrollDetail via PayrollTransactionID
- Links to Payroll.PayrollBenefits via EmployeeID and PayDate
- Used in payroll analysis views
- Source for payroll 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 PayDate for historical filtering
- Transaction history tracked for compliance
Monitoring
- Track transaction counts by pay period
- Monitor gross to net ratios
- Alert on validation rule violations
- Track payment method distribution

Payroll.PayrollTransaction Table Documentation