| Oracle® Retail Enterprise Inventory Cloud Service Administration Guide Release 17.0 E97808-03 |
|
 Previous |
 Next |
| Oracle® Retail Enterprise Inventory Cloud Service Administration Guide Release 17.0 E97808-03 |
|
Previous |
Next |
This chapter describes the batch file layout specification.
<file prefix>_<date in YYYYMMDDHH24MISS format>_<loc id>.dat
Where file prefix value is STK and loc id is the store identifier. This allows file to be unique for every upload.
For each file, there need to be a .complete file present to job to process the file. The complete file can be any empty. This is to notify the job that uploading of data file is completed.
Example:
STK_20180129133250_1111.dat - Data File.
STK_20180129133250_1111.dat.complete - Complete File.
<file prefix>_<date in YYYYMMDDHH24MISS format>.zip
Where file prefix value is STK. 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:
STK_20180129133250.zip - Zip File.
STK_20180129133250.zip.complete - Complete File.
Pipe-delimited (|) file contains store count data for a store and stock count ID as shown in Table D-1:
Table D-1 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 by the third party. (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. |
The following is a sample Third Party Stock Count Import File:
FHEAD|5000|1074| FDETL|20180129235959|1|100665085|1|ItemSerialNum1234| FDETL|201180129140000|1|100665085|1|ItemSerialNum9999| FDETL|20180129000000|1|100665085|1|| FTAIL|
<event type>_<date in YYYYMMDDHH24MISS format>_<loc id>_<loc type>.dat
Where event type value is ”CLRPC” (clearances), the loc type field would be either ’S' or ’W'.
For each file, there need to be a .complete file present to job to process the file. The complete file can be any empty. This is to notify the job that uploading of data file is completed.
Example:
CLRPC_20180129133250_1111_S.dat - Data File.
CLRPC_20180129133250_1111_S.dat.complete - Complete File.
<event type>_<date in YYYYMMDDHH24MISS format>_<loc type>.zip
Where event type value is "CLRPC". The loc type field would be either 'S' or 'W'.
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:
CLRPC_20180129133250_S.zip - Zip File.
CLRPC_20180129133250_S.zip.complete - Complete File.
FHEAD (required): File identification, one line per file.
FDETL (optional): Price Change Event (Create or Modify).
FDELE (optional): Price Change Event (Delete).
FTAIL (required): End of file marker, one line per file.
Table D-2 Clearance Price Change File Layout
| Record Name | Field Name | Field Type | Default Value | Description |
|---|---|---|---|---|
|
FHEAD |
Record Descriptor |
Char(5) |
FHEAD |
File head marker |
|
Line ID |
Number(10) |
1 |
Unique line ID |
|
|
File Type |
Char(5) |
CLRPC |
Clearance Price Changes |
|
|
Export timestamp |
Timestamp |
System clock timestamp (YYYYMMDDHHMISS) |
||
|
Location |
Number(10) |
Location identifier |
||
|
Location Type |
Char(1) |
S = Store, W = Warehouse |
||
|
FDETL |
Record Descriptor |
Char(5) |
FDETL |
File Detail Marker (one per clearance create or modify) |
|
Line ID |
Number(10) |
Unique line ID |
||
|
Event Type |
Char(3) |
CRE = Create MOD = Modify |
||
|
ID |
Number(15) |
Clearance identifier |
||
|
Item |
Char(25) |
Item identifier |
||
|
Effective Date |
Date |
Clearance Effective Date (YYYMMDDHHMISS) |
||
|
Selling Retail |
Number(20,4) |
Selling retail with price change applied |
||
|
Selling Retail UOM |
Char(4) |
Selling retail unit of measure |
||
|
Selling Retail Currency |
Char(3) |
Selling retail currency |
||
|
Reset Clearance ID |
Number(15) |
ID of clearance reset |
||
|
FDELE |
Record Descriptor |
Char(5) |
FDELE |
File Detail Delete Marker (one per clearance delete) |
|
Line ID |
Number(10) |
Unique line ID |
||
|
ID |
Number(15) |
Clearance identifier |
||
|
Item |
Char(25) |
Item identifier |
||
|
FTAIL |
Record Descriptor |
Char(5) |
FTAIL |
File tail marker |
|
Line ID |
Number(10) |
Unique line ID |
||
|
Number of lines |
Number(10) |
Number of lines in file not counting FHEAD and FTAIL |
<event type>_<date in YYYYMMDDHH24MISS format>_<loc id>_<loc type>.dat
Where event type is "REGPC" (regular price changes). The loc type field would be either 'S' or 'W'.
For each file, there need to be a .complete file present to job to process the file. The complete file can be any empty. This is to notify the job that uploading of data file is completed.
Example:
REGPC_20180129133250_1111_S.dat - Data File.
REGPC_20180129133250_1111_S.dat.complete - Complete File.
<event type>_<date in YYYYMMDDHH24MISS format>_<loc type>.zip
Where event type value is "REGPC". The loc type field would be either 'S' or 'W'.
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:
REGPC_20180129133250_S.zip - Zip File.
REGPC_20180129133250_S.zip.complete - Complete File.
FHEAD (required): File identification, one line per file.
FDETL (optional): Price Change Event (Create or Modify).
FDELE (optional): Price Change Event (Delete).
FTAIL (required): End of file marker, one line per file.
Table D-3 RegularPriceChange File Layout
| Record Name | Field Name | Field Type | Default Value | Description |
|---|---|---|---|---|
|
FHEAD |
Record Descriptor |
Char(5) |
FHEAD |
File head marker |
|
Line ID |
Number(10) |
1 |
Unique line ID |
|
|
File Type |
Char(5) |
REGPC |
Regular Price Changes |
|
|
Export timestamp |
Timestamp |
System clock timestamp (YYYYMMDDHHMISS) |
||
|
Location |
Number(10) |
Location identifier |
||
|
Location Type |
Char(1) |
S = Store W= Warehouse |
||
|
FDETL |
Record Descriptor |
Char(5) |
FDETL |
File Detail Marker (one per price change create or modify) |
|
Line ID |
Number(10) |
Unique line ID |
||
|
Event Type |
Char(3) |
CRE = Create MOD = Modify |
||
|
ID |
Number(15) |
Price Change identifier |
||
|
Item |
Char(25) |
Item identifier |
||
|
Effective Date |
Date |
Effective Date of price change (YYYYMMDDHHMISS) |
||
|
Selling Unit Change Ind |
Number(1) |
Did selling unit retail change with this price event (0 = no change, 1 = changed) |
||
|
Selling Retail |
Number(20,4) |
Selling retail with price change applied |
||
|
Selling Retail UOM |
Char(4) |
Selling retail unit of measure |
||
|
Selling Retail Currency |
Char(3) |
Selling retail currency |
||
|
Multi-Unit Change Ind |
Number(1) |
Did multi unit retail change with this price event (0 = no change, 1 = changed) |
||
|
Multi-Units |
Number(12,4) |
Number Multi Units |
||
|
Multi-Unit Retail |
Number(20,4) |
Multi Unit Retail |
||
|
Multi-Unit UOM |
Char(4) |
Multi Unit Retail Unit Of Measure |
||
|
Multi-Unit Currency |
Char(3) |
Multi Unit Retail Currency |
||
|
FDELE |
Record Descriptor |
Char(5) |
FDELE |
File Detail Delete Marker (one per price change delete) |
|
Line ID |
Number(10) |
Unique line ID |
||
|
ID |
Number(15) |
Price Change identifier |
||
|
Item |
Char(25) |
Item identifier |
||
|
FTAIL |
Record Descriptor |
Char(5) |
FTAIL |
File tail marker |
|
Line ID |
Number(10) |
Unique line ID |
||
|
Number of lines |
Number(10) |
Number of lines in file not counting FHEAD and FTAIL |
<event type>_<date in YYYYMMDDHH24MISS format>_<loc id>_<loc type>.dat
Where event type is "PRMPC" (promotional price change). The loc type field would be either 'S' or 'W'.
For each file, there need to be a .complete file present to job to process the file. The complete file can be any empty. This is to notify the job that uploading of data file is completed.
Example:
PRMPC_20180129133250_1111_S.dat - Data File.
PRMPC_20180129133250_1111_S.dat.complete - Complete File.
<event type>_<date in YYYYMMDDHH24MISS format>_<loc type>.zip
Where event type value is "PRMPC". The loc type field would be either 'S' or 'W'.
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:
PRMPC_20180129133250_S.zip - Zip File.
PRMPC_20180129133250_S.zip.complete - Complete File.
FHEAD (required): File identification, one line per file.
TMBPE (optional): Event Type.
TPDTL (required): Promotion Component Detail.
TPGRP (required): Promotion Group (one or more per TPDTL).
TGLST (required): Promotion List (one or more per TPGRP).
TLITM (required): Promotion Item (one or more per TGLST).
TPDSC (required): Promotion Discount (one or more per TGLST).
TPISR (optional): Promotion Item Selling retail (one or more per TPDTL).
TPCDT (optional): Promotion Credit Detail (one or more per TPDTL).
TTAIL (required): Transaction tail (one per promotion).
TPCIL (optional): Promotion Cancel Item Location
FPDEL (optional): Promotion Delete.
FTAIL (required): End of file marker, one line per file.
Table D-4 Promotion Price Change Output File Layout
| Record Name | Field Name | Field Type | Default Value | Description |
|---|---|---|---|---|
|
FHEAD |
Record Descriptor |
Char(5) |
FHEAD |
File head marker |
|
Line ID |
Number(10) |
1 |
Unique line Identifier |
|
|
File Type |
Char(5) |
PROMO |
Promotions |
|
|
Export timestamp |
Timestamp |
System clock timestamp (YYYYMMDDHHMISS) |
||
|
Location |
Number(10) |
Location identifier |
||
|
Location Type |
Char(1) |
S = Store, W = Warehouse |
||
|
TMBPE |
Record Descriptor |
Char(5) |
TMBPE |
Promotion (transaction head) |
|
Line ID |
Number(10) |
Unique line identifier |
||
|
Event Type |
Char(3) |
CRE = Create MOD= Modify |
||
|
TPDTL |
Record Descriptor |
Char(5) |
TPDTL |
Promotion Detail Component |
|
Line ID |
Number(10) |
Unique line identifier |
||
|
Promo ID |
Number(10) |
Promotion identifier |
||
|
Promo Comp ID |
Number(10) |
Promotion Component ID |
||
|
Promo Name |
Char(160) |
Promotion Header Name |
||
|
Promo Desc |
Char(640) |
Promotion Header Description |
||
|
Promo Comp Desc |
Char(160) |
Promotion Component Name |
||
|
Promo Type |
Number(2) |
Valid values will be modified to be as follows: 0 - Multi-Buy Promotion; 1 - Simple Promotion; 2 - Threshold Promotion; 3 - Finance Promotion (formerly tied to a value of 6); 4 - Transaction Promotion |
||
|
Promo Comp Detail ID |
Number(10) |
Promotion Component Detail identifier |
||
|
Start Date |
Date |
Start Date of Promotion Component Detail (YYYYMMDDHH24MISS) |
||
|
End Date |
Date |
End Date of Promotion Component Detail (YYYYMMDDHH24MISS) |
||
|
Apply To Code |
Number(1) |
Holds the apply to code for the promotion detail. Determines if the promotion is applied to regular retail only (no clearances in effect), clearance retail only (only when a clearance is in effect) or both regular and clearance retail. Valid values are 0 - Regular Only; 1 - Clearance Only; 2 - Regular and Clearance. |
||
|
Discount Limit |
Number(3) |
The number of times that the promotion that can be applied to a transaction. |
||
|
Apply Order |
Number(1) |
Application Order of the Promotion |
||
|
Threshold ID |
Number(6) |
Threshold identifier |
||
|
Customer Type ID |
Number(10) |
Customer Type identifier |
||
|
Threshold Qualification Type |
Number(1) |
The qualification type for the threshold. Will only be populated for threshold promotions. Valid values are 0 for item level and 1 for threshold level. |
||
|
Exception Parent ID |
Number(10) |
The promotion component detail ID for the parent event that this event is a child to. Used only for transaction promotion detail exclusions to be able to connect the child exclusion to it's parent promotion detail. |
||
|
TPGRP |
Record Descriptor |
Char(5) |
TPGRP |
Promotion Detail Group |
|
Line ID |
Number(10) |
Unique line identifier |
||
|
Group ID |
Number(10) |
Group Number |
||
|
TGLIST |
Record Descriptor |
Char(5) |
TGLIST |
Promotion Group List |
|
Line ID |
Number(10) |
Unique line identifier |
||
|
List ID |
Number(10) |
List identifier |
||
|
Reward Application |
Number(1) |
How this reward is applied to the promotion detail. |
||
|
Description |
Char(120) |
Description |
||
|
Price Range Min |
Number (20,4) |
Contains price range promotion minimum valid retail value. |
||
|
Price Range Max |
Number (20,4) |
Contains price range promotion maximum valid retail value. |
||
|
TLITM |
Record Descriptor |
Char(5) |
TLITM |
Promotion Group List |
|
Line ID |
Number(10) |
Unique line identifier |
||
|
Item ID |
Char(25) |
Transaction Item Identifier |
||
|
TPDSC |
Record Descriptor |
Char(5) |
TPDSC |
Discount Detail for List |
|
Line ID |
Number(10) |
Unique line identifier |
||
|
Change Type |
Number(2) |
Change Type |
||
|
Change Amount |
Number(20,4) |
Change Amount |
||
|
Change Currency |
Char(3) |
Change Currency |
||
|
Change Percent |
Number(20,4) |
Change Percent |
||
|
Change Selling UOM |
Char(4) |
Change Selling UOM |
||
|
Qual Type |
Number(2) |
Qualification Type |
||
|
Qual Value |
Number(2) |
Qualification Value |
||
|
Change Duration |
Number(20,4) |
Change Duration |
||
|
TPISR |
Record Descriptor |
Char(5) |
TPISR |
Items in Promotion |
|
Line ID |
Number(10) |
Unique line identifier |
||
|
Item ID |
Char(25) |
TTAIL |
Transaction Item Identifier |
|
|
Selling Retail |
Number(20,4) |
Selling retail of the item |
||
|
Selling UOM |
Char(4) |
Selling UOM of the item |
||
|
Effective Date |
Date |
Effective Date of the selling retail - YYYYMMDDHH24MISS |
||
|
Selling Retail Currency |
Char(3) |
Selling retail currency |
||
|
Referenced Promo Detail ID |
Number(15) |
Referenced promotion detail ID. This is an optional field. |
||
|
TPCDT |
Record Descriptor |
Char(5) |
TPCDT |
Credit Detail |
|
Credit Detail ID |
Number(10) |
Credit Detail ID |
||
|
Line ID |
Number(10) |
Unique line ID |
||
|
Credit Type |
Char(40) |
Credit Type |
||
|
BinNumberFrom |
Number(10) |
BinNumber From |
||
|
BinNumberTo |
Number(10) |
Bin Number To |
||
|
Commission Rate |
Number(10) |
Commission Rate |
||
|
Comments |
Char(160) |
Comments |
||
|
TTAIL |
Record Descriptor |
Char(5) |
TTAIL |
Transaction Tail |
|
Line ID |
Number(10) |
Unique line identifier |
||
|
TPCIL |
Record Descriptor |
Char(5) |
TPCIL |
Cancel Item Loc |
|
Line ID |
Number(10) |
Unique line identifier |
||
|
Promo ID |
Number(10) |
The ID of the promotion |
||
|
Promo Comp ID |
Number(10) |
Promotion Component ID |
||
|
Promo Comp Detail ID |
Number(10) |
Promotion Component Detail identifier |
||
|
Item ID |
Char(25) |
Transaction Item Identifier for item |
||
|
Cancellation Date |
Date |
Cancellation effective date - YYYYMMDDHH24MISS |
||
|
FPDEL |
Record Descriptor |
Char(5) |
FPDEL |
Delete Promotion |
|
Line ID |
Number(10) |
Unique line identifier |
||
|
Promo ID |
Number(10) |
The ID of the promotion |
||
|
Promo Comp ID |
Number(10) |
Promotion Component ID |
||
|
Promo Comp Detail ID |
Number(10) |
Promotion Component Detail ID |
||
|
Group ID |
Number(10) |
Group Number |
||
|
List ID |
Number(10) |
List ID |
||
|
Item ID |
Char(25) |
Transaction Item Identifier for item |
||
|
FTAIL |
Record Descriptor |
Char(5) |
FTAIL |
File tail marker |
|
Line ID |
Number(10) |
Unique line identifier |
||
|
Number of lines |
Number(10) |
Number of lines in file not counting FHEAD and FTAIL |
<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.
For each file, there need to be a .complete file present to job to process the file. The complete file can be any empty. This is to notify the job that uploading of data file is completed.
Example:
SIMTLOG_20180129133250_1111.dat - Data File.
SIMTLOG_20180129133250_1111.dat.complete - Complete File.
<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. The complete file needs to be added for zip file for job to pick it for processing.
Example:
SIMTLOG_20180129133250.zip - Zip File.
SIMTLOG_20180129133250.zip.complete - Complete File.
Table D-5 SIMT-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 |
|
Location Number |
NUMBER(10) |
Store Number |
||
|
Business Date |
VARCHAR2(14) |
Business Date of transactions in YYYYMMDDHHSS format |
||
|
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 Number |
VARCHAR2(128) |
The unique transaction reference number generated by ORXPOS/OMS. |
||
|
Transaction Date and Time |
VARCHAR2(14) |
Date transactions were processed in ORXPOS/OMS |
||
|
Customer Order ID |
VARCHAR2(128) |
External customer order ID, if transaction is a customer order |
||
|
Customer Order Comments |
VARCHAR(512) |
Comments on the customer order |
||
|
TRANSACTION DETAIL |
File Type Record Descriptor |
VARCHAR2(5) |
TDETL |
Identifies the File Record Type |
|
Item ID |
VARCHAR2(25) |
ID number of the item. |
||
|
UIN |
VARCHAR2(128) |
This is the UNIQUE_ID value from RTLOG |
||
|
Item Quantity |
NUMBER(12,4) |
Quantity of the item on this transaction |
||
|
Selling UOM |
VARCHAR2(4) |
UOM at which this item was sold |
||
|
Reason Code |
NUMBER(4) |
Reason entered by cashier for some transaction types. Required for voids, returns, for example. |
||
|
Comments |
VARCHAR(512) |
Comments for this line item |
||
|
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 |
||
|
Reservation Type |
VARCHAR(25) |
Reservation type if POS transaction is a customer order. Valid values are SPECIAL_ORDER, WEB_ORDER, PICKUP_AND DELIVERY,LAYAWAY |
||
|
Fulfillment Order Number |
VARCHAR2(48) |
Fulfillment Order Number from OMS |
||
|
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 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 Record Counter |
NUMBER(10) |
Number of records/transactions processed in current file (only records between head and tail) |
<file prefix>_<date in YYYYMMDDHH24MISS format>_<loc id>.dat
Where file prefix value is "SIMT" and loc id is the store identifier. This allows file to be unique for every upload.
For each file, there need to be a .complete file present to job to process the file. The complete file can be any empty. This is to notify the job that uploading of data file is completed.
Example:
SIMT_20180129133250_1111.dat - Data File.
SIMT_20180129133250_1111.dat.complete - Complete File.
<file prefix>_<date in YYYYMMDDHH24MISS format>.zip
Where file prefix value is "SIMT". 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:
SIMT_20180129133250.zip - Zip File.
SIMT_20180129133250.zip.complete - Complete File.
Table D-6 SIM-ReSA File Layout
| Record Name | Field Name | Field Type | Default Value | Description |
|---|---|---|---|---|
|
�FILE HEADER |
FILE Type Record Descriptor |
VARCHAR2(5) |
FHEAD |
Identifies the File Record Type |
|
File Line ID |
VARCHAR(10) |
Sequential file line number |
||
|
File Type Definition |
VARCHAR2(4) |
SIMT |
Identifies the File Type |
|
|
Location Number |
NUMBER(10) |
Store Number |
||
|
Business Date |
VARCHAR2(14) |
Business Date of transactions in YYYYMMDDHHSS format |
||
|
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 |
|
File Line ID |
VARCHAR(10) |
Sequential file line number |
||
|
Transaction Number |
NUMBER(10) |
The unique transaction reference number generated by ORXPOS/OMS |
||
|
Register Number |
NUMBER(10) |
The register reference number generated by ORXPOS |
||
|
Revision Number |
NUMBER(3) |
The version of the transaction being sent |
||
|
Transaction Date and Time |
VARCHAR2(14) |
Date transactions were processed in ORXPOS/OMS |
||
|
Transaction Type |
VARCHAR2(14) |
Transaction Type Code (for example, SALE, RETURN, SPLORD) |
||
|
Pos created flag |
VARCHAR2(1) |
'Y' identifies that the transaction occurred at ORXPOS, 'N' identifies that the transaction was created in ReSA |
||
|
TRANSACTION DETAIL |
File Type Record Descriptor |
VARCHAR2(5) |
TDETL |
Identifies the File Record Type |
|
File Line ID |
VARCHAR(10) |
Sequential file line number. |
||
|
Item Sequence Number |
NUMBER(4) |
The order in which items were entered during a transaction |
||
|
Item |
VARCHAR2(25) |
ID number of the item. |
||
|
Item Type |
VARCHAR2(6) |
Type of Item sold. Can be 'ITEM', 'REF', 'GCN', 'NMITEM' |
||
|
Reference Item |
VARCHAR2(25) |
Identified sub-transaction level merchandise item. |
||
|
Non-Merchandise Item |
VARCHAR2(25) |
Identifies non-merchandise item |
||
|
Item Status |
VARCHAR2(6) |
Status of the item within the transaction, V for item void, S for sold item, R for returned item, Layaway Initiate (LIN), Layaway Cancel, Layaway Complete (LCO), Order Initiate (ORI), Order Cancel (ORC) Order Complete (ORD) |
||
|
Serial Number |
VARCHAR2(128) |
This is the UNIQUE_ID value from RTLOG |
||
|
Pack Indicator |
VARCHAR2(1) |
Pack indicator of item sold or returned |
||
|
Catch Weight Indicator |
VARCHAR2(1) |
Indicates if item is a catchweight item |
||
|
Item Quantity Sign |
VARCHAR2(1) |
Determines if the Total Sale Quantity is positive or negative 'P' - Positive 'N' - Negative |
||
|
Item Quantity Value |
NUMBER(20) |
Total sales value of goods sold/returned (4 implied decimal places), for example, Total Quantity * 10000 |
||
|
Standard UOM |
VARCHAR2(4) |
Standard UOM of the Item |
||
|
Selling UOM |
VARCHAR2(4) |
UOM at which this item was sold |
||
|
Wastage Type |
VARCHAR2(6) |
Wastage type of item sold or returned |
||
|
Wastage Percentage |
NUMBER(12) |
Wastage Percent*10000 (4 implied decimal places), wastage percent of item sold or returned |
||
|
Drop Ship Indicator |
VARCHAR2(1) |
N |
This will always be N for Export |
|
|
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' |
||
|
Reason Code |
VARCHAR2(6) |
Reason entered by cashier for some transaction types |
||
|
Sale Value |
NUMBER(20) |
Total Sales Value * 10000 (4 implied decimal places), sales value, net sales value of goods sold |
||
|
Sales Sign |
VARCHAR2(1) |
Determines if the Total Sales Value is positive or negative 'P' - Positive 'N' - Negative |
||
|
Unit Retail |
NUMBER(20,4) |
Unit retail with 4 implied decimal places |
||
|
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) |
||
|
Customer Order Number |
VARCHAR2(50) |
Customer Order Number |
||
|
Customer Order Type |
Customer order type |
|||
|
Fulfillment Order Number |
VARCHAR2(50) |
Fulfillment Order Number from OMS |
||
|
TRANSACTION TAIL |
File Record Type Descriptor |
VARCHAR2(5) |
TTAIL |
Identifies the File Record Type |
|
File Line ID |
NUMBER(10) |
Sequential file line number |
||
|
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 Line ID |
NUMBER(10) |
Sequential file line number |
||
|
File Record Counter |
NUMBER(10) |
Number of records/transactions processed in current file (only records between head and tail) |
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 D-7 Stock Count Export File
| Record Name | Field Name | Field Type | Description |
|---|---|---|---|
|
File Header |
file type record descriptor |
Char(5) |
hardcode FHEAD |
|
file line identifier |
Number(10) |
ID of current line being processed, hardcode 000000001 |
|
|
file type |
Char(4) |
hardcode STKU |
|
|
file create date |
Date(14)YYYYMMDDHHMISS |
date written by convert program |
|
|
stocktake_date |
Date(14)YYYYMMDDHHMISS |
take_head.stocktake_date |
|
|
cycle count |
Number(8) |
stake_head.cycle_count |
|
|
loc_type |
Char(1) |
hardcode W or S |
|
|
location |
Number(10) |
stake_location.wh or stake_location.store |
|
|
Transaction record |
file type record descriptor |
Char(5) |
hardcode FDETL |
|
file line identifier |
Number(10) |
ID of current line being processed, internally incremented |
|
|
item type |
Char(3) |
hardcode ITM |
|
|
item value |
Char(25) |
item ID |
|
|
inventory quantity |
Number(12,4) |
total units or total weight |
|
|
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 line identifier |
Number(10) |
ID of current line being processed, internally incremented |
|
|
file record count |
Number(10) |
Number of detail records |
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.
<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.
For each file, there need to be a .complete file present to job to process the file. The complete file can be any empty. This is to notify the job that uploading of data file is completed.
Example:
SSEQ_20180129133250_1111.dat - Data File.
SSEQ_20180129133250_1111.dat.complete - Complete File.
<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 - Zip File.
SSEQ_20180129133250.zip.complete - Complete File.
Table D-8 Store Sequence Import File
| Record Name | Field Name | FieldType | Description |
|---|---|---|---|
|
File Header |
file type record descriptor |
Char(5) |
hardcode FHEAD |
|
Store ID |
Number(10) |
Store identifier |
|
|
Sequence record |
file type record descriptor |
Char(5) |
hardcode SHEAD |
|
Area type |
Number(9) |
The Store Sequence Area. 0 = None, 1 = Shopfloor, 2 = Backroom |
|
|
Child sequenced |
Varchar2(1) |
’Y' if child is sequenced, ’N' if not |
|
|
Department ID |
Number(12) |
Department ID |
|
|
Class ID |
Number(12) |
Class ID |
|
|
Description |
Varchar2(255) |
Description of Store Sequence |
|
|
Not sequenced |
Varchar2(1) |
The order the store sequence is in compared to other store sequences |
|
|
Sequence Order |
Number(20) |
Y indicates a default sequence containing all items that have not been sequenced elsewhere |
|
|
Sequence detail |
file type record descriptor |
Char(5) |
hardcode SDETL |
|
Item ID |
Varchar2(25) |
Item ID |
|
|
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 |
|
|
Item sequence order |
Number(20) |
Order of item within store sequence |
|
|
Capacity |
Number(11,2) |
The size of the location appropriate to unit of measure |
|
|
Ticket quantity |
Number(11,2) |
The quantity of tickets that need to be printed or used for the item inventory location |
|
|
Ticket format ID |
Number(10) |
Item ticket format identifier |
|
|
Uom mode |
Number(2) |
The Unit Of Measure display mode: 1 = Units, 2 = Cases |
|
|
Width |
Number(12) |
Width value to indicate how many items can fit across the width of the shelf |
|
|
Sequence trailer |
File type record descriptor |
Char(5) |
hardcode STAIL |
|
File trailer |
File type record descriptor |
Char(5) |
hardcode FTAIL |
<file prefix>_<date in YYYYMMDDHH24MISS format>.dat
Where file prefix value is "WHIMS". The prefix and date combination allows file to be unique for every upload.
For each file, there need to be a .complete file present to job to process the file. The complete file can be any empty. This is to notify the job that uploading of data file is completed.
Example:
WHIMS_20180129133250_1111.dat - Data File.
WHIMS_20180129133250_1111.dat.complete - Complete File.
<file prefix>_<date in YYYYMMDDHH24MISS format>.zip
Where file prefix value is "WHIMS". The zip file can contain one or more files. The complete file needs to be added for zip file for job to pick it for processing.
Example:
WHIMS_20180129133250.zip - Zip File.
WHIMS_20180129133250.zip.complete - Complete File.
Table D-9 Warehouse Stock Import File
| Record Name | Field Name | Field Type | Default Value | Description |
|---|---|---|---|---|
|
1 |
Warehouse ID |
Number(10) |
NA |
ID of the Warehouse, for example, 7000 |
|
2 |
Item ID |
Varchar2(25) |
NA |
Item Identifier. |
|
3 |
Stock On Hand |
Number(12) |
NA |
Stock on hand of the item in the warehouse. |
|
4 |
Status |
Varchar2(2) |
NA |
Status code of the item. Example A - Active, D - Deleted, I - Inactive, C - Discontinued. |
The file generated Inventory extract contains current available stock on hand for all the item in a store. Single file will contain inventory for single store, when executed for all stores job will create single file per store and zip all the file to single zip archive.
<file prefix>_<store id>_<date in YYYYMMDDHH24MISS format>.txt
Where file prefix value is "PRODUCT_LOCATION_INV" and the store id is unique store identifier.
<file prefix>_<date in YYYYMMDDHH24MISS format>.zip
Where file prefix value is "PRODUCT_LOCATION_INV".
The first row will contain the placeholder information of the data on the file separated by pipe ('|').
Example:
system_cd|location_cd|product_cd|available_qty|next_po_qty|next_po_date|daily_sell_through_qty|sell_qty_multiple|minimum_sell_qty|shrink_rate|sales_velocity
The input file would be in Pipe ('|') delimited format.
Table D-10 Inventory Extract File
| Record Name | Field Name | Field Type | Default Value | Description |
|---|---|---|---|---|
|
1 |
System Code |
Varchar2 |
INV |
This field will always hold default value that is, INV |
|
2 |
Location Code |
Number |
NA |
This field holds the store identifier. |
|
3 |
Product Code |
Varchar2 |
NA |
This field hold the item identifier. |
|
4 |
Available Quantity |
Number |
NA |
This field holds the current available stock on hand for the item in store. |
|
5 |
Next PO Quantity |
Number |
0 |
This field will hold next purchase order quantity. |
|
6 |
Next PO Date |
Varchar2 |
NA |
This field hold the next purchase order date. This field is currently unused and will be empty. |
|
7 |
Daily Sell Through Quantity |
Number |
NA |
This field is currently unused and will be empty. |
|
8 |
Sell Qty Multiple |
Number |
NA |
This field is currently unused and will be empty. |
|
9 |
Minimum Sell Qty |
Number |
NA |
This field is currently unused and will be empty. |
|
10 |
Shrink Rate |
Number |
NA |
This field is currently unused and will be empty. |
|
11 |
Sales Velocity |
Number |
NA |
This field is currently unused and will be empty. |
The following is a sample Inventory Extract File:
system_cd|location_cd|product_cd|available_qty|next_po_qty|next_po_date|daily_sell_through_qty|sell_qty_multiple|minimum_sell_qty|shrink_rate|sales_velocity
INV|5000|100665050|10|0||||||
INV|5000|100665051|10|0||||||
INV|5000|100665052|10|0||||||
