APPS.ASO_DEAL_PUB SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 15

Procedure Update_Quote_From_Deal
 (  P_Quote_Header_Id            IN   NUMBER,
    P_resource_id                IN   NUMBER,
    P_event                      IN   VARCHAR2     := FND_API.G_FALSE,
    X_Return_Status              OUT NOCOPY /* file.sql.39 change */  VARCHAR2,
    X_Msg_Count                  OUT NOCOPY /* file.sql.39 change */  NUMBER,
    X_Msg_Data                   OUT NOCOPY /* file.sql.39 change */  VARCHAR2
 )
is

-- for fetching status id
CURSOR C_qte_status_id (pc_quote_status_code VARCHAR2)
	IS
	  SELECT quote_status_id
	   FROM aso_quote_statuses_b
	   WHERE status_code = pc_quote_status_code;

Line: 35

  SELECT last_update_date, quote_number, max_version_flag, pricing_status_indicator, tax_status_indicator,quote_status_id,price_request_id
	FROM aso_quote_headers_all
	WHERE quote_header_id = pc_quote_header_id;

Line: 43

	SELECT  aqla.line_quote_price,aqla.quantity, nvl(aship.ship_method_code,'X') ship_method_code,nvl(payment_term_id,-1) payment_term_id,nvl(PRICING_LINE_TYPE_INDICATOR,'XXX') PRICING_LINE_TYPE_INDICATOR
	FROM aso_quote_lines_all aqla, aso_shipments aship,aso_payments apay
	WHERE aqla.quote_line_id = pc_quote_line_id
	AND  aqla.quote_line_id = aship.quote_line_id (+)
	AND  aqla.quote_line_id = apay.quote_line_id (+) ;

Line: 53

SELECT
	  PRICE_ADJUSTMENT_ID,
          PRICE_BREAK_TYPE_CODE,
           MODIFIER_HEADER_ID,
           MODIFIER_LINE_ID,
           MODIFIER_LINE_TYPE_CODE,
           PRICING_GROUP_SEQUENCE,
           PRICING_PHASE_ID,
           ARITHMETIC_OPERATOR,
           nvl(OPERAND_PER_PQTY,OPERAND) operand,
           MODIFIED_FROM,
           MODIFIED_TO,
           UPDATE_ALLOWABLE_FLAG,
           ON_INVOICE_FLAG,
           MODIFIER_LEVEL_CODE,
           BENEFIT_QTY,
           BENEFIT_UOM_CODE,
           LIST_LINE_NO,
           ACCRUAL_FLAG,
           ACCRUAL_CONVERSION_RATE,
           CHARGE_TYPE_CODE,
           CHARGE_SUBTYPE_CODE,
           RANGE_BREAK_QUANTITY,
           MODIFIER_MECHANISM_TYPE_CODE,
           CHANGE_REASON_CODE,
           CHANGE_REASON_TEXT,
	   adjusted_amount,
	   automatic_flag
            from aso_price_adjustments
           where quote_header_id=pc_quote_header_id
           and quote_line_id =pc_quote_line_id
           and applied_flag='Y'
	   and modifier_line_id<>nvl(pc_modifier_line_id,-1)
           AND nvl(expiration_date,sysdate) >= sysdate;

Line: 91

l_api_name               CONSTANT VARCHAR2(30) := 'UPDATE_QUOTE_FROM_DEAL';

Line: 99

l_updated_line_price     NUMBER;

Line: 122

l_last_update_date        ASO_QUOTE_HEADERS_ALL.last_update_date%TYPE;

Line: 203

       aso_debug_pub.add('ASO_DEAL_PUB: ****** Start of Update_Quote_From_Deal API ******', 1, 'Y');

Line: 233

    FETCH C_quote_header INTO l_last_update_date, l_quote_number, l_max_version_flag, l_pricing_status, l_tax_status,l_quote_Status_id,l_price_request_id;

Line: 243

      IF  ((l_access_level <> 'UPDATE') or (l_max_version_flag <> 'Y') or (l_price_request_id is not null)) THEN
         x_return_status := fnd_api.g_ret_sts_error;

Line: 309

  update aso_quote_headers_All
  set last_update_date=l_last_update_date,quote_status_id=ln_quote_Status_id
  where quote_header_id=p_quote_header_id;

Line: 328

   SELECT count(*) into ln_count FROM
   ASO_STATUS_TRANSITIONS_V
   WHERE TO_STATUS_ID = ld_quote_Status_id
   AND FROM_STATUS_ID = l_quote_Status_id;

Line: 350

    update aso_quote_headers_All
    set quote_status_id=ld_quote_Status_id
    where quote_header_id=p_quote_header_id;

Line: 382

  l_qte_header_rec.last_update_date := l_last_update_date;

Line: 388

   aso_debug_pub.add(  'In call update quote from deal API Before Price Adjustment' , 1 ,'Y') ;

Line: 406

    aso_debug_pub.add(  'In call Update Quote From Deal API-Modifier ' || L_modifier_line_id , 1 ,'Y') ;

Line: 412

 OPEN prcadj_cv FOR 'SELECT  UOM_CODE ,CURRENCY_CODE ,ORDERED_QTY ,PRICE,  SOURCE_REF_LINE_ID, SHIP_METHOD_CODE ,PAYMENT_TERM_ID' ||
                   ' FROM QPR_INT_DEAL_V' || l_db_link ||
		  ' WHERE  CHANGED = ' || '''Y''' ||
		  ' AND SOURCE_REF_HEADER_ID =  :p_quote_header_id ' ||
		  ' AND SOURCE_REF_LINE_ID is not null AND SOURCE = 697'
		  USING p_quote_header_id ;

Line: 421

  fetch prcadj_cv into l_uom_code ,l_currency_code ,l_ordered_qty,l_updated_line_price,l_quote_line_id,l_shipment_method_code,l_payment_term_id;

Line: 426

    aso_debug_pub.add(  'In call Update Quote From Deal API-source line id  ' || l_quote_line_id||'Price'||l_updated_line_price , 1 ) ;

Line: 436

    l_qte_line_tbl(i).operation_code  := 'UPDATE';

Line: 453

    aso_debug_pub.add(  'In call Update Quote From Deal API-qty change ' || l_ordered_qty , 1 ) ;

Line: 466

	    aso_debug_pub.add(  'In call update quote from deal API Before Shipment'|| l_ln_shipment_Tbl1.count , 1 ) ;

Line: 481

        l_ln_shipment_rec.operation_code := 'UPDATE' ;

Line: 490

	    aso_debug_pub.add(  'In call update quote from deal API after payment number of rows: '||l_ln_shipment_Tbl.count, 1 ) ;

Line: 499

	    aso_debug_pub.add(  'In call update quote from deal API payment number of rows: '||l_ln_Payment_Tbl1.count||',payment term id'||l_payment_term_id , 1 ) ;

Line: 512

	    aso_debug_pub.add(  'In call update quote from deal API after payment number of rows: '||l_ln_Payment_rec.payment_id, 1 ) ;

Line: 517

	l_ln_Payment_rec.operation_code := 'UPDATE' ;

Line: 524

	    aso_debug_pub.add(  'In call update quote from deal API after payment number of rows: '||l_ln_Payment_Tbl.count, 1 ) ;

Line: 557

   l_Price_Adjustment_Tbl(p_index).UPDATE_ALLOWABLE_FLAG:= cur_auto_adj.UPDATE_ALLOWABLE_FLAG;

Line: 576

   l_Price_Adjustment_Tbl(p_index).updated_flag:= 'Y';

Line: 580

   l_Price_Adjustment_Tbl(p_index).operation_code := 'UPDATE';

Line: 592

if  (l_updated_line_price is not null) and  (l_line_quote_price<>l_updated_line_price) then -- checking if price has been modified or not


/* l_modifier_line_profile:=fnd_profile.value('QPR_DEAL_DIFF_MODIFIER');

Line: 614

    aso_debug_pub.add(  'In call Update Quote From Deal API-Modifier ' || L_modifier_line_id , 1 ,'Y') ;

Line: 620

  select count(*) into l_count_modifier
  FROM aso_price_adjustments apa, Aso_quote_lines_all aqla
    WHERE apa.quote_line_id =  l_quote_line_id
      AND apa.modifier_line_id = l_modifier_line_id
      AND nvl(apa.expiration_date,sysdate) >= sysdate
      AND apa.quote_line_id = aqla.quote_line_id;

Line: 629

    SELECT apa.price_adjustment_id, apa.Applied_flag, apa.Operand, aqla.line_quote_price
    into l_price_adjustment_id,l_applied_flag,l_operand,l_line_quote_price
    FROM aso_price_adjustments apa, Aso_quote_lines_all aqla
    WHERE apa.quote_line_id =  l_quote_line_id
      AND apa.modifier_line_id = l_modifier_line_id
      AND nvl(apa.expiration_date,sysdate) >= sysdate
      AND apa.quote_line_id = aqla.quote_line_id;

Line: 639

      aso_debug_pub.add(  'Modifier NO data found-Dont update this Line for modifier'  , 1,'Y' ) ; -- need to have an error message here

Line: 642

      select name into l_modifier_name
      from qp_list_headers_tl t,qp_list_lines td
      where t.list_header_id = td.list_header_id
      and list_line_id=l_modifier_line_id
      AND t.LANGUAGE(+) = userenv('LANG');

Line: 663

      ld_operand:= (l_line_quote_price+l_operand) - l_updated_line_price;

Line: 666

      ld_operand:= l_line_quote_price - l_updated_line_price ;

Line: 681

   l_Price_Adjustment_Tbl(p_index).updated_flag:= 'Y';

Line: 685

   l_Price_Adjustment_Tbl(p_index).operation_code := 'UPDATE';

Line: 743

  update aso_quote_headers_All
  set last_update_date=l_last_update_date,quote_status_id=ln_quote_Status_id
  where quote_header_id=p_quote_header_id;

Line: 751

     aso_debug_pub.add('ASO_DEAL_PUB before aso_quote_pub.update_quote - '||p_event, 1, 'Y');

Line: 753

     aso_debug_pub.ADD ('Before calling update quote: Setting the single org context to org_id:  '|| l_qte_header_rec.org_id,1,'N');

Line: 759

    aso_quote_pub.update_quote (
      p_api_version_number         => 1.0,
      p_init_msg_list              => fnd_api.g_false,
      p_commit                     => fnd_api.g_false,
      p_control_rec                => l_control_rec,
      p_qte_header_rec             => l_qte_header_rec,
      p_hd_price_attributes_tbl    => aso_quote_pub.g_miss_price_attributes_tbl,
      p_hd_payment_tbl             => aso_quote_pub.g_miss_payment_tbl,
      p_hd_shipment_tbl            => aso_quote_pub.g_miss_shipment_tbl,
      p_hd_freight_charge_tbl      => aso_quote_pub.g_miss_freight_charge_tbl,
      p_hd_tax_detail_tbl          => aso_quote_pub.g_miss_tax_detail_tbl,
      p_qte_line_tbl               => l_qte_line_tbl, -- need to change here
      p_qte_line_dtl_tbl           => aso_quote_pub.g_miss_qte_line_dtl_tbl,
      p_line_attr_ext_tbl          => aso_quote_pub.g_miss_line_attribs_ext_tbl,
      p_line_rltship_tbl           => aso_quote_pub.g_miss_line_rltship_tbl,
      p_price_adjustment_tbl       => l_Price_Adjustment_Tbl,
      p_price_adj_attr_tbl         => aso_quote_pub.g_miss_price_adj_attr_tbl,
      p_price_adj_rltship_tbl      => aso_quote_pub.g_miss_price_adj_rltship_tbl,
      p_ln_price_attributes_tbl    => aso_quote_pub.g_miss_price_attributes_tbl,
      p_ln_payment_tbl             => l_ln_Payment_Tbl,
      p_ln_shipment_tbl            => l_ln_shipment_Tbl,
      p_ln_freight_charge_tbl      => aso_quote_pub.g_miss_freight_charge_tbl,
      p_ln_tax_detail_tbl          => aso_quote_pub.g_miss_tax_detail_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_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_return_status              => x_return_status,
      x_msg_count                  => x_msg_count,
      x_msg_data                   => x_msg_data
    );

Line: 807

    aso_debug_pub.add('ASO_DEAL_PUB:  after Update_Quote', 1, 'Y');

Line: 811

      Update aso_quote_lines_all
      set PRICING_LINE_TYPE_INDICATOR=NULL
      where quote_header_id = p_quote_header_id
      and PRICING_LINE_TYPE_INDICATOR='D';

Line: 902

END Update_Quote_From_Deal;

Line: 920

    t_update_allowed_flag VARCHAR2(1) :='N';

Line: 938

    SELECT nvl(max_version_flag,'N'), price_request_id, nvl(pricing_status_indicator,'C'),quote_number,quote_status_id
     into  l_max_version_flag, l_price_request_id,l_pricing_status,l_quote_number,l_quote_status_id
	     FROM aso_quote_headers_all
	     WHERE quote_header_id = p_quote_header_id;

Line: 950

    IF   l_access_level = 'UPDATE' then

       SELECT quote_status_id INTO t_quote_status_id
	     FROM aso_quote_statuses_b
	     WHERE status_code = 'PRICE APPROVAL PENDING';

Line: 961

	          SELECT update_allowed_flag
                   INTO t_update_allowed_flag
    		    FROM aso_quote_statuses_b
	       	   WHERE quote_status_id = l_quote_status_id;

Line: 968

	            If (t_update_allowed_flag='N' and l_status_override = 'N') then
	              l_access_level:='READ';

Line: 1013

    SELECT nvl(max_version_flag,'N'), price_request_id, nvl(pricing_status_indicator,'C'),quote_number
     into  l_max_version_flag, l_price_request_id,l_pricing_status,l_quote_number
	     FROM aso_quote_headers_all
	     WHERE quote_header_id = p_quote_header_id;

Line: 1025

    IF   l_access_level = 'UPDATE' then


	     if l_max_version_flag='N' then  -- checking for max version
	        l_access_level:='READ';

Line: 1044

   if l_access_level = 'UPDATE' then
    return 'Y';