APPS.HZ_GEOGRAPHY_VALIDATE_PVT SQL Statements

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

Line: 61

      SELECT geography_id INTO l_geography_id
        FROM hz_geographies
       WHERE country_code = p_country_code
         AND geography_type = 'COUNTRY';

Line: 68

        SELECT 1 into l_count
          FROM hz_geo_structure_levels
         WHERE geography_id = l_geography_id
           AND rownum <2;

Line: 81

       /*SELECT 1 into l_count
         FROM dual
        WHERE p_geography_type in (SELECT geography_type
                                   FROM HZ_GEO_STRUCTURE_LEVELS
                                   WHERE country_code = p_country_code
                                   CONNECT BY PRIOR geography_type=parent_geography_type
                                   START WITH parent_geography_type = p_parent_geography_type); */

Line: 91

   SELECT 1 into l_count
     FROM hz_geo_structure_levels
    WHERE geography_id=l_geography_id
      and geography_type=p_geography_type
      and parent_geography_type=p_parent_geography_type;

Line: 139

      SELECT geography_type
        INTO l_geography_type
        FROM HZ_GEOGRAPHIES
       WHERE geography_id = p_geography_id;

Line: 168

 *     p_create_update_flag           Flag that indicates 'C' for create
 *                                    and 'U' for update
 *
 *   IN/OUT:
 *     x_return_status                Return status.
 *
 * NOTES
 *
 *
 * MODIFICATION HISTORY
 *
 *   11-22-2002    Rekha Nalluri      o Created.
 *
 */
PROCEDURE validate_master_relation (
    p_master_relation_rec                   IN     HZ_GEOGRAPHY_PUB.master_relation_rec_type,
    p_create_update_flag                    IN     VARCHAR2,
    x_return_status                         IN OUT NOCOPY VARCHAR2
  )IS

   l_geography_type          VARCHAR2(30);

Line: 201

      IF p_create_update_flag = 'C' THEN
       -- validate start_date and end_date
       HZ_UTILITY_V2PUB.validate_start_end_date(
           p_create_update_flag                    => p_create_update_flag,
           p_start_date_column_name                => 'start_date',
           p_start_date                            => p_master_relation_rec.start_date,
           p_old_start_date                        => l_start_date,
           p_end_date_column_name                  => 'end_date',
           p_end_date                              => p_master_relation_rec.end_date,
           p_old_end_date                          => l_end_date,
           x_return_status                         => x_return_status
           );

Line: 223

      IF p_create_update_flag = 'C' THEN
       HZ_GEO_STRUCTURE_VALIDATE_PVT.validate_geography_id(
        p_geography_id         => p_master_relation_rec.geography_id,
        p_master_ref_flag      => 'Y',
        x_return_status        => x_return_status
        );

Line: 248

      IF p_create_update_flag = 'C' THEN
      -- get geography_type and parent_geography_type
       l_geography_type := get_geography_type(p_geography_id => p_master_relation_rec.geography_id,
                                              x_return_status  => x_return_status);

Line: 258

          SELECT country_code INTO l_country_code
            FROM HZ_GEOGRAPHIES
           WHERE geography_id=p_master_relation_rec.parent_geography_id;

Line: 289

         IF p_create_update_flag = 'C' THEN

        -- check whether geography_id is unique within parent_geography_id
        SELECT count(*) INTO l_count
          FROM HZ_RELATIONSHIPS
         WHERE subject_id=p_master_relation_rec.parent_geography_id
           AND object_id = p_master_relation_rec.geography_id
           AND subject_type = l_parent_geography_type
           AND object_type = l_geography_type
           AND relationship_type='MASTER_REF'
           AND status = 'A';

Line: 322

 *     p_create_update_flag           Flag that indicates 'C' for create
 *                                    and 'U' for update
 *
 *   IN/OUT:
 *     x_return_status                Return status.
 *
 * NOTES
 *
 *
 * MODIFICATION HISTORY
 *
 *   12-03-2002    Rekha Nalluri      o Created.
 *   08-25-2005    Nishant Singhai    o Modified for Bug 4549821. Added
 *                                      identifier_type check in WHERE clause to
 *                                      to verify if identifier value already exists
 *                                      in case of 'C'.
 *   10-25-2005    Nishant Singhai     Modified for Bug 4578867 (for NAME, if anything other than
 *	                                   STANDARD_NAME is used raise error)
 *
 */

PROCEDURE validate_geo_identifier (
    p_geo_identifier_rec                    IN     HZ_GEOGRAPHY_PUB.geo_identifier_rec_type,
    p_create_update_flag                    IN     VARCHAR2,
    x_return_status                         IN OUT NOCOPY VARCHAR2
  )IS

    l_count             NUMBER;

Line: 353

    IF p_create_update_flag = 'C' THEN
       HZ_GEO_STRUCTURE_VALIDATE_PVT.validate_geography_id(
        p_geography_id         => p_geo_identifier_rec.geography_id,
        p_master_ref_flag      => 'N',
        x_return_status        => x_return_status
        );

Line: 404

      SELECT count(*) INTO l_count
        FROM fnd_languages
       WHERE language_code = p_geo_identifier_rec.language_code
         AND rownum <2;

Line: 420

   /* If p_create_update_flag = 'C' THEN
     -- check the uniqueness for the combination of geography_id,identifier_type,
     -- identifier_subtype,identifier_value and language_code
     SELECT count(*) INTO l_count
       FROM HZ_GEOGRAPHY_IDENTIFIERS
      WHERE geography_id=p_geo_identifier_rec.geography_id
        AND identifier_type=p_geo_identifier_rec.identifier_type
        AND identifier_subtype=p_geo_identifier_rec.identifier_subtype
        AND identifier_value=p_geo_identifier_rec.identifier_value
        AND language_code = p_geo_identifier_rec.language_code;

Line: 446

      IF p_create_update_flag = 'C' THEN
--  Bug 4591502 : ISSUE # 16 : validate only in create

        hz_utility_v2pub.validate_created_by_module(
          p_create_update_flag     => 'C',
          p_created_by_module      => p_geo_identifier_rec.created_by_module,
          p_old_created_by_module  => null,
          x_return_status          => x_return_status);

Line: 458

          SELECT count(*) INTO l_count
            FROM HZ_GEOGRAPHY_IDENTIFIERS
           WHERE geography_id=p_geo_identifier_rec.geography_id
             AND language_code = UPPER(p_geo_identifier_rec.language_code)
             AND UPPER(identifier_value) = UPPER(p_geo_identifier_rec.identifier_value)
             AND identifier_type = p_geo_identifier_rec.identifier_type
             AND rownum <2;

Line: 475

      IF p_create_update_flag = 'U' THEN
        --check if the row exists
        SELECT count(*) INTO l_count
          FROM hz_geography_identifiers
         WHERE geography_id = p_geo_identifier_rec.geography_id
           AND identifier_type = p_geo_identifier_rec.identifier_type
           AND identifier_subtype = p_geo_identifier_rec.identifier_subtype
           AND identifier_value = p_geo_identifier_rec.identifier_value
           AND language_code = p_geo_identifier_rec.language_code
           ;

Line: 511

 *     p_create_update_flag           Flag that indicates 'C' for create
 *                                    and 'U' for update
 *
 *   IN/OUT:
 *     x_return_status                Return status.
 *
 * NOTES
 *
 *
 * MODIFICATION HISTORY
 *
 *   12-09-2002    Rekha Nalluri      o Created.
 *
 */

PROCEDURE validate_master_geography (
    p_master_geography_rec                  IN     HZ_GEOGRAPHY_PUB.master_geography_rec_type,
    p_create_update_flag                    IN     VARCHAR2,
    x_return_status                         IN OUT NOCOPY VARCHAR2
  ) IS

   l_count                 NUMBER;

Line: 551

           p_create_update_flag                    => p_create_update_flag,
           p_start_date_column_name                => 'start_date',
           p_start_date                            => p_master_geography_rec.start_date,
           p_old_start_date                        => l_start_date,
           p_end_date_column_name                  => 'end_date',
           p_end_date                              => p_master_geography_rec.end_date,
           p_old_end_date                          => l_end_date,
           x_return_status                         => x_return_status
           );

Line: 567

   IF p_create_update_flag = 'C' THEN
     -- validate geography_type
   HZ_GEO_STRUCTURE_VALIDATE_PVT.validate_geography_type(
     p_geography_type       => p_master_geography_rec.geography_type,
     p_master_ref_flag      => 'Y',
     x_return_status        => x_return_status
     );

Line: 580

    p_create_update_flag         =>'C',
    p_column                     => 'geography_name',
    p_column_value               => p_master_geography_rec.geography_name,
    p_restricted                 => 'N',
    x_return_status              => x_return_status
    );

Line: 592

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

Line: 613

      SELECT count(*) INTO l_count
        FROM FND_TIMEZONES_B
       WHERE timezone_code = p_master_geography_rec.timezone_code;

Line: 629

          SELECT count(*) INTO l_count
            FROM fnd_languages
           WHERE language_code = UPPER(p_master_geography_rec.language_code)
             AND rownum <2;

Line: 645

    SELECT count(*) INTO l_count
      FROM hz_geographies
     WHERE geography_code=p_master_geography_rec.geography_code
       AND geography_type='COUNTRY';

Line: 673

    SELECT count(*) INTO l_count
      FROM FND_TERRITORIES
     WHERE territory_code = UPPER(p_master_geography_rec.geography_code);

Line: 711

 *     p_create_update_flag           Flag that indicates 'C' for create
 *                                    and 'U' for update
 *
 *   IN/OUT:
 *     x_return_status                Return status.
 *
 * NOTES
 *
 *
 * MODIFICATION HISTORY
 *
 *   01-20-2003    Rekha Nalluri      o Created.
 *
 */

PROCEDURE validate_geography_range (
    p_geography_range_rec                  IN     HZ_GEOGRAPHY_PUB.geography_range_rec_type,
    p_create_update_flag                   IN     VARCHAR2,
    x_return_status                        IN OUT NOCOPY VARCHAR2
  ) IS

  l_count                 NUMBER;

Line: 739

    p_create_update_flag         =>p_create_update_flag,
    p_column                     => 'zone_id',
    p_column_value               => p_geography_range_rec.zone_id,
    p_restricted                 => 'N',
    x_return_status              => x_return_status
    );

Line: 748

    p_create_update_flag         =>p_create_update_flag,
    p_column                     => 'geography_from',
    p_column_value               => p_geography_range_rec.geography_from,
    p_restricted                 => 'N',
    x_return_status              => x_return_status
    );

Line: 755

    IF p_create_update_flag = 'C' THEN
    HZ_UTILITY_V2PUB.validate_mandatory (
    p_create_update_flag         =>p_create_update_flag,
    p_column                     => 'master_ref_geography_id',
    p_column_value               => p_geography_range_rec.master_ref_geography_id,
    p_restricted                 => 'N',
    x_return_status              => x_return_status
    );

Line: 765

    p_create_update_flag         =>p_create_update_flag,
    p_column                     => 'geography_to',
    p_column_value               => p_geography_range_rec.geography_to,
    p_restricted                 => 'N',
    x_return_status              => x_return_status
    );

Line: 773

    p_create_update_flag         =>p_create_update_flag,
    p_column                     => 'identifier_type',
    p_column_value               => p_geography_range_rec.identifier_type,
    p_restricted                 => 'N',
    x_return_status              => x_return_status
    );

Line: 781

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

Line: 788

   IF p_create_update_flag = 'U' THEN

     HZ_UTILITY_V2PUB.validate_mandatory (
    p_create_update_flag         =>'U',
    p_column                     => 'start_date',
    p_column_value               => p_geography_range_rec.start_date,
    p_restricted                 => 'N',
    x_return_status              => x_return_status
    );

Line: 799

    p_create_update_flag         =>'U',
    p_column                     => 'end_date',
    p_column_value               => p_geography_range_rec.end_date,
    p_restricted                 => 'N',
    x_return_status              => x_return_status
    );

Line: 809

   IF p_create_update_flag = 'C' THEN
       -- validate for start_date and end_date
     HZ_UTILITY_V2PUB.validate_start_end_date(
           p_create_update_flag                    => p_create_update_flag,
           p_start_date_column_name                => 'start_date',
           p_start_date                            => p_geography_range_rec.start_date,
           p_old_start_date                        => NULL,
           p_end_date_column_name                  => 'end_date',
           p_end_date                              => p_geography_range_rec.end_date,
           p_old_end_date                          => NULL,
           x_return_status                         => x_return_status
           );

Line: 845

      SELECT 1 INTO l_count
        FROM hz_geographies
       WHERE geography_id = p_geography_range_rec.zone_id
         AND geography_use <> 'MASTER_REF';

Line: 862

     IF p_create_update_flag = 'C' THEN
     BEGIN

       SELECT 1 INTO l_count
         FROM hz_geographies
        WHERE geography_id = p_geography_range_rec.master_ref_geography_id
          AND geography_use = 'MASTER_REF';

Line: 896

 *     p_create_update_flag           Flag that indicates 'C' for create
 *                                    and 'U' for update
 *
 *   IN/OUT:
 *     x_return_status                Return status.
 *
 * NOTES
 *
 *
 * MODIFICATION HISTORY
 *
 *   01-24-2003    Rekha Nalluri      o Created.
 *
 */

PROCEDURE validate_zone_relation (
   p_zone_relation_rec          IN   ZONE_RELATION_REC_TYPE,
   p_create_update_flag         IN   VARCHAR2,
   x_return_status              IN OUT NOCOPY VARCHAR2
   ) IS

   l_count                   NUMBER;

Line: 924

           p_create_update_flag                    => p_create_update_flag,
           p_start_date_column_name                => 'start_date',
           p_start_date                            => p_zone_relation_rec.start_date,
           p_old_start_date                        => NULL,
           p_end_date_column_name                  => 'end_date',
           p_end_date                              => p_zone_relation_rec.end_date,
           p_old_end_date                          => NULL,
           x_return_status                         => x_return_status
           );

Line: 936

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

Line: 950

           SELECT count(*) INTO l_count
          FROM hz_geographies
         WHERE geography_id = p_zone_relation_rec.included_geography_id;