Celonis Product Documentation

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
    )
 )
;