Stephen Smith's Blog

Musings on Machine Learning…

Posts Tagged ‘general ledger

Accounting in the Web with Sage 300

with 9 comments


We are just rolling out Sage 300 2016 which includes Web versions of Common Services, Bank Services, Tax Services, General Ledger, Accounts Receivables and Accounts Payables. The focus of this release isn’t on a new flashy portal with flashy KPIs (although we do have those) or other new technology enabled functionality. The focus is on the core Financial Accounting functionality; on entering financial accounting documents into the system and providing the key reports that go along with these.

This is the first of a series of releases that will translate the Sage 300 product line to a new Web based technology platform. It will move all the functionality of Sage 300 to this platform and use this new platform to add newer technologies and functionality.

This article is meant to give a quick overview and flavor for what you will see in our new Web UIs.

Common Services

A lot of Common Services are screens that enable functionality through the rest of the product. For instance the Optional Fields setup UI is present and is used to configure Optional Fields that then appear in so many other places. There are the usual basic common setup UIs for Company Profile and Calendar Services.


Bank Services is a part of Common Services and it provides a centralized place to manage your bank accounts. Reconcile bank statements and enter various bank related items like service charges. It also provides a common check printing UI for other applications to use when printing checks.


Tax Services is another part of Common Services that lets you configure your sales taxes in a single place and then they are used everywhere that involves sales taxes. You will see the full tax services support though out many Web UIs.


Sage Payment Solutions (SPS) is also configured from Common Services. We fully support Sage Payment Services for taking credit cards in A/R as part of this first release. We integrate to the Web versions of the SPS screens to take and process credit cards. As before no credit card information is stored in the Sage 300 database, its all handled by the SPS screens and saved in the SPS vault.


Although they aren’t supported by their own screens there is a lot of common functionality that goes across all the modules like Import/Export, Finders and processing custom Crystal reports. These are all present in the new Web UIs as well.

All these screens fully support multi-currency and fully honor Sage 300 security.

General Ledger

This module offers all the standard G/L screens. From here you can define your G/L Account structure, create and maintain your G/L Accounts and enter your G/L Journal Entries. Generally everything is G/L is based on fiscal year and period (which we provide a specialized control to enter) and allows you to follow GAAP rules exactly.


Accounts Receivable

Here you have all the screens to setup and maintain you’re A/R sub-ledger. You have screens to enter and maintain Customers. There are the main document entry screens of Invoice Entry and Receipt Entry. There is integration to SPS for credit card transactions. There is retainage accounting, optional fields, sales tax calculations and all the rich functionality you are used to.


Accounts Payable

Here you have all the screens to setup and maintain you’re A/P sub-ledger. You have screens to enter and maintain Vendors. There are the main document entry screens of Invoice Entry and Payment Entry. You can track vendor account and transaction details on screen and on printed reports. Accounts Payable produces the reports you need to avoid late payment charges, secure vendor discounts, and match cash requirements to cash resources. There is tight integration to Bank for printing and tracking checks.



Hopefully you will try out our new Web technology for the Sage 300 UIs. This moves Sage 300 to a fully supported technology platform that continues to evolve quickly. This first release offers lots of functionality across all of Financial Accounting with new releases coming quickly to provide the rest.

If you remember our last UI transition from CA-Realizer to VB6, you will see that this one is happening at a much quicker pace.

Since the Business Logic is still the same as the existing product, you know that this is a very full featured and robust set of accounting functionality that has been in use at a great many companies for quite a few years now.

For more information check out the online help at:



Value Added Application Features for Sage 300 ERP 2012

with 5 comments


With each release, besides the big features, we implement a number of common requests that customers have made through our ideas web site. This blog posting covers a number of these that will appear in our forthcoming Sage 300 ERP 2012 release.

Many of the items will appear in the Order Entry screen (OE1100).  Below is a screen shot where you can find most of the items mentioned for Order Entry.

Alternate Line Shading for Grid/List Controls

Many of the grid, list and table controls in Sage 300 ERP are quite wide and following a row from left to right can be difficult or require a lot of concentration. With the 2012 release all such table controls will display alternate line shading to make it easier to visually track horizontally.

Some tables in Sage 300 ERP are colorful, what about these? Actually the shading is done by taking 95% of the existing color, so the displayed grey is actually 95% of white. In more colorful tables this then gives the same effect in whatever color is being used. On a technical note, this will happen in anything implemented using either a grid or viewlist control.

Add Entered By Field to Order Entry

In the Orion project, we added the Entered By field to the database, with the intention of adding the field to the web version of Order Entry and the VB UI. We are not proceeding at this time with the web screen, but we will still include this field on the VB UI. The field will appear in the Header section of the UI, and will not be editable by the user.

The field will be added to the header section of the UI (not on the tabs) and will not be editable by the user. The field value will be the user name that first posted the order. If the order is subsequently changed and re-posted, the value in the Entered By field will still show the original user that posted the order.

Add On Hold Reason Field to Order Entry

In the Orion project, we added the On Hold Reason field to the database, with the intention of adding the field to the web version of Order Entry and the VB UI. We are not proceeding at this time with the web screen, but we will still include this field on the VB UI. The field will appear in the Header section of the document.

The field (250 characters) will be added to the header section of the document. Whether the field is editable is determined by the state of the On Hold field. If the order is On Hold, the On Hold Reason field will be editable. If the order is subsequently taken off hold, the On Hold Reason field will be blanked out and disabled.

Allow User to Copy Order in Order Entry

There is a Copy Orders UI in Order Entry that allows user to copy an order or range of orders. Users have requested that we allow the user to copy an order from the main Order Entry screen. So they would have an order open in Order Entry, and want the option to copy that order without having to go to the Copy Orders UI and then enter all of the details of that order for copying.

There should be a button at the bottom of Order Entry titled “Copy Order” that launches the Copy Orders UI with the details of the current order already defaulted to the fields on the UI. The user would only be able to do this on a posted order with no pending changes. The user would be able to edit the details of the order on the Copy Orders UI as they would normally be able to do.

Allow User to Create Purchase Orders in Order Entry

There is a Create PO’s from OE function in PO (PO5520) that users can use to create POs from OE requirements. The request is that users be able to create the PO’s directly in Order Entry, without having to go into the Create POs from OE UI and manually enter the details of the current order that they have to create POs for.

There should be a button at the bottom of Order Entry titled “Create PO” that launches the Create PO’s from OE UI with the details of the current order already defaulted to the fields on the UI. The user would only be able to do this on a posted order with no pending changes. The user would be able to edit the details of the order on the Create PO’s UI as they would normally be able to do.

Add Requested By Field to Order Entry

A request has been made to add a “Requested By” date field that would represent the date that the customer wants to have the goods delivered to their door. The field would be on both the header and details of the order, with the default being the same date as the Expected Ship Date. A user would get a warning if they entered a Requested By date that is before the Expected Ship Date. The Requested By date would also be on each detail line (since each detail line can be separately shipped).

Move Ship Via Code, Description and Tracking Number fields from the Customer Tab to the Order tab on Order Entry.

At TPAC, we received a number of requests from partners that we move the Ship Via Code and its description from the Customer tab on Order Entry to the Order tab.

In addition to doing the change to the Ship Via code and description fields, we will bring the Tracking Number field over, too, as they are related fields. In order to make room for this, we are going to move some of the fields around on Order Entry, as well as combine the Entered By (see above) and Source field (this was a suggestion by UCD).

Add Option to Exclude Inactive Accounts from the GL Chart of Accounts Reports

Currently, all Chart of Accounts Reports include will include all GL Accounts in the selected range, whether they are active or flagged as Inactive in GL. The request is that we add an option to the GL Chart of Accounts UI (GL4101) to allow users to exclude accounts that are flagged in GL as Inactive. By default, the reports will still print all accounts, but if the user selects to exclude inactive accounts, only Active accounts will be shown on the reports.

There will be a checkbox added to the UI (GL4101), and the reports (Detail – Short/Long Form, Valid Currencies, Allocation, Control Account Subledgers, Fiscal Set Comparison, and Rollup Accounts) will need to be changed to accept the new parameter and print the parameter (if selected) at the top of the report.

Add Ability to Filter by Range of Fiscal Years on GL Transactions Listing Report

Currently, the GL Transactions Listing Report (GL4103) allows users to select only one fiscal year, but a range of fiscal periods, as parameters on the report. The request is to allow users to select a range of both years and periods for the report.

The selectors for the year and periods on GL 4103 will be changed to Year/Period pickers to allow a From and To date range to include both the year and period. The Report will also be changed to accept the parameter and print the parameters at the top of the report.

Update 2012/06/26: It looks like this feature didn’t make code complete, so its being moved to Product Update 1.

Allow User to Print OE Invoices Directly from AR Customer Inquiry

The Customer Inquiry feature in Accounts Receivable allows users to view details of customer transactions, from both OE and AR. Currently, the user can only print documents from AR from Customer/Document Inquiry, although the user is able to drill down to the OE documents. The request is that the user be able to print an OE Invoice from Customer/Document Inquiry without having to go to OE first. There are several ways we could implement this. We could enable the user to drill down to the OE Invoice from Customer and/or Document Inquiry, and then enable printing of the invoice from the OE Invoice Entry UI (OE1900), OR we could place a button on the document inquiry UI allowing the user to print the OE Invoice (which would launch the OE Invoices form from that UI).

Allow User to Reverse GL Batches, Rather Than Just Single Entries

Currently, a user can reverse a single entry. There are multiple requests to allow users to reverse an entire batch.

We will change the Create Reverse Entry UI to allow users to optionally reverse an entire batch, or just a single entry. They will be able to customize the Entry Descriptions that will be used in the new reverse batch.

Exclude Inactive IC Items from IC Physical Inventory Reports

Pretty apparent, not much else to say.


These are just to show the smaller features that are often lost among the bigger items that are included in our releases.

Update 2012/06/07:

• There will not be a Copy Order button on OE1100. Instead, it will be an option on the file menu labeled “Copy Current Order”.
• The “Requested By” field will actually be labelled “Deliver By”.

Written by smist08

June 2, 2012 at 5:52 pm

Tables and Data Flow of the Accpac General Ledger Module

with 17 comments

This blog posting will be looking at the structure of some of the parts of the General Ledger module of the Sage ERP Accpac product. You can find the structure of the individual tables in the Accpac 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 are 60 tables in G/L, so obviously we can’t describe each in a single blog post. So 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 G/L can be categorized as:

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

General Ledger is the foundational accounting module in Sage ERP Accpac. All numbers eventually flow into G/L (sometimes by a round-about route). Below is a data flow diagram showing how all the other modules feed G/L. Then G/L has sufficient information to provide a complete audit history of a company as well as all the data to prepare Financial Reports.


The Setup Tables are:

GL01 (GL0005): Options. Various options for the G/L modules plus some contact information.

GLABK (GL0022): Segments. Defines all the account segments. For instance account segment region is 4 characters long. These are then the building blocks to build G/L account structures out of.

GLASV (GL0021): Segment codes. Holds the valid values for segments that are validated.

GLABRX (GL0023): Structure Codes. Builds G/L account structures out of combinations of segments.

GLSRC (GL0002): Source Codes. Defines the G/L source codes including a description.

GLSJN (GL0019): Source Journal Profile. Used to configure the source journals report. Specify which sources you want in a report.

GLRVAL (GL0020): Revaluation Codes. Configuration for performing currency revaluations.

GLGSSEG (GL0052): Segment Permissions. Defines segments a user has access to.

GLGSACC (GL0053): Account Permissions. Defines accounts a user has access to.

GLGSUSR (GL0054): GL Users. Users defined to G/L security.

GLACGRP (GL0055): Account Groups. Define the G/L Account Groups.

GLACHD (GL0057): Rollup Groups. Define the G/L Rollup Groups.

GLADSD (GL0058): Rollup Group Relationships.

Many of these setup tables are used to define the GL account structure. How these are used and interact is laid out in the following diagram:

Master Data

The main master data file is the GL chart of accounts table (GLAMF). This table has several detail tables also described below. The other master file is the GL fiscal sets files (GLAFS) which holds all the fiscal set balances by account, year and period. The fiscal sets are then used in financial reporting.

GLAMF  (GL0001): Chart of Accounts. The main list of all the G/L accounts. With detail views:

GLAIS (GL0004): Account Allocation

GLAVC (GL0012): Account Valid Currencies

GLCAS (GL0107): Sub-ledger Control Accounts

GLAMFO (GL0400): Account optional fields

GLAMFTO (GL0401): Account transaction optional fields

GLAFS (GL0003): Fiscal sets. Summarized fiscal set data, usually used for financial reporting. Strictly speaking this is also a detail of GLAMF, but tends to be used as the header when doing reporting.

Data Entry

All data that flows into G/L is via the set of Views/Tables: GLBCTL, GLJEH, GLJED (and some other composites).  All data from everywhere else in the system feeds into these tables. These are the unposted batches. Data is never written directly into the posted transaction or audit table files. All data goes here first and then posting is controlled by G/L.

GLBCTL (GL0008): GL Batch Control. One of these for each G/L Batch

GLJEH (GL0006): Journal Headers. One of these for each entry in a batch.

GLJED (GL0010): Journal Details. One of these for each detail line in an entry.

GLJEC (GL0007): Journal Comments. Holds the detail comment if there is one.

GLJEDO (GL0402): Journal Detail Optional Fields. Hold the optional fields for each detail line.

Limited validation is performed on these items as they are entered. This is because they are usually posted in their source application and cannot be edited there. Then in G/L data errors can be corrected by someone with sufficient privileges to allow the batch to be posted. Generally posting is the data integrity gate keeper here and not data entry.

Data Flow

Below is a diagram showing the data flow of data from outside of G/L into the G/L data entry views. Then it shows how posting takes that data and distributes it to the audit and other tables.

Data Processing

The GLJPST (GL0030) superview is the Accpac View that performs G/L batch posting. This view processes the specified batches, one entry at a time and posts them to the audit files and the fiscal set records. It posts each entry in a batch as a separate transaction. Some of the Views and Tables involved in posting are:

GLJPST  (GL0030): Batch Posting. Not a table, but the superview that performs the posting function.

Posting Journal Audit

GLPJD  (GL0016): Posting Journal Details.

GLPJC  (GL0017): Posting Journal Comments.

GLPERR (GL0015): Posting Errors.

GLPJDO (GL0404): Posting Journal Optional Fields.                            –

Posting Provisional Posting Audit. Provisional posting lets you provisionally (“temporarily”) post a batch to see what affect it has on Financial Reports or other G/L reports. These provisional posts don’t hit the regular posting audit files, but instead hit a set of provisional posting files. These are cleared the next time posting is run.

GLPPD  (GL0015): Provisional Posting Journal Details.

GLPPDO  (GL0406): Provisional Posting Journal Optional Fields.

GLPPER  (GL0013): Provisional Posting Errors.

Main Posted Transactions:

GLPOST (GL0018): Posted Transactions.

Periodic Processing

There are a large set of superviews that perform various periodic processing functions. These Views typically don’t have tables behind them and perform  maintenance type operations. Some like create new year or currency revaluation are quite major operations in their own right. Some of these include:

GLYRM (GL0024): Period End Maintenance. Clears history and/or detail.

GLINTCK  (GL0034): G/L Integrity checker (called from the Admin Service Integrity Checker).

GLINIT  (GL0106): Activation. Called from the Admin Services Activation program.

GLJAL  (GL0029): Auto Allocation Processor.

GLRVLU (GL0027): Currency Revaluation.

GLYRN (GL0026): Create New Year.

GLCSL  (GL0025): Consolidation of History.


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


Written by smist08

July 3, 2011 at 9:01 pm

Posted in sage 300

Tagged with , ,