Pages

Sunday, December 23, 2007

From edifact to the database with mapforce & soa suite 11g

A customer of me wants to have a message handling system which can process dutch energy edifact messages and routes these messages to the right backoffice system. The first problem I had, how to process edifact messages to xml. Oracle has a b2b solution for this but this was not suitable for this case. Then we tried Altova mapforce. This is a mapping tool where you can map xml, web services , files , database and edifact. When you are finished in mapforce you can generate java, .net or xslt and integrate this code in your own application.
You can map edifact to the oracle database but we want to use an esb or a mediator so we don't have to connect our front with the backoffice systems. Our solution is that we want to map edifact to a soa suite web service. The first step is to customize the edifact message to the dutch enerqy standard. The mapforce edifact definitions are xml files so can easily drop segments, elements and add your own enumerations until your have the right edifact definitions.
Because edifact to wsdl mapping is a bit limited by mapforce we make a xsd which looks the same as the final tables definitions. Now we have a edifact to xml to wsdl mapping. This xsd can we use later for our routing / mediator component in the soa suite.

Altova has a great library with a lot of functions and you can make your own functions. It is very easy to use it. Now we are ready with the first part of mapforce let's look if the edifact -> xml mapping looks fine, you can click on the output window for the result (the try and error principle).
Now we go the 11g soasuite ( this works in soasuite 10.1.3 too). Here we make a new soa project. In this project we add the db adapter where we select the tables for the inserts. For this adapter we have to add the db connection to the soa suite db adapter, change the oc4j-ra.xml file, this file is located in system11.1.1.0.22.47.96\o.j2ee\embedded-oc4j\application-deployments\default\DbAdapter
The next step is to add a mediator, make sure you use the xsd which we made for mapforce as input and output and check "create composite service".

We have now the web service wsdl which altova needs for the latest step ( xml -> wsdl mapping). Let's finish the soa project by making a routing rule to the db adapter with a xslt mapping.
Now we go http://localhost:8988/soa-infra/ to download the wsdl of the mediator.
Altova can not use this wdsl directly, we have to change it a little bit ( soa suite generates a wdsl which import a another wsdl). Now we add the wsdl to mapforce and finish the mapping.

The last step is generate java code in mapforce and import this code to jdeveloper. You can integrate this in your application, start the console or the swing application (altova generates this for you.

6 comments:

  1. Hi,

    I have a scenario in which I have to take EDIFACT data as input in BPEL. If I am right in BPEL we need xml data to process it. I dont have much more idea on EDIFACT. So could you please give me an idea of how to do this in BPEL. In this blog you are explaining about Altova and Mapforce. Could you please explain this.


    And one more thing, if we are using OSB(MFL) for the EDIFACT data will it work fine.

    Thanks,
    Anju.

    ReplyDelete
  2. Hi, Anju.

    In OSB it should work because OSB can handle flat files ( MFL, has definitions for Edifact ) , see this link http://biemond.blogspot.com/2008/12/flat-file-to-xml-with-oracle-service.html

    for BPEL I think you have to make a service or a java call out which transforms text to xml.
    maybe this library can help you http://biemond.blogspot.com/2008/09/generate-edifact-message-from-xml.html

    thanks

    ReplyDelete
  3. Hi Edwin,

    Do you have any example to get a DB Adapter work on Web Logic Server 9.2 with BPEL PM.

    ReplyDelete
  4. Sorry I don't have a example. but I saw that in soa suite 11g and wls 11 has db resource adapter.

    after the soa suite 11g release the will release soa suite 10.1.3.5. this version will work on wls 10.3 or 11

    thanks

    ReplyDelete
  5. Hi Edwin,

    Could you point me to the source stating that 10.1.3.5 will be released after 11g?

    Thanks in advance,
    Peter Paul

    ReplyDelete
  6. Hi,

    I don't have a document. I heard this from indirect channels.

    Oracle VP said that soa suite 11g will be released in the 1st quarter of the Oracle fiscal year. < 2 months

    thanks Edwin

    ReplyDelete