Report 1407 Bank Account Statement
- App
- Base Application
- Namespace
- Microsoft.Bank.Reports
- Versions
- 17-28
Versions171819202122232425262728
Source29
Source in 29
src/Layers/W1/BaseApp/Bank/Reports/BankAccountStatement.Report.al394 lines, Copyright (c) Microsoft Corporation. MIT
// ------------------------------------------------------------------------------------------------
// Copyright (c) Microsoft Corporation. All rights reserved.
// Licensed under the MIT License. See License.txt in the project root for license information.
// ------------------------------------------------------------------------------------------------
namespace Microsoft.Bank.Reports;
using Microsoft.Bank.BankAccount;
using Microsoft.Bank.Ledger;
using Microsoft.Bank.Reconciliation;
using Microsoft.Bank.Statement;
/// <summary>
/// Generates bank account statement report with posted reconciliation details and outstanding transactions.
/// Displays statement balances, G/L balances, and outstanding items for bank account analysis.
/// </summary>
/// <remarks>
/// Data Sources: Bank Account Statement, Bank Account Statement Line, Outstanding Bank Transaction, Outstanding Check.
/// Features: Balance reconciliation display, outstanding transaction tracking, statement line detail reporting.
/// Usage: Bank statement documentation, balance verification, outstanding item analysis, statement archiving.
/// </remarks>
report 1407 "Bank Account Statement"
{
Caption = 'Bank Account Statement';
WordMergeDataItem = "Bank Account Statement";
DefaultRenderingLayout = RDLCLayout;
dataset
{
dataitem("Bank Account Statement"; "Bank Account Statement")
{
DataItemTableView = sorting("Bank Account No.", "Statement No.");
PrintOnlyIfDetail = true;
RequestFilterFields = "Bank Account No.", "Statement No.";
column(ComanyName; COMPANYPROPERTY.DisplayName())
{
}
column(BankAccStmtTableCaptFltr; TableCaption + ': ' + BankAccStmtFilter)
{
}
column(BankAccStmtFilter; BankAccStmtFilter)
{
}
column(StmtNo_BankAccStmt; "Statement No.")
{
IncludeCaption = true;
}
column(Bank_Acc__Reconciliation___Statement_Date_Caption; Bank_Acc__Reconciliation___Statement_Date_CaptionLbl)
{
}
column(Bank_Acc__Reconciliation___Statement_Date_; Format("Bank Account Statement"."Statement Date"))
{
}
column(Bank_Acc__Reconciliation___Balance_Last_Statement_Caption; Bank_Acc__Reconciliation___Balance_Last_Statement_CaptionLbl)
{
}
column(Bank_Acc__Reconciliation___Balance_Last_Statement_; "Bank Account Statement"."Balance Last Statement")
{
}
column(Bank_Acc__Reconciliation___Statement_Ending_Balance_Caption; Bank_Acc__Reconciliation___Statement_Ending_Balance_CaptionLbl)
{
}
column(Bank_Acc__Reconciliation___Statement_Ending_Balance_; "Bank Account Statement"."Statement Ending Balance")
{
}
column(G_L_BalanceCaption; G_L_BalanceCaptionTxt)
{
}
column(Bank_Acc__Reconciliation___TotalBalOnBankAccount; GLBalanceAtPostingDate)
{
}
column(Subtotal_Caption; Subtotal_CaptionLbl)
{
}
column(GL_Subtotal; GLBalanceAtPostingDate + "Bank Account Statement"."Total Pos. Diff. at Posting")
{
}
column(Ending_G_L_BalanceCaption; BankAccountBalanceLbl)
{
}
column(Ending_GL_Balance; GLBalanceAtPostingDate + "Bank Account Statement"."Total Pos. Diff. at Posting" + "Bank Account Statement"."Total Neg. Diff. at Posting")
{
}
column(Currency_CodeCaption; CurrencyCodeCaption)
{
}
column(Currency_Code; CurrencyCode)
{
}
column(Statement_BalanceCaption; Statement_BalanceCaptionLbl)
{
}
column(Outstanding_BankTransactionsCaption; Outstanding_BankTransactionsCaptionLbl)
{
}
column(Bank_Acc__Reconciliation___TotalOutstdBankTransactions; "Bank Account Statement"."Outstd. Transact. at Posting")
{
}
column(Statement_Subtotal; "Bank Account Statement"."Statement Ending Balance" + "Bank Account Statement"."Outstd. Transact. at Posting")
{
}
column(Outstanding_PaymentsCaption; Outstanding_PaymentsCaptionLbl)
{
}
column(Bank_Acc__Reconciliation___TotalOutstdPayments; "Bank Account Statement"."Outstd. Payments at Posting")
{
}
column(Ending_BalanceCaption; BankAccountBalanceLbl)
{
}
column(Adjusted_Statement_Ending_Balance; "Bank Account Statement"."Statement Ending Balance" + "Bank Account Statement"."Outstd. Payments at Posting" + "Bank Account Statement"."Outstd. Transact. at Posting")
{
}
column(Amt_BankAccStmtLineStmt; "Bank Account Statement Line"."Statement Amount")
{
}
column(AppliedAmt_BankAccStmtLine; "Bank Account Statement Line"."Applied Amount")
{
}
column(BankAccNo_BankAccStmt; "Bank Account No.")
{
}
column(BankAccStmtCapt; BankAccStmtCaptLbl)
{
}
column(CurrReportPAGENOCapt; CurrReportPAGENOCaptLbl)
{
}
column(BnkAccStmtLinTrstnDteCapt; BnkAccStmtLinTrstnDteCaptLbl)
{
}
column(BnkAcStmtLinValDteCapt; BnkAcStmtLinValDteCaptLbl)
{
}
column(GLBalanceCaption; GLBalanceCaptionLbl)
{
}
column(CompletedPayments_SectionTitleCaption; CompletedPayments_SectionTitleLbl)
{
}
dataitem("Bank Account Statement Line"; "Bank Account Statement Line")
{
DataItemLink = "Bank Account No." = field("Bank Account No."), "Statement No." = field("Statement No.");
DataItemTableView = sorting("Bank Account No.", "Statement No.", "Statement Line No.");
column(Statement_No_BnkAcStmtLine; "Statement No.")
{
}
column(Statement_Line_No_BnkAcStmtLine; "Statement Line No.")
{
}
column(TrnsctnDte_BnkAcStmtLin; Format("Transaction Date"))
{
}
column(Type_BankAccStmtLine; Type)
{
IncludeCaption = true;
}
column(LineDocNo_BankAccStmt; "Document No.")
{
IncludeCaption = true;
}
column(AppliedEntr_BankAccStmtLine; "Applied Entries")
{
IncludeCaption = true;
}
column(Amt1_BankAccStmtLineStmt; "Statement Amount")
{
IncludeCaption = true;
}
column(AppliedAmt1_BankAccStmtLine; "Applied Amount")
{
IncludeCaption = true;
}
column(Desc_BankAccStmtLine; Description)
{
IncludeCaption = true;
}
column(ValueDate_BankAccStmtLine; Format("Value Date"))
{
}
}
#region [Outstanding Bank Transaction]
column(Print_Outstanding_Transactions; PrintOutstandingTransactions)
{
}
column(Outstanding_BankTransaction_SectionTitleCaption; Outstanding_BankTransaction_SectionTitleLbl)
{
}
column(Outstanding_BankTransaction_PostingDateCaption; Outstanding_BankTransaction_PostingDateLbl)
{
}
column(Outstanding_BankTransaction_DocTypeCaption; Outstanding_BankTransaction_DocTypeLbl)
{
}
column(Outstanding_BankTransaction_DocNoCaption; Outstanding_BankTransaction_DocNoLbl)
{
}
column(Outstanding_BankTransaction_DescriptionCaption; Outstanding_BankTransaction_DescriptionLbl)
{
}
column(Outstanding_BankTransaction_AmountCaption; Outstanding_BankTransaction_AmountLbl)
{
}
dataitem(OutstandingBankTransaction; "Bank Account Ledger Entry")
{
DataItemTableView = sorting("Entry No.");
UseTemporary = true;
column(Outstanding_BankTransaction_PostingDate; Format("Posting Date"))
{
}
column(Outstanding_BankTransaction_DocType; "Document Type")
{
}
column(Outstanding_BankTransaction_DocNo; "Document No.")
{
}
column(Outstanding_BankTransaction_Description; Description)
{
}
column(Outstanding_BankTransaction_Amount; Amount)
{
}
}
#endregion
#region [Outstanding Checks]
column(Outstanding_Check_SectionTitleCaption; Outstanding_Check_SectionTitleLbl)
{
}
column(Outstanding_Check_PostingDateCaption; Outstanding_Check_PostingDateLbl)
{
}
column(Outstanding_Check_DocTypeCaption; Outstanding_Check_DocTypeLbl)
{
}
column(Outstanding_Check_CheckNoCaption; Outstanding_Check_CheckNoLbl)
{
}
column(Outstanding_Check_DescriptionCaption; Outstanding_Check_DescriptionLbl)
{
}
column(Outstanding_Check_AmountCaption; Outstanding_Check_AmountLbl)
{
}
dataitem(OutstandingCheck; "Bank Account Ledger Entry")
{
DataItemTableView = sorting("Entry No.");
UseTemporary = true;
column(Outstanding_Check_PostingDate; Format("Posting Date"))
{
}
column(Outstanding_Check_DocType; "Document Type")
{
}
column(Outstanding_Check_CheckNo; "Document No.")
{
}
column(Outstanding_Check_Description; Description)
{
}
column(Outstanding_Check_Amount; Amount)
{
}
}
#endregion
trigger OnAfterGetRecord()
var
BankAcc: Record "Bank Account";
begin
if BankAcc.Get("Bank Account Statement"."Bank Account No.") then begin
CurrencyCode := BankAcc."Currency Code";
CurrencyCodeCaption := BankAcc.FieldCaption("Currency Code");
end;
G_L_BalanceCaptionTxt := G_L_BalanceCaptionLbl;
if "Bank Account Statement"."Statement Date" <> 0D then
G_L_BalanceCaptionTxt := G_L_BalanceCaptionTxt + AtLbl + format("Statement Date");
GLBalanceAtPostingDate := BankAccReconTest.GetGLAccountBalanceLCYForBankStatement("Bank Account Statement");
if PrintOutstandingTransactions then
GatherOutstandingTransactions("Bank Account No.");
end;
}
}
requestpage
{
SaveValues = true;
layout
{
area(content)
{
group(Control2)
{
ShowCaption = false;
field(PrintOutstandingTransaction; PrintOutstandingTransactions)
{
ApplicationArea = Basic, Suite;
Caption = 'Print Outstanding Transactions';
ToolTip = 'Specifies if the report includes lines for outstanding transactions.';
}
}
}
}
actions
{
}
}
rendering
{
layout(RDLCLayout)
{
Type = RDLC;
LayoutFile = './Bank/Reports/BankAccountStatement.rdlc';
Summary = 'Report layout made in the legacy RDLC format. Use an RDLC editor to modify the layout.';
}
}
labels
{
TotalCaption = 'Total';
QuantityCaption = 'Quantity';
}
trigger OnPreReport()
begin
BankAccStmtFilter := "Bank Account Statement".GetFilters();
end;
var
BankAccReconTest: Codeunit "Bank Acc. Recon. Test";
BankAccStmtFilter: Text;
G_L_BalanceCaptionTxt: Text;
CurrencyCodeCaption: Text;
BankAccStmtCaptLbl: Label 'Bank Account Statement';
CurrReportPAGENOCaptLbl: Label 'Page';
BnkAccStmtLinTrstnDteCaptLbl: Label 'Transaction Date';
BnkAcStmtLinValDteCaptLbl: Label 'Value Date';
GLBalanceCaptionLbl: Label 'G/L Balance';
Bank_Acc__Reconciliation___Statement_Date_CaptionLbl: Label 'Statement Date';
Bank_Acc__Reconciliation___Balance_Last_Statement_CaptionLbl: Label 'Balance Last Statement';
Bank_Acc__Reconciliation___Statement_Ending_Balance_CaptionLbl: Label 'Statement Ending Balance';
G_L_BalanceCaptionLbl: Label 'G/L Balance';
Subtotal_CaptionLbl: Label 'Subtotal';
BankAccountBalanceLbl: Label 'Bank Account Balance';
Statement_BalanceCaptionLbl: Label 'Statement Balance';
CompletedPayments_SectionTitleLbl: Label 'Completed Payments';
Outstanding_PaymentsCaptionLbl: Label 'Outstanding Checks';
Outstanding_BankTransaction_SectionTitleLbl: Label 'Outstanding Payments';
Outstanding_BankTransactionsCaptionLbl: Label 'Outstanding Bank Transactions';
Outstanding_BankTransaction_PostingDateLbl: Label 'Posting Date';
Outstanding_BankTransaction_DocTypeLbl: Label 'Document Type';
Outstanding_BankTransaction_DocNoLbl: Label 'Document No.';
Outstanding_BankTransaction_DescriptionLbl: Label 'Description';
Outstanding_BankTransaction_AmountLbl: Label 'Statement Amount';
Outstanding_Check_SectionTitleLbl: Label 'Outstanding Checks';
Outstanding_Check_PostingDateLbl: Label 'Posting Date';
Outstanding_Check_DocTypeLbl: Label 'Document Type';
Outstanding_Check_CheckNoLbl: Label 'Check No.';
Outstanding_Check_DescriptionLbl: Label 'Description';
Outstanding_Check_AmountLbl: Label 'Statement Amount';
AtLbl: Label ' at ', Comment = 'used to build the construct a string like balance at 31-12-2020';
CurrencyCode: Code[20];
PrintOutstandingTransactions: Boolean;
GLBalanceAtPostingDate: Decimal;
local procedure GatherOutstandingTransactions(BankAccountNo: Code[20])
var
TempBankAccountReconciliation: Record "Bank Acc. Reconciliation" temporary;
BankAccountLedgerEntry: Record "Bank Account Ledger Entry";
begin
TempBankAccountReconciliation."Bank Account No." := BankAccountNo;
TempBankAccountReconciliation."Statement No." := "Bank Account Statement"."Statement No.";
TempBankAccountReconciliation."Statement Date" := "Bank Account Statement"."Statement Date";
BankAccReconTest.SetOutstandingFilters(TempBankAccountReconciliation, BankAccountLedgerEntry);
BankAccountLedgerEntry.SetFilter(SystemCreatedAt, '..%1', "Bank Account Statement".SystemCreatedAt);
if BankAccountLedgerEntry.IsEmpty() then
exit;
BankAccountLedgerEntry.SetAutoCalcFields("Check Ledger Entries");
if BankAccountLedgerEntry.FindSet() then
repeat
if BankAccReconTest.CheckBankAccountLedgerEntryFilters(BankAccountLedgerEntry, TempBankAccountReconciliation."Statement No.", TempBankAccountReconciliation."Statement Date") then
if (BankAccountLedgerEntry."Closed at Date" = 0D) or BankAccountLedgerEntry.Open or (BankAccountLedgerEntry."Closed at Date" > "Bank Account Statement"."Statement Date") then
if BankAccountLedgerEntry."Check Ledger Entries" <> 0 then
OutstandingCheck.CopyFromBankAccLedgerEntry(BankAccountLedgerEntry, "Bank Account Statement"."Statement No.")
else
OutstandingBankTransaction.CopyFromBankAccLedgerEntry(BankAccountLedgerEntry, "Bank Account Statement"."Statement No.")
until BankAccountLedgerEntry.Next() = 0;
end;
}