Dynamics GP - Stock Count Variance View

/****** Object:  View [dbo].[BI_INV_StockCountVariance]    Script Date: 17/08/2017 11:40:30 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*This view can be used to review and print completed stock counts before they have been posted.
-------------------------------------------------------------------*/
CREATE VIEW [dbo].[BI_INV_StockCountVariance]
AS
SELECT        dbo.IV10300.STCKCNTID, dbo.IV10300.STCKCNTDSCRPTN, dbo.IV10300.STCKCNTSTTS, dbo.IV10300.DOCDATE, dbo.IV00101.ITMCLSCD AS ItemClassCode, dbo.IV40400.ITMCLSDC AS ItemClassDesc,
                         dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.IV00101.CURRCOST, dbo.IV10301.LOCNCODE, dbo.IV10301.BINNMBR, dbo.IV10301.COUNTDATE, dbo.IV10301.DECPLQTY, dbo.IV10301.ITMTRKOP,
                         dbo.IV10301.IVIVINDX, dbo.IV10301.IVVARIDX, CASE WHEN VERIFIED = 1 THEN 'Yes' ELSE 'No' END AS Verified, dbo.IV40201.BASEUOFM, dbo.GL00100.ACTDESCR AS IVActDesc,
                         dbo.GL00105.ACTNUMST AS IVActNum, GL00100_1.ACTDESCR AS VarActDesc, GL00105_1.ACTNUMST AS VarActNum, dbo.IV10301.CAPTUREDQTY AS QtyOnHand,
                         dbo.IV10301.CAPTUREDQTY * dbo.IV00101.CURRCOST AS AmtOnHand, dbo.IV10301.COUNTEDQTY AS QtyCounted, dbo.IV10301.COUNTEDQTY * dbo.IV00101.CURRCOST AS AmtCounted,
                         dbo.IV10301.COUNTEDQTY - dbo.IV10301.CAPTUREDQTY AS VarQty, (dbo.IV10301.COUNTEDQTY - dbo.IV10301.CAPTUREDQTY) * dbo.IV00101.CURRCOST AS VarAmt
FROM            dbo.IV10300 INNER JOIN
                         dbo.IV10301 ON dbo.IV10300.STCKCNTID = dbo.IV10301.STCKCNTID LEFT OUTER JOIN
                         dbo.IV40400 INNER JOIN
                         dbo.IV40201 INNER JOIN
                         dbo.IV00101 ON dbo.IV40201.UOMSCHDL = dbo.IV00101.UOMSCHDL ON dbo.IV40400.ITMCLSCD = dbo.IV00101.ITMCLSCD ON dbo.IV10301.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
                         dbo.GL00105 AS GL00105_1 INNER JOIN
                         dbo.GL00100 AS GL00100_1 ON GL00105_1.ACTINDX = GL00100_1.ACTINDX ON dbo.IV10301.IVVARIDX = GL00100_1.ACTINDX LEFT OUTER JOIN
                         dbo.GL00105 INNER JOIN
                         dbo.GL00100 ON dbo.GL00105.ACTINDX = dbo.GL00100.ACTINDX ON dbo.IV10301.IVIVINDX = dbo.GL00100.ACTINDX

GO