pixiGetCustomersToRefund · Pixi API documentation

Determines which customers should receive a refund from their customer account (CustomerAccount).

Empty ShopId is treated as NULL. NULL values for AmountFrom / AmountTo fall back to 0 / 1000000.
All customers whose customer account entries sum up to a positive amount are selected as candidates.
For these candidates the internal procedure Pixi_CA_GetSummaryList calculates per customer:
- FinancialBalance: sum of all customer account amounts.
- OpenOrders: value of order lines in an open state (OrderStates.CA_IsOpenOrder = 1) that are not yet invoiced, reduced by unused vouchers and increased by shipping costs. Only orders whose payment method has CA_SubstractFromopenOrdersWhenRefund = 0 count; negative values per currency are set to 0.
- SuggestedRefund: refundable account amount (entries without DoNotRefund, plus the matched part of entries with DoNotRefund) minus OpenOrders.
- OpenToMatch: amount not yet matched.
- IsMatched: match flag derived from the open-to-match sums per currency.
- CurrencyId: currency of the values.
The result contains only customers with SuggestedRefund > 0 and SuggestedRefund between AmountFrom and AmountTo (inclusive). If ShopId is given, only customers of that shop (Customers.CustomerOf) are returned; if IsAccountMatched is given, only customers with the matching IsMatched value.

No sort order is defined; there is no paging.

Notes

Read-only; all tables are read with NOLOCK, so values can reflect uncommitted data.
Behaviour depends on the settings DoNOTChargeShipCostOnSecondDelivery (whether shipping costs are added to open orders when an order is already partly invoiced), MultiCurrencyEnabled and HomeCurrency.
If MultiCurrencyEnabled = 1 and a customer has account entries or open orders in more than one currency, the amounts are converted to the shop's base currency using the exchange rate of the current day (pixi.ExchRate). If no rate exists for today, the amounts in that currency drop out of the sums. Without multi-currency, CurrencyId is the home currency.
The calculation runs over all customers with a positive account balance before the filters are applied, so the call can be expensive on large databases even with a restrictive ShopId.

Tables used

CustomerAccount · Read Customers · Read Settings · Read Orderlines · Read OrderStates · Read Invlines · Read OrderHeader · Read Voucher · Read Addresses · Read Payments · Read Shops · Read Currency · Read ExchRate · Read

Parameter	Type	Default	Description
`ShopId`	varchar(3) string	NULL	Shop of the customers (`Customers.CustomerOf`). `NULL` or empty returns customers of all shops. Example: `SHP`
`AmountFrom`	money decimal	0	Minimum suggested refund (inclusive). `NULL` is treated as 0. Example: `10.00`
`AmountTo`	money decimal	1000000	Maximum suggested refund (inclusive). `NULL` is treated as 1000000. Example: `500.00`
`IsAccountMatched`	bit boolean	NULL	Filter on the match flag `IsMatched`. `NULL` returns both. Allowed values `1` – Only customers with `IsMatched = 1`. `0` – Only customers with `IsMatched = 0`. Example: `1`

Result set 1 · Always (may be empty).

One row per customer with a positive suggested refund matching the filters.

Column	Type	Description
`CustKey`	int	Customer key.
`ShopId` Nullable	varchar(3) string	Shop the customer belongs to (`Customers.CustomerOf`).
`IsMatched` Nullable	bit boolean	Match flag of the customer account, derived from the open-to-match sums per currency.
`SuggestedRefund`	money decimal	Suggested refund amount: refundable account amount minus open orders.
`FinancialBalance` Nullable	money decimal	Total balance of the customer account.
`OpenOrders` Nullable	money decimal	Value of open, not yet invoiced orders (incl. shipping costs, minus unused vouchers).
`OpenToMatch` Nullable	money decimal	Amount of the customer account not yet matched.
`CurrencyId` Nullable	int	Currency of the amounts (shop base currency, account currency or home currency depending on multi-currency settings).

Response example

<SqlRowSet1>
  <row>
    <CustKey>10452</CustKey>
    <ShopId>SHP</ShopId>
    <IsMatched>true</IsMatched>
    <SuggestedRefund>49.9000</SuggestedRefund>
    <FinancialBalance>49.9000</FinancialBalance>
    <OpenOrders>0.0000</OpenOrders>
    <OpenToMatch>49.9000</OpenToMatch>
    <CurrencyId>1</CurrencyId>
  </row>
  <row>
    <CustKey>10988</CustKey>
    <ShopId>SHP</ShopId>
    <IsMatched>false</IsMatched>
    <SuggestedRefund>15.0000</SuggestedRefund>
    <FinancialBalance>40.0000</FinancialBalance>
    <OpenOrders>25.0000</OpenOrders>
    <OpenToMatch>40.0000</OpenToMatch>
    <CurrencyId>1</CurrencyId>
  </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:pixiGetCustomersToRefund>
      <!-- <ShopId>SHP</ShopId> -->
      <!-- <AmountFrom>10.00</AmountFrom> -->
      <!-- <AmountTo>500.00</AmountTo> -->
      <!-- <IsAccountMatched>1</IsAccountMatched> -->
    </ns1:pixiGetCustomersToRefund>
  </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:pixiGetCustomersToRefund>
      <!-- <ShopId>SHP</ShopId> -->
      <!-- <AmountFrom>10.00</AmountFrom> -->
      <!-- <AmountTo>500.00</AmountTo> -->
      <!-- <IsAccountMatched>1</IsAccountMatched> -->
    </ns1:pixiGetCustomersToRefund>
  </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 = [
    // 'ShopId' => 'SHP',
    // 'AmountFrom' => '10.00',
    // 'AmountTo' => '500.00',
    // 'IsAccountMatched' => '1',
];

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

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