Historische voorraad in Exact Online bepalen per magazijn

Hoe kan ik de historische voorraad per peildatum in aantallen, nettogewicht en brutogewicht per artikel en magazijn ophalen uit Exact Online?

Dat zal iets van de volgende vorm zijn, waarbij de huidige voorraad gepakt wordt als uitgangspunt en er cumulatieve voorraadstromen bepaald worden terug tot aan de peildatum uit de volgende onderdelen:

  • Ontvangst in het magazijn.
  • Uitgifte uit het magazijn.
  • Correctie door voorrraadtelling.
  • Interne transfer, zowel in- als uitgaand van het magazijn.

Mogelijk dat er meer bronnen zijn of fouten in zitten, maar dit moet een startpunt zijn:

--
-- Bepaal historische voorraad in hoeveelheden, nettogewicht en brutogewicht.
--
-- Peildatum instellen via DATE_START.
--

local define DATE_START "20210801"

create or replace table warehousetransactions@inmemorystorage
as
select gdy.division
,      gdy.warehouse
,      gdy.warehousecode
,      gdy.deliverydate 
       transactiondate
       label 'Transactiedatum'
,      gle.item
,      gle.itemcode
,      -1 * gle.QuantityDelivered
       Quantity
,      'Uitgifte'
       maincategory
,      'Uitgifte'
       category
,      gdy.entrynumber
,      gle.linenumber
from   goodsdeliveries gdy
join   goodsdeliverylines gle
on     gle.entryid = gdy.entryid
where  gdy.deliverydate > to_date('${DATE_START}', 'YYYYMMDD')
union all
select grt.division
,      grt.warehouse
,      grt.warehousecode
,      grt.receiptdate
,      grl.item
,      grl.itemcode
,      +1 * grl.QuantityReceived
       Quantity
,      'Ontvangst'
       maincategory
,      'Ontvangst'
       category
,      grt.entrynumber
,      grl.linenumber
from   goodsreceipts grt
join   goodsreceiptlines grl
on     grl.GoodsReceiptID = grt.ID
where  grt.receiptdate > to_date('${DATE_START}', 'YYYYMMDD')
union all
select wtr.division
,      wtr.warehousefrom
,      wtr.warehousefromcode
,      wtr.transferdate
,      wtl.item
,      wtl.itemcode
,      -1 * wtl.Quantity
       Quantity
,      'Interne uitgifte'
       maincategory
,      'Interne uitgifte'
       category
,      wtr.TransferNumber
,      wtl.linenumber
from   exactonlinerest..warehousetransfers wtr
join   exactonlinerest..warehousetransferlines wtl
on     wtl.transferid = wtr.transferid
where  wtr.transferdate >= to_date('${DATE_START}', 'YYYYMMDD')
and    wtr.warehousefromcode != wtr.warehousetocode
and    wtr.Status = 50 /* Final */
union all
select wtr.division
,      wtr.warehouseto
,      wtr.warehousetocode
,      wtr.transferdate
,      wtl.item
,      wtl.itemcode
,      +1 * wtl.Quantity
       Quantity
,      'Interne ontvangst'
       maincategory
,      'Interne ontvangst'
       category
,      wtr.TransferNumber
,      wtl.linenumber
from   exactonlinerest..warehousetransfers wtr
join   exactonlinerest..warehousetransferlines wtl
on     wtl.transferid = wtr.transferid
where  wtr.transferdate >= to_date('${DATE_START}', 'YYYYMMDD')
and    wtr.warehousefromcode != wtr.warehousetocode
and    wtr.Status = 50 /* Final */
union all
select sct.division
,      sct.warehouse
,      sct.warehousecode
,      sct.StockCountDate
,      scl.item
,      scl.itemcode
,      scl.QuantityDifference
       Quantity
,      'Voorraadcorrectie'
       maincategory
,      'Correctie ' || sct.OffsetGLInventoryDescription
       category
,      sct.EntryNumber
,      scl.linenumber
from   exactonlinerest..stockcounts sct
join   exactonlinerest..stockcountlines scl
on     scl.stockcountid = sct.StockCountID
and    scl.QuantityDifference != 0
where  sct.status = 21 /* Processed. */
and    sct.stockcountdate >= to_date('${DATE_START}', 'YYYYMMDD')

create or replace table items@inmemorystorage
as
select division
,      id
,      netweight
,      grossweight
from   itemsincremental

create or replace table stock@inmemorystorage
as
select spn.division
,      spn.itemid
,      whe.code
       WarehouseCode
       label 'Magazijncode'
,      sum(spn.CurrentStock)
       CurrentStock
       label 'Huidige Voorraad'
from   stockpositionsincremental spn
join   exactonlinerest..warehouses whe
on     whe.id = spn.warehouse
group
by     spn.division
,      spn.itemid
,      whe.code

create or replace table transactionstatistics@inmemorystorage
as
select wtn.division
,      wtn.maincategory
,      wtn.item
,      wtn.warehousecode
,      sum(wtn.quantity) transaction_quantity
,      count(*) transaction_cnt
,      count(distinct wtn.transactiondate) transaction_days_cnt
from   warehousetransactions@inmemorystorage wtn
group
by     wtn.division
,      wtn.maincategory
,      wtn.item
,      wtn.warehousecode

select spn.division
,      igp.code
       label 'Artikelgroepcode'
,      igp.description
       label 'Artikelgroepomschrijving'
,      itm.code
       label 'Artikelcode'
,      itm.description
       label 'Artikelomschrijving'
,      spn.Quantity
,      spn.WarehouseCode
,      spn.maincategory
,      itm.netweight * spn.Quantity
       NetweightStock
,      itm.grossweight * spn.Quantity
       GrossWeightStock
from   ( select 'Huidige' 
                maincategory
                label 'Categorie'
         ,      spn.division
         ,      spn.warehousecode
         ,      spn.itemid
         ,      spn.CurrentStock
                Quantity
         from   stock@inmemorystorage spn
         union  
         select tst.maincategory
         ,      tst.division
         ,      tst.warehousecode
         ,      tst.item
         ,      tst.Transaction_Quantity
         from   transactionstatistics@inmemorystorage tst
       ) spn
join   itemsincremental itm
on     itm.id       = spn.itemid
join   exactonlinerest..itemgroups igp
on     igp.id = itm.itemgroup
order
by     itm.code
,      itm.description
,      spn.maincategory

local export results as "${system:userdocumentsdirectory}\dump-historische-voorraad-per-${DATE_START}.xlsx" format xlsx include headers

De resulterende Excel-spreadsheet met de artikelen, de huidige voorraad en voorraadstromen kan dan bijvoorbeeld met een draaitabel verwerkt worden. De naam van de export is in Name Manager in Excel terug te vinden en zal normaliter ‘resultset1’ zijn.