Report 1826 Consolidation - Test

Base Application, Microsoft.Finance.Consolidation

src/Layers/W1/BaseApp/Finance/Consolidation/ConsolidationTest.Report.al629 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>/// Validates consolidation setup and data integrity before running consolidation processing./// Tests business unit configuration, G/L account mapping, dimension alignment, and data consistency./// </summary>/// <remarks>/// Comprehensive validation report covering business unit setup, account mapping, dimension configuration,/// and subsidiary data validation. Identifies potential consolidation issues before processing begins./// </remarks>report 1826 "Consolidation - Test"{    Caption = 'Consolidation - Test';    DefaultRenderingLayout = RDLCLayout;    dataset    {        dataitem(BusUnit; "Business Unit")        {            DataItemTableView = sorting(Code) where(Consolidate = const(true));            RequestFilterFields = "Code";            UseTemporary = true;            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(Text009Lbl, ConsolidStartDate, ConsolidEndDate))                {                }                column(Business_Unit__Code; BusUnit.Code)                {                }                column(Business_Unit___Company_Name_; BusUnit."Company Name")                {                }                column(Business_Unit___Consolidation___; BusUnit."Consolidation %")                {                }                column(Business_Unit___Currency_Code_; BusUnit."Currency Code")                {                }                column(Business_Unit___Currency_Exchange_Rate_Table_; BusUnit."Currency Exchange Rate Table")                {                }                column(Business_Unit___Data_Source_; BusUnit."Data Source")                {                }                column(Print_control; Print_control)                {                }                column(Consolidation___Test_DatabaseCaption; Consolidation___Test_DatabaseCaptionLbl)                {                }                column(CurrReport_PAGENOCaption; CurrReport_PAGENOCaptionLbl)                {                }                column(Business_Unit__CodeCaption; BusUnit.FieldCaption(Code))                {                }                column(Business_Unit___Company_Name_Caption; BusUnit.FieldCaption("Company Name"))                {                }                column(Business_Unit___Consolidation___Caption; BusUnit.FieldCaption("Consolidation %"))                {                }                column(Business_Unit___Currency_Code_Caption; BusUnit.FieldCaption("Currency Code"))                {                }                column(Business_Unit___Currency_Exchange_Rate_Table_Caption; BusUnit.FieldCaption("Currency Exchange Rate Table"))                {                }                column(Business_Unit___Data_Source_Caption; BusUnit.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(                                    Text008Txt, 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", '', DimSetEntry."Dimension Code") then begin                                            TempDimBufIn.Init();                                            TempDimBufIn."Entry No." := "Entry No.";                                            TempDimBufIn."Table ID" := DATABASE::"G/L Entry";                                            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";                                            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";                                            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(                            Text031Txt, FieldCaption("Starting Date"),                            FieldCaption("Ending Date"), "Company Name"));                    if "Ending Date" = 0D then                        AddError(StrSubstNo(                            Text031Txt, FieldCaption("Ending Date"),                            FieldCaption("Starting Date"), "Company Name"));                    if "Starting Date" > "Ending Date" then                        AddError(StrSubstNo(                            Text032Txt, 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(                        Text020Txt,                        FieldCaption("Data Source"),                        TableCaption,                        "Data Source",                        SubsidGLSetup.FieldCaption("Additional Reporting Currency")));                "G/L Account".ChangeCompany("Company Name");                DimSetEntry.ChangeCompany("Company Name");                "G/L Entry".ChangeCompany("Company Name");                Dim.ChangeCompany("Company Name");                TempConsolidDim.Reset();                TempConsolidDim.DeleteAll();                if ConsolidDim.Find('-') then                    repeat                        TempConsolidDim.Init();                        TempConsolidDim := ConsolidDim;                        TempConsolidDim.Insert();                    until ConsolidDim.Next() = 0;                TempDim.Reset();                TempDim.DeleteAll();                if Dim.Find('-') then                    repeat                        TempDim.Init();                        TempDim := Dim;                        TempDim.Insert();                    until Dim.Next() = 0;                SelectedDim.SetRange("User ID", UserId);                SelectedDim.SetRange("Object ID", REPORT::"Consolidation - Test");                SelectedDim.SetRange("Object Type", 3);                TempSelectedDim.Reset();                TempSelectedDim.DeleteAll();                if SelectedDim.Find('-') then                    repeat                        TempSelectedDim.Init();                        TempSelectedDim := SelectedDim;                        if TempDim.Get(SelectedDim."Dimension Code") then begin                            if TempDim."Consolidation Code" <> '' then                                if not TempConsolidDim.Get(TempDim."Consolidation Code") then                                    AddError(StrSubstNo(                                        Text017Txt,                                        SelectedDim.FieldCaption("Dimension Code"), TempDim.Code, "Company Name",                                        TempDim.FieldCaption("Consolidation Code"), TempDim."Consolidation Code",                                        CompanyName));                        end else begin                            TempDim.SetRange("Consolidation Code", SelectedDim."Dimension Code");                            if TempDim.FindFirst() then                                TempSelectedDim."Dimension Code" := TempDim.Code                            else                                AddError(StrSubstNo(                                    Text016Txt,                                    SelectedDim.TableCaption(), SelectedDim."Dimension Code", "Company Name"));                        end;                        TempSelectedDim.Insert();                    until SelectedDim.Next() = 0;                TempDim.Reset();                TempConsolidDimVal.Reset();                TempConsolidDimVal.DeleteAll();                if ConsolidDimVal.Find('-') then                    repeat                        TempConsolidDimVal.Init();                        TempConsolidDimVal := ConsolidDimVal;                        TempConsolidDimVal.Insert();                    until ConsolidDimVal.Next() = 0;                SetTempDimValue(DimVal, TempDimVal, "Company Name");            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 Starting Date.';                        }                        field(EndingDate; ConsolidEndDate)                        {                            ApplicationArea = Suite;                            Caption = 'Ending Date';                            ClosingDates = true;                            ToolTip = 'Specifies the Ending Date.';                        }                    }                    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", ColumnDim);                            end;                        }                    }                }            }        }        actions        {        }    }    rendering    {        layout(RDLCLayout)        {            Type = RDLC;            LayoutFile = './Finance/Consolidation/ConsolidationTest.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(Text004Err);        if ConsolidEndDate = 0D then            Error(Text005Err);        ConsolidatingClosingDate :=          (ConsolidStartDate = ConsolidEndDate) and          (ConsolidStartDate <> NormalDate(ConsolidStartDate));        if (ConsolidStartDate <> NormalDate(ConsolidStartDate)) and           (ConsolidStartDate <> ConsolidEndDate)        then            Error(Text007Err);        DimSelectionBuf.CompareDimText(          3, REPORT::"Consolidation - Test", '', ColumnDim, Text015Txt);        CreateBusinessUnits();    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";        BusinessUnit: Record "Business Unit";        DimMgt: Codeunit DimensionManagement;        ConsolidStartDate: Date;        ConsolidEndDate: Date;        ColumnDim: Text[250];        NextErrorIndex: Integer;        ConsolidatingClosingDate: Boolean;        ReportedClosingDateError: Boolean;        GLEntryAddedToDataset: Boolean;        ErrorText: array[100] of Text[250];        ConsolidatedCompany: Text[30];        Print_control: Boolean;        Text004Err: Label 'Enter the starting date for the consolidation period.';        Text005Err: Label 'Enter the ending date for the consolidation period.';        Text007Err: Label 'When using closing dates, the starting and ending dates must be the same.';        Text008Txt: Label 'A %1 with %2 on a closing date (%3) was found while consolidating non-closing entries.', Comment = '%1=Table Caption for Business Unit table.;%2=Field Caption for Posting Date field.;%3=Posting Date.';        Text009Lbl: Label 'Period: %1..%2', Comment = '%1=Consolidate Starting Date.;%2=Consolidate Ending Date.';        Text015Txt: Label 'Copy Dimensions';        Text016Txt: Label '%1 %2 doesn''t exist in %3.', Comment = '%1=Selected Dimension table caption.;%2=Dimension Code value.; %3=Company Name value.';        Text017Txt: Label '%1 %2 in %3 has a %4 %5 that doesn''t exist in %6.', Comment = '%1=Field caption for Dimension Code field.;%2=Dimension Code value.;%3=Current Company Name value.;%4=Field caption for Consolidation Code.;%5=Consolidation Code value.;%6=Current Company name.';        Text018Txt: Label 'There are more than %1 errors.', Comment = '%1=The number of errors reported.';        Text020Txt: Label '%1 for this %2 is set to %3, but there is no %4 set up in the %2.', Comment = '%1=Field caption for Data Source field.;%2=Table caption for Business Unit table.;%3=Data Source value.;%4=Field caption for Additional Reporting Currency field.';        Text021Txt: 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.', Comment = '%1=Value of No. field from GL Account table.;%2=Field caption for Consol. Debit Acc. field.;%3=Field caption for Consol. Translation Method field.;%4=No. value from GL Account table.;%5=Caption for Business Unit table.';        Text022Txt: Label '%1 %2, referenced by Subsidiary (%5) %3 %4, does not exist in the Consolidated %3 table.', Comment = '%1=Field caption for Consol. Debit Acc. field.;%2=Consol. Debit Acc. value from GL Account table.;%3=Caption for GL Account table.;%4=No. value from GL Account table.;%5=Caption for Business Unit table.';        Text023Txt: Label 'Subsidiary (%7) %1 %2 must have the same %3 as Consolidated %1 %4.  (%5 <> %6)', Comment = '%1=Caption for GL Account table.;%2=Value of No. field from GL Account table.;%3=Caption for Consol. Translation Method field.;%4=Value of No. field from Consolidated GL Account table.;%5=Value of Consol. Translation Method field from GL Account.;%6=Value of Consol. Translation Method from the Consolidated GL Account table.;%7=Caption for Business Unit table.';        Text031Txt: Label '%1 must not be empty when %2 is not empty, in company %3.', Comment = '%1=Caption for Starting Date field.;%2=Caption for Ending Date field.;%3=Company Name value from Business Unit table.';        Text032Txt: Label 'The %1 is later than the %2 in company %3.', Comment = '%1=Caption for Starting Date field.;%2=Caption for Ending Date field.;%3=Company Name value from Business Unit table.';        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 the G/L Account No., %3 is "Business Unit"';    local procedure AddError(Text: Text[250])    begin        if NextErrorIndex = ArrayLen(ErrorText) then            ErrorText[NextErrorIndex] := StrSubstNo(Text018Txt, 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        GLAccountToTest: Record "G/L Account";    begin        // First test within the Subsidiary Chart of Accounts        GLAccountToTest := "G/L Account";        if GLAccountToTest.TranslationMethodConflict("G/L Account") then begin            if "G/L Account".GetFilter("Consol. Debit Acc.") <> '' then                AddError(StrSubstNo(                    Text021Txt,                    "G/L Account"."No.",                    "G/L Account".FieldCaption("Consol. Debit Acc."),                    "G/L Account".FieldCaption("Consol. Translation Method"),                    GLAccountToTest."No.", BusUnit.TableCaption()))            else                AddError(StrSubstNo(                    Text021Txt,                    "G/L Account"."No.",                    "G/L Account".FieldCaption("Consol. Credit Acc."),                    "G/L Account".FieldCaption("Consol. Translation Method"),                    GLAccountToTest."No.", BusUnit.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" := GLAccountToTest;            "G/L Account".Find('=');        end;        // Then, test for conflicts between subsidiary and parent (consolidated)        ConsolidGLAcc.ChangeCompany(ConsolidatedCompany);        if "G/L Account"."Consol. Debit Acc." <> '' then begin            if not ConsolidGLAcc.Get("G/L Account"."Consol. Debit Acc.") then                AddError(StrSubstNo(                    Text022Txt,                    "G/L Account".FieldCaption("Consol. Debit Acc."), "G/L Account"."Consol. Debit Acc.",                    "G/L Account".TableCaption(), "G/L Account"."No.", BusUnit.TableCaption()))            else                TestTranslationMethod();        end else            if not ConsolidGLAcc.Get(GLAccountToTest."No.") then                AddError(StrSubstNo(                    AccountDoesNotExistTxt,                    "G/L Account".TableCaption(), "G/L Account"."No.", BusUnit.TableCaption()))            else                TestTranslationMethod();        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(                    Text022Txt,                    "G/L Account".FieldCaption("Consol. Credit Acc."), "G/L Account"."Consol. Credit Acc.",                    "G/L Account".TableCaption(), "G/L Account"."No.", BusUnit.TableCaption()))            else                TestTranslationMethod();        end else            if not ConsolidGLAcc.Get(GLAccountToTest."No.") then                AddError(StrSubstNo(                    AccountDoesNotExistTxt,                    "G/L Account".TableCaption(), "G/L Account"."No.", BusUnit.TableCaption()))            else                TestTranslationMethod();    end;    /// <summary>    /// Sets the consolidated company name for validation testing and report output.    /// </summary>    /// <param name="CompanyName">Name of the company receiving consolidated data</param>    procedure SetConsolidatedCompany(CompanyName: Text[30])    begin        ConsolidatedCompany := CompanyName;    end;    local procedure CreateBusinessUnits()    begin        BusinessUnit.ChangeCompany(ConsolidatedCompany);        if BusinessUnit.Find('-') then            repeat                BusUnit.TransferFields(BusinessUnit);                BusUnit.Insert();            until BusinessUnit.Next() = 0;    end;    local procedure SetTempDimValue(var DimVal2: Record "Dimension Value"; var TempDimVal2: Record "Dimension Value" temporary; CompanyName: Text[30])    begin        TempDimVal2.Reset();        TempDimVal2.DeleteAll();        DimVal2.ChangeCompany(CompanyName);        if DimVal.Find('-') then            repeat                TempDimVal2.Init();                TempDimVal2 := DimVal2;                TempDimVal2.Insert();            until DimVal2.Next() = 0;    end;    local procedure TestTranslationMethod()    begin        if "G/L Account"."Consol. Translation Method" <> ConsolidGLAcc."Consol. Translation Method" then            AddError(StrSubstNo(                Text023Txt,                "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",                BusUnit.TableCaption()));    end;}