We ontvangen bestelbestanden van klanten in verschillende formaten zoals XML, CSV en XLSX.
Het importeren van een bestelbestand van een klant in Exact Online als verkooporders gaat met de onderstaande code via Invantive Cloud.
Importeercode Exact Online
declare
p_run boolean;
--
g_division_code int64 := 123456;
--
l_filename varchar2;
l_order_description varchar2;
l_payload blob;
l_workbook excel_workbook;
l_worksheet excel_worksheet;
l_binary blob;
l_cnt number;
l_cnt_missing_item number;
l_list_missing_items varchar2;
l_our_ref varchar2;
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.
--
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_file">' || translate_resources('{res:itgen_file}') || '</label></li><li><input type="file" id="p_file" name="p_file" accept=".xlsx" required/></li>');
cloud_http.append_line_to_response_body_text('<li><label for="p_description">' || translate_resources('{res:itgen_description}') || '</label></li><li><input type="text" id="p_description" name="p_description" maxlength="240" size="60" /></li>');
cloud_http.append_line_to_response_body_text('<li><input type="submit" value="Inlezen in Exact Online"/></li>');
cloud_http.append_line_to_response_body_text('</ul>');
cloud_http.append_line_to_response_body_text('</form>');
else
--
-- Process form.
--
l_filename := cloud_http.get_request_form_file_name('p_file');
--
if l_filename is null
then
raise_application_error('acme012', 'The file name is empty.');
end if;
--
l_payload := cloud_http.get_request_form_file_contents('p_file');
--
if l_payload is null
then
raise_application_error('acme011', 'The file payload is empty.');
end if;
--
l_order_description := cloud_http.get_request_form_file_name('p_description');
l_our_ref := replace(basename(l_filename, '.xlsx'), 'Purchase order ', '');
--
-- Choose company.
--
use select code, 'eol' from systemdivisions@eol where code = g_division_code;
--
-- Convert to sales order with order lines.
--
create or replace table ExcelLines@inmemorystorage
as
select xlsx.*
from exceltable
( worksheet 'Export - 2023-08-15T114120.211'
passing l_payload
skip empty rows
skip first 1 rows
columns purchase_order_number varchar2 null position next
, description varchar2 null position next
, ke_article_number varchar2 null position next
, supplier_article_number varchar2 null position next
, order_quantity number null position next
, purchase_price_excl_vat number null position next
, line_total number null position next
, ean number null position next
, oem varchar2 null position next
) xlsx
--
-- Exclude lines with totals.
--
where xlsx.purchase_order_number is not null
;
--
-- Add missing unique IDs from Exact Online.
--
create or replace table InboundPurchaseOrdersMatched@inmemorystorage
as
select itm.Id ItemID
, itm.Code ItemCode
, act.Code DeliverToAccountCode
, act.Id DeliverToAccountId
, odr.purchase_price_excl_vat PriceValue
, odr.order_quantity Quantity
--
, odr.supplier_article_number
, odr.ean
, odr.oem
from ExcelLines@inmemorystorage odr
left
outer
join ExactOnlineREST..Accounts@eol act
on act.Code = '5553' /* Customer ACME */
left
outer
join ExactOnlineREST..Items@eol itm
on itm.Code = odr.supplier_article_number
;
--
-- Check that all entries in the purchase order lines could be found:
--
-- * item exists,
--
select count(*)
cnt
, sum(case when ItemId is null then 1 else 0 end)
cnt_missing_item
, listagg(distinct case when ItemId is null then supplier_article_number else null end, ', ')
list_missing_items
into l_cnt
, l_cnt_missing_item
, l_list_missing_items
from InboundPurchaseOrdersMatched@inmemorystorage
;
if l_cnt = 0
then
raise_application_error('ACME009', 'No inbound purchase orders could be matched with available customers and items.');
end if;
if l_cnt_missing_item > 0
then
raise_application_error('ACME010', 'There are ' || to_char(l_cnt_missing_item) || ' lines with unknown items in the file: ' || l_list_missing_items || '.');
end if;
--
-- Convert order and order lines to XML format.
--
create or replace table sourcelinesxml@inmemorystorage
as
select '<SalesOrder>'
|| chr(13)
|| '<OrderedBy ID="{'
|| to_char(src.DeliverToAccountId)
|| '}" code="'
|| src.DeliverToAccountCode
|| '" />'
|| chr(13)
|| '<DeliverTo ID="{'
|| to_char(src.DeliverToAccountId)
|| '}" code="'
|| src.DeliverToAccountCode
|| '" />'
|| chr(13)
|| '<InvoiceTo ID="{'
|| to_char(src.DeliverToAccountId)
|| '}" code="'
|| src.DeliverToAccountCode
|| '" />'
|| chr(13)
|| xmlelement('OurRef', l_our_ref, false)
|| chr(13)
|| xmlelement('Description', l_order_description, false)
head
, '<SalesOrderLine>'
|| '<Item code="'
|| src.ItemCode
|| '" ID="'
|| to_char(src.ItemID)
|| '" />'
|| xmlelement('Quantity', src.quantity, false)
|| '<UnitPrice>'
|| '<Value code="' || to_char(src.PriceValue) || '" />'
|| '</UnitPrice>'
|| '</SalesOrderLine>'
line
from InboundPurchaseOrdersMatched@inmemorystorage src
;
--
-- Prepare XML upload.
--
create or replace table xmlupload@inmemorystorage
as
select 'SalesOrders' topic
, null orig_system_reference
, cast('origsystemgroup' as varchar2 null) orig_system_group
, xmlformat
( '<?xml version="1.0" encoding="utf-8"?>'
|| '<eExact xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="eExact-XML.xsd">'
|| xmlcomment('For company ' || g_division_code)
|| xmlcomment('Original system group ' || 'origsystemgroup')
|| '<SalesOrders>'
|| xml
|| '</SalesOrders>'
|| '</eExact>'
)
filecontents
from ( select listagg
( head
|| lines
|| '</SalesOrder>'
, ''
)
xml
from ( select brl.head
, listagg(brl.line, '') lines
from sourcelinesxml@inmemorystorage brl
group
by brl.head
)
)
;
--
-- Upload into Exact Online.
--
insert into UploadXMLTopics@eol
( topic
, payload
, division_code
, orig_system_reference
, orig_system_group
, fragment_payload_flag
, fragment_max_size_characters
, fail_on_error
)
select topic
, filecontents
, to_char(g_division_code)
, orig_system_reference
, orig_system_group
--
-- Try to split into pieces of 25 KB at most.
--
, true fragment_payload_flag
, 25000 fragment_max_size_characters
--
-- Returns error through the user interface, not as an HTTP 500 error.
--
, false fail_on_error
from xmlupload@inmemorystorage
order
by orig_system_reference
, orig_system_group
;
--
-- Retrieve results.
--
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', replace(translate_resources('{res:itgen_import_results_par2}'), '{0}', 'Exact Online', '{1}', 'Invantive Cloud'));
excel.set_cell_contents(l_worksheet, 'A2', translate_resources('{itgen_created}'));
excel.set_cell_contents(l_worksheet, 'B2', sysdateutc);
excel.set_cell_contents(l_worksheet, 'A3', translate_resources('{res:itgen_file}'));
excel.set_cell_contents(l_worksheet, 'B3', l_filename);
excel.set_cell_contents(l_worksheet, 'A4', translate_resources('{res:itgen_description}'));
excel.set_cell_contents(l_worksheet, 'B4', l_order_description);
--
create or replace table output@inmemorystorage
as
select /*+ result_set_name("Results") */
uxt.transaction_id
, uxt.successful
, uxt.division_code
, uxt.orig_system_reference
, uxt.orig_system_group
, uxt.date_started_utc
, uxt.date_ended_utc
, substr(uxt.payload, 1, 32767)
payload_32k
label "{res:itgen_eol_payload}"
, substr(uxt.result, 1, 32767)
result_32k
label "{res:itgen_result}"
, substr(uxt.result_errors, 1, 32767)
result_errors_32k
label "{res:itgen_eol_result_errors}"
, substr(uxt.payload_retry_failed, 1, 32767)
payload_retry_failed
label "{res:itgen_eol_payload_retry_failed}"
from UploadXMLTopicFragments@eol uxt
order
by uxt.transaction_id desc
;
--
-- Generate output.
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'Upload Results'
);
excel.fill_using_query(l_worksheet, 'select * from output@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="upload-results-' || basename(l_filename) || '.xlsx"');
end if;
end;
Echter, in de EXCELTABLE moet de werkbladnaam momenteel constant zijn (in onderstaand voorbeeld is dat ‘Export - 2023-08-15T114120.211’):
select xlsx.*
from exceltable
( worksheet 'Export - 2023-08-15T114120.211'
passing l_payload
skip empty rows
skip first 1 rows
columns purchase_order_number varchar2 null position next
, description varchar2 null position next
, ke_article_number varchar2 null position next
, supplier_article_number varchar2 null position next
, order_quantity number null position next
, purchase_price_excl_vat number null position next
, line_total number null position next
, ean number null position next
, oem varchar2 null position next
) xlsx
Volgens de Invantive SQL-grammatica is er een keuze uit:
excelDataRectangle:
The rectangle specifies the rectangular area from which data should be taken. Rows in Excel are interpreted as rows from the data source, whereas columns in Excel are interpreted as columns. All cells within the rectangle are considered data; headings should be excluded from the rectangle specification.
A rectangle can be either:
- the contents of a complete worksheet, specified by an expression returning the worksheet name;
- a named range within a specific worksheet, specified by expressions for worksheet name and named range name;
- a cell range identified by an expression with it’s dimensions;
- an Excel table identified by an expression with the table name;
- a named range identified by an expression with the named range name.
Momenteel wordt de eerste optie gebruikt. De bestellende klant levert geen named range/benoemd bereik aan of geen Excel-tabel.
Er is ook geprobeerd om via het bereik een selectie te maken zoals:
select xlsx.*
from exceltable
( area 'Export - 2023-08-15T114120.211!a1:z999'
passing ...
maar zodra de werkbladnaam weggelaten wordt, treedt de volgende melding op:
itgensql207
Gebiedsformule ‘a1:z999’ bevat niet de bladnaam.
Vragen:
- Is er een andere manier om simpelweg het eerste en enige werkblad te selecteren?
- Is het anders mogelijk om bij
worksheetde naam achterwege te laten of de positie op te geven? - Is het anders mogelijk om bij
areaautomatisch het enige werkblad te kiezen als er maar 1 bestaat?