Copy of Script for Identifying Item Records that Have Invalid itemLevelCallNumberTypeId Types

Before MODINVSTOR-987 was released, It was possible to set itemLevelCallNumberTypeId values for items that are invalid UUIDs or do not refer to a known call number type.

This script helps identify the records that would become invalid when this change is made so the records can be fixed prior to rolling out the change.  the records would need to be manually updated either through the UI or via calls to the relevant API.

SET search_path TO {tenant}_mod_inventory_storage;
SELECT item.id AS "item id", item.jsonb ->> 'itemLevelCallNumberTypeId' AS "Call Number Type ID"
FROM item
LEFT JOIN call_number_type ON
  CASE WHEN item.jsonb->>'itemLevelCallNumberTypeId' ~* '^[0-9a-z]{8}-[0-9a-z]{4}-[0-9a-z]{4}-[0-9a-z]{4}-[0-9a-z]{12}$'
       THEN (item.jsonb->>'itemLevelCallNumberTypeId')::uuid = call_number_type.id
       ELSE false
  END
WHERE item.jsonb ->> 'itemLevelCallNumberTypeId' IS NOT NULL
  AND call_number_type.id IS NULL;

Where {tenant} - the owner (tenant) for which you want to run the report.