Report for fine purges from a Sierra database, used the query below to produce a report in the same format as the Fines Paid function.
WITH
charge_type(charge_type_code, charge_type_desc) AS
(
VALUES
( '1', 'Manual Charge' ),
( '2', 'Overdue' ),
( '3', 'Replacement' ),
( '4', 'Adjustment' ),
( '5', 'Lost Book' ),
( '6', 'Overdue Renewed' ),
( '7', 'Rental' ),
( '8', 'Rental Adjustment' ),
( '9', 'Debit' ),
( 'a', 'Notice' ),
( 'b', 'Credit Card' ),
( 'p', 'Program' )
),
fine_creation_mode(fine_creation_mode_code, fine_creation_mode_desc) AS
(
VALUES
( 'a', 'Automatic' ),
( 'm', 'Manual' ),
( 'x', 'Adjustment' )
),
payment_status(payment_status_code, payment_status_desc) AS
(
VALUES
( '0', 'No Payment' ),
( '1', 'Full Payment' ),
( '2', 'Partial Payment' ),
( '3', 'Waive' ),
( '4', 'Item Busy' ),
( '5', 'Will Pay' ),
( '6', 'Purge' ),
( '7', 'Credit' ),
( '8', 'Adjustment' )
)
SELECT
to_char(fp.fine_assessed_date_gmt, 'MM/DD/YYYY') AS "Date Assessed",
TRIM(BOTH FROM pn.last_name || ', ' || pn.first_name || ' ' || pn.middle_name) AS "Patron Name",
pm.record_num AS "Patron Record",
fp.patron_record_metadata_id AS "Patron Unique ID",
fp.invoice_num AS "Invoice",
fp.item_charge_amt AS "Charge Amount",
fp.processing_fee_amt AS "Processing Fee",
fp.billing_fee_amt AS "Billing Fee",
ct.charge_type_desc AS "Charge Type",
fp.charge_location_code AS "Owning Location",
to_char(fp.paid_date_gmt, 'MM/DD/YYYY') AS "Date Paid",
fp.tty_num AS "Statistics Group",
fp.last_paid_amt AS "Last Payment",
fp.iii_user_name AS "Initials",
fcm.fine_creation_mode_desc AS "Creation Mode",
fp.description AS "Description",
fp.paid_now_amt AS "Amount Paid",
ps.payment_status_desc AS "Payment Status",
COALESCE(NULLIF(fp.payment_type_code, ''), 'null') AS "Payment Type",
fp.payment_note AS "Payment Note"
FROM
sierra_view.fines_paid AS fp
LEFT JOIN sierra_view.record_metadata AS pm
ON (
pm.id = fp.patron_record_metadata_id
)
LEFT JOIN sierra_view.patron_record_fullname AS pn
ON (
pn.patron_record_id = fp.patron_record_metadata_id
AND pn.display_order = 0
)
LEFT JOIN charge_type AS ct USING ( charge_type_code )
LEFT JOIN fine_creation_mode AS fcm USING ( fine_creation_mode_code )
LEFT JOIN payment_status AS ps USING ( payment_status_code )
;
Regards,
Jim Nicholls
-----------------
-----------------
For the query to work with multiple fine purges and when your fines_paid has more than just fine purge data, you need to add the following where clause:
WHERE
fp.payment_status_code = '6'
AND fp.paid_date_gmt >= DATE '2014-12-16' -- Purge date, YYYY-MM-DD
I’ve added the query to the IUG clearinghouse.
Regards,
Jim
No comments:
Post a Comment