Report 11 G/L - VAT Reconciliation, source in 29
Source29
src/Layers/W1/BaseApp/Finance/VAT/Reporting/GLVATReconciliation.Report.al587 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.VAT.Reporting;
using Microsoft.Finance.Currency;
using Microsoft.Finance.GeneralLedger.Account;
using Microsoft.Finance.GeneralLedger.Setup;
using Microsoft.Finance.VAT.Ledger;
/// <summary>
/// Reconciles G/L account balances with VAT statement line calculations for accuracy verification.
/// Compares VAT amounts calculated from VAT entries against corresponding G/L account balances to identify discrepancies.
/// </summary>
report 11 "G/L - VAT Reconciliation"
{
ApplicationArea = Basic, Suite;
Caption = 'G/L - VAT Reconciliation';
UsageCategory = ReportsAndAnalysis;
DefaultRenderingLayout = RDLCLayout;
dataset
{
dataitem("VAT Statement Name"; "VAT Statement Name")
{
DataItemTableView = sorting("Statement Template Name", Name);
RequestFilterFields = Name;
column(VAT_Statement_Name_Statement_Template_Name; "Statement Template Name")
{
}
column(VAT_Statement_Name_Name; Name)
{
}
dataitem("VAT Statement Line"; "VAT Statement Line")
{
DataItemLink = "Statement Template Name" = field("Statement Template Name"), "Statement Name" = field(Name);
DataItemTableView = sorting("Statement Template Name", "Statement Name", "Line No.") where(Type = filter("Account Totaling" | "VAT Entry Totaling"));
PrintOnlyIfDetail = true;
column(VAT_Statement_Name__Name; "VAT Statement Name".Name)
{
}
column(VAT_Statement_Name___Statement_Template_Name_; "VAT Statement Name"."Statement Template Name")
{
}
column(COMPANYNAME; COMPANYPROPERTY.DisplayName())
{
}
column(USERID; UserId)
{
}
column(Header; Header)
{
}
column(FORMAT_TODAY_0_0______FORMAT_TIME_0_0_; Format(Today, 0, 0))
{
}
column(HeaderText; HeaderText)
{
}
column(Header2; Header2)
{
}
column(VAT_Statement_Line__TABLECAPTION__________VATStmtLineFilter; TableCaption + ': ' + VATStmtLineFilter)
{
}
column(VATStmtLineFilter; VATStmtLineFilter)
{
}
column(VAT_Statement_Line__Row_No__; "Row No.")
{
}
column(VAT_Statement_Line_Description; Description)
{
}
column(VAT_Statement_Line_Type; Type)
{
}
column(VAT_Statement_Line__Amount_Type_; "Amount Type")
{
}
column(VAT_Statement_Line__VAT_Bus__Posting_Group_; "VAT Bus. Posting Group")
{
}
column(VAT_Statement_Line__VAT_Prod__Posting_Group_; "VAT Prod. Posting Group")
{
}
column(VAT_Statement_Line__Gen__Posting_Type_; "Gen. Posting Type")
{
}
column(TypeNo; TypeNo)
{
}
column(TotalAmount; TotalAmount)
{
}
column(TotalVAT; TotalVAT)
{
}
column(VAT_Statement_Line_Statement_Template_Name; "Statement Template Name")
{
}
column(VAT_Statement_Line_Statement_Name; "Statement Name")
{
}
column(VAT_Statement_Line_Line_No_; "Line No.")
{
}
column(VAT_Statement_Name__NameCaption; VAT_Statement_Name__NameCaptionLbl)
{
}
column(VAT_Statement_Name___Statement_Template_Name_Caption; VAT_Statement_Name___Statement_Template_Name_CaptionLbl)
{
}
column(CurrReport_PAGENOCaption; CurrReport_PAGENOCaptionLbl)
{
}
column(GL_VAT_ReconciliationCaption; GL_VAT_ReconciliationCaptionLbl)
{
}
column(AmountCaption; AmountCaptionLbl)
{
}
column(VATCaption; VATCaptionLbl)
{
}
column(Account_No_Caption; Account_No_CaptionLbl)
{
}
column(G_L_Account_Name_Control1140052Caption; "G/L Account".FieldCaption(Name))
{
}
column(Account_TypeCaption; Account_TypeCaptionLbl)
{
}
column(VAT_Statement_Line__VAT_Prod__Posting_Group_Caption; FieldCaption("VAT Prod. Posting Group"))
{
}
column(VAT_Statement_Line__VAT_Bus__Posting_Group_Caption; FieldCaption("VAT Bus. Posting Group"))
{
}
column(VAT_Statement_Line__Gen__Posting_Type_Caption; FieldCaption("Gen. Posting Type"))
{
}
column(VAT_Statement_Line__Amount_Type_Caption; FieldCaption("Amount Type"))
{
}
column(VAT_Statement_Line___Account_Totaling_Caption; VAT_Statement_Line___Account_Totaling_CaptionLbl)
{
}
column(VAT_Statement_Line_TypeCaption; FieldCaption(Type))
{
}
column(VAT_Statement_Line_DescriptionCaption; FieldCaption(Description))
{
}
column(VAT_Statement_Line__Row_No__Caption; FieldCaption("Row No."))
{
}
column(Grand_TotalCaption; Grand_TotalCaptionLbl)
{
}
dataitem("G/L Account"; "G/L Account")
{
DataItemTableView = sorting("No.") where("No." = filter(<> ''));
column(VAT_Statement_Line___Amount_Type_; Format("VAT Statement Line"."Amount Type"))
{
}
column(VAT_Statement_Line__Description; "VAT Statement Line".Description)
{
}
column(VAT_Statement_Line__Type; Format("VAT Statement Line".Type))
{
}
column(VAT_Statement_Line___Row_No__; "VAT Statement Line"."Row No.")
{
}
column(VAT_Statement_Line___Account_Totaling_; "VAT Statement Line"."Account Totaling")
{
}
column(Number; Number)
{
}
column(CountTotals; CountTotals)
{
}
column(Identifier; Identifier)
{
}
column(VAT_Statement_Line___Row_No___Control1140044; "VAT Statement Line"."Row No.")
{
}
column(VAT_Statement_Line___Amount_Type__Control1140045; "VAT Statement Line"."Amount Type")
{
}
column(VAT_Statement_Line___Account_Totaling__Control1140046; "VAT Statement Line"."Account Totaling")
{
}
column(VAT_Statement_Line__Type_Control1140047; "VAT Statement Line".Type)
{
}
column(VAT; VAT)
{
}
column(Amount1; Amount1)
{
}
column(G_L_Account_Name; Name)
{
}
column(G_L_Account__No__; "No.")
{
}
column(G_L_Account_Name_Control1140052; Name)
{
}
column(G_L_Account__No___Control1140053; "No.")
{
}
column(G_L_Account__TABLECAPTION; TableCaption)
{
}
column(Amount1_Control1140055; Amount1)
{
}
column(VAT_Control1140056; VAT)
{
}
column(VAT_Control1140058; VAT)
{
}
column(Amount1_Control1140059; Amount1)
{
}
column(TotalCaption; TotalCaptionLbl)
{
}
trigger OnAfterGetRecord()
begin
if "VAT Statement Line".Type = "VAT Statement Line".Type::"Account Totaling" then begin
CalcFields("Net Change", "Additional-Currency Net Change", "VAT Amt.");
Amount1 := ConditionalAdd("Net Change", "Additional-Currency Net Change");
VAT := ConditionalAdd("VAT Amt.", ExchangeAmtLCYtoFCY("VAT Amt."));
end else begin
VATEntry.SetRange("G/L Acc. No.", "No.");
if VATEntry.IsEmpty() then
CurrReport.Skip();
case "VAT Statement Line"."Amount Type" of
"VAT Statement Line"."Amount Type"::" ", "VAT Statement Line"."Amount Type"::Amount, "VAT Statement Line"."Amount Type"::Base:
begin
VATEntry.CalcSums(Base, "Additional-Currency Base", Amount, "Additional-Currency Amount");
Amount1 := ConditionalAdd(VATEntry.Base, VATEntry."Additional-Currency Base");
VAT := ConditionalAdd(VATEntry.Amount, VATEntry."Additional-Currency Amount");
end;
"VAT Statement Line"."Amount Type"::"Unrealized Amount", "VAT Statement Line"."Amount Type"::"Unrealized Base":
begin
VATEntry.CalcSums("Unrealized Base", "Add.-Currency Unrealized Base", "Unrealized Amount", "Add.-Currency Unrealized Amt.");
Amount1 := ConditionalAdd(VATEntry."Unrealized Base", VATEntry."Add.-Currency Unrealized Base");
VAT := ConditionalAdd(VATEntry."Unrealized Amount", VATEntry."Add.-Currency Unrealized Amt.");
end;
VATStmtLine2."Amount Type"::"Non-Deductible Base", VATStmtLine2."Amount Type"::"Non-Deductible Amount":
begin
VATEntry.CalcSums("Non-Deductible VAT Base", "Non-Deductible VAT Base ACY", "Non-Deductible VAT Amount", "Non-Deductible VAT Amount ACY");
Amount1 := ConditionalAdd(VATEntry."Non-Deductible VAT Base", VATEntry."Non-Deductible VAT Base ACY");
VAT := ConditionalAdd(VATEntry."Non-Deductible VAT Amount", VATEntry."Non-Deductible VAT Amount ACY");
end;
VATStmtLine2."Amount Type"::"Full Base", VATStmtLine2."Amount Type"::"Full Amount":
begin
VATEntry.CalcSums(
Base, "Additional-Currency Base", Amount, "Additional-Currency Amount",
"Non-Deductible VAT Base", "Non-Deductible VAT Base ACY", "Non-Deductible VAT Amount", "Non-Deductible VAT Amount ACY");
Amount1 :=
ConditionalAdd(
VATEntry.Base + VATEntry."Non-Deductible VAT Base",
VATEntry."Additional-Currency Base" + VATEntry."Non-Deductible VAT Base ACY");
VAT :=
ConditionalAdd(
VATEntry.Amount + VATEntry."Non-Deductible VAT Amount",
VATEntry."Additional-Currency Amount" + VATEntry."Non-Deductible VAT Amount ACY");
end;
end;
end;
OnBeforeCalcTotalAmount("VAT Statement Line", VATEntry, Amount1, VAT);
TotalAmount := TotalAmount + Amount1;
TotalVAT := TotalVAT + VAT;
end;
trigger OnPostDataItem()
begin
if Number > 1 then
if (Amount1 <> 0) or (VAT <> 0) then
CountTotals := CountTotals + 1;
end;
trigger OnPreDataItem()
var
VATEntryCopy: Record "VAT Entry";
begin
if "VAT Statement Line".Type = "VAT Statement Line".Type::"Account Totaling" then begin
SetFilter("No.", "VAT Statement Line"."Account Totaling");
SetRange("Date Filter", StartDate, EndDate);
Number := count();
end else begin
Number := 2;
VATEntry.SetCurrentKey("Posting Date", Type, Closed, "VAT Bus. Posting Group", "VAT Prod. Posting Group", Reversed, "G/L Acc. No.");
VATEntry.SetRange(Type, "VAT Statement Line"."Gen. Posting Type");
case Selection of
Selection::Open:
VATEntry.SetRange(Closed, false);
Selection::Closed:
VATEntry.SetRange(Closed, true);
end;
VATEntry.SetRange("VAT Bus. Posting Group", "VAT Statement Line"."VAT Bus. Posting Group");
VATEntry.SetRange("VAT Prod. Posting Group", "VAT Statement Line"."VAT Prod. Posting Group");
if (EndDateReq <> 0D) or (StartDate <> 0D) then
if PeriodSelection = PeriodSelection::"Before and Within Period" then
VATEntry.SetRange("VAT Reporting Date", 0D, EndDate)
else
VATEntry.SetRange("VAT Reporting Date", StartDate, EndDate);
VATEntry.SetRange(Reversed, false);
end;
VATEntryCopy.Copy(VATEntry);
VATEntryCopy.SetCurrentKey("Entry No.");
VATEntryCopy.SetGLAccountNoWithResponse(true, AdjustVATEntryConfirm, AdjustVATEntry);
AdjustVATEntryConfirm := false;
CheckGLAccountNoFilled(VATEntryCopy);
Identifier := Identifier + 1;
end;
}
trigger OnAfterGetRecord()
begin
if (Type = Type::"Account Totaling") and ("Account Totaling" = '') then
CurrReport.Skip();
VATStmtLine2.Get("Statement Template Name", "Statement Name", "Line No.");
VATStmtLine2.SetRange("Row No.", "Row No.");
VATStmtLine2.SetRange("Gen. Posting Type", "Gen. Posting Type");
VATStmtLine2.SetRange("VAT Bus. Posting Group", "VAT Bus. Posting Group");
VATStmtLine2.SetRange("VAT Prod. Posting Group", "VAT Prod. Posting Group");
if VATStmtLine2.Find('<') then
CurrReport.Skip();
TotalAmount := 0;
TotalVAT := 0;
CountTotals := 0;
TypeNo := Type.AsInteger();
end;
trigger OnPreDataItem()
begin
VATStmtLine2.CopyFilters("VAT Statement Line");
end;
}
trigger OnPreDataItem()
begin
if UseAmtsInAddCurr then
HeaderText := StrSubstNo(AllAmountsLbl, GLSetup."Additional Reporting Currency")
else begin
GLSetup.TestField("LCY Code");
HeaderText := StrSubstNo(AllAmountsLbl, GLSetup."LCY Code");
end;
end;
}
}
requestpage
{
AboutTitle = 'About G/L - VAT Reconciliation';
AboutText = 'The **G/L - VAT Reconciliation** report compares VAT amounts posted in the general ledger with the underlying VAT entries. Use it for validating VAT accuracy during filing periods to ensure reported amounts match transaction-level data. It helps detect discrepancies early and supports audit readiness by reconciling VAT postings with actual entries.';
SaveValues = true;
layout
{
area(content)
{
group(Options)
{
Caption = 'Options';
group("Statement Period")
{
Caption = 'Statement Period';
field(StartDate; StartDate)
{
ApplicationArea = Basic, Suite;
Caption = 'Starting Date';
ToolTip = 'Specifies the date from which the report or batch job processes information.';
}
field(EndDateReq; EndDateReq)
{
ApplicationArea = Basic, Suite;
Caption = 'Ending Date';
ToolTip = 'Specifies the last date that the report includes data for.';
}
}
field(Selection; Selection)
{
ApplicationArea = Basic, Suite;
Caption = 'Include VAT Entries by State';
ToolTip = 'Specifies if you want to include VAT entries that are open and/or closed in the report.';
}
field(PeriodSelection; PeriodSelection)
{
ApplicationArea = Basic, Suite;
Caption = 'Include VAT Entries by Period';
ToolTip = 'Specifies if you want to include VAT entries from before the specified time period in the report.';
}
field(UseAmtsInAddCurr; UseAmtsInAddCurr)
{
ApplicationArea = Basic, Suite;
Caption = 'Show Amounts in Add. Reporting Currency';
MultiLine = true;
ToolTip = 'Specifies if you want report amounts to be shown in the additional reporting currency.';
}
}
}
}
actions
{
}
}
rendering
{
layout(RDLCLayout)
{
Type = RDLC;
LayoutFile = './Finance/VAT/Reporting/GLVATReconciliation.rdlc';
Summary = 'Report layout made in the legacy RDLC format. Use an RDLC editor to modify the layout.';
}
}
labels
{
}
trigger OnPreReport()
begin
AdjustVATEntry := false;
AdjustVATEntryConfirm := true;
if EndDateReq = 0D then
EndDate := 99991231D
else
EndDate := EndDateReq;
"VAT Statement Line".SetRange("Date Filter", StartDate, EndDate);
VATStmtLineFilter := "VAT Statement Line".GetFilters();
if PeriodSelection = PeriodSelection::"Before and Within Period" then
Header := BeforeAndWithinPeriodLbl
else
Header := PeriodLbl + "VAT Statement Line".GetFilter("Date Filter");
case Selection of
Selection::Closed:
Header2 := OnlyClosedVATEntriesLbl;
Selection::"Open and Closed":
Header2 := AllVATEntriesLbl;
end;
GLSetup.Get();
if UseAmtsInAddCurr then begin
GLSetup.TestField("Additional Reporting Currency");
Currency.Get(GLSetup."Additional Reporting Currency");
CurrencyFactor := CurrencyExchRate.ExchangeRate(WorkDate(), GLSetup."Additional Reporting Currency");
end;
end;
var
VATEntry: Record "VAT Entry";
GLSetup: Record "General Ledger Setup";
VATStmtLine2: Record "VAT Statement Line";
CurrencyExchRate: Record "Currency Exchange Rate";
Currency: Record Currency;
UseAmtsInAddCurr: Boolean;
EndDate: Date;
StartDate: Date;
EndDateReq: Date;
Amount1: Decimal;
VAT: Decimal;
CurrencyFactor: Decimal;
TotalAmount: Decimal;
TotalVAT: Decimal;
CountTotals: Integer;
Number: Integer;
Selection: Enum "VAT Statement Report Selection";
PeriodSelection: Enum "VAT Statement Report Period Selection";
VATStmtLineFilter: Text;
Header: Text;
Header2: Text[50];
HeaderText: Text[50];
TypeNo: Integer;
Identifier: Integer;
AdjustVATEntry: Boolean;
AdjustVATEntryConfirm: Boolean;
NoGLAccNoOnVATEntriesErr: Label 'There is one or more VAT Entries with no G/L Account defined in the selected period. Please exclude these VAT entries or ask your partner to help you fix this data issue.';
BeforeAndWithinPeriodLbl: Label 'VAT entries before and within the period';
PeriodLbl: Label 'Period: ';
OnlyClosedVATEntriesLbl: Label 'The report includes only closed VAT entries.';
AllVATEntriesLbl: Label 'The report includes all VAT entries.';
AllAmountsLbl: Label 'All amounts are in %1', Comment = '%1 = currency';
VAT_Statement_Name__NameCaptionLbl: Label 'VAT Statement Name';
VAT_Statement_Name___Statement_Template_Name_CaptionLbl: Label 'VAT Statement Template';
CurrReport_PAGENOCaptionLbl: Label 'Page';
GL_VAT_ReconciliationCaptionLbl: Label 'G/L - VAT Reconciliation';
AmountCaptionLbl: Label 'Amount';
VATCaptionLbl: Label 'VAT';
Account_No_CaptionLbl: Label 'Account No.';
Account_TypeCaptionLbl: Label 'Account Type';
VAT_Statement_Line___Account_Totaling_CaptionLbl: Label 'Account Totaling';
Grand_TotalCaptionLbl: Label 'Grand Total';
TotalCaptionLbl: Label 'Total';
/// <summary>
/// Conditionally returns amount in either local currency or additional reporting currency.
/// Selects appropriate currency amount based on report currency display preferences.
/// </summary>
/// <param name="AmountToAdd">Amount in local currency</param>
/// <param name="AddCurrAmountToAdd">Amount in additional reporting currency</param>
/// <returns>Selected amount based on UseAmtsInAddCurr setting</returns>
procedure ConditionalAdd(AmountToAdd: Decimal; AddCurrAmountToAdd: Decimal): Decimal
begin
if UseAmtsInAddCurr then
exit(AddCurrAmountToAdd);
exit(AmountToAdd);
end;
/// <summary>
/// Converts local currency amount to foreign currency equivalent using exchange rates.
/// Applies currency conversion for additional reporting currency display purposes.
/// </summary>
/// <param name="Amount">Amount in local currency to convert</param>
/// <returns>Converted amount in foreign currency based on exchange rate settings</returns>
procedure ExchangeAmtLCYtoFCY(Amount: Decimal): Decimal
begin
if not UseAmtsInAddCurr then
exit(Amount);
exit(Round(CurrencyExchRate.ExchangeAmtLCYToFCY(WorkDate(), GLSetup."Additional Reporting Currency", Amount, CurrencyFactor), Currency."Amount Rounding Precision"));
end;
local procedure CheckGLAccountNoFilled(var VATEntry2: Record "VAT Entry")
var
VATEntryLocal: Record "VAT Entry";
begin
VATEntryLocal.Copy(VATEntry2);
VATEntryLocal.ReadIsolation := IsolationLevel::ReadCommitted;
VATEntryLocal.SetCurrentKey("G/L Acc. No.");
VATEntryLocal.SetRange("G/L Acc. No.", '');
if not VATEntryLocal.IsEmpty() then
Error(NoGLAccNoOnVATEntriesErr);
end;
/// <summary>
/// Integration event raised before calculating total amounts during G/L VAT reconciliation.
/// Enables custom calculation logic and modification of amount totals before standard processing.
/// </summary>
/// <param name="VATStmtLine">VAT statement line being reconciled</param>
/// <param name="TempVATEntryTable">Temporary VAT entry table for calculation</param>
/// <param name="Amount1">Base amount calculation, can be modified</param>
/// <param name="VAT">VAT amount calculation, can be modified</param>
[IntegrationEvent(false, false)]
local procedure OnBeforeCalcTotalAmount(VATStmtLine: Record "VAT Statement Line"; var TempVATEntryTable: Record "VAT Entry" temporary; var Amount1: Decimal; var VAT: Decimal)
begin
end;
}