May 8, 2007

Design Considerations for item coding

Read my White Paper on this subject in IT Tool Box

Intelligent coding of inventory items is one of the most important activities in an ERP implementation for most of the manufacturing organizations. As they approach ERP, most of the organizations find that their inventory items have been wrongly coded or not coded at all. Further access control issues related to creation of new items are also identified. Many a times these problems are observed relating to Raw Material and Consumable item rather than finished product items. In addition they also find that the same items have been classified differently for different requirements.
The above issues lead to consequences including lack of control on the inventory items, wrong valuation of inventory, item duplication, wrong / mutually exclusive classification, organization receiving wrong picture of their inventory, lack of real time accurate inventory data etc. Most of these consequences play havoc with the downstream activities including production planning, inventory planning, MRP, available to promise, issues with tax authority etc to name a few.
The main advantage of ERP implementation, if properly done, is that it forces the organization to take a long and hard look at the way the inventory items enter, move through and move out of the organization leading to manifold benefits for all the stake holders in the organization.

Objective of this document

This document aims to provide the ERP Consultant with a set of design considerations and check points to ensure an optimal inventory coding for the purpose of ERP implementation.

Design considerations in inventory coding

1. Landscape analysis
Before undertaking the exercise, it is beneficial to look at the organization / project landscape. The key points include, the number of ERPs being used in the organization and the commonality of inventory items being used by these packages, the inventory coding constraints imposed by different ERP packages, the inventory reports provided by different ERP packages, the number of external applications presently available in the organization, the flow of inventory data between these applications and the corresponding constraints. Also consider the future ERP roadmap of the organization (are they planning any new ERP implementation for example) and the expected customization requirements.
2. Ensure that you are only a facilitator
At the outset it must be made clear to the organization that you are going to act as a facilitator in helping the organization to decide on the item coding. It is very important to set this expectation right at the beginning since there is a possibility that the organization may expect you to execute the inventory coding for them. However as a consultant you may not have a clear picture of the inventory related activities / transactions in the organization. Any coding of inventory without a clear picture of the above will lead to a sub-optimal coding.
3. Start early.
As discussed in the introduction to this article, there are substantial issues related to inventory coding in most of the organizations. Along with this, different part of the inventory are owned by different set of stake holders who have their own internal coding conventions. Added to this, if you have complex coding conventions and large item master it spells a potentially risky situation from the implementation timeline point of view. Hence it is better to sensitize the organization of the importance of inventory coding as early as the project boot camp and follow up the progress in all the senior management meetings.
4. Involve senior management from the beginning.
There is a tendency in the organization to leave the responsibility of item coding to the middle management personnel in the materials management department. Even though they are the drivers of the whole exercise, they may not have the authority to demand output from the line departments. It is very much necessary that top management is involved in this exercise from day one to ensure success of the implementation. If possible, consider inventory coding as a sub project in the exercise of ERP implementation. The consultant must remember that while inventory coding is not a component of the standard ERP implementation plan, this is a critical activity which can derail the project schedules.
5. Involve Finance Department
In many organizations there is a tendency to look at inventory coding as a 'materials management' activity. However finance department is the key user of most of the granular reports related to inventory and consumption. Inventory is one of the key components of 'current assets' in many organization and accurate valuation of inventory has significant balance sheet and P&L implications. Finance department need to be extensively consulted before the commencement of the coding exercise. They should also be one of the agencies signing off on any deliverable related to the inventory coding program.
6. Start with the reports:
As a first step towards the inventory coding, a consultant need to take a look at the various management reports being used by the organization. There are multiple reports that an organizations top management requires to assess the financial health of the organization. These include Stock Quantity Ledger, Stock Transaction report, ABC Analysis Report, Stock Valuation report, Raw Material / Consumables Consumption report, Cost Sheet etc. A consultant need to be familiar with these reports especially the granularity of reporting, and the categorization of the data required in these reports. Some of the common categories include item type (RM, SFM, FG etc), purchase category (Local / Imported) etc.
7. What are the details required for querying the item?
Suppose the item required is 'Raw material item, diameter of 0.5 inch, used for customer 1, Imported, purchased from supplier 1 and used for a specific category of FG'. Here each of the details (known as segments in certain ERP product terminology) required to be captured for an item.
8. What are the details required to be captured in the inventory code?
In database terminology this process is known as normalization. Analyse the data in step 3 and decide which of them need to be captured in the inventory code and which of the details can be captured as attributes to the item. This calls for a detailed knowledge of the product and its features. In the above example, you may have to capture the value of Raw material segment, diameter segment and a unique 3-4 character running serial number need to be captured in the item code where as the other details could be moved to the attributes.
9. How many segments do the product allow?
Packages like Oracle allows multiple segments to be used in inventory coding. In the above example you can use segment 1 for item type (RM, FG, SFM etc), Segment 2 for diameter (0.25 inch, 0.5 inch etc) and segment 3 can be used for the running serial number. The advantage of this segmentation is that you can set default rules as well as validation rules to ensure that wrong combinations are used. This is not the normal practice of inventory coding and hence will call for increased training and communication effort from your side to familiarize the users to this approach. In the above example, an item may look like 'RM-50-001'
However some packages do not allow multiple segmentation. In this case, you have to ensure manual rules to delineate the segments. While this method is intuitive, it will call for superior training to ensure that user errors are kept to a minimum. This is because, in this method it is difficult to set the kind of default validation rules that was set in case of multiple segments. In this method the above item will read as 'RM50001'.
A note of caution if you are using both Oracle discrete and Oracle process manufacturing in the same implementation. While Discrete manufacturing allows multiple segments, OPM allows only a single segment for item code. Most of the time a single user handles both discrete and process inventory. In this scenario, it is better to go in for single segment for both discrete and process inventory.
10. Use numbers instead of alphabets while coding the inventory.
This is important especially if you have a large inventory master. Numeric coding will ensure faster data access enabling faster query fetch and report output. This is related to the internal characteristics of most of the databases currently available.
11. Keep it simple
And finally keep your inventory coding optimal, intuitive and as close as possible to the way in which inventory is presently created in the organization. This will ensure quicker training and widespread acceptability to the ERP implementation.


Along with realistic design of Chart of Accounts, the appropriate coding of inventory items is one of the key parameters which decide on the success of an ERP implementation. Properly coded inventory can reap windfall benefits to the organization in providing accurate real time inventory data to the organization. The potential benefits to the organization include accurate profitability data, increased inventory turnover, quick ratio matching current ratio, increase in cash flow and decrease in inventory carrying cost. Moreover inventory optimization helps in de-bottlenecking of the production flow, freeing up additional space and optimum production and capacity planning exercise.
Considering all the significant expected benefits, it is very important for the organization to devote superior talent, planning and time to the exercise of inventory coding. Following the above guidelines can go a long way in ensuring minimal surprises while undertaking the above exercise.


bableo said...

Thanks for your details and explanations..I want more information from your side..I Am working in erp development companies in chennaishould you need for any other clarification please call in this number.044-6565 6523.

Ramaswamy V Krishnamurti said...

Hello Bableo
I tried calling the number that you had given. It went to your company reception. Pl. call me back or update the blog with further contact details.