Use this preprocessor to import employee deduction, contribution, and leave data from a Comma Separated Values (.CSV) or Fixed Length format ASCII text file into the corresponding employee tables in Costpoint.
Data is imported into the following Costpoint employee tables:
EMPL_DED
EMPL_CNTRB
EMPL_LV_ACCRL
Employee records must exist in Costpoint for the employees whose data is being imported. You must also set up employee deduction, contribution, and leave codes prior to using this preprocessor.
Note: You must have a license for the Payroll module to use this preprocessor.
Enter a unique ID for this set of parameters.
Enter a description for this set of parameters.
Enter the path and file name for the input file you are processing.
Enter an alternative path and file name for the input file you are processing.
Use this drop-down box to select the file format of the input file you are processing.
The available formats are:
Comma-Separated Values
Fixed-Length
Use this drop-down box to select the action to be performed when an input file field is too long for the target database column.
The options are:
Always truncate — If you select this option, values that are too long will always be truncated without a warning. A warning message will be written to the Error Report.
Never truncate — If you select this option, values that are too long will never be truncated. The record will be rejected and an error message will be written to the Error Report.
Warn before truncation — If you select this option, a warning will appear when a value that is too large is encountered in the input file. You will be give the option to truncate the value or reject the record.
This field is enabled only when you select Comma-Separated Values as the File Format.
Use the radio buttons in this group box to specify how you want to sort data on the report.
The options are:
Employee ID — Select this option to sort by employee ID.
Deductions, contributions, leave — Select this option to sort by data type.
Input Sequence — Select this option to sort by the sequence from the input file.
Select this checkbox to use the Leave Type associated with the Leave Code in the Costpoint Leave Table, instead of the Leave Type from the input file.
Do not select this checkbox if you want to use the Leave Type from the input file.
Select this button on the toolbar to read the input file, validate the data, and insert the data into the Costpoint employee tables (EMPL_DED, EMPL_CNTRB, and EMPL_LV_ACCRL).
Select this button on the toolbar to print the Audit Report and the Error Report (if errors exist).
Select this button on the toolbar to perform both the Process function and the Print function.
You supply the name for the input file. The error file has the same name as the input file with an “.ERR” extension. The input and error files share the same layout.
The input file can be a fixed-format file or a comma-separated file.
If the input file is a fixed-format file, you must fill every position with either an appropriate character or a space. It is not necessary to zero-fill numbers; you can use spaces to maintain the proper format.
If the input file is a comma-separated file, you do not need to fill every position. However, the file must contain the correct number of commas.
A record in the input file must be in one of three formats: Deduction Record (‘D’), Contribution Record (‘C’), or Leave Record (‘L’). The input file layouts are shown below.
Field Name |
Costpoint Table/Column |
Data Type and Size |
Required/Optional |
Format or Validation Table |
RECORD_CD
|
|
Character 1 |
Required |
‘D’ |
EMPL_ID |
EMPL_DED.EMPL_ID |
Character 12 |
Required |
From EMPL |
DED_CD |
EMPL_DED.DED_CD |
Character 6 |
Required |
From DED_CD |
S_DED_MTHD_CD |
EMPL_DED.S_DED_MTHD_CD |
Character 6 |
Required |
Valid Deduction Code Methods are set up on the Payroll Deductions screen. |
DED_RT_AMT |
EMPL_DED.DED_RT_AMT |
Numeric 12 |
Required |
S99999999.99 |
DED_ANN_CEIL_AMT |
EMPL_DED.DED_ANN_CEIL_AMT |
Numeric 10 |
Required |
S999999.99 |
DED_START_DT |
EMPL_DED.DED_START_DT |
Date 10 |
Optional |
mm/dd/yyyy format |
DED_END_DT |
EMPL_DED.DED_END_DT |
Date 10 |
Optional |
mm/dd/yyyy format |
DED_END_CVG_DT |
EMPL_DED.DED_END_CVG_DT |
Date 10 |
Optional |
mm/dd/yyyy format |
DED_PRIORITY_NO |
EMPL_DED.DED_PRIORITY_NO |
Numeric 2 |
Optional |
99 |
Field Name |
Costpoint Table/Column |
Data Type and Size |
Required/Optional |
Format or Validation Table |
RECORD_CD |
|
Character 1 |
Required |
'C' |
EMPL_ID |
EMPL_CNTRB.EMPL_ID |
Character 12 |
Required |
From EMPL |
CONTRIB_DED_CD |
EMPL_CNTRB.CNTRB_DED_CD |
Character 6 |
Required |
From DED_CD |
S_CNTRB_MTHD_CD |
EMPL_CNTRB.S_CNTRB_MTHD_CD |
Character 6 |
Required |
Valid Contribution Code Methods are set up on the Payroll Contributions screen. |
CNTRB_RT_AMT |
EMPL_CNTRB.CNTRB_RT_AMT |
Numeric 12 |
Optional |
S99999999.99 |
CNTRB_ANN_CEIL_AMT |
EMPL_CNTRB.CNTRB_ANN_CEIL_AMT |
Numeric 10 |
Optional |
S999999.99 |
CNTRB_START_DT |
EMPL_CNTRB.CNTRB_START_DT |
Date 10 |
Optional |
mm/dd/yyyy format |
CNTRB_END_DT |
EMPL_CNTRB.CNTRB_END_DT |
Date 10 |
Optional |
mm/dd/yyyy format |
Field Name |
Costpoint Table/Column |
Data Type and Size |
Required/Optional |
Format or Validation Table |
RECORD_CD |
|
Character 1 |
Required |
'L' |
EMPL_ID |
EMPL_LV_ACCRL.EMPLD |
Character 12 |
Required |
From EMPL |
LV_CD |
EMPL_LV_ACCRL.LV_CD |
Character 6 |
Required |
From LV |
LV_TYPE_CD |
EMPL_LV_ACCRL.LV_TYPE_CD |
Character 4 |
Required |
Required Valid Leave Types are set up on the Leave Type screen. |
LV_HIRE_DT |
EMPL_LV_ACCRL.LV_HIRE_DT |
Date 10 |
Optional |
mm/dd/yyyy format |
Processing Steps for Importing Input File Data into Costpoint Base Tables:
Any existing rows in the worktables are deleted.
The specified input file is opened and read; the record type is determined from the first character in the record (‘D’ = Deduction, ‘C’ = Contribution, and ‘L’ = Leave record). Costpoint performs data type validations on input file data; writes any errors found to error table; and inserts data into the appropriate work tables.
If the ‘Override Leave Types from Leave Table’ checkbox on the screen is checked, the LV_TYPE_CD field read from the Leave records is overwritten with the LV_TABLE. LV_TYPE_CD. The LV_TYPE_CD is set to Null if the input file’s LV_CD = LV_TYPE.LV_TYPE_CD and the employee’s COMPANY_ID is not equal to LV_TYPE.COMPANY_ID.
Various fields are checked to ensure that they do not contain Nulls or invalid data. Key fields are checked to ensure that they are valid keys. For each error found, the Line Number, Field Name, Field Value, and Error/Warning Message are written to the Error table.
The original row from the Input file is written to the Error file for each line number found in the error table. The corresponding input table records are also deleted.
The Image tables are populated with data from employee records in the input table for employees that already exist in the Costpoint database tables (EMPL_DED, EMPL_CNTRB, and EMPL_LV_ACCRL). The Image tables are essentially mirrors of the Input tables and will be used in the Audit report to show the user what information has changed.
The number of error and warning messages encountered in the input file, and the number of new and changed records, are counted. These stats are presented to the user in the Audit and Error reports.
The Audit report table is populated with the merged new and existing data values from the Input and Image tables. Records that show no changes to the existing data are deleted. .
Existing records are deleted from the Costpoint database tables (EMPL_DED, EMPL_CNTRB, and EMPL_LV_ACCRL) for any key combinations that are found to have changes in the Input tables. The new or changed data is then inserted into the Costpoint database tables from the Input tables.
The Audit report table is updated with the employee name (FIRST_NAME, MID_NAME, and LAST_NAME fields from the EMPL table) for each employee (EMPL_ID) in the report.
The Audit report is printed using the records in the audit table.
The Error report is printed using the records in the error table.
The following error messages may be displayed on the screen when you run this preprocessor. These errors will cause processing to terminate.
Each Deduction line in the input file should contain 10 fields. Line <Line Number> of your input file contains <Number of Fields> fields. Abort processing?
Comma Separated Values (.CSV) input file Deduction record (first character of record = ‘D’) contains greater than or less than 10 fields.
Each Contribution line in the input file should contain 8 fields. Line <Line Number> of your input file contains <Number of Fields> fields.
Comma Separated Values (.CSV) input file Contribution record (first character of record = ‘C’) contains greater than or less than 8 fields.
Each Leave line in the input file should contain 5 fields. Line <Line Number> of your input file contains <Number of Fields> fields.
Comma Separated Values (.CSV) input file Leave record (first character of record = ‘L’) contains greater than or less than 5 fields.
Record "D" of the input file should be 79 characters in length. Line <Line Number> of your input file contains <Line Length> characters.
Fixed-length input file Deduction record (first character of record = ‘D’) contains greater than or less than 79 characters.
Record "C" of the input file should be 67 characters in length. Line <Line Number> of your input file contains <Line Length> characters.
Fixed-length input file Contribution record (first character of record = ‘C’) contains greater than or less than 67 characters.
Record "L" of the input file should be 33 characters in length. Line <Line Number> of your input file contains <Line Length> characters.
Fixed-length input file Leave record (first character of record = ‘L’) contains greater than or less than 33 characters.
The record code (Position 1) must be "D", "C" or "L". Line <Line Number> of your input file contains <Position 1 Character>.
Fixed-length input file record does not contain a ‘D’, ‘C’, or ‘L’ as the first character.
The following error messages may appear on the Error Report when you run this preprocessor. If an error is encountered in the input file, the record causing the error will be rejected.
Value truncated.
Input Field Length > Maximum Field Length and Truncate Values is set to Always truncate (‘A’) on screen.
Value too long for field.
If Input Field Length > Maximum Field Length and Truncate Values is set to Never truncate (‘N’) on screen.
The following warning messages may appear on the Error Report when you run this preprocessor. If a warning is encountered in the input file, the record causing the warning will still be processed.
<Field name> '<field value>' on input file line <line number> is too long.
Input Field Length > Maximum Field Length and Truncate Values is set to Warn before truncation (‘W’) on screen.
Invalid Deduction Start Date.
DED_START_DT value read from Input File is not a valid date.
Invalid Deduction End Date.
DED_END_DT value read from Input File is not a valid date.
Invalid Ded End Coverage Date.
END_CVG_DT value read from Input File is not a valid date.
Rate is out of range $-99,999,999.99 to $99,999,999.99.
DED_RT_AMT value from Input File is a valid number and DED_RT_AMT value from Input File < -99999999.99 OR > 99999999.99.
Rate is not numeric.
DED_RT_AMT value from Input File is not a valid number.
Annual Ceiling is out of range $-99,999,999.99 to $99,999,999.99.
DED_ANN_CEIL_AMT value from Input File is a valid number and DED_ANN_CEIL_AMT value from Input File < -99999999.99 OR > 99999999.99.
Annual Ceiling is not numeric.
DED_ANN_CEIL_AMT value from Input File is not a valid number.
A positive Priority Number is required.
DED_PRIORITY_NO value from Input File is a valid number and DED_PRIORITY_NO value from Input File <= 0.
Priority Number is not numeric.
DED_PRIORITY_NO value from Input File is not a valid number.
Invalid Contribution Start Date.
CNTRB_START_DT value read from Input File is not a valid date.
Invalid Contribution End Date.
CNTRB_END_DT value read from Input File is not a valid date.
Rate is out of range $0.0000 to $99,999,999.99.
CNTRB_RT_AMT value from Input File is a valid number and CNTRB_RT_AMT value from Input File < 0 OR > 99999999.99.
Rate is not numeric.
CNTRB_RT_AMT value from Input File is not a valid number.
Annual Ceiling is out of range $0.00 to $99,999,999.99.
CNTRB_ANN_CEIL_AMT value from Input File is a valid number and CNTRB_ANN_CEIL_AMT value from Input File < 0 OR > 99999999.99.
Annual Ceiling is not numeric.
CNTRB_ANN_CEIL_AMT value from Input File is not a valid number.
Invalid Leave Hire Date.
LV_HIRE_DT value read from Input File is not a valid date.
Duplicate Deduction for Employee.
EMPL_ID and DED_CD in one record in table Z_AOPUTLDL_INP_D are the same as EMPL_ID and DED_CD in any other record.
Duplicate Deduction Priority for Employee.
PR_SETTINGS.DED_PRIORITY_FL = ‘Y’ and EMPL_ID and DED_PRIORITY_NO fields in one record in table Z_AOPUTLDL_INP_D are the same as EMPL_ID and DED_PRIORITY_NO in any other record and the DED_PRIORITY_NO is not equal to 99.
Deduction Priority for Employee already exists.
PR_SETTINGS.DED_PRIORITY_FL = ‘Y’ and the DED_PRIORITY_NO is not equal to 99 and EMPL_ID and DED_PRIORITY_NO fields in one record in table Z_AOPUTLDL_INP_D are the same as EMPL_ID and DED_PRIORITY_NO in another record in which the DED_CD fields differs from the DED_CD in the first record.
Duplicate Contribution for Employee.
EMPL_ID and CNTRB_DED_CD in one record in table Z_AOPUTLDL_INP_C are the same as EMPL_ID and CNTRB_DED_CD in any other record.
Duplicate Leave for Employee.
EMPL_ID and LV_CD in one record in table Z_AOPUTLDL_INP_L are the same as EMPL_ID and LV_CD in any other record.
Employee Id is a required field.
EMPL_ID is empty in the Z_AOPUTLDL_INP_D input table.
Deduction Code is a required field.
DED_CD is empty in the Z_AOPUTLDL_INP_D input table.
Deduction Method is a required field.
S_DED_MTHD_CD is empty in the Z_AOPUTLDL_INP_D input table.
Employee Id is a required field.
EMPL_ID is empty in the Z_AOPUTLDL_INP_C input table.
Contribution Code is a required field.
CNTRB_DED_CD is empty in the Z_AOPUTLDL_INP_C input table.
Contribution Rate/Amount is a required field.
CNTRB_RT_AMT is empty in the Z_AOPUTLDL_INP_C input table.
Contribution Method is a required field.
S_CNTRB_MTHD_CD is empty in the Z_AOPUTLDL_INP_C input table.
Employee Id is a required field.
EMPL_ID is empty in the Z_AOPUTLDL_INP_L input table.
Leave Code is a required field.
LV_CD is empty in the Z_AOPUTLDL_INP_L input table.
Leave Type is a required field.
LV_TYPE_CD is empty in the Z_AOPUTLDL_INP_L input table.
Invalid Deduction Method.
S_DED_MTHD_CD in the Z_AOPUTLDL_INP_D table is not one of the following: ‘FIXAMT', 'ADDGRS', 'GRSHRF', 'GRSHRP', 'NO DED', 'PCTANN', 'PCTGRS', 'PCTREG', 'REGHRF', 'REGHRP', ‘PCTCOD’, ‘GHRSPD’, ‘PCTDPI’.
Invalid Contribution Method.
S_CNTRB_MTHD_CD in the Z_AOPUTLDL_INP_C table is not one of the following:
'FIXAMT', 'GRSHRS', 'PCTDED', 'NO DED', 'PCTANN', 'PCTGRS', 'PCTREG', 'REGHRS', ‘PCTDED’, ‘REGHRF’, ‘REHRP’, ‘GRSHRF’, ‘GRSHRP’.
Invalid Date Range.
DED_START_ DT is Not Null and DED_END_DT is Not Null and DED_END_DT < DED_START_DT in table Z_AOPUTLDL_INP_D.
Invalid Date Range.
CNTRB_START_ DT is Not Null and CNTRB _END_DT is Not Null and CNTRB _END_DT < CNTRB _START_DT in table Z_AOPUTLDL_INP_C.
Out of Range 0.00 to 100.
The first three characters of the S_DED_MTHD_CD = 'PCT' and (DED_RT_AMT < 0 or DED_RT_AMT > 100) in table Z_AOPUTLDL_INP_D.
Out of Range 0.00 to 100.
The first three characters of the S_CNTRB_MTHD_CD = 'PCT’ and (CNTRB_RT_AMT < 0 or CNTRB_RT_AMT > 100) in table Z_AOPUTLDL_INP_C.
Too many decimal places.
S_DED_MTHD_CD is either 'FIXAMT' or 'ADDGRS' and DED_RT_AMT is not equal to the rounded value of DED_RT_AMT (2 decimal places).
Too many decimal places.
S_ CNTRB_MTHD_CD is either 'FIXAMT' or 'ADDGRS' and CNTRB_RT_AMT is not equal to the rounded value of CNTRB_RT_AMT (2 decimal places).
Employee ID is invalid.
Value in Z_AOPUTLDL_INP_D.EMPL_ID does not exist in EMPL_ID field of EMPL table.
Employee ID is invalid.
Value in Z_AOPUTLDL_INP_C.EMPL_ID does not exist in EMPL_ID field of EMPL table.
Employee ID is invalid.
Value in Z_AOPUTLDL_INP_L.EMPL_ID does not exist in EMPL_ID field of EMPL table.
Deduction code is not set up.
Not (Z_AOPUTLDL_INP_D.DED_FL = ‘Y’ and Z_AOPUTLDL_INP_D.EMPL_ID = EMPL.EMPL_ID and EMPL.COMPANY_ID = DED_CD.COMPANY_ID and Z_AOPUTLDL_INP_D.DED_CD = DED_CD_CORP.DED_CD and DED_CD_CORP.DED_FL = ‘Y’ and Z_AOPUTLDL_INP_D.DED_CD = DED_CD_CORP.DED_CD).
Contribution Code is not set up.
Not (Z_AOPUTLDL_INP_C.CNTRB_FL = ‘Y’ and Z_AOPUTLDL_INP_C.EMPL_ID = EMPL.EMPL_ID and EMPL.COMPANY_ID = DED_CD.COMPANY_ID And Z_AOPUTLDL_INP_C.CNTRB_DED_CD = DED_CD_CORP.DED_CD and DED_CD_CORP.CNTRB_FL = ‘Y’ and Z_AOPUTLDL_INP_C.CNTRB_DED_CD = DED_CD_CORP.DED_CD).
Leave Type/Leave Code combination is invalid.
Not (Z_AOPUTLDL_INP_L.LV_TYPE_CD = LV_TABLE.LV_TYPE_CD and Z_AOPUTLDL_INP_L.LV_CD = LV_TABLE.LV_CD and Z_AOPUTLDL_INP_L.LV_TYPE_CD Is Not Null and Z_AOPUTLDL_INP_L.LV_CD Is Not Null).
Cannot replace leave code when leave type exists.
Z_AOPUTLDL_INP_L.EMPL_ID = EMPL_LV_ACCRL.EMPL_ID and Z_AOPUTLDL_INP_L.LV_TYPE_CD = EMPL_LV_ACCRL.LV_TYPE_CD.
Deductions Cannot Be Processed – Not licensed for Payroll
Add-On license for Payroll (PR) is not found and RECORD_CD value read from input file = ‘D’.
Contributions Cannot Be Processed – Not licensed for Payroll
Add-On license for Payroll (PR) is not found and RECORD_CD value read from input file = ‘C’.
Union deductions cannot be assigned to individual employees.
The deduction code from the input file has a DED_CD_CORP.s_ded_type = ‘UDED’ (Union Deduction).
Arrears deduction not assigned to a deduction.
The deduction code from the input file has a DED_CD_CORP.s_ded_type = ‘ARR’ (Arrears) and the deduction code from the input file is not assigned to another DED_CD as an Arrears Deduction (Input file Deduction Code is not equal to DED_CD_CORP.ded_arrears_cd).
Method must be ADDGRS.
The deduction code from the input file has a DED_CD_CORP.s_ded_mthd_cd = ‘ADDGRS’ and the compute method from the input file is not equal to ‘ADDGRS’.
Method cannot be changed to ADDGRS.
The deduction code from the input file does not have a DED_CD_CORP.s_ded_mthd_cd = ‘ADDGRS’ and the compute method from the input file is ‘ADDGRS’.
Method cannot be changed to ADDGRS.
The deduction code from the input file has a DED_CD_CORP.s_ded_mthd_cd = ‘PCTCOD’ and the deduction code from the input file does not have a DED_CD_CORP.s_ded_type = ‘ADMFEE’ (Admin. Fee Court Orders).
The Deduction and Leave Preprocessor updates the following tables:
EMPL_DED
EMPL_CNTRB
EMPL_LV_ACCRL
The Deduction and Leave Preprocessor uses the following worktables for processing:
Z_AOPUTLDL_INP_D
Z_AOPUTLDL_INP_C
Z_AOPUTLDL_INP_L
Z_AOPUTLDL_IMG_D
Z_AOPUTLDL_IMG_C
Z_AOPUTLDL_IMG_L
Z_AOPUTLDL_ERROR
Z_AOPUTLDL_DATA
Z_AOPUTLDL_RPT1
Z_AOPUTLDL_TEST
Z_AOPUTLDL_EMP_L
Z_AOPUTLDL_CO_DED
Z_AOPUTLDL_CO_LV