Exporteren inkoopfacturen uit Exact Online ter verdere verwerking in nacalculatie

Wiki
, ,
1

Het volgende script voor Invantive Query Tool en/of Invantive Data Hub exporteer inkoopfacturen, bijbehorende regels en documenten naar een map t.b.v. verdere verwerking in nacalculatiesoftware:

--
-- Exporteer inkoopfacturen, inkoopfactuurregels en documenten uit Exact Online voor
-- verdere verwerking in andere software.
--
-- Vereist invoer van het start boekingsnummer vanaf waar de inkoopfacturen geexporteerd
-- dienen te worden.
--
-- History
--
-- DD-MM-YYYY  Int  Description
-- =============================================================================
-- 21-05-2026  JDO  Add history.
--

local undefine START_NUMBER

--
-- Uitsterren om handmatig te kunnen invoeren.
--
--local define START_NUMBER "26600812"

local define OUT_PATH "${system:userdocumentsdirectory}\invantive"

create or replace table pey@InMemoryStorage
as
select pey.EntryNumber
,      pey.AmountDC
,      pey.AmountFC
,      pey.Description
,      pey.DueDate
,      pey.EntryDate
,      pey.InvoiceNumber
,      pey.Journal
,      pey.OrderNumber
,      pey.PaymentCondition
,      pey.PaymentReference
,      pey.ReportingPeriod
,      pey.Reportingyear
,      pey.SupplierName
,      pey.TypeDescription
,      pey.VATAmountDC
,      pey.VATAmountFC
,      pey.YourRef
from   PurchaseEntries pey
where  pey.EntryNumber >= ${START_NUMBER}
order
by     pey.EntryNumber

create or replace table ple@InMemoryStorage
as
select pey.EntryNumber
,      ple.LineNumber
,      ple.AmountDC
,      ple.AmountFC
,      ple.CostCenter
,      ple.CostUnit
,      ple.Description
,      ple.GLAccountCode
,      ple.GLAccountDescription
,      ple.Notes
,      ple.ProjectDescription
,      ple.Quantity
,      ple.SerialNumber
,      ple.[From]
,      ple.To
,      ple.VATAmountDC
,      ple.VATAmountFC
,      ple.VATCode
from   PurchaseEntries pey
join   PurchaseEntryLines ple
on     ple.Division = pey.Division
and    ple.EntryID  = pey.EntryID
where  pey.EntryNumber >= ${START_NUMBER}
order
by     pey.EntryNumber
,      ple.LineNumber

create or replace table dct@inmemorystorage
as
select pey.EntryNumber
,      dct.AccountCode
,      dct.AccountName
,      dct.DocumentDate
,      dct.Subject
,      dct.TypeDescription
,      fle.FileName
,      fle.FileSize
,      fle.AttachmentFromUrl
,      normalize
       ( pey.EntryNumber
         || '-'
         || dct.AccountName
         || '-'
         || fle.FileName
       , 240
       , true
       )
       FileNameComplete
from   PurchaseEntries pey
join   DocumentsBulk dct
on     dct.Division = pey.Division
and    dct.FinancialTransactionEntryId = pey.EntryId
join   DocumentAttachmentFiles fle
on     fle.Document = dct.ID
where  pey.EntryNumber >= ${START_NUMBER}

select *
from   pey@InMemoryStorage

local export results as "${OUT_PATH}\inkoopfacturen.xlsx" format xlsx include technical headers include sql

select *
from   ple@InMemoryStorage

local export results as "${OUT_PATH}\inkoopfactuurregels.xlsx" format xlsx include technical headers include sql

select *
from   dct@inmemorystorage

local export documents in AttachmentFromUrl to "${OUT_PATH}\documents" filename column FileNameComplete