1. Using Incentive Compensation
  2. Calculate Biweekly Earnings Using a Score with a User-Defined Query to Find Adjustment Factor

Calculate Biweekly Earnings Using a Score with a User-Defined Query to Find Adjustment Factor

This incentive plan example demonstrates how to calculate earnings for participants in the Retail industry. Earnings calculations use a score with a user-defined query to determine participant revenue attainment adjustments based on the number of hours worked.

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

Decision to Consider

In This Example

What is the payout frequency?

Biweekly

What type is the calculation?

Interval-based

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

Two measures, no weights

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

How do you want to apply the rate?

Don't split the attainment

Here are key decisions for performance measures in this scenario.

Decision to Consider

In This Example

How should the application process the transaction?

Group by interval for all measures

What is the unit of measure?

Percent and Amount

What is the performance interval?

Biweekly

Does the calculation involve quota?

No

Does the calculation involve a scorecard?

Yes, with one rate dimension

Don't split

Summary of the Tasks

In the Compensation Plans work area, create a plan component by creating the parts first, and then associating them with the plan component.

  1. Create a user-defined query.

  2. Create a scorecard to associate with a performance measure and a rate table to associate with the plan component.

  3. Create a performance measure to calculate attainment.

  4. Create measure formula expressions to return the participant's adjustment factor and hours worked.

  5. Create the interval adjustment factor performance measure.

  6. Create the rate dimension input and earnings expressions.

  7. Create the plan component, associating the performance measures, expressions, and the rate table created earlier.

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

Create the User-Defined Query

Let's assume that the number of hours worked is stored in attribute_number1 in the participant details table. We use the end date of the given accumulation period to find the corresponding participant detail record. If there's no matching record, then we return 80 as the default.

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

    • Offering: Sales

    • Functional Area: Incentives

  2. Click Create and create a new value set with these criteria:

    Criteria

    Value

    Value Data Type

    Number

    Value Set Code

    cnvs_get_work_hours

    Module

    Incentive Compensation

    Validation Type

    Table

    Security Enabled

    Deselect

    FROM Clause

    cn_srp_participant_details_all spd, cn_periods_b p, cn_repositories_all_b r

    Value Column Name

    attribute_number1

    WHERE Clause

    		 
    spd.participant_id = :1 /*Number*/
and spd.org_id = r.org_id
and p.period_id = :2 /*Number*/
and p.calendar_id = r.calendar_id
and p.end_date between spd.start_date and nvl(spd.end_date, p.end_date)

  3. Save and close the value set.

  4. In the Setup and Maintenance work area, go to the Manage Incentive Lookups task .

  5. Search for the lookup type ORA_CN_USR_DEFN_EXPRSN_VLE_SET.

  6. In the Lookup Codes region, click New.

  7. Add and enable a new lookup value with a code that matches the value set code you created, using these field values:

    Field

    Value

    Lookup Code

    cnvs_get_work_hours

    Meaning

    Get Work Hours

    Description

    Get the working hours for a particular participant and period. Inputs: Participant Id (Number), Period Id (Number)

    Display Sequence

    1

    Enabled

    Select

  8. Save and close.

Create the Scorecard and Rate Table

Create these two rate tables:

Used By

Where

Purpose

Performance measure

Add Scorecard page or tab

Find the adjustment factor based on the number of hours worked.

The plan component with this performance measure uses the adjustment factor in bonus earnings calculations.

Plan component

Add Rate Table page or tab

Calculate bonus earnings based on the revenue attainment.

  1. Navigate to Incentive Compensation > Compensation Plans > Manage Rate Tables.

  2. Click Create.

    Complete these following steps twice to create first the scorecard and then the rate table.

  3. Name the rate table.

    1. Name the first one Work Adjustment.

    2. Name the second one Work Bonus Earnings.

  4. In the Type field, make the following selection:

    1. Percent for the performance measure scorecard

    2. Amount for the plan component rate table

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

    1. Name the dimension Hours for the scorecard, and Revenue for the rate table.

    2. In the Type field, select Amount.

    3. In the Tiers section, add the From and To values for each tier, such as:

      1. For the scorecard: 0 -- 40, 40 -- 60, 60 -- 70, 70 -- 80, and 80 -- 999

      2. For the rate table: 0 -- 100,000, 100,000 -- 200,000, 200,000 -- 300,000, 300,000 -- 9,999,999

        Make the final To value much larger than you would ever conceivably use.

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

  6. Click Edit Rates.

    1. Edit the rate for each tier, such as:

      1. 50, 75, 85, 90, and 100 for the scorecard

      2. 0, 500, 1,000, and 1,500 for the rate table

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

  7. After creating the scorecard, click Save and Create Another.

    After creating the rate table, click Save and Close to return to the Manage Rate Tables page.

  8. Click Done.

Create the First Performance Measure

  1. Click Create in Manage Performance Measures. Name it Interval Revenue Attainment.

  2. In the Performance Interval field, select Biweekly.

    If this performance interval doesn't exist, then create it using the Manage Intervals task in the Setup and Maintenance work area. Your calendar period must be weekly or daily to create a biweekly interval.

  3. In the Unit of Measure field, select Amount.

  4. Enter start and end dates for the year.

  5. Click Next.

  6. Click Add Credit Categories in the guided process. Add the credit category. If the credit category doesn't exist, then click Create to create it and add it to the measure.

  7. Click Next to open the Define Measure Formula page.

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

    2. In the Expression Name field, search for and select any existing expression. You will replace it later.

  8. Save and close.

  9. Navigate to Manage Expressions.

  10. Click Create.

  11. Name the expression Retail Interval Attainment.

  12. In the Configure Expression section, create the rate dimension input expression Measure result.Interval Revenue Attainment.ITD Output Achieved.

    Rate Dimension Input

    1. On the User Defined Objects menu, select Measure result.

    2. In the Measure field, search for and select your performance measure that calculates the Interval Revenue Attainment.

    3. Select ITD Output Achieved.

    4. Click Add to Expression.

    5. Save and close.

    6. Click Done.

  13. Navigate to Manage Performance Measures.

  14. Search for and open Interval Revenue Attainment.

  15. On the Measure formula tab, change the expression name to Interval Revenue Attainment

  16. Save and close.

Create Two Performance Measure Expressions

Create the expressions in this table.

Type of Expression

Purpose

Scorecard rate dimension input

Find the adjustment factor.

Interval adjusted factor

Return the interval adjustment factor for the participant.

  1. Click Create in Manage Expressions.

  2. In the Configure Expression section, create the input expression NVL (Get Work Hours (Measure result.Interval Revenue Attainment.Participant ID , Measure result.Interval Revenue Attainment.Period ID) , 80), and factor expression Rate Table Rate.

    Measure Scorecard Rate Dimension Input: name it Scorecard Adjustment.

    1. On the Attributes menu , select Functions > Null functions > NVL.

    2. Click the left parenthesis (.

    3. On the User Defined Objects menu, select User defined queries.

    4. In the User Defined Queries field, select Get Work Hours.

    5. Click Add to Expression.

    6. Click the left parenthesis (.

    7. On the User Defined Objects menu, select Measure result.

    8. In the Measure field, search for and select the performance measure that calculates the Interval Revenue Attainment.

    9. In the Select an Attribute field, search for and select Participant ID.

    10. Click Add to Expression.

    11. Click the comma ,.

    12. On the User Defined Objects menu, select Measure result.

    13. In the Measure field, search for and select the performance measure that calculates the Interval Revenue Attainment.

    14. In the Select an Attribute field, search for and select Period ID.

      Tip: If Participant ID and Period ID aren't available, then enable them for calculation using the Configure Tables and Columns task in the Setup and Maintenance work area. Enable the corresponding columns of the CN_SRP_PER_FORM_METRICS_ALL table.

    15. Click Add to Expression.

    16. Click the right parenthesis ).

    17. Click the comma ,.

    18. On the Attributes menu, select Constant.

    19. Enter 80.

    20. Click Add to Expression.

    21. Click the right parenthesis ).

    22. Click Save and Create Another.

    23. Name the new expression Adjustment Factor.

    24. On the Attributes menu , select Rate Table Rate.

  3. Click Save and Close to return to the Manage Expressions page.

  4. Click Done.

Create the Interval Adjustment Factor Performance Measure

  1. Navigate to the Manage Performance Measures page.

  2. Search for and select the measure that determines the Interval Revenue Attainment.

  3. On the Search Results section toolbar, click Duplicate.

  4. Edit the name and display name to Interval Adjustment Factor.

  5. Select the Measure Formula tab. Remove the existing expression name and replace it with Adjustment Factor.

  6. Select the Scorecard tab.

    1. In the Number of Dimensions field, enter 1.

    2. In the Split Attainment Across Tiers option, select No.

    3. In the Scorecard section, add a row.

    4. In the Name field, search for and select the scorecard (rate table) that finds the adjustment factor based on the number of hours worked, named Work Adjustment.

    5. In the Rate Dimensional Inputs section Expression Name field, search for and select the expression that finds the adjustment factor, named Scorecard Adjustment.

Create the Rate Dimension Input and Earnings Expressions

Create these two expressions:

Type of Expression

Purpose

Rate dimension input

Provide the retail interval revenue attainment.

Earnings

Calculate the interval earnings for the participant.

  1. Click Create in Manage Expressions.

  2. Name it Retain Revenue Attainment.

  3. In the Configure Expression section, create the rate dimension input expression Measure result.Interval Revenue Attainment.ITD Output Achieved and earnings expression Measure result.Interval Revenue Attainment.ITD Output Achieved * Rate Table Rate * Measure result.Interval Adjustment Factor.ITD Output Achieved.

    Rate Dimension Input

    1. On the User Defined Objects menu, select Measure result.

    2. In the Measure field, search for and select the performance measure that calculates the interval revenue attainment, named Interval Revenue Attainment.

    3. Select ITD Output Achieved.

    4. Click Add to Expression.

    5. Click Save and Create Another.

    Earnings

    1. Name it Interval Earnings.

    2. On the User Defined Objects menu, select Measure result.

    3. In the Measure field, search for and select the performance measure that calculates the interval revenue attainment, named Interval Revenue Attainment.

    4. Select ITD Output Achieved.

    5. Click Add to Expression.

    6. Click the asterisk *.

    7. On the Attributes menu, select Rate Table Rate.

    8. Click the asterisk *.

    9. On the User Defined Objects menu, select Measure result.

    10. In the Measure field, search for and select the performance measure that calculates the adjustment factor, named Interval Adjustment Factor

    11. Select ITD Output Achieved.

    12. Click Add to Expression.

  4. Click Save and Close to return to the Manage Expressions page.

Create the Plan Component

To calculate retail earnings every two weeks using adjustment factors, create a bonus plan component that contains the revenue attainment and adjustment factor performance measures.

  1. Click Create in Manage Plan Components.

  2. Name it Retail Earnings Bonus.

  3. In the Calculate Incentive field, select Per interval.

  4. In the Incentive Type field, select Bonus.

  5. Enter start and end dates for the year.

  6. Click Next to open the Create Plan Component: Add Performance Measure page. Add the performance measures and set the earning basis values, as shown in this table.

    Measure

    Earning Basis

    Sequence

    Interval revenue attainment

    Yes

    1

    Interval Adjustment factor

    No

    2

  7. Click Next to open the Create Plan Component: Define Incentive Formula page. Complete the fields, as shown in this table.

    Field

    Value

    Expression Name

    Search for and select the expression that calculates the interval earnings for the participant, Interval Earnings.

    Payout Frequency

    Biweekly

    True up

    No

    Include Indirect Credits

    All

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

    1. In the Rate Table Dimensions field, enter 1.

    2. In the Rate Table section, add a row.

    3. In the Name field, search for and select the rate table that calculates bonus earnings based on the interval revenue attainment, Work Bonus Earnings.

    4. In the Rate Dimensional Inputs section Expression Name field, search for and select the expression that provides the interval revenue attainment, named Interval Revenue Attainment.

  9. Click Save and Close to return to the Manage Plan Component page.

Related Topics