Dynamics GP - View to Reconcile Inventory Subledger to GL

View for all Posted GL Transactions

http://victoriayudin.com/2011/04/27/sql-view-for-all-gl-transactions-in-dynamics-gp/

--Original View for all GL Trx
-------------------------------------------------------------------------------------------------
create view [dbo].[BI_Posted_GL_Trx]
as

select
YEAR1 Trx_Year,
TRXDATE Trx_Date,
JRNENTRY Journal_Entry,
ORTRXSRC Originating_TRX_Source,
REFRENCE Reference,
ORMSTRID Originating_Master_ID,
ORMSTRNM Originating_Master_Name,
ORDOCNUM Originating_Doc_Number,
DEBITAMT Debit_Amount,
CRDTAMNT Credit_Amount,
ACTNUMST Account_Number,
ACTDESCR Account_Description,
ACCATDSC Account_Category,
CURNCYID Currency_ID,
USWHPSTD User_Who_Posted

from
(select ACTINDX, OPENYEAR YEAR1, TRXDATE, JRNENTRY, ORTRXSRC,
        REFRENCE, ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT,
        CRDTAMNT, CURNCYID, USWHPSTD
 from GL20000
 where SOURCDOC not in ('BBF','P/L') and VOIDED = 0

 union all

 select ACTINDX, HSTYEAR YEAR1, TRXDATE, JRNENTRY, ORTRXSRC,
        REFRENCE, ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT,
        CRDTAMNT, CURNCYID, USWHPSTD
 from GL30000
 where SOURCDOC not in ('BBF','P/L') and VOIDED = 0) GL

inner join GL00105 GM
     on GL.ACTINDX = GM.ACTINDX
inner join GL00100 GA
     on GL.ACTINDX = GA.ACTINDX
inner join GL00102 C
     on GA.ACCATNUM= C.ACCATNUM


GO

--A summarized version of the view
----------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_Posted_GL_Trx_smry]
AS
SELECT     Trx_Year, Trx_Date, Journal_Entry, Originating_TRX_Source, Reference, Originating_Master_ID, Originating_Master_Name, Originating_Doc_Number, SUM(Debit_Amount) AS DebitAmt,
                      SUM(Credit_Amount) AS CreditAmt, Account_Number, Account_Description, Account_Category, Currency_ID, User_Who_Posted, CASE WHEN LEFT(originating_doc_number, 3)
                      = 'TRF' THEN 0 WHEN SUM(debit_amount) > 0 AND SUM(credit_amount) > 0 THEN SUM(debit_amount) ELSE SUM(Debit_Amount - Credit_Amount) END AS AbsAmt
FROM         dbo.BI_Posted_GL_Trx
GROUP BY Trx_Year, Trx_Date, Journal_Entry, Originating_TRX_Source, Reference, Originating_Master_ID, Originating_Master_Name, Originating_Doc_Number, Account_Number, Account_Description,
                      Account_Category, Currency_ID, User_Who_Posted

GO
------------------------------------------------------------------------------------------------

--View for all Inventory Transactions - iv30300 table, credit side
------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_InvTrxSmry]
AS
SELECT     dbo.IV30300.TRXSORCE, dbo.IV30300.DOCTYPE, dbo.IV30300.DOCNUMBR, dbo.IV30300.DOCDATE, dbo.IV30300.HSTMODUL, SUM(dbo.IV30300.EXTDCOST) AS Amt,
                      dbo.GL00105.ACTNUMST AS IvAct, dbo.GL00100.ACTDESCR AS IvActDesc, CASE WHEN TRXQTY >= 0 THEN 1 ELSE - 1 END AS PosNeg,
                      SUM(ROUND(dbo.IV30300.UNITCOST * dbo.IV30300.TRXQTY * CASE WHEN trxqty >= 0 THEN 1 ELSE - 1 END, 2)) AS AmtNoLC, CASE WHEN LEFT(docnumbr, 3)
                      = 'TRF' THEN 0 WHEN LEFT(docnumbr, 3) = 'RCT' THEN SUM(round(dbo.IV30300.UNITCOST * dbo.IV30300.TRXQTY, 2)) ELSE SUM(extdcost)
                      * CASE WHEN trxqty >= 0 THEN 1 ELSE - 1 END END AS AbsAmtNoLC
FROM         dbo.GL00100 INNER JOIN
                      dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX INNER JOIN
                      dbo.IV30300 ON dbo.GL00105.ACTINDX = dbo.IV30300.IVIVINDX
GROUP BY dbo.IV30300.TRXSORCE, dbo.IV30300.DOCTYPE, dbo.IV30300.DOCNUMBR, dbo.IV30300.DOCDATE, dbo.IV30300.HSTMODUL, dbo.GL00105.ACTNUMST, dbo.GL00100.ACTDESCR,
                      CASE WHEN TRXQTY >= 0 THEN 1 ELSE - 1 END

GO


--View for all Inventory Transactions - iv30300 table, debit side
------------------------------------------------------------------------------------------------

CREATE VIEW [dbo].[BI_InvTrxSmry_Offset]
AS
SELECT     dbo.IV30300.TRXSORCE, dbo.IV30300.DOCTYPE, dbo.IV30300.DOCNUMBR, dbo.IV30300.DOCDATE, dbo.IV30300.HSTMODUL, SUM(dbo.IV30300.EXTDCOST) AS Amt,
                      GL00105_1.ACTNUMST AS IvOffsetAct, GL00100_1.ACTDESCR AS IvOffsetActDesc, CASE WHEN TRXQTY >= 0 THEN 1 ELSE - 1 END AS PosNeg,
                      SUM(ROUND(dbo.IV30300.UNITCOST * dbo.IV30300.TRXQTY * CASE WHEN trxqty >= 0 THEN 1 ELSE - 1 END, 2)) AS AmtNoLC, CASE WHEN LEFT(docnumbr, 3)
                      = 'TRF' THEN 0 WHEN LEFT(docnumbr, 3) = 'RCT' THEN SUM(round(dbo.IV30300.UNITCOST * dbo.IV30300.TRXQTY, 2)) * - 1 ELSE SUM(extdcost)
                      * CASE WHEN trxqty >= 0 THEN 1 ELSE - 1 END * - 1 END AS AbsAmtNoLC
FROM         dbo.IV30300 INNER JOIN
                      dbo.GL00105 AS GL00105_1 ON dbo.IV30300.IVIVOFIX = GL00105_1.ACTINDX INNER JOIN
                      dbo.GL00100 AS GL00100_1 ON GL00105_1.ACTINDX = GL00100_1.ACTINDX
GROUP BY dbo.IV30300.TRXSORCE, dbo.IV30300.DOCTYPE, dbo.IV30300.DOCNUMBR, dbo.IV30300.DOCDATE, dbo.IV30300.HSTMODUL, GL00105_1.ACTNUMST, GL00100_1.ACTDESCR,
                      CASE WHEN TRXQTY >= 0 THEN 1 ELSE - 1 END

GO

---------------------------------------------------------------------------------------------------

--View for all journals that touch inventory accounts
---------------------------------------------------------------------------------------------------

CREATE VIEW [dbo].[BI_GL_Inventory_Journals]
AS
SELECT     Journal_Entry
FROM         dbo.BI_Posted_GL_Trx
GROUP BY Journal_Entry, Account_Category
HAVING      (Account_Category = 'inventory')

GO

---------------------------------------------------------------------------------------------------------
--View to pull all data together, and compare GL to SL to identify missing SL
---------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_GL_INV_Rec]
AS
SELECT     TOP (100) PERCENT dbo.BI_Posted_GL_Trx_smry.Journal_Entry, dbo.BI_Posted_GL_Trx_smry.Originating_TRX_Source, dbo.BI_Posted_GL_Trx_smry.Reference,
                      dbo.BI_Posted_GL_Trx_smry.Trx_Year, dbo.BI_Posted_GL_Trx_smry.Trx_Date, dbo.BI_Posted_GL_Trx_smry.Originating_Master_ID, dbo.BI_Posted_GL_Trx_smry.Originating_Master_Name,
                      dbo.BI_Posted_GL_Trx_smry.Currency_ID, dbo.BI_Posted_GL_Trx_smry.User_Who_Posted, dbo.BI_Posted_GL_Trx_smry.Account_Number, dbo.BI_Posted_GL_Trx_smry.Account_Description,
                      dbo.BI_Posted_GL_Trx_smry.Account_Category, dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number, dbo.BI_Posted_GL_Trx_smry.DebitAmt AS GLDebamt,
                      dbo.BI_Posted_GL_Trx_smry.CreditAmt AS GLCredAmt, dbo.BI_InvTrxSmry_Offset.DOCNUMBR AS SLDebitDoc, dbo.BI_InvTrxSmry_Offset.Amt AS SLDebitAmt,
                      dbo.BI_InvTrxSmry.DOCNUMBR AS SLCreditDoc, dbo.BI_InvTrxSmry.Amt AS SLCreditAm, CASE WHEN dbo.BI_InvTrxSmry.DOCNUMBR IS NULL
                      THEN BI_InvTrxSmry_Offset.DOCNUMBR ELSE dbo.BI_InvTrxSmry.docnumbr END AS SLDoc, CASE WHEN dbo.BI_InvTrxSmry.Amt IS NULL
                      THEN BI_InvTrxSmry_Offset.Amt ELSE dbo.BI_InvTrxSmry.Amt END AS SLAmt, CASE WHEN account_category = 'inventory' THEN (abs(ISNULL(dbo.BI_InvTrxSmry.Amt, 0)
                      - ISNULL(dbo.BI_Posted_GL_Trx_smry.debitAmt, 0))) - abs((ISNULL(BI_InvTrxSmry_Offset.Amt, 0) - ISNULL(dbo.BI_Posted_GL_Trx_smry.creditAmt, 0))) ELSE 0 END AS GLSLDiff
FROM         dbo.BI_GL_Inventory_Journals INNER JOIN
                      dbo.BI_Posted_GL_Trx_smry ON dbo.BI_GL_Inventory_Journals.Journal_Entry = dbo.BI_Posted_GL_Trx_smry.Journal_Entry LEFT OUTER JOIN
                      dbo.BI_InvTrxSmry ON dbo.BI_Posted_GL_Trx_smry.AbsAmt = dbo.BI_InvTrxSmry.AbsAmtNoLC AND dbo.BI_Posted_GL_Trx_smry.Account_Number = dbo.BI_InvTrxSmry.IvAct AND
                      dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number = dbo.BI_InvTrxSmry.DOCNUMBR LEFT OUTER JOIN
                      dbo.BI_InvTrxSmry_Offset ON dbo.BI_Posted_GL_Trx_smry.AbsAmt = dbo.BI_InvTrxSmry_Offset.AbsAmtNoLC AND
                      dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number = dbo.BI_InvTrxSmry_Offset.DOCNUMBR AND dbo.BI_Posted_GL_Trx_smry.Account_Number = dbo.BI_InvTrxSmry_Offset.IvOffsetAct
ORDER BY dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number, dbo.BI_Posted_GL_Trx_smry.Journal_Entry

GO
---------------------------------------------------------------------------------------------------------
--View to pull all data together, and compare GL to SL to identify missing GL
---------------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_GL_INV_Rec_NoGL]
AS
SELECT     TOP (100) PERCENT dbo.BI_InvTrxSmry.DOCNUMBR AS SLDoc, dbo.BI_InvTrxSmry.Amt AS SLAmt, dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number, dbo.BI_InvTrxSmry.TRXSORCE,
                      dbo.BI_InvTrxSmry.DOCTYPE, dbo.BI_InvTrxSmry.DOCDATE, YEAR(dbo.BI_InvTrxSmry.DOCDATE) as DocYr, dbo.BI_InvTrxSmry.HSTMODUL, dbo.BI_InvTrxSmry.IvAct, dbo.BI_InvTrxSmry.IvActDesc
FROM         dbo.BI_GL_Inventory_Journals INNER JOIN
                      dbo.BI_Posted_GL_Trx_smry ON dbo.BI_GL_Inventory_Journals.Journal_Entry = dbo.BI_Posted_GL_Trx_smry.Journal_Entry RIGHT OUTER JOIN
                      dbo.BI_InvTrxSmry ON dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number = dbo.BI_InvTrxSmry.DOCNUMBR
WHERE     (dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number IS NULL)
ORDER BY dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number, dbo.BI_Posted_GL_Trx_smry.Journal_Entry

GO