Bring your ERP · Epicor · read-only
Margin check for Prophet 21.
Run the Margin X-Ray on your Epicor Prophet 21 data. You pull a few tables read-only, drop them on the X-Ray, and it finds the supplier increases that never reached the price, the expired contracts still billing, the claims never filed, all inside your browser tab. Nothing is uploaded, and no login, password or key ever leaves your building.
On-premises SQL Server, Epicor-hosted, or Epicor cloud (SaaS on Azure).
Where Prophet 21 keeps it
A distributor’s file is many supplier lines, each with its own rule. These are the places Prophet 21 stores what the X-Ray reads.
| What the X-Ray reads | Where Prophet 21 keeps it |
|---|---|
| Supplier and its list | inventory_supplier (supplier_id, supplier_part_no, list_price, cost); supplier for the name |
| Purchase discount group | inv_loc.purchase_discount_group |
| Price line / product group | inv_mast.product_group_id |
| Customer price class | price library → book → page, assigned per customer (price_library_x_cust_x_cmpy) |
| Quantity breaks | price_page.break1–14 with calculation_value1–15 (row_status_flag 704 = active, 700 = deleted) |
| Contracts / job pricing | job_price_hdr + job_price_line; vendor side job_price_vendor |
| Invoices and overrides | invoice_hdr + invoice_line (cogs_amount); the override flag lives on oe_line.manual_price_overide |
The read-only pull
Read-only OData (the cleanest least-privilege path)
- Your P21 admin creates one API user with “Allow OData API Service” switched on, and nothing else.
- In Role Maintenance > Dataservice Permission, allow ONLY the tables below. Read-only by design: OData here cannot write.
- Run each request below, paging with $top / $skip (an unpaged request can return 800,000 rows in one response).
- Save each result as CSV with the table’s name (inv_mast.csv, inv_loc.csv …) and drop them, or one .zip of them, on the X-Ray.
# replace <your-p21> with your server; the API user’s login stays with your admin, never in a file
GET https://<your-p21>/odataservice/odata/table/inv_mast?$select=inv_mast_uid,item_id,item_desc,base_unit,product_group_id,class_id1,price1,upc_or_ean_id,delete_flag&$top=5000&$skip=0
GET https://<your-p21>/odataservice/odata/table/inv_loc?$select=inv_mast_uid,location_id,moving_average_cost,qty_on_hand,discontinued,purchase_discount_group&$top=5000&$skip=0
GET https://<your-p21>/odataservice/odata/table/inventory_supplier?$select=inv_mast_uid,supplier_id,supplier_part_no,list_price,cost,future_cost,effective_date,primary_supplier&$top=5000&$skip=0
GET https://<your-p21>/odataservice/odata/table/supplier?$select=supplier_id,supplier_name
GET https://<your-p21>/odataservice/odata/table/price_page?$select=price_page_uid,price_page_type_cd,inv_mast_uid,break1,break2,break3,break4,break5,calculation_value1,calculation_value2,calculation_value3,calculation_value4,calculation_value5,effective_date,expiration_date,row_status_flag&$top=5000&$skip=0
GET https://<your-p21>/odataservice/odata/table/job_price_hdr?$select=contract_no,customer_id,ship_to_id,start_date,end_date,approved,cancelled
GET https://<your-p21>/odataservice/odata/table/job_price_line?$select=contract_no,inv_mast_uid,product_group_id,price,multiplier&$top=5000&$skip=0
GET https://<your-p21>/odataservice/odata/table/job_price_vendor?$select=contract_no,inv_mast_uid,supplier_id,rebate_cost
GET https://<your-p21>/odataservice/odata/table/customer?$select=customer_id,customer_name,salesrep_id
GET https://<your-p21>/odataservice/odata/table/invoice_hdr?$select=invoice_no,invoice_date,customer_id,ship_to_id,salesrep_id,branch_id&$filter=invoice_date ge 2024-10-01&$top=5000&$skip=0
GET https://<your-p21>/odataservice/odata/table/invoice_line?$select=invoice_no,line_no,item_id,qty_shipped,unit_of_measure,unit_price,extended_price,cogs_amount,order_no,oe_line_no&$top=5000&$skip=0
GET https://<your-p21>/odataservice/odata/table/oe_line?$select=order_no,line_no,manual_price_overide&$top=5000&$skip=0A few columns in the sample pack come from a second report in a real pull, and are marked illustrative: inv_mast.mfg_name, inv_mast.default_sales_unit_size, inv_mast.price1_last_changed, inventory_supplier.prior_cost, inventory_supplier.cost_last_changed, inventory_supplier.purchase_pack, job_price_line.committed_qty, customer.price_class, customer.freight_terms, customer.free_freight_min, invoice_hdr.freight_cost, oe_line.source_quote_no, oe_line.quote_expiration_date, spa_claims.csv (a rebate-claim report).
The traps the X-Ray handles
- Soft deletes everywhere: price_page.row_status_flag 700 is deleted (in one real sample 297 of 300 pages were). The X-Ray leaves them out and tells you how many.
- Price 1–10 are price SLOTS (often customer columns), not quantity breaks; price1 is list by convention only.
- Three costs: cogs_amount, commission cost and other cost. Say which one you manage margin to.
- Pricing unit is not the sales unit (pricing_unit, unit_size): normalized before any margin math.
- OData has no server-side join: the X-Ray joins the tables in your browser on inv_mast_uid.
The X-Ray recognizes these Prophet 21 tables from their column names alone and joins them in your browser, with no column choices. Soft-deleted rows are left out and counted, never scanned.
Next: your own file
Start with the free X-Ray on your own export. When you want every leak sized against your invoices and supplier files, the Margin Diagnostic does it on your full data (from $5,000), with your Supplier Line Rulebook and a Line Review Kit for each supplier review included.
Other ERPs: SX.e / CSD · Eclipse · NetSuite · Business Central
Field names follow vendor documentation and community data dictionaries, compiled in our ERP research (October 2026). Your version may name a field differently; the X-Ray’s column matcher accepts either.