pixiGetDatevExportWires

Produces the content of the DATEV export file "Payment" (wires).

The setting BookExportDatevActive is checked. If it is not 1, only a status row with Status = ERROR is returned.
Otherwise the internal procedure Pixi_DatevExportWires is executed with the given parameters and SetStartAndEndOfDay = 0, i.e. DateFrom and DateTo are used exactly as passed, without expanding them to start/end of day.

The result combines two sources (UNION ALL):

Bank lines (BankLines)

Bank lines with BankDate between DateFrom and DateTo (inclusive). If the setting BookExportWiresWithBookDate = 1, the date is derived from the bank line's book date (MMDD plus year, with year-change correction), taken from a window of two weeks before/after the range.
Bank lines from bank headers with source type APY are always excluded; CCP only if BookExportCcpTransactions is set; UPD and business code 020 only if BookExportUpdTransactions is set.
Customer, shop and invoice are determined from the final (non-prematch) bank match: invoice, order, direct debit (DTAUS) or customer account entry, in this order.
CustomerNr is the customer's DatevAccNr, or the setting BookDefaultCustomerNr if no customer is found.
InvoiceNr is the external order number if the payment method is listed in the setting BookExportDatevUseOrderNrExternal; otherwise the invoice number (for direct debits the matched invoice). Fallback is BookDefaultInvoiceNr; the value is cut to 12 characters.
BankAccountNr is the bank line's accounting bank account (if BookExportQueryAndUseAccountingBankAccount is set), the PayPal account BookDefaultBankAccountNrPayPal for source types PPL/PLL, otherwise BookDefaultBankAccountNr.
WireText combines external order number, bank texts 1–3 and, for credit card payments, Kreditkarte plus the payment ID; max. 60 characters, double quotes removed.

Customer account events (CustomerAccount, events RPP, RCC, BTC)

Events with EventDate >= DateFrom and < DateTo.
Included if a manual booking is linked, or if the setting BookExportDatevExportAutomaticCAEvents is active and the event type is marked manual; additionally direct-debit-related entries with a matched invoice if BookExportManualDtausToDatev is set.
BankAccountNr is the booking account of the event type; BTC gets the plus sign, the others the minus sign.

Filters (both parts): ShopId (shop of the customer), CostCenter (cost center of the shop), Country (country of the invoice/order address or customer address), AmoutFrom/AmoutTo (on the absolute amount) and BankAccountNr (on the computed bank account).

The sign is s/h if the setting BookExportDatevPro is active, otherwise +/-. Rows are sorted by BankAccountNr, BankDate, Amount.

Notes

Read-only; all tables are read WITH (NOLOCK), so uncommitted data may appear.
Because SetStartAndEndOfDay = 0, a DateTo of 2024-01-31 means midnight at the start of that day: bank lines of that day after 00:00 and customer account events of that day are not included. Pass e.g. 2024-01-31T23:59:59 to include the whole day.
Bank lines use an inclusive upper bound (BETWEEN), customer account events an exclusive one (<).
Despite the comment "amount range in €", AmoutFrom/AmoutTo are compared with the absolute amount in the currency of the bank line/event, not converted.
ShopId is declared as varchar(3); the internal lookup by shop short name (values longer than 3 characters) can therefore not take effect through this call. An empty ShopId is not treated as NULL and yields no rows, whereas empty BankAccountNr and CostCenter are treated as NULL.
Customer account events are only returned if their exchange rate is found in the internally reduced exchange rate set (rates of the database currency source within ±2 weeks for currencies that occur in the selected bank lines).
Bank lines without a bank header are excluded by the source type filters.
The behaviour depends on many settings read at runtime (see description); dbo.SettingGet and pixi.GetDatabaseCurrency are not attached, so their exact semantics are not documented here.

Tables used

Settings · Read Shops · Read BankLines · Read BankHeader · Read BankMatch · Read ExchRate · Read Currency · Read CustomerAccountEvents · Read CustomerAccount · Read CustomerAccountMatch · Read CustomerAccountManualBookings · Read Invoices · Read Invlines · Read Orderlines · Read OrderHeader · Read Customers · Read Addresses · Read DTAUS · Read

Parameter	Type	Default	Description
`DateFrom` Required	datetime dateTime	–	Start of the period (used exactly as passed). Example: `2024-01-01T00:00:00`
`DateTo` Required	datetime dateTime	–	End of the period (used exactly as passed; inclusive for bank lines, exclusive for customer account events). Example: `2024-01-31T23:59:59`
`ShopId`	varchar(3) string	NULL	Optional shop ID; only payments of customers of this shop are returned. Example: `001`
`Country`	varchar(3) string	NULL	Optional country code of the invoice/order address (bank lines) or customer address (customer account events). Example: `DE`
`BankAccountNr`	varchar(10) string	NULL	Optional booking bank account; compared with the computed `BankAccountNr` column. Empty string is treated as NULL. Example: `1200`
`AmoutFrom`	money decimal	NULL	Optional minimum absolute amount. Example: `10.00`
`AmoutTo`	money decimal	NULL	Optional maximum absolute amount. Example: `5000.00`
`CostCenter`	varchar(8) string	NULL	Optional cost center of the shop. Empty string is treated as NULL. Example: `100`

Result set 1 · If setting `BookExportDatevActive` = 1

DATEV payment export rows from bank lines and customer account events, sorted by BankAccountNr, BankDate, Amount.

Column	Type	Description
`Currency` Nullable	string	Currency code (`CodeA`) of the payment.
`Sign`	char(1) string	Debit/credit sign: `+`/`-`, or `s`/`h` if `BookExportDatevPro` is active.
`Amount` Nullable	decimal	Absolute amount of the bank line or customer account event.
`Empty1`	varchar(1) string	Empty placeholder column.
`CustomerNr` Nullable	varchar(20) string	DATEV account number of the customer, or `BookDefaultCustomerNr` if no customer is assigned.
`InvoiceNr` Nullable	varchar string	Invoice number or external order number (bank lines: max. 12 characters, fallback `BookDefaultInvoiceNr`); empty if none for customer account events.
`Empty2`	varchar(1) string	Empty placeholder column.
`BankDate` Nullable	datetime dateTime	Bank date (or derived book date) of the bank line, or event date of the customer account event.
`BankAccountNr` Nullable	varchar(20) string	Booking bank account (accounting bank account, PayPal default account, default bank account or booking account of the event type).
`Empty3`	varchar(1) string	Empty placeholder column.
`Empty4`	varchar(1) string	Empty placeholder column.
`Empty5`	varchar(1) string	Empty placeholder column.
`Empty6`	varchar(1) string	Empty placeholder column.
`WireText` Nullable	varchar(60) string	Booking text (external order number, bank texts, credit card payment ID, or manual booking reference and event ID), max. 60 characters, without double quotes.
`ExchRate` Nullable	decimal(19,9) decimal	Exchange rate to the database currency, rounded to 6 decimals.
`CostCenter` Nullable	string	Cost center of the customer's shop.
`OrderNrExternal`	varchar string	External order number, empty if not available.

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

Single error status row.

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

Response example

<SqlRowSet1>
  <row>
    <Currency>EUR</Currency>
    <Sign>+</Sign>
    <Amount>119.0000</Amount>
    <Empty1/>
    <CustomerNr>10045</CustomerNr>
    <InvoiceNr>RE2024001234</InvoiceNr>
    <Empty2/>
    <BankDate>2024-01-15T00:00:00</BankDate>
    <BankAccountNr>1200</BankAccountNr>
    <Empty3/>
    <Empty4/>
    <Empty5/>
    <Empty6/>
    <WireText>WEB-50012 Zahlung Rechnung RE2024001234</WireText>
    <ExchRate>1.000000</ExchRate>
    <CostCenter>100</CostCenter>
    <OrderNrExternal>WEB-50012</OrderNrExternal>
  </row>
</SqlRowSet1>

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

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:pixiGetDatevExportWires>
      <DateFrom>2024-01-01T00:00:00</DateFrom>
      <DateTo>2024-01-31T23:59:59</DateTo>
      <!-- <ShopId>001</ShopId> -->
      <!-- <Country>DE</Country> -->
      <!-- <BankAccountNr>1200</BankAccountNr> -->
      <!-- <AmoutFrom>10.00</AmoutFrom> -->
      <!-- <AmoutTo>5000.00</AmoutTo> -->
      <!-- <CostCenter>100</CostCenter> -->
    </ns1:pixiGetDatevExportWires>
  </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:pixiGetDatevExportWires>
      <DateFrom>2024-01-01T00:00:00</DateFrom>
      <DateTo>2024-01-31T23:59:59</DateTo>
      <!-- <ShopId>001</ShopId> -->
      <!-- <Country>DE</Country> -->
      <!-- <BankAccountNr>1200</BankAccountNr> -->
      <!-- <AmoutFrom>10.00</AmoutFrom> -->
      <!-- <AmoutTo>5000.00</AmoutTo> -->
      <!-- <CostCenter>100</CostCenter> -->
    </ns1:pixiGetDatevExportWires>
  </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-01-31T23:59:59',
    // 'ShopId' => '001',
    // 'Country' => 'DE',
    // 'BankAccountNr' => '1200',
    // 'AmoutFrom' => '10.00',
    // 'AmoutTo' => '5000.00',
    // 'CostCenter' => '100',
];

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

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

Notes

Tables used

Result set 1 · If setting BookExportDatevActive = 1

Result set 2 · If setting BookExportDatevActive is not 1

Response example

Result set 1 · If setting `BookExportDatevActive` = 1

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