FYRO Error Troubleshooting Knowledge Sharing
#1 Rollover poLines by chunks failed (Encumbrance should be released before deletion in mod-orders logs)
Error description
Error is connected to order rollover process and incorrect status of encumbrances for closed orders - they should be released or even do not exist in new FY
Investigation
From logs it's clear that transactions for closed orders should be removed during order rollover, but as they have unreleased status they can not be removed. The reason why closed orders have unreleased encumbrances in new FY - incorrect encumbrance.orderStatus, they have Open instead of Closed.
Steps taken to fix the issue
Run fix_encumbrances script (options: 4, 5, 6, 7, 9. #8 should be apply for rolled FY if you did a rollover), after that rollover shouldn't fail with "Encumbrance should be released before deletion" error
IMPORTANT
- If you are on testing env and can restore state before the rollover, fix_encumbrances should be run for current FY before the rollover
- If you did a rollover and it failed, fix_encumbrances should be run for rolled FY (next one), after that order rollover process should be started manually via API (POST /orders/rollover)
#2 duplicate key value violates unique constraint "transaction_pkey"
Error description
duplicate key value violates unique constraint "transaction_pkey"
This error is thrown on DB level when FYRO script tries to create transaction with already existing UUID.
Investigation
During investigation we found that old order lines (previous issues with incorrect links between order line and encumbrances) migrated encumbrances from previous FY to current FY as a result in one FY there were base encumbrance (previous FY) and rolled over encumbrance (current FY). As we use encumbrance UUID to create new encumbrance UUID having base in the same FY produced an error to create already existing UUID.
Results
After fixing encumbrances (moving them back to previous FY or removing them) allowed to run FY without errors
Steps taken to fix the issue
Next SQLs helps to identify duplicated encumbrances that need to be fixed: moved back to previous FY or removed
SELECT jsonb_extract_path_text(jsonb, 'encumbrance', 'sourcePoLineId') as sourcePoLineId
FROM {tenant}_mod_finance_storage.transaction WHERE fiscalyearid = {fiscalyearid} AND id in ( SELECT public.uuid_generate_v5(public.uuid_nil(), concat('BER1', id)) as id FROM {tenant}_mod_finance_storage.transaction where fiscalyearid = {fiscalyearid} );
------------------------------------------------------------------------------------------------
SELECT
tr.fromfundid,
jsonb_extract_path_text(tr.jsonb, 'encumbrance', 'sourcePoLineId') as sourcePoLineId,
jsonb_extract_path_text(tr.jsonb, 'fiscalYearId') as trFiscalYearId,
tr.expenseclassid as expenseClass,
count(*)
FROM {tenant}_mod_finance_storage.transaction as tr
LEFT JOIN {tenant}_mod_finance_storage.fund as fund
ON fund.id = tr.fromfundid
WHERE
tr.jsonb->'encumbrance'->>'sourcePoLineId' IS NOT NULL
AND tr.jsonb->'encumbrance'->>'orderStatus' <> 'Closed'
AND tr.fiscalyearid = {fiscalyearid}
GROUP BY tr.fromfundid, trFiscalYearId, sourcePoLineId, expenseClass
HAVING count(*) > 1;
> fiscalyearid is id (UUID) of FY library rollovers from
> tenant is tenant id
After duplicate encumbrances are fixed, fix_encumbrances should be executed to update links, recalculate budgets etc
- run SQL to find duplicates
- SQL returns sourcePoLineId so you need to review transactions connected to this order line and its fund distribution (funds, expense classes, etc) to decide remove or move back to previous year:
To decide what action apply (remove or move back) additional information is required:
0. Find order line id from transaction (run when order line is unknown)select jsonb->'encumbrance'->>'sourcePoLineId' as sourcePoLineId from {tenant}_mod_finance_storage.transaction where id in ({transactionIds});
> transactionIds - list of transaction UUIDs to analize
> tenant is tenant id
1. Find transactions for order line in FY rollover fromSELECT id, jsonb, creation_date, created_by, fiscalyearid, fromfundid, sourcefiscalyearid, tofundid, expenseclassid FROM {tenant}_mod_finance_storage.transaction WHERE jsonb->'encumbrance'->>'sourcePoLineId' in ({...sourcePoLineIds}) AND fiscalyearid = '{fiscalyearid}';
> sourcePoLineIds - list of order line UUIDs to analize
> fiscalyearid is id (UUID) of FY library rollovers from
> tenant is tenant id
2. Find POL fund distributionsSELECT id, jsonb->>'fundDistribution' as fundDistribution FROM {tenant}_mod_orders_storage.po_line WHERE id in ({...sourcePoLineIds});
> tenant is tenant id
> sourcePoLineIds - list of order line UUIDs to analize- move back - I rely only on this query. If this query returns already existing UUID, it means that this transaction is from past
> SELECT public.uuid_generate_v5(public.uuid_nil(), concat('BER1', {transactionId}))
IMPORTANT: only 2.a is enough to fix "duplicate key constraint error", 2.b is used to have right values and get rid of data inconsistency - when you see that there are still duplicates, you can decide what to remove based on order line fund distribution
- move back - I rely only on this query. If this query returns already existing UUID, it means that this transaction is from past
- run fix_encumbrances script in Dry run mode to find duplicates (2 and 3 steps report them)
- repeat #2 for found duplicated lines
- run script in normal mode