31 lines
1.2 KiB
SQL
31 lines
1.2 KiB
SQL
|
|
--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;
|
|
|