B Appendix: Batch File Layout Specifications
This chapter describes the batch file layout specifications for the following batches:
Clearance Import File Specification
Filename Format
Clearance_Tx_{YYYYMMddHHMMss}.csv
File prefix: Clearance_Tx
Note:
If the file comes in as a zip file, the file prefix must match the specified file prefix, and the file inside the zip must have file extension .csv.
File Layout
Comma Delimited File.
Table B-1 Clearance Import File Layout
Name | Type | Required(x indicating required) | Description |
---|---|---|---|
REC_ID |
NUMBER(10) |
x |
The external record id (payload id. |
RECORD_TYPE |
VARCHAR2(50) |
x |
Record type, valid values: Create/Update/Delete. |
CLEARANCE_ID |
NUMBER(15) |
x |
Clearance id. |
ITEM |
VARCHAR2(25) |
Item id. |
|
LOCATION |
NUMBER(10) |
Location id. |
|
LOCATION_TYPE |
VARCHAR2(30) |
The location type. S (STORE), W(WAREHOUSE) (Notes: only location type of 'S' is relevant to SIOCS) |
|
EFFECTIVE_DATE |
TIMESTAMP |
Effective date. |
|
RETAIL |
NUMBER(20,4) |
The clearance price. |
|
UOM |
VARCHAR2(25) |
Unit Of Measure. |
|
CURRENCY |
VARCHAR2(25) |
Price currency. |
|
RE-SET_INDICATOR |
NUMBER(1) |
x |
Indicates if the clearance event is a reset. valid values: 0 - the record is not a reset; 1 - the record is a reset. |
Sample File
1,Create,1041231,100637113,5000,S,2022-06-30 12:06:00.0000000000,12.72,EA,USD,0
2,Create,1041231,100637121,5001,S,2022-06-30 12:06:00.0000000000,12.72,EA,USD,0
Inventory Extract
File Layout
The input file is in Pipe ('|') delimited format.
Table B-2 Inventory Extract File
Record Name | Field Name | Field Type | Description |
---|---|---|---|
File Header |
file type record descriptor |
Char(5) |
hardcode FHEAD |
File Header |
file line identifier |
Number(10) |
ID of current line being processed, hardcode 1 |
File Header |
file type |
Char(4) |
hardcode PLINV |
File Header |
file create date |
Date(14)YYYYMMDDHHMISS |
date written by job program |
File Header |
loc_type |
Char(1) |
hardcode S |
File Header |
location |
Number(10) |
Location id |
Transaction record |
file type record descriptor |
Char(5) |
hardcode FDETL |
Transaction record |
file line identifier |
Number(10) |
ID of current line being processed, internally incremented |
Transaction record |
item type |
Char(3) |
hardcode ITM |
Transaction record |
item value |
Char(25) |
item ID |
Transaction record |
Stock on hand |
Number(12,4) |
total units or total weight |
Transaction record |
Available stock on hand |
Number(12,4) |
Available units or weight |
Transaction record |
SUOM |
Number(12,4) |
Stock unit of measure |
Transaction record |
Last Update Date |
Date(14) YYYYMMDDHHMISS |
|
File trailer |
file type record descriptor |
Char(5) |
hardcode FTAIL |
File trailer |
Number of data records |
Number(12) |
Sample File
FHEAD|000000001|20220607090000|S|5030
FDETL|000000002|ITM|35|40|10|20220607084100
FDETL|000000003|ITM|124|34|15|20220605103215
FTAIL|000000004|3
POS Transaction Import File Specification
Filename Format
<file prefix>_<date in YYYYMMDDHH24MISS format>_<loc id>.dat
Where file prefix value is "SIMTLOG" and loc id is the store identifier. This allows file to be unique for every upload.
Example:
SIMTLOG_20180129133250_1111.dat
Zip File Format
<file prefix>_<date in YYYYMMDDHH24MISS format>.zip
Where file prefix value is "SIMTLOG". The zip file can contain one or more files from same or different stores.
Example:
SIMTLOG_20180129133250.zip
File Layout
The input file is in Pipe ('|') delimited format.
Table B-3 T-LOG File
Record Name | Field Name | Field Type | Default Value | Description |
---|---|---|---|---|
FILE HEADER |
FILE HEADER File Type Record Descriptor |
VARCHAR2(5) |
FHEAD |
Identifies the File Record Type |
FILE HEADER |
Location Number |
NUMBER(10) |
Store Number |
|
FILE HEADER |
Business Date |
VARCHAR2(14) |
Business Date of transactions in YYYYMMDDHHSS format |
|
FILE HEADER |
File Creation Date |
VARCHAR2(14) |
SYSDATE |
File Create Date in YYYMMDDHHMSS format |
TRANSACTION HEADER |
File Type Record Descriptor |
VARCHAR2 (5) |
THEAD |
Identifies the File Record Type |
TRANSACTION HEADER |
Transaction Number |
VARCHAR2(128) |
The unique transaction reference number generated by ORXPOS/OMS. |
|
TRANSACTION HEADER |
Transaction Date and Time |
VARCHAR2(14) |
Date transactions were processed in ORXPOS/OMS |
|
TRANSACTION HEADER |
Customer Order ID |
VARCHAR2(128) |
External customer order ID, if transaction is a customer order |
|
TRANSACTION HEADER |
Customer Order Comments |
VARCHAR(512) |
Comments on the customer order |
|
TRANSACTION DETAIL |
File Type Record Descriptor |
VARCHAR2(5) |
TDETL |
Identifies the File Record Type |
TRANSACTION DETAIL |
Item ID |
VARCHAR2(25) |
ID number of the item. |
|
TRANSACTION DETAIL |
UIN |
VARCHAR2(128) |
This is the UNIQUE_ID value from RTLOG |
|
TRANSACTION DETAIL |
Item Quantity |
NUMBER(12,4) |
Quantity of the item on this transaction |
|
TRANSACTION DETAIL |
Selling UOM |
VARCHAR2(4) |
UOM at which this item was sold |
|
TRANSACTION DETAIL |
Reason Code |
NUMBER(4) |
Reason entered by cashier for some transaction types. Required for voids, returns, for example. |
|
TRANSACTION DETAIL |
Comments |
VARCHAR(512) |
Comments for this line item |
|
TRANSACTION DETAIL |
Transaction Code |
VARCHAR2(25) |
The type of sale represented by this line item. Valid value are SALE,RETURN,VOID_SALE,VOID_RETURN,ORDER_NEW,ORDER_FULFILL,ORDER_CANCEL,ORDER_CANCEL_FULFILL |
|
TRANSACTION DETAIL |
Reservation Type |
VARCHAR(25) |
Reservation type if POS transaction is a customer order. Valid values are SPECIAL_ORDER, WEB_ORDER, PICKUP_AND DELIVERY,LAYAWAY |
|
TRANSACTION DETAIL |
Fulfillment Order Number |
VARCHAR2(48) |
Fulfillment Order Number from OMS |
|
TRANSACTION DETAIL |
Drop Ship Indicator |
VARCHAR(1) |
'P' if it is drop ship otherwise 'N' |
|
TRANSACTION TAIL |
File Record Type Descriptor |
VARCHAR2(5) |
TTAIL |
Identifies the File Record Type |
TRANSACTION TAIL |
Transaction Record Counter |
NUMBER(6) |
Number of TDETL records in this transaction set. |
|
FILE TAIL |
File Record Type Descriptor |
VARCHAR2(5) |
FTAIL |
Identifies the File Record Type |
FILE TAIL |
File Record Counter |
NUMBER(10) |
Number of records/transactions processed in current file (only records between head and tail) |
Price Change Import File Specification
Filename Format
PriceChange _Tx_<YYYYMMddHHMMss>.csvFile prefix: PriceChange _Tx
Note:
If the file comes in as a zip file, the file prefix must match the specified file prefix, and the file inside the zip must have file extension .csv.
File Layout
Comma Delimited File.
Table B-4 Price Change Import File Layout
Name | type | Required(x indicating required) | Description |
---|---|---|---|
REC_ID |
NUMBER(10) |
x |
The external record id (payload id. |
RECORD_TYPE |
VARCHAR2(50) |
x |
Record type, valid values: Create/Update/Delete. |
PRICE_CHANGE_ID |
NUMBER(15) |
x |
The price change ID. |
ITEM |
VARCHAR2(25) |
Item id. |
|
LOCATION |
NUMBER(10) |
Location id. |
|
LOCATION_TYPE |
VARCHAR2(30) |
The location type. S (STORE), W(WAREHOUSE) (Notes: SIOCS only takes the location type of 'S', Warehouse type will be skipped) |
|
EFFECTIVE_DATE |
TIMESTAMP yyyy-mm-dd hh:mm:ss.fffffffff for example 2021-04-09 11:00:00.000000000 |
Effective date of price change. |
|
RETAIL |
NUMBER(20,4) |
The retail with for the item and location based on the price change. |
|
UOM |
VARCHAR2(25) |
The retail Unit Of Measure. |
|
CURRENCY |
VARCHAR2(25) |
The currency for the location. |
|
RETAIL_CHANGE_IND |
NUMBER(6) |
Indicates whether the retail changed with this price change. |
|
MULTI_UNIT_IMPACT |
VARCHAR2(4) |
x |
Indicates if the Price Change has impact to Multi Unit retail. Valid value are AU - Multi Unit information is added or updated; R - Multi Unit in-formation is removed; N - Multi unit information is not changed. |
MULTI_UNITS |
NUMBER(12,4) |
Number of multi units. |
|
MULTI_UNIT_RETAIL |
NUMBER(20,4) |
The Multi Unit Retail value. |
|
MULTI_UNIT_SELLING_UOM |
VARCHAR2(4) |
The Multi Unit Retail Selling UOM. |
|
MULTI_UNIT_RETAIL_CURRENCY |
VARCHAR2(3) |
The Multi Unit Retail Currency. |
Sample File
30003, Create,650664,100637121,5000,S,2022-07-01 12:06:00.0000000000,14.72,EA,USD,1,N,,,,USD
30004,Create,650699,100637113,5000,S,2022-07-02 12:06:00.0000000000,28.72,EA,USD,1,N,,,,USD
Retail Sale Audit Import File Specification
Filename Format
SIMT_< YYYYMMDDHH24MISS>.zip
The zip file can contain one or more files:
SIMT_<YYYYMMDDHH24MISS>_<loc id>.dat
Where loc id is the store identifier.
Example:
SIMT_20180129133250_1111.dat
File Layout
Table B-5 ReSA File Layout
Record Name | Field Name | Field Type | Default Value | Description |
---|---|---|---|---|
FHEAD |
FILE Type Record Descriptor |
VARCHAR2(5) |
FHEAD |
Identifies the File Record Type |
FHEAD |
File Line ID |
VARCHAR(10) |
Sequential file line number |
|
FHEAD |
File Type Definition |
VARCHAR2(4) |
SIMT |
Identifies the File Type |
FHEAD |
Location Number |
NUMBER(10) |
Store Number |
|
FHEAD |
Business Date |
VARCHAR2(14) |
N/A |
Business Date of transactions in YYYYMMDDHHSS format |
FHEAD |
File Creation Date |
VARCHAR2(14) |
N/A |
File Create Date in YYYMMDDHHMSS format |
THEAD |
Record Descriptor |
VARCHAR2 (5) |
TDETL |
Identifies the File Record Type |
THEAD |
File Line ID |
VARCHAR(10) |
Sequential file line number |
|
THEAD |
Transaction Number |
NUMBER(10) |
The unique transaction reference number generated by ORXPOS/OMS |
|
THEAD |
Revision Number |
NUMBER(3) |
The version of the transaction being sent |
|
THEAD |
Transaction Date and Time |
VARCHAR2(14) |
Transaction date in YYYYMMDDHHMMSS format. Corresponds to the date that the transaction occurred. |
|
THEAD |
Transaction Type |
VARCHAR2(14) |
Transaction Type Code (for example, SALE, RETURN, SPLORD) |
|
THEAD |
Pos created flag |
VARCHAR2(1) |
'Y' identifies that the transaction occurred at ORXPOS, 'N' identifies that the transaction was created in ReSA |
|
TDETL |
Record Descriptor |
VARCHAR2(5) |
TDETL |
Identifies the File Record Type |
TDETL |
File Line ID |
VARCHAR(10) |
0000000001 |
Sequential file line number. |
TDETL |
Item Sequence Number |
NUMBER(4) |
The order in which items were entered during a transaction |
|
TDETL |
Item |
VARCHAR2(25) |
ID number of the item. |
|
TDETL |
Item Number Type |
VARCHAR2(6) |
Type of Item sold. Can be 'ITEM', 'REF', 'GCN', 'NMITEM' |
|
TDETL |
Item Status |
VARCHAR2(6) |
Status of the item within the transaction. V - for item void S - for sold item R - for returned item ORI - Order Initiate ORC - Order Cancel ORD - Order Complete LIN - Layaway Initiate LCA - Layaway Cancel LCO - Layaway Complete PVLCO - Post Void Layaway Complete PVORD - Post Void Order Complete |
|
TDETL |
Serial Number |
VARCHAR2(128) |
This is the UNIQUE_ID value from RTLOG |
|
TDETL |
Pack Indicator |
VARCHAR2(1) |
Pack indicator of item sold or returned |
|
TDETL |
Catch Weight Indicator |
VARCHAR2(1) |
Indicates if item is a catchweight item |
|
TDETL |
Item Quantity Sign |
VARCHAR2(1) |
Determines if the Total Sale Quantity is positive or negative 'P' - Positive 'N' - Negative |
|
TDETL |
Item Quantity Value |
NUMBER(20) |
Total sales value of goods sold/returned (4 implied decimal places), for example, Total Quantity * 10000 |
|
TDETL |
Standard UOM |
VARCHAR2(4) |
Standard UOM of the Item |
|
TDETL |
Selling UOM |
VARCHAR2(4) |
UOM at which this item was sold |
|
TDETL |
Wastage Type |
VARCHAR2(6) |
Wastage type of item sold or returned |
|
TDETL |
Wastage Percentage |
NUMBER(12) |
Wastage Percent*10000 (4 implied decimal places), wastage percent of item sold or returned |
|
TDETL |
Drop Ship Indicator |
VARCHAR2(1) |
N |
This will always be N for Export |
TDETL |
Actual Weight Quantity |
NUMBER(12) |
Actual Weight Quantity*10000 (4 implied decimal places), the actual weight of the item, only populated if catchweight_ind = 'Y' |
|
TDETL |
Actual Weight Sign |
Char(1) |
Sign of the actual weight |
|
TDETL |
Reason Code |
VARCHAR2(6) |
Reason entered by cashier for some transaction types |
|
TDETL |
Sale Value |
NUMBER(20) |
Total Sales Value * 10000 (4 implied decimal places), sales value, net sales value of goods sold |
|
TDETL |
Sales Sign |
VARCHAR2(1) |
Determines if the Total Sales Value is positive or negative 'P' - Positive 'N' - Negative |
|
TDETL |
Unit Retail |
NUMBER(20,4) |
Unit retail with 4 implied decimal places |
|
TDETL |
Sales Type |
VARCHAR2(1) |
Indicates if the line item is a Regular Sale, a CO serviced by OMS (External CO), or a CO serviced by Inventory management application (In-Store CO) |
|
TDETL |
Customer Order Number |
VARCHAR2(50) |
Customer Order Number |
|
TDETL |
Customer Order Type |
Char(6) |
Customer order type |
|
TDETL |
Fulfillment Order Number |
VARCHAR2(50) |
Fulfillment Order Number from OMS |
|
TDETL |
Customer Order Line Number |
NUMBER (10) |
Customer order line number |
|
TTAIL |
Record Type Descriptor |
VARCHAR2(5) |
TTAIL |
Identifies the File Record Type |
TTAIL |
File Line ID |
NUMBER(10) |
Sequential file line number |
|
TTAIL |
Transaction Record Counter |
NUMBER(6) |
Number of TDETL records in this transaction set |
|
FTAIL |
File Record Type Descriptor |
VARCHAR2(5) |
FTAIL |
Identifies the File Record Type |
FTAIL |
File Line ID |
NUMBER(10) |
Sequential file line number |
|
FTAIL |
File Record Counter |
NUMBER(10) |
Number of records/transactions processed in current file (only records between head and tail) |
Sample Data File
FHEAD|0000000001|SIMT|5141|20210307111049|20210307144046
THEAD|0000000002|1141|1|1|20210307000000|SALE|N
TDETL|0000000003|1|100000147|ITEM|S||||P|3||EA|||N|||||||||||
TTAIL|0000000004|1
THEAD|0000000005|270888|1|1|20210307000000|RETURN|N
TDETL|0000000006|1|100000147|ITEM|R||||N|3||EA|||N|||||||||||
TTAIL|0000000007|1
FTAIL|0000000008|6
Stock Count Results Export File Specification
The stock count result export file is generated when unit amount stock count authorization completes. The stock count authorization process can be a manual authorization or invoked by third party stock count batch for an auto-authorized unit amount stock count. This export file can be uploaded to RMS by RMS file to update their inventory with the actual physical stock count.
Table B-6 Stock Count Export File
Record Name | Field Name | Field Type | Description |
---|---|---|---|
File Header |
file type record descriptor |
Char(5) |
hardcode FHEAD |
File Header |
file line identifier |
Number(10) |
ID of current line being processed, hardcode 000000001 |
File Header |
file type |
Char(4) |
hardcode STKU |
File Header |
file create date |
Date(14)YYYYMMDDHHMISS |
date written by convert program |
File Header |
stocktake_date |
Date(14)YYYYMMDDHHMISS |
take_head.stocktake_date |
File Header |
cycle count |
Number(8) |
stake_head.cycle_count |
File Header |
loc_type |
Char(1) |
hardcode W or S |
File Header |
location |
Number(10) |
stake_location.wh or stake_location.store |
Transaction record |
file type record descriptor |
Char(5) |
hardcode FDETL |
Transaction record |
file line identifier |
Number(10) |
ID of current line being processed, internally incremented |
Transaction record |
item type |
Char(3) |
hardcode ITM |
Transaction record |
item value |
Char(25) |
item ID |
Transaction record |
inventory quantity |
Number(12,4) |
total units or total weight |
Transaction record |
location description |
Char(30) |
Where in the location the item exists. For example, Back Stockroom or Front Window Display |
File trailer |
file type record descriptor |
Char(5) |
hardcode FTAIL |
File trailer |
file line identifier |
Number(10) |
ID of current line being processed, internally incremented |
File trailer |
file record count |
Number(10) |
Number of detail records |
Store Sequence Data Import File Specification
Sequencing functionality provides users the ability to know the relative location of an item in a store. Sequencing a store improves store processes and reduces the time that employees spend looking for items. The retailer can sequence all items in the store and create unique locations to hold the items.
Sequencing defines how many items can be stored in a particular location, and allows the definition of a capacity for that item location combination. The capacity is used for in-store replenishment when generating the shelf replenishment pick list. Sequencing is used within Stock Counts, Customer Order Picking, Transfer Request, and Shelf Replenishment to aid the user in proceeding to the next item during the transaction for efficiency. Lastly, the Sequencing Primary Location is displayed to the user on the Item Detail screen.
Filename Format
<file prefix>_<date in YYYYMMDDHH24MISS format>_<loc id>.dat
Where file prefix value is "SSEQ" and loc id is the store identifier. This allows file to be unique for every upload.
Example:
SSEQ_20180129133250_1111.dat
Zip Filename Format
<file prefix>_<date in YYYYMMDDHH24MISS format>.zip
Where file prefix value is "SSEQ". The zip file can contain one or more files from same or different stores. The complete file needs to be added for zip file for job to pick it for processing.
Example:
SSEQ_20180129133250.zip
File Layout
Table B-7 Store Sequence Import File
Record Name | Field Name | FieldType | Description |
---|---|---|---|
File Header |
file type record descriptor |
Char(5) |
hardcode FHEAD |
File Header |
Store ID |
Number(10) |
Store identifier |
File Header |
Delete |
DELETALL |
Optional flag to delete previous records |
Sequence record |
file type record descriptor |
Char(5) |
hardcode SHEAD |
Sequence record |
Area type |
Number(9) |
The Store Sequence Area. 0 = None, 1 = Shopfloor, 2 = Backroom |
Sequence record |
Child sequenced |
Varchar2(1) |
‘Y’ if child is sequenced, ‘N’ if not |
Sequence record |
Department ID |
Number(12) |
Department ID |
Sequence record |
Class ID |
Number(12) |
Class ID |
Sequence record |
Description |
Varchar2(255) |
Description of Store Sequence |
Sequence record |
Not sequenced |
Varchar2(1) |
Y indicates a default sequence containing all items that have not been sequenced elsewhere |
Sequence record |
Sequence Order |
Number(20) |
The order the store sequence is in compared to other store sequences |
Sequence detail |
file type record descriptor |
Char(5) |
hardcode SDETL |
Sequence detail |
Item ID |
Varchar2(25) |
Item ID |
Sequence detail |
Primary location |
Varchar2(1) |
Indicator if the location specified is the primary location for the item, Y if is primary location for item, N otherwise |
Sequence detail |
Item sequence order |
Number(20) |
Order of item within store sequence |
Sequence detail |
Capacity |
Number(11,2) |
The size of the location appropriate to unit of measure |
Sequence detail |
Ticket quantity |
Number(11,2) |
The quantity of tickets that need to be printed or used for the item inventory location |
Sequence detail |
Ticket format ID |
Number(10) |
Item ticket format identifier |
Sequence trailer |
File type record descriptor |
Char(5) |
hardcode STAIL |
File trailer |
File type record descriptor |
Char(5) |
hardcode FTAIL |
Sample Data File
FHEAD|5000
SHEAD|1|N|||ShopFloor5|N|1
SDETL|100695153|Y|1|100|1||1|0
STAIL
FTAIL
Third Party RFID File Specification
File Layout
Comma Delimited File.
Table B-8 Third Party RFID File Specification
Field Name | Description | Required | Type |
---|---|---|---|
ACTION |
CREATE and DELETE are the only two valid actions for RFI. |
Yes |
VARCHAR2(20) |
EPC |
Electronic product code (SGTIN-96). |
Yes |
VARCHAR(256) |
ITEM_ID |
Identifier of the item/sku. |
Yes |
VARCHAR2(25) |
LOCATION_ID |
Location identifier. |
Yes |
NUMBER(10) |
LOCATION_TYPE |
Location Type, 1 - store, 2 - warehouse. |
Yes |
NUMBER(2) |
ZONE_ID |
The zone within the location that the RFID is located. |
No |
NUMBER(15) |
EVENT_DATE |
The timestamp of the RFID read. |
No |
TIMESTAMP(6) |
Sample File
RFID_{YYYYMMDDHHMMSS}_{LOC}_{LOC_TYPE}.csv
"REPLACE","1111111111111111111111","100637113",5000,1,1001,
"03-07-2021 0:00"
"REPLACE","1111111111111111111112","100637148",5000,2,1022,
"05-10-2021 0:00"
File Contents Explanation
-
It is expected that the RFID provider to ensure the record uniqueness (A unique record is identified by store/item/effective date time), within a file, each record must be unique. The record action is denoted by action type, only one dataset action is allowed. EICS only support CREATE OR DELETE as dataset action for third party rfid, UPDATE type is not supported, use replace for updating a record.
-
Split the Data into multiple files. EICS loads the data in parallel from multiple files. Loading files from multiple files in parallel provides performance advantage than loading from a single file. It is recommended to file provider to split the data into multiple files to load data efficiently in parallel loading, each file contains single store is recommended.
-
Compress the data files. If data file contains large datasets, it is recommended that compress the load files individually, when loading the data file. Use EICS System Configuration Console to specify the file suffix (for example, zip).
Third Party Price File Layout
Filename Format
EXTPC_{YYYYMMDDHHMMSS}_{LOC}_{LOC_TYPE}.csv
Table B-9 Third Party Price Import File Specification
Field Name | Description | Required | Type |
---|---|---|---|
RECORD_ACTION |
CREATE, UPDATE, DELETE . |
Yes |
CHAR(20) |
ITEM_ID |
The unique alphanumeric value for the transaction level item. |
Yes |
CHAR(25) |
STORE_ID |
The number that uniquely identifies the store. |
Yes |
Number(10) |
EFFECTIVE_DATE |
The date on which the price change became effective. The Dates must be GMT as the file will parse and process the dates as GMT dates. yyyy-mm-dd hh:mm:ss.fffffffff for example, 2021-04-09 11:00:00.000000000 |
Yes |
Timestamp |
END_DATE |
Promotion end date. The Dates must be GMT as the file will parse and process the dates as GMT dates. yyyy-mm-dd hh:mm:ss.fffffffff for example, 2021-04-09 11:00:00.000000000 |
No |
Timestamp |
PRICE_TYPE |
The item price type. Valid values: 200- Clearance 201- Promotional 202- Regular 230- Independent clearance reset. |
Yes |
NUMBER(3) |
PROMOTION_NAME |
Promotion name. |
No |
CHAR(160) |
SELLING_UNIT_RETAIL |
Contains the current single unit retail in the selling unit of measure. |
Yes |
NUMBER(20,4) |
SELLING_UNIT_RETAIL_CURRENCY |
Contains the selling unit retail currency. |
Yes |
CHAR(3) |
SELLING_UOM |
Contains the selling unit of measure for an items single-unit retail. |
Yes |
CHAR(4) |
MULTI_UNITS |
Contains the current multi-units. If the record is being written as a result of a change in the multi-unit retail, then this field contains the new multi-units. |
No |
NUMBER(12,4) |
MULTI_UNIT_RETAIL |
Contains the current multi-unit retail in the selling unit of measure. |
No |
NUMBER(20,4) |
MULTI_UNIT_RETAIL_CURRENCY |
Contains the multi-unit retail currency. |
No |
CHAR(3) |
MULTI_UNIT_SELLING_UOM |
Contains the selling unit of measure for an items multi-unit retail. |
No |
CHAR(4) |
CREATE_DATETIME |
Contains the record creation date. yyyy-mm-dd hh:mm:ss.fffffffff for example, 2021-04-09 11:00:00.000000000 |
No |
Timestamp |
REC_ID |
The id of the record. |
Yes |
NUMBER(15) |
RETAIL_CHANGE_IND |
Indicates whether the retail changed with this price change. Valid values are: 0 - retail price not changed 1 - retail price changed |
No |
NUMBER(6) |
MULTI_UNIT_IMPACT |
Indicates if the Price Change has impact to Multi Unit retail. Valid values are: AU - Multi Unit information is added or updated R - Multi Unit information is removed N - Multi unit information is not changed. |
Yes |
CHAR(4) |
PRICE_EVENT_ID |
The id of the price event. |
Yes |
NUMBER(15) |
Sample File
REPLACE,100637113,5000,2021-04-09 11:00:00,,202,,149.99,USD,EA,,,,,2021-04-07 11:00:00,1,1,N,9999
File Contents Explanation
-
It is expected that the pricing provider will ensure the record uniqueness (A unique record is identified by store/item/effective date time), within a file. Each record must be unique. The record action is denoted by action type, only a dataset action is allowed for unique store/item/date.
-
For example, for store 5000, item A, a price on date 2018 Dec 10 00:00:00 record in the file can be one of the following (CREATE, DELETE). The same record with more than one dataset action will be rejected. EICS only supports CREATE OR DELETE as dataset action for third party pricing.
-
The same file cannot have two records with this combination store/item/effective with different price type, if clearance need to be on today, then this file should only have a single record for clearance type.
-
The clearance record can have an end date if the end date is known at time of the clearance creation.
-
For independent clearance reset event (to end all active clearance for a store/item which does not have end date), the pricing provider needs to send clearance reset record (with price type =203), the import process ends any active clearance for item store timeline (set the end date to the clearance reset effective date). The clearance reset record is only for ending the active item store clearance, the price in the clearance reset record is not used for updating.
-
In EICS, there is no client UI which requires or uses the promotion, clearance or price change identifier. For data import integration backend processing, the record is uniquely identified by item/store/effective date time and price type external pricing change identifier has no meaning to our system. Promotion name is used in EICS as context type; therefore it is included in the integration interface.
-
Split the Data into Multiple Files. EICS loads the data in parallel from multiple files. Loading files from multiple files in parallel provides performance advantage overloading from a single file. It is recommended to file provider to split the data into multiple files to load data efficiently in parallel loading. Each file contains single store is recommended.
-
The Dates must be GMT as the file will parse and process the dates as GMT dates.
Third Party Initial Inventory File Layout
Filename Format
<EXTSTK _<date YYYYMMDDHH24MISS >.zip
The zip file can contain one or more files from same or different stores:
EXTSTK_<date in YYYYMMDDHH24MISS format>.dat
DataFilename format
<file prefix>_<date in YYYYMMDDHH24MISS format>_<loc id>.dat
Where file prefix value is EXTSTK_ and loc id is the store identifier. This allows file to be unique for every upload.
Example: EXTSTK_20180129133250_1111.dat
File Layout
Pipe-delimited (|) file
Table B-10 Initial Inventory Import File
Record Name | Field Name | Field Type | Default Value | Description |
---|---|---|---|---|
FHEAD |
Record Descriptor |
Char(5) |
FHEAD |
File head marker |
Store Number |
Char(10) |
Store number file was uploaded for. It is assumed only one store is passed in per file. (Required) |
||
FDETL |
Record Descriptor |
Char(5) |
FDETL |
Detail record marker |
Upload Date |
Date(14) |
Indicates date/time item was physically counted. (YYYYMMDDHH24MISS) For example, 20180129134600 (Required for UIN Records) |
||
Area Number |
Char(10) |
10-digit code indicating where in the store the item is located. (Optional) |
||
UPC or Item Number |
Char(25) |
25-digit universal product code. (Required) |
||
Count Quantity |
Number(12,4) |
Quantity counted for item, required. This field must allow for decimals when counting in UOM other than each. (Required) |
||
UIN(Item Serial Number) |
Char(128) |
Unique identification serial number for item, required if current item requires serial number. |
||
FTAIL |
Record Descriptor |
Char(5) |
FTAIL |
File tail marker |
Sample File
FHEAD|5000|
FDETL|20180129235959|1|100665085|1|ItemSerialNum1234|
FDETL|201180129140000|1|100665085|1|ItemSerialNum9999|
FDETL|20180129000000|1|100665085|1||
FTAIL|
Third Party Stock Count Import File Layout
Filename Format
<file prefix>_<date YYYYMMDDHH24MISS >.zip
Where file prefix value is STK.
Example:
STK_20180129133250.zip
The zip file can contain one or more files from same or different stores:
Data Filename Format
<file prefix>_<date in YYYYMMDDHH24MISS format>_<loc id>.dat
Where file prefix value is STK and loc id is the store identifier.
Example:
STK_20180129133250_1111.dat
File Layout
Pipe-delimited (|) file
Table B-11 Third Party Stock Count Import File
Record Name | Field Name | Field Type | Default Value | Description |
---|---|---|---|---|
FHEAD |
Record Descriptor |
Char(5) |
FHEAD |
File head marker |
Store Number |
Char(10) |
Store number file was uploaded for. It is assumed only one store is passed in per file. (Required) |
||
Stock Count ID |
Number(12) |
Unique identifier for item. Assumption is application will always take first stock count ID listed. (Required) |
||
FDETL |
Record Descriptor |
Char(5) |
FDETL |
Detail record marker |
Stock Count Date |
Date(14) |
Indicates date/time item was physically counted. (YYYYMMDDHH24MISS) For example, 20180129134600 (Required) Note: If not using timestamp, use 00 for time. |
||
Area Number |
Char(10) |
10-digit code indicating where in the store the item is located. (Optional) |
||
UPC or Item Number |
Char(25) |
25-digit universal product code. (Required) |
||
Count Quantity |
Number(12,4) |
Quantity counted for item, required. This field must allow for decimals when counting in UOM other than each. (Required) |
||
UIN(Item Serial Number) |
Char(128) |
Unique identification serial number for item, required if current item requires serial number. |
||
FTAIL |
Record Descriptor |
Char(5) |
FTAIL |
File tail marker |
Sample File
FHEAD|5000|1074|
FDETL|20180129235959|1|100665085|1|ItemSerialNum1234|
FDETL|201180129140000|1|100665085|1|ItemSerialNum9999|
FDETL|20180129000000|1|100665085|1||
FTAIL|
Warehouse Available Inventory Import Specification
File Layout
-
All files should be in CSV (comma-separated values) format, with a ".csv" filename extension.
-
The batch jobs also support zipped files which will be extracted upon download and processed individually. Files contained within .zip files must adhere to the same filename format.
-
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.
Table B-12 Warehouse Available Inventory Import File Layout
Field Name | Description | Required | Data Type |
---|---|---|---|
ACTION |
The record action type. Valid values: REPLACE |
Yes |
VARCHAR2(20) |
ITEM_ID |
The unique identifier of the item - references the ITEM_ID column in the ITEM table. |
Yes |
VARCHAR2 (25) |
WAREHOUSE_ID |
Virtual warehouse id |
Yes |
NUMBER (10, 0) |
LOC_TYPE |
Type of location. W represents the virtual warehouse.' |
Yes |
VARCHAR2(1) |
AVAIL_QTY |
Available quantity of the item at the location. This qty is calculated by subtracting transfer reserved qty, customer reserved qty, non_sellable inventory and RTV from stock on hand. |
Yes |
NUMBER(20,4) |
STOCK_ON_HAND |
Current stock on hand for the item. |
Yes |
NUMBER(20,4) |
STANDARD_UOM |
The standard unit of measure of the warehouse item. |
No |
VARCHAR2 (4) |
PHYSICAL_WH |
Physical warehouse that Is assigned to the virtual warehouse. |
Yes |
NUMBER(10) |
QUANTITY_RESERVED |
Reserved quantity. |
No |
NUMBER(20,4) |
QUANTITY_IN_TRANSIT |
In transit quantity. |
No |
NUMBER(20,4) |
Example File
File Name: InvAvailWh_Tx_{YYYYMMddHHMMss}.csv
REPLACE,100637113,9999,W,100,150,EA,8888,,,