I am using our query tool to generate XML files for the topic GLTransactions of the XML API of Exact Online. The input for these files are XML Audit Files coming from Twinfield (XAF 3.1 format).
Since Exact Online has a mixed ledger and lots of constraints, it is not possible to directly load the bank entries. Instead, the bank transactions are posted into a separate General Ledger account of Exact Online with a separate journal as a first step when loading the XML Audit Files from Twinfield.
In the next step, the contents in Exact Online of the reserved General Ledger account are posted in the banking journal. Exact Online itself generates the other half as associated transaction lines for the banking general ledger.
The query used to generate the bank entries is:
create or replace table bank@inmemorystorage
as
select case
when substr(tle.description, 1, instr(tle.description, '/') - 1) = 'BNK'
then '20'
when substr(tle.description, 1, instr(tle.description, '/') - 1) = 'BNK2'
then '21'
else '??'
end
txn_journalcode
, txn.financialyear txn_financialyear
, txn.financialperiod txn_financialperiod
, txn.entrynumber txn_entrynumber
, txn.date txn_date
, tle.date tle_date
, tle.linenumber tle_linenumber
, substr(tle.description, instr(tle.description, '/') + 1, instr(tle.description, ':') - instr(tle.description, '/') - 1) tle_glaccountcode_target
, substr(tle.description, instr(tle.description, ':') + 2) tle_description
, trim(tle.accountcode) tle_accountcode
, tle.glaccountcode glaccountcode_source
, tle.amountdc tle_amountdc
, tle.vatcode tle_vatcode
, tle.yourref tle_yourref
from exactonlinerest..transactionlines tle
join exactonlinerest..transactions txn
on tle.entryid = txn.entryid
where tle.glaccountcode like '290%'
and substr(tle.description, instr(tle.description, '/') + 1, instr(tle.description, ':') - instr(tle.description, '/') - 1) not like '11%' /* Not a bank account. */
order
by tle.entrynumber
, tle.linenumber
select 'GLTransactions\99-Interim-empty.xml'
filename
, stg.fileprefix
|| chr(13)
|| '<GLTransactions>'
|| xml
|| chr(13)
|| '</GLTransactions>'
|| stg.filepostfix
filecontents
from ( select listagg
( chr(13)
|| '<GLTransaction entry="'
|| txn_entrynumber
|| '">'
|| chr(13)
|| '<Journal code="'
|| txn_journalcode
|| '" />'
|| chr(13)
|| '<Date>'
|| substr(xmlencode(txn_date), 1, 10)
|| '</Date>'
|| chr(13)
|| xml
|| chr(13)
|| '</GLTransaction>'
, ''
) xml
from ( select txn_date
, txn_journalcode
, txn_financialyear
, txn_financialperiod
, txn_entrynumber
, listagg
( chr(13)
|| '<GLTransactionLine type="40" linetype="0" line="'
|| tle_linenumber
|| '" offsetline="1" status="20">'
|| chr(13)
|| '<Date>'
|| substr(xmlencode(tle_date), 1, 10)
|| '</Date>'
|| chr(13)
|| '<FinYear number="'
|| txn_financialyear
|| '" />'
|| chr(13)
|| '<FinPeriod number="'
|| txn_financialperiod
|| '" />'
|| chr(13)
|| '<GLAccount code="'
|| case
when tle_glaccountcode_target = '1560'
then '2902' /* Separate interim GL account, Twinfield does not provide separated. */
else xmlencode(tle_glaccountcode_target)
end
|| '" />'
|| case
when tle_description is not null
then chr(13)
|| '<Description>'
|| xmlencode(tle_description)
|| '</Description>'
end
|| case
when tle_accountcode is not null
then chr(13)
|| '<Account code="'
|| xmlencode(tle_accountcode)
|| '" />'
end
|| chr(13)
|| '<Amount>'
|| '<Currency code="EUR" />'
|| '<Value>'
|| -1 * tle_amountdc
|| '</Value>'
|| case
when tle_glaccountcode_target like '4%'
then '<VAT code="GB" />' /* GB = No VAT. */
else ''
end
|| '</Amount>'
|| chr(13)
|| '</GLTransactionLine>'
, ''
)
xml
from bank@inmemorystorage
group
by txn_date
, txn_journalcode
, txn_financialyear
, txn_financialperiod
, txn_entrynumber
)
)
join settings@inmemorystorage stg
on 1=1
In the column tle_yourref in bank@inmemorystorage there is a comma-separated list of associated sales/purchase invoices.
When manually entering the General Ledger transaction on the bank journal, the contents of your ref are populated by the reconciliation window. However, when I export the XML file from Exact Online with transactions, the your ref is missing.
At this moment I seem uncapable to automatically reconcile through an XML or REST API of Exact Online these transactions in the bank journal.
As a workaround you can select each individual account in Reconciliation window (they practically all amount to EUR 0) and then choose Automatic reconciliation. But there are too many accounts to do it with each conversion from Twinfield to Exact Online.
Is there an alternative way to associate invoices with the bank transactions through an API (REST or XML) of Exact Online?
If there is a 1 on 1 relation between the invoice and the payment (so only 1 invoice and 1 payment) you can use <References><InvoiceNumber>put invoice entrynumber here</InvoiceNumber></References> in the bank entry to have it automatically reconciled.
Another possibility is to create an XML file with the topic MatchSets (see documentation) to have it matched afterwards.