Returns Write

pixiCreateReturn

Creates a return credit note for selected lines of an invoice, sets the order lines to returned and books the credit to the customer account.

The API counterpart of the returns wizard in Customer Service. It prepares the values and then calls the internal procedure Pixi_CreateReturn.

Input lists

InvoiceLines, Quantities and Prices are parallel lists separated by ; (a trailing ; is added if missing). Entry n of each list belongs to the same invoice line.
Prices are gross unit prices. The net price is calculated per line as Price / (1 + TaxRate/100) using the tax rate of the invoice line.
Each invoice line key is translated into its order line (Invlines.OrderLineRef).

Steps

The first invoice line in InvoiceLines determines the shipping country and the settings VATinShipCost / ChargeHighVATInShipCost of the original invoice.
If no VAT rate is valid today for this country: with Force Country VAT enabled the call fails; otherwise a default VAT rate is inserted for the country (Pixi_InsertDefaultVATRateForCountry).
The invoice header is read by InvoiceNr: customer, billing and shipping address, voucher, currency (the return is always done in the order currency).
Defaults: ShipCost = minus the shipping cost of the invoice (full shipping refund); Voucher_Amount = voucher sum of the invoice (gross for B2C, otherwise net).
Total: minus the sum of quantity × price, plus ShipCost, minus Voucher_Amount, plus ReturnCostsAmount. If the return costs would make the total positive, they are reduced so that the total becomes 0.
Pixi_CreateReturn creates the credit note: only lines that belong to the referenced invoice and whose order line has status AUS are credited. With a smaller quantity the order line is split first. The credited order lines get status RET, the credit is booked to the customer account, lines of the return case ReturnCaseID are closed and a history entry is written, matching pixi.Return records in status Received are set to Credited, and the event InvoiceCreated is published.
If MarkReturnDocumentForPrinting = 1, TimesPrinted of the new credit note is set to 0 so it is printed again.

The call returns the header of the new credit note as one row.

Notes

Runs in its own transaction (or a savepoint if a transaction is already open). Errors are caught, the transaction is rolled back and the error is logged with log.InsertError (not attached); whether the error is passed to the caller is not visible in the code. A failed call can therefore look like a call with an empty result.
Use . as decimal separator in Prices; values are cast to money.
The list parser removes the processed key with REPLACE. Duplicate keys or a key that is the ending of a later key (e.g. 23;123;) break the parsing. Pass each invoice line only once.
Lines whose order line is not in status AUS or that do not belong to the invoice InvoiceNr are silently skipped, but are still included in the calculated total.
CreateDate and InvoiceDate have no effect: the credit note date is always the current date.
DoNotRefund = 1 forces CreateDTAUS to 0.
TimesPrinted in the returned row shows the value before MarkReturnDocumentForPrinting is applied.
The column 1SS is not a valid XML element name; use OneSS instead.
Reads use NOLOCK.

Tables used

Invlines · Read Invoices · Write Addresses · Read Countries · Read VATs · Read Currency · Read

Parameter	Type	Default	Description
`InvoiceNr` Required	varchar(20) string	–	Number of the original invoice that is returned. Example: `RE2024001234`
`InvoiceLines` Required	varchar(max) string	–	Semicolon-separated list of invoice line keys (`InvLineKey`) to credit. The first key determines the shipping country check. Example: `501234;501235;`
`Quantities` Required	varchar(max) string	–	Semicolon-separated return quantities, one per entry in `InvoiceLines`. Example: `1;2;`
`Prices` Required	varchar(max) string	–	Semicolon-separated gross unit prices to credit, one per entry in `InvoiceLines`. Example: `19.99;5.50;`
`PaymentType` Required	char(1) string	–	Payment type code for the refund, stored on the credit note. Example: `U`
`Account`	varchar(10) string	–	Bank account number for the refund; spaces are removed and the value is stored on the billing address.
`BLZ`	char(8) string	–	Bank code (BLZ) for the refund; spaces are removed.
`AccountName` Required	varchar(27) string	–	Account holder name. Example: `Max Mustermann`
`ShipCost`	money decimal	NULL	Shipping cost to credit, as a negative value. NULL refunds the full shipping cost of the invoice; 0 refunds no shipping cost. Example: `-4.90`
`Note`	varchar(255) string	–	Note on the credit note. Example: `Return via API`
`ReturnReason`	varchar(100) string	–	Return reason stored on the credit note. Example: `Wrong size`
`CreateDate`	datetime dateTime	NULL	Creation date; defaults to now. It is passed on but not used by the internal procedure.
`CreateEmp`	varchar(50) string	API	User name recorded as creator of the credit note and of all changes. Example: `API`
`InvoiceDate`	datetime dateTime	NULL	Date of the credit note; overridden by the current date in the internal procedure, so it has no effect.
`ReturnClosed`	bit boolean	1	Whether the return is marked as closed. Allowed values `1` – Stored as `Y` (closed). `0` – Stored as `N` (open). Example: `1`
`ReturnCaseID`	int	NULL	ID of a Returns & Repairs case; its lines for the credited invoice lines are closed and a history entry is written.
`CreateDTAUS`	bit boolean	1	Whether a DTAUS/bank transfer is to be created for the refund. Ignored (0) if `DoNotRefund` = 1. Example: `1`
`DoNotRefund`	bit boolean	0	If 1, the credit is not refunded via the customer account. Allowed values `0` – Refund normally. `1` – Do not refund; `CreateDTAUS` is forced to 0. Example: `0`
`CAComment`	varchar(1000) string	NULL	Comment saved to the customer account history entry of the credit note. Example: `Refund via bank transfer`
`IBAN`	varchar(30) string	NULL	IBAN for the refund, stored on the billing address. Example: `DE89370400440532013000`
`BIC`	varchar(11) string	NULL	BIC for the refund, stored on the billing address. Example: `COBADEFFXXX`
`LanguageId`	int	2	Language for the translated return case history text. Example: `2`
`ReturnCostsAmount`	money decimal	NULL	Return costs charged to the customer (positive value). Any value other than 0 enables return costs; reduced if it would exceed the credited amount. Example: `3.00`
`Voucher_Amount`	money decimal	NULL	Voucher amount deducted from the credit. NULL uses the voucher sum of the invoice (gross for B2C, net otherwise). Example: `0`
`MarkReturnDocumentForPrinting`	bit boolean	0	If 1, the new credit note is marked for printing (`TimesPrinted` = 0). Allowed values `0` – Credit note stays marked as printed (`TimesPrinted` = 1). `1` – `TimesPrinted` is reset to 0. Example: `0`

Result set 1 · Returned by the internal credit creation; one row on success, no row if the credit note could not be created. Not returned if the VAT check fails.

Header of the new credit note from Invoices, joined with the currency.

Column	Type	Description
`InvoiceKey`	int	Internal key of the new credit note.
`InvoiceNr` Nullable	string	Number of the credit note.
`InvDate` Nullable	dateTime	Date of the credit note.
`CustRef` Nullable	int	Customer key.
`AddrRef` Nullable	int	Billing address key.
`Summary` Nullable	string	Summary field of the document.
`VATBaseHigh` Nullable	decimal	Base amount for the high VAT rate.
`VATBaseLow` Nullable	decimal	Base amount for the reduced VAT rate.
`VATType` Nullable	string	VAT type.
`VATNo` Nullable	string	VAT ID.
`skontoprozent` Nullable	decimal	Cash discount percentage.
`SkontoAbz` Nullable	decimal	Cash discount deduction.
`total` Nullable	decimal	Total amount (negative for a credit note).
`Payment` Nullable	string	Payment type.
`Printed` Nullable	string	Printed flag.
`PrintDate` Nullable	dateTime	Print date.
`Supporter` Nullable	string	Responsible employee.
`SupportPhone` Nullable	string	Support phone number.
`PayDay` Nullable	dateTime	Payment date.
`Payed` Nullable	string	Paid flag; NULL after creation.
`PayedSum` Nullable	decimal	Paid amount; 0 after creation.
`SUGKTO` Nullable	string	Accounting field SUGKTO.
`BuchuText` Nullable	string	Booking text.
`AuszugNr` Nullable	string	Bank statement number.
`Account` Nullable	string	Bank account number.
`BLZ` Nullable	string	Bank code.
`AccName` Nullable	string	Account holder.
`LSDate` Nullable	dateTime	Direct debit date.
`LSNr` Nullable	string	Direct debit number.
`RLSDate` Nullable	dateTime	Chargeback date.
`RLSCost` Nullable	decimal	Chargeback costs.
`Reminder1Date` Nullable	dateTime	Date of first reminder.
`Reminder2Date` Nullable	dateTime	Date of second reminder.
`Reminder3Date` Nullable	dateTime	Date of third reminder.
`CardDate` Nullable	dateTime	Credit card date.
`CardCheck` Nullable	string	Credit card check.
`CardType` Nullable	string	Credit card type.
`CardName` Nullable	string	Card holder.
`CardNr` Nullable	string	Card number.
`CardExp` Nullable	string	Card expiry.
`Note` Nullable	string	Note.
`NODRef` Nullable	int	Reference field NODRef.
`ShipCode` Nullable	string	Shipping code.
`CreateDate` Nullable	dateTime	Creation date.
`CreateEmp` Nullable	string	Created by.
`UpdateDate` Nullable	dateTime	Last change date.
`UpdateEmp` Nullable	string	Last changed by.
`BoxNr` Nullable	string	Box number.
`Total_OrderCurr` Nullable	decimal	Total in order currency (same as `total`).
`Total_ShipCost` Nullable	decimal	Shipping cost.
`Total_ShipCost_OrderCurr` Nullable	decimal	Shipping cost in order currency (same as `Total_ShipCost`).
`GNMcharge` Nullable	decimal	Field GNMcharge.
`ScanOutDate` Nullable	dateTime	Scan-out date.
`OrderCurrency` Nullable	string	Currency code of the credit note.
`VATBaseHigh_OrderCurr` Nullable	decimal	Same as `VATBaseHigh`.
`VATBaseLow_OrderCurr` Nullable	decimal	Same as `VATBaseLow`.
`VATHighPerc` Nullable	decimal	High VAT rate in percent.
`VATLowPerc` Nullable	decimal	Reduced VAT rate in percent.
`VATHigh` Nullable	decimal	VAT amount at high rate.
`VATLow` Nullable	decimal	VAT amount at reduced rate.
`VATHigh_OrderCurr` Nullable	decimal	Same as `VATHigh`.
`VATLow_OrderCurr` Nullable	decimal	Same as `VATLow`.
`VATHText` Nullable	string	Label of high VAT rate.
`VATLText` Nullable	string	Label of reduced VAT rate.
`RefToInvoiceNr` Nullable	string	Number of the referenced original invoice.
`RefToInvoiceKey` Nullable	int	Key of the referenced original invoice.
`CCFeeProvision` Nullable	decimal	Credit card fee commission.
`CCAddFee` Nullable	decimal	Additional credit card fee.
`CCFeeCurrency` Nullable	string	Currency of the credit card fee.
`BankFeeAmmount` Nullable	decimal	Bank fee.
`CostChargeShop` Nullable	decimal	Cost charged by the shop.
`CostChargeCurrency` Nullable	string	Currency of the cost charge.
`ChargeVAT` Nullable	string	Whether VAT is charged.
`IssueNr` Nullable	string	Issue number.
`ShipVendor` Nullable	string	Shipping vendor.
`CCPPaymentDone` Nullable	string	Card payment done flag.
`CCPAuthCode` Nullable	string	Card payment authorisation code.
`CCPNumOfTries` Nullable	int	Number of card payment tries.
`CCPReCharge` Nullable	string	Card re-charge flag.
`ShipAdrRef` Nullable	int	Shipping address key.
`InvLocationID` Nullable	int	Location of the document.
`TimesPrinted` Nullable	int	Print counter at creation (1, before `MarkReturnDocumentForPrinting` is applied).
`InvShopID` Nullable	string	Shop ID.
`CCPShipCostChargedYN` Nullable	string	Shipping cost charged by card.
`CCPChargedSoFar` Nullable	decimal	Amount charged by card so far.
`PackageNetWeight` Nullable	decimal	Net package weight.
`PackageGrossWeight` Nullable	decimal	Gross package weight.
`IsPackageLost` Nullable	string	Package lost flag.
`InvShipLock` Nullable	string	Shipping lock.
`InvTrackNumber` Nullable	string	Tracking number.
`ShipVendorIncrement1` Nullable	string	Shipping vendor increment.
`VoucherSum_OrderCurr` Nullable	decimal	Voucher sum in order currency.
`VoucherID` Nullable	string	Voucher ID (max. 60 characters).
`VATinShipCost` Nullable	string	VAT in shipping cost setting.
`ShippedByVendor` Nullable	string	Shipped by vendor flag.
`VATBaseLow_ShipCost` Nullable	decimal	Reduced VAT base of shipping cost.
`VATBaseHigh_ShipCost` Nullable	decimal	High VAT base of shipping cost.
`ccpPayID` Nullable	string	Payment provider payment ID.
`ccpTransID` Nullable	string	Payment provider transaction ID.
`ReminderLevel` Nullable	int	Dunning level.
`ReminderProcessed` Nullable	string	Dunning processed flag.
`ReminderCosts` Nullable	decimal	Dunning costs.
`ReminderReason` Nullable	string	Dunning reason.
`ccpSetFailTries` Nullable	int	Failed payment provider tries.
`ccpSetFailLastTime` Nullable	dateTime	Time of last failed try.
`ShipmentTrackingID` Nullable	string	Shipment tracking ID.
`ReturnReason` Nullable	string	Return reason.
`StockNote` Nullable	string	Stock note (empty when created by this call).
`ReturnSupplier` Nullable	string	Return supplier (NULL for this call).
`ReturnClosed` Nullable	string	`Y` if the return is closed, otherwise `N`.
`Fulfillment` Nullable	string	Fulfillment field.
`InvoiceMailSent` Nullable	string	Invoice mail sent flag.
`TrackingIDsent` Nullable	string	Tracking ID sent flag.
`PaidByCustomer` Nullable	decimal	Amount paid by customer.
`PLHref` Nullable	int	Pick list reference.
`Packages` Nullable	int	Number of packages.
`DiscountTotal` Nullable	decimal	Total discount.
`PaidAtCreation` Nullable	string	Paid at creation flag.
`TextIfNoInvlines` Nullable	string	Text used if there are no lines.
`retTrackID` Nullable	string	Return tracking ID.
`retTrackIDhist` Nullable	string	History of return tracking IDs.
`OneSS` Nullable	string	Value of column `1SS`.
`1SS` Nullable	string	Same as `OneSS`; kept for backward compatibility.
`DueDate` Nullable	dateTime	Due date.
`CreateDTAUS` Nullable	boolean	Whether a DTAUS is created for the refund.
`InvCreationMode` Nullable	string	Creation mode of the document.
`VATBaseHigh_ShipCost_OrderCurr` Nullable	decimal	Same as `VATBaseHigh_ShipCost`.
`VATBaseLow_ShipCost_OrderCurr` Nullable	decimal	Same as `VATBaseLow_ShipCost`.
`VATBaseZero_ShipCost` Nullable	decimal	Zero VAT base of shipping cost.
`VATHigh_ShipCost` Nullable	decimal	High VAT amount of shipping cost.
`VATLow_ShipCost` Nullable	decimal	Reduced VAT amount of shipping cost.
`OrderType` Nullable	string	Order type, e.g. `B2C`.
`Total_Net` Nullable	decimal	Net total.
`VATBaseZero` Nullable	decimal	Zero VAT base.
`Total_ShipCost_Net` Nullable	decimal	Net shipping cost.
`ChargeHighVATInShipCost` Nullable	boolean	Charge high VAT on shipping cost.
`DontChargeShipCostOn2ndDelivery` Nullable	boolean	No shipping cost on second delivery.
`ExecutionDate` Nullable	datetime dateTime	Execution date.
`SEPAMandateReferenceRef` Nullable	int	Reference to the SEPA mandate.
`ReturnCostsAmount` Nullable	decimal	Return costs (gross).
`ReturnCostsAmount_Net` Nullable	decimal	Return costs (net).
`VoucherSum_Net` Nullable	decimal	Net voucher sum.

Response example

<SqlRowSet1>
  <row>
    <InvoiceKey>734521</InvoiceKey>
    <InvoiceNr>GS2024000871</InvoiceNr>
    <InvDate>2024-05-14T10:22:31</InvDate>
    <CustRef>88123</CustRef>
    <AddrRef>145002</AddrRef>
    <total>-30.89</total>
    <Payment>U</Payment>
    <OrderCurrency>EUR</OrderCurrency>
    <RefToInvoiceNr>RE2024001234</RefToInvoiceNr>
    <RefToInvoiceKey>731990</RefToInvoiceKey>
    <TimesPrinted>1</TimesPrinted>
    <ReturnReason>Wrong size</ReturnReason>
    <ReturnClosed>Y</ReturnClosed>
    <CreateDTAUS>true</CreateDTAUS>
    <OrderType>B2C</OrderType>
  </row>
</SqlRowSet1>

Status	Message	Cause
RAISERROR	The VAT rate for selected shipping country was not entered in pixi* Control Center. Since setting "Force Country VAT" is enabled for this country, creation of [Order/Invoice/...] is not possible. Please add VAT rates for country {shipping country} in pixi* Control Center and try again.	No VAT rate is valid today for the shipping country of the invoice (taken from the first invoice line) and Force Country VAT is enabled. Add the VAT rates in the Control Center. The error is caught by the outer handler and logged.
RAISERROR	Error in [Pixi_InsertDefaultVATRateForCountry]: Unknown country code supplied on line {line}	The country could not be determined, e.g. because the first key in `InvoiceLines` does not exist, so no default VAT rate can be inserted. Pass valid invoice line keys.

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:pixiCreateReturn>
      <InvoiceNr>RE2024001234</InvoiceNr>
      <InvoiceLines>501234;501235;</InvoiceLines>
      <Quantities>1;2;</Quantities>
      <Prices>19.99;5.50;</Prices>
      <PaymentType>U</PaymentType>
      <!-- <Account>?</Account> -->
      <!-- <BLZ>?</BLZ> -->
      <AccountName>Max Mustermann</AccountName>
      <!-- <ShipCost>-4.90</ShipCost> -->
      <!-- <Note>Return via API</Note> -->
      <!-- <ReturnReason>Wrong size</ReturnReason> -->
      <!-- <CreateDate>?</CreateDate> -->
      <!-- <CreateEmp>API</CreateEmp> -->
      <!-- <InvoiceDate>?</InvoiceDate> -->
      <!-- <ReturnClosed>1</ReturnClosed> -->
      <!-- <ReturnCaseID>?</ReturnCaseID> -->
      <!-- <CreateDTAUS>1</CreateDTAUS> -->
      <!-- <DoNotRefund>0</DoNotRefund> -->
      <!-- <CAComment>Refund via bank transfer</CAComment> -->
      <!-- <IBAN>DE89370400440532013000</IBAN> -->
      <!-- <BIC>COBADEFFXXX</BIC> -->
      <!-- <LanguageId>2</LanguageId> -->
      <!-- <ReturnCostsAmount>3.00</ReturnCostsAmount> -->
      <!-- <Voucher_Amount>0</Voucher_Amount> -->
      <!-- <MarkReturnDocumentForPrinting>0</MarkReturnDocumentForPrinting> -->
    </ns1:pixiCreateReturn>
  </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:pixiCreateReturn>
      <InvoiceNr>RE2024001234</InvoiceNr>
      <InvoiceLines>501234;501235;</InvoiceLines>
      <Quantities>1;2;</Quantities>
      <Prices>19.99;5.50;</Prices>
      <PaymentType>U</PaymentType>
      <!-- <Account>?</Account> -->
      <!-- <BLZ>?</BLZ> -->
      <AccountName>Max Mustermann</AccountName>
      <!-- <ShipCost>-4.90</ShipCost> -->
      <!-- <Note>Return via API</Note> -->
      <!-- <ReturnReason>Wrong size</ReturnReason> -->
      <!-- <CreateDate>?</CreateDate> -->
      <!-- <CreateEmp>API</CreateEmp> -->
      <!-- <InvoiceDate>?</InvoiceDate> -->
      <!-- <ReturnClosed>1</ReturnClosed> -->
      <!-- <ReturnCaseID>?</ReturnCaseID> -->
      <!-- <CreateDTAUS>1</CreateDTAUS> -->
      <!-- <DoNotRefund>0</DoNotRefund> -->
      <!-- <CAComment>Refund via bank transfer</CAComment> -->
      <!-- <IBAN>DE89370400440532013000</IBAN> -->
      <!-- <BIC>COBADEFFXXX</BIC> -->
      <!-- <LanguageId>2</LanguageId> -->
      <!-- <ReturnCostsAmount>3.00</ReturnCostsAmount> -->
      <!-- <Voucher_Amount>0</Voucher_Amount> -->
      <!-- <MarkReturnDocumentForPrinting>0</MarkReturnDocumentForPrinting> -->
    </ns1:pixiCreateReturn>
  </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',
    'InvoiceLines' => '501234;501235;',
    'Quantities' => '1;2;',
    'Prices' => '19.99;5.50;',
    'PaymentType' => 'U',
    // 'Account' => '?',
    // 'BLZ' => '?',
    'AccountName' => 'Max Mustermann',
    // 'ShipCost' => '-4.90',
    // 'Note' => 'Return via API',
    // 'ReturnReason' => 'Wrong size',
    // 'CreateDate' => '?',
    // 'CreateEmp' => 'API',
    // 'InvoiceDate' => '?',
    // 'ReturnClosed' => '1',
    // 'ReturnCaseID' => '?',
    // 'CreateDTAUS' => '1',
    // 'DoNotRefund' => '0',
    // 'CAComment' => 'Refund via bank transfer',
    // 'IBAN' => 'DE89370400440532013000',
    // 'BIC' => 'COBADEFFXXX',
    // 'LanguageId' => '2',
    // 'ReturnCostsAmount' => '3.00',
    // 'Voucher_Amount' => '0',
    // 'MarkReturnDocumentForPrinting' => '0',
];

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

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

SQL object:: dbo.pipiCreateReturn