Report 104 Customer - Detail Trial Bal., source in 29
Source29
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;
}