Suppliers Write

pixiUpdateItemSupplier

Updates one supplier item record (AddItems) of an article: barcode, supplier price, supplier item number, preferred flag, MOQ, delivery time, active flag.

Updates exactly one supplier item row in AddItems. A supplier item is the combination of article, supplier and barcode (EANUPC).

Processing steps:

SupplNr is mandatory. An empty string is treated as missing. The supplier is looked up in Suppliers by SupplNr, and an error row is returned if it does not exist.
At least one update parameter must be set: NewEAN, SupplPrice, ItemNrSuppl, PreferredSupplier, MinOrderQty, OnlyPU, ItemDeliveryTime or IsActive. For NewEAN and ItemNrSuppl, an empty string counts as not set.
The article is identified from ItemKey, EAN (barcode, code type 1) and/or ItemNrInt (shop item number, code type 2) via pixi.ItemCode. Each identifier that is passed must exist, and all of them must point to the same article. ItemKey = 0 counts as not set.
If NewEAN is passed, it must not already exist as a scannable code of another article.
All supplier items of the article are loaded. The row to update is chosen by the resolved supplier and, if EAN is passed, by EANUPC = EAN. Exactly one row must match. If the supplier has several barcodes for the article, pass EAN to select the right row.
If NewEAN is passed, no other supplier item of the same article may already have this barcode for the same SupplNr.
If PreferredSupplier = 1, every other supplier item of the article that is currently preferred is set to PreferredSupplier = 0.
The update runs in a transaction.

On the target row, only the passed values are changed. Values that are not passed keep their current content. In addition:

UpdateDate is set to the current time.
RowVersion is increased by 1.
SupplPriceUpdateDate is set to now if the new SupplPrice differs from the stored price.

The call returns one status row (Status, Message). On success, Status is Record updated and Message contains the resulting price, supplier item number and barcode of the target row.

Notes

Clearing values is not possible. Parameters that are NULL, and empty strings for NewEAN and ItemNrSuppl, keep the existing value. A field cannot be set to NULL with this call.
EAN has two roles. It identifies the article: it must exist as a barcode (code type 1) in pixi.ItemCode. It also selects the AddItems row by EANUPC.
NewEAN changes only AddItems.EANUPC. The procedure does not update pixi.ItemCode directly. Whether triggers sync the code is not visible in this code.
PreferredSupplier = 0 only clears the flag on the target row. No other supplier is made preferred.
SupplPriceUpdateDate is not updated if the stored price was NULL, because the comparison SupplPrice <> @SupplPrice is not true then.
NULL messages. If no supplier item is found and ItemKey was not passed, the error Message becomes NULL because of string concatenation with NULL. The same happens with the success Message if the resulting SupplPrice is NULL.
Locking. Reads on Suppliers, pixi.ItemCode and AddItems use NOLOCK. The update uses ROWLOCK.
Transactions. The procedure opens its own transaction, or sets a savepoint if a transaction is already open. On error it rolls back, writes a log entry through log.InsertError (definition not attached) and returns ERROR_MESSAGE() as a status row.
Supplier lookup. It uses TOP (1) on Suppliers by SupplNr.

Parameters

Parameter	Type	Default	Description
`ItemKey`	int	NULL	Internal article ID used to identify the article. 0 is treated as not set. Example: `12345`
`ItemNrInt`	varchar(50) string	NULL	Shop item number (code type 2 in `pixi.ItemCode`) used to identify the article. Example: `ART-1001`
`EAN`	varchar(13) string	NULL	Current barcode. Identifies the article (code type 1) and selects the supplier item row by `EANUPC`. Needed when the supplier has several barcodes for the article. Example: `4006381333931`
`NewEAN`	varchar(13) string	NULL	New barcode for `EANUPC`. It must not exist as a scannable code of another article, nor on another supplier item of the same article and supplier. An empty string keeps the current value. Example: `4006381333948`
`SupplNr` Required	varchar(4) string	–	Supplier number. Mandatory. Must exist in `Suppliers`. Example: `L001`
`SupplPrice`	money decimal	NULL	New supplier (purchase) price. If it differs from the stored price, `SupplPriceUpdateDate` is set to now. Example: `12.50`
`ItemNrSuppl`	varchar(50) string	NULL	New supplier item number. An empty string keeps the current value. Example: `LF-4711`
`PreferredSupplier`	bit boolean	NULL	Sets or clears the preferred supplier flag. Allowed values `1` – Mark as preferred supplier; other preferred supplier items of the article are reset to 0. `0` – Clear the preferred flag on this supplier item only. Example: `1`
`MinOrderQty`	int	NULL	New minimum order quantity. Example: `10`
`OnlyPU`	bit boolean	NULL	New value for `OrderOnlyPackageUnits` (order only in package units). Allowed values `1` – Order only in package units. `0` – No package unit restriction. Example: `0`
`ItemDeliveryTime`	int	NULL	New supplier delivery time (`SupplDeliveryTime`). Example: `5`
`IsActive`	bit boolean	NULL	Sets the `Active` flag of the supplier item. Allowed values `1` – Supplier item is active. `0` – Supplier item is inactive. Example: `1`

Response

Result set 1 · On a validation error or an error during the update (instead of the success row)

A single status row describing the error.

Column	Type	Description
`Status`	varchar(5) string	Always `Error`.
`Message` Nullable	varchar string	Error text. Can be NULL in some cases (see notes).

Result set 2 · On success

One row for the updated target supplier item.

Column	Type	Description
`Status`	varchar(14) string	Always `Record updated`.
`Message` Nullable	varchar string	Resulting values in the form `SupplPrice:…, ItemNrSuppl:…, EANUPC:…`. NULL if the resulting price is NULL.

Response example

<SqlRowSet1>
  <row>
    <Status>Record updated</Status>
    <Message>SupplPrice:12.50, ItemNrSuppl:LF-4711, EANUPC:4006381333931</Message>
  </row>
</SqlRowSet1>

Error messages

Status	Message	Cause
Error	Parameter @SupplNr is mandatory.	`SupplNr` is missing or empty.
Error	Supplier with SupplNr:{SupplNr} does not exist.	No supplier with this number exists in `Suppliers`.
Error	At least one update parameter must be provided (@NewEAN, @SupplPrice, @ItemNrSuppl, @PreferredSupplier, @MinOrderQty, @OnlyPU, @ItemDeliveryTime, or @IsActive).	No value to change was passed.
Error	Please specify at least one parameter.	None of `ItemKey`, `EAN` or `ItemNrInt` was passed.
Error	No items found with given parameters. ItemKey: {ItemKey} not found.	`ItemKey` does not exist in `pixi.ItemCode`.
Error	No items found with given parameters. EAN: {EAN} not found.	`EAN` does not exist as a barcode (code type 1).
Error	No items found with given parameters. ItemNrInt: {ItemNrInt} not found.	`ItemNrInt` does not exist as a shop item number (code type 2).
Error	More than one item was identified with the given parameters.	The passed identifiers point to different articles.
Error	The @NewEAN code {NewEAN} already exists on another scannable item.	`NewEAN` is already used as a scannable code of another article.
Error	More than one supplier item found for ItemKey:{ItemId}, SupplNr: {SupplNr}. Use @EAN to identify the exact record.	The supplier has several supplier items (barcodes) for the article. Pass `EAN` to select one.
Error	No supplier item found with given parameters. ItemKey:{ItemKey}, SupplNr: {SupplNr}, EAN: {EAN\|not provided}.	No `AddItems` row exists for this article, supplier and (optional) EAN. The message is NULL if `ItemKey` was not passed.
Error	Item with EANUPC: {NewEAN} and Supplier number "{SupplNr}" already exists.	Another supplier item of the same article already has `NewEAN` for this supplier.
Error	{ERROR_MESSAGE()}	An SQL error occurred during the update. The transaction is rolled back and the error is logged.

Examples

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:pixiUpdateItemSupplier>
      <!-- <ItemKey>12345</ItemKey> -->
      <!-- <ItemNrInt>ART-1001</ItemNrInt> -->
      <!-- <EAN>4006381333931</EAN> -->
      <!-- <NewEAN>4006381333948</NewEAN> -->
      <SupplNr>L001</SupplNr>
      <!-- <SupplPrice>12.50</SupplPrice> -->
      <!-- <ItemNrSuppl>LF-4711</ItemNrSuppl> -->
      <!-- <PreferredSupplier>1</PreferredSupplier> -->
      <!-- <MinOrderQty>10</MinOrderQty> -->
      <!-- <OnlyPU>0</OnlyPU> -->
      <!-- <ItemDeliveryTime>5</ItemDeliveryTime> -->
      <!-- <IsActive>1</IsActive> -->
    </ns1:pixiUpdateItemSupplier>
  </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:pixiUpdateItemSupplier>
      <!-- <ItemKey>12345</ItemKey> -->
      <!-- <ItemNrInt>ART-1001</ItemNrInt> -->
      <!-- <EAN>4006381333931</EAN> -->
      <!-- <NewEAN>4006381333948</NewEAN> -->
      <SupplNr>L001</SupplNr>
      <!-- <SupplPrice>12.50</SupplPrice> -->
      <!-- <ItemNrSuppl>LF-4711</ItemNrSuppl> -->
      <!-- <PreferredSupplier>1</PreferredSupplier> -->
      <!-- <MinOrderQty>10</MinOrderQty> -->
      <!-- <OnlyPU>0</OnlyPU> -->
      <!-- <ItemDeliveryTime>5</ItemDeliveryTime> -->
      <!-- <IsActive>1</IsActive> -->
    </ns1:pixiUpdateItemSupplier>
  </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 = [
    // 'ItemKey' => '12345',
    // 'ItemNrInt' => 'ART-1001',
    // 'EAN' => '4006381333931',
    // 'NewEAN' => '4006381333948',
    'SupplNr' => 'L001',
    // 'SupplPrice' => '12.50',
    // 'ItemNrSuppl' => 'LF-4711',
    // 'PreferredSupplier' => '1',
    // 'MinOrderQty' => '10',
    // 'OnlyPU' => '0',
    // 'ItemDeliveryTime' => '5',
    // 'IsActive' => '1',
];

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

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

Tables used

Suppliers · Read ItemCode · Read ItemCodeType · Read AddItems · Write

SQL object:: dbo.pipiUpdateItemSupplier