Transformations
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
"SHIPMENTS"."MANDT"
,"SHIPMENTS"."TKNUM"
,"SHIPMENTS"."TSNUM"
,"SHIPMENTS"."VSART"
,"SHIPMENTS"."BEZEI"
,UPPER("ST_METADATA"."TRANSPORT_MODE") AS "TRANSPORT_MODE"
FROM
(
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"
) SHIPMENTS
LEFT JOIN "SER_APP_SHIPPING_TYPE_METADATA" AS "ST_METADATA" ON 1=1
AND "SHIPMENTS"."VSART" = "ST_METADATA"."SHIPPING_TYPE"
;
-- helper table 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"
;
-- helper table 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
,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(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" ||'-'|| "SHIPPING_TYPES"."VSART" 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" = "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"
,"ARRIVAL_COUNTRY"
,"ARRIVAL_CITY"
,"TRANSPORT_MODE"
,"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"."ARRIVAL_COUNTRY"
,"SHIPMENT_DELIVERY_TMP"."ARRIVAL_CITY"
,"SHIPMENT_DELIVERY_TMP"."TRANSPORT_MODE"
,"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"
,"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";
The SQL script required for this step is also listed in Appendix C.