Report 1407 Bank Account Statement

Base Application, Microsoft.Bank.Reports

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