CREATE MATERIALIZED VIEW adib_rm.party_portfolios_mv
WITH (
backfill_order=FIXED(olap.service_types_dm -> olap.party_involvements_dm)
) AS
WITH party_portfolio_pairs AS (
SELECT
inv.party_id,
inv.entity_id AS portfolio_id,
MIN(inv.customer_relationship_id) AS customer_relationship_id
FROM olap.party_involvements_dm AS inv
JOIN party.customer_relationships AS cr
ON cr.id = inv.customer_relationship_id
AND cr.type = 'CUSTOMER'
AND cr.status = 'ACTIVE'
AND cr.disabled_at IS NULL
WHERE
inv.entity_type = 'PORTFOLIO'
AND inv.status = 'ACTIVE'
AND inv.disabled_at IS NULL
AND inv.effective_from <= CURRENT_TIMESTAMP
AND (
inv.effective_to IS NULL OR inv.effective_to > CURRENT_TIMESTAMP
)
GROUP BY
inv.party_id,
inv.entity_id
)
SELECT
pp.party_id,
JSONB_AGG(
JSONB_BUILD_OBJECT(
'id',
p.portfolio_id,
'name',
p.name,
'number',
p.number,
'serviceType',
st.type,
'marketValue',
JSONB_BUILD_OBJECT(
'amount',
CAST(COALESCE(ips.market_value, ps.market_value) AS VARCHAR),
'currencyCode',
p.base_currency_code
),
'marketValueSystemCurrency',
JSONB_BUILD_OBJECT(
'amount',
CASE
WHEN NOT ips.market_value IS NULL
THEN CAST(ips.market_value_system_currency AS VARCHAR)
ELSE CAST(ps.market_value_system_currency AS VARCHAR)
END,
'currencyCode',
lp.base_currency_code
),
'unrealizedGainLoss',
JSONB_BUILD_OBJECT(
'value',
JSONB_BUILD_OBJECT(
'amount',
CASE
WHEN NOT ips.market_value IS NULL
THEN CAST((
ips.market_value - ips.total_average_cost
) AS VARCHAR)
ELSE CAST(pnl.unrealized_gain_loss AS VARCHAR)
END,
'currencyCode',
p.base_currency_code
),
'percentage',
CASE
WHEN NOT ips.market_value IS NULL
THEN CASE
WHEN ips.total_average_cost IS NULL OR ips.total_average_cost = 0
THEN NULL
ELSE CAST((
(
ips.market_value - ips.total_average_cost
) / ips.total_average_cost
) AS VARCHAR)
END
ELSE CASE
WHEN pnl.total_average_cost IS NULL OR pnl.total_average_cost = 0
THEN NULL
ELSE CAST((
pnl.unrealized_gain_loss / pnl.total_average_cost
) AS VARCHAR)
END
END
)
) ORDER BY p.portfolio_id
) AS portfolios
FROM party_portfolio_pairs AS pp
JOIN adib_rm.portfolios_plain_mv AS p
ON p.portfolio_id = pp.portfolio_id AND p.closing_date IS NULL
JOIN olap.service_types_dm FOR SYSTEM_TIME AS OF PROCTIME() AS st
ON st.service_type_id = p.service_type_id
LEFT JOIN party.lifecycle_profiles AS lp
ON lp.customer_relationship_id = pp.customer_relationship_id
AND lp.disabled_at IS NULL
LEFT JOIN insights.portfolio_to_account_groups_mv AS pag
ON pag.portfolio_id = p.portfolio_id AND pag.type = 'all'
LEFT JOIN insights.position_snapshot_mv AS ps
ON ps.account_group_id = pag.account_group_id
AND ps.position_type = 'POSITION'
AND ps.currency_code = p.base_currency_code
LEFT JOIN insights.intraday_position_summary_mv AS ips
ON ips.account_group_id = pag.account_group_id
AND ips.position_type = 'POSITION'
AND ips.currency_code = p.base_currency_code
LEFT JOIN adib_rm.pnl_snapshot_mv AS pnl
ON pnl.account_group_id = pag.account_group_id
AND pnl.position_type = 'POSITION'
AND pnl.currency_code = p.base_currency_code
GROUP BY
pp.party_id