9 Replies Latest reply on May 5, 2013 3:43 PM by Solomon Yakobson

# Calculate line price based on adjustments

Hi Guru's.

Selling price has to be calculated based on unit_list_price & adjustment details.

Two tables Order line & Adjustment data.
Pricing group sequence is the order of calculating adjustment at every step to arrive at the current price to adjust further.

Example Calculation :

25000     -- List price
5%     1250 -- First bucket -- 5%discount on list price
50     50 -- First bucket -- 50 discount on list price
23700     -- Price after first bucket adjustment
7%     1659 -- adjustment for second bucket - on top of 23700
22041     -- Price after second bucket adjustment
20     20 -- 20 on 22041
2%     440.82 -- 2% on 22041
21580.18     -- Price after third bucket adjustment
2%     431.6 -- 2% on 21580.18
21148.58

`````` select * from v\$version
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE     11.2.0.3.0     Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

create table xx_line
(line_id number,
item varchar2(30),
unit_list_price number,
selling_price number);

insert into xx_line
(line_id,item,unit_list_price,selling_price)
values
(1234,'xxitem',25000,21148.58);

(
Line_id number,
pricing_group_sequence number,
operand number,
arithmetic_operator varchar2(4) check (arithmetic_operator  in ('%','AMT'))
);

values
(10000,1234,1,5,'%');

values
(10001,1234,1,50,'AMT');

values
(10002,1234,2,7,'%');

values
(10003,1234,3,2,'%');

values
(10004,1234,3,20,'AMT');

values
(10005,1234,4,2,'%');

select * from xx_line;

select * from xx_price_adjustments order by pricing_group_sequence;``````
• ###### 1. Re: Calculate line price based on adjustments
Maybe NOT TESTED! No Database at hand so take it as a template
``````with
anchor_member(line_id,item,unit_list_price,selling_price,
(select xl.line_id,xl.item,xl.unit_list_price,
xl.unit_list_price - case pa.arithmetic_operator when '%'
then xl.unit_list_price * pa.operand * 0.01
else pa.operand
end,
from xx_line xl,
where xl.line_id = pa.line_id
union all
select line_id,item,unit_list_price,
selling_price - case arithmetic_operator when '%'
then selling_price * operand * 0.01
else operand
end,
from anchor_member
)
select line_id,item,unit_list_price,round(selling_price,2) selling_price
from anchor_member``````
Regards

Etbin
• ###### 2. Re: Calculate line price based on adjustments
Hi~.
from 11gR2, you can use not only sys_connect_by_path() function but also recursive subquery factoring.
below is sys_connect_by_path() version.
first of all, you have to create user define function for calculation in case of sys_connect_by_path().
``````create or replace function get_sell_prie(p_list_price in varchar2, p_op in varchar2)
return number
is
v_idx1          number := 0;
v_idx2          number;

v_temp          number;
v_total          number := p_list_price;
begin
v_idx1 := instr(p_op, '/');

loop

v_idx2 := instr(p_op, '/', v_idx1+1);
if v_idx2 = 0 then
v_idx2 := length(p_op)+1;
end if;

v_temp  := to_number(substr(p_op, v_idx1+2, v_idx2-v_idx1-2));

case (substr(p_op, v_idx1+1,1))
when '%' then  v_total := v_total * (100-v_temp) / 100;
when 'A' then  v_total := v_total - v_temp;
end case;

v_idx1 := v_idx2;

exit when v_idx1 > length(p_op);

end loop;

return v_total;
end;
/``````
and then, run below query.
``````select a.line_id,
a.item,
a.unit_list_price,
round(get_sell_prie(a.unit_list_price, b.op),2) sell_price
from   xx_line a, (
select b.line_id, max(sys_connect_by_path(substr(b.arithmetic_operator,1,1)||b.operand,'/')) op
from  (
b.line_id,
b.pricing_group_sequence,
b.operand,
b.arithmetic_operator,
row_number() over (partition by line_id order by adjustment_id) rnum
connect by prior rnum=rnum-1 and prior line_id=line_id
group by b.line_id) b
where a.line_id=b.line_id;

LINE_ID ITEM                           UNIT_LIST_PRICE SELL_PRICE
---------- ------------------------------ --------------- ----------
1234 xxitem                                   25000   21148.58``````
Right ?
if you want to use recursive subquery factoring, this blog maybe helpful. http://dbmstuning.tistory.com/16
Thanks.

Edited by: seankim on 2013. 5. 5 오전 5:19
• ###### 3. Re: Calculate line price based on adjustments
Since you are on 11.2 you can use recursive subquey factoring:
``````with a as (
line_id,
pricing_group_sequence,
operand,
arithmetic_operator
),
r(
line_id,
item,
unit_list_price,
selling_price,
pricing_group_selling_price,
pricing_group_sequence
) as (
select  line_id,
item,
unit_list_price,
unit_list_price selling_price,
unit_list_price pricing_group_selling_price,
1 pricing_group_sequence
from  xx_line
union all
select  r.line_id,
r.item,
r.unit_list_price,
case r.pricing_group_sequence
when a.pricing_group_sequence then r.selling_price - case a.arithmetic_operator
when '%' then r.pricing_group_selling_price / 100 * a.operand
when 'AMT' then a.operand
end
else r.selling_price - case a.arithmetic_operator
when '%' then r.selling_price / 100 * a.operand
when 'AMT' then a.operand
end
end selling_price,
case r.pricing_group_sequence
when a.pricing_group_sequence then r.pricing_group_selling_price
else r.selling_price
end pricing_group_selling_price,
a.pricing_group_sequence
from  r,
a
where a.line_id = r.line_id
)
select  line_id,
item,
unit_list_price,
selling_price
from  r
/

LINE_ID ITEM                           UNIT_LIST_PRICE SELLING_PRICE
---------- ------------------------------ --------------- -------------
1234 xxitem                                   25000    21148.5764

SQL>``````
SY.
• ###### 4. Re: Calculate line price based on adjustments
Wow! Very cool.
As you already know, if xx_line(and xx_price_adjustments) tables have more than 2 line_id, then this query has to add "partition by line_id" in row_number() and count(*).
``````with a as (
count(*) over(partition by line_id ) adjustment_count,
...``````
Thanks.

Edited by: seankim on 2013. 5. 5 오전 6:16
• ###### 5. Re: Calculate line price based on adjustments
seankim wrote:
below is sys_connect_by_path() version.
Data magic. Your solution isn't working. It doesn't take buckets (pricing_group_sequence) into consideration. Just issue:
update xx_price_adjustments set arithmetic_operator = '%';
Now calculations are:

25000 -- List price
5% 1250 -- First bucket -- 5%discount on list price
50% 12500 -- First bucket -- 50% discount on list price
11250 -- Price after first bucket adjustment
7% 787.5 -- adjustment for second bucket - on top of 10462.5
10462.5 -- Price after second bucket adjustment
20% 2092.5 -- 20% on 10462.5
2% 209.25 -- 2% on 10462.5
8160.75 -- Price after third bucket adjustment
2% 163.215 -- 2% on 8160.75
7997.535 - selling price.

And look what your solution returns:
``````SQL> select * from xx_price_adjustments;

------------- ---------- ---------------------- ---------- ----
10000       1234                      1          5 %
10001       1234                      1         50 %
10002       1234                      2          7 %
10003       1234                      3          2 %
10004       1234                      3         20 %
10005       1234                      4          2 %

6 rows selected.

SQL> select a.line_id,
2         a.item,
3        a.unit_list_price,
4        round(get_sell_prie(a.unit_list_price, b.op),2) sell_price
5  from   xx_line a, (
6                   select b.line_id, max(sys_connect_by_path(substr(b.arithmetic_operator,1,1)||b.operand,'/')) op
7                   from  (
9                                     b.line_id,
10                                     b.pricing_group_sequence,
11                                     b.operand,
12                                     b.arithmetic_operator,
13                                     row_number() over (partition by line_id order by adjustment_id) rnum
16                   connect by prior rnum=rnum-1 and prior line_id=line_id
17                   group by b.line_id) b
18  where a.line_id=b.line_id;

LINE_ID ITEM                           UNIT_LIST_PRICE SELL_PRICE
---------- ------------------------------ --------------- ----------
1234 xxitem                                   25000    8485.13``````
Why? It is always basing price on previous step selling price, not on bucket selling price:

25000 -- List price
5% of 25000 = 1250
Selling price 23750
50% of 23750 = 11875
Selling price 11875
7% of 11875 = 831.25
Selling price 11043.75
2% of 11043.75 = 220.875
Selling price 10822.875
20% of 10822.875 = 2164.575
Selling price 8658.3
2% of 8658.3 = 173.166
Selling price 8485.134 (or 8485.13 rounded)

My solution:
``````SQL> with a as (
4                     line_id,
5                     pricing_group_sequence,
6                     operand,
7                     arithmetic_operator
9            ),
10       r(
11         line_id,
12         item,
13         unit_list_price,
14         selling_price,
15         pricing_group_selling_price,
18         pricing_group_sequence
19        ) as (
20               select  line_id,
21                       item,
22                       unit_list_price,
23                       unit_list_price selling_price,
24                       unit_list_price pricing_group_selling_price,
27                       1 pricing_group_sequence
28                 from  xx_line
29              union all
30               select  r.line_id,
31                       r.item,
32                       r.unit_list_price,
33                       case r.pricing_group_sequence
34                         when a.pricing_group_sequence then r.selling_price - case a.arithmetic_operator
35                                                                                when '%' then r.pricing_group_selling_price / 100 * a.operand
36                                                                                when 'AMT' then a.operand
37                                                                              end
38                       else r.selling_price - case a.arithmetic_operator
39                                                when '%' then r.selling_price / 100 * a.operand
40                                                when 'AMT' then a.operand
41                                              end
42                       end selling_price,
43                       case r.pricing_group_sequence
44                         when a.pricing_group_sequence then r.pricing_group_selling_price
45                       else r.selling_price
46                       end pricing_group_selling_price,
49                       a.pricing_group_sequence
50                 from  r,
51                       a
52                 where a.line_id = r.line_id
54             )
55  select  line_id,
56          item,
57          unit_list_price,
58          selling_price
59    from  r
61  /

LINE_ID ITEM                           UNIT_LIST_PRICE SELLING_PRICE
---------- ------------------------------ --------------- -------------
1234 xxitem                                   25000      7997.535

SQL>``````
SY.
• ###### 6. Re: Calculate line price based on adjustments
Hi Etbin,

Your template was helpful for me to come up with below sql
``````with line_adj_view as
(
select xl.unit_list_price,xpa.line_id ,xpa.pricing_group_sequence ,
nvl(sum (decode (arithmetic_operator,'%' , xpa.operand*0.01 )),0) adj_percent,
nvl(sum (decode (arithmetic_operator,'AMT' , operand )),0) adj_amount
where xl.line_id = xpa.line_id
group by xl.unit_list_price, xpa.line_id ,xpa.pricing_group_sequence
order by pricing_group_sequence
)
,
anchor_query  ( line_id,pricing_group_sequence,unit_list_price,selling_price) as
(
select l.line_id,l.pricing_group_sequence,l.unit_list_price,
where l.pricing_group_sequence = 1
union all
select aq.line_id,l2.pricing_group_sequence,aq.selling_price,
from anchor_query aq , line_adj_view l2
where l2.line_id = aq.line_id
and aq.pricing_group_sequence+1 = l2.pricing_group_sequence
)
select * from anchor_query order by pricing_group_sequence;``````
• ###### 7. Re: Calculate line price based on adjustments
Hi SY,

Seems your solution approach is ok, but here are my points to you..

Adjustment id 's are in order in my test data, but we cannot rely on the order of this column.

Lets say we have another row like below for first bucket but in last according to adjustment id order. Your solution will not show correct result.
``````insert into xx_price_adjustments
values
(10007,1234,1,2,'%');``````
• ###### 8. Re: Calculate line price based on adjustments
ActiveSomeTimes wrote:
Seems your solution approach is ok, but here are my points to you..
If adjustment_id is independent of pricing_group_sequence then simply order by bucket:
``````with a as (
select  row_number() over(partition by line_id order by pricing_group_sequence) adjustment_sequence,
count(*) over(partition by line_id ) adjustment_count,
line_id,
pricing_group_sequence,
operand,
arithmetic_operator
),
r(
line_id,
item,
unit_list_price,
selling_price,
pricing_group_selling_price,
pricing_group_sequence
) as (
select  line_id,
item,
unit_list_price,
unit_list_price selling_price,
unit_list_price pricing_group_selling_price,
1 pricing_group_sequence
from  xx_line
union all
select  r.line_id,
r.item,
r.unit_list_price,
case r.pricing_group_sequence
when a.pricing_group_sequence then r.selling_price - case a.arithmetic_operator
when '%' then r.pricing_group_selling_price / 100 * a.operand
when 'AMT' then a.operand
end
else r.selling_price - case a.arithmetic_operator
when '%' then r.selling_price / 100 * a.operand
when 'AMT' then a.operand
end
end selling_price,
case r.pricing_group_sequence
when a.pricing_group_sequence then r.pricing_group_selling_price
else r.selling_price
end pricing_group_selling_price,
a.pricing_group_sequence
from  r,
a
where a.line_id = r.line_id
)
select  line_id,
item,
unit_list_price,
selling_price
from  r
/

LINE_ID ITEM                           UNIT_LIST_PRICE SELLING_PRICE
---------- ------------------------------ --------------- -------------
1234 xxitem                                   25000    20701.9904

SQL>``````
SY.
• ###### 9. Re: Calculate line price based on adjustments
ActiveSomeTimes wrote:
Seems your solution approach is ok, but here are my points to you..
Etbin's approach is much simpler.

SY.