bib deletion

-- Period used to determine how long call numbers and bibs without
-- copies stick around in the nontranscendental sources.
\set period '''30 days'''::INTERVAL

-- CALL NUMBERS SANS COPIES/URIS
WITH empty_acn AS (
SELECT DISTINCT acn.id
FROM asset.call_number acn
LEFT JOIN asset.copy acp ON acp.call_number = acn.id AND NOT acp.deleted
LEFT JOIN asset.uri_call_number_map aucnm ON aucnm.call_number = acn.id
WHERE acn.id > 0 AND acp IS NULL AND aucnm IS NULL AND NOT acn.deleted AND acn.create_date < NOW() - :period
)
DELETE FROM asset.call_number
WHERE id IN
(SELECT id
 FROM empty_acn);

-- BIBS WITH NO CALL NUMBERS
WITH empty_bib AS (
SELECT DISTINCT bre.id
FROM biblio.record_entry bre
LEFT JOIN asset.call_number acn ON acn.record = bre.id AND NOT acn.deleted
LEFT JOIN acq.lineitem acqli ON acqli.eg_bib_id = bre.id -- changed "acqli.record" to "acqli.eg_bib_id"
WHERE bre.id > 0 AND acn IS NULL
-- limit source so as to not delete e-resource records, which can be legitimately empty
AND (bre.source IS NULL OR bre.source IN (1, 2, 5))
AND NOT bre.deleted AND bre.create_date < NOW() - :period
AND acqli.state NOT IN ('pending','new')  -- changed "AND NOT" to "NOT IN"
)
DELETE FROM biblio.record_entry
WHERE id IN
(SELECT id
 FROM empty_bib);