Skip to main content

Celonis Product Documentation

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.

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:

  1. From the Celonis navigation bar, select Data > Objects and Events.

  2. Select Objects from the top navigation bar.

  3. Search or browse for the CustomerAccountDebitItem object.

  4. Click Add to add each of these new attributes:

    Field

    Type

    MasterDataPaymentTermKey

    STRING

    MasterDataPaymentDays1

    INTEGER

    MasterDataPaymentDays2

    INTEGER

    MasterDataPaymentDays3

    INTEGER

    MasterDataCashDiscountPercentage1

    FLOAT

    MasterDataCashDiscountPercentage2

    FLOAT

  5. 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:

  1. From the Celonis navigation bar, select Data > Objects and Events.

  2. Select Transformations from the top navigation bar.

  3. Locate the transformation for the CustomerAccountDebitItem object and click to edit.

  4. If you already have an extension script for attributes from this source system:

    1. Click the extension script to edit it.

    2. Copy and paste the appropriate set of SQL statements below into your script.

  5. If you do not already have an extension script for attributes from this source system:

    1. Click the plus sign next to the Attribute Scripts category in the left navigation.

    2. Enter a name for the script.

    3. Select the check button to create the script.

    4. Copy and paste the appropriate set of SQL statements below into your script.

  6. Click Save Script to save and validate your script.

  7. Click Preview Script to verify the script against a sample of data from your source system.

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