Great Plains SQL Technology Background – tables structure, Dexterity

SQL means tables, their names, fields, data flow as it reflects business processes, such as Sales invoices entering, posting, customer payments against these invoices, voiding, etc.  As you can imagine, Great Plains MRP is not just the collection of tables, it has software logic, which is realized in Microsoft Dexterity (or as it was formerly known Great Plains Dexterity).  If you have intentions to customize Great Plains, you can do it either on SQL level or in Dexterity, plus new tools are now available, such as eConnect, which is in essence the replication of Dex logic in encrypted SQL stored procedures with rich interfaces stack: COM+, BizTalk, Web Services.  Dexterity has also such extensions as Extender (or full version of eOne extender, available from eOne).  Let’s come to technical highlights:

1.       GP tables structure.  The best and the most appropriate way of getting the essence of GP tables and their relations, is to install and review GP SDK, available from CD 2.  However, more practical is quick access of the tables structure in GP workstation interface, simple go to Microsoft Dynamics GP->Tools->Resource Descriptions->Tables, then select series: financials, manufacturing, project, system, financials, receivables, payables, payroll, HR

2.       Data entering and posting workflow.  GP is mid-market ERP, and its business processes are not trivial.  In order to get them imitated via SQL stored procedures, you will need to enter imitating transactions in the test company environment.  Good example – if you plan to imitate Sales Order Processing cycle: enter Sales Invoice, then post it and apply customer payment – monitor each step in SQL to verify which table got the record and on which step

3.       Dexterity.  Great Plains Dex is the Source of current GP version, 10.0 as of March 2008.  Dex business logic is in Dynamics.dic and associated third party dictionaries.  Dexterity in-house programming is not recommended, due to complexity and long learning curve for Dexterity software developer

4.       eConnect.  This SDK is more new GP welcomed tool, than Dex.  We recommend it if you try to do GP modifications, integrations, DB conversion internally

5.       GP eCommerce Shopping Carts and Catalogs.  The best approach is first to explore Microsoft Great plains Dynamics GP Business Portal Order Management module, which is seamlessly integrated with GP SOP and Inventory Control modules natively