6 Replies Latest reply on Jul 19, 2018 6:50 AM by Bommi

    Price List Query

    Royal Cascade

      I am looking for a query in sQL (EBS R12 ) to list all active items with their list price and their Price List they belong to..... any pointers which tables should i query

      Thanks in Advance

        • 1. Re: Price List Query
          Royal Cascade

          any help..

          table names will be sufficient too...

          • 2. Re: Price List Query
            Royal Cascade

            how can i get all price lists attached to an item in SQL.... any help plz

            • 3. Re: Price List Query

              The following should get you started:


              select oeh.order_number,


                     oel.ordered_quantity qty,

                     oel.order_quantity_uom uom,

                     TO_CHAR(oel.request_date,'MM/DD/YYYY') request_date,

                     TO_CHAR(oel.schedule_ship_date,'MM/DD/YYYY') schedule_ship_date,

                     mtp.organization_code ship_from_org,

                     oel.flow_status_code line_status,

                     DECODE(oel.item_type_code,'MODEL','*') ATO,

                     otl.name line_type,

                     oel.cancelled_quantity qty_cancelled,

                     oel.shipped_quantity qty_shipped,


                     oel.ordered_quantity*DECODE(oeh.order_category_code,'RETURN',oel.unit_selling_price*-1,oel.unit_selling_price) unit_selling_price,

                     ROUND(oel.ordered_quantity*oel.unit_selling_price,2) extended_price,

                     qlh.name price_list,

                     qlh.description price_list_description,

                     oel.unit_list_price list_price,

                     oel.tax_value tax_amount,

                     party.party_name ship_to_customer,

                     ship_su.location SHIP_TO,

                     ship_loc.address1 SHIP_TO_ADDRESS,

                     partyb.party_name bill_to_customer,

                     bill_su.location BILL_TO,

                     bill_loc.address1 INVOICE_TO_ADDRESS,

                     oeh.flow_status_code order_status,

                     oth.description order_type

                from oe_order_headers_all oeh,

                     oe_order_lines_all oel,

                     hz_cust_accounts cust_acct,

                     hz_parties party,

                     hz_cust_site_uses_all ship_su,

                     hz_party_sites ship_ps,

                     hz_locations ship_loc,

                     hz_cust_acct_sites_all ship_cas,

                     hz_cust_acct_sites_all bill_cas,

                     hz_cust_site_uses_all bill_su,

                     hz_party_sites bill_ps,

                     hz_parties partyb,

                     hz_locations bill_loc,

                     mtl_parameters mtp,

                     oe_transaction_types_tl otl,

                     oe_transaction_types_tl oth,

                     qp_list_headers_tl qlh

              where oeh.header_id = oel.header_id

                 and oel.sold_to_org_id = cust_acct.cust_account_id

                 and oel.price_list_id = qlh.list_header_id

                 and cust_acct.party_id = party.party_id

                 and oel.line_type_id = otl.transaction_type_id

                 and oeh.order_type_id = oth.transaction_type_id

                 and oel.ship_from_org_id = mtp.organization_id

                 and oel.ship_to_org_id = ship_su.site_use_id(+)

                 and ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id(+)

                 and ship_cas.party_site_id = ship_ps.party_site_id(+)

                 and ship_loc.location_id(+) = ship_ps.location_id

                 and oel.invoice_to_org_id = bill_su.site_use_id(+)

                 and bill_su.cust_acct_site_id = bill_cas.cust_acct_site_id(+)

                 and bill_cas.party_site_id = bill_ps.party_site_id(+)

                 and bill_loc.location_id(+) = bill_ps.location_id

                 and bill_ps.party_id = partyb.party_id;

              1 person found this helpful
              • 4. Re: Price List Query

                Hi Royal,

                     May be below query will help you.


                select  qlh.name, qlh.description,qlh.creation_date effective_date

                        ,msi.segment1 item_name,msi.description product_description

                        ,qpa.product_attribute_context product_context,qpa.PRICING_ATTRIBUTE_CONTEXT pricing_context

                        ,qpa.pricing_attr_value_from value_from,qpa.pricing_attr_value_to value_to,qpa.COMPARISON_OPERATOR_CODE operator


                        ,qll.list_price_uom_code uom,qll.primary_uom_flag primary_uom,qll.list_line_type_code line_type

                        ,qll.arithmetic_operator application_method,qll.operand value,qll.product_precedence precedence

                        ,qll.start_date_active start_date,qll.end_date_active end_date

                from    mtl_system_items_b msi

                        ,qp_pricing_attributes qpa

                        ,qp_list_lines qll

                        ,QP_LIST_HEADERS_TL qlh

                where   1=1

                and     msi.segment1='4B006-102AB'

                and     msi.organization_id=303

                and     TO_CHAR(msi.inventory_item_id)=qpa.product_attr_value

                and     qpa.list_line_id=qll.list_line_id

                and     qll.list_header_id=qlh.list_header_id;




                1 person found this helpful
                • 5. Re: Price List Query
                  Royal Cascade

                  Thanks to all. i got it

                  • 6. Re: Price List Query

                    Hi Royal,

                         Please mark my response as Helpful/Correct accordingly.





                    1 person found this helpful