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")));;