Warehouse Open Lot - Roll Up

Note

Roll Up in Single Row mode is only available in environments 13.x or higher. Roll Up in Batch mode is fully supported.

If you are using EagleML July release or earlier, please add <procedureFlag>PD</procedureFlag> node before the <primaryAssetId> node in order to have the Roll Up work properly in Batch Mode.

Roll Up is the summing of some values from Warehouse Open Lot DB tables and inserting the result values into the fields of Position DB tables.

Instead of a mode, in one of the rules for Warehouse Open Lot data loading, it calls the stored procedure to roll up the values in the HOLDINGDBO.LOT_LEVEL_POSITION DB table into the HOLDINGDBO.POSITION_DETAIL, HOLDINGDBO.POSITION_COST_DETAIL and HOLDINGDBO.POSITION DB tables.

If the batch mode of data loading is used, the stored procedure for roll up will be called in the main rule ‘eagle_default/in/xml/xml-warehouse_v2.xml’.
If the UPSERT mode of data loading, the stored procedure for roll up will be called in the EOFP rule ‘eagle_default/in/xml/xml-dbdirect_invar_eofp.xml’.

All tables bind by the DB field POSITION_ID.

Position Cost Detail DB Table

The following fields of HOLDINGDBO.POSITION_COST_DETAIL DB table are defined by the sum of DB fields from Open Lot tables with the same key (Entity Id + Effective Date + Source):

Fields of of POSITION_COST_DETAIL DB table	Fields of LOT_LEVEL_POSITION DB table are summed	EagleML elements are summed
LOCAL_GAAP_COST_VALUE	LOCAL_GAAP_COST_VALUE	localGaapCostValue
GAAP_COST_VALUE	GAAP_COST_VALUE	gaapCostValue
OID_LTD_INCOME_LOCAL	LOT_LEVEL_POSITION.OID_LTD_INCOME_LOCAL	oidLtdIncomeLocal
AMORT_LTD_LOCAL	AMORTIZATION_LTD_LOCAL	amortizedLtdLocal
OID_LTD_INCOME	OID_LTD_INCOME	oidLtdIncomeBase
AMORT_LTD_BASE	AMORTIZATION_LTD_BASE	amortizedLtdBase

Position Detail DB Table

The following fields of HOLDINGDBO.POSITION_DETAIL DB table are defined by the sum of DB fields from Open Lot tables with the same key (Entity Id + Effective Date + Source Name):

Fields of of POSITION_DETAIL DB table	Fields of LOT_LEVEL_POSITION DB table are summed	EagleML elements are summed	Comments
ORIG_FACE	ORIG_FACE	originalFace
INTEREST_PURCHASED	INT_PURCHASED	intPurchased
SHARE_PAR_VALUE	PAR_OR_SHARES	parOrShares
SETTLE_QUANTITY	SETTLE_QUANTITY	settledQuantity
ACCRUED_INCOME_LOCAL	ACCRUED_INCOME_LOCAL	accruedIncomeLocal
INTEREST_EARNED	INTEREST_EARNED	interestEarned
SECURITY_INCOME	INT_RECEIVED	intReceived
MKT_EXCHANGE_RATE	CURR_FX_RATE	currFxRate	For CURR_FX_RATE an average value is resolved instead of a sum.
LOCAL_MARKET_VALUE	LOCAL_MARKET_VALUE	marketValueLocal
MARKET_VALUE	MARKET_VALUE	marketValueBase
LOCAL_UNREALIZED_GL	LOCAL_UNREALIZED_GL	totalUnrealizedGainLossLocal
UNREALIZED_SEC_GL	UNREALIZED_SEC_GL	urglSecurityBase
BASE_UNREALIZED_GL	BASE_UNREALIZED_GL	totalUnrealizedGainLossBase
UNREALIZED_CURR_GL	UNREALIZED_CURR_GL	urglCurrencyBase
ACCRUED_INCOME	ACCRUED_INCOME	accruedIncome
TIP_LTD_INCOME_LOCAL	TIP_LTD_INCOME_LOCAL	closeTipsIncomeLtdLocal
TIP_LTD_INCOME	TIP_LTD_INCOME	closeTipsIncomeLtdBase
INDX_WEIGHT	INDX_WEIGHT	closeIndexWeight
BOOK_VALUE_LOCAL	BOOK_VALUE_LOCAL	bookValueLocal
BOOK_VALUE	BOOK_VALUE	bookValue
MARKET_VALUE_INCOME	MARKET_VALUE_INCOME	marketValueIncome
MARKET_VALUE_INCOME_LOCAL	MARKET_VALUE_INCOME_LOCAL	marketValueIncomeLocal
NOTIONAL_MARKET_VALUE_LOCAL	NOTIONAL_MARKET_VALUE_LOCAL	notionalMarketValueLocal
NOTIONAL_MARKET_VALUE	NOTIONAL_MARKET_VALUE	notionalMarketValueBase
The following fields are supported for environments 2017 R2.x or higher
ORIGINAL_COST_AMOUNT	ORIGINAL_COST_AMOUNT	originalCostAmount
PROCESSING_ILB_LTD_L	PROCESSING_ILB_LTD_L	processingIlbLtdLocal
PROCESSING_ILB_LTD_B	PROCESSING_ILB_LTD_B	processingIlbLtdBase
PRICE (average value)	PRICE	price

Position DB Table

The following fields of HOLDINGDBO.POSITION DB table are defined by the sum of DB fields from Open Lot tables with the same key (Entity Id + Effective Date + Source Name):

Fields of of POSITION DB table	Fields of LOT_LEVEL_POSITION DB table are summed	EagleML elements are summed
TOTAL_MARKET_VALUE	MARKET_VALUE	marketValueBase
TOTAL_MARKET_VALUE_INCOME	MARKET_VALUE_INCOME	marketValueIncome
TOTAL_PAR_VALUE	PAR_OR_SHARES	parOrShares
TOTAL_LOCAL_MARKET_VALUE	LOCAL_MARKET_VALUE	marketValueLocal
TOTAL_ACCRUED_INCOME_LOCAL	ACCRUED_INCOME_LOCAL	accruedIncomeLocal
TOTAL_ACCRUED_INCOME	ACCRUED_INCOME	accruedIncome
TOTAL_BOOK_VALUE	BOOK_VALUE	bookValue