X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/99b9d833f8c3a3a996eea5acb4fcbc453a0a2ebe..ea910b8d66ff2add3fab2857ad0bdc74501d6969:/sql/Pg-upgrade-2.1.1-2.1.2.sql

diff --git a/sql/Pg-upgrade-2.1.1-2.1.2.sql b/sql/Pg-upgrade-2.1.1-2.1.2.sql
index 91016951b..7cf035580 100644
--- a/sql/Pg-upgrade-2.1.1-2.1.2.sql
+++ b/sql/Pg-upgrade-2.1.1-2.1.2.sql
@@ -116,15 +116,17 @@ alter table vendor drop column creditlimitold;
 --New Fields for customer and vendor
 alter table vendor add column obsolete boolean;
 alter table vendor alter column obsolete set default 'false';
+update table vendor set obsolete='false';
 alter table customer add column obsolete boolean;
 alter table customer alter column obsolete set default 'false';
+update table customer set obsolete='false';
 alter table customer add column ustid varchar(12);
 alter table vendor add column ustid varchar(12);
 
 alter table customer add column username varchar(50);
 alter table vendor add column username varchar(50);
-alter table customer add column user_password varchar(12);
-alter table vendor add column user_password varchar(12);
+alter table customer add column user_password text;
+alter table vendor add column user_password text;
 alter table customer add column salesman_id integer;
 alter table vendor add column salesman_id integer;
 
@@ -182,7 +184,6 @@ alter table employee add primary key (id);
 alter table warehouse add primary key (id);
 alter table business add primary key (id);
 alter table license add primary key (id);
-alter table orderitems add primary key (id);
 
 alter table acc_trans add foreign key (chart_id) references chart (id);
 alter table invoice add foreign key (parts_id) references parts (id);
@@ -223,7 +224,7 @@ CREATE TABLE "pricegroup" (
 
 CREATE TABLE "prices" (
   "parts_id" integer REFERENCES parts(id),
-  "pricegroup_id" integer REFERENCES pricegroup(id),
+  "pricegroup_id" integer,
   "price" numeric(15,5)
 );
 
@@ -234,11 +235,16 @@ ALTER TABLE customer ALTER column klass set default 0;
 ALTER TABLE invoice ADD column pricegroup_id integer;
 ALTER TABLE orderitems ADD column pricegroup_id integer;
 
-update defaults set version = '2.1.2', audittrail = 't';
---
 
--- add fields for ordnumber/transdate/cusordnumber in invoice/orderitems (r690 cleanup)
+-- USTVA Update solve Bug 49 conributed by Andre Schubert
+update chart set pos_ustva='861' where accno='1771';
+update chart set pos_ustva='511' where accno='1775';
+-- update chart set pos_ustva='511' where pos_ustva='51r';
+-- update chart set pos_ustva='861' where pos_ustva='86r';
+-- update chart set pos_ustva='971' where pos_ustva='97r';
+-- update chart set pos_ustva='931' where pos_ustva='93r';
 
+-- add fields for ordnumber/transdate/cusordnumber in invoice/orderitems (r690 cleanup)
 alter table orderitems add column ordnumber text;
 alter table orderitems add column transdate text;
 alter table orderitems add column cusordnumber text;
@@ -246,4 +252,77 @@ alter table invoice add column ordnumber text;
 alter table invoice add column transdate text;
 alter table invoice add column cusordnumber text;
 --
+-- UStVA Link to SKR03/2006
+-- Let this structure like it is, please.
+-- This structure is based on the sequence of the USTVA 2006
+-- Created by Udo Spallek
+--
+-- 1. Page UStVA
+UPDATE chart SET pos_ustva=41  WHERE accno IN ('8125', '8130', '8140', '8724', '8808', '8828');
+UPDATE chart SET pos_ustva=44  WHERE accno IN ('8135');
+UPDATE chart SET pos_ustva=49  WHERE accno IN ('');
+UPDATE chart SET pos_ustva=43  WHERE accno IN ('2402', '8120', '8150', '8505', '8625', '8705', '8807', '8827');
+UPDATE chart SET pos_ustva=48  WHERE accno IN ('8100', '8110', '8504', '8609');
+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');
+UPDATE chart SET pos_ustva=511 WHERE accno IN ('1775');
+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');
+UPDATE chart SET pos_ustva=861 WHERE accno IN ('1771');
+UPDATE chart SET pos_ustva=35  WHERE accno IN ('2407', '2409', '8723', '8729', '8736', '8764', '8794');
+UPDATE chart SET pos_ustva=36  WHERE accno IN ('');
+UPDATE chart SET pos_ustva=77  WHERE accno IN ('');
+UPDATE chart SET pos_ustva=76  WHERE accno IN ('8190');
+UPDATE chart SET pos_ustva=80  WHERE accno IN ('');
+UPDATE chart SET pos_ustva=91  WHERE accno IN ('');
+UPDATE chart SET pos_ustva=97  WHERE accno IN ('3425', '3725');
+UPDATE chart SET pos_ustva=971 WHERE accno IN ('1773');
+UPDATE chart SET pos_ustva=93  WHERE accno IN ('3420', '3724');
+UPDATE chart SET pos_ustva=931 WHERE accno IN ('1772');
+UPDATE chart SET pos_ustva=95  WHERE accno IN ('3727');
+UPDATE chart SET pos_ustva=98  WHERE accno IN ('');
+UPDATE chart SET pos_ustva=94  WHERE accno IN ('');
+UPDATE chart SET pos_ustva=96  WHERE accno IN ('1784');
+UPDATE chart SET pos_ustva=42  WHERE accno IN ('');
+UPDATE chart SET pos_ustva=60  WHERE accno IN ('8337');
+UPDATE chart SET pos_ustva=45  WHERE accno IN ('8338', '8339', '8950');
+-- 2. Page UStVA
+UPDATE chart SET pos_ustva=52  WHERE accno IN ('');
+UPDATE chart SET pos_ustva=53  WHERE accno IN ('');
+UPDATE chart SET pos_ustva=73  WHERE accno IN ('');
+UPDATE chart SET pos_ustva=74  WHERE accno IN ('');
+UPDATE chart SET pos_ustva=84  WHERE accno IN ('3110', '3115', '3120', '3125');
+UPDATE chart SET pos_ustva=85  WHERE accno IN ('1785', '1786');
+UPDATE chart SET pos_ustva=65  WHERE accno IN ('1782');
+UPDATE chart SET pos_ustva=66  WHERE accno IN ('1570', '1571', '1575', '1576');
+UPDATE chart SET pos_ustva=61  WHERE accno IN ('1572', '1573');
+UPDATE chart SET pos_ustva=62  WHERE accno IN ('1588');
+UPDATE chart SET pos_ustva=67  WHERE accno IN ('1578', '1579');
+UPDATE chart SET pos_ustva=63  WHERE accno IN ('1577');
+UPDATE chart SET pos_ustva=64  WHERE accno IN ('1556', '1557', '1558', '1559');
+UPDATE chart SET pos_ustva=59  WHERE accno IN ('');
+UPDATE chart SET pos_ustva=69  WHERE accno IN ('1783');
+UPDATE chart SET pos_ustva=39  WHERE accno IN ('1781');
+--
+-- clear table tax
+DELETE from tax;
+-- insert actual values for SKR03
+INSERT INTO tax (rate, taxkey, taxdescription) VALUES ('0','0','Keine Steuer');
+INSERT INTO tax (rate, taxkey, taxdescription) VALUES ('0','1','Umsatzsteuerfrei (mit Vorsteuerabzug)');
+INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id FROM chart WHERE accno = '1771'),'0.07','1771','2','Umsatzsteuer 7%');
+INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id FROM chart WHERE accno = '1775'),'0.16','1775','3','Umsatzsteuer 16%');
+INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id FROM chart WHERE accno = '1571'),'0.07','1571','8','Vorsteuer 7%');
+INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id FROM chart WHERE accno = '1575'),'0.16','1575','9','Vorsteuer 16%');
+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');
+INSERT INTO tax (taxkey, taxdescription) VALUES ('11','Steuerfreie EG-Lief. an Abn. mit UStIdNr');
+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');
+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');
+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%');
+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%');
+--
+--
+-- add unqiue constraint to project
+ALTER TABLE project ADD constraint project_projectnumber_key UNIQUE(projectnumber);
+--
+-- add column deliverydate to ar
+ALTER TABLE ar ADD COLUMN deliverydate date;
 
+update defaults set version = '2.1.2';
