71 lines
3.2 KiB
SQL
71 lines
3.2 KiB
SQL
CREATE OR REPLACE VIEW public.vw_bi_itsa_fct_full_pnl AS
|
|
WITH trade AS (
|
|
SELECT pc."intPurchaseLineId" AS "intTradeId",
|
|
(pc."intPurchaseLineId" || '_0'::text) AS "strTradeId",
|
|
'Purchase'::text AS "strTradeType",
|
|
pc."strBookName"
|
|
FROM vw_utility_dim_physical_purchase_contract pc
|
|
WHERE ((1 = 1) AND (pc."intPurchaseLineId" > 0))
|
|
UNION ALL
|
|
SELECT sc."intSaleLineId" AS "intTradeId",
|
|
('0_'::text || sc."intSaleLineId") AS "strTradeId",
|
|
'Sale'::text AS "strTradeType",
|
|
sc."strBookName"
|
|
FROM vw_utility_dim_physical_sale_contract sc
|
|
WHERE ((1 = 1) AND (sc."intSaleLineId" > 0))
|
|
)
|
|
SELECT
|
|
CASE
|
|
WHEN ((trade."strBookName")::text ~~ '%Projection%'::text) THEN 1
|
|
ELSE 2
|
|
END AS "intPnlTypeId",
|
|
trade."strTradeId" AS "strConformedReferenceId",
|
|
contracts."intPriceGroupId",
|
|
contracts."Line Description",
|
|
contracts."Open Quantity Signed",
|
|
contracts."Price",
|
|
contracts."Open Cost Amount" AS amount,
|
|
contracts."Estimated BL Date" AS "Delivery Date",
|
|
CASE
|
|
WHEN ((trade."strBookName")::text ~~ '%Projection%'::text) THEN 'Expected'::text
|
|
ELSE contracts."Line Group"
|
|
END AS "Line Group",
|
|
COALESCE(book."intBookId", 0) AS "intBookId",
|
|
abs(contracts."Open Quantity Signed") AS "Open Quantity",
|
|
'Section 1'::text AS "Code Section"
|
|
FROM ((vw_bi_itsa_physical_open_costs_v2 contracts
|
|
JOIN trade trade ON ((contracts."intTradeLineId" = trade."intTradeId")))
|
|
LEFT JOIN vw_utility_book_dimension book ON (((trade."intTradeId" = book."intTradeLineId") AND (book."strTradeCategory" = 'Physical'::text) AND (book."strTradeType" = trade."strTradeType"))))
|
|
UNION ALL
|
|
SELECT 3 AS "intPnlTypeId",
|
|
((shipment."intPurchaseLineId" || '_'::text) || shipment."intSaleLineId") AS "strConformedReferenceId",
|
|
CASE
|
|
WHEN ((fee_alloc."Fee Type")::text = 'price'::text) THEN 1
|
|
ELSE 2
|
|
END AS "intPriceGroupId",
|
|
fee_alloc."Fee" AS "Line Description",
|
|
shipment."Shipped Quantity" AS "Open Quantity Signed",
|
|
fee_alloc."Price",
|
|
(fee_alloc."Amount" * fee_alloc."Sign Multiplier") AS amount,
|
|
shipment."BL Date" AS "Delivery Date",
|
|
fee_alloc."Cost Group" AS "Line Group",
|
|
shipment."intBookId",
|
|
abs(shipment."Shipped Quantity") AS "Open Quantity",
|
|
'Section 4'::text AS "Code Section"
|
|
FROM (vw_bi_itsa_fct_shipments_v2 shipment
|
|
LEFT JOIN vw_bi_fct_fee_allocation fee_alloc ON (((shipment."intShipmentId" = fee_alloc."intShipmentId") AND (shipment."intPurchaseLineId" = fee_alloc."intPurchaseLineId") AND (shipment."intSaleLineId" = fee_alloc."intSaleLineId"))))
|
|
UNION ALL
|
|
SELECT 3 AS "intPnlTypeId",
|
|
'0_0'::text AS "strConformedReferenceId",
|
|
2 AS "intPriceGroupId",
|
|
ifrs."Comments" AS "Line Description",
|
|
0 AS "Open Quantity Signed",
|
|
0 AS "Price",
|
|
ifrs."Amount" AS amount,
|
|
ifrs."Adjustment Date" AS "Delivery Date",
|
|
'Monthly Adjustment'::text AS "Line Group",
|
|
ifrs."intBookId",
|
|
0 AS "Open Quantity",
|
|
'Section 5'::text AS "Code Section"
|
|
FROM vw_bi_itsa_monthly_trade_ifrs_adjustments ifrs;;
|