Get data from checkpoints OPA Interviews

In Oracle Policy Automation (see also the other blogs) you can save temporary information using the SetCheckpoint operations. This sends you a base64 encoded blob with the temporary information from your interview, but no structured data.
However, this base64 encoded blob is simply a zipfile, so we can extract it, find the file we need with our data and use that data during our checkpoints if necessary.

In our use case we would like to see the structured data before the submit, because interviews could run longer over time (people need to gather information, request help, etc). So an interview could be open for a number of days before the submit takes place.

The first thing we need to do is decode the checkpoint

l_blob := XXX_OPA_UTIL_PKG.base64decode (checkPointData.Encoded_Checkpoint);

This gives us the zipfile as a blob. The file we are looking for is UserData.xml, so let's extract that from the zip using the AS_ZIP utility created by Anton Scheffer and which you can find at the site of Amis
https://technology.amis.nl/wp-content/uploads/2010/06/as_zip7.txt

l_xml_blob := AS_ZIP.Get_File 
  (
    p_zipped_blob   => l_blob 
  , p_file_name     => ' UserData.xml' 
  );

Now we have our file, but we need to convert it to XML first.

l_xml_data := Convert_Blob_To_Xml (l_xml_blob);

And then we run through the XML to find our attributes. You can refer to a specific attr_id (like title, username, etc) to find the value and use that value in your process. In our case it was the title of a request that we wanted to store so we can see from all our running requests which one is which! Otherwise you would only have some kind of internal ID maybe or any data you use to start the interview. In our case we started the interview and all data came from the interview, so we started with nothing more than an ID.

 <<Attributes>>
  FOR x IN C_XML_Data (l_xml_data)
  LOOP 
    dbms_output.put_line (x.attr_id || ': ' || x.attr_val);
  END LOOP Attributes;

The code to convert the blob to XML was written like this. The reason for replacing the boolean and number values is because we want to treat everything as a string (varchar) later on and that makes our XML query easier, but of course you can do that differently.

  ---------------------------------------------------------------------------------------
  -- Subfunction to convert the blob we extract to XML.
  ---------------------------------------------------------------------------------------
  FUNCTION Convert_Blob_To_Xml 
  (
    p_blob BLOB
  ) RETURN XmlType
  IS 
    v_clob    CLOB;
v_varchar VARCHAR2(32767);
v_start   PLS_INTEGER := 1;
v_buffer  PLS_INTEGER := 32767;
x         xmltype;
  BEGIN
  
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
  
FOR i IN 1 .. CEIL(DBMS_LOB.GETLENGTH(p_blob) / v_buffer) LOOP
  
v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(p_blob,
                                                         v_buffer,
                                                         v_start));

DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);

v_start := v_start + v_buffer;
END LOOP;

v_clob := replace (v_clob,'boolean-val','text-val');
      v_clob := replace (v_clob,'number-val','text-val');
v_clob := replace (v_clob,'date-val','text-val');


x := xmltype.createxml(v_clob);
Return (x);
  
  END Convert_Blob_To_Xml;


The XML Query is build as follows

 CURSOR C_XML_Data 
  (
    p_xml_data XMLType
  )
  IS
     WITH XmlTypeAsTab as (select 
p_xml_data 
 xml from dual)  
    SELECT attribs.attr_id 
,      attribs.attr_val
    FROM   XmlTypeAsTab x 
    , xmltable ('/user-data/global-inst/attr' passing x.xml 
columns attr_id  varchar2(4000) path './@id'
,
        attr_val varchar2(4000) path './text-val'
) attribs
    ;

Integration OPA Cloud and Oracle eBS (12.1.3) iProcurement - part 3 (close window)

In the current release of the Oracle Policy Automation model we were using, we could not close the interview window at the end. You can however navigate to another window using an URL.
So what we did is create a simple HTML page with java script that closes a window and navigate to that.

Add close link in your model

On the close button in the model we change the style and refer to one of the input parameters we load using the LoadRequest (see previous blog).

The parameter (start_url) refers to the htm page that closes our window.

Place the HTM file on a location you can reach

In order to do this in eBS we place this file in $OA_HTML on the applicationserver, fetch the link using the profile APPS_SERVLET_AGENT and concatenate it with our HTM file to pass the link to OPA.

The HTM file contains the following logic

<html>
<head>
<script type="text/javascript">
function closeWP() {
 var Browser = navigator.appName;
 var indexB = Browser.indexOf('Explorer');

 if (indexB > 0) {
    var indexV = navigator.userAgent.indexOf('MSIE') + 5;
    var Version = navigator.userAgent.substring(indexV, indexV + 1);

    if (Version >= 7) {
        window.open('', '_self', '');
        window.close();
    }
    else if (Version == 6) {
        window.opener = null;
        window.close();
    }
    else {
        window.opener = '';
        window.close();
    }

 }
else {
    window.close();
 }
}
</script>
</head>
<body onload="closeWP();">
</body>
</html>

Integration OPA Cloud and Oracle eBS (12.1.3) iProcurement - part 2

We described earlier some of the initial operations we need to do to integrate OPA with Oracle eBS. Below you see an overview of the rest of the operations. So we have a model for OPA, which we create in the model designer. A model needs to be deployed to be usuable as runtime.
In the model we invoke the GetMetaData to get the data structure and the valuesets we like to use in our interview.

So each time something changes in your data structure or valuesets, you need to do a GetMetaData in the model and deploy the model to the runtime version.

During runtime you have two options
1. Start
2. Resume
These options use their own URLs to start the interview and also invoke other services. The start invokes the LoadRequest, where the resume invokes the GetCheckPoint to resume an existing interview from the point you saved.

LoadRequest
The load request loads initial data from your datamodel, which can be used as input parameters for the interview. When you called the interview we can pass an initial parameter in the URL as well. We used that to indicate the user, responsibility and a unique ID to identify the record in our table. Of course you want to encode those parameters, so we used DBMS_OBFUSCATION_TOOLKIT.Desencrypt to encrypt these parameters into one connection string.
When you then get the LoadRequest, it passes back your initial parameter so you can decrypt it and identify the user, authorization and the record we are creating/updating.

First thing we do then is a fnd_global.apps_initialize, because the user calling our webservice through the Integrated SOA Gateway is a generic user.

Our LoadRequest procedure looks something like

procedure LoadRequest
(
  root                 IN  VARCHAR2
, region               IN  VARCHAR2
, language             IN  VARCHAR2
, timezone             IN  VARCHAR2
, request_context      IN  xxgr_opa_t_RequestContext
, seedDataDescription  IN  xxgr_opa_t_Tables
, loadData             OUT xxgr_opa_t_LoadData
, error_msg            OUT VARCHAR2
, Status               OUT varchar2
)

Here the seedDataDescription contains a list of entities and fields the interview is requesting from us. So you have to check which specific fields the service wants and pass values for those back. You cannot pass more values (service will fail).

We also use a translation for booleans, because Y/N values in the database should be translated to true/false values for the service and return type boolean, where we cannot use booleans in our data structures directly.

What we actually did is draw a sequence number and pass that as parameter on the interview. As soon as we got the LoadRequest back we created our record in the database with that sequence (because we were creating entities using the interview and until we get some feedback back from the interview we do not actually need the record).
During SetCheckpoint and SaveRequest we continue processing our record.

Information we pass on the load is stuff like the name of the user that called the interview, his organization, etc.

Drawback is that we can return an error to the webservice, but OPA cannot handle that. It will not show the error message we send back (just a generic error).


SetCheckPoint
During your interview you can save the data so you can resume later. Those are called checkpoints. The hub call call the SetCheckpoint to save a base64 encoded zip file of the interview. That zip file contains an XML containing variables entered in the interview; so if you want to you could unzip and decode the information and actually store structured data in between save points.

procedure SetCheckpoint
(
  request_context   IN xxgr_opa_t_RequestContext
, checkPointData    IN xxgr_opa_r_CheckPoint_Data
, checkpointId      OUT varchar2
, error_msg         OUT varchar2
, Status                 OUT varchar2
)

So setting a checkpoint is merely saving the blob data given our context (request_context). That contains again the parameter(s) we passed to the initial URL, which is our encrypted key with user information.

In our specific case we would create purchase requisitions from our interviews, so this was the moment we actually created the requisition and related our interview (using a custom table) to it.


SaveRequest
When you are done with your interview you can submit the data using a save request operation.


procedure SaveRequest
(
  root              IN  VARCHAR2
, region            IN  VARCHAR2
, language          IN  VARCHAR2
, timezone          IN  VARCHAR2
, request_context   IN  xxgr_opa_t_RequestContext
, submitData        IN  xxgr_opa_t_submit_data
, attachments       IN  xxgr_opa_t_attachments
, auditReport       IN  xxgr_opa_t_audit_report_list
, updateData        OUT xxgr_opa_t_UpdateData
, error_msg         OUT VARCHAR2
, Status            OUT varchar2
)

This is the most complex operation, because now we get all the structured data in the submitData including attachments and auditreports.

There are some restrictions on attachments in the service. You can restrict the size of files in OPA, but the SOA Gateway also may have its own restrictions. We also had a service bus in between with memory restrictions, so we had a limit of max 40MB on files. But note that on each setcheckpoint it would send any attachments in the base64 encoded zip that you already uploaded. So it's good practice to add your attachments as late as possible in the interview to avoid a lot of data traffic.

The submitData contains two parts
- The input fields
- Request for output fields

The latter is a request after the submit (which you can use in the OPA model) to pass back some information. So we capture the fields that are requested to pass them back later after we are done (for example to pass a requisition number).

 <<OutputFields>>
        FOR i in 1 .. submitData(l_det_ind).submitRow(1).outputfield.count 
        LOOP  
          L_Outputs (submitData(l_det_ind).submitRow(1).outputfield(i).name) := submitData(l_det_ind).submitRow(1).outputfield(i).name;
        END LOOP OutputFields;

Then we loop through all the input fields, validate the input and store the data. Of course we could store the names as indexes in a table, but we still need to identify per field what we want to do with it.

 <<InputFields>>
      FOR i in 1 .. submitData(l_det_ind).submitRow(1).inputfield.count
      LOOP

               IF   submitData(l_det_ind).submitRow(1).inputfield(i).name = 'DESCRIPTION' 

               AND  submitData(l_det_ind).submitRow(1).rowAction = 'update'

               THEN

                  l_description := submitData(l_det_ind).submitRow(1).inputfield(i).data_val;

               END IF;

Note that we can have multiple entities (l_det_ind), with multiple rows and multiple fields. In our specific case we only had one main record, but multiple sub records. So we used submitRow (1) here, but otherwise we would have used  loop.

Attachments

Now we get 2 types of attachments. You get the main report IN the data and a separate attachments parameter.

submitData(l_det_ind).submitRow(1).attachments

contains the attachments on our main entity.

So finally we validate our input, update our record and return a message back. This message can be shown, if you use the LoadAfterSubmit option in OPA. It loads information you can give back, so we can show error and warning messages if necessary.

In our current release you can only submit once. After that you need to close the interview and re-open it to make changes.


GetCheckPoint

Then finally the get check point operation, which is used if you resume an interview. We simply read the base 64 encoded string (zip file) and pass that back to the webservice.


procedure GetCheckpoint
(
  request_context   IN xxgr_opa_t_RequestContext
, checkPointData    OUT xxgr_opa_r_CheckPoint_Data
, error_msg         OUT varchar2
, Status                 OUT varchar2
)

So this is pretty straightforward. The only thing we also do, on all operations, is check whether the user is allowed to do this. For example you cannot do a Load if you aren't logged in right now. And you cannot submit if you were not logged in today.

Integration OPA Cloud and Oracle eBS (12.1.3) iProcurement - part 1

In my current project we've created a custom integration between Oracle iProcurement (12.1.3) and Oracle Policy Automation in the cloud (release 12.2.5). I like to share some lessons learned from this project and help along others who might need to build an integration between eBS and OPA Cloud.

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.