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
|
|
|
25 Things Mapping Software Can Do For You
1. With mapping software you can create a report that tracks your retirement plan for you.2. Use mapping software to create an Executive Dashboard, which tracks the bottom line.3. Use mapping software to find the snowfall reports for your favorite ski resorts in Idaho.4. You can even use mapping software to explore an interactive map of India for school reports, business, or travel.5. Track your store incoming and outgoing inventory, including shipping information and warehouse location, mapping software.6. You can conveniently book your own airline seat, so you don't need to worry if you are a "window person" as opposed to an "aisle person."7. Know the who, what, when, where, and how of your department by using m...(related: Software)
Examining The Substance Of Studio Mx
To all web designers out there, this article is for you! I guess you already heard about Studio MX (I think so!) ? the ideal bundle for professional web designers, bringing together Dreamweaver MX for page design, Flash MX for animation and interactivity, and Fireworks MX for editing and optimizing graphics. With all these components, it certainly provides professional functionality for every aspect of web development.I must say that the integration between the individual products is generally very good. It begins with the interface which uses common elements such ...(related: Software)
Microsoft Great Plains Integrations ? Retail Management Sample
Microsoft Business Solutions is emerging as very attractive vendor for mid-size companies. The strength of its products is in their cross integration potential. This integration strategy will be the software development direction for this Microsoft subdivision. Meanwhile, as of right now ? we have to recommend just to stake on it and deal with custom or in-house developed integrat...(related: Software)
Oecomplete ? A Personal Information Manager
OEComplete is a utility for managing the personal information of the user. It is designed to be flexible and affordable even for the home user. OEComplete allows setting up appointments and tasks, and creates notes for easy reference. Recurrence, reminders and attachment enhance the competence in addition to import and export facility.For example, a weekly status meeting can be set to recur every...(related: Software)
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. This method offers fault tolerance (the ability of a system to continue to perform functions even when one or more hard disk drives have failed...(related: Software)
Great Plains Dexterity ? Microsoft Great Plains Customization Overview
Microsoft Business Solutions Great Plains, former Great Plains Software Dynamics and eEnterprise are Dexterity-written applications. Also small business line: Microsoft Small Business Manager or Small Business Financials is written in Dexterity and uses the same code base as Great Plains.The history of the Dexterity. Great Plains Dexterity - is proprietary programming language and technology, designed back to earlier 1990th with the goal to build platform independent graphical accounting package - Great Plains Dynamics. Dexterity itself is written in C (following popular those days hope - that C will provide platform independence). You can install Dexterity from Great Plains 7.5 CD #2. Obviously it requires a lot of learning / training, but it allows your custom piece b...(related: Software)
Quick Summary Of Basic And Common Linux Commands
There are many commands that are used in linux on a daily basis, ones that everyone should know just to get by. Like back in the days of DOS, you had to know how to work with the command line and how to navigate around. Learning new commands is always hard, especially when there are so many new ones that don't always seem to make sense in their names.cdChange directory - this command is essential for traversing folders and moving about your linux computer. Its quite straight forward, cd /where/you/want/togo or cd .. to go up a level. It's pretty basic, but be aware of absolute and relative paths. If you are currently in the folder /home/puppy/pictur...(related: Software)
Microsoft Crm: Implementation, Customization, Support ? Consultant Overview
Microsoft Business Solutions CRM is present several years on the US software market plus it makes advances in Europe....(related: Software)
Internet Faxing Service Review
The Internet is reshaping every form of communications medium, and faxing is no exception. The latest twist: Internet faxing services that let you send messages to any fax machine from any Web b...(related: Software)
Microsoft Great Plains Food Processing ? Implementation & Customization Highlights
Microsoft Great Plains might be considered as ERP platform to build your own custom solution upon or as the assembly place for the existing modules. Mi...(related: Software)
The Tools And Features Of Adobe Photoshop
Adobe Photoshop is one of the world's leading graphics editing programs, developed by Adobe Systems. Available on Mac O...(related: Software)
A Time-saving Programming Tactic That Doesn?t Work
Let's say that you have a software project that's under severe time pressure. Let's say that this deadline is so tight that you already know it will involve many late nights of black coffee and frenetic programming. What can you do to make this process go faster?I honestly don't know, since the correct answer will depend on one's individual circumsta...(related: Software)
Microsoft Great Plains: Government & Non-profit Organization ? Workflow Implementation
Usually workflow & messaging is realized in CRM and then transactions are just logged into Accounting/ERP/MRP. In the case of Microsoft Business Solutions products: Microsoft Great Plains, Navision, Solomon, Axapta the natural CRM choice would be Microsoft CRM. However typical CRM application targets Sales automation, wh...(related: Software)
site-map - Copyright © 2008 | Contact Webmaster | All Rights Reserved. | Software