APPS.EDW_OWB_COLLECTION_UTIL SQL Statements

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

Line: 48

select
	b.mapping_id,
	c.target_usage_id,
	a.dim_id,
    ltc.name,
    ltc.elementid
from
    edw_dimensions_md_v a,
    edw_tables_md_v ltc,
    edw_pvt_map_properties_md_v b,
    edw_pvt_map_targets_md_v c
where
    a.dim_name =p_dim_name
and b.primary_target=a.dim_id
and c.mapping_id=b.mapping_id
and c.target_id=a.dim_id
and ltc.elementid=b.primary_source;

Line: 67

select distinct
    child_relation.name,
    parent_relation.name,
    child_fk_item.column_name,
    parent_pk_item.column_name,
    nvl(parent_map.mapping_id,0),
    nvl(parent_map.primary_source,0),
    nvl(parent_map.primary_target,0)
from
    edw_levels_md_v lvl_child,
    edw_levels_md_v lvl_parent,
    edw_foreign_keys_md_v child_fk,
    edw_pvt_key_columns_md_v child_fk_set_usage,
    edw_pvt_columns_md_v     child_fk_item,
    edw_unique_keys_md_v  parent_pk,
    edw_pvt_key_columns_md_v parent_pk_set_usage,
    edw_pvt_columns_md_v           parent_pk_item,
    edw_tables_md_v child_relation,
    edw_tables_md_v parent_relation,
    edw_pvt_level_relation_md_v lvl_rel,
    edw_hierarchies_md_v hier,
    edw_pvt_map_properties_md_v parent_map
where
    hier.dim_id=p_dim_id
and lvl_rel.hierarchy_id=hier.hier_id
and lvl_child.level_id=lvl_rel.child_level_id
and lvl_parent.level_id=lvl_rel.parent_level_id
and child_fk.entity_id=child_relation.elementid
and child_fk.key_id=parent_pk.key_id
and child_fk_set_usage.key_id=child_fk.foreign_key_id
and child_fk_item.column_id=child_fk_set_usage.column_id
and child_fk_item.parent_object_id=child_relation.elementid
and parent_pk.entity_id=parent_relation.elementid
and parent_pk_set_usage.key_id=parent_pk.key_id
and parent_pk_item.column_id=parent_pk_set_usage.column_id
and parent_pk_item.parent_object_id=parent_relation.elementid
and parent_relation.name=lvl_parent.level_name||'_LTC'
and child_relation.name=lvl_child.level_name||'_LTC'
and parent_map.primary_target(+)=parent_relation.elementid;

Line: 109

select distinct
    child_relation.name,
    null,
    null,
    parent_pk_item.column_name,--actually child pk
    parent_map.mapping_id, --actually child map
    nvl(parent_map.primary_source,0),
    nvl(parent_map.primary_target,0)
from
    edw_levels_md_v lvl_child,
    edw_unique_keys_md_v  parent_pk,
    edw_pvt_key_columns_md_v parent_pk_set_usage,
    edw_pvt_columns_md_v           parent_pk_item,
    edw_tables_md_v child_relation,
    edw_pvt_level_relation_md_v lvl_rel,
    edw_hierarchies_md_v hier,
    edw_pvt_map_properties_md_v parent_map
where
    hier.dim_id=p_dim_id
and lvl_rel.hierarchy_id=hier.hier_id
and lvl_child.level_id=lvl_rel.child_level_id
and parent_pk.entity_id=child_relation.elementid
and parent_pk_set_usage.key_id=parent_pk.key_id
and parent_pk_item.column_id=parent_pk_set_usage.column_id
and parent_pk_item.parent_object_id=child_relation.elementid
and child_relation.name=lvl_child.level_name||'_LTC'
and parent_map.primary_target(+)=child_relation.elementid;

Line: 140

select
    upper(dim_item.column_name),
    upper(relation.name),
    upper(level_item.column_name)
from
    edw_pvt_map_properties_md_v map_properties,
    edw_pvt_map_sources_md_v map_sources,
    (select * from edw_pvt_map_columns_md_v where mapping_id=p_mapping_id) map_columns,
    edw_tables_md_v relation,
    edw_pvt_columns_md_v	dim_item,
    edw_pvt_columns_md_v	level_item
where
  map_properties.mapping_id=p_mapping_id
  and map_sources.mapping_id=map_properties.mapping_id
  and map_columns.Target_column_id=dim_item.column_id
  and map_columns.source_column_id=level_item.column_id
  and relation.elementid=map_sources.source_id
  and map_columns.Source_usage_id=map_sources.Source_usage_id;

Line: 161

select
    lvl.level_prefix||'_'||item.column_name||'_ROWID',
    lvl.level_name||'_LTC',
    'ROWID'
from
    edw_levels_md_v lvl,
    edw_pvt_level_columns_md_v  item
where
    lvl.dim_id=p_dim_id
and item.level_id=lvl.level_id
and item.column_name like '%_PK';

Line: 175

select
    	map.mapping_id,
        nvl(map.primary_source,0),
        nvl(map.primary_target,0)
from
	edw_pvt_map_properties_md_v map,
	edw_relations_md_v	relation
where
	map.primary_target=relation.relation_id
and	relation.relation_name=p_target_relation;

Line: 511

select
  fact_map.mapping_id,
  fact_map.primary_source,
  fact_map.primary_target
  into
  p_fact_map_id,
  p_fact_src,
  p_fact_target
from
  edw_facts_md_v fact,
  edw_pvt_map_properties_md_v fact_map
where
    fact.fact_name=p_fact_name
and fact_map.primary_target=fact.fact_id;

Line: 548

  l_stmt:='select rel.name from edw_tables_md_v rel, edw_facts_md_v fact, edw_foreign_keys_md_v fk, '||
  'edw_unique_keys_md_v pk where fact.fact_name=:a and fk.entity_id=rel.elementid '||
  'and pk.entity_id=fact.fact_id and fk.key_id=pk.key_id  '||
  'and fk.foreign_key_name like ''%_DLOG''';

Line: 556

    l_stmt:='select rel.RELATION_NAME from edw_relations_md_v rel, edw_facts_md_v fact, edw_foreign_keys_md_v fk, '||
    'edw_unique_keys_md_v pk where fact.fact_name=:a and fk.entity_id=rel.RELATION_ID '||
    'and pk.entity_id=fact.fact_id and fk.key_id=pk.key_id  '||
    'and rel.description=:b';

Line: 583

  l_stmt:='select col.column_name from edw_pvt_columns_md_v col, edw_relations_md_v rel where rel.relation_name=:s and '||
            ' col.parent_object_id=rel.relation_id ';

Line: 652

select column_name,data_type,data_length,num_distinct,num_nulls,avg_col_len from all_tab_columns
where table_name=p_table and owner=p_owner;

Line: 672

    write_to_log_file_n('select column_name,data_type,data_length,num_distinct,num_nulls,'||
    'avg_col_len from all_tab_columns where table_name='||l_table||' and owner='||l_owner);

Line: 715

  l_stmt:='select col.column_name from edw_pvt_columns_md_v col, edw_relations_md_v rel, edw_foreign_keys_md_v fk, '||
  'edw_pvt_key_columns_md_v fku where rel.relation_name=:a and fk.entity_id=rel.relation_id and  '||
  'fku.key_id=fk.foreign_key_id and col.column_id=fku.column_id and col.parent_object_id=rel.relation_id';

Line: 746

  l_stmt:='select fk_item.column_name,p_rel.relation_name '||
  'from  '||
  'edw_relations_md_v rel,  '||
  'edw_foreign_keys_md_v fk,  '||
  'edw_pvt_key_columns_md_v fkisu,  '||
  'edw_pvt_columns_md_v fk_item,  '||
  'edw_relations_md_v p_rel,  '||
  'edw_unique_keys_md_v pk  '||
  'where  '||
  'rel.relation_name=:a  '||
  'and fk.entity_id=rel.relation_id '||
  'and fkisu.key_id=fk.foreign_key_id '||
  'and fk_item.column_id=fkisu.column_id '||
  'and fk_item.parent_object_id=rel.relation_id '||
  'and pk.key_id=fk.key_id '||
  'and p_rel.relation_id=pk.entity_id';

Line: 789

 l_stmt:='select table_owner from user_synonyms where table_name=:a';

Line: 794

 l_stmt:='select log_table from all_snapshot_logs where master=:a and log_owner=:b'; --rkumar:bug#4905343

Line: 810

function delete_table(p_table varchar2) return boolean is
l_stmt varchar2(1000);

Line: 813

  l_stmt:='delete '||p_table;

Line: 819

    write_to_log_file_n('Deleted '||sql%rowcount||' records from '||p_table);

Line: 824

  write_to_log_file_n('Exception in delete_table '||sqlerrm);

Line: 1225

    SELECT edw_language_code INTO l_lang
    FROM edw_system_parameters;

Line: 1240

     the below code was there when we thought that we need to translate before inserting into the
     log tables. now its decided that the conversion needs to happen in the form. so the below portion is
     not needed.
   */

   l_lang := get_wh_language;

Line: 1246

  SELECT
    meaning
  INTO
    l_meaning
  FROM fnd_lookup_values
  WHERE lookup_code= p_lookup_code
    AND lookup_type= p_lookup_type
    AND language = l_lang;

Line: 1274

        p_number_insert number,
        p_number_update number,
        p_number_delete number,
        p_collection_message varchar2,
        p_status varchar2,
        p_load_pk number) return boolean is
l_stmt varchar2(10000);

Line: 1282

l_insert_flag boolean;

Line: 1291

  l_stmt:='select 1 from edw_collection_detail_log where load_pk=:a';

Line: 1296

    l_insert_flag:=true;

Line: 1298

    l_insert_flag:=false;

Line: 1300

  if l_insert_flag then
    l_stmt:='insert into edw_collection_detail_log(load_pk,object_name, object_id,object_type,COLLECTION_CONCURRENT_ID, '||
          'COLLECTION_START_DATE,COLLECTION_END_DATE, '||
          ' NO_ACTUALLY_COLLECTED,NO_OF_READY_RECORDS,NO_OF_SUCC_PROCESSED_RECORDS, NUMBER_INSERT,'||
          ' NUMBER_UPDATE,NUMBER_DELETE,COLLECTION_STATUS, COLLECTION_EXCEPTION_MESSAGE,CREATION_DATE,'||
          'CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,LAST_UPDATE_BY) '||
          ' values (:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9,:a10,:a11,:a12,:a13,:a14,:a15,:a16,:a17,:a18,:a19,:a20) ';

Line: 1311

       p_rows_processed||','||p_number_insert||','||p_number_update||','||p_number_delete||','||
       l_collection_status||','||p_collection_message||','||sysdate||','||
       p_conc_program_id||','||sysdate||','||p_conc_program_id||','||p_conc_program_id);

Line: 1316

    p_rows_collected,p_rows_ready,p_rows_processed,p_number_insert,p_number_update,p_number_delete,
    l_collection_status,p_collection_message,sysdate,
    p_conc_program_id,sysdate,p_conc_program_id,p_conc_program_id;

Line: 1319

    write_to_log_file_n('Inserted '||sql%rowcount||' record into edw_collection_detail_log');

Line: 1321

    l_stmt:='update edw_collection_detail_log set COLLECTION_END_DATE=:a1, '||
          ' NO_ACTUALLY_COLLECTED=:a2,NO_OF_READY_RECORDS=:a3,NO_OF_SUCC_PROCESSED_RECORDS=:a4, '||
          'NUMBER_INSERT=:a5,NUMBER_UPDATE=:a6,NUMBER_DELETE=:a7, '||
          'COLLECTION_STATUS=:a8, COLLECTION_EXCEPTION_MESSAGE=:a9,LAST_UPDATE_DATE=:a10,'||
          'LAST_UPDATE_LOGIN=:a11,LAST_UPDATE_BY=:a12 where load_pk=:a13';

Line: 1329

       p_rows_processed||','||p_number_insert||','||p_number_update||','||p_number_delete||','||
       l_collection_status||','||p_collection_message||','||sysdate||','||
       p_conc_program_id||','||p_conc_program_id||','||p_load_pk);

Line: 1334

    p_number_insert,p_number_update,p_number_delete,
    l_collection_status,p_collection_message,sysdate,p_conc_program_id,p_conc_program_id,p_load_pk;

Line: 1336

    write_to_log_file_n('Updated '||sql%rowcount||' record in edw_collection_detail_log');

Line: 1362

    l_stmt:='insert into edw_general_log (OBJECT_NAME,OBJECT_TYPE,LOG_TYPE,CONCURRENT_ID,START_DATE, '||
    ' END_DATE,MESSAGE,STATUS )'||
             ' values (:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8) ';

Line: 1367

    write_to_log_file_n('Inserted '||sql%rowcount||' records into edw_general_log');

Line: 1393

    l_stmt:='insert into edw_error_log (OBJECT_NAME,OBJECT_TYPE,ERROR_TYPE,CONCURRENT_ID,START_DATE, '||
    ' END_DATE,MESSAGE,STATUS ,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY, '||
    ' LAST_UPDATE_LOGIN,RESP_ID)'||
             ' values (:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9,:a10,:a11,:a12,:a13,:a14) ';

Line: 1399

    write_to_log_file_n('Inserted '||sql%rowcount||' records into edw_error_log');

Line: 1411

function insert_temp_log_table(
        p_object_name varchar2,
        p_object_type varchar2,
        p_concurrent_req_id number,
        p_ins_instance_name varcharTableType,
        p_ins_request_id_table numberTableType,
        p_ins_rows_ready numberTableType,
        p_ins_rows_processed numberTableType,
        p_ins_rows_collected numberTableType,
        p_ins_rows_dangling numberTableType,
        p_ins_rows_duplicate numberTableType,
        p_ins_rows_error numberTableType,
        p_number_ready number,
        p_number_insert number,
        p_number_update number,
        p_number_delete number,
        p_number_ins_req_coll number) return boolean is
l_stmt varchar2(10000);

Line: 1431

    write_to_log_file_n('In EDW_OWB_COLLECTION_UTIL.insert_temp_log_table');

Line: 1434

    'number_insert number_update number_delete');

Line: 1438

        ||'   '||p_ins_rows_duplicate(i)||'    '||p_ins_rows_error(i)||' '||p_number_ready||' '||p_number_insert
        ||' '||p_number_update||' '||p_number_delete);

Line: 1443

    l_stmt:='insert into edw_temp_collection_log(object_name, object_type, concurrent_id,instance, request_id, rows_ready, '||
          ' rows_processed, rows_collected, rows_dangling, rows_duplicate, rows_error, status,number_ready,'||
          'number_insert,number_update,number_delete) values '||
          '(:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9,:a10,:a11,:a12,:a13,:a14,:a15,:a16)';

Line: 1454

        p_number_insert,p_number_update,p_number_delete;

Line: 1459

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

Line: 1486

      l_stmt:='select 1 from '||p_table||' where rownum=1';

Line: 1488

      l_stmt:='select 1 from '||p_owner||'.'||p_table||' where rownum=1';

Line: 1526

  l_stmt:='select table_owner from user_synonyms where table_name=:a';

Line: 1531

  l_stmt:='select trg.trigger_name, trg.status from all_triggers trg where trg.table_name=:a '||
  'and trg.owner=:b'; --rkumar:4905343

Line: 1575

    l_stmt:='select df.fact_id from edw_facts_md_v df, edw_facts_md_v fact, '||
    'edw_pvt_map_properties_md_v map  '||
    'where fact.fact_name=:a and map.primary_source=fact.fact_id and df.fact_id=map.primary_target ';

Line: 1606

    l_stmt:='select 1 from edw_facts_md_v df, edw_facts_md_v fact, '||
    'edw_pvt_map_properties_md_v map, edw_attribute_sets_md_v sis  '||
    'where fact.fact_name=:a and map.primary_source=fact.fact_id and df.fact_id=map.primary_target '||
    'and sis.entity_id=df.fact_id and sis.attribute_group_name=''EDW_INC_REFRESH''';

Line: 1642

  l_stmt:='select df.fact_name, df.fact_id, map.mapping_id  from edw_facts_md_v df, edw_facts_md_v fact, '||
  'edw_pvt_map_properties_md_v map  '||
  'where fact.fact_name=:a and map.primary_source=fact.fact_id and df.fact_id=map.primary_target';

Line: 1801

  l_stmt:='select table_owner from user_synonyms where synonym_name=:a';

Line: 1807

    l_stmt:='select table_owner from user_synonyms where table_name=:a'; --rkumar:bug#4905343

Line: 1811

    l_stmt:='select owner from all_tables where table_name=:a '||
            ' and owner=:b'; --rkumar:bug#4905343

Line: 1908

  if p_action='INSERT' then
    l_stmt:='insert into edw_coll_progress_log(object_name,object_type,status,number_processed, '||
        'last_update_date,last_update_login,creation_date,created_by,last_update_by ) values ( '||
        ' :a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9) ';

Line: 1917

  elsif p_action='UPDATE' then
    l_stmt:='update edw_coll_progress_log set status=:c,number_processed=:d, '||
        'last_update_date=:e,last_update_login=:f,last_update_by=:g where object_name=:a and '||
        ' object_type=:b ';

Line: 1926

  elsif p_action='DELETE' then
    l_stmt:='delete edw_coll_progress_log where object_name=:a and object_type=:b ';

Line: 1967

  l_stmt:='select last_update_by from edw_coll_progress_log where object_name=:a and object_type=:b';

Line: 1972

    write_to_log_file_n('last update by found from edw_coll_progress_log is '||l_var);

Line: 1976

    if record_coll_progress(p_object_name,p_object_type,0,'PROCESSING','INSERT') = false then
      return 0;

Line: 1987

    /*if record_coll_progress(p_object_name,p_object_type,0,'PROCESSING','INSERT') = false then
      return 0;

Line: 2005

    if record_coll_progress(p_object_name,p_object_type,null,null,'DELETE') = false then
      return 0;

Line: 2008

    if record_coll_progress(p_object_name,p_object_type,0,'PROCESSING','INSERT') = false then
      return 0;

Line: 2112

    l_stmt:='select 1 from '||p_table||' where rownum=1';

Line: 2114

    l_stmt:='select 1 from '||p_table||' where '||p_where||' and rownum=1';

Line: 2145

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

Line: 2186

  l_stmt:=l_stmt||' as select ';

Line: 2246

    l_stmt:='select 1 from edw_dimensions_md_v dim,edw_attribute_sets_md_v sis where sis.attribute_group_name=:a '||
    ' and sis.entity_id=dim.dim_id and dim.dim_name=:b';

Line: 2332

    l_stmt:='select dim.dim_name from edw_dimensions_md_v dim,edw_attribute_sets_md_v sis where sis.attribute_group_name=:a '||
    ' and sis.entity_id=dim.dim_id and dim.dim_name in ('||l_in_clause||')';

Line: 2404

  l_stmt:='select 1 from '||p_table;

Line: 2448

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

Line: 2493

    l_stmt:='select 1 from edw_facts_md_v fact, edw_attribute_sets_md_v sis '||
     ' where fact.fact_name=:a and sis.entity_id=fact.fact_id and sis.attribute_group_name=''EDW_INC_REFRESH''';

Line: 2531

  l_stmt:='select 1 from edw_dimensions_md_v dim, edw_attribute_sets_md_v sis '||
     ' where dim.dim_name=:a and sis.entity_id=dim.dim_id '||
     'and sis.attribute_group_name=''EDW_LEVEL_PUSH_DOWN''';

Line: 2568

function is_delete_trigger_imp(p_object varchar2, p_owner varchar2) return boolean is
l_stmt varchar2(10000);

Line: 2575

    write_to_log_file_n('In is_delete_trigger_imp');

Line: 2579

  l_stmt:='select triggering_event from all_triggers where table_name =:a and table_owner=:b';

Line: 2586

  if l_type='DELETE' then
    return true;

Line: 2598

function insert_into_coll_progress(
  OBJECT_NAME                              VARCHAR2
, OBJECT_TYPE                              VARCHAR2
, STATUS                                   VARCHAR2
, NUMBER_PROCESSED                         NUMBER ) return boolean is
l_stmt varchar2(4000);

Line: 2607

    write_to_log_file_n('In insert_into_coll_progress');

Line: 2609

  l_stmt:='insert into edw_coll_progress_log(OBJECT_NAME,OBJECT_TYPE,STATUS, '||
          ' NUMBER_PROCESSED,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,CREATION_DATE, '||
          ' CREATED_BY,LAST_UPDATE_BY) values (:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9)';

Line: 2648

  l_stmt:=' select '||
  'sec_relation.relation_name,  '||
  'sec_relation.relation_id,  '||
  'sec_relation_child.relation_name, '||
  'sec_relation_child.relation_id,  '||
  'pk_item.column_name,  '||
  'fk_item.column_name,  '||
  'sec_relation_usage.source_usage_id,  '||
  'sec_relation_usage.source_alias,  '||
  'sec_relation_child_usage.source_usage_id  '||
  'from  '||
  'edw_pvt_map_sources_md_v        sec_relation_usage,  '||
  'edw_relations_md_v               sec_relation,  '||
  'edw_pvt_map_sources_md_v        sec_relation_child_usage,  '||
  'edw_relations_md_v               sec_relation_child,  '||
  'edw_pvt_map_key_usages_md_v      fk_usage,  '||
  'edw_pvt_key_columns_md_v         fk_isu,  '||
  'edw_pvt_columns_md_v                   fk_item,  '||
  'edw_pvt_key_columns_md_v         pk_isu,  '||
  'edw_pvt_columns_md_v                   pk_item  '||
  'where  '||
  ' sec_relation_usage.mapping_id=:a '||
  'and sec_relation.relation_id=sec_relation_usage.source_id '||
  'and sec_relation_child_usage.mapping_id=sec_relation_usage.mapping_id '||
  'and sec_relation_child.relation_id=sec_relation_child_usage.source_id '||
  'and fk_usage.source_usage_id=sec_relation_child_usage.source_usage_id '||
  'and fk_usage.Parent_table_usage_id=sec_relation_usage.source_usage_id '||
  'and fk_usage.mapping_id=sec_relation_usage.mapping_id '||
  'and fk_isu.key_id=fk_usage.foreign_key_id '||
  'and fk_item.column_id=fk_isu.column_id '||
  'and fk_item.parent_object_id=sec_relation_child.relation_id '||
  'and pk_isu.key_id=fk_usage.unique_key_id '||
  'and pk_item.column_id=pk_isu.column_id '||
  'and pk_item.parent_object_id=sec_relation.relation_id '||
  'order by sec_relation_usage.source_usage_id';

Line: 3123

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,p_obj_id number) is
l_stmt varchar2(2000);

Line: 3132

    l_stmt:='update edw_load_progress_log set end_date=:a1,last_update_date=:a2 '||
    'where load_fk=:a3 and seq_id=:a4 and obj_id=:a5';

Line: 3136

    l_stmt:='insert into edw_load_progress_log(load_fk,seq_id,obj_id,object_name,object_id,load_progress,start_date,end_date,'||
    'category,operation,creation_date,last_update_date,created_by,last_update_by,last_update_login) '||
    'values(:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9,:a10,:a11,:a12,:a13,:a14,:a15)';

Line: 3158

  l_stmt:='select item.column_name from edw_attribute_sets_md_v sis, edw_attribute_set_columns_md_v isu, '||
  'edw_pvt_columns_md_v item, '||
  'edw_relations_md_v rel where rel.relation_name=:a and sis.entity_id=rel.relation_id '||
  'and sis.attribute_group_name=:b and isu.attribute_group_id=sis.attribute_group_id and '||
  'item.column_id=isu.column_id and item.parent_object_id=rel.relation_id';

Line: 3191

  l_stmt:='select level_prefix from edw_levels_md_v where level_name=:a';

Line: 3221

    l_stmt:='select src_it.column_name,tgt_it.column_name '||
    'from '||
    'edw_relations_md_v src_rel, '||
    'edw_relations_md_v tgt_rel, '||
    'edw_pvt_map_sources_md_v map_sources, '||
    '(select * from edw_pvt_map_columns_md_v where mapping_id=:1) map_columns, '||
    'edw_pvt_map_properties_md_v map, '||
    'edw_pvt_columns_md_v tgt_it, '||
    'edw_pvt_columns_md_v src_it '||
    'where '||
    'tgt_rel.relation_name=:a '||
    'and src_rel.relation_name=:b '||
    'and map.primary_target=tgt_rel.relation_id '||
    'and map_sources.mapping_id=map.mapping_id '||
    'and map_sources.source_id=src_rel.relation_id '||
    'and map_columns.source_usage_id=map_sources.source_usage_id '||
    'and tgt_it.column_id=map_columns.Target_column_id '||
    'and src_it.column_id=map_columns.source_column_id '||
    'and tgt_it.parent_object_id=tgt_rel.relation_id '||
    'and src_it.parent_object_id=src_rel.relation_id ';

Line: 3247

    l_stmt:='select src_it.column_name,tgt_it.column_name '||
    'from '||
    'edw_relations_md_v src_rel, '||
    'edw_relations_md_v tgt_rel, '||
    'edw_pvt_map_sources_md_v map_sources, '||
    '(select * from edw_pvt_map_columns_md_v where mapping_id=:1) map_columns, '||
    'edw_pvt_map_properties_md_v map, '||
    'edw_pvt_columns_md_v tgt_it, '||
    'edw_pvt_columns_md_v src_it, '||
    'edw_pvt_mappings_md_v model '||
    'where '||
    'tgt_rel.relation_name=:a '||
    'and src_rel.relation_name=:b '||
    'and model.mapping_name=:c '||
    'and map.primary_target=tgt_rel.relation_id '||
    'and model.mapping_id=map.mapping_id '||
    'and map_sources.mapping_id=map.mapping_id '||
    'and map_sources.source_id=src_rel.relation_id '||
    'and map_columns.source_usage_id=map_sources.source_usage_id '||
    'and tgt_it.column_id=map_columns.Target_column_id '||
    'and src_it.column_id=map_columns.source_column_id '||
    'and tgt_it.parent_object_id=tgt_rel.relation_id '||
    'and src_it.parent_object_id=src_rel.relation_id ';

Line: 3315

  l_stmt:='select map.mapping_id,map.primary_target, map.primary_source '||
  'from  edw_pvt_map_properties_md_v map, edw_relations_md_v src, edw_relations_md_v tgt, '||
  'edw_pvt_mappings_md_v model where map.primary_target=tgt.relation_id and '||
  'map.primary_source=src.relation_id and tgt.relation_name=:a and src.relation_name=:b and '||
  'model.mapping_name=:c and model.mapping_id=map.mapping_id';

Line: 3327

  l_stmt:='select '||
	'src.relation_name, '||
	'src_col.column_name, '||
	'tgt.relation_name, '||
	'tgt_col.column_name '||
	'from '||
    'edw_relations_md_v src, '||
    'edw_relations_md_v tgt, '||
    'edw_pvt_columns_md_v src_col, '||
    'edw_pvt_columns_md_v tgt_col, '||
    'edw_pvt_map_properties_md_v map, '||
    'edw_pvt_map_sources_md_v map_source, '||
    '(select * from edw_pvt_map_columns_md_v where mapping_id=:1) map_columns '||
    'where '||
    'map.mapping_id=:a '||
    'and map_source.mapping_id=map.mapping_id '||
    'and map_source.source_id=src.relation_id '||
    'and map_columns.source_usage_id=map_source.source_usage_id '||
    'and map.primary_target=tgt.relation_id '||
    'and map.primary_source=src.relation_id '||
    'and src_col.column_id=map_columns.Source_column_id '||
    'and tgt_col.column_id=map_columns.target_column_id '||
    'and tgt_col.parent_object_id=tgt.relation_id '||
    'and src_col.parent_object_id=src.relation_id ';

Line: 3397

  l_stmt:='select 1 from edw_dimensions_md_v dim, edw_attribute_sets_md_v sis '||
        ' where dim.dim_name=:a and sis.entity_id=dim.dim_id and sis.attribute_group_name=:b ';

Line: 3426

  l_stmt:='select index_name, column_name,column_position from all_ind_columns where table_name=:a and TABLE_OWNER=:b';

Line: 3504

  l_stmt:='select relation_id from edw_relations_md_v where relation_name=:a';

Line: 3521

  l_stmt:='select dim_id from edw_dimensions_md_v where dim_name=:a';

Line: 3549

    l_stmt:='select last_analyzed from all_tables where table_name=:a and owner=:b';

Line: 3552

      l_stmt:='select table_owner from user_synonyms where table_name=:a';

Line: 3556

     l_stmt:='select last_analyzed from all_tables where table_name=:a and owner=:b';--rkumar:bug#4905343

Line: 3582

  l_stmt:='select ';

Line: 3630

  l_stmt:='select tablespace_name,initial_extent,next_extent,pct_free,pct_used,pct_increase,max_extents,'||
  'avg_row_len from '||
  'all_tables where table_name=:a and owner=:b';

Line: 3652

  l_stmt:='select next_extent from all_tables where table_name=:a and owner=:b';

Line: 3672

  l_stmt:='select '||
  'pk_item.column_name  '||
  'from '||
  'edw_relations_md_v rel ,  '||
  'edw_unique_keys_md_v pk,  '||
  'edw_pvt_key_columns_md_v isu,   '||
  'edw_pvt_columns_md_v pk_item '||
  'where  '||
  'rel.relation_name=:a '||
  'and pk.entity_id=rel.relation_id '||
  'and pk.primarykey <> 1  '||
  'and isu.key_id=pk.key_id '||
  'and pk_item.column_id=isu.column_id '||
  'and pk_item.parent_object_id=rel.relation_id '||
  'and pk_item.data_type<>''NUMBER''';

Line: 3728

    l_stmt:='select 1 from all_tab_columns where table_name=:a and column_name=:b and owner=:c';

Line: 3734

    l_stmt:='select 1 from all_tab_columns,user_synonyms syn where all_tab_columns.table_name=:a '||
    'and column_name=:b and syn.table_name=all_tab_columns.table_name and syn.table_owner=all_tab_columns.owner';

Line: 3759

  l_stmt:='select default_tablespace from dba_users where username=:a';

Line: 3790

    l_stmt:='select 1 '||
    'from all_indexes a,all_ind_columns b '||
    'where '||
    'a.index_name=b.index_name '||
    'and a.owner=b.index_owner '||
    'and a.uniqueness=''UNIQUE'' '||
    'and a.table_name=:a '||
    'and a.table_owner=:b '||
    'and a.index_name in  '||
    '(select c.index_name from all_ind_columns c '||
    'where a.index_name=c.index_name '||
    'and a.owner=c.index_owner '||
    'and c.column_name=:c) '||
    'having count(*)=1 '||
    'group by b.index_name ';

Line: 3806

    l_stmt:='select 1 from all_ind_columns where table_name=:a and table_owner=:b and column_name=:c';

Line: 3854

    l_stmt:='select '||
    'func_name,'||
    'category_name,'||
    'column_name,'||
    'column_id,'||
    'column_usage_id ,'||
    'aggregation,'||
    'is_distinct,'||
    'relation_id,'||
    'relation_name,'||
    'relation_usage_id,'||
    'relation_type,'||
    'func_usage_id,'||
    'attribute_position,'||
    'func_default_value '||
    'from   '||
    'edw_pvt_map_func_md_v map '||
    'where '||
    'map.mapping_id=:a ';

Line: 3906

    l_stmt:='select '||
    '''COPY'' func_name, '||
    '''Basic'' category_name, '||
    'col.column_name column_name, '||
    'col.column_id, '||
    'map_col.target_column_usage_id column_usage_id , '||
    'null, '||
    'null, '||
    'rel.relation_id, '||
    'rel.relation_name, '||
    'map_col.target_usage_id relation_usage_id, '||
    'rel.relation_type, '||
    'map_col.func_usage_id, '||
    '1 attribute_position, '||
    'null '||
    'from '||
    '(select * from edw_pvt_map_columns_md_v where mapping_id=:1) map_col, '||
    'edw_relations_md rel, '||
    'edw_pvt_columns_md col, '||
    'edw_pvt_map_targets_md tgt '||
    'where '||
    'rel.relation_id=tgt.target_id '||
    'and tgt.mapping_id=map_col.mapping_id '||
    'and tgt.target_usage_id=map_col.target_usage_id '||
    'and col.column_id=map_col.target_column_id ';

Line: 3957

    l_stmt:='select '||
    '''COPY'' func_name, '||
    '''Basic'' category_name, '||
    'nvl(col.column_name,''NEXTVAL'') column_name, '||
    'col.column_id, '||
    'map_col.source_column_usage_id column_usage_id , '||
    'null, '||
    'null, '||
    'rel.relation_id, '||
    'rel.relation_name, '||
    'map_col.source_usage_id relation_usage_id, '||
    'rel.relation_type, '||
    'map_col.func_usage_id, '||
    '1 attribute_position, '||
    'null '||
    'from '||
    '(select * from edw_pvt_map_columns_md_v where mapping_id=:1) map_col, '||
    'edw_relations_md rel, '||
    'edw_pvt_columns_md col, '||
    'edw_pvt_map_sources_md src '||
    'where '||
    'rel.relation_id=src.source_id '||
    'and src.mapping_id=map_col.mapping_id '||
    'and src.source_usage_id=map_col.source_usage_id '||
    'and col.column_id(+)=map_col.source_column_id ';

Line: 4044

    l_stmt:='select '||
    'distinct map.mapping_id,  '||
    'tgt.fact_id,  '||
    'src.fact_id  '||
    'from  '||
    'edw_pvt_map_sources_md_v ru,  '||
    'edw_dimensions_md_v rel,  '||
    'edw_pvt_map_properties_md_v map,  '||
    'edw_facts_md_v src,  '||
    'edw_facts_md_v tgt,  '||
    'edw_attribute_sets_md_v sis  '||
    'where  '||
    'ru.mapping_id=map.mapping_id '||
    'and rel.dim_id=ru.source_id '||
    'and rel.dim_name=:a '||
    'and map.primary_source=src.fact_id '||
    'and map.primary_target=tgt.fact_id '||
    'and sis.attribute_group_name=''EDW_INC_REFRESH'' '||
    'and sis.entity_id=tgt.fact_id';

Line: 4064

    l_stmt:='select '||
    'distinct map.mapping_id,  '||
    'tgt.fact_id,  '||
    'src.fact_id  '||
    'from  '||
    'edw_pvt_map_sources_md_v ru,  '||
    'edw_dimensions_md_v rel,  '||
    'edw_pvt_map_properties_md_v map,  '||
    'edw_facts_md_v src,  '||
    'edw_facts_md_v tgt '||
    'where  '||
    'ru.mapping_id=map.mapping_id '||
    'and rel.dim_id=ru.source_id '||
    'and rel.dim_name=:a '||
    'and map.primary_source=src.fact_id '||
    'and map.primary_target=tgt.fact_id ';

Line: 4133

  l_stmt:='select lvl.level_prefix||''_''||item.column_name '||
  'from  '||
  'edw_foreign_keys_md_v fk,  '||
  'edw_unique_keys_md_v pk,  '||
  'edw_dimensions_md_v dim,  '||
  'edw_levels_md_v lvl,  '||
  'edw_tables_md_v ltc,  '||
  'edw_unique_keys_md_v ltc_pk,  '||
  'edw_pvt_columns_md_v item,  '||
  'edw_pvt_key_columns_md_v isu '||
  'where  '||
  'fk.entity_id=:a  '||
  'and fk.key_id=pk.key_id '||
  'and dim.dim_id=pk.entity_id '||
  'and dim.dim_id=:b  '||
  'and lvl.dim_id=dim.dim_id '||
  'and lvl.level_prefix=substr(fk.foreign_key_name,instr(fk.foreign_key_name,''_'',-1)+1,length(fk.foreign_key_name)) '||
  'and ltc.name=lvl.level_name||''_LTC'' '||
  'and ltc_pk.entity_id=ltc.elementid '||
  'and isu.key_id=ltc_pk.key_id '||
  'and isu.column_id=item.column_id '||
  'and item.parent_object_id=ltc.elementid '||
  'and item.data_type=''NUMBER''';

Line: 4181

  l_stmt:='select relation_name from edw_relations_md_v where relation_id=:a';

Line: 4198

  l_stmt:='select edw_load_s.nextval from dual';

Line: 4276

    l_stmt:='select pk_item.column_name, fk_item.column_name '||
    'from  '||
    'edw_pvt_map_properties_md_v map, '||
    'edw_pvt_map_sources_md_v map_sources_dim, '||
    'edw_pvt_map_sources_md_v map_sources_fact, '||
    'edw_pvt_map_key_usages_md_v map_key_usage, '||
    'edw_pvt_key_columns_md_v fk_key_column, '||
    'edw_pvt_columns_md_v fk_item, '||
    'edw_pvt_key_columns_md_v pk_key_column, '||
    'edw_pvt_columns_md_v pk_item '||
    'where '||
    'map_sources_dim.source_id=:a '||
    'and map_sources_fact.source_id=:b '||
    'and map.mapping_id=:c     '||
    'and map_sources_dim.mapping_id=map.mapping_id '||
    'and map_sources_fact.mapping_id=map.mapping_id '||
    'and map_key_usage.source_usage_id=map_sources_fact.source_usage_id '||
    'and map_key_usage.mapping_id=map.mapping_id '||
    'and fk_key_column.key_id=map_key_usage.foreign_key_id '||
    'and fk_item.column_id=fk_key_column.column_id '||
    'and fk_item.parent_object_id=map_sources_fact.source_id '||
    'and pk_key_column.key_id=map_key_usage.unique_key_id '||
    'and pk_item.column_id=pk_key_column.column_id '||
    'and pk_item.parent_object_id=map_sources_dim.source_id ';

Line: 4305

    l_stmt:='select pk_item.column_name,fk_item.column_name '||
    'from  '||
    'edw_foreign_keys_md_v fk, '||
    'edw_unique_keys_md_v pk, '||
    'edw_pvt_key_columns_md_v fk_use, '||
    'edw_pvt_key_columns_md_v pk_use, '||
    'edw_pvt_columns_md_v fk_item, '||
    'edw_pvt_columns_md_v pk_item '||
    'where  '||
    '    pk.entity_id=:a  '||
    'and fk.entity_id=:b  '||
    'and fk_use.key_id=fk.foreign_key_id '||
    'and fk_item.column_id=fk_use.column_id '||
    'and fk_item.parent_object_id=fk.entity_id '||
    'and pk.key_id=fk.key_id '||
    'and pk_use.key_id=pk.key_id '||
    'and pk_item.column_id=pk_use.column_id '||
    'and pk_item.parent_object_id=pk.entity_id ';

Line: 4387

  l_stmt:='select mapping_id from edw_pvt_map_properties_md_v where primary_target=:a';

Line: 4448

    l_stmt:='select 1 from edw_relations_md_v rel,edw_attribute_sets_md_v sis where sis.attribute_group_name=:a '||
          ' and sis.entity_id=rel.relation_id and rel.relation_name=:b';

Line: 4451

    l_stmt:='select 1 from edw_attribute_sets_md_v sis where sis.attribute_group_name=:a and sis.entity_id=:b';

Line: 4546

    l_stmt:='select instr(lower(description),''master instance'') from edw_relations_md_v where relation_name=:a';

Line: 4556

    l_stmt:='select rtrim(ltrim(substr(upper(description),instr(lower(description),''master instance'')+16,'||
    'length(description)))) from edw_relations_md_v where relation_name=:a';

Line: 4596

  l_stmt:='insert into EDW_CDI_RESULTS('||
    'OBJECT_NAME '||
   ',OBJECT_ID   '||
   ',OBJECT_TYPE '||
   ',INTERFACE_TABLE '||
   ',INTERFACE_TABLE_ID '||
   ',INTERFACE_TABLE_PK '||
   ',INTERFACE_TABLE_PK_ID '||
   ',INTERFACE_TABLE_FK '||
   ',INTERFACE_TABLE_FK_ID '||
   ',PARENT_TABLE  '||
   ',PARENT_TABLE_ID '||
   ',PARENT_TABLE_PK '||
   ',PARENT_TABLE_PK_ID '||
   ',NUMBER_DANGLING '||
   ',NUMBER_DUPLICATE '||
   ',NUMBER_ERROR   '||
   ',TOTAL_RECORDS   '||
   ',ERROR_TYPE      '||
   ',CREATED_BY      '||
   ',LAST_UPDATE_BY  '||
   ',LAST_UPDATE_LOGIN '||
   ',CREATION_DATE     '||
   ',LAST_UPDATE_DATE) '||
   'values (:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9,:a10,:a11,:a12,:a13,:a14,:a15,:a16,:a17,:a18,:a19,:a20,'||
   ':a21,:a22,:a23)';

Line: 4646

  l_stmt:='insert into edw_cdi_key_values (key_id,table_id,parent_table_id,instance,key_value,number_key_value,'||
  'bad_key) values(:a1,:a2,:a3,:a4,:a5,:a6,:a7)';

Line: 4662

  l_stmt:='select item.column_id from edw_pvt_columns_md_v item, edw_relations_md_v rel where rel.relation_name=:a '||
  'and item.column_name=:b and item.parent_object_id=rel.relation_id and item.parent_object_id=rel.relation_id';

Line: 4679

  l_stmt:='select fk_item.column_name from  edw_relations_md_v rel, edw_foreign_keys_md_v fk, '||
  'edw_pvt_key_columns_md_v fkisu,edw_pvt_columns_md_v fk_item,edw_dimensions_md_v p_rel,edw_unique_keys_md_v pk '||
  'where rel.relation_name=:a and fk.entity_id=rel.relation_id and fkisu.key_id=fk.foreign_key_id '||
  'and fk_item.column_id=fkisu.column_id and fk_item.parent_object_id=rel.relation_id and pk.key_id=fk.key_id and '||
  'p_rel.dim_id=pk.entity_id and p_rel.dim_name=''EDW_INSTANCE_M''';

Line: 4691

    l_stmt:='select item.column_name from edw_pvt_columns_md_v item, edw_relations_md_v rel where '||
    'item.parent_object_id=rel.relation_id and rel.relation_name=:a and item.column_name '||
    'in (''INSTANCE'',''INSTANCE_CODE'') and item.parent_object_id=rel.relation_id';

Line: 4758

  l_stmt:='select 1 from edw_facts_md_v df, edw_facts_md_v fact, '||
           'edw_pvt_map_properties_md_v map, edw_attribute_sets_md_v sis  '||
           'where fact.fact_name=:a and map.primary_source=fact.fact_id and df.fact_id=map.primary_target '||
           'and sis.entity_id=df.fact_id and sis.attribute_group_name=''EDW_CUSTOM_INC_REFRESH''';

Line: 4785

  l_stmt:='select vw.view_name '||
  'from  '||
  'edw_pvt_md_views_v vw,  '||
  'edw_dimensions_md_v dim,  '||
  'edw_foreign_keys_md_v fk,  '||
  'edw_unique_keys_md_v pk  '||
  'where fk.entity_id=vw.view_id '||
  'and pk.entity_id=dim.dim_id '||
  'and fk.key_id=pk.key_id '||
  'and instr(fk.foreign_key_name,''_PK_VIEW'')<>0  '||
  'and dim.dim_name=:a ';

Line: 4817

    l_stmt:='select '||l_pkg||' from dual';

Line: 4839

    l_stmt:='select 1 from '||l_view(i)||'@'||p_db_link||' where rownum=1';

Line: 4866

  l_stmt:='select relation_long_name from edw_relations_md_v where relation_id=:a';

Line: 4964

  l_stmt:='select '||
  'tgt_column.column_name '||
  'from '||
  'edw_pvt_map_properties_md_v map_properties, '||
  'edw_pvt_map_sources_md_v map_sources, '||
  'edw_pvt_map_targets_md_v map_targets, '||
  'edw_tables_md_v src_table, '||
  'edw_relations_md_v tgt_relation, '||
  'edw_unique_keys_md_v pk, '||
  'edw_pvt_key_columns_md_v key_usage, '||
  'edw_pvt_columns_md_v pk_col, '||
  '(select * from edw_pvt_map_columns_md_v where mapping_id=:1) map_columns, '||
  'edw_pvt_columns_md_v tgt_column '||
  'where '||
  '  map_sources.source_id=map_properties.primary_source '||
  'and map_targets.target_id=map_properties.primary_target '||
  'and map_properties.mapping_id=:a '||
  'and map_sources.mapping_id=map_properties.mapping_id '||
  'and src_table.elementid=map_sources.source_id '||
  'and map_targets.mapping_id=map_properties.mapping_id '||
  'and tgt_relation.relation_id=map_targets.target_id '||
  'and pk.entity_id=map_properties.primary_source '||
  'and key_usage.key_id=pk.key_id '||
  'and pk_col.column_id=key_usage.column_id '||
  'and pk_col.parent_object_id=src_table.elementid '||
  'and map_columns.mapping_id=map_properties.mapping_id '||
  'and map_columns.Source_usage_id=map_sources.source_usage_id '||
  'and map_columns.Source_column_id=pk_col.column_id '||
  'and map_columns.Target_column_id=tgt_column.column_id '||
  'and tgt_column.parent_object_id=tgt_relation.relation_id ';

Line: 5026

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

Line: 5038

  l_stmt:='select '||
	'src.relation_name, '||
	'src_col.column_name, '||
	'tgt.relation_name, '||
	'tgt_col.column_name '||
	'from '||
    'edw_relations_md_v src, '||
    'edw_relations_md_v tgt, '||
    'edw_pvt_columns_md_v src_col, '||
    'edw_pvt_columns_md_v tgt_col, '||
    'edw_pvt_map_properties_md_v map, '||
    'edw_pvt_map_sources_md_v map_source, '||
    '(select * from edw_pvt_map_columns_md_v where mapping_id=:1) map_columns '||
    'where '||
    'map.mapping_id=:a '||
    'and map_source.mapping_id=map.mapping_id '||
    'and map_columns.mapping_id=map.mapping_id '||
    'and map_source.source_id=src.relation_id '||
    'and map_columns.source_usage_id=map_source.source_usage_id '||
    'and map.primary_target=tgt.relation_id '||
    'and src_col.column_id=map_columns.Source_column_id '||
    'and tgt_col.column_id=map_columns.target_column_id '||
    'and src_col.parent_object_id=src.relation_id '||
    'and tgt_col.parent_object_id=tgt.relation_id ';

Line: 5089

  l_stmt:='select '||
  'ltc.relation_name, '||
  'fk_item.column_name  '||
  'from  '||
  'edw_pvt_map_sources_md_v        ltc_ru,  '||
  'edw_relations_md_v               ltc,  '||
  'edw_pvt_map_key_usages_md_v      fk_usage, '||
  'edw_pvt_key_columns_md_v         fk_isu,  '||
  'edw_pvt_columns_md_v                   fk_item,  '||
  'edw_pvt_map_properties_md_v        map,  '||
  'edw_relations_md_v               tgt  '||
  'where  '||
  'tgt.relation_name=:a '||
  'and map.primary_target=tgt.relation_id '||
  'and ltc_ru.mapping_id=map.mapping_id '||
  'and ltc.relation_id=ltc_ru.source_id  '||
  'and fk_usage.source_usage_id=ltc_ru.source_usage_id '||
  'and fk_usage.mapping_id=map.mapping_id '||
  'and fk_isu.key_id=fk_usage.foreign_key_id '||
  'and fk_item.column_id=fk_isu.column_id '||
  'and fk_item.parent_object_id=ltc_ru.source_id';

Line: 5137

  l_stmt:='select '||
  'rel.name,  '||
  'fk_item.column_name  '||
  'from  '||
  'edw_tables_md_v rel,  '||
  'edw_foreign_keys_md_v fk, '||
  'edw_pvt_key_columns_md_v fkisu,  '||
  'edw_pvt_columns_md_v fk_item,  '||
  'edw_dimensions_md_v dim,  '||
  'edw_levels_md_v lvl  '||
  'where  '||
  'dim.dim_name=:a  '||
  'and lvl.dim_id=dim.dim_id '||
  'and rel.name=lvl.level_name||''_LTC'' '||
  'and fk.entity_id=rel.elementid '||
  'and fkisu.key_id=fk.foreign_key_id '||
  'and fk_item.column_id=fkisu.column_id '||
  'and fk_item.parent_object_id=rel.elementid';

Line: 5197

    l_stmt:='select release_name from fnd_product_groups';

Line: 5199

    l_stmt:='select release_name from fnd_product_groups@'||l_db_link;

Line: 5226

    l_stmt:='select '||p_col||' from '||p_table||' where rownum=1';

Line: 5258

  g_stmt:='select 1 from all_tab_columns where table_name=:1 and owner=:2 and column_name=:3';

Line: 5345

  l_stmt:='select max(last_update_date) from edw_collection_detail_log where object_name=:a';

Line: 5352

  l_stmt:='select max(last_update_date) from edw_collection_detail_log where object_name=:a '||
  'and collection_status=''ERROR''';

Line: 5401

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

Line: 5408

  l_stmt:='select 1 from edw_facts_md_v where fact_name=:a';

Line: 5415

  l_stmt:='select 1 from edw_tables_md_v where name=:a';

Line: 5435

  l_stmt:='select ltc.name,lstg.name '||
  'from  '||
  'edw_tables_md_v ltc,  '||
  'edw_tables_md_v lstg,  '||
  'edw_pvt_map_properties_md_v map,  '||
  'edw_dimensions_md_v dim,  '||
  'edw_levels_md_v lvl  '||
  'where dim.dim_name=:a  '||
  'and lvl.dim_id=dim.dim_id '||
  'and ltc.name=lvl.level_name||''_LTC'' '||
  'and map.primary_target(+)=ltc.elementid '||
  'and lstg.elementid(+)=map.primary_source';

Line: 5539

  l_stmt:='select ltc.name,ltc.elementid '||
  'from  '||
  'edw_levels_md_v lvl,  '||
  'edw_tables_md_v ltc,  '||
  'edw_dimensions_md_v dim  '||
  'where lvl.dim_id=dim.dim_id '||
  'and ltc.name=lvl.level_name||''_LTC'' '||
  'and dim.dim_name=:a '||
  'and not exists(  '||
  'select 1  from  '||
  'edw_pvt_level_relation_md_v lvl_rel,  '||
  'edw_hierarchies_md_v hier  '||
  'where  '||
  'lvl_rel.hierarchy_id=hier.hier_id '||
  'and hier.dim_id=dim.dim_id '||
  'and lvl_rel.parent_level_id=lvl.level_id) ';

Line: 5590

  l_stmt:='select ltc.name '||
  'from  '||
  'edw_levels_md_v lvl,  '||
  'edw_tables_md_v ltc,  '||
  'edw_dimensions_md_v dim  '||
  'where lvl.dim_id=dim.dim_id '||
  'and ltc.name=lvl.level_name||''_LTC'' '||
  'and dim.dim_name=:a '||
  'and not exists(  '||
  'select 1  from  '||
  'edw_pvt_level_relation_md_v lvl_rel,  '||
  'edw_hierarchies_md_v hier  '||
  'where  '||
  'lvl_rel.hierarchy_id=hier.hier_id '||
  'and hier.dim_id=dim.dim_id '||
  'and lvl_rel.parent_level_id=lvl.level_id) ';

Line: 5627

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

Line: 5638

  'select 1  from  '||
  'edw_pvt_level_relation_md_v lvl_rel,  '||
  'edw_hierarchies_md_v hier  '||
  'where  '||
  'lvl_rel.hierarchy_id=hier.hier_id  '||
  'and hier.dim_id=dim.dim_id  '||
  'and lvl_rel.parent_level_id=lvl.level_id) ';

Line: 5695

  l_stmt:='select profile_option_name from fnd_profile_options'||l_db_link_stmt||' where profile_option_name '||
  ' like '''||p_parent_table_name||'_PS%''';

Line: 5717

      l_stmt:='select fnd_profile.value'||l_db_link_stmt||'('''||l_profile_options(i)||''') from dual';

Line: 5731

      l_stmt:='select B.profile_option_value from fnd_profile_options'||l_db_link_stmt||' A, '||
      'fnd_profile_option_values'||l_db_link_stmt||' B '||
      'where A.profile_option_id=B.profile_option_id '||
      'and A.profile_option_name=:a';

Line: 5758

  l_stmt:='select warehouse_to_instance_link from edw_source_instances where instance_code=:a';

Line: 5781

  l_stmt:='select sysdate from dual@'||p_db_link;

Line: 5854

  l_stmt:='select pk_item.column_name '||
  'from '||
  'edw_unique_keys_md_v pk, '||
  'edw_pvt_key_columns_md_v isu, '||
  'edw_pvt_columns_md_v pk_item '||
  'where '||
  'pk.entity_id=:a '||
  'and pk.primarykey=1 '||
  'and isu.key_id=pk.key_id '||
  'and pk_item.column_id=isu.column_id '||
  'and pk_item.parent_object_id=pk.entity_id';

Line: 5902

  l_stmt:='select '||
  'lvl.level_prefix||''_''||pk_item.column_name '||
  'from  '||
  'edw_tables_md_v rel,  '||
  'edw_unique_keys_md_v pk,  '||
  'edw_pvt_key_columns_md_v isu,  '||
  'edw_pvt_columns_md_v pk_item,  '||
  'edw_levels_md_v lvl '||
  'where  '||
  ' lvl.dim_id=:a '||
  'and rel.name=lvl.level_name||''_LTC'' '||
  'and pk.entity_id=rel.elementid '||
  'and isu.key_id=pk.key_id '||
  'and pk_item.column_id=isu.column_id '||
  'and pk_item.data_type=''NUMBER'' '||
  'and pk_item.parent_object_id=rel.elementid';

Line: 5950

  l_stmt:='select '||
  'lvl.level_prefix||''_NAME'' '||
  'from '||
  'edw_levels_md_v lvl '||
  'where '||
  ' lvl.dim_id=:a ';

Line: 5986

  l_stmt:='select '||
  'sec_relation.sequence_name  '||
  'from   '||
  'edw_pvt_map_sources_md_v  sec_relation_usage,   '||
  'edw_pvt_sequences_md_v  sec_relation,   '||
  'edw_pvt_map_properties_md_v map  '||
  'where   '||
  'map.primary_target=:a '||
  'and  sec_relation_usage.mapping_id=map.mapping_id '||
  'and sec_relation.sequence_id=sec_relation_usage.source_id';

Line: 6015

  l_stmt:='select lookup_code from fnd_common_lookups where lookup_type=:a';

Line: 6047

  l_stmt:='select pk_item.column_name '||
  'from '||
  'edw_unique_keys_md_v pk, '||
  'edw_pvt_key_columns_md_v isu, '||
  'edw_pvt_columns_md_v pk_item '||
  'where '||
  'pk.entity_id=:a '||
  'and isu.key_id=pk.key_id '||
  'and pk_item.column_id=isu.column_id '||
  'and pk_item.parent_object_id=pk.entity_id';

Line: 6093

  l_stmt:='select round(nvl(num_rows,0)) from all_tables where table_name=:a and owner=:b';

Line: 6400

  l_stmt:='select text from user_views where view_name=:a';

Line: 6944

  l_stmt:='select text from edw_pvt_map_properties_md_v where mapping_id=:a and text_type=''Filter''';

Line: 7038

  l_stmt:='select '||
  'parent_ltc.name,  '||
  'parent_ltc.elementid, '||
  'child_ltc.name,  '||
  'child_ltc.elementid, '||
  '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,  '||
  'edw_tables_md_v parent_ltc,  '||
  'edw_tables_md_v child_ltc  '||
  '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  '||
  'and parent_ltc.name=parent_level.LEVEL_NAME||''_LTC'' '||
  'and child_ltc.name=child_level.LEVEL_NAME||''_LTC'' ';

Line: 7087

    l_stmt:='select mapping_id from edw_pvt_map_properties_md_v where primary_target=:a';

Line: 7095

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

Line: 7133

  l_stmt:='select last_analyzed from all_tables where table_name=:1 and owner=:2';

Line: 7174

  p_update_type varchar2,
  p_mode varchar2,
  p_explain_plan_check boolean,
  p_fact_dlog varchar2,
  p_key_set number,
  p_instance_type varchar2,
  p_load_pk number,
  p_skip_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
  p_number_skip_cols number,
  p_fresh_restart boolean,
  p_op_table_space varchar2,
  p_da_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
  p_number_da_cols number,
  p_da_table varchar2,
  p_pp_table varchar2,
  p_master_instance varchar2,
  p_rollback varchar2,
  p_skip_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
  p_number_skip_levels number,
  p_smart_update boolean,
  p_fk_use_nl number,
  p_fact_smart_update number,
  p_auto_dang_table_extn varchar2,
  p_log_dang_keys boolean,
  p_create_parent_table_records boolean,
  p_smart_update_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
  p_number_smart_update_cols number,
  p_check_fk_change boolean,
  p_stg_join_nl_percentage number,
  p_ok_switch_update number,
  p_stg_make_copy_percentage number,
  p_ok_table varchar2,
  p_hash_area_size number,
  p_sort_area_size number,
  p_trace boolean,
  p_read_cfig_options boolean,
  p_job_status_table varchar2,
  p_max_round number,
  p_update_dlog_lookup_table varchar2,
  p_dlog_has_data boolean,
  p_sleep_time number,
  p_parallel_drill_down boolean
  ) return boolean is
l_fact_audit varchar2(2);

Line: 7225

l_smart_update varchar2(2);

Line: 7232

l_smart_update_table varchar2(80);

Line: 7243

  l_smart_update_table:=p_table_name||'_SU';

Line: 7254

  if drop_table(l_smart_update_table)=false then
    null;

Line: 7286

  'update_type varchar2(80),'||
  'p_mode varchar2(80),'||
  'explain_plan_check varchar2(2),'||
  'fact_dlog varchar2(80),'||
  'key_set number,'||
  'instance_type varchar2(80),'||
  'load_pk number,'||
  'fresh_restart varchar2(2),'||
  'op_table_space varchar2(80),'||
  'da_table varchar2(80),'||
  'pp_table varchar2(80),'||
  'master_instance varchar2(80),'||
  'rollback varchar2(80),'||
  'smart_update varchar2(2),'||
  'fk_use_nl number,'||
  'fact_smart_update number,'||
  'auto_dang_table_extn varchar2(80),'||
  'log_dang_keys varchar2(2),'||
  'create_parent_table_records varchar2(2),'||
  'check_fk_change varchar2(2),'||
  'stg_join_nl_percentage number,'||
  'ok_switch_update number,'||
  'stg_make_copy_percentage number,'||
  'ok_table varchar2(80),'||
  'hash_area_size number,'||
  'sort_area_size number,'||
  'trace_mode varchar2(2),'||
  'read_cfig_options varchar2(2),'||
  'job_status_table varchar2(80),'||
  'max_round number,'||
  'update_dlog_lookup_table varchar2(80),'||
  'dlog_has_data varchar2(2),'||
  'total_records number,'||
  'stg_copy_table_flag varchar2(10),'||
  'sleep_time number,'||
  'parallel_drill_down varchar2(10)'||
  ') tablespace '||p_op_table_space;

Line: 7335

  l_smart_update:='N';

Line: 7367

  if p_smart_update then
    l_smart_update:='Y';

Line: 7391

  g_stmt:='insert into '||p_table_name||'('||
  'object_name,'||
  'mapping_id,'||
  'map_type,'||
  'primary_src ,'||
  'primary_target ,'||
  'primary_target_name,'||
  'object_type,'||
  'conc_id ,'||
  'conc_program_name,'||
  'fact_audit,'||
  'net_change,'||
  'fact_audit_name,'||
  'net_change_name,'||
  'fact_audit_is_name,'||
  'net_change_is_name,'||
  'debug,'||
  'duplicate_collect,'||
  'execute_flag,'||
  'request_id ,'||
  'collection_size ,'||
  'parallel ,'||
  'table_owner,'||
  'bis_owner ,'||
  'temp_log,'||
  'forall_size ,'||
  'update_type,'||
  'p_mode,'||
  'explain_plan_check,'||
  'fact_dlog,'||
  'key_set ,'||
  'instance_type,'||
  'load_pk ,'||
  'fresh_restart,'||
  'op_table_space,'||
  'da_table,'||
  'pp_table,'||
  'master_instance,'||
  'rollback,'||
  'smart_update,'||
  'fk_use_nl ,'||
  'fact_smart_update ,'||
  'auto_dang_table_extn,'||
  'log_dang_keys,'||
  'create_parent_table_records,'||
  'check_fk_change,'||
  'stg_join_nl_percentage ,'||
  'ok_switch_update ,'||
  'stg_make_copy_percentage,'||
  'ok_table, '||
  'hash_area_size,'||
  'sort_area_size,'||
  'trace_mode,'||
  'read_cfig_options, '||
  'job_status_table, '||
  'max_round,'||
  'update_dlog_lookup_table,'||
  'dlog_has_data,'||
  'sleep_time,'||
  'parallel_drill_down'||
  ') values ('||
  ':1,:2,:3,:4,:5,:6,:7,:8,:9,:10,'||
  ':11,:12,:13,:14,:15,:16,:17,:18,:19,:20,'||
  ':21,:22,:23,:24,:25,:26,:27,:28,:29,:30,'||
  ':31,:32,:33,:34,:35,:36,:37,:38,:39,:40,'||
  ':41,:42,:43,:44,:45,:46,:47,:48,:49,:50,'||
  ':51,:52,:53,:54,:55,:56,:57,:58,:59)';

Line: 7487

  p_update_type ,
  p_mode ,
  l_explain_plan_check ,
  p_fact_dlog ,
  p_key_set ,
  p_instance_type ,
  p_load_pk ,
  l_fresh_restart ,
  p_op_table_space ,
  p_da_table ,
  p_pp_table ,
  p_master_instance ,
  p_rollback ,
  l_smart_update ,
  p_fk_use_nl ,
  p_fact_smart_update ,
  p_auto_dang_table_extn ,
  l_log_dang_keys ,
  l_create_parent_table_records ,
  l_check_fk_change ,
  p_stg_join_nl_percentage ,
  p_ok_switch_update ,
  p_stg_make_copy_percentage,
  p_ok_table,
  p_hash_area_size,
  p_sort_area_size,
  l_trace,
  l_read_cfig_options,
  p_job_status_table,
  p_max_round,
  p_update_dlog_lookup_table,
  l_dlog_has_data,
  p_sleep_time,
  l_parallel_drill_down;

Line: 7529

    g_stmt:='insert into '||l_skip_cols_table||'(col_name) values(:1)';

Line: 7545

    g_stmt:='insert into '||l_skip_levels_table||'(col_name) values(:1)';

Line: 7554

  g_stmt:='create table '||l_smart_update_table||'('||
  'col_name varchar2(80)) tablespace '||p_op_table_space;

Line: 7560

  if p_number_smart_update_cols>0 then
    g_stmt:='insert into '||l_smart_update_table||'(col_name) values(:1)';

Line: 7565

    for i in 1..p_number_smart_update_cols loop
      execute immediate g_stmt using p_smart_update_cols(i);

Line: 7577

    g_stmt:='insert into '||l_da_cols_table||'(col_name) values(:1)';

Line: 7614

    g_stmt:='insert into '||p_da_cols_table||'(col_name,stg_col_name) values(:1,:2)';

Line: 7630

function update_load_input_table(
  p_table_name varchar2,
  p_ok_table varchar2,
  p_max_round number,
  p_update_dlog_lookup_table varchar2,
  p_dlog_has_data boolean,
  p_total_records number,
  p_stg_copy_table_flag boolean
)return boolean is
l_dlog_has_data varchar2(10);

Line: 7643

    write_to_log_file_n('In update_load_input_table ');

Line: 7653

  g_stmt:='update '||p_table_name||' set ok_table=:1,max_round=:2,update_dlog_lookup_table=:3,dlog_has_data=:4,'||
  'total_records=:5,stg_copy_table_flag=:6';

Line: 7657

    p_update_dlog_lookup_table||' '||l_dlog_has_data||' '||p_total_records||' '||l_stg_copy_table_flag);

Line: 7659

  execute immediate g_stmt using p_ok_table,p_max_round,p_update_dlog_lookup_table,l_dlog_has_data,
  p_total_records,l_stg_copy_table_flag;

Line: 7665

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

Line: 7674

  g_stmt:='select 1 from all_jobs where job=:1';

Line: 7694

  g_stmt:='select total_time from all_jobs where job=:1';

Line: 7838

  g_stmt:='SELECT lock_table.SID FROM V$LOCK lock_table WHERE lock_table.TYPE=''JQ'' and lock_table.ID2=:1';

Line: 7859

  g_stmt:='select ses.SERIAL# from v$session ses where ses.SID=:1';

Line: 8083

  g_stmt:='insert into '||p_table||'(object_name,id,status,message,measure1,measure2,measure3,measure4,measure5) '||
  'values(:1,:2,:3,:4,:5,:6,:7,:8,:9)';

Line: 8146

    g_stmt:='select id,job_id,status,message from '||p_job_status_table;

Line: 8148

    g_stmt:='select id,job_id,status,message from '||p_job_status_table||' where object_name=:1';

Line: 8199

    p_update_type varchar2,
    p_level_order varcharTableType,
    p_skip_cols varcharTableType,
    p_number_skip_cols number,
    p_load_pk number,
    p_fresh_restart boolean,
    p_op_table_space varchar2,
    p_rollback varchar2,
    p_ltc_merge_use_nl boolean,
    p_dim_inc_refresh_derv boolean,
    p_check_fk_change boolean,
    p_ok_switch_update number,
    p_join_nl_percentage number,
    p_max_threads number,
    p_min_job_load_size number,
    p_sleep_time number,
    p_job_status_table varchar2,
    p_hash_area_size number,
    p_sort_area_size number,
    p_trace boolean,
    p_read_cfig_options boolean,
    p_max_fk_density number
    ) return boolean is
l_debug varchar2(2);

Line: 8251

  'update_type varchar2(200),'||
  'load_pk number,'||
  'fresh_restart varchar2(2),'||
  'op_table_space varchar2(200),'||
  'rollback varchar2(200),'||
  'ltc_merge_use_nl varchar2(2),'||
  'dim_inc_refresh_derv varchar2(2),'||
  'check_fk_change varchar2(2),'||
  'ok_switch_update number,'||
  'join_nl_percentage number,'||
  'max_threads number,'||
  'min_job_load_size number,'||
  'sleep_time number,'||
  'job_status_table varchar2(200),'||
  'hash_area_size number,'||
  'sort_area_size number,'||
  'trace varchar2(2),'||
  'read_cfig_options varchar2(2),'||
  'ilog_table  varchar2(80),'||
  'skip_ilog_update varchar2(2),'||
  'level_change varchar2(2),'||
  'dim_empty_flag varchar2(2),'||
  'before_update_table_final varchar2(80),'||
  'error_rec_flag varchar2(2),'||
  'max_fk_density number'||
  ') tablespace '||p_op_table_space;

Line: 8356

  g_stmt:='insert into '||p_table||'('||
  'conc_id, '||
  'conc_name,'||
  'debug,'||
  'exec_flag,'||
  'bis_owner,'||
  'parallel,'||
  'collection_size,'||
  'table_owner,'||
  'forall_size,'||
  'update_type,'||
  'load_pk,'||
  'fresh_restart,'||
  'op_table_space,'||
  'rollback,'||
  'ltc_merge_use_nl,'||
  'dim_inc_refresh_derv,'||
  'check_fk_change,'||
  'ok_switch_update,'||
  'join_nl_percentage,'||
  'max_threads,'||
  'min_job_load_size,'||
  'sleep_time,'||
  'job_status_table,'||
  'hash_area_size,'||
  'sort_area_size,'||
  'trace,'||
  'read_cfig_options,'||
  'max_fk_density'||
  ') values ('||
  ':1,:2,:3,:4,:5,:6,:7,:8,:9,:10,'||
  ':11,:12,:13,:14,:15,:16,:17,:18,:19,:20,'||
  ':21,:22,:23,:24,:25,:26,:27,:28'||
  ')';

Line: 8403

  write_to_log_file('p_update_type='||p_update_type);

Line: 8411

  write_to_log_file('p_ok_switch_update='||p_ok_switch_update);

Line: 8431

  p_update_type,
  p_load_pk,
  l_fresh_restart,
  p_op_table_space,
  p_rollback,
  l_ltc_merge_use_nl,
  l_dim_inc_refresh_derv,
  l_check_fk_change,
  p_ok_switch_update,
  p_join_nl_percentage,
  p_max_threads,
  p_min_job_load_size,
  p_sleep_time,
  p_job_status_table,
  p_hash_area_size,
  p_sort_area_size,
  l_trace,
  l_read_cfig_options,
  p_max_fk_density;

Line: 8451

  g_stmt:='insert into '||l_level_table||'('||
  'level_number,'||
  'levels,'||
  'child_level_number,'||
  'level_snapshot_logs,'||
  'level_order'||
  ') values('||
  ':1,:2,:3,:4,:5'||
  ')';

Line: 8468

  g_stmt:='insert into '||l_level_child_table||'('||
  'run_number,'||
  'child_levels,'||
  'child_fk,'||
  'parent_pk'||
  ') values('||
  ':1,:2,:3,:4'||
  ')';

Line: 8487

  g_stmt:='insert into '||l_skip_table||'('||
  'skip_cols'||
  ') values('||
  ':1'||
  ')';

Line: 8506

function update_dim_load_input_table(
p_input_table varchar2,
p_ilog_table varchar2,
p_skip_ilog_update boolean,
p_level_change boolean,
p_dim_empty_flag boolean,
p_before_update_table_final varchar2,
p_error_rec_flag boolean,
p_consider_snapshot booleanTableType,
p_levels_I varcharTableType,
p_use_ltc_ilog booleanTableType,
p_number_levels number
)return boolean is
l_skip_ilog_update varchar2(2);

Line: 8527

    write_to_log_file_n('In update_dim_load_input_table');

Line: 8530

  l_skip_ilog_update:='N';

Line: 8534

  if p_skip_ilog_update then
    l_skip_ilog_update:='Y';

Line: 8546

  g_stmt:='update '||p_input_table||' set ilog_table=:1,skip_ilog_update=:2,level_change=:3,'||
  'dim_empty_flag=:4,before_update_table_final=:5,error_rec_flag=:6';

Line: 8551

  execute immediate g_stmt using p_ilog_table,l_skip_ilog_update,l_level_change,l_dim_empty_flag,
  p_before_update_table_final,l_error_rec_flag;

Line: 8554

  g_stmt:='update '||l_level_table||' set consider_snapshot=:1,levels_I=:2,use_ltc_ilog=:3 where level_number=:4';

Line: 8574

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

Line: 8603

p_update_type varchar2,
p_fact_dlog varchar2,
p_skip_cols varcharTableType,
p_number_skip_cols number,
p_load_fk number,
p_fresh_restart boolean,
p_op_table_space varchar2,
p_bu_tables varcharTableType,--before update tables.prop dim change to derv
p_bu_dimensions 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_max_threads number,
p_min_job_load_size number,
p_sleep_time number,
p_job_status_table varchar2,
p_hash_area_size number,
p_sort_area_size number,
p_trace boolean,
p_read_cfig_options boolean
) return boolean is
l_fk_table varchar2(80);

Line: 8668

  'update_type varchar2(80),'||
  'fact_dlog varchar2(80),'||
  'load_fk number,'||
  'fresh_restart varchar2(2),'||
  'op_table_space varchar2(80),'||
  'bu_src_fact varchar2(80),'||
  'load_mode varchar2(80),'||
  'rollback varchar2(80),'||
  'src_join_nl_percentage number,'||
  'max_threads number,'||
  'min_job_load_size number,'||
  'sleep_time number,'||
  'job_status_table varchar2(80),'||
  'hash_area_size number,'||
  'sort_area_size number,'||
  'trace varchar2(2),'||
  'read_cfig_options varchar2(2),'||
  'ilog_table varchar2(80),'||
  'dlog_table varchar2(80),'||
  'skip_ilog_update varchar2(2),'||
  'skip_dlog_update varchar2(2),'||
  'skip_ilog varchar2(2),'||
  'src_object_ilog varchar2(80),'||
  'src_object_dlog varchar2(80),'||
  'src_snplog_has_pk varchar2(2),'||
  'err_rec_flag varchar2(2),'||
  'err_rec_flag_d varchar2(2),'||
  'dbms_job_id number '||
  ') tablespace '||p_op_table_space;

Line: 8721

  g_stmt:='insert into '||p_input_table||'('||
  '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'||
  ') values('||
  ':1,:2,:3,:4,:5,:6,:7,:8,:9,:10,'||
  ':11,:12,:13,:14,:15,:16,:17,:18,:19,:20,'||
  ':21,:22,:23,:24,:25,:26,:27,:28,:29,:30 '||
  ')';

Line: 8774

  p_update_type,
  p_fact_dlog,
  p_load_fk,
  l_fresh_restart,
  p_op_table_space,
  p_bu_src_fact,
  p_load_mode,
  p_rollback,
  p_src_join_nl_percentage,
  p_max_threads,
  p_min_job_load_size,
  p_sleep_time,
  p_job_status_table,
  p_hash_area_size,
  p_sort_area_size,
  l_trace,
  l_read_cfig_options;

Line: 8806

  g_stmt:='insert into '||l_fk_table||'('||
  'fact_fks,'||
  'higher_level,'||
  'parent_dim,'||
  'parent_level,'||
  'level_prefix,'||
  'level_pk,'||
  'level_pk_key,'||
  'dim_pk_key'||
  ') values('||
  ':1,:2,:3,:4,:5,:6,:7,:8'||
  ')';

Line: 8842

  g_stmt:='insert into '||l_skip_table||'('||
  'skip_cols'||
  ') values('||
  ':1'||
  ')';

Line: 8862

  g_stmt:='insert into '||l_bu_table||'('||
  'bu_tables,'||
  'bu_dimensions'||
  ') values('||
  ':1,:2'||
  ')';

Line: 8882

function update_derv_fact_input_table(
p_input_table varchar2,
p_ilog_table varchar2,
p_dlog_table varchar2,
p_skip_ilog_update boolean,
p_skip_dlog_update boolean,
p_skip_ilog boolean,
p_load_mode varchar2,
p_full_refresh boolean,
p_src_object_ilog varchar2,
p_src_object_dlog varchar2,
p_src_snplog_has_pk boolean,
p_err_rec_flag boolean,
p_err_rec_flag_d boolean
) return boolean is
l_skip_ilog_update varchar2(2);

Line: 8898

l_skip_dlog_update varchar2(2);

Line: 8906

    write_to_log_file_n('In update_derv_fact_input_table');

Line: 8908

  l_skip_ilog_update:='N';

Line: 8909

  l_skip_dlog_update:='N';

Line: 8915

  if p_skip_ilog_update then
    l_skip_ilog_update:='Y';

Line: 8918

  if p_skip_dlog_update then
    l_skip_dlog_update:='Y';

Line: 8936

  g_stmt:='update '||p_input_table||' set '||
  'ilog_table=:1,'||
  'dlog_table=:2,'||
  'skip_ilog_update=:3,'||
  'skip_dlog_update=:4,'||
  'skip_ilog=:5,'||
  'load_mode=:6,'||
  'full_refresh=:7,'||
  'src_object_ilog=:8,'||
  'src_object_dlog=:9,'||
  'src_snplog_has_pk=:10,'||
  'err_rec_flag=:11,'||
  'err_rec_flag_d=:12';

Line: 8955

  l_skip_ilog_update,
  l_skip_dlog_update,
  l_skip_ilog,
  p_load_mode,
  l_full_refresh,
  p_src_object_ilog,
  p_src_object_dlog,
  l_src_snplog_has_pk,
  l_err_rec_flag,
  l_err_rec_flag_d;

Line: 8969

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

Line: 9034

      g_stmt:=g_stmt||' select * from '||l_ilog_tables(i)||' union all ';

Line: 9126

  g_stmt:='select table_name from all_tables where (table_name like '''||l_search_string||'_%_'||
  l_ilog_table_extn||''' or table_name like '''||l_search_string||'_%_'||l_ilog_table_extn||'A'') '||
  'and owner='''||p_bis_owner||'''';

Line: 9175

  g_stmt:=g_stmt||' as select '||p_ilog_old||'.*, rownum row_num from '||p_ilog_old;

Line: 9245

function update_inp_table_jobid(
p_input_table varchar2,
p_job_id number
)return boolean is
Begin
  g_stmt:='update '||p_input_table||' set dbms_job_id=:1';

Line: 9258

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

Line: 9262

function update_inp_table_concid(
p_input_table varchar2,
p_conc_id number
)return boolean is
Begin
  g_stmt:='update '||p_input_table||' set conc_id=:1';

Line: 9275

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

Line: 9518

  l_stmt:='select load_pk from edw_collection_detail_log where COLLECTION_STATUS=:a and object_name=:b '||
  'and object_type=:c';

Line: 9591

p_ins_rows_insert out nocopy number,
p_ins_rows_update out nocopy number,
p_ins_rows_delete out nocopy number,
p_ins_instance_name out nocopy varchar2,
p_ins_request_id_table out nocopy varchar2
) return boolean is
l_stmt varchar2(10000);

Line: 9606

    l_stmt:='select nvl(sum(nvl(rows_ready,0)),0), '||
    ' nvl(sum(nvl(rows_collected,0)),0), nvl(sum(nvl(rows_dangling,0)),0), '||
    'nvl(sum(nvl(rows_duplicate,0)),0), nvl(sum(nvl(rows_error,0)),0),nvl(sum(nvl(number_insert,0)),0),'||
    'nvl(sum(nvl(number_update,0)),0),nvl(sum(nvl(number_delete,0)),0) '||
    ' from edw_temp_collection_log where object_name=:a and object_type=:b and status=:c ';

Line: 9613

    l_stmt:='select nvl(sum(nvl(rows_ready,0)),0), '||
    ' nvl(sum(nvl(rows_collected,0)),0), nvl(sum(nvl(rows_dangling,0)),0), '||
    'nvl(sum(nvl(rows_duplicate,0)),0), nvl(sum(nvl(rows_error,0)),0),nvl(sum(nvl(number_insert,0)),0),'||
    'nvl(sum(nvl(number_update,0)),0),nvl(sum(nvl(number_delete,0)),0) '||
    ' from edw_temp_collection_log where request_id=:1';

Line: 9622

    p_ins_rows_error,p_ins_rows_insert,p_ins_rows_update,p_ins_rows_delete;

Line: 9625

  l_stmt:='select max(NUMBER_READY) from edw_temp_collection_log where object_name=:a and object_type=:b '||
  'and status=:c';

Line: 9638

    'insert update delete');

Line: 9642

    p_ins_rows_duplicate||' '||p_ins_rows_error||' '||p_ins_rows_insert||' '||p_ins_rows_update
    ||' '||p_ins_rows_delete);

Line: 9645

  l_stmt:='delete edw_temp_collection_log where object_name=:a and object_type=:b and status=:c';

Line: 9673

  g_stmt:='select application_name from fnd_application_vl where application_short_name=:1';

Line: 9741

  p_level_full_insert booleanTableType,
  p_debug boolean,
  p_parallel number,
  p_collection_size number,
  p_bis_owner  varchar2,
  p_table_owner varchar2,
  p_full_refresh boolean,
  p_forall_size number,
  p_update_type varchar2,
  p_load_pk number,
  p_op_table_space varchar2,
  p_rollback 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_level_table varchar2(80);

Line: 9765

l_level_full_insert varchar2(10);

Line: 9787

  'update_type varchar2(200),'||
  'load_pk number,'||
  'op_table_space varchar2(200),'||
  'rollback varchar2(200),'||
  'max_threads number,'||
  'min_job_load_size number,'||
  'sleep_time number,'||
  'hash_area_size number,'||
  'sort_area_size number,'||
  'trace varchar2(10),'||
  'read_cfig_options varchar2(10),'||
  'join_nl_percentage number'||
  ') tablespace '||p_op_table_space;

Line: 9823

  g_stmt:='insert into '||p_input_table||'('||
  'dim_name'||
  ',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'||
  ') values('||
  ':1,:2,:3,:4,:5,:6,:7,:8,:9,:10,'||
  ':11,:12,:13,:14,:15,:16,:17,:18,:19,:20,'||
  ':21'||
  ')';

Line: 9863

  p_update_type,
  p_load_pk,
  p_op_table_space,
  p_rollback,
  p_max_threads,
  p_min_job_load_size,
  p_sleep_time,
  p_hash_area_size,
  p_sort_area_size,
  l_trace,
  l_read_cfig_options,
  p_join_nl_percentage;

Line: 9887

  'level_full_insert varchar2(10)'||
  ') tablespace '||p_op_table_space;

Line: 9896

  g_stmt:='insert into '||l_level_table||'('||
  'level_number'||
  ',levels'||
  ',child_level_number'||
  ',level_order'||
  ',level_snapshot_logs'||
  ',level_ilog'||
  ',level_consider'||
  ',level_full_insert'||
  ') values ('||
  ':1,:2,:3,:4,:5,:6,:7,:8'||
  ')';

Line: 9916

    l_level_full_insert:='N';

Line: 9917

    if p_level_full_insert(i) then
      l_level_full_insert:='Y';

Line: 9928

    ,l_level_full_insert;

Line: 9944

  g_stmt:='insert into '||l_level_child_table||'('||
  'run_number,'||
  'child_levels,'||
  'child_fk,'||
  'parent_pk'||
  ') values('||
  ':1,:2,:3,:4'||
  ')';

Line: 10006

  g_stmt:=g_stmt||' as select ';

Line: 10039

  ' as select '''||p_status||''' status,'||p_count||' count from dual';

Line: 10144

  l_stmt:='select '||p_seq||'.NEXTVAL from dual';

Line: 10165

  g_stmt:='select max('||p_col||') from '||p_table;

Line: 10200

select
    dim.relation_name,
    dim.relation_id,
    dim_pk_item.column_name dim_col,
    fstg_item.column_name fstg_col,
    fact_item.column_name fact_col
    from
    (select Source_usage_id,Target_column_id from edw_pvt_map_columns_md_v
    where mapping_id=p_mapping_id) map_columns,
    (select edw_pvt_map_key_usages_md_v.Source_usage_id,
    edw_pvt_map_key_usages_md_v.Unique_key_id,
    edw_pvt_map_key_usages_md_v.foreign_key_id,
    edw_pvt_map_key_usages_md_v.Parent_table_usage_id
    from
    edw_pvt_map_key_usages_md_v,edw_pvt_map_sources_md_v
    where edw_pvt_map_sources_md_v.source_usage_id=edw_pvt_map_key_usages_md_v.parent_table_usage_id
    and edw_pvt_map_key_usages_md_v.Source_usage_id=p_fstg_usage_id
    and edw_pvt_map_key_usages_md_v.mapping_id=p_mapping_id
    and edw_pvt_map_sources_md_v.mapping_id=p_mapping_id
    ) key_usage,
    edw_pvt_key_columns_md_v key_col,
    edw_pvt_key_columns_md_v fk_key_col,
    edw_unique_keys_md_v dim_pk,
    edw_relations_md_v dim,
    edw_pvt_columns_md_v dim_pk_item,
    edw_pvt_columns_md_v fstg_item,
    edw_pvt_columns_md_v fact_item
    where
    dim_pk.key_id=key_usage.Unique_key_id
    and dim.relation_id=dim_pk.entity_id
    and key_col.key_id=dim_pk.key_id
    and dim_pk_item.column_id=key_col.column_id
    and fk_key_col.key_id=key_usage.foreign_key_id
    and fstg_item.column_id=fk_key_col.column_id
    and map_columns.Source_usage_id=key_usage.Parent_table_usage_id
    and map_columns.Target_column_id=fact_item.column_id ;

Line: 10310

    l_stmt := 'INSERT INTO '||l_table1||' VALUES(:1,:2,:3,:4,:5)';

Line: 10325

    ' as select distinct relation_name from '||l_table1;

Line: 10334

    ' as select syn.table_owner,tab.relation_name from user_synonyms syn,'||l_table2||' tab '||
    'where tab.relation_name=syn.synonym_name(+)';

Line: 10344

    ' as select syn.table_owner,tab.* from '||l_table3||' syn,'||l_table1||' tab '||
    'where tab.relation_name=syn.relation_name';

Line: 10354

    ' as select all_tab.NUM_ROWS,tab.table_owner,tab.relation_name,tab.relation_id,'||
    'tab.dim_col,tab.fstg_col,tab.fact_col from '||l_table4||' tab, all_tables all_tab '||
    'where all_tab.table_name=tab.relation_name and all_tab.owner=tab.table_owner '||
    'and tab.table_owner is not null '||
    'union all '||
    'select 0,tab.table_owner,tab.relation_name,tab.relation_id,'||
    'tab.dim_col,tab.fstg_col,tab.fact_col from '||l_table4||' tab where tab.table_owner is null ';

Line: 10367

  g_stmt:='select relation_name,num_rows,relation_id,dim_col,fstg_col,fact_col from '||l_table5||
  ' order by num_rows,relation_name';

Line: 10431

    g_stmt:='select '||
    'fk_col.column_name, '||
    'p_table.relation_name '||
    'from '||
    'edw_relations_md_v fstg, '||
    'edw_foreign_keys_md_v fk, '||
    'EDW_PVT_KEY_COLUMNS_MD_V fku, '||
    'edw_pvt_columns_md_v fk_col, '||
    'edw_relations_md_v p_table, '||
    'edw_unique_keys_md_v pk '||
    'where '||
    'fstg.relation_id=fk.entity_id '||
    'and fk.foreign_key_id=fku.key_id '||
    'and fk_col.column_id=fku.column_id '||
    'and fk_col.parent_object_id=fstg.relation_id '||
    'and pk.key_id=fk.key_id '||
    'and pk.entity_id=p_table.relation_id '||
    'and fstg.relation_id=:1 ';

Line: 10533

  g_stmt:='select '||
  'map_sources.source_usage_id, '||
  'map_sources.source_id, '||
  'src_table.name, '||
  'map_targets.target_usage_id, '||
  'map_targets.target_id, '||
  'tgt_relation.relation_name, '||
  'pk_col.column_name, '||
  'tgt_column.column_name '||
  'from '||
  'edw_pvt_map_properties_md_v map_properties, '||
  'edw_pvt_map_sources_md_v map_sources, '||
  'edw_pvt_map_targets_md_v map_targets, '||
  'edw_tables_md_v src_table, '||
  'edw_relations_md_v tgt_relation, '||
  'edw_unique_keys_md_v pk, '||
  'edw_pvt_key_columns_md_v key_usage, '||
  'edw_pvt_columns_md_v pk_col, '||
  '(select * from edw_pvt_map_columns_md_v where mapping_id=:1) map_columns, '||
  'edw_pvt_columns_md_v tgt_column '||
  'where '||
  'map_properties.mapping_id=:2 '||
  'and map_sources.source_id=map_properties.primary_source '||
  'and map_targets.target_id=map_properties.primary_target '||
  'and map_sources.mapping_id=map_properties.mapping_id '||
  'and src_table.elementid=map_sources.source_id '||
  'and map_targets.mapping_id=map_properties.mapping_id '||
  'and tgt_relation.relation_id=map_targets.target_id '||
  'and pk.entity_id=map_properties.primary_source '||
  'and key_usage.key_id=pk.key_id '||
  'and pk_col.column_id=key_usage.column_id '||
  'and map_columns.Source_usage_id=map_sources.source_usage_id '||
  'and map_columns.Source_column_id=pk_col.column_id '||
  'and map_columns.Target_column_id=tgt_column.column_id ';

Line: 10616

  if delete_conc_program(p_conc_short_name,p_exe_name,l_bis_long_name,'LONG')=false then
    null;

Line: 10676

function delete_conc_program(
p_conc_name varchar2,
p_exe_name varchar2,
p_bis_name varchar2,
p_name_type varchar2
) return boolean is
l_bis_long_name varchar2(400);

Line: 10685

    write_to_log_file_n('In delete_conc_program');

Line: 10694

  FND_PROGRAM.DELETE_PROGRAM(p_conc_name,l_bis_long_name);

Line: 10695

  FND_PROGRAM.DELETE_EXECUTABLE(p_exe_name,l_bis_long_name);

Line: 10697

    write_to_log_file_n('Deleted '||p_conc_name||' '||p_exe_name);

Line: 10702

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

Line: 10763

cursor cv(p_name varchar2) is select param.value from v$parameter param where param.name=p_name;

Line: 10766

    write_to_log_file_n('select param.value from v$parameter param where param.name=:1 '||p_name);

Line: 10852

  l_stmt:='select '||p_col||' from '||p_table||' '||p_where;

Line: 10890

    l_stmt:='select ses.sid,sy.name,round(ss.value/1048576,2) from v$sesstat ss,v$sysstat sy,v$session ses '||
    'where sy.statistic#=ss.statistic# and ss.sid = ses.sid and ses.audsid=:1 and '||
    'sy.name in (''session pga memory'',''session pga memory max'',''session uga memory'',''session uga memory max'')';

Line: 10918

  l_stmt:='select pq.statistic,pq.last_query,pq.session_total from v$pq_sesstat pq';

Line: 11008

select table_name from all_tables where table_name like p_pattern and owner=p_owner;

Line: 11015

    write_to_log_file_n('select table_name from all_tables where table_name like '||p_pattern||
    ' and owner='||p_owner);

Line: 11036

function update_status_table(
p_table varchar2,
p_col varchar2,
p_value varchar2,
p_where varchar2
) return boolean is
--
l_stmt varchar2(4000);

Line: 11046

  l_stmt:='update '||p_table||' set '||p_col||'=:1 '||p_where;

Line: 11053

  write_to_log_file_n('Error in update_status_table '||sqlerrm);

Line: 11064

select distinct
lvl.LEVEL_TABLE_ID,
rel.CHIL_LVLTBL_NAME,
rel.CHILD_LVLTBL_ID
from
edw_levels_md_v lvl,
edw_level_relations_md_v rel
where
lvl.LEVEL_TABLE_NAME=p_ltc
and rel.PARENT_LVL_ID(+)=lvl.level_id;

Line: 11106

        write_to_log_file_n('select distinct rel.PARENT_LVLTBL_ID,rel.CHIL_LVLTBL_NAME,rel.CHILD_LVLTBL_ID '||
        'from edw_levels_md_v lvl,edw_level_relations_md_v rel where lvl.LEVEL_TABLE_NAME='||p_level_order(i)||
        ' and rel.PARENT_LVL_ID=lvl.level_id');

Line: 11127

        l_stmt:='insert into '||p_table||'(level_order,parent_ltc,parent_ltc_id)'||
        ' values(:1,:2,:3)';

Line: 11137

              l_stmt:='insert into '||p_table||'(level_order,parent_ltc,parent_ltc_id,child_ltc,child_ltc_id)'||
              ' values(:1,:2,:3,:4,:5)';

Line: 11156

select PARTITIONED from all_tables where table_name=p_table and owner=p_owner;

Line: 11161

    write_to_log_file_n('select PARTITIONED from all_tables where table_name='||p_table||' and owner='||
    p_owner);

Line: 11182

select LEVEL_TABLE_ID from edw_levels_md_v where dim_id=p_dim_id;

Line: 11207

select 1
from
ALL_MVIEWS mv,
ALL_MVIEW_DETAIL_RELATIONS rel
where
rel.mview_name=mv.mview_name
and mv.owner=rel.owner
and mv.fast_refreshable<>'NO'
and rel.detailobj_name=upper(p_object)
and rel.detailobj_owner=upper(p_owner)
and rownum=1;

Line: 11224

    write_to_log_file('select 1 from ALL_MVIEWS mv, ALL_MVIEW_DETAIL_RELATIONS rel '||
    ' where rel.mview_name=mv.mview_name and mv.owner=rel.owner and mv.fast_refreshable<>''NO'''||
    ' and rel.detailobj_name='||p_object||' and rel.detailobj_owner='||p_owner);

Line: 11250

select table_name from all_tables where table_name like p_string and owner=p_owner;

Line: 11261

    write_to_log_file_n('In drop_tables_like, select table_name from all_tables where '||
    'table_name like '||l_string||' and owner='||p_owner);

Line: 11456

  SELECT oracle_username
  FROM fnd_oracle_userid WHERE oracle_id
  BETWEEN 900 AND 999 AND read_only_flag = 'U';