[Home] [Help]
PACKAGE BODY: APPS.BSC_UPDATE_DIM
Source
1 PACKAGE BODY BSC_UPDATE_DIM AS
2 /* $Header: BSCDDIMB.pls 120.16.12010000.2 2008/08/11 09:33:25 sirukull ship $ */
3
4 /*===========================================================================+
5 | FUNCTION Any_Item_Changed_Any_Relation
6 +============================================================================*/
7 FUNCTION Any_Item_Changed_Any_Relation(
8 x_dimension_table IN VARCHAR2,
9 x_temp_table IN VARCHAR2,
10 x_relation_cols IN BSC_UPDATE_UTIL.t_array_of_varchar2,
11 x_num_relation_cols IN NUMBER
12 ) RETURN BOOLEAN IS
13
14 h_i NUMBER;
15 h_res BOOLEAN;
16
17 h_cond VARCHAR2(32700);
18 h_sql VARCHAR2(32700);
19 h_code NUMBER;
20
21 TYPE t_cursor IS REF CURSOR;
22 h_cursor t_cursor;
23
24 BEGIN
25
26 h_res := FALSE;
27 h_cond := NULL;
28
29 FOR h_i IN 1..x_num_relation_cols LOOP
30 IF h_cond IS NULL THEN
31 h_cond := 'd.'||x_relation_cols(h_i)||' <> t.'||x_relation_cols(h_i);
32 ELSE
33 h_cond := h_cond||' OR d.'||x_relation_cols(h_i)||' <> t.'||x_relation_cols(h_i);
34 END IF;
35 END LOOP;
36
37 h_sql := 'SELECT d.code'||
38 ' FROM '||x_dimension_table||' d, '||x_temp_table||' t'||
39 ' WHERE d.code = t.code AND ('||h_cond||')';
40
41 OPEN h_cursor FOR h_sql;
42 FETCH h_cursor INTO h_code;
43 IF h_cursor%FOUND THEN
44 h_res := TRUE;
45 END IF;
46 CLOSE h_cursor;
47
48 RETURN h_res;
49
50 END Any_Item_Changed_Any_Relation;
51
52
53 /*===========================================================================+
54 | FUNCTION Create_Dbi_Dim_Tables
55 +============================================================================*/
56 FUNCTION Create_Dbi_Dim_Tables(
57 x_error_msg IN OUT NOCOPY VARCHAR2
58 ) RETURN BOOLEAN IS
59
60 l_sql VARCHAR2(32000);
61 l_i NUMBER;
62 l_count NUMBER;
63
64 l_table_name VARCHAR2(50);
65 l_index_name VARCHAR2(50);
66 l_constraint_name VARCHAR2(50);
67 l_short_name VARCHAR2(50);
68 l_table_owner VARCHAR2(50);
69 l_date_tracked_dim VARCHAR2(5);
70
71 l_parent_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
72 l_src_parent_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
73 l_num_parent_columns NUMBER;
74
75 l_lst_parent_cols VARCHAR2(8000);
76 l_lst_cols_desc VARCHAR2(8000);
77 l_col_already_indexed EXCEPTION;
78 PRAGMA EXCEPTION_INIT(l_col_already_indexed, -01408);
79
80 BEGIN
81 --Fix bug#4600154: create tables/indexes in summary table/index tablespace instead of dimension table/index tablespace.
82
83 IF g_dbi_dim_tables_set THEN
84 RETURN TRUE;
85 END IF;
86
87 BSC_APPS.Init_Bsc_Apps;
88 Init_Dbi_Dim_Data;
89
90 l_table_owner := UPPER(BSC_APPS.bsc_apps_schema);
91
92 FOR l_i IN 1..g_dbi_dim_data.COUNT LOOP
93 l_table_name := g_dbi_dim_data(l_i).table_name;
94 l_short_name := g_dbi_dim_data(l_i).short_name;
95 l_date_tracked_dim := g_dbi_dim_data(l_i).date_tracked_dim;
96
97 IF l_table_name IS NOT NULL THEN
98 -- Get list of parents
99 l_num_parent_columns := Get_Dbi_Dim_Parent_Columns(l_short_name,
100 l_parent_columns,
101 l_src_parent_columns);
102
103
104 l_lst_cols_desc := 'USER_CODE VARCHAR2(400),CODE VARCHAR2(400)';
105 IF l_num_parent_columns > 0 THEN
106 l_lst_cols_desc := l_lst_cols_desc||', '||
107 BSC_UPDATE_UTIL.Make_Lst_Description(l_parent_columns, l_num_parent_columns, 'VARCHAR2(400)');
108 END IF;
109 IF l_date_tracked_dim = 'YES' THEN
110 l_lst_cols_desc := l_lst_cols_desc||', EFFECTIVE_START_DATE DATE, EFFECTIVE_END_DATE DATE';
111 END IF;
112
113 --remove
114 --if not BSC_UPDATE_UTIL.Drop_Table(l_table_name) then
115 --null;
116 --end if;
117 -- Create the table, if it does not exists
118 IF NOT BSC_APPS.Table_Exists(l_table_name) THEN
119 l_sql := 'CREATE TABLE '||l_table_name||' ('||l_lst_cols_desc||')'||
120 ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.summary_table_tbs_type)||
121 ' '||BSC_APPS.bsc_storage_clause;
122 if bsc_im_utils.g_debug then
123 write_to_log_file_n(l_sql);
124 end if;
125 BSC_APPS.Do_DDL(l_sql, AD_DDL.CREATE_TABLE, l_table_name);
126 END IF;
127
128 -- Create the mv log
129 SELECT COUNT(1) INTO l_count
130 FROM all_snapshot_logs
131 WHERE master = l_table_name AND log_owner = l_table_owner;
132 IF l_count = 0 THEN
133 -- mv log does ot exists
134
135 -- Create PK
136 SELECT COUNT(1) INTO l_count
137 FROM all_constraints
138 WHERE owner = l_table_owner AND constraint_type = 'P' AND table_name = l_table_name;
139 IF l_count = 0 THEN
140 -- PK does not exists
141 l_constraint_name := substr(l_table_name,1,29)||'P';
142 l_sql := 'ALTER TABLE '||l_table_name||
143 ' ADD CONSTRAINT '||l_constraint_name||' PRIMARY KEY(USER_CODE) RELY ENABLE NOVALIDATE';
144 if bsc_im_utils.g_debug then
145 write_to_log_file_n(l_sql);
146 end if;
147 BSC_APPS.Do_DDL(l_sql, AD_DDL.ALTER_TABLE, l_table_name);
148 END IF;
149
150 -- Create mv log
151 l_sql := 'CREATE MATERIALIZED VIEW LOG ON '||l_table_owner||'.'||l_table_name||
152 ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.summary_table_tbs_type)||
153 ' '||BSC_APPS.bsc_storage_clause||' WITH';
154 IF BSC_IM_UTILS.get_db_version = '9i' THEN
155 l_sql := l_sql||' SEQUENCE,';
156 END IF;
157 l_sql := l_sql||' PRIMARY KEY, ROWID(CODE';
158 IF l_num_parent_columns > 0 THEN
159 l_sql := l_sql||','||BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(l_parent_columns, l_num_parent_columns);
160 END IF;
161 l_sql := l_sql||')';
162 l_sql := l_sql||' INCLUDING NEW VALUES';
163 if bsc_im_utils.g_debug then
164 write_to_log_file_n(l_sql);
165 end if;
166 EXECUTE IMMEDIATE l_sql;
167 END IF;
168 -- Create the Unique Index
169 l_index_name := substr(l_table_name,1,29)||'U';
170 IF NOT BSC_APPS.Index_Exists(l_index_name) THEN
171 l_sql := 'CREATE UNIQUE INDEX '||l_index_name||
172 ' ON '||l_table_name||' (USER_CODE)'||
173 ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.summary_index_tbs_type)||
174 ' '||BSC_APPS.bsc_storage_clause;
175 if bsc_im_utils.g_debug then
176 write_to_log_file_n(l_sql);
177 end if;
178 begin
179 BSC_APPS.Do_DDL(l_sql, AD_DDL.CREATE_INDEX, l_index_name);
180 exception
181 when l_col_already_indexed then
182 null;
183 end;
184 END IF;
185 END IF;
186
187 -- Create denorm table for recursive dimensions
188 l_table_name := g_dbi_dim_data(l_i).denorm_table;
189 IF (g_dbi_dim_data(l_i).recursive_dim = 'YES') AND (l_table_name IS NOT NULL) THEN
190 l_lst_cols_desc := g_dbi_dim_data(l_i).parent_col||' VARCHAR2(400), '||
191 g_dbi_dim_data(l_i).child_col||' VARCHAR2(400), '||
192 g_dbi_dim_data(l_i).parent_level_col||' VARCHAR2(40)';
193 IF l_date_tracked_dim = 'YES' THEN
194 l_lst_cols_desc := l_lst_cols_desc||', EFFECTIVE_START_DATE DATE, EFFECTIVE_END_DATE DATE';
195 END IF;
196
197 IF NOT BSC_APPS.Table_Exists(l_table_name) THEN
198 l_sql := 'CREATE TABLE '||l_table_name||' ('||l_lst_cols_desc||')'||
199 ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.summary_table_tbs_type)||
200 ' '||BSC_APPS.bsc_storage_clause;
201 if bsc_im_utils.g_debug then
202 write_to_log_file_n(l_sql);
203 end if;
204 BSC_APPS.Do_DDL(l_sql, AD_DDL.CREATE_TABLE, l_table_name);
205 END IF;
206 -- Create the Index
207 l_index_name := substr(l_table_name,1,29)||'N';
208 IF NOT BSC_APPS.Index_Exists(l_index_name) THEN
209 l_sql := 'CREATE INDEX '||l_index_name||
210 ' ON '||l_table_name||' ('||g_dbi_dim_data(l_i).parent_col||')'||
211 ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.summary_index_tbs_type)||
212 ' '||BSC_APPS.bsc_storage_clause;
213 if bsc_im_utils.g_debug then
214 write_to_log_file_n(l_sql);
215 end if;
216 begin
217 BSC_APPS.Do_DDL(l_sql, AD_DDL.CREATE_INDEX, l_index_name);
218 exception
219 when l_col_already_indexed then
220 null;
221 end;
222 END IF;
223 END IF;
224 END LOOP;
225 g_dbi_dim_tables_set:=true;
226 RETURN TRUE;
227 EXCEPTION
228 WHEN OTHERS THEN
229 x_error_msg := SQLERRM;
230 RETURN FALSE;
231 END Create_Dbi_Dim_Tables;
232
233
234 --AW_INTEGRATION: new function
235 /*===========================================================================+
236 | FUNCTION Create_AW_Dim_Temp_Tables |
237 +============================================================================*/
238 FUNCTION Create_AW_Dim_Temp_Tables RETURN BOOLEAN IS
239
240 e_unexpected_error EXCEPTION;
241
242 h_table_name VARCHAR2(30);
243 h_table_columns BSC_UPDATE_UTIL.t_array_temp_table_cols;
244 h_num_columns NUMBER;
245 h_i NUMBER;
246 h_tablespace VARCHAR2(80);
247 h_idx_tablespace VARCHAR2(80);
248
249 BEGIN
250 h_tablespace := BSC_APPS.Get_Tablespace_Name(BSC_APPS.dimension_table_tbs_type);
251 h_idx_tablespace := BSC_APPS.Get_Tablespace_Name(BSC_APPS.dimension_index_tbs_type);
252
253 -- BSC_AW_DIM_DELETE
254 h_table_name := 'BSC_AW_DIM_DELETE';
255 h_table_columns.delete;
256 h_num_columns := 0;
257 h_num_columns := h_num_columns + 1;
258 h_table_columns(h_num_columns).column_name := 'DIM_LEVEL';
259 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
260 h_table_columns(h_num_columns).data_size := 300;
261 h_table_columns(h_num_columns).add_to_index := 'N';
262 h_num_columns := h_num_columns + 1;
263 h_table_columns(h_num_columns).column_name := 'DELETE_VALUE';
264 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
265 h_table_columns(h_num_columns).data_size := 400;
266 h_table_columns(h_num_columns).add_to_index := 'N';
267 -- Fix bug#5121276 this table needs to be created as a permanent table
268 IF NOT BSC_UPDATE_UTIL.Create_Permanent_Table(h_table_name, h_table_columns, h_num_columns,
269 h_tablespace, h_idx_tablespace) THEN
270 RAISE e_unexpected_error;
271 END IF;
272
273
274 -- BSC_AW_DIM_DATA
275 h_table_name := 'BSC_AW_DIM_DATA';
276 h_table_columns.delete;
277 h_num_columns := 0;
278 h_num_columns := h_num_columns + 1;
279 h_table_columns(h_num_columns).column_name := 'DIM_LEVEL';
280 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
281 h_table_columns(h_num_columns).data_size := 300;
282 h_table_columns(h_num_columns).add_to_index := 'N';
283 h_num_columns := h_num_columns + 1;
284 h_table_columns(h_num_columns).column_name := 'CODE';
285 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
286 h_table_columns(h_num_columns).data_size := 400;
287 h_table_columns(h_num_columns).add_to_index := 'N';
288 IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
289 RAISE e_unexpected_error;
290 END IF;
291
292 -- BSC_AW_TMP_DENORM
293 h_table_name := 'BSC_AW_TMP_DENORM';
294 h_table_columns.delete;
295 h_num_columns := 0;
296 h_num_columns := h_num_columns + 1;
297 h_table_columns(h_num_columns).column_name := 'CHILD_VALUE';
298 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
299 h_table_columns(h_num_columns).data_size := 400;
300 h_table_columns(h_num_columns).add_to_index := 'N';
301 h_num_columns := h_num_columns + 1;
302 h_table_columns(h_num_columns).column_name := 'PARENT_VALUE';
303 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
304 h_table_columns(h_num_columns).data_size := 400;
305 h_table_columns(h_num_columns).add_to_index := 'N';
306 IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
307 RAISE e_unexpected_error;
308 END IF;
309
310 -- BSC_AW_REC_DIM_HIER_CHANGE
311 h_table_name := 'BSC_AW_REC_DIM_HIER_CHANGE';
312 h_table_columns.delete;
313 h_num_columns := 0;
314 h_num_columns := h_num_columns + 1;
315 h_table_columns(h_num_columns).column_name := 'DIM_LEVEL';
316 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
317 h_table_columns(h_num_columns).data_size := 300;
318 h_table_columns(h_num_columns).add_to_index := 'N';
319 h_num_columns := h_num_columns + 1;
320 h_table_columns(h_num_columns).column_name := 'CHILD_VALUE';
321 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
322 h_table_columns(h_num_columns).data_size := 400;
323 h_table_columns(h_num_columns).add_to_index := 'N';
324 h_num_columns := h_num_columns + 1;
325 h_table_columns(h_num_columns).column_name := 'PARENT_VALUE';
326 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
327 h_table_columns(h_num_columns).data_size := 400;
328 h_table_columns(h_num_columns).add_to_index := 'N';
329 h_num_columns := h_num_columns + 1;
330 h_table_columns(h_num_columns).column_name := 'HIER_CHANGE_DATE';
331 h_table_columns(h_num_columns).data_type := 'DATE';
332 h_table_columns(h_num_columns).data_size := NULL;
333 h_table_columns(h_num_columns).add_to_index := 'N';
334 IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
335 RAISE e_unexpected_error;
336 END IF;
337
338 RETURN TRUE;
339
340 EXCEPTION
341 WHEN e_unexpected_error THEN
342 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),
343 x_source => 'BSC_UPDATE_DIM.Create_AW_Dim_Temp_Tables');
344 RETURN FALSE;
345
346 WHEN OTHERS THEN
347 BSC_MESSAGE.Add(x_message => SQLERRM,
348 x_source => 'BSC_UPDATE_DIM.Create_AW_Dim_Temp_Tables');
349 RETURN FALSE;
350 END Create_AW_Dim_Temp_Tables;
351
352
353 --LOCKING: new function
354 /*===========================================================================+
355 | FUNCTION Create_AW_Dim_Temp_Tables_AT
356 +============================================================================*/
357 FUNCTION Create_AW_Dim_Temp_Tables_AT RETURN BOOLEAN IS
358 PRAGMA AUTONOMOUS_TRANSACTION;
359 h_b BOOLEAN;
360 BEGIN
361 h_b := Create_AW_Dim_Temp_Tables;
362 commit; -- all autonomous transaction needs to commit
363 RETURN h_b;
364 END Create_AW_Dim_Temp_Tables_AT;
365
366
367 /*===========================================================================+
368 | FUNCTION Create_Dbi_Dim_Temp_Tables |
369 +============================================================================*/
370 FUNCTION Create_Dbi_Dim_Temp_Tables RETURN BOOLEAN IS
371
372 e_unexpected_error EXCEPTION;
373
374 h_table_name VARCHAR2(30);
375 h_table_columns BSC_UPDATE_UTIL.t_array_temp_table_cols;
376 h_num_columns NUMBER;
377 h_i NUMBER;
378 h_tablespace VARCHAR2(80);
379 h_idx_tablespace VARCHAR2(80);
380
381 BEGIN
382 h_tablespace := BSC_APPS.Get_Tablespace_Name(BSC_APPS.dimension_table_tbs_type);
383 h_idx_tablespace := BSC_APPS.Get_Tablespace_Name(BSC_APPS.dimension_index_tbs_type);
384
385 -- BSC_TMP_DBI_DIM
386 h_table_name := 'BSC_TMP_DBI_DIM';
387 h_table_columns.delete;
388 h_num_columns := 0;
389 h_num_columns := h_num_columns + 1;
390 h_table_columns(h_num_columns).column_name := 'USER_CODE';
391 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
392 h_table_columns(h_num_columns).data_size := 400;
393 h_table_columns(h_num_columns).add_to_index := 'Y';
394 h_num_columns := h_num_columns + 1;
395 h_table_columns(h_num_columns).column_name := 'CODE';
396 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
397 h_table_columns(h_num_columns).data_size := 400;
398 h_table_columns(h_num_columns).add_to_index := 'N';
399 FOR h_i IN 1..10 LOOP
400 h_num_columns := h_num_columns + 1;
401 h_table_columns(h_num_columns).column_name := 'PARENT_CODE'||h_i;
402 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
403 h_table_columns(h_num_columns).data_size := 400;
404 h_table_columns(h_num_columns).add_to_index := 'N';
405 END LOOP;
406 h_num_columns := h_num_columns + 1;
407 h_table_columns(h_num_columns).column_name := 'EFFECTIVE_START_DATE';
408 h_table_columns(h_num_columns).data_type := 'DATE';
409 h_table_columns(h_num_columns).data_size := NULL;
410 h_table_columns(h_num_columns).add_to_index := 'N';
411 h_num_columns := h_num_columns + 1;
412 h_table_columns(h_num_columns).column_name := 'EFFECTIVE_END_DATE';
413 h_table_columns(h_num_columns).data_type := 'DATE';
414 h_table_columns(h_num_columns).data_size := NULL;
415 h_table_columns(h_num_columns).add_to_index := 'N';
416 IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
417 RAISE e_unexpected_error;
418 END IF;
419
420 -- BSC_TMP_DBI_DIM_ADD
421 h_table_name := 'BSC_TMP_DBI_DIM_ADD';
422 h_table_columns.delete;
423 h_num_columns := 0;
424 h_num_columns := h_num_columns + 1;
425 h_table_columns(h_num_columns).column_name := 'USER_CODE';
426 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
427 h_table_columns(h_num_columns).data_size := 400;
428 h_table_columns(h_num_columns).add_to_index := 'Y';
429 IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
430 RAISE e_unexpected_error;
431 END IF;
432
433 -- BSC_TMP_DBI_DIM_DEL (Note this table has the same strucutre of the previouos table)
434 h_table_name := 'BSC_TMP_DBI_DIM_DEL';
435 IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
436 RAISE e_unexpected_error;
437 END IF;
438
439 -- BSC_OBJECT_REFRESH_LOG
440 -- This is a permanent table used to store the last update date of the dbi dimension tables
441 h_table_name := 'BSC_OBJECT_REFRESH_LOG';
442 h_table_columns.delete;
443 h_num_columns := 0;
444 h_num_columns := h_num_columns + 1;
445 h_table_columns(h_num_columns).column_name := 'OBJECT_NAME';
446 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
447 h_table_columns(h_num_columns).data_size := 800;
448 h_table_columns(h_num_columns).add_to_index := 'Y';
449 h_num_columns := h_num_columns + 1;
450 h_table_columns(h_num_columns).column_name := 'OBJECT_TYPE';
451 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
452 h_table_columns(h_num_columns).data_size := 80;
453 h_table_columns(h_num_columns).add_to_index := 'Y';
454 h_num_columns := h_num_columns + 1;
455 h_table_columns(h_num_columns).column_name := 'REFRESH_START_TIME';
456 h_table_columns(h_num_columns).data_type := 'DATE';
457 h_table_columns(h_num_columns).data_size := NULL;
458 h_table_columns(h_num_columns).add_to_index := 'N';
459 h_num_columns := h_num_columns + 1;
460 h_table_columns(h_num_columns).column_name := 'REFRESH_END_TIME';
461 h_table_columns(h_num_columns).data_type := 'DATE';
462 h_table_columns(h_num_columns).data_size := NULL;
463 h_table_columns(h_num_columns).add_to_index := 'N';
464 IF NOT BSC_UPDATE_UTIL.Create_Permanent_Table(h_table_name, h_table_columns, h_num_columns,
465 h_tablespace, h_idx_tablespace) THEN
466 RAISE e_unexpected_error;
467 END IF;
468
469 -- RECURSIVE_DIMS: new temp table
470 -- BSC_TMP_DNT
471 h_table_name := 'BSC_TMP_DNT';
472 h_table_columns.delete;
473 h_num_columns := 0;
474 h_num_columns := h_num_columns + 1;
475 h_table_columns(h_num_columns).column_name := 'PARENT_CODE';
476 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
477 h_table_columns(h_num_columns).data_size := 400;
478 h_table_columns(h_num_columns).add_to_index := 'N';
479 h_num_columns := h_num_columns + 1;
480 h_table_columns(h_num_columns).column_name := 'CODE';
481 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
482 h_table_columns(h_num_columns).data_size := 400;
483 h_table_columns(h_num_columns).add_to_index := 'N';
484 h_num_columns := h_num_columns + 1;
485 h_table_columns(h_num_columns).column_name := 'CHILD_LEVEL';
486 h_table_columns(h_num_columns).data_type := 'NUMBER';
487 h_table_columns(h_num_columns).data_size := NULL;
488 h_table_columns(h_num_columns).add_to_index := 'N';
489 h_num_columns := h_num_columns + 1;
490 h_table_columns(h_num_columns).column_name := 'PARENT_LEVEL';
491 h_table_columns(h_num_columns).data_type := 'NUMBER';
492 h_table_columns(h_num_columns).data_size := NULL;
493 h_table_columns(h_num_columns).add_to_index := 'N';
494 IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
495 RAISE e_unexpected_error;
496 END IF;
497
498 RETURN TRUE;
499
500 EXCEPTION
501 WHEN e_unexpected_error THEN
502 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),
503 x_source => 'BSC_UPDATE_DIM.Create_Dbi_Dim_Temp_Tables');
504 RETURN FALSE;
505
506 WHEN OTHERS THEN
507 BSC_MESSAGE.Add(x_message => SQLERRM,
508 x_source => 'BSC_UPDATE_DIM.Create_Dbi_Dim_Temp_Tables');
509 RETURN FALSE;
510 END Create_Dbi_Dim_Temp_Tables;
511
512
513 /*===========================================================================+
514 | FUNCTION Delete_Codes_Cascade
515 +============================================================================*/
516 FUNCTION Delete_Codes_Cascade(
517 x_dim_table IN VARCHAR2,
518 x_deleted_codes IN BSC_UPDATE_UTIL.t_array_of_number,
519 x_num_deleted_codes IN NUMBER
520 ) RETURN BOOLEAN IS
521
522 e_unexpected_error EXCEPTION;
523
524 h_sql VARCHAR2(32700);
525 TYPE t_cursor IS REF CURSOR;
526 h_cursor t_cursor;
527
528 CURSOR c_dim_info (p_level_table_name VARCHAR2) IS
529 SELECT dim_level_id, level_pk_col
530 FROM bsc_sys_dim_levels_b
531 WHERE level_table_name = p_level_table_name;
532
533 h_dim_level_id bsc_sys_dim_levels_b.dim_level_id%TYPE;
534 h_level_pk_col bsc_sys_dim_levels_b.level_pk_col%TYPE;
535
536 CURSOR c_child_mn (p_dim_level_id NUMBER, p_relation_type NUMBER) IS
537 SELECT r.relation_col, t.level_pk_col
538 FROM bsc_sys_dim_level_rels r, bsc_sys_dim_levels_b t
539 WHERE t.dim_level_id = r.parent_dim_level_id AND
540 r.dim_level_id = p_dim_level_id AND r.relation_type = p_relation_type;
541
542 h_mn_dim_table bsc_sys_dim_level_rels.relation_col%TYPE;
543 h_mn_level_pk_col bsc_sys_dim_levels_b.level_pk_col%TYPE;
544
545 CURSOR c_child (p_parent_id NUMBER, p_relation_type NUMBER) IS
546 SELECT t.level_table_name
547 FROM bsc_sys_dim_levels_b t, bsc_sys_dim_level_rels r
548 WHERE t.dim_level_id = r.dim_level_id AND
549 r.parent_dim_level_id = p_parent_id AND r.relation_type = p_relation_type;
550
551 h_child_dim_table bsc_sys_dim_levels_b.level_table_name%TYPE;
552
553 h_condition VARCHAR2(32700);
554 h_i NUMBER;
555
556 h_deleted_codes BSC_UPDATE_UTIL.t_array_of_number;
557
558 -- BSC-BIS-DIMENSIONS
559 -- MN dimension can be created in BSC to store MN relations between BIS dimensions.
560 -- To spport NUMBER or VARCHAR2 I will change the type of this arrays to varchar2.
561 h_deleted_codes1 BSC_UPDATE_UTIL.t_array_of_varchar2;
562 h_deleted_codes2 BSC_UPDATE_UTIL.t_array_of_varchar2;
563 h_num_deleted_codes NUMBER;
564
565 h_code NUMBER;
566 h_code1 NUMBER;
567 h_code2 NUMBER;
568
569 -- AW_INTEGRATION: new varaibles
570 h_dim_level_list dbms_sql.varchar2_table;
571
572 BEGIN
573 h_num_deleted_codes := 0;
574
575 -- Get info of the dimension table
576 -- OPEN c_dim_info FOR c_dim_info_sql USING x_dim_table;
577 OPEN c_dim_info(x_dim_table);
578 FETCH c_dim_info INTO h_dim_level_id, h_level_pk_col;
579 IF c_dim_info%NOTFOUND THEN
580 RAISE e_unexpected_error;
581 END IF;
582 CLOSE c_dim_info;
583
584 -- Delete from all system tables rows for deleted values
585 -- BSC-MV Note: There is no need to delete data from summary tables
586 -- because the affected indicators will be re-calculated later.
587 -- It is necessary to delete only from base tables.
588 -- This logic is valid for both architectures.
589
590 h_condition := BSC_APPS.Get_New_Big_In_Cond_Number(1, h_level_pk_col);
591 FOR h_i IN 1..x_num_deleted_codes LOOP
592 BSC_APPS.Add_Value_Big_In_Cond(1, x_deleted_codes(h_i));
593 END LOOP;
594
595 IF NOT Delete_Key_Values_In_Tables(h_level_pk_col, h_condition) THEN
596 RAISE e_unexpected_error;
597 END IF;
598
599 -- Delete the items from the mn-child dimension tables
600 --OPEN c_child_mn FOR c_child_mn_sql USING h_dim_level_id, 2;
601 OPEN c_child_mn(h_dim_level_id, 2);
602 FETCH c_child_mn INTO h_mn_dim_table, h_mn_level_pk_col;
603 WHILE c_child_mn%FOUND LOOP
604 h_condition := BSC_APPS.Get_New_Big_In_Cond_Number(1, h_level_pk_col);
605 FOR h_i IN 1..x_num_deleted_codes LOOP
606 BSC_APPS.Add_Value_Big_In_Cond(1, x_deleted_codes(h_i));
607 END LOOP;
608
609 h_sql := 'SELECT '||h_level_pk_col||', '||h_mn_level_pk_col||
610 ' FROM '||h_mn_dim_table||
611 ' WHERE '||h_condition;
612
613 h_num_deleted_codes := 0;
614
615 OPEN h_cursor FOR h_sql;
616 FETCH h_cursor INTO h_code1, h_code2;
617 WHILE h_cursor%FOUND LOOP
618 h_num_deleted_codes := h_num_deleted_codes + 1;
619 h_deleted_codes1(h_num_deleted_codes) := h_code1;
620 h_deleted_codes2(h_num_deleted_codes) := h_code2;
621
622 FETCH h_cursor INTO h_code1, h_code2;
623 END LOOP;
624 CLOSE h_cursor;
625
626 IF h_num_deleted_codes > 0 THEN
627 IF NOT Delete_Codes_CascadeMN(h_mn_dim_table,
628 h_level_pk_col,
629 h_mn_level_pk_col,
630 h_deleted_codes1,
631 h_deleted_codes2,
632 h_num_deleted_codes) THEN
633 RAISE e_unexpected_error;
634 END IF;
635 END IF;
636
637 FETCH c_child_mn INTO h_mn_dim_table, h_mn_level_pk_col;
638 END LOOP;
639 CLOSE c_child_mn;
640
641 -- Delete the items from the child dimension tables
642 --BSC-BIS-DIMENSIONS: A BSC dimension CANNOT be parent of a BIS dimension. So no need change here.
643
644 --OPEN c_child FOR c_child_sql USING h_dim_level_id, 1;
645 OPEN c_child(h_dim_level_id, 1);
646 FETCH c_child INTO h_child_dim_table;
647 WHILE c_child%FOUND LOOP
648 h_condition := BSC_APPS.Get_New_Big_In_Cond_Number(1, h_level_pk_col);
649 FOR h_i IN 1..x_num_deleted_codes LOOP
650 BSC_APPS.Add_Value_Big_In_Cond(1, x_deleted_codes(h_i));
651 END LOOP;
652
653 h_sql := 'SELECT DISTINCT CODE FROM '||h_child_dim_table||
654 ' WHERE '||h_condition;
655
656 h_num_deleted_codes := 0;
657
658 OPEN h_cursor FOR h_sql;
659 FETCH h_cursor INTO h_code;
660 WHILE h_cursor%FOUND LOOP
661 h_num_deleted_codes := h_num_deleted_codes + 1;
662 h_deleted_codes(h_num_deleted_codes) := h_code;
663
664 FETCH h_cursor INTO h_code;
665 END LOOP;
666 CLOSE h_cursor;
667
668 IF h_num_deleted_codes > 0 THEN
669 IF NOT Delete_Codes_Cascade(h_child_dim_table,
670 h_deleted_codes,
671 h_num_deleted_codes) THEN
672 RAISE e_unexpected_error;
673 END IF;
674 -- AW_INTEGRATION: If the dimension is used by any AW indicator, we need to call the
675 -- AW API to re-load child dimension into AW world.
676 IF Dimension_Used_In_AW_Kpi(h_child_dim_table) THEN
677 h_dim_level_list.delete;
678 h_dim_level_list(1) := h_child_dim_table;
679 bsc_aw_load.load_dim(
680 p_dim_level_list => h_dim_level_list,
681 p_options => 'DEBUG LOG'
682 );
683 END IF;
684 END IF;
685
686 FETCH c_child INTO h_child_dim_table;
687 END LOOP;
688 CLOSE c_child;
689
690 -- Delete records from the dimension table
691 h_condition := BSC_APPS.Get_New_Big_In_Cond_Number (1, 'CODE');
692 FOR h_i IN 1..x_num_deleted_codes LOOP
693 BSC_APPS.Add_Value_Big_In_Cond(1, x_deleted_codes(h_i));
694
695 -- AW_INTEGRATION: Need to insert the deleted codes into BSC_AW_DIM_DELETE table
696 IF Dimension_Used_In_AW_Kpi(x_dim_table) THEN
697 Insert_AW_Delete_Value(x_dim_table, x_deleted_codes(h_i));
698 END IF;
699 END LOOP;
700
701 h_sql := 'DELETE FROM '||x_dim_table||
702 ' WHERE '||h_condition;
703 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
704
705 RETURN TRUE;
706
707 EXCEPTION
708 WHEN e_unexpected_error THEN
709 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DIMREC_DELETE_FAILED'),
710 x_source => 'BSC_UPDATE_BASE.Delete_Codes_Cascade');
711 RETURN FALSE;
712
713 WHEN OTHERS THEN
714 BSC_MESSAGE.Add(x_message => SQLERRM,
715 x_source => 'BSC_UPDATE_DIM.Delete_Codes_Cascade');
716 RETURN FALSE;
717
718 END Delete_Codes_Cascade;
719
720
721 /*===========================================================================+
722 | FUNCTION Delete_Codes_CascadeMN
723 +============================================================================*/
724 FUNCTION Delete_Codes_CascadeMN(
725 x_dim_table IN VARCHAR2,
726 x_key_column1 IN VARCHAR2,
727 x_key_column2 IN VARCHAR2,
728 x_deleted_codes1 IN BSC_UPDATE_UTIL.t_array_of_varchar2,
729 x_deleted_codes2 IN BSC_UPDATE_UTIL.t_array_of_varchar2,
730 x_num_deleted_codes IN NUMBER
731 ) RETURN BOOLEAN IS
732
733 h_condition VARCHAR2(32700);
734 h_i NUMBER;
735
736 TYPE t_cursor IS REF CURSOR;
737
738 -- BSC-MV Note: Change query to get only base tables. This logic is valid for both architectures
739 CURSOR c_system_tables (p_col_name1 VARCHAR2, p_col_name2 VARCHAR2,
740 p_col_type VARCHAR2, p_table_type NUMBER, p_count NUMBER) IS
741 SELECT DISTINCT bt.table_name
742 FROM (SELECT DISTINCT table_name
743 FROM bsc_db_tables_rels
744 WHERE source_table_name IN (
745 SELECT table_name
746 FROM bsc_db_tables
747 WHERE table_type = p_table_type)
748 ) bt,
749 bsc_db_tables_cols c
750 WHERE bt.table_name = c.table_name AND
751 (c.column_name = p_col_name1 OR
752 c.column_name = p_col_name2) AND
753 c.column_type = p_col_type
754 GROUP BY bt.table_name
755 HAVING COUNT(*) = p_count;
756
757 h_column_type_p VARCHAR2(1);
758
759 h_system_table VARCHAR2(30);
760 h_sql VARCHAR2(32700);
761
762 -- ENH_B_TABLES_PERF: new variable
763 h_proj_tbl_name VARCHAR2(30);
764
765 BEGIN
766
767 h_column_type_p := 'P';
768
769 h_condition := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, x_key_column1||'||''-''||'||x_key_column2);
770
771 FOR h_i IN 1..x_num_deleted_codes LOOP
772 BSC_APPS.Add_Value_Big_In_Cond(1, x_deleted_codes1(h_i)||'-'||x_deleted_codes2(h_i));
773 END LOOP;
774
775 -- Delete from system tables
776 -- BSC-MV Note: There is no need to delete data from summary tables
777 -- because the affected indicators will be re-calculated later.
778 -- It is necessary to delete only from base tables.
779 -- This logic is valid for both architectures.
780 OPEN c_system_tables(x_key_column1, x_key_column2, h_column_type_p, 0, 2);
781 FETCH c_system_tables INTO h_system_table;
782 WHILE c_system_tables%FOUND LOOP
783 h_sql := 'DELETE FROM '||h_system_table||
784 ' WHERE '||h_condition;
785 --Fix bug#5060236 B table may not exists, GDB may have fail in the middle
786 -- We can ignore error if the table does not exists
787 BEGIN BSC_UPDATE_UTIL.Execute_Immediate(h_sql); EXCEPTION WHEN OTHERS THEN NULL; END;
788
789 -- ENH_B_TABLES_PERF: with the new strategy the B table may have a projection table.
790 -- We need to delete rows from the projection table too.
791 h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_system_table);
792 IF h_proj_tbl_name IS NOT NULL THEN
793 h_sql := 'DELETE FROM '||h_proj_tbl_name||
794 ' WHERE '||h_condition;
795 --Fix bug#5060236 B table may not exists, GDB may have fail in the middle
796 -- We can ignore error if the table does not exists
797 BEGIN BSC_UPDATE_UTIL.Execute_Immediate(h_sql); EXCEPTION WHEN OTHERS THEN NULL; END;
798 END IF;
799
800 FETCH c_system_tables INTO h_system_table;
801 END LOOP;
802 CLOSE c_system_tables;
803
804 -- Delete from dimension table
805 h_sql := 'DELETE FROM '||x_dim_table||
806 ' WHERE '||h_condition;
807 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
808
809 RETURN TRUE;
810
811 EXCEPTION
812 WHEN OTHERS THEN
813 BSC_MESSAGE.Add(x_message => SQLERRM,
814 x_source => 'BSC_UPDATE_DIM.Delete_Codes_CascadeMN');
815 RETURN FALSE;
816
817 END Delete_Codes_CascadeMN;
818
819
820 /*===========================================================================+
821 | FUNCTION Delete_Key_Values_In_Tables
822 +============================================================================*/
823 FUNCTION Delete_Key_Values_In_Tables(
824 x_level_pk_col IN VARCHAR2,
825 x_condition IN VARCHAR2
826 ) RETURN BOOLEAN IS
827
828 h_sql VARCHAR2(32700);
829
830 TYPE t_cursor IS REF CURSOR;
831
832 -- BSC-MV Note: Change query to get only base tables. This logic is valid for both architectures
833 CURSOR c_affected_tables (p_col_name VARCHAR2, p_col_type VARCHAR2, p_table_type NUMBER) IS
834 SELECT DISTINCT bt.table_name
835 FROM (SELECT DISTINCT table_name
836 FROM bsc_db_tables_rels
837 WHERE source_table_name IN (
838 SELECT table_name
839 FROM bsc_db_tables
840 WHERE table_type = p_table_type)
841 ) bt,
842 bsc_db_tables_cols c
843 WHERE bt.table_name = c.table_name AND
844 c.column_name = p_col_name AND
845 c.column_type = p_col_type;
846
847 h_column_type_p VARCHAR2(1);
848 h_table_name VARCHAR2(30);
849
850 -- ENH_B_TABLES_PERF: new variable
851 h_proj_tbl_name VARCHAR2(30);
852
853 BEGIN
854
855 h_column_type_p := 'P';
856
857 OPEN c_affected_tables(x_level_pk_col, h_column_type_p, 0);
858 FETCH c_affected_tables INTO h_table_name;
859 WHILE c_affected_tables%FOUND LOOP
860 h_sql := 'DELETE FROM '||h_table_name||
861 ' WHERE '||x_condition;
862 --Fix bug#5060236 B table may not exists, GDB may have fail in the middle
863 -- We can ignore error if the table does not exists
864 BEGIN BSC_UPDATE_UTIL.Execute_Immediate(h_sql); EXCEPTION WHEN OTHERS THEN NULL; END;
865
866 -- ENH_B_TABLES_PERF: with the new strategy the B table may have a projection table.
867 -- We need to delete rows from the projection table too
868 h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_table_name);
869 IF h_proj_tbl_name IS NOT NULL THEN
870 h_sql := 'DELETE FROM '||h_proj_tbl_name||
871 ' WHERE '||x_condition;
872 --Fix bug#5060236 B table may not exists, GDB may have fail in the middle
873 -- We can ignore error if the table does not exists
874 BEGIN BSC_UPDATE_UTIL.Execute_Immediate(h_sql); EXCEPTION WHEN OTHERS THEN NULL; END;
875 END IF;
876
877 FETCH c_affected_tables INTO h_table_name;
878 END LOOP;
879 CLOSE c_affected_tables;
880
881 COMMIT;
882
883 RETURN TRUE;
884
885 EXCEPTION
886 WHEN OTHERS THEN
887 BSC_MESSAGE.Add(x_message => SQLERRM,
888 x_source => 'BSC_UPDATE_DIM.Delete_Key_Values_In_Tables');
889 RETURN FALSE;
890
891 END Delete_Key_Values_In_Tables;
892
893
894 /*===========================================================================+
895 | FUNCTION Denorm_Eni_Item_Vbh_Cat
896 +============================================================================*/
897 FUNCTION Denorm_Eni_Item_Vbh_Cat RETURN BOOLEAN IS
898
899 l_sql VARCHAR2(32000);
900 l_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
901 l_yes VARCHAR2(3);
902
903 TYPE t_cursor IS REF CURSOR;
904 l_cursor t_cursor;
905
906 l_num_ids_this_level NUMBER;
907 l_current_level NUMBER;
908 l_id NUMBER;
909 l_where_level VARCHAR2(32000);
910
911 l_dim_obj_type VARCHAR2(80);
912
913 -- AW_INTEGRATION: new varaibles
914 l_level_table_name VARCHAR2(30);
915 l_dim_for_aw_kpi BOOLEAN;
916 l_dim_short_name VARCHAR2(100);
917
918 BEGIN
919
920 l_dim_obj_type := 'DIM';
921 l_yes := 'Y';
922 l_dim_short_name := 'ENI_ITEM_VBH_CAT';
923
924 Get_Dbi_Dim_Data(l_dim_short_name, l_dbi_dim_data);
925
926 -- AW_INTEGRATION: We need to know the level_table_name given the short name
927 SELECT level_table_name
928 INTO l_level_table_name
929 FROM bsc_sys_dim_levels_b
930 WHERE short_name = l_dim_short_name;
931
932 -- AW_INTEGRATION: We need to knowif the dimension is used by an AW indicator
933 l_dim_for_aw_kpi := Dimension_Used_In_AW_Kpi(l_level_table_name);
934
935 IF l_dbi_dim_data.denorm_table IS NULL THEN
936 -- there is no denorm table
937 RETURN TRUE;
938 END IF;
939
940 IF (l_dbi_dim_data.top_n_levels IS NULL) OR (l_dbi_dim_data.top_n_levels = 0) THEN
941 -- we do not need to denormalize this table
942 RETURN TRUE;
943 END IF;
944
945 -- We are going to compare the last update date of the source object with
946 -- the refresh_end_time of the dimension table to know if it is necessary to
947 -- refresh it or not.
948 IF NOT Need_Refresh_Dbi_Table(l_dbi_dim_data.denorm_table, l_dbi_dim_data.denorm_source_to_check) THEN
949 -- NO need to refresh
950 RETURN TRUE;
951 END IF;
952
953 -- Udpate REFRESH_START_TIME in BSC_OBJECT_REFRESH_LOG for this table
954 l_sql := 'UPDATE bsc_object_refresh_log'||
955 ' SET refresh_start_time = SYSDATE, refresh_end_time = NULL'||
956 ' WHERE object_name = :1 AND object_type = :2';
957 EXECUTE IMMEDIATE l_sql USING l_dbi_dim_data.denorm_table, l_dim_obj_type;
958 IF SQL%NOTFOUND THEN
959 l_sql := 'INSERT INTO bsc_object_refresh_log'||
960 ' (object_name, object_type, refresh_start_time, refresh_end_time)'||
961 ' VALUES (:1, :2, SYSDATE, NULL)';
962 EXECUTE IMMEDIATE l_sql USING l_dbi_dim_data.denorm_table, l_dim_obj_type;
963 END IF;
964 COMMIT;
965
966 -- AW_INTEGRATION: We need to save the current denorm table data int temp table BSC_AW_TMP_DENORM
967 IF l_dim_for_aw_kpi THEN
968 BSC_UPDATE_UTIL.Execute_Immediate('DELETE BSC_AW_TMP_DENORM');
969 l_sql := 'INSERT INTO BSC_AW_TMP_DENORM (CHILD_VALUE, PARENT_VALUE)'||
970 ' SELECT '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_col||
971 ' FROM '||l_dbi_dim_data.denorm_table;
972 EXECUTE IMMEDIATE l_sql;
973 COMMIT;
974 END IF;
975
976 BSC_UPDATE_UTIL.Truncate_Table(l_dbi_dim_data.denorm_table);
977
978 FOR l_current_level IN 1..l_dbi_dim_data.top_n_levels LOOP
979 l_num_ids_this_level := 0;
980
981 IF l_current_level = 1 THEN
982 -- First level (top_level)
983 l_sql := 'SELECT DISTINCT '||l_dbi_dim_data.parent_col_src||
984 ' FROM '||l_dbi_dim_data.denorm_src_object||
985 ' WHERE top_node_flag = :1';
986 OPEN l_cursor FOR l_sql USING l_yes;
987 ELSE
988 l_sql := 'SELECT DISTINCT imm_child_id'||
989 ' FROM '||l_dbi_dim_data.denorm_src_object||
990 ' WHERE '||l_dbi_dim_data.parent_col_src||' <> imm_child_id'||
991 ' AND ('||l_where_level||')';
992 OPEN l_cursor FOR l_sql;
993 END IF;
994
995 l_where_level := BSC_APPS.Get_New_Big_In_Cond_Number(l_current_level, l_dbi_dim_data.parent_col_src);
996
997 LOOP
998 FETCH l_cursor INTO l_id;
999 EXIT WHEN l_cursor%NOTFOUND;
1000 BSC_APPS.Add_Value_Big_In_Cond(l_current_level, l_id);
1001 l_num_ids_this_level := l_num_ids_this_level + 1;
1002 END LOOP;
1003 CLOSE l_cursor;
1004
1005 IF l_num_ids_this_level = 0 THEN
1006 -- There is not items for this level, then no reason to continue...
1007 EXIT;
1008 END IF;
1009
1010 l_sql := 'INSERT INTO '||l_dbi_dim_data.denorm_table||' ('||
1011 l_dbi_dim_data.parent_col||', '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_level_col||')'||
1012 ' SELECT '||l_dbi_dim_data.parent_col_src||', '||l_dbi_dim_data.child_col_src||', :1'||
1013 ' FROM '||l_dbi_dim_data.denorm_src_object||
1014 ' WHERE '||l_where_level;
1015 EXECUTE IMMEDIATE l_sql USING l_current_level;
1016 END LOOP;
1017
1018 -- AW_INTEGRATION: Need to insert BSC_AW_TMP_DENORM MINUS l_dbi_dim_data.denorm_table
1019 -- into BSC_AW_REC_DIM_HIER_CHANGE
1020 IF l_dim_for_aw_kpi THEN
1021 l_sql := 'INSERT INTO BSC_AW_REC_DIM_HIER_CHANGE (DIM_LEVEL, CHILD_VALUE, PARENT_VALUE)'||
1022 ' SELECT :1, CHILD_VALUE, PARENT_VALUE FROM BSC_AW_TMP_DENORM'||
1023 ' MINUS '||
1024 ' SELECT :2, '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_col||
1025 ' FROM '||l_dbi_dim_data.denorm_table;
1026 EXECUTE IMMEDIATE l_sql USING l_level_table_name, l_level_table_name;
1027 END IF;
1028
1029 COMMIT;
1030
1031 -- Udpate REFRESH_END_TIME in BSC_OBJECT_REFRESH_LOG for this table
1032 l_sql := 'UPDATE bsc_object_refresh_log'||
1033 ' SET refresh_end_time = SYSDATE'||
1034 ' WHERE object_name = :1 AND object_type = :2';
1035 EXECUTE IMMEDIATE l_sql USING l_dbi_dim_data.denorm_table, l_dim_obj_type;
1036 COMMIT;
1037
1038 RETURN TRUE;
1039
1040 EXCEPTION
1041 WHEN OTHERS THEN
1042 BSC_MESSAGE.Add(x_message => SQLERRM,
1043 x_source => 'BSC_UPDATE_DIM.Denorm_Eni_Item_Vbh_Cat');
1044 RETURN FALSE;
1045 END Denorm_Eni_Item_Vbh_Cat;
1046
1047
1048 /*===========================================================================+
1049 | FUNCTION Denorm_Eni_Item_Itm_Cat
1050 +============================================================================*/
1051 FUNCTION Denorm_Eni_Item_Itm_Cat RETURN BOOLEAN IS
1052
1053 e_unexpected_error EXCEPTION;
1054
1055 l_sql VARCHAR2(32000);
1056 l_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
1057
1058 TYPE t_cursor IS REF CURSOR;
1059 l_cursor t_cursor;
1060
1061 l_ids_this_level BSC_UPDATE_UTIL.t_array_of_number;
1062 l_num_ids_this_level NUMBER;
1063 l_ids BSC_UPDATE_UTIL.t_array_of_number;
1064 l_num_ids NUMBER;
1065 l_current_level NUMBER;
1066 l_id NUMBER;
1067 l_where_level VARCHAR2(32000);
1068 l_i NUMBER;
1069 l_short_name VARCHAR2(100);
1070 l_src_condition VARCHAR2(20000);
1071
1072 l_dim_obj_type VARCHAR2(80);
1073
1074 -- AW_INTEGRATION: new varaibles
1075 l_level_table_name VARCHAR2(30);
1076 l_dim_for_aw_kpi BOOLEAN;
1077
1078 BEGIN
1079
1080 l_dim_obj_type := 'DIM';
1081 l_short_name := 'ENI_ITEM_ITM_CAT';
1082
1083 Get_Dbi_Dim_Data(l_short_name, l_dbi_dim_data);
1084
1085 -- AW_INTEGRATION: We need to know the level_table_name given the short name
1086 SELECT level_table_name
1087 INTO l_level_table_name
1088 FROM bsc_sys_dim_levels_b
1089 WHERE short_name = l_short_name;
1090
1091 -- AW_INTEGRATION: We need to knowif the dimension is used by an AW indicator
1092 l_dim_for_aw_kpi := Dimension_Used_In_AW_Kpi(l_level_table_name);
1093
1094 IF l_dbi_dim_data.denorm_table IS NULL THEN
1095 -- there is no denorm table
1096 RETURN TRUE;
1097 END IF;
1098
1099
1100 IF (l_dbi_dim_data.top_n_levels IS NULL) OR (l_dbi_dim_data.top_n_levels = 0) THEN
1101 -- we do not need to denormalize this table
1102 RETURN TRUE;
1103 END IF;
1104
1105 -- We are going to compare the last update date of the source object with
1106 -- the refresh_end_time of the dimension table to know if it is necessary to
1107 -- refresh it or not.
1108 IF NOT Need_Refresh_Dbi_Table(l_dbi_dim_data.denorm_table, l_dbi_dim_data.denorm_source_to_check) THEN
1109 -- NO need to refresh
1110 RETURN TRUE;
1111 END IF;
1112
1113 -- Udpate REFRESH_START_TIME in BSC_OBJECT_REFRESH_LOG for this table
1114 l_sql := 'UPDATE bsc_object_refresh_log'||
1115 ' SET refresh_start_time = SYSDATE, refresh_end_time = NULL'||
1116 ' WHERE object_name = :1 AND object_type = :2';
1117 EXECUTE IMMEDIATE l_sql USING l_dbi_dim_data.denorm_table, l_dim_obj_type;
1118 IF SQL%NOTFOUND THEN
1119 l_sql := 'INSERT INTO bsc_object_refresh_log'||
1120 ' (object_name, object_type, refresh_start_time, refresh_end_time)'||
1121 ' VALUES (:1, :2, SYSDATE, NULL)';
1122 EXECUTE IMMEDIATE l_sql USING l_dbi_dim_data.denorm_table, l_dim_obj_type;
1123 END IF;
1124 COMMIT;
1125
1126 -- AW_INTEGRATION: We need to save the current denorm table data int temp table BSC_AW_TMP_DENORM
1127 IF l_dim_for_aw_kpi THEN
1128 BSC_UPDATE_UTIL.Execute_Immediate('DELETE BSC_AW_TMP_DENORM');
1129 l_sql := 'INSERT INTO BSC_AW_TMP_DENORM (CHILD_VALUE, PARENT_VALUE)'||
1130 ' SELECT '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_col||
1131 ' FROM '||l_dbi_dim_data.denorm_table;
1132 EXECUTE IMMEDIATE l_sql;
1133 COMMIT;
1134 END IF;
1135
1136 BSC_UPDATE_UTIL.Truncate_Table(l_dbi_dim_data.denorm_table);
1137
1138 l_src_condition := 'NVL(node, '||l_dbi_dim_data.child_col_src||') = '||l_dbi_dim_data.child_col_src;
1139
1140 FOR l_current_level IN 1..l_dbi_dim_data.top_n_levels LOOP
1141 l_num_ids_this_level := 0;
1142 l_ids_this_level.delete;
1143
1144 IF l_current_level = 1 THEN
1145 -- First level (top_level)
1146 l_sql := 'SELECT DISTINCT '||l_dbi_dim_data.child_col_src||
1147 ' FROM '||l_dbi_dim_data.denorm_src_object||
1148 ' WHERE '||l_src_condition||
1149 ' AND '||l_dbi_dim_data.parent_col_src||' IS NULL';
1150 ELSE
1151 l_sql := 'SELECT DISTINCT '||l_dbi_dim_data.child_col_src||
1152 ' FROM '||l_dbi_dim_data.denorm_src_object||
1153 ' WHERE '||l_src_condition||
1154 ' AND '||l_where_level;
1155 END IF;
1156 OPEN l_cursor FOR l_sql;
1157 l_where_level := BSC_APPS.Get_New_Big_In_Cond_Number(l_current_level, l_dbi_dim_data.parent_col_src);
1158 LOOP
1159 FETCH l_cursor INTO l_id;
1160 EXIT WHEN l_cursor%NOTFOUND;
1161 BSC_APPS.Add_Value_Big_In_Cond(l_current_level, l_id);
1162 l_num_ids_this_level := l_num_ids_this_level + 1;
1163 l_ids_this_level(l_num_ids_this_level) := l_id;
1164 END LOOP;
1165 CLOSE l_cursor;
1166
1167 IF l_num_ids_this_level = 0 THEN
1168 -- There is not items for this level, then no reason to continue...
1169 EXIT;
1170 END IF;
1171
1172 FOR l_i IN 1..l_num_ids_this_level LOOP
1173 -- Insert row for itself
1174 l_sql := 'INSERT INTO '||l_dbi_dim_data.denorm_table||' ('||
1175 l_dbi_dim_data.parent_col||', '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_level_col||
1176 ') VALUES (:1, :2, :3)';
1177 EXECUTE IMMEDIATE l_sql USING l_ids_this_level(l_i), l_ids_this_level(l_i), l_current_level;
1178
1179 -- Insert children, grand children, etc of this id
1180 l_ids(1) := l_ids_this_level(l_i);
1181 l_num_ids := 1;
1182 IF NOT Insert_Children_Denorm_Table(x_parent_id => l_ids_this_level(l_i),
1183 x_ids => l_ids,
1184 x_num_ids => l_num_ids,
1185 x_level => l_current_level,
1186 x_denorm_table => l_dbi_dim_data.denorm_table,
1187 x_child_col => l_dbi_dim_data.child_col,
1188 x_parent_col => l_dbi_dim_data.parent_col,
1189 x_parent_level_col => l_dbi_dim_data.parent_level_col,
1190 x_denorm_src_object => l_dbi_dim_data.denorm_src_object,
1191 x_child_col_src => l_dbi_dim_data.child_col_src,
1192 x_parent_col_src => l_dbi_dim_data.parent_col_src,
1193 x_src_condition => l_src_condition) THEN
1194 RAISE e_unexpected_error;
1195 END IF;
1196 END LOOP;
1197 END LOOP;
1198
1199 -- AW_INTEGRATION: Need to insert BSC_AW_TMP_DENORM MINUS l_dbi_dim_data.denorm_table
1200 -- into BSC_AW_REC_DIM_HIER_CHANGE
1201 IF l_dim_for_aw_kpi THEN
1202 l_sql := 'INSERT INTO BSC_AW_REC_DIM_HIER_CHANGE (DIM_LEVEL, CHILD_VALUE, PARENT_VALUE)'||
1203 ' SELECT :1, CHILD_VALUE, PARENT_VALUE FROM BSC_AW_TMP_DENORM'||
1204 ' MINUS '||
1205 ' SELECT :2, '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_col||
1206 ' FROM '||l_dbi_dim_data.denorm_table;
1207 EXECUTE IMMEDIATE l_sql USING l_level_table_name, l_level_table_name;
1208 END IF;
1209
1210 COMMIT;
1211
1212 -- Udpate REFRESH_END_TIME in BSC_OBJECT_REFRESH_LOG for this table
1213 l_sql := 'UPDATE bsc_object_refresh_log'||
1214 ' SET refresh_end_time = SYSDATE'||
1215 ' WHERE object_name = :1 AND object_type = :2';
1216 EXECUTE IMMEDIATE l_sql USING l_dbi_dim_data.denorm_table, l_dim_obj_type;
1217 COMMIT;
1218
1219 RETURN TRUE;
1220
1221 EXCEPTION
1222 WHEN e_unexpected_error THEN
1223 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),
1224 x_source => 'BSC_UPDATE_DIM.Denorm_Eni_Item_Itm_Cat');
1225 RETURN FALSE;
1226
1227 WHEN OTHERS THEN
1228 BSC_MESSAGE.Add(x_message => SQLERRM,
1229 x_source => 'BSC_UPDATE_DIM.Denorm_Eni_Item_Itm_Cat');
1230 RETURN FALSE;
1231 END Denorm_Eni_Item_Itm_Cat;
1232
1233
1234 /*===========================================================================+
1235 | FUNCTION Denorm_Hri_Per_Usrdr_H
1236 +============================================================================*/
1237 FUNCTION Denorm_Hri_Per_Usrdr_H RETURN BOOLEAN IS
1238
1239 l_sql VARCHAR2(32000);
1240 l_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
1241 l_lst_cols VARCHAR2(32000);
1242 l_lst_src_cols VARCHAR2(32000);
1243
1244 l_dim_obj_type VARCHAR2(80);
1245
1246 -- AW_INTEGRATION: new varaibles
1247 l_level_table_name VARCHAR2(30);
1248 l_dim_for_aw_kpi BOOLEAN;
1249 l_dim_short_name VARCHAR2(100);
1250
1251 BEGIN
1252
1253 l_dim_obj_type := 'DIM';
1254 l_dim_short_name := 'HRI_PER_USRDR_H';
1255
1256 Get_Dbi_Dim_Data(l_dim_short_name, l_dbi_dim_data);
1257
1258 -- AW_INTEGRATION: We need to know the level_table_name given the short name
1259 SELECT level_table_name
1260 INTO l_level_table_name
1261 FROM bsc_sys_dim_levels_b
1262 WHERE short_name = l_dim_short_name;
1263
1264 -- AW_INTEGRATION: We need to knowif the dimension is used by an AW indicator
1265 l_dim_for_aw_kpi := Dimension_Used_In_AW_Kpi(l_level_table_name);
1266
1267 IF l_dbi_dim_data.denorm_table IS NULL THEN
1268 -- there is no denorm table
1269 RETURN TRUE;
1270 END IF;
1271
1272 IF (l_dbi_dim_data.top_n_levels IS NULL) OR (l_dbi_dim_data.top_n_levels = 0) THEN
1273 -- we do not need to denormalize this table
1274 RETURN TRUE;
1275 END IF;
1276
1277 -- We are going to compare the last update date of the source object with
1278 -- the refresh_end_time of the dimension table to know if it is necessary to
1279 -- refresh it or not.
1280 IF NOT Need_Refresh_Dbi_Table(l_dbi_dim_data.denorm_table, l_dbi_dim_data.denorm_source_to_check) THEN
1281 -- NO need to refresh
1282 RETURN TRUE;
1283 END IF;
1284
1285 -- Udpate REFRESH_START_TIME in BSC_OBJECT_REFRESH_LOG for this table
1286 l_sql := 'UPDATE bsc_object_refresh_log'||
1287 ' SET refresh_start_time = SYSDATE, refresh_end_time = NULL'||
1288 ' WHERE object_name = :1 AND object_type = :2';
1289 EXECUTE IMMEDIATE l_sql USING l_dbi_dim_data.denorm_table, l_dim_obj_type;
1290 IF SQL%NOTFOUND THEN
1291 l_sql := 'INSERT INTO bsc_object_refresh_log'||
1292 ' (object_name, object_type, refresh_start_time, refresh_end_time)'||
1293 ' VALUES (:1, :2, SYSDATE, NULL)';
1294 EXECUTE IMMEDIATE l_sql USING l_dbi_dim_data.denorm_table, l_dim_obj_type;
1295 END IF;
1296 COMMIT;
1297
1298 -- AW_INTEGRATION: We need to save the current denorm table data int temp table BSC_AW_TMP_DENORM
1299 IF l_dim_for_aw_kpi THEN
1300 BSC_UPDATE_UTIL.Execute_Immediate('DELETE BSC_AW_TMP_DENORM');
1301 l_sql := 'INSERT INTO BSC_AW_TMP_DENORM (CHILD_VALUE, PARENT_VALUE)'||
1302 ' SELECT '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_col||
1303 ' FROM '||l_dbi_dim_data.denorm_table;
1304 EXECUTE IMMEDIATE l_sql;
1305 COMMIT;
1306 END IF;
1307
1308 BSC_UPDATE_UTIL.Truncate_Table(l_dbi_dim_data.denorm_table);
1309
1310 l_lst_cols := l_dbi_dim_data.parent_col||', '||l_dbi_dim_data.child_col||
1311 ', '||l_dbi_dim_data.parent_level_col;
1312
1313 l_lst_src_cols := l_dbi_dim_data.parent_col_src||', '||l_dbi_dim_data.child_col_src||
1314 ', '||l_dbi_dim_data.parent_level_src_col;
1315
1316 IF l_dbi_dim_data.date_tracked_dim = 'YES' THEN
1317 l_lst_cols := l_lst_cols||', EFFECTIVE_START_DATE, EFFECTIVE_END_DATE';
1318 l_lst_src_cols := l_lst_src_cols||', EFFECTIVE_START_DATE, EFFECTIVE_END_DATE';
1319 END IF;
1320
1321 l_sql := 'INSERT INTO '||l_dbi_dim_data.denorm_table||' ('||l_lst_cols||')'||
1322 ' SELECT '||l_lst_src_cols||
1323 ' FROM '||l_dbi_dim_data.denorm_src_object||
1324 ' WHERE '||l_dbi_dim_data.parent_level_src_col||' <= :1';
1325
1326 IF l_dbi_dim_data.date_tracked_dim = 'YES' THEN
1327 l_sql := l_sql||' AND (SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE)';
1328 END IF;
1329 EXECUTE IMMEDIATE l_sql USING l_dbi_dim_data.top_n_levels;
1330
1331 -- AW_INTEGRATION: Need to insert BSC_AW_TMP_DENORM MINUS l_dbi_dim_data.denorm_table
1332 -- into BSC_AW_REC_DIM_HIER_CHANGE
1333 IF l_dim_for_aw_kpi THEN
1334 l_sql := 'INSERT INTO BSC_AW_REC_DIM_HIER_CHANGE (DIM_LEVEL, CHILD_VALUE, PARENT_VALUE)'||
1335 ' SELECT :1, CHILD_VALUE, PARENT_VALUE FROM BSC_AW_TMP_DENORM'||
1336 ' MINUS '||
1337 ' SELECT :2, '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_col||
1338 ' FROM '||l_dbi_dim_data.denorm_table;
1339 EXECUTE IMMEDIATE l_sql USING l_level_table_name, l_level_table_name;
1340 END IF;
1341
1342 COMMIT;
1343
1344 -- Udpate REFRESH_END_TIME in BSC_OBJECT_REFRESH_LOG for this table
1345 l_sql := 'UPDATE bsc_object_refresh_log'||
1346 ' SET refresh_end_time = SYSDATE'||
1347 ' WHERE object_name = :1 AND object_type = :2';
1348 EXECUTE IMMEDIATE l_sql USING l_dbi_dim_data.denorm_table, l_dim_obj_type;
1349 COMMIT;
1350
1351 RETURN TRUE;
1352
1353 EXCEPTION
1354 WHEN OTHERS THEN
1355 BSC_MESSAGE.Add(x_message => SQLERRM,
1356 x_source => 'BSC_UPDATE_DIM.Denorm_Hri_Per_Usrdr_H');
1357 RETURN FALSE;
1358 END Denorm_Hri_Per_Usrdr_H;
1359
1360
1361 /*===========================================================================+
1362 | FUNCTION Denorm_Pji_Organizations
1363 +============================================================================*/
1364 FUNCTION Denorm_Pji_Organizations RETURN BOOLEAN IS
1365 e_unexpected_error EXCEPTION;
1366
1367 l_sql VARCHAR2(32000);
1368 l_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
1369
1370 TYPE t_cursor IS REF CURSOR;
1371 l_cursor t_cursor;
1372
1373 l_ids_this_level BSC_UPDATE_UTIL.t_array_of_number;
1374 l_num_ids_this_level NUMBER;
1375 l_ids BSC_UPDATE_UTIL.t_array_of_number;
1376 l_num_ids NUMBER;
1377 l_current_level NUMBER;
1378 l_id NUMBER;
1379 l_where_level VARCHAR2(32000);
1380 l_i NUMBER;
1381 l_short_name VARCHAR2(100);
1382
1383 l_dim_obj_type VARCHAR2(80);
1384
1385 -- AW_INTEGRATION: new varaibles
1386 l_level_table_name VARCHAR2(30);
1387 l_dim_for_aw_kpi BOOLEAN;
1388
1389 BEGIN
1390
1391 l_dim_obj_type := 'DIM';
1392 l_short_name := 'PJI_ORGANIZATIONS';
1393
1394 Get_Dbi_Dim_Data(l_short_name, l_dbi_dim_data);
1395
1396 -- AW_INTEGRATION: We need to know the level_table_name given the short name
1397 SELECT level_table_name
1398 INTO l_level_table_name
1399 FROM bsc_sys_dim_levels_b
1400 WHERE short_name = l_short_name;
1401
1402 -- AW_INTEGRATION: We need to knowif the dimension is used by an AW indicator
1403 l_dim_for_aw_kpi := Dimension_Used_In_AW_Kpi(l_level_table_name);
1404
1405 IF l_dbi_dim_data.denorm_table IS NULL THEN
1406 -- there is no denorm table
1407 RETURN TRUE;
1408 END IF;
1409
1410 IF (l_dbi_dim_data.top_n_levels IS NULL) OR (l_dbi_dim_data.top_n_levels = 0) THEN
1411 -- we do not need to denormalize this table
1412 RETURN TRUE;
1413 END IF;
1414
1415 -- We are going to compare the last update date of the source object with
1416 -- the refresh_end_time of the dimension table to know if it is necessary to
1417 -- refresh it or not.
1418 IF NOT Need_Refresh_Dbi_Table(l_dbi_dim_data.denorm_table, l_dbi_dim_data.denorm_source_to_check) THEN
1419 -- NO need to refresh
1420 RETURN TRUE;
1421 END IF;
1422
1423 -- Udpate REFRESH_START_TIME in BSC_OBJECT_REFRESH_LOG for this table
1424 l_sql := 'UPDATE bsc_object_refresh_log'||
1425 ' SET refresh_start_time = SYSDATE, refresh_end_time = NULL'||
1426 ' WHERE object_name = :1 AND object_type = :2';
1427 EXECUTE IMMEDIATE l_sql USING l_dbi_dim_data.denorm_table, l_dim_obj_type;
1428 IF SQL%NOTFOUND THEN
1429 l_sql := 'INSERT INTO bsc_object_refresh_log'||
1430 ' (object_name, object_type, refresh_start_time, refresh_end_time)'||
1431 ' VALUES (:1, :2, SYSDATE, NULL)';
1432 EXECUTE IMMEDIATE l_sql USING l_dbi_dim_data.denorm_table, l_dim_obj_type;
1433 END IF;
1434 COMMIT;
1435
1436 -- AW_INTEGRATION: We need to save the current denorm table data int temp table BSC_AW_TMP_DENORM
1437 IF l_dim_for_aw_kpi THEN
1438 BSC_UPDATE_UTIL.Execute_Immediate('DELETE BSC_AW_TMP_DENORM');
1439 l_sql := 'INSERT INTO BSC_AW_TMP_DENORM (CHILD_VALUE, PARENT_VALUE)'||
1440 ' SELECT '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_col||
1441 ' FROM '||l_dbi_dim_data.denorm_table;
1442 EXECUTE IMMEDIATE l_sql;
1443 COMMIT;
1444 END IF;
1445
1446 BSC_UPDATE_UTIL.Truncate_Table(l_dbi_dim_data.denorm_table);
1447
1448 FOR l_current_level IN 1..l_dbi_dim_data.top_n_levels LOOP
1449 l_num_ids_this_level := 0;
1450 l_ids_this_level.delete;
1451
1452 IF l_current_level = 1 THEN
1453 -- First level (top_level)
1454 l_sql := 'SELECT DISTINCT '||l_dbi_dim_data.child_col_src||
1455 ' FROM '||l_dbi_dim_data.denorm_src_object||
1456 ' WHERE '||l_dbi_dim_data.parent_col_src||' IS NULL';
1457 ELSE
1458 l_sql := 'SELECT DISTINCT '||l_dbi_dim_data.child_col_src||
1459 ' FROM '||l_dbi_dim_data.denorm_src_object||
1460 ' WHERE '||l_where_level;
1461 END IF;
1462 OPEN l_cursor FOR l_sql;
1463 l_where_level := BSC_APPS.Get_New_Big_In_Cond_Number(l_current_level, l_dbi_dim_data.parent_col_src);
1464 LOOP
1465 FETCH l_cursor INTO l_id;
1466 EXIT WHEN l_cursor%NOTFOUND;
1467 BSC_APPS.Add_Value_Big_In_Cond(l_current_level, l_id);
1468 l_num_ids_this_level := l_num_ids_this_level + 1;
1469 l_ids_this_level(l_num_ids_this_level) := l_id;
1470 END LOOP;
1471 CLOSE l_cursor;
1472
1473 IF l_num_ids_this_level = 0 THEN
1474 -- There is not items for this level, then no reason to continue...
1475 EXIT;
1476 END IF;
1477
1478 FOR l_i IN 1..l_num_ids_this_level LOOP
1479 -- Insert row for itself
1480 l_sql := 'INSERT INTO '||l_dbi_dim_data.denorm_table||' ('||
1481 l_dbi_dim_data.parent_col||', '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_level_col||
1482 ') VALUES (:1, :2, :3)';
1483 EXECUTE IMMEDIATE l_sql USING l_ids_this_level(l_i), l_ids_this_level(l_i), l_current_level;
1484
1485 -- Insert children, grand children, etc of this id
1486 l_ids(1) := l_ids_this_level(l_i);
1487 l_num_ids := 1;
1488
1489 IF NOT Insert_Children_Denorm_Table(x_parent_id => l_ids_this_level(l_i),
1490 x_ids => l_ids,
1491 x_num_ids => l_num_ids,
1492 x_level => l_current_level,
1493 x_denorm_table => l_dbi_dim_data.denorm_table,
1494 x_child_col => l_dbi_dim_data.child_col,
1495 x_parent_col => l_dbi_dim_data.parent_col,
1496 x_parent_level_col => l_dbi_dim_data.parent_level_col,
1497 x_denorm_src_object => l_dbi_dim_data.denorm_src_object,
1498 x_child_col_src => l_dbi_dim_data.child_col_src,
1499 x_parent_col_src => l_dbi_dim_data.parent_col_src,
1500 x_src_condition => NULL) THEN
1501 RAISE e_unexpected_error;
1502 END IF;
1503 END LOOP;
1504 END LOOP;
1505
1506 -- AW_INTEGRATION: Need to insert BSC_AW_TMP_DENORM MINUS l_dbi_dim_data.denorm_table
1507 -- into BSC_AW_REC_DIM_HIER_CHANGE
1508 IF l_dim_for_aw_kpi THEN
1509 l_sql := 'INSERT INTO BSC_AW_REC_DIM_HIER_CHANGE (DIM_LEVEL, CHILD_VALUE, PARENT_VALUE)'||
1510 ' SELECT :1, CHILD_VALUE, PARENT_VALUE FROM BSC_AW_TMP_DENORM'||
1511 ' MINUS '||
1512 ' SELECT :2, '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_col||
1513 ' FROM '||l_dbi_dim_data.denorm_table;
1514 EXECUTE IMMEDIATE l_sql USING l_level_table_name, l_level_table_name;
1515 END IF;
1516
1517 COMMIT;
1518
1519 -- Udpate REFRESH_END_TIME in BSC_OBJECT_REFRESH_LOG for this table
1520 l_sql := 'UPDATE bsc_object_refresh_log'||
1521 ' SET refresh_end_time = SYSDATE'||
1522 ' WHERE object_name = :1 AND object_type = :2';
1523 EXECUTE IMMEDIATE l_sql USING l_dbi_dim_data.denorm_table, l_dim_obj_type;
1524 COMMIT;
1525
1526 RETURN TRUE;
1527
1528 EXCEPTION
1529 WHEN e_unexpected_error THEN
1530 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),
1531 x_source => 'BSC_UPDATE_DIM.Denorm_Pji_Organizations');
1532 RETURN FALSE;
1533
1534 WHEN OTHERS THEN
1535 BSC_MESSAGE.Add(x_message => SQLERRM,
1536 x_source => 'BSC_UPDATE_DIM.Denorm_Pji_Organizations');
1537 RETURN FALSE;
1538 END Denorm_Pji_Organizations;
1539
1540
1541 --AW_INTEGRATION: New function
1542 /*===========================================================================+
1543 | FUNCTION Dimension_Used_In_AW_Kpi
1544 +============================================================================*/
1545 FUNCTION Dimension_Used_In_AW_Kpi(
1546 x_dim_table IN VARCHAR2
1547 ) RETURN BOOLEAN IS
1548 h_count NUMBER;
1549 h_aw_impl_type NUMBER;
1550 h_aw_impl_type_name VARCHAR2(100);
1551 BEGIN
1552 h_count := 0;
1553 h_aw_impl_type_name := 'IMPLEMENTATION_TYPE';
1554 h_aw_impl_type := 2;
1555
1556 select count(level_table_name)
1557 into h_count
1558 from bsc_kpi_dim_levels_b
1559 where indicator in (
1560 select p.indicator
1561 from bsc_kpi_properties p, bsc_kpis_b k
1562 where p.indicator = k.indicator and
1563 p.property_code = h_aw_impl_type_name and
1564 p.property_value = h_aw_impl_type and
1565 k.prototype_flag in (0,6,7)
1566 ) and level_table_name = x_dim_table;
1567
1568 IF h_count > 0 THEN
1569 RETURN TRUE;
1570 ELSE
1571 RETURN FALSE;
1572 END IF;
1573
1574 END Dimension_Used_In_AW_Kpi;
1575
1576
1577 --RECURSIVE_DIMS: New function
1578 /*===========================================================================+
1579 | FUNCTION Dimension_Used_In_MV_Kpi
1580 +============================================================================*/
1581 FUNCTION Dimension_Used_In_MV_Kpi(
1582 x_dim_table IN VARCHAR2
1583 ) RETURN BOOLEAN IS
1584 h_count NUMBER;
1585 h_mv_impl_type NUMBER;
1586 h_mv_impl_type_name VARCHAR2(100);
1587 BEGIN
1588 h_count := 0;
1589 h_mv_impl_type_name := 'IMPLEMENTATION_TYPE';
1590 h_mv_impl_type := 1;
1591
1592 select count(level_table_name)
1593 into h_count
1594 from bsc_kpi_dim_levels_b
1595 where indicator in (
1596 select k.indicator
1597 from bsc_kpi_properties p, bsc_kpis_b k
1598 where p.indicator (+) = k.indicator and
1599 p.property_code (+) = h_mv_impl_type_name and
1600 (p.property_value is null or p.property_value = h_mv_impl_type) and
1601 k.prototype_flag in (0,6,7)
1602 ) and level_table_name = x_dim_table;
1603
1604 IF h_count > 0 THEN
1605 RETURN TRUE;
1606 ELSE
1607 RETURN FALSE;
1608 END IF;
1609
1610 END Dimension_Used_In_MV_Kpi;
1611
1612
1613 /*===========================================================================+
1614 | PROCEDURE Get_All_Dbi_Dim_Data
1615 +============================================================================*/
1616 PROCEDURE Get_All_Dbi_Dim_Data(
1617 x_dbi_dim_data OUT NOCOPY BSC_UPDATE_DIM.t_array_dbi_dim_data
1618 ) IS
1619 l_i NUMBER;
1620 BEGIN
1621
1622 IF g_dbi_dim_data_set is null or g_dbi_dim_data_set = FALSE THEN
1623 Init_Dbi_Dim_Data;
1624 END IF;
1625
1626 FOR l_i IN 1..g_dbi_dim_data.COUNT LOOP
1627 x_dbi_dim_data(l_i) := g_dbi_dim_data(l_i);
1628 END LOOP;
1629
1630 END Get_All_Dbi_Dim_Data;
1631
1632
1633 /*===========================================================================+
1634 | FUNCTION Get_Aux_Fields_Dim_Table
1635 +============================================================================*/
1636 FUNCTION Get_Aux_Fields_Dim_Table(
1637 x_dim_table IN VARCHAR2,
1638 x_aux_fields IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
1639 ) RETURN NUMBER IS
1640
1641 TYPE t_cursor IS REF CURSOR;
1642
1643 /*
1644 c_aux_fields t_cursor; -- x_dim_table, h_column_type_a
1645 c_aux_fields_sql VARCHAR2(2000) := 'SELECT c.column_name'||
1646 ' FROM bsc_sys_dim_level_cols c, bsc_sys_dim_levels_b d'||
1647 ' WHERE d.dim_level_id = c.dim_level_id AND'||
1648 ' d.level_table_name = :1 AND'||
1649 ' column_type = :2';
1650 */
1651 CURSOR c_aux_fields (p_level_table_name VARCHAR2, p_column_type VARCHAR2) IS
1652 SELECT c.column_name
1653 FROM bsc_sys_dim_level_cols c, bsc_sys_dim_levels_b d
1654 WHERE d.dim_level_id = c.dim_level_id AND
1655 d.level_table_name = p_level_table_name AND
1656 column_type = p_column_type;
1657
1658 h_column_type_a VARCHAR2(1);
1659
1660 h_aux_field bsc_sys_dim_level_cols.column_name%TYPE;
1661 h_num_aux_fields NUMBER;
1662
1663 BEGIN
1664 h_column_type_a := 'A';
1665 h_num_aux_fields := 0;
1666
1667 --OPEN c_aux_fields FOR c_aux_fields_sql USING x_dim_table, h_column_type_a;
1668 OPEN c_aux_fields (x_dim_table, h_column_type_a);
1669 FETCH c_aux_fields INTO h_aux_field;
1670 WHILE c_aux_fields%FOUND LOOP
1671 h_num_aux_fields := h_num_aux_fields + 1;
1672 x_aux_fields(h_num_aux_fields) := h_aux_field;
1673
1674 FETCH c_aux_fields INTO h_aux_field;
1675 END LOOP;
1676 CLOSE c_aux_fields;
1677
1678 RETURN h_num_aux_fields;
1679
1680 END Get_Aux_Fields_Dim_Table;
1681
1682
1683 /*===========================================================================+
1684 | FUNCTION Get_Child_Dimensions
1685 +============================================================================*/
1686 FUNCTION Get_Child_Dimensions(
1687 x_dimension_table IN VARCHAR2,
1688 x_child_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
1689 ) RETURN NUMBER IS
1690
1691 h_num_child_dimensions NUMBER;
1692
1693 h_table_id NUMBER;
1694
1695 TYPE t_cursor IS REF CURSOR;
1696 h_cursor t_cursor;
1697 h_sql VARCHAR2(32000);
1698
1699 /*
1700 c_child_dimensions t_cursor; -- h_table_id, 1
1701 c_child_dimensions_sql VARCHAR2(2000) := 'SELECT t.level_table_name'||
1702 ' FROM bsc_sys_dim_levels_b t, bsc_sys_dim_level_rels r'||
1703 ' WHERE t.dim_level_id = r.dim_level_id AND'||
1704 ' r.parent_dim_level_id = :1 AND r.relation_type = :2';
1705 */
1706 CURSOR c_child_dimensions (p_parent_id NUMBER, p_relation_type NUMBER) IS
1707 SELECT t.level_table_name
1708 FROM bsc_sys_dim_levels_b t, bsc_sys_dim_level_rels r
1709 WHERE t.dim_level_id = r.dim_level_id AND
1710 r.parent_dim_level_id = p_parent_id AND r.relation_type = p_relation_type;
1711
1712 h_child_dimension VARCHAR2(30);
1713
1714 BEGIN
1715
1716 h_num_child_dimensions := 0;
1717
1718 -- Get dimension id
1719 /*
1720 h_sql := 'SELECT dim_level_id'||
1721 ' FROM bsc_sys_dim_levels_b'||
1722 ' WHERE level_table_name = :1';
1723 OPEN h_cursor FOR h_sql USING x_dimension_table;
1724 FETCH h_cursor INTO h_table_id;
1725 CLOSE h_cursor;
1726 */
1727 SELECT dim_level_id
1728 INTO h_table_id
1729 FROM bsc_sys_dim_levels_b
1730 WHERE level_table_name = x_dimension_table;
1731
1732 -- Get child dimensions
1733 --OPEN c_child_dimensions FOR c_child_dimensions_sql USING h_table_id, 1;
1734 OPEN c_child_dimensions(h_table_id, 1);
1735 FETCH c_child_dimensions INTO h_child_dimension;
1736 WHILE c_child_dimensions%FOUND LOOP
1737 h_num_child_dimensions := h_num_child_dimensions + 1;
1738 x_child_dimensions(h_num_child_dimensions) := h_child_dimension;
1739
1740 FETCH c_child_dimensions INTO h_child_dimension;
1741 END LOOP;
1742 CLOSE c_child_dimensions;
1743
1744 RETURN h_num_child_dimensions;
1745
1746 END Get_Child_Dimensions;
1747
1748
1749 /*===========================================================================+
1750 | FUNCTION Get_Dbi_Dim_Parent_Columns
1751 +============================================================================*/
1752 FUNCTION Get_Dbi_Dim_Parent_Columns(
1753 x_dim_short_name IN VARCHAR2,
1754 x_parent_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
1755 x_src_parent_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
1756 ) RETURN NUMBER IS
1757
1758 CURSOR c_parent_cols IS
1759 SELECT p.level_pk_col
1760 FROM bsc_sys_dim_levels_b c, bsc_sys_dim_level_rels r, bsc_sys_dim_levels_b p
1761 WHERE c.dim_level_id = r.dim_level_id AND
1762 r.parent_dim_level_id = p.dim_level_id AND
1763 r.relation_type = 1 AND
1764 r.dim_level_id <> r.parent_dim_level_id AND
1765 c.short_name = x_dim_short_name
1766 ORDER BY p.level_pk_col;
1767
1768 l_num_parent_columns NUMBER;
1769 l_parent_column VARCHAR2(50);
1770 l_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
1771
1772 BEGIN
1773 l_num_parent_columns := 0;
1774
1775 Get_Dbi_Dim_Data(x_dim_short_name, l_dbi_dim_data);
1776 x_src_parent_columns(1) := l_dbi_dim_data.parent1_col;
1777 x_src_parent_columns(2) := l_dbi_dim_data.parent2_col;
1778 x_src_parent_columns(3) := l_dbi_dim_data.parent3_col;
1779 x_src_parent_columns(4) := l_dbi_dim_data.parent4_col;
1780 x_src_parent_columns(5) := l_dbi_dim_data.parent5_col;
1781
1782 OPEN c_parent_cols;
1783 LOOP
1784 FETCH c_parent_cols INTO l_parent_column;
1785 EXIT WHEN c_parent_cols%NOTFOUND;
1786
1787 l_num_parent_columns := l_num_parent_columns + 1;
1788 x_parent_columns(l_num_parent_columns) := l_parent_column;
1789
1790 IF x_src_parent_columns(l_num_parent_columns) IS NULL THEN
1791 x_src_parent_columns(l_num_parent_columns) := l_parent_column;
1792 END IF;
1793 END LOOP;
1794 CLOSE c_parent_cols;
1795
1796 RETURN l_num_parent_columns;
1797
1798 END Get_Dbi_Dim_Parent_Columns;
1799
1800
1801 /*===========================================================================+
1802 | FUNCTION Get_Dbi_Dim_View_Name
1803 +============================================================================*/
1804 FUNCTION Get_Dbi_Dim_View_Name(
1805 x_dim_short_name IN VARCHAR2
1806 ) RETURN VARCHAR2 IS
1807
1808 l_view_name VARCHAR2(30);
1809
1810 BEGIN
1811 SELECT level_view_name
1812 INTO l_view_name
1813 FROM bsc_sys_dim_levels_b
1814 WHERE short_name = x_dim_short_name;
1815
1816 RETURN l_view_name;
1817 EXCEPTION
1818 WHEN NO_DATA_FOUND THEN
1819 RETURN NULL;
1820 END Get_Dbi_Dim_View_Name;
1821
1822
1823 /*===========================================================================+
1824 | PROCEDURE Get_Dbi_Dim_Data
1825 +============================================================================*/
1826 PROCEDURE Get_Dbi_Dim_Data(
1827 x_dim_short_name IN VARCHAR2,
1828 x_dbi_dim_data OUT NOCOPY BSC_UPDATE_DIM.t_dbi_dim_data
1829 ) IS
1830
1831 l_i NUMBER;
1832
1833 BEGIN
1834 IF g_dbi_dim_data_set is null or g_dbi_dim_data_set = FALSE THEN
1835 Init_Dbi_Dim_Data;
1836 END IF;
1837
1838 FOR l_i IN 1..g_dbi_dim_data.COUNT LOOP
1839 IF UPPER(g_dbi_dim_data(l_i).short_name) = UPPER(x_dim_short_name) THEN
1840 x_dbi_dim_data := g_dbi_dim_data(l_i);
1841 EXIT;
1842 END IF;
1843 END LOOP;
1844
1845 END Get_Dbi_Dim_Data;
1846
1847
1848 /*===========================================================================+
1849 | FUNCTION Get_Dbi_Dims_Kpis
1850 +============================================================================*/
1851 FUNCTION Get_Dbi_Dims_Kpis(
1852 x_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
1853 x_num_indicators IN NUMBER,
1854 x_dbi_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
1855 x_num_dbi_dimensions IN OUT NOCOPY NUMBER
1856 ) RETURN BOOLEAN IS
1857
1858 h_where_indics VARCHAR2(32000);
1859 h_i NUMBER;
1860
1861 TYPE t_cursor IS REF CURSOR;
1862 h_cursor t_cursor;
1863 h_sql VARCHAR2(32700);
1864
1865 h_short_name VARCHAR2(50);
1866 h_source VARCHAR2(10);
1867 h_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
1868
1869 BEGIN
1870 h_where_indics := NULL;
1871 h_sql := NULL;
1872 h_source := 'PMF';
1873 x_num_dbi_dimensions := 0;
1874
1875 h_where_indics := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'indicator');
1876 FOR h_i IN 1 .. x_num_indicators LOOP
1877 BSC_APPS.Add_Value_Big_In_Cond(1, x_indicators(h_i));
1878 END LOOP;
1879
1880 h_sql := 'SELECT DISTINCT level_shortname'||
1881 ' FROM bsc_kpi_dim_levels_vl'||
1882 ' WHERE ('||h_where_indics||') AND level_source = :1';
1883
1884 OPEN h_cursor FOR h_sql USING h_source;
1885 FETCH h_cursor INTO h_short_name;
1886 WHILE h_cursor%FOUND LOOP
1887 -- AW_INTEGRATION: Since we need to bring all the BIS dimensions used by AW indicators into AW world
1888 -- I need to change the next function to return all the BIS dimensions and not only the
1889 -- ones that are materialized in BSC
1890 x_num_dbi_dimensions := x_num_dbi_dimensions + 1;
1891 x_dbi_dimensions(x_num_dbi_dimensions) := h_short_name;
1892
1893 FETCH h_cursor INTO h_short_name;
1894 END LOOP;
1895 CLOSE h_cursor;
1896
1897 RETURN TRUE;
1898
1899 EXCEPTION
1900 WHEN OTHERS THEN
1901 BSC_MESSAGE.Add(x_message => SQLERRM,
1902 x_source => 'BSC_UPDATE_DIM.Get_Dbi_Dims_Kpis');
1903 RETURN FALSE;
1904
1905 END Get_Dbi_Dims_Kpis;
1906
1907
1908 /*===========================================================================+
1909 | FUNCTION Get_Deleted_Records
1910 +============================================================================*/
1911 FUNCTION Get_Deleted_Records(
1912 x_dimension_table IN VARCHAR2,
1913 x_temp_table IN VARCHAR2,
1914 x_deleted_records IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number
1915 ) RETURN NUMBER IS
1916
1917 h_num_deleted_records NUMBER;
1918 h_sql VARCHAR2(32700);
1919
1920 TYPE t_cursor IS REF CURSOR;
1921 h_cursor t_cursor;
1922
1923 h_code NUMBER;
1924
1925 BEGIN
1926 h_num_deleted_records := 0;
1927
1928 h_sql := 'SELECT T.CODE'||
1929 ' FROM '||x_temp_table||' T, '||x_dimension_table||' D'||
1930 ' WHERE T.CODE = D.CODE (+)'||
1931 ' AND D.CODE IS NULL';
1932
1933 OPEN h_cursor FOR h_sql;
1934 FETCH h_cursor INTO h_code;
1935 WHILE h_cursor%FOUND LOOP
1936 h_num_deleted_records := h_num_deleted_records + 1;
1937 x_deleted_records(h_num_deleted_records) := h_code;
1938
1939 FETCH h_cursor INTO h_code;
1940 END LOOP;
1941 CLOSE h_cursor;
1942
1943 RETURN h_num_deleted_records;
1944
1945 END Get_Deleted_Records;
1946
1947
1948 /*===========================================================================+
1949 | FUNCTION Get_Dim_Table_of_Input_Table
1950 +============================================================================*/
1951 FUNCTION Get_Dim_Table_of_Input_Table(
1952 x_input_table IN VARCHAR2
1953 ) RETURN VARCHAR2 IS
1954
1955 h_table_name VARCHAR2(30);
1956
1957 TYPE t_cursor IS REF CURSOR;
1958 h_cursor t_cursor;
1959 h_sql VARCHAR2(32000);
1960
1961 BEGIN
1962 /*
1963 h_sql := 'SELECT table_name'||
1964 ' FROM bsc_db_tables_rels'||
1965 ' WHERE source_table_name = :1';
1966 OPEN h_cursor FOR h_sql USING x_input_table;
1967 FETCH h_cursor INTO h_table_name;
1968 CLOSE h_cursor;
1969 */
1970 SELECT table_name
1971 INTO h_table_name
1972 FROM bsc_db_tables_rels
1973 WHERE source_table_name = x_input_table;
1974
1975 RETURN h_table_name;
1976
1977 END Get_Dim_Table_of_Input_Table;
1978
1979
1980 /*===========================================================================+
1981 | FUNCTION Get_Dim_Table_Type
1982 +============================================================================*/
1983 FUNCTION Get_Dim_Table_Type(
1984 x_dim_table IN VARCHAR2
1985 ) RETURN NUMBER IS
1986
1987 h_count NUMBER;
1988
1989 TYPE t_cursor IS REF CURSOR;
1990 h_cursor t_cursor;
1991 h_sql VARCHAR2(32000);
1992
1993 BEGIN
1994 h_count := 0;
1995
1996 -- See if the dimension table is 1N
1997 /*
1998 h_sql := 'SELECT COUNT(*)'||
1999 ' FROM bsc_sys_dim_levels_b'||
2000 ' WHERE level_table_name = :1';
2001 OPEN h_cursor FOR h_sql USING x_dim_table;
2002 FETCH h_cursor INTO h_count;
2003 CLOSE h_cursor;
2004 */
2005 SELECT COUNT(*)
2006 INTO h_count
2007 FROM bsc_sys_dim_levels_b
2008 WHERE level_table_name = x_dim_table;
2009
2010 IF h_count > 0 THEN
2011 RETURN DIM_TABLE_TYPE_1N;
2012 END IF;
2013
2014 -- See if the dimension table is MN
2015 /*
2016 h_sql := 'SELECT COUNT(*)'||
2017 ' FROM bsc_sys_dim_level_rels'||
2018 ' WHERE relation_col = :1';
2019 OPEN h_cursor FOR h_sql USING x_dim_table;
2020 FETCH h_cursor INTO h_count;
2021 CLOSE h_cursor;
2022 */
2023 SELECT COUNT(*)
2024 INTO h_count
2025 FROM bsc_sys_dim_level_rels
2026 WHERE relation_col = x_dim_table;
2027
2028 IF h_count > 0 THEN
2029 RETURN DIM_TABLE_TYPE_MN;
2030 END IF;
2031
2032 RETURN DIM_TABLE_TYPE_UNKNOWN;
2033
2034 END Get_Dim_Table_Type;
2035
2036
2037 /*===========================================================================+
2038 | FUNCTION Get_Info_Parents_Dimensions
2039 +============================================================================*/
2040 FUNCTION Get_Info_Parents_Dimensions(
2041 x_dim_table IN VARCHAR2,
2042 x_parent_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2043 x_parent_keys IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
2044 ) RETURN NUMBER IS
2045
2046 TYPE t_cursor IS REF CURSOR;
2047
2048 /*
2049 c_parents t_cursor; -- x_dim_table
2050 c_parents_sql VARCHAR2(2000) := 'SELECT dp.level_table_name, dp.level_pk_col'||
2051 ' FROM bsc_sys_dim_levels_b d, bsc_sys_dim_levels_b dp,'||
2052 ' bsc_sys_dim_level_rels r'||
2053 ' WHERE d.dim_level_id = r.dim_level_id AND'||
2054 ' r.parent_dim_level_id = dp.dim_level_id AND'||
2055 ' DECODE(r.relation_type, 2, r.relation_col,'||
2056 ' d.level_table_name) = :1';
2057 */
2058 CURSOR c_parents (p_dim_table VARCHAR2) IS
2059 SELECT dp.level_table_name, dp.level_pk_col
2060 FROM bsc_sys_dim_levels_b d, bsc_sys_dim_levels_b dp, bsc_sys_dim_level_rels r
2061 WHERE d.dim_level_id = r.dim_level_id AND
2062 r.parent_dim_level_id = dp.dim_level_id AND
2063 DECODE(r.relation_type, 2, r.relation_col, d.level_table_name) = p_dim_table;
2064
2065 h_parent_table bsc_sys_dim_levels_b.level_table_name%TYPE;
2066 h_parent_key bsc_sys_dim_levels_b.level_pk_col%TYPE;
2067
2068 h_num_parents NUMBER;
2069
2070 BEGIN
2071 h_num_parents := 0;
2072
2073 --OPEN c_parents FOR c_parents_sql USING x_dim_table;
2074 OPEN c_parents(x_dim_table);
2075 FETCH c_parents INTO h_parent_table, h_parent_key;
2076 WHILE c_parents%FOUND LOOP
2077 h_num_parents := h_num_parents + 1;
2078 x_parent_tables(h_num_parents) := h_parent_table;
2079 x_parent_keys(h_num_parents) := h_parent_key;
2080
2081 FETCH c_parents INTO h_parent_table, h_parent_key;
2082 END LOOP;
2083 CLOSE c_parents;
2084
2085 RETURN h_num_parents;
2086
2087 END Get_Info_Parents_Dimensions;
2088
2089
2090 /*===========================================================================+
2091 | FUNCTION Get_Level_PK_Col
2092 +============================================================================*/
2093 FUNCTION Get_Level_PK_Col(
2094 x_dimension_table IN VARCHAR2
2095 ) RETURN VARCHAR2 IS
2096
2097 h_level_pk_col VARCHAR2(30);
2098
2099 TYPE t_cursor IS REF CURSOR;
2100 h_cursor t_cursor;
2101 h_sql VARCHAR2(32000);
2102
2103 BEGIN
2104 /*
2105 h_sql := 'SELECT level_pk_col'||
2106 ' FROM bsc_sys_dim_levels_b'||
2107 ' WHERE level_table_name = :1';
2108 OPEN h_cursor FOR h_sql USING x_dimension_table;
2109 FETCH h_cursor INTO h_level_pk_col;
2110 CLOSE h_cursor;
2111 */
2112 SELECT level_pk_col
2113 INTO h_level_pk_col
2114 FROM bsc_sys_dim_levels_b
2115 WHERE level_table_name = x_dimension_table;
2116
2117 RETURN h_level_pk_col;
2118
2119 END Get_Level_PK_Col;
2120
2121
2122 /*===========================================================================+
2123 | FUNCTION Get_New_Code
2124 +============================================================================*/
2125 FUNCTION Get_New_Code(
2126 x_dim_table IN VARCHAR2
2127 ) RETURN NUMBER IS
2128
2129 h_new_code NUMBER;
2130
2131 TYPE t_cursor IS REF CURSOR;
2132 h_cursor t_cursor;
2133
2134 h_sql VARCHAR2(32700);
2135
2136 BEGIN
2137 h_sql := 'SELECT NVL(MAX(CODE) + 1, 1) FROM '||x_dim_table;
2138
2139 OPEN h_cursor FOR h_sql;
2140 FETCH h_cursor INTO h_new_code;
2141 IF h_cursor%NOTFOUND THEN
2142 h_new_code := -1;
2143 END IF;
2144 CLOSE h_cursor;
2145
2146 RETURN h_new_code;
2147
2148 EXCEPTION
2149 WHEN OTHERS THEN
2150 BSC_MESSAGE.Add(x_message => SQLERRM,
2151 x_source => 'BSC_UPDATE_BASE.Get_New_Code');
2152 RETURN -1;
2153
2154 END Get_New_Code;
2155
2156
2157 /*===========================================================================+
2158 | FUNCTION Get_Parent_Dimensions
2159 +============================================================================*/
2160 FUNCTION Get_Parent_Dimensions(
2161 x_dimension_table IN VARCHAR2,
2162 x_parent_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
2163 ) RETURN NUMBER IS
2164
2165 h_num_parent_dimensions NUMBER;
2166
2167 h_table_id NUMBER;
2168
2169 TYPE t_cursor IS REF CURSOR;
2170 h_cursor t_cursor;
2171 h_sql VARCHAR2(32000);
2172
2173 /*
2174 c_parent_dimensions t_cursor; -- h_table_id, 1
2175 c_parent_dimensions_sql VARCHAR2(2000) := 'SELECT t.level_table_name'||
2176 ' FROM bsc_sys_dim_levels_b t, bsc_sys_dim_level_rels r'||
2177 ' WHERE t.dim_level_id = r.parent_dim_level_id AND'||
2178 ' r.dim_level_id = :1 AND r.relation_type = :2';
2179 */
2180 CURSOR c_parent_dimensions (p_dim_level_id NUMBER, p_relation_type NUMBER) IS
2181 SELECT t.level_table_name
2182 FROM bsc_sys_dim_levels_b t, bsc_sys_dim_level_rels r
2183 WHERE t.dim_level_id = r.parent_dim_level_id AND
2184 r.dim_level_id = p_dim_level_id AND r.relation_type = p_relation_type;
2185
2186 h_parent_dimension VARCHAR2(30);
2187
2188 BEGIN
2189 h_num_parent_dimensions := 0;
2190
2191 -- Get dimension id
2192 /*
2193 h_sql := 'SELECT dim_level_id'||
2194 ' FROM bsc_sys_dim_levels_b'||
2195 ' WHERE level_table_name = :1';
2196 OPEN h_cursor FOR h_sql USING x_dimension_table;
2197 FETCH h_cursor INTO h_table_id;
2198 CLOSE h_cursor;
2199 */
2200 SELECT dim_level_id
2201 INTO h_table_id
2202 FROM bsc_sys_dim_levels_b
2203 WHERE level_table_name = x_dimension_table;
2204
2205 -- Get parent dimensions
2206 --OPEN c_parent_dimensions FOR c_parent_dimensions_sql USING h_table_id, 1;
2207 OPEN c_parent_dimensions (h_table_id, 1);
2208 FETCH c_parent_dimensions INTO h_parent_dimension;
2209 WHILE c_parent_dimensions%FOUND LOOP
2210 h_num_parent_dimensions := h_num_parent_dimensions + 1;
2211 x_parent_dimensions(h_num_parent_dimensions) := h_parent_dimension;
2212
2213 FETCH c_parent_dimensions INTO h_parent_dimension;
2214 END LOOP;
2215 CLOSE c_parent_dimensions;
2216
2217 RETURN h_num_parent_dimensions;
2218
2219 END Get_Parent_Dimensions;
2220
2221
2222 /*===========================================================================+
2223 | FUNCTION Get_Relation_Cols
2224 +============================================================================*/
2225 FUNCTION Get_Relation_Cols(
2226 x_dimension_table IN VARCHAR2,
2227 x_relation_cols IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
2228 ) RETURN NUMBER IS
2229
2230 TYPE t_cursor IS REF CURSOR;
2231
2232 /*
2233 c_relation_cols t_cursor; -- x_dimension_table, 1
2234 c_relation_cols_sql VARCHAR2(2000) := 'SELECT r.relation_col'||
2235 ' FROM bsc_sys_dim_levels_b d, bsc_sys_dim_level_rels r'||
2236 ' WHERE d.dim_level_id = r.dim_level_id AND'||
2237 ' d.level_table_name = :1 AND r.relation_type = :2';
2238 */
2239 CURSOR c_relation_cols (p_level_table_name VARCHAR2, p_relation_type NUMBER) IS
2240 SELECT r.relation_col
2241 FROM bsc_sys_dim_levels_b d, bsc_sys_dim_level_rels r
2242 WHERE d.dim_level_id = r.dim_level_id AND
2243 d.level_table_name = p_level_table_name AND r.relation_type = p_relation_type;
2244
2245 h_num_relation_cols NUMBER;
2246 h_relation_col VARCHAR2(50);
2247
2248 BEGIN
2249 h_num_relation_cols := 0;
2250
2251 --OPEN c_relation_cols FOR c_relation_cols_sql USING x_dimension_table, 1;
2252 OPEN c_relation_cols(x_dimension_table, 1);
2253 FETCH c_relation_cols INTO h_relation_col;
2254 WHILE c_relation_cols%FOUND LOOP
2255 h_num_relation_cols := h_num_relation_cols + 1;
2256 x_relation_cols(h_num_relation_cols) := h_relation_col;
2257
2258 FETCH c_relation_cols INTO h_relation_col;
2259 END LOOP;
2260 CLOSE c_relation_cols;
2261
2262 RETURN h_num_relation_cols;
2263
2264 END Get_Relation_Cols;
2265
2266
2267 /*===========================================================================+
2268 | FUNCTION Import_Dbi_Plans
2269 +============================================================================*/
2270 FUNCTION Import_Dbi_Plans(
2271 x_error_msg IN OUT NOCOPY VARCHAR2
2272 ) RETURN BOOLEAN IS
2273
2274 l_sql VARCHAR2(32000);
2275
2276 TYPE t_cursor IS REF CURSOR;
2277 l_cursor t_cursor;
2278
2279 l_id NUMBER;
2280 l_value VARCHAR2(400);
2281
2282 l_ids BSC_UPDATE_UTIL.t_array_of_number;
2283 l_values BSC_UPDATE_UTIL.t_array_of_varchar2;
2284 l_num_ids NUMBER;
2285 l_i NUMBER;
2286 l_bm_id NUMBER;
2287
2288 l_dbi_plans_view VARCHAR2(30);
2289 l_count NUMBER;
2290
2291 BEGIN
2292 -- fix bug#4046594: Validate that isc_plan_snapshot_v exists
2293 l_dbi_plans_view := 'ISC_PLAN_SNAPSHOT_V';
2294 select count(view_name) into l_count
2295 from user_views
2296 where view_name = l_dbi_plans_view;
2297 IF l_count = 0 THEN
2298 -- isc_plan_snapshot_v does not exists so we cannot import dbi plans
2299 RETURN TRUE;
2300 END IF;
2301
2302 -- Delete from BSC_SYS_BENCHMARKS_B and BSC_SYS_BENCHMARKS_TL the
2303 -- DBI plans that are no longer valid.
2304 l_sql := 'DELETE FROM bsc_sys_benchmarks_b'||
2305 ' WHERE source_type = :1'||
2306 ' AND data_type NOT IN ('||
2307 ' SELECT id FROM isc_plan_snapshot_v'||
2308 ')';
2309 EXECUTE IMMEDIATE l_sql USING 2;
2310
2311 DELETE FROM bsc_sys_benchmarks_tl
2312 WHERE bm_id NOT IN (SELECT bm_id FROM bsc_sys_benchmarks_b);
2313
2314 -- Insert new dbi plans into BSC_SYS_BENCHMARKS and BSC_SYS_BENCHMARKS_TL
2315 -- Added s.id < 1000 for bug#6713924
2316 l_num_ids := 0;
2317 l_sql := 'SELECT s.id, s.value'||
2318 ' FROM isc_plan_snapshot_v s, bsc_sys_benchmarks_b b'||
2319 ' WHERE s.id = b.data_type (+) AND b.source_type (+) = :1 AND b.data_type IS NULL AND s.id < 1000';
2320 OPEN l_cursor FOR l_sql USING 2;
2321 LOOP
2322 FETCH l_cursor INTO l_id, l_value;
2323 EXIT WHEN l_cursor%NOTFOUND;
2324
2325 l_num_ids := l_num_ids + 1;
2326 l_ids(l_num_ids) := l_id;
2327 l_values(l_num_ids) := l_value;
2328 END LOOP;
2329 CLOSE l_cursor;
2330
2331 FOR l_i IN 1..l_num_ids LOOP
2332 SELECT NVL(MAX(bm_id)+1,1) INTO l_bm_id
2333 FROM bsc_sys_benchmarks_b;
2334
2335 -- This inserts the record in bs_sys_benchmarks_b and tl for existing languages
2336 BSC_SYS_BENCHMARKS_PKG.Insert_Row(x_bm_id => l_bm_id,
2337 x_color => 0,
2338 x_data_type => l_ids(l_i),
2339 x_source_type => 2,
2340 x_periodicity_id => 0,
2341 x_no_display_flag => 0,
2342 x_name => l_values(l_i));
2343 END LOOP;
2344
2345 COMMIT;
2346 RETURN TRUE;
2347 EXCEPTION
2348 WHEN OTHERS THEN
2349 ROLLBACK;
2350 x_error_msg := SQLERRM;
2351 RETURN FALSE;
2352 END Import_Dbi_Plans;
2353
2354 --LOCKING: new function
2355 /*===========================================================================+
2356 | FUNCTION Import_Dbi_Plans_AT
2357 +============================================================================*/
2358 FUNCTION Import_Dbi_Plans_AT(
2359 x_error_msg IN OUT NOCOPY VARCHAR2
2360 ) RETURN BOOLEAN IS
2361 PRAGMA AUTONOMOUS_TRANSACTION;
2362 h_b BOOLEAN;
2363 BEGIN
2364 h_b := Import_Dbi_Plans(x_error_msg);
2365 commit; -- all autonomous transaction needs to commit
2366 RETURN h_b;
2367 END Import_Dbi_Plans_AT;
2368
2369
2370 /*===========================================================================+
2371 | PROCEDURE Init_Dbi_Dim_Data
2372 +============================================================================*/
2373 PROCEDURE Init_Dbi_Dim_Data IS
2374 l_i NUMBER;
2375 l_owner varchar2(100);
2376 BEGIN
2377 l_i := 0;
2378 g_dbi_dim_data.delete;
2379
2380 l_i := l_i + 1;
2381 g_dbi_dim_data(l_i).short_name := 'JTF_ORG_SALES_GROUP';
2382 g_dbi_dim_data(l_i).table_name := 'BSC_D_JTF_ORG_SALES_GROUP_T';
2383 g_dbi_dim_data(l_i).from_clause := NULL;
2384 g_dbi_dim_data(l_i).where_clause := NULL;
2385 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2386 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2387 g_dbi_dim_data(l_i).source_object := '(SELECT /*+parallel (drg)*/ to_char(drg.id) USER_CODE,to_char(drg.id) CODE '||
2388 'FROM jtf_rs_dbi_denorm_res_groups drg
2389 UNION ALL SELECT TO_CHAR(-1111) USER_CODE,TO_CHAR(-1111) CODE FROM dual
2390 UNION ALL SELECT /*+parallel (drg)*/ to_char(drg.id_for_grp_mem) USER_CODE,to_char(drg.id_for_grp_mem) CODE FROM '||
2391 'jtf_rs_dbi_denorm_res_groups drg
2392 UNION ALL SELECT /*+parallel (d1)*/ to_char(d1.group_id) USER_CODE,to_char(d1.group_id) CODE '||
2393 'FROM jtf_rs_groups_denorm d1) JTF_ORG_SALES_GROUP';
2394 g_dbi_dim_data(l_i).source_object_alias:='JTF_ORG_SALES_GROUP';
2395 g_dbi_dim_data(l_i).materialized :='YES';
2396 g_dbi_dim_data(l_i).user_code_col := NULL;
2397 g_dbi_dim_data(l_i).code_col := NULL;
2398 g_dbi_dim_data(l_i).parent1_col := NULL;
2399 g_dbi_dim_data(l_i).parent2_col := NULL;
2400 g_dbi_dim_data(l_i).parent3_col := NULL;
2401 g_dbi_dim_data(l_i).parent4_col := NULL;
2402 g_dbi_dim_data(l_i).parent5_col := NULL;
2403
2404 l_i := l_i + 1;
2405 g_dbi_dim_data(l_i).short_name := 'FII_CURRENCIES';
2406 g_dbi_dim_data(l_i).table_name := 'BSC_D_FII_CURRENCIES_T';
2407 g_dbi_dim_data(l_i).from_clause := NULL;
2408 g_dbi_dim_data(l_i).where_clause := NULL;
2409 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2410 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2411 g_dbi_dim_data(l_i).source_object := NULL;
2412 g_dbi_dim_data(l_i).materialized :='YES';
2413 g_dbi_dim_data(l_i).user_code_col := NULL;
2414 g_dbi_dim_data(l_i).code_col := NULL;
2415 g_dbi_dim_data(l_i).parent1_col := NULL;
2416 g_dbi_dim_data(l_i).parent2_col := NULL;
2417 g_dbi_dim_data(l_i).parent3_col := NULL;
2418 g_dbi_dim_data(l_i).parent4_col := NULL;
2419 g_dbi_dim_data(l_i).parent5_col := NULL;
2420
2421 l_i := l_i + 1;
2422 l_owner:=bsc_im_utils.get_table_owner('ENI_DENORM_HIERARCHIES');
2423 g_dbi_dim_data(l_i).short_name := 'ENI_ITEM_VBH_CAT';
2424 g_dbi_dim_data(l_i).table_name := 'BSC_D_ENI_ITEM_VBH_CAT_T';
2425 g_dbi_dim_data(l_i).from_clause := l_owner||'.eni_denorm_hierarchies';
2426 g_dbi_dim_data(l_i).where_clause := ' where imm_child_id=child_id and imm_child_id=parent_id ';
2427 g_dbi_dim_data(l_i).recursive_dim := 'YES';
2428 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2429 g_dbi_dim_data(l_i).source_object := NULL;
2430 g_dbi_dim_data(l_i).materialized :='NO';
2431 g_dbi_dim_data(l_i).user_code_col := 'parent_id';
2432 g_dbi_dim_data(l_i).code_col := 'parent_id';
2433 g_dbi_dim_data(l_i).parent1_col := NULL;
2434 g_dbi_dim_data(l_i).parent2_col := NULL;
2435 g_dbi_dim_data(l_i).parent3_col := NULL;
2436 g_dbi_dim_data(l_i).parent4_col := NULL;
2437 g_dbi_dim_data(l_i).parent5_col := NULL;
2438 -- for rec dim
2439 g_dbi_dim_data(l_i).child_col:='child';
2440 g_dbi_dim_data(l_i).parent_col:='parent';
2441 g_dbi_dim_data(l_i).parent_level_col:='parent_level';
2442 g_dbi_dim_data(l_i).denorm_table:='BSC_D_ENI_ITEM_VBH_CAT_DT';
2443 g_dbi_dim_data(l_i).child_col_src:='CHILD_ID';
2444 g_dbi_dim_data(l_i).parent_col_src:='PARENT_ID';
2445 g_dbi_dim_data(l_i).parent_level_src_col:=null;
2446 g_dbi_dim_data(l_i).denorm_src_object:='ENI_DENORM_HIERARCHIES';
2447 --g_dbi_dim_data(l_i).top_n_levels:=fnd_profile.value('BSC_ADVANCED_SUMMARIZATION_LEVEL');--read adv sum profile
2448 g_dbi_dim_data(l_i).top_n_levels:=100000;--denorm all levels
2449 g_dbi_dim_data(l_i).top_n_levels_in_mv:=fnd_profile.value('BSC_ADVANCED_SUMMARIZATION_LEVEL');--read adv sum profile
2450 -----
2451 g_dbi_dim_data(l_i).source_to_check := NULL;
2452 g_dbi_dim_data(l_i).denorm_source_to_check := 'ENI_DENORM_HIERARCHIES';
2453
2454 --3636879
2455 l_i := l_i + 1;
2456 g_dbi_dim_data(l_i).short_name := 'ENI_ITEM_ITM_CAT';
2457 g_dbi_dim_data(l_i).table_name := 'BSC_D_ENI_ITEM_ITM_CAT_T';
2458 g_dbi_dim_data(l_i).from_clause := null;
2459 g_dbi_dim_data(l_i).where_clause := null;
2460 g_dbi_dim_data(l_i).recursive_dim := 'YES';
2461 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2462 g_dbi_dim_data(l_i).source_object := NULL;
2463 g_dbi_dim_data(l_i).materialized :='YES';
2464 g_dbi_dim_data(l_i).user_code_col := null;
2465 g_dbi_dim_data(l_i).code_col := null;
2466 g_dbi_dim_data(l_i).parent1_col := NULL;
2467 g_dbi_dim_data(l_i).parent2_col := NULL;
2468 g_dbi_dim_data(l_i).parent3_col := NULL;
2469 g_dbi_dim_data(l_i).parent4_col := NULL;
2470 g_dbi_dim_data(l_i).parent5_col := NULL;
2471 -- for rec dim
2472 g_dbi_dim_data(l_i).child_col:='child';
2473 g_dbi_dim_data(l_i).parent_col:='parent';
2474 g_dbi_dim_data(l_i).parent_level_col:='parent_level';
2475 g_dbi_dim_data(l_i).denorm_table:='BSC_D_ENI_ITEM_ITM_CAT_DT';
2476 g_dbi_dim_data(l_i).child_col_src:='ID';
2477 g_dbi_dim_data(l_i).parent_col_src:='PARENT_ID';
2478 g_dbi_dim_data(l_i).parent_level_src_col:=null;
2479 g_dbi_dim_data(l_i).denorm_src_object:='ENI_ITEM_ITM_CAT_V';
2480 --g_dbi_dim_data(l_i).top_n_levels:=fnd_profile.value('BSC_ADVANCED_SUMMARIZATION_LEVEL');--read adv sum profile
2481 g_dbi_dim_data(l_i).top_n_levels:=100000;--denorm all levels
2482 g_dbi_dim_data(l_i).top_n_levels_in_mv:=fnd_profile.value('BSC_ADVANCED_SUMMARIZATION_LEVEL');--read adv sum profile
2483 ---
2484
2485 l_i := l_i + 1;
2486 g_dbi_dim_data(l_i).short_name := 'REQUESTTYPE';
2487 g_dbi_dim_data(l_i).table_name := 'BSC_D_REQUESTTYPE_T';
2488 g_dbi_dim_data(l_i).from_clause := NULL;
2489 g_dbi_dim_data(l_i).where_clause := NULL;
2490 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2491 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2492 g_dbi_dim_data(l_i).source_object := NULL;
2493 g_dbi_dim_data(l_i).materialized :='YES';
2494 g_dbi_dim_data(l_i).user_code_col := NULL;
2495 g_dbi_dim_data(l_i).code_col := NULL;
2496 g_dbi_dim_data(l_i).parent1_col := NULL;
2497 g_dbi_dim_data(l_i).parent2_col := NULL;
2498 g_dbi_dim_data(l_i).parent3_col := NULL;
2499 g_dbi_dim_data(l_i).parent4_col := NULL;
2500 g_dbi_dim_data(l_i).parent5_col := NULL;
2501
2502 l_i := l_i + 1;
2503 g_dbi_dim_data(l_i).short_name := 'CAMPAIGN';
2504 g_dbi_dim_data(l_i).table_name := 'BSC_D_CAMPAIGN_T';
2505 g_dbi_dim_data(l_i).from_clause := NULL;
2506 g_dbi_dim_data(l_i).where_clause := NULL;
2507 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2508 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2509 g_dbi_dim_data(l_i).source_object := NULL;
2510 g_dbi_dim_data(l_i).materialized :='YES';
2511 g_dbi_dim_data(l_i).user_code_col := NULL;
2512 g_dbi_dim_data(l_i).code_col := NULL;
2513 g_dbi_dim_data(l_i).parent1_col := NULL;
2514 g_dbi_dim_data(l_i).parent2_col := NULL;
2515 g_dbi_dim_data(l_i).parent3_col := NULL;
2516 g_dbi_dim_data(l_i).parent4_col := NULL;
2517 g_dbi_dim_data(l_i).parent5_col := NULL;
2518
2519 l_i := l_i + 1;
2520 g_dbi_dim_data(l_i).short_name := 'COUNTRY';
2521 g_dbi_dim_data(l_i).table_name := 'BSC_D_COUNTRY_T';
2522 g_dbi_dim_data(l_i).from_clause := NULL;
2523 g_dbi_dim_data(l_i).where_clause := NULL;
2524 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2525 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2526 g_dbi_dim_data(l_i).source_object := NULL;
2527 g_dbi_dim_data(l_i).materialized :='YES';
2528 g_dbi_dim_data(l_i).user_code_col := NULL;
2529 g_dbi_dim_data(l_i).code_col := NULL;
2530 g_dbi_dim_data(l_i).parent1_col := NULL;
2531 g_dbi_dim_data(l_i).parent2_col := NULL;
2532 g_dbi_dim_data(l_i).parent3_col := NULL;
2533 g_dbi_dim_data(l_i).parent4_col := NULL;
2534 g_dbi_dim_data(l_i).parent5_col := NULL;
2535
2536 l_i := l_i + 1;
2537 g_dbi_dim_data(l_i).short_name := 'HRI_PER_USRDR_H';
2538 g_dbi_dim_data(l_i).table_name := 'BSC_D_HRI_PER_USRDR_H_T';
2539 g_dbi_dim_data(l_i).from_clause := NULL;
2540 g_dbi_dim_data(l_i).where_clause := NULL;
2541 g_dbi_dim_data(l_i).recursive_dim := 'YES';
2542 g_dbi_dim_data(l_i).date_tracked_dim := 'YES';
2543 g_dbi_dim_data(l_i).source_object := 'HRI_CS_SUPH';
2544 g_dbi_dim_data(l_i).materialized :='YES';
2545 g_dbi_dim_data(l_i).user_code_col := 'sub_person_id';
2546 g_dbi_dim_data(l_i).code_col := 'sub_person_id';
2547 g_dbi_dim_data(l_i).parent1_col := NULL;
2548 g_dbi_dim_data(l_i).parent2_col := NULL;
2549 g_dbi_dim_data(l_i).parent3_col := NULL;
2550 g_dbi_dim_data(l_i).parent4_col := NULL;
2551 g_dbi_dim_data(l_i).parent5_col := NULL;
2552 -- for rec dim
2553 g_dbi_dim_data(l_i).child_col:='child';
2554 g_dbi_dim_data(l_i).parent_col:='parent';
2555 g_dbi_dim_data(l_i).parent_level_col:='parent_level';
2556 g_dbi_dim_data(l_i).denorm_table:='BSC_D_HRI_PER_USRDR_H_DT';
2557 g_dbi_dim_data(l_i).child_col_src:='SUB_PERSON_ID';
2558 g_dbi_dim_data(l_i).parent_col_src:='SUP_PERSON_ID';
2559 g_dbi_dim_data(l_i).parent_level_src_col:='SUP_LEVEL';
2560 g_dbi_dim_data(l_i).denorm_src_object:='HRI_CS_SUPH';
2561 --g_dbi_dim_data(l_i).top_n_levels:=fnd_profile.value('BSC_ADVANCED_SUMMARIZATION_LEVEL');--read adv sum profile
2562 g_dbi_dim_data(l_i).top_n_levels:=100000;--denorm all levels
2563 g_dbi_dim_data(l_i).top_n_levels_in_mv:=fnd_profile.value('BSC_ADVANCED_SUMMARIZATION_LEVEL');--read adv sum profile
2564 ---
2565 g_dbi_dim_data(l_i).source_to_check := 'HRI_CS_SUPH';
2566 g_dbi_dim_data(l_i).denorm_source_to_check := 'HRI_CS_SUPH';
2567
2568 l_i := l_i + 1;
2569 g_dbi_dim_data(l_i).short_name := 'ORGANIZATION';
2570 g_dbi_dim_data(l_i).table_name := 'BSC_D_ORGANIZATION_T';
2571 g_dbi_dim_data(l_i).from_clause := NULL;
2572 g_dbi_dim_data(l_i).where_clause := NULL;
2573 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2574 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2575 g_dbi_dim_data(l_i).source_object := NULL;
2576 g_dbi_dim_data(l_i).materialized :='YES';
2577 g_dbi_dim_data(l_i).user_code_col := NULL;
2578 g_dbi_dim_data(l_i).code_col := NULL;
2579 g_dbi_dim_data(l_i).parent1_col := NULL;
2580 g_dbi_dim_data(l_i).parent2_col := NULL;
2581 g_dbi_dim_data(l_i).parent3_col := NULL;
2582 g_dbi_dim_data(l_i).parent4_col := NULL;
2583 g_dbi_dim_data(l_i).parent5_col := NULL;
2584
2585 l_i := l_i + 1;
2586 g_dbi_dim_data(l_i).short_name := 'BIX_CALL_CLASSIFICATION';
2587 g_dbi_dim_data(l_i).table_name := 'BSC_D_BIX_CALL_CLASSIFICATIO_T';
2588 g_dbi_dim_data(l_i).from_clause := NULL;
2589 g_dbi_dim_data(l_i).where_clause := NULL;
2590 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2591 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2592 g_dbi_dim_data(l_i).source_object := NULL;
2593 g_dbi_dim_data(l_i).materialized :='YES';
2594 g_dbi_dim_data(l_i).user_code_col := NULL;
2595 g_dbi_dim_data(l_i).code_col := NULL;
2596 g_dbi_dim_data(l_i).parent1_col := NULL;
2597 g_dbi_dim_data(l_i).parent2_col := NULL;
2598 g_dbi_dim_data(l_i).parent3_col := NULL;
2599 g_dbi_dim_data(l_i).parent4_col := NULL;
2600 g_dbi_dim_data(l_i).parent5_col := NULL;
2601
2602 l_i := l_i + 1;
2603 g_dbi_dim_data(l_i).short_name := 'BIX_CALL_CENTER';
2604 g_dbi_dim_data(l_i).table_name := 'BSC_D_BIX_CALL_CENTER_T';
2605 g_dbi_dim_data(l_i).from_clause := NULL;
2606 g_dbi_dim_data(l_i).where_clause := NULL;
2607 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2608 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2609 g_dbi_dim_data(l_i).source_object := NULL;
2610 g_dbi_dim_data(l_i).materialized :='YES';
2611 g_dbi_dim_data(l_i).user_code_col := NULL;
2612 g_dbi_dim_data(l_i).code_col := NULL;
2613 g_dbi_dim_data(l_i).parent1_col := NULL;
2614 g_dbi_dim_data(l_i).parent2_col := NULL;
2615 g_dbi_dim_data(l_i).parent3_col := NULL;
2616 g_dbi_dim_data(l_i).parent4_col := NULL;
2617 g_dbi_dim_data(l_i).parent5_col := NULL;
2618
2619 l_i := l_i + 1;
2620 g_dbi_dim_data(l_i).short_name := 'BIX_DNIS';
2621 g_dbi_dim_data(l_i).table_name := 'BSC_D_BIX_DNIS_T';
2622 g_dbi_dim_data(l_i).from_clause := NULL;
2623 g_dbi_dim_data(l_i).where_clause := NULL;
2624 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2625 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2626 g_dbi_dim_data(l_i).source_object := NULL;
2627 g_dbi_dim_data(l_i).materialized :='YES';
2628 g_dbi_dim_data(l_i).user_code_col := NULL;
2629 g_dbi_dim_data(l_i).code_col := NULL;
2630 g_dbi_dim_data(l_i).parent1_col := NULL;
2631 g_dbi_dim_data(l_i).parent2_col := NULL;
2632 g_dbi_dim_data(l_i).parent3_col := NULL;
2633 g_dbi_dim_data(l_i).parent4_col := NULL;
2634 g_dbi_dim_data(l_i).parent5_col := NULL;
2635
2636 l_i := l_i + 1;
2637 g_dbi_dim_data(l_i).short_name := 'EMAIL ACCOUNT';
2638 g_dbi_dim_data(l_i).table_name := 'BSC_D_EMAIL_ACCOUNT_T';
2639 g_dbi_dim_data(l_i).from_clause := NULL;
2640 g_dbi_dim_data(l_i).where_clause := NULL;
2641 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2642 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2643 g_dbi_dim_data(l_i).source_object := NULL;
2644 g_dbi_dim_data(l_i).materialized :='YES';
2645 g_dbi_dim_data(l_i).user_code_col := NULL;
2646 g_dbi_dim_data(l_i).code_col := NULL;
2647 g_dbi_dim_data(l_i).parent1_col := NULL;
2648 g_dbi_dim_data(l_i).parent2_col := NULL;
2649 g_dbi_dim_data(l_i).parent3_col := NULL;
2650 g_dbi_dim_data(l_i).parent4_col := NULL;
2651 g_dbi_dim_data(l_i).parent5_col := NULL;
2652
2653 l_i := l_i + 1;
2654 g_dbi_dim_data(l_i).short_name := 'EMAIL CLASSIFICATION';
2655 g_dbi_dim_data(l_i).table_name := 'BSC_D_EMAIL_CLASSIFICATION_T';
2656 g_dbi_dim_data(l_i).from_clause := NULL;
2657 g_dbi_dim_data(l_i).where_clause := NULL;
2658 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2659 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2660 g_dbi_dim_data(l_i).source_object := NULL;
2661 g_dbi_dim_data(l_i).materialized :='YES';
2662 g_dbi_dim_data(l_i).user_code_col := NULL;
2663 g_dbi_dim_data(l_i).code_col := NULL;
2664 g_dbi_dim_data(l_i).parent1_col := NULL;
2665 g_dbi_dim_data(l_i).parent2_col := NULL;
2666 g_dbi_dim_data(l_i).parent3_col := NULL;
2667 g_dbi_dim_data(l_i).parent4_col := NULL;
2668 g_dbi_dim_data(l_i).parent5_col := NULL;
2669
2670 l_i := l_i + 1;
2671 l_owner:=bsc_im_utils.get_table_owner('ENI_OLTP_ITEM_STAR');
2672 g_dbi_dim_data(l_i).short_name := 'ENI_ITEM_ORG';
2673 g_dbi_dim_data(l_i).table_name := 'BSC_D_ENI_ITEM_ORG_T';
2674 g_dbi_dim_data(l_i).from_clause := l_owner||'.ENI_OLTP_ITEM_STAR';
2675 g_dbi_dim_data(l_i).where_clause := NULL;
2676 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2677 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2678 g_dbi_dim_data(l_i).source_object := NULL;
2679 g_dbi_dim_data(l_i).materialized :='NO';
2680 g_dbi_dim_data(l_i).user_code_col := 'ID';
2681 g_dbi_dim_data(l_i).code_col := 'ID';
2682 g_dbi_dim_data(l_i).parent1_col := NULL;
2683 g_dbi_dim_data(l_i).parent2_col := NULL;
2684 g_dbi_dim_data(l_i).parent3_col := NULL;
2685 g_dbi_dim_data(l_i).parent4_col := NULL;
2686 g_dbi_dim_data(l_i).parent5_col := NULL;
2687
2688 l_i := l_i + 1;
2689 g_dbi_dim_data(l_i).short_name := 'FII_OPERATING_UNITS';
2690 g_dbi_dim_data(l_i).table_name := 'BSC_D_FII_OPERATING_UNITS_T';
2691 g_dbi_dim_data(l_i).from_clause := NULL;
2692 g_dbi_dim_data(l_i).where_clause := NULL;
2693 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2694 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2695 g_dbi_dim_data(l_i).source_object := NULL;
2696 g_dbi_dim_data(l_i).materialized :='YES';
2697 g_dbi_dim_data(l_i).user_code_col := NULL;
2698 g_dbi_dim_data(l_i).code_col := NULL;
2699 g_dbi_dim_data(l_i).parent1_col := NULL;
2700 g_dbi_dim_data(l_i).parent2_col := NULL;
2701 g_dbi_dim_data(l_i).parent3_col := NULL;
2702 g_dbi_dim_data(l_i).parent4_col := NULL;
2703 g_dbi_dim_data(l_i).parent5_col := NULL;
2704
2705 l_i := l_i + 1;
2706 g_dbi_dim_data(l_i).short_name := 'STORE';
2707 g_dbi_dim_data(l_i).table_name := 'BSC_D_STORE_T';
2708 g_dbi_dim_data(l_i).from_clause := NULL;
2709 g_dbi_dim_data(l_i).where_clause := NULL;
2710 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2711 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2712 g_dbi_dim_data(l_i).source_object := NULL;
2713 g_dbi_dim_data(l_i).materialized :='YES';
2714 g_dbi_dim_data(l_i).user_code_col := NULL;
2715 g_dbi_dim_data(l_i).code_col := NULL;
2716 g_dbi_dim_data(l_i).parent1_col := NULL;
2717 g_dbi_dim_data(l_i).parent2_col := NULL;
2718 g_dbi_dim_data(l_i).parent3_col := NULL;
2719 g_dbi_dim_data(l_i).parent4_col := NULL;
2720 g_dbi_dim_data(l_i).parent5_col := NULL;
2721
2722 l_i := l_i + 1;
2723 g_dbi_dim_data(l_i).short_name := 'PLAN_SNAPSHOT';
2724 g_dbi_dim_data(l_i).table_name := 'BSC_D_PLAN_SNAPSHOT_T';
2725 g_dbi_dim_data(l_i).from_clause := NULL;
2726 g_dbi_dim_data(l_i).where_clause := NULL;
2727 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2728 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2729 g_dbi_dim_data(l_i).source_object := NULL;
2730 g_dbi_dim_data(l_i).materialized :='YES';
2731 g_dbi_dim_data(l_i).user_code_col := NULL;
2732 g_dbi_dim_data(l_i).code_col := NULL;
2733 g_dbi_dim_data(l_i).parent1_col := NULL;
2734 g_dbi_dim_data(l_i).parent2_col := NULL;
2735 g_dbi_dim_data(l_i).parent3_col := NULL;
2736 g_dbi_dim_data(l_i).parent4_col := NULL;
2737 g_dbi_dim_data(l_i).parent5_col := NULL;
2738
2739 l_i := l_i + 1;
2740 g_dbi_dim_data(l_i).short_name := 'PJI_ORGANIZATIONS';
2741 g_dbi_dim_data(l_i).table_name := 'BSC_D_PJI_ORGANIZATIONS_T';
2742 g_dbi_dim_data(l_i).from_clause := NULL;
2743 g_dbi_dim_data(l_i).where_clause := NULL;
2744 g_dbi_dim_data(l_i).recursive_dim := 'YES';
2745 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2746 g_dbi_dim_data(l_i).source_object := NULL;
2747 g_dbi_dim_data(l_i).materialized :='YES';
2748 g_dbi_dim_data(l_i).user_code_col := NULL;
2749 g_dbi_dim_data(l_i).code_col := NULL;
2750 g_dbi_dim_data(l_i).parent1_col := NULL;
2751 g_dbi_dim_data(l_i).parent2_col := NULL;
2752 g_dbi_dim_data(l_i).parent3_col := NULL;
2753 g_dbi_dim_data(l_i).parent4_col := NULL;
2754 g_dbi_dim_data(l_i).parent5_col := NULL;
2755 -- for rec dim
2756 g_dbi_dim_data(l_i).child_col:='child';
2757 g_dbi_dim_data(l_i).parent_col:='parent';
2758 g_dbi_dim_data(l_i).parent_level_col:='parent_level';
2759 g_dbi_dim_data(l_i).denorm_table:='BSC_D_PJI_ORGANIZATIONS_DT';
2760 g_dbi_dim_data(l_i).child_col_src:='ID';
2761 g_dbi_dim_data(l_i).parent_col_src:='PARENT_ID';
2762 g_dbi_dim_data(l_i).parent_level_src_col:=null;
2763 g_dbi_dim_data(l_i).denorm_src_object:='PJI_ORGANIZATIONS_V';
2764 --g_dbi_dim_data(l_i).top_n_levels:=fnd_profile.value('BSC_ADVANCED_SUMMARIZATION_LEVEL');--read adv sum profile
2765 g_dbi_dim_data(l_i).top_n_levels:=100000;--denorm all levels
2766 g_dbi_dim_data(l_i).top_n_levels_in_mv:=fnd_profile.value('BSC_ADVANCED_SUMMARIZATION_LEVEL');--read adv sum profile
2767 ---
2768
2769 l_i := l_i + 1;
2770 g_dbi_dim_data(l_i).short_name := 'POA_COMMODITIES';
2771 g_dbi_dim_data(l_i).table_name := 'BSC_D_POA_COMMODITIES_T';
2772 g_dbi_dim_data(l_i).from_clause := NULL;
2773 g_dbi_dim_data(l_i).where_clause := NULL;
2774 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2775 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2776 g_dbi_dim_data(l_i).source_object := 'PO_commodities_B';
2777 g_dbi_dim_data(l_i).materialized :='YES';
2778 g_dbi_dim_data(l_i).user_code_col := 'commodity_id';
2779 g_dbi_dim_data(l_i).code_col := 'commodity_id';
2780 g_dbi_dim_data(l_i).parent1_col := NULL;
2781 g_dbi_dim_data(l_i).parent2_col := NULL;
2782 g_dbi_dim_data(l_i).parent3_col := NULL;
2783 g_dbi_dim_data(l_i).parent4_col := NULL;
2784 g_dbi_dim_data(l_i).parent5_col := NULL;
2785
2786
2787 -- Enh#4316042 New dbi dimensions
2788 l_i := l_i + 1;
2789 g_dbi_dim_data(l_i).short_name := 'HRI_CL_RQNVAC_VACNCY';
2790 g_dbi_dim_data(l_i).table_name := 'BSC_D_HRI_CL_RQNVAC_VACNCY_T';
2791 g_dbi_dim_data(l_i).from_clause := NULL;
2792 g_dbi_dim_data(l_i).where_clause := NULL;
2793 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2794 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2795 g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.vacancy_id) USER_CODE,'||
2796 ' to_char(s.vacancy_id) CODE'||
2797 ' FROM per_all_vacancies s'||
2798 ' UNION ALL SELECT TO_CHAR(-1) USER_CODE, TO_CHAR(-1) CODE'||
2799 ' FROM dual) PER_ALL_VACANCIES_S';
2800 g_dbi_dim_data(l_i).source_object_alias := 'PER_ALL_VACANCIES_S';
2801 g_dbi_dim_data(l_i).materialized :='YES';
2802 g_dbi_dim_data(l_i).user_code_col := NULL;
2803 g_dbi_dim_data(l_i).code_col := NULL;
2804 g_dbi_dim_data(l_i).parent1_col := NULL;
2805 g_dbi_dim_data(l_i).parent2_col := NULL;
2806 g_dbi_dim_data(l_i).parent3_col := NULL;
2807 g_dbi_dim_data(l_i).parent4_col := NULL;
2808 g_dbi_dim_data(l_i).parent5_col := NULL;
2809
2810 l_i := l_i + 1;
2811 g_dbi_dim_data(l_i).short_name := 'HRI_GRADE_BX';
2812 g_dbi_dim_data(l_i).table_name := 'BSC_D_HRI_GRADE_BX_T';
2813 g_dbi_dim_data(l_i).from_clause := NULL;
2814 g_dbi_dim_data(l_i).where_clause := NULL;
2815 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2816 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2817 g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.grade_id) USER_CODE,'||
2818 ' to_char(s.grade_id) CODE'||
2819 ' FROM per_grades s'||
2820 ' UNION ALL SELECT TO_CHAR(-1) USER_CODE, TO_CHAR(-1) CODE'||
2821 ' FROM dual) PER_GRADES_S';
2822 g_dbi_dim_data(l_i).source_object_alias := 'PER_GRADES_S';
2823 g_dbi_dim_data(l_i).materialized :='YES';
2824 g_dbi_dim_data(l_i).user_code_col := NULL;
2825 g_dbi_dim_data(l_i).code_col := NULL;
2826 g_dbi_dim_data(l_i).parent1_col := NULL;
2827 g_dbi_dim_data(l_i).parent2_col := NULL;
2828 g_dbi_dim_data(l_i).parent3_col := NULL;
2829 g_dbi_dim_data(l_i).parent4_col := NULL;
2830 g_dbi_dim_data(l_i).parent5_col := NULL;
2831
2832 l_i := l_i + 1;
2833 g_dbi_dim_data(l_i).short_name := 'HRI_JOB_BX';
2834 g_dbi_dim_data(l_i).table_name := 'BSC_D_HRI_JOB_BX_T';
2835 g_dbi_dim_data(l_i).from_clause := NULL;
2836 g_dbi_dim_data(l_i).where_clause := NULL;
2837 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2838 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2839 g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.job_id) USER_CODE,'||
2840 ' to_char(s.job_id) CODE'||
2841 ' FROM per_jobs s'||
2842 ' UNION ALL SELECT TO_CHAR(-1) USER_CODE, TO_CHAR(-1) CODE'||
2843 ' FROM dual) PER_JOBS_S';
2844 g_dbi_dim_data(l_i).source_object_alias := 'PER_JOBS_S';
2845 g_dbi_dim_data(l_i).materialized :='YES';
2846 g_dbi_dim_data(l_i).user_code_col := NULL;
2847 g_dbi_dim_data(l_i).code_col := NULL;
2848 g_dbi_dim_data(l_i).parent1_col := NULL;
2849 g_dbi_dim_data(l_i).parent2_col := NULL;
2850 g_dbi_dim_data(l_i).parent3_col := NULL;
2851 g_dbi_dim_data(l_i).parent4_col := NULL;
2852 g_dbi_dim_data(l_i).parent5_col := NULL;
2853
2854 l_i := l_i + 1;
2855 g_dbi_dim_data(l_i).short_name := 'HRI_ORG_HRCYVRSN_BX';
2856 g_dbi_dim_data(l_i).table_name := 'BSC_D_HRI_ORG_HRCYVRSN_BX_T';
2857 g_dbi_dim_data(l_i).from_clause := NULL;
2858 g_dbi_dim_data(l_i).where_clause := NULL;
2859 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2860 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2861 g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.org_structure_version_id) USER_CODE,'||
2862 ' to_char(s.org_structure_version_id) CODE'||
2863 ' FROM per_org_structure_versions s) PER_ORG_STRUCTURE_VERSIONS_S';
2864 g_dbi_dim_data(l_i).source_object_alias := 'PER_ORG_STRUCTURE_VERSIONS_S';
2865 g_dbi_dim_data(l_i).materialized :='YES';
2866 g_dbi_dim_data(l_i).user_code_col := NULL;
2867 g_dbi_dim_data(l_i).code_col := NULL;
2868 g_dbi_dim_data(l_i).parent1_col := NULL;
2869 g_dbi_dim_data(l_i).parent2_col := NULL;
2870 g_dbi_dim_data(l_i).parent3_col := NULL;
2871 g_dbi_dim_data(l_i).parent4_col := NULL;
2872 g_dbi_dim_data(l_i).parent5_col := NULL;
2873
2874 l_i := l_i + 1;
2875 g_dbi_dim_data(l_i).short_name := 'HRI_ORG_HRCY_BX';
2876 g_dbi_dim_data(l_i).table_name := 'BSC_D_HRI_ORG_HRCY_BX_T';
2877 g_dbi_dim_data(l_i).from_clause := NULL;
2878 g_dbi_dim_data(l_i).where_clause := NULL;
2879 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2880 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2881 g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.organization_structure_id) USER_CODE,'||
2882 ' to_char(s.organization_structure_id) CODE'||
2883 ' FROM per_organization_structures s) PER_ORGANIZATION_STRUCTURES_S';
2884 g_dbi_dim_data(l_i).source_object_alias := 'PER_ORGANIZATION_STRUCTURES_S';
2885 g_dbi_dim_data(l_i).materialized :='YES';
2886 g_dbi_dim_data(l_i).user_code_col := NULL;
2887 g_dbi_dim_data(l_i).code_col := NULL;
2888 g_dbi_dim_data(l_i).parent1_col := NULL;
2889 g_dbi_dim_data(l_i).parent2_col := NULL;
2890 g_dbi_dim_data(l_i).parent3_col := NULL;
2891 g_dbi_dim_data(l_i).parent4_col := NULL;
2892 g_dbi_dim_data(l_i).parent5_col := NULL;
2893
2894 l_i := l_i + 1;
2895 g_dbi_dim_data(l_i).short_name := 'HRI_ORG_HR_H';
2896 g_dbi_dim_data(l_i).table_name := 'BSC_D_HRI_ORG_HR_H_T';
2897 g_dbi_dim_data(l_i).from_clause := NULL;
2898 g_dbi_dim_data(l_i).where_clause := NULL;
2899 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2900 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2901 g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) parallel (s1) */ TO_CHAR(s.organization_id) USER_CODE,'||
2902 ' to_char(s.organization_id) CODE'||
2903 ' FROM hr_all_organization_units s, hr_organization_information s1'||
2904 ' WHERE s.organization_id = s1.organization_id AND'||
2905 ' s1.org_information1 = ''HR_ORG'''||
2906 ' UNION ALL SELECT TO_CHAR(-1) USER_CODE, TO_CHAR(-1) CODE'||
2907 ' FROM dual) HR_ALL_ORGANIZATION_UNITS_S';
2908 g_dbi_dim_data(l_i).source_object_alias := 'HR_ALL_ORGANIZATION_UNITS_S';
2909 g_dbi_dim_data(l_i).materialized :='YES';
2910 g_dbi_dim_data(l_i).user_code_col := NULL;
2911 g_dbi_dim_data(l_i).code_col := NULL;
2912 g_dbi_dim_data(l_i).parent1_col := NULL;
2913 g_dbi_dim_data(l_i).parent2_col := NULL;
2914 g_dbi_dim_data(l_i).parent3_col := NULL;
2915 g_dbi_dim_data(l_i).parent4_col := NULL;
2916 g_dbi_dim_data(l_i).parent5_col := NULL;
2917
2918 l_i := l_i + 1;
2919 g_dbi_dim_data(l_i).short_name := 'HRI_ORG_HR_HX';
2920 g_dbi_dim_data(l_i).table_name := 'BSC_D_HRI_ORG_HR_HX_T';
2921 g_dbi_dim_data(l_i).from_clause := NULL;
2922 g_dbi_dim_data(l_i).where_clause := NULL;
2923 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2924 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2925 g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) parallel (s1) */ TO_CHAR(s.organization_id) USER_CODE,'||
2926 ' to_char(s.organization_id) CODE'||
2927 ' FROM hr_all_organization_units s, hr_organization_information s1'||
2928 ' WHERE s.organization_id = s1.organization_id AND'||
2929 ' s1.org_information1 = ''HR_ORG'''||
2930 ' UNION ALL SELECT TO_CHAR(-1) USER_CODE, TO_CHAR(-1) CODE'||
2931 ' FROM dual) HR_ALL_ORGANIZATION_UNITS_S';
2932 g_dbi_dim_data(l_i).source_object_alias := 'HR_ALL_ORGANIZATION_UNITS_S';
2933 g_dbi_dim_data(l_i).materialized :='YES';
2934 g_dbi_dim_data(l_i).user_code_col := NULL;
2935 g_dbi_dim_data(l_i).code_col := NULL;
2936 g_dbi_dim_data(l_i).parent1_col := NULL;
2937 g_dbi_dim_data(l_i).parent2_col := NULL;
2938 g_dbi_dim_data(l_i).parent3_col := NULL;
2939 g_dbi_dim_data(l_i).parent4_col := NULL;
2940 g_dbi_dim_data(l_i).parent5_col := NULL;
2941
2942 l_i := l_i + 1;
2943 g_dbi_dim_data(l_i).short_name := 'HRI_ORG_INHV_H';
2944 g_dbi_dim_data(l_i).table_name := 'BSC_D_HRI_ORG_INHV_H_T';
2945 g_dbi_dim_data(l_i).from_clause := NULL;
2946 g_dbi_dim_data(l_i).where_clause := NULL;
2947 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2948 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
2949 g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) parallel (s1) */ DISTINCT'||
2950 ' TO_CHAR(s.organization_id) USER_CODE, to_char(s.organization_id) CODE'||
2951 ' FROM hr_all_organization_units s, hri_org_hrchy_summary s1'||
2952 ' WHERE s.organization_id = s1.organization_id AND s1.sub_org_relative_level = 0'||
2953 ') HR_ALL_ORGANIZATION_UNITS_S';
2954 g_dbi_dim_data(l_i).source_object_alias := 'HR_ALL_ORGANIZATION_UNITS_S';
2955 g_dbi_dim_data(l_i).materialized :='YES';
2956 g_dbi_dim_data(l_i).user_code_col := NULL;
2957 g_dbi_dim_data(l_i).code_col := NULL;
2958 g_dbi_dim_data(l_i).parent1_col := NULL;
2959 g_dbi_dim_data(l_i).parent2_col := NULL;
2960 g_dbi_dim_data(l_i).parent3_col := NULL;
2961 g_dbi_dim_data(l_i).parent4_col := NULL;
2962 g_dbi_dim_data(l_i).parent5_col := NULL;
2963
2964 l_i := l_i + 1;
2965 g_dbi_dim_data(l_i).short_name := 'HRI_PER_EMP_HX';
2966 g_dbi_dim_data(l_i).table_name := 'BSC_D_HRI_PER_EMP_HX_T';
2967 g_dbi_dim_data(l_i).from_clause := NULL;
2968 g_dbi_dim_data(l_i).where_clause := NULL;
2969 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2970 g_dbi_dim_data(l_i).date_tracked_dim := 'YES';
2971 g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.person_id) USER_CODE,'||
2972 ' to_char(s.person_id) CODE,s.effective_start_date, s.effective_end_date'||
2973 ' FROM per_all_people_f s'||
2974 ' WHERE current_employee_flag = ''Y'' AND'||
2975 ' trunc(sysdate) BETWEEN effective_start_date AND effective_end_date'||
2976 ' UNION ALL SELECT TO_CHAR(-1) USER_CODE, TO_CHAR(-1) CODE,'||
2977 ' hr_general.start_of_time effective_start_date,'||
2978 ' hr_general.end_of_time effective_end_date'||
2979 ' FROM dual) PER_ALL_PEOPLE_F_S';
2980 g_dbi_dim_data(l_i).source_object_alias := 'PER_ALL_PEOPLE_F_S';
2981 g_dbi_dim_data(l_i).materialized :='YES';
2982 g_dbi_dim_data(l_i).user_code_col := NULL;
2983 g_dbi_dim_data(l_i).code_col := NULL;
2984 g_dbi_dim_data(l_i).parent1_col := NULL;
2985 g_dbi_dim_data(l_i).parent2_col := NULL;
2986 g_dbi_dim_data(l_i).parent3_col := NULL;
2987 g_dbi_dim_data(l_i).parent4_col := NULL;
2988 g_dbi_dim_data(l_i).parent5_col := NULL;
2989
2990 l_i := l_i + 1;
2991 g_dbi_dim_data(l_i).short_name := 'HRI_PER_HX';
2992 g_dbi_dim_data(l_i).table_name := 'BSC_D_HRI_PER_HX_T';
2993 g_dbi_dim_data(l_i).from_clause := NULL;
2994 g_dbi_dim_data(l_i).where_clause := NULL;
2995 g_dbi_dim_data(l_i).recursive_dim := 'NO';
2996 g_dbi_dim_data(l_i).date_tracked_dim := 'YES';
2997 g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.person_id) USER_CODE,'||
2998 ' to_char(s.person_id) CODE,s.effective_start_date, s.effective_end_date'||
2999 ' FROM per_all_people_f s'||
3000 ' WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date'||
3001 ' ) PER_ALL_PEOPLE_F_S';
3002 g_dbi_dim_data(l_i).source_object_alias := 'PER_ALL_PEOPLE_F_S';
3003 g_dbi_dim_data(l_i).materialized :='YES';
3004 g_dbi_dim_data(l_i).user_code_col := NULL;
3005 g_dbi_dim_data(l_i).code_col := NULL;
3006 g_dbi_dim_data(l_i).parent1_col := NULL;
3007 g_dbi_dim_data(l_i).parent2_col := NULL;
3008 g_dbi_dim_data(l_i).parent3_col := NULL;
3009 g_dbi_dim_data(l_i).parent4_col := NULL;
3010 g_dbi_dim_data(l_i).parent5_col := NULL;
3011
3012 l_i := l_i + 1;
3013 g_dbi_dim_data(l_i).short_name := 'HRI_PER_SUP_HX';
3014 g_dbi_dim_data(l_i).table_name := 'BSC_D_HRI_PER_SUP_HX_T';
3015 g_dbi_dim_data(l_i).from_clause := NULL;
3016 g_dbi_dim_data(l_i).where_clause := NULL;
3017 g_dbi_dim_data(l_i).recursive_dim := 'NO';
3018 g_dbi_dim_data(l_i).date_tracked_dim := 'YES';
3019 g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.person_id) USER_CODE,'||
3020 ' to_char(s.person_id) CODE,s.effective_start_date, s.effective_end_date'||
3021 ' FROM per_all_people_f s'||
3022 ' WHERE (current_employee_flag = ''Y'' OR current_npw_flag = ''Y'') AND'||
3023 ' trunc(sysdate) BETWEEN effective_start_date AND effective_end_date AND'||
3024 ' EXISTS (SELECT -1 FROM per_assignments_f asg, per_people_f peo2'||
3025 ' WHERE s.person_id = asg.supervisor_id AND asg.person_id = peo2.person_id AND'||
3026 ' trunc(sysdate) BETWEEN peo2.effective_start_date AND peo2.effective_end_date AND'||
3027 ' trunc(sysdate) BETWEEN asg.effective_start_date AND asg.effective_end_date AND'||
3028 ' (peo2.current_employee_flag = ''Y'' OR peo2.current_npw_flag = ''Y''))'||
3029 ' UNION ALL SELECT TO_CHAR(-1) USER_CODE, TO_CHAR(-1) CODE,'||
3030 ' hr_general.start_of_time effective_start_date,'||
3031 ' hr_general.end_of_time effective_end_date'||
3032 ' FROM dual) PER_ALL_PEOPLE_F_S';
3033 g_dbi_dim_data(l_i).source_object_alias := 'PER_ALL_PEOPLE_F_S';
3034 g_dbi_dim_data(l_i).materialized :='YES';
3035 g_dbi_dim_data(l_i).user_code_col := NULL;
3036 g_dbi_dim_data(l_i).code_col := NULL;
3037 g_dbi_dim_data(l_i).parent1_col := NULL;
3038 g_dbi_dim_data(l_i).parent2_col := NULL;
3039 g_dbi_dim_data(l_i).parent3_col := NULL;
3040 g_dbi_dim_data(l_i).parent4_col := NULL;
3041 g_dbi_dim_data(l_i).parent5_col := NULL;
3042
3043 l_i := l_i + 1;
3044 g_dbi_dim_data(l_i).short_name := 'HRI_POSITION';
3045 g_dbi_dim_data(l_i).table_name := 'BSC_D_HRI_POSITION_T';
3046 g_dbi_dim_data(l_i).from_clause := NULL;
3047 g_dbi_dim_data(l_i).where_clause := NULL;
3048 g_dbi_dim_data(l_i).recursive_dim := 'NO';
3049 g_dbi_dim_data(l_i).date_tracked_dim := 'YES';
3050 g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.position_id) USER_CODE,'||
3051 ' to_char(s.position_id) CODE,s.date_effective effective_start_date,'||
3052 ' NVL(s.date_end, hr_general.end_of_time) effective_end_date'||
3053 ' FROM per_positions s'||
3054 ' UNION ALL SELECT TO_CHAR(-1) USER_CODE, TO_CHAR(-1) CODE,'||
3055 ' hr_general.start_of_time effective_start_date,'||
3056 ' hr_general.end_of_time effective_end_date'||
3057 ' FROM dual) PER_POSITIONS_S';
3058 g_dbi_dim_data(l_i).source_object_alias := 'PER_POSITIONS_S';
3059 g_dbi_dim_data(l_i).materialized :='YES';
3060 g_dbi_dim_data(l_i).user_code_col := NULL;
3061 g_dbi_dim_data(l_i).code_col := NULL;
3062 g_dbi_dim_data(l_i).parent1_col := NULL;
3063 g_dbi_dim_data(l_i).parent2_col := NULL;
3064 g_dbi_dim_data(l_i).parent3_col := NULL;
3065 g_dbi_dim_data(l_i).parent4_col := NULL;
3066 g_dbi_dim_data(l_i).parent5_col := NULL;
3067
3068 l_i := l_i + 1;
3069 g_dbi_dim_data(l_i).short_name := 'HRI_POSITION_HX';
3070 g_dbi_dim_data(l_i).table_name := 'BSC_D_HRI_POSITION_HX_T';
3071 g_dbi_dim_data(l_i).from_clause := NULL;
3072 g_dbi_dim_data(l_i).where_clause := NULL;
3073 g_dbi_dim_data(l_i).recursive_dim := 'NO';
3074 g_dbi_dim_data(l_i).date_tracked_dim := 'YES';
3075 g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.position_id) USER_CODE,'||
3076 ' to_char(s.position_id) CODE,s.date_effective effective_start_date,'||
3077 ' NVL(s.date_end, hr_general.end_of_time) effective_end_date'||
3078 ' FROM per_positions s'||
3079 ' WHERE TRUNC(sysdate) <= NVL(s.date_end, hr_general.end_of_time)'||
3080 ' UNION ALL SELECT TO_CHAR(-1) USER_CODE, TO_CHAR(-1) CODE,'||
3081 ' hr_general.start_of_time effective_start_date,'||
3082 ' hr_general.end_of_time effective_end_date'||
3083 ' FROM dual) PER_POSITIONS_S';
3084 g_dbi_dim_data(l_i).source_object_alias := 'PER_POSITIONS_S';
3085 g_dbi_dim_data(l_i).materialized :='YES';
3086 g_dbi_dim_data(l_i).user_code_col := NULL;
3087 g_dbi_dim_data(l_i).code_col := NULL;
3088 g_dbi_dim_data(l_i).parent1_col := NULL;
3089 g_dbi_dim_data(l_i).parent2_col := NULL;
3090 g_dbi_dim_data(l_i).parent3_col := NULL;
3091 g_dbi_dim_data(l_i).parent4_col := NULL;
3092 g_dbi_dim_data(l_i).parent5_col := NULL;
3093
3094 l_i := l_i + 1;
3095 g_dbi_dim_data(l_i).short_name := 'LEGAL ENTITY';
3096 g_dbi_dim_data(l_i).table_name := 'BSC_D_LEGAL_ENTITY_T';
3097 g_dbi_dim_data(l_i).from_clause := NULL;
3098 g_dbi_dim_data(l_i).where_clause := NULL;
3099 g_dbi_dim_data(l_i).recursive_dim := 'NO';
3100 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
3101 g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (o) parallel (o2) parallel (o3) */'||
3102 ' TO_CHAR(o.organization_id) USER_CODE, to_char(o.organization_id) CODE'||
3103 ' FROM hr_all_organization_units o, hr_organization_information o2,'||
3104 ' hr_organization_information o3'||
3105 ' WHERE o.organization_id = o2.organization_id AND'||
3106 ' o.organization_id = o3.organization_id (+) AND'||
3107 ' o2.org_information_context = ''CLASS'' AND'||
3108 ' o3.org_information_context (+) = ''Legal Entity Accounting'' AND'||
3109 ' o2.org_information1 = ''HR_LEGAL'' AND'||
3110 ' o2.org_information2 = ''Y'') HR_ALL_ORGANIZATION_UNITS_O';
3111 g_dbi_dim_data(l_i).source_object_alias := 'HR_ALL_ORGANIZATION_UNITS_O';
3112 g_dbi_dim_data(l_i).materialized :='YES';
3113 g_dbi_dim_data(l_i).user_code_col := NULL;
3114 g_dbi_dim_data(l_i).code_col := NULL;
3115 g_dbi_dim_data(l_i).parent1_col := NULL;
3116 g_dbi_dim_data(l_i).parent2_col := NULL;
3117 g_dbi_dim_data(l_i).parent3_col := NULL;
3118 g_dbi_dim_data(l_i).parent4_col := NULL;
3119 g_dbi_dim_data(l_i).parent5_col := NULL;
3120
3121 l_i := l_i + 1;
3122 g_dbi_dim_data(l_i).short_name := 'OPM COMPANY';
3123 g_dbi_dim_data(l_i).table_name := 'BSC_D_OPM_COMPANY_T';
3124 g_dbi_dim_data(l_i).from_clause := NULL;
3125 g_dbi_dim_data(l_i).where_clause := NULL;
3126 g_dbi_dim_data(l_i).recursive_dim := 'NO';
3127 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
3128 g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.co_code) USER_CODE, to_char(s.co_code) CODE'||
3129 ' FROM sy_orgn_mst s'||
3130 ' WHERE s.orgn_code = s.co_code) SY_ORGN_MST_S';
3131 g_dbi_dim_data(l_i).source_object_alias := 'SY_ORGN_MST_S';
3132 g_dbi_dim_data(l_i).materialized :='YES';
3133 g_dbi_dim_data(l_i).user_code_col := NULL;
3134 g_dbi_dim_data(l_i).code_col := NULL;
3135 g_dbi_dim_data(l_i).parent1_col := NULL;
3136 g_dbi_dim_data(l_i).parent2_col := NULL;
3137 g_dbi_dim_data(l_i).parent3_col := NULL;
3138 g_dbi_dim_data(l_i).parent4_col := NULL;
3139 g_dbi_dim_data(l_i).parent5_col := NULL;
3140
3141 l_i := l_i + 1;
3142 g_dbi_dim_data(l_i).short_name := 'OPM ORGANIZATION';
3143 g_dbi_dim_data(l_i).table_name := 'BSC_D_OPM_ORGANIZATION_T';
3144 g_dbi_dim_data(l_i).from_clause := NULL;
3145 g_dbi_dim_data(l_i).where_clause := NULL;
3146 g_dbi_dim_data(l_i).recursive_dim := 'NO';
3147 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
3148 g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.orgn_code) USER_CODE, to_char(s.orgn_code) CODE'||
3149 ' FROM sy_orgn_mst s) SY_ORGN_MST_S';
3150 g_dbi_dim_data(l_i).source_object_alias := 'SY_ORGN_MST_S';
3151 g_dbi_dim_data(l_i).materialized :='YES';
3152 g_dbi_dim_data(l_i).user_code_col := NULL;
3153 g_dbi_dim_data(l_i).code_col := NULL;
3154 g_dbi_dim_data(l_i).parent1_col := NULL;
3155 g_dbi_dim_data(l_i).parent2_col := NULL;
3156 g_dbi_dim_data(l_i).parent3_col := NULL;
3157 g_dbi_dim_data(l_i).parent4_col := NULL;
3158 g_dbi_dim_data(l_i).parent5_col := NULL;
3159
3160 l_i := l_i + 1;
3161 g_dbi_dim_data(l_i).short_name := 'OPM WAREHOUSE';
3162 g_dbi_dim_data(l_i).table_name := 'BSC_D_OPM_WAREHOUSE_T';
3163 g_dbi_dim_data(l_i).from_clause := NULL;
3164 g_dbi_dim_data(l_i).where_clause := NULL;
3165 g_dbi_dim_data(l_i).recursive_dim := 'NO';
3166 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
3167 g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ s.whse_code USER_CODE, s.whse_code CODE'||
3168 ' FROM ic_whse_mst s) IC_WHSE_MST_S';
3169 g_dbi_dim_data(l_i).source_object_alias := 'IC_WHSE_MST_S';
3170 g_dbi_dim_data(l_i).materialized :='YES';
3171 g_dbi_dim_data(l_i).user_code_col := NULL;
3172 g_dbi_dim_data(l_i).code_col := NULL;
3173 g_dbi_dim_data(l_i).parent1_col := NULL;
3174 g_dbi_dim_data(l_i).parent2_col := NULL;
3175 g_dbi_dim_data(l_i).parent3_col := NULL;
3176 g_dbi_dim_data(l_i).parent4_col := NULL;
3177 g_dbi_dim_data(l_i).parent5_col := NULL;
3178
3179 l_i := l_i + 1;
3180 g_dbi_dim_data(l_i).short_name := 'JTF_ORG_INTERACTION_CENTER_GRP';
3181 g_dbi_dim_data(l_i).table_name := 'BSC_D_JTF_ORG_INTERACTION_CE_T';
3182 g_dbi_dim_data(l_i).from_clause := NULL;
3183 g_dbi_dim_data(l_i).where_clause := NULL;
3184 g_dbi_dim_data(l_i).recursive_dim := 'NO';
3185 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
3186 g_dbi_dim_data(l_i).source_object := ' (SELECT DISTINCT USER_CODE, CODE FROM '||
3187 ' (SELECT /*+ parallel (s) */ TO_CHAR(s.id) USER_CODE, to_char(s.id) CODE'||
3188 ' FROM jtf_rs_dbi_denorm_res_groups s WHERE s.id IS NOT NULL'||
3189 ' UNION ALL SELECT TO_CHAR(-1111) USER_CODE, TO_CHAR(-1111) CODE FROM dual'||
3190 ' UNION ALL SELECT /*+ parallel (s) */ TO_CHAR(s.id_for_grp_mem) USER_CODE,'||
3191 ' to_char(s.id_for_grp_mem) CODE'||
3192 ' FROM jtf_rs_dbi_denorm_res_groups s WHERE s.id_for_grp_mem IS NOT NULL'||
3193 ' UNION ALL SELECT /*+ parallel (s) */ TO_CHAR(s.group_id) USER_CODE,'||
3194 ' to_char(s.group_id) CODE FROM jtf_rs_groups_denorm s WHERE s.group_id IS NOT NULL)'||
3195 ' ) JTF_ORG_INTERACTION_CENTER_S';
3196 g_dbi_dim_data(l_i).source_object_alias := 'JTF_ORG_INTERACTION_CENTER_S';
3197 g_dbi_dim_data(l_i).materialized :='YES';
3198 g_dbi_dim_data(l_i).user_code_col := NULL;
3199 g_dbi_dim_data(l_i).code_col := NULL;
3200 g_dbi_dim_data(l_i).parent1_col := NULL;
3201 g_dbi_dim_data(l_i).parent2_col := NULL;
3202 g_dbi_dim_data(l_i).parent3_col := NULL;
3203 g_dbi_dim_data(l_i).parent4_col := NULL;
3204 g_dbi_dim_data(l_i).parent5_col := NULL;
3205
3206 l_i := l_i + 1;
3207 g_dbi_dim_data(l_i).short_name := 'ENI_ITEM';
3208 g_dbi_dim_data(l_i).table_name := 'ENI_D_ENI_ITEM_T';
3209 g_dbi_dim_data(l_i).from_clause := NULL;
3210 g_dbi_dim_data(l_i).where_clause := NULL;
3211 g_dbi_dim_data(l_i).recursive_dim := 'NO';
3212 g_dbi_dim_data(l_i).date_tracked_dim := 'NO';
3213 g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ s.id USER_CODE, s.id CODE'||
3214 ' FROM eni_oltp_item_star s'||
3215 ' WHERE s.master_id is null) ENI_ITEM_S';
3216 g_dbi_dim_data(l_i).source_object_alias := 'ENI_ITEM_S';
3217 g_dbi_dim_data(l_i).materialized :='YES';
3218 g_dbi_dim_data(l_i).user_code_col := NULL;
3219 g_dbi_dim_data(l_i).code_col := NULL;
3220 g_dbi_dim_data(l_i).parent1_col := NULL;
3221 g_dbi_dim_data(l_i).parent2_col := NULL;
3222 g_dbi_dim_data(l_i).parent3_col := NULL;
3223 g_dbi_dim_data(l_i).parent4_col := NULL;
3224 g_dbi_dim_data(l_i).parent5_col := NULL;
3225
3226 g_dbi_dim_data_set := TRUE;
3227
3228 END Init_Dbi_Dim_Data;
3229
3230
3231 /*===========================================================================+
3232 | FUNCTION Insert_Children_Denorm_Table
3233 +============================================================================*/
3234 FUNCTION Insert_Children_Denorm_Table(
3235 x_parent_id IN number,
3236 x_ids IN BSC_UPDATE_UTIL.t_array_of_number,
3237 x_num_ids IN NUMBER,
3238 x_level IN NUMBER,
3239 x_denorm_table IN VARCHAR2,
3240 x_child_col IN VARCHAR2,
3241 x_parent_col IN VARCHAR2,
3242 x_parent_level_col IN VARCHAR2,
3243 x_denorm_src_object IN VARCHAR2,
3244 x_child_col_src IN VARCHAR2,
3245 x_parent_col_src IN VARCHAR2,
3246 x_src_condition IN VARCHAR2
3247 ) RETURN BOOLEAN IS
3248
3249 l_sql VARCHAR2(32000);
3250 l_where_cond VARCHAR2(32000);
3251 l_i NUMBER;
3252 TYPE t_cursor IS REF CURSOR;
3253 l_cursor t_cursor;
3254
3255 l_num_child_ids NUMBER;
3256 l_child_ids BSC_UPDATE_UTIL.t_array_of_number;
3257 l_child_id NUMBER;
3258
3259 BEGIN
3260
3261 IF x_num_ids = 0 THEN
3262 RETURN TRUE;
3263 END IF;
3264
3265 l_where_cond := BSC_APPS.Get_New_Big_In_Cond_Number(-999999, x_parent_col_src);
3266 FOR l_i IN 1..x_num_ids LOOP
3267 BSC_APPS.Add_Value_Big_In_Cond(-999999, x_ids(l_i));
3268 END LOOP;
3269
3270 l_num_child_ids := 0;
3271 l_sql := 'SELECT DISTINCT '||x_child_col_src||
3272 ' FROM '||x_denorm_src_object||
3273 ' WHERE ';
3274 IF x_src_condition IS NOT NULL THEN
3275 l_sql := l_sql||x_src_condition||' AND ';
3276 END IF;
3277 l_sql := l_sql||l_where_cond;
3278 OPEN l_cursor FOR l_sql;
3279 LOOP
3280 FETCH l_cursor INTO l_child_id;
3281 EXIT WHEN l_cursor%NOTFOUND;
3282 l_num_child_ids := l_num_child_ids + 1;
3283 l_child_ids(l_num_child_ids) := l_child_id;
3284
3285 l_sql := 'INSERT INTO '||x_denorm_table||' ('||
3286 x_parent_col||', '||x_child_col||', '||x_parent_level_col||
3287 ') VALUES (:1, :2, :3)';
3288 EXECUTE IMMEDIATE l_sql USING x_parent_id, l_child_id, x_level;
3289 END LOOP;
3290 -- Fix bug#3899842: Close cursor
3291 CLOSE l_cursor;
3292
3293 IF NOT Insert_Children_Denorm_Table(x_parent_id => x_parent_id,
3294 x_ids => l_child_ids,
3295 x_num_ids => l_num_child_ids,
3296 x_level => x_level,
3297 x_denorm_table => x_denorm_table,
3298 x_child_col => x_child_col,
3299 x_parent_col => x_parent_col,
3300 x_parent_level_col => x_parent_level_col,
3301 x_denorm_src_object => x_denorm_src_object,
3302 x_child_col_src => x_child_col_src,
3303 x_parent_col_src => x_parent_col_src,
3304 x_src_condition => x_src_condition) THEN
3305 RETURN FALSE;
3306 END IF;
3307
3308 RETURN TRUE;
3309 EXCEPTION
3310 WHEN OTHERS THEN
3311 BSC_MESSAGE.Add(x_message => l_sql,
3312 x_source => 'BSC_UPDATE_DIM.Insert_Children_Denorm_Table');
3313 BSC_MESSAGE.Add(x_message => SQLERRM,
3314 x_source => 'BSC_UPDATE_DIM.Insert_Children_Denorm_Table');
3315 RETURN FALSE;
3316
3317 END Insert_Children_Denorm_Table;
3318
3319
3320 --RECURSIVE_DIMS: New function
3321 /*===========================================================================+
3322 | FUNCTION Is_Recursive_Dim
3323 +============================================================================*/
3324 FUNCTION Is_Recursive_Dim(
3325 x_dim_table IN VARCHAR2
3326 ) RETURN BOOLEAN IS
3327 h_count NUMBER;
3328 BEGIN
3329 h_count := 0;
3330
3331 select count(d.dim_level_id)
3332 into h_count
3333 from bsc_sys_dim_levels_b d, bsc_sys_dim_level_rels r
3334 where d.dim_level_id = r.dim_level_id and
3335 d.level_table_name = x_dim_table and
3336 r.parent_dim_level_id = r.dim_level_id;
3337
3338 IF h_count > 0 THEN
3339 RETURN TRUE;
3340 ELSE
3341 RETURN FALSE;
3342 END IF;
3343
3344 END Is_Recursive_Dim;
3345
3346
3347 --AW_INTEGRATION: New procedure
3348 /*===========================================================================+
3349 | FUNCTION Insert_AW_Delete_Value
3350 +============================================================================*/
3351 PROCEDURE Insert_AW_Delete_Value(
3352 x_dim_table IN VARCHAR2,
3353 x_delete_value IN VARCHAR2
3354 ) IS
3355
3356 h_sql VARCHAR2(32000);
3357 l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;
3358 l_num_bind_vars NUMBER;
3359
3360 BEGIN
3361
3362 h_sql := 'INSERT INTO BSC_AW_DIM_DELETE (DIM_LEVEL, DELETE_VALUE)'||
3363 ' VALUES (:1,:2)';
3364 l_bind_vars_values.delete;
3365 l_bind_vars_values(1) := x_dim_table;
3366 l_bind_vars_values(2) := x_delete_value;
3367 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
3368
3369 END Insert_AW_Delete_Value;
3370
3371
3372 --LOCKING: new procedure
3373 /*===========================================================================+
3374 | PROCEDURE Load_Dim_Into_AW_AT
3375 +============================================================================*/
3376 PROCEDURE Load_Dim_Into_AW_AT (
3377 x_dim_table IN VARCHAR2
3378 ) IS
3379 PRAGMA AUTONOMOUS_TRANSACTION;
3380 h_dim_level_list dbms_sql.varchar2_table;
3381 BEGIN
3382 h_dim_level_list.delete;
3383 h_dim_level_list(1) := x_dim_table;
3384 bsc_aw_load.load_dim(
3385 p_dim_level_list => h_dim_level_list,
3386 p_options => 'DEBUG LOG'
3387 );
3388 commit;
3389 END Load_Dim_Into_AW_AT;
3390
3391
3392 /*===========================================================================+
3393 | FUNCTION Load_Dim_Table
3394 +============================================================================*/
3395 FUNCTION Load_Dim_Table(
3396 x_dim_table IN VARCHAR2,
3397 x_input_table IN VARCHAR2
3398 ) RETURN BOOLEAN IS
3399
3400 e_unexpected_error EXCEPTION;
3401
3402 h_loading_mode NUMBER;
3403 h_dim_table_type NUMBER;
3404
3405 TYPE t_cursor IS REF CURSOR;
3406 h_cursor t_cursor;
3407
3408 h_sql VARCHAR2(32700);
3409 h_i NUMBER;
3410 h_do_it_flag BOOLEAN;
3411
3412 h_code NUMBER;
3413 h_code1 NUMBER;
3414 h_code2 NUMBER;
3415 h_user_code VARCHAR2(1000);
3416
3417 h_deleted_codes BSC_UPDATE_UTIL.t_array_of_number;
3418
3419 -- BSC-BIS-DIMENSIONS
3420 -- MN dimension can be created in BSC to store MN relations between BIS dimensions.
3421 -- To spport NUMBER or VARCHAR2 I will change the type of this arrays to varchar2.
3422 h_deleted_codes1 BSC_UPDATE_UTIL.t_array_of_varchar2;
3423 h_deleted_codes2 BSC_UPDATE_UTIL.t_array_of_varchar2;
3424 h_num_deleted_codes NUMBER;
3425
3426 h_table_was_modified BOOLEAN;
3427
3428 h_parent_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3429 h_parent_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
3430 h_num_parents NUMBER;
3431
3432 h_aux_fields BSC_UPDATE_UTIL.t_array_of_varchar2;
3433 h_num_aux_fields NUMBER;
3434
3435 h_installed_languages BSC_UPDATE_UTIL.t_array_of_varchar2;
3436 h_num_installed_languages NUMBER;
3437
3438 h_p_insert VARCHAR2(32700);
3439 h_p_select VARCHAR2(32700);
3440 h_p_from VARCHAR2(32700);
3441 h_p_where VARCHAR2(32700);
3442
3443 h_aux_insert VARCHAR2(32700);
3444 h_aux_select VARCHAR2(32700);
3445
3446 l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;
3447 l_num_bind_vars NUMBER;
3448
3449 h_safe_user_code VARCHAR2(1000);
3450
3451 --AW_INTEGRATION: new variables
3452 h_dim_level_list dbms_sql.varchar2_table;
3453
3454 h_userenv_lang VARCHAR2(10);
3455
3456 BEGIN
3457 h_num_deleted_codes := 0;
3458 h_table_was_modified := FALSE;
3459 l_num_bind_vars := 0;
3460
3461 -- Get loading mode
3462 SELECT generation_type
3463 INTO h_loading_mode
3464 FROM bsc_db_tables
3465 WHERE table_name = x_input_table;
3466
3467 -- Get dimension table type (1n or MN)
3468 h_dim_table_type := Get_Dim_Table_Type(x_dim_table);
3469 IF h_dim_table_type = DIM_TABLE_TYPE_UNKNOWN THEN
3470 RAISE e_unexpected_error;
3471 END IF;
3472
3473 -- Get info of parents
3474 h_num_parents := Get_Info_Parents_Dimensions(x_dim_table, h_parent_tables, h_parent_keys);
3475
3476 -- Delete records existing in the dimension table but not in the input table.
3477 IF h_loading_mode = 1 THEN
3478 -- Overwrite
3479 -- Data in the input table represent the complete dimension table
3480 -- So, we need to delete records existing in the dimension table
3481 -- but not in the input table.
3482
3483 IF h_dim_table_type = DIM_TABLE_TYPE_1N THEN
3484 -- Normal dimension
3485
3486 -- BSC-BIS-DIMENSIONS: No change here to support NUMBER/VARCHAR2. We only can load
3487 -- BSC dimensions. Also the array h_deleted_codes can continue to be NUMBER
3488
3489 h_sql := 'SELECT DISTINCT code FROM '||x_dim_table||
3490 ' WHERE code > :1 AND user_code NOT IN ('||
3491 ' SELECT user_code FROM '||x_input_table||')';
3492
3493 OPEN h_cursor FOR h_sql USING 0;
3494 FETCH h_cursor INTO h_code;
3495 WHILE h_cursor%FOUND LOOP
3496 h_num_deleted_codes := h_num_deleted_codes + 1;
3497 h_deleted_codes(h_num_deleted_codes) := h_code;
3498
3499 FETCH h_cursor INTO h_code;
3500 END LOOP;
3501 CLOSE h_cursor;
3502
3503 IF h_num_deleted_codes > 0 THEN
3504 IF NOT Delete_Codes_Cascade(x_dim_table, h_deleted_codes, h_num_deleted_codes) THEN
3505 RAISE e_unexpected_error;
3506 END IF;
3507
3508 h_table_was_modified := TRUE;
3509 END IF;
3510 ELSE
3511 -- MN dimension
3512 h_sql := 'SELECT DISTINCT '||h_parent_keys(1)||', '||h_parent_keys(2)||
3513 ' FROM '||x_dim_table||
3514 ' WHERE ('||h_parent_keys(1)||', '||h_parent_keys(2)||') NOT IN ('||
3515 ' SELECT d1.code, d2.code'||
3516 ' FROM '||x_input_table||' i, '||h_parent_tables(1)||' d1, '||h_parent_tables(2)||' d2'||
3517 ' WHERE i.'||h_parent_keys(1)||'_usr = d1.user_code'||
3518 ' AND i.'||h_parent_keys(2)||'_usr = d2.user_code)';
3519
3520 OPEN h_cursor FOR h_sql;
3521 FETCH h_cursor INTO h_code1, h_code2;
3522 WHILE h_cursor%FOUND LOOP
3523 h_num_deleted_codes := h_num_deleted_codes + 1;
3524 h_deleted_codes1(h_num_deleted_codes) := h_code1;
3525 h_deleted_codes2(h_num_deleted_codes) := h_code2;
3526
3527 FETCH h_cursor INTO h_code1, h_code2;
3528 END LOOP;
3529 CLOSE h_cursor;
3530
3531 IF h_num_deleted_codes > 0 THEN
3532 IF NOT Delete_Codes_CascadeMN(x_dim_table,
3533 h_parent_keys(1),
3534 h_parent_keys(2),
3535 h_deleted_codes1,
3536 h_deleted_codes2,
3537 h_num_deleted_codes) THEN
3538 RAISE e_unexpected_error;
3539 END IF;
3540
3541 h_table_was_modified := TRUE;
3542 END IF;
3543 END IF;
3544 END IF;
3545
3546
3547 -- Update existing records and insert new records
3548
3549 IF h_dim_table_type = DIM_TABLE_TYPE_1N THEN
3550 -- 1N dimesion
3551
3552 -- Get aux fields
3553 h_num_aux_fields := Get_Aux_Fields_Dim_Table(x_dim_table, h_aux_fields);
3554
3555 -- Update existing records
3556 -- Records in the dimension table that are in the input table
3557
3558 -- Check if there are existing records
3559 h_sql := 'SELECT DISTINCT code FROM '||x_dim_table||
3560 ' WHERE user_code IN ('||
3561 ' SELECT user_code FROM '||x_input_table||')';
3562
3563 OPEN h_cursor FOR h_sql;
3564 FETCH h_cursor INTO h_code;
3565 IF h_cursor%FOUND THEN
3566 h_do_it_flag := TRUE;
3567 ELSE
3568 h_do_it_flag := FALSE;
3569 END IF;
3570 CLOSE h_cursor;
3571
3572 IF h_do_it_flag THEN
3573 -- There are existing records to update
3574
3575 -- Udpate the NAME column (MLS)
3576 h_sql := 'UPDATE '||x_dim_table||' d'||
3577 ' SET name = ('||
3578 ' SELECT name'||
3579 ' FROM '||x_input_table||' i'||
3580 ' WHERE i.user_code = d.user_code),'||
3581 ' source_lang = :1'||
3582 ' WHERE user_code IN (SELECT user_code FROM '||x_input_table||')'||
3583 ' AND (language = :2 OR source_lang = :3)';
3584 l_bind_vars_values.delete;
3585 l_bind_vars_values(1) := userenv('LANG');
3586 l_bind_vars_values(2) := userenv('LANG');
3587 l_bind_vars_values(3) := userenv('LANG');
3588 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,3);
3589
3590 -- Udpate parent key columns
3591 FOR h_i IN 1 .. h_num_parents LOOP
3592 -- Check if there is at least one change of parent to mark this dimension as modified (to recalc kpi totals)
3593 h_sql := 'SELECT user_code FROM '||x_input_table||' i'||
3594 ' WHERE '||h_parent_keys(h_i)||'_usr <> ('||
3595 ' SELECT DISTINCT '||h_parent_keys(h_i)||'_usr'||
3596 ' FROM '||x_dim_table||' d'||
3597 ' WHERE d.user_code = i.user_code)';
3598
3599 OPEN h_cursor FOR h_sql;
3600 FETCH h_cursor INTO h_user_code;
3601 IF h_cursor%FOUND THEN
3602 h_table_was_modified := TRUE;
3603 END IF;
3604 CLOSE h_cursor;
3605
3606 h_sql := 'UPDATE '||x_dim_table||' d'||
3607 ' SET ('||h_parent_keys(h_i)||', '||h_parent_keys(h_i)||'_usr) = ('||
3608 ' SELECT DISTINCT p.code, i.'||h_parent_keys(h_i)||'_usr'||
3609 ' FROM '||x_input_table||' i, '||h_parent_tables(h_i)||' p'||
3610 ' WHERE d.user_code = i.user_code'||
3611 ' AND i.'||h_parent_keys(h_i)||'_usr = p.user_code)'||
3612 ' WHERE d.user_code IN (SELECT user_code FROM '||x_input_table||')';
3613 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
3614 END LOOP;
3615
3616 -- Udpate auxiliary fileds
3617 FOR h_i IN 1 .. h_num_aux_fields LOOP
3618 h_sql := 'UPDATE '||x_dim_table||' d'||
3619 ' SET '||h_aux_fields(h_i)||' = ('||
3620 ' SELECT i.'||h_aux_fields(h_i)||
3621 ' FROM '||x_input_table||' i'||
3622 ' WHERE d.user_code = i.user_code)'||
3623 ' WHERE d.user_code IN (SELECT user_code FROM '||x_input_table||')';
3624 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
3625 END LOOP;
3626
3627 -- Delete existing records from the input table
3628 h_sql := 'DELETE FROM '||x_input_table||
3629 ' WHERE user_code IN (SELECT user_code FROM '||x_dim_table||')';
3630 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
3631
3632 END IF;
3633
3634 -- Insert new records from the input table to the dimension table
3635 -- Get installed languages
3636 h_num_installed_languages := BSC_UPDATE_UTIL.Get_Installed_Languages(h_installed_languages);
3637 IF h_num_installed_languages = -1 THEN
3638 RAISE e_unexpected_error;
3639 END IF;
3640
3641 -- Get portion of the sql statement corresponding to the parents
3642 h_p_insert := NULL;
3643 h_p_select := NULL;
3644 h_p_from := NULL;
3645 h_p_where := NULL;
3646
3647 FOR h_i IN 1 .. h_num_parents LOOP
3648 h_p_insert := h_p_insert||', '||h_parent_keys(h_i)||', '||h_parent_keys(h_i)||'_USR';
3649 h_p_select := h_p_select||', p'||h_i||'.code, i.'||h_parent_keys(h_i)||'_USR';
3650 h_p_from := h_p_from||', '||h_parent_tables(h_i)||' p'||h_i;
3651 h_p_where := h_p_where||' AND i.'||h_parent_keys(h_i)||'_USR = p'||h_i||'.user_code'||
3652 ' AND p'||h_i||'.language = USERENV(''LANG'')';
3653 END LOOP;
3654
3655 -- Get portion of the sql statement corresponding to aux fields
3656 h_aux_insert := NULL;
3657 h_aux_select := NULL;
3658
3659 FOR h_i IN 1 .. h_num_aux_fields LOOP
3660 h_aux_insert := h_aux_insert||', '||h_aux_fields(h_i);
3661 h_aux_select := h_aux_select||', i.'||h_aux_fields(h_i);
3662 END LOOP;
3663
3664 -- Insert record by record (we need to get new code for each one)
3665 h_sql := 'SELECT DISTINCT user_code FROM '||x_input_table;
3666
3667 OPEN h_cursor FOR h_sql;
3668 FETCH h_cursor INTO h_user_code;
3669 WHILE h_cursor%FOUND LOOP
3670 -- Get a new code for this record
3671 h_code := Get_New_Code(x_dim_table);
3672 IF h_code = -1 THEN
3673 RAISE e_unexpected_error;
3674 END IF;
3675
3676 -- Insert one record for each installed language
3677 FOR h_i IN 1 .. h_num_installed_languages LOOP
3678 h_sql := 'INSERT INTO '||x_dim_table||' ('||
3679 ' code, user_code, name'||h_p_insert||h_aux_insert||', language, source_lang)'||
3680 ' SELECT :1, i.user_code, i.name'||h_p_select||h_aux_select||
3681 ', :2, :3'||
3682 ' FROM '||x_input_table||' i'||h_p_from||
3683 ' WHERE i.user_code = :4 '||h_p_where;
3684 --h_p_where looks fine. we may not need binding
3685 --Venu : because there is a mix of number and varchar variables, executing it here
3686 --Bug 3092316: no need the replace!!
3687 --execute immediate h_sql using h_code,h_installed_languages(h_i),REPLACE(h_user_code,'''', '''''');
3688 h_userenv_lang := USERENV('LANG');
3689 execute immediate h_sql using h_code,h_installed_languages(h_i),h_userenv_lang,h_user_code;
3690 END LOOP;
3691
3692 FETCH h_cursor INTO h_user_code;
3693 END LOOP;
3694 CLOSE h_cursor;
3695
3696 ELSE
3697 -- MN dimension
3698 -- Delete existing records from the input table
3699 h_sql := 'DELETE FROM '||x_input_table||
3700 ' WHERE ('||h_parent_keys(1)||'_usr, '||h_parent_keys(2)||'_usr) IN ('||
3701 ' SELECT d1.user_code, d2.user_code'||
3702 ' FROM '||x_dim_table||' d, '||h_parent_tables(1)||' d1, '||h_parent_tables(2)||' d2'||
3703 ' WHERE d.'||h_parent_keys(1)||' = d1.code'||
3704 ' AND d.'||h_parent_keys(2)||' = d2.code)';
3705 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
3706
3707 -- Insert new records from the input table to the dimension table
3708 h_sql := 'INSERT INTO '||x_dim_table||' ('||h_parent_keys(1)||', '||h_parent_keys(2)||')'||
3709 ' SELECT p1.code, p2.code'||
3710 ' FROM '||x_input_table||' i, '||h_parent_tables(1)||' p1, '||h_parent_tables(2)||' p2'||
3711 ' WHERE i.'||h_parent_keys(1)||'_USR = p1.user_code AND p1.language = :1 AND'||
3712 ' i.'||h_parent_keys(2)||'_USR = p2.user_code AND p2.language = :2';
3713 l_bind_vars_values.delete;
3714 l_bind_vars_values(1) := userenv('LANG');
3715 l_bind_vars_values(2) := userenv('LANG');
3716 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
3717
3718 END IF;
3719
3720 -- Delete data from input table
3721 BSC_UPDATE_UTIL.Truncate_Table(x_input_table);
3722
3723 --AW_INTEGRATION: We need to load the dimension into AW even that the dimension
3724 -- input table is empty
3725 h_dim_table_type := Get_Dim_Table_Type(x_dim_table);
3726 IF h_dim_table_type = BSC_UPDATE_DIM.DIM_TABLE_TYPE_1N THEN
3727 IF Dimension_Used_In_AW_Kpi(x_dim_table) THEN
3728 h_dim_level_list.delete;
3729 h_dim_level_list(1) := x_dim_table;
3730 bsc_aw_load.load_dim(
3731 p_dim_level_list => h_dim_level_list,
3732 p_options => 'DEBUG LOG'
3733 );
3734 END IF;
3735 END IF;
3736
3737 -- Synchronize sec assigments
3738 IF NOT Sync_Sec_Assigments THEN
3739 RAISE e_unexpected_error;
3740 END IF;
3741
3742 -- Mark indicators using this dimension to be recalculated.
3743 -- Only if the table was modified.
3744 IF h_table_was_modified THEN
3745 UPDATE BSC_KPIS_B K
3746 SET PROTOTYPE_FLAG = 6,
3747 LAST_UPDATED_BY = BSC_APPS.fnd_global_user_id,
3748 LAST_UPDATE_DATE = SYSDATE
3749 WHERE INDICATOR IN (SELECT D.INDICATOR
3750 FROM BSC_KPI_DIM_LEVELS_B D
3751 WHERE K.INDICATOR = D.INDICATOR AND
3752 (D.LEVEL_TABLE_NAME = x_dim_table OR
3753 D.TABLE_RELATION = x_dim_table)) AND
3754 PROTOTYPE_FLAG in (0, 6, 7);
3755
3756 -- Color By KPI: Mark KPIs for color re-calculation
3757 UPDATE bsc_kpi_analysis_measures_b k
3758 SET prototype_flag = BSC_DESIGNER_PVT.C_COLOR_CHANGE -- 7
3759 WHERE indicator IN (SELECT d.indicator
3760 FROM bsc_kpi_dim_levels_b d
3761 WHERE k.indicator = d.indicator
3762 AND (d.level_table_name = x_dim_table
3763 OR d.table_relation = x_dim_table));
3764
3765 END IF;
3766
3767 COMMIT;
3768
3769 -- Analyze the dimension table
3770 BSC_BIA_WRAPPER.Analyze_Table(x_dim_table);
3771
3772 RETURN TRUE;
3773
3774 EXCEPTION
3775 WHEN e_unexpected_error THEN
3776 ROLLBACK;
3777 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DIMTABLE_UPDATE_FAILED'),
3778 x_source => 'BSC_UPDATE_BASE.Load_Dim_Table');
3779 RETURN FALSE;
3780
3781 WHEN OTHERS THEN
3782 ROLLBACK;
3783 BSC_MESSAGE.Add(x_message => SQLERRM,
3784 x_source => 'BSC_UPDATE_BASE.Load_Dim_Table');
3785 RETURN FALSE;
3786
3787 END Load_Dim_Table;
3788
3789
3790 --LOCKING: new function
3791 /*===========================================================================+
3792 | FUNCTION Load_Dim_Table_AT
3793 +============================================================================*/
3794 FUNCTION Load_Dim_Table_AT(
3795 x_dim_table IN VARCHAR2,
3796 x_input_table IN VARCHAR2
3797 ) RETURN BOOLEAN IS
3798 PRAGMA AUTONOMOUS_TRANSACTION;
3799 h_b BOOLEAN;
3800 BEGIN
3801 h_b := Load_Dim_Table(x_dim_table, x_input_table);
3802 commit; -- all autonomous transaction needs to commit
3803 RETURN h_b;
3804 END Load_Dim_Table_AT;
3805
3806
3807 --LOCKING: new procedure
3808 /*===========================================================================+
3809 | PROCEDURE Load_Type_Into_AW_AT
3810 +============================================================================*/
3811 PROCEDURE Load_Type_Into_AW_AT IS
3812 PRAGMA AUTONOMOUS_TRANSACTION;
3813 h_dim_level_list dbms_sql.varchar2_table;
3814 BEGIN
3815 h_dim_level_list.delete;
3816 h_dim_level_list(1) := 'TYPE';
3817 bsc_aw_load.load_dim(
3818 p_dim_level_list => h_dim_level_list,
3819 p_options => 'DEBUG LOG'
3820 );
3821 commit;
3822 END Load_Type_Into_AW_AT;
3823
3824
3825 /*===========================================================================+
3826 | FUNCTION Need_Refresh_Dbi_Table
3827 +============================================================================*/
3828 FUNCTION Need_Refresh_Dbi_Table(
3829 x_table_name IN VARCHAR2,
3830 x_source_to_check IN VARCHAR2
3831 ) RETURN BOOLEAN IS
3832
3833 l_obj_type VARCHAR2(80);
3834
3835 TYPE t_cursor IS REF CURSOR;
3836 l_cursor t_cursor;
3837 l_sql VARCHAR2(32000);
3838 l_objs_to_check BSC_UPDATE_UTIL.t_array_of_varchar2;
3839 l_num_objs_to_check NUMBER;
3840
3841 l_max_lud_source DATE;
3842 l_max_lud DATE;
3843 l_lud_table DATE;
3844 l_i NUMBER;
3845
3846 BEGIN
3847 l_obj_type := 'DIM';
3848
3849 IF x_source_to_check IS NULL THEN
3850 -- There is no source objects to compare last update dat with.
3851 RETURN TRUE;
3852 END IF;
3853
3854 -- get last update date of the table from bsc_object_refresh_log
3855 l_sql := 'SELECT refresh_end_time'||
3856 ' FROM bsc_object_refresh_log'||
3857 ' WHERE object_name = :1 AND object_type = :2';
3858 OPEN l_cursor FOR l_sql USING x_table_name, l_obj_type;
3859 FETCH l_cursor INTO l_lud_table;
3860 IF l_cursor%NOTFOUND THEN
3861 l_lud_table := NULL;
3862 END IF;
3863 CLOSE l_cursor;
3864
3865 IF l_lud_table IS NULL THEN
3866 -- No info about last update date of the table. So this table need to be refreshed
3867 RETURN TRUE;
3868 END IF;
3869
3870
3871 -- get the max last update date between the source objects
3872 l_num_objs_to_check := BSC_UPDATE_UTIL.Decompose_Varchar2_List(x_source_to_check, l_objs_to_check, ',');
3873 l_max_lud_source := NULL;
3874 FOR l_i IN 1..l_num_objs_to_check LOOP
3875 l_sql := 'SELECT MAX(last_update_date)'||
3876 ' FROM '||l_objs_to_check(l_i);
3877 OPEN l_cursor FOR l_sql;
3878 FETCH l_cursor INTO l_max_lud;
3879 IF l_cursor%NOTFOUND THEN
3880 l_max_lud := NULL;
3881 END IF;
3882 CLOSE l_cursor;
3883 IF l_max_lud IS NOT NULL THEN
3884 IF l_max_lud_source IS NULL THEN
3885 l_max_lud_source := l_max_lud;
3886 ELSE
3887 IF l_max_lud > l_max_lud_source THEN
3888 l_max_lud_source := l_max_lud;
3889 END IF;
3890 END IF;
3891 END IF;
3892 END LOOP;
3893
3894 IF l_max_lud_source IS NULL THEN
3895 -- NO innfo about last update date of the source. So this table need to be refreshed
3896 RETURN TRUE;
3897 END IF;
3898
3899 IF l_max_lud_source > l_lud_table THEN
3900 RETURN TRUE;
3901 ELSE
3902 RETURN FALSE;
3903 END IF;
3904
3905 END Need_Refresh_Dbi_Table;
3906
3907
3908 /*===========================================================================+
3909 | FUNCTION Refresh_Dbi_Dimension_Table
3910 +============================================================================*/
3911 FUNCTION Refresh_Dbi_Dimension_Table(
3912 x_dim_short_name IN VARCHAR2
3913 ) RETURN BOOLEAN IS
3914
3915 e_unexpected_error EXCEPTION;
3916
3917 l_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
3918 l_sql VARCHAR2(32000);
3919 l_i NUMBER;
3920 l_lst_select VARCHAR2(8000);
3921 l_lst_select_src VARCHAR2(8000);
3922 l_lst_select_tmp VARCHAR2(8000);
3923 l_lst_select_tmp_t VARCHAR2(8000);
3924 l_lst_set VARCHAR2(8000);
3925 l_lst_set_tmp VARCHAR2(8000);
3926 l_cond_parents VARCHAR2(8000);
3927 l_cond_eff_date VARCHAR2(8000);
3928
3929 l_parent_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3930 l_src_parent_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3931 l_num_parent_columns NUMBER;
3932
3933 l_source_object VARCHAR2(20000);
3934 l_code varchar2(100);
3935 l_user_code varchar2(100);
3936 l_source_object_alias varchar2(100);
3937
3938 l_dim_obj_type VARCHAR2(80);
3939
3940 -- AW_INTEGRATION: new variables
3941 l_level_table_name VARCHAR2(30);
3942 l_dim_level_list dbms_sql.varchar2_table;
3943 l_dim_for_aw_kpi BOOLEAN;
3944
3945 -- RECURSIVE_DIMS: new variables
3946 l_dim_for_mv_kpi BOOLEAN;
3947 l_denorm_table_name VARCHAR2(30);
3948
3949 BEGIN
3950 l_dim_obj_type := 'DIM';
3951
3952 Get_Dbi_Dim_Data(x_dim_short_name, l_dbi_dim_data);
3953
3954 -- AW_INTEGRATION: We need to know the level_table_name given the short name
3955 SELECT level_table_name
3956 INTO l_level_table_name
3957 FROM bsc_sys_dim_levels_b
3958 WHERE short_name = x_dim_short_name;
3959
3960 -- AW_INTEGRATION: We need to know if the dimension is used by an AW indicator
3961 l_dim_for_aw_kpi := Dimension_Used_In_AW_Kpi(l_level_table_name);
3962 l_dim_for_mv_kpi := Dimension_Used_In_MV_Kpi(l_level_table_name);
3963
3964 IF (l_dbi_dim_data.table_name IS NOT NULL) AND (l_dbi_dim_data.materialized='YES') THEN
3965 --Fix bug#3780702: If the table does not exist we do not do anything.
3966 IF BSC_APPS.Table_Exists(l_dbi_dim_data.table_name) THEN
3967 IF l_dbi_dim_data.source_object IS NULL THEN
3968 l_source_object := Get_Dbi_Dim_View_Name(x_dim_short_name);
3969 ELSE
3970 l_source_object := l_dbi_dim_data.source_object;
3971 END IF;
3972
3973 IF l_source_object IS NOT NULL THEN
3974 -- NOTE: The generic temporary tables needed in this process MUST exist.
3975
3976 -- We are going to compare the last update date of the source object with
3977 -- the refresh_end_time of the dimension table to know if it is necessary to
3978 -- refresh it or not.
3979 IF Need_Refresh_Dbi_Table(l_dbi_dim_data.table_name, l_dbi_dim_data.source_to_check) THEN
3980
3981 -- Udpate REFRESH_START_TIME in BSC_OBJECT_REFRESH_LOG for this table
3982 l_sql := 'UPDATE bsc_object_refresh_log'||
3983 ' SET refresh_start_time = SYSDATE, refresh_end_time = NULL'||
3984 ' WHERE object_name = :1 AND object_type = :2';
3985 EXECUTE IMMEDIATE l_sql USING l_dbi_dim_data.table_name, l_dim_obj_type;
3986 IF SQL%NOTFOUND THEN
3987 l_sql := 'INSERT INTO bsc_object_refresh_log'||
3988 ' (object_name, object_type, refresh_start_time, refresh_end_time)'||
3989 ' VALUES (:1, :2, SYSDATE, NULL)';
3990 EXECUTE IMMEDIATE l_sql USING l_dbi_dim_data.table_name, l_dim_obj_type;
3991 END IF;
3992 COMMIT;
3993
3994 -- Get the parent key columns in an array. It does not include self-relations.
3995 l_num_parent_columns := Get_Dbi_Dim_Parent_Columns(x_dim_short_name, l_parent_columns, l_src_parent_columns);
3996
3997 l_code:='CODE';
3998 IF l_dbi_dim_data.code_col IS NOT NULL THEN
3999 l_code := l_dbi_dim_data.code_col;
4000 END IF;
4001 l_user_code:='USER_CODE';
4002 IF l_dbi_dim_data.user_code_col IS NOT NULL THEN
4003 l_user_code := l_dbi_dim_data.user_code_col;
4004 END IF;
4005 l_lst_select := 'USER_CODE,CODE';
4006 if l_dbi_dim_data.source_object is null then
4007 --for all except hri and po commodities
4008 l_lst_select_src := l_user_code||','||l_code;
4009 else
4010 --for HRI and po commodities
4011 l_lst_select_src := l_user_code||',decode(to_char('||l_user_code||'),''0'',''-99999999'','||
4012 'to_char('||l_user_code||')) '||l_code;
4013 end if;
4014 l_lst_select_tmp := 'USER_CODE,CODE';
4015 l_lst_select_tmp_t := 'T.USER_CODE,T.CODE';
4016 l_lst_set := NULL;
4017 l_lst_set_tmp := NULL;
4018 l_cond_parents := NULL;
4019 l_cond_eff_date := NULL;
4020
4021 IF l_num_parent_columns > 0 THEN
4022 FOR l_i IN 1..l_num_parent_columns LOOP
4023 l_lst_select := l_lst_select||', '||l_parent_columns(l_i);
4024 l_lst_select_src := l_lst_select_src||', '||l_src_parent_columns(l_i);
4025 l_lst_select_tmp := l_lst_select_tmp||', PARENT_CODE'||l_i;
4026 l_lst_select_tmp_t := l_lst_select_tmp_t||', T.PARENT_CODE'||l_i;
4027
4028 IF l_i > 1 THEN
4029 l_lst_set := l_lst_set||', ';
4030 l_lst_set_tmp := l_lst_set_tmp||', ';
4031 l_cond_parents := l_cond_parents||' OR ';
4032 END IF;
4033 l_lst_set := l_lst_set||l_parent_columns(l_i);
4034 l_lst_set_tmp := l_lst_set_tmp||'T.PARENT_CODE'||l_i;
4035 l_cond_parents := l_cond_parents||'T.PARENT_CODE'||l_i||' <> B.'||l_parent_columns(l_i);
4036 END LOOP;
4037
4038 IF l_dbi_dim_data.date_tracked_dim = 'YES' THEN
4039 l_lst_select := l_lst_select||', EFFECTIVE_START_DATE, EFFECTIVE_END_DATE';
4040 l_lst_select_src := l_lst_select_src||', EFFECTIVE_START_DATE, EFFECTIVE_END_DATE';
4041 l_lst_select_tmp := l_lst_select_tmp||', EFFECTIVE_START_DATE, EFFECTIVE_END_DATE';
4042 l_lst_select_tmp_t := l_lst_select_tmp_t||', T.EFFECTIVE_START_DATE, T.EFFECTIVE_END_DATE';
4043
4044 l_cond_eff_date := 'T.EFFECTIVE_START_DATE = B.EFFECTIVE_START_DATE AND'||
4045 ' T.EFFECTIVE_END_DATE = B.EFFECTIVE_END_DATE';
4046 END IF;
4047 END IF;
4048
4049 --delete the temp tables
4050 BSC_UPDATE_UTIL.Execute_Immediate('delete BSC_TMP_DBI_DIM');
4051 BSC_UPDATE_UTIL.Execute_Immediate('delete BSC_TMP_DBI_DIM_ADD');
4052 BSC_UPDATE_UTIL.Execute_Immediate('delete BSC_TMP_DBI_DIM_DEL');
4053 commit;
4054
4055 if l_dbi_dim_data.source_object_alias is not null then
4056 l_source_object_alias:=l_dbi_dim_data.source_object_alias;
4057 else
4058 l_source_object_alias:=l_source_object;
4059 end if;
4060
4061 -- Insert into BSC_TMP_DBI_DIM all items from the view or source object.
4062 l_sql := 'INSERT /*+ parallel(BSC_TMP_DBI_DIM) */'||
4063 ' INTO BSC_TMP_DBI_DIM ('||l_lst_select_tmp||')'||
4064 ' SELECT /*+ parallel('||l_source_object_alias||') */ DISTINCT '||l_lst_select_src||
4065 ' FROM '||l_source_object;
4066 if bsc_im_utils.g_debug then
4067 write_to_log_file_n(l_sql);
4068 end if;
4069 BSC_UPDATE_UTIL.Execute_Immediate(l_sql);
4070 COMMIT;
4071
4072 -- Insert BSC_TMP_DBI_DIM.CODE minus DIM_TABLE.CODE (new records) into BSC_TMP_DBI_DIM_ADD
4073 l_sql := 'INSERT /*+ parallel(BSC_TMP_DBI_DIM_ADD) */'||
4074 ' INTO BSC_TMP_DBI_DIM_ADD (USER_CODE)'||
4075 ' SELECT USER_CODE'||
4076 ' FROM BSC_TMP_DBI_DIM MINUS select USER_CODE from '||l_dbi_dim_data.table_name;
4077 if bsc_im_utils.g_debug then
4078 write_to_log_file_n(l_sql);
4079 end if;
4080 BSC_UPDATE_UTIL.Execute_Immediate(l_sql);
4081 COMMIT;
4082
4083 -- Insert DIM_TABLE.CODE minus BSC_TMP_DBI_DIM.CODE (records to delete) into BSC_TMP_DBI_DIM_DEL
4084 l_sql := 'INSERT /*+ parallel(BSC_TMP_DBI_DIM_DEL) */'||
4085 ' INTO BSC_TMP_DBI_DIM_DEL (USER_CODE)'||
4086 ' SELECT USER_CODE'||
4087 ' FROM '||l_dbi_dim_data.table_name||' MINUS select USER_CODE from BSC_TMP_DBI_DIM';
4088 if bsc_im_utils.g_debug then
4089 write_to_log_file_n(l_sql);
4090 end if;
4091 BSC_UPDATE_UTIL.Execute_Immediate(l_sql);
4092 COMMIT;
4093
4094 -- AW_INTEGRATION: We need to insert the deleted rows into BSC_AW_DIM_DELETE table
4095 -- Here we need to inser CODEs not USER_CODEs
4096 IF l_dim_for_aw_kpi THEN
4097 l_sql := 'INSERT /*+ parallel(BSC_AW_DIM_DELETE) */'||
4098 ' INTO BSC_AW_DIM_DELETE (DIM_LEVEL, DELETE_VALUE)'||
4099 ' SELECT :1, CODE'||
4100 ' FROM '||l_dbi_dim_data.table_name||' MINUS SELECT :2, CODE FROM BSC_TMP_DBI_DIM';
4101 if bsc_im_utils.g_debug then
4102 write_to_log_file_n(l_sql);
4103 end if;
4104 EXECUTE IMMEDIATE l_sql USING l_level_table_name, l_level_table_name;
4105 COMMIT;
4106 END IF;
4107
4108 -- Udpate DIM_TABLE
4109 IF l_num_parent_columns > 0 THEN
4110 l_sql := 'UPDATE '||l_dbi_dim_data.table_name||' B'||
4111 ' SET ('||l_lst_set||') = ('||
4112 ' SELECT '||l_lst_set_tmp||
4113 ' FROM BSC_TMP_DBI_DIM T'||
4114 ' WHERE T.USER_CODE = B.USER_CODE'||
4115 ' )'||
4116 ' WHERE EXISTS ('||
4117 ' SELECT T.USER_CODE'||
4118 ' FROM BSC_TMP_DBI_DIM T'||
4119 ' WHERE T.USER_CODE = B.USER_CODE AND ('||l_cond_parents||')';
4120 IF l_cond_eff_date IS NOT NULL THEN
4121 l_sql := l_sql||' AND '||l_cond_eff_date;
4122 END IF;
4123 l_sql := l_sql||' )';
4124 if bsc_im_utils.g_debug then
4125 write_to_log_file_n(l_sql);
4126 end if;
4127 BSC_UPDATE_UTIL.Execute_Immediate(l_sql);
4128 END IF;
4129
4130 -- Insert new rows into DIM_TABLE
4131 l_sql := 'INSERT /*+ parallel('||l_dbi_dim_data.table_name||') */'||
4132 ' INTO '||l_dbi_dim_data.table_name||' ('||l_lst_select||')'||
4133 ' SELECT '||l_lst_select_tmp_t||
4134 ' FROM BSC_TMP_DBI_DIM T, BSC_TMP_DBI_DIM_ADD N'||
4135 ' WHERE T.USER_CODE = N.USER_CODE';
4136 if bsc_im_utils.g_debug then
4137 write_to_log_file_n(l_sql);
4138 end if;
4139 BSC_UPDATE_UTIL.Execute_Immediate(l_sql);
4140
4141 -- Delete from DIM_TABLE
4142 l_sql := 'DELETE FROM '||l_dbi_dim_data.table_name||
4143 ' WHERE USER_CODE IN (SELECT USER_CODE FROM BSC_TMP_DBI_DIM_DEL)';
4144 if bsc_im_utils.g_debug then
4145 write_to_log_file_n(l_sql);
4146 end if;
4147 BSC_UPDATE_UTIL.Execute_Immediate(l_sql);
4148
4149 COMMIT;
4150
4151 -- Udpate REFRESH_END_TIME in BSC_OBJECT_REFRESH_LOG for this table
4152 l_sql := 'UPDATE bsc_object_refresh_log'||
4153 ' SET refresh_end_time = SYSDATE'||
4154 ' WHERE object_name = :1 AND object_type = :2';
4155 EXECUTE IMMEDIATE l_sql USING l_dbi_dim_data.table_name, l_dim_obj_type;
4156 COMMIT;
4157 END IF;
4158 END IF;
4159 END IF;
4160 ELSE
4161 --AW_INTEGRATION: This is a BIS dimension that is not materialized in BSC
4162 -- We need to insert the deleted codes into BSC_AW_DIM_DELETE table
4163 IF l_dim_for_aw_kpi THEN
4164 -- This procedure will insert into BSC_AW_DIM_DATA all the records
4165 -- existing in AW for the dimension
4166 l_dim_level_list.delete;
4167 l_dim_level_list(1) := l_level_table_name;
4168 bsc_aw_load.dmp_dim_level_into_table(
4169 p_dim_level_list => l_dim_level_list,
4170 p_options => 'DEBUG LOG'
4171 );
4172
4173 -- Now we can compare and insert the deleted rows into BSC_AW_DIM_DELETE
4174 l_sql := 'INSERT /*+ parallel(BSC_AW_DIM_DELETE) */'||
4175 ' INTO BSC_AW_DIM_DELETE (DIM_LEVEL, DELETE_VALUE)'||
4176 ' SELECT DIM_LEVEL, CODE'||
4177 ' FROM BSC_AW_DIM_DATA'||
4178 ' WHERE DIM_LEVEL = :1'||
4179 ' MINUS '||
4180 ' SELECT :2, TO_CHAR(CODE) FROM '||l_level_table_name;
4181 if bsc_im_utils.g_debug then
4182 write_to_log_file_n(l_sql);
4183 end if;
4184 EXECUTE IMMEDIATE l_sql USING l_level_table_name, l_level_table_name;
4185 COMMIT;
4186 END IF;
4187
4188 -- RECURSIVE_DIMS: Refresh the denorm table. We do this only in MV architecture and
4189 -- if the dimension is used by a MV indicator
4190 IF BSC_APPS.bsc_mv AND l_dim_for_mv_kpi THEN
4191 IF Is_Recursive_Dim(l_level_table_name) THEN
4192 l_denorm_table_name := BSC_DBGEN_METADATA_READER.get_denorm_dimension_table(x_dim_short_name);
4193 IF l_denorm_table_name IS NOT NULL THEN
4194 IF NOT Refresh_Denorm_Table(l_level_table_name, l_denorm_table_name) THEN
4195 RAISE e_unexpected_error;
4196 END IF;
4197 END IF;
4198 END IF;
4199 END IF;
4200
4201 END IF;
4202
4203 -- Refresh denormalized table for recursive dimensions for materialized DBI dimensions
4204 IF l_dbi_dim_data.recursive_dim = 'YES' THEN
4205 IF x_dim_short_name = 'ENI_ITEM_VBH_CAT' THEN
4206 IF NOT Denorm_Eni_Item_Vbh_Cat THEN
4207 RAISE e_unexpected_error;
4208 END IF;
4209 ELSIF x_dim_short_name = 'ENI_ITEM_ITM_CAT' THEN
4210 IF NOT Denorm_Eni_Item_Itm_Cat THEN
4211 RAISE e_unexpected_error;
4212 END IF;
4213 ELSIF x_dim_short_name = 'HRI_PER_USRDR_H' THEN
4214 IF NOT Denorm_Hri_Per_Usrdr_H THEN
4215 RAISE e_unexpected_error;
4216 END IF;
4217 ELSIF x_dim_short_name = 'PJI_ORGANIZATIONS' THEN
4218 IF NOT Denorm_Pji_Organizations THEN
4219 RAISE e_unexpected_error;
4220 END IF;
4221 END IF;
4222 END IF;
4223
4224 -- AW_INTEGRATION: We need to bring the bis dimension into AW world
4225 IF l_dim_for_aw_kpi THEN
4226 -- Fix bug#4646856: delete the zero code from bsc_aw_dim_delete table.
4227 -- we cannot delete the zero code from aw
4228 l_sql := 'delete from bsc_aw_dim_delete'||
4229 ' where dim_level = :1 and delete_value = :2';
4230 execute immediate l_sql using l_level_table_name, '0';
4231 commit;
4232 l_dim_level_list.delete;
4233 l_dim_level_list(1) := l_level_table_name;
4234 bsc_aw_load.load_dim(
4235 p_dim_level_list => l_dim_level_list,
4236 p_options => 'DEBUG LOG'
4237 );
4238 END IF;
4239
4240 commit;
4241
4242 RETURN TRUE;
4243
4244 EXCEPTION
4245 WHEN e_unexpected_error THEN
4246 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),
4247 x_source => 'BSC_UPDATE_DIM.Refresh_Dbi_Dimension_Table');
4248 RETURN FALSE;
4249
4250 WHEN OTHERS THEN
4251 BSC_MESSAGE.Add(x_message => SQLERRM,
4252 x_source => 'BSC_UPDATE_DIM.Refresh_Dbi_Dimension_Table');
4253 RETURN FALSE;
4254
4255 END Refresh_Dbi_Dimension_Table;
4256
4257 --LOCKING: new function
4258 /*===========================================================================+
4259 | FUNCTION Refresh_Dbi_Dimension_Table_AT
4260 +============================================================================*/
4261 FUNCTION Refresh_Dbi_Dimension_Table_AT(
4262 x_dim_short_name IN VARCHAR2
4263 ) RETURN BOOLEAN IS
4264 PRAGMA AUTONOMOUS_TRANSACTION;
4265 h_b BOOLEAN;
4266 BEGIN
4267 h_b := Refresh_Dbi_Dimension_Table(x_dim_short_name);
4268 commit; -- all autonomous transaction needs to commit
4269 RETURN h_b;
4270 END Refresh_Dbi_Dimension_Table_AT;
4271
4272
4273 /*===========================================================================+
4274 | PROCEDURE Refresh_DBI_Dimension
4275 +============================================================================*/
4276 PROCEDURE Refresh_Dbi_Dimension(
4277 ERRBUF OUT NOCOPY VARCHAR2,
4278 RETCODE OUT NOCOPY VARCHAR2,
4279 x_dim_short_name IN VARCHAR2
4280 ) IS
4281
4282 e_unexpected_error EXCEPTION;
4283 --LOCKING
4284 e_could_not_get_lock EXCEPTION;
4285
4286 BEGIN
4287 -- Initialize BSC/APPS global variables
4288 BSC_APPS.Init_Bsc_Apps;
4289
4290 -- Initialize the error message stack
4291 BSC_MESSAGE.Init('NO');
4292
4293 --LOCKING: Lock the dimension
4294 IF NOT BSC_UPDATE_LOCK.Lock_DBI_Dimension(x_dim_short_name) THEN
4295 RAISE e_could_not_get_lock;
4296 END IF;
4297
4298 -- Refresh the dbi dimension table
4299 --LOCKING: call the autonomous transaction function
4300 IF NOT Refresh_Dbi_Dimension_Table_AT(x_dim_short_name) THEN
4301 RAISE e_unexpected_error;
4302 END IF;
4303
4304 --LOCKING: commit to release locks
4305 COMMIT;
4306
4307 EXCEPTION
4308 WHEN e_unexpected_error THEN
4309 BSC_MESSAGE.flush;
4310 COMMIT;
4311
4312 BSC_UPDATE_LOG.Write_Errors_To_Log;
4313
4314 ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR');
4315 RETCODE := 2; -- Request completed with errors
4316
4317 --LOCKING
4318 WHEN e_could_not_get_lock THEN
4319 BSC_MESSAGE.flush;
4320 COMMIT;
4321
4322 BSC_UPDATE_LOG.Write_Errors_To_Log;
4323
4324 ERRBUF := 'Loader could not get the required locks to continue.';
4325 RETCODE := 2; -- Request completed with errors
4326
4327 WHEN OTHERS THEN
4328 ROLLBACK;
4329
4330 BSC_MESSAGE.flush;
4331 BSC_MESSAGE.Add(x_message => SQLERRM,
4332 x_source => 'BSC_UPDATE_DIM.Refresh_Dbi_Dimension',
4333 x_mode => 'I');
4334 COMMIT;
4335
4336 BSC_UPDATE_LOG.Write_Errors_To_Log;
4337
4338 ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR');
4339 RETCODE := 2; -- Request completed with errors
4340
4341 END Refresh_Dbi_Dimension;
4342
4343
4344 -- RECURSIVE_DIMS: new function
4345 /*===========================================================================+
4346 | FUNCTION Refresh_Denorm_Table
4347 +============================================================================*/
4348 FUNCTION Refresh_Denorm_Table(
4349 x_level_table_name IN VARCHAR2,
4350 x_denorm_table_name IN VARCHAR2
4351 ) RETURN BOOLEAN IS
4352
4353 l_sql VARCHAR2(32700);
4354 TYPE t_cursor IS REF CURSOR;
4355 l_cursor t_cursor;
4356
4357 l_level_view_name VARCHAR2(30);
4358 l_level_pk_col VARCHAR2(30);
4359
4360 norm_child dbms_sql.varchar2_table;
4361 norm_parent dbms_sql.varchar2_table;
4362 levels dbms_sql.number_table;
4363 parents dbms_sql.varchar2_table;
4364 --
4365 denorm_child dbms_sql.varchar2_table;
4366 denorm_parent dbms_sql.varchar2_table;
4367 dp_level dbms_sql.number_table;
4368 dc_level dbms_sql.number_table;
4369 --
4370 prev_p dbms_sql.varchar2_table;
4371 prev_p_level dbms_sql.number_table;
4372 last_p varchar2(400);
4373 last_level number;
4374 child_level number;
4375
4376
4377 BEGIN
4378
4379 IF NOT BSC_APPS.Table_Exists(x_denorm_table_name) THEN
4380 -- Denorm table does not exists
4381 RETURN TRUE;
4382 END IF;
4383
4384 -- Fix bug#5079365, use level_pk_col instead of relation_col
4385 --select d.level_view_name, r.relation_col
4386 --into l_level_view_name, l_level_pk_col
4387 --from bsc_sys_dim_levels_b d, bsc_sys_dim_level_rels r
4388 --where d.dim_level_id = r.dim_level_id and
4389 -- d.level_table_name = x_level_table_name and
4390 -- r.dim_level_id = r.parent_dim_level_id;
4391 select level_view_name, level_pk_col
4392 into l_level_view_name, l_level_pk_col
4393 from bsc_sys_dim_levels_b
4394 where level_table_name = x_level_table_name;
4395
4396 l_sql := 'select distinct code'||
4397 ' from '||l_level_view_name||
4398 ' where '||l_level_pk_col||' is null';
4399 open l_cursor for l_sql;
4400 loop
4401 fetch l_cursor bulk collect into parents;
4402 exit when l_cursor%notfound;
4403 end loop;
4404 close l_cursor;
4405 --
4406 denorm_child.delete;
4407 denorm_parent.delete;
4408 dc_level.delete;
4409 dp_level.delete;
4410 --
4411 for i in 1..parents.count loop
4412 norm_child.delete;
4413 norm_parent.delete;
4414 l_sql := 'select '||l_level_pk_col||', code, level'||
4415 ' from '||l_level_view_name||
4416 ' start with '||l_level_pk_col||' = :1'||
4417 ' connect by prior code = '||l_level_pk_col;
4418 open l_cursor for l_sql using parents(i);
4419 loop
4420 fetch l_cursor bulk collect into norm_parent, norm_child, levels;
4421 exit when l_cursor%notfound;
4422 end loop;
4423 close l_cursor;
4424 --
4425 prev_p.delete;
4426 last_level := null;
4427 last_p := null;
4428 child_level := null;
4429 --
4430 --add the root node first
4431 denorm_parent(denorm_parent.count+1):=parents(i);
4432 denorm_child(denorm_parent.count):=parents(i);
4433 dp_level(denorm_parent.count):=1;
4434 dc_level(denorm_parent.count):=1;
4435 --
4436 for j in 1..norm_parent.count loop
4437 --delete prev_p if needed. if we come up again, only then we delete from prev_p
4438 if last_level>levels(j) then
4439 for k in reverse 1..prev_p.count loop
4440 if prev_p(k)=norm_parent(j) then
4441 for m in k..prev_p.count loop
4442 prev_p.delete(m);
4443 prev_p_level.delete(m);
4444 end loop;
4445 exit;
4446 end if;
4447 end loop;
4448 end if;
4449 --now insert
4450 child_level:=levels(j)+1;
4451 denorm_parent(denorm_parent.count+1):=norm_parent(j);
4452 denorm_child(denorm_parent.count):=norm_child(j);
4453 dp_level(denorm_parent.count):=levels(j);
4454 dc_level(denorm_parent.count):=child_level;
4455 --now, add elements to prev_p
4456 if last_level<levels(j) then
4457 prev_p(prev_p.count+1):=last_p;
4458 prev_p_level(prev_p.count):=last_level;
4459 end if;
4460 --now, add denorm records
4461 for k in 1..prev_p.count loop
4462 denorm_parent(denorm_parent.count+1):=prev_p(k);
4463 denorm_child(denorm_parent.count):=norm_child(j);
4464 dp_level(denorm_parent.count):=prev_p_level(k);
4465 dc_level(denorm_parent.count):=child_level;
4466 end loop;
4467 --add self level for child
4468 denorm_parent(denorm_parent.count+1):=norm_child(j);
4469 denorm_child(denorm_parent.count):=norm_child(j);
4470 dp_level(denorm_parent.count):=child_level;
4471 dc_level(denorm_parent.count):=child_level;
4472 --now, populate the state variables
4473 last_level:=levels(j);
4474 last_p:=norm_parent(j);
4475 --
4476 end loop;
4477 end loop;
4478
4479 -- update the denorm table
4480 IF BSC_UPDATE_UTIL.Table_Has_Any_Row(x_denorm_table_name) THEN
4481 -- Incremental load
4482 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_DNT');
4483
4484 forall i in 1..denorm_parent.count
4485 execute immediate 'insert into bsc_tmp_dnt (parent_code, code, child_level, parent_level)'||
4486 ' values (:1, :2, :3, :4)'
4487 using denorm_parent(i), denorm_child(i), dc_level(i), dp_level(i);
4488 commit;
4489
4490 l_sql := 'delete from '||x_denorm_table_name||
4491 ' where (parent_code, code, child_level, parent_level) in ('||
4492 ' select parent_code, code, child_level, parent_level'||
4493 ' from '||x_denorm_table_name||
4494 ' minus'||
4495 ' select parent_code, code, child_level, parent_level'||
4496 ' from bsc_tmp_dnt'||
4497 ' )';
4498 execute immediate l_sql;
4499
4500 l_sql := 'insert into '||x_denorm_table_name||' (parent_code, code, child_level, parent_level)'||
4501 ' select parent_code, code, child_level, parent_level'||
4502 ' from bsc_tmp_dnt'||
4503 ' minus'||
4504 ' select parent_code, code, child_level, parent_level'||
4505 ' from '||x_denorm_table_name;
4506 execute immediate l_sql;
4507 commit;
4508 ELSE
4509 -- Initial Load
4510 forall i in 1..denorm_parent.count
4511 execute immediate 'insert into '||x_denorm_table_name||
4512 ' (parent_code, code, child_level, parent_level)'||
4513 ' values (:1, :2, :3, :4)'
4514 using denorm_parent(i), denorm_child(i), dc_level(i), dp_level(i);
4515 commit;
4516 END IF;
4517
4518 commit;
4519 RETURN TRUE;
4520
4521 EXCEPTION
4522 WHEN OTHERS THEN
4523 BSC_MESSAGE.Add(x_message => SQLERRM,
4524 x_source => 'BSC_UPDATE_DIM.Refresh_Denorm_Table');
4525 RETURN FALSE;
4526 END Refresh_Denorm_Table;
4527
4528
4529 /*===========================================================================+
4530 | FUNCTION Refresh_EDW_Dimension
4531 +============================================================================*/
4532 FUNCTION Refresh_EDW_Dimension(
4533 x_dimension_table IN VARCHAR2,
4534 x_mod_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
4535 x_num_mod_dimensions IN OUT NOCOPY NUMBER,
4536 x_checked_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
4537 x_num_checked_dimensions IN OUT NOCOPY NUMBER
4538 ) RETURN BOOLEAN IS
4539
4540 e_unexpected_error EXCEPTION;
4541
4542 h_sql VARCHAR2(32700);
4543
4544 h_relation_cols BSC_UPDATE_UTIL.t_array_of_varchar2;
4545 h_num_relation_cols NUMBER;
4546 h_lst_relation_cols_desc VARCHAR2(32700);
4547 h_lst_relation_cols VARCHAR2(32700);
4548
4549 h_level_pk_col VARCHAR2(30);
4550
4551 h_deleted_records BSC_UPDATE_UTIL.t_array_of_number;
4552 h_num_deleted_records NUMBER;
4553
4554 h_condition VARCHAR2(32700);
4555
4556 h_i NUMBER;
4557
4558 h_child_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;
4559 h_num_child_dimensions NUMBER;
4560 h_parent_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;
4561 h_num_parent_dimensions NUMBER;
4562
4563
4564 BEGIN
4565 h_num_relation_cols := 0;
4566 h_num_deleted_records := 0;
4567 h_num_child_dimensions := 0;
4568 h_num_parent_dimensions := 0;
4569
4570 -- It is possible to try to refresh a dimension which was previously refreshed
4571 -- because of the cascade logical. To prevent this, we insert the dimension into the array
4572 -- x_checked dimensions.
4573 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(x_dimension_table,
4574 x_checked_dimensions,
4575 x_num_checked_dimensions) THEN
4576
4577 BSC_UPDATE_LOG.Write_Line_Log(x_dimension_table, BSC_UPDATE_LOG.OUTPUT);
4578
4579 -- Add the dimension to the array x_checked dimensions
4580 x_num_checked_dimensions := x_num_checked_dimensions + 1;
4581 x_checked_dimensions(x_num_checked_dimensions) := x_dimension_table;
4582
4583 -- Get some information about this dimension table
4584 -- Get level pk column name
4585 h_level_pk_col := Get_Level_PK_Col(x_dimension_table);
4586 -- Get relation cols
4587 h_num_relation_cols := Get_Relation_Cols(x_dimension_table, h_relation_cols);
4588
4589 -- Create a temporal table with the current data
4590 -- Only we are interested in CODE and relation columns.
4591 -- Remember that in EDW all relations are 1-n
4592 -- Drop table if exits
4593 IF NOT BSC_UPDATE_UTIL.Drop_Table('BSC_TMP_DIMENSION') THEN
4594 RAISE e_unexpected_error;
4595 END IF;
4596
4597 -- Create the table
4598 h_lst_relation_cols := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(h_relation_cols, h_num_relation_cols);
4599 h_lst_relation_cols_desc := BSC_UPDATE_UTIL.Make_Lst_Description(h_relation_cols, h_num_relation_cols, 'NUMBER');
4600 IF h_lst_relation_cols IS NOT NULL THEN
4601 h_lst_relation_cols := ', '||h_lst_relation_cols;
4602 h_lst_relation_cols_desc := ', '||h_lst_relation_cols_desc;
4603 END IF;
4604
4605 h_sql := 'CREATE TABLE BSC_TMP_DIMENSION ('||
4606 'CODE NUMBER'||h_lst_relation_cols_desc||
4607 ') TABLESPACE '||BSC_APPS.Get_Tablespace_Name(BSC_APPS.other_table_tbs_type)||
4608 ' '||BSC_APPS.bsc_storage_clause;
4609 BSC_APPS.Do_DDL(h_sql, AD_DDL.CREATE_TABLE, 'BSC_TMP_DIMENSION');
4610
4611 -- Insert records
4612 h_sql := 'INSERT INTO BSC_TMP_DIMENSION (CODE'||h_lst_relation_cols||')'||
4613 ' SELECT CODE'||h_lst_relation_cols||
4614 ' FROM '||x_dimension_table;
4615 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
4616
4617 -- Create unique index
4618 IF NOT BSC_UPDATE_UTIL.Create_Unique_Index('BSC_TMP_DIMENSION',
4619 'BSC_TMP_DIMENSION_U1',
4620 'CODE',
4621 BSC_APPS.other_index_tbs_type) THEN
4622 RAISE e_unexpected_error;
4623 END IF;
4624
4625 -- Refresh dimension table (materialized view)
4626 DBMS_MVIEW.REFRESH(BSC_APPS.BSC_APPS_SCHEMA||'.'||x_dimension_table, 'AF', NULL, FALSE, FALSE, 0, 0, 0, TRUE);
4627
4628 -- Get deleted records (records in BSC_TMP_DIMENSION that are not in x_dimension_table)
4629 h_num_deleted_records := Get_Deleted_Records(x_dimension_table, 'BSC_TMP_DIMENSION', h_deleted_records);
4630
4631 IF h_num_deleted_records > 0 THEN
4632 -- Delete from all system tables rows for deleted values
4633
4634 -- h_condition := BSC_UPDATE_UTIL.Make_Lst_Cond_Number(h_level_pk_col, h_deleted_records, h_num_deleted_records, 'OR');
4635 h_condition := BSC_APPS.Get_New_Big_In_Cond_Number(1, h_level_pk_col);
4636 FOR h_i IN 1..h_num_deleted_records LOOP
4637 BSC_APPS.Add_Value_Big_In_Cond(1, h_deleted_records(h_i));
4638 END LOOP;
4639
4640 IF NOT Delete_Key_Values_In_Tables(h_level_pk_col, h_condition) THEN
4641 RAISE e_unexpected_error;
4642 END IF;
4643
4644 -- Add the dimension table to the array of modified dimensions
4645 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(x_dimension_table,
4646 x_mod_dimensions,
4647 x_num_mod_dimensions) THEN
4648 -- Add the dimension to the array x_checked dimensions
4649 x_num_mod_dimensions := x_num_mod_dimensions + 1;
4650 x_mod_dimensions(x_num_mod_dimensions) := x_dimension_table;
4651 END IF;
4652 END IF;
4653
4654 IF h_num_relation_cols > 0 THEN
4655 -- Check if any dimension item changed any relation
4656 IF Any_Item_Changed_Any_Relation(x_dimension_table, 'BSC_TMP_DIMENSION', h_relation_cols, h_num_relation_cols) THEN
4657 -- Add the dimension table to the array of modified dimensions
4658 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(x_dimension_table,
4659 x_mod_dimensions,
4660 x_num_mod_dimensions) THEN
4661 -- Add the dimension to the array x_checked dimensions
4662 x_num_mod_dimensions := x_num_mod_dimensions + 1;
4663 x_mod_dimensions(x_num_mod_dimensions) := x_dimension_table;
4664 END IF;
4665 END IF;
4666 END IF;
4667
4668 -- Drop the temporal table
4669 IF NOT BSC_UPDATE_UTIL.Drop_Table('BSC_TMP_DIMENSION') THEN
4670 RAISE e_unexpected_error;
4671 END IF;
4672
4673 -- Refresh child dimensions
4674 h_num_child_dimensions := Get_Child_Dimensions(x_dimension_table, h_child_dimensions);
4675 FOR h_i IN 1..h_num_child_dimensions LOOP
4676 IF NOT Refresh_EDW_Dimension(h_child_dimensions(h_i),
4677 x_mod_dimensions,
4678 x_num_mod_dimensions,
4679 x_checked_dimensions,
4680 x_num_checked_dimensions) THEN
4681 RAISE e_unexpected_error;
4682 END IF;
4683 END LOOP;
4684
4685
4686 -- Refresh parent dimensions
4687 h_num_parent_dimensions := Get_Parent_Dimensions(x_dimension_table, h_parent_dimensions);
4688 FOR h_i IN 1..h_num_parent_dimensions LOOP
4689 IF NOT Refresh_EDW_Dimension(h_parent_dimensions(h_i),
4690 x_mod_dimensions,
4691 x_num_mod_dimensions,
4692 x_checked_dimensions,
4693 x_num_checked_dimensions) THEN
4694 RAISE e_unexpected_error;
4695 END IF;
4696 END LOOP;
4697
4698 END IF;
4699
4700 RETURN TRUE;
4701
4702 EXCEPTION
4703 WHEN e_unexpected_error THEN
4704 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_REFRESH_EDW_DIM_FAILED'),
4705 x_source => 'BSC_UPDATE_DIM.Refresh_EDW_Dimension');
4706 RETURN FALSE;
4707
4708 WHEN OTHERS THEN
4709 BSC_MESSAGE.Add(x_message => SQLERRM,
4710 x_source => 'BSC_UPDATE_DIM.Refresh_EDW_Dimension');
4711 RETURN FALSE;
4712 END Refresh_EDW_Dimension;
4713
4714
4715
4716 /*===========================================================================+
4717 | FUNCTION Refresh_EDW_Dimensions
4718 +============================================================================*/
4719 FUNCTION Refresh_EDW_Dimensions(
4720 x_dimension_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
4721 x_num_dimension_tables IN NUMBER
4722 ) RETURN BOOLEAN IS
4723
4724 e_unexpected_error EXCEPTION;
4725
4726 h_mod_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;
4727 h_num_mod_dimensions NUMBER;
4728 h_checked_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;
4729 h_num_checked_dimensions NUMBER;
4730 h_i NUMBER;
4731
4732 BEGIN
4733 h_num_mod_dimensions := 0;
4734 h_num_checked_dimensions := 0;
4735
4736 -- Refresh each dimension. It will be adding modified dimension in the array
4737 -- Also, it will delete from B, S tables the deleted dimension values
4738 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_REFRESHING_EDW_DIM'), BSC_UPDATE_LOG.OUTPUT);
4739 FOR h_i IN 1 .. x_num_dimension_tables LOOP
4740 IF NOT Refresh_EDW_Dimension(x_dimension_tables(h_i),
4741 h_mod_dimensions,
4742 h_num_mod_dimensions,
4743 h_checked_dimensions,
4744 h_num_checked_dimensions) THEN
4745 RAISE e_unexpected_error;
4746 END IF;
4747 END LOOP;
4748
4749 -- Changing a dimension table may affect the totals of the KPIs using
4750 -- that dimension.
4751 -- This procedure mark the affected KPIs with prototype 6 (Recalc data).
4752 -- Once the kpis are marked, Incremental changes will re calculate the data.
4753 IF h_num_mod_dimensions > 0 THEN
4754 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_RECALC_KPI_DIMTABLES'), BSC_UPDATE_LOG.OUTPUT);
4755 FOR h_i IN 1..h_num_mod_dimensions LOOP
4756
4757 UPDATE BSC_KPIS_B K
4758 SET PROTOTYPE_FLAG = 6,
4759 LAST_UPDATED_BY = BSC_APPS.fnd_global_user_id,
4760 LAST_UPDATE_DATE = SYSDATE
4761 WHERE INDICATOR IN (SELECT D.INDICATOR
4762 FROM BSC_KPI_DIM_LEVELS_B D
4763 WHERE K.INDICATOR = D.INDICATOR AND
4764 D.LEVEL_TABLE_NAME = h_mod_dimensions(h_i)) AND
4765 PROTOTYPE_FLAG in (0, 6, 7);
4766
4767 -- Color By KPI: Mark KPIs for color re-calculation
4768 UPDATE bsc_kpi_analysis_measures_b k
4769 SET prototype_flag = BSC_DESIGNER_PVT.C_COLOR_CHANGE -- 7
4770 WHERE indicator IN (SELECT d.indicator
4771 FROM bsc_kpi_dim_levels_b d
4772 WHERE k.indicator = d.indicator
4773 AND d.level_table_name = h_mod_dimensions(h_i));
4774
4775 COMMIT;
4776 END LOOP;
4777 END IF;
4778
4779 -- Synchronize sec assigments
4780 IF NOT Sync_Sec_Assigments THEN
4781 RAISE e_unexpected_error;
4782 END IF;
4783
4784 COMMIT;
4785
4786 RETURN TRUE;
4787
4788 EXCEPTION
4789 WHEN e_unexpected_error THEN
4790 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_REFRESH_EDW_DIM_FAILED'),
4791 x_source => 'BSC_UPDATE_DIM.Refresh_EDW_Dimensions');
4792 RETURN FALSE;
4793
4794 WHEN OTHERS THEN
4795 BSC_MESSAGE.Add(x_message => SQLERRM,
4796 x_source => 'BSC_UPDATE_DIM.Refresh_EDW_Dimensions');
4797 RETURN FALSE;
4798
4799 END Refresh_EDW_Dimensions;
4800
4801
4802 /*===========================================================================+
4803 | FUNCTION Sync_Sec_Assigments
4804 +============================================================================*/
4805 FUNCTION Sync_Sec_Assigments RETURN BOOLEAN IS
4806
4807 h_sql VARCHAR2(32700);
4808
4809 TYPE t_cursor IS REF CURSOR;
4810 h_cursor t_cursor;
4811
4812 h_dim_level_id NUMBER;
4813 h_level_table_name VARCHAR2(30);
4814 l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;
4815 l_num_bind_vars NUMBER;
4816
4817 CURSOR c_list IS
4818 SELECT DISTINCT L.DIM_LEVEL_ID, D.LEVEL_TABLE_NAME
4819 FROM BSC_SYS_COM_DIM_LEVELS L, BSC_SYS_DIM_LEVELS_B D
4820 WHERE L.DIM_LEVEL_ID = D.DIM_LEVEL_ID;
4821
4822 BEGIN
4823 l_num_bind_vars := 0;
4824
4825 -- Get the dimension id and dimension table name for the dimension involved
4826 -- in any list of any tab
4827 /*
4828 h_sql := 'SELECT DISTINCT L.DIM_LEVEL_ID, D.LEVEL_TABLE_NAME'||
4829 ' FROM BSC_SYS_COM_DIM_LEVELS L, BSC_SYS_DIM_LEVELS_B D'||
4830 ' WHERE L.DIM_LEVEL_ID = D.DIM_LEVEL_ID';
4831 */
4832 --OPEN h_cursor FOR h_sql;
4833 OPEN c_list;
4834 FETCH c_list INTO h_dim_level_id, h_level_table_name;
4835 WHILE c_list%FOUND LOOP
4836 -- Delete from BSC_USER_LIST_ACCESS the records which dimension value
4837 -- doest not exist in the dimension table. It deletes all the list assigment
4838 -- for the responsibility in the tab. So if a list in a tab
4839 -- have more that one dimension, if one of them becomes invalid because the
4840 -- dimension value doest not belong to the dimension table, all dimensions
4841 -- for that tab and for that responsibility will be deleted.
4842
4843 -- BSC-BIS-DIMENSIONS: The table column DIM_LEVEL_VALUE in BSC_USER_LIST_ACCESS
4844 -- it is now VARCHAR2 to support BIS/BSC dimensions. So changing condition to use '0'
4845
4846 h_sql := 'DELETE FROM BSC_USER_LIST_ACCESS'||
4847 ' WHERE (RESPONSIBILITY_ID, TAB_ID) IN ('||
4848 ' SELECT LA.RESPONSIBILITY_ID, LA.TAB_ID'||
4849 ' FROM BSC_SYS_COM_DIM_LEVELS L, BSC_USER_LIST_ACCESS LA'||
4850 ' WHERE L.TAB_ID = LA.TAB_ID'||
4851 ' AND L.DIM_LEVEL_INDEX = LA.DIM_LEVEL_INDEX'||
4852 ' AND L.DIM_LEVEL_ID = :1'||
4853 ' AND LA.DIM_LEVEL_VALUE <> ''0'''||
4854 ' AND LA.DIM_LEVEL_VALUE NOT IN ('||
4855 ' SELECT CODE FROM '||h_level_table_name||'))';
4856 l_bind_vars_values.delete;
4857 l_bind_vars_values(1) := TO_CHAR(h_dim_level_id);
4858 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
4859
4860 FETCH c_list INTO h_dim_level_id, h_level_table_name;
4861 END LOOP;
4862 CLOSE c_list;
4863
4864 RETURN TRUE;
4865
4866 EXCEPTION
4867 WHEN OTHERS THEN
4868 BSC_MESSAGE.Add(x_message => SQLERRM,
4869 x_source => 'BSC_UPDATE_DIM.Sync_Sec_Assigments');
4870 RETURN FALSE;
4871
4872 END Sync_Sec_Assigments;
4873
4874
4875 /*===========================================================================+
4876 | FUNCTION Validate_Input_Table
4877 +============================================================================*/
4878 FUNCTION Validate_Input_Table(
4879 x_input_table IN VARCHAR2,
4880 x_dim_table IN VARCHAR2
4881 ) RETURN BOOLEAN IS
4882
4883 e_unexpected_error EXCEPTION;
4884 h_dim_table_type NUMBER;
4885
4886 TYPE t_cursor IS REF CURSOR;
4887 h_cursor t_cursor;
4888
4889 h_sql VARCHAR2(32700);
4890
4891 h_parent_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
4892 h_parent_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
4893 h_num_parents NUMBER;
4894
4895 h_aux_fields BSC_UPDATE_UTIL.t_array_of_varchar2;
4896 h_num_aux_fields NUMBER;
4897
4898 h_i NUMBER;
4899
4900 h_invalid BOOLEAN;
4901
4902 h_null VARCHAR2(250);
4903
4904 h_loading_mode NUMBER;
4905 l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;
4906 l_num_bind_vars NUMBER;
4907
4908 h_num_rows NUMBER;
4909
4910 BEGIN
4911
4912 l_num_bind_vars := 0;
4913
4914 -- Delete the current invalid codes of input table
4915 /*
4916 h_sql := 'DELETE FROM bsc_db_validation'||
4917 ' WHERE input_table_name = :1';
4918 EXECUTE IMMEDIATE h_sql USING x_input_table;
4919 */
4920 DELETE FROM bsc_db_validation
4921 WHERE input_table_name = x_input_table;
4922
4923 -- Get type of dimension table
4924 h_dim_table_type := Get_Dim_Table_Type(x_dim_table);
4925 IF h_dim_table_type = DIM_TABLE_TYPE_UNKNOWN THEN
4926 RAISE e_unexpected_error;
4927 END IF;
4928
4929 -- Get loading mode
4930 /*
4931 h_sql := 'SELECT generation_type'||
4932 ' FROM bsc_db_tables'||
4933 ' WHERE table_name = :1';
4934 OPEN h_cursor FOR h_sql USING x_input_table;
4935 FETCH h_cursor INTO h_loading_mode;
4936 CLOSE h_cursor;
4937 */
4938 SELECT generation_type
4939 INTO h_loading_mode
4940 FROM bsc_db_tables
4941 WHERE table_name = x_input_table;
4942
4943 h_null := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'NULL');
4944 --Fix bug#2562867 The lookup can contain single quotes
4945 h_null := REPLACE(h_null,'''', '''''');
4946
4947 IF h_dim_table_type = DIM_TABLE_TYPE_1N THEN
4948 -- Validate USER_CODE
4949 -- Must be not null and diffent from '0' --> USER_CODE '0' correspondi by design to CODE = 0
4950 -- and we dong allow to import this value.
4951
4952 -- BSC-BIS-DIMENSIONS: No need to change here. We only support to load input tables
4953 -- for BSC dimensions. They always have VARCHAR2 in USER_CODE.
4954
4955 h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code)
4956 SELECT DISTINCT :1, ''USER_CODE'',
4957 NVL(USER_CODE,:2)
4958 FROM '||x_input_table||'
4959 WHERE NVL(USER_CODE, ''0'') = ''0''';
4960 l_bind_vars_values.delete;
4961 l_bind_vars_values(1) := x_input_table;
4962 l_bind_vars_values(2) := h_null;
4963 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
4964
4965 -- Validate NAME
4966 -- Must be not null
4967 h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code)
4968 SELECT DISTINCT :1, ''NAME'', :2
4969 FROM '||x_input_table||'
4970 WHERE NAME IS NULL';
4971 l_bind_vars_values.delete;
4972 l_bind_vars_values(1) := x_input_table;
4973 l_bind_vars_values(2) := h_null;
4974 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
4975
4976 -- NAME should not be duplicated
4977 IF h_loading_mode = 1 THEN
4978 -- Overwrite
4979 h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code)
4980 SELECT DISTINCT :1, ''NAME'', name
4981 FROM '||x_input_table||'
4982 WHERE name IS NOT NULL
4983 GROUP BY name
4984 HAVING count(*) > 1';
4985 l_bind_vars_values.delete;
4986 l_bind_vars_values(1) := x_input_table;
4987 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
4988 ELSE
4989 -- Add/Update
4990 h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code)
4991 SELECT DISTINCT :1, :2, name
4992 FROM (SELECT user_code, name
4993 FROM '||x_input_table||'
4994 UNION
4995 SELECT d.user_code, d.name
4996 FROM '||x_dim_table||' d, '||x_input_table||' i
4997 WHERE d.user_code = i.user_code (+) AND i.user_code IS NULL AND
4998 d.language = :3 AND d.source_lang = :4)
4999 WHERE name IS NOT NULL
5000 GROUP BY name
5001 HAVING count(*) > 1';
5002 l_bind_vars_values.delete;
5003 l_bind_vars_values(1) := x_input_table;
5004 l_bind_vars_values(2) := 'NAME';
5005 l_bind_vars_values(3) := userenv('LANG');
5006 l_bind_vars_values(4) := userenv('LANG');
5007 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,4);
5008 END IF;
5009 END IF;
5010
5011 -- Validate parents
5012 h_num_parents := Get_Info_Parents_Dimensions(x_dim_table, h_parent_tables, h_parent_keys);
5013 FOR h_i IN 1 .. h_num_parents LOOP
5014 -- Value must exist in the parent table (nulls will be catched here) and
5015 -- cannot be '0' becasue no item can be child of total.
5016
5017 -- BSC-BIS-DIMENSIONS Note: A BSC dimension could have a BIS dimension as parent.
5018 -- So the parent key can be NUMBER/VARCHAR2. The following query will support both cases.
5019
5020 h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code)
5021 SELECT DISTINCT :1, :2,
5022 NVL(TO_CHAR('||h_parent_keys(h_i)||'_USR), :3)
5023 FROM '||x_input_table||'
5024 WHERE NVL(TO_CHAR('||h_parent_keys(h_i)||'_USR), ''0'') NOT IN (
5025 SELECT TO_CHAR(USER_CODE) FROM '||h_parent_tables(h_i)||' WHERE TO_CHAR(USER_CODE) <> ''0'')';
5026 l_bind_vars_values.delete;
5027 l_bind_vars_values(1) := x_input_table;
5028 l_bind_vars_values(2) := h_parent_keys(h_i)||'_USR';
5029 l_bind_vars_values(3) := h_null;
5030 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,3);
5031 END LOOP;
5032
5033 -- Validate auxiliar fields
5034 IF h_dim_table_type = DIM_TABLE_TYPE_1N THEN
5035 h_num_aux_fields := Get_Aux_Fields_Dim_Table(x_dim_table, h_aux_fields);
5036 FOR h_i IN 1 .. h_num_aux_fields LOOP
5037 -- Must be not null
5038 h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code)
5039 SELECT DISTINCT :1, :2, :3
5040 FROM '||x_input_table||'
5041 WHERE '||h_aux_fields(h_i)||' IS NULL';
5042 l_bind_vars_values.delete;
5043 l_bind_vars_values(1) := x_input_table;
5044 l_bind_vars_values(2) := h_aux_fields(h_i);
5045 l_bind_vars_values(3) := h_null;
5046 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,3);
5047 END LOOP;
5048 END IF;
5049
5050 -- Check if there were invalid codes
5051 /*
5052 h_sql := 'SELECT COUNT(*) FROM BSC_DB_VALIDATION'||
5053 ' WHERE ROWNUM < :1 AND INPUT_TABLE_NAME = :2';
5054 OPEN h_cursor FOR h_sql USING 2, x_input_table;
5055 FETCH h_cursor INTO h_num_rows;
5056 CLOSE h_cursor;
5057 */
5058 SELECT COUNT(*)
5059 INTO h_num_rows
5060 FROM BSC_DB_VALIDATION
5061 WHERE ROWNUM < 2 AND INPUT_TABLE_NAME = x_input_table;
5062
5063 IF h_num_rows > 0 THEN
5064 h_invalid := TRUE;
5065 ELSE
5066 h_invalid := FALSE;
5067 END IF;
5068
5069 RETURN NOT h_invalid;
5070
5071 EXCEPTION
5072 WHEN e_unexpected_error THEN
5073 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_ITABLE_VALID_FAILED'),
5074 x_source => 'BSC_UPDATE_DIM.Validate_Input_Table');
5075 RETURN NULL;
5076
5077 WHEN OTHERS THEN
5078 BSC_MESSAGE.Add(x_message => SQLERRM,
5079 x_source => 'BSC_UPDATE_DIM.Validate_Input_Table');
5080 RETURN NULL;
5081 END Validate_Input_Table;
5082
5083
5084 --LOCKING: new function
5085 /*===========================================================================+
5086 | FUNCTION Validate_Input_Table_AT
5087 +============================================================================*/
5088 FUNCTION Validate_Input_Table_AT(
5089 x_input_table IN VARCHAR2,
5090 x_dim_table IN VARCHAR2
5091 ) RETURN BOOLEAN IS
5092 PRAGMA AUTONOMOUS_TRANSACTION;
5093 h_b BOOLEAN;
5094 BEGIN
5095 h_b := Validate_Input_Table(x_input_table, x_dim_table);
5096 commit; -- all autonomous transaction needs to commit
5097 RETURN h_b;
5098 END Validate_Input_Table_AT;
5099
5100
5101 /*===========================================================================+
5102 | FUNCTION WriteRemovedKeyItems
5103 +============================================================================*/
5104 FUNCTION WriteRemovedKeyItems RETURN BOOLEAN IS
5105 e_unexpected_error EXCEPTION;
5106
5107 TYPE t_cursor IS REF CURSOR;
5108 h_cursor t_cursor;
5109
5110 /*
5111 c_indicators t_cursor;
5112 c_indicators_sql VARCHAR2(2000) := 'SELECT d.indicator, d.level_table_name, d.default_key_value'||
5113 ' FROM bsc_kpi_dim_levels_b d, bsc_kpis_b k'||
5114 ' WHERE d.indicator = k.indicator AND'||
5115 ' d.default_key_value IS NOT NULL';
5116 */
5117 CURSOR c_indicators IS
5118 SELECT d.indicator, d.level_table_name, d.default_key_value
5119 FROM bsc_kpi_dim_levels_b d, bsc_kpis_b k
5120 WHERE d.indicator = k.indicator AND
5121 d.default_key_value IS NOT NULL;
5122
5123 h_indicator NUMBER;
5124 h_level_table_name bsc_kpi_dim_levels_b.level_table_name%TYPE;
5125 h_default_key_value bsc_kpi_dim_levels_b.default_key_value%TYPE;
5126
5127 h_sql VARCHAR2(32700);
5128
5129 h_code NUMBER;
5130
5131 h_header BOOLEAN;
5132
5133 C_INDICATOR_W CONSTANT NUMBER := 15;
5134 C_DIMENSION_TABLE_W CONSTANT NUMBER := 35;
5135 C_DEFAULT_VALUE_W CONSTANT NUMBER := 15;
5136
5137 BEGIN
5138 h_header := FALSE;
5139
5140 --OPEN c_indicators FOR c_indicators_sql;
5141 OPEN c_indicators;
5142 FETCH c_indicators INTO h_indicator, h_level_table_name, h_default_key_value;
5143 WHILE c_indicators%FOUND LOOP
5144
5145 h_sql := 'SELECT code FROM '||h_level_table_name||
5146 ' WHERE code = :1';
5147
5148 OPEN h_cursor FOR h_sql USING h_default_key_value;
5149 FETCH h_cursor INTO h_code;
5150 IF h_cursor%NOTFOUND THEN
5151 -- The default value wa deleted
5152 IF NOT h_header THEN
5153 BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
5154 BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
5155 BSC_UPDATE_LOG.OUTPUT);
5156 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_DFT_DIMVALUE_MISSING'), BSC_UPDATE_LOG.OUTPUT);
5157 BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
5158 BSC_UPDATE_LOG.OUTPUT);
5159 BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'KPI_CODE'), C_INDICATOR_W)||
5160 RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_TABLE_NAME'), C_DIMENSION_TABLE_W)||
5161 RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_KPIDESIGNER', 'DEFAULT_VALUE'), C_DEFAULT_VALUE_W),
5162 BSC_UPDATE_LOG.OUTPUT);
5163 h_header := TRUE;
5164 END IF;
5165
5166 --Fix bug#4080680 do not use TO_CHAR(h_defualt_key_value)
5167 BSC_UPDATE_LOG.Write_Line_Log(RPAD(TO_CHAR(h_indicator), C_INDICATOR_W)||
5168 RPAD(h_level_table_name, C_DIMENSION_TABLE_W)||
5169 RPAD(h_default_key_value, C_DEFAULT_VALUE_W),
5170 BSC_UPDATE_LOG.OUTPUT);
5171 END IF;
5172 CLOSE h_cursor;
5173
5174 FETCH c_indicators INTO h_indicator, h_level_table_name, h_default_key_value;
5175 END LOOP;
5176 CLOSE c_indicators;
5177
5178 BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
5179
5180 RETURN TRUE;
5181
5182 EXCEPTION
5183 WHEN OTHERS THEN
5184 BSC_MESSAGE.Add(x_message => SQLERRM,
5185 x_source => 'BSC_UPDATE_DIM.WriteRemovedKeyItems');
5186 RETURN FALSE;
5187
5188 END WriteRemovedKeyItems;
5189
5190 procedure write_to_log_file(p_message varchar2) is
5191 Begin
5192 BSC_IM_UTILS.write_to_log_file(p_message);
5193 Exception when others then
5194 BSC_IM_UTILS.g_status_message:=sqlerrm;
5195 null;
5196 End;
5197
5198 procedure write_to_log_file_n(p_message varchar2) is
5199 begin
5200 write_to_log_file(' ');
5201 write_to_log_file(p_message);
5202 Exception when others then
5203 BSC_IM_UTILS.g_status_message:=sqlerrm;
5204 null;
5205 end;
5206
5207
5208 END BSC_UPDATE_DIM;