Willkommen im kivitendo Forum! Hier erweitern und teilen AnwenderInnen und EntwicklerInnen ihr Wissen.

Teste kivitendo!

kivitendo Demo

kivitendo Demo mit Schweizer Kontenplan und neuem Layout

Unterstützt kivitendo mit der Basis-Subskription!

0 Punkte

Hallo ich habe eine Neuinstallation v3.5.8 durchgeführt und habe ein Datenbank dump aus v 3.5.0 eingespielt

Wenn ich mich anmelde und das Dtenbakupdate aktiviere bekomme ich folgenden Fehler:

was kann ich tun

The database update/creation did not succeed. The file sql/Pg-upgrade2/konjunkturpaket_2020_SKR03.sql containing the following query failed:
DO $$
BEGIN

IF ( select coa from defaults ) = 'Germany-DATEV-SKR03EU' THEN

-- DEBUG
-- UPDATE tax SET taxdescription = 'OLD ' || taxdescription WHERE (taxkey = 3 or taxkey = 9) and rate = 0.16;

-- 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 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';

-- 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

-- 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

-- 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

-- 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.';

-- 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);

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),

     -- 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_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% 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 = '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');

-- 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');

-- 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');

END IF;

END $$
The error message was: ERROR: null value in column "tax_id" violates not-null constraint
DETAIL: Failing row contains (35856, 35848, null, 3, 81, 01.07.2020).
CONTEXT: SQL statement "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')"

PL/pgSQL function inline_code_block line 86 at SQL statement
All changes in that file have been reverted.:

von (220 Punkte)

2 Antworten

0 Punkte

Hallo,
hast du in der 3.5 schon selbst Hand angelegt und die MWST. von 2020 angepasst wg des Konjunkturpaketes?

Wenn du ein bisschen fit bist in SQL kannst Du mal diese Abfrage ausführen und schauen ob Du einen Wert bekommst

select id from tax where rate = 0.16 and taxkey = 3 and chart_id = (select id from chart where accno = '1775'))

Wenn das mit der MwSt bei dir so stimmt könntest Du die sql-update Dateien des Konjunkturpaketes auf

-- @ignore: 1

setzen, fällt Dir aber vielleicht später mal auf die Füße. Ansonsten müsstest Du mal das analysieren lassen

Geoffrey Richardson von kivitec hat das damals geschrieben

Beste Grüße
Werner kivitendodienstleister

von (18.9k Punkte)
0 Punkte

Hallo, vielen Dank für die Schnelle Antwort

Nach langem Suchen hat folgendes geholfen:

ich habe jetzt Versuchsweise mal null value in column "tax_id" violates not-null constraint das not null für die Tabelle herausgenommen, dann hats funktioniert. danach habe ich nach den Nullwerten gesucht und diese von Hand korrigiert

von (220 Punkte)
...