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, or to see all the prices for a particular price level. Below is a view that can help with this.

~~~~~

CREATE VIEW view_Inventory_Price_Levels
AS

/*******************************************************************
view_Inventory_Price_Levels
Created on Aug 9, 2010 by Victoria Yudin - Flexible Solutions, Inc.
This only shows prices for Currency Amount and % of List Price methods
For updates visit https://victoriayudin.com/gp-reports/
*******************************************************************/

SELECT IV.ITEMNMBR Item_Number,
       IM.ITEMDESC Item_Description,
       IM.ITMCLSCD Item_Class,
       IV.PRCLEVEL Price_Level,
       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 Price_Method,
       IV.CURNCYID Currency_ID,
       IV.UOFM U_of_M,
       CASE IM.PRICMTHD
	  WHEN 1 THEN IV.UOMPRICE
	  WHEN 2 THEN IV.UOMPRICE * IC.LISTPRCE / 100
	  ELSE 0
	  END Price,
       CASE IM.PRICMTHD
	  WHEN 1 THEN 0
	  ELSE IV.UOMPRICE
	  END Percent_of_List,
       IV.FROMQTY From_Qty,
       IV.TOQTY To_Qty,
       IV.QTYBSUOM Qty_In_Base_UofM

FROM   IV00108 IV

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

/** 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_Inventory_Price_Levels TO DYNGRP

~~~~~

If 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 blog.

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 for Inventory Price Levels in Dynamics GP”

  1. I have added this sql view but the only price I am pulling in are items I have set up as list price. All items I have set up as % Markup – Current Cost are showing up as 0…any ideas what is wrong? thank you

    Like

    • Andrea,

      This code only works for Currency Amount and Percent of List Price pricing methods, all others will return a 0. I thought I had a note in there about this, sorry. I just added it to avoid confusion in the future. I will add a ‘more robust’ version of this view to my blog request list.

      -Victoria

      Like

  2. Thanks Victoria – once again you save the day! 🙂

    Like

  3. Will this show if an item doesn’t have a Price Level assigned?

    Like

    • No, this will only show items with Price Levels assigned.

      -Victoria

      Like

      • Is there a way to tweak it to show items without assigned Price Levels?
        Thanks.

        Like

        • Try this:

          SELECT IM.ITEMNMBR Item_Number,
                 IM.ITEMDESC Item_Description,
                 IM.ITMCLSCD Item_Class,
                 IV.PRCLEVEL Price_Level,
                 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 Price_Method,
                 IV.CURNCYID Currency_ID,
                 IV.UOFM U_of_M,
                 CASE IM.PRICMTHD
                WHEN 1 THEN IV.UOMPRICE
                WHEN 2 THEN IV.UOMPRICE * IC.LISTPRCE / 100
                ELSE 0
                END Price,
                 CASE IM.PRICMTHD
                WHEN 1 THEN 0
                ELSE IV.UOMPRICE
                END Percent_of_List,
                 IV.FROMQTY From_Qty,
                 IV.TOQTY To_Qty,
                 IV.QTYBSUOM Qty_In_Base_UofM
          
          FROM   IV00101 IM
          
          LEFT OUTER JOIN IV00108 IV
                 ON IM.ITEMNMBR = IV.ITEMNMBR
          
          LEFT OUTER JOIN IV00105 IC
                 ON IC.ITEMNMBR = IV.ITEMNMBR
                 AND IV.CURNCYID = IC.CURNCYID
          

          Like

  4. Thanks for the View came in handy on a QuickBooks conversion!

    Like

  5. Hello Victoria,

    I think the answer is no but regarding the Inventory Price List, there is not a column in the IV00108 table with the modified date. Is there a table that holds this info or is it just current?

    Like

  6. Great stuff Victoria!

    Like

  7. I believe you need “TO DYNGRP” at the end of the grant statement.

    Like

Trackbacks/Pingbacks

  1. Coding specific dates in SQL Server | Victoria Yudin - January 15, 2012

    […] SQL view for Inventory Price Levels in Dynamics GP (victoriayudin.com) Share this:EmailPrintFacebookLinkedInTwitterLike this:LikeBe the first to like this post. GP Reports code, SQL coding GP Reports code, SQL code, SQL Server […]

    Like

  2. DynamicAccounting.net - August 10, 2010

    SQL view for Inventory Price Levels in Dynamics GP…

    Victoria Yudin has a new post up with a SQL view for Inventory Price Levels in Dynamics GP . I will add…

    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: