Demystifying GP Tables and Transaction Flows

    

Table Names and Transaction Flow

WORK to OPEN to HISTORY

Update of http://dynamicsconfessions.blogspot.com/2009/11/moving-from-work-to-open-to-history.html

This article started (as so many do) from a question posted on the newsgroup about how data flows through the system and how you can figure out what the tables contain. I did a little research and concluded there wasn’t any summary of data flow and table conventions available. This post is a result of that research.

Tables – what’s in a name?

First, a short explanation of table names is in order. Each table has three names. Sometimes they are the same, but most of the time they are different. The Dexterity programmer determines the names for the tables. The three different table names are:

1. Technical Name
2. Display Name
3. Physical Name

Technical Name

The Technical Name is the name used by the Dexterity programmers in Dexterity code. You will often see the Technical Name in error messages such as:

A Get/Change Operation on table 'UPR_Year_End_WORK_HDR'failed accessing SQL data.

In the above error message, 'UPR_Year_End_WORK_HDR' is the Technical Name for the Payroll Year End Header table.

Display Name

The Display Name is also known as the ‘friendly name’. Display Names use whole words with spaces between them. Display Names protect the user from needing to translate Technical Names or Physical Names into real words. You see Display Names when you are working with a tool that is normally used by the end user. Tools such as SmartList Builder, Excel Report Builder and Report Writer use Display Names.

The screenshot below shows a list of Display Names on the Add Table window of SmartList Builder:

 

Physical Name

The Physical Name is the name seen by the database. Any type of error at the SQL level, for instance, will use the Physical Name. The error message below uses the Physical Name:

 

Also, if you launch the Microsoft SQL Server Management Studio application you will see the Physical Names of the tables. The screen shot below shows you how the table names appear in the Microsoft SQL Server Management Studio.
 

Most of your independent reporting tools, like Crystal Reports, use Physical Names.

 

Tables –naming convention for physical tables

A client recently told me that they were very confused by the table names of the Dynamics GP tables. I’m so used to those legacy table names that I don’t appreciate what a wonder it must be for a new user trying to write reports or follow the data flow.

In fact, any class I train that involves pulling data from tables (Extender, SmartList Builder, Excel ReportBuilder, ListBuilder, Report Writer, SSRS, Crystal Reports, etc.) sooner or later results in the question “How do we know which table to use?”. This query spawned the on-line class I developed for GPUG (Great Plains User Group) entitled “Finding the Dynamics GP Data you Need”

So, what do the table names mean? The old table names were limited to eight characters; remember it was the late 1980’s when this was being developed. The field names were also limited to eight characters.
 

In order to bring some sanity to that eight-character limitation, a pretty straightforward naming convention was created. This convention was followed religiously for the modules actually written by Great Plains vs. those that were acquired from other developers.
 

I actually think it’s a very good naming convention for the Dynamics GP data tables. This is only a convention, however. It is followed by the Great Plains programmers pretty diligently, but not so much by 3rd party developers.
 

Here are the basics, the first 2 or 3 characters will indicate the module name, and the numbers indicate the type of table.
 

Some of the more popular module abbreviations are in the table below.

Prefix	Module
GL	General Ledger
AF	Advanced Financial Analysis
PM	Payables Management
RM	Receivables Management
SOP	Sales Order Processing
POP	Purchase Order Processing
IV	Inventory
IVC	Invoicing (NOT SOP)
UPR	US Payroll
CM	Cash Management (Bank Rec)
LK	Linked Transactions
ME	EFT
PA	Project Accounting
FA	Fixed Assets
AA	Analytical Accounting
DTA	Multi-dimensional Analysis *
SY	System or Company
AHR	Advanced HR
HR	Human Resources
BM	Bill of Materials
DD	Direct Deposit
EXT	Extender
MC	Multicurrency
SVC	Field Service
ASI	SmartList Favorites
ERB	Excel Report Builder
EXT	Extender
SLB	SmartList Builder
CPY	Canadian Payroll **
PDK	Personal Data Keeper **
*	Thanks to Victoria Yudin
**	Thanks to Beat Bucher

 

After the module abbreviation is a five character (normally 5 characters) number that indicates the table type.
 

Knowing what these numbers stand for will help you zero in on the correct table. The table below sets out the numbering convention for the table types.

Table Number	Table Type
00000	Master Tables
10000	Work Tables
20000	Open Tables
30000	History Tables
40000	Setup Tables
50000	Temp Tables
60000	Relation Tables
70000	Report Options Tables
80000	Posting Journal Reprint Tables
90000	Mixed bag – no standard type

 

Master Tables (00000) are mostly what you find under the ‘Cards’ area. These are your Customers, Vendors, Inventory Items, GL Accounts and the like. For instance, the information you see on the Customer Maintenance window is stored in the RM Customer MSTR or RM00101 table.

 

Work, Open and History (10000, 20000 and 30000) These tables are explained in the next section of this document.

 

Setup Tables (40000) include choices you have made to initiate a module. For instance, the information entered on the Payables Management Setup window is stored in the PM Setup File or PM40100 table.
 

Temp Tables (50000) are tables that are used temporarily by the system and the records in those tables can normally be deleted without issue. For instance, the Net Profit Temporary table or AF50000 is used by the Advanced Financial Analysis module to hold the Net Profit amount that will be used on one of the statements. Once the statement has been printed, the number is irrelevant.
 

Relation or Cross Reference Tables (60000) are tables that are used to store information that spans more than one module. For instance, the SOP/POP Link table (SOP60100) holds the information about POP documents linked to SOP documents.
 

Another example is the Sales Customer Item Cross Reference (SOP60300). This table reflects the information as to how customer item numbers relate to regular item numbers. There are not that many tables in the 60000 range.
 

Report Options Tables (70000) contain all of the information you enter in any of the report options windows. The image below comes from the Payables Trial Balance Report Options window (PM70500).

 

Posting Journal Reprint Tables (80000) contain all of the information you need to reprint your posting journals. So don’t feel like you MUST print all of those reports. They are here waiting for you should you ever need to reprint them.
 

Miscellaneous Tables (90000) this group is a mixed bag of information. The table below contains the Display Names of several 90000 tables.

 

Physical Name	Display Name
CM90000	CM Transmission Log
CM90001	Checkbook EFT Log
CN90000	Collections - User Preferences
ERB90100	Data Connection Products
ERB90200	Data Connection Series
ERB90300	Data Connections
ERB90400	Data Connection Restrictions
ERB90450	Data Connection Restriction Values
ERB90500	Data Connection Security
EXT90101	Product List
EXT90101	Resource Cache - Product List
EXT90102	Product Series List
EXT90102	Resource Cache - Product Series List
EXT90103	Form List
EXT90103	Resource Cache - Form List
EXT90104	Window List
EXT90105	Report List
EXT90106	Resource Cache - Table List
EXT90106	Table List
PA91301	PA Contract Segment Override Header
PA91304	PA Contract Segment Override Detail
PA92301	PA Contract Template Seg Override Header
PA92304	PA Contract Template Seg Override Detail
PDK90003	PDK File Error Log
PDK90100	PDK Security
PP900000	Deferral Opened Periods
PTO90000	PTO Pending Master Conversion
SE90001	Account Rollups Account List Accelerator
SE988977	Account Rollups Options Columns
SLB90000	Third Party GoTo Types
SY90000	SY_User_Object_Store (Dynamics User Object Store)
SY90100	Default Chart of Accounts

 

There are, of course exceptions even within the Dynamics.dic dictionary. Additionally, the DYNAMICS database has its own naming scheme. So now you know; if you were looking for the customer master table, you would start with the RM00*** table and go from there.

Victoria Yudin has some fantastic information on the popular tables from each module. If you are creating reports, or crafting SQL queries, it is information you can use http://victoriayudin.com/gp-tables/.

Victoria also has some very helpful SQL queries available for you to download.

Let’s move on to our WORK/OPEN/HISTORY discussion

 

Data Flow (WORK, OPEN and HISTORY progression)

I put together this document attempting to address the progression of transaction data for the following modules:

·         GL (Financial – general ledger journal entries)

·         CM  (Bank Transactions)

·         RM (Receivables Management – A/R transactions)

·         PM (Payables Management – A/P transactions)

·         SOP  (Sales Order Processing)

·         POP (Purchase Order Processing)

·         Invoicing (Basic invoicing, nothing to do with SOP)

·         Inventory
 

Dynamics GP transactions typically have three states:

1. Unposted
2. Posted
3. Historical
   These different states determine, to a large degree, whether the data is in the WORK, OPEN, or HISTORY table.
    

What causes the information to move through these tables varies. The progression is not consistent across modules and it’s easy to get bewildered trying to figure it out.
 

Below you will find a short summary of how the data moves through these tables. Also included under each section is a list of the more common tables for that module’s WORK, OPEN and HISTORY tables. The lists are not comprehensive, but they’ve captured the major tables.

GL (financial– general ledger)

WORK–

Unposted GL Transactions

 

Table Number	Description
GL10000	Transaction WORK
GL10001	Transaction Amounts WORK
GL10002	Transaction Clearing Amounts WORK
GL10100	Quick Journal WORK
GL10101	Quick Journal Amounts WORK
GL10300	General Ledger Tax WORK
GL12000	Budget Transaction WORK
GL12001	Budget Transaction Amounts WORK

A transaction lives in the Transaction WORK table before it has been committed to the ledger. You can still edit or delete a transaction when it is in the WORK tables.

 

OPEN–

Posted GL Transactions

Table Number	Description
GL20000	Year-to-Date Transaction OPEN

Once a transaction is posted, it is committed to the ledger. The header information is added to each line such that each leg of the journal entry is a complete record. The posted transaction moves to the transaction OPEN table. For GL, each record in the OPEN table represents a single leg of the journal entry.

 

HISTORY–

Transactions from a closed year

Table Number	Description
GL30000	Account Transaction HISTORY
GL32000	Budget Transaction HISTORY

Until the year closes all of the GL transactions are in the Year-to-Date Transaction OPEN table (GL20000).  I don’t mean when the year ends, but rather after the year-end closing routine is done. After the closing routine, the posted transactions move to the transaction HISTORY table (GL30000). You cannot move them back to the OPEN table after the year end closes. In addition, you cannot ‘unpost’ a transaction and move it back into the WORK table.
 

As with all rules, there are exceptions. Here's one. Using the Fiscal Periods Modifier tool in the Professional Services Tools Library you can indeed open a previously closed year. However, the data does not move from the Account Transaction HISTORY table (GL30000) over to the Year-to-Date Transaction OPEN table (GL20000).

CM (Bank Transactions)

WORK–

Unposted Deposit Transactions

Table Number	Description
CM10100	Deposit header information
CM10101	Each item to be deposited
ECM10003	ECM_Committed_Detail
ECM10002	ECM_CPO_Inquiry
ECM10001	ECM_Inquiry
ECM10110	ECM_Line_Ctrl
ECM10005	ECM_ReqItemList

 OPEN–

Posted Bank Transactions

Posted Deposit Transactions

Reconciliations

Table Number	Description
CM20203	Checkbook EFT Transaction Batch
CM20202	Checkbook Transaction Electronic Funds Transfer
CM20400	CM Distribution
CM20100	CM Journal
CM20300	CM Receipt
CM20501	CM Reconcile Adjustments
CM20500	CM Reconcile Header
CM20200	CM Transaction
CM20201	CM Transaction Totals
CM20600	CM Transfer

 

HISTORY–

There is no HISTORY table

PM (Payables Management – A/P transactions)

WORK–

Unposted PM Transactions

Table Number	Description
PM10100	PM Distribution WORK OPEN
PM10200	PM Apply To WORK OPEN File
PM10100	PM Distribution WORK OPEN
PM10400	PM Manual Payment WORK File
PM10201	PM Payment Apply To WORK File
PM10300	PM Payment WORK
PM10500	PM Tax WORK File
PM10000	PM Transaction WORK File

OPEN–

Posted PM Transactions

Posted Purchasing Invoices

 

Table Number	Description
PM10100	PM Distribution WORK OPEN
PM10200	PM Apply To WORK OPEN File
PM10100	PM Distribution WORK OPEN
PM20401	PM Scheduled Payment LINE
PM20400	PM Scheduled Payments Header
PM20000	PM Transaction OPEN File

Posted transactions stay in the OPEN table until they have been fully applied. OPEN means there is still an amount outstanding. If you write a check that partially pays an invoice, that invoice would remain in the OPEN table. The check, however, if its entire balance was applied, would move to the HISTORY table.

                

HISTORY–

Posted, Fully Applied, or Voided PM Transactions

Table Number	Description
PM30300	PM Apply To HISTORY File
PM30600	PM Distribution HISTORY File
PM30200	PM Paid Transaction HISTORY File
PM30401	PM Scheduled Payments Header HISTORY
PM30700	PM Tax HISTORY File
PM30800	PM Tax Invoices

Transactions automatically move to HISTORY when the document becomes fully applied (a transaction is a check, credit memo, invoice, etc.) For a check or credit memo, fully applied means the entire balance of the check or credit memo has been matched to an invoice. For an invoice, it is when the balance is fully paid off (or written off), or a credit memo takes the balance to zero. Voiding a document in the OPEN table will automatically move the voided document to HISTORY.

Documents in HISTORY can also be voided thereby moving them back to the OPEN table. Documents cannot be 'unposted' and moved back to the WORK table. Likewise, documents cannot be “unvoided” and moved back to the OPEN table.

RM (Receivables Management - A/R Transactions)

WORK–

Unposted RM Transactions

Table Number	Description
RM10201	RM Cash Receipts WORK File
RM10501	RM Commission WORK File
RM10101	RM Distribution WORK File
RM10301	RM Sales WORK File
RM10601	RM Tax WORK File

 

OPEN–

Posted RM Transactions

Posted SOP Invoices

Posted SOP Cash Receipts

Voided RM Transactions

Table Number	Description
RM10101	RM Distribution WORK File
RM20201	RM Apply OPEN File
RM20101	RM OPEN File
RM20400	RM Scheduled Payment Header
RM20401	RM Scheduled Payment Line

 

HISTORY–

Posted and Fully Applied Receivables Transactions

Table Number	Description
RM30501	Commission HISTORY
RM30301	Receivables GL Distribution HISTORY
RM30201	RM Apply HISTORY File
RM30502	RM Batch HISTORY
RM30101	RM HISTORY File
RM30401	RM Scheduled Payment Header HISTORY
RM30601	RM Tax HISTORY File

Unlike PM transactions, RM transactions do NOT automatically move to HISTORY. You need to run the 'Paid Transaction Removal' routine in order to move the fully applied transactions to HISTORY. Nothing automatically moves to HISTORY, not voids, not write-offs, not payoffs, nothing. Only 'fully applied' documents will be moved via the 'Paid Transaction Removal' routine.

Therefore, if you still have a balance on an invoice, it stays in the OPEN table. If a credit memo or receipt has not been matched up to an invoice, it will stay in the OPEN table. To get it out of the OPEN table and move it to HISTORY, run the 'Paid Transaction Removal' routine. The fact that the customer's net balance is zero does not impact whether or not the document moves to HISTORY.

When you run the Paid Trx Removal you specify a cutoff date that applies to:

     ·         NSF checks

     ·         Voided documents

     ·         Waived (finance charges)

     ·         Paid Transactions

You specify a separate date for:

     ·         Checks

Once RM transactions are moved to HISTORY - they cannot be voided thereby moving them back to OPEN. This is why the checks have a separate date - what if they bounce!
 

Documents can be 'unapplied' only while they are in the OPEN table, not after they have been moved to HISTORY. For instance, if you applied a cash receipt to the wrong invoice, you can change it to the correct invoice only if it has not been moved to HISTORY. Documents can be voided only while they are in the OPEN table. Voided documents stay in the OPEN table until they are moved to history via the 'Paid Transaction Removal' routine. You cannot void a document in the HISTORY table.
 

But here’s one HUGE exception.  Using the Professional Services Tools Library (PSTL) you can indeed “unapply” an amount from a document in HISTORY.  If, for example, an invoice had been completely paid off and moved to history and then you realize the cash was applied to the wrong invoice. Using the (PSTL) tool you can ‘unapply’ the cash thereby moving both the cash receipt and the invoice back into the OPEN table.
 

Once you have the documents back in the OPEN table you can apply the check to the correct invoice, void the document, write off the document or do anything else you can ordinarily do with an OPEN document.

SOP (Sales Order Processing)

WORK–

Unposted SOP documents

Table Number	Description
SOP10100	Sales Transaction WORK (SOP Header)
SOP10200	Sales Transaction Amounts WORK (SOP Line Items)
SOP10102	Sales Distribution WORK and HISTORY (Debits and Credits)
SOP10202	Sales Line Comment WORK and HISTORY (Comments on Line Items)
SOP10101	Sales Commissions WORK and HISTORY
SOP10203	Sales Order Bin Quantities WORK and HISTORY
SOP10103	Sales Payment WORK and HISTORY
SOP10201	Sales Serial/Lot WORK and HISTORY
SOP10105	Sales Taxes WORK and HISTORY
SOP10106	Sales User-Defined WORK HISTORY

An unposted document includes not only Invoices and Returns, but also Quotes, Orders and Backorders.
 

OPEN–

There is no OPEN file

When a SOP document is posted, the transaction moves to the RM OPEN table and the SOP document moves to the SOP HISTORY table. Only Invoices and Returns can be posted. The posted SOP document becomes an open receivable (or credit) in the RM OPEN table.
 

HISTORY–

Posted SOP Invoices or SOP Returns

Voided SOP Documents

Orders whose items have been fully transferred to other documents

Quotes that have had any item transferred to another document

Backorders that have been fully transferred to other documents

Table Number	Description
SOP30200	Sales Transaction HISTORY (Header information)
SOP30300	Sales Transaction Amounts HISTORY (Line item information)
SOP30201	Sales Deposit HISTORY
SOP10102	Sales Distribution WORK and HISTORY (Debits and Credits)
SOP10202	Sales Line Comment WORK and HISTORY (Comments on Line Items)
SOP10101	Sales Commissions WORK and HISTORY
SOP10203	Sales Order Bin Quantities WORK and HISTORY
SOP10103	Sales Payment WORK and HISTORY
SOP10201	Sales Serial/Lot WORK and HISTORY
SOP10105	Sales Taxes WORK and HISTORY
SOP10106	Sales User-Defined WORK HISTORY

SOP invoices and returns automatically move to HISTORY as soon as they are posted. For documents that do not automatically transfer to HISTORY, run the 'Reconcile-Remove Sales Documents' utility to move them.
 

The following documents cannot be posted:

·         Quotes

·         Orders

·        Backorders

POP (Purchase Order Processing)

WORK–

Any Purchase Order document that has not been moved to history

Table Number	Description
POP10100	Purchase Order WORK
POP10110	Purchase Order Line
POP10300	Purchasing Receipt WORK
POP10310	Purchasing Receipt Line
POP10150	Purchase Order Comment
POP10160	Purchase Order Tax
POP10340	Purchasing Bin Quantities WORK
POP10550	Purchasing Comment
POP10390	Purchasing Distribution WORK
POP10700	Purchasing Landed Cost
POP10140	Purchasing Manufacturer Numbers
POP10500	Purchasing Receipt Line Quantities
POP10306	Purchasing Receipt User-Defined
POP10330	Purchasing Serial Lot WORK
POP10600	Purchasing Shipment Invoice Apply
POP10360	Purchasing Tax

OPEN–

There is no open file
 

HISTORY–

Posted Receipts automatically move to history

Posted Purchasing Invoices automatically move to history

Voided Purchasing Documents automatically move to history

Closed Purchase Orders

Cancelled Purchase Orders

Table Number	Description
POP30100	Purchase Order HISTORY (PO Header)
POP30110	Purchase Order Line HISTORY (PO Line Items)
POP30300	Purchasing Receipt HISTORY (Receipt Header)
POP30310	Purchasing Receipt Line HISTORY (Receipt Line Items)
POP30160	Purchase Order Tax HISTORY
POP30000	Purchasing Batch HISTORY
POP30340	Purchasing Bin Quantities HISTORY
POP30390	Purchasing Distribution HISTORY
POP30700	Purchasing Landed Cost HISTORY
POP30330	Purchasing Serial Lot HISTORY
POP30360	Purchasing Tax HISTORY

Posted Receipts, Posted Purchasing Invoices and Voided Purchasing Documents automatically move to history. You must run the Purchasing Routine 'Remove Completed Purchase Orders' to move all of the closed or canceled POs to HISTORY. If a PO is not closed or canceled it will not be moved, so there is no danger of moving a PO that doesn't qualify. You can set restrictions on which POP documents are examined for removal. It is not an ‘all or nothing’ kind of routine.

IVC (Basic Invoicing – not SOP)

WORK

Unposted Documents

Table Number	Description
IVC10400	Invoicing Commissions
IVC10300	Invoicing Distributions
IVC10103	Invoicing Line Comments
IVC10200	Invoicing Payments WORK
IVC10102	Invoicing Serial and Lot Number WORK
IVC10500	Invoicing Taxes
IVC10101	Invoicing Transaction Amounts WORK
IVC10100	Invoicing Transaction WORK

 

OPEN

There is no open file (posted transactions update receivables, like SOP)
 

HISTORY

Posted Invoices

Posted Returns

Table Number	Description
IVC30102	Invoicing Transaction Amounts HISTORY
IVC30101	Invoicing Transaction HISTORY
IVC10400	Invoicing Commissions
IVC10300	Invoicing Distributions
IVC10500	Invoicing Taxes
IVC10103	Invoicing Line Comments
IVC10102	Invoicing Serial and Lot Number WORK

Invoices and Returns automatically move to the HISTORY table when posted. Like SOP, when and invoice or return is posted, the customer transaction record moves into the RM OPEN table. The transaction, therefore becomes an open receivable or credit.

IV (Inventory)

WORK

Unposted Adjustments

Unposted Variances

Unposted Stock Counts

Unposted Transfers

 

Table Number	Description
IV10004	Inventory Bin Quantity Transfer
IV10201	Inventory Purchase Receipts Detail
IV10200	Inventory Purchase Receipts WORK
IV10002	Inventory Serial and Lot Number WORK
IV10001	Inventory Transaction Amounts WORK
IV10003	Inventory Transaction Bin Quantities WORK
IV10000	Inventory Transaction WORK

OPEN

There is no open file
 

HISTORY

Posted Transactions

 

Table Number	Description
IV30500	Inventory Distribution HISTORY
IV30300	Inventory Transaction Amounts HISTORY
IV30100	Inventory Transaction Batch HISTORY
IV30302	Inventory Transaction Bin Quantities HISTORY
IV30301	Inventory Transaction Detail HISTORY
IV30200	Inventory Transaction HISTORY
IV30600	Item Lot Attribute HISTORY
IV30400	Item Serial and Lot Number HISTORY

 

Inventory transactions are automatically moved to history when posted.

That's it! Please let me know of any adjustments you think I need to make to this.
 

Until next post!
 

Leslie