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.
For updates visit http://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

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.


Filed under: Dynamics GP, GP Reports code, GP SQL scripts, Inventory SQL code Tagged: GP Reports code, GP SQL view, Inventory, SQL code