1. Using Incentive Compensation
  2. Calculate Quarterly Bonuses Using a User-Defined Query to Find Ranking

Calculate Quarterly Bonuses Using a User-Defined Query to Find Ranking

This example demonstrates how to create an incentive compensation plan that compensates account managers on revenue billed and collected.

  • At the end of each quarter, account managers also get a bonus based on the national level ranking of all of the plan participants.

  • To get the ranking, the plan calculates attainment first and then the relative ranking, and pays the bonuses accordingly.

Here are the key decisions for the plan component in this scenario.

Decision to Consider

In This Example

What is the payout frequency?

Quarter

What type is the calculation?

Interval-based

How many measures do you require and what are their weights?

Two measures with no weight

Are any of these measures linked?

No

How many rate tables do you require, and how many dimensions for each table?

One rate table with one dimension and five tiers

Do not split

Here are the key decisions for the performance measures in this scenario.

Decisions to Consider

In This Example

How should the application process the transaction?

Group by interval

What is the unit of measure?

Measure 1: Amount

Measure 2: Amount

What is the performance interval?

Quarter

Does the calculation involve quota?

No

Does the calculation involve a scorecard?

No

Summary of the Tasks

In the Compensation Plans work area, create the incentive compensation plan using a top-down approach:

  1. Create a user-defined query to get the participant's ranking.

  2. Create a rate table for the bonus plan component to use with the incentive formula.

  3. Create a plan and plan component.

  4. Create and add a performance measure that includes a new attainment expression in the measure formula.

  5. Create and add a ranking performance measure.

  6. Define a new earnings expression in the incentive formula for the bonus plan component and add the rate table.

Use default values for fields unless the steps specify other values.

Step 1. Create the User-Defined Query

  1. Create a value set. 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

    Value Set Code

    XX_UDQ_GET_RANKING

    Note: The Value Set Code should only be made up of characters (A-Z, a-z) , numbers ( 0 - 9 ) and underscores ( _ ). Do not use a hyphen ( - ) , ASCII characters, spaces, or pipes ( | ) . It should also not be more than 30 characters in length.

    Description

    Get Ranking

    Module

    Incentive Compensation

    Validation Type

    Table

    Value Data Type

    Number

    Security enabled

    Deselected

    FROM Clause

    CN_SRP_PER_FORM_METRICS_ALL a, CN_SRP_PER_FORM_METRICS_ALL b

    Value Column Name

    a.srp_per_form_metric_id

    WHERE Clause

    		 
    1=1
AND a.plan_component_id =  :1 /*N*/
AND a.formula_id        = :2 /*N*/
AND a.period_id         = :3 /*N*/
AND b.plan_component_id =  :1 /*N*/
AND b.formula_id        = :2 /*N*/
AND b.period_id         = :3 /*N*/
AND a.participant_id= :4 /*N*/
AND ( b.output_achieved_ptd > NVL(a. output_achieved_ptd, 0) )

  3. Save and close twice.

  4. Use the Manage Incentive Lookups task to create a matching lookup for the value set.

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

    2. Add a new lookup value with the code XX_UDQ_GET_RANKING.

    3. The display sequence is 1.

    4. Enter Get Ranking in the Meaning field. 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.

    5. Save the lookup value and close.

  5. Use the Configure Tables and Columns task to enable these columns in the CN_SRP_PER_FORM_METRICS_ALL table for calculation and add user names.

    Column

    User Name

    FORMULA_ID

    Formula ID

    PARTICIPANT_ID

    Participant ID

    PERIOD_ID

    Period ID

    PLAN_COMPONENT_ID

    Plan Component ID

  6. Save and close.

Step 2: Create a Rate Table To Determine Ranking

  1. In the Compensation Plans work area go to the Manage Rate Tables task.

  2. Create a rate table for the calculation process to use to find the ranking to apply when computing bonus payouts. Click Create.

    1. Name the rate table Bonus Ranks.

    2. In the Type field, select Amount.

    3. On the Rate Dimensions section toolbar, click Create.

      1. In the Create Rate Dimension page, name the dimension Rank.

      2. In the Type field, select Amount.

      3. In the Tiers section, click Add Row and add the From and To values for each tier: 0 -- 20, 20 -- 50, 50 -- 75, 75 -- 100, and 100 -- 10,000.

      4. Click Save and Close to return to the Create Rate Table page.

    4. Click Edit Rates.

      1. Edit the rate, the incentive earned in USD, for each tier: 25,000, 20,000, 15,000, 10,000, and 0.

      2. Click Save and Close to return to the Create Rate Table page.

    5. Click Save and Close.

  3. Click Done.

Step 3: Create the Plan and a Plan Component Within the Compensation Plan Guided Process

Create and add a plan component, rather than adding an existing one. The new plan component acts as a base plan component to determine ranking. To create the plan component:

  1. Click Create in Manage Compensation Plans.

  2. Enter the primary details. Name your plan Quarterly Bonus with User-Defined Query.

  3. Enter the start date January 1 and end date December 31 for the current year.

    Tip: Always end date your compensation plans.

  4. Click Next to open the Create Compensation Plan: Add Plan Component page.

  5. On the Plan Components section toolbar, click Create.

    1. Complete the primary detail fields, as shown in this table

      Field

      Value

      Name

      Quarterly Bonus

      Calculate Incentive

      Per interval

      Incentive Type

      Bonus

      Earning Type

      Monetary Earnings

    2. Click Next to open the Create Plan Component: Add Performance Measure page.

Step 4: Create and Add the Attainment Performance Measure to the Base Plan Component Within the Plan Component Guided Process

Create and add a performance measure, rather than adding an existing one. The measure calculates attainment based on the credit amount of transactions grouped by interval.

  1. On the Performance Measures section toolbar, click Create in the Actions menu.

  2. Name it Quarterly Attainment

  3. In the Performance Interval field, select Quarter.

  4. The unit of measure is Amount.

  5. In the guided process, click Next to Open Define Goal, and Next again to Add Credit Categories.

  6. In the Credit Categories toolbar, click Add. Search for the Green Servers category and click Apply and Done. If a credit category doesn't exist, then click Create to create it and add it to the measure.

  7. Click Next to open the Create Performance Measure: Define Measure Formula page.

    1. In the Process Transactions field, select Grouped by interval.

    2. In the Expression Name field, select Create New Expression.

    3. Name the expression Sum Quarter.

    4. In the Configure Expression section, create the attainment expression SUM(Credits.Credit Amount):

      1. On the Attributes menu, select Functions > Aggregate Functions > SUM.

      2. Click the left parenthesis (.

      3. On the Attributes menu, select Credit > Credit Amount.

      4. Click the right parenthesis ).

      5. Click Save and Close to return to the Create Performance Measure: Define Measure Formula page.

        The Expression Name field contains the name of the expression that you just created.

  8. Click Save and Close to return to the Create Plan Component: Add Performance Measure page.

Step 5: Create and Add the Ranking Performance Measure to the Bonus Plan Component Within the Plan Component Guided Process

Create and add a performance measure, rather than adding an existing one. The measure calculates ranking based on revenue attainment using a user-defined query.

  1. On the Create Plan Component: Add Performance Measure page, click Create in the Actions menu.

    1. Complete the primary detail fields, as shown in this table.

      Field

      Value

      Performance Measure Name

      Ranking Measure

      Use external formula

      No

      Performance Interval

      Quarter

      Unit of Measure

      Amount

      Dates

      Enter dates from January 1 to December 31 this year

  2. In the guided process, click Next to Open Define Goal, Next to Add Credit Categories, and Next again to Define Measure Formula.

  3. In the Process Transactions field, select Grouped by Interval.

  4. In the Expression Name field, click Create New Expression.

  5. Name the expression Rank Performance.

  6. Create the expression:

    1. Select Select an Attribute > Functions > Value Set Aggregate functions > COUNT_VALUE_SET.

    2. Select the left parenthesis (.

    3. Select the attribute User Defined Objects > User defined queries > Get Ranking and click Add to Expression.

    4. Select the left parenthesis (.

    5. Go to User Defined Objects > Measure Result to select the attainment performance measure you created in Step 2 named Quarterly Attainment, and the Plan Component ID attribute. Click Add to Expression.

    6. Select the comma ,.

    7. Go to User Defined Objects > Measure Result to select the Quarterly Attainment performance measure and the Formula ID attribute. Click Add to Expression.

    8. Select the comma ,.

    9. Go to User Defined Objects > Measure Result to select the Quarterly Attainment performance measure and the Period ID attribute. Click Add to Expression.

    10. Select the comma ,.

    11. Go to User Defined Objects > Measure Result to select the Quarterly Attainment performance measure and the Participant ID attribute. Click Add to Expression.

    12. Select the right parenthesis twice)).

    You completed expression is:
    COUNT_VALUE_SET (Get Ranking ( Measure result.Quarterly Attainment.Plan Component ID , Measure result.Quarterly Attainment.Formula ID , Measure result.Quarterly Attainment.Period ID , Measure result.Quarterly Attainment.Participant ID ))

  7. Save and close.

  8. Save and close.

  9. For the Performance Measure named Ranking Measure, change the calculation sequence to 2.

Step 6: Finish Creating and Adding the Bonus Plan Component Within the Compensation Plan Guided Process

To finish creating the plan component:

  1. Click Next to open the Create Plan Component: Define Incentive Formula page.

    1. In the Payout Frequency field, select Quarter.

    2. In the Expression Name field, select Create New Expression.

      1. Name the new expression Bonus Rate.

      2. On the Configure Expression section Attributes menu, select Rate Table Rate.

      3. Click Save and Close to return to the Create Plan Component: Define Incentive Formula page.

      The Expression Name field contains the name of the expression that you just created.

  2. Click Next to open the Create Plan Component: Add Rate Table page.

  3. Click Add Row.

  4. In the Name field, search for a select the Bonus Ranks rate table.

  5. In the Rate Dimension Inputs section Expression Name field, select Create New Expression.

    1. Name the new expression Bonus by Rank.

    2. In the Configure Expression section, create the expression Measure result.Ranking Measure.ITD Output Achieved:

      1. On the User Defined Objects menu, search for and select the attribute Measure result.

      2. Search for and select the performance measure Ranking Measure.

      3. Select the attribute ITD Output Achieved.

      4. Click Add to Expression.

    3. Click Save and Close to return to the Add Rate table.

    The Expression Name field contains the name of the expression that you just created, Bonus by Rank.

  6. Click Save and Close to return to the Create Compensation Plan: Add Plan Component page.

  7. Click Save and Close to return to the Manage Compensation Plans page.