57 lines
1.9 KiB
SQL
57 lines
1.9 KiB
SQL
CREATE OR REPLACE VIEW public.vw_utility_contract_fees AS
|
|
SELECT f.id AS "intFeeId",
|
|
'Purchase'::text AS "strContractType",
|
|
f.line AS "intContractLineId",
|
|
f.mode AS "strPackaging",
|
|
f.p_r AS "strPayOrRec",
|
|
f.type AS "strType",
|
|
p.code AS "strProduct",
|
|
sup.name AS "strSupplier",
|
|
f.state AS "strState",
|
|
CASE
|
|
WHEN (upper((f.weight_type)::text) = 'BRUT'::text) THEN 'Gross'::text
|
|
ELSE 'Net'::text
|
|
END AS "strWeightType",
|
|
f.quantity AS "dblQuantity",
|
|
f.price AS "dblPrice",
|
|
c.name AS "strCurrency",
|
|
uom.name AS "strUnit",
|
|
CASE
|
|
WHEN (upper((f.p_r)::text) = 'REC'::text) THEN '-1'::integer
|
|
ELSE 1
|
|
END AS "dblSignMultiplier"
|
|
FROM ((((fee_fee f
|
|
JOIN currency_currency c ON ((f.currency = c.id)))
|
|
JOIN product_product p ON ((f.product = p.id)))
|
|
JOIN product_uom uom ON ((f.unit = uom.id)))
|
|
JOIN party_party sup ON ((f.supplier = sup.id)))
|
|
WHERE (COALESCE(f.line, 0) > 0)
|
|
UNION ALL
|
|
SELECT f.id AS "intFeeId",
|
|
'Sale'::text AS "strContractType",
|
|
f.sale_line AS "intContractLineId",
|
|
f.mode AS "strPackaging",
|
|
f.p_r AS "strPayOrRec",
|
|
f.type AS "strType",
|
|
p.code AS "strProduct",
|
|
sup.name AS "strSupplier",
|
|
f.state AS "strState",
|
|
CASE
|
|
WHEN (upper((f.weight_type)::text) = 'BRUT'::text) THEN 'Gross'::text
|
|
ELSE 'Net'::text
|
|
END AS "strWeightType",
|
|
f.quantity AS "dblQuantity",
|
|
f.price AS "dblPrice",
|
|
c.name AS "strCurrency",
|
|
uom.name AS "strUnit",
|
|
CASE
|
|
WHEN (upper((f.p_r)::text) = 'REC'::text) THEN 1
|
|
ELSE '-1'::integer
|
|
END AS "dblSignMultiplier"
|
|
FROM ((((fee_fee f
|
|
JOIN currency_currency c ON ((f.currency = c.id)))
|
|
JOIN product_product p ON ((f.product = p.id)))
|
|
JOIN product_uom uom ON ((f.unit = uom.id)))
|
|
JOIN party_party sup ON ((f.supplier = sup.id)))
|
|
WHERE (COALESCE(f.sale_line, 0) > 0);;
|