QP Price Lists

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Master data report showing the setup of price lists including item prices, calculation methods, terms and dates.
Run QP Price Lists and other Oracle EBS reports with Blitz Report™ on our demo environment
select
qlhv.name price_list,
qlhv.description,
xxen_util.meaning(decode(qlhv.global_flag,'Y','Y'),'YES_NO',0) global,
qlhv.currency_code currency,
fcv.name currency_name,
qclv.name multi_currency_name,
qlhv.rounding_factor,
qllv.start_date_active start_date,
qllv.end_date_active end_date,
qllv.product_attribute_context product_context,
qllv.product_uom_code uom,
qllv.product_attr_val_disp item,
msiv.description item_description,
xxen_util.meaning(qllv.list_line_type_code,'LIST_LINE_TYPE_CODE',661) line_type,
xxen_util.meaning(qllv.arithmetic_operator,'ARITHMETIC_OPERATOR',661) application_method,
xxen_util.meaning(qllv.price_break_type_code,'PRICE_BREAK_TYPE_CODE',661) price_break_type,
qllv.operand value,
qllv.product_precedence precedence,
qlhv.pte_code pricing_transaction_entity,
qlhv.source_system_code,
xxen_util.meaning(qllv.comparison_operator_code,'COMPARISON_OPERATOR',661) operator,
xxen_util.meaning(qlhv.list_type_code,'LIST_TYPE_CODE',661) list_type,
qlhv.list_type_code list_type_code,
xxen_util.meaning(qlhv.source_system_code,'SOURCE_SYSTEM',661) source_system,
qpfv.name formula,
--qp_qpxprcst_xmlp_pkg.cf_product_attributeformula(qllv.product_attribute_context, qllv.product_attribute) product_attribute,
--qp_qpxprcst_xmlp_pkg.cf_product_attr_val_dispformul(qllv.product_attribute_context, qllv.product_attribute, qllv.product_attr_value, qllv.product_attr_val_disp) product_attr_val_disp,
hca.account_number,
hp.party_name customer_name,
xxen_util.meaning(qlhv.ship_method_code,'SHIP_METHOD',3) ship_method,
xxen_util.meaning(qlhv.freight_terms_code,'FREIGHT_TERMS',660) freight_terms,
rtt.name payment_term,
haouv.name operating_unit,
xxen_util.user_name(qllv.created_by) created_by,
xxen_util.client_time(qllv.creation_date) creation_date,
xxen_util.user_name(qllv.last_updated_by) last_updated_by,
xxen_util.client_time(qllv.last_update_date) last_update_date,
qlhv.list_header_id,
qllv.list_line_id
from
qp_list_headers_vl qlhv,
qp_list_lines_v qllv,
mtl_system_items_vl msiv,
qp_currency_lists_vl qclv,
fnd_currencies_vl fcv,
ra_terms_tl rtt,
hz_cust_accounts hca,
hz_parties hp,
hr_all_organization_units_vl haouv,
qp_price_formulas_vl qpfv
where
1=1 and
qlhv.list_header_id=qllv.list_header_id and
(qllv.product_attribute<>'PRICING_ATTRIBUTE1' or msiv.inventory_item_id is not null) and
case when qllv.product_attribute_context='ITEM' and qllv.product_attribute='PRICING_ATTRIBUTE1' then qllv.product_attr_value end=msiv.inventory_item_id(+) and
msiv.organization_id(+)=:organization_id and
qlhv.currency_header_id=qclv.currency_header_id(+) and
qlhv.currency_code=fcv.currency_code(+) and
qlhv.terms_id=rtt.term_id(+) and
rtt.language(+)=userenv('lang') and
qlhv.sold_to_org_id=hca.cust_account_id(+) and
hca.party_id=hp.party_id(+) and
qlhv.orig_org_id=haouv.organization_id(+) and
qllv.price_by_formula_id=qpfv.price_formula_id(+)
order by
qlhv.name,
qllv.product_attr_val_disp,
qllv.start_date_active desc
Parameter Name SQL text Validation
Price List 
qlhv.name=:price_list
 LOV
Item 
qllv.product_attr_value in (select to_char(msiv.inventory_item_id) from mtl_system_items_vl msiv where msiv.concatenated_segments=:item and msiv.organization_id=:organization_id) and
qllv.product_attribute_context='ITEM' and
qllv.product_attribute='PRICING_ATTRIBUTE1'
 LOV
Category Set 
qllv.product_attr_value in (
select
to_char(mic.inventory_item_id)
from
mtl_category_sets_v mcsv,
mtl_item_categories mic
where
mcsv.category_set_name=:category_set_name and
mcsv.category_set_id=mic.category_set_id
) and
qllv.product_attribute_context='ITEM' and
qllv.product_attribute='PRICING_ATTRIBUTE1'
 LOV
Category 
qllv.product_attr_value in (
select
to_char(mic.inventory_item_id)
from
mtl_item_categories mic,
mtl_category_sets mcs,
mtl_categories_kfv mck
where
mck.concatenated_segments=:category and
mck.structure_id=mcs.structure_id and
mck.category_id=mic.category_id and
mcs.category_set_id=mic.category_set_id
) and
qllv.product_attribute_context='ITEM' and
qllv.product_attribute='PRICING_ATTRIBUTE1'
 LOV
Valid on Date 
:valid_on_date between nvl(qllv.start_date_active,sysdate) and nvl(qllv.end_date_active,sysdate)
 Date
Organization Code 
 
 LOV Oracle
Download
 
Related Blitz Reports
PPF_WP3_OM_DETAILS QP Customer Pricing Engine Request ONT Orders and Lines CAC Intercompany SO Price List vs. Item Cost Comparison ONT DIFOT XXONT Orders and Lines Yanmar - Discount on order lines check CAC Internal Order Shipment Margin ONT Orders
Blitz Report™

Blitz Report™ provides multiple benefits: