While there is a Lot Inquiry window (Navigation: Inquiry >> Inventory >> Lots) the flexibility and information available in this window pales in comparison to what is possible using the following view coupled with SmartList Builder.
 |
| Lot Number Inquiry |
CREATE View [dbo].[_Lots]
as
select LOTS.[ITEMNMBR] AS
Item_Number,
LOTS.[LOCNCODE]
AS Location_Code,
LOTS.[DATERECD]
AS Date_Received,
LOTS.[LOTNUMBR]
AS Lot_Number,
LOTS.[QTYRECVD]
AS QTY_Received,
LOTS.[QTYSOLD]
AS QTY_Sold,
LOTS.[ATYALLOC]
AS QTY_Allocated,
cast(LOTS.UNITCOST as money) Unit_Cost,
LOTS.[VNDRNMBR]
AS Vendor_Number,
isnull(VEND.VENDNAME,'') as Vendor_Name,
case when LOTS.LTNUMSLD = 1 then 'Yes' ELSE 'No' end AS 'Lot Number Sold',
case when LOTS.QTYTYPE = 1 then 'On Hand'
when lots.QTYTYPE = 2 then 'Returned'
when lots.QTYTYPE = 3 then 'In Use'
when lots.QTYTYPE = 4 then 'In Service'
when lots.QTYTYPE = 5 then 'Damaged'
else 'ERROR'
end
AS QTY_Type,
LOTS.[BIN]
AS Bin,
LOTS.[MFGDATE]
AS Manufacture_Date,
LOTS.[EXPNDATE]
AS Expiration_Date,
ITEMS.[ITEMDESC] AS Item_Description,
ITEMS.[ITMCLSCD] AS Item_Class_Code,
ITEMS.[LOTTYPE] AS Lot_Type,
(LOTS.[QTYRECVD]-LOTS.[QTYSOLD]) AS QTY_ON_HAND
from IV00300 as LOTS
inner join IV00101 as items
on LOTS.ITEMNMBR
= ITEMS.ITEMNMBR
left join PM00200 as VEND on LOTS.VNDRNMBR = VEND.VENDORID
GO
Grant Select on _Lots to DYNGRP
