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 v3 protocol. It's the fastest way to query P21 tables and views.

Key Characteristics

Endpoints

Endpoint Purpose
/odataservice/odata/table/{tablename} Query a database table
/odataservice/odata/view/{viewname} Query a database view

Base URL Example

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

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

$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

$count - Get Count

Include total count in response:

/odata/table/supplier?$count=true

Filter Expressions

Comparison Operators

Operator Meaning Example
eq Equal $filter=supplier_id eq 10050
ne Not equal $filter=status ne 'Inactive'
gt Greater than $filter=amount gt 100
ge Greater or equal $filter=date ge 2025-01-01
lt Less than $filter=quantity lt 50
le Less or equal $filter=price le 99.99

Logical Operators

Operator Example
and $filter=supplier_id eq 10050 and row_status_flag eq 704
or $filter=status eq 'A' or status eq 'B'
not $filter=not endswith(name,'Inc')

String Functions

Function Example
startswith $filter=startswith(supplier_name,'ABC')
endswith $filter=endswith(supplier_name,'Inc')
contains $filter=contains(description,'filter')

Null Checks

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

Common Patterns

Active Record Filter

P21 uses row_status_flag to track record status. Active records have row_status_flag = 704:

$filter=row_status_flag eq 704

Always include this filter when querying for active data:

$filter=supplier_id eq 10050 and row_status_flag eq 704

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:

from datetime import date, timedelta

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

Data Type Formatting

Type Format Example
String Single quotes 'Active'
Number No quotes 10050
Decimal No quotes 99.99
Date ISO format 2025-01-01
DateTime ISO format 2025-01-01T00:00:00.000Z
Boolean No quotes true or false
GUID No quotes 5BC2E4CE-0C0A-4394-A066-29B5835424DA

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:

from urllib.parse import quote

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)

# Using with httpx (handles URL encoding automatically):
params = {"$filter": safe_item_filter("1/2-FITTING")}
response = httpx.get(f"{base_url}/table/inv_mast", params=params, headers=headers)
# httpx encodes the filter value in the query string automatically

Tip: When using httpx with the params dict, 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

Table Description
supplier Supplier master data
customer Customer records
inv_mast Inventory master
price_page Price page definitions
price_book Price book records
price_library Price library definitions
product_group Product groups

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

Python Examples

Basic Query

import httpx
from scripts.common.auth import get_token, get_auth_headers
from scripts.common.config import load_config

config = load_config()
token_data = get_token(config)
headers = get_auth_headers(token_data["AccessToken"])

# Query suppliers
response = httpx.get(
    f"{config.odata_url}/table/supplier",
    params={"$top": 10, "$select": "supplier_id,supplier_name"},
    headers=headers,
    verify=False
)

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

Filtered Query

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

response = httpx.get(
    f"{config.odata_url}/table/price_page",
    params=params,
    headers=headers,
    verify=False
)

Pagination Helper

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

    Args:
        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 = httpx.get(
            f"{base_url}/table/{table}",
            params=params,
            headers=headers,
            verify=False
        )
        data = response.json()

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

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

    return records

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

Error Cause Solution
400 Bad Request Invalid filter syntax Check filter expression
401 Unauthorized Invalid/expired token Refresh token
401/403 "Not authorized" Valid token but missing P21 permissions Enable "Allow OData API Service" in User Maintenance and grant table access in Role Maintenance → Dataservice Permission. See Prerequisites
404 Not Found Table doesn't exist, or unsupported function Verify table name; avoid now()
500 Server Error Query too complex Simplify query

now() Function Not Supported

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

# 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}"}

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

Scenario Recommended $top Why
Preloading / caching / bulk export 5,000 - 25,000 Minimize HTTP round-trips; 1 request beats 22
UI pagination (browsable tables) 50 - 200 Match what the user actually sees
Unbounded queries (unknown size) 1,000 - 5,000 Balance round-trips vs memory

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:

Page Size Requests Wall Time
100 25 ~16s
5,000 1 ~1.7s

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

Performance Tips

Measured Performance

Query Type Records Time
Simple table 10 ~100ms
Filtered query 160 ~115ms
Full table scan 1000+ ~500ms
Bulk fetch ($top=5000) 2,500 ~1.7s

Avoiding N+1 Query Patterns

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

# 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!

Solution 1: Batch queries

Fetch all related data upfront with IN clauses or multiple conditions:

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

# Get all links in fewer queries
for page_uid in page_uids:
    links = await odata.query("price_page_x_book",
                               filter_expr=f"price_page_uid eq {page_uid}")

Solution 2: Cache lookups

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

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

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)
  2. Go to Administration from the menu
  3. Find the "Refresh OData API service" section
  4. Click "Refresh OData API service"

SOA Admin - 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.