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.

I haven’t had much time to blog lately as we have added functionality to our GP Reports Viewer product that allows seamless replacement of SOP reports in Dynamics GP and the amount of interest has been overwhelming. 

However, I just needed to create a new SmartList to show current inventory quantities on hand with their costs and I wanted to share the view I used. Please bear in mind, this was only tested with our data and only FIFO Perpetual inventory items, so this may not work for all situations. And the average unit cost is simply the total cost divided by the quantity on hand.

For other GP SQL views, please take a look at my GP Reports page.

~~~~~

CREATE VIEW view_On_Hand_Inventory
AS

/********************************************************************
view_On_Hand_Inventory
Created on June 18, 2009 by Victoria Yudin - Flexible Solutions, Inc.
For updates see http://victoriayudin.com/gp-reports/
Inventory on hand quantities by site
Only tested with FIFO Perpetual inventory valuation method
I - IV00101 - Item Master
T - IV10200 - Purchase Receipts
Updated on June 24, 2009 to add Item Class
********************************************************************/

SELECT T.ITEMNMBR Item,
       I.ITEMDESC Description,
I.ITMCLSCD Item_Class,
       T.TRXLOCTN Site_ID,
       sum(T.QTYRECVD-T.QTYSOLD) Quantity,
       sum(T.UNITCOST*(T.QTYRECVD-T.QTYSOLD)) Total_Cost,
       sum(T.UNITCOST*(T.QTYRECVD-T.QTYSOLD))
          /sum(T.QTYRECVD-T.QTYSOLD) Avg_Unit_Cost

FROM IV10200 T
INNER JOIN
     IV00101 I
     ON I.ITEMNMBR = T.ITEMNMBR

WHERE T.QTYRECVD <> T.QTYSOLD
GROUP BY T.ITEMNMBR, T.TRXLOCTN, I.ITEMDESC, I.ITMCLSCD

/** 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_On_Hand_Inventory 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.

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