[Home] [Help]
PACKAGE BODY: APPS.EDW_GL_ACCT_M_T
Source
1 PACKAGE BODY EDW_GL_ACCT_M_T AS
2 /* $Header: EDWVBHCB.pls 120.0 2005/06/01 17:56:30 appldev noship $ */
3
4 ---add global variable to control num of output we need
5 ---for diamond values and standalone values
6 G_DEBUG Boolean:=false;
7 g_diamond_output number;
8 g_rootsetup_error number;
9 g_standalone number;
10 g_err_cum_timestamp number;
11 g_rows_updated number;
12 g_err_smp_size number;
13 g_rows_inserted number;
14 g_not_classified_type varchar2(100);
15 g_na_edw varchar2(100);
16 g_na_err varchar2(100);
17 g_all varchar2(100);
18
19
20 TYPE t_value_desc_pair_rec IS RECORD (
21 value edw_gl_acct1_m.l1_pk%TYPE,
22 name edw_gl_acct1_m.l1_name%TYPE,
23 type edw_gl_acct1_m.l1_type%TYPE,
24 description edw_gl_acct1_m.l1_description%TYPE);
25
26 TYPE t_vbh_level_table IS TABLE OF t_value_desc_pair_rec
27 INDEX BY BINARY_INTEGER;
28 g_vbh_level_table t_vbh_level_table;
29
30
31 PROCEDURE INITDEBUG IS
32 BEGIN
33 IF (fnd_profile.value('EDW_DEBUG') = 'Y') THEN
34 g_debug := true;
35 edw_log.put_line('debug mode true');
36 ELSE
37 g_debug := false;
38 edw_log.put_line('debug mode false');
39 END IF;
40 END INITDEBUG;
41
42 PROCEDURE VBHDEBUG(
43 p_log varchar2)
44 IS
45 BEGIN
46 if( g_debug) then
47 -- calling put_line directly,
48 -- as the edw_log.put_names was never called in EDW_OWB_COLLECTION_UTIL, the debug_line won't work.
49 edw_log.put_line(p_log);
50 end if;
51 END VBHDEBUG ;
52
53 /**
54 Added for bug 4124723, retunrs schema name for a product
55 **/
56 Function get_bis_schema_name return varchar2 is
57 l_dummy1 varchar2(2000);
58 l_dummy2 varchar2(2000);
59 l_schema varchar2(400);
60 l_prod_no VARCHAR2(30);
61 Begin
62 l_prod_no :='BIS';
63 if FND_INSTALLATION.GET_APP_INFO(l_prod_no,l_dummy1, l_dummy2,l_schema) = false then
64 edw_log.put_line('FND_INSTALLATION.GET_APP_INFO returned with error');
65 return null;
66 end if;
67 return l_schema;
68 Exception when others then
69 edw_log.put_line('Error in get_schema_name '||sqlerrm);
70 return null;
71 End;
72
73 -- -----------------------------------------------------------------------
74 -- Procedure: check_precedence
75 -- Description: This function checks the precedence to see if p_acct_name1 is
76 -- the child of p_acct_name2. p_result returns true if p_acct_name1 is
77 -- the child of p_acct_name2, otherwise it returns false.
78 -- ----------------------------------------------------------------------
79 procedure check_precedence(p_acct_name1 in varchar2,
80 p_acct_name2 in varchar2,
81 p_result out nocopy boolean) is
82 l_cursor_id integer;
83 l_select_stmt varchar2(500);
84 l_rows_selected integer;
85 l_rows_fetched integer;
86 l_count number;
87 begin
88
89 l_select_stmt:= 'select count(*) from '|| g_vbh_temp_table_name||'
90 where child=:m1
91 and child in (
92 SELECT child
93 FROM '||g_vbh_temp_table_name||'
94 where child is not null
95 START WITH parent =:m2
96 CONNECT BY parent=PRIOR child)';
97 l_cursor_id:=DBMS_SQL.OPEN_CURSOR;
98 DBMS_SQL.parse(l_cursor_id,l_select_stmt,DBMS_SQL.V7);
99 DBMS_SQL.bind_variable(l_cursor_id,':m1',p_acct_name1);
100 DBMS_SQL.bind_variable(l_cursor_id,':m2',p_acct_name2);
101 DBMS_SQL.define_column(l_cursor_id,1,l_count);
102 l_rows_selected:=DBMS_SQL.execute(l_cursor_id);
103 l_rows_fetched:=DBMS_SQL.fetch_rows(l_cursor_id);
104 DBMS_SQL.column_value (l_cursor_id,1,l_count);
105 DBMS_SQL.close_cursor(l_cursor_id);
106
107 if l_count=0 then
108 p_result:=false;
109 else
110 p_result:=true;
111 end if ;
112 exception
113 when others then
114 p_result:=false;
115 DBMS_SQL.close_cursor(l_cursor_id);
116
117 if(sqlcode = -1436) then
118 edw_log.put_line('Error :'||
119 'When checking precedence between ' || p_acct_name1 || ' and ' ||
120 p_acct_name2 || ' in '|| g_vbh_temp_table_name
121 ||'. '||sqlcode||' : '||sqlerrm);
122 else
123 edw_log.put_line('Error :'||
124 'When updating the TYPE for '|| g_vbh_temp_table_name
125 ||'. '||sqlcode||' : '||sqlerrm);
126 end if;
127
128 raise;
129 end check_precedence;
130
131 -- -----------------------------------------------------------------------
132 -- Procedure: load_type_table
133 -- Description: This procedure reads in table "edw_segment_classes" and
134 -- stores it to a pl/sql table "g_acct_type_root1".
135 -- -----------------------------------------------------------------------
136 procedure load_type_table is
137 l_table_counter integer :=0;
138 l_loop_counter integer:=0;
139 type t_edw_account_class_cur is ref cursor;
140 cur_edw_account_class t_edw_account_class_cur;
141
142 begin
143 open cur_edw_account_class for
144 select t1.value||'-'||t2.set_of_books_id||'-'||t2.instance,
145 t1.type
146 from edw_segment_classes t1, edw_set_of_books t2
147 where t1.edw_set_of_books_id = t2.edw_set_of_books_id
148 and (t1.segment_name,t2.chart_of_accounts_id,lower(t2.instance)) in
149 (select segment_name,structure_num,lower(instance_code)
150 FROM EDW_FLEX_SEG_MAPPINGS_V
151 WHERE DIMENSION_SHORT_NAME=g_dimension_name);
152 loop
153 l_table_counter :=l_table_counter +1;
154 fetch cur_edw_account_class
155 into g_acct_type_root1(l_table_counter );
156 exit when cur_edw_account_class%NOTFOUND;
157 end loop; --for cur_edw_account_class
158 close cur_edw_account_class ;
159 exception
160 when others then
161 edw_log.put_line('Error:'||
162 'When reading in table edw_segment_classes for '
163 ||g_dimension_name ||'. '||sqlcode||' : '||sqlerrm);
164 raise;
165 end load_type_table;
166
167 -- -----------------------------------------------------------------------
168 -- Procedure: reorder_type_table
169 -- Description: This procedure sorts the "g_acct_type_root1" pl/sql
170 -- table based on hierarchical relationship of elements in "edw_vbh_temp"
171 -- table. It stores the sorted result to "g_acct_type_root2" pl/sql table.
172 -- ----------------------------------------------------------------------
173 procedure reorder_type_table is
174 l_loop_counter integer;
175 l_outer_loop_counter integer;
176 l_inner_loop_counter integer;
177 l_table_length_counter integer;
178 l_result_table_counter integer:=0;
179 l_result boolean:=false;
180 l_table_orig_length integer:=g_acct_type_root1.count;
181
182 begin
183 while g_acct_type_root1.count >0 loop
184 for l_outer_loop_counter in 1..l_table_orig_length loop
185 if g_acct_type_root1.exists(l_outer_loop_counter) then
186 l_result :=false; --reset the l_result value
187 for l_inner_loop_counter in 1..l_table_orig_length
188 loop
189 if l_inner_loop_counter <> l_outer_loop_counter and
190 g_acct_type_root1.exists(l_inner_loop_counter) then
191 check_precedence(
192 g_acct_type_root1(l_inner_loop_counter).name,
193 g_acct_type_root1(l_outer_loop_counter).name,
194 l_result);
195 if(l_result) then exit;
196 end if;--for check_precedence
197 end if ;
198 end loop; --for inner loop
199 if(l_result=false) then
200 l_result_table_counter:=l_result_table_counter+1;
201 g_acct_type_root2(l_result_table_counter):=
202 g_acct_type_root1(l_outer_loop_counter);
203 g_acct_type_root1.delete(l_outer_loop_counter);
204 end if;
205 end if ;--for outer loop counter exist
206 end loop; --for outer loop
207 end loop;--for while loop
208
209 exception
210 when others then
211 edw_log.put_line(
212 'Error: When reordering edw_segment_classes table for '|| g_dimension_name||'. '||sqlcode||' : '||sqlerrm);
213 raise;
214 end reorder_type_table;
215
216
217 procedure update_temp_table(p_acct_name in varchar2,
218 p_acct_type in varchar2) is
219 l_CursorID INTEGER;
220 l_UpdateStmt VARCHAR2(500);
221 l_RowsUpdated integer;
222
223 BEGIN
224 l_CursorID := DBMS_SQL.OPEN_CURSOR;
225 l_UpdateStmt :=
226 'UPDATE '||g_vbh_temp_table_name||'
227 SET child_type= :b_acct_type
228 WHERE child =:b_acct_name';
229 DBMS_SQL.PARSE(l_CursorID,l_UpdateStmt,DBMS_SQL.V7);
230 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':b_acct_type',p_acct_type);
231 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':b_acct_name',p_acct_name);
232
233 l_RowsUpdated := DBMS_SQL.EXECUTE(l_CursorID);
234 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
235 l_CursorID := DBMS_SQL.OPEN_CURSOR;
236 l_UpdateStmt :=
237 'UPDATE '|| g_vbh_temp_table_name||'
238 SET parent_type= :b_acct_type
239 WHERE parent =:b_acct_name';
240
241 DBMS_SQL.PARSE(l_CursorID,l_UpdateStmt,DBMS_SQL.V7);
242 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':b_acct_type',p_acct_type);
243 DBMS_SQL.BIND_VARIABLE(l_CursorID, ':b_acct_name',p_acct_name);
244
245 l_RowsUpdated:= DBMS_SQL.EXECUTE(l_CursorID);
246 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
247 exception
248 when others then
249 DBMS_SQL.close_cursor(l_CursorID);
250 edw_log.put_line('Error:'||
251 'when updating table '|| g_vbh_temp_table_name||' for '||
252 p_acct_name||' '||sqlcode||' : '||sqlerrm);
253 raise;
254 END update_temp_table;
255
256 procedure update_class is
257 l_loop_counter integer :=0;
258 l_vbh_acct_name edw_vbh_temp1.parent%TYPE;
259 type t_vbh_temp_cur IS REF CURSOR;
260 cur_vbh_temp t_vbh_temp_cur;
261
262 begin
263 load_type_table;
264 reorder_type_table;
265 for l_loop_counter in reverse 1..g_acct_type_root2.count
266 loop
267 -- update type columns in temp table
268 -- for g_acct_type_root2(l_loop_counter).name
269 update_temp_table(g_acct_type_root2(l_loop_counter).name,
270 g_acct_type_root2(l_loop_counter).type);
271
272 -- update type columns in temp table
273 -- for the children of g_acct_type_root2(l_loop_counter).name
274
275 open cur_vbh_temp for
276 'select child from '|| g_vbh_temp_table_name||
277 ' START WITH parent = :s
278 connect by parent=PRIOR child'
279 using g_acct_type_root2(l_loop_counter).name;
280 loop
281 FETCH cur_vbh_temp INTO l_vbh_acct_name;
282 exit when cur_vbh_temp%NOTFOUND;
283
284 update_temp_table(l_vbh_acct_name,
285 g_acct_type_root2(l_loop_counter).type);
286
287 end loop;
288 close cur_vbh_temp;
289 end loop;
290 exception
291 when others then
292 edw_log.put_line(
293 'Error : When updating account type in '|| g_dimension_name
294 ||' '||sqlcode||' : '||sqlerrm);
295 raise;
296 end;
297
298 procedure clean_up_temp_table as
299 l_cursor_id integer;
300 l_rows_deleted integer:=0;
301 l_delete_stmt varchar2(50);
302 l_truncate_sql varchar2(200);
303
304 begin
305 l_truncate_sql:='truncate table '||get_bis_schema_name||'.'||g_vbh_temp_table_name;
306 VBHDEBUG('Executing '|| l_truncate_sql);
307 execute immediate l_truncate_sql;
308 VBHDEBUG('finished truncate '||g_vbh_temp_table_name);
309 commit;
310 exception
311 when others then
312 edw_Log.put_line('error happened when truncate '||g_vbh_temp_table_name||' '||sqlcode||':'||sqlerrm);
313 raise;
314 end clean_up_temp_table ;
315
316
317 procedure clean_up_dimension_table as
318 l_truncate_sql varchar2(200);
319 begin
320 l_truncate_sql:='truncate table '||get_bis_schema_name||'.'||g_dimension_name;
321 VBHDEBUG('Executing '|| l_truncate_sql);
322 execute immediate l_truncate_sql;
323 VBHDEBUG('finished truncating '||g_dimension_name);
324 exception
325 when others then
326 edw_log.put_line('Error happened when truncating '||g_dimension_name||' '||sqlcode||':'||sqlerrm);
327 raise;
328 end clean_up_dimension_table ;
329
330 procedure clean_up_global_temp_table as
331 l_truncate_sql varchar2(200);
332 begin
333 l_truncate_sql:='truncate table '||get_bis_schema_name||'.'||g_global_temp_table;
334 VBHDEBUG('Executing '|| l_truncate_sql);
335 execute immediate l_truncate_sql;
336 VBHDEBUG('finished truncating '||g_global_temp_table);
337 commit;
338 exception
339 when others then
340 edw_log.put_line('Error happened when truncating table '||g_global_temp_table||' '||sqlcode||':'||sqlerrm);
341 raise;
342 end clean_up_global_temp_table ;
343
344 -- simple timing tools, setTimer and logTime.
345 -- could be used for performance tuning.
346 PROCEDURE setTimer(
347 p_log_timstamp in out nocopy date)
348 IS
349 BEGIN
350 p_log_timstamp := sysdate;
351 END;
352
353
354 PROCEDURE logCumulatedTime(
355 p_log_timstamp date,
356 p_cumulated in out nocopy number)
357 IS
358 l_duration number := null;
359 BEGIN
360 l_duration := sysdate - p_log_timstamp;
361 p_cumulated := p_cumulated + l_duration;
362 END;
363
364
365
366 PROCEDURE logTime(
367 p_process varchar2,
368 p_log_timstamp date)
369 IS
370 l_duration number := null;
371 BEGIN
372 l_duration := sysdate - p_log_timstamp;
373 VBHDEBUG('Process Time for '|| p_process || ' : ' || edw_log.duration(l_duration));
374 VBHDEBUG(' ');
375 END;
376
377 procedure insert_default_value(
381 p_desc IN edw_gl_acct1_m.l1_description%TYPE) as
378 p_pk IN edw_gl_acct1_m.l1_name%TYPE,
379 p_pk_key IN edw_gl_acct1_m.l1_pk_key%TYPE,
380 p_name IN edw_gl_acct1_m.l1_name%TYPE,
382 l_cursor_id integer;
383 l_insert_stmt varchar2(5000);
384 l_rows_inserted integer :=0;
385
386 begin
387 l_cursor_id:=DBMS_SQL.open_cursor;
388 l_insert_stmt:='INSERT INTO '|| g_dimension_name||'(L1_pk ,l1_pk_key,l1_name,l1_description
389 ,l1_type,type_pk,type_name,all_name,all_pk,
390 H102_pk ,H103_pk ,H104_pk ,H105_pk ,H106_pk ,H107_pk ,H108_pk ,
391 H102_name ,H103_name ,H104_name ,H105_name ,H106_name ,H107_name ,H108_name ,
392 H102_type ,H103_type ,H104_type ,H105_type ,H106_type ,H107_type ,H108_type ,
393 H109_pk ,H110_pk ,H111_pk ,H112_pk ,H113_pk ,H114_pk ,H115_pk ,
394 H109_name ,H110_name ,H111_name ,H112_name ,H113_name ,H114_name ,H115_name ,
395 H109_type ,H110_type ,H111_type ,H112_type ,H113_type ,H114_type ,H115_type ,
396 H202_pk ,H203_pk ,H204_pk ,H205_pk ,H206_pk ,H207_pk ,H208_pk ,
397 H202_name ,H203_name ,H204_name ,H205_name ,H206_name ,H207_name ,H208_name ,
398 H202_type ,H203_type ,H204_type ,H205_type ,H206_type ,H207_type ,H208_type ,
399 H209_pk ,H210_pk ,H211_pk ,H212_pk ,H213_pk ,H214_pk ,H215_pk ,
400 H209_name ,H210_name ,H211_name ,H212_name ,H213_name ,H214_name ,H215_name ,
401 H209_type ,H210_type ,H211_type ,H212_type ,H213_type ,H214_type ,H215_type ,
402 H302_pk ,H303_pk ,H304_pk ,H305_pk ,H306_pk ,H307_pk ,H308_pk ,
403 H302_name ,H303_name ,H304_name ,H305_name ,H306_name ,H307_name ,H308_name ,
404 H302_type ,H303_type ,H304_type ,H305_type ,H306_type ,H307_type ,H308_type ,
405 H309_pk ,H310_pk ,H311_pk ,H312_pk ,H313_pk ,H314_pk ,H315_pk ,
406 H309_name ,H310_name ,H311_name ,H312_name ,H313_name ,H314_name ,H315_name ,
407 H309_type ,H310_type ,H311_type ,H312_type ,H313_type ,H314_type ,H315_type ,
408 H402_pk ,H403_pk ,H404_pk ,H405_pk ,H406_pk ,H407_pk ,H408_pk ,
409 H402_name ,H403_name ,H404_name ,H405_name ,H406_name ,H407_name ,H408_name ,
410 H402_type ,H403_type ,H404_type ,H405_type ,H406_type ,H407_type ,H408_type ,
411 H409_pk ,H410_pk ,H411_pk ,H412_pk ,H413_pk ,H414_pk ,H415_pk ,
412 H409_name ,H410_name ,H411_name ,H412_name ,H413_name ,H414_name ,H415_name ,
413 H409_type ,H410_type ,H411_type ,H412_type ,H413_type ,H414_type ,H415_type ,creation_date,LAST_UPDATE_DATE)
414 values (:b_pk, :b_pk_key,:b_name,:b_desc, '''|| g_na_edw||''', '''|| g_na_edw||''', '''|| g_na_edw||''',
415 '''|| g_na_edw||''', ''NA_EDW'',
416 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
417 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
418 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
419 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
420 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
421 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
422 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
423 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
424 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
425 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
426 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
427 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
428 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
429 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
430 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
431 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
432 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
433 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
434 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
435 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
436 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
437 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
438 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
439 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',sysdate,sysdate)';
440
441 DBMS_SQL.parse(l_cursor_id,l_insert_stmt,DBMS_SQL.V7);
442 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':b_pk',p_pk);
443 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':b_pk_key' , p_pk_key);
444 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':b_name' , p_name);
448 exception
445 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':b_desc' , p_desc);
446 l_rows_inserted :=DBMS_SQL.EXECUTE(l_cursor_id);
447 DBMS_SQL.close_cursor(l_cursor_id);
449 when others then
450 edw_log.put_line('Error:'||
451 'When inserting the default value into '|| g_dimension_name
452 ||' '||sqlcode ||' : '||sqlerrm);
453 raise;
454 end insert_default_value;
455
456
457 procedure insert_non_active_values
458 as
459 l_rows_inserted integer :=0;
460 l_value_stmt varchar2(32767) := NULL;
461 l_log_timestamp Date := NULL;
462
463 begin
464 setTimer(l_log_timestamp); -- for errors only
465 l_value_stmt:= 'INSERT INTO '|| g_dimension_name ||
466 '( L1_pk ,l1_pk_key,l1_name,l1_description' ||
467 ' ,l1_type,type_pk,type_name,all_name,all_pk,' ||
468 ' H102_pk ,H103_pk ,H104_pk ,H105_pk ,H106_pk ,H107_pk ,H108_pk ,' ||
469 ' H102_name ,H103_name ,H104_name ,H105_name ,H106_name ,H107_name ,H108_name ,' ||
470 ' H102_type ,H103_type ,H104_type ,H105_type ,H106_type ,H107_type ,H108_type ,' ||
471 ' H109_pk ,H110_pk ,H111_pk ,H112_pk ,H113_pk ,H114_pk ,H115_pk ,' ||
472 ' H109_name ,H110_name ,H111_name ,H112_name ,H113_name ,H114_name ,H115_name ,' ||
473 ' H109_type ,H110_type ,H111_type ,H112_type ,H113_type ,H114_type ,H115_type ,' ||
474 ' H202_pk ,H203_pk ,H204_pk ,H205_pk ,H206_pk ,H207_pk ,H208_pk ,' ||
475 ' H202_name ,H203_name ,H204_name ,H205_name ,H206_name ,H207_name ,H208_name ,' ||
476 ' H202_type ,H203_type ,H204_type ,H205_type ,H206_type ,H207_type ,H208_type ,' ||
477 ' H209_pk ,H210_pk ,H211_pk ,H212_pk ,H213_pk ,H214_pk ,H215_pk ,' ||
478 ' H209_name ,H210_name ,H211_name ,H212_name ,H213_name ,H214_name ,H215_name ,' ||
479 ' H209_type ,H210_type ,H211_type ,H212_type ,H213_type ,H214_type ,H215_type ,' ||
480 ' H302_pk ,H303_pk ,H304_pk ,H305_pk ,H306_pk ,H307_pk ,H308_pk ,' ||
481 ' H302_name ,H303_name ,H304_name ,H305_name ,H306_name ,H307_name ,H308_name ,' ||
482 ' H302_type ,H303_type ,H304_type ,H305_type ,H306_type ,H307_type ,H308_type ,' ||
483 ' H309_pk ,H310_pk ,H311_pk ,H312_pk ,H313_pk ,H314_pk ,H315_pk ,' ||
484 ' H309_name ,H310_name ,H311_name ,H312_name ,H313_name ,H314_name ,H315_name ,' ||
485 ' H309_type ,H310_type ,H311_type ,H312_type ,H313_type ,H314_type ,H315_type ,' ||
486 ' H402_pk ,H403_pk ,H404_pk ,H405_pk ,H406_pk ,H407_pk ,H408_pk ,' ||
487 ' H402_name ,H403_name ,H404_name ,H405_name ,H406_name ,H407_name ,H408_name ,' ||
488 ' H402_type ,H403_type ,H404_type ,H405_type ,H406_type ,H407_type ,H408_type ,' ||
489 ' H409_pk ,H410_pk ,H411_pk ,H412_pk ,H413_pk ,H414_pk ,H415_pk ,' ||
490 ' H409_name ,H410_name ,H411_name ,H412_name ,H413_name ,H414_name ,H415_name ,' ||
491 ' H409_type ,H410_type ,H411_type ,H412_type ,H413_type ,H414_type ,H415_type ,' ||
492 ' creation_date, last_update_date) ' ||
493 ' select a.l1_pk,a.l1_pk_key,a.l1_name,a.l1_description , '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',''NA_EDW'',
494 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
495 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
496 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
497 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
498 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
499 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
500 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
501 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
502 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
503 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
504 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
505 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
506 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
507 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
508 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
509 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
510 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
511 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
512 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
513 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
514 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
515 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
519 ' select b.l1_pk from '|| g_dimension_name||' b where a.l1_pk=b.l1_pk )';
516 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
517 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',sysdate,sysdate ' ||
518 ' from '|| g_global_temp_table||' a where not exists ('||
520 VBHDEBUG('Executing statement : ' || l_value_stmt);
521 execute immediate l_value_stmt;
522 l_rows_inserted := sql%rowcount;
523 VBHDEBUG('Number of non-active rows inserted : ' || l_rows_inserted);
524 exception
525 when others then
526 edw_log.put_line('Error:'|| 'When inserting non-active default value into '|| g_dimension_name ||' '||sqlcode ||' : '||sqlerrm);
527 logTime('Error of non-active default values insertions to dimension star table', l_log_timestamp);
528 raise;
529
530 end insert_non_active_values;
531
532 procedure insert_default_values(
533 p_seq_name in varchar2
534 ) as
535 l_defaultrows_inserted integer :=0;
536 l_value_stmt varchar2(32767) := NULL;
537 l_log_timestamp Date := NULL;
538
539 begin
540 setTimer(l_log_timestamp); -- for errors only
541 l_value_stmt:=
542 'INSERT INTO '|| g_dimension_name ||
543 '( L1_pk ,l1_pk_key,l1_name,l1_description' ||
544 ' ,l1_type,type_pk,type_name,all_name,all_pk,' ||
545 ' H102_pk ,H103_pk ,H104_pk ,H105_pk ,H106_pk ,H107_pk ,H108_pk ,' ||
546 ' H102_name ,H103_name ,H104_name ,H105_name ,H106_name ,H107_name ,H108_name ,' ||
547 ' H102_type ,H103_type ,H104_type ,H105_type ,H106_type ,H107_type ,H108_type ,' ||
548 ' H109_pk ,H110_pk ,H111_pk ,H112_pk ,H113_pk ,H114_pk ,H115_pk ,' ||
549 ' H109_name ,H110_name ,H111_name ,H112_name ,H113_name ,H114_name ,H115_name ,' ||
550 ' H109_type ,H110_type ,H111_type ,H112_type ,H113_type ,H114_type ,H115_type ,' ||
551 ' H202_pk ,H203_pk ,H204_pk ,H205_pk ,H206_pk ,H207_pk ,H208_pk ,' ||
552 ' H202_name ,H203_name ,H204_name ,H205_name ,H206_name ,H207_name ,H208_name ,' ||
553 ' H202_type ,H203_type ,H204_type ,H205_type ,H206_type ,H207_type ,H208_type ,' ||
554 ' H209_pk ,H210_pk ,H211_pk ,H212_pk ,H213_pk ,H214_pk ,H215_pk ,' ||
555 ' H209_name ,H210_name ,H211_name ,H212_name ,H213_name ,H214_name ,H215_name ,' ||
556 ' H209_type ,H210_type ,H211_type ,H212_type ,H213_type ,H214_type ,H215_type ,' ||
557 ' H302_pk ,H303_pk ,H304_pk ,H305_pk ,H306_pk ,H307_pk ,H308_pk ,' ||
558 ' H302_name ,H303_name ,H304_name ,H305_name ,H306_name ,H307_name ,H308_name ,' ||
559 ' H302_type ,H303_type ,H304_type ,H305_type ,H306_type ,H307_type ,H308_type ,' ||
560 ' H309_pk ,H310_pk ,H311_pk ,H312_pk ,H313_pk ,H314_pk ,H315_pk ,' ||
561 ' H309_name ,H310_name ,H311_name ,H312_name ,H313_name ,H314_name ,H315_name ,' ||
562 ' H309_type ,H310_type ,H311_type ,H312_type ,H313_type ,H314_type ,H315_type ,' ||
563 ' H402_pk ,H403_pk ,H404_pk ,H405_pk ,H406_pk ,H407_pk ,H408_pk ,' ||
564 ' H402_name ,H403_name ,H404_name ,H405_name ,H406_name ,H407_name ,H408_name ,' ||
565 ' H402_type ,H403_type ,H404_type ,H405_type ,H406_type ,H407_type ,H408_type ,' ||
566 ' H409_pk ,H410_pk ,H411_pk ,H412_pk ,H413_pk ,H414_pk ,H415_pk ,' ||
567 ' H409_name ,H410_name ,H411_name ,H412_name ,H413_name ,H414_name ,H415_name ,' ||
568 ' H409_type ,H410_type ,H411_type ,H412_type ,H413_type ,H414_type ,H415_type ,' ||
569 ' creation_date, last_update_date) ' ||
570 ' select dim.pk, decode(temp.l1_pk,null,'||p_seq_name||'.nextval ,temp.l1_pk_key), dim.name,dim.description,
571 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',''NA_EDW'',
572 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
573 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
574 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
575 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
576 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
577 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
578 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
579 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
580 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
581 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
582 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
583 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
584 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
585 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
586 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
587 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
588 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
592 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
589 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
590 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
591 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
593 ''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',''NA_EDW'',
594 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',
595 '''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''','''|| g_na_edw||''',sysdate, sysdate'
596 ||' from '|| g_global_temp_table
597 ||' temp, (select distinct child pk,child_name name,child_desc description from '
598 || g_vbh_temp_table_name||' where child is not null union '
599 || ' select distinct parent pk,parent_name name,parent_desc description from '
600 || g_vbh_temp_table_name||') dim where temp.l1_pk(+)=dim.pk';
601
602 VBHDEBUG(l_value_stmt);
603 execute immediate l_value_stmt;
604 l_defaultrows_inserted:= sql%rowcount;
605 VBHDEBUG('Default rows inserted: ' || l_defaultrows_inserted);
606 exception
607 when others then
608 edw_log.put_line('Error:'|| 'When inserting active default value into '|| g_dimension_name ||' '||sqlcode ||' : '||sqlerrm);
609 logTime('Error of active default values insertions to dimension star table', l_log_timestamp);
610 raise;
611 end insert_default_values;
612
613
614 procedure insert_pk_key_into_table as
615 l_cursor_id integer;
616 l_insert_stmt varchar2(3000);
617 l_rows_inserted integer :=0;
618 begin
619 l_insert_stmt :='insert into '|| g_global_temp_table|| ' (l1_pk,l1_pk_key,l1_name,l1_description) ' ||
620 ' select l1_pk, l1_pk_key, l1_name, l1_description from '||g_dimension_name ||
621 ' a where not exists ' ||
622 ' (select b.l1_pk from ' || g_global_temp_table || ' b where a.l1_pk = b.l1_pk)';
623 VBHDEBUG('Executing ' || l_insert_stmt);
624 l_cursor_id:=DBMS_SQL.open_cursor;
625 DBMS_SQL.parse(l_cursor_id,l_insert_stmt,DBMS_SQL.V7);
626 l_rows_inserted :=DBMS_SQL.EXECUTE(l_cursor_id);
627 DBMS_SQL.close_cursor(l_cursor_id);
628 -- issue commit safely, after the backup is done.
629 commit;
630 VBHDEBUG('Inserted ' || l_rows_inserted || ' rows');
631 exception
632 when no_data_found then
633 null;
634 when others then
635 raise;
636 end insert_pk_key_into_table;
637
638
639
640 -- ------------------------------------------------------------------------
641 -- Procedure: update_dimension
642 -- Description: This function updates the "EDW_GL_ACCTx_M" table if it
643 -- doesn't find either root setup error or diamond shape error.
644 -- When it needs to update a row whose "hx02_name" to "hx15_name" columns
645 -- are not all 'NA_EDW', there could be a dimond shape error or a root setup
646 -- error and it will not overwrite the original hierarchy.
647 -- To check the errors, if "p_level15" is the original root, there are
648 -- multiple paths to the root in this particular hierarchy and a dimond
649 -- shape error exists. If "p_level15" is not the same as the orginal root,
650 -- the are multiple roots specified for this hierarchy and a root setup
651 -- error exists.
652 ---------------------------------------------------------------------------
653 PROCEDURE update_dimension(
654 p_CursorID IN integer,
655 p_hierachy_no IN number,
656 p_pk IN edw_gl_acct1_m.l1_pk%TYPE,
657 p_type IN edw_gl_acct1_m.l1_type%TYPE,
658 p_level2 IN edw_gl_acct1_m.l1_pk%TYPE,
659 p_level2_name IN edw_gl_acct1_m.l1_name%TYPE,
660 p_level2_type IN edw_gl_acct1_m.l1_type%TYPE,
661 p_level2_desc IN edw_gl_acct1_m.l1_description%TYPE,
662 p_level3 IN edw_gl_acct1_m.l1_pk%TYPE,
663 p_level3_name IN edw_gl_acct1_m.l1_name%TYPE,
664 p_level3_type IN edw_gl_acct1_m.l1_type%TYPE,
665 p_level3_desc IN edw_gl_acct1_m.l1_description%TYPE,
666 p_level4 IN edw_gl_acct1_m.l1_pk%TYPE,
667 p_level4_name IN edw_gl_acct1_m.l1_name%TYPE,
668 p_level4_type IN edw_gl_acct1_m.l1_type%TYPE,
669 p_level4_desc IN edw_gl_acct1_m.l1_description%TYPE,
670 p_level5 IN edw_gl_acct1_m.l1_pk%TYPE,
671 p_level5_name IN edw_gl_acct1_m.l1_name%TYPE,
672 p_level5_type IN edw_gl_acct1_m.l1_type%TYPE,
673 p_level5_desc IN edw_gl_acct1_m.l1_description%TYPE,
674 p_level6 IN edw_gl_acct1_m.l1_pk%TYPE,
675 p_level6_name IN edw_gl_acct1_m.l1_name%TYPE,
676 p_level6_type IN edw_gl_acct1_m.l1_type%TYPE,
677 p_level6_desc IN edw_gl_acct1_m.l1_description%TYPE,
678 p_level7 IN edw_gl_acct1_m.l1_pk%TYPE,
679 p_level7_name IN edw_gl_acct1_m.l1_name%TYPE,
680 p_level7_type IN edw_gl_acct1_m.l1_type%TYPE,
681 p_level7_desc IN edw_gl_acct1_m.l1_description%TYPE,
682 p_level8 IN edw_gl_acct1_m.l1_pk%TYPE,
683 p_level8_name IN edw_gl_acct1_m.l1_name%TYPE,
684 p_level8_type IN edw_gl_acct1_m.l1_type%TYPE,
685 p_level8_desc IN edw_gl_acct1_m.l1_description%TYPE,
689 p_level9_desc IN edw_gl_acct1_m.l1_description%TYPE,
686 p_level9 IN edw_gl_acct1_m.l1_pk%TYPE,
687 p_level9_name IN edw_gl_acct1_m.l1_name%TYPE,
688 p_level9_type IN edw_gl_acct1_m.l1_type%TYPE,
690 p_level10 IN edw_gl_acct1_m.l1_pk%TYPE,
691 p_level10_name IN edw_gl_acct1_m.l1_name%TYPE,
692 p_level10_type IN edw_gl_acct1_m.l1_type%TYPE,
693 p_level10_desc IN edw_gl_acct1_m.l1_description%TYPE,
694 p_level11 IN edw_gl_acct1_m.l1_pk%TYPE,
695 p_level11_name IN edw_gl_acct1_m.l1_name%TYPE,
696 p_level11_type IN edw_gl_acct1_m.l1_type%TYPE,
697 p_level11_desc IN edw_gl_acct1_m.l1_description%TYPE,
698 p_level12 IN edw_gl_acct1_m.l1_pk%TYPE,
699 p_level12_name IN edw_gl_acct1_m.l1_name%TYPE,
700 p_level12_type IN edw_gl_acct1_m.l1_type%TYPE,
701 p_level12_desc IN edw_gl_acct1_m.l1_description%TYPE,
702 p_level13 IN edw_gl_acct1_m.l1_pk%TYPE,
703 p_level13_name IN edw_gl_acct1_m.l1_name%TYPE,
704 p_level13_type IN edw_gl_acct1_m.l1_type%TYPE,
705 p_level13_desc IN edw_gl_acct1_m.l1_description%TYPE,
706 p_level14 IN edw_gl_acct1_m.l1_pk%TYPE,
707 p_level14_name IN edw_gl_acct1_m.l1_name%TYPE,
708 p_level14_type IN edw_gl_acct1_m.l1_type%TYPE,
709 p_level14_desc IN edw_gl_acct1_m.l1_description%TYPE,
710 p_level15 IN edw_gl_acct1_m.l1_pk%TYPE,
711 p_level15_name IN edw_gl_acct1_m.l1_name%TYPE,
712 p_level15_type IN edw_gl_acct1_m.l1_type%TYPE,
713 p_level15_desc IN edw_gl_acct1_m.l1_description%TYPE,
714 p_all_name in edw_gl_acct1_m.all_name%TYPE,
715 p_RowsUpdated IN OUT NOCOPY INTEGER
716 ) AS
717
718 l_CursorID INTEGER;
719 l_UpdateStmt VARCHAR2(4000);
720 l_root_name varchar2(240);
721 l_dimond_error EXCEPTION;
722 l_root_setup_error exception;
723 l_error_timestamp Date:= null;
724
725
726 type t_cur_check_error is ref cursor;
727 cur_check_error t_cur_check_error;
728 BEGIN
729
730 IF p_hierachy_no <= 4 THEN
731 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_type', p_type);
732 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level2' , p_level2);
733 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level2_name',p_level2_name);
734 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level2_type',p_level2_type);
735 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level2_desc',p_level2_desc);
736 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level3' , p_level3);
737 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level3_name',p_level3_name);
738 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level3_type',p_level3_type);
739 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level3_desc',p_level3_desc);
740 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level4' , p_level4);
741 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level4_name',p_level4_name);
742 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level4_type',p_level4_type);
743 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level4_desc',p_level4_desc);
744 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level5' , p_level5);
745 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level5_name',p_level5_name);
746 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level5_type',p_level5_type);
747 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level5_desc',p_level5_desc);
748 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level6' , p_level6);
749 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level6_name',p_level6_name);
750 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level6_type',p_level6_type);
751 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level6_desc',p_level6_desc);
752 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level7' , p_level7);
753 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level7_name',p_level7_name);
754 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level7_type',p_level7_type);
755 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level7_desc',p_level7_desc);
756 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level8' , p_level8);
757 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level8_name',p_level8_name);
758 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level8_type',p_level8_type);
759 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level8_desc',p_level8_desc);
760 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level9' , p_level9);
761 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level9_name',p_level9_name);
762 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level9_type',p_level9_type);
763 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level9_desc',p_level9_desc);
764 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level10' , p_level10);
765 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level10_name',p_level10_name);
766 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level10_type',p_level10_type);
767 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level10_desc',p_level10_desc);
768 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level11' , p_level11);
769 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level11_name',p_level11_name);
770 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level11_type',p_level11_type);
771 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level11_desc',p_level11_desc);
772 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level12' , p_level12);
773 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level12_name',p_level12_name);
774 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level12_type',p_level12_type);
775 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level12_desc',p_level12_desc);
776 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level13' , p_level13);
777 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level13_name',p_level13_name);
778 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level13_type',p_level13_type);
779 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level13_desc',p_level13_desc);
780 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level14' , p_level14);
784 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level15' , p_level15);
781 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level14_name',p_level14_name);
782 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level14_type',p_level14_type);
783 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level14_desc',p_level14_desc);
785 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level15_name',p_level15_name);
786 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level15_type',p_level15_type);
787 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_level15_desc',p_level15_desc);
788 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_type_pk',p_type);
789 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_type_name',p_type);
790 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_type_desc',p_type);
791 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_all_pk', p_all_name );
792 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_all_name',p_all_name);
793 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_all_desc',p_all_name);
794 DBMS_SQL.BIND_VARIABLE(p_CursorID, ':b_primarykey' ,p_pk);
795
796 p_RowsUpdated := DBMS_SQL.EXECUTE(p_CursorID);
797
798
799 IF p_RowsUpdated=0 THEN
800 begin
801 setTimer(l_error_timestamp);
802 open cur_check_error for
803 'SELECT h'||p_hierachy_no||'15_pk
804 FROM '|| g_dimension_name||'
805 WHERE l1_pk= :s' using p_pk;
806 FETCH cur_check_error into l_root_name;
807
808 if cur_check_error%NOTFOUND then
809 edw_log.put_line(
810 'Warning : '||'Can not find '|| p_pk||' in dimension table.');
811 g_completion_status:=1;
812 logCumulatedTime( l_error_timestamp, g_err_cum_timestamp);
813 return;
814 else
815 if l_root_name=p_level15 then
816 raise l_dimond_error;
817 else raise l_root_setup_error;
818 end if;
819 end if;
820 close cur_check_error;
821
822 exception
823 when l_dimond_error then
824 g_diamond_output:=g_diamond_output+1;
825 if g_diamond_output < g_err_smp_size then
826 edw_log.put_line(
827 'Warning : Dimond shape or duplicate rows.
828 '||p_pk||' rolls up to '
829 ||l_root_name||' twice in hierarchy '||p_hierachy_no||'. ');
830 end if;
831 g_completion_status:=1;
832 logCumulatedTime( l_error_timestamp, g_err_cum_timestamp);
833
834 when l_root_setup_error then
835 g_rootsetup_error:=g_rootsetup_error+1;
836 if g_rootsetup_error< g_err_smp_size then
837 edw_log.put_line('Warning : Root setup error for '
838 ||p_pk||':'||'.
839 '||p_pk||' rolls up to '
840 ||l_root_name||' and '||p_level15||' in hierarchy '
841 ||p_hierachy_no||'.
842 '||l_root_name||' is collected.');
843 end if;
844 g_completion_status:=1;
845 logCumulatedTime( l_error_timestamp, g_err_cum_timestamp);
846
847 end;
848 END IF; --for p_RowsUpdated=0
849 END IF; --for p_hierachy_no <= 4 THEN
850
851 EXCEPTION
852 WHEN OTHERS THEN
853 edw_log.put_line( 'Error: When updating the dimension table. '
854 ||sqlcode||' : '||sqlerrm);
855 raise;
856 END update_dimension;
857
858 procedure check_stand_alone_value as
859 l_select_stmt varchar2(300);
860 l_cursor_id number;
861 l_rows_fetched number;
862 l_name varchar2(80);
863 l_pk varchar2(240);
864
865 begin
866 VBHDEBUG('Checking stand alone values...');
867 l_select_stmt:= 'select l1_pk,l1_name from '||g_dimension_name||
868 ' where h115_pk = ''NA_EDW'' and h215_pk = ''NA_EDW''
869 and h315_pk = ''NA_EDW'' and h415_pk = ''NA_EDW''
870 order by l1_name';
871 VBHDEBUG('Executing : ' || l_select_stmt);
872 l_cursor_id:=DBMS_SQL.OPEN_CURSOR;
873 DBMS_SQL.parse(l_cursor_id,l_select_stmt,DBMS_SQL.V7);
874 DBMS_SQL.define_column(l_cursor_id,1,l_pk,240);
875 DBMS_SQL.define_column(l_cursor_id,2,l_name,80);
876 g_standalone:=DBMS_SQL.execute(l_cursor_id);
877 g_standalone := 0;
878 loop
879 if DBMS_SQL.fetch_rows(l_cursor_id)=0 then
880 exit;
881 end if;
882 if g_standalone<= g_err_smp_size then
883 DBMS_SQL.column_value (l_cursor_id,1,l_pk);
884 DBMS_SQL.column_value (l_cursor_id,2,l_name);
885 VBHDEBUG('Stand alone value : '||'pk:'||l_pk||' name:'||l_name);
886 end if;
887 g_standalone := g_standalone + 1;
888 end loop;
889 DBMS_SQL.close_cursor(l_cursor_id);
890 exception
891 when others then
892 edw_log.put_line( 'Error : When checking stand alone node. '||sqlcode||' : '||sqlerrm);
893 raise;
894 end;
895
896
897 procedure check_root_set_up as
898 l_set_of_books EDW_SET_OF_BOOKS%ROWTYPE;
899 l_status_flag varchar2(1):='Y';
900
901 cursor cur_set_of_books is
902 select *
903 from edw_set_of_books
904 where edw_set_of_books_id not in (
905 select edw_set_of_books_id
906 from edw_vbh_roots)
907 and edw_set_of_books_id not in (
908 select edw_set_of_books_id
909 from edw_equi_set_of_books)
910 and edw_set_of_books_id not in (
911 select child_edw_set_of_books_id
912 from edw_cons_set_of_books) ;
913 begin
917 exit when cur_set_of_books%NOTFOUND;
914 open cur_set_of_books;
915 loop
916 fetch cur_set_of_books into l_set_of_books;
918
919 l_status_flag:='N';
920 VBHDEBUG('Warning: No root specified for '
921 ||l_set_of_books.instance||' : '||l_set_of_books.set_of_books_name);
922 end loop;
923 close cur_set_of_books;
924 if(l_status_flag='N') then
925 g_completion_status:=1;
926 end if;
927 exception
928 when others then
929 edw_log.put_line( 'Error : When checking stand alone node. '||sqlcode||' : '||sqlerrm);
930 raise;
931 end;
932
933 PROCEDURE CLOSE_UPDATE( p_CursorID IN OUT NOCOPY INTEGER) IS
934 BEGIN
935 DBMS_SQL.CLOSE_CURSOR(p_CursorID);
936 VBHDEBUG('Closed the update statement cursor');
937 VBHDEBUG(' ');
938 END;
939
940
941 FUNCTION PARSE_UPDATE(p_hierachy_no integer ) return integer
942 IS
943 l_CursorID INTEGER;
944 l_UpdateStmt varchar2(4000);
945 BEGIN
946 l_CursorID := DBMS_SQL.OPEN_CURSOR;
947 l_UpdateStmt :=
948 'UPDATE '|| g_dimension_name||'
949 SET LAST_UPDATE_DATE=sysdate,
950 l1_type=:b_type,'
951 ||' h'||p_hierachy_no||'02_pk= :b_level2,'
952 ||' h'||p_hierachy_no||'02_name= :b_level2_name,'
953 ||' h'||p_hierachy_no||'02_type= :b_level2_type,'
954 ||' h'||p_hierachy_no||'02_description = :b_level2_desc,'
955 ||' h'||p_hierachy_no||'03_pk= :b_level3,'
956 ||' h'||p_hierachy_no||'03_name= :b_level3_name,'
957 ||' h'||p_hierachy_no||'03_type= :b_level3_type,'
958 ||' h'||p_hierachy_no||'03_description = :b_level3_desc,'
959 ||' h'||p_hierachy_no||'04_pk= :b_level4,'
960 ||' h'||p_hierachy_no||'04_name= :b_level4_name,'
961 ||' h'||p_hierachy_no||'04_type= :b_level4_type,'
962 ||' h'||p_hierachy_no||'04_description = :b_level4_desc,'
963 ||' h'||p_hierachy_no||'05_pk= :b_level5,'
964 ||' h'||p_hierachy_no||'05_name= :b_level5_name,'
965 ||' h'||p_hierachy_no||'05_type= :b_level5_type,'
966 ||' h'||p_hierachy_no||'05_description = :b_level5_desc,'
967 ||' h'||p_hierachy_no||'06_pk= :b_level6,'
968 ||' h'||p_hierachy_no||'06_name= :b_level6_name,'
969 ||' h'||p_hierachy_no||'06_type= :b_level6_type,'
970 ||' h'||p_hierachy_no||'06_description = :b_level6_desc,'
971 ||' h'||p_hierachy_no||'07_pk= :b_level7,'
972 ||' h'||p_hierachy_no||'07_name= :b_level7_name,'
973 ||' h'||p_hierachy_no||'07_type= :b_level7_type,'
974 ||' h'||p_hierachy_no||'07_description = :b_level7_desc,'
975 ||' h'||p_hierachy_no||'08_pk= :b_level8,'
976 ||' h'||p_hierachy_no||'08_name= :b_level8_name,'
977 ||' h'||p_hierachy_no||'08_type= :b_level8_type,'
978 ||' h'||p_hierachy_no||'08_description = :b_level8_desc,'
979 ||' h'||p_hierachy_no||'09_pk= :b_level9,'
980 ||' h'||p_hierachy_no||'09_name= :b_level9_name,'
981 ||' h'||p_hierachy_no||'09_type= :b_level9_type,'
982 ||' h'||p_hierachy_no||'09_description = :b_level9_desc,'
983 ||' h'||p_hierachy_no||'10_pk= :b_level10,'
984 ||' h'||p_hierachy_no||'10_name= :b_level10_name,'
985 ||' h'||p_hierachy_no||'10_type= :b_level10_type,'
986 ||' h'||p_hierachy_no||'10_description= :b_level10_desc,'
987 ||' h'||p_hierachy_no||'11_pk= :b_level11,'
988 ||' h'||p_hierachy_no||'11_name= :b_level11_name,'
989 ||' h'||p_hierachy_no||'11_type= :b_level11_type,'
990 ||' h'||p_hierachy_no||'11_description= :b_level11_desc,'
991 ||' h'||p_hierachy_no||'12_pk= :b_level12,'
992 ||' h'||p_hierachy_no||'12_name= :b_level12_name,'
993 ||' h'||p_hierachy_no||'12_type= :b_level12_type,'
994 ||' h'||p_hierachy_no||'12_description= :b_level12_desc,'
995 ||' h'||p_hierachy_no||'13_pk= :b_level13,'
996 ||' h'||p_hierachy_no||'13_name= :b_level13_name,'
997 ||' h'||p_hierachy_no||'13_type= :b_level13_type,'
998 ||' h'||p_hierachy_no||'13_description= :b_level13_desc,'
999 ||' h'||p_hierachy_no||'14_pk= :b_level14,'
1000 ||' h'||p_hierachy_no||'14_name= :b_level14_name,'
1001 ||' h'||p_hierachy_no||'14_type= :b_level14_type,'
1002 ||' h'||p_hierachy_no||'14_description= :b_level14_desc,'
1003 ||' h'||p_hierachy_no||'15_pk= :b_level15,'
1004 ||' h'||p_hierachy_no||'15_name= :b_level15_name,'
1005 ||' h'||p_hierachy_no||'15_type= :b_level15_type,'
1006 ||' h'||p_hierachy_no||'15_description= :b_level15_desc,'
1007 ||' type_pk =:b_type_pk,'
1008 ||' type_name =:b_type_name,'
1009 ||' type_description =:b_type_desc,'
1010 ||' all_pk =:b_all_pk,'
1011 ||' all_name =:b_all_name,'
1012 ||' all_description =:b_all_desc'
1013 ||' WHERE l1_pk= :b_primarykey AND h'||p_hierachy_no||'02_pk = ''NA_EDW''';
1014
1015 DBMS_SQL.PARSE(l_CursorID,l_UpdateStmt,DBMS_SQL.V7);
1016 VBHDEBUG('Parsed the update statement for hierarchy ' || p_hierachy_no );
1017 return l_CursorID;
1018 END PARSE_UPDATE;
1019
1020 FUNCTION GET_VBH_ROOT ( p_root_sob_id integer, p_root varchar ) RETURN VARCHAR IS
1021 l_root_value varchar2(200);
1022 BEGIN
1023 SELECT p_root ||'-'||set_of_books_id||'-'||instance
1024 INTO l_root_value
1025 FROM edw_set_of_books
1026 WHERE edw_set_of_books_id = p_root_sob_id;
1027 VBHDEBUG('l_root_value: '||l_root_value);
1028
1029 return l_root_value;
1030
1031 exception
1035 raise;
1032 when others then
1033 edw_log.put_line( 'Error : When looking up root set_of_books_id. '
1034 ||sqlcode||' : '||sqlerrm);
1036 END GET_VBH_ROOT;
1037
1038 FUNCTION PROCESS_ROOT (p_root_value varchar, p_hieno integer, p_curid in integer)
1039 RETURN INTEGER IS
1040 TYPE t_value_desc_pair_rec IS RECORD (
1041 value edw_gl_acct1_m.l1_pk%TYPE,
1042 name edw_gl_acct1_m.l1_name%TYPE,
1043 type edw_gl_acct1_m.l1_type%TYPE,
1044 description edw_gl_acct1_m.l1_description%TYPE);
1045
1046 l_descLookup_timestamp Date:=Null;
1047
1048 TYPE t_cur_description IS ref cursor;
1049 cur_description t_cur_description;
1050 l_rowsUpdated integer := 0;
1051 BEGIN
1052 --store the value of current root
1053 g_vbh_level_table(15).value :=p_root_value;
1054
1055 --look up the description for current root
1056 setTimer(l_descLookup_timestamp);
1057 OPEN cur_description FOR
1058 'select parent_name,parent_type,parent_desc
1059 from '|| g_vbh_temp_table_name||'
1060 WHERE parent=:s
1061 union all
1062 select child_name,child_type,child_desc
1063 from '|| g_vbh_temp_table_name||'
1064 WHERE child=:s ' using p_root_value,p_root_value;
1065
1066 FETCH cur_description INTO
1067 g_vbh_level_table(15).name,g_vbh_level_table(15).type, g_vbh_level_table(15).description;
1068 if cur_description%NOTFOUND then
1069 VBHDEBUG( 'Warning : Can not find '|| p_root_value||' in '
1070 || g_vbh_temp_table_name);
1071 end if;
1072 close cur_description;
1073
1074 if(g_vbh_level_table(15).type is null) THEN
1075 g_vbh_level_table(15).type:= g_not_classified_type;
1076 end if;
1077
1078 --create a dummy node for the root;
1079 update_dimension(
1080 p_curid,
1081 p_hieno,
1082 g_vbh_level_table(15).value,
1083 g_vbh_level_table(15).type,
1084 g_vbh_level_table(15).value,
1085 g_vbh_level_table(15).name,
1086 g_vbh_level_table(15).type,
1087 g_vbh_level_table(15).description,
1088 g_vbh_level_table(15).value,
1089 g_vbh_level_table(15).name,
1090 g_vbh_level_table(15).type,
1091 g_vbh_level_table(15).description,
1092 g_vbh_level_table(15).value,
1093 g_vbh_level_table(15).name,
1094 g_vbh_level_table(15).type,
1095 g_vbh_level_table(15).description,
1096 g_vbh_level_table(15).value,
1097 g_vbh_level_table(15).name,
1098 g_vbh_level_table(15).type,
1099 g_vbh_level_table(15).description,
1100 g_vbh_level_table(15).value,
1101 g_vbh_level_table(15).name,
1102 g_vbh_level_table(15).type,
1103 g_vbh_level_table(15).description,
1104 g_vbh_level_table(15).value,
1105 g_vbh_level_table(15).name,
1106 g_vbh_level_table(15).type,
1107 g_vbh_level_table(15).description,
1108 g_vbh_level_table(15).value,
1109 g_vbh_level_table(15).name,
1110 g_vbh_level_table(15).type,
1111 g_vbh_level_table(15).description,
1112 g_vbh_level_table(15).value,
1113 g_vbh_level_table(15).name,
1114 g_vbh_level_table(15).type,
1115 g_vbh_level_table(15).description,
1116 g_vbh_level_table(15).value,
1117 g_vbh_level_table(15).name,
1118 g_vbh_level_table(15).type,
1119 g_vbh_level_table(15).description,
1120 g_vbh_level_table(15).value,
1121 g_vbh_level_table(15).name,
1122 g_vbh_level_table(15).type,
1123 g_vbh_level_table(15).description,
1124 g_vbh_level_table(15).value,
1125 g_vbh_level_table(15).name,
1126 g_vbh_level_table(15).type,
1127 g_vbh_level_table(15).description,
1128 g_vbh_level_table(15).value,
1129 g_vbh_level_table(15).name,
1130 g_vbh_level_table(15).type,
1131 g_vbh_level_table(15).description,
1132 g_vbh_level_table(15).value,
1133 g_vbh_level_table(15).name,
1134 g_vbh_level_table(15).type,
1135 g_vbh_level_table(15).description,
1136 g_vbh_level_table(15).value,
1137 g_vbh_level_table(15).name,
1138 g_vbh_level_table(15).type,
1139 g_vbh_level_table(15).description,
1140 'ALL' ,
1141 l_rowsUpdated);
1142
1143 return l_rowsUpdated;
1144 END PROCESS_ROOT;
1145
1146
1147
1148 FUNCTION PROCESS_ROOT_HIR(p_root_value varchar2, p_hie_no integer , p_curid in integer)
1149 RETURN integer
1150 IS
1151 TYPE t_vbh_temp_rec IS RECORD(
1152 parent edw_vbh_temp1.parent%TYPE,
1153 parent_name edw_vbh_temp1.parent_name%TYPE,
1154 parent_type edw_vbh_temp1.parent_type%TYPE,
1155 parent_desc edw_vbh_temp1.parent_desc%TYPE,
1156 child edw_vbh_temp1.child%TYPE,
1157 child_name edw_vbh_temp1.child_name%TYPE,
1158 child_type edw_vbh_temp1.child_type%TYPE,
1162
1159 child_desc edw_vbh_temp1.child_desc%TYPE,
1160 total_level NUMBER);
1161 TYPE t_cur_vbh_temp_ref IS REF CURSOR;
1163 l_TreeBiuld_timestamp Date:=Null;
1164 cur_vbh_temp t_cur_vbh_temp_ref;
1165 l_vbh_temp t_vbh_temp_rec;
1166 l_rowsUpdated integer := 0;
1167 l_updates_for_root integer := 0;
1168 BEGIN
1169
1170 setTimer(l_TreeBiuld_timestamp);
1171 OPEN cur_vbh_temp FOR
1172 'SELECT parent,parent_name,parent_type,parent_desc,
1173 child,child_name,child_type,child_desc, level+1 total_level
1174 FROM '|| g_vbh_temp_table_name||'
1175 where child is not null
1176 START WITH parent =:s
1177 CONNECT BY parent=PRIOR child' using p_root_value;
1178 LOOP
1179 FETCH cur_vbh_temp INTO l_vbh_temp;
1180 EXIT WHEN cur_vbh_temp%NOTFOUND;
1181
1182
1183 -- WATCH OUT!!
1184 -- This FOR LOOP won't process levels deeper then 15
1185 FOR l_table_counter IN 1..(15-l_vbh_temp.total_level+1) LOOP
1186 g_vbh_level_table(l_table_counter).value:=l_vbh_temp.child;
1187 g_vbh_level_table(l_table_counter).name:=l_vbh_temp.child_name;
1188
1189 if l_vbh_temp.child_type is null then
1190 g_vbh_level_table(l_table_counter).type:= g_not_classified_type;
1191 else
1192 g_vbh_level_table(l_table_counter).type:=l_vbh_temp.child_type;
1193 end if;
1194
1195 g_vbh_level_table(l_table_counter).description:=l_vbh_temp.child_desc;
1196
1197 END LOOP;
1198
1199
1200 -- added to implement the level collapsing when more then 15 levels are
1201 -- pulled into the EDW_VBH_TEMP# tables.
1202 -- all the values in levels deeper then 15th, will be merged in to the
1203 -- 15th( lowest level).
1204
1205 IF l_vbh_temp.total_level > 15 then
1206 g_vbh_level_table(1).value:=l_vbh_temp.child;
1207 g_vbh_level_table(1).name:=l_vbh_temp.child_name;
1208
1209 if l_vbh_temp.child_type is null then
1210 g_vbh_level_table(1).type:= g_not_classified_type;
1211 else
1212 g_vbh_level_table(1).type:=l_vbh_temp.child_type;
1213 end if;
1214 g_vbh_level_table(1).description:=l_vbh_temp.child_desc;
1215 END IF;
1216
1217 update_dimension(
1218 p_curid,
1219 p_hie_no,
1220 g_vbh_level_table(1).value,
1221 g_vbh_level_table(1).type,
1222 g_vbh_level_table(2).value,
1223 g_vbh_level_table(2).name,
1224 g_vbh_level_table(2).type,
1225 g_vbh_level_table(2).description,
1226 g_vbh_level_table(3).value,
1227 g_vbh_level_table(3).name,
1228 g_vbh_level_table(3).type,
1229 g_vbh_level_table(3).description,
1230 g_vbh_level_table(4).value,
1231 g_vbh_level_table(4).name,
1232 g_vbh_level_table(4).type,
1233 g_vbh_level_table(4).description,
1234 g_vbh_level_table(5).value,
1235 g_vbh_level_table(5).name,
1236 g_vbh_level_table(5).type,
1237 g_vbh_level_table(5).description,
1238 g_vbh_level_table(6).value,
1239 g_vbh_level_table(6).name,
1240 g_vbh_level_table(6).type,
1241 g_vbh_level_table(6).description,
1242 g_vbh_level_table(7).value,
1243 g_vbh_level_table(7).name,
1244 g_vbh_level_table(7).type,
1245 g_vbh_level_table(7).description,
1246 g_vbh_level_table(8).value,
1247 g_vbh_level_table(8).name,
1248 g_vbh_level_table(8).type,
1249 g_vbh_level_table(8).description,
1250 g_vbh_level_table(9).value,
1251 g_vbh_level_table(9).name,
1252 g_vbh_level_table(9).type,
1253 g_vbh_level_table(9).description,
1254 g_vbh_level_table(10).value,
1255 g_vbh_level_table(10).name,
1256 g_vbh_level_table(10).type,
1257 g_vbh_level_table(10).description,
1258 g_vbh_level_table(11).value,
1259 g_vbh_level_table(11).name,
1260 g_vbh_level_table(11).type,
1261 g_vbh_level_table(11).description,
1262 g_vbh_level_table(12).value,
1263 g_vbh_level_table(12).name,
1264 g_vbh_level_table(12).type,
1265 g_vbh_level_table(12).description,
1266 g_vbh_level_table(13).value,
1267 g_vbh_level_table(13).name,
1268 g_vbh_level_table(13).type,
1269 g_vbh_level_table(13).description,
1270 g_vbh_level_table(14).value,
1271 g_vbh_level_table(14).name,
1272 g_vbh_level_table(14).type,
1273 g_vbh_level_table(14).description,
1274 g_vbh_level_table(15).value,
1275 g_vbh_level_table(15).name,
1276 g_vbh_level_table(15).type,
1277 g_vbh_level_table(15).description,
1278 'ALL',
1279 l_rowsUpdated);
1280
1281 l_updates_for_root := l_updates_for_root + l_rowsUpdated;
1282
1283 END LOOP;
1284 CLOSE cur_vbh_temp;
1288 END PROCESS_ROOT_HIR;
1285 logTime('buliding tree from root '|| p_root_value, l_TreeBiuld_timestamp);
1286 VBHDEBUG( l_updates_for_root || ' rows updated for root ' || p_root_value);
1287 return l_updates_for_root;
1289
1290
1291 Procedure Collect(Errbuf out NOCOPY Varchar2,
1292 Retcode out NOCOPY Varchar2,
1293 p_dimension_name in varchar2) IS
1294
1295 -- -------------------------------------------
1296 -- Put any additional developer variables here
1297 -- -------------------------------------------
1298 p_dimension_no number;
1299 l_rows_inserted INTEGER :=0;
1300 l_table_counter INTEGER;
1301 l_hierachy_no NUMBER;
1302 l_rowsUpdated INTEGER;
1303 l_root_value edw_vbh_temp1.parent%TYPE;
1304 l_vbh_value edw_gl_acct1_m.l1_pk%TYPE;
1305 l_vbh_name edw_gl_acct1_m.l1_name%TYPE;
1306 l_vbh_desc edw_gl_acct1_m.l1_description%TYPE;
1307 l_pk_key edw_gl_acct1_m.l1_pk_key%TYPE;
1308 l_temp_table_name varchar2(30);
1309 l_value_stmt varchar2(1000);
1310 l_root_counter Integer :=0;
1311 l_check_sob_id number;
1312 l_program_status boolean:=true;
1313 l_exe_status boolean;
1314 l_status boolean;
1315 l_dir varchar2(400);
1316 l_seq_name varchar2(30);
1317 l_element_id number:=0;
1318 l_progress_seq_id number:=0;
1319 l_progress_status boolean;
1320
1321
1322 TYPE t_value_desc_pair_rec IS RECORD (
1323 value edw_gl_acct1_m.l1_pk%TYPE,
1324 name edw_gl_acct1_m.l1_name%TYPE,
1325 type edw_gl_acct1_m.l1_type%TYPE,
1326 description edw_gl_acct1_m.l1_description%TYPE);
1327 l_root_value_desc_pair t_value_desc_pair_rec;
1328
1329 Type t_vbh_root_table is table of edw_vbh_roots.root_value1%type
1330 index by binary_integer;
1331 l_vbh_root_table t_vbh_root_table;
1332
1333
1334 l_vbh_root_sob_id edw_vbh_roots.edw_set_of_books_id%TYPE ;
1335
1336 TYPE t_cur_vbh_root_ref IS REF CURSOR;
1337 cur_vbh_roots t_cur_vbh_root_ref;
1338
1339 TYPE t_cur_vbh_all_value IS REF CURSOR;
1340 cur_vbh_all_value t_cur_vbh_all_value;
1341
1342 l_start_date date:= null;
1343 l_log_timestamp Date:=Null;
1344 l_roots_timestamp Date:=Null;
1345 l_hie_timstamp Date:=Null;
1346 l_descLookup_timestamp Date:=Null;
1347 --l_dummyroot_timestamp Date:=Null;
1348 l_CursorID INTEGER;
1349 l_updates_for_root INTEGER;
1350
1351 BEGIN
1352 -- ian debugging
1353 -- DBMS_SESSION.set_sql_trace(true);
1354
1355 INITDEBUG;
1356 -- initialize global variable
1357 g_diamond_output :=0;
1358 g_standalone :=0;
1359 g_rootsetup_error:=0;
1360 g_err_cum_timestamp := 0;
1361 g_rows_updated := 0;
1362 g_err_smp_size := 20;
1363 g_rows_inserted := 0;
1364
1365 -- select sysdate into l_start_date from dual;
1366 setTimer(l_start_date);
1367
1368
1369 Errbuf :=NULL;
1370 Retcode:=0;
1371
1372 g_conc_program_id :=FND_GLOBAL.conc_request_id;
1373 g_conc_program_name:='EDWFVBCB';
1374
1375
1376 select dim_name into g_dimension_name
1377 from edw_dimensions_md_v
1378 where dim_long_name = p_dimension_name;
1379 p_dimension_no := substr(g_dimension_name, 12 , instr(g_dimension_name, '_M') - 12 );
1380
1381 g_vbh_temp_table_name:='EDW_VBH_TEMP'||p_dimension_no;
1382 g_global_temp_table:=g_dimension_name||'_temp';
1383 l_seq_name:='EDW_GL_ACCT'|| p_dimension_no||'_S';
1384 l_exe_status :=true;
1385 g_completion_status:=0;
1386 l_element_id:=edw_owb_collection_util.GET_OBJECT_ID(g_dimension_name);
1387 edw_owb_collection_util.setup_conc_program_log(g_dimension_name);
1388
1389
1390 -- FND MESSAGE LOOKUP AREA
1391 FND_MESSAGE.SET_NAME('BIS', 'EDW_DIMENSION_LEVEL_TYPE');
1392 g_not_classified_type := FND_MESSAGE.GET;
1393 edw_log.put_line( 'Not Classfied Type lookup: ' || g_not_classified_type);
1394
1395 FND_MESSAGE.SET_NAME('BIS', 'EDW_UNASSIGNED');
1396 g_na_edw := FND_MESSAGE.GET;
1397 edw_log.put_line( 'NA_EDW lookup: ' || g_na_edw);
1398
1399
1400 FND_MESSAGE.SET_NAME('BIS', 'EDW_INVALID');
1401 g_na_err := FND_MESSAGE.GET;
1402 edw_log.put_line( 'NA_ERR lookup: ' || g_na_err);
1403
1404
1405 FND_MESSAGE.SET_NAME('BIS', 'EDW_ALL');
1406 g_all := FND_MESSAGE.GET;
1407 edw_log.put_line( 'EDW_ALL lookup: ' || g_all);
1408
1409
1410 select edw_load_s.nextval
1411 into l_progress_seq_id
1412 from dual;
1413
1414
1415 edw_log.put_line( 'VBH Loading program for '|| p_dimension_name||' '||
1416 to_char(sysdate));
1417
1418 edw_log.put_line('Dimension number '|| p_dimension_no);
1419 edw_log.put_line('Dimension physical name : '|| g_dimension_name);
1420 edw_log.put_line('Error Sample Display Size : '|| g_err_smp_size);
1421
1422 edw_log.put_line('Collecting data...');
1423 if p_dimension_no<1 or p_dimension_no>10 then
1424 edw_log.put_line('Error : Invalid dimension number '
1425 ||p_dimension_no);
1426 g_completion_status:=2;
1427 l_exe_status:=false;
1428 l_program_status:=
1432
1429 fnd_concurrent.set_completion_status('ERROR',NULL);
1430 return;
1431 end if;
1433
1434 setTimer(l_log_timestamp);
1435 check_root_set_up;
1436 logTime('checking root setup', l_log_timestamp);
1437
1438
1439 setTimer(l_log_timestamp);
1440 insert_pk_key_into_table;
1441 logTime('inserting keys to global temp', l_log_timestamp);
1442
1443 setTimer(l_log_timestamp);
1444 clean_up_dimension_table;
1445 logTime('cleaning up dimension star table', l_log_timestamp);
1446
1447 --update edw_vbh_temp table to include type info
1448 edw_owb_collection_util.INSERT_INTO_LOAD_PROGRESS(
1449 l_progress_seq_id,
1450 g_dimension_name,
1451 l_element_id,
1452 'Updating Account Type Information',
1453 sysdate,
1454 null,
1455 null,
1456 null,
1457 100,
1458 'I',
1459 l_element_id);
1460
1461 setTimer(l_log_timestamp);
1462 update_class;
1463 logTime('updating class', l_log_timestamp);
1464
1465 edw_owb_collection_util.INSERT_INTO_LOAD_PROGRESS(
1466 l_progress_seq_id,
1467 g_dimension_name,
1468 l_element_id,
1469 null,
1470 null,
1471 sysdate,
1472 null,
1473 null,
1474 100,
1475 'U',
1476 l_element_id);
1477
1478 edw_owb_collection_util.INSERT_INTO_LOAD_PROGRESS(
1479 l_progress_seq_id,
1480 g_dimension_name,
1481 l_element_id,
1482 'Insert Lowest Level into Dimension Table',
1483 sysdate,
1484 null,
1485 null,
1486 null,
1487 110,
1488 'I',
1489 l_element_id);
1490
1491 --insert default value to dimension tables
1492 setTimer(l_log_timestamp);
1493 insert_default_values(l_seq_name);
1494 logTime('Inserting default values to dimension star table for active values', l_log_timestamp);
1495 VBHDEBUG(' ');
1496
1497 setTimer(l_log_timestamp);
1498 insert_default_value('NA_EDW',0,g_na_edw,g_na_edw);
1499 logTime('Inserting default value for NA_EDW ', l_log_timestamp);
1500 VBHDEBUG(' ');
1501
1502 setTimer(l_log_timestamp);
1503 insert_default_value('NA_ERR',-1,g_na_err,g_na_err);
1504 logTime('Inserting default value for NA_ERR ', l_log_timestamp);
1505 VBHDEBUG(' ');
1506
1507
1508 setTimer(l_log_timestamp);
1509 insert_non_active_values;
1510 logTime('Inserting default values into dimension star table for non-active nodes', l_log_timestamp);
1511 VBHDEBUG(' ');
1512
1513 -- add commit here to reduce the rollback segment space requirement a bit!
1514 COMMIT;
1515
1516 edw_owb_collection_util.INSERT_INTO_LOAD_PROGRESS(
1517 l_progress_seq_id,
1518 g_dimension_name,
1519 l_element_id,
1520 null,
1521 null,
1522 sysdate,
1523 null,
1524 null,
1525 110,
1526 'U',
1527 l_element_id);
1528
1529 setTimer(l_roots_timestamp);
1530 OPEN cur_vbh_roots for
1531 SELECT distinct e1.edw_set_of_books_id,e1.root_value1,e1.root_value2,
1532 e1.root_value3,e1.root_value4
1533 FROM edw_vbh_roots e1, edw_set_of_books e2
1534 WHERE e1.edw_set_of_books_id=e2.edw_set_of_books_id
1535 and e1.segment_name =(
1536 select segment_name
1537 FROM EDW_FLEX_SEG_MAPPINGS_V
1538 WHERE DIMENSION_SHORT_NAME=g_dimension_name
1539 and structure_num=e2.chart_of_accounts_id
1540 and lower(instance_code)=lower(e2.instance));
1541 LOOP
1542 FETCH cur_vbh_roots
1543 INTO l_vbh_root_sob_id,l_vbh_root_table(1),l_vbh_root_table(2)
1544 ,l_vbh_root_table(3),l_vbh_root_table(4);
1545 EXIT WHEN cur_vbh_roots%NOTFOUND;
1546 l_hierachy_no:=0;
1547
1548 for l_root_counter in 1..4 LOOP
1549 setTimer(l_hie_timstamp);
1550 l_hierachy_no := l_root_counter;
1551
1552 -- parse the update statement for each root definition,
1553 -- not for each row, this will gain some performance back.
1554 l_CursorID := PARSE_UPDATE(l_hierachy_no);
1555
1556
1557 edw_owb_collection_util.INSERT_INTO_LOAD_PROGRESS(
1558 l_progress_seq_id,
1559 g_dimension_name,
1560 l_element_id,
1561 'Build Hierarchy '|| l_hierachy_no,
1562 sysdate,
1563 null,
1564 null,
1565 null,
1566 110+ l_hierachy_no,
1567 'I',
1568 l_element_id);
1569
1570 if (l_hierachy_no <=4 and l_vbh_root_table(l_root_counter) is not null) then
1571 l_root_value := GET_VBH_ROOT(l_vbh_root_sob_id, l_vbh_root_table(l_root_counter));
1572
1573 l_rowsUpdated := PROCESS_ROOT(l_root_value,l_hierachy_no, l_CursorID);
1574 g_rows_updated:=g_rows_updated+l_rowsUpdated;
1575
1576 l_rowsUpdated := PROCESS_ROOT_HIR(l_root_value,l_hierachy_no, l_CursorID);
1577 g_rows_updated:=g_rows_updated+l_rowsUpdated;
1578
1579 -- done with the hierarchy, close the update cursor.
1580 CLOSE_UPDATE(l_CursorID);
1581
1582 else
1583 if l_hierachy_no >4 then
1584 edw_log.put_line('Warning : More than 4 roots.');
1585 end if;
1586 end if;
1587
1591 l_element_id,
1588 edw_owb_collection_util.INSERT_INTO_LOAD_PROGRESS(
1589 l_progress_seq_id,
1590 g_dimension_name,
1592 null,
1593 null,
1594 sysdate,
1595 null,
1596 null,
1597 110+ l_hierachy_no,
1598 'U',
1599 l_element_id);
1600 logTime('For hierarchy ' || l_root_counter , l_hie_timstamp);
1601 END LOOP;--for the for loop of root value
1602 END LOOP;
1603 CLOSE cur_vbh_roots;
1604
1605 edw_log.put_line('Finished collecting data.');
1606
1607 setTimer(l_log_timestamp);
1608 check_stand_alone_value;
1609 logTime('checking stand along values', l_log_timestamp);
1610
1611
1612 if (g_completion_status=0) then
1613 l_program_status:=
1614 fnd_concurrent.set_completion_status('NORMAL', 'NORMAL COMPLETION');
1615 edw_log.put_line('Normal complete.');
1616 setTimer(l_log_timestamp);
1617 clean_up_temp_table;
1618 logTime('cleaning up vbh temp table', l_log_timestamp);
1619
1620 setTimer(l_log_timestamp);
1621 clean_up_global_temp_table;
1622 logTime('cleaning up global temp table', l_log_timestamp);
1623
1624 l_status:=edw_owb_collection_util.write_to_collection_log(
1625 g_dimension_name,
1626 l_element_id,
1627 'DIMENSION',
1628 g_conc_program_id,
1629 l_start_date,
1630 sysdate,
1631 null,
1632 g_rows_updated,
1633 g_rows_updated,
1634 null,
1635 null,
1636 null,
1637 'SUCCESS',
1638 'SUCCESS', l_progress_seq_id);
1639
1640 elsif (g_completion_status=1) then
1641 l_program_status:=
1642 fnd_concurrent.set_completion_status('WARNING',NULL);
1643 edw_log.put_line('Complete with warning.');
1644 setTimer(l_log_timestamp);
1645 clean_up_temp_table;
1646 logTime('cleaning up vbh temp table', l_log_timestamp);
1647
1648 setTimer(l_log_timestamp);
1649 clean_up_global_temp_table;
1650 logTime('cleaning up global temp table', l_log_timestamp);
1651
1652 l_status:=edw_owb_collection_util.write_to_collection_log(
1653 g_dimension_name,
1654 l_element_id,
1655 'DIMENSION',
1656 g_conc_program_id,
1657 l_start_date,
1658 sysdate,null,
1659 g_rows_updated,
1660 g_rows_updated,null,null,null,
1661 'WARNING',
1662 'WARNING', l_progress_seq_id);
1663
1664 elsif (g_completion_status=2) then
1665 l_program_status:=
1666 fnd_concurrent.set_completion_status('ERROR',NULL);
1667 edw_log.put_line('Error.');
1668 l_status:=edw_owb_collection_util.write_to_collection_log(
1669 g_dimension_name,
1670 l_element_id,
1671 'DIMENSION',
1672 g_conc_program_id,
1673 l_start_date,
1674 sysdate,null,
1675 g_rows_updated,
1676 g_rows_updated,null,null,null,
1677 'ERROR',
1678 'ERROR',l_progress_seq_id);
1679
1680 end if;
1681 COMMIT;
1682 edw_log.put_line('Total number of rows updated: '||g_rows_updated );
1683 edw_log.put_line('Total number of diamond hits: '||g_diamond_output);
1684 edw_log.put_line('Total number of standalone nodes: '||g_standalone);
1685 edw_log.put_line('Total number of root setup errors: '||g_rootsetup_error);
1686 edw_log.put_line('Total time spent on error handling : '|| edw_log.duration(g_err_cum_timestamp));
1687 edw_log.put_line('finished loading VBH');
1688 edw_log.put_line('current time :'||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
1689 logTime('the whole VBH Loading', l_start_date);
1690
1691 --added for bug 4124723
1692 -- Call Function to create level MV's
1693 create_dim_levels_mv(p_dimension_no);
1694
1695 EXCEPTION
1696 WHEN OTHERS THEN
1697 Errbuf :=sqlerrm;
1698 Retcode:=sqlcode;
1699 l_exe_status:=false;
1700 g_completion_status:=2;
1701
1702 l_status:= edw_owb_collection_util.write_to_collection_log(
1703 g_dimension_name,
1704 l_element_id,
1705 'DIMENSION',
1706 g_conc_program_id,
1707 l_start_date,
1708 sysdate,null,
1709 0,
1710 0,null,null,null,
1711 'Error : '||sqlcode||' : '||sqlerrm ,
1712 'ERROR',l_progress_seq_id);
1713
1714 edw_log.put_line( 'Error : '||sqlcode||' : '||sqlerrm);
1715 l_program_status:=
1716 fnd_concurrent.set_completion_status('ERROR',NULL);
1717 rollback;
1718 END; --end collection program
1719
1720 /**
1721 Added for bug 4124723, retunrs APPS scehma name
1722 **/
1723 FUNCTION get_apps_schema_name RETURN VARCHAR2 IS
1724
1725 l_apps_schema_name VARCHAR2(30);
1726
1727 CURSOR c_apps_schema_name IS
1728 SELECT oracle_username
1729 FROM fnd_oracle_userid WHERE oracle_id
1730 BETWEEN 900 AND 999 AND read_only_flag = 'U';
1731 BEGIN
1732
1733 OPEN c_apps_schema_name;
1734 FETCH c_apps_schema_name INTO l_apps_schema_name;
1735 CLOSE c_apps_schema_name;
1736 RETURN l_apps_schema_name;
1737
1738 EXCEPTION
1739 WHEN OTHERS THEN
1740 RETURN NULL;
1741 END get_apps_schema_name;
1742
1743
1744 /**
1745 Added for bug 4124723
1746 This Procedure will create/refresh MV for all the levels in GL dimensions.
1747 This will called after refreshing Dimension table from Collect.
1748 **/
1749 Procedure create_dim_levels_mv (p_dim_no IN varchar2) IS
1750
1751 db_versn varchar2(100);
1752 l_mview_name varchar2(30);
1753 l_tmp_name varchar2(30);
1754 l_stmt_mvcrt varchar2(1000);
1755 l_level_counter number;
1756 l_hierarchy_cnt number;
1757 l_lvl_num NUMBER;
1758 L_schema_name VARCHAR2(30);
1759 l_tspace_name VARCHAR2(30);
1760 l_stmt VARCHAR2(1000);
1761
1762 CURSOR c_mv_exists(p_mv_name varchar2, p_schema_name varchar2 ) IS
1763 SELECT MVIEW_NAME FROM ALL_MVIEWS WHERE OWNER = p_schema_name
1764 AND MVIEW_NAME= p_mv_name;
1765
1766 BEGIN
1767
1768 l_mview_name := NULL;
1769 l_tmp_name := NULL;
1770
1771 --check database version. it should be 9i or above
1772 --get the version
1773 select version into db_versn from v$instance;
1774 edw_log.put_line( 'Database Version:= '||db_versn);
1775 select replace(substr(version,1,instr(version,'.',1,2)-1),'.') into db_versn from v$instance;
1776
1777 l_stmt:= 'select tablespace_name from all_tables where table_name='''||g_dimension_name||''' and owner='''||get_bis_schema_name||'''';
1778 execute immediate l_stmt into l_tspace_name;
1779
1780 l_schema_name := get_apps_schema_name;
1781 if(db_versn>=90) then --Database version is 9i or above
1782
1783 for l_hierarchy_cnt in 1..4 LOOP --- for all four hierarchies
1784 for l_level_counter in 2..15 LOOP --- for all 15 levels in a hierarchy
1785
1786
1787 l_lvl_num := l_hierarchy_cnt*100+l_level_counter ;
1788
1789 l_mview_name:= 'EDW_GLACT'||p_dim_no|| '_H'||l_lvl_num||'_MV';
1790
1791 edw_log.put_line( 'Checking if MV ' || l_mview_name||' Exists in the Database');
1792
1793 --check if the MV exists
1794 OPEN c_mv_exists(l_mview_Name,l_schema_name);
1795 fetch c_mv_exists into l_tmp_name;
1796 CLOSE c_mv_exists;
1797
1798 --- if the mv exists refresh all the levels mv
1799 if(l_tmp_name is not null) then
1800 edw_log.put_line( 'Refreshing MV'||l_mview_name);
1801 dbms_mview.refresh (l_mview_name,'C');
1802 else -- if mv does not exist creat MV's
1803 l_stmt_mvcrt := 'CREATE MATERIALIZED VIEW '|| l_mview_name||' TABLESPACE '||l_tspace_name || ' ENABLE QUERY REWRITE AS SELECT DISTINCT '|| 'H'||l_lvl_num||'_NAME'||' FROM '||g_dimension_name;
1804 edw_log.put_line(l_stmt_mvcrt);
1805 execute immediate l_stmt_mvcrt;
1806 end if;
1807
1808 --call gather stats for the MV
1809 dbms_stats.gather_table_stats(l_schema_name,l_mview_name);
1810
1811 l_tmp_name:=NULL;
1812 END LOOP;
1813
1814 END LOOP;
1815 else
1816 edw_log.put_line( 'Database Version is lower than 9i dimension levls MVs won''t be created '||db_versn);
1817 end if;
1818
1819 edw_log.put_line( 'Create_dim_levelS_Mv completed successfully');
1820
1821 EXCEPTION
1822 WHEN OTHERS THEN
1826
1823 edw_log.put_line( 'Error : '||sqlcode||' : '||sqlerrm);
1824 edw_log.put_line( 'Warning MV Creation for Dimension levels Failed! Ignorable Error');
1825 END create_dim_levels_mv;
1827 END EDW_GL_ACCT_M_T;