Price List: Query the price of an item

Price list querying for getting the price of an item is a common task you’ll have to do if pricing is done in Oracle fusion. In this post we will share the query you can use to fetch the pricelist of an item and corresponding charges, But before that, let’s have a look at various tables used to store the data and their usages:

1. qp_price_list_items: Table to store the Item and Pricelist relationship.
2. qp_price_lists_all_b: Table to store the price list names and more importantly start and end date of a price list
3. qp_price_lists_tl: This table maintains the name of the price list
4. qp_price_list_charges: To get the various charges of an item
5. qp_charge_definitions_tl: This table stores the name of various charges.

let’s cut to the chase now and see the query. You can use it as per your requirement.

By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...

This query will provide you following fields:
1. Item Number
2. Pricelist Name
3. Price List Description
4. Charge Name
5. Base Price

If you look at the query closely, you’ll see that qp_price_list_items table gives you all the pricelist attached to the item. Now using the pricelist, you can query the qp_price_list_charges table to find out the charge.

select esi.item_number,
qplt.name price_list_name,
qplt.description price_list_desc,
qcdl.name Charge_Name,
qplc.base_price
from egp_system_items_b esi,
inv_org_parameters iop,
qp_price_list_items qpli ,
qp_price_lists_tl qplt,
qp_price_lists_all_b qplab,
qp_price_list_charges qplc,
qp_charge_definitions_tl qcdl
where esi.organization_id = iop.organization_id
AND iop.organization_code= <Your Item Master Org Name>
AND qpli.item_id =
esi.inventory_item_id
AND qpli.item_level_code = ‘ITEM’
AND qpli.PRICE_LIST_ITEM_ID = qplc.parent_entity_id
AND qplab.price_list_id =
qplt.price_list_id
AND SYSDATE BETWEEN qplab.start_date AND NVL(qplab.end_date, SYSDATE+1)
AND qplc.PARENT_ENTITY_TYPE_CODE (+)=
‘PRICE_LIST_ITEM’
AND qplc.price_list_id = qplab.price_list_id
AND SYSDATE BETWEEN qplc.start_date AND
NVL(qplc.end_date, SYSDATE +1)
AND qplt.language = USERENV(‘LANG’)
AND qcdl.CHARGE_DEFINITION_ID =
qplc.CHARGE_DEFINITION_ID
AND qcdl.language = USERENV(‘LANG’)
order by esi.item_number

Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.

Reference:
support.oracle.com

Check out the Amazon Deals of the day based on your interests..