APPS.HZ_GEO_UI_UTIL_PUB SQL Statements

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

Line: 18

   select  hr.child_id,
          geo.geography_name
    from  hz_hierarchy_nodes hr
          , hz_geographies   geo
    where hr.hierarchy_type    = 'MASTER_REF'
     and  hr.parent_id         = l_parent_id
     and  hr.parent_table_name = 'HZ_GEOGRAPHIES'
     and  hr.child_table_name  = 'HZ_GEOGRAPHIES'
     and  hr.child_object_type  = l_geo_type
     and  hr.status            = 'A'
     and  hr.effective_end_date > sysdate
     and  geo.geography_id     =  hr.child_id
     and  nvl(geo.end_date , sysdate)
	                             > sysdate
     and  geo.geography_type   =  hr.child_object_type
     and  (
	       UPPER(geo.geography_name)   =  UPPER(l_geo_name)
	       OR
	       UPPER(geo.geography_code) =  UPPER(l_geo_code)
	       );

Line: 70

  select dtl.usage_dtl_id
   from  Hz_address_usages usg,
         Hz_address_usage_dtls dtl
   where usg.map_id   = l_map_id
     and usg.usage_code = l_geo_tax
     and usg.status_flag = 'A'
     and dtl.usage_id = usg.usage_id
     and dtl.geography_type = l_geo_type;

Line: 98

PROCEDURE  update_map_usages(p_map_id           IN NUMBER,
                             p_tax_tbl          IN    HZ_GEO_UI_UTIL_PUB.tax_geo_tbl_type,
                             p_geo_tbl          IN    HZ_GEO_UI_UTIL_PUB.tax_geo_tbl_type,
                             p_init_msg_list    IN    VARCHAR2 ,
                             x_return_status    OUT   NOCOPY     VARCHAR2,
    						 x_msg_count        OUT   NOCOPY     NUMBER,
                             x_msg_data         OUT   NOCOPY     VARCHAR2,
                             x_show_gnr         OUT   NOCOPY     VARCHAR2
                            )
IS

cursor c_loc_tbl(l_map_id in number)
    is
select st.loc_tbl_name,
       geo.geography_id
 from  Hz_geo_struct_map st,
       hz_geographies geo
where  map_id = l_map_id
AND  st.country_code = geo.country_code
  AND  geo.geography_type = 'COUNTRY';

Line: 125

select usg.usage_id,
       usg.map_id,
       usg.usage_code
from   hz_address_usages usg
where  map_id = l_map_id
and    status_flag = 'A';

Line: 137

select usg.usage_dtl_id,
       usg.geography_type
from   hz_address_usage_dtls usg
where  usg.usage_id = l_usage_id
and    usg.geography_type = l_geo_type;

Line: 232

      l_address_usage_dtls_tbl.delete;

Line: 282

           l_address_usage_dtls_tbl.delete;

Line: 332

          hz_address_usages_pub.delete_address_usages(p_usage_id  => l_tax_usage_id,
      	                                              p_address_usage_dtls_tbl => l_address_usage_dtls_del_tbl,
      		  	     	                      p_init_msg_list       => FND_API.G_FALSE,
                                                      x_return_status => l_return_status,
      				                      x_msg_count     => l_msg_count,
      				                      x_msg_data      => l_msg_data);

Line: 345

          l_address_usage_dtls_del_tbl.delete;

Line: 346

          hz_address_usages_pub.delete_address_usages(p_usage_id  => l_tax_usage_id,
                                                      p_address_usage_dtls_tbl => l_address_usage_dtls_del_tbl,
                                                      p_init_msg_list       => FND_API.G_FALSE,
                                                      x_return_status => l_return_status,
                                                      x_msg_count     => l_msg_count,
                                                      x_msg_data      => l_msg_data);

Line: 372

  l_address_usage_dtls_del_tbl.delete;

Line: 388

      l_address_usage_dtls_tbl.delete;

Line: 435

           l_address_usage_dtls_tbl.delete;

Line: 465

	  if c_get_dtls%found then             -- delete detail
	     del_usg := true;

Line: 484

	  hz_address_usages_pub.delete_address_usages(p_usage_id  => l_geo_usage_id,
	                                             p_address_usage_dtls_tbl => l_address_usage_dtls_del_tbl,
	                                             p_init_msg_list       => FND_API.G_FALSE,
	                                             x_return_status => l_return_status,
	                                             x_msg_count     => l_msg_count,
	                                             x_msg_data      => l_msg_data);

Line: 497

          l_address_usage_dtls_del_tbl.delete;

Line: 498

	  hz_address_usages_pub.delete_address_usages(p_usage_id  => l_geo_usage_id,
	                                             p_address_usage_dtls_tbl => l_address_usage_dtls_del_tbl,
	                                             p_init_msg_list       => FND_API.G_FALSE,
	                                             x_return_status => l_return_status,
                                                     x_msg_count     => l_msg_count,
                                                     x_msg_data      => l_msg_data);

Line: 543

END update_map_usages;

Line: 552

 select country_code
   from hz_geographies
  where geography_id = l_geography_id
    and geography_type =  'COUNTRY';

Line: 561

select 'Y'
  from hz_geo_name_reference_log geo
 where exists ( select 'Y'
                from   hz_locations loc
				where  loc.country = ll_country_code
				AND    geo.location_id = loc.location_id)
and   location_table_name = 'HZ_LOCATIONS'
and rownum = 1; --bug 6870808

Line: 572

select 'Y'
  from hz_geo_name_reference_log geo
 where exists ( select 'Y'
                from   hr_locations_all loc
				where  loc.country = ll_country_code
				AND    geo.location_id = loc.location_id)
and   location_table_name = 'HR_LOCATIONS_ALL'
and rownum = 1; --bug 6870808

Line: 624

select g2.geography_name
 from  hz_geographies g1,
       hz_geographies g2
where g1.geography_id = l_geog_id
  and g2.geography_id = g1.geography_element1_id
  and g2.geography_type = 'COUNTRY';