Report 11 G/L - VAT Reconciliation

Base Application, Microsoft.Finance.VAT.Reporting

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;}