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.
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.