Inventory Flexfield Structure Definition for a Food Processing and Distribution Company

Introduction - This real world case study on proposed item structure for a Food Processing and Distribution Company was based on the requirements of business, gathered through discussions during the implmentation project, as well as recommendations submitted by consultants.

1. Objective

The Decision on Inventory Flex fields Structure was taken based on achieving the following Objective:

Ø Item code structure across all Product lines & Products is required to be uniform;
Ø Item code should be simple and short;
Ø Item code numbering should be driven by a simple logic to avoid deciphering the codes by field staff;
Ø Item code should be independent of the personal view of the person defining the item;
Ø Item code should not be dependent on either supplier or customer codes;
Ø Each item should have code and a description to identify the item uniquely;
Ø Code should not be repeated in description and vice-versa;
Ø Expiry date and location of the item should be identified
Ø All items should be properly classified in a logical manner, so that MIS reports can be generated; and
Ø All existing reporting requirements are met, in addition to the reports available in Oracle Inventory.

In Oracle Inventory, an Item should have a System Flex field; in order to take advantage of the Oracle Apps, features, it is also recommended to use Category Sets, Lot number control and Locator to define an Item.

Based on the above, the following item code structure was designed.

2. System Item Flexfield

The System Item Flex field is used to define the Item Code through which an Item in the Inventory is identified uniquely. For the client's business, the System Item Flex field will be:

No. Of segment = 1
Segment Name = Item

Size = 6 Numeric

The segment will have serial no starting from 000001 to 999999. This gives flexibility to have 999999 items in the company. To ensure that items are numbered in a logical manner, range of serial no. will be allocated, so that serial no. can be used only from the range.

As a next step, Description of the item has to be entered to save the item in the system. It is proposed that the name of the supplier/brand, existing description and the package size shall be entered in description, eg. ABC Supplier (Brand), Mod Chicken (Description) and 900 grams (package size) so the description would be 'ABC Supplier Mod Chicken 900 Grams'.

3.Category Set

A Category is a logical major classification of items that have similar characteristics. A Category Set is a set of distinct categories in which an Item can be grouped/classified. E.g. one grouping or classification can be based on “Buying”; another grouping or classification for the same Items can be based on the Physical Inventory attributes. The flexibility of having multiple category sets allows reporting and query on items in a way that best suits business needs.

For our common business requirements across all departments, an Inventory Category Set will be created at the beginning with the following four segments:

*The segment size has been considered keeping in view that most of the business requirements are met. As these 6 segments are required to appear in Reports, GRNs, PO & Invoices hence, having longer size would mean that on the reports, other information might not fit in 80-column Or 128 column Stationery.. However, in system their full description can be entered and maintained. It is also advisable that these categories can be numbered properly to avoid extending the report size.

The values for the above 6 segments will have to be first updated and combinations also created. This will then be available as List of Values (LOV). Every time an item is created, this default category set structure will be attached to the Item, and the values for each of the segments can be selected from LOV. Few items have been classified under four segments:

Once a Category set is attached to an item, reports on its segments i.e. Focus, Brand, Category, Base Product, Product & Size can be generated. For an existing item, a Category set can be delineated, if required, and a new category set attached. Last change audit trail will be available in the system. A new category set can also be created or an existing Category set be disabled.

4. Lot Control

Lot control feature can be utilized to capture the expiry of items in the inventory. It plays a crucial role in the organizations, which are into Food Processing, Pharmaceutical and products that get expired due to elapse of time. It is proposed that the lot control feature can be enabled and used for all products. Basically, system will require a lot number and expiry date to be entered by the user to complete the transaction. Transactions are as follows:

A. Material Receipts
B. Transfer goods from one location to another location and
C. Material Issues

In case of material transfer between locations, user has to choose the lot number, which is already assigned to the items. This will eliminate multiple lot numbers assigned to single item in different locations for a better control. To ease the operations, it is proposed that the ETA date (Expected Time of Arrival) shall be entered as lot number for the item and the user has to input expiry date of the item. Preferably, these lot numbers should be pasted on the Pallets so that the inventory clerk can easily identify the location of the goods.

5. Locator Structure (To be used in Future)

Locators are used to identify physical location where the item is actually stored in the Warehouse/Store. Locator can track item quantity. An Item can also be restricted to a specific locator or a locator can be dynamically assigned to the item on receipt.


In order to use this available feature for better managing the inventory, we need to –
Ø Design the locator storage system based on the warehouse space and use structure for storing items.
Ø Paint the palettes using standard primary colors.
Ø Assign numbers to all the palettes.
Ø Define all the locator addresses in the Inventory system.
Ø Attach item to a locator.

It is proposed to use a Locator segment of Colour & Number, for e.g. R120 would mean RED colour palette number 120. This detail will help in tracking the item during transfers, issues and also during taking physical stock of the items. Proposed locator address structure is given below:

It is proposed that the locator will be defined in the system but the users will be using it once they are well versed with the system. The locators segment have to entered on following transactions:

A. Material Receipt to Warehouses
B. Transfer of goods from one location to another location
C. Movement of Goods within the warehouse
D. Issue of materials to Customers and Vans
E. Receipt of materials from Van and Customers

6. Benefits of the Proposed Item Structure

a. At the time of Item creation, the only User logic built into the item code is the Sub Division to which the item belongs.
b. The Item Code is short with one segment and is uniform across all Products.
c. Company can have up to 999999 items.
d. The Item Code is not dependent on the item code of the Vendor or Customer and is unique to the Client.
e. The probability of making duplicate item code is nil since the Item Code is unique in inventory. f. Items have been classified in an Inventory Category Set with six segments Type, Product Line, Brand and Product. In the existing system the classification is more or less the same. Focus, Brand, Category, Base Product, Product & Size will be available as List of Values for selection, so that typographical errors are avoided. However, it is important that the selection is correct to avoid changing the category subsequently.
g. Inventory Reports can be generated on any of the segments i.e. Item Code, Description, Focus, Brand, Category, Base Product, Product & Size to sort the items in Inventory Organization.
h. An Item can be attached with lot numbers & Locator to identify the location and expiry dates.
i. Stocks can be maintained Expiry date wise