Dynamics GP - View to reconcile Bank Transactions to GL

--------------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_BankTrx_All]
AS

SELECT     a.CMRECNUM, a.sRecNum, a.RCRDSTTS, a.CHEKBKID, a.CMTrxNum, a.SRCDOCNUM, a.SRCDOCTYP, a.TRXDATE, a.GLPOSTDT, a.TRXAMNT, a.ORIGAMT, a.Checkbook_Amount, a.CURNCYID, a.CMLinkID, a.paidtorcvdfrom, a.DSCRIPTN, a.Recond, a.reconnumb,
                  a.VOIDED, a.VOIDDATE, dbo.GL00105.ACTNUMST, dbo.CM00100.DSCRIPTN AS ChkBkDesc
FROM        dbo.GL00105 INNER JOIN
                  dbo.CM00100 ON dbo.GL00105.ACTINDX = dbo.CM00100.ACTINDX RIGHT OUTER JOIN
                      (SELECT     CMRECNUM, sRecNum, RCRDSTTS, CHEKBKID, CMTrxNum, SRCDOCNUM, SRCDOCTYP, TRXDATE, GLPOSTDT, TRXAMNT, ORIGAMT, Checkbook_Amount, CURNCYID, CMLinkID, paidtorcvdfrom, DSCRIPTN, Recond, CAST(RECONUM AS varchar(50))
                                         AS reconnumb, VOIDED, VOIDDATE
                       FROM        dbo.CM20200
                       UNION
                       SELECT     CMRECNUM, sRecNum, RCRDSTTS, CHEKBKID, RCPTNMBR, SRCDOCNUM, SRCDOCTYP, receiptdate, GLPOSTDT, RCPTAMT, ORIGAMT, Checkbook_Amount, CURNCYID, CMLinkID, RcvdFrom, DSCRIPTN, DEPOSITED,
                                         CAST(depositnumber AS varchar(50)) AS depnumb, VOIDED, VOIDDATE
                       FROM        dbo.CM20300) AS a ON dbo.CM00100.CHEKBKID = a.CHEKBKID

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

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
----------------------------------------------------------------------------
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

--------------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_Rec_GL_vs_Bank]
AS
SELECT     dbo.BI_Posted_GL_Trx_smry.Trx_Year, dbo.BI_Posted_GL_Trx_smry.Trx_Date, 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.Originating_Master_ID, dbo.BI_Posted_GL_Trx_smry.Originating_Master_Name, dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number, dbo.BI_Posted_GL_Trx_smry.DebitAmt, dbo.BI_Posted_GL_Trx_smry.CreditAmt,
                  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.Currency_ID, dbo.BI_Posted_GL_Trx_smry.User_Who_Posted,
                  dbo.BI_Posted_GL_Trx_smry.AbsAmt, dbo.BI_BankTrx_All.*
FROM        dbo.BI_Posted_GL_Trx_smry INNER JOIN
                      (SELECT DISTINCT ACTNUMST
                       FROM        dbo.BI_BankTrx_All AS BI_BankTrx_All_1) AS bankacts ON dbo.BI_Posted_GL_Trx_smry.Account_Number = bankacts.ACTNUMST LEFT OUTER JOIN
                  dbo.BI_BankTrx_All ON dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number = dbo.BI_BankTrx_All.CMTrxNum

GO

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

CREATE VIEW [dbo].[BI_Rec_Bank_vs_GL]
AS
SELECT     dbo.BI_Posted_GL_Trx_smry.Trx_Year, dbo.BI_Posted_GL_Trx_smry.Trx_Date, 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.Originating_Master_ID, dbo.BI_Posted_GL_Trx_smry.Originating_Master_Name, dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number, dbo.BI_Posted_GL_Trx_smry.DebitAmt, dbo.BI_Posted_GL_Trx_smry.CreditAmt,
                  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.Currency_ID, dbo.BI_Posted_GL_Trx_smry.User_Who_Posted,
                  dbo.BI_Posted_GL_Trx_smry.AbsAmt, dbo.BI_BankTrx_All.CMRECNUM, dbo.BI_BankTrx_All.sRecNum, dbo.BI_BankTrx_All.RCRDSTTS, dbo.BI_BankTrx_All.CHEKBKID, dbo.BI_BankTrx_All.CMTrxNum, dbo.BI_BankTrx_All.SRCDOCNUM,
                  dbo.BI_BankTrx_All.SRCDOCTYP, dbo.BI_BankTrx_All.TRXDATE, dbo.BI_BankTrx_All.GLPOSTDT, dbo.BI_BankTrx_All.TRXAMNT, dbo.BI_BankTrx_All.ORIGAMT, dbo.BI_BankTrx_All.Checkbook_Amount, dbo.BI_BankTrx_All.CURNCYID, dbo.BI_BankTrx_All.CMLinkID,
                  dbo.BI_BankTrx_All.paidtorcvdfrom, dbo.BI_BankTrx_All.DSCRIPTN, dbo.BI_BankTrx_All.Recond, dbo.BI_BankTrx_All.reconnumb, dbo.BI_BankTrx_All.VOIDED, dbo.BI_BankTrx_All.VOIDDATE, dbo.BI_BankTrx_All.ACTNUMST, dbo.BI_BankTrx_All.ChkBkDesc
FROM        dbo.BI_Posted_GL_Trx_smry INNER JOIN
                      (SELECT DISTINCT ACTNUMST
                       FROM        dbo.BI_BankTrx_All AS BI_BankTrx_All_1) AS bankacts ON dbo.BI_Posted_GL_Trx_smry.Account_Number = bankacts.ACTNUMST RIGHT OUTER JOIN
                  dbo.BI_BankTrx_All ON dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number = dbo.BI_BankTrx_All.CMTrxNum

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