SAP Business One Simplified Order Guides for Sales Orders
Use this query to display a list of items that a customer likes to buy. It was made to be placed on the Item Number field on the Sales Order as a Formatted Search. The User will press Shift + F2 to display the window as opposed to pressing tab to display the entire catalog of items.
Changing the Where clause to be a parameter will enable you to produce a printed order guide for a customer. If you would like to try out the query without making it a formatted search, just change the $[$-4.0.0] to your favorite customer number surrounded by single quotes.
SELECT DISTINCT
dtl.itemcode ‘Item’
,itm.itemname ‘Item Description’
,ROUND(AVG(quantity),0) ‘Avg Ord’
,AVG(dtl.Price) ‘Avg Price’
,AVG(ROUND(CASE WHEN dtl.linetotal = 0 THEN 0 ELSE dtl.grssProfit/dtl.linetotal END,2))*100 ‘AvgGP%’
,Count(docnum) ‘Total Orders’
,MAX(hdr.docdate) ‘Last Ordered’
,hdr.CardCode
FROM rdr1 dtl
INNER JOIN ordr hdr on dtl.docentry = hdr.docentry
INNER JOIN oitm itm on dtl.itemcode = itm.itemcode
WHERE HDR.cardcode = $[$-4.0.0]
GROUP BY hdr.cardcode, dtl.itemcode, itm.itemname
FOR BROWSE
Thanks to Ed Monk of SBONotes.com
© 2009, Mark Chinsky. All rights reserved. Formed in 2005. the 90 Minds Consulting Group is a collaboration of 100 Sage partners who independently join together online in a 24 x 7 private communication network to solve difficult issues for their customers. The group is not affiliated with Sage and our collaboration provides for unparalleled member access to early warnings of bugs and other issues which members in turn are encouraged to use to provide an exceptional customer experience.
« SAP Business One How To Enter a User Defined Table (UDT) in Query | Home | SAP Business One: Query to Show Customers Without Sales Employees Assigned »
Leave a Comment
You must be logged in to post a comment.