[Home] [Help]
19:
20:
21: FUNCTION get_measures_for_table(p_table_pattern in varchar2) return BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField IS
22: l_stmt varchar2(1000):='SELECT distinct column_Name, source
23: FROM bsc_db_tables_cols
24: WHERE column_type = :1
25: AND table_name like :2
26: AND column_name not like :3';
27: cv CurTyp;
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
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
98: WHERE table_name = l_table AND UPPER(column_name) = l_column AND column_type = l_column_type;
99:
100: BEGIN
101:
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
126: EXIT WHEN l_counter = KpiTable.keys.last;
127: l_counter := KpiTable.keys.next(l_counter);
128: END LOOP;
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
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';
213: New_clsKeyField bsc_metadata_optimizer_pkg.clsKeyField;
214: New_clsDataField bsc_metadata_optimizer_pkg.clsDataField;
215: CURSOR cTable (pTable IN VARCHAR2) IS
216: SELECT TABLE_NAME, TABLE_TYPE, PERIODICITY_ID, EDW_FLAG, CURRENT_PERIOD, TARGET_FLAG
217: FROM BSC_DB_TABLES
218: WHERE TABLE_NAME = pTable;
219: cTableRow cTable%ROWTYPE;
220:
221: CURSOR cCols (pTable IN VARCHAR2) IS
222: SELECT COLUMN_TYPE, COLUMN_NAME, SOURCE_COLUMN, SOURCE_FORMULA
223: --BSC Autogen
224: , SOURCE
225: , measure_group_id
226: FROM BSC_DB_TABLES_COLS cols, bsc_db_measure_cols_vl dbcols
227: WHERE cols.TABLE_NAME = pTable
228: AND cols.column_name = dbcols.measure_col(+);
229: cColsRow cCols%ROWTYPE;
230:
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;
584: --****************************************************************************
585: -- getMaxTableIndex : MaximoIndiceTablas
586: --
587: -- DESCRIPTION:
588: -- Look for tables whose name start with the given word in BSC_DB_TABLES
589: -- From those tables whose name end with a number, this function return
590: -- the maximun number.
591: -- Example. startsWith = 'T_'
592: -- In BSC_DB_TABLES exists the following tables that start with 'T_':
588: -- Look for tables whose name start with the given word in BSC_DB_TABLES
589: -- From those tables whose name end with a number, this function return
590: -- the maximun number.
591: -- Example. startsWith = 'T_'
592: -- In BSC_DB_TABLES exists the following tables that start with 'T_':
593: -- 'T_1', 'T_A', 'T_2' and 'T_3'. This function return 3
594: --
595: -- PARAMETERS:
596: -- startsWith: Word by which the table name start.
601: l_max NUMBER := 0;
602: cv CurTyp;
603: l_startsWith VARCHAR2(100);
604: cursor cTables(pStart IN VARCHAR2) IS
605: SELECT table_name from bsc_db_tables
606: where table_name like pStart;
607:
608:
609: l_str_len number := 0;
2635:
2636: CURSOR cKeys IS
2637: SELECT column_name
2638: FROM
2639: bsc_db_tables_cols cols
2640: WHERE
2641: cols.table_name = p_table
2642: AND cols.column_type = 'P'
2643: ORDER BY cols.column_name;
2644:
2645: CURSOR cData(p_owner varchar2) IS
2646: SELECT cols.column_name, nvl(cols.source, 'BSC'), nvl(dbcols.measure_group_id, -1)
2647: FROM
2648: bsc_db_tables_cols cols,
2649: all_tab_columns tabcols,
2650: bsc_sys_measures sysm,
2651: bsc_db_measure_cols_vl dbcols
2652: WHERE
2660: ORDER BY cols.column_name;
2661:
2662: CURSOR cZeroCode (p_column in varchar2) IS
2663: select source_column, nvl(calculation_type, 0) from
2664: bsc_db_Tables_cols cols,
2665: bsc_db_calculations calc
2666: where
2667: cols.table_name = calc.table_name (+)
2668: and cols.table_name = p_table
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
2856: l_dim_set number;
2857: BEGIN
2858: BSC_MO_HELPER_PKG.writeTmp('Inside LOAD_B_T_TABLES_FROM_DB, system time is '||
2859: bsc_mo_helper_pkg.get_time, FND_LOG.LEVEL_PROCEDURE, true);
2860: l_stmt := 'select distinct table_name from bsc_db_tables where (table_name like ''BSC_B%'' or table_name like ''BSC_T%'' ) and table_type=1';
2861: OPEN cv FOR l_stmt ;
2862: LOOP
2863: FETCH cv INTO l_table;
2864: EXIT WHEN cv%NOTFOUND;