Deduction and Leave Preprocessor

Summary

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:

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.

Input File Information

Report Parameter ID

Enter a unique ID for this set of parameters.

Description

Enter a description for this set of parameters.

File

Enter the path and file name for the input file you are processing.

Alt File Location

Enter an alternative path and file name for the input file you are processing.

File Format

Use this drop-down box to select the file format of the input file you are processing.

The available formats are:

Truncated Values

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:

This field is enabled only when you select Comma-Separated Values as the File Format.

Sort Report by

Use the radio buttons in this group box to specify how you want to sort data on the report.

The options are:

Override Leave Type from Leave Table

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.

Process

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).

Print

Select this button on the toolbar to print the Audit Report and the Error Report (if errors exist).

Process/Print

Select this button on the toolbar to perform both the Process function and the Print function.

Processing Details

Input File and Error File Layout

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.

Input File Deduction Record (‘D’)

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

Input File Contribution Record (‘C’)

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

Input File Leave Record (‘L’)

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

Additional Processing Details

Processing Steps for Importing Input File Data into Costpoint Base Tables:

  1. Any existing rows in the worktables are deleted.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

  8. 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. .

  9. 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.

  10. 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.

  11. The Audit report is printed using the records in the audit table.

  12. The Error report is printed using the records in the error table.

Processing Error Messages

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.

Report Warning Messages

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.

Report Error Messages

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).

Tables

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