Customers Read

pixiReadCustomers

Returns customer master records from the `Customers` table, either one customer by key or a page filtered by create/update date ranges.

Reads customer master data from Customers. Every branch returns the same column list. The parameters select exactly one branch, checked in this order:

Create or update date: if CreateAndUpdateDateFrom or CreateAndUpdateDateTo is set, customers with CustKey > @CustKey are returned whose CreateDate or UpdateDate is >= From and whose CreateDate or UpdateDate is < To. Each bound is ignored if it is NULL.
Create date: otherwise, if CreateDateFrom or CreateDateTo is set, customers with CustKey > @CustKey and CreateDate >= From and CreateDate < To are returned.
Update date: otherwise, if UpdateDateFrom or UpdateDateTo is set, customers with CustKey > @CustKey and UpdateDate >= From and UpdateDate < To are returned.
Key range: otherwise, if RowCount is passed (any value, including 0), all customers with CustKey > @CustKey are returned.
Single customer: otherwise the customer with CustKey = @CustKey is returned.

If CustKey is not passed, it is set to 0. In the date and key range branches this means "start at the beginning". In the single customer branch it means a lookup of key 0, which normally returns no row.

The lower date bounds are inclusive and the upper bounds are exclusive. Branches 1–4 are sorted by CustKey ascending. If RowCount is not 0, the number of rows is limited with SET ROWCOUNT.

Paging: pass the last CustKey of the previous page as CustKey together with RowCount.

Notes

In the date branches CustKey is an exclusive lower bound, not an exact match. Passing a customer key together with a date filter returns the customers after that key.
Only one date filter group is used. CreateAndUpdateDate* takes precedence over CreateDate*, which takes precedence over UpdateDate*. If more groups are passed, the others are ignored.
In the combined filter the upper bound is also an OR condition. A customer created before CreateAndUpdateDateTo matches even if it was last updated after that date, and the other way round.
RowCount = 0 selects the key range branch but does not limit the rows, so all customers after CustKey are returned.
The table is read WITH (NOLOCK), so uncommitted data can be returned.
The result contains sensitive data such as Password, credit card fields (CardNr, CardExp) and bank details (Account, BLZ). Handle it accordingly.
The final "no data" branch (WHERE 1 = 0) cannot be reached, because CustKey is never NULL after the default replacement.
Column data types are not visible in the procedure. They come from the Customers table.

Tables used

Customers · Read

Parameter	Type	Default	Description
`CustKey`	int	NULL	Customer key. Exact match in the single customer branch. Exclusive lower bound (`CustKey > value`) in the date and key range branches. NULL is treated as 0. Example: `10234`
`CreateDateFrom`	datetime dateTime	NULL	Inclusive lower bound for `CreateDate`. Used only if no `CreateAndUpdateDate*` parameter is set. Example: `2024-01-01T00:00:00`
`CreateDateTo`	datetime dateTime	NULL	Exclusive upper bound for `CreateDate`. Used only if no `CreateAndUpdateDate*` parameter is set. Example: `2024-02-01T00:00:00`
`UpdateDateFrom`	datetime dateTime	NULL	Inclusive lower bound for `UpdateDate`. Used only if neither `CreateAndUpdateDate` nor `CreateDate` parameters are set. Example: `2024-03-01T00:00:00`
`UpdateDateTo`	datetime dateTime	NULL	Exclusive upper bound for `UpdateDate`. Used only if neither `CreateAndUpdateDate` nor `CreateDate` parameters are set. Example: `2024-03-02T00:00:00`
`CreateAndUpdateDateFrom`	datetime dateTime	NULL	Inclusive lower bound that matches if `CreateDate` or `UpdateDate` is on or after this value. Takes precedence over all other date parameters. Example: `2024-03-01T00:00:00`
`CreateAndUpdateDateTo`	datetime dateTime	NULL	Exclusive upper bound that matches if `CreateDate` or `UpdateDate` is before this value. Takes precedence over all other date parameters. Example: `2024-03-02T00:00:00`
`RowCount`	int	NULL	Maximum number of rows (via `SET ROWCOUNT`). 0 or NULL means no limit. Any non-NULL value without date filters selects the key range branch (`CustKey > @CustKey`). Example: `500`

Result set 1 · Always. Rows depend on the selected branch; the single customer lookup returns at most one row.

Customer master records from Customers. In the range branches the rows are sorted by CustKey ascending.

Column	Type	Description
`CustKey`	int	Internal customer key.
`AddrRef` Nullable	string	Reference to the address record.
`UserName` Nullable	string	Login user name.
`Password` Nullable	string	Login password.
`BtxNr` Nullable	string	BTX number (legacy field).
`DLocked` Nullable	string	Lock flag `DLocked`.
`ShipRef` Nullable	string	Shipping reference.
`CardType` Nullable	string	Credit card type.
`CardName` Nullable	string	Credit card holder name.
`CardNr` Nullable	string	Credit card number.
`CardExp` Nullable	string	Credit card expiry date.
`Account` Nullable	string	Bank account number.
`BLZ` Nullable	string	Bank code (BLZ).
`BankName` Nullable	string	Bank name.
`AccName` Nullable	string	Account holder.
`WaitDays` Nullable	int	Waiting days.
`MinOrderQty` Nullable	decimal	Minimum order quantity.
`LSType` Nullable	string	Delivery note type.
`Discountable` Nullable	string	Flag: discount allowed.
`NoInvoice` Nullable	string	Flag: no invoice.
`NextShipImmed` Nullable	string	Flag: ship next order immediately.
`SLocked` Nullable	string	Lock flag `SLocked`.
`eMailInfo` Nullable	string	Flag for email information.
`Payment` Nullable	string	Payment method.
`UseShipAddr` Nullable	string	Flag: use separate shipping address.
`SAnrede` Nullable	string	Shipping address: salutation.
`SName` Nullable	string	Shipping address: name.
`SName2` Nullable	string	Shipping address: name line 2.
`SCareOf` Nullable	string	Shipping address: care of.
`SAddress` Nullable	string	Shipping address: street line.
`SCity` Nullable	string	Shipping address: city.
`SState` Nullable	string	Shipping address: state.
`SZIP` Nullable	string	Shipping address: ZIP code.
`SCountry` Nullable	string	Shipping address: country.
`SZIPMB` Nullable	string	Shipping address: ZIP code of the mailbox.
`SMailbox` Nullable	string	Shipping address: mailbox.
`WaitHour` Nullable	int	Waiting time, hours part.
`WaitMinute` Nullable	int	Waiting time, minutes part.
`CustomerOf` Nullable	string	Shop or client the customer belongs to.
`CreateDate` Nullable	dateTime	Creation timestamp.
`CreateEmp` Nullable	string	Employee who created the record.
`UpdateDate` Nullable	dateTime	Last update timestamp.
`UpdateEmp` Nullable	string	Employee who last updated the record.
`Discount` Nullable	decimal	Discount.
`CustomerNrExternal` Nullable	string	External customer number, e.g. from the shop.
`BulkShip` Nullable	string	Flag for bulk/collective shipping.
`IssueNr` Nullable	string	Card issue number.
`CeMail` Nullable	string	Customer email address.
`CAddrNr` Nullable	string	Customer address number.
`CLangCode` Nullable	string	Customer language code.
`CNote` Nullable	string	Customer note.
`CHisNote` Nullable	string	Customer history note.
`SFirstName` Nullable	string	Shipping address: first name.
`SLastName` Nullable	string	Shipping address: last name.
`SStreet` Nullable	string	Shipping address: street.
`SHouseNr` Nullable	string	Shipping address: house number.
`TaxOnInvoice` Nullable	string	Flag: show tax on invoice.
`NoShipCosts` Nullable	string	Flag: no shipping costs.
`ZZSkontoProzent` Nullable	decimal	Payment terms: cash discount percentage.
`ZZSkonto` Nullable	int	Payment terms: cash discount days.
`ZZNetto` Nullable	int	Payment terms: net days.
`CFax` Nullable	string	Customer fax number.
`CPhone` Nullable	string	Customer phone number.
`VATID` Nullable	string	VAT identification number.
`Rabat` Nullable	decimal	Rebate.
`POSCustomer` Nullable	string	Flag: point-of-sale customer.
`ReminderAction` Nullable	string	Dunning action.
`DaysForFirstReminder` Nullable	int	Days until the first reminder.
`DaysForNextReminder` Nullable	int	Days until the next reminder.
`ReminderCost` Nullable	decimal	Reminder fee.
`ReminderDueDays` Nullable	int	Due days of a reminder.
`InvoiceDueDays` Nullable	int	Due days of an invoice.
`Person_Id` Nullable	string	Reference to the person record.

Response example

<SqlRowSet1>
  <row>
    <CustKey>10235</CustKey>
    <UserName>max.mustermann</UserName>
    <SName>Max Mustermann</SName>
    <SStreet>Hauptstraße</SStreet>
    <SHouseNr>12</SHouseNr>
    <SZIP>80331</SZIP>
    <SCity>München</SCity>
    <SCountry>DE</SCountry>
    <CeMail>max@example.com</CeMail>
    <CustomerNrExternal>SHOP-4711</CustomerNrExternal>
    <CreateDate>2024-03-01T10:15:00</CreateDate>
    <UpdateDate>2024-03-01T12:00:00</UpdateDate>
  </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:pixiReadCustomers>
      <!-- <CustKey>10234</CustKey> -->
      <!-- <CreateDateFrom>2024-01-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-02-01T00:00:00</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-03-01T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-03-02T00:00:00</UpdateDateTo> -->
      <!-- <CreateAndUpdateDateFrom>2024-03-01T00:00:00</CreateAndUpdateDateFrom> -->
      <!-- <CreateAndUpdateDateTo>2024-03-02T00:00:00</CreateAndUpdateDateTo> -->
      <!-- <RowCount>500</RowCount> -->
    </ns1:pixiReadCustomers>
  </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:pixiReadCustomers>
      <!-- <CustKey>10234</CustKey> -->
      <!-- <CreateDateFrom>2024-01-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-02-01T00:00:00</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-03-01T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-03-02T00:00:00</UpdateDateTo> -->
      <!-- <CreateAndUpdateDateFrom>2024-03-01T00:00:00</CreateAndUpdateDateFrom> -->
      <!-- <CreateAndUpdateDateTo>2024-03-02T00:00:00</CreateAndUpdateDateTo> -->
      <!-- <RowCount>500</RowCount> -->
    </ns1:pixiReadCustomers>
  </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 = [
    // 'CustKey' => '10234',
    // 'CreateDateFrom' => '2024-01-01T00:00:00',
    // 'CreateDateTo' => '2024-02-01T00:00:00',
    // 'UpdateDateFrom' => '2024-03-01T00:00:00',
    // 'UpdateDateTo' => '2024-03-02T00:00:00',
    // 'CreateAndUpdateDateFrom' => '2024-03-01T00:00:00',
    // 'CreateAndUpdateDateTo' => '2024-03-02T00:00:00',
    // 'RowCount' => '500',
];

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

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

SQL object:: dbo.pipiReadCustomers