EpicorFree referencePDF, Excel, Word exportEpicor KineticDiscrete Manufacturing

Epicor Kinetic Database Tables Reference: Core Tables and Key Columns

When you write a BAQ, a dashboard query or a SQL report against Epicor Kinetic, most of the time goes into remembering which table holds what and how rows join. This reference lists the core tables by area with the columns that identify a row.

Download:

Table (Erp schema)	Area	What it holds	Identifying columns
Part	Inventory	Part master record, company level	Company, PartNum
PartPlant	Inventory	Plant specific planning and costing settings for a part	Company, Plant, PartNum
PartWhse	Inventory	Part settings per warehouse	Company, PartNum, WarehouseCode
PartBin	Inventory	On hand quantity by bin, lot and dimension	Company, PartNum, WarehouseCode, BinNum, LotNum, DimCode
PartRev	Engineering	Part revision header with approval state	Company, PartNum, RevisionNum
PartMtl	Engineering	Bill of material lines on a part revision method	Company, PartNum, RevisionNum, AltMethod, MtlSeq
PartOpr	Engineering	Routing operations on a part revision method	Company, PartNum, RevisionNum, AltMethod, OprSeq
PartTran	Inventory	Inventory transaction history	Company, TranNum
JobHead	Production	Job header: part, quantity, dates, released and closed flags	Company, JobNum
JobAsmbl	Production	Assemblies on a job	Company, JobNum, AssemblySeq
JobMtl	Production	Job material lines with required and issued quantity	Company, JobNum, AssemblySeq, MtlSeq
JobOper	Production	Job operations in the routing	Company, JobNum, AssemblySeq, OprSeq
JobOpDtl	Production	Operation detail: resource group and resource per operation	Company, JobNum, AssemblySeq, OprSeq, OpDtlSeq
LaborHed	Production	Labor entry header per employee and shift	Company, LaborHedSeq
LaborDtl	Production	Labor detail lines against jobs and operations	Company, LaborHedSeq, LaborDtlSeq
OrderHed	Sales	Sales order header	Company, OrderNum
OrderDtl	Sales	Sales order lines	Company, OrderNum, OrderLine
OrderRel	Sales	Sales order releases with required date and quantity	Company, OrderNum, OrderLine, OrderRelNum
Customer	Sales	Customer master	Company, CustNum (CustID is the visible id)
ShipTo	Sales	Ship to addresses for a customer	Company, CustNum, ShipToNum
QuoteHed	Sales	Quote header	Company, QuoteNum
QuoteDtl	Sales	Quote lines	Company, QuoteNum, QuoteLine
POHeader	Purchasing	Purchase order header	Company, PONum
PODetail	Purchasing	Purchase order lines	Company, PONum, POLine
PORel	Purchasing	Purchase order releases with due dates	Company, PONum, POLine, PORelNum
Vendor	Purchasing	Supplier master	Company, VendorNum (VendorID is the visible id)
RcvHead	Receiving	Receipt header by packing slip	Company, VendorNum, PurPoint, PackSlip
RcvDtl	Receiving	Receipt lines	Company, VendorNum, PurPoint, PackSlip, PackLine
ShipHead	Shipping	Shipment (packing slip) header	Company, PackNum
ShipDtl	Shipping	Shipment lines linked to order releases	Company, PackNum, PackLine
InvcHead	AR	Customer invoice header	Company, InvoiceNum
InvcDtl	AR	Customer invoice lines	Company, InvoiceNum, InvoiceLine
APInvHed	AP	Supplier invoice header	Company, VendorNum, InvoiceNum
APInvDtl	AP	Supplier invoice lines	Company, VendorNum, InvoiceNum, InvoiceLine
Warehse	Inventory	Warehouse definitions	Company, WarehouseCode
WhseBin	Inventory	Bins within a warehouse	Company, WarehouseCode, BinNum
Plant	Setup	Plant (site) definitions	Company, Plant
Resource	Setup	Individual resources such as machines	Company, Plant, ResourceID
ResourceGroup	Setup	Resource groups used for scheduling and costing	Company, Plant, ResourceGrpID

Table and column names are listed as they appear in the Erp schema for current Kinetic releases. Custom user defined columns and add-ons can change what you see. Confirm names in the Data Dictionary Viewer or Swagger for your version before shipping a BAQ.

Epicor data model review

Optional: send us your BAQ pain points and we will suggest which questions can be answered read-only with natural language.

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

How to use the reference

Type a table name, an area such as Production, or a word from the purpose column into the search box. Each row gives the table in the Erp schema, what it stores and the key columns you join on. Copy the row set to Excel if you want a cheat sheet for your team. The Company column leads almost every key, so always include it in joins unless you only have one company, otherwise multi-company data will duplicate rows.

Reading the key columns

Most Epicor transactional tables use a composite key built from the parent key plus a sequence. A job material line is identified by Company, JobNum, AssemblySeq and MtlSeq, so a join from JobMtl to JobHead needs Company and JobNum, while a join to JobAsmbl also needs AssemblySeq. Customer and Vendor use a numeric CustNum or VendorNum internally while users see CustID or VendorID, a common source of wrong joins.

Join on every key column of the parent, not only the first one
Use CustNum and VendorNum for joins, show CustID and VendorID in output
Release tables (OrderRel, PORel) hold the dates planners care about

Common mistakes in BAQs and SQL

The most frequent errors are joining without Company, forgetting that OrderRel is the grain for demand dates so summing OrderDtl quantities alongside it double counts, and reading PartBin when you need PartWhse level totals. Another trap is querying SQL directly in production for large tables such as PartTran and LaborDtl without a date filter. Prefer BAQs for anything users run, and use direct SQL read replicas for heavy analysis where your policy allows.

Frequently Asked Questions

Does Epicor use an Erp schema in the database?

Yes. Standard application tables live in the Erp schema, with other schemas used for system objects such as Ice. In BAQs you reference tables as Erp.TableName, and the Data Dictionary Viewer lists the tables and columns for your exact version.

Why do my BAQ results duplicate rows?

Usually a join is missing a key column. Joining OrderRel to OrderDtl on OrderNum only, without OrderLine and Company, multiplies rows. Add every key of the parent table to the join and check the grain of the table you are summing.

Should I query the database directly or use a BAQ?

Use BAQs for anything business users run because they respect Epicor security and are upgrade-aware. Direct SQL is acceptable for analysis on a replica if your DBA and policy allow it, but it bypasses security groups and can break across upgrades.

Are user defined fields stored on these tables?

User defined columns are typically added to the base table as extra columns such as Character01 or Number01 style fields, and UD tables exist for fully custom data. Check the Data Dictionary Viewer for the exact names in your environment.

If your team keeps rebuilding the same BAQs, Netray can set up a read-only natural language layer over your Epicor data that shows the query it ran so your admins can verify it.

Talk to an Expert Explore More Free Tools