Report 121 Customer - Balance to Date, source in 29
Source29
src/Layers/W1/BaseApp/Sales/Reports/CustomerBalancetoDate.Report.al589 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.Sales.Reports;
/// <summary>
/// Generates customer balance statements showing ledger entries and remaining amounts as of a specified date.
/// </summary>
using Microsoft.Finance.Currency;
using Microsoft.Sales.Customer;
using Microsoft.Sales.Receivables;
using Microsoft.Utilities;
using System.Text;
using System.Utilities;
report 121 "Customer - Balance to Date"
{
ApplicationArea = Basic, Suite;
Caption = 'Customer Balance to Date';
ToolTip = 'View a list with customers'' payment history up until a certain date. You can use the report to extract your total sales income at the close of an accounting period or fiscal year.';
UsageCategory = ReportsAndAnalysis;
DataAccessIntent = ReadOnly;
DefaultRenderingLayout = RDLCLayout;
dataset
{
dataitem(Customer; Customer)
{
DataItemTableView = sorting("No.");
PrintOnlyIfDetail = true;
RequestFilterFields = "No.", "Date Filter", Blocked;
column(TodayFormatted; Format(Today, 0, 4))
{
}
column(TxtCustGeTranmaxDtFilter; StrSubstNo(Text000, Format(GetRangeMax("Date Filter"))))
{
}
column(CompanyName; COMPANYPROPERTY.DisplayName())
{
}
column(PrintOnePrPage; PrintOnePrPage)
{
}
column(CustFilter; CustFilter)
{
}
column(PrintAmountInLCY; PrintAmountInLCY)
{
}
column(CustTableCaptCustFilter; TableCaption + ': ' + CustFilter)
{
}
column(No_Customer; "No.")
{
}
column(Name_Customer; Name)
{
}
column(PhoneNo_Customer; "Phone No.")
{
IncludeCaption = true;
}
column(CustBalancetoDateCaption; CustBalancetoDateCaptionLbl)
{
}
column(CurrReportPageNoCaption; CurrReportPageNoCaptionLbl)
{
}
column(AllamtsareinLCYCaption; AllamtsareinLCYCaptionLbl)
{
}
column(CustLedgEntryPostingDtCaption; CustLedgEntryPostingDtCaptionLbl)
{
}
column(OriginalAmtCaption; OriginalAmtCaptionLbl)
{
}
dataitem(CustLedgEntry3; "Cust. Ledger Entry")
{
DataItemTableView = sorting("Entry No.");
column(PostingDt_CustLedgEntry; Format("Posting Date"))
{
IncludeCaption = false;
}
column(DocType_CustLedgEntry; "Document Type")
{
IncludeCaption = true;
}
column(DocNo_CustLedgEntry; "Document No.")
{
IncludeCaption = true;
}
column(Desc_CustLedgEntry; Description)
{
IncludeCaption = true;
}
column(OriginalAmt; Format(OriginalAmt, 0, AutoFormat.ResolveAutoFormat(Enum::"Auto Format"::AmountFormat, CurrencyCode)))
{
}
column(EntryNo_CustLedgEntry; "Entry No.")
{
IncludeCaption = true;
}
column(CurrencyCode; CurrencyCode)
{
}
dataitem("Detailed Cust. Ledg. Entry"; "Detailed Cust. Ledg. Entry")
{
DataItemLink = "Cust. Ledger Entry No." = field("Entry No."), "Posting Date" = field("Date Filter");
DataItemTableView = sorting("Cust. Ledger Entry No.", "Posting Date") where("Entry Type" = filter(<> "Initial Entry"));
column(EntType_DtldCustLedgEnt; "Entry Type")
{
}
column(postDt_DtldCustLedgEntry; Format("Posting Date"))
{
}
column(DocType_DtldCustLedgEntry; "Document Type")
{
}
column(DocNo_DtldCustLedgEntry; "Document No.")
{
}
column(Amt; Format(Amt, 0, AutoFormat.ResolveAutoFormat(Enum::"Auto Format"::AmountFormat, CurrencyCode)))
{
}
column(CurrencyCodeDtldCustLedgEntry; CurrencyCode)
{
}
column(EntNo_DtldCustLedgEntry; DtldCustLedgEntryNum)
{
}
column(RemainingAmt; Format(RemainingAmt, 0, AutoFormat.ResolveAutoFormat(Enum::"Auto Format"::AmountFormat, CurrencyCode)))
{
}
trigger OnAfterGetRecord()
begin
if not PrintUnappliedEntries then
if Unapplied then
CurrReport.Skip();
if PrintAmountInLCY then begin
Amt := "Amount (LCY)";
CurrencyCode := '';
end else begin
Amt := Amount;
CurrencyCode := "Currency Code";
end;
OnAfterDetailedCustLedgEntryOnAfterCalcAmt("Detailed Cust. Ledg. Entry", PrintAmountInLCY, Amt, CurrencyCode, MaxDate);
if Amt = 0 then
CurrReport.Skip();
DtldCustLedgEntryNum := DtldCustLedgEntryNum + 1;
end;
trigger OnPreDataItem()
begin
DtldCustLedgEntryNum := 0;
CustLedgEntry3.CopyFilter("Posting Date", "Posting Date");
end;
}
trigger OnAfterGetRecord()
begin
if PrintAmountInLCY then begin
CalcFields("Original Amt. (LCY)", "Remaining Amt. (LCY)");
OriginalAmt := "Original Amt. (LCY)";
RemainingAmt := "Remaining Amt. (LCY)";
CurrencyCode := '';
end else begin
CalcFields("Original Amount", "Remaining Amount");
OriginalAmt := "Original Amount";
RemainingAmt := "Remaining Amount";
CurrencyCode := "Currency Code";
end;
OnAfterCustLedgEntry3OnAfterGetRecord(CustLedgEntry3, PrintAmountInLCY, OriginalAmt, RemainingAmt, CurrencyCode, MaxDate);
end;
trigger OnPreDataItem()
var
TempCustLedgerEntry: Record "Cust. Ledger Entry" temporary;
ClosedEntryIncluded: Boolean;
begin
Reset();
SetRange("Date Filter", 0D, MaxDate);
FilterCustLedgerEntry(CustLedgEntry3);
if FindSet() then
repeat
if not Open then
ClosedEntryIncluded := CheckCustEntryIncluded("Entry No.");
if Open or ClosedEntryIncluded then begin
Mark(true);
TempCustLedgerEntry := CustLedgEntry3;
TempCustLedgerEntry.Insert();
end;
until Next() = 0;
SetCurrentKey("Entry No.");
MarkedOnly(true);
AddCustomerDimensionFilter(CustLedgEntry3);
CalcCustomerTotalAmount(TempCustLedgerEntry);
end;
}
dataitem(Integer2; "Integer")
{
DataItemTableView = sorting(Number) where(Number = filter(1 ..));
column(CustName; Customer.Name)
{
}
column(TtlAmtCurrencyTtlBuff; TempCurrencyTotalBuffer."Total Amount")
{
AutoFormatExpression = TempCurrencyTotalBuffer."Currency Code";
AutoFormatType = 1;
}
column(CurryCode_CurrencyTtBuff; TempCurrencyTotalBuffer."Currency Code")
{
}
trigger OnAfterGetRecord()
begin
if Number = 1 then
OK := TempCurrencyTotalBuffer.Find('-')
else
OK := TempCurrencyTotalBuffer.Next() <> 0;
if not OK then
CurrReport.Break();
TempCurrencyTotalBuffer2.UpdateTotal(
TempCurrencyTotalBuffer."Currency Code",
TempCurrencyTotalBuffer."Total Amount",
0,
Counter1);
end;
trigger OnPostDataItem()
begin
TempCurrencyTotalBuffer.DeleteAll();
end;
trigger OnPreDataItem()
begin
if not ShowEntriesWithZeroBalance then
TempCurrencyTotalBuffer.SetFilter("Total Amount", '<>0');
end;
}
trigger OnAfterGetRecord()
begin
if MaxDate = 0D then
Error(BlankMaxDateErr);
CalcFields("Net Change (LCY)", "Net Change");
if ("Net Change (LCY)" = 0) and
("Net Change" = 0) and
(not ShowEntriesWithZeroBalance)
then
CurrReport.Skip();
end;
trigger OnPreDataItem()
begin
DateFilterTxt := GetFilter("Date Filter");
SetRange("Date Filter", 0D, MaxDate);
end;
}
dataitem(Integer3; "Integer")
{
DataItemTableView = sorting(Number) where(Number = filter(1 ..));
column(CurryCode_CurrencyTtBuff2; TempCurrencyTotalBuffer2."Currency Code")
{
}
column(TtlAmtCurrencyTtlBuff2; TempCurrencyTotalBuffer2."Total Amount")
{
AutoFormatExpression = TempCurrencyTotalBuffer2."Currency Code";
AutoFormatType = 1;
}
column(TotalCaption; TotalCaptionLbl)
{
}
trigger OnAfterGetRecord()
begin
if Number = 1 then
OK := TempCurrencyTotalBuffer2.Find('-')
else
OK := TempCurrencyTotalBuffer2.Next() <> 0;
if not OK then
CurrReport.Break();
end;
trigger OnPostDataItem()
begin
TempCurrencyTotalBuffer2.DeleteAll();
end;
trigger OnPreDataItem()
begin
TempCurrencyTotalBuffer2.SetFilter("Total Amount", '<>0');
end;
}
}
requestpage
{
AboutTitle = 'About Customer Balance to Date';
AboutText = 'Generate customer statements to get a clear summary of amounts due. You can share the amounts with customers to follow up on payments. For example, if you need to close an accounting period or fiscal year.';
SaveValues = true;
layout
{
area(content)
{
group(Options)
{
Caption = 'Options';
field("Ending Date"; MaxDate)
{
ApplicationArea = Basic, Suite;
Caption = 'Ending Date';
ToolTip = 'Specifies the last date until which information in the report is shown.';
ShowMandatory = true;
}
field(PrintAmountInLCY; PrintAmountInLCY)
{
ApplicationArea = Basic, Suite;
Caption = 'Show Amounts in LCY';
ToolTip = 'Specifies if amounts in the report are displayed in LCY. If you leave the check box blank, amounts are shown in foreign currencies.';
}
field(PrintOnePrPage; PrintOnePrPage)
{
ApplicationArea = Basic, Suite;
Caption = 'New Page per Customer';
ToolTip = 'Specifies if each customer balance is printed on a separate page, in case two or more customers are included in the report.';
}
field(PrintUnappliedEntries; PrintUnappliedEntries)
{
ApplicationArea = Basic, Suite;
Caption = 'Include Unapplied Entries';
ToolTip = 'Specifies if the report includes entries that have been applied and later unapplied using the Unapply action. By default, the report does not show such entries.';
}
field(ShowEntriesWithZeroBalance; ShowEntriesWithZeroBalance)
{
ApplicationArea = Basic, Suite;
Caption = 'Show Entries with Zero Balance';
ToolTip = 'Specifies if the report must include customer ledger entries with a balance of 0. By default, the report only includes customer ledger entries with a positive or negative balance.';
}
}
}
}
actions
{
}
}
rendering
{
layout(RDLCLayout)
{
Type = RDLC;
LayoutFile = './Sales/Reports/CustomerBalancetoDate.rdlc';
Summary = 'Report layout made in the legacy RDLC format. Use an RDLC editor to modify the layout.';
}
}
labels
{
}
trigger OnPreReport()
var
FormatDocument: Codeunit "Format Document";
begin
CustFilter := FormatDocument.GetRecordFiltersWithCaptions(Customer);
end;
var
AutoFormat: Codeunit "Auto Format";
DtldCustLedgEntryNum: Integer;
OK: Boolean;
DateFilterTxt: Text;
#pragma warning disable AA0074
#pragma warning disable AA0470
Text000: Label 'Balance on %1';
#pragma warning restore AA0470
#pragma warning restore AA0074
CustBalancetoDateCaptionLbl: Label 'Customer - Balance to Date';
CurrReportPageNoCaptionLbl: Label 'Page';
AllamtsareinLCYCaptionLbl: Label 'All amounts are in LCY.';
CustLedgEntryPostingDtCaptionLbl: Label 'Posting Date';
OriginalAmtCaptionLbl: Label 'Amount';
TotalCaptionLbl: Label 'Total';
BlankMaxDateErr: Label 'Ending Date must have a value.';
protected var
TempCurrencyTotalBuffer: Record "Currency Total Buffer" temporary;
TempCurrencyTotalBuffer2: Record "Currency Total Buffer" temporary;
CurrencyCode: Code[10];
MaxDate: Date;
PrintAmountInLCY: Boolean;
PrintOnePrPage: Boolean;
ShowEntriesWithZeroBalance: Boolean;
PrintUnappliedEntries: Boolean;
CustFilter: Text;
OriginalAmt: Decimal;
Amt: Decimal;
RemainingAmt: Decimal;
Counter1: Integer;
/// <summary>
/// Initializes the report request options for the Customer Balance to Date report.
/// </summary>
/// <param name="NewPrintAmountInLCY">True to print amounts in local currency.</param>
/// <param name="NewPrintOnePrPage">True to start a new page per customer.</param>
/// <param name="NewPrintUnappliedEntries">True to include unapplied entries.</param>
/// <param name="NewEndingDate">The ending date for the balance calculation.</param>
procedure InitializeRequest(NewPrintAmountInLCY: Boolean; NewPrintOnePrPage: Boolean; NewPrintUnappliedEntries: Boolean; NewEndingDate: Date)
begin
PrintAmountInLCY := NewPrintAmountInLCY;
PrintOnePrPage := NewPrintOnePrPage;
PrintUnappliedEntries := NewPrintUnappliedEntries;
MaxDate := NewEndingDate;
end;
local procedure FilterCustLedgerEntry(var CustLedgerEntry: Record "Cust. Ledger Entry")
begin
CustLedgerEntry.SetCurrentKey("Customer No.", "Posting Date");
CustLedgerEntry.SetRange("Customer No.", Customer."No.");
CustLedgerEntry.SetRange("Posting Date", 0D, MaxDate);
end;
local procedure AddCustomerDimensionFilter(var CustLedgerEntry: Record "Cust. Ledger Entry")
begin
if Customer.GetFilter("Global Dimension 1 Filter") <> '' then
CustLedgerEntry.SetFilter("Global Dimension 1 Code", Customer.GetFilter("Global Dimension 1 Filter"));
if Customer.GetFilter("Global Dimension 2 Filter") <> '' then
CustLedgerEntry.SetFilter("Global Dimension 2 Code", Customer.GetFilter("Global Dimension 2 Filter"));
if Customer.GetFilter("Currency Filter") <> '' then
CustLedgerEntry.SetFilter("Currency Code", Customer.GetFilter("Currency Filter"));
end;
local procedure CalcCustomerTotalAmount(var TempCustLedgerEntry: Record "Cust. Ledger Entry" temporary)
begin
TempCustLedgerEntry.SetCurrentKey("Entry No.");
TempCustLedgerEntry.SetRange("Date Filter", 0D, MaxDate);
AddCustomerDimensionFilter(TempCustLedgerEntry);
if TempCustLedgerEntry.FindSet() then
repeat
CalcRemainingAmount(TempCustLedgerEntry);
if (RemainingAmt <> 0) or ShowEntriesWithZeroBalance then
TempCurrencyTotalBuffer.UpdateTotal(
CurrencyCode,
RemainingAmt,
0,
Counter1);
OnCalcCustomerTotalAmountOnAfterUpdateCurrencyTotalBuffer(TempCustLedgerEntry, TempCurrencyTotalBuffer, Counter1, RemainingAmt, ShowEntriesWithZeroBalance);
until TempCustLedgerEntry.Next() = 0;
end;
local procedure CalcRemainingAmount(var TempCustLedgerEntry: Record "Cust. Ledger Entry" temporary);
begin
if PrintAmountInLCY then begin
TempCustLedgerEntry.CalcFields("Remaining Amt. (LCY)");
RemainingAmt := TempCustLedgerEntry."Remaining Amt. (LCY)";
CurrencyCode := '';
end else begin
TempCustLedgerEntry.CalcFields("Remaining Amount");
RemainingAmt := TempCustLedgerEntry."Remaining Amount";
CurrencyCode := TempCustLedgerEntry."Currency Code";
end;
OnAfterCalcRemainingAmount(TempCustLedgerEntry, PrintAmountInLCY, RemainingAmt, CurrencyCode);
end;
local procedure CheckCustEntryIncluded(EntryNo: Integer): Boolean
var
CustLedgerEntry: Record "Cust. Ledger Entry";
ClosingCustLedgerEntry: Record "Cust. Ledger Entry";
begin
if CustLedgerEntry.Get(EntryNo) and (CustLedgerEntry."Posting Date" <= MaxDate) then begin
CustLedgerEntry.SetRange("Date Filter", 0D, MaxDate);
CustLedgerEntry.CalcFields("Remaining Amount", "Original Amount", Amount);
if CustLedgerEntry."Remaining Amount" <> 0 then
exit(not CheckUnbalancedUnappliedEntryExists(EntryNo));
if (ShowEntriesWithZeroBalance) and (DateFilterTxt <> '') then begin
if ClosingCustLedgerEntry.Get(CustLedgerEntry."Closed by Entry No.") then begin
ClosingCustLedgerEntry.SetFilter("Date Filter", DateFilterTxt);
if CheckCustEntryIncludedWhenShowEntriesWithZeroBalance(ClosingCustLedgerEntry) then
exit(true);
end;
if (CustLedgerEntry.Amount = 0) and
(CustLedgerEntry."Original Amount" = 0) and
not CustLedgerEntry.Open and
(CustLedgerEntry."Posting Date" <= MaxDate)
then
exit(true);
end;
if PrintUnappliedEntries then
exit(CheckUnappliedEntryExists(EntryNo));
end;
exit(false);
end;
local procedure CheckUnappliedEntryExists(EntryNo: Integer): Boolean
var
DetailedCustLedgEntry: Record "Detailed Cust. Ledg. Entry";
begin
DetailedCustLedgEntry.SetCurrentKey("Cust. Ledger Entry No.", "Entry Type", "Posting Date");
DetailedCustLedgEntry.SetRange("Cust. Ledger Entry No.", EntryNo);
DetailedCustLedgEntry.SetRange("Entry Type", DetailedCustLedgEntry."Entry Type"::Application);
DetailedCustLedgEntry.SetFilter("Posting Date", '>%1', MaxDate);
DetailedCustLedgEntry.SetRange(Unapplied, true);
exit(not DetailedCustLedgEntry.IsEmpty);
end;
local procedure CheckCustEntryIncludedWhenShowEntriesWithZeroBalance(var ClosingCustLedgerEntry: Record "Cust. Ledger Entry"): Boolean
var
DateListText: List of [Text];
MinDate: Date;
ListMaxDate: Date;
begin
if StrPos(DateFilterTxt, '..') > 0 then begin
DateListText := ClosingCustLedgerEntry.GetFilter("Date Filter").Split('..');
if DateListText.Get(1) <> '' then
Evaluate(MinDate, DateListText.Get(1));
if DateListText.Get(2) <> '' then
Evaluate(ListMaxDate, DateListText.Get(2));
if (ListMaxDate = 0D) and (MinDate <> 0D) then
exit(ClosingCustLedgerEntry."Posting Date" >= MinDate);
if true in [
(ClosingCustLedgerEntry."Posting Date" <= ListMaxDate) and (ClosingCustLedgerEntry."Posting Date" >= MinDate),
(ClosingCustLedgerEntry."Posting Date" <= ListMaxDate)]
then
exit(true);
end else
if (ClosingCustLedgerEntry."Posting Date" = ClosingCustLedgerEntry.GetRangeMax("Date Filter")) then
exit(true);
end;
local procedure CheckUnbalancedUnappliedEntryExists(EntryNo: Integer): Boolean
var
DetailedCustLedgEntry: Record "Detailed Cust. Ledg. Entry";
begin
DetailedCustLedgEntry.SetCurrentKey("Cust. Ledger Entry No.", "Entry Type", "Posting Date");
DetailedCustLedgEntry.SetRange("Cust. Ledger Entry No.", EntryNo);
DetailedCustLedgEntry.SetRange("Entry Type", DetailedCustLedgEntry."Entry Type"::Application);
DetailedCustLedgEntry.SetFilter("Posting Date", '>%1', MaxDate);
DetailedCustLedgEntry.SetRange(Unapplied, true);
DetailedCustLedgEntry.CalcSums(Amount);
exit(DetailedCustLedgEntry.Amount <> 0);
end;
[IntegrationEvent(false, false)]
local procedure OnAfterCalcRemainingAmount(var TempCustLedgerEntry: Record "Cust. Ledger Entry" temporary; PrintAmountInLCY: Boolean; var RemainingAmt: Decimal; var CurrencyCode: Code[10])
begin
end;
[IntegrationEvent(false, false)]
local procedure OnAfterCustLedgEntry3OnAfterGetRecord(var CustLedgerEntry: Record "Cust. Ledger Entry"; PrintAmountInLCY: Boolean; var OriginalAmt: Decimal; var RemainingAmt: Decimal; var CurrencyCode: Code[10]; MaxDate: Date)
begin
end;
[IntegrationEvent(false, false)]
local procedure OnAfterDetailedCustLedgEntryOnAfterCalcAmt(var DetailedCustLedgEntry: Record "Detailed Cust. Ledg. Entry"; PrintAmountInLCY: Boolean; var Amt: Decimal; var CurrencyCode: Code[10]; MaxDate: Date)
begin
end;
[IntegrationEvent(false, false)]
local procedure OnCalcCustomerTotalAmountOnAfterUpdateCurrencyTotalBuffer(var TempCustLedgerEntry: Record "Cust. Ledger Entry" temporary; var TempCurrencyTotalBuffer: Record "Currency Total Buffer" temporary; Counter1: Integer; RemainingAmt: Decimal; ShowEntriesWithZeroBalance: Boolean)
begin
end;
}