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.

 

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 of an activity that was reversed

RateLockDate

datetime

NULL

 Date of 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

Amount in currency available to purchase units

ValuationUnits

decimal(18,10)

NULL

Number of units purchased

ValuationGainLoss

money

NULL

Gain or loss experienced by the company due to backdating

ValuationPrincipal

money

NULL

Principal balance

GainLossOnShadow

money

NULL

Gain/loss the company experiences because of the reversal effective on a different date than the trade dates

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 money was removed.

PriceDate

 

 

This is used for Unit Link Funds.  This is the guarantee date which can be different from the effective date.

GainLossPriceDate

 

 

The trade date.  Used to help calculate ValuationGainLoss.

ShadowGainLossPriceDate

 

 

The trade date for the reversal activity.  Used to calculate GainLossOnShadow.

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

Link To AsFund

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 working calendar which associates 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

Dollar amount of the asset value

UnitValue

Number(18,10)

NULL

For non-unit linked funds, it is the price for the units.

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

From AsCode.CodeValue  where CodeName = AsCodeAllocationType

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