Best Practices for Creating User-Defined Queries

You can create a user-defined query or a select SQL query to be used by the plan administrators in the incentive compensation expressions to calculate attainments and earnings. Here's a list of best practices when creating user-defined queries.

Since value sets are also used in descriptive flexfields, you can add a common prefix or suffix for value sets created exclusively for user-defined queries. This prevents confusion about where these value sets can be used. For example, all value set codes for user-defined queries begin with cnvs: cnvs_get_work_hours or CNVS_GET_CONV_RT.
Restrict the length of the value set code to 30 characters because the lookup code can only be 30 characters long.

Provide all information about how to use the user-defined query in the description text box of the lookup. The following table lists the questions you should answer in the description.

Usage Question	Text Example for Description
What does it do?	Gets the year-to-date payment already made to a participant.
What does it return?	Returns a string.
What inputs are needed and in what order are they needed?	Pass Participant ID (Number) and Currency Code (String).
Does it need value set aggregation functions?	Use with SUM_VALUE_SET function.
Does it need an NVL function?	Use with NVL function.

Consider using logical operations to cover your complex IF-THEN-ELSE incentive calculation scenarios. Logical operations are provided in the expression builder and can take user-defined queries as the inputs.
Use the BI Data Model to validate your Select query.
In the BI Data Model, generate an Explain Plan to see potential performance and whether you're using an index.
Make sure your WHERE clause has filter conditions on some database fields that are indexed. This improves performance.
When you use custom fields for filter conditions, pick the custom field with the most unique data and designate it as indexed when you create the custom field. This improves performance.