Met hulp van het volgende script downloaden we alle pakbonnen uit Exact Online die nog geen trackingnummer hebben. De pakbonnen worden daarna geupload naar WICS (Warehouse Management Systeem).
Als het script gedraaid wordt vanuit Invantive Cloud, dan pakt ie netjes de nieuwe pakbonnen op.
Als gedraaid via App Online, dan komt er telkens dezelfde uitvoer uit (gemeten in bytes en in inhoud, gemeten in drie pogingen circa 10 minuten uit elkaar).
Merk op dat zowel de HTTP disk cache als HTTP memory cache uitgezet worden.
Hoe kan ik de meest recente pakbonnen laten gebruiken in App Online voor WICS WMS?
declare
l_start_date date;
l_xml varchar2;
begin
--
-- Generate list of delivery orders for the last two weeks
-- for import into WICS WMS.
--
-- Delivery orders from multiple downloads will overlap by design.
-- The receiving party must merge them into their own database.
-- Overlapping delivery orders will always have the same contents.
--
set use-http-disk-cache@eol false;
set use-http-memory-cache@eol false;
--
l_start_date := trunc(sysdateutc) - 14;
--
create or replace table pickorderrequests@inmemorystorage
as
select gdy.entryid
, gdy.deliverynumber
, gdy.deliveryaccountname
, gdy.deliverydate
, gdy.deliverycontactpersonfullname
, ads.addressline1
, ads.postcode
, ads.state
, ads.city
, ads.country
, act.email
, act.phone
, case
when ads.country in ('NL', 'BE', 'DE', 'PT', 'CH', 'DK', 'LT', 'IT', 'SE', 'ID', 'ES', 'AT', 'NO', 'CZ')
then regexp_replace(trim(ads.addressline1), '^(.*) ([0-9]+)(| )(|[a-zA-Z]|[a-zA-Z][a-zA-Z]|[a-zA-Z][a-zA-Z][a-zA-Z]|[a-zA-Z][a-zA-Z][a-zA-Z][a-zA-Z])$', '$1')
when ads.country in ('FR', 'GB', 'US', 'SG', 'AU', 'CY', 'IE', 'CN', 'IN')
then regexp_replace(trim(ads.addressline1), '^([0-9]+)(|[a-zA-Z]|[a-zA-Z][a-zA-Z]|[a-zA-Z][a-zA-Z][a-zA-Z]|[a-zA-Z][a-zA-Z][a-zA-Z][a-zA-Z]) (.*)$', '$3')
else ''
end
street
, case
when ads.country in ('NL', 'BE', 'DE', 'PT', 'CH', 'DK', 'LT', 'IT', 'SE', 'ID', 'ES', 'AT', 'NO', 'CZ')
then regexp_replace(trim(ads.addressline1), '^(.*) ([0-9]+)(| )(|[a-zA-Z]|[a-zA-Z][a-zA-Z]|[a-zA-Z][a-zA-Z][a-zA-Z]|[a-zA-Z][a-zA-Z][a-zA-Z][a-zA-Z])$', '$2')
when ads.country in ('FR', 'GB', 'US', 'SG', 'AU', 'CY', 'IE', 'CN', 'IN')
then regexp_replace(trim(ads.addressline1), '^([0-9]+)(|[a-zA-Z]|[a-zA-Z][a-zA-Z]|[a-zA-Z][a-zA-Z][a-zA-Z]|[a-zA-Z][a-zA-Z][a-zA-Z][a-zA-Z]) (.*)$', '$1')
else ''
end
street_number
, case
when ads.country in ('NL', 'BE', 'DE', 'PT', 'CH', 'DK', 'LT', 'IT', 'SE', 'ID', 'ES', 'AT', 'NO', 'CZ')
then regexp_replace(trim(ads.addressline1), '^(.*) ([0-9]+)(| )(|[a-zA-Z]|[a-zA-Z][a-zA-Z]|[a-zA-Z][a-zA-Z][a-zA-Z]|[a-zA-Z][a-zA-Z][a-zA-Z][a-zA-Z])$', '$4')
when ads.country in ('FR', 'GB', 'US', 'SG', 'AU', 'CY', 'IE', 'CN', 'IN')
then regexp_replace(trim(ads.addressline1), '^([0-9]+)(|[a-zA-Z]|[a-zA-Z][a-zA-Z]|[a-zA-Z][a-zA-Z][a-zA-Z]|[a-zA-Z][a-zA-Z][a-zA-Z][a-zA-Z]) (.*)$', '$2')
else ''
end
street_number_addition
from goodsdeliveries@eol gdy
join addresses@eol ads
on ads.id = gdy.deliveryaddress
join exactonlinerest..accounts@eol act
on act.id = gdy.deliveryaccount
where gdy.deliverydate >= l_start_date
--
-- Not yet communicated nor reported as shipped.
--
-- When communicated, TrackingNumber = 'COMMUNICATED'.
-- When shipped, TrackingNumber = WICS tracking number.
--
and gdy.TrackingNumber is null
order
by gdy.deliverynumber
;
create or replace table pickorderrequestlines@inmemorystorage
as
select odr.entryid
, odr.deliverynumber
, gle.linenumber
, gle.itemcode
, gle.QuantityDelivered
, gle.SalesOrderNumber
, sle.netprice
, round(sle.netprice / ( 1 + sle.vatpercentage ), 2) netpricefcexclvat
, sor.currency
from pickorderrequests@inmemorystorage odr
join goodsdeliverylines@eol gle
on gle.entryid = odr.entryid
join ExactOnlineREST..SalesOrderLines@eol sle
on sle.Id = gle.SalesOrderLineID
join exactonlinerest..salesorders@eol sor
on sor.orderid = sle.orderid
order
by odr.deliverynumber
, gle.linenumber
;
create or replace table pickorderrequestlinesxml@inmemorystorage
as
select entryid
, deliverynumber
, currency
, listagg(distinct SalesOrderNumber) SalesOrderNumbers
, listagg
( '<Orderline>'
|| xmlelement('LineNumber', linenumber)
|| xmlelement('ItemNum', itemcode)
|| xmlelement('QtyOrdered', QuantityDelivered)
|| '<StockUnit/>'
|| xmlelement('UnitSalesPrice', replace(to_char(netpricefcexclvat), '.', ','))
|| '</Orderline>'
, ''
)
xml
from pickorderrequestlines@inmemorystorage
group
by entryid
, deliverynumber
, currency
;
create or replace table pickorderrequestsxml@inmemorystorage
as
select listagg
( '<Order>'
|| '<OrderType/>'
|| xmlelement('ExternalReference', odr.deliverynumber)
|| xmlelement('Reference2', ole.SalesOrderNumbers)
|| xmlelement('CustomerName', odr.deliveryaccountname)
|| xmlelement('CustomerContactName', odr.deliverycontactpersonfullname)
|| '<Shipper/>'
|| '<Carrier/>'
|| '<Delvmode/>'
|| '<PickUpPoint/>'
|| '<DeliveryDate/>'
|| '<DeliverOnce/>'
|| xmlelement('OrderCurrency', ole.currency)
|| '<Address>'
|| xmlelement('AddressType', 'Delivery')
|| xmlelement('Address', odr.street)
|| xmlelement('HouseNumber', odr.street_number)
|| xmlelement('HouseNumberExt', odr.street_number_addition)
|| xmlelement('ZipCode', odr.PostCode)
|| '<State/>'
|| xmlelement('City', odr.city)
|| xmlelement('Country', odr.country)
|| xmlelement('Email', odr.email)
|| xmlelement('Phonenumber', odr.phone)
|| '</Address>'
|| '<Orderlines>'
|| ole.xml
|| '</Orderlines>'
|| '</Order>'
, ''
)
xml
from pickorderrequests@inmemorystorage odr
join pickorderrequestlinesxml@inmemorystorage ole
on ole.entryid = odr.entryid
;
select xmlformat
( '<?xml version="1.0" encoding="utf-8" ?>'
|| '<Message>'
|| '<Orders>'
|| xml
|| '</Orders>'
|| '</Message>'
)
into l_xml
from pickorderrequestsxml@inmemorystorage
;
--
-- Register as (probably) communicated with WICS.
--
update GoodsDeliveries@eol
set TrackingNumber = 'COMMUNICATED'
where entryid in ( select /*+ low_cost */ entryid from pickorderrequests@inmemorystorage )
;
cloud_http.set_response_body_text(l_xml);
cloud_http.set_response_content_type('text/xml');
cloud_http.set_response_header_value('Content-Disposition', 'attachment; filename="wics-wms-pick-requests.xml"');
end;