--DROP VIEW IF EXISTS vw_bi_fct_trade_fulfillment_event;

CREATE OR REPLACE VIEW vw_bi_fct_trade_fulfillment_event AS 
    SELECT 
        SM."intPurchaseLineId"  AS "inTradeId",
        'Physical'              AS "Trade Category",
        'Purchase'              AS "Trade Type", 
        'Delivered'             AS "Event Type",      
        SM."dtmBLDate"          AS "Event Date",
        SM."dblQuantity"        AS "Quantity"
    FROM "vw_utility_stock_movements" AS SM
    WHERE 1=1
      AND COALESCE( SM."intPurchaseLineId" , 0 ) > 0
    UNION ALL
    SELECT 
        SM."intSaleLineId"      AS "inTradeId",
        'Physical'              AS "Trade Category",
        'Sale'                  AS "Trade Type", 
        'Delivered'             AS "Event Type",      
        SM."dtmBLDate"          AS "Event Date",
        SM."dblQuantity"        AS "Quantity"
    FROM "vw_utility_stock_movements" AS SM
    WHERE 1=1
      AND COALESCE( SM."intSaleLineId" , 0 ) > 0;
    -- UNION ALL
    -- Derivative contracts can be added here in the future (Settled, Exercised, Expired, etc.)

ALTER TABLE public.vw_bi_fct_trade_fulfillment_event
    OWNER TO postgres;