Revision 51aa7d3c
Von Jan Büren vor mehr als 2 Jahren hinzugefügt
sql/Pg-upgrade2/clean_tax_18_19.pl | ||
---|---|---|
1 |
# @tag: clean_tax_18_19 |
|
2 |
# @description: Vorbereitung für neue Steuerschlüssel 18,19 |
|
3 |
# @depends: release_3_6_0 |
|
4 |
# @ignore: 0 |
|
5 |
package SL::DBUpgrade2::clean_tax_18_19; |
|
6 |
|
|
7 |
use strict; |
|
8 |
use utf8; |
|
9 |
|
|
10 |
use parent qw(SL::DBUpgrade2::Base); |
|
11 |
|
|
12 |
sub delete_alter_tax { |
|
13 |
my $self = shift; |
|
14 |
|
|
15 |
my $query = <<SQL; |
|
16 |
SELECT id from tax |
|
17 |
where chart_id is not null |
|
18 |
and taxkey = ? |
|
19 |
and reverse_charge_chart_id is null |
|
20 |
SQL |
|
21 |
my $q_fetch = <<SQL; |
|
22 |
SELECT trans_id |
|
23 |
FROM acc_trans where tax_id = ? |
|
24 |
LIMIT 1 |
|
25 |
SQL |
|
26 |
|
|
27 |
my $delete_taxkey = <<SQL; |
|
28 |
DELETE from taxkeys where tax_id = ? |
|
29 |
SQL |
|
30 |
|
|
31 |
my $delete_tax = <<SQL; |
|
32 |
DELETE from tax where id = ? |
|
33 |
SQL |
|
34 |
|
|
35 |
|
|
36 |
my $edit_tax = <<SQL; |
|
37 |
UPDATE tax set chart_id = NULL |
|
38 |
WHERE id = ? |
|
39 |
SQL |
|
40 |
|
|
41 |
|
|
42 |
my $h_fetch = $self->dbh->prepare($query); |
|
43 |
my $acc_fetch = $self->dbh->prepare($q_fetch); |
|
44 |
my $delete_tk = $self->dbh->prepare($delete_taxkey); |
|
45 |
my $delete_t = $self->dbh->prepare($delete_tax); |
|
46 |
my $edit_q = $self->dbh->prepare($edit_tax); |
|
47 |
|
|
48 |
|
|
49 |
my $tax_id; |
|
50 |
foreach ( qw(18 19) ) { |
|
51 |
$h_fetch->execute($_) || $::form->dberror($query); |
|
52 |
while (my $entry = $h_fetch->fetchrow_hashref) { |
|
53 |
$tax_id = $entry->{id}; |
|
54 |
next unless $tax_id; |
|
55 |
$edit_q->execute($tax_id) || $::form->dberror($edit_tax); |
|
56 |
$acc_fetch->execute($tax_id) || $::form->dberror($q_fetch); |
|
57 |
if (!$acc_fetch->fetchrow_hashref) { |
|
58 |
$delete_tk->execute($tax_id) || $::form->dberror($delete_tk); |
|
59 |
$delete_t ->execute($tax_id) || $::form->dberror($delete_t); |
|
60 |
} |
|
61 |
} |
|
62 |
} |
|
63 |
} |
|
64 |
|
|
65 |
sub run { |
|
66 |
my ($self) = @_; |
|
67 |
|
|
68 |
return 1 unless ($self->check_coa('Germany-DATEV-SKR03EU') ||$self->check_coa('Germany-DATEV-SKR04EU')); |
|
69 |
|
|
70 |
$self->delete_alter_tax; |
|
71 |
|
|
72 |
return 1; |
|
73 |
} |
|
74 |
|
|
75 |
1; |
sql/Pg-upgrade2/tax_reverse_charge_key_18.sql | ||
---|---|---|
1 | 1 |
-- @tag: tax_reverse_charge_key_18 |
2 | 2 |
-- @description: Reverse Charge für Kreditorenbelege Steuerschlüssel 18 |
3 |
-- @depends: release_3_6_0 |
|
3 |
-- @depends: release_3_6_0 clean_tax_18_19
|
|
4 | 4 |
-- @ignore: 0 |
5 | 5 |
|
6 | 6 |
INSERT INTO tax ( |
... | ... | |
41 | 41 |
|
42 | 42 |
-- if not defined |
43 | 43 |
insert into taxkeys(chart_id,tax_id,taxkey_id,startdate) SELECT (SELECT reverse_charge_chart_id FROM tax WHERE taxkey = '18' and rate = 0.07 and reverse_charge_chart_id is not null),0,0,'1970-01-01' WHERE NOT EXISTS |
44 |
(SELECT chart_id from taxkeys where chart_id = ( SELECT reverse_charge_chart_id FROM tax WHERE taxkey = '18' and rate = 0.07 and reverse_charge_chart_id is not null)); |
|
44 |
(SELECT chart_id from taxkeys where chart_id = ( SELECT reverse_charge_chart_id FROM tax WHERE taxkey = '18' and rate = 0.07 and reverse_charge_chart_id is not null)) |
|
45 |
AND (EXISTS (SELECT coa FROM defaults WHERE defaults.coa='Germany-DATEV-SKR04EU') OR EXISTS (SELECT coa FROM defaults WHERE defaults.coa='Germany-DATEV-SKR03EU')); |
|
46 |
|
|
45 | 47 |
-- if not defined |
46 | 48 |
insert into taxkeys(chart_id,tax_id,taxkey_id,startdate) SELECT (SELECT chart_id FROM tax WHERE taxkey = '18' and rate = 0.07 and reverse_charge_chart_id is not null),0,0,'1970-01-01' WHERE NOT EXISTS |
47 |
(SELECT chart_id from taxkeys where chart_id = ( SELECT chart_id FROM tax WHERE taxkey = '18' and rate = 0.07 and reverse_charge_chart_id is not null)); |
|
49 |
(SELECT chart_id from taxkeys where chart_id = ( SELECT chart_id FROM tax WHERE taxkey = '18' and rate = 0.07 and reverse_charge_chart_id is not null)) |
|
50 |
AND (EXISTS (SELECT coa FROM defaults WHERE defaults.coa='Germany-DATEV-SKR04EU') OR EXISTS (SELECT coa FROM defaults WHERE defaults.coa='Germany-DATEV-SKR03EU')); |
|
48 | 51 |
|
sql/Pg-upgrade2/tax_reverse_charge_key_19.sql | ||
---|---|---|
1 | 1 |
-- @tag: tax_reverse_charge_key_19 |
2 | 2 |
-- @description: Reverse Charge für Kreditorenbelege Steuerschlüssel 19 |
3 |
-- @depends: release_3_6_0 |
|
3 |
-- @depends: release_3_6_0 clean_tax_18_19
|
|
4 | 4 |
-- @ignore: 0 |
5 | 5 |
|
6 | 6 |
UPDATE tax set rate=0.19 where taxkey=94 AND reverse_charge_chart_id is not NULL; |
... | ... | |
56 | 56 |
|
57 | 57 |
-- if not defined |
58 | 58 |
insert into taxkeys(chart_id,tax_id,taxkey_id,startdate) SELECT (SELECT reverse_charge_chart_id FROM tax WHERE taxkey = '19' and rate = 0.19 and reverse_charge_chart_id is not null),0,0,'1970-01-01' WHERE NOT EXISTS |
59 |
(SELECT chart_id from taxkeys where chart_id = ( SELECT reverse_charge_chart_id FROM tax WHERE taxkey = '19' and rate = 0.19 and reverse_charge_chart_id is not null)); |
|
59 |
(SELECT chart_id from taxkeys where chart_id = ( SELECT reverse_charge_chart_id FROM tax WHERE taxkey = '19' and rate = 0.19 and reverse_charge_chart_id is not null)) |
|
60 |
AND (EXISTS (SELECT coa FROM defaults WHERE defaults.coa='Germany-DATEV-SKR04EU') OR EXISTS (SELECT coa FROM defaults WHERE defaults.coa='Germany-DATEV-SKR03EU')); |
|
60 | 61 |
-- if not defined |
61 | 62 |
insert into taxkeys(chart_id,tax_id,taxkey_id,startdate) SELECT (SELECT chart_id FROM tax WHERE taxkey = '19' and rate = 0.19 and reverse_charge_chart_id is not null),0,0,'1970-01-01' WHERE NOT EXISTS |
62 |
(SELECT chart_id from taxkeys where chart_id = ( SELECT chart_id FROM tax WHERE taxkey = '19' and rate = 0.19 and reverse_charge_chart_id is not null)); |
|
63 |
(SELECT chart_id from taxkeys where chart_id = ( SELECT chart_id FROM tax WHERE taxkey = '19' and rate = 0.19 and reverse_charge_chart_id is not null)) |
|
64 |
AND (EXISTS (SELECT coa FROM defaults WHERE defaults.coa='Germany-DATEV-SKR04EU') OR EXISTS (SELECT coa FROM defaults WHERE defaults.coa='Germany-DATEV-SKR03EU')); |
|
63 | 65 |
|
Auch abrufbar als: Unified diff
Aufräumskript für Steuerschlüssel 18,19 - Prüfung auf Kontenrahmen