Microsoft Dynamics GP 10.0 is SQL 2005 application, however direct SQL scripting to feed or integrate eCommerce orders or invoices and payments might be complicated, due to rich GP business logic and its required validation in data integration. As eCommerce typically sends sales data to Great Plains Sales Order Processing module, which is really eConnect covered area, we recommend you first research Microsoft Dynamics GP eConnect technology, we will review eConnect in brief in this article below. Direct data feeding via SQL stored procedures is also OK if you try to restrict your integration functionality: do not allocate inventory items in SOP lines, create sales orders rather than invoices (to avoid building up GL distribution in your SQL scripting). Let’s begin:
1. eConnect. This tool and SDK was specially developed for eCommerce programmer, and later on Microsoft Business Solutions advanced eConnect and now it is covering other modules: POP, Payroll, General Ledger, etc. If you dig deeper, you will figure out that the heart of eConnect is set of encrypted SQL stored procedures. These procedures imitate GP Dexterity business logic in creating, deleting, modifying master records and work transactions. eConnect should be preferred tool as it validates Dex business logic and is recommended by MBS for e-commerce developers. If you think that eConnect is not a relevant tool for you, you can go ahead and take SQL querying route
2. SQL. Here you need to be comfortable with your GP SQL tables familiarity. The best tables reference could be found in GP workstation: tools->resource description->tables. Tables are sorted by series, you go to Sales, sort by tables physical name and jump to SOP10100 – this is Sales Header table, it keeps Sales Order, Invoice, Return, Quote document header. The second table will be SOP10200 – this one stores sales document lines: inventory items or non-inventoried items
3. eConnect and Batches posting. GP architecture has several phases of transaction life: work, open and history and among these phases only work transactions could be created in eConnect and be submitted for GP operator approvals. In eCommerce application you often want to post payments against invoice plus you want invoice to be posted and scheduled for delivery. If this is the case, you should research Alba Spectrum Posting Server, which is extension to eConnect and by technology it is Great Plains Dexterity application, appealing to Dex source code – developer places approved GP batches to custom table and posting server calls GP Dexterity native logic to post the batches
4. GP Integration Manager. If the volume of eCommerce transactions is relatively low and you are small organization, you can consider integration e-commerce transactions via IM over night. IM deploys GP as OLE server and so, GP workstation should be running behind the scenes. Some customers report IM performance issues, which is normal scenario for OLE server, where you open and feed data into GP hidden forms and screens.