Suppliers Write

pixiSupplierOrderCreate

Creates a supplier order or pre-order from an XML document for a supplier, delivery location and order location.

Creates a supplier order (Type = 1) or a pre-order (Type = 2). The actual creation is delegated to the internal procedure pixi.SupplierOrderCreate. This wrapper resolves the input values and formats the response.

Steps:

Any Type other than 2 is treated as 1.
For supplier orders (Type = 1), OrderConsiderationStartDate must be empty. Otherwise an error row is returned and nothing is created.
A transaction is started. If the caller already has an open transaction, a savepoint is set instead.
UserId is resolved from UserName if not given. Unknown user names fall back to the user pixiAdministration.
The supplier is resolved by SupplierId (Suppliers.SuppliersKey), or by SupplierNr (SupplNr) if SupplierId is NULL.
Delivery location and order location are resolved by their key (DeliveryLocationId, OrderLocationId), or by the location code LocID (DeliveryLocation, OrderLocation) if the key is NULL. A given ID that does not exist resolves to NULL. The code then does not fall back to the code value.
Input values are normalised:
- ConsiderPackageUnits and ConsiderMinOrderQty: NULL becomes 0.
- EstimatedDeliveryDate and OrderConsiderationStartDate: the time part is removed.
- ExternalCode: an empty string becomes NULL.
pixi.SupplierOrderCreate is executed with the order document SupplOrderDocument and all resolved values.
If ShowDetailedReturn = 1, a detail result set is returned. It has one row per item line of the input and shows how each line was matched. The status is one of OK, Item not found, Item does not have the correct supplier assigned or Item number supplier is missing for the item. The row also contains the created order and order line if one exists. If ShowDetailedReturn = 0, the inner procedure is asked to return the created IDs instead.
If the call opened its own transaction, it is committed. After the commit, a ReturnCode/ErrorMessage row is returned if the supplier, delivery location or order location could not be resolved.

Notes

The structure of SupplOrderDocument and the actual inserts are defined in pixi.SupplierOrderCreate, which is not attached. The XML layout, the result set with the created IDs and the tables written cannot be derived from this code. The detail output suggests that lines identify items by internal item number (matched against pixi.ItemCode with CodeTypeId = 2), by supplier item number or by item ID.
Supplier and locations are validated only after the inner procedure has run and the transaction has been committed. Their validation message is therefore not a guarantee that nothing was written.
If the call runs inside an outer transaction, no validation message is returned at all. The commit is then also left to the caller.
The validation row uses the ReturnCode value Error with a trailing space, while other errors use ERROR.
In detailed mode, the created order is determined as MAX(Id) of pixi.SupplierOrder. Under concurrent order creation, this may refer to a different order.
Lookups use NOLOCK.
Errors are caught. The transaction or savepoint is rolled back, and the error is returned as a ReturnCode = ERROR row, not as a SQL error.
AppName is required but not used by the code.

Tables used

Suppliers · Read Location · Read SupplierOrder · Read SupplierOrderLine · Read ItemCode · Read items · Read UCS_USERS · Read

Parameter	Type	Default	Description
`AppName` Required	varchar(255) string	–	Name of the calling application. Not used by the code. Example: `ShopConnector`
`UserId`	int	NULL	ID of the Pixi user. If NULL, it is resolved from `UserName`. Example: `12`
`UserName` Required	varchar(50) string	–	Pixi user name. Used only if `UserId` is NULL. Unknown names fall back to `pixiAdministration`. Example: `jdoe`
`SupplierId`	int	NULL	Supplier key (`SuppliersKey`). Takes precedence over `SupplierNr`. Example: `5`
`SupplierNr`	varchar(4) string	NULL	Supplier number (`SupplNr`). Used if `SupplierId` is NULL. Example: `L001`
`DeliveryLocation`	varchar(3) string	NULL	Location code (`LocID`) of the delivery and billing location. Used if `DeliveryLocationId` is NULL. Example: `001`
`DeliveryLocationId`	int	NULL	Location key of the delivery and billing location. Takes precedence over `DeliveryLocation`. Example: `1`
`OrderLocation`	varchar(3) string	NULL	Location code (`LocID`) of the order location. Used if `OrderLocationId` is NULL. Example: `001`
`OrderLocationId`	int	NULL	Location key of the order location. Takes precedence over `OrderLocation`. Example: `1`
`SupplOrderDocument` Required	xml	–	XML document with the order lines. It is parsed by `pixi.SupplierOrderCreate`, which is not attached, so the exact structure is not known from this code.
`ConsiderPackageUnits`	bit boolean	0	Whether package units are taken into account for the quantities. NULL is treated as 0. Allowed values `0` – No `1` – Yes Example: `0`
`ConsiderMinOrderQty`	bit boolean	0	Whether the minimum order quantity is taken into account. NULL is treated as 0. Allowed values `0` – No `1` – Yes Example: `0`
`ExternalCode`	nvarchar(50) string	NULL	External reference of the order. An empty string is treated as NULL. Example: `PO-2024-0815`
`EstimatedDeliveryDate`	datetime dateTime	NULL	Estimated delivery date. The time part is removed. Example: `2024-06-15T00:00:00`
`ShowDetailedReturn`	bit boolean	0	1 returns a detail row per input line with match status. 0 lets the inner procedure return the created IDs. Allowed values `0` – Return created IDs (from inner procedure) `1` – Return detailed per-line status Example: `1`
`Type`	int	1	Order type. Any value other than 2 is treated as 1. Allowed values `1` – Supplier order `2` – Pre-order Example: `1`
`OrderConsiderationStartDate`	datetime dateTime	NULL	Start date for order consideration. Only allowed for pre-orders (`Type` = 2). The time part is removed.

Result set 1 · If `Type` = 1 and `OrderConsiderationStartDate` is set

Validation error row. Nothing is created.

Column	Type	Description
`ReturnCode`	varchar(5) string	Always `ERROR`.
`ErrorMessage`	varchar(57) string	Error text.

Result set 2 · If `ShowDetailedReturn` = 0 (returned by `pixi.SupplierOrderCreate`)

Created IDs as returned by the inner procedure. The columns are not known from this code.

Result set 3 · If `ShowDetailedReturn` = 1 and no error occurred

One row per item line of the input document, with match status and the created order line.

Column	Type	Description
`SupplierOrderId` Nullable	int	ID of the created supplier order. NULL if no line was created for the item.
`InternalNumber` Nullable	string	Internal number of the supplier order.
`SupplierOrderlineId` Nullable	int	ID of the created order line.
`ItemId` Nullable	int	Matched item ID.
`ItemName` Nullable	string	Item name from the order line, otherwise from the item master.
`ItemNrSuppl` Nullable	varchar(50) string	Supplier item number.
`ItemNrInt` Nullable	varchar(50) string	Internal item number from the input.
`StatusMessage` Nullable	varchar(100) string	`OK`, `Item not found`, `Item does not have the correct supplier assigned` or `Item number supplier is missing for the item`.
`ItemCodeInternal` Nullable	string	Internal item code stored on the order line.

Result set 4 · Without outer transaction, if supplier, delivery or order location could not be resolved

Validation message row, returned after the commit.

Column	Type	Description
`ReturnCode`	varchar(255) string	`Error` (with trailing space).
`ErrorMessage`	varchar(255) string	Which value could not be matched.

Result set 5 · If an exception occurs

Error row after rollback.

Column	Type	Description
`ReturnCode`	varchar(5) string	Always `ERROR`.
`ErrorMessage` Nullable	nvarchar(4000) string	SQL error message.

Response example

<SqlRowSet1>
  <row>
    <SupplierOrderId>1534</SupplierOrderId>
    <InternalNumber>SO-001534</InternalNumber>
    <SupplierOrderlineId>8821</SupplierOrderlineId>
    <ItemId>4711</ItemId>
    <ItemName>Kaffeetasse weiß</ItemName>
    <ItemNrSuppl>KT-100</ItemNrSuppl>
    <ItemNrInt>100200</ItemNrInt>
    <StatusMessage>OK</StatusMessage>
    <ItemCodeInternal>100200</ItemCodeInternal>
  </row>
  <row>
    <ItemNrInt>999999</ItemNrInt>
    <StatusMessage>Item not found</StatusMessage>
  </row>
</SqlRowSet1>

Status	Message	Cause
ERROR	OrderConsiderationStartDate is only valid for Pre-Orders	`OrderConsiderationStartDate` was passed while `Type` is not 2. Omit the date or use `Type` = 2.
Error	Match value for parameter Supplier does not exists in db.	Neither `SupplierId` nor `SupplierNr` matches an existing supplier.
Error	Match value for parameter delivery location does not exists in db.	`DeliveryLocationId` or `DeliveryLocation` does not match an existing location.
Error	Match value for parameter order location does not exists in db.	`OrderLocationId` or `OrderLocation` does not match an existing location.
ERROR	{ERROR_MESSAGE()}	Any SQL error during processing, for example invalid XML or an error raised by `pixi.SupplierOrderCreate`. The transaction is rolled back.

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:pixiSupplierOrderCreate>
      <AppName>ShopConnector</AppName>
      <!-- <UserId>12</UserId> -->
      <UserName>jdoe</UserName>
      <!-- <SupplierId>5</SupplierId> -->
      <!-- <SupplierNr>L001</SupplierNr> -->
      <!-- <DeliveryLocation>001</DeliveryLocation> -->
      <!-- <DeliveryLocationId>1</DeliveryLocationId> -->
      <!-- <OrderLocation>001</OrderLocation> -->
      <!-- <OrderLocationId>1</OrderLocationId> -->
      <SupplOrderDocument><![CDATA[?]]></SupplOrderDocument>
      <!-- <ConsiderPackageUnits>0</ConsiderPackageUnits> -->
      <!-- <ConsiderMinOrderQty>0</ConsiderMinOrderQty> -->
      <!-- <ExternalCode>PO-2024-0815</ExternalCode> -->
      <!-- <EstimatedDeliveryDate>2024-06-15T00:00:00</EstimatedDeliveryDate> -->
      <!-- <ShowDetailedReturn>1</ShowDetailedReturn> -->
      <!-- <Type>1</Type> -->
      <!-- <OrderConsiderationStartDate>?</OrderConsiderationStartDate> -->
    </ns1:pixiSupplierOrderCreate>
  </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:pixiSupplierOrderCreate>
      <AppName>ShopConnector</AppName>
      <!-- <UserId>12</UserId> -->
      <UserName>jdoe</UserName>
      <!-- <SupplierId>5</SupplierId> -->
      <!-- <SupplierNr>L001</SupplierNr> -->
      <!-- <DeliveryLocation>001</DeliveryLocation> -->
      <!-- <DeliveryLocationId>1</DeliveryLocationId> -->
      <!-- <OrderLocation>001</OrderLocation> -->
      <!-- <OrderLocationId>1</OrderLocationId> -->
      <SupplOrderDocument><![CDATA[?]]></SupplOrderDocument>
      <!-- <ConsiderPackageUnits>0</ConsiderPackageUnits> -->
      <!-- <ConsiderMinOrderQty>0</ConsiderMinOrderQty> -->
      <!-- <ExternalCode>PO-2024-0815</ExternalCode> -->
      <!-- <EstimatedDeliveryDate>2024-06-15T00:00:00</EstimatedDeliveryDate> -->
      <!-- <ShowDetailedReturn>1</ShowDetailedReturn> -->
      <!-- <Type>1</Type> -->
      <!-- <OrderConsiderationStartDate>?</OrderConsiderationStartDate> -->
    </ns1:pixiSupplierOrderCreate>
  </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 = [
    'AppName' => 'ShopConnector',
    // 'UserId' => '12',
    'UserName' => 'jdoe',
    // 'SupplierId' => '5',
    // 'SupplierNr' => 'L001',
    // 'DeliveryLocation' => '001',
    // 'DeliveryLocationId' => '1',
    // 'OrderLocation' => '001',
    // 'OrderLocationId' => '1',
    'SupplOrderDocument' => '?',
    // 'ConsiderPackageUnits' => '0',
    // 'ConsiderMinOrderQty' => '0',
    // 'ExternalCode' => 'PO-2024-0815',
    // 'EstimatedDeliveryDate' => '2024-06-15T00:00:00',
    // 'ShowDetailedReturn' => '1',
    // 'Type' => '1',
    // 'OrderConsiderationStartDate' => '?',
];

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

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

SQL object:: dbo.pipiSupplierOrderCreate