In dit artikel leer je hoe je een Exact Globe XML-bestand kunt importeren in een Exact Online administratie als verkooporder met een Invantive Cloud module.
Wilt u een Excel-sheet inlezen als Exact Online bestelling en niet als verkooporder? Een voorbeeld is te vinden in Massaal inlezen Excel-sheet met bestellingen als bestellingen in Exact Online.
Voor het uitwisselen van bestellingen met toeleveranciers kan ook een Exact Globe stock order XML-bestand gebruikt als een EDI-“standaard”. Een Exact Globe stock order bestand is in een voorbeeld XML-bestand aan het einde van dit artikel.
Alhoewel Exact Online zelf ook XML-bestanden kan importeren is het formaat van het XML-bestand anders. Daarnaast zijn het natuurlijk twee verschillende kanten: de een stelt een bestelling voor, de ander een verkooporder.
Dat inlezen van een XML bestand in Exact Online is erg snel en sterk aan te bevelen boven gebruik van de REST-tabellen (zie Does Invantive SQL match the speed of Infinite Probability Drive?).
De voorbeeldcode van de module kan eenvoudig toegevoegd worden via Applicaties → Kies → Modules → Toevoegen. Zie bijvoorbeeld Create a mini-site to download your Exact Online articles.
Werking importmodule
De importmodule presenteert een webpagina waar de gebruiker via de browser de locatie van het Exact Globe XML-bestand kan opgeven. Daarna kiest de gebruik de knop “Inlezen in Exact Online”. Na het inlezen wordt een Excel-bestand gedownload in de browser. In dit Excel-bestand staat het verwerkingsverslag en eventuele problemen. Ook bevat het verwerkingsverslag de XML die niet geïmporteerd kon worden; de geslaagde onderdelen zijn hier uit weggelaten zodat het eenvoudig handmatig gecorrigeerd kan worden.
De velden uit de bestelling worden 1-op-1 afgebeeld. Bijzonderheid is dat het GLN-number uit de bestelling (DeliverTo/Address/AddressLine2) gebruikt wordt om de bestellende partij te bepalen voor de verkooporder.
Een aantal controles worden uitgevoerd voor het daadwerkelijke importeren in Exact Online gebeurt zoals dat de artikelen ook echt bestaan en dat het GLN-number bekend is.
De importmodule is geschikt om duizenden orders in een korte tijd te importeren in één of meerdere administraties. Door het gebruik van de XML API is het benodigde aantal API calls beperkt, reken op 1 API call per 100 orderregels.
StockOrder voorbeeld XML-bestand
Alle XML-bestanden van een stock order zien er uit zoals:
<?xml version="1.0" encoding="UTF-8"?>
<eExact xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="eExact-Schema.xsd">
<Orders>
<Order type="B" number="123456">
<OurRef>123456</OurRef>
<Resource>
<LastName>Tennis</LastName>
<FirstName>Club</FirstName>
</Resource>
<OrderedAt>
<Creditor number="258680" code="258680">
<Name>ACME B.V.</Name>
</Creditor>
<Date>2022-03-11</Date>
</OrderedAt>
<DeliverTo>
<Warehouse code="1">
<Description>Hoofdmagazijn</Description>
</Warehouse>
<Address>
<AddressLine1>Tuinstraat 123</AddressLine1>
<AddressLine2>8712412345678</AddressLine2>
<PostalCode>1020 AA</PostalCode>
<City>Amsterdam</City>
<Country code="NL" />
<Phone>020-1699444</Phone>
</Address>
</DeliverTo>
<InvoiceTo>
<Warehouse code="1">
<Description>Hoofdmagazijn</Description>
</Warehouse>
<Address>
<AddressLine1>Tuinstraat 123</AddressLine1>
<AddressLine2>8712412345678</AddressLine2>
<PostalCode>1020 AA</PostalCode>
<City>Amsterdam</City>
<Country code="NL" />
<Phone>020-1699444</Phone>
</Address>
</InvoiceTo>
<OrderLine lineNo="1">
<Description>Tennisbal</Description>
<Item code="TB" />
<Quantity>2</Quantity>
<Price type="S">
<Currency code="EUR" />
<Value>1.65</Value>
</Price>
<Delivery>
<Date>2022-03-11</Date>
</Delivery>
<ItemCode>TB</ItemCode>
</OrderLine>
</Order>
</Orders>
</eExact>
Inleesmodule Invantive Cloud
De volgende module kan gebruikt worden in Invantive Cloud om een Exact Globe stock order in te lezen als sales orders (verkooporders) in Exact Online:
declare
p_run boolean;
--
g_division_code int64 := 123456;
--
l_filename varchar2;
l_payload blob;
l_workbook excel_workbook;
l_worksheet excel_worksheet;
l_binary blob;
l_cnt number;
l_cnt_missing_item number;
l_cnt_missing_account number;
l_list_missing_items varchar2;
l_list_missing_accounts 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=".xml" required/></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('sample001', '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('sample002', 'The file payload is empty.');
end if;
--
-- Choose company.
--
use select code, 'eol' from systemdivisions@eol where code = g_division_code;
--
-- Find available parties with GLN-number.
--
create or replace table AccountsAvailable@inmemorystorage
as
select act.Division
, act.GlnNumber
, act.Code
, act.Id
from AccountsIncremental act
where act.GlnNumber is not null
;
--
-- Make all items persistent.
--
create or replace table ItemsAvailable@inmemorystorage
as
select itm.Division
, itm.Code
, upper(itm.Code) CodeUC
, itm.Id
, itm.Description
from ItemsIncremental@eol itm
;
--
-- Convert Exact Globe format to a table with order lines.
--
create or replace table InboundPurchaseOrders@inmemorystorage
as
select odr.*
, upper(odr.ItemCode) ItemCodeUC
from xmltable
( '/eExact/Orders/Order/OrderLine'
passing l_payload
columns OrderNumber varchar2 path '../@number'
, LineNumber int32 path '@lineNo'
, Quantity int32 path 'Quantity'
, ItemCode varchar2 path 'ItemCode'
, PriceCurrency varchar2 path 'Price/Currency/@code'
, PriceValue decimal path 'Price/Value'
, DeliveryDate date path 'Delivery/Date'
, Description varchar2 path 'Description'
, OrderRef varchar2 path '../OurRef'
, OrderFirstName varchar2 path '../Resource/FirstName'
, OrderLastName varchar2 path '../Resource/LastName'
, OrderAtCreditorNumber varchar2 path '../OrderedAt/Creditor/@number'
, OrderAtCreditorCode varchar2 path '../OrderedAt/Creditor/@code'
, OrderAtCreditorName varchar2 path '../OrderedAt/Creditor/Name'
, OrderAtDate date path '../OrderedAt/Date'
, DeliverToWarehouesCode varchar2 path '../DeliverTo/Warehouse/@code'
, DeliverToGlnNumber varchar2 path '../DeliverTo/Address/AddressLine2'
) odr
;
--
-- Check that there is at least one purchase order line.
--
select count(*)
into l_cnt
from InboundPurchaseOrders@inmemorystorage
;
if l_cnt = 0
then
raise_application_error('XXCODE001', 'No purchase orders could be extracted from the file.');
end if;
--
-- Add missing unique IDs from Exact Online.
--
create or replace table InboundPurchaseOrdersMatched@inmemorystorage
as
select itm.Division
, itm.Id ItemID
, act.Code DeliverToAccountCode
, act.Id DeliverToAccountId
, odr.*
from InboundPurchaseOrders@inmemorystorage odr
left
outer
join AccountsAvailable@inmemorystorage act
on act.GlnNumber = odr.DeliverToGlnNumber
left
outer
join ItemsAvailable@inmemorystorage itm
on itm.CodeUC = odr.ItemCodeUC
;
--
-- Check that all entries in the purchase order lines could be found:
--
-- * item exists,
-- * GLN number is known.
--
select count(*)
cnt
, sum(case when ItemId is null then 1 else 0 end)
cnt_missing_item
, sum(case when DeliverToAccountId is null then 1 else 0 end)
cnt_missing_account
, listagg(distinct case when ItemId is null then ItemCodeUC else null end, ', ')
list_missing_items
, listagg(distinct case when DeliverToAccountId is null then DeliverToGlnNumber else null end, ', ')
list_missing_accounts
into l_cnt
, l_cnt_missing_item
, l_cnt_missing_account
, l_list_missing_items
, l_list_missing_accounts
from InboundPurchaseOrdersMatched@inmemorystorage
;
if l_cnt = 0
then
raise_application_error('XXCODE002', 'No inbound purchase orders could be matched with available customers and items.');
end if;
if l_cnt_missing_item > 0
then
raise_application_error('XXCODE003', 'There are ' || to_char(l_cnt_missing_item) || ' lines with unknown items in the file: ' || l_list_missing_items || '.');
end if;
if l_cnt_missing_account > 0
then
raise_application_error('XXCODE004', 'There are ' || to_char(l_cnt_missing_account) || ' lines with unknown accounts in the file with GLN: ' || l_list_missing_accounts || '.');
end if;
--
-- Convert order and order lines to XML format.
--
create or replace table sourcelinesxml@inmemorystorage
as
select src.Division
, '<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
|| '" />'
|| xmlelement('YourRef', src.OrderRef, false)
head
, '<SalesOrderLine line="'
|| to_char(src.LineNumber)
|| '">'
|| xmlelement('Description', src.description, false)
|| '<Item code="'
|| src.ItemCode
|| '" ID="'
|| to_char(src.ItemID)
|| '" />'
|| xmlelement('Quantity', src.quantity, false)
|| xmlelement('DeliveryDate', src.DeliveryDate, false)
|| '<UnitPrice>'
|| '<Currency code="' || src.PriceCurrency || '" />'
|| '<Value code="' || to_char(src.PriceValue) || '" />'
|| '</UnitPrice>'
|| '</SalesOrderLine>'
line
from InboundPurchaseOrdersMatched@inmemorystorage src
;
--
-- Prepare XML upload.
-- Note the use of XML upload; the REST upload is an order of magnitude slower.
--
create or replace table xmlupload@inmemorystorage
as
select 'SalesOrders' topic
, division_code
, 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 ' || division_code)
|| xmlcomment('Original system group ' || 'origsystemgroup')
|| '<SalesOrders>'
|| xml
|| '</SalesOrders>'
|| '</eExact>'
)
filecontents
from ( select division_code
, listagg
( head
|| lines
|| '</SalesOrder>'
, ''
)
xml
from ( select brl.Division division_code
, brl.head
, listagg(brl.line, '') lines
from sourcelinesxml@inmemorystorage brl
group
by brl.head
, brl.division
)
group
by division_code
)
;
--
-- 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(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 division_code
, 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', 'Exact Online import results using Invantive Cloud');
excel.set_cell_contents(l_worksheet, 'A2', 'Created ' || to_char(sysdate));
--
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;