My Adventures using eConnect Stored Procedures – Part 1

OK. So over a month ago I talked about doing an integration using the eConnect stored procedures directly instead of the COM+ or Message Queue interfaces. Well, I’m happy to report that the first one is completed. It’s a GL integration with AA (Analytical Accounting) transactions from an Excel file. Each row represents a single journal entry distribution and has two columns with AA dimensions. Each AA column represents a different dimension with a code for each that is allocated 100% of the distribution amount.

As I noted in the previous post, each eConnect XML node represents a single stored procedure of the same name. In this integration we’re using the following nodes:

• taGLTransactionHeaderInsert
• taGLTransactionLineInsert
• taAnalyticsDistribution

Each of these stored procedures has a set of parameters that matches the parameters of the XML node and follows the same rules in terms of defaults and whether it’s required or not. In addition there are two output parameters at the end of the parameters list, @O_iErrorState, an integer and @oErrString a 255 character varchar. These parameters return information an any errors that occurred in processing.

In addition to the stored procedures that represent the eConnect XML nodes, there was an additional stored procedure, taGetNextJournalEntry, I used to return the next journal entry number. This stored procedure has the following three parameters:

• @I_vInc_Dec – An integer that is either 1 or 0 to indicate whether to increment or decrement the number (in case you’re rolling back a transaction)
• @O_vJournalEntryNumber – A 13 character output parameter that returns the next journal entry number.
• @O_iErrorState – An integer output parameter that returns an error number or 0 if no errors occurred.

A lesson I learned was the importance of running the stored procedures in the correct order. My gut told me to create the header first, then the transaction lines and AA distributions. My gut was wrong.

Actually after creating the header and then the transaction lines I was mostly OK. The transactions did get created with the proper distributions. The only clue that something was wrong was that the batch transaction count and total was incorrect. However, after adding the AA logic I got no AA distributions.

Integrating AA has never failed to frustrate me. A couple of hours of foul language and troubleshooting later I ended up writing a little test app that wrote added the transactions through the COM+ interface. After running a SQL trace in SQL Server Profiler, I discovered that the transaction header insert stored procedure needs to be run last, not first. Once the calls were rearranged all was happy!

What did I learn? When you avoid the COM+/XML parsing/DTC overhead it’s incredibly fast. That you should have a test application ready to create the transactions using the COM+ interface to determine the correct stored procedure order. That you’ll need to handle transactions in your own code instead of relying on the COM+ object and DTC. And that it’s really not that hard to do and a good option if you have direct access to the database.

In future posts I’ll talk about some of the other things I did in this integration including handling the errors returned from the eConnect stored procedures and connecting to the database using the end-user’s GP credentials.