51 lines
2.5 KiB
SQL
51 lines
2.5 KiB
SQL
CREATE OR REPLACE VIEW public.vw_utility_physical_mtm_definition AS
|
|
SELECT 'Purchase'::text AS "strContractType",
|
|
pp.id AS "intContractId",
|
|
pl.id AS "intContractLineId",
|
|
pl.quantity AS "dblLineQuantity",
|
|
ms.name AS "strStrategy",
|
|
msc.name AS "strScenario",
|
|
cur.symbol AS "strStrategyCurrency",
|
|
pc_mtm.price_source_type AS "strComponentPriceSource",
|
|
pc_mtm.ratio AS "strComponentRatioPct",
|
|
ft.name AS "strComponentFixType",
|
|
pi.price_desc AS "strComponentCurve",
|
|
pc_cal.name AS "strComponentCalendar",
|
|
pi.id AS "intPriceIndexId",
|
|
TRIM(BOTH FROM regexp_replace((ms.name)::text, '\m[0-9]{4}-[0-9]{2}\M'::text, ''::text, 'g'::text)) AS "strStrategyAltName"
|
|
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)))
|
|
UNION ALL
|
|
SELECT 'Sale'::text AS "strContractType",
|
|
ss.id AS "intContractId",
|
|
sl.id AS "intContractLineId",
|
|
sl.quantity AS "dblLineQuantity",
|
|
ms.name AS "strStrategy",
|
|
msc.name AS "strScenario",
|
|
cur.symbol AS "strStrategyCurrency",
|
|
pc_mtm.price_source_type AS "strComponentPriceSource",
|
|
pc_mtm.ratio AS "strComponentRatioPct",
|
|
ft.name AS "strComponentFixType",
|
|
pi.price_desc AS "strComponentCurve",
|
|
pc_cal.name AS "strComponentCalendar",
|
|
pi.id AS "intPriceIndexId",
|
|
TRIM(BOTH FROM regexp_replace((ms.name)::text, '\m[0-9]{4}-[0-9]{2}\M'::text, ''::text, 'g'::text)) AS "strStrategyAltName"
|
|
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)));;
|