Celonis Product Documentation

Create table

Country_CodesThis table is created once on the initial setup of the data pool. It is used to create a table that stores location data such as regional, sub-regional, country, state, and city information. The ISO2 field in these tables enables it to be joined to the LFA1 (Supplier Table), KNA1 (Customer table), and MARC (Material Master data table) tables, thus enabling location data for the different records within those tables. This will discussed further in the respective jobs that make use of this table, namely:

Extractions

Please see 8.2 Appendix 2: Table Requirements for the list of table requirements for the extractions. Note, for the P2P, O2C, and PP tables, those extractions should have already been performed in the respective data pools. Although the inventory management team tries very hard to keep this information up to date, it is possible that the list of table requirements is slightly out of sync with the requirements of the extraction tables.

Create Tables: Currency Conversion

This transformation is used to transform the TCURR and TCURF tables into an appropriate format such that they can be used for currency conversion throughout the other transformations. For more information, see Currency Conversion - SAP ECC.

Create Tables

Qty Unit ConversionThis transformation is used to create a reduced version of the table MARA to create conversions from any unit to a base SI or non-SI unit. Conversion rates are computed using T006 and MARM.The desired base unit for non-SI units can be defined using data pool parameter targetQtyUnit. It is currently not a part of the standard to convert into multiple different units. If this is a core requirement, please create a support ticket to discuss options.

Create Table

IM_MARC [Case Table]This transformation is used to transform the standard MARC Table into that of the Case Table of the MM data model. The unique identifier of this table is the Material || Plant combination, so the specific material in a specific plant. The table is extended by adding fields from T001W, MAKT, T001K and T001.

Create Table: _CEL_IM_ACTIVITIES [Activity Table]

This transformation is used to transform the MSEG table into the _CEL_IM_Activites table. The table is extended by the MKPF (Material Movement Head Table) to include the exact eventtime of the material movement activity, as well as the username of the document’s creator.  

The activities of this activity table are unique compared to the other standard processes because they are activities on the Material || Plant object. This means that not a single document is followed as the case (like in the P2P process for example), but rather all the activities pertaining to the stock keeping unit (SKU) are tracked. 

The T156T table and the T003T table are used to obtain readable names for the movement types and movement descriptions respectively.

T156

The T156 table is used to determine whether a material movement should be considered a consumption or a replenishment, or not considered at all. 

The T156.KZVBU field is used to determine whether a material movement signifies a change in inventory (ie. replenishment or consumption). The options for this field are:

When NULL, the join condition is not met, and therefore those movements are not considered in the consumptions/replenishment columns.

The MSEG.SHKZG fields (credit or debit indicator) combined with the T156.XSTBW field (whether the movement type is a cancellation / reversal or not)  are leveraged to distinguish between replenishment and consumption. 

So for the CONS_QTY field, the logic highlighted in blue checks whether the consumption is a credit (credit = H = stock being reduced), and then checks if the cancellation / reversal field is null (indicating that the movement is in fact an actual consumption, not a reversal of a consumption). Movement 261 (goods issue for an order) would fall into this category as an example. 

The logic highlighted in yellow indicates that the movement was for a consumption that was reversed. Movement 262 would be an example of a reversal, as it is defined as a goods issue reversal / cancellation. In this case, the reversal is considered as a negative consumption, as stock comes back into the company, but not as a traditional replenishment.

The same logic applies for the REPL_QTY fields, however with the opposite approach for the CONS_QTY. Namely, the order of the S and H in the case when.

QTY_UNIT_CONVERSION

There is a join to QTY_UNIT_CONVERSION present in the IM_Activities table which is used to convert the consumptions into SI units. For example, if one company uses LBs, and another company KGs, this cannot be compared 1:1 inside of the application. This is resolved by converting the units to the SI units based on the quantity dimension (eg. mass, volume, length, etc.). In the event that the dimensions are null, which is the case for ST (Stueck / pieces), then the quantity conversion remains unchanged.

Create Table: IM_MARA

The IM_MARA table is used to provide standard MARA fields, as well as additional name mappings to the Materials present within the data pool. The additional tables T023T and T134T are required for those mappings. 

Create Table: IM_RESB

This transformation is used to create the IM_RESB table, which is used to import all Reservation Orders into the Inventory Management data model. There is a join made on QTY_UNIT_CONVERSION in order to convert the reservation quantities into SI units. The important data from this table is ultimately the reservations that are still open, what materials and plants these are for and what quantities and due dates are required. This information is primarily used in determining the Stock Requirements projected into the future. In the transformation there is a where exists statement that ensures only materials present in the MARC table are considered in the RESB table. 

Create Table: IM_PBED

This transformation is used to create the IM_PBED table, which is used to import all Independent Requirements into the Inventory Management data model. All PBED fields are selected in this transformation, and some additional fields from PBIM are added (Material Number and Plant Number), allowing for the join to MARC to be made in the data model. There is a join made on QTY_UNIT_CONVERSION as well in order to convert the required quantities into SI units. The important data from this table is ultimately the requirements that are still open, what materials and plants these are for and what quantities and due dates are required. This information is primarily used in determining the Stock Requirements projected into the future. In the transformation there is a where exists statement that ensures only materials present in the MARC table are considered in this table. 

Create Table: IM_PLAFT

his transformation is used to create the IM_PLAF table, which is used to import all Planned Orders into the Inventory Management data model. There is a join made on QTY_UNIT_CONVERSION in order to convert the reservation quantities into SI units. The important data from this table is ultimately the future planned orders, what materials and plants these are for and what quantities and due dates are required.

Entries already connected to purchase orders, production orders, or reservations are not imported. This information is primarily used in determining the Stock Requirements projected into the future. In the transformation there is a where exists statement that ensures only materials present in the MARC table are considered in the RESB table. 

Start of Stock History Table Creation

This transformation is empty, and is simply used to mark the start of the stock history table transformations. The Stock history table compiles information from the Valuated Inventory, Sales Order Inventory, Unrestricted Use Stock, Inventory in Transit and Material Movements. The next sections will detail the specifics of each transformation.

Create Temp Table: VALUATED_STOCK_HISTORY_TMP

The Valuated Stock History table is based on the MBEW and MBEWH tables, which evidently store information about the current valuated inventory on hand and the historic valuated inventory on hand respectively. 

Due to the way SAP stores valuated inventory (current and historic), a series of transformations are required to construct a time series representation of this. There is step by step information on this table’s creation inside of the transformation. 

The final result of the table is a month by month history of valuated inventory for each unique Material || Plant combination in the data pool, dating back 24 full months plus the current month.

Create Temp Table: SALES_ORDER_STOCK_HISTORY_TMP

The Sales Order Stock History is used to display the current and historical inventory that was reserved for sales orders. The tables involved are EBEW and EBEWH. It is quite common that these tables are not populated. So if that is the case, simply disable the extraction. A very similar transformation approach to the Valuated Stock History is used for creating this table. The main difference between the two is that the EBEW and EBEWH tables also store VBELN information, hence one aggregation level lower than the MBEW/MBEWH tables. As commented in the transformations, this requires us to carry the Sales Order case key through the first couple of transformations, before aggregating up to the material || plant later on. 

The final result of the table is a month by month history of sales order stock for each unique Material || Plant combination in the data pool, dating back 24 full months plus the current month.

Create Temp Table: UNRESTRICTED_USE_STOCK_HISTORY_TMP

The Unrestricted Use Stock is based on MARD and MARDH tables, and is used to create the current and historical inventory that is unrestricted for use. A very similar approach to the Sales Order Stock table creation is used for this table, as the aggregation level is also one level below Material || Plant, in this case also considering the storage location (LGORT). The final result of the table is a month by month history of unrestricted use stock for each unique Material || Plant combination in the data pool, dating back 24 full months plus the current month.

Create Temp Table: STOCK_IN_TRANSIT_STOCK_HISTORY_TMP

This table is used for storing the current and historical stock in transit information, aggregated to the Material || Plant level for each month. A very similar approach is used to the unrestricted and sales order stock. 

Create Temp Table: MATERIAL_AVAILABILITY

The main objective of this transformation is to create a table that stores the number of days material was available per SKU per month. Availability is defined as inventory qty on hand being greater than zero. This can be adjusted in Step 10 of the transformation if desired. Additionally, the table also stores both the minimum and maximum inventory quantities per SKU per month. See inside of the transformation for a more detailed step by step breakdown of the transformation. 

Create Table: IM_STOCK_HISTORY

This data job is where the final Stock History table is created. This involves two prior steps which are also explained in the data job comments. This table forms the core of the IM data model. 

The first is to create a temporary consumptions and replenishments table that aggregates that data to the material || plant || month level. 

Secondly, a temporary table is created to union the 4 prior temporary tables together. If you do not require one or more of those input tables, simply comment them out of the union function. 

Thirdly, the temporary unioned table is transformed, aggregating over SKUs per month.

Fourthly, the aggregated unioned table, the consumption and replenishment table and the material availability table are joined together, allowing the consumption and replenishment, and material availability history to be present alongside the various inventory fields. This table’s final primary keys are the Client Number, Plant Number, Material Number, and Eventtime. 

The final step is to update the eventtime of the table to the date the transformation is run on for the current month. This ensures that all historical months show the eventtime as the last day of the month, whereas the current month shows the date of the transformation. 

Create Temp Table: M_BOM

The objective of this transformation is to create a graph that stores information about Stock Keeping Units (SKUs) and their relation to other SKUs within the Bill of Materials (BOM). The table is structured in a graph format, whereby there is a node (case key), an output column and an input column, as well as additional metadata valuable for later analysis. For a full overview of this transformation, please refer to the presentation “Inventory Management Connector - Bill of Material Technical Overview” within the help space.

Create Temp: Table M_BOD

The objective of this transformation is to create a graph that stores information about SKUs and their relation to other SKUs within the Bill of Distribution (BOD). This allows for analysis into how material ‘could’ flow between plants, based on the special procurement types set up inside the SAP system. For a full overview of this transformation, and a detailed description of the graph logic, please refer to the presentation “Inventory Management Connector - Bill of Distribution Technical Overview” within the help space.

Create Table: IM_MDSC

The MDSC table is created by unioning (combining) the M_BOM table and the M_BOD table. The only addition to the union is the addition of an extra column ‘Source_Column’. This enables the user to filter the network inside the app based on the source column, for example to just display SKUs that are present in the BOM network. Clean UpWhere activities are removed from the IM_ACTIVTIES table that contain null activity names.

The transformations in this global job are used to simply create global views, leveraging the already transformed data in the 0.1 local jobs. These jobs can be extended to accommodate additional source systems, with the approach discussed in Multiple SAP Source Systems. All transformations in this pool apply the below structure:

The IM_MARC_TMP view is created based on the above description, whereby the IM_MARC table is used to create a temporary MARC view. This temporary view is used to filter views in other global transformations until global job 5.0, where the final IM_MARC view is created.

The transformations in this global job are used for the most part to import views from the source data pool and schema/s. These views are mostly the same, however sometimes quantity and currency conversions are performed here. For example, the transformation for VBAP below is doing the following:

The only transformation that deviates from the above approach (ie. importing from external data pools) is the ‘Create View: IM_SALES_COUNTRIES’. This transformation references the Country_Codes table that was created in Job 0.0, and filters it on the KNA1 table, ensuring that only customer countries are in scope. 

Create View: _CEL_O2C_ACTIVITIES

This transformation is used to create a view from the O2C activities table. The most important activities in this eventlog for this data model is the Goods Issue Date and the Sales Order Item Creation Date. If the O2C process is being set up specifically for the Inventory Management App, then those are the two activities that should be validated with priority. The app will still function if only those two activities are present in the O2C eventlog.

The transformations in this global job are used for the most part to import views from the source data pool and schema/s. These views are mostly the same, however sometimes quantity and currency conversions are performed here. See the information for Global Job 3.0 description for more information. 

Create View: _CEL_P2P_ACTIVITIES

This transformation is used to create a view from the P2P activities table. The most important activities in this eventlog for this data model is the Goods Receipt Date and the Purchase Order Item Creation Date. If the P2P process is being set up specifically for the Inventory Management App, then those are the two activities that should be validated with priority. The app will still function if only those two activities are present in the P2P eventlog.

The transformations in this global job are used for the most part to import views from the source data pool and schema/s. These views are mostly the same, however sometimes quantity and currency conversions are performed here. See the information for Global Job 3.0 description for more information. 

Create View: _CEL_PP_ACTIVITIES

This transformation is used to create a view from the PP activities table. The most important activities in this eventlog for this data model is the Actual Start Time and the Actual Finish Time. If the PP process is being set up specifically for the Inventory Management App, then those are the two activities that should be validated with priority. The app will still function if only those two activities are present in the PP eventlog.

This is the final data job of the IM Connector, and it is made up of three transformations that create the views IM_MARC, IM_MARA and IM_PLANT_COUNTRIES. 

Create Table: IM_MARC

This view creates the IM_MARC table by selecting all fields from IM_MARC_TMP, and then filtering the table down on only those materials present in the global views O2C_VBAP, P2P_EKPO, P2P_EBAN, PP_AFPO, _CEL_IM_ACTIVITIES, IM_PBED, IM_PLAF, IM_RESB and IM_STOCK_HISTORY. The purpose of this is to only load materials into the model where there is at least one transaction for that material somewhere in the system. This puts into perspective the actual materials present in the supply chain, which is especially important for operational personas such as Material Planners. 

Note 1: This transformation does take some time to run due to the multiple WHERE EXISTS statements present.

Note 2: If not all of the filter tables are present in the global jobs, for example if production is not considered at all, then the filter on PP_AFPO would need to be removed / commented out.

Create View: IM_MARA

This view is created by referencing the table created in the local job 0.1, and is then filtered on the IM_MARC table created in the previous step to only show active materials. 

Create View: IM_PLANT_COUNTRIES

This view is created by referencing the COUNTRY_CODES table created in job 0.0, and is then filtered on IM_MARC to only show plant countries where active materials are present.