31 lines
1.4 KiB
SQL
31 lines
1.4 KiB
SQL
CREATE OR REPLACE VIEW public.vw_utility_physical_valuation_line AS
|
|
SELECT 'Purchase'::text AS "strContractType",
|
|
vvl.date AS "dtmValuationDate",
|
|
vvl.reference AS "strReference",
|
|
vvl.purchase AS "intContractId",
|
|
vvl.line AS "intContractLineId",
|
|
vvl.lot AS "intLotId",
|
|
vvl.quantity AS "dblQuantity",
|
|
initcap((vvl.state)::text) AS "strState",
|
|
vvl.price AS "dblPrice",
|
|
COALESCE(vvl.mtm_price, (0)::numeric) AS "dblMtmPrice",
|
|
vvl.amount AS "dblAmount",
|
|
vvl.base_amount AS "dblBaseAmount"
|
|
FROM valuation_valuation_line vvl
|
|
WHERE ((1 = 1) AND ((vvl.type)::text = 'pur. priced'::text) AND (COALESCE(vvl.lot, 0) > 0) AND (COALESCE(vvl.purchase, 0) > 0) AND (COALESCE(vvl.line, 0) > 0))
|
|
UNION ALL
|
|
SELECT 'Sale'::text AS "strContractType",
|
|
vvl.date AS "dtmValuationDate",
|
|
vvl.reference AS "strReference",
|
|
vvl.sale AS "intContractId",
|
|
vvl.sale_line AS "intContractLineId",
|
|
vvl.lot AS "intLotId",
|
|
vvl.quantity AS "dblQuantity",
|
|
initcap((vvl.state)::text) AS "strState",
|
|
vvl.price AS "dblPrice",
|
|
COALESCE(vvl.mtm_price, (0)::numeric) AS "dblMtmPrice",
|
|
vvl.amount AS "dblAmount",
|
|
vvl.base_amount AS "dblBaseAmount"
|
|
FROM valuation_valuation_line vvl
|
|
WHERE ((1 = 1) AND ((vvl.type)::text = 'sale priced'::text) AND (COALESCE(vvl.lot, 0) > 0) AND (COALESCE(vvl.sale, 0) > 0) AND (COALESCE(vvl.sale_line, 0) > 0));;
|