Create a connection pool and a data source to be able to connect to the database.

a.

In Oracle Enterprise Manager Application Server Control, click the Home link

.

b.

In the home OC4J instance page, select the Administration link.

c.

From the list of available administration tasks, click the Go To Task icon for the Services > JDBC Resources task.

d.

In the Connection Pools section, click the Create button.

In the Create Connection Pool - Application, accept default values and click Continue. You create a new connection pool for this tutorial.

Enter the following information in the Create Connection Pool form (leave defaults for the the fields not specified here): Replace the DB hostname, SID/Service name, username and password as appropriate to the Database you are trying to access to perform tasks in this tutorial.

After entering the above details, click the Test Connection button.

In the Test Connection window, click Test button to test the connection with the sample SQL statement.

Activate the Create Connection Pool window and observe the successful connection message. . If you get an error message, check the URL and credentials to ensure you've entered the right information.

Click Finish.

Observe the message that the ebiz_pool connection has been created. Also, observe the ebiz_pool entry in the Connection Pools section.

Now, create a Data Source.

In the JDBC Resources page, click Create button under the Data Sources section.

In the Create Data Source - Application & Type page, accept default values and click Continue. You are going to create a Managed Data Source. This data sources uses OC4J connection pooling capability and hence the connection pool created in the step above can be used in this case.

In the Create Data Source - Managed Data Source page, enter details for the data source as shown in the following table. After entering the above details, click Finish.

Observe the confirmation that the data source ebizDataSource has been created. Also observe the entry in the Data Sources section.

Create an Oracle Applications adapter connection. This connection is needed to propagate sales order information to E-Business suite.

In the Oracle Application Server Control page, select the Cluster Topology breadcrumb link at the top of the page.

.

Under the Members section > View By drop down list, select Applications to view all the applications in the OC4J home instance.

.

In the expanded tree of applications, click the link of the default application.

Under the Modules section, click the link corresponding to the AppsAdapter resource adapter module.

On the Resource Adapter: Oracle Applications Adapter page, select the Connection Factories link.

Under Connection Factories, click Create. (You want to use the create button near the top of the screen, not the one in the Shared Connection Pools section.)

In the Create Connection Factory: Select Interface page, accept default value for the Connection Factory Interface and click Continue.

In the Create Connection Factory page, enter eis/Apps/appsSample as the JNDI Location. In the Configuration Properties, enter jdbc/ebizDataSource as value for xADataSourceName. Accept default values for all other fields. Click Finish.

Observe the confirmation message that the connection factory has been created.

Restart SOA Suite: First go to Start > All Programs > [Oracle_Home] > Stop SOA Suite.

After viewing the message, now select Start > All Programs > [Oracle_Home] > Start SOA Suite.

In this step, you deploy a pre-packaged BPEL process. This process reads a sales order from a pre-configured XML file through an adapter and propagates that oder through AppsAdapter to E-Business Suite

Restart SOA Suite: Go to Start > All Programs > [Oracle_Home] > Stop SOA Suite.

After viewing the message, now select Start > All Programs > [Oracle_Home] > Start SOA Suite.

Access the AppsAdapter sample tutorial under [ORACLE_HOME] directory structure where you installed the SOA Suite. To achieve this task, navigate to [ORACLE_HOME]\bpel\samples\tutorials\150.AppsAdapter\OrderImportConcurrentProgram\bpel folder.

Right click on the bpel.xml file and select Edit to edit this xml file.

Edit bpel.xml to specify the physical directory for the File Adapter - synchronous read operation. Ensure that the code segment related tot he getOrderDetails entry has the LogicalDirectory entry set to the folder path where your AppsAdapter samples are installed, as shown below. (this path may be different for you depending on the directory where you installed your SOA suite. For example, replace c:\SOASuite10131 with the folder where you installed your SOA suite.)

<partnerLinkBinding name="getOrderDetails">
<property name="wsdlLocation">getOrderDetails.wsdl</property>
<property name="inputDir" type="LogicalDirectory">
c:/SOASuite10131/bpel/samples/tutorials /150.AppsAdapter/OrderImportConcurrentProgram/inputDir
</property>
<property name="retryInterval">60</property>
</partnerLinkBinding>

After making changes to the bpel.xml file as necessary, you need to compile and deploy the BPEL process. To perform this task, go to start > All Programs > Oracle_SOA > Oracle BPEL Process Manager > Developer Prompt.

This opens up a command prompt window. In the command prompt window.

In the command prompt window, change directory to the folder where the build.xml of the OrderImportDemo project is present. The following screenshot shows a possible path to the directory.

Then run the obant command to compile and deploy the BPEL process. Observe the message that the Build is successful. (Note that the following screenshot shows only partial message that you see during the compile/deploy process)

Edit the order_data.xml file. Open this file from the directory structure [BPEL_samples]\150.AppsAdapter\OrderImportConcurrentProgram\inputDir folder.

Right click on the order_data.xml file and select Edit.

Find and replace all the three occurrences of "order_id_01" with a non-default value. For example: 123456. (Note: You need to do this because there might already be an order with that particular orderid within E-Business Suite.) Save the file

Go to BPEL Console and initiate the process by supplying a dummy string as input. In a browser window, provide the URL of the format http://<host>:<port>/BPELConsole (The default port is 8888)
for starting the BPEL Console. If you receive an error that the page cannot be opened, go to Start > All Programs > Oracle_SOA > Oracle BPEL Process Manager > BPEL Control to start the console page.

The default login userid=bpeladmin passwd=welcome1

Select the OrderImportDemo link from the Deployed BPEL Processes list under the Dashboard or the BPEL Processes tab.

In the Initiate section, provide dummy as the value to initiate a test instance and click the Post XML Message button.

Observe the message that the Test instance is initiated and that the instance is being processed asynchronously. Click to view the Visual Flow diagram.

The resulting visual flow diagram shows the successful invocation of the Open Interface and Concurrent Program services.

You can go to Audit tab and click View Raw XML link to view the XML file that is generated for the specific instance.

A snippet of the resulting XML file is shown below. Observe the order ID value 123456 that you provided in a previous step.

Scroll down the list of responsibilities available in the Navigator section, and click the responsibility Order Management Super User, Vision Operations (USA). Then select Sales Order from the resulting pane (as shown in the highlighted selection of the second frame of the screenshot). Note: Your screen may show more values than shown in the following screenshot where some portions of the screen are stripped out.

The above step opens up one Oracle Applications 11i browser window that shows a message asking you not to close the window and a Oracle Applications - Vision window that contains the Order Management Super User, Vision Operations (USA) Navigator form and the Sales Orders - [New] form.

Observe that the Order Returns-->Sales Order form is active now.

In the Sales Order form press F11 to enable search for any Order. In the Customer PO field enter value order_id_01 (order id present in the orders xml file) and press CTRL+F11 keys.

Details of newly created order with Order id = 'order_id_01' could be seen.

Press on the Line items tab. Verify the quantity and item type of the order is same as that present in the raw xml data that was generated while the BPEL process was tested with an initiator in step 5i above.

Launch Jdeveloper and create a connection to E-Business Suite using the apps schema.

In JDeveloper select the Connections tab (choose View > Connection Navigator if it's not displayed). Right click on Database and select New Database Connection to open the database wizard.

Enter appropriate values to connect to your database. This tutorial uses Connection name as ebiz and username and password as apps. The following screenshots show the Database connection creation process.

.

If you received an error when you test the connection, verify your database hostname, port, and SID to make sure they are accurate. When the connection is successfully created, you view an entry for ebiz in the Database connection navigator as shown in the following screenshot.

1.

Open Oracle JDeveloper BPEL Designer. Activate the Applications Navigator tab. Right click on the Applications node and select New Applications. .

Provide name of the application as BPEL_eBuz. Click OK.

Do not create the Project file yet. Click Cancel.

Now, create a BPEL Process project. To achieve this task, perform the following steps:

Right click on BPEL_eBuz node in the Application Navigator and select New.

In the New Gallery dialog box select Projects from the General Categories section and select BPEL Process Project from the Items section. Click OK.

The BPEL Process Project dialog box is displayed.
In the BPEL Process Name field, enter a descriptive name. For example, OrderImportDemo.

Observe that a BPEL Process project by name OrderImportDemo has been created in the Applications section and view the OrderImportDemo.bpel visual process flow diagram.

In the Create Partner LInk window click the Define adapter services icon in the WSDL Settings section.

In the Adapter Configuration Wizard, click Next for the Welcome screen. Select Oracle Applications as the Adapter you would like to configure. Click Next.

In step 2, name the service as DeleteOrder and provide description as Deletes an existing order from E-Business suite and click Next.

In Step 3, select the database connection for your applications that you created earlier in this tutorial and click Next.

In the Oracle Applications Module Browser window, navigate to the Delete Order entry through this path:
Product Families > Order Management Suite (OM_PF) > Order Management (ONT) > Sales Order (ONT_SALES_ORDER) > PLSQL > Process Order API (OE_ORDER_PUB) > DELETE ORDER (DELETE_ORDER)

Click OK.

In the Adapter Configuration Wizard step 4, select DELETE ORDER API and click Next.

With this step, you finished defining Oracle Application Adapter Service called DeleteOrder. Click Finish to complete the process.

You are now returned to the Create Partner Link window, populated with WSDL Settings information based on the adapter you created in the previous steps. Click OK.

Now return to the BPEL Designer and observe that DeleteOrder is now available as a service.

Save all your work by .

From the Component Palette, select Process Activities from the drop down list and then select the Invoke activity.

Drag the Invoke activity to the OrderImportDemo.bpel visual process flow diagram beneath the receiveInput activity.

Drag the Invoke activity's connector to the Oracle E-Business Suite DeleteOrder partnerlink. (Hold the right arrow button and drag it to the DeleteOrder icon)

In the Edit Invoke dialogue box, enter CallDeleteOrder as the name of the activity. Click the Automatically Create Input Variables button to select the input variable and similarly for the output variable.

Observe that the Input Variable and Output Variable fields are populated. Click OK.

Drag and drop an Assign Activity from the Component Palette and drop in the flow before the CallDeleteOrder activity.

Double click on the Assign_1 activity to set the values as follows for setting the input value for the CallDeleteOrder invoke activity. On the General tab, provide name as AssignDeleteOrder. On the Copy Operation tab, select the Copy operation from the Create option drop down list.

In the Create Copy Operation window:

in the From section expand the Variables node - Variables > Process > Variables > inputVariable > payload > client:OrderImportDemoProcessRequest > client:input

In the To sections expand the Variables node - Variables > Process > Variables > CallDeleteOrder_DeleteOrder_InputVariable > InputParameters > ns2:InputParameters >ns2:P_HEADER_ID

Click OK.

Click OK.

In the BPEL Process Deployer window > Integration Server Connection, click New to create a new Integration Server connection.

In Step 1, provide Connection Name as SOA. Click Next.

In step 2, click the Configure a new application server icon to create a new application server connection.

In the Create Application Server Connection wizard window, provide Connection Name as ApplicationServerConnection and select Oracle Application Server 10g 10.1.3. as Connection Type. Click Next.

Provide username and password that is appropriate for your SOA Suite installation. Click Next.

Ensure that the hostname and port are accurate and click Next.

Test the Connection and click Finish. Ensure that the status is Success. Otherwise, verify your connection details and test the connection again.

With this step, you completed creating an application server instance connection.

Now, control returns to Step 2 of the Create Integration Server Connection window. Click Next.

In Step 3, Click Test Connection to ensure that the connection is successful. Click Finish.

Now the control returns to the BPEL Process Deployer window. Click Ok.

In the Deployment Properties window, click OK. The version will be different if you tried deployment of this application for more than one time.

View the Apache Ant - Log window to make sure that the deployment is successful. Save all your files in Oracle JDeveloper.

Open a browser window and view the BPEL Console (http://<hostname>:8888/BPELConsole or Start > All Programs > Oracle SOA Suite > Oracle BPEL Process Manager > BPEL Control) to verify that the process OrderImportDemo is available as one of the deployed BPEL Processes on the BPEL server. Click OK.

We query for existing Orders. Click on Cust Name and click F11. When the fields turn blue in color, enter Business World in the Cust Name field and press CTRL - F11. The form is now populated with information corresponding to customer Business World. Also observe there is a line item corresponding to this customer entry. If you do not see a line item, us the up and down arrow keys on your key board to select an Order that contains at least one line item.

The above screenshot shows an order with Order ID 57686 in the system. You may find a different order.

Now find out the corresponding header_id for the Order selected in the step above. We require header_id because Order Management works on internal values. Typically Order number is provided by the external systems and there is a possibility that there can be two Sales Order with the same Order Number(coming from two different companies).

In JDeveloper, go to Tools > SQL Worksheet. Select ebiz as the database connection from the Select Connection window. This is the database connection you created in an earlier step in this tutorial.

In the ebiz window, enter the following SQL statement and click the Execute SQL Statement button to execute the command.

SELECT * FROM oe_order_headers_all
WHERE order_number=57686

Observe that the header_id value for order number 57686 is 104550.

Open a new Internet browser and enter the following URL in the address bar to log into the Oracle BPEL Console:

http://localhost:8888/BPELConsole

If not already logged in, log in to the BPEL Control by providing the user name as oc4jadmin and the password as welcome1 (or the one you provided at the time of the Oracle SOA Suite installation).

Click on OrderImportDemo link and in the Initiate tab, provide the Order ID you identified in a previous step, in the input parameter field. click Post XML Message.

View the message that the instance has been successfully processed asynchronously. Click the Visual Flow diagram to view the visual flow of the BPEL process diagram.

Now, lets verify what the result from the API that actually performs action on the sales orders will be. Click the CallDeleteOrder activity to delete the order with the order ID supplied in the test instance.

In the Activity Audit Trail window, observe the highlighted message that shows status of the delete action.

In this tutorial example, observe the message data OE_PC_DELETE_VIOLATION with message reason as Order has been booked. This message indicates that there is a delete violation constraint defined on the order table and hence this order cannot be deleted. Since the specified order has already been booked, it cannot be deleted and hence the above constraint is raised.

<X_RETURN_STATUS> E <X_RETURN_STATUS>

<X_MSG_COUNT>1<X_MSG_COUNT>

<X_MSG_DATA> ONT$#0; OE_PC_DELETE_VIOLATION ....

Conclusion: The above is a valid error message from the Order management system. If an Order has been booked then you can't delete it. Try to delete another order.

However, if your order with the specified order ID is not yet booked, it can be deleted from the sales order related tables and hence you will see a message that it has been deleted. A successful deletion message would appear as shown in the following screenshot:

<X_RETURN_STATUS> S <X_RETURN_STATUS>

<X_MSG_COUNT> 0 <X_MSG_COUNT>

<X_MSG_DATA xsi:nil= TRUE ...

If you got the above message that the deletion is successful, go back to Oracle Applications and search your order number for the line item that you deleted.

This process proves the capability of the E-Business adapter to invoke a PL/SQL API as a Service inside the BPEL flow.