1 package body hr_dm_gen_tds as
2 /* $Header: perdmgnd.pkb 120.0 2005/05/31 17:08:28 appldev noship $ */
3
4
5 --type t_varchar2_tbl is table of varchar2(32767) index by binary_integer;
6 type t_varchar2_32k_tbl is table of varchar2(32767) index by binary_integer;
7
8
9 g_table_info hr_dm_gen_main.t_table_info;
10 g_columns_tbl hr_dm_library.t_varchar2_tbl;
11 g_parameters_tbl hr_dm_library.t_varchar2_tbl;
12 g_hier_columns_tbl hr_dm_library.t_varchar2_tbl;
13 g_hier_parameters_tbl hr_dm_library.t_varchar2_tbl;
14 g_resolve_pk_columns_tbl hr_dm_gen_main.t_fk_to_aol_columns_tbl;
15 g_surrogate_pk_col_param varchar2(30);
16
17 -- to store the package body in to array so as to overcome the limit of 32767
18 -- character the global variable is defined.
19 g_package_body dbms_sql.varchar2s;
20 g_package_index number := 0;
21
22 --c_newline constant varchar(1) default '
23 --';
24
25 --
26 -- Exception for generated text exceeding the maximum allowable buffer size.
27 --
28 plsql_value_error exception;
29 pragma exception_init(plsql_value_error, -6502);
30
31 -- ----------------------- indent -----------------------------------------
32 -- Description:
33 -- returns the 'n' blank spaces on a newline.used to indent the procedure
34 -- statements.
35 -- if newline parameter is 'Y' then start the indentation from new line.
36 -- ------------------------------------------------------------------------
37
38 function indent
39 (
40 p_indent_spaces in number default 0,
41 p_newline in varchar2 default 'Y'
42 ) return varchar2 is
43 l_spaces varchar2(100);
44 begin
45
46 l_spaces := hr_dm_library.indent(p_indent_spaces => p_indent_spaces,
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;
56
57 -- ----------------------- format_comment ---------------------------------
58 -- Description:
59 -- formats the comments to be written into the procedure body
60 -- e.g comment string ' This is a example comment text' will be converted t
61 -- --
62 -- -- This is a example comment text.
63 -- --
64 -- ------------------------------------------------------------------------
65
66 function format_comment
67 (
68 p_comment_text in varchar2,
69 p_indent_spaces in number default 0,
70 p_ins_blank_lines in varchar2 default 'Y'
71 ) return varchar2 is
72
73 l_comment_text varchar2(20000);
74 l_comment_length number := length(p_comment_text);
75
76 --
77 -- maximum chracters for single comment text line ensuring the single
78 -- comment line cannot be more than 77 characters long excluding 3
79 -- characters ('-- ') at the begning of comment.
80 --
81
82 l_max_comment_line_len number := 77 - p_indent_spaces;
83 l_comment_line_len number;
84 l_comment_line_txt varchar2(80);
85
86 -- start and end pointer of comment line to be copied from comment text.
87 l_start_ptr number := 1;
88 l_end_ptr number;
89
90 -- used for wrapping
91 l_last_space_ptr number;
92 begin
93
94 if p_ins_blank_lines = 'Y' then
95 l_comment_text := indent(p_indent_spaces) || '--';
96 end if;
97
98 loop
99 l_end_ptr := l_start_ptr + l_max_comment_line_len - 1;
100
101 l_comment_line_txt := substr(p_comment_text,l_start_ptr,(l_end_ptr - l_start_ptr + 1));
102
103 l_comment_line_len := length(l_comment_line_txt);
104
105 -- comment line is less than the maximum text that come then it is ok,
106 -- otherwise do word wrapping.If the next character is a space there is
107 -- no need for wrapping
108
109 if l_comment_line_len >= l_max_comment_line_len and
110 substr(p_comment_text,l_end_ptr + 1,1) <> ' '
111 then
112
113 -- this function ensures the wrapping of the word. last word will come
114 -- either full or move to the next line.This gives the position of the
115 -- last space in the comment line text.
116
117 l_last_space_ptr := instr(l_comment_line_txt,' ',-1);
118
119 -- adjust the end pointer as we want to copy the string upto the last
120 -- space only, the remaining word should go into next line.
121
122 l_end_ptr := l_end_ptr - (length(l_comment_line_txt) - l_last_space_ptr);
123
124 end if;
125
126 -- now the end_ptr gives the length of the comment line that can be copied
127 -- with a space in the end.
128
129 l_comment_text := l_comment_text || indent(p_indent_spaces) || '-- ';
130
131
132 l_comment_text := l_comment_text || substr(p_comment_text,l_start_ptr,
133 (l_end_ptr - l_start_ptr + 1));
134
135 l_start_ptr := l_end_ptr +1;
136
137 if l_start_ptr > l_comment_length then
138 exit;
139 end if;
140 end loop;
141
142
143 if p_ins_blank_lines = 'Y' then
144 l_comment_text := l_comment_text || indent(p_indent_spaces) || '--';
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');
154 end format_comment;
155
156 --------------------- init_package_body----------------------------------------
157 -- This package will delete all the elements from the package body pl/sql table.
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',
176 '(none)','R');
177 raise;
178 end init_package_body;
179 -- -----------------------add_debug_messages ---------------------------------
180 -- Description:
181 -- This procedure will add the debug messages to the generated procedures.
182 -- Debug messages are added depending upon the procedure and location.
183 -- Debug messages are added at the start of the procedure body or at the end.
184 -- Input Parameter :
185 -- p_procedure_name : procedure name of the TDS package. e.g 'DOWNLOAD',
186 -- 'CALCULATE_RANGES', 'DELETE_SOURCE', e.t.c.
187 -- p_message_location : it can have following two values
188 -- 'START' - to put the debug message at start
189 -- 'END' - to put the debug message at end.
190 -- ------------------------------------------------------------------------
191 procedure add_debug_messages
192 (
193 p_table_info in hr_dm_gen_main.t_table_info,
194 p_procedure_name in varchar2,
195 p_message_location in varchar2,
196 p_proc_body in out nocopy varchar2
197 ) is
198 l_package_name varchar2(30) := 'hrdmd_' || p_table_info.short_name;
199 l_parameter_string varchar2(1000);
200 l_local_variable_string varchar2(1000);
201 l_indent number;
202 begin
203
204
205 if upper(p_procedure_name) in ('DOWNLOAD', 'DOWNLOAD_HIERARCHY') then
206 l_indent := 4;
207 l_parameter_string := '''(p_migration_type - '' || p_migration_type || ' ||
208 indent(l_indent + 29) || ''')(p_business_group_id - '' || p_business_group_id ||' ||
209 indent(l_indent + 29) || ''')(p_last_migration_date - '' || p_last_migration_date ||' ||
210 indent(l_indent + 29) || ''')(p_start_id - '' || p_start_id ||' ||
211 indent(l_indent + 29) || ''')(p_end_id - '' || p_end_id ||' ||
212 indent(l_indent + 29) || ''')(p_batch_id - '' || p_batch_id || ' ||
213 indent(l_indent + 29) || ''')(p_chunk_size - '' || p_chunk_size ||' ||
214 indent(l_indent + 29) || ''')''';
215
216 l_local_variable_string :=
217 ' || ' || '''(l_rec_inserted_cnt - '' || l_rec_inserted_cnt ||' ||
218 indent(l_indent + 29) || ''')''';
219
220 elsif upper(p_procedure_name) = 'CALCULATE_RANGES' then
221 l_indent := 2;
222 l_parameter_string := '''(p_business_group_id - '' || p_business_group_id || ' ||
223 indent(l_indent + 29) || ''')(p_last_migration_date - '' || p_last_migration_date ||' ||
224 indent(l_indent + 29) || ''')(p_phase_item_id - '' || p_phase_item_id ||' ||
225 indent(l_indent + 29) || ''')(p_no_of_threads - '' || p_no_of_threads ||' ||
226 indent(l_indent + 29) || ''')''';
227
228 if p_table_info.surrogate_primary_key = 'Y' then
229 l_local_variable_string :=
230 ' || ' || '''(l_max_key_value - '' || l_max_key_value ||' ||
231 indent(l_indent + 29) || ''')(l_min_key_value - '' || l_min_key_value ||' ||
232 indent(l_indent + 29) || ''')(l_starting_process_sequence - '' || l_starting_process_sequence ||' ||
233 indent(l_indent + 29) || ''')(l_ending_process_sequence - '' || l_ending_process_sequence ||' ||
234 indent(l_indent + 29) || ''')l_range_value - '' || l_range_value ||' ||
235 indent(l_indent + 29) || ''')''';
236 end if;
237
238 elsif upper(p_procedure_name) = 'DELETE_DATAPUMP' then
239 l_indent := 2;
240 l_parameter_string := '''(p_start_id - '' || p_start_id || ' ||
241 indent(l_indent + 29) || ''')(p_end_id - '' || p_end_id ||' ||
242 indent(l_indent + 29) || ''')(p_batch_id - '' || p_batch_id ||' ||
243 indent(l_indent + 29) || ''')(p_chunk_size - '' || p_chunk_size ||' ||
244 indent(l_indent + 29) || ''')''';
245
246 l_local_variable_string :=
247 ' || ' || '''(l_rec_deleted_cnt - '' || l_rec_deleted_cnt ||' ||
248 indent(l_indent + 29) || ''')''';
249
250 elsif upper(p_procedure_name) = 'DELETE_SOURCE' then
251 l_indent := 2;
252 l_parameter_string := '''(p_business_group_id - '' || p_business_group_id || ' ||
253 indent(l_indent + 29) || '''(p_start_id - '' || p_start_id || ' ||
254 indent(l_indent + 29) || ''')(p_end_id - '' || p_end_id ||' ||
255 indent(l_indent + 29) || ''')(p_chunk_size - '' || p_chunk_size ||' ||
256 indent(l_indent + 29) || ''')''';
257
258 l_local_variable_string :=
259 ' || ' || '''(l_rec_deleted_cnt - '' || l_rec_deleted_cnt ||' ||
260 indent(l_indent + 29) || ''')''';
261
262 end if;
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);';
272
273 end if;
274
275
276 if p_message_location = 'END' then
277 p_proc_body := indent(2) || '-- debug messages ';
278
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
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
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
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) ||
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;
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',
319 '(none)','R');
320 raise;
321 end add_debug_messages;
322 -- -----------------------add_to_package_body; ---------------------------------
323 -- Description:
324 -- This procedure will be called by each procedure to be created by TUPS.
325 -- Each procedure will be stored in the array of varchar2(32767).
326 -- The input to this procedure is pl/sql table i.e array of string.
327 -- Now the task of this procedure is to split the above array elements into
328 -- array elements of size 256. This is required so as to the package body
329 -- of more than 32 K size can be parsed using dbms_sql procedure.
330 --
331 -- ------------------------------------------------------------------------
332
333 procedure add_to_package_body
334 (
335 p_proc_body_tbl t_varchar2_32k_tbl
336 ) is
337
338 l_proc_index number := p_proc_body_tbl.first;
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
348 l_string_index := 1;
349 l_loop_cnt := 1;
350 -- read the string of the procedure body and chop it into the array element
351 -- size of 256 and store it into the global package body. Each looping will
352 -- will read the 256 characters from the procedure body and it will go on
353 -- until no more characters to read.
354 loop
355 if substr(p_proc_body_tbl(l_proc_index),l_string_index,256) is null
356 then
357 exit;
358 end if;
359 g_package_index := g_package_index + 1;
360
361 -- add the procedure body to
362 g_package_body (g_package_index) :=
363 substr(p_proc_body_tbl(l_proc_index),
364 l_string_index ,256);
365 l_string_index := 256*l_loop_cnt + 1;
366 l_loop_cnt := l_loop_cnt + 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);
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');
385 raise;
386 end add_to_package_body;
387 -- ----------------------- get_derive_from_clause -------------------------
388 -- Description:
389 -- Uses the derive_sql_source_tables info stored in HR_DM_TABLES to form the
390 -- 'from clause'.
391 -- The from clause stored in each derive field will be in the following format :
392 -- table1 tbl,:table2 tbl2, :table3 tbl3
393 -- where ':' is the next line indicator i.e : will be replaced with new line.
394 -- o If 'from' string is not there it puts the from string.
395 -- ------------------------------------------------------------------------------
396 procedure get_derive_from_clause
397 (
398 p_table_info in hr_dm_gen_main.t_table_info,
399 p_from_clause in out nocopy varchar2,
400 p_lpad_spaces in number default 2
401 ) is
402 l_derive_sql hr_dm_tables.derive_sql_download_full%type;
403 l_start_ptr number;
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;
413
414
415 -- if 'where' string is not there then add the where string.
416 if instr(lower(l_derive_sql),'from') <= 0 then
417 p_from_clause := ' from ';
418 end if;
419
420 l_end_ptr := instr(l_derive_sql,':') - 1;
421 -- read the where clause string until first ':' . add the new line and chop
422 -- the where clause string upto ':' character. Continue this process until
423 -- full where clause is formatted.
424 loop
425
426 p_from_clause := p_from_clause || substr(l_derive_sql,1,
427 l_end_ptr) || indent(p_lpad_spaces + 5);
428 -- remove the characters from where clause which have been appended in
429 -- the where clause.
430 l_derive_sql := substr(l_derive_sql,l_end_ptr + 2);
431 --
432 l_end_ptr := instr(l_derive_sql,':') - 1;
433
434 if l_end_ptr <= 0 or l_end_ptr is null then
435 p_from_clause := p_from_clause || l_derive_sql;
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
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');
450 raise;
451 end get_derive_from_clause;
452 ---
453 -- ----------------------- get_cursor_from_clause -------------------------
454 -- Description:
455 -- Get the list of all the tables required to get the download from clause.
456 -- if the business group_id field does not exist in the table to be downloaded
457 -- then it is derived from the table hierarchy table.
458 -- ------------------------------------------------------------------------
459 procedure get_cursor_from_clause
460 (
461 p_table_info in hr_dm_gen_main.t_table_info,
462 p_from_clause out nocopy varchar2 ,
463 p_lpad_spaces in number default 2
464 ) is
465
466 l_parent_table_info hr_dm_gen_main.t_table_info;
467
468 -- get all parent tables required to get the business group id, if business
469 -- group id is not there in the table to be downloaded.
470
471 cursor csr_get_table is
472
473 select distinct parent_table_id
474 from (select table_id,parent_table_id
475 from hr_dm_hierarchies
476 where hierarchy_type = 'PC')
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
486 -- clause defined in the table.
487
488 if p_table_info.derive_sql_source_tables is not null then
489 get_derive_from_clause ( p_table_info => p_table_info,
490 p_from_clause => p_from_clause,
491 p_lpad_spaces => p_lpad_spaces);
492 return;
493 end if;
494
495 p_from_clause := lpad(' ',p_lpad_spaces) ||'from ' ||
496 p_table_info.table_name || ' ' || p_table_info.alias;
497
498 -- if the table to be downloaded has table hierarchy i.e business group id
499 -- has to be derived from table hierarchy i.e parent tables.
500 if p_table_info.table_hierarchy = 'Y' and p_table_info.global_data = 'N' then
501 for cst_get_table_rec in csr_get_table loop
502 -- get the parent table name
503 hr_dm_library.get_table_info (cst_get_table_rec.parent_table_id,
504 l_parent_table_info);
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
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;
519
520 -- ----------------------- get_derive_where_clause -------------------------
521 -- Description:
522 -- Uses the derive_sql info stored in HR_DM_TABLES for each type of cursors.
523 -- The where clause stored in each derive field will
524 -- example derive where clause :
525 -- where tbl.col1 = tbl2.col1 : and tbl.col2 = tbl1.col2 : and tbl.col3 = tbl3.col3
526 -- Above derive where clause will be converted as
527 -- where tbl.col1 = tbl2.col1
528 -- and tbl.col2 = tbl1.col2
529 -- and tbl.col3 = tbl3.col3
530 -- by this procedure. replace ':' by newline feed.
531 -- o may or may not include the 'where' string. If the 'where' string is not
532 -- there this procedure will put the 'where' string
533 -- o may or may not have the where clause terminator ';'. If it is not there
534 -- it will add it.
535 -- o the next line indicator will be ':' i.e : will be replaced with new line.
536 -- p_cursor_type :- 'DOWNLOAD' - where clause for full download
537 -- 'DOWNLOAD_DT' - where clause for date track table additive
538 -- download
539 -- 'CALCULATE_RANGES' - where clause for calculated ranges
540 -- 'DELETE_SOURCE' - where clause for delete source
541 -- ------------------------------------------------------------------------------
542 procedure get_derive_where_clause
543 (
544 p_table_info in hr_dm_gen_main.t_table_info,
545 p_where_clause in out nocopy varchar2,
546 p_cursor_type in varchar2 default 'DOWNLOAD',
547 p_lpad_spaces in number default 2
548 ) is
549 l_derive_sql hr_dm_tables.derive_sql_download_full%type;
550 l_start_ptr number;
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;
560 elsif p_cursor_type = 'DOWNLOAD_DT' then
561 l_derive_sql := p_table_info.derive_sql_download_add;
562 elsif p_cursor_type = 'CALCULATE_RANGES' then
563 l_derive_sql := p_table_info.derive_sql_calc_ranges;
564 elsif p_cursor_type = 'DELETE_SOURCE' then
565 l_derive_sql := p_table_info.derive_sql_delete_source;
566 end if;
567
568 -- if terminator ';' is there in derive sql then set the terminator to null.
569 if instr(l_derive_sql,';') > 0 then
570 l_terminator := null;
571 end if;
572
573 -- if 'where' string is not there then add the where string.
574 if instr(lower(l_derive_sql),'where') <= 0 then
575 p_where_clause := ' where ';
576 end if;
577
578 l_end_ptr := instr(l_derive_sql,':') - 1;
579 -- read the where clause string until first ':' . add the new line and chop
580 -- the where clause string upto ':' character. Continue this process until
581 -- full where clause is formatted.
582 loop
583
584 p_where_clause := p_where_clause || substr(l_derive_sql,1,
585 l_end_ptr) || indent(p_lpad_spaces);
586 -- remove the characters from where clause which have been appended in
587 -- the where clause.
588 l_derive_sql := substr(l_derive_sql,l_end_ptr + 2);
589 --
590 l_end_ptr := instr(l_derive_sql,':') - 1;
591 if l_end_ptr <= 0 or l_end_ptr is null then
592 p_where_clause := p_where_clause || l_derive_sql;
593 exit;
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
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 -------------------------
608 -- Description:
609 -- Selective criteria is entered by the user and stored in hr_dm_migrations table.
610 -- The selective string is stored in the following_format
611 -- A:B:C:D:E
612 -- The obejective is to to convert the above string as follows
613 -- 'A','B','C','D','E'
614 -- The above string will be used in the where clause to restrict the data.
615 -- ------------------------------------------------------------------------------
616 procedure format_selective_where_clause
617 (
618 p_text in out nocopy varchar2,
619 p_lpad_spaces in number default 25
620 ) is
621 l_in_str varchar2(32767);
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;
631
632 -- read the where clause string until first ':' . add the new line and chop
633 -- the where clause string upto ':' character. Continue this process until
634 -- full where clause is formatted.
635 loop
636
637 if l_end_ptr > 0 then
638 l_out_str := l_out_str ||',''' || substr(l_in_str,1,l_end_ptr) ||
639 '''' || indent(p_lpad_spaces);
640 -- remove the characters from where clause which have been appended in
641 -- the where clause.
642 l_in_str := substr(l_in_str,l_end_ptr + 2);
643 --
644 l_end_ptr := instr(l_in_str,':') - 1;
645 end if;
646
647 if l_end_ptr <= 0 or l_end_ptr is null then
648 l_out_str := l_out_str || ',''' || l_in_str || '''' || indent(p_lpad_spaces);
649 exit;
650 end if;
651 end loop;
652
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
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 -------------------------
667 -- Description:
668 -- prepares the where clause for the following data to be downloaded:
669 -- o non date track table data
670 -- o full migration of date track table data
671 -- if the business group_id field does not exist in the table to be downloaded
672 -- then complex joins are to be made with the parent tables. Information about
673 -- the join is stored in hr_dm_hierarchy table for a given table.
674 -- The where clause consist of three things
675 -- a) range of surrogate id b) last_update_date and c) business_group_id
676 -- Depending upon the cursor type the where clause will be formed with the above
677 -- components.
678 -- p_cursor_type :- 'DOWNLOAD' - where clause for download procedure
679 -- components : (a,b,c)
680 -- 'CALCULATE_RANGES' - where clause for calculated ranges
681 -- components : (b,c)
682 -- 'DELETE_SOURCE' - where clause for delete source
683 -- components : (a,c)
684 -- ------------------------------------------------------------------------------
685 procedure get_gen_cursor_where_clause
686 (
687 p_table_info in hr_dm_gen_main.t_table_info,
688 p_where_clause in out nocopy varchar2,
689 p_cursor_type in varchar2 default 'DOWNLOAD',
690 p_lpad_spaces in number default 2
691 ) is
692
693 l_parent_table_info hr_dm_gen_main.t_table_info;
694 l_selective_criteria varchar2(8000);
695
696 -- get the selective formula criteria
697 cursor get_sel_formula is
698 select selective_migration_criteria
699 from hr_dm_migrations
700 where migration_id = p_table_info.migration_id
701 and migration_type = 'SF'
702 and selective_migration_criteria is not null;
703
704 begin
705
706 /*
707 Following where clause stmt will be created by the assignment below e.g
708 where adr.address_id between p_start_id and p_end_id
709 and adr.last_update_date >= decode(p_migration_type, 'full',
710 adr.last_update_date,
711 p_last_update_date)
712 adr - table alias name, address_id - surrogate primary key.
713 Note: it does not have business group_id.
714
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
724 -- derive_sql_download_full field is not null.If yes then
725 -- call get_derive_where_clause procedure to format the where
726 -- clause defined in the table.
727
728 if p_table_info.derive_sql_download_full is not null then
729 get_derive_where_clause ( p_table_info => p_table_info,
730 p_where_clause => p_where_clause,
731 p_cursor_type => p_cursor_type,
732 p_lpad_spaces => p_lpad_spaces);
733 return;
734 end if;
735
736 --
737 -- if it is a table hierarchy then call the implicit business group where
738 -- clause package to prepare the where clause, otherwise, create the where
739 -- clause.
740 if p_table_info.table_hierarchy = 'Y' and p_table_info.global_data = 'N' then
741 hr_dm_imp_bg_where.main (p_table_info => p_table_info,
742 p_cursor_type => p_cursor_type,
743 p_query_type => 'MAIN_QUERY',
744 p_where_clause => p_where_clause);
745 else
746 -- preparing where clause for explicit business group id.
747
748 p_where_clause := lpad(' ',p_lpad_spaces) || 'where ';
749
750 -- component range_id and last update date of the where clause
751
752 if p_cursor_type = 'DOWNLOAD' THEN
753 --
754 -- put the search condition of id between start and end id if the
755 -- table has a surrogate id.
756 --
757 if p_table_info.surrogate_primary_key = 'Y'
758 then
759 p_where_clause := p_where_clause || p_table_info.alias || '.' ||
760 p_table_info.surrogate_pk_column_name || ' between p_start_id and ' ||
761 'p_end_id';
762
763 -- p_where_clause := p_where_clause || indent(p_lpad_spaces) || 'and ';
764 end if;
765
766 -- Add the last_update_date comparison in the where clause for non date
767 -- track table only.
768 -- if the table has a child table with 'L' type hierarchy then the
769 -- last_update_date clause is ommitted.
770
771 if (p_table_info.missing_who_info = 'N') and
772 (p_table_info.datetrack = 'N') and
773 (hr_dm_gen_main.chk_ins_resolve_pk(p_table_info.table_id) = 'N') then
774
775 -- add 'and' if the surrogate id criteria has been applied.
776
777 if p_table_info.surrogate_primary_key = 'Y' then
778 p_where_clause := p_where_clause || indent(p_lpad_spaces) || 'and ';
779 end if;
780
781 p_where_clause := p_where_clause || p_table_info.alias
782 || '.' || 'last_update_date >= nvl(p_last_migration_date,' ||
783 indent(38) || p_table_info.alias || '.last_update_date)';
784
785 end if;
786 elsif p_cursor_type = 'DELETE_SOURCE' THEN
787 --
788 -- put the search condition of id between start and end id if the
789 -- table has a surrogate id.
790 --
791 if p_table_info.surrogate_primary_key = 'Y'
792 then
793 p_where_clause := p_where_clause || p_table_info.alias || '.' ||
794 p_table_info.surrogate_pk_column_name || ' between p_start_id and ' ||
795 'p_end_id' ;
796
797 end if;
798
799 elsif p_cursor_type = 'CALCULATE_RANGES' THEN
800
801 -- Add the last_update_date comparison in the where clause, if table has
802 -- WHO columns.
803 if p_table_info.missing_who_info = 'N' and
804 (hr_dm_gen_main.chk_ins_resolve_pk(p_table_info.table_id) = 'N') then
805
806 p_where_clause := p_where_clause || p_table_info.alias || '.' ||
807 'last_update_date >= nvl(p_last_migration_date,' ||
808 indent(38) || p_table_info.alias || '.last_update_date)';
809 end if;
810 else
811 null;
812 end if;
813
814 -- component business_group_id of the where clause
815
816 -- put the business group search condition into where clause.
817 -- if business group exists in the table then it is simple and if not
818 -- then it hass to be derived from the parent table hierarchy information
819 -- stored in hierarchy table.
820
821 if p_table_info.table_hierarchy = 'N' then
822
823 -- for explicit business group id, the business group id will not be
824 -- in the sub query of date track cursor.
825
826 if p_table_info.global_data = 'N' then
827 -- check whether there is any selection criteria already added to this
828 -- where clause. If yes then only add the ' and '
829
830 if ltrim(rtrim(p_where_clause)) <> 'where' then
831 p_where_clause := p_where_clause || indent(p_lpad_spaces) || 'and ';
832 end if;
833
834 p_where_clause := p_where_clause || p_table_info.alias || '.' ||
835 'business_group_id = p_business_group_id' ;
836 else
837 -- if no criteria has been added to the where clause then remove the
838 -- where clause.
839 if ltrim(rtrim(p_where_clause)) = 'where' then
840 p_where_clause := null;
841 end if;
842 end if;
843 -- end if; -- not required.
844 end if;
845 end if;
846
847 -- add the selective migration clause for FF_FORMULAS table
848 if lower(p_table_info.table_name) = 'ff_formulas_f' then
849 open get_sel_formula;
850 fetch get_sel_formula into l_selective_criteria;
851 if get_sel_formula%found then
852 format_selective_where_clause (p_text => l_selective_criteria);
853 p_where_clause := p_where_clause || indent || ' and ' || p_table_info.alias
854 || '.formula_name in ' || l_selective_criteria;
855 end if;
856 close get_sel_formula;
857 end if;
858
859 -- add the order by clause for datetrack table
860 if p_table_info.datetrack = 'Y'
861 and p_cursor_type <> 'DELETE_SOURCE' then
862 p_where_clause := p_where_clause || indent(p_lpad_spaces) ||
863 'order by ' || p_table_info.alias || '.' ||
864 p_table_info.surrogate_pk_column_name ;
865 end if;
866
867 p_where_clause := p_where_clause || ';' ;
868
869 -- if the where clause is empty then remove it
870 if p_where_clause =' where ;' then
871 p_where_clause := ' ;';
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
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
886 -- ----------------------- get_dt_subqry_where_clause -------------------------
887 -- Description:
888 -- prepares the where clause for the sub query of the date track table data to
889 -- be downloaded.
890 -- if the business group_id field does not exist in the table to be downloaded
891 -- then complex joins are to be made with the parent tables. Information about
892 -- the join is stored in hr_dm_hierarchy table for a given table.
893 -- The where clause consist of three things
894 -- a) range of surrogate id b) last_update_date and c) business_group_id
895 -- Depending upon the cursor type the where clause will be formed with the above
896 -- components.
897 -- p_cursor_type :- 'DOWNLOAD' - where clause for download procedure
898 -- components : (a,b,c)
899 -- 'CALCULATE_RANGES' - where clause for calculated ranges
900 -- components : (b,c)
901 -- 'DELETE_SOURCE' - where clause for delete source
902 -- components : (c)
903 -- ------------------------------------------------------------------------
904 procedure get_dt_subqry_where_clause
905 (
906 p_table_info in hr_dm_gen_main.t_table_info,
907 p_where_clause in out nocopy varchar2,
908 p_cursor_type in varchar2 default 'DOWNLOAD',
909 p_lpad_spaces in number default 2
910 ) is
911
912 l_parent_table_info hr_dm_gen_main.t_table_info;
913 begin
914
915 /*
916 Following where clause stmt will be created by the assignment below e.g
917 where adr.last_update_date >= nvl(p_migration_type,
918 adr.last_update_date)
919 adr - table alias name, address_id - surrogate primary key.
920 Note: it does not have business group_id.
921
922 If cursor is for calculate range then the where clause will not contain
923 the first line of the where clause mentioned above.
924 */
925
926 -- last update date comparison for additive migration of the where clause
927
928 if (p_table_info.missing_who_info = 'N') then
929 p_where_clause := p_where_clause || indent(17) || 'and ' ||
930 p_table_info.alias || '.' ||
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
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
946
947 -- ----------------------- get_dt_cursor_where_clause -------------------------
948 -- Description:
949 -- prepares the where clause for the date track table data to to be downloaded.
950 -- if the business group_id field does not exist in the table to be downloaded
951 -- then complex joins are to be made with the parent tables. Information about
952 -- the join is stored in hr_dm_hierarchy table for a given table.
953 -- The where clause consist of three things
954 -- a) range of surrogate id b) last_update_date and c) business_group_id
955 -- Depending upon the cursor type the where clause will be formed with the above
956 -- components.
957 -- p_cursor_type :- 'DOWNLOAD' - where clause for download procedure
958 -- components : (a,b,c)
959 -- ------------------------------------------------------------------------
960 procedure get_dt_cursor_where_clause
961 (
962 p_table_info in hr_dm_gen_main.t_table_info,
963 p_where_clause out nocopy varchar2,
964 p_cursor_type in varchar2 default 'DOWNLOAD',
965 p_lpad_spaces in number default 2
966 ) is
967
968 l_sub_from_clause varchar2(32767);
969 l_sub_where_clause varchar2(32767);
970 l_selective_criteria varchar2(32767);
971
972 -- get the selective formula criteria
973 cursor get_sel_formula is
974 select selective_migration_criteria
975 from hr_dm_migrations
976 where migration_id = p_table_info.migration_id
977 and migration_type = 'SF'
978 and selective_migration_criteria is not null;
979
980
981 begin
982
983 /*
984 Following where clause stmt will be created by the assignment below e.g
985 where ff1.formula_id between p_start_id and p_end_id
986 and ff1.business_group_id = p_business_group_id -- only if table has
987 -- explicit business_group_id
988 and exists ( select 1
989 from avt_ff_formulas ff
990 where ff.formula_id = ff1.formula_id
991 and ff.last_update_date >= NVL(p_last_migration_date,
992 ff1.last_update_date)
993 );
994 Note: it does not have business group_id.
995
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
1005 -- derive_sql_download_full field is not null.If yes then
1006 -- call get_derive_where_clause procedure to format the where
1007 -- clause defined in the table.
1008
1009 if p_table_info.derive_sql_download_full is not null then
1010 get_derive_where_clause ( p_table_info => p_table_info,
1011 p_where_clause => p_where_clause,
1012 p_cursor_type => 'DOWNLOAD_DT',
1013 p_lpad_spaces => p_lpad_spaces);
1014 return;
1015 end if;
1016 --
1017 -- if it is a table hierarchy then call the implicit business group where
1018 -- clause package to prepare the where clause, otherwise, create the where
1019 -- clause.
1020 if p_table_info.table_hierarchy = 'Y' then
1021 hr_dm_imp_bg_where.main (p_table_info => p_table_info,
1022 p_cursor_type => p_cursor_type,
1023 p_query_type => 'SUB_QUERY',
1024 p_where_clause => p_where_clause);
1025 else
1026
1027 p_where_clause := lpad(' ',p_lpad_spaces) || 'where ';
1028
1029 -- put the search condition of id between start and end id.
1030
1031 if p_table_info.surrogate_primary_key = 'Y'
1032 then
1033 p_where_clause := p_where_clause || p_table_info.alias || '1.' ||
1034 p_table_info.surrogate_pk_column_name || ' between p_start_id and ' ||
1035 'p_end_id';
1036
1037 p_where_clause := p_where_clause || indent(p_lpad_spaces) || 'and ';
1038 end if;
1039
1040 -- if business group id field exists in the table then put the business
1041 -- group condition in the main query
1042
1043 if p_table_info.table_hierarchy = 'N' and
1044 p_table_info.global_data = 'N'
1045 then
1046 -- derive business group id search condition from parent tables.
1047 p_where_clause := p_where_clause || p_table_info.alias || '1.' ||
1048 'business_group_id = p_business_group_id';
1049
1050 p_where_clause := p_where_clause || indent(p_lpad_spaces) || 'and ';
1051 end if;
1052
1053
1054 -- add the selective migration clause for FF_FORMULAS table
1055 if lower(p_table_info.table_name) = 'ff_formulas_f' then
1056 open get_sel_formula;
1057 fetch get_sel_formula into l_selective_criteria;
1058 if get_sel_formula%found then
1059 format_selective_where_clause (p_text => l_selective_criteria);
1060 p_where_clause := p_where_clause || p_table_info.alias
1061 || '1.formula_name in ' || l_selective_criteria ||
1062 indent(p_lpad_spaces) || 'and ';
1063 end if;
1064 close get_sel_formula;
1065 end if;
1066
1067
1068 -- prepare the sub query.
1069 p_where_clause := p_where_clause || ' exists ( select 1';
1070
1071 -- get the from clause for sub query
1072 get_cursor_from_clause (p_table_info => p_table_info,
1073 p_from_clause => l_sub_from_clause,
1074 p_lpad_spaces => 17);
1075
1076 -- get the where clause for sub query
1077 l_sub_where_clause := lpad(' ',17) || 'where ';
1078
1079 l_sub_where_clause := l_sub_where_clause || p_table_info.alias || '1.'||
1080 p_table_info.surrogate_pk_column_name || ' = '|| p_table_info.alias
1081 || '.'|| p_table_info.surrogate_pk_column_name;
1082
1083
1084 -- get the where clause of sub query.
1085 get_dt_subqry_where_clause (p_table_info => p_table_info,
1086 p_where_clause => l_sub_where_clause,
1087 p_cursor_type => p_cursor_type,
1088 p_lpad_spaces => 17);
1089 /*
1090 p_where_clause := p_where_clause || indent ||
1091 l_sub_from_clause || indent ||
1092 l_sub_where_clause || indent(16) || ')' ||
1093 indent(2) || 'order by ' ||
1094 p_table_info.surrogate_pk_column_name || ';';
1095 p_cursor_type
1096 */
1097 p_where_clause := p_where_clause || indent ||
1098 l_sub_from_clause || indent ||
1099 l_sub_where_clause || indent(16) || ')';
1100
1101 if p_cursor_type <> 'DELETE_SOURCE' then
1102 p_where_clause := p_where_clause || indent(2) || 'order by ' ||
1103 p_table_info.surrogate_pk_column_name || ';';
1104 else
1105 p_where_clause := p_where_clause || ';';
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
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
1121
1122 -- ----------------------- prepare_download_cursor --------------------------------
1123 -- Description:
1124 -- Preapre the cursor for download data.
1125 -- The download cursor fall into two categories.
1126 -- o Non date track table download (Full and Additive migration) and
1127 -- Date Track Full migration. Cursor where clause is same for both types.
1128 -- o Date Track table Additive migration. In this type the objective is to
1129 -- fetch all the physical records of the logical record if one or more
1130 -- physical records matches the criteria. So subquery is used to achieve
1131 -- this objective.
1132 -- Input Parameters :
1133 -- p_cursor_type - It can have following values :
1134 -- FULL_BG_MIGRATION - full business group migration for date track
1135 -- and non date track table.
1136 -- ADDITIVE_BG_MIGRATION : Additive business group id for date track
1137 -- table only as for non date track the cursor for full or additive
1138 -- migration is same.
1139 -- ------------------------------------------------------------------------
1140 procedure prepare_download_cursor
1141 (
1142 p_table_info in hr_dm_gen_main.t_table_info,
1143 p_cursor out nocopy varchar2,
1144 p_cursor_type in varchar2 default 'FULL_BG_MIGRATION',
1145 p_hier_column in varchar2 default 'N'
1146 )
1147 is
1148 l_cursor_comment varchar2(2000);
1149 l_cursor_defination varchar2(2000);
1150 l_cursor_select_cols varchar2(32767);
1151 l_cursor_select_from varchar2(32767);
1152 l_cursor_select_where varchar2(32767);
1153 l_columns_tbl hr_dm_library.t_varchar2_tbl;
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;
1163 else
1164 l_columns_tbl := g_columns_tbl;
1165 end if;
1166
1167 -- comments about the cursor
1168 l_cursor_comment := indent || '--' || indent ||
1169 '-- cursor to select the data from the ' ||
1170 p_table_info.table_name || ' table to be migrated.' ||
1171 indent || '--';
1172
1173 -- comment and definition of cursor
1174 if p_cursor_type = 'FULL_BG_MIGRATION' then
1175 if p_table_info.datetrack = 'Y' then
1176 l_cursor_comment := format_comment(' cursor to select the data from the '
1177 || p_table_info.table_name || ' for FULL business group migration.',2 );
1178 l_cursor_defination := ' cursor csr_full_mig_' || p_table_info.alias ||
1179 ' is ';
1180 else
1181 l_cursor_comment := format_comment(' cursor to select the data from the '
1182 || p_table_info.table_name || ' table to be migrated.',2);
1183 l_cursor_defination := ' cursor csr_mig_' || p_table_info.alias || ' is ';
1184 end if;
1185 else
1186 l_cursor_comment := format_comment(' cursor to select the data from the '
1187 || p_table_info.table_name || ' for ADDITIVE business group migration.',2 );
1188 l_cursor_defination := ' cursor csr_adt_mig_' || p_table_info.alias ||
1189 ' is ';
1190 end if;
1191 --
1192 -- for normal main query the column name will be alias.col1,alias.col2 but
1193 -- for sub query it will be alias1.col1,alias1.col2
1194 --
1195 if p_cursor_type = 'FULL_BG_MIGRATION' then
1196 l_prefix_col := p_table_info.alias || '.';
1197 else
1198 l_prefix_col := p_table_info.alias || '1.';
1199 end if;
1200
1201 -- select columns in the cursor. This will return the list of all columns
1202 -- of the table separated by comma's.
1203
1204
1205
1206 l_cursor_select_cols := hr_dm_library.conv_list_to_text( p_rpad_spaces => 8,
1207 p_columns_tbl => l_columns_tbl,
1208 p_prefix_col => l_prefix_col);
1209
1210 -- Key combination tables have foreign key on AOL table
1211 -- 'FND_ID_FLEX_STRUCTURES' i.e hierarchy_type = 'A'. To identify
1212 -- the business group for a row in these tables it is required to
1213 -- check whether the ID value of the row is used by any of the
1214 -- child tables. The 'Where' clause generated by this generator
1215 -- does not use the 'exists' clause which results in the same row
1216 -- being fetch equals to the number of child rows it matches. To
1217 -- avoid this ' distinct' clause is added for only these tables.
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')
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;
1235
1236
1237 if p_cursor_type = 'FULL_BG_MIGRATION' then
1238 -- get from clause
1239 get_cursor_from_clause (p_table_info => p_table_info,
1240 p_from_clause => l_cursor_select_from);
1241
1242 -- get where clause
1243 get_gen_cursor_where_clause (p_table_info => p_table_info,
1244 p_where_clause => l_cursor_select_where);
1245
1246
1247 else -- date track table additive migration cursor.
1248 -- get from clause
1249 l_cursor_select_from := ' from ' || p_table_info.table_name || ' ' ||
1250 p_table_info.alias || '1';
1251
1252 -- get where clause for date track
1253 get_dt_cursor_where_clause (p_table_info => p_table_info,
1254 p_where_clause => l_cursor_select_where);
1255
1256
1257 end if;
1258
1259 -- finally put the components of where clause together
1260 p_cursor := l_cursor_comment || indent ||
1261 l_cursor_defination || indent ||
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
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 ------------------
1276 -- Description:
1277 -- Define private procedures to open and fetch cursors depending
1278 -- upon the migration type i.e full or additive for datetrack tables.
1279 -- ------------------------------------------------------------------------
1280
1281 procedure prepare_dt_private_procedures
1282 (
1283 p_table_info in hr_dm_gen_main.t_table_info,
1284 p_body out nocopy varchar2
1285 ) is
1286 l_proc_body varchar2(32767) := null;
1287 l_indent number;
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.
1297 l_proc_body := format_comment('Private procedure to open the cursor depending '
1298 || 'upon the migration type i.e full or additive', l_indent);
1299
1300 --
1301 -- define the private procedure open_cursor
1302 --
1303 l_proc_body := l_proc_body || indent(l_indent) ||
1304 'procedure private_open_cursor is' || indent(l_indent) || 'begin';
1305
1306 --
1307 -- define the body of private procedure open_cursor.
1308 --
1309
1310 l_indent := 4;
1311 l_proc_body := l_proc_body || indent(l_indent) ||
1312 'if p_last_migration_date is null then ' ||
1313 format_comment('Open cursor for a Full Migration of a business group',
1314 l_indent + 2,'N') || indent(l_indent + 2) ||
1315 'open csr_full_mig_' || p_table_info.alias || ';' || indent(l_indent) ||
1316 'else -- additive migration' || indent(l_indent + 2) ||
1317 format_comment('Open cursor for a Additive Migration of a business group'
1318 , l_indent + 2, 'N') || indent(l_indent + 2) ||
1319 'open csr_adt_mig_' || p_table_info.alias || ';' || indent(l_indent) ||
1320 'end if;';
1321
1322 l_indent := 2;
1323 l_proc_body := l_proc_body || indent(l_indent) ||'end private_open_cursor;';
1324
1325 --
1326 -- define the private procedure fetch_cursor
1327 --
1328
1329 l_proc_body := l_proc_body || indent(l_indent) ||
1330 format_comment('Fetch another row from the cursor depending upon migration '
1331 || 'type.', l_indent);
1332
1333 l_proc_body := l_proc_body || indent(l_indent) ||
1334 'procedure private_fetch_cursor is' || indent(l_indent) || 'begin';
1335
1336 --
1337 -- define the body of private procedure fetch_cursor.
1338 --
1339
1340 l_indent := 4;
1341
1342 l_proc_body := l_proc_body || indent(l_indent) ||
1343 'if p_last_migration_date is null then ' ||
1344 format_comment('Fetch a row from the full migration cursor.', l_indent + 2) ||
1345 indent(l_indent + 2) ||
1346 'fetch '|| l_full_mig_csr_name || ' into l_table_rec;' || indent(l_indent +2) ||
1347 'if ' || l_full_mig_csr_name || '%found then' || indent(l_indent +4) ||
1348 'l_row_fetched := TRUE;' || indent(l_indent +2) ||
1349 'else -- no row fetched' || indent(l_indent +4) ||
1350 'l_row_fetched := FALSE;' || indent(l_indent +4) ||
1351 'close ' || l_full_mig_csr_name || ';' || indent(l_indent +2) || 'end if;' ||
1352 indent(l_indent) || 'else -- additive migration' || indent(l_indent + 2)
1353 || format_comment('Fetch a row from the additive migration cursor',
1354 l_indent + 2)|| indent(l_indent + 2) ||
1355 'fetch '|| l_adt_mig_csr_name || ' into l_table_rec;' || indent(l_indent +2) ||
1356 'if ' || l_adt_mig_csr_name || '%found then' || indent(l_indent +4) ||
1357 'l_row_fetched := TRUE;' || indent(l_indent +2) ||
1358 'else -- no row fetched' || indent(l_indent +4) ||
1359 'l_row_fetched := FALSE;' || indent(l_indent +4) ||
1360 'close ' || l_adt_mig_csr_name || ';' || indent(l_indent +2) || 'end if;' ||
1361 indent(l_indent) || 'end if;';
1362
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',
1372 '(none)','R');
1373 raise;
1374 end prepare_dt_private_procedures;
1375 -- ----------------------- nullify_hierarchical_cols -----------------------
1376 -- Description:
1377 -- Generates the text which assigns null values to the hierarchical columns.
1378 -- hier_col1 := null;
1379 -- hier_col2 := null;
1380 -- ------------------------------------------------------------------------
1381 procedure nullify_hierarchical_cols
1382 (
1383 p_table_info in hr_dm_gen_main.t_table_info,
1384 p_lpad_spaces in number,
1385 p_body out nocopy varchar2
1386 )
1387 is
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;
1397
1398 p_body := rpad(' ', p_lpad_spaces) || indent ||
1399 format_comment('assign null values to hierarchical columns',p_lpad_spaces)
1400 || indent;
1401
1402 -- read the hierarchy columns one by one and set them to null except the
1403 -- surrogate id.
1404
1405 while l_list_index is not null loop
1406 --
1407 -- do not nullify the surrogate_id
1408 --
1409 if g_hier_columns_tbl(l_list_index) <> p_table_info.surrogate_pk_column_name
1410 then
1411 p_body := p_body || indent(p_lpad_spaces) || 'l_table_rec.' ||
1412 g_hier_columns_tbl(l_list_index) || ' := null;';
1413 end if;
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',
1423 '(none)','R');
1424 raise;
1425 end nullify_hierarchical_cols;
1426
1427 -- ----------------------- generate_get_link_value -----------------------
1428 -- Description:
1429 -- Generates the get link value from the sequence procedure of the TDS
1430 -- ------------------------------------------------------------------------
1431 procedure generate_get_link_value
1432 (
1433 p_table_info in hr_dm_gen_main.t_table_info
1434 )
1435 is
1436 l_interface varchar2(32767);
1437 l_locals varchar2(32767) := null;
1438 l_cursor varchar2(32767) := null;
1439 l_proc_comment varchar2(4000);
1440 l_cursor_name varchar2(30) := 'csr_get_link_value';
1441
1442 -- block body of the procedure i.e between begin and end.
1443 l_proc_body varchar2(32767) := null;
1444
1445 -- indentation for the statements.it specifies number of blank spaces
1446 -- after which the staement should start.
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
1456 -- local variables of the procedure
1457
1458 l_locals := indent || ' --' || indent ||
1459 ' -- Declare cursors and local variables' || indent ||
1460 ' --' || indent ||
1461 ' l_proc varchar2(72) := g_package ' ||
1462 '|| ''get_link_value'';' || indent ||
1463 ' l_link_value number;' || indent;
1464
1465 -- cursor to get the link value from the sequence from the data pump table
1466 l_cursor := format_comment('Cursor to get the link value from the link '||
1467 'value sequence.');
1468
1469 l_cursor := l_cursor || indent || ' cursor csr_get_link_value is select '
1470 || 'hr_dm_link_value_s.nextval' || indent ||
1471 ' from dual;';
1472 -- add the logic of the body
1473
1474 l_indent := 2;
1475
1476 l_proc_body := l_proc_body || indent(l_indent) ||
1477 'open ' || l_cursor_name || ';' || indent(l_indent) || 'fetch ' ||
1478 l_cursor_name || ' into l_link_value;' || indent(l_indent) ||
1479 'close ' || l_cursor_name || ';' || indent(l_indent) ||
1480 ' p_link_value := l_link_value;';
1481
1482 l_proc_body := l_proc_body || indent || 'end get_link_value;';
1483
1484
1485 l_proc_comment := format_comment('procedure to get the link value from the '
1486 || 'link sequence. Physical records of a date track logical record will ' ||
1487 ' have same link value so as to enable the processing of logical record by '
1488 || 'single thread while uploading. Used by Data Pump.') || indent;
1489
1490
1491 -- add the procedure comment,local variables , cursor and procedure body to
1492 -- complete the procedure
1493
1494 l_proc_body_tbl(1) := l_proc_comment || 'procedure get_link_value' ||
1495 l_interface || ' is' || l_locals || l_cursor || indent ||
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
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
1510 -- ----------------------- generate_developer_key_func -----------------------
1511 -- Description:
1512 -- This function will
1513 -- - Generates the procedure which will get the developer key from the
1514 -- AOL table for the column which have a foreign key to AOL table.
1515 -- - Creates a call to the function to get the developer key
1516 -- for the column value which has a foreign key on AOL table.
1517 -- - Creates a local variable for the developer key.
1518 --
1519 -- This procedure will generate the function for each column which have a
1520 -- foreign key to AOL table.
1521 -- Assumption : Id value is assumed to be number and developer key is assumed
1522 -- to be varchar2.
1523 -- Input Parameters
1524 -- p_fk_to_aol_columns_tbl - Contains the information about all the columns
1525 -- which have foreign key to AOL table.
1526 -- p_table_info - Contains the info about the table to be
1527 -- downloaded
1528 -- Out Parameters
1529 -- p_body - returns the actual procedure body for getting
1530 -- the
1531 -- p_call_to_proc_body - returns the string for a call to the function
1532 -- p_dev_key_local_var_body - returns the string defining local var for
1533 -- developer key
1534 -- ------------------------------------------------------------------------
1535 procedure generate_developer_key_func
1536 (
1537 p_fk_to_aol_columns_tbl in hr_dm_gen_main.t_fk_to_aol_columns_tbl,
1538 p_table_info in hr_dm_gen_main.t_table_info,
1539 p_call_to_proc_body in out nocopy varchar2,
1540 p_dev_key_local_var_body in out nocopy varchar2
1541 )
1542 is
1543 l_interface varchar2(32767);
1544 l_locals varchar2(32767) := null;
1545 l_cursor varchar2(32767) := null;
1546 l_where_clause varchar2(32767) := null;
1547 l_proc_comment varchar2(4000);
1548 l_cursor_name varchar2(30) := 'csr_get_developer_key';
1549 l_proc_name varchar2(32767);
1550
1551 -- variables to store the information about p_fk_to_aol_columns_tbl elements.
1552 -- this is to reduce the variable name and add clarity.
1553
1554 l_column_name hr_dm_hierarchies.column_name%type;
1555 l_parent_table_id hr_dm_hierarchies.parent_table_id%type;
1556 l_parent_table_name hr_dm_tables.table_name%type;
1557 l_parent_table_alias hr_dm_tables.table_alias%type;
1558 l_parent_column_name hr_dm_hierarchies.parent_column_name%type;
1559 l_parent_id_column_name hr_dm_hierarchies.parent_id_column_name%type;
1560
1561
1562 -- block body of the procedure i.e between begin and end.
1563 l_proc_body varchar2(32767) := null;
1564
1565 -- indentation for the statements.it specifies number of blank spaces
1566 -- after which the staement should start.
1567
1568 l_indent number;
1569 l_index number := p_fk_to_aol_columns_tbl.first;
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.
1579
1580 while l_index is not null loop
1581
1582 l_column_name :=
1583 p_fk_to_aol_columns_tbl(l_index).column_name;
1584 l_parent_table_id :=
1585 p_fk_to_aol_columns_tbl(l_index).parent_table_id;
1586 l_parent_table_name :=
1587 p_fk_to_aol_columns_tbl(l_index).parent_table_name;
1588 l_parent_table_alias :=
1589 p_fk_to_aol_columns_tbl(l_index).parent_table_alias;
1590 l_parent_column_name :=
1591 p_fk_to_aol_columns_tbl(l_index).parent_column_name;
1592 l_parent_id_column_name :=
1593 p_fk_to_aol_columns_tbl(l_index).parent_id_column_name;
1594
1595 l_where_clause := l_parent_id_column_name || ' = ' ||
1596 rpad('p_' || l_column_name, 30);
1597
1598 l_proc_name := 'get_developer_key_frm_' || l_parent_table_alias;
1599
1600 -- input parameters for the procedure
1601 l_interface := indent || '(' ||rpad('p_' || l_column_name, 30) ||
1602 ' in number,' || indent || ' ' ||
1603 rpad('p_'|| l_parent_column_name ,30) || ' out nocopy varchar2)';
1604
1605 -- local variables of the procedure
1606
1607 l_locals := indent || ' --' || indent ||
1608 ' -- Declare cursors and local variables' || indent ||
1609 ' --' || indent ||
1610 ' l_proc varchar2(72) := g_package ' ||
1611 '|| ''' || l_proc_name || ''';' || indent;
1612
1613 -- cursor to get the link value from the sequence from the data pump table
1614 l_cursor := format_comment('Cursor to get the developer key from the '||
1615 l_parent_table_name || ' table.',2);
1616
1617 if lower(l_parent_column_name) <> 'id_flex_structure_name' then
1618 l_cursor := l_cursor || indent(2) || 'cursor csr_get_developer_key is ' ||
1619 'select ' || l_parent_column_name || indent(33) ||
1620 'from ' || l_parent_table_name ||
1621 indent(33) || 'where ' || rtrim(l_where_clause) || ';';
1622 else
1623 -- for id_flex_num, we need both id_flex_structure_name and
1624 -- id_flex_code, so download both, concaternated with
1625 -- -dm-dev-key- as a seperator
1626 l_table := upper(p_table_info.table_name);
1627 if (l_table = 'HR_SOFT_CODING_KEYFLEX')
1628 or
1629 (l_table = 'HR_DMVP_HR_SOFT_CODING_KEYFLEX') then
1630 l_code := 'SCL';
1631 elsif (l_table = 'PAY_COST_ALLOCATION_KEYFLEX') then
1632 l_code := 'COST';
1633 elsif (l_table = 'PAY_EXTERNAL_ACCOUNTS') then
1634 l_code := 'BANK';
1635 elsif (l_table = 'PAY_PEOPLE_GROUPS') then
1636 l_code := 'GRP';
1637 elsif (l_table = 'PER_GRADE_DEFINITIONS') then
1638 l_code := 'GRD';
1639 elsif (l_table = 'PER_POSITION_DEFINITIONS') then
1640 l_code := 'POS';
1641 elsif (l_table = 'PER_PERSON_ANALYSES')
1642 or
1643 (l_table = 'PER_SPECIAL_INFO_TYPES')
1644 or
1645 (l_table = 'PER_ANALYSIS_CRITERIA') then
1646 l_code := 'PEA';
1647 elsif (l_table = 'PER_JOB_GROUPS')
1648 or
1649 (l_table = 'HR_DMV_PER_JOB_GROUPS')
1650 or
1651 (l_table = 'PER_JOB_DEFINITIONS') then
1652 l_code := 'JOB';
1653 end if;
1654
1655 l_cursor := l_cursor || indent(2) || 'cursor csr_get_developer_key is ' ||
1656 'select id_flex_structure_name' ||
1657 indent(33) || ' || ''-dm-dev-key-' || l_code || '''' ||
1658 indent(33) || 'from fnd_id_flex_structures_vl' ||
1659 indent(33) || 'where ' || rtrim(l_where_clause) ||
1660 indent(33) || ' and id_flex_code = ''' || l_code || ''';';
1661 end if;
1662
1663
1664 -- add the logic of the body
1665
1666 l_indent := 2;
1667
1668 l_proc_body := indent(l_indent) ||
1669 'open ' || l_cursor_name || ';' || indent(l_indent) || 'fetch ' ||
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);' ||
1679 indent(l_indent + 2) ||
1680 'hr_utility.raise_error;' || indent(l_indent) || 'else' ||indent(l_indent + 2)
1681 ||'close ' || l_cursor_name || ';' || indent(l_indent) || 'end if;';
1682
1683
1684 l_proc_body := l_proc_body || indent || 'end ' || l_proc_name || ';';
1685
1686
1687 l_proc_comment := format_comment('procedure to get the the developer key '
1688 || 'from the '|| l_parent_table_name || ' table for ' ||
1689 l_column_name || ' column.' ) || indent;
1690
1691
1692 -- add the procedure commentlocal variables , cursor and procedure body to
1693 -- complete the procedure
1694
1695 l_proc_body_tbl(1) := l_proc_comment || 'procedure ' || l_proc_name ||
1696 l_interface || ' is' || l_locals || l_cursor || indent ||
1697 'begin' || indent || l_proc_body;
1698
1699 -- add the body of this procedure to the package.
1700 add_to_package_body( l_proc_body_tbl );
1701
1702 -- construct a call to the procedure created above.
1703 l_indent := 6;
1704 p_call_to_proc_body := p_call_to_proc_body || indent ||
1705 format_comment('Get the developer key for ' || l_column_name || '.',
1706 l_indent);
1707 p_call_to_proc_body := p_call_to_proc_body || indent(l_indent) ||
1708 l_proc_name || '(l_table_rec.' || rpad(l_column_name,28) || ',' ||
1709 indent(l_indent + 25) || 'l_' || rpad(l_parent_column_name,28) || ');';
1710
1711 -- construct the defination of local variable
1712 l_indent := 2;
1713
1714 -- for id_flex_structure_name of fnd_id_flex_structures_vl
1715 -- use varchar2(2000) as column type
1716 -- to allow for encoding of name plus code
1717 if (l_parent_table_name = 'fnd_id_flex_structures_vl')
1718 and
1719 (l_parent_column_name = 'id_flex_structure_name') then
1720 p_dev_key_local_var_body := p_dev_key_local_var_body || indent(l_indent)
1721 || 'l_' || rpad(l_parent_column_name,28) || ' varchar2(2000);';
1722 else
1723 p_dev_key_local_var_body := p_dev_key_local_var_body || indent(l_indent)
1724 || 'l_' || rpad(l_parent_column_name,28) || ' ' || l_parent_table_name
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',
1734 '(none)','R');
1735 raise;
1736 end generate_developer_key_func;
1737
1738 -- ----------------------- generate_download --------------------------------
1739 -- Description:
1740 -- Generates the download procedure of the TDS
1741 -- ------------------------------------------------------------------------
1742 procedure generate_download
1743 (
1744 p_table_info in hr_dm_gen_main.t_table_info,
1745 p_header in out nocopy varchar2,
1746 p_hier_column in varchar2 default 'N',
1747 p_call_to_proc_body in varchar2,
1748 p_dev_key_local_var_body in varchar2,
1749 p_fk_to_aol_columns_tbl in hr_dm_gen_main.t_fk_to_aol_columns_tbl
1750 )
1751 is
1752 l_interface varchar2(32767);
1753 l_locals varchar2(32767) := null;
1754 l_cursor varchar2(32767) := null;
1755 l_adt_cursor varchar2(32767) := null;
1756 l_comment varchar2(4000);
1757 l_proc_comment varchar2(4000);
1758 l_cursor_name varchar2(30) := 'csr_mig_' || p_table_info.alias;
1759 l_func_name varchar2(30) := 'download';
1760 l_dp_func_name varchar2(100);
1761 l_null_col varchar2(30);
1762
1763
1764 -- block body of the procedure i.e between begin and end.
1765 l_proc_body varchar2(32767) := null;
1766 l_debug_message_text varchar2(32767) := null;
1767 l_func_body varchar2(32767) := null;
1768
1769 -- block body to store private procedures
1770 l_prv_proc_body varchar2(32767) := null;
1771
1772 -- indentation for the statements.it specifies number of blank spaces
1773 -- after which the staement should start.
1774
1775 l_indent number;
1776 l_columns_tbl hr_dm_library.t_varchar2_tbl;
1777
1778 l_proc_body_tbl t_varchar2_32k_tbl;
1779 l_proc_index number := 1;
1780
1781 cursor csr_null_hierarchy is
1782 select h.column_name
1783 from hr_dm_hierarchies h,
1784 hr_dm_tables t
1785 where h.hierarchy_type = 'N'
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;
1795 l_func_name := 'download_hierarchy';
1796 l_dp_func_name := 'hrdpp_h' || p_table_info.short_name || '.insert_batch_lines';
1797 else
1798 l_columns_tbl := g_columns_tbl;
1799 l_func_name := 'download';
1800 l_dp_func_name := 'hrdpp_u' || p_table_info.short_name || '.insert_batch_lines';
1801 end if;
1802
1803 -- input parameters for the procedure
1804
1805 l_interface := indent ||
1806 '(p_migration_type in varchar2,' || indent ||
1807 ' p_business_group_id in number,' || indent ||
1808 ' p_last_migration_date in date,' || indent ||
1809 ' p_start_id in number,' || indent ||
1810 ' p_end_id in number,' || indent ||
1811 ' p_batch_id in number,' || indent ||
1812 ' p_chunk_size in number,' || indent ||
1813 ' p_rec_downloaded out nocopy number)' || indent;
1814
1815
1816 l_proc_body_tbl(l_proc_index) := l_interface;
1817 l_proc_index := l_proc_index + 1;
1818
1819 -- message (' l_interface = ' || l_interface);
1820 -- local variables of the procedure
1821
1822 l_locals := indent ||
1823 ' -- Declare local variables' || indent ||
1824 ' l_proc varchar2(72) := g_package ' ||
1825 '|| ''' || l_func_name || ''' ;' || indent ||
1826 ' l_link_value number;' || indent ||
1827 ' l_rec_inserted_cnt number := 0;' || indent ||
1828 ' l_row_fetched boolean := FALSE;' || indent;
1829
1830 -- if table has a column which have foreign key to the AOL table then create
1831 -- local variable for each corresponding developer key.
1832
1833 if p_table_info.fk_to_aol_table = 'Y' then
1834 l_locals := l_locals || p_dev_key_local_var_body || indent;
1835 end if;
1836
1837
1838 --
1839 -- if p_hier_column flag is set to 'Y' then prepare the download cursor to
1840 -- get only hierarchy cols only. If it is 'N' then prepare the download
1841 -- cursor to get all the columns.
1842 --
1843
1844 if p_table_info.datetrack = 'N' then
1845 -- call prepare_down_load procedure to create the cursor.
1846 prepare_download_cursor ( p_table_info => p_table_info,
1847 p_cursor => l_cursor,
1848 p_cursor_type => 'FULL_BG_MIGRATION',
1849 p_hier_column => p_hier_column);
1850 l_locals := l_locals ||
1851 ' l_table_rec ' || 'csr_mig_' ||
1852 lower(p_table_info.alias) ||'%rowtype;' || indent;
1853 else -- date track table
1854 --
1855 -- for datetrack table two cursors are required.
1856 -- o For FULL data migration of the business group.
1857 -- o For ADDITIVE migration of the business group.
1858 --
1859
1860 -- call prepare_down_load procedure to create the cursor for FULL data
1861 -- migration
1862 prepare_download_cursor ( p_table_info => p_table_info,
1863 p_cursor => l_cursor,
1864 p_cursor_type => 'FULL_BG_MIGRATION',
1865 p_hier_column => p_hier_column);
1866
1867 -- call prepare_down_load procedure to create the cursor for ADDITIVE
1868 -- data migration
1869
1870 prepare_download_cursor ( p_table_info => p_table_info,
1871 p_cursor => l_adt_cursor,
1872 p_cursor_type => 'ADDITIVE_BG_MIGRATION',
1873 p_hier_column => p_hier_column);
1874
1875 -- declare private procedures to open and fetch cursors depending
1876 -- upon the migration type i.e full or additive.
1877 prepare_dt_private_procedures (p_table_info => p_table_info,
1878 p_body => l_prv_proc_body);
1879
1880
1881 l_locals := l_locals ||
1882 ' l_table_rec ' || 'csr_full_mig_' ||
1883 lower(p_table_info.alias) ||'%rowtype;' || indent || ' ' ||
1884 rpad('l_prv_' || p_table_info.surrogate_pk_column_name,30)
1885 ||' number := -999;' || indent;
1886 end if;
1887
1888 -- message (' l_cursor = ' || l_cursor);
1889 -- add the body of the download procedure
1890
1891 l_indent := 2;
1892 l_proc_body_tbl(l_proc_index) := indent(l_indent) ||
1893 'l_rec_inserted_cnt := 0;' || indent(l_indent) || 'begin';
1894 l_proc_index := l_proc_index + 1;
1895
1896 add_debug_messages (p_table_info => p_table_info,
1897 p_procedure_name => l_func_name,
1898 p_message_location => 'START',
1899 p_proc_body => l_debug_message_text);
1900 l_proc_body_tbl(l_proc_index) := l_debug_message_text;
1901 l_proc_index := l_proc_index + 1;
1902 l_indent := 4;
1903
1904 if p_table_info.datetrack = 'N' then
1905
1906 l_proc_body_tbl(l_proc_index) := indent(l_indent) ||
1907 'open ' || l_cursor_name || ';' || indent(l_indent) || 'loop';
1908 l_proc_index := l_proc_index + 1;
1909
1910 l_indent := 6;
1911 l_proc_body_tbl(l_proc_index) := indent(l_indent) ||
1912 'fetch ' || l_cursor_name ||' into l_table_rec;' ||indent(l_indent) ||
1913 'if ' || l_cursor_name || '%notfound then' || indent(l_indent + 2) ||
1914 'close ' || l_cursor_name || ';' || indent(l_indent + 2) ||
1915 'l_row_fetched := FALSE;' || indent(l_indent) || 'else' ||
1916 indent(l_indent + 2) || 'l_row_fetched := TRUE;' || indent(l_indent) ||
1917 'end if;';
1918 l_proc_index := l_proc_index + 1;
1919 else -- datetrack
1920 l_proc_body_tbl(l_proc_index) := indent(l_indent) ||
1921 'private_open_cursor;' || indent(l_indent) || 'loop' || indent(l_indent + 2)
1922 || 'private_fetch_cursor;' ;
1923 l_proc_index := l_proc_index + 1;
1924 end if;
1925
1926 l_indent := 6;
1927
1928 l_comment := format_comment('if no row fetched then exit the loop',l_indent);
1929
1930
1931 l_proc_body_tbl(l_proc_index) := l_comment || indent(l_indent) ||
1932 'if not l_row_fetched then' ||indent(l_indent + 2) || 'exit;' ||
1933 indent(l_indent) || 'end if;';
1934 l_proc_index := l_proc_index + 1;
1935
1936
1937 --
1938 -- if it is a datetrack then add the code to get the link value from the
1939 -- sequence.
1940 --
1941 if p_table_info.datetrack = 'Y' then
1942 l_proc_body_tbl(l_proc_index) := indent(l_indent) ||
1943 'if ' || rpad('l_prv_' || p_table_info.surrogate_pk_column_name,30) ||
1944 ' <> '|| 'l_table_rec.' || p_table_info.surrogate_pk_column_name ||
1945 ' then' || indent(l_indent + 2) || 'get_link_value(l_link_value);' ||
1946 indent(l_indent) || 'end if;';
1947 l_proc_index := l_proc_index + 1;
1948 end if;
1949
1950 -- if p_hier_column is 'N' i.e this procedure is generated for download of
1951 -- all columns. If this table has a column hierarchy then set the hierarchy
1952 -- columns to null.
1953
1954 if p_hier_column = 'N' and p_table_info.column_hierarchy = 'Y' then
1955 nullify_hierarchical_cols ( p_table_info => p_table_info,
1956 p_lpad_spaces => l_indent,
1957 p_body => l_proc_body );
1958 end if;
1959
1960
1961 -- if table has a column which have foreign key to the AOL table then call
1962 -- procedures to get the developer key.
1963
1964 if p_table_info.fk_to_aol_table = 'Y' then
1965 l_proc_body := l_proc_body || p_call_to_proc_body ;
1966 end if;
1967
1968 l_comment := 'insert the data into batch_lines table of datapump for ' ||
1969 'all columns of ' ||p_table_info.table_name ||
1970 ' and assign null values to hierarchy columns.';
1971
1972 l_proc_body := l_proc_body || format_comment(l_comment,l_indent);
1973
1974 l_proc_body_tbl(l_proc_index) := l_proc_body;
1975 l_proc_index := l_proc_index + 1;
1976
1977
1978 -- nullify columns where an N hierarchy exists
1979 -- only for download procedure
1980
1981 if p_hier_column = 'N' then
1982 open csr_null_hierarchy;
1983 loop
1984 fetch csr_null_hierarchy into l_null_col;
1985 exit when csr_null_hierarchy%notfound;
1986 l_comment := 'Nullify column ' || l_null_col || ' for N hierarchy.';
1987 l_proc_body_tbl(l_proc_index) := format_comment(l_comment,l_indent) ||
1988 indent(l_indent) ||
1989 'l_table_rec.' ||
1990 lower(l_null_col) ||
1991 ' := null;' || indent(l_indent) ;
1992 l_proc_index := l_proc_index + 1;
1993 end loop;
1994 close csr_null_hierarchy;
1995 end if;
1996
1997 -- form a call to data pump function to insert data into batch_lines
1998 -- table.
1999
2000 l_proc_body_tbl(l_proc_index) := indent(l_indent) || l_dp_func_name ||
2001 indent(l_indent) ||
2002 '( p_batch_id => p_batch_id'|| indent(l_indent) ||
2003 ' ,p_user_sequence => l_link_value' || indent(l_indent) ||
2004 ' ,p_link_value => l_link_value' || indent(l_indent) ||
2005 ' ,p_last_migration_date => p_last_migration_date' || indent(l_indent) ||
2006 ' ,p_migration_type => p_migration_type' || indent;
2007 l_proc_index := l_proc_index + 1;
2008
2009 -- if the columns of the table have foreign key to AOL table then call
2010 -- call assignment function which replaces those columns with developer key.
2011 if p_table_info.fk_to_aol_table = 'N' then
2012
2013 l_proc_body_tbl(l_proc_index) := hr_dm_library.get_func_asg (
2014 p_rpad_spaces => l_indent +1,
2015 p_columns_tbl => l_columns_tbl,
2016 p_prefix_left_asg => 'p_',
2017 p_prefix_right_asg => 'l_table_rec.',
2018 p_right_asg_pad_len => 55);
2019 else
2020 l_proc_body_tbl(l_proc_index) := hr_dm_library.get_func_asg_with_dev_key (
2021 p_rpad_spaces => l_indent +1,
2022 p_columns_tbl => l_columns_tbl,
2023 p_prefix_left_asg => 'p_',
2024 p_prefix_right_asg => 'l_table_rec.',
2025 p_right_asg_pad_len => 55,
2026 p_prefix_left_asg_dev_key => 'p_',
2027 p_prefix_right_asg_dev_key => 'l_',
2028 p_use_aol_id_col => 'N',
2029 p_fk_to_aol_columns_tbl => p_fk_to_aol_columns_tbl,
2030 p_resolve_pk_columns_tbl => g_resolve_pk_columns_tbl );
2031
2032 end if;
2033
2034 l_proc_body_tbl(l_proc_index) := l_proc_body_tbl(l_proc_index) || ');';
2035 l_proc_index := l_proc_index + 1;
2036
2037 --
2038 l_indent := 6;
2039 l_proc_body_tbl(l_proc_index) := indent(l_indent) ||
2040 'l_rec_inserted_cnt := l_rec_inserted_cnt + 1;' || indent(l_indent) ||
2041 format_comment('commit after every chunk_size_value (e.g. 10) records.',
2042 l_indent) || indent(l_indent) || 'if mod (l_rec_inserted_cnt, ' ||
2043 'p_chunk_size) = 0 then ' || indent(l_indent + 2) || 'commit;' ||
2044 indent(l_indent) || 'end if;';
2045
2046 l_proc_index := l_proc_index + 1;
2047 --
2048 -- if it is a datetrack then store the id value.
2049 --
2050 if p_table_info.datetrack = 'Y' then
2051 l_proc_body_tbl(l_proc_index) := indent(l_indent) ||
2052 rpad('l_prv_' || p_table_info.surrogate_pk_column_name,30) || ' := ' ||
2053 'l_table_rec.' || p_table_info.surrogate_pk_column_name || ';';
2054 l_proc_index := l_proc_index + 1;
2055 end if;
2056
2057 l_proc_body_tbl(l_proc_index) := indent(l_indent - 2) ||
2058 'end loop;' || indent(l_indent - 2) || 'commit;' || indent(l_indent - 2) ||
2059 'p_rec_downloaded := l_rec_inserted_cnt;' || indent(l_indent - 4) ||
2060 'end;';
2061 l_proc_index := l_proc_index + 1;
2062
2063 -- if p_hier_column is 'N' i.e this procedure is generated for download of
2064 -- all columns. If this table has a column hierarchy then include a call to
2065 -- call the download hieararchy procedure.
2066
2067
2068 if p_hier_column = 'N' and p_table_info.column_hierarchy = 'Y' then
2069 l_indent := 22;
2070 l_proc_body_tbl(l_proc_index) := indent ||
2071 ' download_hierarchy (p_migration_type,' || indent(l_indent) ||
2072 'p_business_group_id,' || indent(l_indent) ||
2073 'p_last_migration_date,' || indent(l_indent) ||
2074 'p_start_id,' || indent(l_indent) ||
2075 'p_end_id ,' || indent(l_indent) ||
2076 'p_batch_id ,' || indent(l_indent) ||
2077 'p_chunk_size,' || indent(l_indent) ||
2078 'l_rec_inserted_cnt);' || indent;
2079 l_proc_index := l_proc_index + 1;
2080 end if;
2081
2082 l_indent := 4;
2083 add_debug_messages (p_table_info => p_table_info,
2084 p_procedure_name => l_func_name,
2085 p_message_location => 'END',
2086 p_proc_body => l_debug_message_text);
2087 l_proc_body_tbl(l_proc_index) := l_debug_message_text;
2088 l_proc_index := l_proc_index + 1;
2089
2090 l_proc_comment := format_comment('procedure to download all columns of '
2091 || upper(p_table_info.table_name) || ' data into datapump interface table.')
2092 || indent;
2093
2094 -- add the procedure comment defination to the package header and body
2095 p_header := p_header || l_proc_comment ||'procedure ' || l_func_name ||
2096 l_proc_body_tbl(1) || ';';
2097 l_proc_body_tbl(1) := l_proc_comment || 'procedure ' || l_func_name ||
2098 l_proc_body_tbl(1) || 'is';
2099
2100 -- add local variables , cursor and procedure body to complete the procedure
2101 l_proc_body_tbl(1) := l_proc_body_tbl(1) || l_cursor || l_adt_cursor ||
2102 l_locals || l_prv_proc_body || indent ||'begin' || indent ;
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',
2112 '(none)','R');
2113 raise;
2114 end generate_download;
2115 -- ----------------------- prepare_calc_range_cursor ---------------------
2116 -- Description:
2117 -- Preapre the cursor for calculate ranges procedure
2118 -- ------------------------------------------------------------------------
2119 procedure prepare_calc_range_cursor
2120 (
2121 p_table_info in hr_dm_gen_main.t_table_info,
2122 p_cursor out nocopy varchar2
2123 )
2124 is
2125 l_cursor_comment varchar2(2000);
2126 l_cursor_defination varchar2(2000);
2127 l_cursor_select_cols varchar2(32767);
2128 l_cursor_select_from varchar2(32767);
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' ||
2138 ' value of the primary key.');
2139
2140 -- defination of cursor
2141 l_cursor_defination := ' cursor csr_get_pk_min_max_val is ' || indent;
2142
2143 -- select columns in the cursor. This will return the list of all columns
2144 -- of the table separated by comma's.
2145
2146 l_cursor_select_cols := ' select min( ' || p_table_info.alias || '.' ||
2147 p_table_info.surrogate_pk_column_name || ')' || indent(10) || ',max( '||
2148 p_table_info.alias || '.' || p_table_info.surrogate_pk_column_name || ')';
2149
2150 -- get from clause
2151 get_cursor_from_clause (p_table_info => p_table_info,
2152 p_from_clause => l_cursor_select_from);
2153 -- get where clause
2154 get_gen_cursor_where_clause (p_table_info => p_table_info,
2155 p_where_clause => l_cursor_select_where,
2156 p_cursor_type => 'CALCULATE_RANGES');
2157
2158 -- finally put the components of where clause together
2159 p_cursor := l_cursor_comment || indent ||
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',
2169 '(none)','R');
2170 raise;
2171 end prepare_calc_range_cursor;
2172
2173
2174 -- ----------------------- generate_calculate_ranges -----------------------
2175 -- Description:
2176 -- Generates the calculate_ranges procedure of the TDS
2177 -- ------------------------------------------------------------------------
2178 procedure generate_calculate_ranges
2179 (
2180 p_table_info in hr_dm_gen_main.t_table_info,
2181 p_header in out nocopy varchar2
2182 )
2183 is
2184 l_interface varchar2(32767);
2185 l_locals varchar2(32767) := null;
2186 l_cursor varchar2(32767) := null;
2187 l_comment varchar2(4000);
2188 l_proc_comment varchar2(4000);
2189 l_cursor_name varchar2(30) := 'csr_get_pk_min_max_val';
2190
2191 -- block body of the procedure i.e between begin and end.
2192 l_proc_body varchar2(32767) := null;
2193 l_func_body varchar2(32767) := null;
2194 l_debug_message_text varchar2(32767) := null;
2195 l_package_name varchar2(30) := 'hrdmd_' || p_table_info.short_name;
2196
2197 -- indentation for the statements.it specifies number of blank spaces
2198 -- after which the staement should start.
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 ||
2208 '(p_business_group_id in number,' || indent ||
2209 ' p_last_migration_date in date,' || indent ||
2210 ' p_phase_item_id in number,' || indent ||
2211 ' p_no_of_threads in number)' || indent;
2212
2213 -- if the table has surrogate primary key then open up a cursor
2214 -- to get the minimum and maximum key value.
2215
2216 if p_table_info.surrogate_primary_key = 'Y' then
2217
2218 -- local variables of the procedure
2219
2220 l_locals := indent || '--' || indent ||
2221 '-- Declare cursors and local variables' || indent ||
2222 '--' || indent ||
2223 ' l_proc varchar2(72) := g_package ' ||
2224 '|| ''calculate_ranges'';' || indent ||
2225 ' l_max_key_value number := 0;' || indent ||
2226 ' l_min_key_value number := 0;' || indent ||
2227 ' l_starting_process_sequence number := 0;' || indent ||
2228 ' l_ending_process_sequence number := 0;' || indent ||
2229 ' l_range_value number := 0;' || indent ||
2230 ' l_row_fetched boolean := FALSE;' || indent;
2231 prepare_calc_range_cursor(p_table_info => p_table_info,
2232 p_cursor => l_cursor);
2233
2234 -- add the body of the download procedure
2235
2236 l_indent := 2;
2237
2238 l_proc_body := l_proc_body || indent|| 'begin';
2239
2240 add_debug_messages (p_table_info => p_table_info,
2241 p_procedure_name => 'calculate_ranges',
2242 p_message_location => 'START',
2243 p_proc_body => l_debug_message_text);
2244
2245 l_proc_body := l_proc_body || l_debug_message_text || indent;
2246
2247 -- open the cursor , fetch the data and close the cursor.
2248
2249 l_proc_body :=
2250 l_proc_body || indent(l_indent) ||
2251 'open ' || l_cursor_name || ';' || indent(l_indent) ||'fetch ' ||
2252 l_cursor_name ||' into l_min_key_value ,l_max_key_value;' ||
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
2262 l_proc_body := l_proc_body || format_comment('find the first range',2);
2263
2264 l_proc_body := l_proc_body || indent(l_indent) ||
2265 'l_range_value := TRUNC ((l_max_key_value - l_min_key_value) / ' ||
2266 'p_no_of_threads);' || indent(l_indent) || 'l_starting_process_sequence :='
2267 ||' l_min_key_value;' || indent(l_indent) ||
2268 'l_ending_process_sequence := l_min_key_value + l_range_value;';
2269
2270 l_proc_body := l_proc_body || format_comment(
2271 'insert the range records equal to the number of threads available for' ||
2272 'processing',2);
2273
2274
2275 -- build up insert statement to insert data into migration range table.
2276 l_proc_body := l_proc_body || indent(l_indent) ||
2277 'for i in 1..p_no_of_threads loop '|| indent(l_indent + 2) ||
2278 'insert into hr_dm_migration_ranges ( range_id ' ;
2279 l_indent := 40;
2280
2281 l_proc_body := l_proc_body ||
2282 indent(l_indent) || ',phase_item_id' ||
2283 indent(l_indent) || ',status' || indent(l_indent) ||
2284 ',starting_process_sequence' || indent(l_indent) ||
2285 ',ending_process_sequence)' ||
2286 indent(l_indent - 7) || 'values ( ' ||
2287 'hr_dm_migration_ranges_s.nextval' ||
2288 indent(l_indent) || ',p_phase_item_id' ||
2289 indent(l_indent) || ',''NS''' || indent(l_indent) ||
2290 ',l_starting_process_sequence' || indent(l_indent) ||
2291 ',l_ending_process_sequence);' || indent;
2292
2293 l_indent := 4;
2294
2295 l_proc_body := l_proc_body || indent(l_indent) ||
2296 'l_starting_process_sequence := l_ending_process_sequence + 1;' ||
2297 format_comment('if it is a last thread then assign the maximum key value to end'
2298 || ' sequence.', l_indent) || indent(l_indent) ||
2299 'if i = p_no_of_threads then' || indent(l_indent+2) ||
2300 'l_ending_process_sequence := l_max_key_value;' || indent(l_indent) ||
2301 'else' || indent(l_indent + 2) ||
2302 'l_ending_process_sequence := l_starting_process_sequence + l_range_value;'
2303 || indent(l_indent) || 'end if;' || indent(l_indent -2) || 'end loop;' ||
2304 indent(l_indent -2) || 'commit;' ;
2305
2306
2307 else
2308 -- if the table does not have a surrogate primary key then insert a
2309 -- dummy row into migration_range table.
2310
2311 -- local variables of the procedure
2312
2313 l_locals := indent || ' --' || indent ||
2314 ' -- Declare cursors and local variables' || indent ||
2315 ' --' || indent ||
2316 ' l_proc varchar2(72) := g_package ' ||
2317 '|| ''calculate_ranges'';' ;
2318
2319 l_indent := 2;
2320
2321 l_proc_body := l_proc_body || indent|| 'begin';
2322
2323 add_debug_messages (p_table_info => p_table_info,
2324 p_procedure_name => 'calculate_ranges',
2325 p_message_location => 'START',
2326 p_proc_body => l_debug_message_text);
2327
2328 l_proc_body := l_proc_body || l_debug_message_text || indent;
2329
2330 -- build up insert statement to insert data into migration range table.
2331
2332 l_indent := 2;
2333 l_proc_body := l_proc_body || indent(l_indent) ||
2334 'insert into hr_dm_migration_ranges ( range_id ' ;
2335 l_indent := 38;
2336
2337 l_proc_body := l_proc_body ||
2338 indent(l_indent) || ',phase_item_id' ||
2339 indent(l_indent) || ',status' || indent(l_indent) ||
2340 ',starting_process_sequence' || indent(l_indent) ||
2341 ',ending_process_sequence)' ||
2342 indent(l_indent - 9) || 'values (' ||
2343 'hr_dm_migration_ranges_s.nextval' ||
2344 indent(l_indent) || ',p_phase_item_id' ||
2345 indent(l_indent) || ',''NS''' || indent(l_indent) ||
2346 ',-99' || indent(l_indent) ||
2347 ',-99);';
2348 end if;
2349
2350 -- add debug messages
2351 add_debug_messages (p_table_info => p_table_info,
2352 p_procedure_name => 'calculate_ranges',
2353 p_message_location => 'END',
2354 p_proc_body => l_debug_message_text);
2355 l_proc_body := l_proc_body || l_debug_message_text || indent;
2356
2357 l_proc_comment := format_comment('procedure to calculate ranges for '
2358 || upper(p_table_info.table_name) || ' data') ||
2359 indent;
2360
2361 -- add the procedure defination to the package header and body
2362 p_header := p_header || l_proc_comment ||'procedure calculate_ranges' ||
2363 l_interface || ';';
2364
2365 -- add the procedure comment defination,local variables , cursor and
2366 -- procedure body
2367
2368 l_proc_body_tbl(1) := l_proc_comment ||'procedure calculate_ranges' ||
2369 l_interface || ' is'|| l_locals || l_cursor || l_proc_body;
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',
2379 '(none)','R');
2380 raise;
2381 end generate_calculate_ranges;
2382
2383 -- ----------------------- prepare_del_datapump_cursor ---------------------
2384 -- Description:
2385 -- Preapre the cursor for delete data pump procedure
2386 -- ------------------------------------------------------------------------
2387 procedure prepare_del_datapump_cursor
2388 (
2389 p_table_info in hr_dm_gen_main.t_table_info,
2390 p_cursor out nocopy varchar2
2391 )
2392 is
2393 l_cursor_comment varchar2(2000);
2394 l_cursor_defination varchar2(2000);
2395 l_cursor_select_cols varchar2(32767);
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
2405 || ' data from data pump batch table for a given batch.');
2406 -- defination of cursor
2407 l_cursor_defination := ' cursor csr_' ||p_table_info.alias || ' is ';
2408
2409 -- select columns in the cursor. This will return the list of all columns
2410 -- of the table separated by comma's.
2411
2412 l_cursor_select_cols := ' select rowid row_id ' ;
2413
2414 -- get from clause. dtapump creates a view of the table to be uploaded.
2415 -- view is based on batch_lines table and TUPS load program parameters.
2416
2417 l_cursor_select_from := ' from hrdpv_u'|| p_table_info.short_name;
2418
2419 -- get where clause
2420 -- if the table has a surrogate primary key then a range of the surrogate
2421 -- primary key will be deleted, otherwise, full table will be deleted.
2422
2423 if p_table_info.surrogate_primary_key = 'Y' then
2424 l_cursor_select_where := ' where batch_id = p_batch_id ' || indent ||
2425 ' and p_start_id <= (select to_number(' || g_surrogate_pk_col_param ||
2426 ') from dual)' || indent ||
2427 ' and p_end_id >= (select to_number(' || g_surrogate_pk_col_param ||
2428 ') from dual);';
2429 else
2430 l_cursor_select_where := ' where batch_id = p_batch_id;';
2431 end if;
2432
2433 -- finally put the components of where clause together
2434 p_cursor := l_cursor_comment || indent ||
2435 l_cursor_defination || indent ||
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',
2445 '(none)','R');
2446 raise;
2447 end prepare_del_datapump_cursor;
2448
2449
2450 -- ----------------------- generate_delete_datapump -----------------------
2451 -- Description:
2452 -- Generates the delete_datapump procedure of the TDS
2453 -- ------------------------------------------------------------------------
2454 procedure generate_delete_datapump
2455 (
2456 p_table_info in hr_dm_gen_main.t_table_info,
2457 p_header in out nocopy varchar2
2458 )
2459 is
2460 l_interface varchar2(32767);
2461 l_locals varchar2(32767) := null;
2462 l_cursor varchar2(32767) := null;
2463 l_comment varchar2(4000);
2464 l_proc_comment varchar2(4000);
2465 l_cursor_name varchar2(30) := 'csr_' || p_table_info.alias;
2466 l_cursor_rec varchar2(30) := 'csr_' || p_table_info.alias || '_rec';
2467
2468 -- block body of the procedure i.e between begin and end.
2469 l_proc_body varchar2(32767) := null;
2470 l_func_body varchar2(32767) := null;
2471 l_debug_message_text varchar2(32767) := null;
2472
2473 -- indentation for the statements.it specifies number of blank spaces
2474 -- after which the staement should start.
2475
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 ||
2485 '(p_start_id in number,' || indent ||
2486 ' p_end_id in number,' || indent ||
2487 ' p_batch_id in number,' || indent ||
2488 ' p_chunk_size in number)' || indent;
2489
2490 -- local variables of the procedure
2491
2492 l_locals := indent || '--' || indent ||
2493 '-- Declare cursors and local variables' || indent ||
2494 '--' || indent ||
2495 ' l_proc varchar2(72) := g_package ' ||
2496 '|| ''delete_datapump'';' || indent ||
2497 ' l_rec_deleted_cnt number := 0;' || indent;
2498
2499 -- cursor to get the data from the data pump table
2500 prepare_del_datapump_cursor (p_table_info,
2501 l_cursor);
2502 -- add the body of the download procedure
2503
2504 l_indent := 2;
2505 l_proc_body := l_proc_body || indent|| 'begin';
2506 l_proc_body := l_proc_body || indent(l_indent) ||
2507 'l_rec_deleted_cnt := 0;';
2508
2509 add_debug_messages (p_table_info => p_table_info,
2510 p_procedure_name => 'delete_datapump',
2511 p_message_location => 'START',
2512 p_proc_body => l_debug_message_text);
2513
2514 l_proc_body := l_proc_body || l_debug_message_text || indent;
2515
2516 l_proc_body :=
2517 l_proc_body || indent(l_indent) ||
2518 'for ' || l_cursor_rec || ' in ' || l_cursor_name || ' loop' ||indent(l_indent);
2519
2520 l_indent := l_indent + 2;
2521
2522 l_comment := format_comment('delete the data from the data pump view for this'
2523 || ' table.',l_indent);
2524
2525 l_proc_body := l_proc_body || l_comment;
2526
2527 l_proc_body := l_proc_body || indent(l_indent) ||
2528 'delete from hrdpv_u' || p_table_info.short_name || indent(l_indent) ||
2529 'where rowid = ' || l_cursor_rec || '.row_id;';
2530
2531 l_proc_body := l_proc_body || indent(l_indent) ||
2532 'l_rec_deleted_cnt := l_rec_deleted_cnt + 1;' || indent(l_indent) ||
2533 format_comment('commit after every chunk_size_value (e.g. 10) records.',
2534 l_indent) || indent(l_indent) || 'if mod (l_rec_deleted_cnt, ' ||
2535 'p_chunk_size) = 0 then ' || indent(l_indent + 2) || 'commit;' ||
2536 indent(l_indent) || 'end if;' || indent(l_indent - 2) || 'end loop;' ||
2537 indent(l_indent - 2) || 'commit;';
2538
2539 add_debug_messages (p_table_info => p_table_info,
2540 p_procedure_name => 'delete_datapump',
2541 p_message_location => 'END',
2542 p_proc_body => l_debug_message_text);
2543
2544 l_proc_body := l_proc_body || l_debug_message_text;
2545
2546 if p_table_info.surrogate_primary_key = 'Y' then
2547 l_proc_comment := format_comment('procedure to delete a range of '
2548 || upper(p_table_info.table_name) || ' data from datapump interface table.')||
2549 indent;
2550 else
2551 l_proc_comment := format_comment('procedure to delete data of '
2552 || upper(p_table_info.table_name) || ' from datapump interface table.')||
2553 indent;
2554 end if;
2555
2556 -- add the procedure comment defination to the package header and body
2557 p_header := p_header || l_proc_comment ||'procedure delete_datapump' ||
2558 l_interface || ';';
2559
2560
2561 -- add the procedure comment defination,local variables , cursor and
2562 -- procedure body
2563
2564 l_proc_body_tbl(1) := l_proc_comment || 'procedure delete_datapump' ||
2565 l_interface || ' is' || l_locals || l_cursor || l_proc_body;
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',
2575 '(none)','R');
2576 raise;
2577 end generate_delete_datapump;
2578
2579 -- ----------------------- prepare_delete_source_cursor; ---------------------
2580 -- Description:
2581 -- Preapre the cursor for delete data from the table for a business group
2582 -- which has been migrated.
2583 -- ------------------------------------------------------------------------
2584 procedure prepare_delete_source_cursor
2585 (
2586 p_table_info in hr_dm_gen_main.t_table_info,
2587 p_cursor out nocopy varchar2
2588 )
2589 is
2590 l_cursor_comment varchar2(2000);
2591 l_cursor_defination varchar2(2000);
2592 l_cursor_select_cols varchar2(32767);
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
2602 || ' data for a given business group.');
2603 -- defination of cursor
2604 l_cursor_defination := ' cursor csr_' ||p_table_info.alias || ' is ';
2605
2606 -- select columns in the cursor. This will return the list of all columns
2607 -- of the table separated by comma's.
2608
2609 -- Key combination tables have foreign key on AOL table
2610 -- 'FND_ID_FLEX_STRUCTURES' i.e hierarchy_type = 'A'. To identify
2611 -- the business group for a row in these tables it is required to
2612 -- check whether the ID value of the row is used by any of the
2613 -- child tables. The 'Where' clause generated by this generator
2614 -- does not use the 'exists' clause which results in the same row
2615 -- being fetch equals to the number of child rows it matches. To
2616 -- avoid this ' distinct' clause is added for only these tables.
2617 -- If the 'distinct' clause causes the performance problem then
2618 -- remove it and seed the derive_sql columns in HR_DM_TABLES for
2619 -- these tables.
2620
2621 if p_table_info.use_distinct = 'Y' then
2622 l_cursor_select_cols := ' select distinct ' || p_table_info.alias ||
2623 '.rowid row_id ';
2624 else
2625 l_cursor_select_cols := ' select ' || p_table_info.alias ||
2626 '.rowid row_id ';
2627 end if;
2628
2629 -- get from clause
2630 get_cursor_from_clause (p_table_info => p_table_info,
2631 p_from_clause => l_cursor_select_from);
2632 -- get where clause
2633 get_gen_cursor_where_clause ( p_table_info => p_table_info ,
2634 p_where_clause => l_cursor_select_where,
2635 p_cursor_type => 'DELETE_SOURCE');
2636
2637 -- finally put the components of where clause together
2638 p_cursor := l_cursor_comment || indent ||
2639 l_cursor_defination || indent ||
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',
2649 '(none)','R');
2650 raise;
2651 end prepare_delete_source_cursor;
2652
2653 -- ----------------------- generate_delete_source -----------------------
2654 -- Description:
2655 -- Generates the delete_source procedure of the TDS.
2656 -- ------------------------------------------------------------------------
2657 procedure generate_delete_source
2658 (
2659 p_table_info in hr_dm_gen_main.t_table_info,
2660 p_header in out nocopy varchar2
2661 )
2662 is
2663 l_interface varchar2(32767);
2664 l_locals varchar2(32767) := null;
2665 l_cursor varchar2(32767) := null;
2666 l_comment varchar2(4000);
2667 l_proc_comment varchar2(4000);
2668 l_cursor_name varchar2(30) := 'csr_' || p_table_info.alias;
2669 l_cursor_rec varchar2(30) := 'csr_' || p_table_info.alias || '_rec';
2670
2671 -- block body of the procedure i.e between begin and end.
2672 l_proc_body varchar2(32767) := null;
2673 l_func_body varchar2(32767) := null;
2674 l_debug_message_text varchar2(32767) := null;
2675
2676 -- indentation for the statements.it specifies number of blank spaces
2677 -- after which the staement should start.
2678
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 ||
2688 '(p_business_group_id in number,' || indent ||
2689 ' p_start_id in number,' || indent ||
2690 ' p_end_id in number,' || indent ||
2691 ' p_chunk_size in number)' || indent;
2692
2693 -- message (' l_interface = ' || l_interface);
2694 -- local variables of the procedure
2695
2696 l_locals := indent || '--' || indent ||
2697 '-- Declare cursors and local variables' || indent ||
2698 '--' || indent ||
2699 ' l_proc varchar2(72) := g_package ' ||
2700 '|| ''delete_source'';' || indent ||
2701 ' l_rec_deleted_cnt number := 0;' || indent;
2702
2703 -- cursor to get the data from the data pump table
2704 prepare_delete_source_cursor (p_table_info,
2705 l_cursor);
2706
2707 -- add the body of the download procedure
2708
2709 l_indent := 2;
2710 l_proc_body := l_proc_body || indent|| 'begin';
2711 l_proc_body := l_proc_body || indent(l_indent) ||
2712 'l_rec_deleted_cnt := 0;';
2713
2714
2715
2716 add_debug_messages (p_table_info => p_table_info,
2717 p_procedure_name => 'delete_source',
2718 p_message_location => 'START',
2719 p_proc_body => l_debug_message_text);
2720
2721 l_proc_body := l_proc_body || l_debug_message_text || indent;
2722
2723 l_proc_body :=
2724 l_proc_body || indent(l_indent) ||
2725 'for ' || l_cursor_rec || ' in ' || l_cursor_name || ' loop' ||indent(l_indent);
2726
2727 l_indent := l_indent + 2;
2728
2729 l_comment := format_comment('delete the row read from the table.',l_indent);
2730
2731 l_proc_body := l_proc_body || l_comment;
2732
2733 l_proc_body := l_proc_body || indent(l_indent) ||
2734 'delete from ' || p_table_info.table_name || indent(l_indent) ||
2735 'where rowid = ' || l_cursor_rec || '.row_id;';
2736
2737
2738 l_proc_body := l_proc_body || indent(l_indent) ||
2739 'l_rec_deleted_cnt := l_rec_deleted_cnt + 1;' || indent(l_indent) ||
2740 format_comment('commit after every chunk_size_value (e.g. 10) records.',
2741 l_indent) || indent(l_indent) || 'if mod (l_rec_deleted_cnt, ' ||
2742 'p_chunk_size) = 0 then ' || indent(l_indent + 2) || 'commit;' ||
2743 indent(l_indent) || 'end if;' || indent(l_indent - 2) || 'end loop;' ||
2744 indent(l_indent - 2) || 'commit;';
2745
2746 add_debug_messages (p_table_info => p_table_info,
2747 p_procedure_name => 'delete_source',
2748 p_message_location => 'END',
2749 p_proc_body => l_debug_message_text);
2750
2751 l_proc_body := l_proc_body || l_debug_message_text;
2752
2753 l_proc_comment := format_comment('procedure to delete data of '
2754 || upper(p_table_info.table_name) || ' for a given business group.')||
2755 indent;
2756
2757 -- add the procedure comment defination to the package header and body
2758 p_header := p_header || l_proc_comment ||'procedure delete_source' ||
2759 l_interface || ';';
2760
2761 -- add the procedure comment defination,local variables , cursor and
2762 -- procedure body
2763
2764 l_proc_body_tbl(1) := l_proc_comment || 'procedure delete_source' ||
2765 l_interface || ' is' || l_locals || l_cursor || l_proc_body;
2766
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
2776 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tds.generate_delete_source',
2777 '(none)','R');
2778 raise;
2779 end generate_delete_source;
2780
2781 -- ------------------------- create_tds_pacakge ------------------------
2782 -- Description: Create the TDS package and relevant procedures for the table.
2783 --
2784 -- Input Parameters :
2785 -- p_table_info - Information about table for which TDS to be generated. Info
2786 -- like Datetrack, Global Data, Surrogate Primary key etc about
2787 -- the table is passed as a record type.
2788 -- p_columns_tbl - All the columns of the table stored as a list.
2789 -- p_parameters_tbl - All the columns of the table stored with data type are
2790 -- stored as a list. e.g p_business_group_id number
2791 -- This is used to create the procedure parameter list for
2792 -- TDS procedure.
2793 -- p_aol_columns_tbl - All the columns of the table which have foreign key to
2794 -- AOL table are stored as a list.
2795 -- p_aol_parameters_tbl - All the columns of the table which have foreign key to
2796 -- AOL table are stored with data type as a list. This is
2797 -- used as a parameter list for the procedure generated to
2798 -- get the AOL developer key for the given ID value
2799 -- e.g p_user_id number
2800 -- p_fk_to_aol_columns_tbl - It stores the list of all the columns which have
2801 -- foreign on AOL table and corresponding name of the AOL
2802 -- table.
2803 -- ------------------------------------------------------------------------
2804 procedure create_tds_pacakge
2805 (
2806 p_table_info in hr_dm_gen_main.t_table_info,
2807 p_columns_tbl in hr_dm_library.t_varchar2_tbl,
2808 p_parameters_tbl in hr_dm_library.t_varchar2_tbl,
2809 p_aol_columns_tbl in hr_dm_library.t_varchar2_tbl,
2810 p_aol_parameters_tbl in hr_dm_library.t_varchar2_tbl,
2811 p_fk_to_aol_columns_tbl in hr_dm_gen_main.t_fk_to_aol_columns_tbl
2812 )
2813 is
2814 l_header varchar2(32767);
2815 l_body varchar2(32767);
2816 l_header_comment varchar2(2048);
2817 l_package_name varchar2(30) := 'hrdmd_' || p_table_info.short_name;
2818 l_generator_version hr_dm_tables.generator_version%type;
2819 l_package_version varchar2(200);
2820 l_index number := 1;
2821 l_call_to_aol_proc varchar2(32767);
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;
2831 g_columns_tbl := p_columns_tbl;
2832 g_parameters_tbl := p_parameters_tbl;
2833 g_surrogate_pk_col_param := 'p_' ||
2834 rpad(p_table_info.surrogate_pk_column_name,28);
2835
2836 -- inialize the global package body pl/sql table by deleting all elements.
2837 init_package_body;
2838
2839 -- Get the version of the generator to be appended to the TDS package
2840 -- generated for a table. This will help in finding out which version
2841 -- of Generator is used to generate the TDS package.
2842
2843 hr_dm_library.get_generator_version(p_generator_version => l_generator_version,
2844 p_format_output => 'Y');
2845
2846 -- Get the package version of this TDS package body.
2847 hr_dm_library.get_package_version ( p_package_name => 'HR_DM_GEN_TDS',
2848 p_package_version => l_package_version,
2849 p_version_type => 'FULL');
2850
2851
2852 -- if there is a column hirearchy then store the hierarchy columns list and
2853 -- parameter assignment in pl/sql variable.
2854
2855 if p_table_info.column_hierarchy = 'Y' then
2856 -- get the columns and parameter list. store in pl/sql table.
2857
2858 hr_dm_library.populate_hierarchy_cols_list
2859 (p_table_info => p_table_info,
2860 p_hier_columns_tbl => g_hier_columns_tbl,
2861 p_hier_parameter_tbl => g_hier_parameters_tbl,
2862 p_called_from => 'TDS' );
2863 end if;
2864
2865 -- Start the package header and body.
2866 begin
2867 --
2868 -- Set up initial parts of the package header and body.
2869 --
2870 l_header_comment := l_package_version || indent || '/*' || indent ||
2871 ' * Generated by hr_dm_gen_tds at: ' ||
2872 to_char( sysdate, 'YYYY/MM/DD HH24:MM:SS' ) || indent ||
2873 ' * Generated Data Migrator TDS for : ' || p_table_info.table_name || '.' ||
2874 indent ||
2875 ' * Generator Version Used to generate this TDS is : ' || indent ||
2876 l_generator_version || indent ||
2877 ' */' || indent || '--' || indent;
2878
2879 l_header :=
2880 'create or replace package ' || l_package_name || ' as' || indent ||
2881 l_header_comment ||
2882 'g_generator_version constant varchar2(128) default ' ||
2883 '''$Revision: 120.0 $'';' || indent || '--' || indent;
2884
2885 l_proc_body_tbl(1) :=
2886 'create or replace package body ' || l_package_name || ' as' || indent ||
2887 l_header_comment;
2888
2889 -- private package variable
2890 l_proc_body_tbl(1) := l_proc_body_tbl(1) || indent || '--' ||
2891 indent || '-- Package Variables' || indent ||
2892 '--' || indent ||
2893 'g_package varchar2(33) := ''' || l_package_name || ''';' ||
2894 indent;
2895
2896 -- add the body of this procedure to the package.
2897 add_to_package_body( l_proc_body_tbl );
2898 --
2899 -- Generate the procedures and functions.
2900 --
2901
2902 -- if it is a datetrack generate procedure to get link value.
2903 if p_table_info.datetrack = 'Y' then
2904
2905 -- create a procedure to get the link value to be used by data pump for
2906 -- uploading.
2907
2908 generate_get_link_value(p_table_info);
2909 end if;
2910
2911 -- if the table has a columns which have a foreign key to AOL table then
2912 -- generate the procedures so as to create the procedures to get the
2913 -- corresponding developer's key for those columns.
2914
2915 if p_table_info.fk_to_aol_table = 'Y' then
2916 generate_developer_key_func
2917 ( p_fk_to_aol_columns_tbl => p_fk_to_aol_columns_tbl,
2918 p_table_info => p_table_info,
2919 p_call_to_proc_body => l_call_to_aol_proc,
2920 p_dev_key_local_var_body => l_dev_key_local_var);
2921 end if;
2922
2923 l_body := l_body || indent || '--' || indent;
2924
2925 -- generate download procedure to download data into batch_lines.
2926 -- for column hierarchy.
2927 --
2928 -- for download of hierarchy columns, we are assuming that there
2929 -- will be no H hierarchy columns where the same column will also
2930 -- have an A hierarchy, ie there will be no A hierarchy calls and
2931 -- hence p_call_to_proc_body => '', instead of
2932 -- p_call_to_proc_body => l_call_to_aol_proc,
2933
2934 if p_table_info.column_hierarchy = 'Y' then
2935 l_body := l_body || indent || '--' || indent;
2936 generate_download(p_table_info => p_table_info,
2937 p_header => l_header,
2938 p_hier_column => 'Y',
2939 p_call_to_proc_body => '',
2940 p_dev_key_local_var_body => l_dev_key_local_var,
2941 p_fk_to_aol_columns_tbl => p_fk_to_aol_columns_tbl);
2942 end if;
2943
2944 -- down load procedure to download all columns.
2945 generate_download(p_table_info => p_table_info,
2946 p_header => l_header,
2947 p_hier_column => 'N',
2948 p_call_to_proc_body => l_call_to_aol_proc,
2949 p_dev_key_local_var_body => l_dev_key_local_var,
2950 p_fk_to_aol_columns_tbl => p_fk_to_aol_columns_tbl);
2951
2952
2953 l_header := l_header || indent || '--' || indent;
2954 l_body := l_body || indent || '--' || indent;
2955
2956 -- generate calculate_ranges procedure to download data into batch_lines.
2957
2958 l_body := l_body || indent || '--' || indent;
2959 generate_calculate_ranges(p_table_info,
2960 l_header);
2961
2962 -- generate delete_datapump procedure to delete data from batch_lines.
2963 -- table for the given table.
2964
2965 l_body := l_body || indent || '--' || indent;
2966 generate_delete_datapump(p_table_info,
2967 l_header );
2968
2969 -- generate delete_source procedure to delete data from batch_lines.
2970 -- table for the given table.
2971
2972 l_body := l_body || indent || '--' || indent;
2973 generate_delete_source(p_table_info,
2974 l_header);
2975
2976 l_header := l_header || indent || '--' || indent;
2977 l_body := l_body || indent || '--' || indent;
2978
2979 -- Terminate the package body and header.
2980 --
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;
2990 end;
2991
2992 g_package_index := g_package_index+1;
2993 g_package_body(g_package_index ) := indent || 'end ' ||
2994 l_package_name || ';';
2995
2996 --
2997 -- Compile the header and body.
2998 --
2999
3000 hr_dm_library.run_sql( l_header );
3001 hr_dm_library.run_sql( g_package_body,
3002 g_package_index);
3003
3004
3005 -- check the status of the package
3006
3007
3008 -- check the status of the package
3009 begin
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
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
3028 end hr_dm_gen_tds;
[Home] [Help]