pixiGetInvoicePackageLines

Returns the lines of invoice packages (pixi.InvoicePackageLines). Each line links a package to an invoice line and a quantity.

Input and validation

At least one of InvoiceNumber, InvoiceId or PackageId must be given. An empty InvoiceNumber counts as not given.
InvoiceNumber is resolved to an invoice key. If both InvoiceId and InvoiceNumber are given, they must point to the same invoice.
If PackageId is given, the package must have package lines. If an invoice is also given, the package must belong to that invoice.
If an invoice is given, at least one package of that invoice must have package lines.
If a check fails, the call returns one Status/Message row with Status = Error. No further result set follows.

Selection

Only an invoice given: all packages of the invoice are used.
PackageId given, with or without an invoice: only this package is used.
Invoices are discarded if they have no invoice lines, or if a joined invoice line has no order line reference (OrderLineRef).
The call matches the package lines with the invoice lines of the selected invoices. It returns only lines whose item has an internal item number (pixi.ItemCode, code type 2).
InvoiceNr is the same value in all rows. It is the number of the selected invoice.

The result has no explicit sort order. If no line matches, the call returns an Info status row.

Notes

All reads use NOLOCK. Data that is being written at the same time can show up uncommitted.
A code comment says that these packages are created by the Delphi ShipOut process.
Pitfall: the invoice is discarded completely if any of its joined invoice lines has no OrderLineRef. In that case the call returns the Info row and no lines, even if the invoice has package lines.
Lines whose item has no internal item number (code type 2 in pixi.ItemCode) do not appear. If an item has several codes of type 2, its lines are returned more than once.
Error and info rows come back in the normal result set with the columns Status and Message. No SQL error is raised. The return code is 1 for errors and 0 otherwise.

Tables used

Invoices · Read InvoicePackage · Read InvoicePackageLines · Read Invlines · Read ItemCode · Read

Parameter	Type	Default	Description
`InvoiceNumber`	varchar(20) string	NULL	Invoice number (`InvoiceNr`). An empty string is treated as NULL. If `InvoiceId` is also given, both must refer to the same invoice. Example: `RE2024001234`
`InvoiceId`	int	NULL	Invoice key (`InvoiceKey`) of the invoice whose package lines are returned. Example: `58231`
`PackageId`	int	NULL	ID of an invoice package. If given, only the lines of this package are returned. If an invoice is also given, the package must belong to it. Example: `10452`

Result set 1 · On success, if at least one matching package line is found

One row per package line, with invoice, item and quantity.

Column	Type	Description
`PackageId`	int	ID of the invoice package.
`InvoiceId`	int	Invoice key of the invoice line.
`InvoiceNr` Nullable	varchar(20) string	Invoice number of the selected invoice. Same value in all rows.
`ItemKey`	int	Item key (`ItemRef` of the invoice line).
`ItemNrInt`	varchar(50) string	Internal item number, taken from `pixi.ItemCode` with code type 2.
`InvlineKey`	int	Key of the invoice line.
`Quantity` Nullable	int	Quantity of the invoice line in this package.

Result set 2 · If a validation check fails (`Error`) or if no matching lines are found (`Info`)

Status row instead of data.

Column	Type	Description
`Status`	varchar(5) string	`Error` or `Info`.
`Message`	varchar(max) string	Message text.

Response example

<SqlRowSet1>
  <row>
    <PackageId>10452</PackageId>
    <InvoiceId>58231</InvoiceId>
    <InvoiceNr>RE2024001234</InvoiceNr>
    <ItemKey>3315</ItemKey>
    <ItemNrInt>ART-100245</ItemNrInt>
    <InvlineKey>912877</InvlineKey>
    <Quantity>2</Quantity>
  </row>
  <row>
    <PackageId>10452</PackageId>
    <InvoiceId>58231</InvoiceId>
    <InvoiceNr>RE2024001234</InvoiceNr>
    <ItemKey>4120</ItemKey>
    <ItemNrInt>ART-200871</ItemNrInt>
    <InvlineKey>912878</InvlineKey>
    <Quantity>1</Quantity>
  </row>
</SqlRowSet1>

Status	Message	Cause
Error	At least one of the parameters needs to be provided	None of `InvoiceNumber`, `InvoiceId` and `PackageId` was given, or `InvoiceNumber` was empty. Provide at least one of them.
Error	No invoice found with invoice id: {InvoiceId} or invoice number: {InvoiceNumber}	The given invoice ID or invoice number does not exist in `Invoices`.
Error	No invoice package lines found with package Id: {PackageId}	The given `PackageId` has no package lines in `pixi.InvoicePackageLines`.
Error	Two different invoices found with provided parameters invoice Id: {InvoiceId} and invoice number: {InvoiceNumber}	`InvoiceId` and `InvoiceNumber` refer to different invoices. Pass only one of them, or pass matching values.
Error	Package Id: {PackageId} does not belong to invoice with invoice Id: {InvoiceId} or invoice number: {InvoiceNumber}	The given package belongs to a different invoice.
Error	No package lines found with package Id: {PackageId} or with invoice Id/invoice number: {InvoiceId}/{InvoiceNumber}	None of the packages of the given invoice has package lines.
Info	No records found with provided parameters	All checks passed, but no lines matched. For example, the invoice was discarded because of invoice lines without an order line reference, or the items have no internal item number.

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:pixiGetInvoicePackageLines>
      <!-- <InvoiceNumber>RE2024001234</InvoiceNumber> -->
      <!-- <InvoiceId>58231</InvoiceId> -->
      <!-- <PackageId>10452</PackageId> -->
    </ns1:pixiGetInvoicePackageLines>
  </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:pixiGetInvoicePackageLines>
      <!-- <InvoiceNumber>RE2024001234</InvoiceNumber> -->
      <!-- <InvoiceId>58231</InvoiceId> -->
      <!-- <PackageId>10452</PackageId> -->
    </ns1:pixiGetInvoicePackageLines>
  </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 = [
    // 'InvoiceNumber' => 'RE2024001234',
    // 'InvoiceId' => '58231',
    // 'PackageId' => '10452',
];

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

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

Notes

Tables used

Result set 1 · On success, if at least one matching package line is found

Result set 2 · If a validation check fails (Error) or if no matching lines are found (Info)

Response example

Result set 2 · If a validation check fails (`Error`) or if no matching lines are found (`Info`)