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

 

Source File	Destination file
Standard 401k	Principal

 

File Specifications

 

File Type Generated	Fixed Length
Header Row(s) Generated	No
Footer Row(s) Generated	No
End of Row Character	CRLF (Carriage Return Line Feed)
File Naming Generated	Test: TEST_[Plan ID]_[date in yyyyMMdd]_[time in HHmmssff].txt Production: [Plan ID]_[date in yyyyMMdd]_[time in HHmmssff].txt

 

Field Mappings

 

The tables below details the specific field mapping and transformation rules applied to ensure the Principal Payroll Contribution .txt format is created correctly from the standard 401k format.

 

180 Record - Basic Demographic Data

Principal Field Name	Standard 401k Field Name	Field Size	Field Start	Field End	Hardcode Value	Details
Record Type		3	1	3	180
Blank Space		1	4	4
Contract Number	PLAN ID	6	5	10
Blank Space		1	11	11
Social Security Number	SSN	9	12	20		Numeric Only
Blank Space		1	21	21
Employee Name	LAST NAME AND FIRST NAME	24	22	45		Concatenate by ', ', to uppercase
Date of Birth	DOB	10	46	55		MM/DD/YYYY
Blank Space		1	56	56
Date of Employment	HIRE DATE	10	57	66		MM/DD/YYYY
Blank Space		1	67	67
Sex	GENDER	1	68	68		Male = M Female = F To Uppercase
Location Number		7	69	75		Plan Specific field override
Blank Space		1	76	76
Social Security Number	SSN	9	77	85		Numeric Only
Blank Space		26	86	111
Alternate ID		20	112	131
Blank Space		1	132	132
Eligibility Hours	CURRENT PAY PERIOD HOURS WORKED	8	133	140		MM/DD/YYYY Plan Specific, remove mapping if not required
Blank Space		1	141	141
Hours Begin Date	PAY PERIOD START DATE	10	142	151		MM/DD/YYYY Plan Specific, remove mapping if not required
Blank Space		1	152	152
Hours End Date	PAY PERIOD END DATE	10	153	162		MM/DD/YYYY Plan Specific, remove mapping if not required
Blank Space		56	163	218
Benefit Event Date	TERMINATION DATE	10	219	228		Provide for terminated employee
Blank Space		1	229	229
Vesting Hours	YTD HOURS WORKED	4	230	233		MM/DD/YYYY Plan Specific, remove mapping if not required
Benefit Event Reason		4	234	237	0001	0001 = Termination
						0003 = Retirement
						0005 = Death
						0007 = Disability
						0009 = In company transfer
						0050 = Retire Continue to Work
Blank Spaces		3	328	240

 

181 Record - Rehire Information

Principal Field Name	Standard 401k Field Name	Field Size	Field Start	Field End	Hardcode Value	Details
Record Type		3	1	3	181
Blank Space		1	4	4
Contract Number	PLAN ID	6	5	10
Blank Space		1	11	11
Employee ID Number	SSN	9	12	20		Numeric Only
Blank Space		1	21	21
Employee Name	LAST NAME AND FIRST NAME	24	22	45		Concatenate by ', ', to uppercase
Blank Space		31	46	76
Social Security Number	SSN	9	77	85		Numeric Only
Blank Space		1	86	86
Rehire Date	REHIRE DATE	10	87	96		MM/DD/YYYY
Blank Space		144	97	240

 

220 Record - Address Information

Principal Field Name	Standard 401k Field Name	Field Size	Field Start	Field End	Hardcode Value	Details
Record Type		3	1	3	220
Blank Space		1	4	4
Contract Number	PLAN ID	6	5	10
Blank Space		1	11	11
Identification Number	SSN	9	12	20		Numeric Only
Blank Space		1	21	21
Foreign Indicator		1	22	22	2
Blank Space		1	23	23
Address Line 1	ADDRESS LINE 1	50	24	73		Take first 50, to uppercase
Address Line 2	ADDRESS LINE 2	50	74	123		Take first 50, to uppercase
Address Line 3		50	124	173
City	CITY	30	174	203		Take first 30, to uppercase
State	STATE	2	204	205		Take first 2, to uppercase
Zip	ZIP	5	206	210		Take first 5
+4 for Zip		4	211	214		optional
Blank Space		26	215	240

 

240 Record - Email and Phone Number

Principal Field Name	Standard 401k Field Name	Field Size	Field Start	Field End	Hardcode Value	Details
Record Type		3	1	3	240
Blank Space		1	4	4
Contract Number	PLAN ID	6	5	10
Blank Space		1	11	11
Social Security Number	SSN	9	12	20		Numeric Only
Blank Space		1	21	21
Work Email Address	EMAIL	75	22	96		To uppercase
Personal Email Address		75	97	171
Work Phone Number		17	172	188
Work Phone Number Extension		7	189	195
Home Phone Number		17	196	212
Home Phone Number Extension		7	213	219
Mobile Phone Number		18	220	237
Blank Space		3	238	240

 

407 Record - Contributions

Principal Field Name	Standard 401k Field Name	Field Size	Field Start	Field End	Hardcode Value	Details
Record Type		3	1	3	407
Blank Space		1	4	4
Contract Number	PLAN ID	6	5	10
Blank Space		1	11	11
Social Security Number	SSN	9	12	20		Numeric Only
Blank Space		9	21	29
Investment Type		2	30	31	00
Transaction Type		4	32	35	0001
Pay Date	PAYROLL CHECK DATE	10	36	45		MM/DD/YYYY
Blank Space		27	46	72
Contribution Qualifier Type 1		1	73	73	L or C	Plan Specific
Contribution Qualifier 1		3	74	76		Plan Specific
Contribution Amount 1		10	77	86		Plan Specific
Contribution Qualifier Type 2		1	87	87	L or C	Plan Specific
Contribution Qualifier 2		3	88	90		Plan Specific
Contribution Amount 2		10	91	100		Plan Specific
Contribution Qualifier Type 3		1	101	101	L or C	Plan Specific
Contribution Qualifier 3		3	102	104		Plan Specific
Contribution Amount 3		10	105	114		Plan Specific
Contribution Qualifier Type 4		1	115	115	L or C	Plan Specific
Contribution Qualifier 4		3	116	118		Plan Specific
Contribution Amount 4		10	119	128		Plan Specific
Contribution Qualifier Type 5		1	129	129	L or C	Plan Specific
Contribution Qualifier 5		3	130	132		Plan Specific
Contribution Amount 5		10	133	142		Plan Specific
Contribution Qualifier Type 6		1	143	143	L or C	Plan Specific
Contribution Qualifier 6		3	144	146		Plan Specific
Contribution Amount 6		10	147	156		Plan Specific
Contribution Qualifier Type 7		1	157	157	L or C	Plan Specific
Contribution Qualifier 7		3	158	160		Plan Specific
Contribution Amount 7		10	161	170		Plan Specific
Contribution Qualifier Type 8		1	171	171	L or C	Plan Specific
Contribution Qualifier 8		3	172	147		Plan Specific
Contribution Amount 8		10	175	184		Plan Specific
Contribution Qualifier Type 9		1	185	185	L or C	Plan Specific
Contribution Qualifier 9		3	186	188		Plan Specific
Contribution Amount 9		10	189	198		Plan Specific
Contribution Qualifier Type 10		1	199	199	L or C	Plan Specific
Contribution Qualifier 10		3	200	202		Plan Specific
Contribution Amount 10		10	203	212		Plan Specific
Contribution Qualifier Type		1	213	213	L or C	Plan Specific
Contribution Qualifier		3	214	216		Plan Specific
Contribution Amount		10	217	226		Plan Specific
Contribution Qualifier Type		1	227	227	L or C	Plan Specific
Contribution Qualifier		3	228	230		Plan Specific
Contribution Amount		10	231	240		Plan Specific

 

408 Record - Loan Payments

Principal Field Name	Standard 401k Field Name	Field Size	Field Start	Field End	Hardcode Value	Details
Record Type		3	1	3	408
Blank Space		1	4	4
Contract Number	PLAN ID	6	5	10
Blank Space		1	11	11
Social Security Number	SSN	9	12	20		Numeric Only
Blank Space		9	21	29
Investment Type		2	30	31	00
Transaction Type		4	32	35	0031
Pay Date	PAYROLL CHECK DATE	10	36	45		MM/DD/YYYY
Blank Space		11	46	56
Loan Number	LOAN ID	3	57	59		Plan Specific
Loan Payment Amount	LOAN REPAYMENT AMOUNT	10	60	69		Round - Force 2 decimals
						EBCDIC Signed
						Replace/Remove - decimal with nothing
						Pad Left - 0
Loan Principal Payment Amount		10	70	79
Blank Space		161	80	240

 

500 Record - Footer (included if client has several bank accounts on Principal's website)

Principal Field Name	Standard 401k Field Name	Field Size	Field Start	Field End	Hardcode Value	Details
Record Type		3	1	3	500
Blank Space		1	4	4
Contract Number	PLAN ID	6	5	10
Blank Space		1	11	11
Identification Number		9	12	20	000000000
Blank Space		122	21	142
ETF Code		7	143	149		Plan Specific
Blank Space		91	150	240