Report 91 Export Consolidation
- App
- Base Application
- Namespace
- Microsoft.Finance.Consolidation
- Versions
- 17-28
Versions171819202122232425262728
Source29
Source in 29
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&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;
}