Item Catalog, Descriptive Element Value
Note:- The following Query can get multiple records, since one Item can assoiciate to many Inventory Organizations.
In the following Query, I have commented the organization_id condition and given the rownum = 1 condition.
Catalog is the Flexfield. The values in these also depends upon the Set-up part of the Catalog Flexfield.
MTL_DESCR_ELEMENT_VALUES stores the descriptive element values for a specific item. When an item is associated with a particular item catalog group, one row per descriptive element (for that catalog group) is inserted into this table.
SELECT c.segment1 || ‘-‘ || a.element_value
FROM mtl_descr_element_values a
,mtl_descriptive_elements b
,mtl_item_catalog_groups c
,mtl_system_items_fvl d
WHERE a.element_name = b.element_name
AND a.inventory_item_id = d.inventory_item_id
— AND d.organization_id = ‘Your Organization ID’
AND b.item_catalog_group_id = d.item_catalog_group_id
AND b.item_catalog_group_id = c.item_catalog_group_id
AND a.element_name = b.element_name
AND a.element_name LIKE ‘%Vendor%’
AND a.inventory_item_id = ‘Your Inventory Item ID’
AND ROWNUM = 1;
In the following Query, I have commented the organization_id condition and given the rownum = 1 condition.
Catalog is the Flexfield. The values in these also depends upon the Set-up part of the Catalog Flexfield.
MTL_DESCR_ELEMENT_VALUES stores the descriptive element values for a specific item. When an item is associated with a particular item catalog group, one row per descriptive element (for that catalog group) is inserted into this table.
SELECT c.segment1 || ‘-‘ || a.element_value
FROM mtl_descr_element_values a
,mtl_descriptive_elements b
,mtl_item_catalog_groups c
,mtl_system_items_fvl d
WHERE a.element_name = b.element_name
AND a.inventory_item_id = d.inventory_item_id
— AND d.organization_id = ‘Your Organization ID’
AND b.item_catalog_group_id = d.item_catalog_group_id
AND b.item_catalog_group_id = c.item_catalog_group_id
AND a.element_name = b.element_name
AND a.element_name LIKE ‘%Vendor%’
AND a.inventory_item_id = ‘Your Inventory Item ID’
AND ROWNUM = 1;
Leave a Reply
Want to join the discussion?Feel free to contribute!