Data Collision with Salesforce Job

[Anonymous]

Talend Open Studio:Version: 6.3.1

Salesforce Soap Version 37

Hello everyone,

Last year, we had set up some Data Integration with our CRM and on an certain Job it came an Data Collision from Time2Time with Account-ID from similar Prospects and Customer Record.

The Job does Upsert our Customer Quotes from our ERP ( Infor) to Salesforce.

The unique Key is the "Quote Number".

The Innerjoin  Lookup is the Account-Number from the ERP System for referencing the Account-ID in Infor.

Account-Number ( ERP):

We have some Customers and some  and some Prospects.  The Customers are having the Number with 5  Digits and the Prospects have the same one plus an CHAR (K) at the beginning. For Example:  K12345 ( Prospect) and 12345 (Customer). The Customer get an new Record and isn't converted in any way from an Prospect.

And i think here is the pain point. 

The Error Mail ( some custom Text): 

Hello,

There is a problem.

Job SF_P_D_M_KA_v2_13_10

Mon May 22 17:34:07 CEST 2017

PROD_17_11 (Default) / tSalesforceOutput_1 Java Exception : java.io.IOException:Attempting to update (as part of an upsert) parent field Account__c with new value 0012400001BRR9VAAX, current value is 0012400000kzBuBAAU

 (1)

 Good Luck.

So the id's are referencing to an an similar Account but not identic one. Here are an short Overview of the  2 Accounts in SF which cause an Crash:

Accounts-View in Salesforce which are related to the 2 ID's: 

 The Data from the Records are the same Company but have different ( every green marked text is identic) Accouint-Numbers ( Infor FirmenNr.) as youi can see on the Picture.

The Unique ID stored in the Oracle  Database are also not Identic:

IK ( Unique ID): 

1. 217346033

2.216018014

The Problem occours not really often but from Time2Time and alway with similar Accounts from an Prospect and an Customer 

And here is my Job: 

 Whole Overview Job:

1.Whole Overview

Mapping:

The MappingThe Inner Join

Update: 

Prod:

I had make an new Job for testing based on this one but only with an Account Lookup from the certain Account's in Salesforce.

Then comes an error that i have an duplicate entry and shows the External-ID ( Quote-Number) but i havent any second Quote Number in my SF-Instance. 

Sandbox:

I mad an another test with the same Job with the 2 Account ID's on an Sandbox an here  Talend says the same that an Duplicate Exist. But this is still less the Truth,  cause on the contrary to the prod Instance there is no Record with the Quote-Number in the System  which is so-called as an Duplicate.

So i think Talend mixed up sth. in the Job itself. 

best regards john 

[Anonymous]

Hello everybody,

Atm. my Workaround is to exclude the similar Accounts from the Oracle Query so far.

best regards john

[Anonymous]

Ok it seems, that i have to solve my Problem in any way cause the following Tasks are built up on this one.

So any ideas? 

@ArvinRapt may  you have an clue or an idea for me ?

Thanks in advance

regards john