Mass loading GL transactions into Exact Online

Question

For performance testing reasons I want to load many General Ledger transactions into Exact Online. I am using the REST API for Transactions and their lines by posting transactions after authenticating using OAuth.

However, this is quite slow and it involves a lot of network round trips. I need to load 180.000 transaction lines. Is there an alternative way to mass load data in Exact Online?

score 1 · Accepted Answer · answered Mar 08 '17 at 13:00

The best way to mass load data into Exact Online is to use the XML APIs. You can either using HTTP POST yourself after authenticating or use a script like the following. Please change the variables for the number of transactions and lines per transaction you would like to load:

local remark Specify the division code here:

local define DIVISION_CODE "868035"

local remark Originating General Ledger account:

local define GL_ACT_CODE_FROM "8000"

local remark Target General Ledger account:

local define GL_ACT_CODE_TO "8001"

local remark Journal

local define JOURNAL_CODE "90"

local remark Number of GL entries to generate:

local define CNT_TXN_HEADERS "5"

local remark Number of lines per GL entry:

local define CNT_TXN_LINES "50"

use ${DIVISION_CODE}

create or replace table settings@inmemorystorage
as
select '<?xml version="1.0" encoding="utf-8"?>'
       || chr(13)
       || '<eExact xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="eExact-XML.xsd">'
       fileprefix
,      chr(13)
       || '</eExact>'
       filepostfix
from   dual@datadictionary

insert into UploadXMLTopics
( topic
, payload
, division_code
, orig_system_reference
)
select 'GLTransactions' topic
,      stg.fileprefix
       || chr(13)
       || '<GLTransactions>'
       || xml
       || chr(13)
       || '</GLTransactions>'
       || stg.filepostfix
       filecontents
,      '${DIVISION_CODE}' division_code
,      'GLTransactions\SAMPLE.xml'
       filename
from   ( select listagg
                ( chr(13)
                  || '<GLTransaction>'
                  || chr(13)
                  || '<Journal code="'
                  || txn_journalcode
                  || '" />'
                  || chr(13)
                  || '<Date>'
                  || substr(xmlencode(trunc(sysdate)), 1, 10)
                  || '</Date>'
                  || chr(13)
                  || xmlsource
                  || chr(13)
                  || xmltarget
                  || chr(13)
                  || '</GLTransaction>'
                  , ''
                  ) xml
          from    ( select trunc(sysdate) txn_date
                    ,      '${JOURNAL_CODE}' txn_journalcode
                    ,      listagg
                           ( chr(13)
                             || '<GLTransactionLine line="'
                             || id
                             || '" >'
                             || chr(13)
                             || '<Date>'
                             || substr(xmlencode(trunc(sysdate)), 1, 10)
                             || '</Date>'
                             || chr(13)
                             || '<GLAccount code="'
                             || xmlencode('${GL_ACT_CODE_FROM}')
                             || '" />'
                             || chr(13)
                             || '<Description>'
                             || xmlencode('Transaction offset #' || id)
                             || '</Description>'
                             || '<Amount>'
                             || '<Currency code="EUR" />'
                             || '<Value>'
                             || -1
                             || '</Value>'
                             || '</Amount>'
                             || chr(13)
                             || '</GLTransactionLine>'
                             , ''
                           )
                           xmlsource
                    ,      listagg
                           ( chr(13)
                             || '<GLTransactionLine line="'
                             || id
                             || '" >'
                             || chr(13)
                             || '<Date>'
                             || substr(xmlencode(trunc(sysdate)), 1, 10)
                             || '</Date>'
                             || chr(13)
                             || '<GLAccount code="'
                             || xmlencode('${GL_ACT_CODE_TO}')
                             || '" />'
                             || '<Description>'
                             || xmlencode('Transaction #' || id)
                             || '</Description>'
                             || chr(13)
                             || '<Amount>'
                             || '<Currency code="EUR" />'
                             || '<Value>'
                             || 1
                             || '</Value>'
                             || '</Amount>'
                             || chr(13)
                             || '</GLTransactionLine>'
                             , ''
                           )
                           xmltarget
                    from   range(${CNT_TXN_LINES})@datadictionary txnlines
                    group
                    by     txn_date
                    ,      txn_journalcode
                    )
         join  range(${CNT_TXN_HEADERS})@datadictionary txnheaders
       )
join   settings@inmemorystorage stg
on     1=1
where  xml is not null

local remark Check outcome:

select * from UploadXMLTopics

The performance of the XML APIs varies, but in general during office hours it is slower. I've just tested it; it takes 15 seconds to upload 500 transaction lines as shown (debet/credt x 5 x 50 as specified in the script). By playing with division IDs, you can load across several divisions to generate inter company posts. This script requires the Exact Online combined or XML driver with invantive sql, but with manual HTTP POSTS it is similar.

Mass loading GL transactions into Exact Online

1 Answers1