1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need a query for Pricelist

Discussion in 'Oracle Apps Technical' started by spdash12, Aug 11, 2009.

  1. spdash12

    spdash12 Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Basically I am looking for a PL/SQL query(for Pricelist) which will help to trace a particular item is linked to which Pricelists. Input for the query should be ITEM. Could someone please help to build a query.

    Query which I built is taking lots of time to execute.

    Query
    =====
    Code (SQL):

    SELECT qp_price_list_line_util.get_product_value
                                                  ('QP_ATTR_DEFNS_PRICING',
                                                   qppr.product_attribute_context,
                                                   qppr.product_attribute,
                                                   qppr.product_attr_value
                                                  ),
           a.NAME, qppr.product_uom_code,                      -- qpll.list_price,
                                         qpll.operand item_price,
           qpll.inventory_item_id,
           DECODE (UPPER (qppr.product_attr_value),
                   'ALL', NULL,
                   qppr.product_attr_value
                  )
      FROM qp_list_headers_all a,
           qp_list_lines qpll,
           qp_pricing_attributes qppr,
           mtl_system_items mtl
    --WHERE a.list_header_id = 533750
    WHERE  a.list_header_id = qpll.list_header_id
       AND qppr.product_attribute_context = 'ITEM'
       AND TO_NUMBER (qppr.list_line_id) = qpll.list_line_id
       AND TO_NUMBER (qppr.product_attr_value) = mtl.inventory_item_id
       AND mtl.organization_id = (SELECT qp_util.get_item_validation_org
                                    FROM DUAL)
       AND mtl.segment1 = '&ITEM'
       AND qpll.list_line_type_code IN ('PLL', 'PBH')
       AND qppr.pricing_phase_id = 1
       AND qppr.qualification_ind IN (4, 6, 20, 22)
       AND qpll.pricing_phase_id = 1
       AND qpll.qualification_ind IN (4, 6, 20, 22)
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Hi...

    Sorry but it seems no one here has written a similar query. It seems in your query the culprit is mtl_system_items and the sub-query

    Code (SQL):
     AND mtl.organization_id = (SELECT qp_util.get_item_validation_org
                                    FROM DUAL)
    You should try and narrrow down on the search performed in mtl_system_items by adding more conditions on it. That should reduce your query time significantly.
     
  3. spdash12

    spdash12 Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Hi Sadiq...
    Thanks for your response.. Yes I know this query is taking time b'coz of the join with MTL_SYSTEM_ITEMS table and moreover I am trying to get Header info by querying at Line level..

    Being a functional consultant, I don't have much expertise in writing PL/SQL.. Any poniter to reduce the execution time will be highly appreciated.

    As per your advise I will also try to rebuild the query.

    Warm Regards,
    S.P DASH
     
  4. Alisha

    Alisha Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    India
    Hi,

    Did you get the query for the price list??