Lot Availability Query

Recently I have been working with a client who has implemented Inventory Bill of Materials to Assemble materials having lot tracked components. It is important to fulfill orders with a single lot. So, quickly viewing component lot availability was a must.

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