Report 321 Vendor - Balance to Date

Base Application, Microsoft.Purchases.Reports

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