[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_package_body.delete(l_index);
' || ' || '''(l_rec_inserted_cnt - '' || l_rec_inserted_cnt ||' ||
indent(l_indent + 29) || ''')''';
elsif upper(p_procedure_name) = 'DELETE_DATAPUMP' then
l_indent := 2;
' || ' || '''(l_rec_deleted_cnt - '' || l_rec_deleted_cnt ||' ||
indent(l_indent + 29) || ''')''';
elsif upper(p_procedure_name) = 'DELETE_SOURCE' then
l_indent := 2;
' || ' || '''(l_rec_deleted_cnt - '' || l_rec_deleted_cnt ||' ||
indent(l_indent + 29) || ''')''';
indent( l_indent + 27) ||'''(l_rec_inserted_cnt) : '' || ' ||
'l_rec_inserted_cnt , 15);';
elsif upper(p_procedure_name) in ('DELETE_SOURCE', 'DELETE_DATAPUMP') then
p_proc_body := p_proc_body ||indent(2) ||
'hr_dm_utility.message(''INFO'',''Number Of records deleted '' || ' ||
indent( l_indent + 27) ||'''(l_rec_deleted_cnt) : '' || ' ||
'l_rec_deleted_cnt , 15);';
select distinct parent_table_id
from (select table_id,parent_table_id
from hr_dm_hierarchies
where hierarchy_type = 'PC')
start with table_id = p_table_info.table_id
connect by prior parent_table_id = table_id;
elsif p_cursor_type = 'DELETE_SOURCE' then
l_derive_sql := p_table_info.derive_sql_delete_source;
procedure format_selective_where_clause
(
p_text in out nocopy varchar2,
p_lpad_spaces in number default 25
) is
l_in_str varchar2(32767);
hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.format_selective_where_clause', 5);
hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.format_selective_where_clause',
25);
hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.format_selective_where_clause',
'(none)','R');
end format_selective_where_clause;
l_selective_criteria varchar2(8000);
select selective_migration_criteria
from hr_dm_migrations
where migration_id = p_table_info.migration_id
and migration_type = 'SF'
and selective_migration_criteria is not null;
and adr.last_update_date >= decode(p_migration_type, 'full',
adr.last_update_date,
p_last_update_date)
adr - table alias name, address_id - surrogate primary key.
Note: it does not have business group_id.
If cursor is for calculate range then the where clause will not contain
the first line of the where clause mentioned above.
*/
hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.get_gen_cursor_where_clause ', 5);
|| '.' || 'last_update_date >= nvl(p_last_migration_date,' ||
indent(38) || p_table_info.alias || '.last_update_date)';
elsif p_cursor_type = 'DELETE_SOURCE' THEN
--
-- put the search condition of id between start and end id if the
-- table has a surrogate id.
--
if p_table_info.surrogate_primary_key = 'Y'
then
p_where_clause := p_where_clause || p_table_info.alias || '.' ||
p_table_info.surrogate_pk_column_name || ' between p_start_id and ' ||
'p_end_id' ;
'last_update_date >= nvl(p_last_migration_date,' ||
indent(38) || p_table_info.alias || '.last_update_date)';
fetch get_sel_formula into l_selective_criteria;
format_selective_where_clause (p_text => l_selective_criteria);
|| '.formula_name in ' || l_selective_criteria;
and p_cursor_type <> 'DELETE_SOURCE' then
p_where_clause := p_where_clause || indent(p_lpad_spaces) ||
'order by ' || p_table_info.alias || '.' ||
p_table_info.surrogate_pk_column_name ;
where adr.last_update_date >= nvl(p_migration_type,
adr.last_update_date)
adr - table alias name, address_id - surrogate primary key.
Note: it does not have business group_id.
If cursor is for calculate range then the where clause will not contain
the first line of the where clause mentioned above.
*/
-- last update date comparison for additive migration of the where clause
if (p_table_info.missing_who_info = 'N') then
p_where_clause := p_where_clause || indent(17) || 'and ' ||
p_table_info.alias || '.' ||
'last_update_date >= nvl(p_last_migration_date,' ||
indent(50) || p_table_info.alias || '.last_update_date)';
l_selective_criteria varchar2(32767);
select selective_migration_criteria
from hr_dm_migrations
where migration_id = p_table_info.migration_id
and migration_type = 'SF'
and selective_migration_criteria is not null;
and exists ( select 1
from avt_ff_formulas ff
where ff.formula_id = ff1.formula_id
and ff.last_update_date >= NVL(p_last_migration_date,
ff1.last_update_date)
);
fetch get_sel_formula into l_selective_criteria;
format_selective_where_clause (p_text => l_selective_criteria);
|| '1.formula_name in ' || l_selective_criteria ||
indent(p_lpad_spaces) || 'and ';
p_where_clause := p_where_clause || ' exists ( select 1';
if p_cursor_type <> 'DELETE_SOURCE' then
p_where_clause := p_where_clause || indent(2) || 'order by ' ||
p_table_info.surrogate_pk_column_name || ';';
l_cursor_select_cols varchar2(32767);
l_cursor_select_from varchar2(32767);
l_cursor_select_where varchar2(32767);
'-- cursor to select the data from the ' ||
p_table_info.table_name || ' table to be migrated.' ||
indent || '--';
l_cursor_comment := format_comment(' cursor to select the data from the '
|| p_table_info.table_name || ' for FULL business group migration.',2 );
l_cursor_comment := format_comment(' cursor to select the data from the '
|| p_table_info.table_name || ' table to be migrated.',2);
l_cursor_comment := format_comment(' cursor to select the data from the '
|| p_table_info.table_name || ' for ADDITIVE business group migration.',2 );
l_cursor_select_cols := hr_dm_library.conv_list_to_text( p_rpad_spaces => 8,
p_columns_tbl => l_columns_tbl,
p_prefix_col => l_prefix_col);
l_cursor_select_cols := ' select distinct ' || l_cursor_select_cols;
l_cursor_select_cols := ' select ' || l_cursor_select_cols;
p_from_clause => l_cursor_select_from);
p_where_clause => l_cursor_select_where);
l_cursor_select_from := ' from ' || p_table_info.table_name || ' ' ||
p_table_info.alias || '1';
p_where_clause => l_cursor_select_where);
l_cursor_select_cols || indent ||
l_cursor_select_from || indent ||
l_cursor_select_where || indent;
l_cursor := l_cursor || indent || ' cursor csr_get_link_value is select '
|| 'hr_dm_link_value_s.nextval' || indent ||
' from dual;';
'select ' || l_parent_column_name || indent(33) ||
'from ' || l_parent_table_name ||
indent(33) || 'where ' || rtrim(l_where_clause) || ';';
'select id_flex_structure_name' ||
indent(33) || ' || ''-dm-dev-key-' || l_code || '''' ||
indent(33) || 'from fnd_id_flex_structures_vl' ||
indent(33) || 'where ' || rtrim(l_where_clause) ||
indent(33) || ' and id_flex_code = ''' || l_code || ''';';
select h.column_name
from hr_dm_hierarchies h,
hr_dm_tables t
where h.hierarchy_type = 'N'
and h.table_id = t.table_id
and t.table_name = upper(p_table_info.table_name);
l_dp_func_name := 'hrdpp_h' || p_table_info.short_name || '.insert_batch_lines';
l_dp_func_name := 'hrdpp_u' || p_table_info.short_name || '.insert_batch_lines';
' l_rec_inserted_cnt number := 0;' || indent ||
'l_rec_inserted_cnt := 0;' || indent(l_indent) || 'begin';
l_comment := 'insert the data into batch_lines table of datapump for ' ||
'all columns of ' ||p_table_info.table_name ||
' and assign null values to hierarchy columns.';
'l_rec_inserted_cnt := l_rec_inserted_cnt + 1;' || indent(l_indent) ||
l_indent) || indent(l_indent) || 'if mod (l_rec_inserted_cnt, ' ||
'p_chunk_size) = 0 then ' || indent(l_indent + 2) || 'commit;' ||
'p_rec_downloaded := l_rec_inserted_cnt;' || indent(l_indent - 4) ||
'l_rec_inserted_cnt);' || indent;
l_cursor_select_cols varchar2(32767);
l_cursor_select_from varchar2(32767);
l_cursor_select_where varchar2(32767);
l_cursor_select_cols := ' select min( ' || p_table_info.alias || '.' ||
p_table_info.surrogate_pk_column_name || ')' || indent(10) || ',max( '||
p_table_info.alias || '.' || p_table_info.surrogate_pk_column_name || ')';
p_from_clause => l_cursor_select_from);
p_where_clause => l_cursor_select_where,
p_cursor_type => 'CALCULATE_RANGES');
l_cursor_select_cols || indent ||
l_cursor_select_from || indent ||
l_cursor_select_where || indent;
format_comment('No rows selected in this table for download.',l_indent + 2) ||
indent(l_indent + 2) || 'close csr_get_pk_min_max_val;' ||
'insert the range records equal to the number of threads available for' ||
'processing',2);
'insert into hr_dm_migration_ranges ( range_id ' ;
'insert into hr_dm_migration_ranges ( range_id ' ;
l_cursor_select_cols varchar2(32767);
l_cursor_select_from varchar2(32767);
l_cursor_select_where varchar2(32767);
l_cursor_select_cols := ' select rowid row_id ' ;
l_cursor_select_from := ' from hrdpv_u'|| p_table_info.short_name;
l_cursor_select_where := ' where batch_id = p_batch_id ' || indent ||
' and p_start_id <= (select to_number(' || g_surrogate_pk_col_param ||
') from dual)' || indent ||
' and p_end_id >= (select to_number(' || g_surrogate_pk_col_param ||
') from dual);';
l_cursor_select_where := ' where batch_id = p_batch_id;';
l_cursor_select_cols || indent ||
l_cursor_select_from || indent ||
l_cursor_select_where || indent;
procedure generate_delete_datapump
(
p_table_info in hr_dm_gen_main.t_table_info,
p_header in out nocopy varchar2
)
is
l_interface varchar2(32767);
hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.generate_delete_datapump', 5);
'|| ''delete_datapump'';' || indent ||
' l_rec_deleted_cnt number := 0;' || indent;
'l_rec_deleted_cnt := 0;';
p_procedure_name => 'delete_datapump',
p_message_location => 'START',
p_proc_body => l_debug_message_text);
l_comment := format_comment('delete the data from the data pump view for this'
|| ' table.',l_indent);
'delete from hrdpv_u' || p_table_info.short_name || indent(l_indent) ||
'where rowid = ' || l_cursor_rec || '.row_id;';
'l_rec_deleted_cnt := l_rec_deleted_cnt + 1;' || indent(l_indent) ||
l_indent) || indent(l_indent) || 'if mod (l_rec_deleted_cnt, ' ||
'p_chunk_size) = 0 then ' || indent(l_indent + 2) || 'commit;' ||
p_procedure_name => 'delete_datapump',
p_message_location => 'END',
p_proc_body => l_debug_message_text);
l_proc_comment := format_comment('procedure to delete a range of '
|| upper(p_table_info.table_name) || ' data from datapump interface table.')||
indent;
l_proc_comment := format_comment('procedure to delete data of '
|| upper(p_table_info.table_name) || ' from datapump interface table.')||
indent;
p_header := p_header || l_proc_comment ||'procedure delete_datapump' ||
l_interface || ';';
l_proc_body_tbl(1) := l_proc_comment || 'procedure delete_datapump' ||
l_interface || ' is' || l_locals || l_cursor || l_proc_body;
hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.generate_delete_datapump',
25);
hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.generate_delete_datapump',
'(none)','R');
end generate_delete_datapump;
procedure prepare_delete_source_cursor
(
p_table_info in hr_dm_gen_main.t_table_info,
p_cursor out nocopy varchar2
)
is
l_cursor_comment varchar2(2000);
l_cursor_select_cols varchar2(32767);
l_cursor_select_from varchar2(32767);
l_cursor_select_where varchar2(32767);
hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.prepare_delete_source_cursor', 5);
l_cursor_select_cols := ' select distinct ' || p_table_info.alias ||
'.rowid row_id ';
l_cursor_select_cols := ' select ' || p_table_info.alias ||
'.rowid row_id ';
p_from_clause => l_cursor_select_from);
p_where_clause => l_cursor_select_where,
p_cursor_type => 'DELETE_SOURCE');
l_cursor_select_cols || indent ||
l_cursor_select_from || indent ||
l_cursor_select_where || indent;
hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.prepare_delete_source_cursor',
25);
hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.prepare_delete_source_cursor',
'(none)','R');
end prepare_delete_source_cursor;
procedure generate_delete_source
(
p_table_info in hr_dm_gen_main.t_table_info,
p_header in out nocopy varchar2
)
is
l_interface varchar2(32767);
hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.generate_delete_source', 5);
'|| ''delete_source'';' || indent ||
' l_rec_deleted_cnt number := 0;' || indent;
prepare_delete_source_cursor (p_table_info,
l_cursor);
'l_rec_deleted_cnt := 0;';
p_procedure_name => 'delete_source',
p_message_location => 'START',
p_proc_body => l_debug_message_text);
l_comment := format_comment('delete the row read from the table.',l_indent);
'delete from ' || p_table_info.table_name || indent(l_indent) ||
'where rowid = ' || l_cursor_rec || '.row_id;';
'l_rec_deleted_cnt := l_rec_deleted_cnt + 1;' || indent(l_indent) ||
l_indent) || indent(l_indent) || 'if mod (l_rec_deleted_cnt, ' ||
'p_chunk_size) = 0 then ' || indent(l_indent + 2) || 'commit;' ||
p_procedure_name => 'delete_source',
p_message_location => 'END',
p_proc_body => l_debug_message_text);
l_proc_comment := format_comment('procedure to delete data of '
|| upper(p_table_info.table_name) || ' for a given business group.')||
indent;
p_header := p_header || l_proc_comment ||'procedure delete_source' ||
l_interface || ';';
l_proc_body_tbl(1) := l_proc_comment || 'procedure delete_source' ||
l_interface || ' is' || l_locals || l_cursor || l_proc_body;
hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.generate_delete_source',
25);
hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.generate_delete_source',
'(none)','R');
end generate_delete_source;
generate_delete_datapump(p_table_info,
l_header );
generate_delete_source(p_table_info,
l_header);