Adrien Geraldes This workflow corresponds to n8n.io template #18168 — we link there as the canonical source.
This workflow follows the Agent → Chat Trigger recipe pattern — see all workflows that pair these two integrations.
The workflow JSON
Copy or download the full n8n JSON below. Paste it into a new n8n workflow, add your credentials, activate. Full import guide →
{
"id": "emYPD7NmvzLkfcDr",
"name": "Query Sage X3 in natural language with an AI agent and an MCP server",
"tags": [],
"nodes": [
{
"id": "12cacbe3-0c69-400a-93c0-d6a9e5db836b",
"name": "Note - Overview",
"type": "n8n-nodes-base.stickyNote",
"position": [
-848,
1520
],
"parameters": {
"color": 7,
"width": 1300,
"height": 992,
"content": "## Query Sage X3 in natural language\n\nAn AI agent that answers sales and supply chain questions asked in plain English, by querying the Sage X3 database directly. The same tools are exposed as an **MCP server**, so they can also be used from Claude, Cursor or any other MCP client.\n\n### Who it is for\n\nADV, sales, supply chain and controlling teams who work on Sage X3 and lose time asking for extracts, and X3 integrators who want to add a conversational layer without building an API.\n\n### The problem it solves\n\nAnswering \"how many of this item are left\", \"what is my 2025 billed revenue by customer\" or \"what does this customer actually buy from me\" normally takes a SQL query, an extract or a ticket. Here the question is asked in plain English and the answer comes back in seconds.\n\n### What it does\n\n1. Receives the question through the n8n chat.\n2. The agent picks the right SQL tool, or tools, out of 14.\n3. Each tool runs a parameterised query on Sage X3, with the filters applied at call time.\n4. Internal X3 codes (local menus) and item descriptions are resolved to text directly in SQL, through APLSTD and ATEXTRA.\n5. The agent writes up the answer, stating currency and horizon.\n\n### The 14 tools\n\n**Reference data** - customer search, item search (short), full item record, customer item cross-reference.\n**Stock** - available by site, detail by lot and location, movements in detail and aggregated.\n**Sales orders** - line history, full read, creation.\n**Revenue** - one aggregate tool driven by a granularity parameter, and two detail tools (billed, ordered).\n\nThe split follows a single rule: **one aggregate to quantify, one detail to explain**. No tool overlaps another.\n\n### Watch out for\n\n- Order creation really writes into Sage X3. It is exposed on the MCP server: any client holding the token can call it without going through the confirmation rule of the system prompt. Remove that node from the MCP if you do not need it.\n- Revenue covers billed and ordered. Forecast is not exposed: the Sage X3 ORDERS table mixes replenishment suggestions with open sales order lines, with no reliable business partner and no usable valuation.\n- The queries are written in **T-SQL (Microsoft SQL Server)**. On Oracle, ISNULL, string concatenation and CAST have to be adapted."
},
"typeVersion": 1
},
{
"id": "f0fbbd3f-3b43-4fc8-83d2-612cafbb9bb9",
"name": "Note - Setup required",
"type": "n8n-nodes-base.stickyNote",
"position": [
544,
1520
],
"parameters": {
"color": 3,
"width": 1300,
"height": 976,
"content": "## Set this up before use\n\n### 1. Install the community node\n\nThis workflow depends on the **`n8n-nodes-nx3`** package, which provides the Sage X3 SOAP connector. Without it, the 14 tools will not appear.\n\nSettings > Community nodes > Install > `n8n-nodes-nx3`\n\n### 2. Create the credentials\n\n- **Nx3 SOAP API**: Sage X3 SOAP server URL, folder, user, password. To be selected on the 14 tool nodes.\n- **Bearer Auth**: on the MCP node, to protect the endpoint. Generate a token with `openssl rand -hex 32`.\n- **OpenRouter**: API key on the language model node. Any compatible provider will do.\n\n### 3. Replace the placeholder values\n\n| Where | Value | Replace with |\n|---|---|---|\n| `x3_sales_order_create` | `YOUR_X3_USER` | The X3 user code the orders are created under |\n| MCP node | path `sage-x3-mcp` | Any path you like |\n\n### 4. Test it\n\nActivate the workflow, open the chat and ask: *\"list my customers in category FR\"*, then *\"what is my billed revenue by month for 2025\"*. If the first question answers and the second returns amounts in euros, the whole chain works.\n\n### Language of the X3 labels\n\nLocal menus and item descriptions are read in English (`LAN_0='ENG'`, `LANGUE_0='ENG'`). If your folder does not hold the English translations, replace `'ENG'` with your own language code in the SQL of the tool nodes."
},
"typeVersion": 1
},
{
"id": "73c3c54a-28dd-4083-9b0b-05836276bd9c",
"name": "Note - Section 1 conversation",
"type": "n8n-nodes-base.stickyNote",
"position": [
0,
0
],
"parameters": {
"color": 4,
"width": 580,
"height": 600,
"content": "## 1. Conversation interface\n\nThe n8n chat receives the question, the agent picks the tools, and the memory keeps the thread so follow-up questions work (\"and for 2026?\").\n\nThe agent system prompt carries most of the logic: routing across the 14 tools."
},
"typeVersion": 1
},
{
"id": "2ffb7642-ec33-48b9-a37f-882c3b466a59",
"name": "Note - Section 2 MCP",
"type": "n8n-nodes-base.stickyNote",
"position": [
624,
0
],
"parameters": {
"color": 4,
"width": 360,
"height": 600,
"content": "## 2. MCP exposure\n\nThe same 14 tools are published on an MCP endpoint, usable from Claude, Cursor or any compatible client.\n\nProtected by Bearer Auth. Careful: order creation is exposed there with no confirmation guard."
},
"typeVersion": 1
},
{
"id": "fd35dac1-b17d-4a80-b359-a98329fac359",
"name": "Note - Section 3 reference data",
"type": "n8n-nodes-base.stickyNote",
"position": [
-848,
672
],
"parameters": {
"color": 5,
"width": 640,
"height": 800,
"content": "## 3. Reference data - turn a name into a code\n\nEvery question that quotes a name comes through here first. The agent turns \"Urban Cycle\" into `FR001` and \"adult bike\" into `FIN008` before querying orders, stock or revenue.\n\n`x3_item_search` returns 10 columns and is there to **find** an item. `x3_item_detail` returns the full record, around sixty columns, and is there to **read** the setup of an item that is already identified. The fourth tool covers the case where the customer uses their own item reference.\n\nItem descriptions are resolved from **ATEXTRA** (`CODFIC_0='ITMMASTER'`, `ZONE_0='DES1AXX'`, `LANGUE_0='ENG'`), falling back to `ITMDES1_0` when no translation exists. Search by description queries both the base language and the translation."
},
"typeVersion": 1
},
{
"id": "c51d3237-5255-4ab3-a7c9-a3d843b53acd",
"name": "Note - Section 4 stock",
"type": "n8n-nodes-base.stickyNote",
"position": [
-176,
672
],
"parameters": {
"color": 5,
"width": 640,
"height": 800,
"content": "## 4. Stock - levels and movements\n\n`x3_stock_available` answers \"how much is left\", by site. The figure to quote is `STOCK_AVAILABLE`, which removes allocations: it is not `STOCK_TOTAL`.\n\n`x3_stock_detail` answers \"where is the stock, which lots\". The two movement tools cover the journal detail and the aggregated volumes over a period."
},
"typeVersion": 1
},
{
"id": "e2e66427-d271-4cd6-8725-aae948b53e79",
"name": "Note - Section 5 sales orders",
"type": "n8n-nodes-base.stickyNote",
"position": [
512,
672
],
"parameters": {
"color": 5,
"width": 640,
"height": 800,
"content": "## 5. Sales orders\n\nHistory of ordered lines, full read of one order from its number, and creation.\n\nFor **what is left to deliver** rather than what was ordered, use `x3_revenue_ordered` in section 6.\n\n**Creation really writes into Sage X3.** The system prompt requires an explicit confirmation before the call, but that rule only applies to the conversational path, not to direct MCP calls."
},
"typeVersion": 1
},
{
"id": "66ddeeed-5d98-4d69-92cb-8adbaebc9099",
"name": "Note - Section 6 revenue",
"type": "n8n-nodes-base.stickyNote",
"position": [
1200,
672
],
"parameters": {
"color": 5,
"width": 640,
"height": 800,
"content": "## 6. Revenue - one aggregate, two details\n\n`x3_revenue_summary` is the entry point: it aggregates on the database side, converts to euros through `TABCHANGE` at the daily rate, and its **`level`** parameter picks the granularity - `Total`, `Month`, `Customer`, `Item` or `CustomerItem`. On its own it answers any total, ranking, trend or period comparison.\n\n`x3_revenue_billed` and `x3_revenue_ordered` are only there to explain a figure already obtained, each with the columns specific to its horizon: document type and shipment site for billed, net price and delivered, invoiced, allocated and short quantities for the order book.\n\nThe business sign follows `TABSIVTYP.INVTYP_0`: credit memos and credit notes negative, proformas at zero. The `LINES_WITHOUT_RATE` column flags the lines whose currency has no exchange rate: their amount is excluded from the total.\n\n**No forecast.** The `ORDERS` table mixes replenishment suggestions with no business partner and open lines coming from sales orders, which would double count against the order book. It is not exposed."
},
"typeVersion": 1
},
{
"id": "edf7cfb8-7e3a-4a5c-889f-7f550be8acce",
"name": "Receive the user question",
"type": "@n8n/n8n-nodes-langchain.chatTrigger",
"position": [
64,
240
],
"parameters": {
"options": {}
},
"typeVersion": 1.4
},
{
"id": "5df79407-1f8a-434e-8978-47efe2914a2c",
"name": "Analyse the question and query Sage X3",
"type": "@n8n/n8n-nodes-langchain.agent",
"position": [
288,
240
],
"parameters": {
"options": {
"systemMessage": "You are a sales and supply chain analyst working on Sage X3. You answer only from the data returned by your tools.\n\n## Choosing the right tool\n\nReference data, to resolve a name into a code before any other query:\n- x3_customer_search: customers. Customer name to BPCNUM_0 code.\n- x3_item_search: items, short answer. Description to ITMREF_0 reference. This is the default item entry point.\n- x3_item_detail: full item record. Only for a setup question about an item that is already identified.\n- x3_customer_product: mapping between the X3 reference and the customer's own reference, when the customer quotes an unknown reference.\n\nStock:\n- x3_stock_available: how much is left, by site. The figure to quote is STOCK_AVAILABLE.\n- x3_stock_detail: where the stock is, which lots, what sits in quality control.\n- x3_stock_movements_summary: inbound and outbound volumes over a period.\n- x3_stock_movements: movement detail, only when the detail is asked for.\n\nSales orders:\n- x3_sales_order_lines: history of ordered lines, ordered quantities.\n- x3_sales_order_read: full read of one order from its exact number.\n- x3_sales_order_create: creation. Irreversible write.\n\nRevenue:\n- x3_revenue_summary: ALWAYS FIRST. Totals converted to euros, with a level parameter that picks the granularity: Total, Month, Customer, Item, CustomerItem. On its own it answers any total, ranking, trend or period comparison.\n- x3_revenue_billed: billed revenue detail, the actuals.\n- x3_revenue_ordered: order book detail, what is left to deliver.\n\nThere is no forecast revenue tool: do not invent one, and say the data is not available when the question is about sales forecasts.\n\nRouting rule: a total or a ranking goes through an aggregate tool (x3_revenue_summary, x3_stock_movements_summary, x3_stock_available). Detail tools only explain a figure that has already been obtained. Never sum detail lines yourself.\n\n## Tool response format\n\nSQL tools return an object { rowCount, columns, rows } where rows is an array of arrays. Map each value to its name through columns, in order. Never assume the column order without reading columns.\n\n## Call rules\n\n1. Never add single quotes or % characters around filter values. The tools add them themselves. Write FIN008 and not 'FIN008', write Bike and not %Bike%.\n2. Multi-value filters are separated by a comma with no space: FR001,FR002. Filters of the same tool combine with AND.\n3. Filter at call time and always bound the dates when the question covers a period.\n4. On x3_revenue_summary, always fill in level. Without it the five granularities come back mixed together and the result is unusable. Columns outside the requested level are NULL, that is expected.\n5. Resolve names into codes before querying orders, stock or revenue.\n6. Never add up amounts in different currencies. Use REVENUE_EUR or AMOUNT_EUR when the column exists. A EUR_RATE of zero flags a missing exchange rate: the euro amount is wrong in that case, say so.\n7. If rowCount reaches the cap announced by the tool, say the result is truncated and offer a narrower filter instead of concluding.\n8. An empty result (rowCount 0) means no data, not an error. Check that the code exists through the reference tools before stating there is nothing.\n\n## Coded status fields\n\nTools return statuses in two forms: a _CODE column (the X3 local menu value) and a _LABEL column resolved to text in the format Label (code). Always display the _LABEL column, never the code on its own. _CODE columns are only there to build the filters of a follow-up call.\n\nOne exception: ORDER_TYPE on x3_sales_order_lines stays a raw code (SON, WEB). Return it as is, without inventing a meaning for it.\n\n## Business precautions\n\n- STOCK_AVAILABLE is not STOCK_TOTAL. Available removes allocations.\n- Billed and ordered are two distinct horizons, never a cumulative figure: billed is realised, ordered is a commitment not yet invoiced. Do not add them up without saying so.\n- Credit memos count as negative in billed revenue, proformas as zero.\n- Never invent a field, table, code or tool name. If a piece of data is not in the result, say so.\n- Write access: never call x3_sales_order_create without explicit confirmation. Recap customer, site, items and quantities, then wait for a clear go-ahead.\n\n## Answer\n\nConcise and structured. Key figures first, detail afterwards, currency and horizon always stated. Flag any data anomaly you notice along the way."
}
},
"typeVersion": 3.1
},
{
"id": "ade1dd3f-4c5d-4c95-8f20-18c0331bd821",
"name": "Language model (OpenRouter)",
"type": "@n8n/n8n-nodes-langchain.lmChatOpenRouter",
"position": [
224,
432
],
"parameters": {
"model": "anthropic/claude-sonnet-5",
"options": {
"temperature": 0.9
}
},
"credentials": {
"openRouterApi": {
"name": "<your credential>"
}
},
"typeVersion": 1
},
{
"id": "8472c724-98a0-42f1-a356-5a2572919987",
"name": "Conversation memory",
"type": "@n8n/n8n-nodes-langchain.memoryBufferWindow",
"position": [
432,
432
],
"parameters": {},
"typeVersion": 1.3
},
{
"id": "7bfa6d03-7d21-4adf-b678-ccdfe02697d5",
"name": "Expose the tools as an MCP server",
"type": "@n8n/n8n-nodes-langchain.mcpTrigger",
"position": [
752,
304
],
"parameters": {
"path": "sage-x3-mcp",
"authentication": "bearerAuth"
},
"credentials": {
"httpBearerAuth": {
"name": "<your credential>"
}
},
"typeVersion": 1
},
{
"id": "3c4d5af7-4cdc-4758-b158-b90085c96f8c",
"name": "x3_customer_search",
"type": "n8n-nodes-nx3.nx3SoapTool",
"position": [
-736,
1120
],
"parameters": {
"advanced": {},
"simplify": true,
"sqlQuery": "SELECT\n BPC.BPCNUM_0 AS CUSTOMER_CODE,\n BPC.BPCNAM_0 AS CUSTOMER_NAME,\n BPC.BCGCOD_0 AS CATEGORY,\n BPC.CUR_0 AS CURRENCY,\n BPC.BPCSTA_0 AS STATUS_CODE,\n BPC.OSTAUZ_0 AS CREDIT_LIMIT\nFROM BPCUSTOMER BPC\nWHERE 1 = 1\n[[AND BPC.BPCNUM_0 IN ({{customer_code}})]]\n[[AND UPPER(BPC.BPCNAM_0) LIKE UPPER({{name_contains}})]]\n[[AND BPC.BCGCOD_0 IN ({{category}})]]\n[[AND BPC.BPCSTA_0 IN ({{status}})]]\nORDER BY BPC.BPCNUM_0",
"operation": "sqlSelect",
"rowFormat": "arrays",
"sqlOptions": {
"maxTime": 20,
"maxLines": 200
},
"sqlParameters": {
"parameter": [
{
"name": "customer_code",
"type": "list",
"value": "={{ $fromAI('customer_code', `One or more Sage X3 customer codes (BPCNUM_0), comma separated, e.g. FR001 or FR001,FR002. No quotes. Leave empty not to filter on the customer.`, 'string', '') }}"
},
{
"name": "name_contains",
"type": "contains",
"value": "={{ $fromAI('name_contains', `Fragment of the customer name, case-insensitive partial search. Pass the raw fragment, e.g. smith. Do not add %, they are added automatically. Leave empty not to filter on the name.`, 'string', '') }}"
},
{
"name": "category",
"type": "list",
"value": "={{ $fromAI('category', `One or more Sage X3 customer category codes (BCGCOD_0), comma separated, e.g. FR or FR,PRO. No quotes. Leave empty for all categories.`, 'string', '') }}"
},
{
"name": "status",
"type": "list",
"value": "={{ $fromAI('status', `Customer status (BPCSTA_0), numeric code, e.g. 2. Leave empty for all statuses.`, 'string', '') }}"
}
]
},
"descriptionType": "manual",
"toolDescription": "Sage X3 customer reference data (BPCUSTOMER table). Returns: CUSTOMER_CODE (BPCNUM_0), CUSTOMER_NAME, CATEGORY (BCGCOD_0), CURRENCY, STATUS_CODE (local menu 1: 2 for active, 1 for inactive), CREDIT_LIMIT (in the customer's currency). Use it to resolve a customer name into a customer code before any call to the sales order tools, or to list the customers of a category. It holds no sales data: for revenue, use x3_revenue_summary.\n\nDo not add quotes or % around values: the filters add them themselves. Multi-value filters are separated by a comma.\n\nMaximum 200 rows. If rowCount is 200 the result is truncated: narrow it down with name_contains or category and tell the user rather than drawing a conclusion."
},
"credentials": {
"nx3SoapApi": {
"name": "<your credential>"
}
},
"typeVersion": 1
},
{
"id": "9b9d14f4-69ae-468c-aee8-79dd37be0f4c",
"name": "x3_item_search",
"type": "n8n-nodes-nx3.nx3SoapTool",
"position": [
-432,
1120
],
"parameters": {
"advanced": {},
"simplify": true,
"sqlQuery": "SELECT\n ITM.ITMREF_0 AS ITEM,\n ISNULL(NULLIF(DES1.LABEL, ''), ITM.ITMDES1_0) AS DESCRIPTION,\n ITM.SEAKEY_0 AS SEARCH_KEY,\n ITM.TCLCOD_0 AS CATEGORY_CODE,\n ISNULL(CAT.LABEL, ITM.TCLCOD_0) AS CATEGORY_LABEL,\n ITM.ITMSTA_0 AS ITEM_STATUS_CODE,\n ISNULL(ML_STA.LANMES_0, 'Unknown')\n + ' (' + CAST(ISNULL(ITM.ITMSTA_0, 0) AS VARCHAR(10)) + ')' AS ITEM_STATUS_LABEL,\n ITM.STU_0 AS STOCK_UNIT,\n ISNULL(STK.STOCK_TOTAL_A, 0) AS STOCK_TOTAL_ALL_SITES,\n ISNULL(STK.STOCK_AVAILABLE, 0) AS STOCK_AVAILABLE_ALL_SITES\n\nFROM ITMMASTER ITM\n\nLEFT JOIN (\n SELECT ITMREF_0,\n SUM(ISNULL(PHYSTO_0, 0)) AS STOCK_TOTAL_A,\n SUM(ISNULL(PHYSTO_0, 0) - ISNULL(PHYALL_0, 0) - ISNULL(GLOALL_0, 0)) AS STOCK_AVAILABLE\n FROM ITMMVT\n GROUP BY ITMREF_0\n) STK ON STK.ITMREF_0 = ITM.ITMREF_0\n\nLEFT JOIN (\n SELECT RTRIM(IDENT1_0) + RTRIM(IDENT2_0) AS TCLCOD, MAX(TEXTE_0) AS LABEL\n FROM ATEXTRA\n WHERE CODFIC_0 = 'ITMCATEG' AND ZONE_0 = 'TCLAXX' AND LANGUE_0 = 'ENG'\n GROUP BY RTRIM(IDENT1_0) + RTRIM(IDENT2_0)\n) CAT ON CAT.TCLCOD = RTRIM(ITM.TCLCOD_0)\n\nLEFT JOIN (\n SELECT RTRIM(IDENT1_0) AS ITMREF, MAX(TEXTE_0) AS LABEL\n FROM ATEXTRA\n WHERE CODFIC_0 = 'ITMMASTER' AND ZONE_0 = 'DES1AXX' AND LANGUE_0 = 'ENG'\n GROUP BY RTRIM(IDENT1_0)\n) DES1 ON DES1.ITMREF = RTRIM(ITM.ITMREF_0)\n\nLEFT JOIN APLSTD ML_STA ON ML_STA.LAN_0='ENG' AND ML_STA.LANCHP_0=246 AND ML_STA.LANNUM_0=ITM.ITMSTA_0\n\nWHERE 1 = 1\n[[AND ITM.ITMREF_0 IN ({{item}})]]\n[[AND (UPPER(ITM.ITMDES1_0) LIKE UPPER({{description}}) OR UPPER(ISNULL(DES1.LABEL, '')) LIKE UPPER({{description}}))]]\n[[AND UPPER(ITM.SEAKEY_0) LIKE UPPER({{search_key}})]]\n[[AND ITM.TCLCOD_0 IN ({{category}})]]\n[[AND ITM.ITMSTA_0 IN ({{item_status}})]]\n[[AND ITM.TSICOD_0 IN ({{stat_group1}})]]\n[[AND ITM.TSICOD_1 IN ({{stat_group2}})]]\n[[AND ITM.SALFLG_0 = {{sold}}]]\n[[AND ITM.PURFLG_0 = {{purchased}}]]\n[[AND ITM.MFGFLG_0 = {{manufactured}}]]\n[[AND ITM.STU_0 IN ({{stock_unit}})]]\n[[AND ITM.BUY_0 IN ({{buyer}})]]\n[[AND ITM.PLANNER_0 IN ({{planner}})]]\n[[AND {{with_stock}} = 1 AND ISNULL(STK.STOCK_TOTAL_A, 0) <> 0]]\nORDER BY ITM.ITMREF_0",
"operation": "sqlSelect",
"rowFormat": "arrays",
"sqlOptions": {
"maxTime": 30,
"maxLines": 500
},
"sqlParameters": {
"parameter": [
{
"name": "item",
"type": "list",
"value": "={{ $fromAI('item', `One or more exact item references (ITMREF_0), comma separated, e.g. FIN008 or FIN008,FIN006. No quotes. This is the most selective filter, prefer it. Leave empty not to filter on the reference.`, 'string', '') }}"
},
{
"name": "description",
"type": "contains",
"value": "={{ $fromAI('description', `Fragment of the main description (ITMDES1_0), case-insensitive partial search. Pass the raw fragment, e.g. mountain. Do not add %, they are added automatically. Leave empty not to filter on the description.`, 'string', '') }}"
},
{
"name": "search_key",
"type": "contains",
"value": "={{ $fromAI('search_key', `Fragment of the item search key (SEAKEY_0), case-insensitive partial search, e.g. STRE. Do not add %. Leave empty not to filter.`, 'string', '') }}"
},
{
"name": "category",
"type": "list",
"value": "={{ $fromAI('category', `One or more item categories (TCLCOD_0), comma separated, e.g. FINIS, SFINI, BMSOL, RAWMA. Leave empty for all categories.`, 'string', '') }}"
},
{
"name": "item_status",
"type": "list",
"value": "={{ $fromAI('item_status', `One or more item statuses (ITMSTA_0), numeric code of local menu 246, e.g. 1 for active. Leave empty for all statuses.`, 'string', '') }}"
},
{
"name": "stat_group1",
"type": "list",
"value": "={{ $fromAI('stat_group1', `One or more statistical group 1 codes (TSICOD_0), e.g. 20 for Cycle. Leave empty for all.`, 'string', '') }}"
},
{
"name": "stat_group2",
"type": "list",
"value": "={{ $fromAI('stat_group2', `One or more statistical group 2 codes (TSICOD_1), e.g. 21 for Bikes or 23 for Spare parts. Leave empty for all.`, 'string', '') }}"
},
{
"name": "sold",
"type": "number",
"value": "={{ $fromAI('sold', `Sold item flag (SALFLG_0): 2 for Yes, 1 for No. Leave empty not to filter.`, 'string', '') }}"
},
{
"name": "purchased",
"type": "number",
"value": "={{ $fromAI('purchased', `Purchased item flag (PURFLG_0): 2 for Yes, 1 for No. Leave empty not to filter.`, 'string', '') }}"
},
{
"name": "manufactured",
"type": "number",
"value": "={{ $fromAI('manufactured', `Manufactured item flag (MFGFLG_0): 2 for Yes, 1 for No. Leave empty not to filter.`, 'string', '') }}"
},
{
"name": "stock_unit",
"type": "list",
"value": "={{ $fromAI('stock_unit', `One or more stock units (STU_0), e.g. UN, KG, MT. Leave empty for all units.`, 'string', '') }}"
},
{
"name": "buyer",
"type": "list",
"value": "={{ $fromAI('buyer', `One or more buyer codes (BUY_0). Leave empty for all buyers.`, 'string', '') }}"
},
{
"name": "planner",
"type": "list",
"value": "={{ $fromAI('planner', `One or more planner codes (PLANNER_0). Leave empty for all planners.`, 'string', '') }}"
},
{
"name": "with_stock",
"type": "number",
"value": "={{ $fromAI('with_stock', `Set 1 to return only the items that hold stock across all sites. Leave empty not to filter on stock.`, 'string', '') }}"
}
]
},
"descriptionType": "manual",
"toolDescription": "Item search in the Sage X3 reference data (ITMMASTER). This is the tool to use to resolve a description into a reference (ITMREF_0), or to list the items of a category, a statistical group, a buyer or a planner.\n\nDeliberately short answer, 10 columns: ITEM, DESCRIPTION (resolved through ATEXTRA, falling back to the language of the item record), SEARCH_KEY, CATEGORY_CODE and CATEGORY_LABEL, ITEM_STATUS_CODE and ITEM_STATUS_LABEL, STOCK_UNIT, STOCK_TOTAL_ALL_SITES, STOCK_AVAILABLE_ALL_SITES.\n\nThe stock shown is an all-site total, good for an order of magnitude only: for stock by site use x3_stock_available. For the full setup of an item, use x3_item_detail.\n\nDo not add quotes or % around values. Write FIN008 and not 'FIN008', write Adult and not %Adult%. Maximum 500 rows: always pass at least one filter."
},
"credentials": {
"nx3SoapApi": {
"name": "<your credential>"
}
},
"typeVersion": 1
},
{
"id": "6319bdca-17d8-4bc9-993a-538fa66e300a",
"name": "x3_customer_product",
"type": "n8n-nodes-nx3.nx3SoapTool",
"position": [
-736,
1296
],
"parameters": {
"advanced": {},
"simplify": true,
"sqlQuery": "SELECT\n ITU.BPCNUM_0 AS CUSTOMER_CODE,\n BPC.BPCNAM_0 AS CUSTOMER_NAME,\n BPC.BCGCOD_0 AS CUSTOMER_CATEGORY,\n\n /* --- Customer status: local menu 1 --- */\n BPC.BPCSTA_0 AS CUSTOMER_STATUS_CODE,\n ISNULL(ML_BPC.LANMES_0, 'Unknown')\n + ' (' + CAST(ISNULL(BPC.BPCSTA_0, 0) AS VARCHAR(10)) + ')'\n AS CUSTOMER_STATUS_LABEL,\n\n ITU.ITMREF_0 AS ITEM,\n ISNULL(NULLIF(DES1.LABEL, ''), ITM.ITMDES1_0) AS X3_DESCRIPTION,\n ITU.ITMREFBPC_0 AS CUSTOMER_REF,\n ITU.ITMDESBPC_0 AS CUSTOMER_DESCRIPTION,\n\n /* --- Cross-reference active: local menu 1 --- */\n ITU.ENAFLG_0 AS ACTIVE_CODE,\n ISNULL(ML_ENA.LANMES_0, 'Unknown')\n + ' (' + CAST(ISNULL(ITU.ENAFLG_0, 0) AS VARCHAR(10)) + ')'\n AS ACTIVE_LABEL,\n\n /* --- Item status: local menu 246 --- */\n ITM.ITMSTA_0 AS ITEM_STATUS_CODE,\n ISNULL(ML_ITM.LANMES_0, 'Unknown')\n + ' (' + CAST(ISNULL(ITM.ITMSTA_0, 0) AS VARCHAR(10)) + ')'\n AS ITEM_STATUS_LABEL,\n\n ITM.TCLCOD_0 AS ITEM_CATEGORY_CODE,\n ISNULL(CAT.LABEL, ITM.TCLCOD_0) AS ITEM_CATEGORY_LABEL,\n\n /* --- Units and packing specific to the item x customer pair --- */\n ITU.SAU_0 AS CUSTOMER_SALES_UNIT,\n ITU.SAUSTUCOE_0 AS SAL_STK_COEF,\n ITM.STU_0 AS STOCK_UNIT,\n ITU.PCK_0 AS PACKING,\n ITU.PCKCAP_0 AS PACKING_CAPACITY,\n ITU.PCU1_0 AS PACK_UNIT_1,\n ITU.PCUSAUCOE1_0 AS PACK_COEF_1,\n ITU.PCU2_0 AS PACK_UNIT_2,\n ITU.PCUSAUCOE2_0 AS PACK_COEF_2,\n ITU.DLUBPC_0 AS CUSTOMER_SHELF_LIFE_COEF,\n\n /* --- All-site available stock for the item, handy to answer \"can I ship it?\" straight away --- */\n ISNULL(STK.STOCK_AVAILABLE, 0) AS STOCK_AVAILABLE_ALL_SITES,\n\n NULLIF(ITU.CREDAT_0, '1753-01-01') AS CREATED_ON,\n NULLIF(ITU.UPDDAT_0, '1753-01-01') AS UPDATED_ON,\n ITU.UPDUSR_0 AS UPDATED_BY\n\nFROM ITMBPC ITU\n\nLEFT JOIN BPCUSTOMER BPC\n ON BPC.BPCNUM_0 = ITU.BPCNUM_0\n\nLEFT JOIN ITMMASTER ITM\n ON ITM.ITMREF_0 = ITU.ITMREF_0\nLEFT JOIN (\n SELECT RTRIM(IDENT1_0) AS ITMREF, MAX(TEXTE_0) AS LABEL\n FROM ATEXTRA\n WHERE CODFIC_0 = 'ITMMASTER' AND ZONE_0 = 'DES1AXX' AND LANGUE_0 = 'ENG'\n GROUP BY RTRIM(IDENT1_0)\n) DES1 ON DES1.ITMREF = RTRIM(ITM.ITMREF_0)\n\nLEFT JOIN (\n SELECT ITMREF_0,\n SUM(ISNULL(PHYSTO_0, 0) - ISNULL(PHYALL_0, 0) - ISNULL(GLOALL_0, 0))\n AS STOCK_AVAILABLE\n FROM ITMMVT\n GROUP BY ITMREF_0\n) STK ON STK.ITMREF_0 = ITU.ITMREF_0\n\n/* --- Item category label: ATEXTRA / ITMCATEG / TCLAXX / ENG --- */\nLEFT JOIN (\n SELECT RTRIM(IDENT1_0) + RTRIM(IDENT2_0) AS TCLCOD,\n MAX(TEXTE_0) AS LABEL\n FROM ATEXTRA\n WHERE CODFIC_0 = 'ITMCATEG'\n AND ZONE_0 = 'TCLAXX'\n AND LANGUE_0 = 'ENG'\n GROUP BY RTRIM(IDENT1_0) + RTRIM(IDENT2_0)\n) CAT ON CAT.TCLCOD = RTRIM(ITM.TCLCOD_0)\n\nLEFT JOIN APLSTD ML_ENA\n ON ML_ENA.LAN_0 = 'ENG'\n AND ML_ENA.LANCHP_0 = 1\n AND ML_ENA.LANNUM_0 = ITU.ENAFLG_0\n\nLEFT JOIN APLSTD ML_BPC\n ON ML_BPC.LAN_0 = 'ENG'\n AND ML_BPC.LANCHP_0 = 1\n AND ML_BPC.LANNUM_0 = BPC.BPCSTA_0\n\nLEFT JOIN APLSTD ML_ITM\n ON ML_ITM.LAN_0 = 'ENG'\n AND ML_ITM.LANCHP_0 = 246\n AND ML_ITM.LANNUM_0 = ITM.ITMSTA_0\n\nWHERE 1 = 1\n[[AND ITU.ITMREF_0 IN ({{item}})]]\n[[AND ITU.BPCNUM_0 IN ({{customer_code}})]]\n[[AND UPPER(BPC.BPCNAM_0) LIKE UPPER({{customer_name}})]]\n[[AND ITU.ITMREFBPC_0 IN ({{customer_ref}})]]\n[[AND UPPER(ITU.ITMREFBPC_0) LIKE UPPER({{customer_ref_contains}})]]\n[[AND UPPER(ITU.ITMDESBPC_0) LIKE UPPER({{customer_description}})]]\n[[AND (UPPER(ITM.ITMDES1_0) LIKE UPPER({{description}}) OR UPPER(ISNULL(DES1.LABEL, '')) LIKE UPPER({{description}}))]]\n[[AND ITM.TCLCOD_0 IN ({{category}})]]\n[[AND ITU.ENAFLG_0 = {{active}}]]\n[[AND ITM.ITMSTA_0 IN ({{item_status}})]]\n\nORDER BY ITU.BPCNUM_0, ITU.ITMREF_0",
"operation": "sqlSelect",
"rowFormat": "arrays",
"sqlOptions": {
"maxTime": 30,
"maxLines": 300
},
"sqlParameters": {
"parameter": [
{
"name": "item",
"type": "list",
"value": "={{ $fromAI('item', `One or more Sage X3 item references (ITMREF_0), comma separated, e.g. FIN008 or FIN008,FIN006. No quotes. Leave empty for all items.`, 'string', '') }}"
},
{
"name": "customer_code",
"type": "list",
"value": "={{ $fromAI('customer_code', `One or more Sage X3 customer codes (BPCNUM_0), comma separated, e.g. FR001 or FR001,FR002. No quotes. Leave empty for all customers.`, 'string', '') }}"
},
{
"name": "customer_name",
"type": "contains",
"value": "={{ $fromAI('customer_name', `Fragment of the customer name, case-insensitive partial search. Pass the raw fragment, e.g. urban. Do not add %, they are added automatically. Leave empty not to filter on the name.`, 'string', '') }}"
},
{
"name": "customer_ref",
"type": "list",
"value": "={{ $fromAI('customer_ref', `One or more of the CUSTOMER's item references (ITMREFBPC_0), exact value, comma separated. Leave empty not to filter.`, 'string', '') }}"
},
{
"name": "customer_ref_contains",
"type": "contains",
"value": "={{ $fromAI('customer_ref_contains', `Fragment of the customer's item reference, partial search. Do not add %. Leave empty not to filter.`, 'string', '') }}"
},
{
"name": "customer_description",
"type": "contains",
"value": "={{ $fromAI('customer_description', `Fragment of the item description as used by the customer (ITMDESBPC_0). Do not add %. Leave empty not to filter.`, 'string', '') }}"
},
{
"name": "description",
"type": "contains",
"value": "={{ $fromAI('description', `Fragment of the item description, case-insensitive partial search. Pass the raw fragment, e.g. mountain. Do not add %, they are added automatically. Leave empty not to filter.`, 'string', '') }}"
},
{
"name": "category",
"type": "list",
"value": "={{ $fromAI('category', `One or more item categories (TCLCOD_0), comma separated, e.g. FINIS, SFINI, BMSOL, RAWMA. Pass the code, not the label. Leave empty for all categories.`, 'string', '') }}"
},
{
"name": "active",
"type": "number",
"value": "={{ $fromAI('active', `Cross-reference ENAFLG_0 flag: 2 for Yes, 1 for No. Careful, many folders leave this field at 0 (not filled in): filtering on 2 then returns nothing. Leave empty by default.`, 'string', '') }}"
},
{
"name": "item_status",
"type": "list",
"value": "={{ $fromAI('item_status', `One or more item statuses (ITMSTA_0), numeric code of local menu 246. Leave empty for all statuses.`, 'string', '') }}"
}
]
},
"descriptionType": "manual",
"toolDescription": "Mapping between the Sage X3 item reference and the customer's own reference (ITMBPC table). Call it when a customer quotes a reference that does not exist in the item reference data, or to list what is referenced for a customer.\n\nReturns CUSTOMER_CODE, CUSTOMER_NAME, CUSTOMER_CATEGORY, CUSTOMER_STATUS_LABEL, ITEM, X3_DESCRIPTION, CUSTOMER_REF, CUSTOMER_DESCRIPTION, ACTIVE_LABEL (Yes / No, or empty when the field is not filled in on the folder), ITEM_STATUS_LABEL, ITEM_CATEGORY_LABEL, the units and packing specific to the item x customer pair (CUSTOMER_SALES_UNIT, SAL_STK_COEF, STOCK_UNIT, PACKING, PACKING_CAPACITY, PACK_UNIT_1 and 2, PACK_COEF_1 and 2, CUSTOMER_SHELF_LIFE_COEF), STOCK_AVAILABLE_ALL_SITES, CREATED_ON, UPDATED_ON, UPDATED_BY.\n\nAlways quote the _LABEL columns. Do not add quotes or % around values. Maximum 300 rows."
},
"credentials": {
"nx3SoapApi": {
"name": "<your credential>"
}
},
"typeVersion": 1
},
{
"id": "e9dcac89-ef19-4f94-8df4-f1ec6d505f01",
"name": "x3_item_detail",
"type": "n8n-nodes-nx3.nx3SoapTool",
"position": [
-432,
1296
],
"parameters": {
"advanced": {},
"simplify": true,
"sqlQuery": "SELECT\n ITM.ITMREF_0 AS ITEM,\n ISNULL(NULLIF(DES1.LABEL, ''), ITM.ITMDES1_0) AS DESCRIPTION,\n ISNULL(NULLIF(DES2.LABEL, ''), ITM.ITMDES2_0) AS DESCRIPTION_2,\n ISNULL(NULLIF(DES3.LABEL, ''), ITM.ITMDES3_0) AS DESCRIPTION_3,\n ITM.SEAKEY_0 AS SEARCH_KEY,\n ITM.TCLCOD_0 AS CATEGORY_CODE,\n ISNULL(CAT.LABEL, ITM.TCLCOD_0) AS CATEGORY_LABEL,\n\n /* --- Item status: local menu 246 --- */\n ITM.ITMSTA_0 AS ITEM_STATUS_CODE,\n ISNULL(ML_STA.LANMES_0, 'Unknown')\n + ' (' + CAST(ISNULL(ITM.ITMSTA_0, 0) AS VARCHAR(10)) + ')'\n AS ITEM_STATUS_LABEL,\n\n /* --- Management mode: local menu 297 --- */\n ITM.STDFLG_0 AS MANAGEMENT_MODE_CODE,\n ISNULL(ML_STD.LANMES_0, 'Unknown')\n + ' (' + CAST(ISNULL(ITM.STDFLG_0, 0) AS VARCHAR(10)) + ')'\n AS MANAGEMENT_MODE_LABEL,\n\n /* --- Stock management: local menu 215 --- */\n ITM.STOMGTCOD_0 AS STOCK_MANAGEMENT_CODE,\n ISNULL(ML_SMG.LANMES_0, 'Unknown')\n + ' (' + CAST(ISNULL(ITM.STOMGTCOD_0, 0) AS VARCHAR(10)) + ')'\n AS STOCK_MANAGEMENT_LABEL,\n\n /* --- Lot management: local menu 2711 --- */\n ITM.LOTMGTCOD_0 AS LOT_MANAGEMENT_CODE,\n ISNULL(ML_LOT.LANMES_0, 'Unknown')\n + ' (' + CAST(ISNULL(ITM.LOTMGTCOD_0, 0) AS VARCHAR(10)) + ')'\n AS LOT_MANAGEMENT_LABEL,\n\n /* --- Serial number management: local menu 210 --- */\n ITM.SERMGTCOD_0 AS SERIAL_MANAGEMENT_CODE,\n ISNULL(ML_SER.LANMES_0, 'Unknown')\n + ' (' + CAST(ISNULL(ITM.SERMGTCOD_0, 0) AS VARCHAR(10)) + ')'\n AS SERIAL_MANAGEMENT_LABEL,\n\n /* --- Traceability: local menu 754 --- */\n ITM.TRKCOD_0 AS TRACEABILITY_CODE,\n ISNULL(ML_TRK.LANMES_0, 'Unknown')\n + ' (' + CAST(ISNULL(ITM.TRKCOD_0, 0) AS VARCHAR(10)) + ')'\n AS TRACEABILITY_LABEL,\n\n /* --- Expiry management: local menu 211 --- */\n ITM.EXYMGTCOD_0 AS EXPIRY_MANAGEMENT_CODE,\n ISNULL(ML_EXY.LANMES_0, 'Unknown')\n + ' (' + CAST(ISNULL(ITM.EXYMGTCOD_0, 0) AS VARCHAR(10)) + ')'\n AS EXPIRY_MANAGEMENT_LABEL,\n ITM.SHL_0 AS SHELF_LIFE,\n\n /* --- Yes / No flags: local menu 1 --- */\n ITM.PURFLG_0 AS PURCHASED_CODE,\n ISNULL(ML_PUR.LANMES_0, 'Unknown')\n + ' (' + CAST(ISNULL(ITM.PURFLG_0, 0) AS VARCHAR(10)) + ')'\n AS PURCHASED_LABEL,\n ITM.SALFLG_0 AS SOLD_CODE,\n ISNULL(ML_SAL.LANMES_0, 'Unknown')\n + ' (' + CAST(ISNULL(ITM.SALFLG_0, 0) AS VARCHAR(10)) + ')'\n AS SOLD_LABEL,\n ITM.MFGFLG_0 AS MANUFACTURED_CODE,\n ISNULL(ML_MFG.LANMES_0, 'Unknown')\n + ' (' + CAST(ISNULL(ITM.MFGFLG_0, 0) AS VARCHAR(10)) + ')'\n AS MANUFACTURED_LABEL,\n ITM.DLVFLG_0 AS DELIVERABLE_CODE,\n ISNULL(ML_DLV.LANMES_0, 'Unknown')\n + ' (' + CAST(ISNULL(ITM.DLVFLG_0, 0) AS VARCHAR(10)) + ')'\n AS DELIVERABLE_LABEL,\n ITM.NEGSTO_0 AS NEGATIVE_STOCK_CODE,\n ISNULL(ML_NEG.LANMES_0, 'Unknown')\n + ' (' + CAST(ISNULL(ITM.NEGSTO_0, 0) AS VARCHAR(10)) + ')'\n AS NEGATIVE_STOCK_LABEL,\n\n /* --- Statistical groups: miscellaneous tables ATABDIV 20 / 21 / 22 --- */\n ITM.TSICOD_0 AS STAT_GROUP1_CODE,\n ISNULL(AD_TS0.TEXTE_0, 'Not set') AS STAT_GROUP1_LABEL,\n ITM.TSICOD_1 AS STAT_GROUP2_CODE,\n ISNULL(AD_TS1.TEXTE_0, 'Not set') AS STAT_GROUP2_LABEL,\n ITM.TSICOD_2 AS STAT_GROUP3_CODE,\n ISNULL(AD_TS2.TEXTE_0, 'Not set') AS STAT_GROUP3_LABEL,\n\n /* --- Units and conversions --- */\n ITM.STU_0 AS STOCK_UNIT,\n ITM.SAU_0 AS SALES_UNIT,\n ITM.SAUSTUCOE_0 AS SAL_STK_COEF,\n ITM.PUU_0 AS PURCHASE_UNIT,\n ITM.PUUSTUCOE_0 AS PUR_STK_COEF,\n ITM.WEU_0 AS WEIGHT_UNIT,\n ITM.ITMWEI_0 AS UNIT_WEIGHT,\n ITM.VOU_0 AS VOLUME_UNIT,\n ITM.ITMVOU_0 AS UNIT_VOLUME,\n\n /* --- External identifiers and logistics --- */\n ITM.EANCOD_0 AS BARCODE,\n ITM.GTIN_0 AS GTIN,\n ITM.CUSREF_0 AS CUSTOMS_CODE,\n ITM.ACCCOD_0 AS ACCOUNTING_CODE,\n ITM.PURBASPRI_0 AS PURCHASE_BASE_PRICE,\n ITM.BUY_0 AS BUYER,\n ITM.PLANNER_0 AS PLANNER,\n ITM.OFS_0 AS REPLENISHMENT_LEAD_TIME,\n\n /* --- All-site stock total (0 when the item is not stock managed) --- */\n ISNULL(STK.STOCK_TOTAL_A, 0) AS STOCK_TOTAL_A_ALL_SITES,\n ISNULL(STK.STOCK_AVAILABLE, 0) AS STOCK_AVAILABLE_ALL_SITES,\n ISNULL(STK.SITE_COUNT, 0) AS SITES_WITH_STOCK,\n\n NULLIF(ITM.CREDAT_0, '1753-01-01') AS CREATED_ON,\n NULLIF(ITM.UPDDAT_0, '1753-01-01') AS UPDATED_ON,\n ITM.UPDUSR_0 AS UPDATED_BY\n\nFROM ITMMASTER ITM\nLEFT JOIN (\n SELECT RTRIM(IDENT1_0) AS ITMREF, MAX(TEXTE_0) AS LABEL\n FROM ATEXTRA\n WHERE CODFIC_0 = 'ITMMASTER' AND ZONE_0 = 'DES1AXX' AND LANGUE_0 = 'ENG'\n GROUP BY RTRIM(IDENT1_0)\n) DES1 ON DES1.ITMREF = RTRIM(ITM.ITMREF_0)\nLEFT JOIN (\n SELECT RTRIM(IDENT1_0) AS ITMREF, MAX(TEXTE_0) AS LABEL\n FROM ATEXTRA\n WHERE CODFIC_0 = 'ITMMASTER' AND ZONE_0 = 'DES2AXX' AND LANGUE_0 = 'ENG'\n GROUP BY RTRIM(IDENT1_0)\n) DES2 ON DES2.ITMREF = RTRIM(ITM.ITMREF_0)\nLEFT JOIN (\n SELECT RTRIM(IDENT1_0) AS ITMREF, MAX(TEXTE_0) AS LABEL\n FROM ATEXTRA\n WHERE CODFIC_0 = 'ITMMASTER' AND ZONE_0 = 'DES3AXX' AND LANGUE_0 = 'ENG'\n GROUP BY RTRIM(IDENT1_0)\n) DES3 ON DES3.ITMREF = RTRIM(ITM.ITMREF_0)\n\nLEFT JOIN (\n SELECT ITMREF_0,\n SUM(ISNULL(PHYSTO_0, 0)) AS STOCK_TOTAL_A,\n SUM(ISNULL(PHYSTO_0, 0) - ISNULL(PHYALL_0, 0) - ISNULL(GLOALL_0, 0))\n AS STOCK_AVAILABLE,\n COUNT(*) AS SITE_COUNT\n FROM ITMMVT\n GROUP BY ITMREF_0\n) STK ON STK.ITMREF_0 = ITM.ITMREF_0\n\n/* --- Item category label: ATEXTRA / ITMCATEG / TCLAXX / ENG. Depending on the folder the code\n sits either in IDENT1_0 or in IDENT2_0 (key ITG0 = \"\"+TCLCOD), hence the concatenation. The\n derived table is tiny, the cost is negligible. --- */\nLEFT JOIN (\n SELECT RTRIM(IDENT1_0) + RTRIM(IDENT2_0) AS TCLCOD,\n MAX(TEXTE_0) AS LABEL\n FROM ATEXTRA\n WHERE CODFIC_0 = 'ITMCATEG'\n AND ZONE_0 = 'TCLAXX'\n AND LANGUE_0 = 'ENG'\n GROUP BY RTRIM(IDENT1_0) + RTRIM(IDENT2_0)\n) CAT ON CAT.TCLCOD = RTRIM(ITM.TCLCOD_0)\n\nLEFT JOIN APLSTD ML_STA ON ML_STA.LAN_0='ENG' AND ML_STA.LANCHP_0=246 AND ML_STA.LANNUM_0=ITM.ITMSTA_0\nLEFT JOIN APLSTD ML_STD ON ML_STD.LAN_0='ENG' AND ML_STD.LANCHP_0=297 AND ML_STD.LANNUM_0=ITM.STDFLG_0\nLEFT JOIN APLSTD ML_SMG ON ML_SMG.LAN_0='ENG' AND ML_SMG.LANCHP_0=215 AND ML_SMG.LANNUM_0=ITM.STOMGTCOD_0\nLEFT JOIN APLSTD ML_LOT ON ML_LOT.LAN_0='ENG' AND ML_LOT.LANCHP_0=2711 AND ML_LOT.LANNUM_0=ITM.LOTMGTCOD_0\nLEFT JOIN APLSTD ML_SER ON ML_SER.LAN_0='ENG' AND ML_SER.LANCHP_0=210 AND ML_SER.LANNUM_0=ITM.SERMGTCOD_0\nLEFT JOIN APLSTD ML_TRK ON ML_TRK.LAN_0='ENG' AND ML_TRK.LANCHP_0=754 AND ML_TRK.LANNUM_0=ITM.TRKCOD_0\nLEFT JOIN APLSTD ML_EXY ON ML_EXY.LAN_0='ENG' AND ML_EXY.LANCHP_0=211 AND ML_EXY.LANNUM_0=ITM.EXYMGTCOD_0\nLEFT JOIN APLSTD ML_PUR ON ML_PUR.LAN_0='ENG' AND ML_PUR.LANCHP_0=1 AND ML_PUR.LANNUM_0=ITM.PURFLG_0\nLEFT JOIN APLSTD ML_SAL ON ML_SAL.LAN_0='ENG' AND ML_SAL.LANCHP_0=1 AND ML_SAL.LANNUM_0=ITM.SALFLG_0\nLEFT JOIN APLSTD ML_MFG ON ML_MFG.LAN_0='ENG' AND ML_MFG.LANCHP_0=1 AND ML_MFG.LANNUM_0=ITM.MFGFLG_0\nLEFT JOIN APLSTD ML_DLV ON ML_DLV.LAN_0='ENG' AND ML_DLV.LANCHP_0=1 AND ML_DLV.LANNUM_0=ITM.DLVFLG_0\nLEFT JOIN APLSTD ML_NEG ON ML_NEG.LAN_0='ENG' AND ML_NEG.LANCHP_0=1 AND ML_NEG.LANNUM_0=ITM.NEGSTO_0\n\nLEFT JOIN ATEXTRA AD_TS0\n ON AD_TS0.CODFIC_0='ATABDIV' AND AD_TS0.ZONE_0='LNGDES'\n AND AD_TS0.LANGUE_0='ENG' AND AD_TS0.IDENT1_0='20'\n AND AD_TS0.IDENT2_0=ITM.TSICOD_0\nLEFT JOIN ATEXTRA AD_TS1\n ON AD_TS1.CODFIC_0='ATABDIV' AND AD_TS1.ZONE_0='LNGDES'\n AND AD_TS1.LANGUE_0='ENG' AND AD_TS1.IDENT1_0='21'\n AND AD_TS1.IDENT2_0=ITM.TSICOD_1\nLEFT JOIN ATEXTRA AD_TS2\n ON AD_TS2.CODFIC_0='ATABDIV' AND AD_TS2.ZONE_0='LNGDES'\n AND AD_TS2.LANGUE_0='ENG' AND AD_TS2.IDENT1_0='22'\n AND AD_TS2.IDENT2_0=ITM.TSICOD_2\n\nWHERE 1 = 1\n[[AND ITM.ITMREF_0 IN ({{item}})]]\n[[AND (UPPER(ITM.ITMDES1_0) LIKE UPPER({{description}}) OR UPPER(ISNULL(DES1.LABEL, '')) LIKE UPPER({{description}}))]]\nORDER BY ITM.ITMREF_0\n",
"operation": "sqlSelect",
"rowFormat": "arrays",
"sqlOptions": {
"maxTime": 30,
"maxLines": 50
},
"sqlParameters": {
"parameter": [
{
"name": "item",
"type": "list",
"value": "={{ $fromAI('item', `One or more exact item references (ITMREF_0), comma separated, e.g. FIN008 or FIN008,FIN006. No quotes. This is the most selective filter, prefer it. Leave empty not to filter on the reference.`, 'string', '') }}"
},
{
"name": "description",
"type": "contains",
"value": "={{ $fromAI('description', `Fragment of the main description (ITMDES1_0), case-insensitive partial search. Pass the raw fragment, e.g. mountain. Do not add %, they are added automatically. Leave empty not to filter on the description.`, 'string', '') }}"
}
]
},
"descriptionType": "manual",
"toolDescription": "Full Sage X3 item record (ITMMASTER): around sixty setup columns for one or a few specific items. Call it only when the question is about the setup of an identified item (stock management, lot, traceability, expiry, units and coefficients, barcode, GTIN, customs code, buyer, planner, replenishment lead time).\n\nTo simply find a reference from a description, use x3_item_search, which is far lighter. Never call this tool to browse a set of items.\n\nAlways display the _LABEL columns, never the codes on their own. Maximum 50 rows."
},
"credentials": {
"nx3SoapApi": {
"name": "<your credential>"
}
},
"typeVersion": 1
},
{
"id": "cad885ed-70c4-4f7e-bc52-daec7b52cbb2",
"name": "x3_stock_available",
"type": "n8n-nodes-nx3.nx3SoapTool",
"position": [
-48,
1120
],
"parameters": {
"advanced": {},
"simplify": true,
"sqlQuery": "SELECT\n ITV.STOFCY_0 AS SITE,\n ITV.ITMREF_0 AS ITEM,\n ISNULL(NULLIF(DES1.LABEL, ''), ITM.ITMDES1_0) AS DESCRIPTION,\n ITM.STU_0 AS STOCK_UNIT,\n ITM.TCLCOD_0 AS CATEGORY_CODE,\n ISNULL(CAT.LABEL, ITM.TCLCOD_0) AS CATEGORY_LABEL,\n\n /* --- Item status: local menu 246 --- */\n ITM.ITMSTA_0 AS ITEM_STATUS_CODE,\n ISNULL(ML_STA.LANMES_0, 'Unknown')\n + ' (' + CAST(ISNULL(ITM.ITMSTA_0, 0) AS VARCHAR(10)) + ')'\n AS ITEM_STATUS_LABEL,\n\n /* --- Stock management: local menu 215 --- */\n ITM.STOMGTCOD_0 AS STOCK_MANAGEMENT_CODE,\n ISNULL(ML_SMG.LANMES_0, 'Unknown')\n + ' (' + CAST(ISNULL(ITM.STOMGTCOD_0, 0) AS VARCHAR(10)) + ')'\n AS STOCK_MANAGEMENT_LABEL,\n\n /* --- Quantities by stock status --- */\n ISNULL(ITV.PHYSTO_0, 0) AS STOCK_ACCEPTED_A,\n ISNULL(ITV.CTLSTO_0, 0) AS STOCK_QUALITY_Q,\n ISNULL(ITV.REJSTO_0, 0) AS STOCK_REJECTED_R,\n ISNULL(ITV.PHYSTO_0, 0) + ISNULL(ITV.CTLSTO_0, 0) + ISNULL(ITV.REJSTO_0, 0)\n AS STOCK_TOTAL,\n\n /* --- Commitments --- */\n ISNULL(ITV.PHYALL_0, 0) AS ALLOCATED_DETAILED,\n ISNULL(ITV.GLOALL_0, 0) AS ALLOCATED_GLOBAL,\n ISNULL(ITV.GLOSHT_0, 0) AS SHORTAGE_GLOBAL,\n ISNULL(ITV.SALSTO_0, 0) AS EXPECTED_SALES_ORDERS,\n ISNULL(ITV.ORDSTO_0, 0) AS EXPECTED_REPLENISHMENT,\n ISNULL(ITV.TRFSTO_0, 0) AS IN_TRANSIT,\n ISNULL(ITV.WAISTO_0, 0) AS PENDING_ISSUES,\n\n /* --- The figure to quote to the user --- */\n ISNULL(ITV.PHYSTO_0, 0) - ISNULL(ITV.PHYALL_0, 0) - ISNULL(ITV.GLOALL_0, 0)\n AS STOCK_AVAILABLE,\n\n /* --- Replenishment parameters (item x site) --- */\n ISNULL(ITF.REOTSD_0, 0) AS REORDER_POINT,\n ISNULL(ITF.SAFSTO_0, 0) AS SAFETY_STOCK,\n ISNULL(ITF.MAXSTO_0, 0) AS STOCK_MAX,\n CASE\n WHEN ISNULL(ITF.REOTSD_0, 0) > 0\n AND (ISNULL(ITV.PHYSTO_0, 0) - ISNULL(ITV.PHYALL_0, 0)\n - ISNULL(ITV.GLOALL_0, 0)) < ITF.REOTSD_0\n THEN 'Yes'\n ELSE 'No'\n END AS BELOW_REORDER_POINT,\n\n ITV.AVC_0 AS AVERAGE_COST,\n ISNULL(ITV.PHYSTO_0, 0) * ISNULL(ITV.AVC_0, 0)\n AS STOCK_VALUE_A,\n NULLIF(ITV.LASRCPDAT_0, '1753-01-01') AS LAST_RECEIPT,\n NULLIF(ITV.LASISSDAT_0, '1753-01-01') AS LAST_ISSUE,\n NULLIF(ITV.LASCUNDAT_0, '1753-01-01') AS LAST_COUNT\n\nFROM ITMMVT ITV\n\nLEFT JOIN ITMMASTER ITM\n ON ITM.ITMREF_0 = ITV.ITMREF_0\nLEFT JOIN (\n SELECT RTRIM(IDENT1_0) AS ITMREF, MAX(TEXTE_0) AS LABEL\n FROM ATEXTRA\n WHERE CODFIC_0 = 'ITMMASTER' AND ZONE_0 = 'DES1AXX' AND LANGUE_0 = 'ENG'\n GROUP BY RTRIM(IDENT1_0)\n) DES1 ON DES1.ITMREF = RTRIM(ITM.ITMREF_0)\n\nLEFT JOIN ITMFACILIT ITF\n ON ITF.ITMREF_0 = ITV.ITMREF_0\n AND ITF.STOFCY_0 = ITV.STOFCY_0\n\n/* --- Item category label: ATEXTRA / ITMCATEG / TCLAXX / ENG --- */\nLEFT JOIN (\n SELECT RTRIM(IDENT1_0) + RTRIM(IDENT2_0) AS TCLCOD,\n MAX(TEXTE_0) AS LABEL\n FROM ATEXTRA\n WHERE CODFIC_0 = 'ITMCATEG'\n AND ZONE_0 = 'TCLAXX'\n AND LANGUE_0 = 'ENG'\n GROUP BY RTRIM(IDENT1_0) + RTRIM(IDENT2_0)\n) CAT ON CAT.TCLCOD = RTRIM(ITM.TCLCOD_0)\n\nLEFT JOIN APLSTD ML_STA\n ON ML_STA.LAN_0 = 'ENG'\n AND ML_STA.LANCHP_0 = 246\n AND ML_STA.LANNUM_0 = ITM.ITMSTA_0\n\nLEFT JOIN APLSTD ML_SMG\n ON ML_SMG.LAN_0 = 'ENG'\n AND ML_SMG.LANCHP_0 = 215\n AND ML_SMG.LANNUM_0 = ITM.STOMGTCOD_0\n\nWHERE 1 = 1\n[[AND ITV.ITMREF_0 IN ({{item}})]]\n[[AND (UPPER(ITM.ITMDES1_0) LIKE UPPER({{description}}) OR UPPER(ISNULL(DES1.LABEL, '')) LIKE UPPER({{description}}))]]\n[[AND ITV.STOFCY_0 IN ({{site}})]]\n[[AND ITM.TCLCOD_0 IN ({{category}})]]\n[[AND ITM.ITMSTA_0 IN ({{item_status}})]]\n[[AND {{only_in_stock}} = 1 AND ISNULL(ITV.PHYSTO_0, 0) <> 0]]\n[[AND {{below_reorder_point}} = 1\n AND ISNULL(ITF.REOTSD_0, 0) > 0\n AND (ISNULL(ITV.PHYSTO_0, 0) - ISNULL(ITV.PHYALL_0, 0)\n - ISNULL(ITV.GLOALL_0, 0)) < ITF.REOTSD_0]]\n\nORDER BY ITV.ITMREF_0, ITV.STOFCY_0",
"operation": "sqlSelect",
"rowFormat": "arrays",
"sqlOptions": {
"maxTime": 30,
"maxLines": 400
},
"sqlParameters": {
"parameter": [
{
"name": "item",
"type": "list",
"value": "={{ $fromAI('item', `One or more Sage X3 item references (ITMREF_0), comma separated, e.g. FIN008 or FIN008,FIN006. No quotes. Leave empty for all items.`, 'string', '') }}"
},
{
"name": "description",
"type": "contains",
"value": "={{ $fromAI('description', `Fragment of the item description, case-insensitive partial search. Pass the raw fragment, e.g. mountain. Do not add %, they are added automatically. Leave empty not to filter.`, 'string', '') }}"
},
{
"name": "site",
"type": "list",
"value": "={{ $fromAI('site', `One or more site codes, comma separated, e.g. FR011. Leave empty for all sites.`, 'string', '') }}"
},
{
"name": "category",
"type": "list",
"value": "={{ $fromAI('category', `One or more item categories (TCLCOD_0), comma separated, e.g. FINIS, SFINI, BMSOL, RAWMA. Pass the code, not the label. Leave empty for all categories.`, 'string', '') }}"
},
{
"name": "item_status",
"type": "list",
"value": "={{ $fromAI('item_status', `One or more item statuses (ITMSTA_0), numeric code of local menu 246. Leave empty for all statuses.`, 'string', '') }}"
},
{
"name": "only_in_stock",
"type": "number",
"value": "={{ $fromAI('only_in_stock', `Set 1 to keep only the rows with non-zero physical stock. Leave empty to see everything.`, 'string', '') }}"
},
{
"name": "below_reorder_point",
"type": "number",
"value": "={{ $fromAI('below_reorder_point', `Set 1 to keep only the items whose available stock has dropped below the reorder point. Leave empty to see everything.`, 'string', '') }}"
}
]
},
"descriptionType": "manual",
"toolDescription": "Available stock by item and by site, from the ITMMVT totals. This is the tool to call for the question how much is left of a given item. One row per item x site pair.\n\nReturns SITE, ITEM, DESCRIPTION, STOCK_UNIT, CATEGORY_LABEL, ITEM_STATUS_LABEL, STOCK_MANAGEMENT_LABEL, STOCK_ACCEPTED_A, STOCK_QUALITY_Q, STOCK_REJECTED_R, STOCK_TOTAL, ALLOCATED_DETAILED, ALLOCATED_GLOBAL, SHORTAGE_GLOBAL, EXPECTED_SALES_ORDERS, EXPECTED_REPLENISHMENT, IN_TRANSIT, PENDING_ISSUES, STOCK_AVAILABLE, REORDER_POINT, SAFETY_STOCK, STOCK_MAX, BELOW_REORDER_POINT (Yes / No), AVERAGE_COST, STOCK_VALUE_A, LAST_RECEIPT, LAST_ISSUE, LAST_COUNT.\n\nThe figure to quote to the user is STOCK_AVAILABLE, equal to accepted physical stock minus detailed allocations minus global allocations. Do not quote STOCK_TOTAL as available stock.\n\nFor the detail by lot and location, use x3_stock_detail. Do not add quotes or % around values. Maximum 400 rows."
},
"credentials": {
"nx3SoapApi": {
"name": "<your credential>"
}
},
"typeVersion": 1
},
{
"id": "5fe9dab2-12cc-4220-9f4e-d0fb2c0c2b2b",
"name": "x3_stock_detail",
"type": "n8n-nodes-nx3.nx3SoapTool",
"position": [
256,
1120
],
"parameters": {
"advanced": {},
"simplify": true,
"sqlQuery": "SELECT\n STO.STOFCY_0 AS SITE,\n STO.ITMREF_0 AS ITEM,\n ISNULL(NULLIF(DES1.LABEL, ''), ITM.ITMDES1_0) AS DESCRIPTION,\n STO.LOT_0 AS LOT,\n STO.SLO_0 AS SUBLOT,\n STO.BPSLOT_0 AS SUPPLIER_LOT,\n STO.SERNUM_0 AS SERIAL_NUMBER,\n STO.LOC_0 AS LOCATION,\n STO.LOCTYP_0 AS LOCATION_TYPE,\n STO.WRH_0 AS WAREHOUSE,\n STO.PALNUM_0 AS CONTAINER,\n STO.OWNER_0 AS OWNER,\n\n /* --- Stock status: A / Q / R code, not a local menu --- */\n STO.STA_0 AS STOCK_STATUS_CODE,\n CASE STO.STA_0\n WHEN 'A' THEN 'Accepted (A)'\n WHEN 'Q' THEN 'Quality control (Q)'\n WHEN 'R' THEN 'Rejected (R)'\n ELSE ISNULL(NULLIF(STO.STA_0, ''), 'Not specified')\n END AS STOCK_STATUS_LABEL,\n\n STO.QTYSTU_0 AS STOCK_QTY,\n ITM.STU_0 AS STOCK_UNIT,\n STO.QTYSTUACT_0 AS ACTIVE_QTY,\n STO.QTYPCU_0 AS PACKING_QTY,\n STO.PCU_0 AS PACKING_UNIT,\n\n ISNULL(STO.CUMALLQTY_0, 0) AS ALLOCATED_QTY,\n ISNULL(STO.CUMWIPQTY_0, 0) AS WIP_QTY,\n STO.QTYSTU_0 - ISNULL(STO.CUMALLQTY_0, 0)\n AS AVAILABLE_QTY,\n\n NULLIF(STO.LASRCPDAT_0, '1753-01-01') AS LAST_RECEIPT,\n NULLIF(STO.LASISSDAT_0, '1753-01-01') AS LAST_ISSUE,\n NULLIF(STO.LASCUNDAT_0, '1753-01-01') AS LAST_COUNT,\n\n /* --- Count in progress: local menu 1 --- */\n STO.CUNLOKFLG_0 AS COUNT_IN_PROGRESS_CODE,\n ISNULL(ML_CUN.LANMES_0, 'Unknown')\n + ' (' + CAST(ISNULL(STO.CUNLOKFLG_0, 0) AS VARCHAR(10)) + ')'\n AS COUNT_IN_PROGRESS_LABEL\n\nFROM STOCK STO\n\nLEFT JOIN ITMMASTER ITM\n ON ITM.ITMREF_0 = STO.ITMREF_0\nLEFT JOIN (\n SELECT RTRIM(IDENT1_0) AS ITMREF, MAX(TEXTE_0) AS LABEL\n FROM ATEXTRA\n WHERE CODFIC_0 = 'ITMMASTER' AND ZONE_0 = 'DES1AXX' AND LANGUE_0 = 'ENG'\n GROUP BY RTRIM(IDENT1_0)\n) DES1 ON DES1.ITMREF = RTRIM(ITM.ITMREF_0)\n\nLEFT JOIN APLSTD ML_CUN\n ON ML_CUN.LAN_0 = 'ENG'\n AND ML_CUN.LANCHP_0 = 1\n AND ML_CUN.LANNUM_0 = STO.CUNLOKFLG_0\n\nWHERE 1 = 1\n[[AND STO.ITMREF_0 IN ({{item}})]]\n[[AND (UPPER(ITM.ITMDES1_0) LIKE UPPER({{description}}) OR UPPER(ISNULL(DES1.LABEL, '')) LIKE UPPER({{description}}))]]\n[[AND STO.STOFCY_0 IN ({{site}})]]\n[[AND STO.LOC_0 IN ({{location}})]]\n[[AND STO.LOT_0 IN ({{lot}})]]\n[[AND STO.SLO_0 IN ({{sublot}})]]\n[[AND STO.SERNUM_0 IN ({{serial_number}})]]\n[[AND STO.STA_0 IN ({{stock_status}})]]\n[[AND STO.OWNER_0 IN ({{owner}})]]\n[[AND {{only_available}} = 1\n AND (STO.QTYSTU_0 - ISNULL(STO.CUMALLQTY_0, 0)) > 0]]\n\nORDER BY STO.ITMREF_0, STO.STOFCY_0, STO.STA_0, STO.LOT_0, STO.LOC_0",
"operation": "sqlSelect",
"rowFormat": "arrays",
"sqlOptions": {
"maxTime": 30,
"maxLines": 400
},
"sqlParameters": {
"parameter": [
{
"name": "item",
"type": "list",
"value": "={{ $fromAI('item', `One or more Sage X3 item references (ITMREF_0), comma separated, e.g. FIN008 or FIN008,FIN006. No quotes. Leave empty for all items.`, 'string', '') }}"
},
{
"name": "description",
"type": "contains",
"value": "={{ $fromAI('description', `Fragment of the item description, case-insensitive partial search. Pass the raw fragment, e.g. mountain. Do not add %, they are added automatically. Leave empty not to filter.`, 'string', '') }}"
},
{
"name": "site",
"type": "list",
"value": "={{ $fromAI('site', `One or more site codes, comma separated, e.g. FR011. Leave empty for all sites.`, 'string', '') }}"
},
{
"name": "location",
"type": "list",
"value": "={{ $fromAI('location', `One or more location codes (LOC_0), comma separated. Leave empty for all locations.`, 'string', '') }}"
},
{
"name": "lot",
"type": "list",
"value": "={{ $fromAI('lot', `One or more lot numbers (LOT_0), comma separated. Leave empty for all lots.`, 'string', '') }}"
},
{
"name": "sublot",
"type": "list",
"value": "={{ $fromAI('sublot', `One or more sublot numbers (SLO_0). Leave empty for all.`, 'string', '') }}"
},
{
"name": "serial_number",
"type": "list",
"value": "={{ $fromAI('serial_number', `One or more serial numbers (SERNUM_0). Leave empty for all.`, 'string', '') }}"
},
{
"name": "stock_status",
"type": "list",
"value": "={{ $fromAI('stock_status', `Stock status: A for accepted, Q for quality control, R for rejected. Several values comma separated. Leave empty for all statuses.`, 'string', '') }}"
},
{
"name": "owner",
"type": "list",
"value": "={{ $fromAI('owner', `One or more stock owner codes (OWNER_0). Leave empty for all.`, 'string', '') }}"
},
{
"name": "only_available",
"type": "number",
"value": "={{ $fromAI('only_available', `Set 1 to keep only the rows with a strictly positive available quantity. Leave empty to see everything.`, 'string', '') }}"
}
]
},
"descriptionType": "manual",
"toolDescription": "Stock detail by lo
Credentials you'll need
Each integration node will prompt for credentials when you import. We strip credential IDs before publishing — you'll add your own.
httpBearerAuthnx3SoapApiopenRouterApi
Pro
For the full experience including quality scoring and batch install features for each workflow upgrade to Pro
About this workflow
This workflow lets users ask natural-language sales, stock, and revenue questions about Sage X3, using an OpenRouter-hosted Claude model to select from predefined Sage X3 SQL tools, and optionally exposing the same tools through a Bearer-authenticated MCP endpoint for MCP…
Source: https://n8n.io/workflows/18168/ — original creator credit. Request a take-down →
Related workflows
Workflows that share integrations, category, or trigger type with this one. All free to copy and import.
This workflow uses an n8n Chat UI plus OpenRouter (Claude Sonnet) to generate and iteratively edit a single-page local business website, serve a live HTML preview, and publish the approved page to a H
Chat Trigger, Agent, OpenRouter Chat +8
This is the core AI agent used for queryverify.com.
Chat Trigger, Memory Buffer Window, Postgres +4
n8n-ejentum-harness-integration-patterns. Uses lmChatOpenRouter, memoryBufferWindow, chatTrigger, mcpClientTool. Chat trigger; 37 nodes.
OpenRouter Chat, Memory Buffer Window, Chat Trigger +3
This n8n workflow is designed for Shopify store owners and e-commerce managers who want to automate their store operations through an intelligent AI assistant. The workflow creates a conversational in
Shopify Tool, Discord, Telegram +10
Send an AI a few details about your "Dream Customer" in normal english, then have it search the web and give you a "Dream 100" - 100 ideal prospects to connect with in your industry.
Agent, OpenRouter Chat, Chat Trigger +7