...
The daily_update_circ_demographics_metadb task for Metadb runs daily at 7am. This task runs a query called update_circsnapshot4_metadb.sql. The update_circ_snapshot4_metadb.sql query (in the LOCAL_AUTOMATED folder in DBeaver) runs the following code on Metadb, which uses the INSERT function to get the new checkouts and add them to the sm_local_core.circsnapshot4 table daily automatically. The sm_local_core.circsnapshot4 table is used in circulation queries to generate reports that need the PII data that is removed everyday from circulation data in the FOLIO system.
INSERT INTO local_core.sm_circ_snapshot4
SELECT
nowNOW() AS extract_date,
li.loan_id,
li.item_id,
li.barcode,
li.loan_date,
li.loan_return_date,
li.patron_group_name,
uu.custom_fields__department,
uu.custom_fields__college,
udu.department_code
FROM folio_reportingderived.loans_items AS li
LEFT JOIN userfolio_users.users__t AS uu
ON li.user_id = uu.id
LEFT JOIN folio_reportingderived.users_departments_unpacked AS udu
ON li.user_id = udu.user_id
WHERE
li.loan_id NOT IN
(SELECT cs4.loan_id
FROM local_core.sm_circ_snapshot4 AS cs4
)
AND li.user_id IS NOT NULL
;
Here is a screenshot of the local_core.circsnapshot4 table in the LDP.
...