Scripts to populate fund code in imported order lines and invoice lines
There was an issue where the fund code was not populated in the order lines during order import, which led to the following issues:
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 {tenantId}_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.