Posted by: IS | November 21, 2017

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

 

 

 

 

 


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Categories

%d bloggers like this: