APPS.HZ_GEO_GET_PUB SQL Statements

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

Line: 24

    SELECT object_type
    FROM   hz_relationship_types
    WHERE  subject_type = p_zone_type
    AND    status = 'A'
    AND    relationship_type = 'TAX';

Line: 32

    SELECT subject_id
    FROM   hz_relationships
    WHERE  subject_type = p_zone_type
    AND    subject_table_name = 'HZ_GEOGRAPHIES'
    AND    object_type = c_object_type
    AND    object_id = c_geography_id
    AND    object_table_name = 'HZ_GEOGRAPHIES'
    AND    relationship_type = 'TAX'
    AND    directional_flag = 'F'
    AND    relationship_code  = 'PARENT_OF'
    AND    p_date BETWEEN start_date and end_date;

Line: 78

    SELECT count(1)
    INTO   l_count
    FROM   hz_geography_types_b
    WHERE  geography_type = p_zone_type
    AND    geography_use <> 'MASTER_REF'
    AND    rownum = 1;

Line: 97

      SELECT count(1)
      INTO   l_count
      FROM   hz_relationship_types
      WHERE  subject_type = p_zone_type
      AND    status = 'A'
      AND    rownum = 1;

Line: 121

         SELECT country
         INTO   l_country_code
         FROM   hz_locations
         WHERE  location_id = p_location_id;

Line: 126

         SELECT country
         INTO   l_country_code
         FROM   hr_locations
         WHERE  location_id = p_location_id;

Line: 145

    SELECT count(1)
    INTO   l_gnr_count
    FROM   hz_geo_name_references
    WHERE  location_table_name = p_location_table_name
    AND    location_id = p_location_id
    AND    rownum = 1;

Line: 181

        SELECT geography_id
        INTO   l_geography_id
        FROM   hz_geo_name_references
        WHERE  location_table_name = p_location_table_name
        AND    location_id = p_location_id
        AND    geography_type = l_ref_geo_type;

Line: 199

           SELECT geography_id
           INTO   l_geography_id
           FROM   hz_geographies
           WHERE  country_code = l_country_code
           AND    geography_type = l_ref_geo_type
           -- Bug 5410283 (Added on 07-Aug-2006)
		   AND    p_date BETWEEN start_date AND end_date;

Line: 231

              SELECT postal_code_range_flag, geography_use
              INTO   l_postal_code_range_flag, l_geography_use
              FROM   hz_geography_types_b
              WHERE  geography_type = p_zone_type;

Line: 259

                 SELECT count(1) INTO l_count
                 FROM  hz_geography_ranges
                 WHERE -- master_ref_geography_id = l_geography_id AND
                       geography_id = l_zone_id
                 AND   geography_type = p_zone_type
                 AND   geography_use = 'TAX'
                 AND   rownum = 1;

Line: 278

                    SELECT postal_code
                    INTO   l_postal_code
                    FROM   hr_locations_all
                    WHERE  location_id = p_location_id;

Line: 285

                    SELECT postal_code
                    INTO   l_postal_code
                    FROM   hz_locations
                    WHERE  location_id = p_location_id;

Line: 303

                  SELECT count(1)
                  INTO   l_count
                  FROM   hz_geography_ranges
                  WHERE  geography_id = l_zone_id
                --AND    master_ref_geography_id = l_geography_id
                  AND    l_postal_code between geography_from and geography_to
                  AND    p_date between start_date and end_date
                  AND    rownum = 1;

Line: 393

                  SELECT geography_id, geography_code, geography_name
                  INTO   l_zone_tbl_final(l_tbl_count+1).zone_id,
                         l_zone_tbl_final(l_tbl_count+1).zone_code,
                         l_zone_tbl_final(l_tbl_count+1).zone_name
                  FROM   hz_geographies
                  WHERE  geography_id = l_zone_tbl(i).zone_id
                  -- Bug 5410283 (Added on 07-Aug-2006)
  	              AND    p_date BETWEEN start_date AND end_date;

Line: 428

               SELECT geography_id, geography_code, geography_name
               INTO   l_zone_tbl_final(i).zone_id, l_zone_tbl_final(i).zone_code, l_zone_tbl_final(i).zone_name
               FROM   hz_geographies
               WHERE  geography_id = l_zone_tbl(i).zone_id
               -- Bug 5410283 (Added on 07-Aug-2006)
               AND    p_date BETWEEN start_date AND end_date;

Line: 438

				hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>' First insert in l_zone_tbl_final ',
									   p_msg_level=>fnd_log.level_statement);

Line: 513

		SELECT geography_element_column
		FROM hz_geo_structure_levels hgsl
		START WITH PARENT_GEOGRAPHY_TYPE = 'COUNTRY'
		and hgsl.geography_id = l_country_id
		CONNECT BY PRIOR hgsl.GEOGRAPHY_TYPE = hgsl.PARENT_GEOGRAPHY_TYPE
		and hgsl.geography_id = l_country_id
		and parent_geography_type <> l_geo_type;

Line: 523

		select geography_element1_id, geography_type into l_country_id, l_geo_type
		from hz_geographies
		where geography_id = l_geo_id;

Line: 542

		'select '||l_clause||' from hz_geographies where geography_id = :1' USING l_geo_id;