Reconciling the General Ledger with the Payables Subledger Using SmartList Builder

For those of you who have clients that use SmartList Builder, you can create a SmartList that will help those clients more easily reconcile payables to the general ledger in Great Plains.

Specifically, this SmartList displays the Payables Batch ID from the PM30200 table (the PM Paid Transaction History Table) as a column alongside the General Ledger Journal Entry column. The SmartList is of a similar configuration as the Current Purchasing Journal already in SmartList, except that this SmartList has the Payables Transaction Entry Batch ID as a column.

The only other way to easily reconcile payables to the general ledger is to modify the Cross-Reference report in Report Writer to contain the payables audit trail code that begins with “PMTRX”.  But, some controllers identify more easily with payables Batch IDs than with audit trail codes. That’s why the creation of this SmartList becomes important.

Build the SmartList on the following three tables:

1. GL YTD Transaction Open (GL20000),
2. PM Paid Transaction History File (PM30200), and
3. Account Index Master (GL00105).

The PM Paid Transaction History File is joined to the GL YTD Transaction Open table by the Equal Link Method, and the Account Index Master table is also joined to the GL YTD Transaction Open table by the Equal Link Method.

The Account Index Master table and the GL YTD Transaction Open table are linked by the Account Index field within each of the two tables, while the PM Paid Transaction History File table and the GL YTD Transaction Open table are linked in the following matter:

1. The Vendor ID field in the PM Paid Transaction History File table is linked to the Originating Master ID field in the GL YTD Transaction Open table
2. The Posting Date field in the PM Paid Transaction History File table is linked to the TRX Date field in the GL YTD Transaction Open table
3. The Voucher Number in the PM Paid Transaction History File table is linked to the Originating Control Number in the GL YTD Transaction Open table

The field for “Display” and “Default” from the Account Index Master is Account Number String; the fields for “Display” and “Default” from the PM Paid Transaction History File table are Batch Number and Transaction Description; and the fields for “Display” and “Default” from the GL YTD Transaction Open table are Journal Entry, Transaction Date, Series, Originating Control Number, Originating Master Name, Originating Document Number, Debit Amount, and Credit Amount.

These fields can be renamed in SmartList Builder to be more meaningful to the client.  For instance, the Originating Master Name field in the GL YTD Transaction Open table can be renamed “Vendor Name”.

Also, create a restriction in SmartList Builder to only pull in the “PMTRX” source document since the client is only concerned about transactions posted from Payables Transaction Entry and not from Purchase Order Processing or some other module.  In addition, restrict the Series as “Purchasing”.

The following shows SmartList Builder, after this work is completed:

Within the SmartList itself, you can restrict the TRX Date based on a monthly range.

In addition, you may want to have the columns renamed and arranged in the following order from left to right:

Journal Entry, GL Transaction Date, Debit Amount, Credit Amount, Account Number, Payables Batch ID, Vendor Name, Payables Voucher Number, Payables Document Number, and Payables Transactions Description.

Here is an illustration of SmartList, after using SmartList Builder:

Now, if the client does not pay its posted payables transactions within a regular monthly cycle, then these transactions will reside in the PM Transaction OPEN File table (PM20000).  So, instead of using the PM Paid Transactions File table (PM30200), you would use the PM Transaction OPEN File table instead.

To learn more about how you can take advantage of this and other Dynamics GP features, visit RSM’s Microsoft Dynamics GP resource. To make sure you stay up to date with the Microsoft Dynamics Community, subscribe to our Microsoft Dynamics Community Newsletter.For more information on Microsoft Dynamics 365, contact us.

By: John Ellis

RSM Staff