Report 129 Customer - Trial Balance

Base Application, Microsoft.Sales.Reports

src/Layers/W1/BaseApp/Sales/Reports/CustomerTrialBalance.Report.al306 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 a trial balance report showing customer balances grouped by customer posting group with debit and credit totals./// </summary>using Microsoft.Foundation.Period;using Microsoft.Sales.Customer;using Microsoft.Sales.Receivables;using System.Telemetry;report 129 "Customer - Trial Balance"{    AdditionalSearchTerms = 'payment due,order status';    ApplicationArea = Basic, Suite;    Caption = 'Customer - Trial Balance';    ToolTip = 'View the beginning and ending balance for customers with entries within a specified period. The report can be used to verify that the balance for a customer posting group is equal to the balance on the corresponding general ledger account on a certain date.';    PreviewMode = PrintLayout;    UsageCategory = ReportsAndAnalysis;    DataAccessIntent = ReadOnly;    DefaultRenderingLayout = RDLCLayout;    dataset    {        dataitem(Customer; Customer)        {            DataItemTableView = sorting("Customer Posting Group");            RequestFilterFields = "No.", "Date Filter", "Customer Posting Group";            column(CompanyName; COMPANYPROPERTY.DisplayName())            {            }            column(PeriodFilter; StrSubstNo(Text003, PeriodFilter))            {            }            column(CustFieldCaptPostingGroup; StrSubstNo(Text005, FieldCaption("Customer Posting Group")))            {            }            column(CustTableCaptioncustFilter; TableCaption + ': ' + CustFilter)            {            }            column(CustFilter; CustFilter)            {            }            column(EmptyString; '')            {            }            column(PeriodStartDate; Format(PeriodStartDate))            {            }            column(PeriodFilter1; PeriodFilter)            {            }            column(FiscalYearStartDate; Format(FiscalYearStartDate))            {            }            column(FiscalYearFilter; FiscalYearFilter)            {            }            column(PeriodEndDate; Format(PeriodEndDate))            {            }            column(PostingGroup_Customer; "Customer Posting Group")            {            }            column(YTDTotal; YTDTotal)            {                AutoFormatType = 1;            }            column(YTDCreditAmt; YTDCreditAmt)            {                AutoFormatType = 1;            }            column(YTDDebitAmt; YTDDebitAmt)            {                AutoFormatType = 1;            }            column(YTDBeginBalance; YTDBeginBalance)            {            }            column(PeriodCreditAmt; PeriodCreditAmt)            {            }            column(PeriodDebitAmt; PeriodDebitAmt)            {            }            column(PeriodBeginBalance; PeriodBeginBalance)            {            }            column(Name_Customer; Name)            {                IncludeCaption = true;            }            column(No_Customer; "No.")            {                IncludeCaption = true;            }            column(TotalPostGroup_Customer; Text004 + Format(' ') + "Customer Posting Group")            {            }            column(CustTrialBalanceCaption; CustTrialBalanceCaptionLbl)            {            }            column(CurrReportPageNoCaption; CurrReportPageNoCaptionLbl)            {            }            column(AmtsinLCYCaption; AmtsinLCYCaptionLbl)            {            }            column(inclcustentriesinperiodCaption; inclcustentriesinperiodCaptionLbl)            {            }            column(YTDTotalCaption; YTDTotalCaptionLbl)            {            }            column(PeriodCaption; PeriodCaptionLbl)            {            }            column(FiscalYearToDateCaption; FiscalYearToDateCaptionLbl)            {            }            column(NetChangeCaption; NetChangeCaptionLbl)            {            }            column(TotalinLCYCaption; TotalinLCYCaptionLbl)            {            }            trigger OnAfterGetRecord()            begin                CalcAmounts(                  PeriodStartDate, PeriodDebitTotals, PeriodCreditTotals,                  PeriodBeginBalance, PeriodDebitAmt, PeriodCreditAmt, YTDTotal);                CalcAmounts(                  FiscalYearStartDate, YTDDebitTotals, YTDCreditTotals,                  YTDBeginBalance, YTDDebitAmt, YTDCreditAmt, YTDTotal);            end;            trigger OnPreDataItem()            var                Telemetry: Codeunit Telemetry;                CustomDimensions: Dictionary of [Text, Text];                StartTime: DateTime;            begin                StartTime := CurrentDateTime();                GetDebitCreditTotals(PeriodStartDate, PeriodEndDate, PeriodDebitTotals, PeriodCreditTotals);                GetDebitCreditTotals(FiscalYearStartDate, PeriodEndDate, YTDDebitTotals, YTDCreditTotals);                CustomDimensions.Add('DurationSec', Format(Round((CurrentDateTime() - StartTime) / 1000, 1)));                Telemetry.LogMessage('0000TZJ', DebitCreditTotalsLbl, Verbosity::Normal, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, CustomDimensions);            end;        }    }    requestpage    {        AboutTitle = 'About Customer - Trial Balance';        AboutText = 'View the closing balances of customers at the end of a period to reconcile the customer subledger against receivables accounts in the general ledger. View beginning balances and net changes by customer for the period and fiscal year to date.';        layout        {        }        actions        {        }    }    rendering    {        layout(RDLCLayout)        {            Type = RDLC;            LayoutFile = './Sales/Reports/CustomerTrialBalance.rdlc';            Summary = 'Report layout made in the legacy RDLC format. Use an RDLC editor to modify the layout.';        }    }    labels    {        PeriodBeginBalanceCaption = 'Beginning Balance';        PeriodDebitAmtCaption = 'Debit';        PeriodCreditAmtCaption = 'Credit';    }    trigger OnPreReport()    begin        PeriodFilter := Customer.GetFilter("Date Filter");        PeriodStartDate := Customer.GetRangeMin("Date Filter");        PeriodEndDate := Customer.GetRangeMax("Date Filter");        Customer.SetRange("Date Filter");        CustFilter := Customer.GetFilters();        Customer.SetRange("Date Filter", PeriodStartDate, PeriodEndDate);        AccountingPeriod.SetRange("Starting Date", 0D, PeriodEndDate);        AccountingPeriod.SetRange("New Fiscal Year", true);        if AccountingPeriod.FindLast() then            FiscalYearStartDate := AccountingPeriod."Starting Date"        else            Error(Text000, AccountingPeriod.FieldCaption("Starting Date"), AccountingPeriod.TableCaption());        FiscalYearFilter := Format(FiscalYearStartDate) + '..' + Format(PeriodEndDate);    end;    var        AccountingPeriod: Record "Accounting Period";        PeriodDebitTotals: Dictionary of [Code[20], Decimal];        PeriodCreditTotals: Dictionary of [Code[20], Decimal];        YTDDebitTotals: Dictionary of [Code[20], Decimal];        YTDCreditTotals: Dictionary of [Code[20], Decimal];        PeriodBeginBalance: Decimal;        PeriodDebitAmt: Decimal;        PeriodCreditAmt: Decimal;        YTDBeginBalance: Decimal;        YTDDebitAmt: Decimal;        YTDCreditAmt: Decimal;        YTDTotal: Decimal;        PeriodFilter: Text;        FiscalYearFilter: Text;        CustFilter: Text;        PeriodStartDate: Date;        PeriodEndDate: Date;        FiscalYearStartDate: Date;#pragma warning disable AA0074#pragma warning disable AA0470        Text000: Label 'It was not possible to find a %1 in %2.';        Text003: Label 'Period: %1';#pragma warning restore AA0470        Text004: Label 'Total for';#pragma warning disable AA0470        Text005: Label 'Group Totals: %1';#pragma warning restore AA0470#pragma warning restore AA0074        CustTrialBalanceCaptionLbl: Label 'Customer - Trial Balance';        CurrReportPageNoCaptionLbl: Label 'Page';        AmtsinLCYCaptionLbl: Label 'Amounts in LCY';        inclcustentriesinperiodCaptionLbl: Label 'Only includes customers with entries in the period';        YTDTotalCaptionLbl: Label 'Ending Balance';        PeriodCaptionLbl: Label 'Period';        FiscalYearToDateCaptionLbl: Label 'Fiscal Year-To-Date';        NetChangeCaptionLbl: Label 'Net Change';        TotalinLCYCaptionLbl: Label 'Total in LCY';        DebitCreditTotalsLbl: Label 'Debit/Credit totals received for Customer Trial Balance', Locked = true;    local procedure CalcAmounts(DateFrom: Date; var DebitTotals: Dictionary of [Code[20], Decimal]; var CreditTotals: Dictionary of [Code[20], Decimal]; var BeginBalance: Decimal; var DebitAmt: Decimal; var CreditAmt: Decimal; var TotalBalance: Decimal)    var        CustomerCopy: Record Customer;    begin        CustomerCopy.Copy(Customer);        CustomerCopy.SetRange("Date Filter", 0D, DateFrom - 1);        CustomerCopy.CalcFields("Net Change (LCY)");        BeginBalance := CustomerCopy."Net Change (LCY)";        if not DebitTotals.Get(Customer."No.", DebitAmt) then            DebitAmt := 0;        if not CreditTotals.Get(Customer."No.", CreditAmt) then            CreditAmt := 0;        TotalBalance := BeginBalance + DebitAmt - CreditAmt;    end;    local procedure GetDebitCreditTotals(DateFrom: Date; DateTo: Date; var DebitTotals: Dictionary of [Code[20], Decimal]; var CreditTotals: Dictionary of [Code[20], Decimal])    var        DetailedCustLedgEntry: Record "Detailed Cust. Ledg. Entry";        CustomerDebitCreditAmount: Query "Customer Debit Credit Amount";        FilterText: Text;    begin        Clear(DebitTotals);        Clear(CreditTotals);        FilterText := Customer.GetFilter("No.");        if FilterText <> '' then            CustomerDebitCreditAmount.SetFilter(Customer_No, FilterText);        CustomerDebitCreditAmount.SetFilter(Entry_Type, '<>%1', DetailedCustLedgEntry."Entry Type"::Application);        CustomerDebitCreditAmount.SetRange(Posting_Date, DateFrom, DateTo);        FilterText := Customer.GetFilter("Global Dimension 1 Filter");        if FilterText <> '' then            CustomerDebitCreditAmount.SetFilter(Initial_Entry_Global_Dim_1, FilterText);        FilterText := Customer.GetFilter("Global Dimension 2 Filter");        if FilterText <> '' then            CustomerDebitCreditAmount.SetFilter(Initial_Entry_Global_Dim_2, FilterText);        FilterText := Customer.GetFilter("Currency Filter");        if FilterText <> '' then            CustomerDebitCreditAmount.SetFilter(Currency_Code, FilterText);        FilterText := Customer.GetFilter("Customer Posting Group");        if FilterText <> '' then            CustomerDebitCreditAmount.SetFilter(Customer_Posting_Group, FilterText);        CustomerDebitCreditAmount.Open();        while CustomerDebitCreditAmount.Read() do begin            DebitTotals.Set(CustomerDebitCreditAmount.Customer_No, CustomerDebitCreditAmount.Sum_Debit_Amount_LCY);            CreditTotals.Set(CustomerDebitCreditAmount.Customer_No, CustomerDebitCreditAmount.Sum_Credit_Amount_LCY);        end;        CustomerDebitCreditAmount.Close();    end;}