camper/deploy/invoice_tax_amount.sql

24 lines
592 B
PL/PgSQL

-- Deploy camper:invoice_tax_amount to pg
-- requires: schema_camper
-- requires: invoice_product
-- requires: invoice_product_tax
begin;
set search_path to camper, public;
create or replace view invoice_tax_amount as
select invoice_id
, tax_id
, sum(round(round(price * quantity * (1 - discount_rate))::integer * tax_rate)::integer)::integer as amount
from invoice_product
join invoice_product_tax using (invoice_product_id)
group by invoice_id
, tax_id
;
grant select on table invoice_tax_amount to employee;
grant select on table invoice_tax_amount to admin;
commit;