Skip to main content

Celonis Product Documentation

Updating the business logic

The business logic is packaged into formulae (KM KPIs) to reuse in the different parts of the app or inside of other KPIs/formulae to make the setup more concise.

These PQL-statements define the calculations of KPIs, filter statements on your data, and inefficiencies. Hence they are one of the most important parts of the app.

To give you an example, the formula FORMULA_STOCKHISTORY_CURRENCYCONVERT_INVENTORY is used both for computing the current Inventory on Hand Value as well as for evaluating Excess Stock Inefficiencies or Turnover Rate. So by adjusting the underlying business logic in that one place, you adjust the business logic for all KPIs and related definitions at once.

This list is not exhaustive and should be checked inside of the Knowledge Model. Make sure to also check nested formulas that might be used within the listed KPIs below. You can identify by the prefix whether it’s defined as KPI or formula.

Table 17. List of indicator KPIs (non-exhaustive)

KPI ID

Formula description

FORMULA_MATERIAL_INDICATOR_STOCK_OUT

Identifies a Stockout if Inventory on Hand and Consignment Stock are zero while Safety Stock is greater than zero.

FORMULA_MATERIAL_INDICATOR_STOCK_UNDER

Identifies Under Stock if Inventory on Hand is between zero and Safety Stock of if Inventory on Hand is zero while there is still Consignment Stock available.

FORMULA_MATERIAL_INDICATOR_STOCK_EXCESS

Identifies Excess Stock if Excess Inventory Rate is greater than zero but smaller than 100%.

FORMULA_MATERIAL_INDICATOR_STOCK_OBSOLETE

Identifies Obsolete Stock if Excess Inventory Rate equals 100% and there is no future demand.

FORMULA_MATERIAL_INDICATOR_STOCK_UNTOUCHED

Identifies Untouched Stock as minimum unsold stock level if said minimum is greater than Safety Stock.

KPI_MATERIAL_INDICATOR_INVENTORY_CLASSIFICATION

Computes overall Inventory Classification.

FORMULA_PLANNED_ORDER_INDICATOR_IS_OPEN

Identifies open Planned Orders

FORMULA_PURCHASE_REQUISITION_INDICATOR_IS_OPEN

Identifies open Purchase Requisitions

FORMULA_PURCHASE_ORDER_INDICATOR_IS_OPEN

Identifies open Purchase Orders

FORMULA_PRODUCTION_ORDER_INDICATOR_IS_OPEN

Identifies open Production Orders

FORMULA_SALES_ORDER_INDICATOR_IS_OPEN

Identifies open Sales Orders

FORMULA_PURCHASE_ORDER_INDICATOR_OTD

Indicates if a Purchase Order was received early, on time, or late. Used to compute Supplier Reliability KPIs.

FORMULA_PRODUCTION_ORDER_INDICATOR_OTD

Indicates if a Production Order was completed early, on time, or late. Used to compute Production Adherence KPIs.

FORMULA_SALES_ORDER_INDICATOR_OTD

Indicates if a Sales Order was shipped early, on time, or late. Used to compute Service Level KPIs. This logic is identical to the “On Time Deliveries” found in our Order Mgmt. apps.



While Stockout and Under Stock scenarios rely on “hard values” like no inventory or a certain Safety Stock, the threshold for Excess or Obsolete Stock is determined dynamically based on actual replenishment lead times and consumption. This threshold is referred to as “Target Stock”.

Table 18. KPIs influencing Excess Stock

KPI ID

Formula description

KPI_MATERIAL_PUSUM_EXCESS_INVENTORY_RATE

Share of Excess in a material’s inventory.

KPI_MATERIAL_PUAVG_TARGET_STOCK_QUANTITY

Threshold to Excess Stock. Computed by adding current Safety Stock to Initial Stock (sometimes also referred to as Cycle Stock).

FORMULA_MATERIAL_PUAVG_INITIAL_STOCK_QUANTITY

Item quantity required to cover replenishment period based on avg. daily consumption.

FORMULA_MATERIAL_PUSUM_CONSUMPTION_QUANTITY

Total of consumed quantity for a material in a given time period.

KPI_PURCHASE_ORDER_LEAD_TIME

Lead time of an individual Purchase Order.

KPI_PRODUCTION_ORDER_ITEM_LEAD_TIME

Lead time of an individual Production Order.



In case you update the underlying business logic or used variables, please make sure to also update the documentation for the business users in the Documentation folder.

Adjusting the Knowledge Model in General

In the Knowledge Model, we use variables and formulas to provide better maintainability as well as permit the bulk change of values. During the first setup of the app, you need to check both the variables and formulas. You should start by validating and adjusting the variables and then validating and adjusting the formulas.

  1. To perform the validation, align with your customer’s SAP IT Business Partner and Process Owner. They have knowledge about the data structure and are aware of customizations that might need to be included.

  2. To validate the PQL statements of the variable/ formula, use analysis in the same Data Model, copy and paste the PQL statements into it and validate the results with your SAP IT Business Partner.

  3. If required, adjust the variable/formula in the Knowledge Model by:

    1. Copying relevant code in View mode.

    2. Entering Edit mode and pasting your selection.

    3. Adjusting the formula/KPI according to your preferences/needs.

Notice

Links to the formula /KPI from dependent areas, such as filters for tasks and views, flags for inefficiencies, or KPI definitions, are automatically updated. This does not hold for changes made in the dependent Knowledge Model. If a customization is also to be used in other Inventory Management apps, the change must be transitioned to the Master Knowledge Model in the IM Starter Kit.

Although it uses many new studio components, all calculations (such as Filters, KPIs, Variables, Charts, and Tables) are still based on PQL within the Knowledge Model. If you get any error messages, you can narrow them down to the respective PQL-statement and test your code in an analysis or view to check if you have any syntax errors.

Inventory Projection - Approach using UNION_ALL()

The IM Control Center enables you to not just explore how your inventory has developed in the past, but it also gives you a glimpse of how said inventory will develop in the future based on current orders.

To achieve this, the following (order) objects are considered:

  • Current inventory on hand

  • Planned Orders

  • Purchase Requisitions

  • Purchase Orders

  • Production Orders

  • Reservations (sometimes referred to as Dependent Requirements)

  • Sales Orders

  • Independent Requirements

Warning

This logic does not include Scheduling Agreements from both the Purchase to Pay and the Order to Cash by default. 

Scheduling Agreements are currently not part of either connector and are thus not represented in the Inventory Management data model due to lack of the relevant tables. They can be added as a customization.

From a conceptual standpoint, the starting point is always today’s inventory. Any outgoing future orders will be subtracted while incoming orders will be added to compute a future inventory:

app_configuration_diagram_1.png

The required tables are combined at runtime rather than in the data model. Instead, the PQL-operator UNION_ALL() is used to create a record combining all the required tables at runtime.

Ultimately, one UNION_ALL()-object (record attribute or KPI) is required for every relevant field. The “shared columns” used to identify a unique instance of this virtual record consist of the following fields:

  • System Client

  • Material Number

  • Plant

  • Order Number

  • Order Position

  • Order Type

Creating this set of columns requires a total of six separate UNION_ALL()-objects - one for each field.

Notice

Filters on e.g. a material used in the UNION_ALL()-object will propagate to all connected tables. I.e. out of all tables referenced by the UNION_ALL()-object, only those entries will be shown which reference the filtered material (or any other attribute used as a filter).

A limitation of the operator is that currently at most eight tables can be leveraged. Thus, if you plan to add additional order types during your implementation project, you may need to either drop existing order types from the logic or combine the tables for the new order types with those of existing orders (e.g. combining EKET and EKEH if you plan to integrate Purchase Scheduling Agreements).

UNION_ALL()-objects can contain either references to a specific table or other KPIs computed by Celonis using Pull Up Aggregation.

Conceptually, a single UNION_ALL()-object is created as shown below:

app_configuration_diagram_2.png

To obtain the Inventory Projection, the UNION_ALL()-object containing the relevant quantity fields is then wrapped by the RUNNING_SUM()-operator. This covers a single Material-Plant combination.

To compute an aggregated Inventory Projection -e.g. on the level of Material Group or Plant- the RUNNING_TOTAL()-operator is used instead.

The combined record created in this manner can be used to compute KPIs such as “Days until Stockout”.

The results provided by it are however quite expensive as far as computational performance is concerned. It is therefore recommended to use alternatives where possible.

Warning

The result of this calculation is typically similar to the output of your MRP run as e.g. displayed in SAP's MD04 transaction. 

Due to recent manual changes by MRP-planners or the data load schedule in Celonis in general, minor deviations are to be expected. The goal is not to replicate MD04 1:1 at all times but to leverage inventory projections for aggregated analysis of future developments and prioritization of respective materials.

Inventory Projection - Visualization using TIMELINE_COLUMN() and TIMELINE_TABLE()

To address the aforementioned impact on computational performance, visualizations of the Inventory Projections should in most cases be handled using the TIMELINE_COLUMN() and TIMELINE_TABLE() operators instead.

Both operators are required to create a date field as well as actual KPIs. The assets required to populate a chart are introduced below:

app_configuration_diagram_3.png

Notice

TIMELINE_COLUMN() and TIMELINE_TABLE() generate a virtual table at runtime which is used to populate e.g. chart components. This virtual table is not connected to any tables in your data model and can therefore not be used to compute additional KPIs.