Canadian Payroll Import

Sage300 Canadian Payroll Import (SAMACCCPTIME)

This integration illustrates how to import a very simple timesheet from Excel into Canadian Payroll.

The Excel sheet has columns to record hours for each day of the week i.e. Monday, Tuesday, etc. The integration uses a combination of the Hierarchy, Map & Aggregate transforms so that each ‘Day’ column is transformed to its own ‘Day’ record so that it can be imported as a ‘Timecard Detail’.

The integration has the flow of:

  1. Read
  2. Hierarchy
  3. Map
  4. Connector

Read

Extracts the data from the spreadsheet.

Hierarchy

Creates a two level hierarchy of Timecard header and the timecard details, where the details are still in a day per column format.

Map

The Timecard field in the Timecard transaction uses an IMan Counter to generate an incrementing timecard Id. Without this the Excel sheet would specify a unique timecard for every transaction.

GetCounterSequence("S300CPTIME")

This function calls the S300CPTIME Counter defined in the setup area of the IMan (created as part of the samples setup).

For each Timecard, this function generates a new sequential id, so the Counter function can be used to specify document numbers (as in this example below), customer numbers (for Sage300) and any other unique id.

The Timecard detail adds four fields to the detail transaction which will be used to translate the 'column per day' values to a 'record per day'.

The EarningType and Rate fields have the static values of SALARY and 10 assigned them respectively.

Aggregate Transform

The aggregate transform is where the columns are translated to their own records so they can be imported into Sage300 as shown with the Hours & Date field below.

  1. Click on the Field Mapping tab & change the Current Transaction Id to TimecardDetail. Attached to the TimeCardDetail are five ‘Calc Records’, one for each day.
  2. Select Monday and press the Edit button directly below.

  1. To set the Hour field of this record to Monday’s value the Hours field simply references the Monday column.

  1. A similar formula is used to calculate the Date of the transaction. The Date of the transaction is simply the %PeriodDate minus four days.

  1. Press the Cancel button to return back to the Field Mapping tab.
  2. Select Tuesday from the ‘Calc Records’ dropdown and press Edit.
  3. The Hours field now references Tuesday and the Date field, instead of substracting 4 from the PeriodEnd field subtracts 3.
  4. Repeated for Wednesday, Thursday & Friday.

Sage300 Connector

Maps the data into Sage300.