Report 5808 Item Age Composition - Value

Base Application, Microsoft.Inventory.Reports

src/Layers/W1/BaseApp/Inventory/Reports/ItemAgeCompositionValue.Report.al635 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.Inventory.Reports;using Microsoft.Inventory.Item;using Microsoft.Inventory.Ledger;using System.Utilities;report 5808 "Item Age Composition - Value"{    ApplicationArea = Basic, Suite;    Caption = 'Item Age Composition by Quantity and Value';    ToolTip = 'View, print, or save an overview of the current age composition of selected items in your inventory.';    UsageCategory = ReportsAndAnalysis;    DataAccessIntent = ReadOnly;    DefaultRenderingLayout = Excel;    dataset    {        dataitem(Item; Item)        {            DataItemTableView = sorting("No.") where(Type = const(Inventory));            RequestFilterFields = "No.", "Inventory Posting Group", "Statistics Group", "Location Filter";#if not CLEAN28            column(TodayFormatted; Format(Today, 0, 4))            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(CompanyName; COMPANYPROPERTY.DisplayName())            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(ItemTableCaptItemFilter; TableCaption + ': ' + ItemFilter)            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(ItemFilter; ItemFilter)            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(PeriodStartDate21; Format(PeriodStartDate[2] + 1))            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(PeriodStartDate3; Format(PeriodStartDate[3]))            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(PeriodStartDate31; Format(PeriodStartDate[3] + 1))            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(PeriodStartDate4; Format(PeriodStartDate[4]))            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(PeriodStartDate41; Format(PeriodStartDate[4] + 1))            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(PeriodStartDate5; Format(PeriodStartDate[5]))            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(InvtValueRTC1; InvtValueRTC[1])            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(InvtValueRTC2; InvtValueRTC[2])            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(InvtValueRTC5; InvtValueRTC[5])            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(InvtValueRTC4; InvtValueRTC[4])            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(InvtValueRTC3; InvtValueRTC[3])            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(TotalInvtValueRTC; TotalInvtValueRTC)            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(InvtValue1_Item; InvtValue[1])            {                AutoFormatType = 1;                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(InvtValue2_Item; InvtValue[2])            {                AutoFormatType = 1;                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(InvtValue3_Item; InvtValue[3])            {                AutoFormatType = 1;                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(InvtValue4_Item; InvtValue[4])            {                AutoFormatType = 1;                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(InvtValue5_Item; InvtValue[5])            {                AutoFormatType = 1;                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(TotalInvtValue_Item; TotalInvtValue_Item)            {                AutoFormatType = 1;                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(ItemAgeCompositionValueCaption; ItemAgeCompositionValueCaptionLbl)            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(CurrReportPageNoCaption; CurrReportPageNoCaptionLbl)            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(AfterCaption; AfterCaptionLbl)            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(BeforeCaption; BeforeCaptionLbl)            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(InventoryValueCaption; InventoryValueCaptionLbl)            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(ItemDescriptionCaption; ItemDescriptionCaptionLbl)            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(ItemNoCaption; ItemNoCaptionLbl)            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }            column(TotalCaption; TotalCaptionLbl)            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }#endif            column(PrintLine; PrintLine)            {            }            column(EndingDateText; Format(PeriodStartDate[5]))            {            }            column(Period1RangeText; Period1Text)            {            }            column(Period2RangeText; Period2Text)            {            }            column(Period3RangeText; Period3Text)            {            }            column(LocationFilterText; GetFilter("Location Filter"))            {            }            dataitem("Item Ledger Entry"; "Item Ledger Entry")            {                DataItemLink = "Item No." = field("No."), "Location Code" = field("Location Filter"), "Variant Code" = field("Variant Filter"), "Global Dimension 1 Code" = field("Global Dimension 1 Filter"), "Global Dimension 2 Code" = field("Global Dimension 2 Filter");                DataItemTableView = sorting("Item No.", Open) where(Open = const(true));                trigger OnAfterGetRecord()                begin                    if "Remaining Quantity" = 0 then                        CurrReport.Skip();                    PrintLine := true;                    CalcRemainingQty();                    RemainingQty += TotalInvtQty;                    if Item."Costing Method" = Item."Costing Method"::Average then begin                        InvtValue[i] += AverageCost[i] * InvtQty[i];                        InvtValueRTC[i] += AverageCost[i] * InvtQty[i];                    end else begin                        CalcUnitCost();                        TotalInvtValue_Item += UnitCost * Abs(TotalInvtQty);                        InvtValue[i] += UnitCost * Abs(InvtQty[i]);                        TotalInvtValueRTC += UnitCost * Abs(TotalInvtQty);                        InvtValueRTC[i] += UnitCost * Abs(InvtQty[i]);                    end;                    for j := 1 to 5 do                        InvtQtyTotal[j] += InvtQty[j];                end;                trigger OnPostDataItem()                var                    AvgCostCurr: Decimal;                begin                    if Item."Costing Method" = Item."Costing Method"::Average then begin                        AvgCostCurr := CalculateAverageCost(DMY2Date(31, 12, 9999));                        TotalInvtValue_Item := AvgCostCurr * RemainingQty;                        TotalInvtValueRTC += TotalInvtValue_Item;                    end;                end;                trigger OnPreDataItem()                begin                    TotalInvtValue_Item := 0;                    for i := 1 to 5 do                        InvtValue[i] := 0;                    RemainingQty := 0;                    Clear(InvtQtyTotal);                end;            }            dataitem("Integer"; "Integer")            {                DataItemTableView = sorting(Number) where(Number = const(1));                column(TotalInvtValue_ItemLedgEntry; TotalInvtValue_Item)                {                    AutoFormatType = 1;                }                column(InvtValue5_ItemLedgEntry; InvtValue[5])                {                    AutoFormatType = 1;                }                column(InvtValue4_ItemLedgEntry; InvtValue[4])                {                    AutoFormatType = 1;                }                column(InvtValue3_ItemLedgEntry; InvtValue[3])                {                    AutoFormatType = 1;                }                column(InvtValue2_ItemLedgEntry; InvtValue[2])                {                    AutoFormatType = 1;                }                column(InvtValue1_ItemLedgEntry; InvtValue[1])                {                    AutoFormatType = 1;                }                column(InvtQty1_ItemLedgEntry; InvtQtyTotal[1])                {                    DecimalPlaces = 0 : 2;                }                column(InvtQty2_ItemLedgEntry; InvtQtyTotal[2])                {                    DecimalPlaces = 0 : 2;                }                column(InvtQty3_ItemLedgEntry; InvtQtyTotal[3])                {                    DecimalPlaces = 0 : 2;                }                column(InvtQty4_ItemLedgEntry; InvtQtyTotal[4])                {                    DecimalPlaces = 0 : 2;                }                column(InvtQty5_ItemLedgEntry; InvtQtyTotal[5])                {                    DecimalPlaces = 0 : 2;                }                column(TotalInvtQty; RemainingQty)                {                    DecimalPlaces = 0 : 2;                }                column(Description_Item; Item.Description)                {                    IncludeCaption = true;                }                column(No_Item; Item."No.")                {                    IncludeCaption = true;                }                column(InventoryPostingGroup_Item; Item."Inventory Posting Group")                {                    IncludeCaption = true;                }                column(StatisticsGroup_Item; Item."Statistics Group")                {                    IncludeCaption = true;                }            }            trigger OnAfterGetRecord()            begin                if "Costing Method" = "Costing Method"::Average then begin                    for i := 2 to 5 do                        AverageCost[i] := CalculateAverageCost(PeriodStartDate[i + 1]);                    AverageCost[1] := CalculateAverageCost(PeriodStartDate[2]);                end;                PrintLine := false;            end;            trigger OnPreDataItem()            begin                Clear(InvtValue);                Clear(TotalInvtValue_Item);            end;        }    }    requestpage    {        AboutTitle = 'About Item Age Composition by Quantity and Value';        AboutText = 'Review the age of stock in your warehouse by value to determine obsolescence and identify slow moving inventory. View your open inventory value split across 5 aging buckets based on the period length and ending date. Filter the report by Location to determine the age of Inventory by warehouse.';        SaveValues = true;        layout        {            area(content)            {                group(Options)                {                    Caption = 'Options';                    field(EndingDate; PeriodStartDate[5])                    {                        ApplicationArea = Basic, Suite;                        Caption = 'Ending Date';                        ToolTip = 'Specifies the end date of the report. The report calculates backwards from this date and sets up three periods of the length specified in the Period Length field.';                        trigger OnValidate()                        begin                            if PeriodStartDate[5] = 0D then                                Error(Text002);                        end;                    }                    field(PeriodLength; PeriodLength)                    {                        ApplicationArea = Basic, Suite;                        Caption = 'Period Length';                        ToolTip = 'Specifies the length of the three periods in the report.';                        trigger OnValidate()                        begin                            if Format(PeriodLength) = '' then                                Evaluate(PeriodLength, '<0D>');                        end;                    }                    field(RequestPeriod1Text; Period1Text)                    {                        ApplicationArea = All;                        Caption = 'Period 1';                        ToolTip = 'Specifies Period 1 on this report.';                        Visible = false;                    }                    field(RequestPeriod2Text; Period2Text)                    {                        ApplicationArea = All;                        Caption = 'Period 2';                        ToolTip = 'Specifies Period 2 on this report.';                        Visible = false;                    }                    field(RequestPeriod3Text; Period3Text)                    {                        ApplicationArea = All;                        Caption = 'Period 3';                        ToolTip = 'Specifies Period 3 on this report.';                        Visible = false;                    }                }            }        }        actions        {        }        trigger OnOpenPage()        begin            if PeriodStartDate[5] = 0D then                PeriodStartDate[5] := CalcDate('<CM>', WorkDate());            if Format(PeriodLength) = '' then                Evaluate(PeriodLength, '<1M>');        end;        trigger OnClosePage()        var            NegPeriodLength: DateFormula;        begin            PeriodStartDate[6] := DMY2Date(31, 12, 9999);            Evaluate(NegPeriodLength, StrSubstNo('-%1', Format(PeriodLength)));            for i := 1 to 3 do                PeriodStartDate[5 - i] := CalcDate(NegPeriodLength, PeriodStartDate[6 - i]);            UpdateRequestPageFilterValues();        end;    }    rendering    {        layout(Excel)        {            Caption = 'Item Age Composition by Quantity and Value Excel';            Type = Excel;            LayoutFile = './Inventory/Reports/ItemAgeCompositionValue.xlsx';            Summary = 'Report layout primarily made for data analysis. Use an Excel editor to modify the layout.';        }#if not CLEAN28        layout(RDLC)        {            Caption = 'Item Age Composition by Quantity and Value RDLC';            Type = RDLC;            LayoutFile = './Inventory/Reports/ItemAgeCompositionValue.rdlc';            ObsoleteState = Pending;            ObsoleteReason = 'The RDLC layout has been replaced by the Excel layout and will be removed in a future release.';            ObsoleteTag = '28.0';            Summary = 'Report layout made in the legacy RDLC format. Use an RDLC editor to modify the layout.';        }#endif    }    labels    {        ItemAgeCompositionValueLbl = 'Item Age Composition - Value';        ItemAgeCompositionQtyLbl = 'Item Age Composition - Quantity';        ItemAgeComposValuePrintLbl = 'Item Age Com. - Val. (Print)', MaxLength = 31, Comment = 'Excel worksheet name.';        ItemAgeComposQtyPrintLbl = 'Item Age Com. - Qty. (Print)', MaxLength = 31, Comment = 'Excel worksheet name.';        ItemAgeComposValueAnalysisLbl = 'Item Age Com. - Val. (Analysis)', MaxLength = 31, Comment = 'Excel worksheet name.';        DataRetrievedLbl = 'Data retrieved:';        BeforeLbl = '...before';        AfterLbl = 'after...';        InvValueBeforeLbl = '...before (Inventory Value)';        InvValueAfterLbl = 'after... (Inventory Value)';        InvBeforeLbl = '...before (Inventory)';        InvAfterLbl = 'after... (Inventory)';        InventoryValue2Lbl = 'Inventory Value for Period 2';        InventoryValue3Lbl = 'Inventory Value for Period 3';        InventoryValue4Lbl = 'Inventory Value for Period 4';        Inventory2Lbl = 'Inventory for Period 2';        Inventory3Lbl = 'Inventory for Period 3';        Inventory4Lbl = 'Inventory for Period 4';        InventoryValueLbl = 'Inventory Value';        InventoryLbl = 'Inventory';        LocationFilterLbl = 'Location Filter';        // About the report labels        AboutTheReportLbl = 'About the report';        EnvironmentLbl = 'Environment';        CompanyLbl = 'Company';        UserLbl = 'User';        RunOnLbl = 'Run on';        ReportNameLbl = 'Report name';        DocumentationLbl = 'Documentation';    }    trigger OnPreReport()    var        NegPeriodLength: DateFormula;    begin        ItemFilter := Item.GetFilters();        PeriodStartDate[6] := DMY2Date(31, 12, 9999);        Evaluate(NegPeriodLength, StrSubstNo('-%1', Format(PeriodLength)));        for i := 1 to 3 do            PeriodStartDate[5 - i] := CalcDate(NegPeriodLength, PeriodStartDate[6 - i]);        UpdateRequestPageFilterValues();    end;    var        PeriodLength: DateFormula;        ItemFilter: Text;        InvtValue: array[6] of Decimal;        InvtValueRTC: array[6] of Decimal;        InvtQty: array[6] of Decimal;        InvtQtyTotal: array[5] of Decimal;        UnitCost: Decimal;        PeriodStartDate: array[6] of Date;        i: Integer;        j: Integer;        TotalInvtValue_Item: Decimal;        TotalInvtValueRTC: Decimal;        TotalInvtQty: Decimal;        PrintLine: Boolean;        AverageCost: array[5] of Decimal;        RemainingQty: Decimal;        Period1Text: Text;        Period2Text: Text;        Period3Text: Text;#pragma warning disable AA0074        Text002: Label 'Enter the ending date';#pragma warning restore AA0074#if not CLEAN28        ItemAgeCompositionValueCaptionLbl: Label 'Item Age Composition - Value';        CurrReportPageNoCaptionLbl: Label 'Page';        AfterCaptionLbl: Label 'After...';        BeforeCaptionLbl: Label '...Before';        InventoryValueCaptionLbl: Label 'Inventory Value';        ItemDescriptionCaptionLbl: Label 'Description';        ItemNoCaptionLbl: Label 'Item No.';        TotalCaptionLbl: Label 'Total';#endif    local procedure CalcRemainingQty()    begin        for i := 1 to 5 do            InvtQty[i] := 0;        TotalInvtQty := "Item Ledger Entry"."Remaining Quantity";        for i := 1 to 5 do            if ("Item Ledger Entry"."Posting Date" > PeriodStartDate[i]) and               ("Item Ledger Entry"."Posting Date" <= PeriodStartDate[i + 1])            then                if "Item Ledger Entry"."Remaining Quantity" <> 0 then begin                    InvtQty[i] := "Item Ledger Entry"."Remaining Quantity";                    exit;                end;    end;    local procedure CalcUnitCost()    var        ValueEntry: Record "Value Entry";    begin        ValueEntry.SetRange("Item Ledger Entry No.", "Item Ledger Entry"."Entry No.");        UnitCost := 0;        if ValueEntry.Find('-') then            repeat                if ValueEntry."Partial Revaluation" then                    SumUnitCost(UnitCost, ValueEntry."Cost Amount (Actual)" + ValueEntry."Cost Amount (Expected)", ValueEntry."Valued Quantity")                else                    SumUnitCost(UnitCost, ValueEntry."Cost Amount (Actual)" + ValueEntry."Cost Amount (Expected)", "Item Ledger Entry".Quantity);            until ValueEntry.Next() = 0;    end;    local procedure CalculateAverageCost(EndDate: Date): Decimal    var        ValueEntry: Record "Value Entry";        ItemLedgerEntry: Record "Item Ledger Entry";        Amount: Decimal;    begin        ValueEntry.SetRange("Item No.", Item."No.");        ValueEntry.SetRange("Valuation Date", 0D, EndDate);        ValueEntry.SetFilter("Location Code", Item.GetFilter("Location Filter"));        ValueEntry.SetFilter("Variant Code", Item.GetFilter("Variant Filter"));        ValueEntry.CalcSums("Cost Amount (Actual)", "Cost Amount (Expected)");        Amount := ValueEntry."Cost Amount (Actual)" + ValueEntry."Cost Amount (Expected)";        if Amount = 0 then            exit(0);        ItemLedgerEntry.SetRange("Item No.", Item."No.");        ItemLedgerEntry.SetRange("Posting Date", 0D, EndDate);        ItemLedgerEntry.SetFilter("Location Code", Item.GetFilter("Location Filter"));        ItemLedgerEntry.SetFilter("Variant Code", Item.GetFilter("Variant Filter"));        ItemLedgerEntry.SetRange(Open, true);        ItemLedgerEntry.CalcSums("Remaining Quantity");        if ItemLedgerEntry."Remaining Quantity" = 0 then            exit(0);        exit(Amount / ItemLedgerEntry."Remaining Quantity");    end;    local procedure SumUnitCost(var UnitCost: Decimal; CostAmount: Decimal; Quantity: Decimal)    begin        UnitCost := UnitCost + CostAmount / Abs(Quantity);    end;    procedure InitializeRequest(NewEndingDate: Date; NewPeriodLength: DateFormula)    begin        PeriodStartDate[5] := NewEndingDate;        PeriodLength := NewPeriodLength;    end;    local procedure UpdateRequestPageFilterValues()    begin        if (PeriodStartDate[2] <> 0D) and (PeriodStartDate[3] <> 0D) and (PeriodStartDate[4] <> 0D) then begin            Period1Text := Format(PeriodStartDate[2] + 1) + '..' + Format(PeriodStartDate[3]);            Period2Text := Format(PeriodStartDate[3] + 1) + '..' + Format(PeriodStartDate[4]);            Period3Text := Format(PeriodStartDate[4] + 1) + '..' + Format(PeriodStartDate[5]);        end;    end;}