Sunday, 22 June 2014

How to find price discounts and surcharges on order lines in Order Management

SELECT   h.order_number, l.line_number, pa.list_line_type_code,
         pa.arithmetic_operator, pa.operand,
         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_amt
    FROM qp_list_headers_vl lh,
         oe_price_adjustments pa,
         oe_order_lines_all l,
         oe_order_headers_all h
   WHERE h.order_number = 151266
     AND 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 pa.list_header_id = lh.list_header_id
--     AND (   pa.list_line_type_code = 'DIS'  OR pa.list_line_type_code = 'SUR'  OR pa.list_line_type_code = 'TAX'        )
     AND pa.applied_flag = 'Y'
     AND NOT EXISTS (
            SELECT 'X'
              FROM oe_price_adj_assocs pas, oe_price_adjustments pa1
             WHERE pas.rltd_price_adj_id = pa.price_adjustment_id
               AND pa1.price_adjustment_id = pas.price_adjustment_id
               AND pa1.list_line_type_code = 'PBH')
ORDER BY l.line_id
/

Note: QP_LIST_HEADERS_VL is view based on QP_LIST_HEADERS_B and QP_LIST_HEADERS_TL tables.

No comments:

Post a Comment