Itgenqtl105 Extractie boekingen Exact Online

Vragen
,
1

Via onderstaande query worden boekingen uit de verschillende (624) Exact Online Administraties geëxporteerd.

Echter, de query wordt succesvol uitgevoerd, ik zie alleen het resultaat nergens terug cq. per administratie geëxporteerd in Excel formaat.

Schermafbeelding 2023-02-06 111234 extractie boekingen
Schermafbeelding 2023-02-06 111234 extractie boekingen2163×1110 263 KB

Onderstaande query:

set requests-parallel-max@eol 16

use select code, 'eol' from systemdivisions@eol limit 2

set use-http-disk-cache@eol true

set http-disk-cache-max-age-sec@eol 86400

create or replace table tle@mss
( divisionownercompanyname     varchar2 label '{res:itgen_eol_division_owner_company_name}'
, divisionownercompanynumber   varchar2 label '{res:itgen_eol_division_owner_company_number}'
, divisionlabel                varchar2 label '{res:itgen_eol_division_label}'
, financialyear                int16    label '{res:itgen_eol_financialyear}'
, financialperiod              int16    label '{res:itgen_eol_financialperiod}'
, date                         datetime label '{res:itgen_eol_date}'
, journalcode                  varchar2 label '{res:itgen_eol_journalcode}'
, journaldescription           varchar2 label '{res:itgen_eol_journaldescription}'
, entrynumber                  int32    label '{res:itgen_eol_entrynumber}'
, linenumber                   int32    label '{res:itgen_eol_linenumber}'
, amountdc                     double   label '{res:itgen_eol_amountdc}'
, currency                     varchar2 label '{res:itgen_eol_currency}'
, amountfc                     double   label '{res:itgen_eol_amountfc}'
, amountvatbasefc              double   label '{res:itgen_eol_amountvatbasefc}'
, amountvatfc                  double   label '{res:itgen_eol_amountvatfc}'
, CostCenter                   varchar2 label '{res:itgen_eol_costcenter}'
, CostCenterDescription        varchar2 label '{res:itgen_eol_costcenter_description}'
, CostUnit                     varchar2 label '{res:itgen_eol_costunit}'
, CostUnitDescription          varchar2 label '{res:itgen_eol_costunit_description}'
, description                  varchar2 label '{res:itgen_eol_description}'
, yourref                      varchar2 label '{res:itgen_eol_yourref}'
, glaccountcode                varchar2 label '{res:itgen_eol_glaccountcode}'
, glaccountdescription         varchar2 label '{res:itgen_eol_glaccountdescription}'
, linetype                     int16    label '{res:itgen_eol_linetype}'
, notes                        varchar2 label '{res:itgen_eol_notes}'
, paymentdiscountamount        double   label '{res:itgen_eol_paymentdiscountamount}'
, paymentreference             varchar2 label '{res:itgen_eol_paymentreference}'
, quantity                     double   label '{res:itgen_eol_quantity}'
, status                       int16    label '{res:itgen_eol_status}'
, type                         int32    label '{res:itgen_eol_type}'
, exchangerate                 double   label '{res:itgen_eol_exchangerate}'
, accountcode                  varchar2 label '{res:itgen_eol_accountcode}'
, accountname                  varchar2 label '{res:itgen_eol_accountname}'
, itemcode                     varchar2 label '{res:itgen_eol_itemcode}'
, itemdescription              varchar2 label '{res:itgen_eol_itemdescription}'
, projectcode                  varchar2 label '{res:itgen_eol_projectcode}'
, projectdescription           varchar2 label '{res:itgen_eol_projectdescription}'
, ordernumber                  int32    label '{res:itgen_eol_ordernumber}'
, invoicenumber                int32    label '{res:itgen_eol_invoicenumber}'
, duedate                      datetime label '{res:itgen_eol_duedate}'
, vatcode                      varchar2 label '{res:itgen_eol_vatcode}'
, vatcodedescription           varchar2 label '{res:itgen_eol_vatcodedescription}'
, vatpercentage                double   label '{res:itgen_eol_vatpercentage}'
, vattype                      varchar2 label '{res:itgen_eol_vattype}'
, extradutyamountfc            double   label '{res:itgen_eol_extradutyamountfc}'
, extradutypercentage          double   label '{res:itgen_eol_extradutypercentage}'
, glaccounttype                int32    label '{res:itgen_eol_gl_account_type}'
, glaccountbalanceside         varchar2 label '{res:itgen_eol_gl_account_balance_side}'
, glaccountbalancetype         varchar2 label '{res:itgen_eol_gl_account_balance_type}'
, glaccountsearchcode          varchar2 label '{res:itgen_eol_gl_account_search_code}'
, glaccounttypedescription     varchar2 label '{res:itgen_eol_gl_account_type_description}'
, SubscriptionNumber           int32    label '{res:itgen_eol_subscription_number}'
, SubscriptionDescription      varchar2 label '{res:itgen_eol_subscription_description}'
, created                      datetime label '{res:itgen_eol_created}'
, creatorfullname              varchar2 label '{res:itgen_eol_creatorfullname}'
, modified                     datetime label '{res:itgen_eol_modified}'
, modifierfullname             varchar2 label '{res:itgen_eol_modifierfullname}'
, divisionshortname            int64    label '{res:itgen_eol_division_short_name}'
, divisioncompanycocnumber     varchar2 label '{res:itgen_eol_division_coc_number}'
, divisioncompanyibannumber    varchar2 label '{res:itgen_eol_division_iban_number}'
, divisioncompanyrsinnumber    varchar2 label '{res:itgen_eol_division_rsin_number}'
, divisioncompanyvatnumber     varchar2 label '{res:itgen_eol_division_vat_number}'
, divisioncompanywagetaxnumber varchar2 label '{res:itgen_eol_division_wage_tax_number}'
, divisionname                 varchar2 label '{res:itgen_eol_division_name}'
, divisioncompanyname          varchar2 label '{res:itgen_eol_division_company_name}'
, division                     int32    label '{res:itgen_eol_division}'
)

bulk insert into tle@mss
( divisionownercompanyname
, divisionownercompanynumber
, divisionlabel
, financialyear
, financialperiod
, date
, journalcode
, journaldescription
, entrynumber
, linenumber
, amountdc
, currency
, amountfc
, amountvatbasefc
, amountvatfc
, CostCenter
, CostCenterDescription
, CostUnit
, CostUnitDescription
, description
, yourref
, glaccountcode
, glaccountdescription
, linetype
, notes
, paymentdiscountamount
, paymentreference
, quantity
, status
, type
, exchangerate
, accountcode
, accountname
, itemcode
, itemdescription
, projectcode
, projectdescription
, ordernumber
, invoicenumber
, duedate
, vatcode
, vatcodedescription
, vatpercentage
, vattype
, extradutyamountfc
, extradutypercentage
, glaccounttype
, glaccountbalanceside
, glaccountbalancetype
, glaccountsearchcode
, glaccounttypedescription
, SubscriptionNumber
, SubscriptionDescription
, created
, creatorfullname
, modified
, modifierfullname
, divisionshortname
, divisioncompanycocnumber
, divisioncompanyibannumber
, divisioncompanyrsinnumber
, divisioncompanyvatnumber
, divisioncompanywagetaxnumber
, divisionname
, divisioncompanyname
, division
)
select tle.DivisionOwnerCompanyName
,      tle.DivisionOwnerCompanyNumber
,      tle.DivisionLabel
,      tle.FinancialYear
,      tle.FinancialPeriod
,      tle.Date
,      tle.JournalCode
,      tle.JournalDescription
,      tle.EntryNumber
,      tle.LineNumber
,      tle.AmountDC
,      tle.Currency
,      tle.AmountFC
,      tle.AmountVATBaseFC
,      tle.AmountVATFC
,      tle.CostCenter
,      tle.CostCenterDescription
,      tle.CostUnit
,      tle.CostUnitDescription
,      tle.Description
,      tle.YourRef
,      tle.GLAccountCode
,      tle.GLAccountDescription
,      tle.LineType
,      tle.Notes
,      tle.PaymentDiscountAmount
,      tle.PaymentReference
,      tle.Quantity
,      tle.Status
,      tle.Type
,      tle.ExchangeRate
--
-- ERP module specific columns.
--
,      tle.AccountCode
,      tle.AccountName
,      tle.ItemCode
,      tle.ItemDescription
,      tle.ProjectCode
,      tle.ProjectDescription
,      tle.OrderNumber
,      tle.InvoiceNumber
,      tle.DueDate
--
-- Generic fields.
--
,      tle.VATCode
,      tle.VATCodeDescription
,      tle.VATPercentage
,      tle.VATType
,      tle.ExtraDutyAmountFC
,      tle.ExtraDutyPercentage
,      tle.GLAccountType
,      tle.GLAccountBalanceSide
,      tle.GLAccountBalanceType
,      tle.GLAccountSearchCode
,      tle.GLAccountTypeDescription
,      ssn.Number 
       SubscriptionNumber
,      ssn.Description
       SubscriptionDescription
,      tle.Created
,      tle.CreatorFullName
,      tle.Modified
,      tle.ModifierFullName
,      tle.DivisionShortName
,      tle.DivisionCompanyCoCNumber
,      tle.DivisionCompanyIBANNumber
,      tle.DivisionCompanyRSINNumber
,      tle.DivisionCompanyVATNumber
,      tle.DivisionCompanyWageTaxNumber
,      tle.DivisionName
,      tle.DivisionCompanyName
,      tle.division
from   transactionlinesbulk@eol tle
left
outer
join   ExactOnlineREST..Subscriptions@eol ssn
on     ssn.Division = tle.Division
and    ssn.EntryId  = tle.Subscription

--select /*+ result_set_name("TransactionLines") */
--       *
--from   tle@inmemorystorage tle
--order
--by     tle.division
--,      tle.journalcode
--,      tle.entrynumber
2

De vraag is niet geheel duidelijk.

In het script zit alleen een export naar de datacontainer mss; waarschijnlijk een Microsoft SQL Server-database, met de tabel tle.

Een export naar Excel is beperkt aan te raden; Excel kan maximaal pakweg 1 miljoen regels bevatten per werkblad. Een export zou er als volgt aan toegevoegd kunnen worden:

select *
from   tle@mss
where  ...criteria...
order
by     divisionlabel
limit  10000

local export results as "c:\pad\excel.xlsx" format xlsx include technical headers

Eventueel kan per wisseling van divisie een ander spreadsheet gemaakt worden met code zoals:

local export results as "c:\temp\prefix.xlsx"  format xlsx split on DivisionLabel include technical headers

Tussen prefix en .xlsx wordt dan standaard een teller ingevoegd plus voorafgaande dash (‘-’).

Een nog meer geavanceerde oplossing is met zelf bepalen van de bestandsnaam:

select 'c:\temp\export-' || normalize(divisionlabel, 48, false) || '.xlsx' filename
,      *
from   tle@mss
where  ...criteria...
order
by     divisionlabel
limit  10000

local export results using filename column filename format xlsx split on DivisionLabel include technical headers

De bestandsnamen van de Exact Online-dump bevatten dan de naam van de administratie, na passend maken in 48 tekens en zonder tekens die leiden tot een ongeldige bestandsnaam dankzij de normalize functie.