Let’s make G/L correspondence creation faster in Russian localized NAV 2017

I’d like to share with you a small modification in codeunit 12404 which is used in Russian localization. 

Almost all of our clients sometimes face with a low performance while creating G/L Correspondence Entry.  Under some conditions this process takes hours or even days. Only cost adjustment can compete with it J. The problem becomes obvious if you post a transaction which consist of thousands of G/L Entries. (All the records have the same value in  “Transaction No.”  field)

If we look at algorithm which is used in function ProcessTransaction of  codeunit 12404 (this code is from Nav 2016 but in other version the code looks similar) we see the following:

SimpleMode := FALSE;

WITH TempGLEntry DO BEGIN

  FOR Level := 1 TO 3 DO BEGIN

    SimpleMode := Level > 1;

    IF FIND('-') THEN

      REPEAT

        IF (Amount = 0) AND ("Additional-Currency Amount" = 0) THEN

          DELETE

        ELSE BEGIN

          CurrentTempGLEntry := TempGLEntry;

          FoundEntry := FALSE;

          SETFILTER("Entry No.",'<>%1',CurrentTempGLEntry."Entry No.");

          IF FIND('-') THEN

            REPEAT

              IF (NOT CurrentTempGLEntry."Used in Correspondence") OR

                 (NOT "Used in Correspondence")

              THEN

                IF ValidateEntries(CurrentTempGLEntry,TempGLEntry,SimpleMode) THEN BEGIN

                  …

                END;

            UNTIL FoundEntry OR (NEXT = 0);

          TempGLEntry := CurrentTempGLEntry;

          IF (Amount = 0) AND ("Additional-Currency Amount" = 0) THEN

            DELETE

          ELSE

            MODIFY;

        END;

      UNTIL NEXT = 0;

  END;

…

How many times  will function ValidateEntries call for transaction which consist of 20000 records ?

The right answer is 399’980’000. Don’t you think It’s too much? And The worse thing  - system do nothing useful if field "Bal. Account No." isn’t filled in 17 table

In ValidateEntries function there is a condition

IF NOT SimpleMode THEN BEGIN

…

  IF ((GLEntry1."Bal. Account Type" = GLEntry1."Bal. Account Type"::"G/L Account") AND

      (GLEntry1."Bal. Account No." <> GLEntry2."G/L Account No.")) OR

     (GLEntry1."Bal. Account No." = '') OR

     ((GLEntry2."Bal. Account Type" = GLEntry2."Bal. Account Type"::"G/L Account") AND

      (GLEntry2."Bal. Account No." <> GLEntry1."G/L Account No.")) OR

     (GLEntry2."Bal. Account No." = '')

  THEN

    EXIT(FALSE);

…

END;

It means that for the first iteration (SimpleMode = FALSE) if "Bal. Account No." isn’t filled system always return False.

If we rewrite Process Transaction in the following way

LOCAL ProcessTransaction()

SimpleMode := FALSE;

WITH TempGLEntry DO BEGIN

  FOR Level := 1 TO 3 DO BEGIN

    SimpleMode := Level > 1;

    // ++ add code here ->

    IF NOT SimpleMode THEN

       SETFILTER("Bal. Account No.",'<>%1','')

    ELSE

        SETRANGE("Bal. Account No.");

    // -- -<

    IF FIND('-') THEN

      REPEAT

        IF (Amount = 0) AND ("Additional-Currency Amount" = 0) THEN

          DELETE

        ELSE BEGIN

          CurrentTempGLEntry := TempGLEntry;

          FoundEntry := FALSE;

          SETFILTER("Entry No.",'<>%1',CurrentTempGLEntry."Entry No.");

          // ++ add code here ->

          IF NOT SimpleMode THEN

            SETFILTER("Bal. Account No.",'<>%1','')

          ELSE

            SETRANGE("Bal. Account No.");

          // -- -<

          IF FIND('-') THEN

            REPEAT

              IF (NOT CurrentTempGLEntry."Used in Correspondence") OR

                 (NOT "Used in Correspondence")

              THEN

                IF ValidateEntries(CurrentTempGLEntry,TempGLEntry,SimpleMode) THEN BEGIN

                  FoundEntry := TRUE;

                  InsertCorrespEntry(CurrentTempGLEntry,TempGLEntry);

                  IF (Amount = 0) AND ("Additional-Currency Amount" = 0) THEN

                    DELETE

                  ELSE

                    MODIFY;

                  IF Level = 3 THEN

                    FoundEntry := "Used in Correspondence";

                END;

            UNTIL FoundEntry OR (NEXT = 0);

          TempGLEntry := CurrentTempGLEntry;

          IF (Amount = 0) AND ("Additional-Currency Amount" = 0) THEN

            DELETE

          ELSE

            MODIFY;

        END;

      UNTIL NEXT = 0;

  END;

  RESET;

  IF FIND('-') THEN

    REPEAT

      UpdateBuffer("Entry No.",Amount);

    UNTIL NEXT = 0;

END;

we remove excess calls of ValidateEntries function.  And now it takes second and minutes instead of hours and days to proceed huge transaction…