pixiCAGetCustomerEvents · Pixi API documentation

Lists the customer account (Kundenkonto) of one customer.

Identifying the customer

Pass CustRef (the internal customer key), or
pass both ShopID and CustNrExt (the external customer number in that shop). The procedure then looks up CustKey in Customers where CustomerOf = ShopID and CustomerNrExternal = CustNrExt.
If CustRef is empty or 0 and one of ShopID/CustNrExt is missing, an error is raised.
If CustRef is given, ShopID and CustNrExt are ignored.

Events All account events of the customer are collected (no filter, translations in language 1):

active bookings from CustomerAccount (IsDeleted = 0),
deleted bookings from CustomerAccountLog with history remark DELETE (IsDeleted = 1).

Number is the invoice number, bank line text, direct debit account number or manual booking reference, depending on what the event refers to. OpenTOMatch is Amount minus the matched amount.

Statistics The account statistics (balance, open orders, suggested refund, matched flag, account currency) are calculated per customer and joined to every event row.

Result One row per event, each carrying the same statistics columns. If the customer has no events, a single row with only the statistics columns filled is returned (event columns, including CustRef, are NULL).

Notes

Read-only; all permanent tables are read with NOLOCK, so uncommitted data may appear.
The final SELECT has no ORDER BY; the events are produced in ascending EventDate order by the helper procedure, but the order of the returned rows is not guaranteed.
If ShopID/CustNrExt do not match a customer, no customer key is found; the result then depends on the statistics procedure Pixi_CA_GetCustomerAccountSummary (not attached) and is typically empty.
The statistics temp table has a unique index on CustRef. If the summary returns more than one row per customer (e.g. several currencies), the insert fails with a duplicate key error.
The column DoNotRefund is the per-event flag, not the do-not-refund total of the statistics.
Currency conversion of the statistics depends on the setting MultiCurrencyEnabled; AccountCurrencyCode may be the order or the base currency.
Column names IsAcountMatched and SuggestedRefound are spelled exactly like this.

Tables used

Customers · Read Shops · Read Currency · Read ExchRate · Read CustomerAccount · Read CustomerAccountLog · Read CustomerAccountEvents · Read CustomerAccountManualBookings · Read DTAUS · Read DTAUSstates · Read CCPTranLog · Read Invoices · Read BankLines · Read Orderlines · Read OrderStates · Read

Parameter	Type	Default	Description
`CustRef`	int	NULL	Internal customer key (`CustKey`). If given (not 0), it takes precedence over `ShopID`/`CustNrExt`. Example: `10452`
`CustNrExt`	varchar(60) string	NULL	External customer number (`CustomerNrExternal`). Only used together with `ShopID` when `CustRef` is not given. Example: `K-200381`
`ShopID`	varchar(3) string	NULL	Shop the customer belongs to (`CustomerOf`). Only used together with `CustNrExt` when `CustRef` is not given. Example: `ABC`

Result set 1 · On success (parameters valid)

One row per customer account event with the customer's account statistics; one statistics-only row if there are no events.

Column	Type	Description
`CustRef` Nullable	int	Customer key of the event; NULL if the customer has no events.
`EventDate` Nullable	datetime dateTime	Date of the account event.
`EventID` Nullable	varchar(3) string	Event type code (see `CustomerAccountEvents`).
`Number` Nullable	varchar(1024) string	Document number: invoice number, bank line text, direct debit account number or manual booking reference; for deleted events the bank line text.
`Reference` Nullable	varchar(1024) string	Booking reference, prefixed with the bank line's third text if present.
`Amount` Nullable	money decimal	Amount of the event in the order currency.
`OpenTOMatch` Nullable	money decimal	Amount not yet matched (amount minus matched amount).
`DoNotRefund` Nullable	bit boolean	Flag of the event: amount must not be refunded.
`OrderCurrency` Nullable	varchar(3) string	Currency code of the event.
`IsAcountMatched` Nullable	bit boolean	Flag whether the customer account is matched (from the statistics).
`CustomerBalance` Nullable	money decimal	Financial balance of the customer account.
`OpenOrders` Nullable	money decimal	Value of open orders of the customer.
`SuggestedRefound` Nullable	money decimal	Suggested refund amount.
`IsDeleted` Nullable	bit boolean	1 if the event was deleted (from `CustomerAccountLog`), 0 for active events.
`AccountCurrencyCode` Nullable	varchar(3) string	Currency of the statistics values; order or base currency.

Response example

<SqlRowSet1>
  <row>
    <CustRef>10452</CustRef>
    <EventDate>2024-03-04T10:15:00</EventDate>
    <EventID>INV</EventID>
    <Number>RE-2024-00871</Number>
    <Reference>Order 55120</Reference>
    <Amount>-119.0000</Amount>
    <OpenTOMatch>0.0000</OpenTOMatch>
    <DoNotRefund>false</DoNotRefund>
    <OrderCurrency>EUR</OrderCurrency>
    <IsAcountMatched>true</IsAcountMatched>
    <CustomerBalance>0.0000</CustomerBalance>
    <OpenOrders>0.0000</OpenOrders>
    <SuggestedRefound>0.0000</SuggestedRefound>
    <IsDeleted>false</IsDeleted>
    <AccountCurrencyCode>EUR</AccountCurrencyCode>
  </row>
  <row>
    <CustRef>10452</CustRef>
    <EventDate>2024-03-06T08:02:00</EventDate>
    <EventID>BNK</EventID>
    <Number>Payment 55120</Number>
    <Reference>K-200381 Order 55120</Reference>
    <Amount>119.0000</Amount>
    <OpenTOMatch>0.0000</OpenTOMatch>
    <DoNotRefund>false</DoNotRefund>
    <OrderCurrency>EUR</OrderCurrency>
    <IsAcountMatched>true</IsAcountMatched>
    <CustomerBalance>0.0000</CustomerBalance>
    <OpenOrders>0.0000</OpenOrders>
    <SuggestedRefound>0.0000</SuggestedRefound>
    <IsDeleted>false</IsDeleted>
    <AccountCurrencyCode>EUR</AccountCurrencyCode>
  </row>
</SqlRowSet1>

Status	Message	Cause
RAISERROR	Parameters ShopID and CustNrExt cannot be provided alone. Use both of them or CustRef parameter (additionally).	`CustRef` is empty or 0 and `ShopID` or `CustNrExt` is missing. Pass `CustRef` or both `ShopID` and `CustNrExt`.

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:pixiCAGetCustomerEvents>
      <!-- <CustRef>10452</CustRef> -->
      <!-- <CustNrExt>K-200381</CustNrExt> -->
      <!-- <ShopID>ABC</ShopID> -->
    </ns1:pixiCAGetCustomerEvents>
  </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:pixiCAGetCustomerEvents>
      <!-- <CustRef>10452</CustRef> -->
      <!-- <CustNrExt>K-200381</CustNrExt> -->
      <!-- <ShopID>ABC</ShopID> -->
    </ns1:pixiCAGetCustomerEvents>
  </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 = [
    // 'CustRef' => '10452',
    // 'CustNrExt' => 'K-200381',
    // 'ShopID' => 'ABC',
];

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

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