47: p_newline => p_newline);
48: return l_spaces;
49: exception
50: when others then
51: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.indent',
52: '(p_indent_spaces - ' || p_indent_spaces ||
53: ')(p_newline - ' || p_newline || ')',
54: 'R');
55: end indent;
145: end if;
146: return l_comment_text;
147: exception
148: when others then
149: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.format_comment',
150: '(p_ins_blank_lines - ' || p_ins_blank_lines ||
151: ')(p_indent_spaces - ' || p_indent_spaces ||
152: ')(p_comment_text - ' || p_comment_text || ')'
153: ,'R');
158: -------------------------------------------------------------------------------
159: procedure init_package_body is
160: l_index number := g_package_body.first;
161: begin
162: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.init_package_body', 5);
163:
164: -- delete all elements from package body pl/sql table.
165: while l_index is not null loop
166: g_package_body.delete(l_index);
167: l_index := g_package_body.next(l_index);
168: end loop;
169: --initialize the index
170: g_package_index := 0;
171: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.init_package_body',
172: 25);
173: exception
174: when others then
175: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.init_package_body',
171: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.init_package_body',
172: 25);
173: exception
174: when others then
175: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.init_package_body',
176: '(none)','R');
177: raise;
178: end init_package_body;
179: -- -----------------------add_debug_messages ---------------------------------
263:
264: if p_message_location = 'START' then
265: p_proc_body := indent(l_indent) || '-- debug messages ';
266: p_proc_body := p_proc_body || indent(l_indent) ||
267: 'hr_dm_utility.message(''ROUT'',''entry:' || l_package_name ||
268: '.' || p_procedure_name || ''', 5);' || indent(l_indent);
269:
270: p_proc_body := p_proc_body || 'hr_dm_utility.message(''PARA'','
271: || l_parameter_string || ', 10);';
266: p_proc_body := p_proc_body || indent(l_indent) ||
267: 'hr_dm_utility.message(''ROUT'',''entry:' || l_package_name ||
268: '.' || p_procedure_name || ''', 5);' || indent(l_indent);
269:
270: p_proc_body := p_proc_body || 'hr_dm_utility.message(''PARA'','
271: || l_parameter_string || ', 10);';
272:
273: end if;
274:
279: -- procedure specific debug messages.
280:
281: if upper(p_procedure_name) in ('DOWNLOAD', 'DOWNLOAD_HIERARCHY') then
282: p_proc_body := p_proc_body ||indent(2) ||
283: 'hr_dm_utility.message(''INFO'',''Number Of records downloaded '' || ' ||
284: indent( l_indent + 27) ||'''(l_rec_inserted_cnt) : '' || ' ||
285: 'l_rec_inserted_cnt , 15);';
286:
287: elsif upper(p_procedure_name) = 'CALCULATE RANGES' then
285: 'l_rec_inserted_cnt , 15);';
286:
287: elsif upper(p_procedure_name) = 'CALCULATE RANGES' then
288: p_proc_body := p_proc_body ||indent(2) ||
289: 'hr_dm_utility.message(''INFO'',''Range Value '' || ' ||
290: indent( l_indent + 27) ||'''(l_range_value) : '' || ' ||
291: 'l_range_value , 15);';
292:
293: elsif upper(p_procedure_name) in ('DELETE_SOURCE', 'DELETE_DATAPUMP') then
291: 'l_range_value , 15);';
292:
293: elsif upper(p_procedure_name) in ('DELETE_SOURCE', 'DELETE_DATAPUMP') then
294: p_proc_body := p_proc_body ||indent(2) ||
295: 'hr_dm_utility.message(''INFO'',''Number Of records deleted '' || ' ||
296: indent( l_indent + 27) ||'''(l_rec_deleted_cnt) : '' || ' ||
297: 'l_rec_deleted_cnt , 15);';
298: end if;
299:
297: 'l_rec_deleted_cnt , 15);';
298: end if;
299:
300: p_proc_body := p_proc_body ||indent(2) ||
301: 'hr_dm_utility.message(''ROUT'',''exit:' || l_package_name ||
302: '.' || p_procedure_name || ''', 25);';
303:
304: p_proc_body := p_proc_body || indent || 'exception' ||
305: indent(2) || 'when others then ' || indent(4) ||
302: '.' || p_procedure_name || ''', 25);';
303:
304: p_proc_body := p_proc_body || indent || 'exception' ||
305: indent(2) || 'when others then ' || indent(4) ||
306: 'hr_dm_utility.error(SQLCODE,''' || l_package_name || '.' ||
307: p_procedure_name || ''', ' || indent(l_indent + 29) || l_parameter_string ||
308: l_local_variable_string || ',''R'');' || indent(2) || ' raise;' ||
309: indent || 'end ' || p_procedure_name|| ';';
310: end if;
308: l_local_variable_string || ',''R'');' || indent(2) || ' raise;' ||
309: indent || 'end ' || p_procedure_name|| ';';
310: end if;
311:
312: hr_dm_utility.message('PARA','(p_proc_body_tbl - table of varchar2' ,10);
313:
314: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.add_debug_messages',
315: 25);
316: exception
310: end if;
311:
312: hr_dm_utility.message('PARA','(p_proc_body_tbl - table of varchar2' ,10);
313:
314: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.add_debug_messages',
315: 25);
316: exception
317: when others then
318: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.add_debug_messages',
314: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.add_debug_messages',
315: 25);
316: exception
317: when others then
318: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.add_debug_messages',
319: '(none)','R');
320: raise;
321: end add_debug_messages;
322: -- -----------------------add_to_package_body; ---------------------------------
339: l_string_index number; -- variable to read the string characters
340: l_loop_cnt number;
341: begin
342:
343: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.add_to_package_body-1', 5);
344: hr_dm_utility.message('PARA','(p_proc_body_tbl - table of varchar2' ,10);
345:
346: while l_proc_index is not null loop
347:
340: l_loop_cnt number;
341: begin
342:
343: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.add_to_package_body-1', 5);
344: hr_dm_utility.message('PARA','(p_proc_body_tbl - table of varchar2' ,10);
345:
346: while l_proc_index is not null loop
347:
348: l_string_index := 1;
367: end loop;
368:
369: l_proc_index := p_proc_body_tbl.next(l_proc_index);
370: end loop;
371: hr_dm_utility.message('INFO',
372: '(l_loop_cnt - ' || l_loop_cnt ||
373: ')(l_string_index - ' ||l_string_index ||
374: ')( g_package_index - ' || g_package_index || ')'
375: ,15);
372: '(l_loop_cnt - ' || l_loop_cnt ||
373: ')(l_string_index - ' ||l_string_index ||
374: ')( g_package_index - ' || g_package_index || ')'
375: ,15);
376: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.add_to_package_body -1',
377: 25);
378: exception
379: when others then
380: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.add_to_package_body-1',
376: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.add_to_package_body -1',
377: 25);
378: exception
379: when others then
380: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.add_to_package_body-1',
381: '(l_loop_cnt - ' || l_loop_cnt ||
382: ')(l_string_index - ' ||l_string_index ||
383: ')( g_package_index - ' || g_package_index || ')'
384: ,'R');
404: l_end_ptr number;
405: l_where_string varchar2(25) := 'where';
406: l_terminator varchar2(5) := ';';
407: begin
408: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.get_derive_from_clause', 5);
409: hr_dm_utility.message('PARA','(p_from_clause - ' || p_from_clause ||
410: ')(p_lpad_spaces - ' || p_lpad_spaces ||
411: ')', 10);
412: l_derive_sql := p_table_info.derive_sql_source_tables;
405: l_where_string varchar2(25) := 'where';
406: l_terminator varchar2(5) := ';';
407: begin
408: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.get_derive_from_clause', 5);
409: hr_dm_utility.message('PARA','(p_from_clause - ' || p_from_clause ||
410: ')(p_lpad_spaces - ' || p_lpad_spaces ||
411: ')', 10);
412: l_derive_sql := p_table_info.derive_sql_source_tables;
413:
436: exit;
437: end if;
438: end loop;
439:
440: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.get_derive_from_clause',
441: 25);
442: hr_dm_utility.message('PARA','(p_from_clause - ' || p_from_clause || ')',30);
443: exception
444: when others then
438: end loop;
439:
440: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.get_derive_from_clause',
441: 25);
442: hr_dm_utility.message('PARA','(p_from_clause - ' || p_from_clause || ')',30);
443: exception
444: when others then
445: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.get_derive_from_clause',
446: '(l_derive_sql - ' || l_derive_sql ||
441: 25);
442: hr_dm_utility.message('PARA','(p_from_clause - ' || p_from_clause || ')',30);
443: exception
444: when others then
445: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.get_derive_from_clause',
446: '(l_derive_sql - ' || l_derive_sql ||
447: ')(l_end_ptr - ' || l_end_ptr ||
448: ')(p_from_clause - ' || p_from_clause || ')'
449: ,'R');
477: start with table_id = p_table_info.table_id
478: connect by prior parent_table_id = table_id;
479: begin
480:
481: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.get_cursor_from_clause', 5);
482:
483: -- check if the from clause is defined for this table i.e
484: -- derive_sql_source_tables field is not null. If yes then
485: -- call get_derive_from_clause procedure to format the from
505: p_from_clause := p_from_clause || indent(p_lpad_spaces + 5) || ',' ||
506: l_parent_table_info.table_name ||' ' || l_parent_table_info.alias;
507: end loop;
508: end if;
509: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.get_cursor_from_clause',
510: 25);
511: hr_dm_utility.message('PARA','(p_from_clause - ' || p_from_clause,30);
512: exception
513: when others then
507: end loop;
508: end if;
509: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.get_cursor_from_clause',
510: 25);
511: hr_dm_utility.message('PARA','(p_from_clause - ' || p_from_clause,30);
512: exception
513: when others then
514: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.get_cursor_from_clause',
515: '(none)','R');
510: 25);
511: hr_dm_utility.message('PARA','(p_from_clause - ' || p_from_clause,30);
512: exception
513: when others then
514: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.get_cursor_from_clause',
515: '(none)','R');
516: p_from_clause := null;
517: raise;
518: end get_cursor_from_clause;
551: l_end_ptr number;
552: l_where_string varchar2(25) := 'where ';
553: l_terminator varchar2(5) := ';';
554: begin
555: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.get_derive_where_clause', 5);
556: hr_dm_utility.message('PARA','(p_cursor_type- ' || p_cursor_type ,10);
557:
558: if p_cursor_type = 'DOWNLOAD' then
559: l_derive_sql := p_table_info.derive_sql_download_full;
552: l_where_string varchar2(25) := 'where ';
553: l_terminator varchar2(5) := ';';
554: begin
555: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.get_derive_where_clause', 5);
556: hr_dm_utility.message('PARA','(p_cursor_type- ' || p_cursor_type ,10);
557:
558: if p_cursor_type = 'DOWNLOAD' then
559: l_derive_sql := p_table_info.derive_sql_download_full;
560: elsif p_cursor_type = 'DOWNLOAD_DT' then
594: end if;
595: end loop;
596:
597: p_where_clause := p_where_clause || l_terminator;
598: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.get_derive_where_clause',
599: 25);
600: hr_dm_utility.message('PARA','(p_where_clause - ' || p_where_clause,30);
601: exception
602: when others then
596:
597: p_where_clause := p_where_clause || l_terminator;
598: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.get_derive_where_clause',
599: 25);
600: hr_dm_utility.message('PARA','(p_where_clause - ' || p_where_clause,30);
601: exception
602: when others then
603: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.get_derive_where_clause',
604: '(none)','R');
599: 25);
600: hr_dm_utility.message('PARA','(p_where_clause - ' || p_where_clause,30);
601: exception
602: when others then
603: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.get_derive_where_clause',
604: '(none)','R');
605: raise;
606: end get_derive_where_clause;
607: -- ----------------------- format_selective_where_clause -------------------------
622: l_out_str varchar2(32767);
623: l_start_ptr number;
624: l_end_ptr number;
625: begin
626: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.format_selective_where_clause', 5);
627: hr_dm_utility.message('PARA','(p_text - ' || p_text ,10);
628:
629: l_in_str := p_text;
630: l_end_ptr := instr(l_in_str,':') - 1;
623: l_start_ptr number;
624: l_end_ptr number;
625: begin
626: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.format_selective_where_clause', 5);
627: hr_dm_utility.message('PARA','(p_text - ' || p_text ,10);
628:
629: l_in_str := p_text;
630: l_end_ptr := instr(l_in_str,':') - 1;
631:
653: -- the l_where_clause string contains ',' as the first character, hence
654: -- the need of using substr function.
655:
656: p_text := '(' || ' ' || substr(l_out_str,2) || ')';
657: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.format_selective_where_clause',
658: 25);
659: hr_dm_utility.message('PARA','(p_text - ' || p_text,30);
660: exception
661: when others then
655:
656: p_text := '(' || ' ' || substr(l_out_str,2) || ')';
657: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.format_selective_where_clause',
658: 25);
659: hr_dm_utility.message('PARA','(p_text - ' || p_text,30);
660: exception
661: when others then
662: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.format_selective_where_clause',
663: '(none)','R');
658: 25);
659: hr_dm_utility.message('PARA','(p_text - ' || p_text,30);
660: exception
661: when others then
662: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.format_selective_where_clause',
663: '(none)','R');
664: raise;
665: end format_selective_where_clause;
666: -- ----------------------- get_gen_cursor_where_clause -------------------------
715: If cursor is for calculate range then the where clause will not contain
716: the first line of the where clause mentioned above.
717: */
718:
719: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.get_gen_cursor_where_clause ', 5);
720: hr_dm_utility.message('PARA','(p_cursor_type - ' || p_cursor_type ||
721: ')', 10);
722:
723: -- check if the where clause is defined for this table i.e
716: the first line of the where clause mentioned above.
717: */
718:
719: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.get_gen_cursor_where_clause ', 5);
720: hr_dm_utility.message('PARA','(p_cursor_type - ' || p_cursor_type ||
721: ')', 10);
722:
723: -- check if the where clause is defined for this table i.e
724: -- derive_sql_download_full field is not null.If yes then
872: end if;
873:
874:
875:
876: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.get_gen_cursor_where_clause',
877: 25);
878: hr_dm_utility.message('PARA','(p_where_clause - ' || p_where_clause || ')' ,30);
879: exception
880: when others then
874:
875:
876: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.get_gen_cursor_where_clause',
877: 25);
878: hr_dm_utility.message('PARA','(p_where_clause - ' || p_where_clause || ')' ,30);
879: exception
880: when others then
881: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.get_gen_cursor_where_clause',
882: '(none)','R');
877: 25);
878: hr_dm_utility.message('PARA','(p_where_clause - ' || p_where_clause || ')' ,30);
879: exception
880: when others then
881: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.get_gen_cursor_where_clause',
882: '(none)','R');
883: raise;
884: end get_gen_cursor_where_clause;
885:
931: 'last_update_date >= nvl(p_last_migration_date,' ||
932: indent(50) || p_table_info.alias || '.last_update_date)';
933: end if;
934:
935: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.get_dt_subqry_where_clause',
936: 25);
937: hr_dm_utility.message('PARA','(p_where_clause - ' || p_where_clause
938: || ')' ,30);
939: exception
933: end if;
934:
935: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.get_dt_subqry_where_clause',
936: 25);
937: hr_dm_utility.message('PARA','(p_where_clause - ' || p_where_clause
938: || ')' ,30);
939: exception
940: when others then
941: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.get_dt_subqry_where_clause',
937: hr_dm_utility.message('PARA','(p_where_clause - ' || p_where_clause
938: || ')' ,30);
939: exception
940: when others then
941: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.get_dt_subqry_where_clause',
942: '(none)','R');
943: raise;
944: end get_dt_subqry_where_clause;
945:
996: If cursor is for calculate range then the where clause will not contain
997: the first line of the where clause mentioned above.
998: */
999:
1000: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.get_dt_cursor_where_clause ', 5);
1001: hr_dm_utility.message('PARA','(p_cursor_type - ' || p_cursor_type ||
1002: ')', 10);
1003:
1004: -- check if the where clause is defined for this table i.e
997: the first line of the where clause mentioned above.
998: */
999:
1000: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.get_dt_cursor_where_clause ', 5);
1001: hr_dm_utility.message('PARA','(p_cursor_type - ' || p_cursor_type ||
1002: ')', 10);
1003:
1004: -- check if the where clause is defined for this table i.e
1005: -- derive_sql_download_full field is not null.If yes then
1106: end if;
1107:
1108:
1109: end if;
1110: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.get_dt_cursor_where_clause',
1111: 25);
1112: hr_dm_utility.message('PARA','(p_where_clause - ' || p_where_clause ||
1113: ')' ,30);
1114: exception
1108:
1109: end if;
1110: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.get_dt_cursor_where_clause',
1111: 25);
1112: hr_dm_utility.message('PARA','(p_where_clause - ' || p_where_clause ||
1113: ')' ,30);
1114: exception
1115: when others then
1116: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.get_dt_cursor_where_clause',
1112: hr_dm_utility.message('PARA','(p_where_clause - ' || p_where_clause ||
1113: ')' ,30);
1114: exception
1115: when others then
1116: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.get_dt_cursor_where_clause',
1117: '(none)','R');
1118: raise;
1119: end get_dt_cursor_where_clause;
1120:
1154: l_prefix_col varchar2(30);
1155:
1156: begin
1157:
1158: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.prepare_download_cursor ', 5);
1159: hr_dm_utility.message('PARA','(p_cursor_type - ' || p_cursor_type ||
1160: ')(p_hier_column - ' || p_hier_column, 10);
1161: if p_hier_column = 'Y' then
1162: l_columns_tbl := g_hier_columns_tbl;
1155:
1156: begin
1157:
1158: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.prepare_download_cursor ', 5);
1159: hr_dm_utility.message('PARA','(p_cursor_type - ' || p_cursor_type ||
1160: ')(p_hier_column - ' || p_hier_column, 10);
1161: if p_hier_column = 'Y' then
1162: l_columns_tbl := g_hier_columns_tbl;
1163: else
1218: -- If the 'distinct' clause causes the performance problem then
1219: -- remove it and seed the derive_sql columns in HR_DM_TABLES for
1220: -- these tables.
1221:
1222: hr_dm_utility.message('INFO','Distinct form for ' || p_table_info.table_name, 25);
1223: hr_dm_utility.message('INFO','p_table_info.use_distinct - ' || p_table_info.use_distinct, 25);
1224: hr_dm_utility.message('INFO','p_table_info.use_distinct_download - ' || p_table_info.use_distinct_download, 25);
1225:
1226: if (p_table_info.use_distinct = 'Y')
1219: -- remove it and seed the derive_sql columns in HR_DM_TABLES for
1220: -- these tables.
1221:
1222: hr_dm_utility.message('INFO','Distinct form for ' || p_table_info.table_name, 25);
1223: hr_dm_utility.message('INFO','p_table_info.use_distinct - ' || p_table_info.use_distinct, 25);
1224: hr_dm_utility.message('INFO','p_table_info.use_distinct_download - ' || p_table_info.use_distinct_download, 25);
1225:
1226: if (p_table_info.use_distinct = 'Y')
1227: or
1220: -- these tables.
1221:
1222: hr_dm_utility.message('INFO','Distinct form for ' || p_table_info.table_name, 25);
1223: hr_dm_utility.message('INFO','p_table_info.use_distinct - ' || p_table_info.use_distinct, 25);
1224: hr_dm_utility.message('INFO','p_table_info.use_distinct_download - ' || p_table_info.use_distinct_download, 25);
1225:
1226: if (p_table_info.use_distinct = 'Y')
1227: or
1228: (p_table_info.use_distinct_download = 'Y') then
1226: if (p_table_info.use_distinct = 'Y')
1227: or
1228: (p_table_info.use_distinct_download = 'Y') then
1229: l_cursor_select_cols := ' select distinct ' || l_cursor_select_cols;
1230: hr_dm_utility.message('INFO','Using distinct form', 25);
1231: else
1232: l_cursor_select_cols := ' select ' || l_cursor_select_cols;
1233: hr_dm_utility.message('INFO','Not using distinct form', 25);
1234: end if;
1229: l_cursor_select_cols := ' select distinct ' || l_cursor_select_cols;
1230: hr_dm_utility.message('INFO','Using distinct form', 25);
1231: else
1232: l_cursor_select_cols := ' select ' || l_cursor_select_cols;
1233: hr_dm_utility.message('INFO','Not using distinct form', 25);
1234: end if;
1235:
1236:
1237: if p_cursor_type = 'FULL_BG_MIGRATION' then
1262: l_cursor_select_cols || indent ||
1263: l_cursor_select_from || indent ||
1264: l_cursor_select_where || indent;
1265:
1266: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.prepare_download_cursor',
1267: 25);
1268: hr_dm_utility.message('PARA','(p_cursor - ' || p_cursor || ')' ,30);
1269: exception
1270: when others then
1264: l_cursor_select_where || indent;
1265:
1266: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.prepare_download_cursor',
1267: 25);
1268: hr_dm_utility.message('PARA','(p_cursor - ' || p_cursor || ')' ,30);
1269: exception
1270: when others then
1271: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.prepare_download_cursor',
1272: '(none)','R');
1267: 25);
1268: hr_dm_utility.message('PARA','(p_cursor - ' || p_cursor || ')' ,30);
1269: exception
1270: when others then
1271: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.prepare_download_cursor',
1272: '(none)','R');
1273: raise;
1274: end prepare_download_cursor;
1275: -- ----------------------- prepare_dt_private_procedures ------------------
1288: l_full_mig_csr_name varchar2(30) := 'csr_full_mig_' || p_table_info.alias;
1289: l_adt_mig_csr_name varchar2(30) := 'csr_adt_mig_' || p_table_info.alias;
1290:
1291: begin
1292: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.prepare_dt_private_procedures ', 5);
1293:
1294: l_indent := 2;
1295:
1296: -- write the code for privte procedure open_cursor.
1363: l_proc_body := l_proc_body|| indent(2) || 'end private_fetch_cursor;';
1364:
1365: p_body := l_proc_body;
1366:
1367: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.prepare_dt_private_procedures',
1368: 25);
1369: exception
1370: when others then
1371: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.prepare_dt_private_procedures',
1367: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.prepare_dt_private_procedures',
1368: 25);
1369: exception
1370: when others then
1371: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.prepare_dt_private_procedures',
1372: '(none)','R');
1373: raise;
1374: end prepare_dt_private_procedures;
1375: -- ----------------------- nullify_hierarchical_cols -----------------------
1388: l_list_index number;
1389: l_count number;
1390:
1391: begin
1392: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.nullify_hierarchical_cols ', 5);
1393:
1394: -- initialise the variables
1395: l_list_index := g_hier_columns_tbl.first;
1396: l_count := 1;
1414:
1415: l_list_index := g_hier_columns_tbl.next(l_list_index);
1416: l_count := l_count + 1;
1417: end loop;
1418: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.nullify_hierarchical_cols',
1419: 25);
1420: exception
1421: when others then
1422: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.nullify_hierarchical_cols',
1418: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.nullify_hierarchical_cols',
1419: 25);
1420: exception
1421: when others then
1422: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.nullify_hierarchical_cols',
1423: '(none)','R');
1424: raise;
1425: end nullify_hierarchical_cols;
1426:
1447:
1448: l_indent number;
1449: l_proc_body_tbl t_varchar2_32k_tbl;
1450: begin
1451: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.generate_get_link_value ', 5);
1452: -- input parameters for the procedure
1453:
1454: l_interface := indent || '(p_link_value out nocopy number)' || indent;
1455:
1496: 'begin' || indent || l_proc_body;
1497:
1498: -- add the body of this procedure to the package.
1499: add_to_package_body( l_proc_body_tbl );
1500: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.generate_get_link_value',
1501: 25);
1502:
1503: exception
1504: when others then
1501: 25);
1502:
1503: exception
1504: when others then
1505: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.generate_get_link_value',
1506: '(none)','R');
1507: raise;
1508: end generate_get_link_value;
1509:
1570: l_proc_body_tbl t_varchar2_32k_tbl;
1571: l_code varchar2(4);
1572: l_table varchar2(30);
1573: begin
1574: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.generate_developer_key_func ', 5);
1575: hr_dm_utility.message('PARA','(p_fk_to_aol_columns_tbl - table of varchar2)', 10);
1576:
1577: -- generate the function to get the developer key for each of the column
1578: -- which has a foreign key on AOL table.
1571: l_code varchar2(4);
1572: l_table varchar2(30);
1573: begin
1574: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.generate_developer_key_func ', 5);
1575: hr_dm_utility.message('PARA','(p_fk_to_aol_columns_tbl - table of varchar2)', 10);
1576:
1577: -- generate the function to get the developer key for each of the column
1578: -- which has a foreign key on AOL table.
1579:
1670: l_cursor_name || ' into ' || rpad('p_'|| l_parent_column_name ,30) || ';' ||
1671: indent(l_indent) ||
1672: 'if ' || l_cursor_name || '%notfound then' || indent(l_indent + 2) ||
1673: 'close ' || l_cursor_name || ';' || indent(l_indent + 2) ||
1674: 'hr_dm_utility.message(''FAIL'',''When obtaining the developer key'' || ' ||
1675: indent(l_indent + 2) ||
1676: ' '' from table / view ' || l_parent_table_name || ' no data was found.'' ||' ||
1677: indent(l_indent + 2) ||
1678: ' '' check for a null or orphaned data.'',10);' ||
1725: || '.' || l_parent_column_name ||'%type;';
1726: end if;
1727: l_index := p_fk_to_aol_columns_tbl.next(l_index);
1728: end loop;
1729: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.generate_developer_key_func',
1730: 25);
1731: exception
1732: when others then
1733: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.generate_developer_key_func',
1729: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.generate_developer_key_func',
1730: 25);
1731: exception
1732: when others then
1733: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.generate_developer_key_func',
1734: '(none)','R');
1735: raise;
1736: end generate_developer_key_func;
1737:
1786: and h.table_id = t.table_id
1787: and t.table_name = upper(p_table_info.table_name);
1788:
1789: begin
1790: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.generate_download', 5);
1791: hr_dm_utility.message('PARA','( p_hier_column - ' || p_hier_column ||
1792: ')', 10);
1793: if p_hier_column = 'Y' then
1794: l_columns_tbl := g_hier_columns_tbl;
1787: and t.table_name = upper(p_table_info.table_name);
1788:
1789: begin
1790: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.generate_download', 5);
1791: hr_dm_utility.message('PARA','( p_hier_column - ' || p_hier_column ||
1792: ')', 10);
1793: if p_hier_column = 'Y' then
1794: l_columns_tbl := g_hier_columns_tbl;
1795: l_func_name := 'download_hierarchy';
2103:
2104: -- add the body of this procedure to the package.
2105: add_to_package_body( l_proc_body_tbl );
2106:
2107: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.generate_download',
2108: 25);
2109: exception
2110: when others then
2111: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.generate_download',
2107: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.generate_download',
2108: 25);
2109: exception
2110: when others then
2111: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.generate_download',
2112: '(none)','R');
2113: raise;
2114: end generate_download;
2115: -- ----------------------- prepare_calc_range_cursor ---------------------
2129: l_cursor_select_where varchar2(32767);
2130:
2131: begin
2132:
2133: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.prepare_calc_range_cursor'
2134: , 5);
2135:
2136: -- comments about the cursor
2137: l_cursor_comment := format_comment('cursor to get the minimum and maximum' ||
2160: l_cursor_defination || indent ||
2161: l_cursor_select_cols || indent ||
2162: l_cursor_select_from || indent ||
2163: l_cursor_select_where || indent;
2164: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.prepare_calc_range_cursor',
2165: 25);
2166: exception
2167: when others then
2168: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.prepare_calc_range_cursor',
2164: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.prepare_calc_range_cursor',
2165: 25);
2166: exception
2167: when others then
2168: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.prepare_calc_range_cursor',
2169: '(none)','R');
2170: raise;
2171: end prepare_calc_range_cursor;
2172:
2199:
2200: l_indent number;
2201: l_proc_body_tbl t_varchar2_32k_tbl;
2202: begin
2203: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.generate_calculate_ranges', 5);
2204:
2205: -- input parameters for the procedure
2206:
2207: l_interface := indent ||
2253: indent(l_indent) || 'if l_min_key_value is null or l_max_key_value is null '
2254: || 'then' ||
2255: format_comment('No rows selected in this table for download.',l_indent + 2) ||
2256: indent(l_indent + 2) || 'close csr_get_pk_min_max_val;' ||
2257: indent(l_indent + 2) || 'hr_dm_utility.message(''ROUT'',''exit:' ||
2258: l_package_name || '.calculate_ranges (no rows found). '', 25);' ||
2259: indent(l_indent + 2) ||'return;' || indent(l_indent ) ||
2260: 'end if;' || indent(l_indent) || 'close csr_get_pk_min_max_val;' || indent;
2261:
2370:
2371: -- add the body of this procedure to the package.
2372: add_to_package_body( l_proc_body_tbl );
2373:
2374: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.generate_calculate_ranges',
2375: 25);
2376: exception
2377: when others then
2378: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.generate_calculate_ranges',
2374: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.generate_calculate_ranges',
2375: 25);
2376: exception
2377: when others then
2378: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.generate_calculate_ranges',
2379: '(none)','R');
2380: raise;
2381: end generate_calculate_ranges;
2382:
2396: l_cursor_select_from varchar2(32767);
2397: l_cursor_select_where varchar2(32767);
2398:
2399: begin
2400: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.prepare_del_datapump_cursor', 5);
2401:
2402: -- comments about the cursor
2403: l_cursor_comment := format_comment('cursor to get the '||
2404: p_table_info.table_name
2436: l_cursor_select_cols || indent ||
2437: l_cursor_select_from || indent ||
2438: l_cursor_select_where || indent;
2439:
2440: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.prepare_del_datapump_cursor',
2441: 25);
2442: exception
2443: when others then
2444: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.prepare_del_datapump_cursor',
2440: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.prepare_del_datapump_cursor',
2441: 25);
2442: exception
2443: when others then
2444: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.prepare_del_datapump_cursor',
2445: '(none)','R');
2446: raise;
2447: end prepare_del_datapump_cursor;
2448:
2476: l_indent number;
2477: l_proc_body_tbl t_varchar2_32k_tbl;
2478: begin
2479:
2480: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.generate_delete_datapump', 5);
2481:
2482: -- input parameters for the procedure
2483:
2484: l_interface := indent ||
2566:
2567: -- add the body of this procedure to the package.
2568: add_to_package_body( l_proc_body_tbl );
2569:
2570: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.generate_delete_datapump',
2571: 25);
2572: exception
2573: when others then
2574: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.generate_delete_datapump',
2570: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.generate_delete_datapump',
2571: 25);
2572: exception
2573: when others then
2574: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.generate_delete_datapump',
2575: '(none)','R');
2576: raise;
2577: end generate_delete_datapump;
2578:
2593: l_cursor_select_from varchar2(32767);
2594: l_cursor_select_where varchar2(32767);
2595:
2596: begin
2597: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.prepare_delete_source_cursor', 5);
2598:
2599: -- comments about the cursor
2600: l_cursor_comment := format_comment('cursor to get the '||
2601: p_table_info.table_name
2640: l_cursor_select_cols || indent ||
2641: l_cursor_select_from || indent ||
2642: l_cursor_select_where || indent;
2643:
2644: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.prepare_delete_source_cursor',
2645: 25);
2646: exception
2647: when others then
2648: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.prepare_delete_source_cursor',
2644: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.prepare_delete_source_cursor',
2645: 25);
2646: exception
2647: when others then
2648: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.prepare_delete_source_cursor',
2649: '(none)','R');
2650: raise;
2651: end prepare_delete_source_cursor;
2652:
2679: l_indent number;
2680: l_proc_body_tbl t_varchar2_32k_tbl;
2681: begin
2682:
2683: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.generate_delete_source', 5);
2684:
2685: -- input parameters for the procedure
2686:
2687: l_interface := indent ||
2767: -- add the body of this procedure to the package.
2768: add_to_package_body( l_proc_body_tbl );
2769:
2770:
2771: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.generate_delete_source',
2772: 25);
2773:
2774: exception
2775: when others then
2772: 25);
2773:
2774: exception
2775: when others then
2776: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.generate_delete_source',
2777: '(none)','R');
2778: raise;
2779: end generate_delete_source;
2780:
2822: l_dev_key_local_var varchar2(32767);
2823: l_proc_body_tbl t_varchar2_32k_tbl;
2824: l_proc_index number := 1;
2825: begin
2826: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.create_tds_pacakge', 5);
2827: hr_dm_utility.message('PARA','(Table Name - ' || p_table_info.table_name ||
2828: ')', 10);
2829:
2830: g_table_info := p_table_info;
2823: l_proc_body_tbl t_varchar2_32k_tbl;
2824: l_proc_index number := 1;
2825: begin
2826: hr_dm_utility.message('ROUT','entry:hr_dm_gen_tds.create_tds_pacakge', 5);
2827: hr_dm_utility.message('PARA','(Table Name - ' || p_table_info.table_name ||
2828: ')', 10);
2829:
2830: g_table_info := p_table_info;
2831: g_columns_tbl := p_columns_tbl;
2981: l_header := l_header || 'end ' || l_package_name || ';';
2982: l_body := l_body || 'end ' || l_package_name || ';';
2983: exception
2984: when plsql_value_error then
2985: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.create_tds_pacakge',
2986: 'Either TDS package code size is too big or ' ||
2987: ' a value error)',
2988: 'R');
2989: raise;
3010: hr_dm_library.check_compile (p_object_name => l_package_name,
3011: p_object_type => 'PACKAGE BODY' );
3012: exception
3013: when others then
3014: hr_dm_utility.error(SQLCODE,'Error in compiling TDS for ' ||
3015: p_table_info.table_name ,'(none)','R');
3016: raise;
3017: end;
3018:
3015: p_table_info.table_name ,'(none)','R');
3016: raise;
3017: end;
3018:
3019: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.create_tds_pacakge',
3020: 25);
3021: exception
3022: when others then
3023: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.create_tds_pacakge ',
3019: hr_dm_utility.message('ROUT','exit:hr_dm_gen_tds.create_tds_pacakge',
3020: 25);
3021: exception
3022: when others then
3023: hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.create_tds_pacakge ',
3024: '(none)','R');
3025: raise;
3026: end create_tds_pacakge ;
3027: