ERPTRADINGACCOUNT table errors in 7.5.4 WEB

[RJSamp]

This is a new 1:1 extension table off of Account for the new ERP integration magic. The customer IS NOT USING THIS STUFF.

For new accounts......when they edit an Account Detail View (phone number, business description, et al) they get an error about 'dbo.ERPTRADINGACCOUNT' and inserting a record with a duplicate key ERPTRADINGACCOUNTID value (it would be the ACCOUNTID and not a NULL or blank value).

What is this field about on an extension table?

Maybe their's a load action\event\databinding floating around on one of the forms (AccountDetailsExtended???).....

I did the old TRUNCATE TABLE and INSERT INTO sysdba.ERPTRADINGACCOUNT trick to populate the table.....

[pzeimet]

Hi RJ,

I have not seen primary key violations on this table before.  I have seen performance issues when this table is not populated fully with all the account records.  Here are the scripts I used to fix the issue in Sawgrass as well as a later update on 7.5.4.  After the table has been populated does the customer still see the issue?

What updates are installed?

INSERT INTO SYSDBA.ERPTRADINGACCOUNT (ACCOUNTID, SECCODEID) SELECT ACCOUNTID, SECCODEID FROM SYSDBA.ACCOUNT WHERE ACCOUNTID NOT IN (SELECT ACCOUNTID FROM SYSDBA.ERPTRADINGACCOUNT)

INSERT INTO SYSDBA.ERPSALESORDER (SALESORDERID, SECCODEID) SELECT SALESORDERID, SECCODEID FROM SYSDBA.SALESORDER WHERE SALESORDERID NOT IN (SELECT SALESORDERID FROM SYSDBA.ERPSALESORDER)

[RJSamp]

Hot fixes 1,2,3 for the Web.....

obviously there is no dbo ownership.....and there is no ERPTRADINGACCOUNTID field to violate a key.....this is a 1:1 table off of Account.....the AccountID IS the unique key......

I'll run your scripts to fix the database....

I had written a quick one of my own to store a record and SYST00000001 as the SECCODEID.....

I understand that the conflict between Account.SeccodeID and ERPTRADINGACCOUNT.SeccodeId may be a cause of the problems.....(although the error was showing up before I ran my quick script)

.the error actually occurs when a NEW ACCOUNT is inserted.....and then fields are updated on the Account Detail View.....

I think this is related to HF 1 or 2 for the web (so that would be 2).

[pzeimet]

Thanks RJ for the additional info. 

I will have QA take a look at this and see if they can reproduce the issue.

[RJSamp]

Case #4832425 for me (and my shadow....)

[RJLedger]

This is now happening 100% of the time at one of my customer sites.. I'm looking at it right now.

A - Add a new contact to an existing account - error

B - change account details on an existing account - error

Need a fix right now - impacting all user's ability to use the system

[RJSamp]

Run the SQL statements IMMEDIATELY!

[RJLedger]

paul's statements?

[RJSamp]

Correct.....I actually did my own prior to his statement.....

where I just inserted AccountID and SeccodeId = 'SYST00000001' but supposedly that doesn't  fix the issues (customer hasn't complained yet).

[RJLedger]

We just put up a TaskCentre task that truncates the (ERP) table.

Completely wasteful to have the extra 1:1 records hanging around when we have no use for them...