2 Data Seeding
SIOCS needs merchandising foundation data (stores, items, initial inventory positions, suppliers, and so on) to function.
Initial inventory data seeding is applicable for new or fresh full SIOCS installation. After the initial set of data is seeded into SIOCS, subsequent inventory changes are communicated via Oracle Retail Integration Cloud Service.
Data Seeding from Merchandising Foundation Cloud Service
When SIOCS and MFCS (Merchandising Foundation Cloud Service) are co-deployed in the same database Container, the data seeding process imports data from the MFCS database into the SIOCS database, and this seeding is an MDI-Based data seeding.
Standalone Data Seeding
In SIOCS standalone installation, SIOCS provides standalone data seeding to seed external data into SIOCS. See Standalone Data Seeding for details.
Transactional Data Seeding
See Transactional Data Seeding for more details.
Data Seeding from Merchandising Foundation Cloud Service
This section contains the following:
Overview
Merchandising Data Integration (MDI) Based Data Seeding process is seeding foundation data from a co-deployed MFCS (Merchandising Foundation Cloud Service) database into SIOCS.
Data Seeding contains 39 modules and are grouped into nine (9) groups based on dependency and functionality.
Data seeding can be used for a variety of use cases:
-
Loading just the Foundation data from Merchandising
-
Loading all stores data
-
Loading a single store data
Table 2-1 Initial Data Loading Groups
Group Number | Data Group | Module | Description |
---|---|---|---|
2 |
Item |
Item Header |
Initial Data Loading Groups |
2 |
Item CFA |
Import item custom flexible attribute data. |
|
2 |
Item Translation |
Import item description translation data. |
|
3 |
Item Image |
Import item image URL data. |
|
3 |
Item UDA |
Import item User Defined Attribute data. |
|
1 |
Item Hierarchy |
Import item merchandise hierarchy data, for example, department, class and subclasses. |
|
3 |
Pack Item |
Import item pack item component data. |
|
3 |
Related Item |
Import related item type data. |
|
4 |
Related Item Detail |
Import related item detail data. |
|
2 |
Miscellaneous |
Differentiator |
Import item differentiation data. |
1 |
Differentiator Type |
Import item differentiator type data, for example, color, size, and so on. |
|
1 |
Transfer Zone |
Import transfer zone data. |
|
1 |
UDA |
Import User Defined Attribute data. |
|
2 |
UDA Values |
Import User Defined Attribute Value data. |
|
1 |
UOM Class |
Import Unit Of Measure class data. |
|
2 |
UOM Conversion |
Import Unit Of Measure conversion data. |
|
Store |
Store Item Stock |
Import store item stock record data. Can be run by a store, or list of stores. |
|
5 |
Store |
Import store data. Can be run by a store, or list of stores. |
|
5 |
Store Address |
Import store address data. Can be run by a store, or list of stores. |
|
6 |
Store Item |
Import store item data. Can be run by a store, or list of stores. |
|
7 |
Store Item CFA |
Import store item custom defined attributes. |
|
7 |
Store Item Stock |
Import store item stock data. |
|
8 |
Store Item Price |
Import store item price data. Can be run by a store, or list of stores. |
|
9 |
Store Item Price History |
Import store item price history data. Can be run by a store, or list of stores. |
|
9 |
Store UIN Admin Item |
Import UIN admin item foundation data. Only applicable if UIN is enabled for the store. Can be run by a store, or list of stores. |
|
4 |
Supplier |
Item Supplier Country Dimension |
Import item supplier country dimension data. |
4 |
Item Supplier Manufacturer Country |
Import item supplier manufacture country data. |
|
4 |
Item Supplier Country |
Import item supplier country data. |
|
4 |
Item Supplier UOM |
Import item supplier UOM data. |
|
4 |
Item Supplier |
Import item supplier data. |
|
1 |
Partner |
Importer partner data. |
|
1 |
Partner Address |
Import partner address data. |
|
Partner Item |
Import partner item data. |
||
2 |
Supplier Organization Unit |
Import supplier organization unit data. |
|
1 |
Supplier |
Import supplier data. |
|
1 |
Supplier Address |
Import supplier address data. |
|
1 |
Supplier CFA |
Import supplier custom flex attributes |
|
1 |
Warehouse |
Warehouse |
Import warehouse data. |
1 |
Warehouse Address |
Import warehouse address data. |
|
3 |
Warehouse Item |
Import warehouse item data. |
Data Seeding Modules
Data seeding modules are grouped into 9 data groups:
See Table 2-3 Data Seeding MFCS-SIOCS View Mappings for additional information.
Data Seeding Steps
Pre-requisites for Seeding from Co-Deployed MFCS
Prior to running data seeding, the following requirements must be met:
-
MFCS database is installed
-
MFCS foundation data setup is completed
-
MFCS and SIOCS are installed in the same pluggable database with different schemas
Assign Application Roles for Initial Data Loading
Users need to have the following Application roles assigned in IDCS or OCI IAM:
{SIOCS Primary APP}.admin_users for example,
RGBU_SIOCS_CFS_EICS.admin_users
{SIOCS Primary APP}.batch_users for example,
RGBU_SIOCS_CFS_EICS.batch_users
Assign Security Permissions for Initial Data Loading
Table 2-2 Security Permissions for Initial Data Loading
Name | Description |
---|---|
Access Initial Data Load |
With this permission the user will have access to the Initial Data Load screen. Without this permission the user will not have access to the Initial Data Load screen. |
Submit Initial Data Load |
With this permission the user will have the permission to submit seed. Without this permission, the Submit Seed button will be disabled for the user. |
Delete Initial Data Load |
With this permission the user will have the permission to delete seeded data. Without this permission, the Delete Seed button will be disabled for the user. |
Initial Data Loading System Configuration
To seed initial inventory foundation data from sourcing system directly into destination application tables, an application implementation consultant must perform the following configuration steps:
Login SIOCS Application as a user who are assigned proper app roles and security permissions, see App Roles and Security Permission Section for details.
To seed data from co-deployed Merchandising data integration shared database, set system configuration values as shown below:
-
Set Initial Data Load Seed to Yes.
-
Set Initial Seed Foundation to Yes.
-
Set Initial Data Load Seed Foundation Data to Yes.
Submit Seed
To start the initial data loading, perform following steps:
-
Login SIOCS Application as app admin user.
-
Navigate to Admin - Technical Maintenance - Initial Data Loading Screen.
Figure 2-1 Initial Data Loading Screen
-
Filter the modules by execution group, start with group 1.
-
Select the module group, then click Submit Seed button.
Note:
To run data seeding for store related groups, user will need to select sourcing stores using Select Store button.
-
Once modules for selected group are completed, then proceed to the next group.
View Selected Module Executions
To view data loading log for the selected module:
-
Click the executed module record from the module list panel.
-
Scroll down to the Executions panel to view execution details for the selected module.
Figure 2-2 Initial Data Loading Execution Panel
View Selected Module Execution Details
To view data loading execution details:
-
Click the executed module record from the module list panel.
-
Select record in the Execution panel.
-
Click the ID link to navigate to the Execution Detail screen.
Figure 2-3 Initial Data Loading Execution Details Screen
Re-run Initial Data Loading
In the event of failures, you may need to re-run the data seeding after correcting the errors.
To re-run data seeding:
-
Select the module, then click the Delete Data button.
-
After delete process to complete, select the module, then click Submit Seed button.
Initial Data Loading Post Steps
-
Verify data seeded into SIOCS application tables without error.
-
Set Initial Data Load Seed to No.
-
Set Initial Seed Foundation to No.
-
Set Initial Data Load Seed Foundation Data to No.
Note:
For stores which need to be rolled out by phases, the value can be set back to Yes before loading another set of stores and set to No after all stores are seeded from sourcing system.
Data Seeding MFCS-SIOCS View Mappings
Table 2-3 Data Seeding MFCS-SIOCS View Mappings
Seeding Module | SIOCS Target Table | SIOCS View | MFCS1 |
---|---|---|---|
Differentiator Type |
DIFFERENTIATOR_TYPE |
IDLV_DIFFERENTIATOR_TYPE |
V_RMS_SIM_DIFF_TYPE |
Differentiator |
DIFFERENTIATOR |
IDLV_DIFFERENTIATOR |
V_RMS_SIM_DIFF |
Item |
ITEM |
IDLV_ITEM |
V_RMS_SIM_ITEM_MASTER |
Item CFA |
ITEM_CFA |
IDLV_ITEM_CFA |
V_RMS_SIM_ITEM_MASTER_CFA_EXT |
Item Description Translation |
ITEM_DESCRIPTION |
IDLV_ITEM_DESCRIPTION |
V_RMS_SIM_ITEM_MASTER_TL |
Item Image |
ITEM_IMAGE |
IDLV_ITEM_IMAGE |
V_RMS_SIM_ITEM_IMAGE |
Item Supp Country Dim |
SUPPLIER_ITEM_COUNTRY_DIM |
IDLV_SUPPLIER_ITEM_COUNTRY_DIM |
V_RMS_SIM_ITEM_SUPP_CTRY_DIM |
Item Supp Man. Country |
SUPPLIER_ITEM_MANUFACTURE |
IDLV_SUPPLIER_ITEM_MANUFACTURE |
V_RMS_SIM_ITEM_SUPP_MANU_CTRY |
Item Supp Country |
SUPPLIER_ITEM_COUNTRY |
IDLV_SUPPLIER_ITEM_COUNTRY |
V_RMS_SIM_ITEM_SUPP_CTRY |
Item Supplier |
SUPPLIER_ITEM |
IDLV_SUPPLIER_ITEM |
V_RMS_SIM_ITEM_SUPPLIER |
Item Supplier UOM |
SUPPLIER_ITEM_UOM |
IDLV_SUPPLIER_ITEM_UOM |
V_RMS_SIM_ITEM_SUPP_UOM |
Item UDA |
ITEM_UDA |
IDLV_ITEM_UDA |
V_RMS_SIM_UDA_ITEM_DATE V_RMS_SIM_UDA_ITEM_FF V_RMS_SIM_UDA_ITEM_LOV |
Merch Hier |
ITEM_HIERARCHY |
IDLV_ITEM_HIER |
V_RMS_SIM_MERCH_HIER |
Pack Item |
ITEM_COMPONENT |
IDLV_ITEM_COMPONENT |
V_RMS_SIM_PACKITEM |
Partner |
PARTNER |
IDLV_PARTNER |
V_RMS_SIM_EXTERNAL_FINISHER |
Partner Address |
ADDRESS |
IDLV_ADDRESS |
V_RMS_SIM_ADDR |
Partner Item |
PARTNER_ITEM |
IDLV_PARTNER_ITEM |
V_RMS_SIM_ITEM_LOC (loc_type = 'E' --external finisher) |
Partner Org Unit |
SUPPLIER_ORGANIZATION |
IDLV_SUPPLIER_ORGANIZATION |
V_RMS_SIM_PARTNER_ORG_UNIT |
Price History |
ITEM_PRICE_HISTORY |
IDLV_STORE_ITEM_PRICE_HIST |
V_RMS_SIM_PRICE_HIST |
Related Item |
RELATED_ITEM_TYPE |
IDLV_RELATED_ITEM_TYPE |
V_RMS_SIM_RELATED_ITEM_HEAD |
Related Item Detail |
RELATED_ITEM |
IDLV_RELATED_ITEM |
V_RMS_SIM_RELATED_ITEM_DETAIL |
Store |
STORE |
IDLV_STORE |
V_RMS_SIM_STORE |
Store Address |
ADDRESS |
IDLV_ADDRESS |
V_RMS_SIM_ADDR |
Store Item |
STORE_ITEM |
IDLV_STORE_ITEM |
V_RMS_SIM_STORE_ITEM V_RMS_SIM_REPL_ITEM_LOC |
Store Uin Admin Item |
STORE_UIN_ADMIN_ITEM |
IDLV_STORE_UIN_ADMIN_ITEM |
V_RMS_SIM_STORE_ITEM |
Store Item CFA |
STORE_ITEM_CFA |
IDLV_STORE_ITEM_CFA |
V_RMS_SIM_ITEM_LOC_CFA_EXT |
Store Item Price |
ITEM_PRICE |
IDLV_STORE_ITEM_PRICE |
V_RMS_SIM_STORE_ITEM |
Store Item Stock |
STORE_ITEM_STOCK STORE_ITEM_STOCK_NONSELL |
IDLV_STORE_ITEM_STOCK IDLV_STORE_ITEM_STOCK_NONSELL |
V_RMS_SIM_STORE_ITEM_SOH |
Supplier |
SUPPLIER |
IDLV_SUPPLIER |
V_RMS_SIM_SUPS |
Supplier CFA |
SUPPLIER_CFA |
IDLV_SUPPLIER_CFA |
V_RMS_SIM_SUPS_CFA_EXT |
Supplier Address |
ADDRESS |
IDLV_ADDRESS |
V_RMS_SIM_ADDR |
Transfer Zone |
STORE_TRANSFER_ZONE |
IDLV_TRANSFER_ZONE |
V_RMS_SIM_TSFZONE |
UDA |
UDA |
IDLV_UDA |
V_RMS_SIM_UDA |
UDA LOV |
UDA LOV |
IDLV_UDA_LOV |
V_RMS_SIM_UDA_VALUES |
UOM Class |
UOM_CLASS |
IDLV_UOM_CLASS |
V_RMS_SIM_UOM_CLASS |
UOM Conversion |
UOM_CONVERSION |
IDLV_UOM_CONVERSION |
V_RMS_SIM_UOM_CONVERSION |
Warehouse |
WAREHOUSEWAREHOUSE_VIRTUAL |
IDLV_WAREHOUSE IDLV_WAREHOUSE_VIRTUAL |
V_RMS_SIM_WH |
Warehouse Address |
ADDRESS |
IDLV_ADDRESS |
V_RMS_SIM_ADDR |
Warehouse Item Address |
WAREHOUSE_ITEM |
IDLV_WAREHOUSE_ITEM |
V_RMS_SIM_ITEM_LOC |
1MFCS view: only applicable for data seeding source is MFCS on a co-deployed Oracle PDB.
Standalone Data Seeding
This section contains the following:
Overview
Data seeding in a SIOCS Standalone installation is achieved by uploading data in CSV (comma-separated values) files to Object Storage via FTS (File Transfer Service). The Initial Foundation Data File Import and Initial Store Data File Import batch jobs then download the relevant files from Object Storage (see Data Seeding Modules) and import the data into SIOCS.
The Initial Data Load UI (see Data Seeding from Merchandising Foundation Cloud Service) can be re-used in a SIOCS Standalone installation to view the status of each Data Seeding Module (see below), view any errors associated with the processing of files for that module, and to Delete Data for that module.
Note:
The Submit Seed button will be disabled in a SIOCS Standalone installation: data seeding will be initiated by running the Initial Foundation Data File Import and Initial Store Data File Import batch jobs from the Job Admin UI (see Batches).
System Admin Parameters
Table 2-4 System Admin Parameters
Option | Description | Default Value | Topic | Type |
---|---|---|---|---|
Initial Data Load Seed |
Determines if data seeding is enabled. |
No |
Admin |
Boolean |
Initial Data Load Seed Foundation Data |
Determines if data seeding of foundation data is enabled. |
No |
Admin |
Boolean |
Initial Data Load Seed Store Data |
Determines if data seeding of store data is enabled. Yes: Store Data will be available for data seeding. No: Store Data will not be available for data seeding. |
No |
Admin |
Boolean |
Initial Data Load Fail Limit |
The maximum number of errors to ignore before processing of a file is terminated. |
0 |
Batch |
Integer |
Initial Data Load Chunk Log Limit |
The maximum number of errors to log when processing a file - this value should be greater than the Initial Data Load Fail Limit. |
10 |
Batch |
Integer |
Initial Data Load Chunk Limit |
The maximum number of records to insert into the DB in a single batch update. |
1000 |
Batch |
Integer |
Initial Data Loading Process
-
Set the Initial Data Load Seed and Initial Data Load Seed Foundation Data options to Yes.
-
Upload the relevant foundation data files to the imports folder in Object Storage via FTS.
-
Run the Initial Foundation Data File Import batch job: the batch job will download the foundation data files from Object Storage, parse the files and insert the data into the staging tables, merge/upsert the data from the staging tables into the SIOCS master tables, upload any failed files/records to the rejects folder, and any successful files/records to the archives folder, in Object Storage.
-
Wait for the batch job to finish then check the Job Execution (Job Admin UI) and Execution Detail (Initial Data Load UI) screens for any errors. If the number of errors exceeds the Initial Data Load Fail Limit the entire file will be rejected and uploaded to the rejects folder in Object Storage. If the number of errors does not exceed the Initial Data Load Fail Limit, the erroneous records will be uploaded to the rejects folder, and the successful records to the archives folder in Object Storage.
Note:
For performance reasons, the batch job will zip any files > 10 MB before uploading to Object Storage.
-
Correct any errors and repeat steps 2 to 4 until there are no errors and all the foundation data has been imported.
Note:
It is not necessary to delete data for a module before re-importing data for that module: the batch job uses a merge/upsert when copying data from the staging tables to the SIOCS master tables.
-
Set the Initial Data Load Seed Foundation Data option to No and the Initial Data Load Seed Store Data option to Yes.
-
Upload the relevant store data files to the imports folder in Object Storage via FTS.
-
Run the Initial Store Data File Import batch job: the batch job follows the same flow as the Initial Foundation Data File Import batch job but for store data.
-
Wait for the batch job to finish then check the Job Execution (Job Admin UI) and Execution Detail (Initial Data Load UI) screens for any errors.
-
Correct any errors and repeat steps 7 to 9 until there are no errors and all the store data has been imported.
-
Set the Initial Data Load Seed and Initial Data Load Seed Store Data options to No.
Figure 2-4 High Level Flow
Data Seeding Modules
Data seeding modules are grouped into 5 data groups:
Table 2-5 Initial Data Loading Groups
Data Group | Module | Description |
---|---|---|
Item |
Item |
Item data. |
Item CFA |
Item custom flexible attribute data. |
|
Item Component |
Pack item component data. |
|
Item Description |
Item description data. |
|
Item Hierarchy |
Item merchandise hierarchy data, for example, department, class and subclasses. |
|
Item Image |
Item image URL data. |
|
Item UDA |
Item user defined attribute data. |
|
Related Item |
Related item detail data. |
|
Related Item Type |
Related item type data. |
|
Miscellaneous |
Differentiator |
Item differentiation data. |
Differentiator Type |
Item differentiation type data, such as style, color, size, and so on. |
|
Transfer Zone |
Transfer zone data. |
|
UDA |
User defined attribute data. |
|
UDA LOV |
User defined attribute list of values data. |
|
UOM Class |
Unit of measure class data. |
|
UOM Conversion |
Unit of measure conversion data. |
|
Store |
Store |
Store data. |
Store Address |
Store address data. |
|
Store Item |
Store item data. |
|
Store Item CFA |
Store item custom flexible attribute data. |
|
Store Item Price |
Store item price data. |
|
Store Item Price History |
Store item price history data. |
|
Store Item Stock |
Store item stock record data. |
|
Store UIN Admin Item |
Store UIN (Unique Identification Number) admin item data. |
|
Supplier |
Partner |
Partner data. |
Partner Address |
Partner address data. |
|
Partner Item |
Partner item data. |
|
Supplier |
Supplier data. |
|
Supplier Address |
Supplier address data. |
|
Supplier CFA |
Supplier custom flexible attribute data. |
|
Supplier Item |
Supplier item data. |
|
Supplier Item Country |
Supplier item country data. |
|
Supplier Item Country Dimension |
Supplier item country dimension data. |
|
Supplier Item Manufacturer |
Supplier item country manufacture data. |
|
Supplier Organization |
Supplier organization unit data. |
|
Supplier UOM |
Supplier UOM data. |
|
Warehouse |
Warehouse |
Warehouse data. |
Warehouse Address |
Warehouse address data. |
|
Warehouse Item |
Warehouse item data. |
The data for the Miscellaneous, Item, Supplier and Warehouse data group modules are imported by the Initial Foundation Data File Import batch job. The data for the Store data group modules are imported by the Initial Store Data File Import batch job. All foundation data should be imported prior to importing any store data. Due to referential integrity constraints (see File Layouts) the batch jobs process the data in the order shown above (for example, the Supplier Item module cannot be imported prior to the Supplier and Item modules, and the Item module cannot be imported prior to the Item Hierarchy module); as such the files for each module should be uploaded to Object Storage and imported in a similar order (or all at the same time). To import data for a group of Stores, upload all the data for those Stores to Object Storage, then run the Initial Store Data File Import batch job. To import data for a single Store, upload all the data for that Store to Object Storage, then run the Initial Store Data File Import batch job. Alternatively, the Initial Store Data File Import batch job can be run for a single Store by entering the Store ID in the Job Admin UI and adding the Store ID to the corresponding filename(s) (vide infra).
File Layouts
All files should be in CSV (comma-separated values) format, with either a ".csv" or ".dat" filename extension. The batch jobs also support zipped files which will be extracted upon download and processed individually. Empty or blank fields within a record will be considered null. String fields containing a comma or double quote must be quoted (with double quotes), a double quote in a field must be represented by 2 double quote characters. Line breaks within quoted fields are not supported. The filename format is IDL-[MODULENAME]-XXXX.csv(/dat/zip). Files contained within .zip files must adhere to the same filename format. To run the Initial Store Data File Import batch job for a particular Store, the filename format is IDL-[MODULENAME]-[STOREID]-XXXX.csv(/dat/zip). Any files which do not adhere to the filename format will not be downloaded or processed. Files > 500MB will be rejected: the file should be split into smaller files and uploaded as a .zip file. It is recommended to not edit the .csv files in Excel as this can lead to formatting issues.
The file layout for each module is described below:
Differentiator File
Table 2-6 Differentiator File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ID |
The unique identifier of the differentiator. |
Yes |
VARCHAR2 (10) |
DESCRIPTION |
The description of the differentiator. |
Yes |
VARCHAR2 (255) |
DIFF_TYPE_ID |
The unique identifier of the differentiator type - this references the ID column in the DIFFERENTIATOR_TYPE table. |
No |
VARCHAR2 (10) |
Example CSV File
IDL-DIFFERENTIATOR-*.csv
1,DESCRIPTION FOR DIFFERENTIATOR 1,1
Differentiator Type File
Table 2-7 Differentiator Type File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ID |
The unique identifier of the differentiator type. |
Yes |
VARCHAR2 (10) |
DESCRIPTION |
The description of the differentiator type. |
Yes |
VARCHAR2 (255) |
Example CSV File
IDL-DIFFERENTIATORTYPE-*.csv
1,DESCRIPTION FOR DIFFERENTIATOR TYPE 1
Item CFA File
Table 2-8 Item CFA File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ITEM_ID |
The unique identifier of the item - this references the ITEM_ID column in the ITEM table. |
Yes |
VARCHAR2 (25) |
NAME |
The name of the custom flex attribute - forms the primary key together with the ITEM_ID field. |
Yes |
VARCHAR2 (30) |
VALUE |
The value of the custom flex attribute. |
No |
VARCHAR2 (250) |
VALUE_DATE |
The date value of the custom flex attribute in "yyyy-MM-dd" format. |
No |
DATE |
Example CSV File
IDL-ITEMCFA-*.csv
2,Name 2,Value 2,
3,Name 3,,2021-10-06
Item Component File
Table 2-9 Item Component File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ITEM_ID |
The unique identifier of the pack item - references the ITEM_ID column in the ITEM table. |
Yes |
VARCHAR2 (25) |
COMPONENT_ITEM_ID |
The unique identifier of the component item - references the ITEM_ID column in the ITEM table and forms the primary key together with the ITEM_ID field. |
Yes |
VARCHAR2 (25) |
QUANTITY |
The quantity of the component item in the pack item. |
Yes |
NUMBER (12, 4) |
Example CSV File
IDL-ITEMCOMPONENT-*.csv
1,11,1.11
Item Description File
Table 2-10 Item Description File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ITEM_ID |
The unique identifier of the pack item - references the ITEM_ID column in the ITEM table. |
Yes |
VARCHAR2 (25) |
LOCALE_ID |
The unique identifier of the locale - references the ID column in the TRANSLATION_LOCALE table and forms the primary key together with the ITEM_ID field - see Supported Locales. |
Yes |
NUMBER (12, 0) |
DESCRIPTION |
The description of the item. |
Yes |
VARCHAR2 (255) |
SHORT_DESCRIPTION |
The short description of the item. |
Yes |
VARCHAR2 (250) |
SECONDARY_DESCRIPTION |
The secondary description of the item. |
No |
VARCHAR2 (250) |
LOCALE lANGUAGE |
The ISO 3166 language code - references the LANUGAGE column in the TRANSLATION_LOCALE table - see Supported Locales. |
Yes |
VARCHAR2 (6) |
LOCALE_DESCRIPTION |
The description of the locale. |
No |
VARCHAR2 (120) |
Example CSV File
IDL-ITEMDESCRIPTION-*.csv
1,1,Description 1,Short Description 1,Secondary Description 1,en,English
Item File
Table 2-11 Item File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ITEM_ID |
The unique identifier of the item |
Yes |
VARCHAR2 (25) |
ITEM_TYPE |
The type of item - 0 (Item), 15 (Simple Pack), 20 (Complex Pack), 25 (Simple Breakable Pack) or 30 (Complex Breakable Pack). |
Yes |
NUMBER (2, 0) |
DEPARTMENT_ID |
The department identifier - references the DEPARTMENT_ID column in the ITEM_HIERARCHY table. |
No |
NUMBER (12, 0) |
CLASS_ID |
The class identifier - references the CLASS_ID column in the ITEM_HIERARCHY table. |
No |
NUMBER (12, 0) |
SUBCLASS_ID |
The subclass identifier - references the SUBCLASS_ID column in the ITEM_HIERARCHY table. |
No |
NUMBER (12, 0) |
SHORT_DESCRIPTION |
The short description of the item. |
No |
VARCHAR2 (255) |
LONG_DESCRIPTION |
The long description of the item. |
No |
VARCHAR2 (400) |
DIFFERENTIATOR_1 |
The identifier of the first differentiator of the item. |
No |
VARCHAR2 (10) |
DIFFERENTIATOR_2 |
The identifier of the second differentiator of the item. |
No |
VARCHAR2 (10) |
DIFFERENTIATOR_3 |
The identifier of the third differentiator of the item. |
No |
VARCHAR2 (10) |
DIFFERENTIATOR_4 |
The identifier of the fourth differentiator of the item. |
No |
VARCHAR2 (10) |
STATUS |
The status of the item - ' ' (None), A (Active), C (Discontinued), I (Inactive), D (Deleted), Q (Auto-stocked) or N (Non-ranged). |
No |
VARCHAR2 (1) |
ORDER_AS_TYPE |
Indicates if a pack item is receivable at the component level or at the pack level (for a buyer pack only). |
No |
VARCHAR2 (1) |
PARENT_ITEM_ID |
The unique identifier of the parent item. |
No |
VARCHAR2 (25) |
TRANSACTION_LEVEL |
Number indicating which of the three levels transactions occur for the item's group. |
No |
NUMBER |
ITEM_LEVEL |
Number indicating which of the three levels the item resides. |
No |
NUMBER |
SELLABLE |
Flag indicating if the item may be sold as a unit - Y or N. |
Yes |
VARCHAR2 (1) |
ORDERABLE |
Flag indicating if the item may be ordered from a supplier - Y or N. |
Yes |
VARCHAR2 (1) |
PACKAGE_UNIT_OF_MEASURE |
The unit of measure associated with the package size. |
No |
VARCHAR2 (4) |
PACKAGE_SIZE |
The size of the product printed on any packaging. |
No |
NUMBER (12, 4) |
UNIT_OF_MEASURE |
The unit of measure. |
Yes |
VARCHAR2 (4) |
CASE_SIZE |
The default number of items that are contained in a case. |
No |
NUMBER (12, 4) |
BARCODE_FORMAT |
The barcode format for the item. |
No |
VARCHAR2 (4) |
BARCODE_PREFIX |
The barcode prefix for the item. |
No |
NUMBER (9, 0) |
TICKET_TYPE_CODE |
The ticket type code for the item. |
No |
VARCHAR2 (6) |
EACH_TO_UOM_FACTOR |
The conversion factor between an "Each" and the standard unit of measure. |
No |
NUMBER (20, 10) |
WASTE_TYPE |
Identifies the wastage type as either sales or spoilage wastage - SL (sales) or SP (spoilage). |
No |
VARCHAR2 (6) |
WASTE_PERCENT |
Average percent of wastage for the item over its shelf life. |
No |
NUMBER (12, 4) |
WASTE_PERCENT_DEFAULT |
Default daily wastage percent for spoilage type wastage items. |
No |
NUMBER (12, 4) |
ESTIMATE_SOH_FOR_PACK |
Indicates if a notional simple pack item's inventory should be displayed in packs - Y or N. |
Yes |
VARCHAR2 (1) |
RETAIL_ZONE_ID |
The unique identifier of the retail pricing strategy associated with the item. |
No |
VARCHAR2 (128) |
IS_PRIMARY |
Flag indicating if the sub-transaction level item is designated as the primary sub-transaction level item - Y or N. |
No |
VARCHAR2 (1) |
BRAND |
The brand associated with the item. |
No |
VARCHAR2 (30) |
MANU_SUGGESTED_RETAIL_PRICE |
The manufacturer's recommended retail price for the item. |
No |
NUMBER (12, 4) |
MANU_SUGGESTED_RETAIL_CURRENCY |
The ISO 4217 currency code of the manufacturer's retail price. |
No |
VARCHAR2 (3) |
INVENTORIABLE |
Flag indicating if the item is inventoriable - Y or N. |
Yes |
VARCHAR2 (1) |
SHIP_ALONE |
Flag indicating if the item should be shipped to the customer as a separate package - Y or N. |
No |
VARCHAR2 (1) |
BRAND_DESCRIPTION |
The description of the brand associated with the item. |
No |
VARCHAR2 (120) |
Example CSV File
IDL-ITEM-*.csv
1,0,1,1,1,SHORT_DESC,LONG_DESC,1,2,3,4,A,N,4,1,3,Y,N,kg,12345678.1234,kg,1,UPCA,22,TT,1,SL,33.33,16.66,Y,RETAIL_ZONE_ID,N,BRAND,4.99,GBP,Y,N,BRAND_DESC
Item Hierarchy File
Table 2-12 Item Hierarchy File Layout
Field Name | Description | Required | Type |
---|---|---|---|
DEPARTMENT_ID |
The department identifier. |
No |
NUMBER (12, 0) |
DEPARTMENT_NAME |
The name of the department. |
No |
VARCHAR2 (360) |
CLASS_ID |
The class identifier. |
No |
NUMBER (12, 0) |
CLASS_NAME |
The name of the class. |
No |
VARCHAR2 (360) |
SUBCLASS_ID |
The subclass identifier. |
No |
NUMBER (12, 0) |
SUBCLASS_NAME |
The name of the subclass. |
No |
VARCHAR2 (360) |
STATUS |
The status of the item hierarchy - A (Active) or D (Deleted). |
Yes |
VARCHAR2 (1) |
The unique key comprises the DEPARTMENT_ID, CLASS_ID and SUBCLASS_ID fields.
Example CSV File
IDL-ITEMHIERARCHY-*.csv
1,Department 1,1,Class 1,1,Subclass 1,A
Item Image File
Table 2-13 Item Image File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ITEM_ID |
The unique identifier of the pack item - references the ITEM_ID column in the ITEM table. |
Yes |
VARCHAR2 (25) |
DISPLAY_SEQUENCE |
The display sequence order of images associated to the item. |
Yes |
NUMBER (2, 0) |
IMAGE_URL |
The URL of the item image. |
Yes |
VARCHAR2 (1000) |
IMAGE_NAME |
The name of the item image - forms a unique key together with the ITEM_ID field. |
Yes |
VARCHAR2 (120) |
IMAGE_SIZE_CODE |
The type of item image. Valid values are defined as members of IITD code type - T (Thumbnail), H (High), M (Medium) or L (Low). |
Yes |
VARCHAR2 (6) |
Example CSV File
IDL-ITEMIMAGE-*.csv
1,99,http://somewhere.com/someimage1.gif,Image1.gif,T
Item UDA File
Table 2-14 Item UDA File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ITEM_ID |
The unique identifier of the item - references the ITEM_ID column in the ITEM table. |
Yes |
VARCHAR2 (25) |
UDA_ID |
The unique identifier of the user defined attribute - references the ID column in the UDA table and forms a unique key along with the ITEM_ID field. |
Yes |
NUMBER (5, 0) |
UDA_DATE |
The value, in 'yyyy-MM-dd HH:mm:ss' format, for DT (Date) user defined attributes. |
No |
DATE |
UDA_TEXT |
The value for FF (Text) user defined attributes. |
No |
VARCHAR2 (250) |
UDA_VALUE |
The value for LOV (List of Values) user defined attributes. |
No |
VARCHAR2 (25) |
Example CSV File
IDL-ITEMUDA-*.csv
1,1,2021-10-01 12:34:56,FF1,LOV1
Partner Address File
Table 2-15 Partner Address File Layout
Field Name | Description | Required | Type |
---|---|---|---|
EXTERNAL_ID |
The external identifier of the address. |
Yes |
VARCHAR2 (25) |
PARTNER_ID |
The unique identifier of the partner - forms a unique key together with the EXTERNAL_ID field. |
Yes |
NUMBER (10, 0) |
ADDRESS_TYPE |
The type of address - 01 (Business), 02 (Postal), 03 (Returns), 04 (Order), 05 (Invoice), 06 (Remittance), 07 (Billing), 08 (Delivery) or 09 (External). |
Yes |
VARCHAR2 (2) |
IS_PRIMARY |
Flag indicating if this is the primary address - Y or N. |
Yes |
VARCHAR2 (1) |
ADDRESS_LINE_1 |
The first line of the address. |
No |
VARCHAR2 (240) |
ADDRESS_LINE_2 |
The second line of the address. |
No |
VARCHAR2 (240) |
ADDRESS_LINE_3 |
The third line of the address. |
No |
VARCHAR2 (240) |
CITY |
The city. |
No |
VARCHAR2 (120) |
STATE |
The state. |
No |
VARCHAR2 (3) |
COUNTRY_ID |
The ISO 3166 2- (or 3-) letter country code. |
No |
VARCHAR2 (3) |
POSTAL_CODE |
The postal code. |
No |
VARCHAR2 (30) |
CONTACT_NAME |
The contact name. |
No |
VARCHAR2 (120) |
CONTACT_PHONE |
The contact phone number. |
No |
VARCHAR2 (20) |
CONTACT_FAX |
The contact fax number. |
No |
VARCHAR2 (20) |
CONTACT_EMAIL |
The contact email address. |
No |
VARCHAR2 (100) |
COUNTY |
The county. |
No |
VARCHAR2 (250) |
Example CSV File
IDL-PARTNERADDR-*.csv
1,1,01,Y,Line 1,Line 2,Line 3,City,MN,USA,Postcode,Contact Name,Contact_Phone,Contact_Fax,Contact_Email,County
Partner File
Table 2-16 Partner File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ID |
The unique identifier of the partner. |
Yes |
NUMBER (10, 0) |
NAME |
The name of the partner. |
No |
VARCHAR2 (240) |
CURRENCY_CODE |
The ISO 4217 currency code of the partner. |
No |
VARCHAR2 (3) |
LOCALE_ID |
The locale identifier of the partner - see Supported Locales. |
No |
NUMBER (6) |
STATUS |
The status of the partner - A (Active) or I (Inactive). |
No |
VARCHAR2 (1) |
CONTACT_NAME |
The contact name. |
No |
VARCHAR2 (120) |
CONTACT_PHONE |
The contact phone number. |
No |
VARCHAR2 (20) |
CONTACT_FAX |
The contact fax number. |
No |
VARCHAR2 (20) |
CONTACT_TELEX |
The contact telex number. |
No |
VARCHAR2 (20) |
CONTACT_EMAIL |
The contact email address. |
No |
VARCHAR2 (100) |
MANUFACTURER_ID |
The manufacturer's tax identification number. |
No |
VARCHAR2 (18) |
PRINCIPAL_COUNTRY_ID |
The ISO 3166 2- (or 3-) letter country code to which the partner is assigned. |
No |
VARCHAR2 (3) |
TAX_ID |
The unique tax identification number of the partner. |
No |
VARCHAR2 (18) |
PAYMENT_TERMS |
The payment terms of the partner. |
No |
VARCHAR2 (20) |
IMPORT_COUNTRY_ID |
The ISO 3166 2- (or 3-) letter country code of the Import Authority. |
No |
VARCHAR2 (3) |
IMPORT_PRIMARY |
Flag that indicates if an Import Authority is the primary Import Authority for an import country - Y or N. |
No |
VARCHAR2 (1) |
ORGANIZATION_UNIT_ID |
The organization unit identifier of the partner. |
No |
VARCHAR2 (15) |
VALUE_ADDED_TAX_REGION |
The VAT region of the partner. |
No |
VARCHAR2 (20) |
TRANSFER_ENTITY_ID |
The transfer entity identifier of the partner. |
No |
VARCHAR2 (20) |
Example CSV File
IDL-PARTNER-*.csv
1,Partner 1,GBP,1,A,Contact Name 1,Contact Phone 1,Contact Fax 1,Contact Telex 1,Contact Email 1,Manufacturer ID 1,GB,123456789012345678,Payment Terms 1,US,Y,Org Unit ID 1,VAT Region 1,Transfer Entity ID 1
Partner Item File
Table 2-17 Partner Item File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ITEM_ID |
The unique identifier of the item - references the ITEM_ID column in the ITEM table. |
Yes |
VARCHAR2 (25) |
PARTNER_ID |
The unique identifier of the partner - this references the ID column in the PARTNER table and forms the primary key together with the ITEM_ID field. |
Yes |
NUMBER (10, 0) |
STATUS |
The status of the item - ' ' (None), A (Active), C (Discontinued), I (Inactive), D (Deleted), Q (Auto-stocked) or N (Non-ranged). |
No |
VARCHAR2 (2) |
Example CSV File
IDL-PARTNERITEM-*.csv
1,1,A
Related Item File
Table 2-18 Related Item File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ITEM_ID |
The unique identifier of the item - references the ITEM_ID column in the ITEM table. |
Yes |
VARCHAR(25) |
RELATIONSHIP_ID_EXTERNAL |
The external identifier of the relationship type. |
Yes |
NUMBER (20, 0) |
RELATIONSHIP_NAME |
The name of the relationship type. |
No |
VARCHAR2 (120) |
RELATIONSHIP_TYPE |
The relationship type - RLTD (Related), SUBS (Substitute), UPSL (Upsell) or CSSL (Crosssell). |
Yes |
VARCHAR2 (6) |
MANDATORY_IND |
Flag indicating if the relationship is mandatory - Y or N. |
Yes |
VARCHAR2 (1) |
RELATED_ITEM_ID |
The unique identifier of the related item - references the ITEM_ID column in the ITEM table. |
Yes |
VARCHAR2 (25) |
PRIORITY_NUMBER |
The priority when there are multiple relationships. |
No |
NUMBER (4, 0) |
EFFECTIVE_DATE |
The effective date of the relationship in "yyyy-MM-dd HH:mm:ss" format. |
No |
DATE |
END_DATE |
The end date of the relationship in "yyyy-MM-dd HH:mm:ss" format. |
No |
DATE |
The primary key comprises the ITEM_ID, RELATIONSHIP_ID_EXTERNAL and RELATED_ITEM_ID fields.
Example CSV File
IDL-RELATEDITEM-*.csv
1,1,Related,RLTD,N,11,9999,2021-10-01 12:34:56,2021-11-01 12:34:56
Related Item Type File
Table 2-19 Related Item Type File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ITEM_ID |
The unique identifier of the item - references the ITEM_ID column in the ITEM table. |
Yes |
VARCHAR2 (25) |
RELATIONSHIP_ID_EXTERNAL |
The external identifier of the relationship type - forms the primary key together with the ITEM_ID field. |
Yes |
NUMBER (20, 0) |
RELATIONSHIP_NAME |
The name of the relationship type. |
No |
VARCHAR2 (120) |
RELATIONSHIP_TYPE |
The relationship type - RLTD (Related), SUBS (Substitute), UPSL (Upsell) or CSSL (Crosssell). |
Yes |
VARCHAR2 (6) |
MANDATORY_IND |
Flag indicating if the relationship is mandatory - Y or N. |
Yes |
VARCHAR2 (1) |
Example CSV File
IDL-RELATEDITEMTYPE-*.csv
1,1,Related,RLTD,N
Store Address File
Table 2-20 Store Address File Layout
Field Name | Description | Required | Type |
---|---|---|---|
EXTERNAL_ID |
The external identifier of the address. |
Yes |
VARCHAR2 (25) |
STORE_ID |
The unique identifier of the store - forms a unique key together with the EXTERNAL_ID field. |
Yes |
NUMBER (10, 0) |
ADDRESS_TYPE |
The type of address - 01 (Business), 02 (Postal), 03 (Returns), 04 (Order), 05 (Invoice), 06 (Remittance), 07 (Billing), 08 (Delivery) or 09 (External). |
Yes |
VARCHAR2 (2) |
IS_PRIMARY |
Flag indicating if this is the primary address - Y or N. |
Yes |
VARCHAR2 (1) |
ADDRESS_LINE_1 |
The first line of the address. |
No |
VARCHAR2 (240) |
ADDRESS_LINE_2 |
The second line of the address. |
No |
VARCHAR2 (240) |
ADDRESS_LINE_3 |
The third line of the address. |
No |
VARCHAR2 (240) |
CITY |
The city. |
No |
VARCHAR2 (120) |
STATE |
The state. |
No |
VARCHAR2 (3) |
COUNTRY_ID |
The ISO 3166 2- (or 3-) letter country code. |
No |
VARCHAR2 (3) |
POSTAL_CODE |
The postal code. |
No |
VARCHAR2 (30) |
CONTACT_NAME |
The contact name. |
No |
VARCHAR2 (120) |
CONTACT_PHONE |
The contact phone number. |
No |
VARCHAR2 (20) |
CONTACT_FAX |
The contact fax number. |
No |
VARCHAR2 (20) |
CONTACT_EMAIL |
The contact email address. |
No |
VARCHAR2 (100) |
COUNTY |
The county. |
No |
VARCHAR2 (250) |
Example CSV File
IDL-STOREADDR-*.csv
1,1,01,Y,Line 1,Line 2,Line 3,City,MN,USA,Postcode,Contact Name,Contact_Phone,Contact_Fax,Contact_Email,County
Store File
Table 2-21 Store File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ID |
The unique identifier of the store. |
Yes |
NUMBER (10,0) |
NAME |
The name of the store. |
Yes |
VARCHAR2 (150) |
ORGANIZATION_UNIT_ID |
The organization unit identifier of the store. |
No |
VARCHAR2 (15) |
LOCALE_LANGUAGE |
The ISO 3166 language to which the store is assigned - see Supported Locales. |
No |
VARCHAR2 (3) |
LOCALE_COUNTRY |
The ISO 3166 2- (or 3-) letter country code to which the store is assigned. |
No |
VARCHAR2 (3) |
OPEN_DATE |
The date on which the store opened in 'yyyy-MM-dd' format. |
No |
DATE |
CLOSE_DATE |
The date on which the store closed in 'yyyy-MM-dd' format. |
No |
DATE |
TOTAL_SQUARE_FEET |
The total square footage of the store. |
No |
NUMBER (9,2) |
SELLING_SQUARE_FEET |
The total square footage of the store's selling area. |
No |
NUMBER (9,2) |
CURRENCY_CODE |
The ISO 4217 currency code of the store. |
No |
VARCHAR2 (40) |
TRANSFER_ZONE_ID |
The transfer zone identifier. |
No |
VARCHAR2 (128) |
SIM_STORE |
Flag indicating if the store is using the SIM application - Y or N. |
No |
VARCHAR2 (1) |
TIMEZONE |
The time zone of the store. |
Yes |
VARCHAR2 (80) |
CUSTOMER_ORDER_LOC_IND |
Flag indicating if the store is a customer order location - Y or N. |
Yes |
VARCHAR2 (1) |
Example CSV File
IDL-STORE-*.csv
1,Store 1,Org Unit ID,en,GB,2001-01-01,2030-12-31,20,10,GBP,1,Y,GMT,Y
Store Item CFA File
Table 2-22 Store Item CFA File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ITEM_ID |
The unique identifier of the item - this references the ITEM_ID column in the ITEM table. |
Yes |
VARCHAR2 (25) |
STORE_ID |
The unique identifier of the store - this references the ID column in the STORE table. |
Yes |
NUMBER (10, 0) |
NAME |
The name of the custom flex attribute. |
Yes |
VARCHAR2 (30) |
VALUE |
The value of the custom flex attribute. |
No |
VARCHAR2 (250) |
VALUE_DATE |
The date value of the custom flex attribute in "yyyy-MM-dd" format. |
No |
DATE |
The primary key comprises the ITEM_ID, STORE_ID and NAME fields.
Example CSV File
IDL-STOREITEMCFA-*.csv
2,1,Name 2,Value 2,
3,1,Name 3, ,2021-10-06
Store Item File
Table 2-23 Store Item File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ITEM_ID |
The unique identifier of the item - references the ITEM_ID column in the ITEM table. |
Yes |
VARCHAR2 (25) |
STORE_ID |
The unique identifier of the store - this references the ID column in the STORE table and forms the primary key together with the ITEM_ID field. |
Yes |
NUMBER (10, 0) |
ITEM_TYPE |
The type of store item - 0 (Item), 15 (Simple Pack), 20 (Complex Pack), 25 (Simple Breakable Pack) or 30 (Complex Breakable Pack). |
Yes |
VARCHAR2 (255) |
SHORT_DESCRIPTION |
The short description of the store item. |
No |
VARCHAR2 (255) |
LONG_DESCRIPTION |
The long description of the store item. |
No |
VARCHAR2 (400) |
STATUS |
The status of the store item - ' ' (None), A (Active), C (Discontinued), I (Inactive), D (Deleted), Q (Auto-stocked) or N (Non-ranged). |
No |
VARCHAR2 (20) |
STATUS_DATE |
The date that the status of the store item was updated in 'yyyy-MM-dd' format. |
No |
DATE |
DEFAULT_CURRENCY |
The default ISO 4217 currency code of the store item. |
Yes |
VARCHAR2 (3) |
PRIMARY_SUPPLIER_ID |
The identifier of the primary supplier of the store item - this references the ID column in the SUPPLIER table. |
No |
NUMBER (10, 0 |
NEXT_DELIVERY_DATE |
The next delivery date of the store item in 'yyyy-MM-dd' format. |
No |
DATE |
UIN_REQUIRED |
Flag to indicate if a UIN (unique identification number) is required for the store item - Y or N. |
No |
VARCHAR2 (1) |
REPLENISHMENT_TYPE |
The replenishment method for the store item - SO (Store Order). |
No |
VARCHAR2 (6) |
REJECT_STORE_ORDER |
Flag indicating if uploaded store orders should be rejected for the store item - Y or N. |
No |
VARCHAR2 (1) |
STORE_CONTROL_PRICING |
Flag indicating if the store can modify the item's price - Y or N. |
No |
VARCHAR2 (1) |
MULTIPLE_DELIVERY_PER_DAY |
Flag indicating if the store item is replenished multiple times per day - Y or N. |
No |
VARCHAR2 (1) |
RFID |
Flag indicating if the store item is RFID tagged - Y or N. |
Yes |
VARCHAR2 (1) |
CONSIGNMENT_TYPE |
The consignment type of the store item - 5 (Consignment) or 10 (Concession). |
No |
NUMBER (2, 0) |
Example CSV File
IDL-STOREITEM-*.csv
1,1,0,Short Desc 1,Long Desc 1,A,2022-01-14,GBP,1,2022-01-31,N,SO,N,Y,Y,N,10
Store Item Price File
Table 2-24 Store Item Price File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ITEM_ID |
The unique identifier of the item - this references the ITEM_ID column in the STORE_ITEM table. |
Yes |
VARCHAR2 (25) |
STORE_ID |
The unique identifier of the store - this references the STORE_ID column in the STORE_ITEM table. |
Yes |
NUMBER (10, 0) |
EFFECTIVE_DATE |
The date that the item price becomes effective in 'yyyy-MM-dd HH:mm:ss' format. |
No |
DATE |
END_DATE |
The date that the item price is no longer valid in 'yyyy-MM-dd HH:mm:ss' format. |
No |
DATE |
PRICE_TYPE |
The item price type - 202 (Permanent/Regular), 201 (Promotional) or 200 (Clearance). |
Yes |
NUMBER (3, 0) |
STORE_REQUESTED |
Flag indicating if the item price was requested by the store - Y or N. |
Yes |
VARCHAR2 (1) |
STATUS |
The status of the item price - 0 (New), 1 (Pending), 2 (Approved), 3 (Completed), 4 (Rejected), 5 (Ticket List), 6 (Active), 7 (Extract Failed), 9 (Deleted) or 99 (Default). |
Yes |
NUMBER (2, 0) |
PROMOTION_ID |
The identifier of the promotion. |
No |
NUMBER (10, 0) |
PROMOTION_COMP_ID |
The identifier of the promotion component. |
No |
NUMBER (10, 0) |
MULTI_UNITS |
The number of units involved in the multi-unit pricing of the item price. |
No |
NUMBER (12, 4) |
MULTI_UNIT_RETAIL_CURRENCY |
The ISO 4217 currency code of the multi-unit price. |
No |
VARCHAR2 (3) |
MULTI_UNIT_RETAIL |
The value of the multi-unit price. |
No |
NUMBER (20, 4) |
MULTI_UNIT_UOM |
The unit of measure of the multi-unit price. |
No |
VARCHAR2 (4) |
MULTI_UNIT_CHANGE |
Flag indicating if the multi-unit price has changed - Y or N. |
Yes |
VARCHAR2 (1) |
SELLING_UNIT_CHANGE |
Flag indicating if the item price has changed - Y or N. |
Yes |
VARCHAR2 (1) |
PROMOTION_NAME |
The name of the promotion. |
No |
VARCHAR2 (160) |
PROMOTION_DESCRIPTION |
The description of the promotion. |
No |
VARCHAR2 (640) |
PROMOTION_COMP_NAME |
The name of the promotion component. |
No |
VARCHAR2 (160) |
RESET_CLEARANCE_ID |
The clearance reset identifier. |
No |
NUMBER (15, 0) |
PROMO_COMP_TYPE |
The promotion component type - 0 (Complex), 1 (Simple), 2 (Threshold), 3 (Credit) or 4 (Threshold). |
No |
NUMBER (2, 0) |
REGULAR_PRICE_CHANGE_ID |
The identifier of the regular price change. |
No |
NUMBER (15, 0) |
CLEARANCE_ID |
The identifier of the clearance price change. |
No |
NUMBER (15, 0) |
PROMO_COMP_DTL_ID |
The identifier of the promotion component detail. |
No |
NUMBER (15, 0) |
PROMO_DURATION_TYPE |
The promotion duration type - 1 (All Day), 2 (Partial Day) or 3 (Multiple Day). |
No |
NUMBER (2, 0) |
PRICE_VALUE |
The value of the item price. |
Yes |
NUMBER (20, 4) |
PRICE_CURRENCY |
The ISO 4217 currency code of the item price. |
No |
VARCHAR2 (3) |
PRICE_UNIT_OF_MEASURE |
The unit of measure of the item price. |
No |
VARCHAR2 (4) |
EXT_PRICE_EVENT_ID |
The external price event identifier. |
No |
NUMBER (12, 0) |
For Permanent/Regular (202) Item Prices the unique key comprises the ITEM_ID, STORE_ID, PRICE_TYPE and REGULAR_PRICE_CHANGE_ID fields. For Promotional (201) Item Prices the unique key comprises the ITEM_ID, STORE_ID, PRICE_TYPE, PROMOTION_ID, PROMOTION_COMP_ID and PROMO_COMP_DTL_ID fields. For Clearance (200) Item Prices the unique key comprises the ITEM_ID, STORE_ID, PRICE_TYPE and CLEARANCE_ID fields.
Example CSV File
IDL-STOREITEMPRICE-*.csv
1,1,2021-10-06 12:34:56,2021-10-06 12:34:56,202,N,6,,,1,GBP,2469,kg,Y,N,,,,,,1,,,,1234.5678,GBP,kg,1111
1,1,2021-10-07 12:34:56,2021-10-07 12:34:56,201,N,6,1,1,1,GBP,2469,g,Y,N,Promo Name,Promo Desc,Promo Comp Name,,1,,,1,3,1234.5678,GBP,g,3333
1,1,2021-10-08 12:34:56,2021-10-08 12:34:56,200,N,6,,,1,GBP,2469,lb,Y,N,,,,1,,,1,,,1234.5678,GBP,lb,5555
Store Item Price History File
Table 2-25 Store Item Price History File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ITEM_PRICE_ID |
The identifier of the item price. |
No |
NUMBER (12, 0) |
ITEM_ID |
The unique identifier of the item. |
Yes |
VARCHAR2 (25) |
STORE_ID |
The unique identifier of the store. |
Yes |
NUMBER (10, 0) |
EFFECTIVE_DATE |
The date that the item price becomes effective in 'yyyy-MM-dd HH:mm:ss' format. |
No |
DATE |
END_DATE |
The date that the item price is no longer valid in 'yyyy-MM-dd HH:mm:ss' format. |
No |
DATE |
PRICE_TYPE |
The item price type - 202 (Permanent/Regular), 201 (Promotional) or 200 (Clearance). |
Yes |
NUMBER (3, 0) |
STORE_REQUESTED |
Flag indicating if the item price was requested by the store - Y or N. |
Yes |
VARCHAR2 (1) |
PROMOTION_ID |
The identifier of the promotion. |
No |
NUMBER (10, 0) |
PROMOTION_COMP_ID |
The identifier of the promotion component. |
No |
NUMBER (10, 0) |
MULTI_UNITS |
The number of units involved in the multi-unit pricing of the item price. |
No |
NUMBER (12, 4) |
MULTI_UNIT_RETAIL_CURRENCY |
The ISO 4217 currency code of the multi-unit price. |
No |
VARCHAR2 (3) |
MULTI_UNIT_RETAIL |
The value of the multi-unit price. |
No |
NUMBER (20, 4) |
MULTI_UNIT_UOM |
The unit of measure of the multi-unit price. |
No |
VARCHAR2 (4) |
MULTI_UNIT_CHANGE |
Flag indicating if the multi-unit price has changed - Y or N. |
Yes |
VARCHAR2 (1) |
SELLING_UNIT_CHANGE |
Flag indicating if the item price has changed - Y or N. |
Yes |
VARCHAR2 (1) |
PROMOTION_NAME |
The name of the promotion. |
No |
VARCHAR2 (160) |
PROMOTION_DESCRIPTION |
The description of the promotion. |
No |
VARCHAR2 (640) |
PROMOTION_COMP_NAME |
The name of the promotion component. |
No |
VARCHAR2 (160) |
RESET_CLEARANCE_ID |
The clearance reset identifier. |
No |
NUMBER (15, 0) |
PROMO_COMP_TYPE |
The promotion component type - 0 (Complex), 1 (Simple), 2 (Threshold), 3 (Credit) or 4 (Threshold). |
No |
NUMBER (2, 0) |
REGULAR_PRICE_CHANGE_ID |
The identifier of the regular price change. |
No |
NUMBER (15, 0) |
CLEARANCE_ID |
The identifier of the clearance price change. |
No |
NUMBER (15, 0) |
PROMO_COMP_DTL_ID |
The identifier of the promotion component detail. |
No |
NUMBER (15, 0) |
PROMO_DURATION_TYPE |
The promotion duration type - 1 (All Day), 2 (Partial Day) or 3 (Multiple Day). |
No |
NUMBER (2, 0) |
PRICE_VALUE |
The value of the item price. |
Yes |
NUMBER (20, 4) |
PRICE_CURRENCY |
The ISO 4217 currency code of the item price. |
No |
VARCHAR2 (3) |
PRICE_UNIT_OF_MEASURE |
The unit of measure of the item price. |
No |
VARCHAR2 (4) |
For Permanent/Regular (202) Item Prices the unique key comprises the ITEM_ID, STORE_ID, PRICE_TYPE and REGULAR_PRICE_CHANGE_ID fields. For Promotional (201) Item Prices the unique key comprises the ITEM_ID, STORE_ID, PRICE_TYPE, PROMOTION_ID, PROMOTION_COMP_ID and PROMO_COMP_DTL_ID fields. For Clearance (200) Item Prices the unique key comprises the ITEM_ID, STORE_ID, PRICE_TYPE and CLEARANCE_ID fields.
Example CSV File
IDL-STOREITEMPRICEHIST-*.csv
1,1,1,2021-10-06 12:34:56,2021-10-06 12:34:56,202,N,,,1,GBP,2469,kg,Y,N,,,,,,1,,,,1234.5678,GBP,kg
3,1,1,2021-10-07 12:34:56,2021-10-07 12:34:56,201,N,1,1,1,GBP,2469,g,Y,N,Promo Name,Promo Desc,Promo Comp Name,,1,,,1,3,1234.5678,GBP,g
5,1,1,2021-10-08 12:34:56,2021-10-08 12:34:56,200,N,,,1,GBP,2469,lb,Y,N,,,,1,,,1,,,1234.5678,GBP,lb
Store Item Stock File
Table 2-26 Store Item Stock File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ITEM_ID |
The unique identifier of the item - this references the ITEM_ID column in the STORE_ITEM table. |
Yes |
VARCHAR2 (25) |
STORE_ID |
The unique identifier of the store - this references the STORE_ID column in the STORE_ITEM table and forms the primary key together with the ITEM_ID field. |
Yes |
NUMBER (10, 0) |
QUANTITY_TOTAL |
The total quantity of the item that is sellable. |
Yes |
NUMBER (12, 4) |
QUANTITY_RESERVED |
The reserved quantity of the item. |
Yes |
NUMBER (12, 4) |
QUANTITY_CUSTOMER_RESERVE |
The quantity of the item reserved for customers. |
Yes |
NUMBER (12, 4) |
QUANTITY_IN_TRANSIT |
The in transit quantity of the item. |
Yes |
NUMBER (12, 4) |
QUANTITY_VENDOR_RETURN |
The vendor return quantity of the item. |
Yes |
NUMBER (12, 4) |
QUANTITY_NON_SELLABLE |
The non-sellable quantity of the item. |
Yes |
NUMBER (12, 4) |
All records in this file will be used to populate the STORE_ITEM_STOCK table. Records where the QUANTITY_NON_SELLABLE field is non-zero will be used to populate the STORE_ITEM_STOCK_NONSELL table.
Example CSV File
IDL-STOREITEMSTOCK-*.csv
1,1,1.1,1.2,1.3,1.4,1.5,1.6
Store UIN Admin Item File
Table 2-27 Store UIN Admin Item File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ITEM_ID |
The unique identifier of the item - this references the ITEM_ID column in the ITEM table. |
Yes |
VARCHAR2 (25) |
STORE_ID |
The unique identifier of the store - this references the ID column in the STORE table and forms the primary key together with the ITEM_ID field. |
Yes |
NUMBER (10, 0) |
UIN_TYPE |
The UIN (Unique Identification Number) type - 1 (Serial Number) or 2 (Auto-generated Serial Number). |
Yes |
NUMBER (2, 0) |
UIN_LABEL_ID |
The UIN label identifier - SN (Serial Number), IM (IMEI), LN (License Number), PN (Plate Number) or SIN (SIN). |
Yes |
VARCHAR2 (3) |
CAPTURE_TIME_ID |
The time to capture the UIN - 1 (Sales) or 2 (Store Receiving). |
No |
NUMBER (2, 0) |
EXTERNAL_CREATE_ALLOWED |
Flag to indicate if the UIN can be created externally - Y or N. |
No |
VARCHAR2 (1) |
TICKET_FORMAT_ID |
The ticket format identifier. |
No |
NUMBER (10, 0) |
Example CSV File
IDL-STOREUINADMINITEM-*.csv
1,1,1,SN,1,N,1
Supplier Address File
Table 2-28 Supplier Address File Layout
Field Name | Description | Required | Type |
---|---|---|---|
EXTERNAL_ID |
The external identifier of the address. |
Yes |
VARCHAR2 (25) |
SUPPLIER_ID |
The unique identifier of the supplier - forms a unique key together with the EXTERNAL_ID field. |
Yes |
NUMBER (10, 0) |
ADDRESS_TYPE |
The type of address - 01 (Business), 02 (Postal), 03 (Returns), 04 (Order), 05 (Invoice), 06 (Remittance), 07 (Billing), 08 (Delivery) or 09 (External). |
Yes |
VARCHAR2 (2) |
IS_PRIMARY |
Flag indicating if this is the primary address - Y or N. |
Yes |
VARCHAR2 (1) |
ADDRESS_LINE_1 |
The first line of the address. |
No |
VARCHAR2 (240) |
ADDRESS_LINE_2 |
The second line of the address. |
No |
VARCHAR2 (240) |
ADDRESS_LINE_3 |
The third line of the address. |
No |
VARCHAR2 (240) |
CITY |
The city. |
No |
VARCHAR2 (120) |
STATE |
The state. |
No |
VARCHAR2 (3) |
COUNTRY_ID |
The ISO 3166 2- (or 3-) letter country code. |
No |
VARCHAR2 (3) |
POSTAL_CODE |
The postal code. |
No |
VARCHAR2 (30) |
CONTACT_NAME |
The contact name. |
No |
VARCHAR2 (120) |
CONTACT_PHONE |
The contact phone number. |
No |
VARCHAR2 (20) |
CONTACT_FAX |
The contact fax number. |
No |
VARCHAR2 (20) |
CONTACT_EMAIL |
The contact email address. |
No |
VARCHAR2 (100) |
COUNTY |
The county. |
No |
VARCHAR2 (250) |
Example CSV File
IDL-SUPPLIERADDR-*.csv
1,1,01,Y,Line 1,Line 2,Line 3,City,MN,USA,Postcode,Contact Name,Contact_Phone,Contact_Fax,Contact_Email,County
Supplier CFA File
Table 2-29 Supplier CFA File Layout
Field Name | Description | Required | Type |
---|---|---|---|
SUPPLIER_ID |
The unique identifier of the supplier - this references the ID column in the SUPPLIER table. |
Yes |
NUMBER (10, 0) |
NAME |
The name of the custom flex attribute - forms the primary key together with the SUPPLIER_ID field. |
Yes |
VARCHAR2 (30) |
VALUE |
The value of the custom flex attribute. |
No |
VARCHAR2 (250) |
VALUE_DATE |
The date value of the custom flex attribute in "yyyy-MM-dd" format. |
No |
DATE |
Example CSV File
IDL-SUPPLIERCFA-*.csv
2,Name 2,Value 2,
3,Name 3,,2021-10-06
Supplier File
Table 2-30 Supplier File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ID |
The unique identifier of the supplier. |
Yes |
NUMBER (10, 0) |
DUNS_NUMBER |
The Dun and Bradstreet number to identify the supplier. |
No |
VARCHAR2 (9) |
NAME |
The name of the supplier. |
No |
VARCHAR2 (240) |
STATUS |
The status of the supplier - A (Active) or I (Inactive). |
No |
VARCHAR2 (1) |
LOCALE_LANGUAGE |
The ISO 3166 language to which the supplier is assigned - see Supported Locales. |
No |
VARCHAR2 (3) |
LOCALE_COUNTRY |
The ISO 3166 2- (or 3-) letter country code to which the supplier is assigned. |
No |
VARCHAR2 (3) |
CURRENCY_CODE |
The ISO 4217 currency code of the supplier. |
No |
VARCHAR2 (3) |
RETURN_ALLOWED |
Flag indicating if the supplier will accept returns - Y or N. |
No |
VARCHAR2 (1) |
AUTHORIZATION_REQUIRED |
Flag indicating if returns must be accompanied by an authorization number - Y or N. |
No |
VARCHAR2 (1) |
PO_CREATE_ALLOWED |
Flag indicating if purchase orders can be created - Y or N. |
No |
VARCHAR2 (1) |
VENDOR_CHECK |
Flag indicating if orders from this supplier will require vendor control - Y or N. |
No |
VARCHAR2 (1) |
VENDOR_CHECK_PERCENT |
The percentage of items per receipt that will be marked for vendor checking. |
No |
NUMBER (12, 4) |
PARENT_ID |
The identifier of the parent supplier. |
No |
VARCHAR2 (128) |
QUANTITY_LEVEL |
The supplier order quantity level - CA (Case) or EA (Each). |
Yes |
VARCHAR2 (6) |
TAX_ID |
The unique tax identification number of the supplier. |
No |
VARCHAR2 (18) |
DELIVERY_DISCREPANCY_TYPE |
The delivery discrepancy type - 0 (Allow), 1 (Overage) or 2 (Restricted). |
No |
NUMBER (2, 0) |
Example CSV File
IDL-SUPPLIER-*.csv
1,1111,Supplier 1,A,en,GB,GBP,Y,N,Y,Y,12345678.1234,Parent Of 1,CA,1234,0
Supplier Item Country File
Table 2-31 Supplier Item Country File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ITEM_ID |
The unique identifier of the item. |
Yes |
VARCHAR2 (25) |
SUPPLIER_ID |
The unique identifier of the supplier. |
Yes |
NUMBER (10, 0) |
COUNTRY_ID |
The ISO 3166 2- (or 3-) letter country code. |
Yes |
VARCHAR2 (3) |
CASE_SIZE |
The default number of items within a case from the supplier. |
No |
NUMBER (12, 4) |
UNIT_COST_CURRENCY |
The unit cost currency of the item for that supplier in that country. |
No |
VARCHAR2 (3) |
UNIT_COST_VALUE |
The unit cost of the item for that supplier in that country. |
No |
NUMBER (12, 4) |
The primary key comprises the ITEM_ID, SUPPLIER_ID and COUNTRY_ID fields.
Example CSV File
IDL-SUPPLIERITEMCOUNTRY-*.csv
1,1,GB,12345678.9012,GBP,11111111.1111
Supplier Item Country Dimension File
Table 2-32 Supplier Item Country Dimension File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ITEM_ID |
The unique identifier of the item - references the ITEM_ID column in the SUPPLIER_ITEM_COUNTRY table. |
Yes |
VARCHAR2 (25) |
SUPPLIER_ID |
The unique identifier of the supplier - references the SUPPLIER_ID column in the SUPPLIER_ITEM_COUNTRY table. |
Yes |
NUMBER (10, 0) |
COUNTRY_ID |
The ISO 3166 2- (or 3-) letter country code - references the COUNTRY_ID column in the SUPPLIER_ITEM_COUNTRY table. |
Yes |
VARCHAR2 (3) |
DIMENSION_OBJECT |
The dimension object. |
Yes |
VARCHAR2 (6) |
PRESENTATION_METHOD |
The packaging (if any) being taken into consideration in the specified dimensions. |
No |
VARCHAR2 (6) |
LENGTH |
The length of the dimension object. |
No |
NUMBER (12, 4) |
WIDTH |
The width of the dimension object. |
No |
NUMBER (12, 4) |
HEIGHT |
The height of the dimension object. |
No |
NUMBER (12, 4) |
DIMENSION_UOM |
The unit of measurement for length, width and height. |
No |
VARCHAR2 (4) |
WEIGHT |
The weight of the dimension object. |
No |
NUMBER (12, 4) |
NET_WEIGHT |
The net weight of the dimension object. |
No |
NUMBER (12, 4) |
WEIGHT_UOM |
The unit of measurement for weight. |
No |
VARCHAR2 (4) |
LIQUID_VOLUME |
The liquid volume or capacity of the dimension object. |
No |
NUMBER (12, 4) |
LIQUID_VOLUME_UOM |
The unit of measurement for liquid volume. |
No |
VARCHAR2 (4) |
STATISTICAL_CUBE |
The statistical value of the dimension object's dimensions to be used for loading purposed. |
No |
NUMBER (12, 4) |
The primary key comprises the ITEM_ID, SUPPLIER_ID, COUNTRY_ID and DIMENSION_OBJECT fields.
Example CSV File
IDL-SUPPLIERITEMCOUNTRYDIM-*.csv
1,1,GB,CASE,BARE,1,1,1,M,1.1,1.01,KG,0.1,ML,1
Supplier Item File
Table 2-33 Supplier Item File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ITEM_ID |
The unique identifier of the item - references the ITEM_ID column in the ITEM table. |
Yes |
VARCHAR2 (25) |
SUPPLIER_ID |
The unique identifier of the supplier - this references the ID column in the SUPPLIER table and forms the primary key together with the ITEM_ID field. |
Yes |
NUMBER (10, 0) |
VENDOR_PRODUCT_NUMBER |
The vendor product number. |
No |
VARCHAR2 (256) |
IS_PRIMARY |
Flag indicating if the supplier is the primary supplier for this item - Y or N. |
No |
VARCHAR2 (3) |
Example CSV File
IDL-SUPPLIERITEM-*.csv
1,1,1,Y
Supplier Item Manufacture File
Table 2-34 Supplier Item Manufacture File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ITEM_ID |
The unique identifier of the item - references the ITEM_ID column in the ITEM table. |
Yes |
VARCHAR2 (25) |
SUPPLIER_ID |
The unique identifier of the supplier - references the ID column in the SUPPLIER table. |
Yes |
NUMBER (10, 0) |
COUNTRY_ID |
The ISO 3166 2- (or 3-) letter country code. |
Yes |
VARCHAR2 (3) |
IS_PRIMARY |
Flag indicating if this is the primary country of manufacture - Y or N. |
No |
VARCHAR2 (1) |
The primary key comprises the ITEM_ID, SUPPLIER_ID and COUNTRY_ID fields.
Example CSV File
IDL-SUPPLIERITEMMANUFACTURE-*.csv
1,1,GB,Y
Supplier Item UOM File
Table 2-35 Supplier Item UOM File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ITEM_ID |
The unique identifier of the item - this references the ITEM_ID column in the SUPPLIER_ITEM table. |
Yes |
VARCHAR2 (25) |
SUPPLIER_ID |
The unique identifier of the supplier - this references the SUPPLIER_ID column in the SUPPLIER_ITEM table. |
Yes |
NUMBER (10, 0) |
UNIT_OF_MEASURE |
The unit of measure - this references the UOM column in the UOM_CLASS table. |
Yes |
VARCHAR2 (4) |
VALUE |
The equivalent value of the item/suppliers shipping carton in the associated unit of measure. |
Yes |
NUMBER (20, 4) |
The primary key comprises the ITEM_ID, SUPPLIER_ID and UNIT_OF_MEASURE fields.
Example CSV File
IDL-SUPPLIERITEMUOM-*.csv
1,1,g,1234567890123456.7890
Supplier Organization File
Table 2-36 Supplier Organization File Layout
Field Name | Description | Required | Type |
---|---|---|---|
SUPPLIER_ID |
The unique identifier of the supplier - this references the ID column in the SUPPLIER table. |
Yes |
NUMBER (10, 0) |
ORGANIZATION_UNIT_ID |
The organization unit identifier - forms a unique key together with the SUPPLIER_ID field. |
Yes |
VARCHAR2 (15) |
Example CSV File
IDL-SUPPLIERORGANIZATION-*.csv
1,Org 1
Transfer Zone File
Table 2-37 Transfer Zone File Layout
Field Name | Description | Required | Type |
---|---|---|---|
TRANSFER_ZONE |
The unique identifier of the transfer zone. |
Yes |
VARCHAR2 (128) |
DESCRIPTION |
The description of the transfer zone. |
Yes |
VARCHAR2 (255) |
Example CSV File
IDL-TRANSFERZONE-*.csv
1,DESCRIPTION FOR TRANSFER ZONE 1
UDA File
Table 2-38 UDA File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ID |
The unique identifier of the user defined attribute. |
Yes |
NUMBER (5, 0) |
TYPE |
The type of user defined attribute - FF, DT or LV. |
Yes |
VARCHAR2 (2) |
DESCRIPTION |
The description of the user defined attribute. |
Yes |
VARCHAR2 (120) |
PRINT_TICKET |
Flag indicating if item tickets should be printed for this user defined attribute - Y or N. |
Yes |
VARCHAR2 (1) |
PRINT_LABEL |
Flag indicating if item labels should be printed for this user defined attribute - Y or N. |
Yes |
VARCHAR2 (1) |
Example CSV File
IDL-UDA-*.csv
1,FF,DESCRIPTION FOR 1,Y,Y
UDA LOV File
Table 2-39 UDA LOV File Layout
Field Name | Description | Required | Type |
---|---|---|---|
UDA_ID |
The unique identifier of the user defined attribute - this references the ID column in the UDA table. |
Yes |
NUMBER (5, 0) |
LOV_ID |
The identifier for the LV (List of Values) user defined attribute value - forms the primary key together with the UDA_ID field. |
Yes |
VARCHAR2 (25) |
DESCRIPTION |
The description of the user defined attribute value. |
Yes |
VARCHAR2 (250) |
Example CSV File
IDL-UDALOV-*.csv
3,LOV_1,DESCRIPTION FOR LOV_1
UOM Class File
Table 2-40 UOM Class File Layout
Field Name | Description | Required | Type |
---|---|---|---|
UOM |
The unique identifier of the unit of measure. |
Yes |
VARCHAR2 (4) |
UOM_CLASS |
The type of unit of measure - AREA, DIMEN, LVOL, MASS, MISC, PACK, QTY or VOL. |
Yes |
VARCHAR2 (6) |
DESCRIPTION |
The description of the unit of measure. |
Yes |
VARCHAR2 (120) |
Example CSV File
IDL-UOMCLASS-*.csv
g,MASS,DESCRIPTION FOR 'GRAM'
UOM Conversion File
Table 2-41 UOM Conversion File Layout
Field Name | Description | Required | Type |
---|---|---|---|
FROM_UOM |
The unit of measure to convert from - this references the UOM column in the UOM_CLASS table. |
Yes |
VARCHAR2 (4) |
TO_UOM |
The unit of measure to convert to - this references the UOM column in the UOM_CLASS table and forms the primary key together with the FROM_UOM field. |
Yes |
VARCHAR2 (4) |
FACTOR |
The factor to apply when converting the unit of measure. |
Yes |
NUMBER (20, 10) |
Example CSV File
IDL-UOMCONVERSION-*.csv
g,lb,453.592
Warehouse Address File
Table 2-42 Warehouse Address File Layout
Field Name | Description | Required | Type |
---|---|---|---|
EXTERNAL_ID |
The external identifier of the address. |
Yes |
VARCHAR2 (25) |
SUPPLIER_ID |
The unique identifier of the warehouse - forms a unique key together with the EXTERNAL_ID field. |
Yes |
NUMBER (10, 0) |
ADDRESS_TYPE |
The type of address - 01 (Business), 02 (Postal), 03 (Returns), 04 (Order), 05 (Invoice), 06 (Remittance), 07 (Billing), 08 (Delivery) or 09 (External). |
Yes |
VARCHAR2 (2) |
IS_PRIMARY |
Flag indicating if this is the primary address - Y or N. |
Yes |
VARCHAR2 (1) |
ADDRESS_LINE_1 |
The first line of the address. |
No |
VARCHAR2 (240) |
ADDRESS_LINE_2 |
The second line of the address. |
No |
VARCHAR2 (240) |
ADDRESS_LINE_3 |
The third line of the address. |
No |
VARCHAR2 (240) |
CITY |
The city. |
No |
VARCHAR2 (120) |
STATE |
The state. |
No |
VARCHAR2 (3) |
COUNTRY_ID |
The ISO 3166 2- (or 3-) letter country code. |
No |
VARCHAR2 (3) |
POSTAL_CODE |
The postal code. |
No |
VARCHAR2 (30) |
CONTACT_NAME |
The contact name. |
No |
VARCHAR2 (120) |
CONTACT_PHONE |
The contact phone number. |
No |
VARCHAR2 (20) |
CONTACT_FAX |
The contact fax number. |
No |
VARCHAR2 (20) |
CONTACT_EMAIL |
The contact email address. |
No |
VARCHAR2 (100) |
COUNTY |
The county. |
No |
VARCHAR2 (250) |
Example CSV File
IDL-WAREHOUSEADDR-*.csv
1,1,01,Y,Line 1,Line 2,Line 3,City,MN,USA,Postcode,Contact Name,Contact_Phone,Contact_Fax,Contact_Email,County
Warehouse Class File
Table 2-43 Warehouse File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ID |
The unique identifier of the warehouse. |
Yes |
NUMBER (10, 0) |
NAME |
The name of the warehouse. |
Yes |
VARCHAR2 (150) |
ORGANIZATION_UNIT_ID |
The organization unit identifier of the warehouse. |
No |
VARCHAR2 (15) |
LOCALE_COUNTRY |
The ISO 3166 2- (or 3-) letter country code. |
No |
VARCHAR2 (3) |
CURRENCY_CODE |
The ISO 4217 currency code of the warehouse. |
No |
VARCHAR2 (40) |
PHYSICAL_WH |
The identifier of the physical warehouse corresponding to the warehouse. |
Yes |
NUMBER (10, 0) |
PRIMARY_VWH |
The identifier of the primary virtual warehouse corresponding to the warehouse. |
No |
NUMBER (10, 0) |
NAME_SECONDARY |
The secondary name of the warehouse. |
No |
VARCHAR2 (150) |
STOCKHOLDING_IND |
Flag indicating if the warehouse is a stock holding location. |
No |
VARCHAR2 (1) |
DUNS_NUMBER |
The Dun and Bradstreet number to identify the location. |
No |
VARCHAR2 (9) |
DUNS_LOC |
The Dun and Bradstreet number to identify the location. |
No |
VARCHAR2(4) |
TSF_ENTITY_ID |
The transfer entity identifier of the warehouse. |
No |
NUMBER (10, 0) |
INBOUND_HANDLING_DAYS |
The number of days that the warehouse requires to receive any item and get it to the shelf so that it is ready to pick. |
No |
NUMBER (2, 0) |
CHANNEL_ID |
The channel identifier of the warehouse. |
No |
NUMBER (4, 0) |
CHANNEL_NAME |
The name of the channel. |
No |
VARCHAR2 (120) |
FINISHER_IND |
Flag indicating if the warehouse is a finisher - Y or N. |
No |
VARCHAR2 (1) |
|
The email address of the warehouse. |
No |
VARCHAR2 (100) |
All records in this file will be used to populate the WAREHOUSE_VIRTUAL (Virtual Warehouse) table. Records where the ID and PHYSICAL_WH match will be used to populate the WAREHOUSE (Physical Warehouse) table with a subset of the fields: ID, NAME, ORGANIZATION_UNIT_ID, LOCALE_COUNTRY and CURRENCY_CODE.
Example CSV File
IDL-WAREHOUSE-*.csv
1,Virtual Warehouse 1,Org Unit ID 1,GB,GBP,1,11,Secondary Name 1,Y,D&B NUM 1,LOC1,1234567890,96,1234,CHANNEL 1234,N,warehouse1@abc.com
Warehouse Item File
Table 2-44 Warehouse Item File Layout
Field Name | Description | Required | Type |
---|---|---|---|
ITEM_ID |
The unique identifier of the item - references the ITEM_ID column in the ITEM table. |
Yes |
VARCHAR2 (25) |
WAREHOUSE_ID |
The unique identifier of the warehouse - this references the ID column in the WAREHOUSE table and forms the primary key together with the ITEM_ID field. |
Yes |
NUMBER (10, 0) |
STATUS |
The status of the warehouse item - ' ' (None), A (Active), C (Discontinued), I (Inactive), D (Deleted), Q (Auto-stocked) or N (Non-ranged). |
Yes |
VARCHAR2 (2) |
QUANTITY_TOTAL |
The total quantity of the warehouse item. |
Yes |
NUMBER (12, 4) |
QUANTITY_RESERVED |
The reserved quantity of the warehouse item. |
Yes |
NUMBER (12, 4) |
QUANTITY_UNAVAILABLE |
The unavailable quantity of the warehouse item. |
Yes |
NUMBER (12, 4) |
QUANTITY_IN_TRANSIT |
The in transit quantity of the warehouse item. |
Yes |
NUMBER (12, 4) |
STANDARD_UOM |
The standard unit of measure of the warehouse item. |
No |
VARCHAR2 (4) |
Example CSV File
IDL-WAREHOUSEITEM-*.csv
1,1,A,12345678.9012,34567890.1234,56789012.3456,78901234.5678,kg
Transactional Data Seeding
Transaction data seeding in a SIOCS installation is achieved by uploading data in CSV (comma-separated values) files to Object Storage via FTS (File Transfer Service).
Process Flow
This is a general overview of the process flow.
-
Each file that loaded is broken into groupings of transactions (1000 per group). Each grouping is given a processing number and a request to process the data is places in MPS.
-
The MPS messages system will grab processing requests off the queue and process each group (of 1000) at a time committing transactions that are successful and failing transactions that have problems.
-
The user can monitor the process both through the MPS Staged Message screen and the Integration Dashboard screen.
-
The user can then export the errors, make corrections, and reload a file. (See Errors and Reprocessing.)
Process Ordering
The processing of sets of data needs to be in order with one set of data being completed before the next begins.
Note:
Data seeding of foundation data and data setup should be completed prior to data seeding transactional data.
Purchase Order Group
Purchase orders and DSDs are loaded for each individual store. The purchase orders for a single store must be fully loaded and finished processing, along with error corrections, prior to loading DSD (Deliveries from Vendors) information for the same store.
Transfer Group
Transfers are not loaded for each individual store. Transfers must be fully loaded and finish processing, along with desired error corrections, prior to loading additional transfer information. Once transfers are loaded, you load transfer information in the following sequence per store: allocation, transfer shipment, transfer delivery.
UIN
The UIN file is loaded by store. Loading in stock UINs is dependent only on the foundation data.
Errors and Reprocessing
When errors occur, they must be manually dealt with by the user.
-
During processing, transactions that fail at any level (header, carton, detail) will fail the entire transaction.
-
The number of failures for a particular data type can be seen in the Integration Dashboard.
-
You can load the same file for different stores without issue (such as loading DSDs for Store 1 and Store 2) prior to dealing with errors, however, you should not load the same data type file for the same store without first clearing out the errors. For example, do not load DSDs for Store 1 and again for Store 1 without first dealing with errors.
-
The integration dashboard will allow a user to export error data back out for examination and correction. If more than one store worth of errors exists, it will create one file for each store on export.
-
Once the data is exported, the errors for the data type should be deleted prior to re-loading additional information. For example, load transfer shipments for store 1, export the failures, delete the failures, correct the failures, reload ONLY the corrected data from transfer shipments for store 1 again. Removal of erroneous data can be done through the integration dashboard.
Note:
Once a transaction is successfully processed and reaches the transaction tables without error, it cannot be loaded again. Additional attempts to load the data will fail with duplicate data errors. Data seeding will not perform updates on currently existing data.
Volume Considerations
This is a general overview of the process flow for basic understanding.
-
To prevent system overload, file sizes should remain under or around 100,000 transactions (that is 100,000 header rows or overall transactions, not rows in file).
-
It is recommended that only 1 or 2 files be loaded at a time and that the processing is completed on these files prior to loading more files.
Integration Dashboard
The integration dashboard screen (see Technical Maintenance Screens – Integration Dashboard) displays the current state of integration processing, which includes the transaction data seeding. This screen can be used to do the following:
-
See the number of records currently processing for a data type
-
See the number of failed records for a data type
-
Export error data back out to file
-
Clear error data out so that reprocessing of a file can occur
Transaction Data Seeding Modules
Table 2-45 Transaction Data Seeding Modules
Module | Description |
---|---|
Allocation |
Loads warehouse to store transfer allocation information by store. |
DSD |
Loads direct store delivery information by store. |
Purchase Order |
Load purchase order information by store. |
Transfer |
Load transfer document information |
Transfer Shipment |
Loads transfer shipment information by shipping store. |
Transfer Delivery |
Load transfer delivery information by receiving store. |
UIN |
Load basic UIN information for a limited set of statuses. |
File Layouts
-
All files should be in CSV (comma-separated values) format, with either a ".csv" or ".dat" filename extension. The batch jobs also support zipped files which will be extracted upon download and processed individually.
-
Empty or blank fields within a record will be considered null. Every column must be present even if it is empty or null.
-
String fields containing a comma or double quote must be quoted (with double quotes), a double quote in a field must be represented by 2 double quote characters. Line breaks within quoted fields are not supported.
-
The filename format is IDL-[MODULENAME]-XXXX.csv(/dat/zip). Files contained within .zip files must adhere to the same filename format.
-
To run the Initial Store Data
-
File Import batch job for a particular Store, the filename format is IDL-
-
[MODULENAME]-[STOREID]-XXXX.csv(/dat/zip). Any files which do not adhere to the filename format will not be downloaded or processed. Files > 500MB will be rejected: the file should be split into smaller files and uploaded as a .zip file.
-
It is recommended to not edit the .csv files in Excel as this can lead to formatting issues.
-
Rows within the file can have different layouts. The row type column located first in any row defines what kind of row it is and the format it must follow.
File Date Requirements
-
All columns noted as required must have values within the file or the entire file will be failed.
-
The data within the file for a particular column must match the data type of the column or the entire file will be failed.
-
Dates must be entered in the format YYYY-MM-DD HH:MM:SS (examples: 2022-12-06 14:34:21).
-
Dates must be GMT as the file will parse and process the dates as GMT dates.
Allocation File
Allocation files must contain information for a single store only.
Table 2-46 Allocation File Row Layout
Field Name | Description | Required | Type |
---|---|---|---|
IMPORT_ALLOC_ID |
A unique identifier of this imported allocation. |
Yes |
VARCHAR2(128) |
EXTERNAL_ID |
The unique allocation identifier from an external system. |
Yes |
NUMBER(12) |
ITEM_ID |
The unique identifier of the item to be delivered. |
Yes |
VARCHAR2(25) |
STORE_ID |
The unique identifier of the store receiving the allocation. |
Yes |
NUMBER(10) |
WAREHOUSE_ID |
The unique identifier of the warehouse shipping the item. |
Yes |
NUMBER(10) |
STATUS |
The status of the allocation |
Yes |
NUMBER(2) |
DISTRIBUTION_PARENT_ID |
The unique identifier of the parent transfer document. |
No |
VARCHAR2(25) |
DELIVERY_DATE |
The date the allocation is expected to be delivered. |
No |
DATE |
CONTEXT_ID |
The identifier of a context associated to the allocation. |
No |
NUMBER(18) |
CONTEXT_VALUE |
A value associated to the context |
No |
VARCHAR2(25) |
DELIVERY_SLOT_ID |
The unique identifier of the delivery slot of expected delivery time. |
No |
NUMBER(15) |
QUANTITY_EXPECTED |
The quantity expected to be delivered. |
No |
NUMBER(20,4) |
QUANTITY_RECEIVED |
The quantity that has been received. |
No |
NUMBER(20,4) |
QUANTITY_DAMAGED |
The quantity that has been received as damaged. |
No |
NUMBER(20,4) |
Data Definition
Status: (1) Approved, (2) Completed, (3) Canceled
Example CSV File
IDL-ALLOCATION-5000.csv
1,5001,100637113,5000,9000,1,1234,2022-10-14 10:40:21,145,CV145,4523026194,100,0,0
DSD File
-
Direct Store Delivery files must contain information for a single store only.
-
Purchase order must be loaded and complete processing prior to loading direct store deliveries.
-
Each delivery must have at least one carton associated to it.
-
Each carton must have at least one item associated to it.
-
The status of the delivery is not uploaded, but rather calculated from the status of the cartons.
Table 2-47 DSD File Row Layout (H – Header)
Field Name | Description | Required | Type |
---|---|---|---|
ROW_TYPE |
The type of row that is represented. |
Yes |
“H” |
IMPORT_DSD_ID |
The unique identifier of the delivery record. |
Yes |
VARCHAR2(128) |
IMPORT_PO_ID |
The purchase order that the delivery is associated to. |
Yes |
VARCHAR2(128) |
STORE_ID |
The unique identifier of the store receiving the inventory. |
Yes |
NUMBER(10) |
SUPPLIER_ID |
The unique identifier of the supplier shipping the inventory. |
Yes |
NUMBER(10) |
ORIGIN_TYPE |
The origin type of the delivery. |
Yes |
NUMBER(2) |
RECEIPT_NO |
Yes |
NUMBER(12) |
|
ASN_ID |
The advanced shipping notification of the delivery. |
No |
VARCHAR2(128) |
INVOICE_ID |
A unique identifier of an invoice associated to this delivery. |
No |
VARCHAR2(128) |
INVOICE_DATE |
The date of the delivery invoice. |
No |
DATE |
CURRENCY_CODE |
A currency code identifying the type of currency. |
No |
VARCHAR2(3) |
INVOICE_COST_VALUE |
The cost of the invoice. |
No |
NUMBER(12,4) |
CARRIER_ENTITY |
The name of the carrier. |
No |
VARCHAR2(128) |
CARRIER_TYPE |
The type of the carrier. |
No |
NUMBER(2) |
CARRIER_CODE |
Unique code that identifies the carrier. |
No |
VARCHAR2(4) |
COUNTRY_CODE |
A country code. |
No |
VARCHAR(3) |
SOURCE_ADDRESS |
The address of the source sending the delivery to the store. |
No |
VARCHAR2(1000) |
LICENSE_PLATE |
The license plate of the delivery vehicle. |
No |
VARCHAR2(128) |
FREIGHT_ID |
A freight identifier associated to the delivery. |
No |
VARCHAR2(128) |
BOL_EXTERNAL_ID |
An external identifier of a bill of lading record. |
No |
VARCHAR2(128) |
FISCAL_DOCUMENT_ID |
The Fiscal Document Number from a fiscal document system. |
No |
VARCHAR2(128) |
EXPECTED_DATE |
The expected date of the delivery. |
No |
DATE |
RECEIVED_DATE |
The date the delivery was received. |
No |
DATE |
RECEIVED_USER |
The user who received the delivery record. |
No |
VARCHAR2(128) |
CREATE_DATE |
The date the delivery record was created. |
Yes |
DATE |
CREATE_USER |
The user that created the delivery record. |
No |
VARCHAR2(128) |
UPDATE_DATE |
The date the delivery record was last updated. |
No |
DATE |
UPDATE_USER |
The user who last updated the delivery record. |
No |
VARCHAR2(128) |
Table 2-48 DSD Row Layout (C – Carton)
Field Name | Description | Required | Type |
---|---|---|---|
ROW_TYPE |
The type of row that is represented. |
Yes |
“C” |
IMPORT_DSD_ID |
An identifier assigned to the DSD header to tie it to cartons and items within the file. |
Yes |
VARCHAR2(128) |
IMPORT_CARTON_ID |
An import identifier assigned to the carton to tie it to the items. |
Yes |
VARCHAR2(128) |
EXTERNAL_ID |
An external identifier of the carton used during integration publication. |
Yes |
VARCHAR2(128) |
STATUS |
The carton status (see Index). |
Yes |
NUMBER(4) |
REFERENCE_ID |
A Reference identifier. |
No |
VARCHAR2(128) |
DAMAGED_REASON |
The reason for container damage. |
No |
VARCHAR2(128) |
TRACKING_NUMBER |
The tracking number for the container. |
No |
VARCHAR2(128) |
SERIAL_CODE |
The serial code. |
No |
NUMBER(18) |
DAMAGE_REMAINING |
Indicates all remaining quantities should be damaged on final receipt. |
No |
VARCHAR2(1) |
UIN_REQUIRED |
The item UIN, Y if UIN item exists in container, else No |
Yes |
VARCHAR2(1) |
RECEIVE_AT_SHOP_FLOOR |
Y if receive at shop floor, else No |
Yes |
VARCHAR2(1) |
QUALITY_CONTROL |
Y indicates the container is flagged for detailed receiving. |
Y |
VARCHAR2(1) |
EXTERNAL_CREATE |
Yes indicates whether the delivery is external created. Valid values: Y or N. |
Yes |
VARCHAR2(1) |
ADJUSTED |
Indicates whether the delivery is adjusted. Valid values: Y or N |
Yes |
VARCHAR2(1) |
RECEIVE_DATE |
The date when the vendor delivery carton was received. |
No |
DATE |
RECEIVE_USER |
The user who received the vendor delivery carton. |
No |
VARCHAR2(128) |
CREATE_DATE |
The date when the vendor delivery carton was created. |
Yes |
DATE |
CREATE_USER |
The user who created the vendor delivery carton. |
No |
VARCHAR2(128) |
UPDATE_DATE |
The date when the vendor delivery carton was updated. |
No |
DATE |
UPDATE_USER |
The user who last updated the vendor delivery carton. |
No |
VARCHAR2(128) |
Table 2-49 DSD Row Layout (D – Detail)
Field Name | Description | Required | Type |
---|---|---|---|
ROW_TYPE |
The type of row that is represented. |
Yes |
“D” |
IMPORT_DSD_ID |
Identifier assigned to the DSD header to tie it to cartons and items within the file. |
Yes |
VARCHAR2(128) |
IMPORT_CARTON_ID |
Import identifier assigned to the carton to tie it to the items. |
Yes |
VARCHAR2(128) |
ITEM_ID |
The unique identifier of the item that is contained in this carton. |
Yes |
VARCHAR2(25) |
CASE_SIZE |
The number of units in the case that this item was shipped in. |
Yes |
NUMBER(10,2) |
QUANTITY_EXPECTED |
The total number of units expected in this direct delivery. |
No |
NUMBER(20,4) |
QUANTITY_RECEIVED |
The total number of units received in this direct delivery. |
No |
NUMBER(20,4) |
QUANTITY_DAMAGED |
The total number of units that were damaged when the direct delivery was received. |
No |
NUMBER(20,4) |
QUANTITY_RECEIVED_OVER |
Amount of received inventory over the expected quantities. |
No |
NUMBER(20,4) |
QUANTITY_DAMAGED_OVER |
Amount of damaged inventory over the expected quantities. |
No |
NUMBER(20,4) |
PREVIOUS_RECEIVED |
Units previous received when container is reopened for adjustment. |
No |
NUMBER(20,4) |
PREVIOUS_DAMAGED |
Units previous received as damaged when container is reopened for adjustment. |
No |
NUMBER(20,4) |
UNIT_COST_CURRENCY |
The unit cost currency code for the line item. |
No |
VARCHAR2(3) |
UNIT_COST_VALUE |
The unit cost value for the line item. |
No |
NUMBER(12,4) |
OVERRIDE_UNIT_COST_CURRENCY |
The override unit cost currency. |
No |
VARCHAR2(3) |
OVERRIDE_UNIT_COST_VALUE |
The override unit cost value. |
No |
NUMBER(12,4) |
IMPORT_PO_ID |
The import identifier used in the PO upload file that this DSD item is associated to. |
Yes |
VARCHAR2(128) |
Data Definition
Delivery Origin Type: (0) Asn, (1) PO, (2) DexNex, (3) Manual
Delivery Carrier Type: (0) Corporate, (1) Third Party
Carton Status: (1), New, (2) In Progress, (3) Submitted, (4) Received, 5 (Damaged), 6 (Missing), (7) Canceled
Example CSV File
IDL-VENDORDELIVERY-5000.csv
H,4,PO 02 TRY,5000,5115,1,3,POASN1,1,2022-10-10,USD,100.00,CI1,1,0,US,SA,LP1,FR1,BOLEID1,FDID1,2022-10-10,2022-10-10,1500,2022-10-10,15000,2022-10-10,15000
C,4,-1,EID1,1,REFID1,NO DAMAGED,TN1,0599123645,N,N,Y,Y,Y,N,2022-10-10 17:12:21,15000,2022-10-10 17:12:21,1500,2022-10-10 17:12:21,1500
D,4,-1,100668163,1,30,30,0,0,0,0,0,USD,50.00,USD,50.00,PO 02 TRY
Purchase Order File
Purchase order files must contain information for a single store only. For each “H” header record, there must be at least one “D” detail record.
Table 2-50 Purchase Order File Row Layout (H – Header)
Field Name | Description | Required | Type |
---|---|---|---|
ROW_TYPE |
The type of row that is represented. |
Yes |
“H” (Header) |
IMPORT_PO_ID |
A unique identifier of this imported purchase order. |
Yes |
VARCHAR2(128) |
EXTERNAL_ID |
An identifier of this purchase order from an external system. |
Yes |
VARCHAR2(128) |
STORE_ID |
The identifier of the store this purchase order is for. |
Yes |
NUMBER(10) |
SUPPLIER_ID |
The identifier of the supplier this purchase order is from. |
Yes |
VARCHAR2(128) |
STATUS |
The status or the purchase order. |
Yes |
NUMBER(4) |
EXTERNAL_STATUS |
The status of the purchase order in the originating system. |
Yes |
NUMBER(4) |
NOT_BEFORE_DATE |
Earliest date that the inventory should arrive at the store. |
No |
DATE |
NOT_AFTER_DATE |
Latest date that the inventory should arrive at the store. |
No |
DATE |
USER_ID |
User who originated the purchase order. |
No |
VARCHAR2(128) |
COMMENTS |
Comments associated to the purchase order. |
No |
VARCHAR2(2000) |
CUST_ORDER_ID |
The external identifier of a customer order associated to the purchase order. |
No |
VARCHAR2(128) |
FUL_ORD_EXTERNAL_ID |
The external identifier of the fulfilment order associated to the order. |
No |
VARCHAR2(128) |
SOURCE |
The originating source of the purchase order. |
Yes |
VARCHAR2(25) |
CREATE_DATE |
The date the purchase order was created. |
Yes |
DATE |
UPDATE_DATE |
The date the purchase order was updated. |
No |
DATE |
COMPLETE_DATE |
The date the purchase order was completed. |
No |
DATE |
Table 2-51 Purchase Order File Row Layout (D – Detail)
Field Name | Description | Required | Type |
---|---|---|---|
ROW_TYPE |
The type of row that is represented. |
Yes |
“D” (Detail) |
IMPORT_PO_ID |
The unique identifier from the (H)eader row this detail is associated to. |
Yes |
VARCHAR2(128) |
ITEM_ID |
The unique sku number. |
Yes |
VARCHAR2(25) |
SUPPLIER_COUNTRY |
The supplier country of origin |
Yes |
VARCHAR(3) |
CASE_SIZE |
The case size coming from the supplier. |
Yes |
NUMBER(10,2) |
QUANTITY_EXPECTED |
The number of units expected to be delivered to the store. |
Yes |
NUMBER(20,4) |
QUANTITY_RECEIVED |
The number of units received to date against the order. |
No |
NUMBER(20,4) |
UNIT_COST_CURRENCY |
The unit cost ISO currency code. |
No |
VARCHAR2(3) |
UNIT_COST_VALUE |
The unit cost value of the item. |
No |
NUMBER(12,4) |
PREFERRED_UOM |
The preferred unit of measure of this item on the order. |
No |
VARCHAR(4) |
Data Definition
Purchase Order Status: (1) New, (2) In Progress, (3) Canceled, (4) Completed
Purchase Order External Status: (1) Worksheet, 2() Submitted, (3) Approved, (4) Closed
Example CSV File
IDL-PURCHASEORDER-5000.csv
H,abcde,EXTID1,5000,5100,1,2,2022-10-06 00:00:00,2022-10-06 00:00:00,15000,NO COMMENTS,1000,POIDSLFILE1,SIOCS,2022-10-06 12:07:01,2022-10-06 12:07:02,2022-10-06 12:07:10
D,abcde,100654087,US,1,100,100,USD,1.55,EA
Transfer File
-
There must be a least one detail row for each header row.
-
Reserved quantities will be incremented by any remaining quantities for the item at the source location.
-
If unavailable inventor is used, the unavailable inventory will be decremented at the source location.
-
The transfer quantities are considered final and correct. Therefore, shipments and deliveries referencing the transfer and loaded later will not update the transfer information.
Table 2-52 Transfer File Row Layout (H – Header)
Field Name | Description | Required | Type |
---|---|---|---|
ROW_TYPE |
The type of row that is represented. |
Yes |
“H” |
IMPORT_TSF_ID |
An import identifier to tie this header with line items. |
Yes |
VARCHAR2(128) |
EXTERNAL_ID |
An external identifier supplied from an external system. |
No |
VARCHAR2(128) |
DISTRO_NUMBER |
Yes |
VARCHAR2(128) |
|
SOURCE_TYPE |
Source location type. |
Yes |
NUMBER(2) |
SOURCE_ID |
The identifier of the source location of the transfer. |
Yes |
NUMBER(10) |
DESTINATION_TYPE |
Destination location type. |
Yes |
NUMBER(2) |
DESTINATION_ID |
The identifier of the destination location of the transfer. |
Yes |
NUMBER(10) |
STATUS |
The transfer status. |
Yes |
NUMBER(2) |
ORIGIN_TYPE |
The origin type of the transfer. |
Yes |
NUMBER(2) |
CONTEXT_ID |
Unique identifier of a context associated to the transfer. |
No |
NUMBER(18) |
CONTEXT_VALUE |
A value or some information related to the context associated to the transfer. |
No |
VARCHAR2(25) |
FUL_ORD_EXTERNAL_ID |
External system identifier of the fulfillment order. |
No |
VARCHAR2(128) |
CUST_ORD_EXTERNAL_ID |
External system identifier of the customer order. |
No |
VARCHAR2(128) |
USE_AVAILABLE |
The Use Available, Y indicates the transfer must use available stock, N indicates it uses unavailable stock. |
Yes |
VARCHAR2(1) |
ALLOW_PARTIAL_DELIVERY |
Y indicates that the partial delivery is allowed for the transfer, N indicates it is not. |
Yes |
VARCHAR2(1) |
AUTHORIZATION_CODE |
An authorization code required for the transfer. |
No |
VARCHAR2(12) |
NOT_AFTER_DATE |
Date after which the transfer is no longer valid. |
No |
DATE |
REQUEST_DATE |
The date the transfer was requested. |
No |
DATE |
REQUEST_USER |
The user that requested the transfer. |
No |
VARCHAR2(128) |
APPROVAL_DATE |
The date the transfer was approved. |
No |
DATE |
APPROVAL_USER |
The user that approved the transfer. |
No |
VARCHAR2(128) |
CREATE_DATE |
The date this record was created. |
Yes |
DATE |
CREATE_USER |
The user that created this record. |
No |
VARCHAR2(128) |
UPDATE_DATE |
The date this record was last updated. |
No |
DATE |
UPDATE_USER |
The user that last updated this record. |
No |
VARCHAR2(128) |
Table 2-53 Transfer File Row Layout (D – Detail)
Field Name | Description | Required | Type |
---|---|---|---|
ROW_TYPE |
The type of row that is represented. |
Yes |
“D” |
IMPORT_TSF_ID |
The unique transfer identifier. |
Yes |
VARCHAR2(128) |
ITEM_ID |
The item identifier. |
Yes |
VARCHAR2(25) |
CASE_SIZE |
The case size associated to this line item. |
Yes |
NUMBER(10,2) |
QUANTITY_REQUESTED |
The quantity that was requested. |
No |
NUMBER(20,4) |
QUANTITY_APPROVED |
The quantity that was approved. |
No |
NUMBER(20,4) |
QUANTITY_SHIPPING |
The quantity that is currently in shipping. |
No |
NUMBER(20,4) |
QUANTITY_SHIPPED |
The quantity that has currently shipped. |
No |
NUMBER(20,4) |
QUANTITY_RECEIVED |
The quantity that has been received into stock. |
No |
NUMBER(20,4) |
QUANTITY_DAMAGED |
The quantity that has been received as damaged. |
No |
NUMBER(20,4) |
PREFERRED_UOM |
The preferred unit of measure of the transfer line item. |
No |
VARCHAR2(4) |
Data Definition
Source Type: (1) Store, (3) Warehouse, (4) Finisher
Destination Type: (1) Store, (3) Warehouse, (4) Finisher
Transfer Status: (1) New Request, (2) Requested, (3) Request In Progress, (4) Rejected, (5) Canceled Request, (6) Transfer In Progress, (7) Approved, (8) In Shipping, (9) Completed, (10) Canceled
Transfer Origin Type: (0 External, (1) Internal, (2) Adhoc
Example CSV File
IDL-TRANSFER.csv
H,TSFID1,EXTID1,DN1,1,5000,1,5001,7,1,1,364155194,MOBCO5,MOBFO5,Y,N,AUTHCODE1,2022-10-30 00:00:01,2022-10-22 09:28:01,1500,2022-10-22 09:28:01,1500,2022-10-22 09:28:02,1500,2022-10-22 09:28:03,1500
D,TSFID1,100701234,1,1,1,1,1,1,0,EA
Transfer Shipment File
-
Transfer shipment files must contain information for a single store only.
-
Duplicate cartons are not allowed on the shipments
-
Cartons not in new status are required to have line items.
-
Transfer shipment status will be calculated from the status of the various cartons on the shipment. Transfer shipments should not be imported if they are in submitted status. The complexities of communication and synchronization with third party systems responsible for manifesting or other fiscal documentation makes this not feasible. Transfer shipments that are currently in progress prior to dispatch should be submitted after the import.
Table 2-54 Transfer Shipment File Row Layout (H – Header)
Field Name | Description | Required | Type |
---|---|---|---|
ROW_TYPE |
The type of row that is represented. |
Yes |
“H” |
IMPORT_SHIP_ID |
An import identifier used to associate the shipment with its cartons and items. |
Yes |
VARCHAR2(128) |
STORE_ID |
The unique store identifier that is the source of the shipment. |
Yes |
NUMER(10) |
DESTINATION_TYPE |
Destination location type. |
Yes |
NUMBER(2) |
DESTINATION_ID |
The unique identifier of the destination. |
Yes |
NUMBER(10) |
ASN |
The advance shipment notification number. |
Yes |
VARCHAR2(128) |
NOT_AFTER_DATE |
A date that the goods should not be shipped after. |
No |
DATE |
AUTHORIZATION_CODE |
An authorization code |
No |
VARCHAR2(128) |
IMPORT_TSF_ID |
The original import identifier of a transfer loaded from the transfer file. |
No |
NUMBER(15) |
TRACKING_NUMBER |
Holds the tracking number for the transaction. |
No |
VARCHAR2(128) |
SHIP_CARRIER_ID |
Identifier representing the carrier for the shipment. |
No |
NUMBER(10) |
SHIP_CARRIER_SERVICE_ID |
Identifier representing the carrier service for the shipment. |
No |
NUMBER(10) |
SHIPMENT_CARTON_DIM_ID |
The shipment carton dimension Id. |
No |
NUMBER(12) |
SHIP_WEIGHT |
The weight of the carton. |
No |
NUMBER(12,4) |
SHIP_WEIGHT_UOM |
The weight UOM of the Carton. |
No |
VARCHAR2(4) |
REQUESTED_PICKUP_DATE |
The field contains the requested pickup date. |
No |
DATE |
SHIP_TO_ADDRESS_TYPE |
The address type for the ship to address. |
No |
VARCHAR2(2) |
ALT_DESTINATION_ADDRESS |
This field contains the alternate destination address. |
No |
VARCHAR2(2000) |
CARRIER_ROLE |
The carrier type for a Bill of Lading. |
Yes |
NUMBER(2) |
THIRD_PARTY_NAME |
This field contains the name of the third party. |
No |
VARCHAR2(240) |
THIRD_PARTY_ADDRESS |
This field contains the address of the third party. |
No |
VARCHAR2(240) |
MOTIVE |
This field contains the motive. |
No |
VARCHAR2(120) |
TAX_ID |
This field contains the tax id of the supplier. |
No |
VARCHAR2(18) |
FISCAL_DOCUMENT_ID |
Fiscal Document Number. |
No |
VARCHAR2(255) |
FISCAL_DOCUMENT_URL |
Fiscal Document printing URL provided by external system. |
No |
VARCHAR2(255) |
SUBMIT_USER |
The user that submitted the shipment record. |
No |
VARCHAR2(128) |
SUBMIT_DATE |
The date the shipment was submitted within EICS. |
No |
DATE |
DISPATCH_USER |
The user that dispatched the shipment. |
No |
VARCHAR2(128) |
DISPATCH_DATE |
The date the shipment was dispatched within EICS. |
No |
DATE |
CREATE_USER |
The user that created the shipment record. |
No |
VARCHAR2(128) |
CREATE_DATE |
The date the shipment record was created. |
Yes |
DATE |
UPDATE_USER |
The user that last updated the shipment. |
No |
VARCHAR2(128) |
UPDATE_DATE |
The last date the shipment was updated. |
No |
DATE |
Table 2-55 Transfer Shipment File Row Layout (C – Carton)
Field Name | Description | Required | Type |
---|---|---|---|
ROW_TYPE |
The type of row that is represented. |
Yes |
“C” |
IMPORT_SHIP_ID |
An import shipment identifier to tie the shipment, carton, and items together. |
Yes |
VARCHAR2(128) |
IMPORT_CARTON_ID |
A unique import carton identifier to tie the carton to its items. |
Yes |
VARCHAR2(128) |
EXTERNAL_ID |
The external identifier. |
Yes |
VARCHAR2(128) |
STATUS |
The status of the shipment carton. |
Yes |
NUMBER(4) |
CARTON_DIMENSION_ID |
The shipment container dimension id. |
No |
NUMBER(10) |
WEIGHT |
The weight of the container. |
No |
NUMBER(12,4) |
WEIGHT_UOM |
The UOM of the container. |
No |
VARCHAR2(4) |
TRACKING_NUMBER |
The tracking number for the container. |
No |
VARCHAR2(128) |
USE_AVAILABLE |
Value of Y indicates carton will use only available inventory, N means carton will use unavailable inventory. |
Yes |
VARCHAR2(1) |
RESTRICTION_LEVEL |
The hierarchy restriction level for items in a container. |
Yes |
NUMBER(4) |
APPROVAL_USER |
The user who approved the shipment. |
No |
VARCHAR2(128) |
APPROVAL_DATE |
The date when the shipment was approved. |
No |
DATE |
CREATE_USER |
The user who created the shipment carton. |
No |
VARCHAR2(128) |
CREATE_DATE |
The create date of the shipment carton. |
Yes |
DATE |
UPDATE_USER |
The user who last updated the shipment carton. |
No |
VARCHAR2(128) |
UPDATE_DATE |
The date when the shipment carton was updated. |
No |
DATE |
Table 2-56 Transfer Shipment File Row Layout (D – Detail)
Field Name | Description | Required | Type |
---|---|---|---|
ROW_TYPE |
The type of row that is represented. |
Yes |
“D” |
IMPORT_SHIP_ID |
An import shipment identifier to tie the shipment, carton, and items together. |
Yes |
VARCHAR2(128) |
IMPORT_CARTON_ID |
A unique import carton identifier to tie the carton to its items. |
Yes |
VARCHAR2(128) |
ITEM_ID |
The item identifier. |
Yes |
VARCHAR2(25) |
IMPORT_TSF_ID |
The import identifier assigned to the transfer in the import file that imported this item in the transfer upload. |
Yes |
VARCHAR2(128) |
SHIPMENT_REASON_ID |
The shipment reason identifier. |
No |
NUMBER(15) |
CASE_SIZE |
Pack size of the item. |
Yes |
NUMBER(10,2) |
QUANTITY |
Quantity to be shipped. |
Yes |
NUMBER(20,4) |
Data Definition
Shipment Destination Type: (1) Store, (3) Warehouse, (4) Finisher
Shipment Ship To Address Type: (01) Business, (02) Postal, (03) Returns, (04) Order, (05) Invoice, (06) Remittance, (07) Billing, (08) Delivery, (09) External
Shipment Carrier Role: (1) Sender, (2) Receiver, (3) Third Party
Carton Status: (1) New, (2) In Progress, (3) Completed, (4) Shipped, (5) Canceled
Carton Restriction Level: (1) Department, (2) Class, (3) Subclass, (4) None
Example CSV File
IDL-TRANSFERSHIP-5000.csv
H,100000,5000,4,8000,4,2022-10-24 16:12:32,AUTCODE1,TSFID1,4,1,1,1,100,KG,2022-10-25 00:12:32,1,ALTDESTADDRESS,1,3RDPARTYNAME,3RDPARTYADDRESS,MOTIVE,TAXID1,FDOC1,FDOCURL1,1500,2022-10-24 16:12:32,1500,2022-10-24 16:12:32,1500,2022-10-24 16:12:32,15000,2022-10-24 16:12:32
C,100000,5,2,1,1,100,KG,1234,Y,4,1500,2022-10-23 11:32:12,15000,2022-10-24 16:12:32,15000,2022-10-24 16:12:32
D,100000,5,100701234,1,1,100,1
Transfer Delivery File
-
Transfer delivery files must contain information for a single store only.
-
Each delivery must contain at least one container.
-
Each container must container at least one item.
-
Duplicate cartons are not allowed on the delivery.
-
If the container is open, the in-transit quantity will be incremented for the items at the destination store for the remaining expected quantity.
-
The status of the delivery will be calculated from the status of the containers.
Table 2-57 Transfer Delivery File Row Layout (H – Header)
Field Name | Description | Required | Type |
---|---|---|---|
ROW_TYPE |
The type of row that is represented. |
Yes |
“H” |
IMPORT_DELV_ID |
An import identifier to tie the delivery to its cartons and items. |
Yes |
VARCHAR2(128) |
STORE_ID |
The receiving store identifier. |
Yes |
NUMBER(10) |
SOURCE_TYPE |
The source type. |
Yes |
NUMBER(4) |
SOURCE_ID |
The source location identifier. |
Yes |
NUMBER(10) |
ASN_ID |
The Advance Shipment Notification number. |
Yes |
VARCHAR2(30) |
RECEIPT_NO |
The receipt number. |
Yes |
VARCHAR2(30) |
CARRIER_ENTITY |
The carrier entity. |
No |
VARCHAR2(128) |
CARRIER_TYPE |
The carrier type. |
No |
NUMBER(2) |
CARRIER_CODE |
Unique code identifier for a carrier. |
No |
VARCHAR2(4) |
SOURCE_ADDRESS |
The address of source sending delivery. |
No |
VARCHAR2(1000) |
LICENSE_PLATE |
A license plate number. |
No |
VARCHAR2(128) |
FREIGHT_ID |
The freight identifier. |
No |
VARCHAR2(128) |
BOL_EXTERNAL_ID |
Delivery Bill Of Lading from external system or entered by SIOCS user. |
No |
VARCHAR2(128) |
FISCAL_DOCUMENT_ID |
Fiscal Document Number. |
No |
VARCHAR2(128) |
EXPECTED_DATE |
The expected date of the Transfer Delivery. |
No |
DATE |
RECEIVED_DATE |
The received date of the Transfer Delivery. |
No |
DATE |
RECEIVED_USER |
The user who received the Transfer Delivery. |
No |
VARCHAR2(128) |
CREATE_DATE |
The create date of the Transfer Delivery. |
Yes |
DATE |
CREATE_USER |
The user who created the Transfer Delivery. |
No |
VARCHAR2(128) |
UPDATE_DATE |
The date when the Transfer Delivery was updated. |
No |
DATE |
UPDATE_USER |
The user who last updated the Transfer Delivery. |
No |
VARCHAR2(128) |
Table 2-58 Transfer Delivery File Row Layout (C – Carton)
Field Name | Description | Required | Type |
---|---|---|---|
ROW_TYPE |
The type of row that is represented. |
Yes |
“C” |
IMPORT_DELV_ID |
The identifier of the legacy information when the record was imported. |
Yes |
VARCHAR2(128) |
IMPORT_CARTON_ID |
The unique identifier for the transfer delivery carton/container. |
Yes |
VARCHAR2(128) |
EXTERNAL_ID |
An external carton identifier, often used to communicate with external systems. |
No |
VARCHAR2(128) |
REFERENCE_ID |
A reference identifier. |
No |
VARCHAR2(128) |
STATUS |
The status of the transfer delivery carton. |
Yes |
NUMBER(4) |
SERIAL_CODE |
A serial code. |
No |
NUMBER(18) |
TRACKING_NUMBER |
A tracking number for the container. |
No |
VARCHAR2(128) |
DAMAGED_REASON |
The reason for container damage. |
No |
VARCHAR2(128) |
DAMAGE_REMAINING |
Y indicates all remaining quantities should be damaged on final receipt. Y/N value. |
Yes |
VARCHAR2(1) |
RECEIVE_AT_SHOP_FLOOR |
Indicates if the stock would be received at shop-floor or not.''Y'' if stock is to be received at shop-floor ''N'' otherwise. |
Yes |
VARCHAR2(1) |
QUALITY_CONTROL |
A quality control indicator. Y indicates that the carton must be manually received. Y/N value. |
Yes |
VARCHAR2(1) |
EXTERNAL_CREATE |
Indicates it was external created. Y indicates it was. Y/N value. |
Yes |
VARCHAR2(1) |
ADJUSTED |
Y indicates the container has been adjusted after receipt. Y/N value. |
Yes |
VARCHAR2(1) |
COPIED |
Y means has been copied as a misdirected container, N means it has not. |
Yes |
VARCHAR2(1) |
RECEIVE_DATE |
The date when the carton was received. |
No |
DATE |
RECEIVE_USER |
The user who received the carton. |
No |
VARCHAR2(128) |
CREATE_DATE |
The date when the carton was created. |
Yes |
DATE |
CREATE_USER |
The user who created the carton. |
No |
VARCHAR2(128) |
UPDATE_DATE |
The date when the carton was updated. |
No |
DATE |
UPDATE_USER |
The user who last updated the carton. |
No |
VARCHAR2(128) |
Table 2-59 Transfer Delivery File Row Layout (D – Detail)
Field Name | Description | Required | Type |
---|---|---|---|
ROW_TYPE |
The type of row that is represented. |
Yes |
"D" |
IMPORT_DELV_ID |
An import identifier to tie the delivery to its cartons and items. |
Yes |
VARCHAR(128) |
IMPORT_CARTON_ID |
Import identifier to tie the cartons to its items. |
Yes |
VARCHAR(128) |
ITEM_ID |
The item identifier. |
Yes |
VARCHAR(25) |
DOCUMENT_TYPE |
Transfer delivery document type. |
Yes |
NUMBER(2) |
DOCUMENT_DATE |
The date when document was created. |
Yes |
DATE |
IMPORT_ALLOC_ID |
The original legacy import identifier from the allocation file when it was uploaded. |
No |
VARCHAR(128) |
IMPORT_TSF_ID |
The original legacy import identifier from the transfer file when it was uploaded. |
No |
VARCHAR(128) |
CUST_ORD_EXTERNAL_ID |
Customer order external identifier. |
No |
VARCHAR(128) |
FUL_ORD_EXTERNAL_ID |
Fulfillment order external identifier. |
No |
VARCHAR(128) |
USE_AVAILABLE |
Value of Y indicates item will be received as available inventory, N means received as unavailable inventory. |
Yes |
VARCHAR(1) |
CASE_SIZE |
Pack size of the item. |
Yes |
NUMBER(10,2) |
QUANTITY_EXPECTED |
The total number of units expected in this transfer delivery. |
No |
NUMBER(20,4) |
QUANTITY_RECEIVED |
The total number of units received in this transfer delivery. |
No |
NUMBER(20,4) |
QUANTITY_DAMAGED |
The total number of units that were damaged when the transfer delivery was received. |
No |
NUMBER(20,4) |
PREVIOUS_RECEIVED |
Units previous received when container is reopened for adjustment. |
No |
NUMBER(20,4) |
PREVIOUS_DAMAGED |
Units previous received as damaged when container is reopened for adjustment. |
No |
NUMBER(20,4) |
Data Definition
Delivery Source Type: (1) Store, (3) Warehouse, (4) Finisher
Delivery Carrier Type: (0) Corporate, (1) Third Party
Carton Status: (1) New, (2) In Progress, (3) Damaged, (4) Received, (5) Missing
Detail Document Type: (0) Transfer, (1) Allocation
Example CSV File
IDL-TRANSFERDELV-5000.csv
H,TSF-DELV-x6,5000,1,5001,ASN-1,Receipt-1,CARRENTITY1,0,CCD1,SRCADDR,LP1,FR1,BOLEXTID1,FDOC1,2022-12-08 09:00:00,2022-12-08 09:00:00,1500,2022-12-07 09:00:00,1500,2022-12-07 09:00:00,1500
C,TSF-DELV-x6,CARTON-3,EXTID1,REFID1,4,0599123645,TKNUM1,NO DAMAGE,N,Y,Y,Y,N,N,2022-12-07 09:00:00,1500,2022-12-07 09:00:00,1500,2022-12-07 09:00:00,1500
D,TSF-DELV-x6,CARTON-3,100637121,0,2022-12-07 09:00:00,IMPALLOCID1,778,CUSTORDID1,FCUSTORDID1,Y,1,1,1,0,0,0
UIN File
-
This file must contain only information for a single store.
-
Only the following statues will be allowed for the UIN: In Stock, Sold, Shipped To Warehouse, Shipped To vendor, Shipped to Finisher, Remove From Inventory, Missing, and Customer Fulfilled.
-
The current functional area will be defaulted to MANUAL.
-
The current functional identifier is not allowed on the import.
-
Note that a UIN history record will be created for each imported UIN.
Table 2-60 UIN File Row Layout (H – Header)
Field Name | Description | Required | Type |
---|---|---|---|
ITEM_ID |
The identifier of the item. |
Yes |
VARCHAR2(25) |
UIN |
The universal identification number. |
Yes |
VARCHAR2(128) |
STORE_ID |
The store identifier. |
Yes |
NUMBER(10) |
STATUS |
The current status of the UIN. |
Yes |
NUMBER(2) |
PREVIOUS_STATUS |
The previous status of the UIN. |
No |
NUMBER(2) |
PREVIOUS_FUNCTIONAL_AREA |
The previous business area that contained the UIN for that previous status. |
No |
NUMBER(2) |
PREVIOUS_FUNCTIONAL_ID |
The transaction id of the transaction that previously contained the UIN for that previous status. |
No |
VARCHAR2(128) |
PREVIOUS_STORE_ID |
The previous store identifier associated with the previous status. |
No |
NUMBER(10) |
PREVIOUS_NONSELLABLE_TYPE_ID |
A non-sellable inventory bucket the UIN was last within for that previous status. |
No |
NUMBER(12) |
PREVIOUS_CARTON_ID |
The identifier of the carton that previously contained the UIN for that previous status. |
No |
VARCHAR2(128) |
CREATE_DATE |
The date the UIN was first inserted into the system. |
Yes |
DATE |
UPDATE_DATE |
The last date the UIN was updated. |
No |
DATE |
CREATE_USER |
The user that first inserted the UIN into the system. |
No |
VARCHAR2(128) |
UPDATE_USER |
The user that last updated the UIN in the system. |
No |
VARCHAR2(128) |
Data Definition
Status: (0) In Stock, (1) Sold, (2) Shipped To Warehouse, (5) Shipped To Vendor, ( (6) Remove From Inventory, (8) Missing, (11) Customer Fulfilled, (12) Shipped to Finisher,
Functional Area: (0) Warehouse Delivery Receipt, (1) Direct Delivery Receipt, (2) Create Transfer, (3) Dispatch Transfer, (4) Receive Transfer, (5) Receipt Adjustment, (6) Crete Return, (7) Dispatch Return, (8) Inventory Adjustment, (9), Stock Count, (10) Stock Recount, (11) Stock Count Authorization, (12) Manual, (13) POS Sale, (14) POS return, (15) POS Sales Void, (16) POS Return Void, (17) UIN Web Service, (18) Customer Order, (20), Direct Delivery ASN, (21) Transfer ASN, (22) Transfer Shipment
Example CSV File
IDL-ITEMUIN-5000.csv
100665085,testuinCsv0,5000,0,0,1,prev function area id,5000,5001,0,2022-10-24 14:23:00,2022-10-24 14:23:01,15000,15000
Supported Locales
Table 2-61 Locale ID Values
LOCALE_ID | LOCALE_LANGUAGE | LOCALE_DESCRIPTION |
---|---|---|
1 |
en |
English |
2 |
de |
German |
3 |
fr |
French |
4 |
es |
Spanish |
5 |
ja |
Japanese |
6 |
ko |
Korean |
7 |
ru |
Russian |
8 |
zh |
Chinese |
9 |
tr |
Turkish |
10 |
hu |
Hungarian |
11 |
zh |
Traditional Chinese |
12 |
pt |
Brazilian Portuguese |
13 |
ar |
Arabic |
15 |
hr |
Croatian |
18 |
nl |
Dutch |
20 |
el |
Greek |
22 |
it |
Italian |
26 |
pl |
Polish |
31 |
sv |
Swedish |
32 |
sq |
Albanian |
33 |
hy |
Armenian |
34 |
az |
Azerbaijani |
35 |
be |
Belarusian |
36 |
bn |
Bengali |
37 |
bs |
Bosnian |
38 |
bg |
Bulgarian |
39 |
my |
Burmese |
40 |
cs |
Czech |
41 |
da |
Danish |
42 |
et |
Estonian |
43 |
fil |
Filipino |
44 |
fi |
Finnish |
45 |
ka |
Georgian |
46 |
he |
Hebrew |
47 |
hi |
Hindi |
48 |
id |
Indonesian |
49 |
kk |
Indonesian |
50 |
km |
Khmer |
51 |
lo |
Lao |
52 |
lv |
Latvian |
53 |
lt |
Lithuanian |
54 |
ms |
Malay |
55 |
no |
Norwegian |
56 |
ro |
Romanian |
57 |
sr |
Serbian |
58 |
sk |
Slovak |
59 |
sl |
Slovene |
60 |
th |
Thai |
61 |
uk |
Ukrainian |
62 |
ur |
Urdu |
63 |
uz |
Uzbek |
64 |
vi |
Vietnamese |