Oracle Pricelist and Associated Items Details in Oracle Apps R12


Introduction:

This query is used to retrieve the relationship between Price Lists,
Items/Products, Customers, and Customer Sites in Oracle
E-Business Suite. It provides a consolidated view of price list assignments,
helping users identify which products are associated with specific price lists
and which customers and customer sites are eligible for those pricing rules.
The query is useful for validating pricing configurations, troubleshooting
pricing issues, and verifying customer-specific product pricing assignments.

Cause of the Issue:

The issue occurs when the pricing setup in Oracle E-Business Suite is incorrect
or incomplete. Common causes include:

  • The required Price List is not assigned to the Customer or Customer Site.
  • The Item/Product is not associated with the applicable Price List.
  • The Customer or Customer Site is inactive or not eligible for the assigned Price List.
  • The Price List is inactive or outside its effective start and end dates.
  • The Item or Customer assignment is missing due to incomplete setup or data migration issues.
  • Pricing qualifiers, qualifier groups, or pricing attributes are configured incorrectly.
  • The Item is not enabled or not assigned to the appropriate Inventory Organization.
  • Recent changes to pricing or customer assignments have not been validated, resulting in incorrect pricing during order entry.

How do we solve:

Develop an RDF or any Oracle Report in Oracle EBS R12 using a custom query.
This requirement is fulfilled by writing a single SQL query that retrieves the
relationship between Price Lists, Associated Items/Products,
Customers, and Customer Sites. The query consolidates
pricing setup information by joining the relevant Oracle Advanced Pricing and
Trading Community Architecture (TCA) tables, enabling users to verify pricing
assignments, troubleshoot pricing issues, and validate customer-specific product
pricing configurations.

Use the following query to retrieve the Price List,Associated Items/Products, and Customer/Customer Site details. 


SELECT DISTINCT
    qph.name "Price List Name",
    qph.description "Price List Description",
    qph.comments "Comments",
    qph.active_flag "Price List Active Flag",
    qph.source_system_code "Source System Code",
    qph.start_date_active "Pricelist Effective Start Date",
    qph.end_date_active "Pricelist Effective End Date",
    qph.creation_date "Pricelist Creation Date",
    msi.segment1 "Product Value",
    msi.description "Product Description",
    qpl.product_uom_code "UOM",
    qpl.list_line_type_code "Line Type",
    qpl.operand "Value",
    qpl.start_date_active "Product Start Date",
    qpl.end_date_active "Product End Date",
    a.party_name "Associated Customer Name",
    a.account_number "Associated Customer Account Number",
    a.party_site_number "Associated Party Site Number",
    qph.currency_code "Currency"
FROM
    apps.qp_list_headers qph,
    apps.qp_list_lines qpl,
    apps.qp_pricing_attributes qpa,
    apps.mtl_system_items_b msi,
    (
        SELECT
            hp.party_name,
            hcaa.account_number,
            hps.party_site_number,
            hcsua.price_list_id AS list_header_id
        FROM
            apps.hz_cust_accounts_all hcaa,
            apps.hz_cust_acct_sites_all hcasa,
            apps.hz_cust_site_uses_all hcsua,
            apps.hz_parties hp,
            apps.hz_party_sites hps
        WHERE
            hcaa.cust_account_id = hcasa.cust_account_id
            AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
            AND hps.party_site_id = hcasa.party_site_id
            AND hcaa.party_id = hp.party_id
    ) a
WHERE
    qph.list_header_id = a.list_header_id (+)
    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 qpa.product_attribute = 'PRICING_ATTRIBUTE1'
    AND msi.inventory_item_id = qpa.product_attr_value
    AND msi.organization_id = 1
    AND qph.creation_date >= TO_DATE('01-01-2026', 'DD-MM-YYYY')
ORDER BY
    qph.creation_date,
    a.account_number,
    a.party_site_number;

Conclusion:

This query provides a consolidated view of Price Lists, Associated Items/Products,
Customers, and Customer Sites, making it easier to validate pricing
setups, troubleshoot pricing issues, and ensure accurate pricing assignments in
Oracle EBS R12.

Recent Posts