2010-1Q: SIMPLer: Financial Database Lock


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.


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