9 Replies Latest reply: May 5, 2013 10:43 AM by Solomon Yakobson RSS

    Calculate line price based on adjustments

    Activesometimes-Oracle
      Hi Guru's.

      Could you please help me to prepare a query?
      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);
      
      create table xx_price_adjustments
      (
      adjustment_id number primary key,
      Line_id number,
      pricing_group_sequence number,
      operand number,
      arithmetic_operator varchar2(4) check (arithmetic_operator  in ('%','AMT'))
      );
      
      insert into xx_price_adjustments
      (adjustment_id,line_id,pricing_group_sequence,Operand,arithmetic_operator)
      values
      (10000,1234,1,5,'%');
      
      insert into xx_price_adjustments
      (adjustment_id,line_id,pricing_group_sequence,Operand,arithmetic_operator)
      values
      (10001,1234,1,50,'AMT');
      
      
      insert into xx_price_adjustments
      (adjustment_id,line_id,pricing_group_sequence,Operand,arithmetic_operator)
      values
      (10002,1234,2,7,'%');
      
      
      insert into xx_price_adjustments
      (adjustment_id,line_id,pricing_group_sequence,Operand,arithmetic_operator)
      values
      (10003,1234,3,2,'%');
      
      
      insert into xx_price_adjustments
      (adjustment_id,line_id,pricing_group_sequence,Operand,arithmetic_operator)
      values
      (10004,1234,3,20,'AMT');
      
      
      insert into xx_price_adjustments
      (adjustment_id,line_id,pricing_group_sequence,Operand,arithmetic_operator)
      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
          Etbin
          Maybe NOT TESTED! No Database at hand so take it as a template
          with
          anchor_member(line_id,item,unit_list_price,selling_price,
                        adjustment_id,pricing_group_sequence,operand,arithmetic_operator) as
          (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,
                  pa.adjustment_id,pa.pricing_group_sequence,pa.operand,pa.arithmetic_operator
             from xx_line xl,
                  xx_price_adjustments pa
            where xl.line_id = pa.line_id
            order by pa.adjustment_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,
                  adjustment_id + 1,pricing_group_sequence,operand,arithmetic_operator
             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
            1006154
            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  ( 
                                      select b.adjustment_id,
                                              b.line_id,
                                              b.pricing_group_sequence,
                                              b.operand,
                                              b.arithmetic_operator,
                                              row_number() over (partition by line_id order by adjustment_id) rnum
                                      from   xx_price_adjustments b)  b
                               start with rnum=1
                               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
              Solomon Yakobson
              Since you are on 11.2 you can use recursive subquey factoring:
              with a as (
                         select  row_number() over(order by adjustment_id) adjustment_sequence,
                                 count(*) over() adjustment_count,
                                 line_id,
                                 pricing_group_sequence,
                                 operand,
                                 arithmetic_operator
                           from  xx_price_adjustments
                        ),
                   r(
                     line_id,
                     item,
                     unit_list_price,
                     selling_price,
                     pricing_group_selling_price,
                     adjustment_sequence,
                     adjustment_count,
                     pricing_group_sequence
                    ) as (
                           select  line_id,
                                   item,
                                   unit_list_price,
                                   unit_list_price selling_price,
                                   unit_list_price pricing_group_selling_price,
                                   0 adjustment_sequence,
                                   1 adjustment_count,
                                   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.adjustment_sequence,
                                   a.adjustment_count,
                                   a.pricing_group_sequence
                             from  r,
                                   a
                             where a.line_id = r.line_id
                               and a.adjustment_sequence = r.adjustment_sequence + 1
                         )
              select  line_id,
                      item,
                      unit_list_price,
                      selling_price
                from  r
                where adjustment_sequence = adjustment_count
              /
              
              
                 LINE_ID ITEM                           UNIT_LIST_PRICE SELLING_PRICE
              ---------- ------------------------------ --------------- -------------
                    1234 xxitem                                   25000    21148.5764
              
              SQL>
              SY.
              • 4. Re: Calculate line price based on adjustments
                1006154
                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 (
                           select  row_number() over(partition by line_id order by adjustment_id) adjustment_sequence,
                                   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
                  Solomon Yakobson
                  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;
                  
                  ADJUSTMENT_ID    LINE_ID PRICING_GROUP_SEQUENCE    OPERAND ARIT
                  ------------- ---------- ---------------------- ---------- ----
                          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  (
                    8                          select b.adjustment_id,
                    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
                   14                          from   xx_price_adjustments b)  b
                   15                   start with rnum=1
                   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 (
                    2             select  row_number() over(order by adjustment_id) adjustment_sequence,
                    3                     count(*) over() adjustment_count,
                    4                     line_id,
                    5                     pricing_group_sequence,
                    6                     operand,
                    7                     arithmetic_operator
                    8               from  xx_price_adjustments
                    9            ),
                   10       r(
                   11         line_id,
                   12         item,
                   13         unit_list_price,
                   14         selling_price,
                   15         pricing_group_selling_price,
                   16         adjustment_sequence,
                   17         adjustment_count,
                   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,
                   25                       0 adjustment_sequence,
                   26                       1 adjustment_count,
                   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,
                   47                       a.adjustment_sequence,
                   48                       a.adjustment_count,
                   49                       a.pricing_group_sequence
                   50                 from  r,
                   51                       a
                   52                 where a.line_id = r.line_id
                   53                   and a.adjustment_sequence = r.adjustment_sequence + 1
                   54             )
                   55  select  line_id,
                   56          item,
                   57          unit_list_price,
                   58          selling_price
                   59    from  r
                   60    where adjustment_sequence = adjustment_count
                   61  /
                  
                     LINE_ID ITEM                           UNIT_LIST_PRICE SELLING_PRICE
                  ---------- ------------------------------ --------------- -------------
                        1234 xxitem                                   25000      7997.535
                  
                  SQL>
                  SY.
                  • 6. Re: Calculate line price based on adjustments
                    Activesometimes-Oracle
                    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
                    from xx_price_adjustments xpa,xx_line xl
                    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, 
                    round(l.unit_list_price - (l.unit_list_price *l.adj_percent) - (l.adj_amount),2)
                     from line_adj_view l
                    where l.pricing_group_sequence = 1
                    union all
                    select aq.line_id,l2.pricing_group_sequence,aq.selling_price,
                    round(aq.selling_price - (aq.selling_price * l2.adj_percent) - (l2.adj_amount),2)
                    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
                      Activesometimes-Oracle
                      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
                      (adjustment_id,line_id,pricing_group_sequence,Operand,arithmetic_operator)
                      values
                      (10007,1234,1,2,'%');
                      • 8. Re: Calculate line price based on adjustments
                        Solomon Yakobson
                        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
                                     from  xx_price_adjustments
                                  ),
                             r(
                               line_id,
                               item,
                               unit_list_price,
                               selling_price,
                               pricing_group_selling_price,
                               adjustment_sequence,
                               adjustment_count,
                               pricing_group_sequence
                              ) as (
                                     select  line_id,
                                             item,
                                             unit_list_price,
                                             unit_list_price selling_price,
                                             unit_list_price pricing_group_selling_price,
                                             0 adjustment_sequence,
                                             1 adjustment_count,
                                             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.adjustment_sequence,
                                             a.adjustment_count,
                                             a.pricing_group_sequence
                                       from  r,
                                             a
                                       where a.line_id = r.line_id
                                         and a.adjustment_sequence = r.adjustment_sequence + 1
                                   )
                        select  line_id,
                                item,
                                unit_list_price,
                                selling_price
                          from  r
                          where adjustment_sequence = adjustment_count
                        / 
                        
                           LINE_ID ITEM                           UNIT_LIST_PRICE SELLING_PRICE
                        ---------- ------------------------------ --------------- -------------
                              1234 xxitem                                   25000    20701.9904
                        
                        SQL>
                        SY.
                        • 9. Re: Calculate line price based on adjustments
                          Solomon Yakobson
                          ActiveSomeTimes wrote:
                          Seems your solution approach is ok, but here are my points to you..
                          Etbin's approach is much simpler.

                          SY.