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.