Prerequisites for the object-centric Accounts Receivable Starter Kit
Before you set up the object-centric version of the Accounts Receivable Starter Kit, you’ll need to enable and configure the Accounts Receivable process, load the object-centric data model with your data, and check that the perspective_celonis_AccountsReceivable perspective is working properly.
For the steps to connect a source system to the OCPM Data Pool and create objects and events from your data, see Quickstart: Extract and transform your data into objects and events.
For instructions to work with perspectives, see Using objects and events for process mining.
You'll also need to create a field extension to the CustomerAccountDebitItem object type to add master data payment terms, and include the data in your extractions. Adding master data payment terms explains how.
If you need help with any of the Starter Kit’s prerequisites or setup steps, Support tells you how.
Adding master data payment terms
The following tables and columns are required for the transformation of master data payment terms. Ensure that they are available using the selected data connection.
Note
If the data requirements are not available, update the extractions in your dedicated extractions data pool. See Quickstart: Extract and transform your data into objects and events for more information.
SAP ECC
Table name | Column list |
|---|---|
KNVV | MANDT, KUNNR, VKORG, VTWEG, ZTERM |
T052 | ZTERM, ZTAGG, ZDART, ZFAEL, ZMONA, ZTAG1, ZPRZ1, ZTAG2, ZPRZ2, ZTAG3ZSMN1, ZSTG1, ZSTG2, ZSMN2, ZSMN3 |
VBRK | MANDT, VBELN, VKORG, VTWEG, KUNRG, SPART |
Oracle EBS
Table name | Column list |
|---|---|
RA_TERMS_LINES_DISCOUNTS | TERM_ID, DISCOUNT_PERCENT, DISCOUNT_DAYS, DISCOUNT_DAY_OF_MONTH, DISCOUNT_MONTHS_FORWARD |
RA_TERMS_B | TERM_ID, BASE_AMOUNT |
RA_TERMS_LINES | TERM_ID, SEQUENCE_NUM, DUE_DAYS, DUE_DAY_OF_MONTH, DUE_MONTHS_FORWARD |
HZ_CUST_ACCOUNTS | CUST_ACCOUNT_ID |
HZ_CUST_SITE_USES_ALL | SITE_USE_ID, PAYMENT_TERM_ID |
HZ_CUST_ACCT_SITES_ALL | CUST_ACCT_SITE_ID |
HZ_CUSTOMER_PROFILES | SITE_USE_ID, STANDARD_TERMS |
AR_PAYMENT_SCHEDULES_ALL | CUSTOMER_ID, CUSTOMER_SITE_USE_ID, TERMS_SEQUENCE_NUMBER, CLASS |
If the data requirements are not available, update the extractions in your dedicated extractions data pool. For more help, refer to Quickstart: Extract and transform your data into objects and events.
Update the CustomerAccountDebitItem object
Use the steps below to update the CustomerAccountDebitItem object to include extra fields for master data payment terms:
From the Celonis navigation bar, select Data > Objects and Events.
Select Objects from the top navigation bar.
Search or browse for the CustomerAccountDebitItem object.
Click Add to add each of these new attributes:
Field
Type
MasterDataPaymentTermKey
STRING
MasterDataPaymentDays1
INTEGER
MasterDataPaymentDays2
INTEGER
MasterDataPaymentDays3
INTEGER
MasterDataCashDiscountPercentage1
FLOAT
MasterDataCashDiscountPercentage2
FLOAT
Click Save to save the CustomerAccountDebitItem object.
For more help, refer to Extending Celonis object types and event types.
Create a custom transformation
Next, you will need to create a custom transformation to update the content of the new fields:
From the Celonis navigation bar, select Data > Objects and Events.
Select Transformations from the top navigation bar.
Locate the transformation for the CustomerAccountDebitItem object and click to edit.
If you already have an extension script for attributes from this source system:
Click the extension script to edit it.
Copy and paste the appropriate set of SQL statements below into your script.
If you do not already have an extension script for attributes from this source system:
Click the plus sign next to the Attribute Scripts category in the left navigation.
Enter a name for the script.
Select the check button to create the script.
Copy and paste the appropriate set of SQL statements below into your script.
Click Save Script to save and validate your script.
Click Preview Script to verify the script against a sample of data from your source system.
Click the Save button at the top right to save the transformation.
For more help, refer to Creating custom transformations.
Extension scripts
Copy and paste the appropriate set of SQL statements into your script:
WITH MasterDataTerms AS (
SELECT <%=sourceSystem%> || 'CustomerAccountDebitItem_' || B."MANDT" || B."BUKRS" || B."BELNR" || B."GJAHR" ||
B."BUZEI" AS "ID",
"T052MD"."ZTERM" AS "MasterDataPaymentTermKey",
CAST("T052MD"."ZTAG1" AS INT) AS "MasterDataPaymentDays1",
CAST("T052MD"."ZTAG2" AS INT) AS "MasterDataPaymentDays2",
CAST("T052MD"."ZTAG3" AS INT) AS "MasterDataPaymentDays3",
CAST("T052MD"."ZPRZ1" AS FLOAT) AS "MasterDataCashDiscountPercentage1",
CAST("T052MD"."ZPRZ2" AS FLOAT) AS "MasterDataCashDiscountPercentage2",
ROW_NUMBER() OVER (PARTITION BY B.BELNR, B.GJAHR, B.BUKRS, B.BUZEI, B.MANDT, T052MD.ZTERM ORDER BY T052MD.ZTAGG ASC) as NUM
FROM
"BSEG" B
LEFT JOIN "BKPF" AS "BKPF"
ON B."MANDT" = "BKPF"."MANDT"
AND B."BUKRS" = "BKPF"."BUKRS"
AND B."BELNR" = "BKPF"."BELNR"
AND B."GJAHR" = "BKPF"."GJAHR"
LEFT JOIN VBRK V ON 1=1
AND "BKPF"."MANDT" = V."MANDT"
AND "BKPF"."AWKEY" = V."VBELN"
LEFT JOIN KNVV K ON 1=1
AND V."MANDT" = K."MANDT"
AND V."KUNRG" = K."KUNNR"
AND V."VKORG" = K."VKORG"
AND V."SPART" = K."SPART"
AND V."VTWEG" = K."VTWEG"
LEFT JOIN KNVV as K_00 ON 1=1
AND V."MANDT" = K_00."MANDT"
AND V."KUNRG" = K_00."KUNNR"
AND V."VKORG" = K_00."VKORG"
AND K_00."SPART" = '00'
AND K_00."VTWEG" = '00'
LEFT JOIN KNB1 as K2 ON 1=1
AND B."MANDT" = K2."MANDT"
AND B."KUNNR" = K2."KUNNR"
AND B."BUKRS" = K2."BUKRS"
INNER JOIN "T052" AS T052MD ON 1=1
--- Joining on the most accurate source for MD
AND COALESCE (K."ZTERM", K_00."ZTERM", K2."ZTERM") = T052MD."ZTERM"
AND COALESCE (K."MANDT", K_00."MANDT", K2."MANDT") = T052MD."MANDT"
AND ( CAST(EXTRACT(DAY FROM "BKPF"."BLDAT") AS INT)
<= CAST("T052MD"."ZTAGG" AS INT) OR CAST("T052MD"."ZTAGG" AS INT) = 0)
WHERE
B."MANDT" IS NOT NULL
AND B."KOART" = 'D'
AND B."SHKZG" = 'S'
)
SELECT
ID,
MasterDataPaymentTermKey,
MasterDataPaymentDays1,
MasterDataPaymentDays2,
MasterDataPaymentDays3,
MasterDataCashDiscountPercentage1,
MasterDataCashDiscountPercentage2
FROM MasterDataTerms
WHERE NUM = 1
WITH DiscountTerms AS ( SELECT rtld.term_id, rtld.discount_percent, rtld.discount_days, rtld.discount_day_of_month, rtld.discount_months_forward, RANK() OVER (PARTITION BY TERM_ID,SEQUENCE_NUM ORDER BY discount_percent DESC) DiscountTermNumber FROM ra_terms_lines_discounts rtld ), FlattenedTerms AS ( SELECT RTB.TERM_ID, RTB.BASE_AMOUNT, RTL.SEQUENCE_NUM, RTL.DUE_DAYS as NetDueDays, RTL.DUE_DAY_OF_MONTH as NetFixedDayOfTheMonth, RTL.DUE_MONTHS_FORWARD as NetFixedMonthsForward, COALESCE(Term1.discount_days, Term2.discount_days, RTL.DUE_DAYS, 0.0) as Days1, COALESCE(Term1.discount_percent, Term2.discount_percent, 0.0 ) as Percentage1, CASE WHEN Term1.term_id IS NOT NULL THEN COALESCE(Term2.discount_days, RTL.DUE_DAYS) ELSE 0.0 END as Days2, CASE WHEN Term1.term_id IS NOT NULL THEN COALESCE(Term2.discount_percent, 0.0) ELSE 0.0 END as Percentage2, CASE WHEN Term1.term_id IS NOT NULL and Term2.term_id IS NOT NULL THEN RTL.DUE_DAYS ELSE 0.0 END as Days3, COALESCE(Term1.discount_day_of_month, Term2.discount_day_of_month, RTL.DUE_DAY_OF_MONTH, 0) as FixedDaysOfMonth1, COALESCE(Term1.discount_months_forward, Term2.discount_months_forward, RTL.DUE_MONTHS_FORWARD, 0) as FixedfMonthsForward1, CASE WHEN Term1.term_id IS NOT NULL THEN COALESCE(Term2.discount_day_of_month, RTL.DUE_DAY_OF_MONTH) ELSE 0 END as FixedDaysOfMonth2, CASE WHEN Term1.term_id IS NOT NULL THEN COALESCE(Term2.discount_months_forward, RTL.DUE_MONTHS_FORWARD) ELSE 0 END as FixedfMonthsForward2, CASE WHEN Term1.term_id IS NOT NULL and Term2.term_id IS NOT NULL THEN RTL.DUE_DAY_OF_MONTH ELSE 0 END as FixedDaysOfMonth3, CASE WHEN Term1.term_id IS NOT NULL and Term2.term_id IS NOT NULL THEN RTL.DUE_MONTHS_FORWARD ELSE 0 END as FixedfMonthsForward3 FROM "RA_TERMS_B" RTB INNER JOIN RA_TERMS_LINES RTL ON RTL.TERM_ID = RTB.TERM_ID LEFT JOIN DiscountTerms Term1 ON RTB.TERM_ID = Term1.TERM_ID AND Term1.DiscountTermNumber = 1 LEFT JOIN DiscountTerms Term2 ON RTB.TERM_ID = Term1.TERM_ID AND Term2.DiscountTermNumber = 2 ) SELECT PAYMENT_SCHEDULE_ID as ID, TERMS_SEQUENCE_NUMBER, COALESCE(SitesUses.PAYMENT_TERM_ID, CustomerProfile.STANDARD_TERMS, CustomerProfileCustomer.STANDARD_TERMS) AS MasterDataPaymentTermKey, ----- Master Data Term standard terms MD_TERMS.Days1 as MasterDataDays1, MD_TERMS.Days2 as MasterDataDays2, MD_TERMS.Days3 as MasterDataDays3, MD_TERMS.Percentage1 as MasterDataPecentage1, MD_TERMS.Percentage2 as MasterDataPecentage2, FROM AR_PAYMENT_SCHEDULES_ALL DebitItem LEFT JOIN HZ_CUST_ACCOUNTS Customers ON Customers.CUST_ACCOUNT_ID = DebitItem.CUSTOMER_ID LEFT JOIN HZ_CUST_SITE_USES_ALL SitesUses ON SitesUses.SITE_USE_ID = DebitItem.CUSTOMER_SITE_USE_ID LEFT JOIN HZ_CUST_ACCT_SITES_ALL Sites ON Sites.CUST_ACCT_SITE_ID = SitesUses.CUST_ACCT_SITE_ID LEFT JOIN HZ_CUSTOMER_PROFILES CustomerProfile ON SitesUses.SITE_USE_ID = CustomerProfile.SITE_USE_ID LEFT JOIN HZ_CUSTOMER_PROFILES CustomerProfileCustomer ON Customers.CUST_ACCOUNT_ID = CustomerProfileCustomer.CUST_ACCOUNT_ID AND CustomerProfileCustomer.SITE_USE_ID = NULL INNER JOIN FlattenedTerms AS MD_TERMS ON 1=1 AND COALESCE(SitesUses.PAYMENT_TERM_ID, CustomerProfile.STANDARD_TERMS, CustomerProfileCustomer.STANDARD_TERMS) = MD_TERMS.TERM_ID AND DebitItem.TERMS_SEQUENCE_NUMBER = MD_TERMS.SEQUENCE_NUM WHERE DebitItem.CLASS = 'INV';
Publish your changes
Select Publish > Publish to development to synchronize your changes to the development environment.
Follow the instructions in Running transformations to run the transformations and update the custom attributes for the CustomerAccountDebitItem object.