EDI: Electronic Document Interchange for Microsoft Great Plains ? Overview for Software Developer/Pr

Microsoft Great Plains - Microsoft Business Solutions accounting and ERP system, originally targeted to mid-size - now, with advancements and increasing reliability of its database - Microsoft SQL Server, Great Plains is attractive solution for large corporation. Big companies usually have purchasing and order processing automation via so-called Electronic Document Interchange or EDI. EDI was introduced long time ago for UNIX systems and in most of the cases appears in the form of Header, Lines and Trailer of predefined fixed position fields.

We would like to give you - programmer, software developer, database administrator the primary clues on producing EDI formatted text from Microsoft Great Plains database. Please, note however that Great Plains here is taken as the example, similar approach will work for other SQL based systems: Navision (SQL Database or C/SIDE database), Microsoft RMS, Solomon as well as Oracle and other non-Microsoft products. In the case of non-SQL system, such as old Great Plains Dynamics, ACCPAC on Pervasive SQL - IDE interface will involve ADO/ODBC or Microsoft Access programming.

1. Sample Query ? The query below uses CAST construction to make the result fixed length and meet the positioning. Plus the unionizing allows to produce Header and Detail in one select statement. Here we are dealing with work Sales Documents

select

case

when b.LNITMSEQ=c.LNITMSEQ+1 and b.CMPNTSEQ=c.CMPNTSEQ+1 then CAST('Header' as char(10))

else CAST('Detail' as char(10))

end

as FIELD0,

case

when b.LNITMSEQ=c.LNITMSEQ+1 and b.CMPNTSEQ=c.CMPNTSEQ+1 then cast(a.CUSTNAME as char(65))

else cast(cast(b.QTYDMGED as decimal(19,5)) as char(65))

end

as FIELD01,

case

when b.LNITMSEQ=c.LNITMSEQ+1 and b.CMPNTSEQ=c.CMPNTSEQ+1 then CONVERT(char(51), a.DOCDATE, 101)

else cast(b.ITEMDESC as char(51))

end

as FIELD03

--Additional fields go here

from SOP10100 a join SOP10200 b on a.SOPTYPE=b.SOPTYPE and a.SOPNUMBE=b.SOPNUMBE join

RM00101 d on a.CUSTNMBR=d.CUSTNMBR

join

(select SOPTYPE as SOPTYPE, SOPNUMBE as SOPNUMBE,

LNITMSEQ

as LNITMSEQ,

CMPNTSEQ

as CMPNTSEQ from SOP10200

union

select SOPTYPE as SOPTYPE, SOPNUMBE as SOPNUMBE, MIN(LNITMSEQ)-1 as LNITMSEQ, MIN(CMPNTSEQ)-1 as CMPNTSEQ from SOP10200

group by SOPTYPE, SOPNUMBE

) c on a.SOPTYPE=c.SOPTYPE and a.SOPNUMBE=c.SOPNUMBE

and ((b.LNITMSEQ=c.LNITMSEQ and b.CMPNTSEQ=c.CMPNTSEQ) or (b.LNITMSEQ=c.LNITMSEQ+1 and b.CMPNTSEQ=c.CMPNTSEQ+1))

left join SOP10106 udf on a.SOPTYPE=udf.SOPTYPE and a.SOPNUMBE=udf.SOPNUMBE

where a.ADDRESS3'Exported' and

a.SOPTYPE=3 and upper(d.USERDEF2)='YES'

order by a.SOPTYPE, a.SOPNUMBE, b.LNITMSEQ asc

2. Mark processed documents - for this reason we use SOP10100.ADDRESS3 field - which was not used in Great Plains Dynamics/eEnterprise version 6.0:

update SOP10100 set ADDRESS3='Exported' where SOPTYPE=3

3. Communication with UNIX EDI Client or Server ? each case requires individual approach. You may have to assign the file directory, exposed to the UNIX system or use old DOS command to move the file, or you can have automatic email. Good idea is to write communication application in your favorite programming language

4. Scheduled DTS Package - you should probably create DTS package to do all the steps: call SQL Query and save it as a file, then call DOS command or simple EXE application - which does communicate with UNIX

Happy programming! if you want us to do the job - give us a call 1-866-528-0577! help@albaspectrum.com

About The Author

Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies ? USA nationwide Great Plains, Microsoft CRM customization company, based in Chicago, California, Colorado, Arizona, New York, Texas, Florida, Georgia and having locations in multiple states and internationally (www.albaspectrum.com), he is Dexterity, Transact SQL, C#.Net, Crystal Reports and Microsoft CRM SDK developer; akarasev@albaspectrum.com

---

The Software 2005 Conference - A Review

The Software 2005 conference is now a wrap. This conference,... Read More

The Tools and Features of Adobe Photoshop

Adobe Photoshop is one of the world's leading graphics editing... Read More

Falling in Love With More Than One Screensaver: The Fun Part

Having from five to ten and more favorite screensavers is... Read More

You Can Write Microsoft Office Program in About 60 Minutes

Microsoft Office program is a programming tool called Visual Basic... Read More

What is Groupware?

Vince Lombardi once said that, "The achievements of an organization... Read More

Cisco Certification: Introduction To ISDN, Part V

The major reason I recommend getting your hands on real... Read More

Keep Your Software Simple! A Review of EditPlus

I like my software simple. If it's too complex or... Read More

A Simple Guide To Wikis

A wiki is an editable text-based website. But you don't... Read More

Protect Your Most Vital Business Asset with Security Software

Homeland security, airport security, Internet security â?" these days weâ??re... Read More

Behave, Word, Behave!

If you copy something from a Web site or elsewhere...... Read More

Microsoft Axapta, Navision or Great Plains: ERP Selection for Large Corporation

If you would like to pick something from Microsoft, or... Read More

Groupware and Version History: Collaboration Series #1

This article is the first of a series of articles... Read More

Spyware Definitions List

The adware and spyware definitions list is very long. But... Read More

Five Tips For A Great Software Demo

Whether you need to close a sale, gather end-user feedback,... Read More

Destination: Desktop for Google

First we had the original Google search that evolved into... Read More

Corel WordPerfect 7 Macro Programming Example

Case study: A secretary using Corel WordPerfect 7 is often... Read More

Microsoft Great Plains - Microsoft RMS Integration ? overview

Microsoft Great Plains and Microsoft Retail Management System (Microsoft RMS)... Read More

Microsoft Great Plains: Annual Enhancement Program ? How To Be Re-Enrolled

Microsoft Business Solutions Great Plains is mid and even corporate... Read More

What to Do if All Screensavers Fun is Grayed Out?

Finally, you have some time to personalize your desktop with... Read More

RFID: Strengthen the Position for SAP; United States

SAP Inc., a global leader in client/server enterprise application software... Read More

ERP Implementation: Success Factors

As seeing large number of implementations ? in our case... Read More

What Stands Behind The Software Description?

With thousands of web pages added to the Net every... Read More

Professional Software Icons For Your Standalone Application

User interfaces and accessibility are some of the most important... Read More

Integrating Microsoft Great Plains Accounting/ERP: RMS, CRM, eCommerce, Lotus Domino ? overview

Microsoft Business Solutions Great Plains has substantial market share among... Read More

Google Brings the Earth to Your Desktop

Google Inc. has launched a new software package that allows... Read More

Internet Relay Chat - A Basic Introduction

What is IRC?IRC is Internet Relay Chat. It is a... Read More

Intro to UNIX Shells

A UNIX Shell is in simplest terms, a command line... Read More

Microsoft CRM Data Conversion FAQ

Microsoft Business Solutions CRM data conversion deserves FAQ type of... Read More

Explore the Internet in a Whole New Way

For a long time now Microsoft's Internet Explorer has ruled... Read More

Microsoft Great Plains: Offshore Customization & Development ? Overview for Consultant

When you visit department stores and see that majority of... Read More

Defining OLAP Solutions and Data Warehouse design

This tutorial covers OLAP solutions used by Data warehouses and... Read More

ERP Consulting: Microsoft Great Plains Partner Future Directions

In the Clinton era the status quo was simple: you... Read More

Your Computer May Be Infected, Heres How To Check (NOT about virus)

NOTE: Please take time to read on - it may... Read More