Exchequer Software Plug-ins

  XML eGateway Import: Exporting Data for External Systems

Export is only available in Pro Version.

This page covers the tables which can be exported and the system settings which control the export process.

Export, for FTP, of Exchequer Enterprise data several times a day.   This will allow external systems to lookup
Customer, Stock and Transaction Info.

• Customer/Supplier Account Info
• Accounts Price Matrix
• Stock Records, with stock levels at locations
• Stock Qty Breaks
• Outstanding Orders
• Trans in the last x months
• Outstanding Invoices
• Cost Centre/Department codes
• Stock Multi Locations codes
• General Ledger codes
• VAT codes
• Currency codes
• Jobs
• Job Analysis
• Job Time Rates
• Job Employees
• Job Types

This data can be exported in CSV format.

The exported data functionality allows an external system to display list of Exchequer data when entering a transaction. Or display outstanding Order or Invoice on a Web site.

System Settings

The Pro version of XML eGateway, in CSV format.  This can then be uploaded into external/e-commerce system.

The settings for the Pro version are held in XML eGatewaySettingsPro.xml, and also available from "View >> Settings" menu.

Controlling which fields are exported

The fields to export can be turned ON/OFF by ticking and unticking the "Export", in table record of file in XMLSettings.mdb. 

Backup the file before making any changes

Using MS Access, open XMLSettings.mdb, then select "Tables" and open "Table":

This lists all the tables which are exported:

Click on the "+" button, to list the fields within the table

The "Export" field controls the data exported.  Two more fields "ExportOutstandingInvoices" and "ExportOutstandingOrders" control the fields exported for outstanding invoices/orders

Other fields should not be edited, as it may break the system

Calculating Exchequer Pricing

To Calculate price from Exchequer discount matrix we do the following

Example: E-commerce system:

Activity on site

User logs into accounts, and thus selects account
     we get acDiscountBand (A-H) code.  Get default currency "acCurrency for account (usually 1)
         if acDiscountBand ="" then acDiscountBand="A"
         Get acDiscount. This can contain a % discount on everything unless???
User selects stock item
     we get stock record, which gives us the price for item in stPriceBandC_Price

If Stock Qty Breaks are used in Exchequer, then we would:

Get stParent1-6 from the stock record.  These represent the parent codes of stock item in Exchequer stock tree.

Usually the tree is not deeper than 4 levels

Look for Qty Discount against the stock record:
Search "stockqtybreaks" table for Stock code in qbCode and for Qty between qbQuantityFrom and qbQuantityTo †

If not found:
if stParent6<>"" then
     Search "stockqtybreaks" table for stParent6 in qbCode and for Qty between qbQuantityFrom and qbQuantityTo †

If not found:
if stParent5<>"" then
     Search "stockqtybreaks" table for stParent5 in qbCode and for Qty between qbQuantityFrom and qbQuantityTo †

If not found:
if stParent4<>"" then
     Search "stockqtybreaks" table for stParent5 in qbCode and for Qty between qbQuantityFrom and qbQuantityTo †

And so on, working your way through the possible discounts at various levels of stock tree

If a record is found then it could have:

• Band price less discount %, held in qbDiscPercent
• Special Price, held in qbPrice
• Markup %, held in qbMarkupMarginPercent
• Margin %, held in qbMarkupMarginPercent

† Exchequer has the ability to select effective dates for a price band, this should be checked also. 

If Account Price Discounts are used in Exchequer, then we would:

Search in "customerprice" table for account + Stock code + band in adCode + adStockCode + adPriceBand + adCurrency

If not found:
If stParent6 <>”” then Search in "customerprice" table for adCode + adStockCode(=stParent6) + adPriceBand + adCurrency

If not found:
If stParent5 <>”” then Search in "customerprice" table for adCode + adStockCode(=stParent5) + adPriceBand + adCurrency

If not found:
If stParent4 <>”” then Search in "customerprice" table for adCode + adStockCode(=stParent4) + adPriceBand + adCurrency

And so on, working your way through the possible discounts at various levels of stock tree

If a record is found then it could have:

• Band price less discount %, held in adDiscPercent
• Special Price, held in adPrice
• Markup %, held in adMarkupMarginPercent
• Margin %, held in adMarkupMarginPercent

Note : Account Qty breaks are not supported at present as very few Exchequer customers use it

Exchequer Customer/Supplier Account Info

Table name:Customer and Supplier

Exchequer Customer and Supplier information export is turned on in Pro Settings. Select “Pro >> Settings” menu option to open the Pro Settings.  see Settings Table

Account fields exported

The Customer/Supplier fields which can be exported are covered by the Import Account page.  This lists the fields which can be imported, which with few exceptions can be exported, plus a list of special export fields at bottom of page.

Exchequer Accounts Price Matrix

Table name:Customerprice

This table represents the qty price breaks, in Exchequer Enterpise, which relate to account records.  more info about Exchequer Pricing

Exchequer Stock Records, with stock levels at locations

Table name:Stock

Stock is an optional module in Exchequer Enterprise.

Exchequer Stock information export is turned on in Pro Settings. Select “Pro >> Settings” menu option to open the Pro Settings.  see Settings Table

Stock fields exported

The fields which can be exported are covered by the Import Stock page.  This lists the fields which can be imported, which with few exceptions can be exported, plus a list of special export fields at bottom of page.

Stock notes

Exchequer Enterprise stock record has 8 price bands, label A-H (stPriceBandA_Price-stPriceBandH_Price).  These are usually in currency 1, but can be in different currencies (stPriceBandA_Currency-stPriceBandH_Currency).

Multiple locations can be enabled within Exchequer Enterprise.  By default XML eGateway will export overall stock level, free to sell, and qty on order.  If the installation has one or more main locations, and several sundry locations, then enter the locations to export a figure for in <stock_locations> list of settings.

Exchequer Stock Qty Breaks

Table name:Stockqtybreaks

This table represents the qty price breaks, in Exchequer Enterprise, which relate to stock items.  more info about Exchequer Pricing

Exchequer Outstanding Orders

Table name:Outstanding_orders and Outstanding_order_lines. The two tables are related by thOurRef (in Outstanding_orders), and tlOurRef (in Outstanding_order_lines).  The Outstanding_order_lines table should then be sorted by tlLineNumber to give correct order.

Sales Order Processing is an optional module in Exchequer Enterprise.

Exchequer Outstanding Orders information export is turned on in Pro Settings. Select “Pro >> Settings” menu option to open the Pro Settings.  see Settings Table

Outstanding Order Transaction fields exported

The fields to export can be turned ON/OFF by ticking and unticking the "ExportOutstandingOrders", in table record of file in XMLSettings.mdb

The fields which can be exported are covered by the Import Transaction page.  This lists the fields which can be imported, which with few exceptions can be exported, plus a list of special export fields at bottom of page.

Additional settings

Exchequer Transactions in the last x months

Table name:Trans_since_x and Trans_since_x_lines.  The two tables are related by thOurRef (in Trans_since_x), and tlOurRef (in Trans_since_x_lines).  The Trans_since_x_lines table should then be sorted by tlLineNumber to give correct order.

Exchequer Transaction information export is turned on in Pro Settings. Select “Pro >> Settings” menu option to open the Pro Settings.  see Settings Table

Transaction fields exported

The fields to export can be turned ON/OFF by ticking and unticking the "Export", in table record of file in XMLSettings.mdb

The fields which can be exported are covered by the Import Transaction page.  This lists the fields which can be imported, which with few exceptions can be exported, plus a list of special export fields at bottom of page.

Additional settings

Exchequer Outstanding Invoices

Table name:Outstanding_invoice and Outstanding_invoice_lines.  The two tables are related by thOurRef (in Outstanding_invoice), and tlOurRef (in Outstanding_invoice_lines).  The Outstanding_invoice_lines table should then be sorted by tlLineNumber to give correct order.

Exchequer Outstanding Invoice information export is turned on in Pro Settings. Select “Pro >> Settings” menu option to open the Pro Settings.  see Settings Table

Outstanding Invoice Transaction fields exported

The fields to export can be turned ON/OFF by ticking and unticking the "ExportOutstandingInvoices", in table record of file in XMLSettings.mdb

The fields which can be exported are covered by the Import Transaction page.  This lists the fields which can be imported, which with few exceptions can be exported, plus a list of special export fields at bottom of page.

Additional settings

Exchequer Cost Centre/Department codes

Table name:CostCentre and Departments

Cost Centre/Departments are used as an extra analysis in Exchequer. If turned On, within Exchequer, then they are on each transaction line. The XML eGateway will set the default value within a transaction when imported. The default value depends on how the Exchequer system is setup.

This information is exported to allow the developer to display the list of Centre/Departments codes within their own app.

Cost Centre

Department

Exchequer Stock Multi Location codes

Table name:Locations

Stock is an optional module in Exchequer Enterprise.

The location record allows the address of the location to be entered.  But usually just the ID and name is of importance.

Exchequer General Ledger codes

Table name:GL

The general Ledger in Exchequer drives the financial analysis.  Defaults for a stock item, or Job Analysis code, are usually set within Exchequer.   Thus the GL Code is set automatically.

If stock and job costing is not used then the GL code would need specifying on a transaction before importing.

This information is exported to allow the developer to display the list of GL codes within their own app.

Exchequer VAT codes

Table name:VAT_codes

Exchequer allows 20 VAT codes.

This information is exported to allow the developer to display the list of currency codes within their own app.

Exchequer Currency codes

Table name:Currency

Exchequer allows 89 currencies.

This information is exported to allow the developer to display the list of currency codes within their own app.

Exchequer Jobs

Table name:Job

Job Costing is an optional module in Exchequer Enterprise.

Exchequer Job information export is turned on in Pro Settings. Select “Pro >> Settings” menu option to open the Pro Settings.  see Settings Table

The fields which can be exported are covered by the Import Job page.  This lists the fields which can be imported, which with few exceptions can be exported, plus a list of special export fields at bottom of page.

Exchequer Job Analysis

Table name:Jobanalysis

Job Analysis codes drive the Job costing analysis.  They set the GL codes when importing.

This information is exported to allow the developer to display the list of Job Type codes within their own app.

Exchequer Job Time Rates

Table name:Jobrates

This table is used when populating timesheets within Job Costing

Exchequer Job Employees

Table name:Jobemployee

This table is used when populating timesheets within Job Costing

Exchequer Job Types

Table name:Jobtype

This information is exported to allow the developer to display the list of Job Type codes within their own app.