2010-1Q: SIMPLer: Financial Database Lock
Overview
The purpose of the Financial Database Lock feature is to allow the operator to "lock" the finance portions of the database, up to a given date, to prevent any changes to the database which would affect financial totals prior to that date. Once locked, any actions which would affect the financial figures, such as adding/deleting/modifying invoices, applying credits notes (with dates prior to the lock), etc. will be blocked and an error message will be given.
Users with appropriate rights will be allowed to set the database lock date. Once set, the lock cannot be removed, and so caution should be exercised when setting the lock. A lock cannot be set for any date earlier than any previous lock, and cannot be set for a date later than "yesterday" - i.e. the new lock date must fall between any previous lock date and "yesterday's" date.
Setup
Only users who have rights to access the financial database lock will be able to update the lock date. Access to the lock date is controlled by a new "lock database" user right, set in the "users" tab - see Figure 1 below.
Fig1: Lock database user right
When a user with the "lock database" right goes to the "invoices" tab, a new section called "Financial Database Lock" will appear below the "Financial Figures" section (see Figure 2 below). This section will not appear for users who do not have the "lock database" user right set.
Fig 2: Finance Database Lock settings
The new section indicates if the database is locked (1), and if so, who set the lock and up to what date it is locked. The lock date can be updated by selecting a new date from the calendar (3) and clicking the "Set Database Lock Date" button (2). A warning will be issued, and if OK is clicked the new database lock date will be set. An error message will be displayed if there are any problems with the new date - for example if you try to set a date earlier than any existing lock, or a date into the future.
Once the date has been set, any actions on or prior to that date will be blocked.
[An audit trail is maintained in the database, so changes to the lock date may be tracked]
First / Recurring Invoices
If a data base lock is set, and an attempt is made to generate a "first" or "recurring" invoice with a date prior to the lock date, the invoice will not be generated and an error message will be displayed for that particular invoice - see Figures 3-5 below.
Fig 3: The "Generate Invoices" button
Fig 4: Choosing an invoice to generate
Fig 5: Invoice not generated
If this error is displayed for a "first" invoice, you can return to the "First Invoices>>" page and adjust the "Start Date" to be a date later than the database lock date (see Figures 6-7 below).
Fig 6: The "First Invoices" button
Fig 7: Adjusting the "Start Date"
For "recurring" invoices it would be necessary to add credit days to the customer's account in order to shift the next invoice date to be a date later than the database lock date (see Figures 8-11 below).
Fig 8: Choosing a customer to whose invoice credit days need to be added
Fig 9: "Modify" Billing Details under "Customer Billing Details"
Fig 10: Adding days to Credit days
Fig 11: Update Customer after making changes
Custom / External Invoices
Attempting to create a custom invoice, or enter an external invoice with a date prior to the lock date will result in an error message such as the one below (see Figures 12-14 below).
Fig 12: The "Create Custom Invoice" button
Fig 13: Creating a custom invoice
Fig 14: Custom/External invoice not generated
Credit Notes
Credit notes may be used to credit out invoices prior to the database lock date. However, the credit note date must be after the lock date. If not, the credit note will not be generated and the following error will be displayed (see Figures 15.1 - 15.5 below).
Fig 15.1: The "E" button under Last 5 Invoices
Fig 15.2: The "Modify" button under Invoice Credits
Fig 15.3: The "Add Credit" button
Fig 15.4: Adding a customer credit
Fig 15.5: Credit note not generated
Modify / Delete Invoice or Credit Note
If the date for an invoice or credit note are before the database lock date, the buttons to modify or delete the invoice/credit note will be disabled as shown in Figure 16 below.
Fig 16: Update/Delete buttons disabled
EFT / non-EFT payments
The processing date for EFT / non-EFT payments and batch credit card payments is verified, and if it is prior to the database lock date, the batch file is not processed and the following error messages is generated (see Figures 17-18.2 below).
Fig 17: The "Invoices to be EFT" and "Non-EFT Payments" buttons
Fig 18.1: Processing date and an invoice to be generated
Fig 18.2: EFT/non-EFT payments
Lodgement details
Modifications and deletions will not be allowed to lodgement details if the date of the lodgement is prior to the database lock date (see Figures 19-21 below) - the "Update Lodgement" button will be disabled, and the "Delete Lodgement" button is removed in this case.
Fig 19: The "Lodgements" button
Fig 20: Choosing a lodgement
Fig 20: The red fields cannot be changed due to the date
The payment amounts and payment status of individual payments made under a lodgement may not be updated if the lodgement date is prior to the database lock date. This is done by disabling the "Process" buttons on the page - a warning note at the end of the pay will indicate why the "Process" button has been disabled (see Figures 21-23 below).
Fig 21: The "Details" button
Fig 22: The payments to be processed
Fig 23: Modifications to the payments disabled
Lodge Pre-payments
By default when loading a pre-payment against an invoice, the date of the lodgement will be set to the date that the pre-payment was created. If this date is prior to the database lock date, the lodgement will be prevented. The lodgement date can then be adjusted to be a date later than the lock date, and the pre-payment can then be re-applied. Figure 24 below shows a sample of where an attempt was made to lodge two prepayments - one was successful and the other was blocked as the date was not valid.
Fig 24: Pre-payment lodgements
Add/Modify/Delete Credits or Pre-payments
If a Credit or Pre-payment is dated before the the database lock date, the "Update" and "Delete" buttons on the modify page will be disabled to prevent changes to the credit/pre-payment (see Figures 25-27 below).
Fig 25: The "Customer Prepayments" button
Fig 26: An invoice dated before the database lock date
Fig 27: Update/delete disabled
Likewise any attempt to change the date of a credit/pre-payment to be before the database lock date will result in the operation being blocked and an error message will be displayed (see Figures 28.1 - 28.4 below).
Fig 28.1: "All" in Last 5 Credits
Fig 28.2: Editing a credit
Fig 28.3: Changing the date and updating the credit
Fig 28.4: Modification of date blocked