Query to get list of shipped serial numbers in Exact Online

Question

We ship item per serial number using Exact Online as ERP system. I seem unable to find the column names to retrieve a list of shipments of serial numbers and associated invoices.

Answer 1

To retrieve a list of sales invoices, use the following query:

select invoicenumber
,      date
,      itemcode
,      quantity
,      amountdc
,      0 listprice /* requires extra sql */
,      0 discountpct /* requires extra sql */
,      '' serialnumbers /* use left outer on goodsdeliverylineserialnumbers */
,      description
,      yourref
,      ordernumber
from   exactonlinerest..transactionlines
where  financialyear   = ${financial_year}
and    financialperiod = ${financial_period}
and    journalcode in (select code from exactonlinerest..journals where glaccounttype=20)
order
by     date

Please note that ${financial_year} and ${financial_period} must either be defined in advance or the user interface tool will ask you to supply a value.

Since the shipment can occur at the same time, later or earlier, you can use a different query which you can join with left outer join into the previous query:

select salesordernumber
,      deliverydate
,      listagg(ssritemcode || ': ' || ssrserialnumber, ', ') ssrserialnumber_list
from   GoodsDeliveryLineSerialNumbers 
group
by     deliverydate
,      salesordernumber
order 
by     deliverydate