SQL view to show customer pricing

January 7, 2009 by Victoria Yudin 16 Comments


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.

Dynamics GP, GP Reports code, GP SQL scripts, Inventory SQL code
, , ,

16 Responses to “SQL view to show customer pricing”

  1. David McIndoe Reply June 23, 2017 at 1:17 am

    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. Tim Smith Reply October 30, 2013 at 11:16 am

    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

    • Victoria Yudin Reply October 30, 2013 at 2:11 pm

      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. John Francis Reply April 30, 2009 at 9:47 am

    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. Victoria Yudin Reply January 28, 2009 at 1:52 pm

    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. Jim Reply January 28, 2009 at 10:15 am

    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. Dick White Reply January 13, 2009 at 2:25 pm

    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

    • Victoria Yudin Reply January 14, 2009 at 5:21 am

      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. Menno Reply January 8, 2009 at 7:30 am

    will this also work with MS Visual Studio reports?

    Like

  8. Leo Reply January 7, 2009 at 8:28 am

    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 comment