What's the difference between the FINE_FEE and FINE_FEE_TRANSACTIONS tables?
- Article Type: General
- Product: Voyager
- Product Version: 6.5.3
Description:
What's the difference between the FINE_FEE and FINE_FEE_TRANSACTIONS tables?
Resolution:
The FINE_FEE table stores current (unpaid) fines.The FINE_FEE_TRANSACTIONS table stores fines that have been paid.
Additional Information:
When a fine is paid, the FINE_FEE_BALANCE goes to zero in the FINE_FEE table, but the record is not deleted from this table and the amount in FINE_FEE_AMOUNT is retained. So it is different than the CIRC_TRANS_ARCHIVE and CIRC_TRANSACTIONS tables. The FINE_FEE table keeps the initial fine and outstanding balance. The detail of the transaction archives are kept in the FINE_FEE_TRANSACTIONS file.
When the FINE_FEE_BALANCE in the FINE_FEE table goes to 0, its record is moved from the Outstanding Fines/Fees tab to the Fines/Fees History tab in the Circulation client.
The combined FINE_FEE_BALANCE data in the FINE_FEE table for a specific patron should be the same as that patron's TOTAL_FEES in the PATRON table.
The following is an example Prepackaged Access Reports SQL query that shows patrons' combined outstanding fines/fees, using the FINE_FEE table:
SELECT PATRON.LAST_NAME, PATRON.FIRST_NAME, PATRON.INSTITUTION_ID, Sum(CCur(Val([FINE_FEE_BALANCE]/100))) AS [Total Fines]
FROM FINE_FEE INNER JOIN PATRON ON FINE_FEE.PATRON_ID = PATRON.PATRON_ID
GROUP BY PATRON.LAST_NAME, PATRON.FIRST_NAME, PATRON.INSTITUTION_ID
HAVING (((Sum(CCur(Val([FINE_FEE_BALANCE]/100))))<>0));
Constructing custom SQL queries by request and troubleshooting unexpected results from customer-created SQL queries falls outside the scope of Support. The above has been posted for informational purposes. Voyager-L and Developer Network are useful resources for finding helpful custom SQL or obtaining assistance from peers in troubleshooting custom queries.
- Article last edited: 19-Oct-2020