FUNCTION f_getOffsettedQuality(locationId integer, locationType integer) RETURN quality_tab
  IS
      l_quality_level quality_tab := quality_tab();
      l_level varchar2(10) := 0;
      lMinx float;
      lMaxX float;
      lMinY float;
      lMaxY float;
      cnt integer;
      lcity_id integer;
      locBlob BLOB;
      gsmOffset integer := 100; --default value for location offsetting
      umtsOffset integer := 100; --default value for location offsetting
      OffsetX    Number ; 
      OffsetY    Number ;       
      l_offset1 Binary_Integer := 0;
      l_offset2 Binary_Integer := 0;
      l_area    Double precision:=0.0;
      l_proc    Double Precision :=0.0;  
      cover binary_integer;      
      c varchar2(100);
  BEGIN
       IF locationType = 0 THEN--house
            
           Select hg.gis_xmin, hg.gis_xmax, hg.gis_ymin, hg.gis_ymax, s.city 
             into lMinx, lMaxX, lMinY, lMaxY,lcity_id 
           from house_gis hg 
           Join house h On hg.id = h.id
           Join street s On h.street = s.id
           where hg.id = locationId;
           --check if exists config for city_id and get offsetting
           select count(*) 
             into cnt 
           from DICT.CITY_CONFIG cc 
           where cc.city_id = lcity_id;
           
           if cnt > 0 then 
               select nvl(cc.sig_house_gsm, gsmOffset), nvl(cc.sig_house_3g, umtsOffset) 
                 into gsmOffset, umtsOffset 
               From CITY_CONFIG cc 
               where cc.city_id = lcity_id;
           else  
               select nvl(cc.sig_house_gsm, gsmOffset), nvl(cc.sig_house_3g, umtsOffset) 
                 into gsmOffset, umtsOffset 
               From CITY_CONFIG cc 
               where cc.city_id = 0;
           end if;
           
           select h.gis into locBlob from house_gis h where h.id = locationId;
                     
       ELSIF locationType = 1 THEN--street
           select distinct h.gis_xmin, h.gis_xmax, h.gis_ymin, h.gis_ymax, s.city 
             into lMinx, lMaxX, lMinY, lMaxY, lcity_id 
           from street_gis h, street s 
           where s.id = h.id and h.id = locationId;
           --check if exists config for city_id and get offsetting
           select count(*) 
              into cnt 
           from DICT.CITY_CONFIG cc 
           where cc.city_id = lcity_id;
           
           if cnt > 0 then 
               select nvl(cc.sig_house_gsm, gsmOffset), nvl(cc.sig_house_3g, umtsOffset) 
                  into gsmOffset, umtsOffset 
               from CITY_CONFIG cc 
               where cc.city_id = lcity_id;
           else  
               select nvl(cc.sig_house_gsm, gsmOffset), nvl(cc.sig_house_3g, umtsOffset) 
                 into gsmOffset, umtsOffset 
               from CITY_CONFIG cc 
               where cc.city_id = 0;
           end if;
           
           select h.gis 
             into locBlob 
           from street_gis h 
           where h.id = locationId;
                  
       ELSIF locationType = 2 THEN--city
           select distinct h.gis_xmin, h.gis_xmax, h.gis_ymin, h.gis_ymax, h.id 
             into lMinx, lMaxX, lMinY, lMaxY,lcity_id 
           from city_gis h 
           Join city c On h.id = c.id
           where h.id = locationId;
           --check if exists config for city_id and get offsetting
           select count(*) into cnt 
           from DICT.CITY_CONFIG cc 
           where cc.city_id = lcity_id;
           
           if cnt > 0 then 
               select nvl(cc.sig_house_gsm, gsmOffset), nvl(cc.sig_house_3g, umtsOffset) 
                 into gsmOffset, umtsOffset 
               from CITY_CONFIG cc 
               where cc.city_id = lcity_id;
           end if;
           
           select h.gis 
             into locBlob 
           from city_gis h 
           where h.id = locationId;
                      
       END IF;

       OffsetX := gsmOffset;
       OffsetY := gsmOffset;
       pac_geocoding.p_meters2geo(OffsetX, OffsetY);
          
       FOR r IN 
       (
           SELECT g1.gis, g1.threshold,g1.gis_xmax,g1.gis_xmin,g1.gis_ymax,g1.gis_ymin
           FROM coverage.QUALITY_GIS g1
           WHERE g1.signal_type = 'GSM' AND g1.detail_m = 5
               AND (lMinx - OffsetX < g1.gis_xmax 
               AND lMaxX + OffsetX > g1.gis_xmin 
               AND lMinY - (OffsetY ) < g1.gis_ymax 
               AND lMaxY + (OffsetY ) > g1.gis_ymin) --offsetted bounds..don't ask...
           ORDER BY g1.threshold  DESC       
       )          
       Loop
-- call external c function
              pac_gis.PolygonsIntersection(r.gis,locBlob,r.gis_xmax,r.gis_xmin,r.gis_ymax,r.gis_ymin,cover);

              $IF pac_3g_service.debugging=1 $THEN    
                dbms_output.put_line('threshold ='||r.threshold||' area='||l_area);
              $END            
              
           IF cover = 1  THEN
              l_level := r.threshold;
              EXIT;
           END IF;
       
       END LOOP;
              
       IF l_level != 0 THEN
         l_quality_level.extend();  
         cnt := l_quality_level.count;
         l_quality_level(cnt) := quality_t('',''); 
         l_quality_level(cnt).Standart := 'GSM';  
         l_quality_level(cnt).Sig_Level:= l_level;
         l_level := 0;
         cover   := 0;
       END IF;
      -- ==================================== 
       OffsetX := umtsOffset;
       OffsetY := umtsOffset;
       pac_geocoding.p_meters2geo(OffsetX, OffsetY);
                  
       FOR r IN 
       (
           SELECT g1.id,g1.gis, g1.threshold,g1.gis_xmax,g1.gis_xmin,g1.gis_ymax,g1.gis_ymin
           FROM coverage.QUALITY_GIS g1
           WHERE g1.signal_type = 'UMTS' AND g1.detail_m = 5
               AND (lMinx - OffsetX < g1.gis_xmax 
               AND lMaxX + OffsetX > g1.gis_xmin 
               AND lMinY - (OffsetY ) < g1.gis_ymax 
               AND lMaxY + (OffsetY ) > g1.gis_ymin) --offsetted bounds..don't ask...
           ORDER BY g1.threshold  DESC       
       )          
       Loop
--call external c function        
             pac_gis.PolygonsIntersection(r.gis,locBlob,r.gis_xmax,r.gis_xmin,r.gis_ymax,r.gis_ymin,cover);
             
              $IF pac_3g_service.debugging=1 $THEN    
                dbms_output.put_line('threshold ='||r.threshold||' area='||l_area);
              $END 

           IF cover = 1  THEN
             l_level := r.threshold;
             EXIT;
           END IF;
 
       END LOOP;               
       
       IF l_level != 0 THEN
         l_quality_level.extend();  
         cnt := l_quality_level.count;
         l_quality_level(cnt) := quality_t('','');  
         l_quality_level(cnt).Standart := '3G';  
         l_quality_level(cnt).Sig_Level:= l_level;    
       END IF;
       $IF pac_3g_service.debugging=1 $THEN 
           dbms_output.put_line('f_getQuality rc='||l_quality_level.count);
           FOR r IN 1..l_quality_level.count LOOP
               dbms_output.put_line('standart='||l_quality_level(r).standart||' level='||l_quality_level(r).sig_level);
           END LOOP;
       $END 

       RETURN l_quality_level;
  END;