Payment Journals posting issue with NAV on SQL Server

Get our Free Newletter

Don't you have the time to be online here at DUG every day? So how do you follow what is going on in the Dynamics industry and community?
If you subscribe to the DUG Newsletter then you can relax! We will make sure that you don't miss the big news!

Check out our
Newsletter Subscription Center
page a complete list of our different newsletters.

This post has 5 Replies | 1 Follower

Male
19 Posts
223 Points
Joined: 2009-4-3
Last Online:
2010-5-4 4:29
Location: Aguascalientes/Mexico
Emanuel Galvan Salazar Posted: 2009-4-4 5:00 | Locked

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. Big Smile 

Emanuel Galvan Salazar.

Male
621 Posts
10,435 Points
Joined: 2003-8-5
Last Online:
2013-1-18 14:55
Location: Washington DC
DynamicsNAVMVP
Rashed Amini replied on 2009-4-4 5:12 | Locked
I suggest to get in contact the Colombian local solution center and ask them to fix their code. Based from my experience with Suani BSM Network S.L. they are usually very responsive.
Male
1,359 Posts
20,712 Points
Joined: 2000-12-18
Last Online:
2014-11-20 13:06
Location: Nürnberg/Germany
DynamicsNAVMVP
Moderator
Jörg A. Stryk replied on 2009-4-4 8:30 | Locked

Hi Emanuel,

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." + 1
ELSE
  NewEntryNo := 1;

If anyone removed this LOCKTABLE then the PK violation error you describe could happen ...

Kind regards,

Jörg

Joerg A. Stryk
NAV/SQL Performance Optimization


Don't forget to the post(s) that solved your problem!

Male
19 Posts
223 Points
Joined: 2009-4-3
Last Online:
2010-5-4 4:29
Location: Aguascalientes/Mexico
Emanuel Galvan Salazar replied on 2009-4-4 18:49 | Locked

Hi Jörg!

   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

  DtldVendLedgEntry.LOCKTABLE;
  VendLedgEntry.LOCKTABLE;

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. Wink

Emanuel Galvan.

Emanuel Galvan Salazar.

Male
19 Posts
223 Points
Joined: 2009-4-3
Last Online:
2010-5-4 4:29
Location: Aguascalientes/Mexico
Emanuel Galvan Salazar replied on 2009-4-4 19:33 | Locked

Hi Rashed!

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.

Emanuel Galvan Salazar.

Male
19 Posts
223 Points
Joined: 2009-4-3
Last Online:
2010-5-4 4:29
Location: Aguascalientes/Mexico
Emanuel Galvan Salazar replied on 2009-4-6 16:07 | Locked

Hi Jörg!

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.

Emanuel Galvan Salazar.

Page 1 of 1 (6 items) | Get this RSS feed | Bookmark and Share