Retrieving Rates with Arrays

Retrieving Rates with Arrays
Oracle® Rules Palette Release 9.1 E14640_01

The RATEARRAY function of OIPA can be configured to return a collection (array) of rates from the rate related tables based on the parameters it is passed. You can also manipulate arrays to further manager the rates that are returned.

The two primary tables used in such a query are:

AsRateGroup &endash; A table that stores the criteria group information, rating method, date and other descriptive rate information.
AsRate &endash; A table that stores the criteria group values from AsCodes, rating and rate.

The AsRate and AsRateGroup tables can be joined, for query purposes, via the RateGroupGUID or RateDescription fields.

AsRate Group Table and AsRate Table

The Integer Criteria column of the AsRate table is used as the main method for rate indexing. Rate indexing is a mechanism used by insurance companies to track the amount of time or change affecting an insurance rate. This form of indexing should not be confused with array indexes. For example, a company can use Integer Criteria as the method of rating because they use ”r;duration in years a policy is in effect” as a rate index. The database does not use this as an index.

Rate Group tables with query statement

Note: There is also a math variable type named RATE, which functions the same as RATEARRAY.

RATEARRAY Configuration

All criteria needed for any call to the RATEARRAY function must be included in the query parameters and taken from what was entered for the rates. The <Parameter> tag values must be defined in the MathVariables section of configuration prior to use in the RATEARRAY function. Examples of commonly used parameters are RateDescription, ActiveFromDate and EffectiveDate.

Note: If you list a parameter, the value must be the correct datatype or it will process in error. You will also receive an error if the value is null.

Parameters

REQUIRED: RateDescription from AsRateGroup defines the set of rates associated to that description. There must be a perfect match between the value of the MathVariable and the data in the table column. There can be no wildcard searches or partial name searches.
REQUIRED: EffectiveDate from AsRateGroup, which is the Rate Activation Date used to group rates by date.

Note: The variable value will be greater than or equal to the column data that results from the execution of RATEARRAY. For multiple entries in AsRateGroup with the same name, at no time will two sets of rates be available for a given rate active date. And at no time can there be a given rate active date without a rate set. The possible scenarios are below in the ”r;Examples of EffectiveDate/ActiveDate Combinations.”

ActiveDate: ActiveToDate from AsRateGroup. The variable value will be greater than or equal to the column data that results from the execution of RATEARRAY. The possible scenarios are below in the ”r;Examples of EffectiveDate/ActiveDate Combinations.”
RateCriteria: The parameters are from AsRateGroup and provide an additional way to filter rate data. The names of the parameters are stored in AsRateGroup, the values are in AsRate. For example, the value of ”r;Gender” for Criteria1, ”r;Tobacco” for Criteria2 and ”r;Age” for IntegerCriteria. Therefore, the three NAME attributes value in any order will be ”r;Gender”, ”r;Tobacco” and ”r;Age”. The data type for the variables feeding data to all of the criteria must be TEXT except for IntegerCriteria, as it must be an INTEGER.

Start and End Index Attribute

The RATEARRAY function has a start and end index attribute that determines the records that are retrieved based on either the integer or date criteria start and end index. Your start and end index can be configured to best meet the needs of the business. Hence if you only wanted to retrieve rates for ages 30 to 39, you should use these attributes.

The StartIndex variable indicates what IntergerCriteria in AsRate you are going to begin with when retrieving values for your RateArray. The array index for identification begins with 0. The StartIndex has no effect on the actual array index.

The EndIndex indicates the last value in IntegeraCriteria that will be used in the function. The EndIndex does not include the last value to be retrieved so you need to set the EndIndex value to be one greater than the length of the array.

XML Sample with Rate Array

Manipulating an Array of Rates

After retrieving an array of rates, you may want to further manipulate the array. OIPA has various functions that can be used in conjunction with RATEARRAY.

The available array functions in are:

AggregateFunction
ArrayFunction
Expand
InsertItem & InsertItems
RemoveItem & RemoveItems
Transform

For further information regarding how these functions operate, please enroll in the Oracle Insurance Policy Administration Configuration IV course offered by Oracle University.