To identify the items at Item Category level against active modifiers

To identify the items at Item Category level against active modifiers :-
--------------------------------------------------------------------------------

When we are define Modifier in the OM module. We can define Modifier at the particular Item or all the Items in the Price List/Item Category etc.

Note:- Item Category is the Flex-field in the Inventory Module. In the Master Item define, we choose what item belongs to which Item Category.

The following query will give the Modifier Name and other details of Particular Item of Item Category:-
----------------------------------------------------------------------------------------

SELECT DISTINCT -- qpa.list_header_id "Modifier Header ID",
qpa.list_line_id "Modifier Line Number",
qlh.COMMENTS "Modifier Name (Description)",
qll.start_date_active "Modifier Start Date",
qll.end_date_active "Modifier End Date",
b.segment1 "SKU",
b.inventory_item_id "Inventory Item ID",
qll.arithmetic_operator_type "Application Method",
qll.operand "Value",
qll.product_precedence "Precedence",
qll.incompatibility_grp "Incompatibility Group",
qll.pricing_group_sequence "Bucket"
FROM qp_pricing_attributes qpa,
qp_qualifiers_v qq,
qp_list_headers_b qlh,
qp_modifier_summary_v qll,
mtl_item_catalog_groups a,
mtl_system_items_b b,
mtl_descr_element_values c
--qp_qualifiers_v q
WHERE b.item_catalog_group_id = a.item_catalog_group_id
AND b.inventory_item_id = c.inventory_item_id
AND b.organization_id =
(SELECT UNIQUE master_organization_id
FROM mtl_parameters)
AND c.element_sequence IN ('20')
AND qq.list_header_id = qpa.list_header_id
AND qq.list_line_id = qpa.list_line_id
AND qq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE35'
AND qq.qualifier_attr_value =
a.segment1 || '-' || c.element_value
AND qpa.product_attribute = 'PRICING_ATTRIBUTE3'
AND qlh.list_header_id = qq.list_header_id
AND qpa.list_header_id = qll.list_header_id
AND qpa.list_line_id = qll.list_line_id
AND qll.list_header_id = qlh.list_header_id
AND qlh.active_flag = 'Y'
AND SYSDATE BETWEEN qll.start_date_active AND qll.end_date_active
AND b.segment1 = 'Your Item Name'