Report 33 Reconcile Cust. and Vend. Accs
- App
- Base Application
- Namespace
- Microsoft.Finance.GeneralLedger.Reports
- Versions
- 17-28
Versions171819202122232425262728
Source29
Source in 29
src/Layers/W1/BaseApp/Finance/GeneralLedger/Reports/ReconcileCustandVendAccs.Report.al724 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.Finance.GeneralLedger.Reports;
using Microsoft.Finance.Currency;
using Microsoft.Finance.GeneralLedger.Account;
using Microsoft.Finance.ReceivablesPayables;
using Microsoft.Purchases.Payables;
using Microsoft.Purchases.Vendor;
using Microsoft.Sales.Customer;
using Microsoft.Sales.Receivables;
using System.Globalization;
using System.Utilities;
/// <summary>
/// Reconciles customer and vendor account balances with corresponding G/L account balances for audit and validation purposes.
/// Provides detailed analysis of posting group account mappings and identifies balance discrepancies between subsidiary and general ledgers.
/// </summary>
/// <remarks>
/// Data sources: G/L Account, Customer/Vendor Posting Groups, Currency, and CV Ledger Entry tables.
/// Analyzes receivables accounts, payables accounts, payment discount accounts, tolerance accounts, and rounding accounts.
/// Critical for month-end reconciliation procedures, audit preparation, and maintaining subsidiary ledger integrity.
/// </remarks>
report 33 "Reconcile Cust. and Vend. Accs"
{
ApplicationArea = Basic, Suite;
Caption = 'Reconcile Customer and Vendor Accounts';
UsageCategory = ReportsAndAnalysis;
DefaultRenderingLayout = RDLCLayout;
dataset
{
dataitem("G/L Account"; "G/L Account")
{
DataItemTableView = sorting("No.");
PrintOnlyIfDetail = true;
RequestFilterFields = "Date Filter";
column(CompanyName; COMPANYPROPERTY.DisplayName())
{
}
column(GLAccountTableCaption; TableCaption + ': ' + GLFilter)
{
}
column(GLFilter; GLFilter)
{
}
column(No_GLAccount; "No.")
{
}
column(Name_GLAccount; Name)
{
}
column(ReconcileCustVendAccCaption; ReconcileCustVendAccCaptionLbl)
{
}
column(PageNoCaption; PageNoCaptionLbl)
{
}
column(NetChange_GLAccountCaption; NetChange_GLAccountCaptionLbl)
{
}
column(IndirectlyPostedAmtCaption; IndirectlyPostedAmtCaptionLbl)
{
}
column(PostingGrpCaption; PostingGrpCaptionLbl)
{
}
column(CurrCodeCaption; CurrCodeCaptionLbl)
{
}
column(TypeCaption; TypeCaptionLbl)
{
}
column(NameCaption; NameCaptionLbl)
{
}
column(No_GLAccountCaption; No_GLAccountCaptionLbl)
{
}
column(DifferenceCaption; DifferenceCaptionLbl)
{
}
dataitem("Integer"; "Integer")
{
DataItemTableView = sorting(Number) where(Number = filter(1 ..));
column(ReconCustVendBufferCurrcode; ReconCustVendBuffer."Currency code")
{
}
column(Amount; Amount)
{
AutoFormatType = 1;
}
column(AccountType; AccountType)
{
}
column(GetTableName; GetTableName())
{
}
column(ReconCustVendBufferPostingGroup; ReconCustVendBuffer."Posting Group")
{
}
column(NetChange_GLAccount; "G/L Account"."Net Change")
{
}
trigger OnAfterGetRecord()
var
Currency: Record Currency;
CustPostingGr: Record "Customer Posting Group";
VendPostingGr: Record "Vendor Posting Group";
DtldCustLedgEntry: Record "Detailed Cust. Ledg. Entry";
DtldVendLedgEntry: Record "Detailed Vendor Ledg. Entry";
Found: Boolean;
begin
AmountTotal := AmountTotal + Amount;
Amount := 0;
Found := false;
if Number = 1 then
Found := ReconCustVendBuffer.Find('-')
else
Found := ReconCustVendBuffer.Next() <> 0;
if not Found then
CurrReport.Break();
case true of
(ReconCustVendBuffer."Table ID" = DATABASE::"Customer Posting Group") and
(ReconCustVendBuffer."Field No." = CustPostingGr.FieldNo("Receivables Account")):
begin
AccountType := CustPostingGr.FieldCaption("Receivables Account");
Amount := CalcCustAccAmount(ReconCustVendBuffer."Posting Group");
end;
(ReconCustVendBuffer."Table ID" = DATABASE::"Customer Posting Group") and
(ReconCustVendBuffer."Field No." = CustPostingGr.FieldNo("Payment Disc. Debit Acc.")):
begin
AccountType := CustPostingGr.FieldCaption("Payment Disc. Debit Acc.");
Amount :=
CalcCustCreditAmount(ReconCustVendBuffer."Posting Group", DtldCustLedgEntry."Entry Type"::"Payment Discount");
end;
(ReconCustVendBuffer."Table ID" = DATABASE::"Customer Posting Group") and
(ReconCustVendBuffer."Field No." = CustPostingGr.FieldNo("Payment Disc. Credit Acc.")):
begin
AccountType := CustPostingGr.FieldCaption("Payment Disc. Credit Acc.");
Amount :=
CalcCustDebitAmount(ReconCustVendBuffer."Posting Group", DtldCustLedgEntry."Entry Type"::"Payment Discount");
end;
(ReconCustVendBuffer."Table ID" = DATABASE::"Customer Posting Group") and
(ReconCustVendBuffer."Field No." = CustPostingGr.FieldNo("Payment Tolerance Debit Acc.")):
begin
AccountType := CustPostingGr.FieldCaption("Payment Tolerance Debit Acc.");
Amount :=
CalcCustCreditAmount(ReconCustVendBuffer."Posting Group", DtldCustLedgEntry."Entry Type"::"Payment Tolerance") +
CalcCustCreditAmount(ReconCustVendBuffer."Posting Group", DtldCustLedgEntry."Entry Type"::"Payment Discount Tolerance");
end;
(ReconCustVendBuffer."Table ID" = DATABASE::"Customer Posting Group") and
(ReconCustVendBuffer."Field No." = CustPostingGr.FieldNo("Payment Tolerance Credit Acc.")):
begin
AccountType := CustPostingGr.FieldCaption("Payment Tolerance Credit Acc.");
Amount :=
CalcCustDebitAmount(ReconCustVendBuffer."Posting Group", DtldCustLedgEntry."Entry Type"::"Payment Tolerance") +
CalcCustDebitAmount(ReconCustVendBuffer."Posting Group", DtldCustLedgEntry."Entry Type"::"Payment Discount Tolerance");
end;
(ReconCustVendBuffer."Table ID" = DATABASE::"Customer Posting Group") and
(ReconCustVendBuffer."Field No." = CustPostingGr.FieldNo("Debit Curr. Appln. Rndg. Acc.")):
begin
AccountType := CustPostingGr.FieldCaption("Debit Curr. Appln. Rndg. Acc.");
Amount :=
CalcCustCreditAmount(ReconCustVendBuffer."Posting Group", DtldCustLedgEntry."Entry Type"::"Appln. Rounding");
end;
(ReconCustVendBuffer."Table ID" = DATABASE::"Customer Posting Group") and
(ReconCustVendBuffer."Field No." = CustPostingGr.FieldNo("Credit Curr. Appln. Rndg. Acc.")):
begin
AccountType := CustPostingGr.FieldCaption("Credit Curr. Appln. Rndg. Acc.");
Amount :=
CalcCustDebitAmount(ReconCustVendBuffer."Posting Group", DtldCustLedgEntry."Entry Type"::"Appln. Rounding");
end;
(ReconCustVendBuffer."Table ID" = DATABASE::"Customer Posting Group") and
(ReconCustVendBuffer."Field No." = CustPostingGr.FieldNo("Debit Rounding Account")):
begin
AccountType := CustPostingGr.FieldCaption("Debit Rounding Account");
Amount :=
CalcCustCreditAmount(
ReconCustVendBuffer."Posting Group", DtldCustLedgEntry."Entry Type"::"Correction of Remaining Amount");
end;
(ReconCustVendBuffer."Table ID" = DATABASE::"Customer Posting Group") and
(ReconCustVendBuffer."Field No." = CustPostingGr.FieldNo("Credit Rounding Account")):
begin
AccountType := CustPostingGr.FieldCaption("Credit Rounding Account");
Amount :=
CalcCustDebitAmount(
ReconCustVendBuffer."Posting Group", DtldCustLedgEntry."Entry Type"::"Correction of Remaining Amount");
end;
(ReconCustVendBuffer."Table ID" = DATABASE::"Vendor Posting Group") and
(ReconCustVendBuffer."Field No." = VendPostingGr.FieldNo("Payables Account")):
begin
AccountType := VendPostingGr.FieldCaption("Payables Account");
Amount := CalcVendAccAmount(ReconCustVendBuffer."Posting Group");
end;
(ReconCustVendBuffer."Table ID" = DATABASE::"Vendor Posting Group") and
(ReconCustVendBuffer."Field No." = VendPostingGr.FieldNo("Payment Disc. Debit Acc.")):
begin
AccountType := VendPostingGr.FieldCaption("Payment Disc. Debit Acc.");
Amount :=
CalcVendCreditAmount(ReconCustVendBuffer."Posting Group", DtldVendLedgEntry."Entry Type"::"Payment Discount");
end;
(ReconCustVendBuffer."Table ID" = DATABASE::"Vendor Posting Group") and
(ReconCustVendBuffer."Field No." = VendPostingGr.FieldNo("Payment Disc. Credit Acc.")):
begin
AccountType := VendPostingGr.FieldCaption("Payment Disc. Credit Acc.");
Amount :=
CalcVendDebitAmount(ReconCustVendBuffer."Posting Group", DtldVendLedgEntry."Entry Type"::"Payment Discount");
end;
(ReconCustVendBuffer."Table ID" = DATABASE::"Vendor Posting Group") and
(ReconCustVendBuffer."Field No." = VendPostingGr.FieldNo("Payment Tolerance Debit Acc.")):
begin
AccountType := VendPostingGr.FieldCaption("Payment Tolerance Debit Acc.");
Amount :=
CalcVendDebitAmount(ReconCustVendBuffer."Posting Group", DtldVendLedgEntry."Entry Type"::"Payment Tolerance") +
CalcVendDebitAmount(ReconCustVendBuffer."Posting Group", DtldVendLedgEntry."Entry Type"::"Payment Discount Tolerance");
end;
(ReconCustVendBuffer."Table ID" = DATABASE::"Vendor Posting Group") and
(ReconCustVendBuffer."Field No." = VendPostingGr.FieldNo("Payment Tolerance Credit Acc.")):
begin
AccountType := VendPostingGr.FieldCaption("Payment Tolerance Credit Acc.");
Amount :=
CalcVendCreditAmount(ReconCustVendBuffer."Posting Group", DtldVendLedgEntry."Entry Type"::"Payment Tolerance") +
CalcVendCreditAmount(ReconCustVendBuffer."Posting Group", DtldVendLedgEntry."Entry Type"::"Payment Discount Tolerance");
end;
(ReconCustVendBuffer."Table ID" = DATABASE::"Vendor Posting Group") and
(ReconCustVendBuffer."Field No." = VendPostingGr.FieldNo("Debit Curr. Appln. Rndg. Acc.")):
begin
AccountType := VendPostingGr.FieldCaption("Debit Curr. Appln. Rndg. Acc.");
Amount :=
CalcVendCreditAmount(ReconCustVendBuffer."Posting Group", DtldVendLedgEntry."Entry Type"::"Appln. Rounding");
end;
(ReconCustVendBuffer."Table ID" = DATABASE::"Vendor Posting Group") and
(ReconCustVendBuffer."Field No." = VendPostingGr.FieldNo("Credit Curr. Appln. Rndg. Acc.")):
begin
AccountType := VendPostingGr.FieldCaption("Credit Curr. Appln. Rndg. Acc.");
Amount :=
CalcVendDebitAmount(ReconCustVendBuffer."Posting Group", DtldVendLedgEntry."Entry Type"::"Appln. Rounding");
end;
(ReconCustVendBuffer."Table ID" = DATABASE::"Vendor Posting Group") and
(ReconCustVendBuffer."Field No." = VendPostingGr.FieldNo("Debit Rounding Account")):
begin
AccountType := VendPostingGr.FieldCaption("Debit Rounding Account");
Amount :=
CalcVendCreditAmount(
ReconCustVendBuffer."Posting Group", DtldVendLedgEntry."Entry Type"::"Correction of Remaining Amount");
end;
(ReconCustVendBuffer."Table ID" = DATABASE::"Vendor Posting Group") and
(ReconCustVendBuffer."Field No." = VendPostingGr.FieldNo("Credit Rounding Account")):
begin
AccountType := VendPostingGr.FieldCaption("Credit Rounding Account");
Amount :=
CalcVendDebitAmount(
ReconCustVendBuffer."Posting Group", DtldVendLedgEntry."Entry Type"::"Correction of Remaining Amount");
end;
(ReconCustVendBuffer."Table ID" = DATABASE::Currency) and
(ReconCustVendBuffer."Field No." = Currency.FieldNo("Unrealized Gains Acc.")):
begin
AccountType := Currency.FieldCaption("Unrealized Gains Acc.");
Amount :=
CalcCurrGainLossAmount(ReconCustVendBuffer."Currency code", DtldVendLedgEntry."Entry Type"::"Unrealized Gain");
end;
(ReconCustVendBuffer."Table ID" = DATABASE::Currency) and
(ReconCustVendBuffer."Field No." = Currency.FieldNo("Realized Gains Acc.")):
begin
AccountType := Currency.FieldCaption("Realized Gains Acc.");
Amount :=
CalcCurrGainLossAmount(ReconCustVendBuffer."Currency code", DtldVendLedgEntry."Entry Type"::"Realized Gain");
end;
(ReconCustVendBuffer."Table ID" = DATABASE::Currency) and
(ReconCustVendBuffer."Field No." = Currency.FieldNo("Unrealized Losses Acc.")):
begin
AccountType := Currency.FieldCaption("Unrealized Losses Acc.");
Amount :=
CalcCurrGainLossAmount(ReconCustVendBuffer."Currency code", DtldVendLedgEntry."Entry Type"::"Unrealized Loss");
end;
(ReconCustVendBuffer."Table ID" = DATABASE::Currency) and
(ReconCustVendBuffer."Field No." = Currency.FieldNo("Realized Losses Acc.")):
begin
AccountType := Currency.FieldCaption("Realized Losses Acc.");
Amount :=
CalcCurrGainLossAmount(ReconCustVendBuffer."Currency code", DtldVendLedgEntry."Entry Type"::"Realized Loss");
end;
end;
end;
trigger OnPreDataItem()
begin
ReconCustVendBuffer.SetCurrentKey("G/L Account No.");
ReconCustVendBuffer.SetRange("G/L Account No.", "G/L Account"."No.");
end;
}
trigger OnAfterGetRecord()
begin
AmountTotal := 0;
CalcFields("Net Change")
end;
trigger OnPreDataItem()
var
Currency: Record Currency;
CustPostingGr: Record "Customer Posting Group";
VendPostingGr: Record "Vendor Posting Group";
begin
if CustPostingGr.Find('-') then begin
Clear(ReconCustVendBuffer);
repeat
ReconCustVendBuffer."Table ID" := DATABASE::"Customer Posting Group";
ReconCustVendBuffer."Posting Group" := CustPostingGr.Code;
ReconCustVendBuffer."Field No." := CustPostingGr.FieldNo("Receivables Account");
ReconCustVendBuffer."G/L Account No." := CustPostingGr."Receivables Account";
ReconCustVendBuffer.Insert();
ReconCustVendBuffer."Field No." := CustPostingGr.FieldNo("Payment Disc. Debit Acc.");
ReconCustVendBuffer."G/L Account No." := CustPostingGr."Payment Disc. Debit Acc.";
ReconCustVendBuffer.Insert();
ReconCustVendBuffer."Field No." := CustPostingGr.FieldNo("Payment Disc. Credit Acc.");
ReconCustVendBuffer."G/L Account No." := CustPostingGr."Payment Disc. Credit Acc.";
ReconCustVendBuffer.Insert();
ReconCustVendBuffer."Field No." := CustPostingGr.FieldNo("Payment Tolerance Debit Acc.");
ReconCustVendBuffer."G/L Account No." := CustPostingGr."Payment Tolerance Debit Acc.";
ReconCustVendBuffer.Insert();
ReconCustVendBuffer."Field No." := CustPostingGr.FieldNo("Payment Tolerance Credit Acc.");
ReconCustVendBuffer."G/L Account No." := CustPostingGr."Payment Tolerance Credit Acc.";
ReconCustVendBuffer.Insert();
ReconCustVendBuffer."Field No." := CustPostingGr.FieldNo("Debit Curr. Appln. Rndg. Acc.");
ReconCustVendBuffer."G/L Account No." := CustPostingGr."Debit Curr. Appln. Rndg. Acc.";
ReconCustVendBuffer.Insert();
ReconCustVendBuffer."Field No." := CustPostingGr.FieldNo("Credit Curr. Appln. Rndg. Acc.");
ReconCustVendBuffer."G/L Account No." := CustPostingGr."Credit Curr. Appln. Rndg. Acc.";
ReconCustVendBuffer.Insert();
ReconCustVendBuffer."Field No." := CustPostingGr.FieldNo("Debit Rounding Account");
ReconCustVendBuffer."G/L Account No." := CustPostingGr."Debit Rounding Account";
ReconCustVendBuffer.Insert();
ReconCustVendBuffer."Field No." := CustPostingGr.FieldNo("Credit Rounding Account");
ReconCustVendBuffer."G/L Account No." := CustPostingGr."Credit Rounding Account";
ReconCustVendBuffer.Insert();
until CustPostingGr.Next() = 0;
end;
if VendPostingGr.Find('-') then begin
Clear(ReconCustVendBuffer);
repeat
ReconCustVendBuffer."Table ID" := DATABASE::"Vendor Posting Group";
ReconCustVendBuffer."Posting Group" := VendPostingGr.Code;
ReconCustVendBuffer."Field No." := VendPostingGr.FieldNo("Payables Account");
ReconCustVendBuffer."G/L Account No." := VendPostingGr."Payables Account";
ReconCustVendBuffer.Insert();
ReconCustVendBuffer."Field No." := VendPostingGr.FieldNo("Payment Disc. Debit Acc.");
ReconCustVendBuffer."G/L Account No." := VendPostingGr."Payment Disc. Debit Acc.";
ReconCustVendBuffer.Insert();
ReconCustVendBuffer."Field No." := VendPostingGr.FieldNo("Payment Disc. Credit Acc.");
ReconCustVendBuffer."G/L Account No." := VendPostingGr."Payment Disc. Credit Acc.";
ReconCustVendBuffer.Insert();
ReconCustVendBuffer."Field No." := VendPostingGr.FieldNo("Payment Tolerance Debit Acc.");
ReconCustVendBuffer."G/L Account No." := VendPostingGr."Payment Tolerance Debit Acc.";
ReconCustVendBuffer.Insert();
ReconCustVendBuffer."Field No." := VendPostingGr.FieldNo("Payment Tolerance Credit Acc.");
ReconCustVendBuffer."G/L Account No." := VendPostingGr."Payment Tolerance Credit Acc.";
ReconCustVendBuffer.Insert();
ReconCustVendBuffer."Field No." := VendPostingGr.FieldNo("Debit Curr. Appln. Rndg. Acc.");
ReconCustVendBuffer."G/L Account No." := VendPostingGr."Debit Curr. Appln. Rndg. Acc.";
ReconCustVendBuffer.Insert();
ReconCustVendBuffer."Field No." := VendPostingGr.FieldNo("Credit Curr. Appln. Rndg. Acc.");
ReconCustVendBuffer."G/L Account No." := VendPostingGr."Credit Curr. Appln. Rndg. Acc.";
ReconCustVendBuffer.Insert();
ReconCustVendBuffer."Field No." := VendPostingGr.FieldNo("Debit Rounding Account");
ReconCustVendBuffer."G/L Account No." := VendPostingGr."Debit Rounding Account";
ReconCustVendBuffer.Insert();
ReconCustVendBuffer."Field No." := VendPostingGr.FieldNo("Credit Rounding Account");
ReconCustVendBuffer."G/L Account No." := VendPostingGr."Credit Rounding Account";
ReconCustVendBuffer.Insert();
until VendPostingGr.Next() = 0;
end;
if Currency.Find('-') then begin
Clear(ReconCustVendBuffer);
repeat
ReconCustVendBuffer."Table ID" := DATABASE::Currency;
ReconCustVendBuffer."Currency code" := Currency.Code;
ReconCustVendBuffer."Field No." := Currency.FieldNo("Unrealized Gains Acc.");
ReconCustVendBuffer."G/L Account No." := Currency."Unrealized Gains Acc.";
ReconCustVendBuffer.Insert();
ReconCustVendBuffer."Field No." := Currency.FieldNo("Realized Gains Acc.");
ReconCustVendBuffer."G/L Account No." := Currency."Realized Gains Acc.";
ReconCustVendBuffer.Insert();
ReconCustVendBuffer."Field No." := Currency.FieldNo("Unrealized Losses Acc.");
ReconCustVendBuffer."G/L Account No." := Currency."Unrealized Losses Acc.";
ReconCustVendBuffer.Insert();
ReconCustVendBuffer."Field No." := Currency.FieldNo("Realized Losses Acc.");
ReconCustVendBuffer."G/L Account No." := Currency."Realized Losses Acc.";
ReconCustVendBuffer.Insert();
until Currency.Next() = 0;
end;
if ReconCustVendBuffer.Find('-') then begin
repeat
"No." := ReconCustVendBuffer."G/L Account No.";
Mark(true);
until ReconCustVendBuffer.Next() = 0;
MarkedOnly(true);
end else
CurrReport.Break();
end;
}
}
requestpage
{
AboutTitle = 'About Reconcile Customer and Vendor Accounts';
AboutText = 'Understand the difference in net change to control the G/L accounts setup on customer and vendor posting group tables. Highlight discrepancies between G/L and customer/vendor ledger balances.';
layout
{
}
actions
{
}
}
rendering
{
layout(RDLCLayout)
{
Type = RDLC;
LayoutFile = './Finance/GeneralLedger/Reports/ReconcileCustandVendAccs.rdlc';
Summary = 'Report layout made in the legacy RDLC format. Use an RDLC editor to modify the layout.';
}
}
labels
{
}
trigger OnPreReport()
begin
GLFilter := "G/L Account".GetFilters();
end;
var
GLFilter: Text;
ReconcileCustVendAccCaptionLbl: Label 'Reconcile Customer and Vendor Accounts';
PageNoCaptionLbl: Label 'Page';
NetChange_GLAccountCaptionLbl: Label 'G/L Account Net Change';
IndirectlyPostedAmtCaptionLbl: Label 'Indirectly Posted Amount';
PostingGrpCaptionLbl: Label 'Posting Group';
CurrCodeCaptionLbl: Label 'Currency Code';
TypeCaptionLbl: Label 'Type';
NameCaptionLbl: Label 'Name';
No_GLAccountCaptionLbl: Label 'Account No.';
DifferenceCaptionLbl: Label 'Difference';
protected var
ReconCustVendBuffer: Record "Reconcile CV Acc Buffer" temporary;
Amount: Decimal;
AmountTotal: Decimal;
AccountType: Text[1024];
var
CustBlankPGCachedGroups: Dictionary of [Code[20], Decimal];
CustBlankPGCreditCache: Dictionary of [Text, Decimal];
CustBlankPGDebitCache: Dictionary of [Text, Decimal];
VendBlankPGCachedGroups: Dictionary of [Code[20], Decimal];
VendBlankPGCreditCache: Dictionary of [Text, Decimal];
VendBlankPGDebitCache: Dictionary of [Text, Decimal];
local procedure EnsureCustBlankPGCache(PostingGr: Code[20])
var
ReconCustPostingGrSum: Query "Recon. Cust. Posting Gr. Sum";
EntryTypeInt: Integer;
CacheKey: Text;
TotalAmount: Decimal;
begin
if CustBlankPGCachedGroups.ContainsKey(PostingGr) then
exit;
TotalAmount := 0;
ReconCustPostingGrSum.SetRange(CustomerPostingGroup, PostingGr);
ReconCustPostingGrSum.SetRange(PostingGroup, '');
ReconCustPostingGrSum.SetFilter(PostingDate, "G/L Account".GetFilter("Date Filter"));
ReconCustPostingGrSum.Open();
while ReconCustPostingGrSum.Read() do begin
EntryTypeInt := ReconCustPostingGrSum.EntryType.AsInteger();
CacheKey := PostingGr + '|' + Format(EntryTypeInt);
CustBlankPGCreditCache.Set(CacheKey, ReconCustPostingGrSum.SumCreditAmountLCY);
CustBlankPGDebitCache.Set(CacheKey, ReconCustPostingGrSum.SumDebitAmountLCY);
TotalAmount += ReconCustPostingGrSum.SumAmountLCY;
end;
ReconCustPostingGrSum.Close();
CustBlankPGCachedGroups.Set(PostingGr, TotalAmount);
end;
local procedure EnsureVendBlankPGCache(PostingGr: Code[20])
var
ReconVendPostingGrSum: Query "Recon. Vend. Posting Gr. Sum";
EntryTypeInt: Integer;
CacheKey: Text;
TotalAmount: Decimal;
begin
if VendBlankPGCachedGroups.ContainsKey(PostingGr) then
exit;
TotalAmount := 0;
ReconVendPostingGrSum.SetRange(VendorPostingGroup, PostingGr);
ReconVendPostingGrSum.SetRange(PostingGroup, '');
ReconVendPostingGrSum.SetFilter(PostingDate, "G/L Account".GetFilter("Date Filter"));
ReconVendPostingGrSum.Open();
while ReconVendPostingGrSum.Read() do begin
EntryTypeInt := ReconVendPostingGrSum.EntryType.AsInteger();
CacheKey := PostingGr + '|' + Format(EntryTypeInt);
VendBlankPGCreditCache.Set(CacheKey, ReconVendPostingGrSum.SumCreditAmountLCY);
VendBlankPGDebitCache.Set(CacheKey, ReconVendPostingGrSum.SumDebitAmountLCY);
TotalAmount += ReconVendPostingGrSum.SumAmountLCY;
end;
ReconVendPostingGrSum.Close();
VendBlankPGCachedGroups.Set(PostingGr, TotalAmount);
end;
local procedure CalcCustAccAmount(PostingGr: Code[20]): Decimal
var
DtldCustLedgEntry: Record "Detailed Cust. Ledg. Entry";
CustAccAmount: Decimal;
CachedAmount: Decimal;
begin
// Pass 1: detail entries explicitly stamped with this posting group (multi-posting-group customers).
DtldCustLedgEntry.SetLoadFields("Amount (LCY)");
DtldCustLedgEntry.SetRange("Posting Group", PostingGr);
"G/L Account".CopyFilter("Date Filter", DtldCustLedgEntry."Posting Date");
DtldCustLedgEntry.CalcSums("Amount (LCY)");
CustAccAmount := DtldCustLedgEntry."Amount (LCY)";
// Pass 2: detail entries with blank Posting Group attributed to the customer's current master
// posting group. Uses multi-entry cached query result to avoid repeated SQL round-trips.
EnsureCustBlankPGCache(PostingGr);
if CustBlankPGCachedGroups.Get(PostingGr, CachedAmount) then
CustAccAmount += CachedAmount;
exit(CustAccAmount);
end;
local procedure CalcCustCreditAmount(PostingGr: Code[20]; EntryType: Enum "Detailed CV Ledger Entry Type"): Decimal
var
DtldCustLedgEntry: Record "Detailed Cust. Ledg. Entry";
CustCreditAmount: Decimal;
CachedAmount: Decimal;
begin
// Pass 1: detail entries explicitly stamped with this posting group (multi-posting-group customers).
DtldCustLedgEntry.SetLoadFields("Credit Amount (LCY)");
DtldCustLedgEntry.SetRange("Posting Group", PostingGr);
DtldCustLedgEntry.SetRange("Entry Type", EntryType);
"G/L Account".CopyFilter("Date Filter", DtldCustLedgEntry."Posting Date");
DtldCustLedgEntry.CalcSums("Credit Amount (LCY)");
CustCreditAmount := DtldCustLedgEntry."Credit Amount (LCY)";
// Pass 2: detail entries with blank Posting Group attributed to the customer's current master
// posting group. Uses multi-entry cached query result to avoid repeated SQL round-trips.
EnsureCustBlankPGCache(PostingGr);
if CustBlankPGCreditCache.Get(PostingGr + '|' + Format(EntryType.AsInteger()), CachedAmount) then
CustCreditAmount += CachedAmount;
exit(CustCreditAmount);
end;
local procedure CalcCustDebitAmount(PostingGr: Code[20]; EntryType: Enum "Detailed CV Ledger Entry Type"): Decimal
var
DtldCustLedgEntry: Record "Detailed Cust. Ledg. Entry";
CustDebitAmount: Decimal;
CachedAmount: Decimal;
begin
// Pass 1: detail entries explicitly stamped with this posting group (multi-posting-group customers).
DtldCustLedgEntry.SetLoadFields("Debit Amount (LCY)");
DtldCustLedgEntry.SetRange("Posting Group", PostingGr);
DtldCustLedgEntry.SetRange("Entry Type", EntryType);
"G/L Account".CopyFilter("Date Filter", DtldCustLedgEntry."Posting Date");
DtldCustLedgEntry.CalcSums("Debit Amount (LCY)");
CustDebitAmount := DtldCustLedgEntry."Debit Amount (LCY)";
// Pass 2: detail entries with blank Posting Group attributed to the customer's current master
// posting group. Uses multi-entry cached query result to avoid repeated SQL round-trips.
EnsureCustBlankPGCache(PostingGr);
if CustBlankPGDebitCache.Get(PostingGr + '|' + Format(EntryType.AsInteger()), CachedAmount) then
CustDebitAmount += CachedAmount;
exit(-CustDebitAmount);
end;
local procedure CalcVendAccAmount(PostingGr: Code[20]): Decimal
var
DtldVendLedgEntry: Record "Detailed Vendor Ledg. Entry";
VendAccAmount: Decimal;
CachedAmount: Decimal;
begin
// Pass 1: detail entries explicitly stamped with this posting group (multi-posting-group vendors).
DtldVendLedgEntry.SetLoadFields("Amount (LCY)");
DtldVendLedgEntry.SetRange("Posting Group", PostingGr);
"G/L Account".CopyFilter("Date Filter", DtldVendLedgEntry."Posting Date");
DtldVendLedgEntry.CalcSums("Amount (LCY)");
VendAccAmount := DtldVendLedgEntry."Amount (LCY)";
// Pass 2: detail entries with blank Posting Group attributed to the vendor's current master
// posting group. Uses multi-entry cached query result to avoid repeated SQL round-trips.
EnsureVendBlankPGCache(PostingGr);
if VendBlankPGCachedGroups.Get(PostingGr, CachedAmount) then
VendAccAmount += CachedAmount;
exit(VendAccAmount);
end;
local procedure CalcVendCreditAmount(PostingGr: Code[20]; EntryType: Enum "Detailed CV Ledger Entry Type"): Decimal
var
DtldVendLedgEntry: Record "Detailed Vendor Ledg. Entry";
VendCreditAmount: Decimal;
CachedAmount: Decimal;
begin
// Pass 1: detail entries explicitly stamped with this posting group (multi-posting-group vendors).
DtldVendLedgEntry.SetLoadFields("Credit Amount (LCY)");
DtldVendLedgEntry.SetRange("Posting Group", PostingGr);
DtldVendLedgEntry.SetRange("Entry Type", EntryType);
"G/L Account".CopyFilter("Date Filter", DtldVendLedgEntry."Posting Date");
DtldVendLedgEntry.CalcSums("Credit Amount (LCY)");
VendCreditAmount := DtldVendLedgEntry."Credit Amount (LCY)";
// Pass 2: detail entries with blank Posting Group attributed to the vendor's current master
// posting group. Uses multi-entry cached query result to avoid repeated SQL round-trips.
EnsureVendBlankPGCache(PostingGr);
if VendBlankPGCreditCache.Get(PostingGr + '|' + Format(EntryType.AsInteger()), CachedAmount) then
VendCreditAmount += CachedAmount;
exit(VendCreditAmount);
end;
local procedure CalcVendDebitAmount(PostingGr: Code[20]; EntryType: Enum "Detailed CV Ledger Entry Type"): Decimal
var
DtldVendLedgEntry: Record "Detailed Vendor Ledg. Entry";
VendDebitAmount: Decimal;
CachedAmount: Decimal;
begin
// Pass 1: detail entries explicitly stamped with this posting group (multi-posting-group vendors).
DtldVendLedgEntry.SetLoadFields("Debit Amount (LCY)");
DtldVendLedgEntry.SetRange("Posting Group", PostingGr);
DtldVendLedgEntry.SetRange("Entry Type", EntryType);
"G/L Account".CopyFilter("Date Filter", DtldVendLedgEntry."Posting Date");
DtldVendLedgEntry.CalcSums("Debit Amount (LCY)");
VendDebitAmount := DtldVendLedgEntry."Debit Amount (LCY)";
// Pass 2: detail entries with blank Posting Group attributed to the vendor's current master
// posting group. Uses multi-entry cached query result to avoid repeated SQL round-trips.
EnsureVendBlankPGCache(PostingGr);
if VendBlankPGDebitCache.Get(PostingGr + '|' + Format(EntryType.AsInteger()), CachedAmount) then
VendDebitAmount += CachedAmount;
exit(-VendDebitAmount);
end;
local procedure CalcCurrGainLossAmount(CurrencyCode: Code[20]; EntryType: Enum "Detailed CV Ledger Entry Type"): Decimal
var
DtldCustLedgEntry: Record "Detailed Cust. Ledg. Entry";
DtldVendLedgEntry: Record "Detailed Vendor Ledg. Entry";
CurrGainLossAmount: Decimal;
begin
DtldCustLedgEntry.SetCurrentKey("Customer No.", "Posting Date", "Entry Type", "Currency Code");
DtldCustLedgEntry.SetFilter("Customer No.", '<> %1', '');
DtldCustLedgEntry.SetRange("Entry Type", EntryType);
DtldCustLedgEntry.SetRange("Currency Code", CurrencyCode);
"G/L Account".CopyFilter("Date Filter", DtldCustLedgEntry."Posting Date");
DtldCustLedgEntry.CalcSums("Amount (LCY)");
CurrGainLossAmount := CurrGainLossAmount + DtldCustLedgEntry."Amount (LCY)";
DtldVendLedgEntry.SetCurrentKey("Vendor No.", "Posting Date", "Entry Type", "Currency Code");
DtldVendLedgEntry.SetFilter("Vendor No.", '<> %1', '');
DtldVendLedgEntry.SetRange("Entry Type", EntryType);
DtldVendLedgEntry.SetRange("Currency Code", CurrencyCode);
"G/L Account".CopyFilter("Date Filter", DtldVendLedgEntry."Posting Date");
DtldVendLedgEntry.CalcSums("Amount (LCY)");
CurrGainLossAmount := CurrGainLossAmount + DtldVendLedgEntry."Amount (LCY)";
exit(-CurrGainLossAmount);
end;
local procedure GetTableName(): Text[100]
var
ObjTransl: Record "Object Translation";
begin
exit(ObjTransl.TranslateObject(ObjTransl."Object Type"::Table, ReconCustVendBuffer."Table ID"));
end;
}