Revision 63a1c75d
Von Stephan Köhler vor fast 19 Jahren hinzugefügt
| bin/mozilla/ustva.pl | ||
|---|---|---|
| 29 | 29 |
#use strict; |
| 30 | 30 |
#no strict 'refs'; |
| 31 | 31 |
#use diagnostics; |
| 32 |
#use warnings FATAL=> 'all'; |
|
| 32 |
#use warnings; # FATAL=> 'all';
|
|
| 33 | 33 |
#use vars qw($locale $form %myconfig); |
| 34 | 34 |
#our ($myconfig); |
| 35 | 35 |
#use CGI::Carp "fatalsToBrowser"; |
| ... | ... | |
| 76 | 76 |
$form->{title} = $locale->text('UStVA');
|
| 77 | 77 |
$form->{kz10} = ''; #Berichtigte Anmeldung? Ja =1
|
| 78 | 78 |
|
| 79 |
# $accrual = ($eur) ? "" : "checked"; |
|
| 80 |
# $cash = ($eur) ? "checked" : ""; |
|
| 81 |
my $year = ''; |
|
| 82 |
my $null = ''; |
|
| 83 |
($null, $null, $null, $null, $null, $year, $null, $null, $null) = |
|
| 84 |
localtime(); |
|
| 85 |
$year += 1900; |
|
| 79 |
my $year = substr($form->datetonum($form->current_date(\%myconfig), \%myconfig),0,4); |
|
| 86 | 80 |
|
| 87 | 81 |
my $department = ''; |
| 88 | 82 |
local $hide = ''; |
| ... | ... | |
| 233 | 227 |
|
| 234 | 228 |
# accounting years if SQL-Ledger Version < 2.4.1 |
| 235 | 229 |
# $year = $form->{year} * 1;
|
| 236 |
@years = sort { $b <=> $a } (2000 .. ($year+1));
|
|
| 230 |
@years = sort { $b <=> $a } (2003 .. ($year+1));
|
|
| 237 | 231 |
$form->{all_years} = \@years;
|
| 238 | 232 |
} |
| 239 | 233 |
map { $form->{selectaccountingyear} .= qq|<option>$_\n| }
|
| ... | ... | |
| 539 | 533 |
sub ustva_vorauswahl {
|
| 540 | 534 |
$lxdebug->enter_sub(); |
| 541 | 535 |
#Aktuelles Datum zerlegen: |
| 542 |
$locale->date(\%myconfig, $form->current_date(\%myconfig, '0', '0'), 0) =~ |
|
| 543 |
/(\d\d).(\d\d).(\d\d\d\d)/; |
|
| 536 |
my $date = $form->datetonum($form->current_date(\%myconfig), \%myconfig); |
|
| 544 | 537 |
|
| 545 | 538 |
#$locale->date($myconfig, $form->current_date($myconfig), 0)=~ /(\d\d).(\d\d).(\d\d\d\d)/; |
| 546 |
$form->{day} = $1;
|
|
| 547 |
$form->{month} = $2;
|
|
| 548 |
$form->{year} = $3;
|
|
| 539 |
$form->{day} = substr($date, 6, 2);
|
|
| 540 |
$form->{month} = substr($date, 4, 2);
|
|
| 541 |
$form->{year} = substr($date, 0, 4);
|
|
| 542 |
$lxdebug->message(LXDebug::DEBUG1, qq| |
|
| 543 |
Actual date from Database: $date\n |
|
| 544 |
Actual year from Database: $form->{year}\n
|
|
| 545 |
Actual day from Database: $form->{day}\n
|
|
| 546 |
Actual month from Database: $form->{month}\n|);
|
|
| 547 |
|
|
| 549 | 548 |
my $sel = ''; |
| 550 | 549 |
my $yymmdd = ''; |
| 551 | 550 |
|
| ... | ... | |
| 585 | 584 |
|
| 586 | 585 |
my $yy = $form->{year} * 10000;
|
| 587 | 586 |
$yymmdd = "$form->{year}$form->{month}$form->{day}" * 1;
|
| 587 |
$yymmdd = 20060121; |
|
| 588 | 588 |
$sel = ''; |
| 589 |
my $dfv = '0'; # Offset f?r Dauerfristverl?ngerung |
|
| 590 |
#$dfv = '100' if ($form->{FA_dauerfrist} eq '1');
|
|
| 589 |
my $dfv = '0'; |
|
| 590 |
|
|
| 591 |
# Offset f?r Dauerfristverl?ngerung |
|
| 592 |
$dfv = '100' if ($form->{FA_dauerfrist} eq '1');
|
|
| 591 | 593 |
|
| 592 | 594 |
SWITCH: {
|
| 593 | 595 |
$yymmdd <= ($yy + 110 + $dfv) && do {
|
| ... | ... | |
| 824 | 826 |
|
| 825 | 827 |
#forgotten the year --> thisyear |
| 826 | 828 |
if ($form->{year} !~ m/^\d\d\d\d$/) {
|
| 827 |
$locale->date(\$myconfig, $form->current_date(\$myconfig), 0) =~ |
|
| 828 |
/(\d\d\d\d)/; |
|
| 829 |
$form->{year} = $1;
|
|
| 829 |
$form->{year} = substr($form->datetonum($form->current_date(\%myconfig), \%myconfig),0,4);
|
|
| 830 |
$lxdebug->message(LXDebug::DEBUG1, qq|Actual year from Database: $form->{year}\n|);
|
|
| 830 | 831 |
} |
| 831 | 832 |
|
| 832 | 833 |
#yearly report |
| 833 | 834 |
if ($form->{duetyp} eq "13") {
|
| 834 |
$form->{fromdate} = "$form->{year}-01-01";
|
|
| 835 |
$form->{todate} = "$form->{year}-12-31";
|
|
| 835 |
$form->{fromdate} = "$form->{year}0101";
|
|
| 836 |
$form->{todate} = "$form->{year}1231";
|
|
| 836 | 837 |
} |
| 837 | 838 |
|
| 838 | 839 |
#Quater reports |
| 839 | 840 |
if ($form->{duetyp} eq "A") {
|
| 840 |
$form->{fromdate} = "$form->{year}-01-01";
|
|
| 841 |
$form->{todate} = "$form->{year}-03-31";
|
|
| 841 |
$form->{fromdate} = "$form->{year}0101";
|
|
| 842 |
$form->{todate} = "$form->{year}0331";
|
|
| 842 | 843 |
$form->{'0441'} = "X";
|
| 843 | 844 |
} |
| 844 | 845 |
if ($form->{duetyp} eq "B") {
|
| 845 |
$form->{fromdate} = "$form->{year}-04-01";
|
|
| 846 |
$form->{todate} = "$form->{year}-06-30";
|
|
| 846 |
$form->{fromdate} = "$form->{year}0401";
|
|
| 847 |
$form->{todate} = "$form->{year}0630";
|
|
| 847 | 848 |
$form->{'0442'} = "X";
|
| 848 | 849 |
} |
| 849 | 850 |
if ($form->{duetyp} eq "C") {
|
| 850 |
$form->{fromdate} = "$form->{year}-07-01";
|
|
| 851 |
$form->{todate} = "$form->{year}-09-30";
|
|
| 851 |
$form->{fromdate} = "$form->{year}0701";
|
|
| 852 |
$form->{todate} = "$form->{year}0930";
|
|
| 852 | 853 |
$form->{'0443'} = "X";
|
| 853 | 854 |
} |
| 854 | 855 |
if ($form->{duetyp} eq "D") {
|
| 855 |
$form->{fromdate} = "$form->{year}-10-01";
|
|
| 856 |
$form->{todate} = "$form->{year}-12-31";
|
|
| 856 |
$form->{fromdate} = "$form->{year}1001";
|
|
| 857 |
$form->{todate} = "$form->{year}1231";
|
|
| 857 | 858 |
$form->{'0444'} = "X";
|
| 858 | 859 |
} |
| 859 | 860 |
|
| 860 | 861 |
#Monthly reports |
| 861 | 862 |
SWITCH: {
|
| 862 | 863 |
$form->{duetyp} eq "01" && do {
|
| 863 |
$form->{fromdate} = "$form->{year}-01-01";
|
|
| 864 |
$form->{todate} = "$form->{year}-01-31";
|
|
| 864 |
$form->{fromdate} = "$form->{year}0101";
|
|
| 865 |
$form->{todate} = "$form->{year}0131";
|
|
| 865 | 866 |
$form->{'0401'} = "X";
|
| 866 | 867 |
last SWITCH; |
| 867 | 868 |
}; |
| 868 | 869 |
$form->{duetyp} eq "02" && do {
|
| 869 |
$form->{fromdate} = "$form->{year}-02-01";
|
|
| 870 |
$form->{fromdate} = "$form->{year}0201";
|
|
| 870 | 871 |
|
| 871 | 872 |
#this works from 1901 to 2099, 1900 and 2100 fail. |
| 872 | 873 |
my $leap = ($form->{year} % 4 == 0) ? "29" : "28";
|
| 873 |
$form->{todate} = "$form->{year}-02-$leap";
|
|
| 874 |
$form->{todate} = "$form->{year}02$leap";
|
|
| 874 | 875 |
$form->{"0402"} = "X";
|
| 875 | 876 |
last SWITCH; |
| 876 | 877 |
}; |
| 877 | 878 |
$form->{duetyp} eq "03" && do {
|
| 878 |
$form->{fromdate} = "$form->{year}-03-01";
|
|
| 879 |
$form->{todate} = "$form->{year}-03-31";
|
|
| 879 |
$form->{fromdate} = "$form->{year}0301";
|
|
| 880 |
$form->{todate} = "$form->{year}0331";
|
|
| 880 | 881 |
$form->{"0403"} = "X";
|
| 881 | 882 |
last SWITCH; |
| 882 | 883 |
}; |
| 883 | 884 |
$form->{duetyp} eq "04" && do {
|
| 884 |
$form->{fromdate} = "$form->{year}-04-01";
|
|
| 885 |
$form->{todate} = "$form->{year}-04-30";
|
|
| 885 |
$form->{fromdate} = "$form->{year}0401";
|
|
| 886 |
$form->{todate} = "$form->{year}0430";
|
|
| 886 | 887 |
$form->{"0404"} = "X";
|
| 887 | 888 |
last SWITCH; |
| 888 | 889 |
}; |
| 889 | 890 |
$form->{duetyp} eq "05" && do {
|
| 890 |
$form->{fromdate} = "$form->{year}-05-01";
|
|
| 891 |
$form->{todate} = "$form->{year}-05-31";
|
|
| 891 |
$form->{fromdate} = "$form->{year}0501";
|
|
| 892 |
$form->{todate} = "$form->{year}0531";
|
|
| 892 | 893 |
$form->{"0405"} = "X";
|
| 893 | 894 |
last SWITCH; |
| 894 | 895 |
}; |
| 895 | 896 |
$form->{duetyp} eq "06" && do {
|
| 896 |
$form->{fromdate} = "$form->{year}-06-01";
|
|
| 897 |
$form->{todate} = "$form->{year}-06-30";
|
|
| 897 |
$form->{fromdate} = "$form->{year}0601";
|
|
| 898 |
$form->{todate} = "$form->{year}0630";
|
|
| 898 | 899 |
$form->{"0406"} = "X";
|
| 899 | 900 |
last SWITCH; |
| 900 | 901 |
}; |
| 901 | 902 |
$form->{duetyp} eq "07" && do {
|
| 902 |
$form->{fromdate} = "$form->{year}-07-01";
|
|
| 903 |
$form->{todate} = "$form->{year}-07-31";
|
|
| 903 |
$form->{fromdate} = "$form->{year}0701";
|
|
| 904 |
$form->{todate} = "$form->{year}0731";
|
|
| 904 | 905 |
$form->{"0407"} = "X";
|
| 905 | 906 |
last SWITCH; |
| 906 | 907 |
}; |
| 907 | 908 |
$form->{duetyp} eq "08" && do {
|
| 908 |
$form->{fromdate} = "$form->{year}-08-01";
|
|
| 909 |
$form->{todate} = "$form->{year}-08-31";
|
|
| 909 |
$form->{fromdate} = "$form->{year}0801";
|
|
| 910 |
$form->{todate} = "$form->{year}0831";
|
|
| 910 | 911 |
$form->{"0408"} = "X";
|
| 911 | 912 |
last SWITCH; |
| 912 | 913 |
}; |
| 913 | 914 |
$form->{duetyp} eq "09" && do {
|
| 914 |
$form->{fromdate} = "$form->{year}-09-01";
|
|
| 915 |
$form->{todate} = "$form->{year}-09-30";
|
|
| 915 |
$form->{fromdate} = "$form->{year}0901";
|
|
| 916 |
$form->{todate} = "$form->{year}0930";
|
|
| 916 | 917 |
$form->{"0409"} = "X";
|
| 917 | 918 |
last SWITCH; |
| 918 | 919 |
}; |
| 919 | 920 |
$form->{duetyp} eq "10" && do {
|
| 920 |
$form->{fromdate} = "$form->{year}-10-01";
|
|
| 921 |
$form->{todate} = "$form->{year}-10-31";
|
|
| 921 |
$form->{fromdate} = "$form->{year}1001";
|
|
| 922 |
$form->{todate} = "$form->{year}1031";
|
|
| 922 | 923 |
$form->{"0410"} = "X";
|
| 923 | 924 |
last SWITCH; |
| 924 | 925 |
}; |
| 925 | 926 |
$form->{duetyp} eq "11" && do {
|
| 926 |
$form->{fromdate} = "$form->{year}-11-01";
|
|
| 927 |
$form->{todate} = "$form->{year}-11-30";
|
|
| 927 |
$form->{fromdate} = "$form->{year}1101";
|
|
| 928 |
$form->{todate} = "$form->{year}1130";
|
|
| 928 | 929 |
$form->{"0411"} = "X";
|
| 929 | 930 |
last SWITCH; |
| 930 | 931 |
}; |
| 931 | 932 |
$form->{duetyp} eq "12" && do {
|
| 932 |
$form->{fromdate} = "$form->{year}-12-01";
|
|
| 933 |
$form->{todate} = "$form->{year}-12-31";
|
|
| 933 |
$form->{fromdate} = "$form->{year}1201";
|
|
| 934 |
$form->{todate} = "$form->{year}1231";
|
|
| 934 | 935 |
$form->{"0412"} = "X";
|
| 935 | 936 |
last SWITCH; |
| 936 | 937 |
}; |
| 937 | 938 |
} |
| 938 | 939 |
} |
| 939 | 940 |
|
| 940 |
#$myconfig = \%myconfig; |
|
| 941 |
#$myconfig->{dateformat} = 'yyyy-mm-dd';
|
|
| 942 |
#$form->{fromdate}= $locale->date(\%myconfig, $form->{fromdate}, 0, 0, 0);
|
|
| 943 |
#$form->{todate}= $locale->date(\%myconfig, $form->{todate}, 0, 0, 0);
|
|
| 941 |
# using dates in ISO-8601 format: yyyymmmdd for Postgres... |
|
| 944 | 942 |
USTVA->ustva(\%myconfig, \%$form); |
| 945 |
|
|
| 946 |
#??($form->{department}) = split /--/, $form->{department};
|
|
| 943 |
|
|
| 944 |
# reformat Dates to dateformat |
|
| 945 |
$form->{fromdate}= $locale->date(\%myconfig, $form->{fromdate}, 0, 0, 0);
|
|
| 946 |
|
|
| 947 |
$form->{todate} = $form->current_date($myconfig) unless $form->{todate};
|
|
| 948 |
$form->{todate}= $locale->date(\%myconfig, $form->{todate}, 0, 0, 0);
|
|
| 947 | 949 |
|
| 948 | 950 |
$form->{period} =
|
| 949 | 951 |
$locale->date(\%myconfig, $form->current_date(\%myconfig), 1, 0, 0); |
| 950 |
$form->{todate} = $form->current_date($myconfig) unless $form->{todate};
|
|
| 952 |
|
|
| 951 | 953 |
|
| 952 | 954 |
# if there are any dates construct a where |
| 953 | 955 |
if ($form->{fromdate} || $form->{todate}) {
|
| sql/Germany-DATEV-SKR03EU-chart.sql | ||
|---|---|---|
| 377 | 377 |
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);
|
| 378 | 378 |
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);
|
| 379 | 379 |
|
| 380 |
INSERT INTO tax (rate, taxkey, taxdescription) values ('0','0','Keine Steuer');
|
|
| 381 |
INSERT INTO tax (rate, taxkey, taxdescription) values ('0','1','Umsatzsteuerfrei');
|
|
| 382 |
insert into tax VALUES ((select id from chart where accno = '1771'),'0.07','1771','2','Umsatzsteuer 7%'); |
|
| 383 |
INSERT INTO tax VALUES ((select id from chart where accno = '1775'),'0.16','1775','3','Umsatzsteuer 16%'); |
|
| 384 |
INSERT INTO tax VALUES ((select id from chart where accno = '1571'),'0.07','1571','8','Vorsteuer 7%'); |
|
| 385 |
insert into tax VALUES ((select id from chart where accno = '1575'),'0.16','1575','9','Vorsteuer 16%'); |
|
| 386 |
insert into tax (taxkey, taxdescription) values ('10','Im anderen EG-Staat steuerpfl. Lieferung');
|
|
| 387 |
insert into tax (taxkey, taxdescription) values ('11','Steuerfreie EG-Lief. an Abn. mit UStIdNr');
|
|
| 388 |
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%'); |
|
| 389 |
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%'); |
|
| 390 |
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%'); |
|
| 391 |
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%'); |
|
| 380 |
-- insert actual tax values for SKR03 |
|
| 381 |
INSERT INTO tax (rate, taxkey, taxdescription) VALUES ('0','0','Keine Steuer');
|
|
| 382 |
INSERT INTO tax (rate, taxkey, taxdescription) VALUES ('0','1','Umsatzsteuerfrei (mit Vorsteuerabzug)');
|
|
| 383 |
INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id FROM chart WHERE accno = '1771'),'0.07','1771','2','Umsatzsteuer 7%'); |
|
| 384 |
INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id FROM chart WHERE accno = '1775'),'0.16','1775','3','Umsatzsteuer 16%'); |
|
| 385 |
INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id FROM chart WHERE accno = '1571'),'0.07','1571','8','Vorsteuer 7%'); |
|
| 386 |
INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id FROM chart WHERE accno = '1575'),'0.16','1575','9','Vorsteuer 16%'); |
|
| 387 |
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'); |
|
| 388 |
INSERT INTO tax (taxkey, taxdescription) VALUES ('11','Steuerfreie EG-Lief. an Abn. mit UStIdNr');
|
|
| 389 |
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'); |
|
| 390 |
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'); |
|
| 391 |
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%'); |
|
| 392 |
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%'); |
|
| 393 |
|
|
| 394 |
-- UStVA Link to SKR03/2006 |
|
| 395 |
-- Let this structure like it is, please. |
|
| 396 |
-- This structure is based on the sequence of the USTVA 2006 |
|
| 397 |
-- Created by Udo Spallek |
|
| 398 |
-- |
|
| 399 |
-- 1. Page UStVA |
|
| 400 |
|
|
| 401 |
UPDATE chart SET pos_ustva=41 WHERE accno IN ('8125', '8130', '8140', '8724', '8808', '8828');
|
|
| 402 |
UPDATE chart SET pos_ustva=44 WHERE accno IN ('8135');
|
|
| 403 |
UPDATE chart SET pos_ustva=49 WHERE accno IN ('');
|
|
| 404 |
UPDATE chart SET pos_ustva=43 WHERE accno IN ('2402', '8120', '8150', '8505', '8625', '8705', '8807', '8827');
|
|
| 405 |
UPDATE chart SET pos_ustva=48 WHERE accno IN ('8100', '8110', '8504', '8609');
|
|
| 406 |
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');
|
|
| 407 |
UPDATE chart SET pos_ustva=511 WHERE accno IN ('1775');
|
|
| 408 |
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');
|
|
| 409 |
UPDATE chart SET pos_ustva=861 WHERE accno IN ('1771');
|
|
| 410 |
UPDATE chart SET pos_ustva=35 WHERE accno IN ('2407', '2409', '8723', '8729', '8736', '8764', '8794');
|
|
| 411 |
UPDATE chart SET pos_ustva=36 WHERE accno IN ('');
|
|
| 412 |
UPDATE chart SET pos_ustva=77 WHERE accno IN ('');
|
|
| 413 |
UPDATE chart SET pos_ustva=76 WHERE accno IN ('8190');
|
|
| 414 |
UPDATE chart SET pos_ustva=80 WHERE accno IN ('');
|
|
| 415 |
UPDATE chart SET pos_ustva=91 WHERE accno IN ('');
|
|
| 416 |
UPDATE chart SET pos_ustva=97 WHERE accno IN ('3425', '3725');
|
|
| 417 |
UPDATE chart SET pos_ustva=971 WHERE accno IN ('1773');
|
|
| 418 |
UPDATE chart SET pos_ustva=93 WHERE accno IN ('3420', '3724');
|
|
| 419 |
UPDATE chart SET pos_ustva=931 WHERE accno IN ('1772');
|
|
| 420 |
UPDATE chart SET pos_ustva=95 WHERE accno IN ('3727');
|
|
| 421 |
UPDATE chart SET pos_ustva=98 WHERE accno IN ('');
|
|
| 422 |
UPDATE chart SET pos_ustva=94 WHERE accno IN ('');
|
|
| 423 |
UPDATE chart SET pos_ustva=96 WHERE accno IN ('1784');
|
|
| 424 |
UPDATE chart SET pos_ustva=42 WHERE accno IN ('');
|
|
| 425 |
UPDATE chart SET pos_ustva=60 WHERE accno IN ('8337');
|
|
| 426 |
UPDATE chart SET pos_ustva=45 WHERE accno IN ('8338', '8339', '8950');
|
|
| 427 |
-- 2. Seite UStVA |
|
| 428 |
UPDATE chart SET pos_ustva=52 WHERE accno IN ('');
|
|
| 429 |
UPDATE chart SET pos_ustva=53 WHERE accno IN ('');
|
|
| 430 |
UPDATE chart SET pos_ustva=73 WHERE accno IN ('');
|
|
| 431 |
UPDATE chart SET pos_ustva=74 WHERE accno IN ('');
|
|
| 432 |
UPDATE chart SET pos_ustva=84 WHERE accno IN ('3110', '3115', '3120', '3125');
|
|
| 433 |
UPDATE chart SET pos_ustva=85 WHERE accno IN ('1785', '1786');
|
|
| 434 |
UPDATE chart SET pos_ustva=65 WHERE accno IN ('1782');
|
|
| 435 |
UPDATE chart SET pos_ustva=66 WHERE accno IN ('1570', '1571', '1575', '1576');
|
|
| 436 |
UPDATE chart SET pos_ustva=61 WHERE accno IN ('1572', '1573');
|
|
| 437 |
UPDATE chart SET pos_ustva=62 WHERE accno IN ('1588');
|
|
| 438 |
UPDATE chart SET pos_ustva=67 WHERE accno IN ('1578', '1579');
|
|
| 439 |
UPDATE chart SET pos_ustva=63 WHERE accno IN ('1577');
|
|
| 440 |
UPDATE chart SET pos_ustva=64 WHERE accno IN ('1556', '1557', '1558', '1559');
|
|
| 441 |
UPDATE chart SET pos_ustva=59 WHERE accno IN ('');
|
|
| 442 |
UPDATE chart SET pos_ustva=69 WHERE accno IN ('1783');
|
|
| 443 |
UPDATE chart SET pos_ustva=39 WHERE accno IN ('1781');
|
|
| sql/Pg-upgrade-2.1.1-2.1.2.sql | ||
|---|---|---|
| 252 | 252 |
alter table invoice add column transdate text; |
| 253 | 253 |
alter table invoice add column cusordnumber text; |
| 254 | 254 |
-- |
| 255 |
|
|
| 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 |
-- |
|
| 256 | 321 |
-- |
| 257 | 322 |
-- add unqiue constraint to project |
| 258 | 323 |
ALTER TABLE project ADD constraint project_projectnumber_key UNIQUE(projectnumber); |
| ... | ... | |
| 261 | 326 |
ALTER TABLE ar ADD COLUMN deliverydate date; |
| 262 | 327 |
|
| 263 | 328 |
update defaults set version = '2.1.2'; |
| 329 |
|
|
| 330 |
|
|
| sql/Swiss-German-chart.sql | ||
|---|---|---|
| 1 | 1 |
-- Swiss chart of accounts |
| 2 | 2 |
-- adapted to numeric representation of chart no. |
| 3 |
-- |
|
| 3 |
-- contributed by Martin Krung
|
|
| 4 | 4 |
INSERT INTO chart (accno,description,charttype,gifi_accno,category,link) VALUES ('10000','AKTIVEN','H','1','A','');
|
| 5 | 5 |
INSERT INTO chart (accno,description,charttype,gifi_accno,category,link) VALUES ('11000','UMLAUFSVERM?GEN','H','10000','A','');
|
| 6 | 6 |
INSERT INTO chart (accno,description,charttype,gifi_accno,category,link) VALUES ('11100','Fl?ssige Mittel','H','11000','A','');
|
| ... | ... | |
| 151 | 151 |
INSERT INTO chart (accno,description,charttype,gifi_accno,category,link) VALUES ('21231','Passive Rechnungsabgrenzung','A','21230','L','');
|
| 152 | 152 |
INSERT INTO chart (accno,description,charttype,gifi_accno,category,link) VALUES ('67002','Produkteentwicklung','A','67000','E','');
|
| 153 | 153 |
-- |
| 154 |
insert into tax (chart_id,rate) values ((select id from chart where accno = '21222'),0.036);
|
|
| 155 |
insert into tax (chart_id,rate) values ((select id from chart where accno = '21223'),0.076);
|
|
| 156 |
insert into tax (chart_id,rate) values ((select id from chart where accno = '21221'),0.024);
|
|
| 157 |
insert into tax (chart_id,rate) values ((select id from chart where accno = '21224'),0.076);
|
|
| 154 |
insert into tax (taxdescription,taxkey,chart_id,rate) values ('MWST 3.6%',1,(select id from chart where accno = '21222'),0.036);
|
|
| 155 |
insert into tax (taxdescription,taxkey,chart_id,rate) values ('MWST 7.6%',2,(select id from chart where accno = '21223'),0.076);
|
|
| 156 |
insert into tax (taxdescription,taxkey,chart_id,rate) values ('MWST 2.4%',3,(select id from chart where accno = '21221'),0.024);
|
|
| 157 |
insert into tax (taxdescription,taxkey,chart_id,rate) values ('MWST 7.6% 1/2',4,(select id from chart where accno = '21224'),0.076);
|
|
| 158 | 158 |
-- |
| 159 | 159 |
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'; |
| 160 | 160 |
-- |
| sql/liste.sql | ||
|---|---|---|
| 373 | 373 |
UPDATE chart set taxkey_id=0, pos_bwa=35 WHERE accno='2215'; |
| 374 | 374 |
UPDATE chart set taxkey_id=0, pos_bwa=35 WHERE accno='2218'; |
| 375 | 375 |
|
| 376 |
-- UStVA Link to SKR03/2006 |
|
| 377 |
-- Let this structure like it is, please. |
|
| 378 |
-- This structure is based on the sequence of the USTVA 2006 |
|
| 379 |
-- The old statements above, will be mixed in the next version |
|
| 380 |
-- Created by Udo Spallek |
|
| 381 |
-- |
|
| 382 |
-- 1. Page UStVA |
|
| 376 | 383 |
|
| 377 |
|
|
| 378 |
|
|
| 384 |
UPDATE chart SET pos_ustva=41 WHERE accno IN ('8125', '8130', '8140', '8724', '8808', '8828');
|
|
| 385 |
UPDATE chart SET pos_ustva=44 WHERE accno IN ('8135');
|
|
| 386 |
UPDATE chart SET pos_ustva=49 WHERE accno IN ('');
|
|
| 387 |
UPDATE chart SET pos_ustva=43 WHERE accno IN ('2402', '8120', '8150', '8505', '8625', '8705', '8807', '8827');
|
|
| 388 |
UPDATE chart SET pos_ustva=48 WHERE accno IN ('8100', '8110', '8504', '8609');
|
|
| 389 |
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');
|
|
| 390 |
UPDATE chart SET pos_ustva=511 WHERE accno IN ('1775');
|
|
| 391 |
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');
|
|
| 392 |
UPDATE chart SET pos_ustva=861 WHERE accno IN ('1771');
|
|
| 393 |
UPDATE chart SET pos_ustva=35 WHERE accno IN ('2407', '2409', '8723', '8729', '8736', '8764', '8794');
|
|
| 394 |
UPDATE chart SET pos_ustva=36 WHERE accno IN ('');
|
|
| 395 |
UPDATE chart SET pos_ustva=77 WHERE accno IN ('');
|
|
| 396 |
UPDATE chart SET pos_ustva=76 WHERE accno IN ('8190');
|
|
| 397 |
UPDATE chart SET pos_ustva=80 WHERE accno IN ('');
|
|
| 398 |
UPDATE chart SET pos_ustva=91 WHERE accno IN ('');
|
|
| 399 |
UPDATE chart SET pos_ustva=97 WHERE accno IN ('3425', '3725');
|
|
| 400 |
UPDATE chart SET pos_ustva=971 WHERE accno IN ('1773');
|
|
| 401 |
UPDATE chart SET pos_ustva=93 WHERE accno IN ('3420', '3724');
|
|
| 402 |
UPDATE chart SET pos_ustva=931 WHERE accno IN ('1772');
|
|
| 403 |
UPDATE chart SET pos_ustva=95 WHERE accno IN ('3727');
|
|
| 404 |
UPDATE chart SET pos_ustva=98 WHERE accno IN ('');
|
|
| 405 |
UPDATE chart SET pos_ustva=94 WHERE accno IN ('');
|
|
| 406 |
UPDATE chart SET pos_ustva=96 WHERE accno IN ('1784');
|
|
| 407 |
UPDATE chart SET pos_ustva=42 WHERE accno IN ('');
|
|
| 408 |
UPDATE chart SET pos_ustva=60 WHERE accno IN ('8337');
|
|
| 409 |
UPDATE chart SET pos_ustva=45 WHERE accno IN ('8338', '8339', '8950');
|
|
| 410 |
-- 2. Seite UStVA |
|
| 411 |
UPDATE chart SET pos_ustva=52 WHERE accno IN ('');
|
|
| 412 |
UPDATE chart SET pos_ustva=53 WHERE accno IN ('');
|
|
| 413 |
UPDATE chart SET pos_ustva=73 WHERE accno IN ('');
|
|
| 414 |
UPDATE chart SET pos_ustva=74 WHERE accno IN ('');
|
|
| 415 |
UPDATE chart SET pos_ustva=84 WHERE accno IN ('3110', '3115', '3120', '3125');
|
|
| 416 |
UPDATE chart SET pos_ustva=85 WHERE accno IN ('1785', '1786');
|
|
| 417 |
UPDATE chart SET pos_ustva=65 WHERE accno IN ('1782');
|
|
| 418 |
UPDATE chart SET pos_ustva=66 WHERE accno IN ('1570', '1571', '1575', '1576');
|
|
| 419 |
UPDATE chart SET pos_ustva=61 WHERE accno IN ('1572', '1573');
|
|
| 420 |
UPDATE chart SET pos_ustva=62 WHERE accno IN ('1588');
|
|
| 421 |
UPDATE chart SET pos_ustva=67 WHERE accno IN ('1578', '1579');
|
|
| 422 |
UPDATE chart SET pos_ustva=63 WHERE accno IN ('1577');
|
|
| 423 |
UPDATE chart SET pos_ustva=64 WHERE accno IN ('1556', '1557', '1558', '1559');
|
|
| 424 |
UPDATE chart SET pos_ustva=59 WHERE accno IN ('');
|
|
| 425 |
UPDATE chart SET pos_ustva=69 WHERE accno IN ('1783');
|
|
| 426 |
UPDATE chart SET pos_ustva=39 WHERE accno IN ('1781');
|
|
Auch abrufbar als: Unified diff
Merge von 816-819,828 aus unstable: UStVa Bugfix, Bug 262 & 264
-816
Bug 262 fixed!
USTVA UPDATE der Positionen und Verknuepfungen.
Die UPDATEs sind relativ vollstaendig und sortiert,
so dass eine weitere Pflege einfach ist. taxkey's sind noch nicht
integriert, deshalb sind einige updates in liste.aql doppelt gemoppelt,
was m.E. unproblematisch ist, gleiches gilt für
den Germany-DATEV-SKR03EU-chart.sql
-817
Bug 264 fixed. Thanks to Martin Krung.
-818
Fixed date problems between database and screen-output,
reported by Holger on the devel-ML.
-819
Update table tax
-828
USTVA: Fixed upgrade Problems for tax table