Need to create a report of Items sold= last sale date report

[Former Member]

Hi Guys,

There is an column of last sale date in OITM but that doesnt gives any data when you run a query. Basicaly client need to generate a report of Items with last sale date, is this possible SAP Business One?

Regards

[Former Member · Accepted solution]

Hi Joe

Yes it is possible if you use GROUP BY in a query with MAX(T0.DocDate).

An example would be:

SELECT T0.ItemCode, MAX(T0.DocDate) AS 'Last Sale Date', SUM(T0.LineTotal)

FROM INV1 T0

WHERE ????

GROUP BY T0.ItemCode

Kind regards

Peter Juby

[Former Member · Accepted solution]

Hi,

Thanks, can you tell me why we cant get last sale date data from OITM?

[Former Member]

Hi Joe

I have no idea why, but I checked a database that is almost 5 years old and the last sale date was never populated against any items, only Last Purchase Date. Might be a country specific field for some reason.

Kind regards

Peter Juby

[K_Pauquet]

Hi Joe,

you could use the stock posting list report with a suitable date range & select only AR invoices in the 'Expanded' options, then sort by posting date in descending order

This report can then be exported to Excel & all irrelevant transactions can be deleted, keeping only the first & second row for each item (Row 1 = Itemcode, Row 2 = last stock posting from AR invoice showing in the 'Price after discount' column).

Alternatively, you might explore whether an SDK solution may be created that runs the last prices report for all items.

All the best,

Kerstin