92 lines
3.4 KiB
SQL
92 lines
3.4 KiB
SQL
-- Contracts with an MtM strategy, pricing_component details, and latest valuation snapshot
|
|
SELECT
|
|
'Purchase' AS side,
|
|
pp.number AS contract,
|
|
pl.quantity AS contracted_qty,
|
|
ms.name AS strategy_name,
|
|
msc.name AS scenario_name,
|
|
msc.valuation_date,
|
|
msc.use_last_price,
|
|
cur.symbol AS strategy_currency,
|
|
-- MtM component (pricing_component linked via strategy)
|
|
pc_mtm.price_source_type AS component_price_source,
|
|
pc_mtm.ratio AS component_ratio_pct,
|
|
ft.name AS component_fix_type,
|
|
pi.price_desc AS component_curve,
|
|
pc_cal.name AS component_calendar,
|
|
pc_mtm.auto AS component_auto,
|
|
pc_mtm.fallback AS component_fallback,
|
|
-- Latest valuation (physical price row)
|
|
vv.date AS last_valuation_date,
|
|
vv.price AS last_valuation_price,
|
|
vv.quantity AS last_valuation_qty,
|
|
vv.amount AS last_valuation_amount,
|
|
vv.mtm AS portfolio_mtm
|
|
FROM purchase_strategy ps
|
|
JOIN purchase_line pl ON pl.id = ps.line
|
|
JOIN purchase_purchase pp ON pp.id = pl.purchase
|
|
JOIN mtm_strategy ms ON ms.id = ps.strategy
|
|
JOIN mtm_scenario msc ON msc.id = ms.scenario
|
|
JOIN currency_currency cur ON cur.id = ms.currency
|
|
LEFT JOIN pricing_component pc_mtm ON pc_mtm.strategy = ms.id
|
|
LEFT JOIN price_fixtype ft ON ft.id = pc_mtm.fix_type
|
|
LEFT JOIN price_price pi ON pi.id = pc_mtm.price_index
|
|
LEFT JOIN price_calendar pc_cal ON pc_cal.id = pc_mtm.calendar
|
|
LEFT JOIN LATERAL (
|
|
SELECT date, price, quantity, amount, mtm
|
|
FROM valuation_valuation
|
|
WHERE line = pl.id
|
|
AND type = 'pur. priced'
|
|
AND reference = 'Purchase/Physic'
|
|
ORDER BY date DESC
|
|
LIMIT 1
|
|
) vv ON TRUE
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
'Sale',
|
|
ss.number,
|
|
sl.quantity,
|
|
ms.name,
|
|
msc.name,
|
|
msc.valuation_date,
|
|
msc.use_last_price,
|
|
cur.symbol,
|
|
pc_mtm.price_source_type,
|
|
pc_mtm.ratio,
|
|
ft.name,
|
|
pi.price_desc,
|
|
pc_cal.name,
|
|
pc_mtm.auto,
|
|
pc_mtm.fallback,
|
|
vv.date,
|
|
vv.price,
|
|
vv.quantity,
|
|
vv.amount,
|
|
vv.mtm
|
|
FROM sale_strategy sa
|
|
JOIN sale_line sl ON sl.id = sa.sale_line
|
|
JOIN sale_sale ss ON ss.id = sl.sale
|
|
JOIN mtm_strategy ms ON ms.id = sa.strategy
|
|
JOIN mtm_scenario msc ON msc.id = ms.scenario
|
|
JOIN currency_currency cur ON cur.id = ms.currency
|
|
LEFT JOIN pricing_component pc_mtm ON pc_mtm.strategy = ms.id
|
|
LEFT JOIN price_fixtype ft ON ft.id = pc_mtm.fix_type
|
|
LEFT JOIN price_price pi ON pi.id = pc_mtm.price_index
|
|
LEFT JOIN price_calendar pc_cal ON pc_cal.id = pc_mtm.calendar
|
|
LEFT JOIN LATERAL (
|
|
SELECT date, price, quantity, amount, mtm
|
|
FROM valuation_valuation
|
|
WHERE line = sl.id
|
|
AND type = 'sale priced'
|
|
AND reference = 'Sale/Physic'
|
|
ORDER BY date DESC
|
|
LIMIT 1
|
|
) vv ON TRUE
|
|
|
|
ORDER BY side, contract;
|
|
|
|
|
|
select * from price_price
|