How to see why you are getting CONSISTENCY Error

ara3n

This is a trick I use whenever I do try to solve and see why you the db is getting CONSISTENT Error.

I create the following CU.

OBJECT Codeunit 50000 Single Instance CU
{
  OBJECT-PROPERTIES
  {
    Date=10/11/07;
    Time=[ 2:50:02 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 CU 12 I 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;
      //MOD01 Start
      SingleCU.InsertGL(GLEntry);
      //MOD01 End
      IF NOT InsertFAAllocDim(GLEntry."Entry No.") THEN

Once you've made the changes. You run the SinleInstanceCU Once.
Then do what ever you do to get the consistency error.
Then Run the SingleInstanceCU again.
You'll see a list of GL lines Inconsistency. You will see why the transaction is not balanced.

kine

Good tip Ara3n =D>

kirankumar

This is really works ...

Can you please let me know the 'Kitting' Functionality Introduced in Nav-5.0.

I am really interested to know Such New things helps lot...

Regards
KiranKumar

Alex_Chow

Very nice! Beats the alternative of turning off the CONSISTENT function and trying to figure out what happened.

Waldo

Nice nice =D> .
Tested and approved!

ara3n

Alex Chow wrote:

Very nice! Beats the alternative of turning off the CONSISTENT function and trying to figure out what happened.

Yep, hopefully you are doing that in a backup db.

ara3n

Waldo wrote:

Nice nice =D> .
Tested and approved!

I'm glad you are approving this one.

Alex_Chow

ara3n wrote:

Alex Chow wrote:

Very nice! Beats the alternative of turning off the CONSISTENT function and trying to figure out what happened.

Yep, hopefully you are doing that in a backup db.

Life would suck if I didn't...

Nonetheless, you still rock.

Waldo

ara3n wrote:

Waldo wrote:

Nice nice =D> .
Tested and approved!

I'm glad you are approving this one.

Well, I've got to keep being objective

Savatage

Question
I can impliment this but the users license gives them an error that they can't use this codeunit. (I had to give it a diff unused #) that is now out of our range.

would adding indirect permissons like
TableData G/L Entry=rimd help? or could it be inserted into another codeunit that is accessible (That would be sloppy tho I think)?

ara3n

No you don't need the permission for GLEntry.
The code only inserts into temporary table not actual table. As long as the user has indirect permission to post glentry, this would work.

As far as not in the license, this 50000 range, I'm assuming most customer start from that range to purchase CU.

Savatage

Now sure if the purchase structure has changed over the years but by puchasing 100 Reports didn't get us any more codeunits. Since it was new to us - we were more fixed on Reports, forms, tables, dataports. Puchasing a decnet number for codeunit spots 50000+ was not a big concern since we didn't even know what it was. And many of the things needed can be done using reports. So our seller never really mentioned to us that we could buy codeunit #'s (50000+) probably for our own good too

ara3n

I see.

I had the same issue with another customer as well.
It was taking too long to order the CU. So I used old CU that was used in 2.6 in 100 range but did not longer exists in 4.x but the client has still license to them.


For example Department and Project tables are no longer there but you can import a 2.6 fob object and then use them as other codes.

kriki

Alex Chow wrote:

Very nice! Beats the alternative of turning off the CONSISTENT function and trying to figure out what happened.

With SQL, I have another trick:
I start the debugger with a breakpoint just before the error. Then I check the T17-table in another session (of course in a test DB!). The advantage of seeing uncommitted records...

davmac1

I have a consistent problem and I believe I know exactly what is causing it. The sales tax calculation is a penny off versus the outstanding amount on one of the lines.
Does anyone know a fix for this?
The invoice rounding is set to .01

I think the problem is caused by the sales tax routine calculating the sales tax for the entire sales invoice, then adjusting the sales lines taxable amounts to make them balance, but not making a corresponding adjustment to the outstanding amount.

ara3n

On sales Order Hit F9 and on invoicing tab change the tax by 0.01 cent.
There is a setup allow tax differnce to be checked.

davmac1

I tried adjusting the amount, but I still get the error.
I have the tax difference checked.

One of the sales lines has Outstanding amount = 27.06 and the Amount Including Tax = 27.07.
Changing the tax in the Invoicing tab does not change this amount.
Do I need to change the amount inclusing tax on the sales line itself by editing the line?

ara3n

Changing the Tax amount in Sales Order Statistic does change Amount including tax on the sales line.

davmac1

I am only seeing a total taxable amount for each tax type, no breakdown by individual sales line is there a way ti get to that?

ara3n

on statistic form you see in the detail lines the breakdown of each joursdiction. you can change the tax amount of each line.

davmac1

But the tax lines are for the order. I don't see a breakdown per line item.

ara3n

you see a breakdown by jurisdiction. what state are you charging tax and how many lines do you see on statistic form? And have you tried to change the tax amount for one of the lines by 1 cent?

davmac1

3 lines - 1 per jurisdiction
The tax amount on the sales line is non-editable.
I could change it as a developer, but that means I cannot turn over future problems to the user.
This particular order had a partial shipment, so I posted the invoice only, which appears to have fixed the problem. The remaining items are resources which did not appear on the shipment and the sales tax amount now reconciles with the outstanding amount.

ara3n

On the statistic Form F9 the user can change the Tax Amount. Click on Invoice tab in statistic form and then on the detail lines where it shows lines for every jurisdiction you can change the Tax amount field. Have you tried to change it by 1 cent?

davmac1

Yes I did. I still had the error. I then tried posting the invoice without selecting the shipment and invoice problem and that posted and it also adjusted the outstanding amount to match the amount including tax on the sales line.

anwar0206

=D> Really a great JOB.

ara3n

anwar0206 wrote:

=D> Really a great JOB.

thanks

David_Singleton

ara3n wrote:

This is a trick I use whenever I do try to solve and see why you the db is getting CONSISTENT Error.

I create the following CU.

OBJECT Codeunit 50000 Single Instance CU
...

And in CU 12 I add the following Code in function FinishCodeunit

     ...
     //MOD01 Start
      SingleCU.InsertGL(GLEntry);
      //MOD01 End
      IF NOT InsertFAAllocDim(GLEntry."Entry No.") THEN

Once you've made the changes. You run the SinleInstanceCU Once.
Then do what ever you do to get the consistency error.
Then Run the SingleInstanceCU again.
You'll see a list of GL lines. You will see why the transaction is not balanced.

I normally use one of the other methods, i.e. create a journal to see what will be posted, or remove the CONSISTENT command to see what was going on. I just recently had a new consistent error, and decided to try this tip, and I must say that its simple, it works and its brilliant.

Well done Ahmed =D>

ara3n

Thanks.

Leaving the code on customer db won't hurt performance because the function will simply exit out if you haven't run it manually from object designer.

David_Singleton

ara3n wrote:

Thanks.

Leaving the code on customer db won't hurt performance because the function will simply exit out if you haven't run it manually from object designer.

Yes I noticed this, but in the client didn't want modified code in the live db (that would require audit testing etc). Anyway great tip.

jaustin

I'm attempting to create the code unit that ara3n suggested but am not sure where/how some of this code can be put into the code unit (designer mode). Example: How or where do you enter the "TempGLEntry@1000000000 : TEMPORARY Record 17;" ??? I don't see a way to enter this in the designer.

I've seen other postings with code displayed in the way that ara3n has put it in his posting. How do you get a code unit to display all of the properties and code the way that has been posted here? Can this code be imported some how, as is?

Thanks,