Transformations - Shipment Tables
Create and execute all shipment data transformations
After extracting all the necessary information, go to the SAP O2C - Full Transformation
data job and create the transformations for the shipping data.
Important
A common problem with long running transformations and slow data model loads is due to missing statistics in Vertica. This issue can be resolved by adding the Vertica ANALYZE_STATISTICS statement directly in the SQL. For more information, refer to Vertica Transformations Optimization.
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" "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" AND "VTTK"."MANDT"="T173T"."MANDT" AND "VTTK"."VSART"="T173T"."VSART" AND "T173T"."SPRAS"='E' LEFT JOIN "T001W" AND "VTTK"."MANDT"="T001W"."MANDT" AND "VTTK"."TPLST"="T001W"."WERKS" WHERE EXISTS( SELECT * FROM "O2C_VTTP" AS "VTTP" 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" 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 AND T173T.MANDT = VTTS.MANDT AND T173T.VSART = VTTS.VSART AND T173T.SPRAS = 'E' WHERE EXISTS( SELECT * FROM "O2C_VTSP" AS "VTSP" VTSP.MANDT = VTTS.MANDT AND VTSP.TKNUM = VTTS.TKNUM AND VTSP.TSNUM = VTTS.TSNUM ) ) ;
The SQL scripts required for this step are also listed in Appendix B. Run the data job for the new transformations listed below.
Important
The order of the transformations is important!