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