Report 106 Customer Detailed Aging

Base Application, Microsoft.Sales.Reports

src/Layers/W1/BaseApp/Sales/Reports/CustomerDetailedAging.Report.al370 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 aging report showing individual customer ledger entries with months overdue and remaining amounts./// </summary>using Microsoft.Finance.Currency;using Microsoft.Sales.Customer;using Microsoft.Sales.Receivables;using Microsoft.Utilities;using System.Utilities;report 106 "Customer Detailed Aging"{    AdditionalSearchTerms = 'customer balance,payment due';    ApplicationArea = Basic, Suite;    Caption = 'Customer Detailed Aging';    ToolTip = 'View, print, or save a detailed list of each customer''s total payments due, divided into three time periods. The report can be used to decide when to issue reminders, to evaluate a customer''s creditworthiness, or to prepare liquidity analyses.';    EnableHyperlinks = true;    UsageCategory = ReportsAndAnalysis;    DefaultRenderingLayout = RDLCLayout;    dataset    {        dataitem(Header; "Integer")        {            DataItemTableView = sorting(Number) where(Number = const(1));            column(STRSUBSTNO_Text000_FORMAT_EndDate_; StrSubstNo(Text000Lbl, Format(EndDate)))            {            }            column(COMPANYNAME; COMPANYPROPERTY.DisplayName())            {            }            column(Customer_TABLECAPTION_CustFilter; Customer.TableCaption + ': ' + CustFilter)            {            }            column(CustFilter; CustFilter)            {            }            column(Customer_Detailed_AgingCaption; Customer_Detailed_AgingCaptionLbl)            {            }            column(CurrReport_PAGENOCaption; CurrReport_PAGENOCaptionLbl)            {            }            column(Cust_Ledger_Entry_Posting_Date_Caption; Cust_Ledger_Entry_Posting_Date_CaptionLbl)            {            }            column(Cust_Ledger_Entry_Document_No_Caption; "Cust. Ledger Entry".FieldCaption("Document No."))            {            }            column(Cust_Ledger_Entry_DescriptionCaption; "Cust. Ledger Entry".FieldCaption(Description))            {            }            column(Cust_Ledger_Entry_Due_Date_Caption; Cust_Ledger_Entry_Due_Date_CaptionLbl)            {            }            column(OverDueMonthsCaption; OverDueMonthsCaptionLbl)            {            }            column(Cust_Ledger_Entry_Remaining_Amount_Caption; "Cust. Ledger Entry".FieldCaption("Remaining Amount"))            {            }            column(Cust_Ledger_Entry_Currency_Code_Caption; "Cust. Ledger Entry".FieldCaption("Currency Code"))            {            }            column(Cust_Ledger_Entry_Remaining_Amt_LCY_Caption; "Cust. Ledger Entry".FieldCaption("Remaining Amt. (LCY)"))            {            }            column(Customer_Phone_No_Caption; Customer.FieldCaption("Phone No."))            {            }            dataitem(Customer; Customer)            {                PrintOnlyIfDetail = true;                RequestFilterFields = "No.", "Customer Posting Group", "Currency Filter", "Payment Terms Code";                column(Customer_No_; "No.")                {                }                column(Customer_Name; Name)                {                }                column(Customer_Phone_No_; "Phone No.")                {                }                column(CustomerContact; Contact)                {                }                column(EMail; "E-Mail")                {                }                dataitem("Cust. Ledger Entry"; "Cust. Ledger Entry")                {                    DataItemLink = "Customer No." = field("No."), "Global Dimension 2 Code" = field("Global Dimension 2 Filter"), "Global Dimension 1 Code" = field("Global Dimension 1 Filter"), "Currency Code" = field("Currency Filter"), "Date Filter" = field("Date Filter");                    DataItemTableView = sorting("Customer No.", "Posting Date", "Currency Code");                    column(Cust_Ledger_Entry_Posting_Date_; Format("Posting Date"))                    {                    }                    column(Cust_Ledger_Entry_Document_No_; "Document No.")                    {                    }                    column(Cust_Ledger_Entry_Description; Description)                    {                    }                    column(Cust_Ledger_Entry_Due_Date_; Format("Due Date"))                    {                    }                    column(OverDueMonths; OverDueMonths)                    {                        AutoFormatExpression = "Currency Code";                        AutoFormatType = 1;                    }                    column(Cust_Ledger_Entry_Remaining_Amount_; "Remaining Amount")                    {                        AutoFormatExpression = "Currency Code";                        AutoFormatType = 1;                    }                    column(Cust_Ledger_Entry_Currency_Code_; "Currency Code")                    {                    }                    column(Cust_Ledger_Entry_Remaining_Amt_LCY_; "Remaining Amt. (LCY)")                    {                        AutoFormatType = 1;                    }                    trigger OnAfterGetRecord()                    begin                        if "Due Date" = 0D then                            OverDueMonths := 0                        else                            OverDueMonths := CalcFullMonthsBetweenDates("Due Date", EndDate);                        if ("Remaining Amount" = 0) and OnlyOpen then                            CurrReport.Skip();                        OnAfterGetCustLedgerEntryOnBeforeUpdateTotal("Cust. Ledger Entry");                        TempCurrencyTotalBuffer.UpdateTotal(                          "Currency Code", "Remaining Amount", "Remaining Amt. (LCY)", Counter);                    end;                    trigger OnPreDataItem()                    begin                        if OnlyOpen then begin                            SetRange(Open, true);                            SetRange("Due Date", 0D, EndDate);                        end else                            SetRange("Due Date", 0D, EndDate);                        Counter := 0;                        SetRange("Date Filter", 0D, EndDate);                        SetAutoCalcFields("Remaining Amount", "Remaining Amt. (LCY)");                    end;                }                dataitem("Integer"; "Integer")                {                    DataItemTableView = sorting(Number) where(Number = filter(1 ..));                    column(TempCurrencyTotalBuffer_Total_Amount_; TempCurrencyTotalBuffer."Total Amount")                    {                        AutoFormatExpression = TempCurrencyTotalBuffer."Currency Code";                        AutoFormatType = 1;                    }                    column(TempCurrencyTotalBuffer_Currency_Code_; TempCurrencyTotalBuffer."Currency Code")                    {                    }                    column(TempCurrencyTotalBuffer_Total_Amount_LCY_; TempCurrencyTotalBuffer."Total Amount (LCY)")                    {                        AutoFormatType = 1;                    }                    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",                          TempCurrencyTotalBuffer."Total Amount (LCY)", Counter1);                    end;                    trigger OnPostDataItem()                    begin                        TempCurrencyTotalBuffer.DeleteAll();                    end;                }                trigger OnAfterGetRecord()                begin                    if not CustomersWithLedgerEntriesList.Contains("No.") then                        CurrReport.Skip();                end;                trigger OnPreDataItem()                begin                    if OnlyOpen then                        NumCustLedgEntriesperCust.SetFilter(OpenValue, 'TRUE');                    OnCustomerPreDataItemOnBeforeFillCustomersWithLedgerEntriesList(NumCustLedgEntriesperCust, Customer);                    if NumCustLedgEntriesperCust.Open() then                        while NumCustLedgEntriesperCust.Read() do                            if not CustomersWithLedgerEntriesList.Contains(NumCustLedgEntriesperCust.Customer_No) then                                CustomersWithLedgerEntriesList.Add(NumCustLedgEntriesperCust.Customer_No);                end;            }            dataitem(Integer2; "Integer")            {                DataItemTableView = sorting(Number) where(Number = filter(1 ..));                column(TempCurrencyTotalBuffer2_Currency_Code_; TempCurrencyTotalBuffer2."Currency Code")                {                }                column(TempCurrencyTotalBuffer2_Total_Amount_; TempCurrencyTotalBuffer2."Total Amount")                {                    AutoFormatExpression = TempCurrencyTotalBuffer."Currency Code";                    AutoFormatType = 1;                }                column(TempCurrencyTotalBuffer2_Total_Amount_LCY_; TempCurrencyTotalBuffer2."Total Amount (LCY)")                {                    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;            }        }    }    requestpage    {        SaveValues = true;        layout        {            area(content)            {                group(Options)                {                    Caption = 'Options';                    field("Ending Date"; EndDate)                    {                        ApplicationArea = Basic, Suite;                        Caption = 'Ending Date';                        ToolTip = 'Specifies the end of the period covered by the report (for example, 12/31/17).';                    }                    field(ShowOpenEntriesOnly; OnlyOpen)                    {                        ApplicationArea = Basic, Suite;                        Caption = 'Show Open Entries Only';                        ToolTip = 'Specifies that you want to only show open entries relating to the list of the customers'' balances that are due.';                    }                }            }        }        actions        {        }        trigger OnOpenPage()        begin            if EndDate = 0D then                EndDate := WorkDate();        end;    }    rendering    {        layout(RDLCLayout)        {            Type = RDLC;            LayoutFile = './Sales/Reports/CustomerDetailedAging.rdlc';            Summary = 'Report layout made in the legacy RDLC format. Use an RDLC editor to modify the layout.';        }    }    labels    {        CustomerContactCaption = 'Contact';    }    trigger OnPreReport()    var        FormatDocument: Codeunit "Format Document";    begin        CustFilter := FormatDocument.GetRecordFiltersWithCaptions(Customer);    end;    var        TempCurrencyTotalBuffer: Record "Currency Total Buffer" temporary;        TempCurrencyTotalBuffer2: Record "Currency Total Buffer" temporary;        NumCustLedgEntriesperCust: Query "Num CustLedgEntries per Cust";        CustomersWithLedgerEntriesList: List of [Code[20]];        CustFilter: Text;        OverDueMonths: Integer;        OK: Boolean;        Counter: Integer;        Counter1: Integer;        Text000Lbl: Label 'As of %1', Comment = '%1 is the as of date';        Customer_Detailed_AgingCaptionLbl: Label 'Customer Detailed Aging';        CurrReport_PAGENOCaptionLbl: Label 'Page';        Cust_Ledger_Entry_Posting_Date_CaptionLbl: Label 'Posting Date';        Cust_Ledger_Entry_Due_Date_CaptionLbl: Label 'Due Date';        OverDueMonthsCaptionLbl: Label 'Months Due';        TotalCaptionLbl: Label 'Total';    protected var        EndDate: Date;        OnlyOpen: Boolean;    /// <summary>    /// Initializes the report request options for the Customer Detailed Aging report.    /// </summary>    /// <param name="SetEndDate">The ending date for the aging calculation.</param>    /// <param name="SetOnlyOpen">True to show only open entries.</param>    procedure InitializeRequest(SetEndDate: Date; SetOnlyOpen: Boolean)    begin        EndDate := SetEndDate;        OnlyOpen := SetOnlyOpen;    end;    local procedure CalcFullMonthsBetweenDates(FromDate: Date; ToDate: Date): Integer    var        FullMonths: Integer;        LeftOverDays: Integer;    begin        FullMonths := (Date2DMY(ToDate, 3) - Date2DMY(FromDate, 3)) * 12 + Date2DMY(ToDate, 2) - Date2DMY(FromDate, 2) - 1;        if Date2DMY(ToDate, 1) = Date2DMY(CalcDate('<CM>', ToDate), 1) then            FullMonths += 1        else            LeftOverDays := Date2DMY(ToDate, 1);        if Date2DMY(FromDate, 1) - LeftOverDays <= 1 then            FullMonths += 1;        exit(FullMonths);    end;    [IntegrationEvent(false, false)]    local procedure OnAfterGetCustLedgerEntryOnBeforeUpdateTotal(var CustLedgerEntry: Record "Cust. Ledger Entry")    begin    end;    [IntegrationEvent(false, false)]    local procedure OnCustomerPreDataItemOnBeforeFillCustomersWithLedgerEntriesList(var NumCustLedgEntriesperCust: Query "Num CustLedgEntries per Cust"; Customer: Record Customer)    begin    end;}