Scripts to populate fund code in imported order lines and invoice lines

https://folio-org.atlassian.net/browse/MODDATAIMP-1057

There was an issue where the fund code was not populated in the order lines during order import, which led to the following issues:

https://folio-org.atlassian.net/browse/MODDATAIMP-1045
https://folio-org.atlassian.net/browse/MODORDERS-1102
https://folio-org.atlassian.net/browse/MODINVOSTO-173

These scripts are aimed to populate the fund code field in existing order lines and invoice lines on the environments based on their assigned funds.

Script to populate fund code in existing order lines created by data import:

UPDATE {tenantId}_mod_orders_storage.po_line
SET jsonb = jsonb_set(jsonb, '{fundDistribution}', prepared_funds.updated_funds)
FROM (
    SELECT po_line.id, jsonb_agg(jsonb_set(fd, '{code}', to_jsonb(f.jsonb ->> 'code'))) AS updated_funds
    FROM {tenantId}_mod_orders_storage.po_line
    CROSS JOIN jsonb_array_elements(po_line.jsonb -> 'fundDistribution') AS fd
    JOIN diku_mod_finance_storage.fund f ON (fd ->> 'fundId')::uuid = f.id
    WHERE po_line.jsonb ->> 'source' = 'MARC'
    GROUP BY po_line.id
) AS prepared_funds
WHERE po_line.id = prepared_funds.id;

Script to populate fund code in existing invoice lines:

UPDATE {tenantId}_mod_invoice_storage.invoice_lines
SET jsonb = jsonb_set(jsonb, '{fundDistributions}', prepared_funds.updated_funds)
FROM (
    SELECT invoice_lines.id, jsonb_agg(jsonb_set(fd, '{code}', to_jsonb(f.jsonb ->> 'code'))) AS updated_funds
    FROM {tenantId}_mod_invoice_storage.invoice_lines
    CROSS JOIN jsonb_array_elements(invoice_lines.jsonb -> 'fundDistributions') AS fd
    JOIN {tenantId}_mod_finance_storage.fund f ON (fd ->> 'fundId')::uuid = f.id
    WHERE NOT (fd ? 'code') AND fd ? 'fundId'
    GROUP BY invoice_lines.id
) AS prepared_funds
WHERE invoice_lines.id = prepared_funds.id;

Where {tenantId} - the owner (tenant) id for which the scripts should be run.

Folio Development Teams

Scripts to populate fund code in imported order lines and invoice lines

Related content