SQL locking error on reservation entry in Production Journal

kjspiteri

Hi All,

I am facing an issue with Navision over SQL server where when I try to post transactions from the Production Journal for a released Production order, I am being given the error:

"Another user has modified the record for this reservation entry after you retrieved it from the database. Enter your changes again in the updated window, or start the interrupted activity again. Identification fields and values: Entry No.=209626, Positive='No'"

Investigations the transactions and background processes seems to indicate that the problem is originating from Codeunit 99000830 "Create Reserv. Entry". in the function "TransferReservEntry" at the NewReservEntry.MODIFY line of the following code:


END ELSE BEGIN
NewReservEntry.MODIFY;
TransferQty := NewReservEntry."Quantity (Base)";
IF NewReservEntry."Source Type" = DATABASE::"Item Ledger Entry" THEN BEGIN
IF NewReservEntry.GET(NewReservEntry."Entry No.",NOT NewReservEntry.Positive) THEN BEGIN // Get partner-record
IF NewReservEntry."Quantity (Base)" < 0 THEN
NewReservEntry."Expected Receipt Date" := 0D
ELSE
NewReservEntry."Shipment Date" := 31129999D;
NewReservEntry.MODIFY;
END;

on a local native copy of the database transactions post ok, Problem only occurs on SQL server. Transactions that seem to trigger the issue have Reservation type set as Tracking in the recordset.

I am suspecting the reason to be SQL's record locking. Would appreciate any possible solution that would not have any adverse effect upon posting of the journal, considering the complexity of Reservation entry processing within Navision

ara3n

SQL has stricter rules on versioning than Native Db. That is why you are not getting the error on native db. What version are using, see if there are improvements for your version.

You can also do a get with a different variable and pass the new values to ti and update the record.

kjspiteri

Apparently this is a bug, for which the following solution was provided:

CORRECTION (BUG 12455)

---

Codeunit 99000830, TransferReservEntry

...
NewReservEntry := OldReservEntry;
IF NewReservEntry.RECORDLEVELLOCKING THEN // new line
NewReservEntry.MODIFY; // new line

NewReservEntry."Source Type" := NewType;
...


Codeunit 99000838
TransferPOCompToItemJnlLine function

IF ReservEngineMgt.InitRecordSet(OldReservEntry) THEN
REPEAT
OldReservEntry.TESTFIELD("Item No.",OldProdOrderComp."Item No.");
OldReservEntry.TESTFIELD("Variant Code",OldProdOrderComp."Variant
Code");
OldReservEntry.TESTFIELD("Location Code",OldProdOrderComp."Location
Code");

TransferQty := CreateReservEntry.TransferReservEntry(DATABASE::"Item
Journal Line",
NewItemJnlLine."Entry Type",NewItemJnlLine."Journal Template
Name",NewItemJnlLine."Journal Batch Name",0,
NewItemJnlLine."Line No.",NewItemJnlLine."Qty. per Unit of
Measure",OldReservEntry,TransferQty);

OldReservEntry.GET(OldReservEntry."Entry No.",OldReservEntry.Positive);
//New line

IF ReservEngineMgt.NEXTRecord(OldReservEntry) = 0 THEN
IF ItemTrackingFilterIsSet THEN BEGIN
OldReservEntry.SETRANGE("Serial No.");
OldReservEntry.SETRANGE("Lot No.");
ItemTrackingFilterIsSet := FALSE;
EndLoop := NOT ReservEngineMgt.InitRecordSet(OldReservEntry);
END;

UNTIL EndLoop OR (TransferQty = 0);


Hope this can be of help to those who encounter this issue....

ara3n

Did you find it on partner source?

GoMaD

Does anybody know if there are more simmilar issues on the SQL Option?

ara3n

Navision themselves did not program on SQL, but on Native and so the programmer test everything in Native, and then it was tested on SQL as well I'm guessing.
But with 4? they've started programming programming in SQL. So things turn the other way around.

GoMaD

ara3n, I just looked in the 4.00 SP2 database and the above mentioned bug is still in this version.

Shouldn't this bug fix be implemented in this version?

Regard,

ara3n

Post your code where it stops. You have do write a get statements. It's fairly easy to fix.

GoMaD

Ara3n,

I used the bug fix suggested by kjspiteri, and it works.

Still, thanks for your help!

Miklos_Hollender

Thanks, it helped me a lot at with client. I think I should ask my managers for a PartnerSource password and start each error investigation there instead of in the debugger :-)

batmaki

Hi all,

i'm having the same problem but the solution that kjspiteri provided is no good for me...

Now, the error appears on the new lines:

[...]
IF NewReservEntry.RECORDLEVELLOCKING THEN // new line 
NewReservEntry.MODIFY; // new line 
[...]

Any news on this issue?

Thanks in advance