Upload Journal Entries to Exact Online

Questions
2

For journal entries, there are various alternatives.

Get My Report

There is a pre-packaged solution on Get My Report. From the menu choose: Upload boekingen ​. The documentation is available at how to upload journal entries

Invantive SQL

The code used by this Get My Report function can also be used for inspiration and runs on any Invantive SQL platform. It is attached as gmr-eol-upload-excel-sheet-transaction-line-entries.sql. The essential part is:

    create or replace table xmlupload@inmemorystorage
     as
     select 'GLTransactions' topic
     ,   division_code
     ,   null orig_system_reference
     ,   :orig_system_group orig_system_group
     ,   xmlformat
         ( '<?xml version="1.0" encoding="utf-8"?>'
          || '<eExact xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="eExact-XML.xsd">'
          || xmlcomment('For division ' || division_code)
          || xmlcomment('Original system group ' || :orig_system_group)
          || '<GLTransactions>'
          || xml
          || '</GLTransactions>'
          || '</eExact>'
         )
         filecontents
     from  ( select division_code
          ,   listagg
             ( head
              || lines
              || '</GLTransaction>'
             , ''
             )
             xml
          from  ( select brl.division_code
              ,   brl.head
              ,   listagg(brl.line, '') lines
              from  sourcelinesxml@inmemorystorage brl
              group
              by   brl.head
              ,   brl.division_code
             )
          group
          by   division_code
         )
     ;
     --
     -- Upload into Exact Online.
     --
     insert into UploadXMLTopics@eol
     ( topic
     , payload
     , division_code
     , orig_system_reference
     , orig_system_group
     , fragment_payload_flag
     , fragment_max_size_characters
     , fail_on_error
     )
     select topic
     ,   filecontents
     ,   to_char(division_code)
     ,   orig_system_reference
     ,   orig_system_group
     --
     -- Try to split into pieces of 25 KB at most.
     --
     ,   true fragment_payload_flag
     ,   25000 fragment_max_size_characters
     --
     -- Returns error through user interface, not as a HTTP 500 error.
     --
     ,   false fail_on_error
     from  xmlupload@inmemorystorage
     order
     by   division_code
     ,   orig_system_reference
     ,   orig_system_group

The code is somewhat complex to ensure it is able to handle uploads of 100K+ entry lines per division without disturbances.

Upload from Invantive Control

You can also journal entries through two ways from Invantive Control for Excel:

Upload using Invantive SQL

Perform the following steps:

  • Create a table with the journal entries to upload such as for example described on Get My Report manual.
  • Assign it a name like ‘mylines’ using the Name Manager of Excel.
  • Go to the Query Tool from the Modeller ribbon.
  • Open the script attached from Get My Report.
  • Replace the ‘from exceltable…’ by ‘mylines@ic’. This takes out parsing the Excel file as source and instead use the data from the mylines named range.
  • Run the script using F5.
  • You can also create a model by putting the script in the pre-sync trigger of the model using the model editor.

A more extensive explanation on uploading transaction entry lines based upon your original spreadsheet is available in the knowledge base.