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

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'

Get the Modifier details of particular item (only at the Line Level)

Get the Modifier details of particular item (only at the Line Level) :-
------------------------------------------------------------------------------

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:-
-------------------------------------------------------------------------------------

Note:- This is applicable only if the Item Number directly given at the Modifier Line Level.

SELECT distinct qlh.comments "Modifier Name"
,qqv.rule_name "Qualifier Group"
,ou.name "Store Id"
--,qlhv.name "Price list name"
,qms.product_attr_value "SKU"
,qms.list_line_no "Modifier Line No"
,qms.start_date_active "Start Date"
,qms.end_date_active "End Date"
,qms.arithmetic_operator_type "Application Method"
,qms.operand "Value"
,qms.product_precedence "Precedence"
,qms.incompatibility_grp "Incompatibility Group"
,qms.pricing_group_sequence "Bucket"
FROM
qp_modifier_summary_v qms
, qp_list_headers_b qlh
,qp_list_headers_tl qlt
,qp_qualifiers_v qqv
,mtl_system_items_b msi
,hr_all_organization_units ou
--,qp_list_headers_v qlhv
WHERE
qlh.list_header_id = qms.list_header_id
--and qms.list_header_id=qlhv.list_header_id
and qlh.list_header_id =qqv.list_header_id
and to_char(msi.inventory_item_id)=qms.product_attr_val
AND ou.organization_id = msi.organization_id
and to_char(ou.organization_id)= qqv.qualifier_attr_value
and sysdate between qms.start_date_active and qms.end_date_active
and qlt.LIST_HEADER_ID=qlh.LIST_HEADER_ID
AND exists
(select 1
from mtl_system_items_b a
where a.organization_id=(SELECT UNIQUE master_organization_id
FROM mtl_parameters)
and to_char(a.inventory_item_id)=qms.product_attr_val
and a.segment1 in('Your Item Name'))