Prerequisites for the case-centric Customer Consignment Stock app

Celonis Product Documentation

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.
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:
1. Go to Data Integration and select the Data Jobs area.
2. Click Add Data Job to create a new data job.
3. Add each of the transformations that we’ve provided in Data Model tables for the case-centric Customer Consignment Stock app.
4. 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)
CONSIGNMENT_MOVEMENTS
CONSIGNMENT_STOCK_HISTORY
CUSTOMERS
CUSTOMER_MATERIALS
CS_VBAK
CS_VBAP
CS_VBEP
CS_VBRP

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.

Was this helpful?

Would you like to provide feedback? Just click here to suggest edits.

Celonis Product Documentation

Prerequisites for the case-centric Customer Consignment Stock app

Required tables and columns for the case-centric Customer Consignment Stock app

Data Model tables for the case-centric Customer Consignment Stock app

IM_MARC (material master data) table

Transformation 1: currency conversion table

Transformation 2: quantity conversion table

Transformation 3: IM_MARC - material table

CONSIGNMENT_MOVEMENTS table

Transformation 4: CONSIGNMENT_MOVEMENTS table

CONSIGNMENT_STOCK_HISTORY table

Transformation 5: CONSIGNMENT_STOCK_HISTORY table

CUSTOMERS table

Transformation 6: CUSTOMERS table

CUSTOMER_MATERIALS table

Transformation 7: CUSTOMER_MATERIALS table

CS_VBAK (order header) table

Transformation 8a: CS_VBAK table reusing O2C_VBAK

Transformation 8b: CS_VBAK table

CS_VBAP (order items) table

Transformation 9a: CS_VBAP table reusing O2C_VBAP

Transformation 9b: Temporary table VBAP_CURR_TMP

Transformation 9c: CS_VBAP table

CS_VBEP (schedule lines) table

Transformation 10a: CS_VBEP table reusing O2C_VBEP

Transformation 10b: CS_VBEP table

CS_VBRP (invoice items) table

Transformation 11a: CS_VBRP table reusing O2C_VBRP

Transformation 11b: CS_VBRP table

Data Model for the case-centric Customer Consignment Stock app

Search results