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