Payout Records - Lab

Lab Info

Learning Outcomes

At the end of the Lab, you should be able to:

• implement creation of PayoutRecords

• configure Payout Record metadata

Figure 1. Sneakpeek of the generated Payout Records per CustomerId

Pre-requisites

In order to be able to complete this laboratory exercise, please complete the prerequisites before you proceed.

Labs

This lab expects, that you already have in your partition a solution of the preceding labs:

• Rebates Configuration - Lab 2

If you need to work on this lab without finishing the preceding ones first, you can deploy the solutions of the pre-requisite labs provided in a ZIP file attached to it.

User Story / Requirements

As a Sales Manager, I want to calculate the rebates on a SoldTo(CustomerId) level, so that I have fine-grained rebates ready as soon as we approve the rebate amount (in Rebate Records).

Details

What should be calculated:

• Rebate Record - will calculate the summary of the rebate on the level entered by the user (likely some set of customers)

• Payout Record - will calculate the rebate amount for each SoldTo(CustomerId). The rebate amount for particular customer will be proportional to the rebate amount of the group, based on the sizes of the Actual Sales.

Acceptance Criteria

• when the user recalculates the Rebate Record, the Payout Records will be created (or updated, if they already existed)

• the Payout Record has fields and values:

  • type - "Payout"

  • name - combination of Rebate Record ID and Customer ID

  • startDate, endDate, payoutDate - the same as the ones used for the Rebate Record

  • Customer Id - so we can easily distinguish for which customer is the amount

  • Actual Rebate - the amount of rebate for the particular customer

  • Currency - in which currency is the rebate amount

  • Rebate % - this is the percentual amount of rebate - the same number as on the Rebate Record

Implementation Steps

Step: Create the new Rebate logic and Condition Type

We will utilize the logic from pre-requisite Lab and build on top of it.

1. Prepare the new Rebate Logic:

   1. In Studio, make a copy of the BonusOnSales logic

   2. edit the file logic.json of this new logic and change of this copy to "BonusOnSalesWithPayoutRecords". This action should not only change the name of logic inside of the Json file, but also the name of the folder with logic.

      

   3. Deploy the logic to your partition

2. prepare a new Rebate Type to use this logic:

   1. In Pricefx UI, navigate to Rebates  Condition Types

   2. create a new Condition Type "BonusOnSalesWithPayoutRecords" which uses as Pricing Logic the new logic BonusOnSalesWithPayoutRecords

Step: Prepare testing data

To be able to test, we need at least one Rebate Agreement with one line, which will generate at least one Rebate Record.

1. create new Rebate Agreement, for example:

   1. For header

      • Description: "Agreement with Payout Records"

      • Start Date: 2021-01-01

      • End Date: 2021-12-31

      • Calculation Date: 2021-05-31

      • Payout Date: 2022-04-01

   2. Items - add new rebate item of the new type BonusOnSalesWithPayoutRecords you just created

      • Customer(s): APPO AG (Customer Group)

      • Product(s): Meatball (Business Unit)

      • Rebate %: 1

   3. Recalculate the Rebate Agreement

   4. Save the Rebate Agreement

   5. Find the Rebate Record which was generated from this Agreement and note down its ID, you will need it later.

Step: Add the code to generate the Payout Records

Now you will modify this newly added Logic to also generate the Payout Records.

1. In Studio, open your logic BonusOnSalesWithPayoutRecords

2. Add the code, which queries Actual Sales per Customer

   1. Add a new Element "ActualSalesPerCustomer" right before the element "ActualSales". The code will be:

      def dmCtx = api.getDatamartContext()
      def dmTable = dmCtx.getDatamart("Transaction")
      
      def query = dmCtx.newQuery(dmTable, true)                   
      
                  .select("CustomerId", "CustomerId")             
                  .select("SUM(InvoicePrice)", "TotalRevenue")
      
                  .where(out.CustomerGroup)
                  .where(out.ProductGroup)
      
                  .where(
                          Filter.greaterOrEqual("InvoiceDate", out.StartDate),
                          Filter.lessOrEqual("InvoiceDate", out.EndDateOrToday)
                  )
      
      def result = dmCtx.executeQuery(query)
      return result?.data?.collectEntries { row ->
          [(row.CustomerId): row.TotalRevenue]                    
      }

      use Roll Up / Group Bygroup Actual Sales by CustomerIdcreate a Map with data: CustomerId → ActualSales

   2. This Element ActualSalesPerCustomer will be used in all contexts, so either keep Calculation Contexts Agreement, Agreement ReadOnly and RebateRecord all blanks or all crossed

   3. to avoid querying the data 2x for the same reason, modify the code of existing element ActualSales, so that it only summarizes the data found in element ActualSalesPerCustomer. So the code of ActualSales element will be:

      return (out.ActualSalesByCustomer as Map)?.values()?.sum()

3. Add the code, which generates the Payout Records:

   1. Add new Element "CreatePayoutRecords" to the end of the logic, with code:

      if (out.RebatePct == null) return
      
      def actualSales = out.ActualSalesByCustomer as Map
      def customerIds = actualSales.keySet()
      
      def rebateRecord = api.currentItem()
      
      
      payoutRecords.bulkLoadMode()                                    
      
      customerIds.each { customerId ->
      
          def rebateValue = (actualSales[customerId] ?: 0.0) * out.RebatePct
      
          def payoutRecord = [
                  startDate : rebateRecord.startDate,
                  endDate   : rebateRecord.endDate,
                  payoutDate: rebateRecord.payoutDate,
                  attribute1: customerId,
                  attribute2: rebateValue,
                  attribute3: "EUR",
                  attribute4: out.RebatePct,
          ]
      
          payoutRecords.add(                                          
                  "Payout",
                  "${rebateRecord.uniqueName}-${customerId}" as String,
                  payoutRecord
          )
      
      }
      
      
      payoutRecords.flush()                                           

      start the Bulk Mode, which helps with speed when generating many thousands of PayoutRecords. If you generate lower amounts, you might not use it and simply commenting out this line should keep the functionality.create the new Payout Record. If the record already existed, it will be updated.use flush() to ensure, that your new records are fully stored in tables. This is a must have for bulk-mode, but not needed for non-bulk mode. Since it’s ok to use it also in non-bulk mode, you can simply use it all the times, once you’re done with generation of the Payout Records.

   2. This Element "CreatePayoutRecords" is used ONLY when recalculating Rebate Records, so ensure, that only the Calculation Context "RebateRecord" is crossed

4. Test the Logic in Studio

   1. in Studio you must test, if your logic is executed without failure, but you cannot test the "writing" of the data to the tables - the writing operation works only in non-testing execution.

   2. setup the input parameters

      

      • Context - ensure to select REBATERECORD

      • RebateRecordName - type a name of existing Rebate Record - e.g. the one you created for testing purposes at the beginning.

      • startDate, endDate - choose a timeframe, where you know you have data in your Datamart. You can use the same you used while creating the Agreement.

      • calculation Date - will be used as Target Date. In the code, the Target Date is used to find the period of time for which we search the data.

      • targetDate - not used, the value does not matter, because it will be overriden by calculationDate

   3. as Studio has the possibility to save the parameters, we strongly encourage you to use it, so that you will not loose the values, when you close the Logic or Studio. In the screenshot you can notice the Load Preset Test RR.

   4. Verify, if the logic passed ok, and the values of the ActualSalesByCustomer, if you got realistis numbers.

      

   5. you can also add api.trace() to element CreatePayoutRecords to visualize, if the PayoutRecords are asked to be created.

      1. Remember, they will not be physically created in the Test mode.

5. Test the logic in Pricefx UI

   1. You will test it on the Rebate Record detail page. So navigate to Rebates  Rebate Records

   2. click on your Rebate Record’s ID (it’s a link), to open its detail page

   3. click on Calculate to recalculate the Rebate Record - since the same logic also creates the Payout Records, they will be created

      you do not need to Save the calculated result to "save" the Payout Records. They’re created already during the execution of the Logic.

      Please note, that the Save cause Calculation and only then saves. So if the users would use manually this detail page for recalculations, they must be trained to properly understand, how buttons Calculate and Save work.

      Remember, that the Rebate Records are typically calculated by the Rebate Calculation job, where the logic executes ones (and stores the Payout Records to database and prepares the values for Rebate Record), and then the Rebate Record values are persisted too.

   4. review the PayoutRecords list - navigate to Rebates  Payout Records and review the data in the columns

      

      1. verify, that you have more customers and they have different rebates. Of course, this depends on the data set, date-ranges, etc., so validate it with respect to your dataset.

Step: Customize the columns of Payout Records table

You’re almost done, the last thing is to modify the names of the columns and how their values are formatted on the screen for the end-user.

1. Rename and Customize columns of the Payout Records

   1. in Pricefx UI, navigate to Rebates  Payout Records

   2. for columns attribute1..4 use Rename and Customize columns to set up properties of the columns

      AttributeNameLabelTypeFormat Type

      attribute1

      CustomerId

      Customer Id

      String

      -

      attribute2

      ActualRebate

      Actual Rebate

      Real

      Money

      attribute3

      Currency

      Currency

      String

      -

      attribute4

      RebatePct

      Rebate %

      Real

      Percent

2. fetch definition into project

   1. in Studio, fetch the object Payout record attr (PYR), PayoutRecordAttribute to your project

References

Groovy API

• PayoutRecordManager

Documentation

• Payout Records

Knowledge Base

• PYR – Payout Record