DBA Data[Home] [Help]

APPS.SERIAL_CHECK SQL Statements

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

Line: 41

       , p_update_reservation => fnd_api.g_false
       , success              => l_success
      );
Line: 73

    p_update_reservation VARCHAR2 DEFAULT fnd_api.g_true, /*** {{ R12 Enhanced reservations code changes }} ***/
    success		 IN OUT	NOCOPY NUMBER)
IS
   l_debug NUMBER := 0;
Line: 95

      l_update_count                NUMBER:= 0;  /*** {{ R12 Enhanced reservations code changes ***/
Line: 98

	 SELECT group_mark_id
	   FROM mtl_serial_numbers
	   WHERE current_organization_id = org_id
	   AND inventory_item_id = item_id
	   AND serial_number BETWEEN from_serial_number
	   AND to_serial_number
	   AND LENGTH(serial_number) = Length(from_serial_number)
	   FOR UPDATE OF group_mark_id NOWAIT;
Line: 108

	 SELECT group_mark_id
	   FROM mtl_serial_numbers
	   WHERE current_organization_id = org_id
	   AND inventory_item_id = item_id
	   AND serial_number = from_serial_number
	   FOR UPDATE OF group_mark_id NOWAIT;
Line: 135

	 SELECT COUNT(group_mark_id)
	   INTO marked_numbers_found
	   FROM mtl_serial_numbers
	   WHERE inventory_item_id = item_id
	   AND group_mark_id > 0
	   AND serial_number between from_serial_number
	   AND to_serial_number
	   AND LENGTH(serial_number) = LENGTH(from_serial_number) ;
Line: 156

	    -- Delete the condition of current_organization_id = org_id
	    -- Here is the scenario why we should not consider the current_organization_id = org_id in the
            -- where clause.
            --  Item    Current Organization Serial_Status		 Serial Number   Serial Type
            --  ABC        M1                4 (Issued from Stores)      S1 to S10       Unique in Org
            --  ABC        M2                                                            Unique within Item
            -- Since Serial Number S1 to S10 has status 4 (issued from Stores), user should be able
            -- to chose S1 to S10 for receipt transaction to organization M2, even though the current
            -- organization of serial S1 to S10 is M1.
	    */
	    UPDATE mtl_serial_numbers
	      SET lot_line_mark_id = lot_temp_id,
	      line_mark_id = temp_id,
	      group_mark_id = l_hdr_id,
              reservation_id = nvl(p_reservation_id, reservation_id) /*** {{ R12 Enhanced reservations code changes ***/
	      WHERE inventory_item_id = item_id
	      AND serial_number between from_serial_number
	      AND to_serial_number
	      AND LENGTH(serial_number) = LENGTH(from_serial_number) ;
Line: 175

            l_update_count := SQL%ROWCOUNT;   /*** {{ R12 Enhanced reservations code changes ***/
Line: 189

	 SELECT COUNT(group_mark_id)
	   INTO marked_numbers_found
	   FROM mtl_serial_numbers
	   WHERE inventory_item_id = item_id
	   AND group_mark_id > 0
	   AND serial_number = from_serial_number ;
Line: 207

	    -- Delete the condition of current_organization_id = org_id
	    -- Here is the scenario why we should not consider the current_organization_id = org_id in the
            -- where clause.
            --  Item    Current Organization Serial_Status		 Serial Number   Serial Type
            --  ABC        M1                4 (Issued from Stores)      S1 to S10       Unique in Org
            --  ABC        M2                                                            Unique within Item
            -- Since Serial Number S1 to S10 has status 4 (issued from Stores), user should be able
            -- to chose S1 to S10 for receipt transaction to organization M2, even though the current
            -- organization of serial S1 to S10 is M1.
	    */

	    UPDATE mtl_serial_numbers
	      SET lot_line_mark_id = lot_temp_id,
	      line_mark_id = temp_id,
	      group_mark_id = l_hdr_id,
              reservation_id = nvl(p_reservation_id, reservation_id)   /*** {{ R12 Enhanced reservations code changes ***/
	      WHERE inventory_item_id = item_id
	      AND serial_number = from_serial_number ;
Line: 225

            l_update_count := 1;   /*** {{ R12 Enhanced reservations code changes ***/
Line: 235

      IF (p_update_reservation = fnd_api.g_true and p_reservation_id is not null) THEN
          BEGIN
             update mtl_reservations
             set    serial_reservation_quantity = serial_reservation_quantity + l_update_count
             where  reservation_id = p_reservation_id;
Line: 317

       , p_update_reservation => fnd_api.g_false
      );
Line: 338

  		, p_update_reservation        IN       VARCHAR2 DEFAULT fnd_api.g_true
  		) IS   /*** {{ R12 Enhanced reservations code changes ***/
    unmarked_value                NUMBER := -1;
Line: 355

    l_update_count                NUMBER;
Line: 358

      SELECT reservation_id
           , COUNT(reservation_id)
        FROM mtl_serial_numbers
       WHERE serial_number = from_serial_number AND
             inventory_item_id = p_inventory_item_id AND
             reservation_id IS NOT NULL
      GROUP BY reservation_id;
Line: 367

      SELECT   reservation_id
             , COUNT(reservation_id)
          FROM mtl_serial_numbers
         WHERE serial_number >= NVL(from_serial_number, serial_number) AND
               serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
               inventory_item_id = p_inventory_item_id AND
               reservation_id IS NOT NULL
      GROUP BY reservation_id;
Line: 377

      SELECT   reservation_id
             , COUNT(reservation_id)
          FROM mtl_serial_numbers
         WHERE group_mark_id IN(hdr_id, lot_temp_id, temp_id) AND
               (
                line_mark_id = temp_id OR
                line_mark_id IS NULL OR
                line_mark_id = -1
               ) AND
               (
                lot_line_mark_id = lot_temp_id OR
                lot_line_mark_id IS NULL
                OR lot_line_mark_id = -1
               ) AND
               serial_number >= NVL(from_serial_number, serial_number) AND
               serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
               LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) ) AND
               reservation_id IS NOT NULL
      GROUP BY reservation_id;
Line: 398

      SELECT   reservation_id
             , COUNT(reservation_id)
          FROM mtl_serial_numbers
         WHERE group_mark_id IN(hdr_id, temp_id) AND
               (
                line_mark_id = temp_id OR
                line_mark_id IS NULL OR
                line_mark_id = -1
               ) AND
               serial_number >= NVL(from_serial_number, serial_number) AND
               serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
               LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) ) AND
               reservation_id IS NOT NULL
      GROUP BY reservation_id;
Line: 414

      SELECT   reservation_id
             , COUNT(reservation_id)
          FROM mtl_serial_numbers
         WHERE group_mark_id = hdr_id AND
               serial_number >= NVL(from_serial_number, serial_number) AND
               serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
               LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) ) AND
               reservation_id IS NOT NULL
      GROUP BY reservation_id;
Line: 440

          || ' p_update_reservation = ' || p_update_reservation , 'SERIAL_CHECK');
Line: 452

          inv_log_util.TRACE('Update msn with serial_number= from_serial_number and '
            || 'inventory_item_id = ' || p_inventory_item_id, 'SERIAL_CHECK');
Line: 457

        IF (p_update_reservation = fnd_api.g_true) THEN
          OPEN serial_rsv1;
Line: 468

        IF (p_update_reservation = fnd_api.g_true) THEN
          IF (l_debug = 1) THEN
            inv_log_util.TRACE('Case 1 and p_update_reservation is T', 'inv_unmark_rsv_serial');
Line: 472

          UPDATE mtl_serial_numbers
            SET line_mark_id = unmarked_value
             , group_mark_id = unmarked_value
             , lot_line_mark_id = unmarked_value
             , reservation_id = NULL   /*** {{ R12 Enhanced reservations code changes ***/
            WHERE serial_number = from_serial_number AND inventory_item_id = p_inventory_item_id;
Line: 483

            inv_log_util.TRACE('Case 1 and p_update_reservation it F', 'inv_unmark_rsv_serial');
Line: 485

          UPDATE mtl_serial_numbers
            SET line_mark_id = unmarked_value
             , group_mark_id = unmarked_value
             , lot_line_mark_id = unmarked_value
            WHERE serial_number = from_serial_number AND inventory_item_id = p_inventory_item_id;
Line: 496

          inv_log_util.TRACE('Update msn with serial_number >=  '
            || ' nvl(from_serial_number, serial_number) '
            || ' AND serial_number <=  nvl(to_serial_number, '
            || 'nvl(from_serial_number, serial_number)) AND inventory_item_id= '
            || p_inventory_item_id, 'SERIAL_CHECK');
Line: 504

        IF (p_update_reservation = fnd_api.g_true) THEN
          OPEN serial_rsv2;
Line: 514

        IF (p_update_reservation = fnd_api.g_true) THEN
          IF (l_debug = 1) THEN
            inv_log_util.TRACE('Case 2 and p_update_reservation it T', 'inv_unmark_rsv_serial');
Line: 518

          UPDATE mtl_serial_numbers
            SET line_mark_id = unmarked_value
             , group_mark_id = unmarked_value
             , lot_line_mark_id = unmarked_value
             , reservation_id = NULL   /*** {{ R12 Enhanced reservations code changes ***/
            WHERE serial_number >= NVL(from_serial_number, serial_number) AND
               serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
               inventory_item_id = p_inventory_item_id;
Line: 531

            inv_log_util.TRACE('Case 2 and p_update_reservation it F', 'inv_unmark_rsv_serial');
Line: 533

          UPDATE mtl_serial_numbers
            SET line_mark_id = unmarked_value
             , group_mark_id = unmarked_value
             , lot_line_mark_id = unmarked_value
            WHERE serial_number >= NVL(from_serial_number, serial_number) AND
               serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
               inventory_item_id = p_inventory_item_id;
Line: 543

        END IF;   --END IF p_update_reservation is T
Line: 548

          inv_log_util.TRACE('Update msn with group_mark_id in ('
            || hdr_id || ', ' || lot_temp_id || ', ' || temp_id
            || 'AND (line_mark_id = ' || temp_id
            || ' OR line_mark_id is NULL OR line_mark_id = -1)', 'SERIAL_CHECK');
Line: 560

        IF (p_update_reservation = fnd_api.g_true) THEN
          OPEN serial_rsv3;
Line: 570

        IF (p_update_reservation = fnd_api.g_true) THEN
          IF (l_debug = 1) THEN
            inv_log_util.TRACE('Case 3 and p_update_reservation it T', 'inv_unmark_rsv_serial');
Line: 574

          UPDATE mtl_serial_numbers
           SET line_mark_id = unmarked_value
             , group_mark_id = unmarked_value
             , lot_line_mark_id = unmarked_value
             , reservation_id = NULL   /*** {{ R12 Enhanced reservations code changes ***/
            WHERE group_mark_id IN(hdr_id, lot_temp_id, temp_id)   -- Bug 2491094: Added Temp ID also
            AND
               (
                line_mark_id = temp_id OR line_mark_id IS NULL OR line_mark_id = -1
               ) AND
               (
                lot_line_mark_id = lot_temp_id OR lot_line_mark_id IS NULL OR lot_line_mark_id = -1
               ) AND
               serial_number >= NVL(from_serial_number, serial_number) AND
               serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
               LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) );
Line: 595

            inv_log_util.TRACE('Case 3 and p_update_reservation it F', 'inv_unmark_rsv_serial');
Line: 597

          UPDATE mtl_serial_numbers
           SET line_mark_id = unmarked_value
             , group_mark_id = unmarked_value
             , lot_line_mark_id = unmarked_value
            WHERE group_mark_id IN(hdr_id, lot_temp_id, temp_id) -- Bug 2491094: Added Temp ID also
            AND
               (
                line_mark_id = temp_id OR line_mark_id IS NULL OR line_mark_id = -1
               ) AND
               (
                lot_line_mark_id = lot_temp_id OR lot_line_mark_id IS NULL OR lot_line_mark_id = -1
               ) AND
               serial_number >= NVL(from_serial_number, serial_number) AND
               serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
               LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) );
Line: 620

                 'update msn with group_mark_id in ('|| hdr_id || ', '|| temp_id
              || ' AND (line_mark_id = '|| temp_id || ' OR line_mark_id in (select SERIAL_TRANSACTION_TEMP_ID'
              || ' from mtl_transaction_lots_temp where group_header_id = '|| hdr_id||' and transaction_temp_id = '||temp_id
              || ') OR line_mark_id is NULL or line_mark_id = -1 ) AND '
              || ' serial_number >= nvl(from_serial_number, serial_number) '
              || ' AND serial_number <=  nvl(to_serial_number, nvl(from_serial_number, serial_number)) '
              || ' AND length(serial_number) = length(nvl(from_serial_number, serial_number))', 'SERIAL_CHECK');
Line: 630

          IF (p_update_reservation = fnd_api.g_true) THEN
            OPEN serial_rsv4;
Line: 640

          IF (p_update_reservation = fnd_api.g_true) THEN
            IF (l_debug = 1) THEN
              inv_log_util.TRACE('Case 4 and p_update_reservation it T', 'inv_unmark_rsv_serial');
Line: 644

            UPDATE mtl_serial_numbers
            SET line_mark_id = unmarked_value
               , group_mark_id = unmarked_value
               , lot_line_mark_id = unmarked_value
               , reservation_id = NULL   /*** {{ R12 Enhanced reservations code changes ***/
            WHERE group_mark_id IN(hdr_id, temp_id) AND
                  (line_mark_id = temp_id OR
	                 --Added code for bug 11820774,as for both lot and serial
	                 --controlled item line_mark_id != temp_id
	                   line_mark_id in (select SERIAL_TRANSACTION_TEMP_ID
	                         from mtl_transaction_lots_temp
	                        where group_header_id = hdr_id
	                          and transaction_temp_id = temp_id)
	                 --end fix for bug 11820774
                  OR line_mark_id IS NULL OR line_mark_id = -1
                 ) AND
                 serial_number >= NVL(from_serial_number, serial_number) AND
                 serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
                 LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) );
Line: 668

              inv_log_util.TRACE('Case 4 and p_update_reservation it F', 'inv_unmark_rsv_serial');
Line: 670

            UPDATE mtl_serial_numbers
            SET line_mark_id = unmarked_value
               , group_mark_id = unmarked_value
               , lot_line_mark_id = unmarked_value
            WHERE group_mark_id IN(hdr_id, temp_id) AND
                  (line_mark_id = temp_id OR
	                 --Added code for bug 11820774,as for both lot and serial
	                 --controlled item line_mark_id != temp_id
	                   line_mark_id in (select SERIAL_TRANSACTION_TEMP_ID
	                         from mtl_transaction_lots_temp
	                        where group_header_id = hdr_id
	                          and transaction_temp_id = temp_id)
	                 --end fix for bug 11820774
                  OR line_mark_id IS NULL OR line_mark_id = -1
                 ) AND
                 serial_number >= NVL(from_serial_number, serial_number) AND
                 serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
                 LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) );
Line: 694

            inv_log_util.TRACE('update msn with group_mark_id = '
              || hdr_id || ' AND serial_number >= nvl(from_serial_number, serial_number) '
              || ' AND serial_number <= nvl(to_serial_number, nvl(from_serial_number, serial_number)) '
              || ' AND length(serial_number) = length(nvl(from_serial_number, serial_number))', 'SERIAL_CHECK');
Line: 701

          IF (p_update_reservation = fnd_api.g_true) THEN
            OPEN serial_rsv5;
Line: 711

          IF (p_update_reservation = fnd_api.g_true) THEN
            IF (l_debug = 1) THEN
              inv_log_util.TRACE('Case 5 and p_update_reservation it T', 'inv_unmark_rsv_serial');
Line: 715

            UPDATE mtl_serial_numbers
            SET line_mark_id = unmarked_value
               , group_mark_id = unmarked_value
               , lot_line_mark_id = unmarked_value
               , reservation_id = NULL   /*** {{ R12 Enhanced reservations code changes ***/
            WHERE group_mark_id = hdr_id AND
                 serial_number >= NVL(from_serial_number, serial_number) AND
                 serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
                 LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) );
Line: 729

              inv_log_util.TRACE('Case 5 and p_update_reservation it F', 'inv_unmark_rsv_serial');
Line: 731

            UPDATE mtl_serial_numbers
            SET line_mark_id = unmarked_value
               , group_mark_id = unmarked_value
               , lot_line_mark_id = unmarked_value
            WHERE group_mark_id = hdr_id AND
                 serial_number >= NVL(from_serial_number, serial_number) AND
                 serial_number <= NVL(to_serial_number, NVL(from_serial_number, serial_number) ) AND
                 LENGTH(serial_number) = LENGTH(NVL(from_serial_number, serial_number) );
Line: 742

          END IF;   --END IF p_update_reservation = F
Line: 748

    IF (p_update_reservation = fnd_api.g_true) THEN
      FOR i IN 1 .. l_rsv_id_tbl.COUNT LOOP
        l_update_count  := l_rsv_count_tbl(i) * -1;
Line: 752

          UPDATE mtl_reservations
             SET serial_reservation_quantity = serial_reservation_quantity + l_update_count
           WHERE reservation_id = l_rsv_id_tbl(i);
Line: 767

PROCEDURE inv_update_marked_serial
  ( from_serial_number IN         VARCHAR2,
    to_serial_number   IN         VARCHAR2 DEFAULT NULL,
    item_id            IN         NUMBER,
    org_id             IN         NUMBER,
    temp_id            IN         NUMBER DEFAULT NULL,
    hdr_id             IN         NUMBER DEFAULT NULL,
    lot_temp_id        IN         NUMBER DEFAULT NULL,
    success            OUT NOCOPY BOOLEAN ) IS

    	l_debug NUMBER :=  NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
Line: 780

		inv_log_util.trace('Inside inv_update_marked_serial','SERIAL_CHECK');
Line: 807

			UPDATE mtl_serial_numbers
	        	--SET    group_mark_id = nvl(temp_id, hdr_id)
                        --rtv enhancement
                        SET    group_mark_id = Nvl(hdr_id,temp_id)
                              ,line_mark_id = Decode(hdr_id,NULL,NULL,temp_id)
                              ,lot_line_mark_id = Decode(hdr_id,NULL,NULL,lot_temp_id)
	      		WHERE  inventory_item_id = item_id
			AND    current_organization_id = org_id
	      		AND    serial_number = from_serial_number ;
Line: 832

			UPDATE mtl_serial_numbers
	      		--SET    group_mark_id = nvl(temp_id, hdr_id)
                        --rtv enhancement
                        SET    group_mark_id = Nvl(hdr_id,temp_id)
                              ,line_mark_id = Decode(hdr_id,NULL,NULL,temp_id)
                              ,lot_line_mark_id = Decode(hdr_id,NULL,NULL,lot_temp_id)
	      		WHERE  inventory_item_id = item_id
			AND    current_organization_id = org_id
	      		AND    serial_number between from_serial_number AND to_serial_number
	      		AND    LENGTH(serial_number) = LENGTH(from_serial_number) ;
Line: 862

END inv_update_marked_serial;