Query - Top 25 items purchased from a customer
This SAP Business One Query lists the Top 25 items purchased from a customer, loading in the most current price based on the customers price list assignment. The things you can do with this query is amazing. For example, you can set it up as a formatted search from the Order or Quote Item number field, providing a quick list of items the customer always orders, or execute this query during order entry to provide instant sales history from this customer.
SELECT T1.[ItmsGrpNam], T0.[ItemName] as 'Item Name',T0.[ItemCode] as 'Item Code', ISNULL(NULLIF(T0.[SalUnitMsr],''), 'ea') as 'UoM' ,
T2.Price as 'List Price',
T3.ISOCurrCod as 'Currency',
T0.SalUnitMsr As MeasureUnit,
T0.NumInSale As UnitsOfMeasurment
FROM OITM T0
INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod
INNER JOIN ITM1 T2 on T0.ItemCode = T2.ItemCode
INNER JOIN OCRN T3 on T2.Currency = T3.CurrCode
WHERE T0.ItemCode in
(
SELECT TOP 25 L1.[ItemCode] FROM ORDR O1
INNER JOIN RDR1 L1 ON O1.[DocEntry] = L1.[DocEntry]
INNER JOIN OITM I1 ON I1.[ItemCode] = L1.[ItemCode]
WHERE O1.[CardCode] = [%CardCode] AND
I1.[SellItem] = 'Y'
GROUP BY L1.[ItemCode]
ORDER BY max(O1.[DocDate]) DESC)
AND T2.PriceList = (SELECT M1.ListNum FROM OCRD M1 WHERE M1.CardCode = [%CardCode])
ORDER BY T1.[ItmsGrpNam], T0.[ItemName]