APPS.CSI_GIS_INSTANCE_LOC_PUB SQL Statements

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

Line: 26

/*Procedure to create or update asset/instance geolocation latitude and longitude values*/
PROCEDURE CREATEUPDATE_INST_GEOLOC_INFO
(
    p_api_version                IN           NUMBER
   ,p_commit	    	         IN           VARCHAR2 := FND_API.G_FALSE
   ,p_CSI_instance_geoloc_tbl    IN           CSI_GIS_INSTANCE_LOC_PUB.csi_instance_geoloc_tbl_type
   ,p_asset_context              IN           VARCHAR2 :='EAM'
   ,x_return_status              OUT  NOCOPY  VARCHAR2
   ,x_msg_count		             OUT  NOCOPY  NUMBER
   ,x_msg_data	                 OUT  NOCOPY  VARCHAR2
 ) IS
   l_api_name                      CONSTANT VARCHAR2(30)   := 'CREATEUPDATE_INST_GEOLOC_INFO';

Line: 46

   l_create_update  VARCHAR2(1);

Line: 55

    SAVEPOINT CREATEUPDATE_INST_GEOLOC_INFO;

Line: 77

     csi_t_gen_utility_pvt.add('In createupdate_inst_geoloc_info');

Line: 114

		    ,x_create_update => l_create_update
		    ,x_return_status => l_return_status
	     	,x_msg_count     => l_msg_count
            ,x_msg_data      => l_msg_data
	     	);

Line: 123

				IF l_create_update = 'U' THEN
				   IF (l_debug_level > 0) THEN
                      csi_t_gen_utility_pvt.add( 'Invalidating Instance geo location info');

Line: 129

		           CSI_GIS_INSTANCE_GEO_LOC_PVT.UPDATE_ROW
                    ( p_instance_id    => p_csi_instance_geoloc_tbl(i).instance_id
                    ,p_inst_latitude  => l_inst_latitude_dd_value
                    ,p_inst_longitude => l_inst_longitude_dd_value
			        ,p_valid_flag     => 'N'
                    ,x_return_status => l_return_status
                    );

Line: 142

				          UPDATE csi_ii_geoloc_interface
				          SET process_flag = 'E',
					          error_message=l_msg_data,
						      process_date=sysdate
			              WHERE  instance_number = (select instance_number
					                          FROM CSI_ITEM_INSTANCES
                                              WHERE instance_id = p_csi_instance_geoloc_tbl(i).instance_id)
				          AND PROCESS_FLAG = 'R';

Line: 178

	    	  	csi_t_gen_utility_pvt.add( 'l_create_update:'||l_create_update);

Line: 188

				   UPDATE csi_ii_geoloc_interface
				      SET process_flag = 'E',
					      error_message=l_msg_data,
						  process_date=sysdate
			       WHERE  instance_number = (select instance_number
					                          FROM CSI_ITEM_INSTANCES
                                              WHERE instance_id = p_csi_instance_geoloc_tbl(i).instance_id)
				   AND PROCESS_FLAG = 'R';

Line: 201

			/*If delete from UI, this invalidates the geolocation information for asset/instance */
         IF p_CSI_instance_geoloc_tbl(i).valid_flag = 'N' THEN

		    IF (l_debug_level > 0) THEN
                csi_t_gen_utility_pvt.add( 'Invalidating Instance geo location info');

Line: 208

		        CSI_GIS_INSTANCE_GEO_LOC_PVT.UPDATE_ROW
             ( p_instance_id    => p_csi_instance_geoloc_tbl(i).instance_id
              ,p_inst_latitude  => l_inst_latitude_dd_value
              ,p_inst_longitude => l_inst_longitude_dd_value
			  ,p_valid_flag     => p_csi_instance_geoloc_tbl(i).valid_flag
              ,x_return_status => l_return_status
              );

Line: 221

				   UPDATE csi_ii_geoloc_interface
				      SET process_flag = 'E',
					      error_message=l_msg_data,
						  process_date=sysdate
			       WHERE  instance_number = (select instance_number
					                          FROM CSI_ITEM_INSTANCES
                                              WHERE instance_id = p_csi_instance_geoloc_tbl(i).instance_id)
				   AND PROCESS_FLAG = 'R';

Line: 257

				   UPDATE csi_ii_geoloc_interface
				      SET process_flag = 'E',
					      error_message=l_msg_data,
						  process_date=sysdate
			       WHERE  instance_number = (select instance_number
					                          FROM CSI_ITEM_INSTANCES
                                              WHERE instance_id = p_csi_instance_geoloc_tbl(i).instance_id)
					AND PROCESS_FLAG = 'R';

Line: 295

				   UPDATE csi_ii_geoloc_interface
				      SET process_flag = 'E',
					      error_message=l_msg_data,
						  process_date=sysdate
			       WHERE  instance_number = (select instance_number
					                          FROM CSI_ITEM_INSTANCES
                                              WHERE instance_id = p_csi_instance_geoloc_tbl(i).instance_id)
					AND PROCESS_FLAG = 'R';

Line: 329

				   UPDATE csi_ii_geoloc_interface
				      SET process_flag = 'E',
					      error_message=l_msg_data,
						  process_date=sysdate
			       WHERE  instance_number = (select instance_number
					                          FROM CSI_ITEM_INSTANCES
                                              WHERE instance_id = p_csi_instance_geoloc_tbl(i).instance_id)
					AND PROCESS_FLAG = 'R';

Line: 349

         /*l_create_update = 'C' indicates that latitude and longitude values are being entered for the first time for asset/instance */
		 IF l_create_update = 'C' THEN

             csi_t_gen_utility_pvt.add( 'Calling Insert row');

Line: 354

		     CSI_GIS_INSTANCE_GEO_LOC_PVT.INSERT_ROW
             ( p_instance_id    => p_CSI_instance_geoloc_tbl(i).instance_id
              ,p_inst_latitude  => l_inst_latitude_dd_value
              ,p_inst_longitude => l_inst_longitude_dd_value
              , x_return_status => l_return_status
              );

Line: 367

				   UPDATE csi_ii_geoloc_interface
				      SET process_flag = 'E',
					      error_message=l_msg_data,
						  process_date=sysdate
			       WHERE  instance_number = (select instance_number
					                          FROM CSI_ITEM_INSTANCES
                                              WHERE instance_id = p_csi_instance_geoloc_tbl(i).instance_id)
				  AND PROCESS_FLAG = 'R';

Line: 382

			   UPDATE csi_ii_geoloc_interface
				      SET process_flag = 'P',
					      process_date = sysdate
			       WHERE  instance_number = (select instance_number
					                          FROM CSI_ITEM_INSTANCES
                                              WHERE instance_id = p_csi_instance_geoloc_tbl(i).instance_id)
					AND PROCESS_FLAG = 'R';

Line: 393

		   /*l_create_update = 'U' indicates that latitude and longitude values are already present for the asset/instance and they are just being updated*/
          ELSIF l_create_update = 'U'   THEN

               csi_t_gen_utility_pvt.add( 'Calling Update row');

Line: 398

			  CSI_GIS_INSTANCE_GEO_LOC_PVT.UPDATE_ROW
             ( p_instance_id    => p_CSI_instance_geoloc_tbl(i).instance_id
              ,p_inst_latitude  => l_inst_latitude_dd_value
              ,p_inst_longitude => l_inst_longitude_dd_value
			        ,p_valid_flag     => p_CSI_instance_geoloc_tbl(i).valid_flag
              , x_return_status => l_return_status
              );

Line: 410

				   UPDATE csi_ii_geoloc_interface
				      SET process_flag = 'E',
					      error_message=l_msg_data,
						  process_date = sysdate
			       WHERE  instance_number = (select instance_number
					                          FROM CSI_ITEM_INSTANCES
                                              WHERE instance_id = p_csi_instance_geoloc_tbl(i).instance_id)
					AND PROCESS_FLAG = 'R';

Line: 425

			   UPDATE csi_ii_geoloc_interface
				      SET process_flag = 'P',
					      process_date = sysdate
			          WHERE  instance_number = (select instance_number
					                          FROM CSI_ITEM_INSTANCES
                                              WHERE instance_id = p_csi_instance_geoloc_tbl(i).instance_id)
                      AND PROCESS_FLAG = 'R' ;

Line: 463

                ROLLBACK TO CREATEUPDATE_INST_GEOLOC_INFO;

Line: 473

                ROLLBACK TO CREATEUPDATE_INST_GEOLOC_INFO;

Line: 483

                ROLLBACK TO CREATEUPDATE_INST_GEOLOC_INFO;

Line: 498

END CREATEUPDATE_INST_GEOLOC_INFO;

Line: 523

       SELECT *
	   FROM csi_ii_geoloc_interface
	   WHERE process_flag = 'R';

Line: 559

		   SELECT Count(instance_number)
		   INTO l_count
		   FROM csi_ii_geoloc_interface
           WHERE instance_number = import_inst_geo_loc_tbl(i).instance_number
		   AND PROCESS_FLAG = 'R';

Line: 569

		     UPDATE csi_ii_geoloc_interface
		     SET process_flag = 'E',
			     error_message = FND_MESSAGE.Get_String('CSI', 'CSI_GIS_DUP_INST_ASSET'),
				 process_date=sysdate
		     WHERE instance_number = import_inst_geo_loc_tbl(i).instance_number
			 AND PROCESS_FLAG = 'R';

Line: 579

		     SELECT instance_id
		     INTO l_instance_id
		     FROM csi_item_instances
			 WHERE 	instance_number = import_inst_geo_loc_tbl(i).instance_number;

Line: 588

				 UPDATE csi_ii_geoloc_interface
				 SET process_flag = 'E',
				     error_message = FND_MESSAGE.Get_String('CSI', 'CSI_INSTANCE_NOT_FOUND'),
					 process_date=sysdate
			     WHERE instance_number = import_inst_geo_loc_tbl(i).instance_number
				 AND PROCESS_FLAG = 'R';

Line: 610

	         UPDATE csi_ii_geoloc_interface
	         SET process_flag = 'E',
		         error_message = FND_MESSAGE.Get_String('CSI', 'CSI_INVALID_GEOCODE_FORMAT'),
		         process_date=sysdate
		     WHERE instance_number = import_inst_geo_loc_tbl(i).instance_number
		       AND PROCESS_FLAG = 'R';

Line: 634

   	 /*calling create update API*/
	 IF l_csi_instance_geoloc_tbl.count > 0 THEN

         CSI_GIS_INSTANCE_LOC_PUB.CREATEUPDATE_INST_GEOLOC_INFO(p_api_version => 1,
                                                                p_commit      => FND_API.G_TRUE
                                                                ,p_csi_instance_geoloc_tbl => l_csi_instance_geoloc_tbl
																,x_return_status => l_return_status
																,x_msg_count     => l_msg_count
																,x_msg_data      => l_msg_data);