Report 5801 Invt. Valuation - Cost Spec.

Base Application, Microsoft.Inventory.Reports

src/Layers/W1/BaseApp/Inventory/Reports/InvtValuationCostSpec.Report.al438 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.Costing;using Microsoft.Inventory.Item;using Microsoft.Inventory.Ledger;using System.Utilities;report 5801 "Invt. Valuation - Cost Spec."{    ApplicationArea = Basic, Suite;    Caption = 'Invt. Valuation - Cost Spec.';    ToolTip = 'View an overview of the current inventory value of selected items and specifies the cost of these items as of the date specified in the Valuation Date field. The report includes all costs, both those posted as invoiced and those posted as expected. For each of the items that you specify when setting up the report, the printed report shows quantity on stock, the cost per unit and the total amount. For each of these columns, the report specifies the cost as the various value entry types.';    UsageCategory = ReportsAndAnalysis;    DefaultRenderingLayout = RDLCLayout;    dataset    {        dataitem(Item; Item)        {            DataItemTableView = where(Type = const(Inventory));            PrintOnlyIfDetail = true;            RequestFilterFields = "No.", "Inventory Posting Group", "Statistics Group";            column(COMPANYNAME; COMPANYPROPERTY.DisplayName())            {            }            column(AsOfValuationDate; StrSubstNo(Text000, Format(ValuationDate)))            {            }            column(No_Item; "No.")            {            }            column(Description_Item; Description)            {            }            column(TotalCost; ResultForTotalCost)            {                AutoFormatType = 1;            }            dataitem("Integer"; "Integer")            {                DataItemTableView = sorting(Number) where(Number = filter(= 0));                column(AvgCost; ResultForAvgCost)                {                    AutoFormatType = 2;                }                column(RemainingQty; ResultForRemainingQty)                {                    DecimalPlaces = 0 : 5;                }                column(UnitCost1; ResultForUnitCost[1])                {                    AutoFormatType = 2;                }                column(UnitCost2; ResultForUnitCost[2])                {                    AutoFormatType = 2;                }                column(UnitCost3; ResultForUnitCost[3])                {                    AutoFormatType = 2;                }                column(UnitCost4; ResultForUnitCost[4])                {                    AutoFormatType = 2;                }                column(UnitCost5; ResultForUnitCost[5])                {                    AutoFormatType = 2;                }                column(TotalCostTotal1; ResultForTotalCostTotal[1])                {                    AutoFormatType = 1;                }                column(TotalCostTotal2; ResultForTotalCostTotal[2])                {                    AutoFormatType = 1;                }                column(TotalCostTotal3; ResultForTotalCostTotal[3])                {                    AutoFormatType = 1;                }                column(TotalCostTotal4; ResultForTotalCostTotal[4])                {                    AutoFormatType = 1;                }                column(TotalCostTotal5; ResultForTotalCostTotal[5])                {                    AutoFormatType = 1;                }                column(NoOfEntries1; NoOfEntries[1])                {                }                column(NoOfEntries2; NoOfEntries[2])                {                }                column(NoOfEntries3; NoOfEntries[3])                {                }                column(NoOfEntries4; NoOfEntries[4])                {                }                column(NoOfEntries5; NoOfEntries[5])                {                }                column(EntryTypeDescription1; EntryTypeDescription[1])                {                }                column(EntryTypeDescription2; EntryTypeDescription[2])                {                }                column(EntryTypeDescription3; EntryTypeDescription[3])                {                }                column(EntryTypeDescription4; EntryTypeDescription[4])                {                }                column(EntryTypeDescription5; EntryTypeDescription[5])                {                }                trigger OnAfterGetRecord()                var                    ItemLedgerEntry: Record "Item Ledger Entry";                begin                    ClearTotals();                    ItemLedgerEntry.SetRange("Item No.", Item."No.");                    ItemLedgerEntry.SetFilter("Variant Code", Item.GetFilter("Variant Filter"));                    ItemLedgerEntry.SetFilter("Location Code", Item.GetFilter("Location Filter"));                    ItemLedgerEntry.SetFilter("Global Dimension 1 Code", Item.GetFilter("Global Dimension 1 Filter"));                    ItemLedgerEntry.SetFilter("Global Dimension 2 Code", Item.GetFilter("Global Dimension 2 Filter"));                    ItemLedgerEntry.SetRange("Posting Date", 0D, ValuationDate);                    ItemLedgerEntry.SetRange("Drop Shipment", false);                    if not ItemLedgerEntry.FindSet() then                        CurrReport.Break();                    repeat                        ClearBufferVariables();                        IsPositive := GetSign(ItemLedgerEntry);                        CalcRemainingQty(ItemLedgerEntry);                        CalcUnitCost(ItemLedgerEntry);                        for i := 1 to ArrayLen(TotalCostTotal) do begin                            if Item."Costing Method" <> Item."Costing Method"::Average then                                TotalCostTotal[i] := TotalCostTotal[i] * Abs(RemainingQty);                            TotalCost := TotalCost + TotalCostTotal[i];                            TotalCostAvg[i] += TotalCostTotal[i];                        end;                        TotalRemAvg += RemainingQty;                        IncrTotals();                    until ItemLedgerEntry.Next() = 0;                    CalcAvgCost();                end;            }        }    }    requestpage    {        SaveValues = true;        layout        {            area(content)            {                group(Options)                {                    Caption = 'Options';                    field(ValuationDate; ValuationDate)                    {                        ApplicationArea = Basic, Suite;                        Caption = 'Valuation Date';                        ToolTip = 'Specifies the valuation date from which the entry is included in the average cost calculation.';                        trigger OnValidate()                        begin                            if ValuationDate = 0D then                                Error(Text001);                        end;                    }                }            }        }        actions        {        }        trigger OnOpenPage()        begin            if ValuationDate = 0D then                ValuationDate := WorkDate();        end;    }    rendering    {        layout(RDLCLayout)        {            Type = RDLC;            LayoutFile = './Inventory/Reports/InvtValuationCostSpec.rdlc';            Summary = 'Report layout made in the legacy RDLC format. Use an RDLC editor to modify the layout.';        }    }    labels    {        InventoryValuationCostSpecificationCaption = 'Inventory Valuation - Cost Specification';        PageCaption = 'Page';        OfCaption = 'of';        RemainingQtyCaption = 'Quantity';        CostPerUnitCaption = 'Cost per Unit';        AmountCaption = 'Amount';        EntryTypeCaption = 'Entry Type';        TotalCaption = 'Total';        ItemCaption = 'Item';        DescriptionCaption = 'Description';    }    trigger OnPreReport()    begin        if ValuationDate = 0D then            ValuationDate := WorkDate();        for i := 1 to ArrayLen(EntryTypeDescription) do begin            ValueEntry."Entry Type" := Enum::"Cost Entry Type".FromInteger(i - 1);            EntryTypeDescription[i] := Format(ValueEntry."Entry Type");        end;        ValueEntry.SetCurrentKey("Item Ledger Entry No.");        ValueEntry.SetRange("Posting Date", 0D, ValuationDate);        ValueEntry.SetFilter("Variant Code", Item.GetFilter("Variant Filter"));        ValueEntry.SetFilter("Location Code", Item.GetFilter("Location Filter"));        ValueEntry.SetFilter("Global Dimension 1 Code", Item.GetFilter("Global Dimension 1 Filter"));        ValueEntry.SetFilter("Global Dimension 2 Code", Item.GetFilter("Global Dimension 2 Filter"));    end;    var        ValueEntry: Record "Value Entry";        EntryTypeDescription: array[5] of Text[30];        i: Integer;        NoOfEntries: array[5] of Decimal;        PosQty: Decimal;        TotalCostTotal: array[5] of Decimal;        TotalRemAvg: Decimal;        TotalCostAvg: array[5] of Decimal;        TotalCost: Decimal;        IsPositive: Boolean;        ResultForTotalCostTotal: array[5] of Decimal;        ResultForUnitCost: array[5] of Decimal;        ResultForTotalCostAvg: array[5] of Decimal;        ResultForTotalCost: Decimal;        ResultForRemainingQty: Decimal;#pragma warning disable AA0074#pragma warning disable AA0470        Text000: Label 'As of %1';#pragma warning restore AA0470        Text001: Label 'Enter the valuation date.';#pragma warning restore AA0074    protected var        ValuationDate: Date;        ResultForTotalRemAvg: Decimal;        ResultForAvgCost: Decimal;        RemainingQty: Decimal;    local procedure CalcRemainingQty(ItemLedgerEntry: Record "Item Ledger Entry")    var        ItemApplnEntry: Record "Item Application Entry";        IsHandled: Boolean;    begin        IsHandled := false;        OnBeforeCalcRemainingQty(ItemLedgerEntry, RemainingQty, PosQty, IsPositive, ValuationDate, IsHandled);        if IsHandled then            exit;        RemainingQty := ItemLedgerEntry.Quantity;        if IsPositive then            PosQty := ItemLedgerEntry.Quantity;        if ItemLedgerEntry.Positive then begin            ItemApplnEntry.Reset();            ItemApplnEntry.SetCurrentKey("Inbound Item Entry No.", "Outbound Item Entry No.", "Cost Application");            ItemApplnEntry.SetRange("Inbound Item Entry No.", ItemLedgerEntry."Entry No.");            ItemApplnEntry.SetFilter("Outbound Item Entry No.", '<>%1', 0);            ItemApplnEntry.SetRange("Posting Date", 0D, ValuationDate);            if ItemApplnEntry.Find('-') then                repeat                    SumQty(RemainingQty, PosQty, ItemApplnEntry."Outbound Item Entry No.", ItemApplnEntry.Quantity);                until ItemApplnEntry.Next() = 0;        end else begin            ItemApplnEntry.Reset();            ItemApplnEntry.SetCurrentKey("Outbound Item Entry No.", "Item Ledger Entry No.", "Cost Application");            ItemApplnEntry.SetRange("Outbound Item Entry No.", ItemLedgerEntry."Entry No.");            ItemApplnEntry.SetRange("Item Ledger Entry No.", ItemLedgerEntry."Entry No.");            ItemApplnEntry.SetRange("Posting Date", 0D, ValuationDate);            if ItemApplnEntry.Find('-') then                repeat                    SumQty(RemainingQty, PosQty, ItemApplnEntry."Inbound Item Entry No.", -ItemApplnEntry.Quantity);                until ItemApplnEntry.Next() = 0;        end;        if IsPositive then            PosQty := RemainingQty;    end;    local procedure SumQty(var RemainingQty: Decimal; var PosQty: Decimal; EntryNo: Integer; AppliedQty: Decimal)    var        ItemLedgEntry: Record "Item Ledger Entry";        ShouldExit: Boolean;    begin        ItemLedgEntry.Get(EntryNo);        ShouldExit := (ItemLedgEntry.Quantity * AppliedQty < 0) or (ItemLedgEntry."Posting Date" > ValuationDate);        OnSumQtyOnAfterCheckExitCondition(ItemLedgEntry, AppliedQty, ValuationDate, ShouldExit);        if ShouldExit then            exit;        RemainingQty := RemainingQty + AppliedQty;        if IsPositive then            PosQty := PosQty + AppliedQty;    end;    local procedure CalcUnitCost(ItemLedgerEntry: Record "Item Ledger Entry")    begin        ValueEntry.SetRange("Item Ledger Entry No.", ItemLedgerEntry."Entry No.");        if ValueEntry.Find('-') then            repeat                if ValueEntry."Partial Revaluation" then                    SumUnitCost(TotalCostTotal[ValueEntry."Entry Type".AsInteger() + 1],                      ValueEntry."Cost Amount (Actual)" + ValueEntry."Cost Amount (Expected)", ValueEntry."Valued Quantity")                else                    SumUnitCost(TotalCostTotal[ValueEntry."Entry Type".AsInteger() + 1],                      ValueEntry."Cost Amount (Actual)" + ValueEntry."Cost Amount (Expected)", ItemLedgerEntry.Quantity);                NoOfEntries[ValueEntry."Entry Type".AsInteger() + 1] := 1;            until ValueEntry.Next() = 0;    end;    local procedure CalcAvgCost()    begin        for i := 1 to ArrayLen(NoOfEntries) do begin            if ResultForTotalRemAvg <> 0 then                ResultForUnitCost[i] := ResultForTotalCostAvg[i] / Abs(ResultForTotalRemAvg)            else                ResultForUnitCost[i] := 0;            ResultForAvgCost += ResultForUnitCost[i];        end;    end;    local procedure GetSign(ItemLedgerEntry: Record "Item Ledger Entry"): Boolean    var        ItemLedgEntry: Record "Item Ledger Entry";    begin        case ItemLedgerEntry."Entry Type" of            ItemLedgerEntry."Entry Type"::Purchase,              ItemLedgerEntry."Entry Type"::"Positive Adjmt.",              ItemLedgerEntry."Entry Type"::Output,              ItemLedgerEntry."Entry Type"::"Assembly Output":                exit(true);            ItemLedgerEntry."Entry Type"::Transfer:                begin                    if not ItemLedgerEntry.Positive then                        exit(false);                    ItemLedgEntry.CopyFilters(ItemLedgerEntry);                    ItemLedgEntry."Entry No." := ItemLedgerEntry."Entry No." - 1;                    exit(ItemLedgEntry.IsEmpty());                end;            else                exit(false)        end;    end;    local procedure SumUnitCost(var UnitCost: Decimal; CostAmount: Decimal; Quantity: Decimal)    begin        if Item."Costing Method" = Item."Costing Method"::Average then            UnitCost := UnitCost + CostAmount        else            UnitCost := UnitCost + CostAmount / Abs(Quantity);    end;    procedure InitializeRequest(NewValuationDate: Date)    begin        ValuationDate := NewValuationDate;    end;    local procedure ClearTotals()    begin        Clear(NoOfEntries);        Clear(ResultForTotalCostTotal);        Clear(ResultForUnitCost);        Clear(ResultForTotalCostAvg);        ResultForTotalRemAvg := 0;        ResultForTotalCost := 0;        ResultForRemainingQty := 0;        ResultForAvgCost := 0;    end;    local procedure ClearBufferVariables()    begin        Clear(TotalCostTotal);        Clear(TotalCostAvg);        TotalRemAvg := 0;        TotalCost := 0;        RemainingQty := 0;    end;    local procedure IncrTotals()    begin        for i := 1 to ArrayLen(TotalCostTotal) do begin            ResultForTotalCostTotal[i] += TotalCostTotal[i];            ResultForTotalCostAvg[i] += TotalCostAvg[i];        end;        ResultForTotalRemAvg += TotalRemAvg;        ResultForTotalCost += TotalCost;        ResultForRemainingQty += RemainingQty;    end;    [IntegrationEvent(false, false)]    local procedure OnBeforeCalcRemainingQty(ItemLedgerEntry: Record "Item Ledger Entry"; var RemainingQty: Decimal; var PosQty: Decimal; IsPositive: Boolean; ValuationDate: Date; var IsHandled: Boolean)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnSumQtyOnAfterCheckExitCondition(ItemLedgerEntry: Record "Item Ledger Entry"; AppliedQuantity: Decimal; ValuationDate: Date; var ShouldExit: Boolean)    begin    end;}