Report 91 Export Consolidation

Base Application, Microsoft.Finance.Consolidation

src/Layers/W1/BaseApp/Finance/Consolidation/ExportConsolidation.Report.al612 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.Ledger;using Microsoft.Finance.GeneralLedger.Setup;using Microsoft.Foundation.Period;using System.Environment;using System.IO;/// <summary>/// Exports G/L account and entry data for consolidation processing to external files./// Supports XML and text file formats for data transfer to consolidated companies./// </summary>/// <remarks>/// Export report for consolidation data transfer supporting multiple file formats and comprehensive/// G/L data export including dimensions, exchange rates, and selected account filtering./// </remarks>report 91 "Export Consolidation"{    ApplicationArea = Suite;    Caption = 'Export Consolidation';    ProcessingOnly = true;    UsageCategory = Tasks;    dataset    {        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");                trigger OnAfterGetRecord()                begin                    Window.Update(2, "Posting Date");                    SetRange("Posting Date", "Posting Date");                    OnBeforeGLEntryOnAfterGetRecord("G/L Entry", TempSelectedDim);                    if not TempSelectedDim.FindFirst() then begin                        CalcSums(                          Amount, "Debit Amount", "Credit Amount",                          "Add.-Currency Debit Amount", "Add.-Currency Credit Amount");                        if (Amount <> 0) or ("Debit Amount" <> 0) or ("Credit Amount" <> 0) then begin                            TempGLEntry.Reset();                            TempGLEntry.DeleteAll();                            TempDimBufOut.Reset();                            TempDimBufOut.DeleteAll();                            TempGLEntry := "G/L Entry";                            TempGLEntry.Insert();                            case FileFormat of                                FileFormat::"Version F&O":                                    ExportFOConsolidation.InsertGLEntry(TempGLEntry);                                FileFormat::"Version 4.00 or Later (.xml)":                                    Consolidate.InsertGLEntry(TempGLEntry);                                FileFormat::"Version 3.70 or Earlier (.txt)":                                    begin                                        UpdateExportedInfo(TempGLEntry);                                        WriteFile(TempGLEntry, TempDimBufOut);                                    end;                            end;                        end;                        Find('+');                    end else begin                        TempGLEntry.Reset();                        TempGLEntry.DeleteAll();                        DimBufMgt.DeleteAllDimensions();                        repeat                            TempDimBufIn.Reset();                            TempDimBufIn.DeleteAll();                            DimSetEntry.Reset();                            DimSetEntry.SetRange("Dimension Set ID", "Dimension Set ID");                            if DimSetEntry.FindSet() then                                repeat                                    if TempSelectedDim.Get(UserId, 3, REPORT::"Export Consolidation", '', 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;                            UpdateTempGLEntry(TempDimBufIn);                        until Next() = 0;                        TempGLEntry.Reset();                        if TempGLEntry.FindSet() then                            repeat                                TempDimBufOut.Reset();                                TempDimBufOut.DeleteAll();                                DimBufMgt.GetDimensions(TempGLEntry."Entry No.", TempDimBufOut);                                TempDimBufOut.SetRange("Entry No.", TempGLEntry."Entry No.");                                case FileFormat of                                    FileFormat::"Version 4.00 or Later (.xml)",                                    FileFormat::"Version F&O":                                        if (TempGLEntry."Debit Amount" <> 0) or (TempGLEntry."Credit Amount" <> 0) then                                            WriteFile(TempGLEntry, TempDimBufOut);                                    FileFormat::"Version 3.70 or Earlier (.txt)":                                        begin                                            UpdateExportedInfo(TempGLEntry);                                            if TempGLEntry.Amount <> 0 then                                                WriteFile(TempGLEntry, TempDimBufOut);                                        end;                                end;                            until TempGLEntry.Next() = 0;                    end;                    SetRange("Posting Date", ConsolidStartDate, ConsolidEndDate);                    OnAfterGLEntryOnAfterGetRecord("G/L Entry", TempSelectedDim);                end;                trigger OnPreDataItem()                begin                    SetRange("Posting Date", ConsolidStartDate, ConsolidEndDate);                    TempDimBufIn.SetRange("Table ID", DATABASE::"G/L Entry");                    TempDimBufOut.SetRange("Table ID", DATABASE::"G/L Entry");                    if ConsolidStartDate = NormalDate(ConsolidStartDate) then                        CheckClosingPostings("G/L Account"."No.", ConsolidStartDate, ConsolidEndDate);                end;            }            trigger OnAfterGetRecord()            begin                Window.Update(1, "No.");                Window.Update(2, '');                case FileFormat of                    FileFormat::"Version 4.00 or Later (.xml)":                        Consolidate.InsertGLAccount("G/L Account");                    FileFormat::"Version F&O":                        ExportFOConsolidation.ProcessGLBugdetEntries("No.", ConsolidStartDate, ConsolidEndDate);                end;            end;            trigger OnPostDataItem()            begin                if FileFormat = FileFormat::"Version 3.70 or Earlier (.txt)" then                    GLEntryFile.Close();            end;            trigger OnPreDataItem()            begin                if ServerFileName = '' then                    Error(Text000);                if ConsolidStartDate = 0D then                    Error(Text001);                if ConsolidEndDate = 0D then                    Error(Text002);                CheckClosingDates(ConsolidStartDate, ConsolidEndDate, TransferPerDay);                if NormalDate(ConsolidEndDate) - NormalDate(ConsolidStartDate) + 1 > 500 then                    Error(Text003);                if Dim.Find('-') then                    repeat                        TempDim.Init();                        TempDim := Dim;                        TempDim.Insert();                    until Dim.Next() = 0;                if DimVal.Find('-') then                    repeat                        TempDimVal.Init();                        TempDimVal := DimVal;                        TempDimVal.Insert();                    until DimVal.Next() = 0;                SelectedDim.SetRange("User ID", UserId);                SelectedDim.SetRange("Object Type", 3);                SelectedDim.SetRange("Object ID", REPORT::"Export Consolidation");                if SelectedDim.Find('-') then                    repeat                        TempSelectedDim.Init();                        TempSelectedDim := SelectedDim;                        TempDim.SetRange("Consolidation Code", SelectedDim."Dimension Code");                        if TempDim.FindFirst() then                            TempSelectedDim."Dimension Code" := TempDim.Code;                        TempSelectedDim.Insert();                    until SelectedDim.Next() = 0;                TempDim.Reset();                if FileFormat = FileFormat::"Version 3.70 or Earlier (.txt)" then begin                    Clear(GLEntryFile);                    GLEntryFile.TextMode := true;                    GLEntryFile.WriteMode := true;                    GLEntryFile.Create(ServerFileName, TEXTENCODING::UTF8);                    GLEntryFile.Write(                      StrSubstNo(                        '<01>#1############################ #2####### #3####### #4#',                        CompanyName, ConsolidStartDate, ConsolidEndDate, Format(TransferPerDay, 0, 2)));                end;                Window.Open(                  Text004 +                  Text005 +                  Text006);            end;        }        dataitem("Currency Exchange Rate"; "Currency Exchange Rate")        {            DataItemTableView = sorting("Currency Code", "Starting Date");            trigger OnAfterGetRecord()            begin                Consolidate.InsertExchRate("Currency Exchange Rate");            end;            trigger OnPostDataItem()            begin                case FileFormat of                    FileFormat::"Version 4.00 or Later (.xml)":                        begin                            Consolidate.SetGlobals(                              ProductVersion, FormatVersion, CompanyName,                              GLSetup."LCY Code", GLSetup."Additional Reporting Currency", ParentCurrencyCode,                              0, ConsolidStartDate, ConsolidEndDate);                            Consolidate.SetGlobals(                              ProductVersion, FormatVersion, CompanyName,                              GLSetup."LCY Code", GLSetup."Additional Reporting Currency", ParentCurrencyCode,                              Consolidate.CalcCheckSum(), ConsolidStartDate, ConsolidEndDate);                            Consolidate.ExportToXML(ServerFileName);                        end;                    FileFormat::"Version F&O":                        ExportFOConsolidation.ExportFile(ServerFileName);                end;            end;            trigger OnPreDataItem()            begin                if FileFormat = FileFormat::"Version 3.70 or Earlier (.txt)" then                    CurrReport.Break();                GLSetup.Get();                if GLSetup."Additional Reporting Currency" = '' then                    SetRange("Currency Code", ParentCurrencyCode)                else                    SetFilter("Currency Code", '%1|%2', ParentCurrencyCode, GLSetup."Additional Reporting Currency");                SetRange("Starting Date", 0D, ConsolidEndDate);            end;        }    }    requestpage    {        SaveValues = true;        layout        {            area(content)            {                group(Options)                {                    Caption = 'Options';                    field(FileFormat; FileFormat)                    {                        ApplicationArea = Suite;                        Caption = 'File Format';                        OptionCaption = 'Version 4.00 or Later (.xml),Version 3.70 or Earlier (.txt),Dynamics 365 Finance (.txt)';                        ToolTip = 'Specifies the file format that you want to use for the consolidation. If the parent company that will perform the consolidation also has Dynamics NAV 4.0 or later versions, select the .xml format. Otherwise, select the .txt format.';                        trigger OnValidate()                        begin                            SetControlsVisibility();                        end;                    }                    field(ClientFileNameControl; ClientFileName)                    {                        ApplicationArea = Suite;                        Caption = 'File Name';                        ToolTip = 'Specifies the name of the file to be exported from a business unit to a consolidated company.';                    }                    group("Consolidation Period")                    {                        Caption = 'Consolidation Period';                        field(StartDate; ConsolidStartDate)                        {                            ApplicationArea = Suite;                            Caption = 'Starting Date';                            ClosingDates = true;                            ToolTip = 'Specifies the first date in the period from which entries will be exported. If you use a closing date, the starting date and ending date must be the same.';                        }                        field(EndDate; ConsolidEndDate)                        {                            ApplicationArea = Suite;                            Caption = 'Ending Date';                            ClosingDates = true;                            ToolTip = 'Specifies the last date in the period from which entries will be exported.';                        }                    }                    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::"Export Consolidation", ColumnDim);                            end;                        }                    }                    field(ParentCurrencyCode; ParentCurrencyCode)                    {                        ApplicationArea = Suite;                        Caption = 'Parent Currency Code';                        TableRelation = Currency;                        ToolTip = 'Specifies the currency code of the company that will perform the consolidation.';                    }                    group("Legal Entity ID")                    {                        ShowCaption = false;                        Visible = FOLegalEntityIDVisible;                        field("F&O Legal Entity ID"; FOLegalEntityID)                        {                            ApplicationArea = Suite;                            Caption = 'F&&O Legal Entity ID';                            ToolTip = 'Specifies the F&O Legal Entity ID of the company that will perform the consolidation.';                            Enabled = true;                            ShowMandatory = true;                        }                    }                }            }        }        actions        {        }        trigger OnInit()        begin            FOLegalEntityIDVisible := false;        end;        trigger OnOpenPage()        begin            SetControlsVisibility();        end;    }    labels    {    }    trigger OnInitReport()    begin        TransferPerDay := true;    end;    trigger OnPostReport()    var        FileManagement: Codeunit "File Management";    begin        if IsWebClient() then begin            if FileFormat = FileFormat::"Version 4.00 or Later (.xml)" then                ClientFileName := ClientFileName + '.xml'            else                ClientFileName := ClientFileName + '.txt';            FileManagement.DownloadHandler(ServerFileName, '', '', '', ClientFileName);        end else            FileManagement.DownloadHandler(ServerFileName, '', '', '', ClientFileName);    end;    trigger OnPreReport()    var        FileMgt: Codeunit "File Management";    begin        DimSelectionBuf.CompareDimText(3, REPORT::"Export Consolidation", '', ColumnDim, Text007);        ServerFileName := FileMgt.ServerTempFileName('xml');        IsWebClient();        if FileFormat = FileFormat::"Version F&O" then begin            if FOLegalEntityID = '' then                Error(LegalEntityIDEmptyErr);            ExportFOConsolidation.SetFOLegalEntityID(FOLegalEntityID);        end;    end;    var        TempGLEntry: Record "G/L Entry" temporary;        DimSetEntry: Record "Dimension Set Entry";        Dim: Record Dimension;        DimVal: Record "Dimension Value";        TempDim: Record Dimension temporary;        TempDimVal: Record "Dimension Value" temporary;        SelectedDim: Record "Selected Dimension";        TempSelectedDim: Record "Selected Dimension" temporary;        TempDimBufIn: Record "Dimension Buffer" temporary;        TempDimBufOut: Record "Dimension Buffer" temporary;        DimSelectionBuf: Record "Dimension Selection Buffer";        GLSetup: Record "General Ledger Setup";        DimBufMgt: Codeunit "Dimension Buffer Management";        Consolidate: Codeunit Consolidate;        ExportFOConsolidation: Codeunit "Export F/O Consolidation";        ClientTypeManagement: Codeunit "Client Type Management";        Window: Dialog;        GLEntryFile: File;        ServerFileName: Text;        FileFormat: Option "Version 4.00 or Later (.xml)","Version 3.70 or Earlier (.txt)","Version F&O";        ConsolidStartDate: Date;        ConsolidEndDate: Date;        TransferPerDay: Boolean;        TransferPerDayReq: Boolean;        FOLegalEntityIDVisible: Boolean;        ColumnDim: Text[250];        ParentCurrencyCode: Code[10];        FOLegalEntityID: Code[4];        ClientFileName: Text;#pragma warning disable AA0074        ProductVersion: Label '4.00';        FormatVersion: Label '1.00';        Text000: Label 'Enter the file name.';        Text001: Label 'Enter the starting date for the consolidation period.';        Text002: Label 'Enter the ending date for the consolidation period.';        Text003: Label 'The export can include a maximum of 500 days.';        Text004: Label 'Processing the chart of accounts...\\';#pragma warning disable AA0470        Text005: Label 'No.             #1##########\';        Text006: Label 'Date            #2######';#pragma warning restore AA0470        Text007: Label 'Copy Dimensions';#pragma warning disable AA0470        Text009: Label 'A G/L Entry with posting date on a closing date (%1) was found while exporting nonclosing entries. G/L Account No. = %2.';#pragma warning restore AA0470        Text010: Label 'When using closing dates, the starting and ending dates must be the same.';#pragma warning restore AA0074        LegalEntityIDEmptyErr: Label 'You must provide a value in the F&O Legal Entity ID field.';    local procedure WriteFile(var GLEntry2: Record "G/L Entry"; var DimBuf: Record "Dimension Buffer")    var        GLEntryNo: Integer;    begin        case FileFormat of            FileFormat::"Version 4.00 or Later (.xml)":                GLEntryNo := Consolidate.InsertGLEntry(GLEntry2);            FileFormat::"Version F&O":                GLEntryNo := ExportFOConsolidation.InsertGLEntry(GLEntry2);            FileFormat::"Version 3.70 or Earlier (.txt)":                GLEntryFile.Write(                  StrSubstNo(                    '<02>#1################## #2####### #3####################',                    GLEntry2."G/L Account No.",                    GLEntry2."Posting Date",                    GLEntry2.Amount));        end;        if DimBuf.Find('-') then            repeat                case FileFormat of                    FileFormat::"Version 4.00 or Later (.xml)":                        Consolidate.InsertEntryDim(DimBuf, GLEntryNo);                    FileFormat::"Version F&O":                        ExportFOConsolidation.InsertEntryDim(DimBuf, GLEntryNo);                    FileFormat::"Version 3.70 or Earlier (.txt)":                        GLEntryFile.Write(                          StrSubstNo(                            '<03>#1################## #2##################',                            DimBuf."Dimension Code",                            DimBuf."Dimension Value Code"));                end;            until DimBuf.Next() = 0;    end;    local procedure UpdateTempGLEntry(var TempDimBuf: Record "Dimension Buffer" temporary)    var        DimEntryNo: Integer;    begin        DimEntryNo := DimBufMgt.FindDimensions(TempDimBuf);        if (not TempDimBuf.IsEmpty) and (DimEntryNo = 0) then begin            TempGLEntry := "G/L Entry";            TempGLEntry."Entry No." := DimBufMgt.InsertDimensions(TempDimBuf);            TempGLEntry.Insert();        end else            if TempGLEntry.Get(DimEntryNo) then begin                TempGLEntry.Amount := TempGLEntry.Amount + "G/L Entry".Amount;                TempGLEntry."Debit Amount" := TempGLEntry."Debit Amount" + "G/L Entry"."Debit Amount";                TempGLEntry."Credit Amount" := TempGLEntry."Credit Amount" + "G/L Entry"."Credit Amount";                TempGLEntry."Additional-Currency Amount" :=                  TempGLEntry."Additional-Currency Amount" + "G/L Entry"."Additional-Currency Amount";                TempGLEntry."Add.-Currency Debit Amount" :=                  TempGLEntry."Add.-Currency Debit Amount" + "G/L Entry"."Add.-Currency Debit Amount";                TempGLEntry."Add.-Currency Credit Amount" :=                  TempGLEntry."Add.-Currency Credit Amount" + "G/L Entry"."Add.-Currency Credit Amount";                TempGLEntry.Modify();            end else begin                TempGLEntry := "G/L Entry";                TempGLEntry."Entry No." := DimEntryNo;                TempGLEntry.Insert();            end;    end;    local procedure UpdateExportedInfo(var GLEntry3: Record "G/L Entry")    begin        if GLEntry3.Amount < 0 then begin            "G/L Account".TestField("Consol. Credit Acc.");            GLEntry3."G/L Account No." := "G/L Account"."Consol. Credit Acc.";        end else begin            "G/L Account".TestField("Consol. Debit Acc.");            GLEntry3."G/L Account No." := "G/L Account"."Consol. Debit Acc.";        end;        GLEntry3.Modify();    end;    local procedure CheckClosingPostings(GLAccNo: Code[20]; StartDate: Date; EndDate: Date)    var        GLEntry: Record "G/L Entry";        AccountingPeriod: Record "Accounting Period";    begin        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.SetRange("G/L Account No.", GLAccNo);            repeat                GLEntry.SetRange("Posting Date", ClosingDate(AccountingPeriod."Starting Date" - 1));                if not GLEntry.IsEmpty() then                    Error(                      Text009,                      GLEntry.GetFilter("Posting Date"),                      GLAccNo);            until AccountingPeriod.Next() = 0;        end;    end;    local procedure CheckClosingDates(StartDate: Date; EndDate: Date; var TransferPerDay: Boolean)    begin        if (StartDate = ClosingDate(StartDate)) or           (EndDate = ClosingDate(EndDate))        then begin            if StartDate <> EndDate then                Error(Text010);            TransferPerDay := false;        end else            TransferPerDay := TransferPerDayReq;    end;    /// <summary>    /// Initializes export parameters for consolidation data export processing.    /// Sets file format and output filename for the consolidation export operation.    /// </summary>    /// <param name="NewFileFormat">File format option for consolidation export (XML, text, or F&amp;O format)</param>    /// <param name="NewFileName">Output filename for the exported consolidation data</param>    procedure InitializeRequest(NewFileFormat: Option; NewFileName: Text)    begin        FileFormat := NewFileFormat;        ClientFileName := NewFileName;        ColumnDim := DimSelectionBuf.GetDimSelectionText(3, REPORT::"Export Consolidation", '');    end;    local procedure IsWebClient(): Boolean    begin        exit(ClientTypeManagement.GetCurrentClientType() in [CLIENTTYPE::Web, CLIENTTYPE::Tablet, CLIENTTYPE::Phone, CLIENTTYPE::Desktop]);    end;    local procedure SetControlsVisibility()    begin        FOLegalEntityIDVisible := FileFormat = FileFormat::"Version F&O";    end;    /// <summary>    /// Integration event raised after processing G/L Entry record during consolidation export.    /// Enables custom post-processing of G/L entry data and dimension handling.    /// </summary>    /// <param name="GLEntry">G/L Entry record being processed for export</param>    /// <param name="TempSelectedDim">Selected Dimension temporary record for dimension processing</param>    [IntegrationEvent(false, false)]    local procedure OnAfterGLEntryOnAfterGetRecord(var GLEntry: Record "G/L Entry"; var TempSelectedDim: Record "Selected Dimension" temporary)    begin    end;    /// <summary>    /// Integration event raised before processing G/L Entry record during consolidation export.    /// Enables custom pre-processing of G/L entry data and dimension selection.    /// </summary>    /// <param name="GLEntry">G/L Entry record about to be processed for export</param>    /// <param name="TempSelectedDim">Selected Dimension temporary record for dimension processing</param>    [IntegrationEvent(false, false)]    local procedure OnBeforeGLEntryOnAfterGetRecord(var GLEntry: Record "G/L Entry"; var TempSelectedDim: Record "Selected Dimension" temporary)    begin    end;}