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