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).