DBA Data[Home] [Help]

APPS.OE_SPLIT_UTIL SQL Statements

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

Line: 20

	   p_x_line_tbl(I).operation = oe_globals.g_opr_update THEN
           IF l_debug_level  > 0 THEN
               oe_debug_pub.add(  'BEFORE CREATING SET : ' ||P_X_LINE_TBL ( I ) .LINE_ID ) ;
Line: 48

Procedure Update_Drop_Ship_Source(p_line_tbl IN OE_ORDER_PUB.Line_Tbl_Type) IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
Line: 59

        UPDATE oe_drop_ship_sources
        SET    line_id = p_line_tbl(I).line_id
        WHERE  line_id = p_line_tbl(I).split_from_line_id;
Line: 69

End Update_Drop_Ship_Source;
Line: 79

       SELECT ORDERED_QUANTITY
              , HEADER_ID
              , LINE_ID
       FROM   OE_ORDER_LINES_ALL
       WHERE  SERVICE_REFERENCE_LINE_ID = l_line_id
       AND    ITEM_TYPE_CODE = 'SERVICE'
       AND    SERVICE_REFERENCE_TYPE_CODE = 'ORDER'
       AND    OPEN_FLAG <> 'N'  -- Bug 7555831 and 7555832
       AND    NVL(CANCELLED_FLAG,'N') <> 'Y';
Line: 94

       OE_DEBUG_PUB.add('#5112495, select service reference type code ORDER only',1);
Line: 98

	IF  p_line_tbl(I).operation = OE_GLOBALS.G_OPR_UPDATE THEN
	    l_line_id :=  p_line_tbl(I).line_id;
Line: 112

	    IF  p_line_tbl(I).operation = OE_GLOBALS.G_OPR_UPDATE THEN
		l_ser_line_tbl(l_service_count).split_action_code := 'SPLIT';
Line: 168

       SELECT line_id FROM oe_order_lines_all
       WHERE line_set_id=  p_line_set_id;
Line: 177

       SELECT  NVL(MAX(SHIPMENT_NUMBER)+1,1)
       INTO    l_ship_number
       FROM    OE_ORDER_LINES
       WHERE   HEADER_ID = p_line_rec.header_id
       AND     LINE_NUMBER = p_line_rec.line_number;
Line: 194

          SELECT line_set_id
          INTO l_line_set_id
          FROM oe_order_lines
          WHERE line_id=p_line_rec.top_model_line_id;
Line: 200

	  SELECT  NVL(MAX(SHIPMENT_NUMBER)+1,1)
          INTO    l_ship_number
          FROM    OE_ORDER_LINES
          WHERE   HEADER_ID = p_line_rec.header_id
          AND     LINE_NUMBER = p_line_rec.line_number
	  AND Nvl(option_number,-1)=Nvl(p_line_rec.option_number,-1)
          AND Nvl(component_number,-1)=Nvl(p_line_rec.component_number,-1)
          AND Nvl(service_number,-1)=Nvl(p_line_rec.service_number,-1);
Line: 218

           SELECT  NVL(MAX(SHIPMENT_NUMBER),-1)
                 INTO l_chk_ship_number
                 FROM oe_order_lines
                 WHERE top_model_line_id=i.line_id
                 AND line_number=p_line_rec.line_number
                 AND Nvl(option_number,-1)=Nvl(p_line_rec.option_number,-1)
                 AND Nvl(component_number,-1)=Nvl(p_line_rec.component_number,-1)
                 AND Nvl(service_number,-1)=Nvl(p_line_rec.service_number,-1);
Line: 246

       SELECT shipment_number
       INTO   l_ship_number
       FROM   oe_order_lines
       WHERE  line_id = p_line_rec.top_model_line_id;
Line: 271

       SELECT ORDERED_QUANTITY
              , HEADER_ID
              , LINE_ID
       FROM   OE_ORDER_LINES_ALL
       WHERE  TOP_MODEL_LINE_ID = l_line_id
       AND    LINE_ID <> l_line_id
       AND    NVL(SHIPPABLE_FLAG,'N')='Y';
Line: 327

       SELECT ORDERED_QUANTITY
              , HEADER_ID
              , LINE_ID
       FROM   OE_ORDER_LINES_ALL
       WHERE  SERVICE_REFERENCE_LINE_ID = l_line_id
       AND    ITEM_TYPE_CODE = 'SERVICE'
       AND    SERVICE_REFERENCE_TYPE_CODE = 'ORDER'
       AND    OPEN_FLAG <> 'N' -- Bug 6710212
       AND    NVL(CANCELLED_FLAG,'N') <> 'Y';
Line: 360

    l_ser_line_tbl(l_service_count).operation := oe_globals.g_opr_update;
Line: 434

l_insert_quantity       NUMBER := 0;
Line: 435

l_update_quantity       NUMBER := 0;
Line: 436

option_updated          BOOLEAN := FALSE;
Line: 440

l_option_not_updated    BOOLEAN := TRUE;
Line: 441

l_update_line_reqd      BOOLEAN := TRUE;
Line: 446

       SELECT ORDERED_QUANTITY,
              HEADER_ID,
              LINE_ID
       FROM   OE_ORDER_LINES_ALL
       WHERE  TOP_MODEL_LINE_ID = l_line_id
       AND    LINE_ID <> l_line_id
       AND    NVL(CANCELLED_FLAG,'N')<> 'Y'
       ORDER  BY LINE_ID;
Line: 460

       SELECT ORDERED_QUANTITY,
              HEADER_ID,
              LINE_ID
       FROM   OE_ORDER_LINES_ALL
       WHERE  ATO_LINE_ID =  l_config_ato_line_id
       AND    ITEM_TYPE_CODE <> 'CONFIG'
       AND    NVL(CANCELLED_FLAG,'N')<> 'Y'
       ORDER  BY LINE_ID;
Line: 502

   l_parent_line.operation := oe_globals.g_opr_update;
Line: 515

       option_updated := FALSE;
Line: 522

              x_line_tbl(I).operation := oe_globals.g_opr_update;
Line: 524

	      option_updated := TRUE;
Line: 531

	IF not option_updated THEN
           l_option_count := l_option_count + 1;
Line: 538

           l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_update;
Line: 542

	END IF; -- Option not updated
Line: 543

	option_updated := false;
Line: 648

      l_parent_line.operation := oe_globals.g_opr_update;
Line: 848

    option_updated := FALSE;
Line: 849

    l_insert_quantity := 0;
Line: 852

	   l_insert_quantity := p_line_tbl(I).shipped_quantity;
Line: 853

	   option_updated := true;
Line: 873

    IF  l_insert_quantity = 0 AND g_max_model > 0  THEN
	l_insert_quantity := g_max_model *l_model_ratio;
Line: 876

    l_update_quantity := l_option_line.ordered_quantity - g_min_model * l_model_ratio - l_insert_quantity ;
Line: 878

    	   oe_debug_pub.add('Update Quantity : '||l_update_quantity,5) ;
Line: 881

	   oe_debug_pub.add('Insert Quantity : ' || l_insert_quantity,5);
Line: 883

    IF l_update_quantity > 0 THEN
	   l_option_count := l_option_count + 1;
Line: 885

	   IF NOT option_updated  AND g_max_model = 0 THEN
	      l_option_line_tbl(l_option_count) := l_option_line;
Line: 887

       	      l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_update;
Line: 932

	   END IF; -- Option updated or g max model
Line: 934

           l_option_line_tbl(l_option_count).ordered_quantity := l_update_quantity;
Line: 935

     END IF; -- Update Quantity
Line: 936

     option_updated := false;
Line: 1005

l_insert_quantity            number := 0;
Line: 1006

l_update_quantity            number := 0;
Line: 1007

option_updated               BOOLEAN := FALSE;
Line: 1011

l_option_not_updated         boolean := TRUE;
Line: 1012

l_update_line_reqd           boolean := TRUE;
Line: 1018

       SELECT ORDERED_QUANTITY,
              HEADER_ID,
              LINE_ID
       FROM   OE_ORDER_LINES_ALL
       WHERE  TOP_MODEL_LINE_ID = l_line_id
       AND    LINE_ID <> l_line_id
       AND    NVL(CANCELLED_FLAG,'N') <> 'Y'
       ORDER  BY LINE_ID;
Line: 1044

       l_parent_line.operation := oe_globals.g_opr_update;
Line: 1058

		   option_updated := FALSE;
Line: 1062

                  x_line_tbl(I).operation := OE_GLOBALS.g_opr_update;
Line: 1064

				  option_updated := TRUE;
Line: 1068

		   IF NOT option_updated THEN
              l_option_count := l_option_count + 1;
Line: 1075

              l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_update;
Line: 1079

           END IF; -- Option not updated
Line: 1080

     	   option_updated := false;
Line: 1295

        option_updated := FALSE;
Line: 1296

	l_insert_quantity := 0;
Line: 1299

      	       l_insert_quantity := p_line_tbl(I).shipped_quantity;
Line: 1300

	       option_updated := true;
Line: 1304

               l_insert_quantity := (g_max_model*l_model_ratio);
Line: 1307

	l_update_quantity := l_option_line.ordered_quantity - g_min_model * l_model_ratio - l_insert_quantity ;
Line: 1309

           oe_debug_pub.add(  'UPDATE QUANTITY : ' || L_UPDATE_QUANTITY ) ;
Line: 1312

	   oe_debug_pub.add(  'INSERT QUANTITY : ' || L_INSERT_QUANTITY ) ;
Line: 1314

	IF l_update_quantity > 0 THEN
	   l_option_count := l_option_count + 1;
Line: 1316

           IF NOT option_updated  AND g_max_model = 0 THEN
              l_option_line_tbl(l_option_count) := l_option_line;
Line: 1318

       	      l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_update;
Line: 1344

           END IF; -- Option updated or g max model
Line: 1346

           l_option_line_tbl(l_option_count).ordered_quantity := l_update_quantity;
Line: 1347

        END IF; -- Update Quantity
Line: 1348

	option_updated := false;
Line: 1430

        SELECT ORDERED_QUANTITY,
               HEADER_ID,
               LINE_ID
        FROM   OE_ORDER_LINES_ALL
        WHERE  ATO_LINE_ID = l_line_id
        AND    LINE_ID <> l_line_id
        AND    NVL(CANCELLED_FLAG,'N')<>'Y'
        ORDER BY LINE_ID;
Line: 1440

        SELECT ORDERED_QUANTITY,
               HEADER_ID,
               LINE_ID
        FROM   OE_ORDER_LINES_ALL
        WHERE  TOP_MODEL_LINE_ID = l_line_id
        AND    LINE_ID <> l_line_id
        AND    NVL(CANCELLED_FLAG,'N') <> 'Y'
        ORDER BY LINE_ID;
Line: 1485

      l_line_scredit_tbl.delete;
Line: 1501

      l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_update;
Line: 1573

      l_split_line_tbl.delete;
Line: 1594

	        IF X_LINE_TBL(J).OPERATION = OE_GLOBALS.G_OPR_UPDATE THEN
		  IF X_LINE_TBL(J).LINE_ID = X_LINE_TBL(I).SPLIT_FROM_LINE_ID THEN
		    FOR K in 1..x_line_tbl.count LOOP
		      IF X_LINE_TBL(J).LINK_TO_LINE_ID = X_LINE_TBL(K).SPLIT_FROM_LINE_ID AND
		        X_LINE_TBL(I).TOP_MODEL_LINE_ID = X_LINE_TBL(K).TOP_MODEL_LINE_ID THEN
		        X_LINE_TBL(I).LINK_TO_LINE_ID := X_LINE_TBL(K).LINE_ID;
Line: 1983

	     l_line_tbl(I).operation = OE_GLOBALS.G_OPR_UPDATE
          THEN
             /* Added this condition to spuress the spliting of the line with zero quantiy.
                Bug: 3318920
                By: Srini
             */
             IF l_line_tbl(I).ordered_quantity = 0
             THEN
                IF l_debug_level  > 0 THEN
                   oe_debug_pub.add(  'ORDERED QUANTITY IS INVALID with UPDATE OPER => '|| l_line_tbl(I).ordered_quantity ) ;
Line: 1998

                   oe_debug_pub.add(  'SPLIT- NOT ALLOWED TO UPDATE WITH QUANTITY ZERO' ) ;
Line: 2089

     	         l_line_scredit_tbl.delete;
Line: 2148

       	         END LOOP; -- Loop for Insert on line table
Line: 2226

     		 -- Delete split line tbl for this line
	         g_split_line_tbl.delete;
Line: 2229

	  END IF; -- Operation Update
Line: 2251

		-- Update Drop Ship Sources
		--Update_Drop_Ship_Source(p_line_tbl => X_Line_Tbl);
Line: 2454

	l_line_scredit_temp_tbl.delete;
Line: 2586

Cursor C1 is SELECT SET_ID
             FROM   OE_LINE_SETS
             WHERE  LINE_ID = p_line_rec.split_from_line_id;
Line: 2668

  SELECT * FROM oe_line_split_details
  WHERE  line_id = l_init_line_id
  AND    request_id = FND_GLOBAL.CONC_REQUEST_ID
  FOR UPDATE NOWAIT;
Line: 2678

   select org_id
   into l_org_id
   from oe_order_lines_all
   where line_id=l_init_line_id;
Line: 2705

  select count(*) into l_count
  from   oe_line_split_details where line_id = l_init_line_id
  AND    request_id = FND_GLOBAL.CONC_REQUEST_ID;
Line: 2720

  SELECT sum(ORDERED_QUANTITY), sum(ORDERED_QUANTITY2)
  INTO   l_ord_qty_sum, l_ord_qty2_sum
  FROM   oe_line_split_details
  WHERE  line_id = l_init_line_id
  AND    request_id = FND_GLOBAL.CONC_REQUEST_ID;
Line: 2726

  SELECT ordered_quantity, ordered_quantity2
  INTO   l_orig_ord_qty, l_orig_ord_qty2
  FROM   oe_order_lines_all
  WHERE  open_flag = 'Y'
  AND    line_id = l_init_line_id;
Line: 2784

        l_x_line_tbl(i).operation := OE_GLOBALS.G_OPR_UPDATE;
Line: 2885

    l_line_tbl.delete;
Line: 2905

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

            l_line_tbl(l_rec_count).operation := OE_GLOBALS.G_OPR_UPDATE;
Line: 3016

     l_x_Header_Adj_tbl.DELETE;
Line: 3017

     l_x_header_price_att_tbl.DELETE;
Line: 3018

     l_x_Header_Adj_att_tbl.DELETE;
Line: 3019

     l_x_Header_Adj_Assoc_tbl.DELETE;
Line: 3020

     l_x_Header_Scredit_tbl.DELETE;
Line: 3021

     l_x_Line_Adj_tbl.DELETE;
Line: 3022

     l_x_Line_Price_att_tbl.DELETE;
Line: 3023

     l_x_Line_Adj_att_tbl.DELETE;
Line: 3024

     l_x_Line_Adj_Assoc_tbl.DELETE;
Line: 3025

     l_x_Line_Scredit_tbl.DELETE;
Line: 3026

     l_x_lot_serial_tbl.DELETE;
Line: 3067

  DELETE FROM oe_line_split_details
  WHERE  line_id = l_init_line_id
  AND    request_id = FND_GLOBAL.CONC_REQUEST_ID;
Line: 3130

    DELETE FROM oe_line_split_details
    WHERE  line_id = l_init_line_id
    AND    request_id = FND_GLOBAL.CONC_REQUEST_ID;
Line: 3136

PROCEDURE Bulk_Insert (p_line_conc_tbl IN Split_Line_Tbl_Type) is

begin
       oe_debug_pub.add('entering oe_split_util.bulk_insert');
Line: 3142

                INSERT INTO OE_LINE_SPLIT_DETAILS
                VALUES p_line_conc_tbl(j);
Line: 3147

 oe_debug_pub.add('failed in bulk insert API'||sqlerrm);
Line: 3149

end Bulk_Insert;