sql/Pg-upgrade-2.1.1-2.1.2.sql

   1
   2 --Update der Numeric-Spalten von 5 auf 10 Vorkommastellen
   3 --
   4 --TABLE ap
   5 alter table ap rename column paid to paidold;
   6 alter table ap add column paid numeric(15,5);
   7 update ap set paid=paidold;
   8 alter table ap drop column paidold;
   9 --
  10 alter table ap rename column netamount to netamountold;
  11 alter table ap add column netamount numeric(15,5);
  12 update ap set netamount=netamountold;
  13 alter table ap drop column netamountold;
  14 --
  15 alter table ap rename column amount to amountold;
  16 alter table ap add column amount numeric(15,5);
  17 update ap set amount=amountold;
  18 alter table ap drop column amountold;
  19 --
  20 --TABLE acc_trans
  21 alter table acc_trans rename column amount to amountold;
  22 alter table acc_trans add column amount numeric(15,5);
  23 update acc_trans set amount=amountold;
  24 alter table acc_trans drop column amountold;
  25 --
  26 --TABLE ar
  27 alter table ar rename column amount to amountold;
  28 alter table ar add column amount numeric(15,5);
  29 update ar set amount=amountold;
  30 alter table ar drop column amountold;
  31 --
  32 alter table ar rename column netamount to netamountold;
  33 alter table ar add column netamount numeric(15,5);
  34 update ar set netamount=netamountold;
  35 alter table ar drop column netamountold;
  36 --
  37 alter table ar rename column paid to paidold;
  38 alter table ar add column paid numeric(15,5);
  39 update ar set paid=paidold;
  40 alter table ar drop column paidold;
  41 --
  42 --TABLE customer
  43 alter table customer rename column creditlimit to creditlimitold;
  44 alter table customer add column creditlimit numeric(15,5);
  45 update customer set creditlimit=creditlimitold;
  46 alter table customer drop column creditlimitold;
  47 --
  48 --TABLE exchangerate
  49 alter table exchangerate rename column buy to buyold;
  50 alter table exchangerate add column buy numeric(15,5);
  51 update exchangerate set buy=buyold;
  52 alter table exchangerate drop column buyold;
  53 --
  54 alter table exchangerate rename column sell to sellold;
  55 alter table exchangerate add column sell numeric(15,5);
  56 update exchangerate set sell=sellold;
  57 alter table exchangerate drop column sellold;
  58 --
  59 --TABLE invoice
  60 alter table invoice rename column sellprice to sellpriceold;
  61 alter table invoice add column sellprice numeric(15,5);
  62 update invoice set sellprice=sellpriceold;
  63 alter table invoice drop column sellpriceold;
  64 --
  65 alter table invoice rename column fxsellprice to fxsellpriceold;
  66 alter table invoice add column fxsellprice numeric(15,5);
  67 update invoice set fxsellprice=fxsellpriceold;
  68 alter table invoice drop column fxsellpriceold;
  69 --
  70 --TABLE oe
  71 alter table oe rename column amount to amountold;
  72 alter table oe add column amount numeric(15,5);
  73 update oe set amount=amountold;
  74 alter table oe drop column amountold;
  75 --
  76 alter table oe rename column netamount to netamountold;
  77 alter table oe add column netamount numeric(15,5);
  78 update oe set netamount=netamountold;
  79 alter table oe drop column netamountold;
  80 --
  81 --TABLE orderitems
  82 alter table orderitems rename column sellprice to sellpriceold;
  83 alter table orderitems add column sellprice numeric(15,5);
  84 update orderitems set sellprice=sellpriceold;
  85 alter table orderitems drop column sellpriceold;
  86 --
  87 --TABLE parts
  88 alter table parts rename column listprice to listpriceold;
  89 alter table parts add column listprice numeric(15,5);
  90 update parts set listprice=listpriceold;
  91 alter table parts drop column listpriceold;
  92 --
  93 alter table parts rename column sellprice to sellpriceold;
  94 alter table parts add column sellprice numeric(15,5);
  95 update parts set sellprice=sellpriceold;
  96 alter table parts drop column sellpriceold;
  97 --
  98 alter table parts rename column lastcost to lastcostold;
  99 alter table parts add column lastcost numeric(15,5);
 100 update parts set lastcost=lastcostold;
 101 alter table parts drop column lastcostold;
 102 --
 103 --TABLE tax
 104 alter table tax rename column rate to rateold;
 105 alter table tax add column rate numeric(15,5);
 106 update tax set rate=rateold;
 107 alter table tax drop column rateold;
 108 --
 109 --TABLE vendor
 110 alter table vendor rename column creditlimit to creditlimitold;
 111 alter table vendor add column creditlimit numeric(15,5);
 112 update vendor set creditlimit=creditlimitold;
 113 alter table vendor drop column creditlimitold;
 114 --
 115
 116 --New Fields for customer and vendor
 117 alter table vendor add column obsolete boolean;
 118 alter table vendor alter column obsolete set default 'false';
 119 update table vendor set obsolete='false';
 120 alter table customer add column obsolete boolean;
 121 alter table customer alter column obsolete set default 'false';
 122 update table customer set obsolete='false';
 123 alter table customer add column ustid varchar(12);
 124 alter table vendor add column ustid varchar(12);
 125
 126 alter table customer add column username varchar(50);
 127 alter table vendor add column username varchar(50);
 128 alter table customer add column user_password text;
 129 alter table vendor add column user_password text;
 130 alter table customer add column salesman_id integer;
 131 alter table vendor add column salesman_id integer;
 132
 133 -- Shipto
 134 alter table shipto add column shiptodepartment_1 varchar(75);
 135 alter table shipto add column shiptodepartment_2 varchar(75);
 136
 137
 138
 139 -- Addon for business
 140 alter table business add column salesman boolean;
 141 alter table business alter column salesman set default 'false';
 142 alter table business add column customernumberinit text;
 143
 144 alter table parts add column ve integer;
 145 alter table parts add column gv numeric(15,5);
 146 --
 147
 148 -- Add table contrains
 149 alter table customer alter name SET NOT NULL;
 150 alter table vendor alter name set NOT NULL;
 151 alter table chart alter accno set NOT NULL;
 152 alter table parts alter partnumber set NOT NULL;
 153 alter table ar alter invnumber set NOT NULL;
 154 alter table ap alter invnumber set NOT NULL;
 155 alter table oe alter ordnumber set NOT NULL;
 156
 157 alter table gl alter id set NOT NULL;
 158 alter table chart alter id set NOT NULL;
 159 alter table parts alter id set NOT NULL;
 160 alter table invoice alter id set NOT NULL;
 161 alter table vendor alter id set NOT NULL;
 162 alter table customer alter id set NOT NULL;
 163 alter table contacts alter cp_id set NOT NULL;
 164 alter table ar alter id set NOT NULL;
 165 alter table ap alter id set NOT NULL;
 166 alter table oe alter id set NOT NULL;
 167 alter table employee alter id set NOT NULL;
 168 alter table warehouse alter id set NOT NULL;
 169 alter table business alter id set NOT NULL;
 170 alter table license alter id set NOT NULL;
 171 alter table orderitems alter id set NOT NULL;
 172
 173 alter table gl add primary key (id);
 174 alter table chart add primary key (id);
 175 alter table parts add primary key (id);
 176 alter table invoice add primary key (id);
 177 alter table vendor add primary key (id);
 178 alter table customer add primary key (id);
 179 alter table contacts add primary key (cp_id);
 180 alter table ar add primary key (id);
 181 alter table ap add primary key (id);
 182 alter table oe add primary key (id);
 183 alter table employee add primary key (id);
 184 alter table warehouse add primary key (id);
 185 alter table business add primary key (id);
 186 alter table license add primary key (id);
 187
 188 alter table acc_trans add foreign key (chart_id) references chart (id);
 189 alter table invoice add foreign key (parts_id) references parts (id);
 190 alter table ar add foreign key (customer_id) references customer (id);
 191 alter table ap add foreign key (vendor_id) references vendor (id);
 192 alter table orderitems add foreign key (parts_id) references parts (id);
 193
 194 --Modify the possible length of bank account numbers
 195 alter table customer add column temp_account_number character varying(15);
 196 update customer set temp_account_number=account_number;
 197 alter table customer drop column account_number;
 198 alter table customer rename temp_account_number to  account_number;
 199
 200 alter table vendor add column temp_account_number character varying(15);
 201 update vendor set temp_account_number=account_number;
 202 alter table vendor drop column account_number;
 203 alter table vendor rename temp_account_number to  account_number;
 204
 205 -- audit
 206 alter table defaults add column audittrail bool;
 207 CREATE TABLE audittrail (
 208   trans_id int,
 209   tablename text,
 210   reference text,
 211   formname text,
 212   action text,
 213   transdate timestamp default current_timestamp,
 214   employee_id int
 215 );
 216
 217 -- pricegroups
 218
 219 CREATE TABLE "pricegroup" (
 220   "id" integer DEFAULT nextval('id'::text),
 221   "pricegroup" text not null,
 222   PRIMARY KEY (id)
 223 );
 224
 225 CREATE TABLE "prices" (
 226   "parts_id" integer REFERENCES parts(id),
 227   "pricegroup_id" integer,
 228   "price" numeric(15,5)
 229 );
 230
 231 ALTER TABLE customer ADD column klass integer;
 232 ALTER TABLE customer ALTER column klass set default 0;
 233
 234 --
 235 ALTER TABLE invoice ADD column pricegroup_id integer;
 236 ALTER TABLE orderitems ADD column pricegroup_id integer;
 237
 238
 239 -- USTVA Update solve Bug 49 conributed by Andre Schubert
 240 update chart set pos_ustva='861' where accno='1771';
 241 update chart set pos_ustva='511' where accno='1775';
 242 -- update chart set pos_ustva='511' where pos_ustva='51r';
 243 -- update chart set pos_ustva='861' where pos_ustva='86r';
 244 -- update chart set pos_ustva='971' where pos_ustva='97r';
 245 -- update chart set pos_ustva='931' where pos_ustva='93r';
 246
 247 -- add fields for ordnumber/transdate/cusordnumber in invoice/orderitems (r690 cleanup)
 248 alter table orderitems add column ordnumber text;
 249 alter table orderitems add column transdate text;
 250 alter table orderitems add column cusordnumber text;
 251 alter table invoice add column ordnumber text;
 252 alter table invoice add column transdate text;
 253 alter table invoice add column cusordnumber text;
 254 --
 255 -- UStVA Link to SKR03/2006
 256 -- Let this structure like it is, please.
 257 -- This structure is based on the sequence of the USTVA 2006
 258 -- Created by Udo Spallek
 259 --
 260 -- 1. Page UStVA
 261 UPDATE chart SET pos_ustva=41  WHERE accno IN ('8125', '8130', '8140', '8724', '8808', '8828');
 262 UPDATE chart SET pos_ustva=44  WHERE accno IN ('8135');
 263 UPDATE chart SET pos_ustva=49  WHERE accno IN ('');
 264 UPDATE chart SET pos_ustva=43  WHERE accno IN ('2402', '8120', '8150', '8505', '8625', '8705', '8807', '8827');
 265 UPDATE chart SET pos_ustva=48  WHERE accno IN ('8100', '8110', '8504', '8609');
 266 UPDATE chart SET pos_ustva=51  WHERE accno IN ('1717', '2404', '2405', '2700', '2705', '2707', '2709', '8196', '8315', '8400', '8595', '8600', '8611', '8640', '8720', '8726', '8735', '8760', '8790', '8801', '8809', '8820', '8910', '8920', '8921', '8922', '8925', '8935', '8940');
 267 UPDATE chart SET pos_ustva=511 WHERE accno IN ('1775');
 268 UPDATE chart SET pos_ustva=86  WHERE accno IN ('1711', '2401', '2403', '8300', '8310', '8506', '8591', '8630', '8710', '8725', '8731', '8750', '8780', '8915', '8930', '8932', '8945');
 269 UPDATE chart SET pos_ustva=861 WHERE accno IN ('1771');
 270 UPDATE chart SET pos_ustva=35  WHERE accno IN ('2407', '2409', '8723', '8729', '8736', '8764', '8794');
 271 UPDATE chart SET pos_ustva=36  WHERE accno IN ('');
 272 UPDATE chart SET pos_ustva=77  WHERE accno IN ('');
 273 UPDATE chart SET pos_ustva=76  WHERE accno IN ('8190');
 274 UPDATE chart SET pos_ustva=80  WHERE accno IN ('');
 275 UPDATE chart SET pos_ustva=91  WHERE accno IN ('');
 276 UPDATE chart SET pos_ustva=97  WHERE accno IN ('3425', '3725');
 277 UPDATE chart SET pos_ustva=971 WHERE accno IN ('1773');
 278 UPDATE chart SET pos_ustva=93  WHERE accno IN ('3420', '3724');
 279 UPDATE chart SET pos_ustva=931 WHERE accno IN ('1772');
 280 UPDATE chart SET pos_ustva=95  WHERE accno IN ('3727');
 281 UPDATE chart SET pos_ustva=98  WHERE accno IN ('');
 282 UPDATE chart SET pos_ustva=94  WHERE accno IN ('');
 283 UPDATE chart SET pos_ustva=96  WHERE accno IN ('1784');
 284 UPDATE chart SET pos_ustva=42  WHERE accno IN ('');
 285 UPDATE chart SET pos_ustva=60  WHERE accno IN ('8337');
 286 UPDATE chart SET pos_ustva=45  WHERE accno IN ('8338', '8339', '8950');
 287 -- 2. Page UStVA
 288 UPDATE chart SET pos_ustva=52  WHERE accno IN ('');
 289 UPDATE chart SET pos_ustva=53  WHERE accno IN ('');
 290 UPDATE chart SET pos_ustva=73  WHERE accno IN ('');
 291 UPDATE chart SET pos_ustva=74  WHERE accno IN ('');
 292 UPDATE chart SET pos_ustva=84  WHERE accno IN ('3110', '3115', '3120', '3125');
 293 UPDATE chart SET pos_ustva=85  WHERE accno IN ('1785', '1786');
 294 UPDATE chart SET pos_ustva=65  WHERE accno IN ('1782');
 295 UPDATE chart SET pos_ustva=66  WHERE accno IN ('1570', '1571', '1575', '1576');
 296 UPDATE chart SET pos_ustva=61  WHERE accno IN ('1572', '1573');
 297 UPDATE chart SET pos_ustva=62  WHERE accno IN ('1588');
 298 UPDATE chart SET pos_ustva=67  WHERE accno IN ('1578', '1579');
 299 UPDATE chart SET pos_ustva=63  WHERE accno IN ('1577');
 300 UPDATE chart SET pos_ustva=64  WHERE accno IN ('1556', '1557', '1558', '1559');
 301 UPDATE chart SET pos_ustva=59  WHERE accno IN ('');
 302 UPDATE chart SET pos_ustva=69  WHERE accno IN ('1783');
 303 UPDATE chart SET pos_ustva=39  WHERE accno IN ('1781');
 304 --
 305 -- clear table tax
 306 DELETE from tax;
 307 -- insert actual values for SKR03
 308 INSERT INTO tax (rate, taxkey, taxdescription) VALUES ('0','0','Keine Steuer');
 309 INSERT INTO tax (rate, taxkey, taxdescription) VALUES ('0','1','Umsatzsteuerfrei (mit Vorsteuerabzug)');
 310 INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id FROM chart WHERE accno = '1771'),'0.07','1771','2','Umsatzsteuer 7%');
 311 INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id FROM chart WHERE accno = '1775'),'0.16','1775','3','Umsatzsteuer 16%');
 312 INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id FROM chart WHERE accno = '1571'),'0.07','1571','8','Vorsteuer 7%');
 313 INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id FROM chart WHERE accno = '1575'),'0.16','1575','9','Vorsteuer 16%');
 314 INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id FROM chart WHERE accno = '1767'),'0.00','1767','10','Im anderen EG-Staat steuerpfl. Lieferung');
 315 INSERT INTO tax (taxkey, taxdescription) VALUES ('11','Steuerfreie EG-Lief. an Abn. mit UStIdNr');
 316 INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id FROM chart WHERE accno = '1772'),'0.07','1772','12','Umsatzsteuer 7% innergem. Erwerb');
 317 INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id FROM chart WHERE accno = '1773'),'0.16','1773','13','Umsatzsteuer 16% innergem. Erwerb');
 318 INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id FROM chart WHERE accno = '1572'),'0.07','1572','18','Steuerpfl. EG-Erwerb 7%');
 319 INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id FROM chart WHERE accno = '1572'),'0.16','1573','19','Steuerpfl. EG-Erwerb 16%');
 320 --
 321 --
 322 -- add unqiue constraint to project
 323 ALTER TABLE project ADD constraint project_projectnumber_key UNIQUE(projectnumber);
 324 --
 325 -- add column deliverydate to ar
 326 ALTER TABLE ar ADD COLUMN deliverydate date;
 327
 328 update defaults set version = '2.1.2';
 329
 330