This article will describe the file transformation that has been created in detamoov to transform the standard 401k file format to the TIAA file specifications.  This transformation is used in a detamoov integration with TIAA.

 

 

Source File	Destination file
Standard 401k	TIAA

 

File Specifications

 

File Type Generated	Pipe-delimited
Header Row(s) Generated	Yes - custom
Footer Row(s) Generated	Yes - custom
End of Row Character	CRLF
File Naming Generated	Production: CR_[Plan Id]_[Date in YYYYMMDD]_[Time in HHMMSS].txt    Test: CR_TEST [Plan Id]_[Date in YYYYMMDD]_[Time in HHMMSS].txt

 

Field Mappings

 

The table below details the specific field mapping and transformation rules applied to ensure the TIAA Payroll Contribution pipe-delimited format is created correctly from the standard 401k format.

 

Header Row

 

TIAA Field Name	Standard 401k Field Name	Field Type	Required?	Default Value	Details
HEADER		STRING	YES	SPARKH
DATA TYPE		STRING	YES	04	01 – Account Data
					02 – Distributions Data
					03 – Census Only Data
					04 – Remittance with Census Data
					05 – Remittance Data Only
DATA SOURCE	COMPANY LEGAL NAME	STRING	YES		Typically client name, To Uppercase
CREATION DATE/TIME		DATE	YES		YYYYMMDD-HHMMSS
CONTACT		STRING	NO
SENDER		STRING	NO
SPARK VERSION		STRING	YES	2.0
AS OF DATE	PAY PERIOD END DATE	DATE	YES		YYYYMMDD
PLAN START DATE		DATE	NO
PAYROLL FUNDING		DATE	NO

 

Body

 

TIAA Field Name	Standard 401k Field Name	Field Type	Required?	Details
DETAIL RECORD ID		STRING	YES	Default value = D
EMPLOYER NAME	COMPANY LEGAL NAME	STRING	YES	To Uppercase
EMPLOYER EIN	COMPANY EIN	STRING	NO
EMPLOYER PLAN ID	PLAN ID	STRING	YES
EMPLOYER SUB PLAN ID		STRING	NO
ORIGINATING VENDOR PLAN ID		STRING	NO
ORIGINATING VENDOR SUB PLAN ID		STRING	NO
RECIPIENT VENDOR PLAN ID		STRING	NO
RECIPIENT VENDOR SUB PLAN ID		STRING	NO
TYPE OF ACCOUNT		STRING	YES	001 = 403(b)(1)
				007 = 403(b)(7)
				008 = Both 403(b)(1) and (7)
				009 = 403(b)(9)
				01A = 401a
				01K = 401k (Default)
				457 = 457
PAYROLL FREQUENCY	PAYROL START DATE AND PAYROLL END DATE	NUMBER	NO	1 = Annual
				2 = Semi-Annual
				4 = Quarterly
				12 = Monthly
				24 = Semi-Monthly
				26 = Bi-Weekly
				52 = Weekly
				365 = Daily
EMPLOYEE SSN	SSN	NUMBER	YES	No dashes
EMPLOYEE ID	EMPLOYEE NUMBER	STRING	NO	To Uppercase
EMPLOYEE TITLE		STRING	NO
EMPLOYEE FIRST NAME	FIRST NAME	STRING	YES	To Uppercase
EMPLOYEE MIDDLE NAME	MIDDLE NAME/INITIAL	STRING	NO	To Uppercase
EMPLOYEE LAST NAME	LAST NAME	STRING	YES	To Uppercase
ADDRESS LINE 1	ADDRESS LINE 1	STRING	YES	To Uppercase
ADDRESS LINE 2	ADDRESS LINE 2	STRING	NO	To Uppercase
ADDRESS LINE 3		STRING	NO
CITY	CITY	STRING	YES	To Uppercase
STATE	STATE	STRING	YES	To Uppercase
ZIP CODE	ZIP	NUMBER	YES	No dashes
COUNTRY CODE		STRING	YES	Default value = US
RESIDENCY CODE		STRING	YES	U = US Citizen (Default)
				N = Non-Resident Alien
DATE OF BIRTH	DOB	DATE	YES	YYYYMMDD
GENDER ID	GENDER	STRING	YES	Male = M Female = F To Uppercase
MARITAL STATUS	MARITAL STATUS	STRING	NO	S = Single M = Married P = Domestic Partner Q = QDRO To Uppercase
PHONE NUMBER 1	PHONE	NUMBER	NO	No dashes
PHONE NUMBER TYPE 1		STRING	NO
PHONE EXTENSION 1		STRING	NO
PHONE NUMBER 2		NUMBER	NO
PHONE NUMBER TYPE 2		STRING	NO
PHONE EXTENSION 2		STRING	NO
BUSINESS EMAIL ADDRESS	EMAIL	STRING	NO	To Uppercase
PERSONAL EMAIL ADDRESS		STRING	NO
PAYROLL DATE	PAYROLL CHECK DATE	DATE	YES	YYYYMMDD
CONTRIBUTION SOURCE CODE 1	Per Plan	STRING	NO	To Uppercase
CONTRIBUTION SOURCE AMOUNT 1	Per Plan	NUMBER	NO	Send 0.00 if input equals 0 or is null
CONTRIBUTION SOURCE CODE 2	Per Plan	STRING	NO	To Uppercase
CONTRIBUTION SOURCE AMOUNT 2	Per Plan	NUMBER	NO	Send 0.00 if input equals 0 or is null
CONTRIBUTION SOURCE CODE 3	Per Plan	STRING	NO	To Uppercase
CONTRIBUTION SOURCE AMOUNT 3	Per Plan	NUMBER	NO	Send 0.00 if input equals 0 or is null
CONTRIBUTION SOURCE CODE 4	Per Plan	STRING	NO	To Uppercase
CONTRIBUTION SOURCE AMOUNT 4	Per Plan	NUMBER	NO	Send 0.00 if input equals 0 or is null
CONTRIBUTION SOURCE CODE 5	Per Plan	STRING	NO	To Uppercase
CONTRIBUTION SOURCE AMOUNT 5	Per Plan	NUMBER	NO	Send 0.00 if input equals 0 or is null
CONTRIBUTION SOURCE CODE 6	Per Plan	STRING	NO	To Uppercase
CONTRIBUTION SOURCE AMOUNT 6	Per Plan	NUMBER	NO	Send 0.00 if input equals 0 or is null
CONTRIBUTION SOURCE CODE 7	Per Plan	STRING	NO	To Uppercase
CONTRIBUTION SOURCE AMOUNT 7	Per Plan	NUMBER	NO	Send 0.00 if input equals 0 or is null
CONTRIBUTION SOURCE CODE 8	Per Plan	STRING	NO	To Uppercase
CONTRIBUTION SOURCE AMOUNT 8	Per Plan	NUMBER	NO	Send 0.00 if input equals 0 or is null
FINAL CONTRIBUTION INDICATOR		STRING	NO
LOAN NUMBER 1	401K LOAN 1 ID	STRING	NO
LOAN REPAYMENT AMOUNT 1	401K LOAN 1 REPAYMENT	NUMBER	NO	Required if Loan Number 1 provided
LOAN NUMBER 2	401K LOAN 2 ID	STRING	NO
LOAN REPAYMENT AMOUNT 2	401K LOAN 2 REPAYMENT	NUMBER	NO	Required if Loan Number 2 provided
LOAN NUMBER 3		STRING	NO
LOAN REPAYMENT AMOUNT 3		NUMBER	NO
LOAN NUMBER 4		STRING	NO
LOAN REPAYMENT AMOUNT 4		NUMBER	NO
LOAN NUMBER 5		STRING	NO
LOAN REPAYMENT AMOUNT 5		NUMBER	NO
HR AREA/LOCATION CODE		STRING	NO
HR SUB AREA		STRING	NO
ORIGINAL DATE OF HIRE	HIRE DATE	DATE	YES
ADJUSTED DATE OF HIRE	HIRE DATE AND REHIRE DATE	DATE	YES	YYYYMMDD If Rehire Blank, set to Hire, else set to Rehire
EMPLOYMENT STATUS	EMPLOYMENT STATUS	STRING	YES	E = Currently Employed D = Deceased P = Disabled R = Retired T = Terminated L = Leave of Absence To Uppercase
				Replace "A" with "E"
EMPLOYMENT SUB TYPE	REHIRE DATE	STRING	NO	To Uppercase
				If Status = "E", check for Rehire Date
				If Rehire Date exists = "R", else set to "O"
				Else set null
EMPLOYMENT STATUS DATE	HIRE DATE, REHIRE DATE, TERMINATION DATE	DATE	YES	Default Hire Date
				If Rehire Date exists, set Rehire Date
				If Termination Date > Rehire Date, set Termination Date
EMPLOYEE TYPE		STRING	YES	Default Value = F
PAYROLL MODE		STRING	YES	Default Value = 100
YEARS OF SERVICE		NUMBER	NO
MONTHS OF SERVICE		NUMBER	NO
ANNUAL SALARY		NUMBER	NO
CASH BONUS AMOUNT		NUMBER	NO
PER PAY COMPENSATION	CURRENT PAY PERIOD GROSS PAY	NUMBER	NO
PER PAY HOURS WORKED	CURRENT PAY PERIOD HOURS WORKED	NUMBER	NO
YEAR TO DATE TYPE		STRING	NO
YEAR TO DATE BASE COMPENSATION		NUMBER	NO
YEAR TO DATE TOTAL COMPENSATION	YTD GROSS PAY	NUMBER	NO
YEAR TO DATE HOURS WORKED	YTD HOURS WORKED	NUMBER	NO
HCE FLAG	HIGHLY COMPENSATED EMPLOYEE	STRING	NO	Y = Yes N = No To Uppercase
KEY EMPLOYEE FLAG	KEY EMPLOYEE	STRING	NO	Y = Yes N = No To Uppercase
UNION EMPLOYEE FLAG		STRING	NO
EMPLOYEE PLAN STATUS		STRING	NO	Required if employer is determining eligibility E = Eligible but not participating P = Eligible and participating X =Excluded class N = Not eligible D = Default Enrolled A = Auto Enrolled
PLAN ENTRY DATE	HIRE DATE	DATE	NO	Default is Hire Date
VESTED DATE		DATE	NO	Date of 100% vested
ALTERNATE VESTING START DATE		DATE	NO	Alternative date from adjusted date of hire
VESTING SOURCE CODE 1		STRING	NO
VESTING PERCENTAGE 1		NUMBER	NO
VESTING SOURCE CODE 2		STRING	NO
VESTING PERCENTAGE 2		NUMBER	NO
VESTING SOURCE CODE 3		STRING	NO
VESTING PERCENTAGE 3		NUMBER	NO
CONTRIBUTION SOURCE CODE 1		STRING	NO
DEFERRAL PERCENTAGE - CS1		NUMBER	NO
PER PAY DEFERRAL AMOUNT  - CS1		NUMBER	NO
CONTRIBUTION SOURCE CODE 2		STRING	NO
DEFERRAL PERCENTAGE - CS2		NUMBER	NO
PER PAY DEFERRAL AMOUNT  - CS2		NUMBER	NO
CONTRIBUTION SOURCE CODE 3		STRING	NO
DEFERRAL PERCENTAGE - CS3		NUMBER	NO
PER PAY DEFERRAL AMOUNT  - CS3		NUMBER	NO
PLAN ANNUAL SALARY		NUMBER	NO
EMPLOYER CONTRIBUTION ELIGIBILITY SOURCE 1		STRING	NO
EMPLOYER CONTRIBUTION ELIGIBILITY DATE 1		DATE	NO
EMPLOYER CONTRIBUTION ELIGIBILITY SOURCE 2		STRING	NO
EMPLOYER CONTRIBUTION ELIGIBILITY DATE 2		DATE	NO
AUTOMATIC DEFERRAL INCREASE TYPE		STRING	NO
AUTOMATIC DEFERRAL INCREASE ELECTION DATE		DATE	NO
AUTOMATIC DEFERRAL INCREASE NEXT INCREASE DATE		DATE	NO
AUTOMATIC DEFERRAL INCREASE END DATE		DATE	NO
AUTOMATIC DEFERRAL INCREASE FREQUENCY		NUMBER	NO
AUTOMATIC DEFERRAL INCREASE AMOUNT		NUMBER	NO
AUTOMATIC DEFERRAL INCREASE AMOUNT MAXIMUM		NUMBER	NO
AUTOMATIC DEFERRAL INCREASE PERCENTAGE		NUMBER	NO
AUTOMATIC DEFERRAL INCREASE PERCENTAGE MAXIMUM		NUMBER	NO

 

Footer

 

TIAA Field Name	Standard 401k Field Name	Field Type	Required?	Default Value	Details
TRAILER		STRING	YES	SPARKTR
RECORD COUNT		NUMBER	YES		Record count, including header and footer
REMITTANCE AMOUNT TOTAL		NUMBER	YES		Total amount of all contributions
LOAN AMOUNT TOTAL		NUMBER	NO		Total amount of all loans included
FILLER		STRING	YES