Implementing a Business Process in BPEL using Oracle BPEL 10g (beta 3) – GetSalesQuote leverages PL/SQL and WebServices

Oracle BPEL PM is a tool that allows us to design and publish WebServices. The design is done in BPEL, the industry standard for specifying Business Processes. BPEL stands for Business Process Execution Language. BPEL is a language that contains variables, calls to external services and an often underestimated set of programmatic logic such as conditional branching, looping and exception handling. In a recent post, I have published a BPEL based WebService that returned a SalesQuote for a certain product: Publishing PL/SQL “Services�? as WebService using Oracle BPEL. This business process was implemented through a PL/SQL package that resides in an Oracle database; the BPEL process was just a thin wrapper for this process. In this post, I will show how a we can reimplement a substantial part of the PL/SQL behind this WebService in BPEL. The BPEL process becomes much more than just a meaningless layer on top of a real process, it defines an important part of the process.

This automatically demonstrates a couple of interesting points: a BPEL process can easily bridge several technologies – in this case PL/SQL and an external WebService that are both invoked as PartnerLink; BPEL supports parallellism in its program flow allowing different program sections to be run simultaneously – in this case this provides a definite improvement over the original PL/SQL based service.

Note: this article is also a continuation of a series on PL/SQL and BPEL and as such it is preceded by:

• Publishing PL/SQL Based WebServices – this post discusses how a Web Service can be published based on a PL/SQL Package inside the Oracle Database using Oracle 10g JDeveloper.
• Consuming Web Services from PL/SQL – Part I: Using Java Stored Procedures
• Consuming Web Services from PL/SQL – Part II: A pure PL/SQL solution using UTL_HTTP (Oracle 9i or 10g).

The service that I want to publish and then redesign using BPEL is currently written in PL/SQL:

function get_quote
( p_product_id in number
, p_quantity  in number
, p_delivery_date in date
, p_currency in varchar2 default 'euro'  -- other values include: us, uk, full country name such as canada, france, hong kong etc.
) return number
is
  l_discount number(3); -- discount percentage
  l_date_discount number(3); -- discount percentage because of the delivery date (deliveries on Monday are cheaper)
  l_product_discount number(3); -- discount percentage - some products are heavily pushed
  l_standard_price number(10,2); -- price as calculated in euros without applying any discounts
  l_price number(10,2); -- price after applying discounts
  l_conversion_rate number(10,2); -- the factor to multiply the price in EUROs with to get the price in the specified currency
begin
  l_discount:= 10;
  -- Discounts Flow
  ---- subFlow dateDiscount
  -- monday deliveries get an extra 15% discount
  if app_utils.is_monday(p_delivery_date)
  then
      l_date_discount := 15;
  end if;
  ---- subFlow hotlistDiscount
  -- a special discount rate is used for products on the hotlist; if the product is not on the hotlist,
  -- then the standard discount rate applies that is used for all products ordered through the webservice
  if app_product.is_on_hotlist(p_product_id)
  then
    l_product_discount:= app_product.get_hotlist_product_discount;
  else
    l_product_discount:= app_product.get_standard_product_discount;
  end if;
  ---- end of subflows
  l_discount:= l_discount + l_date_discount + l_product_discount;
  ---- end of DiscountsFlow
  -- ProductPriceFlow
  l_standard_price:= p_quantity * app_product.get_product_price( p_product_id);
  -- end of ProductPriceFlow
  -- ConversionRateFlow
  l_conversion_rate:= app.currency.get_conversion_rate('eur', p_currency);
  -- end of ConversionRateFlow

  -- after all parallel flows have ended, normal execution resumes
  -- calculate the price as the standard_price times the discount-percentage
  l_price:= l_standard_price * (100 - l_discount)/100;
  -- price is calculated in Euros; the quote may hvan been requested in a different currency (p_currency)
  -- and must be converted in that case
  l_quote:= l_price * l_conversion_rate;
  return l_quote;
end get_quote;

To work this out, I need to implement these PL/SQL functions – preferably in three packages:

• is_monday (1)
• is_onhotlist (2)
• get_standard_product_discount (2)
• get_hotlist_product_discount (2)
• get_product_price (2)
• get_conversion_rate (3)

The app_sales.get_Quote service now looks as follows:

See getSalesQuotePackages.txtfor the implementation of the PL/SQL packages: app_utils, and app_product.

We can now use this Business Services from SQL or PL/SQL:

 select app_sales.get_quote( 9, 23, sysdate+13, 'us')
 from dual
/
APP_SALES.GET_QUOTE(9,23,SYSDATE+13,'US')
------------------------------------------
24928.78

Consuming a WebService in PL/SQL

The function get_conversion_rate should be re-implemented using a real WebService that provides currency conversion rates, for example: XMethods Currency Exchange Rates (see Tutorial for an example of using this webservice). To try this web service, see http://www.mindreef.net/tide/scopeit/start.do?referer=xmethods&url=http://www.xmethods.net/sd/2001/CurrencyExchangeService.wsdl

There seem to be three approaches to consuming WebServices from PL/SQL:

• Use the UTL_HTTP supplied package to communicate directly with the WebService and send and retrieve entire SOAP messages – see post Consuming Web Services from PL/SQL – Part II: A pure PL/SQL solution using UTL_HTTP (Oracle 9i or 10g)
• Develop Java Classes – preferably using the Web Service Wizards in JDeveloper – that consume the WebService and load these into the database wrapped with a PL/SQL package interface; this interface can be called like any normal PL/SQL procedure call; see post Consuming Web Services from PL/SQL – Part I: Using Java Stored Procedures
• Make use of the UTL_DBWS supplied package that allows generic WebService consumption from within PL/SQL without the need to create and load Java classes or write entire SOAP messages from within PL/SQL

For this article, we pick the first method, implementing the PL/SQL WebService call using UTL_HTTP, as it requires the smallest number of objects and the least usage of JDeveloper.

Publishing a PL/SQL Business as WebService

The function get_quote should at this stage be published as WebService. This is described in the post Publishing PL/SQL Based WebServices: we run a JDeveloper wizard that will generate the WebService: primarily a server side Java Stub Class that invokes the PL/SQL package and a WSDL file. We can deploy this WebService in any J2EE application server now and access it from a browser interface or through programmatic SOAP requests (http). Alternatively, we can use Oracle BPEL to publish this package as WebService, as demonstrated in the post Publishing PL/SQL “Services�? as WebService using Oracle BPEL. The application architecture after publish app_sales.get_quote through BPEL looks more or less as shown in this picture:

Redesigning and Reimplementing PL/SQL logic using Oracle BPEL Process Manager

In this step, I will re-implement the function get_quote using BPEL in the Oracle BPEL Process Manager. Note that in the PL/SQL code we can discern several flows that could be executed in parallel:

• DiscountsFlow where we determine in two subflows the possible dateDiscount (monday delivery) and the hotlistDiscount
• ProductPriceFlow where we determine the standard product price
• ConversionRateFlow where we determine the currency exchange rate that we will need to apply to price calculated in euros

These three flows can all be executed at at the same time, as they do not interfere nor act on the same variables.

After reimplementing the app_sales.get_quote function in BPEL, the new application architecture is like this:

The BPEL process is now much more than just a thin wrapper; all logic is bundled in the BPEL process. All external services are invoked from the BPEL process. The PL/SQL package app_currency, with its somewhat awkward call to the CurrencyExchangeRate WebService – through utl_http – is no longer required. We can easily exchange one of the services used by the BPEL process for another; a little redesign of the BPEL process or possibly only the partnerLink and we are back in business. Reimplementing a service, for example app_utils.is_monday, in another technology, for example Java, requires just a little tweaking of the BPEL process.

Developing the getQuote BPEL Process

I will use the Oracle BPEL Designer (beta 3) that runs inside Oracle 10g JDeveloper (10.1.2) for this. The steps are as follows:

• Create a new BPEL Project in Oracle BPEL Designer (JDeveloper or Eclipse, on our case JDeveloper 10.1.2)
• Add PartnerLink for all external services to be invoked from the BPEL Process: the Database PL/SQL Functions in the packages app_product and app_utils using the Adapter Wizard and the CurrencyExchangeRate WebService
• Define variables
• Create the BPEL Process steps
• Deploy and test the BPEL process

Create a new BPEL Project

Open Oracle BPEL Designer (or effective JDeveloper 10.1.2 with BPEL Designer installed), create a new Workspace (or open an existing Workspace), create a new BPEL Project (in the New Gallery, under General – Projects, there should be an option BPEL Process Project; if there is not, make sure that the Filter By elements is set to All Items rather than Available Items).

Create PartnerLinks for PL/SQL Functions

For each of the PL/SQL based services: app_product.get_product_price, app_product.is_on_hotlist, app_product.get_hotlist_product_discount, app_product.get_standard_product_discount and app_utils.is_monday, create a new PartnerLink, using the Adapter Wizard. See Publishing PL/SQL “Services�? as WebService using Oracle BPEL for an example of creating PartnerLinks for external services implemend by PL/SQL Functions. Note: we have had to change several of the PL/SQL functions before we could use them as PartnerLink: their return-type was BOOLEAN; however, the PL/SQL BOOLEAN is not supported by the PL/SQL Adapter in Oracle BPEL PM. Therefore, we changed the return type to VARCHAR2 and have the functions return Y instead of true and N instead of false.

I have also created a PartnerLink for the (External) WebService CurrencyExchangeRate. It’s WSDL can be found at http://www.mindreef.net/tide/scopeit/start.do?referer=xmethods&url=http://www.xmethods.net/sd/2001/CurrencyExchangeService.wsdl

Define Variables

Our BPEL process will use a number of variables. First of all there are the input and output variables of the entire BPEL process, the ones that are part of the invocation from the Client. As Sandor Nieuwenhuijs explained to me, I do not need to edit the WSDL process in order to specify the structure of these variables – as I previously thought and wrote in an earlier post. I need to define an XSD (XSML Schema Definition) that describes my input variable’s structure. Using the XML Schema Editor in JDeveloper, it is fairly easy to create the desired XSD. I set up my own namespace: xmlns:sq="http://www.amis.nl/ns/sales" for this Schema. I define two elements: SalesQuoteInput and SalesQuoteOutput, based respectively on the ComplexTypes SalesQuoteInputType and SalesQuoteOutputType.

Then I specify that the payload of the messageType GetSalesQuoteRequestMessage is based on the SalesQuoteInput element in that XSD. I do this by selecting GetSalesQuoteRequestMessage in the Structure Editor and clicking on the Edit icon. The Edit Message Type dialog is displayed. I select the payload message part and click on Edit. The Edit MessagePart dialog pops up. I click on the Browse for Element icon, and the Type Chooser is displayed. I click on the Import Schema File to load the Schema Definition I have created earlier in the XSD Editor:

The Type Chooser is updated with the new schema:

Now that the definitions in this Schema Definition have become available in my project, I can base the messagePart on the element sq:SalesQuoteInput:

I close this dialog to return to the Edit Message Type window:

After editing the Message Type, the inputVariable based on the payload of this Message Type looks as follows in the Structure Editor:

The GetSalesQuote.wsdl is updated after the import of the XSD and the redefinition of the messageType GetSalesQuoteResponseMessage and GetSalesQuoteRequestMessage:

<definitions
     name="GetSalesQuote"
     targetNamespace="http://xmlns.oracle.com/GetSalesQuote"
     xmlns="http://schemas.xmlsoap.org/wsdl/"
     xmlns:plnk="http://schemas.xmlsoap.org/ws/2003/05/partner-link/"
     xmlns:client="http://xmlns.oracle.com/GetSalesQuote"
     xmlns:sq="http://www.amis.nl/ns/sales"
    >
    <types>
        <schema xmlns="http://www.w3.org/2001/XMLSchema" xmlns:plnk="http://schemas.xmlsoap.org/ws/2003/05/partner-link/"
             xmlns:client="http://xmlns.oracle.com/GetSalesQuote" xmlns:sq="http://www.amis.nl/ns/sales">
            <import namespace="http://www.amis.nl/ns/sales" schemaLocation="GetSalesQuoteSchema.xsd"/>
        </schema>
    </types>
    <message name="GetSalesQuoteResponseMessage">
        <part name="payload" element="sq:SalesQuoteOutput"/>
    </message>
    <message name="GetSalesQuoteRequestMessage">
        <part name="payload" element="sq:SalesQuoteInput"/>
    </message>

It specifies the namespace http://www.amis.nl/ns/sales", imports the GetSalesQuoteSchema.xsd and defines the GetSalesQuoteResponseMessage and GetSalesQuoteRequestMessage in terms of the elements defined in the XSD file. The link to the XSD definitions is done through the sq: namespace specification.

In addition to the (indirect) redefinition of the inputVariable and outputVariable, I have created a number of global variables to carry values through the entire BPEL process. Note that these variables correspond with the local variables in my original PL/SQL code; I have even given them the same names to make this projection obvious. Another set of variables is implicitly (or automatically) created variables for the input and output associated with invocations of the Partner Links. The complete list of variables looks as follows:

Create BPEL process steps

Our BPEL process GetSalesQuote is very simple really: after we have received our input, we have to find out from our PartnerLinks: the price of the product, the product discount (standard or hotlist), the possible date discount in case we have been requested to deliver on a Monday and finally the exchange rate to convert the price in Euros to the requested currency.

The process steps are either assignments, service (partner link) invocations or switches (if-then-else constructs). We also use two types of container-elements: sequence and flow, the latter for parallellization of the former.

Service Invocation

Invocations are easily defined. Just create an activity of type Invocation. Drag and drop a line from the Invoke Activity to the PartnerLink. Then double click the Activity to edit the invocation details:

Here we invoke the IsOnHotlist partnerlink. I have had Oracle BPEL Designer auto-create the input and output variables for all invocations. In Assignments just before and just after the invocation, I prepare the input-variables or retrieve values from the output-variables.

Assignments

The most abundant type of activity is Assignment. Assignments are used to initialize variables from literal values, other variables or complex expressions involving literals, functions and other variables.

The very first assignment in our process initializes the variable l_discount with the value 10 (see how this corresponds with the PL/SQL statement l_discount:= 10;):

    <assign name="AssignInputParametersToLocalVars">
      <copy>
        <from expression="10"/>
        <to variable="l_discount"/>
      </copy>
    </assign>

A more interesting Assignment is the very last, where we mulitply l_product_price with p_quantity, take the discount percentage into account as well as the currency exchange rate.

    <assign name="AssignQuoteToOutputResult">
      <copy>
        <from expression="bpws:getVariableData('l_product_price')         *   bpws:getVariableData('inputVariable', 'payload','/ns13:SalesQuoteInput /ns13:P_QUANTITY')          *   (100-bpws:getVariableData('l_discount')) *    0.01          *   bpws:getVariableData(   'l_currency_exchange_rate')"/>
        <to variable="outputVariable" part="payload" query="/ns13:SalesQuoteOutput/ns13:SalesQuoteResult"/>
      </copy>
    </assign>

Oracle BPEL Designer contains an expression editor that is invoked from the Copy Rule Editor that is started from the Assignment Activity Editor.

Parallel Sequences or Flows

In the PL/SQL code we can discern several flows that could be executed in parallel:

• DiscountsFlow where we determine in two subflows the possible dateDiscount (monday delivery) and the hotlistDiscount
• ProductPriceFlow where we determine the standard product price
• ConversionRateFlow where we determine the currency exchange rate that we will need to apply to price calculated in euros

These three flows can all be executed at at the same time, as they do not interfere nor act on the same variables. In Oracle BPEL Designer, these parallel flows look like this:

The Discount Flow is assembled with two parallel subflows: one for the DateDiscount and one for the ProductDiscount. Both contain a switch that implements the if-then-else logic:

if isMonday
then
  date_discount:=15
else
  date_discount:=0;
end if;

and

if isOnHotList then
    l_product_discount:= get_hotlist_product_discount;
  else
    l_product_discount:= get_standard_product_discount;
  end if;

The highest level overview of the BPEL process – with all switches and flows collapsed – looks like this:

The BPEL process definition can be downloaded here: GetSalesQuote.bpel

Deploying the BPEL Process

Now that we have complete defined the BPEL process, we can deploy it the Oracle BPEL PM Engine we have already running. We can make use of the Deployment tool that is included with Oracle BPEL PM Designer:

The default domain’s password is bpel. When we deploy, the BPEL process is validated and compiled and packaged in a JAR that is then uploaded to the BPEL Engine.

Testing the BPEL Process from the Web Console

Now it is time to eat the pudding. Open the BPEL Console – remember: currently only supported in the Internet Explorer – to test the BPEL Process from the BPEL Console. Login to the default domain (default password bpel). The list of Processes should now include the just deployed getSalesQuote process:

I select the GetSalesQuote process and navigate to the Initiate tab-page. Here I can start execution of a BPEL Process Instance.

After we press Post XML Message, we get the following page:

After we have posted the request, we have to wait for a little while before we can find the results of executing this process instance. The Audit tab provides all details of the process: how long each step took, what data was fed into each step and what the results of executing each intermediate process step were. Note: in the Debug Tab we can find a detailed overview of process execution associated with the WebService’s WSDL-file. The Flow shows a visual representation of the process instance. Note: this is a sub-set of the Process Design we have created in Oracle BPEL Process Manager. Only those process steps and sequence that were actually executed in this instance are shown.

Every step in the Flow tab can be clicked on. A pop-up window opens and displays the relevant variables for the selected process step. For example for the Invoke step IsOnHotList we see this Activity Audit Trail:

The remainder of the flow is shown below. It is interesting to note that in this flow all switches have been consolidated: a switch contains several sequences during design; however, the audit trail for an actual process instance only contains the sequences that have been executed; under every switch, we will find only a single sequence. See for example the switch below the IsMonday invocation: only the Assign_NoDateDiscount sequence is displayed, the AssignDateDiscount step is not shown – because it was not executed in this instance! Apparently, this process had a delivery date that was not on a Monday.

To find out what the overall result was returned by this process, we click on the final process step callbackClient:

Apparently we have received a SalesQuote for $12987!

Download the Application

You can download a zip-file with the entire Oracle BPEL Designer (JDeveloper 10.1.2) Workspace here:GetSalesQuote.zip.

Note: I make us of a Database Connection WS_LOCAL that connects to a schema WS (password WS) in my local database. You will need to edit this connection to have it refer to your schema with the package app_product and app_utils.

Resources

Useful posts on the AMIS Technology Weblog on Oracle BPEL Process Manager:

• Book review: Business Process Execution Language – with a link to a free bookchapter (50 pages) on Oracle BPEL PM (release 2.x)
• More on BPEL – report from two weeks of investigations into Oracle BPEL PM 10g Beta 2
• First impression of Oracle jDeveloper BPEL Beta2
• BPEL-Soft – Oracle’s previous acquisition showcased during AMIS Query
• BPEL and Java by Matjaz JuricBasic step by step introduction of BPEL, SOA and the integration between BPEL processes and Java based services. We will concentrate particularly on the idea of extending BPEL, to be able to compose resources other than web services (for example EJBs, JMS, etc.) on one hand, and the possibility to mix BPEL and Java code on the other hand to open up some interesting new perspectives.