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