De uiteindelijke code in de vorm van een applicatiemodule van App Online om Autotask facturen in Exact Online die er nog niet in staan te laden is (aanmaken zoals beschreven in bijvoorbeeld App Online module om alle Exact Online verkoopfactuurregels te downloaden):
--
-- Laad facturen van Autotask in Exact Online.
--
declare
l_workbook excel_workbook;
l_worksheet excel_worksheet;
l_binary blob;
l_xlsx_filename varchar2;
l_cnt pls_integer;
l_cnt_error pls_integer;
l_sqlerrm varchar2;
begin
--
-- Disable caches.
-- Solely needed when running on Invantive Cloud.
-- Invantive App Online has them disabled by default.
--
set use-http-disk-cache@eol false;
set use-http-disk-cache@at false;
set use-http-memory-cache@eol false;
set use-http-memory-cache@at false;
--
-- Prepare logging.
--
create or replace table output@inmemorystorage
as
select sysdateutc
date_created
label 'Date/time'
, cast('Line of text' as clob)
txt
label 'Text'
from dual@datadictionary
where 1 = 0
;
l_xlsx_filename := 'Autotask2ExactOnline-results.' || to_char(sysdateutc, 'YYYYMMDDHH24MISS') || '.xlsx';
l_workbook := excel.new();
l_worksheet := excel.add_worksheet
( l_workbook
, 'Parameters'
);
excel.set_cell_contents(l_worksheet, 'A1', 'Upload invoices from Autotask to Exact Online using Invantive Cloud');
excel.set_cell_contents(l_worksheet, 'A3', 'Created (UTC)');
excel.set_cell_contents(l_worksheet, 'B3', sysdateutc);
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'Results'
);
--
use 528825@eol
;
create or replace table EOLExistingInvoices@InMemoryStorage
as
select tle.InvoiceNumber
from TransactionLinesIncremental@eol tle
where tle.LineNumber = 0
and tle.JournalCode = 'VU'
;
create or replace table invoices@inmemorystorage
as
select cpy.companyNumber ExactOnlineAccountCode
, cpy.companyName Name
, cpy.id
, cpy.city
, ive.Id invoiceId
, ive.invoiceNumber
, ive.invoiceTotal
, ive.invoiceDateTime
EntryDate
, act.Id ExactOnlineAccountId
, eolsie.invoiceNumber ExactOnlineInvoiceNumber
from Invoices@at ive
join Companies@at cpy
on cpy.id = ive.companyid
left
outer
join AccountsIncremental@eol act
on act.Code = cpy.companyNumber
left
outer
join EOLExistingInvoices@InMemoryStorage eolsie
on eolsie.invoiceNumber = ive.invoiceNumber
where 1=1
--
-- Alleen geldige facturen.
--
and ive.IsVoided = false
and ive.InvoiceDateTime >= add_months(trunc(sysdate), -3)
--
-- Conversiedatum.
--
and ive.InvoiceDateTime >= to_date('20221220', 'YYYYMMDD')
--
-- Factuurnummer is onbekend in Exact Online.
--
and eolsie.invoiceNumber is null
order
by ive.Id
;
create or replace table InvoiceLines@inmemorystorage
as
select ive.invoiceNumber
, gat.ID GLAccount
, itm.Id Item
, bim.Quantity
, bim.ItemName Description
, bim.InternalCurrencyTotalAmount AmountFC /* Totaal prijs, altijd alles in EUR. */
, bim.InternalCurrencyRate UnitPrice
from invoices@inmemorystorage ive
join GLAccountsIncremental@eol gat
on gat.Code = '8101'
join BillingItems@at bim
on bim.InvoiceId = ive.invoiceId
--
-- Alles op 'Autotask Factuur'-artikel.
--
join ItemsIncremental@eol itm
on itm.Code = 'Autotask Factuur'
order
by ive.invoiceNumber
, itm.Id
, bim.InternalCurrencyTotalAmount desc
;
--
-- Loop per invoice to continue loading rest
-- after an individual error.
--
l_cnt := 0;
l_cnt_error := 0;
for r
in
( select ive.InvoiceNumber
, ive.ExactOnlineAccountId Customer
, 'Factuur ' || ive.invoiceNumber Description
, 'VU' Journal
, EntryDate
from invoices@inmemorystorage ive
order
by ive.InvoiceNumber
)
loop
begin
l_cnt := l_cnt + 1;
insert into output@inmemorystorage
( date_created
, txt
)
values
( sysdateutc
, 'Upload Autotask invoice ' || r.InvoiceNumber || '.'
);
begin transaction
;
insert into exactonlinerest..SalesEntries@eol
( EntryNumber
, InvoiceNumber
, Customer
, Description
, Journal
, EntryDate
)
values
( r.InvoiceNumber
, r.InvoiceNumber
, r.Customer
, r.Description
, r.Journal
, r.EntryDate
)
identified
by 'INVOICE' || r.InvoiceNumber
;
insert into ExactOnlineREST..SalesEntryLines@eol
( AmountFC
, Description
, GLAccount
, Quantity
, linenumber
)
select ile.AmountFC
, ile.Description
, ile.GLAccount
, ile.Quantity
, row_number()
linenumber
from InvoiceLines@inmemorystorage ile
where ile.InvoiceNumber = r.InvoiceNumber
attach
to 'INVOICE' || ile.InvoiceNumber
;
commit transaction
;
insert into output@inmemorystorage
( date_created
, txt
)
values
( sysdateutc
, 'Uploaded Autotask invoice ' || r.InvoiceNumber || '.'
);
exception
when others
then
l_sqlerrm := sqlerrm;
rollback transaction;
l_cnt_error := l_cnt_error + 1;
insert into output@inmemorystorage
( date_created
, txt
)
values
( sysdateutc
, 'Upload Autotask invoice ' || r.InvoiceNumber || ' failed: ' || l_sqlerrm
);
end;
end loop;
--
insert into output@inmemorystorage
( date_created
, txt
)
values
( sysdateutc
, 'Uploaded ' || to_char(l_cnt) || ' Autotask invoices.'
);
insert into output@inmemorystorage
( date_created
, txt
)
values
( sysdateutc
, 'Of which ' || to_char(l_cnt_error) || ' returned an error.'
);
--
-- Add to Excel output.
--
excel.fill_using_query(l_worksheet, 'select date_created, txt from output@inmemorystorage order by date_created');
--
-- Retrieve the resulting XLSX-file.
--
l_binary := excel.export_to_xlsx(l_workbook);
--
-- Set status depending on number of errors.
--
--if l_cnt_error = 0
--then
-- cloud_http.set_response_status_code(200);
--else
-- cloud_http.set_response_status_code(422);
--end if;
--
-- Return the XLSX file to the user.
--
cloud_http.set_response_body_binary(l_binary);
cloud_http.set_response_content_type('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
cloud_http.set_response_header_value('Content-Disposition', 'attachment; filename="' || l_xlsx_filename || '"');
end;