1 package BODY EDW_DUPLICATE_CLEAN AS
2 /*$Header: EDWDCLNB.pls 115.10 2003/11/18 07:00:40 smulye noship $*/
3
4 procedure clean_up_object(Errbuf out NOCOPY varchar2,Retcode out NOCOPY varchar2,p_object_name in varchar2) is
5 l_object_name varchar2(400);
6 Begin
7 Errbuf:=null;
8 Retcode:='0';
9 l_object_name:=get_short_name_for_long(p_object_name);
10 EDW_OWB_COLLECTION_UTIL.init_all(l_object_name,null,'bis.edw.duplicate_clean');
11 init_all;
12 if is_dimension(l_object_name) then
13 if clean_dimension_duplicates(l_object_name)=false then
14 errbuf:=g_status_message;
15 retcode:='2';
16 return;
17 end if;
18 else
19 if clean_fact_duplicates(l_object_name)=false then
20 errbuf:=g_status_message;
21 retcode:='2';
22 return;
23 end if;
24 end if;
25 Exception when others then
26 g_status_message:='Error in clean_dimension_duplicates '||sqlerrm;
27 g_status:=false;
28 End;
29
30
31 function clean_dimension_duplicates(p_dim_name varchar2) return boolean is
32 Begin
33 g_dim_name:=p_dim_name;
34 if clean_dimension_duplicates = false then
35 rollback;
36 return false;
37 end if;
38 write_to_log_file_n('Done');
39 return true;
40 Exception when others then
41 g_status_message:='Error in clean_dimension_duplicates '||sqlerrm;
42 write_to_log_file_n(g_status_message);
43 g_status:=false;
44 return false;
45 end;
46
47 function clean_fact_duplicates(p_fact_name varchar2) return boolean is
48 Begin
49 g_fact_name:=p_fact_name;
50 if clean_fact_duplicates = false then
51 rollback;
52 return false;
53 end if;
54 write_to_log_file_n('Done');
55 return true;
56 Exception when others then
57 g_status_message:='Error in clean_fact_duplicates '||sqlerrm;
58 write_to_log_file_n(g_status_message);
59 g_status:=false;
60 return false;
61 end;
62
63
64 function clean_dimension_duplicates return boolean is
65 Begin
66 write_to_log_file_n('clean_dimension_duplicates');
67 if get_dimension_pks=false then
68 return false;
69 end if;
70 if get_ltc_tables=false then
71 return false;
72 end if;
73 if get_ltc_pks=false then
74 return false;
75 end if;
76 if delete_dim_duplicates=false then
77 return false;
78 end if;
79 return true;
80 Exception when others then
81 g_status_message:='Error in clean_dimension_duplicates function '||sqlerrm;
82 write_to_log_file_n(g_status_message);
83 g_status:=false;
84 return false;
85 end;
86
87 function clean_fact_duplicates return boolean is
88 Begin
89 write_to_log_file_n('clean_fact_duplicates');
90 if get_fact_pks=false then
91 return false;
92 end if;
93 if delete_fact_duplicates=false then
94 return false;
95 end if;
96 return true;
97 Exception when others then
98 g_status_message:='Error in clean_fact_duplicates function '||sqlerrm;
99 write_to_log_file_n(g_status_message);
100 g_status:=false;
101 return false;
102 end;
103
104 function get_dimension_pks return boolean is
105 l_stmt varchar2(2000);
106 TYPE CurTyp IS REF CURSOR;
107 cv CurTyp;
108 Begin
109 write_to_log_file_n('get_dimension_pks');
110 l_stmt:='select pk_item.column_name, substr(pk_item.column_name,1,instr(upper(pk_item.column_name),''_KEY'')-1) '||
111 'from edw_dimensions_md_v rel , '||
112 'edw_unique_keys_md_v pk, '||
113 'edw_pvt_key_columns_md_v isu, '||
114 'edw_pvt_columns_md_v pk_item '||
115 'where '||
116 'rel.dim_name=:a '||
117 'and pk.entity_id=rel.dim_id '||
118 'and pk.primarykey=1 '||
119 'and isu.key_id=pk.key_id '||
120 'and pk_item.column_id=isu.column_id';
121 write_to_log_file_n(l_stmt);
122 open cv for l_stmt using g_dim_name;
123 fetch cv into g_dim_pk_key,g_dim_pk;
124 close cv;
125 write_to_log_file_n(g_dim_pk_key||' '||g_dim_pk);
126 return true;
127 Exception when others then
128 g_status_message:='Error in get_dimension_pks function '||sqlerrm;
129 write_to_log_file_n(g_status_message);
130 g_status:=false;
131 return false;
132 end;
133
134 function get_fact_pks return boolean is
135 Begin
136 write_to_log_file_n('get_fact_pks');
137 if get_table_pks(g_fact_name,g_fact_pk,g_fact_pk_key,'FACT')=false then
138 return false;
139 end if;
140 write_to_log_file_n('Fact PKs '||g_fact_pk||','||g_fact_pk_key);
141 return true;
142 Exception when others then
143 g_status_message:='Error in get_fact_pks function '||sqlerrm;
144 write_to_log_file_n(g_status_message);
145 g_status:=false;
146 return false;
147 end;
148
149
150
151 function get_ltc_tables return boolean is
152 l_stmt varchar2(2000);
153 TYPE CurTyp IS REF CURSOR;
154 cv CurTyp;
155 Begin
156 write_to_log_file_n('get_ltc_tables');
157 l_stmt:=' select ltc.name '||
158 'from '||
159 'edw_tables_md_v ltc, '||
160 'edw_dimensions_md_v dim, '||
161 'edw_levels_md_v lvl '||
162 'where dim.dim_name=:a '||
163 'and lvl.dim_id=dim.dim_id '||
164 'and ltc.name=lvl.level_name||''_LTC''';
165 write_to_log_file_n(l_stmt);
166 g_number_ltc:=1;
167 open cv for l_stmt using g_dim_name;
168 loop
169 fetch cv into g_ltc_tables(g_number_ltc);
170 exit when cv%notfound;
171 g_number_ltc:=g_number_ltc+1;
172 end loop;
173 g_number_ltc:=g_number_ltc-1;
174 for i in 1..g_number_ltc loop
175 write_to_log_file(g_ltc_tables(i));
176 end loop;
177 return true;
178 Exception when others then
179 g_status_message:='Error in get_ltc_tables function '||sqlerrm;
180 write_to_log_file_n(g_status_message);
181 g_status:=false;
182 return false;
183 end;
184
185 function get_ltc_pks return boolean is
186 Begin
187 write_to_log_file_n('get_ltc_pks');
188 for i in 1..g_number_ltc loop
189 write_to_log_file_n('Getting PKS for '||g_ltc_tables(i));
190 if get_table_pks(g_ltc_tables(i),g_ltc_pk(i),g_ltc_pk_key(i),null)=false then
191 return false;
192 end if;
193 end loop;
194 write_to_log_file_n('ltc tables and pks');
195 for i in 1..g_number_ltc loop
196 write_to_log_file(g_ltc_tables(i)||' '||g_ltc_pk(i)||' '||g_ltc_pk_key(i));
197 end loop;
198 return true;
199 Exception when others then
200 g_status_message:='Error in get_ltc_pks function '||sqlerrm;
201 write_to_log_file_n(g_status_message);
202 g_status:=false;
203 return false;
204 end;
205
206
207 function get_table_pks(p_table varchar2,p_pk out NOCOPY varchar2,p_pk_key out NOCOPY varchar2,
208 p_option varchar2) return boolean is
209 l_stmt varchar2(2000);
210 TYPE CurTyp IS REF CURSOR;
211 cv CurTyp;
212 l_col EDW_OWB_COLLECTION_UTIL.varcharTableType;
213 l_data_type EDW_OWB_COLLECTION_UTIL.varcharTableType;
214 l_number_keys number;
215 Begin
216 if p_option='FACT' then
217 --due to OWB issue where by the list of all fk is coming out NOCOPY as a UK
218 l_stmt:='select pk_item.column_name, pk_item.data_type '||
219 'from edw_relations_md_v rel , '||
220 'edw_unique_keys_md_v pk, '||
221 'edw_pvt_key_columns_md_v isu, '||
222 'edw_pvt_columns_md_v pk_item '||
223 'where '||
224 'rel.relation_name=:a '||
225 'and pk.entity_id=rel.relation_id '||
226 'and isu.key_id=pk.key_id '||
227 'and pk_item.column_id=isu.column_id '||
228 'and pk_item.column_name like ''%_PK''';
229 else
230 l_stmt:='select pk_item.column_name, pk_item.data_type '||
231 'from edw_relations_md_v rel , '||
232 'edw_unique_keys_md_v pk, '||
233 'edw_pvt_key_columns_md_v isu, '||
234 'edw_pvt_columns_md_v pk_item '||
235 'where '||
236 'rel.relation_name=:a '||
237 'and pk.entity_id=rel.relation_id '||
238 'and isu.key_id=pk.key_id '||
239 'and pk_item.column_id=isu.column_id ';
240 end if;
241 write_to_log_file_n(l_stmt||' using '||p_table);
242 l_number_keys:=1;
243 open cv for l_stmt using p_table;
244 loop
245 fetch cv into l_col(l_number_keys),l_data_type(l_number_keys);
246 exit when cv%notfound;
247 l_number_keys:=l_number_keys+1;
248 end loop;
249 l_number_keys:=l_number_keys-1;
250 close cv;
251 for i in 1..l_number_keys loop
252 if l_data_type(i)='VARCHAR2' then
253 p_pk:=l_col(i);
254 p_pk_key:=p_pk||'_KEY';
255 exit;
256 end if;
257 end loop;
258 return true;
259 Exception when others then
260 g_status_message:='Error in get_table_pks function '||sqlerrm;
261 write_to_log_file_n(g_status_message);
262 g_status:=false;
263 return false;
264 end;
265
266 function delete_dim_duplicates return boolean is
267 Begin
268 write_to_log_file_n('delete_dim_duplicates');
269 /*
270 back up the dimension duplicate keys and then if facts have those keys, replace them.
271 */
272 if delete_dim_duplicate_data(g_dim_name,g_dim_pk,g_dim_pk_key)=false then
273 return false;
274 end if;
275 for i in 1..g_number_ltc loop
276 if delete_table_duplicates(g_ltc_tables(i),g_ltc_pk(i),g_ltc_pk_key(i))=false then
277 return false;
278 end if;
279 end loop;
280 return true;
281 Exception when others then
282 g_status_message:='Error in delete_dim_duplicates function '||sqlerrm;
283 write_to_log_file_n(g_status_message);
284 g_status:=false;
285 return false;
286 end;
287
288 function delete_fact_duplicates return boolean is
289 Begin
290 write_to_log_file_n('delete_fact_duplicates');
291 if delete_table_duplicates(g_fact_name,g_fact_pk,g_fact_pk_key)=false then
292 return false;
293 end if;
294 return true;
295 Exception when others then
296 g_status_message:='Error in delete_fact_duplicates function '||sqlerrm;
297 write_to_log_file_n(g_status_message);
298 g_status:=false;
299 return false;
300 end;
301
302 function delete_table_duplicates(p_table varchar2,p_pk varchar2,p_pk_key varchar2) return boolean is
303 l_stmt varchar2(8000);
304 l_dup_value_table varchar2(400);
305 l_dup_table varchar2(400);
306 l_dup_max_table varchar2(400);
307 l_dup_max_rowid_table varchar2(400);
308 l_dup_rowid_table varchar2(400);
309 l_ok_table varchar2(400);
310 l_name varchar2(400);
311 l_count number;
312 Begin
313 write_to_log_file_n('delete_table_duplicates');
314 l_name:=substr(p_table,1,26);
315 write_to_log_file_n('BIS Owner is '||g_bis_owner);
316
317 l_dup_value_table :=g_bis_owner||'.'||l_name||'A';
318 l_dup_table :=g_bis_owner||'.'||l_name||'B';
319 l_dup_max_table :=g_bis_owner||'.'||l_name||'C';
320 l_dup_max_rowid_table :=g_bis_owner||'.'||l_name||'D';
321 l_dup_rowid_table :=g_bis_owner||'.'||l_name||'E';
322 l_ok_table:=g_bis_owner||'.'||l_name||'OK';
323 if EDW_OWB_COLLECTION_UTIL.drop_table(l_ok_table)=false then
324 null;
325 end if;
326 l_stmt:='create table '||l_dup_value_table||' tablespace '||g_op_table_space;
327 if g_parallel is not null then
328 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
329 end if;
330 l_stmt:=l_stmt||' as select ';
331 if g_parallel is not null then
332 l_stmt:=l_stmt||' /*+PARALLEL('||p_table||','||g_parallel||')*/ ';
333 end if;
334 l_stmt:=l_stmt||p_pk||' from '||p_table||' having count('||p_pk||')>1 group by '||p_pk;
335 if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_value_table)=false then
336 null;
337 end if;
338 write_to_log_file_n(l_stmt||get_time);
339 execute immediate l_stmt;
340 l_count:=sql%rowcount;
341 write_to_log_file_n('Created '||l_dup_value_table||' with '||l_count||' rows '||get_time);
342 if l_count=0 then
343 if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_value_table)=false then
344 null;
345 end if;
346 return true;
347 end if;
348 write_to_log_file_n('Created '||l_dup_value_table||' with '||l_count||' rows '||get_time);
349 EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_dup_value_table,instr(l_dup_value_table,'.')+1,
350 length(l_dup_value_table)),substr(l_dup_value_table,1,instr(l_dup_value_table,'.')-1));
351 l_stmt:='create table '||l_dup_table||' tablespace '||g_op_table_space;
352 if g_parallel is not null then
353 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
354 end if;
355 l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
356 if g_parallel is not null then
357 l_stmt:=l_stmt||' /*+PARALLEL('||p_table||','||g_parallel||')*/ ';
358 end if;
359 l_stmt:=l_stmt||p_table||'.'||p_pk||','||p_table||'.'||p_pk_key||','||p_table||'.rowid row_id from '||
360 l_dup_value_table||','||p_table||' where '||l_dup_value_table||'.'||p_pk||'='||p_table||'.'||p_pk;
361 write_to_log_file_n(l_stmt||get_time);
362 if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_table)=false then
363 null;
364 end if;
365 execute immediate l_stmt;
366 write_to_log_file_n('Created '||l_dup_table||' with '||sql%rowcount||' rows '||get_time);
367 EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_dup_table,instr(l_dup_table,'.')+1,
368 length(l_dup_table)),substr(l_dup_table,1,instr(l_dup_table,'.')-1));
369 if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_value_table)=false then
370 null;
371 end if;
372 l_stmt:='create table '||l_dup_max_table||' tablespace '||g_op_table_space;
373 if g_parallel is not null then
374 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
375 end if;
376 l_stmt:=l_stmt||' as select max('||p_pk_key||') '||p_pk_key||' from '||l_dup_table||
377 ' group by '||p_pk;
378 write_to_log_file_n(l_stmt||get_time);
379 if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_max_table)=false then
380 null;
381 end if;
382 execute immediate l_stmt;
383 write_to_log_file_n('Created '||l_dup_max_table||' with '||sql%rowcount||' rows '||get_time);
384 EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_dup_max_table,instr(l_dup_max_table,'.')+1,
385 length(l_dup_max_table)),substr(l_dup_max_table,1,instr(l_dup_max_table,'.')-1));
386 l_stmt:='create table '||l_dup_max_rowid_table||' tablespace '||g_op_table_space;
387 if g_parallel is not null then
388 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
389 end if;
390 l_stmt:=l_stmt||' as select /*+ORDERED*/ '||l_dup_table||'.row_id from '||l_dup_max_table||','||
391 l_dup_table||' where '||l_dup_max_table||'.'||p_pk_key||'='||l_dup_table||'.'||p_pk_key;
392 write_to_log_file_n(l_stmt||get_time);
393 if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_max_rowid_table)=false then
394 null;
395 end if;
396 execute immediate l_stmt;
397 write_to_log_file_n('Created '||l_dup_max_rowid_table||' with '||sql%rowcount||' rows '||get_time);
398 EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_dup_max_rowid_table,instr(l_dup_max_rowid_table,'.')+1,
399 length(l_dup_max_rowid_table)),substr(l_dup_max_rowid_table,1,instr(l_dup_max_rowid_table,'.')-1));
400 if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_max_table)=false then
401 null;
402 end if;
403 l_stmt:='create table '||l_dup_rowid_table||'(row_id primary key) organization index '||
404 ' tablespace '||g_op_table_space;
405 if g_parallel is not null then
406 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
407 end if;
408 l_stmt:=l_stmt||' as select row_id from '||l_dup_table||' MINUS select row_id from '||
409 l_dup_max_rowid_table;
410 write_to_log_file_n(l_stmt||get_time);
411 if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_rowid_table)=false then
412 null;
413 end if;
414 execute immediate l_stmt;
415 write_to_log_file_n('Created '||l_dup_rowid_table||' with '||sql%rowcount||' rows '||get_time);
416 EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_dup_rowid_table,instr(l_dup_rowid_table,'.')+1,
417 length(l_dup_rowid_table)),substr(l_dup_rowid_table,1,instr(l_dup_rowid_table,'.')-1));
418 if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_table)=false then
419 null;
420 end if;
421 if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_max_rowid_table)=false then
422 null;
423 end if;
424 l_stmt:='delete /*+ORDERED USE_NL('||p_table||')*/ '||p_table||' where rowid in (select row_id from '||
425 l_dup_rowid_table||')';
426 write_to_log_file_n(l_stmt||get_time);
427 execute immediate l_stmt;
428 write_to_log_file_n('Deleted '||sql%rowcount||' rows '||get_time);
429 commit;
430 if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_rowid_table)=false then
431 null;
432 end if;
433 return true;
434 Exception when others then
435 g_status_message:='Error in delete_table_duplicates function '||sqlerrm;
436 write_to_log_file_n(g_status_message);
437 g_status:=false;
438 return false;
439 end;
440
441 function delete_dim_duplicate_data(
442 p_dim_name varchar2,
443 p_dim_pk varchar2,
444 p_dim_pk_key varchar2
445 )return boolean is
446 -------
447 l_pk_table varchar2(200);
448 l_dup_table varchar2(200);
449 l_dup_max_table varchar2(200);
450 l_dup_update_table varchar2(200);
451 l_name varchar2(200);
452 -------
453 l_stmt varchar2(8000);
454 l_count number;
455 ------
456 l_fact EDW_OWB_COLLECTION_UTIL.varcharTableType;
457 l_fact_fk EDW_OWB_COLLECTION_UTIL.varcharTableType;
458 l_number_fact number;
459 ------
460 l_found boolean;
461 ------
462 Begin
463 write_to_log_file_n('delete_dim_duplicate_data');
464 l_name:=substr(p_dim_name,1,27);
465 l_pk_table:=g_bis_owner||'.'||l_name||'P';
466 l_dup_table:=g_bis_owner||'.'||l_name||'D';
467 l_dup_max_table:=g_bis_owner||'.'||l_name||'DM';
468 l_dup_update_table:=g_bis_owner||'.'||l_name||'U';
469 if EDW_OWB_COLLECTION_UTIL.drop_table(l_pk_table)=false then
470 null;
471 end if;
472 if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_table)=false then
473 null;
474 end if;
475 if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_max_table)=false then
476 null;
477 end if;
478 if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_update_table)=false then
479 null;
480 end if;
481 l_stmt:='create table '||l_pk_table||' tablespace '||g_op_table_space;
482 if g_parallel is not null then
483 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
484 end if;
485 l_stmt:=l_stmt||' as select ';
486 if g_parallel is not null then
487 l_stmt:=l_stmt||' /*+PARALLEL('||p_dim_name||','||g_parallel||')*/ ';
488 end if;
489 l_stmt:=l_stmt||p_dim_pk||' from '||p_dim_name||' having count('||p_dim_pk||')>1 group by '||p_dim_pk;
490 write_to_log_file_n(l_stmt||get_time);
491 execute immediate l_stmt;
492 l_count:=sql%rowcount;
493 write_to_log_file_n('Created with '||l_count||' rows '||get_time);
494 if l_count>0 then
495 l_stmt:='create table '||l_dup_table||' tablespace '||g_op_table_space;
496 if g_parallel is not null then
497 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
498 end if;
499 l_stmt:=l_stmt||' as select ';
500 if g_parallel is not null then
501 l_stmt:=l_stmt||' /*+PARALLEL('||p_dim_name||','||g_parallel||')*/ ';
502 end if;
503 l_stmt:=l_stmt||'dim.rowid row_id,dim.'||p_dim_pk||',dim.'||p_dim_pk_key||' from '||l_pk_table||','||
504 p_dim_name||' dim where dim.'||p_dim_pk||'='||l_pk_table||'.'||p_dim_pk;
505 write_to_log_file_n(l_stmt||get_time);
506 execute immediate l_stmt;
507 write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
508 l_stmt:='create table '||l_dup_max_table||' tablespace '||g_op_table_space;
509 if g_parallel is not null then
510 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
511 end if;
512 l_stmt:=l_stmt||' as select '||p_dim_pk||', max('||p_dim_pk_key||') '||p_dim_pk_key||' from '||l_dup_table||
513 ' group by '||p_dim_pk;
514 write_to_log_file_n(l_stmt||get_time);
515 execute immediate l_stmt;
516 write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
517 l_stmt:='create table '||l_dup_update_table||' tablespace '||g_op_table_space;
518 if g_parallel is not null then
522 ' from '||l_dup_max_table||' max_dup,'||l_dup_table||' dup '||
519 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
520 end if;
521 l_stmt:=l_stmt||' as select dup.row_id,dup.'||p_dim_pk_key||',max_dup.'||p_dim_pk_key||' max_'||p_dim_pk_key||
523 'where dup.'||p_dim_pk||'=max_dup.'||p_dim_pk||' and dup.'||p_dim_pk_key||'<>'||
524 'max_dup.'||p_dim_pk_key;
525 write_to_log_file_n(l_stmt||get_time);
526 execute immediate l_stmt;
527 write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
528 l_stmt:='create unique index '||l_dup_update_table||'U1 on '||l_dup_update_table||'(row_id) '||
529 'tablespace '||g_op_table_space;
530 write_to_log_file_n(l_stmt||get_time);
531 execute immediate l_stmt;
532 l_stmt:='create unique index '||l_dup_update_table||'U2 on '||l_dup_update_table||'('||p_dim_pk_key||') '||
533 'tablespace '||g_op_table_space;
534 write_to_log_file_n(l_stmt||get_time);
535 execute immediate l_stmt;
536 EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_dup_update_table,instr(l_dup_update_table,'.')+1,
537 length(l_dup_update_table)),substr(l_dup_update_table,1,instr(l_dup_update_table,'.')-1));
538 if EDW_OWB_COLLECTION_UTIL.drop_table(l_pk_table)=false then
539 null;
540 end if;
541 if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_table)=false then
542 null;
543 end if;
544 if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_max_table)=false then
545 null;
546 end if;
547 --update the facts
548 if get_fact_fk_for_dim(p_dim_name,l_fact,l_fact_fk,l_number_fact)=false then
549 return false;
550 end if;
551 for i in 1..l_number_fact loop
552 l_stmt:='update ';
553 l_found:=EDW_OWB_COLLECTION_UTIL.check_index_on_column(l_fact(i),
554 EDW_OWB_COLLECTION_UTIL.get_table_owner(l_fact(i)),l_fact_fk(i));
555 if l_found then
556 l_stmt:=l_stmt||'/*+ORDERED USE_NL('||l_fact(i)||')*/ ';
557 end if;
558 l_stmt:=l_stmt||l_fact(i)||' set ('||l_fact_fk(i)||')=(select max_'||p_dim_pk_key||' from '||
559 l_dup_update_table||' where '||l_dup_update_table||'.'||p_dim_pk_key||'='||l_fact(i)||'.'||l_fact_fk(i)||
560 ') ';
561 if l_found then
562 l_stmt:=l_stmt||' where '||l_fact(i)||'.'||l_fact_fk(i)||' in (select '||p_dim_pk_key||' from '||
563 l_dup_update_table||')';
564 end if;
565 write_to_log_file_n(l_stmt||get_time);
566 begin
567 execute immediate l_stmt;
568 write_to_log_file_n('Updated '||sql%rowcount||' rows '||get_time);
569 commit;
570 exception when others then
571 write_to_log_file_n(sqlerrm);
572 if sqlcode=-00942 then
573 write_to_log_file('This error can be ignored. Not all facts mentioned in metadata need to be '||
574 'implemented');
575 else
576 g_status_message:=sqlerrm;
577 return false;
578 end if;
579 end;
580 end loop;
581 --delete dim dup data
582 l_stmt:='delete /*+ORDERED USE_NL('||p_dim_name||')*/ '||p_dim_name||' where rowid in (select row_id from '||
583 l_dup_update_table||')';
584 write_to_log_file_n(l_stmt||get_time);
585 execute immediate l_stmt;
586 write_to_log_file_n('Deleted '||sql%rowcount||' rows '||get_time);
587 commit;
588 if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_update_table)=false then
589 null;
590 end if;
591 end if;
592 return true;
593 Exception when others then
594 g_status_message:='Error in delete_dim_duplicate_data function '||sqlerrm;
595 write_to_log_file_n(g_status_message);
596 g_status:=false;
597 return false;
598 end;
599
600
601 function get_short_name_for_long(p_name varchar2) return varchar2 is
602 l_stmt varchar2(2000);
603 TYPE CurTyp IS REF CURSOR;
604 cv CurTyp;
605 l_name varchar2(400);
606 Begin
607 l_stmt:='select relation_name from edw_relations_md_v where relation_long_name=:a';
608 write_to_log_file(l_stmt);
609 open cv for l_stmt using p_name;
610 fetch cv into l_name;
611 close cv;
612 if l_name is null then
613 l_name:=p_name;
614 end if;
615 return l_name;
616 Exception when others then
617 g_status_message:=sqlerrm;
618 g_status:=false;
619 return null;
620 End;
621
622 procedure init_all is
623 check_tspace_exist varchar(1);
624 check_ts_mode varchar(1);
625 physical_tspace_name varchar2(100);
626
627 Begin
628 g_status_message:=null;
629 g_status:=true;
630 g_bis_owner:=EDW_OWB_COLLECTION_UTIL.get_db_user('BIS');
631
632 g_op_table_space:=fnd_profile.value('EDW_OP_TABLE_SPACE');
633
634 if g_op_table_space is null then
635 AD_TSPACE_UTIL.is_new_ts_mode (check_ts_mode);
636 If check_ts_mode ='Y' then
637 AD_TSPACE_UTIL.get_tablespace_name ('BIS', 'INTERFACE','Y',check_tspace_exist, physical_tspace_name);
638 if check_tspace_exist='Y' and physical_tspace_name is not null then
639 g_op_table_space := physical_tspace_name;
640 end if;
641 end if;
642 end if;
643
644 if g_op_table_space is null then
645 g_op_table_space:=EDW_OWB_COLLECTION_UTIL.get_table_space(g_bis_owner);
646 end if;
647
648 write_to_log_file_n('Operation table space='||g_op_table_space);
649 g_parallel:=fnd_profile.value('EDW_PARALLEL');
650 write_to_log_file_n ('Degree of parallelism (null is default)='||g_parallel);
651 if g_parallel=0 then
652 g_parallel:=null;
653 end if;
654 null;
655 Exception when others then
656 g_status_message:='Error in init_all '||sqlerrm;
657 write_to_log_file_n(g_status_message);
658 g_status:=false;
659 end;
660
661 procedure write_to_log_file(p_message varchar2) is
662 begin
663 EDW_OWB_COLLECTION_UTIL.write_to_log_file(p_message);
664 Exception when others then
665 null;
666 End;
667
668 procedure write_to_log_file_n(p_message varchar2) is
669 begin
670 write_to_log_file(' ');
671 write_to_log_file(p_message);
672 Exception when others then
673 null;
674 End;
675
676 function get_time return varchar2 is
677 begin
678 return ' '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS');
679 Exception when others then
680 write_to_log_file_n('Error in get_time '||sqlerrm);
681 End;
682
683 function is_dimension(p_object_name varchar2) return boolean is
684 l_stmt varchar2(2000);
685 TYPE CurTyp IS REF CURSOR;
686 cv CurTyp;
687 l_res number;
688 Begin
689 l_stmt:='select 1 from edw_dimensions_md_v where dim_name=:a';
690 write_to_log_file(l_stmt);
691 open cv for l_stmt using p_object_name;
692 fetch cv into l_res;
693 close cv;
694 if l_res=1 then
695 return true;
696 else
697 return false;
698 end if;
699 Exception when others then
700 g_status_message:=sqlerrm;
701 g_status:=false;
702 return false;
703 End;
704
705 function get_fact_fk_for_dim(
706 p_dim_name varchar2,
707 p_fact out nocopy EDW_OWB_COLLECTION_UTIL.varcharTableType,
708 p_fact_fk out nocopy EDW_OWB_COLLECTION_UTIL.varcharTableType,
709 p_number_fact_fk out nocopy number
710 )return boolean is
711 l_stmt varchar2(2000);
712 TYPE CurTyp IS REF CURSOR;
713 cv CurTyp;
714 Begin
715 l_stmt:='select '||
716 'fact.fact_name,fk_col.column_name '||
717 'from '||
718 'edw_facts_md_v fact, '||
719 'edw_foreign_keys_md_v fk, '||
720 'EDW_PVT_KEY_COLUMNS_MD_V fku, '||
721 'edw_pvt_columns_md_v fk_col, '||
722 'edw_unique_keys_md_v pk, '||
723 'edw_dimensions_md_v dim '||
724 'where '||
725 'fact.fact_id=fk.entity_id '||
726 'and fk.foreign_key_id=fku.key_id '||
727 'and fk_col.column_id=fku.column_id '||
728 'and fk_col.parent_object_id=fact.fact_id '||
729 'and pk.key_id=fk.key_id '||
730 'and pk.entity_id=dim.dim_id '||
731 'and dim.dim_name=:1 '||
732 'order by fact.fact_name';
733 p_number_fact_fk:=1;
734 write_to_log_file(l_stmt);
735 open cv for l_stmt using p_dim_name;
736 loop
737 fetch cv into p_fact(p_number_fact_fk),p_fact_fk(p_number_fact_fk);
738 exit when cv%notfound;
739 p_number_fact_fk:=p_number_fact_fk+1;
740 end loop;
741 close cv;
742 p_number_fact_fk:=p_number_fact_fk-1;
743 write_to_log_file('Results');
744 for i in 1..p_number_fact_fk loop
745 write_to_log_file(p_fact(i)||' '||p_fact_fk(i));
746 end loop;
747 return true;
748 Exception when others then
749 g_status_message:=sqlerrm;
750 write_to_log_file_n('Error in get_fact_fk_for_dim '||g_status_message);
751 g_status:=false;
752 return false;
753 End;
754
755 END EDW_DUPLICATE_CLEAN;
[Home] [Help]