Integration of Oracle EBS with Siebel CRM, Mediator in Oracle BPEL, Integration of Two DBs

In this post i will show how to use Mediator in BPEL Process and Transformation of attributes. This post is part of my previous posts series. You should also go through previous posts to fully understand the scenario. Here is the link of previous post
                             Inserting data using DB Adapter 
lets start on Mediator and Transformation in BPEL Process.

1) This is the composite so far, you will be here if you have followed my previous posts

2) Drag and Drop a Mediator component in Components section of your composite. A pop up will appear, perform the following changes

3) For Input click on the Search icon, front of Input field, In this section we will choose XSD Out schema of our first DB Adapter that is pulling data from Apps

4) Click OK your composite will look like following snap

5) Wired Mediator with your BPEL Process like below

6) Now Wired your Mediator with two DB Adapters that are inserting data into your DB like below

7) Double Click on Your Mediator component a .mplan file will open

8) Click on Filter Icon as show below. Then insert your OutParameter in your Expression as show below

9) Click OK and then click on Transform Icon as shown below and then create a new mapper file as shown below

10) Click OK you BPEL will look like below in Snap

11) Now Map your header table fields with your insert table fields. You can also assign hard code values by using different function as shown right.
For example my first attribute in a sequence just search sequence from right and drop in Mid section double click it and give the values like below. In data source field you have to give your data base connection string

 12) Drag and Drop for-each loop and wire it as shown below, we can have multiple records in our data set so it is required

13) Save all changes, go back to your BPEL process which will look like this. If your Mediator is displaying on Left side no worry, just right click on it and from Display, click move to opposite swim line as shown below

14) Drag and drop another Invoke service and drop it below AssignOutput, A pop up will appear, create an input parameter like below

15) Now double click on your AssignOutput activity to open mapping, because you have to assign Output parameter to input of newly created Invoke activity, like below

16) Your composite is ready to deploy. Just deploy it on server run it, it will fetch data from apps and will insert it into Siebel DB or your another DB, It will also show output on your EM console like below

 So in this series of Posts i have covered

1) Calling a Stored Procedure using DB Adapter which have out parameter.
2) Stored Procedure with Master and Detail Data
3) Invoking and Fetching Data using BPEL process and DB Adapter
4) Fetching data from One DB and Inserting it into another DB using DB Adapter
5) Integration of EBS with Siebel CRM
6) Using Mediator in BPEL Process
7) Transformation in BPEL Process

Read More

Inserting data using DB Adapter in BPEL SOA Suite

In this Post i am going to show how to insert data using DB Adapter in SOA Suite. This is a integration of Oracle Apps with Siebel CRM. First i will show you how to configure DB Adapter to insert data in it. I have two tables master and detail. I will show you only master table insertion wizard, however this can be done in combine as well. You can also create relationship between table in DB Adapter but i am not going into much detail. Please see following posts for basic development and pulling data from Apps DB. I have done this already so you should complete this. Now i will insert that apps data to Siebel CRM table.
1) Stored Procedure calling using DB Adapter
2) Invocation of Stored procedure using BPEL Process
 Lets start configuring DB Adapter for master table insertion data from Apps to Siebel DB

1) Drag and Drop a Database Adapter in you composite. Give it a name click next and create DB connection of DB where you want to insert this Data, i am inserting this data into Siebel DB. Make sure you should create a Data source in your Weblogic and you should give your JNDI name according to that otherwise it will not work.

2) Click next and select insert only as shown below

3) Click next and choose your schema and select table in which you want to insert data and click ok

4) Click next, I am not making any relationship, so click next and on Attribute filtering screen choose attribute, where you will insert data.

 5) Click next and click on deselect all as shown below to avoid any extra attribute if you do not want to include

6) Click next and then again next without any changes then finish . Your DB Adapter to insert data into Other DB is ready.

In next tutorial i will show how to use mediator to transform and insert data into DB using Mediator component.
Next Tutorial http://technasir.blogspot.com/2013/08/integration-of-oracle-ebs-with-siebel.html

Read More

Store Procedure invocation using BPEL Process in SOA Suite

In this post i will show you how to call and get Data using DB Adapter that we have developed previous post. You can check it out here Store Procedure with Out Parameter. In this Post we will develop a BPEL Process to Invoke this stored Procedure.

1) Drag and Drop a BPEL Process in component section. Following snaps shows how to do it. In input parameter select input parameter from XSD that is generated by your DB Adapter.

 2) For Output select output from xsd as shown below

3) After this click OK. Wire BPEL Process with DB Adapter. Your JDeveloper should look like this

4) Double click on EBSPricelistBPEL to open .bpel file. here you will apply little logic

5) Now drag and drop Invoke service from your right between receiveinput and replyoutput. Wire this with DB Adapter, A pop up will appear. give it a appropriate name and create input and output variable like this.

6) Click OK. Now drag and drop an Assign activity and below invoke, give it appropriate name. Your composite will look like this

7) Now Double Click on you AssignOut put activity. And apply the following mapping and then click ok

8) Now your composite is ready to deploy. Deploy it and then test using EM console it will show the Master and detail table data.

In my next post i will show you how to insert data from one Database to another and i will also show how to use mediator in BPEL and about transformation.

Read More

Calling Stored Procedure in BPEL Process, Stored Procedure with Out Parameters

In this post i will show how to call a stored procedure with out parameters using DB Adapter. I have a package which consist of a procedure, this procedure has two output parameters one output parameter returns master data while the other return detail data. Actually i have created a table type structure which that is used to return bulk of data. Following are steps to call store procedure in SOA Suite.

1) Drag and Drop a Database Adapter in external reference line and click next

2) Give the name of you DB Adapter and click next. In next step choose your database or create a new connection

3) The most important thing here is to create a data source in your Weblogic console. That data source you will use here in JNDI name. As you can see EBS is the name of my data source that i have configured on Weblogic, Without it your Webservice will not work

4) In next step check call to stored procedure or a function

5) From next step select your schema and stored procedure. My procedure has two output parameter.

6) Click next, next and then finish this wizard your JDeveloper will be like this

 Now your DB adapter is configured you can call this using BPEL Process and test it. The main thing is to set JNDI name, It should be configured correctly. Follow the following link if you want to call this DB Adapter using BPEL Process.
http://technasir.blogspot.com/2013/08/store-procedure-invocation-using-bpel.html

Read More