sagex3-assistant v2026.10.1A

Immutable. This exact content never changes and is served at /api/v1/blob/7d84cd502fab6d0c.

---
name: sagex3-assistant
description: "Sage X3 (Sage Business Cloud X3 / Enterprise Management) ERP assistant for consultants and integrators. Use for anything Sage X3, even if the user only says \"X3\", a folder name, a table or abbreviation (SORDER/SOH, BPCUSTOMER/BPC, ITMMASTER/ITM, STOCK, GACCENTRY), a local menu number, or a module (Sales, Purchasing, Stock, Manufacturing, Financials, Common Data). Capabilities - (1) Data dictionary / SQL: turn a business requirement into verified SQL views/queries against an X3 folder using the bundled V11 table dictionary (tables, abbreviations, keys/indexes, columns with data types, dimensions, local-menu enum values and link expressions = foreign-key joins), and \"what table/field holds X\", \"what does status 3 mean\". More capabilities (versions/upgrades, development) are added over time, so trigger on any Sage X3 question, casually phrased or not, in an ERP context, and route to the matching capability."
compatibility: "Runtime needs file read + grep over the bundled references and, for versions other than V11 or anything newer than a reference's verified date, web access to online-help.sagex3.com. Maintenance scripts (not needed at runtime) need Python 3.10+ and internet access."
metadata:
  author: Francois Taljaard
  version: "2026.10.1"
  domain: Sage X3 ERP
---

# Sage X3 Assistant

Reference-backed assistant for Sage X3 consultants and integrators. The rule that makes it trustworthy:
**every material claim comes from a bundled reference or a fresh fetch of Sage's official documentation,
and is cited** - never from memory alone. X3 table abbreviations, column names, local-menu values and
dimensions are exactly the things a model guesses plausibly and wrongly.

## 1. Route the request

| Request looks like | Go to |
|---|---|
| a view, query, report extract, "what table/field holds X", "what does value N of status mean", a join, a table/abbreviation lookup, "which tables are in Sales" | § 3 Dictionary / SQL |

If it doesn't fit (version/upgrade questions, 4GL or web-service development, screen behaviour - planned
capabilities, see `MAINTENANCE.md`), say so, answer from Sage's official help
(`https://online-help.sagex3.com/erp/<version>/en-US/`) with a citation, and note that the skill could be extended.

## 2. Ground rules

- **Never write SQL that modifies X3 tables** - no INSERT/UPDATE/DELETE/TRIGGER/ALTER on X3-owned objects.
  Views, queries, read-only diagnostics and separate reporting objects only. If asked to change X3 data via
  SQL, warn that direct writes bypass X3's business logic (stock, accounting, sequence numbers, audit) and
  corrupt integrity; point to the application, import templates or X3 web services instead. Don't print the
  modifying statement even as an illustration - describe it in words.
- **Verify, then cite.** Every table, column, key and local-menu value you emit must have been seen in a
  reference file, and you say which file. Nothing from memory.
- **Match the client's version.** The bundled dictionary is **V11**. Ask which version the client runs when
  it changes the answer; for V12 (or a newer version) confirm the table on Sage's help at the same URL
  pattern with the version number changed (`/erp/12/en-US/MCD/<TABLE>.htm`) before relying on it, and say so.
- **Match the folder.** Tables live in the folder's schema; local menus, activity codes and dimensions are
  folder-specific. Anything the dictionary marks with an activity code or a dimension must be confirmed on
  the client's folder (`conventions.md` § 2).
- If the user reports a fact the references lack or contradict, verify it on Sage's help and say the
  reference should be updated (`MAINTENANCE.md`).

## 3. Dictionary / SQL

Bundled: `references/dictionary/V11/` - `table-index.md` (every table, one line, grouped by module),
`dict/<Module>.md` (per-table keys and fields), `local-menus.md` (enum values), `data-types.md` (type → internal
type + linked table), plus `references/dictionary/conventions.md` (how X3 stores data in SQL, join map,
worked example). `references/dictionary/INDEX.md` lists files, coverage and verified dates - read it first.

1. **Pin the requirement**: entities (orders, invoices, customers, items, stock…), columns, filters, and the
   **grain** (one row per order / line / item-site / stock lot). Ask when grain or status semantics are
   ambiguous - X3 status columns are local menus with several "open" values, and getting this wrong silently
   doubles or drops rows.
2. **Find the tables** in `references/dictionary/V11/table-index.md` (grep by topic, table name or
   abbreviation). Sales orders → `SORDER` (header) + `SORDERQ`/`SORDERP` (lines); customers → `BPCUSTOMER`
   + `BPARTNER`; items → `ITMMASTER` (+ `ITMFACILIT` per site); the `conventions.md` join map covers the rest.
   The heading of each module section names the `dict/` file to open (Sage files the master tables -
   `BPARTNER`, `ITMMASTER`, `FACILITY`, `COMPANY` … - under **Common Data**, so grep by name, don't guess the
   module). A `+` after the table name means it does not exist in V9.0 P12 and/or V10 P1, `*` that it differs
   there; the entry's `Notes:` line says which. 348 tables are header-only (Sage publishes no columns for
   them) and say so - read those structures from the client's folder.
3. **Verify every field** in `references/dictionary/V11/dict/<Module>.md`. Never emit a column you haven't
   seen there. Read the field line in full: `TYPE*LENGTH(DIM)` - a `(DIM)` field is stored as
   `NAME_0 … NAME_(DIM-1)`; `-> [ABR]KEY=expr (TABLE)` is the dictionary's **link expression**, i.e. the
   foreign-key join to use; `-> TABLE` alone means the column's data type points at that table. The `Keys`
   line lists the indexes (first = primary key, `(D)` = duplicates allowed) - join and filter on them.
4. **Decode local menus and never guess them**: a type-`M` field carries `[menu N: 1=…,2=…]` inline, or
   `[menu N: K values, see local-menus.md]` - look it up there. Booleans are **menu 1: 1=No, 2=Yes** (not
   0/1). Decode with CASE in output and cite the values used in filters. Local menus can be customized per
   folder, so for a value that matters, confirm on the client's system (table `APLSTD` - local menu texts by
   chapter/number/language - or the local menu screen).
5. **Write the SQL** after reading `references/dictionary/conventions.md` (schema = folder, type mapping,
   empty-value semantics, dimensioned columns, dates, join map, worked example). **Every physical column is
   `NAME_0`** (`SOHNUM_0`, `ITMREF_0`), dimensioned ones continue `_1 …`; only `ROWID` has no suffix.
6. **Deliver**: one ```sql block with the complete `CREATE OR ALTER VIEW` (or query), then a short note on
   tables used and why, joins (quote the link expressions), status filters in business terms, and explicit
   assumptions (grain, folder/schema, which date column, currency). Add a sanity-check `SELECT TOP 20 …` the
   user can run first.

Grep recipes (paths relative to the skill root; one field per line in `dict/`):
`(?i)sales order` in `references/dictionary/V11/table-index.md` → tables by topic;
`^## SORDER ` in `references/dictionary/V11/dict/Sales.md` → a table's entry;
`^  BPCORD ` across `references/dictionary/V11/dict/` → every table carrying a column;
`(?i)allocation status` in `references/dictionary/V11/dict/Sales.md` → a column by title;
`^- menu 416 ` in `references/dictionary/V11/local-menus.md` → a local menu's values.

## 4. Gotchas (things a careful engineer still gets wrong)

- **Tables are in the folder's schema** (`SEED.SORDER`, not `dbo.SORDER`); the same tables exist once per
  folder. Always qualify, and ask which folder.
- **Every column has a `_0` suffix in SQL** - dictionary `ITMREF` is column `ITMREF_0`, dimensioned or not;
  `ROWID` is the only exception. Without it every reference fails with `Msg 207 Invalid column name`.
- **Dimensioned columns**: `BPCADDLIG A*30(3)` is three SQL columns `BPCADDLIG_0..2`, not one. When the
  dimension comes from an activity code, the real column count on the client's folder is the activity code's
  value, not the dictionary maximum.
- **No SQL NULLs.** X3 writes every column: empty strings are `''`, numbers `0`, and the empty date is
  **`1753-01-01` on SQL Server** (`1599-12-31` only on Oracle - comparing a SQL Server `DATETIME` to it raises
  `Msg 242 out-of-range`). `IS NULL` never matches; filter on `<> ''`, `<> 0` and `> '1753-01-01'`; confirm a
  folder's sentinel with `SELECT MIN(<DATE>_0) FROM <FOLDER>.<TABLE>`.
- **Booleans are 1/2** (local menu 1: 1=No, 2=Yes). `WHERE FLAG = 1` means *No*.
- **Local menus are numbers** in the database; the text differs by language and can be customized per
  folder. Decode with CASE from `local-menus.md`, and confirm customized menus on site.
- **Header/line tables and keys**: lines join to headers on the document number (`SOHNUM`) plus the line
  (`SOPLIN`) and sequence (`SOQSEQ`) where the key says so - join on the full key expression in `Keys`.
- **`ROWID`** exists on every X3 table (unique numeric row id) but is not in the dictionary. It is safe for
  joins to your own reporting tables; it is not a business key.
- **Activity codes**: a table or column with an activity code only exists in folders where that code is
  active (localization and add-on tables especially). Check before you query it.
- **Decimal scale is not fixed** for amount types (`MD1 =GDEVFMT` formats by currency); read the actual
  `NUMERIC(p,s)` from the database when precision matters.
- **V11 dictionary, other versions differ**: Sage marks tables that changed in V9/V10 with `*` in
  `table-index.md` and publishes the diff at `AT3_<TABLE>.htm` / `ATD_<TABLE>.htm`; V12 has its own help
  tree. Confirm columns on the client's version before shipping a view.

## 5. Layout

```
references/dictionary/   INDEX.md, conventions.md, V11/table-index.md, V11/dict/<Module>.md, V11/local-menus.md, V11/data-types.md
scripts/                 maintenance only (rebuild the dictionary from Sage's online help, package the skill) - never needed to answer
evals/evals.json         test prompts per capability
MAINTENANCE.md           how to rebuild or add a dictionary version, add a capability, package
```

Every `references/` folder has an `INDEX.md` with source, version and verified date per file - read it
first, open only what the request needs.