eConnect SOP Issues, Questions, Discussion...

[drdav...@gmail.com]

Hello,

I'm working on a sales order integration with econnect and am having issues updating transactions with serial numbers or manually set taxes... Any suggestions?

Also, I am using the .net classes to run the econnect integration's, is it faster to run the stored procedures instead since I'm always connected and close to SQL anyway? If so, how do you do an sop transaction with all its parts? do you insert the header first and then the lines, serials, payments, taxes etc after? wont econnect say that the numbers are out if you insert the header without lines?

So far I have delt with serial numbers by deleting all the relevant lines from SOP10201 and updating SOP10200 right before I run my update through econnect, then econnect puts them all back thinking they are newly selected and it works great.

<code>

Dim SqlSerialDelete As String = <string>
IF EXISTS(SELECT LNITMSEQ FROM SOP10200 WHERE SOPNUMBE='{0}' AND SOPTYPE={1} AND LNITMSEQ={2} AND CMPNTSEQ={3} AND ITEMNMBR='{4}')
BEGIN
UPDATE SOP10200 SET QTYFULFI=0 WHERE SOPNUMBE='{0}' AND SOPTYPE={1} AND LNITMSEQ={2} AND CMPNTSEQ={3} AND ITEMNMBR='{4}'
IF EXISTS(SELECT SERLTNUM FROM SOP10201 WHERE SOPNUMBE='{0}' AND SOPTYPE={1} AND LNITMSEQ={2} AND CMPNTSEQ={3} AND ITEMNMBR='{4}' AND SERLTNUM='{5}')
BEGIN
DELETE FROM SOP10201 WHERE SOPNUMBE='{0}' AND SOPTYPE={1} AND LNITMSEQ={2} AND CMPNTSEQ={3} AND ITEMNMBR='{4}' AND SERLTNUM='{5}'
IF EXISTS(SELECT SERLNSLD FROM IV00200 WHERE ITEMNMBR='{4}' AND LOCNCODE='{6}' AND SERLNMBR='{5}')
UPDATE IV00200 SET SERLNSLD=0 WHERE ITEMNMBR='{4}' AND LOCNCODE='{6}' AND SERLNMBR='{5}'
END
END
</string>.Value

</code>


Dim strSQL As Atring = String.Format(SqlSerialDelete,
SOPNUMBE, SOPTYPE, LNITMSEQ, CMPNTSEQ, ITEMNMBR, SERLTNUM, LOCNCODE)
The "Update manual tax" issue...

If I set the tax values manually and insert with econnect it works.
Then, if I update the same transaction with the same data (no changes) it fails saying it cant update manually set taxes. However, If generate the taxes to be the same as they would be if econnect generated them (following all the rules) then it will not error.

SO far my best solution would be to update SQL after econnect does, and set the (one specific) taxes manually. However, starting to code this procedure I have quickly realized this is going to be a bit of a daunting task... Particularly updating all the totals to reflect the changed tax amount, then thinking about it, what if the document is fully paid, but the auto tax says its more than what it should be when its manual, I guess this will be better than the other way, econnect probably wont allow a document to have more payment then docamount....

Is there a procedure I can run outside of GP that will automatically update an SOP transactions totals and distributions by actually doing the math?

ARG, suggestions? discussion?

[MikeD]

<drdav...@gmail.com> wrote in message
news:6baf5a89-3de8-4b1d...@googlegroups.com...

> I'm working on a sales order integration with econnect and am having
> issues updating transactions with serial numbers or manually set taxes...
> Any suggestions?
>
> Also, I am using the .net classes to run the econnect integration's, is it
> faster to run the stored procedures instead since I'm always connected and
> close to SQL anyway? If so, how do you do an sop transaction with all its
> parts? do you insert the header first and then the lines, serials,
> payments, taxes etc after? wont econnect say that the numbers are out if
> you insert the header without lines?

If you're going to call the eConnect stored procedures directly, there are
some things to be aware of that you MUST do. First, the order in which you
call the procedures must be the same as they are in the document structure
listed in the the eConnect programmers guide (I'm assuming you have that).
For example, for a SOP transaction, it is this:

<eConnect>
<SOPTransactionType>
<eConnectProcessInfo />
<taRequesterTrxDisabler_Items>
<taRequesterTrxDisabler />
</taRequesterTrxDisabler_Items>
<taUpdateCreateItemRcd />
<taUpdateCreateCustomerRcd />
<taCreateCustomerAddress_Items>
<taCreateCustomerAddress />
</taCreateCustomerAddress_Items>
<taSopSerial_Items>
<taSopSerial />
</taSopSerial_Items>
<taSopLotAuto_Items>
<taSopLotAuto />
</taSopLotAuto_Items>
<taSopLineIvcInsert_Items>
<taSopLineIvcInsert />
</taSopLineIvcInsert_Items>
<taSopLineIvcInsertComponent_Items>
<taSopLineIvcInsertComponent />
</taSopLineIvcInsertComponent_Items>
<taSopTrackingNum_Items>
<taSopTrackingNum />
</taSopTrackingNum_Items>
<taSopCommissions_Items>
<taSopCommissions />
</taSopCommissions_Items>
<taSopLineIvcTaxInsert_Items>
<taSopLineIvcTaxInsert />
</taSopLineIvcTaxInsert_Items>
<taCreateSopPaymentInsertRecord_Items>
<taCreateSopPaymentInsertRecord />
</taCreateSopPaymentInsertRecord_Items>
<taSopUserDefined />
<taSopDistribution_Items>
<taSopDistribution />
</taSopDistribution_Items>
<taAnalyticsDistribution_Items>
<taAnalyticsDistribution />
</taAnalyticsDistribution_Items>
<taSopMultiBin_Items>
<taSopMultiBin />
</taSopMultiBin_Items>
<taSopHdrIvcInsert />
<taSopToPopLink />
<taSopUpdateCreateProcessHold />
<taCreateSOPTrackingInfo />
<taMdaUpdate_Items>
<taMdaUpdate />
</taMdaUpdate_Items>
</SOPTransactionType>
</eConnect>

From this, you can see that taSopSerial should be called first, then
taSopLineIvcInsert, then taSopLineIvcTaxInsert, then taSopDistribution, and
finally taSopHdrIvcInsert. This is also how they are listed under XML Nodes
in the eConnect programmers guide. Notice you don't need to necessarily call
all of them. For example, if you want eConnect to automatically create
distributions based on GP's setup, then you do not need to call
taSopDistribution and you must set the CreateDist element of
taSopHdrInvcInsert to 1 (which is the default, so if you DO call
taSopDistribution, CreateDist must be set to 0).

Additional things you must do when calling the stored procedures directly is
handle SQL transactions yourself. Because every eConnect stored procedure
might make changes to multiple tables, you MUST use SQL transactions. HOW
you use them is up to you depending on what rollback capabilities you want
to implememt. For example, if you are bringing in several SOP documents and
you want it to be "all or nothing" then you'd begin the SQL transaction at
the very start and commit/rollback at the very end. On the other hand, if
you, for example, still want the first document to be brought it even if the
final document errors, then you'd need to begin the SQL transaction for each
document and commit/rollback after each document. This is one reason why I
personally prefer calling the stored procedures directly. You have more
control to do things like this than what the .NET libraries provide.
Certain stored procedures should always be called using the same SQL
transaction. For example, inserting the lines and inserting the header
should always be done using the same SQL transaction because if an error
occurs while inserting the header, you need to be able to rollback the lines
that were already inserted. Otherwise, you're going to have line records
without a corresponding header record....and that's not good.

You also need to handle getting error information yourself. All eConnect
stored procedures have 2 parameters for this. These are not documented as
elements in the eConnect programmers guide because they're not part of the
XML schema. These 2 parameters are @O_iErrorState and @OErrString. These
need initialized to 0 and a 0-length string (that is, "") respectively prior
to calling the stored procedure. Upon the stored procedure returning, check
the value of @O_iErrorState. If it's still 0, it means no errors occurred.
@OErrString is NOT an error message or description. It is a space delimited
string of ALL the error codes for the errors that occurred. In other words,
ErrorState gives you a SINGLE error code whereas ErrString gives you ALL the
error codes. To get a meaningful error message to display or log to a file,
you need to look up the error code(s) in a table in the GP system database
(typically named DYNAMICS but as of GP2013, this CAN be named differently so
you need to be careful about that). The table is named taErrorCode.

It is NEVER a good idea to do direct UPDATEs, DELETEs, and INSERTs of GP's
tables. This bypasses all of GP's business logic and can result in MANY
problems in GP. If you're going to do that, you must be sure that you know
and understand GP's business logic and write your own code to handle it
properly. That's not to say that in some cases you can't do it. You just
better be sure of what you're doing....and test, test again, and then test
some more. The simple fact that eConnect doesn't error after the above SQL
does NOT mean things didn't get hosed in the tables and things in GP aren't
what they should be.

> The "Update manual tax" issue...
>
> If I set the tax values manually and insert with econnect it works.
> Then, if I update the same transaction with the same data (no changes) it
> fails saying it cant update manually set taxes.

This is a limitation of eConnect. It doesn't support this. From the
programmer's guide:

"Manual updates to taxes are not supported. If you attempt to manually
update taxes, an error will occur and the transaction will fail. A future
release of eConnect will include this functionality."

Don't count on a future release of eConnect supporting this. The
programmer's guide has been saying that for years and it's unlikely eConnect
ever will, particularly since MS is moving to SBA for GP integrations.
Although the future of SBA seems to be uncertain as well. I'm hoping to
learn more about this when I attend reImagine2015 in September.

> However, If generate the taxes to be the same as they would be if
> econnect generated them (following all the rules) then it will not error.
>
> SO far my best solution would be to update SQL after econnect does, and
> set the (one specific) taxes manually. However, starting to code this
> procedure I have quickly realized this is going to be a bit of a daunting
> task... Particularly updating all the totals to reflect the changed tax
> amount, then thinking about it, what if the document is fully paid, but
> the auto tax says its more than what it should be when its manual, I guess
> this will be better than the other way, econnect probably wont allow a
> document to have more payment then docamount....
>

> Is there a procedure I can run outside of GP that will automatically
> update an SOP transactions totals and distributions by actually doing the
> math?

There are, but they're not intended to be called except by eConnect;
therefore, they're not documented. And let's face it, eConnect's
documentation is not the best to begin with. A lot of learning eConnect,
particularly if you're going to call the stored procedures directly, has to
be done through trial and error. Unfortunately, all of eConnect's stored
procedures are encrypted so you can't examine the code to figure things out
unless you have a tool that can decrypt them. I've decrypted a number of
the eConnect stored procedures so have learned of some of these undocumented
procedures. For example, one is taSopHdrRecalc. This gets called in
taSopLineIvcInsert, and probably other stored procedures that could have an
affect on values in the SOP10100 table. Decrypting the stored procedures can
be useful for studying and learning, as well as debugging your own code when
eConnect reports an error and you just can't figure out why by any other
way. But never modify the stored procedure. Not only would this be a
violation of GP's license but upgrading GP re-writes all the stored
procedures so you'd lose your changes.

Mike