Hi everyone hope that someone could help me with this.
I'm in charge of a global project for GE and we have SQL Server in each of the countries in the scope of the project, right now I have a problem posting Payment and Cash Receipt Journals for Colombia.
We're creating Purchase Orders and then Purchase Invoices using the Receipt Lines function and after that we're trying to post Payment Journals to apply this Purchase Invoice specifying the Account Type of the first line as Vendor and using the Applies to Doc No. functionality on this first line and a second journal line to create the balancing entry with a Bank Account Account Type. But when I'm trying to post this Payment Journal it send me a message like the next one " The Detailed Vendor Ledg. Entry already exists. Identification fields and values Entry No. = '2' " but I only have on this table (Detailed Vendor Ledg. Entry ID 380) one record and it has for the Entry No. field a value of 1, I already used the Data Cleaning codeunit to clean all the tables from transactions and reset the Series No. Lines table but still I don't get any result on the server, but when I'm doing all this procedures on NAV as a client it works perfectly on any PC I'd tried so far, this keeps getting even more frustrating with each failure.
I merged the Core database that we have with a fob file that a third party vendor from Colombia sent us with the Microsoft Dynamics Developer's Toolkit, this fob was created to calculate the taxes (Localization) for Colombia and this is the only country where we're having this type of problems.
So, I'm writting to ask for help from your side guys. Does anyone has suggestions on why this can be happening? I really appreciate if someone can give suggestions on this regard.
Receive many thanks in advanced and regards.
In reply to Jörg A. Stryk:
I don't get any result on the server, but when I'm doing all this procedures on NAV as a client it works perfectly on any PC I'd tried so far
What does THAT mean??? For a NAV client it does not matter where it is executed, regardless of the machine being a Server or ordinary PC - it's still the same client! A difference might be the security context used ...
My guess is that someone messed with the code for inserting "Detailed Vendor Ledger Entries". The error you discribe is prevent by NAV standard by explicitly locking this table - it's the same algorithm with ANY Ledger Entries:
DetailedVendorLedgerEntry.LOCKTABLE;IF DetailedVendorLedgerEntry.FINDLAST THEN NewEntryNo := DetailedVendorLedgerEntry."Entry No." + 1ELSE NewEntryNo := 1;
If anyone removed this LOCKTABLE then the PK violation error you describe could happen ...
Joerg A. Stryk - NAV/SQL Performance Optimization
Thanks too much for your reply I'd been seeking through the code of the Gen. Jnl.-Post Line codeunit and it has on the PostVend procedure the next structure
exactly as it has to be in NAV 5.0 SP1 Standard and we're using this same code on other countries and we're not having these problems. And the Entry No. values for the Detailed Vendor Ledg. Entry table are being incremented as the standard always does it in the PostDtldVendLedgEntries procedure of this codeunit like this
DtldCVLedgEntryBuf.RESET; IF DtldCVLedgEntryBuf.FINDSET THEN BEGIN IF VendLedgEntryInserted THEN BEGIN SaveEntryNo := NextEntryNo; NextEntryNo := NextEntryNo + 1; END; REPEAT CLEAR(DtldVendLedgEntry); DtldVendLedgEntry.TRANSFERFIELDS(DtldCVLedgEntryBuf); DtldVendLedgEntry."Entry No." := DtldVendLedgEntryNoOffset + DtldCVLedgEntryBuf."Entry No."; DtldVendLedgEntry."Journal Batch Name" := GenJnlLine."Journal Batch Name"; DtldVendLedgEntry."Reason Code" := GenJnlLine."Reason Code"; DtldVendLedgEntry."Source Code" := GenJnlLine."Source Code"; DtldVendLedgEntry."Transaction No." := NextTransactionNo; VendUpdateDebitCredit(GenJnlLine.Correction,DtldVendLedgEntry); DtldVendLedgEntry.INSERT;
Do you think I have to lock the table once again in the PostDtldVendLedgEntries procedure? Or do you have any other idea, thanks in advanced and receive many regards.
In reply to Rashed Amini:
You're very perceptive as a matter of fact Suani created the localization objects for the calculation of the taxes in Colombia!
I will get in touch with them, 'cause I used once again the Developer's Toolkit to assure myself that I wasn't making any error on the merge but it's standard code almost entirely I think it's something related to the security context used as Jörg said in his post or something in the Suani code.
Thanks and receive regards.
Maybe I explained myself not too well, I get the error message " The Detailed Vendor Ledg. Entry already exists. Identification fields and values Entry No. = '2' " described on my first post when I run the Payment or Cash Receipt Journal post procedures on SQL Server but this doesn't happen when it's executed from a NAV native database.
If you have any idea of what could be causing the error I'll aprecciate if you can guide me throught this.
Receive kind regards.