APPS.HZ_GEO_STRUCT_MAP_PUB SQL Statements

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

Line: 63

     SELECT address_style
     INTO   l_context
     FROM   fnd_territories
     WHERE  territory_code = p_territory_code
     AND    l_application_id = 222
     AND    address_style = p_context ;

Line: 73

      SELECT descriptive_flex_context_code
      INTO   l_context
      FROM   fnd_descr_flex_contexts_vl
      WHERE  application_id = l_application_id
      AND    descriptive_flexfield_name = l_descriptive_flexfield_name
      AND    descriptive_flex_context_code = p_context;

Line: 106

    SELECT 'Exists'
    INTO   l_exists
    FROM   fnd_columns col, fnd_tables tbl
    WHERE  tbl.table_id = col.table_id
    AND    tbl.application_id = col.application_id
    AND    tbl.application_id= l_application_id
    AND    col.column_name = p_loc_comp
    AND    tbl.table_name = p_location_table_name
    AND    col.column_name NOT IN ('LAST_UPDATED_BY', 'CREATION_DATE', 'CREATED_BY',
                                   'LAST_UPDATE_DATE', 'LAST_UPDATE_LOGIN');

Line: 131

    SELECT 'Exists'
    INTO   l_exists
    FROM   hz_geography_types_b
    WHERE  geography_type = UPPER(p_geo_type);

Line: 149

      SELECT geography_type, geography_element_column
      FROM  hz_geo_structure_levels
      WHERE country_code = p_country
      START WITH parent_geography_type  = p_parent_geo_type
      AND   country_code = p_country
      CONNECT BY PRIOR geography_type = parent_geography_type
      AND   country_code = p_country;

Line: 164

      SELECT geography_element_column
      INTO  p_geo_element_col
      FROM  hz_geo_structure_levels
      WHERE geography_type = p_geography_type
      AND   parent_geography_type  = p_parent_geography_type
      AND   country_code = p_country;

Line: 218

   SELECT level+1 seq_num
   FROM   hz_geo_structure_levels
   WHERE  geography_type = c_geo_type
   START WITH parent_geography_type = 'COUNTRY'
   AND country_code = c_country
   CONNECT BY PRIOR geography_type = parent_geography_type
   AND country_code = c_country;

Line: 264

      SELECT territory_code
      INTO l_country
      FROM fnd_territories
      WHERE territory_code = UPPER(p_geo_struct_map_rec.country_code);

Line: 431

    BEGIN --insert row
      SELECT HZ_GEO_STRUCT_MAP_S.nextval
      INTO l_map_id
      FROM dual;

Line: 435

      hz_geo_struct_map_pvt.insert_row(
                                  x_map_row_id,
                                  l_map_id,
                                  p_geo_struct_map_rec.country_code,
                                  p_geo_struct_map_rec.loc_tbl_name,
                                  p_geo_struct_map_rec.address_style);

Line: 456

        hz_geo_struct_map_dtl_pvt.insert_row(
                                  x_map_dtl_row_id,
                                  l_map_id,
                                  l_geo_struct_map_dtl_tbl(i).loc_seq_num,
                                  l_geo_struct_map_dtl_tbl(i).loc_comp,
                                  l_geo_struct_map_dtl_tbl(i).geo_type,
                                  l_geo_struct_map_dtl_tbl(i).geo_element_col);

Line: 465

      END; -- insert row

Line: 573

  PROCEDURE delete_geo_struct_mapping(
                   p_map_id              IN         NUMBER,
                   p_location_table_name IN         VARCHAR2,
                   p_country             IN         VARCHAR2,
                   p_address_style       IN         VARCHAR2,
                   p_geo_struct_map_dtl_tbl  IN     geo_struct_map_dtl_tbl_type,
                   p_init_msg_list       IN         VARCHAR2 := FND_API.G_FALSE,
                   x_return_status       OUT NOCOPY VARCHAR2,
                   x_msg_count           OUT NOCOPY NUMBER,
                   x_msg_data            OUT NOCOPY VARCHAR2
                   ) IS
  l_map_id       NUMBER;

Line: 594

  SELECT usage_id
  FROM   hz_address_usages
  WHERE  map_id = p_map_id;

Line: 599

  SELECT dtl.usage_id
  FROM   Hz_address_usages usg, Hz_address_usage_dtls dtl
  WHERE  usg.map_id = p_map_id
  AND    dtl.geography_type = c_geography_type
  AND    dtl.usage_id = usg.usage_id;

Line: 609

  l_delete_mapping_also VARCHAR(1);

Line: 636

          SELECT map_id
          INTO  l_map_id
          FROM  hz_geo_struct_map
          WHERE country_code = p_country
          AND   loc_tbl_name = p_location_table_name
          AND   address_style  IS NULL;

Line: 653

          SELECT map_id
          INTO  l_map_id
          FROM  hz_geo_struct_map
          WHERE country_code = p_country
          AND   loc_tbl_name = p_location_table_name
          AND   address_style = p_address_style;

Line: 671

        SELECT map_id
        INTO  l_map_id
        FROM  hz_geo_struct_map
        WHERE map_id = p_map_id;

Line: 692

             SELECT count(*)
             INTO  l_count
             FROM  hz_geo_struct_map_dtl
             WHERE map_id = p_map_id;

Line: 702

               l_delete_mapping_also := 'N';

Line: 705

                 SELECT geography_type
                 INTO  l_del_geo_type
                 FROM  hz_geo_struct_map_dtl
                 WHERE map_id = p_map_id
                 AND   geography_type <> p_geo_struct_map_dtl_tbl(i).geo_type;

Line: 711

                 l_delete_mapping_also := 'Y';

Line: 722

                l_delete_mapping_also := 'Y';

Line: 742

             HZ_ADDRESS_USAGES_PUB.delete_address_usages(
                                p_usage_id               => l_usage_id,
                                p_address_usage_dtls_tbl => l_address_usage_dtls_tbl,
                                p_init_msg_list          => 'F',
                                x_return_status          => l_return_status,
                                x_msg_count              => l_msg_count,
                                x_msg_data               => l_msg_data);

Line: 758

          hz_geo_struct_map_dtl_pvt.delete_row(l_map_id,p_geo_struct_map_dtl_tbl(i).geo_type);

Line: 764

            hz_geo_struct_map_dtl_pvt.delete_row(l_map_id,l_del_geo_type);

Line: 767

          IF (l_delete_mapping_also = 'Y') THEN
            -- delete mapping record also
            hz_geo_struct_map_pvt.delete_row(l_map_id);

Line: 802

          SELECT count(*)
          INTO  l_count
          FROM  hz_address_usage_dtls
          WHERE usage_id = i.usage_id;

Line: 810

             HZ_ADDRESS_USAGES_PUB.delete_address_usages(
                                p_usage_id       => i.usage_id,
                                p_address_usage_dtls_tbl => l_address_usage_dtls_tbl,
                                p_init_msg_list  => 'F',
                                x_return_status  => l_return_status,
                                x_msg_count      => l_msg_count,
                                x_msg_data       => l_msg_data);

Line: 828

       SELECT count(*)
       INTO   l_count
       FROM   hz_geo_struct_map_dtl
       WHERE  map_id = l_map_id;

Line: 834

         hz_geo_struct_map_dtl_pvt.delete_row(l_map_id);

Line: 836

       hz_geo_struct_map_pvt.delete_row(l_map_id);

Line: 865

 END delete_geo_struct_mapping;

Line: 893

   SELECT country_code, loc_tbl_name
   FROM   hz_geo_struct_map
   WHERE  map_id = c_map_id;

Line: 898

   SELECT count(*)
   FROM   hz_geo_struct_map_dtl
   WHERE  map_id = c_map_id;

Line: 903

   SELECT level+1 seq_num, geography_element_column
   FROM   hz_geo_structure_levels
   WHERE  geography_type = c_geo_type
   START WITH parent_geography_type = 'COUNTRY'
   AND country_code = c_country
   CONNECT BY PRIOR geography_type = parent_geography_type
   AND country_code = c_country;

Line: 1033

   BEGIN --insert row
      i := l_geo_struct_map_dtl_tbl.first;

Line: 1037

         hz_geo_struct_map_dtl_pvt.insert_row(
                                  x_map_dtl_row_id,
                                  p_map_id,
                                  l_geo_struct_map_dtl_tbl(i).loc_seq_num,
                                  l_geo_struct_map_dtl_tbl(i).loc_comp,
                                  l_geo_struct_map_dtl_tbl(i).geo_type,
                                  l_geo_struct_map_dtl_tbl(i).geo_element_col);

Line: 1046

   END; -- insert row

Line: 1114

PROCEDURE update_geo_struct_map_dtls
  (p_map_id                  IN              NUMBER,
   p_geo_struct_map_dtl_tbl  IN              geo_struct_map_dtl_tbl_type,
   p_init_msg_list           IN              VARCHAR2 := FND_API.G_FALSE,
   x_return_status           OUT    NOCOPY   VARCHAR2,
   x_msg_count               OUT    NOCOPY   NUMBER,
   x_msg_data                OUT    NOCOPY   VARCHAR2
  ) IS

   p_index_name              VARCHAR2(30);

Line: 1139

   SELECT country_code, loc_tbl_name
   FROM   hz_geo_struct_map
   WHERE  map_id = c_map_id;

Line: 1144

   SELECT rowid, loc_seq_num,geo_element_col
   FROM   hz_geo_struct_map_dtl
   WHERE  map_id = p_map_id
   AND    geography_type = c_geography_type;

Line: 1152

   SAVEPOINT update_geo_struct_map_dtls;

Line: 1208

        hz_geo_struct_map_dtl_pvt.Update_Row (
                         x_rowid                 => l_rowid,
                         x_map_id                => p_map_id,
                         x_loc_seq_num           => l_loc_seq_num,
                         x_loc_component         => p_geo_struct_map_dtl_tbl(i).loc_comp,
                         x_geography_type        => p_geo_struct_map_dtl_tbl(i).geo_type,
                         x_geo_element_col       => l_geo_element_column);

Line: 1225

      SELECT count(*)
      INTO   l_count
      FROM   Hz_address_usages usg, Hz_address_usage_dtls dtl
      WHERE  usg.map_id = p_map_id
      AND    usg.status_flag = 'A'
      AND    dtl.usage_id = usg.usage_id;

Line: 1260

        ROLLBACK TO update_geo_struct_map_dtls;

Line: 1267

        ROLLBACK TO update_geo_struct_map_dtls;

Line: 1274

        ROLLBACK TO update_geo_struct_map_dtls;

Line: 1296

        ROLLBACK TO update_geo_struct_map_dtls;

Line: 1306

END update_geo_struct_map_dtls;