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 itm.division
, itm.id
, itm.code
, itm.description
, itm.netweight
, itm.grossweight
, itm.itemgroup
, igp.code
ItemGroupCode
label 'Artikelgroepcode'
, igp.description
ItemGroupDescription
label 'Artikelgroepomschrijving'
from itemsincremental itm
join exactonlinerest..itemgroups igp
on igp.id = itm.itemgroup
where itm.IsStockItem = true
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
create or replace table transactionoverview@InMemoryStorage
as
select spn.division
, itm.ItemGroupCode
, itm.ItemGroupDescription
, 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 items@inmemorystorage itm
on itm.id = spn.itemid
order
by itm.code
, itm.description
, spn.maincategory
select *
from transactionoverview@InMemoryStorage
local export results as "${system:userdocumentsdirectory}\dump-historische-voorraad-per-${DATE_START}.xlsx" format xlsx include headers
select wtn.DIVISION
, wtn.WAREHOUSECODE
, wtn.transactiondate
, wtn.ITEMCODE
, itm.ItemGroupCode
, itm.ItemGroupDescription
, wtn.Quantity
, wtn.maincategory
, wtn.category
, wtn.ENTRYNUMBER
, wtn.LINENUMBER
from warehousetransactions@inmemorystorage wtn
join items@inmemorystorage itm
on itm.id = wtn.item
local export results as "${system:userdocumentsdirectory}\dump-historische-transacties-vanaf-${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.
Op Get My Report is nu een module “Stock Transactions” te vinden waarmee historische voorraden gereconstrueerd kunnen worden voor alle artikelen vanaf de ingevoerde datum tot/met heden.