ERP Table and Field Explorer

Epicor Kinetic Database Tables and Field Explorer

Search the core Epicor Kinetic (formerly Epicor ERP 10) tables in the Erp schema by table name, field or description, see each table's key, important columns and joins, then pick two to four tables to get a SQL Server JOIN skeleton that carries Company on every join. Customers and suppliers join on the internal CustNum and VendorNum, sales orders run OrderHed to OrderDtl to OrderRel, and jobs run JobHead to JobAsmbl, JobOper and JobMtl. The same joins can be rebuilt as a BAQ when you have no database access.

55 tables, 502 documented fields and 117 join paths across 9 modules. Free, runs in your browser, no login needed.

1Find a table by name, description or field

Naming: Kinetic tables sit in the Erp schema of a SQL Server database and use PascalCase names without prefixes or suffixes, with Hed, Dtl and Rel for header, line and release (OrderHed, OrderDtl, OrderRel). Almost every table starts with Company, so one database holds several companies, and sites are a Plant column rather than separate tables. Every row has a SysRowID GUID; user-defined columns end in _c and are stored in a companion <Table>_UD table joined on ForeignSysRowID = SysRowID.

Tables by module

2Pick tables

55 of 55

Parts

Part

Part master: one row per part per company, with type, units, product group, class, costing method and lot and serial tracking flags.

Also known as: Part Maintenance, Part Entry, item master

Key:CompanyPartNum
FieldMeaning
CompanyCompany ID.
PartNumPart number.
PartDescriptionPart description.
TypeCodePart type (manufactured, purchased or sales kit).
IUMInventory unit of measure.
PUMPurchasing unit of measure.
ClassIDPart class (PartClass).
ProdCodeProduct group (ProdGrup).
CostMethodCosting method used for the part.
TrackLots1 when the part is lot tracked.
TrackSerialNum1 when the part is serial tracked.
InActive1 when the part is inactive.

Joins

  • Part.PartNum = PartRev.PartNum
  • Part.PartNum = PartPlant.PartNum

    Filter Plant for one site.

  • Part.PartNum = PartWhse.PartNum
  • Part.ClassID = PartClass.ClassID
  • Part.ProdCode = ProdGrup.ProdCode
  • Part.PartNum = PartCost.PartNum

    Filter CostID, see Plant.PlantCostID.

  • Part.SysRowID = Part_UD.ForeignSysRowID

    User-defined (_c) columns.

  • Part.PartNum = PartMtl.MtlPartNum
  • Part.PartNum = JobHead.PartNum
  • Part.PartNum = JobAsmbl.PartNum
  • Part.PartNum = JobMtl.PartNum
  • Part.PartNum = JobPart.PartNum
  • Part.PartNum = OrderDtl.PartNum
  • Part.PartNum = ShipDtl.PartNum
  • Part.PartNum = InvcDtl.PartNum
  • Part.PartNum = PODetail.PartNum
  • Part.PartNum = RcvDtl.PartNum
  • Part.PartNum = VendPart.PartNum
  • Part.PartNum = PartBin.PartNum
  • Part.PartNum = PartTran.PartNum
  • Part.PartNum = PartAlloc.PartNum
  • Part.PartNum = PartDtl.PartNum
  • Part.PartNum = PlantWhse.PartNum
  • Part.PartNum = DMRHead.PartNum
  • Part.PartNum = APInvDtl.PartNum

Other columns include NonStock, UnitPrice, SalesUM, NetWeight, UsePartRev, OnHold, UOMClassID and SysRowID. Site-level planning data is in PartPlant, not in Part.

3Generate a JOIN skeleton (2 to 4 tables)

Starters:

Dialect: SQL Server (T-SQL)

Direct SQL needs access to the Kinetic database. Without it, rebuild the same tables and joins as a BAQ and read it through the REST API at /api/v2/odata/<Company>/BaqSvc/<BAQ ID>/Data with an API key.

-- Query skeleton generated by netray.co/tools/table-explorer
-- Dialect: SQL Server (T-SQL). Review keys, filters and indexes before running on production.
-- Filter Company on every table and keep it in every join: one database holds several companies.
-- Join customers and suppliers on CustNum and VendorNum (internal numbers); CustID and VendorID are the codes users see.
-- UD columns (_c) are in the <Table>_UD table: LEFT JOIN Erp.Part_UD ud ON ud.ForeignSysRowID = Part.SysRowID.
-- Read only. Write through the business objects (REST API, BPM or Epicor Functions), not with direct UPDATE statements.

SELECT TOP 100
    orde.OrderNum,
    orde.CustNum,
    orde.PONum,
    orde.OrderDate,
    orde.RequestDate,
    orde2.OrderLine,
    orde2.PartNum,
    orde2.RevisionNum,
    orde2.LineDesc,
    orde2.OrderQty,
    orde3.OrderRelNum,
    orde3.ReqDate,
    orde3.NeedByDate,
    orde3.OurReqQty,
    orde3.SellingReqQty,
    cust.CustID,
    cust.Name,
    cust.Address1,
    cust.City,
    cust.State
FROM Erp.OrderHed AS orde
LEFT JOIN Erp.OrderDtl AS orde2
    ON orde.OrderNum = orde2.OrderNum
   AND orde.Company = orde2.Company
LEFT JOIN Erp.OrderRel AS orde3
    ON orde2.OrderNum = orde3.OrderNum
   AND orde2.OrderLine = orde3.OrderLine
   AND orde2.Company = orde3.Company
LEFT JOIN Erp.Customer AS cust
    ON orde.CustNum = cust.CustNum
   AND orde.Company = cust.Company
WHERE orde.Company = 'YOURCO'  -- replace with your company ID
ORDER BY orde.OrderNum;
Free, optional. Reopen it on any device.
Download:

How to use it

  1. 1

    Search

    Type a table name such as OrderRel, a column such as OurReqQty, a screen such as Job Entry, or a business word such as invoice. Field matches are highlighted under each table.

  2. 2

    Filter and inspect

    Narrow by module, then open a table to see its key, the columns that matter and the tables it joins to. Click a join to jump to that table.

  3. 3

    Build the join

    Add two to four tables with the plus button or a starter. The SQL skeleton uses the Erp schema and adds Company to every join, ready for SQL Server Management Studio, or as the table list for a BAQ.

  4. 4

    Keep it

    Star tables as favorites, export to PDF, Excel, Word or CSV, and optionally save the setup to your Netray account to reopen it later.

Questions about Epicor Kinetic (formerly Epicor ERP 10) tables

Is Epicor Kinetic the same database as Epicor ERP 10?

Yes, it is the same product line. Epicor announced in April 2021 that Kinetic is the new name for Epicor ERP, with the first Kinetic release planned for June 2021. The tables in the Erp schema (Part, OrderHed, JobHead, PartTran) keep their names, so queries and BAQs written for Epicor 10 mostly carry over. Columns are added between releases, so check new or changed columns in your own database.

What is the difference between SQL and a BAQ in Epicor?

A Business Activity Query (BAQ) is Epicor's visual query designer: you pick tables, joins and filters, and Kinetic runs it as a governed, reusable data view. BAQs feed dashboards, reports, Enterprise Search and the REST API without direct database access. Direct SQL against the Erp schema is faster to write for ad-hoc work on premises, but it bypasses Kinetic security and is not available to every deployment. This explorer's join skeleton maps one to one to a BAQ's table list and join conditions.

Where are sales orders stored in Epicor Kinetic?

OrderHed is the header (customer, dates, terms, open and void flags), OrderDtl holds lines keyed by OrderNum and OrderLine, and OrderRel holds releases keyed by OrderNum, OrderLine and OrderRelNum with the ship by date, quantities and warehouse. Shipments are in ShipHead and ShipDtl, which point back to the release, and invoices in InvcHead and InvcDtl.

Why does my query on Customer return the wrong rows?

Two common causes. First, transactions store CustNum, an internal integer, not the CustID users type, so join OrderHed.CustNum = Customer.CustNum and display CustID. Second, every table carries Company: join on Company as well, or a multi-company database returns rows from the other companies. The same applies to Vendor.VendorNum and VendorID.

Where are custom (UD) fields in the database?

Columns added through UD fields are stored in a companion table named after the base table with _UD, for example Erp.Part_UD or Erp.OrderHed_UD, and their names end in _c. Join them with ForeignSysRowID = SysRowID of the base row. The column names are specific to your installation, so this explorer lists only the link columns. Epicor describes UD fields as available across workflows, reports, integrations and user interfaces, so BAQs, BPM and Application Studio layouts use the same columns.

Where are the BOM and routing of a part?

The engineered method is stored per part revision: PartRev holds the revision and alternate method, PartMtl the material lines (MtlPartNum and QtyPer) and PartOpr the operations (OprSeq and OpCode). Each job holds its own copy of the method in JobAsmbl, JobMtl and JobOper, so report job requirements from those tables, not from PartMtl. A multi-level BOM needs a recursive query on PartMtl.MtlPartNum.

How do I find inventory transactions?

Use PartTran. TranType is a from-to code such as MFG-STK for a job receipt to stock or STK-CUS for a shipment from stock, and the row carries the job, sales order or PO it came from, the warehouse, bin and lot, the quantity and the unit costs. Filter on TranDate, because the table grows with every movement. Stock on hand is in PartWhse by warehouse and PartBin by bin.

How often does Kinetic change, and does that affect my queries?

Epicor said that from the May 2026 release (2026.100) Kinetic moves from a semi-annual model to a monthly CI/CD cadence. It has also announced that the final on-premises release will be 2028.1 in January 2028, with Active Support for it through December 31, 2029; later innovation ships in the Epicor Cloud. More frequent updates mean more frequent new columns, so build reports on named columns rather than SELECT * and retest saved BAQs and SQL after each update.

Sources

Table names and columns were checked against these public references. Tables whose key or columns we could not confirm say so in their notes. Column sets change between releases, so confirm against the data dictionary of your own version before you build on it.