Using Wholesale Electronics to Get Organized

electronic

Microsoft Great Plains – Microsoft Business Solutions accounting and ERP system, originally targeted at mid-size – now, with advancements and increasing reliability of its database – Microsoft SQL Server, Great Plains is attractive solution for large business.

We’d really like to electronic contract manufacturing you – developer, software developer, database administrator the main clues on producing EDI formatted text in Microsoft Great Plains database. Please, note however Great Plains here is accepted as the example, similar approach will work for additional SQL based systems: Navision (SQL Database or C/SIDE database), Microsoft RMS, Solomon as well as Oracle and other non-Microsoft services and products. In the instance 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 structure to make the effect fixed-length and match the positioning. Plus the unionizing lets to produce Header and Detail in a select statement. Here We’re dealing with work Revenue Records

Pick

Case

Else CAST(‘Detail’ as char(10))

end

as FIELD0,

Case

Else cast(throw (b.QTYDMGED as decimal(19,5)) as char(65))

end

as FIELD01,

Case

Else throw (b.ITEMDESC as char(51))

end

as FIELD03

–Additional fields go here

Out of SOP10100 a combine SOP10200 b on a.SOPTYPE=b.SOPTYPE and a.SOPNUMBE=b.SOPNUMBE join

CUSTNMBR

Join

(pick SOPTYPE like SOPTYPE, SOPNUMBE as SOPNUMBE,

LNITMSEQ

as LNITMSEQ,

CMPNTSEQ

Union

Select SOPTYPE as SOPTYPE, SOPNUMBE as SOPNUMBE, MIN(LNITMSEQ)-1 as LNITMSEQ, MIN(CMPNTSEQ)-1 as CMPNTSEQ in SOP10200

Set by SOPTYPE, SOPNUMBE

) c 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

At which a.ADDRESS3’Exported’ along with

A.SOPTYPE=3 and upper(d.USERDEF2)=’YES’


2. Mark processed documents – therefore we use SOP10100.ADDRESS3 field – that was not used in Great Plains Dynamics/eEnterprise version 6.0:

Upgrade SOP10100 place ADDRESS3=’Exported’ where SOPTYPE=3


3. Communicating with UNIX EDI Client or Server – each case requires individual approach. You may need to assign the document directory, either confronted with the UNIX platform or utilize old DOS control to move the document, or you can get automatic email. Great thought is to compose communication application on your preferred programming language

4. Scheduled DTS Package – you should likely make DTS package to perform all the measures: telephone SQL Query and store it as a document, then telephone DOS control or easy EXE program – which does communicate with UNIX

Leave a Reply

Your email address will not be published. Required fields are marked *