Track Exact Online Batches Added Value

Wiki (en)
,
1

This article provides a sample how to report on and analyze financial performance of batches of articles in Exact Online. It concentrates on the Invantive SQL needed to collect the data. Visualization can be done using Power BI, Power Query, Invantive Control or just use the Excel dumps created.

Instructions

Execute the following steps:

  • Start Invantive Query Tool.
  • Select the Exact Online country.
  • Log on.
  • Run the following query to get a list of the batches registered and export them as eol-batches.xlsx in the user’s Documents folder:
select snr.DivisionLabel
,      snr.BatchNumber
,      snr.BatchNumberID
,      snr.ItemCode
,      snr.ItemDescription
,      snr.Quantity
,      snr.Remarks
from   stockbatchnumbers snr
order
by     snr.batchnumber

local export results as "${system:userdocumentsdirectory}\eol-batches.xlsx" format xlsx
  • Next, we collect both purchase and sales orders per batch and export them as a spreadsheet with Exact Online batch transactions:
--
-- Outbound batches and their value.
--
select 'Sales'
       category
,      gdr.DeliveryDate
,      gdr.SalesOrderNumber
,      gdr.SalesOrderLineNumber
,      gdr.BnrBatchNumber
,      gdr.BnrBatchNumberID
,      gdr.itemcode
,      sle.AmountDC 
       AmountTotalDC 
       label "Amount Total (DC)"
,      sle.DeliveryStatus
,      sle.DeliveryDate
,      sle.InvoiceStatus
,      sle.Quantity
,      sle.QuantityDelivered
,      sle.QuantityInvoiced
,      active.code
       CounterPartyCode
       label 'Counter Party Code'
,      sor.invoicetoname
       CounterPartyName
       label 'Counter Party Name'
from   goodsdeliverylinebatchnumbers gdr
join   exactonlinerest..salesorderlines sle
on     sle.ordernumber = gdr.salesordernumber
and    sle.linenumber  = gdr.salesorderlinenumber
join   exactonlinerest..salesorders sor
on     sor.orderid = sle.orderid
join   exactonlinerest..accounts active
on     active.id = sor.invoiceto
union all
--
-- Incoming batches.
--
select 'Purchase' category
,      grr.Created
,      grr.PurchaseOrderNumber
,      ple.LineNumber
,      grr.BnrBatchNumber
,      grr.BnrBatchNumberID
,      grr.ItemCode
,      ple.AmountDC 
       AmountTotalDC 
       label "Amount Total (DC)"
,      null DeliveryStatus
,      null DeliveryDate
,      null InvoiceStatus
,      ple.Quantity
,      ple.ReceivedQuantity
,      ple.InvoicedQuantity
,      por.suppliercode
       CounterPartyCode
       label 'Counter Party Code'
,      por.suppliername
       CounterPartyName
       label 'Counter Party Name'
from   goodsreceiptlinebatchnumbers grr
join   exactonlinerest..purchaseorderlines ple
on     ple.id = grr.PurchaseOrderLineID
join   exactonlinerest..purchaseorders por
on     por.purchaseorderid = ple.purchaseorderid

local export results as "${system:userdocumentsdirectory}\eol-batch-transactions.xlsx" format xlsx
  • The sample can be extended by stock corrections and margin adjustments for purchases made directly for a sales order.