Activity Master Data Table
On this page, the App Store Team provides you with an overview of how to use the Activity Master Data Table. It can be used to:
Differentiate activities that happen at the item or header Level according to our standard script.
Provide estimations on how long it takes to complete an activity. Can be used for savings potential calculations.
It gives you the freedom to add additional columns to your projects such as documentation and more.
For more information, please contact the App Store team.
Some of the values used are best guesses or assumptions. Feel free to adapt the hard-coded values according to your rollout projects and customer feedback.
Motivation
Customers are frequently asking how to know if an activity was performed on the item or header level. This is very interesting in multiple topics, for instance:
Automation: Often the customer objects to the provided automation ratios provided by Celonis. Their main objection is that our analyses are not taking into account that if one activity on the header level is performed manually this might trigger the execution of many automated activities on the item level.
Business Case Calculations: You can take into account activities at the header or item level to avoid counting activities multiple times. Furthermore, time spent on performing activities can be taken into account to count savings, FTEs among other uses cases.
Activity calculation in the script: Instead of going through the script to understand at which level activity is calculated, you can simply check the Level in the Master Data Table.
Contents of the table
The Activity Master Data Table has a very simple structure. It consists of the following columns:
"LEVEL" - This contains the document level of the activity. Currently, only 'Item', 'Header', and 'Custom' are used. The sole purpose of the latter one is to catch activities for which a level has not been specified.
"AVG_TIME" - This column sets a time (minutes). It is used to provide an estimation of how long it takes to complete the activity.
"ACTIVITY_DE"
"ACTIVITY_EN"
Template
Vertica transformation script template- Create Table: Activity Master Data
DROP TABLE IF EXISTS _CEL_XXX_ACTIVITY_MASTER_DATA; CREATE TABLE _CEL_XXX_ACTIVITY_MASTER_DATA AS ( SELECT DISTINCT "ACTIVITY_DE" AS "ACTIVITY_DE" ,"ACTIVITY_EN" AS "ACTIVITY_EN" ,CASE WHEN "ACTIVITY_DE" IN () THEN 'Header' WHEN "ACTIVITY_DE" IN () THEN 'Item' ELSE 'Custom' END AS "LEVEL" ,CASE WHEN "ACTIVITY_DE" IN () THEN 5 WHEN "ACTIVITY_DE" IN () THEN 60 END AS "AVG_TIME" FROM _CEL_XXX_ACTIVITIES);
How to connect the table to the existing data model
Define your activities, level (header or item for SAP systems) and any attributes you would like to include in the activities table. This step should be performed out of Celonis.
Create the table and populate it using a Transformation or use the Import file functionality to import a csv file with all the information.
Connect the new table in the Data Model.
The Activity Master Data table is simply connected to the designated Activity Table by using 'ACTIVITY_EN' (the activity name) as a foreign key.
![]() |
Example of how it is implemented in Purchase-to-Pay for SAP ECC and Order-to-Cash SAP ECC
Vertica transformation script P2P SAP ECC - Create Table: Activity Master Data
DROP TABLE IF EXISTS _CEL_P2P_ACTIVITY_MASTER_DATA; CREATE TABLE _CEL_P2P_ACTIVITY_MASTER_DATA AS ( SELECT DISTINCT "ACTIVITY_DE" AS "ACTIVITY_DE" ,"ACTIVITY_EN" AS "ACTIVITY_EN" ,CASE WHEN "ACTIVITY_EN" IN ('Cancel Invoice Receipt' ,'Change Approval for Purchase Order', 'Cancel Currency','Change Final Invoice Indicator' , 'Change Vendor' , 'Clear Invoice' , 'Record Invoice Receipt' , 'Send Purchase Order Update' ,'Set Payment Block' , 'Waiting for Approval (Initial)') THEN 'Header' WHEN "ACTIVITY_EN" IN ('Block Purchase Order Item', 'Cancel Goods Receipt' , 'Change Delivery Date (actual)' , 'Change Delivery Date (scheduled)' , 'Change Delivery Indicator' , 'Change Outward Delivery Indicator' , 'Change Rejection Indicator' , 'Change Price' , 'Change Quantity' , 'Change Storage Location' , 'Create Purchase Order Item' , 'Create Purchase Requisition Item', 'Delete Purchase Order Item' , 'PO approved (1st stage)', 'PO approved (2nd stage)' , 'PO approved (direct)' , 'Reactivate Purchase Order Item' , 'Receive Order Confirmation' , 'Record Goods Receipt' , 'Remove Payment Block' , 'Send Purchase Order') THEN 'Item' ELSE 'Custom' END AS "LEVEL" ,CASE WHEN "ACTIVITY_EN" IN ('Create Purchase Order Item' , 'Create Purchase Requisition Item' , 'Delete Purchase Order Item' , 'PO approved (1st stage)' , 'Receive Order Confirmation' , 'Record Goods Receipt' , 'Record Invoice Receipt' , 'Remove Payment Block' , 'Send Purchase Order Update' , 'Set Payment Block' , 'Waiting for Approval (Initial)' , 'Change Outward Delivery Indicator') THEN 5 WHEN "ACTIVITY_EN" IN ('Change Approval for Purchase Order' , 'Cancel Currency' , 'Change Delivery Date (actual)' , 'Change Delivery Date (scheduled)' , 'Change Delivery Indicator' , 'Change Final Invoice Indicator' , 'Change Rejection Indicator' , 'Clear Invoice' ) THEN 10 WHEN "ACTIVITY_EN" = 'Send Purchase Order' THEN 15 WHEN "ACTIVITY_EN" IN ('Cancel Goods Receipt' , 'Record Invoice Receipt' , 'Change Price' , 'Change Quantity' , 'Change Storage Location' , 'PO approved (2nd stage)' , 'PO approved (direct)' , 'Reactivate Purchase Order Item') THEN 30 WHEN "ACTIVITY_EN" IN ('Block Purchase Order Item' , 'Change Vendor') THEN 60 ELSE 30 END AS "AVG_TIME" FROM _CEL_P2P_ACTIVITIES);
HANA transformation script P2P SAP ECC - Create Table: Activity Master Data
-- Query No: 33 CALL {{TARGET_SCHEMA}}.DROP_IF_EXISTS ('{{TARGET_SCHEMA}}', '_CEL_P2P_ACTIVITY_MASTER_DATA'); -- Query No: 34 CREATE COLUMN TABLE {{TARGET_SCHEMA}}._CEL_P2P_ACTIVITY_MASTER_DATA AS ( SELECT DISTINCT ACTIVITY_DE AS "ACTIVITY_DE" ,ACTIVITY_EN AS "ACTIVITY_EN" ,CASE WHEN ACTIVITY_EN IN ('Cancel Invoice Receipt' ,'Change Approval for Purchase Order', 'Cancel Currency','Change Final Invoice Indicator' , 'Change Vendor' , 'Clear Invoice' , 'Record Invoice Receipt' , 'Send Purchase Order Update' ,'Set Payment Block' , 'Waiting for Approval (Initial)') THEN 'Header' WHEN ACTIVITY_EN IN ('Block Purchase Order Item', 'Cancel Goods Receipt' , 'Change Delivery Date (actual)' , 'Change Delivery Date (scheduled)' , 'Change Delivery Indicator' , 'Change Outward Delivery Indicator' , 'Change Rejection Indicator' , 'Change Price' , 'Change Quantity' , 'Change Storage Location' , 'Create Purchase Order Item' , 'Create Purchase Requisition Item', 'Delete Purchase Order Item' , 'PO approved (1st stage)', 'PO approved (2nd stage)' , 'PO approved (direct)' , 'Reactivate Purchase Order Item' , 'Receive Order Confirmation' , 'Record Goods Receipt' , 'Remove Payment Block' , 'Send Purchase Order') THEN 'Item' ELSE 'Custom' END AS "LEVEL" ,CASE WHEN ACTIVITY_EN IN ('Create Purchase Order Item' , 'Create Purchase Requisition Item' , 'Delete Purchase Order Item' , 'PO approved (1st stage)' , 'Receive Order Confirmation' , 'Record Goods Receipt' , 'Record Invoice Receipt' , 'Remove Payment Block' , 'Send Purchase Order Update' , 'Set Payment Block' , 'Waiting for Approval (Initial)' , 'Change Outward Delivery Indicator') THEN 5 WHEN "ACTIVITY_EN" IN ('Change Approval for Purchase Order' , 'Cancel Currency' , 'Change Delivery Date (actual)' , 'Change Delivery Date (scheduled)' , 'Change Delivery Indicator' , 'Change Final Invoice Indicator' , 'Change Rejection Indicator' , 'Clear Invoice' ) THEN 10 WHEN "ACTIVITY_EN" = 'Send Purchase Order' THEN 15 WHEN "ACTIVITY_EN" IN ('Cancel Goods Receipt' , 'Record Invoice Receipt' , 'Change Price' , 'Change Quantity' , 'Change Storage Location' , 'PO approved (2nd stage)' , 'PO approved (direct)' , 'Reactivate Purchase Order Item') THEN 30 WHEN "ACTIVITY_EN" IN ('Block Purchase Order Item' , 'Change Vendor') THEN 60 ELSE 30 END AS "AVG_TIME" FROM {{TARGET_SCHEMA}}._CEL_P2P_ACTIVITIES );
Vertica transformation script O2C SAP ECC - Create Table: Activity Master Data
DROP TABLE IF EXISTS _CEL_O2C_ACTIVITY_MASTER_DATA; CREATE TABLE _CEL_O2C_ACTIVITY_MASTER_DATA AS ( SELECT DISTINCT "ACTIVITY_DE" AS "ACTIVITY_DE" ,"ACTIVITY_EN" AS "ACTIVITY_EN" ,CASE WHEN "ACTIVITY_EN" IN ('Create Quotation' ,'Create Purchase Order', 'Create Picking', 'Quotation Statuses' , 'Sales Order Statuses' , 'Approve Credit Check' , 'Deny Credit Check' , 'Partially approve Credit Check' ,'Set Delivery Block' , 'Change Delivery Block', 'Remove Delivery Block', 'Change Delivery Block', 'Delete Delivery Note', 'Record Goods Issue', 'Cancel Goods Issue', 'Create Billing Documents', 'Create Other Billing Document', 'Change Shipping Type', 'Change Freight Terms', 'Change Payment Terms', 'Change Inco Terms (Part 1)', 'Change Inco Terms (Part 2)', 'Due Date Passed', 'Material Availability Date Passed',' Clear Invoice') THEN 'Header' WHEN "ACTIVITY_EN" IN ('Create Sales Order Item', 'Set Reason for Rejection' , 'Change Reason for Rejection' , 'Cancel Reason for Rejection' , 'Change Price' , 'Change Division' , 'Book Delivery Shortage' , 'Change Delivery Amount' , 'Change Material' , 'Change Plant' , 'Change Route' , 'Create Delivery Documents', 'Create Other Delivery Documents') THEN 'Item' ELSE 'Custom' END AS "LEVEL" ,CASE WHEN "ACTIVITY_EN" IN ('Due Date Passed', 'Material Availability Date Passed') THEN 0 WHEN "ACTIVITY_EN" IN ('Create Quotation' ,'Create Purchase Order', 'Quotation Statuses' , 'Sales Order Statuses' ,'Record Goods Issue', 'Create Billing Documents', 'Create Other Billing Document', 'Create Sales Order Item', 'Book Delivery Shortage' , 'Create Delivery Documents', 'Create Other Delivery Documents') THEN 5 WHEN "ACTIVITY_EN" IN ('Partially approve Credit Check','Clear Invoice','Cancel Goods Issue') THEN 10 WHEN "ACTIVITY_EN" IN ('Change Delivery Block','Remove Delivery Block', 'Change Delivery Block', 'Delete Delivery Note', 'Change Delivery Amount' ,'Change Material' , 'Change Plant' , 'Change Route' , 'Create Picking','Set Reason for Rejection' , 'Change Reason for Rejection' , 'Cancel Reason for Rejection' ,'Change Price' ,'Change Division') THEN 30 WHEN "ACTIVITY_EN" IN ('Approve Credit Check' , 'Deny Credit Check' , 'Change Shipping Type', 'Change Freight Terms', 'Change Payment Terms', 'Change Inco Terms (Part 1)', 'Change Inco Terms (Part 2)','Set Delivery Block') THEN 60 ELSE 30 END AS "AVG_TIME" --Minutes FROM _CEL_O2C_ACTIVITIES );
HANA transformation script O2C SAP ECC - Create Table: Activity Master Data
-- Statement: Create Table: _CEL_O2C_ACTIVITY_MASTER_DATA -- Query No: 1 CALL {{TARGET_SCHEMA}}.DROP_IF_EXISTS ('{{TARGET_SCHEMA}}', '_CEL_O2C_ACTIVITY_MASTER_DATA'); -- Query No: 2 CREATE COLUMN TABLE {{TARGET_SCHEMA}}._CEL_O2C_ACTIVITY_MASTER_DATA AS ( SELECT DISTINCT ACTIVITY_DE AS "ACTIVITY_DE" ,ACTIVITY_EN AS "ACTIVITY_EN" ,CASE WHEN ACTIVITY_EN IN ('Create Quotation' ,'Create Purchase Order', 'Create Picking', 'Quotation Statuses' , 'Sales Order Statuses' , 'Approve Credit Check' , 'Deny Credit Check' , 'Partially approve Credit Check' ,'Set Delivery Block' , 'Change Delivery Block', 'Remove Delivery Block', 'Change Delivery Block', 'Delete Delivery Note', 'Record Goods Issue', 'Cancel Goods Issue', 'Create Billing Documents', 'Create Other Billing Document', 'Change Shipping Type', 'Change Freight Terms', 'Change Payment Terms', 'Change Inco Terms (Part 1)', 'Change Inco Terms (Part 2)', 'Due Date Passed', 'Material Availability Date Passed',' Clear Invoice') THEN 'Header' WHEN ACTIVITY_EN IN ('Create Sales Order Item', 'Set Reason for Rejection' , 'Change Reason for Rejection' , 'Cancel Reason for Rejection' , 'Change Price' , 'Change Division' , 'Book Delivery Shortage' , 'Change Delivery Amount' , 'Change Material' , 'Change Plant' , 'Change Route' , 'Create Delivery Documents', 'Create Other Delivery Documents') THEN 'Item' ELSE 'Custom' END AS "LEVEL" ,CASE WHEN "ACTIVITY_EN" IN ('Due Date Passed', 'Material Availability Date Passed') THEN 0 WHEN "ACTIVITY_EN" IN ('Create Quotation' ,'Create Purchase Order', 'Quotation Statuses' , 'Sales Order Statuses' ,'Record Goods Issue', 'Create Billing Documents', 'Create Other Billing Document', 'Create Sales Order Item', 'Book Delivery Shortage' , 'Create Delivery Documents', 'Create Other Delivery Documents') THEN 5 WHEN "ACTIVITY_EN" IN ('Partially approve Credit Check','Clear Invoice','Cancel Goods Issue') THEN 10 WHEN "ACTIVITY_EN" IN ('Change Delivery Block','Remove Delivery Block', 'Change Delivery Block', 'Delete Delivery Note', 'Change Delivery Amount' ,'Change Material' , 'Change Plant' , 'Change Route' , 'Create Picking','Set Reason for Rejection' , 'Change Reason for Rejection' , 'Cancel Reason for Rejection' ,'Change Price' ,'Change Division') THEN 30 WHEN "ACTIVITY_EN" IN ('Approve Credit Check' , 'Deny Credit Check' , 'Change Shipping Type', 'Change Freight Terms', 'Change Payment Terms', 'Change Inco Terms (Part 1)', 'Change Inco Terms (Part 2)','Set Delivery Block') THEN 60 ELSE 30 END AS "AVG_TIME" --Minutes FROM {{TARGET_SCHEMA}}._CEL_O2C_ACTIVITIES );
Outcome of the exemplary transformation snippet once it was executed:
ACTIVITY_DE | ACTIVITY_EN | LEVEL | AVG_TIME |
---|---|---|---|
Ändere Lieferplandatum | Change Schedule Line Date | Custom | 30 |
Sende Bestellung | Send Purchase Order | Item | 15 |
Lege BANF Position an | Create Purchase Requisition Item | Item | 5 |
Erfasse Wareneingang | Record Goods Receipt | Item | 5 |
Erfasse Leistungserfassungsblatt | Record Service Entry Sheet | Custom | 30 |
Erfasse Rechnungseingang | Record Invoice Receipt | Header | 5 |
Lege Bestellposition an | Create Purchase Order Item | Item | 5 |
Lösche Bestellposition | Delete Purchase Order Item | Item | 5 |
Sende Bestelländerung | Send Purchase Order Update | Header | 5 |
Storniere Rechnungseingang | Cancel Invoice Receipt | Header | 30 |
Ändere Preis | Change Price | Header | 30 |
Ändere Freigabe für Bestellung | Change Approval for Purchase Order | Header | 10 |
Storniere Wareneingang | Cancel Goods Receipt | Item | 30 |
Ändere Menge | Change Quantity | Item | 30 |
Using the Activity Master Data Table in KPI calculation (Exemplary use case: Ratio of automated activities)
One of the benefits of this helper table is being able to distinguish activities that are either performed on the header or item level. This applies to most of our use cases as we are still mainly focused on document data from SAP ECC.
Currently - in most cases - we do not distinguish between the document header and item level when calculating a KPI that is based on activity. One example of this is the calculation of the automation ratio. The current formula does not account for whether an activity is performed on the document header or on the document item level.
As a result, many customers object to the automation ratio calculation provided by Celonis.
PQL Formula App Store Analysis "Automation":
Ratio of automated activities (Automation Ratio)
AVG( CASE WHEN "_CEL_P2P_ACTIVITIES"."USER_TYPE" IN (<%= auto_user_type %>) THEN 1.0 ELSE 0.0 END )
Using the "LEVEL" column from the Activity Master Data Table, the PQL statement can be adjusted to include a weighting of activities that are performed on the document header or document item level.
In development: Automation 2.0 :
New weighted ratio of automated activities (Automation Ratio)
SUM( CASE WHEN "_CEL_P2P_ACTIVITIES"."USER_TYPE" IN (<%= auto_user_type %>) AND "_CEL_P2P_ACTIVITY_MASTER_DATA"."LEVEL" = 'Item' THEN 1.0 WHEN "_CEL_P2P_ACTIVITIES"."USER_TYPE" IN (<%= auto_user_type %>) AND "_CEL_P2P_ACTIVITY_MASTER_DATA"."LEVEL" = 'Header' THEN 1.0 / (PU_COUNT("EKKO", "EKPO"."EBELP")) ELSE 0.0 END ) / SUM( CASE WHEN "_CEL_P2P_ACTIVITY_MASTER_DATA"."LEVEL" = 'Item' THEN 1.0 WHEN "_CEL_P2P_ACTIVITY_MASTER_DATA"."LEVEL" = 'Header' THEN 1.0 / (PU_COUNT("EKKO", "EKPO"."EBELP")) ELSE 0.0 END )
This approach is not only limited to the automation use case. It can be applied to any use case that focuses specifically on an activity-based KPI.
Benefits in business case calculation
Another benefit of this table is an arguably more precise business case calculation within a Celonis analysis. Under the assumption that most of the processing times are estimated correctly and have been validated by the customer the calculated potential savings calculated this way are more precise and less prone to objections by a customer.
Each activity count of every distinct activity specified in the helper table is multiplied by the specified individual processing times. The result is a total processing time which is then multiplied by the average cost of labor per minute.
Q + A
Q: 'In the Analysis X when we use custom table _CEL_P2P_ACTIVITY_MASTER_DATA , it uses proxies for avg . time as 5- 60. I l believe this is minutes? is it a good benchmark to consider Y minutes for 'Activity A'?
A: The assumptions made in this additional table are more than debatable. Please feel free to change it during your rollout projects. We are also happy to adjust the hard-coded durations and levels of activities based on customer feedback in the standard scripts.