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:
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: