Best Practices
-
Not everything is a back-end data fix (one thing I stress with customer-account problems/errors, just re-save the customer, bill-to and site to see if that fixes the issue)
-
Ensure backup tables are done by following these 3 steps:
SELECT
SELECT * INTO
UPDATE or DELETE
-
Have one server open per Management Studio at a time: sometimes you might have a Production server open and a Sandbox server open, and think you’re selecting their company database, make a bunch of updates, turned out you were in Sandbox… or visa-versa, you want to do a test in Sandbox, and accidentally made changes to their Production (fair warning!!!)
-
Never touch Closed Accounting Periods without customers’ written approval: best practice is to Email to confirm: “Just checking with you first, January 2020 is closed; do you still want us to update this Register Number’s imbalance?”) – just because an update would “fix” the problem, does not mean they haven’t already reported dollars for that GL account to their shareholders or government agencies (in some cases we negate the entry, then re-introduce it in the current period after discussing with the customer so the net effect does not occur back in Jan 2020, but in the current period instead)
-
Mass updates (don’t be afraid to get a refreshed Sandbox copy and have them approve changes before making changes on Production) one such example is with Deferred Revenue discrepancies we use a mass-update script to fix hundreds of invoices (since it’s a mass amount it is always a good practice to do everything first in a sandbox database that way we know the update scripts work as intended)
-
Always zero out dollar amounts, never delete GL: Reason is “Amount_Nat” holds the original value in case you ever need it back, it will be there then all you need to do is target the specific Id fieldà Ex: UPDATE GL_Register SET Amount = Amount_Nat WHERE Register_ID = xyz
Data edits can affect the general ledger even when you are not updating the GL directly. For example, if you add entries to the deferred revenue table without also adjusting the associated gl entries it will throw off the deferred revenue subledger. If you are unsure how your data work may affect GL reporting, ask before making the changes. Another helpful tool is to run a sandbox test and run reports before and after the data work to determine if a discrepancy has been created. Which reports you run will depend on what you are working on. The first report to run is usually the Trial Balance. If you are working on:
- Customer invoices/credits/payments - compare the Trial Balance to the AR Aging by Effective Date report.
- Deferred Revenue and/or cycle invoices that hit deferred revenue - compare the Trial Balance to the Deferred Revenue Summary Audit.
- Vendor bills/credits/payments - compare the Trial Balance to the AP Retroactive Aging Report.
- Inventory Parts/Warehouses – Compare the Trial Balance to the Stock Status report.
- Inventory Receipts – Compare the Trial Balance to the Open Receipts Report
- Payment Batches and Deposits – Compare the Trial Balance for today to the current total that shows at the bottom of the payments screen. Note – you can only reconcile this as of the current date. It cannot be reconciled for a past date.
- Interbranch AR
- Interbranch AP
- Inventory Transfer
- Customer Refunds – note, this is the customer refunds account used by the system for processing check and eft refunds. Customers sometimes have additional gl accounts with a similar name which carry a balance.
How to be self-sufficient in your research
-
How to filter out tables and research finding columns from tables as needed (there are scripts that can do this, or manual filter-out for table names and “guess” and work with JOINS)
-
Profiling to find out in Dev1/Dev2 FIRST instead of on the customer’s machines: Nicole/Yvonne can take queries from profiled on Dev1 or Dev2 (depending if the customer is on 5.7 or 6.1) and alter the WHERE clause to then run it on the customer’s SQL server to find data error without needing to bug customer for a LiveAssist to profile it
-
OR… profile on the SQL Server itself, no need to bug the customer
Common L2 Support Cases
General Issues:
-
Orphaned records
-
Account showing off balance by one cent
-
Overapplied, or applied deposit more than once
-
Conversion requests that require Sandbox testing
-
Update rate increase dates
-
Anything mentioning “SQL” in the subject header
-
Parts Reconcile for Service Tickets
-
Information not appearing, i.e. “form shows up blank or partially blank”
-
Slowness in specific modules / Performance issues
-
Questions about SQL / Queries
-
Error or warning messages:
-
Deadlocks
-
“Statement Terminated”
-
Run-time 5
-
Invalid Procedure/Call
-
Type mismatch
-
“Switch-To/Retry/Cancel”
-
Key is not unique / any mention of “Array” or “Index”
-
Log Full --> if SedonaCloud, goes to Cloud/IT Services; if on-prem, goes to L2
-
SQL Server Jobs (or SQL Server Jobs Agent)
GL issues
(**All GL issues should be marked critical, we can downgrade if need be):
-
GL oob (or GL out of balance)
-
Balance Sheet
-
Income Statement
-
Trial Balance
-
Credits Not Match Debits
-
Re-open Accounting Periods
-
“GL $$ values don’t tie out to ____” (to such and such a report)
-
Deferred Revenue imbalances
Accounts Receivable issues
(**All AR issues should be marked critical, if not, we can downgrade, but here are examples that go to Data Support):
-
Cannot post our cycle invoicing batch
-
Inputted wrong (date/terms/memo/etc) on our last cycle invoice batch
-
Bank Reconciliation
-
Credit Memos that start with a “-214”
-
Over/Under-applied Credits, Advance Deposits, Unapplied Cash
-
Balance Net Due Incorrect at top of Customer Record
-
Z-Transaction Discrepancies (questions about them go to L1)
Accounts Payable issues:
-
Automation error when opening Vendor Record
-
Purchase Order
Inventory issues:
-
Inventory Stock not matching GL
-
Inventory/Warehouse is “locked”, cannot proceed with counts or release physical
-
Values at top of Part-Warehouse Not Correct
-
Serialized Parts Issues (Integrated Security Systems is a common customer you may see)
Job Management issues:
Report Manager issues:
-
Excel (more than likely a project Wayne O’Keefe made for them in years past) - if not Data Support, then we go to Professional Services
-
Errors with reports
-
“Key is not unique”
-
Open Receipt Report (common request)
