Report 5801 Invt. Valuation - Cost Spec.
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;}