Customers Read

pixiGetDatevExportAddresses

Returns customer (and optionally supplier) address data for the DATEV export file, filtered by period, shop, country, e-mail, DATEV account and cost center.

Provides the address master data (debtors and optionally creditors) for a DATEV export.

The call first checks the setting BookExportDatevActive. If it is not 1, only a single error row (Status = ERROR) is returned.
Otherwise, all customers are selected that have at least one Rechnung with InvDate or at least one order line with OrderDate in the period @DateFrom (inclusive) to @DateTo (exclusive). The date values are used exactly as passed, including the time portion.
For these customers, the main address of the customer is returned, together with shop and country data. Optional filters (each ignored when NULL):
- ShopId: shop of the address
- CostCenter: cost center of the shop (an empty string counts as NULL)
- Country: Pixi country code of the address
- Email: exact e-mail address
- DatevUserAccount: DATEV account number of the customer
If the setting BookExportDatevKreditorenActive is 1, all Lieferanten with a non-empty customer number (CustNr) are appended as creditors. For suppliers only the filters Country, Email and DatevUserAccount apply; period, shop and cost center are not considered.
Double quotes are removed from all values and semicolons are replaced by spaces, so the values can be written directly to a semicolon-separated file.

The result is sorted by CustomerNr (as text).

Notes

Read-only; all tables are read with NOLOCK, so uncommitted data may appear.
@DateTo is exclusive and no start/end-of-day adjustment is applied. To export a full day, pass the following day at 00:00 as @DateTo.
Settings that change the output (a missing setting counts as 0):
- BookExportDatevActive: must be 1, otherwise only the error row is returned.
- BookExportDatevKreditorenActive: appends suppliers as creditors.
- BookUseISOCntCode: ZipOld is prefixed with the ISO3 country code instead of the Pixi country code (for suppliers the ISO3 code is prepended in addition to the Pixi code).
- RemoveNonASCII: removes non-ASCII characters from City.
Customers whose address country is not found in the country table, or whose address shop does not exist, are not returned (inner joins).
The internal procedure can resolve a shop short name longer than 3 characters, but this call declares ShopId as varchar(3), so only the 3-character shop ID can be used.
Company contains the full c/o field of the address, Name2 the same field truncated to 40 characters. ShortCode is always empty, Empty1 is always an empty string.
Supplier rows can appear without a country match; then Country defaults to D, CountryISO3 to DEU, CountryISO2 to DE and CountryKFZ to D, but such rows are excluded as soon as Country is filtered.

Parameters

Parameter	Type	Default	Description
`DateFrom` Required	datetime dateTime	–	Start of the period (inclusive) for invoice and order dates used to select customers. Example: `2024-01-01T00:00:00`
`DateTo` Required	datetime dateTime	–	End of the period (exclusive) for invoice and order dates. Example: `2024-02-01T00:00:00`
`ShopId`	varchar(3) string	NULL	Optional shop ID; only customers whose address belongs to this shop. Not applied to suppliers. Example: `001`
`Country`	varchar(3) string	NULL	Optional Pixi country code of the address (customer address or supplier country). Example: `D`
`Email`	varchar(60) string	NULL	Optional exact e-mail address of the customer address or supplier. Example: `max.mustermann@example.com`
`DatevUserAccount`	varchar(10) string	NULL	Optional DATEV account number: customer DATEV account number or supplier customer number (first 10 characters). Example: `10001`
`CostCenter`	varchar(8) string	NULL	Optional cost center of the shop; an empty string is treated as no filter. Not applied to suppliers. Example: `1000`

Response

Result set 1 · If setting `BookExportDatevActive` = 1

Address rows for the DATEV export (customers, optionally suppliers), sorted by CustomerNr.

Column	Type	Description
`CustomerNr` Nullable	varchar(10) string	DATEV account number of the customer or customer number of the supplier.
`Name` Nullable	varchar(40) string	Name of the address or supplier name, max. 40 characters.
`Name2` Nullable	varchar(40) string	c/o field of the address or second supplier name, max. 40 characters.
`Company` Nullable	varchar(60) string	c/o field of the address; empty for suppliers.
`FirstName` Nullable	varchar(40) string	First name; empty for suppliers.
`LastName` Nullable	varchar(40) string	Last name; empty for suppliers.
`ShortCode` Nullable	int	Placeholder column, always NULL.
`VatId` Nullable	varchar(20) string	VAT ID of the address; empty for suppliers.
`Street` Nullable	varchar(30) string	Street, max. 30 characters.
`Empty1`	varchar(1) string	Placeholder column, always an empty string.
`ZipOld` Nullable	varchar(20) string	Zip code with country prefix (e.g. `D-12345`, or ISO3 prefix depending on `BookUseISOCntCode`).
`Zip` Nullable	varchar(10) string	Zip code.
`Country` Nullable	varchar(3) string	Pixi country code.
`CountryISO3` Nullable	varchar(3) string	ISO 3166 alpha-3 country code.
`CountryISO2` Nullable	varchar(2) string	ISO 3166 alpha-2 country code.
`CountryKFZ` Nullable	varchar(60) string	Vehicle country code / country name from the country table (`EasyLogCntName`).
`City` Nullable	varchar(30) string	City, max. 30 characters.
`Phone` Nullable	varchar(60) string	Phone number.
`Fax` Nullable	varchar(60) string	Fax number.
`eMail` Nullable	varchar(60) string	E-mail address.
`CostCenter` Nullable	varchar(8) string	Cost center of the shop; NULL for suppliers.

Result set 2 · If setting `BookExportDatevActive` is not 1

Single error row.

Column	Type	Description
`Status`	varchar(5) string	Always `ERROR`.
`StatusMessage`	varchar(65) string	Error text.

Response example

<SqlRowSet1>
  <row>
    <CustomerNr>10001</CustomerNr>
    <Name>Max Mustermann</Name>
    <Name2/>
    <Company/>
    <FirstName>Max</FirstName>
    <LastName>Mustermann</LastName>
    <VatId/>
    <Street>Musterstr. 1</Street>
    <Empty1/>
    <ZipOld>D-12345</ZipOld>
    <Zip>12345</Zip>
    <Country>D</Country>
    <CountryISO3>DEU</CountryISO3>
    <CountryISO2>DE</CountryISO2>
    <CountryKFZ>D</CountryKFZ>
    <City>Berlin</City>
    <Phone>030 123456</Phone>
    <Fax/>
    <eMail>max.mustermann@example.com</eMail>
    <CostCenter>1000</CostCenter>
  </row>
</SqlRowSet1>

Error messages

Status	Message	Cause
ERROR	Setting "DATEV API Calls" (BookExportDatevActive) is not enabled.	The setting `BookExportDatevActive` is missing or not set to 1. Enable "DATEV API Calls" in the Pixi settings.

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:pixiGetDatevExportAddresses>
      <DateFrom>2024-01-01T00:00:00</DateFrom>
      <DateTo>2024-02-01T00:00:00</DateTo>
      <!-- <ShopId>001</ShopId> -->
      <!-- <Country>D</Country> -->
      <!-- <Email>max.mustermann@example.com</Email> -->
      <!-- <DatevUserAccount>10001</DatevUserAccount> -->
      <!-- <CostCenter>1000</CostCenter> -->
    </ns1:pixiGetDatevExportAddresses>
  </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:pixiGetDatevExportAddresses>
      <DateFrom>2024-01-01T00:00:00</DateFrom>
      <DateTo>2024-02-01T00:00:00</DateTo>
      <!-- <ShopId>001</ShopId> -->
      <!-- <Country>D</Country> -->
      <!-- <Email>max.mustermann@example.com</Email> -->
      <!-- <DatevUserAccount>10001</DatevUserAccount> -->
      <!-- <CostCenter>1000</CostCenter> -->
    </ns1:pixiGetDatevExportAddresses>
  </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 = [
    'DateFrom' => '2024-01-01T00:00:00',
    'DateTo' => '2024-02-01T00:00:00',
    // 'ShopId' => '001',
    // 'Country' => 'D',
    // 'Email' => 'max.mustermann@example.com',
    // 'DatevUserAccount' => '10001',
    // 'CostCenter' => '1000',
];

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

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

Tables used

Settings · Read Shops · Read Invoices · Read Orderlines · Read Customers · Read Addresses · Read Countries · Read Suppliers · Read

SQL object:: dbo.pipiGetDatevExportAddresses