Search all GP Blogs, Groups and Forums New!
GPWindow is Everything Dynamics GP. Try the Custom GP Search engine which searches high quality GP Blogs, and the Microsoft Dynamics GP Community Forums.
SQL Views – Inventory In All Locations
The following view will show you your inventory quantities across all of your locations.
This view is also a modification of the spvInventory view from Salespad (www.salespad.net).
You can name the view whatever you want by change the [tspvInventory] below. Also the USE [TSP] tells the script to only execute on my database named TSP. You will need to modify the [TSP] to be whatever your database name is.
/****** Object: View…
SQL Views – Inventory Mulitbin
The following view lists all your inventory bin quantities for GP installations that use Mulit-bin.
This view is also a modification of the spvInventoryMultiBin view from Salespad (www.salespad.net).
You can name the view whatever you want by change the [tspvInventoryMultiBin] below. Also the USE [TSP] tells the script to only…
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…
SQL Script to get Inventory Value by Site
The following will return the value of inventory on hand in each site. Good for verifying modified reports are accurate.
SELECT a.ITEMNMBR, b.ITEMDESC, a.LOCNCODE, a.QTYONHND, a.ATYALLOC, b.CURRCOST, CASE WHEN b.VCTNMTHD IN…
SQL - Retrieving the most recent receipt info for an item
It's been quite a while since I have posted a SQL script, and it's funny, because this is what I had in mind when I started out my blog. Shaun Childers posted a question
on the Microsoft Dynamics GP public newsgroup
, as follows:
"We are trying to create a script that will pull together our most recent
purchasing information only. The results should give all inventory items
with a qty on hand > 0, the current cost (we are average perpetual), the most
recent receipt number, the unit cost for that receipted item, the receipt date,
and the vendor name. I have tried to put this together, but have been
At first, this query may not seem to complex, but when you start to analyze the information being requested, it becomes apparent that using a standard set based query is not going to be as…
SQL View – Inventory Serial/Lot Numbers
The following view lists all your inventory item’s serial numbers, warehouse locations, bin locations, unit costs, and allocations.
This view is also a modification of the spvInventorySerialNum view from Salespad (www.salespad.net).
You can name the view whatever you want by change the [tspvInventorySerialNum] below. Also the USE [TSP] tells the script to only execute on my database named TSP. You will need to modify the [TSP] to be whatever your database name is.
/****** Object: View…
Weekly Dynamic: Inventory Value via SQL
We're going to go a little technical stuff this week. Buckle up.
Sometimes you need the on hand value of an inventory item for a Crystal Report or some other external use. Since GP maintains the cost layers regardless of your inventory valuation method, this can be a tough number to get. It's not one number sitting in a table.
First some background. When you buy inventory, GP stores the cost of that item. When your sell inventory, it works it's way back through those costs based on your inventory method. If I buy one item for $1, then $2, then $3 my inventory value is $6. If I sell an item under the FIFO method, $1 cost comes out and my new inventory…