28 lines
1.2 KiB
SQL
28 lines
1.2 KiB
SQL
-- View: public.vw_utility_invoice_lines
|
|
|
|
-- DROP VIEW public.vw_utility_invoice_lines;
|
|
|
|
CREATE OR REPLACE VIEW public.vw_utility_invoice_lines AS
|
|
SELECT ail.invoice AS "intInvoiceId",
|
|
ail.id AS "intInvoiceLineId",
|
|
ail.lot AS "intLotId",
|
|
CASE
|
|
WHEN COALESCE(split_part(ail.origin::text, ','::text, 1), ''::text) = 'purchase.line'::text THEN 'Purchase'::text
|
|
WHEN COALESCE(split_part(ail.origin::text, ','::text, 1), ''::text) = 'sale.line'::text THEN 'Sale'::text
|
|
ELSE ''::text
|
|
END AS "strContractType",
|
|
COALESCE(split_part(ail.origin::text, ','::text, 2)::integer, 0) AS "intContractLineId",
|
|
ail.quantity AS "dblQuantity",
|
|
pu.name AS "strQuantityUom",
|
|
cc.name AS "strCurrencyCode",
|
|
ail.unit_price AS "dblUnitPrice",
|
|
round((ail.quantity * ail.unit_price::double precision)::numeric, get_rounding_position(cc.rounding)) AS "dblLineTotal"
|
|
FROM account_invoice_line ail
|
|
JOIN account_invoice ai ON ail.invoice = ai.id
|
|
JOIN currency_currency cc ON ai.currency = cc.id
|
|
JOIN product_uom pu ON ail.unit = pu.id
|
|
WHERE 1 = 1;
|
|
|
|
ALTER TABLE public.vw_utility_invoice_lines
|
|
OWNER TO postgres;
|