Sage Line 50 ODBC driver outer joins

I've just discovered that you can do outer joins with the Sage Line 50 ODBC drivers. Happy days!

If you try and implement an outer join in MS Query it will use a syntax like:

FROM {oj AUDIT_JOURNAL AUDIT_JOURNAL LEFT OUTER JOIN SALES_LEDGER SALES_LEDGER ON AUDIT_JOURNAL.ACCOUNT_REF = SALES_LEDGER.ACCOUNT_REF}

Unfortunately this behaves like an inner join!  To get proper outer join functionality the easiest way I've found is to create an inner join and then, in the SQL that is created, add '(+)' to the end of the join criteria line as shown below:

FROM AUDIT_JOURNAL AUDIT_JOURNAL, SALES_LEDGER SALES_LEDGER 
WHERE AUDIT_JOURNAL.ACCOUNT_REF = SALES_LEDGER.ACCOUNT_REF(+) 

I always understood the (+) notation (used with Oracle databases) as "ALL values from 'AUDIT_JOURNAL' and all matching records from 'SALES_LEDGER' where AUDIT_JOURNAL.ACCOUNT_REF=SALES_LEDGER.ACCOUNT_REF PLUS NULL records where there is no match for AUDIT_JOURNAL.ACCOUNT_REF".

I'm learning every day!

P.S. If you use MS Access to link to Sage tables I've found that you can't identify any key fields and to get the above syntax to work the query has to be a pass-through query. If anyone finds differently perhaps you'd care to share via a comment? 

Opencart Company and VAT registration numbers added to invoice

The default invoice in Opencart 1.5.5.1does not record the company name, registered address, registered number and place of registration nor the VAT registration number.

A "quick and dirty" fix for this is to hard code the information into the file admin/view/template/sale/order_invoice.tpl as follows: 

immediately before the last </div> line in the file (four lines from the bottom) insert a line reading

<small><?php echo "[xxx]"; ?></small>

where [xxx] is the company and VAT related disclosures you wish to have on the bottom of your invoices.

It may not be pretty but it costs nothing and it works.

Sage year end processing

A common bugbear for Sage users is how to deal with "late" adjustments to the accounts. Should they defer running the Year End process until the audit report has been signed (impacting significantly on "normal" Sage based reporting for the new year) or run the Year End process and deal with any late adjustments manually (how do you process late adjustments anyway?)?  

This post outlines the mechanics of the Sage year end process and proposes a methodology that promotes robust reporting.



Sage analyses transactions to one of 14 balance fields in the NOMINAL_LEDGER table. The transaction date determines which balance field is updated by a transaction. If the transaction date is before the currently defined start of the financial year, the BALANCE_BF field is updated.  If the transaction date is after the currently defined financial year end, the BALANCE_FUTURE field is updated.  If the transaction date is within the currently defined financial year, the appropriate BALANCE_MTH[xx] field is updated. Every transaction also updates the BALANCE field, making it an aggregate of the other 14 balance fields.
  
What does Sage do when you run the year end process?

Firstly, the start and end points of the “current” financial year are incremented by one year.

For each nominal code, inter alia, the following processes seem to be carried out:

1. The balance as at the old financial year month 12 (BALANCE minus BALANCE_FUTURE) is placed in the BALANCE_BF and BALANCE fields.
2. All BALANCE_MTH[xx] fields are copied to the PRIOR_YR_MTH[xx] fields (flipping the sign on any balance where the nominal code is assigned to Categories 1, 7, 8 or 9) and then all BALANCE_MTH[xx] fields are cleared.
3. The BALANCE_FUTURE field is cleared and each future dated transaction from the old financial year is posted to the appropriate BALANCE_MTH[xx] or BALANCE_FUTURE field, also updating the BALANCE field. 
4. A “Ledger Year End” journal, dated the last day of the old financial year, is generated to cancel the P&L amounts (where the nominal code is assigned to Categories 1, 2, 3, 4 or 10) in the BALANCE_BF field with the balancing entry coded to Retained Earnings.  The date on this journal will cause it to post to the BALANCE_BF field. As usual, the BALANCE field is updated too.

In an ideal world that would be the end of it, but, as noted in the introduction, we’ve all come across situations where there are P&L transactions that we need to record after the year end process has been run. The following describes a robust methodology for doing this:

• Use Data Import of "Audit Trail transactions" to upload late journals to the appropriate accounts (if possible, no transactions should be dated on the last day – the last day should be reserved for “Ledger Year End” transactions (see following) so that Sage transaction based reports can isolate them) .  As the year end process has already been run we need to manually generate the equivalent “Ledger Year End” entries as part of the upload to clear the P&L balances to Retained Earnings.  All these journals will analyse to the BALANCE_BF field because the transaction date is prior to the current financial year.  This is the required outcome for the “Ledger Year End” entries but not for the entries that should have been analysed to the appropriate PRIOR_YR_MTH[xx] field as well as the BALANCE_BF field.  
• The balances on these fields can be corrected using Data Import of "Nominal accounts" (keeping in mind the reverse signage of Categories 1, 7, 8 and 9 mentioned above). If you prefer you can make adjustments manually in Modules | Nominal Ledger | Record.
• As always, backup, backup and backup!

What about Balance Sheet roll-ups at the year end?  For example, it is not uncommon for fixed assets costs to be recorded in separate opening balance, additions and disposals nominal codes. The best way to deal with these sorts of aggregations is to process “Ledger Year End” entries, after performing the Year End process, to move the amounts from the additions and disposals codes into the opening balance code ready for the next year.

A brief example may help with understanding:

Say I've already run my Year End for 2012 and the auditors insist on an accrual of £10,000 for Goods Received not Invoiced at the year end. I'd create a journal as Dr Purchases (5000) £10,000 Cr Accruals (2109) £10,000, dated 30th December 2012 (as previously explained), and then create a journal named as "Ledger Year End", with "Ledger Year End" also in the Details field, dated 31st December 2012, to mimic what Sage would have done with the first journal if it had been posted prior to the year end process being run. The "Ledger Year End" journal would be Dr P&L Reserves (3200) £10,0000 and Cr Purchases (5000) £10,000. After processing the journals, the prior year balance adjustments described above keep the prior year balances in line with the transactional data for each month (excluding the Ledger Year End transactions processed as 31 December 2012). Keeping all "normal" transactions away from 31 December 2012 allows you to run transactional reports to 30 December 2012 that agree with the balance based reports - only the Ledger Year End transactions not processed to the prior financial year balances are excluded.

Congratulations if you're still with me!

If you follow the guidance above you should be able to achieve the same robust reporting outcomes from either Sage transactional reports or Sage balance based reports.  Bear in mind that most Sage balance based reports do not report on anything other than the current and prior years.  The Onion product (see banner at the top of the page) allows the same robust transaction based reporting whether the Sage Year End process has been run or not.  Many find that using Onion removes the need to perform the Sage Year End process earlier than they would otherwise choose, just so they can start to report on the current year.

Sage treatment for invoices from suppliers who are not registered for VAT

The following is an excerpt from the TAX_CODE table for the demo company in a Sage Instant Accounts setup:

TAX_CODE	DESCRIPTION	TAX_RATE	VAT_INCLUDE	EURO_SALES	EURO_PURCHASE	RELATED_CODE	RELATED_RATE
T0	Zero rated	0	1
T1	Standard Rate	20	1
T2	Exempt transactions	0	1
T3		0	1
T4	Sales to customers in EC	0	1	1
T5	Lower rate	5	1
T6		0	1
T7	Zero rated purchases from suppliers in EC	0	1		1
T8	Standard rated purchases from suppliers in EC	0	1		1	1	20
T9	Non-Vatable Tax Code	0	0

The Value Added Tax Act 1994, 1994 c. 23, Part I Supply of goods or services in the United Kingdom, Section 4 contains the following:

Scope of VAT on taxable supplies.

(1) VAT shall be charged on any supply of goods or services made in the United Kingdom, where it is a taxable supply made by a taxable person in the course or furtherance of any business carried on by him.

(2) A taxable supply is a supply of goods or services made in the United Kingdom other than an exempt supply.

Part I, Imposition and rate of VAT, Section 3 of the same act defines a taxable person as follows:

Taxable persons and registration.

(1) A person is a taxable person for the purposes of this Act while he is, or is required to be, registered under this Act.

The HMRC guidance (http://www.hmrc.gov.uk/vat/forms-rates/rates/rates.htm#5) says (emphasis added): "Goods and services that are outside the scope of UK VAT includes anything you: sell (or otherwise supply) when you're not registered for VAT - and you don't need to be registered...".

So, if a supplier is not required to be registered for VAT, and has not done so voluntarily, they are not  a taxable person and any transactions undertaken are outside the scope of VAT on taxable supplies in the UK.

If goods/services are supplied "outside the scope" it follows that they are received "outside the scope". T9 is the code to use for supplies made "outside the scope" of VAT. It is the only code with a 0 in the VAT_INCLUDE column. The 0 means it is omitted from VAT returns altogether.

I've seen all sorts of other approaches that will get the correct net VAT amount (use T0; use T1 but zero out the VAT; use T2) even though the "stats" boxes will incorrectly include purchase amounts "outside the scope" of VAT.  The VAT inspectors may well be relaxed about this but why risk it when T9 will get the job done properly? 

In essence, both the status of the supplier and the nature of the supply determine whether something is VATable - not just the nature of the supply. It is a two stage test.

Possible types of	Sage T code	Sage T code
supply (ignoring	if supplier	if supplier is
supplier VAT reg status)	is VAT reg'd	not VAT reg'd
Standard	T1	T9
Reduced	T5	T9
Zero	T0	T9
Exempt	T2	T9
Outside Scope	T9	T9

DSN-less ODBC connection for Sage

Sometimes you would prefer to have a DSN-less ODBC connection to your Sage data.

In Excel 2003, if you execute the following vba snippet, you'll get a DSN-less connection to your Sage demonstration company and a QueryTable containing the company name of the data at that location.

ActiveSheet.QueryTables.Add( _
    Connection:="ODBC;" & _
        "Driver={Sage Line 50 v18};" & _
        "DIR=C:\ProgramData\Sage\Accounts\2012\DemoData\accdata", _
    Destination:=Range("A1"), _
    Sql:="select Name from Company").Refresh

You should edit the snippet where it is highlighted in yellow to reflect your Sage version (e.g. Sage 2010 = v16, Sage 2011 = v17, Sage 2012 = v18, Sage 2013 = v19, etc ...) 

Once the QueryTable is created, you can edit the datapath in the Connection Dialog to access other company data and edit the query to access other tables.

Happy browsing!

Sage ODBC schema

I've found this to be a very useful resource. Respect to Rick!

http://rickvause.com/post/23345383645/sage-line-50-odbc-schema#disqus_thread

Sage Charts of Accounts by Business Type

Maybe I'm missing something but I'd have thought it would have been relatively easy to see details of the various Charts of Accounts supplied as standard with Sage accounting products. I haven't been able to find any so I went through the process of taking a backup and then completely destroying my company data to load each of the twelve charts of accounts in succession and recording the results.

I hope you will find the contents of the attached PDF useful in deciding which Chart of Accounts, if any, will be best for you.

Changing the font of OpenCart mail communications

Opencart has a "newsletter" email facility to communicate with customers. I recently used it for the first time and discovered that, whilst in drafting mode the font of the email text had looked something like Arial (acceptable to me), the text of the email was Times New Roman (I just can't like this font).

There then ensued a search for how to control the default font type of the outgoing email html.  I don't want to have to hand code the source HTML for every communication. 

I eventually found how to do it and share it here in the hopes that it will save someone else the long and frustrating search I had to locate something I would have hoped would be easily found online. Not so!

1. Locate the file admin/controller/sale/contact.php.
2. Go to line 230 in that file. The line reads: $message .= '  <body>' . html_entity_decode($this->request->post['message'], ENT_QUOTES, 'UTF-8') . '</body>' . "\n";
3. Change the text: '<body>' to '<body style="font-family: Arial, Helvetica, sans-serif; font-size: 12px; color: #000000;">' .
4. Save the file

The full line should read: $message .= '  <body style="font-family: Arial, Helvetica, sans-serif; font-size: 12px; color: #000000;">' . html_entity_decode($this->request->post['message'], ENT_QUOTES, 'UTF-8') . '</body>' . "\n";

Your communications should now be in the font of your choice by default.

Excel #VALUE! error in simple formula

I've come across the dreaded "space in a cell" scenario quite a few times in client Excel spreadsheets.  Some seem to think that [space]+[enter] is the same as [delete]. The trouble is that if you try to perform any arithmetic using the cell reference alone you'll get a #VALUE! error.
With the formula =A2+B2-C2 you'll get the error if you put non-numeric text in any of the three cells.

There are several different strategies I employ in these types of scenario. 

One observation is that the Sum() function will treat a space (or any text for that matter) as zero. So, instead of A2+B2-C2 you can write the formula as Sum(A2)+Sum(B2)-Sum(C2) or Sum(A2,B2)-Sum(C2). Note that you cannot write Sum(A2,B2,-C2) or a space character in cell C2 will provoke a #VALUE! error - it tries to calculate the negative of a space character before the Sum function has had a chance to turn it into a zero for arithmetic purposes. An element of error proofing is thus applied to your spreadsheets if you always wrap individual cell references with Sum().

However, you'll often come across this where it may be very time consuming to change an existing spreadsheet to employ this strategy.  In these cases I find that the Edit | Goto (F5 key) followed by Special... and then selecting the Constants radio button in combination with only the Text tick box identifies all cells with text in them.  The ones that look empty are the ones with spaces in them.  Delete the spaces and things should start working again.

Last, but not least, if there ought not to be any valid space characters in the highlighted cells, I would use search and replace.  A single space character goes in the "Find what:" field and the "Replace with:" field is left empty. 

I hope this helps.

Replacement function for GETPIVOTDATA

Why is GETPIVOTDATA so cumbersome to work with!  I'm always having to do stuff like:
=if(iserror(GETPIVOTDATA([parameter set])),0,GETPIVOTDATA([parameter set]))

It gets to be a real pain as I have to edit both [parameter set]s to have it work properly and there's always the possibility of making a mistake that is not immediately apparent.

I finally decided I'd create my own function that returned 0 instead of erroring:

Function GETMYPIVOTDATA(data_field, pivot_table, _
    Optional field1, Optional item1, _
    Optional field2, Optional item2, _
    Optional field3, Optional item3, _
    Optional field4, Optional item4, _
    Optional field5, Optional item5, _
    Optional field6, Optional item6, _
    Optional field7, Optional item7, _
    Optional field8, Optional item8, _
    Optional field9, Optional item9, _
    Optional field10, Optional item10, _
    Optional field11, Optional item11, _
    Optional field12, Optional item12, _
    Optional field13, Optional item13, _
    Optional field14, Optional item14)
Dim retval
Err.Clear
On Error Resume Next
retval = pivot_table.pivottable.GetPivotData(data_field, _
    field1, item1, field2, item2, field3, item3, field4, item4, _
    field5, item5, field6, item6, field7, item7, field8, item8, _
    field9, item9, field10, item10, field11, item11, field12, item12, _
    field13, item13, field14, item14 _
    )
On Error GoTo 0
GETMYPIVOTDATA = IIf(IsError(retval), 0, retval)
End Function

Now I just use =GETMYPIVOTDATA([parameter set])

New Sage to Excel tool for SMEs

Shameless self-promotion follows:

I've just released version 1 of a new data warehousing tool for Sage Instant and Sage Line 50 accounting packages.  A fully functioning 30 day trial is available at www.onionrs.co.uk. There's an overview demo on YouTube.

I'd be very interested in feedback.