APPS.AMS_VENUE_PVT SQL Statements

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

Line: 64

PROCEDURE Update_Venue_Base (
   p_api_version       IN  NUMBER,
   p_init_msg_list     IN  VARCHAR2  := FND_API.g_false,
   p_commit            IN  VARCHAR2  := FND_API.g_false,
   p_validation_level  IN  NUMBER    := FND_API.g_valid_level_full,

   x_return_status     OUT NOCOPY VARCHAR2,
   x_msg_count         OUT NOCOPY NUMBER,
   x_msg_data          OUT NOCOPY VARCHAR2,

   p_venue_rec         IN  Venue_Rec_Type,
   p_object_type       IN  VARCHAR2
);

Line: 631

      SELECT ams_venues_b_s.NEXTVAL
      FROM   dual;

Line: 635

      SELECT 1
      FROM   dual
      WHERE EXISTS (SELECT 1
                    FROM   ams_venues_vl
                    WHERE  venue_id = x_id);

Line: 642

   SELECT sysdate
   FROM dual;

Line: 702

       AMS_Utility_PVT.debug_message (l_full_name || ': Insert ' || p_object_type || ', ' || l_venue_rec.venue_id);

Line: 708

   INSERT INTO ams_venues_b (
        VENUE_ID,
       CUSTOM_SETUP_ID,
       LAST_UPDATE_DATE,
       LAST_UPDATED_BY,
       CREATION_DATE,
       CREATED_BY,
       LAST_UPDATE_LOGIN,
       OBJECT_VERSION_NUMBER,
       VENUE_TYPE_CODE,
       DIRECT_PHONE_FLAG,
       INTERNAL_FLAG,
       ENABLED_FLAG,
       RATING_CODE,
       TELECOM_CODE,
       CAPACITY,
       AREA_SIZE,
       AREA_SIZE_UOM_CODE,
       CEILING_HEIGHT,
       CEILING_HEIGHT_UOM_CODE,
       USAGE_COST,
       USAGE_COST_UOM_CODE,
       USAGE_COST_CURRENCY_CODE,
       PARENT_VENUE_ID,
       LOCATION_ID,
       DIRECTIONS,
       VENUE_CODE,
       OBJECT_TYPE,
       PARTY_ID,
       ATTRIBUTE_CATEGORY,
       ATTRIBUTE1,
       ATTRIBUTE2,
       ATTRIBUTE3,
       ATTRIBUTE4,
       ATTRIBUTE5,
       ATTRIBUTE6,
       ATTRIBUTE7,
       ATTRIBUTE8,
       ATTRIBUTE9,
       ATTRIBUTE10,
       ATTRIBUTE11,
       ATTRIBUTE12,
       ATTRIBUTE13,
       ATTRIBUTE14,
       ATTRIBUTE15
   )
   VALUES (
      l_venue_rec.venue_id,
      l_venue_rec.custom_setup_id,

      -- standard who columns
      SYSDATE,
      FND_GLOBAL.User_Id,
      SYSDATE,
      FND_GLOBAL.User_Id,
      FND_GLOBAL.Conc_Login_Id,

         1,    -- object_version_number
      l_venue_rec.venue_type_code,
      NVL (l_venue_rec.direct_phone_flag, 'N'),   -- Default is 'N'
       NVL (l_venue_rec.internal_flag, 'Y'),   -- Default is 'Y'
       NVL (l_venue_rec.enabled_flag, 'Y'),   -- Default is 'Y'

      l_venue_rec.rating_code,
      l_venue_rec.telecom_code,
      l_venue_rec.capacity,
      l_venue_rec.area_size,
      l_venue_rec.area_size_uom_code,
      l_venue_rec.ceiling_height,
      l_venue_rec.ceiling_height_uom_code,
      l_venue_rec.usage_cost,
      l_venue_rec.usage_cost_uom_code,
      l_venue_rec.usage_cost_currency_code,
      l_venue_rec.parent_venue_id,
      l_venue_rec.location_id,
      l_venue_rec.directions,
      l_venue_rec.venue_code,
      p_object_type,
      l_venue_rec.party_id,
      l_venue_rec.attribute_category,
       l_venue_rec.attribute1,
       l_venue_rec.attribute2,
       l_venue_rec.attribute3,
       l_venue_rec.attribute4,
       l_venue_rec.attribute5,
       l_venue_rec.attribute6,
       l_venue_rec.attribute7,
       l_venue_rec.attribute8,
       l_venue_rec.attribute9,
       l_venue_rec.attribute10,
       l_venue_rec.attribute11,
       l_venue_rec.attribute12,
       l_venue_rec.attribute13,
       l_venue_rec.attribute14,
       l_venue_rec.attribute15
   );

Line: 805

   INSERT INTO ams_venues_tl (
       venue_id,
      language,
       last_update_date,
       last_updated_by,
       creation_date,
       created_by,
       last_update_login,
       source_lang,
      venue_name,
       description
   )
   SELECT   l_venue_rec.venue_id,
            l.language_code,
            -- standard who columns
            SYSDATE,
            FND_GLOBAL.User_Id,
            SYSDATE,
            FND_GLOBAL.User_Id,
            FND_GLOBAL.Conc_Login_Id,
            USERENV('LANG'),
            l_venue_rec.venue_name,
            l_venue_rec.description
     FROM     fnd_languages l
     WHERE    l.installed_flag IN ('I', 'B')
     AND NOT EXISTS (SELECT  NULL
                    FROM    ams_venues_tl t
                    WHERE   t.venue_id = l_venue_rec.venue_id
                    AND     t.language = l.language_code);

Line: 935

PROCEDURE Update_Venue (
   p_api_version       IN  NUMBER,
   p_init_msg_list     IN  VARCHAR2  := FND_API.g_false,
   p_commit            IN  VARCHAR2  := FND_API.g_false,
   p_validation_level  IN  NUMBER    := FND_API.g_valid_level_full,

   x_return_status     OUT NOCOPY VARCHAR2,
   x_msg_count         OUT NOCOPY NUMBER,
   x_msg_data          OUT NOCOPY VARCHAR2,

   p_venue_rec         IN  Venue_Rec_Type
)
IS

   CURSOR c_location(ven_id IN NUMBER) IS
      SELECT   loc.address1, loc.address2, loc.city, loc.state, loc.country
      FROM     hz_parties loc, ams_venues_vl ven
      WHERE    loc.party_id = ven.party_id
     and      ven.venue_id = ven_id;

Line: 970

   L_API_NAME           CONSTANT VARCHAR2(30) := 'Update_Venue';

Line: 979

   SAVEPOINT Update_Venue;

Line: 1120

   Update_Venue_Base(  p_api_version => p_api_version
                     , p_init_msg_list => p_init_msg_list
                     , p_commit => p_commit
                     , p_validation_level => p_validation_level
                     , x_return_status => x_return_status
                     , x_msg_count => x_msg_count
                     , x_msg_data => x_msg_data
                     , p_venue_rec => l_venue_rec
                     , p_object_type => 'VENU'
                    );

Line: 1140

      ROLLBACK TO Update_Venue;

Line: 1148

      ROLLBACK TO Update_Venue;

Line: 1156

      ROLLBACK TO Update_Venue;

Line: 1169

END Update_Venue;

Line: 1176

PROCEDURE Update_Room (
   p_api_version       IN  NUMBER,
   p_init_msg_list     IN  VARCHAR2  := FND_API.g_false,
   p_commit            IN  VARCHAR2  := FND_API.g_false,
   p_validation_level  IN  NUMBER    := FND_API.g_valid_level_full,

   x_return_status     OUT NOCOPY VARCHAR2,
   x_msg_count         OUT NOCOPY NUMBER,
   x_msg_data          OUT NOCOPY VARCHAR2,

   p_venue_rec         IN  Venue_Rec_Type
)
IS
   L_API_VERSION        CONSTANT NUMBER := 1.0;

Line: 1190

   L_API_NAME           CONSTANT VARCHAR2(30) := 'Update_Room';

Line: 1198

   SAVEPOINT Update_Venue;

Line: 1241

   Update_Venue_Base(  p_api_version => p_api_version
                     , p_init_msg_list => p_init_msg_list
                     , p_commit => p_commit
                     , p_validation_level => p_validation_level
                     , x_return_status => x_return_status
                     , x_msg_count => x_msg_count
                     , x_msg_data => x_msg_data
                     , p_venue_rec => l_venue_rec
                     , p_object_type => 'ROOM'
                    );

Line: 1254

      ROLLBACK TO Update_Venue;

Line: 1262

      ROLLBACK TO Update_Venue;

Line: 1270

      ROLLBACK TO Update_Venue;

Line: 1283

END Update_Room;

Line: 1293

PROCEDURE Update_Venue_Base (
   p_api_version       IN  NUMBER,
   p_init_msg_list     IN  VARCHAR2  := FND_API.g_false,
   p_commit            IN  VARCHAR2  := FND_API.g_false,
   p_validation_level  IN  NUMBER    := FND_API.g_valid_level_full,

   x_return_status     OUT NOCOPY VARCHAR2,
   x_msg_count         OUT NOCOPY NUMBER,
   x_msg_data          OUT NOCOPY VARCHAR2,

   p_venue_rec         IN  Venue_Rec_Type,
   p_object_type       IN  VARCHAR2
)
IS
/*
   CURSOR c_location(ven_id IN NUMBER) IS
      SELECT   loc.address1, loc.address2, loc.city, loc.state, loc.country
      FROM     hz_locations loc, ams_venues_vl ven
      WHERE    loc.location_id = ven.location_id
     and      ven.venue_id = ven_id;

Line: 1323

   L_API_NAME           CONSTANT VARCHAR2(30) := 'Update_Venue_Base';

Line: 1346

         p_validation_mode    => JTF_PLSQL_API.g_update,
         x_return_status      => l_return_status
      );

Line: 1374

SELECT count(*)
INTO l_rec_cnt
FROM ams_venues_tl
where venue_id <> l_venue_rec.venue_id
and   venue_name = l_venue_rec.venue_name
and   SOURCE_LANG = USERENV('LANG');

Line: 1393

       AMS_Utility_PVT.debug_message (l_full_name || ': Update');

Line: 1396

   UPDATE ams_venues_b
   SET
   last_update_date        = SYSDATE,
   last_updated_by          = FND_GLOBAL.User_Id,
   last_update_login       = FND_GLOBAL.Conc_Login_Id,

   object_version_number   = object_version_number + 1,
        venue_type_code           = l_venue_rec.venue_type_code,
   custom_setup_id = l_venue_rec.custom_setup_id,
   direct_phone_flag       = NVL (l_venue_rec.direct_phone_flag, 'N'),
   internal_flag      = NVL (l_venue_rec.internal_flag, 'Y'),
   enabled_flag      = NVL (l_venue_rec.enabled_flag, 'Y'),
        rating_code             = l_venue_rec.rating_code,
        capacity                = l_venue_rec.capacity,
        area_size      = l_venue_rec.area_size,
   area_size_uom_code   = l_venue_rec.area_size_uom_code,
   ceiling_height      = l_venue_rec.ceiling_height,
   ceiling_height_uom_code   = l_venue_rec.ceiling_height_uom_code,
   usage_cost      = l_venue_rec.usage_cost,
   usage_cost_uom_code   = l_venue_rec.usage_cost_uom_code,
   usage_cost_currency_code = l_venue_rec.usage_cost_currency_code,
   parent_venue_id      = l_venue_rec.parent_venue_id,
   location_id      = l_venue_rec.location_id,
   directions      = l_venue_rec.directions,
   venue_code      = l_venue_rec.venue_code,
   telecom_code    = l_venue_rec.telecom_code, -- Added Bug 4083293
   attribute_category      = l_venue_rec.attribute_category,
   attribute1       = l_venue_rec.attribute1,
   attribute2       = l_venue_rec.attribute2,
   attribute3       = l_venue_rec.attribute3,
   attribute4       = l_venue_rec.attribute4,
   attribute5       = l_venue_rec.attribute5,
   attribute6       = l_venue_rec.attribute6,
   attribute7       = l_venue_rec.attribute7,
   attribute8       = l_venue_rec.attribute8,
   attribute9       = l_venue_rec.attribute9,
   attribute10       = l_venue_rec.attribute10,
   attribute11       = l_venue_rec.attribute11,
   attribute12       = l_venue_rec.attribute12,
   attribute13       = l_venue_rec.attribute13,
   attribute14       = l_venue_rec.attribute14,
   attribute15       = l_venue_rec.attribute15
   WHERE   venue_id = l_venue_rec.venue_id
     AND   object_version_number = l_venue_rec.object_version_number;

Line: 1450

   UPDATE ams_venues_tl
   SET
      last_update_date    = SYSDATE,
      last_updated_by    = FND_GLOBAL.User_Id,
      last_update_login    = FND_GLOBAL.Conc_Login_Id,
                source_lang          = USERENV('LANG'),
      venue_name         = l_venue_rec.venue_name,
          description       = l_venue_rec.description
     WHERE venue_id = l_venue_rec.venue_id
     AND   USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);

Line: 1490

      ROLLBACK TO Update_Venue;

Line: 1498

      ROLLBACK TO Update_Venue;

Line: 1506

      ROLLBACK TO Update_Venue;

Line: 1518

END Update_Venue_Base;

Line: 1526

PROCEDURE Delete_Venue (
   p_api_version       IN  NUMBER,
   p_init_msg_list     IN  VARCHAR2  := FND_API.g_false,
   p_commit            IN  VARCHAR2  := FND_API.g_false,
   p_validation_level  IN  NUMBER    := FND_API.g_valid_level_full,

   x_return_status     OUT NOCOPY VARCHAR2,
   x_msg_count         OUT NOCOPY NUMBER,
   x_msg_data          OUT NOCOPY VARCHAR2,

   p_venue_id          IN  NUMBER,
   p_object_version    IN  NUMBER
)
IS
   l_api_version CONSTANT NUMBER       := 1.0;

Line: 1541

   l_api_name    CONSTANT VARCHAR2(30) := 'Delete_Venue';

Line: 1545

   SAVEPOINT Delete_Venue;

Line: 1573

       AMS_Utility_PVT.debug_message (l_full_name || ': Delete');

Line: 1577

   DELETE FROM ams_venues_tl
   WHERE  venue_id = p_venue_id;

Line: 1588

   DELETE FROM ams_venues_b
   WHERE  venue_id = p_venue_id
   AND    object_version_number = p_object_version;

Line: 1613

      ROLLBACK TO Delete_Venue;

Line: 1621

      ROLLBACK TO Delete_Venue;

Line: 1629

      ROLLBACK TO Delete_Venue;

Line: 1641

END Delete_Venue;

Line: 1677

      SELECT object_version_number
      FROM   ams_venues_vl
      WHERE  venue_id = p_venue_id
      AND    object_version_number = p_object_version
      FOR UPDATE NOWAIT;

Line: 2003

  /* dbiswas added the following two conditions to check that no more than 15 chars are inserted for
     ceiling height and capacity on May 9, 2003 for bug 2950429 */

   IF(length(p_venue_rec.ceiling_height) > 15)
   THEN
       IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR)
       THEN
          Fnd_Message.set_name('AMS', 'AMS_OVERSIZE_CEILING_HEIGHT');

Line: 2026

   /* end update by dbiswas on May 9, 2003 */

   IF x_return_status <> FND_API.g_ret_sts_success THEN
      RETURN;

Line: 2071

   x_venue_rec.last_update_date := FND_API.g_miss_date;

Line: 2072

   x_venue_rec.last_updated_by := FND_API.g_miss_num;

Line: 2075

   x_venue_rec.last_update_login := FND_API.g_miss_num;

Line: 2130

      SELECT   *
      FROM     ams_venues_vl
      WHERE    venue_id = id_in;

Line: 2135

      SELECT   address1, address2, city, state, country
      FROM     hz_parties
      WHERE    party_id = p_party_id;

Line: 2500

   SELECT 1 FROM DUAL WHERE EXISTS (select 1 from ams_venues_vl
          where VENUE_NAME = vnu_name_in);

Line: 2504

   SELECT 1 FROM DUAL WHERE EXISTS (select 1 from ams_venues_vl
          where VENUE_NAME = room_name_in and parent_venue_id = venue_id_in);

Line: 2508

   SELECT 1 FROM DUAL WHERE EXISTS (select 1 from ams_venues_vl
          where VENUE_NAME = vnu_name_in
          and  VENUE_ID = vnu_id_in);

Line: 2564

   IF (    (p_validation_mode = JTF_PLSQL_API.g_create OR  p_validation_mode = JTF_PLSQL_API.g_update )
       AND (p_object_type = 'ROOM')
      )
   THEN

      open c_room_name(p_venue_rec.venue_name, p_venue_rec.parent_venue_id);