Query for Modifiers and Qualifiers in Oracle-R12 Pricing
Price List :
- Price lists are essential for ordering products because each item entered on an order must have a price.
- Each price list contains basic list information and one or more pricing lines, price breaks, pricing attributes, qualifiers, and secondary price lists.
Modifiers:
- Using modifiers, you can set up price adjustments, benefits, freight and special charges, and promotional limits to control spending or usage. You can define simple discounts and surcharges as well as more advanced deals and promotions.
- Modifiers can adjust net price either up or down
Qualifiers:
- Qualifiers are used in sync with Price Lists and Modifiers. Qualifiers can be set According to the business needs.
- A qualifier consists of one or more conditions that define eligibility for a discount, promotion, or surcharge
Query for Modifiers and Qualifiers
SELECT a.NAME "Modifier Name",
b.list_line_no "Modifier Line No",
a.description "Modifier Description",
a.comments,
b.list_line_type_code,
d.qualifier_context,
a.orig_org_id,
a.active_flag,
b.start_date_active,
b.end_date_active,
a.list_type_code,
b.modifier_level_code,
b.organization_id,
f.order_number,
b.pricing_phase_id,
b.list_line_no,
d.header_quals_exist_flag,
d.qualifier_datatype,
d.qualifier_attribute,
d.segment_id,
d.CONTEXT,
d.qualifier_grouping_no
FROM qp_list_headers_all a,
qp_list_lines b,
qp_pricing_attributes c,
qp_qualifiers d,
qp_qualifier_rules qqr,
oe_order_lines_all e,
oe_order_headers_all f
WHERE a.list_header_id = b.list_header_id
AND b.list_header_id = c.list_header_id
AND b.list_line_id = d.list_line_id(+)
AND qqr.qualifier_rule_id(+) = d.created_from_rule_id
AND e.price_list_id(+) = b.list_header_id
AND e.header_id = f.header_id(+)
AND b.list_line_id = c.list_line_id(+)
AND b.pricing_phase_id > 1
AND a.name=P_Price_list_name
