Contents


Integrating Oracle E-Business Suite through the Oracle Open Interface and Concurrent Program using WebSphere Adapters

Comments

WebSphere Adapter for Oracle E-Business Suite (hereafter called Oracle EBS Adapter), as an important product of the IBM® WebSphere Adapter family, provides first class integration with Oracle E-Business Suite (hereafter called Oracle EBS). It can help you integrate existing Oracle EBS system resources into service-oriented architecture (SOA) business solutions.

Oracle E-Business Suite is a new generation Enterprise Resource Planning (ERP) that has great deal of users in the worldwide ERP market. To enable external interaction, Oracle EBS exposes several kinds of interface types, such as Oracle Open interface, Concurrent Program, Java™ Service, XML Gateway, PL/SQL API, and so on. Therefore, to integrate with Oracle EBS through these interfaces is a big challenge that IT engineers are facing.

This article illustrates how to integrate Oracle EBS with WebSphere Process Server through Oracle Open Interface and Concurrent Program using WebSphere Adapter for Oracle E-Business Suite.

WebSphere Adapter

WebSphere Adapters implement the Java EE Connector Architecture (JCA) and Enterprise MetaData Discovery specifications to provide a perfect and quick integration experience with graphical discovery tools without resorting to writing code. It delivers generic technology and business application adapters with wizards that quickly and easily service enable legacy applications, ERP, HR, CRM, and supply chain systems.

WebSphere Adapters build up the communication bridge between the enterprise applications (like SAP®, Siebel®, Oracle e-Business Suite, and so on) and WebSphere platform (Figure 1). It also enables the WebSphere platform with the capability to access the external data sources through various technologies, such as JDBC, e-mail, FTP, and so on.

Figure 1. WebSphere Adapter family overview
WebSphere Adapter family overview
WebSphere Adapter family overview

WebSphere Adapter for Oracle E-Business Suite provides inbound and outbound bi-directional interaction with Oracle EBS.

Inbound

Inbound processing enables an application to receive notification when objects in Oracle EBS system are changed. The change causes a trigger, or a business event, to update the event store with information about the change. Periodically, the adapter polls the event store, retrieves, and processes events, and then delivers them to the export component of a module that is part of an application that runs in WebSphere Process Server or WebSphere Enterprise Service Bus. Figure 2 shows the inbound processing.

Figure 2. Processing inbound events
Processing inbound event
Processing inbound event

Outbound

Outbound processing enables an application to access or modify data in Oracle EBS system. An application running in WebSphere Process Server or WebSphere Enterprise Service Bus invokes a service in an outbound module, which sends a request to the adapter to process one or more business objects. The adapter connects to Oracle EBS system, inserts the data into Oracle Interface Tables, and executes the specific Oracle Concurrent Program that validates and applies the data into Oracle Base Tables. Figure 3 shows the outbound processing.

Figure 3. Processing outbound request
Processing outbound request
Processing outbound request

Oracle E-Business Suite and Oracle Open Interface/Concurrent Program

Oracle E-Business Suite is a comprehensive suite of integration and global business management. It mainly applies to the Financial, Retail, Telco, Insurance and Manufacture fields. Besides the enterprise resource management ability, it also exposes some public interface types such as Oracle Open interface, Concurrent Program, Java Service, XML Gateway, PL/SQL API, and e-Commerce gateway. Oracle Open interface and Concurrent Program are the most popular interface types.

Oracle Interface Table is used to store data from external sources. A concurrent program runs as a concurrent process and is executed by the Concurrent Manager. The Concurrent program validates the data in Oracle Interface Table and then applies it into the Oracle Base Table. This mechanism ensures the data integrity and consistency in Oracle EBS system.

Interaction between WebSphere Adapter for Oracle E-Business Suite V7.0 and Oracle E-Business Suite R12

This article will introduce the mechanism and practice of integrating Oracle EBS through Oracle Open Interface and Concurrent Program using WebSphere Adapter for Oracle EBS. In Figure 4, it shows the outbound direction interaction. The Oracle invoice data can be first inserted into Account Payables (hereafter called AP) Interface Tables, and then moved to Account Payables Base Tables after validation.

The main steps of the process are:

  1. Define a stored procedure that wraps the invoice import Concurrent Program. This article will introduce why and how to define such kinds of stored procedures.
  2. Run the Oracle EBS Adapter EMD process to discover the AP Interface Tables and the predefined stored procedure. This will generate the adapter outbound module.
  3. Deploy the module on WebSphere Process Server.
  4. During the Oracle EBS Adapter outbound runtime, the invoice data is inserted into the AP Interface Tables, and then the predefined stored procedure is invoked automatically to execute the specific Concurrent Program to validate and apply the invoice data into the AP Base Tables. Now, the invoice data becomes effective in the Oracle EBS system.
  5. The invoice data can be queried through the Oracle EBS administration console.
Figure 4. Interaction scenario between Oracle EBS Adapter and Oracle EBS
Interaction scenario between Oracle EBS Adapter and Oracle EBS
Interaction scenario between Oracle EBS Adapter and Oracle EBS

Develop and test the invoice entry into Account Payables

Scenario overview

In this section, we will provide the detailed development, configuration, and test process of one business scenario that enters the invoice data into the Oracle E-Business Suite Account Payables module.

Create stored procedure that wraps the Oracle Concurrent Program

In general, after the data inserted into the Oracle Interface Tables, the business specific Concurrent Program should be invoked to validate and apply the input data into Oracle Base Tables. Before running the Concurrent Program, it needs to initialize the system parameters. When running the Concurrent Program, it needs to provide the specific parameters based on what business module will be accessed. We can wrap all these actions into one store procedure.

Oracle EBS Adapter can run such stored procedure as an in-place operation AfterCreateSP, which means the stored procedure will be called automatically after the data is created into the Oracle Interface Tables.

Listing 1 is the SQL script is the stored procedure that wraps all related actions to invoke the business specific Concurrent Program. We created a stored procedure named IMPORT_AP_INVOICE into the APPS schema of the Oracle EBS system using the account of apps. This stored procedure completes two parts.

First, execute FND_GLOBAL.APPS_INITIALIZE to initialize the system parameters Application User ID, Responsibility ID and Application ID. A different Concurrent Program requires different values for these three parameters. Second, execute FND_REQUEST.SUBMIT_REQUEST to invoke the specific Concurrent Program with the corresponding runtime parameters. A different Concurrent Program requires different parameter list and parameter values, which totally depend on the business module to access.

Listing 1. Create stored procedure
CREATE OR REPLACE PROCEDURE APPS.IMPORT_AP_INVOICE(
source IN VARCHAR2 
)IS
v_request_id NUMBER;
BEGIN
	APPS.FND_GLOBAL.APPS_INITIALIZE(
		1318,	-- Application User ID (OPERATIONS)
		50554,	-- Responsibility ID (Inventory, Vision Operations (USA))
		200	-- Application ID (SQLAP)
	);
  
	v_request_id := APPS.FND_REQUEST.SUBMIT_REQUEST(
		'SQLAP',		-- Applicaiton Short Name
		'APXIIMPT',	-- Concurrent Program Short Name
		'Payables Open Interface Import',	-- Description of the request 
		TO_CHAR(SYSDATE,'DD-MON-RR HH24:MI:SS'), --Request Start Running Time
		FALSE,		-- sub_request
		204,		-- From here, are the APXIIMPT parameters. Operating Unit
		source,		--Source
		NULL,		--Group
		NULL,		--Batch Name
		NULL,		--Hold Name
		NULL,		--Hold Reason
		NULL,		--GL Date
		'Y',		--Purge
		NULL,		--Trace Switch
		NULL,		--Debug Switch
		NULL,		--Summarize Report
		NULL,		--Commit Batch Size
		NULL,		--User ID
		NULL		--Login ID
	);
COMMIT;
END;
/
SHOW ERRORS

Develop the Oracle EBS Adapter outbound module

  1. Open IBM WebSphere Integration Developer V7.0, and change to the Business Integration perspective.
  2. Start the WebSphere Adapter for Oracle E-Business Suite External Service wizard by selecting File-> New –> External Service.
  3. In the Available Types tree view, select Adapters -> Oracle E-Business Suite, and then click Next.
  4. Select the IBM WebSphere Adapter for Oracle E-Business Suite (IBM: 7.0.0.0) node, and then click Next.
  5. In the “Import a RAR File” window, keep the default settings, and then click Next.
  6. Browse and add the Oracle JDBC driver ojdbc6.jar in the “Locate the Required Files and Libraries” window, and then click Next.
  7. In the “Select the Processing Direction” window, click Outbound, and then click Next.
  8. In the “Specify the Discovery Properties” window, select your database version and fill in the necessary connection and authentication information (Figure 5), and then click Next.
    Figure 5. Configuring the Oracle EBS connection information
    Configuring the Oracle EBS connection information
    Configuring the Oracle EBS connection information
  9. In the “Find Objects in the Enterprise System” window, click Edit Query.
    1. In the “Specify the Query Properties” window, type APPS in the “Schema name or pattern” field.
    2. Select the Prompt for additional configuration settings when adding business object check box, and then click OK.
    3. Click Run Query to display the objects discovered by the query.
  10. Filter the table ap_invoices_interface under Synonyms – Nicknames node and click the Add icon (>) to select the object to be imported, as shown in Figure 6.
    Figure 6. Selecting the Oracle EBS data object
    Selecting the Oracle EBS data object
    Selecting the Oracle EBS data object
  11. In the “Specify the Configuration Properties for AP_INVOICES_INTERFACE” window, configure the primary key, stored procedure AfterCreateSP (IMPORT_AP_INVOICE) and its parameter, and then click OK, as shown in Figure 7.
    Figure 7. Configuring Oracle Interface Table: AP_INVOICES_INTERFACE
    Configuring the Oracle Interface Table: AP_INVOICES_INTERFACE
    Configuring the Oracle Interface Table: AP_INVOICES_INTERFACE
  12. As in Steps 10 and 11, select the table AP_INVOICE_LINES_INTERFACE and configure the primary key, parent table, and foreign key. Select the Parent object owns child object (cascade) check box and Preserves AP_INVOICE_LINES_INTERFACE when the parent is updated check box, and then click OK, as shown in Figure 8.
    Figure 8. Configuring Oracle Interface Table: AP_INVOICE_LINES_INTERFACE
    Configuring Oracle Interface Table: AP_INVOICE_LINES_INTERFACE
    Configuring Oracle Interface Table: AP_INVOICE_LINES_INTERFACE
  13. Through the above steps, two interface tables, AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE, are selected (Figure 9). Click Next.
    Figure 9. Two selected Oracle interface tables
    Two selected Oracle interface tables
    Two selected Oracle interface tables
  14. In the “Specify Composite Properties” window, only the Create operation remains. Leave the default values for the other fields, and then click Next.
  15. In the “Specify the Service Generation and Deployment Properties” window, do the following (Figure 10):
    1. Check Using security properties from the managed connection factory.
    2. Clear the Join global transaction check box.
    3. In the “Deploy connector project” field, select With module for use by single application.
    4. In the “Database connection information” field, select Specify local database connection information.
    5. Click Next.
      Figure 10. Configuring service generation and deployment properties
      Configuring service generation and deployment properties
      Configuring service generation and deployment properties
  16. Name the new module CreateAPInvoice, and then click Finish.

Until now, the outbound module CreateAPInvoice is generated for runtime use.

Scenario test

Next, you will test the generated module CreateAPInvoice:

  1. Start WebSphere Process Server V7.0 in WebSphere Integration Developer.
  2. Deploy the module CreateAPInvoice on to WebSphere Process Server.
  3. Start the test client and fill in the sample test data as shown in Table 1 and Table 2. The test data can also be found in the provided download files.
Table 1. Sample data for Table AP_INVOICES_INTERFACE
Attribute Value Notes
invoice_id 216410 Unique identifier for this invoice
invoice_num cp-1000 The number must be unique for the supplier
invoice_type_lookup_code STANDARD Type of invoice (can be STANDARD, CREDIT or PREPAYMENT)
invoice_date 2009-12-14 Date of the invoice. Use date format of yyyy-mm-dd
vendor_name AccessMicron Supplier name
vendor_site_code IRELAND Supplier pay site code
invoice_amount 63 Invoice amount
invoice_currency_code USD Currency of invoice
terms_name 45 Net (terms date + 45) Payment terms identifier
description INVOICES FROM AccessMicron Invoice description
source MANUAL INVOICE ENTRY Source of the invoices
org_id 204 Unique internal identifier of the Operating Unit to which the invoice is to be imported
vendor_email_address test@cn.ibm.com Supplier e-mail address for XML invoice rejections
Table 2. Sample data for Table AP_INVOICE_LINES_INTERFACE
Attribute Value Notes
invoice_id 216410 Invoice identifier. Validated against AP_INVOICES_INTERFACE.INVOICE_ID
line_number 1 Unique line number for an invoice
line_type_lookup_code ITEM Type of invoice line (Item, Freight, Tax, Miscellaneous)
amount 63 Line amount
accounting_date 2009-12-14 Accounting date. Use date format of yyyy-mm-dd
item_description 1st Invoie Line Inventory item description
quantity_invoiced 1 Quantity invoiced against purchase order shipment
unit_price 100 Unit Price of the distribution line item(s)
org_id 204 Unique internal identifier of the Operating Unit to which the invoice is to be imported
  1. Click the Continue icon to send the outbound invoice data (Figure 11).
    Figure 11. Executing the Create operation
    Executing the Create operation
    Executing the Create operation
  2. Check whether the Concurrent Program has executed successfully:
    1. Logon to the Oracle EBS administration console using the operations/welcome account.
    2. Select Payables, Vision Operations (USA) responsibility.
    3. Select the Other -> Concurrent menu option.
    4. In Figure 12, click Find. The result completes as normal, as shown in Figure 13.
    5. In Figure 13, click View Output. It shows that the invoice is imported successfully in Figure 14.
      Figure 12. Finding the submitted Concurrent Program
      Finding the submitted Concurrent Program
      Finding the submitted Concurrent Program
      Figure 13. Concurrent Program execute result
      Concurrent Program execute result
      Concurrent Program execute result
      Figure 14. Viewing output of the executed Concurrent Program
      Viewing output of the executed Concurrent Program
      Viewing output of the executed Concurrent Program
  3. Inquire about the imported invoice:
    1. Logon to the Oracle EBS administration console using the operations/welcome account.
    2. Select Payables, Vision Operations (USA) responsibility.
    3. Select the Invoice:Inquiry -> Invoices menu option.
    4. In Figure 15, input cp-1000 in the “Invoice Number” field and click Find. The result is shown in Figure 16.
    Figure 15. Inquiring the imported invoice
    Inquiring the imported invoice
    Inquiring the imported invoice
    Figure 16. Imported invoice detailed information
    Imported invoice detailed information
    Imported invoice detailed information

Test result analysis

In the development and test sections, we used WebSphere Adapter for Oracle E-Business Suite to create an invoice in Oracle Account Payables (AP) module through Open Interface and Concurrent Program. The adapter first inserts the invoice data in to Oracle nterface table AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE, and then the Adapter invokes the stored procedure IMPORT_AP_INVOICE to execute the Concurrent Program APXIIMPT to validate and apply data from the interface tables to the base tables. Finally, query the imported invoice from the Oracle EBS administration console.

The key points to complete this scenario successfully are as follows:

  1. The stored procedure that wraps all related actions to invoke business specific Concurrent Program was defined correctly. The stored procedure contains two parts:
    • FND_GLOBAL.APPS_INITIALIZE to initialize the system parameters.
    • FND_REQUEST.SUBMIT_REQUEST to invoke the specific Concurrent Program with the corresponding parameters.

    All the parameter values should be provided correctly according to the specific Concurrent Program.

  2. The Adapter generated the correct business objects, service definition, and configuration files. When running the External Service wizard in WebSphere Integration Developer, the interface tables and stored procedure should be configured correctly, including the primary key, foreign key, parent-child relationship, and the in-place operation AfterCreateSP.
  3. The invoice data was inserted into the interface tables successfully. The imported invoice data should be well defined so that it can be inserted into the interface tables. That means the invoice data inserted into several interface tables should be consistent and valid.
  4. The Concurrent Program was executed successfully. With the configured stored procedure, the specific Concurrent Program will be executed automatically. The Concurrent Program can be executed successfully when providing the appropriate parameter values.

Conclusion

In this article, we introduced the mechanism to integrate Oracle E-Business Suite R12 through Oracle Open Interface and Concurrent Program by using WebSphere Adapter for Oracle E-Business Suite V7.0. The article described a detailed development, configuration, and test process of one business scenario that creates an Oracle invoice into the Oracle E-Business Suite Account Payables module.


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=WebSphere
ArticleID=464343
ArticleTitle=Integrating Oracle E-Business Suite through the Oracle Open Interface and Concurrent Program using WebSphere Adapters
publish-date=01272010