Codeunit 9 Acc. Sched. KPI Dimensions, source in 29
Source29
src/Layers/W1/BaseApp/Finance/FinancialReports/AccSchedKPIDimensions.Codeunit.al955 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.Finance.FinancialReports;
using Microsoft.CashFlow.Account;
using Microsoft.CashFlow.Forecast;
using Microsoft.CostAccounting.Account;
using Microsoft.CostAccounting.Budget;
using Microsoft.CostAccounting.Ledger;
using Microsoft.Finance.Analysis;
using Microsoft.Finance.GeneralLedger.Account;
using Microsoft.Finance.GeneralLedger.Budget;
using Microsoft.Finance.GeneralLedger.Ledger;
/// <summary>
/// Processes account schedule KPI calculations with dimension filtering and analysis capabilities.
/// Handles complex financial data aggregation across multiple ledger types with dimension support.
/// </summary>
/// <remarks>
/// Primary functionality: KPI calculation with dimensions, multi-ledger data aggregation, filter processing.
/// Integration: Links with G/L, Cost, Cash Flow systems, Analysis Views, and Dimension Management.
/// Extensibility: Integration events for custom calculation logic and dimension handling.
/// </remarks>
codeunit 9 "Acc. Sched. KPI Dimensions"
{
TableNo = "Acc. Schedule Line";
trigger OnRun()
begin
end;
var
AccSchedName: Record "Acc. Schedule Name";
AccScheduleLine: Record "Acc. Schedule Line";
TempAccSchedKPIBuffer: Record "Acc. Sched. KPI Buffer" temporary;
AccSchedManagement: Codeunit AccSchedManagement;
StartDate: Date;
EndDate: Date;
CallLevel: Integer;
CallingAccSchedLineID: Integer;
CallingColumnLayoutID: Integer;
IllegalValErr: Label 'You have entered an illegal value or a nonexistent row number.';
GeneralErr: Label '%1\\ %2 %3 %4.', Locked = true;
ErrorOccurredErr: Label 'The error occurred when the program tried to calculate:\';
AccSchedLineErr: Label 'Acc. Sched. Line: Row No. = %1, Line No. = %2, Totaling = %3\', Comment = '%1 = Row No., %2= Line No., %3 = Totaling';
ColumnErr: Label 'Acc. Sched. Column: Column No. = %1, Line No. = %2, Formula = %3', Comment = '%1 = Column No., %2= Line No., %3 = Formula';
CircularRefErr: Label 'Because of circular references, the program cannot calculate a formula.';
/// <summary>
/// Calculates and populates KPI buffer with cell data including dimension filtering and analysis.
/// Processes account schedule line and column layout intersection with comprehensive dimension support.
/// </summary>
/// <param name="AccSchedLine">Account schedule line record defining row calculation parameters</param>
/// <param name="ColumnLayout">Column layout record defining column calculation parameters and data source</param>
/// <param name="TempAccSchedKPIBuffer2">Temporary KPI buffer to populate with calculated cell values</param>
procedure GetCellDataWithDimensions(var AccSchedLine: Record "Acc. Schedule Line"; var ColumnLayout: Record "Column Layout"; var TempAccSchedKPIBuffer2: Record "Acc. Sched. KPI Buffer" temporary)
var
LastDataLineNo: Integer;
begin
TempAccSchedKPIBuffer.Init();
TempAccSchedKPIBuffer.TransferFields(TempAccSchedKPIBuffer2, false);
if not TempAccSchedKPIBuffer.Insert() then
TempAccSchedKPIBuffer.Modify();
TempAccSchedKPIBuffer2.Reset();
if TempAccSchedKPIBuffer2.FindLast() then;
LastDataLineNo := TempAccSchedKPIBuffer2."No.";
AccScheduleLine.CopyFilters(AccSchedLine);
StartDate := AccScheduleLine.GetRangeMin("Date Filter");
if EndDate <> AccScheduleLine.GetRangeMax("Date Filter") then
EndDate := AccScheduleLine.GetRangeMax("Date Filter");
CallLevel := 0;
CallingAccSchedLineID := AccSchedLine."Line No.";
CallingColumnLayoutID := ColumnLayout."Line No.";
ColumnLayout.FindSet();
repeat
AddCellValueDimensions(AccSchedLine, ColumnLayout, TempAccSchedKPIBuffer2);
until ColumnLayout.Next() = 0;
TempAccSchedKPIBuffer2.Reset();
if TempAccSchedKPIBuffer2.FindLast() then;
if TempAccSchedKPIBuffer2."No." = LastDataLineNo then begin
TempAccSchedKPIBuffer2.Init();
TempAccSchedKPIBuffer2."No." += 1;
TempAccSchedKPIBuffer2.Date := TempAccSchedKPIBuffer.Date;
TempAccSchedKPIBuffer2."Closed Period" := TempAccSchedKPIBuffer."Closed Period";
TempAccSchedKPIBuffer2."Account Schedule Name" := TempAccSchedKPIBuffer."Account Schedule Name";
TempAccSchedKPIBuffer2."KPI Code" := TempAccSchedKPIBuffer."KPI Code";
TempAccSchedKPIBuffer2."KPI Name" := TempAccSchedKPIBuffer."KPI Name";
TempAccSchedKPIBuffer2.Insert();
end;
end;
local procedure AddCellValueDimensions(AccSchedLine: Record "Acc. Schedule Line"; ColumnLayout: Record "Column Layout"; var AccSchedKPIBuffer: Record "Acc. Sched. KPI Buffer")
var
GLAcc: Record "G/L Account";
CostType: Record "Cost Type";
CFAccount: Record "Cash Flow Account";
IsExpression: Boolean;
begin
if AccSchedLine.Totaling = '' then
exit;
if AccSchedName.Name <> AccSchedLine."Schedule Name" then
AccSchedName.Get(AccSchedLine."Schedule Name");
IsExpression :=
AccSchedLine."Totaling Type" in
[AccSchedLine."Totaling Type"::Formula, AccSchedLine."Totaling Type"::"Set Base For Percent"];
if IsExpression then
EvalExprWithDimensions(AccSchedLine.Totaling, AccSchedLine, ColumnLayout, AccSchedKPIBuffer)
else begin
if (StartDate = 0D) or (EndDate = 0D) or (EndDate = DMY2Date(31, 12, 9999)) then // Period Error
exit;
AccSchedManagement.SetStartDateEndDate(StartDate, EndDate);
if AccSchedLine."Totaling Type" in
[AccSchedLine."Totaling Type"::"Posting Accounts",
AccSchedLine."Totaling Type"::"Total Accounts"]
then begin
AccSchedLine.CopyFilters(AccScheduleLine);
AccSchedManagement.SetGLAccRowFilters(GLAcc, AccSchedLine);
AccSchedManagement.SetGLAccColumnFilters(GLAcc, AccSchedLine, ColumnLayout);
if (AccSchedLine."Totaling Type" = AccSchedLine."Totaling Type"::"Posting Accounts") and
(StrLen(AccSchedLine.Totaling) <= MaxStrLen(GLAcc.Totaling)) and (StrPos(AccSchedLine.Totaling, '*') = 0)
then begin
GLAcc."Account Type" := GLAcc."Account Type"::Total;
GLAcc.Totaling := AccSchedLine.Totaling;
AddGLAccDimensions(GLAcc, AccSchedLine, ColumnLayout, AccSchedKPIBuffer);
end else begin
GLAcc.SetLoadFields(Totaling, "Account Type");
if GLAcc.Find('-') then
repeat
AddGLAccDimensions(GLAcc, AccSchedLine, ColumnLayout, AccSchedKPIBuffer);
until GLAcc.Next() = 0;
end;
end;
if AccSchedLine."Totaling Type" in
[AccSchedLine."Totaling Type"::"Cost Type",
AccSchedLine."Totaling Type"::"Cost Type Total"]
then begin
AccSchedLine.CopyFilters(AccScheduleLine);
AccSchedManagement.SetCostTypeRowFilters(CostType, AccSchedLine, ColumnLayout);
AccSchedManagement.SetCostTypeColumnFilters(CostType, AccSchedLine, ColumnLayout);
if (AccSchedLine."Totaling Type" = AccSchedLine."Totaling Type"::"Cost Type") and
(StrLen(AccSchedLine.Totaling) <= MaxStrLen(GLAcc.Totaling)) and (StrPos(AccSchedLine.Totaling, '*') = 0)
then begin
CostType.Type := CostType.Type::Total;
CostType.Totaling := AccSchedLine.Totaling;
AddCostTypeDimensions(CostType, AccSchedLine, ColumnLayout, AccSchedKPIBuffer);
end else
if CostType.Find('-') then
repeat
AddCostTypeDimensions(CostType, AccSchedLine, ColumnLayout, AccSchedKPIBuffer);
until CostType.Next() = 0;
end;
if AccSchedLine."Totaling Type" in
[AccSchedLine."Totaling Type"::"Cash Flow Entry Accounts",
AccSchedLine."Totaling Type"::"Cash Flow Total Accounts"]
then begin
AccSchedLine.CopyFilters(AccScheduleLine);
AccSchedManagement.SetCFAccRowFilter(CFAccount, AccSchedLine);
AccSchedManagement.SetCFAccColumnFilter(CFAccount, AccSchedLine, ColumnLayout);
if (AccSchedLine."Totaling Type" = AccSchedLine."Totaling Type"::"Cash Flow Entry Accounts") and
(StrLen(AccSchedLine.Totaling) <= 30)
then begin
CFAccount."Account Type" := CFAccount."Account Type"::Total;
CFAccount.Totaling := AccSchedLine.Totaling;
AddCFAccountDimensions(CFAccount, AccSchedLine, ColumnLayout, AccSchedKPIBuffer);
end else
if CFAccount.Find('-') then
repeat
AddCFAccountDimensions(CFAccount, AccSchedLine, ColumnLayout, AccSchedKPIBuffer);
until CFAccount.Next() = 0;
end;
end;
end;
local procedure AddGLAccDimensions(var GLAcc: Record "G/L Account"; var AccSchedLine: Record "Acc. Schedule Line"; ColumnLayout: Record "Column Layout"; var AccSchedKPIBuffer: Record "Acc. Sched. KPI Buffer")
var
AnalysisViewEntry: Record "Analysis View Entry";
GLEntryDimensions: Query "G/L Entry Dimensions";
GLBudgetEntryDimensions: Query "G/L Budget Entry Dimensions";
AnalysisViewEntryDimensions: Query "Analysis View Entry Dimensions";
AnalysisViewBudgEntryDims: Query "Analysis View Budg. Entry Dims";
FilterText: Text;
AmountType: Enum "Account Schedule Amount Type";
AmountToAdd: Decimal;
begin
if ConflictAmountType(AccSchedLine, ColumnLayout."Amount Type", AmountType) then
exit;
case ColumnLayout."Ledger Entry Type" of
ColumnLayout."Ledger Entry Type"::Entries:
if AccSchedName."Analysis View Name" = '' then begin
GLEntryDimensions.SetFilter(Posting_Date, GLAcc.GetFilter("Date Filter"));
if GLAcc.Totaling = '' then
GLEntryDimensions.SetRange(G_L_Account_No, GLAcc."No.")
else
GLEntryDimensions.SetFilter(G_L_Account_No, GLAcc.Totaling);
GLEntryDimensions.SetFilter(
Business_Unit_Code, AccSchedLine.GetFilter("Business Unit Filter"));
FilterText := CombineFilters(
AccSchedLine.GetFilter("Dimension 1 Filter"),
AccSchedManagement.GetDimTotalingFilter(1, AccSchedLine."Dimension 1 Totaling"), '&');
GLEntryDimensions.SetFilter(Global_Dimension_1_Code, FilterText);
FilterText := CombineFilters(
AccSchedLine.GetFilter("Dimension 2 Filter"),
AccSchedManagement.GetDimTotalingFilter(2, AccSchedLine."Dimension 2 Totaling"), '&');
GLEntryDimensions.SetFilter(Global_Dimension_2_Code, FilterText);
GLEntryDimensions.Open();
while GLEntryDimensions.Read() do begin
case AmountType of
AmountType::"Net Amount":
AmountToAdd := GLEntryDimensions.Sum_Amount;
AmountType::"Debit Amount":
AmountToAdd := GLEntryDimensions.Sum_Debit_Amount;
AmountType::"Credit Amount":
AmountToAdd := GLEntryDimensions.Sum_Credit_Amount;
end;
if PassToResult(AccSchedLine.Show, GLEntryDimensions.Sum_Amount) then
CheckAddDimsToResult(
AccSchedKPIBuffer, ColumnLayout, GLEntryDimensions.Dimension_Set_ID, AmountToAdd);
end;
GLEntryDimensions.Close();
end else begin
if GLAcc.Totaling = '' then
FilterText := GLAcc."No."
else
FilterText := GLAcc.Totaling;
FilterAnalysisViewEntriesDim(
AnalysisViewEntryDimensions, AccSchedName."Analysis View Name",
AnalysisViewEntry."Account Source"::"G/L Account", FilterText,
GLAcc.GetFilter("Date Filter"), AccSchedLine);
AnalysisViewEntryDimensions.Open();
while AnalysisViewEntryDimensions.Read() do begin
case AmountType of
AmountType::"Net Amount":
AmountToAdd := AnalysisViewEntryDimensions.Sum_Amount;
AmountType::"Debit Amount":
AmountToAdd := AnalysisViewEntryDimensions.Sum_Debit_Amount;
AmountType::"Credit Amount":
AmountToAdd := AnalysisViewEntryDimensions.Sum_Credit_Amount;
end;
if PassToResult(AccSchedLine.Show, AnalysisViewEntryDimensions.Sum_Amount) then
CheckAddDimsToResult(AccSchedKPIBuffer, ColumnLayout, 0, AmountToAdd);
end;
AnalysisViewEntryDimensions.Close();
end;
ColumnLayout."Ledger Entry Type"::"Budget Entries":
if AccSchedName."Analysis View Name" = '' then begin
GLBudgetEntryDimensions.SetFilter(Budget_Name, AccSchedLine.GetFilter("G/L Budget Filter"));
GLBudgetEntryDimensions.SetFilter(Date, GLAcc.GetFilter("Date Filter"));
if GLAcc.Totaling = '' then
GLBudgetEntryDimensions.SetRange(G_L_Account_No, GLAcc."No.")
else
GLBudgetEntryDimensions.SetFilter(G_L_Account_No, GLAcc.Totaling);
GLBudgetEntryDimensions.SetFilter(
Business_Unit_Code, AccSchedLine.GetFilter("Business Unit Filter"));
FilterText := CombineFilters(
AccSchedLine.GetFilter("Dimension 1 Filter"),
AccSchedManagement.GetDimTotalingFilter(1, AccSchedLine."Dimension 1 Totaling"), '&');
GLBudgetEntryDimensions.SetFilter(Global_Dimension_1_Code, FilterText);
FilterText := CombineFilters(
AccSchedLine.GetFilter("Dimension 2 Filter"),
AccSchedManagement.GetDimTotalingFilter(2, AccSchedLine."Dimension 2 Totaling"), '&');
GLBudgetEntryDimensions.SetFilter(Global_Dimension_2_Code, FilterText);
GLBudgetEntryDimensions.Open();
while GLBudgetEntryDimensions.Read() do begin
case AmountType of
AmountType::"Net Amount":
AmountToAdd := GLBudgetEntryDimensions.Sum_Amount;
AmountType::"Debit Amount":
begin
AmountToAdd := GLBudgetEntryDimensions.Sum_Amount;
if AmountToAdd < 0 then
AmountToAdd := 0;
end;
AmountType::"Credit Amount":
begin
AmountToAdd := -GLBudgetEntryDimensions.Sum_Amount;
if AmountToAdd < 0 then
AmountToAdd := 0;
end;
end;
if PassToResult(AccSchedLine.Show, GLBudgetEntryDimensions.Sum_Amount) then
CheckAddDimsToResult(
AccSchedKPIBuffer, ColumnLayout, GLBudgetEntryDimensions.Dimension_Set_ID, AmountToAdd);
end;
GLBudgetEntryDimensions.Close();
end else begin
if GLAcc.Totaling = '' then
AnalysisViewBudgEntryDims.SetRange(G_L_Account_No, GLAcc."No.")
else
AnalysisViewBudgEntryDims.SetFilter(G_L_Account_No, GLAcc.Totaling);
AnalysisViewBudgEntryDims.SetRange(Analysis_View_Code, AccSchedName."Analysis View Name");
AnalysisViewBudgEntryDims.SetFilter(Budget_Name, AccSchedLine.GetFilter("G/L Budget Filter"));
AnalysisViewBudgEntryDims.SetFilter(Posting_Date, GLAcc.GetFilter("Date Filter"));
AnalysisViewBudgEntryDims.SetFilter(
Business_Unit_Code, AccSchedLine.GetFilter("Business Unit Filter"));
FilterText := CombineFilters(
AccSchedLine.GetFilter("Dimension 1 Filter"),
AccSchedManagement.GetDimTotalingFilter(1, AccSchedLine."Dimension 1 Totaling"), '&');
AnalysisViewBudgEntryDims.SetFilter(Dimension_1_Value_Code, FilterText);
FilterText := CombineFilters(
AccSchedLine.GetFilter("Dimension 2 Filter"),
AccSchedManagement.GetDimTotalingFilter(2, AccSchedLine."Dimension 2 Totaling"), '&');
AnalysisViewBudgEntryDims.SetFilter(Dimension_2_Value_Code, FilterText);
FilterText := CombineFilters(
AccSchedLine.GetFilter("Dimension 3 Filter"),
AccSchedManagement.GetDimTotalingFilter(3, AccSchedLine."Dimension 3 Totaling"), '&');
AnalysisViewBudgEntryDims.SetFilter(Dimension_3_Value_Code, FilterText);
FilterText := CombineFilters(
AccSchedLine.GetFilter("Dimension 4 Filter"),
AccSchedManagement.GetDimTotalingFilter(4, AccSchedLine."Dimension 4 Totaling"), '&');
AnalysisViewBudgEntryDims.SetFilter(Dimension_4_Value_Code, FilterText);
AnalysisViewBudgEntryDims.Open();
while AnalysisViewBudgEntryDims.Read() do begin
case AmountType of
AmountType::"Net Amount":
AmountToAdd := AnalysisViewBudgEntryDims.Sum_Amount;
AmountType::"Debit Amount":
begin
AmountToAdd := AnalysisViewBudgEntryDims.Sum_Amount;
if AmountToAdd < 0 then
AmountToAdd := 0;
end;
AmountType::"Credit Amount":
begin
AmountToAdd := -AnalysisViewBudgEntryDims.Sum_Amount;
if AmountToAdd < 0 then
AmountToAdd := 0;
end;
end;
if PassToResult(AccSchedLine.Show, AnalysisViewBudgEntryDims.Sum_Amount) then
CheckAddDimsToResult(AccSchedKPIBuffer, ColumnLayout, 0, AmountToAdd);
end;
AnalysisViewBudgEntryDims.Close();
end;
end;
end;
local procedure AddCostTypeDimensions(var CostType: Record "Cost Type"; var AccSchedLine: Record "Acc. Schedule Line"; ColumnLayout: Record "Column Layout"; var AccSchedKPIBuffer: Record "Acc. Sched. KPI Buffer")
var
CostEntry: Record "Cost Entry";
CostBudgetEntry: Record "Cost Budget Entry";
AmountType: Enum "Account Schedule Amount Type";
TestBalance: Boolean;
Balance: Decimal;
AmountToAdd: Decimal;
IsHandled: Boolean;
begin
IsHandled := false;
OnBeforeAddCostTypeDimensions(CostType, AccSchedLine, ColumnLayout, AccSchedKPIBuffer, IsHandled, TempAccSchedKPIBuffer);
if IsHandled then
exit;
if ConflictAmountType(AccSchedLine, ColumnLayout."Amount Type", AmountType) then
exit;
TestBalance := AccSchedLine.Show in
[AccSchedLine.Show::"When Negative Balance", AccSchedLine.Show::"When Positive Balance"];
if ColumnLayout."Ledger Entry Type" = ColumnLayout."Ledger Entry Type"::Entries then begin
if CostType.Totaling = '' then
CostEntry.SetRange("Cost Type No.", CostType."No.")
else
CostEntry.SetFilter("Cost Type No.", CostType.Totaling);
CostType.CopyFilter("Date Filter", CostEntry."Posting Date");
AccSchedLine.CopyFilter("Cost Center Filter", CostEntry."Cost Center Code");
AccSchedLine.CopyFilter("Cost Object Filter", CostEntry."Cost Object Code");
CostEntry.FilterGroup(2);
CostEntry.SetFilter(
CostEntry."Cost Center Code",
AccSchedManagement.GetDimTotalingFilter(5, AccSchedLine."Cost Center Totaling"));
CostEntry.SetFilter(
CostEntry."Cost Object Code",
AccSchedManagement.GetDimTotalingFilter(6, AccSchedLine."Cost Object Totaling"));
CostEntry.FilterGroup(0);
case AmountType of
AmountType::"Net Amount":
begin
CostEntry.CalcSums(Amount);
AmountToAdd := CostEntry.Amount;
Balance := AmountToAdd;
end;
AmountType::"Debit Amount":
begin
if TestBalance then begin
CostEntry.CalcSums("Debit Amount", Amount);
Balance := CostEntry.Amount;
end else
CostEntry.CalcSums("Debit Amount");
AmountToAdd := CostEntry."Debit Amount";
end;
AmountType::"Credit Amount":
begin
if TestBalance then begin
CostEntry.CalcSums("Credit Amount", Amount);
Balance := CostEntry.Amount;
end else
CostEntry.CalcSums("Credit Amount");
AmountToAdd := CostEntry."Credit Amount";
end;
end;
if not TestBalance then
CheckAddDimsToResult(AccSchedKPIBuffer, ColumnLayout, 0, AmountToAdd)
else
if PassToResult(AccSchedLine.Show, Balance) then
CheckAddDimsToResult(AccSchedKPIBuffer, ColumnLayout, 0, AmountToAdd);
end;
if ColumnLayout."Ledger Entry Type" = ColumnLayout."Ledger Entry Type"::"Budget Entries" then begin
CostBudgetEntry.SetCurrentKey("Budget Name", "Cost Type No.", "Cost Center Code", "Cost Object Code", Date);
if CostType.Totaling = '' then
CostBudgetEntry.SetRange("Cost Type No.", CostType."No.")
else
CostBudgetEntry.SetFilter("Cost Type No.", CostType.Totaling);
CostType.CopyFilter("Date Filter", CostBudgetEntry.Date);
AccSchedLine.CopyFilter("Cost Budget Filter", CostBudgetEntry."Budget Name");
AccSchedLine.CopyFilter("Cost Center Filter", CostBudgetEntry."Cost Center Code");
AccSchedLine.CopyFilter("Cost Object Filter", CostBudgetEntry."Cost Object Code");
CostBudgetEntry.FilterGroup(2);
CostBudgetEntry.SetFilter("Cost Center Code", AccSchedManagement.GetDimTotalingFilter(5, AccSchedLine."Cost Center Totaling"));
CostBudgetEntry.SetFilter("Cost Object Code", AccSchedManagement.GetDimTotalingFilter(6, AccSchedLine."Cost Object Totaling"));
CostBudgetEntry.FilterGroup(0);
CostBudgetEntry.CalcSums(Amount);
case AmountType of
AmountType::"Net Amount":
AmountToAdd := CostBudgetEntry.Amount;
AmountType::"Debit Amount":
if CostBudgetEntry.Amount > 0 then
AmountToAdd := CostBudgetEntry.Amount;
AmountType::"Credit Amount":
if CostBudgetEntry.Amount < 0 then
AmountToAdd := CostBudgetEntry.Amount;
end;
if not TestBalance then
CheckAddDimsToResult(AccSchedKPIBuffer, ColumnLayout, 0, AmountToAdd)
else
if PassToResult(AccSchedLine.Show, CostBudgetEntry.Amount) then
CheckAddDimsToResult(AccSchedKPIBuffer, ColumnLayout, 0, AmountToAdd);
end;
end;
local procedure AddCFAccountDimensions(var CFAccount: Record "Cash Flow Account"; var AccSchedLine: Record "Acc. Schedule Line"; ColumnLayout: Record "Column Layout"; var AccSchedKPIBuffer: Record "Acc. Sched. KPI Buffer")
var
AnalysisViewEntry: Record "Analysis View Entry";
CFForecastEntryDimensions: Query "CF Forecast Entry Dimensions";
AnalysisViewEntryDimensions: Query "Analysis View Entry Dimensions";
FilterText: Text;
AmountType: Enum "Account Schedule Amount Type";
AmountToAdd: Decimal;
begin
if ConflictAmountType(AccSchedLine, ColumnLayout."Amount Type", AmountType) then
exit;
if ColumnLayout."Ledger Entry Type" = ColumnLayout."Ledger Entry Type"::Entries then
if AccSchedName."Analysis View Name" = '' then begin
if CFAccount.Totaling = '' then
CFForecastEntryDimensions.SetRange(Cash_Flow_Account_No, CFAccount."No.")
else
CFForecastEntryDimensions.SetFilter(Cash_Flow_Account_No, CFAccount.Totaling);
CFForecastEntryDimensions.SetFilter(Cash_Flow_Date, CFAccount.GetFilter("Date Filter"));
CFForecastEntryDimensions.SetFilter(
Cash_Flow_Forecast_No, AccSchedLine.GetFilter("Cash Flow Forecast Filter"));
FilterText := CombineFilters(
AccSchedLine.GetFilter("Dimension 1 Filter"), AccSchedLine."Dimension 1 Totaling", '&');
CFForecastEntryDimensions.SetFilter(Global_Dimension_1_Code, FilterText);
FilterText := CombineFilters(
AccSchedLine.GetFilter("Dimension 2 Filter"), AccSchedLine."Dimension 2 Totaling", '&');
CFForecastEntryDimensions.SetFilter(Global_Dimension_2_Code, FilterText);
CFForecastEntryDimensions.Open();
while CFForecastEntryDimensions.Read() do begin
if AmountType = AmountType::"Net Amount" then
AmountToAdd := CFForecastEntryDimensions.Sum_Amount_LCY
else
AmountToAdd := 0;
CheckAddDimsToResult(
AccSchedKPIBuffer, ColumnLayout, CFForecastEntryDimensions.Dimension_Set_ID,
AmountToAdd);
end;
CFForecastEntryDimensions.Close();
end else begin
if CFAccount.Totaling = '' then
FilterText := CFAccount."No."
else
FilterText := CFAccount.Totaling;
FilterAnalysisViewEntriesDim(
AnalysisViewEntryDimensions, AccSchedName."Analysis View Name",
AnalysisViewEntry."Account Source"::"Cash Flow Account", FilterText,
CFAccount.GetFilter("Date Filter"), AccSchedLine);
AnalysisViewEntryDimensions.SetFilter(
Cash_Flow_Forecast_No, AccSchedLine.GetFilter("Cash Flow Forecast Filter"));
AnalysisViewEntryDimensions.Open();
while AnalysisViewEntryDimensions.Read() do begin
if AmountType = AmountType::"Net Amount" then
AmountToAdd := AnalysisViewEntryDimensions.Sum_Amount
else
AmountToAdd := 0;
CheckAddDimsToResult(AccSchedKPIBuffer, ColumnLayout, 0, AmountToAdd);
end;
AnalysisViewEntryDimensions.Close();
end;
end;
local procedure EvalExprWithDimensions(Expression: Text[250]; AccSchedLine: Record "Acc. Schedule Line"; ColumnLayout: Record "Column Layout"; var AccSchedKPIBuffer: Record "Acc. Sched. KPI Buffer")
var
TempAccSchedKPIBufferDim: Record "Acc. Sched. KPI Buffer" temporary;
SrcAccSchedLineFilter: Text;
Value: Decimal;
begin
SrcAccSchedLineFilter := GetAccSchedLineFormulaFilter(Expression, AccSchedLine);
GetExpressionDimensions(AccSchedKPIBuffer, TempAccSchedKPIBufferDim, SrcAccSchedLineFilter);
if TempAccSchedKPIBufferDim.FindSet() then
repeat
Value :=
EvalExprWithDimFilter(TempAccSchedKPIBufferDim."Dimension Set ID", Expression, AccSchedLine, ColumnLayout, AccSchedKPIBuffer);
CheckAddDimsToResult(
AccSchedKPIBuffer, ColumnLayout, TempAccSchedKPIBufferDim."Dimension Set ID", Value);
until TempAccSchedKPIBufferDim.Next() = 0;
end;
local procedure GetExpressionDimensions(var TempAccSchedKPIBufferExisting: Record "Acc. Sched. KPI Buffer" temporary; var TempAccSchedKPIBufferResulting: Record "Acc. Sched. KPI Buffer" temporary; LineFilter: Text)
begin
TempAccSchedKPIBufferExisting.SetFilter(TempAccSchedKPIBufferExisting."KPI Code", LineFilter);
if TempAccSchedKPIBufferExisting.FindSet() then
repeat
if (TempAccSchedKPIBufferExisting."Net Change Actual" <> 0) or (TempAccSchedKPIBufferExisting."Balance at Date Actual" <> 0) or
(TempAccSchedKPIBufferExisting."Net Change Budget" <> 0) or (TempAccSchedKPIBufferExisting."Balance at Date Budget" <> 0) or
(TempAccSchedKPIBufferExisting."Net Change Actual Last Year" <> 0) or (TempAccSchedKPIBufferExisting."Balance at Date Act. Last Year" <> 0) or
(TempAccSchedKPIBufferExisting."Net Change Budget Last Year" <> 0) or (TempAccSchedKPIBufferExisting."Balance at Date Bud. Last Year" <> 0)
then
AddDimsToBuffer(TempAccSchedKPIBufferResulting, TempAccSchedKPIBufferExisting."Dimension Set ID")
until TempAccSchedKPIBufferExisting.Next() = 0;
TempAccSchedKPIBufferExisting.SetRange("KPI Code");
end;
local procedure GetCellValueWithDimFilter(var TempAccSchedKPIBufferExisting: Record "Acc. Sched. KPI Buffer" temporary; AccSchedLine: Record "Acc. Schedule Line"; ColumnLayout: Record "Column Layout"; DimSetID: Integer) Result: Decimal
begin
TempAccSchedKPIBufferExisting.SetRange("Account Schedule Name", AccSchedLine."Schedule Name");
TempAccSchedKPIBufferExisting.SetRange("KPI Code", AccSchedLine."Row No.");
TempAccSchedKPIBufferExisting.SetRange("Dimension Set ID", DimSetID);
if TempAccSchedKPIBufferExisting.FindFirst() then
Result := TempAccSchedKPIBufferExisting.GetColumnValue(ColumnLayout);
TempAccSchedKPIBufferExisting.SetRange("Account Schedule Name");
TempAccSchedKPIBufferExisting.SetRange("KPI Code");
TempAccSchedKPIBufferExisting.SetRange("Dimension Set ID");
end;
local procedure GetAccSchedLineFormulaFilter(Expression: Text; AccSchedLine: Record "Acc. Schedule Line") ResultingFilter: Text
var
FilterPart: Text;
i: Integer;
j: Integer;
IsFilter: Boolean;
FoundFilterPart: Boolean;
begin
Expression := DelChr(Expression, '<>', ' ');
if StrLen(Expression) > 0 then begin
Expression := ConvertStr(Expression, '+-*/^%()', ';;;;;;;;');
i := StrLen(Expression);
while i > 0 do begin
if Expression[i] <> ';' then begin
FoundFilterPart := false;
j := i - 1;
repeat
if j = 0 then
FoundFilterPart := true;
if not FoundFilterPart then
FoundFilterPart := Expression[j] = ';';
if FoundFilterPart then begin
FilterPart := CopyStr(Expression, j + 1, i - j);
AccSchedLine.SetRange("Schedule Name", AccSchedLine."Schedule Name");
AccSchedLine.SetFilter("Row No.", FilterPart);
IsFilter :=
(StrPos(FilterPart, '..') +
StrPos(FilterPart, '|') +
StrPos(FilterPart, '<') +
StrPos(FilterPart, '>') +
StrPos(FilterPart, '&') +
StrPos(FilterPart, '=') > 0);
if (StrLen(FilterPart) <= 10) or IsFilter then
if AccSchedLine.FindFirst() then
ResultingFilter := CombineFilters(ResultingFilter, FilterPart, '|');
i := j;
end else
j -= 1;
until FoundFilterPart;
end;
i -= 1;
end;
end;
end;
local procedure EvalExprWithDimFilter(DimSetID: Integer; Expression: Text[250]; AccSchedLine: Record "Acc. Schedule Line"; ColumnLayout: Record "Column Layout"; var ExistingAccSchedKPIBuffer: Record "Acc. Sched. KPI Buffer"): Decimal
var
Result: Decimal;
Parantheses: Integer;
Operator: Char;
LeftOperand: Text[250];
RightOperand: Text[250];
LeftResult: Decimal;
RightResult: Decimal;
i: Integer;
IsExpression: Boolean;
IsFilter: Boolean;
Operators: Text[8];
OperatorNo: Integer;
AccSchedLineID: Integer;
begin
Result := 0;
CallLevel := CallLevel + 1;
if CallLevel > 25 then
ShowError(CircularRefErr, AccSchedLine, ColumnLayout);
Expression := DelChr(Expression, '<>', ' ');
if StrLen(Expression) > 0 then begin
Parantheses := 0;
IsExpression := false;
Operators := '+-*/^%';
OperatorNo := 1;
repeat
i := StrLen(Expression);
repeat
if Expression[i] = '(' then
Parantheses := Parantheses + 1
else
if Expression[i] = ')' then
Parantheses := Parantheses - 1;
if (Parantheses = 0) and (Expression[i] = Operators[OperatorNo]) then
IsExpression := true
else
i := i - 1;
until IsExpression or (i <= 0);
if not IsExpression then
OperatorNo := OperatorNo + 1;
until (OperatorNo > StrLen(Operators)) or IsExpression;
if IsExpression then begin
if i > 1 then
LeftOperand := CopyStr(Expression, 1, i - 1)
else
LeftOperand := '';
if i < StrLen(Expression) then
RightOperand := CopyStr(Expression, i + 1)
else
RightOperand := '';
Operator := Expression[i];
LeftResult :=
EvalExprWithDimFilter(
DimSetID, LeftOperand, AccSchedLine, ColumnLayout, ExistingAccSchedKPIBuffer);
RightResult :=
EvalExprWithDimFilter(
DimSetID, RightOperand, AccSchedLine, ColumnLayout, ExistingAccSchedKPIBuffer);
case Operator of
'^':
Result := Power(LeftResult, RightResult);
'%':
if RightResult = 0 then // Division Error
Result := 0
else
Result := 100 * LeftResult / RightResult;
'*':
Result := LeftResult * RightResult;
'/':
if RightResult = 0 then // Division Error
Result := 0
else
Result := LeftResult / RightResult;
'+':
Result := LeftResult + RightResult;
'-':
Result := LeftResult - RightResult;
end;
end else
if (Expression[1] = '(') and (Expression[StrLen(Expression)] = ')') then
Result :=
EvalExprWithDimFilter(
DimSetID, CopyStr(Expression, 2, StrLen(Expression) - 2),
AccSchedLine, ColumnLayout, ExistingAccSchedKPIBuffer)
else begin
IsFilter :=
(StrPos(Expression, '..') +
StrPos(Expression, '|') +
StrPos(Expression, '<') +
StrPos(Expression, '>') +
StrPos(Expression, '&') +
StrPos(Expression, '=') > 0);
if (StrLen(Expression) > 10) and (not IsFilter) then
Evaluate(Result, Expression)
else begin
AccSchedLine.SetRange("Schedule Name", AccSchedLine."Schedule Name");
AccSchedLine.SetFilter("Row No.", Expression);
AccSchedLineID := AccSchedLine."Line No.";
if AccSchedLine.Find('-') then
repeat
if AccSchedLine."Line No." <> AccSchedLineID then
Result +=
GetCellValueWithDimFilter(ExistingAccSchedKPIBuffer, AccSchedLine, ColumnLayout, DimSetID);
until AccSchedLine.Next() = 0
else
if IsFilter or (not Evaluate(Result, Expression)) then
ShowError(IllegalValErr, AccSchedLine, ColumnLayout);
end;
end;
end;
CallLevel := CallLevel - 1;
exit(Result);
end;
local procedure ShowError(MessageLine: Text[100]; var AccSchedLine: Record "Acc. Schedule Line"; var ColumnLayout: Record "Column Layout")
begin
AccSchedLine.SetRange("Schedule Name", AccSchedLine."Schedule Name");
AccSchedLine.SetRange("Line No.", CallingAccSchedLineID);
if AccSchedLine.FindFirst() then;
ColumnLayout.SetRange("Column Layout Name", ColumnLayout."Column Layout Name");
ColumnLayout.SetRange("Line No.", CallingColumnLayoutID);
if ColumnLayout.FindFirst() then;
Error(GeneralErr,
MessageLine,
ErrorOccurredErr,
StrSubstNo(AccSchedLineErr, AccSchedLine."Row No.", AccSchedLine."Line No.", AccSchedLine.Totaling),
StrSubstNo(ColumnErr, ColumnLayout."Column No.", ColumnLayout."Line No.", ColumnLayout.Formula));
end;
/// <summary>
/// Determines if there is a conflict between account schedule line and column layout amount types.
/// Resolves amount type conflicts and returns the appropriate amount type for calculations.
/// </summary>
/// <param name="AccSchedLine">Account schedule line with amount type configuration</param>
/// <param name="ColumnLayoutAmtType">Column layout amount type to compare against</param>
/// <param name="AmountType">Variable to receive the resolved amount type for calculations</param>
/// <returns>True if there is a conflict that requires resolution, false if amount types are compatible</returns>
procedure ConflictAmountType(AccSchedLine: Record "Acc. Schedule Line"; ColumnLayoutAmtType: Enum "Account Schedule Amount Type"; var AmountType: Enum "Account Schedule Amount Type"): Boolean
begin
if (ColumnLayoutAmtType = AccSchedLine."Amount Type") or
(AccSchedLine."Amount Type" = AccSchedLine."Amount Type"::"Net Amount")
then
AmountType := ColumnLayoutAmtType
else
if ColumnLayoutAmtType = ColumnLayoutAmtType::"Net Amount" then
AmountType := AccSchedLine."Amount Type"
else
exit(true);
exit(false);
end;
/// <summary>
/// Combines two filter text parts using the specified combination symbol for complex filtering.
/// Prepares and merges filter expressions with proper formatting and logical operators.
/// </summary>
/// <param name="FilterPart1">First filter text part to combine</param>
/// <param name="FilterPart2">Second filter text part to combine</param>
/// <param name="CombineSymbol">Logical operator symbol to use for combination (e.g., '&', '|')</param>
/// <returns>Combined filter text with proper formatting and logical operators</returns>
procedure CombineFilters(FilterPart1: Text; FilterPart2: Text; CombineSymbol: Text[1]): Text
begin
PrepareFilterPart(FilterPart1);
PrepareFilterPart(FilterPart2);
if (FilterPart1 <> '') and (FilterPart2 <> '') then
exit(StrSubstNo('%1%2%3', FilterPart1, CombineSymbol, FilterPart2));
if FilterPart1 <> '' then
exit(FilterPart1);
if FilterPart2 <> '' then
exit(FilterPart2);
exit('');
end;
/// <summary>
/// Prepares filter text part by trimming spaces and adding parentheses for proper logical grouping.
/// Ensures filter expressions are properly formatted for combination with other filter parts.
/// </summary>
/// <param name="FilterText">Filter text to prepare and format for logical operations</param>
procedure PrepareFilterPart(var FilterText: Text)
begin
FilterText := DelChr(FilterText, '<>', ' ');
if FilterText <> '' then
if (FilterText[1] <> '(') and (FilterText[StrLen(FilterText)] <> ')') then
FilterText := StrSubstNo('(%1)', FilterText);
end;
local procedure CheckAddDimsToResult(var AccSchedKPIBuffer: Record "Acc. Sched. KPI Buffer"; ColumnLayout: Record "Column Layout"; DimensionSetID: Integer; Amount: Decimal)
begin
if Amount = 0 then
exit;
AccSchedKPIBuffer.SetRange("Account Schedule Name", TempAccSchedKPIBuffer."Account Schedule Name");
AccSchedKPIBuffer.SetRange("KPI Code", TempAccSchedKPIBuffer."KPI Code");
AccSchedKPIBuffer.SetRange("Dimension Set ID", DimensionSetID);
if not AccSchedKPIBuffer.FindFirst() then begin
AccSchedKPIBuffer.Reset();
if AccSchedKPIBuffer.FindLast() then;
AccSchedKPIBuffer.Init();
AccSchedKPIBuffer."No." += 1;
AccSchedKPIBuffer.Date := TempAccSchedKPIBuffer.Date;
AccSchedKPIBuffer."Closed Period" := TempAccSchedKPIBuffer."Closed Period";
AccSchedKPIBuffer."Account Schedule Name" := TempAccSchedKPIBuffer."Account Schedule Name";
AccSchedKPIBuffer."KPI Code" := TempAccSchedKPIBuffer."KPI Code";
AccSchedKPIBuffer."KPI Name" := TempAccSchedKPIBuffer."KPI Name";
AccSchedKPIBuffer."Dimension Set ID" := DimensionSetID;
AccSchedKPIBuffer.AddColumnValue(ColumnLayout, Amount);
AccSchedKPIBuffer.Insert();
end else begin
AccSchedKPIBuffer.AddColumnValue(ColumnLayout, Amount);
AccSchedKPIBuffer.Modify();
end;
AccSchedKPIBuffer.SetRange("Account Schedule Name");
AccSchedKPIBuffer.SetRange("KPI Code");
AccSchedKPIBuffer.SetRange("Dimension Set ID");
end;
local procedure AddDimsToBuffer(var TempAccSchedKPIBufferResulting: Record "Acc. Sched. KPI Buffer" temporary; DimensionSetID: Integer)
begin
TempAccSchedKPIBufferResulting.SetRange("Dimension Set ID", DimensionSetID);
if not TempAccSchedKPIBufferResulting.FindFirst() then begin
TempAccSchedKPIBufferResulting.Reset();
if TempAccSchedKPIBufferResulting.FindLast() then;
TempAccSchedKPIBufferResulting."No." += 1;
TempAccSchedKPIBufferResulting."Dimension Set ID" := DimensionSetID;
TempAccSchedKPIBufferResulting.Insert();
end;
TempAccSchedKPIBufferResulting.SetRange("Dimension Set ID");
end;
/// <summary>
/// Post-processes calculated amount based on account schedule line configuration.
/// Applies sign reversal if specified in the account schedule line settings.
/// </summary>
/// <param name="AccSchedLine">Account schedule line record containing processing configuration</param>
/// <param name="Amount">Calculated amount to post-process</param>
/// <returns>Processed amount with appropriate sign adjustment applied</returns>
procedure PostProcessAmount(AccSchedLine: Record "Acc. Schedule Line"; Amount: Decimal): Decimal
begin
if AccSchedLine."Show Opposite Sign" then
exit(-Amount);
exit(Amount);
end;
/// <summary>
/// Determines whether a calculated balance passes the display criteria based on account schedule line show option.
/// Evaluates balance against positive/negative/zero conditions for conditional display logic.
/// </summary>
/// <param name="AccSchedLineShow">Show option enum defining the display condition criteria</param>
/// <param name="Balance">Calculated balance amount to evaluate against display criteria</param>
/// <returns>True if balance meets the display criteria, false if it should be hidden</returns>
procedure PassToResult(AccSchedLineShow: Enum "Acc. Schedule Line Show"; Balance: Decimal) BalanceIsOK: Boolean
begin
BalanceIsOK := true;
case AccSchedLineShow of
AccSchedLineShow::"When Positive Balance":
if Balance > 0 then
BalanceIsOK := true
else
BalanceIsOK := false;
AccSchedLineShow::"When Negative Balance":
if Balance < 0 then
BalanceIsOK := true
else
BalanceIsOK := false;
end;
exit(BalanceIsOK);
end;
local procedure FilterAnalysisViewEntriesDim(var AnalysisViewEntryDimensions: Query "Analysis View Entry Dimensions"; AnalysisViewName: Code[10]; AccountSource: Enum "Analysis Account Source"; AccountFilter: Text; DateFilter: Text; AccScheduleLine2: Record "Acc. Schedule Line")
begin
AnalysisViewEntryDimensions.SetRange(Analysis_View_Code, AnalysisViewName);
AnalysisViewEntryDimensions.SetRange(Account_Source, AccountSource);
AnalysisViewEntryDimensions.SetFilter(Account_No, AccountFilter);
AnalysisViewEntryDimensions.SetFilter(Business_Unit_Code, AccScheduleLine2.GetFilter("Business Unit Filter"));
AnalysisViewEntryDimensions.SetFilter(Posting_Date, DateFilter);
AnalysisViewEntryDimensions.SetFilter(Dimension_1_Value_Code,
CombineFilters(
AccScheduleLine2.GetFilter("Dimension 1 Filter"),
AccSchedManagement.GetDimTotalingFilter(1, AccScheduleLine2."Dimension 1 Totaling"), '&'));
AnalysisViewEntryDimensions.SetFilter(Dimension_2_Value_Code,
CombineFilters(
AccScheduleLine2.GetFilter("Dimension 2 Filter"),
AccSchedManagement.GetDimTotalingFilter(2, AccScheduleLine2."Dimension 2 Totaling"), '&'));
AnalysisViewEntryDimensions.SetFilter(Dimension_3_Value_Code,
CombineFilters(
AccScheduleLine2.GetFilter("Dimension 3 Filter"),
AccSchedManagement.GetDimTotalingFilter(3, AccScheduleLine2."Dimension 3 Totaling"), '&'));
AnalysisViewEntryDimensions.SetFilter(Dimension_4_Value_Code,
CombineFilters(
AccScheduleLine2.GetFilter("Dimension 4 Filter"),
AccSchedManagement.GetDimTotalingFilter(4, AccScheduleLine2."Dimension 4 Totaling"), '&'));
end;
/// <summary>
/// Sets the temporary KPI buffer for dimension calculations and data aggregation.
/// Copies the provided buffer to the internal temporary buffer for processing operations.
/// </summary>
/// <param name="NewTempAccSchedKPIBuffer">Temporary KPI buffer to copy for dimension calculations</param>
procedure SetTempAccSchedKPIBuffer(var NewTempAccSchedKPIBuffer: Record "Acc. Sched. KPI Buffer" temporary)
begin
TempAccSchedKPIBuffer.Copy(NewTempAccSchedKPIBuffer, true);
end;
/// <summary>
/// Integration event raised before adding cost type dimensions to KPI calculations.
/// Enables custom handling of cost type dimension processing and calculation logic.
/// </summary>
/// <param name="CostType">Cost type record being processed for dimension analysis</param>
/// <param name="AccSchedLine">Account schedule line record defining calculation parameters</param>
/// <param name="ColumnLayout">Column layout record providing calculation context</param>
/// <param name="AccSchedKPIBuffer">KPI buffer record to populate with calculated values</param>
/// <param name="IsHandled">Set to true to skip standard cost type dimension processing</param>
/// <param name="TempAccSchedKPIBuffer">Temporary KPI buffer for intermediate calculations</param>
[IntegrationEvent(false, false)]
local procedure OnBeforeAddCostTypeDimensions(var CostType: Record "Cost Type"; var AccSchedLine: Record "Acc. Schedule Line"; ColumnLayout: Record "Column Layout"; var AccSchedKPIBuffer: Record "Acc. Sched. KPI Buffer"; var IsHandled: Boolean; var TempAccSchedKPIBuffer: Record "Acc. Sched. KPI Buffer" temporary)
begin
end;
}