Loading

Thursday, May 6, 2010

Query to find the Discount amount related to an order (R12)

Below query gives you the information about order and if any discounts available against the order.

I have tested this query in R12.1.1. 

SELECT h.order_number
      ,l.line_number
      ,l.unit_list_price
      ,l.ordered_quantity
      ,pa.list_line_type_code
      ,pa.arithmetic_operator
      ,pa.operand
      ,pa.modifier_level_code
      ,pa.adjusted_amount
      ,DECODE(pa.modifier_level_code
               ,'ORDER', l.unit_list_price*l.ordered_quantity*pa.operand*SIGN(pa.adjusted_amount)/100
                   ,(pa.adjusted_amount*NVL(l.ordered_quantity,0))
                   ) Discount_Amount
FROM   oe_order_headers_all h
      ,oe_order_lines_all l
      ,oe_price_adjustments pa
WHERE h.header_id   = l.header_id
AND   h.org_id      = l.org_id
AND   h.header_id   = pa.header_id
AND   l.line_id     = pa.line_id(+)
AND   h.order_number='&order_number';

0 Responses to “Query to find the Discount amount related to an order (R12)”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.