Interpolated Result Not Working When Rate Table Has A Negative Range (Doc ID 2719192.1)

Last updated on APRIL 25, 2024

Applies to:

Symptoms

For rate tables with interprolated split tier, calculation is not starting from the first tier ( negative ) but calculating from zero starting point.
Normally, negative rates are used to generate negative commission ( return ).
In this case, it is used to still give bonus based on improvement (even if it's a negative improvement).

Steps to Reproduce:
1. Create 2 dimensional rate tables
    Dim 1 : Test Dim ( Amount )
         FROM            TO             RATE
        -0.010001    -0.01         0.0113
               -0.01   0.003          0.0338
              0.003      0.01            0.03
    Dim 2 : Test String State Tier ( String )
                Fl , CA , SC
2. Create Compensation Plan : Test Comp Plan
    Plan Component ( Test RevPAR PC) with rate :
            Rate Table = Test RevPar 2Dim Rate Table
            Split Attainment Across Tiers = Yes
            Split Option Proportional within a tier. Also known as an interpolated rate.
3. Assign comp plan to participant (Rep1 )
4. Create transactions (eg. TRX-% for 6/26/20 - 6/27/20 )
5. Calculate
            TRX-3 , Input = 0.0079 , Earning Rate = 0.0288
            The Earning Rate is currently derived as :
                                                        (TO - FROM)
             TIER   FROM         TO            TIER RANGE    RATE          SPLIT      COMMISSION RATE
                1    -0.010001   -0.010000    0.000001     0.01125                        0.000000
                2    -0.010000    0.003000    0.013000     0.03375     0.003           0.007788
                3     0.003000    0.010000    0.007000     0.03000     0.0049          0.021000
                                                                                            ---------        ------------
                                                                                TOTAL    0.0079         0.028788

            User is expecting :
            TIER   FROM         TO            TIER RANGE     RATE          SPLIT      COMMISSION RATE
               1     -0.010001  -0.010000   0.000001      0.01125                          0.01125
               2     -0.010000   0.003000   0.013000      0.03375       0.003           0.03375
               3      0.003000   0.010000    0.007000     0.03000       0.0049          0.021000
                                                                                             ---------       -------------
                                                                               TOTAL     0.0079           0.066

             Expectation is that :
             If meet minimum tier -0.01000, then get minimum rate 0.01125.
          + If meet targeted tier 0.003000, then get target rate 0.03375.
          + If remainder fall between tier, then prorate split.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.