Could Someone Tell Me How This Code Throws an Error?

matttrax · 2009-06-11

I'm trying to post an Item Journal to negatively adjust some inventory. Everything is tracked by a serial so there are item tracking lines for every journal line. When the posting routine gets to the second line it errors out, saying that "Value Entry xxxx already exists." The value entry that does already exist has nothing to do with the line that is being posted, and in fact shouldn't even come up in the filters.

I have looked at the debugger to see that no other filters are applied. It is only the Item Ledger Entry No. It is filtering on the correct Item Ledger Entry, but when it does a FIND 1) It errors out somehow even though it's in a conditional statement and 2) The Value Entry that already exists does not have that Item Ledger Entry on it.

The following code is from Codeunit 22. Function EnsureValueEntryLoaded(ValueEntry,ItemLedgEntry)

ValueEntry.SETCURRENTKEY("Item Ledger Entry No.");
ValueEntry.SETRANGE("Item Ledger Entry No.",ItemLedgEntry."Entry No.");
IF ValueEntry.FIND('-') THEN; <-- Error thrown here

It is called by Codeunit 22 in the Function TouchItemEntryCost(ItemLedgerEntry,IsAdjustment)

ItemLedgerEntry."Applied Entry to Adjust" := TRUE;
WITH ItemLedgerEntry DO
  SetAdjmtProp("Item No.",ItemLedgerEntry."Entry Type",IsAdjustment,"Prod. Order No.",
  "Prod. Order Line No.","Posting Date","Posting Date");

IF NOT IsAdjustment THEN BEGIN
  EnsureValueEntryLoaded(ValueEntry,ItemLedgerEntry);  // <---- Here
  AvgCostAdjmtEntryPoint.UpdateValuationDate(ValueEntry);
END;

kriki · 2009-06-11

You have been hit by the buffering mechanism of NAV!

When NAV (all native without debugger) and also from some version under SQL buffers the insert into tables UNTIL some command requests a return from the DB-server. In that moment NAV sends all the inserts to SQL and you might hit the error. Search for an INSERT-statement on that table and for testing, change it to

IF TheTable.INSERT THEN ;

This will immediately trigger the error.

David_Singleton · 2009-06-11

kriki wrote:
You have been hit by the buffering mechanism of NAV!

When NAV (all native without debugger) and also from some version under SQL buffers the insert into tables UNTIL some command requests a return from the DB-server. In that moment NAV sends all the inserts to SQL and you might hit the error. Search for an INSERT-statement on that table and for testing, change it to
IF TheTable.INSERT THEN ;
This will immediately trigger the error.

Interesting, I thought this was only in Native. Do you know which versions of SQL this happens in?

ara3n · 2009-06-11

bulk inserting was introduced with 5.0 sp1

gerdhuebner · 2009-06-12

Well, the question why the error is thrown lately may be answered but why is there such a kind of error at all? At first I would ensure that the already existing value entry is definitely created during the active transaction. To test this, please take a look at the value entry table (object designer) after the error occured and search for the value entry with the entry no. corresponding to the error message. It may be (though unlikely but theoretically) possible that someone has deleted some item ledger entries (with an appropriate license, of course) and forgot the value entries (and may be the application entries, too).

David_Singleton · 2009-06-12

ara3n wrote:

bulk inserting was introduced with 5.0 sp1

#-o yes of course. Thanks.

kriki · 2009-06-12

gerdhuebner wrote:

Well, the question why the error is thrown lately may be answered but why is there such a kind of error at all? At first I would ensure that the already existing value entry is definitely created during the active transaction. To test this, please take a look at the value entry table (object designer) after the error occured and search for the value entry with the entry no. corresponding to the error message. It may be (though unlikely but theoretically) possible that someone has deleted some item ledger entries (with an appropriate license, of course) and forgot the value entries (and may be the application entries, too).

Probably it is not the problem. Each insert of an entry-table is done by locking the table and taking the last record (on SQL, only this last record is exclusively locked) and then adding 1 to the Entry No.
I think someone is inserting extra value entries by non-standard NAV code.
If you post more than 1 item ledger at a time, the last used Value entry "Entry No." is saved to avoid having to lock the table and get the last value entry again. If extra records are created by non-standard NAV code, this generates problems.
This is the reason I said to use "IF TheTable.INSERT THEN ; ".

And now that I think about it: I made a mistake. previous insert would avoid the error.
It should be :

IF NOT ValueEntry.INSERT THEN 
  ERROR('Value entry %1 already exists',ValueEntry."Entry No.");

gerdhuebner · 2009-06-12

I could reproduce the above described behavior by deleting the last few item ledger entries from table 32 manually and trying to post an item journal.
As Kriki already pointed out, the reason why the error within the debugger occurs so lately is true to the new so called bulk-inserts-feature, which was introduced in NAV 5.0 (SP1) - see for example:
http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/archive/2008/04/14/bulk-inserts-in-microsoft-dynamics-nav-5-0-sp1.aspx
This feature applies only to SQL server. If you try the same within the native server, the debugger will stop at the right position.

It may be worth noting, that in the case of item journal posting (and other posting routines), the amount of time that a table is locked (this is claimed to be the main purpose of the new feature) is not reduced - at least not for the item ledger entry and value entry tables - because the locking immediately (and inevitably) becomes effective right in the beginning of codeunit 22 due to the usual LOCKTABLE - FINDLAST commands.

There is another remarkable point, yet:

kriki wrote:

...Each insert of an entry-table is done by locking the table and taking the last record (on SQL, only this last record is exclusively locked) and then adding 1 to the Entry No.

So why is there an error at all, if the posting routine simply adds value entries with the next free entry no.?
The reason is due to the function EnsureValueEntryLoaded, which has already been posted in the beginning:

ValueEntry.SETCURRENTKEY("Item Ledger Entry No.");
ValueEntry.SETRANGE("Item Ledger Entry No.",ItemLedgEntry."Entry No.");
IF ValueEntry.FIND('-') THEN;

Now it is clear, that the ValueEntry with the next free entry no. is switched back to a lower entry no., because there already exists a value entry for the corresponding Item Ledger Entry (which has been deleted before). In some respect I find it striking that the same piece of code where the debugger more or less accidentally stops is responsible for the error in the end...

By the way, someone should check if the ValueEntry.FIND('-') should be changed to ValueEntry.FIND('+') - I'm worrying about the case when there exist two or more value entries for an item ledger entry...

matttrax · 2009-06-12

It was the buffer issue, but the real reason was because I had value entries with no corresponding item ledger entry. I was creating a test database and accidentally copied these value entries over as I didn't really feel like writing code to create thousands of journal lines and post them.

On the one hand yes it's bad (very very bad) to delete ledger entries and import directly into the ledger instead of a journal. But on the other, I don't know if there's a better way to really learn all those dependencies.

And then you learn / remember other things, like the buffer issue.

kriki · 2009-06-15

gerdhuebner wrote:

It may be worth noting, that in the case of item journal posting (and other posting routines), the amount of time that a table is locked (this is claimed to be the main purpose of the new feature) is not reduced - at least not for the item ledger entry and value entry tables - because the locking immediately (and inevitably) becomes effective right in the beginning of codeunit 22 due to the usual LOCKTABLE - FINDLAST commands.

Well, it doesn't really help postponing the locking time, but it definitly helps to speed up a lot of inserts in the same table (but only if no other actions happen on that table). See also http://mibuso.com/blogs/kriki/2008/04/01/sql-changes-for-50sp1/

Could Someone Tell Me How This Code Throws an Error?

Answers

Categories