donderdag 1 maart 2018

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

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.

  FOR x IN C_XML_Data (l_xml_data)
    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
    v_clob    CLOB;
v_varchar VARCHAR2(32767);
v_start   PLS_INTEGER := 1;
v_buffer  PLS_INTEGER := 32767;
x         xmltype;
FOR i IN 1 .. CEIL(DBMS_LOB.GETLENGTH(p_blob) / v_buffer) LOOP

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

v_start := v_start + v_buffer;

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

    p_xml_data XMLType
     WITH XmlTypeAsTab as (select 
 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

zondag 21 januari 2018

Scanning medical supplies

Patient safety and logistics in a hospital are closely related. Is the correct pacemaker available at the appropriate time for the operation and which heart valve has been used for a specific patient?
In able to quickly see which medical supplies are used in a specific patient or to a recall for a given lot it's important to have a correct administration.
In able to support this, insight is needed in the logistical processes, inventory levels and usage within the hospital. Profource offers an integrated solution within the Oracle e-Business Suite for healthcare focussed on patient safety, traceability, barcode scanning and logical efficiency.

Below a simplified process is shown that we implement at hospitals. Items are received by the supplier in a central repository. They unpack boxes and transport the items to the departments who require the goods. Goods are then placed in specific subinventories (can be sterile or not sterile subinventories) used for the operations.
Usually the first receipt at the central repository is without barcode scanning (we do not register the specific lots and/or serial numbers received).
The receipt on the department however is supported by barcode scanning.

Once the items are registered, they can be used at operations. Some larger hospitals use trays they prepare for operations and make a subinventory transfer from the OR subinventory to the tray subinventory for full traceability.
We also support this transaction using barcode scanning.
When items are used during the operation, we can register the issue directly into the eBusiness Suite again using barcode scanning. Supplies that have medical consequences ("in patient") are interfaced to an EPD.
We also support registration of other goods, like test items, sets that are on loan from a supplier, etc. These may or may not have financial impact and may or may not be send to the EPD depending on the usage.

Barcode scanning

Profource has built a configurable decoder to recognize GS1 and HIBC compliant barcodes. The GS1 barcode for example consists of
  • a prefix (usually 01)
  • the product identifier (14 positions)
  • a lot number starting with 10
  • an expiration date starting with 17
  • a serial number starting with 21
Barcodes can be split across multiple lines as well. In Oracle we register whether items are under lot, tht and/or serial control, so we know what to expect in the barcode. The system automatically expects a second line if the first line did not contain the serial number and the item is under serial control.

The system is very flexible so it can also recognize non-GS1 compliant barcodes as long as they comply to certain rules.

Next to recognizing GS1 barcodes, we also print GS1 compliant barcodes for items that cannot be scanned due to invalid barcodes (not all suppliers are yet GS1 or HIBC compliant).


In our solution we are able to configure to print barcode labels on the first receipt if necessary (items that always have non compliant barcodes). The items with the labels are then delivered to the department inventory for the second receipt. 
We make sure that on receipt the label on the item is always correct, so we do not have any issues during issue at the OR itself.

In this custom screen we (1) scan the purchase order number (and if necessary the release), (2) scan the barcode label on the item and (3) shows the open receipts we are expecting from the first receipt.

The system decodes the barcode and retrieves the item in Oracle using the PID which we have linked to the item.
Using the receipt open interfaces we register the receipt immediately on the correct subinventory and location.

Big advantage here is of course that the logistics employee does not have to enter the specific lot or serial number anymore. By scanning the item, the registration is instantly, without any errors or typos.
Another advantage is that the system immediately warns you if an item you place in inventory may expire within a certain period. We register on item level how long it should be available (say 3 months), so any item with an expiration date within that window, would trigger a warning. 


Item issues can be performed both directly in eBusiness Suite using a customized screen or imported when scanned in an external system. An issue is always linked to a patient number or operation ID and a specialty (configurable in the system).

Issues are directly processed, so we both have the medical registration as the logistics transaction. You can indicate whether the item has been used in the patient or not (for example if you dropped the item).

In the system we support multiple types of issue. The material transaction is the most common, but you can also register an item from a loan set. 
A loan set is a set from a supplier for which you only pay once used (including using some rent once you starting using the set). The solution makes sure the rent and usage is registered so it can be invoiced from the supplier.

One of the advantages of the fully integrated solution is that during scanning the system immediately warns you if the item is not available or expired.

Label printing

The printing solution for the label printing is very flexible. Using lookup codes we can configure the label for each printer type (Intermec, Toshiba, Zebra).
Lookup codes for each element on the label are defined and can be configured for each printer. For example the start label for a specific Intermec would be


Where for the Zebra it would be


And the Toshiba uses something like


Usually we use 2D barcodes rather than the 1D codes to make sure there is enough space to print both the barcode and the information required (like item description, supplier item number, location, etc).

zondag 12 november 2017

On premise to Cloud integration with Oracle Enterprise Contracts

During several conversion projects towards Oracle Enterprise Contracts we have used the method of integration through the database using UTL_HTTP.
The same method can be used for a full integration between for example Oracle eBusiness Suite on premise and Oracle Contracts Cloud. This article describes the contracts integration we've setup for several of our customers to integrate between Oracle eBusiness Suite on premise and Oracle Cloud Contracts. Note that you could also use any other (Oracle) database to implement the same, but in most of our cases we've used eBS. I hope this article will help to get you started on your own integration with Cloud Contracts as well!

The method consists of a number of webservices we call in a specific order to upload the contract, activate the contract and upload documents (attachments), like the signed contract.

To find information on the webservice itself you check Fusion Enterprise Repository (OER)
You should check under the sales section
for the Contracts Service

The URL for your WSDL is
https://(CRMDomain,Contract Management)/external-contractmanagement-contractsCoreTransaction/ContractService?WSDL

Something we encode as generic parameters in our service

 g_fs_user           varchar2(200)     default '...;
  g_fs_pswd           varchar2(200)     default '...';
  g_activity_ws       varchar2(200)     default '';
  g_contract_ws_r10   varchar2(200)     default 'https://.../';

Usually we use lookups in eBS for this purpose. The lookup code in that case is the name of the environment, the description is the URL to the service. This way, post clone, the values will still be correct.

Generic method

In general what we did is
a) Create a global variable that contains the payload for the webservice with replaceable tags
b) Loop through the transactions you like to process
c) Transform, validate the data into what is required
d) Replace the tag in the payload with your value
e) Call the webservice

So for example the contract creation itself is a variable like

  g_contract_header_start varchar2(4000) default '' ||
    '     <typ:contractHeader>
            <tran:EstimatedAmount currencyCode="[CurrencyCode]">[AgreedAmount]</tran:EstimatedAmount>

During creation we loop through the contracts we need to create and fetch the necessary values. Now you may notice we need the contract type id for example. This is a value that exists in the cloud environment and not in the eBS environment. So how do we get that?

Get Translation Data

In order to get "translation data" like that, we create a datamodel in the BI environment of cloud that provides us with all necessary internal values and setup.
So this queries the legal entities that have been setup, the contract types, etc. We download this into an XML file and upload it to a table to use for conversions.

To upload the file we place the XML file on the server and upload it to a conversion table using SQL Loader

INFILE 'content.dat'
  INTO TABLE xxconv_test
    fname   filler char(80),

Then we convert the clob into XML and put it in our translation table. We've used the same method for several clients, hence we also use the client to see for which client this transformation was used.

  l_xml XMLTYPE;
  l_clob CLOB;
l_xml := xmltype.createxml (l_clob);

Now we have all our data in a table we can use queries like this. This shows all valuesets we have used for the flexfields for example, since we also want to validate the values in the flexfields before uploading.

select flex_value
, description
      SELECT Flex_Value_Set_Name, Flex_Value,Description
      FROM xxconv_okc_xml_all t
         , XMLTable('/DATA_DS/DFF_VALUESETS'
             passing t.IDS
               FLEX_VALUE_SET_NAME       varchar2(240)     path 'FLEX_VALUE_SET_NAME'
             , FLEX_VALUE       varchar2(240)  path 'FLEX_VALUE'
             , DESCRIPTION    varchar2(240)  path 'DESCRIPTION'
           WHERE t.client = G_CONV_CLIENT
    ) pt
    where pt.flex_value_set_name = 'Your_Valueset'

Step 1: Creating the contract

Now all the pieces are in place we can start creating contracts. So we loop through our transactions and for each we transform, validate the data. The main idea is shown below. You get the internal values and replace the tags in the generic header.

      l_bu := get_bu(...);
      l_contract_header := g_contract_header_start;
      l_contract_header := replace(l_contract_header, '[OrgId]',  l_bu);

To get the business unit we use a query on our transformation data, for example

    into l_bu_id
    , x_le_id
      ,      NAME
      FROM xxconv_okc_xml_all t
         , XMLTable('/DATA_DS/BU'
             passing t.IDS
               ORGANIZATION_ID    number(18)     path 'ORGANIZATION_ID'
             , NAME               varchar2(240)  path 'NAME'
    where t.client = G_CONV_CLIENT
    ) bu
    where upper( like upper(l_bu_name || '%');

Depending on whether this is a BUY or SELL contract (which can be found in the setup of the contract type which we downloaded in our pre-liminary step), we also add suppliers or customers and their contacts. Or even other sub-parties can be added.

We also add the roles on the contract, like contractmanager, owner, buyer, etc with  their access level (* Note that during our conversion the access role READ did not seem to work).
A contract party could be something like

  g_contract_party_sell varchar2(2000) default ''||          
' <tran:ContractPartyContact> 
                 </tran:ContractPartyContact> ';

with its own tags to be replaced. Note that we downloaded the suppliers also first before we migrate the data.

Flexfields have a complexity of their own. Especially context dependent flexfields. So depending on the context (usually determine by the contract type), we add a generic flexfield structure and replace the tags. But we always validate the fields in the flexfields before uploading them.

For example a flexfield with the name of the legal rep.

l_context_iden :=   validate_dff (p_dff=> 'SG_LEGALREP',p_value=> r_cur.legal_rep,p_dff_desc=> 'Legal Rep);
l_contract_header_dff := replace(l_contract_header_dff, '[LegalRep]',l_context_iden);

Once we've build up our payload we call the actual webservice

We set the mapping

    l_ns_map := l_ns_map ||'xmlns:env="" ';
    l_ns_map := l_ns_map ||'xmlns:wsa="" ';
    l_ns_map := l_ns_map ||'xmlns:ns0="" ';    
    l_ns_map := l_ns_map ||'xmlns:ns2="" ';
    l_ns_map := l_ns_map ||'xmlns:ns1="" ';

Set the wallet

UTL_HTTP.set_wallet(g_ora_wallet, g_ora_wallet_pwsd);

I've described wallets before in

Then the header and authentication

l_http_request := UTL_HTTP.begin_request(g_contract_ws, 'POST','HTTP/1.1');
UTL_HTTP.SET_AUTHENTICATION(l_http_request, g_fs_user, g_fs_pswd);

We configure the header

    UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml;charset="UTF-8"');
    UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(p_req));
    UTL_HTTP.set_header(l_http_request, 'Transfer-Encoding', 'chunked');
    UTL_HTTP.set_header(l_http_request, 'SOAPAction', '');
Then we write the data in chunks

UTL_HTTP.write_text(l_http_request, l_chunkData);

And perform the call

 l_http_response := UTL_HTTP.get_response(l_http_request);

To read back the response we also use a temporary lob.

 dbms_lob.createtemporary(x_clob, FALSE ); x_clob, dbms_lob.lob_readwrite );
   l_info := 'read text';
        utl_http.read_text(l_http_response, l_buffer);
                          , length(l_buffer)
                          , l_buffer);
      end loop;

End the response


On errors we can subtract the fault string

l_resp_xml := XMLType.createXML(x_clob);
      SELECT  extractValue(l_resp_xml, '/env:Envelope/env:Body/env:Fault/faultstring', l_ns_map)
      INTO    l_fault_string 
      FROM    dual;

And finally we save the response in our progress table for reporting purposes.

Step 2: Activating the contract

The second step is to activate the contract IF it should be activated of course (usually depending on start and end date). We do the same stuff as for creating the contract, but just a different operation.

 l_ns_map := l_ns_map ||'xmlns:env="" ';
    l_ns_map := l_ns_map ||'xmlns:wsa="" ';
    l_ns_map := l_ns_map ||'xmlns:ns0="" ';
    l_ns_map := l_ns_map ||'xmlns:ns2="" ';
    l_ns_map := l_ns_map ||'xmlns:ns1="" ';

And operation

UTL_HTTP.set_header(l_http_request, 'SOAPAction', '');

Step 3: Uploading documents 

The documents may be more complex, depending on where the documents reside. If they are on the server we need to load them into BLOBs before sending. To do that we need to create a directory in DBA_DIRECTORIES to read it.

An attachment payload looks something like this

l_req := '
<soapenv:Envelope xmlns:soapenv="" xmlns:typ="" xmlns:obj="">

Depending on whether it's a buy or sell contract we replace ECM_BUY with ECM_BUY or ECM_SELL.

We get the file from the file server, escape all XML in the filename, description etc. And then open the wallet and do our call again.

 UTL_HTTP.set_header(l_http_request, 'SOAPAction', '');
To get the file from the server we use something like this. So each file is encoded into base64 before sending.

procedure get_file
  ( p_dir  IN VARCHAR2
  , p_file IN VARCHAR2
  , x_skip out varchar2
    l_bfile BFILE;
    l_step  PLS_INTEGER := 12000;
    l_bfile := BFILENAME(p_dir, p_file);
    DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
    if dbms_lob.getlength( l_bfile ) > 0
      FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(l_bfile) - 1 )/l_step) LOOP
        p_clob := p_clob || UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(l_bfile, l_step, i * l_step + 1)));
      END LOOP;
      x_skip := 'N';
      x_skip := 'Y';
    end if;
    when others
  end get_file;    

Some tips 

In the current release (11 and I also think 12) deliverables were not available in the webservice.

We also used a static transformation table, but of course it's possible that you first need to fetch data before you send up your contract information. In order to do that you would have to call a reporting webservice first, fetch the XML data, place it in your transformation table and then upload your contract.
Depending on how often the data changes this may or may not work. For example, downloading all suppliers/customers before uploading a contract is not what you want to do right? So you make sure this is synchronized in an earlier stage and you focus on the contract itself.

dinsdag 26 september 2017

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

<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) {'', '_self', '');
    else if (Version == 6) {
        window.opener = null;
    else {
        window.opener = '';

else {
<body onload="closeWP();">