Valuation Database Definitions
The following database definitions describe the tables used in valuation and the associated columns.
Select a link to view a specific database table and the associated definitions.
-
PIT Database Tables
AsValuation Data Definitions
The AsValuation table contains the basic data set for each deal at the fund level.
|
Name |
Datatype |
Null Option |
Description |
|---|---|---|---|
|
ValuationGUID |
uniqueidentifier |
NOT NULL |
Primary Key. A separate ValuationGUID is written for each fund affected by the transaction. |
|
FundGUID |
uniqueidentifier |
NOT NULL |
Link to AsFund |
|
PolicyGUID |
uniqueidentifier |
NOT NULL |
Link To AsPolicy |
|
ActivityGUID |
uniqueidentifier |
NOT NULL |
Link to AsActivity |
|
EffectiveDate |
datetime |
NOT NULL |
The activity as of date. |
|
ActiveFromDate |
datetime |
NOT NULL |
The system date. |
|
ActiveToDate |
datetime |
NULL |
The system date when the associated activity was reversed/undone. |
|
RateLockDate |
datetime |
NULL |
Date used for rate lookup. |
|
MoneyTypeCode |
varchar(2) |
NOT NULL |
Indicates the type of money used and can be found in AsCodeMoneyTypeCode. |
|
RemovedFromDepositGUID |
uniqueidentifier |
NULL |
DepositGUID where money is being removed. |
|
SeedDepositGUID |
uniqueidentifier |
NULL |
Original DepositGUID where money is deposited. |
|
TaxlotGUID |
uniqueidentifier |
NULL |
Foreign key into AsTaxLot. |
|
TaxlotTradeDate |
datetime |
NULL |
|
|
TaxlotGainLoss |
money |
NULL |
|
|
ValuationAmount |
money |
NULL |
A monetary amount of purchase or removal. |
|
ValuationUnits |
decimal(18,10) |
NULL |
Number of units purchased or removed. |
|
ValuationGainLoss |
money |
NULL |
Gain or loss due to backdated activities (activities effective on a date different than the system date). |
|
ValuationPrincipal |
money |
NULL |
Principal balance for simple interest calculations. |
|
GainLossOnShadow |
money |
NULL |
Gain/loss due to reversal/undo of activities that are effective on dates different than the system date. |
|
Bucket |
Used for Equity Index Fund. |
||
|
FundCurrenyAmount |
The valuation amount converted to a fund’s currency. |
||
|
CurrencyConversionCost |
Cost of converting the plan’s currency to the fund’s currency. Usually seen on a premium. |
||
|
DepositDepletedDate |
Date the deposit’s value was completely removed. |
||
|
PriceDate |
This is used for Unit Link Funds. This is the guarantee date which can be different from the effective date. |
||
|
GainLossPriceDate |
This is used for Unit Link Funds. The date that purchases and removals are actually bought and sold. This will determine the gain/loss for the company. |
||
|
ShadowGainLossPriceDate |
This is used for Unit Link Funds. The date that purchases and removals are actually bought and sold when the activity is reversed/undone. |
||
|
BareSpreadAmount |
Difference in unit value between bear price and bid or offer price. Deposits use offer price. Withdrawals use bear price. |
||
|
CashValueAmount |
AsFund Data Definitions
|
Name |
Datatype |
Null Option |
Description |
|---|---|---|---|
|
FundGUID |
uniqueidentifier |
NOT NULL |
Primary Key |
|
PlanGUID |
uniqueidentifier |
NOT NULL |
Link to AsPlan |
|
FundName |
uniqueidentifier |
NOT NULL |
Fund's name. |
|
StatusCode |
uniqueidentifier |
NOT NULL |
Indicates the status of the fund |
|
TypeCode |
datetime |
NOT NULL |
From AsCode.CodeValue where CodeName = AsCodeFundType |
|
XMLData |
datetime |
||
|
RemovalPrecendence |
datetime |
NULL |
|
|
RemovalMethodCode |
datetime |
NULL |
|
|
CurrencyCode |
varchar(2) |
NOT NULL |
The ISO 4217 three letter currency code that is used for the fund. |
|
CalendarCode |
uniqueidentifier |
NOT NULL |
The fund's working calendar which associates it to a market. |
|
DepositLevelTracking |
singlecharacter |
NULL |
AsNetAssetValue Data Definitions
|
Name |
Datatype |
Null Option |
Description |
|---|---|---|---|
|
NetAssetValueGUID |
uniqueidentifier |
NOT NULL |
Primary Key |
|
FundGUID |
uniqueidentifier |
NOT NULL |
Link to AsFund. |
|
EffectiveDate |
Date |
NOT NULL |
Date of the unit deals. |
|
NetAssetValue |
Decimal(19,4) |
NULL |
Monetary value of the asset value. |
|
UnitValue |
Number(18,10) |
NULL |
Monetary value of a unit of the asset. |
|
Dividend |
Decimal(18,10) |
NULL |
Dividend Amount |
|
MortatlityAndExpense |
Decimal(18,10) |
NULL |
The M&E for the asset on the effective date. |
|
BareUnitValue |
Number(18,10) |
NULL |
System does not set to bid if no bare or the same. Up to user to supply value. |
|
OfferUnitValue |
Number(18,10) |
NULL |
Up to user to supply value. If there is no Offer price, it should be set by the user to the Unit Value. |
AsAllocations Data Definitions
|
Name |
Datatype |
Null Option |
Description |
|---|---|---|---|
|
AllocationGUID |
CHARACTER(36) |
NOT NULL |
Primary key |
|
GroupGUID |
CHARACTER(36) |
NULL |
Identifies the group to which this allocation belongs |
|
TypeCode |
VARCHAR(2) |
NOT NULL |
01: Plan Default 02: Future Allocations 03: Activity Allocation 05:Segment Default 51: Original Benefit 61: Directed Deductions 99: Deleted Allocation (used by Reversal) 100: Conversion Premium 101: Conversion Premium Tax 102: Conversion Interest 103: Conversion Interest Expense &endash; Gain 104: Conversion Interest Expense &endash; Loss 105: Conversion Optional Death Benefit Fee 106: Conversion Withdrawl 107: Conversion Redemption Fee 108: Conversion Annual Contract Fee 109: Conversion Split Deposit Removal 110: Conversion Split Deposit Apply |
|
RelatedGUID |
CHARACTER(36) |
NULL |
Either PolicyGUID, ActivityGUID or PlanGUID |
|
FundGUID |
CHARACTER(36) |
NOT NULL |
Link to AsFund |
|
AllocationMethodCode |
VARCHAR(2) |
NULL |
From AsCode.CodeValue where CodeName ='AsCodeAllocationMethod’ 01:Percent 02:Amount 03:Units 04:Preferred Pro Rata |
|
AllocationPercent |
DECIMAL(18,10) |
NULL |
Percent entered |
|
AllocationAmount |
DECIMAL(38,10) |
NULL |
Amount entered |
|
AllocationUnits |
DECIMAL(18,10) |
NULL |
Units entered |
|
PercentInAllocation |
DECIMAL(18,10) |
NOT NULL |
Prorata calculated percentage |
|
EffectiveDate |
TIMESTAMP |
NULL |
As of date |
AsPlan Data Definitions
| Name |
Datatype |
Null Option | Description |
|---|---|---|---|
|
PlanGUID |
uniqueidentifier |
NOT NULL |
Primary Key |
|
CompanyGUID |
uniqueidentifier |
NOT NULL |
Link to AsCompany. |
|
PlanName |
VARCHAR2 |
NOT NULL |
Name of the plan. |
|
EffectiveDate |
Date |
NULL |
Effective date of the plan inception. |
|
ExpirationDate |
Date |
NULL |
Expiration date of the plan. |
|
DefaultCurrencyCode |
CHAR(3) |
NULL |
The default currency for any currency transactions on policies in this plan. |
|
MarketMakerGUID |
CHAR(36) |
NULL |
Link to AsMarketMaker. |
|
PointInTimeValuation |
CHAR(1) |
NULL |
Point-in-Time valuation indicator. T: Transition from Traditional to Point-in-Time valuation Y: Point-in-Time N | NULL: Traditional valuation |
|
MixedValuation |
CHAR(1) |
NULL |
Mixed valuation indicator. Y:Valuation records written as determined by AsValuationTransition:TransitionDate. N | NULL: Always write Point-in-Time valuation records. |
AsValuationTransition Data Definitions
| Name |
Datatype |
Null Option | Description |
|---|---|---|---|
|
PolicyGUID |
uniqueidentifier |
NOT NULL |
Primary Key |
|
TransitionDate |
Date |
NOTNULL |
Transition date for transition from Traditional to Point-in-Time valuation. |
