Hoe laad ik factuurregels uit AutoTask in Exact Online?

Vragen
,
1

Ik gebruik de volgende query om Autotask facturen te laden in Exact Online, hoe kan ik de regels bij de Invoices uit AutoTask ophalen?

--
-- Laad facturen van Autotask in Exact Online.
--
use 528825@eol

create or replace table EOLExistingInvoices@InMemoryStorage
as
select sie.InvoiceNumber
from   SalesInvoiceLinesIncremental@eol sie
where  sie.LineNumber = 0
and    sie.Journal = 'VU'

create or replace table invoices@inmemorystorage
as
select cpy.companyNumber ExactOnlineAccountCode
,      cpy.companyName   Name
,      cpy.id
,      cpy.city
,      ive.invoiceNumber
,      ive.invoiceTotal
,      ive.invoiceDateTime
,      act.Id ExactOnlineAccountId
,      eolsie.invoiceNumber
from   InvoicesById@at(29507) ive /* Of andere ID voor test. */
join   CompaniesById@at(ive.CompanyId) cpy
left
outer
join   AccountsIncremental@eol act
on     act.Code = cpy.companyNumber
left
outer
join   EOLExistingInvoices@InMemoryStorage eolsie
on     eolsie.invoiceNumber = ive.invoiceNumber
--
-- Factuurnummer is onbekend in Exact Online.
--
where  eolsie.invoiceNumber is null

create or replace table InvoiceLines@inmemorystorage
as
select ive.invoiceNumber
,      gat.ID GLAccount
,      itm.Id Item
,      1 Quantity
,      itm.Description Description
,      1.23 AmountDC
from   invoices@inmemorystorage ive
join   ( select /*+ low_cost */ Id from ExactOnlineREST..GLAccounts@eol gat where code = '8101' ) gat
join   ( select * from exactonlinerest..items@eol itm where  itm.IsSalesItem = true limit 3 ) itm


begin transaction

insert into exactonlinerest..SalesInvoices@eol
( InvoiceNumber
, InvoiceTo
, OrderedBy
, DeliverTo
, Description
, Journal
, Status
, Type
) 
select null InvoiceNumber
,      ExactOnlineAccountId InvoiceTo
,      ExactOnlineAccountId OrderedBy
,      ExactOnlineAccountId DeliverTo
,      'Factuur ' || invoiceNumber description
,      'VU' Journal
,      50 /* 20=Open, 50=Processed. */
,      8020 Type /* 8020=Sales entry, 8021=Credit note. */
from   invoices@inmemorystorage
identified by 'INVOICE' || ive.InvoiceNumber

insert into ExactOnlineREST..SalesInvoiceLines@eol
( item
, AmountDC
, Description
, GLAccount
, Quantity
, linenumber
)
select ile.Item
,      ile.AmountDC
,      ile.Description
,      ile.GLAccount
,      ile.Quantity
,      row_number()
       linenumber
from   InvoiceLines@inmemorystorage ile
attach to 'INVOICE' || ile.InvoiceNumber

commit transaction
2

Regels bij AutoTask-facturen

De factuurregels bij de Autotask facturen zijn op te vragen via BillingItems zoals in deze query getoond:

select ive.InvoiceNumber
,      bim.sortOrderID
,      bim.ExtendedPrice
,      bim.ItemDate
,      bim.quantity
,      bim.rate
,      bim.totalAmount
,      bim.ItemName
,      bim.Description
,      bim.lineItemFullDescription
from   invoices ive
join   BillingItems bim
on     bim.InvoiceId = ive.ID
where  ive.InvoiceNumber = 1000
order
by     ive.InvoiceNumber
,      bim.sortOrderID

Het resultaat van de factuurregels van 1 Autotask factuur is bijvoorbeeld:

image
image1263×479 23 KB

Het veld InvoiceNumber zal over alle Autotask-facturen heen anders zijn, maar elke factuur in Autotask heeft 1 of meer regels.

Autotask facturen in Exact Online inlezen

Het laden van facturen kan via de koppeling SalesInvoiceLines; dan kan het artikel uit Autotask meegegeven worden en gekoppeld aan een Exact Online artikel. De facturen kunnen ook geladen worden in Exact Online via SalesEntryLines; dan kan het artikel NIET opgenomen worden op een factuur, maar hoeven de facturen ook niet eerst afgedrukt te worden vooraleer de facturen in de Exact Online administratie(s)/boekhouding(en) verschijnen.

De volgende integratie vergelijkt alle facturen uit Autotask met de verkoopboekingen in Exact Online en voegt alle niet-bestaande facturen uit Autotask toe aan Exact Online (de koppeling werkt niet met het Free Plan van Invantive omdat de Autotask-driver nodig is; de maandelijkse kosten zijn normaliter EUR 49, zie ook Interface Autotask naar Exact Online, 180 dagen gratis):

--
-- Laad facturen vanuit Autotask in Exact Online.
--
-- Alleen Autotask-facturen die nog niet met hetzelfde nummer geladen zijn
-- in Exact Online worden toegevoegd.
--
-- Instelopties via Invantive Script-variabelen:
--
-- * EOL_DIVISION_CODE: unieke divisiecode van de Exact Online administratie (zie ook
--   tabel SystemDivisions@eol).
-- * EOL_JOURNAL_CODE: Dagboek waar de Autotask-facturen in moeten komen (zie ook
--   tabel ExactOnlineREST..Journals@eol).
-- * EOL_CODE_NO_CUSTOMER: Code van de "Onbekende" klant als in Autotask bij 
--   het veld companyNumber niet een geldige Exact Online klantcode staat (zie ook
--   tabel AccountsIncremental@eol).
-- * EOL_FIXED_CURRENCY_CODE: standaard valuta om altijd te gebruiken.
-- * EOL_FIXED_GL_GL_ACCOUNT_CODE: vaste grootboekrekening waar alle omzet op geboekt
--   moet worden (zie ook tabel GLAccountsIncremental@eol).
--
-- Invantive Script is alleen beschikbaar in on-premises producten zoals
-- Invantive Query Tool.
-- Indien gebruik gemaakt wordt van Invantive Cloud, gebruik dan 
-- een HTML-invulscherm via de cloud_http-package en een Invantive Cloud applicatie.
--

local define EOL_DIVISION_CODE "102673"

local define EOL_JOURNAL_CODE "70"

local define EOL_CODE_NO_CUSTOMER "90910"

local define EOL_FIXED_CURRENCY_CODE "EUR"

local define EOL_FIXED_GL_ACCOUNT_CODE "8000"

--
-- Stel Exact Online administratie in.
--
use ${EOL_DIVISION_CODE}@eol

--
-- Facturen uit Autotask die al in Exact Online geregistreerd zijn
-- in het dagboek.
--
create or replace table EOLExistingInvoices@InMemoryStorage
as
select tle.EntryNumber
from   TransactionLinesIncremental@eol tle
where  tle.LineNumber = 0
and    tle.JournalCode = '${EOL_JOURNAL_CODE}'

--
-- Haal lijst van alle Autotask-facturen op.
--
-- Voeg indien nodig een filter toe zoals "niet ouder dan datum X"
-- om te voorkomen dat heel oude facturen alsnog in Exact Online 
-- geladen worden.
--
create or replace table Invoices@inmemorystorage
as
select ${EOL_DIVISION_CODE}
       ExactOnlineDivisionCode
       label 'Exact Online Division Code'
,      '${EOL_FIXED_CURRENCY_CODE}'
       Currency
       label 'Exact Online Currency Code'
,      coalesce(cpy.companyNumber, '${EOL_CODE_NO_CUSTOMER}') 
       ExactOnlineAccountCode
       label 'Exact Online Account Code'
,      cpy.companyName 
       CompanyName
       label 'Customer Name'
,      cpy.id 
       CompanyId
       label 'Customer ID'
,      cpy.city 
       CompanyCity
       label 'Customer City'
,      ive.Id 
       InvoiceId
       label 'Invoice ID'
,      ive.invoiceNumber
       label 'Invoice Number'
,      ive.invoiceDateTime
,      act.Id 
       ExactOnlineAccountId
       label 'Exact Online Account ID'
,      'Autotask #' || to_char(ive.invoiceNumber)
       YourRef
from   Invoices@at ive
--
-- Zoek Autotask-klant er bij.
--
join   Companies@at cpy
on     cpy.id = ive.companyid
--
-- Zoek Exact Online klant bij de Autotask-klant.
--
left
outer
join   AccountsIncremental@eol act
on     act.Code = coalesce(cpy.companyNumber, '${EOL_CODE_NO_CUSTOMER}')
--
-- Zoek een eventueel bestaande verkoopboeking erbij.
--
left
outer
join   EOLExistingInvoices@InMemoryStorage eoltle
on     eoltle.EntryNumber = ive.invoiceNumber
--
-- Autotask-factuurnummer is onbekend in Exact Online.
--
where  eoltle.entryNumber is null

--
-- Maak lijst van factuurregels.
--
create or replace table InvoiceLines@inmemorystorage
as
select ive.ExactOnlineDivisionCode
,      ive.InvoiceId
,      ive.invoiceNumber
,      bim.sortOrderID
       LineNumber
,      gat.ID 
       GLAccount
       label 'General Ledger Account ID'
,      bim.Quantity
,      bim.totalAmount
       AmountDC
,      coalesce(bim.Description, bim.ItemName)
       Description
       label 'Description'
--
,      bim.rate
,      bim.lineItemFullDescription
,      bim.ItemDate
from   Invoices@inmemorystorage ive
--
-- Zoek factuurregels uit Autotask er bij.
--
join   BillingItems@at bim
on     bim.InvoiceId = ive.InvoiceID
left
outer
join   ExactOnlineREST..GLAccounts@eol gat
on     gat.code = '${EOL_FIXED_GL_ACCOUNT_CODE}'
order
by     ive.InvoiceNumber
,      bim.sortOrderID

--
-- De Autotask-facturen worden geladen als een transactie per
-- factuurnummer. Elke transactie heeft een factuurkop uit Autotask,
-- plus alle bijbehorende regels, die in 1x ingelezen worden in
-- Exact Online.
--
-- Het gebruik van de Exact Online tabel UploadXMLTopics kan gebruikt
-- worden indien het gaat om meer dan 1.000 facturen per dag.
--
begin transaction

insert into exactonlinerest..SalesEntries@eol
( Division
, Customer
, Currency
, EntryNumber
, Description
, Journal
, YourRef
, EntryDate
) 
select ive.ExactOnlineDivisionCode
,      ive.ExactOnlineAccountId
,      ive.Currency
,      ive.InvoiceNumber
,      'Factuur ' || invoiceNumber description
,      '${EOL_JOURNAL_CODE}' Journal
,      ive.YourRef
,      ive.invoiceDateTime
from   invoices@inmemorystorage ive
identified by 'INVOICE-' || ive.InvoiceNumber

insert into ExactOnlineREST..SalesEntryLines@eol
( division
, AmountFC
, Description
, GLAccount
, Quantity
)
select ile.ExactOnlineDivisionCode
,      ile.AmountDC
,      ile.Description
,      ile.GLAccount
,      ile.Quantity
from   InvoiceLines@inmemorystorage ile
attach to 'INVOICE-' || ile.InvoiceNumber

commit transaction

Inrichting Integratie Autotask / Exact Online

Voor gebruik van deze integratie-query kan hetzij een Invantive Cloud-database gemaakt worden met zowel een Autotask als Exact Online-datacontainer, als een on-premises virtual database door een bestand settings-sample.xml te maken in de %USERPROFILE%\invantive map met de volgende inhoud.

Merk op dat de koppeling met zowel Autotask als Exact Online tegelijk verbinding maakt. Het is dus niet mogelijk om met twee losse databases te werken voor de koppeling, maar 1 database met 2 datacontainers: 1 voor de Exact Online administraties, 1 voor de Autotask-omgeving.

<?xml version="1.0" encoding="utf-16"?>
<settings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  VersionUpdateDate="2022-06-18T08:03:09.9107783Z"
  version="5">
  <group
    name="ACME"
    id="b9a1a317-f173-4410-8447-298fa4344a9a"
    sortingOrder="0"
   >
    <connection
      name="Exact Online (nl) + Autotask"
      authentication="Default"
      sortingOrder="10"
      id="c149becf-01d3-4a58-9ee7-7369f34bdfef">
      <database
        order="10"
        alias="at"
        provider="Autotask"
        userLogonCodeMode="Auto"
        PasswordLabel="{res:itgen_cloud_autotask_secret}"
        UserLogonCodeLabel="{res:itgen_cloud_autotask_api_username}"
        passwordMode="Auto"
        AllowConnectionPooling="false" />
      <database
        order="20"
        alias="eol"
        provider="ExactOnlineAll"
        userLogonCodeMode="Auto"
        passwordMode="Auto"
        connectionString="api-url=https://start.exactonline.nl"
        AllowConnectionPooling="false" />
    </connection>
  </group>
</settings>

Het gebruik van Autotask voor de koppeling van facturen vereist enige voorbereidingen in Autotask zelf qua API-instellingen; zie Bepaal Autotask API-gebruiker en wachtwoord voor Power BI connector en SQL.

Integratie Autotask-klanten naar Exact Online of andere gegevens uit Autotask

Op vergelijkbare wijze is het - naast voor facturen - ook eenvoudig mogelijk om klanten, contactpersonen en bijvoorbeeld configuratie-items in Exact Online te laden.

Via een Invantive Cloud-app kan er snel een HTML-user interface klaargezet worden waarna de facturen, klanten, etc. dagelijks automatisch met 1 klik geladen kunnen worden of automatisch afgetrapt via een HTTP-verzoek.

Een overzicht van de beschikbare Autotask-tabellen is te vinden in:

De Exact Online-tabellen staan in:

3

Deze vraag is automatisch gesloten na tenminste 2 weken inactiviteit nadat een mogelijk passend antwoord is gegeven. Het laatste gegeven antwoord is gemarkeerd als oplossing.

Gelieve een nieuwe vraag te stellen via een apart topic als het probleem opnieuw optreedt. Gelieve in de nieuwe vraag een link naar dit topic op te nemen door de URL er van in de tekst te plakken.

4

De uiteindelijke code in de vorm van een applicatiemodule van App Online om Autotask facturen in Exact Online die er nog niet in staan te laden is (aanmaken zoals beschreven in bijvoorbeeld App Online module om alle Exact Online verkoopfactuurregels te downloaden):

--
-- Laad facturen van Autotask in Exact Online.
-- 
declare
  l_workbook      excel_workbook;
  l_worksheet     excel_worksheet;
  l_binary        blob;
  l_xlsx_filename varchar2;
  l_cnt           pls_integer;
  l_cnt_error     pls_integer;
  l_sqlerrm       varchar2;
begin
  --
  -- Disable caches.
  -- Solely needed when running on Invantive Cloud.
  -- Invantive App Online has them disabled by default.
  --
  set use-http-disk-cache@eol false;
  set use-http-disk-cache@at false;
  set use-http-memory-cache@eol false;
  set use-http-memory-cache@at false;
  --
  -- Prepare logging.
  --
  create or replace table output@inmemorystorage
  as
  select sysdateutc
         date_created
         label 'Date/time'
  ,      cast('Line of text' as clob)
         txt
         label 'Text'
  from   dual@datadictionary
  where  1 = 0
  ;
  l_xlsx_filename := 'Autotask2ExactOnline-results.' || to_char(sysdateutc, 'YYYYMMDDHH24MISS') || '.xlsx';
  l_workbook := excel.new();
  l_worksheet := excel.add_worksheet
  ( l_workbook
  , 'Parameters'
  );
  excel.set_cell_contents(l_worksheet, 'A1', 'Upload invoices from Autotask to Exact Online using Invantive Cloud');
  excel.set_cell_contents(l_worksheet, 'A3', 'Created (UTC)');
  excel.set_cell_contents(l_worksheet, 'B3', sysdateutc);
  --
  l_worksheet := excel.add_worksheet
  ( l_workbook
  , 'Results'
  );
  --
  use 528825@eol
  ;
  create or replace table EOLExistingInvoices@InMemoryStorage
  as
  select tle.InvoiceNumber
  from   TransactionLinesIncremental@eol tle
  where  tle.LineNumber = 0
  and    tle.JournalCode = 'VU'
  ;
  create or replace table invoices@inmemorystorage
  as
  select cpy.companyNumber ExactOnlineAccountCode
  ,      cpy.companyName   Name
  ,      cpy.id
  ,      cpy.city
  ,      ive.Id invoiceId
  ,      ive.invoiceNumber
  ,      ive.invoiceTotal
  ,      ive.invoiceDateTime
         EntryDate
  ,      act.Id ExactOnlineAccountId
  ,      eolsie.invoiceNumber ExactOnlineInvoiceNumber
  from   Invoices@at ive
  join   Companies@at cpy
  on     cpy.id = ive.companyid
  left
  outer
  join   AccountsIncremental@eol act
  on     act.Code = cpy.companyNumber
  left
  outer
  join   EOLExistingInvoices@InMemoryStorage eolsie
  on     eolsie.invoiceNumber = ive.invoiceNumber
  where  1=1
  --
  -- Alleen geldige facturen.
  --
  and    ive.IsVoided = false
  and    ive.InvoiceDateTime >= add_months(trunc(sysdate), -3)
  --
  -- Conversiedatum.
  --
  and    ive.InvoiceDateTime >= to_date('20221220', 'YYYYMMDD')
  --
  -- Factuurnummer is onbekend in Exact Online.
  --
  and    eolsie.invoiceNumber is null
  order
  by     ive.Id
  ;
  create or replace table InvoiceLines@inmemorystorage
  as
  select ive.invoiceNumber
  ,      gat.ID GLAccount
  ,      itm.Id Item
  ,      bim.Quantity
  ,      bim.ItemName Description
  ,      bim.InternalCurrencyTotalAmount AmountFC /* Totaal prijs, altijd alles in EUR. */
  ,      bim.InternalCurrencyRate UnitPrice
  from   invoices@inmemorystorage ive
  join   GLAccountsIncremental@eol gat
  on     gat.Code = '8101'
  join   BillingItems@at bim
  on     bim.InvoiceId = ive.invoiceId
  --
  -- Alles op 'Autotask Factuur'-artikel.
  --
  join   ItemsIncremental@eol itm
  on     itm.Code = 'Autotask Factuur'
  order
  by     ive.invoiceNumber
  ,      itm.Id
  ,      bim.InternalCurrencyTotalAmount desc
  ;
  --
  -- Loop per invoice to continue loading rest
  -- after an individual error.
  --
  l_cnt := 0;
  l_cnt_error := 0;
  for r
  in
  ( select ive.InvoiceNumber
    ,      ive.ExactOnlineAccountId Customer
    ,      'Factuur ' || ive.invoiceNumber Description
    ,      'VU' Journal
    ,      EntryDate
    from   invoices@inmemorystorage ive
    order
    by     ive.InvoiceNumber
  )
  loop
    begin
      l_cnt := l_cnt + 1;
      insert into output@inmemorystorage
      ( date_created
      , txt
      )
      values
      ( sysdateutc
      , 'Upload Autotask invoice ' || r.InvoiceNumber || '.'
      );
      begin transaction
      ;
      insert into exactonlinerest..SalesEntries@eol
      ( EntryNumber
      , InvoiceNumber
      , Customer
      , Description
      , Journal
      , EntryDate
      )
      values
      ( r.InvoiceNumber
      , r.InvoiceNumber
      , r.Customer
      , r.Description
      , r.Journal
      , r.EntryDate
      )
      identified
      by     'INVOICE' || r.InvoiceNumber
      ;
      insert into ExactOnlineREST..SalesEntryLines@eol
      ( AmountFC
      , Description
      , GLAccount
      , Quantity
      , linenumber
      )
      select ile.AmountFC
      ,      ile.Description
      ,      ile.GLAccount
      ,      ile.Quantity
      ,      row_number()
             linenumber
      from   InvoiceLines@inmemorystorage ile
      where  ile.InvoiceNumber = r.InvoiceNumber
      attach
      to     'INVOICE' || ile.InvoiceNumber
      ;
      commit transaction
      ;
      insert into output@inmemorystorage
      ( date_created
      , txt
      )
      values
      ( sysdateutc
      , 'Uploaded Autotask invoice ' || r.InvoiceNumber || '.'
      );
    exception
      when others
      then
        l_sqlerrm := sqlerrm;
        rollback transaction;
        l_cnt_error := l_cnt_error + 1;
        insert into output@inmemorystorage
        ( date_created
        , txt
        )
        values
        ( sysdateutc
        , 'Upload Autotask invoice ' || r.InvoiceNumber || ' failed: ' || l_sqlerrm
        );
    end;
  end loop;
  --
  insert into output@inmemorystorage
  ( date_created
  , txt
  )
  values
  ( sysdateutc
  , 'Uploaded ' || to_char(l_cnt) || ' Autotask invoices.'
  );
  insert into output@inmemorystorage
  ( date_created
  , txt
  )
  values
  ( sysdateutc
  , 'Of which ' || to_char(l_cnt_error) || ' returned an error.'
  );
  --
  -- Add to Excel output.
  --
  excel.fill_using_query(l_worksheet, 'select date_created, txt from output@inmemorystorage order by date_created');
  --
  -- Retrieve the resulting XLSX-file.
  --
  l_binary := excel.export_to_xlsx(l_workbook);
  --
  -- Set status depending on number of errors.
  --
  --if l_cnt_error = 0
  --then 
  --  cloud_http.set_response_status_code(200);
  --else
  --  cloud_http.set_response_status_code(422);
  --end if;
  --
  -- Return the XLSX file to the user.
  --
  cloud_http.set_response_body_binary(l_binary);
  cloud_http.set_response_content_type('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  cloud_http.set_response_header_value('Content-Disposition', 'attachment; filename="' || l_xlsx_filename || '"');
end;