August 9, 2010
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 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.
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?
Hi Andrew,
You are correct, GP does not track any change history for price lists out-of-the-box.
-Victoria
Great stuff Victoria!
I believe you need “TO DYNGRP” at the end of the grant statement.
RS,
Thanks for catching that. My copying and pasting must have gone awry.
-Victoria