Skip Headers

Oracle Fusion Middleware Adapter for Oracle Applications User's Guide
11g Release 1 (11.1.1.4.0)
Part Number E10537-03		Go to Table of Contents
Contents 		Go to previous page
Previous 		Go to next page
Next

Using PL/SQL APIs

This chapter covers the following topics:

Overview of PL/SQL APIs

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 Fusion Middleware User's Guide for Technology Adapters.

Design-Time Tasks for PL/SQL APIs

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

BPEL Process Scenario

In this example, Adapter for Oracle Applications exposes the following stored procedures as Web services in a BPEL process to update the 'quantity' field of an existing purchase order based on user input.

When a change order request is received, the purchase order information including order quantity and other line item details will be retrieved. Based on user input, a new order quantity will be updated in Oracle Order Management.

If the BPEL process is successfully executed after deployment, you can validate the process by querying it directly from Order Management tables or validate it from the Formed-based Oracle Order Management application. The retrieved ordered_quantity value from the query table or you find in the Order Management application should be the same as the quantity value given by the user through changeorder_data.xml file.

Prerequisites to Configure PL/SQL APIs

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 Application Context Header Variables

You need to populate certain variables in the BPEL PM in order to provide context information for Oracle Applications. The context information is required for 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 properties including Username, Responsibility, Responsibility Application, Security Group, and NLS Language. If the values for the new header properties Responsibility Application, Security Group, and NLS Language are not passed, context information will be determined based on Username and Responsibility.

The default Username is SYSADMIN, the default 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 Invoke activity.

For more information about applications context, see Supporting for Normalized Message Properties.

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. 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 partner links.

    Add a partner link for File Adapter.

  3. Define wrapper APIs.

  4. Declare parameters with a DEFAULT clause.

  5. Configure the Invoke Activities

  6. Configure the Transform Activity

  7. Configure an Assign Activity

Creating a New BPEL Project

To create a new BPEL project

  1. Launch Oracle JDeveloper.

  2. Click New Application in the Application Navigator.

    The Create SOA Application - Name your application page is displayed.

    The Create SOA Application - Name your application Page

    the picture is described in the document text

  3. Enter an appropriate name for the application in the Application Name field and select SOA Application from the Application Template list.

    Click Next. The Create SOA Application - Name your project page is displayed.

  4. Enter an appropriate name for the project in the Project Name field. For example, ChangeOrderAPI.

    The Create SOA Application - Name your project Page

    the picture is described in the document text

  5. In the Project Technologies tab, ensure that SOA is selected from the Available technology list to the Selected technology list.

    Click Next. The Create SOA Application - Configure SOA settings page is displayed.

    The Create SOA Application - Configure SOA settings Page

    the picture is described in the document text

  6. Select Composite With BPEL from the Composite Template list, and then click Finish. You have created a new application, and an SOA project. This automatically creates an SOA composite.

    The Create BPEL Process page is displayed.

    The Create BPEL Process Page

    the picture is described in the document text

  7. Enter an appropriate name for the BEPL process in the Name field. For example, ChangeOrderAPI.

    Select Asynchronous BPEL Process in the Template field. Click OK.

    An asynchronous BPEL process is created with the Receive and Reply activities. The required source files including bpel and wsdl, using the name you specified (for example, ChangeOrderAPI.bpel and ChangeOrderAPI.wsdl) and composite.xml are also generated.

    New BPEL Process

    the picture is described in the document text

Adding Partner Links

The next task is to add a partner link to the BPEL process. This section describes how to create an Oracle Applications adapter for the application service by adding 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.

Based on the BPEL process scenario discussed earlier, the following two partner links need to be configured:

To add the first partner link

  1. Click BPEL Services in the Component palette.

    Drag and drop Oracle Applications from the BPEL Services list into the right Partner Link swim lane of the process diagram. The Adapter Configuration Wizard Welcome page appears. Click Next.

  2. Enter a service name in the Service Name field. For example, initLineRec.

    Click Next. The Service Connection dialog appears.

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

    Note: You need to connect to the database where Oracle Applications is running.

  4. Once you have completed creating a new connection for the service, you can add a PL/SQL API by browsing through the list of APIs available in Oracle Applications.

    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 appears indicating that Adapter could not find the Oracle Integration Repository data file corresponding to the database you are connecting to Oracle Applications 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 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 Applications instance, you must select the interface type in the Adapter Configuration Wizard. Select Tables/Views/APIs/Concurrent Programs to proceed.

    Click Get Object in the Application Interface dialog to open the Oracle Applications Module Browser.

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

    Specify an API from The Oracle Applications Module Browser

    the picture is described in the document text

    Navigate to Other Interfaces > Custom Objects >PLSQL APIs > OE_ORDER_PUB to select a custom stored procedure GET_G_MISS_LINE_REC.

    Note: The GET_G_MISS_LINE_REC (GET_G_MISS_LINE_REC) stored procedure does not take any input but has a complex data type (PL/SQL Table) as output. Adapter for Oracle Applications design-time automatically generates a wrapper stored procedure and loads it on the underlying database.

  6. Click OK.

    Please note that the input and the output parameters of the Stored Procedure contains complex data types that are not readily mapped to JDBC types. The Adapter for Oracle Applications wizard provides a mechanism that detects when these types are used and then invokes Oracle JPublisher to generate the necessary wrappers automatically. Oracle JPublisher generates two SQL files, one to create schema objects, and another to drop them. The SQL that creates the schema objects is automatically executed from within the wizard to create the schema objects in the database schema before the XSD is generated.

    The Adapter Service points to the wrapper Stored Procedure instead of the Process Order Line Stored Procedure. The Adapter design-time automatically loads the wrapper procedure onto the underlying database.

    Application Interface Page

    the picture is described in the document text

  7. Click Next, 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.

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

    After adding and configuring the partner link, the next task is to configure the BPEL process.

    Partner Link Information

    the picture is described in the document text

To add the second partner link

  1. Click BPEL Services in the Component palette.

    Drag and drop Oracle Applications from the BPEL Services list into the right Partner Link swim lane of the process diagram. The Adapter Configuration Wizard Welcome page appears. Click Next.

  2. Enter a service name in the Service Name field. For example, OrderManagement.

    Click Next. The Service Connection dialog appears.

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

    Note: You need to connect to the database where Oracle Applications is running.

  4. Once you have completed creating a new connection for the service, you can add a PL/SQL API by browsing through the list of APIs available in Oracle Applications.

    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 appears indicating that Adapter 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 will pick it up automatically next time and retrieve data from your local Integration Repository.

    You can select one of the following options:

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

    Specify an API from The Oracle Applications Module Browser

    the picture is described in the document text

    Select the required PL/SQL API. For example, select Process Order Line (PROCESS_LINE) API from Product Families > Supply Chain Management (SCM_PF)> Supply Chain Trading Connector(CLN) > Sales Order (ONT_SALES_ORDER) > PLSQL > Process Order API(OE_ORDER_PUB) > Process Order Line (PROCESS_LINE).

    Note: Use the Search option to quickly find the required objects. Enter the required database object name in the Object Name field and select APIs. And then, 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.

  6. Click OK.

    Adapter Configuration Wizard - Application Interface Page

    the picture is described in the document text

    Please note that the input and the output parameters of the Stored Procedure contains complex data types that are not readily mapped to JDBC types. The Adapter for Oracle Applications wizard provides a mechanism that detects when these types are used and then invokes Oracle JPublisher to generate the necessary wrappers automatically. Oracle JPublisher generates two SQL files, one to create schema objects, and another to drop them. The SQL that creates the schema objects is automatically executed from within the wizard to create the schema objects in the database schema before the XSD is generated.

    The Adapter Service points to the wrapper Stored Procedure instead of the Process Order Line Stored Procedure. The Adapter design-time automatically loads the wrapper procedure onto the underlying database.

  7. Click Next, then click Finish to complete the process of configuring Adapter for Oracle Applications.

    the picture is described in the document text

    The wizard generates the WSDL file corresponding to the XML schema. This WSDL file is now available for the partner link.

    Partner Link Information

    the picture is described in the document text

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

    After adding and configuring the partner link, the next task is to configure the BPEL process.

Adding a Partner Link for File Adapter

Use this step to configure a BPEL process by synchronously reading an existing purchase order to obtain the order details.

To add a Partner Link for File Adapter to read order details:

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

    Drag and drop File Adapter from the BPEL Services list into the right Partner Link swim lane of the process diagram. The Adapter Configuration Wizard Welcome page appears.

    Click Next.

  2. In the Service Name page, enter a name for the file adapter service, such as getOrderDetails.

  3. Click Next. The Adapter Interface page appears.

    Specifying the Adapter Interface

    the picture is described in the document text

    Select the Define from operation and schema (specified later) radio button and click Next.

  4. In the Operation page, specify the operation type. For example, select the Synchronous Read File radio button. This automatically populates the Operation Name field.

    Specifying the Operation

    the picture is described in the document text

    Click Next to access the File Directories page.

  5. Select the Logical Name radio button and specify directory for incoming files, such as inputDir.

    Ensure the Delete files after successful retrieval check box is not selected.

    Configuring the Input File

    the picture is described in the document text

    Click Next to open the File Name page.

  6. Enter the name of the file for the synchronous read file operation. For example, enter changeorder_data.xml. Click Next to open the Messages page.

  7. Select the 'browse for schema file' icon on the top right corner to open the Type Chooser window.

    Click Type Explorer and select Project WSDL Files > OrderManagement.wsdl > Inline Schemas > schema > InputParameters.

    The selected schema information will be automatically populated in the URL and Schema Element fields.

    Specifying Message Schema

    the picture is described in the document text

  8. Click Next and then Finish. The wizard generates the WSDL file corresponding to the partner link. The main Create Partner Link dialog box appears, specifying the new WSDL file getOrderDetails.wsdl.

    Completing the Partner Link Configuration

    the picture is described in the document text

    Click Apply and OK to complete the configuration and create the partner link with the required WSDL settings for the File Adapter service.

    The getOrderDetails Partner Link appears in the BPEL process diagram.

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 2 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 Activities

This step is to configure three Invoke activities:

  1. To get the order details that is received from the Receive activity by invoking the getOrderDetails partner link in an XML file.

  2. To initialize the existing purchase order by invoking initLineRec partner link.

  3. To update the purchase order quantity information to Oracle Applications by invoking OrderManagement partner link.

To add the first Invoke activity for a partner link to get order details:

  1. In JDeveloper BPEL Designer, select BPEL Activities and Components in the component palette. Drag and drop the first Invoke activity into the center swim lane of the process diagram, between the receiveInput and callbackClient activities.

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

  3. Enter a name for the Invoke activity, then 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, then 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 to create a new variable. The Create Variable dialog box appears.

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

    the picture is described in the document text

    Click Apply and then OK in the Edit Invoke dialog to finish configuring the Invoke activity.

    The Invoke activity appears in the process diagram.

To add the second Invoke activity for a partner link to initialize the existing purchase order:

  1. In JDeveloper BPEL Designer, select BPEL Activities and Components in the component palette. Drag and drop the second Invoke activity into the center swim lane of the process diagram, between the first Invoke and callbackClient activities.

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

  3. Repeat Step 3 to Step 5 described in the first Invoke activity procedure to complete the second Invoke activity.

    the picture is described in the document text

To add the third Invoke activity for a partner link to update the order quantity information to Oracle Applications:

  1. In JDeveloper BPEL Designer, select BPEL Activities and Components in the component palette. Drag and drop the third Invoke activity into the center swim lane of the process diagram, between the second Invoke and callbackClient activities.

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

  3. Repeat Step 3 to Step 5 described in the first Invoke activity procedure.

    the picture is described in the document text

  4. Setting Header Properties for Application Context

    Use the following steps to set the header message properties required to pass application context required to complete the BPEL process:

    1. Select the Properties tab in the Edit Invoke dialog box.

    2. Scroll down to locate the jca.apps.Username property from the list and double click the associated value field to enable the Adapter Property Value icon.

      Setting Header Message Properties

      the picture is described in the document text

    3. Click the icon to open the Adapter Property Value dialog for the selected jca.apps.Username property.

      Entering the Header Message Property Value

      the picture is described in the document text

    4. Select the Expression radio button and enter 'OPERATIONS' as the property value.

      Click OK.

    5. Repeat Step 2 to Step 4 to assign 'Order Management Super User, Vision Operations (USA)' for jca.apps.Responsibility.

  5. Click Apply and then OK to complete the Invoke activity.

Configuring the Transform Activity

The Transform activity can be used to configure the parameters for the input and output variables. The Transform activity can also be used if variable values need to be transformed before updating them in Oracle Applications.

To configure the Transform activity

Before add a Transform activity, use the following steps to add variables:

  1. From the structure panel, select ChangeOrderAPI.bpel > Variables > Process > Variables folder. Right click on Variables folder and select Create Variable from the drop-down menu.

  2. In the Create Variable dialog, enter temp_miss_line in the variable Name field.

  3. Select the Message Type radio button and click the Browse Message Type icon to open the Type Chooser.

    Select args_in_msg from Message Types > Project WSDL Files > OrderManagement.wsdl > Message Types. Click OK to close the Type Chooser.

    The selected information will be populated in the Message Type field in the Create Variable dialog.

Use the following step to configure the Transform activity:

  1. In JDeveloper BPEL Designer, select BPEL Activities and Components in the component palette. Drag and drop Transform into center swim lane of the process map window. The Transform activity should be placed in between the second and thirdInvoke activities.

    This Transform activity is used to set the output of initLineRec service to the input of the OrderManagement service.

  2. Double-click Transform in the process map to open the Transform dialog. The Transformation tab is selected by default.

    Click the General tab to name this Transform activity as set_Miss_Line.

  3. In the Transformation tab, click the Create icon to open the Source Variable dialog.

    Source Variable Dialog

    the picture is described in the document text

    Select the Invoke_initLineRec_OutputVariable from the Source Variable drop-down list, and select OutputParameters element as the Source Part value.

    Click OK.

  4. Select the Invoke_OrderManagement_InputVariable from the Target Variable drop-down list. The Target Part value of the variable is also selected.

  5. Click the Create icon next to the Mapper File field to create a new transformation mapping file. Mapper File specifies the file in which you create the mappings using the XSLT Mapper Transformation tool.

  6. The transformation mapping file appears. The Design view appears by default.

  7. You can define the parameter values in the Design view. Drag a string function to the Design area. Connect the function to the appropriate parameter for which you want to define a value.

    For example, link db:OE_ORDER_PUB-24GET_G_MISS_LINE from the source variable to db:P_LINE_TBL_ITEM as the target variable.

    Variable Mapping

    the picture is described in the document text

    Note: You can use an input parameter value from the source variable, transform it using a string function, and use it as the input parameter value for the target variable.

  8. Click OK to create the mapping from source variable to target variable.

Configuring an Assign Activity

Use the Assign activity to pass the output of getOrderDetails service and initLineRec service as an input to the OrderManagement service.

To add an Assign activity:

  1. In JDeveloper BPEL Designer, select BPEL Activities and Components in the component palette. Drag and drop the Assign activity into the center swim lane of the process diagram right after the Transform activity that you just created earlier.

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

    Click the General tab to enter a name for the Assign activity. For example, setOrderChanges.

  3. Select the Copy Operation tab, click the 'Plus' sign icon and select Copy Operation from the menu. The Create Copy Operation window appears.

    Specifying a Copy Operation Action

    the picture is described in the document text

  4. Enter the first pair of parameters:

  5. Repeat Step 3 to 4 to enter the second pair of parameters:

  6. Repeat Step 3 to 4 to enter the third pair of parameters:

  7. Repeat Step 3 to 4 to enter the fourth pair of parameters:

  8. Repeat Step 3 to 4 to enter the fifth pair of parameters:

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

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 application server (Oracle WebLogic Server) that you have established the connection.

Prerequisites

To deploy the BPEL process

  1. Select the BPEL project in the Applications window.

  2. Right-click the project name, and then select Deploy > [project name] > [serverConnection] from the menu that appears.

    Deploying the BPEL Process

    the picture is described in the document text

  3. The BPEL process is compiled and deployed. You can check the progress in the Messages window.

Testing the BPEL Process

Once the BPEL process is deployed, you can manage and monitor the process from the Oracle Enterprise Manager Fusion Middleware Control 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 Enterprise Manager Fusion Middleware Control Console (http://<servername>:<portnumber>/em). The composite you deployed is displayed in the Applications Navigation tree.

    the picture is described in the document text

  2. Enter username (such as weblogic) and password and click Login to log in to a farm.

    You may need to select an appropriate target instance farm if there are multiple target Oracle Enterprise Manager Fusion Middleware Control Console farms.

  3. From the Farm base domain, expand the SOA > soa-infra to navigate through the SOA Infrastructure home page and menu to access your deployed SOA composite applications running in the SOA Infrastructure for that managed server.

    Note: The Farm menu always displays at the top of the navigator. As you expand the SOA folder in the navigator and click the links displayed beneath it, the SOA Infrastructure menu becomes available at the top of the page.

    Click the SOA composite application that you want to initiate (such as 'ChangeOrderAPI') from the SOA Infrastructure.

    Viewing Deployed SOA Composites

    the picture is described in the document text

    Click Test at the top of the page.

  4. The Test Web Service page for initiating an instance appears. You can specify the XML payload data to use in the Input Arguments section.

    Enter the input string required by the process and click Test Web Service to initiate the process.

    Testing Web Service

    the picture is described in the document text

    The test results appear in the Response tab upon completion.

  5. Click on the BPEL process name and then click the Instances tab. The SOA composite application instance ID, name, conversation ID, most recent known state of each instance since the last data refresh of the page are displayed.

    In the Instance ID column, click a specific instance ID to show the message flow through the various service components and binding components. The Flow Trace page is displayed.

    In the Trace section, you should find the sequence of the message flow for the service binding component (changeorderapi_client_ep), BPEL component (ChangeOrderAPI), and reference binding components (getOrderDetails, initLineRec, and OrderManagement). All involved components have successfully received and processed messages.

    Flow Trace Page

    the picture is described in the document text

    Click your BPEL service component instance link (such as ChangeOrderAPI) to display the Instances page where you can view execution details for the BPEL activities in the Audit Trail tab.

    Click the Flow tab to check the BPEL process flow diagram. Click an activity of the process diagram to view the activity details and flow of the payload through the process.

    Validating Records Using SQL

    To confirm that the records have been written into the Oracle Applications, you can write SQL select l.ordered_quantity from oe_order_lines_all l,oe_order_headers_all h where h.orig_sys_document_ref='<ORDER_ID> and h.header_id=l.header_id; statements and fetch the results showing the ordered_quantity should be the same as the value provided in the changeorder_data.xml document.

    Verifying Records in Oracle Applications

    Alternatively, you can go to the specific module in Oracle Applications with Oracle Management Super User, Vision Operation (USA) responsibility.

    To validate it in Oracle Order Management:

    1. Log on to the Forms-based Oracle Applications with the Order Management, Super User responsibility.

    2. Select Order Returns > Sales Order. Sales Order Forms would open up.

    3. Search for an order by entering the order number in the Customer PO field. This would bring up the details of a newly created order.

    4. Select the Line Items tab to check the quantity of the order. It should be the same as it is set in changeorder_data.xml file.

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, 2010, Oracle and/or its affiliates. All rights reserved.