Customers Read

pixiGetCustomerOrderAccount

Returns a customer's master data, addresses, orders, order lines, bundle lines, invoices, invoice lines and customer account summary and entries.

Collects everything needed for a customer account view of one customer (CustKey) and returns it as nine result sets.

Order selection

The orders come from Orderlines with CustRef = @CustKey. The procedure applies an optional filter on Orderlines.OrderDate:

OrderDateFrom and OrderDateTo set: OrderDate >= OrderDateFrom and OrderDate < OrderDateTo. The upper bound is exclusive.
Only OrderDateFrom set: OrderDate >= OrderDateFrom.
Otherwise: no date filter. This also applies when only OrderDateTo is set (see notes).

An order is included if at least one of its lines matches. All lines of a selected order are returned afterwards.

The invoices are all distinct InvRef values of the lines of the selected orders.

Result sets

Customer master data from Customers.
All addresses of the customer (AdrCustRef = @CustKey), including bank and credit card data and the ISO country codes from Countries.
Order headers of the selected orders, with the currency code.
Order lines of the selected orders, sorted by OrderNr. Includes supplier item number, estimated delivery and a computed ResQty.
Bundle order lines (BundleOrderlines) of the selected orders, sorted by OrderNr.
Invoices linked to the selected orders.
Invoice lines of these invoices, sorted by InvoiceRef.
Financial summary of the customer account. It is calculated by Pixi_CA_GetCustomerAccountSummary and is not date-filtered.
All customer account entries (CustomerAccount) of the customer, sorted by CustomerAccountKey. They are not date-filtered.

Notes

Date filter bug: the third branch repeats the condition of the second one (OrderDateFrom IS NOT NULL AND OrderDateTo IS NULL). If only OrderDateTo is passed, it is ignored and all orders of the customer are returned. To set only an upper bound, also pass a very early OrderDateFrom.
No locking: all reads use NOLOCK, so uncommitted data can appear.
Currency joins: order headers, order lines, bundle lines, invoices, invoice lines and account entries are joined to pixi.Currency with an inner join. Records without a matching currency are not returned.
ResQty in result set 4:
- The code ranks the active reservations of the same item by ResDate and ResOrderlineRef, across all customers and locations. Active means: order status in OrderStates with FinalState = 'N', excluding EIN, FUT and FUS.
- ResQty is the location stock (ItemLocationSpecific.LocationQuantity) minus the number of earlier reservations.
- Lines without an active reservation get the full location stock.
Always-NULL column: SOrderlineRef is always NULL.
Duplicate columns: columns ending in _OrderCurr repeat the base column. The code marks them for removal.
Repeated values: ShipCost in result sets 4 and 5 is the order header value, repeated on every line.
Sensitive data: result set 2 returns credit card number, CVC and bank data. Handle the response accordingly.
Result set 8:
- It depends on Pixi_CA_GetCustomerAccountSummary, which reads the settings DoNOTChargeShipCostOnSecondDelivery, MultiCurrencyEnabled and HomeCurrency.
- It can be empty if the customer's shop or the HomeCurrency setting cannot be resolved.
- With several currencies and multi-currency enabled, amounts are converted with the exchange rate of the current day.
Non-existent customer: for an unknown CustKey the result sets are simply empty. No error is raised.

Tables used

Orderlines · Read OrderStates · Read Customers · Read Addresses · Read Countries · Read OrderHeader · Read Currency · Read Reservation · Read Items · Read ItemLocationSpecific · Read Orderline_EstimatedDelivery · Read BundleOrderlines · Read Invoices · Read Invlines · Read CustomerAccount · Read

Parameter	Type	Default	Description
`CustKey` Required	int	–	Internal customer key (`Customers.CustKey`). Example: `10234`
`OrderDateFrom`	datetime dateTime	NULL	Lower bound (inclusive) for the order line date. NULL means no lower bound. Example: `2024-01-01T00:00:00`
`OrderDateTo`	datetime dateTime	NULL	Upper bound (exclusive) for the order line date. Only used together with `OrderDateFrom`; ignored if passed alone. Example: `2024-07-01T00:00:00`

Result set 1 · Always (empty if the customer does not exist)

Customer master data.

Column	Type	Description
`CustomerNrExternal` Nullable	string	External customer number.
`ShopID` Nullable	string	Shop the customer belongs to (`CustomerOf`).
`CeMail` Nullable	string	Customer e-mail address.
`CustomerLocked` Nullable	string	Customer lock flag (`SLocked`).
`AddrRef` Nullable	int	Main address of the customer.

Result set 2 · Always

All addresses of the customer with payment data and ISO country codes.

Column	Type	Description
`AddrKey`	int	Address key.
`AddrNr` Nullable	string	Address number.
`AddrType` Nullable	string	Address type.
`Anrede` Nullable	string	Salutation.
`Name` Nullable	string	Name.
`Name2` Nullable	string	Name line 2.
`FirstName` Nullable	string	First name.
`LastName` Nullable	string	Last name.
`CareOf` Nullable	string	Care of.
`Address` Nullable	string	Address line.
`Street` Nullable	string	Street.
`HouseNr` Nullable	string	House number.
`City` Nullable	string	City.
`State` Nullable	string	State/region.
`ZIP` Nullable	string	Postal code.
`Country` Nullable	string	Pixi country code.
`ZIPMB` Nullable	string	Postal code of the mailbox.
`Mailbox` Nullable	string	Mailbox (PO box).
`Phone` Nullable	string	Phone.
`Phone2` Nullable	string	Second phone.
`Fax` Nullable	string	Fax.
`Fax2` Nullable	string	Second fax.
`eMail` Nullable	string	E-mail.
`eMail2` Nullable	string	Second e-mail.
`VATID` Nullable	string	VAT ID (`UsStId`).
`BirthDate` Nullable	dateTime	Date of birth.
`Note` Nullable	string	Address note.
`CCType` Nullable	string	Credit card type.
`CCName` Nullable	string	Credit card holder.
`CCNumber` Nullable	string	Credit card number.
`CCExpiry` Nullable	string	Credit card expiry.
`CCCVC` Nullable	string	Credit card CVC.
`BankAccountNumber` Nullable	string	Bank account number.
`BankBLZ` Nullable	string	Bank code (BLZ).
`BankName` Nullable	string	Bank name.
`BankAccountName` Nullable	string	Account holder.
`BankBIC` Nullable	string	BIC.
`BankIBAN` Nullable	string	IBAN.
`PaymentType` Nullable	string	Payment type code (`APayment`).
`AddressLocked` Nullable	string	Address lock flag.
`CntCodeISO2` Nullable	string	ISO 3166 alpha-2 country code (NULL if country unknown).
`CntCodeISO3` Nullable	string	ISO 3166 alpha-3 country code.

Result set 3 · Always

Order headers of the selected orders.

Column	Type	Description
`OrderHeaderKey`	int	Order header key.
`OrderNr`	int	Pixi order number.
`OrderNrExternal` Nullable	string	External (shop) order number.
`OrderStatus` Nullable	string	Order status.
`OrderlinesTotal` Nullable	decimal	Total of the order lines.
`OrderlinesDiscount` Nullable	decimal	Discount on the order lines.
`OrderDiscount` Nullable	decimal	Order discount.
`OrderTotal` Nullable	decimal	Order total.
`ShipVendorCode` Nullable	string	Carrier code.
`GiftMessage` Nullable	string	Gift message.
`ShipInstruct` Nullable	string	Shipping instructions.
`DHLVerfahren` Nullable	string	DHL procedure (`Verfahren`).
`DHLProdukt` Nullable	string	DHL product.
`DHLExtras` Nullable	string	DHL extras.
`AddressRemarks` Nullable	string	Address remarks.
`BillingAddrRef` Nullable	int	Billing address key.
`ShippingAddrRef` Nullable	int	Shipping address key.
`OrderLocked` Nullable	string	Order lock flag.
`ShipDate` Nullable	dateTime	Ship date.
`CCAuthID` Nullable	string	Payment/credit card authorisation ID (`ccpPayID`).
`ShopNote` Nullable	string	Shop note.
`DFType` Nullable	string	Direct fulfillment type.
`SubShopLogo` Nullable	string	Sub-shop logo.
`SubShopName` Nullable	string	Sub-shop name.
`Referrer` Nullable	string	Referrer.
`PartialDeliveryActive`	boolean	`ForceToPicklistIfNotFULL`, 0 if NULL.
`CancelUnavailableItems` Nullable	boolean	Cancel remaining items after forced ship-out.
`PartialDeliveryForRemaining` Nullable	boolean	Multiple forced partial deliveries allowed.
`OrderCurrencyCode`	string	Currency code of the order.

Result set 4 · Always

All order lines of the selected orders, sorted by OrderNr.

Column	Type	Description
`OrderlineKey`	int	Order line key.
`OrderNr`	int	Order number.
`BillingAddrRef` Nullable	int	Billing address key.
`ShippingAddrRef` Nullable	int	Shipping address key.
`InvRef` Nullable	int	Invoice key.
`SupplNr` Nullable	string	Supplier (`SupplInfo`).
`EANUPC` Nullable	string	EAN/UPC (`ArtNr`).
`ArticleName` Nullable	string	Item name.
`ItemNrSuppl` Nullable	string	Supplier item number.
`Qty` Nullable	int	Quantity.
`Price` Nullable	decimal	Price.
`Discount` Nullable	decimal	Discount.
`DiscountValue` Nullable	decimal	Discount value.
`DiscountPerc` Nullable	decimal	Discount percentage.
`FullPrice` Nullable	decimal	Full price.
`OrderCurrency`	string	Order currency code.
`ShipCost` Nullable	decimal	Shipping cost of the order header.
`ShipCost_OrderCurr` Nullable	decimal	Same as `ShipCost` (marked for removal).
`VATPercent` Nullable	decimal	VAT rate.
`SupplPrice` Nullable	decimal	Supplier price.
`Status` Nullable	string	Order line status.
`ItemNote` Nullable	string	Item special note.
`OrderDate` Nullable	dateTime	Order date.
`ShipDate` Nullable	dateTime	Ship date.
`BoxNr` Nullable	string	Box number.
`PickListNrUsed` Nullable	string	Pick list used.
`LineItemID` Nullable	string	External line item ID.
`LocationID` Nullable	string	Location.
`LastOrdLocationID` Nullable	string	Last ordering location.
`OrdShopID` Nullable	string	Ordering shop.
`SerialNr` Nullable	string	Serial number.
`DFType` Nullable	string	Direct fulfillment type.
`DFShipDate` Nullable	dateTime	Direct fulfillment ship date.
`DFShipmentTrackingID` Nullable	string	Direct fulfillment tracking ID.
`InvoiceNrExternal` Nullable	string	External invoice number.
`OriginalOrderlineRef` Nullable	int	Original order line (e.g. for replacements).
`EstimatedDelivery` Nullable	datetime dateTime	Estimated delivery from `Orderline_EstimatedDelivery`.
`SOrderlineRef` Nullable	int	Always NULL.
`RealSOrderlineRef` Nullable	int	Supplier order line reference from `Orderline_EstimatedDelivery`.
`ResQty`	decimal	Location stock minus number of earlier active reservations of the item.
`BundleOrderlineRef` Nullable	int	Bundle order line reference.
`ItemNrInt` Nullable	string	Internal item number.

Result set 5 · Always

Bundle order lines of the selected orders, sorted by OrderNr.

Column	Type	Description
`OrderlineKey`	int	Bundle order line key.
`OrderNr`	int	Order number.
`BillingAddrRef` Nullable	int	Billing address key.
`ShippingAddrRef` Nullable	int	Shipping address key.
`InvRef` Nullable	int	Invoice key.
`SupplNr` Nullable	string	Supplier.
`EANUPC` Nullable	string	EAN/UPC.
`ArticleName` Nullable	string	Item name.
`ItemNrSuppl` Nullable	string	Supplier item number.
`Qty` Nullable	int	Quantity.
`Price` Nullable	decimal	Price.
`Discount` Nullable	decimal	Discount.
`DiscountValue` Nullable	decimal	Discount value.
`DiscountPerc` Nullable	decimal	Discount percentage.
`FullPrice` Nullable	decimal	Full price.
`OrderCurrency`	string	Order currency code.
`ShipCost` Nullable	decimal	Shipping cost of the order header.
`ShipCost_OrderCurr` Nullable	decimal	Same as `ShipCost` (marked for removal).
`VATPercent` Nullable	decimal	VAT rate.
`SupplPrice` Nullable	decimal	Supplier price.
`Status` Nullable	string	Status.
`ItemNote` Nullable	string	Item special note.
`OrderDate` Nullable	dateTime	Order date.
`ShipDate` Nullable	dateTime	Ship date.
`BoxNr` Nullable	string	Box number.
`LineItemID` Nullable	string	External line item ID.
`LocationID` Nullable	string	Location.
`LastOrdLocationID` Nullable	string	Last ordering location.
`OrdShopID` Nullable	string	Ordering shop.
`DFType` Nullable	string	Direct fulfillment type.
`OriginalOrderlineRef` Nullable	int	Original order line.
`ItemNrInt` Nullable	string	Internal item number.

Result set 6 · Always

Invoices linked to the selected orders.

Column	Type	Description
`InvoiceKey`	int	Invoice key.
`InvoiceNr` Nullable	string	Invoice number.
`InvDate` Nullable	dateTime	Invoice date.
`BillingAddrRef` Nullable	int	Billing address key.
`ShippingAddrRef` Nullable	int	Shipping address key.
`PaymentType` Nullable	string	Payment type.
`BankAccountNumber` Nullable	string	Bank account number.
`BankBLZ` Nullable	string	Bank code.
`BankAccountName` Nullable	string	Account holder.
`BoxNr` Nullable	string	Box number.
`Total` Nullable	decimal	Invoice total.
`PayDay` Nullable	dateTime	Payment date.
`Payed` Nullable	string	Paid flag.
`PayedSum` Nullable	decimal	Amount paid.
`Total_OrderCurr` Nullable	decimal	Same as `Total` (marked for removal).
`Total_ShipCost` Nullable	decimal	Shipping cost total.
`Total_ShipCost_OrderCurr` Nullable	decimal	Same as `Total_ShipCost` (marked for removal).
`Discount` Nullable	decimal	Voucher amount: `VoucherSum` for `B2C` invoices, otherwise `VoucherSum_Net`.
`ScanOutDate` Nullable	dateTime	Scan-out date.
`OrderCurrency`	string	Invoice currency code.
`ChargeVAT` Nullable	string	VAT charged flag.
`VATBaseHigh` Nullable	decimal	Net base at the high VAT rate.
`VATBaseLow` Nullable	decimal	Net base at the low VAT rate.
`VATNo` Nullable	string	VAT number.
`VATBaseHigh_OrderCurr` Nullable	decimal	Same as `VATBaseHigh` (marked for removal).
`VATBaseLow_OrderCurr` Nullable	decimal	Same as `VATBaseLow` (marked for removal).
`VATHighPerc` Nullable	decimal	High VAT rate.
`VATLowPerc` Nullable	decimal	Low VAT rate.
`VATHigh` Nullable	decimal	VAT amount at the high rate.
`VATLow` Nullable	decimal	VAT amount at the low rate.
`VATHigh_OrderCurr` Nullable	decimal	Same as `VATHigh` (marked for removal).
`VATLow_OrderCurr` Nullable	decimal	Same as `VATLow` (marked for removal).
`VATinShipCost` Nullable	decimal	VAT in shipping cost.
`VATBaseLow_ShipCost` Nullable	decimal	Low-rate base of the shipping cost.
`VATBaseHigh_ShipCost` Nullable	decimal	High-rate base of the shipping cost.
`VATHText` Nullable	string	Text for the high VAT rate.
`VATLText` Nullable	string	Text for the low VAT rate.
`RefToInvoiceNr` Nullable	string	Referenced invoice number (e.g. for credit notes).
`RefToInvoiceKey` Nullable	int	Referenced invoice key.
`ShipVendor` Nullable	string	Carrier.
`InvLocationID` Nullable	string	Invoice location.
`InvShopID` Nullable	string	Invoice shop.
`PackageNetWeight` Nullable	decimal	Net package weight.
`PackageGrossWeight` Nullable	decimal	Gross package weight.
`InvShipLock` Nullable	string	Shipping lock of the invoice.
`VoucherID` Nullable	string	Voucher ID.
`CCPPayID` Nullable	string	Payment provider ID.
`TrackingID` Nullable	string	Shipment tracking ID.
`ReturnReason` Nullable	string	Return reason.
`ReturnTrackingID` Nullable	string	Return tracking ID.
`DueDate` Nullable	dateTime	Due date.

Result set 7 · Always

Invoice lines of the invoices from result set 6, sorted by InvoiceRef.

Column	Type	Description
`InvlineKey`	int	Invoice line key.
`InvoiceRef`	int	Invoice key.
`OrderlineRef` Nullable	int	Order line key.
`ItemRef` Nullable	int	Item key.
`EANUPC` Nullable	string	EAN/UPC (`ItemNr`).
`ItemQty` Nullable	int	Invoiced quantity.
`OrderQty` Nullable	int	Ordered quantity.
`ItemText` Nullable	string	Item text.
`ItemPrice` Nullable	decimal	Item price.
`ItemPrice_OrderCurr` Nullable	decimal	Same as `ItemPrice` (marked for removal).
`ItemDiscount` Nullable	decimal	Item discount.
`ItemVAT` Nullable	decimal	Item VAT.
`DiscountValue` Nullable	decimal	Discount value.
`DiscountPerc` Nullable	decimal	Discount percentage.
`ShipCost` Nullable	decimal	Shipping cost.
`ShipCost_OrderCurr` Nullable	decimal	Same as `ShipCost` (marked for removal).
`FullPrice` Nullable	decimal	Full price.
`SerialNr` Nullable	string	Serial number.
`OrderCurrencyCode`	string	Invoice currency code.

Result set 8 · Always; usually one row, empty if the summary cannot be calculated

Financial summary of the customer account from Pixi_CA_GetCustomerAccountSummary.

Column	Type	Description
`FinancialBalance` Nullable	money decimal	Sum of all customer account amounts.
`OpenOrders` Nullable	money decimal	Value of open, not yet invoiced orders (not below 0).
`SuggestedRefund` Nullable	money decimal	Suggested refund amount.
`OpenToMatch` Nullable	money decimal	Absolute amount not yet matched.
`IsMatched` Nullable	bit boolean	1 if the account is matched.
`CurrencyCode`	string	Currency of the summary.

Result set 9 · Always

All customer account entries, sorted by CustomerAccountKey.

Column	Type	Description
`CustomerAccountKey`	int	Entry key.
`EventID` Nullable	string	Event type.
`EventDate` Nullable	dateTime	Event date.
`Reference` Nullable	string	Reference.
`Amount` Nullable	decimal	Amount.
`Matched` Nullable	decimal	Matched amount.
`InvoiceRef` Nullable	int	Invoice key.
`BanklineRef` Nullable	int	Bank line reference.
`DTAUSRef` Nullable	int	Direct debit (DTAUS) reference.
`ManualBookingRef` Nullable	int	Manual booking reference.
`DoNotRefund` Nullable	boolean	Do not refund flag.
`Comment` Nullable	string	Comment.
`CurrencyCode`	string	Currency code.

Response example

<SqlRowSet1>
  <row>
    <CustomerNrExternal>K-100234</CustomerNrExternal>
    <ShopID>SHOP1</ShopID>
    <CeMail>max.mustermann@example.com</CeMail>
    <CustomerLocked>N</CustomerLocked>
    <AddrRef>55871</AddrRef>
  </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:pixiGetCustomerOrderAccount>
      <CustKey>10234</CustKey>
      <!-- <OrderDateFrom>2024-01-01T00:00:00</OrderDateFrom> -->
      <!-- <OrderDateTo>2024-07-01T00:00:00</OrderDateTo> -->
    </ns1:pixiGetCustomerOrderAccount>
  </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:pixiGetCustomerOrderAccount>
      <CustKey>10234</CustKey>
      <!-- <OrderDateFrom>2024-01-01T00:00:00</OrderDateFrom> -->
      <!-- <OrderDateTo>2024-07-01T00:00:00</OrderDateTo> -->
    </ns1:pixiGetCustomerOrderAccount>
  </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',
    // 'OrderDateFrom' => '2024-01-01T00:00:00',
    // 'OrderDateTo' => '2024-07-01T00:00:00',
];

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

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

SQL object:: dbo.pipiGetCustomerOrderAccount