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:
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, ReportWriter, 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 |
|
WDC |
Field-Level Security |
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) 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.
Several tables in the 90000 range
|
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/.
MVP Victoria Yudin 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 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)
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 (after running the year-end closing routine)
|
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).
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)
WORK–
Unposted
Deposit Transactions
|
Table Number |
Description |
|
CM10100 |
Deposit header information |
|
CM10101 |
Each item to be deposited |
|
ECM10003 |
ECM_Commited_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 |
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)
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:
·
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
|
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. 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:
/*******************************************************************
Leslie Vail
08/27/2012
This statement moves expired quotes from the SOP WORK tables to
the SOP HISTORY tables. BE SURE TO CHECK THE TABLES JUST IN
CASE NEW FIELDS WERE ADDED WITH UPDATES. THIS WAS HOW THE TABLES LOOKED IN 2012
******************************************************************/
--LINE
--copy line items from expired quotes to history
BEGIN TRANSACTION
INSERT INTO SOP30300
(SOPTYPE, SOPNUMBE, LNITMSEQ, CMPNTSEQ, ITEMNMBR, ITEMDESC, NONINVEN,
DROPSHIP,
UOFM, LOCNCODE,
UNITCOST,
ORUNTCST,
UNITPRCE,
ORUNTPRC,
XTNDPRCE,
OXTNDPRC,
REMPRICE,
OREPRICE,
EXTDCOST,
OREXTCST,
MRKDNAMT,
ORMRKDAM,
MRKDNPCT,
MRKDNTYP,
INVINDX,
CSLSINDX,
SLSINDX,
MKDNINDX,
RTNSINDX,
INUSINDX,
INSRINDX,
DMGDINDX,
ITMTSHID,
IVITMTXB,
BKTSLSAM,
ORBKTSLS,
TAXAMNT,
ORTAXAMT,
TXBTXAMT,
OTAXTAMT,
BSIVCTTL,
TRDISAMT,
ORTDISAM,
DISCSALE,
ORDAVSLS,
QUANTITY,
ATYALLOC,
QTYINSVC,
QTYINUSE,
QTYDMGED,
QTYRTRND,
QTYONHND,
QTYCANCE,
QTYCANOT,
QTYORDER,
QTYPRBAC,
QTYPRBOO,
QTYPRINV,
QTYPRORD,
QTYPRVRECVD,
QTYRECVD,
QTYREMAI,
QTYREMBO,
QTYTBAOR,
QTYTOINV,
QTYTORDR,
QTYFULFI,
QTYSLCTD,
QTYBSUOM,
EXTQTYAL,
EXTQTYSEL,
ReqShipDate,
FUFILDAT,
ACTLSHIP,
SHIPMTHD,
SALSTERR,
SLPRSNID,
PRCLEVEL,
COMMNTID,
BRKFLD1,
BRKFLD2,
BRKFLD3,
CURRNIDX,
TRXSORCE,
SOPLNERR,
ORGSEQNM,
ITEMCODE,
PURCHSTAT,
DECPLQTY,
DECPLCUR,
ODECPLCU,
EXCEPTIONALDEMAND, TAXSCHID, TXSCHSRC, PRSTADCD, ShipToName, CNTCPRSN, ADDRESS1, ADDRESS2, ADDRESS3, CITY, STATE, ZIPCODE, CCode, COUNTRY, PHONE1, PHONE2, PHONE3, FAXNUMBR, Flags, CONTNBR, CONTLNSEQNBR,
CONTSTARTDTE, CONTENDDTE, CONTITEMNBR, CONTSERIALNBR, ISLINEINTRA)
SELECT
/* BE SURE TO CHECK THE TABLES JUST IN CASE NEW FIELDS WERE ADDED
WITH UPDATES. THIS WAS HOW THE TABLES LOOKED IN 2012 */
SOP10200.SOPTYPE, SOP10200.SOPNUMBE, SOP10200.LNITMSEQ, SOP10200.CMPNTSEQ, SOP10200.ITEMNMBR, SOP10200.ITEMDESC, SOP10200.NONINVEN, SOP10200.DROPSHIP, SOP10200.UOFM, SOP10200.LOCNCODE, SOP10200.UNITCOST, SOP10200.ORUNTCST, SOP10200.UNITPRCE, SOP10200.ORUNTPRC, SOP10200.XTNDPRCE, SOP10200.OXTNDPRC, SOP10200.REMPRICE, SOP10200.OREPRICE, SOP10200.EXTDCOST, SOP10200.OREXTCST, SOP10200.MRKDNAMT, SOP10200.ORMRKDAM, SOP10200.MRKDNPCT, SOP10200.MRKDNTYP, SOP10200.INVINDX, SOP10200.CSLSINDX, SOP10200.SLSINDX, SOP10200.MKDNINDX, SOP10200.RTNSINDX, SOP10200.INUSINDX, SOP10200.INSRINDX, SOP10200.DMGDINDX, SOP10200.ITMTSHID, SOP10200.IVITMTXB, SOP10200.BKTSLSAM, SOP10200.ORBKTSLS, SOP10200.TAXAMNT, SOP10200.ORTAXAMT, SOP10200.TXBTXAMT, SOP10200.OTAXTAMT, SOP10200.BSIVCTTL, SOP10200.TRDISAMT, SOP10200.ORTDISAM, SOP10200.DISCSALE, SOP10200.ORDAVSLS, SOP10200.QUANTITY, SOP10200.ATYALLOC, SOP10200.QTYINSVC, SOP10200.QTYINUSE, SOP10200.QTYDMGED, SOP10200.QTYRTRND, SOP10200.QTYONHND, SOP10200.QTYCANCE, SOP10200.QTYCANOT, SOP10200.QTYORDER, SOP10200.QTYPRBAC, SOP10200.QTYPRBOO, SOP10200.QTYPRINV, SOP10200.QTYPRORD, SOP10200.QTYPRVRECVD, SOP10200.QTYRECVD, SOP10200.QTYREMAI, SOP10200.QTYREMBO, SOP10200.QTYTBAOR, SOP10200.QTYTOINV, SOP10200.QTYTORDR, SOP10200.QTYFULFI, SOP10200.QTYSLCTD, SOP10200.QTYBSUOM, SOP10200.EXTQTYAL, SOP10200.EXTQTYSEL, SOP10200.ReqShipDate, SOP10200.FUFILDAT, SOP10200.ACTLSHIP, SOP10200.SHIPMTHD, SOP10200.SALSTERR, SOP10200.SLPRSNID, SOP10200.PRCLEVEL, SOP10200.COMMNTID, SOP10200.BRKFLD1, SOP10200.BRKFLD2, SOP10200.BRKFLD3, SOP10200.CURRNIDX, SOP10200.TRXSORCE, SOP10200.SOPLNERR, SOP10200.ORGSEQNM, SOP10200.ITEMCODE, SOP10200.PURCHSTAT, SOP10200.DECPLQTY, SOP10200.DECPLCUR, SOP10200.ODECPLCU, SOP10200.EXCEPTIONALDEMAND, SOP10200.TAXSCHID, SOP10200.TXSCHSRC, SOP10200.PRSTADCD, SOP10200.ShipToName, SOP10200.CNTCPRSN, SOP10200.ADDRESS1, SOP10200.ADDRESS2, SOP10200.ADDRESS3, SOP10200.CITY, SOP10200.STATE, SOP10200.ZIPCODE, SOP10200.CCode, SOP10200.COUNTRY, SOP10200.PHONE1, SOP10200.PHONE2, SOP10200.PHONE3, SOP10200.FAXNUMBR, SOP10200.Flags, SOP10200.CONTNBR, SOP10200.CONTLNSEQNBR, SOP10200.CONTSTARTDTE, SOP10200.CONTENDDTE, SOP10200.CONTITEMNBR, SOP10200.CONTSERIALNBR, SOP10200.ISLINEINTRA
FROM SOP10100 LEFT OUTER JOIN
SOP10200 ON SOP10100.SOPTYPE = SOP10200.SOPTYPE AND SOP10100.SOPNUMBE
= SOP10200.SOPNUMBE
WHERE SOP10200.SOPTYPE = 1 AND (SOP10100.QUOEXPDA < GETDATE())
--ROLLBACK TRANSACTION (Uncomment this if you
indeed want to rollback the transaction)
COMMIT TRANSACTION
--SOP HEADER
--copy header records from expired quotes to the HISTORY table
BEGIN TRANSACTION
/* BE SURE TO CHECK THE TABLES JUST IN CASE NEW FIELDS WERE ADDED
WITH UPDATES. THIS WAS HOW THE TABLES LOOKED IN 2012 */
insert into SOP30200
(SOPTYPE, SOPNUMBE, ORIGTYPE, ORIGNUMB, DOCID, DOCDATE, GLPOSTDT, QUOTEDAT, QUOEXPDA, ORDRDATE, INVODATE, BACKDATE, RETUDATE, ReqShipDate, FUFILDAT, ACTLSHIP, DISCDATE, DUEDATE, REPTING, TRXFREQU, TIMEREPD, TIMETREP, DYSTINCR, DTLSTREP, DSTBTCH1, DSTBTCH2, USDOCID1, USDOCID2, DISCFRGT, ORDAVFRT, DISCMISC, ORDAVMSC, DISAVAMT, ORDAVAMT, DISCRTND, ORDISRTD, DISTKNAM, ORDISTKN, DSCPCTAM, DSCDLRAM, ORDDLRAT, DISAVTKN, ORDATKN, PYMTRMID, PRCLEVEL, LOCNCODE, BCHSOURC, BACHNUMB, CUSTNMBR, CUSTNAME, CSTPONBR, PROSPECT, MSTRNUMB, PCKSLPNO, PICTICNU, MRKDNAMT, ORMRKDAM, PRBTADCD, PRSTADCD, CNTCPRSN, ShipToName, ADDRESS1, ADDRESS2, ADDRESS3, CITY, STATE, ZIPCODE, CCode, COUNTRY, PHNUMBR1, PHNUMBR2, PHONE3, FAXNUMBR, COMAPPTO, COMMAMNT, OCOMMAMT, CMMSLAMT, ORCOSAMT, NCOMAMNT, ORNCMAMT, SHIPMTHD, TRDISAMT, ORTDISAM, TRDISPCT, SUBTOTAL, ORSUBTOT, REMSUBTO, OREMSUBT, EXTDCOST, OREXTCST, FRTAMNT, ORFRTAMT, MISCAMNT, ORMISCAMT, TXENGCLD, TAXEXMT1, TAXEXMT2, TXRGNNUM, TAXSCHID, TXSCHSRC, BSIVCTTL, FRTSCHID, FRTTXAMT, ORFRTTAX, FRGTTXBL, MSCSCHID, MSCTXAMT, ORMSCTAX, MISCTXBL, BKTFRTAM, ORBKTFRT, BKTMSCAM, ORBKTMSC, BCKTXAMT, OBTAXAMT, TXBTXAMT, OTAXTAMT, TAXAMNT, ORTAXAMT, ECTRX, DOCAMNT, ORDOCAMT, PYMTRCVD, ORPMTRVD, DEPRECVD, ORDEPRVD, CODAMNT, ORCODAMT, ACCTAMNT, ORACTAMT, SALSTERR, SLPRSNID, UPSZONE, TIMESPRT, PSTGSTUS, VOIDSTTS, ALLOCABY, NOTEINDX, CURNCYID, CURRNIDX, RATETPID, EXGTBLID, XCHGRATE, DENXRATE, EXCHDATE, TIME1, RTCLCMTD, MCTRXSTT, TRXSORCE, SOPHDRE1, SOPHDRE2, SOPLNERR, SOPHDRFL, COMMNTID, REFRENCE, POSTEDDT, PTDUSRID, USER2ENT, CREATDDT, MODIFDT, Tax_Date, APLYWITH, WITHHAMT, SHPPGDOC, CORRCTN, SIMPLIFD, DOCNCORR, SEQNCORR, SALEDATE, EXCEPTIONALDEMAND, Flags, SOPSTATUS, SHIPCOMPLETE, DIRECTDEBIT, WorkflowApprStatCreditLm, WorkflowPriorityCreditLm, WorkflowApprStatusQuote, WorkflowPriorityQuote, ContractExchangeRateStat)
SELECT
/* BE SURE TO CHECK THE TABLES JUST IN CASE NEW FIELDS WERE ADDED
WITH UPDATES. THIS WAS HOW THE TABLES LOOKED IN 2012 */
SOP10100.SOPTYPE, SOP10100.SOPNUMBE, SOP10100.ORIGTYPE, SOP10100.ORIGNUMB, SOP10100.DOCID, SOP10100.DOCDATE, SOP10100.GLPOSTDT, SOP10100.QUOTEDAT, SOP10100.QUOEXPDA, SOP10100.ORDRDATE, SOP10100.INVODATE, SOP10100.BACKDATE, SOP10100.RETUDATE, SOP10100.ReqShipDate, SOP10100.FUFILDAT, SOP10100.ACTLSHIP, SOP10100.DISCDATE, SOP10100.DUEDATE, SOP10100.REPTING, SOP10100.TRXFREQU, SOP10100.TIMEREPD, SOP10100.TIMETREP, SOP10100.DYSTINCR, SOP10100.DTLSTREP, SOP10100.DSTBTCH1, SOP10100.DSTBTCH2, SOP10100.USDOCID1, SOP10100.USDOCID2, SOP10100.DISCFRGT, SOP10100.ORDAVFRT, SOP10100.DISCMISC, SOP10100.ORDAVMSC, SOP10100.DISAVAMT, SOP10100.ORDAVAMT, SOP10100.DISCRTND, SOP10100.ORDISRTD, SOP10100.DISTKNAM, SOP10100.ORDISTKN, SOP10100.DSCPCTAM, SOP10100.DSCDLRAM, SOP10100.ORDDLRAT, SOP10100.DISAVTKN, SOP10100.ORDATKN, SOP10100.PYMTRMID, SOP10100.PRCLEVEL, SOP10100.LOCNCODE, SOP10100.BCHSOURC, SOP10100.BACHNUMB, SOP10100.CUSTNMBR, SOP10100.CUSTNAME, SOP10100.CSTPONBR, SOP10100.PROSPECT, SOP10100.MSTRNUMB, SOP10100.PCKSLPNO, SOP10100.PICTICNU, SOP10100.MRKDNAMT, SOP10100.ORMRKDAM, SOP10100.PRBTADCD, SOP10100.PRSTADCD, SOP10100.CNTCPRSN, SOP10100.ShipToName, SOP10100.ADDRESS1, SOP10100.ADDRESS2, SOP10100.ADDRESS3, SOP10100.CITY, SOP10100.STATE, SOP10100.ZIPCODE, SOP10100.CCode, SOP10100.COUNTRY, SOP10100.PHNUMBR1, SOP10100.PHNUMBR2, SOP10100.PHONE3, SOP10100.FAXNUMBR, SOP10100.COMAPPTO, SOP10100.COMMAMNT, SOP10100.OCOMMAMT, SOP10100.CMMSLAMT, SOP10100.ORCOSAMT, SOP10100.NCOMAMNT, SOP10100.ORNCMAMT, SOP10100.SHIPMTHD, SOP10100.TRDISAMT, SOP10100.ORTDISAM, SOP10100.TRDISPCT, SOP10100.SUBTOTAL, SOP10100.ORSUBTOT, SOP10100.REMSUBTO, SOP10100.OREMSUBT, SOP10100.EXTDCOST, SOP10100.OREXTCST, SOP10100.FRTAMNT, SOP10100.ORFRTAMT, SOP10100.MISCAMNT, SOP10100.ORMISCAMT, SOP10100.TXENGCLD, SOP10100.TAXEXMT1, SOP10100.TAXEXMT2, SOP10100.TXRGNNUM, SOP10100.TAXSCHID, SOP10100.TXSCHSRC, SOP10100.BSIVCTTL, SOP10100.FRTSCHID, SOP10100.FRTTXAMT, SOP10100.ORFRTTAX, SOP10100.FRGTTXBL, SOP10100.MSCSCHID, SOP10100.MSCTXAMT, SOP10100.ORMSCTAX, SOP10100.MISCTXBL, SOP10100.BKTFRTAM, SOP10100.ORBKTFRT, SOP10100.BKTMSCAM, SOP10100.ORBKTMSC, SOP10100.BCKTXAMT, SOP10100.OBTAXAMT, SOP10100.TXBTXAMT, SOP10100.OTAXTAMT, SOP10100.TAXAMNT, SOP10100.ORTAXAMT, SOP10100.ECTRX, SOP10100.DOCAMNT, SOP10100.ORDOCAMT, SOP10100.PYMTRCVD, SOP10100.ORPMTRVD, SOP10100.DEPRECVD, SOP10100.ORDEPRVD, SOP10100.CODAMNT, SOP10100.ORCODAMT, SOP10100.ACCTAMNT, SOP10100.ORACTAMT, SOP10100.SALSTERR, SOP10100.SLPRSNID, SOP10100.UPSZONE, SOP10100.TIMESPRT, SOP10100.PSTGSTUS, SOP10100.VOIDSTTS, SOP10100.ALLOCABY, SOP10100.NOTEINDX, SOP10100.CURNCYID, SOP10100.CURRNIDX, SOP10100.RATETPID, SOP10100.EXGTBLID, SOP10100.XCHGRATE, SOP10100.DENXRATE, SOP10100.EXCHDATE, SOP10100.TIME1, SOP10100.RTCLCMTD, SOP10100.MCTRXSTT, SOP10100.TRXSORCE, SOP10100.SOPHDRE1, SOP10100.SOPHDRE2, SOP10100.SOPLNERR, SOP10100.SOPHDRFL, SOP10100.COMMNTID, SOP10100.REFRENCE, SOP10100.POSTEDDT, SOP10100.PTDUSRID, SOP10100.USER2ENT, SOP10100.CREATDDT, SOP10100.MODIFDT, SOP10100.Tax_Date, SOP10100.APLYWITH, SOP10100.WITHHAMT, SOP10100.SHPPGDOC, SOP10100.CORRCTN, SOP10100.SIMPLIFD, SOP10100.DOCNCORR, SOP10100.SEQNCORR, SOP10100.SALEDATE, SOP10100.EXCEPTIONALDEMAND, SOP10100.Flags, SOP10100.SOPSTATUS, SOP10100.SHIPCOMPLETE, SOP10100.DIRECTDEBIT, SOP10100.WorkflowApprStatCreditLm, SOP10100.WorkflowPriorityCreditLm, SOP10100.WorkflowApprStatusQuote, SOP10100.WorkflowPriorityQuote, SOP10100.ContractExchangeRateStat
FROM SOP10100
WHERE SOPTYPE = 1 AND (SOP10100.QUOEXPDA < GETDATE())
--ROLLBACK TRANSACTION (Uncomment this if you
indeed want to rollback the transaction)
COMMIT TRANSACTION
-------------------------------------------------------------------------------------
--SOP LINE ITEMS
--delete line items of expired quotes from the WORK table
BEGIN TRANSACTION
/* BE SURE TO CHECK THE TABLES JUST IN CASE NEW FIELDS WERE ADDED
WITH UPDATES. THIS WAS HOW THE TABLES LOOKED IN 2012 */
DELETE SOP10200
FROM SOP10100 LEFT OUTER JOIN
SOP10200 ON
SOP10100.SOPTYPE =
SOP10200.SOPTYPE AND
SOP10100.SOPNUMBE =
SOP10200.SOPNUMBE
WHERE SOP10200.SOPTYPE = 1 AND (SOP10100.QUOEXPDA < GETDATE())
--ROLLBACK TRANSACTION (Uncomment this if you
indeed want to rollback the transaction)
COMMIT TRANSACTION
-----------------------------------------------------------------------------------------------
--HEADER
--delete header records of expired quotes from the WORK table
BEGIN TRANSACTION
/* BE SURE TO CHECK THE TABLES JUST IN CASE NEW FIELDS WERE ADDED
WITH UPDATES. THIS WAS HOW THE TABLES LOOKED IN 2012 */
delete sop10100
WHERE SOPTYPE = 1 AND (SOP10100.QUOEXPDA < GETDATE())
--ROLLBACK TRANSACTION (Uncomment this if you
indeed want to rollback the transaction)
COMMIT TRANSACTION
--View remaining quotes
Select * from
sop10100 where soptype =
1
Select * from
sop10200 where soptype =
1
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 |
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
StockCounts
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
