Microsoft DynamicsFree referencePDF, Excel, Word exportMicrosoft Dynamics GP

Dynamics GP SQL Table Reference

If you write reports, migration scripts or integrations on Dynamics GP, you end up searching for table names constantly. This reference lists the common tables with module, stage and a practical note.

Download:

Table	Module	Stage	What it holds	Migration or query note
IV00101	Inventory	Master	Item Master, one row per item number	Key ITEMNMBR. Source for item migration.
IV00102	Inventory	Master	Item Quantity Master, quantities by item and site	Key ITEMNMBR with LOCNCODE. Check against valuation.
IV00103	Inventory	Master	Item Vendors Master	Links items to vendors and vendor item numbers.
IV00200	Inventory	Master	Serial number master	Needed when serial tracking is in use.
IV00300	Inventory	Master	Serial and lot number master	Lot quantities and expiry dates.
IV30200	Inventory	History	Inventory transaction history header	Posted inventory transactions.
IV30300	Inventory	History	Inventory transaction amounts history	Line level history. Large on mature systems.
SOP10100	Sales Order Processing	Work	Sales transaction header, not yet moved to history	Key SOPTYPE with SOPNUMBE. Open orders and invoices.
SOP10200	Sales Order Processing	Work	Sales transaction line items	Join to SOP10100 on SOPTYPE and SOPNUMBE.
SOP30200	Sales Order Processing	History	Sales transaction header history	Posted and historical sales documents.
SOP30300	Sales Order Processing	History	Sales transaction line history	Often the largest sales table.
POP10100	Purchase Order Processing	Work	Purchase order header, open and unposted	Key PONUMBER.
POP10110	Purchase Order Processing	Work	Purchase order line items	Join to POP10100 on PONUMBER.
POP10300	Purchase Order Processing	Work	Receivings transaction header	Receipts not yet in history.
POP10310	Purchase Order Processing	Work	Receivings transaction line items	Receipt lines.
POP30100	Purchase Order Processing	History	Purchase order header history	Closed and cancelled purchase orders.
POP30110	Purchase Order Processing	History	Purchase order line history	Closed PO lines.
POP30300	Purchase Order Processing	History	Receivings history header	Posted receipts.
POP30310	Purchase Order Processing	History	Receivings history line items	Posted receipt lines.
GL00100	General Ledger	Master	Account Master, one row per account index	Key ACTINDX. Source for chart of accounts mapping.
GL00105	General Ledger	Master	Account Index Master, account number to index	Holds the segment string such as ACTNUMST.
GL10000	General Ledger	Work	Transaction work header	Unposted batches. Clear before cutover.
GL10001	General Ledger	Work	Transaction work lines	Unposted journal lines.
GL20000	General Ledger	Open	Year-to-date transaction open	Current year posted detail.
GL30000	General Ledger	History	Account transaction history	Prior year posted detail.
GL10110	General Ledger	Open	Account summary master, open year	Period balances for the current year.
GL10111	General Ledger	History	Account summary master, history	Period balances for closed years.
RM00101	Receivables	Master	Customer Master	Key CUSTNMBR.
RM00102	Receivables	Master	Customer address master	Ship-to and bill-to addresses.
RM20101	Receivables	Open	Receivables open transactions	Basis for opening AR aging.
RM30101	Receivables	History	Receivables history transactions	Paid and closed documents.
PM00200	Payables	Master	Vendor Master	Key VENDORID.
PM00300	Payables	Master	Vendor address master	Remit-to and purchase addresses.
PM20000	Payables	Open	Payables open transactions	Basis for opening AP aging.
PM30200	Payables	History	Payables paid transaction history	Closed vendor documents.
CM00100	Bank Reconciliation	Master	Checkbook Master	Bank accounts.
CM20200	Bank Reconciliation	Open	Checkbook transactions	Basis for bank reconciliation state.
FA00100	Fixed Assets	Master	Asset Master	Key ASSETINDEX.
SY01500	System (DYNAMICS)	Master	Company Master	Lists company databases. Lives in the DYNAMICS database.
SY01400	System (DYNAMICS)	Master	User Master	GP users. Lives in the DYNAMICS database.

Table names are consistent across GP versions, but columns can differ slightly, so check your own schema. Company data lives in one database per company, and system tables with an SY prefix live in the DYNAMICS database. Always query a copy or a read-only login, never write directly to GP tables.

Ask GP in plain English

See how a read-only natural language layer can query GP tables on-prem. Optional.

Optional. The tool and every export work without this. No spam.

How to use this reference

Type a table name such as IV00101, SOP10100 or POP10100, or a word like receivables or history, into the search box. Each row shows the module, whether the table is master, work, open or history data, what it holds and a note for queries or migration. Use the Excel export to hand a scoped table list to a data migration team. It is meant as a map, so confirm column names in your own database before writing production queries.

How GP organises its tables

GP tables use a module prefix and a number. Numbers ending in 00 are usually masters, 10 are work or open data, 20 are open transactions and 30 are history. Sales and purchasing keep documents in work tables until they are moved to history, so a full picture of orders needs both SOP10100 and SOP30200, or POP10100 and POP30100. The general ledger splits current year detail in GL20000 from prior years in GL30000.

Work tables hold unposted or open documents
History tables hold closed or moved documents
System tables live in the DYNAMICS database
One database exists per GP company

Query and migration tips

To rebuild a sales order list, union the work and history headers and join to their line tables on SOPTYPE and SOPNUMBE. For the chart of accounts, join GL00100 to GL00105 to get account strings and segments. For opening balances, open receivables and payables sit in RM20101 and PM20000. Before migrating, count rows in the history tables to size your archive decision, since SOP30300 and IV30300 are often the largest.

Common mistakes

Developers often query only the work table and miss history, or only history and miss open documents. Another mistake is writing into GP tables directly, which bypasses business logic and can corrupt the ledger. Do not hard-code company database names, and remember that custom reports built on these tables will need to be rebuilt after a migration. Keep a list of which reports and jobs touch which tables.

Frequently Asked Questions

What is the IV00101 table in Dynamics GP?

IV00101 is the Item Master. It holds one row per item number with description, class, type, unit of measure schedule and costs. Quantities by site are in IV00102.

What is the difference between SOP10100 and SOP30200?

SOP10100 is the sales transaction header work table for documents still in process. SOP30200 is the header history table for documents moved to history. Report on both to see all sales documents.

Where are GP purchase orders stored?

Open purchase orders are in POP10100 with lines in POP10110. Closed or cancelled purchase orders move to POP30100 and POP30110. Receipts are held in POP10300 and POP30300.

Is it safe to query GP tables directly?

Reading is generally fine on a copy or with a read-only login, though large queries can slow the live system. Writing directly to GP tables is not recommended since it bypasses business logic and can corrupt data.

If you want to query GP data in plain language, or turn your table inventory into an archive plan, we can help. The reference is free without us.

Talk to an Expert Explore More Free Tools

Related Tools

Microsoft Dynamics

More Microsoft Dynamics tools

See all

Back to All Tools