SQL view to show customer pricing


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.

16 Responses to “SQL view to show customer pricing”

  1. 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

    Like

  2. 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!

    Like

    • 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

      Like

  3. 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

    Like

  4. 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

    Like

  5. 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

    Like

  6. 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?

    Like

    • 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

      Like

  7. will this also work with MS Visual Studio reports?

    Like

  8. Excellent… very fine SQL script, congratulations victoria

    Like

Trackbacks/Pingbacks

  1. Victoria Yudin - August 9, 2010

    SQL view for Inventory Price Levels in Dynamics GP…

    When you are creating prices for different price levels in Dynamics GP, there is no easy way to see these…

    Like

  2. SQL view for Inventory Price Levels in Dynamics GP « Victoria Yudin - August 9, 2010

    […] you are looking to see customer specific pricing, take a look at my Customer Pricing post.  You can also see a full list of my Dynamics GP SQL code on the GP Reports page of this […]

    Like

  3. SQL View to Show Customer Specific Pricing - DynamicAccounting.net - January 7, 2009

    […] View to Show Customer Specific Pricing On Victoria Yudin's blog today she provides a SQL script to show customer specific item pricing in Dynamics GP. Published: Wednesday, January 07, 2009, 07:30 […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: