Table Names and Transaction Flow
WORK to OPEN to HISTORY
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:
-
Technical Name -
Display Name -
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
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.
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 h
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:
- Unposted
- Posted
- 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!
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)
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
|
There is no open file (posted transactions update receivables, like SOP)
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)
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
|
There is no open file
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