From 63a1c75d320045aea95c6bfd2444716d34882a56 Mon Sep 17 00:00:00 2001
From: =?utf8?q?Stephan=20K=C3=B6hler?= <s.koehler@linet-services.de>
Date: Thu, 2 Feb 2006 15:56:08 +0000
Subject: [PATCH] =?utf8?q?Merge=20von=20816-819,828=20aus=20unstable:=20US?=
 =?utf8?q?tVa=20Bugfix,=20Bug=20262=20&=20264=20-816=20Bug=20262=20fixed!?=
 =?utf8?q?=20USTVA=20UPDATE=20der=20Positionen=20und=20Verknuepfungen.=20D?=
 =?utf8?q?ie=20UPDATEs=20sind=20relativ=20vollstaendig=20und=20sortiert,?=
 =?utf8?q?=20so=20dass=20eine=20weitere=20Pflege=20einfach=20ist.=20taxkey?=
 =?utf8?q?'s=20sind=20noch=20nicht=20integriert,=20deshalb=20sind=20einige?=
 =?utf8?q?=20updates=20in=20liste.aql=20doppelt=20gemoppelt,=20was=20m.E.?=
 =?utf8?q?=20unproblematisch=20ist,=20gleiches=20gilt=20f=C3=BCr=20den=20G?=
 =?utf8?q?ermany-DATEV-SKR03EU-chart.sql=20-817=20Bug=20264=20fixed.=20Tha?=
 =?utf8?q?nks=20to=20Martin=20Krung.=20-818=20Fixed=20date=20problems=20be?=
 =?utf8?q?tween=20database=20and=20screen-output,=20reported=20by=20Holger?=
 =?utf8?q?=20on=20the=20devel-ML.=20-819=20Update=20table=20tax=20-828=20U?=
 =?utf8?q?STVA:=20Fixed=20upgrade=20Problems=20for=20tax=20table?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

---
 bin/mozilla/ustva.pl                | 122 ++++++++++++++--------------
 sql/Germany-DATEV-SKR03EU-chart.sql |  76 ++++++++++++++---
 sql/Pg-upgrade-2.1.1-2.1.2.sql      |  69 +++++++++++++++-
 sql/Swiss-German-chart.sql          |  10 +--
 sql/liste.sql                       |  52 +++++++++++-
 5 files changed, 249 insertions(+), 80 deletions(-)

diff --git a/bin/mozilla/ustva.pl b/bin/mozilla/ustva.pl
index 6fa1ed75e..2151c4ab1 100644
--- a/bin/mozilla/ustva.pl
+++ b/bin/mozilla/ustva.pl
@@ -29,7 +29,7 @@ require "$form->{path}/arap.pl";
 #use strict;
 #no strict 'refs';
 #use diagnostics;
-#use warnings FATAL=> 'all';
+#use warnings; # FATAL=> 'all';
 #use vars qw($locale $form %myconfig);
 #our ($myconfig);
 #use CGI::Carp "fatalsToBrowser";
@@ -76,13 +76,7 @@ sub report {
   $form->{title} = $locale->text('UStVA');
   $form->{kz10}  = '';                       #Berichtigte Anmeldung? Ja =1
 
-  #  $accrual = ($eur) ? "" : "checked";
-  #  $cash = ($eur) ? "checked" : "";
-  my $year = '';
-  my $null = '';
-  ($null, $null, $null, $null, $null, $year, $null, $null, $null) =
-    localtime();
-  $year += 1900;
+  my $year = substr($form->datetonum($form->current_date(\%myconfig), \%myconfig),0,4);
 
   my $department = '';
   local $hide = '';
@@ -233,7 +227,7 @@ sub report {
 
     # accounting years if SQL-Ledger Version < 2.4.1
     #    $year = $form->{year} * 1;
-    @years = sort { $b <=> $a } (2000 .. ($year+1));
+    @years = sort { $b <=> $a } (2003 .. ($year+1));
     $form->{all_years} = \@years;
   }
   map { $form->{selectaccountingyear} .= qq|<option>$_\n| }
@@ -539,13 +533,18 @@ sub show {
 sub ustva_vorauswahl {
   $lxdebug->enter_sub();
   #Aktuelles Datum zerlegen:
-  $locale->date(\%myconfig, $form->current_date(\%myconfig, '0', '0'), 0) =~
-    /(\d\d).(\d\d).(\d\d\d\d)/;
+  my $date = $form->datetonum($form->current_date(\%myconfig), \%myconfig);
 
   #$locale->date($myconfig, $form->current_date($myconfig), 0)=~ /(\d\d).(\d\d).(\d\d\d\d)/;
-  $form->{day}   = $1;
-  $form->{month} = $2;
-  $form->{year}  = $3;
+  $form->{day}   = substr($date, 6, 2);
+  $form->{month} = substr($date, 4, 2);
+  $form->{year}  = substr($date, 0, 4);
+  $lxdebug->message(LXDebug::DEBUG1, qq|
+    Actual date from Database: $date\n    
+    Actual year from Database: $form->{year}\n
+    Actual day from Database: $form->{day}\n
+    Actual month from Database: $form->{month}\n|);
+  
   my $sel    = '';
   my $yymmdd = '';
 
@@ -585,9 +584,12 @@ sub ustva_vorauswahl {
        
     my $yy = $form->{year} * 10000;
     $yymmdd = "$form->{year}$form->{month}$form->{day}" * 1;
+    $yymmdd = 20060121;
     $sel    = '';
-    my $dfv = '0';    # Offset für Dauerfristverlängerung
-                      #$dfv = '100' if ($form->{FA_dauerfrist} eq '1');
+    my $dfv = '0';    
+    
+    # Offset für Dauerfristverlängerung
+    $dfv = '100' if ($form->{FA_dauerfrist} eq '1');
 
   SWITCH: {
       $yymmdd <= ($yy + 110 + $dfv) && do {
@@ -824,130 +826,130 @@ sub generate_ustva {
 
     #forgotten the year --> thisyear
     if ($form->{year} !~ m/^\d\d\d\d$/) {
-      $locale->date(\$myconfig, $form->current_date(\$myconfig), 0) =~
-        /(\d\d\d\d)/;
-      $form->{year} = $1;
+      $form->{year} = substr($form->datetonum($form->current_date(\%myconfig), \%myconfig),0,4);
+      $lxdebug->message(LXDebug::DEBUG1, qq|Actual year from Database: $form->{year}\n|);
     }
 
     #yearly report
     if ($form->{duetyp} eq "13") {
-      $form->{fromdate} = "$form->{year}-01-01";
-      $form->{todate}   = "$form->{year}-12-31";
+      $form->{fromdate} = "$form->{year}0101";
+      $form->{todate}   = "$form->{year}1231";
     }
 
     #Quater reports
     if ($form->{duetyp} eq "A") {
-      $form->{fromdate} = "$form->{year}-01-01";
-      $form->{todate}   = "$form->{year}-03-31";
+      $form->{fromdate} = "$form->{year}0101";
+      $form->{todate}   = "$form->{year}0331";
       $form->{'0441'}   = "X";
     }
     if ($form->{duetyp} eq "B") {
-      $form->{fromdate} = "$form->{year}-04-01";
-      $form->{todate}   = "$form->{year}-06-30";
+      $form->{fromdate} = "$form->{year}0401";
+      $form->{todate}   = "$form->{year}0630";
       $form->{'0442'}   = "X";
     }
     if ($form->{duetyp} eq "C") {
-      $form->{fromdate} = "$form->{year}-07-01";
-      $form->{todate}   = "$form->{year}-09-30";
+      $form->{fromdate} = "$form->{year}0701";
+      $form->{todate}   = "$form->{year}0930";
       $form->{'0443'}   = "X";
     }
     if ($form->{duetyp} eq "D") {
-      $form->{fromdate} = "$form->{year}-10-01";
-      $form->{todate}   = "$form->{year}-12-31";
+      $form->{fromdate} = "$form->{year}1001";
+      $form->{todate}   = "$form->{year}1231";
       $form->{'0444'}   = "X";
     }
 
     #Monthly reports
   SWITCH: {
       $form->{duetyp} eq "01" && do {
-        $form->{fromdate} = "$form->{year}-01-01";
-        $form->{todate}   = "$form->{year}-01-31";
+        $form->{fromdate} = "$form->{year}0101";
+        $form->{todate}   = "$form->{year}0131";
         $form->{'0401'}   = "X";
         last SWITCH;
       };
       $form->{duetyp} eq "02" && do {
-        $form->{fromdate} = "$form->{year}-02-01";
+        $form->{fromdate} = "$form->{year}0201";
 
         #this works from 1901 to 2099, 1900 and 2100 fail.
         my $leap = ($form->{year} % 4 == 0) ? "29" : "28";
-        $form->{todate} = "$form->{year}-02-$leap";
+        $form->{todate} = "$form->{year}02$leap";
         $form->{"0402"} = "X";
         last SWITCH;
       };
       $form->{duetyp} eq "03" && do {
-        $form->{fromdate} = "$form->{year}-03-01";
-        $form->{todate}   = "$form->{year}-03-31";
+        $form->{fromdate} = "$form->{year}0301";
+        $form->{todate}   = "$form->{year}0331";
         $form->{"0403"}   = "X";
         last SWITCH;
       };
       $form->{duetyp} eq "04" && do {
-        $form->{fromdate} = "$form->{year}-04-01";
-        $form->{todate}   = "$form->{year}-04-30";
+        $form->{fromdate} = "$form->{year}0401";
+        $form->{todate}   = "$form->{year}0430";
         $form->{"0404"}   = "X";
         last SWITCH;
       };
       $form->{duetyp} eq "05" && do {
-        $form->{fromdate} = "$form->{year}-05-01";
-        $form->{todate}   = "$form->{year}-05-31";
+        $form->{fromdate} = "$form->{year}0501";
+        $form->{todate}   = "$form->{year}0531";
         $form->{"0405"}   = "X";
         last SWITCH;
       };
       $form->{duetyp} eq "06" && do {
-        $form->{fromdate} = "$form->{year}-06-01";
-        $form->{todate}   = "$form->{year}-06-30";
+        $form->{fromdate} = "$form->{year}0601";
+        $form->{todate}   = "$form->{year}0630";
         $form->{"0406"}   = "X";
         last SWITCH;
       };
       $form->{duetyp} eq "07" && do {
-        $form->{fromdate} = "$form->{year}-07-01";
-        $form->{todate}   = "$form->{year}-07-31";
+        $form->{fromdate} = "$form->{year}0701";
+        $form->{todate}   = "$form->{year}0731";
         $form->{"0407"}   = "X";
         last SWITCH;
       };
       $form->{duetyp} eq "08" && do {
-        $form->{fromdate} = "$form->{year}-08-01";
-        $form->{todate}   = "$form->{year}-08-31";
+        $form->{fromdate} = "$form->{year}0801";
+        $form->{todate}   = "$form->{year}0831";
         $form->{"0408"}   = "X";
         last SWITCH;
       };
       $form->{duetyp} eq "09" && do {
-        $form->{fromdate} = "$form->{year}-09-01";
-        $form->{todate}   = "$form->{year}-09-30";
+        $form->{fromdate} = "$form->{year}0901";
+        $form->{todate}   = "$form->{year}0930";
         $form->{"0409"}   = "X";
         last SWITCH;
       };
       $form->{duetyp} eq "10" && do {
-        $form->{fromdate} = "$form->{year}-10-01";
-        $form->{todate}   = "$form->{year}-10-31";
+        $form->{fromdate} = "$form->{year}1001";
+        $form->{todate}   = "$form->{year}1031";
         $form->{"0410"}   = "X";
         last SWITCH;
       };
       $form->{duetyp} eq "11" && do {
-        $form->{fromdate} = "$form->{year}-11-01";
-        $form->{todate}   = "$form->{year}-11-30";
+        $form->{fromdate} = "$form->{year}1101";
+        $form->{todate}   = "$form->{year}1130";
         $form->{"0411"}   = "X";
         last SWITCH;
       };
       $form->{duetyp} eq "12" && do {
-        $form->{fromdate} = "$form->{year}-12-01";
-        $form->{todate}   = "$form->{year}-12-31";
+        $form->{fromdate} = "$form->{year}1201";
+        $form->{todate}   = "$form->{year}1231";
         $form->{"0412"}   = "X";
         last SWITCH;
       };
     }
   }
 
-  #$myconfig = \%myconfig;
-  #$myconfig->{dateformat} = 'yyyy-mm-dd';
-  #$form->{fromdate}= $locale->date(\%myconfig, $form->{fromdate}, 0, 0, 0);
-  #$form->{todate}= $locale->date(\%myconfig, $form->{todate}, 0, 0, 0);
+  # using dates in ISO-8601 format: yyyymmmdd  for Postgres...
   USTVA->ustva(\%myconfig, \%$form);
-
-  #??($form->{department}) = split /--/, $form->{department};
+  
+  # reformat Dates to dateformat
+  $form->{fromdate}= $locale->date(\%myconfig, $form->{fromdate}, 0, 0, 0);
+  
+  $form->{todate} = $form->current_date($myconfig) unless $form->{todate};
+  $form->{todate}= $locale->date(\%myconfig, $form->{todate}, 0, 0, 0);
 
   $form->{period} =
     $locale->date(\%myconfig, $form->current_date(\%myconfig), 1, 0, 0);
-  $form->{todate} = $form->current_date($myconfig) unless $form->{todate};
+
 
   # if there are any dates construct a where
   if ($form->{fromdate} || $form->{todate}) {
diff --git a/sql/Germany-DATEV-SKR03EU-chart.sql b/sql/Germany-DATEV-SKR03EU-chart.sql
index 602c2d0fb..1395d0bfc 100644
--- a/sql/Germany-DATEV-SKR03EU-chart.sql
+++ b/sql/Germany-DATEV-SKR03EU-chart.sql
@@ -377,15 +377,67 @@ INSERT INTO chart (accno, description, charttype, category, link, gifi_accno, ta
 INSERT INTO chart (accno, description, charttype, category, link, gifi_accno, taxkey_id, pos_ustva, pos_bwa, pos_bilanz, pos_eur, datevautomatik) VALUES ('2215', 'Zinsabschlagsteuer', 'A', 'E', '', '2215', 0, NULL, 35, NULL, NULL, FALSE);
 INSERT INTO chart (accno, description, charttype, category, link, gifi_accno, taxkey_id, pos_ustva, pos_bwa, pos_bilanz, pos_eur, datevautomatik) VALUES ('2218', 'Anrechb.Solidari.zuschlag a.Zinsabschlagst.', 'A', 'E', '', '2218', 0, NULL, 35, NULL, NULL, FALSE);
 
-INSERT INTO tax (rate, taxkey, taxdescription) values ('0','0','Keine Steuer');
-INSERT INTO tax (rate, taxkey, taxdescription) values ('0','1','Umsatzsteuerfrei');
-insert into tax VALUES ((select id from chart where accno = '1771'),'0.07','1771','2','Umsatzsteuer 7%');
-INSERT INTO tax VALUES ((select id from chart where accno = '1775'),'0.16','1775','3','Umsatzsteuer 16%');
-INSERT INTO tax VALUES ((select id from chart where accno = '1571'),'0.07','1571','8','Vorsteuer 7%');
-insert into tax VALUES ((select id from chart where accno = '1575'),'0.16','1575','9','Vorsteuer 16%');
-insert into tax (taxkey, taxdescription) values ('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','Steuerpflicht. EG-Lieferungen 7%');
-insert into tax (chart_id,rate,taxnumber, taxkey, taxdescription) values ((select id from chart where accno = '1772'),'0.16','1773','13','Steuerpflicht. EG-Lieferungen 16%');
-insert into tax (chart_id,rate,taxnumber, taxkey, taxdescription) values ((select id from chart where accno = '1572'),'0.07','1572','18','Steuerpflicht. EG-Erwerb 7%');
-insert into tax (chart_id,rate,taxnumber, taxkey, taxdescription) values ((select id from chart where accno = '1572'),'0.16','1573','19','Steuerpflicht. EG-Erwerb 16%');
\ No newline at end of file
+-- insert actual tax 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%');
+
+-- 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. Seite 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');
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 068ee2319..7ff7ad4d3 100644
--- a/sql/Pg-upgrade-2.1.1-2.1.2.sql
+++ b/sql/Pg-upgrade-2.1.1-2.1.2.sql
@@ -252,7 +252,72 @@ 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);
@@ -261,3 +326,5 @@ ALTER TABLE project ADD constraint project_projectnumber_key UNIQUE(projectnumbe
 ALTER TABLE ar ADD COLUMN deliverydate date;
 
 update defaults set version = '2.1.2';
+
+
diff --git a/sql/Swiss-German-chart.sql b/sql/Swiss-German-chart.sql
index 75959efd2..8de5b5898 100644
--- a/sql/Swiss-German-chart.sql
+++ b/sql/Swiss-German-chart.sql
@@ -1,6 +1,6 @@
 -- Swiss chart of accounts
 -- adapted to numeric representation of chart no.
---
+-- contributed by Martin Krung
 INSERT INTO chart (accno,description,charttype,gifi_accno,category,link) VALUES ('10000','AKTIVEN','H','1','A','');
 INSERT INTO chart (accno,description,charttype,gifi_accno,category,link) VALUES ('11000','UMLAUFSVERMÖGEN','H','10000','A','');
 INSERT INTO chart (accno,description,charttype,gifi_accno,category,link) VALUES ('11100','Flüssige Mittel','H','11000','A','');
@@ -151,10 +151,10 @@ INSERT INTO chart (accno,description,charttype,gifi_accno,category,link) VALUES
 INSERT INTO chart (accno,description,charttype,gifi_accno,category,link) VALUES ('21231','Passive Rechnungsabgrenzung','A','21230','L','');
 INSERT INTO chart (accno,description,charttype,gifi_accno,category,link) VALUES ('67002','Produkteentwicklung','A','67000','E','');
 --
-insert into tax (chart_id,rate) values ((select id from chart where accno = '21222'),0.036);
-insert into tax (chart_id,rate) values ((select id from chart where accno = '21223'),0.076);
-insert into tax (chart_id,rate) values ((select id from chart where accno = '21221'),0.024);
-insert into tax (chart_id,rate) values ((select id from chart where accno = '21224'),0.076);
+insert into tax (taxdescription,taxkey,chart_id,rate) values ('MWST 3.6%',1,(select id from chart where accno = '21222'),0.036);
+insert into tax (taxdescription,taxkey,chart_id,rate) values ('MWST 7.6%',2,(select id from chart where accno = '21223'),0.076);
+insert into tax (taxdescription,taxkey,chart_id,rate) values ('MWST 2.4%',3,(select id from chart where accno = '21221'),0.024);
+insert into tax (taxdescription,taxkey,chart_id,rate) values ('MWST 7.6% 1/2',4,(select id from chart where accno = '21224'),0.076);
 --
 update defaults set inventory_accno_id = (select id from chart where accno = '11121'), income_accno_id = (select id from chart where accno = '34002'), expense_accno_id = (select id from chart where accno = '42005'), fxgain_accno_id = (select id from chart where accno = '36005'), fxloss_accno_id = (select id from chart where accno = '39006'), invnumber = '2002000', sonumber = '2002000', ponumber = '2002000', curr = 'EUR:USD', weightunit = 'kg';
 --
diff --git a/sql/liste.sql b/sql/liste.sql
index 1da446b87..27dd28f0d 100644
--- a/sql/liste.sql
+++ b/sql/liste.sql
@@ -373,6 +373,54 @@ UPDATE chart set taxkey_id=0, pos_bwa=35 WHERE accno='2214';
 UPDATE chart set taxkey_id=0, pos_bwa=35 WHERE accno='2215';
 UPDATE chart set taxkey_id=0, pos_bwa=35 WHERE accno='2218';
 
+-- UStVA Link to SKR03/2006
+-- Let this structure like it is, please.
+-- This structure is based on the sequence of the USTVA 2006
+-- The old statements above, will be mixed in the next version
+-- 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. Seite 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');
-- 
2.20.1