magritton
9/25/2014 - 12:30 PM

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

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

CQ Item Modifiers Detail2.sql
content_copyfile_download
  select qlh.name modifier_list_number
       , qlh.description name
       , qlh.comments description
       , qlh.list_type_code
       , qlh.automatic_flag
       , qlh.active_flag
       , qlh.start_date_active
       , qlh.end_date_active
       , qlh.currency_code
       , qq.qualifier_grouping_no
       , 'Customer' qualifier_context
       , 'Customer Name' qualifier_attribute
       , qq.qualifier_precedence
       , qq.comparison_operator_code
       , hp.party_name customer_name
       , hca.account_number customer_number
       , qq.active_flag
       , qq.start_date_active
       , qq.end_date_active
       , qq.qualifier_attr_value
  from qp_list_headers_vl qlh
     , qp_qualifiers qq
     , hz_cust_accounts hca
     , hz_parties hp
  where 1=1
    and qlh.list_type_code in ('DLT', 'SLT', 'CHARGES', 'PRO')
    and qlh.source_system_code = 'QP'
    and qlh.active_flag = 'Y'
    and nvl(qlh.start_date_active, sysdate-1) <= sysdate
    and nvl(qlh.end_date_active, sysdate+1) >= sysdate
    and qlh.name like 'KUN%'
    and qq.list_header_id = qlh.list_header_id
    and qq.list_line_id = -1
    and qq.active_flag = 'Y'
    and nvl(qq.start_date_active, sysdate-1) <= sysdate
    and nvl(qq.end_date_active, sysdate+1) >= sysdate
    and qq.qualifier_context = 'CUSTOMER' -- Customer
    and qq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE2' -- Customer Name
    and hca.cust_account_id = qq.qualifier_attr_value
    and hp.party_id = hca.party_id
CQ Item Modifiers Detail.sql
content_copyfile_download
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'))