National UPC File

NOTE: The Position column applies only to text file import and export processes. The Position column is not applicable for Excel file imports and exports.

Data Element Name

Format

Position

Notes

Record Identification Code

PIC X(2)

1-2

Value = "A1"

Record Sequence Number

PIC 9(6)

3-8

Value = "000001"

File Create Date

PIC 9(8)

9-16

CCYYMMDD

File Create Time

PIC 9(6)

17-22

HHMMSS

File Format Version

PIC 9(2)

23-24

Value = "04" X9.93:2007

File Name

PIC X(25)

25-49

Value = "UPC/PLU STORE FILE"

File Type

PIC X(8)

50-57

Values = "NEW"

File Sequence Number

PIC 9(4)

58-61

Value = "0001"

State Identifier

PIC X(2)

62-63

Value = "CN"

Receiving Institution Identification Code

PIC 9(11)

64-74

Not used

Data Element Name

Format

Position

Notes

Record Identification Code

PIC X(2)

1-2

Value = "D4"

Record Sequence Number

PIC 9(6)

3-8

Starting at "000002" and incrementing by one for each detail record

Leading zeros are not required when importing an Excel file.

Message Type Identifier

PIC 9(4)

9-12

Value = "1344"

UPC/PLU

PIC 9(17)

13-29

Right Justified Zero Filled

Leading zeros are not required when importing an Excel file.

To indicate the value is a PLU it must meet both of the following:

• It must be seventeen (17) digits for both Excel and text files. For Excel files, if this value is not seventeen (17) digits, the record will be set as Not PLU by default.

• The first digit is a PLU Indicator. If it is one (1), then the record will be treated as a PLU.

Item Description

PIC X(50)

30-79

Product Name added to the system only if entry is a new product (no overwriting)

The field allows alphabetic, numeric, and special characters. The maximum number of characters that can be entered in the field is fifty (50) characters. 

The field is overwritten when importing an Excel file. 

Category Code

PIC 9(2)

80-81

Right Justified Zero Filled

EBTFOODCATSUBCATEGORY = FOODCATEGORYCD

Leading zeros are not required when importing an Excel file.

Category Description

PIC X (50)

82-131

EBTFOODCATSUBCATEGORY = FOODCATDESCRIPTION

Subcategory Code

PIC 9(3)

132-134

Right Justified Zero Filled

EBTFOODCATSUBCATEGORY = FOODCATEGORYCD

Leading zeros are not required when importing an Excel file.

Subcategory Description

PIC X(50)

135-184

EBTFOODCATSUBCATEGORY - FOODSUBCATDESCRIPTION

Unit of Measure

PIC X(10)

185-194

Values should match the three (3) character values in the EXTERNALID column within the REFERENCEDICTIONARY table of the database where the value of the CATEGORY column equals "EBTJPMCATSUBCATUOM".

For more information on food categories, subcategories, and units of measure, see the Category/Subcategory Maintenance topic.

Package Size

PIC 9(3)V99

195-199

Text files do not use decimals. Instead, the last two (2) digits represent the decimal. For example, 00100 represents 1, and 00050 represents .5.

Decimals are required in Excel import files and display in export files.

Leading zeros are not required when importing an Excel file.

Benefit Quantity

PIC 9(3)V99

200-204

Text files do not use decimals. Instead, the last two (2) digits represent the decimal. For example, 00100 represents 1, and 00050 represents .5.

Decimals are required in Excel import files and display in export files.

Leading zeros are not required when importing an Excel file.

Benefit Unit

PIC X(50)

205-254

Values should match the three (3) character values in the EXTERNALID column within the REFERENCEDICTIONARY table of the database where the value of the CATEGORY column equals "EBTNATUPCBENUNITS".

Item Price

PIC 9(4)V99

255-260

Leave 6 blank spaces in the text file or a "0" value in the Excel column when entering data for upload.

Excel accepts values with 2 decimal places.

Price Type

PIC X(2)

261-262

Values should match the values in the EXTERNALID column within the REFERENCEDICTIONARY table of the database where the value of the CATEGORY column equals "EBTNATUPCPRICETYPE".

Values: "03" – valid for Category 19 products only (both UPCs and PLUs); all others products must be "00".

Upon downloading a file, the system shall always populate the Price Type field with "03" for Category 19 PLU or UPCs and "00" for UPCs in any other Category besides Category 19.

Leading zeros are not required when importing an Excel file.

Value is saved to the PRICETYPE column within the EBTAUTHORIZEDUPC table of the database when file is imported.

Card Acceptor Identification Code

PIC X(15)

263-277

Leave 15 blank spaces in the text file or a blank value in the Excel column when entering data for upload.

Date Effective

PIC 9(8)

278-285

CCYYMMDD format for text files. MM/DD/CCYY format for Excel files.

If no date is entered, effective upon receipt

Date End

PIC 9(8)

286-293

CCYYMMDD format for text files. MM/DD/CCYY format for Excel files.

UPC/PLU Data Length

PIC 9(2)

294-295

Indicates the significant digits in the UPC or PLU

Valid values for UPC:

• 12

• 13

• 14

Valid values for PLU, which include the check digit:

• 05

• 06

Value is saved to the UPCPLUDATALENGTH column within the EBTAUTHORIZEDUPC table of the database when file is imported.

Purchase Indicator

PIC 9(1)

296

Values either:

"1" for Yes

"0" for No

Rebate Item

PIC 9(1)

297

Values either:

"1" for Yes

"0" for No

PLUs must be "0"

UPCs can be "0" or "1"

Short Product Description

PIC X(24)

298-321

This field is limited to twenty-four (24) characters, including alpha, numeric, special characters, and spacing enabled.

User must enter data in the Excel file or leave twenty-four (24) blank spaces in the text file order for system to accept the uploaded file.

APL Types

PIC 9(1),PIC 9(1)

Not valid for Excel file

APL Types apply only to Excel files and are not included in a text file.

Allows Comma(,) separated APL Type ID values.

For example:

• "1,2"

OR

• "1"

OR

• "2"

Values:

• "1" for store brand (ALL)

• "2" for national brand (Commissary)

APL Types Description

PIC X(135), PIC X(135)

Not valid for Excel file

APL Types apply only to Excel files and are not included in a text file.

Comma-separated (,) APL Type Descriptions. These are not used for Import, and are only added for the purpose of referencing APL Type IDs.

Data Element Name

Format

Position

Notes

Record Identification Code

PIC X(2)

1-2

Value = "D6"

Record Sequence Number

PIC 9(6)

3-8

Starting at "000001" and incrementing by one for each detail record

Message Type Identifier

PIC 9(4)

9-12

Value = "1344"

Category Code

PIC 9(2)

13-14

Right Justified Zero Filled

EBTFOODCATSUBCATEGORY = FOODCATEGORYCD

Category Description

PIC X(50)

15-64

EBTFOODCATSUBCATEGORY = FOODCATDESCRIPTION

Subcategory Code

PIC 9(3)

65-67

Right Justified Zero Filled

EBTFOODCATSUBCATEGORY = FOODSUBCATEGORYCD

Subcategory Description

PIC X(50)

68-117

EBTFOODCATSUBCATEGORY = FOODSUBCATDESCRIPTION

Subcategory Short Description

PIC X(24)

118-141

SUBCATSHORTDESCRIPTION

Unit of Measure

PIC X(50)

142-191

Use the following query to find the associated Unit of Measure for a Category/Subcategory within the database:

SELECT FOODCATEGORYCD, FOODCATDESCRIPTION, FOODSUBCATEGORYCD, FOODSUBCATDESCRIPTION, UNITOFMEASURECD FROM EBTFOODCATSUBCATEGORY

Date, Effective

PIC 9(8)

192-199

CCYYMMDD

If no date is entered, effective upon receipt

Date, End

PIC 9(8)

200-207

CCYYMMDD

(Unknown)

PIC9

208

·

Data Element Name

Format

Position

Notes

Record Identification Code

PIC X(2)

1-2

Value = "Z1"

Record Sequence Number

PIC 9(6)

3-8

Value of "000001" and incrementing by one for each detail record

File Create Date

PIC 9(8)

9-16

CCYYMMDD

File Create Time

PIC 9(6)

17-22

HHMMSS

File Format Version

PIC 9(2)

23-24

Value = "04" X9.93:2007

Counts, Records

PIC 9(7)

25-31

Total count of Records in the file

Count, Adds

PIC 9(7)

32-38

Total count of Adds in the file

Count, Changes

PIC 9(7)

39-45

Total count of Changes in the file

Count, Deletes

PIC 9(7)

46-52

Total count of Deletes in the file

Count, Replacements

PIC 9(7)

53-59

Total count of Replacements in the file