APPS.HZ_GEO_STRUCTURE_VALIDATE_PVT SQL Statements

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

Line: 65

          SELECT count(*)
          INTO   l_count
          FROM   hz_geography_types_b
          WHERE  GEOGRAPHY_TYPE = UPPER(p_geography_type)
            AND  GEOGRAPHY_USE = decode(p_master_ref_flag,'Y','MASTER_REF',GEOGRAPHY_USE);

Line: 116

          SELECT count(*)
          INTO   l_count
          FROM   HZ_GEOGRAPHIES
          WHERE  GEOGRAPHY_ID = p_geography_id
            AND  GEOGRAPHY_USE = decode(p_master_ref_flag,'Y','MASTER_REF',GEOGRAPHY_USE);

Line: 172

     p_create_update_flag           IN  VARCHAR2,
     p_geo_rel_type_rec    IN  HZ_GEOGRAPHY_STRUCTURE_PUB.geo_rel_type_REC_TYPE,
     x_return_status                IN OUT NOCOPY VARCHAR2
   ) IS

   BEGIN

     IF p_create_update_flag='C' THEN
         -- validate mandatory columns
        hz_utility_v2pub.validate_mandatory(
          p_create_update_flag     => 'C',
          p_column                 => 'geography_type',
          p_column_value           => p_geo_rel_type_rec.geography_type,
          x_return_status          => x_return_status
          );

Line: 190

          p_create_update_flag     => 'C',
          p_column                 => 'parent_geography_type',
          p_column_value           => p_geo_rel_type_rec.parent_geography_type,
          x_return_status          => x_return_status
          );

Line: 197

          p_create_update_flag     => 'C',
          p_created_by_module      => p_geo_rel_type_rec.created_by_module,
          p_old_created_by_module  => null,
          x_return_status          => x_return_status);

Line: 214

      IF p_create_update_flag='C' THEN
         -- validate parent geography type
         validate_geography_type (
          p_geography_type       => p_geo_rel_type_rec.parent_geography_type,
          p_master_ref_flag      => 'N',
          x_return_status        => x_return_status
          );

Line: 232

      IF p_create_update_flag='C' THEN
          -- validate geography type uniqueness within parent geography type
          validate_geo_type_unique(
           p_geography_type          => p_geo_rel_type_rec.geography_type,
           p_parent_geography_type   => p_geo_rel_type_rec.parent_geography_type,
           x_return_status           => x_return_status
           );

Line: 242

      /*IF p_create_update_flag ='C' THEN
        -- validate whether geography_type is below parent_geography_type
        HZ_GEOGRAPHY_VALIDATE_PVT.validate_structure(
            p_geography_type        => p_geo_rel_type_rec.geography_type,
            p_parent_geography_type => p_geo_rel_type_rec.parent_geography_type,
            p_country_code          => p_geo_rel_type_rec.country_code,
            x_return_status         => x_return_status
            );

Line: 288

  p_create_update_flag        IN VARCHAR2,
  p_geo_structure_rec         IN HZ_GEOGRAPHY_STRUCTURE_PUB.geo_structure_rec_type,
  x_return_status             IN OUT NOCOPY VARCHAR2
  ) IS

    l_geography_type          VARCHAR2(30);

Line: 302

  IF p_create_update_flag = 'C' THEN
    hz_utility_v2pub.validate_mandatory(
          p_create_update_flag     => 'C',
          p_column                 => 'geography_id',
          p_column_value           => p_geo_structure_rec.geography_id,
          x_return_status          => x_return_status
          );

Line: 311

          p_create_update_flag     => 'C',
          p_column                 => 'geography_type',
          p_column_value           => p_geo_structure_rec.geography_type,
          x_return_status          => x_return_status
          );

Line: 318

          p_create_update_flag     => 'C',
          p_column                 => 'parent_geography_type',
          p_column_value           => p_geo_structure_rec.parent_geography_type,
          x_return_status          => x_return_status
          );

Line: 326

          p_create_update_flag     => 'C',
          p_column                 => 'geography_element_column',
          p_column_value           => p_geo_structure_rec.geography_element_column,
          x_return_status          => x_return_status
          );*/

Line: 333

      p_create_update_flag     => 'C',
      p_created_by_module      => p_geo_structure_rec.created_by_module,
      p_old_created_by_module  => null,
      x_return_status          => x_return_status);

Line: 342

  IF p_create_update_flag = 'C' THEN

    validate_geography_id(
       p_geography_id    => p_geo_structure_rec.geography_id,
       p_master_ref_flag => 'Y',
       x_return_status   => x_return_status
       );

Line: 351

        SELECT geography_type
          INTO l_geography_type
          FROM HZ_GEOGRAPHIES
         WHERE geography_id = p_geo_structure_rec.geography_id;

Line: 364

  IF p_create_update_flag = 'C' THEN

      validate_geography_type(
        p_geography_type  => p_geo_structure_rec.geography_type,
        p_master_ref_flag => 'Y',
        x_return_status   => x_return_status
         );

Line: 381

  IF p_create_update_flag = 'C' THEN
    BEGIN

      SELECT count(*) INTO l_geo_count
      FROM   hz_geo_structure_levels
      WHERE  geography_id = p_geo_structure_rec.geography_id
      AND    geography_type = p_geo_structure_rec.geography_type;

Line: 397

      SELECT count(*) INTO l_pgeo_count
      FROM   hz_geo_structure_levels
      WHERE  geography_id = p_geo_structure_rec.geography_id
      AND    parent_geography_type = p_geo_structure_rec.parent_geography_type;

Line: 413

  IF p_create_update_flag = 'C' THEN
     -- Added the below if condition and error message for bug # 4596440
     -- Address validation level should not be populated all the geography types.
     -- It should be populated only for parent_geography_type = 'COUNTRY'.
     IF p_geo_structure_rec.addr_val_level is NOT NULL then
        IF p_geo_structure_rec.parent_geography_type = 'COUNTRY' then
           BEGIN
              SELECT lookup_code
              INTO   l_addr_val_level
              FROM   ar_lookups
              WHERE  lookup_type = 'HZ_ADDRESS_VALIDATION_LEVEL'
              AND    lookup_code = p_geo_structure_rec.addr_val_level;

Line: 455

  IF p_create_update_flag = 'U' THEN

  -- validate mandatory for relationship_type_id
  hz_utility_v2pub.validate_mandatory(
          p_create_update_flag     => 'U',
          p_column                 => 'geography_id',
          p_column_value           => p_geo_structure_rec.geography_id,
          x_return_status          => x_return_status
          );

Line: 466

          p_create_update_flag     => 'U',
          p_column                 => 'geography_type',
          p_column_value           => p_geo_structure_rec.geography_type,
          x_return_status          => x_return_status
          );

Line: 472

          p_create_update_flag     => 'U',
          p_column                 => 'parent_geography_type',
          p_column_value           => p_geo_structure_rec.parent_geography_type,
          x_return_status          => x_return_status
          );

Line: 499

   SELECT count(*) INTO l_count
     FROM hz_relationship_types
    WHERE SUBJECT_TYPE = p_parent_geography_type
      AND OBJECT_TYPE = p_geography_type
      AND FORWARD_REL_CODE = 'PARENT_OF'
      AND BACKWARD_REL_CODE = 'CHILD_OF';

Line: 531

          SELECT 1
          INTO   l_count
          FROM   HZ_RELATIONSHIP_TYPES
          WHERE  RELATIONSHIP_TYPE_ID = p_relationship_type_id;

Line: 574

    p_create_update_flag                    IN     VARCHAR2,
    x_return_status                         IN OUT NOCOPY VARCHAR2
  ) IS

   l_count         NUMBER;

Line: 584

      IF p_create_update_flag = 'C' THEN
      hz_utility_v2pub.validate_mandatory(
          p_create_update_flag     => 'C',
          p_column                 => 'geography_type',
          p_column_value           => p_zone_type_rec.geography_type,
          x_return_status          => x_return_status
          );

Line: 593

          p_create_update_flag     => 'C',
          p_column                 => 'geography_use',
          p_column_value           => p_zone_type_rec.geography_use,
          x_return_status          => x_return_status
          );

Line: 600

        p_create_update_flag     => 'C',
        p_created_by_module      => p_zone_type_rec.created_by_module,
        p_old_created_by_module  => null,
        x_return_status          => x_return_status);

Line: 614

      IF p_create_update_flag = 'C' and
         p_zone_type_rec.limited_by_geography_id is not null
        and p_zone_type_rec.limited_by_geography_id <> fnd_api.g_miss_num THEN
      -- check for the mandatory columnm included_geography_type
         IF p_zone_type_rec.included_geography_type.count = 0 THEN
           FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );

Line: 627

       IF p_create_update_flag = 'C' THEN
         HZ_UTILITY_V2PUB.validate_lookup(
       p_column         => 'geography_use',
       p_lookup_type    => 'HZ_RELATIONSHIP_TYPE',
       p_column_value   => p_zone_type_rec.geography_use,
       x_return_status  => x_return_status
        );

Line: 637

       SELECT count(*) INTO l_count
         FROM hz_geography_types_b
        WHERE geography_type=p_zone_type_rec.geography_type;

Line: 650

        SELECT count(*) INTO l_count
          FROM hz_geographies
         WHERE geography_id = p_zone_type_rec.limited_by_geography_id;