[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
type t_delete_dml is record
(
where_clause varchar2(32767),
call_to_proc varchar2(32767),
proc_parameters varchar2(32767)
);
g_delete_dml t_delete_dml;
g_exception_tbl.delete;
g_package_body.delete(l_index);
g_resolve_pk_columns_tbl.delete(l_index);
'select ' || l_parent_id_column_name || indent(4) ||
'from ' || l_parent_table_name ||
indent(4) || 'where ' || rtrim(l_where_clause) || ';';
procedure prepare_ins_dt_delete_stmt
(
p_table_info in hr_dm_gen_main.t_table_info,
p_proc_body in out nocopy varchar2 ,
p_ins_type in varchar2 default 'P',
p_indent in number
)
is
l_table_name varchar2(30) := upper(p_table_info.upload_table_name);
hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.prepare_ins_dt_delete_stmt ', 5);
'hr_dm_library.ins_dt_delete (p_id => ' || 'p_' ||
rpad(g_pk_columns_tbl(1),28) ||',' ||indent(p_indent + 29) ||
'p_table_name => '''|| l_table_name || ''',' ||
indent(p_indent + 29) || 'p_ins_type => ''' || p_ins_type ||
''');' ;
'hr_dm_library.ins_dt_delete ( p_table_name => '''|| l_table_name
|| '''' || indent(p_indent + 29) || ',p_ins_type => ''' ||
p_ins_type || '''' ;
hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.prepare_ins_dt_delete_stmt',
25);
hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.prepare_ins_dt_delete_stmt',
'(none)','R');
end prepare_ins_dt_delete_stmt;
procedure prepare_chk_dt_delete_stmt
(
p_table_info in hr_dm_gen_main.t_table_info,
p_proc_body in out nocopy varchar2 ,
p_indent in number
)
is
l_proc_name varchar2(30) ;
hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.prepare_chk_dt_delete_stmt ', 5);
'hr_dm_library.chk_row_in_dt_delete (p_id => p_' ||
rpad(g_pk_columns_tbl(1),28) ||',' ||indent(p_indent + 30) ||
'p_table_name => '''|| l_table_name || ''',' ||
indent(p_indent + 30) || 'p_ins_type => l_ins_type,' ||
indent(p_indent + 30) || 'p_row_exists => l_row_exists);';
l_proc_name := 'chk_row_in_dt_delete_' || to_char(g_no_of_pk_columns) ||
'_pkcol' ;
hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.prepare_chk_dt_delete_stmt',
25);
hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.prepare_chk_dt_delete_stmt',
'(none)','R');
end prepare_chk_dt_delete_stmt;
' row is inserted if a logical record does not exist (excluding the logical'
|| ' record created by other slave processes). if logical record exists for'
|| ' a Application data type migration, then logical record is deleted from '
|| ' destination and a record is created into ' ||
'dt_delete table with type ''D'' (so as other physical records for this ' ||
'logical record will skip this check) and will upload the ' ||
'data. If logical record exists for'
|| ' any other type of data migration then a record is created into ' ||
'dt_delete table with type ''P''.', l_indent) || indent;
' row is inserted if a logical record does not exist (excluding the logical'
|| ' record created by other slave processes). if logical record exists for'
|| ' a Application data type migration, then logical record is deleted from '
|| ' destination and a record is created into ' ||
'dt_delete table with type ''D'' (so as other physical records for this ' ||
'logical record will skip this check) and will upload the ' ||
'data. If logical record exists for'
|| ' any other type of data migration then a record is created into ' ||
'dt_delete table with type ''P''.', l_indent) || indent;
' row is inserted if a logical record does not exist (excluding the logical'
|| ' record created by other slave processes). if logical record exists for'
|| ' a Application data type migration, then logical record is deleted from '
|| ' destination and a record is created into ' ||
'dt_delete table with type ''D'' (so as other physical records for this ' ||
'logical record will skip this check) and will upload the ' ||
'data. If logical record exists for'
|| ' any other type of data migration then a record is created into ' ||
'dt_delete table with type ''P''.', l_indent) || indent;
indent(l_indent + 2) || 'l_insert := TRUE;' || indent(l_indent) || 'else' ||
'Find out nocopy if the row already exists for this id in the dt_deletes table '||
'in the database or not.',
p_indent_spaces => l_indent) || indent;
prepare_chk_dt_delete_stmt
( p_table_info => p_table_info,
p_proc_body => l_proc_body,
p_indent => l_indent);
'''D'' then ' || indent(l_indent + 4) || 'l_insert := TRUE;' ||
|| 'l_insert := FALSE;' || indent(l_indent + 2) || 'end if; -- l_row_exists'
format_comment('create a row into dt_deletes table of type ''D'',so the' ||
' subsequent physical records can be uploaded without performing any ' ||
'checks.',l_indent+2) || indent(l_indent + 2);
prepare_ins_dt_delete_stmt
( p_table_info => p_table_info,
p_proc_body => l_proc_body,
p_ins_type => 'D',
p_indent => l_indent + 2);
'l_insert := TRUE;' || indent(l_indent) || 'else -- row already exists' ||
'l_insert := FALSE;' || indent(l_indent) || 'else -- row already exists' ||
format_comment('if migration_type is application then update the row',
l_indent,'N') || indent(l_indent - 2);
prepare_ins_dt_delete_stmt
( p_table_info => p_table_info,
p_proc_body => l_proc_body,
p_ins_type => 'P',
p_indent => l_indent);
'l_insert := FALSE;';
format_comment('create a row into dt_deletes table of type ''D'',so the' ||
' subsequent physical records can be uploaded without performing any ' ||
'checks.',l_indent,'N') || indent(l_indent) ;
prepare_ins_dt_delete_stmt
( p_table_info => p_table_info,
p_proc_body => l_proc_body,
p_ins_type => 'D',
p_indent => l_indent);
format_comment('call delete_dml procedure to delete all existing rows for' ||
' the given id',l_indent);
l_proc_body := l_proc_body || indent(l_indent) || g_delete_dml.call_to_proc;
'delete_dml( ' || hr_dm_library.conv_list_to_text(
p_rpad_spaces => l_indent + 11,
p_pad_first_line => 'N',
p_prefix_col => 'p_',
p_columns_tbl => g_pk_columns_tbl,
p_col_length => 28) || ');';
'l_insert := TRUE;';
format_comment('create a row into dt_deletes table of type ''P'',so the' ||
' subsequent physical records can skip these checks and info will be used' ||
'for reporting.',l_indent,'N') || indent(l_indent);
prepare_ins_dt_delete_stmt
( p_table_info => p_table_info,
p_proc_body => l_proc_body,
p_ins_type => 'P',
p_indent => l_indent);
l_proc_body := l_proc_body || indent(l_indent) || 'l_insert := FALSE;' ||
indent(l_indent + 2) || 'l_insert := TRUE;' || indent(l_indent) ||
'inserted if it does not exist or updated if row exists for a Application' ||
' data type migration, otherwise, a record is created in DT_DELETES table.',
l_indent) || indent;
'inserted, if it does not exist or updated if row exists for a Application' ||
' or Full data type migration, otherwise,a record is created in DT_DELETES table.',
l_indent) || indent;
'inserted, if it does not exist or updated if row exists for a Application' ||
' or Full data type migration, otherwise,a record is created in DT_DELETES table.',
l_indent) || indent;
'if l_row_exists = ''N'' then' || indent(l_indent + 2) ||'l_insert := TRUE;'
|| indent(l_indent + 2) ||'l_update := FALSE;' || indent(l_indent) ||
format_comment('if migration_type is application then update the row',
l_indent + 2);
indent(l_indent) || 'l_update := TRUE;'||
indent(l_indent - 2) || 'else' || indent(l_indent) ||'l_update := FALSE;' ||
indent(l_indent) || '-- write into dt_deletes_table' || indent(l_indent);
prepare_ins_dt_delete_stmt
( p_table_info => p_table_info,
p_proc_body => l_proc_body,
p_ins_type => 'P',
p_indent => l_indent);
indent(l_indent + 2) || 'l_insert := TRUE;' || indent(l_indent) ||
' l_insert boolean := FALSE;' || indent ||
' l_update boolean := FALSE;' || indent ||
'-- if l_insert flag is true then insert the row. ' ||indent(l_indent )
|| 'if l_insert then ' || indent(l_indent + 2)
|| 'insert_dml (' ;
'-- if l_update flag is true then update the row. ' ||indent(l_indent)
|| 'if l_update then ' || indent(l_indent + 2)
|| 'update_dml (' ;
l_cursor_select_cols varchar2(32767);
l_cursor_select_from varchar2(32767);
l_cursor_select_where varchar2(32767);
l_cursor_select_cols := ' select ' || p_table_info.surrogate_pk_column_name;
l_cursor_select_cols := ' select ''1''' ;
p_from_clause => l_cursor_select_from);
l_cursor_select_from := ' from ' || p_table_info.upload_table_name || ' ' || p_table_info.alias;
p_where_clause => l_cursor_select_where);
l_cursor_select_where := g_chk_row_exists.where_clause ;
l_cursor_select_where := ' where' || hr_dm_library.get_func_asg (
p_rpad_spaces => 0,
p_columns_tbl => g_pk_columns_tbl,
p_prefix_left_asg => p_table_info.alias || '.' ,
p_prefix_right_asg => 'p_',
p_omit_business_group_id => 'N',
p_comma_on_first_line => 'N',
p_pad_first_line => 'N',
p_equality_sign => ' = ',
p_left_asg_pad_len => 80,
p_right_asg_pad_len => 80,
p_start_terminator => ' and ',
p_test_with_nvl => 'Y',
p_table_name => upper(p_table_info.upload_table_name))
||';';
l_cursor_select_cols || indent ||
l_cursor_select_from || indent ||
l_cursor_select_where || indent;
l_delete_operand varchar2(30);
l_delete_seperator varchar2(30);
select ORACLE_USERNAME
from fnd_oracle_userid
where ORACLE_ID = 900;
select lower(hir.column_name) column_name ,
lower(col.data_type) data_type,
hir.hierarchy_type,
lower(dmt.table_alias) table_alias,
tbl.table_name table_name
from all_tab_columns col,
hr_dm_tables tbl,
hr_dm_tables dmt,
hr_dm_hierarchies hir
where tbl.table_name = nvl(dmt.upload_table_name, dmt.table_name)
and dmt.table_id = p_table_info.table_id
and hierarchy_type = 'R'
and tbl.table_id = hir.table_id
and col.table_name = tbl.table_name
and col.column_name = hir.column_name
and col.owner in
(l_apps_name,
l_fnd_owner,
l_ff_owner,
l_ben_owner,
l_pay_owner,
l_per_owner);
g_delete_dml.where_clause := ' where ';
g_delete_dml.call_to_proc := 'delete_dml (';
g_delete_dml.proc_parameters := '(';
l_delete_operand := '';
l_delete_seperator := '';
g_delete_dml.where_clause := g_delete_dml.where_clause ||
l_delete_operand || ' ' || l_nvl_left ||
csr_get_chk_columns_rec.table_alias || '.' ||
substr(csr_get_chk_columns_rec.column_name,1,28) || l_nvl_right || ' = ' ||
rpad(l_nvl_left ||'p_' ||
csr_get_chk_columns_rec.column_name || l_nvl_right,80) || indent(2);
g_delete_dml.proc_parameters := g_delete_dml.proc_parameters
|| l_delete_seperator || rpad('p_' || csr_get_chk_columns_rec.column_name,30) || ' ' ||
csr_get_chk_columns_rec.data_type || indent;
l_delete_operand := 'and ';
g_delete_dml.call_to_proc := g_delete_dml.call_to_proc
|| l_delete_seperator || rpad('p_' || csr_get_chk_columns_rec.column_name,30) ||
indent (l_indent + 15);
l_delete_seperator := ',';
g_delete_dml.call_to_proc := g_delete_dml.call_to_proc
|| l_delete_seperator || rpad('l_' || csr_get_chk_columns_rec.column_name,30) ||
indent (l_indent + 15);
l_delete_seperator := ',';
g_delete_dml.where_clause := g_delete_dml.where_clause || ';';
g_delete_dml.call_to_proc := g_delete_dml.call_to_proc || ');';
g_delete_dml.proc_parameters := g_delete_dml.proc_parameters || ')';
procedure generate_insert_dml
(
p_table_info in hr_dm_gen_main.t_table_info
)
is
l_interface varchar2(32767);
hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.generate_insert_dml', 5);
'|| ''insert_dml'';' || indent;
' select 1' || indent(l_indent) ||
' from ' || p_table_info.upload_table_name || indent(l_indent) ||
' where ' || p_table_info.surrogate_pk_column_name || ' = ' ||
' p_' || p_table_info.surrogate_pk_column_name || ';'
' select ' || p_table_info.sequence_name ||
'.nextval' || indent(l_indent) ||
' from dual;'
format_comment('insert the row', l_indent) || indent ||
' insert into ' || p_table_info.upload_table_name || indent(l_indent)
|| '( ' || indent(l_indent) ||
hr_dm_library.conv_list_to_text (
p_rpad_spaces => l_indent + 1,
p_pad_first_line => 'Y',
p_columns_tbl => g_columns_tbl);
');' || indent || 'end insert_dml;';
format_comment('see if id value already used and no DT_DELETE' ||
' entry exists.', l_indent) || indent;
prepare_chk_dt_delete_stmt
(p_table_info => p_table_info,
p_proc_body => l_temp,
p_indent => l_indent);
format_comment('insert the row', l_indent) || indent ||
' insert into ' || p_table_info.upload_table_name || indent(l_indent)
|| '( ' || indent(l_indent) ||
hr_dm_library.conv_list_to_text (
p_rpad_spaces => l_indent + 1,
p_pad_first_line => 'Y',
p_columns_tbl => g_columns_tbl);
');' || indent || 'end insert_dml;';
l_proc_comment := format_comment('procedure to insert a row in '
|| upper(p_table_info.upload_table_name) || 'table.')||
indent;
l_proc_body_tbl(1) := l_proc_comment || 'procedure insert_dml' ||
l_proc_body_tbl(1) || ' is' || l_locals || 'begin' ||indent;
hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.generate_insert_dml',
25);
hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.generate_insert_dml',
'(none)','R');
end generate_insert_dml;
procedure generate_update_dml
(
p_table_info in hr_dm_gen_main.t_table_info
)
is
l_interface varchar2(32767);
hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.generate_update_dml', 5);
'|| ''update_dml'';' || indent;
l_proc_body_tbl(l_proc_index) := format_comment('update all columns of' ||
' the row.', l_indent) || indent ||
' update ' || p_table_info.upload_table_name || ' ' || p_table_info.alias
|| indent(l_indent) || 'set ' ||
hr_dm_library.get_func_asg (
p_rpad_spaces => l_indent + 4,
p_columns_tbl => l_columns_tbl,
p_prefix_left_asg => null ,
p_omit_business_group_id => 'N',
p_comma_on_first_line => 'N',
p_pad_first_line => 'N',
p_equality_sign => ' = ');
' contain any columns other than primary key column. Hence no update' ||
' statement is required.', l_indent) || indent(l_indent) || 'null;';
l_proc_body_tbl(l_proc_index) := indent || 'end update_dml;';
l_proc_comment := format_comment('procedure to update a row in '
|| upper(p_table_info.upload_table_name) || ' table.') || indent;
l_proc_body_tbl(1) := l_proc_comment || 'procedure update_dml' ||
l_proc_body_tbl(1) || ' is'|| l_locals || indent ||
'begin' || indent;
hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.generate_update_dml',
25);
hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.generate_update_dml',
'(none)','R');
end generate_update_dml;
indent||format_comment('update all columns of the row.',l_indent);
l_proc_body_tbl(l_proc_index) := indent(l_indent)||'update ' ||
p_table_info.upload_table_name ||
indent(l_indent)||'set ';
procedure generate_delete_dml
(
p_table_info in hr_dm_gen_main.t_table_info
)
is
l_interface varchar2(32767);
hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.generate_delete_dml', 5);
l_interface := indent || g_delete_dml.proc_parameters || indent;
'|| ''delete_dml'';' || indent;
l_proc_body := format_comment('delete the logical records for the given id.',
l_indent) || indent;
' delete ff_compiled_info_f where formula_id in (
select '||p_table_info.alias||'.formula_id
from ff_formulas_f '||p_table_info.alias;
replace(g_delete_dml.where_clause,';','')||');'||indent(l_indent) ;
' delete ' || p_table_info.upload_table_name || ' ' ||
p_table_info.alias;
g_delete_dml.where_clause ;
l_proc_body := l_proc_body || indent || 'end delete_dml;';
l_proc_comment := format_comment('procedure to delete the logical record for'
|| ' a given id in '|| upper(p_table_info.upload_table_name) || ' table.')||
indent;
l_proc_body_tbl(1) := l_proc_comment || 'procedure delete_dml' ||
l_interface || ' is' || l_locals || 'begin' ||
indent || l_proc_body ;
hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.generate_delete_dml',
25);
hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.generate_delete_dml',
'(none)','R');
end generate_delete_dml;
generate_insert_dml (p_table_info);
generate_update_dml (p_table_info );
generate_delete_dml (p_table_info);