Solved

EDI intergration and Price File template

Posted on 2009-06-29
26
1,986 Views
Last Modified: 2013-11-15
Hi,
Currently we use an access database that was written in house years ago (not by me) that creates flat files from an access report. These flat files then get imported into our EDI software which is: GXS Desktop EDI to send the flat files (invoices) we also receive orders via EDI from various customers.

The problem is one of our customers is now requesting that we send them a weekly price file via EDI (in version 8 price file format), and we dont have this setup in access. 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. The customer is requesting we send them price file information via EDI urgently asap.

I would really like some help on what would be the best solution to this?, should we try to setup a price file report within access? or look at an alternative solution such as changing to some other EDI software that might allow us to extract the required data from our backend ERP system more easily?.

Does anyone have a price file template that could be used in access?.

An EDI system that allows easy migration with external data sources such as Oracle or SQL tables could be useful. Is there some EDI software that you would recommend?

More detailed information:
The EDI access database is linked to one oracle table (this table is used by our ERP system) and some other linked access tables, within these tables contains information about customers, invoice information, and product information.

A report in access has a template setup with the required valid fields needed in a flat file. A macro runs every night which exports the report into a text file. The text file has a list of all the latest EDI invoice information.

The maco then runs some VBA which splits out the text file into separate flat files for each customer. Each morning these flat files get imported into the GXS desktop EDI software so that they can be sent.

Please see attached screenshots of the access tables and the report
EDI-Access-Tables.JPG
EDI-Report.JPG
0
Comment
Question by:kevin1983
  • 15
  • 5
  • 4
  • +1
26 Comments
 
LVL 84
ID: 24734976
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.
0
 

Author Comment

by:kevin1983
ID: 24735004
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
0
 
LVL 84
ID: 24735097
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.

0
 

Author Comment

by:kevin1983
ID: 24735130
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?."
0
 

Author Comment

by:kevin1983
ID: 24735159
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
0
 

Author Comment

by:kevin1983
ID: 24735189
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
0
 

Author Comment

by:kevin1983
ID: 24735222
we currently use flattfile format  using the Tradacoms EDI standard
0
 

Author Comment

by:kevin1983
ID: 24752366
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?
0
 
LVL 84
ID: 24752907
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).
0
 

Author Comment

by:kevin1983
ID: 24753341
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.


0
 
LVL 84
ID: 24753855
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
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 24760436
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

0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 57
ID: 24762571
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.
0
 

Author Comment

by:kevin1983
ID: 24762938
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.

0
 

Author Comment

by:kevin1983
ID: 24812636
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





 
0
 
LVL 57
ID: 24813999
<<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.
0
 

Author Comment

by:kevin1983
ID: 24814279
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

0
 

Author Comment

by:kevin1983
ID: 24814366
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
0
 
LVL 57
ID: 24814645
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.

0
 

Author Comment

by:kevin1983
ID: 24814832
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?
0
 
LVL 57
ID: 24815601
<<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
0
 

Author Comment

by:kevin1983
ID: 24815860
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.



0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 24816442
<<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?>>
  That's a hard call because I don't know anything about your business, future plans, current budget, how much you plan to continue into EDI, complexity of your invoices (one line item or ten?  Discounts or no?), what it's going to cost you for the different options, timeframes, etc.
  As for the web portals, some do allow for spreadsheet import and may not be a strictly "manual" operation.  You have to check.  Just keep in mind that with even that minimal amount of keying, you are prone to more errors that way.
<<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? >>
  Again, too hard to say from here.  The first thing that would need to be done is to look at what the customer is requesting in terms of data.  While the 832 is typically simple, it can turn into something fairly complex.  For example, transmistting Hazmat data, pricing structure breaks, specific types of handling and shipping, etc.
  If it was just this one customer and this one document and just simple data being requested, then I would certainly lean towards a web portal solution or for this specific case (producing item data), a Data Sync web portal (which I mentioned earlier) solution.  
  Even if you only start out this way, it might give you sufficent time to explore other options.
JimD.
0
 

Author Closing Comment

by:kevin1983
ID: 31597849
Thanks for all your help Jimd, thats great
0
 

Author Comment

by:kevin1983
ID: 24904929
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

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now