Here is a view to show customer specific item pricing in Dynamics GP when using standard pricing. This will work either with SmartList Builder or Crystal Reports as I have made sure not to put any spaces in the names. If you are using SmartList Builder this means you may want to go down the list of the column names and add spaces to make them more user friendly.
For other SQL views on Dynamics GP data, please visit my GP Reports page.
CREATE VIEW view_Customer_Pricing AS /** view_Customer_Pricing Created Jan. 7 2009 by Victoria Yudin - Flexible Solutions, Inc. For updates see https://victoriayudin.com/gp-reports/ Shows individual item pricing based on customer price level. For use with standard pricing. Excludes customers with no price level. Updated Jan. 14 2009 to add price method, % column value if price list is not currency amount and calculation for % of List Price price method. Does not show actual price for % Margin or % Markup price methods. **/ SELECT C.CUSTNMBR as 'CustomerID', C.CUSTNAME as 'CustomerName', C.PRCLEVEL as 'PriceLevel', IV.ITEMNMBR as 'ItemNumber', IM.ITEMDESC as 'ItemDescription', CASE IM.PRICMTHD WHEN 1 THEN 'Currency Amount' WHEN 2 THEN '% of List Price' WHEN 3 THEN '% Markup – Current Cost' WHEN 4 THEN '% Markup – Standard Cost' WHEN 5 THEN '% Margin – Current Cost' WHEN 6 THEN '% Margin – Standard Cost' END as 'PriceMethod', IV.CURNCYID as 'CurrencyID', IV.UOFM as 'UofM', CASE IM.PRICMTHD WHEN 1 THEN IV.UOMPRICE WHEN 2 THEN IV.UOMPRICE * IC.LISTPRCE / 100 ELSE 0 END as 'Price', CASE IM.PRICMTHD WHEN 1 THEN 0 ELSE IV.UOMPRICE END as 'Percent', IV.FROMQTY as 'FromQty', IV.TOQTY as 'ToQty', IV.QTYBSUOM as 'QtyInBaseUofM' FROM RM00101 C LEFT OUTER JOIN IV00108 IV ON C.PRCLEVEL = IV.PRCLEVEL LEFT OUTER JOIN IV00101 IM ON IM.ITEMNMBR = IV.ITEMNMBR LEFT OUTER JOIN IV00105 IC ON IC.ITEMNMBR = IV.ITEMNMBR AND IV.CURNCYID = IC.CURNCYID WHERE C.PRCLEVEL <> '' --excludes customers with no price level /** the following will grant permissions to this view to DYNGRP, leave this section off if you do not want to grant permissions **/ GO GRANT SELECT ON view_Customer_Pricing TO DYNGRP
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.