Dynamics GP - Item Lot Quantity SQL View

IV00300 - Item Lot quantities
IV00301 - Lot Attributes


/****** Object:  View [dbo].[BI_ItemLotQty]    Script Date: 09/10/2019 12:01:05 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[BI_ItemLotQty]
AS
SELECT     dbo.IV00300.ITEMNMBR, dbo.IV00300.LOCNCODE, SUM(dbo.IV00300.QTYRECVD - dbo.IV00300.QTYSOLD) AS LotQtyOnHnd, SUM(dbo.IV00300.ATYALLOC) AS LotQtyAllocated, dbo.IV00101.ITMTRKOP,
                  SUM(dbo.IV00300.QTYRECVD - dbo.IV00300.QTYSOLD - dbo.IV00300.ATYALLOC) AS LotQtyAvlbl, dbo.IV00101.USCATVLS_2 AS Category, dbo.IV00101.CURRCOST, SUM(dbo.IV00300.QTYRECVD - dbo.IV00300.QTYSOLD * dbo.IV00101.CURRCOST) AS CostOnHand,
                  dbo.IV00101.ITEMDESC, CASE WHEN iv00101.itmclscd LIKE '%fg%' THEN 'Finished Goods' ELSE 'Other' END AS FGOTH, dbo.IV00301.LOTNUMBR, dbo.IV00301.LOTATRB1, dbo.IV00301.LOTATRB2, dbo.IV00301.LOTATRB3, dbo.IV00301.LOTATRB4 AS BestBefore,
                  dbo.IV00301.LOTATRB5, dbo.IV00300.DATERECD AS Received, dbo.IV00300.MFGDATE AS Manufactured, dbo.IV00300.EXPNDATE AS Expiry
FROM        dbo.IV00101 INNER JOIN
                  dbo.IV00300 ON dbo.IV00101.ITEMNMBR = dbo.IV00300.ITEMNMBR LEFT OUTER JOIN
                  dbo.IV00301 ON dbo.IV00300.LOTNUMBR = dbo.IV00301.LOTNUMBR AND dbo.IV00300.ITEMNMBR = dbo.IV00301.ITEMNMBR
GROUP BY dbo.IV00300.ITEMNMBR, dbo.IV00300.LOCNCODE, dbo.IV00101.ITMTRKOP, dbo.IV00101.USCATVLS_2, dbo.IV00101.CURRCOST, dbo.IV00101.ITEMDESC, dbo.IV00301.LOTNUMBR, dbo.IV00301.LOTATRB1, dbo.IV00301.LOTATRB2, dbo.IV00301.LOTATRB3,
                  dbo.IV00301.LOTATRB4, dbo.IV00301.LOTATRB5, dbo.IV00300.DATERECD, dbo.IV00300.MFGDATE, dbo.IV00300.EXPNDATE, CASE WHEN iv00101.itmclscd LIKE '%fg%' THEN 'Finished Goods' ELSE 'Other' END
HAVING     (dbo.IV00101.ITMTRKOP = 3) AND (dbo.IV00300.LOCNCODE > '') AND (SUM(dbo.IV00300.QTYRECVD - dbo.IV00300.QTYSOLD) <> 0)

GO