Location changes in holdings records via batch-synchronous APIs not triggering update to item effective location

Overview:

When using the /holdings-storage/batch/synchronous API with upsert=true to update holdings permanent and/or temporary locations, the associated items' effective locations are not being updated as they should be.

Steps to Reproduce:

1. Using an API client like Postman, extract holdings records for 10 holdings records with associated items that do not have permanent or temporary locations set
2. Change the permanentLocationId or temporaryLocationId value for each of the holdings
3. POST these modified holdings records using /holdings-storage/batch/synchronous?upsert=true

Expected Results:

Holdings records are updated and associated items' effective locations are updated as appropriate.

Actual Results:

Holdings records are update, but associated items retain their original effective locations.

Cause:

The API to update a single holdings record correctly updates affected items:
https://github.com/folio-org/mod-inventory-storage/blob/master/src/main/java/org/folio/services/holding/HoldingsService.java#L150-L164

However, the API to update a batch of holdings records doesn't update affected items:
https://github.com/folio-org/mod-inventory-storage/blob/master/src/main/java/org/folio/services/holding/HoldingsService.java#L136-L148

Requestor will present these items in meeting with RMS panel:
https://wiki.folio.org/display/REL/Critical+Service+Patch+Process

Describe issue impact on business

• Wrong effective shelving order in item.
• Wrong effective call number components in item.
• Wrong effective location id in item.
• Slow performance of holdings batch API.

What institutions are affected? (field "Effected Institutions" in Jira to be populated)

All GBV institutions are affected because they use mod-inventory-update to get the holdings from the union catalog:
https://github.com/folio-org/mod-inventory-update/blob/v3.1.0/descriptors/ModuleDescriptor-template.json#L232
Bremen is one of the GBV institutions for which this issue is a show-stopper.

The holdings batch API might be used by other institutions that bulk load holdings.

As an open source product that doesn't phone home we don't know which other institutions run mod-inventory-storage or mod-inventory-update and use that API.

What is the workaround if exists?

Update the holdings records via

/holdings-storage/holdings/{holdingsRecordId}

singleton API, however, this is very slow and causes high-traffic for large data sets.

This workaround doesn't work for the 200 GBV libraries that edit instances, holdings and items in the union catalog only, the records a read only in FOLIO; they need a fast data feed from the union catalog into FOLIO.

What areas will be impacted by fix (i.e. what areas need to be retested)

Single holdings record update API and batch holdings record update API.

Brief explanation of technical implementation and the level of effort (in workdays) and technical risk (low/medium/high)

Move the Java code that updates the item update into an SQL function. This is needed to avoid a performance regression on bulk holdings import.

Several workdays.

Technical risk is medium.
1. Single holdings API and batch holdings API use the same new SQL code. That new SQL code is tested by both the existing single holdings and batch holdings unit tests and Karate integration tests. This gives us high confidence that we don't break anything.
2. The fix re-implements existing logic in SQL that already exists in Java.
3. There are no changes to the API interface. The change is purely internal to mod-inventory-storage.
4. In the folio-org GitHub organisation only mod-inventory-update uses the batch API: https://github.com/search?q=org%3Afolio-org+%2Fholdings-storage%2Fbatch%2Fsynchronous&type=code Only the batch holdings update API has the effective item values bug that this issue fixes.
While the amount of new SQL code is not small, the facts 1.-4. reduce the overall risk to medium risk.

Brief explanation of testing required and level of effort (in workdays). Provide test plan agreed with by QA Manager and PO.

Only module level unit tests and Karate integration tests are needed.

What is the roll back plan in case the fix does not work?

Use previous version of mod-inventory-storage (v26.0.0).