Using Tables and Views of Oracle Application Server Adapter

This chapter covers the following topics:

• Overview of Tables and Views
• Design-Time Tasks for Interface Tables
• Creating a New BPEL Project
• Adding a Partner Link
• Configuring the Invoke Activity
• Configuring the Assign Activity
• Run-Time Tasks for Interface Tables
• Deploying the BPEL Process
• Testing the BPEL Process
• Design-Time Tasks for Views
• Creating a New BPEL Project
• Adding a Partner Link
• Configuring the Invoke Activity
• Configuring the Assign Activity
• Run-Time Tasks for Views
• Deploying the BPEL Process
• Testing the BPEL Process
• Troubleshooting and Debugging

Overview of Tables and Views

OracleAS Adapter for Oracle Applications uses interface tables to insert and update data in Oracle Applications, and uses views to retrieve data from Oracle Applications. This chapter describes the following interfaces:

• Interface Tables

• Views

Interface Tables

OracleAS Adapter for Oracle Applications use interface tables to insert data in Oracle Applications. For example, by using interface tables, you can insert a purchase order into Oracle Applications to generate the sales order automatically. Data is never loaded directly into Oracle Applications 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.

OracleAS Adapter for Oracle Applications use open interface tables to integrate with Oracle Applications through direct database access. The OracleAS Adapter for Oracle Applications 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 Applications.

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 Applications 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

OraclesAS Adapter for Oracle Applications uses views to retrieve data from Oracle Applications. For example, by using views, you can retrieve information about your customers from the required tables in Oracle Applications.

OracleAS Adapter for Oracle Applications uses views to retrieve data from Oracle Applications. Views allow only simple definition. By using views, you can get synchronous data access to Oracle Applications. In OracleAS Adapter for Oracle Applications, views are created on base tables as well as interface tables. These views can be used only for select operations.

In the Oracle Applications 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 OracleAS Adapter for Oracle Applications to use interface tables. It describes the steps to configure OracleAS Adapter for Oracle Applications using the Adapter Configuration Wizard in Oracle JDeveloper.

Prerequisites to Configure Interface Tables

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

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

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

• Create a new BPEL project.
• Add a partner link.
• Configure the Invoke activity.
• Configure the Assign activity.

Creating a New BPEL Project

To create a new BPEL project

1. Open JDeveloper BPEL Designer.

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

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

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

5. Select BPEL Process Project from the Items list.

   Creating a New BPEL Process Project

   

6. Click OK. The BPEL Process Project dialog box appears.

7. In the BPEL Process Name field, enter a descriptive name. For example, InsertSalesOrder.

8. From the Template list, select Asynchronous BPEL Process. Keep the default selection for Use Default in the Project Content section.

   Specifying a Name for the New BPEL Process Project

   

9. Click OK. A new asynchronous BPEL process, with the required source files including bpel.xml, InsertSalesOrder.xml, and InsertSalesOrder.wsdl, is created.

   New BPEL Process Project

   

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

1. Drag and drop PartnerLink into the border area of the process diagram. The Create Partner Link dialog box appears.

2. Click Define Adapter Service in the WSDL Settings section. The Adapter Configuration Wizard appears.

3. Click Next. The Adapter Type dialog box appears.

4. Select Oracle Applications to specify the adapter you want to configure.

   Selecting OracleAS Adapter for Oracle Applications

   

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

6. Enter the following information:

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

   2. In the Description field, enter a description for the service. This is an optional field.

      Specifying the Service Name

      

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

   Specifying a Database Connection

   

8. Select an existing database connection from the Connection list. Alternatively, you can click New to define a database connection. The Create Database Connection Wizard appears.

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

9. Enter the following information in the Type dialog box:

   1. In the Connection Name field, specify a unique name for the database connection.

   2. From the Connection Type list, select the type of connection for your database connection.

      Specifying the Connection Name and Type of Connection

      

10. Click Next. The Authentication dialog box appears.

11. Enter information in the following fields:

    1. In the UserName field, specify a unique name for the database connection.

    2. In the Password field, specify a password for the database connection.

12. Click Next. The Connection dialog box appears.

13. Enter information in the following fields:

    1. From the Driver list, select Thin.

    2. In the Host Name field, specify the host name for the database connection.

    3. In the JDBC Port field, specify the port number for the database connection.

    4. In the SID field, specify a unique SID value for the database connection.

       Specifying the New Database Connection Information

       

14. Click Next. The Test dialog box appears.

15. Click Test Connection to confirm that the specified information establishes a connection with the database.

16. Click Next. The Service Connection dialog box returns, providing a summary of the database connection.

17. 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 Applications 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 Application Server Adapter Concepts.

18. Click Finish to complete the process of creating a new database connection.

    Once you have completed creating a new connection for the service, you can add an interface table by browsing through the maps available in Oracle Applications.

19. Click Next in the Service Connection dialog box. The Operation dialog box appears.

    Note: If you are connecting to a pre-11.5.10 Oracle Applications instance, you would be required to select the interface type in the Adapter Configuration Wizard. Select Tables/Views/APIs/Concurrent Programs to proceed.

20. Click Get Object to open the Oracle Applications Module Browser.

    Adding a Table from the Oracle Applications Module Browser

    

    Oracle Applications Module Browser includes the various product families that are available in Oracle Applications. For example, Applications Technology or Order Management Suite are product families in Oracle Applications. The product families contain the individual products. For example, Order Management Suite contains the Order Management product. The product contains 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. Tables can be found under the OpenInterfaces category.

21. Select a table, then click OK.

    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.

    The table is added to the Operation Objects.

22. Repeat the preceding two steps to add more tables.

23. Click Next in the Operation dialog box. The Operation Type dialog box appears.

24. Select Perform an Operation on a Table, and then select Insert or Update.

    Selecting the Type of Operation

    

    Note: You can only insert data into the interface tables even though the Delete and Select options are enabled.

25. Click Next. The Select Table dialog box appears.

26. Select the root database table in the Select Table dialog box, 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.

    Selecting a Root Table

    

    Note: If the required root database table for the operation is not imported, then click Import Tables. In addition, you can reimport a table and overwrite the previously configured table definition. However, in this case, you will lose any custom relationships that you may have defined on that table as well as any WHERE clause if a root table is imported.

27. Click Next. The Define Primary Keys dialog box appears.

28. Select the primary key fields. You can also select multiple fields.

    Selecting the Primary Key

    

29. Click Next. The Relationships dialog box appears.

30. Click Create Relationship to define a new relationship. The Create Relationship dialog box appears.

    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.

31. Specify the following information to create a new relationship:

    1. Select the parent and child tables.

    2. Select the mapping type (1:M, 1:1, or 1:1 with Foreign Key on Child Table).

    3. Associate the foreign key fields to the primary key fields.

    4. Enter a name for the relationship you are creating. It is optional to specify a name. By default, a name is generated for the relationship.

       Note: You can select only those tables as parent tables that can be accessed from the root table.

       Defining Relationships Between Parent and Child Tables

       

32. Click OK. The Relationships dialog box appears.

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

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

Configuring the Invoke Activity

To configure the Invoke activity

1. Drag Invoke from the Component palette and drop it at the location where you want to insert the invoke activity in your BPEL process.

   Dragging an Invoke Activity

   

   The Edit Invoke dialog box appears.

2. Double-click Invoke in the process map to open the Invoke dialog box. The General tab is selected by default.

   Configuring the Invoke Activity

   

3. In the Partner Link box, select the partner link to invoke. This is the partner link that you configured in the previous section.

4. Click the Create icon next to the Input Variable field. Enter a descriptive name for the variable in the Create Variable dialog box that appears. You can also accept the default name. Click OK.

   Creating a Variable

   

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

Configuring the 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 the Assign activity

1. Drag and drop the Assign activity to the process map. The Assign activity must be dropped in between the Receive and Invoke activities.

   Adding the Assign Activity

   

2. You now need to configure the Assign activity. Double-click the Assign activity in the process map.

3. The Assign dialog box appears. The Copy Rules tab appears by default. Click Create.

4. The Create Copy Rule dialog box appears. In the To group, expand the Variables node by clicking the plus sign next to it, then select Expression from the From group to assign values to the input variables.

   Selecting the From variable and To variable will populate the XPath input boxes at the bottom of the window.

   Assigning Values to Input Variables

   

5. After assigning values to the input variables, click OK.

6. Select Make from the Run menu or press Ctrl+F9 to compile the BPEL process to check for errors. The compilation result appears.

Run-Time Tasks for Interface Tables

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 need to deploy the BPEL process to a BPEL server before you can run it. The BPEL process is first compiled, and then deployed to the BPEL server.

To deploy the BPEL process

1. Select the BPEL project in the Applications window.

2. Right-click the project name. Select Deploy from the menu that appears.

3. Select Local BPEL Server followed by Deploy to Default Domain, if you are deploying the process on the local BPEL server.

   Deploying the BPEL Process

   

   Note: You can select Invoke Deployment Tool if you want to deploy to a different BPEL server.

4. The Password Prompt dialog box appears. Enter the password for the default domain in the Domain Password field. Click OK.

   Specifying the Domain Password

   

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

   Messages Window

   

Testing the BPEL Process

Once the BPEL process is deployed, it can be seen in the BPEL console. You can manage and monitor the process from the BPEL console. You can also test the process and the integration interface by manually initiating the process.

To manually initiate and monitor the BPEL process

1. To open the BPEL Console, click Start, and then select Programs. In the Programs menu, select Oracle - ORACLE_HOME, Oracle BPEL Process Manager 10.1.3, and then select BPEL Console.

2. The BPEL console login dialog box appears. Select Default in the Domain box. Enter the password for the default domain in the Password field, and then click Login.

3. The Oracle BPEL console page appears. The list of deployed processes is shown under Deployed BPEL Processes.

   Deployed BPEL Processes

   

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

5. Click Post XML Message to initiate the process.

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

   BPEL Console Initiate Page

   

7. The audit trail provides information about the steps that have been executed. You can check the audit trail by clicking the Audit Instance icon.

   Note: To confirm that the records have been written into the open interface tables, you can write the SQL SELECT statements and fetch the results showing the latest records inserted into the open interface tables. Alternatively, open the forms of the module for which the record has been inserted, and then check for changes in the form.

Design-Time Tasks for Views

This section describes the steps to configure OracleAS Adapter for Oracle Applications using the Adapter Configuration Wizard in Oracle JDeveloper.

Prerequisites to Configure Views

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

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

• Create a new BPEL project.
• Add a partner link.
• Configure the Invoke activity.
• Configure the Assign activity.

Creating a New BPEL Project

To create a new BPEL project

1. Open JDeveloper BPEL Designer.

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

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

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

5. Select BPEL Process Project from the Items list.

   Creating a New BPEL Process Project

   

6. Click OK. The BPEL Process Project dialog box appears.

7. In the BPEL Process Name field, enter a descriptive name, for example, SelectCustomer.

8. From the Template list, select Asynchronous BPEL Process. Keep the default selection for Use Default in the Project Content section.

   Specifying a Name for the New BPEL Process Project

   

9. Click OK. A new asynchronous BPEL process with the required source files including bpel.xml, SelectCustomer.xml, and SelectCustomer.wsdl is created.

   New BPEL Process

   

Adding a Partner Link

This section describes how to create an OracleAS adapter for the application service by adding a partner link to the BPEL process. A BPEL partner link defines the link name, type, and the role of the BPEL process that interacts with the partner service.

To add a partner link

1. Drag and drop PartnerLink into the border area of the process diagram. The Create Partner Link dialog box appears.

2. Click Define Adapter Service in the WSDL Settings section. The Adapter Configuration Wizard appears.

3. Click Next. The Adapter Type dialog box appears.

4. Select Oracle Applications to specify the adapter you want to configure.

   Selecting OracleAS Adapter for Oracle Applications

   

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

6. Enter the following information:

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

   2. In the Description field, enter a description for the service. This is an optional field.

      Specifying the Service Name

      

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

   Specifying a Database Connection

   

8. Select an existing database connection from the Connection list. Alternatively, you can click New to define a database connection. The Create Database Connection Wizard appears.

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

9. Enter the following information in the Type dialog box:

   1. In the Connection Name field, specify a unique name for the database connection.

   2. From the Connection Type list, select the type of connection for your database connection.

      Specifying the Name and Type of Connection

      

10. Click Next. The Authentication dialog box appears.

11. Enter information in the following fields:

    1. In the UserName field, specify a unique name for the database connection.

    2. In the Password field, specify a password for the database connection.

12. Click Next. The Connection dialog box appears.

13. Enter information in the following fields:

    1. From the Driver list, select Thin.

    2. In the Host Name field, specify the host name for the database connection.

    3. In the JDBC Port field, specify the port number for the database connection.

    4. In the SID field, specify a unique SID value for the database connection.

       Specifying the New Database Connection Information

       

14. Click Next. The Test dialog box appears.

15. Click Test Connection to determine whether the specified information establishes a connection with the database.

16. Click Next. The Service Connection dialog box appears, providing a summary of the database connection.

17. 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 Applications 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: Oracle Application Server Adapter Concepts for understanding JNDI concepts.

18. Click Finish to complete the process of creating a new database connection.

    Once you have completed creating a new connection for the service, you can add a view by browsing through the maps available in Oracle Applications.

19. Click Next in the Service Connection dialog box. The Operation dialog box appears.

    Note: If you are connecting to a pre-11.5.10 Oracle Applications instance, then you would be required to select the interface type in the Adapter Configuration Wizard. Select Tables/Views/APIs/Programs to proceed.

20. Click Get Object to open the Oracle Applications Module Browser.

    Selecting a View from the Oracle Applications Module Browser

    

21. Select a view, and then click OK.

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

    The view is added to the Operation Objects section.

22. Repeat the preceding two steps to add more views.

23. Click Next in the Operation dialog box. The Operation Type dialog box appears.

24. Select Perform an Operation on a Table, then select Select.

    Selecting the Type of Operation

    

    Note: You can perform only the Select operation on views.

25. Click Next. The Select Table dialog box appears.

26. Select the required database table, and then click Next. The Define Primary Keys dialog box appears.

27. Select the primary key fields. You can also select multiple fields to define primary keys.

28. Click Next. The Relationships dialog box appears.

29. Click Next. The Object Model dialog box appears, which shows the object models that are created from the imported table definitions, including any relationships that you may have defined.

    Available Object Models

    

30. Click Next. The Define WHERE Clause dialog box appears. If your service contains a SELECT query, then you can customize the WHERE clause of the SELECT statement.

31. Click Add to add a new parameter. The Parameter Name dialog box appears.

32. Enter a name for the new parameter, and then click OK.

33. Click Edit to create an expression. The Expression Builder appears.

34. Click Add to create a TopLink expression.

    Creating an Expression

    

35. Click Edit. The Choose dialog box appears.

36. Select the required attribute, and then click OK.

37. Select Parameter in the Second Argument section.

    Selecting the Required Attributes

    

    Note: For more information about configuring expressions using the Expression Builder, refer to the Oracle TopLink page on OTN, and the TopLink documentation in the Oracle Application Server documentation library.

38. Click OK. The Define WHERE Clause dialog box appears with the selected values.

39. Click Next. The Finish dialog box appears.

40. Click Finish. The Create Partner Link dialog box appears.

41. Click OK. The new BPEL process is created.

    New BPEL Process

    

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

Configuring the Invoke Activity

To configure the Invoke activity

1. Drag Invoke from the palette and drop it at the location where you want to insert the invoke activity in your BPEL process.

   Dragging an Invoke Activity

   

   The Edit Invoke dialog box appears.

2. Double-click Invoke in the process map to open the Invoke dialog box. The General tab is selected by default.

   Configuring the Invoke Activity

   

3. In the Partner Link box, select the partner link to invoke. This is the partner link that you configured in the previous section.

4. Click the Create icon next to the Input Variable field. Enter a descriptive name for the variable in the Create Variable dialog box that appears. You can also accept the default name. Click OK.

   Creating a Variable

   

5. In the Invoke dialog box, click Apply, and then click OK. The BPEL process is configured.

Configuring the 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 the Assign activity

1. Drag and drop the Assign activity to the process map. The Assign activity must be dropped in between the Receive and Invoke activities.

   Adding the Assign Activity

   

2. You must now configure the Assign activity. Double-click the Assign activity in the process map.

3. The Assign dialog box appears. The Copy Rules tab appears by default. Click Create.

4. The Create Copy Rule dialog box appears. In the To group, expand the Variables node by clicking the plus sign next to it, then select Expression from the From group to assign values to the input variables.

   Assigning Values to the Input Variables

   

5. After assigning values to the input variables, click OK.

6. Select Make from the Run menu or press Ctrl+F9 to compile the BPEL process to check for errors. The compilation result appears.

Run-Time Tasks for Views

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

1. Deploy the BPEL process.
2. Test the BPEL process.

Deploying the BPEL Process

You need to deploy the BPEL process before you can run it. The BPEL process is first compiled, and then deployed to the BPEL server. The following steps discuss deploying the BPEL process to a BPEL server:

To deploy the BPEL process

1. Select the BPEL project in the Applications window.

2. Right-click the project name. Select Deploy from the menu that appears.

3. Select Local BPEL Server followed by Deploy to Default Domain, if you are deploying the BPEL process on the local BPEL server.

   Deploying the BPEL Process

   

   Note: You can select Invoke Deployment Tool if you want to deploy to a different BPEL server.

4. The Password Prompt dialog box appears. Enter the password for the default domain in the Domain Password field. Click OK.

   Specifying the Domain Password

   

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

   Messages Window

   

Testing the BPEL Process

Once the BPEL process is deployed, it can be seen in the BPEL console. You can manage and monitor the process from the BPEL console. You can also test the process and the integration interface by manually initiating the process. The following steps discuss manually initiating and monitoring the BPEL process:

To manually initiate and monitor the BPEL process

1. To open the BPEL Console, click Start, and then select Programs. In the Programs menu, select Oracle - ORACLE_HOME, Oracle BPEL Process Manager 10.1.3, and then select BPEL Console.

2. The BPEL Console login page appears. Select Default in the Domain box. Enter the password for the default domain in the Password field, and then click Login.

3. The Oracle BPEL Console appears. The list of deployed processes is shown under Deployed BPEL Processes on the BPEL Console page.

   Deployed BPEL Processes

   

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

5. Click Post XML Message to initiate the process.

6. The BPEL process is now initiated. You can check the process flow by clicking the Visual Flow icon on the BPEL Console Initiate page.

   BPEL Console Initiate Page

   

7. The audit trail provides information about the steps that have been executed. You can check the audit trail by clicking the Audit Instance icon.

Troubleshooting and Debugging

If you experience problems with your Open Interface integration, you can take the following troubleshooting steps:

• If you're using complex (that is, Boolean) datatypes, ensure that PL/SQL wrappers are applied on the target instance of the concurrent program.

• Examine your transaction data in the Open Interface Tracking Form.

• Check the status of the concurrent programs by Request ID.

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

Enabling Debugging

You can enable debugging for interface tables 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.