Introduction: This query will fetch you the pricelist name, start date, end date and price of the item. The input parameter passed is item name which will get processed and provide you with the pricelist name, start date, end date, price of the item and the org id it belongs to.
How do we solve:
SELECT
qph.name,
qpl.start_date_active “Start Date”,
qpl.end_date_active “End date”,
qpl.operand “price”,
msi.segment1 “item”,
msi.organization_id “org_ID”
FROM
qp_list_headers qph,
qp_list_lines qpl,
qp_pricing_attributes qpa,
mtl_system_items_b msi
WHERE
1 = 1
AND 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.inventory_item_id = qpa.product_attr_value
AND qpl.start_date_active < SYSDATE
AND (
qpl.end_date_active > SYSDATE
OR qpl.end_date_active IS NULL
)
AND msi.segment1 = p_item_name;
Conclusion: this query will help us find the pricelist name, start date, end date and price of the item
