Sql Query Job Schedule
Dynamics GP Integration Technology Highlights
Modern Corporate ERP application is not something isolated. The majority of new implementations require connection with the legacy parts of your IT infrastructure. Even when you plan to discontinue legacy applications, you do it in the evolutionary manner, step-by-step. ERP integration is recommended methodology. Today we would like to review Great Plains integration tools and scenarios. This paper is not intended to be very technical and it might be recommended to company management team for setting up the goals for the integration project homework. Let's begin with integration tools review:
1. Integration Manager. This instrument validates business logic and is data compromise proof. Having long years working with IM, we would like to reassure that everything, that you can do in other integration tools you can do directly in Integration Manager (one exception is Dexterity). Integration Manager is end user friendly and at the same time it is open for advanced SQL Queries, VBA event driven custom scripts, incoming parameters translation. Latest versions 10.0 and 2010 have eConnect connectors, making IM performance in fact the same as direct eConnect programming. There are some areas, where IM doesn't have integration options, Invoicing module for example, but eConnect doesn't work with Invoicing as well. If you have to do Invoicing direct integration, consider Dexterity programming. However before you go this direction, please know that Sales Order Processing module has the same functionality plus additional features, where both IM and eConnect are in your disposition
2. eConnect. Its libraries could be included into your Microsoft Visual Studio .Net web project, where can find code samples in C#, VB and C++. Initially it was introduced around 2003 for ecommerce programmers as the tool to enable ecommerce shopping cart integration as SOP Invoice or Sales Order with potential customer deposit/credit card payment. Modern day eConnect covers most of the modules: General Ledger, SOP, POP, Receivable Management, Payable Management, US Payroll, Inventory Control, Project Accounting as the top of the list. If you need to program integration from non-Microsoft operating system or environment, such as Linux/PHP/MySQL, Oracle, Mac, please read the next paragraph about web services
3. Web Services. This is abstraction level, which allows you to call eConnect methods via HTTP post or SOAP. Such technologies as SOAP allow you to call eConnect and manipulate GP master records and documents from Linux, Unix, Oracle, Macintosh platforms, or virtually from everything that supports web service call technology
4. Dexterity. This is the architecture of initial Great Plains Dynamics and it is still the foundation of modern Dynamics GP. There are no limits for Dex programmer on what could be modified, altered or added to this application business logic. However, Dexterity is semi proprietary and its Sanscript programming is not recommended for somebody, who is not working for consulting firm, as the learning curve might be a show stopper. We would like to come through on of the Dexterity integration extension example – Posting Server. Alba Spectrum Posting Server allows you to schedule the batch for automatic posting directly from your MS Visual Studio project or from another software development environment. Posting Server is programmed in Dexterity with extensive reference to the Source Code
5. Direct SQL Insert Statements. GP doesn't prohibit direct SQL insertion, however this is not something you should try right away, as being excited about SQL programming and making initial steps in your new ERP table structure and data flow discovery
6. Table Import tool. This is rather similar to SQL direct insertion, this method was great for non MS SQL Server DB platforms for earlier Dynamics versions, especially 7.5, 7.0, 6.0, 5.5, 5.0 on Pervasive SQL 2000 and Ctree. We would only recommend it for historical versions, where direct Microsoft SQL Server Query Analyzer is not available
7. Case Studies: eCommerce. Here in our experience we have to confess, that Integration Manager does perfect job in quasi real time shopping cart integration scenarios. Theoretically it is possible to pull paid shopping carts directly from MySQL, Oracle, Microsoft Access or other ODBC compliant database, but in reality the majority of our ecommerce customers preferred to do every few minutes shopping cart export into coma or tab delimited text file and then pull it into SOP transaction via scheduled IM integration (every several minutes as well)
8. Case Studies: Manufacturing. Imagine you are mid-size production company with existing in-house developed simple MRP system. It might be Microsoft Access, FoxPro, Visual Basics, MySQL/PHP. Now – you would like to post Bill of Materials all the way through GL in GP automatically. We recommend Alba Spectrum Posting Server with Workflow, where initial integration could be done in Dexterity Sanscript programming and the rest is our traditional Posting Server features
9. Case Studies: Healthcare Nurse Placement industry. The challenge was to send billable time and expense reports into GP, where we recommended posting into Project Accounting Timesheets, Expenses. The set of SQL Stored procedures was created to move timesheets and expenses into PA document batches
10. Case Studies: International Business or Multinational Corporation. Headquarters in Chicago had GP as its Corporate ERP system, while in Brazil it is not localized and we recommended SAP Business One with the daily GL transactions consolidations via Export to Excel in SB1 and import via IM to Great Plains
11. Case Studies: Collection Agency. Here your customers are reversed into your vendors. In our experience we did in in the Integration Manager via VBA scripting and Names Translation (vendor to customer translation could be done in Excel and then loaded into IM integration)
12. Case Studies: Advertising Agency. Here you have the problem of overselling advertising space to your new customers, who could easily go bankrupt and block your collection efforts. At the same time, you would like to keep these customers and prospects to enter the advertising space. There is Collection Management module, recommended for your environment
13. Some advices on the international projects. Please, check if GP is localized for your targeted country. In general – it is localized for English, Spanish (except continental Spain, sometimes Brazil is mistreated – they speak there Brazilian Portuguese, not Spanish), Arabic speaking countries. If you are in Russian, Serbian, German, French (except Quebec province in Canada), Italian, Chinese, Japanese, Korean speaking country, consider alternative Corporate or Small Business ERP package. We recommend you to take a look at Dynamics AX Axapta or SAP Business One. In Brazil, consider also Microsiga, while in Russian Federation we recommend you to take a look at 1S Bukhgalteria
About the Author
Andrew Karasev is Great Plains Certified Master, MVP, [email protected] 1-866-304-3265, 1-269-605-4904. He is also the initiator of eFaru project http://www.efaru.com and founder of Alba Spectrum information space
Sql Query Job Schedule
Oracle DBA Lite: an introduction to Oracle administration, part 7
Ecommerce Integration with Dynamics GP: Simple Method to Achieve Results
Sometimes we are trying to get the best products in integrating ecommerce shopping cart with your Corporate ERP application. But, it might be much easier, if we just listening the stories of other ecommerce merchants and retailers. As we see from our consulting practice very successful and relatively low budget ecommerce integration projects were done in old-good-days Integration Manager. This method gives you flexibility and such great advantage, as preserving your historical ecommerce programming investment, as it doesn't require you to redeploy B2B or B2C ecommerce website on the new platform or product. You keep your existing site and export newly checked out shopping carts either into the text files with folder controlling mechanism (exported, processed, exceptions with notification to the IT team), or even open ecommerce SQL database (you are not restricted to Microsoft SQL Server, it might be on Oracle, MySQL with PHP, FoxPro, MS Access, Pervasive SQL, anything that is ODBC compliant). Let's review the recommendations on IM integration scenarios:
1. Where shopping cart goes in Great Plains? Usually into SOP Sales Invoice (SOP10100 and SOP10200 tables) with Customer Deposit (in B2C case) or just without deposit (in B2B scenario, where you may sell on account). Sometimes, when you are selling service with flat one line description – it is more natural to send it as Accounts Receivable Invoice (RM10301). We do not really recommend you to target the carts into older Invoicing module (IVC10100), as this module is not exposed via Integration Manager
2. Integration Anatomy. First of all you need to build Query or several linked Queries (SOP transaction uses at least two queries, where you have to link SOP Header and Lines). Next, you choose the integration destination, such as SOP Entry for the recommended way to transform the cart into Sales Invoice. And finally in the destination your design how the fields from the Queries will be mapped into destination document fields (with possible VBA script modification logic, data translation – replacing one value or string with another, and other methods). In recent versions of IM, such as 10.0 and 2010/11.0 – you can also deploy so-called eConnect adapters, where database access is more efficient, as eConnect is coded in SQL Stored Procedures (encrypted to discourage copying), versus old fashioned IM connectors, where such technology as Microsoft OLE is deployed (plus it requires GP workstation to be running for OLE fields enabling – obviously stored procedure is faster, comparing to multiple applications and graphical interface stack dependent OLE Server)
3. What kind of experience is required to setup IM Integration? It is recommended to have Great Plains technical or even functional consultant to help you with setup. But, IM is end user friendly tool, especially when you are planning to base it on text files – there are examples on each scenario in Integration Manager default database (installed automatically). If you plan to go and try advanced ODBC queries (directly pulling data from ODBC compliant database) – then you should have experience with the DB platform (the most elegant and powerful way is to create SQL view and base the ODBC query on the view). If you want to process text files when integration is complete, you should do it in VBA scripts (upon integration completed – just move text file into Completed folder, maybe you also need Exception transactions – these should be handled in upon document completed event)
4. Integration scheduling. We recommend quasi real time ecommerce integration approach, where you schedule IM integration via Windows macro shareware or freeware routines (or more advanced tool is available for purchase directly from Microsoft Business Solutions). If your customer is OK with twenty (or five) minutes delay – Integration Manager will do the job, and there is no need for deploying real time integration tools, such as eConnect direct programming in Microsoft Visual Studio
5. Ecommerce Shopping Cart platforms and integration to Great Plains. We strongly believe that such popular carts, as Magento on MySQL and PHP, ASP.Net Storefront, or your custom SC are open for transactions export into the text file or ready for their database table exposure via ODBC. Higher percentage of the customers however decided to undergo Text file (CSV) direction
6. If you think Integration Manager doesn't fit and you need real time ecommerce shopping cart integration. Not a problem, assuming that you have read the recommendations above and you still feel that they do not do the job or do not give you a way to follow. We recommend you two options to consider. First one would be eConnect direct programming (you should be experienced in such Microsoft .Net programing languages as C# or VB – where you will enjoy code samples, potentially also C++). As eConnect libraries could be included into MS Visual Studio web project, shopping cart integration could be triggered to happen immediately when the order is paid. Second method – we can give you a set of SQL Stored Procedures, geared to open SOP Transaction for simpler way of data feeding (comparing to eConnect, where some learning curve might be needed)
7. If you would like to deploy eCommerce product and decided that neither Integration Manager, not eConnect, nor Order Connector do the job for you, we invite you to see the demo of Alba Spectrum eCommerce module for Dynamics GP Great Plains. We are always trying to save consulting budget money for our customers, but it is often the case that the project is too challenging and requires moderate or even substantial budget to be fulfilled. We are here to help you – feel free to call
8. For additional information, please feel free to call us 1-866-528-0577 or 1-630-961-5918 (this number works for international customers) or email us [email protected] We serve you USA/Canada nationwide via remote support (web sessions and phone/Skype conferences). Local service is available in Western Michigan, Chicagoland, Southern California (LA, Orange County, San Diego), Houston area of the state of Texas
About the Author
Andrew Karasev, http://www.albaspectrum.com Great Plains Dynamics GP and eEnterprise Certified Master, Microsoft MVP and consultant with 10 years and plus experience and expertise. Alba 866 Corporation, 1-866-528-0577, [email protected] If you are thinking to implement Dynamics GP in challenging environment, we recommend you to give us a call. Our information portal is http://www.pegasplanet.com Pegas Planet