Epicor Kinetic SQL schema cheat sheet: Erp vs. Ice, plant boundaries and core tables

1. How the schemas fit together

In Kinetic's user interface, the term Site is commonly used where database columns and tables retain the name Plant. For example, a user-facing screen may say Site while a query references PartPlant.Plant or JobHead.Plant.

Custom Fields and _UD Tables

In environments that expose the physical _UD tables, a common relationship is:

SELECT
    p.Company,
    p.PartNum,
    p.PartDescription,
    pud.Character01
FROM Erp.Part AS p
LEFT JOIN Erp.Part_UD AS pud
    ON pud.ForeignSysRowID = p.SysRowID
WHERE p.Company = 'EPIC01';

Character01 is only an example; replace it with the custom field that exists in your environment. If your SQL reporting source exposes a unified view with custom columns already included, an explicit _UD join may be unnecessary. Confirm the source object and column names before using this pattern. To add a field like this to a printed form, see How to add a field to an Epicor Kinetic SSRS report.

2. Sales orders: OrderHed → OrderDtl → OrderRel

Sales orders are organized into header, line, and release records:

• OrderHed (header): customer, order date, terms, and order-level information. Common key: Company, OrderNum. (Note that OrderHed links to Customer via the internal integer CustNum, whereas Customer.CustID is the human-readable code displayed on screens.)
• OrderDtl (line): part, description, quantity, and unit price. Common key: Company, OrderNum, OrderLine.
• OrderRel (release): release quantity, dates, and fulfillment-related details, including plant and warehouse where applicable. Common key: Company, OrderNum, OrderLine, OrderRelNum.

The Release-Level Join Trap

An order line can have multiple releases. Shipping records are associated with releases, so joining OrderDtl directly to shipment detail can multiply rows or misstate totals. When linking order lines to shipment details, use the release relationship and confirm the exact key fields available in your version.

For a backlog query, keep the release in the result set if the report needs release-level dates, quantities, or plant information.

3. Manufacturing: JobHead, JobAsmbl, JobOper, and JobMtl

Manufacturing jobs contain related records at different levels:

• JobHead: job-level part, production quantity, dates, plant, and status.
• JobAsmbl: assembly structure for the job. Assembly sequence 0 commonly represents the top-level assembly.
• JobOper: routing operations with estimated and actual setup and production hours. Resource groups and resources are held on the related JobOpDtl records.
• JobMtl: material requirements for a job assembly.

Typical relationships:

• JobHead to JobAsmbl: Company, JobNum
• JobAsmbl to JobOper: Company, JobNum, AssemblySeq
• JobAsmbl to JobMtl: Company, JobNum, AssemblySeq

The Operation-Sequence Trap

Materials can be associated with a specific operation through JobMtl.RelatedOperation in environments where that field and relationship apply. Joining job materials to operations using only AssemblySeq can pair each material with every operation in the same assembly. Join materials to assemblies first, and only link to operations when the report requires that relationship; then include the operation-sequence key.

4. Inventory and parts: Part, PartPlant, PlantWhse, PartWhse, and PartBin

These tables represent different levels of part and inventory information:

• Part: global part definition, such as part number, description, and inventory unit of measure.
• PartPlant: plant-specific planning and purchasing settings.
• PlantWhse: the warehouses set up for a part at a plant. It is part-specific: there is at least one row for each PartPlant record.
• PartWhse: warehouse-level part balances and replenishment settings.
• PartBin: bin-level on-hand quantity, where supported by the data source.

Common relationships include:

• Part to PartPlant: Company, PartNum
• PartPlant to PlantWhse: Company, Plant, PartNum
• PartWhse to PartBin: typically Company, PartNum, WarehouseCode (add further keys where required by the specific object and report)

The PartWhse / PartBin Plant Trap

PartWhse and PartBin do not contain a Plant column. PlantWhse records which warehouses are set up for each part at each plant, so a site-specific inventory report should use that bridge rather than assume a direct plant key exists on the balance table. Because PlantWhse is part-specific, join it on Company, Plant, and PartNum; joining on Company and Plant alone repeats each balance row once for every part in the warehouse.

If a part is configured at multiple plants, joining PartPlant directly to inventory balances on part number alone can include warehouses from other plants. Use PlantWhse to restrict the warehouse set, and verify that the join returns the expected row count.

5. Part costing: Respect CostID

PartCost can contain separate cost records for the same part under different costing IDs. A join on Company and PartNum alone may therefore return more than one record per part.

A common join pattern is:

1. Join Part to PartPlant using Company and PartNum.
2. Join PartPlant to Plant on Company and Plant to get the site's cost ID (Plant.PlantCostID), then join PartCost on Company, PartNum, and CostID = Plant.PlantCostID.

SELECT
    p.PartNum,
    p.PartDescription,
    pp.Plant,
    s.PlantCostID AS CostID,
    pc.StdMaterialCost,
    pc.StdLaborCost,
    pc.StdBurdenCost
FROM Erp.Part AS p
INNER JOIN Erp.PartPlant AS pp
    ON pp.Company = p.Company
   AND pp.PartNum = p.PartNum
INNER JOIN Erp.Plant AS s
    ON s.Company = pp.Company
   AND s.Plant = pp.Plant
INNER JOIN Erp.PartCost AS pc
    ON pc.Company = pp.Company
   AND pc.PartNum = pp.PartNum
   AND pc.CostID = s.PlantCostID
WHERE p.Company = 'EPIC01';

The cost columns shown are examples and may differ by release or costing setup. Verify the available columns and the intended cost basis before using this query for valuation or financial reporting. Note: In some setups (such as after copying a company), Plant.PlantCostID can be blank until initialized in Site Maintenance, which will cause an inner join on s.PlantCostID to return no rows for that site.

6. Purchasing: POHeader, PODetail, PORel, RcvHead, and RcvDtl

Purchasing follows a header, line, and release structure:

• POHeader: vendor, buyer, terms, and order-level status.
• PODetail: line-level part, description, quantity, and cost.
• PORel: delivery schedule, due date, plant, and warehouse where applicable.
• RcvHead / RcvDtl: receipt header and detail records.

For release-level reporting, link purchase order releases to receipt detail using the purchase order number, line, and release identifiers that are present in the relevant tables. Check whether the report also needs receipt number, pack slip, vendor, or other keys to avoid duplicate or incomplete matches.

7. Invoicing: InvcHead, InvcDtl, and TranGLC

• InvcHead: invoice-level information, including customer and invoice date.
• InvcDtl: invoice lines, quantities, prices, and source-document references.
• TranGLC: general-ledger control/distribution records associated with transactions.

TranGLC relationships can depend on transaction type and how key fields are populated. Do not assume that Key1 and Key2 always represent invoice number and invoice line for every record. Validate RelatedToFile, the key fields, and the transaction type against representative posted transactions in your environment. For join performance on TranGLC and PartTran, see How to fix Epicor Kinetic BAQ and report timeouts.

8. Starter SQL patterns

The examples below are starting points. Replace EPIC01 with a valid company ID and confirm each column against your database. They are intended for authorized reporting sources, not for writing directly to application tables.

Pattern 1: Open Sales Order Releases

SELECT
    h.OrderNum,
    h.OrderDate,
    c.CustID,
    c.Name AS CustomerName,
    d.OrderLine,
    d.PartNum,
    d.LineDesc,
    r.OrderRelNum,
    r.Plant,
    r.OurReqQty,
    r.NeedByDate,
    r.ReqDate AS ShipByDate
FROM Erp.OrderHed AS h
INNER JOIN Erp.Customer AS c
    ON c.Company = h.Company
   AND c.CustNum = h.CustNum
INNER JOIN Erp.OrderDtl AS d
    ON d.Company = h.Company
   AND d.OrderNum = h.OrderNum
INNER JOIN Erp.OrderRel AS r
    ON r.Company = d.Company
   AND r.OrderNum = d.OrderNum
   AND r.OrderLine = d.OrderLine
WHERE h.Company = 'EPIC01'
  AND h.OpenOrder = 1
  AND d.OpenLine = 1
  AND r.OpenRelease = 1;

This returns open releases for open order lines. Confirm whether your definition of backlog also needs quantity-shipped, credit, hold, or other business rules. OurReqQty is the release quantity in the inventory unit of measure (SellingReqQty holds it in the selling unit), and ReqDate is the release's ship-by date.

Pattern 2: Active Job Operations

SELECT
    j.JobNum,
    j.Plant,
    j.PartNum,
    j.PartDescription,
    j.ProdQty,
    j.StartDate,
    j.DueDate,
    o.AssemblySeq,
    o.OprSeq,
    o.OpCode,
    o.QtyCompleted,
    o.EstSetHours,
    o.EstProdHours,
    o.ActSetupHours,
    o.ActProdHours
FROM Erp.JobHead AS j
INNER JOIN Erp.JobOper AS o
    ON o.Company = j.Company
   AND o.JobNum = j.JobNum
WHERE j.Company = 'EPIC01'
  AND j.JobReleased = 1
  AND j.JobClosed = 0
  AND j.JobComplete = 0
ORDER BY j.JobNum, o.AssemblySeq, o.OprSeq;

This lists operations for jobs that match the status filters. It intentionally includes operations from all assemblies in each qualifying job. Add operation-status filters if the report should show only operations that remain open. If you need the resource group, join JobOpDtl on Company, JobNum, AssemblySeq, and OprSeq.

Pattern 3: Part On-Hand by Plant, Warehouse, and Bin

SELECT
    p.PartNum,
    p.PartDescription,
    p.IUM AS UnitOfMeasure,
    pp.Plant,
    b.WarehouseCode,
    b.BinNum,
    b.OnhandQty
FROM Erp.Part AS p
INNER JOIN Erp.PartPlant AS pp
    ON pp.Company = p.Company
   AND pp.PartNum = p.PartNum
INNER JOIN Erp.PlantWhse AS pw
    ON pw.Company = pp.Company
   AND pw.Plant = pp.Plant
   AND pw.PartNum = pp.PartNum
INNER JOIN Erp.PartBin AS b
    ON b.Company = p.Company
   AND b.PartNum = p.PartNum
   AND b.WarehouseCode = pw.WarehouseCode
WHERE p.Company = 'EPIC01'
  AND b.OnhandQty <> 0;

This pattern uses PlantWhse to restrict bins to the warehouses set up for that part at that plant. Because PlantWhse is part-specific, the join must include PartNum. If your company uses Shared Warehouses across sites, PlantWhse will link the shared warehouse to more than one plant—filter to a single pp.Plant or account for shared warehouses before summing OnhandQty across plants. Confirm whether additional dimensions (such as lot or serial tracking) are needed for the report, and account for them before aggregating quantities.

9. Troubleshooting: Symptom, Likely cause, and fix

10. Practical checks before publishing a query

• Confirm the source: Determine whether the connection is a BAQ, an SSRS dataset, an on-premises SQL database, or a supported cloud reporting replica. Access and schema exposure differ.
• Check the Data Dictionary: Verify table names, column names, and key fields for the installed release.
• Set the row grain: Decide whether one row represents an order line, release, shipment line, job operation, warehouse balance, or bin balance.
• Join on the complete key: Include Company and all relationship fields required by the target tables.
• Test for duplicates: Compare row counts before and after each join and inspect a small set of known records.
• Validate totals: Reconcile quantities and costs against a trusted Kinetic screen or report before relying on the result.
• Use read-only access where appropriate: Do not update Epicor application tables directly with ad hoc SQL.

FAQ

Why does Epicor use both Erp and Ice schemas?

Erp holds business data such as parts, orders, jobs, and GL transactions. Ice holds the framework: users, menus, tasks, and BAQ definitions. Reports almost always query Erp and touch Ice only for system data.

Where are custom fields stored?

In the database, fields added through Extended Table Maintenance live in a paired _UD table, such as Erp.Part_UD, joined on ForeignSysRowID = SysRowID. In BAQ Designer the same fields already appear on the base table, so no join is needed there.

How do I find which warehouses belong to a plant?

Use PlantWhse, joined on Company, Plant, and PartNum, to see which warehouses are set up for a part at each plant. Do not assume that PartWhse or PartBin contains a direct plant column.

How do I identify the table behind a field on a Kinetic screen?

Use the field-help features available in your Kinetic release, or inspect the application's data binding in Application Studio where you have access. Confirm the resulting table and column in the Data Dictionary before writing SQL.

Why do order lines duplicate when an order has multiple releases or shipments?

Because one order line can have several releases, and each release can ship on several pack slips. Join shipments through OrderRel, and decide whether the report counts lines, releases, or shipments before you sum anything.

References

This article is a practical schema guide, not a substitute for release-specific documentation. Before publishing or using a query in production, verify the examples against your Epicor Data Dictionary and your actual reporting source:

• Epicor Data Dictionary / Data Dictionary Viewer for the installed release
• Epicor application help and technical references for Sales Order, Job Management, Purchasing, Inventory, and General Ledger
• Epicor support knowledge base articles relevant to your version and deployment
• EpiUsers: How PlantWhse and PartWhse relate — https://epiusers.help/t/plantwhse-partwhse-how-do-these-relate-think-we-have-bad-data/105593
• EpiUsers: Plant.PlantCostID as the site cost ID — https://www.epiusers.help/t/plant-plantcostid-field-empty/113861