Anpassungen an dem Kontenrahmen fuer neues Steuersystem

[kivitendo-erp.git] / sql / Germany-DATEV-SKR03EU-chart.sql

diff --git a/sql/Germany-DATEV-SKR03EU-chart.sql b/sql/Germany-DATEV-SKR03EU-chart.sql
index 7367c27..42b605a 100644 (file)
--- a/sql/Germany-DATEV-SKR03EU-chart.sql
+++ b/sql/Germany-DATEV-SKR03EU-chart.sql
@@ -1,6 +1,6 @@
 -- created by Yvonne Einberger
 -- contributed by Andre Schubert
--- 
+--
 INSERT INTO chart (accno, description, charttype, category, link, gifi_accno, taxkey_id, pos_ustva, pos_bwa, pos_bilanz, pos_eur, datevautomatik) VALUES ('0027', 'EDV-Software', 'A', 'A', 'AP_amount', '0027', 9, NULL, NULL, 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 ('0090', 'Geschäftsbauten', 'A', 'A', 'AP_amount', '0090', 9, NULL, NULL, 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 ('0200', 'Techn.Anlagen und Maschinen', 'A', 'A', 'AP_amount', '0200', 9, NULL, NULL, NULL, NULL, FALSE);
@@ -391,6 +391,19 @@ INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SEL
 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%');
 
+-- Ergaenzungen fuer 19% UmSt. ab 1.01.2007
+insert into taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate) select chart.id, tax.id, taxkey_id, pos_ustva, '1970-01-01' from chart LEFT JOIN tax on (tax.taxkey=chart.taxkey_id) WHERE taxkey_id is not null;
+insert into taxkeys (chart_id, tax_id, taxkey_id,startdate) SELECT 0, id, taxkey, '1970-01-01' FROM tax;
+INSERT INTO chart (accno, description, charttype, category, link, taxkey_id, pos_ustva, pos_eur)
+      VALUES ('1776','Umsatzsteuer 19 %', 'A', 'I', 'AR_tax:IC_taxpart:IC_taxservice:CT_tax', 0, 511,6);
+INSERT INTO chart (accno, description, charttype, category, link, taxkey_id, pos_ustva, pos_eur)
+      VALUES ('1576','Abziehbare Vorsteuer 19 %', 'A', 'E', 'AP_tax:IC_taxpart:IC_taxservice:CT_tax', 0, 66,27);
+INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id from CHART WHERE accno='1776'), 0.19, '1776', 3, 'Umsatzsteuer 19%');
+INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id from CHART WHERE accno='1576'), 0.19, '1576', 9, 'Vorsteuer 19%');
+insert into taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate) select chart.id, (SELECT id from tax where taxdescription='Umsatzsteuer 19%'), 3, pos_ustva, '2007-01-01' from chart WHERE taxkey_id=3;
+insert into taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate) select chart.id, (SELECT id from tax where taxdescription='Vorsteuer 19%'), 9, pos_ustva, '2007-01-01' from chart WHERE taxkey_id=9;
+
+
 -- UStVA Link to SKR03/2006
 -- Let this structure like it is, please.
 -- This structure is based on the sequence of the USTVA 2006
@@ -441,3 +454,59 @@ 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');
+
+-- Buchungsgruppen
+
+INSERT INTO buchungsgruppen
+  (description, inventory_accno_id,
+   income_accno_id_0, expense_accno_id_0,
+   income_accno_id_1, expense_accno_id_1,
+   income_accno_id_2, expense_accno_id_2,
+   income_accno_id_3, expense_accno_id_3)
+ VALUES
+  ('Standard 16%',
+   -- 3980: Bestand Waren
+   (SELECT id FROM chart WHERE accno = '3980'),
+   -- 8400: Erlöse 16% USt.
+   -- 3400: Wareneingang 16% Vorsteuer
+   (SELECT id FROM chart WHERE accno = '8400'),
+   (SELECT id FROM chart WHERE accno = '3400'),
+   -- 8320: Erlöse aus im and.EG-Land steuerpfl.Lieferungen
+   -- 3550: Steuerfreier innergem.Erwerb
+   (SELECT id FROM chart WHERE accno = '8320'),
+   (SELECT id FROM chart WHERE accno = '3550'),
+   -- 8315: Erlös Inland stpfl.EG-Lieferung 16%
+   -- 3425: Innergem.Erwerb 16% VorSt u. Ust
+   (SELECT id FROM chart WHERE accno = '8315'),
+   (SELECT id FROM chart WHERE accno = '3425'),
+    -- 8120: Steuerfreie Umsätze §4Nr.1a UstG
+    -- 3559: Steuerfreie Einfuhren
+   (SELECT id FROM chart WHERE accno = '8120'),
+   (SELECT id FROM chart WHERE accno = '3559'));
+
+INSERT INTO buchungsgruppen
+  (description, inventory_accno_id,
+   income_accno_id_0, expense_accno_id_0,
+   income_accno_id_1, expense_accno_id_1,
+   income_accno_id_2, expense_accno_id_2,
+   income_accno_id_3, expense_accno_id_3)
+ VALUES
+  ('Standard 7%',
+   -- 3980: Bestand Waren
+   (SELECT id FROM chart WHERE accno = '3980'),
+   -- 8300: Erlöse 7% USt.
+   -- 3300: Wareneingang 7% Vorsteuer
+   (SELECT id FROM chart WHERE accno = '8300'),
+   (SELECT id FROM chart WHERE accno = '3300'),
+   -- 8125: Steuerfrei innergem. Lieferungen §41bUStG
+   -- 3550: Steuerfreier innergem.Erwerb
+   (SELECT id FROM chart WHERE accno = '8125'),
+   (SELECT id FROM chart WHERE accno = '3550'),
+   -- 8310: Erlöse Inland stpfl. EG-Lieferung7%
+   -- 3420: Innergemein.Erwerb 7% VorSt u. Ust
+   (SELECT id FROM chart WHERE accno = '8310'),
+   (SELECT id FROM chart WHERE accno = '3420'),
+   -- 8120: Steuerfreie Umsätze §4Nr.1a UstG
+   -- 3559: Steuerfreie Einfuhren
+   (SELECT id FROM chart WHERE accno = '8120'),
+   (SELECT id FROM chart WHERE accno = '3559'));