Report 321 Vendor - Balance to Date
src/Layers/W1/BaseApp/Purchases/Reports/VendorBalancetoDate.Report.al606 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.Finance.Currency;using Microsoft.Purchases.Payables;using Microsoft.Purchases.Vendor;using Microsoft.Utilities;using System.Text;using System.Utilities;report 321 "Vendor - Balance to Date"{ ApplicationArea = Basic, Suite; Caption = 'Vendor - Balance to Date'; ToolTip = 'View, print, or save a detail balance to date for selected vendors.'; PreviewMode = PrintLayout; UsageCategory = ReportsAndAnalysis; DataAccessIntent = ReadOnly; WordMergeDataItem = Vendor; DefaultRenderingLayout = RDLCLayout; dataset { dataitem(Vendor; Vendor) { DataItemTableView = sorting("No."); PrintOnlyIfDetail = true; RequestFilterFields = "No.", "Search Name", Blocked, "Date Filter"; column(StrNoVenGetMaxDtFilter; StrSubstNo(Text000, Format(GetRangeMax("Date Filter")))) { } column(CompanyName; COMPANYPROPERTY.DisplayName()) { } column(VendFilter; VendFilter) { } column(PrintAmountInLCY; PrintAmountInLCY) { } column(PrintOnePrPage; PrintOnePrPage) { } column(VendorCaption; TableCaption + ': ' + VendFilter) { } column(No_Vendor; "No.") { } column(Name_Vendor; Name) { } column(PhoneNo_Vendor; "Phone No.") { IncludeCaption = true; } column(VendorBalancetoDateCptn; VendorBalancetoDateCptnLbl) { } column(PageNoCaption; PageNoCaptionLbl) { } column(AllamountsareinLCYCaption; AllamountsareinLCYCaptionLbl) { } column(PostingDateCption; PostingDateCptionLbl) { } column(OriginalAmtCaption; OriginalAmtCaptionLbl) { } column(DocNoCaption; DocNoCaption) { } dataitem(VendLedgEntry3; "Vendor Ledger Entry") { DataItemTableView = sorting("Entry No."); column(PostDt_VendLedgEntry3; Format("Posting Date")) { } column(DocType_VendLedgEntry3; "Document Type") { IncludeCaption = true; } column(DocNo_VendLedgEntry3; VendLedgDocumentNo) { } column(Desc_VendLedgEntry3; Description) { IncludeCaption = true; } column(OriginalAmt; Format(OriginalAmt, 0, AutoFormat.ResolveAutoFormat(Enum::"Auto Format"::AmountFormat, CurrencyCode))) { } column(EntryNo_VendLedgEntry3; "Entry No.") { IncludeCaption = true; } column(CurrencyCode; CurrencyCode) { } dataitem("Detailed Vendor Ledg. Entry"; "Detailed Vendor Ledg. Entry") { DataItemLink = "Vendor Ledger Entry No." = field("Entry No."), "Posting Date" = field("Date Filter"); DataItemTableView = sorting("Vendor Ledger Entry No.", "Posting Date") where("Entry Type" = filter(<> "Initial Entry")); column(EntryTp_DtldVendLedgEntry; "Entry Type") { } column(PostDate_DtldVendLedEnt; Format("Posting Date")) { } column(DocType_DtldVendLedEnt; "Document Type") { } column(DocNo_DtldVendLedgEntry; DtldVendLedgDocumentNo) { } column(Amt; Format(Amt, 0, AutoFormat.ResolveAutoFormat(Enum::"Auto Format"::AmountFormat, CurrencyCode))) { } column(CurrencyCode1; CurrencyCode) { } column(DtldVendtLedgEntryNum; DtldVendtLedgEntryNum) { } column(RemainingAmt; Format(RemainingAmt, 0, AutoFormat.ResolveAutoFormat(Enum::"Auto Format"::AmountFormat, CurrencyCode))) { } trigger OnAfterGetRecord() begin if not PrintUnappliedEntries then if Unapplied then CurrReport.Skip(); if PrintAmountInLCY then begin Amt := "Amount (LCY)"; CurrencyCode := ''; end else begin Amt := Amount; CurrencyCode := "Currency Code"; end; if Amt = 0 then CurrReport.Skip(); if UseExternalDocNo then DtldVendLedgDocumentNo := GetAppliedEntryExternalDocNo("Applied Vend. Ledger Entry No.") else DtldVendLedgDocumentNo := "Document No."; DtldVendtLedgEntryNum := DtldVendtLedgEntryNum + 1; end; trigger OnPreDataItem() begin DtldVendtLedgEntryNum := 0; VendLedgEntry3.CopyFilter("Posting Date", "Posting Date"); end; } trigger OnAfterGetRecord() begin if PrintAmountInLCY then begin CalcFields("Original Amt. (LCY)", "Remaining Amt. (LCY)"); OriginalAmt := "Original Amt. (LCY)"; RemainingAmt := "Remaining Amt. (LCY)"; CurrencyCode := ''; end else begin CalcFields("Original Amount", "Remaining Amount"); OriginalAmt := "Original Amount"; RemainingAmt := "Remaining Amount"; CurrencyCode := "Currency Code"; end; if UseExternalDocNo then begin VendLedgDocumentNo := "External Document No."; if VendLedgDocumentNo = '' then VendLedgDocumentNo := "Document No."; end else VendLedgDocumentNo := "Document No."; OnAfterVendLedgEntry3OnAfterGetRecord(VendLedgEntry3, PrintAmountInLCY, OriginalAmt, RemainingAmt, CurrencyCode); end; trigger OnPreDataItem() begin Reset(); FilterDetailedVendLedgerEntry(DtldVendLedgEntry, StrSubstNo('%1..%2', MaxDate + 1, DMY2Date(31, 12, 9999))); if DtldVendLedgEntry.Find('-') then repeat "Entry No." := DtldVendLedgEntry."Vendor Ledger Entry No."; if CheckVendLedgerEntryIncludedWithCache("Entry No.") then Mark(true); until DtldVendLedgEntry.Next() = 0; FilterVendorLedgerEntry(VendLedgEntry3); if Find('-') then repeat if CheckVendLedgerEntryIncludedWithCache("Entry No.") then Mark(true); until Next() = 0; SetCurrentKey("Entry No."); SetRange(Open); MarkedOnly(true); SetRange("Date Filter", 0D, MaxDate); AddVendorDimensionFilter(VendLedgEntry3); CalcTotalVendorAmount(); end; } dataitem(Integer2; "Integer") { DataItemTableView = sorting(Number) where(Number = filter(1 ..)); column(Name1_Vendor; Vendor.Name) { } column(CurrTotalBufferTotalAmt; TempCurrencyTotalBuffer."Total Amount") { AutoFormatExpression = TempCurrencyTotalBuffer."Currency Code"; AutoFormatType = 1; } column(CurrTotalBufferCurrCode; TempCurrencyTotalBuffer."Currency Code") { } trigger OnAfterGetRecord() begin if Number = 1 then OK := TempCurrencyTotalBuffer.Find('-') else OK := TempCurrencyTotalBuffer.Next() <> 0; if not OK then CurrReport.Break(); TempCurrencyTotalBuffer2.UpdateTotal( TempCurrencyTotalBuffer."Currency Code", TempCurrencyTotalBuffer."Total Amount", 0, Counter1); end; trigger OnPostDataItem() begin TempCurrencyTotalBuffer.DeleteAll(); end; trigger OnPreDataItem() begin if not ShowEntriesWithZeroBalance then TempCurrencyTotalBuffer.SetFilter("Total Amount", '<>0'); end; } trigger OnAfterGetRecord() var ShouldSkipVendor: Boolean; begin Clear(VendorLedgerEntriesIncluded); MaxDate := GetRangeMax("Date Filter"); if MinDate = 0D then MinDate := GetRangeMin("Date Filter"); SetRange("Date Filter", 0D, MaxDate); CalcFields("Net Change (LCY)", "Net Change"); ShouldSkipVendor := ("Net Change (LCY)" = 0) and ("Net Change" = 0) and (not ShowEntriesWithZeroBalance); OnVendorOnAfterGetRecordOnAfterCalcShouldSkipVendor(Vendor, ShouldSkipVendor); if ShouldSkipVendor then CurrReport.Skip(); end; } dataitem(Integer3; "Integer") { DataItemTableView = sorting(Number) where(Number = filter(1 ..)); column(CurrTotalBuffer2CurrCode; TempCurrencyTotalBuffer2."Currency Code") { } column(CurrTotalBuffer2TotalAmt; TempCurrencyTotalBuffer2."Total Amount") { AutoFormatExpression = TempCurrencyTotalBuffer2."Currency Code"; AutoFormatType = 1; } column(TotalCaption; TotalCaptionLbl) { } trigger OnAfterGetRecord() begin if Number = 1 then OK := TempCurrencyTotalBuffer2.Find('-') else OK := TempCurrencyTotalBuffer2.Next() <> 0; if not OK then CurrReport.Break(); end; trigger OnPostDataItem() begin TempCurrencyTotalBuffer2.DeleteAll(); end; trigger OnPreDataItem() begin TempCurrencyTotalBuffer2.SetFilter("Total Amount", '<>0'); end; } } requestpage { AboutTitle = 'About Vendor - Balance to Date'; AboutText = 'Track your outstanding payables and manage your vendor relationships'; SaveValues = true; layout { area(content) { group(Options) { Caption = 'Options'; field(ShowAmountsInLCY; PrintAmountInLCY) { ApplicationArea = Basic, Suite; Caption = 'Show Amounts in LCY'; ToolTip = 'Specifies if amounts in the report are displayed in LCY. If you leave the check box blank, amounts are shown in foreign currencies.'; } field(PrintOnePrPage; PrintOnePrPage) { ApplicationArea = Basic, Suite; Caption = 'New Page per Vendor'; ToolTip = 'Specifies that you want to print each vendor balance on a separate page, if you have chosen two or more vendors to be included in the report.'; } field(PrintUnappliedEntries; PrintUnappliedEntries) { ApplicationArea = Basic, Suite; Caption = 'Include Unapplied Entries'; ToolTip = 'Specifies if the report includes entries that have been applied and later unapplied using the Unapply action. By default, the report does not show such entries.'; } field(ShowEntriesWithZeroBalance; ShowEntriesWithZeroBalance) { ApplicationArea = Basic, Suite; Caption = 'Show Entries with Zero Balance'; ToolTip = 'Specifies if the report must include vendor ledger entries with a balance of 0. By default, the report only includes vendor ledger entries with a positive or negative balance.'; } field(UseExternalDocNo; UseExternalDocNo) { ApplicationArea = Basic, Suite; Caption = 'Use External Document No.'; ToolTip = 'Specifies if you want to print the vendor''''s document numbers, such as the invoice number, for all transactions. Clear this check box to print only internal document numbers.'; } } } } actions { } } rendering { layout(RDLCLayout) { Type = RDLC; LayoutFile = './Purchases/Reports/VendorBalancetoDate.rdlc'; Summary = 'Report layout made in the legacy RDLC format. Use an RDLC editor to modify the layout.'; } } labels { } trigger OnPreReport() var FormatDocument: Codeunit "Format Document"; begin VendFilter := FormatDocument.GetRecordFiltersWithCaptions(Vendor); if UseExternalDocNo then DocNoCaption := VendLedgEntry3.FieldCaption("External Document No.") else DocNoCaption := VendLedgEntry3.FieldCaption("Document No."); end; var DtldVendLedgEntry: Record "Detailed Vendor Ledg. Entry"; TempCurrencyTotalBuffer: Record "Currency Total Buffer" temporary; TempCurrencyTotalBuffer2: Record "Currency Total Buffer" temporary; VendorLedgerEntriesIncluded: Dictionary of [Integer, Boolean]; AutoFormat: Codeunit "Auto Format"; PrintAmountInLCY: Boolean; PrintOnePrPage: Boolean; VendFilter: Text; MaxDate: Date; MinDate: Date; OriginalAmt: Decimal; Amt: Decimal; RemainingAmt: Decimal; Counter1: Integer; DtldVendtLedgEntryNum: Integer; OK: Boolean; CurrencyCode: Code[10]; PrintUnappliedEntries: Boolean; ShowEntriesWithZeroBalance: Boolean; UseExternalDocNo: Boolean; VendLedgDocumentNo: Code[35]; DtldVendLedgDocumentNo: Code[35]; DocNoCaption: Text;#pragma warning disable AA0074#pragma warning disable AA0470 Text000: Label 'Balance on %1';#pragma warning restore AA0470#pragma warning restore AA0074 VendorBalancetoDateCptnLbl: Label 'Vendor - Balance to Date'; PageNoCaptionLbl: Label 'Page'; AllamountsareinLCYCaptionLbl: Label 'All amounts are in LCY.'; PostingDateCptionLbl: Label 'Posting Date'; OriginalAmtCaptionLbl: Label 'Amount'; TotalCaptionLbl: Label 'Total'; procedure InitializeRequest(NewPrintAmountInLCY: Boolean; NewPrintOnePrPage: Boolean; NewPrintUnappliedEntries: Boolean) begin PrintAmountInLCY := NewPrintAmountInLCY; PrintOnePrPage := NewPrintOnePrPage; PrintUnappliedEntries := NewPrintUnappliedEntries; end; local procedure FilterDetailedVendLedgerEntry(var DetailedVendorLedgEntry: Record "Detailed Vendor Ledg. Entry"; DateFilter: Text) begin DetailedVendorLedgEntry.SetCurrentKey("Vendor No.", "Posting Date", "Entry Type"); DetailedVendorLedgEntry.SetRange("Vendor No.", Vendor."No."); DetailedVendorLedgEntry.SetFilter("Posting Date", DateFilter); DetailedVendorLedgEntry.SetRange("Entry Type", DtldVendLedgEntry."Entry Type"::Application); end; local procedure FilterVendorLedgerEntry(var VendorLedgerEntry: Record "Vendor Ledger Entry") begin VendorLedgerEntry.SetCurrentKey("Vendor No.", Open); VendorLedgerEntry.SetRange("Vendor No.", Vendor."No."); if not ShowEntriesWithZeroBalance then VendorLedgerEntry.SetRange(Open, true); VendorLedgerEntry.SetRange("Posting Date", 0D, MaxDate); end; local procedure AddVendorDimensionFilter(var VendorLedgerEntry: Record "Vendor Ledger Entry") begin if Vendor.GetFilter("Global Dimension 1 Filter") <> '' then VendorLedgerEntry.SetFilter("Global Dimension 1 Code", Vendor.GetFilter("Global Dimension 1 Filter")); if Vendor.GetFilter("Global Dimension 2 Filter") <> '' then VendorLedgerEntry.SetFilter("Global Dimension 2 Code", Vendor.GetFilter("Global Dimension 2 Filter")); if Vendor.GetFilter("Currency Filter") <> '' then VendorLedgerEntry.SetFilter("Currency Code", Vendor.GetFilter("Currency Filter")); end; local procedure CalcTotalVendorAmount() var VendorLedgerEntry: Record "Vendor Ledger Entry"; TempVendorLedgerEntry: Record "Vendor Ledger Entry" temporary; DetailedVendorLedgEntry: Record "Detailed Vendor Ledg. Entry"; begin FilterDetailedVendLedgerEntry(DetailedVendorLedgEntry, ''); if DetailedVendorLedgEntry.FindSet() then repeat VendorLedgerEntry.Get(DetailedVendorLedgEntry."Vendor Ledger Entry No."); if not TempVendorLedgerEntry.Get(VendorLedgerEntry."Entry No.") then if CheckVendLedgerEntryIncludedWithCache(VendorLedgerEntry."Entry No.") then begin TempVendorLedgerEntry := VendorLedgerEntry; TempVendorLedgerEntry.Insert(); end; until DetailedVendorLedgEntry.Next() = 0; FilterVendorLedgerEntry(VendorLedgerEntry); if VendorLedgerEntry.FindSet() then repeat if not TempVendorLedgerEntry.Get(VendorLedgerEntry."Entry No.") then if CheckVendLedgerEntryIncludedWithCache(VendorLedgerEntry."Entry No.") then begin TempVendorLedgerEntry := VendorLedgerEntry; TempVendorLedgerEntry.Insert(); end; until VendorLedgerEntry.Next() = 0; TempVendorLedgerEntry.SetCurrentKey("Entry No."); TempVendorLedgerEntry.SetRange("Date Filter", 0D, MaxDate); AddVendorDimensionFilter(TempVendorLedgerEntry); if TempVendorLedgerEntry.FindSet() then repeat CalcVendorRemainingAmount(TempVendorLedgerEntry); if (RemainingAmt <> 0) or ShowEntriesWithZeroBalance then TempCurrencyTotalBuffer.UpdateTotal( CurrencyCode, RemainingAmt, 0, Counter1); OnCalcTotalVendorAmountOnAfterUpdateCurrencyTotalBuffer(TempVendorLedgerEntry, TempCurrencyTotalBuffer, Counter1, RemainingAmt, ShowEntriesWithZeroBalance) until TempVendorLedgerEntry.Next() = 0; end; local procedure CalcVendorRemainingAmount(var TempVendorLedgerEntry: Record "Vendor Ledger Entry" temporary) begin if PrintAmountInLCY then begin TempVendorLedgerEntry.CalcFields("Remaining Amt. (LCY)"); RemainingAmt := TempVendorLedgerEntry."Remaining Amt. (LCY)"; CurrencyCode := ''; end else begin TempVendorLedgerEntry.CalcFields("Remaining Amount"); RemainingAmt := TempVendorLedgerEntry."Remaining Amount"; CurrencyCode := TempVendorLedgerEntry."Currency Code"; end; OnAfterCalcVendorRemainingAmount(TempVendorLedgerEntry, PrintAmountInLCY, RemainingAmt, CurrencyCode); end; local procedure CheckVendLedgerEntryIncludedWithCache(EntryNo: Integer): Boolean var IsIncluded: Boolean; begin if VendorLedgerEntriesIncluded.ContainsKey(EntryNo) then begin VendorLedgerEntriesIncluded.Get(EntryNo, IsIncluded); exit(IsIncluded); end; IsIncluded := CheckVendEntryIncluded(EntryNo); VendorLedgerEntriesIncluded.Add(EntryNo, IsIncluded); exit(IsIncluded); end; local procedure CheckVendEntryIncluded(EntryNo: Integer): Boolean var VendorLedgerEntry: Record "Vendor Ledger Entry"; ClosingVendorLedgerEntry: Record "Vendor Ledger Entry"; begin if VendorLedgerEntry.Get(EntryNo) and (VendorLedgerEntry."Posting Date" <= MaxDate) then begin VendorLedgerEntry.SetRange("Date Filter", 0D, MaxDate); VendorLedgerEntry.CalcFields("Remaining Amount", "Original Amount", Amount); if VendorLedgerEntry."Remaining Amount" <> 0 then exit(true); // if "Show Entries with Zero Balance" is checked, show only closed entries that were closed within the report date filter if ShowEntriesWithZeroBalance then begin if ClosingVendorLedgerEntry.Get(VendorLedgerEntry."Closed by Entry No.") then exit((ClosingVendorLedgerEntry."Posting Date" >= MinDate) and (ClosingVendorLedgerEntry."Posting Date" <= MaxDate)); if (VendorLedgerEntry.Amount = 0) and (VendorLedgerEntry."Original Amount" = 0) and not VendorLedgerEntry.Open and (VendorLedgerEntry."Posting Date" >= MinDate) and (VendorLedgerEntry."Posting Date" <= MaxDate) then exit(true); end; if PrintUnappliedEntries then exit(CheckUnappliedEntryExists(EntryNo)); end; exit(false); end; local procedure CheckUnappliedEntryExists(EntryNo: Integer): Boolean var DetailedVendorLedgEntry: Record "Detailed Vendor Ledg. Entry"; begin DetailedVendorLedgEntry.SetCurrentKey("Vendor Ledger Entry No.", "Entry Type", "Posting Date"); DetailedVendorLedgEntry.SetRange("Vendor Ledger Entry No.", EntryNo); DetailedVendorLedgEntry.SetRange("Entry Type", DetailedVendorLedgEntry."Entry Type"::Application); DetailedVendorLedgEntry.SetFilter("Posting Date", '>%1', MaxDate); DetailedVendorLedgEntry.SetRange(Unapplied, true); exit(not DetailedVendorLedgEntry.IsEmpty); end; local procedure GetAppliedEntryExternalDocNo(AppliedEntryNo: Integer): Code[35] var VendorLedgerEntry: Record "Vendor Ledger Entry"; begin if VendorLedgerEntry.Get(AppliedEntryNo) then exit(VendorLedgerEntry."External Document No."); exit(''); end; [IntegrationEvent(false, false)] local procedure OnAfterCalcVendorRemainingAmount(var TempVendorLedgerEntry: Record "Vendor Ledger Entry" temporary; PrintAmountInLCY: Boolean; var RemainingAmt: Decimal; var CurrencyCode: Code[10]) begin end; [IntegrationEvent(false, false)] local procedure OnAfterVendLedgEntry3OnAfterGetRecord(VendorLedgerEntry: Record "Vendor Ledger Entry"; PrintAmountInLCY: Boolean; var OriginalAmt: Decimal; var RemainingAmt: Decimal; var CurrencyCode: Code[10]) begin end; [IntegrationEvent(true, false)] local procedure OnVendorOnAfterGetRecordOnAfterCalcShouldSkipVendor(var Vendor: Record Vendor; var ShouldSkipVendor: Boolean) begin end; [IntegrationEvent(false, false)] local procedure OnCalcTotalVendorAmountOnAfterUpdateCurrencyTotalBuffer(var TempVendorLedgerEntry: Record "Vendor Ledger Entry" temporary; var TempCurrencyTotalBuffer: Record "Currency Total Buffer" temporary; Counter1: Integer; RemainingAmt: Decimal; ShowEntriesWithZeroBalance: Boolean) begin end;}