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.)

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.p21server.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.p21server.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. ifp_G21_Alerts, ifp_g21_alerts and IFP_G21_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.

Authentication

Include the Bearer token in the Authorization header:

GET /odataservice/odata/table/supplier HTTP/1.1
Host: play.p21server.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

$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. Verified on 26.1.5910.3 — see Avoiding N+1 Query Patterns. | not | $filter=not endswith(name,'Inc') |

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.

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).

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.

Data Type Formatting

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.p21server.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.p21server.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.p21server.com/odataservice/odata/$metadata#supplier",
    "value": [
        {
            "supplier_id": 10050,
            "supplier_name": "ABC 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": 10397,
      "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": 10397,
      "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.p21server.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.p21server.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.p21server.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.p21server.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.p21server.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.p21server.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

now() Function Not Supported

The standard OData now() function returns 404 in P21. Use explicit date values instead:

Full runnable version: Code Examples — the Filtered Query example is a complete, authenticated program using the same params pattern.

# Calculate date in code
from datetime import date, timedelta
tomorrow = (date.today() + timedelta(days=1)).isoformat()

# Use in filter
params = {"$filter": f"expiration_date ge {tomorrow}"}

// Calculate date in code
var tomorrow = DateTime.Today.AddDays(1).ToString("yyyy-MM-dd");

// Use in filter
var filter = Uri.EscapeDataString($"expiration_date ge {tomorrow}");
var url = $"{odataUrl}/table/price_page?$filter={filter}";

Page Size Guidance

Each HTTP request carries overhead: TCP connection, authentication, query planning, and serialization. For large result sets, the number of round-trips is usually the biggest performance factor, not the response payload size.

Choosing a Page Size

No documented server-side cap. The P21 SDK does not specify a maximum page size. The Web.config allows up to 100 MB responses. A $top=25000 request has been verified working in production. Test with your dataset, but don't assume 100 is the right default.

Real-World Impact

Fetching ~2,500 records with $select on a few columns:

The 10x improvement comes entirely from eliminating round-trip overhead.

Performance Tips

• Minimize HTTP round-trips - This is the #1 factor. Use a large $top when you need all the data instead of looping with small pages
• Always use $select - Only request fields you need; smaller payloads transfer faster
• Filter server-side - Use $filter instead of fetching everything and filtering in code
• Use views for pre-joined data when available
• For UI-driven pagination, match page size to display needs (50-200 rows)

Measured Performance

Avoiding N+1 Query Patterns

When working with related entities (e.g., pages → books → libraries), avoid fetching related data in a loop:

Full runnable version: Pagination Helper — fetch each related set with a single paged query instead of one call per row.

# BAD: N+1 queries - one query per page
for page in pages:
    book = await odata.get_book_for_page(page['uid'])  # N queries!
    library = await odata.get_library_for_book(book['uid'])  # N more!

// BAD: N+1 queries - one query per page
foreach (var page in pages)
{
    var book = await odata.GetBookForPageAsync(page["uid"]!.ToString());    // N queries!
    var library = await odata.GetLibraryForBookAsync(book["uid"]!.ToString()); // N more!
}

Solution 1: Batch queries

Collapse the N follow-up queries into one by joining the ids with or. This is the real fix for the N+1 above — the loop below is what it replaces.

The in operator is silently ignored — do not use it

The obvious way to batch is $filter=price_page_uid in (2,3,4). It returns HTTP 200 and the entire table. Verified on 26.1.5910.3 (2026-08-11) against a view holding 50,077 rows:

Filter	Result
(none)	200 — 50,077 rows
price_page_uid in (2,3,4)	200 — 50,077 rows (filter dropped)
price_page_uid eq 2 or price_page_uid eq 3	200 — 2 rows (correct)
price_page_uid zz 2 (deliberate garbage)	404 — Syntax error at position 17

The service does validate filter syntax — garbage is rejected loudly. in parses and is then discarded, so you get a successful-looking response, no warning, and every row in the table. On a large view that is a silent full-table scan feeding whatever you do next. Use an or chain.

Keep the chain to a sane length — URLs have limits. Batch ids in groups of ~50 and issue one request per group; that is still an enormous improvement over one request per id.

# Get all pages first -- one query
pages = await odata.query("price_page", filter_expr="supplier_id eq 10050")
page_uids = [p["price_page_uid"] for p in pages]

# Then ONE query per batch of ids instead of one per id.
# `in` is silently ignored on this service -- join with `or`.
BATCH = 50
links = []
for i in range(0, len(page_uids), BATCH):
    chunk = page_uids[i:i + BATCH]
    or_chain = " or ".join(f"price_page_uid eq {uid}" for uid in chunk)
    links += await odata.query("price_page_x_book", filter_expr=or_chain)

// Get all pages first -- one query
var pages = await odata.QueryAsync("price_page", filterExpr: "supplier_id eq 10050");
var pageUids = pages.Select(p => p["price_page_uid"]!.ToString()).ToList();

// Then ONE query per batch of ids instead of one per id.
// `in` is silently ignored on this service -- join with `or`.
const int Batch = 50;
var links = new List<JsonElement>();
for (var i = 0; i < pageUids.Count; i += Batch)
{
    var chunk = pageUids.Skip(i).Take(Batch);
    var orChain = string.Join(" or ", chunk.Select(uid => $"price_page_uid eq {uid}"));
    links.AddRange(await odata.QueryAsync("price_page_x_book", filterExpr: orChain));
}

Solution 2: Cache lookups

For repeated lookups (like library-to-book mapping), cache results:

Full runnable version: Code Examples — no full program in this doc wraps caching directly; combine the cache pattern below with the authenticated request/response handling shown in Basic Query.

class P21OData:
    def __init__(self):
        self._library_book_cache: dict[str, dict | None] = {}

    async def get_book_for_library(self, library_id: str) -> dict | None:
        # Return cached result if available
        if library_id in self._library_book_cache:
            return self._library_book_cache[library_id]

        # Fetch and cache
        result = await self._fetch_book_for_library(library_id)
        self._library_book_cache[library_id] = result
        return result

public class P21OData
{
    private readonly Dictionary<string, JObject?> _libraryBookCache = new();

    public async Task<JObject?> GetBookForLibraryAsync(string libraryId)
    {
        // Return cached result if available
        if (_libraryBookCache.TryGetValue(libraryId, out var cached))
            return cached;

        // Fetch and cache
        var result = await FetchBookForLibraryAsync(libraryId);
        _libraryBookCache[libraryId] = result;
        return result;
    }
}

The other OData surface: /data/erp/views/v1

A P21 server exposes two OData endpoints. Everything above describes /odataservice/odata/. The middleware's API Reference page also lists a Data Services family at /data/erp/views/v1 — an older, narrower surface that is genuinely useful for one thing the current one does awkwardly: fetching a single record by key.

Verified on a 26.1 tenant, August 2026.

Which one you want

Both are read-only, both need the same token, and neither can join — you assemble related data client-side.

The SDK page for this endpoint claims v4, which appears to be copied from its sibling page. The wire format says v3 (odata.metadata at the document root, #p21_view_oe_hdr/@Element for a single entity). Trust the wire.

Listing what's exposed

GET {base}/data/erp/views/v1

Returns the 118 available views. The set is inventory- and order-centric — p21_view_oe_hdr, p21_view_oe_line, p21_view_invoice_hdr, p21_view_po_hdr, p21_view_inv_mast, p21_view_inv_loc, p21_view_lot, p21_view_prod_order_hdr, p21_view_job_price_hdr, the allocation-finder views, and so on. If your view is on the list, this endpoint is often the shorter path; if not, use /odataservice/.

Fetching one record by key

This is the reason to know the endpoint exists:

GET {base}/data/erp/views/v1/p21_view_oe_hdr('1013938')

{"odata.metadata": "{base}/data/erp/views/v1/$metadata#p21_view_oe_hdr/@Element",
 "order_no": "1013938", "customer_id": "13162", "order_date": "..."}

For a view with a compound key, name each part — and mind the OData type suffixes, which is where this bites:

GET {base}/data/erp/views/v1/p21_view_customer(company_id = '1', customer_id = 100915M)

The trailing M marks an Edm.Decimal. Omitting it, or quoting a numeric key, produces the same incompatible types family of errors documented under Data Type Formatting — the rules there apply to both surfaces.

Documented limitations

• Read-only — no create, update or delete.
• No joins — retrieve each set and join locally.
• Curated views only — the list above is all of it.
• User Defined Fields cannot be queried. (Stated by Epicor for this surface. Not re-tested against /odataservice/, which does expose UDF columns on the tables that carry them.)

(Source: middleware API Reference ({middleware}/docs/apiref.aspx) and the SDK Data Services v1 reference guide; view count and key addressing verified live.)

OData Schema Refresh

The OData service automatically picks up changes to existing table/view schemas (e.g., column type changes). However, when new tables or views are added to the database, the OData service must be manually refreshed:

1. Log in to the SOA Middleware home page (https://{hostname}/api/admin) — this requires the P21 user's Access to SOA Admin Page application-security setting to be Yes; see Authentication § Application Security settings that affect API access
2. Go to Administration from the menu
3. Find the "Refresh OData API service" section
4. Click "Refresh OData API service"

Note: Schema changes from P21 application upgrades are handled automatically. Manual refresh is only needed for ad-hoc database changes between upgrades.

Related

• Authentication
• API Selection Guide
• Error Handling
• Batch Processing Patterns - Caching and N+1 query patterns
• examples/python/odata/ - Working examples