Using Oracle Enterprise Business Suite EBS Plugin in Tibco BW

Today I am going to explain the usage of TIBCO Oracle E-Business Suite (EBS) plugin, how EBS plugin configured in Tibco BW designer and calling different oracle procedures with the wrapper package.

Oracle E-Business Suite (EBS) is a complete set of business applications for managing and automating processes for your enterprise. It is also known as Oracle Enterprise Resource Planning (ERP), Oracle Apps, Oracle Applications, and Oracle Financials on the market. It is a comprehensive suite of integrated, global business applications that provides:

• A complete, integrated business intelligence portfolio
• An adaptable global business platform
• A customer-focused applications strategy

After installing Oracle EBS plugin you will find following activities in the designer pallet.

• Oracle E-Business Suite Connection
• Oracle Business Event
• Oracle API
• Oracle Concurrent Program
• Custom API
• Custom Concurrent Program

Now I am going to explain the usage of Oracle Custom API Activity in this tutorial.

Configuration Steps:

Step 1:

First of all, you need to define the connection for communicating with Oracle by clicking the Oracle E-Business Suite Connection and configure the connection activity as shown in the figure.

Test the Connection by pressing the Test Connection button after completing all fields.

Step2:

Configure Custom API:

Drag and drop the custom API activity form pallet in BW designer. Provide Oracle EBS connection, Oracle Package, Oracle Procedure and Timeout values; you can do it by using search button of each configuration.

Map the inputs in Input tab of configuration. This is pretty simple as JDBC activities with simple in/ out parameters of oracle procedure. Consider the example with you required table type as oracle “IN” parameter in that case you need to generate Wrapper Package. Following are the steps for generating Wrapper Package:

1. Place the following files in “C:\TibcoHome\bw\plugins\oracleebs\examples\jpublisher\lib” where “TibcoHome” is your system tibco home directory.

• ojdbc5.jar
• runtime12.jar
• translator.jar

2. Open the command prompt and set he class path set CLASSPATH=%CLASSPATH%;C:\TibcoHome\bw\plugins\oracleebs\examples\jpublisher\lib\runtime12.jar;C:\TibcoHome\bw\plugins\oracleebs\examples\jpublisher\lib\translator.jar;C:\TibcoHome\bw\plugins\oracleebs\examples\jpublisher\lib\ojdbc5.jar
3. Go to jre bin folder by using CD command in cmd prompt “cd C:\TibcoHome\tibcojre64\1.7.0\bin”
4. Type “Java oracle.jpub.Main -user={username}/{password} -url=jdbc:oracle:thin:@{url}:{port}:{SID} -sql={Packagename} -plsqlpackage={wrapperpackageName} -plsqlmap=always -dir=java/{directory} -plsqlfile=sql/{directory}/{scriptFile}.sql”
5. It will generate sql scripts for wrapper package
6. Run Wrapper Package in you data base
7. Give appropriate rights as per your need to this wrapper package.

 

After successful wrapper generation you will see new “Wrapper Package Name” and “Wrapper Procedure Name” in configuration tab as shown below: