Dynamics GP inventory by location with dates and sites

January 29, 2011 by Victoria Yudin 13 Comments

In response to a request from a reader, I have created a new version of the Inventory with Dates script to show the same information by Site ID. I also added a Quantity on Order column.

You can see more Dynamics GP Inventory scripts here. Or visit my GP Reports page for links to additional GP scripts and other reporting information and tips.

~~~~~

create view view_Inventory_by_Site_with_Dates_and_Sites
as
/********************************************************************
view_Inventory_by_Site_with_Dates
Created on Jan 29, 2011 by Victoria Yudin - Flexible Solutions, Inc.
For updates see https://victoriayudin.com/gp-reports/
All inventory items with quantity on hand and last sale
     and receipt dates, by site ID
Functional amounts only
Tables used:
 i - IV00101 - Item Master
 s - IV30300 - Transaction Amounts History with DOCTYPE = 6 (sales)
 q - IV00102 - Item Quantity Master
 u - IV40201 - U of M Schedule Header
Updated Oct 15, 2021 to include last customer (will show first
    customer if there are multiple on the date of last sale)
********************************************************************/

select 
i.ITEMNMBR Item_Number,
q.LOCNCODE Site_ID,
i.ITEMDESC Item_Description,
q.QTYONHND Quantity_on_Hand,
q.QTYONORD Quantity_on_Order,
u.BASEUOFM U_of_M,
case i.ITEMTYPE
   when 1 then 'Sales Inventory'
   when 2 then 'Discontinued'
   when 3 then 'Kit'
   when 4 then 'Misc Charges'
   when 5 then 'Services'
   when 6 then 'Flat Fee'
   end Item_Type,
i.CURRCOST Current_Cost,
i.ITMCLSCD Item_Class,
coalesce(s.LastSale,'1/1/1900') Last_Sale_Date,
coalesce(s.CustomerID,'') Last_Customer,
coalesce(q.LSRCPTDT,'1/1/1900') Last_Receipt_Date,
coalesce(q.LSORDVND,'') Last_Vendor

from dbo.IV00101 i

inner join dbo.IV00102 q
   on i.ITEMNMBR = q.ITEMNMBR
   and q.RCRDTYPE = 2

left outer join
(select d.*, min(c.CUSTNMBR) CustomerID
 from
 (select ITEMNMBR, MAX(DOCDATE) LastSale, TRXLOCTN
  from dbo.IV30300
  where DOCTYPE = 6
  group by ITEMNMBR, TRXLOCTN) d
 inner join dbo.IV30300 c
   on d.ITEMNMBR = c.ITEMNMBR 
   and d.TRXLOCTN = c.TRXLOCTN 
   and d.LastSale = c.DOCDATE
 group by d.ITEMNMBR, d.LastSale, d.TRXLOCTN) s
   on i.ITEMNMBR = s.ITEMNMBR
   and q.LOCNCODE = s.TRXLOCTN

inner join dbo.IV40201 u
   on u.UOMSCHDL = i.UOMSCHDL

where q.QTYONHND <> 0


/** 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_by_Site_with_Dates_and_Sites 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

Dynamics GP, GP Reports code, GP SQL scripts, Inventory SQL code

Dynamics GP, GP Reports code, Inventory, SQL code

Change GP fiscal period names using SQL Server

Results of SQL Server poll

13 Responses to “Dynamics GP inventory by location with dates and sites”

andy browning Reply October 14, 2021 at 3:41 pm

hi victoria – trying to figure out how to include the last customer to include with the last sales date in this query. I’ve tried several times, but get multiple records per customer. I’m trying to pull inventory items that have not been sold in the last 180 days. Thanks

LikeLike
- Victoria Yudin Reply October 15, 2021 at 7:01 am
  
  Hi Andy,
  
  It’s not you – that is a bit tricky to do. You basically have to take an additional loop through the data, so once you figure out what the last sale date was, go back and get the customer for that item, location, and date. You also have to account for the possibility that there may be more than one customer that was sold the same item on that same date.
  
  I updated my code in this blog to include the last customer ID for you. If there are multiple customers on the same date, this code will only show the first one (alphabetically by Customer ID).
  
  Hope that helps,
  -Victoria
  
  LikeLike
Abdurahiman Reply February 29, 2012 at 10:33 am

Dear Victoria

Thanks a lot for your scripts and they are fantastic and I use many of them saving uncountable hours.

But now I tried the Inventory Views “Inventory with Dates” and “Inventory with Dates by Site”. Here in the View “Inventory with Dates” the Last Sales date is coming good, but in the view “Inventory with Dates by Site” many of the Last Sales date is coming 1/1/1900. I checked and found the same item is coming with the correct Last Sale Date in the View Inventory with Dates.

Thanks in advance

Abdul Rahman

LikeLike
- Victoria Yudin Reply March 12, 2012 at 8:59 am
  
  Hi Abdul,
  
  Remember that this view will return a row for each item/site combination where the on hand quantity is not 0. 1/1/1900 in the Last Sale date means that item has a quantity other than 0 for the site, but has not been sold from this site.
  
  -Victoria
  
  LikeLike
Ernie G. Blanche Reply June 28, 2011 at 4:10 am

Hi Victoria,

Do you have a SQL view that will show inventory movement, both outgoing and incoming, showing the cost and sales amount per item?

Thanks in advance.

Ernie

LikeLike
- Victoria Yudin Reply June 28, 2011 at 5:29 am
  
  Hi Ernie,
  
  Sorry, I don’t have anything like that. I’ve looked around at other blogs and sites, as well, but have not found any scripts that do what you’re asking.
  
  -Victoria
  
  LikeLike
Ian Greenhill Reply March 17, 2011 at 11:13 am

Thanks, This was a great time saver in identifying slows moving and ‘stuck’ stock lines.

LikeLike
- Ernie G. Blanche Reply June 28, 2011 at 9:46 pm
  
  OK. Thanks, anyway. I’ll try to create one and share the script here when I’m done.
  
  Ernie
  
  LikeLike
Rick Reply January 31, 2011 at 8:23 am

Thank you another great Report!!!

LikeLike