Database diagram
Dependency diagram for the CSV output format, including SQL scripts for generating the tables and to load the data with load data local infile at the end of this article.
Overview of data format elements
In CSV output format, these elements are files, in XML or JSON output format these are nodes.
The data format standardUpdate consists of the following elements:
- Product: product from the ITscope catalogue.
- SupplierItem: source of supply for an ITscope product. A specific product offering from a distributor listed on ITscope.
- Project: price, availability and further information on a project item.
Elements marked with a globe vary depending on the selected export language:
product
Product from the ITscope catalogue.
| Name | Compulsory field | Data type | Description |
|---|---|---|---|
| puid | long | Unique key | |
| price | BigDecimal | Price basis for the calculated price | |
| priceCalc | BigDecimal | Calculated price, based on the individual price calculation | |
| currencyCode | String (3) | Currency unit that applies to this price information | |
| priceCalcVat | BigDecimal | Sales tax rate that was used to determine the calculated price | |
| priceLastUpdate | Date | Time of last update of price information | |
| priceSupplierId | long | Reference to a supplier (1:1) who has provided the source of supply with this price information | |
| priceSupplierName | String (255) | Name of the supplier that provided this source of supply | |
| priceSupplierItemId | long | Reference to a source of supply (n:1); if this field is zero, then the price information refers to a product (source of supply reference and product reference are mutually exclusive) | |
| priceSupplierSKU | String (150) | Product identifier given by the supplier that provided this source of supply [was String(40) before 18/10/2021] | |
| stockSupplierText | String (40) | Textual inventory information of the supplier, directly taken over, without interpretation | |
| stockStatus | Integer | Numeric key of the delivery status of the inventory information | |
 stockStatusText | String (255) | Delivery status of inventory information, such as ‘in stock’ or ‘in field warehouse’ | |
| stock | Integer | Quantity of products for the delivery status indicated in this structure | |
| externalStock | Integer | External stock (additional information provided by some suppliers in structures with ‘in stock’ delivery status) | |
| incomingStock | Integer | Stock quantity that will be replenished (additional information provided by some suppliers in structures with ‘in stock’ delivery status) | |
| stockAvailabilityDate | Date | Delivery date for products that are not in stock | |
| stockLastUpdate | Date | Time of last update of inventory information | |
| aggregatedStatus | Yes | Integer | Best availability status |
| aggregatedStatusText | Yes | String (20) | Best availability status |
| aggregatedStock | Yes | Integer | Sum of all stock levels |
| aggregatedSupplierItems | Yes | Integer | Sum of all distributors offering this product |
| priceBillingPeriod | Integer | Length of the price billing period | |
| priceBillingPeriodUnit | String (40) | Unit of the price billing period (e.g. “Years”, “Month”) | |
| priceSubscriptionPeriod | Integer | Length of the price subscription period | |
| priceSubscriptionPeriodUnit | String (40) | Unit of the price subscription period (e.g. “Years”, “Month”) | |
| supplierItem | Yes | List <supplierItem> | Source of supply of an ITscope product. A specific offer from a distributor listed on ITscope. |
supplierItem
Source of supply for an ITscope product. A specific offering from a distributor listed on ITscope.
| Name | Compulsory field | Data type | Description |
|---|---|---|---|
| id | Yes | long | Unique key |
| supplierSKU | String (150) | Product number of the supplier [was String(40) before 18/10/2021] | |
| supplierId | Yes | long | Refers 1:1 to Supplier.id |
| supplierName | String (255) | Name of Supplier | |
| price | Yes | BigDecimal | Price |
| priceCalc | Yes | BigDecimal | Calculated price, based on the individual price calculation |
| currencyCode | Yes | String (3) | Currency unit that applies to this price information |
| priceCalcVat | Yes | BigDecimal | Sales tax rate that was used to determine the calculated price |
| priceLastUpdate | Yes | Date | Time of last update of price information |
| stockSupplierText | String | Stock information on the source of supply, as given by the supplier | |
| stockStatus | Integer | Numeric key for the delivery status of the inventory information | |
| stockStatusText | String (255) | Delivery status of inventory information, such as ‘in stock’ or ‘in field warehouse’ | |
| stock | Integer | Stock level | |
| externalStock | Integer | External stock (additional information provided by some suppliers in structures with ‘in stock’ delivery status) | |
| incomingStock | Integer | Stock quantity that will be replenished (additional information provided by some suppliers in structures with ‘in stock’ delivery status) | |
| stockAvailabilityDate | Date | Delivery date for products that are not in stock | |
| lastStockUpdate | Date | Time of last update of inventory information | |
| priceBillingPeriod | Integer | Length of the price billing period | |
| priceBillingPeriodUnit | String (40) | Unit of the price billing period (e.g. “Years”, “Month”) | |
| priceSubscriptionPeriod | Integer | Length of the price subscription period | |
| priceSubscriptionPeriodUnit | String (40) | Unit of the price subscription period (e.g. “Years”, “Month”) | |
| projects | List<project> | Projects for the source of supply | |
| supplierPackagingInfo | supplierPackagingInfo | Information on the packaging units of a source of supply | |
| scaledPriceInfo | List<scaledPriceInfo> | Information on scaled prices of a source of supply |
supplierPackageInfo
Information on packaging units of a source of supply. The unit codes for packaging units are in UN/ECE Recommendation No.21. This standard is recommended by UN/CEFACT. Details see UNECE.
| Name | Mandatory | Data type | description |
|---|---|---|---|
| contentUnit | String (50) | Unit code of the contents of a package | |
| intervalQuantity | | Integer | Number indicating in which graduation the item can be ordered (in order units). |
| minQuantity | Integer | Minimum number of packaging units to trigger an order | |
| numberContentUnitPerOrderUnit | Integer | Number of contents of a packaging unit | |
| orderUnit | | String (50) | Packaging unit code |
| priceQuantity | Integer | Number of packaging units for a given price | |
| supplierPackingQuantity | Integer | Number of packaging units a supplier provides in a purchase order | |
| totalPriceQuantity | | Integer | Total number of units (packaging units * contents) for a given price |
scaledPriceInfo
Information on scaled prices of a source of supply
| Name | Mandatory | Data type | DESCRIPTION |
|---|---|---|---|
| scale | Yes | Integer | Indicates the scale from which the price applies |
| price | Yes | BigDecimal | The price for the scale |
project
Price, availability and further information on a project agreement.
| name | Mandatory | Data type | description |
|---|---|---|---|
| supplierProjectId | String (40) | Project number of the supplier | |
| manufacturerProjectId | Yes | String (40) | Project number of the manufacturer (unique key) |
| price | Yes | BigDecimal | Project price |
| remainingQuantity | Yes | Integer | Remaining quota that can be ordered |
| projectLastUpdate | Date | Time of last update of project information | |
| projectBundleId | String (50) | Unique key of the project bundle (activation via ITscope test lab) | |
| projectPosition | Integer | Project line item number (activation via ITscope test lab) |