1. Using Incentive Compensation
  2. Create Expressions with User-Defined Queries

Create Expressions with User-Defined Queries

You can create expressions with user-defined queries using the Manage Expressions task in the Compensation Plans work area. User-defined queries appear under the User Defined Objects menu when you click the User-Defined Queries menu item.

You can select and add user-defined queries just like any other attribute within the Expressions menu. However, it's advisable to look at the description before doing so. Since user-defined queries are created using SQL queries, you should test the earnings calculated and performance on your test environment thoroughly before using the query in production.

Pass Arguments or Inputs to User-Defined Queries

Some user-defined queries need arguments or inputs to be passed to them to work. You can pass plan component results, measure results, other user-defined queries, constants, or attributes enabled in the expression builder menu. The data types and order must match what's expected by the user-defined query. To pass arguments, enter a left parenthesis after the user-defined query name, add the arguments in the right order separated by commas, and follow with the right parenthesis. Here's an example: UserDefinedQuery (1000, 'US', Measure result.Product Sales.ITD Output Achieved)

Create an Expression with a User-Defined Query

Be sure you read the administrator's descriptions of user-defined queries. Here are some things you should understand before adding a user-defined query to an expression.

Data

Example

Behavior of the user-defined query

None

The return data type of the user-defined query to avoid the wrong usage

Credit.Credit Amount + UserDefinedQuery () will go invalid if the user-defined query returns a string

Number and order of arguments passed

UserDefinedQuery (Credit.Credit Amount, Participant.Currency Code, Measure result.Product Sales.ITD Output Achieved) will go invalid if UserDefinedQuery expects only two inputs.

Whether a value set aggregate function is needed to avoid calculation errors

COUNT_VALUE_SET ( UserDefinedQuery () )

Whether an NVL function is needed in case of no values returned

NVL ( UserDefinedQuery () , -1 )

  1. On the User Defined Objects menu, click User defined query.

  2. In the User Defined Functions list, select a user-defined query.

  3. Click Add to Expression.

  4. If the query requires inputs, then do these steps to add them:

    1. Click the left parenthesis (.

    2. Add items from the Attributes menu or the User Defined Objects menu, separated by commas.

    3. Click the right parenthesis ).

  5. Save.