Views:

The purpose of this integration is to import a benefit deduction file provided by a third-party provider, Cowan Insurance Group. The import will create the appropriate payroll setup information (for example, insert an employee rate record) to cause a benefit deduction to occur during normal payroll processing.

This integration also creates an export file to be sent back to Cowan Insurance Group noting actual payroll deductions. This is referred to as Payroll Reconciliation.

NOTE: This process follows the existing OTIP payroll import/export. In some cases, the integration uses OTIP objects to use as the base for the ONE-T functionality.

ONE-T Payroll Import Process

  1. If you are setting up the ONE-T Payroll Import for the first time, navigate to Departments > Administration > Application Setup > HRIS Exports and then run the Reset Definition task to add the appropriate fields to the ONE-T setup.

    Select Reset Definitions to Default and click OK.
  2. This adds the required fields to the ONE-T Setup page.
  3. Once a file has been received from Cowan Insurance Group, the employer should confirm their ONE-T import setups.
    1. In Microsoft Dynamics NAV, open the ONE-T Setup page. In the Search field, type ONE-T Setup and select the page from the drop-down list.
      The ONE-T Setup page displays.
  4. Expand the Payroll Import FastTab.
  5. Specify the following:
    1. In the Process File Folder Location field, specify the location where you will store the file provided by Cowan Insurance Group.
    2. In the Processed File Folder Location field, specify the location where the imported file is moved once it has been processed.
    3. In the Error Log File Folder Location field, specify the location for the error log file.
    4. In the Cycle No. field, specify a value equal to the last cycle number processed plus 1. This is used by Cowan Insurance Group to keep track of files being returned to them. The Cycle No. should be a sequential value with each file submitted to them.
    5. Add a checkmark to the Production check box if you are creating a production file to submit to Cowan Insurance Group. If this check box is disabled, then the file created will be for testing purposes.
  6. Confirm that all Payroll Controls have been setup that directly relate to the benefit deductions being imported by the file:
    1. Each Cowan Insurance Group Payroll Code (for example, 11, 13, 1, 6, 31, and so on) directly relates to a specific Payroll Rate and Payroll Control.
    2. In Rates under the Payroll, Setup, Calculation menu, verify that the Payroll Rate Codes used by the deduction payroll controls have been properly associated with the Payroll Code from Cowan Insurance Group. The Cowan Payroll Code is setup in the Benefit Integration Code field on Payroll Rates. The Benefit Integration Index field holds the field position for the rate in the import file.
    3. The import file will contain up to four different deduction amounts for a single Cowan Payroll Code. For example, for Extended Health Care (Cowan Payroll Code = 11), file position/index 6 is the Member Amount, file position/index 7 is the Member Retro Amount, file position/index 8 is the Employer Amount and file position/index 9 is the Employer Retro Amount.  
      NOTE: Both of the Employer amounts are for future use and do not need to be setup at this time.
    4. The following image shows a sample setup:
  7. Open the ONE-T Payroll Deductions page. In the Search field, type ONE-T Payroll Deductions and select the page from the drop-down list.
  8. From the Ribbon, click Payroll Import. 
    1. This process imports all files located in the directory specified in the To-Process File Location field. The report request page Test Import field only loads the data in the worksheet without creating employee rates. The report request page Update Employee Rates field causes the import process to replace already existing employee rates with new rates from the import file. The standard behavior is to skip existing records (based on rate code and effective date).
    2. The import process creates employee rates with amounts, unless Test Import is set to True.
    3. If there are any errors with the import process, individual lines that encountered an error during import will contain a check mark in the Has Error field.  
      Note: This field is not visible on the page by default.
    4. View the error log file and resolve any errors before moving to the next step. The error log file is found in the location specified in the Error-log location field on the Payroll Import FastTab of the ONE-T Setup page. After errors have been resolved, you can delete the entries in the worksheet and run the Payroll Import again to reload the file.
  9. Run Payroll and post the Payroll Journal.
    After payroll has successfully posted, populate the actual payroll deduction amounts in the ONE-T Payroll Deduction page.
  10. Open the ONE-T Payroll Deductions page, and from the Ribbon click Get Deduction Amounts.
    1. The Effective Dates on the report request page defaults to the month of the Payroll Effective Date value received in the import file from Cowan Insurance Group. This date is used to determine the start and end of the month. Once the month is known, the Payroll Ledger Entries are filtered by Pay Period Start Date that is within the month range. This determines what the posted deduction amounts are to report back to Cowan Insurance Group.
    2. Verify the data on the ONE-T Payroll Deductions page and confirm that the actual deduction amounts are correct.
  11. On the ONE-T Payroll Deductions page, click Payroll Reconciliation. This creates the export file to be sent back to Cowan Insurance Group. 

Payroll Codes

The following Payroll Codes are mapped to a Payroll Rate within Sparkrock NAV:

Code Description Taxable Benefit Paid By Trust 
11 Extended Health (EHC)  No
13 Dental (DEN) No
1 Basic Life (BLIF)  Yes, if trust paid 
6 Member Optional Life (OPTLF)  No
31 Optional Spouse Life (SPLIF)  No 
4 Optional Child Life (CHLF)  No
2 Basic Accident (BADD)  Yes, if trust paid 
3 Member Optional AD&D (OPADD)  No
30 Optional Spouse AD&D (SPADD)  No

Based on these Payroll Codes, you must set up new Payroll Controls which use employee rates for each deduction. There are a total of nine payroll codes, which will require 18 setups – one for the deduction and one for the related retro amount.

Note: Please setup Payroll Postings Groups pertaining to ONE-T and attach to the applicable ONE-T Payroll Controls.

These deductions will go to a different vendor than the OTIP deductions. Also, most school boards will prefer to have the retro amount deduction broken out on the pay stub, therefore you need to have separate payroll controls for the deduction and the retro amount.

Payroll Import File

The import file contains three parts:

  • a header record
  • transaction records
  • trailer record.

When the file is imported, the transaction records in the file Create Employee Rate Records (table 37032345).  The payroll rate code to use is determined from the Cowan Payroll Code. The Employee Rate Amount is determined from the Cowan import file and the position of the amount. The Cowan import file will contain a Member Amount and a Member Retro Amount. These amounts will be mapped to two different payroll rate codes.  

The Payroll Rate Benefit Integration Code and Benefit Integration Index fields are used to map the Cowan Payroll Codes to rates that have been setup. See the Payroll Rate Table section for a sample of this mapping.

This functionality uses the existing ONE-T Setup page, HRIS Export ONE-T Setup (23046110). This page reads data from the HRIS Export Setup Definition table. 

Import File Header Record

The header record of the import file contains four values:

Field Name Description Format NAV Usage
Interface ID Employer Group ID that identifies the file. Numeric

Example:
‘985000001’		 The import compares the value of the Interface ID field in the file to Field 1 (Employer Group ID) from the HRIS Export Setup Definition (23046103) for ONE-T setup.

If the values do not match, then the following error is issued to the user: “The Employer Group ID contained in the file does not match the ONE-T Setup Employer Group ID.”
Header Tag String identifying the header record. Character (3)

Example:
‘HDR’ constant		  
Interface creation date Date that the file was created. Date (8) (YYYYMMDD)

Example:
‘20160825’		  
Frequency File Frequency Character (10)

Example:
‘Monthly’ – use a setup field to control this value in case it changes.		  

 For example: 985000001,HDR,20160825,Monthly

Import File Transaction Record

The transaction record in the import file contains employee-specific deduction information. This information creates employee deductions for the benefits to which the employee belongs.

Field Name Description Format NAV Usage
Employer Group ID Used to indicate the Employer Group (Board).

This field is mandatory. 		 Numeric (9)

Example:
‘985000001’		 The import compares the value of the Employer Group ID in the file to Field 1 (Employer Group ID) from the HRIS Export Setup Definition (23046103) for ONE-T Setup.

If the values do not match, then the following error is issued to the user: “The Employer Group ID contained in the file does not match the ONE-T Setup Employer Group ID.”
Client ID Organizational identifier – Provincial Plan Policy Holder (Trust).  Numeric (3)

Example:
ONE-T = 985 constant		  
Class Member’s Class (Location).

For future use.		 Numeric  
Plan Member’s Plan Domain. Numeric (6) Based on the plan domain code from HRIS feed. This uses the same logic as in the HRIS Export ONE-T batch report (23046100).

This is the HRPLE "Member Plan". 
Payroll ID Employee ID.

This field is mandatory.
 		 Character (11)

Example:
‘7890123’		 The Payroll ID is the same Payroll ID sent for HRIS ONE-T.

Use the Employee Benefit ID field.
Payroll Code Payroll Code.

See the Import File Header Record table; 9 valid values.		 Numeric (2)  
Member Amount Regular amount of member share, including taxes.

Used as the amount in the Employee Rate table.

This field is mandatory.		 Decimal (10,2)

Example:
20.50, -20.50		  
Retro Member Amount Retro amount of member share, including taxes.

Used as the Amount in the Employee Rate table.

This field is mandatory.		 Decimal (10,2)

Example:
20.50, -20.50		  
Employer Amount For future use.

Set to 0.00		 Decimal (10,2)  
Employer Retro Amount For future use.

Set to 0.00		 Decimal (10,2)    
Payroll Effective Date Period start on the first of the month.

Used as the Effective Date in the Employee Rate table.

This field is mandatory.		 Date (8) (YYYYMMDD)

Example:
'19990415' for April 15, 1999		  

Example:  985000001,985,,985001,2,13,2.5,5,0,0,20180101 

Import File Trailer Record

Field Name Description Format NAV Usage
Interface ID Employer Group ID that identifies the file. Numeric

Example:
'985000001'		 The import compares the value of the Interface ID field in the file to Field 1 (Employer Group ID) from the HRIS Export Setup Definition (23046103) for ONE-T Setup.
Trailer Tag String identifying the trailer record. Character (3)

Example:
'TRL'		  
Interface Creation Date Date file was created. Date (8) (YYYYMMDD)

Example:
'20160825'		  
Trailer Record Count Validation Record count of transaction records in the file. This does not include header and trailer records. Numeric

Example:
'1345' for 1345 records		  

Example: 985000001,TRL,20160825,1345

Payroll Rate Table

This table has two fields added for the OTIP integration which are also used for the ONE-T integration. 

OTIP and ONE-T use separate rate codes and separate payroll controls.

  1. Use the Payroll Control Reporting Authority Code field to distinguish between payroll controls.
    Note: As part of this integration, we have renamed the OTIP_Integration Xref field and caption to Benefit Integration Code. We have also renamed the OTIP_Integration Index field to Benefit Integration Index.

The Benefit Integration Code field is used to store the Payroll Code (numeric value) from Cowan Insurance Group. The Benefit Integration Index field is used to determine which field position in the file the rate should be used with. For example, the Member Amount in the imported file uses position/index 6 and the Retro Member Amount uses position/index 7.

The following is a sample Payroll Rate setup for Basic AD&D, Basic Life, Child Life and Dental:

This data is used to filter the Payroll Ledger Entry table to determine actual deduction amounts.

In this example, the Payroll Control Code field should also be populated. This gives us an easier way to determine which payroll control codes are associated with each Benefit Integration Code and Benefit Integration Index value. This data is used to filter the Payroll Ledger Entry table to determine actual deduction amounts.

Notes: 

  • Row one shows the Index value (for display purposes only, the actual file does not have an index row). There are eleven fields total in the file.
  • Index 4 is the Payroll ID/Employee ID.
  • Index 10 is the Effective Date any new Employee Rates that are created.

Rules for Employee Rate Creation

  • The Employee Rate amount comes from a value in either Index 6 and/or Index 7 in the import file.
  • The value in Index 5 represents the Payroll Code (that is, benefit type) which is mapped to the Payroll Rate Benefit Integration Code field.
  • Look for a previous entry for the same Payroll Rate code in the Employee Rate table.
  • If no previous entry exists and the new value = 0, then skip and no employee rate is created.
  • If previous (Payroll Rate <> 0) and (New Rate = 0), then make a new entry with a new value and effective date from the ONE-T file.
  • If no previous entry exists and New Rate <> 0, make a new entry with a new value and effective date from ONE-T file.
  • Use the Payroll Rate code to populate the new entry using the new effective date and the value from the OTIP file.
  • If the amount from the import file is 0 AND we do not find a Payroll Rate setup matching the Benefit Integration Code and Benefit Integration Index, then skip and do not throw an error.
  • If the amount from the import file <> 0 AND we do not find a Payroll Rate setup matching the Benefit Integration Code and Benefit Integration Index, then issue an error in the error file.

Payroll Import File

Note: This object is run from the ONE-T Payroll Deductions page.

This function creates records in the ONE-T Payroll Deductions worksheet.  

The Payroll Import action on the ONE-T Payroll Deductions page will import all files found in the folder specified by the To-Process File Location field on the ONE-T Setup page.

If there are any errors during the import process, then the Has Error field is set to True. This field is not visible by default. The error file can be found in the folder specified by the Error-Log Location field on the ONE-T Setup page.

Payroll Reconciliation File

The Payroll Reconciliation file is created from the ONE-T Payroll Deductions worksheet. It creates an export file showing the Member Rates provided by Cowan for the current payroll and the actual payroll deduction amounts.

The name of the file is composed as follows: 

NatureofFile_PR_EmployerGroupID_Date_CycleNumber.csv 

Where:

  • NatureofFile is four characters with values = TEST or PROD.
  • PR is a constant to indicate the file is a payroll reconciliation.
  • EmployerGroupID matches the Employer Group ID in the transaction record.
  • Date is the date the file was generated. Format= yyyymmdd.
  • CycleNumber matches the Cycle Number in the header record. 

Example:

TEST_ PR_985000001_20180615_001.csv 
PROD_PR_985000001_20180615_801.csv

Export File Format

Field Name Description Format NAV Usage
Header Tag String identifying the header record. Value must be "HDR".

This field is mandatory.		 Character (3)

Example:
'HDR’		  
File ID Employer Group ID that identifies the file. This field must match the File ID in trailer.

This field is mandatory.		 Numeric (9)

Example:
‘985000001’		 The EmployerGroupID from HRIS Export ONE-T Setup (that is, Field 1 in the HRIS Export Setup Definition (23046103) table).
Cycle
Number		 This cycle should equal the last cycle number processed plus 1. If the expected cycle number exceeds the length of CYCLE_LEN, it will be reset to 1.

This field must be in the range of 1-999, and must follow the last file's cycle number.

This field is mandatory.		 Numeric (1-999)

Example:
'9'		 This comes from the Cycle No. field on the HRIS Export ONE-T Setup page. 
File
creation
date & time
validation		 Run date and time.

This field must be a valid date/time and must not be greater than current date/time.

This field must contain 14 digits with no spaces, and the value should be no later than 19000101000001.

 Date/time (14)
(yyyymmddhhmmss)

Example:
'20180615080130'

 

Example: HDR,985000001,1,20180615080130

Transaction Record File Format

Field Name Description Format NAV Usage
Payroll ID Employee ID number from the payroll system. This value is unique to the employee and cannot be changed. 

This field is mandatory.		 Character (11)

Example:
‘7890123’		 This is the Employee Benefit ID.
Payroll Code Payroll deduction code. This must be a valid Payroll Code from the Payroll Codes table. 

This field is mandatory. 		 Numeric (2)

Example:
11		  
Member 
Amount		 Member amount provided in the Payroll Export file. This field must be a decimal (zero is valid).

This field is mandatory.		 Decimal (10,2)

Example:
20.50, -20.50		 This is the Member Amount field from the ONE-T Payroll Deductions table.
Actual
Deduction		 Deduction amount from payroll. This field must be a decimal (zero is valid).

This field is mandatory.		 Decimal (10,2)

Example:
20.50, -20.50		 This is the Member Amount Deducted field from the ONE-T Payroll Deductions worksheet.
Cowan Retro
Amount		 Retro amount.This field must be a decimal (zero is valid).

This field is mandatory.		 Decimal (10,2)

Example:
20.50, -20.50		 This is the Retro Employee Amount field from the ONE-T Payroll Deductions table.
Actual Retro
Amount		 Retro amount.This field must be a decimal (zero is valid).

This field is mandatory.		 Decimal (10,2)

Example:
20.50, -20.50		 This is the Retro Member Amount Deducted from the ONE-T Payroll Deductions worksheet.
Payroll
Effective date		 Payroll Effective Date from the Payroll Export File. This field must match the date from the associated Payroll Export file.

This field is mandatory.		 Date (8)
(YYYYMMDD)		 This is the Payroll Effective Date from the ONE-T Payroll Deductions worksheet.

 Trailer Record File Format

Field Name Description Format NAV Usage
Trailer Tag This field should contain the string identifying the trailer record. The trailer
tag should be located in the first column of the record. The value must be 'TRL'.

This field is mandatory. 		 Character (3) 

Example:
'TRL'
 		  
File ID Employer Group ID that identifies the file. This field must match the File ID in the header.

This field is mandatory. 		 Numeric (9)

Example:
'985000001'		 The Employer Group ID from HRIS Export ONE-T Setup (that is, Field 1 in the HRIS Export Setup Definition table(23046103)).
Trailer Records
count validation		 The records count in the trailer does not include header & trailer records. This value must equal the total record count in the file.

This field is mandatory.		 Numeric

Example:
‘1345’ for 1345 records		  

Example: TRL,985000001,300 

Add a comment