DBA Data[Home] [Help]

APPS.EDW_DUPLICATE_CLEAN SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 76

  if delete_dim_duplicates=false then
    return false;
Line: 93

  if delete_fact_duplicates=false then
    return false;
Line: 110

  l_stmt:='select pk_item.column_name, substr(pk_item.column_name,1,instr(upper(pk_item.column_name),''_KEY'')-1) '||
  'from edw_dimensions_md_v rel ,  '||
  'edw_unique_keys_md_v pk,  '||
  'edw_pvt_key_columns_md_v isu,  '||
  'edw_pvt_columns_md_v pk_item  '||
  'where  '||
  'rel.dim_name=:a '||
  'and pk.entity_id=rel.dim_id '||
  'and pk.primarykey=1  '||
  'and isu.key_id=pk.key_id '||
  'and pk_item.column_id=isu.column_id';
Line: 157

  l_stmt:=' select ltc.name '||
  'from '||
  'edw_tables_md_v ltc, '||
  'edw_dimensions_md_v dim, '||
  'edw_levels_md_v lvl '||
  'where dim.dim_name=:a '||
  'and lvl.dim_id=dim.dim_id '||
  'and ltc.name=lvl.level_name||''_LTC''';
Line: 218

    l_stmt:='select pk_item.column_name, pk_item.data_type '||
    'from edw_relations_md_v rel ,  '||
    'edw_unique_keys_md_v pk,  '||
    'edw_pvt_key_columns_md_v isu,  '||
    'edw_pvt_columns_md_v pk_item '||
    'where  '||
    'rel.relation_name=:a  '||
    'and pk.entity_id=rel.relation_id '||
    'and isu.key_id=pk.key_id '||
    'and pk_item.column_id=isu.column_id '||
    'and pk_item.column_name like ''%_PK''';
Line: 230

    l_stmt:='select pk_item.column_name, pk_item.data_type '||
    'from edw_relations_md_v rel ,  '||
    'edw_unique_keys_md_v pk,  '||
    'edw_pvt_key_columns_md_v isu,  '||
    'edw_pvt_columns_md_v pk_item '||
    'where  '||
    'rel.relation_name=:a  '||
    'and pk.entity_id=rel.relation_id '||
    'and isu.key_id=pk.key_id '||
    'and pk_item.column_id=isu.column_id ';
Line: 266

function delete_dim_duplicates return boolean is
Begin
  write_to_log_file_n('delete_dim_duplicates');
Line: 272

  if delete_dim_duplicate_data(g_dim_name,g_dim_pk,g_dim_pk_key)=false then
    return false;
Line: 276

    if delete_table_duplicates(g_ltc_tables(i),g_ltc_pk(i),g_ltc_pk_key(i))=false then
      return false;
Line: 282

  g_status_message:='Error in delete_dim_duplicates function '||sqlerrm;
Line: 288

function delete_fact_duplicates return boolean is
Begin
  write_to_log_file_n('delete_fact_duplicates');
Line: 291

  if delete_table_duplicates(g_fact_name,g_fact_pk,g_fact_pk_key)=false then
    return false;
Line: 296

  g_status_message:='Error in delete_fact_duplicates function '||sqlerrm;
Line: 302

function delete_table_duplicates(p_table varchar2,p_pk varchar2,p_pk_key varchar2) return boolean is
l_stmt varchar2(8000);
Line: 313

  write_to_log_file_n('delete_table_duplicates');
Line: 330

  l_stmt:=l_stmt||' as select ';
Line: 355

  l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
Line: 376

  l_stmt:=l_stmt||' as select max('||p_pk_key||') '||p_pk_key||' from '||l_dup_table||
  ' group by '||p_pk;
Line: 390

  l_stmt:=l_stmt||' as select /*+ORDERED*/ '||l_dup_table||'.row_id from '||l_dup_max_table||','||
  l_dup_table||' where '||l_dup_max_table||'.'||p_pk_key||'='||l_dup_table||'.'||p_pk_key;
Line: 408

  l_stmt:=l_stmt||' as select row_id from '||l_dup_table||' MINUS select row_id from '||
  l_dup_max_rowid_table;
Line: 424

  l_stmt:='delete /*+ORDERED USE_NL('||p_table||')*/ '||p_table||' where rowid in (select row_id from '||
  l_dup_rowid_table||')';
Line: 428

  write_to_log_file_n('Deleted '||sql%rowcount||' rows '||get_time);
Line: 435

  g_status_message:='Error in delete_table_duplicates function '||sqlerrm;
Line: 441

function delete_dim_duplicate_data(
p_dim_name varchar2,
p_dim_pk varchar2,
p_dim_pk_key varchar2
)return boolean is
-------
l_pk_table varchar2(200);
Line: 450

l_dup_update_table  varchar2(200);
Line: 463

  write_to_log_file_n('delete_dim_duplicate_data');
Line: 468

  l_dup_update_table:=g_bis_owner||'.'||l_name||'U';
Line: 478

  if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_update_table)=false then
    null;
Line: 485

  l_stmt:=l_stmt||' as select ';
Line: 499

    l_stmt:=l_stmt||' as select ';
Line: 512

    l_stmt:=l_stmt||' as select '||p_dim_pk||', max('||p_dim_pk_key||') '||p_dim_pk_key||' from '||l_dup_table||
    ' group by '||p_dim_pk;
Line: 517

    l_stmt:='create table '||l_dup_update_table||' tablespace '||g_op_table_space;
Line: 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||
    ' from '||l_dup_max_table||' max_dup,'||l_dup_table||' dup '||
    'where dup.'||p_dim_pk||'=max_dup.'||p_dim_pk||' and dup.'||p_dim_pk_key||'<>'||
    'max_dup.'||p_dim_pk_key;
Line: 528

    l_stmt:='create unique index '||l_dup_update_table||'U1 on '||l_dup_update_table||'(row_id) '||
    'tablespace '||g_op_table_space;
Line: 532

    l_stmt:='create unique index '||l_dup_update_table||'U2 on '||l_dup_update_table||'('||p_dim_pk_key||') '||
    'tablespace '||g_op_table_space;
Line: 536

    EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_dup_update_table,instr(l_dup_update_table,'.')+1,
    length(l_dup_update_table)),substr(l_dup_update_table,1,instr(l_dup_update_table,'.')-1));
Line: 552

      l_stmt:='update ';
Line: 558

      l_stmt:=l_stmt||l_fact(i)||' set ('||l_fact_fk(i)||')=(select max_'||p_dim_pk_key||' from '||
      l_dup_update_table||' where '||l_dup_update_table||'.'||p_dim_pk_key||'='||l_fact(i)||'.'||l_fact_fk(i)||
      ') ';
Line: 562

        l_stmt:=l_stmt||' where '||l_fact(i)||'.'||l_fact_fk(i)||' in (select '||p_dim_pk_key||' from '||
        l_dup_update_table||')';
Line: 568

        write_to_log_file_n('Updated '||sql%rowcount||' rows '||get_time);
Line: 582

    l_stmt:='delete /*+ORDERED USE_NL('||p_dim_name||')*/ '||p_dim_name||' where rowid in (select row_id from '||
    l_dup_update_table||')';
Line: 586

    write_to_log_file_n('Deleted '||sql%rowcount||' rows '||get_time);
Line: 588

    if EDW_OWB_COLLECTION_UTIL.drop_table(l_dup_update_table)=false then
      null;
Line: 594

  g_status_message:='Error in delete_dim_duplicate_data function '||sqlerrm;
Line: 607

  l_stmt:='select relation_name from edw_relations_md_v where relation_long_name=:a';
Line: 689

  l_stmt:='select 1 from edw_dimensions_md_v where dim_name=:a';
Line: 715

  l_stmt:='select '||
  'fact.fact_name,fk_col.column_name '||
  'from '||
  'edw_facts_md_v fact, '||
  'edw_foreign_keys_md_v fk, '||
  'EDW_PVT_KEY_COLUMNS_MD_V fku, '||
  'edw_pvt_columns_md_v fk_col, '||
  'edw_unique_keys_md_v pk, '||
  'edw_dimensions_md_v dim '||
  'where '||
  'fact.fact_id=fk.entity_id '||
  'and fk.foreign_key_id=fku.key_id '||
  'and fk_col.column_id=fku.column_id '||
  'and fk_col.parent_object_id=fact.fact_id '||
  'and pk.key_id=fk.key_id '||
  'and pk.entity_id=dim.dim_id '||
  'and dim.dim_name=:1 '||
  'order by fact.fact_name';