First you need to understand the basic integration between OPA cloud and its environment. You call the cloud environment using an URL with some parameters. Then OPA cloud performs calls to your environment using the connection framework you have to develop based on predefined WSDLs OPA is prescribing.
Your connection framework needs to be able to receive SOAP calls and answer appropriately. Since we want to connect to Oracle eBS and we started out with creating APIs in the eBS environment (PL/SQL packages) which could be exposed to OPA.
To expose them we could have build BPEL processes using SOA Suite calling our API's directly of course, but since we could not use SOA Suite (domain restrictions), we've used the Integrated SOA Gateway in eBS.
Unfortunately the SOAP responses generated by Integrated SOA Gateway were not exactly what OPA Cloud expects and you are very limited in steering the respons created by the SOA Gateway because the WSDL is automatically generated based on your PL/SQL packages. So therefore we needed a translation between the SOA Gateway and OPA, the ESB.
Integrated SOA Gateway
Now there are some points which are important when you use the Integrated SOA Gateway here .. First, the input and output parameters to the procedures are complex datatypes, which all kinds of nested tables of records of tables of records. You cannot define these as types WITHIN your PL/SQL package (otherwise you can't get you package deployed as a webservice), so you have to make object types like
create
type xxx_opa_t_metatable is object
( name varchar2(80)
, can_be_input varchar2(10)
, can_be_output varchar2(10)
, description varchar2(240)
, accepts_attachments varchar2(10)
, table_fields xxx_opa_t_MetaTableFields
, table_links xxx_opa_t_MetaTableLinks
);
as separate types in the database.
For each service you can find the input and output parameters described in the OPA Documentation: http://documentation.custhelp.com/euf/assets/devdocs/august2016/PolicyAutomation/en/Default.htm#Guides/Developer_Guide/Connector_Framework/Expose_application_metadata.htm%3FTocPath%3DDeveloper%2520Guide%7CConnector%2520framework%7C_____2
Our package header is stored in a pls file with the following annotations
create or replace package xxx_opa_wsep_pkg as
/* $header: apps.apps.apps.xxx_opa_wsep_pkg $ */
/*#
* ebs opa webservice connector
* @rep:scope public
* @rep:product XXX
* @rep:lifecycle active
* @rep:displayname eBS OPA Webservice Connector EBS Endpoint
* @rep:compatibility S
* @rep:category BUSINESS_ENTITY XXX_OPA_WSEP_PKG
*/
Including functions for each of the operations
CheckAlive
GetMetaData
LoadRequest
SaveRequest
SetCheckpoint
GetCheckpoint
which we will describe in more detail later.
CheckAlive
The checkalive function is used in the OPA Cloud environment to check if there is a valid connection. It sends back a very simple SOAP response
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body>
<check-alive-response xmlns="http://xmlns.oracle.com/policyautomation/hub/12.2.2/metadata/types"/>
</S:Body>
</S:Envelope>To give you an idea on the required translation when using Integrated SOA Gateway, this is what our CheckAlive function returns by default
<env:Envelope
xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
<env:Header/>
<env:Body>
<OutputParameters
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://xmlns.oracle.com/apps/xxgr/soaprovider/plsql/xxgr_opa_wsep_pkg/checkalive/">
<ERROR_MSG
xsi:nil="true"/>
<STATUS>S</STATUS>
</OutputParameters>
</env:Body>
</env:Envelope>
So this needs to be translated into the response required by OPA.
If this service works, you can see the connection is green in the OPA Hub.
In our case we did not directly communicate with the service bus, but we had to pass a load balancer, an open tunnel, some firewalls and then we reach the service bus, which connects through some firewalls with the Oracle eBS environment.
GetMetaData
The GetMetaData service exposes the datamodel from eBS that can be used in OPA to map fields. OPA is aware of a lot more types than Oracle including currency, time, etc. We cannot create object types with booleans either, so if we want to indicate that some field is a boolean we define it as text (which may hold values like true/false) and we have to tell OPA this field is a boolean.
The metadata also contains any list of values you want to use in OPA. Note that OPA should not be used as a form to enter values (like select a supplier, select an employee), but you can use select lists for smaller lists to choose an answer from.
In our example we added list of values for line types, unit of measure and item categories, where we made dependent valuesets between the segments.
Important to understand in dependent valuesets is that the the main valueset contains all its children. So if you have one segment Animals including Mammal, Hoofed animal, etc and Hoofed animal includes Cow, Horse, etc, you would send list one Animals with child Hoofed animal and all its children and then a second list of Hoofed animals (and a third with mammals), etc.
For each field in your data element you specify whether it is an input or output field, whether it is required and the type. And as said, since we only have text types in Oracle for our booleans, we have to indicate this is actually a boolean to OPA so it can treat it as a boolean and pass true/false back.
Just an example of how our code was build up. We fetched all columns from a given view and passed them in our case all as input/output, but of course you could make this more complex by defining in a lookup which fields can be inputs or outputs to OPA.
<<Fields>>
FOR F IN C_Fields (cp_table_name => 'XXX_REQUISITION_DETAILS_V')
LOOP
nr_of_cols := nr_of_cols + 1;
--
lt_tablefields.extend;
l_can_be_input := 'true';
l_can_be_output := 'true';
l_is_required := F.Is_Required;
--
l_data_type := F.Data_Type;
IF F.Column_Name = ( my list of boolean columns ) THEN l_data_type := 'boolean'; END IF;
l_tableFields := xxx_opa_r_MetaTableFields (F.Column_Name,l_data_type,null,l_Can_Be_Input,l_Can_Be_Output,l_Is_Required,Initcap (F.Column_Name));
lt_tablefields (nr_of_cols) := l_tablefields;
--
END LOOP Fields;
Metatable := xxx_opa_t_metatables();
MetaTable.extend;
MetaTable (1) := xxx_opa_t_metatable ('XXX_REQUISITION_DETAILS_V','true','true','Request','true',lt_tablefields,null);
Next time we will describe the Load and Saverequests in more detail and the checkpoints.
