Exact Online environments differ widely in their size. In this sample for an environment with a large volume of orders and items the need arose for an overview per supplier of items to order for drop shipment.
A first query selects the items to select open sales orders for, given the large volume of sales orders and the limited volume of items that are both out-of-stock and eligible for drop shipment:
--
-- Select all items out-of-stock which
-- the main supplier supports drop shipment for.
--
create or replace table itemsfordropshipment@inmemorystorage
as
select isr.item
, iwe.projectedstock
, isr.suppliercode
, isr.supplierdescription
from supplieritems isr
join exactonlinerest..itemwarehouses iwe
on iwe.division = isr.division
and iwe.item = isr.item
and iwe.ProjectedStock < 0
where isr.MainSupplier = true
--
-- Dropshipment is possible.
--
and isr.dropshipment != 0
Query runtime is typically 15 seconds.
All open sales orders for these items are then selected using a query such as:
--
-- Overview of all unfullfilled sales order lines
-- for the drop shipment items.
--
select idt.suppliercode
, idt.supplierdescription
, idt.projectedstock
, itm.* prefix with 'itm_' label prefix with 'Artikel '
, sor.* prefix with 'sor_' label prefix with 'Bestelling '
, sle.* prefix with 'sle_' label prefix with 'Regel '
from itemsfordropshipment@inmemorystorage idt
join exactonlinerest..salesorderlines sle
--
-- Open or partial open.
--
on sle.deliverystatus in (12, 20)
and sle.item = idt.item
join exactonlinerest..salesordersbulk sor
on sor.division = sle.division
and sor.orderid = sle.orderid
--
-- Open or partial open.
--
and sor.deliverystatus in (12, 20)
join exactonlinerest.items itm
on itm.division = sle.division
and itm.id = sle.item
order
by idt.supplierdescription
, sor.DeliverToName
This query typically runs 5 seconds.
After that the results are exported to an Excel sheet in the current OS-user’s document folder:
local export results as "${system:userdocumentsdirectory}\items-to-order.xlsx" format xlsx include headers
The spreadsheet created can be changed into a report using a pivot table in report layout, Power Query or Power BI.