eConnect General Ledger and Duplicate Key Issues

(0) Share

Report

Posted on by Tommy G

355

Hello,

Recently at my workplace we implemented eConnect to integrate general ledger batches into GP. However, in the past week we have had (3) instances of users encountering duplicate key issues when attempting to save their batches in GP. In one instance, the error message was 'A save operation on table GL_TRX_HDR_WORK has created a duplicate key.' The accompanying error message said, 'Cannot insert duplicate key row in object dbo.GL10000 with unique index AK2GL10000. The duplicate key value is (84584).

We are not aware of these errors occurring prior to implementing eConnect. I have removed the C# code that gets the next journal number from GP, allowing eConnect to automatically handle it. I am not convinced that eConnect is causing the problem, but it is interesting that these errors have occurred since we put eConnect in place. Does eConnect make this concurrency issue more prone to happen? Just wondering if anyone has encountered this from an eConnect perspective, and what they did to fix it?

Thanks!

Tom

*This post is locked for comments

All responses (24)

Answers (0)

steveendow 2,281 on at

Like (0)

Report

RE: eConnect General Ledger and Duplicate Key Issues

Hi Tom,

For integrations implemented as Console applications, I use Windows Task Scheduler. With recent versions of windows (Server 2008, etc.) it has been very reliable and has worked well.

The only recommendation would be to confirm that the last option on the Settings tab be set to "Do not start a new instance". I believe that this is the default value--you want to avoid having two instances running at the same time.

My only general recommendation would be to have validation and error handling. What if a JE is imported a day after close that is dated for the prior fiscal period? What if you attempt to import a new or invalid GL account? Validate those cases and any other issue that comes up and have a way to notify users, log the errors, and/or reprocess the failed transactions.
steveendow 2,281 on at

Like (0)

Report

RE: eConnect General Ledger and Duplicate Key Issues

Hi Tom,

For integrations implemented as Console applications, I typically use Windows Task Scheduler. With recent versions of windows (Server 2008, etc.) it has been very reliable and has worked well.

The only recommendation would be to confirm that the last option on the Settings tab be set to "Do not start a new instance". I believe that this is the default value--you want to avoid having two instances running at the same time.

My only general recommendation would be to have validation and error handling. What if a JE is imported a day after close that is dated for the prior fiscal period? What if you attempt to import a new or invalid GL account? Validate those cases and any other issue that comes up and have a way to notify users, log the errors, and/or reprocess the failed transactions.
Tommy G 355 on at

Like (0)

Report

RE: eConnect General Ledger and Duplicate Key Issues

Thanks again Steve. Your help has been invaluable.

I was just wondering how your eConnect code is kicked off? In our environment we have a Windows task that runs once a minute which kicks off the compiled executable C# file which creates the XML and writes it to the eConnect service. I was wondering if there is anything inherently problematic with that approach that might make it more susceptible to duplicate journal entry issues?

Also, is there anything else we should know (i.e. enabling or disabling anything in Great Plains, config file or server settings, etc.), before attempting to plug eConnect back in to our production environment?

Thanks!

Tom
steveendow 2,281 on at

Like (0)

Report
RE: eConnect General Ledger and Duplicate Key Issues

Hi Tom,

My InsertTransaction method is very similar to yours--I just have a few additional catch statements.

Glad to hear that increasing the JE number appears to have helped.

public bool InsertTransaction(ref string response, string transactionXML) { GP gp = new GP(gpServer, gpUserID, gpPassword); //, log string connString = gp.ConnectionStringWindows(); response = string.Empty; eConnectMethods eConnCall = new eConnectMethods(); try { response = eConnCall.CreateTransactionEntity(connString, transactionXML); return true; } catch (eConnectException ex) { response = "eConnectException: " + ex.Message; response += "\r\nInner Exception: " + ex.InnerException; //response += "\r\n" + transactionXML; return false; } catch (SqlException ex) { response = "SqlException: " + ex.Message; response += "\r\nInner Exception: " + ex.InnerException; //response += "\r\n" + transactionXML; return false; } catch (Exception ex) { response = "Exception: " + ex.Message; response += "\r\nInner Exception: " + ex.InnerException; //response += "\r\n" + transactionXML; return false; } }
Tommy G 355 on at

Like (0)

Report

RE: eConnect General Ledger and Duplicate Key Issues

Hi Steve,

I couldn't see the implementation of your eConn.InsertTransaction() method, so here's what I'm using. Does it look okay?

private String writeXMLToGreatPlains(String xml)

{

String theReturnedXML = "";

eConnectMethods eConnMethods = new eConnectMethods();

try

{

theReturnedXML = eConnMethods.CreateTransactionEntity(MSDynamicsConnectionString, xml);

Console.WriteLine("XML returned from CreateTransactionEntity():" + theReturnedXML);

}

catch (Exception x)

{

throw x;

}

finally

{

eConnMethods.Dispose();

}

return theReturnedXML;

}

Also, so far so good in terms of the manual creation of batches since we bumped up the next journal entry to 85000 yesterday. Thirteen batches have been created with no issues. I'm wondering if GP won't try to fill gaps if they exceed a certain number, and what that number is?

Thanks and Regards,

Tom
Tommy G 355 on at

Like (0)

Report

RE: eConnect General Ledger and Duplicate Key Issues

Hi Steve,

I talked to our General Accounting Manager and she's going to increase the journal entry number per your suggestion. If all goes well I'll reintroduce eConnect back into production with your improved code.

Thanks,

Tom
steveendow 2,281 on at

Like (0)

Report

RE: eConnect General Ledger and Duplicate Key Issues

Hi Tom,

If you are getting errors after manually entering JEs in GP, you may want to change your Next Journal Entry value under General Ledger Setup (Tools -> Setup -> Financial -> General Ledger).

Try rounding the number up to the next thousand, or increase it to get into a "clean" number series.

I'm wondering if importing JEs and letting eConnect assign the numbers, and the other issues you are having with the JE import errors has resulted in a situation where GP is attempting to issue a JE number that has been used.
Tommy G 355 on at

Like (0)

Report

RE: eConnect General Ledger and Duplicate Key Issues

Hi Steve,

Thanks so much for your code. It's a lot more concise that mine, and I've got it in our development environment and am running batches through it. In my prior code, I was passing a pointer (or reference, or whatever C# calls it) to the GLTransactionType into the method I was using to create the header, instantiating a taGLTransactionHeaderInsert(), assigning the values (i.e. BACHNUMB, JRNENTRY, etc.), and then assigning the taGLTransactionHeaderInsert to the passed in GLTransactionType.

The interesting thing is that we are receiving the duplicate journal entry issue both on the eConnect side, when integrating a batch, and when users log into GP and attempt to create batches. Yesterday a user logged in, brought up a batch that she had created (2) days ago, then added some entries and attempted to save it. She then received the infamous "A save operation on table GL_TRX_HDR_WORK has created a duplicate key" message. And it displayed the same journal entry number she saved the batch a couple of days ago. Anyway, I backed out my eConnect code from production and went back to the integration manager - we want to see if these errors will continue when eConnect isn't being used. This has us all scratching our heads. If no errors occur over a period of time, I'll put in eConnect again, but using your code.

Thanks and Regards,

Tom
steveendow 2,281 on at

Like (0)

Report
RE: eConnect General Ledger and Duplicate Key Issues

Hi Tom,

That explains it. You should only use one eConnect method to insert the entire JE. You should not submit the lines and header separately.

You should create an eConnect GLTransactionType and assign the taGLTransactionHeaderInsert object and taGLTransactionLineInsert objects to it.

That is one of the (several) key benefits of using the eConnect serialization--you build the entire transaction and submit it once. eConnect then deals with the various calls to the stored procedures.

See my full code below, but here are the lines that should explain the process:

GLTransactionType glTrans = new GLTransactionType();
taGLTransactionLineInsert_ItemsTaGLTransactionLineInsert[] glTransLines = glTrxLines.ToArray();

glTrans.taGLTransactionHeaderInsert = glTrxHeader;
glTrans.taGLTransactionLineInsert_Items = glTransLines;

GLTransactionType[] glType = { glTrans };

public bool InsertJETransaction(string batchNumb, DateTime trxDate, int lines, ref string message) { taGLTransactionHeaderInsert glTrxHeader = new taGLTransactionHeaderInsert(); List<taGLTransactionLineInsert_ItemsTaGLTransactionLineInsert> glTrxLines = new List<taGLTransactionLineInsert_ItemsTaGLTransactionLineInsert>(); taGLTransactionLineInsert_ItemsTaGLTransactionLineInsert glTrxLine = new taGLTransactionLineInsert_ItemsTaGLTransactionLineInsert(); int hdrTrxNumber = gp.GetNextJENumber(); //int lineSequence = 0; bool returnValue = false; string glAccount = Properties.Settings.Default.glAccount; string description = "GP Batch Load Test"; try { glTrxHeader.BACHNUMB = batchNumb; glTrxHeader.JRNENTRY = hdrTrxNumber; glTrxHeader.REFRENCE = description; glTrxHeader.TRXDATE = trxDate.ToShortDateString(); glTrxHeader.TRXTYPE = 0; glTrxHeader.SERIES = 2; glTrxHeader.USERID = "BatchLoadTest"; for (int lineLoop = 1; lineLoop <= lines; lineLoop++) { //lineSequence += 16384; glTrxLine = new taGLTransactionLineInsert_ItemsTaGLTransactionLineInsert(); glTrxLine.BACHNUMB = batchNumb; glTrxLine.JRNENTRY = hdrTrxNumber; glTrxLine.ACTNUMST = glAccount; glTrxLine.DSCRIPTN = description; glTrxLine.DEBITAMT = 1.23m; glTrxLine.CRDTAMNT = 0; glTrxLines.Add(glTrxLine); glTrxLine = new taGLTransactionLineInsert_ItemsTaGLTransactionLineInsert(); glTrxLine.BACHNUMB = batchNumb; glTrxLine.JRNENTRY = hdrTrxNumber; glTrxLine.ACTNUMST = glAccount; glTrxLine.DSCRIPTN = description; glTrxLine.DEBITAMT = 0; glTrxLine.CRDTAMNT = 1.23m; glTrxLines.Add(glTrxLine); } GLTransactionType glTrans = new GLTransactionType(); taGLTransactionLineInsert_ItemsTaGLTransactionLineInsert[] glTransLines = glTrxLines.ToArray(); glTrans.taGLTransactionHeaderInsert = glTrxHeader; glTrans.taGLTransactionLineInsert_Items = glTransLines; GLTransactionType[] glType = { glTrans }; eConnectType eConnect = new eConnectType(); eConnect.GLTransactionType = glType; MemoryStream memoryStream = new MemoryStream(); XmlSerializer xmlSerializer = new XmlSerializer(eConnect.GetType()); xmlSerializer.Serialize(memoryStream, eConnect); memoryStream.Position = 0; XmlDocument xmlDocument = new XmlDocument(); xmlDocument.Load(memoryStream); memoryStream.Close(); eConn eConn = new eConn(gpServer, gpUserID, gpPassword); string response = string.Empty; returnValue = eConn.InsertTransaction(ref response, xmlDocument.OuterXml, Properties.Settings.Default.gpDatabase); message = response; return returnValue; } catch (Exception ex) { message = ex.Message; return false; } }
Tommy G 355 on at

Like (0)

Report

RE: eConnect General Ledger and Duplicate Key Issues

Hi Steve,

We are using GP 2010.

In terms of getting the next journal entry, I'm using the modified method that I listed above, based on the code you gave me, and then passing that journal entry number into (2) methods that I wrote - one that inserts the line items, and at the end, one that inserts the header. At no time is the journal entry number updated.

Thanks for the load test tool.

Regards,

Tom