2 Using Tables and Views

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 is displayed.

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, as shown in Figure 2-1.

   Figure 2-1 Creating a New BPEL Process Project

   
   Description of "Figure 2-1 Creating a New BPEL Process Project"
   
   

6. Click OK. The BPEL Process Project dialog box is displayed.

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, as shown in Figure 2-2.

   Figure 2-2 Specifying a Name for the New BPEL Process Project

   
   Description of "Figure 2-2 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, as shown in Figure 2-3.

   Figure 2-3 New BPEL Process Project

   
   Description of "Figure 2-3 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 is displayed.

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

3. Click Next. The Adapter Type dialog box is displayed.

4. Select Oracle Applications to specify the adapter you want to configure, as shown in Figure 2-4.

   Figure 2-4 Selecting OracleAS Adapter for Oracle Applications

   
   Description of "Figure 2-4 Selecting OracleAS Adapter for Oracle Applications"
   
   

5. Click Next. The Service Name dialog box is displayed.

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. The Service Name dialog box is displayed, as shown in Figure 2-5.

      Figure 2-5 Specifying the Service Name

      
      Description of "Figure 2-5 Specifying the Service Name"
      
      

7. Click Next. The Service Connection dialog box is displayed.

8. Enter the Java Naming and Directory Interface (JNDI) name in the Database Server JNDI Name field. 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 production.

   
   

   See Also: Oracle Application Server Adapter Concepts for understanding JNDI concepts

   
   

   Figure 2-6 shows how to create a new database connection.

   Figure 2-6 Creating a New Database Connection

   
   Description of "Figure 2-6 Creating a New Database Connection"
   
   

9. Click New to define a database connection. The Create Database Connection Wizard is displayed.

   
   

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

   
   

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

       The Type dialog box is displayed in Figure 2-7.

       Figure 2-7 Specifying the Connection Name and Type of Connection

       
       Description of "Figure 2-7 Specifying the Connection Name and Type of Connection"
       
       

11. Click Next. The Authentication dialog box is displayed.

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

13. Click Next. The Connection dialog box is displayed.

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

    The Connection dialog box is displayed, as shown in Figure 2-8.

    Figure 2-8 Specifying the New Database Connection Information

    
    Description of "Figure 2-8 Specifying the New Database Connection Information"
    
    

15. Click Next. The Test dialog box is displayed.

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

17. Click Next. The Service Connection dialog box is displayed, providing a summary of the database connection.

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 is displayed.

    
    

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

    
    

20. Click Get Object to open the Oracle Applications Module Browser. Figure 2-9 shows the Oracle Applications Module Browser.

    Figure 2-9 Adding a Table from the Oracle Applications Module Browser

    
    Description of "Figure 2-9 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, and 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, and then click Search.

    
    

22. The table is added to the Operation Objects. Repeat steps 2 and 3 to add more tables.

23. Click Next in the Operation dialog box. The Operation Type dialog box is displayed.

24. Select Perform an Operation on a Table, and then select Insert or Update, as shown in Figure 2-10.

    
    

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

    
    

    Figure 2-10 Selecting the Type of Operation

    
    Description of "Figure 2-10 Selecting the Type of Operation"
    
    

25. Click Next. The Select Table screen is displayed.

26. Select the root database table in the Select Table screen, 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. Figure 2-11 shows selecting a root table.

    Figure 2-11 Selecting a Root Table

    
    Description of "Figure 2-11 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 screen is displayed.

28. Select the primary key fields, as shown in Figure 2-12. You can also select multiple fields.

    Figure 2-12 Selecting the Primary Key

    
    Description of "Figure 2-12 Selecting the Primary Key"
    
    

29. Click Next. The Relationships screen is displayed.

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

    
    

    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.

       
       

    The Create Relationship dialog box is displayed in Figure 2-13.

    Figure 2-13 Defining Relationships Between Parent and Child Tables

    
    Description of "Figure 2-13 Defining Relationships Between Parent and Child Tables"
    
    

32. Click OK. The Relationships screen is displayed.

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

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, as shown in Figure 2-14.

   Figure 2-14 Dragging an Invoke Activity

   
   Description of "Figure 2-14 Dragging an Invoke Activity"
   
   

   The Edit Invoke dialog box is displayed.

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

   Figure 2-15 Configuring the Invoke Activity

   
   Description of "Figure 2-15 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. Figure 2-16 shows the Create Variable dialog box.

   Figure 2-16 Creating a Variable

   
   Description of "Figure 2-16 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. The following steps discuss this task:

1. Drag and drop the Assign activity to the process map. The Assign activity must be dropped in between the Receive and Invoke activities. Figure 2-17 shows the Assign activity after it has been added to the process map.

   Figure 2-17 Adding the Assign Activity

   
   Description of "Figure 2-17 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 is displayed. The Copy Rules tab is displayed by default. Click Create.

4. The Create Copy Rule dialog box is displayed. In the To group, expand the Variables node by clicking the plus sign next to it, and then select Expression from the From group to assign values to the input variables. Figure 2-18 shows assigning values to the input variables.

5. Figure 2-18 Assigning Values to Input Variables

   
   Description of "Figure 2-18 Assigning Values to Input Variables"
   
   

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

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

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 is displayed.

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, as shown in Figure 2-25.

   Figure 2-25 Creating a New BPEL Process Project

   
   Description of "Figure 2-25 Creating a New BPEL Process Project"
   
   

6. Click OK. The BPEL Process Project dialog box is displayed.

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, as shown in Figure 2-26.

   Figure 2-26 Specifying a Name for the New BPEL Process Project

   
   Description of "Figure 2-26 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, as shown in Figure 2-27.

   Figure 2-27 New BPEL Process

   
   Description of "Figure 2-27 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 is displayed.

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

3. Click Next. The Adapter Type dialog box is displayed.

4. Select Oracle Applications to specify the adapter you want to configure, as shown in Figure 2-28.

   Figure 2-28 Selecting OracleAS Adapter for Oracle Applications

   
   Description of "Figure 2-28 Selecting OracleAS Adapter for Oracle Applications"
   
   

5. Click Next. The Service Name dialog box is displayed.

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. The Service Name dialog box is displayed, as shown in Figure 2-29.

      Figure 2-29 Specifying the Service Name

      
      Description of "Figure 2-29 Specifying the Service Name"
      
      

7. Click Next. The Service Connection dialog box is displayed.

8. Enter the Java Naming and Directory Interface (JNDI) name in the Database Server JNDI Name field. 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 production.

   
   

   See Also: Oracle Application Server Adapter Concepts for understanding JNDI concepts

   
   

   Figure 2-30 shows how to create a new database connection.

   Figure 2-30 Creating a New Database Connection

   
   Description of "Figure 2-30 Creating a New Database Connection"
   
   

9. Click New to define a database connection. The Create Database Connection Wizard is displayed.

   
   

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

   
   

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

       The Type dialog box is displayed in Figure 2-31.

       Figure 2-31 Specifying the Name and Type of Connection

       
       Description of "Figure 2-31 Specifying the Name and Type of Connection"
       
       

11. Click Next. The Authentication dialog box is displayed.

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

13. Click Next. The Connection dialog box is displayed.

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

    The Connection dialog box is displayed in Figure 2-32.

    Figure 2-32 Specifying the New Database Connection Information

    
    Description of "Figure 2-32 Specifying the New Database Connection Information"
    
    

15. Click Next. The Test dialog box is displayed.

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

17. Click Next. The Service Connection dialog box is displayed, providing a summary of the database connection.

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 is displayed.

    
    

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

    
    

20. Click Get Object to open the Oracle Applications Module Browser. Figure 2-33 shows the Oracle Applications Module Browser.

    Figure 2-33 Selecting a View from the Oracle Applications Module Browser

    
    Description of "Figure 2-33 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.

    
    

22. The view is added to the Operation Objects section. Repeat steps 2 and 3 to add more tables.

23. Click Next in the Operation dialog box. The Operation Type dialog box is displayed.

24. Select Perform an Operation on a Table, and then select Select, as shown in Figure 2-34.

    
    

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

    
    

    Figure 2-34 Selecting the Type of Operation

    
    Description of "Figure 2-34 Selecting the Type of Operation"
    
    

25. Click Next. The Select Table screen is displayed.

26. Select the required database table, and then click Next. The Define Primary Keys screen is displayed.

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

28. Click Next. The Relationships screen is displayed.

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

    Figure 2-35 Available Object Models

    
    Description of "Figure 2-35 Available Object Models"
    
    

30. Click Next. The Define WHERE Clause dialog box is displayed. 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 is displayed.

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

33. Click Edit to create an expression. The Expression Builder is displayed.

34. Click Add to create a TopLink expression, as shown in Figure 2-36.

    Figure 2-36 Creating an Expression

    
    Description of "Figure 2-36 Creating an Expression"
    
    

35. Click Edit. The Choose screen is displayed.

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

37. Select Parameter in the Second Argument section, as shown in Figure 2-37.

    Figure 2-37 Selecting the Required Attributes

    
    Description of "Figure 2-37 Selecting the Required Attributes"
    
    
    
    

    See Also: ÒUnderstanding descriptors - Building Expressions in the Oracle TopLink documentation for more information about configuring expressions using the Expression Builder, refer to the following information: Oracle TopLink page on OTN http://www.oracle.com/technology/products/ias/toplink/index.html Oracle TopLink Documentation http://download.oracle.com/docs/cd/B14099_04/web.htm#toplink

    
    

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

39. Click Next. The Finish screen is displayed.

40. Click Finish. The Create Partner Link dialog box is displayed.

41. Click OK. The new BPEL process is created, as shown in Figure 2-38.

    Figure 2-38 New BPEL Process

    
    Description of "Figure 2-38 New BPEL Process "
    
    

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

Configuring 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, as shown in Figure 2-39.

   Figure 2-39 Dragging an Invoke Activity

   
   Description of "Figure 2-39 Dragging an Invoke Activity"
   
   

   The Edit Invoke dialog box is displayed.

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

   Figure 2-40 Configuring the Invoke Activity

   
   Description of "Figure 2-40 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. Figure 2-41 shows the Create Variable dialog box.

   Figure 2-41 Creating a Variable

   
   Description of "Figure 2-41 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. The following steps discuss this task:

1. Drag and drop the Assign activity to the process map. The Assign activity must be dropped in between the Receive and Invoke activities. Figure 2-42 shows the Assign activity after it has been added to the process map.

   Figure 2-42 Adding the Assign Activity

   
   Description of "Figure 2-42 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 is displayed. The Copy Rules tab is displayed by default. Click Create.

4. The Create Copy Rule dialog box is displayed. In the To group, expand the Variables node by clicking the plus sign next to it, and then select Expression from the From group to assign values to the input variables. Figure 2-43 shows assigning values to the input variables.

5. Figure 2-43 Assigning Values to the Input Variables

   
   Description of "Figure 2-43 Assigning Values to the Input Variables"
   
   

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

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