Report 329 Vendor - Trial Balance
src/Layers/W1/BaseApp/Purchases/Reports/VendorTrialBalance.Report.al307 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.Purchases.Reports;using Microsoft.Foundation.Period;using Microsoft.Purchases.Payables;using Microsoft.Purchases.Vendor;using System.Telemetry;report 329 "Vendor - Trial Balance"{ ApplicationArea = Basic, Suite; Caption = 'Vendor - Trial Balance'; ToolTip = 'View the beginning and ending balance for vendors with entries within a specified period. The report can be used to verify that the balance for a vendor posting group is equal to the balance on the corresponding general ledger account on a certain date.'; PreviewMode = PrintLayout; UsageCategory = ReportsAndAnalysis; DefaultRenderingLayout = RDLCLayout; dataset { dataitem(Vendor; Vendor) { DataItemTableView = sorting("Vendor Posting Group"); RequestFilterFields = "No.", "Date Filter", "Vendor Posting Group"; column(CompanyName; COMPANYPROPERTY.DisplayName()) { } column(PeriodPeriodFilter; StrSubstNo(Text003, PeriodFilter)) { } column(VendPostGrpGroupTotal; StrSubstNo(Text005, FieldCaption("Vendor Posting Group"))) { } column(VendTblCapVendFilter; TableCaption + ': ' + VendFilter) { } column(VendFilter; VendFilter) { } column(PeriodStartDate; Format(PeriodStartDate)) { } column(PeriodFilter; PeriodFilter) { } column(FiscalYearStartDate; Format(FiscalYearStartDate)) { } column(FiscalYearFilter; FiscalYearFilter) { } column(PeriodEndDate; Format(PeriodEndDate)) { } column(VendorPostingGroup_Vendor; "Vendor Posting Group") { } column(YTDTotal; YTDTotal) { AutoFormatType = 1; } column(YTDCreditAmt; YTDCreditAmt) { AutoFormatType = 1; } column(YTDDebitAmt; YTDDebitAmt) { AutoFormatType = 1; } column(YTDBeginBalance; YTDBeginBalance) { } column(PeriodCreditAmt; PeriodCreditAmt) { } column(PeriodDebitAmt; PeriodDebitAmt) { } column(PeriodBeginBalance; PeriodBeginBalance) { } column(Name_Vendor; Name) { IncludeCaption = true; } column(No_Vendor; "No.") { IncludeCaption = true; } column(TotForFrmtVendPostGrp; Text004 + Format(' ') + "Vendor Posting Group") { } column(VendTrialBalanceCap; VendTrialBalanceCapLbl) { } column(CurrReportPageNoCaption; CurrReportPageNoCaptionLbl) { } column(AmountsinLCYCaption; AmountsinLCYCaptionLbl) { } column(VendWithEntryPeriodCapt; VendWithEntryPeriodCaptLbl) { } column(PeriodBeginBalCap; PeriodBeginBalCapLbl) { } column(PeriodDebitAmtCaption; PeriodDebitAmtCaptionLbl) { } column(PeriodCreditAmtCaption; PeriodCreditAmtCaptionLbl) { } column(YTDTotalCaption; YTDTotalCaptionLbl) { } column(PeriodCaption; PeriodCaptionLbl) { } column(FiscalYearToDateCaption; FiscalYearToDateCaptionLbl) { } column(NetChangeCaption; NetChangeCaptionLbl) { } column(TotalinLCYCaption; TotalinLCYCaptionLbl) { } trigger OnAfterGetRecord() begin CalcAmounts( PeriodStartDate, PeriodDebitTotals, PeriodCreditTotals, PeriodBeginBalance, PeriodDebitAmt, PeriodCreditAmt, YTDTotal); CalcAmounts( FiscalYearStartDate, YTDDebitTotals, YTDCreditTotals, YTDBeginBalance, YTDDebitAmt, YTDCreditAmt, YTDTotal); end; trigger OnPreDataItem() var Telemetry: Codeunit Telemetry; CustomDimensions: Dictionary of [Text, Text]; StartTime: DateTime; begin StartTime := CurrentDateTime(); GetDebitCreditTotals(PeriodStartDate, PeriodEndDate, PeriodDebitTotals, PeriodCreditTotals); GetDebitCreditTotals(FiscalYearStartDate, PeriodEndDate, YTDDebitTotals, YTDCreditTotals); CustomDimensions.Add('DurationSec', Format(Round((CurrentDateTime() - StartTime) / 1000, 1))); Telemetry.LogMessage('0000TZI', DebitCreditTotalsLbl, Verbosity::Normal, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, CustomDimensions); end; } } requestpage { AboutTitle = 'About Vendor - Trial Balance'; AboutText = 'View the closing balances of vendors at the end of a period to reconcile the vendor subledger against payables accounts in the general ledger. View beginning balances and net changes by vendor for the period and fiscal year to date.'; layout { } actions { } } rendering { layout(RDLCLayout) { Type = RDLC; LayoutFile = './Purchases/Reports/VendorTrialBalance.rdlc'; Summary = 'Report layout made in the legacy RDLC format. Use an RDLC editor to modify the layout.'; } } labels { } trigger OnPreReport() begin PeriodFilter := Vendor.GetFilter("Date Filter"); PeriodStartDate := Vendor.GetRangeMin("Date Filter"); PeriodEndDate := Vendor.GetRangeMax("Date Filter"); Vendor.SetRange("Date Filter"); VendFilter := Vendor.GetFilters(); Vendor.SetRange("Date Filter", PeriodStartDate, PeriodEndDate); AccountingPeriod.SetRange("Starting Date", 0D, PeriodEndDate); AccountingPeriod.SetRange("New Fiscal Year", true); if AccountingPeriod.FindLast() then FiscalYearStartDate := AccountingPeriod."Starting Date" else Error(Text000, AccountingPeriod.FieldCaption("Starting Date"), AccountingPeriod.TableCaption()); FiscalYearFilter := Format(FiscalYearStartDate) + '..' + Format(PeriodEndDate); end; var AccountingPeriod: Record "Accounting Period"; PeriodDebitTotals: Dictionary of [Code[20], Decimal]; PeriodCreditTotals: Dictionary of [Code[20], Decimal]; YTDDebitTotals: Dictionary of [Code[20], Decimal]; YTDCreditTotals: Dictionary of [Code[20], Decimal]; PeriodBeginBalance: Decimal; PeriodDebitAmt: Decimal; PeriodCreditAmt: Decimal; YTDBeginBalance: Decimal; YTDDebitAmt: Decimal; YTDCreditAmt: Decimal; YTDTotal: Decimal; PeriodFilter: Text; FiscalYearFilter: Text; VendFilter: Text; PeriodStartDate: Date; PeriodEndDate: Date; FiscalYearStartDate: Date; VendTrialBalanceCapLbl: Label 'Vendor - Trial Balance'; CurrReportPageNoCaptionLbl: Label 'Page'; AmountsinLCYCaptionLbl: Label 'Amounts in LCY'; VendWithEntryPeriodCaptLbl: Label 'Only includes vendors with entries in the period'; PeriodBeginBalCapLbl: Label 'Beginning Balance'; PeriodDebitAmtCaptionLbl: Label 'Debit'; PeriodCreditAmtCaptionLbl: Label 'Credit'; YTDTotalCaptionLbl: Label 'Ending Balance'; PeriodCaptionLbl: Label 'Period'; FiscalYearToDateCaptionLbl: Label 'Fiscal Year-To-Date'; NetChangeCaptionLbl: Label 'Net Change'; TotalinLCYCaptionLbl: Label 'Total in LCY'; DebitCreditTotalsLbl: Label 'Debit/Credit totals received for Vendor Trial Balance', Locked = true;#pragma warning disable AA0074#pragma warning disable AA0470 Text000: Label 'It was not possible to find a %1 in %2.'; Text003: Label 'Period: %1';#pragma warning restore AA0470 Text004: Label 'Total for';#pragma warning disable AA0470 Text005: Label 'Group Totals: %1';#pragma warning restore AA0470#pragma warning restore AA0074 local procedure CalcAmounts(DateFrom: Date; var DebitTotals: Dictionary of [Code[20], Decimal]; var CreditTotals: Dictionary of [Code[20], Decimal]; var BeginBalance: Decimal; var DebitAmt: Decimal; var CreditAmt: Decimal; var TotalBalance: Decimal) var VendorCopy: Record Vendor; begin VendorCopy.Copy(Vendor); VendorCopy.SetRange("Date Filter", 0D, DateFrom - 1); VendorCopy.CalcFields("Net Change (LCY)"); BeginBalance := -VendorCopy."Net Change (LCY)"; if not DebitTotals.Get(Vendor."No.", DebitAmt) then DebitAmt := 0; if not CreditTotals.Get(Vendor."No.", CreditAmt) then CreditAmt := 0; TotalBalance := BeginBalance + DebitAmt - CreditAmt; end; local procedure GetDebitCreditTotals(DateFrom: Date; DateTo: Date; var DebitTotals: Dictionary of [Code[20], Decimal]; var CreditTotals: Dictionary of [Code[20], Decimal]) var DetailedVendorLedgEntry: Record "Detailed Vendor Ledg. Entry"; VendorDebitCreditAmount: Query "Vendor Debit Credit Amount"; FilterText: Text; begin Clear(DebitTotals); Clear(CreditTotals); FilterText := Vendor.GetFilter("No."); if FilterText <> '' then VendorDebitCreditAmount.SetFilter(Vendor_No, FilterText); VendorDebitCreditAmount.SetFilter(Entry_Type, '<>%1', DetailedVendorLedgEntry."Entry Type"::Application); VendorDebitCreditAmount.SetRange(Posting_Date, DateFrom, DateTo); FilterText := Vendor.GetFilter("Global Dimension 1 Filter"); if FilterText <> '' then VendorDebitCreditAmount.SetFilter(Initial_Entry_Global_Dim_1, FilterText); FilterText := Vendor.GetFilter("Global Dimension 2 Filter"); if FilterText <> '' then VendorDebitCreditAmount.SetFilter(Initial_Entry_Global_Dim_2, FilterText); FilterText := Vendor.GetFilter("Currency Filter"); if FilterText <> '' then VendorDebitCreditAmount.SetFilter(Currency_Code, FilterText); FilterText := Vendor.GetFilter("Vendor Posting Group"); if FilterText <> '' then VendorDebitCreditAmount.SetFilter(Vendor_Posting_Group, FilterText); VendorDebitCreditAmount.Open(); while VendorDebitCreditAmount.Read() do begin DebitTotals.Set(VendorDebitCreditAmount.Vendor_No, VendorDebitCreditAmount.Sum_Debit_Amount_LCY); CreditTotals.Set(VendorDebitCreditAmount.Vendor_No, VendorDebitCreditAmount.Sum_Credit_Amount_LCY); end; VendorDebitCreditAmount.Close(); end;}