APPS.EDW_PUSH_DOWN_DIMS SQL Statements

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

Line: 21

   p_update_type varchar2,
   p_load_pk number,
   p_op_table_space varchar2,
   p_dim_push_down out NOCOPY boolean,
   p_rollback varchar2,
   p_thread_type varchar2,
   p_max_threads number,
   p_min_job_load_size number,
   p_sleep_time number,
   p_hash_area_size number,
   p_sort_area_size number,
   p_trace boolean,
   p_read_cfig_options boolean,
   p_join_nl_percentage number
   ) return boolean is
l_status number;

Line: 55

  g_update_type:=p_update_type;

Line: 70

  if g_update_type='DELETE-INSERT' then
    g_update_type:='MASS';

Line: 265

    g_level_full_insert,
    g_debug,
    g_parallel,
    g_collection_size,
    g_bis_owner,
    g_table_owner,
    g_full_refresh,
    g_forall_size,
    g_update_type,
    g_load_pk,
    g_op_table_space,
    g_rollback,
    g_max_threads,
    g_min_job_load_size,
    g_sleep_time,
    g_hash_area_size,
    g_sort_area_size,
    g_trace,
    g_read_cfig_options,
    g_join_nl_percentage
    )=false then
    return false;

Line: 422

      if EDW_OWB_COLLECTION_UTIL.delete_conc_program(
        l_temp_conc_short_name,
        l_temp_exe_name,
        l_bis_short_name,
        'SHORT')=false then
        null;

Line: 623

  l_stmt:='select lvl.level_prefix, lvl.level_name||''_LTC'' from edw_levels_md_v lvl where lvl.level_name '||
  'in ('||l_in_stmt||')';

Line: 688

  l_stmt:='select lookup_code,meaning from FND_LOOKUP_VALUES_VL where lookup_type=''EDW_LEVEL_PUSH_DOWN'' '||
  ' and lookup_code in ('||l_in_stmt||')';

Line: 960

      insert_into_load_progress_d(g_load_pk,g_level_order(i),'Push Down Level '||g_jobid_stmt,sysdate,null,'LEVEL',
      'LEVEL-PUSH-DOWN','LPD'||i||g_jobid_stmt,'I');

Line: 964

        insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,'LPD'||i||g_jobid_stmt,'U');

Line: 967

      insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,'LPD'||i||g_jobid_stmt,'U');

Line: 1009

function execute_update_stmt(p_update_stmt varchar2,p_update_stmt_row varchar2,p_update_rowid_table varchar2)
return boolean is
l_stmt varchar2(5000);

Line: 1017

l_update_type varchar2(400);

Line: 1020

    write_to_log_file_n('In execute_update_stmt '||get_time);

Line: 1022

  l_update_type:=g_update_type;

Line: 1023

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

Line: 1036

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

Line: 1048

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

Line: 1052

  elsif l_update_type='MASS' then
    EDW_OWB_COLLECTION_UTIL.alter_session('NO-PARALLEL');

Line: 1056

      execute immediate p_update_stmt;

Line: 1064

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

Line: 1065

        l_update_type:='ROW-BY-ROW';

Line: 1066

        goto start_update;

Line: 1070

      write_to_log_file('Problem stmt '||p_update_stmt);

Line: 1079

    write_to_log_file_n('Number of rows updated '||l_total_count||get_time);

Line: 1102

    if g_skip_ilog_update(l_parent_index)=false then
      l_status:=set_gilog_status(g_level_ilog(l_parent_index),l_parent_index);

Line: 1106

      g_skip_ilog_update(l_parent_index):=false;

Line: 1139

      if g_type_ilog_generation='UPDATE' then
        if update_gilog(g_level_ilog(l_parent_index))=false then
          write_to_log_file_n('Error in update_gilog');

Line: 1175

l_update_stmt varchar2(32000);

Line: 1176

l_update_stmt_row varchar2(32000);

Line: 1177

l_insert_stmt varchar2(32000);

Line: 1241

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

Line: 1398

  if g_level_full_insert(l_child_index) then
    --only insert
    if g_debug then
      write_to_log_file_n('Full Insert');

Line: 1403

    l_stmt:='insert into '||p_child_level||'('||l_user_pk||','||l_pk;

Line: 1410

    l_stmt:=l_stmt||',NAME,CREATION_DATE,LAST_UPDATE_DATE,PUSHED_DOWN_FLAG) select ';

Line: 1455

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

Line: 1463

    l_opcode_table:=g_update_rowid(l_child_index)||'O';

Line: 1468

    l_stmt:=l_stmt||'  as select ';

Line: 1489

        write_to_log_file_n('Update needed for child level');

Line: 1491

      l_stmt:='create table '||g_update_rowid(l_child_index)||' tablespace '||g_op_table_space;

Line: 1495

      l_stmt:=l_stmt||'  as select ';

Line: 1534

      if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_rowid(l_child_index))=false then
        null;

Line: 1544

      l_stmt:='create unique index '||g_update_rowid(l_child_index)||'u on '||
      g_update_rowid(l_child_index)||'(row_id) tablespace '||g_op_table_space;

Line: 1553

      l_table:=g_update_rowid(l_child_index);

Line: 1557

      l_update_stmt:='update /*+ORDERED USE_NL('||p_child_level||')*/ '||p_child_level||' set '||
      '(NAME,LAST_UPDATE_DATE,PUSHED_DOWN_FLAG';

Line: 1559

      l_update_stmt_row:='update '||p_child_level||' set (NAME,LAST_UPDATE_DATE,PUSHED_DOWN_FLAG';

Line: 1561

        l_update_stmt:=l_update_stmt||','||g_final_fk(l_final(i));

Line: 1562

        l_update_stmt_row:=l_update_stmt_row||','||g_final_fk(l_final(i));

Line: 1565

        l_update_stmt:=l_update_stmt||','||l_other_fks(i);

Line: 1566

        l_update_stmt_row:=l_update_stmt_row||','||l_other_fks(i);

Line: 1568

      l_update_stmt:=l_update_stmt||')=(select NAME,SYSDATE,''Y''';

Line: 1569

      l_update_stmt_row:=l_update_stmt_row||')=(select NAME,SYSDATE,''Y''';

Line: 1571

        l_update_stmt:=l_update_stmt||','||g_final_fk(l_final(i));

Line: 1572

        l_update_stmt_row:=l_update_stmt_row||','||g_final_fk(l_final(i));

Line: 1575

        l_update_stmt:=l_update_stmt||','||l_other_fks(i);

Line: 1576

        l_update_stmt_row:=l_update_stmt_row||','||l_other_fks(i);

Line: 1578

      l_update_stmt:=l_update_stmt||' from '||g_update_rowid(l_child_index)||' where '||
      g_update_rowid(l_child_index)||'.row_id='||p_child_level||'.rowid) where '||p_child_level||'.rowid in '||
      '(select row_id from '||g_update_rowid(l_child_index)||')';

Line: 1581

      l_update_stmt_row:=l_update_stmt_row||' from '||g_update_rowid(l_child_index)||' where '||
      g_update_rowid(l_child_index)||'.row_id=:a) where '||p_child_level||'.rowid=:b';

Line: 1585

        write_to_log_file_n('MASS Update stmt '||l_update_stmt);

Line: 1586

        write_to_log_file_n('ROW-BY-ROW Update stmt '||l_update_stmt_row);

Line: 1588

      if execute_update_stmt(l_update_stmt,l_update_stmt_row,g_update_rowid(l_child_index))=false then
        return false;

Line: 1591

      if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_rowid(l_child_index))=false then
        null;

Line: 1597

        write_to_log_file_n('Insert needed for child level');

Line: 1599

      l_insert_stmt:='insert into '||p_child_level||'('||l_user_pk||','||l_pk;

Line: 1601

        l_insert_stmt:=l_insert_stmt||','||g_final_fk(l_final(i));

Line: 1604

        l_insert_stmt:=l_insert_stmt||','||l_other_fks(i);

Line: 1606

      l_insert_stmt:=l_insert_stmt||',NAME,CREATION_DATE,LAST_UPDATE_DATE,PUSHED_DOWN_FLAG) select ';

Line: 1608

        l_insert_stmt:=l_insert_stmt||' /*+parallel(A,'||g_parallel||')*/ ';

Line: 1610

      l_insert_stmt:=l_insert_stmt||'A.'||l_parent_user_pk||'||''-'||g_level_prefix(l_parent_index)||''''||
      ','||g_level_seq(l_child_index)||'.NEXTVAL';

Line: 1614

          l_insert_stmt:=l_insert_stmt||',A.'||g_final_pk_value(l_final(i));

Line: 1618

          l_insert_stmt:=l_insert_stmt||','||g_ltc_copy(l_level_copy_index(l_index))||'.'||g_final_next_pk(l_final(i));

Line: 1623

          l_insert_stmt:=l_insert_stmt||','||l_diamond_fk_table||'.'||l_other_fks(i);

Line: 1625

          l_insert_stmt:=l_insert_stmt||',0 ';

Line: 1628

      l_insert_stmt:=l_insert_stmt||','''||g_level_display_prefix(l_child_index)||
      '(''||A.NAME||'')'',SYSDATE,SYSDATE,''Y'' from '||l_opcode_table||','||p_parent_level||' A ';

Line: 1631

        l_insert_stmt:=l_insert_stmt||','||g_ltc_copy(l_level_copy_index(i));

Line: 1634

        l_insert_stmt:=l_insert_stmt||','||l_diamond_fk_table;

Line: 1636

      l_insert_stmt:=l_insert_stmt||' where '||l_opcode_table||'.row_id=A.rowid and '||
      l_opcode_table||'.status=0 and ';

Line: 1639

        l_insert_stmt:=l_insert_stmt||'A.rowid='||g_ltc_copy(l_level_copy_index(i))||'.row_id and ';

Line: 1642

        l_insert_stmt:=l_insert_stmt||'A.rowid='||l_diamond_fk_table||'.row_id and ';

Line: 1644

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

Line: 1646

        write_to_log_file_n('Going to execute Insert stmt '||l_insert_stmt||get_time);

Line: 1650

      execute immediate l_insert_stmt;

Line: 1652

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

Line: 1683

function update_gilog(p_ilog varchar2) return boolean is
l_stmt varchar2(2000);

Line: 1687

   write_to_log_file_n('In update_gilog for '||p_ilog||get_time);

Line: 1689

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

Line: 1694

   write_to_log_file_n('Updated '||sql%rowcount||' rows from 1 to 2 for '||p_ilog||get_time);

Line: 1704

 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(p_ilog in out NOCOPY varchar2,p_index number) return number is
l_stmt varchar2(10000);

Line: 1720

  if g_type_ilog_generation='UPDATE' then
    if EDW_OWB_COLLECTION_UTIL.does_table_have_data(p_ilog,'status=1')=2 then
      return 2;

Line: 1725

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

Line: 1727

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

Line: 1740

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

Line: 1756

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

Line: 1760

        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 '||p_ilog||' order by status) abc ';

Line: 1765

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

Line: 1768

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

Line: 1832

    g_skip_ilog_update(i):=false;

Line: 1847

    g_insert_rowid(i):=g_bis_owner||'.'||l_name||'IR';

Line: 1848

    g_update_rowid(i):=g_bis_owner||'.'||l_name||'UR';

Line: 1953

    g_level_full_insert(i):=false;--default

Line: 1955

      g_level_full_insert(i):=true;

Line: 1962

      g_level_full_insert(i):=true;

Line: 2055

  l_stmt:='select seq.sequence_name, rel.name from edw_tables_md_v rel, edw_pvt_sequences_md_v seq, '||
  'edw_pvt_map_properties_md_v map, edw_pvt_map_sources_md_v ru where rel.name in ('||l_in_stmt||') '||
  'and map.primary_target=rel.elementid and ru.mapping_id=map.mapping_id '||
  'and ru.source_id=seq.sequence_id  ';

Line: 2142

    if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_rowid(i))=false then
      null;

Line: 2145

    if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_rowid(i))=false then
      null;

Line: 2190

    l_stmt:=l_stmt||'  as select chartorowid(M_ROW$$) row_id ';

Line: 2260

        l_stmt:='insert into '||g_level_ilog(l_index)||'(row_id,'||g_level_pk(l_index)||',status) select ';

Line: 2266

        l_stmt:='insert into '||g_level_ilog(l_index)||'(row_id, status) select ';

Line: 2275

        l_stmt:='insert into '||g_level_ilog(l_index)||'(row_id,'||g_level_pk(l_index)||',status) select ';

Line: 2282

        l_stmt:='insert into '||g_level_ilog(l_index)||'(row_id, status) select ';

Line: 2312

      l_stmt:=l_stmt||'  as select /*+ordered*/ A.rowid row_id';

Line: 2320

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

Line: 2351

    l_stmt:=l_stmt||' as select ';

Line: 2363

    'MINUS select ';

Line: 2379

      l_stmt:='insert into '||g_level_ilog(l_index)||'(row_id,'||g_level_pk(l_index)||',status) select '||
      'row_id,'||g_level_pk(l_index)||',status from '||l_level_ilog_el;

Line: 2382

      l_stmt:='insert into '||g_level_ilog(l_index)||'(row_id, status) select '||
      'row_id,status from '||l_level_ilog_el;

Line: 2451

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_level_id,p_load_progress,
    p_start_date,p_end_date,p_category,p_operation,p_seq_id,p_flag,g_level_id);

Line: 2569

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

Line: 2602

  l_stmt:='select parent_level.level_name||''_LTC'',child_level.level_name||''_LTC'', hier.hier_name '||
  'from  '||
  'edw_pvt_level_relation_md_v lvl_rel,  '||
  'edw_hierarchies_md_v hier,  '||
  'edw_dimensions_md_v dim,  '||
  'edw_levels_md_v child_level,  '||
  'edw_levels_md_v parent_level  '||
  'where  '||
  'dim.dim_name=:a '||
  'and hier.dim_id=dim.dim_id '||
  'and lvl_rel.hierarchy_id=hier.hier_id '||
  'and child_level.level_id=lvl_rel.child_level_id '||
  'and parent_level.level_id=lvl_rel.parent_level_id';

Line: 2924

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

Line: 2962

  l_stmt:=l_stmt||'  as select max(row_num) row_num,'||p_child_fk_order(p_number_level_order)||' from '||
  l_diamond_table||' group by '||p_child_fk_order(p_number_level_order);

Line: 2980

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

Line: 3111

        g_skip_ilog_update(i):=true;

Line: 3145

l_level_full_insert varchar2(10);

Line: 3151

  g_stmt:='select '||
  'dim_id'||
  ',debug'||
  ',parallel'||
  ',collection_size'||
  ',bis_owner'||
  ',table_owner'||
  ',full_refresh'||
  ',forall_size'||
  ',update_type'||
  ',load_pk'||
  ',op_table_space'||
  ',rollback'||
  ',max_threads'||
  ',min_job_load_size'||
  ',sleep_time'||
  ',hash_area_size'||
  ',sort_area_size'||
  ',trace'||
  ',read_cfig_options'||
  ',join_nl_percentage'||
  ' from '||p_input_table;

Line: 3186

  ,g_update_type
  ,g_load_pk
  ,g_op_table_space
  ,g_rollback
  ,g_max_threads
  ,g_min_job_load_size
  ,g_sleep_time
  ,g_hash_area_size
  ,g_sort_area_size
  ,l_trace
  ,l_read_cfig_options
  ,g_join_nl_percentage;

Line: 3207

    write_to_log_file('g_update_type='||g_update_type);

Line: 3236

  g_stmt:='select '||
  'levels'||
  ',child_level_number'||
  ',level_order'||
  ',level_snapshot_logs'||
  ',level_ilog'||
  ',level_consider'||
  ',level_full_insert '||
  ' from '||l_level_table||' order by level_number';

Line: 3258

    ,l_level_full_insert;

Line: 3261

    g_level_full_insert(g_number_levels):=false;

Line: 3265

    if l_level_full_insert='Y' then
      g_level_full_insert(g_number_levels):=true;

Line: 3282

      if g_level_full_insert(i) then
        write_to_log_file('g_level_full_insert('||i||') TRUE');

Line: 3285

        write_to_log_file('g_level_full_insert('||i||') FALSE');

Line: 3290

  g_stmt:='select '||
  'child_levels'||
  ',child_fk'||
  ',parent_pk from '||l_level_child_table||' order by run_number';