Exact Online Items to Order for Drop Shipment

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
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
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.