1--
2create table def (
3  inventory_accno_id int,
4  income_accno_id int,
5  expense_accno_id int,
6  fxgain_accno_id int,
7  fxloss_accno_id int,
8  invnumber text,
9  ordnumber text,
10  yearend varchar(5),
11  weightunit varchar(5),
12  businessnumber text,
13  version varchar(8),
14  curr text
15);
16insert into def (inventory_accno_id, income_accno_id, expense_accno_id, invnumber, ordnumber, yearend, weightunit, businessnumber, version, curr) select inventory_accno_id, income_accno_id, expense_accno_id, invnumber, ponumber, yearend, weightunit, businessnumber, version, nativecurr from defaults;
17drop table defaults;
18alter table def rename to defaults;
19update defaults set version = '1.8.0';
20--
21-- create a default accno for exchange rate gain and loss
22--
23select accno into temp from chart where category = 'I' order by accno desc limit 1;
24update temp set accno = accno + 1;
25insert into chart (accno) select accno from temp;
26update chart set description = 'Foreign Exchange Gain', category = 'I', charttype = 'A' where accno = (select accno from temp);
27update defaults set fxgain_accno_id = (select id from chart where chart.accno = temp.accno);
28drop table temp;
29select accno into temp from chart where category = 'E' order by accno desc limit 1;
30update temp set accno = accno + 1;
31insert into chart (accno) select accno from temp;
32update chart set description = 'Foreign Exchange Loss', category = 'E', charttype = 'A' where accno = (select accno from temp);
33update defaults set fxloss_accno_id = (select id from chart where chart.accno = temp.accno);
34drop table temp;
35--
36alter table parts add column bin text;
37alter table parts alter column onhand set default 0;
38update parts set onhand = 0 where onhand = NULL;
39alter table parts add column obsolete bool;
40alter table parts alter column obsolete set default 'f';
41update parts set obsolete = 'f';
42--
43alter table ap rename column vendor to vendor_id;
44alter table ap add column curr char(3);
45--
46alter table ar rename column customer to customer_id;
47alter table ar add column curr char(3);
48alter table ar add column ordnumber text;
49--
50alter table acc_trans add column source text;
51alter table acc_trans add column cleared bool;
52alter table acc_trans alter column cleared set default 'f';
53alter table acc_trans add column fx_transaction bool;
54alter table acc_trans alter column fx_transaction set default 'f';
55update acc_trans set cleared = 'f', fx_transaction = 'f';
56--
57create table oe (
58  id int default nextval('id'),
59  ordnumber text,
60  transdate date default current_date,
61  vendor_id int,
62  customer_id int,
63  amount float8,
64  netamount float8,
65  reqdate date,
66  taxincluded bool,
67  shippingpoint text,
68  notes text,
69  curr char(3)
70);
71--
72create table orderitems (
73  trans_id int,
74  parts_id int,
75  description text,
76  qty float4,
77  sellprice float8,
78  discount float4
79);
80--
81alter table invoice rename to invoiceold;
82create table invoice (
83  id int default nextval('id'),
84  trans_id int,
85  parts_id int,
86  description text,
87  qty float4,
88  allocated float4,
89  sellprice float8,
90  fxsellprice float8,
91  discount float4,
92  assemblyitem bool default 'f'
93);
94insert into invoice (id, trans_id, parts_id, description, qty, allocated, sellprice, fxsellprice, discount, assemblyitem) select id, trans_id, parts_id, description, qty, allocated, sellprice, sellprice, discount, assemblyitem from invoiceold;
95update invoice set assemblyitem = 'f' where assemblyitem = NULL;
96drop table invoiceold;
97--
98create table exchangerate (
99  curr char(3),
100  transdate date,
101  buy float8,
102  sell float8
103);
104--
105