CREATE MATERIALIZED VIEW adib_rm.activity_order_events_mv
WITH (
backfill_order=FIXED(asset_service.assets_dm -> order_service.orders)
) AS
SELECT
ord.id,
ord.security_account_id,
ord.portfolio_id,
ord.created_at,
CASE ord.side
WHEN 'TRADE_SIDE_BUY'
THEN 'BUY'
WHEN 'TRADE_SIDE_SELL'
THEN 'SELL'
WHEN 'TRADE_SIDE_SUBSCRIBE'
THEN 'SUBSCRIBE'
WHEN 'TRADE_SIDE_REDEEM'
THEN 'REDEEM'
WHEN 'TRADE_SIDE_DRIP'
THEN 'DRIP'
ELSE ord.side
END AS order_side,
CASE ord.workflow_state
WHEN 'WORKFLOW_STATE_DRAFT'
THEN 'DRAFT'
WHEN 'WORKFLOW_STATE_PENDING_COMPLIANCE'
THEN 'PENDING_COMPLIANCE'
WHEN 'WORKFLOW_STATE_PENDING_APPROVAL'
THEN 'PENDING_APPROVAL'
WHEN 'WORKFLOW_STATE_REQUIRES_MODIFICATION'
THEN 'REQUIRES_MODIFICATION'
WHEN 'WORKFLOW_STATE_TERMINAL'
THEN CASE ord.execution_state
WHEN 'EXECUTION_STATE_FILLED'
THEN 'FILLED'
WHEN 'EXECUTION_STATE_PARTIALLY_FILLED'
THEN 'CANCELED'
WHEN 'EXECUTION_STATE_CANCELLED'
THEN 'CANCELED'
WHEN 'EXECUTION_STATE_REJECTED'
THEN 'REJECTED'
WHEN 'EXECUTION_STATE_EXPIRED'
THEN 'EXPIRED'
ELSE NULL
END
WHEN 'WORKFLOW_STATE_ACTIVE'
THEN CASE ord.execution_state
WHEN 'EXECUTION_STATE_NEW'
THEN 'NEW'
WHEN 'EXECUTION_STATE_ACKNOWLEDGED'
THEN 'NEW'
WHEN 'EXECUTION_STATE_PARTIALLY_FILLED'
THEN 'PARTIALLY_FILLED'
WHEN 'EXECUTION_STATE_PENDING_CANCEL'
THEN 'PENDING_CANCEL'
WHEN 'EXECUTION_STATE_FILLED'
THEN 'FILLED'
ELSE 'NEW'
END
ELSE NULL
END AS order_status,
COALESCE(
CAST((
ord.execution_spec -> 'order_book' -> 'shares' ->> 'value'
) AS DECIMAL),
CAST((
ord.execution_spec -> 'order_book' -> 'money' -> 'amount' ->> 'value'
) AS DECIMAL)
) AS quantity,
COALESCE(ex.filled_quantity, CAST(0 AS DECIMAL)) AS filled_quantity,
COALESCE(ex.average_price, CAST(0 AS DECIMAL)) AS average_price,
COALESCE(ex.total_amount, CAST(0 AS DECIMAL)) AS gross_executed_amount,
COALESCE(ex.total_fees, CAST(0 AS DECIMAL)) AS total_fee,
CAST((
ord.cost_estimate -> 'estimatedGross' -> 'amount' ->> 'value'
) AS DECIMAL) AS estimated_gross_amount,
CAST((
ord.cost_estimate -> 'estimatedNet' -> 'amount' ->> 'value'
) AS DECIMAL) AS estimated_net_amount,
CAST((
ord.cost_estimate -> 'estimatedFees' -> 'amount' ->> 'value'
) AS DECIMAL) AS estimated_fee_amount,
COALESCE(
ex.execution_currency_code,
ord.cost_estimate -> 'estimatedGross' -> 'currencyCode' ->> 'value',
ord.execution_spec -> 'order_book' -> 'limit_price' -> 'currency_code' ->> 'value',
ord.execution_spec -> 'order_book' -> 'money' -> 'currency_code' ->> 'value',
a.issue_currency_code
) AS currency_code,
a.id AS asset_id,
a.name_en AS asset_name_en,
a.name_ar AS asset_name_ar,
a.type AS asset_type,
a.issue_currency_code AS asset_currency_code,
a.ticker AS asset_ticker,
a.isin AS asset_isin
FROM order_service.orders AS ord
LEFT JOIN adib_rm.sdk_order_execution_aggregates_mv AS ex
ON ex.order_id = ord.id
LEFT JOIN asset_service.assets_dm FOR SYSTEM_TIME AS OF PROCTIME() AS a
ON a.id = ord.asset_id
WHERE
ord.created_at >= CURRENT_TIMESTAMP - INTERVAL '2 YEARS'
AND (
NOT (
ord.workflow_state = 'WORKFLOW_STATE_TERMINAL'
AND COALESCE(ord.filled_quantity, CAST(0 AS DECIMAL)) > 0
)
OR COALESCE(ex.filled_quantity, CAST(0 AS DECIMAL)) = COALESCE(ord.filled_quantity, CAST(0 AS DECIMAL))
)