pixiSetHistoricalSupplierPrice · Pixi API documentation

Manually sets the supplier price of one historical (FIFO) inbound price record, identified by InvBinItemLogId.

Steps:

All four parameters are mandatory. If any of them is missing, an error row is returned.
A negative SupplierPrice is rejected.
SupplierId must exist as SuppliersKey in Suppliers.
UserName must exist as USER_NAME in UCS_USERS. The matching user ID is stored as the modifying user.
The record InvBinItemLogId must exist in InvBinItemLog_In_Price.
Only records without a DeliveryId can be changed. Records already linked to a delivery are rejected.
The record is updated:
- Price is set to SupplierPrice.
- SupplierId is set to SupplierId.
- DeliveryId and DeliveryId_Assigned are set to NULL.
- QuerySource is set to 5 (manual update via API).
- RecordModifiedByUserId and RecordModifiedOn are set; the timestamp is the current time as smalldatetime.

The call returns exactly one row with a status and a message, either an error or a success confirmation.

Notes

Column name pitfall: Validation error rows use the column Status. The success row and the error row from the CATCH block use the misspelled column Staus. Clients should check both column names.
The header comment of the procedure ("Returns FIFO supplier prices") does not match its behaviour. The procedure updates data.
The procedure opens its own transaction, or a savepoint if the caller already has an open transaction.
The validation checks return before a commit or rollback. If no outer transaction exists, the call can end with the transaction still open. SQL Server may then report a transaction count mismatch.
If a runtime error occurs, the transaction or savepoint is rolled back. The error is logged via log.InsertError (not attached).
Lookups use NOLOCK. The update uses ROWLOCK.
In the success message the price is formatted as money cast to varchar, i.e. with two decimal places.

Tables used

Suppliers · Read UCS_USERS · Read InvBinItemLog_In_Price · Write

Parameter	Type	Default	Description
`InvBinItemLogId`	int	NULL	ID of the historical inbound price record in `InvBinItemLog_In_Price`. Effectively mandatory. Example: `123456`
`SupplierPrice`	money decimal	NULL	New supplier price. Must not be negative. Effectively mandatory. Example: `12.50`
`SupplierId`	int	NULL	Supplier key (`SuppliersKey`) to store on the record. Must exist. Effectively mandatory. Example: `17`
`UserName`	varchar(30) string	NULL	Pixi user name (`USER_NAME` in `UCS_USERS`) recorded as the modifying user. Effectively mandatory. Example: `api_user`

Result set 1 · If a validation check fails

Error row from parameter or record validation. No data is changed.

Column	Type	Description
`Status`	varchar(5) string	Always `Error`.
`Message`	varchar string	Error description.

Result set 2 · On success

Confirmation row after the record was updated. Note the column name Staus.

Column	Type	Description
`Staus`	varchar(7) string	Always `Success` (misspelled column name).
`Message`	varchar string	Confirmation text with the price and the record ID.

Result set 3 · If a runtime error occurs (CATCH block)

Error row with the SQL Server error message. The changes are rolled back.

Column	Type	Description
`Staus`	varchar(5) string	Always `Error` (misspelled column name).
`Message` Nullable	nvarchar(4000) string	Result of `ERROR_MESSAGE()`.

Response example

<SqlRowSet1>
  <row>
    <Staus>Success</Staus>
    <Message>Price (12.50) successfully updated for record: 123456.</Message>
  </row>
</SqlRowSet1>

Status	Message	Cause
Error	All parameters are mandatory.	At least one of `InvBinItemLogId`, `SupplierPrice`, `SupplierId`, `UserName` is missing or NULL. Pass all four parameters.
Error	Negative supplier price is not allowed.	`SupplierPrice` is less than 0. Pass a price of 0 or higher.
Error	Supplier with SupplierId: {SupplierId} does not exist.	No supplier with this `SuppliersKey` exists. Use a valid supplier key.
Error	User with UserName: "{UserName}" does not exist.	No user with this `USER_NAME` exists in `UCS_USERS`. Use an existing Pixi user name.
Error	Historical supplier price record with InvBinItemLogId: {InvBinItemLogId} does not exist.	No record with this ID exists in `InvBinItemLog_In_Price`.
Error	Only records that do not have a delivery already assigned can be updated.	The record already has a `DeliveryId`. Only records without a delivery can be changed manually.
Error	{ERROR_MESSAGE()}	An unexpected SQL error occurred during processing. The text is returned in column `Staus`/`Message`, and the error is logged.

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:pixiSetHistoricalSupplierPrice>
      <!-- <InvBinItemLogId>123456</InvBinItemLogId> -->
      <!-- <SupplierPrice>12.50</SupplierPrice> -->
      <!-- <SupplierId>17</SupplierId> -->
      <!-- <UserName>api_user</UserName> -->
    </ns1:pixiSetHistoricalSupplierPrice>
  </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:pixiSetHistoricalSupplierPrice>
      <!-- <InvBinItemLogId>123456</InvBinItemLogId> -->
      <!-- <SupplierPrice>12.50</SupplierPrice> -->
      <!-- <SupplierId>17</SupplierId> -->
      <!-- <UserName>api_user</UserName> -->
    </ns1:pixiSetHistoricalSupplierPrice>
  </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 = [
    // 'InvBinItemLogId' => '123456',
    // 'SupplierPrice' => '12.50',
    // 'SupplierId' => '17',
    // 'UserName' => 'api_user',
];

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

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