Post Rollover cleanup

[ahafele]

After rollover SUL discovered a number of missing encumbrance transactions from FY25.

Reason we think they didn't roll: There is both the reencumber_box at the PO level and a reencumber field in the transaction. Prior to rollover Acq ran a process to unopen each order via a script, set the reencumber to true and then reopen the order again, but the PO box didn't automatically sync with the transaction wrt reencumber and therefore these didn't meet the criteria for rolling.

Actions taken so far: For all but 4 transactions that had a status of pending (~3k), Acq was able to update the cost and then revert back in order to trigger folio to create the encumbrance transaction for FY25.

Acq tested this action (and changing the fund) for the remaining missing and it creates a fy25 transaction but it is still released and should be unreleased. They also tried to unopen/re-open or close/re-open, but it doesnt create a fy25 transactions.

For the attached data the transactions for FY25 need to be created and ideally as soon as possible because if staff update these orders the transactions get very messy. @shelleydoljack in your experience is this possible/what is the best way to handle these?

fyro_sul_errors.xlsx

[shelleydoljack]

Just comparing the counts I took for encumbrances just after rollover and before the fix_encumbrances script ran:

SUL2025 encumbrances:
okapi=# select count(*) from sul_mod_finance_storage.transaction where fiscalyearid = '9109e7fe-712f-4e76-90eb-c2862715badb' and jsonb->> 'transactionType' = 'Encumbrance';
 count 
-------
 30395

SUL2024 encumbrances:
okapi=# select count(*) from sul_mod_finance_storage.transaction where fiscalyearid = '200bfabe-07c7-4deb-b54e-99d64a3435cb' and jsonb->> 'transactionType' = 'Encumbrance';
 count 
-------
 99377

And now these counts after fix encumbrances and fixes by Irina:

SUL2025 encumbrances:
okapi=# select count(*) from sul_mod_finance_storage.transaction where fiscalyearid = '9109e7fe-712f-4e76-90eb-c2862715badb' and jsonb->> 'transactionType' = 'Encumbrance';
 count 
-------
 34252

SUL2024 encumbrances:
okapi=# select count(*) from sul_mod_finance_storage.transaction where fiscalyearid = '200bfabe-07c7-4deb-b54e-99d64a3435cb' and jsonb->> 'transactionType' = 'Encumbrance';
 count 
-------
 96350

So the discrepancy between SUL2024 encumbrances before and after is 3,027. Are these the ~3k that Irina did something with? Were the SUL2024 encumbrances updated for SUL2025? But the task on this ticket is to create new SUL2025 encumbrances.

[shelleydoljack]

For the first transaction in the spreadsheet, f60470a0-a759-49a9-8618-7b530b58a221, I noticed that the orderStatus=Pending. I tried to change it to "Open" using the finance-storage/transactions endpoint, and got a 400 http response with the message "All expected transactions already processed". 🙄 I was thinking maybe the transaction could be updated to status=Unreleased, then from the order, select the action "Update encumbrances". With the encumbrance the way it is (status Released and order status Pending), when trying to "Update encumbrances" you see this request/response:

https://okapi-prod.stanford.edu/orders/composite-orders/f9805824-de91-58d4-a544-1299c91083fd/re-encumber
{
  "errors" : [ {
    "message" : "The encumbrances were correctly created during the rollover or have already been updated.",
    "code" : "encumbrancesForReEncumberNotFound",
    "parameters" : [ ]
  } ],
  "total_records" : 1
}

which is being thrown by this code: https://github.com/folio-org/mod-orders/blob/cb75a8f5ec8cb05f3330f3d87d1b7e0bcca67057/src/main/java/org/folio/service/orders/OrderReEncumberService.java#L189-L193

I'd like to get some feedback from the folio community on how to proceed, especially since the encumbrances from the previous fy seem to be in a state that we might need to fix. I think it's just more than "create these new encumbrances". I think we need to fix the old transactions in order to be able to do a POST to orders/composite-orders/{po uuid}/re-encumber.

[shelleydoljack]

Link to slack thread: https://folio-project.slack.com/archives/C217N937A/p1724952080283809

I did some experimenting in folio-stage with the po's 611670F21 and 36733 (1st and 3rd in spreadsheet). For po 611670F21, in the UI I removed the fund distribution from the po line that links to a fy2024 encumbrance and saved it. Then I edited the po line again by adding a new fund distribution. The new fund distribution's encumbrance was in fy2025 but the action updated the paid invoice fiscalYearId to fy2025.

For po 36733, I did the following, which is a process I think we should take:

1. GET the po line
2. remove the encumbrance field and PUT back (use the orders-storage endpoint b/c orders will not remove it but still return 204), fundDistribution example for PUT:

   "fundDistribution" : [ {
     "code" : "CHINAHV-SUL",
     "fundId" : "9cee0ae4-7e9d-4476-a72c-84afc51f8b70",
     "distributionType" : "percentage",
     "value" : 100.0
   } ]

3. Then use the UI to edit the po line. Remove the fund distribution then save and close.
4. Edit the po line again and add a fund distribution and save and close.

The above will "safely" create fy2025 encumbrances, keep fiscalYearId for linked invoices in fy2024, and keep the fy2024 encumbrance around. Libsys can do parts 1 and 2 and staff can do the rest. Sound good?

[shelleydoljack]

I tried to use the /orders/composite-orders/792123f1-1249-47e5-9bc6-04b97c2e8665/re-encumber endpoint for po 42450 (after doing a PUT to remove encumbrance field for the po line fund distribution) and it returned the 409 conflict message. So we can't create the fy2025 encumbrances using that endpoint.

[trapido]

Thanks so much for all your help with troubleshooting this, Shelley! Regarding the discrepancy between the counts: when I updated the unit price in the order line, it didn’t create a new encumbrance, but updated the FY of the existing “pending” encumbrance to FY2025 and also updated its status to “unreleased”. 2,990 transactions were updated in this way. I am not sure about the remaining 37. Regarding the 162 problematic transactions, do you think it will be safer to do parts 3 and 4 in the UI or can we use orders/order-lines endpoint?

[shelleydoljack]

I don't think it's good that it updated the encumbrances to FY2025. We want new encumbrances and to leave the FY2024 encumbrances alone. Any invoices linked to those encumbrances will probably now have FY2025 in the fiscalYearId, which I don't think is a good thing.

[ahafele]

@trapido Responding to the question of doing 3 and 4 in UI vs endpoint - we think it would be best to do in the UI.

[trapido]

@shelleydoljack None of the purchase order lines with a pending encumbrance had a payment transaction linked to it (we’ve verified this in several different ways). These POLs never had any transactions linked to them in FY2024 except for unreleased encumbrance transactions, so invoices shouldn't be impacted. @ahafele sounds good

[shelleydoljack]

@trapido I'm relieved to hear that none of the po lines had invoices linked to them! I'm not certain of the impact of no longer having fy2024 encumbrances. They were likely all $0 anyways so 🤷 .

[shelleydoljack]

The po lines from the spreadsheet have been updated so now the fundDistributions no longer have an encumbrance field linking them to the fy2024 encumbrance. Acq staff can proceed with removing the fundDistributions, then re-adding them to create fy2025 encumbrances.

[shelleydoljack]

@scanedastanford and @trapido I'm just confirming that the po lines from the spreadsheet were updated so now staff can remove the fundDistributions and add them back to create fy2025 encumbrances.

[trapido]

@shelleydoljack Thanks so much, Shelley!

[trapido]

Hi @shelleydoljack, We have corrected all the POLs on the initial list of open POs, but I am afraid there are additional ones that need fixing. We discovered that when FYRO ran, it didn’t remove links to encumbrances on the PO lines for closed orders, as it should have, according to MODORDERS-712. As a result, when staff reopens a closed order, FOLIO retains the link to the old FY2024 encumbrance transaction, instead of creating a new FY2025 encumbrance. Suzette reached out directly to Dennis, but he couldn’t give a clear answer as to why this might have happened. To prevent problems when staff reopens old orders, we think we need to remove encumbrance links for all SUL’s POLs that are linked to FY2024 encumbrances (see the attached list). Once this is done, we will manually remove and re-add the fund distribution for the ones that have been reopened since rollover. pols_with_fy2024_encumbrance.csv

[trapido]

Additionally, we ran into problems with the following POLs:

1. POL 51000 (dba96c01-10d2-4ea0-b9f1-260f2d9e5cd9) has 2 FY25 encumbrances: 60719c0c-bdec-433b-86bc-1221bbdf63ca and b5e15875-a9fe-48aa-a21d-eb942ce51049. 60719c0c-bdec-433b-86bc-1221bbdf63ca is supposed to be the FY24 encumbrance but got updated by mistake so it changed it to FY25. Can the fiscal year on 60719c0c-bdec-433b-86bc-1221bbdf63ca be changed back to FY24 (200bfabe-07c7-4deb-b54e-99d64a3435cb)?
2. POL 575220F11 (100b77d2-c9c1-4adc-8e47-b8c34693aa40) created 2 FY25 encumbrances: 7116b34b-2129-55a0-bc61-53e9f6635688 and b751bc75-8748-41ac-a7e9-d284b1bad042. The POL should be linked to b751bc75-8748-41ac-a7e9-d284b1bad042 and the duplicate 7116b34b-2129-55a0-bc61-53e9f6635688 should be deleted from the database. The FY24 transactions are fine.
3. POL 751462F04 (4c6c2ac1-2fc2-4d75-a473-c4bad9211962) created 2 FY25 encumbrances: 906c4e4e-d843-5150-aec2-f0a048103c3e and ab3b7345-dcca-4f6c-911c-20e0114e8c82. The POL should be linked to ab3b7345-dcca-4f6c-911c-20e0114e8c82 and the duplicate 906c4e4e-d843-5150-aec2-f0a048103c3e should be deleted from the database. The FY24 transactions are fine.
4. POL 114225-2 (aeb24657-cd6c-4ff7-9cbd-1238f9aad04a) is linked to a non-existing encumbrance: 14bf5a62-df1d-5b75-a602-848acf440c3b. Could you please remove the encumbrance link from the POL and we will delete 114225-2 because this POL was created by mistake.

[shelleydoljack]

I will update the po lines from the csv on test and stage today and ping Suzette to make sure it's okay to do it on prod either this weekend or next. Suzette will find out if any of the po lines in the list also have FY25 encumbrances (probably not) because those would be deleted as well when running the rake task to remove the encumbrances. I'm not sure about the po lines with multiple FY25 encumbrances; it probably just needs some manual updating.

[scanedastanford]

@shelleydoljack Irina created a query to find if any of the POLs have both an fy24 and fy25 encumbrance link. There were none. So I think it’s safe to do in prod after we do our testing in test or stage.

[shelleydoljack]

@scanedastanford I started running the task to remove encumbrances from the po line on folio-test at 11am and it is still running. It has processed about 2500 po lines so far.

[shelleydoljack]

@scanedastanford the rake task failed after processing 5,960 lines of the list of 65,780 po lines because po line 110661-1 doesn't exist on folio-test. I'll add to the rake task to skip when the po line doesn't exist. Do you want to go head and test some of the po lines that did update?

[shelleydoljack]

I fixed the rake task to skip if the po line doesn't exist and not error in case the po line doesn't have a fund distribution. I started running it against folio-stage at 11:10am PDT on 9/16/2024.

[scanedastanford]

@shelleydoljack it looks like I will have to wait until it finishes running because I'm not able to log in to folio-test

[shelleydoljack]

@scanedastanford I think folio-test is available again. Also, about 8,500 po lines have been processed on folio-stage so far.

[scanedastanford]

@shelleydoljack @itrapido I re-opened a few orders in folio-stage (e.g. 78467, 20136, 653718F12, and 291095F08). It worked as expected. A new encumbrance id for FY25 was created and linked to the POL. The FY24 encumbrance remained untouched. I think we can proceed in prod. Thank you Shelley!

[shelleydoljack]

Removing FY24 encumbrances on stage finished at Sep 17 04:18:22 AM PDT 2024, so total time took about 17 hours to run.

[shelleydoljack]

Started rake task to remove encumbrances from prod on 9/20/24 at 7pm PDT.

[shelleydoljack]

Rake task finished Sat Sep 21 07:16:48 PM PDT 2024. It took longer in prod.

[shelleydoljack]

Additionally, we ran into problems with the following POLs:

1. POL 51000 (dba96c01-10d2-4ea0-b9f1-260f2d9e5cd9) has 2 FY25 encumbrances: 60719c0c-bdec-433b-86bc-1221bbdf63ca and b5e15875-a9fe-48aa-a21d-eb942ce51049. 60719c0c-bdec-433b-86bc-1221bbdf63ca is supposed to be the FY24 encumbrance but got updated by mistake so it changed it to FY25. Can the fiscal year on 60719c0c-bdec-433b-86bc-1221bbdf63ca be changed back to FY24 (200bfabe-07c7-4deb-b54e-99d64a3435cb)?
2. POL 575220F11 (100b77d2-c9c1-4adc-8e47-b8c34693aa40) created 2 FY25 encumbrances: 7116b34b-2129-55a0-bc61-53e9f6635688 and b751bc75-8748-41ac-a7e9-d284b1bad042. The POL should be linked to b751bc75-8748-41ac-a7e9-d284b1bad042 and the duplicate 7116b34b-2129-55a0-bc61-53e9f6635688 should be deleted from the database. The FY24 transactions are fine.
3. POL 751462F04 (4c6c2ac1-2fc2-4d75-a473-c4bad9211962) created 2 FY25 encumbrances: 906c4e4e-d843-5150-aec2-f0a048103c3e and ab3b7345-dcca-4f6c-911c-20e0114e8c82. The POL should be linked to ab3b7345-dcca-4f6c-911c-20e0114e8c82 and the duplicate 906c4e4e-d843-5150-aec2-f0a048103c3e should be deleted from the database. The FY24 transactions are fine.
4. POL 114225-2 (aeb24657-cd6c-4ff7-9cbd-1238f9aad04a) is linked to a non-existing encumbrance: 14bf5a62-df1d-5b75-a602-848acf440c3b. Could you please remove the encumbrance link from the POL and we will delete 114225-2 because this POL was created by mistake.

@trapido

1. Cannot PUT to finance-storage/transactions. "All expected transactions already processed".
2. a."Replace the encumbrance link in POL 575220F11-1 (100b77d2-c9c1-4adc-8e47-b8c34693aa40) for WEIDLER-SUL (transaction uuid: 7116b34b-2129-55a0-bc61-53e9f6635688) with transaction b751bc75-8748-41ac-a7e9-d284b1bad042." Done. b. "Delete encumbrance transaction 7116b34b-2129-55a0-bc61-53e9f6635688" Done.
3. a. "Replace the encumbrance link in POL 575220F11-1 (4c6c2ac1-2fc2-4d75-a473-c4bad9211962) for BRUSLIT-SUL (transaction uuid: 906c4e4e-d843-5150-aec2-f0a048103c3e) with transaction ab3b7345-dcca-4f6c-911c-20e0114e8c82." Done. b. "Delete encumbrance transaction 906c4e4e-d843-5150-aec2-f0a048103c3e." Done.
4. "Remove encumbrance link from POL 114225-2 (aeb24657-cd6c-4ff7-9cbd-1238f9aad04a)." Done.

[trapido]

Thank you very much, @shelleydoljack! Regarding 1, would it be possible to update the fiscal year for the encumbrance transaction 60719c0c-bdec-433b-86bc-1221bbdf63ca to SUL2024 directly in the database and then delete this encumbrance link from POL dba96c01-10d2-4ea0-b9f1-260f2d9e5cd9? I will then create a new encumbrance.

[jgreben]

This will involve updating transaction 60719c0c-bdec-433b-86bc-1221bbdf63ca fiscalYearId to 200bfabe-07c7-4deb-b54e-99d64a3435cb.

[jgreben]

I ran: update sul_mod_finance_storage.transaction set jsonb = JSONB_SET(jsonb, '{fiscalYearId}', '"200bfabe-07c7-4deb-b54e-99d64a3435cb"') where id = '60719c0c-bdec-433b-86bc-1221bbdf63ca';

Now the transaction looks like this:

  id                  |                                                                                                                                                                                                                                                                                                                                                                                                                         jsonb                                                                                                                                                                                                                                                                                                                                                                                                                         |      creation_date      |              created_by              |             fiscalyearid             |              fromfundid              | sourcefiscalyearid | tofundid | expenseclassid 
--------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------+----------+----------------
 60719c0c-bdec-433b-86bc-1221bbdf63ca | {"id": "60719c0c-bdec-433b-86bc-1221bbdf63ca", "amount": 0.0, "source": "PoLine", "_version": 7, "currency": "USD", "metadata": {"createdDate": "2024-02-07T15:54:47.238Z", "updatedDate": "2024-08-29T01:47:29.487Z", "createdByUserId": "7ced6673-ff0d-4619-997d-34c41d17baec", "updatedByUserId": "b296db77-f6c8-44fb-b16d-2281a9f37b01"}, "fromFundId": "ed85fb26-4754-4dcc-831b-422ee8aa04d9", "encumbrance": {"status": "Released", "orderType": "Ongoing", "reEncumber": true, "orderStatus": "Open", "subscription": true, "amountExpended": 1106.62, "sourcePoLineId": "dba96c01-10d2-4ea0-b9f1-260f2d9e5cd9", "amountAwaitingPayment": 0.0, "sourcePurchaseOrderId": "be8a4b6d-7914-4d0e-b000-de15ea2b3b40", "initialAmountEncumbered": 1113.85}, "fiscalYearId": "200bfabe-07c7-4deb-b54e-99d64a3435cb", "transactionType": "Encumbrance"} | 2024-02-07 15:54:47.238 | 7ced6673-ff0d-4619-997d-34c41d17baec | 200bfabe-07c7-4deb-b54e-99d64a3435cb | ed85fb26-4754-4dcc-831b-422ee8aa04d9 |                    |          | 

[jgreben]

@trapido please take a look on your end and see if that transaction looks ok now. I deleted the po line using @shelleydoljack's rake task.

[trapido]

Transaction 60719c0c-bdec-433b-86bc-1221bbdf63ca looks correct now, and I was able to create a new SUL2025 encumbrance for POL51000-1. I believe we are now done with all post-rollover cleanup. Thank you so much for all your help, @shelleydoljack and @jgreben !