Stephen Smith's Blog

Musings on Machine Learning…

Posts Tagged ‘payroll

New Payroll with Sage 300 ERP 2012 Product Update 1

with 3 comments


With the upcoming Product Update 1 for Sage 300 ERP 2012 we will be releasing new versions of Canadian and US Payroll. These now have the new names of Sage HRMS Canadian Payroll 2012 and Sage HRMS US Payroll 2012. These will be separate installations that you need to download along with the main Product Update 1. The new name is to reflect that this is the common Sage Payroll used in several Sage products (including Sage HRMS), but since this is based on Sage 300 ERP Payroll, for Sage 300 ERP users this will just appear as a new version. The benefit of sharing the Sage 300 Payroll is to pool some development resources to get further product enhancements incorporated to the core product.

There are quite a few new features across this offering, so I’ll quickly list them whether they apply to both Payrolls or just Canadian or just US.

Common Features

Payroll Tax Number: A new Tax Number field has been added to the Company tab on the Payroll Setup > Options window. You can assign a tax number for each company or database.

Common Deduction Limits: You can use this screen to associate multiple deductions based on a single limit and ensure that employee deductions do not exceed defined annual maximums. Multiple deductions can now be combined to allow a single annual maximum to be applied, removing the need for manual calculation of group limits for multiple deductions such as union dues, garnishments, and retirement plans. And of course there is a matching report.


General Ledger Accounts for Workers’ Compensation Groups: Employer expense and liability accounts from General Ledger can now be associated to a workers’ compensation group so that the expense and liability codes associated with that group post in the General Ledger batch file. With this enhancement, a complete picture is provided of the employer cost associated with each check run.

Workers’ Compensation Codes Modifications: Allow assignment of a G/L account to a Worker’s Compensation Group, and include:

Expense Account – The General Ledger expense account posts the employers’ portion of workers compensation.

Liability Account – The General Ledger liability account posts the employers’ portion of workers’ compensation.

Workers’ Compensation Transaction Reports: A new report format, G/L Transactions, lists the applicable assessment amounts of employee earnings and the amount of the General Ledger transaction, as well as the associated expense and liability accounts. This report also helps identify check details with missing G/L account associations. The Retrieve Assessments from Payroll Register option on the Workers’ Compensation report window allows processing of four new transaction reports. Existing reports have not been changed.

Transaction History:  With the introduction of workers’ compensation calculations, you can now enter any adjustments for workers’ compensation using the Transaction History window. The Transaction History window now contains additional fields needed for adjustments to workers’ compensation; the Base, Rate, and Assessment.

The Transaction History Report has been modified to include workers’ compensation information, applicable both as a total and on the employee level, detailing the employee’s earnings and the associated assessment amounts, bases, rates, and workers compensation groups/codes.

Cheque Printing Security Enhancements: Sage HRMS Payroll 2012 has been enhanced to improve security measures during the Cheque Printing and Posting process. With this update, users with security permissions that limit access to certain employees will no longer be able to print cheques for employees to which access is restricted.

Pre-Cheque Payroll Register and Payroll Registers: The Pre-Cheque Payroll Register and Payroll Register have both been modified to include workers’ compensation information when applicable, both as a total and on the employee level, detailing the employee’s earnings or hours and their associated assessment amounts, bases, rates, and group codes.

Employer Match Calculations for Timecard and Manual Check Entry: Two new fields are now available in Sage HRMS Payroll on the Timecards and Manual Checks windows. The new fields are available to allow you to temporarily change the employer contributions to the deductions during the Timecard or Manual Check entry process.

Expanded Cost Center Override: You can now define the cost center override for deductions with the Flat calculation method setting in the Earnings and Deductions setup window. Select the Cost Center Override Allocated Based on Calc Base check box on the Basic Info tab, and complete the Calc Base tab to apply the overrides. After an earning is associated with a flat deduction type, the deduction will then be allocated based on the associated earnings allocation.

International ACH Transfer (IAT): The new NACHA International ACH Transaction (IAT) rules require each transaction to be classified as foreign or domestic, allowing the depository financial institutions to identify whether the transactions meet their review requirements as outlined by the US Treasury Department. The IAT rules exist to identify the financial institution responsible for moving the funds rather than the originator or receiver of the funds. Any financial institution outside the territorial jurisdiction of the US that is involved in a payment transaction where the institution is moving the funds from the originator or receiver are required to transmit the transaction using the new IAT format.

Employee Work Location: A new tab, Work Location, was added to the Payroll Employees > Employees record window. On this tab, you can enter address information about an employee’s work location.

US Only Features

Sage Payroll Tax Forms and eFiling by Aatrix: This functionality provides updated reports and forms at no cost and enables you to fill out and file tax forms electronically for a small fee; including W-2, W-3, U.S. federal forms (941, 943, 944, and 945), U.S. state tax forms, and new hire reporting by state. The first time you sign in to Sage 300 ERP after installing Sage HRMS Payroll, Sage 300 ERP uses a secure connection to automatically register your company with Sage Payroll Tax Forms and eFiling by Aatrix.

Assign Tax to Employees: Instead of assigning taxes to one employee at a time, you can now assign a tax to multiple employees in a single operation. Selection criteria can be used to assign taxes to groups; for example, a state tax can be applied only to employees who live in a selected state.


The following new windows and reports have been added, moved, and consolidated to support Sage Payroll Tax Forms and eFiling by Aatrix:

  • New Federal Tax Filing window: You can use this new window to fill out federal tax forms for the purpose of printing or e-filing them.
  • New State Tax Filing window: You can use this new window to fill out state tax forms for the purpose of printing or e-filing them.
  • Moved 941 window: The 941 window has been removed from Government Reports, and now appears in the list of forms on the Federal Tax Filing window.
  • Consolidated W-2 Tax Filing window: The W-2s on Paper and W-2s on Disk(ette) windows have been replaced with a single W-2 Tax Filing window.

Child Support EFT: Sage HRMS Payroll has been enhanced to generate federally compliant Child Support EFT files for all states that require electronic submission to a State Disbursement Unit (SDU). A new tab, Garnishment, is available on the Payroll Employees > Employees record, which allows entry of data pertaining to a specific employee’s garnishment. The data entered on the Garnishment tab is used in the creation of EFT files using Generate EFT File while meeting Federal and State guidelines.

US Citizen Field: A new US Citizen field was added to the Class/Schd tab of the Payroll Employees > Employees record window. Selection of this option designates the employee as a US citizen.

Canadian Only Features

Support for Multiple QEIR and EIR Numbers per Employee: You can now enter both federal (EIR) and Quebec (QEIR) Employment Insurance rates for employees who are working between Quebec and other provinces. Sage HRMS Payroll calculates EIR if the Province of Employment field is set to provinces other than Quebec in the Employee record, and calculates QEIR if the Province of Employment field is set to Quebec. You will no longer have to add or remove EIR or QEIR depending on the employee’s work location.

Updated EFT Format For HSBC Bank: A new Bank format for HSBC was added in the Payroll Setup > EFT Options window.


People who are familiar with the internals of Sage 300 ERP may puzzle a bit about the versioning of this product. The official external version is 2012 like the rest of Sage 300 ERP 2012. However if you dig into the internal version numbers you will find that the main products are version 7.0A and then the Canadian tax tables are 7.0D and the US tax tables are 7.0G. So why is the version 7 when the internal version of everything else is 6.1A? The reason is that Sage HRMS Payroll is now the Payroll of several other Sage products and has its own versioning. Similarly why don’t the tax tables start at 7.0A, again the reason is that the earlier letters have already shipped with other products and that is where we are jumping in.


As you can see from all the features added and changes made that this is a fairly substantial update to the Payroll modules for Sage 300 ERP. Now that this is the standard Payroll module across several Sage products we should see more good progress. Certainly more than you would usually see associated with a Product Update.

Tables and Data Flow of the Sage 300 ERP Payroll Module

with 2 comments

This blog posting will be looking at the structure of some of the parts of the Canadian and U.S. Payroll modules for the Sage 300 ERP product (previously known as Sage ERP Accpac). You can find the structure of the individual tables in the Sage 300 ERP Application Object Model (AOM), which must be viewed in IE. However this doesn’t tell you how the tables are related or how data flows from one table to another as you post various transactions. There about 99 database tables in either the U.S. or Canadian Payroll module, so obviously we can’t describe each in a single blog post. We’ll just look at a few interesting cases. Understanding these relationships can be especially important to people writing sophisticated custom reports or BI Views.

The main types of database tables in Payroll can be categorized as:

  • Setup
  • Master Data
  • Data Entry
  • Data Processing
  • Periodic Processing

The Payroll modules feed data into both General Ledger and Project and Job Costing. Payroll also interacts very closely with the Bank module for the processing of paychecks. Each Payroll module contains a main common portion which is mostly shared; these contain all the files that start with cp or up. Then for each Payroll there is a Payroll calculation module which has the country specific tables, reports and calculations; these include the tables that start with: CT or UT. Often as the government changes the Payroll rules and rates, we just need to updated and distribute updated CT and UT applications rather than the larger core CP or UP application.

In the descriptions below we will list the U.S. Payroll table name and View IDs. However to get the Canadian Payroll versions, just replace the UP with CP and you have it. Most of these are compiled from the same source code (which uses PR) and then UP or CP are substituted in as part of the product build process.

Incidentally this is the same Payroll module that is used in the Sage HRMS SQL Payroll product, so everything here applies there as well.


Setting up Payroll correctly is the most crucial task to have things run smoothly. If all the employees and their earnings and deductions are configured correctly then processing Payroll will go much smoother. The Setup Tables are:

UPOPTS (UP0023): Options.

UPCLAS (UP0006): Class codes.

UPDTLM (UP0007): Earnings/deductions. (UPDTLX, UP0099 is UP0007, without some of its functionality for faster read access.)

UPINCL (UP0015): Include list. Used by UPTXMS, too. Holds the applicable taxes for each E/D.

UPDIST (UP0009): Distribution codes.

UPWCCH (UP0036): Worker’s compensation master.

UPWCCD (UP0037): Worker’s compensation codes.

UPOTSC (UP0022): Overtime schedules.

UPSHFT (UP0025): Shift differential schedules.

UPSHFD (UP0038): Shift Differential Details.

UPSHFB (UP0039): Shift Differential Billing Details.

UPTXMS (UP0029): Company payroll taxes.

UPINCL (UP0015): Include list.

UPDIST (UP0009): Distribution codes.

UPTXMO (UP0124): Taxes Optional Field Values.

UPWRKC (UP0027): Work Classification Codes.

UPOFH (UP0120): Optional Field Locations.

UPOFD (UP0121): Optional Field Values.

UPGLREF (UP0057): G/L Reference Intergration.

Master Data

The main master data file for Payroll is the Employees. Then secondarily we have Employee selection lists.

UPEMPL (UP0014): Employees

UPEMPD (UP0008): Employee earnings/deductions.

UPEMPT (UP0010): Employee taxes.

UPEMPC (UP0053): Employee notes.

UPEMBK (UP0201): Employee EFT Banks.

UPEMPO (UP0122): Employee Optional Field Values.

UPEMTF (UP0062): Employee Tax Fields (Implicitly handled by UP0014).

UPESLH                (UP0045): Employee selection lists.

UPESLD (UP0046): Employee selection list members.

There is no master of monetary balances as GL has.  The detail for all checks issued is kept in the following, which serves as a master and detail archive all in one.

UPCHKH (UP0048): Check Header. (Holds completed and un-posted checks.)

UPCHKD (UP0049): Check Details.

                UPCHDO (UP0134): Check detail optional field values.

                UPCHJB (UP0056): Check job details.

                                UPCHJO (UP0144): Check job details optional field values.

UPCHKC (UP0052): Check comment details.

UPCHKE (UP0202): Check EFT details.

UPCHHO (UP0133): Check header optional field values.

Data Entry

The main data entry task for Payroll is the entry of Timecards. A common integration is for ISVs to feed Timecard data into Payroll from an external system (like a time clock check-in system). You may also need to enter some manual checks.

UPTCHD (UP0031): Timecard headers.

UPTCDT (UP0032): Timecard details.

                UPTCDO (UP0128): Timecard detail optional field values.

                UPTCJB (UP0042): Timecard job details.

                                UPTCJO (UP0141): Timecard jobs optional field values.

UPTCHO (UP0127): Timecard optional field values.

UPMCHD (UP0019): Manual check headers

UPMCDT (UP0020): Manual check details.

                UPMCDO (UP0130): Manual check detail optional field values.

                UPMCJB (UP0043): Manual check job details.

                                UPMCJO (UP0142): Manual check job detail optional field values.

UPMCHO (UP0129): Manual check optional field values.

Data Processing

The Calculate Payroll process is the central part of Payroll. This takes all the setup, employee and timecard data and calculates the Payroll. This includes all taxes and deductions, sets up everything to print checks and advice and record all the values that need to be accrued.

UPCALC (UP0083): Superview to calculate payroll.

UPCLCO (UP0137): Calculate payroll optional field values. Since UPCALC has no data, these optional fields have no header.

UPCALE  (UP0084): E/D/T List, helper to UP0083

UPTXMC (UP0100): Include List, table helper, used internally in conjunction with Tax Master by UP0084.

UPCALT (UP0085): Timecard List, helper to UP0083.

UPCALA (UP0086): Cost Center Allocations (there is data here).

UPCHKR (UP0088): Check Run Header, temporary table helper to UP0083.

UPCTRL (UP0026): Concurrency Control.

UPXCPT (UP0064): Calculate Payroll Exceptions, produced by UP0083 if errors detected.

Once the calculate Payroll is performed, the next big process is printing all the checks.

UPPMAN (UP0065): Process Manual Checks (called by UP0078 , UI ‘Process’ button, and Process Icon).

UPCTRL (UP0026): Concurrency Control.

UPXCPT (UP0064): Calc Payroll Exceptions, produced by UP0065 if errors detected.

UPHHAU (UP0021): History Audit Headers.

UPHHOA (UP0135): History audit header optional field values.

UPHDAU (UP0024): History Audit Details.

                UPHDOA (UP0136): History Audit detail optional field values.

UPPCKS (UP0078): Print Checks, controls the Print process and calls Post (UP0050).

UPPCKF                (UP0089): Print Checks Trans, temporary helper to UP0078.

Updates the following staging tables for printing. When you print checks, part of the header is transfer to the BKCHK table which drives check printing. Then the Payroll Crystal check forms will join back to the following two tables to get all the Payroll specific data they need for the check and advice.

UPPCKH (UP0079): Print Checks Header.

UPPCKD (UP0080): Print Checks Details.

Post All Checks

Called internally by UP0078, UP0083 and UP0012.

UPPOST (UP0050): Post checks.

Updates the following tables as needed:

UPMCHD (UP0019): Manual Checks Header

UPMCDT (UP0020): Manual Checks Detail

UPCHKH (UP0048): Check Header

UPCHKD (UP0049): Check Detail

UPYTDS                (UP0047): Employee YTD summaries

CPEUIC (UP0030): Employee EI history (Canadian payroll only).

Periodic Processing

There are a set of superviews that perform various periodic processing functions. These Views typically don’t have tables behind them and perform maintenance type operations. Some of these include:

UPMAIN (UP0018):  Delete inactive records.

UPDCHK: (UP0016): Integrity checker.

Update All employees:

UPGMET (UP0058): Globally modify process.

Uses the following table:

UPGLBM (UP0051): Globally modify list.


Hopefully this blog posting provides a bit more insight into how Payroll operates and hopefully helps when you use Payroll, interface to Payroll or are creating custom Payroll reports.


Written by smist08

December 10, 2011 at 7:45 pm