Thursday, October 23, 2014

Error : The transaction cannot be completed because it will cause inconsistencies - NAV 2009 SP 01 | NAV 2009 R2

Today one of our client complained that he can't unapply a payment, at first we thought he was doing it wrong. So we remotely logged to their system and asked him to unapply the entry. 
Here comes the error..

To find the problem and a solution I started to surf. I found 2 fine articles and I have used both these article to provide the solution for this issue. 


How to find why this is happening and which entry is actually triggering this error. 


First created a new codeunit using following code,

 OBJECT Codeunit 50000 Single Instance CU
{
  OBJECT-PROPERTIES
  {
    Date=14-10-23;
    Time=12:54:04 PM;
    Modified=Yes;
    Version List=MOD01;
  }
  PROPERTIES
  {
    SingleInstance=Yes;
    OnRun=BEGIN
             IF NOT StoreToTemp THEN BEGIN
              StoreToTemp := TRUE;
            END ELSE
              FORM.RUNMODAL(0,TempGLEntry);
          END;
  }
  CODE
  {
    VAR
      TempGLEntry@1000000000 : TEMPORARY Record 17;
      StoreToTemp@1000000001 : Boolean;

    PROCEDURE InsertGL@1000000000(GLEntry@1000000000 : Record 17);
    BEGIN
      IF StoreToTemp THEN BEGIN
        TempGLEntry := GLEntry;
        IF NOT TempGLEntry.INSERT THEN BEGIN
           TempGLEntry.DELETEALL;
           TempGLEntry.INSERT;
       END;
      END;
    END;
    BEGIN
    END.
  }
}


And in Codeunit 12 add the following code in function "FinishCodeunit()
 FinishCodeunit()  
 WITH GenJnlLine DO BEGIN  
  IF GLEntryTmp.FIND('-') THEN BEGIN  
   REPEAT  
    GLEntry := GLEntryTmp;  
    IF GLSetup."Additional Reporting Currency" = '' THEN BEGIN  
     GLEntry."Additional-Currency Amount" := 0;  
     GLEntry."Add.-Currency Debit Amount" := 0;  
     GLEntry."Add.-Currency Credit Amount" := 0;  
    END;  
    GLEntry.INSERT;  
    //Modification 01 Start  
    SingleCU.InsertGL(GLEntry);  
    //Modification 01 End  
    IF NOT InsertFAAllocDim(GLEntry."Entry No.") THEN  

Once you completed the creating of new object and modification to the codeunit 12, follow the below steps,
1. Run the Single "Single Instance CU" ONLY ONCE 
2. Then try to unapply the entry which generate the above error. 
3. Then once again run the "Single Instance CU" and you will see the actual transaction that generate the error.  

How to fix this error.
In order to fix this you need to modify 3 codeunits. 
  •  Codeunit 12 - "Gen. Jnl.-Post Line"
  •  Codeunit 226 - "CustEntry-Apply Posted Entries"
  •  Codeunit 227 - "VendEntry-Apply Posted Entries"

1. Change the code in the UnapplyCustLedgEntry function in the Gen. Jnl.-Post Line codeunit (12) as follow:
Existing code

 NextDtldLedgEntryEntryNo := NextDtldLedgEntryEntryNo + 1;  
       DtldCVLedgEntryBuf.TRANSFERFIELDS(NewDtldCustLedgEntry);  
      //Delete the following line.  
      GenJnlLine."Source Currency Code" := DtldCustLedgEntry2."Currency Code";  
      IF GLSetup."Additional Reporting Currency" <> DtldCVLedgEntryBuf."Currency Code" THEN  
       DtldCVLedgEntryBuf."Additional-Currency Amount" :=  
        CalcAddCurrForUnapplication(DtldCVLedgEntryBuf."Posting Date",DtldCVLedgEntryBuf."Amount (LCY)")  

Replacement code
  NextDtldLedgEntryEntryNo := NextDtldLedgEntryEntryNo + 1;  
      DtldCVLedgEntryBuf.TRANSFERFIELDS(NewDtldCustLedgEntry);  
      IF GLSetup."Additional Reporting Currency" <> DtldCVLedgEntryBuf."Currency Code" THEN  
       DtldCVLedgEntryBuf."Additional-Currency Amount" :=  
        CalcAddCurrForUnapplication(DtldCVLedgEntryBuf."Posting Date",DtldCVLedgEntryBuf."Amount (LCY)")  

2. Change the code in the UnapplyVendLedgEntry function in the Gen. Jnl.-Post Line codeunit (12) as follow:
Existing code

  NextDtldLedgEntryEntryNo := NextDtldLedgEntryEntryNo + 1;  
      DtldCVLedgEntryBuf.TRANSFERFIELDS(NewDtldVendLedgEntry);  
      //Delete the following line.  
      GenJnlLine."Source Currency Code" := DtldVendLedgEntry2."Currency Code";  
      IF GLSetup."Additional Reporting Currency" <> DtldCVLedgEntryBuf."Currency Code" THEN  
       DtldCVLedgEntryBuf."Additional-Currency Amount" :=  
        CalcAddCurrForUnapplication(DtldCVLedgEntryBuf."Posting Date",DtldCVLedgEntryBuf."Amount (LCY)")  
 ...  

Replacement code
   NextDtldLedgEntryEntryNo := NextDtldLedgEntryEntryNo + 1;  
      DtldCVLedgEntryBuf.TRANSFERFIELDS(NewDtldVendLedgEntry);  
      IF GLSetup."Additional Reporting Currency" <> DtldCVLedgEntryBuf."Currency Code" THEN  
       DtldCVLedgEntryBuf."Additional-Currency Amount" :=  
        CalcAddCurrForUnapplication(DtldCVLedgEntryBuf."Posting Date",DtldCVLedgEntryBuf."Amount (LCY)")  
 ...  

3. Change the code in the PostUnApplyCustomer function in the CustEntry-Apply Posted codeunit (226) as follow:
Existing code
 GenJnlLine."Source Type" := GenJnlLine."Source Type"::Customer;  
      GenJnlLine."Source No." := "Customer No.";  
      GenJnlLine."Source Code" := SourceCodeSetup."Unapplied Sales Entry Appln.";  
      //Delete the following line.  
      GenJnlLine."Source Currency Code" := DtldCustLedgEntry2."Currency Code";  
      GenJnlLine."System-Created Entry" := TRUE;  
      Window.OPEN(Text008);  
      GenJnlPostLine.UnapplyCustLedgEntry(GenJnlLine,DtldCustLedgEntry2);  

Replacement code
 GenJnlLine."Source Type" := GenJnlLine."Source Type"::Customer;  
      GenJnlLine."Source No." := "Customer No.";  
      GenJnlLine."Source Code" := SourceCodeSetup."Unapplied Sales Entry Appln.";  
      GenJnlLine."System-Created Entry" := TRUE;  
      Window.OPEN(Text008);  
      GenJnlPostLine.UnapplyCustLedgEntry(GenJnlLine,DtldCustLedgEntry2);  

4. Change the code in the PostUnApplyVendor function in the VendEntry-Apply Posted Entries codeunit (227) as follow:
Existing code

  GenJnlLine."Source Type" := GenJnlLine."Source Type"::Vendor;  
      GenJnlLine."Source No." := "Vendor No.";  
      GenJnlLine."Source Code" := SourceCodeSetup."Unapplied Purch. Entry Appln.";  
      //Delete the following line.  
      GenJnlLine."Source Currency Code" := DtldVendLedgEntry2."Currency Code";  
      GenJnlLine."System-Created Entry" := TRUE;  
      Window.OPEN(Text008);  
      GenJnlPostLine.UnapplyVendLedgEntry(GenJnlLine,DtldVendLedgEntry2);  

Replacement code
  GenJnlLine."Source Type" := GenJnlLine."Source Type"::Vendor;  
      GenJnlLine."Source No." := "Vendor No.";  
      GenJnlLine."Source Code" := SourceCodeSetup."Unapplied Purch. Entry Appln.";  
      GenJnlLine."System-Created Entry" := TRUE;  
      Window.OPEN(Text008);  
      GenJnlPostLine.UnapplyVendLedgEntry(GenJnlLine,DtldVendLedgEntry2);  

** You must have Microsoft Dynamics NAV 2009 Service Pack 1 installed to apply this hotfix.

********************** You cannot remove this hotfix. **********************  

Click here to get the link to hot Fix 
Click here to get the article about the first section 

Thank you and Regards,
Tharanga Chandrasekara


3 comments:

  1. Hi Tharanga,

    a few comments on this, as discussed:

    the hotfix you mention is only a hotfix, not a fix for the underlying issue. When you apply it, the unapply posting wil go through alright, but the resulting additional reporting currency (ACY) amounts can be wrong in the end. That's not always good ;)

    I try to explain it, it's a little complicated. The ACY handling in codeunit 12 is not complete, they are tinkering on it since the 2013 version on almost every other Cumulative Update. In the NAV3,NAV4,NAV5,NAV2009 code base, it's also a little bit like layer cake, it's bugs on bugs that normally cancel each other out, but not always.

    Root cause is the "wrong" dependency between GenJnlLine."Source Currency Code", InitGLEntry() and CalcAddCurrency(). Add to this missing ACY amounts in the customer/vendor ledger entries and detailed entries. How ACY gets calculated on the GLEntry level depends on "Source Currency Code". If it is just empty (but the source cust/vend ledger entry has a currency code), this leads to a different calculation. If the currency code should be the same as ACY code, the the amount will be used unchanged. And now you have several combinations, where sometimes the "right" combination is used and sometimes not. In a small percentage of these cases, you will get the consistency check error.

    Back to the hotfix: This forces the calculation of the ACY from the LCY values on the GLEntry level, regardless of the ACY values generated on the cust/vend ledger level. So, this will (almost) always be consistent, but depending on the currency code of the cust/vend ledger entry and if ACY is used the ACY value in the G/L can be really wrong. The only way to cleanly solve this is to set up source currency code for the corresponding det. entry buffer before every InitGLEntry() or after every record change (in the buffers).

    A useful shortcut is to force ACY as Source Currency Code in the GenJnlLine for AutoEntrForDtldCust/VendLedgEntries() even if it isn't, to ensure that ACY amounts that have been calculated will actually be used. The idea behind it is that a great deal of effort is used in the code to calculate these ACY values with the right exchange rate anf save the value in DtldCVLedgEntryBuf, only to not always / never use them, depending on the currency codes in the unapplication.

    As I said, microsoft is reworking this mess and releases fixes every other CU for this. The code more and more approaches my code for the Transaction Currency AddOn :) I had to go over the whole of CU12 for this, and due to the requirement (track the amounts in originating currency, also in the G/L and VAT entries) I had to insert proper currency code handling for ACY, too.

    It's a complicated topic, I'll see if I can find a good example for this.

    with best regards

    Jens

    ReplyDelete
  2. Priyanga de SilvaJune 21, 2016 at 2:28 AM

    Are these codes compatible with 2015 version?

    ReplyDelete