Incidenteel worden bestellingen direct geplaatst bij een grote handelspartner die dan een verzameld overzicht opstuurt van alle bestellingen die onderweg zijn. Pas dan wordt de inkooporder opgevoerd in Exact Online.
Dit besteloverzicht kan aangeboden worden door de leverancier als CSV-bestand, XML-bestand of Excel-bestand. Soms betreft het één bestand, soms zijn het meerdere bestanden, bijvoorbeeld één bestand per bestelling.
In dit voorbeeld is het de bedoeling om de bestellingen uit een redelijk ongestructureerd Excel-sheet te importeren in Exact Online. Aangezien Exact Online formeel maximaal 250 regels op een individuele bestelling kent moet de bestelling met mogelijk 1.000 regels of meer opgeknipt worden in een aantal losse bestellingen in Exact Online.
De relatie tussen de artikelen in het Excel-bestand en Exact Online artikelen is op basis van EAN (barcode). De artikelprijs, BTW-code, munteenheid en grootboekrekeningen worden uit de Exact Online-administratie gebruikt.
De getoonde code kan gedraaid worden met Invantive Query Tool of met een minimale aanpassing voor de bron van het bestand via Invantive Cloud. De getoonde code gaat er van uit dat via use maximaal één administratie geselecteerd.
Voor gebruik met CSV-bestand(en) dient exceltable vervangen te worden door csvtable. Voor XML-bestand(en) kan xmltable gebruikt worden.
--
-- Lees Excel sheet sales.xlsx op het bureaublad in met de volgende specificaties:
--
-- * Cel D7 bevat 'Order Number' en in E7 het ordernummer
-- * Cel D9 bevat 'Purchase Order Number' en in E9 de waarde.
-- * Cel D10 bevat 'Purchase Order Date' en in E10 de waarde.
-- * Op regel 26 13 kolomkoppen, met daaronder de volgende velden met één waarde per kolom:
-- 1. LineNo
-- 2. Silhouette
-- 3. License
-- 4. Team
-- 5. Material
-- 6. Material Description
-- 7. Size
-- 8. UPC (EAN / barcode).
-- 9. Quantity
-- 10. Rejection Code (indien bekend)
-- 11. Confirmed Date (bevestigde leverdatum)
-- 12. Item Price
-- 13. Total Price
--
-- De Excel-regels worden per 250 gecombineerd tot 1 inkooporder.
-- Prijzen uit Exact Online krijgen voorrang boven de prijzen uit het Excel-sheet.
--
declare
l_missing_ean varchar2;
l_cnt varchar2;
--
p_file_name varchar2 := '${system:userdesktopdirectory}\sales.xlsx';
--
g_batch_size pls_integer := 250;
g_excel_sheet_name varchar2 := 'Sheet1';
g_shipping_method varchar2 := '2';
g_item_group_code varchar2 := '1';
g_supplier_code varchar2 := '3';
begin
create or replace table ExcelLines@InMemoryStorage
as
select xlsx.*
, to_char(floor(row_number() / g_batch_size)) transaction_number
from exceltable
( worksheet g_excel_sheet_name
passing file p_file_name
skip empty rows
columns a varchar2 position 1
, b varchar2 position 2
, c varchar2 position 3
, d varchar2 position 4
, e varchar2 position 5
, f varchar2 position 6
, g varchar2 position 7
, h varchar2 position 8
, i varchar2 position 9
, j varchar2 position 10
, k varchar2 position 11
, l varchar2 position 12
, m varchar2 position 13
) xlsx
;
create or replace table OrderProperties@inmemorystorage
as
select max
( case
when xlsx.d = 'Purchase Order Number'
then xlsx.e
end
)
PurchaseOrderNumber
, max
( case
when xlsx.d = 'Customer Currency'
then xlsx.e
end
)
CustomerCurrency
, max
( case
when xlsx.d = 'Order Number'
then xlsx.e
end
)
OrderNumber
, max
( case
when xlsx.d = 'Purchase Order Date'
then to_date(replace(xlsx.e, '.', '-'), 'DD-MM-YYYY')
end
)
PurchaseOrderDate
from ExcelLines@InMemoryStorage xlsx
;
create or replace table ImportPurchaseOrderLines@InMemoryStorage
as
select t.*
, act.id EolSupplierId
, smd.id EolShippingMethodId
, opy.PurchaseOrderNumber || '-' || t.transaction_number EolDescription
, opy.OrderNumber EolYourRef
, opy.PurchaseOrderDate EolOrderDate
from ExactOnlineREST..Accounts act
join exactonlinerest..shippingmethods smd
on smd.code = g_shipping_method
join OrderProperties@inmemorystorage opy
join ( select itm.division EolDivision
, itm.id EolItemId
, itm.code EolItemCode
, to_number(xlsx.a) LineNo
, xlsx.b Silhouette
, xlsx.c License
, xlsx.d Team
, xlsx.e Material
, xlsx.f MaterialDescription
, xlsx.g Size
, xlsx.h UPC
, cast(to_number(xlsx.i) as int) Quantity
, xlsx.j RejectionCode
, cast(to_date(replace(xlsx.k, '.', '-'), 'dd-mm-yyyy') as date) ConfirmedDate
, to_number(xlsx.l) ItemPrice
, to_number(xlsx.m) TotalPrice
, xlsx.e || '_' || xlsx.g ExpectedEolItemCode
, xlsx.transaction_number
from ExcelLines@InMemoryStorage xlsx
left
outer
join itemsincremental itm
on itm.barcode = xlsx.h
left
outer
join exactonlinerest..itemgroups igp
on igp.id = itm.itemgroup
and igp.code = g_item_group_code
where xlsx.a is not null
and xlsx.g is not null
and xlsx.m is not null
and xlsx.i is not null
and xlsx.a != 'Line No'
order
by xlsx.h
) t
where act.Code = g_supplier_code
;
--
-- Controleer tenminste 1 bestellingregel.
--
select count(*)
into l_cnt
from ImportPurchaseOrderLines@InMemoryStorage
;
if l_cnt = 0
then
raise_application_error
( 'xxacme002'
, 'De bestelling bevat geen regels.'
);
end if;
--
-- Controleer dat alle EAN nummers bestaan.
--
select listagg(distinct upc, ', ')
into l_missing_ean
from ImportPurchaseOrderLines@InMemoryStorage
where EolItemId is null
;
if l_missing_ean is not null
then
raise_application_error
( 'xxacme001'
, 'De volgende EAN nummers zijn niet bekend: '
|| l_missing_ean
|| '.'
);
end if;
--
-- Importeer als purchase orders met elk ongeveer en maximaal 250 regels.
--
-- Circa 5 seconden per 250 regels.
--
begin transaction;
insert into exactonlinerest..purchaseorders
( Description
, Supplier
, YourRef
, ShippingMethod
, OrderDate
)
select ipe.EolDescription Description
, ipe.EolSupplierId Supplier
, ipe.EolYourRef YourRef
, ipe.EolShippingMethodId ShippingMethod
, ipe.EolOrderDate OrderDate
from ImportPurchaseOrderLines@InMemoryStorage ipe
group
by ipe.EolDescription
, ipe.EolSupplierId
, ipe.EolYourRef
, ipe.EolShippingMethodId
, ipe.EolOrderDate
identified
by ipe.EolDescription
;
insert into exactonlinerest..purchaseorderlines
( item
, QuantityInPurchaseUnits
, ReceiptDate
)
select ipe.EolItemId
, ipe.Quantity
, ipe.ConfirmedDate
from ImportPurchaseOrderLines@InMemoryStorage ipe
attach
to ipe.EolDescription
;
commit transaction;
end;