# Epicor Kinetic Database Tables Reference: Core Tables and Key Columns > Source: https://netray.co/tools/epicor-kinetic-database-tables-reference > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer Look up the main Erp.* tables in Epicor Kinetic with what each holds and the columns that identify a row. Useful when writing BAQs and SQL. ## Category epicor ## Erp Epicor Kinetic ## Industry Discrete Manufacturing ## Audience - Epicor admin - BAQ developer - ERP consultant ## Tool **Kind:** worksheet **Mode:** reference **Columns:** #### Table (Erp schema) **Key:** table **Type:** text #### Area **Key:** area **Type:** text #### What it holds **Key:** purpose **Type:** text #### Identifying columns **Key:** keys **Type:** text **Rows:** **Table:** Part **Area:** Inventory **Purpose:** Part master record, company level **Keys:** Company, PartNum **Table:** PartPlant **Area:** Inventory **Purpose:** Plant specific planning and costing settings for a part **Keys:** Company, Plant, PartNum **Table:** PartWhse **Area:** Inventory **Purpose:** Part settings per warehouse **Keys:** Company, PartNum, WarehouseCode **Table:** PartBin **Area:** Inventory **Purpose:** On hand quantity by bin, lot and dimension **Keys:** Company, PartNum, WarehouseCode, BinNum, LotNum, DimCode **Table:** PartRev **Area:** Engineering **Purpose:** Part revision header with approval state **Keys:** Company, PartNum, RevisionNum **Table:** PartMtl **Area:** Engineering **Purpose:** Bill of material lines on a part revision method **Keys:** Company, PartNum, RevisionNum, AltMethod, MtlSeq **Table:** PartOpr **Area:** Engineering **Purpose:** Routing operations on a part revision method **Keys:** Company, PartNum, RevisionNum, AltMethod, OprSeq **Table:** PartTran **Area:** Inventory **Purpose:** Inventory transaction history **Keys:** Company, TranNum **Table:** JobHead **Area:** Production **Purpose:** Job header: part, quantity, dates, released and closed flags **Keys:** Company, JobNum **Table:** JobAsmbl **Area:** Production **Purpose:** Assemblies on a job **Keys:** Company, JobNum, AssemblySeq **Table:** JobMtl **Area:** Production **Purpose:** Job material lines with required and issued quantity **Keys:** Company, JobNum, AssemblySeq, MtlSeq **Table:** JobOper **Area:** Production **Purpose:** Job operations in the routing **Keys:** Company, JobNum, AssemblySeq, OprSeq **Table:** JobOpDtl **Area:** Production **Purpose:** Operation detail: resource group and resource per operation **Keys:** Company, JobNum, AssemblySeq, OprSeq, OpDtlSeq **Table:** LaborHed **Area:** Production **Purpose:** Labor entry header per employee and shift **Keys:** Company, LaborHedSeq **Table:** LaborDtl **Area:** Production **Purpose:** Labor detail lines against jobs and operations **Keys:** Company, LaborHedSeq, LaborDtlSeq **Table:** OrderHed **Area:** Sales **Purpose:** Sales order header **Keys:** Company, OrderNum **Table:** OrderDtl **Area:** Sales **Purpose:** Sales order lines **Keys:** Company, OrderNum, OrderLine **Table:** OrderRel **Area:** Sales **Purpose:** Sales order releases with required date and quantity **Keys:** Company, OrderNum, OrderLine, OrderRelNum **Table:** Customer **Area:** Sales **Purpose:** Customer master **Keys:** Company, CustNum (CustID is the visible id) **Table:** ShipTo **Area:** Sales **Purpose:** Ship to addresses for a customer **Keys:** Company, CustNum, ShipToNum **Table:** QuoteHed **Area:** Sales **Purpose:** Quote header **Keys:** Company, QuoteNum **Table:** QuoteDtl **Area:** Sales **Purpose:** Quote lines **Keys:** Company, QuoteNum, QuoteLine **Table:** POHeader **Area:** Purchasing **Purpose:** Purchase order header **Keys:** Company, PONum **Table:** PODetail **Area:** Purchasing **Purpose:** Purchase order lines **Keys:** Company, PONum, POLine **Table:** PORel **Area:** Purchasing **Purpose:** Purchase order releases with due dates **Keys:** Company, PONum, POLine, PORelNum **Table:** Vendor **Area:** Purchasing **Purpose:** Supplier master **Keys:** Company, VendorNum (VendorID is the visible id) **Table:** RcvHead **Area:** Receiving **Purpose:** Receipt header by packing slip **Keys:** Company, VendorNum, PurPoint, PackSlip **Table:** RcvDtl **Area:** Receiving **Purpose:** Receipt lines **Keys:** Company, VendorNum, PurPoint, PackSlip, PackLine **Table:** ShipHead **Area:** Shipping **Purpose:** Shipment (packing slip) header **Keys:** Company, PackNum **Table:** ShipDtl **Area:** Shipping **Purpose:** Shipment lines linked to order releases **Keys:** Company, PackNum, PackLine **Table:** InvcHead **Area:** AR **Purpose:** Customer invoice header **Keys:** Company, InvoiceNum **Table:** InvcDtl **Area:** AR **Purpose:** Customer invoice lines **Keys:** Company, InvoiceNum, InvoiceLine **Table:** APInvHed **Area:** AP **Purpose:** Supplier invoice header **Keys:** Company, VendorNum, InvoiceNum **Table:** APInvDtl **Area:** AP **Purpose:** Supplier invoice lines **Keys:** Company, VendorNum, InvoiceNum, InvoiceLine **Table:** Warehse **Area:** Inventory **Purpose:** Warehouse definitions **Keys:** Company, WarehouseCode **Table:** WhseBin **Area:** Inventory **Purpose:** Bins within a warehouse **Keys:** Company, WarehouseCode, BinNum **Table:** Plant **Area:** Setup **Purpose:** Plant (site) definitions **Keys:** Company, Plant **Table:** Resource **Area:** Setup **Purpose:** Individual resources such as machines **Keys:** Company, Plant, ResourceID **Table:** ResourceGroup **Area:** Setup **Purpose:** Resource groups used for scheduling and costing **Keys:** Company, Plant, ResourceGrpID **Notes:** 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. ## Content **Intro:** 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. **Sections:** #### How to use the reference **Body:** 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 **Body:** 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. **Bullets:** - 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 **Body:** 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. **Cta:** 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. **Faq:** #### Does Epicor use an Erp schema in the database? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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. ## Search terms epicor table names, epicor database schema, epicor kinetic tables, epicor JobHead JobMtl JobOper, epicor OrderRel table, epicor erp schema reference, epicor BAQ tables list, epicor PartTran table --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json