Prerequisites for the case-centric Customer Consignment Stock app
Before you install the case-centric version of the Customer Consignment Stock app, you’ll need to complete these prerequisite steps:
Install and configure a Data Connection between Celonis and your source system.
If you’ve already set up a Data Connection to this source system, you can reuse it. If not, you'll need to install the Celonis components in your source system, set up the extractor server, and configure a Data Connection in the Celonis platform. Data Connections has the instructions to do this.
Set up extraction jobs to extract the required tables from SAP ECC. The reference list is in Required tables and columns for the case-centric Customer Consignment Stock app.
Create a transformation for each of the tables for the Data Model as follows:
Go to Data Integration and select the Data Jobs area.
Click Add Data Job to create a new data job.
Add each of the transformations that we’ve provided in Data Model tables for the case-centric Customer Consignment Stock app.
Run the data job to create the tables.
Create a new Data Model using the tables, as described in Data Model for the case-centric Customer Consignment Stock app.
Load the Data Model with your data.
When the Data Connection is in place and you’ve extracted the tables, created the transformations, and created and loaded the Data Model, follow the steps in Installing the case-centric Customer Consignment Stock app.
If you need to get help with any of the app’s prerequisites or setup steps, Support tells you how.
Required tables and columns for the case-centric Customer Consignment Stock app
Here’s the reference list of the SAP ECC tables that you need to extract data from for the Customer Consignment Stock app:
MARC, MARA, MAKT, MARM, MBEW, MSEG, MKPF, MSKU, MSKUH, T001, T001W, T001K, T003T, T006, T006D, TCURR, TCURF, TCURX, T156, T156T, TVAKT, TVKOT, TVAPT, TVAGT, USR02, KNA1, VBAP, VBAK, VBEP, VBAK, VBRP
Data Model tables for the case-centric Customer Consignment Stock app
Add the transformations in this section to your data job to create each of the tables for the Data Model. In the data job, click Add Transformation, give it a suitable name, and paste in one of the transformations. Each transformation creates one of the tables or a helper table associated with it.
Here’s the full list of Data Model tables to create:
IM_MARC (and two helper tables for currency conversion and quantity conversion)
When you assemble the Data Model, you'll need to create aliases for the IM_MARC, CS_VBAK, CS_VBAP, CS_VBEP, and CS_VBRP tables to rename them back to the standard SAP ECC table names.
IM_MARC (material master data) table
The SAP ECC tables that supply data for these transformations are MARC, MARA, MAKT, MARM, T001W, T001K, T001, T006, T006D, MBEW, TCURR, TCURF, and TCURX.
The three transformations here create the MARC table and two helper tables for currency conversion and quantity conversion. You can put them all under the same transformation or create three separate transformations.
If your data pool already contains the table IM_MARC from the standard Celonis data model for Inventory Management, you can use that table in place of the MARC table.
If your data pool already contains the tables TCURF_CC and TCURR_CC from the data model for another app, you can use those in place of the currency conversion table.
Transformation 1: currency conversion table
-- Query No: 1
DROP TABLE IF EXISTS "TCURF_CC";
-- Query No: 2
DROP TABLE IF EXISTS "TCURF_TMP";
-- Query No: 3
CREATE TABLE "TCURF_TMP" AS (
SELECT
*
,CAST(NULL AS TIMESTAMP) AS "VALID_START"
,CAST(NULL AS TIMESTAMP) AS "VALID_END"
,DENSE_RANK() OVER (ORDER BY
TCURF."MANDT"
,TCURF."KURST"
,TCURF."FCURR"
,TCURF."TCURR"
) AS "TCURF_KEY"
,ROW_NUMBER() OVER (PARTITION BY
TCURF."MANDT"
,TCURF."KURST"
,TCURF."FCURR"
,TCURF."TCURR"
ORDER BY (99999999-CAST(TCURF."GDATU" AS INT)) ASC) AS "TCURF_ROWNR"
FROM
"TCURF" as TCURF
WHERE 1=1
AND (99999999-cast(TCURF."GDATU" as int)) >= 18000000
AND (99999999-cast(TCURF."GDATU" as int)) <= 20990000);
SELECT ANALYZE_STATISTICS('TCURF_TMP');
-- Query No: 4
CREATE TABLE "TCURF_CC" AS (
SELECT
*
FROM
"TCURF_TMP");
SELECT ANALYZE_STATISTICS('TCURF_CC');
-- Query No: 5
UPDATE
"TCURF_CC"
SET
"VALID_START" = CAST(LPAD(CAST((99999999-CAST("GDATU" AS INT)) AS VARCHAR(10)),8,'0') || ' ' || '00:00:00' AS TIMESTAMP);
-- Query No: 6
UPDATE
"TCURF_CC" AS TCURF_CC
SET
"VALID_END" = CAST(LPAD(CAST((99999999-CAST(TCURF_TMP."GDATU" AS INT)) AS VARCHAR(10)),8,'0') || ' ' || '00:00:00' AS TIMESTAMP)
FROM
"TCURF_TMP" AS TCURF_TMP
WHERE 1=1
AND TCURF_CC."TCURF_KEY" = TCURF_TMP."TCURF_KEY"
AND TCURF_CC."TCURF_ROWNR" + 1 = TCURF_TMP."TCURF_ROWNR";
-- Query No: 7
UPDATE
"TCURF_CC"
SET
"VALID_END" = '2099-01-01'
WHERE
"VALID_END" IS NULL;
-- Query No: 8
DROP TABLE IF EXISTS "TCURF_TMP";
--Add statistics for created temporary join table
SELECT ANALYZE_STATISTICS('TCURF_CC');
-------------------------------------
-------------------------------------
-- Statement: Database Preparation: Create Table: TCURR_CC
-- Query No: 1
DROP TABLE IF EXISTS "TCURR_CC";
-- Query No: 2
DROP TABLE IF EXISTS "TCURR_TMP";
-- Query No: 3
CREATE TABLE "TCURR_TMP" AS (
SELECT
TCURR."MANDT"
,TCURR."KURST"
,TCURR."FCURR"
,TCURR."TCURR"
,CAST (TCURR."GDATU" AS VARCHAR) AS GDATU
,TCURR."UKURS"
,CAST (TCURR."FFACT" AS FLOAT) AS FFACT
,CAST (TCURR."TFACT" AS FLOAT) AS TFACT
,CAST(NULL AS TIMESTAMP) AS "VALID_START"
,CAST(NULL AS TIMESTAMP) AS "VALID_END"
,DENSE_RANK() OVER (ORDER BY
TCURR."MANDT"
,TCURR."KURST"
,TCURR."FCURR"
,TCURR."TCURR"
) AS "TCURR_KEY"
,ROW_NUMBER() OVER (PARTITION BY
TCURR."MANDT"
,TCURR."KURST"
,TCURR."FCURR"
,TCURR."TCURR"
ORDER BY (99999999-CAST(TCURR."GDATU" AS INT)) ASC) AS "TCURR_ROWNR"
FROM
"TCURR" AS TCURR
WHERE 1=1
AND (99999999-CAST(TCURR."GDATU" AS INT)) >= 18000000
AND (99999999-CAST(TCURR."GDATU" AS INT)) <= 20990000
);
SELECT ANALYZE_STATISTICS('TCURR_TMP');
-- Query No: 4
CREATE TABLE "TCURR_CC" AS (
SELECT
*
FROM
"TCURR_TMP"
);
-- Query No: 5
UPDATE
"TCURR_CC" AS TCURR_CC
SET
"VALID_START" = CAST(LPAD(CAST((99999999-cast(TCURR_CC."GDATU" AS INT)) AS VARCHAR(10)),8,'0') || ' ' || '00:00:00' AS TIMESTAMP)
;
-- Query No: 6
UPDATE
"TCURR_CC" AS TCURR_CC
SET
"VALID_END" = CAST(LPAD(CAST((99999999-CAST(TCURR_TMP."GDATU" AS INT)) AS VARCHAR(10)),8,'0') || ' ' || '00:00:00' AS timestamp)
FROM
"TCURR_TMP" AS TCURR_TMP
WHERE 1=1
AND TCURR_CC."TCURR_KEY" = TCURR_TMP."TCURR_KEY"
AND TCURR_CC."TCURR_ROWNR" + 1=TCURR_TMP."TCURR_ROWNR"
;
-- Query No: 7
UPDATE
"TCURR_CC" AS TCURR_CC
SET
"VALID_END" = '2099-01-01'
WHERE
"VALID_END" IS NULL
;
--Add statistics for created temporary join table
SELECT ANALYZE_STATISTICS('TCURR_CC');
-- Query No: 8
DROP TABLE IF EXISTS "TCURR_TMP";
Transformation 2: quantity conversion table
DROP TABLE IF EXISTS QTY_UNIT_CONVERSION;
CREATE TABLE QTY_UNIT_CONVERSION AS
SELECT
"MARA"."MANDT"
,"MARA"."MATNR"
,"MARA"."MEINS" AS "FROM_UNIT"
,CASE
WHEN "T006"."DIMID" != 'AAAADL' THEN "T006D"."MSSIE" -- check if the dimension (DIMID) is an SI unit (ie. != 'AAAADL'), if it is then take the SI base unit (MSSIE)
WHEN "T006"."DIMID" = 'AAAADL' THEN "MARM"."MEINH" -- check if the dimension (DIMID) is not an SI unit (ie. = AAAADL), if not then take the non-SI base unit (MEINH)
END AS "TO_UNIT"
,CASE
WHEN "T006"."DIMID" != 'AAAADL' THEN "T006"."ZAEHL" / "T006"."NENNR" * POWER(10, "T006"."EXP10") -- if DIMID is an SI unit, apply the SI conversion logic to convert to base unit. (eg. converting Grams into KG)
WHEN "T006"."DIMID" = 'AAAADL' THEN "MARM"."UMREN"/ "MARM"."UMREZ" -- if DIMID is not an SI unit, apply the non-SI conversion logic to convert to base unit (eg. converting Pallets into Pieces)
END AS "CONVERSION_FROM_TO"
,"T006"."DIMID" AS "CONVERSION_DIMENSION"
FROM "MARA"
LEFT JOIN T006 AS T006 ON 1=1
AND T006.MANDT = MARA.MANDT
AND T006.MSEHI = MARA.MEINS
LEFT JOIN T006D ON 1=1
AND T006D.MANDT = T006.MANDT
AND T006D.DIMID = T006.DIMID
LEFT JOIN "MARM" ON 1=1
AND "MARA"."MANDT" = "MARM"."MANDT" -- TB: MOVED UP
AND "MARA"."MATNR" = "MARM"."MATNR"
AND "MARM"."MEINH" = 'EA' -- target/base unit for all non SI units. If you have more than 1 base non-SI unit, please create a Service Desk ticket to discuss your use case with the Supply Chain Team.
;
--Add statistics for created temporary join table
SELECT ANALYZE_STATISTICS('QTY_UNIT_CONVERSION');Transformation 3: IM_MARC - material table
DROP TABLE IF EXISTS IM_MARC;
CREATE TABLE IM_MARC AS (
SELECT
MARC.*
,T001W.REGIO
,T001W.COUNC
,T001W.LAND1
,T001W.CITYC
,T001W.ORT01
,T001W.NAME1
,MAKT.MAKTX
,T001K.BUKRS -- Company Code
,T001.BUTXT -- Company Name
,CASE -- 01
WHEN MBEW.VPRSV = 'V' THEN COALESCE(MBEW.VERPR / NULLIFZERO(MBEW.PEINH), MBEW.VERPR)
WHEN MBEW.VPRSV = 'S' THEN COALESCE(MBEW.STPRS / NULLIFZERO(MBEW.PEINH), MBEW.STPRS)
ELSE MBEW.SALK3 / MBEW.LBKUM
END AS "VERPR" -- Dividing the MAP by the price unit.
,(CASE
WHEN MBEW.VPRSV = 'V'
THEN
CASE
WHEN "T001"."WAERS" = '<%=currency%>' THEN MBEW."VERPR"*COALESCE("TCURX"."TDEC",1)
WHEN "TCURR_CC"."UKURS" < 0 THEN (MBEW."VERPR"*COALESCE("TCURX"."TDEC",1))*(1/ABS(TCURR_CC.UKURS)/(CASE WHEN COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT) END))*(CASE WHEN COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT) END)
WHEN "TCURR_CC"."UKURS" > 0 THEN (MBEW."VERPR"*COALESCE("TCURX"."TDEC",1))*(TCURR_CC.UKURS/CASE WHEN COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT) END*CASE WHEN COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT) END)
END
WHEN MBEW.VPRSV = 'S'
THEN
CASE
WHEN "T001"."WAERS" = '<%=currency%>' THEN MBEW."STPRS"*COALESCE("TCURX"."TDEC",1)
WHEN "TCURR_CC"."UKURS" < 0 THEN (MBEW."STPRS"*COALESCE("TCURX"."TDEC",1))*(1/ABS(TCURR_CC.UKURS)/(CASE WHEN COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT) END))*(CASE WHEN COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT) END)
WHEN "TCURR_CC"."UKURS" > 0 THEN (MBEW."STPRS"*COALESCE("TCURX"."TDEC",1))*(TCURR_CC.UKURS/CASE WHEN COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT) END*CASE WHEN COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT) END)
END
ELSE
CASE
WHEN "T001"."WAERS" = '<%=currency%>' THEN MBEW.SALK3 / MBEW.LBKUM *COALESCE("TCURX"."TDEC",1)
WHEN "TCURR_CC"."UKURS" < 0 THEN (MBEW.SALK3 / MBEW.LBKUM *COALESCE("TCURX"."TDEC",1))*(1/ABS(TCURR_CC.UKURS)/(CASE WHEN COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT) END))*(CASE WHEN COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT) END)
WHEN "TCURR_CC"."UKURS" > 0 THEN (MBEW.SALK3 / MBEW.LBKUM *COALESCE("TCURX"."TDEC",1))*(TCURR_CC.UKURS/CASE WHEN COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT) END*CASE WHEN COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT) END)
END
END) / NULLIFZERO(MBEW.PEINH) / "QUC"."CONVERSION_FROM_TO" AS "VERPR_CON"
,"MARA"."MEINS"
,"QUC"."TO_UNIT" AS "MSSIE"
FROM MARC
LEFT JOIN T001W ON 1=1
AND MARC.MANDT = T001W.MANDT
AND MARC.WERKS = T001W.WERKS
LEFT JOIN MAKT ON 1=1
AND MARC.MANDT = MAKT.MANDT
AND MARC.MATNR = MAKT.MATNR
AND MAKT.SPRAS = 'E'
LEFT JOIN T001K ON 1=1
AND MARC.MANDT = T001K.MANDT
AND MARC.WERKS = T001K.BWKEY
LEFT JOIN T001 ON 1=1
AND T001.MANDT = T001K.MANDT
AND T001.BUKRS = T001K.BUKRS
LEFT JOIN MBEW ON 1=1
AND MBEW.MANDT = MARC.MANDT
AND MBEW.MATNR = MARC.MATNR
AND MBEW.BWKEY = MARC.WERKS
AND MBEW.BWTAR IS NULL -- removes duplicate rows in this table.
LEFT JOIN "MARA" AS "MARA" ON 1=1
AND "MARA"."MANDT" = "MARC"."MANDT"
AND "MARA"."MATNR" = "MARC"."MATNR"
-- Qty Conversion
LEFT JOIN "QTY_UNIT_CONVERSION" AS "QUC" ON 1=1
AND "QUC"."MANDT" = "MARA"."MANDT"
AND "QUC"."MATNR" = "MARA"."MATNR"
AND "QUC"."FROM_UNIT" = "MARA"."MEINS"
-- Currency Conversion for MAP
LEFT JOIN "TCURR_CC" AS "TCURR_CC" ON 1=1
AND "TCURR_CC"."MANDT" = T001.MANDT
AND "TCURR_CC"."TCURR" = '<%=currency%>'
AND "TCURR_CC"."FCURR" = "T001"."WAERS"
AND "TCURR_CC"."KURST" = 'M'
AND CURRENT_DATE >= "TCURR_CC"."VALID_START"
AND CURRENT_DATE < "TCURR_CC"."VALID_END"
LEFT JOIN "TCURF_CC" AS "TCURF_CC" ON 1=1
AND "TCURR_CC"."MANDT" = "TCURF_CC"."MANDT"
AND "TCURR_CC"."TCURR" = "TCURF_CC"."TCURR"
AND "TCURR_CC"."FCURR" = "TCURF_CC"."FCURR"
AND "TCURR_CC"."KURST" = "TCURF_CC"."KURST"
AND CURRENT_DATE >= "TCURF_CC"."VALID_START"
AND CURRENT_DATE < "TCURF_CC"."VALID_END"
LEFT JOIN (
SELECT
"TCURX"."CURRKEY"
,CAST("TCURX"."CURRDEC" AS INT) AS "CURRDEC"
,POWER(CAST(10 AS FLOAT),(2-"CURRDEC")) AS "TDEC"
FROM "TCURX"
) AS "TCURX" ON 1=1
AND "T001"."WAERS" = "TCURX"."CURRKEY"
);
--Add statistics for created table
SELECT ANALYZE_STATISTICS('IM_MARC');CONSIGNMENT_MOVEMENTS table
The SAP ECC tables that supply data for this transformation are MSEG, MKPF, T156, T156T, T001, T003T, and USR02.
Transformation 4: CONSIGNMENT_MOVEMENTS table
DROP TABLE IF EXISTS CONSIGNMENT_MOVEMENTS;
CREATE TABLE CONSIGNMENT_MOVEMENTS AS(
SELECT
CAST(MKPF.CPUDT AS DATE) + COALESCE(CAST(MKPF.CPUTM AS TIME),CAST('00:00:00' AS TIME)) AS EVENTTIME
,YEAR(CAST(MKPF.CPUDT AS DATE) + COALESCE(CAST(MKPF.CPUTM AS TIME),CAST('00:00:00' AS TIME))) AS JAHR
,MONTH(CAST(MKPF.CPUDT AS DATE) + COALESCE(CAST(MKPF.CPUTM AS TIME),CAST('00:00:00' AS TIME))) AS MONAT
,CAST(MKPF.BUDAT AS DATE) + CAST('00:00:00' AS TIME) AS BUDAT
,MSEG.KUNNR
,MSEG.VFDAT
,MSEG.MANDT
,MSEG.MBLNR
,MSEG.MJAHR
,MSEG.ZEILE
,T156T_DE.BTEXT AS ACTIVITY_DE
,T156T_EN.BTEXT AS ACTIVITY_EN
,T003T_DE.LTEXT AS ACTIVITY_DETAIL_DE
,T003T_EN.LTEXT AS ACTIVITY_DETAIL_EN
,MKPF.USNAM AS USER_NAME
,USR02.USTYP AS USER_TYPE
-- The table 'T156' lists all movement types, it can be restricted to consumption (SHKZG = 'H') or replenishment (SHKZG = 'S').
-- This first indicator is refined by using KZVBU (consumption posting - i.e. were goods actually moved) XSTBW (is the current movement type a cancellation ('X') or not (NULL))
, CASE
WHEN "MSEG"."SHKZG" = 'H' AND "T156"."SHKZG" IS NOT NULL AND "T156"."XSTBW" IS NULL THEN "MSEG"."MENGE"
WHEN "MSEG"."SHKZG" = 'S' AND "T156"."SHKZG" IS NOT NULL AND "T156"."XSTBW" = 'X' THEN -1.0 * "MSEG"."MENGE"
ELSE 0.0
END AS CONS_QTY
, CASE
WHEN "MSEG"."SHKZG" = 'H' AND "T156"."SHKZG" IS NOT NULL AND "T156"."XSTBW" IS NULL THEN "MSEG"."MENGE" * COALESCE("QUC"."CONVERSION_FROM_TO",1)
WHEN "MSEG"."SHKZG" = 'S' AND "T156"."SHKZG" IS NOT NULL AND "T156"."XSTBW" = 'X' THEN -1.0 * "MSEG"."MENGE" * COALESCE("QUC"."CONVERSION_FROM_TO",1)
ELSE 0.0
END AS CONS_QTY_CON
, CASE
WHEN "MSEG"."SHKZG" = 'S' AND "T156"."SHKZG" IS NOT NULL AND "T156"."XSTBW" IS NULL THEN "MSEG"."MENGE"
WHEN "MSEG"."SHKZG" = 'H' AND "T156"."SHKZG" IS NOT NULL AND "T156"."XSTBW" = 'X' THEN -1.0 * "MSEG"."MENGE"
ELSE 0.0
END AS REPL_QTY
, CASE
WHEN "MSEG"."SHKZG" = 'S' AND "T156"."SHKZG" IS NOT NULL AND "T156"."XSTBW" IS NULL THEN "MSEG"."MENGE" * COALESCE("QUC"."CONVERSION_FROM_TO",1)
WHEN "MSEG"."SHKZG" = 'H' AND "T156"."SHKZG" IS NOT NULL AND "T156"."XSTBW" = 'X' THEN -1.0 * "MSEG"."MENGE" * COALESCE("QUC"."CONVERSION_FROM_TO",1)
ELSE 0.0
END AS "REPL_QTY_CON"
,"MSEG"."MEINS"
,"QUC"."TO_UNIT" AS "MSSIE"
,"MSEG"."BWART"
,"MSEG"."MATNR"
,"MSEG"."WERKS"
,"MSEG"."LGORT"
,"MSEG"."CHARG"
,"MSEG"."LIFNR"
,"MSEG"."KDAUF"
,"MSEG"."KDPOS"
,"MSEG"."SHKZG"
,"MSEG"."DMBTR"
,"MSEG"."BWTAR"
,"MSEG"."MENGE"
,MENGE * "QUC"."CONVERSION_FROM_TO" AS MENGE_CONVERTED
,"MSEG"."EBELN"
,"MSEG"."EBELP"
,"MSEG"."SGTXT"
,"MSEG"."BUKRS"
,"MSEG"."RSNUM"
,"MSEG"."RSPOS"
,"MSEG"."UMWRK"
,"MSEG"."UMLGO"
,"MSEG"."LBKUM"
,LBKUM * "QUC"."CONVERSION_FROM_TO" AS LBKUM_CONVERTED
,"MSEG"."SALK3"
,"T001"."WAERS" -- local currency
,"MSEG"."PROJN"
,"MSEG"."AUFNR"
,"MSEG"."AUFPS"
,"MSEG"."SOBKZ"
,"MSEG"."KZBEW"
,"MSEG"."KZZUG"
,"MSEG"."KZVBR"
,"QUC"."TO_UNIT" AS MEINS_CONVERTED_TO
,"QUC"."CONVERSION_DIMENSION" AS CONVERSION_DIMENSION
FROM MSEG
JOIN IM_MARC AS MARC ON 1=1
AND MSEG.MANDT = MARC.MANDT
AND MSEG.MATNR = MARC.MATNR
AND MSEG.WERKS = MARC.WERKS
JOIN MKPF AS MKPF ON 1=1
AND MKPF.MANDT = MSEG.MANDT
AND MKPF.MBLNR = MSEG.MBLNR
AND MKPF.MJAHR = MSEG.MJAHR
--Movement Types Clear Names
LEFT JOIN T156 ON 1=1 -- Consumption / Replenishment Information
AND MSEG.MANDT = T156.MANDT
AND MSEG.BWART = T156.BWART
AND T156.KZVBU IS NOT NULL
LEFT JOIN T156T AS T156T_EN ON 1=1
AND T156T_EN.MANDT = MSEG.MANDT
AND T156T_EN.BWART = MSEG.BWART
AND COALESCE(MSEG.SOBKZ,'') = COALESCE(T156T_EN.SOBKZ,'')
AND COALESCE(MSEG.KZBEW,'') = COALESCE(T156T_EN.KZBEW,'')
AND COALESCE(MSEG.KZZUG,'') = COALESCE(T156T_EN.KZZUG,'')
AND COALESCE(MSEG.KZVBR,'') = COALESCE(T156T_EN.KZVBR,'')
AND T156T_EN.SPRAS = 'E'
LEFT JOIN T156T AS T156T_DE ON 1=1
AND T156T_DE.MANDT = MSEG.MANDT
AND T156T_DE.BWART = MSEG.BWART
AND COALESCE(MSEG.SOBKZ,'') = COALESCE(T156T_DE.SOBKZ,'')
AND COALESCE(MSEG.KZBEW,'') = COALESCE(T156T_DE.KZBEW,'')
AND COALESCE(MSEG.KZZUG,'') = COALESCE(T156T_DE.KZZUG,'')
AND COALESCE(MSEG.KZVBR,'') = COALESCE(T156T_DE.KZVBR,'')
AND T156T_DE.SPRAS = 'D'
LEFT JOIN T003T AS T003T_EN ON 1=1
AND T003T_EN.MANDT = MKPF.MANDT
AND T003T_EN.BLART = MKPF.BLART
AND T003T_EN.SPRAS = 'E'
LEFT JOIN T003T AS T003T_DE ON 1=1
AND T003T_DE.MANDT = MKPF.MANDT
AND T003T_DE.BLART = MKPF.BLART
AND T003T_DE.SPRAS = 'D'
--User Types
LEFT JOIN USR02 AS USR02 ON 1=1
AND USR02.MANDT = MKPF.MANDT
AND USR02.BNAME = MKPF.USNAM
--Local Currency
LEFT JOIN "T001" ON 1=1
AND "T001"."MANDT" = "MSEG"."MANDT"
AND "T001"."BUKRS" = "MSEG"."BUKRS"
-- Quantitiy Conversion
LEFT JOIN "QTY_UNIT_CONVERSION" AS "QUC" ON 1=1
AND QUC.MANDT = MARC.MANDT
AND QUC.MATNR = MARC.MATNR
AND QUC.FROM_UNIT = MSEG.MEINS
WHERE MSEG.SOBKZ IN ('W') AND
YEAR(CAST(MKPF.CPUDT AS DATE) + COALESCE(CAST(MKPF.CPUTM AS TIME),CAST('00:00:00' AS TIME))) > 2021
);
SELECT ANALYZE_STATISTICS('CONSIGNMENT_MOVEMENTS');CONSIGNMENT_STOCK_HISTORY table
The SAP ECC tables that supply data for this transformation are MSKU and MSKUH.
Transformation 5: CONSIGNMENT_STOCK_HISTORY table
-----------------------------------------------------------------------------
-- Step 1: Create a table that unions MSKU and MSKUH
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
--- Step 1a: Create a temporary view for MSKUH, , using the lead function to get the next fiscal year entry per material || plant || eventtime combination.
-----------------------------------------------------------------------------
DROP TABLE IF EXISTS MSKUH_TMP;
CREATE TABLE MSKUH_TMP AS (
SELECT
MANDT
,MATNR
,WERKS
,SOBKZ
,KUNNR
,ADD_MONTHS(
CAST(
LAST_DAY(
CAST(
LFGJA ||'-'||LFMON||'-'||'01' AS DATE
)
)
+
CAST(
'23:59:59' AS TIME
)
AS DATETIME
)
, 0
) AS 'CALENDAR_DATE'
,LEAD(LFGJA,1,0) OVER (PARTITION BY MANDT, MATNR, WERKS, SOBKZ, KUNNR ORDER BY LFGJA, LFMON ASC) AS NEXT_ROW_FISCAL_YEAR -- The next fiscal year in the table is returned to each material || plant combination. Eg. If material A has an entry in 01.2020 and another 02.2021, 2021 would be returned as Next_row_fiscal_year for 01.2020, and 0 would be returned for the row containing 02.2021 (as there is no leading fiscal year)
,SUM(KULAB) AS KULAB -- Amount of unrestricted, available stock
,SUM(KUINS) AS KUINS -- Amount of stock in quality inspection
,SUM(KUEIN) AS KUEIN -- Amount of stock in transfer
,SUM(KUUML) AS KUUML -- Amount of blocked stock
FROM MSKUH
WHERE
EXISTS(
SELECT *
FROM IM_MARC AS MARC
WHERE 1=1
AND MSKUH.MANDT = MARC.MANDT
AND MSKUH.MATNR = MARC.MATNR
AND MSKUH.WERKS = MARC.WERKS
)
AND (KULAB IS NOT NULL
OR KUINS IS NOT NULL
OR KUEIN IS NOT NULL
OR KUUML IS NOT NULL)
GROUP BY MANDT, MATNR, WERKS, SOBKZ, KUNNR, CALENDAR_DATE, LFGJA, LFMON
ORDER BY CALENDAR_DATE ASC
);
SELECT ANALYZE_STATISTICS('MSKUH_TMP');
-----------------------------------------------------------------------------
--- Step 1b: Create MSKU+MSKUH merged table by unioning.
-----------------------------------------------------------------------------
DROP TABLE IF EXISTS MSKU_FULL_TMP;
CREATE TABLE MSKU_FULL_TMP AS (
SELECT
MANDT
,MATNR
,WERKS
,SOBKZ
,KUNNR
,ADD_MONTHS(
CAST(
LAST_DAY(
CAST(
LFGJA ||'-'||LFMON||'-'||'01' AS DATE
)
)
+ CAST('23:59:59' AS TIME) AS DATETIME
)
, 0
) AS 'CALENDAR_DATE'
,SUM(KULAB) AS KULAB -- Amount of unrestricted, available stock
,SUM(KUINS) AS KUINS -- Amount of stock in quality inspection
,SUM(KUEIN) AS KUEIN -- Amount of stock in transfer
,SUM(KUUML) AS KUUML -- Amount of blocked stock
FROM MSKU
WHERE
EXISTS(
SELECT *
FROM IM_MARC AS MARC
WHERE 1=1
AND MSKU.MANDT = MARC.MANDT
AND MSKU.MATNR = MARC.MATNR
AND MSKU.WERKS = MARC.WERKS
)
AND (KULAB IS NOT NULL
OR KUINS IS NOT NULL
OR KUEIN IS NOT NULL
OR KUUML IS NOT NULL
)
GROUP BY
MANDT, MATNR, WERKS, SOBKZ, KUNNR, CALENDAR_DATE
UNION ALL
SELECT
MANDT
,MATNR
,WERKS
,SOBKZ
,KUNNR
,CALENDAR_DATE
,KULAB -- Amount of unrestricted, available stock
,KUINS -- Amount of stock in quality inspection
,KUEIN -- Amount of stock in transfer
,KUUML -- Amount of blocked stock
FROM MSKUH_TMP
WHERE NEXT_ROW_FISCAL_YEAR >= 2000 OR NEXT_ROW_FISCAL_YEAR = 0 -- ensures that only relevant fiscal years are used in creating the valuated stock history table. In the case of 0, that's the most recent entry with no lead fiscal year.
ORDER BY CALENDAR_DATE DESC
);
SELECT ANALYZE_STATISTICS('MSKU_FULL_TMP');
-----------------------------------------------------------------------------
-- Step 2: Create a Calendar Table that can be joined to
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
-- Step 2a: Create a table with two entries
-----------------------------------------------------------------------------
DROP TABLE IF EXISTS "TMP_CALENDAR";
CREATE TABLE TMP_CALENDAR
(
"EVENTTIME" DATETIME PRIMARY KEY
);
INSERT INTO TMP_CALENDAR SELECT DISTINCT CAST(LAST_DAY(CAST(current_timestamp AS DATE)) + CAST('00:00:00' AS TIME) AS DATETIME);
INSERT INTO TMP_CALENDAR SELECT MIN(CALENDAR_DATE) FROM MSKU_FULL_TMP;
SELECT ANALYZE_STATISTICS('TMP_CALENDAR');
-----------------------------------------------------------------------------
-- Step 2b: Slice the table to create a date for every month between the upper dates
-----------------------------------------------------------------------------
DROP TABLE IF EXISTS "CALENDAR";
CREATE TABLE CALENDAR AS(
SELECT DISTINCT
LAST_DAY(CAST(slice_time AS DATE)) AS 'EVENTTIME'
FROM TMP_CALENDAR C
TIMESERIES slice_time as '25 DAYS'
OVER (ORDER BY CAST(C."EVENTTIME" as DATETIME)));
SELECT ANALYZE_STATISTICS('CALENDAR');
-----------------------------------------------------------------------------
-- Step 3: Join the calendar to the MARC table, ie. give each material and plant combination n eventtimes.
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
---Step 3a: Create a single instance for each MANDT, MATNR, WERKS, KUNNR in order to create only one calendar record per day and avoid duplicates
-----------------------------------------------------------------------------
DROP TABLE IF EXISTS MSKU_FULL_TMP_DEDUPLICATED;
CREATE TABLE MSKU_FULL_TMP_DEDUPLICATED AS
SELECT DISTINCT
MANDT
,MATNR
,WERKS
,SOBKZ
,KUNNR
,MIN(CALENDAR_DATE) AS MIN_CALENDAR_DATE
,SUM(KULAB) AS SUM_KULAB
,SUM(KUINS) AS SUM_KUINS
,SUM(KUEIN) AS SUM_KUEIN
,SUM(KUUML) AS SUM_KUUML
FROM MSKU_FULL_TMP
GROUP BY MANDT,MATNR, WERKS, SOBKZ, KUNNR
;
SELECT ANALYZE_STATISTICS('MSKU_FULL_TMP_DEDUPLICATED');
-----------------------------------------------------------------------------
--Step 3b: Create MARC_DATES table for each material and plant combination n eventtimes
-----------------------------------------------------------------------------
DROP TABLE IF EXISTS MARC_DATES;
CREATE TABLE MARC_DATES AS
SELECT
MSKU_FULL_TMP.MANDT
,MSKU_FULL_TMP.MATNR
,MSKU_FULL_TMP.WERKS
,MSKU_FULL_TMP.SOBKZ
,MSKU_FULL_TMP.KUNNR
,CALENDAR.EVENTTIME
FROM "MSKU_FULL_TMP_DEDUPLICATED" AS MSKU_FULL_TMP
JOIN CALENDAR AS CALENDAR ON 1=1
AND MSKU_FULL_TMP.MIN_CALENDAR_DATE <= CALENDAR.EVENTTIME -- filters the table such that the minimum eventtime for each material || plant combination equals the minimum eventtime from the MSKU + MSKUH table.
AND (
MSKU_FULL_TMP.SUM_KULAB > 0 -- filters the table on material || plant combinations where unrestricted available stock has been > 0 at least once.
OR MSKU_FULL_TMP.SUM_KUINS > 0 -- filters the table on material || plant combinations where stock in quality inspected has been > 0 at least once.
OR MSKU_FULL_TMP.SUM_KUEIN > 0 -- filters the table on material || plant combinations where stock in transfer has been > 0 at least once.
OR MSKU_FULL_TMP.SUM_KUUML > 0 -- filters the table on material || plant combinations where blocked stock has been > 0 at least once.
) -- filters the table on material || plant combinations where inventory has been > 0 at least once
;
-----------------------------------------------------------------------------
-- Step 4: Left Join the MARC Calendar table created in the last step with the MSKU + MSKUH table created in Step 1.
-- This merges the two tables, creating dates for each month of the MSKU+MSKUH table where they did not previously exist
-----------------------------------------------------------------------------
DROP TABLE IF EXISTS MSKU_FULL_TMP_1;
CREATE TABLE MSKU_FULL_TMP_1 AS(
SELECT DISTINCT
MARC_DATES.MANDT
,MARC_DATES.MATNR
,MARC_DATES.WERKS
,MARC_DATES.SOBKZ
,MARC_DATES.KUNNR
,MSKU_FULL_TMP.KULAB
,MSKU_FULL_TMP.KUINS
,MSKU_FULL_TMP.KUEIN
,MSKU_FULL_TMP.KUUML
,EVENTTIME
FROM MARC_DATES
LEFT JOIN MSKU_FULL_TMP ON
MARC_DATES.EVENTTIME = MSKU_FULL_TMP.CALENDAR_DATE
AND MARC_DATES.MATNR = MSKU_FULL_TMP.MATNR
AND MARC_DATES.WERKS = MSKU_FULL_TMP.WERKS
AND MARC_DATES.SOBKZ = MSKU_FULL_TMP.SOBKZ
AND MARC_DATES.KUNNR = MSKU_FULL_TMP.KUNNR
ORDER BY EVENTTIME DESC
);
SELECT ANALYZE_STATISTICS('MSKU_FULL_TMP_1');
-----------------------------------------------------------------------------
-- Step 5: If there was no match on the primary keys in step 4, ie. no calendar date for a certain material, there will be material and plant
-- combinations that have null values for LABST. In this step we solve this by carrying the previous non-null entries to all nulls,
-- in ascending order on eventtime, until the next non-null is found. This process is completed until the most recent month.
-----------------------------------------------------------------------------
DROP TABLE IF EXISTS MSKU_FULL_TMP_2;
CREATE TABLE MSKU_FULL_TMP_2 AS(
SELECT
MSKU_FULL_TMP_1.MANDT
,MSKU_FULL_TMP_1.MATNR
,MSKU_FULL_TMP_1.WERKS
,MSKU_FULL_TMP_1.SOBKZ
,MSKU_FULL_TMP_1.KUNNR
,FIRST_VALUE(MSKU_FULL_TMP_1.KULAB IGNORE NULLS) OVER (PARTITION BY MSKU_FULL_TMP_1.MATNR, MSKU_FULL_TMP_1.WERKS, MSKU_FULL_TMP_1.SOBKZ, MSKU_FULL_TMP_1.KUNNR ORDER BY MSKU_FULL_TMP_1."EVENTTIME" DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS KULAB
,FIRST_VALUE(MSKU_FULL_TMP_1.KUINS IGNORE NULLS) OVER (PARTITION BY MSKU_FULL_TMP_1.MATNR, MSKU_FULL_TMP_1.WERKS, MSKU_FULL_TMP_1.SOBKZ, MSKU_FULL_TMP_1.KUNNR ORDER BY MSKU_FULL_TMP_1."EVENTTIME" DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS KUINS
,FIRST_VALUE(MSKU_FULL_TMP_1.KUEIN IGNORE NULLS) OVER (PARTITION BY MSKU_FULL_TMP_1.MATNR, MSKU_FULL_TMP_1.WERKS, MSKU_FULL_TMP_1.SOBKZ, MSKU_FULL_TMP_1.KUNNR ORDER BY MSKU_FULL_TMP_1."EVENTTIME" DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS KUEIN
,FIRST_VALUE(MSKU_FULL_TMP_1.KUUML IGNORE NULLS) OVER (PARTITION BY MSKU_FULL_TMP_1.MATNR, MSKU_FULL_TMP_1.WERKS, MSKU_FULL_TMP_1.SOBKZ, MSKU_FULL_TMP_1.KUNNR ORDER BY MSKU_FULL_TMP_1."EVENTTIME" DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS KUUML
,MSKU_FULL_TMP_1."EVENTTIME"
FROM MSKU_FULL_TMP_1
ORDER BY MSKU_FULL_TMP_1.EVENTTIME ASC
);
SELECT ANALYZE_STATISTICS('MSKU_FULL_TMP_2');
-----------------------------------------------------------------------------
-- Step 6: Aggregate up to the Material Number and filter timeframe based on start date variable.
-----------------------------------------------------------------------------
DROP TABLE IF EXISTS MSKU_FULL_TMP_3;
CREATE TABLE MSKU_FULL_TMP_3 AS (
SELECT
MSKU_FULL_TMP_2.MANDT
,MSKU_FULL_TMP_2.MATNR
,MSKU_FULL_TMP_2.WERKS
,MSKU_FULL_TMP_2.SOBKZ
,MSKU_FULL_TMP_2.KUNNR
,SUM(MSKU_FULL_TMP_2.KULAB) AS KULAB
,SUM(MSKU_FULL_TMP_2.KUINS) AS KUINS
,SUM(MSKU_FULL_TMP_2.KUEIN) AS KUEIN
,SUM(MSKU_FULL_TMP_2.KUUML) AS KUUML
,MSKU_FULL_TMP_2."EVENTTIME"
FROM MSKU_FULL_TMP_2
WHERE MSKU_FULL_TMP_2."EVENTTIME" >= ADD_MONTHS(CURRENT_DATE,(-12 * 23 - 1)) -- lower threshold of timeseries.
GROUP BY MANDT, MATNR, WERKS, SOBKZ, KUNNR, EVENTTIME
ORDER BY MSKU_FULL_TMP_2.EVENTTIME ASC
);
SELECT ANALYZE_STATISTICS('MSKU_FULL_TMP_3');
-----------------------------------------------------------------------------
-- Step 7: Finalize table by adding currency and unit conversions.
-----------------------------------------------------------------------------
DROP TABLE IF EXISTS CONSIGNMENT_STOCK_HISTORY;
CREATE TABLE CONSIGNMENT_STOCK_HISTORY AS (
SELECT *,
YEAR(EVENTTIME) AS JAHR,
MONTH(EVENTTIME) AS MONAT
FROM "MSKU_FULL_TMP_3"
WHERE MSKU_FULL_TMP_3.SOBKZ = 'W' AND YEAR(EVENTTIME)> 2021
);
-----------------------------------------------------------------------------
-- CLEAN UP --
-----------------------------------------------------------------------------
DROP TABLE IF EXISTS "MSKUH_TMP";
DROP TABLE IF EXISTS "MSKU_FULL_TMP";
DROP TABLE IF EXISTS "TMP_CALENDAR";
DROP TABLE IF EXISTS "CALENDAR";
DROP TABLE IF EXISTS "MSKU_FULL_TMP_DEDUPLICATED";
DROP TABLE IF EXISTS "MARC_DATES";
DROP TABLE IF EXISTS "MSKU_FULL_TMP_1";
DROP TABLE IF EXISTS "MSKU_FULL_TMP_2";
DROP TABLE IF EXISTS "MSKU_FULL_TMP_3";
CUSTOMERS table
The SAP ECC table that supplies data for this transformation is KNA1.
The CUSTOMERS table is a helper table for the augmented attribute that is used to define contractual parameters for each customer.
Transformation 6: CUSTOMERS table
DROP TABLE IF EXISTS CONSIGNMENT_INPUT_TEMP; CREATE TABLE CONSIGNMENT_INPUT_TEMP AS SELECT DISTINCT MANDT, KUNNR FROM CONSIGNMENT_STOCK_HISTORY; DROP TABLE IF EXISTS CUSTOMERS; CREATE TABLE CUSTOMERS AS SELECT CONSIGNMENT_INPUT_TEMP.MANDT, CONSIGNMENT_INPUT_TEMP.KUNNR, KNA1.NAME1 AS NAME FROM CONSIGNMENT_INPUT_TEMP LEFT JOIN KNA1 ON CONSIGNMENT_INPUT_TEMP.MANDT = KNA1.MANDT AND CONSIGNMENT_INPUT_TEMP.KUNNR = KNA1.KUNNR; ----------------------------------------------------------------------------- -- CLEAN UP -- ----------------------------------------------------------------------------- DROP TABLE IF EXISTS CONSIGNMENT_INPUT_TEMP;
CUSTOMER_MATERIALS table
The CUSTOMER_MATERIALS table references the CONSIGNMENT_STOCK_HISTORY table. It’s a helper table used for augmented attributes and for simplifying PQL queries. The data at this level represents the default lowest level of aggregation in the Customer Consignment Stock app (material + customer + plant).
Transformation 7: CUSTOMER_MATERIALS table
DROP TABLE IF EXISTS CUSTOMER_MATERIALS CASCADE; CREATE TABLE CUSTOMER_MATERIALS AS SELECT DISTINCT MANDT, KUNNR, MATNR, WERKS FROM CONSIGNMENT_STOCK_HISTORY;
CS_VBAK (order header) table
The SAP ECC tables that supply data for this transformation are VBAK, TVAKT, TVKOT, and T001.
If your data pool already contains the table O2C_VBAK from the standard Celonis data model for Order to Cash, you can reuse that table instead of creating it. To reuse the O2C_VBAK table, add transformation 8a. Otherwise, create the CS_VBAK table by adding transformation 8b.
Transformation 8a: CS_VBAK table reusing O2C_VBAK
DROP TABLE IF EXISTS "CS_VBAK"; CREATE TABLE "CS_VBAK" AS ( SELECT * FROM "O2C_VBAK" WHERE "O2C_VBAK"."MANDT"||"O2C_VBAK"."VBELN" IN (SELECT "VBAP"."MANDT"||"VBAP"."VBELN" FROM "CS_VBAP" AS "VBAP") );
Transformation 8b: CS_VBAK table
DROP TABLE IF EXISTS "CS_VBAK";
CREATE TABLE "CS_VBAK" AS (
SELECT
"VBAK".*,
CAST("VBAK"."ERDAT" AS DATE) AS "TS_ERDAT",
CAST("VBAK"."VDATU" AS DATE) AS "TS_VDATU",
IFNULL("TVAKT"."BEZEI",'') AS "AUART_TEXT",
IFNULL("TVKOT"."VTEXT",'') AS "VKORG_TEXT",
IFNULL("T001"."BUTXT",'') AS "BUKRS_TEXT"
FROM
"VBAK" AS "VBAK"
LEFT JOIN "TVAKT" AS "TVAKT" ON 1=1
AND "VBAK"."MANDT" = "TVAKT"."MANDT"
AND "VBAK"."AUART" = "TVAKT"."AUART"
AND "TVAKT"."SPRAS" = 'E'
LEFT JOIN "TVKOT" ON 1=1
AND "VBAK"."MANDT"="TVKOT"."MANDT"
AND "VBAK"."VKORG" = "TVKOT"."VKORG"
AND "TVKOT"."SPRAS" = 'E'
LEFT JOIN "T001" ON 1=1
AND "VBAK"."MANDT" = "T001"."MANDT"
AND "VBAK"."BUKRS_VF" = "T001"."BUKRS"
WHERE VBAK.MANDT||VBAK.VBELN IN (SELECT VBAP.MANDT||VBAP.VBELN FROM "CS_VBAP" AS VBAP)
);CS_VBAP (order items) table
The SAP ECC tables that supply data for this transformation are VBAP, VBAK, MAKT, TVAPT, and TVAGT.
The two transformations here create the CS_VBAP table and a temporary table with the converted net order value. You can put them both under the same transformation or create two separate transformations.
If your data pool already contains the table O2C_VBAP from the standard Celonis data model for Order to Cash, you can reuse the table instead of creating one. To reuse the O2C_VBAP table, add transformation 9a. Otherwise, create the CS_VBAP table by adding transformations 9b and 9c.
Transformation 9a: CS_VBAP table reusing O2C_VBAP
DROP TABLE IF EXISTS "CS_VBAP"; CREATE TABLE "CS_VBAP" AS ( SELECT * FROM "O2C_VBAP" WHERE "O2C_VBAP"."MANDT"||"O2C_VBAP"."VBELN"||"O2C_VBAP"."POSNR" IN (SELECT "CM"."MANDT"||"CM"."KDAUF"||"CM"."KDPOS" FROM "CONSIGNMENT_MOVEMENTS" AS "CM") );
Transformation 9b: Temporary table VBAP_CURR_TMP
--Create temp table with converted net order value
DROP TABLE IF EXISTS "VBAP_CURR_TMP";
CREATE TABLE "VBAP_CURR_TMP" AS(
SELECT
"VBAP"."MANDT"
,"VBAP"."VBELN"
,"VBAP"."POSNR"
,CASE
WHEN "VBAP"."WAERK" = '<%=currency%>' THEN "VBAP"."NETWR"*COALESCE("TCURX"."TDEC",1)
WHEN "TCURR_CC"."UKURS" < 0 THEN ("VBAP"."NETWR"*COALESCE("TCURX"."TDEC",1))*(1/ABS(TCURR_CC.UKURS)/(CASE WHEN COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT) END))*(CASE WHEN COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT) END)
WHEN "TCURR_CC"."UKURS" > 0 THEN ("VBAP"."NETWR"*COALESCE("TCURX"."TDEC",1))*(TCURR_CC.UKURS/CASE WHEN COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT) END*CASE WHEN COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT) END)
END AS "NETWR_CONVERTED"
,CASE
WHEN "VBAP"."WAERK" = '<%=currency%>' THEN "VBAP"."NETPR"*COALESCE("TCURX"."TDEC",1)
WHEN "TCURR_CC"."UKURS" < 0 THEN ("VBAP"."NETPR"*COALESCE("TCURX"."TDEC",1))*(1/ABS(TCURR_CC.UKURS)/(CASE WHEN COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT) END))*(CASE WHEN COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT) END)
WHEN "TCURR_CC"."UKURS" > 0 THEN ("VBAP"."NETPR"*COALESCE("TCURX"."TDEC",1))*(TCURR_CC.UKURS/CASE WHEN COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT) END*CASE WHEN COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT) END)
END AS "NETPR_CONVERTED"
,CASE
WHEN "VBAP"."WAERK" = '<%=currency%>' THEN "VBAP"."WAVWR"*COALESCE("TCURX"."TDEC",1)
WHEN "TCURR_CC"."UKURS" < 0 THEN ("VBAP"."WAVWR"*COALESCE("TCURX"."TDEC",1))*(1/ABS(TCURR_CC.UKURS)/(CASE WHEN COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT) END))*(CASE WHEN COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT) END)
WHEN "TCURR_CC"."UKURS" > 0 THEN ("VBAP"."WAVWR"*COALESCE("TCURX"."TDEC",1))*(TCURR_CC.UKURS/CASE WHEN COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.FFACT, TCURR_CC.FFACT) END*CASE WHEN COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT, 0) = 0 THEN 1 ELSE COALESCE(TCURF_CC.TFACT, TCURR_CC.TFACT) END)
END AS "WAVWR_CONVERTED"
FROM "VBAP"
LEFT JOIN "TCURR_CC" AS "TCURR_CC" ON 1=1
AND "TCURR_CC"."MANDT" = "VBAP"."MANDT"
AND "TCURR_CC"."TCURR" = '<%=currency%>'
AND "TCURR_CC"."FCURR" = "VBAP"."WAERK"
AND "TCURR_CC"."KURST" = 'M'
AND "VBAP"."ERDAT" >= "TCURR_CC"."VALID_START" AND "VBAP"."ERDAT" < "TCURR_CC"."VALID_END"
LEFT JOIN "TCURF_CC" AS "TCURF_CC" ON 1=1
AND "TCURR_CC"."MANDT" = "TCURF_CC"."MANDT"
AND "TCURR_CC"."TCURR" = "TCURF_CC"."TCURR"
AND "TCURR_CC"."FCURR" = "TCURF_CC"."FCURR"
AND "TCURR_CC"."KURST" = "TCURF_CC"."KURST"
AND "VBAP"."ERDAT" >= "TCURF_CC"."VALID_START" AND "VBAP"."ERDAT" < "TCURF_CC"."VALID_END"
LEFT JOIN (
SELECT
"TCURX"."CURRKEY"
,CAST("TCURX"."CURRDEC" AS INT) AS "CURRDEC"
,POWER(CAST(10 AS FLOAT),(2-"CURRDEC")) AS "TDEC"
FROM "TCURX") AS "TCURX" ON 1=1
AND "VBAP"."WAERK" = "TCURX"."CURRKEY"
);
-- Analyze (create) statistics for created temporary table
SELECT ANALYZE_STATISTICS('VBAP_CURR_TMP');Transformation 9c: CS_VBAP table
DROP TABLE IF EXISTS "CS_VBAP";
CREATE TABLE "CS_VBAP" AS (
SELECT
"VBAP".*,
"VBAP"."MANDT"||"VBAP"."VBELN"||"VBAP"."POSNR" as "_CASE_KEY",
CAST("VBAP"."ERDAT" AS DATE) AS "TS_ERDAT",
IFNULL("MAKT"."MAKTX",'') AS "MATNR_TEXT",
IFNULL("TVAPT"."VTEXT",'') AS "PSTYV_TEXT",
IFNULL("TVAGT"."BEZEI",'') AS "ABGRU_TEXT",
"VBAP_CURR_TMP"."NETWR_CONVERTED" AS "NETWR_CONVERTED",
"VBAP_CURR_TMP"."NETPR_CONVERTED" AS "NETPR_CONVERTED"
FROM "VBAP" AS "VBAP"
INNER JOIN "VBAK" ON 1=1
AND "VBAK"."MANDT" = "VBAP"."MANDT"
AND "VBAK"."VBELN" = "VBAP"."VBELN"
AND "VBAK"."VBTYP" = 'C'
LEFT JOIN "VBAP_CURR_TMP" AS "VBAP_CURR_TMP" ON 1=1
AND "VBAP"."MANDT" = "VBAP_CURR_TMP"."MANDT"
AND "VBAP"."VBELN" = "VBAP_CURR_TMP"."VBELN"
AND "VBAP"."POSNR" = "VBAP_CURR_TMP"."POSNR"
LEFT JOIN "MAKT" ON 1=1
AND "VBAP"."MANDT" = "MAKT"."MANDT"
AND "VBAP"."MATNR" = "MAKT"."MATNR"
AND "MAKT"."SPRAS" = 'E'
LEFT JOIN "TVAPT" ON 1=1
AND "VBAP"."MANDT" = "TVAPT"."MANDT"
AND "VBAP"."PSTYV" = "TVAPT"."PSTYV"
AND "TVAPT"."SPRAS" = 'E'
LEFT JOIN "TVAGT" ON 1=1
AND "VBAP"."MANDT" = "TVAGT"."MANDT"
AND "VBAP"."ABGRU" = "TVAGT"."ABGRU"
AND "TVAGT"."SPRAS" = 'E'
WHERE VBAP.MANDT||VBAP.VBELN||VBAP.POSNR IN (SELECT CM.MANDT||CM.KDAUF||CM.KDPOS FROM "CONSIGNMENT_MOVEMENTS" AS CM)
);CS_VBEP (schedule lines) table
The SAP ECC table that supplies data for this transformation is VBEP.
If your data pool already contains the table O2C_VBEP from the standard Celonis data model for Order to Cash, you can reuse that table instead of creating one. To reuse the O2C_VBEP table, add transformation 10a. Otherwise, create the CS_VBEP table by adding transformation 10b.
Transformation 10a: CS_VBEP table reusing O2C_VBEP
DROP TABLE IF EXISTS "CS_VBEP"; CREATE TABLE "CS_VBEP" AS ( SELECT * FROM "O2C_VBEP" WHERE "O2C_VBEP"."MANDT"||"O2C_VBEP"."VBELN"||"O2C_VBEP"."POSNR" IN (SELECT "VBAP"."MANDT"||"VBAP"."VBELN"||"VBAP"."POSNR" FROM "CS_VBAP" AS "VBAP") );
Transformation 10b: CS_VBEP table
DROP TABLE IF EXISTS "CS_VBEP";
CREATE TABLE "CS_VBEP" AS (
SELECT
"VBEP".*,
CAST("VBEP"."EDATU" AS DATE) AS "TS_EDATU",
CAST("VBEP"."LDDAT" AS DATE) AS "TS_LDDAT",
CAST("VBEP"."MBDAT" AS DATE) AS "TS_MBDAT",
CAST("VBEP"."TDDAT" AS DATE) AS "TS_TDDAT",
CAST("VBEP"."WADAT" AS DATE) AS "TS_WADAT"
FROM "VBEP" AS "VBEP"
WHERE VBEP.MANDT||VBEP.VBELN||VBEP.POSNR IN (SELECT VBAP.MANDT||VBAP.VBELN||VBAP.POSNR FROM "CS_VBAP" AS VBAP)
);CS_VBRP (invoice items) table
The SAP ECC table that supplies data for this transformation is VBRP.
If your data pool already contains the table O2C_VBRP from the standard Celonis data model for Order to Cash, you can reuse that table instead of creating it. To reuse the O2C_VBRP table, add transformation 11a. Otherwise, create the VBRP table by adding transformation 11b.
Transformation 11a: CS_VBRP table reusing O2C_VBRP
DROP TABLE IF EXISTS "CS_VBRP"; CREATE TABLE "CS_VBRP" AS ( SELECT * FROM "O2C_VBRP" WHERE "O2C_VBRP"."MANDT"||"O2C_VBRP"."AUBEL"||"O2C_VBRP"."AUPOS" IN (SELECT "VBAP"."MANDT"||"VBAP"."VBELN"||"VBAP"."POSNR" FROM "CS_VBAP" AS "VBAP") );
Transformation 11b: CS_VBRP table
DROP TABLE IF EXISTS "CS_VBRP"; CREATE TABLE "CS_VBRP" AS( SELECT "VBRP".* FROM "VBRP" AS "VBRP" WHERE VBRP.MANDT || VBRP.AUBEL || VBRP.AUPOS IN (SELECT VBAP.MANDT || VBAP.VBELN || VBAP.POSNR FROM "CS_VBAP" AS VBAP) );
Data Model for the case-centric Customer Consignment Stock app
When you’ve extracted the required tables, created the transformations, and run them, use the instructions in this section to create and load the data model shown in the diagram.
In Data Integration, go to your data pool and click the Data Models area. Click Add Data Model to create a new data model. Give it a suitable name and click Save.
Select all of the tables you created with the transformations, which are: IM_MARC, CONSIGNMENT_MOVEMENTS, CONSIGNMENT_STOCK_HISTORY, CUSTOMERS, CUSTOMER_MATERIALS, CS_VBAK, CS_VBAP, CS_VBEP, and CS_VBRP.
Create an alias for the tables with an CS_ or IM_ prefix, and give them the standard SAP ECC table names without the prefix (MARC, VBAK, VBAP, VBEP, and VBRP). Click the context menu (the three dots) at the top right of each table to create its alias.
Join the tables with the joins shown in the following table.
Table 1
Join type
Table 2
On columns
VBAK
1:n
VBAP
MANDT, VBELN
VBAP
1:n
VBEP
MANDT, VBELN, POSNR
VBAP
1:n
VBRP
MANDT, AUBEL/VBELN, AUPOS/POSNR
VBAP
1:n
CONSIGNMENT_MOVEMENTS
MANDT, VBELN/KDAUF, POSNR/KDPOS
CONSIGNMENT_STOCK_HISTORY
1:n
CONSIGNMENT_MOVEMENTS
MANDT, KUNNR, MATNR, WERKS, JAHR, MONAT
CUSTOMER_MATERIALS
1:n
CONSIGNMENT_STOCK_HISTORY
MANDT, KUNNR, MATNR, WERKS
MARC
1:n
CUSTOMER_MATERIALS
MANDT, WERKS, MATNR
CUSTOMERS
1:n
CUSTOMER_MATERIALS
MANDT, KUNNR
Load the finished data model.
Now you can install the case-centric Customer Consignment Stock app, following the instructions in Installing the case-centric Customer Consignment Stock app.