A Query In A Program Language Can Be Used To EDI: Electronic Document Interchange for Microsoft Great Plains – Overview for Software Developer/Pr

You are searching about A Query In A Program Language Can Be Used To, today we will share with you article about A Query In A Program Language Can Be Used To was compiled and edited by our team from many sources on the internet. Hope this article on the topic A Query In A Program Language Can Be Used To is useful to you.

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

Microsoft Great Plains – Microsoft Business Solutions accounting and ERP system, originally designed for medium-sized – now with advances and increased database reliability – Microsoft SQL Server, Great Plains is an attractive solution for large corporations. Large companies typically have purchasing and order processing automation through Electronic Document Interchange, or EDI. EDI was introduced a long time ago for UNIX systems and most often appears in the form of predefined fixed position fields Header, Lines and Trailers.

We’d like to give you – the programmer, software developer, database administrator – basic tips on creating EDI-formatted text from the Microsoft Great Plains database. Note that Great Plains is used as an example here, but a 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 a non-SQL system such as legacy Great Plains Dynamics, ACCPAC will include ADO/ODBC or Microsoft Access programming in the Pervasive SQL – IDE interface.

1. Sample query – The following query uses the CAST construct to make the result a fixed length and respond to the placement. Moreover, the merge allows you to create Header and Detail in one select statement. Here we deal with 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. Note the processed documents – this is why we use the SOP10100.ADDRESS3 field, which is 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 an individual approach. You may need to specify a file directory exposed to a UNIX system, or use an old DOS command to transfer a file, or have automatic email. A good idea is to write a communication program in your favorite programming language

4. Scheduled DTS Package – you should probably create a DTS package to perform all the steps: call SQL Query and save it as a file, then call a DOS command or a simple EXE program that communicates with UNIX.

Happy programming! if you want us to do the work – call 1-866-528-0577! [email protected]

Video about A Query In A Program Language Can Be Used To

You can see more content about A Query In A Program Language Can Be Used To on our youtube channel: Click Here

Question about A Query In A Program Language Can Be Used To

If you have any questions about A Query In A Program Language Can Be Used To, please let us know, all your questions or suggestions will help us improve in the following articles!

The article A Query In A Program Language Can Be Used To was compiled by me and my team from many sources. If you find the article A Query In A Program Language Can Be Used To helpful to you, please support the team Like or Share!

Rate Articles A Query In A Program Language Can Be Used To

Rate: 4-5 stars
Ratings: 4174
Views: 88518865

Search keywords A Query In A Program Language Can Be Used To

A Query In A Program Language Can Be Used To
way A Query In A Program Language Can Be Used To
tutorial A Query In A Program Language Can Be Used To
A Query In A Program Language Can Be Used To free
#EDI #Electronic #Document #Interchange #Microsoft #Great #Plains #Overview #Software #DeveloperPr

Source: https://ezinearticles.com/?EDI:-Electronic-Document-Interchange-for-Microsoft-Great-Plains—Overview-for-Software-Developer/Pr&id=16018

Related Posts

default-image-feature

A Psychology Specializing In Language And Language Development Is A An Overview of Educational Psychology for Future Teachers

You are searching about A Psychology Specializing In Language And Language Development Is A, today we will share with you article about A Psychology Specializing In Language…

default-image-feature

A Proposed Law Drafted In Legal Language Is Called A Avoiding Court Ordered Dissolution of Business

You are searching about A Proposed Law Drafted In Legal Language Is Called A, today we will share with you article about A Proposed Law Drafted In…

default-image-feature

A Proper Way To Create A Website Using Programming Languages 5 Reasons To Use A Content Management System For Your Website

You are searching about A Proper Way To Create A Website Using Programming Languages, today we will share with you article about A Proper Way To Create…

default-image-feature

A Programming Language That Can Simulate Another Programming Language Is Intelligence Way is Artificial

You are searching about A Programming Language That Can Simulate Another Programming Language Is, today we will share with you article about A Programming Language That Can…

default-image-feature

A Programmer Uses In Selecting The Language For A Project What Is The Best Platform to Develop Web Applications?

You are searching about A Programmer Uses In Selecting The Language For A Project, today we will share with you article about A Programmer Uses In Selecting…

default-image-feature

A Program Written In Low Level Language Is Faster Why The McDonnell Douglas MD-11

You are searching about A Program Written In Low Level Language Is Faster Why, today we will share with you article about A Program Written In Low…