Skip to main content

Celonis Product Documentation

Transformations - Shipment Tables
Create and execute all shipment data transformations

After extracting all the necessary information, go to the SAP O2C - Full Transformation data job and create the transformations for the shipping data.

Important

A common problem with long running transformations and slow data model loads is due to missing statistics in Vertica. This issue can be resolved by adding the Vertica ANALYZE_STATISTICS statement directly in the SQL. For more information, refer to Vertica Transformations Optimization.

  • Create Table: O2C_VTTP

    DROP TABLE IF EXISTS "O2C_VTTP";
    
    
    -- Query 2
    CREATE TABLE "O2C_VTTP" AS (
        SELECT
            "VTTP"."MANDT"
            ,"VTTP"."TKNUM"
            ,"VTTP"."TPNUM"
            ,"VTTP"."VBELN"
            ,"VTTP"."ERDAT"
            ,CAST("VTTP"."ERDAT" AS DATE) AS "TS_ERDAT"
            ,CAST("VTTP"."ERZET" AS DATE) AS "TS_ERZET"
        FROM "VTTP" AS "VTTP"
        WHERE EXISTS(
            SELECT *
            FROM "O2C_VBFA_V" AS "VBFA"
     "VBFA"."MANDT" = "VTTP"."MANDT"
                AND "VBFA"."VBELN" = "VTTP"."VBELN"
        )
    );
    
  • Create Table: O2C_VTTK

    -- Query 1
    DROP TABLE IF EXISTS "O2C_VTTK";
    -- Query 2
    CREATE TABLE "O2C_VTTK" AS (
    SELECT
    "VTTK".*
    ,"T001W"."NAME1"
    ,"T001W"."LAND1"
    ,"T001W"."PSTLZ"
    ,"T001W"."ORT01"
    ,"T173T"."BEZEI"
    ,CAST("VTTK"."ERDAT" AS DATE) AS "TS_ERDAT"
    ,CAST("VTTK"."ERZET" AS DATE) AS "TS_ERZET"
    FROM "VTTK"
         LEFT JOIN "T173T"
            AND "VTTK"."MANDT"="T173T"."MANDT"
            AND "VTTK"."VSART"="T173T"."VSART"
            AND "T173T"."SPRAS"='E'
         LEFT JOIN "T001W"
            AND "VTTK"."MANDT"="T001W"."MANDT"
            AND "VTTK"."TPLST"="T001W"."WERKS"
         WHERE EXISTS(
            SELECT *
            FROM "O2C_VTTP" AS "VTTP"
          VTTP.MANDT = VTTK.MANDT
                AND VTTP.TKNUM = VTTK.TKNUM
         )
    );
    
  • Create Table: O2C_VTSP

    -- Query No: 1
    DROP TABLE IF EXISTS "O2C_VTSP";
    -- Query No: 2
    CREATE TABLE "O2C_VTSP" AS (
        SELECT
            "VTSP".*
        FROM "VTSP" AS "VTSP"
        WHERE EXISTS(
            SELECT *
            FROM "O2C_VTTP" AS "VTTP"
    VTSP.TKNUM = VTTP.TKNUM
                AND VTSP.TPNUM = VTTP.TPNUM
        )
     )
    ;
    
  • Create Table: O2C_VTTS

    -- Query No: 1
    DROP TABLE IF EXISTS "O2C_VTTS";
    -- Query No: 2
    CREATE TABLE "O2C_VTTS" AS (
        SELECT
            "VTTS".*
            ,"T173T".BEZEI
            ,CAST("VTTS"."ERDAT" AS DATE) AS "TS_ERDAT"
            ,CAST("VTTS"."ERZET" AS DATE) AS "TS_ERZET"
        FROM "VTTS" AS "VTTS"
        LEFT JOIN T173T
            AND T173T.MANDT = VTTS.MANDT
            AND T173T.VSART = VTTS.VSART
            AND T173T.SPRAS = 'E'
        WHERE EXISTS(
            SELECT *
            FROM "O2C_VTSP" AS "VTSP"
    VTSP.MANDT = VTTS.MANDT
                AND VTSP.TKNUM = VTTS.TKNUM
                AND VTSP.TSNUM = VTTS.TSNUM
        )
     )
    ;
    

The SQL scripts required for this step are also listed in Appendix B. Run the data job for the new transformations listed below.

image23.png

Important

The order of the transformations is important!