Payment Terms Optimizer - Setting up app-specific object types, attributes and relationships

Celonis Product Documentation

Payment Terms Optimizer - Setting up app-specific object types, attributes and relationships

For the object-centric Payment Terms Optimizer app for Accounts Receivable, you’ll need to create the custom object type MasterPaymentTerms, add the transformation for it, and extend the Celonis catalog Accounts Receivable perspective to add it. You’ll also need to add custom attributes and relationships to the Celonis object types CustomerAccountDebitItem and SalesOrderItem, with transformations for them.

You can automate the manual setup process described in this guide using the our Module for Advanced Deployments. After running the Module for Advanced Deployments, we recommend that you cross-check with this guide to validate that all object types, transformations, and the perspective were created or updated correctly.

Before you begin

Check that your extractions include the required tables and columns to populate the new object type and attributes. Here’s the complete list of table and field requirements for an SAP ECC source system:

Table	Column
BKPF	BUDAT, AWKEY
VBKD	MANDT, VBELN, KUNRG, VKORG, VTWEG, SPART
KNVV	MANDT, VBELN, POSNR, ZTERM
KNB1	MANDT, KUNNR, VKORG, VTWEG, SPART, ZTERM
KNA1	MANDT, KUNNR, BUKRS, ZTERM
KNA1	STCEG
T052	All
T052U	All
TVZBT	All
CDPOS	Extraction filter = FNAME IN ('ZTERM')

Enable and configure the Accounts Receivable process for SAP from the Celonis catalog.

Load the object-centric data model with your data, and validate that it’s all working in the standard Accounts Receivable perspective.
Publish to the production environment.

Important

The transformations in this guide are built for an SAP ECC source system. If you need transformations for another source system, ask your Celonis point of contact.

Here’s how to set up the app-specific items. If you want to replace the manual procedure with the automated Module for Advanced Deployments, run the module now. Afterwards, check through the manual procedure and make sure everything described in it is now available in your object-centric data model and data pool.

From the Celonis navigation bar, select Data > Data Integration, then select the data pool where you’re working with objects and events. Select Data Jobs in the data pool.

Select each data job in each scope, and add to each the transformation “Customer Master - Payment Term Changes”.

--- Step 1: Get all change log for KNVV ZTERM
DROP TABLE IF EXISTS TMP_PT_MD_CHANGES_KNVV;

CREATE TABLE TMP_PT_MD_CHANGES_KNVV AS
  (
  SELECT
  "CDPOS"."MANDANT"
  ,"CDPOS"."FNAME"
  ,"CDPOS"."TABNAME"
  ,"CDPOS"."TABKEY"
  ,"CDPOS"."VALUE_OLD"
  ,"CDPOS"."VALUE_NEW"
  ,CAST("CDHDR"."UDATE" AS DATE) + CAST("CDHDR"."UTIME" AS TIME) AS "EVENTTIME"
  ,"CDHDR"."CHANGENR"
  ,"CDHDR"."TCODE"
  ,"CDPOS"."OBJECTCLAS"
  ,"CDPOS"."OBJECTID"
  ,"CDHDR"."USERNAME"
  FROM "CDHDR" CDHDR
  INNER JOIN CDPOS CDPOS
  ON 1=1
  AND "CDPOS"."MANDANT" = "CDHDR"."MANDANT"
  AND "CDPOS"."OBJECTCLAS" = "CDHDR"."OBJECTCLAS"
  AND "CDPOS"."OBJECTID" = "CDHDR"."OBJECTID"
  AND "CDPOS"."CHANGENR" = "CDHDR"."CHANGENR"
  AND "CDPOS"."CHNGIND" = 'U'
  AND "CDHDR"."UDATE" IS NOT NULL
  AND "CDPOS"."FNAME" IN ('ZTERM')
  AND "CDPOS"."TABNAME" IN ('KNVV')
);


--- Step 2: Get all change logs for KNVV ZTERM

DROP TABLE IF EXISTS MD_PT_CHANGES_KNVV;

CREATE TABLE MD_PT_CHANGES_KNVV
AS
(
--- Get Zterm periods for KNVV ZTERM
SELECT
--- Historic changes
TABKEY,
FNAME,
VALUE_OLD "ZTERM",
COALESCE(LAG(EVENTTIME) OVER (PARTITION BY TABKEY ORDER BY EVENTTIME),'1953-01-01 00:00:00') "FROM_TIME"
,EVENTTIME "TO_TIME",
CHANGENR
FROM TMP_PT_MD_CHANGES_KNVV

UNION
--- Pot. drop duplicates for the latest change

SELECT
--- Get Latest change
TABKEY,FNAME,VALUE_NEW "ZTERM",EVENTTIME "FROM_TIME",NOW() + INTERVAL '1 year' "TO_TIME",'9999999999' "CHANGENR"  --- Adding 1 year to ensure it works
  FROM
    (
    SELECT * FROM (
    SELECT *,ROW_NUMBER() OVER (PARTITION BY TABKEY ORDER BY EVENTTIME DESC) RN
    FROM TMP_PT_MD_CHANGES_KNVV
    ORDER BY EVENTTIME
  ) TMP_PT_MD_CHANGES_KNVV_ORDERED
  WHERE RN =1
  ) TMP_PT_MD_CHANGES_KNVV_LATEST

);

--SELECT * FROM MD_PT_CHANGES_KNVV

--- Step 3: Get all change log for KNB1 ZTERM

DROP TABLE IF EXISTS TMP_PT_MD_CHANGES_KNB1;

CREATE TABLE "TMP_PT_MD_CHANGES_KNB1" AS (
 SELECT
 "CDPOS"."MANDANT"
 ,"CDPOS"."FNAME"
 ,"CDPOS"."TABNAME"
 ,"CDPOS"."TABKEY"
 ,"CDPOS"."VALUE_OLD"
 ,"CDPOS"."VALUE_NEW"
 ,CAST("CDHDR"."UDATE" AS DATE) + CAST("CDHDR"."UTIME" AS TIME) AS "EVENTTIME"
 ,"CDHDR"."CHANGENR"
 ,"CDHDR"."TCODE"
 ,"CDPOS"."OBJECTCLAS"
 ,"CDPOS"."OBJECTID"
 ,"CDHDR"."USERNAME"
FROM "CDHDR" CDHDR
INNER JOIN CDPOS CDPOS
ON 1=1
AND "CDPOS"."MANDANT" = "CDHDR"."MANDANT"
AND "CDPOS"."OBJECTCLAS" = "CDHDR"."OBJECTCLAS"
AND "CDPOS"."OBJECTID" = "CDHDR"."OBJECTID"
AND "CDPOS"."CHANGENR" = "CDHDR"."CHANGENR"
AND "CDPOS"."CHNGIND" = 'U'
--AND "CDPOS"."VALUE_OLD" IS NOT NULL
AND "CDHDR"."UDATE" IS NOT NULL
AND "CDPOS"."FNAME" IN ('ZTERM')
AND "CDPOS"."TABNAME" IN ('KNB1')
);


--- Step 4: Get all change logs for KNB1 ZTERM

DROP TABLE IF EXISTS MD_PT_CHANGES_KNB1;

CREATE TABLE  MD_PT_CHANGES_KNB1
AS
(
--- Get Zterm periods for KNB1 ZTERM
SELECT
--- Historic changes
TABKEY,
FNAME,
VALUE_OLD "ZTERM",
COALESCE(LAG(EVENTTIME) OVER (PARTITION BY TABKEY ORDER BY EVENTTIME),'1953-01-01 00:00:00') "FROM_TIME"
,EVENTTIME "TO_TIME",
CHANGENR
FROM TMP_PT_MD_CHANGES_KNB1

UNION
--- Pot. drop duplicates for the latest change

SELECT
--- Get Latest change
TABKEY,FNAME,VALUE_NEW "ZTERM",EVENTTIME "FROM_TIME",NOW() + INTERVAL '1 year' "TO_TIME",'9999999999' "CHANGENR"  --- Adding 1 year to ensure it works
  FROM
    (
    SELECT * FROM (
    SELECT *,ROW_NUMBER() OVER (PARTITION BY TABKEY ORDER BY EVENTTIME DESC) RN
    FROM TMP_PT_MD_CHANGES_KNB1
    ORDER BY EVENTTIME
  ) TMP_PT_MD_CHANGES_KNB1_ORDERED
  WHERE RN =1
  ) TMP_PT_MD_CHANGES_KNB1_LATEST

)

From the Celonis navigation bar, select Data > Objects and Events, then select Objects in the top navigation bar.

Create the MasterPaymentTerms object type. Here’s how:

Click the Create button on the list of objects. Select Model from scratch.
Name the new object type MasterPaymentTerms (with no spaces).
Add the description “Master Payment Terms”.
Add the tag Accounts Receivable from the Catalog Processes category, and the tag Master Data from the Metadata category.

Click Add attribute to add each of these attributes, selecting the appropriate data type from the Data type dropdown:

Attribute	Data type
ID	String
DueDateDayFixed	Long integer
DueDateMonthsForward	Long integer
DueDateDays	Long integer
DateLimit	Long integer
PaymentTermCode	String
BaselineDateDayFixed	Long integer
BaselineDateMonthsForward	Long integer
DiscountPercentage1	Floating point
DiscountPercentage2	Floating point
DiscountDays1	Long integer
DiscountDays2	Long integer
DiscountMonthsForward1	Long integer
DiscountMonthsForward2	Long integer
DiscountDayFixed1	Long integer
DiscountDayFixed2	Long integer
BaselineDateType	String
TextExplanation	String

Click Save to create the MasterPaymentTerms object type.

Creating custom object types and custom event types has more details about creating custom object types.

Add the transformation for the new custom object type MasterPaymentTerms. Here’s how:

In the pane for the object type MasterPaymentTerms, select Transformations.
In the transformation editor, click Add transformation.

Paste this SQL script into the SQL editor, replacing the generated suggestion. Remember to create a parameter for your source system, or hard-code it in the transformation:

SELECT
    <%=sourceSystem%>  || 'PaymentTerm_' ||  "T052"."MANDT" || "T052"."ZTERM" || "T052"."ZTAGG" AS "ID",
    "T052"."ZTERM"
    CAST("T052"."ZTAGG" AS INT)                        AS "DateLimit",
    CAST("T052"."ZMONA" AS INT)                        AS "BaselineDateMonthsForward",
    CAST("T052"."ZFAEL" AS INT)                        AS "BaselineDateDayFixed",
    CASE
        WHEN "T052"."ZDART" = 'B' THEN 'Document date'
        WHEN "T052"."ZDART" = 'C' THEN 'Entry date'
        WHEN "T052"."ZDART" = 'D' THEN 'Posting date'
        WHEN "T052"."ZDART" IS NULL THEN NULL
        ELSE 'Other'
        END
    CAST("T052"."ZPRZ1" AS FLOAT)                      AS "DiscountPercentage1",
    CAST("T052"."ZPRZ2" AS FLOAT)                      AS "DiscountPercentage2",
    CASE
        WHEN CAST("T052"."ZPRZ1" AS FLOAT) > 0
            THEN CAST("T052"."ZTAG1" AS INT)
        ELSE 0
        END                                            AS "DiscountDays1",
    CASE
        WHEN CAST("T052"."ZPRZ2" AS FLOAT) > 0
            THEN CAST("T052"."ZTAG2" AS INT)
        ELSE 0
        END                                            AS "DiscountDays2",
    CASE
        WHEN CAST("T052"."ZPRZ1" AS FLOAT) > 0
            THEN CAST("T052"."ZSMN1" AS INT)
        ELSE 0
        END                                            AS "DiscountMonthsForward1",
    CASE
        WHEN CAST("T052"."ZPRZ2" AS FLOAT) > 0
            THEN CAST("T052"."ZSMN2" AS INT)
        ELSE 0
        END                                            AS "DiscountMonthsForward2",
    CASE
        WHEN CAST("T052"."ZPRZ1" AS FLOAT) > 0
            THEN CAST("T052"."ZSTG1" AS INT)
        ELSE 0
        END                                            AS "DiscountDayFixed1",
    CASE
        WHEN CAST("T052"."ZPRZ2" AS FLOAT) > 0
            THEN CAST("T052"."ZSTG2" AS INT)
        ELSE 0
        END                                            AS "DiscountDayFixed2",
    CASE
        WHEN CAST("T052"."ZTAG2" AS INT) = 0 AND    
            CAST("T052"."ZSMN2" AS INT) = 0 AND
            CAST("T052"."ZSTG2" AS INT) = 0
            THEN CAST("T052"."ZTAG1" AS INT)
        WHEN CAST("T052"."ZTAG3" AS INT) = 0 AND    
            CAST("T052"."ZSMN3" AS INT) = 0 AND
            CAST("T052"."ZSTG3" AS INT) = 0
            THEN CAST("T052"."ZTAG2" AS INT)
        ELSE CAST("T052"."ZTAG3" AS INT)
        END                                           AS "DueDateDays",
    CASE
        WHEN CAST("T052"."ZTAG2" AS INT) = 0 AND    
            CAST("T052"."ZSMN2" AS INT) = 0 AND
            CAST("T052"."ZSTG2" AS INT) = 0
            THEN CAST("T052"."ZSMN1" AS INT)
        WHEN CAST("T052"."ZTAG3" AS INT) = 0 AND    
            CAST("T052"."ZSMN3" AS INT) = 0 AND
            CAST("T052"."ZSTG3" AS INT) = 0
            THEN CAST("T052"."ZSMN2" AS INT)
        ELSE CAST("T052"."ZSMN3" AS INT)
        END                                           AS "DueDateMonthsForward",
    CASE
        WHEN CAST("T052"."ZTAG2" AS INT) = 0 AND    
            CAST("T052"."ZSMN2" AS INT) = 0 AND
            CAST("T052"."ZSTG2" AS INT) = 0
            THEN CAST("T052"."ZSTG1" AS INT)
        WHEN CAST("T052"."ZTAG3" AS INT) = 0 AND    
            CAST("T052"."ZSMN3" AS INT) = 0 AND
            CAST("T052"."ZSTG3" AS INT) = 0
            THEN CAST("T052"."ZSTG2" AS INT)
        ELSE CAST("T052"."ZSTG3" AS INT)
        END                                           AS "DueDateDayFixed",
     COALESCE("T052U"."TEXT1", "TVZBT"."VTEXT")       AS "TextExplanation"
FROM "T052" AS "T052"
         LEFT JOIN "T052U" AS "T052U"
                   ON "T052U"."MANDT" = "T052"."MANDT"
                       AND "T052U"."ZTERM" = "T052"."ZTERM"
                       AND "T052U"."ZTAGG" = "T052"."ZTAGG"
                       AND "T052U"."SPRAS" = 'E'
         LEFT JOIN "TVZBT" AS "TVZBT"
                   ON "TVZBT"."MANDT" = "T052"."MANDT"
                       AND "TVZBT"."ZTERM" = "T052"."ZTERM"
                       AND "TVZBT"."SPRAS" = 'E'

Click Save to save and validate the transformation.

Creating custom transformations has more details about creating custom transformations and the SQL editor.

Extend the Celonis object type CustomerAccountDebitItem to add custom attributes, and add the transformation script for it. Here’s how:

Browse or search to find the Celonis object type CustomerAccountDebitItem in the list of object types, and select it.
Select Object details.

Click Add to add each of these attributes:

Object	Type
MasterDataPaymentTermCode	String
MasterPaymentTermsSource	String
PostingDate	Datetime
SalesOrganization	String
DistributionChannel	String
Division	String
MasterPaymentTermsValidity	String
MasterPaymentTermsValidUntil	Datetime

Click Save to apply the extension to the CustomerAccountDebitItem object type.
In the pane for the CustomerAccountDebitItem object type, select Transformations, then click the name of the transformation you’re using for the object type.
Click the + icon next to the folder Custom Attributes to create a new transformation script, and name it PaymentTerms.

Paste this SQL script into the SQL editor, replacing the generated suggestion:

SELECT
    <%=sourceSystem%>  || 'CustomerAccountDebitItem_' || "BSEG"."MANDT" || "BSEG"."BUKRS" || "BSEG"."BELNR" || "BSEG"."GJAHR"
        || "BSEG"."BUZEI"                                                          AS "ID",
    "VBRK"."VKORG"                                                                 AS "SalesOrganization",
    "VBRK"."VTWEG"                                                                 AS "DistributionChannel",
    "VBRK"."SPART"                                                                 AS "Division",
    CAST ("BKPF"."BUDAT" AS TIMESTAMP)                                             AS "PostingDate",  
    CASE
        WHEN "CHG_KNVV_SPART"."ZTERM" IS NOT NULL OR "KNVV_SPART"."ZTERM" IS NOT NULL THEN 'Division/distribution channel'
        WHEN "CHG_KNVV_00"."ZTERM" IS NOT NULL OR "KNVV_00"."ZTERM" IS NOT NULL THEN 'Sales organization'
        WHEN "CHG_KNB1"."ZTERM" IS NOT NULL OR "KNB1"."ZTERM" IS NOT NULL THEN 'Company code'
        ELSE NULL
        END                                                                        AS "MasterPaymentTermsSource",
    COALESCE("CHG_KNVV_SPART"."ZTERM", "KNVV_SPART"."ZTERM",
            "CHG_KNVV_00"."ZTERM", "KNVV_00"."ZTERM",
            "CHG_KNB1"."ZTERM", "KNB1"."ZTERM")                                    AS "MasterDataPaymentTermCode",  
    CASE
        WHEN "CHG_KNVV_SPART"."ZTERM" IS NOT NULL THEN 'Outdated'
        WHEN "KNVV_SPART"."ZTERM" IS NOT NULL THEN 'Up-to-date'
        WHEN "CHG_KNVV_00"."ZTERM" IS NOT NULL THEN 'Outdated'
        WHEN "KNVV_00"."ZTERM" IS NOT NULL THEN 'Up-to-date'
        WHEN "CHG_KNB1"."ZTERM" IS NOT NULL THEN 'Outdated'
        WHEN "KNB1"."ZTERM" IS NOT NULL THEN 'Up-to-date'
        ELSE NULL
        END                                                                        AS "MasterPaymentTermsValidity",
    CASE
        WHEN "CHG_KNVV_SPART"."ZTERM" IS NOT NULL THEN CAST("CHG_KNVV_SPART"."TO_TIME" AS TIMESTAMP)
        WHEN "KNVV_SPART"."ZTERM" IS NOT NULL THEN NULL
        WHEN "CHG_KNVV_00"."ZTERM" IS NOT NULL THEN CAST("CHG_KNVV_00"."TO_TIME" AS TIMESTAMP)
        WHEN "KNVV_00"."ZTERM" IS NOT NULL THEN NULL
        WHEN "CHG_KNB1"."ZTERM" IS NOT NULL THEN CAST("CHG_KNB1"."TO_TIME" AS TIMESTAMP)
        WHEN "KNB1"."ZTERM" IS NOT NULL THEN NULL
        ELSE NULL
        END                                                                        AS "MasterPaymentTermsValidUntil"


FROM "BSEG" AS "BSEG"
         LEFT JOIN "BKPF" AS "BKPF"
                   ON "BSEG"."MANDT" = "BKPF"."MANDT"
                       AND "BSEG"."BUKRS" = "BKPF"."BUKRS"
                       AND "BSEG"."BELNR" = "BKPF"."BELNR"
                       AND "BSEG"."GJAHR" = "BKPF"."GJAHR"
         LEFT JOIN "VBRK" AS "VBRK"
                   ON "BKPF"."MANDT" = "VBRK"."MANDT"
                       AND "BKPF"."AWKEY" = "VBRK"."VBELN"
         LEFT JOIN "KNVV" AS "KNVV_SPART"
                   ON "VBRK"."MANDT" = "KNVV_SPART"."MANDT"
                       AND "VBRK"."KUNRG" = "KNVV_SPART"."KUNNR"
                       AND "VBRK"."VKORG" = "KNVV_SPART"."VKORG"
                       AND "VBRK"."SPART" = "KNVV_SPART"."SPART"
                       AND "VBRK"."VTWEG" = "KNVV_SPART"."VTWEG"
    -- Incorporating historical changes of Division level Sales Org Customer Master
         LEFT JOIN "MD_PT_CHANGES_KNVV" AS "CHG_KNVV_SPART"
                   ON "CHG_KNVV_SPART"."TABKEY" = "KNVV_SPART"."MANDT"||"KNVV_SPART"."KUNNR"||"KNVV_SPART"."VKORG"||"KNVV_SPART"."VTWEG"||"KNVV_SPART"."SPART"
                       AND CAST("BKPF"."BLDAT" AS DATE) < CAST("CHG_KNVV_SPART"."TO_TIME" AS DATE)
                       AND CAST("BKPF"."BLDAT" AS DATE) >= CAST("CHG_KNVV_SPART"."FROM_TIME" AS DATE)
         LEFT JOIN "KNVV" as "KNVV_00"
                   ON "VBRK"."MANDT" = "KNVV_00"."MANDT"
                       AND "VBRK"."KUNRG" = "KNVV_00"."KUNNR"
                       AND "VBRK"."VKORG" = "KNVV_00"."VKORG"
                       AND "KNVV_00"."SPART" = '00'
                       AND "KNVV_00"."VTWEG" = '00'
    -- Incorporating historical changes of Cross-divisional Sales Org Customer Master
         LEFT JOIN "MD_PT_CHANGES_KNVV" AS "CHG_KNVV_00"
                   ON "CHG_KNVV_00"."TABKEY" = "KNVV_00"."MANDT"||"KNVV_00"."KUNNR"||"KNVV_00"."VKORG"||"KNVV_00"."VTWEG"||"KNVV_00"."SPART"
                       AND CAST("BKPF"."BLDAT" AS DATE) < CAST("CHG_KNVV_00"."TO_TIME" AS DATE)
                       AND CAST("BKPF"."BLDAT" AS DATE) >= CAST("CHG_KNVV_00"."FROM_TIME" AS DATE)
         LEFT JOIN "KNB1" as "KNB1"
                   ON "BSEG"."MANDT" = "KNB1"."MANDT"
                       AND "BSEG"."KUNNR" = "KNB1"."KUNNR"
                       AND "BSEG"."BUKRS" = "KNB1"."BUKRS"
    -- Incorporating historical changes of Company Code Customer Master
         LEFT JOIN "MD_PT_CHANGES_KNB1" AS "CHG_KNB1"
                   ON "CHG_KNB1"."TABKEY" = "KNB1"."MANDT"||"KNB1"."KUNNR"||"KNB1"."BUKRS"
                       AND CAST("BKPF"."BLDAT" AS DATE) < CAST("CHG_KNB1"."TO_TIME" AS DATE)
                       AND CAST("BKPF"."BLDAT" AS DATE) >= CAST("CHG_KNB1"."FROM_TIME" AS DATE)
WHERE "BSEG"."MANDT" IS NOT NULL
  AND "BKPF"."MANDT" IS NOT NULL
  AND "BSEG"."KOART" = 'D'
  AND "BSEG"."SHKZG" = 'S'

Click Save to save and validate the transformation.

Extending Celonis object types and event types has more details about extending a Celonis object type, and Creating custom transformations has more details about creating custom transformations and the SQL editor.

Create an object to object relationship between the new custom object type MasterPaymentTerms and the Celonis object type CustomerAccountDebitItem. Here’s how:
- In the pane for the Celonis object type CustomerAccountDebitItem, select Relationships to objects.
- Click Add, and add an outgoing relationship to the custom object type MasterPaymentTerms. Make this a many to many (m:n) relationship. Use the suggested name for the relationship.
- Click Save to create the object to object relationship.

Create the transformation for the custom relationship between the Celonis object type CustomerAccountDebitItem and the new custom object type MasterPaymentTerms. Here’s how:

In the Objects page of the Objects and Events environment, browse or search to find the Celonis object type CustomerAccountDebitItem in the list of object types, and select it.
In the pane for the CustomerAccountDebitItem object type, select Transformations, then click the name of the transformation you’re using for the object type.
Click the + icon next to the folder Custom Attributes to create a new transformation script, and name it MasterPaymentTerms.

Paste this SQL script into the SQL editor, replacing the generated suggestion:

WITH "CTE_MainData" AS (
SELECT
    <%=sourceSystem%>  || 'CustomerAccountDebitItem_' || "BSEG"."MANDT" || "BSEG"."BUKRS" || "BSEG"."BELNR" || "BSEG"."GJAHR"
        || "BSEG"."BUZEI"                                                          AS "ID",


    <%=sourceSystem%>  || 'PaymentTerm_' ||
    "T052"."MANDT" || "T052"."ZTERM" || "T052"."ZTAGG"                             AS "MasterPaymentTerms"


             FROM "BSEG" AS "BSEG"
                      INNER JOIN "T052" AS "T052"
                                ON "BSEG"."MANDT" = "T052"."MANDT"
                                    AND "BSEG"."ZTERM" = "T052"."ZTERM"
             WHERE "BSEG"."MANDT" IS NOT NULL
                AND "BSEG"."KOART" = 'D'
                AND "BSEG"."SHKZG" = 'S'
),




"CTE_CompanyCodeData" AS (


SELECT
    <%=sourceSystem%>  || 'CustomerAccountDebitItem_' || "BSEG"."MANDT" || "BSEG"."BUKRS" || "BSEG"."BELNR" || "BSEG"."GJAHR"
        || "BSEG"."BUZEI"                                                          AS "ID",


    <%=sourceSystem%>  || 'PaymentTerm_' ||
    "T052"."MANDT" || "T052"."ZTERM" || "T052"."ZTAGG"                             AS "MasterPaymentTerms"


             FROM "BSEG" AS "BSEG"
                      LEFT JOIN "KNB1" as "KNB1"
                                ON "BSEG"."MANDT" = "KNB1"."MANDT"
                                    AND "BSEG"."KUNNR" = "KNB1"."KUNNR"
                                    AND "BSEG"."BUKRS" = "KNB1"."BUKRS"        
                      INNER JOIN "T052" AS "T052"
                                ON "KNB1"."MANDT" = "T052"."MANDT"
                                    AND "KNB1"."ZTERM" = "T052"."ZTERM"
             WHERE "BSEG"."MANDT" IS NOT NULL
                AND "BSEG"."KOART" = 'D'
                AND "BSEG"."SHKZG" = 'S'
),




"CTE_MasterData" AS (


SELECT
    <%=sourceSystem%>  || 'CustomerAccountDebitItem_' || "BSEG"."MANDT" || "BSEG"."BUKRS" || "BSEG"."BELNR" || "BSEG"."GJAHR"
        || "BSEG"."BUZEI"                                                          AS "ID",


    <%=sourceSystem%>  || 'PaymentTerm_' ||
    "T052"."MANDT" || "T052"."ZTERM" || "T052"."ZTAGG"                             AS "MasterPaymentTerms"


FROM "BSEG" AS "BSEG"
         LEFT JOIN "BKPF" AS "BKPF"
                   ON "BSEG"."MANDT" = "BKPF"."MANDT"
                       AND "BSEG"."BUKRS" = "BKPF"."BUKRS"
                       AND "BSEG"."BELNR" = "BKPF"."BELNR"
                       AND "BSEG"."GJAHR" = "BKPF"."GJAHR"
         LEFT JOIN "USR02" AS "USR02"
                   ON "BKPF"."MANDT" = "USR02"."MANDT"
                       AND "BKPF"."USNAM" = "USR02"."BNAME"
         LEFT JOIN "VBRK" AS "VBRK"
                   ON "BKPF"."MANDT" = "VBRK"."MANDT"
                       AND "BKPF"."AWKEY" = "VBRK"."VBELN"
         LEFT JOIN "KNVV" AS "KNVV_SPART"
                   ON "VBRK"."MANDT" = "KNVV_SPART"."MANDT"
                       AND "VBRK"."KUNRG" = "KNVV_SPART"."KUNNR"
                       AND "VBRK"."VKORG" = "KNVV_SPART"."VKORG"
                       AND "VBRK"."SPART" = "KNVV_SPART"."SPART"
                       AND "VBRK"."VTWEG" = "KNVV_SPART"."VTWEG"
    -- Incorporating historical changes of Division level Sales Org Customer Master
         LEFT JOIN "MD_PT_CHANGES_KNVV" AS "CHG_KNVV_SPART"
                   ON "CHG_KNVV_SPART"."TABKEY" = "KNVV_SPART"."MANDT"||"KNVV_SPART"."KUNNR"||"KNVV_SPART"."VKORG"||"KNVV_SPART"."VTWEG"||"KNVV_SPART"."SPART"
                       AND CAST("BKPF"."BLDAT" AS DATE) < CAST("CHG_KNVV_SPART"."TO_TIME" AS DATE)
                       AND CAST("BKPF"."BLDAT" AS DATE) >= CAST("CHG_KNVV_SPART"."FROM_TIME" AS DATE)
         LEFT JOIN "KNVV" as "KNVV_00"
                   ON "VBRK"."MANDT" = "KNVV_00"."MANDT"
                       AND "VBRK"."KUNRG" = "KNVV_00"."KUNNR"
                       AND "VBRK"."VKORG" = "KNVV_00"."VKORG"
                       AND "KNVV_00"."SPART" = '00'
                       AND "KNVV_00"."VTWEG" = '00'
    -- Incorporating historical changes of Cross-divisional Sales Org Customer Master
         LEFT JOIN "MD_PT_CHANGES_KNVV" AS "CHG_KNVV_00"
                   ON "CHG_KNVV_00"."TABKEY" = "KNVV_00"."MANDT"||"KNVV_00"."KUNNR"||"KNVV_00"."VKORG"||"KNVV_00"."VTWEG"||"KNVV_00"."SPART"
                       AND CAST("BKPF"."BLDAT" AS DATE) < CAST("CHG_KNVV_00"."TO_TIME" AS DATE)
                       AND CAST("BKPF"."BLDAT" AS DATE) >= CAST("CHG_KNVV_00"."FROM_TIME" AS DATE)
         LEFT JOIN "KNB1" as "KNB1"
                   ON "BSEG"."MANDT" = "KNB1"."MANDT"
                       AND "BSEG"."KUNNR" = "KNB1"."KUNNR"
                       AND "BSEG"."BUKRS" = "KNB1"."BUKRS"
    -- Incorporating historical changes of Company Code Customer Master
         LEFT JOIN "MD_PT_CHANGES_KNB1" AS "CHG_KNB1"
                   ON "CHG_KNB1"."TABKEY" = "KNB1"."MANDT"||"KNB1"."KUNNR"||"KNB1"."BUKRS"
                       AND CAST("BKPF"."BLDAT" AS DATE) < CAST("CHG_KNB1"."TO_TIME" AS DATE)
                       AND CAST("BKPF"."BLDAT" AS DATE) >= CAST("CHG_KNB1"."FROM_TIME" AS DATE)
         INNER JOIN "T052" AS "T052"
                   ON COALESCE ("CHG_KNVV_SPART"."ZTERM", "KNVV_SPART"."ZTERM", "CHG_KNVV_00"."ZTERM", "KNVV_00"."ZTERM", "CHG_KNB1"."ZTERM", "KNB1"."ZTERM") = "T052"."ZTERM"
                       AND COALESCE (SUBSTRING("CHG_KNVV_SPART"."TABKEY", 1,3), "KNVV_SPART"."MANDT", SUBSTRING("CHG_KNVV_00"."TABKEY", 1,3), "KNVV_00"."MANDT", SUBSTRING("CHG_KNB1"."TABKEY", 1,3), "KNB1"."MANDT") = "T052"."MANDT"


WHERE "BSEG"."MANDT" IS NOT NULL
  AND "BKPF"."MANDT" IS NOT NULL
  AND "BSEG"."KOART" = 'D'
  AND "BSEG"."SHKZG" = 'S'
),


"CTE_PaymentTerms" AS (SELECT *
                    FROM "CTE_MainData"
                    UNION
                    SELECT *
                    FROM "CTE_CompanyCodeData"
                    UNION
                    SELECT *
                    FROM "CTE_MasterData"
                    )


SELECT "PaymentTerms"."ID"                              AS "ID",
       "PaymentTerms"."MasterPaymentTerms"              AS "MasterPaymentTerms"
FROM "CTE_PaymentTerms" AS "PaymentTerms"

Click Save to save and validate the transformation.

Extend the Celonis object type SalesOrderItem to add custom attributes, and add the transformation script for it. Here’s how:

In the Objects page of the Objects and Events environment., browse or search to find the Celonis object type SalesOrderItem in the list of object types, and select it.
Select Object details.
Click Add to add each of these attributes:
Object
Type
PaymentTermsCode
String
MasterDataPaymentTermCode
String
MasterPaymentTermsSource
String
Click Save to apply the extension to the SalesOrderItem object type.
In the pane for the SalesOrderItem object type, select Transformations, then click the name of the transformation you’re using for the object type.
Click the + icon next to the folder Custom Attributes to create a new transformation script, and name it MasterPaymentTerms.

Object	Type
PaymentTermsCode	String
MasterDataPaymentTermCode	String
MasterPaymentTermsSource	String

Paste this SQL script into the SQL editor, replacing the generated suggestion:

SELECT <%=sourceSystem%>  || 'SalesOrderItem_' || "VBAP"."MANDT" || "VBAP"."VBELN" || "VBAP"."POSNR"              AS "ID",
    COALESCE("VBKD_LINE"."ZTERM", "VBKD_HEADER"."ZTERM")                                                          AS "PaymentTermsCode",
    CASE
        WHEN "KNVV"."MANDT" IS NOT NULL THEN 'Division/distribution channel'
        WHEN "KNVV_00"."MANDT" IS NOT NULL THEN 'Sales organization'
        WHEN "KNB1"."MANDT" IS NOT NULL THEN 'Company code'
        ELSE NULL
        END                                                                                                       AS "MasterPaymentTermsSource",
    COALESCE("KNVV"."ZTERM", "KNVV_00"."ZTERM", "KNB1"."ZTERM")                                                   AS "MasterDataPaymentTermCode"
FROM "VBAP" AS "VBAP"
         LEFT JOIN "VBAK" AS "VBAK"
                   ON "VBAP"."MANDT" = "VBAK"."MANDT"
                       AND "VBAP"."VBELN" = "VBAK"."VBELN"
         LEFT JOIN "VBKD" AS "VBKD_LINE"
                   ON "VBAP"."MANDT" = "VBKD_LINE"."MANDT"
                       AND "VBAP"."VBELN" = "VBKD_LINE"."VBELN"
                       AND "VBAP"."POSNR" = "VBKD_LINE"."POSNR"
         LEFT JOIN "VBKD" AS "VBKD_HEADER"
                   ON "VBAP"."MANDT" = "VBKD_HEADER"."MANDT"
                          AND "VBAP"."VBELN" = "VBKD_HEADER"."VBELN"
                          AND "VBKD_HEADER"."POSNR" = '000000'
         LEFT JOIN "KNVV" AS "KNVV"
                   ON "VBAK"."MANDT" = "KNVV"."MANDT"
                       AND "VBAK"."KUNNR" = "KNVV"."KUNNR"
                       AND "VBAK"."VKORG" = "KNVV"."VKORG"
                       AND "VBAK"."SPART" = "KNVV"."SPART"
                       AND "VBAK"."VTWEG" = "KNVV"."VTWEG"
         LEFT JOIN "KNVV" AS "KNVV_00"
                   ON "VBAK"."MANDT" = "KNVV_00"."MANDT"
                       AND "VBAK"."KUNNR" = "KNVV_00"."KUNNR"
                       AND "VBAK"."VKORG" = "KNVV_00"."VKORG"
                       AND "KNVV_00"."SPART" = '00'
                       AND "KNVV_00"."VTWEG" = '00'
         LEFT JOIN "KNB1" AS "KNB1"
                   ON "VBAK"."MANDT" = "KNB1"."MANDT"
                       AND "VBAK"."KUNNR" = "KNB1"."KUNNR"
                       AND "VBAK"."BUKRS_VF" = "KNB1"."BUKRS"
WHERE "VBAP"."MANDT" IS NOT NULL
  AND "VBAK"."VBTYP" IN ('C', 'I')

Click Save to save and validate the transformation.

Create an object to object relationship between the new custom object type MasterPaymentTerms and the Celonis object type SalesOrderItem. Here’s how:
- In the pane for the Celonis object type SalesOrderItem, select Relationships to objects.
- Click Add, and add an outgoing relationship to the custom object type MasterPaymentTerms. Make this a many to many (m:n) relationship. Use the suggested name for the relationship.
- Click Save to create the object to object relationship.

Create the transformation for the custom relationship between the Celonis object type SalesOrderItem and the new custom object type MasterPaymentTerms. Here’s how:

In the Objects page of the Objects and Events environment, browse or search to find the Celonis object type SalesOrderItem in the list of object types, and select it.
In the pane for the SalesOrderItem object type, select Transformations, then click the name of the transformation you’re using for the object type.
Click the + icon next to the folder Custom Attributes to create a new transformation script, and name it MasterPaymentTerms.

Paste this SQL script into the SQL editor, replacing the generated suggestion:

WITH "CTE_MainData"
         AS (SELECT "VBAP"."MANDT" || "VBAP"."VBELN" || "VBAP"."POSNR" AS "ID",
                    "T052"."MANDT" || "T052"."ZTERM" || "T052"."ZTAGG" AS "MasterPaymentTerms_ID"
             FROM "VBAP" AS "VBAP"
                      LEFT JOIN "VBAK" AS "VBAK"
                                ON "VBAP"."MANDT" = "VBAK"."MANDT"
                                    AND "VBAP"."VBELN" = "VBAK"."VBELN"
                      LEFT JOIN "VBKD" AS "VBKD_LINE"
                                ON "VBAP"."MANDT" = "VBKD_LINE"."MANDT"
                                    AND "VBAP"."VBELN" = "VBKD_LINE"."VBELN"
                                    AND "VBKD_LINE"."POSNR" = "VBAP"."POSNR" -- Line-level match
                      LEFT JOIN "VBKD" AS VBKD_HEADER
                                ON "VBAP"."MANDT" = "VBKD_HEADER"."MANDT"
                                    AND "VBAP"."VBELN" = "VBKD_HEADER"."VBELN"
                                    AND "VBKD_HEADER"."POSNR" = '000000' -- Header level match
                      LEFT JOIN "T052" AS "T052"
                                ON ("VBKD_LINE"."MANDT" = "T052"."MANDT"
                                    OR "VBKD_HEADER"."MANDT" = "T052"."MANDT")
                                    AND ("VBKD_LINE"."ZTERM" = "T052"."ZTERM"
                                        OR "VBKD_HEADER"."ZTERM" = "T052"."ZTERM")
             WHERE "VBAP"."MANDT" IS NOT NULL
               AND "VBAK"."VBTYP" IN ('C', 'I')
               AND "T052"."MANDT" IS NOT NULL),
     "CTE_MasterData"
         AS (SELECT "VBAP"."MANDT" || "VBAP"."VBELN" || "VBAP"."POSNR" AS "ID",
                    "T052"."MANDT" || "T052"."ZTERM" || "T052"."ZTAGG" AS "MasterPaymentTerms_ID"
             FROM "VBAP" AS "VBAP"
                      LEFT JOIN "VBAK" AS "VBAK"
                                ON "VBAP"."MANDT" = "VBAK"."MANDT"
                                    AND "VBAP"."VBELN" = "VBAK"."VBELN"
                      LEFT JOIN "KNVV" AS "KNVV"
                                ON "VBAK"."MANDT" = "KNVV"."MANDT"
                                    AND "VBAK"."KUNNR" = "KNVV"."KUNNR"
                                    AND "VBAK"."VKORG" = "KNVV"."VKORG"
                                    AND "VBAK"."SPART" = "KNVV"."SPART"
                                    AND "VBAK"."VTWEG" = "KNVV"."VTWEG"
                      LEFT JOIN "KNVV" AS "KNVV_00"
                                ON "VBAK"."MANDT" = "KNVV_00"."MANDT"
                                    AND "VBAK"."KUNNR" = "KNVV_00"."KUNNR"
                                    AND "VBAK"."VKORG" = "KNVV_00"."VKORG"
                                    AND "KNVV_00"."SPART" = '00'
                                    AND "KNVV_00"."VTWEG" = '00'
                      LEFT JOIN "KNB1" AS "KNB1"
                                ON "VBAK"."MANDT" = "KNB1"."MANDT"
                                    AND "VBAK"."KUNNR" = "KNB1"."KUNNR"
                                    AND "VBAK"."BUKRS_VF" = "KNB1"."BUKRS"
                      LEFT JOIN "T052" AS "T052"
                                ON ("KNVV"."MANDT" = "T052"."MANDT"
                                    OR "KNVV_00"."MANDT" = "T052"."MANDT"
                                    OR "KNB1"."MANDT" = "T052"."MANDT")
                                    AND ("KNVV"."ZTERM" = "T052"."ZTERM"
                                        OR "KNVV_00"."ZTERM" = "T052"."ZTERM"
                                        OR "KNB1"."ZTERM" = "T052"."ZTERM")
             WHERE "VBAP"."MANDT" IS NOT NULL
               AND "VBAK"."VBTYP" IN ('C', 'I')
               AND "T052"."MANDT" IS NOT NULL),
     "CTE_PaymentTerms" AS (SELECT *
                            FROM "CTE_MainData"
                            UNION
                            SELECT *
                            FROM "CTE_MasterData")
SELECT <%=sourceSystem%>  || 'SalesOrderItem_' || "PaymentTerms"."ID" AS "ID",
       <%=sourceSystem%>  || 'PaymentTerm_'    || "PaymentTerms"."MasterPaymentTerms_ID"   AS "MasterPaymentTerms"
FROM "CTE_PaymentTerms" AS "PaymentTerms"

Click Save to save and validate the transformation.

Extend the Celonis object type Customer to add custom attributes, and add the transformation script for it. Here’s how:
- In the Objects page of the Objects and Events environment, browse or search for the Celonis object type Customer in the list of object types and select it.
- Select Object details.
- Click Add to add each of these attributes: VATCode (data type String)
- Click Save to apply the extension to the Customer object type.
- In the pane for the Customer object type, select Transformations, then click the name of the transformation you’re using for the object type.
- Click the + icon next to the folder Custom Attributes to create a new transformation script, and name it VAT.
- Paste this SQL script into the SQL editor, replacing the generated suggestion:
```
SELECT <%=sourceSystem%>  || 'Customer_' || "KNA1"."MANDT" || "KNA1"."KUNNR" AS "ID",
    "KNA1"."STCEG"                                                           AS "VATCode"
FROM "KNA1" AS "KNA1"
WHERE "KNA1"."MANDT" IS NOT NULL
```
Extend the Celonis object type SalesOrder to add custom attributes, and add the transformation script for it. Here’s how:
- In the Objects page of the Objects and Events environment, browse or search for the Celonis object type SalesOrder in the list of object types and select it.
- Select Object details.
- Click Add to add each of these attributes: Division (data type String)
- Click Save to apply the extension to the SalesOrder object type.
- In the pane for the SalesOrder object type, select Transformations, then click the name of the transformation you’re using for the object type.
- Click the + icon next to the folder Custom Attributes to create a new transformation script, and name it VBAK.
- Paste this SQL script into the SQL editor, replacing the generated suggestion:
```
SELECT <%=sourceSystem%>  || 'SalesOrder_' || "VBAK"."MANDT" || "VBAK"."VBELN" AS "ID",
    "VBAK"."SPART"                                                             AS "Division"
FROM "VBAK" AS "VBAK"
WHERE "VBAK"."MANDT" IS NOT NULL
  AND "VBAK"."VBTYP" IN ('C', 'I')
```
Extend the Celonis catalog perspective for the Accounts Receivable process to add the MasterPaymentTerms object. You’ll add it twice, once as a linked object and once as an embedded object. The perspective already contains the other objects, so their new attributes will be added automatically. Here’s how to extend the perspective:
- Go back to the Objects and Events environment, and select Perspectives in the top navigation bar. Find the Celonis-supplied Accounts Receivable perspective (perspective_celonis_AccountsReceivable).
- Click the Extend button, or go to the context menu (the three vertical dots) for the perspective and select Extend.
- Type a name for the extension - we recommend “PaymentTermsOptimizer” - and click Next.
- Search in the Object list for the custom object type MasterPaymentTerms. Select it to see its details pane.
- Select Linked objects in the sidebar.
- Click Add and start typing the name of the CustomerAccountDebitItem object type, then click to select it.
- Select the checkbox button for the MasterPaymentTerms relationship between the two object types.
- Now search in the Object list for the Celonis object type SalesOrderItem. Select it to see its details pane.
- Select Embedded objects in the sidebar.
- Click Add and start typing the name of the MasterPaymentTerms object type, then click to select it.
- Select the radio button for the MasterPaymentTerms relationship between the two object types.
- Click Save to save the extension.
Select Publish > Publish to development to publish all your changes to the development environment or development and production.
Follow the instructions in Running transformations to run test:ocpm-data-job to create objects and events in the development environment or ocpm-data-job to create objects and events in the production environment. Check that there are no errors.
In the data pool where you’re working with objects and events, carry out a full load of the data model for the extended perspective PaymentTermsOptimizer (or your chosen name for the custom perspective).

Was this helpful?

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

Celonis Product Documentation

Payment Terms Optimizer - Setting up app-specific object types, attributes and relationships

Before you begin

Search results