APPS.OE_ORDER_CLOSE_UTIL SQL Statements

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

Line: 9

 DELETE_ADJUSTMENTS purges the the unapplied adjustments from oe table

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/

PROCEDURE DELETE_ADJUSTMENTS (
        p_header_id                    IN NUMBER DEFAULT NULL
        ,p_line_id                     IN NUMBER DEFAULT NULL
        )
IS
l_adjustment_id        NUMBER;

Line: 24

         SELECT price_adjustment_id
         FROM oe_price_adjustments
         WHERE header_id = p_header_id
         AND list_line_type_code<>'TAX'
         AND automatic_flag = 'N'
         AND applied_flag = 'N'
         AND retrobill_request_id IS NULL; --bug4099565

Line: 35

         SELECT price_adjustment_id
         FROM oe_price_adjustments adj
         WHERE line_id = p_line_id
         AND list_line_type_code<>'TAX'
         AND automatic_flag = 'N'
         AND applied_flag = 'N'
         AND retrobill_request_id IS NULL --bug4099565
         AND not exists( select 1 from oe_price_adj_assocs
                         where rltd_price_adj_id = adj.price_adjustment_id);

Line: 51

     oe_debug_pub.add('ENTERING DELETE_ADJUSTMENTS');

Line: 61

      DELETE FROM oe_price_adj_assocs
      WHERE price_adjustment_id = l_adjustment_id;

Line: 64

      DELETE FROM oe_price_adj_attribs
      WHERE price_adjustment_id = l_adjustment_id;

Line: 67

     DELETE FROM oe_price_adjustments
     WHERE price_adjustment_id = l_adjustment_id;

Line: 87

       DELETE FROM oe_price_adj_assocs
       WHERE price_adjustment_id = l_adjustment_id;

Line: 90

       DELETE FROM oe_price_adj_attribs
       WHERE price_adjustment_id = l_adjustment_id;

Line: 93

       DELETE FROM oe_price_adjustments
       WHERE price_adjustment_id = l_adjustment_id;

Line: 108

             oe_debug_pub.add(  'ERROR: BOTH PARAMETERS ARE NULL IN DELETE_ADJUSTMENTS ' , 1 ) ;

Line: 115

     oe_debug_pub.add('EXITING DELETE_ADJUSTMENTS');

Line: 124

END DELETE_ADJUSTMENTS;

Line: 174

        OE_MSG_PUB.update_msg_context(
         p_entity_code                  => 'HEADER'
        ,p_entity_id                    => l_old_header_rec.header_id
        ,p_header_id                    => l_old_header_rec.header_id
        ,p_line_id                      => null
        ,p_orig_sys_document_ref        => l_old_header_rec.orig_sys_document_ref
        ,p_orig_sys_document_line_ref   => null
        ,p_change_sequence              => l_old_header_rec.change_sequence
        ,p_source_document_id           => l_old_header_rec.source_document_id
        ,p_source_document_line_id      => null
        ,p_order_source_id            => l_old_header_rec.order_source_id
        ,p_source_document_type_id    => l_old_header_rec.source_document_type_id);

Line: 227

	l_header_rec.last_updated_by := NVL(OE_STANDARD_WF.g_user_id, FND_GLOBAL.USER_ID); -- 3169637;

Line: 228

	l_header_rec.last_update_login	:= FND_GLOBAL.LOGIN_ID;

Line: 229

	l_header_rec.last_update_date		:= SYSDATE;

Line: 232

	UPDATE oe_order_headers
	SET open_flag 			= l_header_rec.open_flag
	  , flow_status_code 	= l_header_rec.flow_status_code
	  , last_updated_by		= l_header_rec.last_updated_by
	  , last_update_login	= l_header_rec.last_update_login
	  , last_update_date	= l_header_rec.last_update_date
	  , lock_control         = l_header_rec.lock_control
	WHERE header_id 		= p_header_id;

Line: 251

        DELETE_ADJUSTMENTS(p_header_id => p_header_id);

Line: 257

    OE_ORDER_UTIL.Update_Global_Picture
	(p_Upd_New_Rec_If_Exists =>FALSE
	, p_header_rec		=> l_header_rec
	, p_old_header_rec	=> l_old_header_rec
        , p_header_id 		=> l_header_rec.header_id
        , x_index 		=> l_notify_index
        , x_return_status 	=> x_return_status);

Line: 266

        oe_debug_pub.add(  'UPDATE_GLOBAL RETURN STATUS FOR HDR IS: ' || X_RETURN_STATUS ) ;

Line: 277

    OE_ORDER_UTIL.g_header_rec.last_updated_by:=l_header_rec.last_updated_by;

Line: 278

    OE_ORDER_UTIL.g_header_rec.last_update_login:=l_header_rec.last_update_login;

Line: 279

    OE_ORDER_UTIL.g_header_rec.last_update_date:=l_header_rec.last_update_date;

Line: 336

	-- Added following code to raise OIP status update event for Order Header during closure.
	-- Call to the following procedure added for the bug 13930149
	oe_debug_pub.add('RAISING OIP STATUS CHANGE BUSINESS EVENT FOR ORDER HEADER CLOSURE');

Line: 344

     *  the update_flow_status is getting called directly. So   *
     *  we need to call synch_header_line for 28                *
     ***********************************************************/
     IF l_debug_level  > 0 THEN
        oe_debug_pub.add(  ' GENESIS : -CLOSE ORDER- header rec order source'||l_header_rec.order_source_id);

Line: 355

         select OE_XML_MESSAGE_SEQ_S.nextval
         into l_itemkey_sso
         from dual;

Line: 471

           SELECT cancelled_flag,
                  lock_control,
                  line_id,
                  header_id,
                  order_source_id,
                  orig_sys_document_ref,
                  orig_sys_line_ref,
                  orig_sys_shipment_ref,
                  change_sequence,
                  source_document_type_id,
                  source_document_id,
                  source_document_line_id
           INTO   l_old_line_tbl(1).cancelled_flag,
                  l_old_line_tbl(1).lock_control,
                  l_old_line_tbl(1).line_id,
                  l_old_line_tbl(1).header_id,
                  l_old_line_tbl(1).order_source_id,
                  l_old_line_tbl(1).orig_sys_document_ref,
                  l_old_line_tbl(1).orig_sys_line_ref,
                  l_old_line_tbl(1).orig_sys_shipment_ref,
                  l_old_line_tbl(1).change_sequence,
                  l_old_line_tbl(1).source_document_type_id,
                  l_old_line_tbl(1).source_document_id,
                  l_old_line_tbl(1).source_document_line_id
           FROM   oe_order_lines_all
           WHERE  line_id = p_line_id
           FOR UPDATE NOWAIT;

Line: 499

        OE_MSG_PUB.update_msg_context
        ( p_entity_code                 => 'LINE'
         ,p_entity_id                   => l_old_line_tbl(1).line_id
         ,p_header_id                   => l_old_line_tbl(1).header_id
         ,p_line_id                     => l_old_line_tbl(1).line_id
         ,p_orig_sys_document_ref       => l_old_line_tbl(1).orig_sys_document_ref
         ,p_orig_sys_document_line_ref  => l_old_line_tbl(1).orig_sys_line_ref
         ,p_orig_sys_shipment_ref       => l_old_line_tbl(1).orig_sys_shipment_ref
         ,p_change_sequence             => l_old_line_tbl(1).change_sequence
         ,p_source_document_id          => l_old_line_tbl(1).source_document_id
         ,p_source_document_line_id     => l_old_line_tbl(1).source_document_line_id
         ,p_order_source_id             => l_old_line_tbl(1).order_source_id
         ,p_source_document_type_id     => l_old_line_tbl(1).source_document_type_id);

Line: 527

        OE_MSG_PUB.update_msg_context
        ( p_entity_code                 => 'LINE'
         ,p_entity_id                   => l_old_line_tbl(1).line_id
         ,p_header_id                   => l_old_line_tbl(1).header_id
         ,p_line_id                     => l_old_line_tbl(1).line_id
         ,p_orig_sys_document_ref       => l_old_line_tbl(1).orig_sys_document_ref
         ,p_orig_sys_document_line_ref  => l_old_line_tbl(1).orig_sys_line_ref
         ,p_orig_sys_shipment_ref       => l_old_line_tbl(1).orig_sys_shipment_ref
         ,p_change_sequence             => l_old_line_tbl(1).change_sequence
         ,p_source_document_id          => l_old_line_tbl(1).source_document_id
         ,p_source_document_line_id     => l_old_line_tbl(1).source_document_line_id
         ,p_order_source_id             => l_old_line_tbl(1).order_source_id
         ,p_source_document_type_id     => l_old_line_tbl(1).source_document_type_id);

Line: 615

            SELECT inventory_item_id, org_id
             INTO l_inventory_item_id, l_org_id
              FROM oe_order_lines_all
                WHERE line_id = p_line_id;

Line: 644

	        UPDATE oe_order_lines_all
		   SET flow_status_code='NOTIFY_COSTING_ERROR'
		   WHERE line_id = p_line_id;

Line: 667

            l_line_tbl(1).last_updated_by       := NVL(OE_STANDARD_WF.g_user_id, FND_GLOBAL.USER_ID);

Line: 668

            l_line_tbl(1).last_update_login     := FND_GLOBAL.LOGIN_ID;

Line: 669

            l_line_tbl(1).last_update_date      := SYSDATE;

Line: 683

	            l_line_tbl(1).last_updated_by := NVL(OE_STANDARD_WF.g_user_id, FND_GLOBAL.USER_ID);

Line: 684

	            l_line_tbl(1).last_update_login	:= FND_GLOBAL.LOGIN_ID;

Line: 685

	          l_line_tbl(1).last_update_date	:= SYSDATE;

Line: 690

              UPDATE oe_order_lines
	            SET open_flag 			= l_line_tbl(1).open_flag
                  , calculate_price_flag = l_line_tbl(1).calculate_price_flag
	              , flow_status_code 	= l_line_tbl(1).flow_status_code
	              , last_updated_by		= l_line_tbl(1).last_updated_by
	              , last_update_login	= l_line_tbl(1).last_update_login
	              , last_update_date	= l_line_tbl(1).last_update_date
	              , lock_control         = l_line_tbl(1).lock_control
	          WHERE line_id 		= p_line_id;

Line: 719

            l_line_tbl(1).last_updated_by       := NVL(OE_STANDARD_WF.g_user_id, FND_GLOBAL.USER_ID);

Line: 720

            l_line_tbl(1).last_update_login     := FND_GLOBAL.LOGIN_ID;

Line: 721

            l_line_tbl(1).last_update_date      := SYSDATE;

Line: 733

	    l_line_tbl(1).last_updated_by := NVL(OE_STANDARD_WF.g_user_id, FND_GLOBAL.USER_ID);

Line: 734

	    l_line_tbl(1).last_update_login	:= FND_GLOBAL.LOGIN_ID;

Line: 735

	    l_line_tbl(1).last_update_date	:= SYSDATE;

Line: 739

        UPDATE oe_order_lines
	    SET open_flag 			= l_line_tbl(1).open_flag
          , calculate_price_flag = l_line_tbl(1).calculate_price_flag
	      , flow_status_code 	= l_line_tbl(1).flow_status_code
	      , last_updated_by		= l_line_tbl(1).last_updated_by
	      , last_update_login	= l_line_tbl(1).last_update_login
	      , last_update_date	= l_line_tbl(1).last_update_date
	      , lock_control         = l_line_tbl(1).lock_control
	    WHERE line_id 		= p_line_id;

Line: 764

	OE_ORDER_UTIL.Update_Global_Picture
	(p_Upd_New_Rec_If_Exists =>FALSE
	, p_line_rec		=> l_line_tbl(1)
	, p_old_line_rec	=> l_old_line_tbl(1)
        , p_line_id 		=> l_line_tbl(1).line_id
        , x_index 		=> l_notify_index
        , x_return_status 	=> x_return_status);

Line: 773

        oe_debug_pub.add(  'UPDATE_GLOBAL RET_STATUS FOR LINE_ID '||L_LINE_TBL ( 1 ) .LINE_ID ||' IS: ' || X_RETURN_STATUS , 1 ) ;

Line: 776

        oe_debug_pub.add(  'UPDATE_GLOBAL INDEX FOR LINE_ID '||L_LINE_TBL ( 1 ) .LINE_ID ||' IS: ' || L_NOTIFY_INDEX , 1 ) ;

Line: 790

    OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_updated_by:=	l_line_tbl(1).last_updated_by;

Line: 791

    OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_login:=l_line_tbl(1).last_update_login;

Line: 792

    OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_date:=l_line_tbl(1).last_update_date;

Line: 847

    END IF; -- we should notify after we update

Line: 855

       DELETE_ADJUSTMENTS(p_line_id => p_line_id);

Line: 869

    *  the update_flow_status is getting called directly. So   *
    *  we need to call synch_header_line for 28                *
    ***********************************************************/
/* 10065583  Moved the If condition up */

   IF (OE_GENESIS_UTIL.source_aia_enabled(l_old_line_tbl(1).order_source_id)) THEN
    IF l_debug_level  > 0 THEN
       oe_debug_pub.add(  ' GENESIS : CLOSE LINE  - header rec order source'||p_line_id);

Line: 891

        select OE_XML_MESSAGE_SEQ_S.nextval
	      into l_itemkey_sso
	      from dual;

Line: 898

	      OE_SYNC_ORDER_PVT.INSERT_SYNC_lINE(P_LINE_rec       => l_line_tbl(1),
	                                         p_change_type   => 'LINE_STATUS',
	  	                                     p_req_id        => l_itemkey_sso,
		                                       X_RETURN_STATUS => L_RETURN_STATUS_GEN);