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_ALL_COLLECT SQL Statements

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

Line: 117


insert_into_load_progress(g_load_pk,g_object_name,g_object_id,'Pre Coll Hook',sysdate,null,'PRE-LEVEL',
'PRE-COLL-HOOK',10,'I');

Line: 129


insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,10,'U');

Line: 133


insert_into_load_progress(g_load_pk,g_object_name,g_object_id,'Pre Dimension Coll Hook',sysdate,null,'PRE-LEVEL',
'PRE-DIM-COLL-HOOK',11,'I');

Line: 145


insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,11,'U');

Line: 150


insert_into_load_progress(g_load_pk,g_object_name,g_object_id,'Load NA_EDW',sysdate,null,'PRE-LEVEL',
'NAEDW_LOAD',12,'I');

Line: 158


insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,12,'U');

Line: 165


insert_into_load_progress(g_load_pk,g_object_name,g_object_id,'Read Metadata',sysdate,null,'PRE-LEVEL',
'METADATA_READ',13,'I');

Line: 338


    when smart update is false, turn off fk change check. this means when dim is loaded, it will pull in all the levels
    in the select and from clause.
    */
    if g_smart_update=false then
      g_check_fk_change:=false;

Line: 344


        write_to_log_file_n('Smart Update false. Turning OFF fk change check');

Line: 460


insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,13,'U');

Line: 471


insert_into_load_progress_nd(g_load_pk,g_object_name,g_object_id,'Push Down Levels',sysdate,null,'LEVEL',
'DIMENSION','PD10','I');

Line: 490


    g_update_type,
    g_load_pk,
    g_op_table_space,
    g_dim_push_down,
    g_rollback,
    g_thread_type,
    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_stg_join_nl
  ) =false then
  errbuf:='FINISH push_down_all_levels WITH ERROR '||EDW_PUSH_DOWN_DIMS.g_status_message;

Line: 507


  insert_into_load_progress_nd(g_load_pk,null,null,null,null,sysdate,null,null,'PD10','U');

Line: 511


insert_into_load_progress_nd(g_load_pk,null,null,null,null,sysdate,null,null,'PD10','U');

Line: 517


  insert_into_load_progress_nd(g_load_pk,g_object_name,g_object_id,'Dimension Collection',sysdate,null,'DIMENSION',
  'DIMENSION','DC10','I');

Line: 537


    g_update_type,
    g_level_order,
    g_skip_cols,
    g_number_skip_cols,
    g_load_pk,
    g_fresh_restart,
    g_op_table_space,
    g_rollback,
    g_ltc_merge_use_nl,
    g_dim_inc_refresh_derv,
    g_check_fk_change,
    g_ok_switch_update,
    g_stg_join_nl,
    g_thread_type,
    g_max_threads,
    g_min_job_load_size,
    g_sleep_time,
    g_job_status_table,
    g_hash_area_size,
    g_sort_area_size,
    g_trace,
    g_read_cfig_options,
    g_max_fk_density,
    g_analyze_frequency,
    g_parallel_drill_down,
    g_dd_status_table
    );

Line: 564


  insert_into_load_progress_nd(g_load_pk,null,null,null,null,sysdate,null,null,'DC10','U');

Line: 598


insert_into_load_progress(g_load_pk,g_object_name,g_object_id,'Post Dimension Coll Hook',sysdate,null,'POST-LEVEL',
'POST-DIM-COLL-HOOK',14,'I');

Line: 602


 insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,14,'U');

Line: 615


 insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,14,'U');

Line: 830


l_smart_update_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;

Line: 831


l_number_smart_update_cols number;

Line: 905


   l_number_smart_update_cols:=0;

Line: 906


   for k in 1..g_number_smart_update_cols loop
     for j in 1..l_numer_cols loop
       if g_smart_update_cols(k)=l_tgt_cols(j) then
         l_number_smart_update_cols:=l_number_smart_update_cols+1;

Line: 910


         l_smart_update_cols(l_number_smart_update_cols):=l_src_cols(j);

Line: 916


   insert_into_load_progress_nd(g_load_pk,g_level_order(i),g_primary_target(i),'Collect Level',sysdate,null,'LEVEL',
   'LEVEL-LOAD',100+i,'I');

Line: 945


   g_update_type,
   g_mode,
   g_explain_plan_check,
   null,
   g_key_set,
   g_instance_type,
   g_load_pk,
   l_skip_cols,
   l_number_skip_cols,
   g_fresh_restart,
   g_op_table_space,
   l_da_cols,
   l_number_da_cols,
   l_da_table,
   l_pp_table,
   g_master_instance,
   g_rollback,
   g_skip_levels,
   g_number_skip_levels,
   g_smart_update,
   g_fk_use_nl,
   g_fact_smart_update,
   g_auto_dang_table_extn,
   g_auto_dang_recovery,--all the levels get the same flag
   g_create_parent_table_records,
   l_smart_update_cols,
   l_number_smart_update_cols,
   g_check_fk_change,
   g_stg_join_nl,
   g_ok_switch_update,
   g_stg_make_copy_percentage,
   g_hash_area_size,
   g_sort_area_size,
   g_trace,
   g_read_cfig_options,
   g_min_job_load_size,
   g_sleep_time,
   g_thread_type,
   g_max_threads,
   g_job_status_table,
   g_analyze_frequency,
   g_parallel_drill_down,
   g_dd_status_table
   );

Line: 989


   insert_into_load_progress_nd(g_load_pk,null,null,null,null,sysdate,null,null,100+i,'U');

Line: 1026


    g_ins_rows_insert(1),
    g_ins_rows_update(1),
    g_ins_rows_delete(1),
    g_ins_instance_name(1),
    g_ins_request_id_table(1))=false then
    null;

Line: 1047


  l_stmt:='select 1 from EDW_FACTS_MD_V where fact_name=:s';

Line: 1163


insert_into_load_progress(g_load_pk,g_object_name,g_object_id,'Pre Coll Hook',sysdate,null,'FACT',
'PRE-COLL-HOOK',20,'I');

Line: 1175


insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,20,'U');

Line: 1179


insert_into_load_progress(g_load_pk,g_object_name,g_object_id,'Pre Fact Coll Hook',sysdate,null,'FACT',
'PRE-FACT-COLL-HOOK',21,'I');

Line: 1188


  insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,21,'U');

Line: 1192


insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,21,'U');

Line: 1214


   insert_into_load_progress(g_load_pk,g_object_name,g_object_id,'Derived Fact Collect',sysdate,null,'FACT',
  'DERIVED-FACT-COLLECT',22,'I');

Line: 1227


     g_update_type,
     g_skip_cols,
     g_number_skip_cols,
     g_load_pk,
     g_fresh_restart,
     g_op_table_space,
     g_rollback,
     g_stg_join_nl,
     g_thread_type,
     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
     ) = true then
     EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('EDW_DERIVED_FACT_COLLECT.COLLECT_FACT returned with success '||get_time
     ,FND_LOG.LEVEL_PROCEDURE);

Line: 1246


     insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,22,'U');

Line: 1250


     insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,22,'U');

Line: 1267


     select fact_name  into l_audit_name from edw_facts_md_v where fact_name=p_fact_name||'_AU' ;

Line: 1279


     select fact_name  into l_net_change_name from edw_facts_md_v where fact_name=p_fact_name||'_NC' ;

Line: 1310


   insert_into_load_progress_nd(g_load_pk,g_object_name,g_object_id,'Load Fact',sysdate,null,'FACT','FACT','LF','I');

Line: 1338


   g_update_type,--for facts, we need to log into temp log table
   g_mode,
   g_explain_plan_check,
   g_fact_dlog,
   g_key_set,
   g_instance_type,
   g_load_pk,
   g_skip_cols,
   g_number_skip_cols,
   g_fresh_restart,
   g_op_table_space,
   g_da_cols,
   g_number_da_cols,
   null,--g_da_table
   null,--g_pp_table
   g_master_instance,
   g_rollback,
   g_skip_levels,
   g_number_skip_levels,
   g_smart_update,
   g_fk_use_nl,
   g_fact_smart_update,
   g_auto_dang_table_extn,
   g_auto_dang_recovery,
   g_create_parent_table_records,
   g_smart_update_cols,
   g_number_smart_update_cols,
   g_check_fk_change,
   g_stg_join_nl,
   g_ok_switch_update,
   g_stg_make_copy_percentage,
   g_hash_area_size,
   g_sort_area_size,
   g_trace,
   g_read_cfig_options,
   g_min_job_load_size,
   g_sleep_time,
   g_thread_type,
   g_max_threads,
   g_job_status_table,
   g_analyze_frequency,
   false,
   null
   );

Line: 1382


   insert_into_load_progress_nd(g_load_pk,null,null,null,null,sysdate,null,null,'LF','U');

Line: 1406


  insert_into_load_progress(g_load_pk,g_object_name,g_object_id,'Post Fact Coll Hook',sysdate,null,'POST-FACT',
  'POST-FACT',23,'I');

Line: 1415


    insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,23,'U');

Line: 1419


  insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,23,'U');

Line: 1424


  delete_object_log_tables is contained in refresh_all_derived_facts
  In this the snp log of base fact is truncated
  */
  begin
    if g_logical_object_type='FACT' then
      if refresh_all_derived_facts=false then
        errbuf:=g_status_message;

Line: 1467


    'DELETE') = false then
    g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;

Line: 1471


    EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Util.record_coll_progress returned with error for delete',
    FND_LOG.LEVEL_STATEMENT);

Line: 1490


  g_ok_switch_update:=5;--% when to swith to update for ok table

Line: 1523


 g_fact_dlog:=g_bis_owner||'.'||substr(g_object_name,1,26)||'DLG'; --delete log for facts

Line: 1533


 set_g_fact_smart_update;

Line: 1569


procedure set_g_fact_smart_update is
Begin
  g_fact_smart_update:=50;--number of columns below which there is smart update for facts

Line: 1573


    g_fact_smart_update:=150;

Line: 1575


      g_fact_smart_update:=g_fact_smart_update+(g_max_threads/2)*(g_fact_smart_update/2);

Line: 1579


    EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('g_fact_smart_update='||g_fact_smart_update,FND_LOG.LEVEL_STATEMENT);

Line: 1582


  g_fact_smart_update:=50;

Line: 1758


        g_ins_rows_insert(i),
        g_ins_rows_update(i),
        g_ins_rows_delete(i),
        p_message(i),
        l_status,
        p_load_pk)= false then
          g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;

Line: 1816


  l_stmt:='select 1 from edw_pvt_map_properties_md_v map, EDW_FACTS_MD_V tgt, EDW_FACTS_MD_V src '||
  ' where tgt.fact_name=:a and map.Primary_target=tgt.fact_id and map.Primary_source=src.fact_id and rownum=1';

Line: 1842


l_stmt:='select 1 from edw_pvt_map_properties_md_v map, edw_facts_md_v tgt '||
' where map.primary_target=tgt.fact_id and map.primary_source=:a and rownum=1';

Line: 1915


    g_update_type,
    g_fact_dlog,
    g_fresh_restart,
    g_op_table_space,
    l_bu_tables,--dummy
    l_bu_dimensions,--dummy
    l_number_bu_tables,--dummy
    l_bu_src_fact,--dummy
    l_load_mode,--dummy
    g_rollback,
    g_stg_join_nl,
    g_thread_type,
    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_job_queue_processes
    )=false then
    g_status_message:=EDW_DERIVED_FACT_COLLECT.get_status_message;

Line: 1997


  l_stmt:='select dim_name from edw_dimensions_md_v where dim_name=:a or dim_long_name=:b';

Line: 2006


  l_stmt:='select fact_name from edw_facts_md_v where fact_name=:a or fact_longname=:b';

Line: 2030


  l_dlog:=EDW_OWB_COLLECTION_UTIL.get_log_for_table(g_object_name,'Delete Log');

Line: 2040


      ' as select '||g_object_name||'.*,'||g_object_name||'.rowid row_id from '||
      g_object_name||' where 1=2';

Line: 2073


procedure insert_into_load_progress(p_load_fk number,p_object_name varchar2,p_object_id number,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,p_object_id,p_load_progress,p_start_date,
  p_end_date,p_category,p_operation,p_seq_id,p_flag,1);

Line: 2085


procedure insert_into_load_progress_nd(p_load_fk number,p_object_name varchar2,p_object_id number,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=false then
    EDW_OWB_COLLECTION_UTIL.insert_into_load_progress(p_load_fk,p_object_name,p_object_id,p_load_progress,p_start_date,
    p_end_date,p_category,p_operation,p_seq_id,p_flag,1);

Line: 2130


l_bu_tables EDW_OWB_COLLECTION_UTIL.varcharTableType;--before update tables.prop dim change to derv

Line: 2137


l_prot_delete varchar2(400);

Line: 2138


l_prot_update varchar2(400);

Line: 2155


  g_before_update_table:=EDW_SUMMARY_COLLECT.g_before_update_table;--pl/sql table

Line: 2156


  g_number_before_update_table:=EDW_SUMMARY_COLLECT.g_number_before_update_table;

Line: 2158


  if g_before_update_table.count=0 then
    EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('There are no update tables created to refresh the derived facts',
    FND_LOG.LEVEL_STATEMENT);

Line: 2263


      for j in 1..g_number_before_update_table loop
        l_number_bu_tables:=1;

Line: 2265


        l_bu_tables(1):=g_before_update_table(j);

Line: 2267


        l_prot_delete:=g_bis_owner||'.PD'||l_dim_id||'_'||g_dim_derv_map_id(i)||'_'||j;

Line: 2268


        l_prot_update:=g_bis_owner||'.PU'||l_dim_id||'_'||g_dim_derv_map_id(i)||'_'||j;

Line: 2270


        g_tables_to_drop(g_number_tables_to_drop):=l_prot_delete;

Line: 2272


        g_tables_to_drop(g_number_tables_to_drop):=l_prot_update;

Line: 2276


          if EDW_OWB_COLLECTION_UTIL.check_table(l_prot_delete)=false or
          EDW_OWB_COLLECTION_UTIL.check_table(l_prot_update)=false then
            l_derv_bu_map_src_table:=g_bis_owner||'.BUS'||l_dim_id||'_'||g_dim_derv_map_id(i)||'_'||j;

Line: 2280


            l_derv_bu_map_src_table,g_before_update_table(j),l_bu_src_fact)=false then
              return false;

Line: 2289


            l_load_mode:='BU-DELETE';

Line: 2290


            if EDW_OWB_COLLECTION_UTIL.check_table(l_prot_delete) then
              goto loopend;

Line: 2294


            l_load_mode:='BU-UPDATE';

Line: 2296


            if EDW_OWB_COLLECTION_UTIL.check_table(l_prot_update) then
              goto loopend;

Line: 2328


            g_update_type,
            null,--the fact dlog
            l_skip_cols ,
            l_number_skip_cols ,
            l_df_load_pk(i),
            false,--fresh restart
            g_op_table_space,
            l_bu_tables ,--before update tables.prop dim change to derv
            l_bu_dimensions ,
            l_number_bu_tables ,
            l_bu_src_fact ,--what table to look at as the src fact. if null, scan the actual src fact
            l_load_mode,
            g_rollback,
            g_stg_join_nl,
            g_thread_type,
            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
            )=false then
            g_status_message:=EDW_DERIVED_FACT_COLLECT.g_status_message;

Line: 2355


          if l_load_mode='BU-DELETE' then
            if EDW_OWB_COLLECTION_UTIL.create_prot_table(l_prot_delete,g_op_table_space)=false then
              return false;

Line: 2360


            if EDW_OWB_COLLECTION_UTIL.create_prot_table(l_prot_update,g_op_table_space)=false then
              return false;

Line: 2367


      end loop;--for j in 1..g_number_before_update_table loop

Line: 2403


      g_update_type,
      l_skip_cols,
      l_number_skip_cols,
      l_df_load_pk(i),--p_load_pk
      false,--g_fresh_restart
      g_op_table_space,
      g_rollback,
      g_stg_join_nl,
      g_thread_type,
      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
      ) = false then
      g_status_message:=EDW_DERIVED_FACT_COLLECT.get_status_message;

Line: 2435


  for i in 1..g_number_before_update_table loop
    if EDW_OWB_COLLECTION_UTIL.drop_table(g_before_update_table(i))=false then
      null;

Line: 2455


p_derv_before_update_table varchar2,p_bu_src_table out NOCOPY varchar2)
return boolean is
l_stmt varchar2(10000);

Line: 2488


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

Line: 2492


      l_stmt:=l_stmt||p_src_fact||'.* from '||p_derv_before_update_table||','||p_src_fact||' where '||
      p_derv_before_update_table||'.'||l_pk(1)||'='||p_src_fact||'.'||l_fk(1);

Line: 2501


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

Line: 2505


        l_stmt:=l_stmt||p_src_fact||'.rowid row_id from '||p_derv_before_update_table||','||p_src_fact||
        ' where '||p_derv_before_update_table||'.'||l_pk(i)||'='||p_src_fact||'.'||l_fk(i)||' UNION ';

Line: 2525


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

Line: 2564


  l_stmt:='select rel.relation_name,rel.relation_id '||
  'from '||
  'edw_pvt_map_properties_md_v map, '||
  'edw_relations_md_v rel '||
  'where map.mapping_id=:a '||
  'and rel.relation_id=map.primary_source ';

Line: 2781


  if edw_option.get_warehouse_option(null,g_object_id,'UPDATETYPE',l_option_value)=false then
    null;

Line: 2785


    g_update_type:=l_option_value;

Line: 2787


    g_update_type:='MASS';

Line: 2789


  if g_update_type<>'MASS' and g_update_type<>'ROW-BY-ROW' then
    g_update_type:='MASS';

Line: 2792


  EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Update Type='||g_update_type,FND_LOG.LEVEL_STATEMENT);

Line: 2794


  if edw_option.get_warehouse_option(null,g_object_id,'SMARTUPDATE',l_option_value)=false then
    null;

Line: 2798


    g_smart_update:=true;

Line: 2799


    EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Smart Update TRUE',FND_LOG.LEVEL_STATEMENT);

Line: 2801


    g_smart_update:=false;

Line: 2802


    EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Smart Update FALSE',FND_LOG.LEVEL_STATEMENT);

Line: 2804


  g_number_smart_update_cols:=0;

Line: 2805


  if g_smart_update then
    if edw_option.get_option_columns(null,g_object_id,'SMARTUPDATE',g_smart_update_cols,
      g_number_smart_update_cols)=false then
      EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Error in getting columns for smart update '||edw_option.g_status_message,
      FND_LOG.LEVEL_STATEMENT);

Line: 2810


      g_number_smart_update_cols:=0;

Line: 2813


      EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('The smart update columns',FND_LOG.LEVEL_STATEMENT);

Line: 2814


      for i in 1..g_number_smart_update_cols loop
        EDW_OWB_COLLECTION_UTIL.write_to_log_file(g_smart_update_cols(i),FND_LOG.LEVEL_STATEMENT);

Line: 2863


  if edw_option.get_warehouse_option(null,g_object_id,'OK_UPDATE',l_option_value)=false then
    null;

Line: 2868


      g_ok_switch_update:=to_number(l_option_value);

Line: 3042


  g_update_type:=fnd_profile.value('EDW_UPDATE_TYPE');

Line: 3043


  if g_update_type is null then
    g_update_type:='MASS';

Line: 3046


  if g_update_type<>'MASS' and g_update_type<>'ROW-BY-ROW' then
    g_update_type:='MASS';

Line: 3049


  EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Update Type='||g_update_type,FND_LOG.LEVEL_STATEMENT);

Line: 3050


  if fnd_profile.value('EDW_SMART_UPDATE')='Y' then
    g_smart_update:=true;

Line: 3052


    EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Smart Update TRUE',FND_LOG.LEVEL_STATEMENT);

Line: 3054


    g_smart_update:=false;

Line: 3055


    EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Smart Update FALSE',FND_LOG.LEVEL_STATEMENT);

Line: 3057


  g_number_smart_update_cols:=0;

Line: 3058


  if g_smart_update then
    if EDW_OWB_COLLECTION_UTIL.get_item_set_cols(g_smart_update_cols,g_number_smart_update_cols,g_object_name,
      'CHECK_COLUMNS_FOR_UPDATE')=false then
      EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(EDW_OWB_COLLECTION_UTIL.g_status_message,FND_LOG.LEVEL_STATEMENT);

Line: 3064


      EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('The smart update columns',FND_LOG.LEVEL_STATEMENT);

Line: 3065


      for i in 1..g_number_smart_update_cols loop
        EDW_OWB_COLLECTION_UTIL.write_to_log_file(g_smart_update_cols(i),FND_LOG.LEVEL_STATEMENT);

Line: 3104


  l_num:=fnd_profile.value('EDW_OK_UPDATE');

Line: 3107


      g_ok_switch_update:=l_num;

Line: 3187


      l_stmt:='select running_processes from FND_CONCURRENT_QUEUES where concurrent_queue_name=''STANDARD''';