Shelflist-holdings needs to be updated to work with MetaDB

[jeremythuff]

In shelflist-holdings/nodes/connect.json:

• The name field needs to be refactored "Connect LDP" -> "Connect MetaDB"
• The description field needs to be refactored "Connect to the LDP" -> "Connect to the MetaDB"
• designation field needs to be changed from ldp
• 'ldp' in variable references needs to be refactored to 'metadb' (ldp-url, ldp-user, ldp-password)

In shelflist-holdings/nodes/disconnect.json

• The description field needs to be refactored "Disconnect from the LDP." -> "Disconnect from the MetaDB."
• designation field needs to be changed from ldp

In shelflist-holdings/nodes/queryShelfList.json

• designation field needs to be changed from ldp*

In shelflist-holdings/nodes/js/buildShelflistQuery.js

• folio_reporting.holdings_ext -> folio_inventory.holdings_record__t__
• folio_reporting.instance_ext -> folio_inventory.instance__t__
• [ ]folio_reporting.instance_contributors -> folio_derived.instance_contributors
• folio_reporting.instance_publication -> folio_derived.instance_publication
• folio_reporting.instance_identifiers -> folio_derived.instance_identifiers
• folio_reporting.instance_formats -> folio_inventory.instance_format__t__
• folio_reporting.instance_languages -> folio_derived.instance_languages
• folio_reporting.instance_statistical_codes -> folio_derived.instance_statistical_codes
• folio_reporting.holdings_statements -> folio_derived.holdings_statements
• folio_reporting.items_holdings_instances -> folio_derived.item_holdings_instances
• folio_reporting.item_ext -> folio_inventory.item__t__
• mis.item_history -> mis.item_history

[rmathew1011]

Query prior to refactor ( used in LDP PRE)

WITH item_detail as (
    SELECT
        holdings_ext.holdings_id,
        SUM(item_hist.hist_charges) AS hist_charges,
        SUM(item_hist.hist_browses) AS hist_browses,
        MAX(item_hist.last_transaction) AS last_trans_date,
        string_agg(DISTINCT item_ext.material_type_name, ' || '  ORDER BY item_ext.material_type_name ASC) AS item_material_type
    FROM folio_reporting.holdings_ext holdings_ext
        LEFT JOIN folio_reporting.item_ext item_ext ON holdings_ext.holdings_id = item_ext.holdings_record_id
        LEFT JOIN mis.item_history item_hist ON item_ext.item_id = item_hist.item_id
    GROUP BY
        holdings_ext.holdings_id
)
SELECT DISTINCT ON (instance_hrid, holdings_hrid) * FROM (
    SELECT DISTINCT
        instance_ext.instance_id AS instance_id,
        instance_ext.instance_hrid AS instance_hrid,
        holdings_ext.holdings_hrid AS holdings_hrid,
        item_detail.hist_charges,
        item_detail.hist_browses,
        item_detail.last_trans_date,
        item_detail.item_material_type,
        quote_ident(holdings_ext.permanent_location_name) AS location,
        quote_ident(hold_state.statement) AS holdings_statement,
        CASE WHEN call_number_prefix IS NOT NULL AND call_number_suffix IS NOT NULL THEN quote_ident(call_number_prefix || ' ' || holdings_ext.call_number || ' ' || call_number_suffix)
        WHEN call_number_prefix IS NOT NULL AND call_number_suffix IS NULL THEN quote_ident(call_number_prefix || ' ' || holdings_ext.call_number)
        WHEN call_number_prefix IS NULL AND call_number_suffix IS NOT NULL THEN quote_ident(holdings_ext.call_number || ' ' || call_number_suffix)
        ELSE quote_ident(holdings_ext.call_number)
        END AS call_number,
        instance_ext.discovery_suppress AS instance_suppress,
        holdings_ext.discovery_suppress AS holdings_suppress,
        CASE WHEN contributor_primary = 't' THEN quote_ident(contributor_name) ELSE '' END AS author,
        RANK() OVER (PARTITION BY holdings_ext.holdings_hrid ORDER BY contributor_primary) AS author_rank,
        quote_ident(instance_ext.index_title) AS title,
        instance_ext.mode_of_issuance_name AS mode_of_issuance,
        instance_ext.type_name AS resource_type,
        formats.format_name AS format,
        CASE WHEN idents.identifier_type_name = 'ISBN' AND instance_ext.mode_of_issuance_name = 'serial' THEN idents.identifier
        WHEN idents.identifier_type_name = 'ISSN_Invalid' AND instance_ext.mode_of_issuance_name = 'serial' THEN idents.identifier
        WHEN idents.identifier_type_name = 'ISSN' AND instance_ext.mode_of_issuance_name = 'serial' THEN idents.identifier
        WHEN idents.identifier_type_name = 'OCLC' AND instance_ext.mode_of_issuance_name != 'serial' THEN idents.identifier
        ELSE ''
        END AS identifier,
        CASE WHEN lan.language_ordinality = 1 THEN lan.language
        ELSE ''
        END AS language,
        stat_codes.statistical_code_name AS stat_code,
        pubs.date_of_publication AS begin_pub_date,
        RANK() OVER (PARTITION BY holdings_ext.holdings_hrid ORDER BY pubs.date_of_publication) AS pub_rank,
        CAST(to_timestamp(instance_ext.record_created_date::text,'YYYY-MM-DD') AT TIME ZONE 'America/Chicago' AS DATE) AS create_date,
        CAST(to_timestamp(instance_ext.updated_date::text,'YYYY-MM-DD') AT TIME ZONE 'America/Chicago' AS DATE) AS update_date
    FROM folio_reporting.holdings_ext holdings_ext
        INNER JOIN folio_reporting.instance_ext ON holdings_ext.instance_id = instance_ext.instance_id
        LEFT JOIN folio_reporting.instance_contributors contribs ON holdings_ext.instance_id = contribs.instance_id
        LEFT JOIN folio_reporting.instance_publication pubs ON holdings_ext.instance_id = pubs.instance_id
        LEFT JOIN folio_reporting.instance_identifiers idents ON holdings_ext.instance_id = idents.instance_id
        LEFT JOIN folio_reporting.instance_formats formats ON holdings_ext.instance_id = formats.instance_id
        LEFT JOIN folio_reporting.instance_languages lan ON holdings_ext.instance_id = lan.instance_id
        LEFT JOIN folio_reporting.instance_statistical_codes stat_codes ON holdings_ext.instance_id = stat_codes.instance_id
        LEFT JOIN folio_reporting.holdings_statements hold_state ON holdings_ext.holdings_hrid = hold_state.holdings_hrid
        LEFT JOIN folio_reporting.items_holdings_instances item_hold_in ON holdings_ext.instance_id = item_hold_in.instance_id
        LEFT JOIN item_detail item_detail ON holdings_ext.holdings_id = item_detail.holdings_id
    WHERE TRUE
) AS superset
WHERE pub_rank = 1 AND author_rank = 1
ORDER BY instance_hrid, holdings_hrid, author desc, identifier desc, begin_pub_date desc, format
LIMIT 5;

Additional Change made:

holdings_ext.holdings_id  -> holdings_ext.id

[rmathew1011]

Refactored query:

WITH item_detail as (
    SELECT
        holdings_ext.id,
        SUM(item_hist.hist_charges) AS hist_charges,
        SUM(item_hist.hist_browses) AS hist_browses,
        MAX(item_hist.last_transaction) AS last_trans_date,
        string_agg(DISTINCT folio_derived.item_ext.material_type_name, ' || '  ORDER BY item_ext.material_type_name ASC) AS item_material_type
    FROM folio_inventory.holdings_record__t__ holdings_ext
        LEFT JOIN folio_derived.item_ext item_ext ON holdings_ext.id = item_ext.holdings_record_id 
        LEFT JOIN mis.item_history item_hist ON item_ext.item_id = item_hist.item_id
    GROUP BY
        holdings_ext.id
)
SELECT DISTINCT ON (instance_hrid, holdings_hrid) * FROM (
    SELECT DISTINCT
        instance_ext.instance_id AS instance_id,
        instance_ext.instance_hrid AS instance_hrid,
        holdings_ext.holdings_hrid AS holdings_hrid,
        item_detail.hist_charges,
        item_detail.hist_browses,
        item_detail.last_trans_date,
        item_detail.item_material_type,
        quote_ident(holdings_ext.permanent_location_name) AS location,
        quote_ident(hold_state.statement) AS holdings_statement,
        CASE WHEN call_number_prefix IS NOT NULL AND call_number_suffix IS NOT NULL THEN quote_ident(call_number_prefix || ' ' || holdings_ext.call_number || ' ' || call_number_suffix)
        WHEN call_number_prefix IS NOT NULL AND call_number_suffix IS NULL THEN quote_ident(call_number_prefix || ' ' || holdings_ext.call_number)
        WHEN call_number_prefix IS NULL AND call_number_suffix IS NOT NULL THEN quote_ident(holdings_ext.call_number || ' ' || call_number_suffix)
        ELSE quote_ident(holdings_ext.call_number)
        END AS call_number,
        instance_ext.discovery_suppress AS instance_suppress,
        holdings_ext.discovery_suppress AS holdings_suppress,
        CASE WHEN contributor_primary = 't' THEN quote_ident(contributor_name) ELSE '' END AS author,
        RANK() OVER (PARTITION BY holdings_ext.holdings_hrid ORDER BY contributor_primary) AS author_rank,
        quote_ident(instance_ext.index_title) AS title,
        instance_ext.mode_of_issuance_name AS mode_of_issuance,
        instance_ext.type_name AS resource_type,
        formats.format_name AS format,
        CASE WHEN idents.identifier_type_name = 'ISBN' AND instance_ext.mode_of_issuance_name = 'serial' THEN idents.identifier
        WHEN idents.identifier_type_name = 'ISSN_Invalid' AND instance_ext.mode_of_issuance_name = 'serial' THEN idents.identifier
        WHEN idents.identifier_type_name = 'ISSN' AND instance_ext.mode_of_issuance_name = 'serial' THEN idents.identifier
        WHEN idents.identifier_type_name = 'OCLC' AND instance_ext.mode_of_issuance_name != 'serial' THEN idents.identifier
        ELSE ''
        END AS identifier,
        CASE WHEN lan.language_ordinality = 1 THEN lan.language
        ELSE ''
        END AS language,
        stat_codes.statistical_code_name AS stat_code,
        pubs.date_of_publication AS begin_pub_date,
        RANK() OVER (PARTITION BY holdings_ext.holdings_hrid ORDER BY pubs.date_of_publication) AS pub_rank,
        CAST(to_timestamp(instance_ext.record_created_date::text,'YYYY-MM-DD') AT TIME ZONE 'America/Chicago' AS DATE) AS create_date,
        CAST(to_timestamp(instance_ext.updated_date::text,'YYYY-MM-DD') AT TIME ZONE 'America/Chicago' AS DATE) AS update_date
    FROM folio_inventory.holdings_record__t__ holdings_ext
        INNER JOIN folio_inventory.instance__t__ instance_ext ON holdings_ext.instance_id = instance_ext.instance_id
        LEFT JOIN folio_derived.instance_contributors contribs ON holdings_ext.instance_id = contribs.instance_id
        LEFT JOIN folio_derived.instance_publication pubs ON holdings_ext.instance_id = pubs.instance_id
        LEFT JOIN folio_derived.instance_identifiers idents ON holdings_ext.instance_id = idents.instance_id
        LEFT JOIN folio_inventory.instance_format__t__ formats ON holdings_ext.instance_id = formats.instance_id
        LEFT JOIN folio_derived.instance_languages lan ON holdings_ext.instance_id = lan.instance_id
        LEFT JOIN folio_derived.instance_statistical_codes stat_codes ON holdings_ext.instance_id = stat_codes.instance_id
        LEFT JOIN folio_derived.holdings_statements hold_state ON holdings_ext.holdings_hrid = hold_state.holdings_hrid
        LEFT JOIN folio_derived.items_holdings_instances item_hold_in ON holdings_ext.instance_id = item_hold_in.instance_id
        LEFT JOIN item_detail item_detail ON holdings_ext.id = item_detail.holdings_id
    WHERE TRUE
) AS superset
WHERE pub_rank = 1 AND author_rank = 1
ORDER BY instance_hrid, holdings_hrid, author desc, identifier desc, begin_pub_date desc, format
LIMIT 5;

[rmathew1011]

ERROR 1 --- [mod-camunda] [aTaskExecutor-2] org.camunda.bpm.engine.context           : ENGINE-16004 Exception while closing command context: couldn't execute activity <serviceTask id="database_query_task_1554364a_f73a_41ee_b61b_8c1d8755d021" ...>: ERROR: relation "folio_reporting.item_ext" does not exist
  Position: 400

[wwtamu]

ERROR 1 --- [mod-camunda] [aTaskExecutor-2] org.camunda.bpm.engine.context           : ENGINE-16004 Exception while closing command context: couldn't execute activity <serviceTask id="database_query_task_1554364a_f73a_41ee_b61b_8c1d8755d021" ...>: ERROR: relation "folio_reporting.item_ext" does not exist
  Position: 400

Here it is https://github.com/TAMULib/fw-registry/pull/447/files#diff-56e6ff7da07eaad6ca47ce2c332f32adf1882ad9b64190c25a6b226b0467cb99R149

folio_reporting.item_ext -> folio_inventory.item__t__

[rmathew1011]

1. ERROR: column holdings_ext.holdings_hrid does not exist -> refactored to holdings_ext.hrid
2. holdings_ext.holdings_id refactored to holdings_ext.id
3. folio_reporting.holdings_ext -> folio_inventory.holdings_record__t__
4. instance_ext.instance_id -> instance_ext.id AS instance_id
5. instance_ext.instance_hrid -> instance_ext.hrid
6. holdings_ext.holdings_hrid -> holdings_ext.hrid
7. Added the 2 joins:

INNER JOIN folio_derived.holdings_ext fd_holdings_ext ON holdings_ext.instance_id = fd_holdings_ext.instance_id
INNER JOIN folio_derived.instance_ext fd_instance_ext ON holdings_ext.instance_id = fd_instance_ext.instance_id

8. holdings_ext.permanent_location_name -> fd_holdings_ext.permanent_location_name
9. hold_state.statement -> hold_state.holdings_statement
10. call_number_prefix -> holdings_ext.call_number_prefix
11. call_number_suffix -> holdings_ext.call_number_suffix
12. contributor_primary -> contribs.contributor_is_primary
13. contributor_name -> contribs.contributor_name
14. instance_ext.mode_of_issuance_name -> fd_instance_ext.mode_of_issuance_name
15. instance_ext.type_name -> fd_instance_ext.type_name
16. formats.format_name -> formats.instance_format_name
17. lan.language -> lan.instance_language
18. instance_ext.record_created_date::text -> fd_instance_ext.record_created_date::text
19. instance_ext.record_updated_date::text -> fd_instance_ext.record_updated_date::text

working query:

WITH item_detail as (
    SELECT
        holdings_ext.id as holdings_id,
        SUM(item_hist.hist_charges) AS hist_charges,
        SUM(item_hist.hist_browses) AS hist_browses,
        MAX(item_hist.last_transaction) AS last_trans_date,
        string_agg(DISTINCT item_ext.material_type_name, ' || '  ORDER BY item_ext.material_type_name ASC) AS item_material_type
    FROM folio_inventory.holdings_record__t__ holdings_ext
        LEFT JOIN folio_derived.item_ext item_ext ON holdings_ext.id = item_ext.holdings_record_id 
        LEFT JOIN mis.item_history item_hist ON item_ext.item_id = item_hist.item_id :: uuid
    GROUP BY
        holdings_ext.id
)
SELECT DISTINCT ON (instance_hrid, holdings_hrid) * FROM (
    SELECT DISTINCT
        instance_ext.id AS instance_id,
        instance_ext.hrid AS instance_hrid,
        holdings_ext.hrid AS holdings_hrid,
        item_detail.hist_charges,
        item_detail.hist_browses,
        item_detail.last_trans_date,
        item_detail.item_material_type,
        quote_ident(fd_holdings_ext.permanent_location_name) AS location,
        quote_ident(hold_state.holdings_statement) AS holdings_statement,
        CASE WHEN holdings_ext.call_number_prefix IS NOT NULL AND holdings_ext.call_number_suffix IS NOT NULL THEN quote_ident(holdings_ext.call_number_prefix || ' ' || holdings_ext.call_number || ' ' || holdings_ext.call_number_suffix)
        WHEN holdings_ext.call_number_prefix IS NOT NULL AND holdings_ext.call_number_suffix IS NULL THEN quote_ident(holdings_ext.call_number_prefix || ' ' || holdings_ext.call_number)
        WHEN holdings_ext.call_number_prefix IS NULL AND holdings_ext.call_number_suffix IS NOT NULL THEN quote_ident(holdings_ext.call_number || ' ' || holdings_ext.call_number_suffix)
        ELSE quote_ident(holdings_ext.call_number)
        END AS call_number,
        instance_ext.discovery_suppress AS instance_suppress,
        holdings_ext.discovery_suppress AS holdings_suppress,
        CASE WHEN contribs.contributor_is_primary = 't' THEN quote_ident(contribs.contributor_name) ELSE '' END AS author,
        RANK() OVER (PARTITION BY holdings_ext.hrid ORDER BY contribs.contributor_is_primary) AS author_rank,
        quote_ident(instance_ext.index_title) AS title,
        fd_instance_ext.mode_of_issuance_name AS mode_of_issuance,
        fd_instance_ext.type_name AS resource_type,
        formats.instance_format_name AS format,
        CASE WHEN idents.identifier_type_name = 'ISBN' AND fd_instance_ext.mode_of_issuance_name = 'serial' THEN idents.identifier
        WHEN idents.identifier_type_name = 'ISSN_Invalid' AND fd_instance_ext.mode_of_issuance_name = 'serial' THEN idents.identifier
        WHEN idents.identifier_type_name = 'ISSN' AND fd_instance_ext.mode_of_issuance_name = 'serial' THEN idents.identifier
        WHEN idents.identifier_type_name = 'OCLC' AND fd_instance_ext.mode_of_issuance_name != 'serial' THEN idents.identifier
        ELSE ''
        END AS identifier,
        CASE WHEN lan.language_ordinality = 1 THEN lan.instance_language
        ELSE ''
        END AS language,
        stat_codes.statistical_code_name AS stat_code,
        pubs.date_of_publication AS begin_pub_date,
        RANK() OVER (PARTITION BY holdings_ext.hrid ORDER BY pubs.date_of_publication) AS pub_rank,
        CAST(to_timestamp(fd_instance_ext.record_created_date::text,'YYYY-MM-DD') AT TIME ZONE 'America/Chicago' AS DATE) AS create_date,
        CAST(to_timestamp(fd_instance_ext.updated_date::text,'YYYY-MM-DD') AT TIME ZONE 'America/Chicago' AS DATE) AS update_date
    FROM folio_inventory.holdings_record__t__ holdings_ext
        INNER JOIN folio_inventory.instance__t__ instance_ext ON holdings_ext.instance_id = instance_ext.id
        INNER JOIN folio_derived.holdings_ext fd_holdings_ext ON holdings_ext.instance_id = fd_holdings_ext.instance_id
        INNER JOIN folio_derived.instance_ext fd_instance_ext ON holdings_ext.instance_id = fd_instance_ext.instance_id
        LEFT JOIN folio_derived.instance_contributors contribs ON holdings_ext.instance_id = contribs.instance_id
        LEFT JOIN folio_derived.instance_publication pubs ON holdings_ext.instance_id = pubs.instance_id
        LEFT JOIN folio_derived.instance_identifiers idents ON holdings_ext.instance_id = idents.instance_id
        LEFT JOIN folio_derived.instance_formats formats ON holdings_ext.instance_id = formats.instance_id
        LEFT JOIN folio_derived.instance_languages lan ON holdings_ext.instance_id = lan.instance_id
        LEFT JOIN folio_derived.instance_statistical_codes stat_codes ON holdings_ext.instance_id = stat_codes.instance_id
        LEFT JOIN folio_derived.holdings_statements hold_state ON holdings_ext.hrid = hold_state.holdings_hrid
        LEFT JOIN folio_derived.items_holdings_instances item_hold_in ON holdings_ext.instance_id = item_hold_in.instance_id
        LEFT JOIN item_detail item_detail ON holdings_ext.id = item_detail.holdings_id
    WHERE TRUE
) AS superset
WHERE pub_rank = 1 AND author_rank = 1
ORDER BY instance_hrid, holdings_hrid , author desc, identifier desc, begin_pub_date desc, format
LIMIT 1;

[rmathew1011]

Error executing query:
SQL Error [57014]: ERROR: canceling statement due to statement timeout

[rmathew1011]

additional sql query refactor:

public.inventory_locations publoc is changed to folio_inventory.location__t__  publoc
public.inventory_libraries publib is changed to folio_inventory.loclibrary__t__ publib

[rmathew1011]

Working Query ( Workflow triggered using Catalog Reports dev)

Correlating message /events/shelflist-holdings/start
2025-08-07T22:13:04.443Z  INFO 1 --- [mod-camunda] [-listener-0-C-1] o.f.rest.camunda.delegate.SetupDelegate  : Setup [] started
2025-08-07T22:13:04.443Z  INFO 1 --- [mod-camunda] [-listener-0-C-1] o.f.rest.camunda.delegate.SetupDelegate  : loading initial context
2025-08-07T22:13:04.443Z  INFO 1 --- [mod-camunda] [-listener-0-C-1] o.f.rest.camunda.delegate.SetupDelegate  : loading scripts
2025-08-07T22:13:04.443Z  INFO 1 --- [mod-camunda] [-listener-0-C-1] o.f.rest.camunda.delegate.SetupDelegate  : Setup finished in 0.348729 milliseconds
[engine] WARNING: The polyglot context is using an implementation that does not support runtime compilation.
The guest application code will therefore be executed in interpreted mode only.
Execution only in interpreted mode will strongly impact the guest application performance.
For more information on using GraalVM see https://www.graalvm.org/java/quickstart/.
To disable this warning the '--engine.WarnInterpreterOnly=false' option or use the '-Dpolyglot.engine.WarnInterpreterOnly=false' system property.
[engine] WARNING: The polyglot context is using an implementation that does not support runtime compilation.
The guest application code will therefore be executed in interpreted mode only.
Execution only in interpreted mode will strongly impact the guest application performance.
For more information on using GraalVM see https://www.graalvm.org/java/quickstart/.
To disable this warning the '--engine.WarnInterpreterOnly=false' option or use the '-Dpolyglot.engine.WarnInterpreterOnly=false' system property.

logLevel = DEBUG

emailFrom = folio_admin-dev@library.tamu.edu

emailTo = rmathew@tamu.edu

libraryName = null

locationDiscoveryDisplayName = null

locationName = ["Evans pres repr"]

language = ["All"]

resourceType = null

format = null

batchId = 

issuance = 

suppressInstance = null

suppressHoldings = null

createdDateStart = 

createdDateEnd = 

updatedDateStart = 

updatedDateEnd = 


shelflistQuery = 

WITH item_detail as (
	SELECT
		holdings_ext.id as holdings_id,
		SUM(item_hist.hist_charges) AS hist_charges,
		SUM(item_hist.hist_browses) AS hist_browses,
		MAX(item_hist.last_transaction) AS last_trans_date,
		string_agg(DISTINCT item_ext.material_type_name, ' || '  ORDER BY item_ext.material_type_name ASC) AS item_material_type
	FROM folio_inventory.holdings_record__t__ holdings_ext
		LEFT JOIN folio_derived.item_ext item_ext ON holdings_ext.id :: uuid = item_ext.holdings_record_id ::uuid
		LEFT JOIN mis.item_history item_hist ON item_ext.item_id :: uuid = item_hist.item_id ::uuid
	GROUP BY
		holdings_ext.id
)
SELECT DISTINCT ON (instance_hrid, holdings_hrid) * FROM (
	SELECT DISTINCT
		instance_ext.id AS instance_id,
		instance_ext.hrid AS instance_hrid,
		holdings_ext.hrid AS holdings_hrid,
		item_detail.hist_charges,
		item_detail.hist_browses,
		item_detail.last_trans_date,
		item_detail.item_material_type,
		quote_ident(fd_holdings_ext.permanent_location_name) AS location,
		quote_ident(hold_state.holdings_statement) AS holdings_statement,
		CASE WHEN holdings_ext.call_number_prefix IS NOT NULL AND holdings_ext.call_number_suffix IS NOT NULL THEN quote_ident(holdings_ext.call_number_prefix || ' ' || holdings_ext.call_number || ' ' || holdings_ext.call_number_suffix)
		WHEN holdings_ext.call_number_prefix IS NOT NULL AND holdings_ext.call_number_suffix IS NULL THEN quote_ident(holdings_ext.call_number_prefix || ' ' || holdings_ext.call_number)
		WHEN holdings_ext.call_number_prefix IS NULL AND holdings_ext.call_number_suffix IS NOT NULL THEN quote_ident(holdings_ext.call_number || ' ' || holdings_ext.call_number_suffix)
		ELSE quote_ident(holdings_ext.call_number)
		END AS call_number,
		instance_ext.discovery_suppress AS instance_suppress,
		holdings_ext.discovery_suppress AS holdings_suppress,
		CASE WHEN contribs.contributor_is_primary = 't' THEN quote_ident(contribs.contributor_name) ELSE '' END AS author,
		RANK() OVER (PARTITION BY holdings_ext.hrid ORDER BY contribs.contributor_is_primary) AS author_rank,
		quote_ident(instance_ext.index_title) AS title,
		fd_instance_ext.mode_of_issuance_name AS mode_of_issuance,
		fd_instance_ext.type_name AS resource_type,
		formats.instance_format_name AS format,
		CASE WHEN idents.identifier_type_name = 'ISBN' AND fd_instance_ext.mode_of_issuance_name = 'serial' THEN idents.identifier
		WHEN idents.identifier_type_name = 'ISSN_Invalid' AND fd_instance_ext.mode_of_issuance_name = 'serial' THEN idents.identifier
		WHEN idents.identifier_type_name = 'ISSN' AND fd_instance_ext.mode_of_issuance_name = 'serial' THEN idents.identifier
		WHEN idents.identifier_type_name = 'OCLC' AND fd_instance_ext.mode_of_issuance_name != 'serial' THEN idents.identifier
		ELSE ''
		END AS identifier,
		CASE WHEN lan.language_ordinality = 1 THEN lan.instance_language
		ELSE ''
		END AS language,
		stat_codes.statistical_code_name AS stat_code,
		pubs.date_of_publication AS begin_pub_date,
		RANK() OVER (PARTITION BY holdings_ext.hrid ORDER BY pubs.date_of_publication) AS pub_rank,
		CAST(to_timestamp(fd_instance_ext.record_created_date::text,'YYYY-MM-DD') AT TIME ZONE 'America/Chicago' AS DATE) AS create_date,
		CAST(to_timestamp(fd_instance_ext.updated_date::text,'YYYY-MM-DD') AT TIME ZONE 'America/Chicago' AS DATE) AS update_date
	FROM folio_inventory.holdings_record__t__ holdings_ext
		INNER JOIN folio_inventory.instance__t__ instance_ext ON holdings_ext.instance_id = instance_ext.id
		INNER JOIN folio_derived.holdings_ext fd_holdings_ext ON holdings_ext.instance_id = fd_holdings_ext.instance_id
		INNER JOIN folio_derived.instance_ext fd_instance_ext ON holdings_ext.instance_id = fd_instance_ext.instance_id
		LEFT JOIN folio_derived.instance_contributors contribs ON holdings_ext.instance_id = contribs.instance_id
		LEFT JOIN folio_derived.instance_publication pubs ON holdings_ext.instance_id = pubs.instance_id
		LEFT JOIN folio_derived.instance_identifiers idents ON holdings_ext.instance_id = idents.instance_id
		LEFT JOIN folio_derived.instance_formats formats ON holdings_ext.instance_id = formats.instance_id
		LEFT JOIN folio_derived.instance_languages lan ON holdings_ext.instance_id = lan.instance_id
		LEFT JOIN folio_derived.instance_statistical_codes stat_codes ON holdings_ext.instance_id = stat_codes.instance_id
		LEFT JOIN folio_derived.holdings_statements hold_state ON holdings_ext.hrid = hold_state.holdings_hrid
		LEFT JOIN folio_derived.items_holdings_instances item_hold_in ON holdings_ext.instance_id = item_hold_in.instance_id
		LEFT JOIN item_detail item_detail ON holdings_ext.id = item_detail.holdings_id
	WHERE TRUE
		AND fd_holdings_ext.permanent_location_name IN ('Evans pres repr')
) AS superset
WHERE pub_rank = 1 AND author_rank = 1
ORDER BY instance_hrid, holdings_hrid, author desc, identifier desc, begin_pub_date desc, format

2025-08-07T22:13:04.497Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.d.DatabaseConnectionDelegate     : Connect MetaDB [] started
2025-08-07T22:13:04.497Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.d.DatabaseConnectionDelegate     : Connect MetaDB finished in 0.041296 milliseconds
2025-08-07T22:13:04.510Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.delegate.DatabaseQueryDelegate   : Query Shelflist (holdings level) [] started
2025-08-07T22:15:54.954Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.delegate.DatabaseQueryDelegate   : Writing results to file as CSV including headers
2025-08-07T22:15:54.955Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.delegate.DatabaseQueryDelegate   : Query Shelflist (holdings level) did not specify output variable for result count
2025-08-07T22:15:54.956Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.delegate.DatabaseQueryDelegate   : Query Shelflist (holdings level) finished in 170445.910016 milliseconds
2025-08-07T22:15:54.971Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.d.DatabaseDisconnectDelegate     : Disconnect MetaDB [] started
2025-08-07T22:15:54.971Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.d.DatabaseDisconnectDelegate     : Disconnect MetaDB finished in 0.010285 milliseconds
2025-08-07T22:15:54.983Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.delegate.CompressFileDelegate    : Zip Shelflist (holdings level) report [] started
2025-08-07T22:15:54.984Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.delegate.CompressFileDelegate    : Destination: /mnt/workflows/tamu/shelflist-holdings/shelflist-holdings-report-1754604784443.zip
2025-08-07T22:15:54.984Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.delegate.CompressFileDelegate    : Source: /mnt/workflows/tamu/shelflist-holdings/shelflist-holdings-report-1754604784443.csv
2025-08-07T22:15:54.984Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.delegate.CompressFileDelegate    : Compress format: ZIP
2025-08-07T22:15:55.000Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.delegate.CompressFileDelegate    : Zip Shelflist (holdings level) report finished in 16.807897 milliseconds
2025-08-07T22:15:55.011Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.rest.camunda.delegate.FileDelegate   : Delete Shelftlist (holdings level) Report file [DELETE] started
2025-08-07T22:15:55.013Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.rest.camunda.delegate.FileDelegate   : /mnt/workflows/tamu/shelflist-holdings/shelflist-holdings-report-1754604784443.csv has been deleted
2025-08-07T22:15:55.013Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.rest.camunda.delegate.FileDelegate   : Delete Shelftlist (holdings level) Report file finished in 1.911449 milliseconds
2025-08-07T22:15:55.023Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.rest.camunda.delegate.EmailDelegate  : Send Shelflist (holdings level) Report Email [] started
2025-08-07T22:15:55.027Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.rest.camunda.delegate.EmailDelegate  : No attachment required
2025-08-07T22:15:55.234Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.rest.camunda.delegate.EmailDelegate  : Send Shelflist (holdings level) Report Email finished

[rmathew1011]

[rmathew1011]

logLevel = DEBUG

emailFrom = folio_admin-dev@library.tamu.edu

emailTo = rmathew@tamu.edu

libraryName = null

locationDiscoveryDisplayName = ["All"]

locationName = null

language = ["aar"]

resourceType = null

format = null

batchId = AADR

issuance = single unit

suppressInstance = f

suppressHoldings = f

createdDateStart = 2025-08-01

createdDateEnd = 2025-08-07

updatedDateStart = 2025-08-01

updatedDateEnd = 2025-08-07


shelflistQuery = 

WITH item_detail as (
	SELECT
		holdings_ext.id as holdings_id,
		SUM(item_hist.hist_charges) AS hist_charges,
		SUM(item_hist.hist_browses) AS hist_browses,
		MAX(item_hist.last_transaction) AS last_trans_date,
		string_agg(DISTINCT item_ext.material_type_name, ' || '  ORDER BY item_ext.material_type_name ASC) AS item_material_type
	FROM folio_inventory.holdings_record__t__ holdings_ext
		LEFT JOIN folio_derived.item_ext item_ext ON holdings_ext.id :: uuid = item_ext.holdings_record_id ::uuid
		LEFT JOIN mis.item_history item_hist ON item_ext.item_id :: uuid = item_hist.item_id ::uuid
	GROUP BY
		holdings_ext.id
)
SELECT DISTINCT ON (instance_hrid, holdings_hrid) * FROM (
	SELECT DISTINCT
		instance_ext.id AS instance_id,
		instance_ext.hrid AS instance_hrid,
		holdings_ext.hrid AS holdings_hrid,
		item_detail.hist_charges,
		item_detail.hist_browses,
		item_detail.last_trans_date,
		item_detail.item_material_type,
		quote_ident(fd_holdings_ext.permanent_location_name) AS location,
		quote_ident(hold_state.holdings_statement) AS holdings_statement,
		CASE WHEN holdings_ext.call_number_prefix IS NOT NULL AND holdings_ext.call_number_suffix IS NOT NULL THEN quote_ident(holdings_ext.call_number_prefix || ' ' || holdings_ext.call_number || ' ' || holdings_ext.call_number_suffix)
		WHEN holdings_ext.call_number_prefix IS NOT NULL AND holdings_ext.call_number_suffix IS NULL THEN quote_ident(holdings_ext.call_number_prefix || ' ' || holdings_ext.call_number)
		WHEN holdings_ext.call_number_prefix IS NULL AND holdings_ext.call_number_suffix IS NOT NULL THEN quote_ident(holdings_ext.call_number || ' ' || holdings_ext.call_number_suffix)
		ELSE quote_ident(holdings_ext.call_number)
		END AS call_number,
		instance_ext.discovery_suppress AS instance_suppress,
		holdings_ext.discovery_suppress AS holdings_suppress,
		CASE WHEN contribs.contributor_is_primary = 't' THEN quote_ident(contribs.contributor_name) ELSE '' END AS author,
		RANK() OVER (PARTITION BY holdings_ext.hrid ORDER BY contribs.contributor_is_primary) AS author_rank,
		quote_ident(instance_ext.index_title) AS title,
		fd_instance_ext.mode_of_issuance_name AS mode_of_issuance,
		fd_instance_ext.type_name AS resource_type,
		formats.instance_format_name AS format,
		CASE WHEN idents.identifier_type_name = 'ISBN' AND fd_instance_ext.mode_of_issuance_name = 'serial' THEN idents.identifier
		WHEN idents.identifier_type_name = 'ISSN_Invalid' AND fd_instance_ext.mode_of_issuance_name = 'serial' THEN idents.identifier
		WHEN idents.identifier_type_name = 'ISSN' AND fd_instance_ext.mode_of_issuance_name = 'serial' THEN idents.identifier
		WHEN idents.identifier_type_name = 'OCLC' AND fd_instance_ext.mode_of_issuance_name != 'serial' THEN idents.identifier
		ELSE ''
		END AS identifier,
		CASE WHEN lan.language_ordinality = 1 THEN lan.instance_language
		ELSE ''
		END AS language,
		stat_codes.statistical_code_name AS stat_code,
		pubs.date_of_publication AS begin_pub_date,
		RANK() OVER (PARTITION BY holdings_ext.hrid ORDER BY pubs.date_of_publication) AS pub_rank,
		CAST(to_timestamp(fd_instance_ext.record_created_date::text,'YYYY-MM-DD') AT TIME ZONE 'America/Chicago' AS DATE) AS create_date,
		CAST(to_timestamp(fd_instance_ext.updated_date::text,'YYYY-MM-DD') AT TIME ZONE 'America/Chicago' AS DATE) AS update_date
	FROM folio_inventory.holdings_record__t__ holdings_ext
		INNER JOIN folio_inventory.instance__t__ instance_ext ON holdings_ext.instance_id = instance_ext.id
		INNER JOIN folio_derived.holdings_ext fd_holdings_ext ON holdings_ext.instance_id = fd_holdings_ext.instance_id
		INNER JOIN folio_derived.instance_ext fd_instance_ext ON holdings_ext.instance_id = fd_instance_ext.instance_id
		LEFT JOIN folio_derived.instance_contributors contribs ON holdings_ext.instance_id = contribs.instance_id
		LEFT JOIN folio_derived.instance_publication pubs ON holdings_ext.instance_id = pubs.instance_id
		LEFT JOIN folio_derived.instance_identifiers idents ON holdings_ext.instance_id = idents.instance_id
		LEFT JOIN folio_derived.instance_formats formats ON holdings_ext.instance_id = formats.instance_id
		LEFT JOIN folio_derived.instance_languages lan ON holdings_ext.instance_id = lan.instance_id
		LEFT JOIN folio_derived.instance_statistical_codes stat_codes ON holdings_ext.instance_id = stat_codes.instance_id
		LEFT JOIN folio_derived.holdings_statements hold_state ON holdings_ext.hrid = hold_state.holdings_hrid
		LEFT JOIN folio_derived.items_holdings_instances item_hold_in ON holdings_ext.instance_id = item_hold_in.instance_id
		LEFT JOIN item_detail item_detail ON holdings_ext.id = item_detail.holdings_id
	WHERE TRUE
		AND lan.instance_language IN ('aar')
		AND lan.language_ordinality = 1
		AND stat_codes.statistical_code_type_name = 'batch_id'
		AND stat_codes.statistical_code_name = 'AADR'
		AND fd_instance_ext.mode_of_issuance_name = 'single unit'
		AND instance_ext.discovery_suppress = false
		AND holdings_ext.discovery_suppress = false
		AND cast(to_timestamp(fd_instance_ext.record_created_date::text,'YYYY-MM-DD') AT TIME ZONE 'America/Chicago' AS DATE) >= to_date('2025-08-01', 'YYYY-MM-DD')
		AND cast(to_timestamp(fd_instance_ext.record_created_date::text,'YYYY-MM-DD') AT TIME ZONE 'America/Chicago' AS DATE) <= to_date('2025-08-07', 'YYYY-MM-DD')
		AND cast(to_timestamp(fd_instance_ext.updated_date::text,'YYYY-MM-DD') AT TIME ZONE 'America/Chicago' AS DATE) >= to_date('2025-08-01', 'YYYY-MM-DD')
		AND cast(to_timestamp(fd_instance_ext.updated_date::text,'YYYY-MM-DD') AT TIME ZONE 'America/Chicago' AS DATE) <= to_date('2025-08-07', 'YYYY-MM-DD')
) AS superset
WHERE pub_rank = 1 AND author_rank = 1
ORDER BY instance_hrid, holdings_hrid, author desc, identifier desc, begin_pub_date desc, format

2025-08-08T02:50:26.014Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.d.DatabaseConnectionDelegate     : Connect MetaDB [] started
2025-08-08T02:50:26.014Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.d.DatabaseConnectionDelegate     : Connect MetaDB finished in 0.010414 milliseconds
2025-08-08T02:50:26.026Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.delegate.DatabaseQueryDelegate   : Query Shelflist (holdings level) [] started
2025-08-08T02:50:29.422Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.delegate.DatabaseQueryDelegate   : Writing results to file as CSV including headers
2025-08-08T02:50:29.422Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.delegate.DatabaseQueryDelegate   : Query Shelflist (holdings level) did not specify output variable for result count
2025-08-08T02:50:29.423Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.delegate.DatabaseQueryDelegate   : Query Shelflist (holdings level) finished in 3396.749963 milliseconds
2025-08-08T02:50:29.436Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.d.DatabaseDisconnectDelegate     : Disconnect MetaDB [] started
2025-08-08T02:50:29.436Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.d.DatabaseDisconnectDelegate     : Disconnect MetaDB finished in 0.007969 milliseconds
2025-08-08T02:50:29.447Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.delegate.CompressFileDelegate    : Zip Shelflist (holdings level) report [] started
2025-08-08T02:50:29.448Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.delegate.CompressFileDelegate    : Destination: /mnt/workflows/tamu/shelflist-holdings/shelflist-holdings-report-1754621425932.zip
2025-08-08T02:50:29.448Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.delegate.CompressFileDelegate    : Source: /mnt/workflows/tamu/shelflist-holdings/shelflist-holdings-report-1754621425932.csv
2025-08-08T02:50:29.449Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.delegate.CompressFileDelegate    : Compress format: ZIP
2025-08-08T02:50:29.470Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.r.c.delegate.CompressFileDelegate    : Zip Shelflist (holdings level) report finished in 22.598473 milliseconds
2025-08-08T02:50:29.482Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.rest.camunda.delegate.FileDelegate   : Delete Shelftlist (holdings level) Report file [DELETE] started
2025-08-08T02:50:29.484Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.rest.camunda.delegate.FileDelegate   : /mnt/workflows/tamu/shelflist-holdings/shelflist-holdings-report-1754621425932.csv has been deleted
2025-08-08T02:50:29.484Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.rest.camunda.delegate.FileDelegate   : Delete Shelftlist (holdings level) Report file finished in 1.803685 milliseconds
2025-08-08T02:50:29.494Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.rest.camunda.delegate.EmailDelegate  : Send Shelflist (holdings level) Report Email [] started
2025-08-08T02:50:29.498Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.rest.camunda.delegate.EmailDelegate  : No attachment required
2025-08-08T02:50:29.659Z  INFO 1 --- [mod-camunda] [aTaskExecutor-2] o.f.rest.camunda.delegate.EmailDelegate  : Send Shelflist (holdings level) Report Email finished in 164.892701 milliseconds

Another case:

Setup [] started
2025-08-08T02:54:46.096Z  INFO 1 --- [mod-camunda] [-listener-0-C-1] o.f.rest.camunda.delegate.SetupDelegate  : loading initial context
2025-08-08T02:54:46.096Z  INFO 1 --- [mod-camunda] [-listener-0-C-1] o.f.rest.camunda.delegate.SetupDelegate  : loading scripts
2025-08-08T02:54:46.096Z  INFO 1 --- [mod-camunda] [-listener-0-C-1] o.f.rest.camunda.delegate.SetupDelegate  : Setup finished in 0.200599 milliseconds
[engine] WARNING: The polyglot context is using an implementation that does not support runtime compilation.
The guest application code will therefore be executed in interpreted mode only.
Execution only in interpreted mode will strongly impact the guest application performance.
For more information on using GraalVM see https://www.graalvm.org/java/quickstart/.
To disable this warning the '--engine.WarnInterpreterOnly=false' option or use the '-Dpolyglot.engine.WarnInterpreterOnly=false' system property.
[engine] WARNING: The polyglot context is using an implementation that does not support runtime compilation.
The guest application code will therefore be executed in interpreted mode only.
Execution only in interpreted mode will strongly impact the guest application performance.
For more information on using GraalVM see https://www.graalvm.org/java/quickstart/.
To disable this warning the '--engine.WarnInterpreterOnly=false' option or use the '-Dpolyglot.engine.WarnInterpreterOnly=false' system property.

logLevel = DEBUG

emailFrom = folio_admin-dev@library.tamu.edu

emailTo = rmathew@tamu.edu

libraryName = ["MSL: Bryan"]

locationDiscoveryDisplayName = null

locationName = null

language = ["aar"]

resourceType = ["All"]

format = null

batchId = AADR

issuance = single unit

suppressInstance = t

suppressHoldings = t

createdDateStart = 2025-08-01

createdDateEnd = 2025-08-07

updatedDateStart = 2025-08-01

updatedDateEnd = 2025-08-07


shelflistQuery = 

WITH item_detail as (
	SELECT
		holdings_ext.id as holdings_id,
		SUM(item_hist.hist_charges) AS hist_charges,
		SUM(item_hist.hist_browses) AS hist_browses,
		MAX(item_hist.last_transaction) AS last_trans_date,
		string_agg(DISTINCT item_ext.material_type_name, ' || '  ORDER BY item_ext.material_type_name ASC) AS item_material_type
	FROM folio_inventory.holdings_record__t__ holdings_ext
		LEFT JOIN folio_derived.item_ext item_ext ON holdings_ext.id :: uuid = item_ext.holdings_record_id ::uuid
		LEFT JOIN mis.item_history item_hist ON item_ext.item_id :: uuid = item_hist.item_id ::uuid
	GROUP BY
		holdings_ext.id
)
SELECT DISTINCT ON (instance_hrid, holdings_hrid) * FROM (
	SELECT DISTINCT
		instance_ext.id AS instance_id,
		instance_ext.hrid AS instance_hrid,
		holdings_ext.hrid AS holdings_hrid,
		item_detail.hist_charges,
		item_detail.hist_browses,
		item_detail.last_trans_date,
		item_detail.item_material_type,
		quote_ident(fd_holdings_ext.permanent_location_name) AS location,
		quote_ident(hold_state.holdings_statement) AS holdings_statement,
		CASE WHEN holdings_ext.call_number_prefix IS NOT NULL AND holdings_ext.call_number_suffix IS NOT NULL THEN quote_ident(holdings_ext.call_number_prefix || ' ' || holdings_ext.call_number || ' ' || holdings_ext.call_number_suffix)
		WHEN holdings_ext.call_number_prefix IS NOT NULL AND holdings_ext.call_number_suffix IS NULL THEN quote_ident(holdings_ext.call_number_prefix || ' ' || holdings_ext.call_number)
		WHEN holdings_ext.call_number_prefix IS NULL AND holdings_ext.call_number_suffix IS NOT NULL THEN quote_ident(holdings_ext.call_number || ' ' || holdings_ext.call_number_suffix)
		ELSE quote_ident(holdings_ext.call_number)
		END AS call_number,
		instance_ext.discovery_suppress AS instance_suppress,
		holdings_ext.discovery_suppress AS holdings_suppress,
		CASE WHEN contribs.contributor_is_primary = 't' THEN quote_ident(contribs.contributor_name) ELSE '' END AS author,
		RANK() OVER (PARTITION BY holdings_ext.hrid ORDER BY contribs.contributor_is_primary) AS author_rank,
		quote_ident(instance_ext.index_title) AS title,
		fd_instance_ext.mode_of_issuance_name AS mode_of_issuance,
		fd_instance_ext.type_name AS resource_type,
		formats.instance_format_name AS format,
		CASE WHEN idents.identifier_type_name = 'ISBN' AND fd_instance_ext.mode_of_issuance_name = 'serial' THEN idents.identifier
		WHEN idents.identifier_type_name = 'ISSN_Invalid' AND fd_instance_ext.mode_of_issuance_name = 'serial' THEN idents.identifier
		WHEN idents.identifier_type_name = 'ISSN' AND fd_instance_ext.mode_of_issuance_name = 'serial' THEN idents.identifier
		WHEN idents.identifier_type_name = 'OCLC' AND fd_instance_ext.mode_of_issuance_name != 'serial' THEN idents.identifier
		ELSE ''
		END AS identifier,
		CASE WHEN lan.language_ordinality = 1 THEN lan.instance_language
		ELSE ''
		END AS language,
		stat_codes.statistical_code_name AS stat_code,
		pubs.date_of_publication AS begin_pub_date,
		RANK() OVER (PARTITION BY holdings_ext.hrid ORDER BY pubs.date_of_publication) AS pub_rank,
		CAST(to_timestamp(fd_instance_ext.record_created_date::text,'YYYY-MM-DD') AT TIME ZONE 'America/Chicago' AS DATE) AS create_date,
		CAST(to_timestamp(fd_instance_ext.updated_date::text,'YYYY-MM-DD') AT TIME ZONE 'America/Chicago' AS DATE) AS update_date
	FROM folio_inventory.holdings_record__t__ holdings_ext
		INNER JOIN folio_inventory.instance__t__ instance_ext ON holdings_ext.instance_id = instance_ext.id
		INNER JOIN folio_derived.holdings_ext fd_holdings_ext ON holdings_ext.instance_id = fd_holdings_ext.instance_id
		INNER JOIN folio_derived.instance_ext fd_instance_ext ON holdings_ext.instance_id = fd_instance_ext.instance_id
		LEFT JOIN folio_derived.instance_contributors contribs ON holdings_ext.instance_id = contribs.instance_id
		LEFT JOIN folio_derived.instance_publication pubs ON holdings_ext.instance_id = pubs.instance_id
		LEFT JOIN folio_derived.instance_identifiers idents ON holdings_ext.instance_id = idents.instance_id
		LEFT JOIN folio_derived.instance_formats formats ON holdings_ext.instance_id = formats.instance_id
		LEFT JOIN folio_derived.instance_languages lan ON holdings_ext.instance_id = lan.instance_id
		LEFT JOIN folio_derived.instance_statistical_codes stat_codes ON holdings_ext.instance_id = stat_codes.instance_id
		LEFT JOIN folio_derived.holdings_statements hold_state ON holdings_ext.hrid = hold_state.holdings_hrid
		LEFT JOIN folio_derived.items_holdings_instances item_hold_in ON holdings_ext.instance_id = item_hold_in.instance_id
		LEFT JOIN item_detail item_detail ON holdings_ext.id = item_detail.holdings_id
		LEFT JOIN folio_inventory.location__t__  publoc ON holdings_ext.permanent_location_id = publoc.id
		LEFT JOIN folio_inventory.loclibrary__t__ publib ON publoc.library_id = publib.id
	WHERE TRUE
		AND publib.name IN ('MSL: Bryan')
		AND lan.instance_language IN ('aar')
		AND lan.language_ordinality = 1
		AND stat_codes.statistical_code_type_name = 'batch_id'
		AND stat_codes.statistical_code_name = 'AADR'
		AND fd_instance_ext.mode_of_issuance_name = 'single unit'
		AND instance_ext.discovery_suppress = true
		AND holdings_ext.discovery_suppress = true
		AND cast(to_timestamp(fd_instance_ext.record_created_date::text,'YYYY-MM-DD') AT TIME ZONE 'America/Chicago' AS DATE) >= to_date('2025-08-01', 'YYYY-MM-DD')
		AND cast(to_timestamp(fd_instance_ext.record_created_date::text,'YYYY-MM-DD') AT TIME ZONE 'America/Chicago' AS DATE) <= to_date('2025-08-07', 'YYYY-MM-DD')
		AND cast(to_timestamp(fd_instance_ext.updated_date::text,'YYYY-MM-DD') AT TIME ZONE 'America/Chicago' AS DATE) >= to_date('2025-08-01', 'YYYY-MM-DD')
		AND cast(to_timestamp(fd_instance_ext.updated_date::text,'YYYY-MM-DD') AT TIME ZONE 'America/Chicago' AS DATE) <= to_date('2025-08-07', 'YYYY-MM-DD')
) AS superset
WHERE pub_rank = 1 AND author_rank = 1
ORDER BY instance_hrid, holdings_hrid, author desc, identifier desc, begin_pub_date desc, format

2025-08-08T02:54:46.162Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.d.DatabaseConnectionDelegate     : Connect MetaDB [] started
2025-08-08T02:54:46.162Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.d.DatabaseConnectionDelegate     : Connect MetaDB finished in 0.010035 milliseconds
2025-08-08T02:54:46.175Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.delegate.DatabaseQueryDelegate   : Query Shelflist (holdings level) [] started
2025-08-08T02:54:55.406Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.delegate.DatabaseQueryDelegate   : Writing results to file as CSV including headers
2025-08-08T02:54:55.407Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.delegate.DatabaseQueryDelegate   : Query Shelflist (holdings level) did not specify output variable for result count
2025-08-08T02:54:55.408Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.delegate.DatabaseQueryDelegate   : Query Shelflist (holdings level) finished in 9232.175252 milliseconds
2025-08-08T02:54:55.420Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.d.DatabaseDisconnectDelegate     : Disconnect MetaDB [] started
2025-08-08T02:54:55.421Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.d.DatabaseDisconnectDelegate     : Disconnect MetaDB finished in 0.007082 milliseconds
2025-08-08T02:54:55.433Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.delegate.CompressFileDelegate    : Zip Shelflist (holdings level) report [] started
2025-08-08T02:54:55.435Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.delegate.CompressFileDelegate    : Destination: /mnt/workflows/tamu/shelflist-holdings/shelflist-holdings-report-1754621686096.zip
2025-08-08T02:54:55.435Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.delegate.CompressFileDelegate    : Source: /mnt/workflows/tamu/shelflist-holdings/shelflist-holdings-report-1754621686096.csv
2025-08-08T02:54:55.435Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.delegate.CompressFileDelegate    : Compress format: ZIP
2025-08-08T02:54:55.438Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.r.c.delegate.CompressFileDelegate    : Zip Shelflist (holdings level) report finished in 4.939267 milliseconds
2025-08-08T02:54:55.453Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.rest.camunda.delegate.FileDelegate   : Delete Shelftlist (holdings level) Report file [DELETE] started
2025-08-08T02:54:55.455Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.rest.camunda.delegate.FileDelegate   : /mnt/workflows/tamu/shelflist-holdings/shelflist-holdings-report-1754621686096.csv has been deleted
2025-08-08T02:54:55.455Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.rest.camunda.delegate.FileDelegate   : Delete Shelftlist (holdings level) Report file finished in 1.844457 milliseconds
2025-08-08T02:54:55.465Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.rest.camunda.delegate.EmailDelegate  : Send Shelflist (holdings level) Report Email [] started
2025-08-08T02:54:55.469Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.rest.camunda.delegate.EmailDelegate  : No attachment required
2025-08-08T02:54:55.527Z  INFO 1 --- [mod-camunda] [aTaskExecutor-3] o.f.rest.camunda.delegate.EmailDelegate  : Send Shelflist (holdings level) Report Email finished in 61.381887 milliseconds