Oracle® Insurance Rules Palette 10.1.2.0 E59346-01

Retrieve Map Group Values

In order to retrieve the rate, a SQL needs to be written that links all three tables. The same rules and standards for SQL apply for the AsMapSeries lookup.  

For example, to lookup the GMIB rate, assume that the following variables have been set:

• PlanGUID: E99F8927-B97E-40F4-B6A3-D53C6BA00F82

• SegmentRateActiveDate: 3/5/2006

• IssueState: 32

SELECT AsMapValue.IntValue FROM AsMapValue

JOIN AsMapGroup ON AsMapGroup.MapGroupGUID = AsMapValue.MapGroupGUID

AND AsMapGroup.MapGroupDescription = 'GMIBMaximumAge'

JOIN AsMapCriteria ON AsMapCriteria.MapValueGUID = AsMapValue.MapValueGUID

AND AsMapCriteria.MapCriteriaName = 'PlanGUID'

AND AsMapCriteria.TextValue = '[Policy:PlanGUID]'

JOIN AsMapCriteria AS R ON R.MapValueGUID = AsMapValue.MapValueGUID

AND R.MapCriteriaName = 'StateCode' AND ((R.TextValue = '[IssueState]') OR (R.TextValue = '*'))

ORDER BY R.TextValue ASC,S.TextValue ASC FETCH FIRST ROW ONLY

Without the FETCH FIRST ROW ONLY, the Query would return both rates associated with the GMIBMaximumAge MapGroup. Due to the ordering of R.TextValue, NY will appear prior to *, and therefore the FETCH FIRST ROW ONLY will correctly return an integer value of 65.  

The above SQL shows:

• Hardcoded values (AsMapGroup.MapGroupDescription = 'GMIBMaximumAge'),

• Policy fields (AsMapCriteria.TextValue = '[Policy:PlanGUID]')

• A math variable (R.MapCriteriaName = 'StateCode' AND ((R.TextValue = '[IssueState]') OR (R.TextValue = '*')))

• The use of * (as seen in the StateCode criteria).

Free Look SQL Example

SELECT TOP 1 AsMapValue.IntValue FROM AsMapValue

JOIN AsMapGroup ON AsMapGroup.MapGroupGUID = AsMapValue.MapGroupGUID

AND AsMapGroup.MapGroupDescription = 'FreeLookDays'

JOIN AsMapCriteria ON AsMapCriteria.MapValueGUID = AsMapValue.MapValueGUID

AND AsMapCriteria.MapCriteriaName = 'PlanGUID'

AND (AsMapCriteria.TextValue = '[Policy:PlanGUID]'

OR AsMapCriteria.TextValue = '*')

JOIN AsMapCriteria AS Q ON Q.MapValueGUID = AsMapValue.MapValueGUID AND Q.MapCriteriaName = 'ReplacementType'

AND (Q.TextValue='[ReplacementCriteria]')

JOIN AsMapCriteria AS R ON R.MapValueGUID = AsMapValue.MapValueGUID AND R.MapCriteriaName ='StateCode' AND (R.TextValue = '[Policy:IssueStateCode]' OR R.TextValue='*') ORDER BY R.TextValue DESC