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';
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
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.
0 Responses to “Query to find the Discount amount related to an order (R12)”
Post a Comment