Qty Differences Between Stock Ledger and Stock Status Query

A2000 ONLINE SUPPORT DEPARTMENT

KNOWLEDGE BASE

DIFFERENCE IN STOCK LEDGER & STOCK STATUS QUERY

DESCRIPTION         Why is there a difference in quantity balance in the Stock Ledger (Stock Card), compared to what we see in the drop-down boxes in Sales & Purchase Transactions & Stock Status Query and the also the quantity in Warehouse Stock Report?

1. WHICH STOCK BALANCE IS MOST ACCURATE?

To understand why there is a difference, we first need to know how the numbers are derived within the system.

A) The stock balances in Stock Status Query, Warehouse Stock and in the drop-downs when entering any sales/purchase transactions, are picked up from a STATIC Quantity Balance stored in the product and warehouse stock tables. This static balance is referred to as “quick-reference quantities”. Note that these are not sensitive to dates, and always reported “as-at-today” 

B) The Stock Ledger (or Stock Card) quantities are derived from the stock transaction table. When reporting the final stock balance, it computes the Opening Stock, Incoming and Outgoing transactions. 

NOTE: The Stock Ledger/Valuations can be reported as on any date (even in back-dated report), as it extracts directly from the actual transaction details table. As such, the stock ledger is always the most accurate report.

2.  WHY NEED TO USE QUICK REFERENCE QUANTITIES?

In a transaction entry (sales or purchase), the system needs to show user the available quantities to sell or buy quickly. The system cannot perform detailed queries (like Stock Ledger) at each transaction line. The response at the transaction level will be too slow. 

As such, the system uses a compromise value, or a “quick reference quantities”. This is a field constantly updated whenever an item is transacted. The value increases when stocks are received & decreases when stocks are depleted. This quantity balance shown is always “as at-today” as it is a static field updated in real-time.

This will result in very fast search whenever a transaction is carried out.

3.  WHAT ARE ISSUES WITH QUICK REFERENCE QUANTITIES?

3.1 CANNOT BE USED TO CHECK BACK-DATED STOCK BALANCES

Quick reference value is not to be used for checking back/post-dated balances. 

Example: User has a product that shows 100 pieces today. When they edit an invoice dated 1 month back, the stock status shown will still show 100 pieces (even though they may have zero stock 1 month ago). 

 NOTE: It is unsuitable to check stock balances on a back/post-dated basis, as it will always show current date’s status. To do that the only place is to user Stock Valuation or Stock ledger reports.

3.2 INACCURATE BALANCES WITH STOCK LEDGER REPORT

At times, users may find the stock balances between the stock ledger (the most accurate) and the quick-reference values, are not tied. These are caused by:

A) Failure to Complete an Update – because the system only updates the “quick reference value” when the transaction line item is accepted; i.e. the user saved line item. After the balances are updated, instantaneously someone else looking at this product will see the revised balance. The problem arises when they did not save the change, and closes the transaction panel (or at times the entire web browser).

B) Back/Post-Dated Transaction Entry – when user enters a post-dated transaction (say 10 pieces at a date 1 week later), the quick reference quantity is updated as at today. When user checks the Stock Ledger/Valuation Report, they may see a quantity of 100 pieces today. However, the post-dated transaction already reduced the quick reference field to 90 pieces, resulting in a discrepancy of 10 pieces. 

C) Imported Master Records & Transactions – it is possible that product master and sales orders are updated via Excel files and re-imported back to the system. It could also be 3rd party insertion through our web services like e-commerce transactions. When such event(s) happen, the stocks may go out of sync with the stock ledger. They need to go through a Recalculate Product to refresh the balances .

 REMEDY: Above (except para B) are easily fixed by running the Recalculate Product Cost, and it will refresh the static balance for the items you recalculate.

4.  ARE THESE DUE TO SOFTWARE BUG/ISSUE?

No, absolutely not. We advocate industry best practices as default in our implementation, but understand the needs of small/medium companies to have flexibility. 

Users have a choice to disallow post/back-dated entries and/or edit of past transactions, which will eradicate these issues. However operational flexibility will be affected. If users need this operational ease, then they have to accept small compromises to using the system; i.e. perform recalculation of product once a while to sync their stock balances. The system can be configured to remove such flexibility (for industry best practice) if need be.

*END*

Disclaimer: The information provided here is provided on an as-is basis without warranty of any kind, either expressed or implied, including warranties of merchantability and fitness for a particular purpose. In no events shall A2000 Solutions Pte Ltd or its agents, distributors and suppliers (collectively known as A2000) be liable for any damages whatsoever including direct, indirect, incidental, consequential, loss of business profits or special damages, even if A2000 have been advised of the possibility of such damages. The names of actual companies and products mentioned herein may be the trademarks of their respective owners. All third party trademarks are the property of their respective owners.