Invantive SQL ondersteunt Exact Online met ruim 1.100 tabellen. Daarnaast zijn er producten zoals Get My Report waar vooraf gedefinieerde rapporten de gegevens als download ter beschikking stellen over meerdere administraties heen in één keer.
Soms is het echter wenselijk om een eigen rapportagetool te hebben waarin de rapportages in een vast eigen formaat opgehaald kunnen worden door de gebruikers van het bedrijf. Met Invantive Cloud is dit mogelijk, ook zonder het gebruik van Power BI of Qlik.
Dit topic laat zien hoe je alle verkoopfactuurregels met aanvullende informatie in 1x kunt selecteren en downloaden via een eigen Invantive Cloud app. Het Excel-downloadformaat is een veelgebruikt formaat voor salesmanagers en verkopers die hun Exact Online handelsactiviteiten willen analyseren via eigen draaitabellen. De Exact Online SQL query is de Verkoopfactuurregels-module op Get My Report voor Exact Online.
De verkoopcijfers kunnen afkomstig zijn uit één financiële administratie of meerdere tegelijk, zoals ingesteld op de Exact Online database in Invantive Cloud. Hou er wel rekening mee dat financiële gegevens uit het verkoopboek van verschillende administraties een andere grondslag kunnen hebben, bijvoorbeeld in Euro’s en in US Dollar.
Indien een app langer dan 3 minuten nodig heeft of geautomatiseerd aangeroepen moet worden, dan kan dat via Invantive App Online, het zusje van Invantive Bridge Online voor Power BI. Zie ook Wat is Invantive App Online?.
Voorbereidingen
De volgende voorbereidingen zijn nodig:
- Een aanmeldcode op Invantive Cloud.
- Een aanmeldcode op Exact Online met een of meerdere administraties.
Exact Online Database Definiëren
De eerste stap is het definiëren van een Exact Online database. Dat kan heel eenvoudig door dit stappenplan te volgen:
Deze Exact Online database kun je gebruiken voor Power BI, Power Query of Azure Data Factory, maar net zo gemakkelijk voor bijvoorbeeld Access OData of - zoals hier - als bron voor een Invantive Cloud applicatie.
Cloud Applicatie Definiëren
De volgende stap is het definiëren van een app op Invantive Cloud die de verkoopcijfers uit Exact Online haalt. Voer de volgende stappen uit:
- Selecteer in het linkermenu ‘Applicaties’:
- Kies ‘Nieuw’.
- Vul de velden als volgt in:
- Kies ‘Modules’.
- Kies ‘Nieuw’.
- Vul de velden als volgt in:
Excel-bestand maken en Vullen met Exact Online Verkoopcijfers
Deze module zal de code bevatten om de instellingen op te geven en daarna het Excel-bestand aan te maken met daarin de verkoopcijfers uit Exact Online.
De volledige code staat aan het einde van dit artikel.
Wil je snel verder?
- Plak dan de code in de module bij het veld “Broncode”.
- Kies “My First App” in het kruimelspoor bovenaan.
- Kies “Uitvoeren”.
- Kies de Exact Online database (indien er meerdere databases zijn).
- Kies “Uitvoeren”:
- Kies de module:
- Vul de begin- en einddatum in:
- Het Excel-bestand wordt gedownload.
Meer voorbeelden van apps
Meer voorbeelden van eigen apps zijn te vinden in:
- Hoe kan ik openstaande posten en bankmutaties downloaden in Google Sheets vanuit Exact Online?
- Create a mini-site to download your Exact Online articles (Engels)
- Excel download uit Exact Online in eigen formaat met een cloud app
- Upload and process a file using Invantive App Online (Engels)
- App Online download van Exact Online voor WICS bevat niet laatste transacties
- Uploaden WICS XML pick files naar Invantive App Online
- Generate JSON from Invantive SQL queries
Broncode Verkooprapportage
De volgende broncode is het rapportagetool om Exact Online verkoopinformatie te downloaden als een Excel-werkboek. Zolang de variabele p_run niet true is, zal het invulscherm voor de begin- en einddatum getoond worden. Het Excel-werkboek wordt gedownload als de variabele p_run door het invulscherm op true gezet is.
declare
p_run boolean := false;
p_cfn_factuur_datum_vanaf date := null;
p_cfn_factuur_datum_tot date := null;
--
l_workbook excel_workbook;
l_worksheet excel_worksheet;
l_binary blob;
begin
--
-- Get parameters.
--
p_run := cast(cloud_http.get_request_query_parameter_value('p_run') as boolean);
--
p_cfn_factuur_datum_vanaf := to_date(cloud_http.get_request_query_parameter_value('p_cfn_factuur_datum_vanaf'), 'YYYY-MM-DD');
p_cfn_factuur_datum_tot := to_date(cloud_http.get_request_query_parameter_value('p_cfn_factuur_datum_tot'), 'YYYY-MM-DD');
--
if coalesce(p_run, false) = false
then
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_cfn_factuur_datum_vanaf">' || translate_resources('{res:itgen_invoice_date_from}') || '</label></li><li><input id="p_cfn_factuur_datum_vanaf" name="p_cfn_factuur_datum_vanaf" type="date" value="' || to_char(add_months(trunc(sysdateutc, -1), -1), 'YYYY-MM-DD') || '"/></li>');
cloud_http.append_line_to_response_body_text('<li><label for="p_cfn_factuur_datum_tot">' || translate_resources('{res:itgen_invoice_date_to}') || '</label></li><li><input id="p_cfn_factuur_datum_tot" name="p_cfn_factuur_datum_tot" type="date" value="' || to_char(add_months(trunc(sysdateutc, -1), +1), 'YYYY-MM-DD') || '"/></li>');
cloud_http.append_line_to_response_body_text('<li><input type="submit" value="Start"/></li>');
cloud_http.append_line_to_response_body_text('</ul>');
cloud_http.append_line_to_response_body_text('</form>');
else
--
-- Check parameters.
--
-- No checks.
--
-- Create an Excel workbook.
--
l_workbook := excel.new();
--
-- Add a worksheet for Exact Online sales invoice lines.
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'SALES SOURCE DATA'
);
--
-- Create a list of sales invoice lines.
--
create or replace table rpt@inmemorystorage
as
select /*+ result_set_name("SalesInvoiceLines") */
sie.invoicenumber
label 'Invoice Number'
, sle.LineNumber
label 'Line Number'
, sie.invoicedate
label 'Invoice Date'
, sie.ordernumber
label 'Order Number'
, sie.orderdate
label 'Order Date'
, sie.SalespersonFullName
label 'Sales Person Full Name'
, actord.code
label 'Ordered by Code'
, actord.name
label 'Ordered by Name'
, cttord.fullname
label 'Ordered by Contact'
, amrord.FullName AccountManagerFullName
label 'Ordered by Account Manager'
, actord.Email
label 'Ordered by Email'
, actdlr.code
label 'Deliver to Company Code'
, actdlr.name
label 'Deliver to Company Name'
, sie.delivertocontactpersonfullname
label 'Deliver to Contact'
, adsdlr.addressline1
label 'Deliver to Address Line 1'
, adsdlr.postcode
label 'Deliver to Postcode'
, adsdlr.city
label 'Deliver to City'
, adsdlr.country
label 'Deliver to Country'
, actdlr.email
label 'Deliver to Email'
, amrive.FullName AccountManagerFullName
label 'Invoice Account Manager'
, active.name
label 'Invoice Company Name'
, active.Email
label 'Invoice Email'
, active.AddressLine1
label 'Invoice Address Line 1'
, active.Postcode
label 'Invoice Postcode'
, active.City
label 'Invoice City'
, active.Country
label 'Invoice Country'
, sie.duedate
label 'Due Date'
, gat.Code GLAccountCode
label 'General Ledger Account'
, igp.code itemgroupcode
label 'Item Group'
, sle.itemcode
label 'Item Code'
, sle.itemdescription
label 'Item Description'
, sle.description
label 'Description'
, sim.SupplierCode
label 'Main Supplier Code'
, sim.SupplierDescription
label 'Main Supplier Name'
, sle.StartTime
label 'Start'
, sle.EndTime
label 'End'
, sle.quantity
label 'Quantity'
, sle.UnitDescription
label 'Unit Description'
, sle.NetPrice
label 'Net Price'
, sle.unitprice
label 'Unit Price'
, sle.discount * 100
label 'Discount'
, sle.unitprice * ( 1 - sle.discount )
label 'Net Price per Unit'
, sle.netamountdc
label 'Net Price Total'
, sle.unitprice * sle.discount * sle.quantity
label 'Amount Discount'
, sle.VATAmountDC
label 'VAT Amount (EUR)'
, itm.costpricecurrency
label 'Cost Price Currency'
, itm.costpricestandard
label 'Cost Price Standard'
, itm.costpricestandard * sle.quantity
label 'Standard Cost Price Total'
, itm.NetWeight
label 'Net Weight'
, itm.GrossWeight
label 'Gross Weight'
from exactonlinerest..salesinvoicesbulk@eol sie
join exactonlinerest..salesinvoicelinesBulk@eol sle
on sle.division = sie.division
and sle.invoiceid = sie.invoiceid
join ItemsIncremental@eol itm
on itm.division = sle.division
and itm.id = sle.item
join ItemGroups@eol igp
on igp.division = itm.division
and igp.id = itm.ItemGroup
join GLAccountsIncremental@eol gat
on gat.Id = sle.GLAccount
join AccountsIncremental@eol actord
on actord.division = sie.division
and actord.id = sie.orderedby
left
outer
join exactonlinerest..users@eol amrord
on amrord.userId = actord.AccountManager
left
outer
join ContactsIncremental@eol cttord
on cttord.id = sie.orderedbycontactperson
join AccountsIncremental@eol actdlr
on actdlr.id = sie.deliverto
left
outer
join AddressesIncremental@eol adsdlr
on adsdlr.id = sie.delivertoaddress
join AccountsIncremental@eol active
on active.id = sie.invoiceto
left
outer
join exactonlinerest..users@eol amrive
on amrive.userId = actord.AccountManager
left
outer
join SupplierItems@eol sim
on sim.division = sle.division
and sim.ItemCode = sle.itemcode
and sim.MainSupplier = true
where sie.status = 50
and sie.invoicedate between coalesce(p_cfn_factuur_datum_vanaf, sie.invoicedate) and coalesce(p_cfn_factuur_datum_tot, sie.invoicedate)
order
by sie.invoicenumber
, sie.ordernumber
, sle.linenumber
;
--
-- Place the items in the Excel worksheet.
--
excel.fill_using_query(l_worksheet, 'select * except rowid$ from rpt@inmemorystorage');
--
-- Add a worksheet with a title.
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'FILTER'
);
excel.set_cell_contents(l_worksheet, 'A1', 'Sales Invoices');
excel.set_cell_contents(l_worksheet, 'A3', translate_resources('{res:itgen_user}'));
excel.set_cell_contents(l_worksheet, 'B3', sys_context('USERENV', 'USER_FULL_NAME', 'eol'));
--
excel.set_cell_contents(l_worksheet, 'A4', translate_resources('{res:itgen_generated}'));
excel.set_cell_contents(l_worksheet, 'B4', to_char(sysdateutc, 'DD-MM-YYYY HH24:MI:SS'));
--
excel.set_cell_contents(l_worksheet, 'A6', translate_resources('{res:itgen_invoice_date_from}'));
excel.set_cell_contents(l_worksheet, 'B6', p_cfn_factuur_datum_vanaf);
--
excel.set_cell_contents(l_worksheet, 'A7', translate_resources('{res:itgen_invoice_date_to}'));
excel.set_cell_contents(l_worksheet, 'B7', p_cfn_factuur_datum_tot);
--
-- 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="Sales Invoices Fixed Format.xlsx'
);
end if;
end;