OData API

Disclaimer: This is unofficial, community-created documentation for Epicor Prophet 21 APIs. It is not affiliated with, endorsed by, or supported by Epicor Software Corporation. All product names, trademarks, and registered trademarks are property of their respective owners. Use at your own risk.

Overview

The OData API provides read-only access to P21 data using the OData v4 protocol. It's the fastest way to query P21 tables and views.

Key Characteristics

• Read-only - Cannot create, update, or delete data
• Standard protocol - OData v4 (see Protocol version — earlier versions of this doc said v3)
• Direct access - Query any P21 table or view
• Efficient - Supports filtering, field selection (no server-driven paging — see What the service supports)
• No session - Simple request/response model

There are two OData surfaces on a P21 server. This document covers /odataservice/odata/ — the current one, which reaches every exposed table and view. A second, older surface at /data/erp/views/v1 exposes a curated set of ~118 views with single-row key addressing. See The other OData surface.

Protocol version

Verified against a 26.1 tenant (August 2026) and re-verified on 26.1.5940.0 — the service answers as OData 4.0, and it answers the same on a production and a test tenant (both checked, August 2026), so this is not an environment-specific setting:

GET {base}/odataservice/odata/table/
    OData-Version: 4.0
    Content-Type: application/json; odata.metadata=minimal; odata.streaming=true

{"@odata.context": "{base}/odataservice/odata/table/$metadata", "value": [...]}

GET {base}/odataservice/odata/table/$metadata
    {"$Version": "4.0", "$EntityContainer": "ns.container", ...}

@odata.context and a JSON CSDL $metadata are v4 constructs; v3 used odata.metadata at the document root and an XML metadata document. Epicor's own SDK reference guide agrees: "The Data Services API is based on the OData v4 standard."

This matters in practice because v4 removed substringof (use contains) and changed the metadata format — a v3 client library will not talk to this service correctly.

What the service supports

Epicor publishes an explicit capability list for this implementation. The negatives are the useful part:

(Source: Prophet 21 SDK, Data Services reference guide, served from your own middleware at {middleware}/docs/p21sdk/index.html#/data/reference-guide. The in, $apply and $query rows are from testing on play 26.1.5950.0, 2026-10-29: $apply=aggregate($count as n), $apply=groupby((company_id)) and a groupby with an aggregate each returned HTTP 200 and ordinary oe_hdr rows.)

Endpoints

table and view are two separate surfaces, and an object appears on exactly one of them. Ask for a view on the table path, or a base table on the view path, and you get an empty-bodied 404 — the same response as a name that does not exist at all. See The table/view split.

Base URL Example

https://play.example.com/odataservice/odata/table/supplier

Base host, not ui_server: OData runs on the P21 base host — unlike the Transaction and Interactive APIs, it does not use the UI server URL returned by the router endpoint. Don't prefix OData paths with the ui_server.

Discovering What's Exposed ($metadata)

$metadata hangs off the collection path, not the service root: /odataservice/odata/table/$metadata works, while /odataservice/odata/$metadata returns 404. (The correct path is the one echoed in every response's @odata.context.)

The document is JSON CSDL, not the XML EDMX you may expect, and it is large — roughly 4 MB and ~3,400 tables on a stock tenant. Exposed tables are the keys of ns.container:

"""Check what tables are exposed via OData $metadata."""
import re

import httpx

# ---- EDIT THESE -----------------------------------------------------------
BASE_URL = "https://play.example.com"   # your P21 server
USERNAME = "apiuser"
PASSWORD = "your-password"
VERIFY_SSL = False                        # True once you trust the cert chain
# ---------------------------------------------------------------------------


def get_token(client: httpx.Client) -> str:
    """v2 token endpoint — credentials go in the body, never in headers."""
    r = client.post(
        f"{BASE_URL}/api/security/token/v2",
        json={"username": USERNAME, "password": PASSWORD},
        headers={"Accept": "application/json"},
    )
    r.raise_for_status()
    try:
        return r.json()["AccessToken"]
    except (ValueError, KeyError):  # some middleware answers in XML
        match = re.search(r"<AccessToken>([^<]+)</AccessToken>", r.text)
        if not match:
            raise ValueError(f"No AccessToken in response: {r.text[:200]}") from None
        return match.group(1)


with httpx.Client(verify=VERIFY_SSL, timeout=120, follow_redirects=True) as client:
    token = get_token(client)
    headers = {
        "Authorization": f"Bearer {token}",
        "Accept": "application/json",       # without this you get XML, not JSON
        "Content-Type": "application/json",
    }

    response = client.get(
        f"{BASE_URL}/odataservice/odata/table/$metadata",
        headers=headers,
        timeout=300,  # ~4 MB payload; the shared client's 120s default is tight
    )
    response.raise_for_status()
    metadata = response.json()
    tables = [name for name in metadata["ns"]["container"] if not name.startswith("$")]
    print(f"{len(tables)} tables exposed")           # e.g. 3394
    print("po_hdr exposed?", "po_hdr" in tables)

This is the quickest way to answer "is this table actually exposed to Data Services?" before debugging a 404 — and to find the real name of a table you're guessing at.

The same document also answers "what are this table's real column names" — check it before guessing a $select, not after a 404. Each table is a JSON CSDL EntityType, keyed by table name under its DB schema (dbo for base tables — not under ns, which only holds ns.container's table list, no column detail). Every other key on the EntityType object is a column name ($Kind/$Key are the only non-column keys; a property with no $Type at all defaults to Edm.String):

# Continuing from the client/metadata above; needs Accept: application/json — this endpoint
# answers in XML EDMX by default (see the token endpoint's own default-XML behavior, docs/00).
entity_type = metadata["dbo"]["address"]          # schema first, then table name
columns = [k for k in entity_type if not k.startswith("$")]
print(columns)   # e.g. ['id', 'name', 'mail_address1', 'mail_address2', 'mail_city', ...]

This is the address table's actual answer: the mailing columns are mail_address1/mail_city/mail_postal_code/mail_country (not address1/city), which cost a 404 to discover the hard way once — see Common Tables for the full verified list. Checking $metadata first avoids that round trip entirely; it is the authoritative schema, not a cache of it. Verified live against a real dbo.address EntityType, 26.1, September 2026 — including catching a first draft of this exact note that guessed the wrong key path (ns.address / $Properties) before checking the real response.

Newly created tables need a manual refresh. A table added after the service started (e.g. a new UDT) is absent from $metadata and 404s on query until SOA Admin → Refresh OData API service is run. See Schema Refresh.

The table/view split, and the 404 it produces

The two collection paths partition the database exactly, by INFORMATION_SCHEMA.TABLES.TABLE_TYPE. Measured on a production tenant (September 2026) and matched against the SQL catalogue on the same database:

Every base table is on table, every view is on view, and nothing is on both. The practical consequences:

• Asking the wrong path returns an empty-bodied 404, identical to the response for a name that doesn't exist. GET /odataservice/odata/table/p21_view_oe_hdr → 404; GET /odataservice/odata/view/p21_view_oe_hdr → 200. GET /odataservice/odata/view/oe_hdr → 404; on table it returns 252 columns. A 404 here means wrong surface, wrong column name, or wrong object — it is not a permissions signal.
• Don't infer the surface from the name. class_expansion_view is a BASE TABLE despite the suffix, so it lives on table and 404s on view. The object's TABLE_TYPE decides, never its name.
• Check the matching $metadata before concluding something is unexposed. A view missing from table/$metadata proves nothing — all 3,759 of them are missing from it.
• User-defined tables are fully readable, and this is not obvious from the table list: P21's own *_ud tables (oe_hdr_ud, oe_line_ud, inv_mast_ud, customer_ud, supplier_ud, location_ud, po_line_ud) and site-custom base tables alike are ordinary base tables on the table surface.
• Object names are case-insensitive. Custom_Alerts, custom_alerts and CUSTOM_ALERTS all resolve.

The 25 Enterprise/Global Search views documented below are on the view surface, which is why they do not appear in table/$metadata.

One object name is unreachable regardless of surface: bin

GET /odataservice/odata/table/bin and GET /odataservice/odata/view/bin both 404 — with a raw IIS "404 - File or directory not found" HTML page, not the normal OData JSON 404 body every other missing-object case in this section produces. Verified live on 26.1.5950.0, every casing tried (bin, Bin), both surfaces, with and without a $select/$filter.

The table itself is real — bin.$Key appears in table/$metadata, and its data is reachable indirectly through inv_loc.primary_bin and the bin_ud extension table (which resolves fine; only the bare name bin fails). The most likely explanation is a routing collision: bin is also IIS's own reserved name for a website's compiled-assemblies folder, and something in the URL rewrite chain in front of the OData service appears to intercept a path segment named exactly bin before it reaches the OData handler — which would explain why the failure looks like a static-file 404 rather than an application-level one. Not confirmed against IIS configuration directly; recorded as an observed, reproducible symptom.

Read bin data through a join instead. inv_loc.primary_bin and inv_loc.location_id cover the common case (an item's primary bin at a location); for other bin attributes, bin_ud (its user-defined-field extension table) is reachable normally and may carry what you need.

One table is withheld from every token: users

The users table (P21 user accounts) cannot be read through OData by any token. The two authentication methods fail in different shapes, and one of them looks like success:

What the tests ruled out:

• Scope. The consumer key's decoded aud claim listed users as an exact entry in its OData allow-list, and the token was minted immediately before the calls. Every other table in the same list returned 200 with the same token.
• Columns. Under a consumer key, $select=id,name returns the same 401, so the block is on the table, not on sensitive columns.
• Filters. Under a password grant, $filter on the caller's own id still returns an empty value. A control read of table/customer?$top=1 with the same token returns a real row, and the table is not empty in SQL.

A P21 system administrator confirmed that withholding users from consumer keys is intended behavior: adding users to a key's scope has no effect by design. The password-grant result shows the restriction covers that method too. Epicor has not published a list of tables withheld this way.

Treat an empty value on users as "withheld", not "no rows". A client that checks only the status code reports a password-grant read as a success that found nothing. There is no supported OData workaround. Whether a custom view that selects from users is also withheld is untested.

Credit: @mrwuss — verified on production and play 26.1, September 2026, with both authentication methods; a second integrator reported the same consumer-key 401 on another play middleware host and on live (#164). An earlier reading that a password grant could read users came from a status-code-only check and is withdrawn.

Read-after-write consistency

Every write recipe in this repo verifies a Transaction API write by reading it back over OData (or /transaction/get) immediately afterward — no delay, no retry. That pattern is now a tested fact, not just an accident of every example happening to work: 125 back-to-back single-field writes (inv_mast.item_desc via the Item service), each followed instantly by an OData table: read-back of the same field, returned zero stale reads — 75 sequential plus 50 more across 5 concurrently-writing items (26.1, September 2026). The OData table: surface reflects a Transaction API write with no observable lag, at least at this scale.

What this does not establish, so do not over-read it:

• Only the table: surface was tested. inv_mast/Item has no matching view: object to compare against ($metadata confirms no item/inv_mast/v_item view exists — Discovering What's Exposed); whether a view: object (which may be backed by its own query/cache layer) lags is unconfirmed.
• This was light load (5 concurrent writers, single tenant), not a stress test. Heavier concurrent write volume, a busier tenant, or a different middleware deployment could behave differently.
• This is a snapshot of one build. Re-verify before relying on it as a hard invariant if the tenant's P21 or middleware version changes — see the 2026.1 breaking-changes registry for how much this platform's behavior moves between builds.

Within those bounds: a downstream integration treating an immediate post-write OData mismatch as a hard data-integrity alarm (no retry, no grace period) is not chasing a phantom on this surface — verified table: reads have not lagged a Transaction write in any attempt made here.

Authentication

Include the Bearer token in the Authorization header:

GET /odataservice/odata/table/supplier HTTP/1.1
Host: play.example.com
Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...
Accept: application/json

See Authentication for token generation.

Prerequisites (User Credential Auth Only)

If you authenticate with User Credentials (username/password), a valid token alone is not enough. The P21 user must also have two permissions configured in the P21 Desktop Client:

1. User Maintenance → Application Security → "Allow OData API Service" = Yes
2. Role Maintenance → Dataservice Permission → Allow the specific tables/views being queried

Without these, you'll get a "You are not authorized to access API" error even with a valid token.

Consumer Key authentication skips these requirements - access is controlled by the key's API scope instead. See Authentication - P21 Permissions for full setup details and screenshots.

Query Parameters

All OData query parameters are prefixed with $.

$select - Choose Fields

Return only specific fields:

/odata/table/supplier?$select=supplier_id,supplier_name

⚠️ An unknown $select column returns 404, not 400. Naming a column that doesn't exist fails the whole request with an empty-bodied 404 — indistinguishable at a glance from "table not exposed" or "no permission". If a table you know exists suddenly 404s, drop the $select and retry before chasing Data Service permissions; a misremembered column name (e.g. supplier_id on po_hdr, which actually has vendor_id) is the more common cause. Verified on 2026.1.

$filter - Filter Results

Filter records based on conditions:

/odata/table/supplier?$filter=supplier_id eq 10050

Dates are bare yyyy-MM-dd literals, and now() is not supported — see Non-Expired Records.

$orderby - Sort Results

Sort by one or more fields:

/odata/table/supplier?$orderby=supplier_name asc

$top - Limit Results

Return only N records:

/odata/table/supplier?$top=10

$skip - Pagination

Skip N records (combine with $top for paging):

/odata/table/supplier?$skip=20&$top=10

No @odata.nextLink: P21's OData responses do not include a continuation link. Paging is entirely client-driven with $skip/$top — loop until a page comes back with fewer rows than $top (or track @odata.count). See the Pagination Helper.

The service will not save you from yourself here. Re-confirmed on 26.1.5940.0: a $select with no $top against p21_view_oe_hdr returned 806,503 rows in a single response — no truncation, no continuation link, no server-side cap. An unbounded query is not a page-one query; it is the whole table, materialized in your process. Always send $top.

$count - Get Count

Include total count in response:

/odata/table/supplier?$count=true

Filter Expressions

Comparison Operators

Logical Operators

⚠️ in is accepted and silently ignored. $filter=id in (2,3,4) returns HTTP 200 with every row in the table — the filter is discarded without a warning, unlike a genuine syntax error, which 404s. Match multiple values with an or chain instead, and keep it inside the length limits. Verified on 26.1.5910.3 and again on 26.1.5950.0 — see Avoiding N+1 Query Patterns.

String Functions

Null Checks

$filter=expiration_date eq null
$filter=notes ne null

The right-hand side is always a literal — you cannot compare two columns

Every comparison in $filter is column operator literal. Naming a second column on the right does not compare the two; the service takes the name as a literal value and tries to convert it to the left column's type. On a typed column that fails loudly. On a string column it succeeds and quietly returns the wrong answer.

That last row is the hazard. The same predicate in SQL matches 739,355 rows on the tenant it was measured against; over OData it returns HTTP 200, @odata.count: 0, and an empty value array, because it was evaluated as bill2_name eq 'ship2_name'. Nothing in the response distinguishes "no rows match" from "your filter did not mean what you wrote" — this is the same class of silent-success trap as in being accepted and ignored.

Do the comparison client-side. Filter server-side on what the service can evaluate — literals, and flag columns that already encode the answer — then compare columns in your own code:

# wrong: silently or loudly fails
$filter=total_amount gt amount_paid

# right: filter on the flag P21 maintains for exactly this question,
# then compute the balance yourself
$filter=paid_in_full_flag eq 'N' and invoice_type eq 'IN'

Where a derived comparison has to happen in the database — an aging report, a reconciliation — that is a reason to reach for SQL or a purpose-built view rather than OData.

Long filters and POST /$query

A long or chain runs into two separate limits. Both return HTTP 404, the status this service also uses for a missing table or a bad column name, so read the body before you debug the query: the first limit answers with an IIS HTML page, the second with a one-line message.

The first limit binds first on a GET: 220 terms of the form order_no eq 'NNNNNNN' (seven-digit order numbers) fit in 8,167 characters, and 221 do not. The count depends on how long each term is, so measure the encoded query string, not the term count.

POST {collection}/$query removes the query-string limit. Send the same options you would put after the ? as the request body, with Content-Type: text/plain. The body can be plain or URL-encoded. application/json and application/x-www-form-urlencoded bodies return HTTP 500.

POST /odataservice/odata/table/oe_line/$query
Content-Type: text/plain
Accept: application/json

$select=order_no,line_no&$orderby=order_no,line_no&$top=10000&$skip=0&$filter=order_no eq '500100' or order_no eq '500101'

It works on both the table and view surfaces, and $select, $orderby, $top, $skip and $count behave as they do on a GET.

The term limit still applies. Keep each flat chain under 800 terms, or group the terms in parentheses: (a or b or ... ) or (c or d or ...) with 50 terms in each group returned HTTP 200 and 1,969 oe_hdr rows for 2,000 consecutive order numbers in one call.

(Verified on play 26.1.5950.0, 2026-10-29, against oe_hdr, oe_line and p21_view_oe_line. Both limits are server configuration, so a different tenant can have different values.)

For a worked example that batches order numbers through $query, see Order lines carry no header status.

Common Patterns

Active Record Filter

P21 tracks record status two different ways, and using the wrong one 404s the whole request. Check which column the table actually has before filtering.

Tables with row_status_flag (e.g. price_page, customer_salesrep). The values are code_p21 codes — confirmed against those tables on 26.1:

$filter=row_status_flag eq 704

This filter matters more than it looks: soft-deleted rows are not removed, so they accumulate. On the tenants tested, 297 of 300 sampled price_page rows were 700 (deleted), and customer_salesrep held 12,060 rows at 700 against 20,201 at 704 — an unfiltered query returns dead data, in the price_page case overwhelmingly so.

A deleted row also keeps its old values (customer_salesrep rows at 700 still carry their last commission_percentage), so an unfiltered read isn't merely noisy — it reports retired records with live-looking data. The write side of the same flag is in 03 § Removing a Salesrep Grid Row: the Transaction API sets it with the label (Delete), not the code.

Tables with delete_flag — customer, supplier, inv_mast and many others have no row_status_flag at all. They use a 'Y'/'N' char column:

$filter=delete_flag eq 'N'

⚠️ Filtering on the wrong column fails loudly — and misleadingly. supplier?$filter=row_status_flag eq 704 returns 404 with Could not find a property named 'row_status_flag' on type 'dbo.supplier'. A bare 404 from this API is easy to misread as "table not exposed" or "no permission", so check the column first: GET /odataservice/odata/table/{name}?$top=1 and look at the keys.

Quoting a numeric key fails the same way. customer_salesrep?$filter=customer_id eq '100198' also returns 404 — A binary operator with incompatible types was detected. Found operand types 'Edm.Decimal' and 'Edm.String'. Many *_id columns (customer_salesrep.customer_id, ship_to_salesrep.ship_to_id) are Edm.Decimal even though the value looks like an identifier string, so send it bare: customer_id eq 100198. The same $top=1 probe answers this too — a quoted value in the response means a string column. Note the asymmetry with the Transaction API, where every Value you write is a string regardless of column type.

Company Scoping — company_id vs company_no

The company code is the same value everywhere ("ACME"), but the column that holds it has two different names, and a third group of tables doesn't carry it at all. Verified on 26.1.5910.3:

Purchasing and invoicing use company_no; order entry, customers and inventory locations use company_id. There is no way to infer which from the table's subject matter, and guessing wrong produces the 404 described above rather than an empty result — po_hdr?$select=po_no,company_id fails outright.

The API's field names are not the column names. The m_reprintpurchaseorders report takes its criteria field as company_id, even though the underlying po_hdr column is company_no. Window/service field names and database column names are separate namespaces throughout P21 — take criteria names from GET /api/v2/definition/{service} and column names from the table itself, and don't translate between them.

Non-Expired Records

To filter out expired records, compare expiration_date against a date value:

$filter=expiration_date ge 2025-01-01

Warning: The now() function is not supported in P21 OData. Using it will return a 404 error:

# DOES NOT WORK - returns 404
$filter=expiration_date ge now()

# CORRECT - use explicit date
$filter=expiration_date ge 2025-12-28

For date-relative queries, calculate the date in your application code:

Full runnable version: Code Examples — the same params/filter pattern shown below is used in a complete, authenticated query in the Filtered Query example.

from datetime import date, timedelta

tomorrow = (date.today() + timedelta(days=1)).isoformat()
filter_expr = f"expiration_date ge {tomorrow}"

var tomorrow = DateTime.Today.AddDays(1).ToString("yyyy-MM-dd");
var filterExpr = $"expiration_date ge {tomorrow}";

No Joins — Chain Queries by UID

P21 OData has no joins: each request hits a single table or view. To traverse relationships, chain queries on the _uid key columns — every child table carries its parent's uid. Example, walking a job contract down to its bins and item IDs:

job_price_hdr    $filter=contract_no eq 'JOB-1001'          → job_price_hdr_uid
job_price_line   $filter=job_price_hdr_uid eq {uid}        → job_price_line_uid, inv_mast_uid
job_price_bin    $filter=job_price_line_uid eq {line_uid}  → min_qty / max_qty / reorder_qty ...
inv_mast         $filter=inv_mast_uid eq {im_uid}          → item_id

Two ways to avoid long chains:

• Check for a pre-joined view first. Many p21_view_* views already join the common paths (e.g. p21_view_bin) — query /odataservice/odata/view/{viewname} instead of chaining tables.
• Batch the lookups — collect the uids from one query and fetch the next level with an or-combined filter rather than one call per row (see Avoiding N+1 Query Patterns).

Order lines carry no header status: read the headers first

completed, approved and projected_order (the quote flag) exist only on oe_hdr. Neither oe_line nor the p21_view_oe_line view has them; the lines carry their own complete, cancel_flag and delete_flag, which describe the line, not the order. To work on the lines of open orders, filter the headers, then fetch the lines for those order numbers in batches. Filtering the lines by order-number range or by line complete alone pulls in lines from completed orders and quotes.

This program totals extended_price across the lines of open orders. It sends both queries through POST /$query, so one request carries 500 order numbers, and it parses the prices as decimals (see Decimals arrive as JSON numbers):

"""Total the open order lines: read the headers, then their lines through POST /$query."""
import json
import re
from decimal import Decimal

import httpx

# ---- EDIT THESE -----------------------------------------------------------
BASE_URL = "https://play.example.com"   # your P21 server
USERNAME = "apiuser"
PASSWORD = "your-password"
VERIFY_SSL = False                        # True once you trust the cert chain
COMPANY_ID = "ACME"                       # oe_hdr.company_id to total
MAX_ORDERS = 2000                         # stop after this many open orders
BATCH = 500                               # order numbers per request; a flat or-chain fails at 800 terms
PAGE = 10000                              # rows per page
# ---------------------------------------------------------------------------


def get_token(client: httpx.Client) -> str:
    """v2 token endpoint — credentials go in the body, never in headers."""
    r = client.post(
        f"{BASE_URL}/api/security/token/v2",
        json={"username": USERNAME, "password": PASSWORD},
        headers={"Accept": "application/json"},
    )
    r.raise_for_status()
    try:
        return r.json()["AccessToken"]
    except (ValueError, KeyError):  # some middleware answers in XML
        match = re.search(r"<AccessToken>([^<]+)</AccessToken>", r.text)
        if not match:
            raise ValueError(f"No AccessToken in response: {r.text[:200]}") from None
        return match.group(1)


def query(client: httpx.Client, headers: dict, path: str, options: str) -> list:
    """POST the query options as text/plain; parse decimals as Decimal, not float."""
    r = client.post(
        f"{BASE_URL}/odataservice/odata/{path}/$query",
        content=options,
        headers={**headers, "Content-Type": "text/plain"},  # JSON or form bodies return 500
    )
    r.raise_for_status()
    return json.loads(r.text, parse_float=Decimal)["value"]


with httpx.Client(verify=VERIFY_SSL, timeout=120, follow_redirects=True) as client:
    token = get_token(client)
    headers = {
        "Authorization": f"Bearer {token}",
        "Accept": "application/json",       # without this you get XML, not JSON
    }

    # 1. The status flags live on oe_hdr only; oe_line has none of them.
    header_filter = (
        f"company_id eq '{COMPANY_ID}' and completed eq 'N' and projected_order eq 'N' "
        "and cancel_flag eq 'N' and delete_flag eq 'N'"
    )
    order_nos: list[str] = []
    while len(order_nos) < MAX_ORDERS:
        page = query(client, headers, "table/oe_hdr",
                     f"$select=order_no&$orderby=order_no&$top={PAGE}"
                     f"&$skip={len(order_nos)}&$filter={header_filter}")
        order_nos += [row["order_no"] for row in page]
        if len(page) < PAGE:
            break
    order_nos = order_nos[:MAX_ORDERS]

    # 2. The lines for those orders, BATCH order numbers per request.
    total = Decimal("0")
    line_count = 0
    for i in range(0, len(order_nos), BATCH):
        chain = " or ".join(f"order_no eq '{n}'" for n in order_nos[i:i + BATCH])
        skip = 0
        while True:
            rows = query(client, headers, "table/oe_line",
                         f"$select=order_no,line_no,extended_price&$orderby=order_no,line_no"
                         f"&$top={PAGE}&$skip={skip}"
                         f"&$filter=({chain}) and cancel_flag eq 'N' and delete_flag eq 'N'")
            total += sum((row["extended_price"] for row in rows), Decimal("0"))
            line_count += len(rows)
            skip += len(rows)
            if len(rows) < PAGE:
                break

    print(f"{len(order_nos)} open orders, {line_count} lines, extended price {total}")

using System.Net.Http.Headers;
using System.Text;
using System.Text.Json;

// ---- EDIT THESE -----------------------------------------------------------
const string BaseUrl = "https://play.example.com";   // your P21 server
const string Username = "apiuser";
const string Password = "your-password";
const string CompanyId = "ACME";                      // oe_hdr.company_id to total
const int MaxOrders = 2000;                           // stop after this many open orders
const int Batch = 500;                                // order numbers per request; a flat or-chain fails at 800 terms
const int Page = 10000;                               // rows per page
// ---------------------------------------------------------------------------

var handler = new HttpClientHandler
{
    // Test tenants often present a self-signed cert. Delete this line in production.
    ServerCertificateCustomValidationCallback =
        HttpClientHandler.DangerousAcceptAnyServerCertificateValidator,
};
using var client = new HttpClient(handler) { Timeout = TimeSpan.FromMinutes(2) };
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

var token = await GetTokenAsync(client);
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token);

// 1. The status flags live on oe_hdr only; oe_line has none of them.
var headerFilter = $"company_id eq '{CompanyId}' and completed eq 'N' and projected_order eq 'N' " +
                   "and cancel_flag eq 'N' and delete_flag eq 'N'";
var orderNos = new List<string>();
while (orderNos.Count < MaxOrders)
{
    var page = await QueryAsync(client, "table/oe_hdr",
        $"$select=order_no&$orderby=order_no&$top={Page}&$skip={orderNos.Count}&$filter={headerFilter}");
    orderNos.AddRange(page.Select(row => row.GetProperty("order_no").GetString()!));
    if (page.Count < Page) break;
}
orderNos = orderNos.Take(MaxOrders).ToList();

// 2. The lines for those orders, Batch order numbers per request.
var total = 0m;
var lineCount = 0;
for (var i = 0; i < orderNos.Count; i += Batch)
{
    var chain = string.Join(" or ", orderNos.Skip(i).Take(Batch).Select(n => $"order_no eq '{n}'"));
    var skip = 0;
    while (true)
    {
        var rows = await QueryAsync(client, "table/oe_line",
            $"$select=order_no,line_no,extended_price&$orderby=order_no,line_no&$top={Page}&$skip={skip}" +
            $"&$filter=({chain}) and cancel_flag eq 'N' and delete_flag eq 'N'");
        // GetDecimal reads the JSON number text exactly; GetDouble would round it.
        total += rows.Sum(row => row.GetProperty("extended_price").GetDecimal());
        lineCount += rows.Count;
        skip += rows.Count;
        if (rows.Count < Page) break;
    }
}

Console.WriteLine($"{orderNos.Count} open orders, {lineCount} lines, extended price {total}");

// --- helpers ---------------------------------------------------------------

// POST the query options as text/plain; JSON or form bodies return 500.
static async Task<List<JsonElement>> QueryAsync(HttpClient client, string path, string options)
{
    var response = await client.PostAsync(
        $"{BaseUrl}/odataservice/odata/{path}/$query",
        new StringContent(options, Encoding.UTF8, "text/plain"));
    response.EnsureSuccessStatusCode();
    using var doc = JsonDocument.Parse(await response.Content.ReadAsStringAsync());
    return doc.RootElement.GetProperty("value").EnumerateArray().Select(e => e.Clone()).ToList();
}

// v2 token endpoint — credentials go in the body, never in headers.
static async Task<string> GetTokenAsync(HttpClient client)
{
    var payload = JsonSerializer.Serialize(new { username = Username, password = Password });
    var response = await client.PostAsync(
        $"{BaseUrl}/api/security/token/v2",
        new StringContent(payload, Encoding.UTF8, "application/json"));
    response.EnsureSuccessStatusCode();
    return ReadField(await response.Content.ReadAsStringAsync(), "AccessToken");
}

// Some middleware answers this endpoint in XML even when asked for JSON.
static string ReadField(string payload, string field)
{
    try
    {
        var value = JsonDocument.Parse(payload).RootElement.GetProperty(field).GetString();
        if (!string.IsNullOrEmpty(value)) return value;
    }
    catch (Exception ex) when (ex is JsonException or KeyNotFoundException) { }

    var match = System.Text.RegularExpressions.Regex.Match(payload, $"<{field}>([^<]+)</{field}>");
    if (!match.Success)
        throw new InvalidOperationException(
            $"No {field} in response: {payload[..Math.Min(200, payload.Length)]}");
    return match.Groups[1].Value;
}

(Verified on play 26.1.5950.0, 2026-10-29: both programs returned 1,200 open orders, 10,320 lines and the same total in three line requests. The same lines fetched with GET in batches of 50 order numbers took 24 requests and returned the same total.)

Columns that don't mean what their name says

A column name is a claim about intent, and P21 has a few where the stored value stops matching the intent once a downstream process touches the row. These are read hazards specifically: the value is present, well-typed and plausible, so nothing in the response tells you it is no longer the thing you asked for.

customer.terms_id is the default for the next document, not the terms on an existing one

customer.terms_id is the master-file default applied when a document is created. The terms a given invoice was actually billed on live on that invoice: invoice_hdr.terms_id, with the resulting date in invoice_hdr.net_due_date. The two disagree whenever an account's terms have changed, and an account can carry several sets of terms across its open invoices at once.

This is a read hazard because the master value is the easier one to join to and looks authoritative. Age an invoice against the customer's current terms and the arithmetic silently describes a document that does not exist. A worked case from a production tenant: an account whose master record read Net 180 had two invoices sitting 142 and 149 days past due — both billed Net 30, months before the terms changed. Judged against the master's 180 days they looked comfortably inside terms; judged against their own net_due_date they were the oldest receivables on the account.

Age every invoice on its own net_due_date. Reach for customer.terms_id only when you want to know what the next document will be billed on. The same applies to terms_desc, which is denormalized onto invoice_hdr — the invoice's copy is the historical truth, and terms.terms_id → terms.net_days resolves the master's current meaning.

oe_hdr.completed is not a boolean — 'T' is a real third value

Y and N are what you expect. T marks a document left in an in-progress editing state, and it is rare enough to miss in a sample: company-wide on a production tenant (September 2026), Y 639,247 · N 184,408 · T 308. It matters for two reasons. A filter written as = 'N' and one written as <> 'Y' disagree on every T row, so the choice is never cosmetic. And a T row refuses Transaction API writes — the record-lock prompt fires and the stateless API answers it No — which makes WHERE completed <> 'T' the pre-screen for any batch write against orders. See Order Service — Reassigning the Salesrep.

Do not reach for process_in_progress_lock to find these: it was empty (0 rows) on the same tenant while the prompt was firing, and does not back this behaviour.

Quotes are oe_hdr.projected_order = 'Y' — quote_type is empty and there is no quote_flag

Looking for the obvious column gets you nothing: quote_type is NULL on every row in the table, and no quote_flag column exists. The flag that actually separates a quote from a live order is projected_order. Measured over three months of orders on production: projected_order = 'Y' → 0 of 5,469 ever invoiced (0.0%); projected_order = 'N' → 7,266 of 10,878 invoiced (66.8%, the rest still open or cancelled).

The practical consequence runs in both directions. A query for live documents that does not exclude 'Y' silently mixes quotes into the order book — 5,469 of 16,347 documents in that window. A query for quotes that filters on quote_type returns nothing at all and looks like the site does not use quotes.

po_line.supplier_ship_date is last-shipment-observed on direct-ship POs, not a supplier promise

On a direct-ship PO (po_hdr.po_type = 'D'), confirming the shipment writes the confirmation's ship date down onto po_line.supplier_ship_date for every line on that confirmation — including quantity that has not shipped. Read the column afterwards and you get the date of the last confirmation, not the date the supplier promised.

On a partial confirmation, the promise for the open balance is destroyed. There is nowhere in the PO line to record a reforecast for the unshipped quantity, so the original promise is simply gone:

Each of these has hundreds of pieces still open against a supplier_ship_date in the past that describes a shipment covering a fraction of the line.

It is confined to direct ship, and the split is total. Verified against a 26.1 tenant (August 2026) across 687,879 receipts, all history, zero exceptions in either direction:

shipment_date is only ever set by the direct-ship confirmation, and only the direct-ship confirmation pushes it down to the line. On every other PO type, supplier_ship_date is untouched by receiving and remains a usable promise.

What to use instead. date_due (labeled Expected Date) is not written by receiving on any PO type, and it survives the confirmation — in every row above it still carries a later, different date. Use date_due for the live line-level expectation. For on-time-delivery analysis, either exclude po_type = 'D' lines that have a receipt against them, or capture supplier_ship_date into your own store before the first confirmation, because P21 does not keep the prior value.

GET {base}/odataservice/odata/table/po_line
    ?$select=po_no,line_no,supplier_ship_date,date_due,qty_ordered,qty_received
    &$filter=qty_received gt 0 and qty_received lt qty_ordered

Join to po_hdr.po_type to tell which rows are affected — the line carries no flag of its own, which is the whole trap.

The write itself is documented on the service that performs it: Interactive API § Direct-ship confirmation.

oe_line_po holds stale links: join on both keys and filter delete_flag

po_line has no sales-order column. The link from a PO line to the sales-order line it was bought for lives in oe_line_po: order_number, line_number, po_no, po_line_number, quantity_on_po, connection_type, delete_flag. (On a direct-ship PO, po_hdr.sales_order_number also names the order.)

The table keeps stale rows. Some rows carry a po_no that matches a current PO but point at unrelated sales orders years older than it. Match on po_no alone and those old orders attach themselves to today's PO. To get only real links:

1. Match (po_no, po_line_number) to po_line.(po_no, line_no).
2. Match (order_number, line_number) to oe_line.(order_no, line_no).
3. Drop rows with delete_flag = 'Y'. Treat a NULL delete_flag as not deleted.

GET {base}/odataservice/odata/table/oe_line_po
    ?$select=order_number,line_number,po_no,po_line_number,quantity_on_po,delete_flag
    &$filter=po_no eq {po_no} and po_line_number eq {line_no}

Write each literal to match the column's type in $metadata — a quoted numeric key 404s (details). Then keep the rows whose order_number + line_number pair exists in oe_line and whose delete_flag is not Y.

A deleted PO leaves its sales-order lines open with nothing on order. Deleting the PO sets delete_flag = 'Y' on its oe_line_po rows, but the sales-order lines it was bought for stay open. A query that filters delete_flag correctly shows those lines with no PO, which is the true state: the demand is uncovered and needs a new PO. See 03 § Purchase Order Types for the flags on the deleted PO itself.

oe_line_po also returns every historical link for a PO line, not the receipt layer that is on the shelf now. Tracing current stock to the order it was bought for needs lot or receipt matching on top of this join.

Credit: @mrwuss — verified against production 26.1 reads, October 2026 (#184).

Kit and assembly lines: parent cost ignores rebates, and component lines carry COGS too

Two traps make kit (assembly) margin wrong when you read oe_line or invoice_line naively.

1. The parent's commission_cost is the pre-rebate rollup. A kit parent line rolls up its components' sales_cost (the pre-rebate average) into commission_cost. A rebate price page that lowers a component's cost does not reach the parent's commission_cost, so margin computed from it shows rebated kits as losses.

For rebated kits, read the after-rebate cost from the parent's other_cost on assembly = 'B' lines, or from the components; do not use commission_cost. The contract-side fix is in rebate-assembly-program.

2. Summing every invoice line double-counts kit cost. Component lines carry their own COGS on the invoice, in addition to the parent's rolled-up cost. invoice_line.invoice_line_uid_parent ≠ 0 marks a component line. Count cost from the parent lines or from the components, never both:

GET {base}/odataservice/odata/table/invoice_line
    ?$select=invoice_no,line_no,invoice_line_uid_parent,commission_cost,other_cost,cogs_amount
    &$filter=invoice_no eq {invoice_no} and invoice_line_uid_parent eq 0

As with oe_line_po above, write {invoice_no} to match the column's type in $metadata.

Credit: @mrwuss — verified against production data 26.1, June 2026, re-checked October 2026 (#183).

inv_adj_line.cost is 0 on lines valued at moving average

An inventory adjustment writes inv_adj_hdr / inv_adj_line and posts inv_tran rows with trans_type = 'IA'. On lines that P21 values at moving-average cost when the adjustment posts, inv_adj_line.cost stays 0. The cost that reached the books is inv_tran.unit_cost_amt on the IA row. Value adjustments from inv_tran, not from the adjustment lines.

Two more traps when you total adjustments:

• Net both directions per item. A filter on negative quantity counts one half of every lost-then-found pair and overstates loss.
• Check the reason codes for automated transfers. Some sites book automatic location-to-location replenishment as paired IA rows under an adjustment reason. Those pairs net to zero and are not losses; exclude that reason from a loss view. (Real transfers are trans_type = 'TR'.)

The reason text is in reason, joined on reason.id = inv_adj_hdr.reason_id and reason.company_no = inv_adj_hdr.company_id — company_no on reason, not company_id (see Company Scoping).

User-defined dropdown labels are in extensibility_dropdown_value, not on the row

A user-defined dropdown column (on a *_ud table) stores the data value, not the label users see. The labels live in extensibility_dropdown_value, keyed by table_name and field_name, mapping data_value to display_value:

GET {base}/odataservice/odata/table/extensibility_dropdown_value
    ?$select=table_name,field_name,data_value,display_value
    &$filter=table_name eq 'oe_hdr_ud' and field_name eq '{ud_column}'

They are not in code_p21, which holds P21's own enums.

Column names that trip queries

The obvious guess is wrong for these, and a wrong name in $select or $filter fails with a 404 (Could not find a property named ...) rather than returning an empty column:

Credit: @mrwuss — the adjustment, dropdown-label and column-name findings verified against production 26.1 reads, June to October 2026 (#185).

Data Type Formatting

Decimals arrive as JSON numbers

Edm.Decimal columns come back as JSON numbers written at the column's full scale: "unit_price":235.000000000, "extended_price":940.0000. The wire value is exact. Precision is lost in the client, when a JSON parser turns the number into a binary float. Summing 10,320 oe_line.extended_price values as Python floats gave a total ending in .020000003; the same values parsed as decimals gave one ending in .0200.

Many key-looking columns are Edm.Decimal too, written without a fraction: oe_hdr.customer_id arrives as 100198, and oe_line.line_no as 1. The *_uid columns are Edm.Int32.

Parse money and quantities as decimals:

import json
from decimal import Decimal

rows = json.loads(response.text, parse_float=Decimal)["value"]   # not response.json()

var price = row.GetProperty("extended_price").GetDecimal();   // GetDouble() rounds

For a client that cannot change its parser, send Accept: application/json;IEEE754Compatible=true. The service then writes every Edm.Decimal value as a JSON string ("extended_price":"940.0000", "customer_id":"100198", "line_no":"1") and leaves Edm.Int32 values as numbers. (Verified on play 26.1.5950.0, 2026-10-29.)

String Escaping

Single quotes in values must be escaped by doubling:

$filter=supplier_name eq 'O''Brien Supply'

Item IDs with Special Characters

P21 item IDs commonly contain characters that need URL encoding in OData filters: /, +, #, &, and spaces. The single-quote doubling rule still applies within the OData filter expression, but these special characters also need URL encoding in the query string.

Python pattern for safe OData filter construction:

"""Build an OData filter for an item ID with special characters, then query it."""
import re

import httpx

# ---- EDIT THESE -----------------------------------------------------------
BASE_URL = "https://play.example.com"   # your P21 server
USERNAME = "apiuser"
PASSWORD = "your-password"
VERIFY_SSL = False                        # True once you trust the cert chain
ITEM_ID = "1/2-FITTING"                   # item ID containing characters that need escaping
# ---------------------------------------------------------------------------


def get_token(client: httpx.Client) -> str:
    """v2 token endpoint — credentials go in the body, never in headers."""
    r = client.post(
        f"{BASE_URL}/api/security/token/v2",
        json={"username": USERNAME, "password": PASSWORD},
        headers={"Accept": "application/json"},
    )
    r.raise_for_status()
    try:
        return r.json()["AccessToken"]
    except (ValueError, KeyError):  # some middleware answers in XML
        match = re.search(r"<AccessToken>([^<]+)</AccessToken>", r.text)
        if not match:
            raise ValueError(f"No AccessToken in response: {r.text[:200]}") from None
        return match.group(1)


def safe_item_filter(item_id: str) -> str:
    """Build a safe OData filter for item IDs with special characters.

    Handles:
    - Single quotes (doubled within OData expression)
    - URL-unsafe characters (/, +, #, &, spaces) via percent-encoding

    Args:
        item_id: Raw item ID (e.g., "1/2-FITTING", "ITEM+SIZE#3")

    Returns:
        URL-encoded filter expression
    """
    # First, escape single quotes for OData
    escaped = item_id.replace("'", "''")

    # Build the filter expression
    filter_expr = f"item_id eq '{escaped}'"

    return filter_expr


# Examples of item IDs that need escaping:
# "1/2-FITTING"     -> item_id eq '1/2-FITTING'     (/ needs URL encoding)
# "ITEM+SIZE"       -> item_id eq 'ITEM+SIZE'       (+ needs URL encoding)
# "PART #3"         -> item_id eq 'PART #3'          (# and space need URL encoding)
# "O'RING-204"      -> item_id eq 'O''RING-204'     (quote doubled)


with httpx.Client(verify=VERIFY_SSL, timeout=120, follow_redirects=True) as client:
    token = get_token(client)
    headers = {
        "Authorization": f"Bearer {token}",
        "Accept": "application/json",       # without this you get XML, not JSON
        "Content-Type": "application/json",
    }

    # httpx encodes the filter value in the query string automatically
    params = {"$filter": safe_item_filter(ITEM_ID)}
    response = client.get(
        f"{BASE_URL}/odataservice/odata/table/inv_mast",
        params=params,
        headers=headers,
    )
    response.raise_for_status()

    data = response.json()
    print(f"{len(data['value'])} matching rows for {ITEM_ID!r}")
    for row in data["value"]:
        print(row.get("item_id"), row.get("item_desc"))

using System.Net.Http.Headers;
using System.Text;
using System.Text.Json;

// ---- EDIT THESE -----------------------------------------------------------
const string BaseUrl = "https://play.example.com";   // your P21 server
const string Username = "apiuser";
const string Password = "your-password";
const string ItemId = "1/2-FITTING";                    // item ID containing characters that need escaping
// ---------------------------------------------------------------------------

var handler = new HttpClientHandler
{
    // Test tenants often present a self-signed cert. Delete this line in production.
    ServerCertificateCustomValidationCallback =
        HttpClientHandler.DangerousAcceptAnyServerCertificateValidator,
};
using var client = new HttpClient(handler) { Timeout = TimeSpan.FromMinutes(2) };
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

var token = await GetTokenAsync(client);
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token);

// Examples of item IDs that need escaping:
// "1/2-FITTING"     -> item_id eq '1/2-FITTING'     (/ needs URL encoding)
// "ITEM+SIZE"       -> item_id eq 'ITEM+SIZE'       (+ needs URL encoding)
// "PART #3"         -> item_id eq 'PART #3'          (# and space need URL encoding)
// "O'RING-204"      -> item_id eq 'O''RING-204'     (quote doubled)

// Using with HttpClient (URL encoding via Uri.EscapeDataString):
var filter = Uri.EscapeDataString(SafeItemFilter(ItemId));
var url = $"{BaseUrl}/odataservice/odata/table/inv_mast?$filter={filter}";
var response = await client.GetAsync(url);
response.EnsureSuccessStatusCode();
// HttpClient sends the properly encoded query string

var json = await response.Content.ReadAsStringAsync();
var data = JsonDocument.Parse(json).RootElement;
var rows = data.GetProperty("value");
Console.WriteLine($"{rows.GetArrayLength()} matching rows for '{ItemId}'");
foreach (var row in rows.EnumerateArray())
{
    var itemId = row.TryGetProperty("item_id", out var idProp) ? idProp.ToString() : "";
    var itemDesc = row.TryGetProperty("item_desc", out var descProp) ? descProp.ToString() : "";
    Console.WriteLine($"{itemId} {itemDesc}");
}

// --- helpers ---------------------------------------------------------------

/// <summary>
/// Build a safe OData filter for item IDs with special characters.
/// Doubles single quotes for OData; URL encoding is handled by HttpClient.
/// </summary>
static string SafeItemFilter(string itemId)
{
    // Escape single quotes for OData
    var escaped = itemId.Replace("'", "''");

    // Build the filter expression
    return $"item_id eq '{escaped}'";
}

// v2 token endpoint — credentials go in the body, never in headers.
static async Task<string> GetTokenAsync(HttpClient client)
{
    var payload = JsonSerializer.Serialize(new { username = Username, password = Password });
    var response = await client.PostAsync(
        $"{BaseUrl}/api/security/token/v2",
        new StringContent(payload, Encoding.UTF8, "application/json"));
    response.EnsureSuccessStatusCode();
    return ReadField(await response.Content.ReadAsStringAsync(), "AccessToken");
}

// Some middleware answers this endpoint in XML even when asked for JSON.
static string ReadField(string payload, string field)
{
    try
    {
        var value = JsonDocument.Parse(payload).RootElement.GetProperty(field).GetString();
        if (!string.IsNullOrEmpty(value)) return value;
    }
    catch (Exception ex) when (ex is JsonException or KeyNotFoundException) { }

    var match = System.Text.RegularExpressions.Regex.Match(payload, $"<{field}>([^<]+)</{field}>");
    if (!match.Success)
        throw new InvalidOperationException(
            $"No {field} in response: {payload[..Math.Min(200, payload.Length)]}");
    return match.Groups[1].Value;
}

Tip: When using httpx (Python) with the params dict or Uri.EscapeDataString (C#), URL encoding is handled automatically. The main concern is correctly doubling single quotes within the OData filter expression itself.

Response Format

Success Response

{
    "@odata.context": "https://play.example.com/odataservice/odata/$metadata#supplier",
    "value": [
        {
            "supplier_id": 10050,
            "supplier_name": "ACME Supply Company",
            "row_status_flag": 704,
            ...
        },
        ...
    ]
}

With Count

{
    "@odata.context": "...",
    "@odata.count": 1547,
    "value": [...]
}

Error Response

{
    "error": {
        "code": "400",
        "message": "Invalid filter expression"
    }
}

Common Tables

address columns keep their mail_/phys_ prefix on OData — mail_address1, mail_address2, mail_city, mail_state, mail_postal_code, mail_country for the mailing address; phys_address1, phys_address2, phys_city, phys_state, phys_postal_code, phys_country for the physical one. A $select without the prefix (address1, city, postal_code, …) 404s: Could not find a property named 'address1' on type 'dbo.address'. Verified live, 26.1 (September 2026); confirmed against definitions/Address.json's DbColumnName mapping (address.mail_address1, etc.). The Entity API's Address Fields table documents the same columns under their PascalCase names (MailAddress1, PhysAddress1) if you're on that surface instead.

Enterprise/Global Search Views (p21_view_es_*)

Added August 2026 — discovered and verified live against a 26.1.5930.1 tenant.

25 views share a p21_view_es_* prefix and back P21's in-app global search feature (the es reads as "enterprise search"; not an officially documented expansion, inferred from the schema below). They are not part of the curated 118-view /data/erp/views/v1 surface — query them the normal way, at /odataservice/odata/view/{viewname}, and the same $top is mandatory guidance applies.

They split cleanly into two groups by what their columns look like.

Search-source views (18)

Denormalized, heavily-joined views of a single business entity — one row per record, with lookups (class codes, descriptions, salesrep names, etc.) already resolved. All 18 share two trailing fields useful for incremental sync: unique_id (a per-view natural key, often composite — e.g. p21_view_es_item's is {item_id}--{supplier_id}, since the view fans an item out to one row per supplier) and max_date_last_modified (the latest modification timestamp across every joined table, so $filter=max_date_last_modified gt {timestamp} catches changes anywhere in the join, not just on the primary table).

Index configuration views (7)

These carry no business data — they configure the search feature itself. Every one of them returned "value": [] on the tenant this was verified against (no custom indexes had been configured), but the schema is always present and the relationships below are legible from the foreign-key-shaped column names:

Inferred, not confirmed: the hdr→field and priority-hdr→field/ranking/role/user relationships above are read off the column names and $metadata types (Edm.Int32 FK-shaped columns), not off live data — every *_priority_* view was empty on the verification tenant, so the actual row shape and what "priority" changes about search ranking were not directly observed.

Example: querying p21_view_es_item

Verified live, 26.1.5930.1:

GET /odataservice/odata/view/p21_view_es_item?$top=1
Authorization: Bearer {token}
Accept: application/json

{
  "@odata.context": "{base}/odataservice/odata/view/$metadata#p21_view_es_item",
  "value": [
    {
      "item_id": "WIDGET-001",
      "item_desc": "Standard Widget Assembly",
      "extended_desc": "Standard Widget Assembly - full description text",
      "supplier_part_no": "",
      "upc_code": null,
      "catalog_name": null,
      "contract_number": null,
      "ean_code": null,
      "purchase_discount_group": "Default",
      "discount_group_description": "Default",
      "alternate_code": null,
      "alternate_code_desc": null,
      "supplier_id": 10143,
      "unique_id": "WIDGET-001--10143",
      "max_date_last_modified": "2026-07-24T11:22:14.747-04:00"
    }
  ]
}

Some rows on the verification tenant had a stray leading space on item_id (e.g. " EX21-NI"). It isn't consistent padding — row lengths on the same column varied freely (7 to 19 characters) with no fixed width, so this reads as pre-existing data-entry noise in that specific database rather than a behavior of the view or column type. Not included as a documented gotcha; if you see it on your tenant, treat it as a data-quality issue, not an API contract.

Example: querying p21_view_es_ship_to — the case for these views

p21_view_es_item above is deliberately the simplest of the 18 (15 fields). p21_view_es_ship_to (63 fields) is a better demonstration of what these views are for: one row already joins ship-to, customer, tax group, freight/carrier, payment terms, branch, salesrep, and both customer- and ship-to-level class codes — a query that would otherwise mean separately reading ship_to, customer, tax_group, freight_code, terms, company, and the class-code tables and joining them client-side (OData has no server-side joins).

Verified live, 26.1.5930.1:

GET /odataservice/odata/view/p21_view_es_ship_to?$top=1
Authorization: Bearer {token}
Accept: application/json

{
  "@odata.context": "{base}/odataservice/odata/view/$metadata#p21_view_es_ship_to",
  "value": [
    {
      "ship_to_id": 1,
      "ship_to_name": "Address 1",
      "customer_id": 100199,
      "customer_name": "ACME Plumbing Supply",
      "phys_address1": null,
      "phys_address2": null,
      "phys_city": null,
      "phys_state": null,
      "phys_postal_code": "08701",
      "company_id": "ACME",
      "company_name": "ACME Distribution Inc.",
      "default_branch": "01",
      "tax_group_id": "NJ UEZ TAX",
      "terms_id": "10",
      "default_carrier_id": null,
      "delivery_instructions": null,
      "freight_code_uid": 2,
      "tax_group_description": "NJ UEZ Sales Tax",
      "freight_cd": "IN/OUT",
      "freight_desc": "Customer Pays Incoming and Outgoing Freight",
      "branch_description": "Main Branch",
      "carrier_name": null,
      "terms_desc": "COD",
      "cust_class_1id": null,
      "cust_class_1desc": null,
      "cust_class_2id": "NOT INSU",
      "cust_class_2desc": "Not Insured",
      "ship_to_class1_id": null,
      "ship_to_class1_desc": null,
      "date_created": "2019-03-27T15:12:13.623-04:00",
      "date_last_modified": "2021-04-26T13:09:15.507-04:00",
      "created_by": "apiuser",
      "last_maintained_by": "apiuser",
      "corp_address_id": 100199,
      "corp_address_name": "ACME Plumbing Supply",
      "salesrep_id": "1002",
      "salesrep_name": "Jane Smith",
      "freight_charge_id": null,
      "unique_id": "ACME-1-1002",
      "max_date_last_modified": "2026-08-19T15:00:00-04:00"
    }
  ]
}

Trimmed to the fields that make the point; the live response carries all 63 (mailing address, all five customer- and ship-to-level class code pairs, route_description, shipping_route_uid, etc.). Note unique_id's shape here is {company_id}-{ship_to_id}-{salesrep_id} — a different composite pattern than p21_view_es_item's {item_id}--{supplier_id} (double dash). unique_id composition is per-view; don't assume one view's key shape for another.

Undeployed / Unlicensed Windows: Readable Tables, No API Surface

OData exposes the schema, not the deployment state. A table can be fully readable over OData while the feature that populates it is undeployed or unlicensed — its maintenance window is classic-desktop-only and it has no Transaction/Interactive API surface. The rows you read are then dead storage: nothing writes them and no business logic consults them.

The clearest verified example (26.1.5894.1, play, July 2026) is the native zip → salesrep/territory family. The schema is present and OData-readable, but on an install where the module is undeployed the tables are empty and inert:

Why there's no write path:

• Window: "Postal Code Group Maintenance" (w_postal_code_group_maint), AR › Maintenance — classic desktop only (frame_menu: new_ui_enabled='N', angular_enabled='N', service_name NULL). A NULL service_name rules out the Transaction and Interactive APIs; the 'N' web flags rule out the ui/full surface as well, and that combination is what leaves the window with no API surface at all — confirmed by an ui/full open of m_postalcodegroupmaintenance, which 400s exactly like a menu name that does not exist (see Window→Service Discovery).
• Transaction API: no service — absent from /api/v2/services; plausible hidden names all 500 on /definition.
• Interactive API: by-Name open returns 400 "not available or user does not have permission" — the undeployed-window signal, not a grantable permission.

Behavioral consequence — verified: seeding matching rows in both salesrep_postalcode and postal_code_group_hdr+detail (a zip mapped to a deliberately wrong-region rep) and then creating a customer with that zip in the mailing and physical address does not default salesrep_id. The create fails "Salesrep ID is required for a new ship to." until the rep is supplied explicitly — Customer create never consults these tables when the module is undeployed. So: readable ≠ live. Treat OData-visibility of a feature table as evidence the schema exists, not that the feature is active on your install.

Examples

Basic Query

Get all suppliers:

GET /odataservice/odata/table/supplier

Filtered Query

Get active price pages for a supplier:

GET /odataservice/odata/table/price_page
    ?$filter=supplier_id eq 10050 and row_status_flag eq 704
    &$select=price_page_uid,description,effective_date,expiration_date
    &$orderby=description

Pagination

Get page 3 (10 records per page):

GET /odataservice/odata/table/supplier
    ?$skip=20
    &$top=10
    &$count=true

Complex Filter

Products starting with 'FILTER' and price over $10:

GET /odataservice/odata/table/inv_mast
    ?$filter=startswith(item_id,'FILTER') and list_price gt 10
    &$select=item_id,item_desc,list_price

Code Examples

Basic Query

"""Query suppliers with $top and $select."""
import re

import httpx

# ---- EDIT THESE -----------------------------------------------------------
BASE_URL = "https://play.example.com"   # your P21 server
USERNAME = "apiuser"
PASSWORD = "your-password"
VERIFY_SSL = False                        # True once you trust the cert chain
# ---------------------------------------------------------------------------


def get_token(client: httpx.Client) -> str:
    """v2 token endpoint — credentials go in the body, never in headers."""
    r = client.post(
        f"{BASE_URL}/api/security/token/v2",
        json={"username": USERNAME, "password": PASSWORD},
        headers={"Accept": "application/json"},
    )
    r.raise_for_status()
    try:
        return r.json()["AccessToken"]
    except (ValueError, KeyError):  # some middleware answers in XML
        match = re.search(r"<AccessToken>([^<]+)</AccessToken>", r.text)
        if not match:
            raise ValueError(f"No AccessToken in response: {r.text[:200]}") from None
        return match.group(1)


with httpx.Client(verify=VERIFY_SSL, timeout=120, follow_redirects=True) as client:
    token = get_token(client)
    headers = {
        "Authorization": f"Bearer {token}",
        "Accept": "application/json",       # without this you get XML, not JSON
        "Content-Type": "application/json",
    }

    # Query suppliers
    response = client.get(
        f"{BASE_URL}/odataservice/odata/table/supplier",
        params={"$top": 10, "$select": "supplier_id,supplier_name"},
        headers=headers,
    )
    response.raise_for_status()

    data = response.json()
    for supplier in data["value"]:
        print(f"{supplier['supplier_id']}: {supplier['supplier_name']}")

using System.Net.Http.Headers;
using System.Text;
using System.Text.Json;

// ---- EDIT THESE -----------------------------------------------------------
const string BaseUrl = "https://play.example.com";   // your P21 server
const string Username = "apiuser";
const string Password = "your-password";
// ---------------------------------------------------------------------------

var handler = new HttpClientHandler
{
    // Test tenants often present a self-signed cert. Delete this line in production.
    ServerCertificateCustomValidationCallback =
        HttpClientHandler.DangerousAcceptAnyServerCertificateValidator,
};
using var client = new HttpClient(handler) { Timeout = TimeSpan.FromMinutes(2) };
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

var token = await GetTokenAsync(client);
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token);

// Query suppliers
var select = Uri.EscapeDataString("supplier_id,supplier_name");
var url = $"{BaseUrl}/odataservice/odata/table/supplier?$top=10&$select={select}";
var response = await client.GetAsync(url);
response.EnsureSuccessStatusCode();

var json = await response.Content.ReadAsStringAsync();
var data = JsonDocument.Parse(json).RootElement;
foreach (var supplier in data.GetProperty("value").EnumerateArray())
{
    var supplierId = supplier.GetProperty("supplier_id");
    var supplierName = supplier.GetProperty("supplier_name");
    Console.WriteLine($"{supplierId}: {supplierName}");
}

// --- helpers ---------------------------------------------------------------

// v2 token endpoint — credentials go in the body, never in headers.
static async Task<string> GetTokenAsync(HttpClient client)
{
    var payload = JsonSerializer.Serialize(new { username = Username, password = Password });
    var response = await client.PostAsync(
        $"{BaseUrl}/api/security/token/v2",
        new StringContent(payload, Encoding.UTF8, "application/json"));
    response.EnsureSuccessStatusCode();
    return ReadField(await response.Content.ReadAsStringAsync(), "AccessToken");
}

// Some middleware answers this endpoint in XML even when asked for JSON.
static string ReadField(string payload, string field)
{
    try
    {
        var value = JsonDocument.Parse(payload).RootElement.GetProperty(field).GetString();
        if (!string.IsNullOrEmpty(value)) return value;
    }
    catch (Exception ex) when (ex is JsonException or KeyNotFoundException) { }

    var match = System.Text.RegularExpressions.Regex.Match(payload, $"<{field}>([^<]+)</{field}>");
    if (!match.Success)
        throw new InvalidOperationException(
            $"No {field} in response: {payload[..Math.Min(200, payload.Length)]}");
    return match.Groups[1].Value;
}

Filtered Query

"""Query active price pages for a supplier."""
import re

import httpx

# ---- EDIT THESE -----------------------------------------------------------
BASE_URL = "https://play.example.com"   # your P21 server
USERNAME = "apiuser"
PASSWORD = "your-password"
VERIFY_SSL = False                        # True once you trust the cert chain
SUPPLIER_ID = 10050                       # supplier to look up price pages for
# ---------------------------------------------------------------------------


def get_token(client: httpx.Client) -> str:
    """v2 token endpoint — credentials go in the body, never in headers."""
    r = client.post(
        f"{BASE_URL}/api/security/token/v2",
        json={"username": USERNAME, "password": PASSWORD},
        headers={"Accept": "application/json"},
    )
    r.raise_for_status()
    try:
        return r.json()["AccessToken"]
    except (ValueError, KeyError):  # some middleware answers in XML
        match = re.search(r"<AccessToken>([^<]+)</AccessToken>", r.text)
        if not match:
            raise ValueError(f"No AccessToken in response: {r.text[:200]}") from None
        return match.group(1)


with httpx.Client(verify=VERIFY_SSL, timeout=120, follow_redirects=True) as client:
    token = get_token(client)
    headers = {
        "Authorization": f"Bearer {token}",
        "Accept": "application/json",       # without this you get XML, not JSON
        "Content-Type": "application/json",
    }

    # Get price pages for supplier
    params = {
        "$filter": f"supplier_id eq {SUPPLIER_ID} and row_status_flag eq 704",
        "$select": "price_page_uid,description,calculation_value1",
        "$orderby": "description",
    }

    response = client.get(
        f"{BASE_URL}/odataservice/odata/table/price_page",
        params=params,
        headers=headers,
    )
    response.raise_for_status()

    data = response.json()
    for page in data["value"]:
        print(page["price_page_uid"], page["description"], page["calculation_value1"])

using System.Net.Http.Headers;
using System.Text;
using System.Text.Json;

// ---- EDIT THESE -----------------------------------------------------------
const string BaseUrl = "https://play.example.com";   // your P21 server
const string Username = "apiuser";
const string Password = "your-password";
const int SupplierId = 10050;                            // supplier to look up price pages for
// ---------------------------------------------------------------------------

var handler = new HttpClientHandler
{
    // Test tenants often present a self-signed cert. Delete this line in production.
    ServerCertificateCustomValidationCallback =
        HttpClientHandler.DangerousAcceptAnyServerCertificateValidator,
};
using var client = new HttpClient(handler) { Timeout = TimeSpan.FromMinutes(2) };
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

var token = await GetTokenAsync(client);
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token);

// Get price pages for supplier
var filter = Uri.EscapeDataString($"supplier_id eq {SupplierId} and row_status_flag eq 704");
var select = Uri.EscapeDataString("price_page_uid,description,calculation_value1");
var orderby = Uri.EscapeDataString("description");

var url = $"{BaseUrl}/odataservice/odata/table/price_page?$filter={filter}&$select={select}&$orderby={orderby}";
var response = await client.GetAsync(url);
response.EnsureSuccessStatusCode();

var json = await response.Content.ReadAsStringAsync();
var data = JsonDocument.Parse(json).RootElement;
foreach (var page in data.GetProperty("value").EnumerateArray())
{
    var uid = page.GetProperty("price_page_uid");
    var description = page.GetProperty("description");
    var calcValue = page.GetProperty("calculation_value1");
    Console.WriteLine($"{uid} {description} {calcValue}");
}

// --- helpers ---------------------------------------------------------------

// v2 token endpoint — credentials go in the body, never in headers.
static async Task<string> GetTokenAsync(HttpClient client)
{
    var payload = JsonSerializer.Serialize(new { username = Username, password = Password });
    var response = await client.PostAsync(
        $"{BaseUrl}/api/security/token/v2",
        new StringContent(payload, Encoding.UTF8, "application/json"));
    response.EnsureSuccessStatusCode();
    return ReadField(await response.Content.ReadAsStringAsync(), "AccessToken");
}

// Some middleware answers this endpoint in XML even when asked for JSON.
static string ReadField(string payload, string field)
{
    try
    {
        var value = JsonDocument.Parse(payload).RootElement.GetProperty(field).GetString();
        if (!string.IsNullOrEmpty(value)) return value;
    }
    catch (Exception ex) when (ex is JsonException or KeyNotFoundException) { }

    var match = System.Text.RegularExpressions.Regex.Match(payload, $"<{field}>([^<]+)</{field}>");
    if (!match.Success)
        throw new InvalidOperationException(
            $"No {field} in response: {payload[..Math.Min(200, payload.Length)]}");
    return match.Groups[1].Value;
}

Pagination Helper

"""Fetch all records from a table via $skip/$top pagination."""
import re

import httpx

# ---- EDIT THESE -----------------------------------------------------------
BASE_URL = "https://play.example.com"   # your P21 server
USERNAME = "apiuser"
PASSWORD = "your-password"
VERIFY_SSL = False                        # True once you trust the cert chain
TABLE = "supplier"                        # table to page through
FILTER_EXPR = "row_status_flag eq 704"    # optional; set to None for no filter
# ---------------------------------------------------------------------------


def get_token(client: httpx.Client) -> str:
    """v2 token endpoint — credentials go in the body, never in headers."""
    r = client.post(
        f"{BASE_URL}/api/security/token/v2",
        json={"username": USERNAME, "password": PASSWORD},
        headers={"Accept": "application/json"},
    )
    r.raise_for_status()
    try:
        return r.json()["AccessToken"]
    except (ValueError, KeyError):  # some middleware answers in XML
        match = re.search(r"<AccessToken>([^<]+)</AccessToken>", r.text)
        if not match:
            raise ValueError(f"No AccessToken in response: {r.text[:200]}") from None
        return match.group(1)


def get_all_records(client, headers, base_url, table, filter_expr=None, page_size=5000):
    """Fetch all records with automatic pagination.

    Args:
        client: An authenticated httpx.Client.
        headers: Request headers, including the Authorization bearer token.
        base_url: OData service base URL.
        table: Table name to query.
        filter_expr: Optional OData $filter expression.
        page_size: Records per request. Larger values mean fewer HTTP
            round-trips, which is usually the biggest performance factor.
            Use 5,000-25,000 for bulk/preload scenarios. Use 50-200
            when paginating for a UI. There is no documented server-side
            maximum -- 25,000 has been verified in production.
    """
    records = []
    skip = 0

    while True:
        params = {"$top": page_size, "$skip": skip, "$count": "true"}
        if filter_expr:
            params["$filter"] = filter_expr

        response = client.get(
            f"{base_url}/table/{table}",
            params=params,
            headers=headers,
        )
        response.raise_for_status()
        data = response.json()

        records.extend(data["value"])
        total = data.get("@odata.count", len(records))

        if len(records) >= total:
            break
        skip += page_size

    return records


with httpx.Client(verify=VERIFY_SSL, timeout=120, follow_redirects=True) as client:
    token = get_token(client)
    headers = {
        "Authorization": f"Bearer {token}",
        "Accept": "application/json",       # without this you get XML, not JSON
        "Content-Type": "application/json",
    }

    all_records = get_all_records(
        client,
        headers,
        f"{BASE_URL}/odataservice/odata",
        TABLE,
        filter_expr=FILTER_EXPR,
        page_size=200,
    )
    print(f"{len(all_records)} records fetched from {TABLE}")

using System.Net.Http.Headers;
using System.Text;
using System.Text.Json;

// ---- EDIT THESE -----------------------------------------------------------
const string BaseUrl = "https://play.example.com";   // your P21 server
const string Username = "apiuser";
const string Password = "your-password";
const string Table = "supplier";                        // table to page through
const string FilterExpr = "row_status_flag eq 704";     // optional; pass null for no filter
// ---------------------------------------------------------------------------

var handler = new HttpClientHandler
{
    // Test tenants often present a self-signed cert. Delete this line in production.
    ServerCertificateCustomValidationCallback =
        HttpClientHandler.DangerousAcceptAnyServerCertificateValidator,
};
using var client = new HttpClient(handler) { Timeout = TimeSpan.FromMinutes(2) };
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

var token = await GetTokenAsync(client);
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token);

var records = await GetAllRecordsAsync(
    client, $"{BaseUrl}/odataservice/odata", Table, FilterExpr, pageSize: 200);
Console.WriteLine($"{records.Count} records fetched from {Table}");

// --- helpers ---------------------------------------------------------------

/// <summary>
/// Fetch all records with automatic pagination.
/// </summary>
/// <param name="client">An authenticated HttpClient.</param>
/// <param name="baseUrl">OData service base URL.</param>
/// <param name="table">Table name to query.</param>
/// <param name="filterExpr">Optional OData $filter expression.</param>
/// <param name="pageSize">
/// Records per request. Larger values mean fewer HTTP round-trips,
/// which is usually the biggest performance factor.
/// Use 5,000-25,000 for bulk/preload scenarios. Use 50-200
/// when paginating for a UI. There is no documented server-side
/// maximum -- 25,000 has been verified in production.
/// </param>
static async Task<List<JsonElement>> GetAllRecordsAsync(
    HttpClient client, string baseUrl, string table,
    string? filterExpr = null, int pageSize = 5000)
{
    var records = new List<JsonElement>();
    var skip = 0;

    while (true)
    {
        var query = $"$top={pageSize}&$skip={skip}&$count=true";
        if (filterExpr != null)
            query += $"&$filter={Uri.EscapeDataString(filterExpr)}";

        var url = $"{baseUrl}/table/{table}?{query}";
        var response = await client.GetAsync(url);
        response.EnsureSuccessStatusCode();

        var json = await response.Content.ReadAsStringAsync();
        var data = JsonDocument.Parse(json).RootElement;

        foreach (var item in data.GetProperty("value").EnumerateArray())
            records.Add(item.Clone());

        var total = data.TryGetProperty("@odata.count", out var countProp)
            ? countProp.GetInt32()
            : records.Count;

        if (records.Count >= total)
            break;
        skip += pageSize;
    }

    return records;
}

// v2 token endpoint — credentials go in the body, never in headers.
static async Task<string> GetTokenAsync(HttpClient client)
{
    var payload = JsonSerializer.Serialize(new { username = Username, password = Password });
    var response = await client.PostAsync(
        $"{BaseUrl}/api/security/token/v2",
        new StringContent(payload, Encoding.UTF8, "application/json"));
    response.EnsureSuccessStatusCode();
    return ReadField(await response.Content.ReadAsStringAsync(), "AccessToken");
}

// Some middleware answers this endpoint in XML even when asked for JSON.
static string ReadField(string payload, string field)
{
    try
    {
        var value = JsonDocument.Parse(payload).RootElement.GetProperty(field).GetString();
        if (!string.IsNullOrEmpty(value)) return value;
    }
    catch (Exception ex) when (ex is JsonException or KeyNotFoundException) { }

    var match = System.Text.RegularExpressions.Regex.Match(payload, $"<{field}>([^<]+)</{field}>");
    if (!match.Success)
        throw new InvalidOperationException(
            $"No {field} in response: {payload[..Math.Min(200, payload.Length)]}");
    return match.Groups[1].Value;
}

Best Practices

1. Always use $select - Only request fields you need
2. Add $filter early - Filter server-side, not client-side
3. Use $top for previews - Don't fetch all data unnecessarily
4. Right-size your page size - Use large $top (5,000-25,000) for bulk/preload fetches; use small $top (50-200) for UI pagination. Round-trip overhead dwarfs payload size — see Page Size Guidance
5. Escape strings properly - Double single quotes in values
6. Handle null values - Check for null in filters and responses

Common Errors