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"
        WHERE 1=1
            AND "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" ON 1=1
        AND "VTTK"."MANDT"="T173T"."MANDT"
        AND "VTTK"."VSART"="T173T"."VSART"
        AND "T173T"."SPRAS"='E'
     LEFT JOIN "T001W" ON 1=1
        AND "VTTK"."MANDT"="T001W"."MANDT"
        AND "VTTK"."TPLST"="T001W"."WERKS"
     WHERE EXISTS(
        SELECT *
        FROM "O2C_VTTP" AS "VTTP"
        WHERE 1=1
            AND 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"
        WHERE 1=1
            AND 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 ON 1=1
        AND T173T.MANDT = VTTS.MANDT
        AND T173T.VSART = VTTS.VSART
        AND T173T.SPRAS = 'E'
    WHERE EXISTS(
        SELECT *
        FROM "O2C_VTSP" AS "VTSP"
        WHERE 1=1
            AND 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!