| |
| |
|
softwarecatch.com
|
|
|
|
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
MORE RESOURCES:
Google News |
|
|
|
RELATED ARTICLES
Running a Program on a Remote Server Using SSH How do you run a program on a remote server using ssh?For this example we'll have two servers, one named Johnny and another named Cash. Both are running openssh. ERP Remote Support: Microsoft Great Plains Analysis - Pluses & Minuses Former Great Plains Software Dynamics/eEnterprise and currently Microsoft Business Solutions Great Plains serves midsize and corporate clients as ERP system in the following countries and regions: USA, Canada, Mexico and Latin America, Brazil (where MBS actually promotes Navision and has GP for multinational corporations), Saudi Arabia, OAE, Egypt and the rest of Middle East, South Africa, Nigeria and the whole African continent, U.K. Easy Guide to RAID Recovery What is RAID RECOVERY?RAID stands for Redundant Array of Inexpensive Disks. It is a method of combining several hard drives into one unit. How To Choose A Fire Wall Software Program In the real world a "fire wall" is a fireproof wall that is built to stop the spread of fire from one part of a building to others. In the Internet world a firewall has a similar purpose in that it stops the spread of harmful viruses and attacks from entering your home or office network. CRM and Customer Life Cycle Customer Relationship Management or CRM is a combination of enterprise strategies, business processes and information technologies used to learn more about customers' needs and behaviors in order to develop stronger relationships with them. CRM software systems automate many customer-related business tasks. Lotus Domino: Reports and Connectors - Crystal Reports, XML, ODBC/JDBC, OLE Lotus Notes Domino is very efficient in electronic document workflow automation. Unfortunately the electronic document workflow can not replace paper documents completely. Microsoft CRM Integration with Microsoft Retail Management System (RMS) - Overview Microsoft Client Relation Management system (Microsoft CRM) and Microsoft RMS are both Microsoft SQL Server based applications, however historically Microsoft was purchasing industry leading software applications, such as QuickSell which is now Microsoft RMS. So, RMS design fundamentals were minted a long time before Microsoft CRM. Does your Company have Documentum? Are you lost in the mess of documents that get passed around your company, never knowing what the latest version is and which one you should work on without worrying if someone else has already made the same editions that you are making? Perhaps you have heard of collaboration software solutions such as Documentum to help your company manage its documents that are passed around. Documentum is a very good solution to this problem but is it the only one? In this article you will be presented with some basic information about the differences in collaboration software from Documentum and NextPage. 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. Groupware and Version History: Collaboration Series #1 This article is the first of a series of articles exploring specific aspects of groupware. The brief informational articles in this series discuss some of the technologies associated with groupware, as well as some of the characteristics of groupware. Why Stick With Email Clients Like Outlook? Trying to figure out a stream in banning one email client or another is no easy job. As soon as somebody rises up saying Outlook is bad, somebody else comes saying it's good and the other one is bad. Microsoft Great Plains Furniture & Fixtures - Implementation & Customization Highlights Microsoft Great Plains, former Great Plains Software Dynamics / eEnterprise was introduced in 1993 as first Microsoft Windows and Macintosh based graphical accounting/ERP application for Mid-Size businesses. Considering the history of furniture retailer and custom assembly lines - they showed up on the market about five decades ago and they have automated their business operations with Unix-based businesses in the late 1960th and earlier 1970th. Linux vs Windows Operating Systems With so many Microsoft Windows related viruses, errors, and other software problems, many average computer users are looking at alternative operating system platforms, such as Linux. It is important that users, frustrated with Windows, don't simply install a version of Llinux without knowing what they are in for. How to Evaluate Staffing Software If you are in the market for new staffing software, I suspect that one of the most daunting of tasks will be to sift through the many vendors that are now servicing the staffing industry.You will find many very qualified companies with good staffing software products. Microsoft Great Plains PM: Payables Management Microsoft Business Solutions Great Plains is marketed for mid-size companies as well as Navision (which has very good positions in Europe and emerging markets where it can be easily localized).Great Plains Payables Management (PM) module improves control over your payables. eConnect: eCommerce Development for Microsoft Great Plains Microsoft Business Solutions Great Plains has several options to enable web ordering. Traditionally Great Plains Dynamics/eEnterprise had eOrder - this is ASP pages based ordering application, enabling you to place or retrieve your Sales Order Processing (SOP) Sales Orders over the web. What is Spyware? Are You Being Watched? Find out why Spyware Removal from computers is important and how adware infestations affect your computer, your privacy and your family.Internet users worldwide spend several hours to several days each week browsing or searching the net. Getting Patched with Windows Service Pack Are you one of those people that keeps track of recalls, just in case one of your most recent purchases gets recalled? Well, some of us do, but many of us don't.For those of us who don't keep up with recalls in general, it's just as easy to be unaware of the fact that Microsoft Windows offers us this wonderful service called a Service Pack, which is essentially a recall service for Microsoft Windows. Instant Messenger Clients If you have been using the Internet for any amount of time you have probably used an instant messenger at some time or another. They allow you to talk to your friends, family and co-workers with the click of a button. Should I Use Windows Update? Should one use Windows Update?This topic has good and valid arguments on both sides. Some would say keeping your windows up to date is essential in keeping it free of the newest, worst viruses and the like.
|
|
Powered By: Free Work At Home Business Opportunity! |
| © 2006 |
