summaryrefslogtreecommitdiff
path: root/sql/Pg-upgrade-1.6.0-1.8.0.sql
blob: 4e98e1fe4fd3b532aebfdb11e8032506084d69cb (plain)
  1. --
  2. create 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. );
  16. insert 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;
  17. drop table defaults;
  18. alter table def rename to defaults;
  19. update defaults set version = '1.8.0';
  20. --
  21. -- create a default accno for exchange rate gain and loss
  22. --
  23. select accno into temp from chart where category = 'I' order by accno desc limit 1;
  24. update temp set accno = accno + 1;
  25. insert into chart (accno) select accno from temp;
  26. update chart set description = 'Foreign Exchange Gain', category = 'I', charttype = 'A' where accno = (select accno from temp);
  27. update defaults set fxgain_accno_id = (select id from chart where chart.accno = temp.accno);
  28. drop table temp;
  29. select accno into temp from chart where category = 'E' order by accno desc limit 1;
  30. update temp set accno = accno + 1;
  31. insert into chart (accno) select accno from temp;
  32. update chart set description = 'Foreign Exchange Loss', category = 'E', charttype = 'A' where accno = (select accno from temp);
  33. update defaults set fxloss_accno_id = (select id from chart where chart.accno = temp.accno);
  34. drop table temp;
  35. --
  36. alter table parts add column bin text;
  37. alter table parts alter column onhand set default 0;
  38. update parts set onhand = 0 where onhand = NULL;
  39. alter table parts add column obsolete bool;
  40. alter table parts alter column obsolete set default 'f';
  41. update parts set obsolete = 'f';
  42. --
  43. alter table ap rename column vendor to vendor_id;
  44. alter table ap add column curr char(3);
  45. --
  46. alter table ar rename column customer to customer_id;
  47. alter table ar add column curr char(3);
  48. alter table ar add column ordnumber text;
  49. --
  50. alter table acc_trans add column source text;
  51. alter table acc_trans add column cleared bool;
  52. alter table acc_trans alter column cleared set default 'f';
  53. alter table acc_trans add column fx_transaction bool;
  54. alter table acc_trans alter column fx_transaction set default 'f';
  55. update acc_trans set cleared = 'f', fx_transaction = 'f';
  56. --
  57. create 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. --
  72. create table orderitems (
  73. trans_id int,
  74. parts_id int,
  75. description text,
  76. qty float4,
  77. sellprice float8,
  78. discount float4
  79. );
  80. --
  81. alter table invoice rename to invoiceold;
  82. create 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. );
  94. insert 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;
  95. update invoice set assemblyitem = 'f' where assemblyitem = NULL;
  96. drop table invoiceold;
  97. --
  98. create table exchangerate (
  99. curr char(3),
  100. transdate date,
  101. buy float8,
  102. sell float8
  103. );
  104. --