Calculation with Multi-dimensional Rate Table Returns Incorrect Rate. (Doc ID 2049559.1)

Last updated on APRIL 11, 2024

Applies to:

Symptoms

Calculation with multi-dimensional rate table returns unexpected rate.

STEPS
1. Create custom function
2. Use custom function with multi-dimensional input rate table in measure formula

   Eg. Rate Table Name : Evaluate Project Type and Deal Size for Analysis Role with DECODE
   Dimension 1 = Deal Size (Analysis and Operations)
   Dimension 2 = Project Type Code
   Rates
                            0 - 499000         499000 - 1000000
   0 - 150                     1.00                 12.00
   150 - 250                 8.00                 18.00
   250 - 350                 6.00                 16.00
   350 - 450                 5.00                 15.00

3. User had copied the original multi-dimensional rate table , delete the dimension, and add back.
4. Create transaction
5. Calculate

The project type is coming in with a value of 300 and the deal size with a value of 400,000 which should return 5.00 but instead getting 16.00 or zero.

Check the debug messages for calculation and it shows :

Measure in a different plan component result : 220522.61
l_start_period_id =2016001, p_period_id = 2016003, l_endofinterval_flag = Y, l_processed_date = 2016-03-31
Rate Dim Sequence : 1
Input Amount : 0
Amount : 0
Base Amount : 0

Cause

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