Skip to main content

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

    (warning)

    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

    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

    Initial Billing Block

    O2C

    VBAP

    Create Sales Order

    O2C

    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

    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" ON 1=1
                    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" ON 1=1
                    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" ON 1=1
            AND "BKPF_INV"."MANDT" = "VBRK"."MANDT" 
            AND "BKPF_INV"."AWKEY" = "VBRK"."VBELN" 
            AND "BKPF_INV"."AWTYP" = 'VBRK'
        INNER JOIN "VBRP" AS "VBRP" ON 1=1
            AND "VBRP"."MANDT" = "VBRK"."MANDT"
            AND "VBRP"."VBELN" = "VBRK"."VBELN" 
            INNER JOIN      "VBFA" ON 1=1
                    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 ON 1=1
                                    AND "V_ORDERS"."MANDT" = "VBFA"."MANDT"
                                    AND "V_ORDERS"."VBELN" = "VBFA"."VBELV"
                                    AND "V_ORDERS"."POSNR" = "VBFA"."POSNV"
        LEFT JOIN "DD07T" AS "DD07T_EN" ON 1=1
                    AND "DD07T_EN"."DOMNAME" = 'VBTYP'
                    AND "DD07T_EN"."DDLANGUAGE" = 'E'
                    AND "DD07T_EN"."DOMVALUE_L" = "VBRK"."VBTYP"
            LEFT JOIN "DD07T" AS "DD07T_DE" ON 1=1
                    AND "DD07T_DE"."DOMNAME" = 'VBTYP'
                    AND "DD07T_DE"."DDLANGUAGE" = 'D'
                    AND "DD07T_DE"."DOMVALUE_L" = "VBRK"."VBTYP"
            LEFT JOIN "USR02" AS "USR02" ON 1=1
                    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 1=1 
            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 1=1
                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.