[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select warehouse_to_instance_link
into p_db_link
from edw_source_instances
where instance_code=p_instance
and warehouse_to_instance_link is not null
and enabled_flag='Y';
PROCEDURE INSERT_INTO_EDW_SET_OF_BOOKS(
p_status out nocopy boolean,
p_errMsg out nocopy varchar2) AS
l_instance_code edw_source_instances.instance_code%TYPE;
l_insert_stmt varchar2(20000);
l_rows_inserted integer:=0;
select INSTANCE_CODE,WAREHOUSE_TO_INSTANCE_LINK
from edw_source_instances
where WAREHOUSE_TO_INSTANCE_LINK is not null
and enabled_flag ='Y'
order by instance_code;
delete from edw_set_of_books;
delete from edw_cons_set_of_books;
delete from edw_equi_set_of_books;
delete from edw_vbh_roots;
delete from edw_segment_classes;
l_insert_stmt:= 'insert into edw_set_of_books(EDW_SET_OF_BOOKS_ID,
instance,
SET_OF_BOOKS_ID,
SET_OF_BOOKS_NAME ,
CHART_OF_ACCOUNTS_ID,
description,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
select EDW_SET_OF_BOOKS_S.nextval,'''||l_instance_code||''',set_of_books_id,
name,CHART_OF_ACCOUNTS_ID,description,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
from gl_sets_of_books@'||l_instance_link
||' where CHART_OF_ACCOUNTS_ID in (select distinct STRUCTURE_NUM from edw_flex_seg_mappings_v where instance_code=:b_instance_code)';
DBMS_SQL.parse(l_cursor_id,l_insert_stmt,DBMS_SQL.V7);
l_rows_inserted:=DBMS_SQL.execute(l_cursor_id);
end insert_into_edw_set_of_books;
procedure insert_source(p_status out nocopy boolean,p_errMsg out nocopy varchar2) is
l_instance_link edw_source_instances.WAREHOUSE_TO_INSTANCE_LINK%TYPE;
l_insert_stmt varchar2(20000);
l_delete_stmt varchar2(200);
select warehouse_to_instance_link, INSTANCE_CODE
from edw_source_instances
where WAREHOUSE_TO_INSTANCE_LINK is not null
and enabled_flag='Y';
l_delete_stmt:='delete from EDW_LOCAL_SET_OF_BOOKS@'||l_instance_link;
execute immediate l_delete_stmt;
l_insert_stmt:= 'insert into EDW_LOCAL_SET_OF_BOOKS@'||l_instance_link||'
(EDW_SET_OF_BOOKS_ID,
instance,
SET_OF_BOOKS_ID,
SET_OF_BOOKS_NAME ,
CHART_OF_ACCOUNTS_ID,
description,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
select EDW_SET_OF_BOOKS_ID,
instance,
SET_OF_BOOKS_ID,
SET_OF_BOOKS_NAME ,
CHART_OF_ACCOUNTS_ID,
description,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
from edw_set_of_books
WHERE instance = ''' || l_instance_code || '''';
execute immediate l_insert_stmt;
end insert_source;
procedure insert_cons_to_source(p_status out nocopy boolean,p_errMsg out nocopy varchar2) is
l_instance_link edw_source_instances.WAREHOUSE_TO_INSTANCE_LINK%TYPE;
l_insert_stmt varchar2(20000);
l_delete_stmt varchar2(200);
l_delete_cursor_id integer;
l_insert_cursor_id integer;
l_rows_deleted integer:=0;
l_rows_inserted integer:=0;
select WAREHOUSE_TO_INSTANCE_LINK
from edw_source_instances
where WAREHOUSE_TO_INSTANCE_LINK is not null
and enabled_flag ='Y';
l_delete_cursor_id:=DBMS_SQL.OPEN_CURSOR;
l_delete_stmt:='delete from EDW_LOCAL_CONS_SET_OF_BOOKS@'||l_instance_link;
DBMS_SQL.parse(l_delete_cursor_id,l_delete_stmt,DBMS_SQL.V7);
l_rows_deleted:=DBMS_SQL.execute(l_delete_cursor_id);
DBMS_SQL.close_cursor(l_delete_cursor_id);
l_insert_cursor_id:=DBMS_SQL.OPEN_CURSOR;
l_insert_stmt:= 'insert into EDW_LOCAL_CONS_SET_OF_BOOKS@'||l_instance_link||'
(child_EDW_SET_OF_BOOKS_ID,
parent_edw_SET_OF_BOOKS_ID,
consolidation_id,
consolidation_NAME,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
select child_EDW_SET_OF_BOOKS_ID,
parent_edw_SET_OF_BOOKS_ID,
consolidation_id,
consolidation_NAME,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
from edw_cons_set_of_books';
DBMS_SQL.parse(l_insert_cursor_id,l_insert_stmt,DBMS_SQL.V7);
l_rows_inserted:=DBMS_SQL.execute(l_insert_cursor_id);
DBMS_SQL.close_cursor(l_insert_cursor_id);
end insert_cons_to_source;
procedure insert_equi_to_source(p_status out nocopy boolean, p_errMsg out nocopy varchar2) is
l_instance_link edw_source_instances.WAREHOUSE_TO_INSTANCE_LINK%TYPE;
l_insert_stmt varchar2(20000);
l_delete_stmt varchar2(200);
l_delete_cursor_id integer;
l_insert_cursor_id integer;
l_rows_deleted integer:=0;
l_rows_inserted integer:=0;
select WAREHOUSE_TO_INSTANCE_LINK
from edw_source_instances
where WAREHOUSE_TO_INSTANCE_LINK is not null
and enabled_flag='Y';
l_delete_cursor_id:=DBMS_SQL.OPEN_CURSOR;
l_delete_stmt:='delete from EDW_LOCAL_EQUI_SET_OF_BOOKS@'||l_instance_link;
DBMS_SQL.parse(l_delete_cursor_id,l_delete_stmt,DBMS_SQL.V7);
l_rows_deleted:=DBMS_SQL.execute(l_delete_cursor_id);
DBMS_SQL.close_cursor(l_delete_cursor_id);
l_insert_cursor_id:=DBMS_SQL.OPEN_CURSOR;
l_insert_stmt:= 'insert into EDW_LOCAL_EQUI_SET_OF_BOOKS@'||l_instance_link||'
(EDW_SET_OF_BOOKS_ID,
equi_SET_OF_BOOKS_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
select EDW_SET_OF_BOOKS_ID,
equi_SET_OF_BOOKS_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
from edw_equi_set_of_books';
DBMS_SQL.parse(l_insert_cursor_id,l_insert_stmt,DBMS_SQL.V7);
l_rows_inserted:=DBMS_SQL.execute(l_insert_cursor_id);
DBMS_SQL.close_cursor(l_insert_cursor_id);
end insert_equi_to_source;
l_select_stmt varchar2(2000);
l_rows_selected number;
l_select_stmt :=
'select set_of_books_id, chart_of_accounts_id, description
from gl_sets_of_books@'||p_db_link||'
where name = :b_sob_name';
dbms_sql.parse(l_cursor_id,l_select_stmt,dbms_sql.v7);
l_rows_selected:= dbms_sql.execute(l_cursor_id);
select dim_long_name
into p_wh_dimension_name
from edw_dimensions_md_v
where dim_name =(
select DIMENSION_SHORT_NAME
from edw_flex_seg_mappings_v
where lower(instance_code)=lower(p_instance)
and segment_name =p_segment_name
and structure_num=p_coa_id);
SELECT instance_code, warehouse_to_instance_link
FROM edw_source_instances
WHERE enabled_flag = 'Y';
DBMS_SQL.PARSE(cid, 'SELECT 1 FROM sys.dual@'||l_db_link, dbms_sql.native);
l_select_stmt varchar2(20000);
l_rows_selected integer:=0;
l_select_stmt:=
'select count(*)
into :b_count
from gl_consolidation@'||l_instance_link||'
where from_ledger_id =:b_from_ledger_id
and to_ledger_id=:b_to_ledger_id';
DBMS_SQL.parse(l_cursor_id,l_select_stmt,DBMS_SQL.V7);
l_rows_selected:=DBMS_SQL.execute(l_cursor_id);
l_select_stmt varchar2(20000);
l_rows_selected integer:=0;
l_select_stmt:=
'select consolidation_id
into :b_consolidation_id
from gl_consolidation@'||l_instance_link||'
where from_ledger_id =:b_from_ledger_id
and to_ledger_id=:b_to_ledger_id
and name =:b_consolidation_name';
DBMS_SQL.parse(l_cursor_id,l_select_stmt,DBMS_SQL.V7);
l_rows_selected:=DBMS_SQL.execute(l_cursor_id);
select edw_set_of_books_id,segment_name,
root_value1,root_value2,root_value3,root_value4
from edw_vbh_roots;
select instance into l_instance
from edw_set_of_books
where edw_set_of_books_id= l_edw_sob_id;
l_select_stmt varchar2(200);
select parent_edw_set_of_books_id, child_edw_set_of_books_id,
consolidation_id
from edw_cons_set_of_books
where child_edw_set_of_books_id
in (select edw_set_of_books_id from edw_vbh_roots where segment_name=p_segment_name)
and parent_edw_set_of_books_id=p_edw_sob_id;
select from_f.value_set_id
into l_from_value_set_id
from edw_flex_seg_mappings_v from_f,edw_set_of_books from_b
where from_b.edw_set_of_books_id=l_child_edw_sob_id
and from_b.chart_of_accounts_id=from_f.structure_num
and from_f.instance_code=p_instance
and from_f.segment_name=p_segment_name;
select to_f.value_set_id
into l_to_value_set_id
from edw_flex_seg_mappings_v to_f,edw_set_of_books to_b
where to_b.edw_set_of_books_id=l_parent_edw_sob_id
and to_b.chart_of_accounts_id=to_f.structure_num
and to_f.instance_code=p_instance
and to_f.segment_name=p_segment_name;
l_select_stmt:='select count(*) from edw_cons_mapping_v@'||l_db_link||
' where FROM_VALUE_SET_ID=:b_from_value_set_id and to_value_set_id=:b_to_value_set_id and consolidation_id=:b_consolidation_id';
dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
l_select_stmt:='select count(*) from edw_vbh_roots where edw_set_of_books_id=:b_problem_sob_id and root_value'||p_hierarchy_no||' is not null and segment_name =:b_segment_name';
dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
select parent_edw_set_of_books_id,child_edw_set_of_books_id,consolidation_id
from edw_cons_set_of_books
where parent_edw_set_of_books_id in (select edw_set_of_books_id from edw_vbh_roots where segment_name=p_segment_name)
and child_edw_set_of_books_id =p_edw_sob_id;
select from_f.value_set_id
into l_from_value_set_id
from edw_flex_seg_mappings_v from_f,edw_set_of_books from_b
where from_b.edw_set_of_books_id=l_child_edw_sob_id
and from_b.chart_of_accounts_id=from_f.structure_num
and from_f.instance_code=p_instance
and from_f.segment_name=p_segment_name;
select to_f.value_set_id
into l_to_value_set_id
from edw_flex_seg_mappings_v to_f,edw_set_of_books to_b
where to_b.edw_set_of_books_id=l_parent_edw_sob_id
and to_b.chart_of_accounts_id=to_f.structure_num
and to_f.instance_code=p_instance
and to_f.segment_name=p_segment_name;
l_select_stmt:='select count(*) from edw_cons_mapping_v@'||l_db_link||
' where FROM_VALUE_SET_ID=:b_from_value_set_id and to_value_set_id=:b_to_value_set_id and consolidation_id=:b_consolidation_id';
dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
l_select_stmt:='select count(*) from edw_vbh_roots where edw_set_of_books_id=:b_problem_sob_id and root_value'||p_hierarchy_no||' is not null and segment_name=:b_segment_name';
dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
l_stmt := 'select SET_OF_BOOKS_ID from edw_set_of_books where SET_OF_BOOKS_ID = '|| p_set_of_books_id;
procedure insert_set_of_books(
p_status out nocopy BOOLEAN,
p_errMsg out nocopy VARCHAR2,
p_edw_set_of_books_id NUMBER,
p_instance VARCHAR2,
p_set_of_books_id NUMBER,
p_set_of_books_name VARCHAR2,
p_chart_of_accounts_id NUMBER,
p_description VARCHAR2,
p_creation_date DATE,
p_created_by NUMBER,
p_last_update_date DATE,
p_last_updated_by NUMBER ,
p_last_update_login NUMBER) as
l_insert_stmt varchar2(20000);
l_insert_stmt:= 'insert into edw_set_of_books(EDW_SET_OF_BOOKS_ID,instance,
SET_OF_BOOKS_ID,
SET_OF_BOOKS_NAME ,
CHART_OF_ACCOUNTS_ID,
description,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)values('||p_edw_set_of_books_id ||','''|| p_instance||''','
||p_set_of_books_id||','''||p_set_of_books_name||''','
||p_chart_of_accounts_id||','''
||p_description||''' ,'''
||p_creation_date||''','
||p_created_by||','''
||p_last_update_date||''','
||p_last_updated_by||','
||p_last_update_login||')';
execute immediate l_insert_stmt;
end insert_set_of_books;