Report 14 Consolidation - Test Database, source in 29
Source29
src/Layers/W1/BaseApp/Finance/Consolidation/ConsolidationTestDatabase.Report.al605 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.Consolidation;
using Microsoft.Finance.Dimension;
using Microsoft.Finance.GeneralLedger.Account;
using Microsoft.Finance.GeneralLedger.Ledger;
using Microsoft.Finance.GeneralLedger.Setup;
using Microsoft.Projects.Project.Job;
using System.Utilities;
/// <summary>
/// Tests and validates consolidation database connectivity and data consistency within the same environment.
/// Analyzes database consolidation setup to identify configuration issues and data availability problems.
/// </summary>
/// <remarks>
/// Database connectivity validation report for same-environment consolidation ensuring proper setup and access.
/// Validates G/L account availability, dimension consistency, and exchange rate configuration.
/// Critical for ensuring database-based consolidation processes can access required subsidiary data.
/// </remarks>
report 14 "Consolidation - Test Database"
{
Caption = 'Consolidation - Test Database (same environment)';
DefaultRenderingLayout = RDLCLayout;
dataset
{
dataitem("Business Unit"; "Business Unit")
{
DataItemTableView = sorting(Code) where(Consolidate = const(true));
RequestFilterFields = "Code";
column(Business_Unit_Code; Code)
{
}
dataitem(Header; "Integer")
{
DataItemTableView = sorting(Number) where(Number = const(1));
column(COMPANYNAME; COMPANYPROPERTY.DisplayName())
{
}
column(STRSUBSTNO_Text009_ConsolidStartDate_ConsolidEndDate_; StrSubstNo(Text009, ConsolidStartDate, ConsolidEndDate))
{
}
column(Business_Unit__Code; "Business Unit".Code)
{
}
column(Business_Unit___Company_Name_; "Business Unit"."Company Name")
{
}
column(Business_Unit___Consolidation___; "Business Unit"."Consolidation %")
{
}
column(Business_Unit___Currency_Code_; "Business Unit"."Currency Code")
{
}
column(Business_Unit___Currency_Exchange_Rate_Table_; "Business Unit"."Currency Exchange Rate Table")
{
}
column(Business_Unit___Data_Source_; "Business Unit"."Data Source")
{
}
column(Print_control; Print_control)
{
}
column(Consolidation___Test_DatabaseCaption; Consolidation___Test_DatabaseCaptionLbl)
{
}
column(CurrReport_PAGENOCaption; CurrReport_PAGENOCaptionLbl)
{
}
column(Business_Unit__CodeCaption; "Business Unit".FieldCaption(Code))
{
}
column(Business_Unit___Company_Name_Caption; "Business Unit".FieldCaption("Company Name"))
{
}
column(Business_Unit___Consolidation___Caption; "Business Unit".FieldCaption("Consolidation %"))
{
}
column(Business_Unit___Currency_Code_Caption; "Business Unit".FieldCaption("Currency Code"))
{
}
column(Business_Unit___Currency_Exchange_Rate_Table_Caption; "Business Unit".FieldCaption("Currency Exchange Rate Table"))
{
}
column(Business_Unit___Data_Source_Caption; "Business Unit".FieldCaption("Data Source"))
{
}
column(Selected_dimensions_will_be_copied_Caption; Selected_dimensions_will_be_copied_CaptionLbl)
{
}
dataitem(BusUnitErrorLoop; "Integer")
{
DataItemTableView = sorting(Number) where(Number = const(1));
column(ErrorText_Number_; ErrorText[Number])
{
}
column(Errors_in_Business_Unit_Caption; Errors_in_Business_Unit_CaptionLbl)
{
}
trigger OnPostDataItem()
begin
ClearErrors();
end;
trigger OnPreDataItem()
begin
SetRange(Number, 1, NextErrorIndex);
end;
}
dataitem("G/L Account"; "G/L Account")
{
DataItemTableView = sorting("No.") where("Account Type" = const(Posting));
PrintOnlyIfDetail = true;
column(G_L_Account__No__; "No.")
{
}
column(G_L_Account_Name; Name)
{
}
column(G_L_Account__Consol__Translation_Method_; "Consol. Translation Method")
{
}
column(G_L_Account__Consol__Debit_Acc__; "Consol. Debit Acc.")
{
}
column(G_L_Account__Consol__Credit_Acc__; "Consol. Credit Acc.")
{
}
column(G_L_Account__No__Caption; FieldCaption("No."))
{
}
column(G_L_Account_NameCaption; FieldCaption(Name))
{
}
column(G_L_Account__Consol__Translation_Method_Caption; FieldCaption("Consol. Translation Method"))
{
}
column(G_L_Account__Consol__Debit_Acc__Caption; FieldCaption("Consol. Debit Acc."))
{
}
column(G_L_Account__Consol__Credit_Acc__Caption; FieldCaption("Consol. Credit Acc."))
{
}
dataitem("G/L Entry"; "G/L Entry")
{
DataItemLink = "G/L Account No." = field("No.");
DataItemTableView = sorting("G/L Account No.", "Posting Date");
column(EntryNo_GLEntry; "Entry No.")
{
}
trigger OnAfterGetRecord()
var
TempDimBufIn: Record "Dimension Buffer" temporary;
TableID: array[10] of Integer;
No: array[10] of Code[20];
CheckFinished: Boolean;
begin
if ("Posting Date" <> NormalDate("Posting Date")) and
not ConsolidatingClosingDate and
not ReportedClosingDateError
then begin
AddError(StrSubstNo(
Text008, TableCaption(),
FieldCaption("Posting Date"), "Posting Date"));
ReportedClosingDateError := true;
end;
if TempSelectedDim.FindFirst() then begin
DimSetEntry.SetRange("Dimension Set ID", "Dimension Set ID");
TempDimBufIn.DeleteAll();
if DimSetEntry.FindSet() then begin
repeat
if TempSelectedDim.Get(UserId, 3, REPORT::"Consolidation - Test Database", '', DimSetEntry."Dimension Code") then begin
TempDimBufIn.Init();
TempDimBufIn."Table ID" := DATABASE::"G/L Entry";
TempDimBufIn."Entry No." := "Entry No.";
if TempDim.Get(DimSetEntry."Dimension Code") then
if TempDim."Consolidation Code" <> '' then
TempDimBufIn."Dimension Code" := TempDim."Consolidation Code"
else
TempDimBufIn."Dimension Code" := TempDim.Code
else
TempDimBufIn."Dimension Code" := DimSetEntry."Dimension Code";
if TempDimVal.Get(DimSetEntry."Dimension Code", DimSetEntry."Dimension Value Code") then
if TempDimVal."Consolidation Code" <> '' then
TempDimBufIn."Dimension Value Code" := TempDimVal."Consolidation Code"
else
TempDimBufIn."Dimension Value Code" := TempDimVal.Code
else
TempDimBufIn."Dimension Value Code" := DimSetEntry."Dimension Value Code";
TempDimBufIn.Insert();
end;
until DimSetEntry.Next() = 0;
if not DimMgt.CheckDimBuffer(TempDimBufIn) then
AddError(StrSubstNo(
'%1 %2: %3',
TableCaption, "Entry No.",
DimMgt.GetDimCombErr()));
TableID[1] := DATABASE::"G/L Account";
No[1] := "G/L Account No.";
TableID[2] := DATABASE::"G/L Account";
No[2] := "Bal. Account No.";
TableID[3] := DATABASE::Job;
No[3] := "Job No.";
CheckFinished := DimMgt.CheckDimBufferValuePosting(TempDimBufIn, TableID, No);
if not CheckFinished then
AddError(StrSubstNo(
'%1 %2: %3',
TableCaption, "Entry No.",
DimMgt.GetDimValuePostingErr()));
end;
end;
if GLEntryAddedToDataset then
CurrReport.Skip();
GLEntryAddedToDataset := true;
end;
trigger OnPreDataItem()
begin
SetRange("Posting Date", ConsolidStartDate, ConsolidEndDate);
ReportedClosingDateError := false;
GLEntryAddedToDataset := false;
end;
}
dataitem(ErrorLoop; "Integer")
{
DataItemTableView = sorting(Number);
column(ErrorText_Number__Control23; ErrorText[Number])
{
}
column(Errors_in_this_G_L_Account_Caption; Errors_in_this_G_L_Account_CaptionLbl)
{
}
trigger OnPostDataItem()
begin
ClearErrors();
end;
trigger OnPreDataItem()
begin
SetRange(Number, 1, NextErrorIndex);
end;
}
trigger OnAfterGetRecord()
begin
TestGLAccounts();
end;
}
trigger OnAfterGetRecord()
begin
Print_control := TempSelectedDim.FindFirst();
end;
}
trigger OnAfterGetRecord()
begin
TestField("Company Name");
ClearErrors();
if ("Starting Date" <> 0D) or ("Ending Date" <> 0D) then begin
if "Starting Date" = 0D then
AddError(StrSubstNo(
Text031, FieldCaption("Starting Date"),
FieldCaption("Ending Date"), "Company Name"));
if "Ending Date" = 0D then
AddError(StrSubstNo(
Text031, FieldCaption("Ending Date"),
FieldCaption("Starting Date"), "Company Name"));
if "Starting Date" > "Ending Date" then
AddError(StrSubstNo(
Text032, FieldCaption("Starting Date"),
FieldCaption("Ending Date"), "Company Name"));
end;
SubsidGLSetup.ChangeCompany("Company Name");
SubsidGLSetup.Get();
if (SubsidGLSetup."Additional Reporting Currency" = '') and
("Data Source" = "Data Source"::"Add. Rep. Curr. (ACY)")
then
AddError(StrSubstNo(
Text020,
FieldCaption("Data Source"),
TableCaption,
"Data Source",
SubsidGLSetup.FieldCaption("Additional Reporting Currency")));
"G/L Account".ChangeCompany("Company Name");
"G/L Entry".ChangeCompany("Company Name");
DimSetEntry.ChangeCompany("Company Name");
Dim.ChangeCompany("Company Name");
TempDim.Reset();
TempDim.DeleteAll();
if Dim.Find('-') then
repeat
TempDim.Init();
TempDim := Dim;
TempDim.Insert();
until Dim.Next() = 0;
TempConsolidDim.Reset();
TempConsolidDim.DeleteAll();
if ConsolidDim.Find('-') then
repeat
TempConsolidDim.Init();
TempConsolidDim := ConsolidDim;
TempConsolidDim.Insert();
until ConsolidDim.Next() = 0;
SelectedDim.SetRange("User ID", UserId);
SelectedDim.SetRange("Object Type", 3);
SelectedDim.SetRange("Object ID", REPORT::"Consolidation - Test Database");
TempSelectedDim.Reset();
TempSelectedDim.DeleteAll();
if SelectedDim.Find('-') then
repeat
TempSelectedDim.Init();
TempSelectedDim := SelectedDim;
if not TempDim.Get(SelectedDim."Dimension Code") then begin
TempDim.SetRange("Consolidation Code", SelectedDim."Dimension Code");
if TempDim.FindFirst() then
TempSelectedDim."Dimension Code" := TempDim.Code
else
AddError(StrSubstNo(
Text016,
SelectedDim.TableCaption(), SelectedDim."Dimension Code", "Company Name"));
end else
if TempDim."Consolidation Code" <> '' then
if not TempConsolidDim.Get(TempDim."Consolidation Code") then
AddError(StrSubstNo(
Text017,
SelectedDim.FieldCaption("Dimension Code"), TempDim.Code, "Company Name",
TempDim.FieldCaption("Consolidation Code"), TempDim."Consolidation Code",
CompanyName));
TempSelectedDim.Insert();
until SelectedDim.Next() = 0;
TempDim.Reset();
TempDimVal.Reset();
TempDimVal.DeleteAll();
DimVal.ChangeCompany("Company Name");
if DimVal.Find('-') then
repeat
TempDimVal.Init();
TempDimVal := DimVal;
TempDimVal.Insert();
until DimVal.Next() = 0;
TempConsolidDimVal.Reset();
TempConsolidDimVal.DeleteAll();
if ConsolidDimVal.Find('-') then
repeat
TempConsolidDimVal.Init();
TempConsolidDimVal := ConsolidDimVal;
TempConsolidDimVal.Insert();
until ConsolidDimVal.Next() = 0;
end;
}
}
requestpage
{
SaveValues = true;
layout
{
area(content)
{
group(Options)
{
Caption = 'Options';
group("Consolidation Period")
{
Caption = 'Consolidation Period';
field(StartingDate; ConsolidStartDate)
{
ApplicationArea = Suite;
Caption = 'Starting Date';
ClosingDates = true;
ToolTip = 'Specifies the first date in the period from which the business units'' entries will be tested. If a business unit has a different fiscal year than the consolidated company, its starting and ending dates must be entered in the Business Unit table.';
}
field(EndingDate; ConsolidEndDate)
{
ApplicationArea = Suite;
Caption = 'Ending Date';
ClosingDates = true;
ToolTip = 'Specifies the last date in the period from which the business units'' entries will be tested.';
}
}
group("Copy Field Contents")
{
Caption = 'Copy Field Contents';
field(CopyDimensions; ColumnDim)
{
ApplicationArea = Dimensions;
Caption = 'Copy Dimensions';
Editable = false;
ToolTip = 'Specifies dimensions that are to be copied.';
trigger OnAssistEdit()
begin
DimSelectionBuf.SetDimSelectionMultiple(3, REPORT::"Consolidation - Test Database", ColumnDim);
end;
}
}
}
}
}
actions
{
}
}
rendering
{
layout(RDLCLayout)
{
Type = RDLC;
LayoutFile = './Finance/Consolidation/ConsolidationTestDatabase.rdlc';
Summary = 'Report layout made in the legacy RDLC format. Use an RDLC editor to modify the layout.';
}
}
labels
{
}
trigger OnPreReport()
begin
if ConsolidStartDate = 0D then
Error(Text004);
if ConsolidEndDate = 0D then
Error(Text005);
ConsolidatingClosingDate :=
(ConsolidStartDate = ConsolidEndDate) and
(ConsolidStartDate <> NormalDate(ConsolidStartDate));
if (ConsolidStartDate <> NormalDate(ConsolidStartDate)) and
(ConsolidStartDate <> ConsolidEndDate)
then
Error(Text007);
DimSelectionBuf.CompareDimText(
3, REPORT::"Consolidation - Test Database", '', ColumnDim, Text015);
end;
var
ConsolidGLAcc: Record "G/L Account";
SubsidGLSetup: Record "General Ledger Setup";
SelectedDim: Record "Selected Dimension";
TempSelectedDim: Record "Selected Dimension" temporary;
Dim: Record Dimension;
DimVal: Record "Dimension Value";
TempDim: Record Dimension temporary;
TempDimVal: Record "Dimension Value" temporary;
ConsolidDim: Record Dimension;
ConsolidDimVal: Record "Dimension Value";
TempConsolidDim: Record Dimension temporary;
TempConsolidDimVal: Record "Dimension Value" temporary;
DimSetEntry: Record "Dimension Set Entry";
DimSelectionBuf: Record "Dimension Selection Buffer";
DimMgt: Codeunit DimensionManagement;
ConsolidStartDate: Date;
ConsolidEndDate: Date;
ColumnDim: Text[250];
NextErrorIndex: Integer;
ConsolidatingClosingDate: Boolean;
ReportedClosingDateError: Boolean;
GLEntryAddedToDataset: Boolean;
ErrorText: array[100] of Text[250];
Print_control: Boolean;
#pragma warning disable AA0074
Text004: Label 'Enter the starting date for the consolidation period.';
Text005: Label 'Enter the ending date for the consolidation period.';
Text007: Label 'When using closing dates, the starting and ending dates must be the same.';
#pragma warning disable AA0470
Text008: Label 'A %1 with %2 on a closing date (%3) was found while consolidating non-closing entries.';
Text009: Label 'Period: %1..%2';
#pragma warning restore AA0470
Text015: Label 'Copy Dimensions';
#pragma warning disable AA0470
Text016: Label '%1 %2 doesn''t exist in %3.';
Text017: Label '%1 %2 in %3 has a %4 %5 that doesn''t exist in %6.';
Text018: Label 'There are more than %1 errors.';
Text020: Label '%1 for this %2 is set to %3, but there is no %4 set up in the %2.';
Text021: Label 'Within the Subsidiary (%5), there are two G/L Accounts: %1 and %4; which refer to the same %2, but with a different %3.';
Text022: Label '%1 %2, referenced by Subsidiary (%5) %3 %4, does not exist in the Consolidated %3 table.';
Text023: Label 'Subsidiary (%7) %1 %2 must have the same %3 as Consolidated %1 %4. (%5 <> %6)';
Text031: Label '%1 must not be empty when %2 is not empty, in company %3.';
Text032: Label 'The %1 is later than the %2 in company %3.';
#pragma warning restore AA0470
#pragma warning restore AA0074
Consolidation___Test_DatabaseCaptionLbl: Label 'Consolidation - Test Database';
CurrReport_PAGENOCaptionLbl: Label 'Page';
Selected_dimensions_will_be_copied_CaptionLbl: Label 'Selected dimensions will be copied.';
Errors_in_Business_Unit_CaptionLbl: Label 'Errors in Business Unit:';
Errors_in_this_G_L_Account_CaptionLbl: Label 'Errors in this G/L Account:';
AccountDoesNotExistTxt: Label '%1 %2 referenced by Subsidiary (%3) does not exist in the Consolidated %1 table.', Comment = '%1 is G/L Account, %2 is G/L Account No., %3 Business Unit';
local procedure AddError(Text: Text[250])
begin
if NextErrorIndex = ArrayLen(ErrorText) then
ErrorText[NextErrorIndex] := StrSubstNo(Text018, ArrayLen(ErrorText))
else begin
NextErrorIndex := NextErrorIndex + 1;
ErrorText[NextErrorIndex] := Text;
end;
end;
local procedure ClearErrors()
begin
Clear(ErrorText);
NextErrorIndex := 0;
end;
local procedure TestGLAccounts()
var
AccountToTest: Record "G/L Account";
begin
// First test within the Subsidiary Chart of Accounts
AccountToTest := "G/L Account";
if AccountToTest.TranslationMethodConflict("G/L Account") then begin
if "G/L Account".GetFilter("Consol. Debit Acc.") <> '' then
AddError(StrSubstNo(
Text021,
"G/L Account"."No.",
"G/L Account".FieldCaption("Consol. Debit Acc."),
"G/L Account".FieldCaption("Consol. Translation Method"),
AccountToTest."No.", "Business Unit".TableCaption()))
else
AddError(StrSubstNo(
Text021,
"G/L Account"."No.",
"G/L Account".FieldCaption("Consol. Credit Acc."),
"G/L Account".FieldCaption("Consol. Translation Method"),
AccountToTest."No.", "Business Unit".TableCaption()));
end else begin
"G/L Account".Reset();
"G/L Account".FilterGroup(2);
"G/L Account".SetRange("Account Type", "G/L Account"."Account Type"::Posting);
"G/L Account" := AccountToTest;
"G/L Account".Find('=');
end;
// Then, test for conflicts between subsidiary and parent (consolidated)
if "G/L Account"."Consol. Debit Acc." <> '' then begin
if not ConsolidGLAcc.Get("G/L Account"."Consol. Debit Acc.") then
AddError(StrSubstNo(
Text022,
"G/L Account".FieldCaption("Consol. Debit Acc."), "G/L Account"."Consol. Debit Acc.",
"G/L Account".TableCaption(), "G/L Account"."No.", "Business Unit".TableCaption()))
else
CheckConsolTranslationMethod();
end else
if not ConsolidGLAcc.Get(AccountToTest."No.") then
AddError(StrSubstNo(
AccountDoesNotExistTxt,
"G/L Account".TableCaption(), "G/L Account"."No.", "Business Unit".TableCaption()))
else
CheckConsolTranslationMethod();
if "G/L Account"."Consol. Debit Acc." = "G/L Account"."Consol. Credit Acc." then
exit;
if "G/L Account"."Consol. Credit Acc." <> '' then begin
if not ConsolidGLAcc.Get("G/L Account"."Consol. Credit Acc.") then
AddError(StrSubstNo(
Text022,
"G/L Account".FieldCaption("Consol. Credit Acc."), "G/L Account"."Consol. Credit Acc.",
"G/L Account".TableCaption(), "G/L Account"."No.", "Business Unit".TableCaption()))
else
CheckConsolTranslationMethod();
end else
if not ConsolidGLAcc.Get(AccountToTest."No.") then
AddError(StrSubstNo(
AccountDoesNotExistTxt,
"G/L Account".TableCaption(), "G/L Account"."No.", "Business Unit".TableCaption()))
else
CheckConsolTranslationMethod();
end;
local procedure CheckConsolTranslationMethod()
begin
if "G/L Account"."Consol. Translation Method" <> ConsolidGLAcc."Consol. Translation Method" then
AddError(StrSubstNo(
Text023,
"G/L Account".TableCaption(), "G/L Account"."No.",
"G/L Account".FieldCaption("Consol. Translation Method"), ConsolidGLAcc."No.",
"G/L Account"."Consol. Translation Method", ConsolidGLAcc."Consol. Translation Method",
"Business Unit".TableCaption()));
end;
}