Version 5.4.1 report changes

Borrower Financial (Report Type: Borrower (list) Financial)

There is now a Where parameter for Transaction Owing which is applicable to single and double entry.

This is useful as you can limit the transactions reported to just those for which there are still have outstanding amounts. For example show those Borrowers who owe more than $5.00 and list only the outstanding transactions for each of these Borrowers.

Example: Borrower Owing (&borfino.qrp) – Summary

The order can be by Borrower - Surname, Barcode for example:

The listings can vary according to the Report Template chosen.

Borrower Owing (&borfino.qrp) - Simple listing with amount owed, paid and remaining

Borrower Owing (&borfin.qrp) – Details of each Transaction still owing

Example: Borrower Owing by Financial Type (&bofinto.qrp)

Includes Financial Type so it is useful to sort the list by the Financial Type

Note: Dates relating to the Financial Table from the Borrower available in the Where parameters include:

• Transaction Date (Trans Date column)
• Date Updated (Date Modified column)
• Invoice/Receipt Date (Inv Date column)

Borrower Financial Purge (Report Type: Borrower (purge) Financial)

Corrected a problem in double entry where a partial payment credit transaction would not be marked for delete even though the linked debit had been fully paid off by a later credit and all transactions were before the cutoff date.

Ensure the template is set to be a Borrower (purge) Financial Report type. To check this:

1. Launch the Amlib client
2. Go to Main > Reports > RepAddNew – the Report Files screen will display:
3. From the Report Entity drop-down, select the appropriate module for your report – for example: Borrower
4. Find the Borrower Purge report
5. Highlight and click the F2 Modify button - The Borrower – Modify Report File will display with the settings for the Purge report
   
6. Ensure the Report Type (shown in Red is Borrower (purge) Financial)
7. If not, click the Drop-down arrow to find it
8. Click F3 Update
9. Click F3 Save

The purge is run differently depending on whether you are using Single or Double entry Accounting. In Double entry the report is run twice in update mode (Y in Set Update column, using F6 Print and run through RepStartSchedule) with a cutoff date in the F10 More parameters. The first run gives a Number that is entered into the F10 More parameters for the second run. 

In Single entry, the More parameters are not used and it only has to be run once in Update mode.

Circulation Transactions (Circulation Trans)- Hard coded Start and End Dates

Financial Transaction records for the Preceding Month

Templates (&CFINDET.QRP and &CFINDET.INI) – Debtors Report

This report displays each borrower with transactions for the Preceding Month and at the end of the Report there will be Totals for the Balances at the start of the Month and at the end of the month The Balance as at Start of Month for the grand total is derived by SQL as below –

“Select SUM(FIN_VALUE) - SUM(FIN_PAID) From FIN_TRANS Where FIN_DATE < Start date “

There Where search of the Report should have where the Orig Transaction Dates are within the targeted month OR the Latest Transactions Dates are within the targeted month – for example a report being run in August 2013 will focus on the Transactions done in July – Either Original or Latest Transaction dates.

• Payments received
• Value of Items returned
• Write offs
• New invoices raised
• Balance at start and end of Month
  • Totals for Period at the end of report which includes an amount for the Balance as at Start of Month.
  • The Balance as at Start of Month for the grand total is derived by SQL as below –

“Select SUM(FIN_VALUE) - SUM(FIN_PAID) From FIN_TRANS Where FIN_DATE < Start date “

Note: This report type has hard coded start and end dates:

• Start date is the first day for the previous month and
• End date is the start date for the current month.

Transactions are included in the report where the transaction date is greater than or equal to the start date and less than the end date. (Previously the transaction date checking against the end date was less than or equal).

Where parameters

Added Transaction Owing as a Where parameter. This parameter is applicable to both single and double entry.

• Transaction Owing NOT used: All transactions with a date entered or date modified between the start and end dates are included. These include BOTH credit and debit transactions.
• Transaction Owing IS used: Only debit transactions which meet the criteria will be included (Note: the balance figures could be incorrect as not all transactions for the month may be processed).
• Write Offs total previously only checked for a transaction type starting with WRITE such as WRITEOFF. For double entry this total now also includes amounts for the WAIVE transaction type.

Note: Within this Report there is no breakdown by location. This is not valid as a borrower may have had Financial Transactions at more than one location for the period of the report and this report adds the totals for each borrower. The totals should balance against the totals in the transactional reports and the location summary reports.

Financial - Borrower Owing (Financial - Borrower Owing)

Modified to ensure only debit transactions with an outstanding amount are output.

• For double entry the paid amount is calculated for payment(s) and added to the report output as for single entry. This means that existing report templates for single entry should also work correctly for double entry.

Example Where parameters:

Example Report (&finowe.qrp) – Report Type Borrower Owing

In RepAddNew, this style of Financial report has a Report Type of Borrower Owing

Any blue parameters are special parameters that need to be used in conjunction with other where parameters. With this Report Type there are 4 Special parameters :

Also &finOweE.qrp – (Report Type of Borrower Owing Email)

Example Where parameters: There is no need to use Borrower Use Email = Y as this is built into the reporting code.

Audit By Borrower(Borrower Fin. Trans. Audit)

• Added ‘Fin CR/DB’ as a where parameter which is only applicable to double entry and enables selection of Debits or Credits for reporting.
• There are new report templates for this report type (&FBFKAL.qrp and &FBFKCSV.qrp enables it to go to a text file and open in excel)
• Transactional reports. The totals should balance against the totals in the Debtors reports and the location summary reports:

&FBFKAL.QRP Print report – can be used to output Debits, Credits or all transactions depending on where parameters – for example, July 2013

Without the Fin CR/DB in the Where parameters both Credits and Debits appear in the same report. The listing is broken down by Location, Financial Type and Borrower showing each transaction.

However, it is possible to report on the Credits and Debits separately

The final page shows the Grand Totals from all Locations

For example: Credit for July balances the Payments received and Value for Items returned ($509.72 +

$7,209.13 = Credit of $7,718.85)

Debit for July from this report results in $7,789.15 the exact total of Debits as shown as New Invoices raised in the Debtors report shown below.

July’s totals of Debtor’s Report (&cfindet.qrp)

&FBFKCSV.QRP EXCEL report – as for the print report but in csv format.

See Appendix 1 for sending to Excel

Audit Summary - Location (Borrower Summary Fin. Trans. Location)

This is a new financial report type to enable the type of reporting as specified as a Balance Sheet. It is similar to the Audit Summary report with the addition of the transactions grouped by the Location where the transaction occurred. The transaction types are separated into credits and debits with separate totals.

Two new RepFinancial Templates are provided that can have this Report Type.

• &FBALLOC.QRP Print report
• &FBALOCX.QRP EXCEL report

Totals are also provided for the balance carried forward from last report, location, period the current report covers and the resulting totals including outstanding balance. The totals should balance against the totals in the transactional and debtor reports.

The initial balance figures are created by a once only report an end date set to the day before the start of the period to be reported. It must be Printed (F6) with Set Update column to Y.

There is a separate Guide on running this report to give more details of the procedure:

• Run once to get the balances correct up until the month you wish to begin running the Balance update for each month
• After the initial balance figures are updated the report can run each month updating the monthly balances

Once this has been updated, the Balance carried forward will appear in the next report.

&FBALOCX.QRP.qrp – sent to File and opened up in excel (comma delimited)

The Balance carried forward amounts are stored in the LIB_OPTIONS table at the DEFAULT location.

Note: It is important not to run the report more than once in Update mode as the Balance carried forward will be updated.

If there is a problem and the totals need to be reset, the Carried Forward Totals can be viewed or edited in Supervisor, Installation, Other for the DEFAULT location. For example, set back to the figures for last month if accidentally run incorrectly.

Parameter	Explanation	Example
Credit Balance Amount for Location Type Financial Reports	Amount paid in total for all locations to get carried forward	10.20
Credit GST Balance Amount for Location Type Financial Reports	Amount of GST in total included in the Amount paid to get carried forward	0.09
Credit Transaction Count for Location Type Financial Reports	The number of transactions included for the Credit amount carried forward	5
Debit Balance Amount for Location Type Financial Reports	Amount owing in total for all locations to get carried forward – Credit amount	35.00
Debit GST Balance Amount for Location Type Financial Reports	Amount of GST in total included in the Amount owing to get carried forward	1.18
Debit Transaction Count for Location Type Financial Reports	The number of transactions included for the Debit amount carried forward	16

These amounts are always retrieved at the start of the report and updated at the end if the report is being printed and Update is set to Y.

LO_CODE entry in LIB_OPTIONS

FIN_DB_AMOUNT Debit amount

FIN_CR_AMOUNT Credit amount

FIN_DB_COUNT Count of Debit Transactions

FIN_CR_COUNT Count of Credit Transactions

FIN_DB_GST GST Amount included in Debits

FIN_CR_GST GST Amount included in Credits

Example Balances after using &BFALLOC.qrp with Update mode set to Y – Up to May

After June

After July:

Audit Summary (Borrower Summary Fin. Trans. Audit)

Corrected a problem where the outstanding balance was not correct after running the report due to no transactions during the report period for a transaction type where transactions had occurred during an earlier period in the current year. This caused the year to date amounts for these transaction types to not be included and the balance to be incorrect. If this situation arises, there is now an additional line at the end of the report with a transaction type description of Types not included showing zero amounts for the report period and a total for any transaction types where no transactions occurred in the year to date area.

These Reports rely on getting the balance using an SQL Query and updating the Circ Financial Type summary totals. Most sites would not run these reports (as they do require an SQL query usually run by IT) and separate notes are to be requested from Customer Support if required.