Returns Read

pixiGetReturnItems

Returns the lines of a return case with item, invoice line, bin location and translated reason and quality classification.

Returns all return lines (pixi.ReturnLine) that belong to the return case given in ReturnId.

Steps:

Loads the translations of code table values (DBObjectName = 'CodeTableValue') for the language LanguageId into a temporary table.
Selects every return line of the return case and joins:
- the invoice line (Invlines) and its item (items), which provide item number, EAN, name and internal item number,
- the return reason code (ReasonId) with its code and translated description,
- the item quality classification code (ItemQualityClassificationId) with its code and translated description,
- the bin item log entry (InvBinItemLog), which provides quantity, location, bin, best-before date and batch number.

All joins are inner joins, so a line only appears if all referenced records exist and both the reason and the classification have a translation in the requested language.

No explicit sort order is applied.

Notes

Most tables are read with NOLOCK, so uncommitted data may be returned; the join to the quality classification (CodeTableValue as ct2) is read without NOLOCK.
If a reason or classification has no translation for the given LanguageId, the whole line is missing from the result. Use a language for which translations exist (default 1).
The temporary table is dropped via util.TempTableDrop, whose definition is not attached.
If the return case does not exist or has no lines, an empty result set is returned; no error is raised.

Tables used

Translation · Read ReturnLine · Read Invlines · Read items · Read CodeTableValue · Read InvBinItemLog · Read

Parameter	Type	Default	Description
`ReturnId` Required	int	–	ID of the return case whose lines are returned. Example: `1042`
`LanguageId`	int	1	Language ID used for the translated descriptions of the return reason and the item quality classification. Example: `1`

Result set 1 · Always (may be empty)

One row per return line of the return case.

Column	Type	Description
`Id`	int	ID of the return line.
`ReasonId`	int	ID of the return reason code table value.
`ReasonDescription` Nullable	string	Translated description of the return reason in the requested language.
`ItemId`	int	Internal item key (`ItemKey`) of the returned item.
`EANUPC` Nullable	string	EAN/UPC of the item.
`ItemName` Nullable	string	Name of the item.
`Quantity` Nullable	int	Quantity from the linked bin item log entry.
`LocationCode` Nullable	string	Location (warehouse) of the bin item log entry.
`BinId` Nullable	int	Reference to the bin (Lagerplatz) of the bin item log entry.
`BestBeforeDate` Nullable	dateTime	Best-before date from the bin item log entry.
`BatchNumber` Nullable	string	Batch number (Charge) from the bin item log entry.
`InvoiceLineId`	int	ID of the invoice line the return line refers to.
`IsSpecialCase` Nullable	boolean	Flag whether the return line is marked as special case.
`CustomerComment` Nullable	string	Comment of the customer on the return line.
`UserComment` Nullable	string	Comment of the user on the return line.
`RecordModifiedOn` Nullable	dateTime	Last modification time of the return line.
`ItemNrInt` Nullable	string	Internal item number of the item.
`ReasonCode` Nullable	string	Code of the return reason.
`ItemClassification` Nullable	string	Code of the item quality classification.
`ItemClassificationDescription` Nullable	string	Translated description of the item quality classification in the requested language.

Response example

<SqlRowSet1>
  <row>
    <Id>5531</Id>
    <ReasonId>12</ReasonId>
    <ReasonDescription>Item does not fit</ReasonDescription>
    <ItemId>20871</ItemId>
    <EANUPC>4006381333931</EANUPC>
    <ItemName>Running shirt blue M</ItemName>
    <Quantity>1</Quantity>
    <LocationCode>001</LocationCode>
    <BinId>3345</BinId>
    <BestBeforeDate>2026-12-31T00:00:00</BestBeforeDate>
    <BatchNumber>B2024-117</BatchNumber>
    <InvoiceLineId>889120</InvoiceLineId>
    <IsSpecialCase>false</IsSpecialCase>
    <CustomerComment>Too small</CustomerComment>
    <UserComment>Checked, as new</UserComment>
    <RecordModifiedOn>2024-05-14T10:22:31</RecordModifiedOn>
    <ItemNrInt>RS-BLU-M</ItemNrInt>
    <ReasonCode>SIZE</ReasonCode>
    <ItemClassification>A</ItemClassification>
    <ItemClassificationDescription>As new</ItemClassificationDescription>
  </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:pixiGetReturnItems>
      <ReturnId>1042</ReturnId>
      <!-- <LanguageId>1</LanguageId> -->
    </ns1:pixiGetReturnItems>
  </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:pixiGetReturnItems>
      <ReturnId>1042</ReturnId>
      <!-- <LanguageId>1</LanguageId> -->
    </ns1:pixiGetReturnItems>
  </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 = [
    'ReturnId' => '1042',
    // 'LanguageId' => '1',
];

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

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

SQL object:: dbo.pipiGetReturnItems