Ik gebruik de volgende applicatiemodule voor F01DGS met Invantive Cloud:
declare
p_run boolean;
p_jaar number;
p_maand_start number;
p_maand_einde number;
--
l_workbook excel_workbook;
l_worksheet excel_worksheet;
l_xlsx_filename varchar2;
l_binary blob;
begin
--
-- Get parameters.
--
p_run := cast(cloud_http.get_request_form_value('p_run') as boolean);
--
if coalesce(p_run, false) = false
then
--
-- Enter parameters manually (currently solely a button to start synchronization).
--
cloud_http.set_use_template(true);
cloud_http.set_template_step_name(translate_resources('{res:itgen_parameters}'));
cloud_http.append_line_to_response_body_text('<form method="post" enctype="multipart/form-data">');
cloud_http.append_line_to_response_body_text('<input type="hidden" id="p_run" name="p_run" value="true"/>');
cloud_http.append_line_to_response_body_text('<ul>');
cloud_http.append_line_to_response_body_text('<li><label for="p_jaar">Boekjaar</label></li><li><input type="number" id="p_jaar" name="p_jaar" value="true" min="2020" max="2999" /></li>');
cloud_http.append_line_to_response_body_text('<li><label for="p_maand_start">Boekperiode vanaf</label></li><li><input type="number" id="p_maand_start" name="p_maand_start" value="true" min="1" max="12" /></li>');
cloud_http.append_line_to_response_body_text('<li><label for="p_maand_einde">Boekperiode tot/met</label></li><li><input type="number" id="p_maand_einde" name="p_maand_einde" value="true" min="1" max="12" /></li>');
cloud_http.append_line_to_response_body_text('<li><input type="submit" value="Download"/></li>');
cloud_http.append_line_to_response_body_text('</ul>');
cloud_http.append_line_to_response_body_text('</form>');
else
--
-- Maak rapport.
--
p_jaar := to_number(cloud_http.get_request_form_value('p_jaar'));
p_maand_start := to_number(cloud_http.get_request_form_value('p_maand_start'));
p_maand_einde := to_number(cloud_http.get_request_form_value('p_maand_einde'));
--
if p_jaar is null
then
raise_application_error(-20163, 'Het boekjaar ontbreekt.');
end if;
if p_jaar > year(sysdateutc)
then
raise_application_error(-20163, 'Het boekjaar ligt in de toekomst.');
end if;
if p_maand_start is null
then
raise_application_error(-20163, 'De start boekperiode ontbreekt.');
end if;
if p_maand_einde is null
then
raise_application_error(-20163, 'De eind boekperiode ontbreekt.');
end if;
if p_maand_einde < p_maand_start
then
raise_application_error(-20163, 'De eind boekperiode ligt voor de start boekperiode.');
end if;
--
create or replace table transacties@inmemorystorage
as
select /*+ result_set_name('Transactieregels') */
tle.Date
label 'Boekdatum'
, tle.EntryNumber
label 'Boekstuknummer'
, tle.LineNumber
label 'Regelnummer'
, act.Code AccountCode label 'Relatiecode'
, act.Name AccountName label 'Relatienaam'
, act.VATNumber AccountVATNumber label 'Relatie BTW-nummer'
, act.Country AccountCountry label 'Relatie Land'
, tle.description
label 'Omschrijving'
, tle.AmountDC
label 'Bedrag'
, tle.FinancialYear
label 'Boekjaar'
, tle.FinancialPeriod
label 'Boekperiode'
, itm.Code
ItemCode
label 'Artikelcode'
, itm.Description
ItemDescription
label 'Artikelomschrijving'
, igp.Code
ItemGroupCode
label 'Artikelgroep'
from TransactionLinesIncremental@eol tle
left
outer
join ItemsIncremental@eol itm
on itm.Id = tle.Item
left
outer
join ExactOnlineREST..ItemGroups@eol igp
on igp.Id = itm.ItemGroup
join AccountsIncremental@eol act
on act.id = tle.Account
where tle.FinancialYear = p_jaar
--
-- No total line and no VAT line.
--
and tle.LineNumber not in (0, 9999)
and ( tle.FinancialPeriod between p_maand_start and p_maand_einde )
--
-- There is a counter party involved.
--
and tle.Account is not null
and tle.JournalCode like '7%'
order
by tle.Date
;
create or replace table relaties@inmemorystorage
as
select act.Code label 'Code'
, act.name label 'Naam'
, act.VATNumber label 'BTW-nummer'
, act.Country label 'Land'
from act.AccountsIncremental@eol act
order
by act.Code
;
--
-- Retrieve results as an Excel workbook.
--
l_xlsx_filename := 'rapport-' || to_char(sysdateutc, 'YYYYMMDDHH24MISS') || '.xlsx';
l_workbook := excel.new();
--
-- Add a worksheet with a title.
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'Parameters'
);
excel.set_cell_contents(l_worksheet, 'A1', 'Data voor F01DGS');
excel.set_cell_contents(l_worksheet, 'A3', 'Aangemaakt (UTC)');
excel.set_cell_contents(l_worksheet, 'B3', sysdateutc);
excel.set_cell_contents(l_worksheet, 'A4', 'Boekjaar');
excel.set_cell_contents(l_worksheet, 'B4', p_jaar);
excel.set_cell_contents(l_worksheet, 'A5', 'Boekperiode vanaf');
excel.set_cell_contents(l_worksheet, 'B5', p_maand_start);
excel.set_cell_contents(l_worksheet, 'A6', 'Boekperiode tot/met');
excel.set_cell_contents(l_worksheet, 'B6', p_maand_einde);
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'Transacties'
);
excel.fill_using_query(l_worksheet, 'select * except rowid$ from transacties@inmemorystorage');
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'Relaties'
);
excel.fill_using_query(l_worksheet, 'select * except rowid$ from relaties@inmemorystorage');
--
-- Retrieve the resulting XLSX-file.
--
l_binary := excel.export_to_xlsx(l_workbook);
--
-- 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 if;
end;