Dynamics GP POP Tables: Purchase Order Processing Reference and Sample SQL
pop tables dynamics gp
Also searched as
- POP10100 POP10110 dynamics gp
- dynamics gp purchase order tables
- POP30100 POP30110 history tables
- dynamics gp purchase order receipts sql query
Short answer
In Dynamics GP, purchase order headers live in POP10100 (open) and POP30100 (history), lines in POP10110 and POP30110, and receipts in POP10300 and POP10310 with history in POP30300 and POP30310. Join header to line on PONUMBER, and link receipts to PO lines through the receipt line quantity table POP10500. Always read from the right open or history table, because closed documents move.
Applies to: Microsoft Dynamics GP 2015 through current releases, SQL Server. Verify column lists in your own company database, since customizations and version changes may add columns.
How to query PO data in GP safely
- 1Connect read-only to the company database (not DYNAMICS) using SQL Server Management Studio or a reporting login.
- 2Start from the PO header table: POP10100 for open orders and POP30100 for closed or historical orders. Use UNION ALL when you need both.
- 3Join the line table on PONUMBER: POP10110 for open lines and POP30110 for history.
- 4Join to receipts through POP10500 (open receipt line quantities) or POP30310 (history receipt lines) using the PO number and PO line number.
- 5Translate codes such as PO status with CASE expressions, and join to RM or PM tables (for vendor name) using VENDORID.
- 6Run queries with NOLOCK only for reporting, and never write to these tables directly; use eConnect, Web Services or GP itself for changes.
- 7Test results against a GP inquiry screen such as Purchase Order Inquiry before trusting the query.
The main POP tables
Open and history tables mirror each other. POP10100 holds PO headers (PO number, status, vendor ID, document date, subtotal, tax, freight) and POP10110 holds the lines (item number, description, quantity ordered, quantity canceled, unit cost, extended cost, location, required and promised dates). When a PO is fully received and closed, the records move to POP30100 and POP30110.
Receipts use a similar pattern: POP10300 for open receipt headers and POP10310 for receipt lines, with POP30300 and POP30310 for history. POP10500 stores receipt line quantities and links receipts back to the PO and PO line. Always check POPTYPE and status values in your version before filtering.
Joins that work
Header to line is straightforward on PONUMBER. Line to receipt is the one people get wrong: join on PONUMBER and the PO line sequence field, and do not rely on item number, because the same item can appear on several lines.
To report open quantity, use ordered minus canceled minus received, and compute received from the receipt line quantities, not from header fields. Return and invoice-matching transactions complicate this, so reconcile your totals to a GP report before using them in a dashboard.
SELECT h.PONUMBER, h.VENDORID, h.VENDNAME, h.DOCDATE, h.POSTATUS,
l.ORD, l.ITEMNMBR, l.ITEMDESC, l.QTYORDER, l.QTYCANCE,
l.UNITCOST, l.EXTDCOST, l.REQDATE
FROM POP10100 h
JOIN POP10110 l ON l.PONUMBER = h.PONUMBER
WHERE h.POSTATUS IN (1, 2, 3, 4)
ORDER BY h.PONUMBER, l.ORD;Open and history together
A purchase history report needs both sets. Use UNION ALL across the open and history tables with the same column list, and tag each row with its source so you can tell which are closed.
PO status is a coded value: new, released, change order, received, closed and canceled map to numbers 1 to 6 in standard GP. Use a CASE expression in the query rather than hard-coding text in a report.
SELECT PONUMBER, VENDORID, DOCDATE, POSTATUS, 'OPEN' AS SRC FROM POP10100 UNION ALL SELECT PONUMBER, VENDORID, DOCDATE, POSTATUS, 'HIST' AS SRC FROM POP30100;
Safe usage notes
Treat these tables as read-only. GP maintains related records in other tables (inventory, payables, tax, distributions), and direct updates can leave the company out of balance in ways that are very hard to repair.
Prefer SQL views for reports so that when the schema changes at an upgrade you fix one definition, and give the report login only SELECT rights on the views.
Try it here, free
Dynamics GP SQL Table Reference (IV00101, SOP10100, POP10100)
Searchable reference of common Dynamics GP tables with module, purpose and work, open or history status.
Common pitfalls
- !Querying only POP10100 and missing closed orders that moved to POP30100.
- !Joining receipts to lines on item number instead of PO and line number.
- !Updating GP tables directly instead of using eConnect, Web Services or the application.
- !Assuming status codes without checking them in the target version.
- !Reading from the DYNAMICS system database when company data is in the company database.
How an ERP-grounded AI assistant handles this
Table knowledge is the barrier for most GP reporting requests. A private assistant grounded in your GP schema can turn a question like which POs are past due with open quantity into the correct joins across POP tables, display the SQL, and run it read-only. Netray runs this against the customer's own SQL Server so no GP data leaves your network.
Frequently asked questions
What is the difference between POP10100 and POP30100?
POP10100 contains open purchase order headers, while POP30100 holds history headers after the order is closed or canceled and moved. Reports that need the full history must read both tables, normally combined with UNION ALL.
How do I link a receipt to a purchase order line?
Use the receipt line quantity table POP10500 for open receipts, or the history receipt tables, and join on PO number and PO line sequence. Verify with Purchase Order Inquiry that the quantities match what GP shows.
Can I update POP tables with SQL?
You should not. GP updates several related tables for each transaction, and direct changes can break integrity and audit trails. Use the GP application, eConnect or Web Services for any changes, and use SQL only to read.
Where do I find vendor details for a PO?
Join the VENDORID from the PO header to the payables vendor master table, which is PM00200 in standard GP. Many header tables also store the vendor name, which is handy but may differ from the current master.
Related
SQL to Find Responsibilities Assigned to a User in Oracle EBS
Join FND_USER to FND_USER_RESP_GROUPS on USER_ID, then to FND_RESPONSIBILITY_TL on RESPONSIBILITY_ID and APPLICATION_ID to get the responsibility name, filtering the END_DATE columns to show only currently active assignments. This is the standard query DBAs and support teams use instead of clicking through System Administrator > Security > User > Define for every user.
How-toOdoo Inventory Valuation Report Does Not Match the Balance Sheet
In Odoo with automated inventory valuation, the stock valuation report should equal the balance of the stock valuation account. Differences nearly always come from manual journal entries to that account, products with manual valuation, costing method changes, landed costs, negative stock, or vendor bills posted at a different price than the receipt.
Error fixFix Oracle EBS Requisition Import Errors in PO_INTERFACE_ERRORS
When Requisition Import (REQIMPORT) rejects rows, it leaves them in PO_REQUISITIONS_INTERFACE_ALL with PROCESS_FLAG = 'ERROR' and writes a reason per row to PO_INTERFACE_ERRORS. Read the Requisition Import Exceptions Report or query the error table, correct the source data, reset the rows and run the import again with the right Import Source and Batch ID.
Error fixFixing "Cannot create a record in (table). The record already exists" in D365 F&SCM data entity imports
This is the standard kernel duplicate-key exception, thrown when the Data Management Framework tries to insert a staging row into a target table whose unique or alternate key already matches an existing record. It almost always means the entity's insert-vs-update logic did not recognize the target row as an update, not that you have a true accidental duplicate in your source file.
Error fixFixing "There are currently no batch servers available for processing" in D365 F&SCM
This message means the batch framework cannot find any AOS instance flagged as a batch server and available for the batch group the job is assigned to. The fix is almost always in server configuration (System administration > Servers) or batch group assignment, not in the batch job itself.
Error fixFixing "You do not have the following permissions on TableData (table): (permission)" in Business Central AL
This runtime error means the current user's assigned permission sets do not grant the required Insert, Modify, Delete, Read, or Execute right on that specific table. It is most common right after installing or updating a custom AL extension, because Business Central checks object-level permissions even for tables an extension owns.
AI for ERPAI for Dynamics GP and NAV, Before the Business Central Decision
Dynamics GP mainstream support is ending and NAV is long retired from active development. Add a private LLM over GP or NAV data now, on-prem, without a forced Business Central move.
Stuck on Microsoft Dynamics GP?
Talk to engineers who work inside Microsoft Dynamics GP every week, and who build private AI that answers these questions from your own ERP data.
Keep exploring
Free tools
- Dynamics GP SQL Table Reference (IV00101, SOP10100, POP10100)Searchable reference of common Dynamics GP tables with module, purpose and work, open or history status.
- Dynamics GP AI Assistant Sizing and Value CalculatorSize a private AI assistant for Dynamics GP: GPUs, index and monthly value from users and questions per day.
- Dynamics GP SQL Trial Balance Query GeneratorBuild a read-only trial balance by account for a chosen fiscal year and period from the GP summary tables.
Integrations
- How to add WhatsApp to Microsoft Dynamics GPHow to connect WhatsApp Business to Microsoft Dynamics GP: read SOP, POP and RM data from SQL Server, post replies through eConnect, with templates and opt-in handled.
- How to automate email in Microsoft Dynamics GPAutomate inbound email for Dynamics GP: turn POs, RFQs, supplier invoices and confirmations into reviewed SOP, POP and payables transactions via eConnect.