January 7, 2009
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 http://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.
Hello Victoria,
It was defnitely a great script. I would appreicate if you could do an item specific SQL script to list all related price list for an item. It would require to convert rows into column.
francis
John,
I am not sure what you mean by ‘It would require to convert rows into column.’ Can you please explain in a little more detail?
-Victoria
Jim,
Thanks for sharing that. Glad that adding indexes helped. Yes – this is sometimes an issue with views when you have a lot of data. Since SmartList Builder can only take views or tables, we’re a bit limited by that. If this was being used in a Crystal or SRS report (instead of SLB), it might also be better to make this into a stored procedure for better performance.
-Victoria
I love it – except for us as a query it ran for a LONG time (47 secs to get the 234k rows). Profiler had me create 4 nonclustered indexes which takes the time to under 10 secs for the 234k rows
Victoria – I think this view works fine if you are using Currency Amount as the Price Method since the actual price is stored in the IV00108 table. However, if you are using any of the other pricing methods, the actual price is not stored in the IV00108 table – the percentage is. So, depending on the Price Method, you would also need to link to the List Price, Standard Cost, or Current Cost. Correct?
Dick,
You are 100% correct – thank you for pointing this out. I just updated the code to add a column for the Price Method and a column for the Percent. I also added the calculation for the price if the Price Method is % of List Price. I didn’t add the calculations for % Margin or % Markup price methods.
-Victoria
will this also work with MS Visual Studio reports?
Menno,
Yes, it will work just like a SQL table that you would bring into your report.
-Victoria
Excellent… very fine SQL script, congratulations victoria