89: cv CurTyp;
90: KpiTableName VARCHAR2(100);
91: l_error VARCHAR2(1000);
92: CURSOR cTable(l_src IN VARCHAR2, l_tbl IN VARCHAR2) IS
93: SELECT table_name FROM bsc_db_tables_rels
94: WHERE source_table_name = l_src AND table_name LIKE l_tbl;
95:
96: CURSOR cColumns(l_table IN VARCHAR2, l_column IN VARCHAR2, l_column_type IN VARCHAR2) IS
97: SELECT source_column FROM bsc_db_tables_cols
106: If BSC_METADATA_OPTIMIZER_PKG.gIndicators(l_index).OptimizationMode = 0 Then
107: --The indicator is pre-calculated.
108: TableName := 'BSC_S_' || KpiTable.Indicator || '_' || KpiTable.Configuration || '%';
109: If KpiTable.keys.Count = 0 Then
110: l_stmt := 'SELECT DISTINCT r.source_table_name FROM bsc_db_tables_rels r, bsc_db_tables t
111: WHERE r.table_name LIKE :1 AND
112: NOT r.source_table_name LIKE :2 AND
113: r.table_name = t.table_name AND
114: t.periodicity_id = :3 AND
129: l_stmt := 'SELECT DISTINCT table_name AS SOURCE_TABLE_NAME
130: FROM bsc_db_tables_cols
131: WHERE table_name IN (
132: SELECT r.source_table_name
133: FROM bsc_db_tables_rels r, bsc_db_tables t
134: WHERE r.table_name LIKE :1 AND
135: NOT r.source_table_name LIKE :2 AND
136: r.table_name = t.table_name AND
137: t.periodicity_id = :3 AND
148: Else
149: TableName := 'BSC_S_' || KpiTable.Indicator || '_' || KpiTable.Configuration || '%';
150: End If;
151: l_stmt := 'SELECT DISTINCT r.source_table_name
152: FROM bsc_db_tables_rels r, bsc_db_tables t
153: WHERE r.table_name LIKE :1 AND
154: NOT r.source_table_name LIKE :2 AND
155: r.table_name = t.table_name AND
156: t.periodicity_id = :3 AND r.relation_type = 0';
229: cColsRow cCols%ROWTYPE;
230:
231: CURSOR cTableRels0(pTable IN VARCHAR2) IS
232: SELECT SOURCE_TABLE_NAME
233: FROM BSC_DB_TABLES_RELS
234: WHERE TABLE_NAME = pTable
235: AND RELATION_TYPE = 0
236: ORDER BY SOURCE_TABLE_NAME;
237: cTableRels0Row cTableRels0%ROWTYPE;
237: cTableRels0Row cTableRels0%ROWTYPE;
238:
239: CURSOR cTableRels1(pTable IN VARCHAR2) IS
240: SELECT SOURCE_TABLE_NAME
241: FROM BSC_DB_TABLES_RELS
242: WHERE TABLE_NAME = pTable
243: AND RELATION_TYPE = 1
244: ORDER BY SOURCE_TABLE_NAME;
245: cTableRels1Row cTableRels1%ROWTYPE;
2671: and cols.column_name = p_column;
2672:
2673: CURSOR cOriginTables IS
2674: SELECT source_table_name FROM
2675: BSC_DB_TABLES_RELS
2676: WHERE table_name=p_table;
2677:
2678: l_stmt varchar2(1000);
2679: l_column VARCHAR2(100);
2824: select substr(table_name, instr(table_name, ''_'', 1, 2)+1,
2825: instr(table_name, ''_'', 1, 3)-instr(table_name, ''_'', 1, 2)-1) indicator,
2826: substr(table_name, instr(table_name, ''_'', 1, 3)+1,
2827: instr(table_name, ''_'', 1, 4)-instr(table_name, ''_'', 1, 3)-1) dim_set_id
2828: from bsc_db_tables_rels
2829: where table_name like ''BSC_S%''
2830: connect by prior table_name = source_table_name
2831: start with source_table_name = :1
2832: ) tmp