X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/4858eee496c36d20673f0e2bcb680abc887fb8ba..9ea55a4e0dceba7470a477744cf29deb95eb09a8:/sql/Pg-upgrade2/konjunkturpaket_2020_SKR03.sql

diff --git a/sql/Pg-upgrade2/konjunkturpaket_2020_SKR03.sql b/sql/Pg-upgrade2/konjunkturpaket_2020_SKR03.sql
index 8ff52d69d..90ae22f52 100644
--- a/sql/Pg-upgrade2/konjunkturpaket_2020_SKR03.sql
+++ b/sql/Pg-upgrade2/konjunkturpaket_2020_SKR03.sql
@@ -3,110 +3,98 @@
 -- @depends: release_3_5_5
 -- @ignore: 0
 
--- TODO:
--- how to deal with old 16% charts in SKR03?
--- A) move to their correct taxkeys, 5 and 8, like for SKR04
---    and then create new versions of 3 and 9 with same taxkey
--- UST 5%, already exists in SKR03, so rename it, but also add new taxkeys
-
--- DEBUG
-
 DO $$
 BEGIN
 
 IF ( select coa from defaults ) = 'Germany-DATEV-SKR03EU' THEN
 
-  UPDATE tax SET taxdescription = 'OLD ' || taxdescription WHERE (taxkey = 3 or taxkey = 9) and rate = 0.16;
+  -- DEBUG
+  -- UPDATE tax SET taxdescription = 'OLD ' || taxdescription WHERE (taxkey = 3 or taxkey = 9) and rate = 0.16;
 
-  -- rename some of the charts
+  -- rename some of the charts, 1773 already exists in kivitendo as Umsatzsteuer 16% innergem.Erwerb
+  -- this is being used by taxkey 13, which is called "Steuerpflichtige EG-Lieferung zum vollen Steuersatz" in kivitendo
+  -- in DATEV taxkey 13 is: innergem. Lieferung ohne USt-IdNr. and should use a different chart
   UPDATE chart SET description = 'Umsatzsteuer 5 %' where accno = '1773';
 
-  -- rename charts if they weren't 't already changed
+  -- rename charts if they weren't already changed
   UPDATE chart SET description = 'ErlÃ¶se 19 % / 16 % USt' where accno = '8400' and description = 'ErlÃ¶se 16%/19% USt.';
   UPDATE chart SET description = 'ErlÃ¶se 7 % / 5 % USt'   where accno = '8300' and description = 'ErlÃ¶se 7%USt';
 
-  -- move old 16% taxkeys to their proper taxkeys, should be 5 and 7
-  UPDATE tax SET taxkey = 5 WHERE taxkey = 3 and rate = 0.16;
-  UPDATE tax SET taxkey = 7 WHERE taxkey = 9 and rate = 0.16;
+  -- there are two strategies for updating the taxkeys.
 
+  -- 1) in any case we need to add the 2 new cases for 5%: 2/0.05/1773 and 8/0.05/1568
 
-  -- new charts
-  INSERT INTO chart (accno, description, charttype, category, link, taxkey_id, pos_bwa, pos_bilanz, pos_eur, datevautomatik)
-         VALUES ('8732','GewÃ¤hrte Skonti 5% USt','A', 'I', 'AR_paid', 2, 1, null,1, 't');
+  -- 2) default kivi SKR03 already has the correct configuration for 16%, with two entries 3/0.16/1775 and 9/0.16/1575
+  --   a) we could move those to 5 and 7, and then create new 3/0.16/1775 and 9/0.16/1575 entries
+  --   b) simply keep those entries and don't use 5 and 7 (in which case ar/ap/gl must use deliverydate), or create 5 and 7 manually if needed
 
-  INSERT INTO chart (accno, description, charttype, category, link, taxkey_id, pos_bwa, pos_bilanz, pos_eur, datevautomatik)
-         VALUES ('3732','Erhaltene Skonti 5 % Vorsteuer','A', 'E', 'AP_paid', 8, 4, null, null, 't');
+  -- strategy a:
+  -- datev reactivated the previously reserved chart 1775 in 2020, but it still exists in kivitendo (at least for SKR03)
+  -- with a taxkey starting from 2007 and pointing to the existing automatic tax chart 1775
 
-  -- SKR03
-  -- datev reactivated the previously reserved chart 1775 in 2020, but it still exists in kivitendo (at least for SKR03) with a taxkey starting from 2007 and pointing to the existing automatic tax chart 1775
-  -- so we don't need to to anything!
-  --       3 | 0.16000 | Umsatzsteuer                                                 | 1775  | Umsatzsteuer 16%
+  -- strategy b:
+  -- UPDATE tax SET taxkey = 5 WHERE taxkey = 3 and rate = 0.16;
+  -- UPDATE tax SET taxkey = 7 WHERE taxkey = 9 and rate = 0.16;
 
   -- rename old 8735 to 8736
   UPDATE chart SET accno = '8736', description = 'GewÃ¤hrte Skonti 19 % USt' where accno = '8735' and description = 'GewÃ¤hrte Skonti 16%/19% USt.';
-  -- create new 8735 with 16%
 
-  -- create new 16% chart for GewÃ¤hrte Skonti
+  -- new charts, each of these will need a manual taxkey entry for 2020-07-01 after their tax entries are added
+  -- 8732, 3732, 8735, 3737
+  INSERT INTO chart (accno, description, charttype, category, link, taxkey_id, pos_bwa, pos_bilanz, pos_eur, datevautomatik)
+         VALUES ('8732','GewÃ¤hrte Skonti 5% USt','A', 'I', 'AR_paid', 2, 1, null,1, 't');
+
+  INSERT INTO chart (accno, description, charttype, category, link, taxkey_id, pos_bwa, pos_bilanz, pos_eur, datevautomatik)
+         VALUES ('3732','Erhaltene Skonti 5 % Vorsteuer','A', 'E', 'AP_paid', 8, 4, null, null, 't');
+
+  -- create new 16% charts Skonto
   INSERT INTO chart(accno,                description, charttype, category,      link, taxkey_id, pos_bwa, pos_bilanz, pos_eur, datevautomatik, pos_er)
             VALUES ('8735','GewÃ¤hrte Skonti 16 % USt',       'A',      'I', 'AR_paid',         3,       1,       null,       1,            't',      1);
 
-  -- create new chart for Abziehbare Vorsteuer 5 % with taxkey 8
+  INSERT INTO chart(accno,                description, charttype, category,       link, taxkey_id, pos_bwa, pos_bilanz, pos_eur, datevautomatik, pos_er)
+            VALUES ('3737','Erhaltene Skonti 16 % USt',       'A',      'E', 'AP_paid',         9,       4,       null,    null,            't',   null);
+
+  -- create new chart for Abziehbare Vorsteuer 5 % with taxkey 8 for 3732
   INSERT INTO chart (accno, description, charttype, category, link, taxkey_id, pos_bwa, pos_bilanz, pos_eur, datevautomatik, pos_er)
          VALUES ('1568','Abziehbare Vorsteuer 5 %','A', 'E', 'AP_tax:IC_taxpart:IC_taxservice', 8, null, null, 27, 't', 27);
+  INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate)
+               VALUES ( (select id from chart where accno = '1568'), 0, 0, 66, '1970-01-01');
 
   -- taxkeys can't be inserted until the new taxes exist
 
+  -- new taxes:
+  -- 5% cases for 2 Umsatzsteuer and 8 Vorsteuer
   INSERT INTO tax (chart_id, rate, taxkey, taxdescription, chart_categories, skonto_sales_chart_id, skonto_purchase_chart_id)
   VALUES ( (select id from chart where accno = '1773'), 0.05, 2, 'Umsatzsteuer', 'I', (select id from chart where accno = '8732'), null),
-         ( (select id from chart where accno = '1775'), 0.16, 3, 'Umsatzsteuer', 'I', (select id from chart where accno = '8735'), null),
-         ( (select id from chart where accno = '1575'), 0.16, 9, 'Vorsteuer',    'E', null, (select id from chart where accno = '3735')),
+         -- don't add these two entries if we keep the original two 16% accounts, instead better to add new tax entries with taxkey 5 and 7
+         -- ( (select id from chart where accno = '1775'), 0.16, 3, 'Umsatzsteuer', 'I', (select id from chart where accno = '8735'), null),
+         -- ( (select id from chart where accno = '1575'), 0.16, 9, 'Vorsteuer',    'E', null, (select id from chart where accno = '3735')),
          ( (select id from chart where accno = '1568'), 0.05, 8, 'Vorsteuer',    'E', null, (select id from chart where accno = '3732'));
 
-  UPDATE tax SET skonto_sales_chart_id = (select id from chart where accno = '8735') where taxkey = 3 and rate = 0.16 and skonto_sales_chart_id is null;
+  UPDATE tax SET skonto_sales_chart_id    = (select id from chart where accno = '8735') where taxkey = 3 and rate = 0.16 and skonto_sales_chart_id    is null;
+  UPDATE tax SET skonto_purchase_chart_id = (select id from chart where accno = '3737') where taxkey = 9 and rate = 0.16 and skonto_purchase_chart_id is null;
 
-  -- new taxkeys for 5% and 16% only need one startdate, not valid before and won't change back to anything later
+  -- new taxkeys for 5% charts only need one startdate, not valid before and won't change back to anything later
   -- these taxkeys won't be valid on 2020-06-30, so won't be affected later by big taxkeys update
+  -- However, this will also cause opening the charts before 2020-07-01 via the
+  -- interface to break, as AM.pm always calls get_active_taxkey and there won't
+  -- be an active taxkey before 2020-07-01.
+  -- Alternatively you could set those active from 2020-06-01 and in the taxkey upgrade script check for taxkey entries before that date
   INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate)
                VALUES ( (select id from chart where accno = '8732'), (select id from tax where rate = 0.05 and taxkey = 2 and chart_id = (select id from chart where accno = '1773')), 2, 861, '2020-07-01');
 
   INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate)
-               VALUES ( (select id from chart where accno = '8735'), (select id from tax where rate = 0.16 and taxkey = 3 and chart_id = (select id from chart where accno = '1775')), 3, 81, '2020-07-01');
+               VALUES ( (select id from chart where accno = '3732'), (select id from tax where rate = 0.05 and taxkey = 8 and chart_id = (select id from chart where accno = '1568')), 8, 861, '2020-07-01');
 
-  -- INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate)
-  --              VALUES ( (select id from chart where accno = '8400'), (select id from tax where rate = 0.16 and taxkey = 3 and chart_id = (select id from chart where accno = '1775')), 3, 81, '2020-07-01'); -- is 81 correct, or 51?
+  -- 8735 / 3737 - these were never created in the original SKR03, so also start using them from 2020-07-01
+  -- taxkey for GewÃ¤hrte Skonti 16 % USt pointing to tax 1775 Umsatzsteuer 16%
+  INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate)
+               VALUES ( (select id from chart where accno = '8735'), (select id from tax where rate = 0.16 and taxkey = 3 and chart_id = (select id from chart where accno = '1775')), 3, 81, '2020-07-01');
 
-  -- INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate)
-  --              VALUES ( (select id from chart where accno = '8400'), (select id from tax where rate = 0.19 and taxkey = 3 and chart_id = (select id from chart where accno = '1776')), 3, 81, '2021-01-01');
+  -- taxkey for Erhaltene Skonti 16 % USt pointing to tax 1575 Vorsteuer 16%
+  INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate)
+               VALUES ( (select id from chart where accno = '3737'), (select id from tax where rate = 0.16 and taxkey = 9 and chart_id = (select id from chart where accno = '1575')), 9, 66, '2020-07-01');
 
-  -- the taxkeys for the existing charts will be updated in a later update
 END IF;
 
 END $$;
-
-
--- select t.taxkey,
---       t.rate,
---       t.taxdescription,
---       c.accno,
---       c.description
---  from tax t
---       left join chart c on (c.id = t.chart_id)
---order by t.taxkey;
--- taxkey |  rate   |                        taxdescription                        | accno |                  description
-----------+---------+--------------------------------------------------------------+-------+------------------------------------------------
---      0 | 0.00000 | Keine Steuer                                                 | â      | â 
---      1 | 0.00000 | USt-frei                                                     | â      | â 
---      2 | 0.07000 | Umsatzsteuer                                                 | 1771  | Umsatzsteuer 7%
---      3 | 0.19000 | Umsatzsteuer                                                 | 1776  | Umsatzsteuer 19 %
---      3 | 0.16000 | Umsatzsteuer                                                 | 1775  | Umsatzsteuer 16%
---      8 | 0.07000 | Vorsteuer                                                    | 1571  | Abziehbare Vorsteuer 7%
---      9 | 0.16000 | Vorsteuer                                                    | 1575  | Abziehbare Vorsteuer 16%
---      9 | 0.19000 | Vorsteuer                                                    | 1576  | Abziehbare Vorsteuer 19 %
---     10 | 0.00000 | Im anderen EU-Staat steuerpflichtige Lieferung               | 1767  | Im anderen EG-Staat steuerpfl. Lieferung
---     11 | 0.00000 | Steuerfreie innergem. Lieferung an Abnehmer mit Id.-Nr.      | â      | â 
---     12 | 0.07000 | Steuerpflichtige EG-Lieferung zum ermÃ¤Ãigten Steuersatz      | 1772  | Umsatzsteuer 7% innergem.Erwerb
---     13 | 0.19000 | Steuerpflichtige EG-Lieferung zum vollen Steuersatz          | 1774  | Umsatzsteuer aus innergem. Erwerb 19 %
---     13 | 0.16000 | Steuerpflichtige EG-Lieferung zum vollen Steuersatz          | 1773  | Umsatzsteuer 16% innergem.Erwerb
---     18 | 0.07000 | Steuerpflichtiger innergem. Erwerb zum ermÃ¤Ãigten Steuersatz | 1572  | Abziehbare Vorsteuer 7% innergem. Erwerb
---     19 | 0.19000 | Steuerpflichtiger innergem. Erwerb zum vollen Steuersatz     | 1574  | Abziehbare Vorsteuer aus innergem. Erwerb 19 %
---     19 | 0.16000 | Steuerpflichtiger innergem. Erwerb zum vollen Steuersatz     | 1572  | Abziehbare Vorsteuer 7% innergem. Erwerb
