XML eGateway  XML eGateway Importer for Exchequer Enterprise:

XML eGateway allows external systems to post virtually any type of transaction into Exchequer Enterprise Software, by simply creating an XML data file in a network directory, or sending to a dedicated e-mail address. It also allows create/update associated data such as customer/stock records before posting a transaction. Thus an external system (e-commerce, till solution or bespoke software) can post virtually any type of transaction, such as an invoice, and create or update an associated account beforehand.


0:00 Intro
5:00 Import SOR example
7:00 How failures are handled
7:30 Pro Export of Data
8:30 System Settings walk through

Features

XML eGateway will:

  • Import/Update Exchequer Account Records
  • Import/Update Exchequer Stock Records
  • Import/Update Exchequer Job records for analysis
  • Import any Exchequer transaction type, SIN,SOR,TSH,NOM,ADJ … with General, Dated or External Linked documents
  • All Exchequer COM Toolkit fields, in above records, mapped to XML Entities.
  • Handles long account codes using Exchequer Account Alt Ref field, and performs automatic conversion, so external system can use an external account and stock code.  more info on long external account codes
  • Attach linked documents to Exchequer Account, Stock, Job or Transaction.
  • Attach diary entries to Exchequer Account, Stock, Job or Transaction.  To allow designated user to review new accounts or stock items.
  • Rename any XML Entity/Element, or default value.
  • Create code conversion list on any field.   Allows VAT codes, etc to be converted from external system code to Exhequer code  more info on code conversion
  • e-mail notification, via IMAPI server, of any errors during operation.  Any files are then moved to "failure" directory and can easily be resubmitted.
  • e-mail user if XML eGateway has NOT been running for 1 hour.

Uses and Benefits

e-commerce systems and external bespoke software

The e-commerce system, on your web site, simply formats any orders into XML.  These are then sent by e-mail to a designated e-mail account.

XML eGateway will then check for orders every minute, and import any for processing, in Exchequer Enterprise Sales Order Processing Module.

The Pro version will export:

  • Customer Account Info, with notes, turnover, commited value, age debt etc. and optional filter which is applied to all related data
  • Supplier Account Info, with notes, turnover, commited value, age debt etc. and optional filter which is applied to all related data
  • Stock Records, with stock levels at locations
  • Outstanding Orders
  • Orders/Invoices in the last x months
  • Outstanding Invoices
  • Price Discount for Account and stock Qty breaks as stored in Exchequer
  • Jobs with notes, analysis, time rate, employee, VAT, Currency, Location, etc codes

And then ftp this information to your web server, via external ftp program.   more information about Export Data

Simplified testing and setup for the developer

Traditional implementation can be daunting for the developer not familiar with Exchequer Enterprise.

XML eGateway, can be implemented for testing very easily:

  • At customer’s site, make a copy of the main company database for testing †
  • Install XML eGateway, setup to receive XML data via IMAPI e-mail account.  †
  • If using pro version, simply export XML data, for use in developers system †
  • The developer can then e-mail in test transactions, to XML eGateway importer, thus being imported into the test company.   Any failed transactions will be bounced back to this account, with error message produced by the Exchequer Com Toolkit validation.
  • Developer can log into customers installation, via Team Viewer or similar program, to check transactions in Exchequer Enterprise.  
  • When happy simply change setting in XML eGateway to submit to live company
  • Export file can then have unused fields turned off to optimise the data export.

† = We can perform this task for you


Document Type Definition (DTD):

The DTD defines the data structure that XML eGateway will expect in the XML file.

The XML file can contain one or more records/entities of same, or of different types:

DTD XML eGateway.xsd

 

Sales Order XML Example

<?xml version="1.0" ?>

        <ExchequerData>

        <TransactionHeader>
        <acOLECode>ZZZZ02</acOLECode>
        <!-- Create Exchequer SOR Transaction, in XML -->

        <thDocType>SOR</thDocType>
        <thAcCode>ABAP01</thAcCode>
        <thTransDate>16/05/2016</thTransDate>
        <thDueDate>16/05/2016</thDueDate>
        <thYourRef>TEST Data</thYourRef>

<!-- Set transaction defaults. The following are not turned ON in all installation of IRIS Exchequer -->
<!-- These can be expressed on each line, but setting them on the header dictates the values on the lines -->
<!-- Depending on how IRIS Exchequer is setup, these could be set as defaults agains the Account/Stock item,
in which case they do not need expressing -->

       <thLocation>AAA</thLocation>

       <TransactionLine>

               <!-- This line specifies Stock code and Price and VAT -->

               <tlStockCode>ALARMSYS-DOM-1</tlStockCode>

               <tlQty>1</tlQty>

                <tlDescr>Alaram system</tlDescr>

               <tlNetValue>57.45</tlNetValue>

               <tlVATCode>Standard</tlVATCode>

               <!-- Standard is converted to S by conversion list -->

        <tlGLCode>52010</tlGLCode>

        </TransactionLine>

        <TransactionLine>

                <!-- All price, VAT will be automatically calculated -->

                <tlStockCode>CAMERA-BRACKET</tlStockCode>

                <tlQty>5</tlQty> <!-- Price has not been expressed so will use IRIS price -->

        </TransactionLine>

        <TransactionLine>

                <!-- Comment line -->

                <tlStockCode></tlStockCode>

                <tlQty>1</tlQty>

                <tlDescr>A comment line</tlDescr>

                <!-- Price has not been expressed so will use IRIS price -->

        </TransactionLine>

</TransactionHeader>

</ExchequerData>

 

But an account could be created/updated before importing a SOR

Transaction and Account XML Example

<?xml version="1.0" ?>

<ExchequerData>

          <Account>  

                    <!-- Create/Update Exchequer Account record, from XML file -->  

                    <!-- Note use of acAltCode, allowing long account code --> 

                    <acAltCode>COMTLONGCODE198</acAltCode>

                    <acCompany>Comtek Accounts</acCompany >

                    <acAddress1>Venture House</acAddress1>

                    <acAddress2>Venture Court</acAddress2>

                    <acAddress3>Bolness Rd.</acAddress3>

                    <acAddress4>Wisbech</acAddress4>

                    <acAddress5>Cambs</acAddress5>

                    <acPostCode>PE13 2QL</acPostCode>

                    <acContact>Sam Smith</acContact>

                    <acGeneralNotes>General Note can be imported against the Account/Transaction

                                </acGeneralNotes>

          </Account>

          <TransactionHeader>  

                     <!-- Create Exchequer SOR Transaction, in XML -->

                    <!-- Note use of acAltCode, allowing long account code -->

                    <thDocType>SOR</thDocType>

                    <thAcCode>COMTLONGCODE198</thAcCode>

                    <thTransDate>1/11/2005</thTransDate>

                    <thDueDate>1/12/2005</thDueDate>

                    <thYourRef>4571025</thYourRef>

                    <thCostCentre>East Division</thCostCentre>

                    <thDepartment>Jane Smith</thDepartment>

                    <thLocation>Main warehouse</thLocation>

 

                    <TransactionLine>

                    <!-- This line specifies Stock code and Price and VAT -->

                             <tlStockCode>ALARMSYS-DOM-1</tlStockCode>

                             <tlQty>1</tlQty>

                             <tlDescr>Labour2</tlDescr>

                             <tlNetValue>57.45</tlNetValue>

                             <tlVATCode>Standard</tlVATCode>

                             <tlVATAmount>10.05</tlVATAmount >

                             <tlGLCode>SALES</tlGLCode>

                    </TransactionLine>

                    <TransactionLine>

                    <!-- All price, VAT will be automatically calculated -->    

                             <tlStockCode>CAMERA-BRACKET</tlStockCode>

                             <tlQty>5</tlQty>

                    </TransactionLine>

          </TransactionHeader>

</ExchequerData>

The order of the elements, within the entities, is not important

Account, Stock and Jobs must be created before a transaction is posted to it. TransactionLine is a child record of Transaction. All XML Data files should have unique filenames.

Data type format

As specified by the W3C for XML

Data Type

Example Values

boolean

true, false, 1, 0

date

1999-05-31

decimal

-1.23, 0, 123.4, 1000.00

integer

-2, 4, 5, 6

string

String

Warning when creating XML Data files using VB, Delphi, etc

Special characters such as “<>&” are not allowed in XML.   If not using an XML writer, then this can be overcome by using the CDATA statement, to encapsulate text data, in the XML data:

                    <acCompany>CDATA[Comtek Accounts & Sons]</acCompany>

                    <acAddress1>CDATA[Venture House,]</acAddress1>

 

All dates should be in ISO 8601 date format as above. YYYY-MM-DD.  When ProVersion exports date data which is blank in Exchequer, it exmports 1900-01-01,


Example: Importing Sales Order and Create Customer Account:

The following XML will create or update an account, with AltCode = COMTLONGCODE198, then post 2 line SOR for COMTLONGCODE198.

   Transaction and Account XML

<?xml version="1.0" ?>

<ExchequerData>

          <Account>  

                    <!-- Create/Update Exchequer Account record, from XML file -->  

                    <!-- Note use of acAltCode, allowing long account code --> 

                    <acAltCode>COMTLONGCODE198</acAltCode>

                    <acCompany>Comtek Accounts</acCompany >

                    <acAddress1>Venture House</acAddress1>

                    <acAddress2>Venture Court</acAddress2>

                    <acAddress3>Bolness Rd.</acAddress3>

                    <acAddress4>Wisbech</acAddress4>

                    <acAddress5>Cambs</acAddress5>

                    <acPostCode>PE13 2QL</acPostCode>

                    <acContact>Sam Smith</acContact>

                    <acGeneralNotes>General Note can be imported against the Account/Transaction

                                </acGeneralNotes>

          </Account>

          <TransactionHeader>  

                     <!-- Create Exchequer SOR Transaction, in XML -->

                    <!-- Note use of acAltCode, allowing long account code -->

                    <thDocType>SOR</thDocType>

                    <thAcCode>COMTLONGCODE198</thAcCode>

                    <thTransDate>1/11/2005</thTransDate>

                    <thDueDate>1/12/2005</thDueDate>

                    <thYourRef>4571025</thYourRef>

                    <thCostCentre>East Division</thCostCentre>

                    <thDepartment>Jane Smith</thDepartment>

                    <thLocation>Main warehouse</thLocation>

                    <TransactionLine>

                    <!-- This line specifies Stock code and Price and VAT -->

                             <tlStockCode>ALARMSYS-DOM-1</tlStockCode>

                             <tlQty>1</tlQty>

                             <tlDescr>Labour2</tlDescr>

                             <tlNetValue>57.45</tlNetValue>

                             <tlVATCode>Standard</tlVATCode>

                             <tlVATAmount>10.05</tlVATAmount >

                             <tlGLCode>SALES</tlGLCode>

                    </TransactionLine>

                    <TransactionLine>

                    <!-- All price, VAT will be automatically calculated -->    

                             <tlStockCode>CAMERA-BRACKET</tlStockCode>

                             <tlQty>5</tlQty>

                    </TransactionLine>

          </TransactionHeader>

</ExchequerData>

 

More information are on the following pages:

 

More examples included with test program

Conversion of Codes:

Exchequer Software Codes such as Location, CostCentre, Department, GLCode, Currency and VATCode are converted using bespoke conversion list.   This list is specified globally in the XMLeGatewayConversion.xml, available from the "View >> Conversion Settings" menu. 

10 Bespoke lists can be created and defined on any import fields, by entering "bespoke1-10" in the "Conversion" field, in the "Exchequer Tables" table, in XMLSettings.mdb database.

For example in system settings we may have:

   System Settings XML

<ConversionSetttings>

          <!-- The following list allows the importer to convert codes from the external -->

          <!-- system to the Exchequer codes,  any errors will be logged and e-mailed -->

 

          <!-- Currency codes 1-89 -->

          <Currency>GB=1,EUR=2,DOL=3,Others</Currency>

         

          <!-- CostCentre code conversion -->

          <CostCentre>East Division=AAA,West Division=WST,North Division=NOT,Others</CostCentre>

         

          <!-- Department code conversion -->

          <Department>Jim Marsh=JIM,Jane Smith=SMT,Others</Department>

         

          <!-- VATCodes code conversion -->

          <VATCode>Standard=S,Zero=Z,Others</VATCode>

 

          <!-- Location code conversion -->

          <Location>Main warehouse=AAA,VAN=VAN,Others</Location>

         

          <!-- Job Type code conversion -->

          <JobType>east=EAT,Others</JobType>

         

          <!-- GL code conversion -->

          <GLcode>BANK=2010,SALES=52010,COSTS=62010,Others</GLcode>

</ConversionSetttings>

Enter <Currency>NONE</Currency> for NO conversion


Therefore, values in our file are converted to Exchequer Software codes:

<thCostCentre>East Division</thCostCentre> = AAA

<thDepartment>Jane Smith</thDepartment> = SMT

<tlVATCode>Standard</tlVATCode> = S

<tlGLCode>SALES</tlGLCode> = 52010  

 

Always sets GL to 990, so we do not have to define the complete GL structure while testing

Element properties

All XML Element properties are stored in simple MS Access Database, called “XMLSettings.mdb” in the main program directory. In table "Exchequer Tables". This allows the name of each Element to be changed; by default they are the same as the name used within the Exchequer Software COM Toolkit, but can be easily changed to match external systems. A conversion list can be defined for each individual Element, or mapped to a global conversion list defined in system settings.

Other considerations

When the external system creates the XML Data file, it is advisable to create the file in another directory, then data file copy to the XML eGateway directory. This will help avoid file-locking problems.

It is advisable to breakdown operations into as smaller operations as possible, so if account fails to update, due to record lock, then the transaction does not post.


Sign-Up for Newsletter

Sign up for our newsletter for the latest information on our products, offers and new Exchequer plug-ins.

* required








Facebook

Follow us on Facebook
Twitter

Follow us on Twitter for latest news.