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'))