Excel sheet voor bestel- en voorraadplanning uit Exact Online

Corona heeft gezorgd voor een enorme omzetverschuiving naar levering aan huis vanaf webshops en groothandels. Dergelijke webshops draaien in Nederland vaak op Magento en Exact Online. Dit veroorzaakt voor Exact Online een versnelling van het aantal verkoopregels per maand over alle webshops heen, maar ook de voorraadplanning wordt er niet eenvoudiger op. Veel goederen komen met een container uit bijvoorbeeld China.

Een regelmatig terugkerende wens is het opvragen als Excel sheet van de huidige voorraden in Exact Online, samen met de geplande voorraadmutaties op basis van geplaatste verkooporders en inkooporders onderweg. Hiermee kan een planner zijn eigen “MRP” run doen qua voorraadplanning en het plannen van nieuwe bestellingen op de juiste momenten om de voorraadkosten zo laag mogelijk te houden.

Onderstaand SQL script maakt een drietal Excel bestanden in de Documenten-map beperkt tot een specifiek magazijn en een aantal productgroepen. In die Excel bestanden staan de volgende gegevens:

  • actuele voorraad, geplande uitstroom en geplande instroom per artikel.
  • open regels van verkooporders
  • open regels van inkooporders

Om dit script uit te voeren start je het Invantive Query Tool en plak je na het aanmelden op Exact Online het script in de editor.

Druk dan op F5 of gebruik de knop “Alles Uitvoeren”.

Ook kun je de SQL verwerken in Get My Report of Invantive Cloud.

local define WAREHOUSE_FILTER "001"

select to_char(sysdate, 'YYYYMMDDHH24MISS')

local define TIMESTAMP "${outcome:0,0}"

create or replace table itmsel@inmemorystorage
as
select *
from   exactonlinerest..itemsbulk itm
where  itm.itemgroupcode in ('ProductGroep1', 'ProductGroep2')

select itm.itemgroupcode
,      spn.ITEM_CODE_ATTR
,      spn.ITEM_DESCRIPTION
,      spn.CURRENTQUANTITY
,      spn.PLANNING_IN
,      spn.PLANNING_OUT
from   stockpositions spn
join   itmsel@inmemorystorage itm
on     itm.code = spn.item_code_attr
where  spn.WAREHOUSE_CODE_ATTR = '${WAREHOUSE_FILTER}'
order
by     itm.itemgroupcode
,      spn.ITEM_CODE_ATTR

local export results as "${system:userdocumentsdirectory}\invantive-${TIMESTAMP}-voorraad.xlsx" format xlsx include headers

select por.ordernumber
,      por.orderdate
,      por.description
,      por.yourref
,      por.SupplierCode
,      por.SupplierName
,      por.orderstatus
,      ple.itemcode
,      ple.itemdescription
,      itm.itemgroupcode
,      ple.receiptdate
,      ple.quantity
,      ple.ReceivedQuantity
,      ple.quantity
       - ple.ReceivedQuantity
       OpenQuantity
       label 'Open Quantity'
from   exactonlinerest..purchaseorders por
join   exactonlinerest..purchaseorderlines ple
on     ple.PurchaseOrderID = por.PurchaseOrderID
and    ple.quantity != ple.ReceivedQuantity
join   itmsel@inmemorystorage itm
on     itm.code = ple.itemcode
where  por.orderstatus not in (30 /* Complete. */, 40 /* Canceled. */)
and    por.warehousecode = '${WAREHOUSE_FILTER}'
order
by     por.ordernumber
,      ple.linenumber

local export results as "${system:userdocumentsdirectory}\invantive-${TIMESTAMP}-inkoop.xlsx" format xlsx include headers

select sor.ordernumber
,      sor.orderdate
,      sor.description
,      sor.yourref
,      sor.InvoiceToName
,      sor.status
,      sle.itemcode
,      sle.itemdescription
,      itm.itemgroupcode
,      sle.DeliveryDate
,      sle.quantity
,      sle.QuantityDelivered
,      sle.quantity
       - sle.QuantityDelivered
       OpenQuantity
       label 'Open Quantity'
from   exactonlinerest..salesorders sor
join   exactonlinerest..salesorderlines sle
on     sle.orderid = sor.OrderID
and    sle.quantity != sle.QuantityDelivered
join   itmsel@inmemorystorage itm
on     itm.code = sle.itemcode
where  sor.status not in (21 /* Complete. */, 45 /* Canceled. */)
and    sor.warehousecode = '${WAREHOUSE_FILTER}'
order
by     sor.ordernumber
,      sle.linenumber

local export results as "${system:userdocumentsdirectory}\invantive-${TIMESTAMP}-verkoop.xlsx" format xlsx include headers