Scenario 

 

When there are too many data in the database after few years. Some of the user would request for database purging in order to remove previous year data and have a new financial period in system

 

Solution 

 

  1. Check Financial Period, for example this database is start from 1/1/2011 to 31/12/2011

  1. To proceed year end, go to Tools > Database Utility > Year End Processing


  1. System will request to perform backup first.

Note: How to Backup

https://support.qne.com.my/support/solutions/articles/81000383845-how-to-backup-optimum- 

  1. Click Continue the year end step after backup.


  1. Tick done for Step 1 Backup.

  1. For step 2, system requets to restore the database. This database will keep as a “Source database”. It allows you to perform a migration of the outstanding transaction later at step 4. Besides, you can name it as “Original Database” whicg allow you to double check or compare the data before year end and after year end 

Note: How to restore database
 https://support.qne.com.my/support/solutions/articles/81000391002-how-to-restore-database

  1. Tick Done for Step 2 Restore the backup database.
     Continue for step 3 to perform Clear transactions. Set the cutoff date you which to begin. The sample below cutoff date as 1/1/2021. Means system will clear the database from 1/1/2011 to 3112/2020

  1. Click Purge Data.

  1. System will prompt message as below if there is any document yet to knockoff

You can check below reports, these reports show that those Receive Payments / Pay bills / Credit Notes / Journal Vouchers have been created but yet to knockoff, system will prompt a reminder you to complete it first

You can find the report from:
View > Reports > Supplier Reports > Master Reports > Supplier Open Credit Listing
 View > Reports > Customer Reports > Master Reports > Customer Open Credit Listing

Click Yes if you want to continue


  1. Next, system will prompt remind message if there is any outstanding document in Purchase, Sales and Stock Module transactions. Transactions can define as outstanding:

Purchase Requisition, Purchase Order, Goods Received Notes
Quotation, Sales Order, Delivery Order
 Stock Outs

If the above transactions created but without full transfer, and the status is still open system will prompt message as below

Example if you have created Purchase Requisition but not yet transferred to Purchase Order due to some reason, system will still remain as outstanding.

Thus, you need to close these documents.

Refer to this link on how to close the Outstanding document. https://support.qne.com.my/support/solutions/articles/81000387776-sales-and-purchase-module-one-by-one-document-mark-as-close- and-mark-as-close-by-batch  

Note: System will check the outstanding transaction within the purging period

It is advised you can check below documents to find out the outstanding reports and then close it so that system will not keep unnecessary documents in the new database  
View > Reports > Purchase Reports > Outstanding Purchase Requisition
View > Reports > Purchase Reports > Outstanding Purchase Order
View > Reports > Purchase Reports > Outstanding Goods Received
View > Reports > Sales Reports > Outstanding Quotation
View > Reports > Sales Reports > Outstanding Sales Order
View > Reports > Sales Reports > Outstanding Delivery Order
 View > Reports > Stock Reports > Outstanding Stock Outs

Notes Click No if there is no any outstanding document and you want to further proceed

               


  1. Click OK when it is completed.

  1. Check the financial period, system will start from 1/1/2021 now. All transactions before 2021 has been removed.

 

 

 

 

 

 

 

  1. Continue the year end step for step no. 4 to migrate the opening balance & outstanding documents

  1. Migrate those outstanding document to the system > Click on Optimum


  1. Search the database as the working database. The database which you perform for year end

  1. Then, choose the source of database which we restore at step 6 just now.  

  1. Tick the table you would like to migrate. Click Import

From here you can see, system allow you to migrate

  • GL opening balance
  • AP opening balance
  • AR opening balance
  • Stock opening balance
  • Bank Reconciliation opening balance 
  • Month End Stock Value 
  • Outstanding Purchase Requisition, Outstanding Purchase Order, Outstanding Goods Receive Notes
  • Outstanding Quotation, Outstanding Sales Order, Outstanding Delivery Order
  • Outstanding Stock Outs 

 System will display as Complete if the import is successfully > Click Finish and Close all the screens


  1. Go back to the login screen of working database

Click Ctrl + U, system prompt below message, click YES

  1. System will update something you can continue to login the system
  2. If you search Purchase Requisition / Purchase Order / Goods Received Notes / Quotation / Sales Order / Delivery Order, those outstanding transactions will be migrated and it will display as “Past Entries” in the transactions.

 

21. The year end processing is completed. Next, you can continue to compare below report with using the working database and source (original) database. The purpose of this checking to ensure the result are tally after year end. 

  • GL Opening Balance  
  • Balance Sheeting report as at the year end closing date.  For this scenario, it will be 31/12/2020  
  • Customer Aging report as at the year end closing date.  For this scenario, it will be 31/12/2020  
  • Supplier Aging report as at the year end closing date.  For this scenario, it will be 31/12/2020  
  • Month End Stock by Location as at the year end closing date. (If you do not use Location, you can view Month End Stock Report) For this scenario, it will be 31/12/2020  
  • Outstanding Purchase Requisition
  • Outstanding Purchase Order
  • Outstanding Goods Receive Notes
  • Outstanding Quotation 
  • Outstanding Sales Order 
  • Outstanding Delivery Order 
  • Outstanding Stock Outs