Direct to SQL Integration - Sage 200 on prem

Question

Hey Everyone, 
 I've been looking at a site (Sage 200 on premise version 12.00) as a favour they are having issues with Sage financial reports not reconciling. The TB, Aged Creditors and VAT returns are in dispute. 
 I believe that a developer has written SQL directly to tables in their Purchase Ledger, firstly I suspect that this is the incorrect method? Could somebody confirm this? 
 Secondly is there any data integrity scripts we can run to highlight where this has caused issues in Sage? 
 Thirdly could anyone point me in the correct direction on how to integrate, I can't seem to find anything useful on Google. 
 
 Many thanks. 
 
 John

Graham Michel · Accepted Answer

Hi John, yes, as you suspect correctly, this is completely the wrong way to go about things as posting transactions to the ledgers rarely involves updating the few obvious tables, there are often many others to consider depending on the nature of the transaction. 
 The correct ways to integrate to S200, is via the standard Import routines within the Sales, Purchase and Nominal ledgers or via a multitude of Add-On's written by Developers that are members of the Developers program..This allows the Developer access to integrate with the Business Objects that both validate the posting as well as updating the ledgers and the multitude of associated tables to retain the financial integrity of the system. Note that this is is the only method that is supported by Sage. 
 In terms of Data integrity, your stating point should be to Validate Data (via Accounting System Manager > System Utilities > Verify Data > Financials (SL, PL, NL, CB) and Commercials (SOP, POP, Stock). In some cases, e.g SL & PL, you have the opportunity to report Imbalances before correcting rather than just Update which does not create a compensating transaction i.e you want to know what is wrong as you may want to investigate before correcting. NL & CB don't offer this facility - you can only update so you need to ensure you have a valid backup first. 
 You mention Aged Cr'editors, is your client taking Revaluations and/or Deferred postings into Account in any Reconciliation? 
 Have Journals been posted incorrectly to Debtors and Creditors Control Accounts? 
 Is the VAT Return being reconciled to the VAT Control or have postings been made to one and not the other? This re-enforces the absolute necessity of using the Developers Toolkit. 
 Finally, Version 12.00 is not the full picture, you need to know the variant i.e. 12.00.00nn which tells you which version it is and furthermore, if its still supported, here is a link to the currret article https://gb-kb.sage.com/portal/app/portlets/results/viewsolution.jsp?solutionid=210303143522330 
 Your client's linked Business Partner should be able to help you with specifics as you have introduced a wide range of areas to investigate. If you want to respond here with what exactly what your client is trying to achieve, I'll try and point you in the right direction. As mentioned, there are a numbers of excellent add-on's that can greatly simplify this - and do it in the proper way if the Imports can't be used.

Sage 200 UKI

Direct to SQL Integration - Sage 200 on prem

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING