Dynamics GP - SQL View - Inventory Transaction Adjustment Line Detail with Reason codes, accounts and username

/****** Object:  View [dbo].[BI_INV_ItemAdjDtl]    Script Date: 17/08/2017 10:11:09 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[BI_INV_ItemAdjDtl]
AS
SELECT        dbo.IV30200.TRXSORCE, dbo.IV30200.IVDOCTYP, dbo.IV30200.DOCNUMBR, dbo.IV30200.DOCDATE, dbo.IV30200.BACHNUMB, dbo.IV30200.GLPOSTDT, dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC,
                         dbo.IV30300.UOFM, dbo.IV30300.TRXQTY, dbo.IV30300.UNITCOST, dbo.IV30300.EXTDCOST, dbo.IV30300.TRXLOCTN, dbo.IV30300.TRNSTLOC, dbo.IV30300.QTYBSUOM, dbo.IV30300.Reason_Code,
                         dbo.IV40300.Reason_Code_Description, dbo.GL00105.ACTNUMST AS IVActNum, dbo.GL00100.ACTDESCR AS IVActDesc, GL00105_1.ACTNUMST AS OffsetActNum, GL00100_1.ACTDESCR AS OffsetActDesc,
                         GLTrxSmry.UserNm
FROM            dbo.IV30200 INNER JOIN
                         dbo.IV30300 ON dbo.IV30200.IVDOCTYP = dbo.IV30300.DOCTYPE AND dbo.IV30200.DOCNUMBR = dbo.IV30300.DOCNUMBR INNER JOIN
                         dbo.GL00100 ON dbo.IV30300.IVIVINDX = dbo.GL00100.ACTINDX INNER JOIN
                         dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX INNER JOIN
                         dbo.GL00100 AS GL00100_1 ON dbo.IV30300.IVIVOFIX = GL00100_1.ACTINDX INNER JOIN
                         dbo.GL00105 AS GL00105_1 ON GL00100_1.ACTINDX = GL00105_1.ACTINDX INNER JOIN
                         dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
                             (SELECT        ORTRXSRC AS TrxSource, DTAControlNum AS SourceDocNum, USWHPSTD AS UserNm
                               FROM            dbo.GL10000
                               UNION
                               SELECT        ORGNTSRC, ORCTRNUM, USWHPSTD
                               FROM            dbo.GL20000
                               UNION
                               SELECT        ORGNTSRC, ORCTRNUM, USWHPSTD
                               FROM            dbo.GL30000) AS GLTrxSmry ON dbo.IV30300.TRXSORCE = GLTrxSmry.TrxSource AND dbo.IV30300.DOCNUMBR = GLTrxSmry.SourceDocNum LEFT OUTER JOIN
                         dbo.IV40300 ON dbo.IV30300.Reason_Code = dbo.IV40300.Reason_Code


GO