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 sql for pricing & OM

Discussion in 'Oracle Apps Technical' started by g2938, Jan 22, 2009.

  1. g2938

    g2938 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    need to determine which discount that was applied to a particular order. How do I get both order details and names of the discount that was applied to this order.

    Thanks a bunch
    G
     
  2. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    hi u can look at the views

    • OE_DISCOUNTS_V
    • OE_DISCOUNT_BY_V
    • OE_DISCOUNT_CUSTOMERS_V
    • OE_DISCOUNT_LINES_V
    • OE_PRICE_LISTS_V
    • OE_PRICE_LIST_LINES_V

    to query for the info you want
     
  3. g2938

    g2938 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Thank you, apps_expert!,

    I'm working on it now, will repost if I have questions.

    G
     
  4. g2938

    g2938 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    How do I get to oe_order_headers_all from all these views?, I don't see a link that will get me to the orders. All the views mentioned here are just for pricing. Thanks much in advance!
     
  5. 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)