Skip to main content

Celonis Product Documentation

Datapool Setup

The Shipped not Invoiced app relies on the same data model as the Order Management Starter Kit.

A separate data model must be created if the VBAP table exceeds 30 million rows. This can be a copy of the original O2C data model with a reduced VBAP table. To reduce data model reload times, a further reduction can be performed, such as removing tables that are not required for the SNI use case.

Reduction of VBAP

For the SNI use case not all items are relevant. For certain sales document types no invoice is expected to be created. These can be excluded to reduce the VBAP table to < 30 million rows.

Create a new transformation in data integration in a new or existing data job and paste the following statements to create a reduced version. Customize the transformation to ensure no relevant items are excluded from VBAP.

-- O2C_VBAP_SNI_REDUCED is a reduced version of VBAP with
-- only order items for which an incoice is expected to 
-- allow for augmentation (requirement < 30M rows in table 
-- with aug. attributes)
DROP VIEW IF EXISTS O2C_VBAP_SNI_REDUCED;

CREATE VIEW O2C_VBAP_SNI_REDUCED AS (
  SELECT *
  FROM "O2C_VBAP"
  WHERE (
    EXISTS (SELECT 1
            FROM "O2C_VBAK"
            WHERE 1=1
              AND "O2C_VBAK"."MANDT" = "O2C_VBAP"."MANDT"
              AND "O2C_VBAK"."VBELN" = "O2C_VBAP"."VBELN"
              AND "O2C_VBAK"."AUART" NOT IN ('RE', 'KB')) -- add the sales document types for which no invoice is expected
  )
);

In the SNI specific data model, choose the reduced table as VBAP:

image4.png

If the above transformation does not sufficiently reduce the number of rows in VBAP, use the following transformation to only include items with a goods issue or equivalent activity, such as "Supplier Invoice Receipt" for drop shipments, but without invoice creation activity. Note: This reduction limits the historical performance insights available in the Shipped Not Invoiced app; therefore avoid it if the necessary reduction can be achieved with the transformation above.

-- O2C_VBAP_SNI_REDUCED is a preliminary reduced version of VBAP with 
-- unbilled order items to allow for augmentation (requirement < 30M 
-- rows in table with aug. attributes)
DROP VIEW IF EXISTS O2C_VBAP_SNI_REDUCED;

CREATE VIEW O2C_VBAP_SNI_REDUCED AS (
   SELECT *
   FROM "O2C_VBAP"
   WHERE (
       EXISTS (SELECT 1
                   FROM "_CEL_O2C_ACTIVITIES"
                   WHERE 1=1
                       AND "O2C_VBAP"."MANDT" = "_CEL_O2C_ACTIVITIES"."MANDT"
                       AND "O2C_VBAP"."VBELN" = "_CEL_O2C_ACTIVITIES"."VBELN"
                       AND "O2C_VBAP"."POSNR" = "_CEL_O2C_ACTIVITIES"."POSNR"
                       AND "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" = 'Record Goods Issue')
       AND NOT EXISTS (SELECT 1
                   FROM "_CEL_O2C_ACTIVITIES"
                   WHERE 1=1
                       AND "O2C_VBAP"."MANDT" = "_CEL_O2C_ACTIVITIES"."MANDT"
                       AND "O2C_VBAP"."VBELN" = "_CEL_O2C_ACTIVITIES"."VBELN"
                       AND "O2C_VBAP"."POSNR" = "_CEL_O2C_ACTIVITIES"."POSNR"
                       AND "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" = 'Create Invoice')
   )
);