1. Implementing Incentive Compensation
  2. Add Select SQL Queries to the Expression Builder Using the User-Defined Queries Menu

Add Select SQL Queries to the Expression Builder Using the User-Defined Queries Menu

You can create a user-defined query or a select SQL query for plan administrators to use in the incentive compensation expressions to calculate attainments and earnings. Completed queries appear in User-Defined Objects, User-Defined Queries in the UI for the Create Expressions task.

To create and manage user-defined queries and make them available for building expressions:

  1. Create a user-defined query.

  2. Configure the inputs needed by the user-defined query.

  3. Modify and delete user-defined queries.

Create a User-Defined Query

To create a user-defined query:

  1. Create a table validation type value set using these steps:

    1. In the Setup and Maintenance work area, go to the following:

      • Offering: Sales

      • Functional Area: Incentives

      • Task: Manage Incentive Value Sets

    2. On the Manage Incentive Value Sets page, create a new value set with these criteria:

      Criteria

      Value

      Validation Type

      Table

      Module

      Incentive Compensation

      Security Enabled

      Deselect

    3. Select Value Data Type as the output format for the expression.

    4. Enter the select SQL query needed.

    5. Save the value set.

  2. Create a matching lookup.

    1. In the Setup and Maintenance work area, go to the following:

      • Offering: Sales

      • Functional Area: Incentives

      • Task: Manage Incentive Lookups

    2. On the Manage Incentive Lookups page, search for the Lookup type ORA_CN_USR_DEFN_EXPRSN_VLE_SET.

    3. Add a new lookup value with a code that matches the value set code created in step one. In the Expressions UI, the meaning of this lookup value will display as the name of the new user-defined query. The description displays when you select the user-defined query.

    4. Select Enabled for the value.

    5. Save the lookup value.

User-defined queries are created using SQL queries. Therefore, be sure to test the calculations and performance of the query on your test environment before you move the queries to production.

Configure the Inputs Needed by the User-Defined Query

Inputs can be passed to the user-defined query when it's used in the expression builder UI. You define inputs in the WHERE clause of the value set. You can define the sequence of multiple inputs. An explanation of the syntax WHERE <Table Field Name> = :<Sequence of input to User-Defined Query> /*<Data Type of Table Field>*/follows:

  • Table Field Name: The name of the attribute/column name in the WHERE clause.

  • The sequence of the input to the user-defined query: The order of the inputs must be passed to the user-defined query in the Expressions UI. Users must pass consecutive natural numbers starting from 1 (:1, :2, :3).

  • Data Type of Table Field Name: The data type of the table name field. The possible entries are:

    • NUMBER or N for all Number fields

    • DATE or D for Date fields

    • VARCHAR2 or V for Varchar2 fields

For example, PARTICIPANT_ID = :1 /*Number*/ includes the following:

  1. The table field name is PARTICIPANT_ID.

  2. The sequence of the input to the user-defined query is 1.

  3. The data type of the table field name is Number.

The following example shows three attributes defined in the WHERE clause of the value set:

    Number_Attribute = :1 /*Number*/
AND String_Attribute = :3 /*Varchar2*/
AND Date_Attribute = :2 /*Date*/

In the Expressions UI, you must pass the following for the previous example user-defined query: Value Set (21, TO_DATE('14-Jun-2018','DD-MON-YYYY'), 'World Cup'). The calculation then uses the value set query with the passed inputs as follows:

  1. Number_Attribute = 21

  2. Date_Attribute = '14-Jun-2018'

  3. String_Attribute = 'World Cup'

Note: If the same sequence appears twice in the value set, then only the data type of the first usage of the sequence is used.

Edit and Delete User-Defined Queries

You can edit the SQL query used in a user-defined query or delete the user-defined query entirely only if it's not used in an expression. You must remove the value set from all expressions before you do so. If you delete a user-defined query, be sure to also delete the corresponding lookup.