PurchTable and PurchLine: D365 Purchase Order Tables

Dynamics 365 Finance and Operations stores purchase orders in two tables: PurchTable holds the header, one row per order, and PurchLine holds the line items, one row per line. They join on PurchId. Unlike Dynamics GP, D365 keeps the same two tables for the entire life of an order rather than moving completed orders to history tables, and tracks progress through the PurchStatus and DocumentState enum fields instead.

Last updated August 2026.

That single-table design is the first thing to get straight, because most of the confusion around D365 purchasing reports comes from people arriving with habits from another ERP. In GP you have to remember that closed orders move to POP30100. In D365 nothing moves. A three year old fully invoiced order sits in PurchTable next to one you created this morning, and the only thing separating them is a status field. Get the status enums wrong and your open-PO report quietly includes everything the company has ever bought.

Below is the map: which table holds what, how they join, what each status value actually means, where the posted receipts and invoices live, and the part that trips up everyone new to the cloud version, which is that you cannot open SQL Server Management Studio and query production.

What table stores purchase orders in D365?

PurchTable. It holds one row per purchase order, keyed on PurchId, with the vendor account, order dates, currency, delivery terms, and the two status fields. PurchLine holds the lines, one row each, joined back to the header on PurchId. Both tables carry DataAreaId, so every query has to filter by legal entity or you will pull orders from every company in the instance.

Here are the tables that make up the purchasing data model, and what each one is for.

What is the difference between PurchTable and PurchLine?

PurchTable is the order, PurchLine is what you ordered. The header carries anything true of the whole document: vendor, dates, currency, payment and delivery terms, approval state. The lines carry item, quantity, price, delivery address if it differs, and their own status. The join is PurchId, and only PurchId.

SELECT  pt.PURCHID,
        pt.ORDERACCOUNT,
        pt.PURCHSTATUS,
        pl.LINENUMBER,
        pl.ITEMID,
        pl.PURCHQTY,
        pl.PURCHPRICE,
        pl.LINEAMOUNT
FROM    PURCHTABLE pt
JOIN    PURCHLINE  pl
  ON    pl.PURCHID    = pt.PURCHID
 AND    pl.DATAAREAID = pt.DATAAREAID
WHERE   pt.DATAAREAID = 'USMF'
  AND   pt.PURCHSTATUS = 1;

Two things in that query matter more than they look. The DataAreaId condition on the join is not optional: these are company-scoped tables and leaving it off will cross-join orders between legal entities the moment you have more than one. And PurchStatus lives on both tables. The header value is a rollup; the line values are the truth. A purchase order where four lines are invoiced and one is still on backorder shows a header status of Backorder, so a report that only reads the header will tell you the whole order is outstanding.

What are the D365 purchase order status values?

PurchStatus is an enum with five values, and the stored integers do not match the labels users see. Backorder is displayed as "Open order" in the interface, which is the single most common source of confusion when a functional consultant and a report writer talk past each other.

If you want genuinely open purchasing commitment, filter the lines on PurchStatus in (1, 2) rather than the header, and be explicit about whether received-not-invoiced counts. That distinction is an accounting question before it is a SQL one, and it is the same accrual that sits behind goods received not invoiced.

What is the difference between PurchStatus and DocumentState?

They track two different lifecycles on the same row. PurchStatus answers "how far along is the fulfillment", from open through received to invoiced. DocumentState answers "how far along is the approval", and it only does real work when change management is switched on. An order can sit at DocumentState Approved and PurchStatus Backorder at the same time, and that is completely normal: approved to buy, nothing delivered yet.

DocumentState uses the VersioningDocumentState enum, and moves Draft, then In review once submitted to workflow, then Approved or Rejected, then Finalized. Change management itself is turned on with the "Activate change management" option on the Procurement and sourcing parameters page, and it can also be set per vendor. With it off, orders skip the draft and review states entirely, which is why DocumentState looks useless in some environments and load-bearing in others.

Where does D365 store purchase order versions?

In PurchTableAllVersions and PurchLineAllVersions. Every time a change-managed order is re-approved, D365 writes the prior state to those tables against a version number, which is what the version comparison screen reads. Separately, each confirmation posts a row to VendPurchOrderJour with its line detail in VendPurchOrderTrans, so the confirmation journal is a record of what was actually sent to the vendor at each revision, not just what the order says today.

This matters for audit work more than for reporting. If somebody asks what quantity was on the PO when the vendor accepted it, PurchLine will not tell you, because PurchLine has moved on. VendPurchOrderTrans will.

How do you join PurchTable to product receipts and invoices?

Not through PurchId alone, if you want line-level accuracy. PurchId gets you to the right documents, but matching a specific PO line to the receipt and invoice lines that consumed it runs through InventTransId, the inventory transaction identifier stamped on the PurchLine when it is created.

SELECT  pl.PURCHID,
        pl.LINENUMBER,
        pl.ITEMID,
        pl.PURCHQTY                      AS ordered_qty,
        SUM(vpst.QTY)                    AS received_qty,
        SUM(vit.QTY)                     AS invoiced_qty
FROM    PURCHLINE pl
LEFT JOIN VENDPACKINGSLIPTRANS vpst
       ON vpst.INVENTTRANSID = pl.INVENTTRANSID
      AND vpst.DATAAREAID    = pl.DATAAREAID
LEFT JOIN VENDINVOICETRANS vit
       ON vit.INVENTTRANSID  = pl.INVENTTRANSID
      AND vit.DATAAREAID     = pl.DATAAREAID
WHERE   pl.DATAAREAID = 'USMF'
  AND   pl.PURCHID    = 'PO-000123'
GROUP BY pl.PURCHID, pl.LINENUMBER, pl.ITEMID, pl.PURCHQTY;

Watch the sign convention. Receipt and invoice quantities on the purchase side are stored as negative values in some of these transaction tables because they are inventory movements viewed from the item ledger, so check your data before assuming a SUM comes back positive. And joining both receipts and invoices in one query multiplies rows when a line has several of each; the aggregate above hides it, but a detail query will not.

For inventory-level detail, the chain is PurchLine.InventTransId to InventTransOrigin.InventTransId, then InventTrans.InventTransOrigin to InventTransOrigin.RecId. People routinely try to join PurchLine straight to InventTrans on InventTransId and find the field is not there. It was in older versions of AX. It is not any more.

Can you query PurchTable directly with SQL in D365?

Not in a cloud production environment. Microsoft does not provide direct database access to production Finance and Operations instances, so there is no connection string to hand to a reporting tool. This is the single biggest adjustment for teams coming from AX 2012 or Dynamics GP, where opening Management Studio against the live database was routine.

What you use instead, depending on what you are doing:

• Data entities over OData for integrations that read or write one order at a time.
• The Data Management Framework for bulk import and export, using Purchase order headers V2 and Purchase order lines V2, or Purchase orders composite V3 to move header and lines in a single file.
• Entity store, Azure Data Lake, or the Fabric link for analytical reporting, where the tables land somewhere you genuinely can write T-SQL against.
• A Tier-2 sandbox for investigation, where just-in-time read-only database access can be requested.

The SQL above is still worth knowing, because that is the shape the data takes once it reaches your lake or your BYOD database. You just do not run it against production.

What are the purchase order types in D365?

PurchTable.PurchaseType distinguishes what kind of purchasing document the row is. The values are Journal, Quotation, Subscription, Purchase order, and Returned order. Nearly everything you think of as a PO is the Purchase order type, stored as PURCH.

This is not trivia if you import data. The Purchase order headers V2 entity carries a range on purchase type of PURCH, so it handles regular purchase orders and nothing else. Teams migrating a mixed set of documents load a file, see fewer records than they sent, and spend a day looking for a validation error that was never raised. The rows were filtered out by the entity, not rejected.

PurchTable, Purchase Header, or POP10100? Telling the Dynamics products apart

Three different Microsoft ERPs use three completely different purchasing schemas, and search results mix them freely. If a forum answer mentions a table you cannot find, it is usually because it is for a different product.

The Business Central and GP schemas are covered in detail in Dynamics GP purchase order tables. For the same exercise on other vendors, there is Oracle purchase order tables and purchase order history in SAP.

Getting purchase orders into these tables in the first place

Reading the schema is the easy half. The harder half, for most teams, is that a large share of purchase orders arrive as PDFs in a mailbox, and somebody retypes them into D365 before any of this data exists. The Data Management Framework will happily load ten thousand orders from a CSV in a few minutes; producing that CSV from ten thousand documents is what actually takes the quarter.

That gap is what we build for. PurchaseOrders reads a purchase order PDF, scan, or photo and returns the header fields and full line-item table as Excel, CSV, or JSON that you map to the V2 entity columns, which is covered on D365 purchase order import. To be clear about the boundary, it is a document capture step and not a Dynamics connector: it does not install in D365, run your data project, or write to PurchTable. The general pattern across systems is in how to import purchase orders to an ERP, and the field-by-field output is in purchase order line item extraction.

One practical note for anyone documenting this internally. The schema knowledge above is scattered across Microsoft Learn, a dozen consultant blogs, and whatever your own team wrote in a wiki three years ago, and the slowest part of a purchasing investigation is usually finding where a decision was already recorded rather than working it out again. Teams that put a layer of search across every internal system at once in front of that sprawl stop rediscovering the same answers.

Quick reference

Four things worth remembering. The header is PurchTable and the lines are PurchLine, joined on PurchId plus DataAreaId. Nothing moves to a history table, so status is the only thing separating an open order from a five year old one. PurchStatus tracks fulfillment while DocumentState tracks approval, and Backorder means "Open order" on screen. And in the cloud you query an export of these tables, not the tables themselves. For the fields that appear on the document rather than in the database, see purchase order fields.