Skip to main content

Celonis Product Documentation

Prerequisites for the case-centric Unshipped Orders app

Before you set up the Unshipped Orders app, you’ll need to complete these prerequisite steps:

  1. 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.

  2. Install and configure the newest version of the Order to Cash SAP ECC Process Connector.

    This is the one shown on the app’s Celonis Marketplace listing page as the required connector. The Data Model “SAP ECC - Order to Cash Data Model” that you’ll use with the app comes with the Process Connector. The easiest way to get the connector is through the Process Connector library. If you’ve already set up this Process Connector for a different app, you can reuse it.

  3. Load the Data Model “SAP ECC - Order to Cash Data Model” with your data.

    Required tables and columns lists the tables and columns you’ll need from SAP ECC.

    Important

    The VBAP and LIPS tables, which contain sales order items and delivery items, must have fewer than 30 million rows for the Unshipped Orders app’s augmentation functionality to work. If yours are larger, see Reducing the VBAP or LIPS table size for how to work around this.

When the Data Connection and Process Connector are in place and you’ve loaded the Data Model, follow the steps in Installing the case-centric Unshipped Orders 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

Here’s the reference list of the SAP ECC tables and columns that the Unshipped Orders app needs data from:

Table

Columns

LIKP

MANDT, VBELN, KUNNR, ERNAM, LIFSK, TS_WADAT, TS_ERDAT, INCO1

LIPS

MANDT, VBELN, POSNR, LFIMG, TS_ERDAT, MATNR, WERKS, VGPOS, VGBEL

VBUP_DELIVERY

(see note below)

MANDT, VBELN, POSNR, WBSTA, KOSTA, PKSTA, CMPPI, CMPPJ, GBSTA

KNA1

KUNNR, MANDT, NAME1, VBUND, ORT01, LAND01

VBAK

AUART, AUART_TEXT, BUKRS_TEXT, BUKRS_VF, ERNAM, KUNNR, MANDT, VBELN, VKGRP, VKGRP_TEXT, VKORG, VKORG_TEXT, VTWEG, VTWEG_TEXT, WAERK, LIFSK, KVGR1, INCO1

VBAP

ABGRU, MANDT, MATKL, MATKL_TEXT, MATNR, MATNR_TEXT, NETWR, NETWR_CONVERTED, POSNR, VBELN, WERKS, WERKS_TEXT

VBUK

MANDT, VBELN, CMGST

VBFA

MANDT, VBELN, POSNN, VBELV, POSNV

VBEP

MANDT, VBELN, POSNR, ETENR, BMENG

ACTIVITY TABLE

The activity table needs to have an activity column, a case column and a timestamp column.

Note

The VBUP_DELIVERY table was only added to the Celonis Order to Cash connector recently, so if you set up the Order Management process in Celonis a while ago, you might not have it in your Data Model. The steps to add it are in Adding VBUP_DELIVERY to the Order to Cash Data Model.

Reducing the VBAP or LIPS table size

If the VBAP or LIPS tables, which contain sales order items and delivery items, exceed 30 million rows, you’ll need to reduce their sizes so that the app can create augmented attributes. The Unshipped Orders app doesn’t need to look at shipped sales order items or delivery items, so you can safely exclude these.

Here’s how to create and use reduced versions of the tables:

  1. Create a copy of the Data Model for the Unshipped Orders app. Duplicate the original “Order-to-Cash for SAP ECC” Data Model, where you’ll be replacing the VBAP and LIPS tables with your reduced tables.

    Tip

    If you want to reduce the reload time for your Data Model copy for the Unshipped Orders app, you can remove tables that aren’t required for the Unshipped Orders use case.

  2. In the Data Pool that the Unshipped Orders app is using, create a new transformation in a new or existing data job. In the transformation, paste in the following statements to create a reduced version of the VBAP table named O2C_VBAP_OPS, that excludes sales order items that have been shipped.

    DROP TABLE IF EXISTS O2C_VBAP_OPS; 

CREATE TABLE O2C_VBAP_OPS AS
(
    SELECT  DISTINCT
    VBAP.*
    FROM O2C_VBAP AS VBAP
    JOIN O2C_VBUP AS VBUP
VBAP.MANDT = VBUP.MANDT
        AND VBAP.VBELN = VBUP.VBELN
        AND VBAP.POSNR = VBUP.POSNR

    LEFT JOIN "O2C_VBFA_V" AS "VBFA"
        AND VBAP.POSNR = VBFA.POSNV
        AND VBAP.VBELN = VBFA.VBELV
        AND VBAP.MANDT = VBFA.MANDT

    LEFT JOIN "O2C_LIPS" AS "LIPS"
        AND "VBFA"."MANDT" = "LIPS"."MANDT"
        AND "VBFA"."VBELN" = "LIPS"."VBELN"
        AND "VBFA"."POSNN" = "LIPS"."POSNR"

    LEFT JOIN VBUP AS VBUP_DEL
        AND "VBUP_DEL"."MANDT" = "LIPS"."MANDT"
        AND "VBUP_DEL"."VBELN" = "LIPS"."VBELN"
        AND "VBUP_DEL"."POSNR" = "LIPS"."POSNR"
WHERE   
   (-- 0) Make sure to use your brackets correctly
     (-- 1) order item is not yet completed nor rejected
        VBUP.GBSTA IS NOT NULL AND
        VBUP.GBSTA !='C'       AND
        VBUP.ABSTA != 'C'     
        )
    OR
     -- 2) order item is completed, but DN item is not yet completed
nor rejected but not irrelevant (i.e. not null)
     -- 2.1) it exists    
     -- 2.2) open DN item
     -- 2.3) which is not rejected 
     -- Use COALESCE() to replace with '' if not existent yet, as NULL
included would lead condition to not being applied
        (
         VBUP_DEL.GBSTA IS NOT NULL AND          -- 2.1)
         VBUP_DEL.GBSTA != 'C' AND               -- 2.2)
         COALESCE("VBUP_DEL"."ABSTA",'') != 'C'  -- 2.3)
        )
    )
);

  3. Create another  transformation in the same way, and paste in the following statements to create a reduced version of the LIPS table named O2C_LIPS_OPS, that excludes delivery items that have been shipped.

    DROP TABLE IF EXISTS O2C_LIPS_OPS;

CREATE TABLE O2C_LIPS_OPS AS
(
    SELECT  DISTINCT
    LIPS.*
    FROM O2C_LIPS AS LIPS
    JOIN O2C_VBUP_DELIVERY AS VBUP
        AND LIPS.MANDT = VBUP.MANDT
        AND LIPS.VBELN = VBUP.VBELN
        AND LIPS.POSNR = VBUP.POSNR
WHERE
    -- delivery item is not yet completed nor rejected
      VBUP.GBSTA IS NOT NULL AND
      VBUP.GBSTA !='C'       AND
      VBUP.ABSTA != 'C'
);

  4. In your Data Model copy for the Unshipped Orders app, replace the current VBAP table with the O2C_VBAP_OPS table you just created. Check that the alias name specified in the Alias field is VBAP - this is the default, so it’ll be there unless you changed it previously. Do the same with the O2C_LIPS_OPS table, with the alias LIPS.

  5. Load your Data Model copy for the Unshipped Orders app to verify that the number of rows in the VBAP table and in the LIPS table is now below 30 million.

Adding VBUP_DELIVERY to the Order to Cash Data Model

The VBUP_DELIVERY table is a version of the regular VBUP table present in most Order to Cash Data Models. It contains information related to the delivery item table LIPS. The VBUP_DELIVERY table was only added to the Celonis Order to Cash connector recently, so if you set up the Order Management process in Celonis a while ago, you might not have it in your Data Model.  The Unshipped Orders app requires the VBUP_DELIVERY table, so if you don’t have it, you’ll need to add it manually to the Data Model. Here’s how:

  1. Go to the data pool where you are extracting your source system data for the app.

  2. Click Data Jobs and select the scope for the Data Connection that you're using to extract the data.

  3. In the extraction row, click Add to add a new extraction task.

  4. Give the new extraction task any meaningful name - we’ve used “Extract table: VBUP_DELIVERY”. You don’t need to choose a task template. Click Save.

    The New Extraction popup with a dropdown Choose Task Template, Name entry box, and Cancel and Save buttons.

  5. Select the new extraction task and click Add Extraction.

    unshipped_cc_vbup7.png

  6. Search for the VBUP table and check it to add it to the extraction task.

    The Search Tables tab, with the search bar under the heading Search for tables, and a check box to select the VBUP table.

  7. In the Table Configuration tab, select the VBUP table, and go to the column configuration (under the General heading). Check Rename target table and specify the target table name VBUP_DELIVERY.

    The column configuration for the VBUP table as described in this step.

  8. In the join configuration for the VBUP table, select Join another table during extraction, then click Add Join, and select the LIPS table from the list.

  9. For the LIPS table, select Use primary keys to join tables. Add this statement in the Filter on joined table box to filter the joined table with the startDate parameter:

    ERDAT >= <%=startDate%>
    The Join Configuration section with the radio button "Join another table during extraction" selected, and other selections as described in this step.

  10. Run the extraction task.

  11. Go to the Order to Cash Data Model in your Data Pool. Click Add Tables, search for the VBUP_DELIVERY table, and click on the + icon to add it. Then click Save.

    The Add Tables button next to the Set foreign keys button and the settings icon.

  12. Click New foreign key on the VBUP_DELIVERY table’s tile.

    The VBUP_DELIVERY tile in the model navigation.

  13. Link the VBUP_DELIVERY table to the LIPS table using the primary keys (MANDT, VBELN, and POSNR), with the LIPS table as the dimension table (1) and the VBUP_DELIVERY table as the fact table (N).

    The LIPS and VBUP_DELIVERY tables linked using the primary keys as described.

  14. Reload the Order to Cash Data Model.