eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BSC_DIMENSION_EDIT SQL Statements

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

Line: 15


 | 07-Jan-04 SMULYE    Bug 3343979  Fixed API Delete_Codes_CascadeMN to      |
 |  delete  only from base tables , not system tables.			     |
 |									     |
 | 14-Jan-04 MREZA	Bug 3363584 Fixed API checkUsercodeChange to	     |
 |			increase width of l_old_user_code.		     |
 | 16-NOV-2006 ankgoel  Color By KPI enh#5244136                             |
 |									     |
 +==========================================================================+*/

h_installed_languages BSC_UPDATE_UTIL.t_array_of_varchar2;

Line: 28


 SELECT DISTINCT L.DIM_LEVEL_ID, D.LEVEL_TABLE_NAME
    FROM BSC_SYS_COM_DIM_LEVELS L, BSC_SYS_DIM_LEVELS_B D
    WHERE L.DIM_LEVEL_ID = D.DIM_LEVEL_ID;

Line: 33


     SELECT T.LEVEL_TABLE_NAME AS CHILDTABLE , P.level_pk_col
     FROM BSC_SYS_DIM_LEVELS_B T, BSC_SYS_DIM_LEVEL_RELS R, BSC_SYS_DIM_LEVELS_B P
   WHERE T.DIM_LEVEL_ID = R.DIM_LEVEL_ID
     and R.PARENT_DIM_LEVEL_ID =P.dim_level_id
        AND R.RELATION_TYPE = 1
        and P.level_table_name=l_table_name;

Line: 55


         l_stmt:='DELETE FROM BSC_USER_LIST_ACCESS '||
                ' WHERE (RESPONSIBILITY_ID, TAB_ID) IN '||
          '( SELECT LA.RESPONSIBILITY_ID, LA.TAB_ID '||
          '  FROM BSC_SYS_COM_DIM_LEVELS L, BSC_USER_LIST_ACCESS LA '||
          '  WHERE L.TAB_ID = LA.TAB_ID '||
          ' AND L.DIM_LEVEL_INDEX = LA.DIM_LEVEL_INDEX '||
          ' AND L.DIM_LEVEL_ID = :1 '||
          ' AND LA.DIM_LEVEL_VALUE <> 0 '||
          ' AND LA.DIM_LEVEL_VALUE NOT IN ( '||
          ' SELECT CODE FROM '||L_TABLE_NAME||'))';

Line: 71


    h_num_installed_languages := BSC_UPDATE_UTIL.Get_Installed_Languages(h_installed_languages);

Line: 78


procedure deleteNormalRow(   l_dim_table IN VARCHAR2,
        l_deleted_code IN NUMBER) is
deleted_code  BSC_UPDATE_UTIL.t_array_of_number;

Line: 83


deleted_code(1):=l_deleted_code;

Line: 84


 temp:=BSC_UPDATE_DIM.Delete_Codes_Cascade(
        l_dim_table,
        deleted_code, 1);

Line: 91


procedure deleteMNRow(l_dim_table IN VARCHAR2,
                      l_key_column1 IN VARCHAR2,
                      l_key_column2 IN VARCHAR2,
                      l_rowid IN VARCHAR2)   is
    TYPE CURSORTYPE IS REF CURSOR;

Line: 102


    l_stmt := 'SELECT '||l_key_column1||', '||l_key_column2||' FROM '||l_dim_table||' WHERE rowid='''||l_rowid||'''';

Line: 106


    result := Delete_Codes_CascadeMN(
                l_dim_table,
                l_key_column1,
                l_key_column2,
                l_code1 ,
                l_code2
            );

Line: 117


                        x_source => 'BSC_UPDATE_DIM.deleteMNRow');

Line: 118


  end deleteMNRow;

Line: 122


        UPDATE BSC_KPIS_B K
        SET PROTOTYPE_FLAG = 6,
            LAST_UPDATED_BY = BSC_APPS.fnd_global_user_id,
            LAST_UPDATE_DATE = SYSDATE
        WHERE INDICATOR IN (SELECT D.INDICATOR
                            FROM BSC_KPI_DIM_LEVELS_B D
                            WHERE K.INDICATOR = D.INDICATOR AND
                                  (UPPER(D.LEVEL_TABLE_NAME) = UPPER(l_dim_table) OR
                                   UPPER(D.TABLE_RELATION) = UPPER(l_dim_table))) AND
              PROTOTYPE_FLAG in (0, 6, 7);

Line: 134


        UPDATE bsc_kpi_analysis_measures_b k
	  SET prototype_flag = 7
          WHERE indicator IN (SELECT D.INDICATOR
                              FROM BSC_KPI_DIM_LEVELS_B D
                              WHERE K.INDICATOR = D.INDICATOR AND
                                    (UPPER(D.LEVEL_TABLE_NAME) = UPPER(l_dim_table) OR
                                     UPPER(D.TABLE_RELATION) = UPPER(l_dim_table)));

Line: 156


     updateNormalRowNW(l_dim_table,l_fk,l_fk_user,l_code_number,
       l_user_code, l_name2, l_fkcode_number, l_fkusercode, l_message);

Line: 159


     insertNormalRowNW(l_dim_table,l_fk,l_fk_user,
       l_user_code, l_name2, l_fkcode_number, l_fkusercode, l_message);

Line: 172


     updateNormalRowNO(l_dim_table,l_code_number,l_user_code,l_name2,l_message);

Line: 174


     insertNormalRowNO(l_dim_table,l_user_code,l_name2, l_message);

Line: 189


   l_stmt:='update '||l_dim_table ||' set '||
    l_key_column1 ||'=:1, '||
    l_key_column2 ||'=:2  ' ||
    ' where rowid=:3 ';

Line: 200


     l_stmt:='insert into '||l_dim_table ||'( '||
      l_key_column1 ||','|| l_key_column2 ||')'||
       'values(:1, :2)';

Line: 210


procedure updateNormalRowNO(l_dim_table in varchar2, l_code in number,
    l_user_code in varchar2, l_name in varchar2,l_message out nocopy varchar2) is
l_stmt varchar2(3000);

Line: 221


      l_stmt:=' update '||l_dim_table||' set '||
              ' user_code=:1 where code=:2 ';

Line: 228


      l_stmt:=' update '||l_dim_table||' set '||
              ' name=:1, source_lang=:2 where code=:3 and (language=:4 or source_lang=:5) ';

Line: 236


end updateNormalRowNO;

Line: 238


procedure insertNormalRowNO(l_dim_table in varchar2,
    l_user_code in varchar2, l_name in varchar2, l_message out  nocopy varchar2)is
l_stmt varchar2(3000);

Line: 249


      l_code:=BSC_UPDATE_DIM.get_new_code(l_dim_table);

Line: 253


	    h_num_installed_languages := BSC_UPDATE_UTIL.Get_Installed_Languages(h_installed_languages);

Line: 261


       l_stmt := 'INSERT INTO '||l_dim_table||' ('||
                         ' code, user_code, name, language, source_lang)'||
                         ' values (:1,:2,:3,:4,:5)';

Line: 269


end insertNormalRowNO;

Line: 271


procedure updateNormalRowNW(l_dim_table in varchar2, l_fk in varchar2,
l_fk_user in varchar2, l_code in number, l_user_code in varchar2,
l_name in varchar2, l_fkcode in number, l_fkusercode in varchar2,
l_message out nocopy varchar2)is
TYPE curcode IS REF CURSOR;

Line: 284


      l_stmt:='select code from '||l_dim_table ||' where (user_code=:1 or name=:2) and ('||l_fk||' is null or '||l_fk_user||' is null) and code!=:3';

Line: 289


         deleteNormalRow(l_dim_table, l_code_number);

Line: 297


      l_stmt:=' update '||l_dim_table||' set '||
        ' user_code=:1, '||l_fk||'=:2, '||l_fk_user||'=:3  where code=:4 ';

Line: 304


      l_stmt:=' update '||l_dim_table||' set '||
        ' name=:1, source_lang=:2 where code=:3 and (language=:4 or source_lang=:5) ';

Line: 312


end updateNormalRowNW;

Line: 314


procedure insertNormalRowNW(l_dim_table in varchar2, l_fk in varchar2,
l_fk_user in varchar2, l_user_code in varchar2,
l_name in varchar2, l_fkcode in number, l_fkusercode in varchar2,
l_message out  nocopy varchar2) is
TYPE curcode IS REF CURSOR;

Line: 326


      l_stmt:='select code from '||l_dim_table ||' where (user_code=:1 or name=:2) and ('||l_fk||' is null or '||l_fk_user||' is null)';

Line: 331


         updateNormalRowNW(l_dim_table, l_fk, l_fk_user, l_code, l_user_code, l_name, l_fkcode, l_fkusercode, l_message);

Line: 337


      l_code:=BSC_UPDATE_DIM.get_new_code(l_dim_table);

Line: 341


	    h_num_installed_languages := BSC_UPDATE_UTIL.Get_Installed_Languages(h_installed_languages);

Line: 348


       l_stmt := 'INSERT INTO '||l_dim_table||' ('||
                         ' code, user_code, name, language, source_lang,'||
            l_fk||','||l_fk_user||')'||
                         ' values (:1,:2,:3,:4,:5,:6,:7)';

Line: 357


end insertNormalRowNW;

Line: 366


  l_stmt:='select count(*) from '||l_dim_table ||' where user_code=:1';

Line: 374


  l_stmt:='select count(*) from '||l_dim_table ||' where name=:1';

Line: 392


  l_stmt:='select count(*) from '||l_dim_table ||' where user_code=:1 and code!=:2';

Line: 400


  l_stmt:='select count(*) from '||l_dim_table ||' where name=:1 and code!=:2';

Line: 419


  l_stmt:='select count(*) from '||l_dim_table ||' where '||l_key_column1
   ||'=:1 and '||l_key_column2||' =:2';

Line: 439


  l_stmt:='select count(*) from '||l_dim_table ||' where '||l_key_column1
   ||'=:1 and '||l_key_column2||' =:2 and rowid!=:3 ';

Line: 459


 /*l_stmt:='select instr('||''''||l_name||''''||','','') from dual';

Line: 468


  /*l_stmt:='select instr('||''''||l_name2||''''||','','') from dual';

Line: 479


FUNCTION Delete_Codes_CascadeMN(
    x_dim_table IN VARCHAR2,
    x_key_column1 IN VARCHAR2,
    x_key_column2 IN VARCHAR2,
    x_deleted_codes1 IN number,
    x_deleted_codes2 IN number
    ) RETURN BOOLEAN IS

    h_condition VARCHAR2(32700);

Line: 494


     c_base_tables_sql VARCHAR2(2000) := 'SELECT DISTINCT bt.table_name '||
                                        ' FROM (SELECT DISTINCT table_name'||
					' FROM bsc_db_tables_rels'||
					' WHERE source_table_name IN '||
					' ( SELECT table_name  FROM bsc_db_tables '||
					' WHERE table_type = 0 ) )bt,'||
					' bsc_db_tables_cols c '||
					' WHERE bt.table_name = c.table_name AND '||
					' (UPPER(c.column_name) = UPPER(:1)  OR '||
					' UPPER(c.column_name) = UPPER(:2)) AND '||
					' c.column_type = :3 '||
					' GROUP BY bt.table_name '||
					' HAVING COUNT(*) = 2 ';

Line: 514


    h_condition := x_key_column1||'='||x_deleted_codes1||' and '
    ||x_key_column2||'='||x_deleted_codes2;

Line: 521


     	 h_sql := 'DELETE FROM '||h_base_table||
                 ' WHERE '||h_condition;

Line: 523


               BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

Line: 530


    h_sql := 'DELETE FROM '||x_dim_table||
             ' WHERE '||h_condition;

Line: 532


    BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

Line: 539


                        x_source => 'BSC_UPDATE_DIM.Delete_Codes_CascadeMN');

Line: 542


END Delete_Codes_CascadeMN;

Line: 556


procedure insertNormalRowNWM(l_dim_table in varchar2,
l_user_code in varchar2, l_name in varchar2, l_parentcount in number,
l_fklist in BSC_EDIT_VLIST,
l_fkvaluelist in BSC_EDIT_VLIST, l_fkuservaluelist in  BSC_EDIT_VLIST,
l_message out  nocopy varchar2) is
TYPE curcode IS REF CURSOR;

Line: 579


      l_stmt := 'select code from '||l_dim_table ||' where (user_code=:1 or name=:2) and '||l_t3;

Line: 584


         updateNormalRowNWM(l_dim_table, l_code, l_user_code, l_name, l_parentcount, l_fklist, l_fkvaluelist, l_fkuservaluelist, l_message);

Line: 590


      l_code:=BSC_UPDATE_DIM.get_new_code(l_dim_table);

Line: 599


	    h_num_installed_languages := BSC_UPDATE_UTIL.Get_Installed_Languages(h_installed_languages);

Line: 608


      l_stmt := 'INSERT INTO '||l_dim_table||' ('||
                         ' code, user_code, name, language, source_lang '||
            l_t1||
                         ' ) values (:1,:2,:3,:4,:5 ' ||l_t2 ||' )';

Line: 617


end insertNormalRowNWM;

Line: 619


procedure updateNormalRowNWM(l_dim_table in varchar2, l_code in number,
l_user_code in varchar2, l_name in varchar2,l_parentcount in number,
l_fklist in BSC_EDIT_VLIST,
l_fkvaluelist in BSC_EDIT_VLIST, l_fkuservaluelist in  BSC_EDIT_VLIST,
l_message out nocopy varchar2
)is
TYPE curcode IS REF CURSOR;

Line: 643


      l_stmt:='select code from '||l_dim_table||' where (user_code=:1 or name=:2) and code!=:3 and '||l_t2;

Line: 648


         deleteNormalRow(l_dim_table, l_code_number);

Line: 660


      l_stmt:=' update '||l_dim_table||' set '||
        ' user_code=:1 '||l_t1||'  where code=:2 ';

Line: 667


      l_stmt:=' update '||l_dim_table||' set '||
        ' name=:1, source_lang=:2 where code=:3 and (language=:4 or source_lang=:5) ';

Line: 676


end updateNormalRowNWM;

Line: 690


     updateNormalRowNWM(l_dim_table,l_code_number,
      l_user_code,l_name2,l_parentcount,l_fklist,
    l_fkvaluelist,l_fkuservaluelist,l_message );

Line: 694


     insertNormalRowNWM(l_dim_table,
      l_user_code,l_name2,l_parentcount,l_fklist,
    l_fkvaluelist,l_fkuservaluelist,  l_message );

Line: 707


 l_stmt:='select user_code from '||l_dim_table ||' where code=:1';

Line: 724


 l_stmt:='SELECT count(*) FROM BSC_SYS_DIM_LEVELS_B T, BSC_SYS_DIM_LEVEL_RELS R, BSC_SYS_DIM_LEVELS_B P WHERE T.DIM_LEVEL_ID = R.DIM_LEVEL_ID and R.PARENT_DIM_LEVEL_ID =P.dim_level_id AND R.RELATION_TYPE = 1  and P.level_table_name=:1';

Line: 745


     l_stmt:='update '||l_childtable ||' set '||l_pk||'_USR =:1 '||
           ' where '||l_pk||' = :2';

Line: 757


 l_sql:='select ''1'' from '||p_view_name||' where rownum=1';