Skip to main content

Notifications

Announcements

No record found.

Business Central forum
Answered

Where to find technical documentation about Detailed Customer Ledger Entries?

Posted on by 15

Hi,

We're looking for a way to link sales invoices to payments.

I've found this thread which has been helpful: https://community.dynamics.com/business/f/dynamics-365-business-central-forum/409048/power-bi-how-to-get-the-sales-invoice-s-to-which-a-payment-was-applied

It explains that the Detailed Customer Ledger Entries table can be used for this purpose.

However, it also explains that logic is needed to link the entries, and that there can be edge cases. To understand this better, I'd like to look at some (ideally, in depth) documentation for the Detailed Customer Ledger Entries table and certain columns (e.g. "Applied Cust. Ledger Entry No."). Does something like that exist? I've been looking for it but have been unsuccessful so far. Perhaps paid documentation?

Any help would be much appreciated.

Categories:
  • Nick22 Profile Picture
    Nick22 15 on at
    RE: Where to find technical documentation about Detailed Customer Ledger Entries?

    It's good to know that I'm not missing anything obvious.

    Thank you both for your suggestions, these are good alternatives.

  • Verified answer
    YUN ZHU Profile Picture
    YUN ZHU 70,124 Super User 2024 Season 2 on at
    RE: Where to find technical documentation about Detailed Customer Ledger Entries?

    Hi, I have only seen the document created by the partner below, I don't know if they can give you some help.

    https://dynamicsdocs.com/nav/2016/w1/table/detailed-cust-ledg-entry

    Thanks.

    ZHU

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,744 Moderator on at
    RE: Where to find technical documentation about Detailed Customer Ledger Entries?

    I have never seen any documentation like that.

    If you look at how the table is defined in the base application a lot of explanation can be read out of that.

    Use a sandbox to  post one invoice and post a payment that you apply to that invoice. Then you can look at the detailed CLE that is created from those postings and you will be able to figure out most of it. And then you can always ask here if there are any specific fields you need assistance with.

    table 379 "Detailed Cust. Ledg. Entry"
    {
        Caption = 'Detailed Cust. Ledg. Entry';
        DataCaptionFields = "Customer No.";
        DrillDownPageID = "Detailed Cust. Ledg. Entries";
        LookupPageID = "Detailed Cust. Ledg. Entries";
        Permissions = TableData "Detailed Cust. Ledg. Entry" = m;
    
        fields
        {
            field(1; "Entry No."; Integer)
            {
                Caption = 'Entry No.';
            }
            field(2; "Cust. Ledger Entry No."; Integer)
            {
                Caption = 'Cust. Ledger Entry No.';
                TableRelation = "Cust. Ledger Entry";
            }
            field(3; "Entry Type"; Enum "Detailed CV Ledger Entry Type")
            {
                Caption = 'Entry Type';
            }
            field(4; "Posting Date"; Date)
            {
                Caption = 'Posting Date';
            }
            field(5; "Document Type"; Enum "Gen. Journal Document Type")
            {
                Caption = 'Document Type';
            }
            field(6; "Document No."; Code[20])
            {
                Caption = 'Document No.';
            }
            field(7; Amount; Decimal)
            {
                AutoFormatExpression = "Currency Code";
                AutoFormatType = 1;
                Caption = 'Amount';
            }
            field(8; "Amount (LCY)"; Decimal)
            {
                AutoFormatType = 1;
                Caption = 'Amount (LCY)';
            }
            field(9; "Customer No."; Code[20])
            {
                Caption = 'Customer No.';
                TableRelation = Customer;
            }
            field(10; "Currency Code"; Code[10])
            {
                Caption = 'Currency Code';
                TableRelation = Currency;
            }
            field(11; "User ID"; Code[50])
            {
                Caption = 'User ID';
                DataClassification = EndUserIdentifiableInformation;
                TableRelation = User."User Name";
                //This property is currently not supported
                //TestTableRelation = false;
                ValidateTableRelation = false;
    
                trigger OnValidate()
                var
                    UserSelection: Codeunit "User Selection";
                begin
                    UserSelection.ValidateUserName("User ID");
                end;
            }
            field(12; "Source Code"; Code[10])
            {
                Caption = 'Source Code';
                TableRelation = "Source Code";
            }
            field(13; "Transaction No."; Integer)
            {
                Caption = 'Transaction No.';
            }
            field(14; "Journal Batch Name"; Code[10])
            {
                Caption = 'Journal Batch Name';
                //This property is currently not supported
                //TestTableRelation = false;
            }
            field(15; "Reason Code"; Code[10])
            {
                Caption = 'Reason Code';
                TableRelation = "Reason Code";
            }
            field(16; "Debit Amount"; Decimal)
            {
                AutoFormatExpression = "Currency Code";
                AutoFormatType = 1;
                BlankZero = true;
                Caption = 'Debit Amount';
            }
            field(17; "Credit Amount"; Decimal)
            {
                AutoFormatExpression = "Currency Code";
                AutoFormatType = 1;
                BlankZero = true;
                Caption = 'Credit Amount';
            }
            field(18; "Debit Amount (LCY)"; Decimal)
            {
                AutoFormatType = 1;
                BlankZero = true;
                Caption = 'Debit Amount (LCY)';
            }
            field(19; "Credit Amount (LCY)"; Decimal)
            {
                AutoFormatType = 1;
                BlankZero = true;
                Caption = 'Credit Amount (LCY)';
            }
            field(20; "Initial Entry Due Date"; Date)
            {
                Caption = 'Initial Entry Due Date';
            }
            field(21; "Initial Entry Global Dim. 1"; Code[20])
            {
                Caption = 'Initial Entry Global Dim. 1';
                TableRelation = "Dimension Value".Code WHERE("Global Dimension No." = CONST(1));
            }
            field(22; "Initial Entry Global Dim. 2"; Code[20])
            {
                Caption = 'Initial Entry Global Dim. 2';
                TableRelation = "Dimension Value".Code WHERE("Global Dimension No." = CONST(2));
            }
            field(24; "Gen. Bus. Posting Group"; Code[20])
            {
                Caption = 'Gen. Bus. Posting Group';
                TableRelation = "Gen. Business Posting Group";
            }
            field(25; "Gen. Prod. Posting Group"; Code[20])
            {
                Caption = 'Gen. Prod. Posting Group';
                TableRelation = "Gen. Product Posting Group";
            }
            field(29; "Use Tax"; Boolean)
            {
                Caption = 'Use Tax';
            }
            field(30; "VAT Bus. Posting Group"; Code[20])
            {
                Caption = 'VAT Bus. Posting Group';
                TableRelation = "VAT Business Posting Group";
            }
            field(31; "VAT Prod. Posting Group"; Code[20])
            {
                Caption = 'VAT Prod. Posting Group';
                TableRelation = "VAT Product Posting Group";
            }
            field(35; "Initial Document Type"; Enum "Gen. Journal Document Type")
            {
                Caption = 'Initial Document Type';
            }
            field(36; "Applied Cust. Ledger Entry No."; Integer)
            {
                Caption = 'Applied Cust. Ledger Entry No.';
            }
            field(37; Unapplied; Boolean)
            {
                Caption = 'Unapplied';
            }
            field(38; "Unapplied by Entry No."; Integer)
            {
                Caption = 'Unapplied by Entry No.';
                TableRelation = "Detailed Cust. Ledg. Entry";
            }
            field(39; "Remaining Pmt. Disc. Possible"; Decimal)
            {
                AutoFormatExpression = "Currency Code";
                AutoFormatType = 1;
                Caption = 'Remaining Pmt. Disc. Possible';
            }
            field(40; "Max. Payment Tolerance"; Decimal)
            {
                AutoFormatExpression = "Currency Code";
                AutoFormatType = 1;
                Caption = 'Max. Payment Tolerance';
            }
            field(41; "Tax Jurisdiction Code"; Code[10])
            {
                Caption = 'Tax Jurisdiction Code';
                Editable = false;
                TableRelation = "Tax Jurisdiction";
            }
            field(42; "Application No."; Integer)
            {
                Caption = 'Application No.';
                Editable = false;
            }
            field(43; "Ledger Entry Amount"; Boolean)
            {
                Caption = 'Ledger Entry Amount';
                Editable = false;
            }
            field(44; "Posting Group"; Code[20])
            {
                Caption = 'Customer Posting Group';
                Editable = false;
                TableRelation = "Customer Posting Group";
            }
            field(45; "Exch. Rate Adjmt. Reg. No."; Integer)
            {
                Caption = 'Exch. Rate Adjmt. Reg. No.';
                Editable = false;
                TableRelation = "Exch. Rate Adjmt. Reg.";
            }
        }
    
        keys
        {
            key(Key1; "Entry No.")
            {
                Clustered = true;
            }
            key(Key2; "Cust. Ledger Entry No.", "Posting Date")
            {
            }
            key(Key3; "Cust. Ledger Entry No.", "Entry Type", "Posting Date")
            {
            }
            key(Key4; "Ledger Entry Amount", "Cust. Ledger Entry No.", "Posting Date")
            {
                MaintainSQLIndex = false;
                MaintainSiftIndex = false;
                SumIndexFields = Amount, "Amount (LCY)", "Debit Amount", "Debit Amount (LCY)", "Credit Amount", "Credit Amount (LCY)";
                ObsoleteState = Pending;
                ObsoleteReason = 'Replaced with "Key17" for better peformance.';
                ObsoleteTag = '18.0';
            }
            key(Key5; "Initial Document Type", "Entry Type", "Customer No.", "Currency Code", "Initial Entry Global Dim. 1", "Initial Entry Global Dim. 2", "Posting Date")
            {
                SumIndexFields = Amount, "Amount (LCY)";
            }
            key(Key6; "Customer No.", "Currency Code", "Initial Entry Global Dim. 1", "Initial Entry Global Dim. 2", "Initial Entry Due Date", "Posting Date")
            {
                SumIndexFields = Amount, "Amount (LCY)";
            }
            key(Key7; "Document No.", "Document Type", "Posting Date")
            {
            }
            key(Key8; "Applied Cust. Ledger Entry No.", "Entry Type")
            {
            }
            key(Key9; "Transaction No.", "Customer No.", "Entry Type")
            {
            }
            key(Key10; "Application No.", "Customer No.", "Entry Type")
            {
            }
            key(Key11; "Customer No.", "Entry Type", "Posting Date", "Initial Document Type")
            {
                SumIndexFields = Amount, "Amount (LCY)";
            }
            key(Key12; "Document Type")
            {
                SumIndexFields = "Amount (LCY)";
            }
            key(Key13; "Initial Document Type", "Initial Entry Due Date")
            {
                SumIndexFields = "Amount (LCY)";
            }
            key(Key14; "Customer No.", "Initial Entry Due Date")
            {
                SumIndexFields = Amount, "Amount (LCY)";
            }
            key(Key17; "Cust. Ledger Entry No.", "Posting Date", "Ledger Entry Amount")
            {
                MaintainSQLIndex = false;
                SumIndexFields = Amount, "Amount (LCY)", "Debit Amount", "Debit Amount (LCY)", "Credit Amount", "Credit Amount (LCY)";
            }
        }
    
        fieldgroups
        {
            fieldgroup(DropDown; "Entry No.", "Cust. Ledger Entry No.", "Customer No.", "Posting Date", "Document Type", "Document No.")
            {
            }
        }
    
        trigger OnInsert()
        begin
            SetLedgerEntryAmount;
        end;
    
        procedure GetLastEntryNo(): Integer;
        var
            FindRecordManagement: Codeunit "Find Record Management";
        begin
            exit(FindRecordManagement.GetLastEntryIntFieldValue(Rec, FieldNo("Entry No.")))
        end;
    
        procedure UpdateDebitCredit(Correction: Boolean)
        begin
            if ((Amount > 0) or ("Amount (LCY)" > 0)) and not Correction or
               ((Amount < 0) or ("Amount (LCY)" < 0)) and Correction
            then begin
                "Debit Amount" := Amount;
                "Credit Amount" := 0;
                "Debit Amount (LCY)" := "Amount (LCY)";
                "Credit Amount (LCY)" := 0;
            end else begin
                "Debit Amount" := 0;
                "Credit Amount" := -Amount;
                "Debit Amount (LCY)" := 0;
                "Credit Amount (LCY)" := -"Amount (LCY)";
            end;
        end;
    
        procedure SetZeroTransNo(TransactionNo: Integer)
        var
            DtldCustLedgEntry: Record "Detailed Cust. Ledg. Entry";
            ApplicationNo: Integer;
        begin
            DtldCustLedgEntry.SetCurrentKey("Transaction No.");
            DtldCustLedgEntry.SetRange("Transaction No.", TransactionNo);
            if DtldCustLedgEntry.FindSet(true) then begin
                ApplicationNo := DtldCustLedgEntry."Entry No.";
                repeat
                    DtldCustLedgEntry."Transaction No." := 0;
                    DtldCustLedgEntry."Application No." := ApplicationNo;
                    DtldCustLedgEntry.Modify();
                until DtldCustLedgEntry.Next() = 0;
            end;
        end;
    
        local procedure SetLedgerEntryAmount()
        begin
            "Ledger Entry Amount" :=
              not (("Entry Type" = "Entry Type"::Application) or ("Entry Type" = "Entry Type"::"Appln. Rounding"));
        end;
    
        procedure GetUnrealizedGainLossAmount(EntryNo: Integer): Decimal
        begin
            SetCurrentKey("Cust. Ledger Entry No.", "Entry Type");
            SetRange("Cust. Ledger Entry No.", EntryNo);
            SetRange("Entry Type", "Entry Type"::"Unrealized Loss", "Entry Type"::"Unrealized Gain");
            CalcSums("Amount (LCY)");
            exit("Amount (LCY)");
        end;
    }
    
    

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,564 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,651 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans