Version 5.4.1 report changes
It was necessary to make some changes to Financial Reports to ensure that Double entry Financial Reports had similar functionality to that already available for Single entry reporting.
The main changes were:
- To add Transaction Owing as a Where parameter in suitable Financial and Borrower Financial Report Types.
- Also there is now a Where parameter in Financial reports to allow a report for just the CR (Credit) or DB (Debit) transactions in suitable Financial Report Types
- There is a new Report type available to update the Balance from the month before, to report on the updated Balance from the new month. For example, new Debits and Credits for the current month added to the Balance brought forward from the previous month – based on Location.
Note: Any blue parameters are special parameters that need to be used in conjunction with other where parameters. For example Transaction owing
Borrower reports
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:
- Launch the Amlib client
- Go to Main > Reports > RepAddNew – the Report Files screen will display:
- From the Report Entity drop-down, select the appropriate module for your report – for example: Borrower
- Find the Borrower Purge report
- Highlight and click the F2 Modify button - The Borrower – Modify Report File will display with the settings for the Purge report
- Ensure the Report Type (shown in Red is Borrower (purge) Financial)
- If not, click the Drop-down arrow to find it
- Click F3 Update
- 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