Updating business partner addresses from an SQL call

[Former Member]

I am working with a customer who is writing an integration scenario in B1iF to bring data in from SQL to either update or insert a business partner, business partner addresses, etc.  The scenario seems to be working sporadically at best.  Occasionally we get the error message "Exception: DI Error (-2035) This entry already exists in the following tables table (ODBC -2035)"  He also has set the Outbound channel to B1, synchronous update to fall back with insert, and CardCode as the key name.

Why would this error suddenly appear with all of those things done?

He has basically structured his call as shown below.  Is there a problem with trying to update addresses like this?  Would we need something like a linenum to update addresses in case an address such as "Premises" already exists and is linked to the business partner?

<b1im_multimsg xmlns="">

  <xsl:for-each select="/vpf:Msg/vpf:Body/vpf:Payload[./@Role=&apos;C&apos; and ./@id=&apos;atom2&apos;]/jdbc:ResultSet/jdbc:Row">

   <b1im_msg>

    <B1out type="object">

     <BusinessPartners>

      <row>

       <CardCode><xsl:value-of select="jdbc:CardCode/text()"></xsl:value-of></CardCode>

       <CardName><xsl:value-of select="jdbc:CardName/text()"></xsl:value-of></CardName>

       <Series>1</Series>

       <CardType><xsl:value-of select="jdbc:CardType/text()"></xsl:value-of></CardType>

       <GroupCode><xsl:value-of select="jdbc:GroupCode/text()"></xsl:value-of></GroupCode>

       <Address><xsl:value-of select="jdbc:Address/text()"></xsl:value-of></Address>

       <ZipCode><xsl:value-of select="jdbc:ZipCode/text()"></xsl:value-of></ZipCode>

       <MailAddress><xsl:value-of select="jdbc:MailAddress/text()"></xsl:value-of></MailAddress>

       <MailZipCode><xsl:value-of select="jdbc:MailZipCode/text()"></xsl:value-of></MailZipCode>

       <Phone1><xsl:value-of select="jdbc:Phone1/text()"></xsl:value-of></Phone1>

       <Phone2><xsl:value-of select="jdbc:Phone2/text()"></xsl:value-of></Phone2>

       <ContactPerson><xsl:value-of select="jdbc:CntctPrsn/text()"></xsl:value-of></ContactPerson>

       <PayTermsGrpCode><xsl:value-of select="jdbc:PayTermsGrpCode/text()"></xsl:value-of></PayTermsGrpCode>

       <PriceListNum><xsl:value-of select="jdbc:PriceListNum/text()"></xsl:value-of></PriceListNum>

       <City><xsl:value-of select="jdbc:City/text()"></xsl:value-of></City>

       <County><xsl:value-of select="jdbc:County/text()"></xsl:value-of></County>

       <Country><xsl:value-of select="jdbc:Country/text()"></xsl:value-of></Country>

       <MailCity><xsl:value-of select="jdbc:MailCity/text()"></xsl:value-of></MailCity>

       <MailCounty><xsl:value-of select="jdbc:MailCounty/text()"></xsl:value-of></MailCounty>

       <MailCountry><xsl:value-of select="jdbc:MailCountry/text()"></xsl:value-of></MailCountry>

       <DebitorAccount><xsl:value-of select="jdbc:DebitorAccount/text()"></xsl:value-of></DebitorAccount>

       <BillToState><xsl:value-of select="jdbc:BillToState/text()"></xsl:value-of></BillToState>

       <ExemptNum><xsl:value-of select="jdbc:ExemptNo/text()"></xsl:value-of></ExemptNum>

       <BilltoDefault>Billing</BilltoDefault>

       <ShipToDefault>Premises</ShipToDefault>

       <U_acServiceType><xsl:value-of select="jdbc:U_acServiceType/text()"></xsl:value-of></U_acServiceType>

       <U_acBranch><xsl:value-of select="jdbc:U_acBranch/text()"></xsl:value-of></U_acBranch>

       <U_acGuardianCo><xsl:value-of select="jdbc:U_acGuardianCo/text()"></xsl:value-of></U_acGuardianCo>

       <U_acRegion><xsl:value-of select="jdbc:U_acRegion/text()"></xsl:value-of></U_acRegion>

       <U_acRefCustomer><xsl:value-of select="jdbc:U_acRefCustomer/text()"></xsl:value-of></U_acRefCustomer>

       <U_acResRefCustomer><xsl:value-of select="jdbc:U_acComRefCustomer/text()"></xsl:value-of></U_acResRefCustomer>

       <U_acInfoSource><xsl:value-of select="jdbc:U_acInfoSource/text()"></xsl:value-of></U_acInfoSource>

       <U_acIntRange><xsl:value-of select="jdbc:U_acIntRange/text()"></xsl:value-of></U_acIntRange>

       <U_acSiteScenario><xsl:value-of select="jdbc:U_acSiteScenario/text()"></xsl:value-of></U_acSiteScenario>

       <U_acBPRole><xsl:value-of select="jdbc:U_acBPRole/text()"></xsl:value-of></U_acBPRole>

       <U_acFranchCode><xsl:value-of select="jdbc:U_acFranchCode/text()"></xsl:value-of></U_acFranchCode>

       <U_acFranchName><xsl:value-of select="jdbc:U_acFranchName/text()"></xsl:value-of></U_acFranchName>

       <U_acLegacyCust><xsl:value-of select="jdbc:U_acLegacyCust/text()"></xsl:value-of></U_acLegacyCust>

       <U_acLegacySite><xsl:value-of select="jdbc:U_acLegacySite/text()"></xsl:value-of></U_acLegacySite>

       <U_acFranchiseCode><xsl:value-of select="jdbc:U_acFranchiseCode/text()"></xsl:value-of></U_acFranchiseCode>

       <U_acFranchiseName><xsl:value-of select="jdbc:U_acFranchiseName/text()"></xsl:value-of></U_acFranchiseName>

       <U_acCreditScore><xsl:value-of select="jdbc:U_acCreditScore/text()"></xsl:value-of></U_acCreditScore>

       <U_acSiteID><xsl:value-of select="jdbc:U_acSiteID/text()"></xsl:value-of></U_acSiteID>

       <Valid><xsl:value-of select="jdbc:Valid/text()"></xsl:value-of></Valid>

       <Frozen><xsl:value-of select="jdbc:Frozen/text()"></xsl:value-of></Frozen>

       <U_acLastUpdSys><xsl:value-of select="jdbc:U_acLastUpdSys/text()"></xsl:value-of></U_acLastUpdSys>

       <U_acLastUpdStamp><xsl:value-of select="jdbc:U_acLastUpdStamp/text()"></xsl:value-of></U_acLastUpdStamp>

      </row>

     </BusinessPartners>

     <BPAddresses>

      <row>

<AddressName>Premises</AddressName>

<Street><xsl:value-of select="jdbc:Address/text()"></xsl:value-of></Street>

<ZipCode><xsl:value-of select="jdbc:ZipCode/text()"></xsl:value-of></ZipCode>

<City><xsl:value-of select="jdbc:City/text()"></xsl:value-of></City>

<County><xsl:value-of select="jdbc:County/text()"></xsl:value-of></County>

<Country><xsl:value-of select="jdbc:Country/text()"></xsl:value-of></Country>

<State><xsl:value-of select="jdbc:BillToState/text()"></xsl:value-of></State>

<TaxCode><xsl:value-of select="jdbc:TaxCode/text()"></xsl:value-of></TaxCode>

<AddressType>bo_ShipTo</AddressType>

<StreetNo></StreetNo>

      </row>

      <row>

<AddressName>Billing</AddressName>

<Street><xsl:value-of select="jdbc:Address/text()"></xsl:value-of></Street>

<ZipCode><xsl:value-of select="jdbc:ZipCode/text()"></xsl:value-of></ZipCode>

<City><xsl:value-of select="jdbc:City/text()"></xsl:value-of></City>

<County><xsl:value-of select="jdbc:County/text()"></xsl:value-of></County>

<Country><xsl:value-of select="jdbc:Country/text()"></xsl:value-of></Country>

<State><xsl:value-of select="jdbc:BillToState/text()"></xsl:value-of></State>

<TaxCode><xsl:value-of select="jdbc:TaxCode/text()"></xsl:value-of></TaxCode>

<AddressType>bo_BillTo</AddressType>

<StreetNo></StreetNo>

      </row>

     </BPAddresses>

    </B1out>

   </b1im_msg>

  </xsl:for-each>

</b1im_multimsg>

[Former Member · Accepted solution]

Hi Kevin,

I recommend you to use the method: UpdateWithSubdeletion on the B1 call for updating the business partner. This is the recommended way to update business partners with addresses.

In order to also maintain the already existing address you should take care of merging the Updated and New addresses with the already existing ones.

[Former Member · Accepted solution]

Hi Jereon,

Thank you for your reply.  I'm a little unclear as to what you might mean by "to maintain the already existing address you should take care of merging and updated and new addresses with the already existing ones."

Is there a better way to maintain addresses on the business partner in B1iF?  Maybe a line number column we need to use on update?

I'll definitely try that first bit but I'm really curious about this last bit of info as well.  Is there something we're missing in terms of maintaining our addresses that should be added to this atom or the scenario?

Kevin Ly

kly@aetherconsulting.com

[Former Member · Accepted solution]

Hi Kevin,

updateWithSubdeletion will always remove all the existing addresses on the BP and then only adds the addresses specified by your payload.

So if there are any others addresses, outside of the update payload, that should be maintained on the BP,you should add them to your payload.

One way to manage this is to retrieve the addresses by a SQL Query and rebuild them from the ground up. There is a column "LineNum" on the CRD1 table, which will always be unique for a single BP. Even if addresses get deleted or added.

If you need any further help, let me know.

[Former Member · Accepted solution]

Thank you Jeroen, that appears to have done the trick.

[Former Member · Accepted solution]

Hi,

i have the same Problem  but I can't find "UpdateWithSubdeletion" in the Method of my Outbound Step Definiton. Is this only possible with a Call-Atom or how can I achieve this with normal DI Outbound?

Best Regards

Christian

[former_member264352 · Accepted solution]

Hi Christian,

Yes, "UpdateWithSubDeletion" can be used only in Call B1 Object, but not in the Outbound.

If you're using a newer SAP Business One version such 8.82 or 9.0, you can directly use "Synchronous Update" at the Outbound stage with the whole payload and the DI API will make the change. Before that, please refer to the note 2018182 for a potential issue.

Regards,

Bo Zhao

[Former Member · Accepted solution]

Hi Bo,

Thank you for your reply. Actually I tried with "Synchronous Upate" at the Outbound and it works well if I only like to update data from the parent object (BusinessPartners) but as soon as include a child object in my message (e.g. BPAddresses) I get an error Message:

"Error on completion of HTTPS roundtrip...."

I also tried to include the row Num of addresses in the <QueryParams> Section but with no sucsess.

Do you have another tip ?

Best Regards

Christian

[former_member264352 · Accepted solution]

Hi Christian,

Such an error (HTTPS roundtrip) suggests a payload issue. There should be something wrong with the payload. I suggest to use DI Tool or DI API (UpdateFromXML()) to update the BP with the payload you used to test. Or you can paste the payload in the forum.

Anyway, since this post is resolved, I suggest to create a new one for this if you want to discuss further. Thank you.

Regards,

Bo Zhao