Demystifying GP Tables

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 readily available.

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.”

'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:

Several tables in the 90000 range

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 statuses:

1.     Unposted

2.     Posted

3.     Historical

These different statuses 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)

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).

Update for GP2013R2, You can now ‘unclose’ (reopen is probably a better term) a previously closed year and the transactions will indeed move back to the OPEN table (GL20000)

CM (Bank Transactions)

   HISTORY–

There is no HISTORY table

PM (Payables Management = A/P transactions)

   WORK–

Unposted PM Transactions

    OPEN–

Posted PM Transactions

Posted Purchasing Invoices

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

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)

   WORK–

Unposted RM Transactions

   OPEN–

Posted RM Transactions

Posted SOP Invoices

Posted SOP Cash Receipts

Voided RM Transactions

   HISTORY–

Posted and Fully Applied Receivables Transactions

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:

·       NSFchecks

·       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 “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

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

SOP invoices and returns automatically move to HISTORY as soon as they are posted. A quote moves to HISTORY when an item from the quote is transferred to an Order or Invoice. An Invoice moves to HISTORY if it is transferred to a backorder. A Backorder moves to HISTORY if it is transferred to an Order or Invoice. And on and on and on. For documents that do not automatically transfer to HISTORY, run the 'Reconcile-Remove Sales Documents' utility to move them. An expired Quote will never move to HISTORY. If you want it in HISTORY, you will need to move the record using a SQL statement. I normally create a stored procedure and run it on a schedule for my clients that want their expired quotes to move to the HISTORY table.

If you don’t want to move the Quotes to history, you must manually delete them. I would normally put them all in a single batch and then delete that batch. Once deleted you cannot get them back or look them up. There gone.

The following documents cannot be posted:

· Quotes

· Orders

· Backorders

Here’s the SQL statement I use to move Expired Quotes to HISTORY:

POP (Purchase Order Processing)

   WORK–

Any Purchase Order document that has not been moved to history

   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

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

    OPEN

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

   HISTORY

Posted Invoices

Posted Returns

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 StockCounts

Unposted Transfers

 OPEN

There is no open file

 HISTORY

Posted Transactions

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