This discussion is archived
5 Replies Latest reply: Sep 24, 2013 2:05 AM by ocol RSS

Oracle Quoting : Apply a Specific Modifier for Price Adjustments

924032 Newbie
Currently Being Moderated
Hi all,

While applying discounts/surcharge to a quote line from application it is picking some modifier and applying it.
We required same functionality with custom code(we are using aso_quote_pub API) to support ADF Page.
The issue we are facing is for price adjustments we need to pass the modifier header and line id to aso_quote_pub API .
So while adjustments made (discounts/surcharge) how do we know which modifier needs to apply.

Thanks in advance.

Thanks
Srinivas
  • 1. Re: Oracle Quoting : Apply a Specific Modifier for Price Adjustments
    DipanjanMaitra Explorer
    Currently Being Moderated
    Did you try passing Calculate Price Flag as 'Y'? This will automatically apply the 'Automatic' Modifiers that the Quote or Sales order currently qualifies for.
  • 2. Re: Oracle Quoting : Apply a Specific Modifier for Price Adjustments
    924032 Newbie
    Currently Being Moderated
    We did not find any column like caluculate price flag in any record types in aso_quote_pub. So can you please let us know what is the colum name so we can try out it.

    Edited by: 921029 on May 10, 2012 1:57 AM
  • 3. Re: Oracle Quoting : Apply a Specific Modifier for Price Adjustments
    924032 Newbie
    Currently Being Moderated
    Our Requirement is we need to pass some discounts like -10% to line_adjustment_percent record type but aso_quote_pub is not applying any modifier.So can anyone let us know how to deal with modifiers by using aso_qquote_pub. Sameple code is more helpful.

    Edited by: 921029 on May 10, 2012 3:01 AM
  • 4. Re: Oracle Quoting : Apply a Specific Modifier for Price Adjustments
    924032 Newbie
    Currently Being Moderated
    we find the solution. In aso_quote_pub line record type we need to pass selling_price_changed = 'Y' along with the selling price.

    Edited by: 921029 on May 14, 2012 12:21 AM
  • 5. Re: Oracle Quoting : Apply a Specific Modifier for Price Adjustments
    ocol Newbie
    Currently Being Moderated

    Hi,

     

    Here is a sample code updating a quote line price to 200 and automatically applying relevant modifiers thanks to "selling_price_change := 'Y'":

     

    DECLARE
      
      CURSOR c_quote(c_qte_header_id NUMBER) IS
        SELECT  last_update_date 
        FROM    aso_quote_headers_all
        WHERE   quote_header_id = c_qte_header_id;
      lc_last_update_date     DATE;
      l_control_rec           aso_quote_pub.control_rec_type;
      l_qte_header_rec        aso_quote_pub.qte_header_rec_type;
      l_qte_line_tbl          aso_quote_pub.qte_line_tbl_type;
      l_qte_line_rec          aso_quote_pub.qte_line_rec_type;
      l_qte_line_dtl_tbl      aso_quote_pub.qte_line_dtl_tbl_type;
      l_hd_price_attr_tbl     aso_quote_pub.price_attributes_tbl_type;
      l_hd_payment_tbl        aso_quote_pub.payment_tbl_type;
      l_hd_shipment_tbl       aso_quote_pub.shipment_tbl_type;
      l_hd_freight_charge_tbl aso_quote_pub.freight_charge_tbl_type;
      l_hd_tax_detail_tbl     aso_quote_pub.tax_detail_tbl_type;
      l_line_attr_ext_tbl     aso_quote_pub.line_attribs_ext_tbl_type;
      l_line_rltship_tbl      aso_quote_pub.line_rltship_tbl_type;
      l_price_adjustment_tbl  aso_quote_pub.price_adj_tbl_type;
      l_price_adj_attr_tbl    aso_quote_pub.price_adj_attr_tbl_type;
      l_price_adj_rltship_tbl aso_quote_pub.price_adj_rltship_tbl_type;
      l_ln_price_attr_tbl     aso_quote_pub.price_attributes_tbl_type;
      l_ln_payment_tbl        aso_quote_pub.payment_tbl_type;
      l_ln_shipment_tbl       aso_quote_pub.shipment_tbl_type;
      l_ln_freight_charge_tbl aso_quote_pub.freight_charge_tbl_type;
      l_ln_tax_detail_tbl     aso_quote_pub.tax_detail_tbl_type;
      l_hd_sales_credit_tbl   aso_quote_pub.sales_credit_tbl_type := aso_quote_pub.g_miss_sales_credit_tbl;
      l_ln_sales_credit_tbl   aso_quote_pub.sales_credit_tbl_type := aso_quote_pub.g_miss_sales_credit_tbl;
      lx_qte_header_rec         aso_quote_pub.qte_header_rec_type;
      lx_qte_line_tbl           aso_quote_pub.qte_line_tbl_type;
      lx_qte_line_dtl_tbl       aso_quote_pub.qte_line_dtl_tbl_type;
      lx_hd_price_attr_tbl      aso_quote_pub.price_attributes_tbl_type;
      lx_hd_payment_tbl         aso_quote_pub.payment_tbl_type;
      lx_hd_shipment_tbl        aso_quote_pub.shipment_tbl_type;
      lx_hd_freight_charge_tbl  aso_quote_pub.freight_charge_tbl_type;
      lx_hd_tax_detail_tbl      aso_quote_pub.tax_detail_tbl_type;
      lx_hd_attr_ext_tbl        aso_quote_pub.line_attribs_ext_tbl_type;
      lx_line_attr_ext_tbl      aso_quote_pub.line_attribs_ext_tbl_type;
      lx_line_rltship_tbl       aso_quote_pub.line_rltship_tbl_type;
      lx_price_adjustment_tbl   aso_quote_pub.price_adj_tbl_type;
      lx_price_adj_attr_tbl     aso_quote_pub.price_adj_attr_tbl_type;
      lx_price_adj_rltship_tbl  aso_quote_pub.price_adj_rltship_tbl_type;
      lx_hd_sales_credit_tbl    aso_quote_pub.sales_credit_tbl_type;
      lx_quote_party_tbl        aso_quote_pub.quote_party_tbl_type;
      lx_ln_sales_credit_tbl    aso_quote_pub.sales_credit_tbl_type;
      lx_ln_quote_party_tbl     aso_quote_pub.quote_party_tbl_type;
      lx_ln_price_attr_tbl      aso_quote_pub.price_attributes_tbl_type;
      lx_ln_payment_tbl         aso_quote_pub.payment_tbl_type;
      lx_ln_shipment_tbl        aso_quote_pub.shipment_tbl_type;
      lx_ln_freight_charge_tbl  aso_quote_pub.freight_charge_tbl_type;
      lx_ln_tax_detail_tbl      aso_quote_pub.tax_detail_tbl_type;
      lx_return_status  varchar2(1);
      lx_msg_count      number;
      lx_msg_data       varchar2(2000);
      l_payment_rec     aso_quote_pub.payment_rec_type;
      l_shipment_rec    aso_quote_pub.shipment_rec_type;
      l_tax_detail_rec  aso_quote_pub.tax_detail_rec_type;
      
    BEGIN
      -- Quote Header Rec
      l_qte_header_rec.quote_header_id  := 44237;
      -- Get the Last Updated Date
      OPEN c_quote(l_qte_header_rec.quote_header_id);
      FETCH c_quote INTO lc_last_update_date;
      CLOSE c_quote;
      l_qte_header_rec.last_update_date := lc_last_update_date;
      -- Control Rec
      l_control_rec.pricing_request_type          := 'ASO';
      l_control_rec.header_pricing_event          := 'BATCH';
      l_control_rec.line_pricing_event            := 'LINE';      -- Not sure if requested
      l_control_rec.calculate_tax_flag            := 'Y';
      l_control_rec.calculate_freight_charge_flag := 'Y';
      l_control_rec.price_mode                    := 'QUOTE_LINE'; --'ENTIRE_QUOTE';  
      l_control_rec.last_update_date              := lc_last_update_date;
      -- Quote Line Rec 
      l_qte_line_rec.quote_header_id              := 44237;
      l_qte_line_rec.quote_line_id                := 1540564;
      l_qte_line_rec.line_quote_price             := 200;
      l_qte_line_rec.operation_code               := 'UPDATE';
      l_qte_line_rec.last_update_date             := lc_last_update_date;
      l_qte_line_rec.selling_price_change         := 'Y'; -- According to https://forums.oracle.com/thread/2387453
      l_qte_line_tbl(1)                           := l_qte_line_rec;
      aso_quote_pub.update_quote(p_api_version_number       =>  1.0
                                ,p_init_msg_list            => fnd_api.g_true
                                ,p_commit                   => fnd_api.g_true
                                ,p_control_rec              => l_control_rec
                                ,p_qte_header_rec           => l_qte_header_rec
                                ,p_qte_line_tbl             => l_qte_line_tbl
                                ,p_qte_line_dtl_tbl         => l_qte_line_dtl_tbl
                                ,p_hd_tax_detail_tbl        => l_hd_tax_detail_tbl
                                ,p_ln_payment_tbl           => l_ln_payment_tbl
                                ,p_hd_sales_credit_tbl      => l_hd_sales_credit_tbl
                                ,p_ln_sales_credit_tbl      => l_ln_sales_credit_tbl
                                ,p_ln_shipment_tbl          => l_ln_shipment_tbl
                                ,x_qte_header_rec           => lx_qte_header_rec
                                ,x_qte_line_tbl             => lx_qte_line_tbl
                                ,x_qte_line_dtl_tbl         => lx_qte_line_dtl_tbl
                                ,x_hd_price_attributes_tbl  => lx_hd_price_attr_tbl
                                ,x_hd_payment_tbl           => lx_hd_payment_tbl
                                ,x_hd_shipment_tbl          => lx_hd_shipment_tbl
                                ,x_hd_freight_charge_tbl    => lx_hd_freight_charge_tbl
                                ,x_hd_tax_detail_tbl        => lx_hd_tax_detail_tbl
                                ,x_hd_attr_ext_tbl          => lx_hd_attr_ext_tbl
                                ,x_hd_sales_credit_tbl      => lx_hd_sales_credit_tbl
                                ,x_hd_quote_party_tbl       => lx_quote_party_tbl
                                ,x_line_attr_ext_tbl        => lx_line_attr_ext_tbl
                                ,x_line_rltship_tbl         => lx_line_rltship_tbl
                                ,x_price_adjustment_tbl     => lx_price_adjustment_tbl
                                ,x_price_adj_attr_tbl       => lx_price_adj_attr_tbl
                                ,x_price_adj_rltship_tbl    => lx_price_adj_rltship_tbl
                                ,x_ln_price_attributes_tbl  => lx_ln_price_attr_tbl
                                ,x_ln_payment_tbl           => lx_ln_payment_tbl
                                ,x_ln_shipment_tbl          => lx_ln_shipment_tbl
                                ,x_ln_freight_charge_tbl    => lx_ln_freight_charge_tbl
                                ,x_ln_tax_detail_tbl        => lx_ln_tax_detail_tbl
                                ,x_ln_sales_credit_tbl      => lx_ln_sales_credit_tbl
                                ,x_ln_quote_party_tbl       => lx_ln_quote_party_tbl
                                ,x_return_status            => lx_return_status
                                ,x_msg_count                => lx_msg_count
                                ,x_msg_data                 => lx_msg_data);
        
      dbms_output.put_line('lx_return_status: '||lx_return_status);
      dbms_output.put_line('lx_msg_count:     '||lx_msg_count);
      dbms_output.put_line('lx_msg_data:     '||lx_msg_data);
      fnd_msg_pub.count_and_get(p_encoded => 'F'
                               ,p_count   => lx_msg_count
                               ,p_data    => lx_msg_data);
      dbms_output.put_line('no. of FND messages :'||lx_msg_count);
      FOR k IN 1 .. lx_msg_count LOOP
        lx_msg_data := fnd_msg_pub.get(p_msg_index => k
                                      ,p_encoded => 'F');
        dbms_output.put_line('Error msg:   '||substr(lx_msg_data,1,240));
      END LOOP;
      dbms_output.put_line(lx_return_status);
      dbms_output.put_line(to_char(lx_msg_count));
      dbms_output.put_line(lx_msg_data);
      
    END;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points