APPS.EDW_NAEDW_PUSH SQL Statements

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

Line: 7

select dim.dim_name,
       dim.dim_id
from   edw_dimensions_md_v dim
where dim_name not like 'EDW_GL_ACCT%_M'
and dim_name <> 'EDW_NA'
order by dim.dim_name;

Line: 15

select dim.dim_name,dim.dim_id
from
edw_dimensions_md_v dim
where dim_long_name=p_dim;

Line: 21

select level_name||'_LTC'
from edw_levels_md_v
where dim_id=p_dim_id;

Line: 26

select distinct
    lvl_child.level_name||'_LTC',
    lvl_parent.level_name||'_LTC'
from
    edw_levels_md_v lvl_child,
    edw_levels_md_v lvl_parent,
    edw_pvt_level_relation_md_v lvl_rel,
    edw_hierarchies_md_v hier
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;

Line: 41

select
    relation.name,
    item.column_name,
    item.data_type,
    'FK',
    parent.name
from
    edw_levels_md_v lvl,
    edw_tables_md_v relation,
    edw_pvt_columns_md_v item,
    edw_foreign_keys_md_v fk,
    edw_pvt_key_columns_md_v isu,
    edw_unique_keys_md_v pk,
    edw_tables_md_v parent
where
    lvl.dim_id=p_dim_id
and relation.name=lvl.level_name||'_LTC'
and item.parent_object_id=relation.elementid
and fk.entity_id=relation.elementid
and isu.key_id=fk.foreign_key_id
and item.column_id=isu.column_id
and pk.key_id=fk.key_id
and parent.elementid=pk.entity_id
union all
select
    relation.name,
    item.column_name,
    item.data_type,
    'PK',
    null
from
    edw_levels_md_v lvl,
    edw_tables_md_v relation,
    edw_pvt_columns_md_v item,
    edw_unique_keys_md_v pk,
    edw_pvt_key_columns_md_v isu
where
    lvl.dim_id=p_dim_id
and relation.name=lvl.level_name||'_LTC'
and item.parent_object_id=relation.elementid
and pk.entity_id=relation.elementid
and isu.key_id=pk.key_id
and item.column_id=isu.column_id;

Line: 87

select
    relation.name,
    item.column_name,
    item.length,
    item.data_type
from
    edw_tables_md_v relation,
    edw_pvt_columns_md_v item
where
     relation.name in (select level_name||'_LTC' from edw_levels_md_v where dim_id=p_dim_id)
and item.parent_object_id=relation.elementid
and item.column_name in ('NAME');

Line: 101

select
    all_tab.table_name,
    all_tab.column_name,
    all_tab.data_length,
    all_tab.data_type
from
    all_tab_columns all_tab,
    all_tables tab
where
    all_tab.nullable in ('N')
and all_tab.table_name in (select level_name||'_LTC' from edw_levels_md_v where dim_id=p_dim_id)
and tab.table_name=all_tab.table_name
and tab.owner=p_owner
and all_tab.owner= p_owner

and all_tab.column_name not in ('NAME');

Line: 143

Execute_insert_stmt;

Line: 144

write_to_log_file_n('Executed all insert '||get_time);

Line: 201

  Execute_insert_stmt;

Line: 276

  Execute_insert_stmt;

Line: 335

    l_stmt:='select dim_name,dim_id from edw_dimensions_md_v where dim_long_name=:s';

Line: 337

    l_stmt:='select dim_name,dim_id from edw_dimensions_md_v where dim_name=:s';

Line: 501

PROCEDURE Make_insert_stmt(p_level_index number) IS
Begin
  g_check_stmt:='select 1 from '||g_levels(p_level_index)||' where '||
  g_level_pk(g_varchar_pk_index)||' = '||''''||g_naedw_varchar2||''''||' or '||
  g_level_pk(g_varchar_pk_index)||' = '||''''||g_all_varchar2||'''';

Line: 507

  g_err_check_stmt:='select 1 from '||g_levels(p_level_index)||' where '||
  g_level_pk(g_varchar_pk_index)||' = '||''''||g_err_varchar2||'''';

Line: 510

  g_insert_stmt:='insert into '||g_levels(p_level_index)||' ( ';

Line: 511

  make_body_insert_update_stmt(p_level_index,true);

Line: 515

  g_insert_stmt:=g_insert_stmt||g_body_insert_update_stmt;

Line: 516

  g_err_insert_stmt:='insert into '||g_levels(p_level_index)||' ( ';

Line: 517

  make_err_body_insert_stmt(p_level_index,true);

Line: 521

  g_err_insert_stmt:=g_err_insert_stmt||g_err_body_insert_update_stmt;

Line: 524

  write_to_log_file_n('Error in make_insert_stmt for level '||g_levels(p_level_index)||' '||
    g_status_message||get_time);

Line: 527

End;--PROCEDURE Make_insert_stmt IS

Line: 529

PROCEDURE Make_Update_Stmt(p_level_index number) IS
first_find boolean :=true;

Line: 534

  g_update_stmt:='update  '||g_levels(p_level_index)||' set ( ';

Line: 535

  make_body_insert_update_stmt(p_level_index,false);

Line: 539

  g_update_stmt:=g_update_stmt||g_body_insert_update_stmt;

Line: 540

  g_err_update_stmt:='update  '||g_levels(p_level_index)||' set ( ';

Line: 541

  make_err_body_insert_stmt(p_level_index,false);

Line: 545

  g_err_update_stmt:=g_err_update_stmt||g_err_body_insert_update_stmt;

Line: 548

write_to_log_file_n('Error in make_update_stmt for level '||g_levels(p_level_index)||' '||
    g_status_message||get_time);

Line: 551

End;--PROCEDURE Make_Update_Stmt(p_level_index number) IS

Line: 553

PROCEDURE make_body_insert_update_stmt(p_level_index number, p_insert_flag boolean) IS
first_find boolean:=true;

Line: 560

  g_body_insert_update_stmt:='';

Line: 563

      g_body_insert_update_stmt:=g_body_insert_update_stmt||' '||g_level_pk(i);

Line: 566

      g_body_insert_update_stmt:=g_body_insert_update_stmt||','||g_level_pk(i);

Line: 571

    g_body_insert_update_stmt:=g_body_insert_update_stmt||','||g_level_fk(i);

Line: 578

          g_body_insert_update_stmt:=g_body_insert_update_stmt||','||g_level_cols(i);

Line: 582

          g_body_insert_update_stmt:=g_body_insert_update_stmt||','||g_level_cols(i);

Line: 586

      if g_level_cols(i) <> 'CREATION_DATE' and g_level_cols(i) <> 'LAST_UPDATE_DATE' then
        g_body_insert_update_stmt:=g_body_insert_update_stmt||','||g_level_cols(i);

Line: 591

  if p_insert_flag then
    --if g_level_status(p_level_index)<>'P' then
      g_body_insert_update_stmt:=g_body_insert_update_stmt||',CREATION_DATE, LAST_UPDATE_DATE)';

Line: 597

    g_body_insert_update_stmt:=g_body_insert_update_stmt||' values (';

Line: 600

      g_body_insert_update_stmt:=g_body_insert_update_stmt||',LAST_UPDATE_DATE)';

Line: 604

    g_body_insert_update_stmt:=g_body_insert_update_stmt||'= (select ';

Line: 628

      g_body_insert_update_stmt:=g_body_insert_update_stmt||' '||l_pk_stmt;

Line: 631

      g_body_insert_update_stmt:=g_body_insert_update_stmt||','||l_pk_stmt;

Line: 657

    g_body_insert_update_stmt:=g_body_insert_update_stmt||','||l_fk_stmt;

Line: 664

          g_body_insert_update_stmt:=g_body_insert_update_stmt||','||''''||g_all_varchar2_mesg||'''';

Line: 668

          g_body_insert_update_stmt:=g_body_insert_update_stmt||','||''''||g_unassigned||'''';

Line: 672

      if g_level_cols(i) <> 'CREATION_DATE' and g_level_cols(i) <> 'LAST_UPDATE_DATE' then
        g_body_insert_update_stmt:=g_body_insert_update_stmt||','||'null';

Line: 676

      g_body_insert_update_stmt:=g_body_insert_update_stmt||','||g_naedw_number;

Line: 679

  if p_insert_flag then
    --if g_level_status(p_level_index)<>'P' then
      g_body_insert_update_stmt:=g_body_insert_update_stmt||',SYSDATE,SYSDATE)';

Line: 687

      g_body_insert_update_stmt:=g_body_insert_update_stmt||',SYSDATE ';

Line: 689

    g_body_insert_update_stmt:=g_body_insert_update_stmt||' from dual) where '||
       g_level_pk(l_pk_num)||'=';

Line: 692

       g_body_insert_update_stmt:=g_body_insert_update_stmt||g_all_number||' ';

Line: 694

       g_body_insert_update_stmt:=g_body_insert_update_stmt||g_naedw_number||' ';

Line: 699

  write_to_log_file_n('Error in make_body_insert_update_stmt for level '||g_levels(p_level_index)||' '||
    g_status_message||get_time);

Line: 702

End;--make_body_insert_update_stmt(p_level_index number) IS

Line: 704

PROCEDURE make_err_body_insert_stmt(p_level_index number, p_insert_flag boolean) IS
first_find boolean:=true;

Line: 711

  g_err_body_insert_update_stmt:='';

Line: 714

      g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||' '||g_level_pk(i);

Line: 717

      g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||','||g_level_pk(i);

Line: 722

    g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||','||g_level_fk(i);

Line: 729

          g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||','||g_level_cols(i);

Line: 733

          g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||','||g_level_cols(i);

Line: 737

      if g_level_cols(i) <> 'CREATION_DATE' and g_level_cols(i) <> 'LAST_UPDATE_DATE' then
        g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||','||g_level_cols(i);

Line: 742

  if p_insert_flag then
    --if g_level_status(p_level_index)<>'P' then
      g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||',CREATION_DATE, LAST_UPDATE_DATE)';

Line: 748

    g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||' values (';

Line: 751

      g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||',LAST_UPDATE_DATE)';

Line: 755

    g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||'= (select ';

Line: 779

      g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||' '||l_pk_stmt;

Line: 782

      g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||','||l_pk_stmt;

Line: 808

    g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||','||l_fk_stmt;

Line: 815

          g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||','||''''||g_all_varchar2_mesg||'''';

Line: 819

          g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||','||''''||g_invalid||'''';

Line: 823

      if g_level_cols(i) <> 'CREATION_DATE' and g_level_cols(i) <> 'LAST_UPDATE_DATE' then
        g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||','||'null';

Line: 827

      g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||','||g_err_number;

Line: 830

  if p_insert_flag then
    --if g_level_status(p_level_index)<>'P' then
      g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||',SYSDATE,SYSDATE)';

Line: 838

      g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||',SYSDATE ';

Line: 840

    g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||' from dual) where '||
       g_level_pk(l_pk_num)||'=';

Line: 843

       g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||g_all_number||' ';

Line: 845

       g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||g_err_number||' ';

Line: 850

  write_to_log_file_n('Error in make_err_body_insert_update_stmt for level '||g_levels(p_level_index)||' '||
    g_status_message||get_time);

Line: 855

PROCEDURE Execute_insert_stmt_level(p_level_index number) IS
TYPE CurTyp IS REF CURSOR;

Line: 859

l_insert boolean:=true;

Line: 874

      l_insert:=true;

Line: 875

      write_to_log_file_n('Need to insert NAEDW or ALL for '||g_levels(p_level_index));

Line: 877

      l_insert:=false;

Line: 888

  if l_insert then
   l_stmt:=g_insert_stmt;

Line: 892

     l_stmt:=g_update_stmt;

Line: 914

End;--PROCEDURE Execute_insert_stmt_level(p_level_index number) IS

Line: 916

PROCEDURE Execute_err_insert_stmt_level(p_level_index number) IS
TYPE CurTyp IS REF CURSOR;

Line: 920

l_insert boolean:=true;

Line: 935

      l_insert:=true;

Line: 936

      write_to_log_file_n('Need to insert ERR  for '||g_levels(p_level_index));

Line: 938

      l_insert:=false;

Line: 949

  if l_insert then
   l_stmt:=g_err_insert_stmt;

Line: 953

      l_stmt:=g_err_update_stmt;

Line: 1136

   Make_insert_stmt(i);      --pass the level index

Line: 1143

     Make_update_stmt(i);      --pass the level index

Line: 1148

   Execute_insert_stmt_level(i);

Line: 1153

     Execute_err_insert_stmt_level(i);

Line: 1166

PROCEDURE Execute_insert_stmt IS
Begin

for i in 1..g_number_dims loop
  g_status:=true;

Line: 1188

End;--PROCEDURE Execute_insert_stmt IS

Line: 1287

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