Generate Your Own Event Log From Oracle E-Business Suite

Know your tools!

This is a guest post by Marcel Koolwijk. If you have a process mining article or case study that you would like to share as well, please contact us at anne@fluxicon.com.

Generate your own event log

To be able to do process mining you need to have some data. Data can come from many sources and some sources are better structured for generating event logs than others. ERP applications in general, and the Oracle E-Business Suite in particular, are great sources for event logs. But ERP applications do not generate event logs automatically in a way that you can use for process mining. So there is some work to be done. The challenge is to translate the existing data from the table structure of the ERP application into an event log that can be used for process mining. Because of the complexity of the table structure you will need to have in-depth knowledge about the ERP application to make this translation, and to create an extraction program that generates the event log.

However, as a first step – before you start the (often time-consuming) work of writing functional designs, technical designs, and getting your IT department involved – you typically just want to get some data from your ERP application to try out process mining for your own processes and get some hands on experience.

This article gives you an example with step-by-step instructions for how you can quickly get some first data from your own Oracle E-Business Suite to get started.

Oracle EBS version and the tools you need

You can use the description below for an Oracle E-Business Suite Release 12.1 installation but it probably also works fine (although not tested) for any other release of the Oracle E-Business Suite.

For generating the event log it is easiest if you have SQL query access to the database (just query access is sufficient for now). If you do not have query access to the database then there are other options as well, but for the description below I assume you do have SQL query access. I use SQL Developer from Oracle as SQL query tool, but any other SQL tool should work in a similar way.

Other than the SQL query access to the database, there is no installation or setup required in the Oracle E-Business Suite in order to generate the event log.

Step-by-step Instructions

The process that we are looking at is the requisition process in Oracle iProcurement. We will extract data from the approval process for the last 1000 requisitions.

As case ID we use the internal requisition header ID. The activity is the name of the activity that Oracle stores in the table. We use the date the action is performed as the time stamp and as resource we use the employee ID. For now, we just add the org ID and the requisition number as additional attributes, but any further attribute can be added rather easily.

Here are the step-by-step instructions to create your first event log from your Oracle E-Business Suite:

Step 1

Logon to the database with you query account in Oracle SQL Developer

Step 2

Run the query below:

SELECT PRH.REQUISITION_HEADER_ID AS CASE_ID , 'Requisition '||FLV.MEANING AS ACTIVITY_NAME , TO_CHAR(PAH.ACTION_DATE,'DD-MM-YYYY HH24:MI:SS') AS TIME_STAMP , PAH.EMPLOYEE_ID AS RESOURCE_ID , PRH.ORG_ID AS ORG_ID, PRH.SEGMENT1 AS REQUISITION_NUMBER FROM PO.PO_REQUISITION_HEADERS_ALL PRH INNER JOIN PO.PO_ACTION_HISTORY PAH ON PAH.OBJECT_ID = PRH.REQUISITION_HEADER_ID INNER JOIN FND_LOOKUP_VALUES FLV ON FLV.LOOKUP_CODE = PAH.ACTION_CODE AND FLV.LOOKUP_TYPE = 'APPR_HIST_ACTIONS' AND PAH.OBJECT_TYPE_CODE = 'REQUISITION' AND PAH.OBJECT_SUB_TYPE_CODE = 'PURCHASE' AND FLV.LANGUAGE = 'US' WHERE PRH.REQUISITION_HEADER_ID > (SELECT MAX(REQUISITION_HEADER_ID) FROM PO_REQUISITION_HEADERS_ALL)-1000;

The result of the query will be shown in Oracle SQL Developer: