80 lines
4.8 KiB
SQL
80 lines
4.8 KiB
SQL
CREATE OR REPLACE VIEW public.vw_bi_fct_open_position AS
|
|
WITH purchase_position AS (
|
|
SELECT contracts."intTradeId",
|
|
contracts."Trade Category",
|
|
contracts."Trade Type",
|
|
contracts."Delivery Date",
|
|
to_char((contracts."Delivery Date")::timestamp with time zone, 'YYYY-MM'::text) AS delivery_month,
|
|
to_char((contracts."Delivery Date")::timestamp with time zone, 'Mon YYYY'::text) AS delivery_month_label,
|
|
to_char((contracts."Delivery Date")::timestamp with time zone, 'YYYYMM'::text) AS delivery_month_code,
|
|
contracts."Quantity",
|
|
(contracts."Quantity" - COALESCE(delivery.delivered_quantity, (0)::double precision)) AS "Open Position Quantity",
|
|
(((('Deal '::text || (dc."Contract Nb")::text) || ' ('::text) || (dc."Contract Ref")::text) || ')'::text) AS "Label"
|
|
FROM ((vw_bi_fct_trade_line contracts
|
|
JOIN vw_bi_dim_purchase_physical_contract dc ON ((contracts."intTradeId" = dc."intPurchaseLineId")))
|
|
LEFT JOIN ( SELECT ffe."intTradeId",
|
|
sum(ffe."Quantity") AS delivered_quantity
|
|
FROM vw_bi_fct_trade_fulfillment_event ffe
|
|
WHERE ((ffe."Trade Category" = 'Physical'::text) AND (ffe."Trade Type" = 'Purchase'::text))
|
|
GROUP BY ffe."intTradeId") delivery ON ((contracts."intTradeId" = delivery."intTradeId")))
|
|
WHERE ((1 = 1) AND (contracts."Trade Category" = 'Physical'::text) AND (contracts."Trade Type" = 'Purchase'::text))
|
|
), sale_position AS (
|
|
SELECT contracts."intTradeId",
|
|
contracts."Trade Category",
|
|
contracts."Trade Type",
|
|
contracts."Delivery Date",
|
|
to_char((contracts."Delivery Date")::timestamp with time zone, 'YYYY-MM'::text) AS delivery_month,
|
|
to_char((contracts."Delivery Date")::timestamp with time zone, 'Mon YYYY'::text) AS delivery_month_label,
|
|
to_char((contracts."Delivery Date")::timestamp with time zone, 'YYYYMM'::text) AS delivery_month_code,
|
|
contracts."Quantity",
|
|
((contracts."Quantity" - COALESCE(delivery.delivered_quantity, (0)::double precision)) * ('-1'::integer)::double precision) AS "Open Position Quantity",
|
|
(((('Deal '::text || (dc."Contract Nb")::text) || ' ('::text) || (dc."Contract Ref")::text) || ')'::text) AS "Label"
|
|
FROM ((vw_bi_fct_trade_line contracts
|
|
JOIN vw_bi_dim_sale_physical_contract dc ON ((contracts."intTradeId" = dc."intSaleLineId")))
|
|
LEFT JOIN ( SELECT ffe."intTradeId",
|
|
sum(ffe."Quantity") AS delivered_quantity
|
|
FROM vw_bi_fct_trade_fulfillment_event ffe
|
|
WHERE ((ffe."Trade Category" = 'Physical'::text) AND (ffe."Trade Type" = 'Sale'::text))
|
|
GROUP BY ffe."intTradeId") delivery ON ((contracts."intTradeId" = delivery."intTradeId")))
|
|
WHERE ((1 = 1) AND (contracts."Trade Category" = 'Physical'::text) AND (contracts."Trade Type" = 'Sale'::text))
|
|
)
|
|
SELECT purchase_position."intTradeId",
|
|
purchase_position."Trade Category",
|
|
purchase_position."Trade Type",
|
|
purchase_position."Delivery Date",
|
|
purchase_position.delivery_month,
|
|
purchase_position.delivery_month_label,
|
|
purchase_position.delivery_month_code,
|
|
purchase_position."Quantity",
|
|
purchase_position."Open Position Quantity",
|
|
purchase_position."Label"
|
|
FROM purchase_position
|
|
WHERE (purchase_position."Open Position Quantity" <> (0)::double precision)
|
|
UNION ALL
|
|
SELECT sale_position."intTradeId",
|
|
sale_position."Trade Category",
|
|
sale_position."Trade Type",
|
|
sale_position."Delivery Date",
|
|
sale_position.delivery_month,
|
|
sale_position.delivery_month_label,
|
|
sale_position.delivery_month_code,
|
|
sale_position."Quantity",
|
|
sale_position."Open Position Quantity",
|
|
sale_position."Label"
|
|
FROM sale_position
|
|
WHERE (sale_position."Open Position Quantity" <> (0)::double precision)
|
|
UNION ALL
|
|
SELECT ffe."intTradeId",
|
|
'Physical'::text AS "Trade Category",
|
|
'Shipped'::text AS "Trade Type",
|
|
sh."BL Date" AS "Delivery Date",
|
|
to_char((sh."BL Date")::timestamp with time zone, 'YYYY-MM'::text) AS delivery_month,
|
|
to_char((sh."BL Date")::timestamp with time zone, 'Mon YYYY'::text) AS delivery_month_label,
|
|
to_char((sh."BL Date")::timestamp with time zone, 'YYYYMM'::text) AS delivery_month_code,
|
|
ffe."Quantity",
|
|
0 AS "Open Position Quantity",
|
|
(((((('Shipment '::text || (sh."Shipment Nb")::text) || ' ('::text) || (dc."Contract Ref")::text) || ' - '::text) || (sh."Vessel")::text) || ')'::text) AS "Label"
|
|
FROM ((vw_bi_fct_trade_fulfillment_event ffe
|
|
JOIN vw_bi_dim_shipment sh ON ((ffe."intShipmentId" = sh."intShipmentId")))
|
|
JOIN vw_bi_dim_purchase_physical_contract dc ON ((ffe."intTradeId" = dc."intPurchaseLineId")));;
|