Sinds afgelopen vrijdag krijgen we deze error bij het runnen van een module van een app in Invantive Cloud (niet via app-online):
An error occured - f4929732-312b-420f-b2ff-31d41631999f
itgenclr090:
The given key ‘ID’ was not present in the dictionary.
Dit ontstond 2 dagen na het verhelpen van de '“ –> ‘ omzetting en staat er volgens mij los van.
We hebben verschillende modules die bestanden (inkooporders) transformeren tot een verkooporder. Alleen deze ene module geeft de fout. Dit is de enige module die een XML als input (de rest krijgt XLSX/CSV binnen).
Vrijdagochtend hebben we succesvol nog een XML ingeladen via deze module. Sinds vrijdagmiddag krijgen we de error.
Ook het inladen van oude XML’s (die eerder wel succesvol ingeladen zijn) lukt niet meer. Dus ik heb het vermoeden dat de oorzaak ergens in Invantive ligt.
Ik heb al flink wat zitten klooien, maar ik kom er niet uit.
Enig idee wat de oorzaak hiervan is?
declare
p_run boolean;
--
g_division_code int64 := 2847907;
--
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_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><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('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;
--
l_order_description := cloud_http.get_request_form_value('p_description');
--
-- 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('XXMOV002', '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('XXMOV002', 'No inbound purchase orders could be matched with available customers and items.');
end if;
if l_cnt_missing_item > 0
then
raise_application_error('XXMOV003', '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('XXMOV004', '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
|| '" />'
|| chr(13)
|| xmlelement('YourRef', src.OrderRef, false)
|| chr(13)
|| xmlelement('Description', l_order_description, 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)
|| '</SalesOrderLine>'
line
from InboundPurchaseOrdersMatched@inmemorystorage src
;
--
-- Prepare XML upload.
--
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();
--
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');
--
-- 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, 'A3', translate_resources('{res:itgen_module}'));
excel.set_cell_contents(l_worksheet, 'B3', 'XXMOVEURFOTO');
excel.set_cell_contents(l_worksheet, 'A4', translate_resources('{res:itgen_created}'));
excel.set_cell_contents(l_worksheet, 'B4', sysdateutc);
excel.set_cell_contents(l_worksheet, 'A5', translate_resources('{res:itgen_file}'));
excel.set_cell_contents(l_worksheet, 'B5', l_filename);
excel.set_cell_contents(l_worksheet, 'A6', translate_resources('{res:itgen_description}'));
excel.set_cell_contents(l_worksheet, 'B6', l_order_description);
--
-- 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;
