Invoices Read

pixiGetInvoiceShippingInformation

Returns shipping-relevant information for one invoice: payment, ship vendor, totals, shipping address with country data, order details and package weights.

Looks up the invoice with the given InvoiceNr and returns one row of data that a shipping or label tool typically needs.

The row is built as follows:

Invoice: number, date, shop, payment code, ship vendor code, total and shipping cost from Invoices.
Currency: the currency code (CodeA) of the invoice currency. This join is an inner join, so an invoice without a matching currency record returns no row.
Payment and ship vendor: text of the payment method and name of the ship vendor (optional, may be empty).
Shipping address: the address referenced by the invoice's shipping address reference (ShipAdrRef), including name parts, street, house number, city, ZIP, mailbox, phone, e-mail and VAT ID.
Country: country name, EasyLog country name and ISO2/ISO3 codes of the address country.
Order: the related order is determined via the invoice lines: the first invoice line found points to an order line, and the order of that order line is used. From it the call returns address remarks, DHL procedure/product/extras, gift message, ship date, shop note, order number and external order number.
Weights: net and gross package weight, rounded to 3 decimals.

If no invoice with this number exists, the result set is empty.

Notes

All tables are read with NOLOCK, so uncommitted data may be returned.
The order is found with TOP 1 subqueries without ORDER BY. If an invoice contains lines from several orders, only one of them is used and which one is not deterministic.
Total_OrderCurr always equals Total and ShipCost_OrderCurr always equals ShipCost; code comments mark these columns for removal, so do not rely on them.
PackageNetWeight and PackageGrossWeight are referenced without a table alias; from the code it cannot be seen whether they come from Invoices or OrderHeader.
No error rows are returned; an unknown invoice number simply yields no rows.

Tables used

Invoices · Read Currency · Read Payments · Read ShipVendor · Read Addresses · Read Countries · Read OrderHeader · Read OrderLines · Read InvLines · Read

Parameter	Type	Default	Description
`InvoiceNr` Required	varchar(50) string	–	Number of the invoice (`Invoices.InvoiceNr`) to return shipping information for. Example: `RE2024001234`

Result set 1 · Always; contains one row if the invoice exists and has a valid currency, otherwise no rows.

Shipping information of the invoice.

Column	Type	Description
`InvoiceNr`	string	Invoice number.
`InvDate` Nullable	dateTime	Invoice date.
`Shop` Nullable	string	Shop ID of the invoice (`InvShopId`).
`PaymentCode` Nullable	string	Payment method code of the invoice.
`PaymentText` Nullable	string	Text of the payment method.
`ShipVendorCode` Nullable	string	Ship vendor code of the invoice.
`ShipVendorName` Nullable	string	Name of the ship vendor.
`Total` Nullable	decimal	Invoice total.
`Currency` Nullable	string	Currency code (`Currency.CodeA`) of the invoice.
`Total_OrderCurr` Nullable	decimal	Same value as `Total`; marked for removal.
`ShipCost` Nullable	decimal	Shipping cost of the invoice (`Total_ShipCost`).
`ShipCost_OrderCurr` Nullable	decimal	Same value as `ShipCost`; marked for removal.
`Anrede` Nullable	string	Salutation of the shipping address.
`Name` Nullable	string	Name of the shipping address.
`Name2` Nullable	string	Second name line of the shipping address.
`FirstName` Nullable	string	First name.
`LastName` Nullable	string	Last name.
`CareOf` Nullable	string	Care-of line.
`Address` Nullable	string	Address line of the shipping address.
`Street` Nullable	string	Street.
`HouseNr` Nullable	string	House number.
`City` Nullable	string	City.
`State` Nullable	string	State or region.
`ZIP` Nullable	string	ZIP code.
`Country` Nullable	string	Pixi country code of the shipping address.
`CountryName` Nullable	string	Country name.
`EasylogCountryName` Nullable	string	Country name used for EasyLog.
`CountryISO2` Nullable	string	ISO 3166 alpha-2 country code.
`CountryISO3` Nullable	string	ISO 3166 alpha-3 country code.
`ZIPMailbox` Nullable	string	ZIP code of the mailbox (`ZIPMB`).
`Mailbox` Nullable	string	Mailbox (P.O. box).
`Phone` Nullable	string	Phone number.
`Fax` Nullable	string	Fax number.
`eMail` Nullable	string	E-mail address.
`UsStId` Nullable	string	VAT ID of the address.
`Note` Nullable	string	Note of the address.
`Address_Remarks` Nullable	string	Address remarks of the order.
`DHLVerfahren` Nullable	string	DHL procedure of the order (`Verfahren`).
`DHLProdukt` Nullable	string	DHL product of the order.
`DHLExtras` Nullable	string	DHL extras of the order.
`GiftMessage` Nullable	string	Gift message of the order.
`OrderShipDate` Nullable	dateTime	Ship date of the order.
`ShopNote` Nullable	string	Shop note of the order.
`OrderNr` Nullable	string	Pixi order number of the related order.
`OrderNrExternal` Nullable	string	External (shop) order number.
`PackageNetWeight` Nullable	numeric(8,3) decimal	Net package weight, rounded to 3 decimals.
`PackageGrossWeight` Nullable	numeric(8,3) decimal	Gross package weight, rounded to 3 decimals.

Response example

<SqlRowSet1>
  <row>
    <InvoiceNr>RE2024001234</InvoiceNr>
    <InvDate>2024-03-15T10:22:00</InvDate>
    <Shop>SHOP1</Shop>
    <PaymentCode>VK</PaymentCode>
    <PaymentText>Vorkasse</PaymentText>
    <ShipVendorCode>DHL</ShipVendorCode>
    <ShipVendorName>DHL Paket</ShipVendorName>
    <Total>59.90</Total>
    <Currency>EUR</Currency>
    <Total_OrderCurr>59.90</Total_OrderCurr>
    <ShipCost>4.90</ShipCost>
    <ShipCost_OrderCurr>4.90</ShipCost_OrderCurr>
    <Anrede>Frau</Anrede>
    <Name>Anna Schmidt</Name>
    <FirstName>Anna</FirstName>
    <LastName>Schmidt</LastName>
    <Street>Hauptstraße</Street>
    <HouseNr>12</HouseNr>
    <City>München</City>
    <ZIP>80331</ZIP>
    <Country>D</Country>
    <CountryName>Deutschland</CountryName>
    <EasylogCountryName>DEUTSCHLAND</EasylogCountryName>
    <CountryISO2>DE</CountryISO2>
    <CountryISO3>DEU</CountryISO3>
    <eMail>anna.schmidt@example.com</eMail>
    <OrderNr>456789</OrderNr>
    <OrderNrExternal>100012345</OrderNrExternal>
    <PackageNetWeight>1.250</PackageNetWeight>
    <PackageGrossWeight>1.480</PackageGrossWeight>
  </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:pixiGetInvoiceShippingInformation>
      <InvoiceNr>RE2024001234</InvoiceNr>
    </ns1:pixiGetInvoiceShippingInformation>
  </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:pixiGetInvoiceShippingInformation>
      <InvoiceNr>RE2024001234</InvoiceNr>
    </ns1:pixiGetInvoiceShippingInformation>
  </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 = [
    'InvoiceNr' => 'RE2024001234',
];

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

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

SQL object:: dbo.pipiGetInvoiceShippingInformation