To Get Active Price list,Modifiers,

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=22

and to_char(a.inventory_item_id)=qms.product_attr_val

and a.segment1 in('61054243'))



Active Price list query:



SELECT msi.segment1 "Sku",

msi.description "Sku Description",

qph.name "Price list name",

qpl.START_DATE_ACTIVE "Start Date",

qpl.END_DATE_ACTIVE "End date"

---qph.list_header_id

FROM

qp_list_headers_v qph,

qp.qp_list_lines qpl,

qp_pricing_attributes qpa,

mtl_system_items_b msi

WHERE

---source_lang = 'US'

qph.list_header_id = qpl.list_header_id

AND qpa.list_line_id = qpl.list_line_id

AND qpa.list_header_id = qph.list_header_id

AND qpa.product_attribute_context = 'ITEM'

AND product_attribute = 'PRICING_ATTRIBUTE1'

AND msi.organization_id = 22

AND msi.inventory_item_id = qpa.product_attr_value

--AND qpl.end_date_active IS NULL

--AND qph.LANGUAGE = 'US'

and ((sysdate between qpl.START_DATE_ACTIVE

and qpl.END_DATE_ACTIVE)

or (qpl.START_DATE_ACTIVE < sysdate

and qpl.END_DATE_ACTIVE is null ))

AND msi.segment1 in ('61054243')





Active Modifiers:



SELECT distinct qlh.comments "Modifier Name"

,qms.start_date_active "Start Date"

,qms.end_date_active "End Date"

,qlh.active_flag

,qlh.automatic_flag

FROM

qp_modifier_summary_v qms,

qp_list_headers_b qlh

WHERE

qlh.list_header_id = qms.list_header_id

and sysdate between qms.start_date_active and qms.end_date_active

and trunc(qms.end_date_active) <'12-JAN-2009'