APPS.AMS_IBA_PL_SITES_B_PKG SQL Statements

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

Line: 34

PROCEDURE Insert_Row(
          px_site_id   IN OUT NOCOPY NUMBER,
          p_site_ref_code    VARCHAR2,
          p_site_category_type    VARCHAR2,
          p_site_category_object_id    NUMBER,
          p_status_code    VARCHAR2,
          p_created_by    NUMBER,
          p_creation_date    DATE,
          p_last_updated_by    NUMBER,
          p_last_update_date    DATE,
          p_last_update_login    NUMBER,
          px_object_version_number   IN OUT NOCOPY NUMBER,
          p_name in VARCHAR2,
          p_description in VARCHAR2
)

IS
   x_rowid    VARCHAR2(30);

Line: 59

   INSERT INTO ams_iba_pl_sites_b(
           site_id,
           site_ref_code,
           site_category_type,
           site_category_object_id,
           status_code,
           created_by,
           creation_date,
           last_updated_by,
           last_update_date,
           last_update_login,
           object_version_number
   ) VALUES (
           DECODE( px_site_id, FND_API.g_miss_num, NULL, px_site_id),
           DECODE( p_site_ref_code, FND_API.g_miss_char, NULL, p_site_ref_code),
           DECODE( p_site_category_type, FND_API.g_miss_char, NULL, p_site_category_type),
           DECODE( p_site_category_object_id, FND_API.g_miss_num, NULL, p_site_category_object_id),
           DECODE( p_status_code, FND_API.g_miss_char, NULL, p_status_code),
           DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
           DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
           DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
           DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
           DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
           DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number));

Line: 84

  INSERT INTO ams_iba_pl_sites_tl (
		site_id,
		name,
		description,
		created_by,
		creation_date,
		last_updated_by,
		last_update_date,
		last_update_login,
		object_version_number,
		language,
		source_lang
  )
  SELECT
		DECODE( px_site_id, FND_API.g_miss_num, NULL, px_site_id),
          DECODE( p_name, FND_API.g_miss_char, NULL, p_name),
		DECODE( p_description, FND_API.g_miss_char, NULL, p_description),
          DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
		DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
		DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
		DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
		DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
	     DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
		l.language_code,
		USERENV('LANG')
  FROM 	fnd_languages l
  WHERE 	l.installed_flag in ('I', 'B')
  AND 	NOT EXISTS(
			    	 SELECT NULL
				 FROM ams_iba_pl_sites_tl t
				 WHERE t.site_id = DECODE( px_site_id, FND_API.g_miss_num, NULL, px_site_id)
				    AND t.language = l.language_code);

Line: 117

END Insert_Row;

Line: 131

PROCEDURE Update_Row(
          p_site_id    NUMBER,
          p_site_ref_code    VARCHAR2,
          p_site_category_type    VARCHAR2,
          p_site_category_object_id    NUMBER,
          p_status_code    VARCHAR2,
          p_created_by    NUMBER,
          p_creation_date    DATE,
          p_last_updated_by    NUMBER,
          p_last_update_date    DATE,
          p_last_update_login    NUMBER,
          p_object_version_number    NUMBER,
	  p_name	VARCHAR2,
	  p_description VARCHAR2)
IS
BEGIN
   IF (AMS_DEBUG_HIGH_ON) THEN

   AMS_UTILITY_PVT.debug_message('table handler : before update site_id = ' || p_site_id );

Line: 151

    UPDATE ams_iba_pl_sites_b
    SET
              last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
              last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
              last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
              site_ref_code = DECODE( p_site_ref_code, FND_API.g_miss_char, site_ref_code, p_site_ref_code),
	      site_category_object_id  = DECODE(p_site_category_object_id , FND_API.g_miss_num, site_category_object_id, p_site_category_object_id )
	      -- anchaudh 12/26/2002 : fixed bug#2678933.
   WHERE site_id = p_site_id;

Line: 166

  UPDATE ams_iba_pl_sites_tl
  SET
	 	name 			= DECODE(p_name,FND_API.g_miss_char,name,p_name),
		description 		= DECODE(p_description,FND_API.g_miss_char,description,p_description),
		last_update_date 	= DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
		last_updated_by 	= DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
		last_update_login 	= DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
		source_lang             = userenv('LANG')
  where 	site_id 		= p_site_id
  AND 	USERENV('LANG') in (language, source_lang);

Line: 182

END Update_Row;

Line: 196

PROCEDURE Delete_Row(
    p_site_id  NUMBER,
    p_object_version_number NUMBER)
 IS
BEGIN
   IF (AMS_DEBUG_HIGH_ON) THEN

   AMS_UTILITY_PVT.debug_message('table handler : before delete of b; site_id = ' || p_site_id || ' object_version_num = ' || p_object_version_number);

Line: 205

   DELETE FROM ams_iba_pl_sites_b
   WHERE site_id = p_site_id
   AND   object_version_number = p_object_version_number;

Line: 213

   AMS_UTILITY_PVT.debug_message('table handler : After delete of b; site_id = ' || p_site_id || ' object_version_num = ' || p_object_version_number);

Line: 221

   DELETE FROM ams_iba_pl_sites_tl
   WHERE site_id = p_site_id
   AND   object_version_number = p_object_version_number;

Line: 228

END Delete_Row ;

Line: 233

  delete from AMS_IBA_PL_SITES_TL T
  where not exists
    (select NULL
    from AMS_IBA_PL_SITES_B B
    where B.SITE_ID = T.SITE_ID
    );

Line: 240

  update AMS_IBA_PL_SITES_TL T set (
      NAME,
      DESCRIPTION
    ) = (select
      B.NAME,
      B.DESCRIPTION
    from AMS_IBA_PL_SITES_TL B
    where B.SITE_ID = T.SITE_ID
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.SITE_ID,
      T.LANGUAGE
  ) in (select
      SUBT.SITE_ID,
      SUBT.LANGUAGE
    from AMS_IBA_PL_SITES_TL SUBB, AMS_IBA_PL_SITES_TL SUBT
    where SUBB.SITE_ID = SUBT.SITE_ID
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.NAME <> SUBT.NAME
      or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
      or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
      or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
  ));

Line: 264

  insert into AMS_IBA_PL_SITES_TL (
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN,
    SECURITY_GROUP_ID,
    OBJECT_VERSION_NUMBER,
    SITE_ID,
    NAME,
    DESCRIPTION,
    CREATED_BY,
    CREATION_DATE,
    LANGUAGE,
    SOURCE_LANG
  ) select
    B.LAST_UPDATED_BY,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATE_LOGIN,
    B.SECURITY_GROUP_ID,
    B.OBJECT_VERSION_NUMBER,
    B.SITE_ID,
    B.NAME,
    B.DESCRIPTION,
    B.CREATED_BY,
    B.CREATION_DATE,
    L.LANGUAGE_CODE,
    B.SOURCE_LANG
  from AMS_IBA_PL_SITES_TL B, FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and B.LANGUAGE = userenv('LANG')
  and not exists
    (select NULL
    from AMS_IBA_PL_SITES_TL T
    where T.SITE_ID = B.SITE_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 322

          p_last_updated_by    NUMBER,
          p_last_update_date    DATE,
          p_last_update_login    NUMBER,
          p_object_version_number    NUMBER,
          p_name in VARCHAR2,
          p_description in VARCHAR2
)

 IS
   CURSOR C IS
        SELECT *
         FROM AMS_IBA_PL_SITES_B
        WHERE SITE_ID =  p_SITE_ID
        FOR UPDATE of SITE_ID NOWAIT;

Line: 342

        FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');

Line: 366

       AND (    ( Recinfo.last_updated_by = p_last_updated_by)
            OR (    ( Recinfo.last_updated_by IS NULL )
                AND (  p_last_updated_by IS NULL )))
       AND (    ( Recinfo.last_update_date = p_last_update_date)
            OR (    ( Recinfo.last_update_date IS NULL )
                AND (  p_last_update_date IS NULL )))
       AND (    ( Recinfo.last_update_login = p_last_update_login)
            OR (    ( Recinfo.last_update_login IS NULL )
                AND (  p_last_update_login IS NULL )))
       AND (    ( Recinfo.object_version_number = p_object_version_number)
            OR (    ( Recinfo.object_version_number IS NULL )
                AND (  p_object_version_number IS NULL )))
       ) THEN
       RETURN;

Line: 394

  cursor c_last_updated_by is
    select last_updated_by
    from ams_iba_pl_sites_tl
    where site_id = x_site_id
    and  USERENV('LANG') = LANGUAGE;

Line: 400

    l_luby number; --last updated by

Line: 403

    open c_last_updated_by;

Line: 404

    fetch c_last_updated_by into l_luby;

Line: 405

    close c_last_updated_by;

Line: 410

    update ams_iba_pl_sites_tl set
       name = nvl(x_name, name),
       description = nvl(x_description, description),
       source_lang = userenv('LANG'),
       last_update_date = sysdate,
       last_updated_by = decode(x_owner, 'SEED', 1,
				'ORACLE', 2,
				'SYSADMIN', 0, -1),
       last_update_login = 0
    where  site_id = x_site_id
    and      userenv('LANG') in (language, source_lang);

Line: 444

     select object_version_number
     from    ams_iba_pl_sites_b
     where  site_id =  x_site_id;*/

Line: 449

     select last_updated_by, nvl(object_version_number,1)
     from ams_iba_pl_sites_b
     where site_id =  x_site_id;

Line: 454

     select 'x'
     from   ams_iba_pl_sites_b
     where  site_id = x_site_id;

Line: 459

      select ams_iba_pl_sites_b_s.nextval
      from dual;

Line: 482

      AMS_IBA_PL_SITES_B_PKG.Insert_Row (
         px_site_id => l_site_id,
         p_site_ref_code => x_site_ref_code,
	 p_site_category_type => x_site_ctgy_type,
	 p_site_category_object_id => x_site_ctgy_obj_id,
         p_status_code => x_status_code,
         p_created_by => l_user_id,
         p_creation_date => SYSDATE,
         p_last_updated_by => l_user_id,
         p_last_update_date => SYSDATE,
         p_last_update_login => 1,
         px_object_version_number => l_obj_verno,
         p_name => x_name,
         p_description => x_description
      );

Line: 504

      AMS_IBA_PL_SITES_B_PKG.UPDATE_ROW (
         p_site_id => x_site_id,
         p_site_ref_code => x_site_ref_code,
	 p_site_category_type => x_site_ctgy_type,
	 p_site_category_object_id => x_site_ctgy_obj_id,
         p_status_code => x_status_code,
         p_created_by => l_user_id,
         p_creation_date => SYSDATE,
         p_last_updated_by => l_user_id,
         p_last_update_date => SYSDATE,
         p_last_update_login => 1,
         p_object_version_number => l_obj_verno,
         p_name => x_name,
         p_description => x_description
      );