CREATE MATERIALIZED VIEW insights.holding_values_with_accruals_mv AS
SELECT
h.account_id,
h.asset_id,
h.dim_value_date,
h.type,
h.currency_code,
h.market_value,
h.average_cost_per_unit * h.purchased_quantity AS average_cost,
h.average_cost_per_unit,
h.purchased_quantity,
CASE
WHEN NOT a.account_id IS NULL
THEN h.market_value + a.accrued_amount
WHEN NOT h.deposit_profit_accrued IS NULL
THEN h.market_value + h.deposit_profit_accrued
ELSE h.market_value
END AS fair_value,
CASE
WHEN NOT a.account_id IS NULL
THEN a.accrued_amount
WHEN NOT h.deposit_profit_accrued IS NULL
THEN h.deposit_profit_accrued
ELSE CAST(0 AS DECIMAL)
END AS accrued_value,
CASE
WHEN NOT a.account_id IS NULL
THEN a.accrued_value_system_currency
WHEN NOT h.deposit_profit_accrued IS NULL
THEN h.deposit_profit_accrued * COALESCE(fx_sys.rate, CASE WHEN h.currency_code = 'SAR' THEN 1 ELSE NULL END)
ELSE CAST(0 AS DECIMAL)
END AS accrued_value_system_currency,
h.market_value * COALESCE(fx_sys.rate, CASE WHEN h.currency_code = 'SAR' THEN 1 ELSE NULL END) AS market_value_system_currency,
COALESCE(
h.total_cost_system_currency,
(
h.average_cost_per_unit * h.purchased_quantity
) * COALESCE(fx_sys.rate, CASE WHEN h.currency_code = 'SAR' THEN 1 ELSE NULL END)
) AS average_cost_system_currency,
COALESCE(
h.average_cost_per_unit_system_currency,
h.average_cost_per_unit * COALESCE(fx_sys.rate, CASE WHEN h.currency_code = 'SAR' THEN 1 ELSE NULL END)
) AS average_cost_per_unit_system_currency,
CASE
WHEN NOT a.account_id IS NULL
THEN (
h.market_value + a.accrued_amount
) * COALESCE(fx_sys.rate, CASE WHEN h.currency_code = 'SAR' THEN 1 ELSE NULL END)
WHEN NOT h.deposit_profit_accrued IS NULL
THEN (
h.market_value + h.deposit_profit_accrued
) * COALESCE(fx_sys.rate, CASE WHEN h.currency_code = 'SAR' THEN 1 ELSE NULL END)
ELSE h.market_value * COALESCE(fx_sys.rate, CASE WHEN h.currency_code = 'SAR' THEN 1 ELSE NULL END)
END AS fair_value_system_currency
FROM insights.holding_values_raw_mv AS h
LEFT JOIN insights.accruals_agg_mv AS a
ON a.account_id = h.account_id
AND a.asset_id = h.asset_id
AND a.dim_value_date = h.dim_value_date
AND a.type = h.type
AND a.currency_code = h.currency_code
LEFT JOIN asset_service.foreign_exchange_rates_eod_ft AS fx_sys
ON fx_sys.source_currency_code = h.currency_code
AND fx_sys.target_currency_code = 'SAR'
AND fx_sys.date = h.dim_value_date
WHERE
NOT fx_sys.rate IS NULL OR h.currency_code = 'SAR'