Using Interface Tables and Views

This chapter covers the following topics:

• Overview of Interface Tables and Views
• Design-Time Tasks for Interface Tables
• Creating a New SOA Composite Application with BPEL Process
• Adding a Partner Link
• Adding a Partner Link for File Adapter
• Configuring Invoke Activities
• Configuring an Assign Activity
• Run-Time Tasks for Interface Tables
• Deploying the SOA Composite Application with BPEL Process
• Testing the Deployed SOA Composite Application with BPEL Process
• Design-Time Tasks for Views
• Creating a New SOA Composite Application with BPEL Process
• Adding a Partner Link
• Adding a Partner Link for File Adapter
• Configuring Invoke Activities
• Configuring an Assign Activity
• Run-Time Tasks for Views
• Deploying the SOA Composite Application with BPEL Process
• Testing the Deployed SOA Composite Application with BPEL Process

Overview of Interface Tables and Views

Interface tables can be used to insert and update data in Oracle E-Business Suite, and interface views can be used to retrieve data from Oracle E-Business Suite. This chapter describes the following interfaces:

• Interface Tables

• Interface Views

Interface Tables

Interface tables can be used to insert and update data in Oracle E-Business Suite. For example, you can insert a purchase order into Oracle E-Business Suite to generate the sales order automatically. Data is never loaded directly into Oracle E-Business Suite application base tables. Instead, data is first loaded into interface tables, and then Oracle-supplied concurrent programs move data from interface tables to base tables. This ensures that all business logic and processing is handled using Oracle components.

Oracle E-Business Suite Adapter uses open interface tables to integrate with Oracle E-Business Suite applications through direct database access. Oracle E-Business Suite Adapter inserts data into the open interface tables. These interface tables can be used only for insert operations and support only an inbound connection into Oracle E-Business Suite.

Interface tables are intermediate tables into which the data is inserted first. Once the data gets inserted into the interface tables, the data is validated, and then transferred to the base tables. Base tables are real application tables that reside in the application database. The data that resides in the interface tables is transferred to the base tables using concurrent programs. A concurrent program is an instance of an execution file. Concurrent programs are scheduled in Oracle E-Business Suite to move data from interface tables to base tables. These programs perform the application-level checks and run validation before inserting data into base tables.

Views

Oracle E-Business Suite Adapter uses views to retrieve data from Oracle E-Business Suite applications. For example, you can retrieve information about your customers from the required tables in Oracle E-Business Suite.

Views allow only simple definition. By using views, you can get synchronous data access to Oracle E-Business Suite. In Oracle E-Business Suite Adapter, views are created on base tables as well as interface tables. These views can be used only for select operations.

In the Oracle E-Business Suite 11.5.10 release, you cannot work on multiple views. A work around to address this would be to create a view that spans multiple views.

Design-Time Tasks for Interface Tables

This section describes how to configure the Oracle E-Business Suite Adapter to use interface tables. It describes the steps to configure the adapter using the Adapter Configuration Wizard in Oracle JDeveloper.

SOA Composite Application with BPEL Process Scenario

Take Open Interface tables (OE_HEADER_IFACE_ALL and OE_LINES_IFACE_ALL) as examples.

When a request of inserting order data into Open Interface tables is received, order details will be retrieved through synchronous read operation from an input file and then inserted into Open Interface tables (OE_HEADER_IFACE_ALL and OE_LINES_IFACE_ALL).

When the SOA Composite application with BPEL process has been successfully executed after deployment, you can validate the result by fetching the inserted data using SQL statement.

Prerequisites to Configure Interface Tables

• Define primary keys on all the interface tables being used.

• Define parent-child relationships among all the selected interface tables.

SOA Composite Application with BPEL Process Flow

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

1. Create a new SOA Composite application with BPEL process

2. Add a partner link

3. Add a partner link for File Adapter

4. Configure the Invoke activities

5. Configure the Assign activity

Creating a New SOA Composite Application with BPEL Process

To create a new SOA Composite application with BPEL process

1. Open JDeveloper BPEL Designer.

2. Click New Application in the Application Navigator.

   The New Gallery page appears. Select SOA Application from the Items list.

   The New Gallery Page

   

   Click OK.

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

   The Create SOA Application - Name your application Page

   

   Enter an appropriate name for the application in the Application Name field.

   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, InsertOrder.

   Notice that SOA Suite is shown in the Project Features section.

   The Create SOA Application - Name your project Page

   

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

   The Create SOA Application - Configure SOA settings Page

   

6. Leave the default Standard Composite selection unchanged for the Start from field.

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

   The Create BPEL Process page is displayed.

7. Leave the default BPEL 2.0 Specification selection unchanged. This creates a BPEL project that supports the BPEL 2.0 specification.

   Enter an appropriate name for the BPEL process in the Name field. For example, InsertOrder.

   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, InsertOrder.bpel and InsertOrder.wsdl) and InsertOrder (composite.xml) are also generated.

Adding a Partner Link

This section describes how to add a partner link to your BPEL process. A 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 to insert order details into selected Open Interface tables

1. Click BPEL Services in the Component Palette.

   Drag and drop Oracle E-Business Suite (formerly known as Oracle Applications) from the BPEL Services list into the right Partner Link swim lane of the process diagram. The Oracle E-Business Suite Reference page appears.

   Enter a service name in the Name field. For example, InsertOrder.

2. 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 E-Business Suite is running.

   • You can create a new database connection by clicking the Create a New Database Connection icon.

     How to define a new database connection, see Create a New Database Connection.

   • You can select an existing database connection that you have configured earlier from the Connection drop-down list.

     The Service Connection page will be displayed with the selected connection information. The JNDI (Java Naming and Directory Interface) name corresponding to the database connection appears automatically in the Database Server JNDI Name field. Alternatively, you can specify a JNDI name.

     Note: When you specify a JNDI name, the deployment descriptor of the Oracle E-Business Suite Adapter must associate this JNDI name with configuration properties required by the adapter to access the database.

     The JNDI name acts as a placeholder for the connection used when your service is deployed to the BPEL server. This enables you to use different databases for development and later for production.

     Note: For more information about JNDI concepts, refer to Oracle Fusion Middleware User's Guide for Technology Adapters.

4. Click Next in the Service Connection dialog box. You can add an interface table by browsing through the list of interface tables available in Oracle E-Business Suite.

   If you are connecting to Oracle E-Business Suite Release 12, then the IREP File not present dialog box 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:

   • Click Yes to extract the Integration Repository data file.

     After the system successfully creates a local copy of the Integration Repository data file, next time when you connect to the database, you will find the IRep Data File field appears in the Operation dialog box indicating where your local copy exists with the creation date and time as part of the file name.

     Using the Local Integration Repository Data File

     

   • Click No to query the Integration Repository data file from the live database you are connecting to display the Integration Repository tree.

   In this example, click No. Click Next in the Operation page to open the Oracle E-Business Suite Module Browser.

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

   Note: Oracle E-Business Suite Module Browser includes the various product families that are available in Oracle E-Business Suite. For example, Applications Technology or Order Management Suite are product families in Oracle E-Business Suite. The product families contain the individual products. For example, Order Management Suite contains the Order Management product. The individual products contain the business entities associated with the product. For example, the Order Management product contains the Sales Order business entity.

   Business entities contain the various application modules that are exposed for integration. These modules are grouped according to the interface they provide. concurrent programs can be found under the Open Interfaces category.

   Navigate to Order Management Suite (OM_PF) > Order Management (ONT) > Sales Order (ONT_SALES_ORDER) > OpenInterfaces >Order Management Sales Orders Open Interface (OEOIMP) > Tables to select OE_HEADERS_IFACE_ALL.

   Note: You can also search for a table by entering the name of the program in the Object Name field. Select the Tables check box, then click Search.

6. Oracle E-Business Suite Adapter provides flexfield support for Open Interface Tables. Once the selected table is displayed in the Oracle E-Business Suite Module Browser, you can either configure flexfield mappings for the table that has flexfields defined in Oracle E-Business Suite or use current data without further flexfield configuration.

   Displaying a Selected Interface Table from the Module Browser

   

   Note: If a selected interface table has pre-configured flexfield mappings available, a pop-up window appears allowing you to decide if you want to use these pre-configured mappings. Click Yes to display these mappings as part of the flexfield definitions in the Configure Flexfields region. You can modify them later if needed.

   Click No to indicate that you will not use these mappings and the pop-up window disappears.

   Configuring Flexfield Mapping Data for the Selected Open Interface Table

   Perform either one of the following tasks in the Oracle E-Business Suite Module Browser:

   • Use the current data without further configuration by clicking OK to continue.

   • Configure flexfield mapping data by either using an existing flexfield mapping or creating a new mapping through a flexfield wizard if desired.

     • Creating a New Mapping

       You can create a new mapping for a selected open interface table by clicking Add Mapping in the Flexfield Metadata section. A new flexfield mapping wizard appears guiding you through each configuration page where you can add key and descriptive flexfields, as well as configure flexfield mapping between the selected interface table columns and flexfields defined in the Oracle E-Business Suite instance.

       For more information on how to create a new mapping and configure key and descriptive flexfields, see:

       • Adding or Configuring a New Mapping

       • Configuring Key Flexfield Mappings for an Open Interface Table

       • Configuring Descriptive Flexfield Mappings for an Open Interface Table

     • Importing an Existing Mapping

       Instead of creating a new mapping, you can use an existing mapping that has been created earlier by clicking Import from File in the Flexfield Metadata section. This lets you select a desired flexfield mapping for your selected interface table. Once a desired mapping is imported, you can modify the context values for a descriptive flexfield, and modify structure for a key flexfield if needed to meet your needs.

       See: Importing an Existing Flexfield Mapping.

     For more information on flexfield support for open interface tables and how to modify existing mapping configuration, see Flexfield Support for PL/SQL APIs and Open Interface Tables.

   In this example, click OK to continue. The Application Interface page appears with the selected open table.

   Adapter Configuration Wizard - Application Interface Page

   

7. Click Get Object to open the Oracle E-Business Suite Module Browser again to select another open interface table OE_LINES_IFACE_ALL using the same navigation path Order Management Suite (OM_PF) > Order Management (ONT) > Sales Order (ONT_SALES_ORDER) > OpenInterfaces >Order Management Sales Orders Open Interface (OEOIMP) > Tables.

   In this example, click OK to continue the partner link creation without configuring flexfields for the selected interface table.

   Note: Configuring Flexfield Mapping Data for the Selected Open Interface Table

   If a selected interface table has pre-configured flexfield mappings available, a pop-up window appears allowing you to decide if you want to use these pre-configured mappings.

   • Click Yes to display these mappings as part of the flexfield definitions in the Configure Flexfields region. You can modify them later if needed.

   • Click No to indicate that you will not use these mappings and the pop-up window disappears.

   If you want to configure flexfield mappings without using the pree-configured flexfield ones, you can create a new mapping through a flexfield wizard if desired. For information on how to configure flexfield data for the selected open interface table, see:

   • Adding or Configuring a New Mapping

   • Configuring Key Flexfield Mappings for an Open Interface Table

   • Configuring Descriptive Flexfield Mappings for an Open Interface Table

   • Importing an Existing Flexfield Mapping

   The Application Interface page appears with the two selected tables.

   Adapter Configuration Wizard - Application Interface Page

   

   Click OK.

8. Click Next. The Operation Type page is displayed.

   Adapter Configuration Wizard - Operation Type Page

   

   Select the Perform an Operation on a Table radio button and then choose the Insert check box. Ensure that the Select check box is deselected.

9. Click Next. The Select Table page appears which displays the tables that have been previously imported in this JDeveloper project (including tables that were imported for other partner links). This enables you to reuse configured table definitions in multiple partner links.

   Adapter Configuration Wizard - Select Table Page

   

   Select OE_HEADERS_IFACE_ALL as the root database table for this service's query.

10. Click Next. The Define Primary Keys page is displayed.

    Adapter Configuration Wizard - Define Primary Keys Page for OE_HEADERS_IFACE_ALL

    

    Select the following primary keys for the OE_HEADERS_IFACE_ALL table:

    • ORDER_SOURCE_ID

    • ORIG_SYS_DOCUMENT_REF

    Click Next.

    Select the same primary keys for the OE_LINES_IFACE_ALL table.

11. Click Next. The Relationships page appears. Click Create to open the Create Relationship dialog.

    Defining Relationships

    

    Enter the following information to define the relationship between the header and the detail table:

    • Select the OE_HEADERS_IFACE_ALL as the parent table and OE_LINES_IFACE_ALL as the child table.

    • Select the mapping type: OE_HEADERS_IFACE_ALL has a 1:M Relationship with OE_LINES_IFACE_ALL

      Note: If foreign key constraints between tables already exist in the database, then two relationships are created automatically while importing tables. One of the relationships is 1:M relationship from the source table, which is the table containing the foreign key constraints, to the target table. The other relationship is a 1:1 back pointer from the target table to the source table.

    • Select the Private Owned check box.

    • Associate the foreign key fields with the primary key fields:

      • OE_HEADERS_IFACE_ALL.ORDER_SOURCE_ID: ORDER_SOURCE_ID

      • OE_HEADERS_IFACE_ALL.ORIG_SYS_DOCUMENT_REF: ORIG_SYS_DOCUMENT_REF

    • The Relationship Name field is populated automatically by default. You can optionally specify a new name for the relationship you are creating.

    Click OK. The newly created relationship information appears in the Relationships page.

    Adapter Configuration Wizard - Relationships Page

    

12. Click Next. The Attribute Filtering page appears. Leave default selections unchanged.

    Adapter Configuration Wizard - Attribute Filtering Page

    

    Click Next. The Advanced Options page appears.

    Click Next.

13. Click Finish. The wizard generates the WSDL file corresponding to the selected interface. This WSDL file is now available for the partner link.

Adding a Partner Link for File Adapter

Use this step to configure a BPEL process by synchronously reading payload from an input file to obtain the purchase order details.

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

1. In Oracle 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 before the first partner link InsertOrder. The File Adapter Reference page appears.

   Specifying a File Reference Name

   

   Enter a name for the File Adapter service, for example, getOrderDetails.

2. Click Next. The Adapter Interface page appears.

   Specifying the Adapter Interface

   

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

   The File Server Connection page appears. Leave the default value unchanged for the File Server JNDI Name field.

   Specifying File Server JNDI Name

   

3. 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

   

   Click Next to access the File Directories page.

4. 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

   

   Click Next to open the File Name page.

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

6. Select the 'browse for schema file' icon next to the URL field to open the Type Chooser.

   Click Type Explorer and select Project Schema Files > InsertOrder_table.xsd > OeHeadersIfaceAllCollection. Click OK.

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

   Specifying Message Schema

   

7. Click Next and then Finish. This completes the configuration and create the partner link with the required WSDL settings for the File Adapter service. The wizard also generates the WSDL file corresponding to the partner link. The main Create Partner Link dialog box appears, specifying the new WSDL file getOrderDetails.wsdl.

   The getOrderDetails Partner Link appears in the BPEL process diagram.

Configuring Invoke Activities

After configuring partner links, you need to configure the following two Invoke activities:

1. To get the acknowledgement data by invoking the ViewAck partner link.

2. To write acknowledgement data to an XML file by invoking WriteAckdata partner link for File Adapter.

To add the first Invoke activity for a partner link to get acknowledgement data:

1. In Oracle JDeveloper BPEL Designer, expand the BPEL Constructs from the Component Palette. Drag and drop the first Invoke activity from the Web Service section into the center swim lane of the process diagram, between the receiveInput and callbackClient activities.

   Adding an Invoke Activity

   

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

3. Enter a name for the Invoke activity. In the Input tab, ensure the Input Variable radio button is selected. 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 Output tab, ensure the Output Variable radio button is selected. Click the Create icon next to the Output Variable field to create a new variable. The Create Variable dialog box appears.

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

   

   Click Apply and OK in the Edit Invoke dialog box 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 insert order data into Open Interface tables:

1. In Oracle JDeveloper BPEL Designer, expand the BPEL Constructs from the Component Palette. Drag and drop the second Invoke activity from the Web Service section into the center swim lane of the process diagram, between the first Invoke and callbackClient activities.

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

3. Repeat Step 3 and Step 4 described in the first Invoke activity procedure. Click Apply and then OK in the Edit Invoke dialog box to finish configuring the second Invoke activity.

   

Configuring an Assign Activity

The next task is to add an Assign activity to the process map. This is used to pass the output of File Adapter’s Synchronous Read (getOrderDetails) service as an input to the Open InterfaceInsertOrder service.

To add an Assign activity:

1. In JDeveloper BPEL Designer, expand the BPEL Constructs from the Component Palette.

   Drag and drop the Assign activity from the Basic Activities section into the center swim lane of the process diagram, between the first and second Invoke activities 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, SetOrderDetails.

3. Enter the following parameter information:

   • In the From navigation tree, navigate to Variable > Process > Variables > Invoke_SynchRead_OutputVariable and select body.

   • In the To navigation tree, navigate to Variable > Process > Variables > Invoke_insert_InputVariable and select OeHeadersIfaceAllCollection.

   Drag the source node (body) to connect to the target node (OeHeadersIfaceAllCollection) that you just specified. This creates a line that connects the source and target nodes. The copy rule is displayed in the From and To sections at the bottom of the Edit Assign dialog box.

   

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

Completed BPEL Process Diagram

Click the InsertOrder (composite.xml) to display the Oracle JDeveloper composite diagram:

Note: Click the Source tab of InsertOrder (composite.xml) to enter a value for the physical directory inputDir for the reference getOrderDetails (such as /usr/tmp).

<property name="inputDir" type="xs:string" many="false" override="may">/usr/tmp</property>

Run-Time Tasks for Interface Tables

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

1. Deploy the SOA Composite application with BPEL process

2. Test the deployed SOA Composite application with BPEL process

Deploying the SOA Composite Application with BPEL Process

To invoke the services from the BPEL client contained in the SOA composite, the SOA composite needs to be deployed to the Oracle WebLogic managed server. This can be achieved using Oracle JDeveloper. Once the composite is deployed, it can be tested from the Oracle Enterprise Manager Fusion Middleware Control Console.

Prerequisites

Before deploying the SOA composite with BPEL process using Oracle JDeveloper, you must have established the connectivity between the design-time environment and the run-time server. For more information, see Configuring the Data Source in Oracle WebLogic Server and Creating an Application Server Connection.

Note: If a local instance of the WebLogic Server is used, start the WebLogic Server by selecting Run > Start Server Instance from Oracle JDeveloper. Once the WebLogic Admin Server "DefaultServer" instance is successfully started, the <Server started in Running mode> and DefaultServer started message in the Running:DefaultServer and Messages logs should appear.

To deploy the SOA Composite application with BPEL process

1. Select the SOA Composite project in the Applications Navigator.

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

   For example, you can select Deploy > InsertOrder > soa-server1 to deploy the process if you have the connection set up appropriately.

   Note: If this is the first time to set up server connection, then the Deployment Action window appears. Select 'Deploy to Application Server' and click Next.

   

   In the Deploy Configuration window, ensure the following information is selected before clicking Next to add a new application server:

   • New Revision ID: 1.0

   • Mark composite revision as default: Select this check box.

   • Overwrite any existing composites with the same revision ID: Select this check box.

   The steps to create a new Oracle WebLogic Server connection from JDeveloper are covered in Creating an Application Server Connection.

3. In the Select Server page, select 'soa-server1' that you have established the server connection earlier. Click Next.

   

4. In the SOA Servers page, accept the default target SOA Server ('soa-server1') selection.

   

   Click Next and Finish.

   If you are deploying the composite for the first time from your Oracle JDeveloper session, the Authorization Request window appears. Enter username and password information specified during Oracle SOA Suite installation. Click OK.

5. Deployment processing starts. Monitor deployment progress and check for successful compilation in the SOA - Log window as well as in the Deployment - Log window.

Testing the Deployed SOA Composite Application with BPEL Process

Once the SOA Composite application with 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 manually initiate and monitor the deployed SOA Composite application with 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.

   

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 > soa-infra (soa_server1) > default 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 'OrderInsert') from the SOA Infrastructure.

   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 test results appear in the Response tab upon completion.

5. Click Launch Flow Trace in the Test Web Service page. The Flow Trace page is displayed.

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

   If any error occurred during the test, you should find it in the Faults section.

   Flow Trace Page

   

6. Click your BPEL service component instance link (such as InsertOrder) 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.

7. Validating Records Using SQL

   To confirm that the records have been inserted into the selected Open Interface tables, you can write SQL SELECT statements and fetch the results showing the latest records inserted into the open interface tables.

Design-Time Tasks for Views

This section describes the steps to configure Oracle E-Business Suite Adapter using the Adapter Configuration Wizard in Oracle JDeveloper.

SOA Composite Application with BPEL Process Scenario

In this example, Oracle E-Business Suite Adapter retrieves purchase order acknowledgement information from Interface Views OE_HEADER_ACKS_V and OE_LINE_ACKS_V. The acknowledgement information will then be written to an XML file as an output for confirmation.

When the SOA Composite application with BPEL process has been successfully executed after deployment, you can validate the output XML file with the same order book reference ID once an order is approved.

Prerequisites to Configure Views

You need to define a uniqueness criteria, which could be a single key or composite keys.

SOA Composite Application with BPEL Process Flow

Based on the scenario, the following design-time tasks are discussed in this chapter.

1. Create a new SOA Composite application with BPEL process

2. Add a partner link

3. Add a partner link for File Adapter

4. Configure the Invoke activity

5. Configure the Assign activity

Creating a New SOA Composite Application with BPEL Process

To create a new SOA Composite application with BPEL process

1. Launch Oracle JDeveloper.

2. Click New Application in the Application Navigator.

   The New Gallery page appears. Select SOA Application from the Items list.

   The New Gallery Page

   

   Click OK.

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

   The Create SOA Application - Name your application Page

   

   Enter an appropriate name for the application in the Application Name field.

   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, WriteAck.

   The Create SOA Application - Name your project Page

   

   Notice that SOA Suite is shown in the Project Features section.

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

   The Create SOA Application - Configure SOA settings Page

   

6. Leave the default Standard Composite selection unchanged for the Start from field.

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

   The Create BPEL Process page is displayed.

7. Leave the default BPEL 2.0 Specification selection unchanged. This creates a BPEL project that supports the BPEL 2.0 specification.

   Enter an appropriate name for the BPEL process in the Name field. For example, WriteAck.

   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, WriteAck.bpel and WriteAck.wsdl) and WriteAck.bpel (composite.xml) are also generated.

8. Navigate to SOA folder and click the WriteAck.bpel (composite.xml) to view the composite diagram.

   

   Double click on the WriteAckcomponent to open the BPEL process.

Adding a Partner Link

A BPEL partner link defines the link name, type, and the role of the BPEL process that interacts with the partner service.

In the scenario described earlier, you need to add a partner link to retrieve order acknowledgement information from selected Open Interface Views.

To add a partner link

1. Click BPEL Services in the Component palette.

   Drag and drop Oracle E-Business Suite (formerly known as Oracle Applications) from the BPEL Services list into the right Partner Link swim lane of the process diagram. The Oracle E-Business Suite Reference page appears.

   Enter a reference name in the Name field. For example, ViewAck.

2. 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 E-Business Suite is running.

   • You can create a new database connection by clicking the Create a New Database Connection icon.

     How to define a new database connection, see Create a New Database Connection.

   • You can select an existing database connection that you have configured earlier from the Connection drop-down list.

     The Service Connection page will be displayed with the selected connection information. The JNDI (Java Naming and Directory Interface) name corresponding to the database connection appears automatically in the Database Server JNDI Name field. Alternatively, you can specify a JNDI name.

     Note: When you specify a JNDI name, the deployment descriptor of the Oracle E-Business Suite Adapter must associate this JNDI name with configuration properties required by the adapter to access the database.

     The JNDI name acts as a placeholder for the connection used when your service is deployed to the BPEL server. This enables you to use different databases for development and later for production.

     Note: For more information about JNDI concepts, refer to Oracle Fusion Middleware User's Guide for Technology Adapters.

4. Click Next in the Service Connection dialog box. You can add an interface view by browsing through the list of open interface views available in Oracle E-Business Suite.

   If you are connecting to Oracle E-Business Suite Release 12, then the IREP File not present dialog box 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:

   • Click Yes to extract the Integration Repository data file.

     After the system successfully creates a local copy of the Integration Repository data file, next time when you connect to the database, you will find the IRep Data File field appears in the Operation dialog box indicating where your local copy exists with the creation date and time as part of the file name.

     Using the Local Integration Repository Data File

     

   • Click No to query the Integration Repository data file from the live database you are connecting to display the Integration Repository tree.

   In this example, click No. Click Next in the Operation page to open the Oracle E-Business Suite Module Browser.

5. Oracle E-Business Suite Module Browser includes the various product families that are available in Oracle E-Business Suite.

   Navigate to Order Management Suite (OM_PF) > Order Management (ONT) > Sales Order (ONT_SALES_ORDER) > Interface_Views to select OE_HEADER_ACKS_V.

   Selecting a view from the Module Browser

   

6. Click OK. The Application Interface page appears with the selected interface view.

   Adapter Configuration Wizard - Application Interface Page

   

7. Click Get Object to open the Oracle E-Business Suite Module Browser again to select another interface viewOE_LINE_ACKS_V using the same navigation path Order Management Suite (OM_PF) > Order Management (ONT) > Sales Order (ONT_SALES_ORDER) > Interface_Views.

   Click OK. The Application Interface page appears with the two selected views.

   Adapter Configuration Wizard - Application Interface Page

   

8. Click Next. The Operation Type page is displayed.

   Adapter Configuration Wizard - Operation Type Page

   

   Select the Perform an Operation on a Table radio button and then choose the Select check box.

   Note: Interface views can be used only for Select operations.

9. Click Next. The Select Table page is displayed.

   Adapter Configuration Wizard - Select Table Page

   

   Select OE_HEADERS_ACKS_V as the root database table for this service's query.

10. Click Next. The Define Primary Keys page is displayed.

    Adapter Configuration Wizard - Define Primary Keys Page for OE_HEADERS_ACKS_V

    

    Select the following primary keys for the OE_HEADERS_ACKS_V table:

    • ORIG_SYS_DOCUMENT_REF

    • ORDER_SOURCE_ID

    Click Next.

    Select the same primary keys for the OE_LINE_ACKS_V table.

    Adapter Configuration Wizard - Define Primary Keys Page for OE_LINE_ACKS_V

    

11. Click Next. The Relationships page appears.

    Click Create to open the Create Relationship dialog.

    Defining Relationships

    

    Enter the following information to define the relationship between the header and the detail table:

    • Select the OE_HEADERS_ACKS_V as the parent table and OE_LINES_ACKS_V as the child table.

    • Select the mapping type: OE_HEADERS_ACKS_V has a 1:M Relationship with OE_LINES_ACKS_V

      Note: If foreign key constraints between tables already exist in the database, then two relationships are created automatically while importing tables. One of the relationships is 1:M relationship from the source table, which is the table containing the foreign key constraints, to the target table. The other relationship is a 1:1 back pointer from the target table to the source table.

    • Select the Private Owned check box.

    • Associate the foreign key fields with the primary key fields:

      • OE_HEADERS_ACKS_V.ORDER_SOURCE_ID: ORDER_SOURCE_ID

      • OE_HEADERS_ACKS_V.ORIG_SYS_DOCUMENT_REF: ORIG_SYS_DOCUMENT_REF

    • The Relationship Name field is populated automatically by default. You can optionally specify a new name for the relationship you are creating.

    Click OK. The newly created relationship information appears in the Relationships page.

12. Click Next. The Attribute Filtering page appears. Leave default selections unchanged.

    Adapter Configuration Wizard - Attribute Filtering Page

    

    Click Next. The Define Selection Criteria page appears.

    Click Add to add a new parameter if necessary. Click Edit to use the graphical expression builder to create the expression. You can define your own custom Select SQL by modifying the predefined SQL string.

13. Click Next. The Advanced Options page appears.

    Click Next.

14. Click Finish. The wizard generates the WSDL file corresponding to the selected interface. This WSDL file is now available for the partner link.

Adding a Partner Link for File Adapter

Use this step to configure a BPEL process by adding a partner link for File Adapter. This allows the acknowledgement data to be written to an XML file.

To add a partner link for the file adapter

1. In Oracle 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 right after the partner link you just created. The File Adapter Reference page appears.

   Specifying a File Adapter Reference Name

   

   Enter a name for the File Adapter service, for example, WriteAckdata.

2. Click Next. The Adapter Interface page appears.

   Specifying the Adapter Interface

   

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

   The File Server Connection page appears. Leave the default value unchanged for the File Server JNDI Name field.

   Specifying File Server JNDI Name

   

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

   Specifying the Operation

   

4. Click Next to access the File Configuration page.

   Configuring the Output File

   

5. For the Directory specified as field, select the Logical Name radio button. Enter OutputDir as the Directory for Outgoing Files (logical name) and specify a naming convention for the output file, such as EventAck%yyMMddHHmmss%.xml.

   Tip: When you type a percent sign (%), you can choose from a list of date variables or a sequence number variable (SEQ) as part of the filename.

   Confirm the default write condition: Number of Messages Equals 1.

6. Click Next, and the Messages page appears. For the output file to be written, you must provide a schema.

7. Click Browse to access the Type Chooser.

8. Expand the node by clicking Project Schema Files > ViewAck_table.xsd and selecting OeHeaderAcksSelectInputParameters. Click OK.

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

   Populating the Selected Message Schema

   

9. 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.

Configuring Invoke Activities

Based on the scenario described earlier, you need to configure the following two Invoke activities:

1. To get the purchase order acknowledgement details by invoking the ViewAck partner link.

2. To write the purchase order acknowledgement information to an XML file by invoking WriteAckdata partner link for File Adapter.

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

1. In Oracle JDeveloper BPEL Designer, expand the BPEL Constructs from the Component Palette. Drag and drop the first Invoke activity from the Web Service section into the center swim lane of the process diagram, between the receiveInput and callbackClient activities.

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

   The value of the Operation field is automatically selected based on the associated partner link. For example, this Invoke activity is associated with an interface table partner link for 'select' operation only, the 'ViewAckSelect' service name with 'Select' operation is populated as the value.

3. Enter a name for the Invoke activity. In the Input tab, ensure the Input Variable radio button is selected. 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 Output tab, ensure the Output Variable radio button is selected. Click the Create icon next to the Output Variable field to create a new variable. The Create Variable dialog box appears.

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

   Click OK to close the Create Variable dialog.

   

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

   The Invoke activity appears in the process diagram.

To add the second Invoke activity for a File Adapter partner link to write acknowledgement details in an XML file:

1. In Oracle JDeveloper BPEL Designer, expand the BPEL Constructs from the Component Palette. Drag and drop the first Invoke activity from the Web Service section into the center swim lane of the process diagram, right after the first Invoke activity.

2. Link the Invoke activity to the WriteAckdata service for File Adapter. The Edit Invoke dialog appears.

3. Enter a name for the Invoke activity. In the Input tab, ensure the Input Variable radio button is selected. 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 to return to the Edit Invoke dialog box.

   

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

   The second Invoke activity appears in the process diagram.

Configuring an Assign Activity

The next task is to add an Assign activity to the process map. This is used to provide values to the input variables.

To configure an Assign activity

1. In Oracle JDeveloper BPEL Designer, expand the BPEL Constructs from the Component Palette.

   Drag and drop the Assign activity from the Basic Activities section into the center swim lane of the process diagram between the two Invoke activities 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, setAckdata.

3. Select the Copy Rules tab and expand the target trees:

   • In the From navigation tree, navigate to Variable > Process > Variables > Invoke_ViewAckSelect_OutputVariaable and select ViewAckSelect_inputParameters.

   • In the To navigation tree, navigate to Variable > Process > Variables > Invoke1_Write_InputVariable and select body.

   Drag the source node (ViewAckSelect_inputParameters) to connect to the target node (body) that you just specified. This creates a line that connects the source and target nodes. The copy rule is displayed in the From and To sections at the bottom of the Edit Assign dialog box.

   

   Click Apply and then OK.

   The BPEL diagram is completed.

   BPEL Process Diagram

   

   Click the WriteAck (composite.xml) to display the Oracle JDeveloper composite diagram:

   Note: Click the Source tab of WriteAck (composite.xml) to enter a value for the physical directory outputDir for the reference WriteAckdata (such as /usr/tmp).

   <property name="outputDir" type="xs:string" many="false" override="may">/usr/tmp</property>

   Oracle JDeveloper Composite Diagram

   

Run-Time Tasks for Views

After designing the SOA Composite application with BPEL process, the next steps are to deploy, run and monitor it.

1. Deploy the SOA Composite application with BPEL process

2. Test the deployed SOA Composite application with BPEL process

Deploying the SOA Composite Application with BPEL Process

To invoke the service from the BPEL client contained in the SOA composite, the SOA composite needs to be deployed to the Oracle WebLogic managed server. This can be achieved using Oracle JDeveloper. Once the composite is deployed, it can be tested from the Oracle Enterprise Manager Fusion Middleware Control Console.

Prerequisites

Before deploying the SOA composite with BPEL process using Oracle JDeveloper, you must have established the connectivity between the design-time environment and the run-time server. For more information, see Configuring the Data Source in Oracle WebLogic Server and Creating an Application Server Connection.

Note: If a local instance of the WebLogic Server is used, start the WebLogic Server by selecting Run > Start Server Instance from Oracle JDeveloper. Once the WebLogic Admin Server "DefaultServer" instance is successfully started, the <Server started in Running mode> and DefaultServer started message in the Running:DefaultServer and Messages logs should appear.

To deploy the SOA Composite application with BPEL process

1. Select the SOA Composite project in the Applications Navigator.

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

   For example, you can select Deploy >WriteAck > soa-server1 to deploy the process if you have the connection set up appropriately.

   Note: If this is the first time to set up server connection, then the Deployment Action window appears. Select 'Deploy to Application Server' and click Next.

   

   In the Deploy Configuration window, ensure the following information is selected before clicking Next to add a new application server:

   • New Revision ID: 1.0

   • Mark composite revision as default: Select this check box.

   • Overwrite any existing composites with the same revision ID: Select this check box.

   The steps to create a new Oracle WebLogic Server connection from JDeveloper are covered in Creating an Application Server Connection.

3. In the Select Server page, select 'soa-server1' that you have established the server connection earlier. Click Next.

   

4. In the SOA Servers page, accept the default target SOA Server ('soa-server1') selection.

   

   Click Next and Finish.

   If you are deploying the composite for the first time from your Oracle JDeveloper session, the Authorization Request window appears. Enter username and password information specified during Oracle SOA Suite installation. Click OK.

5. The BPEL process contained in the SOA Composite application is compiled and deployed. You can check the progress of the compilation in the Messages window.

Testing the Deployed SOA Composite Application with BPEL Process

Once the SOA Composite application 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 manually initiate and monitor the deployed SOA Composite application with 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.

   

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 > soa-infra (soa_server1) > default 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 'WriteAck') from the SOA Infrastructure.

   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 test results appear in the Response tab upon completion.

5. Click Launch Flow Trace in the Test Web Service page. The Flow Trace page is displayed.

   In the Trace section, you should find the sequence of the message flow for the service binding component (writeack_client_ep), BPEL component (WriteAck), and reference binding components (ViewAck and WriteAckdata). All involved components have successfully received and processed messages.

   If any error occurred during the test, you should find it in the Faults section.

6. Click your BPEL service component instance link (such as WriteAck) 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.

7. Verifying Records in Oracle E-Business Suite

   Log on to Oracle E-Business Suite with Purchasing, Vision Operations (USA) responsibility and select Purchase Order from the navigation menu.

   The Purchase Order forms is displayed.

8. Create a purchase order with the following header values:

   • Supplier: Enter a supplier information, such as 'Advanced Network Devices'.

   • Site: Select a site information, such as 'SANTA CLARA-ERS'.

9. On the Lines tab, enter a data row with the following values:

   • Type: Goods

   • Item: CM13139

   • Quantity: 1

   • Description: Hard Drive - 8GB

   • Promised: Enter any future date in the format of dd-mmm-yyyy (such as 23-JUN-2009)

10. Save your purchase order. The status of the purchase order is 'Incomplete'.

11. Click Approve. The Approve Document form appears.

    Click OK to confirm the approval.

    Note: Because the trading partner is set up and valid, the transmission method is automatically set to XML.

    

    The status of the purchase order is now changed to 'Approved'. For future reference, note the value of the PO, Rev field. For example, the PO number 5789.

    Once the purchase order is approved, the order details should be recorded in the system.

    After deploying the BPEL process contained in a SOA Composite application, the order acknowledgement information should be retrieved from the OE_HEADER_ACKS_V and OE_LINE_ACKS_V interface views.

    Validate the output file EventAck%yyMMddHHmmss%.xml in the specified output directory by opening the xml file to confirm the purchase order details. The document number in the xml file should be the same number you just approved in Oracle E-Business Suite.