Skip to main content

Celonis Product Documentation

Appendix C
Create a new transformation with the title “Create Table: Shipment Network (Signal Link)”:
-- 0.0 Preliminary ---
-- need to create a temp table that maps the shipping type codes to actual shipping types --

DROP TABLE IF EXISTS SHIPPING_TYPES;
CREATE TABLE SHIPPING_TYPES AS
    SELECT DISTINCT
        "VTTS"."MANDT"
        ,"VTTS"."TKNUM"
        ,"VTTS"."TSNUM"
        ,COALESCE("VTTS"."VSART","VTTK"."VSART") AS "VSART"
        ,COALESCE("VTTS"."BEZEI","VTTK"."BEZEI") AS "BEZEI"
        ,CASE
            WHEN COALESCE(VTTS.VSART,VTTK.VSART) IN <%=Shipping_Type_Road%> THEN 'ROAD'
            WHEN COALESCE(VTTS.VSART,VTTK.VSART) IN <%=Shipping_Type_Rail%> THEN 'RAIL'
            WHEN COALESCE(VTTS.VSART,VTTK.VSART) IN <%=Shipping_Type_Sea%> THEN 'SEA'
            WHEN COALESCE(VTTS.VSART,VTTK.VSART) IN <%=Shipping_Type_Air%> THEN 'AIR'
            ELSE NULL
        END AS "TRANSPORT_MODE"
    FROM "O2C_VTTS" AS "VTTS"
    LEFT JOIN "O2C_VTSP" AS "VTSP" ON 1=1
        AND "VTTS"."MANDT" = "VTSP"."MANDT"
        AND "VTTS"."TKNUM" = "VTSP"."TKNUM"
        AND "VTTS"."TSNUM" = "VTSP"."TSNUM"
    LEFT JOIN "O2C_VTTP" AS "VTTP" ON 1=1
        AND "VTSP"."MANDT" = "VTTP"."MANDT"
        AND "VTSP"."TKNUM" = "VTTP"."TKNUM"
        AND "VTSP"."TPNUM" = "VTTP"."TPNUM"
    LEFT JOIN "O2C_VTTK" AS "VTTK" ON 1=1
        AND "VTTP"."MANDT" = "VTTK"."MANDT"
        AND "VTTP"."TKNUM" = "VTTK"."TKNUM"; 

-- need to create a temp table that replaces empty shipping stage information by plant/customer data
DROP TABLE IF EXISTS STAGE_PLANT_CUSTOMER;
CREATE TABLE STAGE_PLANT_CUSTOMER AS
    SELECT DISTINCT
        "VTTS"."MANDT"
        ,"VTTS"."TKNUM"
        ,"VTTS"."TSNUM"
        ,CASE
            WHEN "VTTS"."ABLAND1" IS NULL AND "VTTS"."TSRFO" = '0001' THEN "T001W"."LAND1"
            WHEN "VTTS"."ABLAND1" IS NULL AND "VTTS"."TSRFO" <> '0001' THEN "DEPARTURE_CUSTOMER"."LAND1"
            ELSE "VTTS"."ABLAND1"
        END AS "DEPARTURE_COUNTRY"
        ,CASE
            WHEN "VTTS"."ABORT01" IS NULL AND "VTTS"."TSNUM" = '0001' THEN "T001W"."ORT01"
            WHEN "VTTS"."ABORT01" IS NULL AND "VTTS"."TSNUM" <> '0001' THEN "DEPARTURE_CUSTOMER"."ORT01"
            ELSE "VTTS"."ABORT01"
        END AS "DEPARTURE_CITY"
        ,CASE
            WHEN "VTTS"."EDLAND1" IS NULL THEN "ARRIVAL_CUSTOMER"."LAND1"
            ELSE "VTTS"."EDLAND1"
        END AS "ARRIVAL_COUNTRY"
        ,CASE
            WHEN "VTTS"."EDORT01" IS NULL THEN "ARRIVAL_CUSTOMER"."ORT01"
            ELSE "VTTS"."EDORT01"
        END AS "ARRIVAL_CITY"
    FROM "O2C_VTTS" AS "VTTS"
    LEFT JOIN "O2C_VTSP" AS "VTSP" ON 1=1
        AND "VTTS"."MANDT" = "VTSP"."MANDT"
        AND "VTTS"."TKNUM" = "VTSP"."TKNUM"
        AND "VTTS"."TSNUM" = "VTSP"."TSNUM"
    LEFT JOIN "O2C_VTTP" AS "VTTP" ON 1=1
        AND "VTSP"."MANDT" = "VTTP"."MANDT"
        AND "VTSP"."TKNUM" = "VTTP"."TKNUM"
        AND "VTSP"."TPNUM" = "VTTP"."TPNUM"
    LEFT JOIN "O2C_VTTK" AS "VTTK" ON 1=1
        AND "VTTP"."MANDT" = "VTTK"."MANDT"
        AND "VTTP"."TKNUM" = "VTTK"."TKNUM"
    LEFT JOIN "T001W" ON 1=1
        AND "VTTK"."MANDT"="T001W"."MANDT"
        AND "VTTK"."TPLST"="T001W"."WERKS"

 -- Transportation Planning Point connected to Plant Master Data
    LEFT JOIN "O2C_KNA1" AS "DEPARTURE_CUSTOMER" ON 1=1
        AND "DEPARTURE_CUSTOMER"."MANDT" = "VTTS"."MANDT"
        AND "DEPARTURE_CUSTOMER"."KUNNR" = "VTTS"."KUNNA"
    LEFT JOIN "O2C_KNA1" AS "ARRIVAL_CUSTOMER" ON 1=1
        AND "ARRIVAL_CUSTOMER"."MANDT" = "VTTS"."MANDT"
        AND "ARRIVAL_CUSTOMER"."KUNNR" = "VTTS"."KUNNZ"
;

-- need to create a temp table that replaces empty shipping stage--
DROP TABLE IF EXISTS ADDRESS_BACKUP;
CREATE TABLE ADDRESS_BACKUP AS
        SELECT
            "VTTS"."MANDT"
            ,"VTTS"."TKNUM"
            ,"VTTS"."TSNUM"
            ,LAG("VTTS"."EDLAND1", 1, NULL) OVER
                (PARTITION BY "VTTS"."MANDT", "VTTS"."TKNUM"
                ORDER BY "VTTS"."TSRFO")
            AS "DEPARTURE_COUNTRY"
            ,LAG("VTTS"."EDORT01", 1, NULL) OVER
                (PARTITION BY "VTTS"."MANDT", "VTTS"."TKNUM"
                ORDER BY "VTTS"."TSRFO")
            AS "DEPARTURE_CITY"
            ,LEAD("VTTS"."ABLAND1", 1, NULL) OVER
                (PARTITION BY "VTTS"."MANDT", "VTTS"."TKNUM"
                ORDER BY "VTTS"."TSRFO")
            AS "ARRIVAL_COUNTRY"
            ,LEAD("VTTS"."ABORT01", 1, NULL) OVER
                (PARTITION BY "VTTS"."MANDT", "VTTS"."TKNUM"
                ORDER BY "VTTS"."TSRFO")
            AS "ARRIVAL_CITY"
        FROM "O2C_VTTS" AS "VTTS"
;

--- 1.0 create temporary table with all important columns ---
DROP TABLE IF EXISTS SHIPMENT_DELIVERY_TMP;
CREATE TABLE SHIPMENT_DELIVERY_TMP AS
    SELECT DISTINCT
        "VTTS"."MANDT"
        ,"VTTS"."TKNUM" -- Shipment Number
        ,"VTTS"."TSNUM" -- Shipment Stage Number
        ,"VTTS"."TSRFO" -- Stage of transport sequence
        ,"VTTP"."TPNUM" -- Shipment Item
        ,"LIPS"."VBELN" AS "LIPS_VBELN" -- Delivery Number
        ,"LIPS"."POSNR" AS "LIPS_POSNR" -- Delivery Item
        ,"VBAP"."VBELN" AS "VBAP_VBELN" -- Sales Order Number
        ,"VBAP"."POSNR" AS "VBAP_POSNR" -- Sales Order Item
        ,"LIPS"."BRGEW" -- Gross Weight
        ,"LIPS"."NTGEW" -- Net Wight
        ,"LIPS"."GEWEI" -- Weight Unit
        ,"LIPS"."VOLUM" -- Volume
        ,"LIPS"."VOLEH" -- Volume Unit
        ,COALESCE("STAGE_PLANT_CUSTOMER"."DEPARTURE_COUNTRY", "ADDRESS_BACKUP"."DEPARTURE_COUNTRY")  AS "DEPARTURE_COUNTRY" -- Departure Country
        ,COALESCE("STAGE_PLANT_CUSTOMER"."DEPARTURE_CITY", "ADDRESS_BACKUP"."DEPARTURE_CITY")  AS "DEPARTURE_CITY" -- Departure City
        ,COALESCE("STAGE_PLANT_CUSTOMER"."ARRIVAL_COUNTRY", "ADDRESS_BACKUP"."ARRIVAL_COUNTRY")  AS "ARRIVAL_COUNTRY" -- Arrival Country
        ,COALESCE("STAGE_PLANT_CUSTOMER"."ARRIVAL_CITY", "ADDRESS_BACKUP"."ARRIVAL_CITY")  AS "ARRIVAL_CITY" -- Arrival City
        ,"SHIPPING_TYPES"."VSART" -- Shipping Type
        ,"SHIPPING_TYPES"."BEZEI" -- Shipping Type Text
        ,"SHIPPING_TYPES"."TRANSPORT_MODE" -- Transport Mode
        ,"VTTS"."DATEN" AS "ACTUAL_ARRIVAL_DATE" -- Stage: Actual Date for End of Shipment
        ,"VTTS"."DATBG" AS "CURRENT_DEPARTURE_DATE" --  Stage: Current date for start of shipment
        ,YEAR("VTTS"."ERDAT") AS "CREATION_YEAR"
        ,REPLACE(UPPER("VTTS"."ABLAND1" ||'-'||"VTTS"."ABORT01"||'-'||"VTTS"."EDLAND1"||'-'||"VTTS"."EDORT01"),' ','_') AS "UNIQUE_STAGE"
        ,REPLACE(UPPER("VTTS"."ABLAND1" ||'-'||"VTTS"."ABORT01"||'-'||"VTTS"."EDLAND1"||'-'||"VTTS"."EDORT01"),' ','_')||'-'||"SHIPPING_TYPES"."TRANSPORT_MODE" AS "UNIQUE_STAGE_IDENTIFIER"
    FROM
        "O2C_VBFA_V" AS "VBFA"
    JOIN "VBFA" AS "VBFA_SHIP" ON 1=1
        AND "VBFA_SHIP"."MANDT" = "VBFA"."MANDT"
        AND "VBFA_SHIP"."VBELV" = "VBFA"."VBELN"
        AND "VBFA_SHIP"."VBTYP_N" = '8'
    JOIN "O2C_VTTP" AS "VTTP" ON 1=1
        AND "VBFA_SHIP"."MANDT" = "VTTP"."MANDT"
        AND "VBFA_SHIP"."VBELN" = "VTTP"."TKNUM"
        AND RIGHT("VBFA_SHIP"."POSNN",4) = "VTTP"."TPNUM"
    JOIN "O2C_VTTK" AS "VTTK" ON 1=1
        AND "VTTK"."MANDT" = "VTTP"."MANDT"
        AND "VTTK"."TKNUM" = "VTTP"."TKNUM"

    -- JOIN "O2C_VTSP" AS "VTSP" ON 1=1
    --     AND "VTSP"."MANDT" = "VTTP"."MANDT"
    --     AND "VTSP"."TKNUM" = "VTTP"."TKNUM"
    --     AND "VTSP"."TPNUM" = "VTTP"."TPNUM"
    JOIN "O2C_VTTS" AS "VTTS" ON 1=1
        AND "VTTS"."MANDT" = "VTTP"."MANDT"
        AND "VTTS"."TKNUM" = "VTTP"."TKNUM"
    --    AND "VTTS"."TSNUM" = "VTSP"."TSNUM"
    JOIN "O2C_LIPS" AS "LIPS" ON 1=1
        AND "VBFA"."MANDT" = "LIPS"."MANDT"
        AND "VBFA"."VBELN" = "LIPS"."VBELN"
        AND "VBFA"."POSNN" = "LIPS"."POSNR"
    JOIN "O2C_LIKP" AS LIKP ON 1=1
        AND "LIPS"."MANDT" = "LIKP"."MANDT"
        AND "LIPS"."VBELN" = "LIKP"."VBELN"
        AND "LIPS"."TS_ERDAT" IS NOT NULL
      JOIN "O2C_VBAP" AS "VBAP" ON 1=1
        AND "VBAP"."MANDT" = "VBFA"."MANDT"
        AND "VBAP"."VBELN" = "VBFA"."VBELV"
        AND "VBAP"."POSNR" = "VBFA"."POSNV"
    JOIN "O2C_VBAK_VBAP" AS "VBAK" ON 1=1
        AND "VBAP"."MANDT" = "VBAK"."MANDT"
        AND "VBAP"."VBELN" = "VBAK"."VBELN"
    LEFT JOIN "SHIPPING_TYPES" ON 1=1
        AND "VTTS"."MANDT" = "SHIPPING_TYPES"."MANDT"
        AND "VTTS"."TKNUM" = "SHIPPING_TYPES"."TKNUM"
        AND "VTTS"."TSNUM" = "SHIPPING_TYPES"."TSNUM"
    LEFT JOIN "STAGE_PLANT_CUSTOMER" ON 1=1
        AND "VTTS"."MANDT" = "STAGE_PLANT_CUSTOMER"."MANDT"
        AND "VTTS"."TKNUM" = "STAGE_PLANT_CUSTOMER"."TKNUM"
        AND "VTTS"."TSNUM" = "STAGE_PLANT_CUSTOMER"."TSNUM"
    LEFT JOIN "ADDRESS_BACKUP" ON 1=1
        AND "VTTS"."MANDT" = "ADDRESS_BACKUP"."MANDT"
        AND "VTTS"."TKNUM" = "ADDRESS_BACKUP"."TKNUM"
        AND "VTTS"."TSNUM" = "ADDRESS_BACKUP"."TSNUM"
    ;  

--- 1.1 create a table with all unique stages for the emission calculation ---
DROP TABLE IF EXISTS UNIQUE_SHIPPING_STAGES;
CREATE TABLE UNIQUE_SHIPPING_STAGES AS
SELECT
    "UNIQUE_STAGE_IDENTIFIER"
    ,"UNIQUE_STAGE"
    ,"DEPARTURE_COUNTRY"
    ,"DEPARTURE_CITY"
    ,"ARRIVAL_COUNTRY"
    ,"ARRIVAL_CITY"
    ,"TRANSPORT_MODE"
FROM (
    SELECT
          "SHIPMENT_DELIVERY_TMP"."UNIQUE_STAGE_IDENTIFIER"
        ,"SHIPMENT_DELIVERY_TMP"."UNIQUE_STAGE"
        ,"SHIPMENT_DELIVERY_TMP"."DEPARTURE_COUNTRY"
        ,"SHIPMENT_DELIVERY_TMP"."DEPARTURE_CITY"
        ,"SHIPMENT_DELIVERY_TMP"."ARRIVAL_COUNTRY"
        ,"SHIPMENT_DELIVERY_TMP"."ARRIVAL_CITY"
        ,"SHIPMENT_DELIVERY_TMP"."TRANSPORT_MODE"
        ,ROW_NUMBER() OVER (PARTITION BY "SHIPMENT_DELIVERY_TMP"."UNIQUE_STAGE_IDENTIFIER") AS ROW_NUM
    FROM
        "SHIPMENT_DELIVERY_TMP"
    WHERE
        "SHIPMENT_DELIVERY_TMP"."UNIQUE_STAGE" IS NOT NULL) AS "UNIQUE_SHIPPING_STAGES"
WHERE ROW_NUM = 1
;

---- 2.0 DEPARTURE NODES ----- 
-- Create a table that stores information about the departure nodes -- 

DROP TABLE IF EXISTS DEPARTURE_NODES_TMP;
CREATE TABLE DEPARTURE_NODES_TMP AS
    SELECT DISTINCT
        "SHIPMENT_DELIVERY_TMP"."MANDT"
        ,"SHIPMENT_DELIVERY_TMP"."TKNUM" -- Shipment Number
        ,"SHIPMENT_DELIVERY_TMP"."TSNUM" -- Shipment Stage Number
        ,"SHIPMENT_DELIVERY_TMP"."TPNUM" -- Shipment Item
        ,"SHIPMENT_DELIVERY_TMP"."TSRFO" -- Shipment Sequence
        ,"SHIPMENT_DELIVERY_TMP"."LIPS_VBELN" -- Delivery Number
        ,"SHIPMENT_DELIVERY_TMP"."LIPS_POSNR" -- Delivery Item
        ,"SHIPMENT_DELIVERY_TMP"."VBAP_VBELN" -- Sales Order Number
        ,"SHIPMENT_DELIVERY_TMP"."VBAP_POSNR" -- Sales Order Item
        ,"SHIPMENT_DELIVERY_TMP"."DEPARTURE_COUNTRY" AS "COUNTRY" --  Departure Country
        ,"SHIPMENT_DELIVERY_TMP"."DEPARTURE_CITY" AS "CITY" -- Departure City
        ,'Stage: Current date for start of shipment' AS "ACTIVITY_EN"
        ,"SHIPMENT_DELIVERY_TMP"."CREATION_DATE" AS EVENTTIME
        ,"SHIPMENT_DELIVERY_TMP"."LIPS_VBELN"||"SHIPMENT_DELIVERY_TMP"."DEPARTURE_COUNTRY"||"SHIPMENT_DELIVERY_TMP"."DEPARTURE_CITY" AS _CASE_KEY
        ,"SHIPMENT_DELIVERY_TMP"."TSRFO"||"SHIPMENT_DELIVERY_TMP"."TKNUM" ||"SHIPMENT_DELIVERY_TMP"."TSNUM"||"SHIPMENT_DELIVERY_TMP"."TPNUM" AS SIGNAL_OUT
        ,NULL AS SIGNAL_IN
        ,"SHIPMENT_DELIVERY_TMP"."UNIQUE_STAGE_IDENTIFIER" AS "UNIQUE_STAGE_IDENTIFIER_OUT"
        ,NULL AS "UNIQUE_STAGE_IDENTIFIER_IN"
        ,"SHIPMENT_DELIVERY_TMP"."BRGEW" AS "GROSS_WEIGHT_OUT"
        ,0.0 AS "GROSS_WEIGHT_IN"
        ,"SHIPMENT_DELIVERY_TMP"."NTGEW" AS "NET_WEIGHT_OUT"
        ,0.0 AS "NET_WEIGHT_IN"
        ,"SHIPMENT_DELIVERY_TMP"."GEWEI" AS "WEIGHT_UNIT_OUT"
        , NULL AS "WEIGHT_UNIT_IN"
        ,"SHIPMENT_DELIVERY_TMP"."VOLUM" AS "VOLUME_OUT"
        ,0.0 AS "VOLUME_IN"
        ,"SHIPMENT_DELIVERY_TMP"."VOLEH" AS "VOLUM_UNIT_OUT"
        , NULL AS "VOLUM_UNIT_IN"
        ,"SHIPMENT_DELIVERY_TMP"."VSART" AS "SHIPPING_TYPE_OUT"
        , NULL AS "SHIPPING_TYPE_IN"
        ,"SHIPMENT_DELIVERY_TMP"."BEZEI" AS "SHIPPING_TYPE_TEXT_OUT"
        , NULL AS "SHIPPING_TYPE_TEXT_IN"
        ,"SHIPMENT_DELIVERY_TMP"."TRANSPORT_MODE" AS "TRANSPORT_MODE_OUT"
        , NULL AS "TRANSPORT_MODE_IN"
    FROM "SHIPMENT_DELIVERY_TMP"
; 

---- 3.0 ARRIVAL NODES -----
-- Create a table that stores information about the Arrival nodes --
 
DROP TABLE IF EXISTS ARRIVAL_NODES_TMP;
CREATE TABLE ARRIVAL_NODES_TMP AS
    SELECT DISTINCT
        "SHIPMENT_DELIVERY_TMP"."MANDT"
        ,"SHIPMENT_DELIVERY_TMP"."TKNUM" -- Shipment Number
        ,"SHIPMENT_DELIVERY_TMP"."TSNUM" -- Shipment Stage Number
        ,"SHIPMENT_DELIVERY_TMP"."TPNUM" -- Shipment Item
        ,"SHIPMENT_DELIVERY_TMP"."TSRFO" -- Shipment Sequence
        ,"SHIPMENT_DELIVERY_TMP"."LIPS_VBELN" -- Delivery Number
        ,"SHIPMENT_DELIVERY_TMP"."LIPS_POSNR" -- Delivery Item
        ,"SHIPMENT_DELIVERY_TMP"."VBAP_VBELN" -- Sales Order Number
        ,"SHIPMENT_DELIVERY_TMP"."VBAP_POSNR" -- Sales Order Item
        ,"SHIPMENT_DELIVERY_TMP"."ARRIVAL_COUNTRY" AS "COUNTRY" --  Arrival Country
        ,"SHIPMENT_DELIVERY_TMP"."ARRIVAL_CITY" AS "CITY" -- Arrival City
        ,'Stage: Actual Date for End of Shipment' AS "ACTIVITY_EN"
        ,"SHIPMENT_DELIVERY_TMP"."CREATION_DATE" AS EVENTTIME
        ,"SHIPMENT_DELIVERY_TMP"."LIPS_VBELN"||"SHIPMENT_DELIVERY_TMP"."ARRIVAL_COUNTRY"||"SHIPMENT_DELIVERY_TMP"."ARRIVAL_CITY" AS _CASE_KEY
        ,NULL AS SIGNAL_OUT
        ,"SHIPMENT_DELIVERY_TMP"."TSRFO"||"SHIPMENT_DELIVERY_TMP"."TKNUM" ||"SHIPMENT_DELIVERY_TMP"."TSNUM"||"SHIPMENT_DELIVERY_TMP"."TPNUM" AS SIGNAL_IN
        ,NULL AS "UNIQUE_STAGE_IDENTIFIER_OUT"
        ,"SHIPMENT_DELIVERY_TMP"."UNIQUE_STAGE_IDENTIFIER" AS "UNIQUE_STAGE_IDENTIFIER_IN"
        ,0.0 AS "GROSS_WEIGHT_OUT"
        ,"SHIPMENT_DELIVERY_TMP"."BRGEW" AS "GROSS_WEIGHT_IN"
        ,0.0 AS "NET_WEIGHT_OUT"
        ,"SHIPMENT_DELIVERY_TMP"."NTGEW" AS "NET_WEIGHT_IN"
        ,NULL AS "WEIGHT_UNIT_OUT"
        ,"SHIPMENT_DELIVERY_TMP"."GEWEI" AS "WEIGHT_UNIT_IN"
        ,0.0 AS "VOLUME_OUT"
        ,"SHIPMENT_DELIVERY_TMP"."VOLUM" AS "VOLUME_IN"
        ,NULL AS "VOLUM_UNIT_OUT"
        ,"SHIPMENT_DELIVERY_TMP"."VOLEH" AS "VOLUM_UNIT_IN"
        ,NULL AS "SHIPPING_TYPE_OUT"
        ,"SHIPMENT_DELIVERY_TMP"."VSART" AS "SHIPPING_TYPE_IN"
        ,NULL AS "SHIPPING_TYPE_TEXT_OUT"
        ,"SHIPMENT_DELIVERY_TMP"."BEZEI" AS "SHIPPING_TYPE_TEXT_IN"
        ,NULL AS "TRANSPORT_MODE_OUT"
        ,"SHIPMENT_DELIVERY_TMP"."TRANSPORT_MODE" AS "TRANSPORT_MODE_IN"
    FROM "SHIPMENT_DELIVERY_TMP"
;   

---- 4.0 UNION -----
-- Create a table that unions the departures and arrivals -- 
DROP TABLE IF EXISTS SHIPMENT_NETWORK;
CREATE TABLE SHIPMENT_NETWORK AS
    SELECT DISTINCT
        *
    FROM DEPARTURE_NODES_TMP
 
    UNION ALL 

    SELECT DISTINCT
        *
    FROM ARRIVAL_NODES_TMP
; 

---- 5.0 Clean up -----
DROP TABLE IF EXISTS DEPARTURE_NODES_TMP;
DROP TABLE IF EXISTS ARRIVAL_NODES_TMP;
DROP TABLE IF EXISTS "SHIPMENT_DELIVERY_TMP";