[Home] [Help]
1109: END;
1110:
1111: FUNCTION get_lowest_s_table(p_fact IN VARCHAR2, p_dim_set IN NUMBER) return VARCHAR2 IS
1112: CURSOR cSTable(p_fact_pattern varchar2, p_s_pattern varchar2) IS
1113: select table_name from bsc_db_Tables_rels
1114: where table_name like p_fact_pattern
1115: and source_table_name not like p_s_pattern;
1116: l_s_tablename VARCHAR2(300);
1117: BEGIn
1882:
1883: function is_b_table_a_source(p_s_table in varchar2, p_b_table in varchar2) return boolean is
1884: l_count number;
1885: begin
1886: select count(1) into l_count from bsc_db_tables_rels
1887: where table_name = p_s_table and source_table_name=p_b_table;
1888: if l_count>0 then
1889: return true;
1890: end if;
1951: -- there can be multiple Base tables feeding this kpi
1952: -- this may be because of different lowest level periodicities or different measures from diff. tables
1953: CURSOR cLowestStable IS
1954: SELECT table_name FROM
1955: BSC_DB_TABLES_RELS rels
1956: WHERE table_name like 'BSC_S%'||p_fact||'_'||p_dim_set||'%'
1957: AND source_table_name not like 'BSC_S%'
1958: AND p_base_table IN (select table_name from bsc_db_tables_rels rels2 connect by table_name=prior source_table_name start with table_name = rels.table_name);
1959:
1954: SELECT table_name FROM
1955: BSC_DB_TABLES_RELS rels
1956: WHERE table_name like 'BSC_S%'||p_fact||'_'||p_dim_set||'%'
1957: AND source_table_name not like 'BSC_S%'
1958: AND p_base_table IN (select table_name from bsc_db_tables_rels rels2 connect by table_name=prior source_table_name start with table_name = rels.table_name);
1959:
1960:
1961: l_lowest_s_table VARCHAR2(100);
1962: Begin
2113: p_dim_set in number,
2114: p_targets in boolean) return dbms_sql.varchar2_table is
2115: CURSOR cBTables(p_prefix VARCHAR2) IS
2116: select distinct rels.table_name
2117: from bsc_db_Tables_rels rels,
2118: bsc_db_tables src
2119: where
2120: rels.source_table_name = src.table_name
2121: and src.table_type=0
2122: and rels.table_name like 'BSC_B%'
2123: connect by rels.table_name=prior rels.source_table_name
2124: start with rels.table_name in -- lowest level S tables
2125: ( SELECT table_name FROM
2126: BSC_DB_TABLES_RELS rels
2127: WHERE table_name like p_prefix||p_fact||'_'||p_dim_set||'%'
2128: AND source_table_name not like 'BSC_S%'
2129: ) ;
2130:
2761: function get_target_per_for_b_table(p_fact in varchar2, p_dim_set in number, p_b_table in varchar2) return dbms_sql.varchar2_table is
2762: cursor cTgtPeriodicities is
2763: select periodicity_id from bsc_db_tables where table_name in
2764: (
2765: select distinct table_name from bsc_db_tables_rels rels
2766: where
2767: rels.table_name like 'BSC_S%'||p_fact||'_'||p_dim_set||'%'
2768: and rels.source_table_name not like 'BSC_S%'||p_fact||'_'||p_dim_set||'%'
2769: connect by prior rels.table_name=rels.source_table_name
2810: --BSC_DBGEN_STD_METADATA.tab_clsLevel
2811: is
2812: --Find Summary Table fed by this B table
2813: cursor cSummary(p_pattern in varchar2) is
2814: select distinct table_name from bsc_db_tables_rels
2815: where instr(table_name, p_pattern)=1
2816: and source_table_name not like p_pattern||'%'
2817: connect by source_table_name = prior table_name
2818: start with source_table_name = p_b_table_name;
2824: (
2825: SELECT table_name child_lvl
2826: , source_Table_name parent_lvl
2827: , LEVEL lvl
2828: FROM bsc_db_tables_rels rels
2829: START WITH source_table_name = p_parent_level
2830: CONNECT BY source_table_name = PRIOR table_name
2831: )
2832: SELECT parent_lvl, child_lvl, lvl
2840: FROM tree
2841: WHERE child_lvl = p_child_level
2842: )
2843: union
2844: select source_table_name,table_name , -1 from bsc_db_Tables_rels
2845: where table_name=p_parent_level
2846: order by lvl ;
2847:
2848: cursor cCols (p_table_name in varchar2, p_col_type1 in varchar2, p_col_type2 in varchar2) is