eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.EDW_DUPLICATE_CLEAN dependencies on EDW_OWB_COLLECTION_UTIL

Line 10: EDW_OWB_COLLECTION_UTIL.init_all(l_object_name,null,'bis.edw.duplicate_clean');

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;

Line 212: l_col EDW_OWB_COLLECTION_UTIL.varcharTableType;

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

Line 213: l_data_type EDW_OWB_COLLECTION_UTIL.varcharTableType;

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

Line 323: if EDW_OWB_COLLECTION_UTIL.drop_table(l_ok_table)=false then

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

Line 335: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_value_table)=false then

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;

Line 343: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_value_table)=false then

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;

Line 349: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_dup_value_table,instr(l_dup_value_table,'.')+1,

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||') ';

Line 362: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_table)=false then

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);

Line 367: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_dup_table,instr(l_dup_table,'.')+1,

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;

Line 369: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_value_table)=false then

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

Line 379: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_max_table)=false then

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);

Line 384: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_dup_max_table,instr(l_dup_max_table,'.')+1,

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||') ';

Line 393: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_max_rowid_table)=false then

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);

Line 398: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_dup_max_rowid_table,instr(l_dup_max_rowid_table,'.')+1,

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;

Line 400: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_max_table)=false then

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;

Line 411: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_rowid_table)=false then

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);

Line 416: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_dup_rowid_table,instr(l_dup_rowid_table,'.')+1,

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;

Line 418: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_table)=false then

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;

Line 421: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_max_rowid_table)=false then

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||')';

Line 430: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_rowid_table)=false then

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

Line 456: l_fact EDW_OWB_COLLECTION_UTIL.varcharTableType;

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;

Line 457: l_fact_fk EDW_OWB_COLLECTION_UTIL.varcharTableType;

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: ------

Line 469: if EDW_OWB_COLLECTION_UTIL.drop_table(l_pk_table)=false then

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;

Line 472: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_table)=false then

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;

Line 475: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_max_table)=false then

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;

Line 478: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_update_table)=false then

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

Line 536: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_dup_update_table,instr(l_dup_update_table,'.')+1,

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;

Line 538: if EDW_OWB_COLLECTION_UTIL.drop_table(l_pk_table)=false then

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;

Line 541: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_table)=false then

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;

Line 544: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_max_table)=false then

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

Line 553: l_found:=EDW_OWB_COLLECTION_UTIL.check_index_on_column(l_fact(i),

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;

Line 554: EDW_OWB_COLLECTION_UTIL.get_table_owner(l_fact(i)),l_fact_fk(i));

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 '||

Line 588: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_update_table)=false then

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;

Line 630: g_bis_owner:=EDW_OWB_COLLECTION_UTIL.get_db_user('BIS');

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

Line 645: g_op_table_space:=EDW_OWB_COLLECTION_UTIL.get_table_space(g_bis_owner);

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');

Line 663: EDW_OWB_COLLECTION_UTIL.write_to_log_file(p_message);

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:

Line 707: p_fact out nocopy EDW_OWB_COLLECTION_UTIL.varcharTableType,

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);

Line 708: p_fact_fk out nocopy EDW_OWB_COLLECTION_UTIL.varcharTableType,

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;