[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_select_stmt varchar2(2000);
l_rows_inserted integer:=0;
l_select_stmt:=
'SELECT value_set_id
FROM EDW_FLEX_SEG_MAPPINGS_V@' || G_TARGET_LINK ||
' WHERE DIMENSION_SHORT_NAME= :g_dimension_name
AND lower(INSTANCE_CODE)= lower(:p_instance_code)
AND structure_num=(SELECT chart_of_accounts_id FROM GL_SETS_OF_BOOKS WHERE set_of_books_id= :p_set_of_books_id)';
DBMS_SQL.parse(l_cursor_id,l_select_stmt,DBMS_SQL.NATIVE);
l_rows_inserted:=DBMS_SQL.execute(l_cursor_id);
select set_of_books_name into l_set_of_books_name
from edw_local_set_of_books
where lower(p_instance_code)=lower(instance)
and set_of_books_id=p_set_of_books_id;
l_stmt := 'select COUNT(*) from FND_LOOKUP_VALUES ' ||
'where ENABLED_FLAG = ''Y'' and LOOKUP_TYPE = ''EDW_OBJECTS_TO_LOAD'' and LOOKUP_CODE = ''' ||
g_dimension_name || '''';
procedure insert_into_temp_table(
p_temp_table_name IN VARCHAR2,
p_parent IN varchar2,
p_parent_name in varchar2,
p_parent_desc in varchar2,
p_child in varchar2,
p_child_name in varchar2,
p_child_desc in varchar2,
p_rows_inserted out NOCOPY integer) as
l_insert_stmt varchar2(20000);
l_rows_inserted integer:=0;
l_insert_stmt:= 'INSERT INTO ' || p_temp_table_name || '(parent,parent_name,parent_desc,child,child_name,child_desc)
values(:b_parent,:b_parent_name,:b_parent_desc,:b_child,:b_child_name,:b_child_desc)';
VBHDEBUG('Going to execute '|| l_insert_stmt);
DBMS_SQL.parse(l_cursor_id,l_insert_stmt,DBMS_SQL.V7);
l_rows_inserted:=DBMS_SQL.execute(l_cursor_id);
p_rows_inserted:=l_rows_inserted;
p_rows_inserted := 0;
VBHDEBUG('error: when inserting '||p_parent||','||p_child||' into ' || p_temp_table_name );
end insert_into_temp_table ;
l_rows_deleted integer:=0;
l_delete_stmt varchar2(200);
l_delete_stmt:='delete from ' || p_temp_table_name || ' where child like :b_temp_value or (child is null and parent like :b_temp_value1)';
DBMS_SQL.parse(l_cursor_id,l_delete_stmt,DBMS_SQL.V7);
l_rows_deleted:=DBMS_SQL.execute(l_cursor_id);
VBHDEBUG('Removed ' || l_rows_deleted || ' rows from ' || p_temp_table_name );
l_stmt:='select 1 from '||p_table||' where rownum=1';
as select b.edw_set_of_books_id, b.instance, b.set_of_books_id,
b.set_of_books_name, b.chart_of_accounts_id,
b.description, c.value_set_id
from
(SELECT distinct *
FROM edw_local_set_of_books
WHERE instance IN (
select instance_code
from edw_local_instance )
AND edw_set_of_books_id NOT IN(
SELECT DISTINCT edw_set_of_books_id
FROM edw_local_equi_set_of_books)
) B,
EDW_FLEX_SEG_MAPPINGS_V@' || G_TARGET_LINK || ' C
where C.DIMENSION_SHORT_NAME = ''' || g_dimension_name|| '''
AND lower(C.INSTANCE_CODE)= lower(B.INSTANCE)
AND c.structure_num=(SELECT chart_of_accounts_id FROM GL_SETS_OF_BOOKS WHERE set_of_books_id= b.set_of_books_id)';
' as select distinct value_set_id from edw_flex_seg_mappings_v@' || G_TARGET_LINK ||
' where dimension_short_name = '''|| g_dimension_name||'''';
'select a.flex_value_set_id,a.parent_flex_value, c.description parent_desc,'||
'a.flex_value,a.description,a.summary_flag from
( SELECT v.flex_value_set_id, h.parent_flex_value,
v.flex_value, v.description, v.summary_flag
FROM fnd_flex_values_vl v, fnd_flex_value_norm_hierarchy h,
fnd_flex_value_sets s, '||g_value_set_temp_table||' vst
WHERE vst.value_set_id = v.flex_value_set_id
AND h.flex_value_set_id = v.flex_value_set_id
AND s.flex_value_set_id = v.flex_value_set_id
AND (((s.format_type NOT IN (''N'',''D'', ''T''))
AND ( v.flex_value BETWEEN h.child_flex_value_low AND
h.child_flex_value_high)))
AND ( (v.summary_flag = ''Y'' AND h.range_attribute = ''P'')
OR (v.summary_flag = ''N'' AND h.range_attribute = ''C''))) a, ' || g_value_set_temp_table || ' b,'
|| g_value_temp_table||' c where a.flex_value_set_id = b.value_set_id '
|| ' and c.flex_value_set_id=a.flex_value_set_id and a.parent_flex_value=c.flex_value';
' as select distinct flex_value_set_id,flex_value,description,summary_flag, ENABLED_FLAG '||
' from fnd_flex_values_vl where flex_value_set_id in '||
'(select value_set_id from edw_flex_seg_mappings_v@' ||
G_TARGET_LINK ||
' where dimension_short_name = '''|| g_dimension_name||''') '||
'UNION '||
'select flex_value_set_id, value_column_name, meaning_column_name, summary_column_name, ''Y'' '||
' from FND_FLEX_VALIDATION_TABLES '||
' WHERE FLEX_VALUE_SET_ID in '||
' (select value_set_id from edw_flex_seg_mappings_v@'||
G_TARGET_LINK||
' where value_set_type=''F'' and dimension_short_name = '''||g_dimension_name||''')';
l_temp_insert_count number := 0;
l_stmt:= 'INSERT INTO '|| p_temp_table_name || '(parent,parent_name,parent_desc,child,child_name,child_desc) '
|| 'select a.flex_value||''-''||b.set_of_books_id||''-''||b.instance, a.flex_value,a.description, NULL, NULL,NULL'
||' FROM (select flex_value_set_id, flex_value,description from '|| g_value_temp_table
||' minus '||
'(select flex_value_set_id,flex_value,description from ' || g_hie_temp_table_name
|| ' union all '
|| ' select flex_value_set_id, parent_flex_value,parent_desc from ' || g_hie_temp_table_name
|| ' )) a, '
|| g_sob_vset_lookup_table || ' b'
|| ' WHERE b.value_set_id = a.flex_value_set_id';
l_temp_insert_count:=sql%rowcount;
VBHDEBUG('inserted '|| l_temp_insert_count||' stand alone nodes into '|| p_temp_table_name );
return l_temp_insert_count;
l_temp_insert_count number := 0;
l_stmt:='INSERT INTO '|| p_temp_table_name || '(parent,parent_name,parent_desc,child,child_name,child_desc)
'|| 'select a.flex_value||''-''||'''
||p_set_of_books.set_of_books_id||'''||''-''||'''
||p_set_of_books.instance||
''',a.flex_value,a.description, NULL, NULL,NULL'
||' FROM (select flex_value,description from '|| g_value_temp_table
||' where flex_value_set_id= '|| p_value_set_id ||' minus '||
'(select flex_value,description from '
|| g_hie_temp_table_name
||' where flex_value_set_id='|| p_value_set_id||' union all '
||' select parent_flex_value,parent_desc from '
|| g_hie_temp_table_name
||' where flex_value_set_id='|| p_value_set_id ||')) a';
l_temp_insert_count:=sql%rowcount;
VBHDEBUG('inserted '|| l_temp_insert_count||' stand alone nodes into '|| p_temp_table_name||' from '|| p_set_of_books.set_of_books_name);
return l_temp_insert_count;
Select instance_code INTO l_ins_code FROM edw_local_instance;--added bug 3973264
l_stmt:= 'select instance.instance_code from edw_local_instance@'|| G_TARGET_LINK ||' instance';
l_stmt := 'create table ' || p_source_temp_table_name || ' as select * from ' || p_target_temp_table_name
|| ' where 1 = 2 ';
l_temp_insert_count number := 0;
l_temp_delete_count number := 0;
l_stmt := 'delete from '|| p_target_temp_table_name ;
l_temp_delete_count:=sql%rowcount;
l_stmt := 'INSERT INTO '|| p_target_temp_table_name ||
' SELECT * from ' || p_source_temp_table_name;
l_temp_insert_count:=sql%rowcount;
return l_temp_insert_count;
l_temp_insert_count number := 0;
l_stmt:= 'INSERT INTO '|| p_temp_table_name || '(parent,parent_name,parent_desc,child,child_name,child_desc) '
|| ' select a.parent_flex_value||''-''||b.set_of_books_id||''-''||b.instance, a.parent_flex_value, a.parent_desc,'
|| ' a.flex_value||''-''||b.set_of_books_id||''-''||b.instance, a.flex_value, a.description FROM '
|| g_hie_temp_table_name || ' A,'
|| g_sob_vset_lookup_table || ' B'
|| ' where a.flex_value_set_id = b.value_set_id ';
l_temp_insert_count:=sql%rowcount;
VBHDEBUG('inserted '|| l_temp_insert_count||' parent-child pairs into '|| p_temp_table_name);
return l_temp_insert_count;
l_temp_insert_count number := 0;
l_stmt:='INSERT INTO '|| p_temp_table_name || '(parent,parent_name,parent_desc,child,child_name,child_desc) '||
' select parent_flex_value||''-''||'''
||p_set_of_books.set_of_books_id||'''||''-''||'''
||p_set_of_books.instance||
''',parent_flex_value,parent_desc,flex_value||''-''||'''
||p_set_of_books.set_of_books_id||'''||''-''||'''
||p_set_of_books.instance||
''',flex_value,description FROM '
|| g_hie_temp_table_name
||' where flex_value_set_id = '||p_value_set_id;
l_temp_insert_count:=sql%rowcount;
VBHDEBUG('inserted '|| l_temp_insert_count||' parent-child pairs into '|| p_temp_table_name ||' from '|| p_set_of_books.set_of_books_name);
return l_temp_insert_count;
l_temp_insert_count number := 0;
l_deleted_count number := 0;
l_stmt := 'SELECT parent, count(*) count from ' || p_temp_table_name ||
' WHERE child IS NULL GROUP BY parent having count(*) > 1';
l_stmt := 'DELETE FROM ' || p_temp_table_name ||
' WHERE parent = '''|| l_parent ||''' AND child IS NULL AND ROWNUM < ' || l_count;
l_deleted_count:=sql%rowcount;
VBHDEBUG(l_deleted_count || ' duplicate rows got removed for ' || l_parent );
l_dups_removed := l_dups_removed + l_deleted_count;
VBHDEBUG(l_deleted_count || ' duplicate orphan rows got removed' );
' as select a.parent, max(a.rowid) rep , count(*) count
from ' || p_temp_table_name || ' a
where a.child is null
group by a.parent
having count(*) > 1 ';
l_temp_insert_count number := 0;
l_deleted_count number := 0;
l_stmt := 'delete from ' || p_temp_table_name || ' a
where
a.child is null
and exists(
select 1 from
' || g_value_orp_dup_table || ' b
where a.parent = b.parent
and a.rowid <> b.rep )';
l_deleted_count := sql%rowcount;
VBHDEBUG(l_deleted_count || ' duplicate orphan rows got removed' );
l_dups_removed := l_dups_removed + l_deleted_count;
' as select a.parent, a.child, max(a.rowid) rep , count(*) count
from ' || p_temp_table_name || ' a
where a.child is not null
group by a.parent, a.child
having count(*) > 1 ';
l_temp_insert_count number := 0;
l_deleted_count number := 0;
l_stmt := 'delete from ' || p_temp_table_name || ' a
where
a.child is not null
and exists(
select 1 from
' || g_value_con_dup_table || ' b
where a.parent = b.parent
and a.child = b.child
and a.rowid <> b.rep )';
l_deleted_count := sql%rowcount;
VBHDEBUG(l_deleted_count || ' duplicate consolidation rows got removed' );
l_dups_removed := l_dups_removed + l_deleted_count;
l_temp_insert_count number := 0;
l_deleted_count number := 0;
l_stmt := 'SELECT parent, child, count(*) count from ' || p_temp_table_name || ' GROUP BY parent, child having count(*) > 1';
l_stmt := 'DELETE FROM ' || p_temp_table_name ||
' WHERE parent = '''|| l_parent ||''' AND child = '''||l_child||''' AND ROWNUM < ' || l_count;
l_deleted_count:=sql%rowcount;
VBHDEBUG(l_deleted_count || ' duplicate rows got removed for (' || l_parent ||',' || l_child ||')' );
l_dups_removed := l_dups_removed + l_deleted_count;
VBHDEBUG(l_deleted_count || ' duplicate consolidated rows got removed' );
l_rows_inserted Number:=0;
l_insert_count integer:=0;
l_temp_insert_count number := 0;
SELECT distinct *
FROM edw_local_set_of_books
WHERE instance IN (
select instance_code
from edw_local_instance
)
AND edw_set_of_books_id NOT IN(
SELECT DISTINCT edw_set_of_books_id
FROM edw_local_equi_set_of_books
);
l_temp_stmt:='select dim_name from edw_dimensions_md_v@' || g_target_link ||
' where DIM_LONG_NAME = :b_dimension_name';
select FND_PROFILE.VALUE('EDW_PARALLEL_SRC') into g_parallel_level from dual;
select instance_code
into g_instance_code
from edw_local_instance;
l_temp_insert_count := bulk_push_parent_child_pair( l_temp_table_name);
g_row_count:= g_row_count+ l_temp_insert_count;
l_temp_insert_count := bulk_push_orphans( l_temp_table_name);
g_row_count:= g_row_count+ l_temp_insert_count;
l_stmt := 'SELECT DISTINCT
con.consolidation_id
, con.child_edw_set_of_books_id
, con.parent_edw_set_of_books_id
, p_sob.instance
, p_sob.set_of_books_id
, p_sob.set_of_books_name
, p_sob.chart_of_accounts_id
, p_sob.description
, lookup.value_set_id
, c_sob.instance
, c_sob.set_of_books_id
, c_sob.set_of_books_name
, c_sob.value_set_id
FROM edw_local_cons_set_of_books con,
edw_local_set_of_books p_sob,
' || g_sob_vset_lookup_table || ' c_sob,
EDW_FLEX_SEG_MAPPINGS_V@' || G_TARGET_LINK || ' lookup
WHERE p_sob.edw_set_of_books_id = con.parent_edw_set_of_books_id
AND c_sob.edw_set_of_books_id = con.child_edw_set_of_books_id
AND lookup.DIMENSION_SHORT_NAME= ''' || g_dimension_name || '''
AND lower(lookup.INSTANCE_CODE)= lower(p_sob.instance)
AND lookup.structure_num=(
SELECT chart_of_accounts_id FROM GL_SETS_OF_BOOKS WHERE set_of_books_id= p_sob.set_of_books_id)
';
SELECT coa_mapping_id
From gl_consolidation
WHERE consolidation_id=l_consolidation_id;
SELECT distinct *
FROM edw_local_set_of_books
WHERE
edw_set_of_books_id=(
SELECT equi_set_of_books_id
FROM edw_local_equi_set_of_books
WHERE edw_set_of_books_id=l_parent_set_of_books.edw_set_of_books_id
);
'SELECT flex_value,description,summary_flag
FROM '|| g_value_temp_table||'
WHERE flex_value_set_id=:s1'
using l_value_set_id;
'SELECT description FROM '|| g_value_temp_table||'
WHERE flex_value_set_id=:s1
AND flex_value=:s2' using l_parent_value_set_id,l_parent_value;
insert_into_temp_table(l_temp_table_name,
l_parent_value||'-'||l_parent_set_of_books.set_of_books_id||'-'||
l_parent_set_of_books.instance,l_parent_value,l_parent_desc,
l_flex_value_desc.value||'-'||l_set_of_books.set_of_books_id
||'-'||l_set_of_books.instance,l_flex_value_desc.value,
l_flex_value_desc.description,
l_insert_count);
l_rows_inserted:=l_rows_inserted + l_insert_count;
edw_log.put_line('inserted '|| l_rows_inserted||' consolidation relationships between '||
l_set_of_books.set_of_books_name||' and '|| l_parent_set_of_books.set_of_books_name||
' into '|| l_temp_table_name);
g_row_count:= g_row_count+ l_rows_inserted;
l_rows_inserted := 0;
edw_log.put_line(g_row_count||' rows inserted');