Report 104 Customer - Detail Trial Bal.
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;}