Tables and Data Flow of the Sage 300 ERP Payroll Module
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:
- 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.
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.
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.
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).
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.