APPS.EDW_DERIVED_FACT_FACT_COLLECT SQL Statements

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

Line: 141

  g_load_mode is BU-DELETE when inc dim changes are propogated to derived/summary facts
  In initial_set_up g_ilog and g_dlog will change names
  */
  if initial_set_up(
    p_input_table,
    g_max_threads,
    l_ilog_table,
    l_dlog_table)=false then
    return false;

Line: 166

    if EDW_OWB_COLLECTION_UTIL.update_derv_fact_input_table(
      p_input_table,
      l_ilog_table,--the g_ilog_name
      l_dlog_table, --the g_dlog_name
      g_skip_ilog_update,
      g_skip_dlog_update,
      g_skip_ilog,
      g_load_mode,
      g_full_refresh,
      g_src_object_ilog,
      g_src_object_dlog,
      g_src_snplog_has_pk,
      g_err_rec_flag,
      g_err_rec_flag_d
      )=false then
      return false;

Line: 238

          if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(p_input_table,l_job_id(l_number_jobs))=false then
            return false;

Line: 252

          if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(p_input_table,l_job_id(l_number_jobs))=false then
            return false;

Line: 271

        if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(p_input_table,l_job_id(l_number_jobs))=false then
          return false;

Line: 356

          if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(p_input_table,l_job_id(l_number_jobs))=false then
            return false;

Line: 370

          if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(p_input_table,l_job_id(l_number_jobs))=false then
            return false;

Line: 389

        if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(p_input_table,l_job_id(l_number_jobs))=false then
          return false;

Line: 598

    g_skip_ilog_update:=false;

Line: 599

    g_skip_dlog_update:=false;

Line: 605

    insert_into_load_progress_d(g_load_fk,g_fact_name,'ILOG DLOG Processing'||g_jobid_stmt,sysdate,null,'DF',
    'INSERT','ILOGPROC'||g_jobid_stmt,'I');

Line: 610

    insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'ILOGPROC'||g_jobid_stmt,'U');

Line: 675

    if EDW_OWB_COLLECTION_UTIL.drop_prot_tables(g_insert_prot_log,'PI',g_bis_owner)=false then
      return false;

Line: 678

    if EDW_OWB_COLLECTION_UTIL.drop_prot_tables(g_update_prot_log,'PU',g_bis_owner)=false then
      return false;

Line: 681

    if EDW_OWB_COLLECTION_UTIL.drop_prot_tables(g_delete_prot_log,'PD',g_bis_owner)=false then
      return false;

Line: 703

    insert_into_load_progress_d(g_load_fk,g_fact_name,'Recover from any Previous Error'||g_jobid_stmt,sysdate,null,'DF',
    'RECOVER','DFRPE'||g_jobid_stmt,'I');

Line: 709

    insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFRPE'||g_jobid_stmt,'U');

Line: 730

  elsif g_load_mode='BU-DELETE' then
    if g_collection_size=0 then
      g_skip_ilog:=true;

Line: 734

  elsif g_load_mode='BU-UPDATE' then
    if g_collection_size=0 then
      g_skip_ilog:=true;

Line: 746

  if g_err_rec_flag and g_full_refresh = false and g_load_mode <>'BU-DELETE' and g_load_mode <>'BU-UPDATE' then
    insert_into_load_progress_d(g_load_fk,g_fact_name,'Error Recovery into ILOG,DLOG'||g_jobid_stmt,sysdate,null,'DF',
    'INSERT','ERRECDI'||g_jobid_stmt,'I');

Line: 749

    if load_new_update_data= false then
      insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'ERRECDI'||g_jobid_stmt,'U');

Line: 753

    insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'ERRECDI'||g_jobid_stmt,'U');

Line: 756

    insert_into_load_progress_d(g_load_fk,g_fact_name,'Move Data into ILOG'||g_jobid_stmt,sysdate,null,'DF',
    'INSERT','DFILOG'||g_jobid_stmt,'I');

Line: 760

      insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFILOG'||g_jobid_stmt,'U');

Line: 763

    insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFILOG'||g_jobid_stmt,'U');

Line: 768

    g_type_ilog_generation:='UPDATE';

Line: 769

    g_type_dlog_generation:='UPDATE';

Line: 771

  if g_load_mode <>'BU-DELETE' and g_load_mode <>'BU-UPDATE' then
    if g_src_object_dlog is not null and g_full_refresh = false then
      insert_into_load_progress_d(g_load_fk,g_fact_name,'Move Data into DLOG'||g_jobid_stmt,sysdate,null,'DF',
      'INSERT','DFDLOG'||g_jobid_stmt,'I');

Line: 777

        insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDLOG'||g_jobid_stmt,'U');

Line: 780

      insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDLOG'||g_jobid_stmt,'U');

Line: 785

      g_type_dlog_generation:='UPDATE';

Line: 801

  insert_into_load_progress_d(g_load_fk,g_fact_name,'Read Metadata'||g_jobid_stmt,sysdate,null,'DF',
  'METADAT','DFRM'||g_jobid_stmt,'I');

Line: 808

    insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFRM'||g_jobid_stmt,'U');

Line: 837

  insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFRM'||g_jobid_stmt,'U');

Line: 872

  p_update_type varchar2,
  p_fact_dlog varchar2,
  p_skip_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
  p_number_skip_cols number,
  p_load_fk number,
  p_fresh_restart boolean,
  p_op_table_space varchar2,
  p_bu_tables EDW_OWB_COLLECTION_UTIL.varcharTableType,--before update tables.prop dim change to derv
  p_bu_dimensions EDW_OWB_COLLECTION_UTIL.varcharTableType,
  p_number_bu_tables number,
  p_bu_src_fact varchar2,--what table to look at as the src fact. if null, scan full the src fact
  p_load_mode varchar2,
  p_rollback varchar2,
  p_src_join_nl_percentage number,
  p_pre_hook varchar2,
  p_post_hook varchar2
) return boolean is
Begin
  g_fact_name:=p_fact_name;

Line: 919

  g_update_type :=p_update_type;

Line: 943

  g_load_mode is BU-DELETE when inc dim changes are propogated to derived/summary facts
  */
  write_to_log_file_n('In EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT'||get_time);

Line: 1008

    write_to_log_file_n('Delete Tables Done');

Line: 1032

  if update_rowid_table_stmt = false then
    write_to_log_file_n('update_rowid_table_stmt returned with error');

Line: 1036

  if delete_rowid_table_stmt = false then
    write_to_log_file_n('delete_rowid_table_stmt returned with error');

Line: 1040

  if insert_rowid_table_stmt = false then
    write_to_log_file_n('insert_rowid_table_stmt returned with error');

Line: 1044

  if make_insert_into_fact  = false then --make the stmt
    write_to_log_file_n('make_insert_into_fact_iv returned with error');

Line: 1048

  if make_update_into_fact  = false then --make the stmt
    write_to_log_file_n('make_update_into_fact returned with error');

Line: 1052

  if make_delete_into_fact  = false then --make the stmt
    write_to_log_file_n('make_delete_into_fact returned with error');

Line: 1058

    if g_load_mode<>'BU-DELETE' then
      if g_debug then
        write_to_log_file_n('In update mode. derv fact getting added');

Line: 1069

        g_total_insert:=0;

Line: 1070

        g_total_update:=0;

Line: 1071

        g_total_delete:=0;

Line: 1074

          if g_skip_ilog_update=false then
            l_status:=set_gilog_status;

Line: 1078

            g_skip_ilog_update:=false;

Line: 1087

          g_skip_ilog_update:=false;

Line: 1099

          insert_into_load_progress_d(g_load_fk,g_fact_name,'Move Data into Temp'||g_jobid_stmt,sysdate,null,'DF',
          'INSERT','DFTEMP'||l_count||g_jobid_stmt,'I');

Line: 1103

            insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFTEMP'||l_count||g_jobid_stmt,'U');

Line: 1106

          insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFTEMP'||l_count||g_jobid_stmt,'U');

Line: 1113

              insert_into_load_progress_d(g_load_fk,g_fact_name,'Summarize Base Fact Data'||g_jobid_stmt,sysdate,null,'DF',
              'CREATE-TABLE','DFSUM'||l_count||g_jobid_stmt,'I');

Line: 1116

                insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFSUM'||l_count||g_jobid_stmt,'U');

Line: 1120

            insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFSUM'||l_count||g_jobid_stmt,'U');

Line: 1121

            insert_into_load_progress_d(g_load_fk,g_fact_name,'Create Update/Insert rowid Tables'||g_jobid_stmt,sysdate,null,'DF',
            'CREATE-TABLE','DFROWID'||l_count||g_jobid_stmt,'I');

Line: 1123

            if execute_data_into_rowid_table = false then --creates update and insert rowid tables as select
              write_to_log_file_n('execute_data_into_rowid_table returned with error');

Line: 1125

              insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFROWID'||l_count||g_jobid_stmt,'U');

Line: 1128

            insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFROWID'||l_count||g_jobid_stmt,'U');

Line: 1133

            insert_into_temp_log('+');

Line: 1135

          if g_type_ilog_generation='UPDATE' then
            if update_ilog_status_2 = false then
              return false;

Line: 1149

    end if;--if g_load_mode<>'BU-DELETE' then

Line: 1155

    if g_load_mode='BU-DELETE' or (g_full_refresh=false and g_src_object_dlog is not null and
      g_load_mode<>'BU-UPDATE') then
      if g_debug then
        write_to_log_file_n('In delete mode. derv fact getting subtracted');

Line: 1166

        g_total_insert:=0;

Line: 1167

        g_total_update:=0;

Line: 1168

        g_total_delete:=0;

Line: 1170

          if g_skip_dlog_update=false then
            l_status:=set_gdlog_status;

Line: 1174

            g_skip_dlog_update:=false;

Line: 1183

          g_skip_dlog_update:=false;

Line: 1193

          insert_into_load_progress_d(g_load_fk,g_fact_name,'Move Update/Delete Data into Temp'||g_jobid_stmt,sysdate,null,'DF',
          'INSERT','DFDTEMP'||l_count||g_jobid_stmt,'I');

Line: 1195

          l_status:=execute_delete_data_into_temp;

Line: 1197

            insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDTEMP'||l_count||g_jobid_stmt,'U');

Line: 1200

          insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDTEMP'||l_count||g_jobid_stmt,'U');

Line: 1207

              insert_into_load_progress_d(g_load_fk,g_fact_name,'Summarize Base Fact Data'||g_jobid_stmt,sysdate,null,'DF',
              'CREATE-TABLE','DFDSUM'||l_count||g_jobid_stmt,'I');

Line: 1210

                insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDSUM'||l_count||g_jobid_stmt,'U');

Line: 1213

              insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDSUM'||l_count||g_jobid_stmt,'U');

Line: 1215

            insert_into_load_progress_d(g_load_fk,g_fact_name,'Create Delete rowid Tables'||g_jobid_stmt,sysdate,null,'DF',
            'CREATE-TABLE','DFDROWID'||l_count||g_jobid_stmt,'I');

Line: 1217

            if execute_ddata_into_rowid_table = false then --moves data into delete
              write_to_log_file_n('execute_ddata_into_rowid_table returned with error');

Line: 1219

              insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDROWID'||l_count||g_jobid_stmt,'U');

Line: 1222

            insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDROWID'||l_count||g_jobid_stmt,'U');

Line: 1227

            insert_into_temp_log('-');

Line: 1229

          if g_type_dlog_generation='UPDATE' then
            if update_dlog_status_2 = false then
             return false;

Line: 1257

  if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_delete_rowid_table) = 2 then
    insert_into_load_progress_d(g_load_fk,g_fact_name,'Update Derived/Summary Fact with Delete Data'||g_jobid_stmt,sysdate,
    null,'DF','UPDATE','DFDDEL'||p_count||g_jobid_stmt,'I');

Line: 1260

    if delete_into_fact = false then
      write_to_log_file_n('delete_into_fact returned with false');

Line: 1262

      insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDDEL'||p_count||g_jobid_stmt,'U');

Line: 1265

    insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDDEL'||p_count||g_jobid_stmt,'U');

Line: 1280

  if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_insert_rowid_table)=2 then
    insert_into_load_progress_d(g_load_fk,g_fact_name,'Insert Into Derived/Summary Fact'||g_jobid_stmt,sysdate,null,'DF',
    'INSERT','DFDINS'||p_count||g_jobid_stmt,'I');

Line: 1283

    if insert_into_fact = false then
      write_to_log_file_n('insert_into_fact returned with false');

Line: 1285

      insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDINS'||p_count||g_jobid_stmt,'U');

Line: 1288

    insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDINS'||p_count||g_jobid_stmt,'U');

Line: 1290

  if drop_insert_lock_table=false then
    return false;

Line: 1293

  if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_update_rowid_table) =2 then
    insert_into_load_progress_d(g_load_fk,g_fact_name,'Update Derived/Summary Fact'||g_jobid_stmt,sysdate,null,'DF',
    'UPDATE','DFDUPD'||p_count||g_jobid_stmt,'I');

Line: 1296

    if update_into_fact = false then
      write_to_log_file_n('update_into_fact returned with false');

Line: 1298

      insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDUPD'||p_count||g_jobid_stmt,'U');

Line: 1301

    insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDUPD'||p_count||g_jobid_stmt,'U');

Line: 1343

    l_stmt:='insert into '||p_ilog_temp||' (row_id,'||g_src_pk||') select ';

Line: 1345

    l_stmt:='insert into '||p_ilog_temp||' (row_id) select ';

Line: 1441

    g_skip_ilog_update:=true;

Line: 1447

    l_stmt:=l_stmt||' as select ';

Line: 1482

      l_stmt1:=l_stmt1||' as select ';

Line: 1547

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

Line: 1594

        l_stmt:=l_stmt||' as select /*+PARALLEL ('||g_src_object_ilog||','||g_parallel||')*/ '||
        ' distinct chartorowid(m_row$$) row_id,'||g_src_pk||',0 status  from '||g_src_object_ilog||
        ' MINUS select row_id row_id,'||g_src_pk||',0 status from '||g_ilog;

Line: 1598

        l_stmt:=l_stmt||' as select /*+PARALLEL ('||g_src_object_ilog||','||g_parallel||')*/ '||
        ' distinct chartorowid(m_row$$) row_id ,0 status  from '||g_src_object_ilog||
        ' MINUS select row_id row_id ,0 status from '||g_ilog;

Line: 1604

        l_stmt:=l_stmt||' as select distinct chartorowid(m_row$$) row_id,'||g_src_pk||',0 status  from '||
        g_src_object_ilog||' MINUS select row_id row_id,'||g_src_pk||',0 status from '||g_ilog;

Line: 1607

        l_stmt:=l_stmt||' as select distinct chartorowid(m_row$$) row_id ,0 status  from '||g_src_object_ilog||
        ' MINUS select row_id row_id ,0 status from '||g_ilog;

Line: 1628

      l_stmt:='insert into '||g_ilog||'(row_id,'||g_src_pk||',status,round) select row_id,'||g_src_pk||
      ',status,0 from '||l_ilog_temp;

Line: 1631

      l_stmt:='insert into '||g_ilog||'(row_id, status,round) select row_id,status,0 from '||l_ilog_temp;

Line: 1641

      write_to_log_file_n('Inserted '||g_ilog||' with '||sql%rowcount||' records'||get_time);

Line: 1729

    g_skip_dlog_update:=true;

Line: 1735

    l_stmt:=l_stmt||' as select ';

Line: 1782

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

Line: 1822

    l_stmt:=l_stmt||' as select ';

Line: 1848

    l_stmt:=l_stmt||' as select row_id,0 status from '||l_dlog||' MINUS select row_id,0 status from '||g_dlog;

Line: 1862

      l_stmt:='insert into '||g_dlog||'(row_id,row_id1,status,pk_key,round) select ';

Line: 1873

      l_stmt:='insert into '||g_dlog||'(row_id,status) select row_id,status  from '||l_dlog_temp;

Line: 1883

      write_to_log_file_n('Inserted '||g_dlog||' with '||sql%rowcount||' records'||get_time);

Line: 1913

 this function sets the status of the ilog from 0 to 1 and also deletes those that are 1 first
 returns:
 0: error
 1: no more records to change from 0 to 1
 2: success
*/
function set_gilog_status return number is
l_stmt varchar2(10000);

Line: 1930

  if g_type_ilog_generation='UPDATE' then
    if g_collection_size =0 then
      l_stmt:='update '||g_ilog||' set status=1 where status=0';

Line: 1934

      l_stmt:='update '||g_ilog||' set status=1 where status=0 and rownum <='||g_collection_size;

Line: 1943

      write_to_log_file_n('Updated '||l_count||' rows in '||g_ilog||get_time);

Line: 1969

        l_stmt:=l_stmt||' as select row_id,'||g_src_pk||',decode(status,1,2,2,2,decode(sign(rownum-'||
        g_collection_size||'),1,0,1)) status,round from (select row_id,'||g_src_pk||',status,round from '||
        g_ilog_prev||' order by status) abc ';

Line: 1973

        l_stmt:=l_stmt||' as select row_id,decode(status,1,2,2,2,decode(sign(rownum-'||
        g_collection_size||'),1,0,1)) status,round from (select row_id,status,round from '||
        g_ilog_prev||' order by status) abc ';

Line: 1979

        l_stmt:=l_stmt||' as select row_id,'||g_src_pk||',decode(status,1,2,0,1,2) status,round from '||
        g_ilog_prev;

Line: 1982

        l_stmt:=l_stmt||' as select row_id,decode(status,1,2,0,1,2) status,round from '||
        g_ilog_prev;

Line: 2020

 this function sets the status of the dlog from 0 to 1 and also deletes those that are 1 first
 returns:
 0: error
 1: no more records to change from 0 to 1
 2: success
*/
function set_gdlog_status return number is
l_stmt varchar2(10000);

Line: 2038

  if g_type_dlog_generation='UPDATE' then
    if g_collection_size =0 then
      l_stmt:='update '||g_dlog||' set status=1 where status=0';

Line: 2042

      l_stmt:='update '||g_dlog||' set status=1 where status=0 and rownum <='||g_collection_size;

Line: 2050

      write_to_log_file_n('Updated '||l_count||' rows in '||g_dlog||get_time);

Line: 2074

        l_stmt:=l_stmt||' as select row_id,decode(status,1,2,2,2,decode(sign(rownum-'||
        g_collection_size||'),1,0,1)) status,row_id1,pk_key,round from (select row_id,status,row_id1,pk_key,'||
        'round from '||g_dlog_prev||' order by status) abc ';

Line: 2078

        l_stmt:=l_stmt||' as select row_id,decode(status,1,2,2,2,decode(sign(rownum-'||
        g_collection_size||'),1,0,1)) status from (select row_id,status from '||g_dlog_prev||' order by status) abc ';

Line: 2083

        l_stmt:=l_stmt||' as select row_id,decode(status,1,2,0,1,2) status,row_id1,pk_key,round from '||
        g_dlog_prev;

Line: 2086

        l_stmt:=l_stmt||' as select row_id,decode(status,1,2,0,1,2) status from '||
        g_dlog_prev;

Line: 2131

l_dlog_desc :='Delete Log';

Line: 2163

  l_stmt:='select fk_item.column_name from edw_foreign_keys_md_v fk, '||
  'edw_pvt_key_columns_md_v isu,  '||
  'edw_pvt_columns_md_v fk_item  '||
  'where  '||
  'fk.entity_id=:s  '||
  'and isu.key_id=fk.foreign_key_id '||
  'and fk_item.column_id=isu.column_id';

Line: 2281

  l_stmt:=l_stmt||' as select ';

Line: 2404

    l_stmt:=l_stmt||' as select row_id from '||g_ilog||' where status=1';

Line: 2432

      l_stmt:=l_stmt||' as select ';

Line: 2532

function make_delete_data_into_temp return boolean is
l_stmt varchar2(30000);

Line: 2537

    write_to_log_file_n('In move_delete_data_into_temp');

Line: 2544

  l_stmt:=l_stmt||' as select ';

Line: 2608

  g_delete_data_temp_stmt:=l_stmt;

Line: 2612

  write_to_log_file_n('Error in make_delete_data_into_temp '||sqlerrm||' '||get_time);

Line: 2616

function execute_delete_data_into_temp return number is
l_status number :=0;

Line: 2621

    write_to_log_file_n('In execute_delete_data_into_temp');

Line: 2629

    l_stmt:=l_stmt||' as select row_id from '||g_dlog||' where status=1';

Line: 2643

  if make_delete_data_into_temp=false then
    return 0;

Line: 2650

    write_to_log_file_n('Goint to execute '||g_delete_data_temp_stmt);

Line: 2652

  execute immediate g_delete_data_temp_stmt;

Line: 2674

  write_to_log_file_n('Error in execute_delete_data_into_temp '||sqlerrm||' '||get_time);

Line: 2748

function make_delete_into_fact  return boolean is
l_last_update_date_flag boolean;

Line: 2753

    write_to_log_file_n('In make_delete_into_fact');

Line: 2756

      g_number_input_params,'LAST_UPDATE_DATE')= false then
    l_last_update_date_flag:=true;

Line: 2759

    l_last_update_date_flag:=false;

Line: 2767

  if g_update_type='DELETE-INSERT' then
    g_delete_stmt:='insert into '||g_fact_name||' ( ';

Line: 2770

      g_delete_stmt:=g_delete_stmt||g_output_params(i)||',';

Line: 2773

      g_delete_stmt:=g_delete_stmt||'CREATION_DATE,';

Line: 2775

    if l_last_update_date_flag then
      g_delete_stmt:=g_delete_stmt||'LAST_UPDATE_DATE,';

Line: 2778

    g_delete_stmt:=substr(g_delete_stmt,1,length(g_delete_stmt)-1);

Line: 2779

    g_delete_stmt:=g_delete_stmt||') select ';

Line: 2781

      g_delete_stmt:=g_delete_stmt||g_delete_rowid_table||'.'||g_output_params(i)||',';

Line: 2784

      g_delete_stmt:=g_delete_stmt||'SYSDATE,';

Line: 2786

    if l_last_update_date_flag then
      g_delete_stmt:=g_delete_stmt||'SYSDATE,';

Line: 2789

    g_delete_stmt:=substr(g_delete_stmt,1,length(g_delete_stmt)-1);

Line: 2790

    g_delete_stmt:=g_delete_stmt||' from '||g_delete_rowid_table;

Line: 2792

    g_delete_stmt_row:='update '||g_fact_name||' set ( ';

Line: 2793

    if g_update_type='ROW-BY-ROW' then
      g_delete_stmt:='update '||g_fact_name||' set ( ';

Line: 2795

    elsif g_update_type='MASS' then
      if g_parallel is null then
        g_delete_stmt:='update /*+ ORDERED USE_NL('||g_fact_name||')*/ '||g_fact_name||' set ( ';

Line: 2799

        g_delete_stmt:='update /*+ ORDERED USE_NL('||g_fact_name||')*/ /*+ PARALLEL ('||g_fact_name||','||
        g_parallel||')*/  '||g_fact_name||' set ( ';

Line: 2805

        g_delete_stmt:=g_delete_stmt||g_output_params(i)||',';

Line: 2806

        g_delete_stmt_row:=g_delete_stmt_row||g_output_params(i)||',';

Line: 2810

        g_number_input_params,'LAST_UPDATE_DATE')= false then
      l_last_update_date_flag:=true;

Line: 2812

      g_delete_stmt:=g_delete_stmt||'LAST_UPDATE_DATE,';

Line: 2813

      g_delete_stmt_row:=g_delete_stmt_row||'LAST_UPDATE_DATE,';

Line: 2815

      l_last_update_date_flag:=false;

Line: 2817

    g_delete_stmt:=substr(g_delete_stmt,1,length(g_delete_stmt)-1);

Line: 2818

    g_delete_stmt_row:=substr(g_delete_stmt_row,1,length(g_delete_stmt_row)-1);

Line: 2819

    g_delete_stmt:=g_delete_stmt||') = (select ';

Line: 2820

    g_delete_stmt_row:=g_delete_stmt_row||') = (select ';

Line: 2825

       g_delete_stmt:=g_delete_stmt||' nvl('||g_fact_name||'.'||g_output_params(i)||',0)-'||
       g_delete_rowid_table||'.'||g_output_params(i)||',';

Line: 2827

       g_delete_stmt_row:=g_delete_stmt_row||' nvl('||g_fact_name||'.'||g_output_params(i)||',0)-'||
       g_delete_rowid_table||'.'||g_output_params(i)||',';

Line: 2831

    if l_last_update_date_flag then
      g_delete_stmt:=g_delete_stmt||'SYSDATE,';

Line: 2833

      g_delete_stmt_row:=g_delete_stmt_row||'SYSDATE,';

Line: 2835

    g_delete_stmt:=substr(g_delete_stmt,1,length(g_delete_stmt)-1);

Line: 2836

    g_delete_stmt_row:=substr(g_delete_stmt_row,1,length(g_delete_stmt_row)-1);

Line: 2837

    g_delete_stmt:=g_delete_stmt||' from '||g_delete_rowid_table||' where ';

Line: 2838

    g_delete_stmt_row:=g_delete_stmt_row||' from '||g_delete_rowid_table||' where ';

Line: 2839

    g_delete_stmt_row:=g_delete_stmt_row||g_delete_rowid_table||'.row_id1=:a) where '||g_fact_name||'.rowid=:b';

Line: 2840

    if g_update_type='ROW-BY-ROW' then
      g_delete_stmt:=g_delete_stmt||g_delete_rowid_table||'.row_id1=:a) where '||g_fact_name||'.rowid=:b';

Line: 2842

    elsif g_update_type='MASS' then
      g_delete_stmt:=g_delete_stmt||g_delete_rowid_table||'.row_id1='||g_fact_name||'.rowid ) where '||
      g_fact_name||'.rowid in (select row_id1 from '||g_delete_rowid_table||')';

Line: 2850

  write_to_log_file_n('Error in make_delete_into_fact '||sqlerrm||' '||get_time);

Line: 2854

function make_update_into_fact return boolean is
l_last_update_date_flag boolean;

Line: 2859

    write_to_log_file_n('In make_update_into_fact');

Line: 2862

      g_number_input_params,'LAST_UPDATE_DATE')= false then
    l_last_update_date_flag:=true;

Line: 2865

    l_last_update_date_flag:=false;

Line: 2873

  if g_update_type='DELETE-INSERT' then
    g_update_stmt:='insert into '||g_fact_name||' ( ';

Line: 2876

      g_update_stmt:=g_update_stmt||g_output_params(i)||',';

Line: 2879

      g_update_stmt:=g_update_stmt||'CREATION_DATE,';

Line: 2881

    if l_last_update_date_flag then
      g_update_stmt:=g_update_stmt||'LAST_UPDATE_DATE,';

Line: 2884

    g_update_stmt:=substr(g_update_stmt,1,length(g_update_stmt)-1);

Line: 2885

    g_update_stmt:=g_update_stmt||') select ';

Line: 2887

      g_update_stmt:=g_update_stmt||g_update_rowid_table||'.'||g_output_params(i)||',';

Line: 2890

      g_update_stmt:=g_update_stmt||'SYSDATE,';

Line: 2892

    if l_last_update_date_flag then
      g_update_stmt:=g_update_stmt||'SYSDATE,';

Line: 2895

    g_update_stmt:=substr(g_update_stmt,1,length(g_update_stmt)-1);

Line: 2896

    g_update_stmt:=g_update_stmt||' from '||g_update_rowid_table;

Line: 2898

    g_update_stmt_row:='update '||g_fact_name||' set ( ';

Line: 2899

    if g_update_type='ROW-BY-ROW' then
      g_update_stmt:='update '||g_fact_name||' set ( ';

Line: 2901

    elsif g_update_type='MASS' then
      if g_parallel is null then
        g_update_stmt:='update  /*+ ORDERED USE_NL('||g_fact_name||')*/ '||g_fact_name||' set ( ';

Line: 2905

        g_update_stmt:='update /*+ ORDERED USE_NL('||g_fact_name||')*/ /*+ PARALLEL ('||g_fact_name||','||
        g_parallel||')*/  '||g_fact_name||' set ( ';

Line: 2911

        g_update_stmt:=g_update_stmt||g_output_params(i)||',';

Line: 2912

        g_update_stmt_row:=g_update_stmt_row||g_output_params(i)||',';

Line: 2915

    if l_last_update_date_flag then
      g_update_stmt:=g_update_stmt||'LAST_UPDATE_DATE,';

Line: 2917

      g_update_stmt_row:=g_update_stmt_row||'LAST_UPDATE_DATE,';

Line: 2919

    g_update_stmt:=substr(g_update_stmt,1,length(g_update_stmt)-1);

Line: 2920

    g_update_stmt_row:=substr(g_update_stmt_row,1,length(g_update_stmt_row)-1);

Line: 2921

    g_update_stmt:=g_update_stmt||') = (select ';

Line: 2922

    g_update_stmt_row:=g_update_stmt_row||') = (select ';

Line: 2925

        g_update_stmt:=g_update_stmt||' nvl('||g_fact_name||'.'||g_output_params(i)||',0)+'||
        g_update_rowid_table||'.'||g_output_params(i)||',';

Line: 2927

        g_update_stmt_row:=g_update_stmt_row||' nvl('||g_fact_name||'.'||g_output_params(i)||',0)+'||
        g_update_rowid_table||'.'||g_output_params(i)||',';

Line: 2931

    if l_last_update_date_flag then
      g_update_stmt:=g_update_stmt||'SYSDATE,';

Line: 2933

      g_update_stmt_row:=g_update_stmt_row||'SYSDATE,';

Line: 2935

    g_update_stmt:=substr(g_update_stmt,1,length(g_update_stmt)-1);

Line: 2936

    g_update_stmt_row:=substr(g_update_stmt_row,1,length(g_update_stmt_row)-1);

Line: 2937

    g_update_stmt:=g_update_stmt||' from '||g_update_rowid_table||' where ';

Line: 2938

    g_update_stmt_row:=g_update_stmt_row||' from '||g_update_rowid_table||' where ';

Line: 2939

    g_update_stmt_row:=g_update_stmt_row||g_update_rowid_table||'.row_id1=:a) where '||g_fact_name||'.rowid=:b';

Line: 2940

    if g_update_type='ROW-BY-ROW' then
      g_update_stmt:=g_update_stmt||g_update_rowid_table||'.row_id1=:a) where '||g_fact_name||'.rowid=:b';

Line: 2942

    elsif g_update_type='MASS' then
      g_update_stmt:=g_update_stmt||g_update_rowid_table||'.row_id1='||g_fact_name||'.rowid ) where '||
      g_fact_name||'.rowid in (select row_id1 from '||g_update_rowid_table||')';

Line: 2949

    write_to_log_file_n('g_update_stmt is '||g_update_stmt);

Line: 2954

  write_to_log_file_n('Error in make_update_into_fact '||sqlerrm||' '||get_time);

Line: 2959

function make_insert_into_fact return boolean is
l_creation_date_flag boolean;

Line: 2961

l_last_update_date_flag boolean;

Line: 2964

    write_to_log_file_n('In make_insert_into_fact');

Line: 2967

    g_insert_stmt:='insert into '||g_fact_name||'(';

Line: 2969

    g_insert_stmt:='insert /*+ PARALLEL ('||g_fact_name||','||g_parallel||')*/ into '||g_fact_name||'(';

Line: 2972

    g_insert_stmt:=g_insert_stmt||' '||g_output_params(i)||',';

Line: 2976

     g_insert_stmt:=g_insert_stmt||' '||g_df_extra_fks(i)||',';

Line: 2982

    g_insert_stmt:=g_insert_stmt||'CREATION_DATE,';

Line: 2987

      g_number_input_params,'LAST_UPDATE_DATE')= false then
    l_last_update_date_flag:=true;

Line: 2989

    g_insert_stmt:=g_insert_stmt||'LAST_UPDATE_DATE,';

Line: 2991

    l_last_update_date_flag:=false;

Line: 2993

  g_insert_stmt:=substr(g_insert_stmt,1,length(g_insert_stmt)-1);

Line: 2994

  g_insert_stmt:=g_insert_stmt||' ) select /*+ORDERED */ ';

Line: 2996

    g_insert_stmt:=g_insert_stmt||' '||g_output_params(i)||',';

Line: 3000

     g_insert_stmt:=g_insert_stmt||' '||g_df_extra_fks(i)||',';

Line: 3004

    g_insert_stmt:=g_insert_stmt||'SYSDATE,';

Line: 3006

  if l_last_update_date_flag then
    g_insert_stmt:=g_insert_stmt||'SYSDATE,';

Line: 3009

  g_insert_stmt:=substr(g_insert_stmt,1,length(g_insert_stmt)-1);

Line: 3010

  g_insert_stmt:=g_insert_stmt||' from '||g_insert_rowid_table||','||g_temp_fact_name||
  ' where '||g_temp_fact_name||'.rowid='||g_insert_rowid_table||'.row_id';

Line: 3014

    write_to_log_file('The statement to insert into the IV');

Line: 3015

    write_to_log_file_n(g_insert_stmt);

Line: 3020

  write_to_log_file_n('Error in make_insert_into_fact '||sqlerrm||' '||get_time);

Line: 3024

function delete_into_fact return boolean is
l_stmt varchar2(5000);

Line: 3031

l_update_type varchar2(400);

Line: 3034

    write_to_log_file_n('In delete_into_fact');

Line: 3036

  l_update_type:=g_update_type;

Line: 3037

  <>
  if l_update_type='ROW-BY-ROW' then
    l_stmt:='select row_id1 from '||g_delete_rowid_table;

Line: 3044

      write_to_log_file('Going to execute '||g_delete_stmt_row||get_time);

Line: 3053

          execute immediate g_delete_stmt_row using l_rowid(i),l_rowid(i);

Line: 3066

        execute immediate g_delete_stmt_row using l_rowid(i),l_rowid(i);

Line: 3070

  elsif l_update_type='MASS' then
    begin
      if g_debug then
        write_to_log_file_n('Going to execute '||g_delete_stmt||get_time);

Line: 3076

      execute immediate g_delete_stmt;

Line: 3083

          write_to_log_file_n('Memory issue with Mass Update. Retrying using ROW_BY_ROW');

Line: 3085

        l_update_type:='ROW-BY-ROW';

Line: 3086

        goto start_delete;

Line: 3092

        goto start_delete;

Line: 3096

        write_to_log_file('Problem stmt '||g_delete_stmt);

Line: 3100

  elsif l_update_type='DELETE-INSERT' then
    l_stmt:='delete '||g_fact_name||' where exists (select 1 from '||g_delete_rowid_table||' where '||
    g_delete_rowid_table||'.row_id1='||g_fact_name||'.rowid)';

Line: 3110

        write_to_log_file('Deleted '||sql%rowcount||' rows'||get_time);

Line: 3120

        write_to_log_file_n('Going to execute '||g_delete_stmt||get_time);

Line: 3123

      execute immediate g_delete_stmt;--this is actually an insert

Line: 3128

      write_to_log_file('Problem stmt '||g_delete_stmt);

Line: 3135

  if make_delete_prot_log=false then --this is the commit
    write_to_log_file_n('make_delete_prot_log returned with error');

Line: 3141

  g_total_delete:=nvl(g_total_delete,0)+l_total_count;

Line: 3143

    write_to_log_file_n('Number of rows updated for delete in the fact '||l_total_count);

Line: 3149

  write_to_log_file_n('Error in delete_into_fact '||sqlerrm||' '||get_time);

Line: 3153

function update_into_fact return boolean is
l_stmt varchar2(5000);

Line: 3160

l_update_type varchar2(400);

Line: 3163

    write_to_log_file_n('In update_into_fact');

Line: 3165

  l_update_type:=g_update_type;

Line: 3166

  <>
  if l_update_type='ROW-BY-ROW' then
    l_stmt:='select row_id1 from '||g_update_rowid_table;

Line: 3173

      write_to_log_file_n('Goint to execute '||g_update_stmt_row||get_time);

Line: 3182

          execute immediate g_update_stmt_row using l_rowid(i),l_rowid(i);

Line: 3195

        execute immediate g_update_stmt_row using l_rowid(i),l_rowid(i);

Line: 3199

  elsif g_update_type='MASS' then
    begin
      if g_debug then
        write_to_log_file('Going to execute '||g_update_stmt);

Line: 3206

      execute immediate g_update_stmt;

Line: 3212

        write_to_log_file_n('Memory issue with Mass Update. Retrying using ROW_BY_ROW');

Line: 3213

        l_update_type:='ROW-BY-ROW';

Line: 3214

        goto start_update;

Line: 3220

        goto start_update;

Line: 3224

        write_to_log_file('Problem stmt '||g_update_stmt);

Line: 3228

  elsif g_update_type='DELETE-INSERT' then
    l_stmt:='delete '||g_fact_name||' where exists (select 1 from '||g_update_rowid_table||' where '||
    g_update_rowid_table||'.row_id1='||g_fact_name||'.rowid)';

Line: 3238

        write_to_log_file('Deleted '||sql%rowcount||' rows');

Line: 3248

        write_to_log_file('Going to execute '||g_update_stmt);

Line: 3251

      execute immediate g_update_stmt;--this is actually an insert

Line: 3256

      write_to_log_file('Problem stmt '||g_update_stmt);

Line: 3263

  if make_update_prot_log=false then --this is the commit
    write_to_log_file_n('make_update_prot_log returned with error');

Line: 3269

  g_total_update:=nvl(g_total_update,0)+l_total_count;

Line: 3271

    write_to_log_file_n('Number of rows updated in the fact '||l_total_count);

Line: 3277

  write_to_log_file_n('Error in update_into_fact '||sqlerrm||' '||get_time);

Line: 3281

function insert_into_fact return boolean is
l_count number;

Line: 3285

    write_to_log_file_n('In insert_into_fact');

Line: 3288

    write_to_log_file('Going to execute '||g_insert_stmt);

Line: 3292

  execute immediate g_insert_stmt;

Line: 3297

  if make_insert_prot_log=false then --this is the commit!!
    write_to_log_file_n('make_insert_prot_log returned with error');

Line: 3303

  g_total_insert:=nvl(g_total_insert,0)+l_count;

Line: 3311

  write_to_log_file_n('Error in insert_into_fact '||sqlerrm||' '||get_time);

Line: 3326

  if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_rowid_table)=false then
    null;

Line: 3329

  if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_rowid_table)=false then
    null;

Line: 3332

  if EDW_OWB_COLLECTION_UTIL.drop_table(g_delete_rowid_table)=false then
    null;

Line: 3346

function update_log_status_0(p_log varchar2) return boolean is
l_stmt varchar2(2000);

Line: 3350

    write_to_log_file_n('In update_ilog_status_2');

Line: 3352

  l_stmt:='update '||p_log||' set status=0 where status=1';

Line: 3363

    write_to_log_file_n('Updated '||sql%rowcount||' rows in '||p_log||' from status 1 to status 0'||get_time);

Line: 3372

function update_ilog_status_2 return boolean is
l_stmt varchar2(2000);

Line: 3376

    write_to_log_file_n('In update_ilog_status_2');

Line: 3378

  l_stmt:='update '||g_ilog||' set status=2 where status=1';

Line: 3386

    write_to_log_file_n('Updated '||sql%rowcount||' rows in '||g_ilog||' from status 1 to status 2'||get_time);

Line: 3397

function update_dlog_status_2 return boolean is
l_stmt varchar2(2000);

Line: 3401

    write_to_log_file_n('In update_dlog_status_2');

Line: 3403

  l_stmt:='update '||g_dlog||' set status=2 where status=1';

Line: 3411

    write_to_log_file_n('Updated '||sql%rowcount||' rows in '||g_dlog||' from status 1 to status 2'||get_time);

Line: 3422

function delete_rowid_table_stmt return boolean is
l_divide number:=2;

Line: 3431

    write_to_log_file_n('In delete_rowid_table_stmt');

Line: 3438

  g_delete_rowid_stmt:='create table '||g_delete_rowid_table||' tablespace '||g_op_table_space;

Line: 3445

    g_delete_rowid_stmt:=g_delete_rowid_stmt||' storage(initial '||g_fact_next_extent/2||' next '||
   (g_fact_next_extent/l_divide)||' pctincrease 0 MAXEXTENTS 2147483645) ';

Line: 3449

    g_delete_rowid_stmt:=g_delete_rowid_stmt||' parallel (degree '||g_parallel||') ';

Line: 3451

  g_delete_rowid_stmt:=g_delete_rowid_stmt||' ';

Line: 3452

  g_delete_rowid_stmt:=g_delete_rowid_stmt||' as select /*+ ORDERED */ ';

Line: 3453

  if g_update_type='DELETE-INSERT' then
    for i in 1..g_number_input_params loop
      if g_fk_flag(i)=false and g_groupby_col_flag(i)=false  then
        g_delete_rowid_stmt:=g_delete_rowid_stmt||'nvl('||g_fact_name||'.'||g_output_params(i)||',0)-nvl('||
          g_temp_fact_name||'.'||g_output_params(i)||',0) '||g_output_params(i)||',';

Line: 3459

        g_delete_rowid_stmt:=g_delete_rowid_stmt||g_temp_fact_name||'.'||g_output_params(i)||' '||
          g_output_params(i)||',';

Line: 3466

        g_delete_rowid_stmt:=g_delete_rowid_stmt||' nvl('||g_temp_fact_name||'.'||g_output_params(i)||',0) '||
        g_output_params(i)||',';

Line: 3471

  if g_update_type='DELETE-INSERT' then
    g_delete_rowid_stmt:=g_delete_rowid_stmt||g_fact_name||'.rowid row_id1,'||
    g_fact_name||'.CREATION_DATE CREATION_DATE from '||g_temp_fact_name||','||g_fact_name||' where ';

Line: 3475

    g_delete_rowid_stmt:=g_delete_rowid_stmt||g_fact_name||'.rowid row_id1 from '||g_temp_fact_name||','||
    g_fact_name||' where ';

Line: 3488

        g_delete_rowid_stmt:=g_delete_rowid_stmt||' '||g_fact_name||'.'||g_output_group_by_cols(i)||'='||
          g_temp_fact_name||'.'||g_output_group_by_cols(i)||' and ';

Line: 3495

            g_delete_rowid_stmt:=g_delete_rowid_stmt||' nvl('||g_fact_name||'.'||g_output_group_by_cols(i)||',0)=nvl('||
            g_temp_fact_name||'.'||g_output_group_by_cols(i)||',0) and ';

Line: 3498

            g_delete_rowid_stmt:=g_delete_rowid_stmt||' nvl('||g_fact_name||'.'||g_output_group_by_cols(i)||
            ',sysdate)=nvl('||g_temp_fact_name||'.'||g_output_group_by_cols(i)||',sysdate) and ';

Line: 3501

            g_delete_rowid_stmt:=g_delete_rowid_stmt||' '||g_fact_name||'.'||g_output_group_by_cols(i)||'='||
            g_temp_fact_name||'.'||g_output_group_by_cols(i)||' and ';

Line: 3505

          g_delete_rowid_stmt:=g_delete_rowid_stmt||' '||g_fact_name||'.'||g_output_group_by_cols(i)||'='||
          g_temp_fact_name||'.'||g_output_group_by_cols(i)||' and ';

Line: 3510

    g_delete_rowid_stmt:=substr(g_delete_rowid_stmt,1,length(g_delete_rowid_stmt)-4);

Line: 3519

function update_rowid_table_stmt return boolean is
l_divide number:=2;

Line: 3527

    write_to_log_file_n('In update_rowid_table_stmt');

Line: 3534

  g_update_rowid_stmt:='create table '||g_update_rowid_table||' tablespace '||g_op_table_space;

Line: 3541

    g_update_rowid_stmt:=g_update_rowid_stmt||' storage(initial '||g_fact_next_extent/2||' next '||
   (g_fact_next_extent/l_divide)||' pctincrease 0 MAXEXTENTS 2147483645) ';

Line: 3545

    g_update_rowid_stmt:=g_update_rowid_stmt||' parallel (degree '||g_parallel||') ';

Line: 3547

  g_update_rowid_stmt:=g_update_rowid_stmt||' ';

Line: 3548

  g_update_rowid_stmt:=g_update_rowid_stmt||' as select /*+ ORDERED */ ';

Line: 3549

  if g_update_type='DELETE-INSERT' then
    for i in 1..g_number_input_params loop
      if g_fk_flag(i)=false and g_groupby_col_flag(i)=false then
        g_update_rowid_stmt:=g_update_rowid_stmt||' nvl('||g_fact_name||'.'||g_output_params(i)||',0)+nvl('||
          g_temp_fact_name||'.'||g_output_params(i)||',0) '||g_output_params(i)||',';

Line: 3554

      else --we need the keys also in this update mode for inserts
        g_update_rowid_stmt:=g_update_rowid_stmt||g_temp_fact_name||'.'||g_output_params(i)||' '||
          g_output_params(i)||',';

Line: 3562

        g_update_rowid_stmt:=g_update_rowid_stmt||' nvl('||g_temp_fact_name||'.'||g_output_params(i)||',0) '||
        g_output_params(i)||',';

Line: 3567

  if g_update_type='DELETE-INSERT' then
    g_update_rowid_stmt:=g_update_rowid_stmt||g_fact_name||'.rowid row_id1,'||
    g_fact_name||'.CREATION_DATE CREATION_DATE from '||g_temp_fact_name||','||g_fact_name||' where ';

Line: 3571

    g_update_rowid_stmt:=g_update_rowid_stmt||g_fact_name||'.rowid row_id1,'||g_temp_fact_name||'.rowid row_id '||
    ' from '||g_temp_fact_name||','||g_fact_name||' where ';

Line: 3584

        g_update_rowid_stmt:=g_update_rowid_stmt||' '||g_fact_name||'.'||g_output_group_by_cols(i)||'='||
        g_temp_fact_name||'.'||g_output_group_by_cols(i)||' and ';

Line: 3591

            g_update_rowid_stmt:=g_update_rowid_stmt||' nvl('||g_fact_name||'.'||g_output_group_by_cols(i)||',0)=nvl('||
            g_temp_fact_name||'.'||g_output_group_by_cols(i)||',0) and ';

Line: 3594

            g_update_rowid_stmt:=g_update_rowid_stmt||' nvl('||g_fact_name||'.'||g_output_group_by_cols(i)||
            ',sysdate)=nvl('||g_temp_fact_name||'.'||g_output_group_by_cols(i)||',sysdate) and ';

Line: 3597

            g_update_rowid_stmt:=g_update_rowid_stmt||' '||g_fact_name||'.'||g_output_group_by_cols(i)||'='||
            g_temp_fact_name||'.'||g_output_group_by_cols(i)||' and ';

Line: 3601

          g_update_rowid_stmt:=g_update_rowid_stmt||' '||g_fact_name||'.'||g_output_group_by_cols(i)||'='||
          g_temp_fact_name||'.'||g_output_group_by_cols(i)||' and ';

Line: 3606

    g_update_rowid_stmt:=substr(g_update_rowid_stmt,1,length(g_update_rowid_stmt)-4);

Line: 3615

function insert_rowid_table_stmt return boolean is
Begin
  if g_debug then
    write_to_log_file_n('In insert_rowid_table_stmt');

Line: 3620

  g_insert_rowid_stmt:='create table '||g_insert_rowid_table||' tablespace '||g_op_table_space;

Line: 3622

    g_insert_rowid_stmt:=g_insert_rowid_stmt||' parallel (degree '||g_parallel||') ';

Line: 3624

  g_insert_rowid_stmt:=g_insert_rowid_stmt||' ';

Line: 3625

  g_insert_rowid_stmt:=g_insert_rowid_stmt||' as select rowid row_id from '||
     g_temp_fact_name||' MINUS select row_id row_id from '||g_update_rowid_table||'  ';

Line: 3629

    write_to_log_file_n('g_insert_rowid_stmt is '||g_insert_rowid_stmt);

Line: 3644

    if EDW_OWB_COLLECTION_UTIL.drop_table(g_delete_rowid_table) = false then
      write_to_log_file_n('Table '||g_delete_rowid_table||' not found for dropping');

Line: 3648

      write_to_log_file_n('Going to execute '||g_delete_rowid_stmt||get_time);

Line: 3651

    execute immediate g_delete_rowid_stmt;

Line: 3653

      write_to_log_file_n('Moved '||sql%rowcount||' rows into the delete rowid table');

Line: 3680

  if create_insert_lock_table=false then
    return false;

Line: 3685

    if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_rowid_table) = false then
      write_to_log_file_n('Table '||g_update_rowid_table||' not found for dropping');

Line: 3689

      l_stmt:='create table '||g_update_rowid_table||'(row_id rowid,row_id1 rowid)'||
      ' tablespace '||g_op_table_space;

Line: 3697

        write_to_log_file_n('Going to execute '||g_update_rowid_stmt||get_time);

Line: 3700

      execute immediate g_update_rowid_stmt;

Line: 3702

        write_to_log_file_n('Moved '||sql%rowcount||' rows into the update rowid table');

Line: 3711

    if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_rowid_table) = false then
      write_to_log_file_n('Table '||g_insert_rowid_table||' not found for dropping');

Line: 3715

      write_to_log_file_n('Going to execute '||g_insert_rowid_stmt);

Line: 3718

    execute immediate g_insert_rowid_stmt;

Line: 3721

      write_to_log_file_n('Moved '||l_count||' rows into the insert rowid table');

Line: 3728

  if l_count=0 then --then there is no need to keep this table as a lock as there are going to be no inserts
    if drop_insert_lock_table=false then
      return false;

Line: 3750

  l_stmt:='create unique index '||g_delete_rowid_table||'u1 on '||g_delete_rowid_table||'(row_id1) '||
  ' tablespace '||g_op_table_space;

Line: 3759

  EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_delete_rowid_table,instr(g_delete_rowid_table,'.')+1,
  length(g_delete_rowid_table)),substr(g_delete_rowid_table,1,instr(g_delete_rowid_table,'.')-1));

Line: 3775

  l_stmt:='create unique index '||g_update_rowid_table||'u1 on '||g_update_rowid_table||'(row_id1) '||
  ' tablespace '||g_op_table_space;

Line: 3784

  EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_insert_rowid_table,instr(g_insert_rowid_table,'.')+1,
  length(g_insert_rowid_table)),substr(g_insert_rowid_table,1,instr(g_insert_rowid_table,'.')-1));

Line: 3786

  EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_update_rowid_table,instr(g_update_rowid_table,'.')+1,
  length(g_update_rowid_table)),substr(g_update_rowid_table,1,instr(g_update_rowid_table,'.')-1));

Line: 3871

  g_insert_lock_table:=g_bis_owner||'.INSERT_LOCK_'||g_fact_id;--should this be a passed down parameter?

Line: 3876

  g_insert_rowid_table:=g_bis_owner||'.'||l_fact_name||'IR';

Line: 3877

  g_update_rowid_table:=g_bis_owner||'.'||l_fact_name||'UR';

Line: 3878

  g_delete_rowid_table:=g_bis_owner||'.'||l_fact_name||'DR';

Line: 3879

  g_insert_prot_log :=g_bis_owner||'.'||l_fact_name||'PI';

Line: 3880

  g_update_prot_log :=g_bis_owner||'.'||l_fact_name||'PU';

Line: 3881

  g_delete_prot_log :=g_bis_owner||'.'||l_fact_name||'PD';

Line: 3887

  g_total_insert:=0;

Line: 3888

  g_total_update:=0;

Line: 3889

  g_total_delete:=0;

Line: 3891

  g_skip_ilog_update:=false;

Line: 3892

  g_skip_dlog_update:=false;

Line: 4020

  l_stmt:=l_stmt||' as select ';

Line: 4109

  l_stmt:=l_stmt||' as select ';

Line: 4181

  l_stmt:=l_stmt||' as select ';

Line: 4237

  l_stmt:=l_stmt||' as select ';

Line: 4276

  if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_prot_log)=false then
    null;

Line: 4279

  if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_prot_log)=false then
    null;

Line: 4291

  if EDW_OWB_COLLECTION_UTIL.drop_table(g_delete_prot_log)=false then
    null;

Line: 4301

function make_insert_prot_log return boolean is
l_stmt varchar2(2000);

Line: 4305

  l_stmt:='create table '||g_insert_prot_log||' tablespace '||g_op_table_space||
  ' storage(initial 4M next 4M pctincrease 0) ';

Line: 4310

  l_stmt:=l_stmt||' as select row_id from '||g_ilog||' where status=1';

Line: 4315

  EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_insert_prot_log,instr(g_insert_prot_log,'.')+1,
  length(g_insert_prot_log)),substr(g_insert_prot_log,1,instr(g_insert_prot_log,'.')-1));

Line: 4320

  write_to_log_file_n('Error in make_insert_prot_log '||g_status_message);

Line: 4324

function make_update_prot_log return boolean is
l_stmt varchar2(2000);

Line: 4328

  l_stmt:='create table '||g_update_prot_log||' tablespace '||g_op_table_space||
  ' storage(initial 4M next 4M pctincrease 0) ';

Line: 4333

  l_stmt:=l_stmt||' as select row_id from '||g_ilog||' where status=1';

Line: 4338

  EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_update_prot_log,instr(g_update_prot_log,'.')+1,
  length(g_update_prot_log)),substr(g_update_prot_log,1,instr(g_update_prot_log,'.')-1));

Line: 4347

function make_delete_prot_log return boolean is
l_stmt varchar2(2000);

Line: 4351

  l_stmt:='create table '||g_delete_prot_log||' tablespace '||g_op_table_space||
  ' storage(initial 4M next 4M pctincrease 0) ';

Line: 4356

  l_stmt:=l_stmt||' as select row_id from '||g_dlog||' where status=1';

Line: 4361

  EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_delete_prot_log,instr(g_delete_prot_log,'.')+1,
  length(g_delete_prot_log)),substr(g_delete_prot_log,1,instr(g_delete_prot_log,'.')-1));

Line: 4385

procedure insert_into_temp_log(p_flag varchar2) is
Begin
  g_number_ins_req_coll:=1;

Line: 4397

  else --delete data
    g_ins_rows_ready(1):=g_ins_rows_processed;

Line: 4402

  if EDW_OWB_COLLECTION_UTIL.insert_temp_log_table(
      g_fact_name,
      'FACT',
      g_conc_id,
      g_ins_instance_name,
      g_ins_request_id_table,
      g_ins_rows_ready,
      g_ins_rows_processed_tab,
      g_ins_rows_collected,
      g_ins_rows_dangling,
      g_ins_rows_duplicate,
      g_ins_rows_error,
      null,
      g_total_insert,
      g_total_update,
      g_total_delete,
      g_number_ins_req_coll) = false then
    g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;

Line: 4427

procedure insert_into_load_progress(p_load_fk number,p_object_name varchar2,p_load_progress varchar2,
  p_start_date date,p_end_date date,p_category varchar2, p_operation varchar2,p_seq_id varchar2,p_flag varchar2) is
Begin
  EDW_OWB_COLLECTION_UTIL.insert_into_load_progress(p_load_fk,p_object_name,g_fact_id,p_load_progress,p_start_date,
  p_end_date,p_category,p_operation,p_seq_id,p_flag,g_fact_id);

Line: 4439

procedure insert_into_load_progress_d(p_load_fk number,p_object_name varchar2,p_load_progress varchar2,
  p_start_date date,p_end_date date,p_category varchar2, p_operation varchar2,p_seq_id varchar2,p_flag varchar2) is
Begin
  if g_debug then
    EDW_OWB_COLLECTION_UTIL.insert_into_load_progress(p_load_fk,p_object_name,g_fact_id,p_load_progress,p_start_date,
    p_end_date,p_category,p_operation,p_seq_id,p_flag,g_fact_id);

Line: 4507

      l_stmt:='select /*+parallel('||g_src_object||','||g_parallel||')*/ count(*) from '||g_src_object;

Line: 4509

      l_stmt:='select count(*) from '||g_src_object;

Line: 4535

  g_skip_ilog_update:=true;

Line: 4538

    ' as select rowid row_id,-10 '||g_src_pk||', 1 status from dual ';

Line: 4541

    ' as select rowid row_id, 1 status from dual ';

Line: 4560

  g_skip_dlog_update:=true;

Line: 4562

  ' as select rowid row_id, 1 status from dual ';

Line: 4594

function load_new_update_data return boolean is
l_stmt varchar2(10000);

Line: 4606

    write_to_log_file_n('In load_new_update_data'||get_time);

Line: 4644

  l_stmt:=l_stmt||' as select B.'||l_col||',B.'||l_round||' from '||g_ilog||' A,'||g_dlog||' B where '||
  ' A.row_id=B.row_id1 and A.status=2';

Line: 4669

  l_stmt:=l_stmt||' as select /*+ordered*/ ';

Line: 4694

    l_stmt:=l_stmt||' as select A.rowid row_id from '||l_table_dlog||' A,'||g_ilog||' B where ';

Line: 4715

    l_stmt:=l_stmt||' as select rowid row_id from '||l_table_dlog||' MINUS select row_id from '||
    l_table_1;

Line: 4731

      l_stmt:='insert into '||g_ilog||'(row_id,status,'||l_round||','||g_src_pk||') select row_id1,0,'||
      l_round||','||l_dlog_col||' from '||l_table_2||','||l_table_dlog||' where '||l_table_2||'.row_id='||
      l_table_dlog||'.rowid';

Line: 4735

      l_stmt:='insert into '||g_ilog||'(row_id,status,'||l_round||') select row_id1,0,'||l_round||
      ' from '||l_table_2||','||l_table_dlog||' where '||l_table_2||'.row_id='||l_table_dlog||'.rowid';

Line: 4745

      write_to_log_file_n('Inserted '||l_count||' rows '||get_time);

Line: 4748

    l_stmt:='insert into '||g_dlog||'(row_id,row_id1,status,'||l_round||','||l_col||') select B.row_id,B.row_id1,'||
    '0,B.'||l_round||',B.'||l_dlog_col||' from '||l_table_2||' A,'||l_table_dlog||' B where '||
    'A.row_id=B.rowid';

Line: 4758

      write_to_log_file_n('Inserted '||l_count||' rows '||get_time);

Line: 4803

  if EDW_OWB_COLLECTION_UTIL.merge_all_prot_tables(g_insert_prot_log,'PI',g_op_table_space,g_bis_owner,
    g_parallel)=false then
    return false;

Line: 4807

  if EDW_OWB_COLLECTION_UTIL.merge_all_prot_tables(g_update_prot_log,'PU',g_op_table_space,g_bis_owner,
    g_parallel)=false then
    return false;

Line: 4811

  if EDW_OWB_COLLECTION_UTIL.merge_all_prot_tables(g_delete_prot_log,'PD',g_op_table_space,g_bis_owner,
    g_parallel)=false then
    return false;

Line: 4816

  if EDW_OWB_COLLECTION_UTIL.check_table(g_insert_prot_log) then
    g_stmt:='update '||g_ilog||' set status=2 where row_id in (select row_id from '||g_insert_prot_log||')';

Line: 4819

  elsif EDW_OWB_COLLECTION_UTIL.check_table(g_update_prot_log) then
    g_stmt:='update '||g_ilog||' set status=2 where row_id in (select row_id from '||g_update_prot_log||')';

Line: 4822

  elsif EDW_OWB_COLLECTION_UTIL.check_table(g_delete_prot_log) then
    g_stmt:='update '||g_dlog||' set status=2 where row_id in (select row_id from '||g_delete_prot_log||')';

Line: 4832

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

Line: 4873

l_skip_ilog_update varchar2(2);

Line: 4874

l_skip_dlog_update varchar2(2);

Line: 4885

  g_stmt:='select '||
  'fact_id,'||
  'mapping_id,'||
  'src_object,'||
  'src_object_id,'||
  'conc_id,'||
  'conc_program_name,'||
  'debug,'||
  'collection_size,'||
  'parallel,'||
  'bis_owner,'||
  'table_owner ,'||
  'full_refresh,'||
  'forall_size,'||
  'update_type,'||
  'fact_dlog,'||
  'load_fk,'||
  'fresh_restart,'||
  'op_table_space,'||
  'bu_src_fact,'||
  'load_mode,'||
  'rollback,'||
  'src_join_nl_percentage,'||
  'max_threads,'||
  'min_job_load_size,'||
  'sleep_time,'||
  'job_status_table,'||
  'hash_area_size,'||
  'sort_area_size,'||
  'trace,'||
  'read_cfig_options,'||
  'ilog_table,'||
  'dlog_table,'||
  'skip_ilog_update,'||
  'skip_dlog_update,'||
  'skip_ilog,'||
  'src_object_ilog,'||
  'src_object_dlog,'||
  'src_snplog_has_pk,'||
  'err_rec_flag,'||
  'err_rec_flag_d,'||
  'dbms_job_id '||
  ' from '||p_table;

Line: 4946

  ,g_update_type
  ,g_fact_dlog
  ,g_load_fk
  ,l_fresh_restart
  ,g_op_table_space
  ,g_bu_src_fact
  ,g_load_mode
  ,g_rollback
  ,g_src_join_nl_percentage
  ,g_max_threads
  ,g_min_job_load_size
  ,g_sleep_time
  ,g_job_status_table
  ,g_hash_area_size
  ,g_sort_area_size
  ,l_trace
  ,l_read_cfig_options
  ,g_ilog_name
  ,g_dlog_name
  ,l_skip_ilog_update
  ,l_skip_dlog_update
  ,l_skip_ilog
  ,g_src_object_ilog
  ,g_src_object_dlog
  ,l_src_snplog_has_pk
  ,l_err_rec_flag
  ,l_err_rec_flag_d
  ,g_dbms_job_id;

Line: 4993

    write_to_log_file('g_update_type='||g_update_type);

Line: 5012

    write_to_log_file('l_skip_ilog_update='||l_skip_ilog_update);

Line: 5013

    write_to_log_file('l_skip_dlog_update='||l_skip_dlog_update);

Line: 5028

  g_skip_ilog_update:=false;

Line: 5029

  g_skip_dlog_update:=false;

Line: 5049

  if l_skip_ilog_update='Y' then
    g_skip_ilog_update:=true;

Line: 5052

  if l_skip_dlog_update='Y' then
    g_skip_dlog_update:=true;

Line: 5067

  g_stmt:='select '||
  'fact_fks,'||
  'higher_level,'||
  'parent_dim,'||
  'parent_level,'||
  'level_prefix,'||
  'level_pk,'||
  'level_pk_key,'||
  'dim_pk_key '||
  ' from '||l_fk_table;

Line: 5111

  g_stmt:='select skip_cols from '||l_skip_table;

Line: 5129

  g_stmt:='select bu_tables,bu_dimensions from '||l_bu_table;

Line: 5180

    g_skip_ilog_update:=false;

Line: 5182

      g_skip_ilog_update:=true;

Line: 5185

    g_skip_dlog_update:=false;

Line: 5187

      g_skip_dlog_update:=true;

Line: 5254

both trying to insert into the fact, there will be duplicate rows.
so keep insert locked. so inserts will be single threaded.
*/
function create_insert_lock_table return boolean is
l_stmt varchar2(2000);

Line: 5270

    write_to_log_file_n('In create_insert_lock_table '||get_time);

Line: 5287

  g_stmt:='create table '||g_insert_lock_table||' tablespace '||g_op_table_space||
  ' as select nvl('||l_my_conc_id||',-1) conc_id, nvl('||l_my_job_id||',-1) dbms_job_id from dual';

Line: 5289

  l_stmt:='select conc_id,dbms_job_id from '||g_insert_lock_table;

Line: 5297

        write_to_log_file_n('Created '||g_insert_lock_table||get_time);

Line: 5324

          if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_lock_table)=false then
            null;

Line: 5335

          if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_lock_table)=false then
            null;

Line: 5350

  write_to_log_file_n('Error in create_insert_lock_table '||g_status_message);

Line: 5354

function drop_insert_lock_table return boolean is
Begin
  if g_debug then
    write_to_log_file_n('In drop_insert_lock_table');

Line: 5359

  if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_lock_table)=false then
    null;

Line: 5365

  write_to_log_file_n('Error in drop_insert_lock_table '||g_status_message);

Line: 5374

  insert_into_load_progress(g_load_fk,g_fact_name,'Pre Fact Load Hook',sysdate,null,'DF',
  'PRE-FACT-HOOK','PREDFHOOK'||g_fact_id||'-'||g_src_object_id,'I');

Line: 5381

  insert_into_load_progress(g_load_fk,null,null,null,sysdate,null,null,'PREDFHOOK'||g_fact_id||'-'||g_src_object_id,
  'U');

Line: 5398

  insert_into_load_progress(g_load_fk,g_fact_name,'Post Fact Load Hook',sysdate,null,'DF',
  'POST-FACT-HOOK','PDFHOOK'||g_fact_id||'-'||g_src_object_id,'I');

Line: 5405

  insert_into_load_progress(g_load_fk,null,null,null,sysdate,null,null,'PDFHOOK'||g_fact_id||'-'||g_src_object_id,
  'U');