This script is based off adjusting one that was posted on Discord. Part of it was vibe coded and although it runs, IT HAS NOT BEEN FULLY TESTED. It attempts to show payments made for items not owned by your branch. Importantly, it includes assigned branch information for items that have been fully deleted* from the system. Given that it has to go through TransactionDetails twice, it will take some time to run.
Feel free to make any suggested edits or report positive/negative results.
*Note: This assumes that your system is configured to record transactions for the transaction type 3007 item record deleted.
/*
========================================================================================================================
Report: Reconciliation for Lost/Damaged Items
Description: This query calculates the total payments made by one library branch to another for items that were
lost or damaged. It identifies payments for specific fees (replacement, damaged, processing) and
correctly attributes the item to its original owning branch, even if the item has since been deleted.
========================================================================================================================
*/
-- Step 1: Declare and set the report parameters.
DECLARE @BeginDate DATETIME = '2023-01-01';
DECLARE @EndDate DATETIME = '2023-12-31';
DECLARE @OrganizationList NVARCHAR(MAX) = '0'; -- Use '0' for all branches, or a comma-separated list like '1,4,5'.
DECLARE @MinimumAmount MONEY = 10.00;
------------------------------------------------------------------------------------------------------------------------
-- Use Common Table Expressions (CTEs) to build the data in logical steps.
WITH
-- CTE 1: Determine which library branches to include in the report based on the @OrganizationList parameter.
Branches AS (
SELECT OrganizationID
FROM Polaris.Polaris.Organizations
WHERE
@OrganizationList = N'0' AND OrganizationCodeID = 3 -- OrganizationCodeID 3 represents a branch.
UNION ALL
SELECT OrganizationID
FROM Polaris.Polaris.Organizations
WHERE
@OrganizationList <> N'0' AND OrganizationID IN (SELECT value FROM STRING_SPLIT(@OrganizationList, ','))
),
-- CTE 2: Find the definitive "Assigned Branch ID" for items that have been explicitly deleted from the system.
-- This is based on 'Item record deleted' transactions (Type 3007).
DeletedItemBranches AS (
SELECT
td_item.numValue AS ItemRecordID,
td_branch.numValue AS AssignedBranchID
FROM PolarisTransactions.Polaris.TransactionHeaders AS th
INNER JOIN PolarisTransactions.Polaris.TransactionDetails AS td_item
ON th.TransactionID = td_item.TransactionID AND td_item.TransactionSubTypeID = 38 -- ItemRecordID
INNER JOIN PolarisTransactions.Polaris.TransactionDetails AS td_branch
ON th.TransactionID = td_branch.TransactionID AND td_branch.TransactionSubTypeID = 58 -- Assigned Branch ID
WHERE
th.TransactionTypeID = 3007 -- Transaction Type for 'Item record deleted'
),
-- CTE 3: Gather the primary payment transactions and link them to the correct item and branch.
TransactionData AS (
SELECT
th.OrganizationID,
td_amount.numValue / 1000.0 AS Amount,
-- Determine the item's home branch. Prioritize the branch from the 'deleted' transaction (CTE 2).
-- If not found, fall back to the branch from the payment transaction itself (SubType 125).
COALESCE(dib.AssignedBranchID, td_item_branch.numValue) AS ItemBranchID
FROM PolarisTransactions.Polaris.TransactionHeaders AS th
-- Join TransactionDetails to get specific pieces of information about the payment.
INNER JOIN PolarisTransactions.Polaris.TransactionDetails AS td_fee
ON th.TransactionID = td_fee.TransactionID AND td_fee.TransactionSubTypeID = 8 -- Fee reason code ID
INNER JOIN PolarisTransactions.Polaris.TransactionDetails AS td_patron
ON th.TransactionID = td_patron.TransactionID AND td_patron.TransactionSubTypeID = 6 -- PatronID
INNER JOIN PolarisTransactions.Polaris.TransactionDetails AS td_amount
ON th.TransactionID = td_amount.TransactionID AND td_amount.TransactionSubTypeID = 141 -- Monetary amount
-- Join to get the item's assigned branch ID from the payment transaction itself as a fallback.
LEFT JOIN PolarisTransactions.Polaris.TransactionDetails AS td_item_branch
ON th.TransactionID = td_item_branch.TransactionID AND td_item_branch.TransactionSubTypeID = 125 -- Item assigned branch ID
-- Join other tables to find the ItemRecordID to link to the deleted item's branch.
-- *** FIX ***: Corrected table name to be fully qualified.
LEFT JOIN Polaris.Polaris.PatronAccount AS pa
ON td_patron.numValue = pa.PatronID
LEFT JOIN DeletedItemBranches AS dib -- Join our CTE for deleted items
ON pa.ItemRecordID = dib.ItemRecordID
WHERE
th.TransactionTypeID = 6016 -- Transaction Type for 'Patron accounting payment'
AND th.TranClientDate BETWEEN @BeginDate AND @EndDate
AND th.OrganizationID IN (SELECT OrganizationID FROM Branches) -- Filter for selected branches
AND td_fee.numValue IN ('-6', '-1', '5') -- Filter for Replacement, Damaged, or Processing fees
AND th.OrganizationID NOT IN (2) -- Exclude specific organizations
AND th.WorkstationID NOT IN (3) -- Exclude specific workstations
)
------------------------------------------------------------------------------------------------------------------------
-- Final Step: Aggregate the data, join to get branch names, and format the final output.
SELECT
td.OrganizationID,
paying_branch.Name AS OrganizationName,
td.ItemBranchID,
item_branch.Name AS ItemBranchName,
CAST(SUM(td.Amount) AS MONEY) AS TotalPayments
FROM
TransactionData AS td
INNER JOIN Polaris.Polaris.Organizations AS paying_branch
ON td.OrganizationID = paying_branch.OrganizationID
INNER JOIN Polaris.Polaris.Organizations AS item_branch
ON td.ItemBranchID = item_branch.OrganizationID
WHERE
-- Only include transactions where the paying branch is different from the item's home branch.
td.OrganizationID <> td.ItemBranchID
AND td.ItemBranchID IS NOT NULL
GROUP BY
td.OrganizationID,
paying_branch.Name,
td.ItemBranchID,
item_branch.Name
HAVING
-- Only show results where the total amount exceeds the minimum threshold.
SUM(td.Amount) > @MinimumAmount
ORDER BY
OrganizationName,
ItemBranchName;