Report 90 Import Consolidation from DB
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;}