Wednesday, May 4, 2022

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:

 


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

    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)

   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

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