Invoices Read

pixiDatevExportPosInvoices

Returns POS sales and POS payments of a date range as DATEV booking lines (revenue per VAT rate and payment entries) for export to accounting.

Wrapper for the internal procedure Pixi_DatevExportPOSInvoices. It builds DATEV booking lines from point-of-sale documents (POSheader2/POSitem2) and POS payments (POSPayment).

Date range: DateFrom is moved to the start of its day and DateTo to the end of its day. Both are compared with the POS document date PHdate.

Revenue lines (Source = INV)

One line per POS document and VAT rate, sign, item booking account and some other grouping attributes. Line amounts are PIQty * PIEndPrice. A header discount (amount or percent) is spread proportionally over the lines.
MwstSatz is the item VAT rate (1 = high, 2 = low, 3 = zero). It is set to 3 if the customer address has AddrNoChargeVAT.
BookKeepingAccount depends on the VAT rate and comes from the settings AccountNumberPOSInvoice, AccountNumberPOSInvoiceVatLow and AccountNumberPOSInvoiceVatZero.
VatCode and BookKeepingAccount can then be overridden by DatevVatCodeOverrideHigh/Low and DatevAccountOverrideHigh/Low from the VATs table. A country-specific entry valid on the document date wins over a generic one. Accounts that appear as values of settings named like book%accounteucompanies% are not overridden.
Groups whose sum is 0 are skipped.

Payment lines (Source = PAY)

One line per POS payment amount, except payment codes B and -. The amount is POSPayment.Amount.
BookKeepingAccount is the BookkeepingAccountPos of the payment type, or the setting AccountNumberPOSPayment as fallback.
Gegenkonto comes from the shop (Shops.Gegenkonto) of the customer (Customers.CustomerOf) linked through the invoice with the same number.

Filters (each one is ignored when NULL):

ShopId: shop of the address. For INV lines this is the POS customer address. For PAY lines it is the address of the invoice with the same number.
Country: country of the POS customer address.
InvoiceNr: POS document number (PHdocNr).
OrderNrExternal: external order number of the order line (INV) or of the order header (PAY).
BookKeepingAccount: works as an all-or-nothing switch, not as a per-row filter. INV lines are returned only if the value equals one of the three POS invoice account settings. PAY lines are returned only if it equals AccountNumberPOSPayment.

Output

With ShowOnlySums = 0, all lines are returned. Only lines whose country exists in Countries are included.
Price and PriceInCents are absolute values; the direction is in Sign.
CustomerNr falls back to the setting DefaultCustomerAccountNumber.
Sort order: InvoiceNr, Source, InvText, Price.

With ShowOnlySums = 1, only one placeholder row with ReportName = DATEVExportInvoices is returned. All its sum columns are NULL.

Notes

Read-only. All tables are read with NOLOCK, except POSheader2 and Addresses in the payment part. Uncommitted data can therefore appear.
Many settings change the output:
- BookExportDatevPro: sign s/h instead of +/-.
- BookExportDatevHomeCountry: home country, default D.
- BookExportDatevUseOrderNrExternal: comma list of payment types for which InvoiceNr holds the external order number instead of the POS document number.
- BookExportBookAccountAsKostenStelle: puts the item booking account into Empty2.
- ExportPaymentTypeAsKostenStelle2: Empty3 gets the payment type (1). With 2 it gets NULL for INV lines and the shop Gegenkonto for PAY lines.
- VAT code settings BookEu*VatCode, BookGer*VatCode and BookNonEuVatCode.
VAT code settings are cast to int. A non-numeric value causes a conversion error.
Country falls back to the country of the POS location (Location.LocCountry). VatCode and AccountNrItems, however, are derived only from the customer address country, so they can be NULL for anonymous POS sales.
Payment lines are joined to the POS items. A payment can therefore be split into several lines if the items have different VAT codes or item accounts.
The currency is always the database home currency (pixi.GetDatabaseCurrency).
OrderNrExternal is cut to 12 characters and InvText to 60 characters.
The ShowOnlySums mode currently returns no real sums, only NULL values.

Tables used

POSheader2 · Read POSitem2 · Read POSPayment · Read Payments · Read items · Read Addresses · Read Countries · Read Orderlines · Read OrderHeader · Read Invoices · Read Customers · Read Location · Read Shops · Read VATs · Read Settings · Read

Parameter	Type	Default	Description
`DateFrom` Required	datetime dateTime	–	Start of the period (POS document date). The time part is reset to the start of the day. Example: `2024-05-01T00:00:00`
`DateTo` Required	datetime dateTime	–	End of the period (POS document date). Extended to the end of the day. Example: `2024-05-31T00:00:00`
`ShopId`	varchar(3) string	NULL	Optional shop filter on the shop of the customer address. Sales without an address are excluded when set. Example: `ABC`
`ShowOnlySums`	bit boolean	0	Selects the output mode: booking lines or a placeholder sum row. Allowed values `0` – Return all DATEV booking lines. `1` – Return only one row `DATEVExportInvoices` with NULL sums. Example: `0`
`Country`	varchar(3) string	NULL	Optional filter on the Pixi country code of the POS customer address. Example: `D`
`InvoiceNr`	varchar(20) string	NULL	Optional filter on the POS document number (`PHdocNr`). Example: `K2024000123`
`OrderNrExternal`	varchar(50) string	NULL	Optional filter on the external order number linked to the POS sale. Example: `WEB-100045`
`BookKeepingAccount`	varchar(255) string	NULL	Optional account switch. `INV` lines are returned only if it equals one of the POS invoice account settings. `PAY` lines are returned only if it equals `AccountNumberPOSPayment`. Example: `8400`

Result set 1 · If `ShowOnlySums` = 1

Single placeholder row; all sum columns are NULL.

Column	Type	Description
`ReportName`	varchar(19) string	Always `DATEVExportInvoices`.
`SumQtyItems` Nullable	int	Always NULL.
`Sum_VK` Nullable	int	Always NULL.
`Sum_VK_Netto` Nullable	int	Always NULL.
`Sum_ShipCost_Netto` Nullable	int	Always NULL.
`Sum_ShipCost` Nullable	int	Always NULL.
`Sum_VAT` Nullable	int	Always NULL.
`Sum_NettoBruttoControl` Nullable	int	Always NULL.

Result set 2 · If `ShowOnlySums` = 0 (default)

DATEV booking lines for POS revenue (INV) and POS payments (PAY), ordered by InvoiceNr, Source, InvText, Price.

Column	Type	Description
`Source`	varchar(5) string	`INV` for revenue lines, `PAY` for payment lines.
`OrderCurrency`	varchar(3) string	Database home currency.
`PHDate`	datetime dateTime	POS document date.
`Country` Nullable	varchar(3) string	Country of the customer address, otherwise of the POS location.
`MwstSatz` Nullable	smallint int	VAT rate key (1 high, 2 low, 3 zero); NULL for payment lines.
`Sign`	varchar(1) string	Booking direction: `+`/`-`, or `s`/`h` if `BookExportDatevPro` is set.
`Price` Nullable	money decimal	Absolute amount, rounded to 2 decimals for revenue lines.
`PriceInCents` Nullable	bigint long	Absolute amount in cents.
`VatCode` Nullable	int	DATEV tax key from settings or `VATs` overrides.
`BookKeepingAccount` Nullable	varchar(255) string	Revenue account (per VAT rate) or payment account.
`AccountNrItems` Nullable	varchar(20) string	Item booking account (EU, EU companies or non-EU).
`InvoiceNr` Nullable	varchar(50) string	POS document number, or external order number for payment types listed in `BookExportDatevUseOrderNrExternal`.
`Empty1`	varchar(1) string	Always empty.
`InvoiceDate`	datetime dateTime	POS document date.
`CustomerNr` Nullable	varchar(60) string	External customer number of the address, else setting `DefaultCustomerAccountNumber`.
`Empty2` Nullable	varchar(20) string	Item booking account as cost center if `BookExportBookAccountAsKostenStelle` is set, else empty.
`Empty3` Nullable	varchar(50) string	Depends on `ExportPaymentTypeAsKostenStelle2`: payment type, shop Gegenkonto (PAY) or NULL, otherwise empty.
`Empty4`	varchar(1) string	Always empty.
`Skonto` Nullable	int	Always NULL.
`InvText` Nullable	varchar(60) string	Booking text: external order number, `Ref. rechn.` reference document and (INV) invoice comment, max. 60 characters.
`OrderNrExternal` Nullable	varchar(12) string	External order number, trimmed and cut to 12 characters.
`CustomerName` Nullable	varchar(60) string	Address name or customer name of the POS document.
`Firstname` Nullable	varchar(60) string	Part of the name before the first space.
`Lastname` Nullable	varchar(60) string	Part of the name after the first space.
`VatID` Nullable	varchar(50) string	VAT ID of the POS document (INV) or of the customer (PAY).
`Gegenkonto` Nullable	varchar(50) string	Contra account of the customer's shop; NULL for revenue lines.
`IsEU` Nullable	boolean	EU flag of the country from `Countries`.
`CntCodeISO2` Nullable	string	ISO-2 code of the country.

Response example

<SqlRowSet1>
  <row>
    <Source>INV</Source>
    <OrderCurrency>EUR</OrderCurrency>
    <PHDate>2024-05-03T14:22:00</PHDate>
    <Country>D</Country>
    <MwstSatz>1</MwstSatz>
    <Sign>-</Sign>
    <Price>49.90</Price>
    <PriceInCents>4990</PriceInCents>
    <VatCode>3</VatCode>
    <BookKeepingAccount>8400</BookKeepingAccount>
    <AccountNrItems>8400</AccountNrItems>
    <InvoiceNr>K2024000123</InvoiceNr>
    <Empty1/>
    <InvoiceDate>2024-05-03T14:22:00</InvoiceDate>
    <CustomerNr>10000</CustomerNr>
    <Empty2/>
    <Empty3/>
    <Empty4/>
    <InvText/>
    <CustomerName>Max Mustermann</CustomerName>
    <Firstname>Max</Firstname>
    <Lastname>Mustermann</Lastname>
    <IsEU>1</IsEU>
    <CntCodeISO2>DE</CntCodeISO2>
  </row>
  <row>
    <Source>PAY</Source>
    <OrderCurrency>EUR</OrderCurrency>
    <PHDate>2024-05-03T14:22:00</PHDate>
    <Country>D</Country>
    <Sign>-</Sign>
    <Price>49.90</Price>
    <PriceInCents>4990</PriceInCents>
    <VatCode>3</VatCode>
    <BookKeepingAccount>1000</BookKeepingAccount>
    <AccountNrItems>8400</AccountNrItems>
    <InvoiceNr>K2024000123</InvoiceNr>
    <Empty1/>
    <InvoiceDate>2024-05-03T14:22:00</InvoiceDate>
    <CustomerNr>10000</CustomerNr>
    <Empty2/>
    <Empty3/>
    <Empty4/>
    <InvText/>
    <CustomerName>Max Mustermann</CustomerName>
    <Firstname>Max</Firstname>
    <Lastname>Mustermann</Lastname>
    <Gegenkonto>1360</Gegenkonto>
    <IsEU>1</IsEU>
    <CntCodeISO2>DE</CntCodeISO2>
  </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:pixiDatevExportPosInvoices>
      <DateFrom>2024-05-01T00:00:00</DateFrom>
      <DateTo>2024-05-31T00:00:00</DateTo>
      <!-- <ShopId>ABC</ShopId> -->
      <!-- <ShowOnlySums>0</ShowOnlySums> -->
      <!-- <Country>D</Country> -->
      <!-- <InvoiceNr>K2024000123</InvoiceNr> -->
      <!-- <OrderNrExternal>WEB-100045</OrderNrExternal> -->
      <!-- <BookKeepingAccount>8400</BookKeepingAccount> -->
    </ns1:pixiDatevExportPosInvoices>
  </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:pixiDatevExportPosInvoices>
      <DateFrom>2024-05-01T00:00:00</DateFrom>
      <DateTo>2024-05-31T00:00:00</DateTo>
      <!-- <ShopId>ABC</ShopId> -->
      <!-- <ShowOnlySums>0</ShowOnlySums> -->
      <!-- <Country>D</Country> -->
      <!-- <InvoiceNr>K2024000123</InvoiceNr> -->
      <!-- <OrderNrExternal>WEB-100045</OrderNrExternal> -->
      <!-- <BookKeepingAccount>8400</BookKeepingAccount> -->
    </ns1:pixiDatevExportPosInvoices>
  </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-05-01T00:00:00',
    'DateTo' => '2024-05-31T00:00:00',
    // 'ShopId' => 'ABC',
    // 'ShowOnlySums' => '0',
    // 'Country' => 'D',
    // 'InvoiceNr' => 'K2024000123',
    // 'OrderNrExternal' => 'WEB-100045',
    // 'BookKeepingAccount' => '8400',
];

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

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

SQL object:: dbo.pipiDatevExportPosInvoices