+-- 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);
INSERT INTO chart (accno, description, charttype, category, link, gifi_accno, taxkey_id, pos_ustva, pos_bwa, pos_bilanz, pos_eur, datevautomatik) VALUES ('0650', 'Vblk.geg.Kreditinst.- Restlaufzeit grösser als 5 Jahre', 'A', 'A', '', '0650', 0, 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 ('4510', 'Kfz-Steuer', 'A', 'E', 'AP_amount', '4510', 0, NULL, 19, NULL, 15, FALSE);
INSERT INTO chart (accno, description, charttype, category, link, gifi_accno, taxkey_id, pos_ustva, pos_bwa, pos_bilanz, pos_eur, datevautomatik) VALUES ('4520', 'Kfz-Versicherungen', 'A', 'E', 'AP_amount', '4520', 0, NULL, 14, NULL, 16, FALSE);
-INSERT INTO chart (accno, description, charttype, category, link, gifi_accno, taxkey_id, pos_ustva, pos_bwa, pos_bilanz, pos_eur, datevautomatik) VALUES ('1771', 'Umsatzsteuer 7%', 'A', 'I', 'AR_tax:IC_taxpart:IC_taxservice:CT_tax', '1771', 0, NULL, NULL, NULL, 5, FALSE);
-INSERT INTO chart (accno, description, charttype, category, link, gifi_accno, taxkey_id, pos_ustva, pos_bwa, pos_bilanz, pos_eur, datevautomatik) VALUES ('1775', 'Umsatzsteuer 16%', 'A', 'I', 'AR_tax:IC_taxpart:IC_taxservice:CT_tax', '1775', 0, NULL, NULL, NULL, 6, FALSE);
+INSERT INTO chart (accno, description, charttype, category, link, gifi_accno, taxkey_id, pos_ustva, pos_bwa, pos_bilanz, pos_eur, datevautomatik) VALUES ('1771', 'Umsatzsteuer 7%', 'A', 'I', 'AR_tax:IC_taxpart:IC_taxservice:CT_tax', '1771', 861, NULL, NULL, NULL, 5, FALSE);
+INSERT INTO chart (accno, description, charttype, category, link, gifi_accno, taxkey_id, pos_ustva, pos_bwa, pos_bilanz, pos_eur, datevautomatik) VALUES ('1775', 'Umsatzsteuer 16%', 'A', 'I', 'AR_tax:IC_taxpart:IC_taxservice:CT_tax', '1775', 511, NULL, NULL, NULL, 6, FALSE);
INSERT INTO chart (accno, description, charttype, category, link, gifi_accno, taxkey_id, pos_ustva, pos_bwa, pos_bilanz, pos_eur, datevautomatik) VALUES ('1767', 'Im anderen EG-Staat steuerpfl. Lieferung', 'A', ' ', '', '1767', 10, NULL, 0, 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 ('0853', 'Satzungsm.Rücklagen 0% Vorbelast.(st.Einlagekto.)', 'A', 'Q', '', '0853', 0, 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 ('1607', 'Vblk.a.LuL ohne Vorsteuer (EÜR)', 'A', 'L', 'AP', '1607', 0, 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 ('2746', 'Steuerfr.Erträge a.d.Auflös.v.SoPo m.Rücklageanteil', 'A', 'I', '', '2746', 0, NULL, NULL, NULL, 2, FALSE);
INSERT INTO chart (accno, description, charttype, category, link, gifi_accno, taxkey_id, pos_ustva, pos_bwa, pos_bilanz, pos_eur, datevautomatik) VALUES ('2747', 'Sonst.steuerfreie Betriebseinnahmen', 'A', 'I', '', '2747', 0, NULL, NULL, NULL, 2, FALSE);
INSERT INTO chart (accno, description, charttype, category, link, gifi_accno, taxkey_id, pos_ustva, pos_bwa, pos_bilanz, pos_eur, datevautomatik) VALUES ('2797', 'Entnahmen a.satzungsmäßigen Rücklagen', 'A', 'E', '', '2797', 0, 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 ('3559', 'Steuerfreie Einfuhren', 'A', 'E', '', '3559', 0, NULL, NULL, NULL, 8, FALSE);
+INSERT INTO chart (accno, description, charttype, category, link, gifi_accno, taxkey_id, pos_ustva, pos_bwa, pos_bilanz, pos_eur, datevautomatik) VALUES ('3559', 'Steuerfreie Einfuhren', 'A', 'E', 'AP_amount:IC_cogs', '3559', 0, 91, NULL, NULL, 8, FALSE);
INSERT INTO chart (accno, description, charttype, category, link, gifi_accno, taxkey_id, pos_ustva, pos_bwa, pos_bilanz, pos_eur, datevautomatik) VALUES ('3580', 'Wareneinkauf z.allg.Umsatzsteuersatz (EÜR)', 'A', 'E', '', '3580', 0, 0, 4, NULL, 8, FALSE);
INSERT INTO chart (accno, description, charttype, category, link, gifi_accno, taxkey_id, pos_ustva, pos_bwa, pos_bilanz, pos_eur, datevautomatik) VALUES ('3581', 'Wareneinkauf z.erm.Umsatzsteuersatz(EÜR)', 'A', 'E', '', '3581', 0, 0, 4, NULL, 8, FALSE);
INSERT INTO chart (accno, description, charttype, category, link, gifi_accno, taxkey_id, pos_ustva, pos_bwa, pos_bilanz, pos_eur, datevautomatik) VALUES ('3582', 'Wareneinkauf ohne Vorsteuerabzug(EÜR)', 'A', 'E', '', '3582', 0, 0, 4, NULL, 8, FALSE);
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);
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);
-INSERT INTO tax (rate, taxkey, taxdescription) values ('0','0','Keine Steuer');
-INSERT INTO tax (rate, taxkey, taxdescription) values ('0','1','Umsatzsteuerfrei');
-insert into tax VALUES ((select id from chart where accno = '1771'),'0.07','1771','2','Umsatzsteuer 7%');
-INSERT INTO tax VALUES ((select id from chart where accno = '1775'),'0.16','1775','3','Umsatzsteuer 16%');
-INSERT INTO tax VALUES ((select id from chart where accno = '1571'),'0.07','1571','8','Vorsteuer 7%');
-insert into tax VALUES ((select id from chart where accno = '1575'),'0.16','1575','9','Vorsteuer 16%');
-insert into tax (taxkey, taxdescription) values ('10','Im anderen EG-Staat steuerpfl. Lieferung');
-insert into tax (taxkey, taxdescription) values ('11','Steuerfreie EG-Lief. an Abn. mit UStIdNr');
-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%');
-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%');
-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%');
-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%');
\ No newline at end of file
+-- insert actual tax values for SKR03
+INSERT INTO tax (rate, taxkey, taxdescription) VALUES ('0','0','Keine Steuer');
+INSERT INTO tax (rate, taxkey, taxdescription) VALUES ('0','1','Umsatzsteuerfrei (mit Vorsteuerabzug)');
+INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id FROM chart WHERE accno = '1771'),'0.07','1771','2','Umsatzsteuer 7%');
+INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id FROM chart WHERE accno = '1775'),'0.16','1775','3','Umsatzsteuer 16%');
+INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id FROM chart WHERE accno = '1571'),'0.07','1571','8','Vorsteuer 7%');
+INSERT INTO tax (chart_id, rate, taxnumber, taxkey, taxdescription) VALUES ((SELECT id FROM chart WHERE accno = '1575'),'0.16','1575','9','Vorsteuer 16%');
+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');
+INSERT INTO tax (taxkey, taxdescription) VALUES ('11','Steuerfreie EG-Lief. an Abn. mit UStIdNr');
+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');
+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');
+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
+-- Created by Udo Spallek
+--
+-- 1. Page UStVA
+
+UPDATE chart SET pos_ustva=41 WHERE accno IN ('8125', '8130', '8140', '8724', '8808', '8828');
+UPDATE chart SET pos_ustva=44 WHERE accno IN ('8135');
+UPDATE chart SET pos_ustva=49 WHERE accno IN ('');
+UPDATE chart SET pos_ustva=43 WHERE accno IN ('2402', '8120', '8150', '8505', '8625', '8705', '8807', '8827');
+UPDATE chart SET pos_ustva=48 WHERE accno IN ('8100', '8110', '8504', '8609');
+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');
+UPDATE chart SET pos_ustva=511 WHERE accno IN ('1775');
+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');
+UPDATE chart SET pos_ustva=861 WHERE accno IN ('1771');
+UPDATE chart SET pos_ustva=35 WHERE accno IN ('2407', '2409', '8723', '8729', '8736', '8764', '8794');
+UPDATE chart SET pos_ustva=36 WHERE accno IN ('');
+UPDATE chart SET pos_ustva=77 WHERE accno IN ('');
+UPDATE chart SET pos_ustva=76 WHERE accno IN ('8190');
+UPDATE chart SET pos_ustva=80 WHERE accno IN ('');
+UPDATE chart SET pos_ustva=91 WHERE accno IN ('');
+UPDATE chart SET pos_ustva=97 WHERE accno IN ('3425', '3725');
+UPDATE chart SET pos_ustva=971 WHERE accno IN ('1773');
+UPDATE chart SET pos_ustva=93 WHERE accno IN ('3420', '3724');
+UPDATE chart SET pos_ustva=931 WHERE accno IN ('1772');
+UPDATE chart SET pos_ustva=95 WHERE accno IN ('3727');
+UPDATE chart SET pos_ustva=98 WHERE accno IN ('');
+UPDATE chart SET pos_ustva=94 WHERE accno IN ('');
+UPDATE chart SET pos_ustva=96 WHERE accno IN ('1784');
+UPDATE chart SET pos_ustva=42 WHERE accno IN ('');
+UPDATE chart SET pos_ustva=60 WHERE accno IN ('8337');
+UPDATE chart SET pos_ustva=45 WHERE accno IN ('8338', '8339', '8950');
+-- 2. Seite UStVA
+UPDATE chart SET pos_ustva=52 WHERE accno IN ('');
+UPDATE chart SET pos_ustva=53 WHERE accno IN ('');
+UPDATE chart SET pos_ustva=73 WHERE accno IN ('');
+UPDATE chart SET pos_ustva=74 WHERE accno IN ('');
+UPDATE chart SET pos_ustva=84 WHERE accno IN ('3110', '3115', '3120', '3125');
+UPDATE chart SET pos_ustva=85 WHERE accno IN ('1785', '1786');
+UPDATE chart SET pos_ustva=65 WHERE accno IN ('1782');
+UPDATE chart SET pos_ustva=66 WHERE accno IN ('1570', '1571', '1575', '1576');
+UPDATE chart SET pos_ustva=61 WHERE accno IN ('1572', '1573');
+UPDATE chart SET pos_ustva=62 WHERE accno IN ('1588');
+UPDATE chart SET pos_ustva=67 WHERE accno IN ('1578', '1579');
+UPDATE chart SET pos_ustva=63 WHERE accno IN ('1577');
+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'));
projects / kivitendo-erp.git / blobdiff