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

·         Select Human Resources

·         Select Human Resources personelle

·         Select Salary Proposal

·         Select PLSQL

·         Select Maintain Proposal

·         Select Insert Salary Proposal

·         Click OK

·         Click next

·         Click finish

A wrapper procedure will now be generated from the selected API

George Goosen

Add comment