Hello,
Some time ago we had a vendor create a custom inventory report for us using SQL 2008, SQL Server Business Intelligence Development Studio, and SRSS. This report has never worked correctly. This has become a high priority item for us, so I could sure use some help. The SQL code is posted below:
USE
[UNCHM]
GO
/****** Object: StoredProcedure [dbo].[xxx_InventoryReport] Script Date: 12/02/2013 16:46:09 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
ALTER
procedure [dbo].[tmc_InventoryReport]
@ITEMNMBR
varchar(31) = '%'
as
select
rtrim(im.ITEMNMBR) as ITEMNMBR,
rtrim(im.ITEMDESC) as ITEMDESC,
rtrim(im.USCATVLS_2) as USCATVLS_2,
rtrim(im.ITMGEDSC) as ITMGEDSC,
rtrim(case when len(iq.LOCNCODE) > 0 then iq.LOCNCODE else 'All Sites' end) as LOCNCODE,
iq.QTYONHND,
iq.ATYALLOC,
(iq.QTYONHND - iq.ATYALLOC) as QTYAVAIL,
iq.QTYONORD,
rtrim(isnull(il.LOTNUMBR,'')) as LOTNUMBR,
(isnull(il.QTYRECVD,0) - isnull(il.QTYSOLD,0)) as LOTQTYONHND,
isnull(il.ATYALLOC,0) as LOTATYALLOC,
isnull(il.QTYTYPE,1) as QTYTYPE
from
IV00101 as im
left outer join IV00102 as iq on im.ITEMNMBR = iq.ITEMNMBR
left outer join IV00300 as il on im.ITEMNMBR = il.ITEMNMBR and iq.LOCNCODE = il.LOCNCODE
where
iq.QTYONHND <> 0 and
im.ITEMNMBR like @ITEMNMBR and
il.QTYTYPE <> 5
order by
ITEMNMBR,
LOCNCODE
If you run this query as is, there will be multiple rows of the same PID with the same lot number, but with different quantities. In some cases the quantity avialable will be double the actual quantity. My knowledge of T-SQL is pretty limited, so any help would be appreciated.
*This post is locked for comments