Items Write

pixiCreateItemSupplier

Creates a new supplier assignment (AddItems record) for an existing item, optionally with its own EAN and as preferred supplier.

Adds a supplier to an item by inserting a row into AddItems. The call returns exactly one status row (Status, Message).

Steps:

Supplier check: SupplNr is mandatory. An empty string counts as missing. The supplier must exist in Suppliers.
Item identification: the item is resolved from ItemKey, EAN (barcode, code type 1) and/or ItemNrInt (shop item number, code type 2) through pixi.ItemCode. At least one of them is required. If several are given, all of them must point to the same item. Each given value must be found.
NewEAN check: if NewEAN is given, it must not exist as a code of a scannable code type on any other item.
EAN of the new record: the EAN stored on the new supplier record is NewEAN. If that is empty, EAN is used. If both are empty, the item's EANUPC from items is used. ItemNrInt (if not given), IntLinkNR1, IntLinkNR and the sales Price are taken from the item.
Duplicate check: if the item already has a barcode entry in pixi.ItemCode with this EAN for this supplier, the call stops with an error.
Preferred supplier: if PreferredSupplier = 1, the item's current preferred supplier record (one record, TOP (1)) is reset to PreferredSupplier = 0 first.
Insert: a new AddItems row is created. It gets SupplPrice, ItemNrSuppl, SupplDeliveryTime (= ItemDeliveryTime), MinOrderQty, OrderOnlyPackageUnits (= OnlyPU), Active (= IsActive) and PreferredSupplier. AutoPrice is set to 0 and CreateDate to the current time.

On success the status is New record inserted. The message lists the stored supplier price, supplier item number and EAN.

Notes

All errors are returned as a normal row with Status = Error; no RAISERROR is used.
The reset of the old preferred supplier and the insert run in a transaction. If the call is already inside a transaction, a savepoint is used instead.
On an exception the transaction is rolled back and the error is logged via log.InsertError. The SQL error text is then returned in Message.
Lookups use NOLOCK, so uncommitted data may be read.
The duplicate check reads pixi.ItemCode, not AddItems. Whether pixi.ItemCode is kept in sync with AddItems (e.g. by triggers) is not visible in this code.
If no EAN can be determined at all (no NewEAN, no EAN, item without EANUPC), the duplicate check never matches. The record is then inserted with an empty EAN.
NewEAN only sets the EAN of the new supplier record. The item's main EAN in items is not changed.
ItemNrInt is not converted from an empty string to NULL. Sending an empty ItemNrInt together with another identifier stores an empty ItemNrInt on the new record.
ItemDeliveryTime is not replaced by 0 when NULL is sent explicitly.
Only one previously preferred record (TOP (1)) is reset. Other records flagged as preferred remain unchanged.

Parameters

Parameter	Type	Default	Description
`ItemKey`	int	NULL	Item key used to identify the item. 0 is treated as not given. Example: `10452`
`ItemNrInt`	varchar(50) string	NULL	Shop item number used to identify the item (code type 2). If omitted, the item's own `ItemNrInt` is stored on the new record. Example: `SHOP-10452`
`EAN`	varchar(13) string	NULL	Barcode used to identify the item (code type 1). It is also stored as the EAN of the new record if `NewEAN` is empty. Example: `4006381333931`
`NewEAN`	varchar(13) string	NULL	EAN to store on the new supplier record. It must not exist as a scannable code on another item. Example: `4006381333948`
`SupplNr` Required	varchar(4) string	–	Supplier number (mandatory). It must exist in `Suppliers`. Example: `L001`
`SupplPrice`	money decimal	0	Purchase price from this supplier. NULL becomes 0. Example: `12.50`
`ItemNrSuppl`	varchar(50) string	NULL	Supplier's item number. An empty string is stored as NULL. Example: `SUP-4711`
`PreferredSupplier`	bit boolean	0	Marks the new record as preferred supplier. The previous preferred supplier record is reset first. Allowed values `0` – Not preferred `1` – Preferred supplier; resets the current preferred supplier record Example: `1`
`MinOrderQty`	int	0	Minimum order quantity. NULL becomes 0. Example: `10`
`OnlyPU`	bit boolean	0	Order only in package units. Stored in `OrderOnlyPackageUnits`. Allowed values `0` – No restriction `1` – Order only package units Example: `0`
`ItemDeliveryTime`	int	0	Supplier delivery time. Stored in `SupplDeliveryTime`. Example: `5`
`IsActive`	bit boolean	1	Whether the supplier record is active. NULL becomes 1. Allowed values `1` – Active `0` – Inactive Example: `1`

Response

Result set 1 · Always (exactly one row: success or error)

Status row of the call.

Column	Type	Description
`Status`	varchar(19) string	`New record inserted` on success, `Error` otherwise.
`Message` Nullable	nvarchar(4000) string	On success, the stored supplier price, supplier item number and EAN. Otherwise the error text.

Response example

<SqlRowSet1>
  <row>
    <Status>New record inserted</Status>
    <Message>SupplPrice:12.50, ItemNrSuppl:SUP-4711, EAN:4006381333931</Message>
  </row>
</SqlRowSet1>

Error messages

Status	Message	Cause
Error	Parameter @SupplNr is mandatory.	`SupplNr` was not sent or is empty.
Error	Supplier with SupplNr:{SupplNr} does not exist.	No supplier with this number exists in `Suppliers`.
Error	Please specify at least one parameter.	None of `ItemKey`, `EAN` or `ItemNrInt` was given.
Error	No items found with given parameters. ItemKey: {ItemKey} not found.	The given `ItemKey` has no entry in `pixi.ItemCode`.
Error	No items found with given parameters. EAN: {EAN} not found.	The given `EAN` is not a barcode (code type 1) of any item.
Error	No items found with given parameters. ItemNrInt: {ItemNrInt} not found.	The given `ItemNrInt` is not a shop item number (code type 2) of any item.
Error	More than one item was identified with the given parameters.	The given identifiers point to different items. Send identifiers that belong to the same item.
Error	The @NewEAN code {NewEAN} already exists on another scannable item.	`NewEAN` is already used as a scannable code on another item.
Error	Item with EANUPC: {EAN} and Supplier number "{SupplNr}" already exists.	The item already has this EAN registered for this supplier.
Error	{ERROR_MESSAGE()}	An SQL error occurred while updating or inserting `AddItems` (e.g. a constraint violation). 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:pixiCreateItemSupplier>
      <!-- <ItemKey>10452</ItemKey> -->
      <!-- <ItemNrInt>SHOP-10452</ItemNrInt> -->
      <!-- <EAN>4006381333931</EAN> -->
      <!-- <NewEAN>4006381333948</NewEAN> -->
      <SupplNr>L001</SupplNr>
      <!-- <SupplPrice>12.50</SupplPrice> -->
      <!-- <ItemNrSuppl>SUP-4711</ItemNrSuppl> -->
      <!-- <PreferredSupplier>1</PreferredSupplier> -->
      <!-- <MinOrderQty>10</MinOrderQty> -->
      <!-- <OnlyPU>0</OnlyPU> -->
      <!-- <ItemDeliveryTime>5</ItemDeliveryTime> -->
      <!-- <IsActive>1</IsActive> -->
    </ns1:pixiCreateItemSupplier>
  </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:pixiCreateItemSupplier>
      <!-- <ItemKey>10452</ItemKey> -->
      <!-- <ItemNrInt>SHOP-10452</ItemNrInt> -->
      <!-- <EAN>4006381333931</EAN> -->
      <!-- <NewEAN>4006381333948</NewEAN> -->
      <SupplNr>L001</SupplNr>
      <!-- <SupplPrice>12.50</SupplPrice> -->
      <!-- <ItemNrSuppl>SUP-4711</ItemNrSuppl> -->
      <!-- <PreferredSupplier>1</PreferredSupplier> -->
      <!-- <MinOrderQty>10</MinOrderQty> -->
      <!-- <OnlyPU>0</OnlyPU> -->
      <!-- <ItemDeliveryTime>5</ItemDeliveryTime> -->
      <!-- <IsActive>1</IsActive> -->
    </ns1:pixiCreateItemSupplier>
  </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' => '10452',
    // 'ItemNrInt' => 'SHOP-10452',
    // 'EAN' => '4006381333931',
    // 'NewEAN' => '4006381333948',
    'SupplNr' => 'L001',
    // 'SupplPrice' => '12.50',
    // 'ItemNrSuppl' => 'SUP-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('pixiCreateItemSupplier', $soapArguments);

Tables used

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

SQL object:: dbo.pipiCreateItemSupplier