Inventory Quantities on hand by Lot Number by site – View for Smartlist

A customer needed a new Smartlist showing their inventory quantities on hand analysed by Lot Number and site in order to complete monthly stock takes. This is the view I came up with, hope its of use to someone else. The total quantity on hand should reconcile to the out of the box Item Quantities Smartlist in GP.

___________________________________________________________________________________________

create view [dbo].[IS_InvOnHand_LotNo]
as

/********************************************************************
view: IS_InvOnHand_LotNo
Created on Nov 21, 2017 by Ian Stewart
Inventory quantities on hand analysed by Lot Number and Site. Showing the Quantity on hand,
the total quantity in, the total quantity out – analysed by Sales and Returns.

Tables used:
A – IV00300 – Item Lot Number Master
B – SOP10201 – Sales Serial/Lot work and history
C – IV00101 – Item Master

The results of this view should reconcile to the total On Hand quantity from the Item Quantities
Smartlist delivered out of the box with GP.

********************************************************************/

SELECT
A.ITEMNMBR AS ITEM,
C.ITEMDESC AS DESCRIPTION,
A.LOCNCODE AS SITE,
A.LOTNUMBR,
A.EXPNDATE AS EXPIRY_DATE,
(A.QTYRECVD-(CASE WHEN A.QTYTYPE = 1 THEN A.QTYSOLD ELSE (A.QTYRECVD) END)) AS QTY_ON_HAND,
A.QTYRECVD AS QTY_IN,
CASE WHEN A.QTYTYPE = 1 THEN A.QTYSOLD ELSE (A.QTYRECVD) END AS QTY_OUT,
A.UNITCOST,
A.QTYTYPE AS QTY_TYPE,
A.QTYRECVD AS QTY_RECEIVED,
A.QTYSOLD AS QTY_SOLD,
CASE WHEN A.QTYTYPE = 2 THEN A.QTYRECVD ELSE (0) END AS QTY_RETURNED,
C.ITMSHNAM AS SHORTNAME,
C.ITMGEDSC AS GENERIC_DESC,
C.ITMCLSCD AS CLASS_ID

FROM IV00300  A
left JOIN SOP10201 B on B.ITEMNMBR = A.ITEMNMBR and B.SERLTNUM = A.LOTNUMBR
left JOIN IV00101 C ON C.ITEMNMBR = A.ITEMNMBR

group by A.ITEMNMBR, A.QTYRECVD,
A.QTYSOLD, A.LOTNUMBR, A.LOCNCODE,
A.QTYTYPE, A.UNITCOST, C.ITEMDESC,
C.ITMSHNAM, C.ITMCLSCD, C.ITMGEDSC,
A.EXPNDATE

GO