02 - Real Time Transformations for Activities

Celonis Product Documentation

02 - Real Time Transformations for Activities

Step 1: Identify the Trigger Table
Each activity needs to be mapped to a trigger table whose extraction triggers the respective transformation statement.
Warning
The identification of the trigger table for activities is probably the most important but at the same time also a very difficult step.
The trigger table of an activity is the table of which a new/updated record leads to the creation of the activity itself.
In most cases, the trigger table is the one that contains the event timestamp, or a corresponding Header/Item table. However, for each activity, the logic and the sequence of the record appearance within the tables should be analyzed. You will need this mapping to save the transformations under the respective tables in the Replication Cockpit, so that they are executed only against the respective records.
For the creation of the activity, you will need to select from the staging table of the respective trigger table.
→ when the trigger is VBFA, you will use _CELONIS_TMP_VBFA_TRANSFORM_DATA

Step 2: Split the common Activity table into several smaller ones

With the concept of Delta Transformations, it is not possible to have one large activity table anymore. It can happen that several trigger tables try to write to the activity table at the same time, leading to a table lock and transformation failures. To prevent this, each trigger table should have its own dedicated activity table. For CDHDR it could be _CEL_O2C_CDHDR_ACTIVITIES.

Note

To bring the dedicated activity tables back together and use it as one common activity table in the Data Model, we will create a view that unions all activity tables. This view needs to be created only once, as it is calculated each time it is referenced (in the Data Model). You can find an example code snippet for the view creation below.

Create activity tableBefore

-- This code creates a new dedicated activity table for all activities triggered by CDHDR
-- based on the columns that are defined in the existing full table _CEL_O2C_ACTIVITIES
-- Note: needs to be executed only once

CREATE TABLE _CEL_O2C_CDHDR_ACTIVITIES LIKE _CEL_O2C_ACTIVITIES;

Create activity view (union)Before

-- This code creates the activity view that combines all dedicated activity tables together
-- Make sure to adapt it based on the activity tables that exist for your case
-- Note: needs to be executed only once

DROP VIEW IF EXISTS _CEL_O2C_ACTIVITIES;
CREATE VIEW _CEL_O2C_ACTIVITIES AS (
    SELECT * FROM _CEL_O2C_VBAK_ACTIVITIES   
    UNION ALL
    SELECT * FROM _CEL_O2C_VBUK_ACTIVITIES    
    UNION ALL  
    SELECT * FROM _CEL_O2C_VBAP_ACTIVITIES    
    UNION ALL
    SELECT * FROM _CEL_O2C_LIKP_ACTIVITIES    
    UNION ALL
    SELECT * FROM _CEL_O2C_JCDS_ACTIVITIES    
    UNION ALL
    SELECT * FROM _CEL_O2C_LIPS_ACTIVITIES    
    UNION ALL   
    SELECT * FROM _CEL_O2C_VBRP_ACTIVITIES    
    UNION ALL  
    SELECT * FROM _CEL_O2C_VBEP_ACTIVITIES    
    UNION ALL  
    SELECT * FROM _CEL_O2C_NAST_ACTIVITIES    
    UNION ALL  
    SELECT * FROM _CEL_O2C_VBFA_ACTIVITIES    
    UNION ALL
    SELECT * FROM _CEL_O2C_VTTP_ACTIVITIES   
    UNION ALL  
    SELECT * FROM _CEL_O2C_BSAD_ACTIVITIES    
    UNION ALL   
    SELECT * FROM _CEL_O2C_CDHDR_ACTIVITIES 
);

Step 3: Implement a suitable Delta Approach

For activities, there are 3 different approaches to perform delta transformations, which depend on the logic of the activity itself.

Tip

To simplify the choice of a suitable approach, we provide a matching table for the main 4 processes (O2C, AP, P2P, AR) that shows the recommended approach below.

	Insert	Insert where not exists	Merge
When to use?	no updates of activities required after the first creation no updates possible in the trigger table (insert only) or new activity for every update wanted	no updates of activities are required after the first creation updates are possible in the trigger table	updates of activities after the first creation required updates are possible in the trigger table
Implications	Creation or updating a record in the trigger table always creates a new activity.	Creation or updating a record in the trigger table creates a new activity when one with the specified conditions does not exist yet.	Creation or updating a record in the trigger table creates a new activity and updates existing ones that fulfill the specified condition.
Disadvantage		Activities cannot be updated, once they were created	very complex script (might require introducing an Activity Key)
Performance	+++	++	+
Example	Currenlty, there are no applicable scenarios for the SAP Main 4 standard scripts to use this approach.	below	below

Standard Connector Matching Table - Main 4 Processes

Process	Trigger Table	Activity
O2C	BSAD	Clear Invoice
O2C	CDHDR	Change Sales Order
O2C		Change Sales Order Item
O2C		Delete Delivery Note
O2C		Change Business Data
O2C		Change Schedule Line
O2C	JCDS	Quotation Statuses
O2C	JCDS	Sales Order Statuses
O2C	LIKP	Create Picking (Sales Order)
O2C	LIPS	Create Delivery Documents
O2C	NAST	Send/Update Order Confirmation
O2C	VBAK	Initial Delivery Block
O2C	VBAK	Initial Billing Block
O2C	VBAP	Create Sales Order
O2C	VBAP	Create Sales Order Item
O2C	VBEP	Material Availability Date passed
O2C	VBFA	Create Quotation
O2C		Create Purchase Order
O2C		Records/Cancel Goods Issue
O2C	VBRP	Create Billing Documents
O2C	VBUK	Initial Credit Block
O2C	VTTP	Create Shipment

Process	Trigger Table	Activity
AR	BKPF	Reversals
	BSAD	Create Invoice
		Create Credit Memo
		Enter in SAP
		Clear Invoice
		Clear Credit Memo
		Cash Discount Due Date Passed
		Due Date Passed
	BSID	Create Invoice
		Enter in SAP
		Create Credit Memo
		Cash Discount Due Date Passed
		Due Date Passed
	CDHDR	Changes
	CDHDR	Overdue Notices
	VBFA	Record Goods Issue

Example - Merge

-- This code creates the activity 'Clear Invoice' based on the 'Merge' approach
-- A new activity is added or an existing one is updated when the specified conditions are met.

MERGE INTO _CEL_BSAD_ACTIVITIES AS TARGET_TABLE
USING(
        SELECT DISTINCT
                "V_ORDERS"."_CASE_KEY" AS "_CASE_KEY"
        ,CASE
            WHEN "DD07T_DE"."DDTEXT" IS NOT NULL THEN 'Gleiche ' || "DD07T_DE"."DDTEXT" || ' aus'
            ELSE 'Gleiche sonstiges Rechnungsdokument aus'
        END AS "ACTIVITY_DE"
        ,CASE
            WHEN "DD07T_EN"."DDTEXT" = 'Intercompany invoice' THEN 'Clear Internal Offset Invoice' 
            WHEN "DD07T_EN"."DDTEXT" IS NOT NULL THEN 'Clear ' || "DD07T_EN"."DDTEXT"
            ELSE 'Clear Other Billing Document'
        END AS "ACTIVITY_EN"
                ,CAST("BKPF_PAY"."CPUDT" AS DATE) + CAST("BKPF_PAY"."CPUTM" AS TIME) AS "EVENTTIME" 
        , 100 AS "_SORTING"
                ,"V_ORDERS"."MANDT" AS "MANDT"
                ,"V_ORDERS"."VBELN" AS "VBELN"
                ,"V_ORDERS"."POSNR" AS "POSNR"
                , "BKPF_PAY"."USNAM" AS "USER_NAME"
                ,CASE WHEN ("USR02"."USTYP" = 'A' AND "USR02"."CLASS" = 'IT-SYST') THEN 'B' ELSE "USR02"."USTYP" END AS "USER_TYPE"
FROM
        _CELONIS_TMP_BSAD_TRANSFORM_DATA  AS BSAD
        INNER JOIN "BKPF" AS "BKPF_PAY"
                AND "BSAD"."MANDT" = "BKPF_PAY"."MANDT"
                AND "BSAD"."BUKRS" = "BKPF_PAY"."BUKRS"
                AND "BSAD"."AUGBL" = "BKPF_PAY"."BELNR"
                AND "BSAD"."AUGGJ" = "BKPF_PAY"."GJAHR"
        INNER JOIN  "BKPF" AS "BKPF_INV" 
                AND "BSAD"."MANDT" = "BKPF_INV"."MANDT"
                AND "BSAD"."BUKRS" = "BKPF_INV"."BUKRS"
                AND "BSAD"."BELNR" = "BKPF_INV"."BELNR"
                AND "BSAD"."GJAHR" = "BKPF_INV"."GJAHR"
                AND "BSAD"."AUGDT" IS NOT NULL
   INNER JOIN "VBRK" AS "VBRK" 
        AND "BKPF_INV"."MANDT" = "VBRK"."MANDT" 
        AND "BKPF_INV"."AWKEY" = "VBRK"."VBELN" 
        AND "BKPF_INV"."AWTYP" = 'VBRK'
    INNER JOIN "VBRP" AS "VBRP" 
        AND "VBRP"."MANDT" = "VBRK"."MANDT"
        AND "VBRP"."VBELN" = "VBRK"."VBELN" 
        INNER JOIN      "VBFA" 
                AND "VBFA"."MANDT" = "VBRP"."MANDT"
                AND "VBFA"."VBELN" = "VBRP"."VBELN"
                AND "VBFA"."POSNN" = "VBRP"."POSNR"
        INNER JOIN 
                        ( SELECT DISTINCT
                                "VBAK"."MANDT"
                                ,"VBAK"."VBELN"
                                ,"VBAP"."POSNR"
                                ,"VBAK"."BUKRS_VF"
                                ,"VBAK"."KUNNR"
                                ,"VBAK"."MANDT" || "VBAK"."VBELN" || "VBAP"."POSNR" AS "_CASE_KEY"
                                ,"VBAK"."MANDT" || "VBAK"."VBELN" AS "TABKEY_VBAK"
                                , "VBAP"."AUFNR"
                                , "VBAK"."KNUMV"
                                FROM "VBAK"
                                JOIN "VBAP" ON 
                                        "VBAK"."MANDT" = "VBAP"."MANDT"
                                        AND "VBAK"."VBELN" = "VBAP"."VBELN"
                                WHERE ("VBAK"."VBTYP" = 'C' OR "VBAK"."AUART" = 'ZLZ')
                        )AS V_ORDERS 
                                AND "V_ORDERS"."MANDT" = "VBFA"."MANDT"
                                AND "V_ORDERS"."VBELN" = "VBFA"."VBELV"
                                AND "V_ORDERS"."POSNR" = "VBFA"."POSNV"
    LEFT JOIN "DD07T" AS "DD07T_EN"
                AND "DD07T_EN"."DOMNAME" = 'VBTYP'
                AND "DD07T_EN"."DDLANGUAGE" = 'E'
                AND "DD07T_EN"."DOMVALUE_L" = "VBRK"."VBTYP"
        LEFT JOIN "DD07T" AS "DD07T_DE"
                AND "DD07T_DE"."DOMNAME" = 'VBTYP'
                AND "DD07T_DE"."DDLANGUAGE" = 'D'
                AND "DD07T_DE"."DOMVALUE_L" = "VBRK"."VBTYP"
        LEFT JOIN "USR02" AS "USR02"
                AND "BKPF_PAY"."MANDT" = "USR02"."MANDT"
                AND "BKPF_PAY"."USNAM" = "USR02"."BNAME"
) AS SOURCE_TABLE
                ON "SOURCE_TABLE"."_CASE_KEY" = "TARGET_TABLE"."_CASE_KEY"
                AND "SOURCE_TABLE"."ACTIVITY_EN" = "TARGET_TABLE"."ACTIVITY_EN"
        AND "SOURCE_TABLE"."EVENTTIME" = "TARGET_TABLE"."EVENTTIME"
WHEN MATCHED THEN UPDATE
        SET
                "EVENTTIME" = SOURCE_TABLE."EVENTTIME"      
                ,"USER_NAME" = SOURCE_TABLE."USER_NAME"
                ,"USER_TYPE" = SOURCE_TABLE."USER_TYPE"     
WHEN NOT MATCHED THEN
INSERT  (
        "_CASE_KEY",
        "ACTIVITY_DE",
        "ACTIVITY_EN",
        "EVENTTIME",
        "_SORTING",
        "MANDT",
        "VBELN",
        "POSNR",
        "USER_NAME",
        "USER_TYPE")
VALUES 
     (SOURCE_TABLE.*)
;

Example - Insert where not exists

-- This code creates the activity 'Create Sales Order Item' based on the 'Insert where not exists' approach
-- A new activity is only added when one for the sales order item doesn't exist yet.

INSERT INTO _CEL_O2C_VBAP_ACTIVITIES (
    _CASE_KEY,
    ACTIVITY_DE,
    ACTIVITY_EN,
    EVENTTIME,
    _SORTING,
    USER_NAME,
    USER_TYPE,
    MANDT,
    VBELN,
    POSNR, 
    TRANSACTION_CODE,
    _ACTIVITY_KEY)

SELECT * FROM (

    SELECT 
        VBAP.MANDT || VBAP.VBELN || VBAP.POSNR AS _CASE_KEY
        ,'Lege Auftragsposition an' AS ACTIVITY_DE
        , 'Create Sales Order Item' AS ACTIVITY_EN
        , CAST(VBAP.ERDAT AS DATE) + CAST(VBAP.ERZET AS TIME) AS EVENTTIME
        , 20 AS _SORTING
        ,VBAP.ERNAM AS USER_NAME
        , USR02.USTYP AS USER_TYPE
        ,VBAP.MANDT AS MANDT
        ,VBAP.VBELN AS VBELN
        ,VBAP.POSNR AS POSNR
    ,V_CHANGES.TCODE AS TRANSACTION_CODE
    ,VBAP.MANDT || VBAP.VBELN || VBAP.POSNR AS _ACTIVITY_KEY
FROM
        _CELONIS_TMP_VBAP_TRANSFORM_DATA AS VBAP
    JOIN VBAK ON
        AND VBAK.MANDT = VBAP.MANDT
        AND VBAK.VBELN = VBAP.VBELN
        AND VBAK.VBTYP ='<%=orderDocSalesOrders%>'
        LEFT JOIN USR02 AS USR02 ON
                VBAP.MANDT = USR02.MANDT AND
                VBAP.ERNAM = USR02.BNAME 
    LEFT JOIN TMP_SO_CDHDR_CDPOS AS V_CHANGES ON
            AND VBAP.MANDT || VBAP.VBELN || VBAP.POSNR = V_CHANGES.TABKEY 

) AS NEW_ACTIVITIES 
 WHERE NOT EXISTS (
                    SELECT 1 FROM _CEL_O2C_VBAP_ACTIVITIES AS OLD_ACTIVITIES
                    WHERE 
                        NEW_ACTIVITIES._ACTIVITY_KEY=OLD_ACTIVITIES._ACTIVITY_KEY
                        AND NEW_ACTIVITIES.ACTIVITY_EN=OLD_ACTIVITIES.ACTIVITY_EN
                    )
;

Step 4: Delete Temporary Tables and move the query directly into the transformation
For the concept of Delta Transformations, temp tables that are used in multiple different transformations (with different trigger tables) are not possible anymore. The idea is that the operations of the temp tables are implemented as a subquery directly into the transformation. The results are therefore generated dynamically during run time, rather than in advance (before the execution of the transformation).
Step 5: Define Dependencies
As the last step, the corresponding dependent tables need to be identified and configured within the Replication Cockpit. Dependent tables are usually all tables that are being inner joined on or that are used in a Exist-statement.
The dependent tables need to be selected in the Transformation Configuration Tab in the Replication Cockpit.

Was this helpful?

Would you like to provide feedback? Just click here to suggest edits.

Celonis Product Documentation

02 - Real Time Transformations for Activities

Warning

Note

Tip

Search results