b2b edi is a very complex process but one thing to make it easier you can use Microsoft Fantastic Plains – Microsoft Company Answers accounting and ERP process, originally targeted to mid-size – now, with advancements and escalating reliability of its repository – Microsoft SQL Server, Fantastic Plains is alluring remedy for big corporation. Major companies commonly have buying and purchase processing automation by way of so-called Electronic Document Interchange or EDI. EDI was launched long time ago for UNIX systems and in most in the situations seems in the form of Header, Lines and Trailer of predefined fixed placement fields.
We would want to offer you – programmer, program developer, collection administrator the major clues on producing EDI formatted text from Microsoft Wonderful Plains databases. Please, note nonetheless that Fantastic Plains right here is obtained as the example, related strategy will job for other SQL dependent systems: Navision (SQL Databases or C/SIDE database), Microsoft RMS, Solomon too as Oracle and other non-Microsoft merchandise. During the instance of non-SQL technique, like as aged Excellent Plains Dynamics, ACCPAC on Pervasive SQL – IDE interface will require ADO/ODBC or Microsoft Gain access to programming.
One. Sample Question – The question below employs Forged construction to generate the consequence fixed length and fulfill the positioning. Plus the unionizing lets to make Header and Detail in 1 select statement. The following we are managing work Sales Files
select
instance
when b.LNITMSEQ=c.LNITMSEQ+1 and b.CMPNTSEQ=c.CMPNTSEQ+1 then CAST(‘Header’ as char(10))
otherwise CAST(‘Detail’ as char(10))
conclude
as FIELD0,
instance
when b.LNITMSEQ=c.LNITMSEQ+1 and b.CMPNTSEQ=c.CMPNTSEQ+1 then cast(a.CUSTNAME as char(65))
otherwise cast(cast(b.QTYDMGED as decimal(19,5)) as char(65))
conclusion
as FIELD01,
event
when b.LNITMSEQ=c.LNITMSEQ+1 and b.CMPNTSEQ=c.CMPNTSEQ+1 then CONVERT(char(51), a.DOCDATE, 101)
otherwise cast(b.ITEMDESC as char(51))
conclude
as FIELD03
–Additional fields go the following
from SOP10100 a subscribe to SOP10200 b on a.SOPTYPE=b.SOPTYPE plus a.SOPNUMBE=b.SOPNUMBE join
RM00101 d on a.CUSTNMBR=d.CUSTNMBR
subscribe to
(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 with a.SOPTYPE=c.SOPTYPE and also 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 enroll in SOP10106 udf with a.SOPTYPE=udf.SOPTYPE and a.SOPNUMBE=udf.SOPNUMBE
wherever a.ADDRESS3′Exported’ and
a.SOPTYPE=3 and upper(d.USERDEF2)=’YES’
get by a.SOPTYPE, a.SOPNUMBE, b.LNITMSEQ asc
2. Mark processed paperwork – for this purpose we use SOP10100.ADDRESS3 area – which was not utilized in Fantastic Plains Dynamics/eEnterprise variation 6.0:
update SOP10100 collection ADDRESS3=’Exported’ where SOPTYPE=3
A few. Communication with UNIX EDI Client or Server – every instance requires individual strategy. You might should assign the file directory, exposed on the UNIX technique or use aged DOS command to go the file, or you are able to have automatic email. Very good notion is usually to write communication use with your preferred programming language
4. Scheduled DTS Arrangement – you ought to perhaps make DTS package to accomplish each of the steps: call SQL Query and save it for a file, then call DOS command or easy EXE use – which does communicate with UNIX.
Find practical recommendations about the topic of one way links – make sure to read this web site. The times have come when proper info is really within one click, use this chance.
