Report 121 Customer - Balance to Date
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;}