Calculation Error No Rate Found With String UDQ Input In Two Dimensional Rate Table (Doc ID 3012012.1)

Last updated on MARCH 21, 2024

Applies to:

Symptoms

Calculation did not generate output for performance measure.  Debug messages show no rate found on the string input for the two dimensional rate table .

ERROR
l_start_period_id =2023001, p_period_id = 2023004, l_endofinterval_flag = Y, l_processed_date = 2023-12-31
Rate Dim Sequence : 1
Input Amount : 8.33333333370786516853932584269662921348
Amount : 8.33333333370786516853932584269662921348
Base Amount : 0
Calling user defined query "RATE TYPE_DEPENDENCY" with parameters: 1234189,ORDER1,ORDER1 RT
execute_query_1 executed successfully.
Returning: 0/0/50/100/150/300
Rate Dim Sequence : 2
String Input : 0/0/50/100/150/300
Base Amount : 8.33333333370786516853932584269662921348
Done with get_input
Processing participant : 300000010341913, plan componnent : 300000045316307, formula : 300000045316120, rate table 300000027987284
l_split_dim: 1
cn_tp_calc_util.get_rates.l_invalid_rate_tier
No matching rate



STEPS

1. Create UDQ "RATE TYPE_DEPENDENCY"

select MOT.EXTN_ATTRIBUTE_CHAR010
from
CN_SRP_COMP_PLANS_ALL SRP,
MOT_REF_ENTITIES MOT,
CN_SRP_PLAN_COMPONENTS_ALL SRPC,
CN_COMP_PLANS_ALL_TL CP
where
1=1
AND MOT.ATTRIBUTE_CATEGORY = 'RateType_c'
AND SRP.SRP_COMP_PLAN_ID = SRPC.SRP_COMP_PLAN_ID
AND SRP.COMP_PLAN_ID = CP.COMP_PLAN_ID
AND MOT.EXTN_ATTRIBUTE_CHAR009 = SRPC.ATTRIBUTE2
AND MOT.EXTN_ATTRIBUTE_CHAR008 = CP.COMP_PLAN_NAME
AND CP.ORG_ID = SRP.ORG_ID
AND SRP.SRP_COMP_PLAN_ID = :1 /* NUMBER */
AND SRPC.ATTRIBUTE2 = :2 /* VARCHAR2 */
AND MOT.EXTN_ATTRIBUTE_CHAR011 = :3 /* VARCHAR2 */

2. Use UDQ as input expression for two dimensional rate table

3. Calculate Commission and see that there's no output rate result for :
    Plan component "Order 1 2023 v2"
    Performance measure "ORD 1 YTD PM 2023 v2"
    Rate table "NA Multidimensional Rate Table"

Cause

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