Exporteer controlelijst voor SEPA betaalbestanden in meerdere Exact Online-administraties

Wiki
,
1

Vaak willen bestuurders een controlelijst voor geplande betalingen.

Het volgende Invantive UniversqlSQL-script haalt voor dit doel uit een periode alle SEPA-betaalbestanden op uit alle Exact Online-administraties en maakt een Excel-werkboek met alle betalingen. Als laatste kolom staat er een URL die de onderliggende factuur toont.

use all

set ignore-document-download-errors true

create or replace table PaymentLines@InMemoryStorage
as
select dct.Created label 'Aangemaakt'
,      dct.DivisionName label 'Dossier'
,      xte.ReqdExctnDt label 'Betalingsdatum'
,      xte.DbtrAcctIBAN label 'Eigen rekening'
,      bat.AccountName label 'Relatie'
,      xte.CdtrAcctIban label 'bankrekening'
,      coalesce(tlepay.EntryNumber, tleyour.EntryNumber) EntryNumber label 'Boekstuknummer'
,      coalesce(xte.TxRef, xte.TxUstrd) Reference label 'Betalingsreferentie'
,      xte.TxAmt label 'Bedrag'
,      coalesce(dfepaypdf.Url, dfeyourpdf.Url) URL
from   documentsbulk dct
join   documentattachmentfiles daf
on     daf.division = dct.division
and    daf.document = dct.id
and    daf.filename like '%.xml'
join   xmltable
       ( '/Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf'
         passing replace
                 ( cast(daf.AttachmentFromUrl as varchar2)
                 , 'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" '
                 , ''
                 )
         columns CtrlAmt         number   path '../CtrlSum'
         ,       ReqdExctnDt     date     path '../ReqdExctnDt'
         ,       DbtrNm          varchar2 path '../Dbtr/Nm'
         ,       DbtrAcctIBAN    varchar2 path '../DbtrAcct/Id/IBAN'
         ,       TxAmt           number   path 'Amt/InstdAmt'
         ,       CdtrNm          varchar2 path 'Cdtr/Nm'
         ,       CdtrAcctIban    varchar2 path 'CdtrAcct/Id/IBAN'
         ,       TxRef           varchar2 path 'RmtInf/Strd/CdtrRefInf/Ref'
         ,       TxUstrd         varchar2 path 'RmtInf/Ustrd'
       ) xte
--
-- Note: there can be multiple accounts with the same IBAN.
--
left
outer
join   ExactOnlineREST..BankAccounts bat
on     bat.Division = daf.Division
and    bat.IBAN     = xte.CdtrAcctIban
left
outer
join   TransactionLinesIncremental tlepay
on     tlepay.Division         = daf.Division
and    tlepay.Account          = bat.Account
and    tlepay.PaymentReference = coalesce(xte.TxRef, xte.TxUstrd)
and    tlepay.Type             != 40 /* Not the cash flow. */
and    tlepay.LineNumber       = 0 /* Header. */
left
outer
join   TransactionLinesIncremental tleyour
on     tleyour.Division         = daf.Division
and    tleyour.Account          = bat.Account
and    tleyour.YourRef          = coalesce(xte.TxRef, xte.TxUstrd)
and    tleyour.Type             != 40 /* Not the cash flow. */
and    tleyour.LineNumber       = 0 /* Header. */
left
outer
join   DocumentAttachmentFiles dfepaypdf
on     dfepaypdf.Division = bat.Division
and    dfepaypdf.Document = tlepay.Document
and    dfepaypdf.FileName like '%.pdf'
and    tlepay.Document is not null
left
outer
join   DocumentAttachmentFiles dfeyourpdf
on     dfeyourpdf.Division = bat.Division
and    dfeyourpdf.Document = tleyour.Document
and    dfeyourpdf.FileName like '%.pdf'
and    tleyour.Document is not null
where  dct.Type = 41 /* Bank export file */
and    dct.DocumentDate >= add_months(trunc(sysdateutc) - to_char(sysdateutc, 'D') + 1, -2) /* Since Monday two months ago. */

select ple.*
from   PaymentLines@InMemoryStorage ple
order
by     ple.DivisionName
,      ple.ReqdExctnDt
,      ple.AccountName

local export results as filename '${system:userdesktopdirectory}\Betalingen.xlsx' format xlsx include headers