SAP Commissions New functions Introduced - Welcomes Groovy Function for Custom Queries

[yogananda]

In this post I'd like to present you with new functions got introduced which you can evaluate the deseried output via Groovy language with supporting one of import package.

You can use the Groovy Function to build a Custom Query Function. This feature helps build logic for input parameters, for example, formatting input data.

What is Groovy?

Groovy is an object-oriented programming language that is both static and dynamic. It can be used both as a programming language and as a scripting language for Java. Groovy is a pleasant language to develop in, as it reduces huge amounts of boilerplate code including no semi colons, no getters/setters, type inference, null safety, elvis operators and much, much more.

That’s not to say you’re not permitted to use Java notation if you so choose, though. Interestingly, most Java code will compile in groovy so it has a low bar for entry into the language.  It’s become a mature choice that developers trust when Java verbosity hurts and dynamic typing isn’t an issue.

---

Practical examples for Evaluating Functions through Formulas

Currently, the usage of Groovy Function is supported only for importing below packages only.

import java.text.*;

import java.math.*; 

Not Supported packages

import groovy.json.JsonSlurper

import java.util.HashMap;

import groovy.transform.Field;

import groovy.json.JsonOutput;

import groovy.xml.XmlUtil;

The groovy function must have a main method with the signature def main($1, $2, $3, $4, $5, $6, $7, $8, $9)

Following is an example of a Groovy Function to format the input parameter to a text string:

import java.text.*

 def main($1, $2, $3, $4, $5, $6, $7, $8, $9) { 

  DecimalFormat df = new DecimalFormat("#,###.00");

  if ($1 == 'TEXT') {

    if ($3[0] == null){

      return null

    } else {

      def s = $3[0].toString()

      return s.substring(0, s.indexOf('.'))

    }

  } else if ($1 == 'DATE'){

    if ($7 == null){

      return '01/01/2200'

    } else {

      SimpleDateFormat sm = new SimpleDateFormat('MM/dd/yy')

      return sm.format($7)

    }

  } else if ($1 == 'QUANTITY'){

    if ($3[0] == null){

      return '0.00'

    } else {

      return df.format($3[0].setScale(2, java.math.RoundingMode.CEILING))

    }

  } else if ($1 == 'MONEY'){

    if ($3[0] == null){

      return '$0.00'

    } else {

      return '$' + df.format($3[0].setScale(2, java.math.RoundingMode.CEILING))

    }

  } else if ($1 == 'PERCENT'){

    if ($3[0] == null){

      return '0.00%'

    } else {

      return $3[0].multiply(new BigDecimal(100)).setScale(2, java.math.RoundingMode.CEILING).toString()+'%'

    }

  } else {

    return ''

  }

}

You can add this function to the CS_PlugInQuery with the insert SQL as follows:

INSERT INTO CS_PLUGINQUERY (tenantId, name, query) VALUES ( '0504', 'FORMAT MONEY',

'import java.text.*

 def main($1, $2, $3, $4, $5, $6, $7, $8, $9) { 

  DecimalFormat df = new DecimalFormat("#,###.00");

  if ($1 == ''TEXT'') {

    if ($3 == null){

      return null

    } else {

      def s = $3[0].toString()

      return s.substring(0, s.indexOf(''.''))

    }

  } else if ($1 == ''DATE''){

    if ($7 == null){

      return ''01/01/2200''

    } else {

      SimpleDateFormat sm = new SimpleDateFormat(''MM/dd/yy'')

      return sm.format($7)

    }

  } else if ($1 == ''QUANTITY''){

    if ($3[0] == null){

      return ''0.00''

    } else {

      return df.format($3[0].setScale(2, java.math.RoundingMode.CEILING))

    }

  } else if ($1 == ''MONEY''){

    if ($3[0] == null){

      return ''$0.00''

    } else {

      return ''$'' + df.format($3[0].setScale(2, java.math.RoundingMode.CEILING))

    }

  } else if ($1 == ''PERCENT''){

    if ($3[0] == null){

      return ''0.00%''

    } else {

      return $3[0].multiply(new BigDecimal(100)).setScale(2, java.math.RoundingMode.CEILING).toString()+''%''

    }

  } else {

    return ''''

  }

}');

Use the Evaluate function in the Credit Rule and specify the name of the query

Bonus Level

If you're stuck with groovy programming language or syntax or to write your own logic, Ask your questions or explain the code using Prompts which ChatGPT will try to help you out.

I have provided you sample examples from above code to understand "MONEY" format in Groovy script

Evaluate through Groovy IDE which comes in handy for your testing.. so you can validate your code with right results .. Once your code is working fine, you can insert into Plugin table.

---

Best Practice

• Use the context variable (Example: $periodStartDate, $periodEndDate) instead of the parameter variable ($1 to $8) if possible. This will perform better because it doesn't need to process the input parameter. It will also make your query less dependent on the user provided value, which can be changed.

• Provide default value for your function to reduce the warnings in the log file for null value.

• If you have a complex query that needs to join many tables or query large volumes of data, use a stage hook to pregenerate the result in bulk and populate it in the EXT table and then use the Query function to query the result from that table. The query runs when processing each SalesTransaction for each Rule in Allocate stage and will improve the performance significantly.