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.

Pratical examples for Evaluating Functions through Formulas

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

Ask your questions or doubts and clarify from ChatGPT

Understanding of MONEY format for above Groovy script you inserted to Plugin table

Evaluate through Groovy IDE which comes in handy for your testing.. so you can insert proper into your 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.