Customers Read

pixiAddressesDisplayDuplicates

Returns pairs of potentially duplicate customer addresses from the duplicate check, with address, order, invoice and customer details for both sides.

Reads the results of a previous duplicate check from Addresses_Duplicates. Each record there links two addresses (AddrRef1/AddrRef2) and two customers (CustRef1/CustRef2) together with a match level and a match text.

For each pair the call returns:

match level and match text,
name, company (CareOf), street, house number, city, ZIP and country of both addresses,
the number of invoices and the date of the latest invoice (LastDelivery1/2) for each customer,
the external customer number and the DATEV account number of both customers,
order number and order status of the orders that use each address,
NrOfRecords, the total number of unused duplicate records.

Filtering:

Only records with CustRef1 greater than StartCustRef are returned. A NULL value is treated as 0.
With ShowUsed = 0 only records whose UsedFlag is 0 or NULL are returned. With ShowUsed = 1 all records are returned, including those already processed.
RowCount limits the number of returned rows through SET ROWCOUNT.

Rows are sorted by CustRef1 ascending. For paging, pass the highest returned CustRef1 as StartCustRef of the next call.

Notes

All tables are read with NOLOCK, so uncommitted data can appear.
Addresses are joined to OrderHeader with inner joins. A pair is only returned if both addresses are used by at least one Auftrag, and a pair produces one row for every combination of orders of address 1 and orders of address 2. Rows are therefore not unique per duplicate record.
Because one CustRef1 can produce several rows, a page limited by RowCount can end in the middle of a CustRef1. Continuing with that CustRef1 as StartCustRef skips the remaining rows of it.
RowCount = 0 turns the limit off (SET ROWCOUNT 0).
NrOfRecords is the same in every row. It counts all unused records whose two customers exist and ignores StartCustRef, ShowUsed and the order join, so it can differ from the number of rows that can actually be fetched.
CustKeyMatchedTo is NULL when ShowUsed = 0. Otherwise it always equals the key of customer 1 (CustRef1), because customer 1 is joined with an inner join.
The call only displays data. It does not merge customers or set UsedFlag. How Addresses_Duplicates is filled is not visible in this code.

Parameters

Parameter Type Default Description

Parameter	Type	Default	Description
`StartCustRef`	int	0	Only records with `CustRef1` greater than this value are returned. Used for paging. `NULL` is treated as `0`. Example: `0`
`ShowUsed`	bit boolean	0	Controls whether duplicate records that are already marked as used are included. Allowed values `0` – Only records with `UsedFlag` 0 or NULL; `CustKeyMatchedTo` is NULL. `1` – All records, including used ones; `CustKeyMatchedTo` is filled. Example: `0`
`RowCount`	int	100	Maximum number of rows returned (applied with `SET ROWCOUNT`). `0` means no limit. Example: `100`

StartCustRef

int

Only records with CustRef1 greater than this value are returned. Used for paging. NULL is treated as 0.

Example: 0

ShowUsed

bit
boolean

Controls whether duplicate records that are already marked as used are included.

Allowed values

0 – Only records with `UsedFlag` 0 or NULL; `CustKeyMatchedTo` is NULL.
1 – All records, including used ones; `CustKeyMatchedTo` is filled.

Example: 0

RowCount

int

100

Maximum number of rows returned (applied with SET ROWCOUNT). 0 means no limit.

Example: 100

Response

Result set 1 · Always; may be empty if no matching duplicate records exist.

One row per duplicate pair and combination of orders of both addresses, sorted by CustRef1.

Column	Type	Description
`matchlevel` Nullable	string	Match level of the duplicate check (`MatchLevel`).
`matchtext` Nullable	string	Text describing the match (`MatchText`).
`CustRef1`	int	Key of the first customer.
`AddrKey1`	int	Key of the first address.
`FirstName1` Nullable	string	First name of address 1.
`LastName1` Nullable	string	Last name of address 1.
`Company1` Nullable	string	Company / care-of of address 1 (`CareOf`).
`Street1` Nullable	string	Street of address 1.
`HouseNr1` Nullable	string	House number of address 1.
`City1` Nullable	string	City of address 1.
`ZIP1` Nullable	string	ZIP code of address 1.
`Country1` Nullable	string	Country of address 1.
`CustRef2`	int	Key of the second customer.
`AddrKey2`	int	Key of the second address.
`FirstName2` Nullable	string	First name of address 2.
`LastName2` Nullable	string	Last name of address 2.
`Company2` Nullable	string	Company / care-of of address 2 (`CareOf`).
`Street2` Nullable	string	Street of address 2.
`HouseNr2` Nullable	string	House number of address 2.
`City2` Nullable	string	City of address 2.
`ZIP2` Nullable	string	ZIP code of address 2.
`Country2` Nullable	string	Country of address 2.
`NrOfInvoices1`	int	Number of invoices of customer 1.
`NrOfInvoices2`	int	Number of invoices of customer 2.
`LastDelivery1` Nullable	dateTime	Latest invoice date (`MAX(InvDate)`) of customer 1; NULL if there is no invoice.
`LastDelivery2` Nullable	dateTime	Latest invoice date (`MAX(InvDate)`) of customer 2; NULL if there is no invoice.
`CustomerNrExternal1` Nullable	string	External customer number of customer 1.
`CustomerNrExternal2` Nullable	string	External customer number of customer 2.
`NrOfRecords`	int	Total number of unused duplicate records whose customers exist; same value in every row.
`CustKeyMatchedTo` Nullable	int	NULL if `ShowUsed` = 0, otherwise the key of customer 1.
`OrderNr1` Nullable	int	Order number of an Auftrag that uses address 1.
`OrderStatus1` Nullable	string	Status of that order.
`OrderNr2` Nullable	int	Order number of an Auftrag that uses address 2.
`OrderStatus2` Nullable	string	Status of that order.
`DatevUserAccount1` Nullable	varchar(10) string	DATEV account number of customer 1 (`DatevAccNr`).
`DatevUserAccount2` Nullable	varchar(10) string	DATEV account number of customer 2 (`DatevAccNr`).

Response example

<SqlRowSet1>
  <row>
    <matchlevel>1</matchlevel>
    <matchtext>Name, Street, ZIP</matchtext>
    <CustRef1>1024</CustRef1>
    <AddrKey1>5511</AddrKey1>
    <FirstName1>Anna</FirstName1>
    <LastName1>Müller</LastName1>
    <Company1/>
    <Street1>Hauptstraße</Street1>
    <HouseNr1>12</HouseNr1>
    <City1>Berlin</City1>
    <ZIP1>10115</ZIP1>
    <Country1>DE</Country1>
    <CustRef2>2048</CustRef2>
    <AddrKey2>8830</AddrKey2>
    <FirstName2>Anna</FirstName2>
    <LastName2>Mueller</LastName2>
    <Company2/>
    <Street2>Hauptstr.</Street2>
    <HouseNr2>12</HouseNr2>
    <City2>Berlin</City2>
    <ZIP2>10115</ZIP2>
    <Country2>DE</Country2>
    <NrOfInvoices1>3</NrOfInvoices1>
    <NrOfInvoices2>1</NrOfInvoices2>
    <LastDelivery1>2024-03-11T10:15:00</LastDelivery1>
    <LastDelivery2>2024-05-02T08:40:00</LastDelivery2>
    <CustomerNrExternal1>SHOP-1024</CustomerNrExternal1>
    <CustomerNrExternal2>SHOP-2048</CustomerNrExternal2>
    <NrOfRecords>57</NrOfRecords>
    <OrderNr1>300112</OrderNr1>
    <OrderStatus1>AU</OrderStatus1>
    <OrderNr2>300987</OrderNr2>
    <OrderStatus2>AU</OrderStatus2>
    <DatevUserAccount1>10024</DatevUserAccount1>
    <DatevUserAccount2>10048</DatevUserAccount2>
  </row>
</SqlRowSet1>

Examples

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:pixiAddressesDisplayDuplicates>
      <!-- <StartCustRef>0</StartCustRef> -->
      <!-- <ShowUsed>0</ShowUsed> -->
      <!-- <RowCount>100</RowCount> -->
    </ns1:pixiAddressesDisplayDuplicates>
  </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:pixiAddressesDisplayDuplicates>
      <!-- <StartCustRef>0</StartCustRef> -->
      <!-- <ShowUsed>0</ShowUsed> -->
      <!-- <RowCount>100</RowCount> -->
    </ns1:pixiAddressesDisplayDuplicates>
  </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 = [
    // 'StartCustRef' => '0',
    // 'ShowUsed' => '0',
    // 'RowCount' => '100',
];

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

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

Tables used

Addresses_Duplicates · Read Addresses · Read OrderHeader · Read Customers · Read Invoices · Read

SQL object:: dbo.pipiAddressesDisplayDuplicates