Payroll_DirectDeposit

Purpose

The Payroll.DirectDeposit table stores employee direct deposit information, including bank account details and deposit preferences. It manages the electronic transfer of payroll funds to employee bank accounts, tracking account types, routing numbers, and deposit distribution percentages. This table is essential for payroll processing, bank account management, and payment distribution.

Table Structure

Field Name	Data Type	Nullable	Description	Source Table	Source Field	Transformation Notes
DirectDepositID	INT	NO	Primary key, unique identifier	DIRECT_DEPOSIT	DEPOSIT_ID	Direct mapping
EmployeeID	INT	NO	Foreign key to Core.Employee	DIRECT_DEPOSIT	EMPL_ID	Direct mapping
AccountType	VARCHAR(20)	NO	Type of bank account	DIRECT_DEPOSIT	ACCOUNT_TYPE	Mapped from codes
BankName	VARCHAR(100)	NO	Name of financial institution	DIRECT_DEPOSIT	BANK_NAME	Direct mapping
RoutingNumber	VARCHAR(20)	NO	Bank routing number	DIRECT_DEPOSIT	ROUTING_NUMBER	Direct mapping
AccountNumber	VARCHAR(50)	NO	Bank account number	DIRECT_DEPOSIT	ACCOUNT_NUMBER	Direct mapping
Priority	INT	NO	Deposit priority order	DIRECT_DEPOSIT	PRIORITY	Direct mapping
Percentage	DECIMAL(5,2)	YES	Deposit percentage	DIRECT_DEPOSIT	PERCENTAGE	Direct mapping
FixedAmount	DECIMAL(12,2)	YES	Fixed deposit amount	DIRECT_DEPOSIT	FIXED_AMOUNT	Direct mapping
EffectiveDate	DATE	NO	Account effective date	DIRECT_DEPOSIT	EFFECTIVE_DATE	Direct mapping
EndDate	DATE	YES	Account end date	DIRECT_DEPOSIT	END_DATE	Direct mapping
IsActive	BIT	NO	Active status indicator	DIRECT_DEPOSIT	IS_ACTIVE	Derived
IsPrimary	BIT	NO	Primary account indicator	DIRECT_DEPOSIT	IS_PRIMARY	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: DIRECT_DEPOSIT Table

Contains employee direct deposit information
Updated when account details change
Key fields mapped directly to Payroll.DirectDeposit

Secondary Sources:

BANK_CODES Table
- Bank routing information
- Used for bank validation
- Updated when bank information changes
EMPLOYEE_PAYMENT Table
- Employee payment preferences
- Used for payment validation
- Updated when payment preferences change

Data Transformation Rules

Account Type Mapping

Source Code -> Target Type
'CHK' -> 'Checking'
'SAV' -> 'Savings'
'MMA' -> 'Money Market'
'PCHK' -> 'Pay Card'

Active Status Derivation

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

Primary Account Derivation

IsPrimary = CASE
  WHEN Priority = 1 THEN 1
  ELSE 0
END

Percentage Validation

Percentage = CASE
  WHEN Percentage IS NULL AND FixedAmount IS NULL THEN 100
  ELSE Percentage
END

Data Quality Rules

Required Fields
- DirectDepositID
- EmployeeID
- AccountType
- BankName
- RoutingNumber
- AccountNumber
- Priority
- EffectiveDate
- IsActive
- IsPrimary
Validation Rules
- RoutingNumber must be valid
- AccountNumber must be valid
- Priority must be positive
- Percentage must be between 0 and 100 if present
- FixedAmount must be positive if present
- EndDate must be after EffectiveDate if present
Business Rules
- Only one primary account per employee
- Total percentage must equal 100% for all active accounts
- Routing number must match bank name
- Account must be active for payroll processing

Usage Notes

Reporting Considerations
- Use AccountType for bank account analysis
- Consider Priority for deposit distribution
- Track account changes over time
- Monitor active account status
Performance Considerations
- Indexed on DirectDepositID, EmployeeID, and Priority
- Consider filtering on IsActive for current accounts
- Use CreatedDate/ModifiedDate for incremental loads
Integration Points
- Links to Payroll.PayrollTransaction via EmployeeID
- Used in payroll processing
- Source for payment distribution
- Used in bank account management views

Maintenance

Refresh Schedule
- Daily updates for account changes
- Full refresh monthly
- Emergency updates for corrections
Archive Strategy
- Historical accounts maintained in table
- Use EffectiveDate/EndDate for historical filtering
- Account history tracked for audit purposes
Monitoring
- Track account changes by employee
- Monitor account validation
- Alert on validation rule violations
- Track bank account usage

Payroll.DirectDeposit Table Documentation