What are the attribute details on mtl_system_items table

One of our readers recently asked the above question and I thought of giving a quick answer 🙂

Attribute columns on this inventory table are used as additional information columns known as Descriptive flexfields. The reason they do not have any specific column name is because each implementation of Oracle Apps can customize as to what is stored in this DFF.

To find out what is stored in these attribute columns…
1. find out the different dff defined on this table, how?

Easy way is to just query the fnd_descriptive_flexs_vl view and in the base_table column provide the desired table you wish to query for. Once I find run the query I get the below table that lists all the defined DFF on this table

Flex Title Code base
Items MTL_SYSTEM_ITEMS MTL_SYSTEM_ITEMS_B
Item Order Attributes BOM_ITEM_ORDER_ATTRIBUTES MTL_SYSTEM_ITEMS_B
Item Shipping Attributes BOM_ITEM_SHIPPING_ATTRIBUTES MTL_SYSTEM_ITEMS_B
JG_MTL_SYSTEM_ITEMS JG_MTL_SYSTEM_ITEMS MTL_SYSTEM_ITEMS_B
Master Items EGO_MASTER_ITEMS MTL_SYSTEM_ITEMS_B

2. Once you know the DFF title, go to System Administrator -> Application -> FlexField -> Descriptive -> Segments

Query the form and enter the Flex title value from the above table and enter in the title field of this form. Click on the Segments and there you see all the End user column names and the attribute associations.

But there is another easier way to find out the attribute and dff column names associations.

You can query the view fnd_descr_flex_col_usage_vl as demonstrated in the below query.

SELECT
dfc.end_user_column_name user_column_name,
dfc.column_seq_num column_sequence,
dfc.application_column_name table_column_name,
dfc.flex_value_set_id,
dff.application_table_name base_table,
dff.descriptive_flexfield_name flex_code,
dff.title flex_name
FROM fnd_descr_flex_col_usage_vl dfc,
fnd_descriptive_flexs_vl dff
WHERE dff.descriptive_flexfield_name = dfc.descriptive_flexfield_name
and dff.descriptive_flexfield_name =

I get the below list of columns and attributes

User Column Name Column Sequence Table Column Name
Drop Shipment 10 ATTRIBUTE10
Country of Origin 10 ATTRIBUTE1
Invoice UOM 15 ATTRIBUTE15
Harmonized Tarriff Code 20 ATTRIBUTE2
Business Class (y/n) 30 ATTRIBUTE3
Mac_ID Required 40 ATTRIBUTE4
Parent Item for DP 50 ATTRIBUTE14
ECCN 60 ATTRIBUTE9
CCATS 70 ATTRIBUTE12
Version 80 ATTRIBUTE11
Legacy Part Number 90 ATTRIBUTE6

Later I will cover some tricks on query the Key and Descriptive flexfields
Have a nice day!