Skip to main content

Prerequisites for the Payment Terms Harmonizer app

Before you set up the object-centric version of the Payment Terms Harmonizer app, you’ll need to enable and configure the Procurement process from the Celonis catalog. Load the object-centric data model with your data, and validate that it’s all working in the standard Procurement perspective. Then publish it to the production environment.

To supply the Payment Terms Harmonizer app with information about the payment terms for your suppliers, you’ll need to create a custom object type, and add custom attributes to some Celonis object types. Then you’ll need to create an extension to the standard Procurement perspective, and use the extended perspective with the app. Extending the Procurement process with payment terms tells you how.

If you need to get help with any of the app’s prerequisites or setup steps, Support tells you how.

Extending the Procurement process with payment terms

To provide the Payment Terms Harmonizer app with information about payment terms, you’ll need to create the custom object type VendorMasterPurchasingOrg, add the transformation for it, and extend the Celonis catalog Procurement perspective to add it. You’ll also need to add custom attributes to the Celonis object types Vendor and Contract, with transformations for them.

Important

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

For an SAP ECC source system, check that your extractions include the following tables and columns to populate the new object type and attributes:

Table

Columns

LFM1

MANDT, EKORG, LIFNR, ZTERM, ERDAT, ERNAM

T052

ZPRZ1, ZPRZ2, ZTAG1, ZTAG2, ZTAG3, ZTERM

Here’s how to extend the Procurement process:

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

  2. Create the VendorMasterPurchasingOrg object type. Here’s how:

    1. Click the Create button on the list of objects. Select Model from scratch.

    2. Name the new object type VendorMasterPurchasingOrg (with no spaces).

    3. Add the description “Vendor Master for Purchasing Org”.

    4. Add the tag Procurement from the Catalog Processes category, and the tag Master Data from the Metadata category.

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

      • PurchasingOrganizationText (data type String)

      • PurchasingOrganization (data type String)

      • VendorPaymentDays1 (data type Long Integer)

      • VendorPaymentDays2 (data type Long Integer)

      • VendorPaymentDays3 (data type Long Integer)

      • VendorCashDiscountPercentage1 (data type Floating Point)

      • VendorCashDiscountPercentage2 (data type Floating Point)

      • SourceSystemInstance (data type String)

      • SourceSystemType (data type String)

      • PaymentTerms (data type String)

      • CreationTime (data type Datetime)

      • VendorNumber (data type String)

      • VendorName (data type String)

    6. Click Save to create the VendorMasterPurchasingOrg object type. Here’s how it should look:

      The VendorMasterPurchasingOrg object type with the list of attributes and data types given in the previous step.

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

  3. Create an object to object relationship between the new custom object type VendorMasterPurchasingOrg and the Celonis object type PurchaseOrder. Here’s how:

    1. In the pane for the object type VendorMasterPurchasingOrg, select Relationships to objects.

    2. Click Add, and add an incoming relationship to the Celonis object type PurchaseOrder. Make this a one to many (1:m) relationship with VendorMasterPurchasingOrg on the one side, and PurchaseOrder on the many side. Use the suggested name for the relationship.

    3. Click Save to create the object to object relationship.

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

    1. In the pane for the object type VendorMasterPurchasingOrg, select Transformations.

    2. In the transformation editor, click Add transformation.

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

      WITH "CTE_T052_Intermediate" AS (SELECT "LFM1"."MANDT",
                                      "LFM1"."EKORG",
                                      "LFM1"."LIFNR",
                                      "T052"."ZPRZ1",
                                      "T052"."ZPRZ2",
                                      "T052"."ZTAG1",
                                      "T052"."ZTAG2",
                                      "T052"."ZTAG3",
                                      "T052"."ZTERM",
                                      ROW_NUMBER()
                                      OVER (PARTITION BY "LFM1"."MANDT", "LFM1"."EKORG", "LFM1"."LIFNR","T052"."ZTERM"
                                          ORDER BY "T052"."ZTAGG" ASC) AS "NUM"
                               FROM "lfm1" AS "LFM1"
                                        LEFT JOIN "T052" AS "T052"
                                                  ON "LFM1"."MANDT" = "T052"."MANDT"
                                                      AND "LFM1"."ZTERM" = "T052"."ZTERM")


SELECT <%=sourceSystem%> || 'VendorMasterPurchasingOrg_' || "LFM1"."MANDT" || "LFM1"."LIFNR" || "LFM1"."EKORG" AS "ID",
  CAST("LFM1"."ERDAT" AS DATE) + CAST('00:00:01' AS TIME)                          AS "CreationTime",
  "LFM1"."ZTERM"                                                                   AS "PaymentTerms",
  <%=sourceSystem%> || 'Vendor_' || "LFM1"."MANDT" || "LFM1"."LIFNR"                                    AS "Vendor",
  'SAP'                                                                            AS "SourceSystemType",
  "LFM1"."MANDT"                                                                   AS "SourceSystemInstance",
  CAST("T052_Intermediate"."ZTAG3" AS BIGINT) AS "VendorPaymentDays3",
      CAST("T052_Intermediate"."ZTAG2" AS BIGINT) AS "VendorPaymentDays2",
      CAST("T052_Intermediate"."ZTAG1" AS BIGINT) AS "VendorPaymentDays1",
      "T052_Intermediate"."ZPRZ2"  AS "VendorCashDiscountPercentage2",
      "T052_Intermediate"."ZPRZ1" AS "VendorCashDiscountPercentage1",
    "LFM1"."EKORG" AS "PurchasingOrganization",
    "T024E"."EKOTX" AS "PurchasingOrganizationText",
    "LFM1"."LIFNR" AS "VendorNumber",
    "LFA1"."NAME1" AS "VendorName"


    FROM "lfm1" AS "LFM1"
     LEFT JOIN "LFA1"
       ON "LFM1"."MANDT" = "LFA1"."MANDT"
           AND "LFM1"."LIFNR" = "LFA1"."LIFNR"FROM "lfm1" AS "LFM1"
      LEFT JOIN "CTE_T052_Intermediate" AS "T052_Intermediate"
      ON "LFM1"."MANDT" = "T052_Intermediate"."MANDT"
          AND "LFM1"."EKORG" = "T052_Intermediate"."EKORG"
          AND  "LFM1"."LIFNR" =  "T052_Intermediate"."LIFNR"
          AND "LFM1"."ZTERM" = "T052_Intermediate"."ZTERM"
          AND "T052_Intermediate"."NUM" = 1
      LEFT JOIN "USR02" AS "USR02"
                 ON "LFM1"."MANDT" = "USR02"."MANDT"
                     AND "LFM1"."ERNAM" = "USR02"."BNAME"
      LEFT JOIN "T024E" AS "T024E"
                 ON "LFM1"."MANDT" = "T024E"."MANDT"
                     AND "LFM1"."EKORG" = "T024E"."EKORG"
      WHERE "LFM1"."MANDT" IS NOT NULL

    4. Click Save to save and validate the transformation.

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

  5. Create the transformation for the custom relationship between the Celonis object type PurchaseOrder and the new custom object type VendorMasterPurchasingOrg. Here’s how:

    1. In the Objects page of the Objects and Events environment, browse or search to find the Celonis object type PurchaseOrder in the list of object types, and select it.

    2. In the pane for the PurchaseOrder object type, select Transformations, then click the name of the transformation you’re using for the object type.

    3. Click the + icon next to the folder Custom Attributes to create a new transformation script, and name it VendorMasterPurchasingOrg.

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

      SELECT <%=sourceSystem%> || 'PurchaseOrder_' || "EKKO"."MANDT" || "EKKO"."EBELN"                             AS "ID",
<%=sourceSystem%> || 'VendorMasterPurchasingOrg_' || "EKKO"."MANDT" || "EKKO"."LIFNR" || "EKKO"."EKORG"  AS "VendorMasterPurchasingOrg"
FROM "EKKO" AS "EKKO"
WHERE "EKKO"."MANDT" IS NOT NULL
AND "EKKO"."BSTYP" = 'F'

    5. Click Save to save and validate the transformation.

  6. Extend the Celonis object type Vendor to add the VAT code as an attribute, and add the transformation script for it. Here’s how:

    1. In the Objects page of the Objects and Events environment., browse or search to find the Celonis object type Vendor in the list of object types, and select it.

    2. Select Object details.

    3. Click Add to add a new attribute, and add an attribute named VATCode (with no spaces) with the data type String.

    4. Click Save to apply the extension to the Vendor object type.

    5. In the pane for the Vendor object type, select Transformations, then click the name of the transformation you’re using for the object type.

    6. Click the + icon next to the folder Custom Attributes to create a new transformation script, and name it VATCode.

      paymenttermsharmonizer_editvendor.png

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

      SELECT <%=sourceSystem%> || 'Vendor_' || "LFA1"."MANDT" || "LFA1"."LIFNR"             AS "ID",
      "LFA1"."STCEG"                                            AS "VATCode"


FROM "LFA1" AS "LFA1"

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

  7. Extend the Celonis object type Contract to add the Payment Days and Cash Discount as attributes, and add the transformation script for it. Here’s how:

    1. Browse or search to find the Celonis object type Contract in the list of object types, and select it.

    2. Select Object details.

    3. Click Add to add each of these attributes:

      • PaymentDays1 (data type Long Integer)

      • PaymentDays2 (data type Long Integer)

      • PaymentDays3 (data type Long Integer)

      • CashDiscountPercentage1 (data type Floating Point)

      • CashDiscountPercentage2 (data type Floating Point)

    4. Click Save to apply the extension to the Contract object type.

    5. In the pane for the Contract object type, select Transformations, then click the name of the transformation you’re using for the object type.

    6. Click the + icon next to the folder Custom Attributes to create a new transformation script, and name it PaymentTerms.

      paymenttermsharmonizer_editcontract.png

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

      WITH "CTE_Changes" AS (
   SELECT "CDPOS"."MANDANT",
   "CDPOS"."TABKEY",
   "CDHDR"."UDATE",
   "CDHDR"."UTIME",
   "CDHDR"."USERNAME",
   ROW_NUMBER() OVER(PARTITION BY "CDPOS"."TABKEY" ORDER BY "CDHDR"."UDATE", "CDHDR"."UTIME" desc) as rn
   FROM "CDPOS" AS "CDPOS"
   LEFT JOIN "CDHDR" AS "CDHDR"
                  ON "CDHDR"."MANDANT" = "CDPOS"."MANDANT"
                      AND "CDHDR"."CHANGENR" = "CDPOS"."CHANGENR"
                      AND "CDHDR"."OBJECTCLAS" = "CDPOS"."OBJECTCLAS"
                      AND "CDHDR"."OBJECTID" = "CDPOS"."OBJECTID"
   WHERE "CDPOS"."OBJECTCLAS" = 'EINKBELEG'
   AND "CDPOS"."TABNAME" = 'EKKO'
   AND "CDPOS"."FNAME" = 'KEY'
   AND "CDPOS"."CHNGIND" = 'I')
SELECT <%=sourceSystem%> || 'Contract_' || "EKKO"."MANDT" || "EKKO"."EBELN"     AS "ID",
      "EKKO"."BUKRS"                                                           AS "CompanyCode",
      "EKKO"."ZBD1T"                                                                   AS "PaymentDays1",
      "EKKO"."ZBD2T"                                                                   AS "PaymentDays2",
      "EKKO"."ZBD3T"                                                                   AS "PaymentDays3",
      "EKKO"."ZBD1P"                                                                   AS "CashDiscountPercentage1",
      "EKKO"."ZBD2P"                                                                   AS "CashDiscountPercentage2"
FROM "EKKO" AS "EKKO"
        LEFT JOIN "CTE_Changes" AS "Changes"
                  ON "EKKO"."MANDT"= "Changes"."MANDANT"
                      AND "EKKO"."MANDT" || "EKKO"."EBELN" = "Changes"."TABKEY"
                      AND "Changes"."rn" = 1
        LEFT JOIN "USR02" AS "USR02"
                  ON "EKKO"."MANDT" = "USR02"."MANDT"
                      AND COALESCE("Changes"."USERNAME", "EKKO"."ERNAM") = "USR02"."BNAME"
WHERE "EKKO"."MANDT" IS NOT NULL
 AND "EKKO"."BSTYP" = 'K'

    8. Click Save to save and validate the transformation.

  8. Extend the Celonis catalog perspective for the Procurement process to add the VendorMasterPurchasingOrg object. The perspective already contains the other objects, so their new attributes will be added automatically. Here’s how to extend the perspective:

    1. Go back to the Objects and Events environment, and select Perspectives in the top navigation bar. Find the Celonis-supplied Procurement perspective (perspective_celonis_Procurement).

    2. Click the Extend button, or go to the context menu (the three vertical dots) for the perspective and select Extend.

    3. Type a name for the extension - we recommend “PaymentTermsHarmonizer” - and click Next.

    4. Search in the Object list for the VendorMasterPurchasingOrg object type. Select it to see its details pane.

    5. Select Linked objects in the sidebar.

    6. Click Add and start typing the name of the PurchaseOrder object type, then click to select it.

    7. Select the radio button for the VendorMasterPurchasingOrg relationship between the two object types.

    8. Click Save to save the extension.

  9. Select Publish > Publish to development to publish all your changes to the development environment.

  10. Follow the instructions in Running transformations to run test:ocpm-data-job to create objects and events in the development environment. Check that there are no errors.

  11. 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 PaymentTermsHarmonizer (or your chosen name for the custom perspective).