X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=sql%2FGermany-DATEV-SKR03EU-chart.sql;h=42b605a47d92c5fcfd10bd0caf238629a1016939;hb=ca2faab78bcda704a153142517ad41d5ef559b34;hp=1395d0bfce2d30492fb07d5e5ada2de52cbe18c7;hpb=54e4131e091831e00a861fe2c4f53e344b87ddca;p=kivitendo-erp.git diff --git a/sql/Germany-DATEV-SKR03EU-chart.sql b/sql/Germany-DATEV-SKR03EU-chart.sql index 1395d0bfc..42b605a47 100644 --- 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); @@ -216,7 +216,7 @@ INSERT INTO chart (accno, description, charttype, category, link, gifi_accno, ta 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); @@ -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'));