Voor de replicatie van Exact Online-tabellen in SQL Server kunnen scripts gebruikt worden zoals:
- Met Cloud: Elementaire datareplicatie tussen Exact Online en Azure SQL Server
- Met Data Hub: Kopieer Exact Online naar SQL Server
Echter, voor serieus grote omgevingen is het gebruik van de *Incremental-tabellen of Invantive Cloud af te raden. Beter schaalbaar is het gebruik van TransactionLinesBulk met filter op datum en Invantive Data Hub.
Het volgende script kopieert een Exact Online-administratie met meer dan 20 miljoen boekingen naar SQL Server. Vanaf een begindatum worden de transacties ingelezen en zodra bij wordt de laatste week telkens bijgewerkt.
Een audit trail is terug te vinden in de eol_sync_logs-tabel op SQL Server.
--
-- Kopieer Exact Online naar DWH.
--
-- Voorbereiding:
--
-- create or replace table eol_transaction_lines@sql
-- as
-- select *
-- from TransactionLinesBulk@eol
-- where Date = trunc(sysdateutc)
-- and false
--
-- create or replace table eol_sync_settings@sql
-- --
-- -- Functioneel.
-- --
-- ( date_last_completed_tle date /* De laatste datum voor welke alle transactieregels gekopieerd zijn. */
-- --
-- -- Audit.
-- --
-- , date_last_run_tle datetime /* De laatste datum/tijd waarop de transactieregels laatste keer bijgewerkt zijn. */
-- )
--
-- insert into eol_sync_settings@sql
-- ( date_last_completed_tle
-- , date_last_run_tle
-- )
-- values
-- ( to_date('20200101', 'YYYYMMDD') - 1
-- , null
-- )
--
-- create or replace table eol_sync_logs@sql
-- ( id guid not null
-- , occurrence_date datetime not null -- UTC
-- , action_type char not null -- 'I', 'D' or 'U' for Insert, Delete or Update.
-- , table_name varchar2 not null
-- , nk1 varchar2 not null -- First element of business key.
-- , nk2 varchar2 null
-- , nk3 varchar2 null
-- , nk4 varchar2 null
-- , nk5 varchar2 null
-- , label varchar2 null -- Textual background.
-- , actions varchar2 not null -- Changed values.
-- --
-- , ip_address varchar2 not null -- Audit fields.
-- , iuid varchar2 not null
-- , date_created datetime not null
-- , created_by varchar2 not null
-- , created_at varchar2 not null
-- , session_created varchar2 not null
-- )
--
use all@eol
set use-http-memory-cache@eol false
set use-http-disk-cache@eol false
--
-- Full copies.
--
create or replace table eol_journals@sql
as
select *
from exactonlinerest..journals@eol
create or replace table eol_glaccounts@sql
as
select *
from GLAccountsIncremental@eol
--
-- Incrementele kopie van TransactionLinesBulk.
--
-- Er wordt bewust geen TransactionLinesIncremental gebruikt
-- omdat de image dan tientallen miljoenen rijen zou bevatten.
--
-- Een glijdend venster van 7 dagen wordt elke dag opnieuw berekend
-- en geladen in het datawarehouse.
--
-- De code is herstartbaar. Mocht terugzetten nodig zijn naar een eerdere
-- startdatum, vervang dan de datum in cs_settings.
--
declare
--
-- Historisch venster om te laden.
--
g_window_days pls_integer := 7;
--
-- 3 maanden plus historisch venster.
--
g_max_days pls_integer := 93 + 7;
--
l_date_last_completed_tle date;
l_date_last_completed_tle_orig date;
l_loop boolean;
l_loop_cnt pls_integer;
begin
select date_last_completed_tle
into l_date_last_completed_tle
from eol_sync_settings@sql
;
l_date_last_completed_tle_orig := l_date_last_completed_tle;
--
-- Shift back one week to reload recent days over and over again.
--
l_date_last_completed_tle := l_date_last_completed_tle - g_window_days;
l_loop_cnt := 0;
--
l_loop := l_loop_cnt <= g_max_days and l_date_last_completed_tle < trunc(sysdateutc);
--
begin
while l_loop
loop
dbms_output.put_line('Laadactie #' || to_char(l_loop_cnt) || ': ' || to_char(l_date_last_completed_tle + 1, 'DD-MM-YYYY') || '.');
delete eol_transaction_lines@sql
where Date = l_date_last_completed_tle + 1
;
insert into eol_sync_logs@sql
( id
, occurrence_date
, action_type
, table_name
, nk1
, nk2
, nk3
, nk4
, nk5
, label
, actions
--
, ip_address
, iuid
, date_created
, created_by
, created_at
, session_created
)
values
( newid()
, sysdateutc
, 'D'
, 'eol_transaction_lines'
, to_char(l_date_last_completed_tle, 'YYYYMMDD')
, null
, null
, null
, null
, 'Delete old version'
, to_char(sqlrowcount) || ' rijen verwijderd.'
--
, sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'eol')
, sys_context('USERENV', 'IUID', 'eol')
, sysdateutc
, sys_context('USERENV', 'CURRENT_USER', 'eol')
, sys_context('USERENV', 'APPLICATION_FULL', 'eol')
, sys_context('USERENV', 'SESSIONID', 'eol')
);
--
bulk insert into eol_transaction_lines@sql
( Account
, AccountCode
, AccountName
, AmountDC
, AmountFC
, AmountVATBaseFC
, AmountVATFC
, Asset
, AssetCode
, AssetDescription
, CostCenter
, CostCenterDescription
, CostUnit
, CostUnitDescription
, Created
, Creator
, CreatorFullName
, Currency
, CustomField
, Date
, Description
, Division
, Document
, DocumentNumber
, DocumentSubject
, DueDate
, EntryID
, EntryNumber
, ExchangeRate
, ExternalLinkDescription
, ExternalLinkReference
, ExtraDutyAmountFC
, ExtraDutyPercentage
, FinancialPeriod
, FinancialYear
, GLAccount
, GLAccountCode
, GLAccountDescription
, ID
, InvoiceNumber
, Item
, ItemCode
, ItemDescription
, JournalCode
, JournalDescription
, LineNumber
, LineType
, Modified
, Modifier
, ModifierFullName
, Notes
, OffsetID
, OrderNumber
, PaymentDiscountAmount
, PaymentReference
, Project
, ProjectCode
, ProjectDescription
, Quantity
, SerialNumber
, ShopOrder
, Status
, Subscription
, SubscriptionDescription
, TrackingNumber
, TrackingNumberDescription
, Type
, VATCode
, VATCodeDescription
, VATPercentage
, VATType
, YourRef
, CreatedUtc
, ModifiedUtc
, DivisionShortName
, DivisionCompanyCoCNumber
, DivisionCompanyIBANNumber
, DivisionCompanyRSINNumber
, DivisionCompanyVATNumber
, DivisionCompanyWageTaxNumber
, DivisionName
, DivisionLabel
, DivisionCompanyName
, DivisionOwnerCompanyName
, DivisionOwnerCompanyNumber
, DisplayDocumentsUrl
, GLAccountType
, GLAccountBalanceSide
, GLAccountBalanceType
, GLAccountRevalueCurrency
, GLAccountSearchCode
, GLAccountTypeDescription
, GLAccountUseCostCenter
, GLAccountUseCostUnit
, GLAccountVatCode
)
select tle.Account
, tle.AccountCode
, tle.AccountName
, tle.AmountDC
, tle.AmountFC
, tle.AmountVATBaseFC
, tle.AmountVATFC
, tle.Asset
, tle.AssetCode
, tle.AssetDescription
, tle.CostCenter
, tle.CostCenterDescription
, tle.CostUnit
, tle.CostUnitDescription
, tle.Created
, tle.Creator
, tle.CreatorFullName
, tle.Currency
, tle.CustomField
, tle.Date
, tle.Description
, tle.Division
, tle.Document
, tle.DocumentNumber
, tle.DocumentSubject
, tle.DueDate
, tle.EntryID
, tle.EntryNumber
, tle.ExchangeRate
, tle.ExternalLinkDescription
, tle.ExternalLinkReference
, tle.ExtraDutyAmountFC
, tle.ExtraDutyPercentage
, tle.FinancialPeriod
, tle.FinancialYear
, tle.GLAccount
, tle.GLAccountCode
, tle.GLAccountDescription
, tle.ID
, tle.InvoiceNumber
, tle.Item
, tle.ItemCode
, tle.ItemDescription
, tle.JournalCode
, tle.JournalDescription
, tle.LineNumber
, tle.LineType
, tle.Modified
, tle.Modifier
, tle.ModifierFullName
, tle.Notes
, tle.OffsetID
, tle.OrderNumber
, tle.PaymentDiscountAmount
, tle.PaymentReference
, tle.Project
, tle.ProjectCode
, tle.ProjectDescription
, tle.Quantity
, tle.SerialNumber
, tle.ShopOrder
, tle.Status
, tle.Subscription
, tle.SubscriptionDescription
, tle.TrackingNumber
, tle.TrackingNumberDescription
, tle.Type
, tle.VATCode
, tle.VATCodeDescription
, tle.VATPercentage
, tle.VATType
, tle.YourRef
, tle.CreatedUtc
, tle.ModifiedUtc
, tle.DivisionShortName
, tle.DivisionCompanyCoCNumber
, tle.DivisionCompanyIBANNumber
, tle.DivisionCompanyRSINNumber
, tle.DivisionCompanyVATNumber
, tle.DivisionCompanyWageTaxNumber
, tle.DivisionName
, tle.DivisionLabel
, tle.DivisionCompanyName
, tle.DivisionOwnerCompanyName
, tle.DivisionOwnerCompanyNumber
, tle.DisplayDocumentsUrl
, tle.GLAccountType
, tle.GLAccountBalanceSide
, tle.GLAccountBalanceType
, tle.GLAccountRevalueCurrency
, tle.GLAccountSearchCode
, tle.GLAccountTypeDescription
, tle.GLAccountUseCostCenter
, tle.GLAccountUseCostUnit
, tle.GLAccountVatCode
from TransactionLinesBulk@eol tle
where tle.Date = l_date_last_completed_tle + 1
--
-- Ontcommentarieren voor test modus om snel een aantal dagen door te testen.
-- limit 1000
;
dbms_output.put_line('Laadactie #' || to_char(l_loop_cnt) || ': ' || to_char(sqlrowcount) || ' rijen verwijderd en weer geladen.');
insert into eol_sync_logs@sql
( id
, occurrence_date
, action_type
, table_name
, nk1
, nk2
, nk3
, nk4
, nk5
, label
, actions
--
, ip_address
, iuid
, date_created
, created_by
, created_at
, session_created
)
values
( newid()
, sysdateutc
, 'I'
, 'eol_transaction_lines'
, to_char(l_date_last_completed_tle, 'YYYYMMDD')
, null
, null
, null
, null
, 'Insert new version'
, to_char(sqlrowcount) || ' rijen toegevoegd.'
--
, sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'eol')
, sys_context('USERENV', 'IUID', 'eol')
, sysdateutc
, sys_context('USERENV', 'CURRENT_USER', 'eol')
, sys_context('USERENV', 'APPLICATION_FULL', 'eol')
, sys_context('USERENV', 'SESSIONID', 'eol')
);
--
l_date_last_completed_tle := l_date_last_completed_tle + 1;
l_loop_cnt := l_loop_cnt + 1;
--
-- Doorgaan met laden? Nooit vandaag of later, en nooit meer dan 100 dagen.
--
l_loop := l_loop_cnt <= g_max_days and l_date_last_completed_tle < trunc(sysdateutc);
end loop;
exception
when others
then
--
-- Verplaatsen naar laatste afgeronde laadactie.
--
update eol_sync_settings@sql
set date_last_completed_tle = l_date_last_completed_tle
, date_last_run_tle = sysdateutc
;
insert into eol_sync_logs@sql
( id
, occurrence_date
, action_type
, table_name
, nk1
, nk2
, nk3
, nk4
, nk5
, label
, actions
--
, ip_address
, iuid
, date_created
, created_by
, created_at
, session_created
)
values
( newid()
, sysdateutc
, 'U'
, 'eol_sync_settings'
, to_char(l_date_last_completed_tle, 'YYYYMMDD')
, null
, null
, null
, null
, 'Verschuif laatste correcte laadactie na foutmelding'
, sqlerrm
--
, sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'eol')
, sys_context('USERENV', 'IUID', 'eol')
, sysdateutc
, sys_context('USERENV', 'CURRENT_USER', 'eol')
, sys_context('USERENV', 'APPLICATION_FULL', 'eol')
, sys_context('USERENV', 'SESSIONID', 'eol')
);
--
raise;
end;
--
-- Verplaatsen naar laatste afgeronde laadactie.
--
update eol_sync_settings@sql
set date_last_completed_tle = l_date_last_completed_tle
, date_last_run_tle = sysdateutc
;
insert into eol_sync_logs@sql
( id
, occurrence_date
, action_type
, table_name
, nk1
, nk2
, nk3
, nk4
, nk5
, label
, actions
--
, ip_address
, iuid
, date_created
, created_by
, created_at
, session_created
)
values
( newid()
, sysdateutc
, 'U'
, 'eol_sync_settings'
, to_char(l_date_last_completed_tle, 'YYYYMMDD')
, null
, null
, null
, null
, 'Verschuif laatste correcte laadactie'
, 'Geslaagd.'
--
, sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'eol')
, sys_context('USERENV', 'IUID', 'eol')
, sysdateutc
, sys_context('USERENV', 'CURRENT_USER', 'eol')
, sys_context('USERENV', 'APPLICATION_FULL', 'eol')
, sys_context('USERENV', 'SESSIONID', 'eol')
);
end;