How to fix issues with finance data

Owned by Andrei Makaranka
Last updated: Mar 18, 2024 by DamienVersion comment

Introduction

This is a list of issues that can happen in a FOLIO environment, and how to work around them. Often they have already been fixed in a later version of FOLIO, but the workarounds can be useful to resolve issues in a production environment before upgrading FOLIO. Whenever possible we should link to matching JIRA issues so that people can check the affected versions.

Encumbrance issues

Unreleased Encumbrance for Open Order if Pending Payment and Payment transactions were removed before

In Quesnelia there is a new UI feature to unrelease an encumbrance. If changed in the database, there is no need to change transaction summaries.

Prerequisites

Pending Payment and Payment transactions were removed before

1. Get order transaction summary for order where we need to UnRelease encumbrance:


GET {{protocol}}://{{url}}:{{okapiport}}/finance-storage/order-transaction-summaries/{{orderId}}

Response sample:

{
    "id": "f2715f25-8504-4698-afd0-3025aa779ac6",
    "numTransactions": -1
}

Note : "orderId" possible to get from encumbrance "sourcePurchaseOrderId"

2. Update order transaction summary for order


PUT {{protocol}}://{{url}}:{{okapiport}}/finance-storage/order-transaction-summaries/{{orderId}}
Request sample:
{
    "id": "{{orderId}}",
    "numTransactions": 1
}
Note : Change only "numTransactions" : "Total number of encumbrances(transactions) expected to update for this order(for your case should be 1)

3. UnRelease encumbrance 

3.1 Set "status" to "Unreleased"

3.2 Set in the "amount" the same value as in "initialAmountEncumbered"

3.3 Set "amountExpended" and "amountAwaitingPayment" to ZERO


PUT {{protocol}}://{{url}}:{{okapiport}}/finance/release-encumbrance/{encumbranceId}

Encumbrance 
{
    "id": "fd65e1cf-6dd1-46e6-b491-034eb8acde7e",
    "amount": 54.65,
    "source": "PoLine",
    "currency": "USD"
    "fromFundId": "b8ee517d-e585-43ef-bb34-5e0fc999b8ca",
    "encumbrance": {
        "status": "Unreleased",
        "orderType": "One-Time",
        "reEncumber": true,
        "orderStatus": "Open",
        "subscription": false,
        "amountExpended": 0,
        "sourcePoLineId": "cad9474d-93d0-44f6-bd19-0bcae6a56137",
        "amountAwaitingPayment": 0,
        "sourcePurchaseOrderId": "021d67bb-b01d-42f4-8073-fd475a6c03de",
        "initialAmountEncumbered": 54.65
    },
    "fiscalYearId": "f28b758c-2bd8-480e-a5a3-eede648fdc34",
    "expenseClassId": "ba70820f-d8c9-4567-a8a8-1e3059d1e7ea",
    "transactionType": "Encumbrance"
}

4. Update Budget's amounts

Check that the encumbrance "amount" is not already included in the budget's "encumbered" amount.

If not then update budget's "encumbered" and "awaitingPayment" amounts :

  1. New "encumbered" = Prev. budget's "encumbered" + encumbrance "amount"
  2. New "awaitingPayment" = Prev. budget's "awaitingPayment" - encumbrance "amount"

PUT {{protocol}}://{{url}}:{{okapiport}}/finance/budgets/{{budgetId}}

Budget 
{
  "id" : "{{budgetId}}",
  "_version" : 13,
  "name" : "ASIAHIST-FY2022",
  "budgetStatus" : "Active",
  "allowableEncumbrance" : 110.0,
  "allowableExpenditure" : 100.0,
  "allocated" : 23000.0,
  "awaitingPayment" : 0.0,
  "available" : 22850.0,
  "encumbered" : 54.65,
  "expenditures" : 0.0,
  "netTransfers" : 0.0,
  "unavailable" : 150.0,
  "overEncumbrance" : 0.0,
  "overExpended" : 0.0,
  "fundId" : "55f48dc6-efa7-4cfe-bc7c-4786efe493e3",
  "fiscalYearId" : "a06a95ef-5a96-4839-a2d9-ac484a25c261",
  "acqUnitIds" : [ ]
  "initialAllocation" : 23000.0,
  "allocationTo" : 0.0,
  "allocationFrom" : 0.0,
  "totalFunding" : 23000.0,
  "cashBalance" : 23000.0,
  "statusExpenseClasses" : [ ]
}


Unreleased Encumbrance for Closed Order if Pending Payment, Payment transactions and Invoice were removed before

In Quesnelia there is a new UI feature to unrelease an encumbrance. If changed in the database, there is no need to change transaction summaries.

Prerequisites

Pending Payment and Payment transactions were removed before

1 Save budgets information for further check and update

{{protocol}}://{{url}}:{{okapiport}}/finance/budgets?query=fundId=={{fundId}} 

Note : {{fundId}} please take from fund distributions

2. Reopen order

GET : {{protocol}}://{{url}}:{{okapiport}}/orders-storage/purchase-orders/{{orderId}}

Response : {{orderBody}}


PUT : {{protocol}}://{{url}}:{{okapiport}}/orders/composite-orders/{{orderId}}

Body : {{orderBody}} and set "workflowStatus" to "Open"

In scope of reopening status of the encumbrance will be set to "Unrelease", but amount of encumbrance and budget  are not always updated.

3. Check that encumbrance amount and budget amounts are updated if not go to item 4, 5, 6, 7 :

Encumbrance

  • "status" to "Unreleased"
  • "amount" the same value as in "initialAmountEncumbered"
  • "amountExpended" and "amountAwaitingPayment" to ZERO

Budget

  • New "encumbered" = Prev. budget's "encumbered" + encumbrance "amount"
  • New "awaitingPayment" = Prev. budget's "awaitingPayment" - encumbrance "amount"

4. Get order transaction summary for order where we need to UnRelease encumbrance:


GET {{protocol}}://{{url}}:{{okapiport}}/finance-storage/order-transaction-summaries/{{orderId}}

Response sample:

{
    "id": "f2715f25-8504-4698-afd0-3025aa779ac6",
    "numTransactions": -1
}

Note : "orderId" possible to get from encumbrance "sourcePurchaseOrderId"

5. Update order transaction summary for order


PUT {{protocol}}://{{url}}:{{okapiport}}/finance-storage/order-transaction-summaries/{{orderId}}
Request sample:
{
    "id": "{{orderId}}",
    "numTransactions": 1
}
Note : Change only "numTransactions" : "Total number of encumbrances(transactions) expected to update for this order(for your case should be 1)

6. UnRelease encumbrance 

3.1 Set "status" to "Unreleased"

3.2 Set in the "amount" the same value as in "initialAmountEncumbered"

3.3 Set "amountExpended" and "amountAwaitingPayment" to ZERO


PUT {{protocol}}://{{url}}:{{okapiport}}/finance/release-encumbrance/{encumbranceId}

Encumbrance 
{
    "id": "fd65e1cf-6dd1-46e6-b491-034eb8acde7e",
    "amount": 54.65,
    "source": "PoLine",
    "currency": "USD"
    "fromFundId": "b8ee517d-e585-43ef-bb34-5e0fc999b8ca",
    "encumbrance": {
        "status": "Unreleased",
        "orderType": "One-Time",
        "reEncumber": true,
        "orderStatus": "Open",
        "subscription": false,
        "amountExpended": 0,
        "sourcePoLineId": "cad9474d-93d0-44f6-bd19-0bcae6a56137",
        "amountAwaitingPayment": 0,
        "sourcePurchaseOrderId": "021d67bb-b01d-42f4-8073-fd475a6c03de",
        "initialAmountEncumbered": 54.65
    },
    "fiscalYearId": "f28b758c-2bd8-480e-a5a3-eede648fdc34",
    "expenseClassId": "ba70820f-d8c9-4567-a8a8-1e3059d1e7ea",
    "transactionType": "Encumbrance"
}

7. Update Budget's amounts

Check that the encumbrance "amount" is not already included in the budget's "encumbered" amount.

If not then update budget's "encumbered" and "awaitingPayment" amounts :

  1. New "encumbered" = Prev. budget's "encumbered" + encumbrance "amount"
  2. New "awaitingPayment" = Prev. budget's "awaitingPayment" - encumbrance "amount"

PUT {{protocol}}://{{url}}:{{okapiport}}/finance/budgets/{{budgetId}}

Budget 
{
  "id" : "{{budgetId}}",
  "_version" : 13,
  "name" : "ASIAHIST-FY2022",
  "budgetStatus" : "Active",
  "allowableEncumbrance" : 110.0,
  "allowableExpenditure" : 100.0,
  "allocated" : 23000.0,
  "awaitingPayment" : 0.0,
  "available" : 22850.0,
  "encumbered" : 54.65,
  "expenditures" : 0.0,
  "netTransfers" : 0.0,
  "unavailable" : 150.0,
  "overEncumbrance" : 0.0,
  "overExpended" : 0.0,
  "fundId" : "55f48dc6-efa7-4cfe-bc7c-4786efe493e3",
  "fiscalYearId" : "a06a95ef-5a96-4839-a2d9-ac484a25c261",
  "acqUnitIds" : [ ]
  "initialAllocation" : 23000.0,
  "allocationTo" : 0.0,
  "allocationFrom" : 0.0,
  "totalFunding" : 23000.0,
  "cashBalance" : 23000.0,
  "statusExpenseClasses" : [ ]
}


Issue "All expected transaction already processed"

Overview

This issue cannot happen in Quesnelia and beyond.

This issue can be caused by different bugs, not all of which have been fixed in Lotus. An example is MODORDERS-432. There is also MODFISTO-260.

Here is a request from someone looking for a workaround:

[...] I am working on an issue where an invoice cannot be paid. The encumbrance ID that is attached to one of the invoice's pending payment transactions does not exist....and there is an encumbrance with the same information that does exist. When I try to replace the awaitingPayment.encumbranceId on the pending payment (with a PUT api call) I get this error: All expected transactions already processed. The invoice has 15 items and the invoice transaction summary was -15/15 so I changed it to -14/14 thinking that would let me PUT the update and it still fails (All expected transactions already processed). [...] Do you have any suggestions? [...] This was the initial error in the log:


2022-03-01 02:40:33.758,"2022-03-01T02:40:33,758 ERROR [vert.x-eventloop-thread-1] PaymentCreditWorkflowService Failed to create transaction for invoice with id - a3d970c0-a2b0-4a46-b108-08956f61da44"
2022-03-01 02:40:33.758,org.folio.invoices.rest.exceptions.HttpException: {
2022-03-01 02:40:33.758," ""errors"" : [ {"
2022-03-01 02:40:33.758," ""message"" : ""insert or update on table \""temporary_invoice_transactions\"" violates foreign key constraint \""paymentencumbranceid_transaction_fkey\"": Key (paymentencumbranceid)=(5e3fe89b-456f-43af-bc07-f085d11dd52e) is not present in table \""transaction\""."","
2022-03-01 02:40:33.758," ""code"" : ""genericError"","
2022-03-01 02:40:33.758," ""parameters"" : [ ]"
2022-03-01 02:40:33.758," } ],"
2022-03-01 02:40:33.758," ""total_records"" : 1"
2022-03-01 02:40:33.758,}

Steps to fix

1. Get invoice transaction summary for invoice where need to update encumbrance reference:
GET {{protocol}}://{{url}}:{{okapiport}}/finance-storage/invoice-transaction-summaries/{{invoiceId}}

Response sample:
{
"id": "ae27a94f-6313-48c6-b887-2b703bafc25f",
"numPendingPayments": 1,
"numPaymentsCredits": 1
}

2. Get pending payments for invoice
GET all pending payments -> {{protocol}}://{{url}}:{{okapiport}}/finance-storage/transactions?query=sourceInvoiceLineId=={{invoiceLineId}}
Or
GET exact one pending payment where need to update encumbrance reference -> {{protocol}}://{{url}}:{{okapiport}}/finance-storage/transactions/{{pendingPaymentId}}

3. Update invoice transaction summary for invoice

PUT {{protocol}}://{{url}}:{{okapiport}}/finance-storage/invoice-transaction-summaries/{{invoiceId}}
Request sample:
{
"id": "{{invoiceId}}",
"numPendingPayments": 1,
"numPaymentsCredits": 1
}
Note : Change only "numPendingPayments" : "Total number of pending payments(transactions) expected to update for this invoice(for your case should be 1)
Don't update "numPaymentsCredits", because this is total number of payment transaction which will be created in invoice Pay time.
For your case I suppose body should be
{
"id": "{{invoiceId}}",
"numPendingPayments": 1,
"numPaymentsCredits": 15
}
4. Update pending payment with right encumbrance reference

PUT {{protocol}}://{{url}}:{{okapiport}}/finance-storage/transactions/{{pendingPaymentId}}
Request sample:
{
"id": "{{pendingPaymentId}}",
"amount": 4.6,
"awaitingPayment": {
"encumbranceId": "{{rightEncumbranceReference}}",
"releaseEncumbrance": true
},
"currency": "USD",
"fiscalYearId": "a06a95ef-5a96-4839-a2d9-ac484a25c261",
"fromFundId": "65032151-39a5-4cef-8810-5350eb316300",
"source": "Invoice",
"sourceInvoiceId": "ae27a94f-6313-48c6-b887-2b703bafc25f",
"sourceInvoiceLineId": "3e19f3c1-7a9f-4939-8083-06bf3afb4cae",
"transactionType": "Pending payment"
}
Success response : 204

Can not pay invoice that references the same POL and 2 different invoice lines

Overview 

To resolve issues with duplicate encumbrances, it is best to use the encumbrance script. Fixing transaction summaries is no longer needed with Quesnelia.

The duplicate key is an encumbrance ID and it is attached to 11 invoice lines Kiwi. In the past...in a similar situation (with Juniper)


2022-03-21 14:44:20.542,"java.lang.IllegalStateException: 
Duplicate key 0601baf1-530d-47e0-b000-bed00fb457da 
(attempted merging values 
org.folio.rest.acq.model.finance.Transaction@2da30222
[id=,id=0601baf1-530d-47e0-b000-bea,amount=0.0,
awaitingPayment=,currency=GBP,description=

Steps to fix

Juniper:

Remove the duplicate encumbrance IDs from the invoice lines.

Kiwi:

1. Get order transaction summary for order where we need to release encumbrance:
GET {{protocol}}://{{url}}:{{okapiport}}/finance-storage/order-transaction-summaries/{{orderId}}

Response sample:

{
  "id": "f2715f25-8504-4698-afd0-3025aa779ac6",
  "numTransactions": -1
}

3. Update order transaction summary for order

PUT {{protocol}}://{{url}}:{{okapiport}}/finance-storage/order-transaction-summaries/{{orderId}}
Request sample:
{
"id": "{{orderId}}",
 "numTransactions": 1
}
Note : Change only "numTransactions" : "Total number of encumbrances(transactions) expected to update for this order(for your case should be 1)

2. Release encumbrance for which duplication error occurs
POST {{protocol}}://{{url}}:{{okapiport}}/finance/release-encumbrance/{encumbranceId}

Request body is Empty

3. Remove "encumbranceId" from fund distributions

4. Try to Approve an Pay invoice


Pending payment issues

A deleted invoice with pending payments impacted the budget

This should no longer happen, but either way fixing transaction summaries is no longer needed with Quesnelia.

A customer deleted an approved invoice.  The pending payments still existed and were impacting the budget.  The workaround we used was to update all of the transaction amounts to zero which updated the budget also. 

Step 1 (update transaction summary so pending payment updates are accepted)

GET https://<okapi-url>/finance-storage/invoice-transaction-summaries/<invoice-uuid>

returns
{
"id": "ab2c4fce-45bb-4f67-8d2c-ec8ff44480f1",
"numPendingPayments": -74,
"numPaymentsCredits": 74
}

change -74 to 74 and

PUT https://<okapi-url>/finance-storage/invoice-transaction-summaries/<invoice-uuid>

Step 2 (update each pending payment amount to zero)

GET https://<okapi-url>/finance/transactions?query=(sourceInvoiceId=<invoice-uuid>)&limit=100

For each of the 74 pending payments returned in the above API call (I've written a script for this):
GET https://<okapi-url>/finance-storage/transactions/TRANSACTION-ID
change amount to 0
PUT https://<okapi-url>/finance-storage/transactions/TRANSACTION-ID

Rollover issues

Appraoches how to fix rollover issues are described in this document: FYRO Error Troubleshooting Knowledge Sharing