Liebe Kivitendo-Community!
Folgende Ausgangssituation: Kivitendo 3.4.1 war über Jahre erfolgreich im Einsatz.
Jetzt erfolgte ein Systemupdate des VServers auf Ubuntu 20.02.1, wobei ich kivitendo neu aufgesetzt habe.
Ich bin direkt auf die 3.5.6.1 gegangen: kivitendo_auth frisch angelegt. Mandanten-DB aus Backup (.sql) via psql eingespielt, Einloggen mit dem Hauptbenutzer. Dann erfolgte das DB-Update, das durchlief mit Ausnahme der Updates für "Konjunkturpaket 2020". Fehlerlisting siehe unten. System war dann nicht mehr nutzbar. Neu aufgesetzt.
Ich habe mir beholfen, indem ich die Abhängigkeitn zum Konjunkturpaket in release_3_5_6[_1].sql entfernt habe und die entsprechenden .sql's gelöscht habe. Dannn lief das Update durch.
System war lauffähig, ich habe erfolgreich eine alte Rechnung ausgegeben und wollte meinen Erfolg feiern: Doch dann, kam beim Aufruf eines Angebotes der rote Balken:
Fehler!
Can't call method "tax" on an undefined value at /var/www/kivitendo/SL/DB/Helper/PriceTaxCalculator.pm line 114.
Daher stehe ich vor drei Fragen:
Kann jemand direkt aus der "tax"-Meldung etwas schließen? (Es betrifft Angebote und Aufträge, nicht aber Rechnungen und Lieferscheine)
Besteht ein offensichtlicher Zusammenhang zwischen meinen "Cheat" mit dem DB-Update und dem "tax"-Fehler?
Wie kann ich das Problem mit dem Konjunkturpaket (das ich inhaltlich/funktional nicht brauche!) sauberer lösen?
Vielen Dank für jeglichen Tipp!
Hier das Listing vom DB-Upgrade:
(Ich musste kürzen, weil nur 12000 Zeichen erlaubt.)
Führe konjunkturpaket_2020_SKR03.sql aus: Anpassung des Deutschen DATEV-Kontenrahmen für SKR03 Konjunkturpaket
Führe konjunkturpaket_2020_SKR04.sql aus: Anpassung des Deutschen DATEV-Kontenrahmen für SKR04 Konjunkturpaket
Führe konjunkturpaket_2020.sql aus: Anpassung der Steuersätze für 16%/5% für Deutsche DATEV-Kontenrahmen SKR03 und SKR04
Fehler!
The database update/creation did not succeed. The file sql/Pg-upgrade2/konjunkturpaket_2020.sql containing the following query failed:
DO $$
DECLARE
-- variables for main taxkey creation loop, not all are needed
_chart_id int;
_accno text;
_description text;
_startdates date[];
_tax_ids int[];
_taxkeyentry_id int[];
_taxkey_ids int[];
_rates numeric[];
_taxcharts text[];
current_taxkey record;
new_taxkey record;
_rate numeric;
_tax record; -- store the new tax we need to assign to a chart, e.g. 5%, 16%
_taxkey int;
_old_rate numeric;
_old_chart text;
_new_chart numeric;
_new_rate text;
_tax_conversion record;
BEGIN
IF ( select coa from defaults ) ~ 'DATEV' THEN
--begin;
--delete from taxkeys where startdate >= '2020-01-01';
-- create temp table temp_taxkey_conversions (taxkey int, old_rate numeric, new_rate numeric, tax_chart_skr03 text, tax_chart_skr04 text);
-- insert into temp_taxkey_conversions (taxkey, old_rate, new_rate, tax_chart_skr03, tax_chart_skr04) values
---- (2, 0.07, 0.05, '1773', '3803'), -- 5% case is handled by skr03 case -> needs different automatic chart: 1773 Umsatzsteuer 5% (SKR03, instead of 1771 Umsatzsteuer 7%) or 3803 Umsatzsteuer 5%
-- -- (8, 0.07, 0.05, null, null),
-- -- (3, 0.19, 0.16, null, null),
-- -- (9, 0.19, 0.16, null, null),
-- (13, 0.19, 0.16, null, null);
create temp table temp_taxkey_conversions (taxkey int, old_rate numeric, old_chart text, new_rate numeric, new_chart text);
IF ( select coa from defaults ) = 'Germany-DATEV-SKR03EU' THEN
insert into temp_taxkey_conversions (taxkey, old_rate, old_chart, new_rate, new_chart)
values (9, 0.19, '1576', 0.16, '1575'),
(8, 0.07, '1571', 0.05, '1568'),
(3, 0.19, '1776', 0.16, '1575'),
(2, 0.07, '1771', 0.05, '1775');
--1776 => 19%
--1775 => 16%
--1775 => 5%
--1771 => 7%
--
--VSt:
--1576 => 19%
--1575 => 16%
--1568 => 5%
--1571 => 7%
ELSE -- Germany-DATEV-SKR04EU
insert into temp_taxkey_conversions (taxkey, old_rate, old_chart, new_rate, new_chart)
values (9, 0.19, '1406', 0.16, '1405'),
(8, 0.07, '1401', 0.05, '1403'),
(3, 0.19, '3806', 0.16, '3805'),
(2, 0.07, '3801', 0.05, '3803');
END IF;
FOR _chart_id, _accno, _description, _startdates, _tax_ids, _taxkeyentry_id, _taxkey_ids, _rates, _taxcharts IN
select c.id as chart_id,
c.accno,
c.description,
array_agg(t.startdate order by t.startdate desc) as startdates,
array_agg(t.tax_id order by t.startdate desc) as tax_ids,
array_agg(t.id order by t.startdate desc) as taxkeyentry_id,
array_agg(t.taxkey_id order by t.startdate desc) as taxkey_ids,
array_agg(tax.rate order by t.startdate desc) as rates,
array_agg(tc.accno order by t.startdate desc) as taxcharts
from taxkeys t
left join chart c on (c.id = t.chart_id)
left join tax on (tax.id = t.tax_id)
left join chart tc on (tax.chart_id = tc.id)
where t.taxkey_id in (select taxkey from temp_taxkey_conversions) -- 2, 3, 8, 9
-- and (c.accno = '8400') -- debug
-- you can't filter for valid taxrates 19% or 7% here, as that would still leave the 16% rates as the current one
group by c.id,
c.accno,
c.description
order by c.accno
-- example output for human debugging:
-- chart_id | accno | description | startdates | tax_ids | taxkeyentry_id | taxkey_ids | rates | taxcharts
-- ----------+-------+---------------------+-------------------------+-----------+----------------+------------+-------------------+-------------
-- 184 | 8400 | Erlöse 16%/19% USt. | {2007-01-01,1970-01-01} | {777,379} | {793,676} | {3,3} | {0.19000,0.16000} | {1776,1775}
-- each chart with one of the applicable taxkeys should receive two new entries, one starting on 01.07.2020, the other on 01.01.2021
LOOP
-- 1. create new taxkey entry on 2020-07-01, using the active taxkey on 2020-06-30 as a template, but linking to a tax with a different tax rate
-- 2. create new taxkey entry on 2021-01-01, using the active taxkey on 2020-06-30 as a template, but with the new date
-- fetch tax information for 2020-06-30, one day before the change, this should also be the first entry in the ordered array aggregates
-- this can be used as the template for the reset on 2021-01-01
[...gekürzt...]
IF current_taxkey.rate != 0 THEN -- debug
-- _rate := null;
-- IF current_taxkey.rate = 0.19 THEN _rate := 0.16; END IF;
-- IF current_taxkey.rate = 0.07 THEN _rate := 0.05; END IF;
IF _old_rate is NULL THEN
-- option A: ignore rates which don't make sense, useful for upgrade mode
-- option B: throw exception, useful for manually testing script
-- A:
-- if the rate on 2020-06-30 is neither 19 or 7, simply ignore it, it is obviously not configured correctly
-- This is the case for SKR03 and chart 8315 (taxkey 13)
-- It might be better to throw an exception, however then the test cases don't run. Or just fix the chart via an upgrade script!
CONTINUE;
-- B:
-- RAISE EXCEPTION 'illegal current taxrate % on 2020-06-30 (startdate = %) for chart % with taxkey %, should be either 0.19 or 0.07',
-- current_taxkey.rate, current_taxkey.startdate,
-- (select accno from chart where id = current_taxkey.chart_id),
-- current_taxkey.taxkey_id;
END IF;
-- RAISE NOTICE 'current_taxkey.rate = %, desired rate = %, looking for taxkey_id %', current_taxkey.rate, _rate, _taxkey_ids[1];
-- if a chart was created way after 2007 and only ever configured for
-- 19%, never 16%, which is the case for SKR04 and taxkey 13, there will only be 3
-- taxkeys per chart after adding the two new ones
-- RAISE NOTICE 'searching for tax with taxkey % and rate %', _taxkey_ids[1], _rate;
select into _tax
*
from tax
where tax.rate = _old_rate
and tax.taxkey = _taxkey_ids[1]
order by itime desc
limit 1; -- look up tax with same taxkey but corresponding rate. As there will now be two entries for e.g. taxkey 9 with rate of 0.16, the old pre-2007 entry and the new 2020-entry. They can only be differentiated by their (automatic tax) chart_id, or during this upgrade script, via itime, use the later one
-- this also assumes taxkeys never change
-- RAISE NOTICE 'tax = %', _tax;
-- insert into taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate)
-- values ( (select id from chart where accno = 'kkkkgtkttttkk current_taxkey.chart_id, _tax.id, _tax.taxkey, current_taxkey.pos_ustva, '2020-07-01');
END IF;
-- raise notice 'inserting taxkey';
insert into taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate )
values (_chart_id,
(select id from tax where taxkey = current_taxkey.taxkey and rate = _new_rate::numeric),
current_taxkey.taxkey, -- 2, 3, 8, 9
current_taxkey.pos_ustva, '2020-07-01');
-- finally insert a copy of the taxkey on 2020-06-30 with the new startdate 2021-01-01, thereby resetting the tax rates again
insert into taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate)
values (_chart_id,
current_taxkey.tax_id,
current_taxkey.taxkey,
current_taxkey.pos_ustva, '2021-01-01');
-- RAISE NOTICE 'inserted 2 taxkeys for chart % with taxkey %', (select accno from chart where id = current_taxkey.chart_id), current_taxkey.taxkey_id;
END LOOP; --
drop table temp_taxkey_conversions;
END IF;
END $$
The error message was: ERROR: more than one row returned by a subquery used as an expression
CONTEXT: SQL statement "insert into taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate )
values (_chart_id,
(select id from tax where taxkey = current_taxkey.taxkey and rate = _new_rate::numeric),
current_taxkey.taxkey, -- 2, 3, 8, 9
current_taxkey.pos_ustva, '2020-07-01')"
PL/pgSQL function inline_code_block line 180 at SQL statement
All changes in that file have been reverted.:
`
