201210234AM-DIR : Changes to CALC_DIR_VALUE

This is to announce changes to the DIR functionalty that will be released shortly.

Calculation Types

There are 2 new calculation types Graded and Total Calculation (GC and TC). These are used to link Range Items to other Value Definitions in order to record a single Rate or Amount, when that Rate or Amount can be used in more than one Value Definitions Range. For example:-

Val Def 1:-

Tax_for Category1
0 to 1000 use SPECIAL_RATE
1001 to 10000 use STANDARD RATE
10001 to 999999999 use ENHANCED RATE

Val Def 2:-
Tax for Category 2
0 to 1000 use ENHANCED_RATE
1001 to 10000 use SPECIAL_RATE
10001 to 99999999 use STANDARD RATE

Under these circumstances you do not want to maintain the STANDARD_RATE multiple times in different Value Definition Ranges, but once then link that definition into the Range. In this case you can use either the Graded or Total Calculation, in which case the value1 column contains the Value Definition ID to be used for that Range.

Range Offsets

Range Offsets will typically be used with the "Graded" Calculation Types. The DIR function takes a value called range which is used to work out how to calculate the value. For Example, using Val Def 2 from above, if the range passed is 2200, the calculation will be 1000 at ENHANCED_RATE and 1200 at SPECIAL_RATE. However the Range Offset allows the caller to take into account any previous calculations performed that could affect the range. For Example, if a Tax is split into Category 1 and Category 2, both of which share a "free pay limit". Thus, Category 1 may have already accounted for 500 of the range.... therefore the calculation is passed a range offset of 500 hence the result becomes.... 500 at ENHANCED RATE and 1700 at SPECIAL RATE

Dynamic Ranges

Dynamic Ranges allows the formula to manipulate the Range Item Ranges. This will typically be used when a localisation has ranges expressed in weekly or monthly amounts by the legislation, but needs to convert into the Payroll frequency amounts. For example, the legislation has defined the weekly limits, but we are running a 2weekly payroll in which case the limits need to be adjusted. In order to do this there are 2 new functions:-
DEFINE_RANGE (range_name)
DEFINE_RANGE_ITEM(range_name, low_value, high_value)

e.g.
DEFINE_RANGE('TEST Range')
DEFINE_RANGE_ITEM('TEST Range', 0, 300)
DEFINE_RANGE_ITEM('TEST Range', 301, 999999999)

DEFINE_RANGE this function simply just creates the name of the Dynamic Range in memory, if one already exists with this name it simply OVERWRITES it.
DEFINE_RANGE_ITEM simply attached the range denoted by the low and high values to the Dynamic Range indicated by the range name parameter. Note, this function currently must be called in value order and must be called the same number of time there are Range Items for the Value definition you are trying to override.

CALL_CALC_VALUE Formula
Finally, we have created the CALL_CALC_VALUE formula which wraps some of these concepts into a single formula. The formula has the following parameters:-

CALL_CALC_VALUE
base - number - mandatory
range_value - number - optional
calculation_factor - number - optional
range_offset - number - optional
range_low_override - number, number array - optional
range_high_override - number, number array - optional

Output
ded_amount - number

The formula only uses the CALC_DIR_VALUE function at present and not the CALC_DIR_VALUE_TEXT.

 

Link: http://myforums.oracle.com/jive3/thread.jspa?messageID=4493231