Skip to Content
Technical Articles
Author's profile photo Yogananda Muthaiah

How to use Plugin’s efficiently to derive your calculations

Hello ,

The purpose of this article is to demonstrate and explain how Query functions for String, Value, Date and Boolean works.

This will help Comp Admins to achieve when there is a complex requirement, by trying to construct the Formula and assign in a rule for Pipeline calculation to generate results;

Comp Admin will create a custom SQL SELECT Statements in a efficient way that results to return within 5 seconds incase it will be a session run time out and results won’t be appearing.

Admins to ensure that the uptime, performance are taken care.

SQL Statement will have parameter condition to set and to be inserted in Plug-in table (TCMP).
By running the pipeline job, rule gets processed and calls the custom SQL query during the stages so that you can get derived results out of it.

Context Variables: The following context variables can be used to reference values in the current processing context:

  • $pipelineRunSeq – current pipeline run’s pipelineRunSeq
  • $pipelineRunDate – current pipeline run’s startTime
  • $modelSeq – current pipeline run’s modelSeq
  • $periodSeq – current pipeline run’s periodSeq
  • $calendarSeq – current pipeline run’s calendarSeq
  • $processingUnitSeq – current pipeline run’s processingUnitSeq
  • $periodStartDate – current pipeline run period’s startDate
  • $periodEndDate – current pipeline run period’s endDate
  • $lastDateInPeriod – current pipeline run period’s lastDate (i.e. endDate minus one second)
  • $tenantId – current tenantId
  • $positionSeq – current position in the processing
  • $positionName – name of the current position in the processing
  • $positionEffectiveStartDate – effectiveStartDate of the current position in the processing
  • $positionEffectiveEndDate – effectiveEndDate of the current position in the processing
  • $positionProcessingStartDate – processingStartDate of the current position in the processing
  • $positionProcessingEndDate – processingEndDate of the current position in the processing
  • $positionCreditStartDate – creditStartDate of the current position in the processing
  • $positionCreditEndDate – creditEndDate of the current position in the processing
  • $payeeSeq – current payee in the processing
  • $ruleSeq – current rule in the processing
  • $planSeq – current plan in the processing
  • $salesTransactionSeq – current sales transaction in the processing (only apply in Allocate stage)
  • $compensationDate – compensation date of the current sales transaction in the processing (only apply in Allocate stage)
  • $salesOrderSeq – current sales order in the processing (only apply in Allocate stage)
  • $creditSeq – current credit in the processing (only apply in Allocate stage)

Parameter Variables: The following parameter variables can be used as values in the input parameter of the query function:

  • $1 – value of the first String parameter in the Query function. (first String parameter)
  • $2 – value of the second String parameter in the Query function (second String parameter)
  • $3 – value of the first Value parameter in the Query function (first Value parameter)
  • $4 – value of the second Value parameter in the Query function (second Value parameter)
  • $5 – value of the first Boolean parameter in the Query function (first Boolean parameter)
  • $6 – value of the second Boolean parameter in the Query function (second Boolean parameter)
  • $7 – value of the first Date parameter in the Query function (first Date parameter)
  • $8 – value of the second Date parameter in the Query function (second Date parameter)

Let’s go overview to understand more details about it.

Query for String

Assign the Formula to a Rule based on Rule usage

Insert the SQL Select Statement to CS_Pluginquery Table

Query for Boolean

Insert the SQL Select Statement to CS_Pluginquery Table

Query for Date

Insert the SQL Select Statement to CS_Pluginquery Table

Query for Value 


Using above use-cases, Pipeline results are generated for the functions ;

Query for String , Boolean , Date & Value


 

Note : If SQL Select Statements are not inserted, will receive pipeline errors as shown here below

 

Disclaimers

  • SELECT Statement results should return within 5 seconds else timeout(or it will be canceled)
  • Ensure you have SPACE after the parameter
  • Allowed ONLY SELECT Statements.
  • No UPDATE, INSERT & DELETE Statements allowed.
  • No Stored Procs or Packages to call
  • If the query returns multiple rows, only the first row’s value will be considered.

Assigned tags

      17 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Meharunnisha Sheik
      Meharunnisha Sheik

      Informative Yoga!!!

      Author's profile photo Suresh Musham
      Suresh Musham

      This is interesting! We can get rid of building Stagehooks for any minor requirements. Good that you have stressed on the performance aspect when using Query functions.

      Author's profile photo Yogananda Muthaiah
      Yogananda Muthaiah
      Blog Post Author

      Suresh, your thinking on eliminating Stage-hook is highlight and added advantage for Comp admin and support team for risk free deployment and no ticketless

      Author's profile photo Saurabh Katoch
      Saurabh Katoch

      Yogananda Muthaiah - The 5 sec aspect is very important else the function will not work. Nice to see another wonderful article.

      Author's profile photo Yogananda Muthaiah
      Yogananda Muthaiah
      Blog Post Author

      you Nailed it !  That's cool Saurabh!

      Author's profile photo Saurabh Katoch
      Saurabh Katoch

      Yogananda Muthaiah - for getting the query function into the system you have to go through support to get the insert script executed.

      Author's profile photo Yogananda Muthaiah
      Yogananda Muthaiah
      Blog Post Author

      yes Agree for Production.

      Author's profile photo Teja Thogaru
      Teja Thogaru

      Nice article Yoga 🙂

      Author's profile photo Yogananda Muthaiah
      Yogananda Muthaiah
      Blog Post Author

      Thanks Teja(Mike)!!

      Author's profile photo Srinivas Rachapudi
      Srinivas Rachapudi

      Thanks much for great article. Read this long back and finally we got a use case to implement recently which was successful. Appreciate for sharing your knowledge.

      Author's profile photo Yogananda Muthaiah
      Yogananda Muthaiah
      Blog Post Author

      Thank you

      Author's profile photo Suhas Kinnimulki
      Suhas Kinnimulki

      Great, was trying to explore this.
      Keep penning more.

      Author's profile photo Yogananda Muthaiah
      Yogananda Muthaiah
      Blog Post Author

      Thanks Suhas Kinnimulki !

      Author's profile photo Kameshwar Nukala
      Kameshwar Nukala

      Hi Guys,

      We have 1.2 M positions and we have written a query to fetch a data point from position how to check the query runs in less than 5 sec during runtime.

      Author's profile photo Yogananda Muthaiah
      Yogananda Muthaiah
      Blog Post Author

      Kameshwar Nukala you will test it in local webide before inserting into cs_pluginquery table.

      Author's profile photo Krishnan Ramany
      Krishnan Ramany

      Hey Yoga, Thanks for sharing this. Referred to this article to setup a plugin and it was very helpful.  In fact the effort required to create a plug in is quite minimal compared to a stage hook and it does give the developer more flexibility.

      Thanks again.

      Author's profile photo Yogananda Muthaiah
      Yogananda Muthaiah
      Blog Post Author

      Thanks, Krishnan Ramany for your valuable feedback..

      Please do share this article with your SPM colleagues, customers, and partners who can make use of this functionality much more...