We gebruiken een PSQL module om WICS XML pick order bevestigingen te verwerken in Exact Online. Zie onder voor SQL code.
Er is een batchfile die over alle ShipOrder*.xml bestanden in een map heenloopt en ze telkens POST naar Invantive App Online. Zie onder voor script.
Probleem is dat bij uitvoering van Curl, volgens Invantive App Online de formulierwaardes voor p_run en p_file niet doorkomen. In plaats van het bestand verwerken ontvangt curl het HTML-scherm om een bestand op te geven. Via de Firefox-browser kan ik wel een bestand uploaden.
In Invantive App Online Monitoring zie ik bij het request een itgenimg019 melding:
Aanvraagheaders: Accept: /, Authorization: (header aanwezig), Connection: close, Content-Length: 828, Content-Type: multipart/form-data; boundary=------------------------4be6d4e6efc53fe1, Host: app-online.cloud, User-Agent: curl/7.82.0.
Hoe kan ik vanuit Curl massaal de XML-bestanden uploaden of hoe kan ik de oorzaak achterhalen?
Alle WICS XML pick order bevestigingen uploaden
set XMLPATH=C:\Users\...\Desktop\XML shipment files
for /f %%f in ('dir /b "%XMLPATH%\ShipmentStatus-*.xml"') do echo "C:\curl\curl\bin\curl.exe" -X POST -F 'p_run=true' -F "p_file=@%XMLPATH%\%%f" --user john.doe@acme.com:secret https://app-online.cloud/apps/.../databases/...-exact-online/modules/.../
WICS XML pick order bevestigingen inlezen
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 := to_char(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 )
;
--
-- Accept POST of XML file with picked items.
--
create or replace table output@inmemorystorage
as
select txt
label 'Text'
from csvtable
( passing l_errors_1 || chr(13) || l_errors_2 || chr(13) || l_errors_3
row delimiter chr(13)
column delimiter '###shouldneveroccur###'
columns txt varchar2 position next
)
where txt is not null
;
select count(*)
into l_cnt_output
from output@inmemorystorage
;
if l_cnt_output = 0
then
l_xlsx_filename := 'shipping-results-success-' || to_char(sysdateutc, 'YYYYMMDDHH24MISS') || '.xlsx';
cloud_http.set_response_status_code(200);
else
l_xlsx_filename := 'shipping-results-errors' || to_char(sysdateutc, 'YYYYMMDDHH24MISS') || '.xlsx';
--
-- Register as Bad Request.
--
cloud_http.set_response_status_code(400);
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, 'A3', 'Created (UTC)');
excel.set_cell_contents(l_worksheet, 'B3', sysdateutc);
excel.set_cell_contents(l_worksheet, 'A4', 'File:');
excel.set_cell_contents(l_worksheet, 'B4', l_xml_filename);
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'Shipping Results'
);
excel.fill_using_query(l_worksheet, 'select txt 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 || '"');
--
-- Register in system messages.
--
dbms_audit.register_event('xxscgwicswms2eolpickeditems001', 'Processed ' || l_xml_filename || ' to ' || l_xlsx_filename || '.', natural_key => l_xml_filename);
end if;
end;