# Adding a Private AI Layer to Dynamics GP On-Prem > Source: https://netray.co/erp-help/private-ai-layer-dynamics-gp-on-prem-architecture > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: ERP Help ## Short answer Dynamics GP is an easy system to read from and a delicate one to write to. Read from curated SQL views on a replica of the company database, search documents with a local retrieval index, serve an open-weight model on your own GPU, and send any write through eConnect or another supported integration after a person approves it. ## Kind advanced ## System dynamics ## Erp Microsoft Dynamics GP ## Module Data access / eConnect / SQL Server / Security ## Query how to add AI to Dynamics GP on-prem ## Alternate Queries - Dynamics GP AI integration without cloud - Dynamics GP local LLM SQL Server - chat with Dynamics GP data privately - GP private AI architecture eConnect ## Applies To Dynamics GP 2016 to GP 18.x on SQL Server, single or multi-company, with a SQL replica available ## Steps ### How to stand up a private AI layer on GP **Items:** - Pick the first questions: open receivables and payables, inventory by site, open purchase orders, trial balance, and who approved what. - Create a read-only copy of the DYNAMICS system database and each company database (for example TWO) using AlwaysOn, log shipping or restores. - Build curated views over the master, open, work and history tables so the assistant sees one clean shape per business object. - Create a dedicated SQL login with SELECT only on those views; never reuse a GP user login or DYNSA. - Index attachments, item notes, vendor documents and any scanned files kept on file shares in a local retrieval store. - Expose named read actions via a tool server with parameter validation and row limits, and write every call to an audit log. - Serve the model on local GPUs and pilot read-only questions with finance and purchasing before enabling drafts. ## Sections ### Real data access paths in GP **Body:** GP keeps a system database named DYNAMICS and one database per company, named by the company id. Tables follow a letters-and-numbers convention: RM00101 customer master, PM00200 vendor master, RM20101 open receivables, PM20000 open payables, IV00101 item master, IV00102 item quantities, POP10100 and POP10110 purchase order header and lines, SOP10100 and SOP10200 sales documents in work, GL00100 account master, GL10000 unposted batches and GL20000 and GL30000 open and history journal transactions. Open, work and history tables are separate, so one question often needs a union. For supported integration, eConnect and the Integration Manager are the usual paths for writing documents, while read-only reporting typically uses views, SmartList and SSRS. Some GP versions also offer service-based interfaces, but check what your build supports. Reading SELECT-only views from a replica is simple and robust; writing with SQL is not supported. **Code:** -- Read-only: open receivables by customer from the open table SELECT c.CUSTNMBR, c.CUSTNAME, SUM(CASE WHEN o.RMDTYPAL IN (7,8,9) THEN -o.CURTRXAM ELSE o.CURTRXAM END) AS OpenBalance FROM RM20101 o JOIN RM00101 c ON c.CUSTNMBR = o.CUSTNMBR WHERE o.VOIDSTTS = 0 GROUP BY c.CUSTNMBR, c.CUSTNAME HAVING SUM(CASE WHEN o.RMDTYPAL IN (7,8,9) THEN -o.CURTRXAM ELSE o.CURTRXAM END) <> 0; -- RMDTYPAL document types differ by build and setup; confirm the sign logic against an aging report. ### Reference architecture and air-gapped option **Body:** The architecture is a read-only replica or reporting copy, change data capture or timestamp-based incremental extracts feeding a retrieval index, a tool server such as an MCP server exposing approved read actions, an open-weight model on vLLM or Ollama, and a local audit log. GP environments often run on a modest virtual host, so the replica and the model server should be separate machines to avoid starving the ERP. An air-gapped option is straightforward because GP itself needs no internet: bring in model weights and packages by controlled transfer, refresh the replica from backups inside the enclave and keep logs local. You own model upgrades and evaluation, which is the cost of full isolation. ### Permissions and write-back **Body:** GP creates a SQL login for each GP user and manages access with security tasks and roles in the DYNAMICS database, plus company-level access. Mirroring this means reading the user's roles and companies at question time and restricting the tool server to the companies and object types they may see. Many GP shops have broad roles; use the migration to tighten them and avoid exposing payroll or bank data by default. Keep write-back as a later, narrow phase. The assistant may draft a payables invoice or a sales order and show the proposed values; after a person approves, the document is submitted through eConnect or a vetted integration so GP validation and posting numbers apply. Never let the assistant hold a login with write rights to company tables. ### Sizing, rollout and honest limits **Body:** A 30 to 150 user GP site usually needs one modest inference GPU for an 8B to 14B class model, or a quantised larger model, plus a small server for the index. Document sets are often small, so an index of a few gigabytes is common. Phase one is read-only questions, phase two is drafts, phase three is a handful of approved actions. Honest limits: GP is a mature product with a finite roadmap, so check your support and migration plans before investing in write-back. SmartList, SSRS and Power BI already answer fixed questions well; an assistant earns its keep on ad hoc questions and documents. Third-party add-ons with their own tables need separate mapping, and answers that depend on them should be tested before release. ## Pitfalls - Querying only open tables and missing history. Unioning RM20101 with RM30101 or PM20000 with PM30200 changes the answer. - Reusing a GP user login for the assistant. GP users are SQL logins with broad rights in many installs. - Treating document type codes as universal. Confirm them against a known report for your build. - Ignoring multi-currency amounts and originating versus functional columns in the tables. - Writing to company tables with SQL because it seems quick. It bypasses GP validation and breaks posting integrity. ## Ai Angle A grounded GP assistant answers from curated views, shows the query behind each figure and respects the asking user's company access. Netray builds this as a custom private layer using open-weight models on your hardware, with approval before anything is written. ## Faq ### Can we use SmartList and SSRS instead? **Answer:** For fixed, repeated questions, yes, and they are cheaper. An assistant adds value for ad hoc questions across modules and for searching documents. Many sites keep SmartList and add the assistant on top. ### Is eConnect the only way to write to GP? **Answer:** It is the long-standing developer path, and Integration Manager is common for imports. Which options are available depends on your version, so confirm in your documentation. Direct table writes are not supported. ### Does this work with GP 2016 or older? **Answer:** Read-only SQL access works across versions with table-level differences. Older builds may lack newer interfaces, so keep the tool server on views and test per version. ### How is multi-company handled? **Answer:** Build one view set per company database or a parameterised tool that selects the database from the user's allowed list. Never let the model supply a database name freely. ## Search terms Dynamics GP AI integration, Dynamics GP local LLM, GP SQL views AI, eConnect write-back, Dynamics GP RM20101 PM20000, private AI Dynamics GP, air-gapped Dynamics GP AI, chat with Dynamics GP data, Dynamics GP security roles, Dynamics GP MCP server --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json