Table 273 Bank Acc. Reconciliation, source in 29
Source29
src/Layers/W1/BaseApp/Bank/Reconciliation/BankAccReconciliation.Table.al1099 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.Reconciliation;
using Microsoft.Bank.BankAccount;
using Microsoft.Bank.Ledger;
using Microsoft.Bank.Statement;
using Microsoft.Finance.Dimension;
using Microsoft.Foundation.AuditCodes;
using Microsoft.Foundation.NoSeries;
using System.IO;
/// <summary>
/// Central table for managing bank account reconciliation and payment application processes.
/// This table serves as the header for bank reconciliation workflows, containing statement information,
/// balances, and processing settings. It supports both traditional bank reconciliation and automated
/// payment application scenarios with comprehensive tracking of applied amounts and differences.
/// </summary>
/// <remarks>
/// Supports two main workflows via Statement Type: Bank Reconciliation for matching bank statements
/// with ledger entries, and Payment Application for processing payment files and applying them to invoices.
/// Integrates with bank statement import, automated matching, manual application, and posting processes.
/// Key extension points include dimension handling, statement import customization, and posting validation.
/// </remarks>
table 273 "Bank Acc. Reconciliation"
{
Caption = 'Bank Acc. Reconciliation';
DataCaptionFields = "Bank Account No.", "Statement No.";
LookupPageID = "Bank Acc. Reconciliation List";
Permissions = TableData "Bank Account" = rm,
TableData "Data Exch." = rimd;
DataClassification = CustomerContent;
fields
{
/// <summary>
/// Bank account number that this reconciliation statement relates to.
/// Links the reconciliation to a specific bank account and drives currency and setup inheritance.
/// </summary>
field(1; "Bank Account No."; Code[20])
{
Caption = 'Bank Account No.';
ToolTip = 'Specifies the number of the bank account that you want to reconcile with the bank''s statement.';
NotBlank = true;
TableRelation = "Bank Account";
trigger OnValidate()
var
NoSeries: Codeunit "No. Series";
begin
if "Statement No." = '' then begin
BankAcc.Get("Bank Account No.");
case "Statement Type" of
"Statement Type"::"Payment Application":
if BankAcc."Pmt. Rec. No. Series" = '' then begin
SetLastPaymentStatementNo(BankAcc);
"Statement No." := IncStr(BankAcc."Last Payment Statement No.");
end else
"Statement No." := NoSeries.GetNextNo(BankAcc."Pmt. Rec. No. Series", Today());
"Statement Type"::"Bank Reconciliation":
begin
SetLastStatementNo(BankAcc);
"Statement No." := IncStr(BankAcc."Last Statement No.");
end;
end;
"Balance Last Statement" := BankAcc."Balance Last Statement";
end;
CreateDimFromDefaultDim();
end;
}
/// <summary>
/// Unique statement number for this reconciliation document.
/// Automatically generated based on bank account number series configuration.
/// </summary>
field(2; "Statement No."; Code[20])
{
Caption = 'Statement No.';
ToolTip = 'Specifies the number of the bank account statement.';
Editable = false;
NotBlank = true;
trigger OnValidate()
begin
TestField("Bank Account No.");
end;
}
/// <summary>
/// Ending balance as reported on the bank statement.
/// Used as the target balance for reconciliation and difference calculation.
/// </summary>
field(3; "Statement Ending Balance"; Decimal)
{
AutoFormatExpression = GetCurrencyCode();
AutoFormatType = 2;
Caption = 'Statement Ending Balance';
ToolTip = 'Specifies the ending balance shown on the bank''s statement that you want to reconcile with the bank account.';
}
/// <summary>
/// Date of the bank statement being reconciled.
/// Determines the cutoff date for transaction matching and validation.
/// </summary>
field(4; "Statement Date"; Date)
{
Caption = 'Statement Date';
ToolTip = 'Specifies the date on the bank account statement.';
}
/// <summary>
/// Beginning balance from the previous statement or last reconciliation.
/// Used as the starting point for calculating current period reconciliation differences.
/// </summary>
field(5; "Balance Last Statement"; Decimal)
{
AutoFormatExpression = GetCurrencyCode();
AutoFormatType = 1;
Caption = 'Balance Last Statement';
ToolTip = 'Specifies the ending balance shown on the last bank statement, which was used in the last posted bank reconciliation for this bank account.';
trigger OnValidate()
begin
BankAcc.Get("Bank Account No.");
if "Balance Last Statement" <> BankAcc."Balance Last Statement" then
if not
Confirm(
BalanceQst, false,
FieldCaption("Balance Last Statement"), BankAcc.FieldCaption("Balance Last Statement"),
BankAcc.TableCaption())
then
"Balance Last Statement" := xRec."Balance Last Statement";
end;
}
/// <summary>
/// Binary storage for the imported bank statement file.
/// Contains the original electronic statement data for reference and audit purposes.
/// </summary>
field(6; "Bank Statement"; BLOB)
{
Caption = 'Bank Statement';
}
/// <summary>
/// Calculated total of all bank account ledger entries for this bank account.
/// Represents the current book balance in the general ledger system.
/// </summary>
field(7; "Total Balance on Bank Account"; Decimal)
{
AutoFormatExpression = GetCurrencyCode();
AutoFormatType = 1;
CalcFormula = sum("Bank Account Ledger Entry".Amount where("Bank Account No." = field("Bank Account No.")));
Caption = 'Total Balance on Bank Account';
Editable = false;
FieldClass = FlowField;
}
/// <summary>
/// Sum of all applied amounts on reconciliation lines for this statement.
/// Tracks how much of the statement transactions have been matched and applied.
/// </summary>
field(8; "Total Applied Amount"; Decimal)
{
AutoFormatType = 1;
AutoFormatExpression = GetCurrencyCode();
CalcFormula = sum("Bank Acc. Reconciliation Line"."Applied Amount" where("Statement Type" = field("Statement Type"),
"Bank Account No." = field("Bank Account No."),
"Statement No." = field("Statement No.")));
Caption = 'Total Applied Amount';
Editable = false;
FieldClass = FlowField;
}
/// <summary>
/// Sum of all statement amounts on reconciliation lines for this statement.
/// Represents the total value of transactions imported from the bank statement.
/// </summary>
field(9; "Total Transaction Amount"; Decimal)
{
AutoFormatType = 1;
AutoFormatExpression = GetCurrencyCode();
CalcFormula = sum("Bank Acc. Reconciliation Line"."Statement Amount" where("Statement Type" = field("Statement Type"),
"Bank Account No." = field("Bank Account No."),
"Statement No." = field("Statement No.")));
Caption = 'Total Transaction Amount';
ToolTip = 'Specifies the sum of values in the Statement Amount field on all the lines in the Bank Acc. Reconciliation and Payment Reconciliation Journal windows.';
Editable = false;
FieldClass = FlowField;
}
/// <summary>
/// Sum of applied amounts excluding bank account entries (manual adjustments).
/// Tracks amounts that will create new ledger entries when posted rather than matching existing ones.
/// </summary>
field(10; "Total Unposted Applied Amount"; Decimal)
{
AutoFormatType = 1;
AutoFormatExpression = GetCurrencyCode();
CalcFormula = sum("Bank Acc. Reconciliation Line"."Applied Amount" where("Statement Type" = field("Statement Type"),
"Bank Account No." = field("Bank Account No."),
"Statement No." = field("Statement No."),
"Account Type" = filter(<> "Bank Account")));
Caption = 'Total Unposted Applied Amount';
Editable = false;
FieldClass = FlowField;
}
/// <summary>
/// Sum of all differences between statement amounts and applied amounts.
/// Indicates the total amount of unreconciled differences requiring resolution.
/// </summary>
field(11; "Total Difference"; Decimal)
{
AutoFormatType = 1;
AutoFormatExpression = GetCurrencyCode();
CalcFormula = sum("Bank Acc. Reconciliation Line".Difference where("Statement Type" = field("Statement Type"),
"Bank Account No." = field("Bank Account No."),
"Statement No." = field("Statement No.")));
Caption = 'Total Difference';
ToolTip = 'Specifies the total amount that exists on the bank account per the last time it was reconciled.';
Editable = false;
FieldClass = FlowField;
}
/// <summary>
/// Sum of negative statement amounts representing payments made from the account.
/// Shows total outgoing transactions on the bank statement.
/// </summary>
field(12; "Total Paid Amount"; Decimal)
{
AutoFormatType = 1;
AutoFormatExpression = GetCurrencyCode();
CalcFormula = sum("Bank Acc. Reconciliation Line"."Statement Amount" where("Statement Type" = field("Statement Type"),
"Bank Account No." = field("Bank Account No."),
"Statement No." = field("Statement No."),
"Statement Amount" = filter(< 0)));
Caption = 'Total Paid Amount';
Editable = false;
FieldClass = FlowField;
}
/// <summary>
/// Sum of positive statement amounts representing deposits received in the account.
/// Shows total incoming transactions on the bank statement.
/// </summary>
field(13; "Total Received Amount"; Decimal)
{
AutoFormatType = 1;
AutoFormatExpression = GetCurrencyCode();
CalcFormula = sum("Bank Acc. Reconciliation Line"."Statement Amount" where("Statement Type" = field("Statement Type"),
"Bank Account No." = field("Bank Account No."),
"Statement No." = field("Statement No."),
"Statement Amount" = filter(> 0)));
Caption = 'Total Received Amount';
Editable = false;
FieldClass = FlowField;
}
/// <summary>
/// Type of reconciliation statement being processed.
/// Determines workflow behavior and available features (Bank Reconciliation vs Payment Application).
/// </summary>
field(20; "Statement Type"; Enum "Bank Acc. Rec. Stmt. Type")
{
Caption = 'Statement Type';
}
/// <summary>
/// First global dimension code for this reconciliation.
/// Inherited from bank account setup and applied to generated journal entries.
/// </summary>
field(21; "Shortcut Dimension 1 Code"; Code[20])
{
CaptionClass = '1,2,1';
Caption = 'Shortcut Dimension 1 Code';
TableRelation = "Dimension Value".Code where("Global Dimension No." = const(1),
Blocked = const(false));
trigger OnValidate()
begin
Rec.ValidateShortcutDimCode(1, "Shortcut Dimension 1 Code");
end;
}
/// <summary>
/// Second global dimension code for this reconciliation.
/// Inherited from bank account setup and applied to generated journal entries.
/// </summary>
field(22; "Shortcut Dimension 2 Code"; Code[20])
{
CaptionClass = '1,2,2';
Caption = 'Shortcut Dimension 2 Code';
TableRelation = "Dimension Value".Code where("Global Dimension No." = const(2),
Blocked = const(false));
trigger OnValidate()
begin
Rec.ValidateShortcutDimCode(2, "Shortcut Dimension 2 Code");
end;
}
/// <summary>
/// Flag indicating whether to post only payment applications without bank account entries.
/// Used in payment application scenarios to create customer/vendor entries without bank entries.
/// </summary>
field(23; "Post Payments Only"; Boolean)
{
Caption = 'Post Payments Only';
}
/// <summary>
/// Option controlling whether already posted transactions should be imported from the bank account.
/// Helps prevent duplicate transaction import and processing.
/// </summary>
field(24; "Import Posted Transactions"; Option)
{
Caption = 'Import Posted Transactions';
ToolTip = 'Specifies whether to import bank transactions that are already applied but whose related bank account ledger entries are not yet closed.';
OptionCaption = ' ,Yes,No';
OptionMembers = " ",Yes,No;
}
/// <summary>
/// Sum of open bank account ledger entries excluding check entries.
/// Represents outstanding bank transactions that have not been reconciled.
/// </summary>
field(25; "Total Outstd Bank Transactions"; Decimal)
{
AutoFormatType = 1;
AutoFormatExpression = GetCurrencyCode();
CalcFormula = sum("Bank Account Ledger Entry".Amount where("Bank Account No." = field("Bank Account No."),
Open = const(true),
"Check Ledger Entries" = const(0)));
Caption = 'Total Outstd Bank Transactions';
Editable = false;
FieldClass = FlowField;
}
/// <summary>
/// Sum of open bank account ledger entries with associated check entries.
/// Represents outstanding check payments that have not been reconciled.
/// </summary>
field(26; "Total Outstd Payments"; Decimal)
{
AutoFormatType = 1;
AutoFormatExpression = GetCurrencyCode();
CalcFormula = sum("Bank Account Ledger Entry".Amount where("Bank Account No." = field("Bank Account No."),
Open = const(true),
"Check Ledger Entries" = filter(> 0)));
Caption = 'Total Outstd Payments';
Editable = false;
FieldClass = FlowField;
}
/// <summary>
/// Total bank account balance in local currency.
/// Provides balance information in the company's reporting currency regardless of bank account currency.
/// </summary>
field(28; "Bank Account Balance (LCY)"; Decimal)
{
CalcFormula = sum("Bank Account Ledger Entry"."Amount (LCY)" where("Bank Account No." = field("Bank Account No.")));
AutoFormatType = 1;
AutoFormatExpression = '';
Caption = 'Bank Account Balance (LCY)';
Editable = false;
FieldClass = FlowField;
}
/// <summary>
/// Sum of positive adjustment amounts for non-bank account applications.
/// Tracks positive differences that will increase the bank balance when posted.
/// </summary>
field(29; "Total Positive Adjustments"; Decimal)
{
AutoFormatType = 1;
AutoFormatExpression = GetCurrencyCode();
CalcFormula = sum("Bank Acc. Reconciliation Line"."Applied Amount" where("Statement Type" = field("Statement Type"),
"Bank Account No." = field("Bank Account No."),
"Statement No." = field("Statement No."),
"Account Type" = filter(<> "Bank Account"),
"Statement Amount" = filter(> 0)));
Caption = 'Total Positive Adjustments';
Editable = false;
FieldClass = FlowField;
}
/// <summary>
/// Sum of negative adjustment amounts for non-bank account applications.
/// Tracks negative differences that will decrease the bank balance when posted.
/// </summary>
field(30; "Total Negative Adjustments"; Decimal)
{
AutoFormatType = 1;
AutoFormatExpression = GetCurrencyCode();
CalcFormula = sum("Bank Acc. Reconciliation Line"."Applied Amount" where("Statement Type" = field("Statement Type"),
"Bank Account No." = field("Bank Account No."),
"Statement No." = field("Statement No."),
"Account Type" = filter(<> "Bank Account"),
"Statement Amount" = filter(< 0)));
Caption = 'Total Negative Adjustments';
Editable = false;
FieldClass = FlowField;
}
/// <summary>
/// Flag controlling whether VAT setup should be copied to generated journal lines.
/// Ensures proper VAT handling when posting reconciliation adjustments.
/// </summary>
field(33; "Copy VAT Setup to Jnl. Line"; Boolean)
{
Caption = 'Copy VAT Setup to Jnl. Line';
ToolTip = 'Specifies whether the program to calculate VAT for accounts and balancing accounts on the journal line of the selected bank account reconciliation.';
InitValue = true;
}
/// <summary>
/// Name of the bank account for display purposes.
/// Automatically retrieved from the bank account master record.
/// </summary>
field(50; "Bank Account Name"; Text[100])
{
ToolTip = 'Specifies the name of the bank account that you want to reconcile.';
FieldClass = FlowField;
CalcFormula = lookup("Bank Account".Name where("No." = field("Bank Account No.")));
}
/// <summary>
/// Flag allowing the import of duplicate transactions from bank statements.
/// When enabled, prevents blocking of statement import due to duplicate detection.
/// </summary>
field(51; "Allow Duplicated Transactions"; Boolean)
{
Caption = 'Import Duplicated Transactions';
ToolTip = 'Specifies whether to allow bank account reconciliation lines to have the same transaction ID.';
}
/// <summary>
/// Dimension set identifier linking this reconciliation to its dimension values.
/// Used for tracking and reporting on reconciliation activities by dimension.
/// </summary>
field(480; "Dimension Set ID"; Integer)
{
Caption = 'Dimension Set ID';
Editable = false;
TableRelation = "Dimension Set Entry";
trigger OnLookup()
begin
Rec.ShowDocDim();
end;
trigger OnValidate()
begin
DimMgt.UpdateGlobalDimFromDimSetID("Dimension Set ID", "Shortcut Dimension 1 Code", "Shortcut Dimension 2 Code");
end;
}
}
keys
{
key(Key1; "Statement Type", "Bank Account No.", "Statement No.")
{
Clustered = true;
}
}
fieldgroups
{
}
trigger OnDelete()
begin
if BankAccReconLine.LinesExist(Rec) then
BankAccReconLine.DeleteAll(true);
end;
trigger OnInsert()
begin
OnInsertValidations(Rec);
SetLastStatementNoInBankAccount(Rec);
end;
trigger OnRename()
begin
Error(RenameErr, TableCaption);
end;
var
BankAccReconLine: Record "Bank Acc. Reconciliation Line";
DimMgt: Codeunit DimensionManagement;
DuplicateStatementErr: Label 'A statement with number %1 has already been posted. Make sure that the fields "Last Statement No." and "Last Payment Statement No." in the bank account are correct. If you are changing the statement number, you can choose a different target number or undo the existing bank statement. ', Comment = '%1=Statement No. value';
RenameErr: Label 'You cannot rename a %1.', Comment = '%1=Table name caption';
BalanceQst: Label '%1 is different from %2 on the %3. Do you want to change the value?', Comment = '%1=Balance Last Statement field caption;%2=field caption;%3=table caption';
YouChangedDimQst: Label 'You may have changed a dimension.\\Do you want to update the lines?';
NoBankAccountsMsg: Label 'You have not set up a bank account.\To use the payments import process, set up a bank account.';
NoBankAccWithFileFormatMsg: Label 'No bank account exists that is ready for import of bank statement files.\Fill the Bank Statement Import Format field on the card of the bank account that you want to use.';
PostHighConfidentLinesQst: Label 'All imported bank statement lines were applied with high confidence level.\Do you want to post the payment applications?';
#pragma warning disable AA0470
MustHaveValueQst: Label 'The bank account must have a value in %1. Do you want to open the bank account card?';
#pragma warning restore AA0470
NoTransactionsImportedMsg: Label 'No bank transactions were imported. For example, because the transactions were imported in other bank account reconciliations, or because they are already applied to bank account ledger entries. You can view the applied transactions on the Bank Account Statement List page and on the Posted Payment Reconciliations page.';
BankReconciliationFeatureNameTelemetryTxt: Label 'Bank reconciliation', Locked = true;
PaymentRecJournalFeatureNameTelemetryTxt: Label 'Payment Reconciliation', Locked = true;
protected var
BankAcc: Record "Bank Account";
local procedure OnInsertValidations(BankAccReconciliation: Record "Bank Acc. Reconciliation")
begin
BankAccReconciliation.TestField("Statement No.");
BankAccReconciliation.TestField("Bank Account No.");
OnInsertValidations(BankAccReconciliation."Statement Type", BankAccReconciliation."Bank Account No.", BankAccReconciliation."Statement No.");
end;
internal procedure OnInsertValidations(StatementType: Enum "Bank Acc. Rec. Stmt. Type"; BankAccountNo: Code[20]; StatementNo: Code[20])
var
BankAccount: Record "Bank Account";
BankAccountStatement: Record "Bank Account Statement";
PostedPaymentReconciliationHeader: Record "Posted Payment Recon. Hdr";
begin
BankAccount.Get(BankAccountNo);
case StatementType of
StatementType::"Bank Reconciliation":
if BankAccountStatement.Get(BankAccountNo, StatementNo) then
Error(DuplicateStatementErr, StatementNo);
StatementType::"Payment Application":
if PostedPaymentReconciliationHeader.Get(BankAccountNo, StatementNo) then
Error(DuplicateStatementErr, StatementNo);
end;
end;
local procedure SetLastStatementNoInBankAccount(BankAccReconciliation: Record "Bank Acc. Reconciliation")
var
BankAccount: Record "Bank Account";
begin
BankAccount.Get(BankAccReconciliation."Bank Account No.");
case BankAccReconciliation."Statement Type" of
"Bank Acc. Rec. Stmt. Type"::"Bank Reconciliation":
BankAccount."Last Statement No." := BankAccReconciliation."Statement No.";
"Bank Acc. Rec. Stmt. Type"::"Payment Application":
if BankAccount."Pmt. Rec. No. Series" = '' then
BankAccount."Last Payment Statement No." := BankAccReconciliation."Statement No.";
end;
BankAccount.Modify();
end;
internal procedure GetPaymentRecJournalTelemetryFeatureName(): Text
begin
exit(PaymentRecJournalFeatureNameTelemetryTxt);
end;
internal procedure GetBankReconciliationTelemetryFeatureName(): Text
begin
exit(BankReconciliationFeatureNameTelemetryTxt);
end;
/// <summary>
/// Creates dimension set ID from default dimension sources for this bank reconciliation.
/// Builds dimension combinations from bank account defaults and custom dimension sources.
/// Updates all related reconciliation lines when dimensions change.
/// </summary>
/// <param name="DefaultDimSource">Dictionary list containing dimension source types and values to apply</param>
procedure CreateDim(DefaultDimSource: List of [Dictionary of [Integer, Code[20]]])
var
SourceCodeSetup: Record "Source Code Setup";
OldDimSetID: Integer;
begin
SourceCodeSetup.Get();
"Shortcut Dimension 1 Code" := '';
"Shortcut Dimension 2 Code" := '';
OldDimSetID := "Dimension Set ID";
"Dimension Set ID" :=
DimMgt.GetRecDefaultDimID(
Rec, CurrFieldNo, DefaultDimSource, SourceCodeSetup."Payment Reconciliation Journal",
"Shortcut Dimension 1 Code", "Shortcut Dimension 2 Code", 0, 0);
OnCreateDimOnAfterSetDimensionSetID(Rec, OldDimSetID, DefaultDimSource);
if (OldDimSetID <> "Dimension Set ID") and LinesExist() then begin
Modify();
UpdateAllLineDim("Dimension Set ID", OldDimSetID);
end;
end;
local procedure GetCurrencyCode(): Code[10]
var
BankAcc2: Record "Bank Account";
begin
if "Bank Account No." = BankAcc2."No." then
exit(BankAcc2."Currency Code");
if BankAcc2.Get("Bank Account No.") then
exit(BankAcc2."Currency Code");
exit('');
end;
/// <summary>
/// Runs automatic matching process for bank reconciliation lines against bank ledger entries.
/// Uses configurable date range and matching rules to find and apply probable matches.
/// </summary>
/// <param name="DateRange">Number of days to extend the matching date range beyond statement dates</param>
procedure MatchSingle(DateRange: Integer)
var
MatchBankRecLines: Codeunit "Match Bank Rec. Lines";
IsHandled: Boolean;
begin
IsHandled := false;
OnBeforeMatchSingle(Rec, DateRange, IsHandled);
if IsHandled then
exit;
MatchBankRecLines.BankAccReconciliationAutoMatch(Rec, DateRange);
end;
/// <summary>
/// Initiates bank statement import process for this reconciliation.
/// Creates reconciliation record and imports statement data using configured import format.
/// </summary>
procedure ImportBankStatement()
var
DataExch: Record "Data Exch.";
ProcessBankAccRecLines: Codeunit "Process Bank Acc. Rec Lines";
begin
CreateBankAccountReconcillation();
if BankAccountCouldBeUsedForImport() then begin
DataExch.Init();
BindSubscription(ProcessBankAccRecLines);
ProcessBankAccRecLines.SetBankAccountNo(Rec."Bank Account No.");
ProcessBankAccRecLines.ImportBankStatement(Rec, DataExch);
UnBindSubscription(ProcessBankAccRecLines);
end;
end;
/// <summary>
/// Validates and updates dimension shortcut codes for this reconciliation.
/// Propagates dimension changes to all related reconciliation lines automatically.
/// </summary>
/// <param name="FieldNumber">Field number of the shortcut dimension being validated (1 or 2)</param>
/// <param name="ShortcutDimCode">Dimension value code to validate and assign</param>
procedure ValidateShortcutDimCode(FieldNumber: Integer; var ShortcutDimCode: Code[20])
var
OldDimSetID: Integer;
begin
OnBeforeValidateShortcutDimCode(Rec, xRec, FieldNumber, ShortcutDimCode);
OldDimSetID := "Dimension Set ID";
DimMgt.ValidateShortcutDimValues(FieldNumber, ShortcutDimCode, "Dimension Set ID");
if OldDimSetID <> "Dimension Set ID" then begin
Modify();
UpdateAllLineDim("Dimension Set ID", OldDimSetID);
end;
OnAfterValidateShortcutDimCode(Rec, xRec, FieldNumber, ShortcutDimCode);
end;
/// <summary>
/// Opens dimension maintenance dialog for this reconciliation header.
/// Allows interactive editing of all dimension values and propagates changes to lines.
/// </summary>
procedure ShowDocDim()
var
OldDimSetID: Integer;
begin
OldDimSetID := "Dimension Set ID";
"Dimension Set ID" :=
DimMgt.EditDimensionSet(
Rec, "Dimension Set ID", StrSubstNo('%1 %2', TableCaption(), "Statement No."),
"Shortcut Dimension 1 Code", "Shortcut Dimension 2 Code");
if OldDimSetID <> "Dimension Set ID" then begin
Modify();
UpdateAllLineDim("Dimension Set ID", OldDimSetID);
end;
end;
local procedure UpdateAllLineDim(NewParentDimSetID: Integer; OldParentDimSetID: Integer)
var
BankAccReconciliationLine: Record "Bank Acc. Reconciliation Line";
NewDimSetID: Integer;
begin
// Update all lines with changed dimensions.
if NewParentDimSetID = OldParentDimSetID then
exit;
BankAccReconciliationLine.LockTable();
if BankAccReconciliationLine.LinesExist(Rec) then begin
if not Confirm(YouChangedDimQst) then
exit;
repeat
NewDimSetID :=
DimMgt.GetDeltaDimSetID(BankAccReconciliationLine."Dimension Set ID", NewParentDimSetID, OldParentDimSetID);
if BankAccReconciliationLine."Dimension Set ID" <> NewDimSetID then begin
BankAccReconciliationLine."Dimension Set ID" := NewDimSetID;
DimMgt.UpdateGlobalDimFromDimSetID(
BankAccReconciliationLine."Dimension Set ID",
BankAccReconciliationLine."Shortcut Dimension 1 Code",
BankAccReconciliationLine."Shortcut Dimension 2 Code");
OnUpdateAllLineDimOnAfterUpdateGlobalDimFromDimSetID(BankAccReconciliationLine);
BankAccReconciliationLine.Modify();
end;
until BankAccReconciliationLine.Next() = 0;
end;
end;
/// <summary>
/// Creates new payment application batch and opens payment reconciliation worksheet.
/// Prompts user to select bank account and initializes new payment application session.
/// </summary>
procedure OpenNewWorksheet()
var
BankAccount: Record "Bank Account";
BankAccReconciliation: Record "Bank Acc. Reconciliation";
begin
if not SelectBankAccountToUse(BankAccount, false) then
exit;
CreateNewBankPaymentAppBatch(BankAccount."No.", BankAccReconciliation);
OpenWorksheet(BankAccReconciliation);
end;
/// <summary>
/// Imports bank statement file and processes it into new reconciliation with automatic matching.
/// Handles complete workflow from bank account selection through statement import and processing.
/// </summary>
procedure ImportAndProcessToNewStatement()
var
BankAccount: Record "Bank Account";
BankAccReconciliation: Record "Bank Acc. Reconciliation";
DataExch: Record "Data Exch.";
DataExchDef: Record "Data Exch. Def";
DummyBankAccReconciliationLine: Record "Bank Acc. Reconciliation Line";
LastStatementNo: Code[20];
IsHandled: Boolean;
begin
IsHandled := false;
OnBeforeImportAndProcessToNewStatement(BankAccReconciliation, DataExch, DataExchDef, IsHandled);
if IsHandled then
exit;
if not SelectBankAccountToUse(BankAccount, true) then
exit;
BankAccount.GetDataExchDef(DataExchDef);
DataExch."Related Record" := BankAccount.RecordId;
if not DataExch.ImportFileContent(DataExchDef) then
exit;
BankAccount.LockTable();
LastStatementNo := BankAccount."Last Statement No.";
CreateNewBankPaymentAppBatch(BankAccount."No.", BankAccReconciliation);
if not ImportStatement(BankAccReconciliation, DataExch) then begin
DeleteBankAccReconciliation(BankAccReconciliation, BankAccount, LastStatementNo);
Message(NoTransactionsImportedMsg);
exit;
end;
if DummyBankAccReconciliationLine.BankStatementLinesListIsEmpty(BankAccReconciliation."Statement No.", BankAccReconciliation."Statement Type".AsInteger(), BankAccReconciliation."Bank Account No.") then begin
DeleteBankAccReconciliation(BankAccReconciliation, BankAccount, LastStatementNo);
Message(NoTransactionsImportedMsg);
exit;
end;
Commit();
if BankAccount.Get(BankAccReconciliation."Bank Account No.") then
if BankAccount."Disable Automatic Pmt Matching" then
exit;
ProcessStatement(BankAccReconciliation);
end;
local procedure DeleteBankAccReconciliation(var BankAccReconciliation: Record "Bank Acc. Reconciliation"; var BankAccount: Record "Bank Account"; LastStatementNo: Code[20])
begin
BankAccReconciliation.Delete();
BankAccount.Get(BankAccount."No.");
BankAccount."Last Statement No." := LastStatementNo;
BankAccount.Modify();
Commit();
end;
/// <summary>
/// Imports statement data from data exchange record into bank reconciliation lines.
/// Processes electronic bank statement data and creates reconciliation line entries.
/// </summary>
/// <param name="BankAccReconciliation">Bank reconciliation record to populate with imported lines</param>
/// <param name="DataExch">Data exchange record containing bank statement data to import</param>
/// <returns>True if import was successful, false if no transactions were imported</returns>
procedure ImportStatement(var BankAccReconciliation: Record "Bank Acc. Reconciliation"; DataExch: Record "Data Exch."): Boolean
var
ProcessBankAccRecLines: Codeunit "Process Bank Acc. Rec Lines";
begin
exit(ProcessBankAccRecLines.ImportBankStatement(BankAccReconciliation, DataExch))
end;
procedure ProcessStatement(var BankAccReconciliation: Record "Bank Acc. Reconciliation")
begin
CODEUNIT.Run(CODEUNIT::"Match Bank Pmt. Appl.", BankAccReconciliation);
if ConfidenceLevelPermitToPost(BankAccReconciliation) then begin
Commit();
CODEUNIT.Run(CODEUNIT::"Bank Acc. Reconciliation Post", BankAccReconciliation)
end else
OpenWorksheetFromProcessStatement(BankAccReconciliation);
end;
local procedure OpenWorksheetFromProcessStatement(var BankAccReconciliation: Record "Bank Acc. Reconciliation")
var
IsHandled: Boolean;
begin
IsHandled := false;
OnBeforeOpenWorksheetFromProcessStatement(BankAccReconciliation, IsHandled);
if IsHandled then
exit;
if GuiAllowed then
OpenWorksheet(BankAccReconciliation);
end;
procedure CreateNewBankPaymentAppBatch(BankAccountNo: Code[20]; var BankAccReconciliation: Record "Bank Acc. Reconciliation")
begin
BankAccReconciliation.Init();
BankAccReconciliation."Statement Type" := BankAccReconciliation."Statement Type"::"Payment Application";
BankAccReconciliation.Validate("Bank Account No.", BankAccountNo);
BankAccReconciliation.Insert(true);
end;
/// <summary>
/// Prompts user to select a bank account for payment reconciliation operations.
/// Provides filtered selection based on import format configuration and displays appropriate bank account lists.
/// Handles scenarios with single bank accounts, multiple options, and import format requirements.
/// </summary>
/// <param name="BankAccount">Bank account record to populate with the selected account details.</param>
/// <param name="OnlyWithImportFormatSet">If true, only shows bank accounts with configured import formats; otherwise shows all accounts.</param>
/// <returns>True if a bank account was successfully selected; false if user cancelled or no accounts available.</returns>
procedure SelectBankAccountToUse(var BankAccount: Record "Bank Account"; OnlyWithImportFormatSet: Boolean): Boolean
var
TempBankAccount: Record "Bank Account" temporary;
TempLinkedBankAccount: Record "Bank Account" temporary;
NoOfAccounts: Integer;
begin
if OnlyWithImportFormatSet then begin
// copy to temp as we need OR filter
BankAccount.SetFilter("Bank Statement Import Format", '<>%1', '');
CopyBankAccountsToTemp(TempBankAccount, BankAccount);
// clear filters
BankAccount.SetRange("Bank Statement Import Format");
TempLinkedBankAccount.SetRange("Bank Statement Import Format");
BankAccount.GetLinkedBankAccounts(TempLinkedBankAccount);
CopyBankAccountsToTemp(TempBankAccount, TempLinkedBankAccount);
NoOfAccounts := TempBankAccount.Count();
end else
NoOfAccounts := BankAccount.Count();
case NoOfAccounts of
0:
begin
if not BankAccount.Get(CantFindBancAccToUseInPaymentFileImport()) then
exit(false);
exit(true);
end;
1:
if TempBankAccount.Count > 0 then begin
TempBankAccount.FindFirst();
BankAccount.Get(TempBankAccount."No.");
end else
BankAccount.FindFirst();
else begin
if TempBankAccount.Count > 0 then begin
if PAGE.RunModal(PAGE::"Payment Bank Account List", TempBankAccount) = ACTION::LookupOK then begin
BankAccount.Get(TempBankAccount."No.");
exit(true)
end;
exit(false);
end;
exit(PAGE.RunModal(PAGE::"Payment Bank Account List", BankAccount) = ACTION::LookupOK);
end;
end;
exit(true);
end;
procedure OpenWorksheet(BankAccReconciliation: Record "Bank Acc. Reconciliation")
var
BankAccReconciliationLine: Record "Bank Acc. Reconciliation Line";
begin
SetFiltersOnBankAccReconLineTable(BankAccReconciliation, BankAccReconciliationLine);
PAGE.Run(PAGE::"Payment Reconciliation Journal", BankAccReconciliationLine);
end;
procedure OpenList(BankAccReconciliation: Record "Bank Acc. Reconciliation")
var
BankAccReconciliationLine: Record "Bank Acc. Reconciliation Line";
begin
SetFiltersOnBankAccReconLineTable(BankAccReconciliation, BankAccReconciliationLine);
PAGE.Run(PAGE::"Pmt. Recon. Journal Overview", BankAccReconciliationLine);
end;
local procedure CantFindBancAccToUseInPaymentFileImport(): Code[20]
var
BankAccount: Record "Bank Account";
begin
if BankAccount.Count = 0 then
Message(NoBankAccountsMsg)
else
Message(NoBankAccWithFileFormatMsg);
if PAGE.RunModal(PAGE::"Payment Bank Account List", BankAccount) = ACTION::LookupOK then
if (BankAccount."Bank Statement Import Format" <> '') or
BankAccount.IsLinkedToBankStatementServiceProvider()
then
exit(BankAccount."No.");
exit('');
end;
local procedure SetLastPaymentStatementNo(var BankAccount: Record "Bank Account")
var
BankAccReconciliation: Record "Bank Acc. Reconciliation";
begin
if BankAccount."Last Payment Statement No." = '' then begin
BankAccReconciliation.SetRange("Bank Account No.", BankAccount."No.");
BankAccReconciliation.SetRange("Statement Type", "Statement Type"::"Payment Application");
if BankAccReconciliation.FindLast() then
BankAccount."Last Payment Statement No." := IncStr(BankAccReconciliation."Statement No.")
else
BankAccount."Last Payment Statement No." := '0';
BankAccount.Modify();
end;
end;
local procedure SetLastStatementNo(var BankAccount: Record "Bank Account")
begin
if BankAccount."Last Statement No." = '' then begin
BankAccount."Last Statement No." := '0';
BankAccount.Modify();
end;
end;
procedure SetFiltersOnBankAccReconLineTable(BankAccReconciliation: Record "Bank Acc. Reconciliation"; var BankAccReconciliationLine: Record "Bank Acc. Reconciliation Line")
begin
BankAccReconciliationLine.FilterGroup := 2;
BankAccReconciliationLine.SetRange("Statement Type", BankAccReconciliation."Statement Type");
BankAccReconciliationLine.SetRange("Bank Account No.", BankAccReconciliation."Bank Account No.");
BankAccReconciliationLine.SetRange("Statement No.", BankAccReconciliation."Statement No.");
BankAccReconciliationLine.FilterGroup := 0;
end;
local procedure ConfidenceLevelPermitToPost(BankAccReconciliation: Record "Bank Acc. Reconciliation"): Boolean
var
BankAccReconciliationLine: Record "Bank Acc. Reconciliation Line";
begin
SetFiltersOnBankAccReconLineTable(BankAccReconciliation, BankAccReconciliationLine);
if BankAccReconciliationLine.Count = 0 then
exit(false);
BankAccReconciliationLine.SetFilter("Match Confidence", '<>%1', BankAccReconciliationLine."Match Confidence"::High);
if BankAccReconciliationLine.Count <> 0 then
exit(false);
if Confirm(PostHighConfidentLinesQst) then
exit(true);
exit(false);
end;
local procedure LinesExist(): Boolean
var
BankAccReconciliationLine: Record "Bank Acc. Reconciliation Line";
begin
exit(BankAccReconciliationLine.LinesExist(Rec));
end;
local procedure BankAccountCouldBeUsedForImport(): Boolean
var
BankAccount: Record "Bank Account";
begin
BankAccount.Get("Bank Account No.");
if BankAccount."Bank Statement Import Format" <> '' then
exit(true);
if BankAccount.IsLinkedToBankStatementServiceProvider() then
exit(true);
if not Confirm(MustHaveValueQst, true, BankAccount.FieldCaption("Bank Statement Import Format")) then
exit(false);
if PAGE.RunModal(PAGE::"Bank Account Card", BankAccount) = ACTION::LookupOK then
if BankAccount."Bank Statement Import Format" <> '' then
exit(true);
exit(false);
end;
/// <summary>
/// Opens bank account ledger entries page filtered to show open entries for this reconciliation's bank account.
/// Provides drill-down functionality to view detailed ledger entries that contribute to the bank account balance.
/// Used for balance verification and investigation of unreconciled transactions.
/// </summary>
procedure DrillDownOnBalanceOnBankAccount()
var
BankAccountLedgerEntry: Record "Bank Account Ledger Entry";
begin
BankAccountLedgerEntry.SetRange(Open, true);
BankAccountLedgerEntry.SetRange("Bank Account No.", "Bank Account No.");
PAGE.Run(PAGE::"Bank Account Ledger Entries", BankAccountLedgerEntry);
end;
/// <summary>
/// Calculates the optimal filter date for finding matching candidates during bank reconciliation.
/// Determines the earliest transaction date from reconciliation lines to establish the date range
/// for searching potential matches in ledger entries and improving matching performance.
/// </summary>
/// <returns>Date representing the earliest transaction date for candidate filtering; WorkDate if no lines exist.</returns>
procedure MatchCandidateFilterDate(): Date
var
BankAccReconciliationLine: Record "Bank Acc. Reconciliation Line";
begin
BankAccReconciliationLine.SetRange("Statement Type", "Statement Type");
BankAccReconciliationLine.SetRange("Statement No.", "Statement No.");
BankAccReconciliationLine.SetRange("Bank Account No.", "Bank Account No.");
BankAccReconciliationLine.SetCurrentKey("Transaction Date");
BankAccReconciliationLine.Ascending := false;
if BankAccReconciliationLine.FindFirst() then
if BankAccReconciliationLine."Transaction Date" > "Statement Date" then
exit(BankAccReconciliationLine."Transaction Date");
exit("Statement Date");
end;
local procedure CopyBankAccountsToTemp(var TempBankAccount: Record "Bank Account" temporary; var FromBankAccount: Record "Bank Account")
begin
if FromBankAccount.FindSet() then
repeat
TempBankAccount := FromBankAccount;
if TempBankAccount.Insert() then;
until FromBankAccount.Next() = 0;
end;
procedure CreateDimFromDefaultDim()
var
DefaultDimSource: List of [Dictionary of [Integer, Code[20]]];
begin
InitDefaultDimensionSources(DefaultDimSource);
CreateDim(DefaultDimSource);
end;
local procedure InitDefaultDimensionSources(var DefaultDimSource: List of [Dictionary of [Integer, Code[20]]])
begin
DimMgt.AddDimSource(DefaultDimSource, Database::"Bank Account", Rec."Bank Account No.");
OnAfterInitDefaultDimensionSources(Rec, DefaultDimSource);
end;
procedure CreateBankAccountReconcillation()
var
BankAccReconciliation: Record "Bank Acc. Reconciliation";
begin
if not BankAccReconciliation.Get(
Rec."Statement Type",
Rec."Bank Account No.",
Rec."Statement No.") and
(Rec."Bank Account No." <> '') and
(Rec."Statement Type" = Rec."Statement Type"::"Bank Reconciliation")
then
Rec.Insert(true);
end;
[IntegrationEvent(false, false)]
local procedure OnAfterInitDefaultDimensionSources(var BankAccReconciliation: Record "Bank Acc. Reconciliation"; var DefaultDimSource: List of [Dictionary of [Integer, Code[20]]])
begin
end;
[IntegrationEvent(false, false)]
local procedure OnAfterValidateShortcutDimCode(var BankAccReconciliation: Record "Bank Acc. Reconciliation"; var xBankAccReconciliation: Record "Bank Acc. Reconciliation"; FieldNumber: Integer; var ShortcutDimCode: Code[20])
begin
end;
[IntegrationEvent(false, false)]
local procedure OnBeforeImportAndProcessToNewStatement(var BankAccReconciliation: Record "Bank Acc. Reconciliation"; var DataExch: Record "Data Exch."; var DataExchDef: Record "Data Exch. Def"; var IsHandled: Boolean)
begin
end;
[IntegrationEvent(false, false)]
local procedure OnBeforeMatchSingle(var BankAccReconciliation: Record "Bank Acc. Reconciliation"; DateRange: Integer; var IsHandled: Boolean)
begin
end;
[IntegrationEvent(false, false)]
local procedure OnBeforeOpenWorksheetFromProcessStatement(var BankAccReconciliation: Record "Bank Acc. Reconciliation"; var IsHandled: Boolean)
begin
end;
[IntegrationEvent(false, false)]
local procedure OnBeforeValidateShortcutDimCode(var BankAccReconciliation: Record "Bank Acc. Reconciliation"; var xBankAccReconciliation: Record "Bank Acc. Reconciliation"; FieldNumber: Integer; var ShortcutDimCode: Code[20])
begin
end;
[IntegrationEvent(false, false)]
local procedure OnCreateDimOnAfterSetDimensionSetID(var BankAccReconciliation: Record "Bank Acc. Reconciliation"; OldDimSetID: Integer; DefaultDimSource: List of [Dictionary of [Integer, Code[20]]])
begin
end;
[IntegrationEvent(false, false)]
local procedure OnUpdateAllLineDimOnAfterUpdateGlobalDimFromDimSetID(var BankAccReconciliationLine: Record "Bank Acc. Reconciliation Line")
begin
end;
}