Redesign of Point in Time (PIT) Valuation Storage

Point in Time valuation generates data providing valuation at the start of an activity and at the end of an activity when money has been added, removed or moved around the investment options. This data occupied two separate records for each of the fund's deposits, with deposits summarized up to the fund at start and end of activity and each fund summarized to the policy at start and end of activity. This improvement combines the data into one record for each of the entities; deposits, funds and policy. For example, if the system valued 3 existing deposits in each of 4 different funds, the system has been recording 24 records for the deposits, 8 summarizing to the funds and 2 summarizing to the policy; 34 records total. The improvement reduces the volume to 12 records for the deposits, 4 records for the funds and 1 record for the policy; 17 records total. Other scenarios will result in a less profound difference, but overall expected space savings will be somewhere less than but close to 50%.

 

In the past customers had a choice to record ending valuation records only where money moved or for every fund and/or deposit. This feature eliminates the former option and all ending valuation data will be stored regardless of money movement affecting the fund's/deposit's value. In addition, the system allowed optional storage at the start of an activity. That option has been eliminated and valuation at the start of an activity will always be recorded. This enhances valuation performance. With this feature, an activity's beginning valuation always starts with the ending valuation of the prior activity. When that ending valuation was sometimes recorded, OIPA was required to search all of valuation history to pick up significant ending fund values.

Required Manual Configuration Updates

Custom SQL queries that access the CashValue, Principal, RawCashValue and Units columns on AsPolicyValue, AsFundValue and AsDepositValue will need to be updated on a case by case basis to reference the correct new columns. The NetGain records written to AsDepositValueField, AsFundValueField and AsPolicyValueField must be identified as a value for the beginning or end of valuation and were renamed to BeginNetGain and EndNetGain. If NetGain associated to a "begin" valuation record is pulled by SQL statement, the statement must be altered to pull BeginNetGain. If NetGain associated to an "end" valuation record is pulled by SQL statement, the statement must be altered to pull EndNetGain.

PointInTimeValuation Business Rule has been deprecated and should be removed; if the rule exists in the DB, OIPA will ignore it.

Upgrade Utility Updates:

This feature will convert existing PIT Valuation records to the new format by the v11.3.0.1 Upgrade Utility. The upgrade utility will:

  • Update the necessary database tables / columns updates:
Table

Column

Definition

AsFundValue

CashValue

remove column (replaced by EndCashValue and BeginCashValue)

Principal

remove column (replaced by EndPrincipal and BeginPrincipa)

RawCashValue

remove

column (replaced by EndRawCashValue and BeginRawCashValue)

Units

remove column (replaced by EndUnits and BeginUnits)

EndCashValue

Add column to hold CashValue where BeginEndFlag would have been E

BeginCashValue

Add column to hold CashValue where BeginEndFlag would have been B

EndPrincipal

Add column to hold Principal where BeginEndFlag would have been E

BeginPrincipal

Add column to hold Principal where BeginEndFlag would have been B

EndRawCashValue

Add column to hold RawCashValue where BeginEndFlag would have been E

BeginRawCashValue

Add column to hold RawCashValue where BeginEndFlag would have been B

EndUnits

Add column to hold Units where BeginEndFlag would have been E

BeginUnits

Add column to hold Units where BeginEndFlag would have been B

BeginEndFlag

remove column

Gain

remove column (replaced by BeginNetGain and EndNetGain in AsFundValueField)

AsDepositValue

CashValue

remove column (replaced by EndCashValue and BeginCashValue)

Principal

remove column (replaced by EndPrincipal and BeginPrincipal)

RawCashValue

remove column (replaced by EndRawCashValue and BeginRawCashValue)

Units

remove column (replaced by EndUnits and BeginUnits)

EndCashValue

Add column to hold CashValue where BeginEndFlag would have been E

BeginCashValue

Add column to hold CashValue where BeginEndFlag would have been B

EndPrincipal

Add column to hold Principal where BeginEndFlag would have been E

BeginPrincipal

Add column to hold Principal where BeginEndFlag would have been B

EndRawCashValue

Add column to hold RawCashValue where BeginEndFlag would have been E

BeginRawCashValue

Add column to hold RawCashValue where BeginEndFlag would have been B

EndUnits

Add column to hold Units where BeginEndFlag would have been E

BeginUnits

Add column to hold Units where BeginEndFlag would have been B

BeginEndFlag

remove column

Gain

remove column (replaced by BeginNetGain and EndNetGain in AsDepositValueField)

AsPolicyValue

CashValue

remove column (replaced by EndCashValue and BeginCashValue)

Principal

remove column (replaced by EndPrincipal and BeginPrincipal)

EndCashValue

Add column to hold CashValue where BeginEndFlag would have been E

BeginCashValue

Add column to hold CashValue where BeginEndFlag would have been B

EndPrincipal

Add column to hold Principal where BeginEndFlag would have been E

BeginPrincipal

Add column to hold Principal where BeginEndFlag would have been B

BeginEndFlag

remove column

Gain

remove column (replaced by BeginNetGain and EndNetGain in AsPolicyValueField)

  • Convert data to the new structure (collapsing the Begin and End records into a single record), as well as removing the extraneous records from AsPolicyValue, AsPolicyValueField, AsFundValue, AsFundValueField and AsDepositValue and AsDepositValueField.
  • Update WriteValuationElements Business Rule to replace any <Element> value of NetGain with a BeginNetGain and EndNetGain <Element> value.