From 78f615a41212683647d9c1f0900b300598235a41 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Jan=20B=C3=BCren?= Date: Fri, 11 Feb 2022 12:56:26 +0100 Subject: [PATCH] =?utf8?q?Anzahlungsrechnung:=20Transferkonten=20f=C3=BCr?= =?utf8?q?=20Steuerbuchungen=20anlegen?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit In defaults persistent setzen Verrechnungskonto muss doch ein Sammelkonto (Link: AR) sein --- ...faults_advance_payment_transfer_charts.sql | 57 +++++++++++++++++++ sql/Pg-upgrade2/new_chart_3260_1711.sql | 37 ++++++++++++ sql/Pg-upgrade2/new_chart_3272_1718.sql | 37 ++++++++++++ 3 files changed, 131 insertions(+) create mode 100644 sql/Pg-upgrade2/defaults_advance_payment_transfer_charts.sql create mode 100644 sql/Pg-upgrade2/new_chart_3260_1711.sql create mode 100644 sql/Pg-upgrade2/new_chart_3272_1718.sql diff --git a/sql/Pg-upgrade2/defaults_advance_payment_transfer_charts.sql b/sql/Pg-upgrade2/defaults_advance_payment_transfer_charts.sql new file mode 100644 index 000000000..9205e2eba --- /dev/null +++ b/sql/Pg-upgrade2/defaults_advance_payment_transfer_charts.sql @@ -0,0 +1,57 @@ +-- @tag: defaults_advance_payment_transfer_charts +-- @description: Standardkonten für erhaltene versteuerte Anzahlungen 7% und 19% setzen +-- @depends:new_chart_3260_1711 new_chart_3272_1718 + + +ALTER TABLE defaults ADD COLUMN advance_payment_taxable_19_id INTEGER; +ALTER TABLE defaults ADD COLUMN advance_payment_taxable_7_id INTEGER; + +DO $$ +BEGIN + + IF ( SELECT coa FROM defaults ) = 'Germany-DATEV-SKR03EU' THEN + DECLARE + clearing_accno text := '1718'; + + BEGIN + IF ( SELECT COUNT(accno) FROM chart WHERE accno LIKE clearing_accno ) = 1 THEN + UPDATE defaults SET advance_payment_taxable_19_id = (SELECT id FROM chart WHERE accno LIKE clearing_accno); + END IF; + END; + END IF; + + IF ( SELECT coa FROM defaults ) = 'Germany-DATEV-SKR04EU' THEN + DECLARE + clearing_accno text := '3272'; + + BEGIN + IF ( SELECT COUNT(accno) FROM chart WHERE accno LIKE clearing_accno ) = 1 THEN + UPDATE defaults SET advance_payment_taxable_19_id = (SELECT id FROM chart WHERE accno LIKE clearing_accno); + END IF; + END; + END IF; + + IF ( SELECT coa FROM defaults ) = 'Germany-DATEV-SKR03EU' THEN + DECLARE + clearing_accno text := '1711'; + + BEGIN + IF ( SELECT COUNT(accno) FROM chart WHERE accno LIKE clearing_accno ) = 1 THEN + UPDATE defaults SET advance_payment_taxable_7_id = (SELECT id FROM chart WHERE accno LIKE clearing_accno); + END IF; + END; + END IF; + + IF ( SELECT coa FROM defaults ) = 'Germany-DATEV-SKR04EU' THEN + DECLARE + clearing_accno text := '3260'; + + BEGIN + IF ( SELECT COUNT(accno) FROM chart WHERE accno LIKE clearing_accno ) = 1 THEN + UPDATE defaults SET advance_payment_taxable_7_id = (SELECT id FROM chart WHERE accno LIKE clearing_accno); + END IF; + END; + END IF; + + +END $$; diff --git a/sql/Pg-upgrade2/new_chart_3260_1711.sql b/sql/Pg-upgrade2/new_chart_3260_1711.sql new file mode 100644 index 000000000..a9fb7d4ae --- /dev/null +++ b/sql/Pg-upgrade2/new_chart_3260_1711.sql @@ -0,0 +1,37 @@ +-- @tag: new_chart_3260_1711 +-- @description: Neues Konto "Erhaltene, versteuerte Anzahlungen 7 % USt (Verbindlichkeiten)" +-- @depends: release_3_5_8 + + +DO $$ +BEGIN + + IF ( SELECT coa FROM defaults ) = 'Germany-DATEV-SKR03EU' THEN + DECLARE + new_accno text := '1711'; + + BEGIN + IF ( SELECT COUNT(accno) FROM chart WHERE accno LIKE new_accno ) = 0 THEN + INSERT INTO chart (accno, description, charttype, category, link, taxkey_id) + VALUES (new_accno, 'Erhaltene, versteuerte Anzahlungen 7 % USt (Verbindlichkeiten)','A', 'L', 'AR_amount', 0); + INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, startdate) + VALUES ((SELECT id FROM chart WHERE accno LIKE new_accno), 0, 0, '1970-01-01'); + END IF; + END; + END IF; + + IF ( SELECT coa FROM defaults ) = 'Germany-DATEV-SKR04EU' THEN + DECLARE + new_accno text := '3260'; + + BEGIN + IF ( SELECT COUNT(accno) FROM chart WHERE accno LIKE new_accno ) = 0 THEN + INSERT INTO chart (accno, description, charttype, category, link, taxkey_id) + VALUES (new_accno, 'Erhaltene, versteuerte Anzahlungen 7 % USt (Verbindlichkeiten)','A', 'L', 'AR_amount', 0); + INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, startdate) + VALUES ((SELECT id FROM chart WHERE accno LIKE new_accno), 0, 0, '1970-01-01'); + END IF; + END; + END IF; + +END $$; diff --git a/sql/Pg-upgrade2/new_chart_3272_1718.sql b/sql/Pg-upgrade2/new_chart_3272_1718.sql new file mode 100644 index 000000000..24c973ad8 --- /dev/null +++ b/sql/Pg-upgrade2/new_chart_3272_1718.sql @@ -0,0 +1,37 @@ +-- @tag: new_chart_3272_1718 +-- @description: Neues Konto "Erhaltene, versteuerte Anzahlungen 19 % USt (Verbindlichkeiten)" +-- @depends: release_3_5_8 + + +DO $$ +BEGIN + + IF ( SELECT coa FROM defaults ) = 'Germany-DATEV-SKR03EU' THEN + DECLARE + new_accno text := '1718'; + + BEGIN + IF ( SELECT COUNT(accno) FROM chart WHERE accno LIKE new_accno ) = 0 THEN + INSERT INTO chart (accno, description, charttype, category, link, taxkey_id) + VALUES (new_accno, 'Erhaltene, versteuerte Anzahlungen 19 % USt (Verbindlichkeiten)','A', 'L', 'AR_amount', 0); + INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, startdate) + VALUES ((SELECT id FROM chart WHERE accno LIKE new_accno), 0, 0, '1970-01-01'); + END IF; + END; + END IF; + + IF ( SELECT coa FROM defaults ) = 'Germany-DATEV-SKR04EU' THEN + DECLARE + new_accno text := '3272'; + + BEGIN + IF ( SELECT COUNT(accno) FROM chart WHERE accno LIKE new_accno ) = 0 THEN + INSERT INTO chart (accno, description, charttype, category, link, taxkey_id) + VALUES (new_accno, 'Erhaltene, versteuerte Anzahlungen 19 % USt (Verbindlichkeiten)','A', 'L', 'AR_amount', 0); + INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, startdate) + VALUES ((SELECT id FROM chart WHERE accno LIKE new_accno), 0, 0, '1970-01-01'); + END IF; + END; + END IF; + +END $$; -- 2.20.1