To identify the items at Pricelist level against active modifiers

To identify the items at Price-list 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.

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

SELECT DISTINCT qpa.list_header_id "Modifier Header ID",
qlh.COMMENTS "Modifier Name (Description)",
qpa.list_line_id "Modifier Line ID",
qll.start_date_active "Modifier start date",
qll.end_date_active "Modifier end date",
qq.qualifier_attr_value "Price List ID",
qllv.product_attr_value "Inventory Item ID",
msi.segment1 "SKU",
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,
mtl_system_items_b msi,
qp_qualifiers_v qq,
qp_list_headers_b qlh,
qp_list_lines_v qllv,
qp_modifier_summary_v qll
WHERE qpa.product_attribute = 'PRICING_ATTRIBUTE3'
AND qq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
AND qllv.product_attribute = 'PRICING_ATTRIBUTE1'
AND qllv.product_attr_value = msi.inventory_item_id
AND qllv.list_header_id = qq.qualifier_attr_value
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 msi.segment1 = 'Your Item Name'
AND SYSDATE BETWEEN qll.start_date_active AND qll.end_date_active
AND TRUNC (NVL (qllv.end_date_active, SYSDATE)) <= TRUNC (SYSDATE)
-- AND rownum <=10