pixiRepairCustomerData

According to its header comment, the procedure is meant to repair strange characters for API output in customer data. The current code does not change any data. It only selects the affected records.

Steps:

If FromDate is not passed, it is set to the start of the current day (00:00:00). If ToDate is not passed, it is set to the end of the current day (23:59:59.997).
It collects all orders from OrderHeader whose CreateDate lies between FromDate and ToDate (both inclusive) and that have both a billing address (AddrRef <> 0) and a shipping address (ShipAdrRef <> 0).
It collects the billing and shipping address keys of these orders.
It opens a transaction:
- With CommitResult = 1 the empty transaction is committed. No result set is returned.
- Otherwise (0 or NULL) two result sets are returned and the transaction is rolled back:
  - the name, address and phone fields of all collected addresses from Addresses. Each address appears only once.
  - OrderNr and Shop_Note of all collected orders.

The sort order of both result sets is not defined.

Notes

Despite its name, the procedure performs no updates. The transaction contains no data changes, so CommitResult = 1 has no effect except suppressing the output. The actual repair logic appears to have been removed.
All reads use NOLOCK. Uncommitted data may therefore be returned.
Orders without a billing or shipping address (reference 0) are ignored entirely.
Large date ranges can produce large result sets.

Tables used

OrderHeader · Read Addresses · Read

Parameter Type Default Description

Parameter	Type	Default	Description
`FromDate`	datetime dateTime	NULL	Start of the order creation date range (inclusive). If NULL, the start of the current day is used. Example: `2024-05-01T00:00:00`
`ToDate`	datetime dateTime	NULL	End of the order creation date range (inclusive). If NULL, the end of the current day (23:59:59.997) is used. Example: `2024-05-01T23:59:59`
`CommitResult`	bit boolean	0	Controls whether the transaction is committed or the affected data is returned as a preview. Allowed values `0` – Preview: returns the addresses and order notes, then rolls back. `1` – Commits the (empty) transaction. No result sets are returned. Example: `0`

FromDate

datetime
dateTime

NULL

Start of the order creation date range (inclusive). If NULL, the start of the current day is used.

Example: 2024-05-01T00:00:00

ToDate

datetime
dateTime

NULL

End of the order creation date range (inclusive). If NULL, the end of the current day (23:59:59.997) is used.

Example: 2024-05-01T23:59:59

CommitResult

bit
boolean

0

Controls whether the transaction is committed or the affected data is returned as a preview.

Allowed values

0 – Preview: returns the addresses and order notes, then rolls back.
1 – Commits the (empty) transaction. No result sets are returned.

Example: 0

Result set 1 · If `CommitResult` is not 1

Billing and shipping addresses of the selected orders.

Column	Type	Description
`Anrede` Nullable	string	Salutation.
`CareOf` Nullable	string	Care-of line.
`Name` Nullable	string	Name line 1.
`Name2` Nullable	string	Name line 2.
`FirstName` Nullable	string	First name.
`LastName` Nullable	string	Last name.
`Street` Nullable	string	Street.
`HouseNr` Nullable	string	House number.
`City` Nullable	string	City.
`State` Nullable	string	State or region.
`Country` Nullable	string	Country.
`Phone` Nullable	string	Phone number.
`Phone2` Nullable	string	Second phone number.

Result set 2 · If `CommitResult` is not 1

Order numbers and shop notes of the selected orders.

Column	Type	Description
`OrderNr`	int	Order number.
`Shop_Note` Nullable	string	Note from the shop for the order.

Response example

<SqlRowSet1>
  <row>
    <Anrede>Herr</Anrede>
    <CareOf/>
    <Name>Max Mustermann</Name>
    <Name2/>
    <FirstName>Max</FirstName>
    <LastName>Mustermann</LastName>
    <Street>Hauptstraße</Street>
    <HouseNr>12</HouseNr>
    <City>Berlin</City>
    <State/>
    <Country>DE</Country>
    <Phone>030 123456</Phone>
    <Phone2/>
  </row>
</SqlRowSet1>

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:pixiRepairCustomerData>
      <!-- <FromDate>2024-05-01T00:00:00</FromDate> -->
      <!-- <ToDate>2024-05-01T23:59:59</ToDate> -->
      <!-- <CommitResult>0</CommitResult> -->
    </ns1:pixiRepairCustomerData>
  </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:pixiRepairCustomerData>
      <!-- <FromDate>2024-05-01T00:00:00</FromDate> -->
      <!-- <ToDate>2024-05-01T23:59:59</ToDate> -->
      <!-- <CommitResult>0</CommitResult> -->
    </ns1:pixiRepairCustomerData>
  </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 = [
    // 'FromDate' => '2024-05-01T00:00:00',
    // 'ToDate' => '2024-05-01T23:59:59',
    // 'CommitResult' => '0',
];

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

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

Notes

Tables used

Result set 1 · If CommitResult is not 1

Result set 2 · If CommitResult is not 1

Response example

Result set 1 · If `CommitResult` is not 1

Result set 2 · If `CommitResult` is not 1