Poppy: Fiscal year is not populated on invoices created prior to Poppy
Description
CSP Request Details
CSP Rejection Details
Potential Workaround
Attachments
- 01 Mar 2024, 01:05 PM
- 01 Mar 2024, 01:05 PM
- 01 Mar 2024, 01:05 PM
- 01 Mar 2024, 01:05 PM
Checklist
hideActivity
Azizbek Khushvakov March 12, 2024 at 2:04 PM
More faster SQL script than previous one [Execution time for 25 000 invoices ~ 10 sec]
WITH updated_invoices AS (
SELECT inv.id AS invoice_id,
to_jsonb(trx.jsonb->>'fiscalYearId') AS fiscal_year_id
FROM <tenant>_mod_invoice_storage.invoices AS inv
JOIN <tenant>_mod_finance_storage.transaction AS trx
ON trx.jsonb->>'sourceInvoiceId' = inv.id::text
WHERE inv.jsonb->>'fiscalYearId' IS NULL
)
UPDATE <tenant>_mod_invoice_storage.invoices AS inv
SET jsonb = jsonb_set(inv.jsonb, '{fiscalYearId}', ui.fiscal_year_id)
FROM updated_invoices AS ui
WHERE inv.id = ui.invoice_id
AND ui.fiscal_year_id IS NOT NULL;Serhii_Nosko March 4, 2024 at 1:38 PM
Tested SQL script executing on Rancher env https://folio-dev-thunderjet-diku.ci.folio.org/., going to close this ticket.
This SQL script can be used to populate fiscalYearId on demand if the library wants to have fiscalYearId as quickly as possible at any moment.
We also planned to investigate the possibility to include migration logic in the official Quesnelia release that should be placed during the mod-invoice-storage module installing process. https://folio-org.atlassian.net/browse/MODINVOSTO-177 In the scope of this story we will investigate whether it is a synchronous or asynchronous process depending on the amount of invoices without fiscalYearId populated from the largest libraries. This story is planned for the next sprint.
After investigations made in that story we will decide whether we need to include running of SQL script in official release notes for Quesnelia or our automated process of installing mod-invoice-storage can take care of it.
Azizbek Khushvakov March 1, 2024 at 1:31 PM
WITH updated_invoices AS (
SELECT
inv.id AS invoice_id,
(
SELECT to_jsonb(trx.jsonb->>'fiscalYearId')
FROM <tenant>_mod_finance_storage.transaction AS trx
WHERE trx.jsonb->>'sourceInvoiceId' = inv.id::text
LIMIT 1
) AS fiscal_year_id
FROM <tenant>_mod_invoice_storage.invoices AS inv
WHERE inv.jsonb->>'fiscalYearId' IS NULL
)
UPDATE <tenant>_mod_invoice_storage.invoices AS inv
SET jsonb = jsonb_set(inv.jsonb, '{fiscalYearId}', ui.fiscal_year_id)
FROM updated_invoices AS ui
WHERE inv.id = ui.invoice_id
AND ui.fiscal_year_id IS NOT NULL;more general sql script for any tenant.
<tenant> name should be changed to tenant name
e.g - <tenant> → diku → diku_mod_invoice_storage.invoices
Azizbek Khushvakov March 1, 2024 at 1:05 PM
There is two solution: 1) SQL Script, 2) Cross Module approach
SQL Script
WITH updated_invoices AS (
SELECT
inv.id AS invoice_id,
(
SELECT to_jsonb(trx.jsonb->>'fiscalYearId')
FROM diku_mod_finance_storage.transaction AS trx
WHERE trx.jsonb->>'sourceInvoiceId' = inv.id::text
LIMIT 1
) AS fiscal_year_id
FROM diku_mod_invoice_storage.invoices AS inv
WHERE inv.jsonb->>'fiscalYearId' IS NULL
)
UPDATE diku_mod_invoice_storage.invoices AS inv
SET jsonb = jsonb_set(inv.jsonb, '{fiscalYearId}', ui.fiscal_year_id)
FROM updated_invoices AS ui
WHERE inv.id = ui.invoice_id
AND ui.fiscal_year_id IS NOT NULL;Result:
- There is invoices without fiscalYearId
- There are should be filled from transaction filed
Scrip was executed and affect two rows
The result:
Dennis Bridges February 14, 2024 at 3:08 PM
As per discussion in refinement the team will create a SQL script and make it available to the community to resolve this issue. Migration script was not possible at time of implementation. Notes will be added to the Poppy and Quesnelia releases to ensure that hosting teams are aware that this tool is available.
Details
Details
Assignee
Reporter
Priority
P2
See https://folio-org.atlassian.net/wiki/pages/viewpage.action?pageId=5210256 and assign the bug to the appropriate development team and PO.
Overview: The Poppy release introduced functionality to pay invoices against previous fiscal year budgets (see https://folio-org.atlassian.net/browse/UXPROD-3256 )
This created a new fiscal year field on the invoice record, as well as a filter in the invoices app, and a column in the related invoices area on orders/POL for fiscal year. However, this field is not filled in for invoices created prior to the upgrade to Poppy, rendering no results when filtering on past fiscal years and showing a blank display for fiscal year on those records.
Steps to Reproduce:
Log into Poppy Bugfest as a user with admin permissions.
Open the Invoices app and view this record: https://bugfest-poppy.int.aws.folio.org/invoice/view/d9697abe-09e8-4e72-85b1-c3a96a1787c0?limit=50&offset=50&status=Paid
Expected Results: Fiscal year value displays.
Actual Results: Fiscal year field is blank because the invoice was paid prior to the Bugfest upgrade to Poppy. “Looks like there is no fiscal year associated with the invoice”
Additional Information: This bug is impacting the majority of invoices in the FOLIO tenant we use for training at EBSCO. The fiscal year is only showing on newly created invoices.