Voor Heineken gebruiken we maandelijkse deze rapportage qua verkoop:
--
-- Report Heineken sales overview.
--
-- HISTORY
--
-- YYYY-MM-DD Init Description
-- ===========================================================================
-- 2025-12-01 GLE Initial version.
--
declare
l_workbook excel_workbook;
l_worksheet excel_worksheet;
l_binary blob;
l_default_year number;
l_default_period number;
l_date_start date;
l_date_end date;
l_date_ref date;
--
p_run boolean;
p_year number;
p_period number;
p_finyear_end number;
p_finperiod_end number;
begin
--
-- Get parameters.
--
p_run := cast(cloud_http.get_request_query_parameter_value('p_run') as boolean);
--
l_date_ref := add_months(trunc(sysdateutc, -1), -1);
--
l_default_year := to_char(year(l_date_ref));
l_default_period := to_char(month(l_date_ref));
--
if coalesce(p_run, false) = false
then
--
-- Enter parameters.
--
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>');
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_year">Jaar</label></li><li><input type="number" id="p_year" name="p_year" value="' || l_default_year || '" required/></li>');
cloud_http.append_line_to_response_body_text('<li><label for="p_period">Periode</label></li><li><input type="number" id="p_period" name="p_period" value="' || l_default_period || '" required/></li>');
cloud_http.append_line_to_response_body_text('<li><input type="submit" value="Rapport opvragen"/></li>');
cloud_http.append_line_to_response_body_text('</ul>');
cloud_http.append_line_to_response_body_text('</form>');
else
--
-- Process form.
--
p_year := to_number(cloud_http.get_request_query_parameter_value('p_year'));
--
if p_year is null
then
p_year := to_number(l_default_year);
end if;
--
p_period := to_number(cloud_http.get_request_query_parameter_value('p_period'));
--
if p_period is null
then
p_period := to_number(l_default_period);
end if;
--
l_date_start := to_date(to_char(p_year) || lpad(to_char(p_period), 2, '0') || '01', 'YYYYMMDD');
l_date_end := add_months(l_date_start, 1);
--
-- Create an Excel workbook.
--
l_workbook := excel.new();
--
-- Determine transactions.
--
--
create or replace table result@inmemorystorage
as
select sie.InvoiceDate
label 'Date'
, act.Code
AccountCode
label 'CustomerCode'
, itm.BarCode
ItemBarCode
label 'ItemEan'
, 'onbekend'
HeinekenShipTo
label 'HeinekenShipTo'
, sle.AmountDC
label 'ItemAmountSold'
, sle.Quantity
label 'ItemUnitsSold'
, act.AddressLine1
label 'CustomerAddress'
, act.City
label 'CustomerCity'
, act.Name
label 'CustomerName'
, act.Postcode
label 'CustomerPostalCode'
, itm.Code
ItemCode
label 'ItemCode'
--
, itm.Description
ItemDescription
from SalesInvoicesBulk@eol sie
join SalesInvoiceLinesBulk@eol sle
on sle.Division = sie.Division
and sle.InvoiceID = sie.InvoiceID
join AccountsIncremental@eol act
on act.Division = sie.Division
and act.Id = sie.InvoiceTo
join ItemsIncremental@eol itm
on itm.Division = sle.Division
and itm.Id = sle.Item
and itm.class_04 = 'Heineken Brouwerijen'
where sie.InvoiceDate >= l_date_start
and sie.InvoiceDate < l_date_end
and sie.Division = NUMMER
;
--
l_worksheet := excel.add_worksheet( l_workbook, 'Data');
excel.fill_using_query
( l_worksheet
, 'select * except rowid$'
|| ' from result@inmemorystorage'
|| ' order by InvoiceDate'
);
--
-- 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="'
|| 'data-heineken-'
|| to_char(p_year)
|| lpad(to_char(p_period), 2, '0')
|| '-'
|| to_char(p_finyear_end)
|| lpad(to_char(p_finperiod_end), 2, '0')
|| '.xlsx"'
);
end if;
end;