Streamline your accounting by integrating AdvanceRetail with Xero
Introduction
The AdvanceRetail Accounting Interface simplifies your accounting process by automatically importing AdvanceRetail transactional data into your Xero account.
This user guide is to assist in the initial setup and configuration of the AdvanceRetail Xero Accounting Interface.
Please note that the accounting information in this document is for illustrative purposes only. You should consult an accounting professional regarding your business’s particular requirements prior to using the AdvanceRetail Accounting Interface.
Data Flows between AdvanceRetail & Xero
Getting Started
Prior to installing and running the interface it is important that the following steps have been completed. Time spent planning and setting up the interface will reward you with a successful installation resulting in the quick and easy transfer of your data from AdvanceRetail to Xero.
- Prerequisites
- Xero Authentication
- Setup AdvanceRetail
- Install and configure the interface
- Using the interface
Prerequisities
Before setup, please ensure you have the following:
- AdvanceRetail Version 2.7 or higher
- AdvanceRetail Supplier Invoice matching module if you want to post matched supplier invoices to Xero
- A Xero Company setup
- A basic understanding of accounting, AdvanceRetail and Xero
Xero Authentication
Xero Authentication for OAuth 2.0
This application needs to be registered at https://developer.xero.com/myapps so that a “Client Id” and “Client Secret” can be retrieved.
Set the application name as follows:
Set the OAuth2.0 redirect URL as follows:
- Copy the value from Client Id and keep a copy, this value will be required in configuration.
- Generate a Client Secret and keep a copy, this value will be required in configuration.
- When finished make sure your press “Save” to record the registered application.
Xero Interface Setup
After installing the Xero Interface, you will be prompted to make a connection with an AdvanceRetail database.
Once a connection has been entered you will be prompted for the Xero specific settings:
Values should be set as follows:
- Use OAuth2 – this should always be checked. With this checked fields “Xero Consumer key” and “Xero Private Key” will become “Xero Client id” and “Xero Client Secret” respectively.
- Clearing Account Code – Sales clearing for the Daily Sales Invoice posting. Accounts Payable Clearing for Supplier Invoices and Credit Notes.
- Xero Client Id – Value copied when registering this application at Xero.
- Xero Client Secret – Value copied when registering this application at Xero.
- Xero Sales Contact – This is the contact (account) used in the Daily Sales Invoice posting. The contact must exist in Xero; otherwise an error will be generated when a extract (post) is attempted.
- Xero Tracking Name 1 – The name of the tracking category as setup in Xero. If not used this can be left blank. This name must exist in Xero; otherwise an error will be generated when an extract (post) is attempted.
- Xero Tracking Name 2 - The name of the tracking category as setup in Xero. If not used this can be left blank and unless multi company postings are being used, it should be left blank. This name must exist in Xero; otherwise an error will be generated when an extract (post) is attempted.
- Use cat1 to retrieve GL Codes – Check this if you want to use Category1 value as the link between an item and the GL Group that contains account codes for an item.
NOTE: This option is for Xero interface only so if the standard AdvanceRetail batch report is used to report GL batches account numbers would not be resolved in the report.
- Xero Tax codes – The tax code names as setup in Xero. (Refer defaults).
- Exporting Sales (Invoice and GL Jnl) – Check this to post till totals. Sales will be posted as a taxable sales invoice. The rest of till totals will be posted in a $0 sales invoice.
- Exporting Post Cost of Sales – Check this if cost of sales is to be posted for sales extracted in the first option.
- Exporting Post (Inventory) – Check this to post stock receipts, stock returns and all stock adjustments.
- Exporting Post Bank Transactions – Check this to post cash movements in and out of a store (bank deposit/withdrawal) and movements within a store. These transactions are only generated in AdvanceRetail if Advanced Banking is turned on.
- Exporting Supplier Invoices – Approved Supplier Invoices and Credit Notes are posted to Xero as entered in AdvanceRetail’s invoice matching module. The AdvanceRetail Supplier Name must be an exact match to the Xero contact name for the supplier to achieve a posting.
AdvanceRetail Setup
General
Most of the GL account codes in AdvanceRetail are only 10 characters long. Therefore, if tracking category 1 (P&L dissection) is used, then the maximum combined length of any Xero account code and tracking 1 combination will be 9 characters allowing for the “:” delimiter character.
Codes are maintained in AdvanceRetail in several places, system wide account codes, GL Group codes that are tied to items, codes against tenders, and codes against select lists where the select list is used to categorise a transaction.
In the case of GL Group codes, standard AdvanceRetail requires that an item be tagged with a GL Group, otherwise an account cannot be resolved for posting. For the Xero interface an option has been provided to either use this standard method OR just use a link between an items category 1 and GL Group. This for some will be a much easier method of maintaining codes for posting.
When generating a Xero posting all codes will be checked against Xero, and if any code does not exist, an error will be generated, and the posting will not proceed.
System Accounts
Access through Maintenance -> System Settings -> System Options -> GL Settings tab.
Post Detail: - This setting is not used by the Xero Interface.
Prefix/Suffix: - If tracking category 2 is being used (branch posting) then it is always suffix. Any account where “Branch Pfx/Sfx” is checked will have the value as loaded for the transactions branch (tracking category 2) appended to the account.
Debtors Control: - This is the accounts receivable control account for Debtors run in AdvanceRetail.
Layby Control: - This is the control account for items on layby (Amount owing).
Rounding: - This account receives any sales rounding’s from sales transactions.
Deposits: - This is the account to hold prepaid deposits on customer orders or processing.
Branch Float: - Future use
Fex Gain/Loss: - Future use
Unders/Overs: - This account is the expense code for any till discrepancies found at session end.
Freight In: - Supplier Invoices/Credit Notes that have a freight content will post to this account.
Discount In: - Future use
Purchases Control: - Stock Receipts and Returns are posted to Inventory with the other leg going to this account. Purchases Control is an accrual account pending receipt of a Supplier Invoice/Credit Note which when processed reverses the accrual.
Goods in Transit: - Stock that has been transferred is posted to this account until receipted. Any discrepancy in the receipt creates a stock adjustment (system option) that clears any residual balance in this account back to the sender’s inventory.
Creditors Control: - Control Account for accounts Payable. Supplier Invoices and Credit Notes post the full amount to this account.
Change Account: - This account is where to post “Change not given”. I.e. Tenders set up to allow over tender and marked as don’t give change. Sometimes tenders such as gift vouchers are set this way.
Suspense Account: - Future Use
Voucher Activation: - Future Use
GL Group Codes
Access through Maintenance -> System Setup -> GL Codes.
Items are linked to GL groups either by GL Code or by Category1 depending upon the Xero configuration.
Prefix/Suffix: - Check this box to have Tracking Category 2 implemented for this account code. If tracking category 2 is being used (branch posting) then it is always suffix.
Sales: - Xero Sales code and tracking category 1 (if used).
Discount: - Future Use.
Cost of Sales: - Xero Cost of Sales code and tracking category 1 (if used).
Inventory: - Control account for stock items.
Services: - Control account for Non Stocked items (services).
Faulty Stock: - Expense or clearing account for items returned at POS faulty.
Stock Take Variance: - Expense account for stock takes gains/losses.
Buying Variance: - This is a Cost Of Sales account that receives variances resulting from processing Supplier Invoice/Credit Notes matched to Stock Receipts and Returns. In addition Write back of unmatched Receipts/Returns clear the accrual and post to this account.
Negative Stk Adj: - Used when posting the system generated stock adjustment generated when moving into or out of negative stock positions. This account should be in the cost of sales area of the P&L as it represents COS that is attributable to sales that have already occurred.
Charge on (All Accounts): - Future Use.
Tender Accounts
Access through Maintenance -> Tenders -> Tenders Types.
GL Clearing Account: - Future Use.
GL Account: - Post this tender to this account.
GL Commission A/C: - Future Use.
Select List Accounts
Access through Maintenance -> System Setup -> Select Lists
Load expense account against each stock adjustment reason.
Load expense account against each stock revaluation reason.
Load expense account against each debtor adjustment reason.
Branch Maintenance
Access through Maintenance -> Branches -> Branch Maintenance
If tracking category 2 has been implemented, then the value required for this category is loaded in the field indicated. The value MUST be prefixed by a “:” so that the posting process can identify this value.
Supported AdvanceRetail Transactions
Daily Sales
Sales are posted as an Accounts Receivable invoice, the Xero Contact used is as per Xero configuration.
For each extraction one invoice is posted per Sales Date and GL Batch Number.
For each Invoice one line is generated for each Tax, Tracking 1 and Tracking 2 combination and posted to Sales.
Each Invoice includes one line posting to sales clearing; this effectively makes it a $0 invoice and reverses the sales clearing entry from the Sales GL Journal.
Sales GL Journal
The rest of till totals is posted as a Xero $0 Invoice. The balancing leg of this journal (Sales) is posted to the clearing account as setup in the Xero configuration.
In addition, if specified, Cost of Sales is posted for each Tracking 1 and Tracking 2 combination, the balancing leg going to Inventory.
| Transaction | Comments | Support | Option |
| Cash Sale | Sale is part of ACCREC Invoice.
Payment is included in Journal |
Yes | Sales |
| Layby New | Sale is part of ACCREC Invoice.
Payment is included in Journal |
Yes | Sales |
| Layby Amend | Sale/Credit is part of ACCREC Invoice. | Yes | Sales |
| Layby Payment | Payment is included in Journal | Yes | Sales |
| Layby Cancel | Sale/Credit is part of ACCREC Invoice.
Refund is included in journal. |
Yes | Sales |
| Layby Final | Payment is included in Journal | Yes | Sales |
| Order New | Deposit and payment are included in Journal | Yes | Sales |
| Order Payment | Deposit and payment are included in Journal | Yes | Sales |
| Order Release | Sale is included in Journal
Deposit and payment are included in Journal |
Yes | Sales |
| Order Amend | Deposit and payment/refund are included in Journal | Yes | Sales |
| Order Final | Sale is included in Journal
Deposit and payment are included in Journal |
Yes | Sales |
| Order Cancel | Yes | Sales | |
| Process New | Deposit and payment are included in Journal | Yes | Sales |
| Process Amend | Deposit and payment/refund are included in Journal | Yes | Sales |
| Process Final | Sale is included in Journal
Deposit and payment are included in Journal |
Yes | Sales |
| Account Payment | Payment is included in Journal | Yes | Sales |
| Paid Out | Payment and expense are included in Journal | Yes | Sales |
| Paid In | Payment and expense are included in Journal | Yes | Sales |
| Cash Out | Pay out and in are included in the Journal. | Yes | Sales |
| Cost of Sales | Posted Inventory/Cost of Sales via Tracking category 1 for any sale in transactions above. | Sales &
COS |
Inventory
| Transaction | Comments | Support | Option |
| Stock Receipts | Stock Receipt
Transfer In Write Back Receipt |
Yes
Yes Yes |
Inventory
Inventory Inventory |
| Stock Returns | Stock Return
Write Back Return Charge On Credit |
Yes
Yes No |
Inventory
Inventory |
| Req. for Credit | Req for Credit
Write Back Req for Credit |
Yes
Yes |
Inventory
Inventory |
| Stock Adjustments | Manual Adjustment.
Stock Take Adjustment. Return stock Faulty. Kit Sale Adjust. Negative Stock Adjust. Stock Transfer Adjust. Stock Revaluation. Stock Adjust (Invoice Match) |
Yes
Yes Yes Yes Yes Yes Yes Yes |
Inventory
Inventory Inventory Inventory Inventory Inventory Inventory Inventory |
| Transfer In | Yes | Inventory | |
| Transfer Out | Yes | Inventory |
Accounts Payable
If supplier invoice matching is used, then committed supplier invoices and credit notes may be input directly into Xero if the option is selected in Xero configuration. Note Supplier Name in AdvanceRetail must be an exact match for the Xero Contact name.
| Transaction | Comments | Support | Option |
| Supplier Invoice | Posted to Accounts Payable. AdvanceRetail Supplier Code must exist in Xero as a Contact. The appropriate Tax amount is included. One Invoice line to represent the total less tax posted to Clearing. | Yes | Supplier |
| Supplier Credit | Posted to Accounts Payable. AdvanceRetail Supplier Code must exist in Xero as a Contact. The appropriate Tax amount is included. One Credit line to represent the total less tax posted to Clearing. | Yes | Supplier |
Included in the GL journal are summary postings for Invoices and Credits as follows:
| Transaction | Comments | Support | Option |
| Supplier Invoice/Credit | Posts to Clearing (Reverse AP posting), Accruals (reverse Receipt/Return posting), Expenses (if any), Freight (if any) and Buying Variance (difference between invoiced and receipted. | Yes | Inventory |
Cashier Transactions
This is the movement of money within a store and to and from a bank or other financial institution.
| Transaction | Comments | Support | Option |
| Session End | Unders/Overs posting | Yes | Sales |
| Bank Deposit | No | ||
| Bank Withdrawal | No | ||
| Branch Balance | No | ||
| Cash Transfers | No |
Using the Xero Interface
Starting the Xero Interace for the first time
During the installation process the following icon will be created on your desktop. Double clicking on this will start the program.
The first time you start the application the following series of forms will need to be completed.
Select Xero New Zealand.
Click [Save] to continue.
Enter Xero Configuration.
Click [Save] to continue
The Xero Interface Screens
This is the main user interface for the program:
The central message area displays information to the operator. This includes:
- Name of branch the interface is connect to
- Whether there is anything to post
- Error messages
Refresh
Clicking on this button will refresh the display of information. This is useful if you have made changes in AdvanceRetail after starting the interface.
Export
This allows you to export information up to and including the date specified and is useful for posting transactions up to the end of the month. The default is today’s date minus one day.
The first step in an export is to mark transactions to be included with the next GL batch number. This updating occurs prior to any attempt to generate the posting so that other activities do not change the state of the transaction file while the posting is occurring.
If an error occurs in the export, then the whole posting for the given GL batch is abandoned. The “Last Batch Number Posted” is NOT updated. The following export will select transactions within the range “Last Batch Number Posted” and “Current batch Number” thereby completed all un posted transactions.
Exit
Quit the program
Menu Options
| File -> Exit | Quit the program. |
| View -> AdvanceRetail Transaction | View old and new transactions, see below for details. |
| View-> Interface Log File | A quick and easy way to view the latest log file created by Xero. This is where you can find detailed error messages for failed imports. |
| Re-Export -> GL Batch file | Allows you to select and re-export a batch file to Xero. |
| Re-Export -> Supplier Invoice Batch | Allows you to select and re-export a batch of supplier invoices. |
| Setup -> Connection | Displays the same Database connection form that is shown on installation. Allows the database connection to be changed. |
| Setup –> Configure | Displays the same configuration form that is shown when installing the software. Allows you to make changes to these settings |
| Setup -> Prepare AdvanceRetail | Marks all transactions in AdvanceRetail as posted. This is a useful function when you first start using the interface as it prevents to posting of old transactions. |
| Help -> Help About | Display the version number of the interface. |
AdvanceRetail Transaction Listing
General
An empty transaction screen is displayed with options to select the various transaction extraction types as follows:
Selecting any of the listed transaction types will display a grid with the following common behaviour:
- The Transaction List will be grouped by default.
- Grouping can be changed. To ungroup drag (e.g. “Transaction”) object back over the grid. To add grouping, drag a column from the grid to the group bar.
- The transaction list will be collapsed by default - to expand click the “+” and to collapse press the “-“
- A filter will be displayed for every grid column - to apply a filter enter a valid value and press <enter>
- For information purposes, the last batch number posted is displayed at the base of the screen. To limit the results displayed, the user can either specify the maximum number of transactions to retrieve, or can specify a single batch to display.
Posting (or re-posting) to Xero
When you go to post or re post a batch you will be prompted in your browser to log in to Xero followed by selection of the organisation to connect to.
Selection of organisation:
When complete you can close your browser or the browser tab and return to the interface to proceed with posting.
FAQ's
Here are answers to some of the most frequently asked questions about the Xero interface:
| Where do I find log files for the Xero interface ? | Log files will be generated in directory:
The file name will be Account.Log The size and number of logs is maintained automatically so there is no need to perform maintenance on this directory. |
| Are backups of the Xero posting XML files saved ? | Every time a posting is performed a backup file of the XML that is transmitted is performed. This directory is not automatically maintained with regard to deleting old files, and therefore the user should from time to time clean out this directory.
Files generated on an export (if postings exist) are as follows:
If a re-export is being performed, then the original file will be overwritten by the new file. |