Report 90 Import Consolidation from DB

Base Application, Microsoft.Finance.Consolidation

src/Layers/W1/BaseApp/Finance/Consolidation/ImportConsolidationfromDB.Report.al606 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.Currency;using Microsoft.Finance.Dimension;using Microsoft.Finance.GeneralLedger.Account;using Microsoft.Finance.GeneralLedger.Journal;using Microsoft.Finance.GeneralLedger.Ledger;using Microsoft.Finance.GeneralLedger.Setup;using Microsoft.Foundation.NoSeries;using Microsoft.Foundation.Period;using System.Utilities;/// <summary>/// Imports consolidation data from subsidiary companies through direct database connections within the same environment./// Executes database-based consolidation data extraction for traditional on-premises consolidation scenarios./// </summary>/// <remarks>/// Core database import report for same-environment consolidation leveraging direct database connectivity./// Retrieves G/L entries, account structures, dimensions, and exchange rates from subsidiary databases./// Alternative to file-based or API-based consolidation for environments with shared database access./// Integrates with consolidation engine for seamless multi-company data processing./// </remarks>report 90 "Import Consolidation from DB"{    Caption = 'Consolidation Report (same environment)';    ProcessingOnly = true;    dataset    {        dataitem("Business Unit"; "Business Unit")        {            DataItemTableView = sorting(Code) where(Consolidate = const(true));            RequestFilterFields = "Code";            dataitem("G/L Account"; "G/L Account")            {                DataItemTableView = sorting("No.") where("Account Type" = const(Posting));                dataitem("G/L Entry"; "G/L Entry")                {                    DataItemLink = "G/L Account No." = field("No.");                    DataItemTableView = sorting("G/L Account No.", "Posting Date");                    dataitem("Dimension Set Entry"; "Dimension Set Entry")                    {                        DataItemLink = "Dimension Set ID" = field("Dimension Set ID");                        DataItemTableView = sorting("Dimension Set ID", "Dimension Code");                        trigger OnAfterGetRecord()                        var                            TempDimBuf: Record "Dimension Buffer" temporary;                        begin                            TempDimBuf.Init();                            TempDimBuf."Table ID" := DATABASE::"G/L Entry";                            TempDimBuf."Entry No." := GLEntryNo;                            if TempDim.Get("Dimension Code") and                               (TempDim."Consolidation Code" <> '')                            then                                TempDimBuf."Dimension Code" := TempDim."Consolidation Code"                            else                                TempDimBuf."Dimension Code" := "Dimension Code";                            if TempDimVal.Get("Dimension Code", "Dimension Value Code") and                               (TempDimVal."Consolidation Code" <> '')                            then                                TempDimBuf."Dimension Value Code" := TempDimVal."Consolidation Code"                            else                                TempDimBuf."Dimension Value Code" := "Dimension Value Code";                            BusUnitConsolidate.InsertEntryDim(TempDimBuf, TempDimBuf."Entry No.");                        end;                        trigger OnPreDataItem()                        var                            BusUnitDim: Record Dimension;                            DimMgt: Codeunit DimensionManagement;                            ColumnDimFilter: Text;                        begin                            if ColumnDim <> '' then begin                                ColumnDimFilter := ConvertStr(ColumnDim, ';', '|');                                BusUnitDim.ChangeCompany("Business Unit"."Company Name");                                SetFilter("Dimension Code", DimMgt.GetConsolidatedDimFilterByDimFilter(BusUnitDim, ColumnDimFilter));                            end;                        end;                    }                    trigger OnAfterGetRecord()                    begin                        GLEntryNo := BusUnitConsolidate.InsertGLEntry("G/L Entry");                    end;                    trigger OnPreDataItem()                    begin                        SetRange("Posting Date", ConsolidStartDate, ConsolidEndDate);                        if GetRangeMin("Posting Date") = NormalDate(GetRangeMin("Posting Date")) then                            CheckClosingPostings("G/L Account"."No.", GetRangeMin("Posting Date"), GetRangeMax("Posting Date"));                    end;                }                trigger OnAfterGetRecord()                begin                    Window.Update(2, "No.");                    Window.Update(3, '');                    BusUnitConsolidate.InsertGLAccount("G/L Account");                end;                trigger OnPreDataItem()                begin                    "G/L Account".SetRange("Exclude From Consolidation", false);                end;            }            dataitem("Currency Exchange Rate"; "Currency Exchange Rate")            {                DataItemTableView = sorting("Currency Code", "Starting Date");                trigger OnAfterGetRecord()                begin                    BusUnitConsolidate.InsertExchRate("Currency Exchange Rate");                end;                trigger OnPreDataItem()                var                    SubsidGLSetup: Record "General Ledger Setup";                begin                    if "Business Unit"."Currency Code" = '' then                        CurrReport.Break();                    SubsidGLSetup.ChangeCompany("Business Unit"."Company Name");                    SubsidGLSetup.GetRecordOnce();                    AdditionalCurrencyCode := SubsidGLSetup."Additional Reporting Currency";                    if SubsidGLSetup."LCY Code" <> '' then                        SubsidCurrencyCode := SubsidGLSetup."LCY Code"                    else                        SubsidCurrencyCode := "Business Unit"."Currency Code";                    if (ParentCurrencyCode = '') and (AdditionalCurrencyCode = '') then                        CurrReport.Break();                    SetFilter("Currency Code", '%1|%2', ParentCurrencyCode, AdditionalCurrencyCode);                    SetRange("Starting Date", 0D, ConsolidEndDate);                end;            }            dataitem(DoTheConsolidation; "Integer")            {                DataItemTableView = sorting(Number) where(Number = const(1));                trigger OnAfterGetRecord()                begin                    BusUnitConsolidate.SetGlobals(                      '', '', "Business Unit"."Company Name",                      SubsidCurrencyCode, AdditionalCurrencyCode, ParentCurrencyCode,                      0, ConsolidStartDate, ConsolidEndDate);                    BusUnitConsolidate.UpdateGLEntryDimSetID();                    if not OnlyImportData then                        BusUnitConsolidate.Run("Business Unit");                end;            }            trigger OnAfterGetRecord()            begin                Window.Update(1, Code);                Window.Update(2, '');                Clear(BusUnitConsolidate);                BusUnitConsolidate.SetDocNo(GLDocNo);                GLSetup.GetRecordOnce();                if GLSetup."Journal Templ. Name Mandatory" then begin                    GenJnlBatch."Journal Template Name" := GenJnlLineReq."Journal Template Name";                    GenJnlBatch.Name := GenJnlLineReq."Journal Batch Name";                    BusUnitConsolidate.SetGenJnlBatch(GenJnlBatch);                end;                TestField("Company Name");                "G/L Entry".ChangeCompany("Company Name");                "Dimension Set Entry".ChangeCompany("Company Name");                "G/L Account".ChangeCompany("Company Name");                "Currency Exchange Rate".ChangeCompany("Company Name");                Dim.ChangeCompany("Company Name");                DimVal.ChangeCompany("Company Name");                SelectedDim.SetRange("User ID", UserId);                SelectedDim.SetRange("Object Type", 3);                SelectedDim.SetRange("Object ID", REPORT::"Import Consolidation from DB");                BusUnitConsolidate.SetSelectedDim(SelectedDim);                TempDim.Reset();                TempDim.DeleteAll();                if Dim.Find('-') then                    repeat                        TempDim.Init();                        TempDim := Dim;                        TempDim.Insert();                    until Dim.Next() = 0;                TempDim.Reset();                TempDimVal.Reset();                TempDimVal.DeleteAll();                if DimVal.Find('-') then                    repeat                        TempDimVal.Init();                        TempDimVal := DimVal;                        TempDimVal.Insert();                    until DimVal.Next() = 0;                AdditionalCurrencyCode := '';                SubsidCurrencyCode := '';            end;            trigger OnPreDataItem()            begin                CheckConsolidDates(ConsolidStartDate, ConsolidEndDate);                if BusinessUnitCode <> '' then                    "Business Unit".SetRange(Code, BusinessUnitCode);                "Business Unit".SetRange("Default Data Import Method", "Business Unit"."Default Data Import Method"::Database);                "Business Unit".SetFilter("Company Name", '<>%1', '');                if GLDocNo = '' then                    Error(Text000);                Window.Open(                  Text001 +                  Text002 +                  Text003 +                  Text004);            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(ColumnDim; ColumnDim)                        {                            ApplicationArea = Dimensions;                            Caption = 'Copy Dimensions';                            Editable = false;                            ToolTip = 'Specifies if you want the entries to be classified by dimensions when they are transferred.';                            trigger OnAssistEdit()                            begin                                DimSelectionBuf.SetDimSelectionMultiple(3, REPORT::"Import Consolidation from DB", ColumnDim);                            end;                        }                    }                    field(DocumentNo; GLDocNo)                    {                        ApplicationArea = Suite;                        Caption = 'Document No.';                        ToolTip = 'Specifies the G/L document number.';                        Visible = not IsJournalTemplNameVisible;                    }                    field(JournalTemplateName; GenJnlLineReq."Journal Template Name")                    {                        ApplicationArea = Basic, Suite;                        Caption = 'Journal Template Name';                        TableRelation = "Gen. Journal Template";                        ToolTip = 'Specifies the name of the journal template that is used for the posting.';                        Visible = IsJournalTemplNameVisible;                        trigger OnValidate()                        begin                            GenJnlLineReq."Journal Batch Name" := '';                        end;                    }                    field(JournalBatchName; GenJnlLineReq."Journal Batch Name")                    {                        ApplicationArea = Basic, Suite;                        Caption = 'Journal Batch Name';                        Lookup = true;                        ToolTip = 'Specifies the name of the journal batch that is used for the posting.';                        Visible = IsJournalTemplNameVisible;                        trigger OnLookup(var Text: Text): Boolean                        var                            GenJnlManagement: Codeunit GenJnlManagement;                        begin                            GenJnlManagement.SetJnlBatchName(GenJnlLineReq);                            if GenJnlLineReq."Journal Batch Name" <> '' then                                GenJnlBatch.Get(GenJnlLineReq."Journal Template Name", GenJnlLineReq."Journal Batch Name");                        end;                        trigger OnValidate()                        begin                            if GenJnlLineReq."Journal Batch Name" <> '' then begin                                GenJnlLineReq.TestField("Journal Template Name");                                GenJnlBatch.Get(GenJnlLineReq."Journal Template Name", GenJnlLineReq."Journal Batch Name");                            end;                        end;                    }                    field(ParentCurrencyCode; ParentCurrencyCode)                    {                        ApplicationArea = Suite;                        Caption = 'Parent Currency Code';                        ToolTip = 'Specifies the parent currency code.';                    }                }            }        }        actions        {        }        trigger OnOpenPage()        begin            SetDefaultParameters();        end;    }    labels    {    }    trigger OnPostReport()    begin        Commit();        if not SkipRunningTrialBalanceAfter then            REPORT.Run(REPORT::"Consolidated Trial Balance");    end;    trigger OnPreReport()    var        NoSeries: Codeunit "No. Series";    begin        DimSelectionBuf.CompareDimText(          3, REPORT::"Import Consolidation from DB", '', ColumnDim, Text020);        GLSetup.GetRecordOnce();        if GLSetup."Journal Templ. Name Mandatory" then begin            if GenJnlLineReq."Journal Template Name" = '' then                Error(PleaseEnterErr, GenJnlLineReq.FieldCaption("Journal Template Name"));            if GenJnlLineReq."Journal Batch Name" = '' then                Error(PleaseEnterErr, GenJnlLineReq.FieldCaption("Journal Batch Name"));            Clear(GLDocNo);            GenJnlBatch.Get(GenJnlLineReq."Journal Template Name", GenJnlLineReq."Journal Batch Name");            GenJnlBatch.TestField("No. Series");            GLDocNo := NoSeries.GetNextNo(GenJnlBatch."No. Series", WorkDate());        end;    end;    var        SelectedDim: Record "Selected Dimension";        Dim: Record Dimension;        DimVal: Record "Dimension Value";        TempDim: Record Dimension temporary;        TempDimVal: Record "Dimension Value" temporary;        GLSetup: Record "General Ledger Setup";        DimSelectionBuf: Record "Dimension Selection Buffer";        GenJnlLineReq: Record "Gen. Journal Line";        GenJnlBatch: Record "Gen. Journal Batch";        BusUnitConsolidate: Codeunit Consolidate;        Window: Dialog;        ConsolidStartDate: Date;        ConsolidEndDate: Date;        GLDocNo: Code[20];        ColumnDim: Text[250];        ParentCurrencyCode: Code[10];        SubsidCurrencyCode: Code[10];        AdditionalCurrencyCode: Code[10];        BusinessUnitCode: Code[20];        IsJournalTemplNameVisible: Boolean;        SkipDateConfirm, SkipRunningTrialBalanceAfter : Boolean;        OnlyImportData: Boolean;#pragma warning disable AA0470        Text032Err: Label 'The %1 is later than the %2 in company %3.';#pragma warning restore AA0470        GLEntryNo: Integer;#pragma warning disable AA0074        Text000: Label 'Enter a document number.';        Text001: Label 'Importing Subsidiary Data...\\';#pragma warning disable AA0470        Text002: Label 'Business Unit Code   #1##########\';        Text003: Label 'G/L Account No.      #2##########\';        Text004: Label 'Date                 #3######';#pragma warning restore AA0470        Text006: Label 'Enter the starting date for the consolidation period.';        Text007: Label 'Enter the ending date for the consolidation period.';        Text020: Label 'Copy Dimensions';#pragma warning disable AA0470        Text022: Label 'A %1 with %2 on a closing date (%3) was found while consolidating nonclosing entries (%4 %5).';        Text023: Label 'Do you want to consolidate in the period from %1 to %2?';        Text024: Label 'There is no %1 to consolidate.';#pragma warning restore AA0470        Text028: Label 'You must create a new fiscal year in the consolidated company.';        Text030: Label 'When using closing dates, the starting and ending dates must be the same.';#pragma warning restore AA0074        ConsPeriodSubsidiaryQst: Label 'The consolidation period %1 .. %2 is not within the fiscal year of one or more of the subsidiaries.\Do you want to proceed with the consolidation?', Comment = '%1 and %2 - request page values';        ConsPeriodCompanyQst: Label 'The consolidation period %1 .. %2 is not within the fiscal year %3 .. %4 of the consolidated company %5.\Do you want to proceed with the consolidation?', Comment = '%1, %2, %3, %4 - request page values, %5 - company name';        PleaseEnterErr: Label 'Please enter a %1.', Comment = '%1 - field caption';    internal procedure SetConsolidationProcessParameters(ConsolidationProcess: Record "Consolidation Process"; BusinessUnit: Record "Business Unit")    begin        SetDefaultParameters();        ConsolidStartDate := ConsolidationProcess."Starting Date";        ConsolidEndDate := ConsolidationProcess."Ending Date";        ColumnDim := ConsolidationProcess."Dimensions to Transfer";        GLDocNo := ConsolidationProcess."Document No.";        GenJnlLineReq."Journal Template Name" := ConsolidationProcess."Journal Template Name";        GenJnlLineReq."Journal Batch Name" := ConsolidationProcess."Journal Batch Name";        ParentCurrencyCode := ConsolidationProcess."Parent Currency Code";        BusinessUnitCode := BusinessUnit.Code;        SkipDateConfirm := true;        SkipRunningTrialBalanceAfter := true;        OnlyImportData := true;        if ColumnDim = '' then            ColumnDim := DimSelectionBuf.GetDimSelectionText(3, REPORT::"Import Consolidation from DB", '');    end;    internal procedure GetConsolidate(var ConsolidateToGet: Codeunit Consolidate)    begin        ConsolidateToGet := BusUnitConsolidate;    end;    local procedure SetDefaultParameters()    begin        if ConsolidStartDate = 0D then            ConsolidStartDate := WorkDate();        if ConsolidEndDate = 0D then            ConsolidEndDate := WorkDate();        GLSetup.GetRecordOnce();        if ParentCurrencyCode = '' then            ParentCurrencyCode := GLSetup."LCY Code";        IsJournalTemplNameVisible := GLSetup."Journal Templ. Name Mandatory";    end;    local procedure CheckClosingPostings(GLAccNo: Code[20]; StartDate: Date; EndDate: Date)    var        GLEntry: Record "G/L Entry";        AccountingPeriod: Record "Accounting Period";    begin        AccountingPeriod.ChangeCompany("Business Unit"."Company Name");        AccountingPeriod.SetCurrentKey("New Fiscal Year", "Date Locked");        AccountingPeriod.SetRange("New Fiscal Year", true);        AccountingPeriod.SetRange("Date Locked", true);        AccountingPeriod.SetRange("Starting Date", StartDate + 1, EndDate);        if AccountingPeriod.Find('-') then begin            GLEntry.ChangeCompany("Business Unit"."Company Name");            GLEntry.SetRange("G/L Account No.", GLAccNo);            repeat                GLEntry.SetRange("Posting Date", ClosingDate(AccountingPeriod."Starting Date" - 1));                if not GLEntry.IsEmpty() then                    Error(                      Text022,                      GLEntry.TableCaption(),                      GLEntry.FieldCaption("Posting Date"),                      GLEntry.GetFilter("Posting Date"),                      GLEntry.FieldCaption("G/L Account No."),                      GLAccNo);            until AccountingPeriod.Next() = 0;        end;    end;    local procedure CheckConsolidDates(StartDate: Date; EndDate: Date)    var        BusUnit: Record "Business Unit";        ConfirmManagement: Codeunit "Confirm Management";        ConsolPeriodInclInFiscalYears: Boolean;        IsHandled: Boolean;    begin        if StartDate = 0D then            Error(Text006);        if EndDate = 0D then            Error(Text007);        IsHandled := false;        OnCheckConsolidDatesOnBeforeConfirmConsolidationDates(IsHandled, StartDate, EndDate);        if not isHandled then            if not SkipDateConfirm then                if not ConfirmManagement.GetResponseOrDefault(StrSubstNo(Text023, StartDate, EndDate), true) then                    CurrReport.Break();        CheckClosingDates(StartDate, EndDate);        BusUnit.CopyFilters("Business Unit");        BusUnit.SetRange(Consolidate, true);        if not BusUnit.Find('-') then            Error(Text024, BusUnit.TableCaption());        ConsolPeriodInclInFiscalYears := true;        repeat            if (StartDate = NormalDate(StartDate)) or (EndDate = NormalDate(EndDate)) then                if (BusUnit."Starting Date" <> 0D) or (BusUnit."Ending Date" <> 0D) then begin                    CheckBusUnitsDatesToFiscalYear(BusUnit);                    ConsolPeriodInclInFiscalYears :=                      ConsolPeriodInclInFiscalYears and CheckDatesToBusUnitDates(StartDate, EndDate, BusUnit);                end;        until BusUnit.Next() = 0;        if not ConsolPeriodInclInFiscalYears then            if not ConfirmManagement.GetResponseOrDefault(                 StrSubstNo(ConsPeriodSubsidiaryQst, StartDate, EndDate), true)            then                CurrReport.Break();        CheckDatesToFiscalYear(StartDate, EndDate);    end;    local procedure CheckDatesToFiscalYear(StartDate: Date; EndDate: Date)    var        AccountingPeriod: Record "Accounting Period";        ConfirmManagement: Codeunit "Confirm Management";        FiscalYearStartDate: Date;        FiscalYearEndDate: Date;        ConsolPeriodInclInFiscalYear: Boolean;    begin        ConsolPeriodInclInFiscalYear := true;        AccountingPeriod.Reset();        AccountingPeriod.SetRange(Closed, false);        AccountingPeriod.SetRange("New Fiscal Year", true);#pragma warning disable AA0181, AA0233 // Positional Find() paired with Next(); suppression tracked for follow-up        if AccountingPeriod.Find('-') then begin#pragma warning restore AA0181, AA0233            FiscalYearStartDate := AccountingPeriod."Starting Date";            if AccountingPeriod.Find('>') then                FiscalYearEndDate := CalcDate('<-1D>', AccountingPeriod."Starting Date")            else                Error(Text028);            ConsolPeriodInclInFiscalYear := (StartDate >= FiscalYearStartDate) and (EndDate <= FiscalYearEndDate);            if not ConsolPeriodInclInFiscalYear then                if not ConfirmManagement.GetResponseOrDefault(                     StrSubstNo(                       ConsPeriodCompanyQst, StartDate, EndDate, FiscalYearStartDate,                       FiscalYearEndDate, CompanyName), true)                then                    CurrReport.Break();        end;    end;    local procedure CheckDatesToBusUnitDates(StartDate: Date; EndDate: Date; BusUnit: Record "Business Unit"): Boolean    var        ConsolPeriodInclInFiscalYear: Boolean;    begin        ConsolPeriodInclInFiscalYear := (StartDate >= BusUnit."Starting Date") and (EndDate <= BusUnit."Ending Date");        exit(ConsolPeriodInclInFiscalYear);    end;    local procedure CheckClosingDates(StartDate: Date; EndDate: Date)    begin        if (StartDate = ClosingDate(StartDate)) or           (EndDate = ClosingDate(EndDate))        then            if StartDate <> EndDate then                Error(Text030);    end;    /// <summary>    /// Integration event raised before confirming consolidation dates during database import validation.    /// Enables custom date validation logic and confirmation handling during consolidation processing.    /// </summary>    /// <param name="IsHandled">Set to true to bypass standard date confirmation processing</param>    /// <param name="StartDate">Starting date for consolidation period validation</param>    /// <param name="EndDate">Ending date for consolidation period validation</param>    [IntegrationEvent(false, false)]    local procedure OnCheckConsolidDatesOnBeforeConfirmConsolidationDates(var IsHandled: Boolean; StartDate: Date; EndDate: Date)    begin    end;    local procedure CheckBusUnitsDatesToFiscalYear(var BusUnit: Record "Business Unit")    begin        if (BusUnit."Starting Date" <> 0D) or (BusUnit."Ending Date" <> 0D) then begin            BusUnit.TestField("Starting Date");            BusUnit.TestField("Ending Date");            if BusUnit."Starting Date" > BusUnit."Ending Date" then                Error(                  Text032Err, BusUnit.FieldCaption("Starting Date"),                  BusUnit.FieldCaption("Ending Date"), BusUnit."Company Name");        end;    end;}