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.
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
LikeLike
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
LikeLike
Thanks Victoria – once again you save the day! 🙂
LikeLike
Glad to help Linda, hope you are well! 🙂
-Victoria
LikeLike
Will this show if an item doesn’t have a Price Level assigned?
LikeLike
No, this will only show items with Price Levels assigned.
-Victoria
LikeLike
Is there a way to tweak it to show items without assigned Price Levels?
Thanks.
LikeLike
Try this:
LikeLike
Thanks for the View came in handy on a QuickBooks conversion!
LikeLike
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?
LikeLike
Hi Andrew,
You are correct, GP does not track any change history for price lists out-of-the-box.
-Victoria
LikeLike
Great stuff Victoria!
LikeLike
I believe you need “TO DYNGRP” at the end of the grant statement.
LikeLike
RS,
Thanks for catching that. My copying and pasting must have gone awry.
-Victoria
LikeLike