Report 108 Customer - Order Detail

Base Application, Microsoft.Sales.Reports

src/Layers/W1/BaseApp/Sales/Reports/CustomerOrderDetail.Report.al577 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>/// Lists outstanding sales order lines by customer showing item details, quantities, and expected shipment dates./// </summary>using Microsoft.Finance.Currency;using Microsoft.Sales.Customer;using Microsoft.Sales.Document;using Microsoft.Utilities;using System.Utilities;report 108 "Customer - Order Detail"{    ApplicationArea = Basic, Suite;    Caption = 'Customer - Order Detail';    ToolTip = 'View a list of orders divided by customer. The order amounts are totaled for each customer and for the entire list. The report can be used, for example, to obtain an overview of sales over the short term or to analyze possible shipment problems.';    DefaultRenderingLayout = Excel;    PreviewMode = PrintLayout;    UsageCategory = ReportsAndAnalysis;    dataset    {        dataitem(Customer; Customer)        {            PrintOnlyIfDetail = true;            RequestFilterFields = "No.", "Search Name", Priority;            column(ShipmentPeriodDate; StrSubstNo(Text000, PeriodText))            {            }#if not CLEAN27            column(CompanyName; COMPANYPROPERTY.DisplayName())            {                ObsoleteState = Pending;                ObsoleteReason = 'The RDLC layout has been replaced by the Excel layout and will be removed in a future release.';                ObsoleteTag = '27.0';            }            column(PageGroupNo; PageGroupNo)            {                ObsoleteState = Pending;                ObsoleteReason = 'The RDLC layout has been replaced by the Excel layout and will be removed in a future release.';                ObsoleteTag = '27.0';            }            column(CustOrderDetailCaption; CustOrderDetailCaptionLbl)            {                ObsoleteState = Pending;                ObsoleteReason = 'The RDLC layout has been replaced by the Excel layout and will be removed in a future release.';                ObsoleteTag = '27.0';            }            column(PageCaption; PageCaptionLbl)            {                ObsoleteState = Pending;                ObsoleteReason = 'The RDLC layout has been replaced by the Excel layout and will be removed in a future release.';                ObsoleteTag = '27.0';            }            column(ShipmentDateCaption; ShipmentDateCaptionLbl)            {                ObsoleteState = Pending;                ObsoleteReason = 'The RDLC layout has been replaced by the Excel layout and will be removed in a future release.';                ObsoleteTag = '27.0';            }            column(QtyOnBackOrderCaption; QtyOnBackOrderCaptionLbl)            {                ObsoleteState = Pending;                ObsoleteReason = 'The RDLC layout has been replaced by the Excel layout and will be removed in a future release.';                ObsoleteTag = '27.0';            }            column(OutstandingOrdersCaption; OutstandingOrdersCaptionLbl)            {                ObsoleteState = Pending;                ObsoleteReason = 'The RDLC layout has been replaced by the Excel layout and will be removed in a future release.';                ObsoleteTag = '27.0';            }#endif            column(PrintAmountsInLCY; PrintAmountsInLCY)            {            }            column(CustTableCapCustFilter; CustFilterText)            {            }            column(CustFilter; CustFilter)            {            }            column(SalesOrderLineFilter; SalesLineFilterText)            {            }            column(SalesLineFilter; SalesLineFilter)            {            }            column(No_Customer; "No.")            {                IncludeCaption = true;            }            column(Name_Customer; Name)            {                IncludeCaption = true;            }            column(AllAmtAreInLCYCaption; AllAmtAreInLCY)            {            }            column(CustomerNoAndName; CustomerNoAndName)            {            }            dataitem("Sales Header"; "Sales Header")            {                DataItemLink = "Bill-to Customer No." = field("No.");                DataItemTableView = sorting("Document Type", "Bill-to Customer No.", "Currency Code") where("Document Type" = const(Order));                RequestFilterFields = "No.";                RequestFilterHeading = 'Sales Order';                column(No_; "No.")                {                    IncludeCaption = true;                }                column(OrderNoAndDate; OrderNoAndDate)                {                }                dataitem("Sales Line"; "Sales Line")                {                    DataItemLinkReference = "Sales Header";                    DataItemLink = "Document Type" = field("Document Type"), "Document No." = field("No.");                    DataItemTableView = sorting("Document Type", "Bill-to Customer No.", "Currency Code") where("Document Type" = const(Order), "Outstanding Quantity" = filter(<> 0));                    RequestFilterFields = "Shipment Date";                    RequestFilterHeading = 'Sales Order Line';                    column(SalesHeaderNo; SalesHeader."No.")                    {                        IncludeCaption = true;                    }                    column(SalesHeaderOrderDate; SalesHeader."Order Date")                    {                        IncludeCaption = true;                    }                    column(Description_SalesLine; Description)                    {                        IncludeCaption = true;                    }                    column(No_SalesLine; "No.")                    {                        IncludeCaption = true;                    }                    column(Type_SalesLine; Type)                    {                        IncludeCaption = true;                    }                    column(ShipmentDate_SalesLine; Format("Shipment Date"))                    {                    }                    column(Quantity_SalesLine; Quantity)                    {                        IncludeCaption = true;                    }                    column(OutStandingQty_SalesLine; "Outstanding Quantity")                    {                        IncludeCaption = true;                    }                    column(BackOrderQty; BackOrderQty)                    {                        DecimalPlaces = 0 : 5;                    }                    column(UnitPrice_SalesLine; "Unit Price")                    {                        AutoFormatExpression = "Currency Code";                        AutoFormatType = 2;                        IncludeCaption = true;                    }                    column(LineDiscAmt_SalesLine; "Line Discount Amount")                    {                        IncludeCaption = true;                    }                    column(InvDiscAmt_SalesLine; "Inv. Discount Amount")                    {                        AutoFormatExpression = "Currency Code";                        AutoFormatType = 2;                        IncludeCaption = true;                    }                    column(SalesOrderAmount; SalesOrderAmount)                    {                        AutoFormatExpression = "Currency Code";                        AutoFormatType = 1;                    }                    column(SalesHeaderCurrCode; SalesHeader."Currency Code")                    {                    }                    trigger OnAfterGetRecord()                    var                        IsHandled: Boolean;                    begin                        ReportHasData := true;                        OrderNoAndDate := StrSubstNo(OrderNoAndDateLbl, "Sales Header"."No.", Format("Sales Header"."Order Date", 0, '<Closing><Day> <Month Text> <Year4>'));                        IsHandled := false;                        OnSalesLineOnAfterGetRecordOnBeforeNewOrder("Sales Line", IsHandled);                        if IsHandled then                            CurrReport.Skip();                        NewOrder := "Document No." <> SalesHeader."No.";                        if NewOrder then                            SalesHeader.Get(Enum::"Sales Document Type"::Order, "Document No.");                        if "Shipment Date" <= WorkDate() then                            BackOrderQty := "Outstanding Quantity"                        else                            BackOrderQty := 0;                        Currency.InitRoundingPrecision();                        if "VAT Calculation Type" in ["VAT Calculation Type"::"Normal VAT", "VAT Calculation Type"::"Reverse Charge VAT"] then                            SalesOrderAmount :=                              Round(                                (Amount + "VAT Base Amount" * "VAT %" / 100) * "Outstanding Quantity" / Quantity / (1 + "VAT %" / 100),                                Currency."Amount Rounding Precision")                        else                            SalesOrderAmount :=                              Round(                                "Outstanding Amount" / (1 + "VAT %" / 100),                                Currency."Amount Rounding Precision");                        SalesOrderAmountLCY := SalesOrderAmount;                        if SalesHeader."Currency Code" <> '' then begin                            if SalesHeader."Currency Factor" <> 0 then                                SalesOrderAmountLCY :=                                  Round(                                    CurrExchRate.ExchangeAmtFCYToLCY(                                      WorkDate(), SalesHeader."Currency Code",                                      SalesOrderAmountLCY, SalesHeader."Currency Factor"));                            if PrintAmountsInLCY then begin                                "Unit Price" :=                                  Round(                                    CurrExchRate.ExchangeAmtFCYToLCY(                                      WorkDate(), SalesHeader."Currency Code",                                      "Unit Price", SalesHeader."Currency Factor"));                                SalesOrderAmount := SalesOrderAmountLCY;                            end;                        end;                        if SalesHeader."Prices Including VAT" then begin                            "Unit Price" := "Unit Price" / (1 + "VAT %" / 100);                            "Inv. Discount Amount" := "Inv. Discount Amount" / (1 + "VAT %" / 100);                        end;                        "Inv. Discount Amount" := "Inv. Discount Amount" * "Outstanding Quantity" / Quantity;                        CurrencyCode2 := SalesHeader."Currency Code";                        if PrintAmountsInLCY then                            CurrencyCode2 := '';                        TempCurrencyTotalBuffer.UpdateTotal(                          CurrencyCode2,                          SalesOrderAmount,                          Counter1,                          Counter1);                    end;                    trigger OnPreDataItem()                    begin                        Clear(SalesOrderAmountLCY);                        Clear(SalesOrderAmount);                        "Sales Line".SetFilter("Document No.", "Sales Header"."No.");                    end;                }                trigger OnPreDataItem()                begin                    Clear(OrderNoAndDate);                end;                trigger OnAfterGetRecord()                begin                    CustomerNoAndName := Customer."No." + ' - ' + Customer.Name;                    if not SalesOrderHasMatchingLines("Sales Header"."No.") then                        CurrReport.Skip();                end;            }            dataitem("Integer"; "Integer")            {                DataItemTableView = sorting(Number) where(Number = filter(1 ..));                column(TotalAmt_CurrTotalBuff; TempCurrencyTotalBuffer."Total Amount")                {                    AutoFormatExpression = TempCurrencyTotalBuffer."Currency Code";                    AutoFormatType = 1;                }                column(CurrCode_CurrTotalBuff; TempCurrencyTotalBuffer."Currency Code")                {                }                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",                      Counter1,                      Counter1);                end;                trigger OnPostDataItem()                begin                    TempCurrencyTotalBuffer.DeleteAll();                end;            }#if not CLEAN28            trigger OnAfterGetRecord()            begin                if PrintOnlyOnePerPage then                    PageGroupNo := PageGroupNo + 1;            end;#endif            trigger OnPreDataItem()            begin                PageGroupNo := 1;                Clear(SalesOrderAmountLCY);            end;        }        dataitem(Integer2; "Integer")        {            DataItemTableView = sorting(Number) where(Number = filter(1 ..));            column(TotalAmt_CurrTotalBuff2; TempCurrencyTotalBuffer2."Total Amount")            {                AutoFormatExpression = TempCurrencyTotalBuffer2."Currency Code";                AutoFormatType = 1;            }            column(CurrCode_CurrTotalBuff2; TempCurrencyTotalBuffer2."Currency Code")            {            }#if not CLEAN27            column(TotalCaption; TotalCaptionLbl)            {                ObsoleteState = Pending;                ObsoleteReason = 'The RDLC layout has been replaced by the Excel layout and will be removed in a future release.';                ObsoleteTag = '27.0';            }#endif            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    {        AboutTitle = 'About Customer - Order Detail';        AboutText = 'Analyse your outstanding sales orders to understand your expected sales volume. Show all outstanding sales and highlight overdue sales lines for each customer.';        SaveValues = true;        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.';                    }#if not CLEAN28                    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.';                        ObsoleteState = Pending;                        ObsoleteReason = 'The New Page per Customer option is only supported by the RDLC layout which has been deprecated.';                        ObsoleteTag = '28.0';#if CLEAN27                        Visible = false;#endif                    }#endif                    field(PostingDateFilter; PostingDateFilter)                    {                        ApplicationArea = Basic, Suite;                        Caption = 'Posting Date Filter';                        ToolTip = 'Specifies the Posting Date Filter applied to this report.';                        Visible = false;                    }                }            }        }        actions        {        }        trigger OnQueryClosePage(CloseAction: Action): Boolean        begin            PostingDateFilter := "Sales Header".GetFilter("Date Filter");        end;    }    rendering    {        layout(Word)        {            Caption = 'Customer - Order Detail Word';            Type = Word;            LayoutFile = './Sales/Reports/CustomerOrderDetail.docx';            Summary = 'Report layout made for print. Use a Word editor to modify the layout.';        }        layout(Excel)        {            Caption = 'Customer - Order Detail Excel';            Type = Excel;            LayoutFile = './Sales/Reports/CustomerOrderDetail.xlsx';            Summary = 'Report layout primarily made for data analysis. Use an Excel editor to modify the layout.';        }#if not CLEAN27        layout(RDLC)        {            Caption = 'Customer - Order Detail RDLC (Obsolete)';            Type = RDLC;            LayoutFile = './Sales/Reports/CustomerOrderDetail.rdlc';            ObsoleteState = Pending;            ObsoleteReason = 'The RDLC layout has been replaced by the Excel layout and will be removed in a future release.';            ObsoleteTag = '27.0';            Summary = 'Report layout made in the legacy RDLC format. Use an RDLC editor to modify the layout.';        }#endif    }    labels    {        OrderNoCaption = 'Order No.';        DataRetrievedLbl = 'Data retrieved:';        CustOrderDetailLbl = 'Customer - Order Detail';        CustOrderDetailPrintLbl = 'Cust. Order Detail (Print)', MaxLength = 31, Comment = 'Excel worksheet name.';        CustOrderDetailAnalysisLbl = 'Cust. Order Detail (Analysis)', MaxLength = 31, Comment = 'Excel worksheet name.';        PostingDateFilterLbl = 'Posting Date Filter:';        // About the report labels        AboutTheReportLbl = 'About the report', MaxLength = 31, Comment = 'Excel worksheet name.';        EnvironmentLbl = 'Environment';        CompanyLbl = 'Company';        UserLbl = 'User';        RunOnLbl = 'Run on';        ReportNameLbl = 'Report name';        DocumentationLbl = 'Documentation';        BackOrderQtyLbl = 'Quantity on Back Order';        SalesOrderAmountLbl = 'Outstanding Orders';        CustomerNoLbl = 'Customer No.';        OrderNoLbl = 'Order No';        ShipmentDateLbl = 'Shipment Date';        TotalLbl = 'Total';    }    trigger OnPreRendering(var RenderingPayload: JsonObject)    begin        if not ReportHasData then            Error(EmptyReportDatasetErr);    end;    trigger OnPreReport()    var        FormatDocument: Codeunit "Format Document";    begin        CustFilter := FormatDocument.GetRecordFiltersWithCaptions(Customer);        SalesLineFilter := "Sales Line".GetFilters();        PeriodText := "Sales Line".GetFilter("Shipment Date");        SalesLineRequestFilter.CopyFilters("Sales Line");        if PrintAmountsInLCY then            AllAmtAreInLCY := AllAmtAreInLCYCaptionLbl;        if CustFilter <> '' then            CustFilterText := StrSubstNo(CustFilterLbl, CustFilter);        if SalesLineFilter <> '' then            SalesLineFilterText := StrSubstNo(Text001, SalesLineFilter);    end;    var        CurrExchRate: Record "Currency Exchange Rate";        TempCurrencyTotalBuffer: Record "Currency Total Buffer" temporary;        TempCurrencyTotalBuffer2: Record "Currency Total Buffer" temporary;        Currency: Record Currency;        CustomerNoAndName: Text;        CustFilter: Text;        CustFilterText: Text;        SalesLineFilter: Text;        SalesLineFilterText: Text;        OrderNoAndDate: Text;        AllAmtAreInLCY: Text;        PostingDateFilter: Text;        SalesOrderAmount: Decimal;        SalesOrderAmountLCY: Decimal;        PrintAmountsInLCY: Boolean;        PeriodText: Text;#if not CLEAN28        PrintOnlyOnePerPage: Boolean;#endif        BackOrderQty: Decimal;        NewOrder: Boolean;        OK: Boolean;        Counter1: Integer;        CurrencyCode2: Code[10];        PageGroupNo: Integer;        ReportHasData: Boolean;        EmptyReportDatasetErr: Label 'The report couldn''t be generated, because it was empty. Adjust your filters and try again.';#pragma warning disable AA0074#pragma warning disable AA0470        Text000: Label 'Shipment Date: %1';        Text001: Label 'Sales Order Line: %1';#pragma warning restore AA0470#pragma warning restore AA0074        AllAmtAreInLCYCaptionLbl: Label 'All amounts are in LCY';        CustFilterLbl: Label 'Customer: %1', Comment = '%1 Customer Name';        OrderNoAndDateLbl: Label 'Order No. %1 - %2', Comment = '%1 Sales Header No., %2 Order Date';#if not CLEAN27        CustOrderDetailCaptionLbl: Label 'Customer - Order Detail';        PageCaptionLbl: Label 'Page';        ShipmentDateCaptionLbl: Label 'Shipment Date';        QtyOnBackOrderCaptionLbl: Label 'Quantity on Back Order';        OutstandingOrdersCaptionLbl: Label 'Outstanding Orders';        TotalCaptionLbl: Label 'Total';#endif    protected var        SalesHeader: Record "Sales Header";        SalesLineRequestFilter: Record "Sales Line";#if not CLEAN28#pragma warning disable AS0072    /// <summary>    /// Initializes the report request options for the Customer Order Detail report.    /// </summary>    /// <param name="ShowAmountInLCY">True to show amounts in local currency.</param>    /// <param name="NewPagePerCustomer">True to start a new page per customer.</param>    [Obsolete('The New Page per Customer option is only supported by the RDLC layout which has been deprecated.', '28.0')]    procedure InitializeRequest(ShowAmountInLCY: Boolean; NewPagePerCustomer: Boolean)    begin        PrintAmountsInLCY := ShowAmountInLCY;        PrintOnlyOnePerPage := NewPagePerCustomer;    end;#pragma warning restore AS0072#endif    /// <summary>    /// Initializes the report request options for the Customer Order Detail report.    /// </summary>    /// <param name="ShowAmountInLCY">True to show amounts in local currency.</param>    procedure InitializeRequest(ShowAmountInLCY: Boolean)    begin        PrintAmountsInLCY := ShowAmountInLCY;    end;    local procedure SalesOrderHasMatchingLines(SalesHeaderNo: Code[20]): Boolean    var        SalesLine: Record "Sales Line";    begin        SalesLine.CopyFilters(SalesLineRequestFilter);        SalesLine.SetRange("Document Type", SalesLine."Document Type"::Order);        SalesLine.SetRange("Document No.", SalesHeaderNo);        SalesLine.SetFilter("Outstanding Quantity", '<>%1', 0);        exit(not SalesLine.IsEmpty());    end;    [IntegrationEvent(false, false)]    local procedure OnSalesLineOnAfterGetRecordOnBeforeNewOrder(var SalesLine: Record "Sales Line"; var IsHandled: Boolean)    begin    end;}