APPS.BSC_PMD_OPT_DOC_UTIL SQL Statements

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

Line: 32

    UPDATE bsc_db_tables_display
    set DISPLAY_TYPE = 'TABLE'
    where PHYSICAL_TYPE = 'TABLE';

Line: 36

    UPDATE bsc_db_tables_display
    set DISPLAY_TYPE = 'ANALYTICAL_WORKSPACES'
    where PHYSICAL_TYPE = 'VIEW'
    and TABLE_NAME_DISPLAY LIKE 'BSC\_S%' ESCAPE '\'
    and exists (SELECT 1
                from BSC_KPI_PROPERTIES
                where INDICATOR =
                        TO_NUMBER(
                          SUBSTR(TABLE_NAME_DISPLAY,
                                 INSTR(TABLE_NAME_DISPLAY,'_',1,2)+1,
                                 INSTR(TABLE_NAME_DISPLAY,'_',1,3)-INSTR(TABLE_NAME_DISPLAY,'_',1,2)-1))
                and PROPERTY_CODE = 'IMPLEMENTATION_TYPE'
                and PROPERTY_VALUE = 2);

Line: 50

    UPDATE bsc_db_tables_display
    set DISPLAY_TYPE = 'VIEW'
    where PHYSICAL_TYPE = 'VIEW'
    and (TABLE_NAME_DISPLAY NOT LIKE 'BSC\_S%' ESCAPE '\'
    or not exists (SELECT 1
                   from BSC_KPI_PROPERTIES
                   where INDICATOR =
                           TO_NUMBER(
                             SUBSTR(TABLE_NAME_DISPLAY,
                                    INSTR(TABLE_NAME_DISPLAY,'_',1,2)+1,
                                    INSTR(TABLE_NAME_DISPLAY,'_',1,3)-INSTR(TABLE_NAME_DISPLAY,'_',1,2)-1))
                   and PROPERTY_CODE = 'IMPLEMENTATION_TYPE'
                   and PROPERTY_VALUE = 2));

Line: 64

    UPDATE bsc_db_tables_display
    set DISPLAY_TYPE = 'MATERIALIZED_VIEW'
    where PHYSICAL_TYPE = 'MATERIALIZED VIEW';

Line: 88

select
  TABLE_NAME TABLE_DISPLAY,
  TABLE_TYPE,
  null DISPLAY_TYPE,
  ''TABLE'' PHYSICAL_TYPE
from bsc_db_tables
--where TABLE_TYPE <> 2 -- commented by Arun as we need to display D tables also in the new UI

union /* for D tables*/  --bug 3918860
select
	TABLE_NAME TABLE_DISPLAY,
	2 TABLE_TYPE,
	null DISPLAY_TYPE,
  	''TABLE'' PHYSICAL_TYPE
from
	bsc_db_tables_rels
where
	TABLE_NAME like ''BSC_D_%''
';

Line: 108

    l_stmt:='insert into ' || G_MASTER_DISPLAY_TABLE || l_stmt ;

Line: 117

select *
from bsc_db_tables_rels
';

Line: 122

    l_stmt:='insert into ' || G_REL_DISPLAY_TABLE || l_stmt ;

Line: 130

		select *
		from bsc_db_tables_rels
		where table_name like ''BSC_D%''
		';

Line: 136

    l_stmt:='insert into ' || G_REL_DISPLAY_TABLE || l_stmt ;

Line: 230

        select * from bsc_db_tables_rels
        where (SOURCE_TABLE_NAME like ''BSC_B%''
        and TABLE_NAME like ''BSC_T%'') OR
        SOURCE_TABLE_NAME like ''BSC_T%''
    ';

Line: 247

select distinct BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_STABLE(t1.table_name) table_name_display, k.source_table_name source_table_name_display, t1.relation_type
from '||G_BSC_SCHEMA||'.BSC_T_COLLAPSE_TEMP t1, '||G_BSC_SCHEMA||'.BSC_T_COLLAPSE_TEMP k
where t1.source_table_name in (
        select t2.table_name
        from '||G_BSC_SCHEMA||'.BSC_T_COLLAPSE_TEMP t2
        start with t2.source_table_name = k.source_table_name
        connect by prior t2.table_name = t2.source_table_name
        and t2.table_name like ''BSC_T%''
)
and t1.TABLE_NAME like ''BSC_S_%''
and k.source_table_name like ''BSC_B%''
and BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_STABLE(t1.table_name) is not null
union
select distinct BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_SBTABLE(t1.table_name) table_name_display, k.source_table_name source_table_name_display, t1.relation_type
from '||G_BSC_SCHEMA||'.BSC_T_COLLAPSE_TEMP t1, '||G_BSC_SCHEMA||'.BSC_T_COLLAPSE_TEMP k
where t1.source_table_name in (
        select t2.table_name
        from '||G_BSC_SCHEMA||'.BSC_T_COLLAPSE_TEMP t2
        start with t2.source_table_name = k.source_table_name
        connect by prior t2.table_name = t2.source_table_name
        and t2.table_name like ''BSC_T%''
)
and t1.TABLE_NAME like ''BSC_SB_%''
and k.source_table_name like ''BSC_B%''
    ';

Line: 273

    l_stmt:='insert into ' || G_REL_DISPLAY_TABLE || l_stmt ;

Line: 281

  select TABLE_NAME TABLE_NAME_DISPLAY, SOURCE_TABLE_NAME SOURCE_TABLE_NAME_DISPLAY, relation_type
  from bsc_db_tables_rels
  where (SOURCE_TABLE_NAME like ''BSC_I%''
  or SOURCE_TABLE_NAME like ''BSC_B%'' )
  and TABLE_NAME not like ''BSC_S%''
  and TABLE_NAME not like ''BSC_T%''
  and relation_type = 0
  union
  select distinct BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_STABLE(TABLE_NAME) TABLE_NAME_DISPLAY
                  , SOURCE_TABLE_NAME SOURCE_TABLE_NAME_DISPLAY, relation_type from bsc_db_tables_rels
  where
      SOURCE_TABLE_NAME like ''BSC_B%''
  and TABLE_NAME like ''BSC_S_%''
  and TABLE_NAME not like ''BSC_SB_%''
  and BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_STABLE(TABLE_NAME) is not null
  and relation_type = 0
  union
  select distinct BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_SBTABLE(TABLE_NAME) TABLE_NAME_DISPLAY
                  , SOURCE_TABLE_NAME SOURCE_TABLE_NAME_DISPLAY, relation_type from bsc_db_tables_rels
  where
      SOURCE_TABLE_NAME like ''BSC_B%''
  and TABLE_NAME like ''BSC_SB_%''
  and BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_SBTABLE(TABLE_NAME) is not null
  and relation_type = 0
  ';

Line: 307

    l_stmt:='insert into ' || G_REL_DISPLAY_TABLE || l_stmt ;

Line: 315

select distinct
       BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_SBTABLE(TABLE_NAME) TABLE_NAME_DISPALY,
       BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_SBTABLE(SOURCE_TABLE_NAME) SOURCE_TABLE_NAME_DISPALY,
       RELATION_TYPE
from bsc_db_tables_rels
where source_table_name like ''BSC_SB_%''
and table_name like ''BSC_SB_%''
and BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_SBTABLE(TABLE_NAME) <>
    BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_SBTABLE(SOURCE_TABLE_NAME)
union
select distinct
       BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_STABLE(TABLE_NAME) TABLE_NAME_DISPALY,
       BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_SBTABLE(SOURCE_TABLE_NAME) SOURCE_TABLE_NAME_DISPALY,
       RELATION_TYPE
from bsc_db_tables_rels
where source_table_name like ''BSC_SB_%''
and table_name like ''BSC_S_%''
and BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_STABLE(TABLE_NAME) is not null
';

Line: 335

    l_stmt:='insert into ' || G_REL_DISPLAY_TABLE || l_stmt ;

Line: 343

select distinct BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_STABLE(table_name) table_name_display,
       BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_STABLE(source_table_name) source_table_name_display,
       relation_type
from bsc_db_tables_rels
where
    SOURCE_TABLE_NAME like ''BSC_S_%''
and TABLE_NAME like ''BSC_S_%''
and BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_STABLE(table_name) <>
    BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_STABLE(source_table_name)
and relation_type = 0
';

Line: 355

    l_stmt:='insert into ' || G_REL_DISPLAY_TABLE || l_stmt ;

Line: 363

select distinct BSC_PMD_OPT_DOC_UTIL.GET_ZMV_BY_STABLE(table_name) table_name_display,
       BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_STABLE(table_name) source_table_name_display,
       null relation_type
from bsc_db_tables
where
    TABLE_NAME like ''BSC_S_%''
and BSC_PMD_OPT_DOC_UTIL.GET_ZMV_BY_STABLE(table_name) is not null
';

Line: 372

    l_stmt:='insert into ' || G_REL_DISPLAY_TABLE || l_stmt ;

Line: 380

select /*I and B tables*/
  TABLE_NAME TABLE_DISPLAY,
  TABLE_TYPE,
  null DISPLAY_TYPE,
  ''TABLE'' PHYSICAL_TYPE
from bsc_db_tables
where ( TABLE_NAME like ''BSC_I_%'' OR TABLE_NAME like ''BSC_B_%'' OR TABLE_NAME like ''BSC_DI_%'' )
union /* for _MV*/
select
  BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_STABLE(TABLE_NAME) TABLE_DISPLAY,
  TABLE_TYPE,
  null DISPLAY_TYPE,
  o.object_type PHYSICAL_TYPE
from bsc_db_tables, all_objects o
where o.owner in (:1, :2)
and o.object_type in (''VIEW'', ''MATERIALIZED VIEW'')
and TABLE_TYPE = 1
and TABLE_NAME like ''BSC_S_%''
and BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_STABLE(TABLE_NAME) is not null
and o.object_name = BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_STABLE(TABLE_NAME)
union /* for _ZMV*/
select
  BSC_PMD_OPT_DOC_UTIL.GET_ZMV_BY_STABLE(TABLE_NAME) TABLE_DISPLAY,
  TABLE_TYPE,
  null DISPLAY_TYPE,
  o.object_type PHYSICAL_TYPE
from bsc_db_tables, all_objects o
where TABLE_TYPE =1
and TABLE_NAME like ''BSC_S_%''
and BSC_PMD_OPT_DOC_UTIL.GET_ZMV_BY_STABLE(TABLE_NAME) is not null
and o.owner in (:3, :4)
and o.object_type in (''VIEW'', ''MATERIALIZED VIEW'')
and o.object_name = BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_STABLE(TABLE_NAME)
union /* for SB tables*/
select
  BSC_PMD_OPT_DOC_UTIL.GET_MV_BY_SBTABLE(TABLE_NAME) TABLE_DISPLAY,
  TABLE_TYPE,
  null DISPLAY_TYPE,
  ''TABLE'' PHYSICAL_TYPE
from bsc_db_tables
where TABLE_TYPE = 1
and TABLE_NAME like ''BSC_SB_%''
union /* for D tables*/  --bug 3918860
select
	TABLE_NAME TABLE_DISPLAY,
	2 TABLE_TYPE,
	null DISPLAY_TYPE,
  	''TABLE'' PHYSICAL_TYPE
from
	bsc_db_tables_rels
where
	TABLE_NAME like ''BSC_D_%''
';

Line: 434

    l_stmt:='insert into ' || G_MASTER_DISPLAY_TABLE || l_stmt ;

Line: 443

    SELECT distinct TABLE_NAME,MV_NAME,decode(substr(MV_NAME,-3),'ZMV','ZMV','MV') mv_type FROM BSC_KPI_DATA_TABLES where MV_NAME is not null;

Line: 495

    UPDATE BSC_DB_TABLES_DISPLAY
    SET TABLE_NAME_DISPLAY = P_NEW
    WHERE TABLE_NAME_DISPLAY = P_OLD;

Line: 501

    UPDATE BSC_DB_TABLES_RELS_DISPLAY
    SET SOURCE_TABLE_NAME_DISPLAY = P_NEW
    WHERE SOURCE_TABLE_NAME_DISPLAY = P_OLD;