APPS.INV_LPN_RESERVATIONS_PVT SQL Statements

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

Line: 43

        SELECT 1
          INTO l_result
          FROM dual
         WHERE EXISTS (
                       SELECT 1
                         FROM mtl_reservations
                        WHERE organization_id = p_org_id
                          AND inventory_item_id = p_item_id
                          AND (demand_source_line_id <> p_demand_source_line_id
                               OR demand_source_line_id IS NULL)
                          AND lpn_id = p_lpn_id
                       );

Line: 56

        SELECT 1
          INTO l_result
          FROM dual
         WHERE EXISTS (
                       SELECT 1
                         FROM mtl_reservations
                        WHERE organization_id = p_org_id
                          AND inventory_item_id = p_item_id
                          AND (demand_source_header_id <> p_demand_source_header_id
                               OR demand_source_header_id IS NULL)
                          AND lpn_id = p_lpn_id
                       );

Line: 69

        SELECT 1
          INTO l_result
          FROM dual
         WHERE EXISTS (
                       SELECT 1
                         FROM mtl_reservations
                        WHERE organization_id = p_org_id
                          AND inventory_item_id = p_item_id
                          AND (demand_source_name <> p_demand_source_name
                               OR demand_source_name IS NULL)
                          AND lpn_id = p_lpn_id
                      );

Line: 157

      SELECT revision_qty_control_code
           , lot_control_code
           , primary_uom_code
           , secondary_uom_code    -- INVCONV
           , tracking_quantity_ind -- INVCONV
        FROM mtl_system_items
       WHERE inventory_item_id = p_inventory_item_id
         AND organization_id = p_organization_id;

Line: 167

      SELECT   revision
             , lot_number
             , subinventory_code
             , locator_id
             , SUM(primary_transaction_quantity)
             , SUM(secondary_transaction_quantity)    -- INVCONV
          FROM mtl_onhand_quantities_detail
         WHERE lpn_id = p_lpn_id
      GROUP BY revision, lot_number, subinventory_code, locator_id;

Line: 179

    SELECT SUM(primary_transaction_quantity)
      FROM mtl_onhand_quantities_detail
     WHERE lpn_id = p_lpn_id;

Line: 243

      SELECT nvl(ordered_quantity,0),order_quantity_uom
        INTO l_ord_qty,l_order_qty_uom
        FROM oe_order_lines_all
       WHERE line_id = p_demand_source_line_id;

Line: 276

    SELECT nvl(sum(primary_reservation_quantity),0)
      INTO l_reserved_qty
      FROM mtl_reservations
     WHERE demand_source_line_id = p_demand_source_line_id
       AND organization_id = p_organization_id
       AND inventory_item_id = p_inventory_item_id;

Line: 283

    SELECT nvl(sum(primary_reservation_quantity),0)
      INTO l_lpn_reserved_qty
      FROM mtl_reservations
     WHERE demand_source_line_id = p_demand_source_line_id
       AND lpn_id = p_lpn_id
       AND organization_id = p_organization_id
       AND inventory_item_id = p_inventory_item_id
       AND demand_source_line_detail IS NULL;

Line: 536

              debug_print('Calling Update Reservation');

Line: 539

            inv_reservation_pvt.update_reservation(
              p_api_version_number         => 1.0
            , p_init_msg_lst               => fnd_api.g_false
            , x_return_status              => l_return_status
            , x_msg_count                  => l_msg_count
            , x_msg_data                   => l_msg_data
            , p_original_rsv_rec           => l_rsv_tbl(1)
            , p_to_rsv_rec                 => l_rsv_rec
            , p_original_serial_number     => l_dummy_sn
            , p_to_serial_number           => l_dummy_sn
            , p_validation_flag            => fnd_api.g_true
            );

Line: 554

                debug_print('Error from update_reservation');

Line: 560

                debug_print('Unexpected error from update_reservation');

Line: 798

  SELECT reservable_type,lpn_controlled_flag
  INTO l_reservable_type, l_lpn_controlled_flag
  FROM mtl_secondary_inventories
  WHERE secondary_inventory_name LIKE (p_to_subinventory_code)
  AND organization_id = p_organization_id;

Line: 813

	   UPDATE mtl_reservations
           SET subinventory_code = p_to_subinventory_code,
	   locator_id = p_to_locator_id
           WHERE organization_id = p_organization_id
           AND inventory_item_id = p_inventory_item_id
           AND lpn_id IN (SELECT lpn_id
                          FROM wms_license_plate_numbers
                          WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id);  --NESTED LPN ER 7307189

Line: 822

           UPDATE mtl_reservations
           SET subinventory_code = p_to_subinventory_code,
	   locator_id = p_to_locator_id
           WHERE organization_id = p_organization_id
           AND lpn_id IN (SELECT lpn_id
                          FROM wms_license_plate_numbers
                          WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id);  --NESTED LPN ER 7307189

Line: 832

	   UPDATE mtl_reservations
	   SET subinventory_code = p_to_subinventory_code,
	   locator_id = p_to_locator_id,
	   lpn_id = NULL
	   WHERE organization_id = p_organization_id
	   AND inventory_item_id = p_inventory_item_id
	   AND lpn_id IN (SELECT lpn_id
			  FROM wms_license_plate_numbers
			  WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id);   --NESTED LPN ER 7307189

Line: 842

	   UPDATE mtl_reservations
	   SET subinventory_code = p_to_subinventory_code,
	   locator_id = p_to_locator_id,
	   lpn_id = NULL
	   WHERE organization_id = p_organization_id
	   AND lpn_id IN (SELECT lpn_id
			  FROM wms_license_plate_numbers
			  WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id);   --NESTED LPN ER 7307189

Line: 856

	      DELETE FROM mtl_reservations
	       WHERE organization_id = p_organization_id
		 AND inventory_item_id = p_inventory_item_id
		 AND lpn_id IN (SELECT lpn_id
				FROM wms_license_plate_numbers
				WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id);   --NESTED LPN ER 7307189

Line: 863

	    DELETE FROM mtl_reservations
	    WHERE organization_id = p_organization_id
	    AND lpn_id IN (SELECT lpn_id
	                   FROM wms_license_plate_numbers
                           WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id );  --NESTED LPN  ER 7307189

Line: 965

	  SELECT reservable_type,lpn_controlled_flag
	  INTO l_reservable_type, l_lpn_controlled_flag
	  FROM mtl_secondary_inventories
	  WHERE secondary_inventory_name LIKE (p_to_subinventory_code)
	  AND organization_id = p_organization_id;

Line: 980

		      UPDATE mtl_reservations
					SET subinventory_code = p_to_subinventory_code
					  , locator_id = p_to_locator_id
					  , lpn_id=p_transfer_lpn_id
				      WHERE organization_id = p_organization_id
				      AND inventory_item_id = p_inventory_item_id
				      AND lpn_id IN (SELECT lpn_id
							  FROM wms_license_plate_numbers
							WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id )    --nested lpn
				    AND demand_source_line_detail IS NULL;

Line: 991

				  UPDATE mtl_reservations
					SET subinventory_code = p_to_subinventory_code
					  , locator_id = p_to_locator_id
					  , lpn_id=p_transfer_lpn_id
				      WHERE organization_id = p_organization_id
				      AND lpn_id IN (SELECT lpn_id
							  FROM wms_license_plate_numbers
							WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id )    --nested lpn
				    AND demand_source_line_detail IS NULL;

Line: 1003

				UPDATE mtl_reservations
					    SET subinventory_code = p_to_subinventory_code
					      , locator_id = p_to_locator_id
					      , lpn_id=NULL
					  WHERE organization_id = p_organization_id
					  AND inventory_item_id = p_inventory_item_id
					  AND lpn_id IN (SELECT lpn_id
							      FROM wms_license_plate_numbers
							    WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id )    --nested lpn
					  AND demand_source_line_detail IS NULL;

Line: 1014

				UPDATE mtl_reservations
					    SET subinventory_code = p_to_subinventory_code
					      , locator_id = p_to_locator_id
					      , lpn_id=NULL
					  WHERE organization_id = p_organization_id
					  AND lpn_id IN (SELECT lpn_id
							      FROM wms_license_plate_numbers
							    WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id )    --nested lpn
					  AND demand_source_line_detail IS NULL;

Line: 1027

		   --need to delete the reservations
		 IF p_inventory_item_id IS NOT NULL THEN
				    DELETE FROM mtl_reservations
				    WHERE organization_id = p_organization_id
				    AND inventory_item_id = p_inventory_item_id
				    AND lpn_id IN (SELECT lpn_id
						  FROM wms_license_plate_numbers
						WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id );    --nested lpn

Line: 1036

				    DELETE FROM mtl_reservations
				    WHERE organization_id = p_organization_id
				    AND lpn_id IN (SELECT lpn_id
						  FROM wms_license_plate_numbers
						WHERE outermost_lpn_id = p_lpn_id OR lpn_id = p_lpn_id );    --nested lpn