1 -- @tag: ustva_setup_2007_update_chart_taxkeys_tax_skr04
2 -- @description: Anpassung der UStVA-Schlüssel für Konten 3801, 3806, 3804 und 4400
3 -- @depends: release_2_7_0
5 -- 3806 - neuer Eintrag pos_ustva 811 ab 2007 (falls noch nicht existiert)
6 -- 3801 - neuer Eintrag pos_ustva 861 ab 2007 (falls noch nicht existiert)
7 -- 4400 - pos_ustva von 51 auf 81 für Eintrag 2007
8 -- 3804 - pos_ustva Eintrag 891 ab 2007
11 chart_id, pos_ustva, startdate)
12 SELECT chart.id, '811', '2007-01-01'
13 FROM chart LEFT JOIN tax ON (chart.id = tax.chart_id)
14 WHERE chart.accno = '3806'
16 EXISTS ( -- update only for SKR04
17 SELECT coa FROM defaults
18 WHERE defaults.coa='Germany-DATEV-SKR04EU'
20 select * from taxkeys where chart_id = (select id from chart where accno = '3806') and pos_ustva = '811' and startdate = '2007-01-01' )
25 chart_id, pos_ustva, startdate)
26 SELECT chart.id, '861', '2007-01-01'
27 FROM chart LEFT JOIN tax ON (chart.id = tax.chart_id)
28 WHERE chart.accno = '3801'
30 EXISTS ( -- update only for SKR04
31 SELECT coa FROM defaults
32 WHERE defaults.coa='Germany-DATEV-SKR04EU'
34 select * from taxkeys where chart_id = (select id from chart where accno = '3801') and pos_ustva = '861' and startdate = '2007-01-01' )
38 UPDATE taxkeys SET pos_ustva = '81'
39 WHERE chart_id = (SELECT id FROM chart WHERE accno = '4400')
40 AND startdate = '2007-01-01'
43 SELECT coa FROM defaults
44 WHERE defaults.coa='Germany-DATEV-SKR04EU'
47 -- insert taxkey for 3804, but leave taxkey_id empty, because Kivitendo can't
48 -- handle this automatic booking and tax has to be booked manually
49 -- don't insert this key with this startdate if it already exists (was already added manually)
51 chart_id, pos_ustva, startdate)
52 SELECT chart.id, '891', '2007-01-01'
53 FROM chart LEFT JOIN tax ON (chart.id = tax.chart_id)
54 WHERE chart.accno = '3804'
56 EXISTS ( -- update only for SKR04
57 SELECT coa FROM defaults
58 WHERE defaults.coa='Germany-DATEV-SKR04EU'
60 select * from taxkeys where chart_id = (select id from chart where accno = '3804') and pos_ustva = '891' and startdate = '2007-01-01' )