Report 104 Customer - Detail Trial Bal.

Base Application, Microsoft.Sales.Reports

src/Layers/W1/BaseApp/Sales/Reports/CustomerDetailTrialBal.Report.al446 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 detailed trial balance showing customer ledger entries with opening and closing balances for a specified period./// </summary>using Microsoft.Finance.GeneralLedger.Setup;using Microsoft.Sales.Customer;using Microsoft.Sales.Receivables;using Microsoft.Utilities;using System.Utilities;report 104 "Customer - Detail Trial Bal."{    ApplicationArea = Basic, Suite;    Caption = 'Customer - Detail Trial Bal.';    ToolTip = 'View the balance for customers with balances on a specified date. The report can be used at the close of an accounting period, for example, or for an audit.';    UsageCategory = ReportsAndAnalysis;    DefaultRenderingLayout = RDLCLayout;    dataset    {        dataitem(Customer; Customer)        {            DataItemTableView = sorting("No.");            PrintOnlyIfDetail = true;            RequestFilterFields = "No.", "Search Name", "Customer Posting Group", "Date Filter";            column(TodayFormatted; Format(Today))            {            }            column(PeriodCustDatetFilter; StrSubstNo(Text000, CustDateFilter))            {            }            column(CompanyName; COMPANYPROPERTY.DisplayName())            {            }            column(PrintAmountsInLCY; PrintAmountsInLCY)            {            }            column(ExcludeBalanceOnly; ExcludeBalanceOnly)            {            }            column(PrintDebitCredit; PrintDebitCredit)            {            }            column(CustFilterCaption; TableCaption + ': ' + CustFilter)            {            }            column(CustFilter; CustFilter)            {            }            column(AmountCaption; AmountCaption)            {            }            column(DebitAmountCaption; DebitLbl)            {            }            column(CreditAmountCaption; CreditLbl)            {            }            column(RemainingAmtCaption; RemainingAmtCaption)            {            }            column(No_Cust; "No.")            {            }            column(Name_Cust; Name)            {            }            column(PageGroupNo; PageGroupNo)            {            }            column(StartBalanceLCY; StartBalanceLCY)            {                AutoFormatType = 1;            }            column(CustBalanceLCY; CustBalanceLCY)            {                AutoFormatType = 1;            }            column(CustLedgerEntryAmtLCY; "Cust. Ledger Entry"."Amount (LCY)")            {                AutoFormatType = 1;            }            column(CustDetailTrialBalCaption; CustDetailTrialBalCaptionLbl)            {            }            column(PageNoCaption; PageNoCaptionLbl)            {            }            column(AllAmtsLCYCaption; AllAmtsLCYCaptionLbl)            {            }            column(RepInclCustsBalCptn; RepInclCustsBalCptnLbl)            {            }            column(PostingDateCaption; PostingDateCaptionLbl)            {            }            column(DueDateCaption; DueDateCaptionLbl)            {            }            column(BalanceLCYCaption; BalanceLCYCaptionLbl)            {            }            column(AdjOpeningBalCaption; AdjOpeningBalCaptionLbl)            {            }            column(BeforePeriodCaption; BeforePeriodCaptionLbl)            {            }            column(TotalCaption; TotalCaptionLbl)            {            }            column(OpeningBalCaption; OpeningBalCaptionLbl)            {            }            column(ExternalDocNoCaption; ExternalDocNoCaptionLbl)            {            }            dataitem("Cust. Ledger Entry"; "Cust. Ledger Entry")            {                DataItemLink = "Customer No." = field("No."), "Posting Date" = field("Date Filter"), "Global Dimension 2 Code" = field("Global Dimension 2 Filter"), "Global Dimension 1 Code" = field("Global Dimension 1 Filter"), "Date Filter" = field("Date Filter");                DataItemTableView = sorting("Customer No.", "Posting Date");                column(PostDate_CustLedgEntry; Format("Posting Date"))                {                }                column(DocType_CustLedgEntry; "Document Type")                {                    IncludeCaption = true;                }                column(DocNo_CustLedgEntry; "Document No.")                {                    IncludeCaption = true;                }                column(ExtDocNo_CustLedgEntry; "External Document No.")                {                }                column(Desc_CustLedgEntry; Description)                {                    IncludeCaption = true;                }                column(CustAmount; CustAmount)                {                    AutoFormatExpression = CustCurrencyCode;                    AutoFormatType = 1;                }                column(CustDebitAmount; CustDebitAmount)                {                    AutoFormatExpression = CustCurrencyCode;                    AutoFormatType = 1;                }                column(CustCreditAmount; CustCreditAmount)                {                    AutoFormatExpression = CustCurrencyCode;                    AutoFormatType = 1;                }                column(CustRemainAmount; CustRemainAmount)                {                    AutoFormatExpression = CustCurrencyCode;                    AutoFormatType = 1;                }                column(CustEntryDueDate; Format(CustEntryDueDate))                {                }                column(EntryNo_CustLedgEntry; "Entry No.")                {                    IncludeCaption = true;                }                column(CustCurrencyCode; CustCurrencyCode)                {                }                column(CustBalanceLCY1; CustBalanceLCY)                {                    AutoFormatType = 1;                }                trigger OnAfterGetRecord()                var                    DetailedCustLedgEntry: Record "Detailed Cust. Ledg. Entry";                begin                    CustLedgEntryExists := true;                    if PrintAmountsInLCY then begin                        CustAmount := "Amount (LCY)";                        CustRemainAmount := "Remaining Amt. (LCY)";                        CustCurrencyCode := '';                    end else begin                        CustAmount := Amount;                        CustRemainAmount := "Remaining Amount";                        CustCurrencyCode := "Currency Code";                    end;                    CustDebitAmount := 0;                    CustCreditAmount := 0;                    if CustAmount > 0 then                        CustDebitAmount := CustAmount                    else                        CustCreditAmount := -CustAmount;                    CustTotalDebitAmount += CustDebitAmount;                    CustTotalCreditAmount += CustCreditAmount;                    CustBalanceLCY := CustBalanceLCY + "Amount (LCY)";                    DetailedCustLedgEntry.SetCurrentKey("Cust. Ledger Entry No.", "Entry Type", "Posting Date");                    DetailedCustLedgEntry.SetRange("Cust. Ledger Entry No.", "Entry No.");                    DetailedCustLedgEntry.SetFilter("Entry Type", '%1|%2',                        DetailedCustLedgEntry."Entry Type"::"Correction of Remaining Amount",                        DetailedCustLedgEntry."Entry Type"::"Appln. Rounding");                    DetailedCustLedgEntry.SetFilter("Posting Date", CustDateFilter);                    DetailedCustLedgEntry.CalcSums("Amount (LCY)");                    CustBalanceLCY := CustBalanceLCY + DetailedCustLedgEntry."Amount (LCY)";                    if ("Document Type" = "Document Type"::Payment) or ("Document Type" = "Document Type"::Refund) then                        CustEntryDueDate := 0D                    else                        CustEntryDueDate := "Due Date";                end;                trigger OnPreDataItem()                begin                    CustLedgEntryExists := false;                    CustTotalDebitAmount := 0;                    CustTotalCreditAmount := 0;                    CustAmount := 0;                    CustDebitAmount := 0;                    CustCreditAmount := 0;                    SetAutoCalcFields(Amount, "Remaining Amount", "Amount (LCY)", "Remaining Amt. (LCY)");                end;            }            dataitem("Integer"; "Integer")            {                DataItemTableView = sorting(Number) where(Number = const(1));                column(Name1_Cust; Customer.Name)                {                }                column(CustBalanceLCY4; CustBalanceLCY)                {                    AutoFormatType = 1;                }                column(StartBalanceLCY2; StartBalanceLCY)                {                }                column(CustTotalDebitAmount; CustTotalDebitAmount)                {                }                column(CustTotalCreditAmount; CustTotalCreditAmount)                {                }                trigger OnAfterGetRecord()                begin                    if not CustLedgEntryExists and ((StartBalanceLCY = 0) or ExcludeBalanceOnly) then begin                        StartBalanceLCY := 0;                        CurrReport.Skip();                    end;                end;            }            trigger OnAfterGetRecord()            var                CustLedgerEntry: Record "Cust. Ledger Entry";                DetailedCustLedgEntry: Record "Detailed Cust. Ledg. Entry";            begin                if PrintOnlyOnePerPage then                    PageGroupNo := PageGroupNo + 1;                StartBalanceLCY := 0;                StartBalAdjLCY := 0;                if CustDateFilter <> '' then begin                    if GetRangeMin("Date Filter") <> 0D then begin                        SetRange("Date Filter", 0D, GetRangeMin("Date Filter") - 1);                        CalcFields("Net Change (LCY)");                        StartBalanceLCY := "Net Change (LCY)";                    end;                    SetFilter("Date Filter", CustDateFilter);                    CalcFields("Net Change (LCY)");                    StartBalAdjLCY := "Net Change (LCY)";                    CustLedgerEntry.SetCurrentKey("Customer No.", "Posting Date");                    CustLedgerEntry.SetRange("Customer No.", "No.");                    CustLedgerEntry.SetFilter("Posting Date", CustDateFilter);                    if CustLedgerEntry.Find('-') then                        repeat                            CustLedgerEntry.SetFilter("Date Filter", CustDateFilter);                            CustLedgerEntry.CalcFields("Amount (LCY)");                            StartBalAdjLCY := StartBalAdjLCY - CustLedgerEntry."Amount (LCY)";                            DetailedCustLedgEntry.SetCurrentKey("Cust. Ledger Entry No.", "Entry Type", "Posting Date");                            DetailedCustLedgEntry.SetRange("Cust. Ledger Entry No.", CustLedgerEntry."Entry No.");                            DetailedCustLedgEntry.SetFilter("Entry Type", '%1|%2',                                DetailedCustLedgEntry."Entry Type"::"Correction of Remaining Amount",                                DetailedCustLedgEntry."Entry Type"::"Appln. Rounding");                            DetailedCustLedgEntry.SetFilter("Posting Date", CustDateFilter);                            if DetailedCustLedgEntry.Find('-') then                                repeat                                    StartBalAdjLCY := StartBalAdjLCY - DetailedCustLedgEntry."Amount (LCY)";                                until DetailedCustLedgEntry.Next() = 0;                            DetailedCustLedgEntry.Reset();                        until CustLedgerEntry.Next() = 0;                end;                CurrReport.PrintOnlyIfDetail := ExcludeBalanceOnly or (StartBalanceLCY = 0);                CustBalanceLCY := StartBalanceLCY + StartBalAdjLCY;            end;            trigger OnPreDataItem()            begin                PageGroupNo := 1;                Clear(StartBalanceLCY);                Clear(StartBalAdjLCY);            end;        }    }    requestpage    {        SaveValues = true;        AboutTitle = 'About Customer - Detail Trial Bal.';        AboutText = 'View customer balances at the end of a period, including the opening balance, each transaction within the period, and the closing balance grouped by customer. View details for remaining amounts and due dates.';        layout        {            area(content)            {                group(Options)                {                    Caption = 'Options';                    field(ShowAmountsInLCY; PrintAmountsInLCY)                    {                        ApplicationArea = Basic, Suite;                        Caption = 'Show Amounts in LCY';                        ToolTip = 'Specifies if the reported amounts are shown in the local currency.';                    }                    field(NewPageperCustomer; PrintOnlyOnePerPage)                    {                        ApplicationArea = Basic, Suite;                        Caption = 'New Page per Customer';                        ToolTip = 'Specifies if each customer''s information is printed on a new page if you have chosen two or more customers to be included in the report.';                    }                    field(ExcludeCustHaveaBalanceOnly; ExcludeBalanceOnly)                    {                        ApplicationArea = Basic, Suite;                        Caption = 'Exclude Customers That Have a Balance Only';                        MultiLine = true;                        ToolTip = 'Specifies if you do not want the report to include entries for customers that have a balance but do not have a net change during the selected time period.';                    }                }            }        }        actions        {        }    }    rendering    {        layout(RDLCLayout)        {            Type = RDLC;            LayoutFile = './Sales/Reports/CustomerDetailTrialBal.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        GeneralLedgerSetup.Get();        PrintDebitCredit := GeneralLedgerSetup."Show Amounts" = GeneralLedgerSetup."Show Amounts"::"Debit/Credit Only";        CustFilter := FormatDocument.GetRecordFiltersWithCaptions(Customer);        CustDateFilter := Customer.GetFilter("Date Filter");        if PrintAmountsInLCY then begin            AmountCaption := "Cust. Ledger Entry".FieldCaption("Amount (LCY)");            RemainingAmtCaption := "Cust. Ledger Entry".FieldCaption("Remaining Amt. (LCY)");        end else begin            AmountCaption := "Cust. Ledger Entry".FieldCaption(Amount);            RemainingAmtCaption := "Cust. Ledger Entry".FieldCaption("Remaining Amount");        end;    end;    var        GeneralLedgerSetup: Record "General Ledger Setup";        PrintDebitCredit: Boolean;        PrintAmountsInLCY: Boolean;        PrintOnlyOnePerPage: Boolean;        ExcludeBalanceOnly: Boolean;        CustDateFilter: Text;        AmountCaption: Text[80];        RemainingAmtCaption: Text[30];        CustAmount: Decimal;        CustDebitAmount: Decimal;        CustCreditAmount: Decimal;        CustTotalDebitAmount: Decimal;        CustTotalCreditAmount: Decimal;        CustRemainAmount: Decimal;        CustBalanceLCY: Decimal;        CustCurrencyCode: Code[10];        CustEntryDueDate: Date;        StartBalanceLCY: Decimal;        StartBalAdjLCY: Decimal;        CustLedgEntryExists: Boolean;        PageGroupNo: Integer;#pragma warning disable AA0074#pragma warning disable AA0470        Text000: Label 'Period: %1';#pragma warning restore AA0470#pragma warning restore AA0074        CustDetailTrialBalCaptionLbl: Label 'Customer - Detail Trial Bal.';        PageNoCaptionLbl: Label 'Page';        AllAmtsLCYCaptionLbl: Label 'All amounts are in LCY';        RepInclCustsBalCptnLbl: Label 'This report also includes customers that only have balances.';        PostingDateCaptionLbl: Label 'Posting Date';        DueDateCaptionLbl: Label 'Due Date';        BalanceLCYCaptionLbl: Label 'Balance (LCY)';        AdjOpeningBalCaptionLbl: Label 'Adj. of Opening Balance';        BeforePeriodCaptionLbl: Label 'Total (LCY) Before Period';        TotalCaptionLbl: Label 'Total (LCY)';        OpeningBalCaptionLbl: Label 'Total Adj. of Opening Balance';        DebitLbl: Label 'Debit Amount';        CreditLbl: Label 'Credit Amount';        ExternalDocNoCaptionLbl: Label 'External Doc. No.';    protected var        CustFilter: Text;    /// <summary>    /// Initializes the report request options for the Customer Detail Trial Balance report.    /// </summary>    /// <param name="ShowAmountInLCY">True to show amounts in local currency.</param>    /// <param name="SetPrintOnlyOnePerPage">True to start a new page per customer.</param>    /// <param name="SetExcludeBalanceOnly">True to exclude customers with only balance entries.</param>    procedure InitializeRequest(ShowAmountInLCY: Boolean; SetPrintOnlyOnePerPage: Boolean; SetExcludeBalanceOnly: Boolean)    begin        PrintOnlyOnePerPage := SetPrintOnlyOnePerPage;        PrintAmountsInLCY := ShowAmountInLCY;        ExcludeBalanceOnly := SetExcludeBalanceOnly;    end;}