I have had many requests for an example on how to use BPEL to import data into Oracle, this is a common requirement and I thought that instead of just using a table and file, I will use Oracle APPS Api along with BPEL Oracle Apps Partner link for inserting of a Salary Proposal Update.

Pre-Requisites

You will need SOA suite setup on your machine and a connection to an Oracle Applications Instance, and a working copy of JDeveloper with BPEL 10.1.3.2 will suffice.

Assumptions.

Although this example is explained in as simple terms as possible, it is assumed you understand what BPEL is and basic understanding of Oracle Applications

I have already created a CSV file for this example that we will be using, the file although not perfectly aligned to a normal business process will give you a good idea on how the process works. The file itself contains rows of salary proposal information which we are going to use to run the API.

Step 1 – Create a new workspace in Oracle JDeveloper

· From the file menu click New.

· Select Applications and Select Application under items

· In Application name type, FileImportApplication

· Leave all other defaults

· Click Ok

· Click Cancel on the Create Project Screen

· From Applications Navigator Right click FileImportApplication

· Select New Project

· Under new projects window select BPEL Project

· Type FileUpload for the File name

· Change template to Empty BPEL Process

· Click Finish

We have now created a new application and Empty BPEL project.

We are now going to define a partner link that will read the CSV file from our file system

· If not already selected make sure that you select FileUpload.Bpel, so that the BPEL diagram moddler appears on your screen

· In component panel select services

· Drag and Drop a Partner Link Service to the right hand side of your BPEL diagram

· Once create partner window pops up select Define Adapter Service button(second last button from the right under the WSDL settings section.

· Select File Adapter from the adapter type screen

· Click Next

· In the service name type, PayrolFileReadService

· Click Next

· Select Read File as your Operation type

· Click Next

· Type in c:\temp\ in the directory for incoming files(you can select any folder you like here but just remember that this is where the service will be polling for new files.

· Click Next

· In the include files with Name Pattern type in payrol*.csv

· Click next

· Change polling frequency to 1 second

· Click next

· Click the Define Schema for native format button

· Skip the welcome page and select Delimited under create new native format from the choose type screen.

· Click next

· Under filename select browse and navigate to Payroll.csv file

· Change number of rows to skip to 1.

· Click next

· Select file contains multiple record instances and select multiple records are of single type.

· Click Next

· Type SalaryUpload for enter name of element containing multiple records

· And Salary for enter name for element that will represent one record

· Click Next

· Accept defaults on pg 5 and click next

· Change C1 through to C5 as follows

o C1 = Assignment_Id

o C2 = Business_Group_id

o C3 = Employee_Number

o C4 = Change_Date

o C5 = Change_Percentage

o C6 = Proposal_Reason

· Click Next

· Next

· Finish

· Click next on the Adapter Config page and then finish

You have now created a file listening service

Next we are going to create a receive activity

· Select Process Activities from the component pallet

· Drag and drop a Receive Activity onto you BPEL diagram

· Double click the receive activity

· Type receive File in the Name under general tab

· Select the flashlight next to the partner link field

· Select PayrolFileReadService from the partner links

· Click ok

· Click the create wand next Variable (this will create a default variable for the receive activity

· Click ok

· Make sure you select Create Instance on the receive activity and click ok

· Click file Save ALL

We have now create a receive activity to receive the file once picked up

Although not necessary in this scenario , but necessary in most business scenarios I will create a transformation activity.. so that we can transform data types, but first we need to create the Oracle Applications Service Partner

· Click on services in the component palette

· Drag and drop and Oracle Applications Partner link to the right side of your diagram

· Type in SalaryPropasalService in the Name field

· Click Next

· Select a defined apps connection(if one is not showing define one)

· Click Next

· The oracle applications module browser will now popup

· Type %PROPOSAL% and Select API’s and select search( I wrote a wrapper procedure, as the seeded proc contains Boolean values, which are not compatable with BPEL)

· Select Insert Salary Proposal

· Click OK

· Click next

· Click finish

· Click OK

The service has now been created. We need to now create a invoke method in order to be able to use the invoke method’s variables in the transformation activity.

· Select Process activities

· Drag a invoke activity below your receive activity

· In name type invokeProposalAPI

· Select the flashlight next to the Partner link field and select the salaryproposalapi partner link

· Select the wand next to input variable to generate a variable

· Click Ok

We are now going to create a transformation activity.

· Select Process Activities

· Drag a transform activity to below your receive file activity

· Double click the transform activity

· Click General Tab

· Type TransformDataForAPI for name

· Click transformation

· Select source variable as receiveFile_Read_InputVariable

· Select SalaryUpload as the source part

· Select target variable as invokeProposalAPI_salaryProposal

· Select Input Parameters as the Target Part

· Select the wand next to the transformation mapper file field

· A screen will appear that has source and targets

· Drag and drop the mappings AssignmentID > P_Assignment_Id, and the rest

· In my example I used a concat string function by dragging the function onto the map and then selecting employee-number and change percentage as the input variable

· Right click the map and select test

Ok

We have now selected a file from the file system transformed it and called a Apps API. One would still add error handling to this.

Deploy the BPEL File to BPEL Serv

· Right click FIleUpload project

· Select Deploy

· Deploy to default Server

To test this you can copy SalaryUpload.csv file into c:\temp

Comments (5)

Subscribe to this comment's feed

Write comment

Name

Email

Title

Comment

smaller | bigger

Subscribe via email (Registered users only)

Write the displayed characters

Add Comment