ONE-T Payroll Import

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:

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:

 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.

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

Import File Trailer Record

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

Example: HDR,985000001,1,20180615080130

Transaction Record File Format

 Trailer Record File Format

Example: TRL,985000001,300 

Attachments (7)

Reset_20Defi....jpeg
16.1 KB

ONET_Setup.png
71.3 KB

example_20setup.png
27.5 KB

fields1.png
4.96 KB

fields_202.png
4.43 KB

example_payr....png
14.8 KB

excel_20sheet.png
16.1 KB