Object Types in Integration PLSQL API using OA Adapter

If you are planning to use plsql API with plsql collection parameters for any integration using OA Adapter / DB Adapter then use Object Types rather than plsql collections (ie tables and records).

Advantages are

1. Adapter can not use plsql tables and records directly. It will create similar database Object Type and use that in xsd creation, transformation etc. It also creates package to assign plsql collection into object types. This can be avoided if we use database object.
2. If there are any changes then just we need to change the object type and respective xsd. If we use plsql tables and records then we have to make changes in too many places. Some time it creates major issues in particular if we have complex transformations and assignments.

How BPEL OA Adapter connect to Oracle EBS?

In this article, I listed the steps to setup the connection factory for OA Adapter to connect to EBS. There are two major portions in this setup.

1. JDBC Resource Setups
2. AppsAdapter (ie OA Adapter) Connection Factory Setups

JDBC setups need to be completed first…

1. Get Database TNS Entry and APPS schema password
2. Login as Application Server Enterprise Manager (eg: oc4jadmin) and click oc4j_soa (default OC4J Instance)
3. Go to Administrator tab and click JDBC Resources Go to Task icon
4. Create new Connection Pool by giving name and URL. Follow the URL syntax carefully
5. Enter Credential user as APPS and its password and then test the connection
6. If test connection is success then create Data Source.
7. Select Data Source Type as Managed Data Source. Enter data source name and JNDI location. JNDI Location can be start with jdbc/NAME to maintain the consistency.
8. Note down the JNDI Name, it will be used while defining Connection Factory.
9. Select the above created Connection Pool and test the connection.

Now we can to use this JDBC Resource in OA Adapter Connection Factory Setups.

1. Go to Applications tab in oc4j_soa OC4J Instance
2. Select view as Modules (Default is Applications)
3. Select AppsAdapter Module and go to Connection Factories tab
4. Create new Connection Factory. Give meaningful JNDI Location. It can start with eis/Apps/NAME. For example eis/Apps/EBS.
5. Enter xADataSourceName as JDBC JNDI Name just created.

Now OA Adapter can be used in BPEL Process. Make sure same JNDI Name is entered when Partner Link is created for OA Adapter in JDeveloper.

Single BPEL Process for Multiple Operations using Pick Activity

Usually we will create individual BPEL Processes for each Operation in either synchronous or asynchronous. This blog will help to build single BPEL Process for more than one Operation for exceptional cases like requesting system might have restricted number of adapters or ports to connect to other systems. This can be done using Pick activity.

1. Get target system WSDL file
2. Create Empty Project
3. Create Partner Link for the WSDL file
4. Select Pick Activity and drag and drop into Process
5. Don’t forget to select Create Instance check box in Pick activity
6. Add OnMessage Branch as much as the number of Operations as you have
7. Go to any one OnMessage. Select Partner Link created, select the Operation and create Local Request Variable.
8. Add activities and partner links you wanted for the selected operation
   Finally add Reply/Callback activity based on type of Operation.
9. Repeat this for all remaining Operations
10. Deploy into BPEL Server
11. Initiate the process from where ever you want. Note that this process can not be initiated from 10g BPEL Console. May be this is a bug.

Validate & Get Values from XML using PL/SQL

There are various methods available in PLSQL to read, write and validate XML. Following sample code has simple approach to read, validate and get element value from XML using DBMS_XMLDOM API. This sample will read employee name and print it in the console.

DECLARE
lc_return_msg VARCHAR2(1000);
lc_xml_buf VARCHAR2(2000);
lc_xml_value VARCHAR2(2000);
lc_resp_xml XMLType;
lc_xml_doc DBMS_XMLDOM.DOMDocument;
lc_xml_ndoc DBMS_XMLDOM.DOMNode;
lc_xml_docelem DBMS_XMLDOM.DOMElement;
lc_xml_node DBMS_XMLDOM.DOMNode;
lc_xml_childnode DBMS_XMLDOM.DOMNode;
lc_xml_nodelist DBMS_XMLDOM.DOMNodelist;
BEGIN
-- XML Message
lc_return_msg := '<?xml version="1.0" encoding="utf-8"?>
<Department>
<Code>001</Code>
<Name>Sales</Name>
<Employees>
<Employee>
<FirstName>Scott</FirstName>
<LastName>Tiger</LastName>
<DoB>01/01/1975</DoB>
</Employee>
<Employee>
<FirstName>Adam</FirstName>
<LastName>Ford</LastName>
<DoB>12/03/1983</DoB>
</Employee>
</Employees>
</Department>';

-- Validate XML
lc_resp_xml := XMLType(lc_return_msg);

-- Create DOMDocument Handle
lc_xml_doc := DBMS_XMLDOM.newDOMDocument(lc_resp_xml);
lc_xml_ndoc := DBMS_XMLDOM.makeNode(lc_xml_doc);

DBMS_XMLDOM.writeToBuffer(lc_xml_ndoc, lc_xml_buf);

-- Get all elements
lc_xml_docelem := DBMS_XMLDOM.getDocumentElement(lc_xml_doc);

-- Get Result Elemement
lc_xml_nodelist := DBMS_XMLDOM.getElementsByTagName(lc_xml_docelem, 'FirstName');

-- Get Second Employee Name
lc_xml_node := DBMS_XMLDOM.item(lc_xml_nodelist, 1);
lc_xml_childnode := DBMS_XMLDOM.getFirstChild(lc_xml_node);
lc_xml_value := DBMS_XMLDOM.getNodeValue(lc_xml_childnode);

-- Print second employee name
DBMS_OUTPUT.put_line(lc_xml_value);
END;

APPS Context in BPEL OA Adapter

Oracle Application Adapter (OA Adapter) connects to EBS Database as user SYSADMIN and responsibility System Administrator. This needs to be changed for different reasons like Security, Using Multi Operating Units.

If user and responsibility are know and won’t change then this can be hardcoded in OA Adapter Partner Link Created. Search for element Responsibility, change the Username, Responsibility and save the file.

If Contexts are dynamic then follow these steps:

1. Create new Message Type Variable for Header_msg in AppsContextHeader.wsdl file.
2. Assign Username, Responsibility & ORG_ID variables
3. Go to Adapters tab in OA Adapter Partner Link Invoke activity and Select the above created new variable as Input Header Variable.