APPS.JTF_TTY_GEOSOURCE_PUB SQL Statements

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

Line: 52

      SELECT 'Y'
      into    p_geo_type_exists_flag
      FROM    fnd_lookups
      WHERE   lookup_type = 'JTF_TTY_GEO_TYPE'
      AND     lookup_code = p_geo_type
      AND     ROWNUM < 2;

Line: 78

      SELECT 'Y'
      into    p_parent_exists_flag
      FROM    jtf_tty_geographies
      WHERE   geo_type = 'COUNTRY'
      AND     geo_code = p_country_code
      AND     ROWNUM < 2;

Line: 98

      SELECT 'Y'
      into    p_exists_flag
      FROM    jtf_tty_geographies
      WHERE   geo_type = 'STATE'
      AND     country_code = p_country_code
      AND     state_code = p_state_code
      AND     ROWNUM < 2;

Line: 120

      SELECT 'Y'
      into    p_parent_exists_flag
      FROM    jtf_tty_geographies
      WHERE   geo_type = 'COUNTRY'
      AND     geo_code = p_country_code
      AND     ROWNUM < 2;

Line: 140

      SELECT 'Y'
      into    p_exists_flag
      FROM    jtf_tty_geographies
      WHERE   geo_type = 'PROVINCE'
      AND     country_code = p_country_code
      AND     province_code = p_province_code
      AND     ROWNUM < 2;

Line: 163

       SELECT 'Y'
       into    p_parent_exists_flag
       FROM    jtf_tty_geographies
       WHERE   geo_type = 'STATE'
       AND   country_code = p_country_code
       AND     state_code = p_state_code
       AND     ROWNUM < 2;

Line: 171

       SELECT 'Y'
       into    p_parent_exists_flag
       FROM    jtf_tty_geographies
       WHERE   geo_type = 'PROVINCE'
       AND   country_code = p_country_code
       AND    province_code = p_province_code
       AND     ROWNUM < 2;

Line: 194

      SELECT 'Y'
      into    p_parent_exists_flag
      FROM    jtf_tty_geographies
      WHERE   ((geo_type = 'STATE' and (p_state_code is not null and p_county_code is null))
               or
               (geo_type = 'PROVINCE' and (p_province_code is not null and p_county_code is null))
               or
               (geo_type = 'COUNTY' and p_county_code is not null))
      AND   country_code = p_country_code
      AND    ((state_code = p_state_code  and p_state_code is not null)
        OR
            (province_code = p_province_code  and p_province_code is not null))
      AND   (p_county_code is null or county_code = p_county_code)
      AND     ROWNUM < 2;

Line: 223

      SELECT 'Y'
      into    p_parent_exists_flag
      FROM    jtf_tty_geographies
      WHERE   (
            (p_city_code is not null and geo_type = 'CITY')
            OR
            ((p_city_code is null and p_county_code is not null) and geo_type = 'COUNTY')
            OR
            ((p_city_code is null and p_county_code is null and p_state_code is not null) and geo_type = 'STATE')
            OR
            ((p_city_code is null and p_county_code is null and p_province_code is not null) and geo_type = 'PROVINCE')
             )
      AND     country_code = p_country_code
      AND     ((state_code = p_state_code  and p_state_code is not null)
              OR
              (province_code = p_province_code and p_province_code is not null))
      AND     (p_county_code is null or county_code = p_county_code)
      AND     (p_city_code is null or city_code = p_city_code)
      AND     ROWNUM < 2;

Line: 260

   INSERT INTO jtf_tty_geographies(
            geo_id,
            geo_name,
            geo_type,
            geo_code,
            country_code,
            state_code,
            province_code,
            county_code,
            city_code,
            postal_code,
            object_version_number,
            created_by,
            creation_date,
            last_updated_by,
            last_update_date)
   VALUES(
           jtf_tty_geographies_s.nextval,
           p_geo_name,
           p_geo_type,
           p_geo_code,
           p_country_code,
           decode(p_geo_type, 'STATE', p_geo_code,  p_state_code),
           decode(p_geo_type, 'PROVINCE', p_geo_code, p_province_code),
           decode(p_geo_type, 'COUNTY', p_geo_code, p_county_code),
           decode(p_geo_type, 'CITY', p_geo_code, p_city_code),
           decode(p_geo_type, 'POSTAL_CODE', p_geo_code, p_postal_code),
           1,
           p_user_id,
           p_date,
           p_user_id,
           p_date);

Line: 313

* Updates a Geography
* Non Required parameters: state code, province code, county
*                          code, city and postal code
************************************************************/
PROCEDURE update_geo(
                     p_geo_id                     IN   VARCHAR2,
                     p_geo_name                   IN   VARCHAR2,
                     x_return_status              IN OUT  NOCOPY VARCHAR2,
                     x_error_msg                  IN OUT  NOCOPY VARCHAR2)
AS
   l_api_name CONSTANT VARCHAR2(30) :=  'UPDATE_GEO';

Line: 336

       fnd_message.set_name('JTF', 'JTF_TTY_UPDATE_NOT_ENOUGHVALUES');

Line: 340

      SELECT 'Y'
      into    p_exists_flag
      FROM    jtf_tty_geographies
      WHERE   geo_id = p_geo_id;

Line: 352

       fnd_message.set_token('p_action_type','updated');

Line: 359

   UPDATE jtf_tty_geographies
   SET    geo_name = p_geo_name,
          last_updated_by = p_user_id,
          last_update_date = p_date
   WHERE  geo_id   = p_geo_id;

Line: 380

END update_geo;

Line: 383

* Deletes a Geography
* Non Required parameters: state code, province code, county
*                          code, city and postal code
************************************************************/
PROCEDURE delete_geo(
                     p_geo_type                   IN   VARCHAR2,
                     p_geo_code                   IN   VARCHAR2,
                     p_country_code               IN   VARCHAR2,
                     p_state_code                 IN   VARCHAR2 default null,
                     p_province_code              IN   VARCHAR2 default null,
                     p_county_code                IN   VARCHAR2 default null,
                     p_city_code                  IN   VARCHAR2 default null,
                     p_postal_code                IN   VARCHAR2 default null,
                     p_delete_cascade_flag        IN   VARCHAR2 default 'N',
	             x_return_status              IN OUT  NOCOPY VARCHAR2,
	             x_error_msg                  IN OUT  NOCOPY VARCHAR2)
AS
   l_api_name CONSTANT VARCHAR2(30) :=  'DELETE_GEO';

Line: 411

     SELECT 'Y'
     INTO p_exists_flag
     FROM jtf_tty_geographies
     WHERE country_code = p_country_code
     AND   geo_code = p_geo_code
     AND   geo_type = p_geo_type
     AND   (p_state_code is null or state_code = p_state_code)
     AND   (p_province_code is null or province_code = p_province_code)
     AND   (p_county_code is null or county_code = p_county_code)
     AND   (p_city_code is null or city_code = p_city_code)
     AND   (p_postal_code is null or postal_code = p_postal_code)
     AND ROWNUM < 2;

Line: 428

      SELECT 'Y'
      into    p_geo_type_exists_flag
      FROM    fnd_lookups
      WHERE   lookup_type = 'JTF_TTY_GEO_TYPE'
      AND     lookup_code = p_geo_type
      AND     ROWNUM < 2;

Line: 457

    if (p_delete_cascade_flag = 'N') THEN
     BEGIN
      SELECT 'Y'
      into    p_child_exists_flag
      FROM    jtf_tty_geographies
      WHERE   geo_type <> 'COUNTRY'
      AND     country_code = p_country_code
      AND     ROWNUM < 2;

Line: 476

    if (p_delete_cascade_flag = 'N') THEN
     BEGIN
      SELECT 'Y'
      into    p_child_exists_flag
      FROM    jtf_tty_geographies
      WHERE   (geo_type = 'COUNTY'
               or geo_type = 'CITY'
               or geo_type = 'POSTAL_CODE')
      AND     country_code = p_country_code
      AND     state_code = p_geo_code
      AND     ROWNUM < 2;

Line: 498

    if (p_delete_cascade_flag = 'N') THEN
     BEGIN
      SELECT 'Y'
      into    p_child_exists_flag
      FROM    jtf_tty_geographies
      WHERE   (geo_type = 'COUNTY'
               or geo_type = 'CITY'
               or geo_type = 'POSTAL_CODE')
      AND     country_code = p_country_code
      AND     province_code = p_geo_code
      AND     ROWNUM < 2;

Line: 520

    if (p_delete_cascade_flag = 'N') THEN
     BEGIN
      SELECT 'Y'
      into    p_child_exists_flag
      FROM    jtf_tty_geographies
      WHERE   (geo_type = 'CITY' or geo_type = 'POSTAL_CODE')
      AND     country_code = p_country_code
      AND     ((p_province_code is not null or province_code = p_province_code)
               OR
               (p_state_code is not null or state_code = p_state_code))
      AND     county_code = p_geo_code
      AND     ROWNUM < 2;

Line: 543

    if (p_delete_cascade_flag = 'N') THEN
     BEGIN
      SELECT 'Y'
      into    p_child_exists_flag
      FROM    jtf_tty_geographies
      WHERE   geo_type = 'POSTAL_CODE'
      AND     country_code = p_country_code
      AND     ((p_province_code is not null or province_code = p_province_code)
               OR
               (p_state_code is not null or state_code = p_state_code))
      AND     (p_county_code is null or county_code = p_county_code)
      AND     city_code = p_city_code
      AND     ROWNUM < 2;

Line: 574

     if (p_delete_cascade_flag = 'Y') THEN
       DELETE from jtf_tty_geographies
       WHERE state_code = p_geo_code
       AND   country_code = p_country_code;

Line: 579

       DELETE from jtf_tty_geographies
       WHERE state_code = p_geo_code
       AND   geo_type = 'STATE'
       AND   country_code = p_country_code;

Line: 604

END delete_geo;