, , ,

To identify the items at Pricelist 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

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *