Great Plains Consultant Newsflash: Custom EDI by Andrew Karasev

Microsoft Dynamics GP ERP application, or popular old name Great Plains Dynamics and eEnterprise, if you have relatively recent version, is based in Microsoft SQL Server. If you are experienced SQL DBA yourself, just this fact should give you the idea on structuring select statement and make it Electronic Document Interchange code compliant. Of course, for luxury EDI solution you can purchase Add-Ons and simply setup EDI export in the user friendly interface, however this publication is for people, who are either on the tight budget, or who have really complex EDI requirements and data format export rules. This news should also ideally help you with your Great Plains Partner, ISV and technical consultant and programmer selection:

1. Header and Trailer. In order to produce header and trailer, you should consider unions, where you simply group your records to produce single heading and footing lines

2. EDI lines. Normally these are items related lines, which you see either on customer or vendor invoice. You pull these lines from SOP30300 or POP tables

3. EDI formatting. The idea is fairly simple - EDI format is typically fixed length fields groups. In SQL Select statement you format each fields individually with either CONVERT or CAST constructions. Sometimes, especially new EDI rules require CSV or even XML files - if you got these - you should be lucky to avoid doing precise fixed length formatting. EDI with XML format are becoming popular in eCommerce scenarios

4. Marking exported records. This task should be done by SQL stored procedure, this is why in EDI integration you should consider MS SQL Server DTS package creation. DTS package will allow you to export text EDI file (plus potentially email it to your vendor or even upload it to your vendor ftp) and run SQL stored procedure to mark exported records. In Great Plains for marking records you can either deploy existing fields, that are not in use by your business logic or user defined fields, plus you can create EDI export status custom table in each company database. GP doesn't have restrictions on creating custom objects in your company database, this restriction exists in other mid-market MRP applications, such as SAP Business One

About the Author

Andrew Karasev, Alba Spectrum LLC, help@albaspectrum.com, http://www.albaspectrum.com, 1-866-528-0577 , Great Plains, VAR, Partner and Reseller in Illinois, Georgia, South Carolina, California, Texas. Please visit our info portal Pegas Planet: http://www.pegasplanet.com Local Service in Chicago, Atlanta, San Diego, Los Angeles, Orange County, Houston