Bij het inlezen van de XML file met de verscheepte goederen uit WICS naar Exact Online, krijg ik met onderstaande code een foutmelding:
An error occured - 6295ac6c-19f7-4e02-ac8f-79d3fac83b33
itgengpr015:
Onbekende tabel ‘COLLOS’.
De tabel collos wordt wel gedefinieerd en daarna gebruikt.
Hoe kan ik die tabel binnen het PSQL-blok gebruiken direct na het maken?
declare
p_run boolean;
--
l_errors_1 varchar2;
l_errors_2 varchar2;
l_errors_3 varchar2;
l_xml varchar2;
l_workbook excel_workbook;
l_worksheet excel_worksheet;
l_binary blob;
l_cnt_output number;
l_xlsx_filename varchar2;
l_xml_filename 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 manually.
-- A POST from curl loading the WICS shipping XML file directly into Exact Online is also possible.
--
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 WICS shipments in Exact Online"/></li>');
cloud_http.append_line_to_response_body_text('</ul>');
cloud_http.append_line_to_response_body_text('</form>');
else
--
-- Process XML file with shipments from WICS.
--
l_xml_filename := cloud_http.get_request_form_file_name('p_file');
--
if l_xml_filename is null
then
raise_application_error('sample001', 'The file name is empty.');
end if;
--
l_xml := cloud_http.get_request_form_file_contents('p_file');
--
if l_xml is null
then
raise_application_error('sample002', 'The file XML payload is empty.');
end if;
--
l_errors_1 := '';
l_errors_2 := '';
l_errors_3 := '';
--
create or replace table shipments@inmemorystorage
as
select *
from xmltable
( '/Shipment'
passing l_xml
columns OrderExternalRef varchar2 path './OrderExternalRef'
, ShippingDate datetime path './ShippingDate'
)
;
create or replace table collos@inmemorystorage
as
select *
from xmltable
( '/Shipment/Collos/Collo'
passing l_xml
columns OrderExternalRef varchar2 path '../../OrderExternalRef'
, ShippingDate datetime path '../../ShippingDate'
, TrackingCode varchar2 path './TrackingCode'
)
;
create or replace table collolines@inmemorystorage
as
select *
from xmltable
( '/Shipment/Collos/Collo/Lines/Line'
passing l_xml
columns OrderExternalRef varchar2 path '../../../../OrderExternalRef'
, ShippingDate datetime path '../../../../ShippingDate'
, TrackingCode varchar2 path '../../TrackingCode'
, OrderlineID integer path './OrderlineID'
, ItemNum varchar2 path './ItemNum'
, QtyDelivered integer path './QtyDelivered'
, StockUnit varchar2 path './StockUnit'
, Notes varchar2 path './Notes'
)
;
--
-- Update tracking numbers.
--
for r
in
( select *
from collos@inmemorystorage
)
loop
update goodsdeliveries@eol
set TrackingNumber = r.TrackingCode
where deliverynumber = r.OrderExternalRef
and TrackingNumber = 'COMMUNICATED'
;
if sqlrowcount != 1
then
l_errors_1 := l_errors_1 || chr(13) || 'Could not find delivery number ' || r.OrderExternalRef || ' to update tracking reference to ' || r.TrackingCode || '.';
end if;
end loop;
--
select listagg
( 'The delivery number ' || clo.OrderExternalRef || ' can not be found in Exact Online.'
, chr(13) || chr(10)
)
errors_txt
into l_errors_2
from collos@inmemorystorage clo
left
outer
join goodsdeliveries@eol gdy
on gdy.DeliveryNumber = clo.OrderExternalRef
where gdy.DeliveryNumber is null
;
create or replace table gdy_totals@inmemorystorage
as
select gdy.EntryId
, gdy.DeliveryNumber
, gle.ItemCode
, sum(gle.QuantityDelivered) QuantityDelivered
from Collos@inmemorystorage clo
join GoodsDeliveries@eol gdy
on gdy.DeliveryNumber = clo.OrderExternalRef
join goodsdeliverylines@eol gle
on gle.entryid = gdy.entryid
group
by gdy.EntryId
, gdy.DeliveryNumber
, gle.ItemCode
;
select listagg
( 'The delivery number '
|| coalesce(cle.OrderExternalRef, to_char(gdy.DeliveryNumber))
|| ' for '
|| coalesce(cle.QtyDelivered, gdy.QuantityDelivered)
|| ' x '
|| coalesce(gdy.ItemCode, cle.ItemNum)
|| ' '
|| case
when gdy.DeliveryNumber is null
then ' can not be found in Exact Online.'
when cle.OrderExternalRef is null
then ' exists in Exact Online, but not in the picked items report.'
else ' has ' || cle.QtyDelivered || ' pieces in picked items report, but ' || gdy.QuantityDelivered || ' in Exact Online.'
end
, chr(13) || chr(10)
) errors_txt
into l_errors_3
from collolines@inmemorystorage cle
full
outer
join gdy_totals@inmemorystorage gdy
on gdy.DeliveryNumber = cle.OrderExternalRef
and gdy.ItemCode = cle.ItemNum
where ( gdy.DeliveryNumber is null or cle.OrderExternalRef is null or cle.QtyDelivered != gdy.QuantityDelivered )
;
--
-- TODO: Accept POST of xml file with picked items.
--
create or replace table output@inmemorystorage
as
select l_errors_1 txt
from dual@datadictionary
where l_errors_1 is not null
union all
select l_errors_2
from dual@datadictionary
where l_errors_2 is not null
union all
select l_errors_3
from dual@datadictionary
where l_errors_3 is not null
;
select count(*)
into l_cnt_output
from output@inmemorystorage
;
if l_cnt_output = 0
then
l_xlsx_filename := 'shipping-results-success.xlsx';
else
l_xlsx_filename := 'shipping-results-errors.xlsx';
end if;
--
-- 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 load WICS shipping results using Invantive Cloud');
excel.set_cell_contents(l_worksheet, 'A2', 'Created (UTC) ' || to_char(sysdateutc));
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'Shipping 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="' || l_xlsx_filename || '"');
end if;
end;```