File: Pg-upgrade-2.8.3-2.8.4.sql

package info (click to toggle)
sql-ledger 3.0.8-1
  • links: PTS, VCS
  • area: main
  • in suites: stretch
  • size: 35,524 kB
  • ctags: 4,556
  • sloc: perl: 64,519; sql: 28,330; sh: 34; makefile: 21
file content (37 lines) | stat: -rw-r--r-- 2,514 bytes parent folder | download | duplicates (6)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
--
drop trigger del_exchangerate on ar;
drop trigger del_exchangerate on ap;
drop trigger del_exchangerate on oe;
drop function del_exchangerate();
--
drop trigger check_inventory on oe;
drop function check_inventory();
--
create sequence paymentid;
create table payment (id int not null, trans_id int not null, exchangerate float default 1, paymentmethod_id int);
update acc_trans set id = nextval('paymentid') from ar, chart where ar.id = acc_trans.trans_id and acc_trans.chart_id = chart.id and chart.link like '%paid%' and acc_trans.fx_transaction = '0' and acc_trans.id is null;
update acc_trans set id = nextval('paymentid') from ap, chart where ap.id = acc_trans.trans_id and acc_trans.chart_id = chart.id and chart.link like '%paid%' and acc_trans.fx_transaction = '0' and acc_trans.id is null;
drop sequence paymentid;
--
insert into payment (id, trans_id, exchangerate) select ac.id, a.id, ex.buy from acc_trans ac join ar a on (a.id = ac.trans_id) join exchangerate ex on (ex.curr = a.curr and a.transdate = ex.transdate) join chart c on (c.id = ac.chart_id) where ac.id > 0 and c.link like '%paid%' order by ac.id;
insert into payment (id, trans_id, exchangerate) select ac.id, a.id, ex.sell from acc_trans ac join ap a on (a.id = ac.trans_id) join exchangerate ex on (ex.curr = a.curr and a.transdate = ex.transdate) join chart c on (c.id = ac.chart_id) where ac.id > 0 and c.link like '%paid%' order by ac.id;
--
alter table ar add exchangerate float;
update ar set exchangerate = 1;
update ar set exchangerate = exchangerate.buy from exchangerate where ar.transdate = exchangerate.transdate and ar.curr = exchangerate.curr;
--
alter table ap add exchangerate float;
update ap set exchangerate = 1;
update ap set exchangerate = exchangerate.sell from exchangerate where ap.transdate = exchangerate.transdate and ap.curr = exchangerate.curr;
--
alter table oe add exchangerate float;
update oe set exchangerate = 1;
update oe set exchangerate = exchangerate.buy from exchangerate where oe.transdate = exchangerate.transdate and oe.curr = exchangerate.curr and oe.customer_id > 0;
update oe set exchangerate = exchangerate.sell from exchangerate where oe.transdate = exchangerate.transdate and oe.curr = exchangerate.curr and oe.vendor_id > 0;
--
alter table gl add curr char(3);
alter table gl add exchangerate float;
update gl set curr = (select substr(fldvalue,1,3) from defaults where fldname = 'currencies'), exchangerate = 1;
--
update defaults set fldvalue = '2.8.4' where fldname = 'version';