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 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.
Victoria Yudin 
Hi Victoria, this script is great! Your blog has been a huge help to our organization in supplying me with extremely valuable GP reporting information.
Is there a way to add the actual price of the product for each price level into this script? I would like to be able to run a report to see each SKU, The price levels, and actual price of the product for each price level so I can quickly pull reports and filter to send to customers as a price sheet. Is this possible?
Thank you in advance for your assistance.
LikeLike
Hi DDB,
This code should show the actual price unless you are using % Margin or % Markup for the price method. Depending on which and on the option for them, the calculation will be different. I don’t have that already written, as we don’t have too many clients using those price methods. Let me know if I am misunderstanding your question or if you’re not using those price methods.
-Victoria
LikeLike
Hi Victoria, we have GP2015. I tried running this script and received a messgae – Msg 208, Level 16, State 1, Line 1
Invalid object name ‘RM00101’.
CAn you help?
David
LikeLike
Hi David,
Sounds like you might be running this against the wrong database – please make sure you are using your GP company database.
-Victoria
LikeLike
Hello Victoria,
First I want to say thank you for your blog. We have used many things off of your blog that made our employee’s lives easier. Again, THANK YOU.
I have a question. We used this SQL script so we could quickly look up pricing for customers in the Smartlist. This is especially useful if the customer buys multiple items. What we are now trying to accomplish is to make a shortcut to this Smartlist information from the Customer Maintenance Card so the CSR doesn’t have to go far to get the info.
For instance, a customer calls in and our customer service reps pull up the Customer Maintenance Card. Many times the customer will say whats my price on this item and what about this item etc. As you know this can be troublesome to look up in GP. So we wanted to setup a shortcut to this Smartlist we setup for pricing from the Customer Maintenance Card. We thought using Extender might work but not sure. Thoughts? Thanks!
LikeLike
Hi Tim,
Thank you for your kind words!
Without a customization, I am not sure how you can pop a SmartList open from any window. Nor can you do this with Extender. 😦
If a 3rd party product is an option, you could create a Crystal or SSRS report based on the view. Using our GP Reports Viewer product (http://www.GPReportsViewer.com/GP-Reports-Viewer) you can link the report to any window where the customer ID is selected – it could be the Customer Maintenance window, or the Sales Transaction Entry window or anywhere else in GP. Not sure if this is overkill for one request like this, but if you can see multiple instances of similar needs, then it might start making sense.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
will this also work with MS Visual Studio reports?
LikeLike
Menno,
Yes, it will work just like a SQL table that you would bring into your report.
-Victoria
LikeLike
Excellent… very fine SQL script, congratulations victoria
LikeLike