Sunday, May 20, 2012

Demystifying GP Tables and Transaction Flows

    

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

24 comments:

Anonymous said...

Hi,

This is a helpful post, but I have another question if I may.
I need to write custom views in SQL to speed up reports. I can translate the user table names to SQL physical names using the System table SY40501 in Dynamics:
SELECT * FROM dbo.SY40501
Is there any equivalent reference in the Dynamics tables for columns?
SY40503 looked promising, but wasn't what I need.
I have the SDK, and I can look things up from the Dynamics UI, but if such a list existed in the tables it would be so much faster.

Regards,

Richard

Dynamics Confessor said...

Hi Richard,

Thank you for your kind words. You inspire me!

Regarding column names. Check out Mark Polino's blog. He has an Excel spreadsheet that you can download that lists all of the columns in the tables, as well as the tables. Wow!

Kind regards,

Leslie

mhaines said...

Thanks for posting this very helpful document.

This is so well put together and answers a myriad of questions that save a ton of time.

Sincerely,

Mike

Dynamics Confessor said...

Thank you Mike,

Please let me know if you have any questions, or suggestions on other things you'd like to see covered.

Kind regards,

Leslie

Unknown said...

Leslie, nice post.

Dynamics Confessor said...

Thanks Soma,

This means a lot coming from you. I appreciate your taking the time to read it and to comment :)

Leslie

Unknown said...

hi, recently started working in dynamics support, and you blog is really helping me. Much appreciated.

Anthony Monteras said...

Hi Leslie i would like to know what is the difference between 0=New Transaction and 1= unposted in IV10000?

You stated that 0 is equivalent to now open file but when i check there is a transaction. thanks

Anthony Monteras said...

Hi i would like to know what is the difference between 0 = new transaction and 1= unposted in IV10000

Anthony Monteras said...

HI i would like to know the difference between new transaction and unposted , 0 and 1 in IV10000

Dynamics Confessor said...

Hi Anthony,

A WORK transaction has not yet been posted. I expect that is the same as a new transaction. 'New' is not a term used regarding GP transactions. To my knowledge, the IV10000 table is the transaction header to unposted Inventory Transactions. The IV10001 table is the line items of the unposted transactions. You need both the IV10000 and the IV10001 to complete the unposted Inventory "WORK" transactions.

Leslie

Dynamics Confessor said...

Anthony,

I looked in the IV10000 table for a field where the value could be 0 or 1. Which field (column) are you referring to? The SOURCEINDICATOR column stores a 0 if the transaction has not yet been saved, hence 'new'. It stores a 1 if the transaction has been saved in a batch, but not yet posted, hence 'work'. Is that the column you are referring to?

Leslie

MarkW101 said...

Great resource. You are a great contributor to the Dynamics GP community. I have learned a lot from reading your posts. It was great to Meet you in Reno a few years ago at GPUG.

Dynamics Confessor said...

Thank you for your kind words. If you go to any of the upcoming conferences, be sure to flag me down. I'd love to chat with you.
Kind regards,
Leslie

Unknown said...

Hi Leslie,
Thanks so much for your posting as I'm very new to GP this is super helpful.
I have a quick question. As you mentioned in SOP unposted document (Invoice, Return, Quote, Order, and Backorder) are in the WORK (SOP10100 & SOP10200) once they are posted will automatically move to HISTORY. However, only Invoice and Return can be posted.
Why in the HISTORY table there will be

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

What is the status mean? Should I not see Order, Backorder in HISTORY since they cannot be posted?

Another question is how come the Return in SOP30300 show as positive amount? Shall they show as negative amount?

Really appreciated your help.

Sue

Unknown said...

Hi Leslie,
Thanks so much for your posting as I'm very new to GP this is super helpful.
I have a quick question. As you mentioned in SOP unposted document (Invoice, Return, Quote, Order, and Backorder) are in the WORK (SOP10100 & SOP10200) once they are posted will automatically move to HISTORY. However, only Invoice and Return can be posted.
Why in the HISTORY table there will be

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

What is the status mean? Should I not see Order, Backorder in HISTORY since they cannot be posted?

Another question is how come the Return in SOP30300 show as positive amount? Shall they show as negative amount?

Really appreciated your help.

Sue

Dynamics Confessor said...

Hi Sue,
You are seeing exactly the correct things in the History table. While only the Invoice and Return can be posted, they are not the only transactions in the history table. The other documents automatically move to history as you've described.
1.Orders move to history when all of their items have been fully transferred to other docs.
2.Quotes move to history when any item is transferred to another document
3.Backorders move to history when all items have been fully transferred to other docs.
Return documents should show a positive amount, the system differentiates between what is positive and what is negative according to the document type. This is consistent with most of the modules in GP. You'll see the same thing in RM and PM.
Kindly,
Leslie

Unknown said...

Hi Leslie,

Thanks so much for the quick reply. I'm currently working on the project with Tableau. I connected SQL server directly to Tableau. My understanding is :

1. We cannot change the Return Documents amount in SQL database to negative
2. If I need to know the sales performance I should pull the data from SOP HISTORY table (ie. SOP30200 & SOP30300) since these are POSTED and CORRECT actual records. I should NOT pull data from SOP WORK table (SOP10100 & SOP10200) as these are unposted transactions.

Does that make sense? Please correct me if I am wrong.

Thanks so much for your help.

Sue

Dynamics Confessor said...

Hi Sue,
First, I want to admit to you know that I have never worked with Tableau. In fact, I had to look it up to figure out what it was :)

1. Correct, do not change any amounts anywhere in GP via SQL. Certainly not the SOP tables. SQL will let you do it, but you will not like what happens to the software and the accounting department will be really mad.

2. Correct, the SOP History tables hold posted sales & returns and the other items you noted in the previous comments. Be sure to exclude 'voided' transactions and consider any write-offs if you have them. Don't pull things from the SOP10XXX tables because they are unposted, correct again.

Another note, I don't know if anyone posts sales or adjustments directly in the receivables transaction entry window, but consider the RM Open and History tables if they do. SOP is not updated by anything posted through Receivables Management. Write offs, for example, are recorded in Receivables.

Live the dream!
Leslie

MarkW101 said...

FYI - If you void an invoice or return in RM that started out in SOP, the void status (VOIDSTTS) in SOP30200 does not get updated. I wrote a SQL job to synch with RM.

update SOP
set VOIDSTTS = 1
from ( select *
from RM20101
where RMDTYPAL = 1
and VOIDSTTS = 1) RM
join ( select *
from SOP30200
where SOPTYPE = 3
and VOIDSTTS = 0) SOP
on RM.DOCNUMBR = SOPNUMBE

update SOP
set VOIDSTTS = 1
from ( select *
from RM20101
where RMDTYPAL = 8
and VOIDSTTS = 1) RM
join ( select *
from SOP30200
where SOPTYPE = 4
and VOIDSTTS = 0) SOP
on RM.DOCNUMBR = SOPNUMBE

Dynamics Confessor said...

Hi,
Thank you for your comment and for sharing these scripts. If you wanted to run an update like this, you would also want to include the RM transaction history table -RM30101.

The fact that SOP is not updated when an invoice or return is voided in RM is not a bug. It's working as designed. The void status in SOP is used when you actually void the transaction in SOP. I wouldn't suggest voiding a SOP transaction in RM because your inventory balances will not be properly updated. If you are not using 'Sales Inventory' or 'Discontinued' items, it shouldn't be a problem, but be careful of where your sales reports are coming from.

Leslie

Dynamics Confessor said...

If you wanted to change the SOP30200 Void status using sql, I'd most likely do it in a single statement to deal with the work and history RM tables. I game up with this:

UPDATE SOP30200
SET VOIDSTTS = 1
FROM SOP30200
INNER JOIN ReceivablesTransactions ON SOP30200.SOPNUMBE = ReceivablesTransactions.[Document Number]
AND SOP30200.CUSTNMBR = ReceivablesTransactions.[Customer Number]
WHERE (ReceivablesTransactions.[Void Status] = 'Voided')
AND ReceivablesTransactions.[Document Type] IN ('Sales / Invoices', 'Returns')

MarkW101 said...

Ideally, the client should not void an invoice or return that came from SOP. Better to use a debit memo or credit memo in RM, or offset with another SOP transaction that would adjust the impact on inventory. I agree that in an inventory system, it can create confusion to update the VOIDSTTS to 1.

But if we are using this script on a regular basis, the thought is we do not need to worry about RM30101 as the issue would be captured before the document moves to history.

In my case, the client uses non-inventory items, and they were "fixing" the issue by bad business practices, of voiding the transaction in RM and re-issuing the invoice. This had the affect of doubling the sales reports, so we used the scripts above to handle the already poor situation, and keep the sales reports accurate. Eventually, we were able to correct the user's poor workflow.

Dynamics Confessor said...

Mark,
Congrats to you for being able to change the workflow. It's just so easy to void the invoice in RM, I think it was a good fix if you've got non-inventory items but still want to get your reports from SOP.
Leslie