APPS.CSC_PROF_TABLE_COLUMNS_PKG SQL Statements

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

Line: 17

PROCEDURE Insert_Row(
          px_TABLE_COLUMN_ID   IN OUT NOCOPY NUMBER,
          p_BLOCK_ID    NUMBER,
          p_TABLE_NAME    VARCHAR2,
          p_COLUMN_NAME    VARCHAR2,
          p_LABEL    VARCHAR2,
          p_TABLE_ALIAS VARCHAR2,
          p_COLUMN_SEQUENCE NUMBER,
	       p_DRILLDOWN_COLUMN_FLAG VARCHAR2,
          p_LAST_UPDATE_DATE    DATE,
          p_LAST_UPDATED_BY    NUMBER,
          p_CREATION_DATE    DATE,
          p_CREATED_BY    NUMBER,
          p_LAST_UPDATE_LOGIN    NUMBER,
          p_SEEDED_FLAG   VARCHAR2,
          x_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER)

 IS
   CURSOR C2 IS SELECT CSC_PROF_TABLE_COLUMNS_S.nextval FROM sys.dual;

Line: 57

   INSERT INTO CSC_PROF_TABLE_COLUMNS_B(
           TABLE_COLUMN_ID,
           BLOCK_ID,
           TABLE_NAME,
           COLUMN_NAME,
           ALIAS_NAME,
           COLUMN_SEQUENCE,
	   DRILLDOWN_COLUMN_FLAG,
           LAST_UPDATE_DATE,
           LAST_UPDATED_BY,
           CREATION_DATE,
           CREATED_BY,
           LAST_UPDATE_LOGIN,
           SEEDED_FLAG,
           OBJECT_VERSION_NUMBER
          ) VALUES (
           px_TABLE_COLUMN_ID,
           decode( p_BLOCK_ID, CSC_CORE_UTILS_PVT.G_MISS_NUM, NULL, p_BLOCK_ID),
           decode( p_TABLE_NAME, CSC_CORE_UTILS_PVT.G_MISS_CHAR, NULL, p_TABLE_NAME),
           decode( p_COLUMN_NAME, CSC_CORE_UTILS_PVT.G_MISS_CHAR, NULL, p_COLUMN_NAME),
           decode( p_TABLE_ALIAS, CSC_CORE_UTILS_PVT.G_MISS_CHAR,NULL,p_TABLE_ALIAS),
           decode( p_COLUMN_SEQUENCE,CSC_CORE_UTILS_PVT.G_MISS_NUM,NULL,p_COLUMN_SEQUENCE),
	        decode( p_DRILLDOWN_COLUMN_FLAG,CSC_CORE_UTILS_PVT.G_MISS_CHAR,NULL,p_DRILLDOWN_COLUMN_FLAG),
           decode( p_LAST_UPDATE_DATE, CSC_CORE_UTILS_PVT.G_MISS_DATE, NULL, p_LAST_UPDATE_DATE),
           decode( p_LAST_UPDATED_BY, CSC_CORE_UTILS_PVT.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
           decode( p_CREATION_DATE, CSC_CORE_UTILS_PVT.G_MISS_DATE, NULL, p_CREATION_DATE),
           decode( p_CREATED_BY, CSC_CORE_UTILS_PVT.G_MISS_NUM, NULL, p_CREATED_BY),
           decode( p_LAST_UPDATE_LOGIN, CSC_CORE_UTILS_PVT.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
           decode( p_SEEDED_FLAG, CSC_CORE_UTILS_PVT.G_MISS_CHAR,NULL,ps_SEEDED_FLAG),
           l_OBJECT_VERSION_NUMBER);

Line: 91

   INSERT INTO CSC_PROF_TABLE_COLUMNS_TL(
           TABLE_COLUMN_ID,
           LABEL,
           CREATED_BY,
           CREATION_DATE,
           LAST_UPDATED_BY,
           LAST_UPDATE_DATE,
           LAST_UPDATE_LOGIN,
           LANGUAGE,
           SOURCE_LANG
           ) select
           px_TABLE_COLUMN_ID,
    	     decode( p_LABEL, CSC_CORE_UTILS_PVT.G_MISS_CHAR, NULL, p_LABEL),
           decode( p_CREATED_BY, CSC_CORE_UTILS_PVT.G_MISS_NUM, NULL, p_CREATED_BY),
           decode( p_CREATION_DATE, CSC_CORE_UTILS_PVT.G_MISS_DATE, NULL, p_CREATION_DATE),
           decode( p_LAST_UPDATED_BY, CSC_CORE_UTILS_PVT.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
           decode( p_LAST_UPDATE_DATE, CSC_CORE_UTILS_PVT.G_MISS_DATE, NULL, p_LAST_UPDATE_DATE),
           decode( p_LAST_UPDATE_LOGIN, CSC_CORE_UTILS_PVT.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
    	     L.LANGUAGE_CODE,
           userenv('LANG')
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists
    (select NULL
    from CSC_PROF_TABLE_COLUMNS_TL T
    where T.TABLE_COLUMN_ID = Px_TABLE_COLUMN_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 120

End Insert_Row;

Line: 122

PROCEDURE Update_Row(
          p_TABLE_COLUMN_ID    NUMBER,
          p_BLOCK_ID    NUMBER,
          p_TABLE_NAME    VARCHAR2,
          p_COLUMN_NAME    VARCHAR2,
          p_LABEL    VARCHAR2,
          p_TABLE_ALIAS VARCHAR2,
          p_COLUMN_SEQUENCE NUMBER,
	       p_DRILLDOWN_COLUMN_FLAG VARCHAR2,
          p_LAST_UPDATE_DATE    DATE,
          p_LAST_UPDATED_BY    NUMBER,
          p_LAST_UPDATE_LOGIN    NUMBER,
          p_SEEDED_FLAG    VARCHAR2,
          px_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER)

 IS
 BEGIN

    Update CSC_PROF_TABLE_COLUMNS_B
    SET
       BLOCK_ID = decode( p_BLOCK_ID, CSC_CORE_UTILS_PVT.G_MISS_NUM, BLOCK_ID, p_BLOCK_ID),
       TABLE_NAME = decode( p_TABLE_NAME, CSC_CORE_UTILS_PVT.G_MISS_CHAR, TABLE_NAME, p_TABLE_NAME),
       COLUMN_NAME = decode( p_COLUMN_NAME, CSC_CORE_UTILS_PVT.G_MISS_CHAR, COLUMN_NAME, p_COLUMN_NAME),
              -- LABEL = decode( p_LABEL, CSC_CORE_UTILS_PVT.G_MISS_CHAR, LABEL, p_LABEL),
	    ALIAS_NAME = decode(p_TABLE_ALIAS,CSC_CORE_UTILS_PVT.G_MISS_CHAR,ALIAS_NAME,p_TABLE_ALIAS),
	    COLUMN_SEQUENCE = decode(p_COLUMN_SEQUENCE, CSC_CORE_UTILS_PVT.G_MISS_NUM,COLUMN_SEQUENCE,p_COLUMN_SEQUENCE),
		 DRILLDOWN_COLUMN_FLAG = decode(p_DRILLDOWN_COLUMN_FLAG,CSC_CORE_UTILS_PVT.G_MISS_CHAR,DRILLDOWN_COLUMN_FLAG,p_DRILLDOWN_COLUMN_FLAG),
       LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, CSC_CORE_UTILS_PVT.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
       LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, CSC_CORE_UTILS_PVT.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
       LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, CSC_CORE_UTILS_PVT.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
       SEEDED_FLAG = decode( p_SEEDED_FLAG, CSC_CORE_UTILS_PVT.G_MISS_CHAR,SEEDED_FLAG, NVL(p_SEEDED_FLAG,SEEDED_FLAG)),
       OBJECT_VERSION_NUMBER = object_version_number + 1
    where TABLE_COLUMN_ID = p_TABLE_COLUMN_ID
    RETURNING OBJECT_VERSION_NUMBER INTO px_OBJECT_VERSION_NUMBER;

Line: 157

    Update CSC_PROF_TABLE_COLUMNS_TL
    SET
      LABEL = decode( p_LABEL, CSC_CORE_UTILS_PVT.G_MISS_CHAR, LABEL, p_LABEL),
      LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, CSC_CORE_UTILS_PVT.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
      LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, CSC_CORE_UTILS_PVT.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
      LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, CSC_CORE_UTILS_PVT.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
    	SOURCE_LANG = userenv('LANG')
    where TABLE_COLUMN_ID = P_TABLE_COLUMN_ID
    and userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 170

END Update_Row;

Line: 176

  cursor c is select
      BLOCK_ID,
      TABLE_NAME,
      ALIAS_NAME,
      COLUMN_NAME,
      COLUMN_SEQUENCE,
      OBJECT_VERSION_NUMBER
    from CSC_PROF_TABLE_COLUMNS_B
    where TABLE_COLUMN_ID = P_TABLE_COLUMN_ID
    and object_version_number = p_object_version_number
    for update of TABLE_COLUMN_ID nowait;

Line: 189

  cursor c1 is select
      LABEL,
      decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
    from CSC_PROF_TABLE_COLUMNS_TL
    where TABLE_COLUMN_ID = P_TABLE_COLUMN_ID
    and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
    for update of TABLE_COLUMN_ID nowait;

Line: 208

procedure DELETE_ROW (
  P_TABLE_COLUMN_ID       NUMBER,
  P_OBJECT_VERSION_NUMBER NUMBER
) is
begin
  delete from CSC_PROF_TABLE_COLUMNS_TL
  where TABLE_COLUMN_ID = P_TABLE_COLUMN_ID;

Line: 220

  delete from CSC_PROF_TABLE_COLUMNS_B
  where TABLE_COLUMN_ID = P_TABLE_COLUMN_ID
  and OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER;

Line: 227

end DELETE_ROW;

Line: 229

Procedure DELETE_EXISTING_ROW (
	p_BLOCK_ID 		NUMBER,
   p_TABLE_NAME 	VARCHAR2,
   p_COLUMN_NAME	VARCHAR2) IS
Begin
   DELETE FROM CSC_PROF_TABLE_COLUMNS_TL
     WHERE TABLE_COLUMN_ID IN (SELECT TABLE_COLUMN_ID FROM CSC_PROF_TABLE_COLUMNS_B
       WHERE BLOCK_ID = p_BLOCK_ID
       AND TABLE_NAME = p_TABLE_NAME
       AND COLUMN_NAME = p_COLUMN_NAME);

Line: 240

      DELETE FROM CSC_PROF_TABLE_COLUMNS_B
         WHERE BLOCK_ID = p_BLOCK_ID
         AND TABLE_NAME = p_TABLE_NAME
         AND COLUMN_NAME = p_COLUMN_NAME ;

Line: 245

End Delete_Existing_Row;

Line: 250

  delete from CSC_PROF_TABLE_COLUMNS_TL T
  where not exists
    (select NULL
    from CSC_PROF_TABLE_COLUMNS_B B
    where B.TABLE_COLUMN_ID = T.TABLE_COLUMN_ID
    );

Line: 257

  update CSC_PROF_TABLE_COLUMNS_TL T set (
      LABEL
    ) = (select
      B.LABEL
    from CSC_PROF_TABLE_COLUMNS_TL B
    where B.TABLE_COLUMN_ID = T.TABLE_COLUMN_ID
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.TABLE_COLUMN_ID,
      T.LANGUAGE
  ) in (select
      SUBT.TABLE_COLUMN_ID,
      SUBT.LANGUAGE
    from CSC_PROF_TABLE_COLUMNS_TL SUBB, CSC_PROF_TABLE_COLUMNS_TL SUBT
    where SUBB.TABLE_COLUMN_ID = SUBT.TABLE_COLUMN_ID
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.LABEL <> SUBT.LABEL
      or (SUBB.LABEL is null and SUBT.LABEL is not null)
      or (SUBB.LABEL is not null and SUBT.LABEL is null)
  ));

Line: 278

  insert into CSC_PROF_TABLE_COLUMNS_TL (
    TABLE_COLUMN_ID,
    LABEL,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN,
    LANGUAGE,
    SOURCE_LANG
  ) select
    B.TABLE_COLUMN_ID,
    B.LABEL,
    B.CREATED_BY,
    B.CREATION_DATE,
    B.LAST_UPDATED_BY,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATE_LOGIN,
    L.LANGUAGE_CODE,
    B.SOURCE_LANG
  from CSC_PROF_TABLE_COLUMNS_TL B, FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and B.LANGUAGE = userenv('LANG')
  and not exists
    (select NULL
    from CSC_PROF_TABLE_COLUMNS_TL T
    where T.TABLE_COLUMN_ID = B.TABLE_COLUMN_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 314

     Update Csc_Prof_Table_Columns_TL set
          label = nvl(P_LABEL,label),
          last_update_date  = sysdate,
	  last_updated_by   = decode(p_OWNER,'SEED',1,0),
	  last_update_login = 0,
	  source_lang       = userenv('LANG')
     Where table_column_id = P_TABLE_COLUMN_ID
     and  userenv('LANG') in (language, source_lang);

Line: 335

   p_last_update_date       IN DATE,
   p_last_updated_by        IN NUMBER,
   p_last_update_login      IN NUMBER
 ) is

 l_object_version_number number := 0;

Line: 345

  Csc_Prof_Table_Columns_Pkg.Update_Row(
   p_TABLE_COLUMN_ID        => P_TABLE_COLUMN_ID,
   p_BLOCK_ID               => P_BLOCK_ID,
   p_TABLE_NAME             => P_TABLE_NAME,
   p_COLUMN_NAME            => P_COLUMN_NAME,
   p_LABEL                  => P_LABEL,
   p_TABLE_ALIAS            => P_ALIAS_NAME,
   p_COLUMN_SEQUENCE        => P_COLUMN_SEQUENCE,
   p_drilldown_column_flag  => P_DRILLDOWN_COLUMN_FLAG,
   p_LAST_UPDATE_DATE       => p_last_update_date,
   p_LAST_UPDATED_BY        => p_last_updated_by,
   p_LAST_UPDATE_LOGIN      => p_last_update_login,
   p_SEEDED_FLAG            => p_SEEDED_FLAG,
   px_OBJECT_VERSION_NUMBER => l_object_version_number);

Line: 369

   Csc_Prof_Table_Columns_Pkg.Delete_Existing_Row(
   	p_BLOCK_ID			=>p_block_id,
   	p_TABLE_NAME      =>p_table_name,
   	p_COLUMN_NAME     =>p_column_name
   	);

Line: 375

   Csc_Prof_Table_Columns_Pkg.Insert_Row(
      px_TABLE_COLUMN_ID      => l_table_column_id,
      p_BLOCK_ID              => P_block_id,
      p_TABLE_NAME            => P_table_name,
      p_COLUMN_NAME           => P_column_name,
      p_LABEL                 => P_label,
      p_TABLE_ALIAS           => P_alias_name,
      p_COLUMN_SEQUENCE       => P_column_sequence,
      p_drilldown_column_flag  =>P_drilldown_column_flag,
      p_LAST_UPDATE_DATE      => p_last_update_date,
      p_LAST_UPDATED_BY       => p_last_updated_by,
      p_CREATION_DATE         => p_last_update_date,
      p_CREATED_BY            => p_last_updated_by,
      p_LAST_UPDATE_LOGIN     => p_last_update_login,
      p_SEEDED_FLAG           => p_SEEDED_FLAG,
      x_OBJECT_VERSION_NUMBER => l_object_version_number);