[Home] [Help]
PACKAGE BODY: APPS.BSC_MO_DB_PKG
Source
1 Package Body BSC_MO_DB_PKG AS
2 /* $Header: BSCMODBB.pls 120.22.12000000.2 2007/04/24 05:29:11 amitgupt ship $ */
3
4
5 g_conc_short_name VARCHAR2(100):='BSC_METADATA_OPTIMIZER_SPAWNED';
6 g_status_message VARCHAR2(100);
7
8
9 g_buckets NUMBER := 1;
10 g_bucket_id NUMBER := 1;
11 g_ddl_table_name VARCHAR2(100) := 'BSC_TMP_DDL_TABLE'||bsc_metadata_optimizer_pkg.g_session_id;
12 g_parallelize boolean := false;
13 g_parallel_threshold number := 50000000; -- never parallelize in 5.2
14 g_tables_processed number := 0;
15 g_bucket_size number := 100000;
16 g_sleep_time NUMBER;
17
18 g_max_buckets NUMBER;
19 g_ddl_bucket_id DBMS_SQL.NUMBER_TABLE;
20 g_ddl_object DBMS_SQL.VARCHAR2_TABLE;
21 g_ddl_object_ddl DBMS_SQL.VARCHAR2_TABLE;
22 g_ddl_object_type DBMS_SQL.VARCHAR2_TABLE;
23
24
25 TYPE rec_update_tables is record(
26 table_name varchar2(100),
27 property varchar2(100));
28 TYPE tab_update_tables is TABLE of rec_update_tables INDEX BY PLS_INTEGER;
29 g_update_tables tab_update_tables;
30
31 PROCEDURE CreateDDLTable IS
32 l_stmt VARCHAR2 (1000) := 'CREATE TABLE '||g_ddl_table_name||' (bucket_id NUMBER, object_name VARCHAR2(100), '||
33 ' object_ddl varchar2(4000), object_type varchar2(100), status VARCHAR2(10), error_message varchar2(4000))';
34
35 l_index VARCHAR2(1000) := 'CREATE UNIQUE INDEX '||g_ddl_table_name||'_U1 ON '||g_ddl_table_name||' (bucket_id, object_name)';
36 l_error VARCHAR2(1000);
37 BEGIN
38 IF bsc_mo_helper_pkg.tableexists(g_ddl_table_name)=false THEN
39 bsc_mo_helper_pkg.do_ddl(l_stmt, ad_ddl.create_table, g_ddl_table_name);
40 bsc_mo_helper_pkg.do_ddl(l_index, ad_ddl.create_index, g_ddl_table_name||'_U1');
41 ELSE
42 execute immediate 'truncate table '||BSC_METADATA_OPTIMIZER_PKG.gBSCSchema||'.'||g_ddl_table_name;
43 END IF;
44
45 EXCEPTION WHEN OTHERS THEN
46 l_error := sqlerrm;
47 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in CreateDDLTable:'||l_error);
48 raise;
49 END;
50
51 PROCEDURE InsertDDL(pObjectName IN VARCHAR2,
52 pObjectDDL IN VARCHAR2,
53 pObjectType IN VARCHAR2) IS
54 l_stmt VARCHAR2 (1000) := 'INSERT INTO '||g_ddl_table_name ||
55 ' (bucket_id, object_name, object_ddl, object_type) VALUES (:1, :2, :3, :4)';
56 l_error varchar2(1000);
57 BEGIN
58 IF (pObjectTYPE = 'TABLE') THEN
59 IF (mod(g_tables_processed, g_bucket_size) = 0 AND g_tables_processed <> 0 ) THEN
60 g_bucket_id := g_bucket_id + 1;
61 g_buckets := g_bucket_id;
62 bsc_mo_helper_pkg.writetmp('Incrementing bucket id to '||g_bucket_id||', # Tables processed='||g_tables_processed||', g_bucket_size='||g_bucket_size);
63 END IF;
64 END IF;
65 g_ddl_bucket_id(g_tables_processed) := g_bucket_id ;
66 g_ddl_object(g_tables_processed) := pObjectName;
67 g_ddl_object_ddl(g_tables_processed) := pObjectDDL;
68 g_ddl_object_type(g_tables_processed) := pObjectType;
69 g_tables_processed := g_tables_processed + 1;
70
71 EXCEPTION WHEN OTHERS THEN
72 l_error := sqlerrm;
73 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in InsertDDL:'||l_error);
74 raise;
75 END;
76
77 --****************************************************************************
78 -- sort_data_columns
79 --
80 -- DESCRIPTION:
81 -- Given a list of measures, sort them and return it
82 --
83 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
84 --****************************************************************************
85 PROCEDURE sort_data_columns(p_data IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField) IS
86 wasAdded boolean ;
87 l_data_cols BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
88 l_col BSC_METADATA_OPTIMIZER_PKG.clsDataField;
89 CURSOR cOrdered IS
90 SELECT value_v, value_n order_index FROM BSC_TMP_BIG_IN_COND
91 WHERE variable_id=10
92 AND session_id=USERENV('SESSIONID')
93 ORDER BY value_v;
94 l_dummy VARCHAR2(100);
95 l_ordered_index number;
96 l_ins varchar2(1000);
97 BEGIN
98 IF (p_data.count=0) THEN
99 return;
100 END IF;
101 DELETE BSC_TMP_BIG_IN_COND WHERE variable_id = 10 AND session_id=USERENV('SESSIONID');
102 l_ins := 'INSERT INTO BSC_TMP_BIG_IN_COND(variable_id, value_v, value_n, session_id) values (:1, :2, :3, :4)';
103 for i in p_data.first..p_data.last loop
104 execute immediate l_ins USING 10, upper(p_data(i).fieldName), i, USERENV('SESSIONID');
105 end loop;
106 for i in cOrdered loop
107 l_data_cols(l_data_cols.count) := p_data(i.order_index);
108 end loop;
109 p_data := l_data_cols;
110 END;
111 --****************************************************************************
112 -- GetDimKeyDataType
113 --
114 -- DESCRIPTION:
115 -- Get the data type of the given key columns
116 --
117 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
118 --****************************************************************************
119 Function GetDimKeyDataType(dimTable IN VARCHAR2, columnName IN VARCHAR2, dataLength OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
120 l_stmt VARCHAR2(1000);
121 CURSOR cUserTabColumns IS
122 SELECT DATA_TYPE, DATA_LENGTH
123 FROM USER_TAB_COLUMNS
124 WHERE TABLE_NAME = dimTable
125 AND COLUMN_NAME = columnName;
126
127 CURSOR cAllTabColumns IS
128 SELECT DATA_TYPE, DATA_LENGTH
129 FROM ALL_TAB_COLUMNS
130 WHERE TABLE_NAME = dimTable
131 AND COLUMN_NAME = columnName
132 AND OWNER = BSC_METADATA_OPTIMIZER_PKG.gBSCSchema;
133
134 l_temp1 NUMBER;
135 l_type VARCHAR2(30) := null;
136 l_length NUMBER;
137
138 BEGIN
139 l_temp1 := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, dimTable);
140 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
141 bsc_mo_helper_pkg.writetmp('l_temp1 ='||l_temp1);
142 bsc_mo_helper_pkg.writetmp('dimTable = '||dimTable||', column = '||columnName||', source ='||
143 BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).Source);
144 END IF;
145 If BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).Source = 'PMF' Then
146 --The dimension table is a View
147 OPEN cUserTabColumns;
148 FETCH cUserTabColumns INTO l_type, l_length;
149 CLOSE cUserTabColumns;
150 Else
151 OPEN cAllTabColumns;
152 FETCH cAllTabColumns INTO l_type, l_length;
153 CLOSE cAllTabColumns;
154 End If;
155
156 IF l_type = 'UNDEFINED' then -- view is invalid
157 bsc_mo_helper_pkg.writeTmp('View '||dimTable||' is invalid', FND_LOG.LEVEL_EXCEPTION);
158 raise bsc_metadata_optimizer_pkg.optimizer_exception;
159 END IF;
160 If l_type IS NULL Then
161 dataLength := 5;
162 return 'VARCHAR2';
163 Else
164 If l_TYPE = 'NUMBER' Then
165 dataLength := 0;
166 Else
167 dataLength := l_length;
168 End If;
169 return l_type;
170 End If;
171
172 EXCEPTION WHEN OTHERS THEN
173 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in GetDimKeyDataType, dimTable='||dimTable|| ', columnName='||
174 columnName||' :'||sqlerrm);
175 raise;
176 End;
177
178 function reorder_index(p_b_pt_table_name varchar2, colColumns IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn) return varchar2 is
179 l_periodicity_id_exists boolean;
180 l_year_exists boolean;
181 l_period_exists boolean;
182 l_type_exists boolean;
183 l_stmt varchar2(1000);
184 l_zero_code_cols varchar2(1000) ;
185 begin
186
187 for i in 0..colColumns.count-1 loop
188 if (colColumns(i).columnName = 'PERIODICITY_ID') then
189 l_periodicity_id_exists := true;
190 elsif (colColumns(i).columnName = 'YEAR') then
191 l_year_exists := true;
192 elsif (colColumns(i).columnName = 'PERIOD') then
193 l_period_exists := true;
194 elsif (colColumns(i).columnName = 'TYPE') then
195 l_type_exists := true;
196 end if;
197 end loop;
198 -- changing here as this isnt used by anything other than bsc
199 -- bug 3876730, add in the following order
200 -- 'PERIODICITY_ID', 'YEAR', 'PERIOD', 'TYPE'
201 l_stmt := null;
202 if (l_periodicity_id_exists) then
203 l_stmt := l_stmt||'PERIODICITY_ID,';
204 end if;
205 if (l_year_exists) then
206 l_stmt := l_stmt||'YEAR,';
207 end if;
208 if (l_period_exists) then
209 l_stmt := l_stmt||'PERIOD,';
210 end if;
211 if (l_type_exists) then
212 l_stmt := l_stmt||'TYPE,';
213 end if;
214
215 l_zero_code_cols := null;
216 for i in 0..colColumns.count-1 loop
217 if (colColumns(i).isKey) then
218 if (colColumns(i).columnName not in ('PERIODICITY_ID', 'YEAR', 'PERIOD', 'TYPE')) then
219 if(BSC_IM_UTILS.needs_zero_code_b_pt(p_b_pt_table_name, colColumns(i).columnName)) then
220 l_zero_code_cols := l_zero_code_cols||colColumns(i).columnName||',';
221 else
222 l_stmt:=l_stmt||colColumns(i).columnName||',';
223 end if;
224 end if;
225 end if;
226 end loop;
227 if (l_zero_code_cols is not null) then
228 l_stmt := l_stmt ||l_zero_code_cols;
229 end if;
230 l_stmt := substr(l_stmt, 1, length(l_stmt)-1);
231 return l_stmt;
232 end;
233
234 PROCEDURE addAWColumns(
235 colCampos IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn,
236 p_b_aw_table IN boolean)
237 IS
238 dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
239 dbColumn_null BSC_METADATA_OPTIMIZER_PKG.clsDBColumn := null;
240 l_columns BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn;
241 BEGIN
242 -- projection
243 dbColumn := dbColumn_null;
244 dbColumn.columnName := 'PROJECTION';
245 dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTVarchar2;
246 dbColumn.columnLength := 100;
247 dbColumn.iskey := false;
248 colCampos(colCampos.count) := dbColumn;
249 --05/18/2005, Venu asked me to not create B_AW table and move change_vector to B table
250 --Add Change Vector right after projection col
251 dbColumn := dbColumn_null;
252 dbColumn.columnName := 'CHANGE_VECTOR';
253 dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
254 dbColumn.columnLength := 0;
255 dbColumn.isKey := false;
256 colCampos(colCampos.count) := dbColumn;
257
258 bsc_mo_helper_pkg.writeTmp('added AW column(s) to table');
259 END;
260
261 PROCEDURE get_column_list(
262 colColumns IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn,
263 p_column_sql OUT NOCOPY VARCHAR2,
264 p_primary_key_sql OUT NOCOPY VARCHAR2,
265 p_keys_exist OUT NOCOPY boolean ) IS
266 l_index1 number;
267 dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
268 BEGIN
269 p_column_sql := null;
270 l_index1 := colColumns.first;
271 LOOP
272 dbColumn := colColumns(l_index1);
273 p_column_sql := p_column_sql || dbColumn.columnName;
274 IF dbColumn.columnLength = 0 THEN
275 p_column_sql := p_column_sql || ' '|| dbColumn.columnType;
276 ELSE
277 p_column_sql := p_column_sql || ' ' || dbColumn.columnType || '(' || dbColumn.columnLength ||')';
278 END IF;
279 IF dbColumn.isKey THEN
280 p_column_sql := p_column_sql || ' NOT NULL';
281 p_primary_key_sql :=p_primary_key_sql|| dbColumn.columnName;
282 p_primary_key_sql :=p_primary_key_sql||',';
283 -- Bug 4765104
284 IF (dbColumn.isTimeKey=false) THEN
285 p_keys_exist := true;
286 END IF;
287 END IF;
288 p_column_sql := p_column_sql || ',';
289 EXIT WHEN l_index1 = colColumns.last;
290 l_index1 := colColumns.next(l_index1);
291 END LOOP;
292 p_column_sql := substr(p_column_sql, 1, length(p_column_sql)-1);
293 bsc_mo_helper_pkg.writeTmp('get_col_list, p_primary_key_sql ='||p_primary_key_sql);
294 p_primary_key_sql := substr(p_primary_key_sql, 1, length(p_primary_key_sql)-1);
295 bsc_mo_helper_pkg.writeTmp('get_col_list, p_primary_key_sql ='||p_primary_key_sql);
296 EXCEPTION WHEN OTHERS THEN
297 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in get_column_list:'||sqlerrm);
298 raise;
299 END;
300
301 PROCEDURE handle_ddl(p_object_name varchar2, p_object_type varchar2, p_ddl varchar2) IS
302 BEGIN
303 IF (g_parallelize) THEN
304 insertDDL(p_object_name, p_ddl, p_object_type);
305 return;
306 END IF;
307 IF (p_object_type = 'INDEX') THEN
308 begin
309 BSC_MO_HELPER_PKG.do_ddl('drop index '||p_object_name, ad_ddl.drop_index, p_object_name);
310 exception when others then
311 null;
312 end;
313 BSC_MO_HELPER_PKG.Do_DDL (p_ddl, ad_ddl.create_index, p_object_name);
314 ELSIF p_object_type='TABLE' THEN
315 BEGIN
316 BSC_MO_HELPER_PKG.Do_DDL (p_ddl, ad_ddl.create_table, p_object_name);
317 exception when others then
318 if sqlcode = -955 then --already exists for some reason, so drop and recreate
319 BSC_MO_HELPER_PKG.Do_DDL ('drop table '||p_object_name, ad_ddl.drop_table, p_object_name);
320 BSC_MO_HELPER_PKG.Do_DDL (p_ddl, ad_ddl.create_table, p_object_name);
321 else
322 raise;
323 end if;
324 END;
325 END IF;
326 if (bsc_metadata_optimizer_pkg.g_log) then
327 bsc_mo_helper_pkg.writeTmp(p_ddl);
328 bsc_mo_helper_pkg.writetmp(p_object_type||' '||p_object_name||' created successfully');
329 end if;
330
331 EXCEPTION WHEN OTHERS THEN
332 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in handle_ddl, Table='||p_object_name||',ddl='||p_ddl||', error='||sqlerrm);
333 raise;
334 END;
335
336 FUNCTION get_table_name(p_table_name varchar2) return VARCHAR2 IS
337 l_index number:=0;
338 BEGIN
339 IF NOT bsc_mo_helper_pkg.tableexists(p_table_name) THEN
340 return p_table_name;
341 END IF;
342 LOOP
343 IF NOT bsc_mo_helper_pkg.tableexists(p_table_name||l_index) THEN
344 return p_table_name||l_index;
345 END IF;
346 l_index := l_index+1;
347 END LOOP;
348 EXCEPTION WHEN OTHERS THEN
349 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in get_table_name, Table='||p_table_name);
350 raise;
351 END;
352
353 FUNCTION create_b_prj_table(p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable, p_column_clause VARCHAR2, p_storage_clause VARCHAR2)
354 RETURN VARCHAR2 IS
355 l_stmt VARCHAR2(32767);
356 l_table_name varchar2(100);
357 BEGIN
358 bsc_mo_helper_pkg.writeTmp('create_b_prj_table for '||p_table.name);
359 l_table_name := get_table_name(p_table.name||'_PRJ');
360 l_stmt := 'CREATE TABLE ' || l_table_name||' (' ||p_column_clause||') '||p_storage_clause;
361 handle_ddl(l_table_name, 'TABLE', l_stmt);
362
363 return l_table_name;
364 EXCEPTION WHEN OTHERS THEN
365 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in create_b_prj_table, Table='||p_table.name||', stmt='||l_stmt||', error='||sqlerrm);
366 raise;
367 END;
368
369 FUNCTION create_i_rowid_table(p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable, p_storage_clause VARCHAR2)
370 RETURN VARCHAR2 IS
371 l_stmt VARCHAR2(4000);
372 l_table_name varchar2(100);
373
374 BEGIN
375
376 l_table_name := get_table_name(p_table.name||'_ROWID');
377 l_stmt := 'CREATE TABLE ' || l_table_name||' (ROW_ID ROWID, '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' NUMBER) '||p_storage_clause;
378 handle_ddl(l_table_name, 'TABLE', l_stmt);
379 return l_table_name;
380 EXCEPTION WHEN OTHERS THEN
381 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in create_i_rowid_table, Table='||p_table.name||', stmt='||l_stmt||', error:'||sqlerrm);
382 raise;
383 END;
384
385 PROCEDURE add_to_update_tables_list(p_Table_name VARCHAR2, p_property VARCHAR2, p_value VARCHAR2) IS
386 l_update_rec rec_update_tables;
387 BEGIN
388 l_update_rec.table_name := p_table_name;
389 l_update_rec.property := p_property||BSC_DBGEN_STD_METADATA.BSC_ASSIGNMENT||p_value;
390 g_update_tables(g_update_tables.count+1) := l_update_rec;
391 EXCEPTION WHEN OTHERS THEN
392 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in add_to_update_tables_list, Table='||p_table_name|| ', property='||
393 p_property||', value='||p_value||', error:'||sqlerrm);
394 raise;
395 END;
396
397
398 --****************************************************************************
399 -- CrearTablaDB : CreateTableInDB
400 --
401 -- DESCRIPTION:
402 -- Create the given table in the database.
403 -- Create an index on the key columns.
404 --
405 -- PARAMETERS:
406 -- p_table_name: table name
407 -- colColumns: collection with the table columns
408 -- db_obj: database object
409 --****************************************************************************
410 PROCEDURE CreateTableInDB(
411 p_table_name IN VARCHAR2,
412 colColumns IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn,
413 TableTbsName IN VARCHAR,
414 IndexTbsName IN VARCHAR,
415 p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable,
416 p_table_type IN VARCHAR2)
417 IS
418 dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
419 l_stmt VARCHAR2(32767);
420 columnList VARCHAR2(8000):=null;
421 l_primary_key VARCHAR2(1000) := null;
422 l_index1 NUMBER;
423 l_newline varchar2(10) :='
424 ';
425 l_index_stmt VARCHAR2(32767) ;
426 l_index_name VARCHAR2(100);
427 l_error varchar2(2000);
428 l_aw_stmt varchar2(32767);
429 l_aw_index_stmt varchar2(32767);
430 l_table BSC_METADATA_OPTIMIZER_PKG.clsTable;
431
432 l_b_aw_columns BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn;
433
434 l_table_name VARCHAR2(100);
435 b_keys_exist boolean ;
436
437 l_table_misc_clause varchar2(4000) := null;
438 BEGIn
439 --Create the table in the database
440 IF (colColumns.count =0) THEN
441 RETURN;
442 END IF;
443 get_column_list(colColumns, columnList, l_primary_key, b_keys_exist);
444
445 l_stmt := 'CREATE TABLE ' || p_table_name||' (' || columnList||') ';
446 if (p_table_type = 'B' and b_keys_exist) then
447 l_stmt := l_stmt || bsc_metadata_optimizer_pkg.g_partition_clause;
448 end if;
449 l_stmt := l_stmt||' TABLESPACE '||TableTbsName || ' '||BSC_METADATA_OPTIMIZER_PKG.gStorageClause;
450 handle_ddl(p_table_name, 'TABLE', l_stmt);
451
452 -- Create B_PRJ table and indexes
453 IF (p_table_type = 'B') THEN
454 -- Bug 4765104, partition only when dim keys exist
455 if (b_keys_exist) then
456 l_table_misc_clause := bsc_metadata_optimizer_pkg.g_partition_clause;
457 add_to_update_tables_list(p_table_name, BSC_DBGEN_STD_METADATA.BSC_PARTITION,
458 bsc_metadata_optimizer_pkg.g_num_partitions);
459 end if;
460 l_table_misc_clause := l_table_misc_clause||' TABLESPACE '||TableTbsName ||
461 ' '||BSC_METADATA_OPTIMIZER_PKG.gStorageClause;
462 l_table_name := create_b_prj_table(p_table, columnList, l_table_misc_clause);
463 add_to_update_tables_list(p_table_name, BSC_DBGEN_STD_METADATA.BSC_B_PRJ_TABLE, l_table_name);
464 --bug fix 5647971 centralized logic to get index name
465 -- added api in helper package
466 l_index_name := BSC_MO_HELPER_PKG.generate_index_name(p_table_name,'B','1');
467 l_index_stmt := 'CREATE BITMAP INDEX '||l_index_name||' ON '||p_table_name||'(YEAR) TABLESPACE '||IndexTbsName||
468 ' '|| BSC_METADATA_OPTIMIZER_PKG.gStorageClause;
469 IF (bsc_metadata_optimizer_pkg.g_num_partitions>1 and b_keys_exist) then
470 l_index_stmt := l_index_stmt||' LOCAL ';
471 END IF;
472 -- Create Bitmap indexes on YEAR And CHANGE_VECTOR (AW case)
473
474 handle_ddl(l_index_name, 'INDEX', l_index_stmt);
475 -- For AW create separate index on CHANGE_VECTOR
476 IF (p_table.Impl_type=2) THEN -- create an AW type index
477 --bug fix 5647971 centralized logic to get index name
478 -- added api in helper package
479 l_index_name := BSC_MO_HELPER_PKG.generate_index_name(p_table_name,'B','2');
480 l_index_stmt := 'CREATE BITMAP INDEX ' || l_index_name || ' ON '|| p_table_name ||
481 '(CHANGE_VECTOR) TABLESPACE '||IndexTbsName || ' '|| BSC_METADATA_OPTIMIZER_PKG.gStorageClause;
482 IF (bsc_metadata_optimizer_pkg.g_num_partitions>1 and b_keys_exist) then
483 l_index_stmt := l_index_stmt||' LOCAL ';
484 END IF;
485 handle_ddl(l_index_name, 'INDEX', l_index_stmt);
486 END IF;
487 return; -- B table has only the 2 bit map indexes
488 END IF;
489
490 -- create I_ROWID table for Input Tables
491 IF p_table_type ='I' THEN
492 -- 11/16/2005, always generate rowid tables, after discussion with venu/mauricio.
493 --AND bsc_metadata_optimizer_pkg.g_num_partitions>1
494 l_table_name := create_i_rowid_table(p_table, ' TABLESPACE '||TableTbsName ||
495 ' '||BSC_METADATA_OPTIMIZER_PKG.gStorageClause);
496 add_to_update_tables_list(p_table_name, BSC_DBGEN_STD_METADATA.BSC_I_ROWID_TABLE, l_table_name);
497 END IF;
498
499 IF l_primary_key IS NOT NULL THEN -- create normal index
500 if (p_table_name like 'BSC_S%_PT' ) then
501 l_primary_key := reorder_index(p_table_name, colColumns);
502 BSC_MO_HELPER_PKG.writeTmp('index columns after reordering is :'||l_primary_key);
503 end if;
504 l_index_name := p_table_name ||'_U1';
505 l_index_stmt := 'CREATE UNIQUE INDEX ' || l_index_name || ' ON '|| p_table_name ||
506 ' ('|| l_primary_key || ') '||
507 ' TABLESPACE '||IndexTbsName || ' '|| BSC_METADATA_OPTIMIZER_PKG.gStorageClause;
508 handle_ddl(l_index_name, 'INDEX', l_index_stmt);
509 END IF;
510
511 EXCEPTION WHEN OTHERS THEN
512 l_error := sqlerrm;
513 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in CreateTableInDB, Table='||p_table_name|| ':'||l_error);
514 BSC_MO_HELPER_PKG.writeTmp('Table creation stmt ='||l_stmt, FND_LOG.LEVEL_UNEXPECTED, true );
515 BSC_MO_HELPER_PKG.writeTmp('Index creation stmt ='||l_index_stmt, FND_LOG.LEVEL_UNEXPECTED, true );
516 raise;
517 END ;
518
519 FUNCTION ExistIndex(IndexName IN VARCHAR2) RETURN boolean IS
520 l_res boolean := FALSE;
521 l_dummy VARCHAR2(1000);
522
523 CURSOR c1 (pSchema IN VARCHAR2) IS
524 SELECT INDEX_NAME FROM ALL_INDEXES
525 WHERE INDEX_NAME = upper(IndexName)
526 AND OWNER = pSchema;
527
528 l_error VARCHAR2(1000);
529 BEGIN
530 OPEN c1(BSC_METADATA_OPTIMIZER_PKG.gBscSchema);
531 FETCH c1 INTO l_dummy;
532 IF c1%FOUND THEN
533 l_res := TRUE;
534 END IF;
535 Close c1;
536 RETURN l_res;
537 EXCEPTION WHEN OTHERS THEN
538 l_error := sqlerrm;
539 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in ExistIndex:'||l_error);
540 raise;
541 END ;
542
543 --****************************************************************************
544 -- CreateProjTable
545 --
546 -- DESCRIPTION:
547 -- Creates the given table in the database.
548 --
549 -- PARAMETERS:
550 -- Tabla: Table. It is an object with all inFORmation about the table
551 --
552 --****************************************************************************
553 PROCEDURE CreateProjTable(p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable) IS
554
555 Llave BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
556 Dato BSC_METADATA_OPTIMIZER_PKG.clsDataField;
557 colCampos BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn;
558
559 dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
560 NomCampoPeriod VARCHAR2(100);
561 NomCampoSubPeriod VARCHAR2(100);
562 msg VARCHAR2(100);
563 uv_name VARCHAR2(100);
564 l_stmt VARCHAR2(1000);
565 isBaseTable Boolean;
566 createTable Boolean;
567
568 Tabla_Keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
569 Tabla_Data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
570
571 l_index NUMBER;
572
573 New_clsdbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
574 l_error VARCHAR2(1000);
575
576 dimTable VARCHAR2(100);
577 l_temp1 NUMBER;
578 columnSize NUMBER;
579
580 BEGIN
581
582 --BIS DIMENSIONS: From now on we cannot assume that USER CODE and CODE are
583 --VARCHAR2 and NUMBER respectively. From now on I will get the data type
584 --direclty from the dimension table.
585
586 --Table should not exist
587 IF bsc_mo_helper_pkg.tableexists(p_table.projectionTable) THEN
588 bsc_mo_helper_pkg.droptable(p_table.projectionTable);
589 END IF;
590
591 --Create the table in the database
592 Tabla_Keys := p_table.keys;
593 Tabla_Data := p_table.data;
594 --Keys
595 l_index := Tabla_Keys.first;
596 LOOP
597 EXIT WHEN Tabla_Keys.count=0;
598 Llave := Tabla_Keys(l_index);
599 dbColumn := New_clsdbColumn;
600
601 l_temp1 := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, Llave.keyName);
602
603 dimTable := BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).name;
604 dbColumn.columnName := Llave.keyName;
605 dbColumn.columnType := GetDimKeyDataType(dimTable, 'CODE', columnSize);
606 dbColumn.columnlength := columnSize;
607 dbColumn.isKey := TRUE;
608 dbColumn.isTimeKey := FALSE;
609 colCampos(colCampos.count) := dbColumn;
610
611 EXIT WHEN l_index = Tabla_keys.last;
612 l_index := Tabla_keys.next(l_index);
613
614 END LOOP;
615 --Year
616 dbColumn := New_clsdbColumn;
617 dbColumn.columnName := 'YEAR';
618 dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
619 dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_INTEGER;
620 dbColumn.isKey := TRUE;
621 dbColumn.isTimeKey := TRUE;
622 colCampos(colCampos.count) := dbColumn;
623
624 --Type
625 dbColumn := New_clsdbColumn;
626 dbColumn.columnName := 'TYPE';
627 dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
628 dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_BYTE;
629 dbColumn.isKey := TRUE;
630 dbColumn.isTimeKey := TRUE;
631 colCampos(colCampos.count) := dbColumn;
632
633 --Period
634 dbColumn := New_clsdbColumn;
635 dbColumn.columnName := 'PERIOD';
636 dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
637 dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_INTEGER;
638 dbColumn.isKey := TRUE;
639 dbColumn.isTimeKey := TRUE;
640 colCampos(colCampos.count) := dbColumn;
641
642 --Periodicity_Id
643 dbColumn := New_clsdbColumn;
644 dbColumn.columnName := 'PERIODICITY_ID';
645 dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
646 dbColumn.columnLength := 0;
647 dbColumn.isKey := TRUE;
648 dbColumn.isTimeKey := TRUE;
649 colCampos(colCampos.count) := dbColumn;
650
651
652 --Period_Type_Id
653 --Fix bug#3353111 In Projection Tables created FOR target at dIFferent levels,
654 -- PERIOD_TYPE_ID should be not null and should not included in the index
655 dbColumn := New_clsdbColumn;
656 dbColumn.columnName := 'PERIOD_TYPE_ID';
657 dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
658 dbColumn.columnLength := 0;
659 dbColumn.isKey := FALSE;
660 colCampos(colCampos.count) := dbColumn;
661
662 --Data columns
663 sort_data_columns(Tabla_Data);
664 l_index := Tabla_Data.first;
665 LOOP
666 EXIT WHEN Tabla_Data.count =0;
667 Dato := Tabla_Data(l_index);
668 dbColumn := New_clsdbColumn;
669 dbColumn.columnName := Dato.fieldName;
670 dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
671 dbColumn.columnLength := 0;
672 dbColumn.isKey := FALSE;
673 colCampos(colCampos.count) := dbColumn;
674 EXIT WHEN l_index = Tabla_Data.last;
675 l_index := Tabla_data.next(l_index);
676 END LOOP;
677 CreateTableInDB(
678 p_table.projectionTable,
679 colCampos,
680 BSC_METADATA_OPTIMIZER_PKG.gSummaryTableTbsName,
681 BSC_METADATA_OPTIMIZER_PKG.gSummaryIndexTbsName,
682 p_table,
683 'PROJECTION');
684
685 EXCEPTION WHEN OTHERS THEN
686 l_error := sqlerrm;
687 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in CreateProjTable:'||l_error);
688 raise;
689 END ;
690
691
692
693
694 PROCEDURE AddPeriodicityId(BaseTable BSC_METADATA_OPTIMIZER_PKG.clsTable) IS
695 l_stmt VARCHAR2(32000);
696 IndexName VARCHAR2(1000);
697 IndexedColumns VARCHAR2(4000);
698 keyColumn BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
699 BaseTable_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
700
701 l_index NUMBER;
702 l_error VARCHAR2(1000);
703 l_zero_code_cols VARCHAR2(1000) := null;
704 l_prj_table varchar2(100);
705
706 CURSOR cDropIndexes(p_table IN VARCHAR2, p_owner IN VARCHAR2) IS
707 SELECT INDEX_NAME FROM ALL_INDEXES WHERE TABLE_NAME= p_table and table_owner=p_owner;
708 BEGIN
709
710 IF bsc_mo_helper_pkg.tableExists(BaseTable.Name) THEN
711 IF Not bsc_mo_helper_pkg.table_column_exists(BaseTable.Name, 'PERIODICITY_ID') THEN
712 --Add periodicity_id
713 l_stmt := 'ALTER TABLE ' || BaseTable.Name || ' ADD PERIODICITY_ID NUMBER';
714 BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, BaseTable.Name);
715
716 --Update periodicity_id
717 l_stmt := 'UPDATE ' || BaseTable.Name||' SET PERIODICITY_ID =:1 ';
718 EXECUTE IMMEDIATE l_stmt using BaseTable.Periodicity;
719
720 --Set periodicity_id NOT NULL
721 l_stmt := 'ALTER TABLE ' || BaseTable.Name || ' MODIFY PERIODICITY_ID NOT NULL';
722 BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, BaseTable.Name);
723
724 --Drop existing indexes
725 OPEN cDropIndexes(BaseTable.Name, BSC_METADATA_OPTIMIZER_PKG.gBSCSchema);
726 LOOP
727 FETCH cDropIndexes INTO IndexName;
728 EXIT WHEN cDropIndexes%NOTFOUND;
729 --Drop index
730 l_stmt := 'DROP INDEX ' || IndexName;
731 BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.drop_index, IndexName);
732 END LOOP;
733 CLOSE cDropIndexes;
734 --Re-create index to include PERIODICITY_ID
735 IndexName := BaseTable.Name || '_U1';
736 IndexedColumns := IndexedColumns || 'PERIODICITY_ID, YEAR, PERIOD, TYPE,';
737 BaseTable_keys := BaseTable.keys;
738 l_index := BaseTable_keys.first;
739 LOOP
740 EXIT WHEN BaseTable_keys.count = 0;
741 keyColumn := BaseTable_Keys(l_index);
742 if(BSC_IM_UTILS.needs_zero_code_b_pt(BaseTable.Name, keyColumn.keyName)) then
743 l_zero_code_cols := l_zero_code_cols||keyColumn.keyName||',';
744 else
745 IndexedColumns := IndexedColumns ||keyColumn.keyName||',';
746 end if;
747 EXIT WHEN l_index = BaseTable_keys.last;
748 l_index := BaseTable_keys.next(l_index);
749 END LOOP;
750 IndexedColumns := IndexedColumns ||l_zero_code_cols;
751 IndexedColumns := substr(IndexedColumns, 1, Length(IndexedColumns)-1);
752 begin
753 -- just for safety, in case of corrupt left over tables
754 bsc_mo_helper_pkg.do_ddl('drop index '||IndexName, ad_ddl.drop_index, IndexName);
755 -- ignore all exceptions here
756 exception when others then
757 null;
758 end;
759 l_stmt := 'CREATE UNIQUE INDEX '|| IndexName||
760 ' ON ' || BaseTable.Name || ' (' || IndexedColumns || ') '||
761 ' TABLESPACE '||BSC_METADATA_OPTIMIZER_PKG.gBaseIndexTbsName||' ' || BSC_METADATA_OPTIMIZER_PKG.gStorageClause;
762 bsc_mo_helper_pkg.Do_DDL (l_stmt, ad_ddl.create_index, IndexName);
763 END IF;
764 END IF;
765 -- Bug#:5214589
766 -- Alter B_PRJ Table if required
767 l_prj_table := BSC_DBGEN_METADATA_READER.get_table_properties(BaseTable.Name,
768 BSC_DBGEN_STD_METADATA.BSC_B_PRJ_TABLE);
769 if (l_prj_table is not null) then
770 IF Not bsc_mo_helper_pkg.table_column_exists(l_prj_table, 'PERIODICITY_ID') THEN
771 --Add periodicity_id
772 l_stmt := 'ALTER TABLE ' || l_prj_table || ' ADD PERIODICITY_ID NUMBER';
773 BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, BaseTable.Name);
774 --Update periodicity_id
775 l_stmt := 'UPDATE ' || l_prj_table||' SET PERIODICITY_ID =:1 ';
776 EXECUTE IMMEDIATE l_stmt using BaseTable.Periodicity;
777 --Set periodicity_id NOT NULL
778 l_stmt := 'ALTER TABLE ' || l_prj_table || ' MODIFY PERIODICITY_ID NOT NULL';
779 BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, BaseTable.Name);
780 END IF;
781 end if;
782 EXCEPTION WHEN OTHERS THEN
783 l_error := sqlerrm;
784 BSC_MO_HELPER_PKG.writeTmp('Error in statement:'||l_stmt, FND_LOG.LEVEL_EXCEPTION, true);
785 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in AddPeriodicityId:'||l_error);
786 raise;
787 END ;
788
789 --****************************************************************************
790 -- CreateDefMaterializedView
791 --
792 -- DESCRIPTION:
793 -- Create the definition of a materialized view corresponding to the
794 -- given system table. The table is FOR an EDW KPI.
795 --
796 -- PARAMETERS:
797 -- Tabla: clsTabla object with all info about the table
798 --****************************************************************************
799 PROCEDURE CreateDefMaterializedView(p_table IN BSC_METADATA_OPTIMIZER_PKG.clsTable) IS
800 l_stmt VARCHAR2(100);
801 cal_id NUMBER;
802 num_anos NUMBER;
803 num_anosant NUMBER;
804 fiscal_year NUMBER;
805 start_year NUMBER;
806 END_year NUMBER;
807 l_index NUMBER;
808 l_error VARCHAR2(1000);
809 BEGIN
810 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
811 BSC_MO_HELPER_PKG.writeTmp('Inside CreateDefMaterializedView', FND_LOG.LEVEL_PROCEDURE);
812 END IF;
813 --Create the materialized view
814 l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, p_table.periodicity);
815 cal_id := BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_index).CalENDarID;
816
817 l_index := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalENDars, cal_id);
818 num_anos := BSC_METADATA_OPTIMIZER_PKG.gCalENDars(l_index).NumOfYears;
819 num_anosant := BSC_METADATA_OPTIMIZER_PKG.gCalENDars(l_index).PreviousYears;
820 fiscal_year := BSC_METADATA_OPTIMIZER_PKG.gCalENDars(l_index).CurrFiscalYear;
821
822 start_year := fiscal_year - num_anosant;
823 END_year := start_year + num_anos - 1;
824
825 -- EDW code, obsoleted
826 --BSC_INTEGRATION_MV_GEN.Create_Def_Materialized_View(p_table.name, cal_id, start_year||'-'||END_year);
827 --BSC_MO_HELPER_PKG.CHeckError('BSC_INTEGRATION_MV_GEN.Create_Def_Materialized_View');
828
829 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
830 BSC_MO_HELPER_PKG.writeTmp('Completed CreateDefMaterializedView FOR '||p_table.name, FND_LOG.LEVEL_PROCEDURE);
831 END IF;
832
833 EXCEPTION WHEN OTHERS THEN
834 l_error := sqlerrm;
835 BSC_MO_HELPER_PKG.TerminateWithMsg('Exception in CreateDefMaterializedView:'||l_error);
836 raise;
837
838 END ;
839
840
841 --****************************************************************************
842 -- GetSubperiodColumnName : GetNombreCampoSubPeriod
843 --
844 -- DESCRIPTION:
845 -- RETURNs the subperiod column name of the given periodicity.
846 -- It is got from BSC_SYS_PERIODICITIES table
847 --
848 -- PARAMETERS:
849 -- Periodicidad: Periodicity code
850 --****************************************************************************
851 FUNCTION GetSubperiodColumnName(Periodicity IN VARCHAR2) RETURN VARCHAR2 IS
852
853 l_RETURN VARCHAR2(100);
854 CURSOR c1 IS
855 SELECT SUBPERIOD_COL_NAME
856 FROM BSC_SYS_PERIODICITIES
857 WHERE PERIODICITY_ID = Periodicity;
858 l_error varchar2(1000);
859 BEGIN
860
861 OPEN c1;
862 FETCH c1 INTO l_RETURN;
863 IF (c1%FOUND) THEN
864 IF (l_RETURN IS NULL) THEN
865 l_RETURN := '';
866 END IF;
867 END IF;
868 Close c1;
869
870 RETURN l_RETURN;
871
872 EXCEPTION WHEN OTHERS THEN
873 l_error := sqlerrm ;
874 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in GetSubperiodColumnName, Periodicity = '||Periodicity||' : '||l_error);
875 BSC_MO_HELPER_PKG.TerminateWithError ('BSC_SUBPERIOD_FAILED', 'GetSubperiodColumnName');
876 RAISE;
877 END;
878
879
880 --****************************************************************************
881 -- GetPeriodColumnName : GetNombreCampoPeriod
882 --
883 -- DESCRIPTION:
884 -- RETURNs the period column name of the given periodicity.
885 -- It is got from BSC_SYS_PERIDIOCITIES table.
886 --
887 -- PARAMETERS:
888 -- Periodicidad: Periodicity code
889 --****************************************************************************
890 FUNCTION GetPeriodColumnName(Periodicity IN NUMBER) RETURN VARCHAR2 IS
891
892 CURSOR c1 IS
893 SELECT PERIOD_COL_NAME
894 FROM BSC_SYS_PERIODICITIES
895 WHERE PERIODICITY_ID = Periodicity;
896 l_RETURN VARCHAR2(100);
897 l_error VARCHAR2(1000);
898 BEGIN
899
900
901 OPEN c1;
902 FETCH c1 INTO l_RETURN;
903
904 IF (c1%NOTFOUND) THEN
905 l_RETURN := 'PERIOD';
906 ELSE
907 IF (l_RETURN IS NULL ) THEN
908 l_RETURN := 'PERIOD';
909 END IF;
910 END IF;
911
912 CLOSE C1;
913
914 RETURN l_RETURN;
915
916 EXCEPTION WHEN OTHERS THEN
917 l_error := sqlerrm;
918 FND_FILE.put_line(FND_FILE.LOG, 'Exception in GetPeriodColumnName:'||l_error);
919 BSC_MO_HELPER_PKG.TerminateWithError('BSC_RETR_PERIOD_FAILED' , 'GetPeriodColumnName');
920 raise;
921 END ;
922
923 --****************************************************************************
924 -- GetKeyLength : GetTamanoCampoLlave
925 --
926 -- DESCRIPTION:
927 -- Get the lenght of the given key column. FOR that, it looks FOR the
928 -- lenght of the column USER_CODE of the dimension table associted to
929 -- the given key column.
930 --
931 -- PARAMETERS:
932 -- Llave: key column name
933 --****************************************************************************
934 /*
935 FUNCTION GetKeyLength(Llave IN VARCHAR2) RETURN NUMBER IS
936 Maestra VARCHAR2(100);
937 l_stmt VARCHAR2(300);
938 l_temp NUMBER ;
939 CURSOR c1 (p1 VARCHAR2) IS
940 SELECT DATA_LENGTH FROM ALL_TAB_COLUMNS
941 WHERE UPPER(TABLE_NAME)= p1
942 AND UPPER(COLUMN_NAME) = 'USER_CODE'
943 AND UPPER(OWNER) = BSC_METADATA_OPTIMIZER_PKG.gBSCSchema;
944
945 l_error VARCHAR2(1000);
946 BEGIN
947
948 l_temp := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMastertable, Llave);
949 IF (l_temp=-1) THEN
950 Maestra := null;
951 RETURN 256;
952 ELSE
953 Maestra := BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp).name;
954 END IF;
955
956 --APPS
957 OPEN c1(Maestra);
958 FETCH c1 INTO l_temp;
959 IF (c1%NOTFOUND) THEN
960 l_temp := 0;
961 END IF;
962 Close c1;
963
964 RETURN l_temp;
965
966 EXCEPTION WHEN OTHERS THEN
967 l_error := sqlerrm;
968 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
969 bsc_mo_helper_pkg.writeTmp('Exception in GetKeyLength:'||l_error);
970 END IF;
971 raise;
972 END;
973 */
974
975 PROCEDURE clearColumn(col IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.clsDBColumn) IS
976 BEGIN
977 col.columnName := null;
978 col.columnType := null;
979 col.columnLength := null;
980 col.isKey := null;
981 END;
982
983 FUNCTION getDataTypeForTimeFK(pCalendar IN NUMBER, pPeriodicity IN NUMBER) return VARCHAR2 IS
984 CURSOR cPeriodicityType IS
985 SELECT periodicity_type
986 FROM bsc_sys_periodicities
987 where calendar_id = pCalendar
988 and periodicity_id = pPeriodicity;
989
990 l_periodicity NUMBER;
991 BEGIN
992 OPEN cPeriodicityType;
993 FETCH cPeriodicityType INTO l_periodicity;
994 CLOSE cPeriodicityType;
995
996 IF (l_periodicity = 9) THEN -- daily
997 return BSC_METADATA_OPTIMIZER_PKG.DTDate;
998 ELSE
999 return BSC_METADATA_OPTIMIZER_PKG.DTVarchar2;
1000 END IF;
1001 END;
1002
1003
1004 PROCEDURE addYearPeriodType (
1005 colCampos IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn,
1006 periodicity IN NUMBER,
1007 addSubPeriod IN BOOLEAN,
1008 tableType IN NUMBER -- 0 for input table
1009 ) IS
1010
1011 dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
1012 NomCampoPeriod VARCHAR2(100);
1013 NomCampoSubPeriod VARCHAR2(100);
1014 periodicitySource VARCHAR2(100);
1015 l_temp1 NUMBER;
1016 l_temp2 NUMBER;
1017
1018 BEGIN
1019
1020 l_temp1 := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, periodicity);
1021 l_temp2 := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gCalendars, BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp1).CalendarID);
1022 periodicitySource := BSC_METADATA_OPTIMIZER_PKG.gCalendars(l_temp2).Source;
1023
1024 If periodicitySource = 'PMF' and tableType= 0 Then
1025 --TIME_FK
1026 dbColumn.columnName := 'TIME_FK';
1027 dbColumn.columnType := getDataTypeForTimeFK(
1028 BSC_METADATA_OPTIMIZER_PKG.gPeriodicities(l_temp1).CalendarID,
1029 periodicity);
1030 IF (dbColumn.columnType = BSC_METADATA_OPTIMIZER_PKG.DTVarchar2 ) THEN
1031 dbColumn.columnLength := 4000;
1032 ELSE
1033 dbColumn.columnLength := 0;
1034 END IF;
1035 dbColumn.isKey := TRUE;
1036 dbColumn.isTimeKey := TRUE;
1037 ELSE
1038 dbColumn.columnName := 'YEAR';
1039 dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
1040 dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_INTEGER;
1041 dbColumn.isKey := TRUE;
1042 dbColumn.isTimeKey := TRUE;
1043 END IF;
1044
1045 IF (colCampos.count > 0 ) THEN
1046 colCampos(colCampos.last+1) := dbColumn;
1047 ELSE
1048 colCampos(0) := dbColumn;
1049 END IF;
1050
1051 --Type
1052 clearColumn(dbColumn);
1053 dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;
1054 dbColumn.columnName := 'TYPE';
1055 dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
1056 dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_BYTE;
1057 dbColumn.isKey := TRUE;
1058 dbColumn.isTimeKey := TRUE;
1059
1060 colCampos(colCampos.last+1) := dbColumn;
1061
1062 If periodicitySource = 'PMF' and tableType= 0 Then
1063 null;
1064 ELSE
1065 --Period
1066 NomCampoPeriod := GetPeriodColumnName(Periodicity);
1067 clearColumn(dbColumn);
1068 dbColumn.columnName := NomCampoPeriod;
1069 dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
1070 dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_INTEGER;
1071 dbColumn.iskey := TRUE;
1072 dbColumn.isTimeKey := TRUE;
1073 colCampos(colCampos.last+1) := dbColumn;
1074
1075 --SubPeriod
1076 IF (addSubPeriod) THEN
1077 NomCampoSubPeriod := GetSubperiodColumnName(Periodicity);
1078 IF NomCampoSubPeriod <> '' AND NomCampoSubPeriod IS NOT NULL THEN
1079 dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;
1080 dbColumn.columnName := NomCampoSubPeriod;
1081 dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
1082 dbColumn.columnLength := BSC_METADATA_OPTIMIZER_PKG.ORA_DATA_PRECISION_INTEGER;
1083 dbColumn.isKey := TRUE;
1084 dbColumn.isTimeKey := TRUE;
1085 colCampos(colCampos.last+1) := dbColumn;
1086 END IF;
1087 END IF;
1088 END IF;
1089
1090 END;
1091
1092
1093 PROCEDURE add_columns_to_tables(p_Table BSC_METADATA_OPTIMIZER_PKG.clsTable)IS
1094 l_origin_tables DBMS_SQL.VARCHAR2_TABLE;
1095 l_stmt VARCHAR2(1000);
1096 l_table_type varchar2(10);
1097 l_prj_table varchar2(100);
1098 BEGIN
1099 bsc_mo_helper_pkg.writeTmp('Inside Alter Tables for '||p_Table.name||' with data.count= '||p_table.data.count, FND_LOG.LEVEL_STATEMENT, true);
1100 IF (p_table.data.count=0) THEN
1101 bsc_mo_helper_pkg.writeTmp('Completed Alter Tables zero data count= ', FND_LOG.LEVEL_STATEMENT, true);
1102 return;
1103 END IF;
1104 l_origin_tables := BSC_DBGEN_UTILS.get_source_table_names(p_table.name);
1105 l_origin_tables(l_origin_tables.count) := p_table.name;
1106 FOR i IN p_table.data.first..p_table.data.last LOOP
1107 IF p_table.data(i).changeType='NEW' THEN -- new column, insert into db_tables_cols
1108 --alter all tables in l_origin_tables as they need this new column, eg, T, B and I tables if p_table is a T table
1109 bsc_mo_helper_pkg.writeTmp('Measure '||p_table.data(i).fieldName||' needs to be added ', FND_LOG.LEVEL_STATEMENT, false);
1110 FOR j in l_origin_tables.first..l_origin_tables.last LOOP
1111 l_table_type := BSC_DBGEN_UTILS.get_table_type(l_origin_tables(j));
1112 IF BSC_METADATA_OPTIMIZER_PKG.g_BSC_mv AND l_table_type='T' THEN -- T tables do not exist in MV arch.
1113 null;
1114 ELSIF Not bsc_mo_helper_pkg.table_column_exists(l_origin_tables(j), p_table.data(i).fieldName) THEN
1115 l_stmt := 'ALTER TABLE ' || l_origin_tables(j) || ' ADD '||p_table.data(i).fieldName||' NUMBER';
1116 bsc_mo_helper_pkg.writeTmp(l_stmt, FND_LOG.LEVEL_STATEMENT, false);
1117 BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, l_origin_tables(j));
1118 --IF B table is being altered,, B_PRJ table also needs to b altered
1119 END IF;
1120 IF BSC_MO_LOADER_CONFIG_PKG.isBasicTable(l_origin_tables(j)) THEN
1121 -- Alter B_PRJ Table if required
1122 l_prj_table := BSC_DBGEN_METADATA_READER.get_table_properties(l_origin_tables(j), BSC_DBGEN_STD_METADATA.BSC_B_PRJ_TABLE);
1123 if (l_prj_table is not null) then
1124 IF Not bsc_mo_helper_pkg.table_column_exists(l_prj_table, p_table.data(i).fieldName) THEN
1125 l_stmt := 'ALTER TABLE ' || l_prj_table || ' ADD '||p_table.data(i).fieldName||' NUMBER';
1126 bsc_mo_helper_pkg.writeTmp(l_stmt, FND_LOG.LEVEL_STATEMENT, false);
1127 BSC_MO_HELPER_PKG.Do_DDL(l_stmt, ad_ddl.alter_table, l_prj_table);
1128 END IF;
1129 end if;
1130 l_stmt := 'ALTER MATERIALIZED VIEW LOG ON ' || BSC_METADATA_OPTIMIZER_PKG.gBSCSchema||'.'||l_origin_tables(j)
1131 || ' ADD ('||p_table.data(i).fieldName||')';
1132 BEGIN
1133 execute immediate l_stmt;
1134 EXCEPTION WHEN OTHERS THEN
1135 IF (SQLCODE=-12002 -- mv log does not exist
1136 OR SQLCODE=-1430 -- ORA-01430: column being added already exists in table
1137 OR SQLCODE=-12027) -- ORA-12027: duplicate filter column
1138 THEN
1139 null;
1140 ELSE
1141 bsc_mo_helper_pkg.writeTmp('Exception while adding measure to MV log '||l_stmt, FND_LOG.LEVEL_STATEMENT, true);
1142 RAISE;
1143 END IF;
1144 END;
1145 END IF;
1146 END LOOP;
1147 END IF;
1148 END LOOP;
1149 bsc_mo_helper_pkg.writeTmp('Completed Alter Tables zero data count= ', FND_LOG.LEVEL_STATEMENT, true);
1150 END;
1151
1152 /*
1153 AddPartitionCols
1154 */
1155 PROCEDURE addPartitionCols(
1156 p_cols IN OUT NOCOPY BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn)
1157 IS
1158 dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
1159 dbColumn_null BSC_METADATA_OPTIMIZER_PKG.clsDBColumn := null;
1160
1161 BEGIN
1162 -- batch column
1163 dbColumn := dbColumn_null;
1164 dbColumn.columnName := BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME;
1165 dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
1166 dbColumn.columnLength := 0;
1167 dbColumn.iskey := false;
1168 p_cols(p_cols.count) := dbColumn;
1169
1170 END;
1171
1172 --****************************************************************************
1173 -- CrearTabla : CreateOneTable
1174 -- DESCRIPTION:
1175 -- Creates thegiven table in the database.
1176 -- PARAMETERS:
1177 -- Tabla: Table. It is an object with all inFORmation about the table
1178 --****************************************************************************
1179 PROCEDURE CreateOneTable(
1180 l_table_rec BSC_METADATA_OPTIMIZER_PKG.clsTable,
1181 TableTBSName IN VARCHAR2,
1182 IndexTBSName IN VARCHAR2,
1183 p_table_type IN VARCHAR2) IS
1184
1185 Llave BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1186 Dato BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1187 colCampos BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn;
1188 dbColumn BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
1189 msg VARCHAR2(100);
1190 uv_name VARCHAR2(100);
1191 l_stmt VARCHAR2(1000);
1192 l_index1 NUMBER;
1193 l_index2 NUMBER;
1194 l_keys BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField;
1195 l_data BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField;
1196 l_error varchar2(1000);
1197 dimTable VARCHAR2(100);
1198 columnSize NUMBER;
1199 l_temp1 NUMBER;
1200 BEGIN
1201 IF (l_table_rec.isProductionTable) THEN
1202 return;
1203 END IF;
1204 l_keys := l_table_rec.keys;
1205 l_data := l_table_rec.data;
1206 sort_data_columns(l_data);
1207 IF l_table_rec.Type = 0 THEN
1208 --It is an input table
1209 --Create the table in the database
1210 colCampos.delete;
1211 --Keys
1212 l_index1 := l_keys.first;
1213 LOOP
1214 EXIT WHEN l_keys.count=0;
1215 dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;
1216 Llave := l_keys(l_index1);
1217 l_temp1 := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, Llave.keyName);
1218 dimTable := BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).name;
1219 dbColumn.columnName := Llave.keyName;
1220 dbColumn.columnType := GetDimKeyDataType(dimTable, 'USER_CODE', columnSize);
1221 dbColumn.columnLength := columnSize;
1222 dbColumn.isKey := TRUE;
1223 dbColumn.isTimeKey := FALSE;
1224 IF (colCampos.count >0) THEN
1225 colCampos(colCampos.last+1) := dbColumn;
1226 ELSE
1227 colCampos(0) := dbColumn;
1228 END IF;
1229 EXIT WHEN l_index1 = l_keys.last;
1230 l_index1 := l_keys.next(l_index1);
1231 END LOOP;
1232 --Year
1233 dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;
1234 AddYearPeriodType(colCampos, l_table_rec.periodicity, true, l_table_rec.type);
1235 --Data colunms
1236
1237 IF (l_data.count > 0) THEN
1238 l_index1 := l_data.first;
1239 END IF;
1240 LOOP
1241 EXIT WHEN l_data.count = 0;
1242 Dato := l_data (l_index1);
1243 dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;
1244 dbColumn.columnName := Dato.fieldName;
1245 dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
1246 dbColumn.columnLength := 0;
1247 dbColumn.isKey := FALSE;
1248 colCampos(colCampos.last+1) := dbColumn;
1249 EXIT WHEN l_index1 = l_data.last;
1250 l_index1 := l_data.next(l_index1);
1251 END LOOP;
1252 CreateTableInDB(
1253 l_table_rec.Name,
1254 colCampos,
1255 TableTBSName,
1256 IndexTBSName,
1257 l_table_rec,
1258 p_table_type);
1259 ELSE
1260 --It is a system table (base, temporal or summary)
1261 --BSC-MV Note: EDW logic need to be re-visisted IF in the future
1262 --EDW integration happens.EDW integration was never
1263 --released. I will apply the same logic even IF it is a EDW table
1264 --I will create only base tables.
1265 colCampos.delete;
1266 IF (l_keys.count>0) THEN
1267 l_index1 := l_keys.first;
1268 END IF;
1269 LOOP
1270 EXIT WHEN l_keys.count = 0;
1271 Llave := l_keys(l_index1);
1272 dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;
1273 l_temp1 := BSC_MO_HELPER_PKG.FindKeyIndex(BSC_METADATA_OPTIMIZER_PKG.gMasterTable, Llave.keyName);
1274 IF (l_temp1 = -1) THEN
1275 bsc_mo_helper_pkg.writeTmp('l_temp1=-1 in FindKeyIndex for key = '||Llave.keyName);
1276 END IF;
1277 dimTable := BSC_METADATA_OPTIMIZER_PKG.gMasterTable(l_temp1).name;
1278 dbColumn.columnName := Llave.keyName;
1279 dbColumn.columnTYpe := GetDimKeyDataType(dimTable, 'CODE', columnSize);
1280 dbColumn.columnLength := columnSize;
1281 dbColumn.isKey := TRUE;
1282 dbColumn.isTimeKey := FALSE;
1283 colCampos(colCampos.count) := dbColumn;
1284 EXIT WHEN l_index1 = l_keys.last;
1285 l_index1 := l_keys.next(l_index1);
1286 END LOOP;
1287 AddYearPeriodType(colCampos, l_table_rec.periodicity, false, l_table_rec.type);
1288 --BSC-MV Note: Need new column: PERIODICITY_ID
1289 --Periodicity_id
1290 --BSC AW- B tables for AW dont require Periodicity id
1291 IF BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV and l_table_rec.impl_type <> 2 THEN
1292 dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;
1293 dbColumn.columnName := 'PERIODICITY_ID';
1294 dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
1295 dbColumn.columnLength := 0;
1296 dbColumn.isKey := TRUE;
1297 dbColumn.isTimeKey := TRUE;
1298 colCampos(colCampos.count) := dbColumn;
1299 END IF;
1300
1301 IF (l_table_rec.impl_type = 2 ) THEN -- B table for AW
1302 AddAWColumns(colCampos, false);
1303 END IF;
1304 IF (l_data.count >0) THEN
1305 l_index1 := l_data.first;
1306 END IF;
1307 --Data columns
1308 LOOP
1309 EXIT WHEN l_data.count = 0;
1310 Dato := l_data (l_index1);
1311 dbColumn := bsc_mo_helper_pkg.new_clsDBColumn;
1312 dbColumn.columnName := Dato.fieldName;
1313 dbColumn.columnType := BSC_METADATA_OPTIMIZER_PKG.DTNumber;
1314 dbColumn.columnLength := 0;
1315 dbColumn.isKey := FALSE;
1316 colCampos(colCampos.last+1) := dbColumn;
1317 EXIT WHEN l_index1 = l_data.last;
1318 l_index1 := l_data.next(l_index1);
1319 END LOOP;
1320 IF (p_table_type='B') THEN
1321 AddPartitionCols(colCampos);
1322 END IF;
1323 CreateTableInDB (
1324 l_table_rec.name,
1325 colCampos,
1326 TableTBSName,
1327 IndexTBSName,
1328 l_table_rec,
1329 p_table_type);
1330 END IF;
1331 EXCEPTION WHEN OTHERS THEN
1332 l_error := sqlerrm;
1333 FND_FILE.put_line(FND_FILE.LOG, 'Exception in CreateOneTable:'||l_error);
1334 fnd_message.set_name ('BSC', 'BSC_TABLENAME_CREATION_FAILED');
1335 fnd_message.set_token('TABLE_NAME', l_table_rec.name);
1336 bsc_mo_helper_pkg.terminateWithMsg(fnd_message.get);
1337 raise;
1338 END;
1339
1340
1341 --****************************************************************************
1342 -- CrearTablasDB
1343 --
1344 -- DESCRIPTION:
1345 -- Create all system tables in the database.
1346 --****************************************************************************
1347 PROCEDURE CreateAllTables IS
1348 l_table_rec BSC_METADATA_OPTIMIZER_PKG.clsTable;
1349 l_index NUMBER;
1350 arrProjTables DBMS_SQL.VARCHAR2_TABLE;
1351 numProjTables NUMBER := 0;
1352 lTablesCount NUMBER := 0;
1353 l_counter NUMBER := 0;
1354
1355 l_varchar_table1 dbms_sql.varchar2_table;
1356 l_varchar_table2 dbms_sql.varchar2_table;
1357 BEGIN
1358 lTablesCount := BSC_METADATA_OPTIMIZER_PKG.gTables.count;
1359 IF (lTablesCount = 0 ) THEN
1360 RETURN;
1361 END IF;
1362
1363 IF (lTablesCount > g_parallel_threshold) THEN
1364 g_parallelize := true;
1365 select max_processes into g_max_buckets from fnd_concurrent_queues where concurrent_queue_name='STANDARD' and application_id=0;
1366 g_max_buckets := g_max_buckets/2 ; -- consume only 1/2 max processes
1367 -- each table can have two indexes
1368 g_bucket_size := ceil(lTablesCount*2/g_max_buckets);
1369 -- assuming
1370 BSC_MO_HELPER_PKG.writeTmp('Bucket size = '||lTablesCount||'*2/('||g_max_buckets||') ='||g_bucket_size);
1371 if g_max_buckets > 1 then
1372 CreateDDLTable;
1373 else
1374 g_parallelize := false;
1375 end if;
1376 END IF;
1377
1378
1379
1380 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1381 BSC_MO_HELPER_PKG.writeTmp('Inside CreateAllTables');
1382 END IF;
1383 --BSC-MV Note: From this implementation only base tables are created
1384 --in the database. Also, FOR some special cases summary tables are created
1385 --(Example FOR projection in kpi tables because of targets at dIFferent levels)
1386 --Also in this procedure we handle the case when it is running FOR upgrade
1387 l_index := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
1388
1389 LOOP
1390 l_counter := l_counter + 1;
1391 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1392 BSC_MO_HELPER_PKG.writeTmp('Table # :'||l_counter||' out of :'||BSC_METADATA_OPTIMIZER_PKG.gTables.count
1393 ||'... '||BSC_METADATA_OPTIMIZER_PKG.gTables(l_index).name||
1394 ' time is '||to_Char(sysdate, 'hh24:mi:ss'));
1395 END IF;
1396 l_table_rec := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index);
1397 IF (l_table_rec.isProductionTable AND l_table_rec.isProductionTableAltered) THEN
1398 add_columns_to_tables(l_table_rec);
1399 END IF;
1400
1401 IF l_table_rec.isProductionTable AND BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 1 And l_table_rec.upgradeFlag <> 1 THEN
1402 null;
1403 ELSIF l_table_rec.TYPE = 0 THEN
1404 --Input table
1405 IF BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change = 1 And l_table_rec.upgradeFlag = 1 THEN
1406 --In upgrade mode, the input table already exist, we cannot drop/create
1407 NULL;
1408 ELSE
1409 BSC_MO_HELPER_PKG.writeTmp('Calling CreateOneTable1 for '||l_table_rec.name);
1410 CreateOneTable(l_table_rec, BSC_METADATA_OPTIMIZER_PKG.gInputTableTbsName, BSC_METADATA_OPTIMIZER_PKG.gInputIndexTbsName, 'I');
1411 END IF;
1412 ELSIF BSC_MO_LOADER_CONFIG_PKG.isBasicTable(l_table_rec.name) THEN
1413 --Base table
1414 IF BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change = 1 and l_table_rec.isProductionTable THEN
1415 --In upgrade mode, we only need to add periodicity id to the table/index
1416 BSC_MO_HELPER_PKG.writeTmp('Calling AddPeriodicityId2 for '||l_table_rec.name);
1417 AddPeriodicityId (l_table_rec);
1418 ELSE
1419 CreateOneTable(l_table_rec, BSC_METADATA_OPTIMIZER_PKG.gBaseTableTbsName, BSC_METADATA_OPTIMIZER_PKG.gBaseIndexTbsName, 'B');
1420 BSC_MO_HELPER_PKG.writeTmp('Calling CreateOneTable3 for '||l_table_rec.name);
1421 END IF;
1422 ELSE
1423 --Summary table
1424 IF Not BSC_METADATA_OPTIMIZER_PKG.g_BSC_MV THEN
1425 BSC_MO_HELPER_PKG.writeTmp('Calling CreateOneTable4 for '||l_table_rec.name);
1426 CreateOneTable(l_table_rec, BSC_METADATA_OPTIMIZER_PKG.gSummaryTableTbsName, BSC_METADATA_OPTIMIZER_PKG.gSummaryIndexTbsName, 'S');
1427 ELSE
1428 BSC_MO_HELPER_PKG.writeTmp('MV architecture, Not Calling CreateOneTable5 for '||l_table_rec.name);
1429 --BSC-MV New architecture: None of the summary tables are needed.
1430 --The only tables we need to create are the tables created FOR projection
1431 IF l_table_rec.projectionTable is not null THEN
1432 -- Need to check this because one projection table corresponds
1433 -- to many summary tables (same level but dIFferent periodicities)
1434 IF Not bsc_mo_helper_pkg.searchStringExists(arrProjTables, arrProjTables.count, l_table_rec.projectionTable) THEN
1435 CreateProjTable(l_table_rec);
1436 arrProjTables(arrProjTables.count) := l_table_rec.projectionTable;
1437 numProjTables := numProjTables + 1;
1438 END IF;
1439 END IF;
1440
1441 IF BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change = 1 And l_table_rec.upgradeFlag = 1 THEN
1442 --In upgrade mode we need to drop the summary tables used
1443 --by production indicators
1444 BSC_MO_HELPER_PKG.dropTable(l_table_rec.Name);
1445 END IF;
1446 END IF;
1447 END IF;
1448 EXIT WHEN l_index = BSC_METADATA_OPTIMIZER_PKG.gTables.last;
1449 l_index := BSC_METADATA_OPTIMIZER_PKG.gTables.next(l_index);
1450 END LOOP;
1451 commit;
1452
1453 IF (g_parallelize) THEN -- tables need to be created by child processes
1454 spawn_child_processes;
1455 END IF;
1456
1457 bsc_mo_helper_pkg.writeTmp('updating properties in bsc_db_tables, count='||g_update_tables.count);
1458 FOR i IN 1..g_update_tables.count LOOP
1459 l_varchar_table1(i) := g_update_tables(i).table_name;
1460 l_varchar_table2(i) := g_update_tables(i).property;
1461 bsc_mo_helper_pkg.writeTmp('table_name='||l_varchar_table1(i)||', property='||l_varchar_table2(i));
1462 END LOOP;
1463 -- Update Table Properties
1464 FORALL i IN 1..g_update_tables.count
1465 UPDATE BSC_DB_TABLES
1466 SET PROPERTIES=PROPERTIES||l_varchar_table2(i)||BSC_DBGEN_STD_METADATA.BSC_PROPERTY_SEPARATOR
1467 WHERE table_name = l_varchar_table1(i);
1468
1469 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1470 BSC_MO_HELPER_PKG.writeTmp('Completed CreateAllTables');
1471 END IF;
1472 END ;
1473
1474
1475
1476 function get_child_job_status(
1477 p_job_status_table varchar2,
1478 p_object_name varchar2,
1479 p_id out nocopy dbms_sql.number_table,
1480 p_job_id out nocopy dbms_sql.number_table,
1481 p_status out nocopy dbms_sql.varchar2_table,
1482 p_message out nocopy dbms_sql.varchar2_table,
1483 p_number_jobs out nocopy number
1484 )return boolean is
1485 TYPE CurTyp IS REF CURSOR;
1486 cv CurTyp;
1487 l_stmt VARCHAR2(1000);
1488 Begin
1489 if p_object_name is null then
1490 l_stmt:='select id,job_id,status,message from '||p_job_status_table;
1491 else
1492 l_stmt:='select id,job_id,status,message from '||p_job_status_table||' where object_name=:1';
1493 end if;
1494 p_number_jobs:=1;
1495 if bsc_metadata_optimizer_pkg.g_debug then
1496 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1497 bsc_mo_helper_pkg.writeTmp(l_stmt||' '||p_object_name);
1498 END IF;
1499 end if;
1500 if p_object_name is null then
1501 open cv for l_stmt;
1502 else
1503 open cv for l_stmt using p_object_name;
1504 end if;
1505 loop
1506 fetch cv into p_id(p_number_jobs),p_job_id(p_number_jobs),
1507 p_status(p_number_jobs),p_message(p_number_jobs);
1508 exit when cv%notfound;
1509 p_number_jobs:=p_number_jobs+1;
1510 end loop;
1511 close cv;
1512 p_number_jobs:=p_number_jobs-1;
1513 if bsc_metadata_optimizer_pkg.g_debug then
1514 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1515 bsc_mo_helper_pkg.writeTmp('The job status');
1516 END IF;
1517 for i in 1..p_number_jobs loop
1518 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1519 bsc_mo_helper_pkg.writeTmp(p_id(i)||' '||p_job_id(i)||' '||p_status(i)||' '||p_message(i));
1520 END IF;
1521 end loop;
1522 end if;
1523 return true;
1524 Exception when others then
1525 g_status_message:=sqlerrm;
1526 bsc_mo_helper_pkg.writeTmp('Exception in get_child_job_status '||g_status_message, fnd_log.level_statement, TRUE);
1527 return false;
1528 End;
1529
1530
1531 function check_all_child_jobs(
1532 p_job_status_table varchar2,
1533 p_job_id dbms_sql.number_table,
1534 p_number_jobs number,
1535 p_object_name varchar2
1536 ) return boolean is
1537 l_id dbms_sql.number_table;
1538 l_job_id dbms_sql.number_table;
1539 l_status dbms_sql.varchar2_table;
1540 l_message dbms_sql.varchar2_table;
1541 l_number_jobs number;
1542 Begin
1543 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1544 bsc_mo_helper_pkg.writeTmp('Starting check_all_child_jobs');
1545 END IF;
1546
1547 if get_child_job_status(
1548 p_job_status_table,
1549 p_object_name,
1550 l_id,
1551 l_job_id,
1552 l_status,
1553 l_message,
1554 l_number_jobs)=false then
1555 return false;
1556 end if;
1557 for i in 1..l_number_jobs loop
1558 if l_status(i)='ERROR' then
1559 return false;
1560 end if;
1561 end loop;
1562 return true;
1563 Exception when others then
1564 g_status_message:=sqlerrm;
1565 bsc_mo_helper_pkg.writeTmp('Exception in check_all_child_jobs '||g_status_message, fnd_log.level_statement, TRUE);
1566 return false;
1567 End;
1568
1569
1570
1571 -- create the tables stored in bsc_tmp_ddl_table
1572 -- with bucket_id = pStripe
1573
1574
1575 PROCEDURE create_tables_spawned(
1576 Errbuf out NOCOPY Varchar2,
1577 Retcode out NOCOPY Varchar2,
1578 pStripe IN NUMBER,
1579 pTableName IN VARCHAR2) IS
1580 BEGIN
1581 g_ddl_table_name :=pTableName;
1582 fnd_file.put_names('gdb.log', 'gdb.out', null);
1583 create_tables_spawned(pStripe);
1584
1585 END;
1586
1587 procedure writelog(pmsg in varchar2) is
1588 begin
1589 fnd_file.put_line(FND_FILE.log, pmsg);
1590 end;
1591
1592 -- create the tables stored in bsc_tmp_mo_create_table
1593 -- with bucket_id = pStripe
1594
1595 PROCEDURE create_tables_spawned(pStripe IN NUMBER) IS
1596 TYPE CurTyp IS REF CURSOR;
1597 cv CurTyp;
1598 l_stmt VARCHAR2(100) := ' SELECT object_name, object_ddl, object_type FROM '|| g_ddl_table_name||' WHERE bucket_id = :1';
1599 l_object_name VARCHAR2(100);
1600 l_object_ddl VARCHAR2(4000);
1601 l_object_type VARCHAR2(100);
1602 l_error varchar2(4000);
1603 BEGIN
1604
1605 OPEN cv FOR l_stmt USING pStripe;
1606 LOOP
1607 FETCH cv INTO l_object_name, l_object_ddl, l_object_type;
1608 EXIT WHEN cv%NOTFOUND;
1609 BEGIN
1610 null;
1611 --BSC_MO_HELPER_PKG.Do_DDL('drop table '||l_table_name, ad_ddl.drop_table, l_table_name);
1612 EXCEPTION WHEN OTHERS THEN
1613 null;
1614 END;
1615
1616 BEGIN
1617 IF (l_object_type='TABLE') THEN
1618 BSC_MO_HELPER_PKG.Do_DDL(l_object_ddl, ad_ddl.create_table, l_object_name);
1619 ELSE
1620 BSC_MO_HELPER_PKG.Do_DDL(l_object_ddl, ad_ddl.create_index, l_object_name);
1621 END IF;
1622 writelog(l_object_type ||' '||l_object_name||' created successfully.');
1623 EXCEPTION WHEN OTHERS THEN
1624 l_error := sqlerrm;
1625 execute immediate 'UPDATE '||g_ddl_table_name||' set status = :1 where bucket_id = :2 and object_name = :3'
1626 using 'ERROR', pStripe, l_object_name;
1627 END;
1628 END LOOP;
1629 CLOSE cv;
1630 END;
1631
1632
1633 FUNCTION check_job_status(p_job_id number) RETURN varchar2 is
1634 TYPE CurTyp IS REF CURSOR;
1635 cv CurTyp;
1636 l_var number;
1637 l_stmt VARCHAR2(100);
1638 l_fail number;
1639 Begin
1640 l_stmt:='select 1, failures from all_jobs where job=:1';
1641 OPEN cv FOR l_stmt using p_job_id;
1642 fetch cv into l_var, l_fail;
1643 close cv;
1644 IF l_var=1 THEN -- still in process or failure
1645 IF (l_fail IS NULL ) THEN
1646 RETURN 'Y';--job running
1647 ELSE
1648 return 'ERROR';
1649 END IF;
1650 ELSE
1651 RETURN 'N';
1652 END IF;
1653 Exception when others THEN
1654 g_status_message:=sqlerrm;
1655 bsc_mo_helper_pkg.writeTmp('Exception in check_job_status '||g_status_message, fnd_log.level_statement, TRUE);
1656 RETURN null;
1657 END;
1658
1659 FUNCTION check_conc_process_status(p_conc_id number) RETURN varchar2 is
1660 l_phase varchar2(400);
1661 l_status varchar2(400);
1662 l_dev_phase varchar2(400);
1663 l_dev_status varchar2(400);
1664 l_message varchar2(4000);
1665 l_conc_id number;
1666 Begin
1667 l_conc_id:=p_conc_id;
1668 IF FND_CONCURRENT.get_request_status(l_conc_id,null,null,l_phase,l_status,
1669 l_dev_phase,l_dev_status,l_message)=FALSE THEN
1670 RETURN 'N';
1671 END IF;
1672 IF l_dev_phase is null or l_dev_phase='COMPLETE' THEN
1673 RETURN 'N';--there is no more this process
1674 ELSE
1675 RETURN 'Y';--still running
1676 END IF;
1677 Exception when others THEN
1678
1679 RETURN null;
1680 END;
1681
1682
1683 FUNCTION wait_on_jobs(
1684 p_job_id DBMS_SQL.NUMBER_TABLE,
1685 p_number_jobs number,
1686 p_sleep_time number,
1687 p_thread_type varchar2
1688 ) RETURN boolean is
1689 l_found boolean;
1690 l_status varchar2(2);
1691 l_job_status DBMS_SQL.VARCHAR2_TABLE;
1692 l_error VARCHAR2(1000);
1693
1694 l_start date := sysdate;
1695 Begin
1696
1697 IF p_number_jobs<=0 THEN
1698 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1699 bsc_mo_helper_pkg.writeTmp('Done wait_on_jobs, zero count');
1700 END IF;
1701 RETURN TRUE;
1702 END IF;
1703 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1704 bsc_mo_helper_pkg.writeTmp('Inside wait_on_jobs, jobs are ');
1705 END IF;
1706 bsc_mo_helper_pkg.write_this(p_job_id);
1707 FOR i in 1..p_job_id.count LOOP
1708 l_job_status(i):='Y';
1709 END LOOP;
1710 LOOP
1711 l_found:=FALSE;
1712 -- ARUN TBC
1713 IF ((sysdate - l_start)*86400 > 900) THEN -- 15 mins for testing
1714 return false;
1715 END IF;
1716 DBMS_LOCK.SLEEP(g_sleep_time); -- ignore p_sleep_time
1717 FOR i in 1..p_job_id.count LOOP
1718 IF l_job_status(i)='Y' THEN
1719 IF p_thread_type='JOB' THEN
1720 l_status:=check_job_status(p_job_id(i));
1721 ELSE
1722 l_status:=check_conc_process_status(p_job_id(i));
1723 END IF;
1724 bsc_mo_helper_pkg.writeTmp('status returned for '||p_job_id(i)||':'||l_status||' l_job_status='||l_job_status(i));
1725 IF l_status is null THEN
1726 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1727 bsc_mo_helper_pkg.writeTmp('Compl wait_on_jobs, returning false');
1728 END IF;
1729 RETURN FALSE;
1730 ELSIF l_status='Y' THEN
1731 l_found:=TRUE;
1732 ELSE
1733 IF l_job_status(i)='Y' THEN
1734 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1735 bsc_mo_helper_pkg.writeTmp('Job '||p_job_id(i)||' has terminated '||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1736 END IF;
1737 l_job_status(i):='N';
1738 ELSE -- error
1739 l_job_status(i) := 'ERROR';
1740 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1741 bsc_mo_helper_pkg.writeTmp('Compl wait_on_jobs, returning false');
1742 END IF;
1743 return false;
1744 END IF;
1745 END IF;
1746 END IF;
1747 END LOOP;
1748 IF l_found=FALSE THEN
1749 exit;
1750 END IF;
1751
1752 END LOOP;
1753 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1754 bsc_mo_helper_pkg.writeTmp('Compl wait_on_jobs, returning true');
1755 END IF;
1756 RETURN TRUE;
1757 Exception when others THEN
1758
1759 RETURN FALSE;
1760 END;
1761
1762 FUNCTION check_ora_job_parameters return boolean IS
1763 l_value NUMBER;
1764 TYPE CurTyp IS REF CURSOR;
1765 cv CurTyp;
1766 CURSOR cParam(pParam IN VARCHAR2) IS
1767 select value from v$parameter param
1768 where param.name = pParam;
1769
1770 BEGIN
1771
1772 OPEN cParam('job_queue_processes');
1773 FETCH cParam INTO l_value;
1774 CLOSE cParam;
1775 IF (l_value is null OR l_value = 0) THEN
1776 -- dont override system settings
1777 g_parallelize := false;
1778 return false;
1779 END IF;
1780 OPEN cParam('job_queue_interval');
1781 FETCH cParam INTO l_value;
1782 IF (l_value is null OR l_value = 0) THEN
1783 -- dont override system settings
1784 g_parallelize := false;
1785 return false;
1786 END IF;
1787 CLOSE cParam;
1788 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1789 bsc_mo_helper_pkg.writeTmp('Done with check_ora_job_parameters, returning true');
1790 END IF;
1791 return true;
1792 EXCEPTION WHEN OTHERS THEN
1793 bsc_mo_helper_pkg.writeTmp('Exception in check_ora_job_parameters : '||sqlerrm, fnd_log.level_statement, TRUE);
1794 return false;
1795 END;
1796
1797
1798
1799 PROCEDURE spawn_child_processes IS
1800
1801 l_try_serial boolean := false;
1802 l_job_id DBMS_SQL.NUMBER_TABLE;
1803 L_BSC_SHORT_NAME varchar2(10);
1804 l_sleep_time NUMBER := 90 ; -- 1.5 minutes
1805 errBuf VARCHAR2(100);
1806 retCode VARCHAR2(100);
1807 --L_NUMBER_JOBS NUMBER;
1808
1809 l_status boolean;
1810 p_job_status_table VARCHAR2(1000);
1811 l_error varchar2(1000);
1812 BEGIN
1813
1814
1815 /*
1816 we will go FOR active polling. this main session will sleep FOR g_sleep_time and THEN
1817 wake up and check the status of each of the jobs. IF they are done, we can THEN proceed.
1818 DBMS_JOB.SUBMIT(id,'test_pack_2.run_pack;')
1819 */
1820
1821 g_sleep_time := 5;-- check every 5 seconds
1822
1823 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1824 bsc_mo_helper_pkg.writeTmp('Inside spawn_child_processes, system time is '||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1825 END IF;
1826
1827 -- INsert into Database
1828 --forall and execute immediate not compatible with 8i
1829 FORALL i IN g_ddl_bucket_id.first..g_ddl_bucket_id.last
1830 execute immediate 'INSERT INTO '||g_ddl_table_name||'(bucket_id, object_name, object_ddl, object_type)
1831 VALUES (:1, :2, :3, :4)'
1832 USING g_ddl_bucket_id(i), g_ddl_object(i), g_ddl_object_ddl(i), g_ddl_object_type(i);
1833
1834 commit;
1835
1836 bsc_metadata_optimizer_pkg.g_debug := true;
1837 bsc_metadata_optimizer_pkg.gThreadType := 'JOB';
1838
1839 IF (fnd_global.CONC_REQUEST_ID <> -1) THEN
1840 bsc_metadata_optimizer_pkg.gThreadType := 'CONC';
1841 l_bsc_short_name:='BSC';
1842 ELSE
1843 IF (check_ora_job_parameters = false) THEN
1844 l_try_serial := true;
1845 END IF;
1846 END IF;
1847
1848 bsc_mo_helper_pkg.writeTmp(
1849 ' Type of thread='||bsc_metadata_optimizer_pkg.gThreadType);
1850
1851 IF l_try_serial THEN
1852 bsc_mo_helper_pkg.writeTmp('Single thread ');
1853 ELSE
1854 bsc_mo_helper_pkg.writeTmp('Launch multiple threads ('||g_buckets||'). ');
1855 END IF;
1856
1857
1858 -- buckets are striped starting with 1 sequentially
1859 FOR bucket_num in 1..g_buckets LOOP
1860
1861 l_job_id(bucket_num):=null;
1862 begin
1863 IF bsc_metadata_optimizer_pkg.gThreadType='CONC' THEN
1864 l_job_id(bucket_num):=FND_REQUEST.SUBMIT_REQUEST(
1865 application=>l_bsc_short_name,
1866 program=>g_conc_short_name,
1867 argument1=>bucket_num,
1868 argument2=>g_ddl_table_name);
1869 commit;
1870 bsc_mo_helper_pkg.writeTmp('Concurrent Request '||l_job_id(bucket_num)||' launched at '||
1871 to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1872 IF l_job_id(bucket_num)<=0 THEN
1873 l_try_serial:=TRUE;
1874 END IF;
1875 ELSIF l_try_serial = false THEN --not a concurrent program and job init.ora params ok
1876 bsc_mo_helper_pkg.writeTmp('Not a Concurrent program, trying DBMS JOBS');
1877 DBMS_JOB.SUBMIT(l_job_id(bucket_num),
1878 'BSC_MO_DB_PKG.create_tables_spawned('||bucket_num||');',
1879 sysdate + (10/86400)); -- next second
1880 commit;--this commit is very imp
1881 bsc_mo_helper_pkg.writeTmp(' submitted dbms_job : id is '||l_job_id(bucket_num));
1882 bsc_mo_helper_pkg.writeTmp('Job '||l_job_id(bucket_num)||' launched '||
1883 to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1884 IF l_job_id(bucket_num)<=0 THEN
1885 l_try_serial:=TRUE;
1886 END IF;
1887 END IF;
1888 exception when others THEN
1889 bsc_mo_helper_pkg.writeTmp('Error launching parallel slaves '||sqlerrm||'. Attempt serial load', fnd_log.level_statement, TRUE);
1890 l_try_serial:=TRUE;
1891 END;
1892
1893 IF l_try_serial THEN
1894 IF bsc_metadata_optimizer_pkg.g_debug THEN
1895 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1896 bsc_mo_helper_pkg.writeTmp('Attempt serial load');
1897 END IF;
1898 END IF;
1899 create_tables_spawned(bucket_num); -- serial
1900 END IF;
1901 END LOOP;
1902
1903
1904 IF (NOT l_try_serial) THEN
1905 --wait to make sure that all threads launched are complete.
1906 IF wait_on_jobs(
1907 l_job_id,
1908 g_buckets,
1909 l_sleep_time,
1910 bsc_metadata_optimizer_pkg.gThreadType)=FALSE THEN -- error
1911 l_status:=false;
1912 bsc_mo_helper_pkg.TerminateWithMsg('One or more spawned programs failed');
1913 raise bsc_metadata_optimizer_pkg.optimizer_exception;
1914 END IF;
1915
1916 /*IF l_status THEN
1917 --just to note. l_job_id is not used in check_all_child_jobs
1918
1919 IF check_all_child_jobs(p_job_status_table,l_job_id,
1920 g_buckets,null)=FALSE THEN
1921 l_status:=FALSE;
1922 RETURN;
1923 END IF;
1924 END IF;
1925 */
1926 END IF;
1927 bsc_mo_helper_pkg.do_ddl('drop table '||g_ddl_table_name, ad_ddl.drop_table, g_ddl_table_name);
1928
1929 IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1930 bsc_mo_helper_pkg.writeTmp('Compl spawn_child_processes, system time is '||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1931 END IF;
1932
1933 EXCEPTION WHEN OTHERS THEN
1934
1935 bsc_mo_helper_pkg.writeTmp('Exception in spawn_child_processes :'||sqlerrm, fnd_log.level_statement, TRUE);
1936 raise;
1937
1938 END;
1939
1940 END BSC_MO_DB_PKG;