Feature - Next UPC Code Assignment Enhancement
Version: 8.5 & Up
This feature improves the Next UPC Code Assignment in Item File Maintenance -> Extra Item Data Screen, where you can press F1 = Automatically Assign Next UPC Code. See sample screen below:
In the past, if the next UPC code was already in use, the user might have received a confusing prompt, such as the following:
Do You Wish To Assign Next UPC/UCC-14?
The default value was "No." If the user clicked on "Yes," the system would attempt to assign the next UPC code. If it was duplicate, it would display the same message.
This message was confusing, but now the prompt is improved and displays a more user-friendly message like the following:
UPC Code 663370999956 Is Used By XXXXXXXXXXXXXXX Assign Next Available UPC Code?
where XXXXXXXXXXXXXXX is the item number.
The default value of this prompt is "Yes." If the user just presses the ENTER key, the system will skip all duplicate UPC codes without additional warning and will assign the next available UPC code. That is, if there are still available UPC codes to assign before the maximum sequence number of 99999 is reached.
The purpose of this change is to help users to reclaim the UPC codes for obsoleted items when the UPC code sequence number reaches the maximum of 99999. This usually happens quietly and suddenly without the user realizing that a crisis is imminent. The new approach allows the user to delete the UPC code from those obsoleted items, then reset the UPC code sequence counter to 1. Since the available UPC code will not be in consecutive sequence in this case, we need to allow for the assigning of the next available UPC code in as user-friendly a manner as possible.
Note the 12-digit UPC code consists of a 6-digit vendor ID + a 5-digit sequence number + 1 checksum digit. So the above example of 663370999956 means the current sequence is now 99995 and is already approaching the maximum value of 99999. The reason we need to display this warning message (vs. just assigning the next available UPC code without warning) is to remind users to get a new vendor ID as the UPC code counter starts to approach 99999.
When the UPC code sequence reaches 99999, the system will perform a hard stop and you will see the next message:
Maximum UPC Code Counter 99999 Reached! Reset Global Setup Counter
In the previous implementation, since we did not expect that the user would use up all 99999 UPC codes, the system simply rolled over 99999 and started to assign numbers starting from 0 again.
In the new Item CSV Import utility, the system will be allowed to automatically assign the next UPC code. There is no warning message in this utility and the system just assigns the next available UPC code. If 99999 is reached, the system will simply not assign the UPC code (blank) for the item.
Reset UPC Code Counter
When your UPC code reaches 99999, you can either get a new vendor ID or remove the UPC code from the obsoleted items. Then go to Global Setup -> System -> Additional Search Key -> 10. Use UPC/SCC-14 / Alternate Item No, accept value "Y" and press ENTER. You will then see the following popup window:
Change the field "UPC Next Sequence No" to 1. If you have a new UPC Vendor No, then change the "UPC Vendor No" accordingly.
Delete Obsoleted Items UPC Codes
You can go to your obsolete items and erase the UPC code one-by-one manually. If you need to perform a mass reclaiming of obsoleted UPC codes, you can use IM -> Utility -> Change Existing Item Import. You will prepare a list of items in CSV format. Reference our change item CSV import layout. The UPC code will be in one of the Item Note 1 - 5 fields. You can use $$BLANK$$ value to clear the UPC code field.
You can also use a SQL statement to perform a mass update. The following SQL statement and procedure is listed as a reference. Note that updating Elliott's database through SQL statements is extremely dangerous. You should ask Netcellent to help.
Backup the Obsolete Items' Original UPC Code
UPDATE "IMITMFIL" SET ITEM_NOTE_2 = ITEM_NOTE_1 WHERE
ITEM_NOTE_1 LIKE '663370%' AND ITEM_QTY_ON_HAND <= 0
AND ITEM_QTY_ON_ORDER <= 0 AND ITEM_QTY_ALLOC <= 0 AND ITEM_ACTIVITY_CD = 'O'
Assuming all your UPC code start with 663370, we move ITEM_NOTE_1 (UPC code) to ITEM_NOTE_2 as a backup just in case we need to find out the original UPC code we retire.
Wipe Out Obsolete Items' UPC Code
UPDATE "IMITMFIL" SET ITEM_NOTE_1 = '' WHEREAssuming all your UPC code start with 663370.
ITEM_NOTE_1 LIKE '663370%' AND ITEM_QTY_ON_HAND <= 0
AND ITEM_QTY_ON_ORDER <= 0 AND ITEM_QTY_ALLOC <= 0 AND ITEM_ACTIVITY_CD = 'O'
Delete GTIN Codes for Obsoleted UPC Codes
DELETE FROM IMGTINFI WHERE GTIN_UCC8_12_13_CD IN
(SELECT ITEM_NOTE_2 FROM IMITMFIL WHERE ITEM_NOTE_2 LIKE '663370%')
Assuming your backup UPC code is in ITEM_NOTE_2 and they all start with 663370.
Reset the Global Setup Counter to 1 or any starting sequence number that is available.
You also need to run Global Setup -> Utilities -> System Utilities -> Generate User Index -> Item Index Key.
* Note - There is a danger of reusing UPC code from obsoleting item. Certain trading partners may reject it as duplicate (e.g. Amazon.)
Modified Programs: ITMNOTIN.PL, Recompile (IM0101, IM101OTF, IMITMNO2, IMITMNOT,) IMIMPCSV
EMK