EDI intergration and Price File template

You refer to EDI software - what exactly do you mean? EDI is generally known as "Electronic Data Interchange", which incorporate a multitude of concepts.

That said, you can typically build a Query that can be used to export to a Text file, if that's what you're after.

If not, please give us a bit more to go on.

we use GXS Desktop EDI to send and recieve electronic invoices and orders.
We send electronic invoices in a flat file format, the flat files are created from an access database and then imported into the GXS dekstop EDI software.

As i said we already use an access report to create text files and in turn use vba to create flat files from the text file, what im saying is should we try to create another report to create a price file in access? if so how? or look at an alternative solution?

Does this clarify things?, if not tell me what addiotional information you need to know that i've left out

I see ...

You could certainly build a report that you could export to text, which could then be imported to your software. However, you must be very careful to have the text file in the correct format. It appears GXS supports several different formats, so you'd have to choose which you would use.

As I said earlier, you can also do this with a Query, or a set of queries, if need be. Again, the required format would drive which of those methods you use.

Note also that Access can export as XML, if that helps.

Yes the format issue was part of my question as i said: "Ive tried to find a template that I could use in access but I havent found one that would work easily in access without having to do a lot of testing, and lots of manual manipulation which is likely to take quite some time to do" and i said "Does anyone have a price file template that could be used in access?."

unless theres some other software youd advise using to extract the required data from our tables and to send electonically?, as i think possibly creating a new report or query in access may take a long time to do, epecially when im not sure what format the Price files needs to be

i think it needs to be something like the below:
the problem is the spaces need to be in exactly the right place otherwise it will get rejected when sent.

STX=ANA:1+5000117000008:SUPPLIER NAME+5010011900016:SUPERMARKETS
LTD+090119:120000+005919+ +PRIHDR
MHD=1+PRIHDR:8
TYP=0150+COMPLETE-PRI
SDT=5000117000008:A0001+SUPPLIER NAME+ADDRESS1:ADD 2:ADD 4+394422930
SDT=5000117000008:A0001+SUPPLIER NAME+ADDRESS1:ADD 2:ADD 3:ADD 4:PC12 3PC+394422930
CDT=5010011900016+SUPERMARKETS LTD
FIL=1+1+090119
MTR=6
MHD=2+PRIINF:8
NOI=1+REPORT
PRN=1+1+1:1+5010012345678++10::CASE+TIN BOXES
PRI=1+1+1+090125
PRP=1+1+1+1+++:17500
PRN=1+2+1:1+5010012345876++10::CASE+TIN CUP
PRI=1+2+1+090125
PRP=1+2+1+1+++:07500
PRN=1+3+1:1+5010012345876:95010012345876++1::KG+CATCH WEIGHT
PRI=1+2+1+090125
PRP=1+2+1+1+++:10500
MTR=12

we currently use flattfile format  using the Tradacoms EDI standard

I know its possible to build a report that you could export to text, which could then be imported to your software....this is what we already do for our normal edi invoices. I think you have mis-understood my question. Did you read all of my original question?

Yes, I did read all of your original question. I don't have a template, nor could I easily find one on the web. My point is that if you attempt to build this and run into specific issues, we can certainly help with it, but we can't really build the entire template for you. There are certainly method that can be used to exactly and specifically format an outputted file, but at this point I'm still not sure exactly what format you require. I see where you've listed the "Tradacoms EDI Standard", but I have no idea what that is, and you didn't clarify this at all (nor did you provide a link where we could look up that format).

i dont expect you to build the entire template just ideally need some help on what to do...either try to build it myself or look at using some other software that already has this template setup.

im not an EDI expert thats why i need some help and i dont know the exact tradacoms standard either i just thought it might be helpful informaiton. some info is here: http://en.wikipedia.org/wiki/TRADACOMS
i dont have a link to more inoformation on this, if i did id provide you with that info.

Sorry, I know nothing about those standards ... thought perhaps I could help if you already had data in the correct format, but it appears you don't and aren't really sure exactly what that format should be.

Here's a little more about Tradacoms:
http://www.xmlconverters.com/standards/tradacoms/

Here's a product that could get your data from Access and convert it, perhaps:
http://www.stylusstudio.com/xml_product_index.html

hi Kevin,

I have built files for GXS, but the last time i did that was like 2001. I was able to find the specification for what I had to build on the GE (at that time) website and just build records with all that formatting garbage and insert the data from our Great Plains SQL database. We received 850 (PO) records and had to send PO Ack (855)? and Invoice (827)? records back over the VAN. I may even be able to find a copy of that database, but i'm not looking tonight.

Regards,

Jim

I regularly work with EDI.  It's a two part process; you need to build the flat file interface to your GXS Desktop software, then do the mapping there to produce the EDI document.
However, I have another suggestion; Data Sync.   Many customers are now requiring Data Sync capability (ie. Wal-mart, Walgreen's, etc).  A data sync pool is a catalog of your products and their attributes (including price) which is verified and published.  It's listing of the way you sell everything and it's published to all your customers that subscribe.  You can control who gets what.
  Most data sync products have a flat file interface capability, so you may still find that you have to do that in Access, but at least you'll be using something that can be used with other customers.  GXS offers this as a hosted solution.  You may want to check into this before proceeding
 
JimD.

Jim - ok if you get chance to have a look for that database that would be great.

Ok, thanks for that infomation JimD, ill ask GXS about that solution.

We are now thinking about using Freeways  Spectrum Professional solution EDI software but As the Spectrum software cannot access directly our Oracle Tables to read data. we could create XML or CSV Invoice files based on the data exported from the oracle tables into Freeway's Standard formats.

Then once the CSV or XML files are on the network they can then be imported into the Spectrum software and sent. The access database would then not need to be used.

Is this the only way of getting our data from our oracle tables so that it can be sent via EDI or is there a better method? is it possible to use some EDI software to directly link to oracle tables rather than having to find a way of creating CSV or XML files?

I'm not sure how to create the CV or xml files oracle tables





 

<<Is this the only way of getting our data from our oracle tables so that it can be sent via EDI or is there a better method? is it possible to use some EDI software to directly link to oracle tables rather than having to find a way of creating CSV or XML files?>>
In general, you 'll will almost always find that an "application file" is used between an EDI system and a application. Note I said "application". It doesn't matter so much that the datastore is Oracle, but that someone has already done the work for your specific application.
If your application is custom, then that work is up to you. If not, then you should contact the software vendor to see if there is an interface written.
JimD.

Our ERP software is called Icon360 which was created by a company called Maxima: http://www.maxima.co.uk/icon360

Theres other companies that also use this software but not a user base on such a large scale such as SAP. To clarify do you mean in some ERP software such as SAP there is already an interface or tool written that is able to Export the application files (into CSS or XML file) so that the file can then be imported into the EDI software (such as Freeways  Spectrum Professional solution EDI software)?

I've contacted Maxima and they said they would have to write someothing for us to export the data within the Oracle tables into CSV format using Freeways standard formatting standard. We can the import that into the Freeways Spectrum Professional software. They said its probably around 4-5 days work and would be chargeable. So i guess by that they mean there is no interface already available...is that what you mean by interface?

Is there no software tools available that can act as an interface to gather the data from oracle tables and put them into an application file such as CSV or is this issue normal for EDI?
Would we likely have the same issue with other software such as SAP or is this normal

Id appreciate your futher advice on this
Kevin

Below is a sample Freeway Sales Invoice example file
--------------------------------------------------------------------------
The following example shows a typical message compiled against the Sales Invoice Format. (Note: data is for example use only and does not reflect actual codes)

COMMADDMMYY HHMM HEAD DTL VAT TLR
HEAD,FREEWAY,JSHO,5010011090065,15,220101,6353242,220101,16,220101,16,220101,,End of Following Month,280201,,J SAINSBURY PLC,50 Lombard Wall,Anchor & Hope Lane,Charlton,SE7 7SH
DTL,JSCOS10,,1,,,100.00000,,,7.95000,0.00,0.00,7.95000,3,795.00,795.00,N,5010012416992,
VAT,3,1,795.00,795.00,,0.00
TLR,795.00,,,,0.00,0.00
HEAD,FREEWAY,JSHO,5010011090243,17,240101,78673532,240101,18,240101,18,240101,,End of Following Month,280201,,J SAINSBURY PLC,McGregor Cory,190 London Road,Hackbridge,SM6 7AB
DTL,JSCOS10V3,,10,,,187.00000,,,7.95000,0.00,0.00,7.95000,3,1486.65,1486.65,N,5010012416992,
DTL,JSCOS10V3,,10,,,203.00000,,,7.95000,0.00,0.00,7.95000,3,1613.85,1613.85,N,5010012416992,
DTL,JSCOS10V3,,10,,,10.00000,,,7.95000,0.00,0.00,7.95000,3,79.50,79.50,N,5010012416992,
VAT,3,3,3180.00,3180.00,,0.00
TLR,3180.00,,,,0.00,0.00
HEAD,FREEWAY,JSHO,5010011090036,18,240101,8963432,240101,19,240101,19,240101,,End of Following Month,280201,,J SAINSBURY PLC,Houndmills Road,Basingstoke,Hampshire,RG21 2XW
DTL,PTBAGESTIMA,,25,,,800.00000,,,4.50000,0.00,0.00,4.50000,3,3600.00,3600.00,N,,
DTL,PTMIDS,,25,,,200.00000,,,4.00000,0.00,0.00,4.00000,3,800.00,800.00,N,,
VAT,3,2,4400.00,4400.00,,0.00
TLR,4400.00,,,,0.00,0.00
HEAD,FREEWAY,JSHO,5010011090036,19,010201,2354346,010201,21,010201,21,010201,,End of Following Month,310301,,J SAINSBURY PLC,Houndmills Road,Basingstoke,Hampshire,RG21 2XW
DTL,JSCOS10V3,,10,,,800.00000,,,7.95000,0.00,0.00,7.95000,3,6360.00,6360.00,N,5010012416992,
VAT,3,1,6360.00,6360.00,,0.00
TLR,6360.00,,,,0.00,0.00

Kevin,
<<To clarify do you mean in some ERP software such as SAP there is already an interface or tool written that is able to Export the application files (into CSS or XML file) so that the file can then be imported into the EDI software (such as Freeways Spectrum Professional solution EDI software)?>>
Correct.
<<I've contacted Maxima and they said they would have to write someothing for us to export the data within the Oracle tables into CSV format using Freeways standard formatting standard. We can the import that into the Freeways Spectrum Professional software. They said its probably around 4-5 days work and would be chargeable. So i guess by that they mean there is no interface already available...is that what you mean by interface?>>
Yup and depending on the number of application files that would need to be generated (one for each type of EDI document you plan to implement; ie. one for 850 PO, one for 856 ASN, one for 810 invoice, etc.) the time to develop such an interface can be considerable.
A full EDI interface is going to take a lot longer then 4 or 5 days to develop. It's not so much the application files themselves, but hooking all that in to your existing app. For example, importing a 850 Purchase Order into your system. All the logic that is used when you type an order into your system needs to be applied to the EDI import. And then there are issues like data translation (customer sends their SKU, not yours or a UPC code), showing order specific messages, pricing variations, invalid SKUs, etc that need to all be dealt with.
However for doing something as simple as producing an 832 (Price Catalog) alone, that is very simple and straight forward. Should take no more then half a day to accomplish at most. You yourself could probably do that with an Access query and a export. I haven't looked at what goes into a 832, but you probably are going to need to do nothing more then an Export of a half a dozen fields or so out of your app.
<< Is there no software tools available that can act as an interface to gather the data from oracle tables and put them into an application file such as CSV or is this issue normal for EDI?>>
This is a normal EDI issue. Again, it's not just the application file itself that acts as a go between the EDI software and the application, but the logic to implement it all. It breaks out like this:


                                                                        |------- application interface ---------|
EDI Document <----------> EDI Software <--------> application files <----------> X <------------> application
The EDI software (ie. Sterling Gentran) is what translates an EDI Document to/from a application file. "X" is the code and /or import/export routines that takes data in the application file and translates it for the application.

<<Would we likely have the same issue with other software such as SAP or is this normal>>
Same issue with any software. Only difference would be is if someone had already written the interface and application file formats already for the app. Then you'd just buy that and have someone map the application data into the EDI documents for each trading partner using the EDI software.
JimD.

Ok thanks for this detailed explanation Jim thats really helped understand how it works much better.

If we implement the Freeway Spectrum Professional solution software thats meant to take care of all the formatting of the CSV or XML file into the right EDI format for each trading partner. Even if different trading partners require different fields of information the Spectrum software would be setup by freeway to only include the needed fields for each trading partner.

Also i think only the 1 CSV or XML file would be needed regardles of whether it was going to be used for an invoice or a price file or other application file because again the Freeway spectrum software is meant to extract only the required fileds for the needed EDI document type (invoice/price file ect). I guess the CSV/XML file would just need to contain all possible fields for all application file types.

Well this is what Freeway and Maxima seem to think how it works anyway, any comments?

Overall is sounds like implemeting EDI is not really straight foreward at all and seems a bit more complex than what it should be considering the amount of companies that seem to be forced into useing it mainly by the big supermarkets. would you agree with this?

<<If we implement the Freeway Spectrum Professional solution software thats meant to take care of all the formatting of the CSV or XML file into the right EDI format for each trading partner. Even if different trading partners require different fields of information the Spectrum software would be setup by freeway to only include the needed fields for each trading partner.>>
   Yes, that's what the software does, although just buying the software alone does not mean the mapping gets done for you.  Usally that's an add on fee per map.
<<Also i think only the 1 CSV or XML file would be needed regardles of whether it was going to be used for an invoice or a price file or other application file because again the Freeway spectrum software is meant to extract only the required fileds for the needed EDI document type (invoice/price file ect). I guess the CSV/XML file would just need to contain all possible fields for all application file types.

Well this is what Freeway and Maxima seem to think how it works anyway, any comments?>>
  That's usually not the case because the application file (the flat files) need different structures.  It's almost always one file format for each type of document.  For example, in a file for a 810 (Invoice), an app would need to pass this data:
Invoice header info
SAC (Service Allowance/Charge) for the entire invoice
Line Item Detail
SAC (Service Allowance/Charge) for the line
Line Item Detail
SAC (Service Allowance/Charge) for the line
Line Item Detail
SAC (Service Allowance/Charge) for the line
...
Totals

  each line being a seperate record type and doing that for each invoice passed.  Contrast that to an export for the 832 (Catalog Info):
SKU Data
  which would be one record per SKU.   I suppose you could do it all as one file type, but I have never seen it done that way and I think it would be very confusing.  Usually, the application file format follows the structure of a given EDI document.
 <<Overall is sounds like implemeting EDI is not really straight foreward at all and seems a bit more complex than what it should be considering the amount of companies that seem to be forced into useing it mainly by the big supermarkets. would you agree with this?>>
  Yes, it can be quite complex, which is why you have the cottage industry springing up of companies (Sterling, GXS, etc) offering web based EDI portals (where you type in data manually and they take it from there) and managed solutions (where they take care of the EDI side and you just provide the application interface).
  The first is meant to handle low volume accounts who have customers that have mandated the use of EDI but don't want to have anything to do with it.  The second is for customers with a little more volume, which cannot be handled with manual entry of data, but they don't want to have anything to do with the EDI side of things.
 JimD

Ok many thanks for clarifying the above, i see what you mean about different file structures i guess what freeway said was not quite right or i was mislead a bit.

Some final (i think) queries: We only have quite low volumes of EDI Orders and invoices. We usually send no more than EDI 10 invoices a day and recieve no more than 10 EDI Orders a day. So do you think we would be better of switching to a web based EDI portal for example?. I guess with a web based portal you cant import an CSV/XML file into it as its all manully inputing the invoice details? and invoices are just stored within the web portal....ie you cant transfer them onto your internal ERP system?

Overall you said  "producing an 832 (Price Catalog) alone, that is very simple and straight forward. Should take no more then half a day to accomplish at most. You yourself could probably do that with an Access query and a export. I haven't looked at what goes into a 832, but you probably are going to need to do nothing more then an Export of a half a dozen fields or so out of your app."   so do you think 1) we should just try to write something in access to create the price file ourselves? (which could take us quite some time to do even for just a price file but save some money) or 2) change to using freeways spectrum software and ask maxima to write some code to create the CSV appliaction files for both invoices and price files and then we import these files into the Spectrum software? and we could then get rid of the current access database we use and would probably get the job done quicker than us doing it internally.

I guess it could be difficult to advise but im interested in out of the options what would you most likely do in this situation?

I'm gussing if maxima wrote some code it would involve them writting SQL.

Thanks for all your help Jimd, thats great

Thanks all for all your help.

Below is the solution we have decided to go for in the end:
SOLUTION SUMMARY
Our ERP software provider will write some code to Integrate EDI Invoices by extracting data from our ERP software called Icon360 (which uses oracle tables) into Freeway's Standard XML File Format.
These XML files will then be imported into Freeway spectrum professtional software and Translated into the required EDI Invoice format for each of our Customers/ trading partners

Kevin