Condition / Cause

This WIKI applies when the GL (financial information) is not in balance. The only valid case when this should occur is with starting balance numbers (since Cyrious SMS is not a full ledger).

This is usually caused by a bug in SMS or by missing/inconsistent data when the GL is being computer. The most common causes in SMS for this are:

  • Line Items missing from the database, usually from a Key Violation. (Reduced likelihood in SMS 8.91.)
  • A bug in SMS 8.6 when order level discounts are used. (Fixed in SMS 8.91.)
  • A bug in SMS 8.6 which may round incorrectly, leaving a $0.01 out of balance amount. (Fixed in SMS 8.91.)
  • An index error or other database error.
  • An issue with customer credits when orders are edited multiple times without closing and re-opening.
  • Other less common bugs in SMS.

Notes:

  • Existing users do not need to exit for these actions to be run.
  • Always backup first! Running a SQL query is a powerful tool and a misplaced comma can result in data loss.

Explanation of Process

SQL Queries are used to identify the timing of the Out of Balance (OOB). The repair mechanism will vary based on the actual issue.

Tier 2 Actions

  1. Confirm you have the correct Data folder by looking at the modified date of the files in the data folder.
  2. Backup the Database by
    • Right-clicking on the Data folder
    • Choosing Send To | Compressed File.
    • When complete, name the file “Data YYYY-MM-DD xx.zip”, as in “Data 2015-03-18 JB.zip” where “xx” are the technicians intials
    • Move the back up to the ..\Cyrious$\SMS\Tech Backups folder. Create the folder if it does not exist.
  3. Run Database Browser (DBBrowser.exe in the ..\Cyrious$\SMS\Utilities folder).
  4. Click on the SQL tab, then click on Advanced in the middle row of tabs.
  5. Modify and run the SQL below. You will need to adjust or confirm:
    • StartDate - The date to start the search for Out of Balances.
    • EndDate - The last date to search for Out of Balances. This is usually set well in to the future.

code format"SQL"

Query to find OOB Periods Define StartDate 1/1/2014 Define EndDate 3/1/2019

Select OrderID, CustomerID, AccountCode, Amount, Extract(Year from DateTime) Year1, Extract(Month from DateTime) Month1, Extract(Day from DateTime) Day1, Extract(Hour from DateTime) Hour1, Extract(Minute from DateTime) Minute1

From “GL Database”

where DateTime between “[StartDate]” and “[EndDate]”

; TempGL

select OrderID, CustomerID, sum(Amount) Amount, Year1, Month1, Day1, Hour1, Minute1

from TempGL

Group By Year1, Month1, Day1, Hour1, Minute1, OrderID, CustomerID

Having sum(Amount) not between -0.0005 and 0.0005

order by Year1 desc, Month1 desc, Day1 desc, Hour1 desc, Minute1 desc, OrderID

; TempGL2

select OrderID, CustomerID, Amount, cast(cast(month1 as char(2)) || “/” || cast(day1 as char(2)) || “/” || cast(year1 as char(4)) as Date) TheDate, cast(cast(hour1 as char(2)) || “:” || cast(minute1 as char(2)) as Time) TheTime

from TempGL2

</code>

  1. Look for Out of Balance conditions in the results. Note that you will see some entries with the same positive and negative values one minute apart. This occurs when the order is saving the GL and the time crosses the minute threshold. It does not represent an actual OOB condition and can be ignored.
  2. For each OOB condition, you will need to look at the specific order and/or customer to determine the source of the error. Common conditions are:
    • Order Level Discount not included or correct in the GL. Manually add or correct the entry in Mgmt | Accounting | GL Manual Entry.
    • Line Items Missing or Line Item Totals do not match Order Totals. Discuss with the customer. Correct Line Item Totals. On Save, correcting GL entries should be made. Move the new GL entries to the original date.
    • Other issues. Investigate and repair.
  3. Re-Run the OOB Query above. The OOB issues should no longer exist. If they do, repeat the above process and recheck your results.
  4. Close the Database Browser (DBBrowser).
  5. Discuss the changes with the customer.
    • If any GL entries were created for a previous period, the daily reports for that period must be re-printed.
    • If any GL entries were created for a previous period, the GL Export for accounting must be re-exported and corrected in the accounting software (QuickBooks).

Task Information

  • Last Updated: 2/15/2015
  • Created By: Michael Nolan
  • Verified By:
  • Applies to All Versions
You could leave a comment if you were logged in.