Missing times for timestamps in SAP purchasing

Since some time i am working on the process mining of a purchasing process. It is a part of my thesis. The database , that i use fpr Celonis academic Version, is provided by SAP ERP R/3. the protocol tables EBAN, EKPO and EKKO, as well as the other tables that belong to the purchasing process, are time-stamped with date but without time. the consequence problem is that the process flow cannot be mapped on a daily basis. This means, that within a business case that took place on one day, the timestamps cannot be sorted by time, because the times in SAP are missing. I know that many other companies have such an SAP purchasing module, which maps business cases only on a daily basis. how do they prepare the data for Celonis, if you also have the missing time stamps ?

Hier are some examples:

Thank you for your experience in advance.
Best Regards,
Maria

Hey Maria,

Thank you for the good question!

The issue is easily resolved by appending an optional Sorting column to your activation table / event log and then specifying it when loading the data into Celonis. This sort column must contain ascending integers with the lowest value for the first activity performed.

See the example below for clarification, the sorting will then lead to the correct display of the process sequence in Celonis:

Best Regards,
Benedict

FYI: From the processing logic point of view, the sort table is only used if two activities were executed absolutely simultaneously (as is the case with you). If two activities are performed following each other, it will be displayed accordingly within Celonis with disregard to the specified sorting.

Hi Maria,
First thing to do would be to investigate if there is an alternative approach for the retrieving the timestamp. In the case of the “Create purchase order” activity, you are right that within EKKO/EKPO you only have a creation date. However this event is also tracked within SAP’s changelog (CDHDR/CDPOS) and there you will find a full timestamp.

If there is really no time available then the sorting column is used (see @Bene’s post). Please note also, that Celonis derives a timestamp based on the time of neighboring events (when sorted by day and sorting). Therefore don’t be surprised if you see a full timestamp in Celonis while only having a date on the database.

Best regards,
Pol

Hi Bene,
thank you very much for your solution!
I also thought earlier about it. But the goal behind it stops me: I want to measure the actual processes (as-is-processes), not the target processes (must-have-processes). If I would set the sequence myself, then I would not be able to see the process deviations or compliance violations.
That would be my opinion, maybe someone has a different opinion.
Best Regards,
Maria

Hello Pol,

Thank you for the information, I actually found the time stamps for purchase orders in the SAP table CDHDR. After I filtered them to CHANGE_IND, I got the created orders with “I” and the changed orders with “U”. This solved the problem with the completeness of the time stamps (date + time).
My SAP table CDPOS unfortunately contains any time stamps.
The table CDHDR even gave me the times of changing of purchase requisitions (BANFN). But there is no reference to purchase orders (EBELN), so I couldn’t use the information. What also surprised me a lot was that the time of creating an order requisition was not recorded in CDHDR - the data is simply missing.
Below is an example of what it looks like in my case: I still lack time stamps for purchase requisitions.
%20BANFen
In red: Perhaps someone knows in what SAP tables the time stamps for purchase requisitions could be found?
Thank you very much for your effort and further information.

Best Regards,
Maria

Hey Maria,

Normally the exact timestamps for Purchase Requisitions are found in the CDHDR analogous to EKKO/EKPO.
Did you try finding the corresponding PR No. from the EBAN in the CDHDR?
The relationship is OBJECTID = PR No.

Best regards,
Bene

Hi, Bene,
thank you for the information. Exactly, that’s what I also found.
As Case ID I use the order numbers (EBELN) for my dataset. The purchase requisition numbers (BANFN) can be found in the SAP table CDPOS, but I can’t assign them to order numbers. Or would it be possible in Celonis?

Best regards,
Maria

Hey Maria,

I think I can understand the problem you are having better now.

Please keep in mind, that you have to search the BANFN in CDHDR field OBJECTID, as only CDHDR contains the relevant time data.

Additionally, depending on the SAP System settings, it could be that only changes to the Purchase Requisition are tracked and not the creation itself. Also depending of the system settings it could well be that you have to search the BANFN with preceding zeros in order to be able to join the EBAN with the CDHDR and find the relevant data you are looking for.

You can find out how many preceding zeros you need and if the creation and changes are even being tracked in CDHDR by filtering for the TCODEs : “ME51” (‘Create Purchase Requisition’) and “ME52” (‘Change Purchase Requisition’). If there are no CDHDR Entries with these TCODEs the data you are looking for isn’t available. If there are entries, look at character length of the OBJECTIDs: Any character above 10 has to be added as a preceding zero to your BANFN.

Best regards,

Bene

Hi, Bene,

thank you very much for confirming my assumptions. in fact, in the CDHDR table I can only find the time stamps for the process steps “Create order” and “Change order”.
that works quite well, like order numbers with “I” are the created orders, and those with “U” - changed.


The problem occurs when assigning purchase requisitions to orders. as you can see below on the picture, i can only see the purchase requisition numbers in the CDHDR table, but there is no connection or reference how to assign them to the purchase order numbers (in my dataset my Case ID is EBELN so purchase order number, not BANFN)

would you have any idea? Thank you very much in advance!

Best regards,
Maria

Hey Maria,

If I understand it right the logic for the join is the problem.

What you do is, in order to create the activity table, enriching the EBAN Data with the CDHDR Data (so the timestamp and activities for the BANFN which are contained in the CDHDR) and then join EBAN and EKPO with the regular CASE Key, thus you don’t need to reference the BANFN-OBJECTID with the EBELN.

Best regards,

Bene

Edit: English.

Dear Benedict,

Thanks for your question. Your problem is well know. When you know how the purcase requisition are created / changed the solution is very simple. In most of the companies PR are not created manually but through MRP. Also the changes (date / amounts) are done by MRP. We have companies worldwide with different time zones and we saw that we get all kind of silly behaviours due to that.
Since all PR steps are done by background jobs and not manually we decided to skip them completely. We did not see any business value for the monitoring of them.

Have :grinning:!
Hans.

Dear Benedict,
Thanks for the idea. I have joined the data with SVERWEIS in Excel.
But there is despite one problem: only one timestemp can be taken. If there more than one change of PR, Excel can not allocate it. For example, as you can see on the picture, if I want to assign the order request 0015384120 to one or more order numbers, only one time stamp is taken, although there are many.

Best regards,
Maria

Hey Maria,

I don’t think an SVERWEIS is the best solution for this kind of task. If your data is saved locally on the computer you are working on, then easiest approach for you would probably be working in Access or any similar visual SQL Editor.

If you want to do it in Excel, then please keep in mind that EBAN and CDHDR is a 1:n relationship. Best is you assign the BANFN-entries in CDHDR over the EBAN-EKPO connection their respective Case IDs like you would do with EKPO-CDHDR or similar and give them their according activity and timestamp. Later you can just copy paste your entries in to your activity table like you did before.

Many regards,

Benedict

Hello,

thank you very much for your contributions. After a longer tests and tryings the problem was solved. Here I would like to present the solution.

To illustrate the process step “order created” I took the data from SAP tables CDHDR (time stamps - UDATE and UTIME) and CDPOS (change types of orders - FNAME).

First, I narrowed down the period for which I wanted to analyze the created orders. For example, all orders that were created in one quarter. I found the purchasing document numbers in table CDHDR, where I set the “I” (insert) for CHANGE_ING. Also i have set the “EKKO” (this SAP table contains all created purchase orders) for TABNAME in table CDPOS. Tables CDHDR and CDPOS were linked in Access using change number CHANGENR.

Then I linked the data in Access with the following join and later transferred them to Excel:


In this way the process step “Order created” with complete time stamps (date and time of day) was extracted from SAP.

Many regards,
Maria