pi-sap-business-one

Pi extension that adds a sap_business_one_query tool for querying SAP Business One company databases hosted on Microsoft SQL Server.

The extension is tailored to the SAP Business One schema and prompts Pi to use standard objects such as business partners (OCRD), items (OITM), sales orders (ORDR/RDR1), deliveries (ODLN/DLN1), A/R invoices (OINV/INV1), and purchase orders (OPOR/POR1). Custom fields generally start with U_.

This package currently supports SAP Business One on Microsoft SQL Server. SAP HANA is not supported.

Install

pi install npm:pi-sap-business-one

Or from Git/local development:

pi install git:https://git.otmatas.com/pi-extensions/pi-sap-business-one.git
# or, from this directory
npm install
pi install ./

Configuration

The extension creates and reads only:

~/.pi/agent/pi-sap-business-one/config.json

{
  "server": "192.168.1.10",
  "port": 1433,
  "user": "sap_b1_readonly",
  "password": "",
  "database": "SBODEMOUS",
  "encrypt": true,
  "trustServerCertificate": false,
  "readOnly": true,
  "maxRows": 100,
  "timeoutMs": 30000
}

The database should be an SAP Business One company database, not SBO-COMMON.

Security

Always use a dedicated SQL login with read-only access to the required company database. Never use sa or the SAP database administration credentials.

readOnly defaults to true. It accepts one SELECT or WITH statement and rejects DML, DDL, procedure execution, SELECT INTO, permission changes, and administrative commands. This guard is defense-in-depth; SQL Server permissions remain the primary protection.

Keep trustServerCertificate: false unless a self-signed certificate on a trusted network is intentional.

Tool

sap_business_one_query accepts:

• query: SQL Server query against the SAP Business One company schema
• database: optional company database override
• maxRows: optional returned-row limit (maximum 1000)
• timeoutMs: optional query timeout

Results are returned as a compact Markdown table, with structured rows in tool details.

Example request:

Show the 10 newest open SAP Business One sales orders with customer, date, and total.

Typical query:

SELECT TOP 10
  T0.DocNum,
  T0.CardCode,
  T0.CardName,
  T0.DocDate,
  T0.DocTotal,
  T0.DocCur
FROM ORDR AS T0
WHERE T0.DocStatus = 'O' AND T0.CANCELED = 'N'
ORDER BY T0.DocDate DESC, T0.DocEntry DESC;

Development

npm install
npm run typecheck
npm test
npm pack --dry-run
pi -e ./