APPS.OE_SALES_CAN_UTIL SQL Statements

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

Line: 29

Procedure UpdateLine
          (p_line_id           In  Number
          ,p_ordered_quantity  In  Number
          ,p_change_reason     In Varchar2
          ,p_change_comments   In Varchar2
,x_return_status out nocopy Varchar2

,x_msg_count out nocopy Number

,x_msg_data out nocopy Varchar2)

Is
l_api_name                    VARCHAR2(30) := 'UPDATELINE';

Line: 56

      oe_debug_pub.add(  'ENTERING OE_SALES_CAN_UTIL.UPDATE_LINE' ) ;

Line: 110

  l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;

Line: 151

        oe_debug_pub.ADD('Update Line Process Order return UNEXP_ERROR');

Line: 154

        oe_debug_pub.ADD('Update Line Process Order return RET_STS_ERROR');

Line: 162

      oe_debug_pub.add(  'EXITING OE_SALES_CAN_UTIL.UPDATE_LINE' ) ;

Line: 188

END UpdateLine;

Line: 232

             SELECT  PRL.REQUISITION_LINE_ID
             FROM    PO_REQUISITION_LINES PRL,
                     PO_REQUISITION_HEADERS PRH
             WHERE   PRH.SEGMENT1 = p_line_rec.source_document_id
             AND     PRL.LINE_NUM =  p_line_rec.source_document_line_id
             AND     PRL.REQUISITION_HEADER_ID =PRH.REQUISITION_HEADER_ID;*/

Line: 271

            lhisttypecode := 'QUANTITY UPDATE';

Line: 274

        lhisttypecode := 'QUANTITY UPDATE';

Line: 283

            lhisttypecode := 'QUANTITY UPDATE';

Line: 286

        lhisttypecode := 'QUANTITY UPDATE';

Line: 293

    ** update service not called when order is being cancelled.
    if NOT(g_ord_lvl_can) then
    */

    /*
    ** Fix Bug # 2157850
    ** Store the g_require_reason global before calling update
    ** service and restore after that.
    */
    l_require_reason := oe_sales_can_util.g_require_reason;

Line: 305

        oe_debug_pub.add('Before calling update_service()',1) ;

Line: 308

    update_service(p_line_rec, p_old_line_rec, x_return_status);

Line: 311

        oe_debug_pub.add('After calling update_service , return status : '||x_return_status,1) ;

Line: 366

	       	   SELECT quantity
	             INTO l_mtl_supply_quantity
	             FROM mtl_supply
	            WHERE req_line_id = p_line_rec.source_document_line_id
	              AND supply_type_code = 'REQ';

Line: 376

	              fnd_message.set_name('ONT','OE_CAN_UPDATE_SUPPLY');

Line: 383

	           fnd_message.set_name('ONT','OE_CAN_UPDATE_SUPPLY');

Line: 394

                         p_action        => 'Update_Req_Line_Qty',
                         p_recreate_flag => FALSE,
                         p_qty           => p_line_rec.ordered_quantity,
                         p_receipt_date  => null,
			 p_reservation_action=>'UPDATE_SO_QUANTITY',
			 p_ordered_uom	 => l_order_quantity_uom);

Line: 646

                p_request_type               => OE_GLOBALS.G_DELETE_CHARGES,
                x_return_status              => l_return_status);

Line: 837

      ** Following columns are updated when WF Handle error is called.
      p_x_line_rec.open_flag := 'N';

Line: 921

PROCEDURE update_service
( p_line_rec      IN  OE_Order_PUB.Line_Rec_Type
, p_old_line_rec  IN  OE_Order_PUB.Line_Rec_Type := OE_Order_PUB.G_MISS_LINE_REC
, x_return_status OUT NOCOPY VARCHAR2

)
IS
l_api_name          VARCHAR2(30):= 'Update_Service';

Line: 941

SELECT  LINE_ID, ORDERED_QUANTITY
FROM    oe_order_lines
WHERE  service_reference_line_id = p_line_rec.line_id
AND    service_reference_type_code = 'ORDER' -- these two conditions added for bug 2946327
AND    nvl(cancelled_flag, 'N') <> 'Y';

Line: 953

      oe_debug_pub.add(  'ENTERING OE_SALES_CAN_UTIL.UPDATE_SERVICE' ) ;

Line: 966

        oe_debug_pub.add(  'BEFORE CALLING UPDATELINE FOR LINE ID: '|| L_LINE_ID ) ;

Line: 969

    /* Call the UpdateLine only if ordered_quantity is greater than zero */
    IF( l_service_quantity <> 0 )then
		Updateline(l_line_id,
			   l_ordered_quantity,
                           l_change_reason,
                           l_change_comments,
                           l_return_status,
			   x_msg_count,
			   x_msg_data);

Line: 981

        oe_debug_pub.add(  'AFTER CALLING UPDATELINE , RETURN STATUS: '|| L_RETURN_STATUS ) ;

Line: 995

      oe_debug_pub.add(  'EXITING OE_SALES_CAN_UTIL.UPDATE_SERVICE' ) ;

Line: 1018

end update_service;

Line: 1039

  select nvl(ordered_quantity, 0)
  ,      nvl(cancelled_quantity, 0)
  into   l_old_ord_quantity
  ,      l_old_can_quantity
  from   oe_order_lines
  where  line_id = p_line_rec.line_id;

Line: 1074

** order right after quantity on the lines was updated to 0.
** Need to send the Cancelled Qty as Zero in such instances.
*/
IF p_line_rec.ordered_quantity = 0 AND
   p_old_line_rec.ordered_quantity = 0 AND
   OE_OE_FORM_CANCEL_LINE.g_ord_lvl_can THEN

  RETURN 0;

Line: 1258

     OE_Header_Adj_Util.Delete_Header_Charges( p_header_id  => p_header_rec.header_id );

Line: 1270

            oe_debug_pub.add(  'LINE SELECTED FOR CANCELLATION , ID: '|| L_LINE_OLD_TBL ( J ) .LINE_ID , 1 ) ;

Line: 1278

            select 'Y'
            into   l_service_parent_exists
            from   oe_order_lines
            where  header_id = l_line_old_tbl(j).header_id
            and    line_id   = l_line_old_tbl(j).service_reference_line_id;

Line: 1300

          select count(*)
          into   l_prg_line_count
          from   oe_price_adjustments opa1,
                 oe_price_adjustments opa2,
                 oe_price_adj_assocs opaa
          where  opa1.list_line_type_code = 'PRG'
          and    opa1.price_adjustment_id = opaa.price_adjustment_id
          and    opa2.price_adjustment_id = opaa.rltd_price_adj_id
          and    opa2.line_id             = l_line_old_tbl(j).line_id;

Line: 1329

        l_line_tbl(i).operation := OE_GLOBALS.G_OPR_UPDATE;

Line: 1362

        oe_debug_pub.add(  'UPDATE LINE PROCESS ORDER RETURN UNEXP_ERROR' ) ;

Line: 1367

        oe_debug_pub.add(  'UPDATE LINE PROCESS ORDER RETURN RET_STS_ERROR' ) ;

Line: 1683

  SELECT DISTINCT OS.SET_ID
  FROM oe_order_lines ol, oe_sets OS, oe_line_sets ols
  WHERE ol.header_id  = p_header_id
    and OS.HEADER_ID  = ol.header_id
    and ol.line_id    = ols.line_id
    and ols.set_id    = OS.set_id
    and OS.SET_TYPE   = 'FULFILLMENT_SET'
    and OS.SET_STATUS in ('A','T')
    and ol.cancelled_flag <> 'Y'
    and ol.open_flag  = 'Y';

Line: 1705

    SELECT count(1) INTO l_lines_in_set
    FROM oe_order_lines ol, oe_line_sets ols
    WHERE ol.header_id  = p_header_id
      AND ols.set_id    = C_Set.set_id
      AND ol.line_id    = ols.line_id
      AND ol.cancelled_flag <> 'Y'
      AND ol.open_flag  = 'Y';

Line: 1719

      SELECT count(1) INTO l_lines_awaiting
      FROM oe_order_lines ol, oe_line_sets ols,
           wf_item_activity_statuses WIAS,
           wf_process_activities WPA
      WHERE ol.header_id = p_header_id
        AND ols.set_id = C_Set.set_id
        AND ols.line_id = ol.line_id
        AND WPA.activity_name     = 'FULFILL_LINE'
        AND WIAS.item_type        = 'OEOL'
        AND WIAS.item_key         = to_char(ol.line_id)
        AND WIAS.activity_status  = 'NOTIFIED'
        AND WIAS.Process_Activity = WPA.instance_id;

Line: 1738

        SELECT WIAS.Process_Activity, ol.line_id
        INTO l_activity_id, l_line_id
        FROM oe_order_lines ol, oe_line_sets ols,
             wf_item_activity_statuses WIAS,
             wf_process_activities WPA
        WHERE ol.header_id = p_header_id
          AND ols.set_id = C_Set.set_id
          AND ols.line_id = ol.line_id
          AND WPA.activity_name     = 'FULFILL_LINE'
          AND WIAS.item_type        = 'OEOL'
          AND WIAS.item_key         = to_char(ol.line_id)
          AND WIAS.activity_status  = 'NOTIFIED'
          AND WIAS.Process_Activity = WPA.instance_id
          AND ROWNUM = 1;

Line: 1902

            oe_debug_pub.add(  'LINE SELECTED FOR CANCELLATION , ID: '|| L_LINE_OLD_TBL ( I ) .LINE_ID , 1 ) ;

Line: 1910

            select 'Y'
            into   l_service_parent_exists
            from   oe_order_lines
            where  header_id = l_line_old_tbl(i).header_id
            and    line_id   = l_line_old_tbl(i).service_reference_line_id;

Line: 1932

          select count(*)
          into   l_prg_line_count
          from   oe_price_adjustments opa1,
                 oe_price_adjustments opa2,
                 oe_price_adj_assocs opaa,
                 oe_order_lines_all ol --bug 4156493
          where  opa1.list_line_type_code = 'PRG'
          and    opa1.price_adjustment_id = opaa.price_adjustment_id
          and    opa2.price_adjustment_id = opaa.rltd_price_adj_id
          and    opa2.line_id             = l_line_old_tbl(i).line_id
          --bug 4156493
          and    opa1.line_id            = ol.line_id
          and    ol.shipped_quantity is null;

Line: 1965

        l_line_tbl(j).operation := OE_GLOBALS.G_OPR_UPDATE;

Line: 1970

        ** Following column will be updated in Check_Constraints()
        ** procedure if line reaches the cancellation point.
        l_line_tbl(j).cancelled_flag := 'Y';

Line: 1976

        ** Following columns are updated when WF Handle error is called.
        */
        --l_line_tbl(j).flow_status_code := 'CANCELLED';

Line: 2080

select ordered_quantity from
oe_order_lines_all
where line_id = x_request_rec.entity_id;

Line: 2085

select ordered_quantity from
oe_order_lines_all
where header_id = x_request_rec.entity_id
and ordered_quantity <> 0;

Line: 2102

         OE_DEBUG_PUB.ADD('Before select ');

Line: 2127

         OE_DEBUG_PUB.ADD('before select Cursoer C1 ');

Line: 2136

		Update oe_order_lines_all
		set cancelled_flag = 'Y' where
		line_id = x_request_rec.entity_id;

Line: 2204

  select nvl(ordered_quantity2, 0)
  ,      nvl(cancelled_quantity2, 0)
  into   l_old_ord_quantity2
  ,      l_old_can_quantity2
  from   oe_order_lines
  where  line_id = p_line_rec.line_id;

Line: 2239

** order right after quantity on the lines was updated to 0.
** Need to send the Cancelled Qty as Zero in such instances.
*/
IF l_debug_level  > 0 THEN
    			  oe_debug_pub.add(  'in Cal_Cancelled_Qty2 2 ') ;