1. Using Incentive Compensation
  2. Create a User Defined Query

Create a User Defined Query

This example shows how to create a user defined query to get a participant's detail attribute value and avoid associating a credit category to the measure.

The plan administrator wants to use an expression to get a participant detail attribute defined as Position and stored in ATTRIBUTE_CHAR8. But to use the expression requires associating a credit category to the performance measure. This approach causes unnecessary calculation time and many measure results if a participant has many transactions.

This is the query you're going to create:

select d.ATTRIBUTE_CHAR8
 from cn_srp_participant_details_all d,
        cn_period_statuses_v p
where d.participant_id = p_participant_id
   and d.org_id = p.org_id
   and p.period_id = p_period_id
   and ( p.start_date between d.start_date and nvl(d.end_date, sysdate )
        or p.end_date between d.start_date and nvl(d.end_date, sysdate ) ) ;

Here are the tasks to be done:

  1. Create a value set.

  2. Add the value set to a lookup code.

  3. Enable inputs for the expression.

  4. Create the expression.

Create a Value Set

A value set defines the actual query.

  1. Create a value set that will get the value from the ATTRIBUTE_CHAR8 column for a participant:

    1. In the Setup and Maintenance work area, go to the Manage Incentive Value Sets task:

      • Offering: Sales

      • Functional Area: Incentives

    2. Click Create.

    3. In the Create Value Set page, enter or select these fields:

      Field

      Value

      Value Set Code

      		 
      GetPartDetailAttr8

      Description

      Get Participant Details ATTRIBUTE_CHAR8 value

      Module

      Incentive Compensation

      Validation Type

      Table

      Value Data Type

      Character

      FROM Clause

      		 
      CN_SRP_PARTICIPANT_DETAILS_ALL d, CN_PERIOD_STATUSES_V p

      Value Column Name

      ATTRIBUTE_CHAR8

      WHERE Clause

      Note: This clause forms the filter the query will use. Participant ID and Period ID are the two parameters this value set expects. The numbers 1 and 2 indicate the order to pass the parameters in. 
      d.participant_id = :1 /* NUMBER */
    AND d.org_id = p.org_id
    AND p.period_id = :2 /* NUMBER */
    AND ( p.start_date between d.start_date and nvl(d.end_date, sysdate )
    OR p.end_date between d.start_date and nvl(d.end_date, sysdate ) )

    4. Save and close.

  2. Click Cancel.

Add the Value Set to a Lookup Code

You're back to the Incentives functional area in Setup and Maintenance. Add your new value set to the table value sets list you can select while you build an expression.

  1. Go to the Manage Incentive Lookups task.

  2. Search for the lookup type ORA_CN_USR_DEFN_EXPRSN_VLE_SET.

  3. In the Lookup Codes region click New.

  4. Enter the value set as a lookup code GetPartDetailAttr8.

  5. Enter the meaning Get Position.

  6. Click Save and Close.

Enable Expression Inputs

Enable Participant ID and Period ID so you can choose these parameters in the expression builder.

  1. Go to the Configure Tables and Columns task.

  2. Use the query by example under View to search for the table CN_SRP_PER_FORM_METRICS_ALL.

  3. Use Query by Example to search for the column PARTICIPANT_ID.

  4. Enter the user name MSRParticipantID.

  5. Select Enable for Calculation.

  6. Click Save.

  7. Search for the column PERIOD_ID.

  8. Enter the user name MSRPeriodID

  9. Select Enable for Calculation.

  10. Click Save and Close.

Create the Expression

Create the expression that gets the Position attribute value from Participant Detail using the user defined query.

  1. Navigate to Incentive Compensation > Compensation Plans > Manage Expressions.

  2. Click Create.

  3. Name the expression Get Position.

  4. Create the expression:

    1. Select User Defined Objects > User defined queries > Get Position.

    2. Click Add to Expression.

    3. Click the left parenthesis.

    4. Select User Defined Objects > Measure result and your performance measure (for example, Servers Attainment), then the attribute MSRParticipantID.

    5. Click Add to Expression.

    6. Click comma.

    7. Select User Defined Objects > Measure result and your performance measure (for example, Servers Attainment), then the attribute MSRPeriodID.

    8. Click Add to Expression.

    9. Click the right parenthesis.

      The expression is Position (Measure result.Servers Attainment.MSRParticipantID, Measure result.Servers Attainment.MSR Period ID)

    10. Save and close.

Related Topics