Item Catalog, Descriptive Element Value

Catalog and Descriptive element value on the Inventory Item ID of the Item:-
-----------------------------------------------------------------------------------------

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;

Read More

Modifier Header ID based on the Modifier Description

INV Modifier Header ID based on the Modifier Description:-
---------------------------------------------------------------------

The following Query would get the Modifier Header ID based on the Description given for that Modifier. In many places we use the Modifier Header ID to get other values related to that Modifier.

SELECT list_header_id "Modifier Header ID"
FROM qp_list_headers_b
WHERE trim(SUBSTR (comments, 1, 240)) = trim('ALLORACLETECH Description') AND ROWNUM = 1;

Get the Modifier Description Based on the Modifier ID

The following Query will get the Modifier Description given for the Modifier based on the Modifier Header ID.

SELECT SUBSTR (comments, 1, 240) "Modifier Description"
FROM qp_list_headers_b
WHERE list_header_id = 'Your modifier header ID' AND ROWNUM = 1;

Read More