pixiMergeCustomers · Pixi API documentation

Moves everything that belongs to customer CustRefFrom to customer CustRefTo, then deletes CustRefFrom.

Validation. The checks run in this order. The first one that fails ends the call with ReturnCode = ERROR:

CustRefTo and CustRefFrom must differ.
The pair must be listed in Addresses_Duplicates. Either direction counts (CustRef1/CustRef2).
Both customers must exist in Customers.
Neither customer may have an order with an OrderHeaderAttribute of type 3 (archived orders).
The base currencies (Shops.BaseCurrencyId) of both customers' shops (Customers.CustomerOf) must be the same.

Merge. Inside a transaction, the customer reference is changed from CustRefFrom to CustRefTo in these tables:

orders and order lines: OrderHeader, Orderlines, BundleOrderlines, OrderHistory
invoices and reminders: Invoices, Invlines, InvoicesDeleted, InvlinesDeleted, Reminders
interface tables: C_Int_OrderHeader, C_Int_Orderlines, C_Int_Addresses
addresses and history: Addresses, AddressesHist, CustomersHist
customer account: CustomerAccount, CustomerAccountLog, CustomerAccountManualBookings, CustomerAccountManualBookingsLog
payments and direct debits: CCPTranLog, DTAUS, DTAUSLog
other: Voucher, eMaillogs, AM_Mail, AM_MailHist, Boxes, pixi.Offer, lastsdetail

In Addresses and C_Int_Addresses, CustomerNrExternal is also set to the external customer number of the target customer.

After that, the duplicate entries of CustRefFrom are removed from Addresses_Duplicates and the source row is deleted from Customers.

Finish. This step always runs, also after a failed validation or an error. Rows with CustRefFrom are deleted from Addresses_Duplicates and tmp.Addresses_Simple, and an event is logged. One status row with ReturnCode and ErrorMessage is returned.

Notes

Pitfall: the final cleanup always runs. All Addresses_Duplicates and tmp.Addresses_Simple entries of CustRefFrom are deleted even when validation fails. If both parameters are equal, this removes the duplicate entries of that customer.
If an outer transaction is open, the call uses a savepoint and does not commit. Otherwise it opens and commits its own transaction. On error it rolls back, logs the error via log.EventInsert and log.InsertError, and returns Unexpected error: "…" with the error text cut to 80 characters.
The final cleanup and logging run after the merge transaction has been committed, or inside the caller's transaction if one is open.
XACT_ABORT is on.
CONTEXT_INFO is set to several values during the merge to bypass triggers on OrderHeader, Addresses, CCPTranLog, Invoices, Invlines, Orderlines and CustomerAccount. The triggers are not attached, so their skipped logic is unknown.
After the merge, the code tries to refresh the customer account sums with a dummy update on CustomerAccount. The update filters on CustRefFrom, whose rows have already been moved, so it probably affects no row.
If one of the two customers has no matching shop, the currency is NULL and the currency check does not stop the merge.
Validation reads use NOLOCK. The rows to be moved are collected with UPDLOCK.
The tables written by the logging procedures log.EventInsert and log.InsertError are not visible because their definitions are not attached.
The call is irreversible: the source customer is deleted.

Tables used

Addresses_Duplicates · Write Customers · Write Shops · Read OrderHeader · Write OrderHeaderAttribute · Read Voucher · Write CustomersHist · Write AddressesHist · Write Addresses · Write CCPTranLog · Write C_Int_Addresses · Write C_Int_Orderlines · Write C_Int_OrderHeader · Write Invlines · Write Invoices · Write InvoicesDeleted · Write InvlinesDeleted · Write Reminders · Write BundleOrderlines · Write OrderHistory · Write Orderlines · Write eMaillogs · Write CustomerAccountManualBookingsLog · Write CustomerAccountLog · Write CustomerAccount · Write CustomerAccountManualBookings · Write DTAUSLog · Write DTAUS · Write AM_MailHist · Write AM_Mail · Write Boxes · Write Offer · Write lastsdetail · Write Addresses_Simple · Write

Parameter	Type	Default	Description
`CustRefTo` Required	int	–	Customer key (`Customers.CustKey`) of the target customer that remains and receives all data. Example: `10452`
`CustRefFrom` Required	int	–	Customer key (`Customers.CustKey`) of the source customer whose data is moved and who is deleted afterwards. Example: `10987`

Result set 1 · Always, as the last statement

One status row with the result of the merge.

Column	Type	Description
`ReturnCode`	varchar(10) string	`OK` on success, otherwise `ERROR`.
`ErrorMessage`	varchar(100) string	Success or error message.

Response example

<SqlRowSet1>
  <row>
    <ReturnCode>OK</ReturnCode>
    <ErrorMessage>Both customers successfully merged</ErrorMessage>
  </row>
</SqlRowSet1>

Status	Message	Cause
ERROR	Same CustRef provided as CustRefTo and CustRefFrom parameter	Both parameters contain the same customer key. Pass two different customers.
ERROR	Customers are not on the list for merging	The pair is not listed in `Addresses_Duplicates` in either direction. Only customers detected as duplicates can be merged.
ERROR	At least one customer does not exist in pixi	One or both customer keys are not found in `Customers`.
ERROR	At least one customer has archived order(s) and cant be merged	One of the customers has an order with an `OrderHeaderAttribute` of type `3` (archived).
ERROR	Customers shop currency do not match	The shops of the two customers have different base currencies.
ERROR	Unexpected error: "{error}"	A SQL error occurred during the merge. The transaction is rolled back and the error is logged. `{error}` holds the first 80 characters of the SQL error message.

POST https://apigateway.descartes.com/tms/pixi/
Content-Type: text/xml; charset=utf-8
Authorization: Basic BASE64(USERNAME:PASSWORD)

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="https://apigateway.descartes.com/tms/pixi/">
  <SOAP-ENV:Body>
    <ns1:pixiMergeCustomers>
      <CustRefTo>10452</CustRefTo>
      <CustRefFrom>10987</CustRefFrom>
    </ns1:pixiMergeCustomers>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

curl -X POST 'https://apigateway.descartes.com/tms/pixi/' \
  --user 'USERNAME:PASSWORD' \
  --header 'Content-Type: text/xml; charset=utf-8' \
  --data-binary @- <<'XML'
<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="https://apigateway.descartes.com/tms/pixi/">
  <SOAP-ENV:Body>
    <ns1:pixiMergeCustomers>
      <CustRefTo>10452</CustRefTo>
      <CustRefFrom>10987</CustRefFrom>
    </ns1:pixiMergeCustomers>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>
XML

<?php

$soapClient = new SoapClient(null, [
    'login' => 'USERNAME',
    'password' => 'PASSWORD',
    'location' => 'https://apigateway.descartes.com/tms/pixi/',
    'uri' => 'https://apigateway.descartes.com/tms/pixi/',
]);

$arguments = [
    'CustRefTo' => '10452',
    'CustRefFrom' => '10987',
];

$soapArguments = [];
foreach ($arguments as $name => $value) {
    $soapArguments[] = new SoapVar($value, XSD_STRING, '', '', $name);
}

$response = $soapClient->__soapCall('pixiMergeCustomers', $soapArguments);