Oracle® Retail Enterprise Inventory Cloud Service Administration Guide Release 19.7 F70119-01 |
|
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 E-1:
Table E-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 E-2 Clearance Price Change File Layout
Record Name | Field Name | Field Type | Default Value | Description |
---|---|---|---|---|
FHEAD |
Record Descriptor |
Char(5) |
FHEAD |
File head marker |
FHEAD |
Line ID |
Number(10) |
1 |
Unique line ID |
FHEAD |
File Type |
Char(5) |
CLRPC |
Clearance Price Changes |
FHEAD |
Export timestamp |
Timestamp |
System clock timestamp (YYYYMMDDHHMISS) |
|
FHEAD |
Location |
Number(10) |
Location identifier |
|
FHEAD |
Location Type |
Char(1) |
S = Store, W = Warehouse |
|
FDETL |
Record Descriptor |
Char(5) |
FDETL |
File Detail Marker (one per clearance create or modify) |
FDETL |
Line ID |
Number(10) |
Unique line ID |
|
FDETL |
Event Type |
Char(3) |
CRE = Create MOD = Modify |
|
FDETL |
ID |
Number(15) |
Clearance identifier |
|
FDETL |
Item |
Char(25) |
Item identifier |
|
FDETL |
Effective Date |
Date |
Clearance Effective Date (YYYMMDDHHMISS) |
|
FDETL |
Selling Retail |
Number(20,4) |
Selling retail with price change applied |
|
FDETL |
Selling Retail UOM |
Char(4) |
Selling retail unit of measure |
|
FDETL |
Selling Retail Currency |
Char(3) |
Selling retail currency |
|
FDETL |
Reset Clearance ID |
Number(15) |
ID of clearance reset |
|
FDELE |
Record Descriptor |
Char(5) |
FDELE |
File Detail Delete Marker (one per clearance delete) |
FDELE |
Line ID |
Number(10) |
Unique line ID |
|
FDELE |
ID |
Number(15) |
Clearance identifier |
|
FDELE |
Item |
Char(25) |
Item identifier |
|
FTAIL |
Record Descriptor |
Char(5) |
FTAIL |
File tail marker |
FTAIL |
Line ID |
Number(10) |
Unique line ID |
|
FTAIL |
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 E-3 Regular Price Change File Layout
Record Name | Field Name | Field Type | Default Value | Description |
---|---|---|---|---|
FHEAD |
Record Descriptor |
Char(5) |
FHEAD |
File head marker |
FHEAD |
Line ID |
Number(10) |
1 |
Unique line ID |
FHEAD |
File Type |
Char(5) |
REGPC |
Regular Price Changes |
FHEAD |
Export timestamp |
Timestamp |
System clock timestamp (YYYYMMDDHHMISS) |
|
FHEAD |
Location |
Number(10) |
Location identifier |
|
FHEAD |
Location Type |
Char(1) |
S = Store W= Warehouse |
|
FDETL |
Record Descriptor |
Char(5) |
FDETL |
File Detail Marker (one per price change create or modify) |
FDETL |
Line ID |
Number(10) |
Unique line ID |
|
FDETL |
Event Type |
Char(3) |
CRE = Create MOD = Modify |
|
FDETL |
ID |
Number(15) |
Price Change identifier |
|
FDETL |
Item |
Char(25) |
Item identifier |
|
FDETL |
Effective Date |
Date |
Effective Date of price change (YYYYMMDDHHMISS) |
|
FDETL |
Selling Unit Change Ind |
Number(1) |
Did selling unit retail change with this price event (0 = no change, 1 = changed) |
|
FDETL |
Selling Retail |
Number(20,4) |
Selling retail with price change applied |
|
FDETL |
Selling Retail UOM |
Char(4) |
Selling retail unit of measure |
|
FDETL |
Selling Retail Currency |
Char(3) |
Selling retail currency |
|
FDETL |
Multi-Unit Change Ind |
Number(1) |
Did multi unit retail change with this price event (0 = no change, 1 = changed) |
|
FDETL |
Multi-Units |
Number(12,4) |
Number Multi Units |
|
FDETL |
Multi-Unit Retail |
Number(20,4) |
Multi Unit Retail |
|
FDETL |
Multi-Unit UOM |
Char(4) |
Multi Unit Retail Unit Of Measure |
|
FDETL |
Multi-Unit Currency |
Char(3) |
Multi Unit Retail Currency |
|
FDELE |
Record Descriptor |
Char(5) |
FDELE |
File Detail Delete Marker (one per price change delete) |
FDELE |
Line ID |
Number(10) |
Unique line ID |
|
FDELE |
ID |
Number(15) |
Price Change identifier |
|
FDELE |
Item |
Char(25) |
Item identifier |
|
FTAIL |
Record Descriptor |
Char(5) |
FTAIL |
File tail marker |
FTAIL |
Line ID |
Number(10) |
Unique line ID |
|
FTAIL |
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 E-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 |
FHEAD |
Line ID |
Number(10) |
1 |
Unique line Identifier |
FHEAD |
File Type |
Char(5) |
PROMO |
Promotions |
FHEAD |
Export timestamp |
Timestamp |
System clock timestamp (YYYYMMDDHHMISS) |
|
FHEAD |
Location |
Number(10) |
Location identifier |
|
FHEAD |
Location Type |
Char(1) |
S = Store, W = Warehouse |
|
TMBPE |
Record Descriptor |
Char(5) |
TMBPE |
Promotion (transaction head) |
TMBPE |
Line ID |
Number(10) |
Unique line identifier |
|
TMBPE |
Event Type |
Char(3) |
CRE = Create MOD= Modify |
|
TPDTL |
Record Descriptor |
Char(5) |
TPDTL |
Promotion Detail Component |
TPDTL |
Line ID |
Number(10) |
Unique line identifier |
|
TPDTL |
Promo ID |
Number(10) |
Promotion identifier |
|
TPDTL |
Promo Comp ID |
Number(10) |
Promotion Component ID |
|
TPDTL |
Promo Name |
Char(160) |
Promotion Header Name |
|
TPDTL |
Promo Desc |
Char(640) |
Promotion Header Description |
|
TPDTL |
Promo Comp Desc |
Char(160) |
Promotion Component Name |
|
TPDTL |
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 |
|
TPDTL |
Promo Comp Detail ID |
Number(10) |
Promotion Component Detail identifier |
|
TPDTL |
Start Date |
Date |
Start Date of Promotion Component Detail (YYYYMMDDHH24MISS) |
|
TPDTL |
End Date |
Date |
End Date of Promotion Component Detail (YYYYMMDDHH24MISS) |
|
TPDTL |
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. |
|
TPDTL |
Discount Limit |
Number(3) |
The number of times that the promotion that can be applied to a transaction. |
|
TPDTL |
Apply Order |
Number(1) |
Application Order of the Promotion |
|
TPDTL |
Threshold ID |
Number(6) |
Threshold identifier |
|
TPDTL |
Customer Type ID |
Number(10) |
Customer Type identifier |
|
TPDTL |
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. |
|
TPDTL |
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 |
TPGRP |
Line ID |
Number(10) |
Unique line identifier |
|
TPGRP |
Group ID |
Number(10) |
Group Number |
|
TGLIST |
Record Descriptor |
Char(5) |
TGLIST |
Promotion Group List |
TGLIST |
Line ID |
Number(10) |
Unique line identifier |
|
TGLIST |
List ID |
Number(10) |
List identifier |
|
TGLIST |
Reward Application |
Number(1) |
How this reward is applied to the promotion detail. |
|
TGLIST |
Description |
Char(120) |
Description |
|
TGLIST |
Price Range Min |
Number (20,4) |
Contains price range promotion minimum valid retail value. |
|
TGLIST |
Price Range Max |
Number (20,4) |
Contains price range promotion maximum valid retail value. |
|
TLITM |
Record Descriptor |
Char(5) |
TLITM |
Promotion Group List |
TLITM |
Line ID |
Number(10) |
Unique line identifier |
|
TLITM |
Item ID |
Char(25) |
Transaction Item Identifier |
|
TPDSC |
Record Descriptor |
Char(5) |
TPDSC |
Discount Detail for List |
TPDSC |
Line ID |
Number(10) |
Unique line identifier |
|
TPDSC |
Change Type |
Number(2) |
Change Type |
|
TPDSC |
Change Amount |
Number(20,4) |
Change Amount |
|
TPDSC |
Change Currency |
Char(3) |
Change Currency |
|
TPDSC |
Change Percent |
Number(20,4) |
Change Percent |
|
TPDSC |
Change Selling UOM |
Char(4) |
Change Selling UOM |
|
TPDSC |
Qual Type |
Number(2) |
Qualification Type |
|
TPDSC |
Qual Value |
Number(2) |
Qualification Value |
|
TPDSC |
Change Duration |
Number(20,4) |
Change Duration |
|
TPISR |
Record Descriptor |
Char(5) |
TPISR |
Items in Promotion |
TPISR |
Line ID |
Number(10) |
Unique line identifier |
|
TPISR |
Item ID |
Char(25) |
TTAIL |
Transaction Item Identifier |
TPISR |
Selling Retail |
Number(20,4) |
Selling retail of the item |
|
TPISR |
Selling UOM |
Char(4) |
Selling UOM of the item |
|
TPISR |
Effective Date |
Date |
Effective Date of the selling retail - YYYYMMDDHH24MISS |
|
TPISR |
Selling Retail Currency |
Char(3) |
Selling retail currency |
|
TPISR |
Referenced Promo Detail ID |
Number(15) |
Referenced promotion detail ID. This is an optional field. |
|
TPCDT |
Record Descriptor |
Char(5) |
TPCDT |
Credit Detail |
TPCDT |
Credit Detail ID |
Number(10) |
Credit Detail ID |
|
TPCDT |
Line ID |
Number(10) |
Unique line ID |
|
TPCDT |
Credit Type |
Char(40) |
Credit Type |
|
TPCDT |
BinNumberFrom |
Number(10) |
BinNumber From |
|
TPCDT |
BinNumberTo |
Number(10) |
Bin Number To |
|
TPCDT |
Commission Rate |
Number(10) |
Commission Rate |
|
TPCDT |
Comments |
Char(160) |
Comments |
|
TTAIL |
Record Descriptor |
Char(5) |
TTAIL |
Transaction Tail |
TTAIL |
Line ID |
Number(10) |
Unique line identifier |
|
TPCIL |
Record Descriptor |
Char(5) |
TPCIL |
Cancel Item Loc |
TPCIL |
Line ID |
Number(10) |
Unique line identifier |
|
TPCIL |
Promo ID |
Number(10) |
The ID of the promotion |
|
TPCIL |
Promo Comp ID |
Number(10) |
Promotion Component ID |
|
TPCIL |
Promo Comp Detail ID |
Number(10) |
Promotion Component Detail identifier |
|
TPCIL |
Item ID |
Char(25) |
Transaction Item Identifier for item |
|
TPCIL |
Cancellation Date |
Date |
Cancellation effective date - YYYYMMDDHH24MISS |
|
FPDEL |
Record Descriptor |
Char(5) |
FPDEL |
Delete Promotion |
FPDEL |
Line ID |
Number(10) |
Unique line identifier |
|
FPDEL |
Promo ID |
Number(10) |
The ID of the promotion |
|
FPDEL |
Promo Comp ID |
Number(10) |
Promotion Component ID |
|
FPDEL |
Promo Comp Detail ID |
Number(10) |
Promotion Component Detail ID |
|
FPDEL |
Group ID |
Number(10) |
Group Number |
|
FPDEL |
List ID |
Number(10) |
List ID |
|
FPDEL |
Item ID |
Char(25) |
Transaction Item Identifier for item |
|
FTAIL |
Record Descriptor |
Char(5) |
FTAIL |
File tail marker |
FTAIL |
Line ID |
Number(10) |
Unique line identifier |
|
FTAIL |
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 E-5 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) |
<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 E-6 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 HEADER |
File Line ID |
VARCHAR(10) |
Sequential file line number |
|
FILE HEADER |
File Type Definition |
VARCHAR2(4) |
SIMT |
Identifies the File 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 |
File Line ID |
VARCHAR(10) |
Sequential file line number |
|
TRANSACTION HEADER |
Transaction Number |
NUMBER(10) |
The unique transaction reference number generated by ORXPOS/OMS |
|
TRANSACTION HEADER |
Register Number |
NUMBER(10) |
The register reference number generated by ORXPOS |
|
TRANSACTION HEADER |
Revision Number |
NUMBER(3) |
The version of the transaction being sent |
|
TRANSACTION HEADER |
Transaction Date and Time |
VARCHAR2(14) |
Date transactions were processed in ORXPOS/OMS |
|
TRANSACTION HEADER |
Transaction Type |
VARCHAR2(14) |
Transaction Type Code (for example, SALE, RETURN, SPLORD) |
|
TRANSACTION HEADER |
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 |
TRANSACTION DETAIL |
File Line ID |
VARCHAR(10) |
Sequential file line number. |
|
TRANSACTION DETAIL |
Item Sequence Number |
NUMBER(4) |
The order in which items were entered during a transaction |
|
TRANSACTION DETAIL |
Item |
VARCHAR2(25) |
ID number of the item. |
|
TRANSACTION DETAIL |
Item Type |
VARCHAR2(6) |
Type of Item sold. Can be 'ITEM', 'REF', 'GCN', 'NMITEM' |
|
TRANSACTION DETAIL |
Reference Item |
VARCHAR2(25) |
Identified sub-transaction level merchandise item. |
|
TRANSACTION DETAIL |
Non-Merchandise Item |
VARCHAR2(25) |
Identifies non-merchandise item |
|
TRANSACTION DETAIL |
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) |
|
TRANSACTION DETAIL |
Serial Number |
VARCHAR2(128) |
This is the UNIQUE_ID value from RTLOG |
|
TRANSACTION DETAIL |
Pack Indicator |
VARCHAR2(1) |
Pack indicator of item sold or returned |
|
TRANSACTION DETAIL |
Catch Weight Indicator |
VARCHAR2(1) |
Indicates if item is a catchweight item |
|
TRANSACTION DETAIL |
Item Quantity Sign |
VARCHAR2(1) |
Determines if the Total Sale Quantity is positive or negative 'P' - Positive 'N' - Negative |
|
TRANSACTION DETAIL |
Item Quantity Value |
NUMBER(20) |
Total sales value of goods sold/returned (4 implied decimal places), for example, Total Quantity * 10000 |
|
TRANSACTION DETAIL |
Standard UOM |
VARCHAR2(4) |
Standard UOM of the Item |
|
TRANSACTION DETAIL |
Selling UOM |
VARCHAR2(4) |
UOM at which this item was sold |
|
TRANSACTION DETAIL |
Wastage Type |
VARCHAR2(6) |
Wastage type of item sold or returned |
|
TRANSACTION DETAIL |
Wastage Percentage |
NUMBER(12) |
Wastage Percent*10000 (4 implied decimal places), wastage percent of item sold or returned |
|
TRANSACTION DETAIL |
Drop Ship Indicator |
VARCHAR2(1) |
N |
This will always be N for Export |
TRANSACTION DETAIL |
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' |
|
TRANSACTION DETAIL |
Reason Code |
VARCHAR2(6) |
Reason entered by cashier for some transaction types |
|
TRANSACTION DETAIL |
Sale Value |
NUMBER(20) |
Total Sales Value * 10000 (4 implied decimal places), sales value, net sales value of goods sold |
|
TRANSACTION DETAIL |
Sales Sign |
VARCHAR2(1) |
Determines if the Total Sales Value is positive or negative 'P' - Positive 'N' - Negative |
|
TRANSACTION DETAIL |
Unit Retail |
NUMBER(20,4) |
Unit retail with 4 implied decimal places |
|
TRANSACTION DETAIL |
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) |
|
TRANSACTION DETAIL |
Customer Order Number |
VARCHAR2(50) |
Customer Order Number |
|
TRANSACTION DETAIL |
Customer Order Type |
Customer order type |
||
TRANSACTION DETAIL |
Fulfillment Order Number |
VARCHAR2(50) |
Fulfillment Order Number from OMS |
|
TRANSACTION TAIL |
File Record Type Descriptor |
VARCHAR2(5) |
TTAIL |
Identifies the File Record Type |
TRANSACTION TAIL |
File Line ID |
NUMBER(10) |
Sequential file line number |
|
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 Line ID |
NUMBER(10) |
Sequential file line number |
|
FILE TAIL |
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 E-7 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 |
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 E-8 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 |
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 detail |
Uom mode |
Number(2) |
The Unit Of Measure display mode: 1 = Units, 2 = Cases |
Sequence detail |
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 E-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. |
<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 - Data File.
Pipe-delimited (|) file contains store count data for a store as shown.
Table E-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 |
The following is a sample Initial Inventory Import File:
FHEAD|5000|
FDETL|20180129235959|1|100665085|1|ItemSerialNum1234|
FDETL|201180129140000|1|100665085|1|ItemSerialNum9999|
FDETL|20180129000000|1|100665085|1||
FTAIL|
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 E-11 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||||||