late payer report

lanemark · 2008-03-07

Does anyone know how I can produce a report to show customers who pay later than their terms state on their customer card.
We wish to create a blacklist of late payers, so we can maybe change their terms of sale.

alan

fab_manzella · 2008-03-07

You have to process all Customer ledger entry (Document Type: Invoice or Credit Memo) and, for each of them, compare the Orignal Due Date with the Actual Payment Date.

Create a Report wit Customer as DataItem.

On Customer - OnAfterGetRecord put code like:

AverageDelay := 0;
TempVarDelay := 0;
TempVarDelay := 0;

CustLedgEntry2.RESET;
CustLedgEntry.SETRANGE("Customer No.","No.");
CLEAR(CustLedgEntry2);
// YOU CAN FILTER ON DOC TYPE (Invoice, Credit Memo)
IF CustLedgEntry.FIND('-') THEN
REPEAT
CLEAR(CustLedgEntry2);
CustLedgEntry2.RESET;
IF NOT CustLedgEntry.Open THEN BEGIN
IF CustLedgEntry."Closed by Entry No." <> 0 THEN BEGIN
CustLedgEntry2.GET(CustLedgEntry."Closed by Entry No.");
CustLedgEntry2.MARK(TRUE);
END;
CustLedgEntry2.SETCURRENTKEY("Closed by Entry No.");
CustLedgEntry2.SETRANGE("Closed by Entry No.",CustLedgEntry."Entry No.");
IF CustLedgEntry2.FIND('-') THEN
REPEAT
CustLedgEntry2.MARK(TRUE);
UNTIL CustLedgEntry2.NEXT = 0;
CustLedgEntry2.SETCURRENTKEY("Entry No.");
CustLedgEntry2.SETRANGE("Closed by Entry No.");
CustLedgEntry2.MARKEDONLY(TRUE);
IF CustLedgEntry2.FIND('-') THEN REPEAT
IF (NOT CustLedgEntry2.Open) AND (CustLedgEntry2."Document Type" = CustLedgEntry2."Document Type"::Payment) THEN BEGIN
TempVarDelay += (CustLedgEntry2."Due Date" - CustLedgEntry."Due Date");
CounterMov += 1;
END;
UNTIL CustLedgEntry2.NEXT = 0;
END

UNTIL CustLedgEntry.NEXT = 0;

AverageDelay:= TempVarDelay /TempVarDelay

ara3n · 2008-03-07

Hello.
If you are on customer card and you click on Customer button->Entry Statistics

On No. of Documents Tab you'll see Avg. Collection Period (Days).

All the logic is in there and it will give you the average based on period

Here is report that will give you what you are looking for. You simply specify the date range and the report will give you the avg. collection period, it also prints payment terms so you can compare them and make changes to your customers.

OBJECT Report 50053 Credit Evaluation
{
  OBJECT-PROPERTIES
  {
    Date=03/12/08;
    Time=[ 1:34:47 PM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    LeftMargin=500;
    OnPreReport=BEGIN
                  AvgMonthSales := 1;
                  Filters :=  Customer.GETFILTERS;
                  FromDate := Customer.GETRANGEMIN("Date Filter");
                  ToDate := Customer.GETRANGEMAX("Date Filter");
                END;

  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table18;
        DataItemTableView=SORTING(No.);
        OnAfterGetRecord=BEGIN

                           Date.SETRANGE("Period Type",Date."Period Type"::Month);
                           Date.SETRANGE("Period Start",FromDate,ToDate);
                           AvgMonthSales := Date.COUNT;
                           IF AvgMonthSales = 0 THEN
                             AvgMonthSales := 1;
                         END;

        ReqFilterFields=No.,Date Filter;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=19800;
            SectionHeight=1692;
          }
          CONTROLS
          {
            { 1000000001;Label  ;0    ;0    ;7500 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=[ENU=Customer;
                                                                    ESM=Cliente;
                                                                    FRC=Client;
                                                                    ENC=Customer] }
            { 1000000002;TextBox;15000;0    ;3150 ;423  ;HorzAlign=Right;
                                                         SourceExpr=FORMAT(TODAY,0,4) }
            { 1000000003;TextBox;0    ;423  ;7500 ;423  ;SourceExpr=COMPANYNAME }
            { 1000000004;TextBox;17700;423  ;450  ;423  ;CaptionML=ENU=Page;
                                                         SourceExpr=CurrReport.PAGENO }
            { 1000000005;Label  ;16950;423  ;750  ;423  ;ParentControl=1000000004 }
            { 1000000006;TextBox;15900;846  ;2250 ;423  ;HorzAlign=Right;
                                                         SourceExpr=USERID }
            { 1000000000;TextBox;0    ;1269 ;12000;423  ;SourceExpr=Filters }
          }
           }
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=19800;
            SectionHeight=1269;
          }
          CONTROLS
          {
            { 1000000009;Label  ;0    ;0    ;1500 ;846  ;ParentControl=1000000008;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000012;Label  ;1650 ;0    ;4500 ;846  ;ParentControl=1000000011;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000015;Label  ;6300 ;0    ;1800 ;846  ;ParentControl=1000000014;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000018;Label  ;8250 ;0    ;1800 ;846  ;ParentControl=1000000017;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000021;Label  ;12150;0    ;1500 ;846  ;ParentControl=1000000020;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000007;Label  ;10200;0    ;1800 ;846  ;VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Avg Monthly Sales }
            { 1000000010;Label  ;13800;0    ;2100 ;1269 ;VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Avg Collection Days }
            { 1000000013;Label  ;15900;0    ;1800 ;846  ;VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=New Limit }
            { 1000000016;Label  ;18000;0    ;1800 ;846  ;VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Signature Required }
          }
           }
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=19800;
            SectionHeight=423;
          }
          CONTROLS
          {
            { 1000000008;TextBox;0    ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         SourceExpr="No." }
            { 1000000011;TextBox;1650 ;0    ;4500 ;423  ;HorzAlign=Left;
                                                         SourceExpr=Name }
            { 1000000014;TextBox;6300 ;0    ;1800 ;423  ;HorzAlign=Right;
                                                         SourceExpr="Sales (LCY)" }
            { 1000000017;TextBox;8250 ;0    ;1800 ;423  ;HorzAlign=Right;
                                                         SourceExpr="Credit Limit (LCY)" }
            { 1000000020;TextBox;12150;0    ;1500 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Payment Terms Code" }
            { 1000000019;TextBox;10200;0    ;1800 ;423  ;HorzAlign=Right;
                                                         SourceExpr="Sales (LCY)"/AvgMonthSales }
            { 1000000022;TextBox;13950;0    ;1800 ;423  ;HorzAlign=Right;
                                                         SourceExpr=CalcValue }
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  REQUESTPAGE
  {
    PROPERTIES
    {
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      AvgMonthSales@1000000000 : Decimal;
      FromDate@1000000001 : Date;
      ToDate@1000000002 : Date;
      AvgColDays@1000000003 : Decimal;
      Filters@1000000004 : Text[1000];
      Date@1000000005 : Record 2000000007;

    PROCEDURE CalcValue@1000000003() : Decimal;
    VAR
      CustLedgEntry2@1000000015 : Record 21;
      CustLedgEntry3@1000000014 : Record 21;
      DtldCustLedgEntry2@1000000013 : Record 379;
      DateFilterCalc@1000000012 : Codeunit 358;
      CustLedgEntry@1000000011 : ARRAY [6] OF Record 21;
      CustDateFilter@1000000010 : ARRAY [3] OF Text[30];
      CustDateName@1000000009 : ARRAY [3] OF Text[30];
      TotalRemainAmountLCY@1000000008 : ARRAY [6] OF Decimal;
      NoOfDoc@1000000007 : ARRAY [3,6] OF Integer;
      AvgDaysToPay@1000000006 : ARRAY [3] OF Decimal;
      DaysToPay@1000000005 : Decimal;
      NoOfInv@1000000004 : Integer;
      HighestBalanceLCY@1000000003 : ARRAY [3] OF Decimal;
      CustBalanceLCY@1000000002 : Decimal;
      i@1000000001 : Integer;
      j@1000000000 : Integer;
    BEGIN
      WITH Customer DO BEGIN
      FOR j := 1 TO 6 DO BEGIN
        CustLedgEntry[j].SETCURRENTKEY("Document Type","Customer No.","Posting Date");
        CustLedgEntry[j].SETRANGE("Document Type",j); // Payment,Invoice,Credit Memo,Finance Charge Memo,Reminder,Refund
        CustLedgEntry[j].SETRANGE("Customer No.","No.");
        IF CustLedgEntry[j].FIND('+') THEN
          CustLedgEntry[j].CALCFIELDS(Amount,"Remaining Amount");
      END;

      CustLedgEntry2.SETCURRENTKEY("Customer No.",Open);
      CustLedgEntry2.SETRANGE("Customer No.","No.");
      CustLedgEntry2.SETRANGE(Open,TRUE);
      IF CustLedgEntry2.FIND('+') THEN
        REPEAT
          j := CustLedgEntry2."Document Type";
          IF j > 0 THEN BEGIN
            CustLedgEntry2.CALCFIELDS("Remaining Amt. (LCY)");
            TotalRemainAmountLCY[j] := TotalRemainAmountLCY[j] + CustLedgEntry2."Remaining Amt. (LCY)";
          END;
        UNTIL CustLedgEntry2.NEXT(-1) = 0;

      //DateFilterCalc.CreateAccountingPeriodFilter(CustDateFilter[1],CustDateName[1],WORKDATE,0);
      CustDateFilter[1] := FORMAT(FromDate) + '..'+FORMAT(ToDate);

      //DateFilterCalc.CreateFiscalYearFilter(CustDateFilter[2],CustDateName[2],WORKDATE,0);
      //DateFilterCalc.CreateFiscalYearFilter(CustDateFilter[3],CustDateName[3],WORKDATE,-1);

      i := 1;
      CustLedgEntry2.RESET;
      CustLedgEntry2.SETCURRENTKEY("Customer No.","Posting Date");
      CustLedgEntry2.SETRANGE("Customer No.","No.");
      CustLedgEntry2.SETFILTER("Posting Date",CustDateFilter[i]);
      CustLedgEntry2.SETRANGE("Posting Date",0D,CustLedgEntry2.GETRANGEMAX("Posting Date"));
      DtldCustLedgEntry2.SETCURRENTKEY("Customer No.","Posting Date");
      CustLedgEntry2.COPYFILTER("Customer No.",DtldCustLedgEntry2."Customer No.");
      CustLedgEntry2.COPYFILTER("Posting Date",DtldCustLedgEntry2."Posting Date");
      DtldCustLedgEntry2.CALCSUMS("Amount (LCY)");
      CustBalanceLCY := DtldCustLedgEntry2."Amount (LCY)";
      HighestBalanceLCY[i] := CustBalanceLCY;
      DaysToPay := 0;
      NoOfInv := 0;

      CustLedgEntry2.SETFILTER("Posting Date",CustDateFilter[i]);
      IF CustLedgEntry2.FIND('+') THEN REPEAT
          j := CustLedgEntry2."Document Type";
          IF j > 0 THEN
              NoOfDoc[i][j] := NoOfDoc[i][j] + 1;

          CustLedgEntry2.CALCFIELDS("Amount (LCY)");
          CustBalanceLCY := CustBalanceLCY - CustLedgEntry2."Amount (LCY)";
          IF CustBalanceLCY > HighestBalanceLCY[i] THEN
            HighestBalanceLCY[i] := CustBalanceLCY;

          // Optimized Approximation
          IF (CustLedgEntry2."Document Type" = CustLedgEntry2."Document Type"::Invoice) AND
             NOT CustLedgEntry2.Open
          THEN
            IF CustLedgEntry2."Closed at Date" > CustLedgEntry2."Posting Date" THEN
              UpdateDaysToPay(CustLedgEntry2."Closed at Date" - CustLedgEntry2."Posting Date",DaysToPay,NoOfInv)
            ELSE
              IF CustLedgEntry2."Closed by Entry No." <> 0 THEN BEGIN
                IF CustLedgEntry3.GET(CustLedgEntry2."Closed by Entry No.") THEN
                  UpdateDaysToPay(CustLedgEntry3."Posting Date" - CustLedgEntry2."Posting Date",DaysToPay,NoOfInv);
              END ELSE BEGIN
                CustLedgEntry3.SETCURRENTKEY("Closed by Entry No.");
                CustLedgEntry3.SETRANGE("Closed by Entry No.",CustLedgEntry2."Entry No.");
                IF CustLedgEntry3.FIND('+') THEN
                  UpdateDaysToPay(CustLedgEntry3."Posting Date" - CustLedgEntry2."Posting Date",DaysToPay,NoOfInv);
             END;
        UNTIL CustLedgEntry2.NEXT(-1) = 0;
      IF NoOfInv <> 0 THEN
        AvgDaysToPay[i] := DaysToPay / NoOfInv;

      END;
      EXIT(AvgDaysToPay[i]);
    END;

    LOCAL PROCEDURE UpdateDaysToPay@1(NoOfDays@1000 : Integer;VAR DaysToPay@1000000000 : Decimal;VAR NoOfInv@1000000001 : Integer);
    BEGIN
      DaysToPay := DaysToPay + NoOfDays;
      NoOfInv := NoOfInv + 1;
    END;

    BEGIN
    END.
  }
  RDLDATA
  {
  }
}

lanemark · 2008-03-10

thanks guys,
I there not a standard report that can report the average payment time for all customers rather than looking at individual customer cards.
The two reports you have offered may do the full blown report I asked for but looks a little more complicated to set up.

alan

ara3n · 2008-03-10

My report doesn't require any setup. Just copy it to text and import it into Navision. Compile it and run it.

jonsan21 · 2008-03-11

Next time I need any report, I'll make sure I post it here first.

Great sharing spirit !

ara3n · 2008-03-11

jonsan21 wrote:

Next time I need any report, I'll make sure I post it here first.

Great sharing spirit !

Out of 55K companies out there, I'm guessing 30-50 percent of the reports are same report that is rewritten for the customer.

Savatage · 2008-03-12

For Me I had to simply change all the (LCY)'s to ($)'s

- thanks for the report.

Savatage · 2008-03-12

Jut noticed that if I run the report for a month or two or three
the Ave Monthly Sales is Correct - But If I run it for a year it divides the
Sales ($) by 13 not 12 making the Avg Monthly Sales calc incorrect.
Date Filter: 010107..123107

Anyone else?
http://savatage99.googlepages.com/Incor ... yTotal.pdf

ara3n · 2008-03-12

I changed how AvgMonthSales was calculated

Date.SETRANGE("Period Type",Date."Period Type"::Month);
Date.SETRANGE("Period Start",FromDate,ToDate);
AvgMonthSales := Date.COUNT;
IF AvgMonthSales = 0 THEN
  AvgMonthSales := 1;
END;

I fixed the object above. so you can copy and paste the object.

Savatage · 2008-03-12

=D> Sweet \:D/ Thanks Again!

ara3n · 2008-03-12

Sure.

late payer report

Comments

Categories