1 select xt.create_view('xt.invciteminfo', $$
3 -- select distinct on allows us to add the aggregated tax total column
4 -- without having to inner join a temporarily grouped tax table or specify
8 case when invcitem_item_id = -1 then true else false end as invcitem_is_misc,
9 invcitem_billed * invcitem_qty_invuomratio
10 * (invcitem_price / invcitem_price_invuomratio) as invcitem_ext_price,
11 sum(taxhist_tax) as invcitem_tax_total
13 left join invcitemtax on invcitem_id = taxhist_parent_id
18 create or replace rule "_INSERT" as on insert to xt.invciteminfo do instead
20 insert into invcitem (
37 invcitem_qty_invuomratio,
38 invcitem_price_uom_id,
39 invcitem_price_invuomratio,
42 invcitem_rev_accnt_id,
46 new.invcitem_invchead_id,
47 new.invcitem_linenumber,
49 new.invcitem_warehous_id,
55 new.invcitem_custprice,
58 new.invcitem_salescat_id,
59 new.invcitem_taxtype_id,
60 new.invcitem_qty_uom_id,
61 new.invcitem_qty_invuomratio,
62 new.invcitem_price_uom_id,
63 new.invcitem_price_invuomratio,
64 new.invcitem_coitem_id,
65 new.invcitem_updateinv,
66 new.invcitem_rev_accnt_id,
70 create or replace rule "_UPDATE" as on update to xt.invciteminfo do instead
73 invcitem_id = new.invcitem_id,
74 invcitem_invchead_id = new.invcitem_invchead_id,
75 invcitem_linenumber = new.invcitem_linenumber,
76 invcitem_item_id = new.invcitem_item_id,
77 invcitem_warehous_id = new.invcitem_warehous_id,
78 invcitem_custpn = new.invcitem_custpn,
79 invcitem_number = new.invcitem_number,
80 invcitem_descrip = new.invcitem_descrip,
81 invcitem_ordered = new.invcitem_ordered,
82 invcitem_billed = new.invcitem_billed,
83 invcitem_custprice = new.invcitem_custprice,
84 invcitem_price = new.invcitem_price,
85 invcitem_notes = new.invcitem_notes,
86 invcitem_salescat_id = new.invcitem_salescat_id,
87 invcitem_taxtype_id = new.invcitem_taxtype_id,
88 invcitem_qty_uom_id = new.invcitem_qty_uom_id,
89 invcitem_qty_invuomratio = new.invcitem_qty_invuomratio,
90 invcitem_price_uom_id = new.invcitem_price_uom_id,
91 invcitem_price_invuomratio = new.invcitem_price_invuomratio,
92 invcitem_coitem_id = new.invcitem_coitem_id,
93 invcitem_updateinv = new.invcitem_updateinv,
94 invcitem_rev_accnt_id = new.invcitem_rev_accnt_id,
95 obj_uuid = new.obj_uuid
96 where invcitem_id = old.invcitem_id;
98 create or replace rule "_DELETE" as on delete to xt.invciteminfo do instead
100 delete from invcitem where invcitem_id = old.invcitem_id;