Yesterday user reported they had wrong discount and unit price in one of the SO extraction program. After checking the oe_price_adjustments table, I found adjusted_amount_per_pqty, the column I used to calculate discount and unit_price, contains very strange amount
select ool.line_id,ool.ordered_quantity,ool.pricing_quantity,opa.creation_date, opa.last_update_date, opa.last_updated_by
,opa.operand_per_pqty,opa.operand_per_pqty, arithmetic_operator, adjusted_amount , adjusted_amount_per_pqty
from oe_order_lines ool
,oe_order_headers ooh
,oe_price_adjustments opa
where ool.header_id =ooh.header_id
AND ool.line_id = opa.line_id
AND opa.list_line_type_code = 'DIS'
and ooh.order_number = 1047756 --1048839
and ool.line_number = 2
unit_list_price unit_selling_price operand_per_pqty arithmetic_operator adjusted_amount_per_pqty
63.73 33.5 47.4344892515299 % -5.03833333333333
After checking the program OE_OE_FORM_LINE_ADJ, Oracle calculates adjusted_amount_per_pqty using following formular:
adjusted_amount_per_pqty:= adjusted_amount * ordered_quantity / pricing_quantity,
but unfortunately, the ordered_quantity is 2134 while the pricing_quantity is 3000 which is the ordered_quantity before split. How come this pricing_quantity was not updated to be the same to ordered_quantity when splitting line? This is still under investigation.