Dynamics NAV - Bank Rec Notes

• P379-Bank Acc. Reconciliation
• T273-Bank Acc. Reconciliation
• C374-Bank Rec apply
• C375-Bank Acc. entry Set Recon-No (Bank Unapply)
• C371 - Bank Acc. Recon. Post (Yes/No) (Post Button)
• C370-Bank Acc Rec Post 
• C12252-Match Bank Rec Lines
• T271-Bank Account Ledger entry (Right hand side) (P380)
• T274-Bank Acc. Reconciliation Line (Left Hand Side) (P372)
• 
  
• BankRecLineTypes
• 0-Bank Account Ledger Entry,
• 1-Check Ledger Entry,
• 2-Difference
• Statement Statuses
• 0-Open,
• 1-Bank Acc. Entry Applied,
• 2-Check Entry Applied,
• 3-Closed
• Unapplying a bank entry to a rec line does the following
• BankAccReconLine."Applied Amount" -= BankAccLedgEntry."Remaining Amount";
• BankAccReconLine."Applied Entries" := BankAccReconLine."Applied Entries" - 1;

• Applying a bank entry to a rec line does the following
• BankAccReconLine."Ready for Application" := TRUE;
• SetReconNo(BankAccLedgEntry,BankAccReconLine);
• If BankLedgerentry
• BankAccLedgEntry.TESTFIELD(Open,TRUE);
• BankAccLedgEntry.TESTFIELD("Statement Status",BankAccLedgEntry."Statement Status"::Open);
• BankAccLedgEntry.TESTFIELD("Statement No.",'');
• BankAccLedgEntry.TESTFIELD("Statement Line No.",0);
• BankAccLedgEntry.TESTFIELD("Bank Account No.",BankAccReconLine."Bank Account No.");
• BankAccLedgEntry."Statement Status" :=
•   BankAccLedgEntry."Statement Status"::"Bank Acc. Entry Applied";
• BankAccLedgEntry."Statement No." := BankAccReconLine."Statement No.";
• BankAccLedgEntry."Statement Line No." := BankAccReconLine."Statement Line No.";
• If checkLedgerentry
•     CheckLedgEntry.TESTFIELD("Statement Status",CheckLedgEntry."Statement Status"::Open);
•     CheckLedgEntry.TESTFIELD("Statement No.",'');
•     CheckLedgEntry.TESTFIELD("Statement Line No.",0);
•     CheckLedgEntry."Statement Status" :=
•       CheckLedgEntry."Statement Status"::"Bank Acc. Entry Applied";
•     CheckLedgEntry."Statement No." := '';
•     CheckLedgEntry."Statement Line No." := 0;
• BankAccReconLine."Applied Amount" += BankAccLedgEntry."Remaining Amount";
• BankAccReconLine."Applied Entries" := BankAccReconLine."Applied Entries" + 1;

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

--Views to show what trx are applied, and to check the totals

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

/****** Object:  View [dbo].[BI_BankRecApply]    Script Date: 6/22/2018 2:10:02 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[BI_BankRecApply]

AS

SELECT        dbo.[CRONUS$Bank Acc_ Reconciliation Line].[Bank Account No_], dbo.[CRONUS$Bank Acc_ Reconciliation Line].[Statement No_], 

                         dbo.[CRONUS$Bank Acc_ Reconciliation Line].[Statement Line No_], dbo.[CRONUS$Bank Acc_ Reconciliation Line].[Statement Amount], 

                         dbo.[CRONUS$Bank Acc_ Reconciliation Line].[Applied Entries], dbo.[CRONUS$Bank Acc_ Reconciliation Line].[Applied Amount], 

                         SUM(dbo.[CRONUS$Bank Account Ledger Entry].[Debit Amount]) AS BLEDebit, SUM(dbo.[CRONUS$Bank Account Ledger Entry].[Credit Amount]) AS BLECredit, 

                         COUNT(dbo.[CRONUS$Bank Account Ledger Entry].[Document No_]) AS BLEApplied

FROM            dbo.[CRONUS$Bank Acc_ Reconciliation Line] LEFT OUTER JOIN

                         dbo.[CRONUS$Bank Account Ledger Entry] ON dbo.[CRONUS$Bank Acc_ Reconciliation Line].[Bank Account No_] = dbo.[CRONUS$Bank Account Ledger Entry].[Bank Account No_] AND 

                         dbo.[CRONUS$Bank Acc_ Reconciliation Line].[Statement No_] = dbo.[CRONUS$Bank Account Ledger Entry].[Statement No_] AND 

                         dbo.[CRONUS$Bank Acc_ Reconciliation Line].[Statement Line No_] = dbo.[CRONUS$Bank Account Ledger Entry].[Statement Line No_]

GROUP BY dbo.[CRONUS$Bank Acc_ Reconciliation Line].[Bank Account No_], dbo.[CRONUS$Bank Acc_ Reconciliation Line].[Statement No_], 

                         dbo.[CRONUS$Bank Acc_ Reconciliation Line].[Statement Line No_], dbo.[CRONUS$Bank Acc_ Reconciliation Line].[Statement Amount], 

                         dbo.[CRONUS$Bank Acc_ Reconciliation Line].[Applied Entries], dbo.[CRONUS$Bank Acc_ Reconciliation Line].[Applied Amount]

GO

/****** Object:  View [dbo].[BI_BankRecApply_Smry]    Script Date: 6/22/2018 2:10:09 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/****** Script for SelectTopNRows command from SSMS  ******/

CREATE VIEW [dbo].[BI_BankRecApply_Smry]

AS

SELECT        dbo.BI_BankRecApply.[Bank Account No_], dbo.BI_BankRecApply.[Statement No_], SUM(dbo.BI_BankRecApply.[Statement Amount]) AS StmtAmt, SUM(dbo.BI_BankRecApply.[Applied Amount]) AS AppldAmt, 

                         SUM(dbo.BI_BankRecApply.BLEDebit) AS BLEDebit, SUM(dbo.BI_BankRecApply.BLECredit) AS BLECredit, SUM(dbo.BI_BankRecApply.BLEDebit - dbo.BI_BankRecApply.BLECredit) AS BLETotal, 

                         dbo.[CRONUS$Bank Acc_ Reconciliation].[Statement Ending Balance], dbo.[CRONUS$Bank Acc_ Reconciliation].[Balance Last Statement]

FROM            dbo.BI_BankRecApply INNER JOIN

                         dbo.[CRONUS$Bank Acc_ Reconciliation] ON dbo.BI_BankRecApply.[Bank Account No_] = dbo.[CRONUS$Bank Acc_ Reconciliation].[Bank Account No_]

GROUP BY dbo.BI_BankRecApply.[Bank Account No_], dbo.BI_BankRecApply.[Statement No_], dbo.[CRONUS$Bank Acc_ Reconciliation].[Statement Ending Balance], 

                         dbo.[CRONUS$Bank Acc_ Reconciliation].[Balance Last Statement]

GO

/****** Object:  View [dbo].[BI_BankRecApply_Smry2]    Script Date: 6/22/2018 2:10:17 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[BI_BankRecApply_Smry2]

AS

SELECT        [Bank Account No_], ([Balance Last Statement] - [Statement Ending Balance] + StmtAmt) * - 1 AS RndgAdjustment, StmtAmt, AppldAmt, BLEDebit, BLECredit, BLETotal, [Balance Last Statement], [Statement Ending Balance], 

                         BLETotal + [Balance Last Statement] AS CalcStmtBal

FROM            dbo.BI_BankRecApply_Smry

GO