Skip to main content

Celonis Product Documentation

Transformations - Shipment Network Table
Create data transformation for the network table (Signal Link)

Go back to the “SAP O2C - Full Transformation” data job and create a new transformation called “Create Table: Shipment Network (Signal Link)”. Afterwards, perform the following intermediate steps:

  • Insert the following SQL script

 

-- 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"

   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"."DEPARTURE_COUNTRY", "ADDRESS_BACKUP"."DEPARTURE_COUNTRY") ||'-'|| COALESCE("STAGE_PLANT_CUSTOMER"."DEPARTURE_CITY", "ADDRESS_BACKUP"."DEPARTURE_CITY") AS "DEPARTURE_QUERY" -- Departure Query

       ,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

       ,COALESCE("STAGE_PLANT_CUSTOMER"."ARRIVAL_COUNTRY", "ADDRESS_BACKUP"."ARRIVAL_COUNTRY") ||'-'|| COALESCE("STAGE_PLANT_CUSTOMER"."ARRIVAL_CITY", "ADDRESS_BACKUP"."ARRIVAL_CITY") AS "ARRIVAL_QUERY" -- Arrival Query

       ,"SHIPPING_TYPES"."VSART" -- Shipping Type

       ,"SHIPPING_TYPES"."BEZEI" -- Shipping Type Text

       ,"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

       ,COALESCE("VTTS"."TS_ERDAT", "VTTP"."TS_ERDAT", "VTTK"."TS_ERDAT") AS "CREATION_DATE"

       ,YEAR(COALESCE("VTTS"."TS_ERDAT", "VTTP"."TS_ERDAT", "VTTK"."TS_ERDAT")) AS "CREATION_YEAR"

       ,REPLACE(COALESCE("STAGE_PLANT_CUSTOMER"."DEPARTURE_COUNTRY", "ADDRESS_BACKUP"."DEPARTURE_COUNTRY") ||'-'|| UPPER(COALESCE("STAGE_PLANT_CUSTOMER"."DEPARTURE_CITY", "ADDRESS_BACKUP"."DEPARTURE_CITY"))||'-'||COALESCE("STAGE_PLANT_CUSTOMER"."ARRIVAL_COUNTRY", "ADDRESS_BACKUP"."ARRIVAL_COUNTRY") ||'-'|| UPPER(COALESCE("STAGE_PLANT_CUSTOMER"."ARRIVAL_CITY", "ADDRESS_BACKUP"."ARRIVAL_CITY")),' ','_') AS "UNIQUE_STAGE" -- Departure Query - Arrival Query

       ,REPLACE(COALESCE("STAGE_PLANT_CUSTOMER"."DEPARTURE_COUNTRY", "ADDRESS_BACKUP"."DEPARTURE_COUNTRY") ||'-'|| UPPER(COALESCE("STAGE_PLANT_CUSTOMER"."DEPARTURE_CITY", "ADDRESS_BACKUP"."DEPARTURE_CITY"))||'-'||COALESCE("STAGE_PLANT_CUSTOMER"."ARRIVAL_COUNTRY", "ADDRESS_BACKUP"."ARRIVAL_COUNTRY") ||'-'|| UPPER(COALESCE("STAGE_PLANT_CUSTOMER"."ARRIVAL_CITY", "ADDRESS_BACKUP"."ARRIVAL_CITY")),' ','_')||'-'|| "SHIPPING_TYPES"."VSART" AS "UNIQUE_STAGE_IDENTIFIER" -- Departure Query - Arrival Query - Shipping Type

   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" = "VTSP"."MANDT"

       AND "VTTS"."TKNUM" = "VTSP"."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"

   ,"DEPARTURE_QUERY"

   ,"ARRIVAL_COUNTRY"

   ,"ARRIVAL_CITY"

   ,"ARRIVAL_QUERY"

   ,"SHIPPING_TYPE"

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"."DEPARTURE_QUERY"

       ,"SHIPMENT_DELIVERY_TMP"."ARRIVAL_COUNTRY"

       ,"SHIPMENT_DELIVERY_TMP"."ARRIVAL_CITY"

       ,"SHIPMENT_DELIVERY_TMP"."ARRIVAL_QUERY"

       ,"SHIPMENT_DELIVERY_TMP"."VSART" AS "SHIPPING_TYPE"

       ,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"

   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"

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

DROP TABLE IF EXISTS SHIPMENT_DELIVERY_TMP;

DROP TABLE IF EXISTS ADDRESS_BACKUP;

DROP TABLE IF EXISTS DEPARTURE_NODES_TMP;

DROP TABLE IF EXISTS ARRIVAL_NODES_TMP;

DROP TABLE IF EXISTS "SHIPMENT_DELIVERY_TMP";

Important

The two new fields DEPARTURE_QUERY and ARRIVAL_QUERY will be used to specify the route for the emission calculation. It is possible to add additional address information like postal codes or street names to the queries if these information are available in your data. But make sure, that any change on this query fields also needs to be reflected in the UNIQUE_STAGE_IDENTIFIER column as this Identifier needs to be the combination of DEPARTURE_QUERY-ARRIVAL_QUERY-SHIPPING_TYPE