Software Information |
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: Unable to open RSS Feed $XMLfilename with error HTTP ERROR: 404, exiting |
RELATED ARTICLES
Is Software Tester a Most Infamous Person in a Software Project Team? The fact that a software tester is a most infamous person in a software project team has some grounds underneath it. This is because a software tester usually speaks of the bugs in the developer's code. Review of TikiWiki Content Management System TikiWiki is open source software - it is written in PHP, uses phpAdmin for mySQL database administration, and uses the Smarty Template Engine.Installation - Since our host installed the TikiWiki, I cannot comment on ease of installation. Microsoft Great Plains Implementation & Customization: Computer Parts Retailer Example Microsoft Great Plains fits to majority of horizontals and retail business is good fit as well. We'll consider the system, consisting from Microsoft Great Plains as Accounting, Inventory Pricing and Microsoft Retail Management System (RMS) Headquarters and Store Operations Manager. How to Make Attractive and Effective PowerPoint Presentations Microsoft PowerPoint has dramatically changed the way in which academic and business presentations are made. This article outlines few tips on making more effective and attractive PowerPoint presentations. Cisco Certification: Five Things To Do DURING Your CCNA Exam There are plenty of articles out there about how to prepare for the CCNA exam. However, there are also things you can do to increase your chances of success on exam day during the most important part of the entire process -- the time that you're actually taking the test. Microsoft Great Plains IV: Inventory Management Great Plains Inventory Management (IV) module gives your business a competitive edge. By achieving accurate visibility and effective control over inventory, and setting prices on a customer-by-customer basis, you can reduce operating costs and achieve the fast, efficient fulfillment that keeps customers coming back. Dont Choose Adobe When Working With PDF While Adobe is the most known maker of PDF tools, your business will be much better off, if you select third party tools to work with PDF documents.ABC Amber PDF converterPriced at eleven dollars, this tool may very well be the cheapest PDF software on the planet. Microsoft CRM Conversation Gateway: VoIP - Implementation & Customization Microsoft CRM is winning market share step-by-step from such the traditional CRM providers as Siebel, Saleslogix. In this article we'll show Microsoft Business Solutions CRM potential in the VoIP direction. Best Spyware Removers Finding the best spyware removers to detect and remove spyware and adware from your computer is much easier if you consider a few things before you make your purchase. Here are a few things to keep in mind when looking for a spyware protection program. Brief Notes About Mainframe Software Pricing We don't think about mainframe software pricing anymore, we just go out buy a server hardware/software "package" from Microsoft, but did you ever tink about how they got their ideas for the usage licence, it all started with IBM and the introduction of Cmos logic technology in 1994, simnce then mainframe software pricing has steadily became disproportion to the comparatively minute hardware costs, which has been stated to be "typically four to five times the hardware cost" in terms of the system budget.This trend has given rise to growing concerns over the direct results of current software pricing techniques which are leading to problems such as making smaller applications costly to operate on large systems. What is Groupware? Vince Lombardi once said that, "The achievements of an organization are the results of the combined effort of each individual." If this is the case, then what do we need to do in order to insure success in our companies? How can we combine the efforts of each individual as Mr. 3 Reasons Why Medical Billing Software is Leading the Way Since technology changes so quickly, it is hard to begin by saying "in the old days . . Microsoft Great Plains: Dexterity vs. eConnect - FAQ Microsoft Business Solutions Great Plains, former Great Plains Software Dynamics/eEnterprise was originally written in IDE and programming language - Great Plains Dexterity. Currently Microsoft is on the way of transforming its ERP applications: Great Plains, Navision, Solomon, Axapta and MS CRM to be better integrated and fit into the future of its . Alien Intruders! You probably didn't casually invite, or extend a formal attendance requestto, these undesirables known as viruses.Regardless of your opinions, such cyber-nomads may call on you, complete andactive, with their destructive payloads. Programming Language Migration Path While I was preparing some personal background information for a potential client, I was reviewing all the programming languages that I have had experience with. I list languages that I'm most experienced with on my resume. Reduce Pop-ups and Annoying Ads There is many things more frustrating than surfing a website only to have your screen suddenly full of pop-up advertising or a cascade of new windows opening. There are so many intrusive kinds of advertising on websites that there must be a way to deal with them. Bill of Lading - Custom Reporting for Microsoft Great Plains - Overview for Consultant Bill of Lading is required report for Logistics and Freight Forwarding companies. If you are looking at Freight Forwarding software, targeted to automate transportation business - Bill of Lading with multiple custom forms should be present there. Free Microsoft Word Online Training Tutorial Resources Microsoft Word is one of the most popular office applications that provide many features such as word processing, web publishing and database creation. Tapping into these Word resources, however, is not always easy and straightforward, leaving users stumped and puzzled. Keep Your Software Simple! A Review of EditPlus I like my software simple. If it's too complex or difficult to figure out, I don't use it. Twelve Things You Should Know to Save on Computer Software Do you want to get quality software at a reasonable price? Whether you work at home or just need good software for your personal computer, you are faced with an almost endless selection of computer software. Sorting through the options can sometimes be a major task. |
home | site map | contact us |