Skip to main content

Celonis Product Documentation

1.7 Data Transformations

This section details the reasoning behind different transformations in the AR connector, and what to consider when implementing. The transformations should be run in the order of the prefix and in the order that follows. Please refer to the Data Pool Overview for more details on each job.

Following are the transformations in jobs A1 - AR Collection Full Load Job, A2 - AR Collection Delta Daily Job, and A3 - AR Collection Delta Hourly Job.

  1. Create Table : BKPF Union (full): Creates an Invoice Header table (BKPF) by considering Open Invoice and Cleared invoice till configured Fiscal year - GJAHR (fiscalYears) and Document Enter in System - CPUDT (startDate)

  2. Create Table - BSEG Union (full): Creates an Invoice Line table (BSEG) by taking data from the BSID and BSAD tables and unioning them. We will consider Open Invoice and Cleared invoice till configured Fiscal year - GJAHR (fiscalYears) and Document Enter in System - CPUDT (startDate).

  3. Create Table - Transaction (full): Creates a Transaction table with a generic name to store the data from the BSEG and BKPF tables created from Step 1 and Step 2 transformation.

  4. Insert Record - Transaction (Invoice Header) (full): Inserts data into the Transaction table from the BSEG and BKPF tables created during Step 1 and Step 2 transformation.

  5. Create Table - Account (full): Creates an Account table with a generic name to store the data from the KNA1 and the KNB1 (Customer + Company) data extracted from SAP.

  6. Insert Records - Account (full): Inserts Customer data from the KNA1 and KNB1 tables in SAP.

  7. Create Table - Contact (full): Creates a generic table called Contact that is used to store Customer contact details.

  8. Insert Records - Contact (full): Inserting contact details from the KNVK table in SAP.

  9. Create Table - Account Contact Relationship (ACR) (full): Creates an ACR (Account Contact Relationship) table to store the mapping between Account and Contact.

  10. Insert Records - ACR (full): Inserts the mapping between the Account and Contact tables created in Steps 6 and 8. For example, if any contact is associated with the customer, then ACR will contain the AccountId and ContactId of that customer.

  11. Delete Records - ACR (full): If the Account and Contact mapping was previously added, then the mapping will be removed now that Contact is not associated with the Customer.

  12. Create Table - Billing (full): Creates a table to store billing items.

  13. Insert Records - Billing (full): Inserts billing data from VBRP and VBRK table of SAP.

  14. Create Temp Tables: Currency Conversion (full): Converts all date fields (such as GDATU from Inverted date) to the actual date format of the TCURR, TCURX, or TCURF table and then creates the TCURR_CC, TCURX_CC, TCURF_CC table.

  15. Create Table - Cases (full): Creates a table to store Case data.

  16. Insert Record - Cases (full): Inserts data from the Transaction table into the Case table.

  17. Create Table - Transaction Activity (full): Creates a table to store Transaction Activity. Will be used for process analytics.

  18. Add Transaction Activity - Invoice Created (full): Captures Invoice creation activity based on the TRANSACTION.INVOICE_DATE field.

  19. Add Transaction Activity - Due Date passed (full): Captures Invoice Due Date past activity based on the TRANSACTION.DUE_DATE field.

  20. Add Transaction Activity - Cash Discount Due Date Passed (full): Captures Invoice Activity where the Cash Discount Due Date has passed.

  21. Add Transaction Activity - Enter in SAP (full): Captures activity when an Invoice is entered in SAP based on the TRANSACTION.SYSTEM_CREATE_DATE field.

  22. Add Transaction Activity - Change Payment Terms (full): Captures the following activities at invoice level from this transformation:

    1. Change Payment Method

    2. Change Payment Term

    3. Change Baseline Date

    4. Change Item Text

    5. Cash Discount Percentage 1

    6. Cash Discount Percentage 2

    7. Cash discount days 1

    8. Cash discount days 2

    9. Cash discount days 3

    10. Change Dunning Level

  23. Add Transaction Activity - Cancel Invoice (full): Captures Invoice cancel activity.

  24. Add Transaction Activity - Dunning Blocks (full): Captures activity at the Invoice level for the following events:

    1. Set Dunning Block

    2. Change Dunning Block

    3. Remove Dunning Block

  25. Add Transaction Activity - Receive Other Payments (full): Captures activity at the invoice level for receiving different types of payment including:

    1. Receive Residual Items

    2. Clear Down Payment

    3. Receive Credit Memo

    4. Receive Payment

    5. Receive Supplement

    6. Receive Prepayment

    7. Receive Follow-On Document

  26. Add Transaction Activity - Clear Accounting Document (full): Captures the clearing activity of an invoice.

  27. Add Transaction Activity - Overdue Notices (full): Captures the following activities of a Sent Overdue Notice for all invoices.

    1. Send first Overdue Notice

    2. Send second Overdue Notice

    3. Send third Overdue Notice

    4. Send fourth Overdue Notice

    5. Send fifth Overdue Notice

    6. Send sixth Overdue Notice

    7. Send seventh Overdue Notice

    8. Send eighth Overdue Notice

    9. Send ninth Overdue Notice

  28. Add Transaction Activity - Clear Invoice (full): Captures activities related to when an invoice was cleared.

  29. Update Accounts : Average DPD (full): Calculates the Average DPD at the account level based on Open items.

  30. Update Transaction : DPD (full): Updates Transactions DPD every day.