Skip Headers

Oracle Application Server Adapter for Oracle Applications User's Guide
10g (10.1.3.5.0)
Part Number E14293-01		Go to Table of Contents
Contents 		Go to previous page
Previous 		Go to next page
Next

Using PL/SQL APIs for BPEL Process Integration

This chapter covers the following topics:

Overview of PL/SQL APIs

OracleAS Adapter for Oracle Applications uses PL/SQL application programming interfaces (APIs) to insert and update data in Oracle Applications. APIs are stored procedures that enable you to insert and update data in Oracle Applications. Additionally, you can use PL/SQL APIs to retrieve data. For example, by using PL/SQL APIs, you can insert a customer record in Oracle Applications.

Note: For more information about PL/SQL procedure limitations, refer to Oracle Application Server Adapters for Files, FTP, Databases, and Enterprise Messaging User’s Guide.

Design-Time Tasks for PL/SQL APIs

This section describes how to configure the OracleAS Adapter for Oracle Applications to use PL/SQL APIs. It describes the tasks required to configure OracleAS Adapter for Oracle Applications using the Adapter Configuration Wizard in Oracle JDeveloper.

Prerequisites to Configure PL/SQL APIs

OracleAS Adapter for Oracle Applications is deployed using the BPEL Process Manager (PM) in Oracle JDeveloper. The BPEL PM creates the WSDL interfaces for the API.

Populating Applications Context Header Variables

You need to populate certain variables in the SOA Suite to provide context information required in an API transaction in order for an Oracle Applications user that has sufficient privileges to run the program.

The context is set taking into account the values passed for the header parameters including Username, Responsibility, Responsibility Application, Security Group, and NLS Language. If the values for the new header parameters Responsibility Application, Security Group, and NLS Language are not passed, context information will be determined based on Username and Responsibility.

The default value for the Username is SYSADMIN, the default value for Responsibility is SYSTEM ADMINISTRATOR, the default Security Group Key is STANDARD, and the default NLS Language is US.

You can change the default values specified in the generated WSDL. This is a static way of changing the context information. These values would apply to all invocations of the deployed business process. However, if you need to provide different context information for different invocations of the business process, then you can dynamically populate the header values. The context information can be specified by configuring an Assign activity before the Invoke activity in the BPEL PM.

Detailed instructions on how to assign header variables, see Design-Time Tasks for Assigning Header Variables.

Populating Default Values for Record Types

Certain PL/SQL APIs exposed from Oracle E-Business Suite take record types as input. Such APIs expect default values to be populated for parameters within these record types for successful execution.

The default values are FND_API.G_MISS_CHAR for characters, FND_API.G_MISS_DATE for dates, and FND_API.G_MISS_NUM for numbers. OracleAS Adapter for Oracle Applications can default these values when the parameters within the record type are passed as nil values, for example, as shown below:

<PRICE_LIST_REC>
<ATTRIBUTE1 xsi:nil="true"/>
<ATTRIBUTE2 xsi:nil="true"/>
<ATTRIBUTE3 xsi:nil="true"/>
...
</PRICE_LIST_REC>

This can be achieved with the help of a function in a Transform activity, or by directly passing the XML input with nil values and then assigning them to the record types within an Assign activity.

Following is a list of the procedures required to accomplish the design-time tasks.

  1. Create a new BPEL project

  2. Add a partner link

  3. Define wrapper APIs

  4. Declare parameters with a DEFAULT clause

  5. Configure the Invoke Activity

  6. Configure the Assign Activity

Creating a New BPEL Project

To create a new BPEL project

  1. Open BPEL Designer.

  2. From the File menu, select New. The New Gallery dialog box appears.

  3. Select All Items from the Filter By list. This displays a list of available categories.

  4. Expand the General node, and then select Projects.

  5. Select BPEL Process Project from the Items list.

    Creating a New BPEL Process Project

    the picture is described in the document text

  6. Click OK. The BPEL Project Creation Wizard - Project Settings dialog box appears.

  7. In the Name field, enter a descriptive name. For example, enter UpdatePO.

    Keep the default selection Use Default Project Settings unchanged.

  8. Keep the default selection Template as the Type field. Select Asynchronous BPEL Process as the BPEL process type.

    Specifying a Name for the New BPEL Process Project

    the picture is described in the document text

  9. Click OK.

    A new asynchronous BPEL process is created with the receiveInput and callbackClient activities.

    The required source files including bpel.xml, UpdatePO.bpel, and UpdatePO.wsdl are also generated.

    New BPEL Process

    the picture is described in the document text

Adding a Partner Link

This section describes how to add a partner link to your BPEL process. A BPEL partner link defines the link name, type, and the role of the BPEL process that interacts with the partner service.

To add a partner link

  1. In JDeveloper BPEL Designer, click Services in the Component palette.

    Drag and drop Oracle Applications icon into the border area of the process diagram.

    The Adapter Configuration Wizard appears.

  2. Click Next. The Service Name dialog box appears.

  3. Enter the following information:

    1. In the Service Name field, enter a service name, such as UpdatePO.

    2. In the Description field, enter a description for the service. This is an optional field. The Service Name dialog box appears.

      Specifying the Service Name and Description

      the picture is described in the document text

  4. Click Next. The Service Connection dialog box appears.

  5. You can perform either one of the following options for your database connection:

    Note: You need to connect to the database where Oracle E-Business Suite is running.

  6. Once you have created a new connection or selected an existing connection, you can add a PL/SQL API by browsing through the list of APIs available in Oracle E-Business Suite.

  7. Click Next.

    For Oracle E-Business Suite Release 12:

    If you are connecting to Oracle E-Business Suite Release 12, then the IREP File not present dialog box appears indicating that Adapter for Oracle Applications could not find the Oracle Integration Repository data file corresponding to the database you are connecting in your workspace. Absence of the data file would make browsing or searching of Integration Repository tree considerably slow. You can choose to extract the data file and create a local copy of the Integration Repository data file. Once it is created successfully, Adapter for Oracle Applications will pick it up automatically next time and retrieve data from your local Integration Repository.

    You can select one of the following options:

    For Oracle E-Business Suite pre-Release 11.5.10:

    If you are connecting to a pre-11.5.10 Oracle E-Business Suite instance, you must select the interface type in the Adapter Configuration Wizard. Select Tables/Views/APIs/Concurrent Programs to proceed.

    Click Add in the Application Interface dialog box to open the Oracle Applications Module Browser.

  8. The Oracle Applications Module Browser combines interface data from Oracle Integration Repository with information about the additional interfaces supported by Adapter for Oracle Applications, organized in a tree hierarchy.

    Specifying the PL/SQL API

    the picture is described in the document text

    Note: The Oracle Applications Module Browser includes the various product families that are available in Oracle Applications. Each product family contains the individual products. Each product contains the business entities associated with the product. Business entities contain the various application modules that are exposed for integration. These modules are grouped according to the interface they provide PL/SQL APIs can be found under the PL/SQL category.

    Navigate to Supplier Chain Management (SCM_PF) > Internet Procurement Enterprise Connector (ITG) >Purchase Blanket Release > PLSQL > Purchase Order Change APIs > UPDATE_PO. The signature of that given API appears.

    Note: You can use the Search option to quickly find the required objects. Enter the required database object name in the Object Name field and select APIs. Click Search to retrieve the required database objects. When searching for a PL/SQL API, enter the package name, and not the procedure name. In contrast, when using the DB Adapter Wizard, the user must enter the name of the PL/SQL API while searching.

    Adding the PL/SQL API

    the picture is described in the document text

  9. Click Next and then click Finish to complete the process of configuring Adapter for Oracle Applications. The wizard generates the WSDL file corresponding to the XML schema. This WSDL file is now available for the partner link.

    Create Partner Link

    the picture is described in the document text

  10. Click Apply and OK. The partner link is created with the required WSDL settings.

Defining Wrapper APIs

The Adapter Configuration Wizard generates a wrapper API when a PL/SQL API has arguments of data types, such as PL/SQL Boolean, PL/SQL Table, or PL/SQL Record. For generating the wrapper API, Oracle JPublisher is automatically invoked in the background. When a wrapper API is created, besides the WSDL and XSD files, two SQL files are created: one for creating the wrapper API and necessary datatypes, and another for deleting it. The two SQL files are saved in the same directory where the WSDL and XSD files are stored, and are available in the Project view.

Following is a sample code of a PL/SQL API that would require a wrapper to be generated:

package pkg is
    type rec is record (...);
    type tbl is table of .. index by ..;
    procedure proc(r rec, t tbl, b boolean);
end;

If the preceding PL/SQL API is selected in the wizard, then a wrapper API will be created and loaded into the database. This wrapper API will be used instead of the originally selected PL/SQL API. For this reason, the content of the WSDL and XSD files represent the wrapper procedure, not the procedure originally selected.

The following are the types that will be created for the wrapper API:

The generated SQL file that creates the wrapper API also creates the required schema objects. The types of the wrapper API's parameters will be those of the new schema object types. The wrapper package will contain conversion APIs to convert between the base PL/SQL type and the new schema object types.

Note: The SQLJUTL package contains the BOOL2INT and INT2BOOL conversion functions used for PL/SQL BOOLEAN arguments whose data types have been changed to INTEGER.

The wrapper API is created in a package. This package is named XX_BPEL_servicename.servicename is the name of the service that you entered in step 6 of Adding a Partner Link. If this package already exists, then the wizard prompts for a different package name, or to select a checkbox, to overwrite the existing package. Overwriting an existing package causes all PL/SQL APIs in the specified package to be lost. When the wizard creates a package for the wrapper, only one API, that is, the wrapper API, is contained in it.

Note: Despite specifying to overwrite an existing package, if the wrapper API already exists in the specified package, the wizard will not re-create the wrapper API, as it would take some time. This means no SQL files will be created, Oracle JPublisher will not be run, and the WSDL and XSD files will be for the existing wrapper API. The Finish page of the wizard will indicate that these actions will take place, but it is possible that they will not, depending on whether the wrapper already exists.

Entering a Package Name for the Wrapper API

the picture is described in the document text

Note: The package name for the wrapper has a limit of 30 characters, and the wrapper API name has a limit of 29 characters. Thus, if the package name or the wrapper API name is longer than the maximum limit, it will be truncated accordingly.

The name of the wrapper API depends on whether the PL/SQL API that was originally selected is in a package or not. If the original PL/SQL API is a root-level API, that is, it does not belong in a package, then the name of the wrapper API will be, TOPLEVEL$original_api_name. If the originally selected PL/SQL API is in a package, then the name of the wrapper API will be original_package_name$original_api_name.

Wrapper APIs follow the naming convention of Oracle JPublisher. For example, if the original PL/SQL API was called CREATE_EMPLOYEE and was a root-level API, then the wrapper API would be named TOPLEVEL$CREATE_EMPLOYEE. If the original PL/SQL API is in a package called EMPLOYEE, then the wrapper API would be named EMPLOYEE$CREATE_EMPLOYEE.

The Finish page of the wizard is different when a wrapper API needs to be created. The Finish page informs you that a wrapper API is needed, and in addition, lists the name of the wrapper package, wrapper API, and the SQL files that will be created.

Note: When a wrapper API needs to be created, it may take a while before the wizard completes. However, the processing time for subsequent PL/SQL APIs in the same package would be much shorter.

The Finish Page

the picture is described in the document text

Note: The REF CURSOR type is not supported out of the box. However, for detailed steps to generate an adapter service for a PL/SQL API which takes REF CURSOR type, see the section "Support for REF CURSOR" in Oracle BPEL Process Manager Developer's Guide on OTN.

Overloaded APIs are not supported in release 11.5.10.

Declaring Parameters with a DEFAULT Clause

You can declare parameters of a stored procedure with a DEFAULT clause, so that when you invoke the procedure without that parameter, BPEL Process Manager will supply a default value for that parameter. For example:

PROCEDURE addEmployee (name VARCHAR2, country VARCHAR2 DEFAULT ‘US')

This procedure can be invoked in the following two ways:

Omitting Parameters With a DEFAULT Clause

You can omit elements for parameters with default values in the instance XML. The procedure will be invoked without these parameters, allowing their default values to be used, as shown in the following example of input and runtime invocation.

Input

<db:InputParameters xmlns:db="…">
    <name>John Smith</name>
</db:InputParameters>

Runtime Invocation

BEGIN addEmployee (name=>?); END; // country => 'US'

If the input includes a value for the defaulted parameter, the value in the input will be used, rather than the default, as follows:

Input

<db:InputParameters xmlns:db="…">
    <name>John Smith</name>
    <country>France</country>
</db:InputParameters>

Runtime Invocation

BEGIN addEmployee (name=>?, country=>?); END; // country => 'France'

Omitting Parameters Without a DEFAULT Clause

The element in the XSD for parameters with a default clause is annotated with a special tag to indicate that the parameter has a default clause, as shown in the following example.

<element name="country" … db:default="true" …/>

This new functionality allows elements for parameters without a default clause also to be omitted in the instance XML. In these cases, the parameter is still included in the invocation of the stored procedure. A value of NULL is bound by default. Following is an example of a declaration where neither parameter has a DEFAULT clause:

PROCEDURE addEmployee (name VARCHAR2, country VARCHAR2)

In BPEL Process Manager release 10.1.2, elements for both parameters were required in the instance XML. If an element was omitted, it was presumed to have a DEFAULT clause, so the parameter was not included in the invocation of the procedure. In this case, the missing parameter resulted in a PL/SQL error stating that an incorrect number of arguments was passed to the procedure.

In the current BPEL Process Manager release, the missing parameter will be included in the invocation of the procedure. A NULL value will be bound, as shown in the following example:

Input

<db:InputParameters xmlns:db="…">
    <name>John Smith</name>
</db:InputParameters>

Runtime Invocation

BEGIN addEmployee (name =>?, country=>?); END; // country => NULL

Even though the element for country was not provided in the instance XML, it still appears in the call to the procedure. In this case, country will be NULL.

DEFAULT Clause Handling in Wrapper Procedures

If a procedure contains a special type requiring a wrapper to be generated, the default clauses on any of the original parameters will not be carried over to the wrapper, as shown in the following example:

PROCEDURE needsWrapper(isTrue BOOLEAN, value NUMBER DEFAULT 0)

Assuming that the procedure in the preceding example was defined at the top level, outside of a package, the generated wrapper will appear, as shown in the following example:

TOPLEVEL$NEEDSWRAPPER (isTrue INTEGER, value NUMBER)

In the preceding example, the BOOLEAN type has been replaced by INTEGER. The default clause on the value parameter is missing. In the current release, parameters of generated wrapper procedures will never have a default clause, even if these parameters did in the original procedure. If the element is missing in the instance XML, instead of defaulting to 0, then the value of the parameter will be NULL, as shown in the following example:

Input

<db:InputParameters xmlns:db="…">
    <isTrue>1</isTrue>
</db:InputParameters>

Runtime Invocation

BEGIN TOPLEVEL$NEEDSWRAPPER (isTrue =>?, value =>?); END; // value => NULL

To fix this, you can edit the generated SQL file, restoring the default clauses. You should then, run the SQL file to reload the wrapper definitions into the database schema. In addition, you should modify the generated XSD.

Following are the steps to fix the default clause with the wrapper generated for needsWrapper():

  1. Change the signature in the following manner in the generated wrapper SQL file, from:

    TOPLEVEL$NEEDSWRAPPER (isTrue INTEGER, value NUMBER)

    To:

    TOPLEVEL$NEEDSWRAPPER (isTrue INTEGER, value NUMBER DEFAULT 0)

  2. Reload the modified wrapper SQL file mentioned in the preceding example into the appropriate database schema. For BOOLEAN parameters with DEFAULT clause, you need to map as follows:

    DEFAULT TRUE(base) to DEFAULT 1 (wrapper)
DEFAULT FALSE (base) to DEFAULT 0 (wrapper)

    For example, if the base stored procedure is PROCEDURE needsWrapper(isTrue BOOLEAN TRUE, value NUMBER DEFAULT 0), then the generated wrapper would be TOPLEVEL$NEEDSWRAPPER (isTrue INTEGER, value NUMBER). You should manually fix the store procedure to be TOPLEVEL$NEEDSWRAPPER (isTrue INTEGER DEFAULT 1, value NUMBER DEFAULT 0)

  3. If a parameter has a default clause, then its corresponding element in the XSD must have an extra attribute, db:default="true". For example, if a parameter has a default clause TOPLEVEL$NEEDSWRAPPER(isTrue INTEGER DEFAULT 1, value NUMBER DEFAULT 0), then the elements in the XSD for isTrue and value need to have the following new attributes:

    <element name="ISTRUE" ... db:default="true" .../>
<element name="VALUE" ... db:default="true" .../>

Configuring the Invoke Activity

To configure the Invoke activity

  1. In JDeveloper BPEL Designer, drag and drop the Invoke activity from the Component Palette into the process diagram between the receiveInput and callbackClient activities.

    Adding the Invoke Activity

    the picture is described in the document text

  2. Link the Invoke activity to the UpdatePO service. The Edit Invoke dialog appears.

  3. In the General tab, enter a name for the Invoke activity and click the Create icon next to the Input Variable field to create a new variable. The Create Variable dialog box appears.

  4. Select Global Variable and enter a name for the variable. You can also accept the default name. Click OK.

  5. In the Edit Invoke dialog,

    click the Create icon next to the Output Variable field. Select Global Variable and enter a name for the variable. You can also accept the default name. Click OK.

    Edit Invoke Dialog

    the picture is described in the document text

  6. In the Edit Invoke dialog box, click Apply and then OK.

    Note: Declaring Header Variables

    You can define an Input Header Variable under the Adapters tab of the Edit Invoke dialog box. This variable can be used to provide applications context information required in the SOA Suite to process Concurrent Programs and PL/SQL APIs and to support multiple languages and multiple organization setups.

    To Create the Header Variable:

    1. Click on the Adapters tab in the Edit Invoke dialog box and click the Browse Variable... icon for the Input Header Variable field.

      Specifying Input Header Variable

      the picture is described in the document text

    2. In the Variable Chooser dialog box, right-click on the Variables node and select Create Variable option from the menu.

    3. In the Name field, enter header for the new variable.

    4. Select Message Type and click Browse Message Type... icon to open Type Chooser dialog box.

    5. Expand the Partner Link node to locate the Header_msg node {http://xmlns.oracle.com/pcbpel/adapter/appscontext/}Header_msg for your partner link. The Header_msg node should be under the path, Your Partner Link WSDL > AppsContextHeader.wsdl > Message Types > Header_msg.

      Declaring Header Variable

      the picture is described in the document text

    6. Click OK to return to the Create Variable dialog box with your selected message type populated.

      Populating Selected Header Variable

      the picture is described in the document text

    7. To view your header variable including Username, Responsibility, ORG_ID, Responsibility Application, Security Group, and NLS Language, click the Browse Message Type... icon to open Variable Chooser dialog box. Locate the header variable to view the variable hierarchical structure with these parameters needed for applications context.

      Viewing Header Variable Structure

      the picture is described in the document text

    8. Click OK to return to the Edit Invoke dialog with the selected header variable populated for the Input Header Variable field. Click Apply to complete the header creation.

      Populating Input Header Variable

      the picture is described in the document text

Configuring the Assign Activity

This step is to configure two Assign activities in order to:

To add the first Assign activity to set applications context information:

  1. In JDeveloper BPEL Designer, drag and drop the Assign activity from the Component Palette into the process map before the Invoke activity.

    Adding an Assign Activity

    the picture is described in the document text

  2. Double-click the Assign activity to access the Edit Assign dialog box.

  3. Click the General tab to enter the name for the Assign activity, such as 'SetAppsContext'.

  4. On the Copy Operation tab, click Create and select Copy Operation from the menu. The Create Copy Operation window appears.

  5. Enter the first pair of parameters:

  6. Enter the second pair of parameters:

    On the Copy Operation tab, click Create and select Copy Operation from the menu. The Create Copy Operation window appears.

  7. Enter the third pair of parameters:

    On the Copy Operation tab, click Create and select Copy Operation from the menu. The Create Copy Operation window appears.

  8. Enter the fourth pair of parameters:

    On the Copy Operation tab, click Create and select Copy Operation from the menu. The Create Copy Operation window appears.

  9. Enter the fifth pair of parameters:

    On the Copy Operation tab, click Create and select Copy Operation from the menu. The Create Copy Operation window appears.

  10. Enter the sixth pair of parameters:

    On the Copy Operation tab, click Create and select Copy Operation from the menu. The Create Copy Operation window appears.

  11. Click Apply and then OK to complete the configuration of the Assign activity.

To add the second Assign activity to set payload for the UpdatePO service:

  1. In JDeveloper BPEL Designer, drag and drop the Assign activity from the Component Palette into the Activity box of the process diagram after the first Assign activity.

  2. Double-click the Assign activity to access the Edit Assign dialog box.

  3. Click the General tab to enter the name for the second Assign activity, such as 'SetPayload'.

  4. On the Copy Operation tab, click Create and select Copy Operation from the menu. The Create Copy Operation window appears.

  5. Enter the first pair of parameters:

  6. Enter the second pair of parameters:

    On the Copy Operation tab, click Create and select Copy Operation from the menu. The Create Copy Operation window appears.

  7. Enter the third pair of parameters:

    On the Copy Operation tab, click Create and select Copy Operation from the menu. The Create Copy Operation window appears.

  8. Enter the fourth pair of parameters:

    On the Copy Operation tab, click Create and select Copy Operation from the menu. The Create Copy Operation window appears.

  9. Enter the fifth pair of parameters:

    On the Copy Operation tab, click Create and select Copy Operation from the menu. The Create Copy Operation window appears.

  10. Click Apply and then OK to complete the configuration of the second Assign activity.

    BPEL Process Diagram

    the picture is described in the document text

Run-Time Tasks for PL/SQL APIs

After designing the BPEL process, the next step is to deploy, run and monitor it.

  1. Deploy the BPEL Process

  2. Test the BPEL Process

Deploying the BPEL Process

You must deploy the BPEL process before you can run it. The BPEL process is first compiled, and then deployed to the BPEL server.

To deploy the BPEL process

  1. Select the BPEL project in the Applications window.

  2. Right-click the project name, and then select Deploy > [Server Connection] > Deploy to Default Domain from the menu that appears.

    Deploy the BPEL Process

    the picture is described in the document text

  3. The BPEL process is compiled and deployed. You can check if the deployment is successful in the Apache Ant log.

    the picture is described in the document text

Testing the BPEL Process

Once the BPEL process is deployed, you can manage and monitor the process from the BPEL Console. You can also test the process and the integration interface by manually initiating the process.

To test the BPEL process

  1. Navigate to Oracle Application Server 10g BPEL Console (http://<soaSuiteServerHostName>:<port>/BPELConsole).

    The BPEL Console login page appears.

    the picture is described in the document text

  2. Enter the username and password and click Login.

  3. The Oracle Enterprise Manager 10g BPEL Control appears. The list of deployed processes is shown under Deployed BPEL Processes.

  4. Click the 'UpdatePO' BPEL process that you want to initiate. The Initiate page appears. Enter the input string required by the process.

  5. Click Post XML Message to initiate the process.

  6. The BPEL process is now initiated. You can check the process flow by clicking the Visual Flow icon. This shows the flow of the BPEL process.

    the picture is described in the document text

  7. Click the Invoke activity in the flow.

  8. Notice that <UPDATE_PO>1</UPDATE_PO> in the output parameters. This shows that the update is successful.

  9. To validate the BPEL process, you can log on to Oracle Applications forms to check if the quantity field is updated in the Purchase Order form.

    1. Log in to Oracle E-Business Suite with the Order Management Super User, Vision Operations (USA) responsibility.

    2. Select Purchasing: Purchase Orders > Purchase Orders to open the Purchase Orders form.

    3. Query your purchase order number by selecting View > Query by Example > Enter.

    4. In the PO, Rev field, enter your order number '4419'.

      Query an Existing Order

      the picture is described in the document text

    5. Execute the query by selecting View > Query by Example > Run.

    6. You should find the quantity number is updated to number '6'.

      Locating an Existing Order

      the picture is described in the document text

Troubleshooting and Debugging

If you experience problems with your PL/SQL API integration, you can take the following troubleshooting steps:

If you still experience problems with your integration, you can enable debugging.

Enabling Debugging

You can enable debugging for PL/SQL APIs using the BPEL Process Manager.

To enable debugging:

  1. Log into your BPEL Process Manager domain.

  2. Select yourdomain.collaxa.cube.ws

  3. Select Debug.

Debugging information is output to the log file for your domain. To examine the log file in the BPEL Process Manager, navigate to Home > BPEL Domains > yourdomain > Logs. The log file is yourdomain.log.

Contents   |   Previous   |   Top of Page   |   Next

Oracle Logo
Copyright © 2005, 2009, Oracle and/or its affiliates. All rights reserved.