1 package body hr_dm_gen_tups as
2 /* $Header: perdmgnu.pkb 120.1 2005/08/14 23:22:37 mmudigon noship $ */
3
4
5 type t_varchar2_32k_tbl is table of varchar2(32767) index by binary_integer;
6
7 --
8 -- this record stores the information to build and call the chk_row_exists
9 -- procedure. It will be used only if criteia to check whether row exists
10 -- is other than the primary key. This will be populated only if the
11 -- table need to use non primary key columns.
12 --
13 type t_chk_row_exists is record
14 (
15 where_clause varchar2(32767),
16 call_to_proc varchar2(32767),
17 proc_parameters varchar2(32767)
18 );
19
20 --
21 -- this record stores the information to build and call the delete_dml
22 -- procedure. It will be used only if criteia to check whether row exists
23 -- is other than the primary key. This will be populated only if the
24 -- table need to use non primary key columns.
25 --
26 type t_delete_dml is record
27 (
28 where_clause varchar2(32767),
29 call_to_proc varchar2(32767),
30 proc_parameters varchar2(32767)
31 );
32
33 g_table_info hr_dm_gen_main.t_table_info;
34 g_columns_tbl hr_dm_library.t_varchar2_tbl;
35 g_parameters_tbl hr_dm_library.t_varchar2_tbl;
36 g_hier_columns_tbl hr_dm_library.t_varchar2_tbl;
37 g_hier_parameters_tbl hr_dm_library.t_varchar2_tbl;
38 g_aol_columns_tbl hr_dm_library.t_varchar2_tbl;
39 g_aol_parameters_tbl hr_dm_library.t_varchar2_tbl;
40 g_pk_columns_tbl hr_dm_library.t_varchar2_tbl;
41 g_pk_parameters_tbl hr_dm_library.t_varchar2_tbl;
42 g_fk_to_aol_columns_tbl hr_dm_gen_main.t_fk_to_aol_columns_tbl;
43 g_resolve_pk_columns_tbl hr_dm_gen_main.t_fk_to_aol_columns_tbl;
44 g_surrogate_pk_col_param varchar2(30);
45 g_no_of_pk_columns number;
46 g_chk_row_exists t_chk_row_exists;
47 g_delete_dml t_delete_dml;
48 g_exception_tbl hr_dm_library.t_varchar2_tbl;
49
50 -- to store the package body in to array so as to overcome the limit of 32767
51 -- character the global variable is defined.
52 g_package_body dbms_sql.varchar2s;
53 g_package_index number := 0;
54 /*
55 c_newline constant varchar(1) default '
56 ';
57 */
58
59 --
60 -- Exception for generated text exceeding the maximum allowable buffer size.
61 --
62 plsql_value_error exception;
63 pragma exception_init(plsql_value_error, -6502);
64
65 -- ----------------------- indent -----------------------------------------
66 -- Description:
67 -- returns the 'n' blank spaces on a newline.used to indent the procedure
68 -- statements.
69 -- if newline parameter is 'Y' then start the indentation from new line.
70 -- ------------------------------------------------------------------------
71
72 function indent
73 (
74 p_indent_spaces in number default 0,
75 p_newline in varchar2 default 'Y'
76 ) return varchar2 is
77 l_spaces varchar2(100);
78 begin
79
80 l_spaces := hr_dm_library.indent(p_indent_spaces => p_indent_spaces,
81 p_newline => p_newline);
82 return l_spaces;
83 exception
84 when others then
85 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.indent',
86 '(p_indent_spaces - ' || p_indent_spaces ||
87 ')(p_newline - ' || p_newline || ')',
88 'R');
89 end indent;
90 --------------------- migrate_table_data----------------------------------------
91 -- This function will decide if the table is on the exception list
92 -- Exception list is maintained here for now
93 -------------------------------------------------------------------------------
94 function migrate_table_data (p_table_info in hr_dm_gen_main.t_table_info )
95 return boolean is
96
97 l_migrate boolean := true;
98 begin
99
100 g_exception_tbl.delete;
101 g_exception_tbl(1) := 'HR_DMV_FF_FORMULAS_F';
102
103 for i in 1.. g_exception_tbl.count
104 loop
105 if upper(p_table_info.table_name) = g_exception_tbl(i) then
106 l_migrate := false;
107 exit;
108 end if;
109 end loop;
110
111 return l_migrate;
112
113 end migrate_table_data;
114 --------------------- init_package_body----------------------------------------
115 -- This package will delete all the elements from the package body pl/sql table.
116 -------------------------------------------------------------------------------
117 procedure init_package_body is
118 l_index number := g_package_body.first;
119 begin
120 hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.init_package_body', 5);
121 -- delete all elements from package body pl/sql table.
122 while l_index is not null loop
123 g_package_body.delete(l_index);
124 l_index := g_package_body.next(l_index);
125 end loop;
126
127
128 -- delete all elements from resolve_pk pl/sql table.
129 l_index := g_resolve_pk_columns_tbl.first;
130
131 while l_index is not null loop
132 g_resolve_pk_columns_tbl.delete(l_index);
133 l_index := g_resolve_pk_columns_tbl.next(l_index);
134 end loop;
135
136 --initialize the index
137 g_package_index := 0;
138
139 g_chk_row_exists.where_clause := null;
140 g_chk_row_exists.call_to_proc := null;
141 g_chk_row_exists.proc_parameters := null;
142
143 hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.init_package_body',
144 25);
145 exception
146 when others then
147 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.init_package_body',
148 '(none)','R');
149 raise;
150 end init_package_body;
151
152 -- -----------------------add_to_package_body; ---------------------------------
153 -- Description:
154 -- This procedure will be called by each procedure to be created by TUPS.
155 -- Each procedure will be stored in the array of varchar2(32767).
156 -- Now the task of this procedure is to split the above array elements into
157 -- array elements of size 256. This is required so as to the package body
158 -- of more than 32 K size can be parsed using dbms_sql procedure.
159 --
160 -- ------------------------------------------------------------------------
161
162 procedure add_to_package_body
163 (
164 p_proc_body_tbl t_varchar2_32k_tbl
165 ) is
166
167 l_proc_index number := p_proc_body_tbl.first;
168 l_string_index number; -- variable to read the string characters
169 l_loop_cnt number;
170 begin
171
172 hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.add_to_package_body-1', 5);
173 hr_dm_utility.message('PARA','(p_proc_body_tbl - table of varchar2',10);
174
175 while l_proc_index is not null loop
176
177 l_string_index := 1;
178 l_loop_cnt := 1;
179 -- read the string of the procedure body and chop it into the array element
180 -- size of 256 and store it into the global package body. Each looping will
181 -- will read the 256 characters from the procedure body and it will go on
182 -- until no more characters to read.
183 loop
184 if substr(p_proc_body_tbl(l_proc_index),l_string_index,256) is null
185 then
186 exit;
187 end if;
188 g_package_index := g_package_index + 1;
189
190 -- add the procedure body to
191 g_package_body (g_package_index) :=
192 substr(p_proc_body_tbl(l_proc_index),
193 l_string_index ,256);
194 l_string_index := 256*l_loop_cnt + 1;
195 l_loop_cnt := l_loop_cnt + 1;
196 end loop;
197
198 l_proc_index := p_proc_body_tbl.next(l_proc_index);
199 end loop;
200 hr_dm_utility.message('INFO',
201 '(l_loop_cnt - ' || l_loop_cnt ||
202 ')(l_string_index - ' ||l_string_index ||
203 ')( g_package_index - ' || g_package_index || ')'
204 ,15);
205 hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.add_to_package_body -1',
206 25);
207 exception
208 when others then
209 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.add_to_package_body-1',
210 '(l_loop_cnt - ' || l_loop_cnt ||
211 ')(l_string_index - ' ||l_string_index ||
212 ')( g_package_index - ' || g_package_index || ')'
213 ,'R');
214 raise;
215 end add_to_package_body;
216
217 -- ----------------------- format_comment ---------------------------------
218 -- Description:
219 -- formats the comments to be written into the procedure body
220 -- e.g comment string ' This is a example comment text' will be converted t
221 -- --
222 -- -- This is a example comment text.
223 -- --
224 -- ------------------------------------------------------------------------
225
226 function format_comment
227 (
228 p_comment_text in varchar2,
229 p_indent_spaces in number default 0,
230 p_ins_blank_lines in varchar2 default 'Y'
231 ) return varchar2 is
232
233 l_comment_text varchar2(20000);
234 l_comment_length number := length(p_comment_text);
235
236 --
237 -- maximum chracters for single comment text line ensuring the single
238 -- comment line cannot be more than 77 characters long excluding 3
239 -- characters ('-- ') at the begning of comment.
240 --
241
242 l_max_comment_line_len number := 77 - p_indent_spaces;
243 l_comment_line_len number;
244 l_comment_line_txt varchar2(80);
245
246 -- start and end pointer of comment line to be copied from comment text.
247 l_start_ptr number := 1;
248 l_end_ptr number;
249
250 -- used for wrapping
251 l_last_space_ptr number;
252 begin
253
254 if p_ins_blank_lines = 'Y' then
255 l_comment_text := indent(p_indent_spaces) || '--';
256 end if;
257
258 loop
259
260 l_end_ptr := l_start_ptr + l_max_comment_line_len - 1;
261
262 l_comment_line_txt := substr(p_comment_text,l_start_ptr,(l_end_ptr - l_start_ptr + 1));
263
264 l_comment_line_len := length(l_comment_line_txt);
265
266 -- comment line is less than the maximum text that come then it is ok,
267 -- otherwise do word wrapping.If the next character is a space there is
268 -- no need for wrapping
269
270 if l_comment_line_len >= l_max_comment_line_len and
271 substr(p_comment_text,l_end_ptr + 1,1) <> ' '
272 then
273
274 -- this function ensures the wrapping of the word. last word will come
275 -- either full or move to the next line.This gives the position of the
276 -- last space in the comment line text.
277
278 l_last_space_ptr := instr(l_comment_line_txt,' ',-1);
279
280 -- adjust the end pointer as we want to copy the string upto the last
281 -- space only, the remaining word should go into next line.
282
283 l_end_ptr := l_end_ptr - (length(l_comment_line_txt) - l_last_space_ptr);
284 end if;
285
286 -- now the end_ptr gives the length of the comment line that can be copied
287 -- with a space in the end.
288
289 l_comment_text := l_comment_text || indent(p_indent_spaces) || '-- ';
290
291
292 l_comment_text := l_comment_text || substr(p_comment_text,l_start_ptr,
293 (l_end_ptr - l_start_ptr + 1));
294 l_start_ptr := l_end_ptr +1;
295 if l_start_ptr > l_comment_length then
296 exit;
297 end if;
298 end loop;
299
300
301 if p_ins_blank_lines = 'Y' then
302 l_comment_text := l_comment_text || indent(p_indent_spaces) || '--';
303 end if;
304 return l_comment_text;
305 exception
306 when others then
307 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.format_comment',
308 '(p_ins_blank_lines - ' || p_ins_blank_lines ||
309 ')(p_indent_spaces - ' || p_indent_spaces ||
310 ')(p_comment_text - ' || p_comment_text || ')'
311 ,'R');
312 end format_comment;
313 ------- prepare_code_for_resolving_pk(Overloaded)-----------------------
314 -- Description:
315 -- This function will
316 -- - Creates a call to the function to get the get the id value
317 -- in the parent table of destination corresponding to the
318 -- given parent id value of source database.
319 -- Input Parameters
320 -- g_pk_columns_tbl - Contains the information about all the columns
321 -- whose primary key has to be resolved.
322 -- p_table_info - Contains the info about the table to be
323 -- downloaded
324 -- Out Parameters
325 -- p_call_to_proc_body - returns the string for a call to the function
326 -- p_dev_key_local_var_body - returns the string defining local variable
327 -- ------------------------------------------------------------------------
328 procedure prepare_code_for_resolving_pk
329 (
330 p_pk_columns_tbl in hr_dm_library.t_varchar2_tbl,
331 p_table_info in hr_dm_gen_main.t_table_info,
332 p_call_to_proc_body in out nocopy varchar2,
333 p_local_var_body in out nocopy varchar2
334 )
335 is
336 l_interface varchar2(32767);
337 l_locals varchar2(32767) := null;
338 l_cursor varchar2(32767) := null;
339 l_where_clause varchar2(32767) := null;
340 l_proc_comment varchar2(4000);
341 l_cursor_name varchar2(30) := 'csr_get_id_val';
342 l_proc_name varchar2(32767);
343 l_proc_body_tbl t_varchar2_32k_tbl;
344
345 -- variables to store the information about p_pk_columns_tbl elements.
346 -- this is to reduce the variable name and add clarity.
347 l_column_name hr_dm_hierarchies.column_name%type;
348
349
350 -- block body of the procedure i.e between begin and end.
351 l_proc_body varchar2(32767) := null;
352
353 -- indentation for the statements.it specifies number of blank spaces
354 -- after which the statment should start.
355
356 l_indent number;
357 l_index number := g_pk_columns_tbl.first;
358 l_index2 number;
359 l_duplicate number;
360
361 begin
362 hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.prepare_code_for_resolving_pk ', 5);
363
364
365 while l_index is not null loop
366 l_column_name :=
367 p_pk_columns_tbl(l_index);
368
369 -- construct a call to the procedure created above.
370 l_indent := 4;
371 p_call_to_proc_body := p_call_to_proc_body || indent ||
372 format_comment('Get the id value in the destination database for ' || l_column_name ||
373 ' from table ' || p_table_info.table_name || ' corresponding to ' ||
374 ' the value in source database.',
375 l_indent) || indent(l_indent);
376
377 p_call_to_proc_body := p_call_to_proc_body ||
378 'hr_dm_library.get_resolved_pk (' ||
379 'p_table_name => ''' || upper(p_table_info.table_name) || '''' ||
380 indent(l_indent+32) ||
381 ', p_source_id => ' ||rpad('p_' || l_column_name,30)
382 || indent(l_indent+32) ||
383 ', p_destination_id => ' || rpad('l_' || l_column_name,30)
384 || ');' || indent(l_indent);
385
386 -- construct the definition of local variable
387 l_indent := 4;
388
389 -- ensure we only add each local variable once.
390 l_duplicate := 0;
391 l_index2 := g_pk_columns_tbl.first;
392 while l_index2 <= l_index loop
393 if (l_column_name =
394 g_pk_columns_tbl(l_index2)) then
395 l_duplicate := l_duplicate + 1;
396 end if;
397 l_index2 := g_pk_columns_tbl.next(l_index2);
398 end loop;
399 if l_duplicate = 1 then
400 p_local_var_body := p_local_var_body || indent(l_indent)
401 || 'l_' || rpad(l_column_name,28) || ' ' ||
402 p_table_info.upload_table_name || '.' || l_column_name ||'%type;';
403 end if;
404 l_index := g_pk_columns_tbl.next(l_index);
405 end loop;
406 hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.prepare_code_for_resolving_pk',
407 25);
408 exception
409 when others then
410 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.prepare_code_for_resolving_pk',
411 '(none)','R');
412 raise;
413 end prepare_code_for_resolving_pk;
414 ------- prepare_code_for_resolving_pk(Overloaded)-----------------------
415 -- Description:
416 -- This function will
417 -- - Creates a call to the function to get the get the id value
418 -- in the parent table of destination corresponding to the
419 -- given parent id value of source database.
420 -- Input Parameters
421 -- g_resolve_pk_columns_tbl - Contains the information about all the columns
422 -- whose primary key has to be resolved.
423 -- p_table_info - Contains the info about the table to be
424 -- downloaded
425 -- Out Parameters
426 -- p_call_to_proc_body - returns the string for a call to the function
427 -- p_dev_key_local_var_body - returns the string defining local variable
428 -- ------------------------------------------------------------------------
429 procedure prepare_code_for_resolving_pk
430 (
431 p_resolve_pk_columns_tbl in hr_dm_gen_main.t_fk_to_aol_columns_tbl,
432 p_table_info in hr_dm_gen_main.t_table_info,
433 p_call_to_proc_body in out nocopy varchar2,
434 p_local_var_body in out nocopy varchar2
435 )
436 is
437 l_interface varchar2(32767);
438 l_locals varchar2(32767) := null;
439 l_cursor varchar2(32767) := null;
440 l_where_clause varchar2(32767) := null;
441 l_proc_comment varchar2(4000);
442 l_cursor_name varchar2(30) := 'csr_get_id_val';
443 l_proc_name varchar2(32767);
444 l_proc_body_tbl t_varchar2_32k_tbl;
445
446 -- variables to store the information about p_fk_to_aol_columns_tbl elements.
447 -- this is to reduce the variable name and add clarity.
448 l_column_name hr_dm_hierarchies.column_name%type;
449 l_parent_table_id hr_dm_hierarchies.parent_table_id%type;
450 l_parent_table_name hr_dm_tables.table_name%type;
451 l_parent_table_alias hr_dm_tables.table_alias%type;
452 l_parent_column_name hr_dm_hierarchies.parent_column_name%type;
453 l_parent_id_column_name hr_dm_hierarchies.parent_id_column_name%type;
454
455
456 -- block body of the procedure i.e between begin and end.
457 l_proc_body varchar2(32767) := null;
458
459 -- indentation for the statements.it specifies number of blank spaces
460 -- after which the statment should start.
461
462 l_indent number;
463 l_index number := g_resolve_pk_columns_tbl.first;
464 l_index2 number;
465 l_duplicate number;
466
467 begin
468 hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.prepare_code_for_resolving_pk ', 5);
469
470
471 while l_index is not null loop
472 l_column_name :=
473 g_resolve_pk_columns_tbl(l_index).column_name;
474 l_parent_table_id :=
475 g_resolve_pk_columns_tbl(l_index).parent_table_id;
476 l_parent_table_name :=
477 g_resolve_pk_columns_tbl(l_index).parent_table_name;
478 l_parent_table_alias :=
479 g_resolve_pk_columns_tbl(l_index).parent_table_alias;
480 l_parent_column_name :=
481 g_resolve_pk_columns_tbl(l_index).parent_column_name;
482 l_parent_id_column_name :=
483 g_resolve_pk_columns_tbl(l_index).parent_id_column_name;
484
485 -- construct a call to the procedure created above.
486 l_indent := 2;
487 p_call_to_proc_body := p_call_to_proc_body || indent ||
488 format_comment('Get the id value in the destination database for ' || l_column_name ||
489 ' from parent table ' || l_parent_table_name || ' corresponding to ' ||
490 ' the value in source database.',
491 l_indent) || indent(l_indent);
492
493 p_call_to_proc_body := p_call_to_proc_body ||
494 'hr_dm_library.get_resolved_pk (' ||
495 'p_table_name => ''' || upper(l_parent_table_name) || '''' ||
496 indent(l_indent+32) ||
497 ', p_source_id => ' ||rpad('p_' || l_column_name,30)
498 || indent(l_indent+32) ||
499 ', p_destination_id => ' || rpad('l_' || l_column_name,30)
500 || ');' || indent(l_indent);
501
502 -- construct the definition of local variable
503 l_indent := 2;
504
505 -- ensure we only add each local variable once.
506 l_duplicate := 0;
507 l_index2 := g_resolve_pk_columns_tbl.first;
508 while l_index2 <= l_index loop
509 if (l_column_name =
510 g_resolve_pk_columns_tbl(l_index2).column_name) then
511 l_duplicate := l_duplicate + 1;
512 end if;
513 l_index2 := g_resolve_pk_columns_tbl.next(l_index2);
514 end loop;
515 if l_duplicate = 1 then
516 p_local_var_body := p_local_var_body || indent(l_indent)
517 || 'l_' || rpad(l_column_name,28) || ' ' ||
518 p_table_info.upload_table_name || '.' || l_column_name ||'%type;';
519 end if;
520 l_index := g_resolve_pk_columns_tbl.next(l_index);
521 end loop;
522 hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.prepare_code_for_resolving_pk',
523 25);
524 exception
525 when others then
526 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.prepare_code_for_resolving_pk',
527 '(none)','R');
528 raise;
529 end prepare_code_for_resolving_pk;
530 ------- generate_get_id_frm_dev_key -----------------------
531 -- Description:
532 -- This function will
533 -- - Generates the procedure which will get the id value from the
534 -- AOL table for a given developer key.
535 -- - Creates a call to the function to get the id value from the
536 -- AOL table for a given developer key.
537 -- - Creates a local variable for the id value.
538 --
539 -- This procedure will generate the function for each column which have a
540 -- foreign key to AOL table.
541 -- Assumption : Id value is assumed to be number and developer key is assumed
542 -- to be varchar2.
543 -- Input Parameters
544 -- p_fk_to_aol_columns_tbl - Contains the information about all the columns
545 -- which have foreign key to AOL table.
546 -- p_table_info - Contains the info about the table to be
547 -- downloaded
548 -- Out Parameters
549 -- p_body - returns the actual procedure body for getting
550 -- the
551 -- p_call_to_proc_body - returns the string for a call to the function
552 -- p_dev_key_local_var_body - returns the string defining local var for
553 -- developer key
554 -- ------------------------------------------------------------------------
555 procedure generate_get_id_frm_dev_key
556 (
557 p_fk_to_aol_columns_tbl in hr_dm_gen_main.t_fk_to_aol_columns_tbl,
558 p_table_info in hr_dm_gen_main.t_table_info,
559 p_body in out nocopy varchar2,
560 p_call_to_proc_body in out nocopy varchar2,
561 p_dev_key_local_var_body in out nocopy varchar2
562 )
563 is
564 l_interface varchar2(32767);
565 l_locals varchar2(32767) := null;
566 l_cursor varchar2(32767) := null;
567 l_where_clause varchar2(32767) := null;
568 l_proc_comment varchar2(4000);
569 l_cursor_name varchar2(30) := 'csr_get_id_val';
570 l_proc_name varchar2(32767);
571 l_proc_body_tbl t_varchar2_32k_tbl;
572
573 -- variables to store the information about p_fk_to_aol_columns_tbl elements.
574 -- this is to reduce the variable name and add clarity.
575 l_column_name hr_dm_hierarchies.column_name%type;
576 l_parent_table_id hr_dm_hierarchies.parent_table_id%type;
577 l_parent_table_name hr_dm_tables.table_name%type;
578 l_parent_table_alias hr_dm_tables.table_alias%type;
579 l_parent_column_name hr_dm_hierarchies.parent_column_name%type;
580 l_parent_id_column_name hr_dm_hierarchies.parent_id_column_name%type;
581
582
583 -- block body of the procedure i.e between begin and end.
584 l_proc_body varchar2(32767) := null;
585
586 -- indentation for the statements.it specifies number of blank spaces
587 -- after which the staement should start.
588
589 l_indent number;
590 l_index number := p_fk_to_aol_columns_tbl.first;
591 begin
592 hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.generate_get_id_frm_dev_key ', 5);
593
594 while l_index is not null loop
595
596 l_column_name :=
597 p_fk_to_aol_columns_tbl(l_index).column_name;
598 l_parent_table_id :=
599 p_fk_to_aol_columns_tbl(l_index).parent_table_id;
600 l_parent_table_name :=
601 p_fk_to_aol_columns_tbl(l_index).parent_table_name;
602 l_parent_table_alias :=
603 p_fk_to_aol_columns_tbl(l_index).parent_table_alias;
604 l_parent_column_name :=
605 p_fk_to_aol_columns_tbl(l_index).parent_column_name;
606 l_parent_id_column_name :=
607 p_fk_to_aol_columns_tbl(l_index).parent_id_column_name;
608
609 if lower(l_parent_column_name) <> 'id_flex_structure_name' then
610 l_where_clause := l_parent_column_name || ' = ' ||
611 rtrim(rpad('p_' || l_parent_column_name, 30));
612 else
613 l_where_clause := 'id_flex_structure_name = ' || indent(8) ||
614 'substr(p_id_flex_structure_name, 1, ' || indent(10) ||
615 'instr(p_id_flex_structure_name,''-dm-dev-key-'') - 1)'
616 || indent(6) ||
617 'and id_flex_code = ' || indent(8) ||
618 'substr(p_id_flex_structure_name, ' || indent(16) ||
619 'instr(p_id_flex_structure_name,''-dm-dev-key-'') + 12' ||
620 indent(16) ||
621 ', length(p_id_flex_structure_name) - ' || indent(18) ||
622 'instr(p_id_flex_structure_name,''-dm-dev-key-''))';
623 end if;
624
625 l_proc_name := 'get_id_val_frm_' || l_parent_table_alias;
626
627 -- input parameters for the procedure
628 l_interface := indent || '(' ||rpad('p_' || l_parent_column_name, 30) ||
629 ' in varchar2,' || indent || ' ' ||
630 rpad('p_'|| l_column_name ,30) || ' out nocopy number)';
631
632 -- local variables of the procedure
633
634 l_locals := format_comment('Declare cursors and local variables',2)
635 || indent ||
636 ' l_proc varchar2(72) := g_package ' ||
637 '|| ''' || l_proc_name || ''';' || indent;
638
639 -- cursor to get the link value from the sequence from the data pump table
640 l_cursor := format_comment('Cursor to get the developer key from the '||
641 l_parent_table_name || ' table.',2);
642
643 l_cursor := l_cursor || indent(2) || 'cursor csr_get_id_val is ' ||
644 indent(4) ||
645 'select ' || l_parent_id_column_name || indent(4) ||
646 'from ' || l_parent_table_name ||
647 indent(4) || 'where ' || rtrim(l_where_clause) || ';';
648
649 -- add the logic of the body
650
651 l_indent := 2;
652
653 l_proc_body := indent(l_indent) ||
654 'open ' || l_cursor_name || ';' || indent(l_indent) || 'fetch ' ||
655 l_cursor_name || ' into ' || rpad('p_'|| l_column_name ,30)
656 || ';' || indent(l_indent) ||
657 'if ' || l_cursor_name || '%notfound then' || indent(l_indent + 2) ||
658 'close ' || l_cursor_name || ';' || indent(l_indent + 2) ||
659 'hr_utility.raise_error;' || indent(l_indent) || 'else'
660 ||indent(l_indent + 2)
661 ||'close ' || l_cursor_name || ';' || indent(l_indent) || 'end if;';
662
663
664 l_proc_body := l_proc_body || indent || 'end ' || l_proc_name || ';';
665
666
667 l_proc_comment := format_comment('procedure to get the the id value from '
668 || l_parent_table_name || ' table for ' ||
669 l_parent_column_name || ' column.' ) || indent;
670
671 -- add the procedure comment defination,local variables , cursor and
672 -- procedure body
673
674 l_proc_body_tbl(1) := l_proc_comment || 'procedure ' || l_proc_name ||
675 l_interface || ' is' || l_locals || l_cursor || indent ||
676 'begin' || indent || l_proc_body;
677
678 -- add the body of this procedure to the package.
679 add_to_package_body( l_proc_body_tbl );
680
681 -- construct a call to the procedure created above.
682 l_indent := 2;
683 p_call_to_proc_body := p_call_to_proc_body || indent ||
684 format_comment('Get the id value key for ' || l_column_name || '.',
685 l_indent);
686 p_call_to_proc_body := p_call_to_proc_body || indent(l_indent) ||
687 l_proc_name || '(p_' || rpad(l_parent_column_name,28) || ',' ||
688 indent(l_indent + 19) || 'l_' || rpad(l_column_name,28)
689 || ');';
690
691
692 -- construct the defination of local variable
693 l_indent := 2;
694 p_dev_key_local_var_body := p_dev_key_local_var_body || indent(l_indent)
695 || 'l_' || rpad(l_column_name,28) || ' ' ||
696 p_table_info.upload_table_name || '.' || l_column_name ||'%type;';
697 l_index := p_fk_to_aol_columns_tbl.next(l_index);
698 end loop;
699 hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.generate_get_id_frm_dev_key',
700 25);
701 exception
702 when others then
703 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.generate_get_id_frm_dev_key',
704 '(none)','R');
705 raise;
706 end generate_get_id_frm_dev_key;
707 --
708 -- ----------------------- prepare_glob_var_def_for_dt ----------------------
709 -- Description:
710 -- prepares the defination of the global variables for the primary key.
711 -- e.g g_old_pk_col1 number;
712 -- g_old_pk_col2 number;
713 -- the data type derived from g_pk_parameters_tbl which have entries like
714 -- column1 in number | column2 in varchar2
715 -- use instr function in conjunction with substr to get the data type.
716 -- ------------------------------------------------------------------------
717 procedure prepare_glob_var_def_for_dt
718 (
719 p_table_info in hr_dm_gen_main.t_table_info,
720 p_body in out nocopy varchar2
721 ) is
722 l_list_index number;
723 l_count number;
724 begin
725 hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.prepare_glob_var_def_for_dt ', 5);
726
727 -- initialise the variables
728 l_list_index := g_pk_columns_tbl.first;
729 l_count := 1;
730 --
731 -- read all the elements of pl/sql table and append them into text.
732 --
733 p_body := p_body || format_comment ( ' Global variables to store the primary '
734 || 'key columns of the last physical record processed.');
735
736 while l_list_index is not null loop
737
738 p_body := p_body || indent ||
739 rpad('g_old_'|| g_pk_columns_tbl(l_list_index),30) || ' ' ||
740 p_table_info.upload_table_name || '.' || g_pk_columns_tbl(l_list_index)
741 || '%type;';
742
743 l_list_index := g_pk_columns_tbl.next(l_list_index);
744 l_count := l_count + 1;
745 end loop;
746 hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.prepare_glob_var_def_for_dt',
747 25);
748 exception
749 when others then
750 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.prepare_glob_var_def_for_dt',
751 '(none)','R');
752 raise;
753 end prepare_glob_var_def_for_dt;
754
755 --
756 -- ----------------------- prepare_ins_dt_delete_stmt ----------------------
757 -- Description:
758 -- Parameter to the above procedures are also different.
759 -- Prepares the call to the ins_dt_delete procedure depending upon the number
760 -- of primary key columns and whether table has surrogate id or not.
761 -- ------------------------------------------------------------------------
762 procedure prepare_ins_dt_delete_stmt
763 (
764 p_table_info in hr_dm_gen_main.t_table_info,
765 p_proc_body in out nocopy varchar2 ,
766 p_ins_type in varchar2 default 'P',
767 p_indent in number
768 )
769 is
770 l_table_name varchar2(30) := upper(p_table_info.upload_table_name);
771 begin
772 hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.prepare_ins_dt_delete_stmt ', 5);
773
774 if p_table_info.surrogate_primary_key = 'Y' then
775 p_proc_body := p_proc_body ||
776 'hr_dm_library.ins_dt_delete (p_id => ' || 'p_' ||
777 rpad(g_pk_columns_tbl(1),28) ||',' ||indent(p_indent + 29) ||
778 'p_table_name => '''|| l_table_name || ''',' ||
779 indent(p_indent + 29) || 'p_ins_type => ''' || p_ins_type ||
780 ''');' ;
781 else
782 p_proc_body := p_proc_body ||
783 'hr_dm_library.ins_dt_delete ( p_table_name => '''|| l_table_name
784 || '''' || indent(p_indent + 29) || ',p_ins_type => ''' ||
785 p_ins_type || '''' ;
786 -- add the first column of the primary key.
787 p_proc_body := p_proc_body || indent(p_indent + 29) ||
788 ',p_pk_column_1 => p_' || rpad(g_pk_columns_tbl(1),28);
789
790 -- if the composite primary key has more than one column then add the column
791 -- to the call to ins_dt procedure.
792 if g_no_of_pk_columns > 1 then
793 p_proc_body := p_proc_body || indent(p_indent + 29) ||
794 ',p_pk_column_2 => p_' || rpad(g_pk_columns_tbl(2),28);
795 end if;
796
797 -- if the composite primary key has more than two column then add the column
798 -- to the call to ins_dt procedure.
799
800 if g_no_of_pk_columns > 2 then
801 p_proc_body := p_proc_body || indent(p_indent + 29) ||
802 ',p_pk_column_3 => p_' || rpad(g_pk_columns_tbl(3),28);
803 end if;
804
805 -- if the composite primary key has more than three column then add the
806 -- column to the call to ins_dt procedure.
807
808 if g_no_of_pk_columns > 3 then
809 p_proc_body := p_proc_body || indent(p_indent + 29) ||
810 ',p_pk_column_4 => p_' || rpad(g_pk_columns_tbl(4),28);
811 end if;
812
813 p_proc_body := p_proc_body || ');';
814 end if;
815 hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.prepare_ins_dt_delete_stmt',
816 25);
817 exception
818 when others then
819 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.prepare_ins_dt_delete_stmt',
820 '(none)','R');
821 raise;
822 end prepare_ins_dt_delete_stmt;
823 --
824 -- ----------------------- prepare_chk_dt_delete_stmt ----------------------
825 -- Description:
826 -- Prepares the call to the procedure to check the row in dt_delete table
827 -- depending upon the number of primary key columns and whether table has
828 -- surrogate id or not. The procedure name for table with surrogate_id is
829 -- Table Type procedure name
830 -- surrogate_id chk_row_in_dt_delete
831 -- 1 column primary key chk_row_in_dt_delete_1_pkcol
832 -- 2 column primary key chk_row_in_dt_delete_2_pkcol
833 -- 3 column primary key chk_row_in_dt_delete_3_pkcol
834 -- 4 column primary key chk_row_in_dt_delete_4_pkcol
835 -- ------------------------------------------------------------------------
836 procedure prepare_chk_dt_delete_stmt
837 (
838 p_table_info in hr_dm_gen_main.t_table_info,
839 p_proc_body in out nocopy varchar2 ,
840 p_indent in number
841 )
842 is
843 l_proc_name varchar2(30) ;
844 l_table_name varchar2(30) := upper(p_table_info.upload_table_name);
845 begin
846 hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.prepare_chk_dt_delete_stmt ', 5);
847
848 if p_table_info.surrogate_primary_key = 'Y' then
849 p_proc_body := p_proc_body ||
850 'hr_dm_library.chk_row_in_dt_delete (p_id => p_' ||
851 rpad(g_pk_columns_tbl(1),28) ||',' ||indent(p_indent + 30) ||
852 'p_table_name => '''|| l_table_name || ''',' ||
853 indent(p_indent + 30) || 'p_ins_type => l_ins_type,' ||
854 indent(p_indent + 30) || 'p_row_exists => l_row_exists);';
855 else
856 -- form the procedure name based on the number of columns in primary
857 -- key.
858 l_proc_name := 'chk_row_in_dt_delete_' || to_char(g_no_of_pk_columns) ||
859 '_pkcol' ;
860 p_proc_body := p_proc_body ||
861 'hr_dm_library.' || l_proc_name || '(' ||
862 indent(p_indent + 28) || 'p_table_name => ''' || l_table_name || '''' ||
863 indent(p_indent + 29) || ',p_ins_type => l_ins_type' ||
864 indent(p_indent + 29) || ',p_row_exists => l_row_exists';
865
866 -- add the first column of the primary key.
867 p_proc_body := p_proc_body || indent(p_indent + 29) ||
868 ',p_pk_column_1 => p_' || rpad(g_pk_columns_tbl(1),28);
869
870 -- if the composite primary key has more than one column then add the column
871 -- to the call to ins_dt procedure.
872 if g_no_of_pk_columns > 1 then
873 p_proc_body := p_proc_body || indent(p_indent + 29) ||
874 ',p_pk_column_2 => p_' || rpad(g_pk_columns_tbl(2),28);
875 end if;
876
877 -- if the composite primary key has more than two column then add the column
878 -- to the call to ins_dt procedure.
879
880 if g_no_of_pk_columns > 2 then
881 p_proc_body := p_proc_body || indent(p_indent + 29) ||
882 ',p_pk_column_3 => p_' || rpad(g_pk_columns_tbl(3),28);
883 end if;
884
885 -- if the composite primary key has more than three column then add the
886 -- column to the call to ins_dt procedure.
887
888 if g_no_of_pk_columns > 3 then
889 p_proc_body := p_proc_body || indent(p_indent + 29) ||
890 ',p_pk_column_4 => p_' || rpad(g_pk_columns_tbl(4),28);
891 end if;
892
893 p_proc_body := p_proc_body || ');';
894 end if;
895 hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.prepare_chk_dt_delete_stmt',
896 25);
897 exception
898 when others then
899 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.prepare_chk_dt_delete_stmt',
900 '(none)','R');
901 raise;
902 end prepare_chk_dt_delete_stmt;
903 --
904 -- ----------------------- prepare_dt_upload_body ----------------------
905 -- Description:
906 -- Prepare the procedure body of upload procedure for date tracked table.
907 -- ------------------------------------------------------------------------
908 procedure prepare_dt_upload_body
909 (
910 p_table_info in hr_dm_gen_main.t_table_info,
911 p_proc_body out nocopy varchar2
912 )
913 is
914 l_proc_comment varchar2(4000);
915
916 -- block body of the procedure i.e between begin and end.
917 l_proc_body varchar2(32767) := null;
918 l_func_body varchar2(32767) := null;
919
920 -- indentation for the statements.it specifies number of blank spaces
921 -- after which the staement should start.
922
923 l_migrate_data boolean := true;
924 l_indent number;
925
926 begin
927 hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.prepare_dt_upload_body', 5);
928
929 l_indent := 2;
930
931 -- add in SR migration code
932
933 l_proc_body := format_comment ('For an SR migration the existance of the' ||
934 ' logical record is checked and if the id values are ' ||
935 'different then an entry is made into the resolve pks ' ||
936 'table.');
937 l_proc_body := l_proc_body || indent(l_indent) ||
938 'if p_migration_type = ''SR'' then ';
939
940 l_indent := 4;
941 -- call chk_row_exists code
942 l_proc_comment := format_comment('Call chk_row_exists code to populate' ||
943 ' the hr_dm_resolve_pks table.');
944 if p_table_info.use_non_pk_col_for_chk_row = 'Y' then
945 l_proc_body := l_proc_body || l_proc_comment || indent(l_indent) ||
946 g_chk_row_exists.call_to_proc;
947 else
948 l_proc_body := l_proc_body || l_proc_comment || indent(l_indent) ||
949 'chk_row_exists( ' ||
950 hr_dm_library.conv_list_to_text(
951 p_rpad_spaces => l_indent + 13,
952 p_pad_first_line => 'N',
953 p_prefix_col => 'p_',
954 p_columns_tbl => g_pk_columns_tbl,
955 p_col_length => 28,
956 p_overide_tbl => g_resolve_pk_columns_tbl,
957 p_overide_prefix => 'l_') || indent(l_indent + 13) ||
958 ',l_row_exists)' || ';';
959 end if;
960
961 l_proc_body := l_proc_body || indent(l_indent-2) || 'else';
962
963
964
965 if p_table_info.global_data = 'N' then
966
967 -- if code to chk row exists is required always then do not
968 -- put the if clause.
969
970 if p_table_info.chk_row_exists_for_non_glb_tbl = 'N' then
971
972 -- comment for the logic
973 l_proc_comment := l_proc_comment || format_comment (
974 ' For FULL migration no checks are made. ' ||
975 'For ADDITIVE migration a physical'||
976 ' row is inserted if a logical record does not exist (excluding the logical'
977 || ' record created by other slave processes). if logical record exists for'
978 || ' a Application data type migration, then logical record is deleted from '
979 || ' destination and a record is created into ' ||
980 'dt_delete table with type ''D'' (so as other physical records for this ' ||
981 'logical record will skip this check) and will upload the ' ||
982 'data. If logical record exists for'
983 || ' any other type of data migration then a record is created into ' ||
984 'dt_delete table with type ''P''.', l_indent) || indent;
985
986
987 l_proc_body := l_proc_body || l_proc_comment || indent(l_indent) ||
988 'if p_last_migration_date is not null then ' ||
989 indent(l_indent);
990
991 l_indent := 6;
992 else
993 -- comment for the logic
994
995 l_proc_comment := l_proc_comment || format_comment (
996 ' For FULL or ADDITIVE migration a physical'||
997 ' row is inserted if a logical record does not exist (excluding the logical'
998 || ' record created by other slave processes). if logical record exists for'
999 || ' a Application data type migration, then logical record is deleted from '
1000 || ' destination and a record is created into ' ||
1001 'dt_delete table with type ''D'' (so as other physical records for this ' ||
1002 'logical record will skip this check) and will upload the ' ||
1003 'data. If logical record exists for'
1004 || ' any other type of data migration then a record is created into ' ||
1005 'dt_delete table with type ''P''.', l_indent) || indent;
1006
1007
1008 l_proc_body := l_proc_body || l_proc_comment || indent(l_indent);
1009
1010 l_indent := 4;
1011 end if;
1012 else
1013
1014 -- comment for the logic
1015
1016 l_proc_comment := l_proc_comment || format_comment (
1017 ' For FULL or ADDITIVE migration a physical'||
1018 ' row is inserted if a logical record does not exist (excluding the logical'
1019 || ' record created by other slave processes). if logical record exists for'
1020 || ' a Application data type migration, then logical record is deleted from '
1021 || ' destination and a record is created into ' ||
1022 'dt_delete table with type ''D'' (so as other physical records for this ' ||
1023 'logical record will skip this check) and will upload the ' ||
1024 'data. If logical record exists for'
1025 || ' any other type of data migration then a record is created into ' ||
1026 'dt_delete table with type ''P''.', l_indent) || indent;
1027
1028
1029 l_proc_body := l_proc_body || l_proc_comment || indent(l_indent);
1030
1031 l_indent := 4;
1032
1033 end if;
1034
1035 -- code to check whether this Id had been processed earlier by comapring the
1036 -- Id value of the last
1037
1038 l_proc_body := l_proc_body || indent(l_indent) || 'if ' ||
1039 hr_dm_library.get_func_asg
1040 ( p_rpad_spaces => l_indent,
1041 p_columns_tbl => g_pk_columns_tbl,
1042 p_prefix_left_asg => 'g_old_',
1043 p_prefix_right_asg => 'p_',
1044 p_omit_business_group_id => 'N',
1045 p_comma_on_first_line => 'N',
1046 p_pad_first_line => 'N',
1047 p_equality_sign => ' = ',
1048 p_start_terminator => 'and ',
1049 p_end_terminator => null);
1050
1051 l_proc_body := l_proc_body || indent(l_indent) ||'then' ||
1052 indent(l_indent + 2) || 'l_insert := TRUE;' || indent(l_indent) || 'else' ||
1053 indent(l_indent + 2);
1054
1055
1056 l_indent := l_indent + 2; -- l_indent = 6
1057
1058 l_proc_comment := format_comment (p_comment_text =>
1059 'Find out nocopy if the row already exists for this id in the dt_deletes table '||
1060 'in the database or not.',
1061 p_indent_spaces => l_indent) || indent;
1062
1063 --
1064 -- construct a call to function to check whether a row exists in dt_delete
1065 -- table for the given table/id combination.
1066 --
1067
1068 l_proc_body := l_proc_body ||l_proc_comment || indent(l_indent);
1069
1070 -- call to procedure to check whether row exists in dt_delete table.
1071 prepare_chk_dt_delete_stmt
1072 ( p_table_info => p_table_info,
1073 p_proc_body => l_proc_body,
1074 p_indent => l_indent);
1075
1076 --
1077 -- Processing logic based on if row exists in dt_delete.
1078 --
1079 l_proc_body := l_proc_body || indent(l_indent) ||
1080 'if l_row_exists = ''Y'' then' || indent(l_indent + 2) ||'if l_ins_type = ' ||
1081 '''D'' then ' || indent(l_indent + 4) || 'l_insert := TRUE;' ||
1082 indent(l_indent + 2) || 'else -- l_ins_type = ''P''' || indent(l_indent + 4)
1083 || 'l_insert := FALSE;' || indent(l_indent + 2) || 'end if; -- l_row_exists'
1084 || '= ''Y''' ||indent(l_indent) || 'else -- l_row_exists <> ''Y''. ';
1085
1086
1087 --
1088 -- Processing logic based on if row does not exists in dt_delete.
1089 --
1090
1091 l_indent := l_indent + 4; -- l_indent = 10
1092
1093 l_proc_comment := format_comment (p_comment_text =>
1094 'call chk_row_exists procedure to check whether this row already exist ' ||
1095 'in the database or not.',
1096 p_indent_spaces => l_indent) || indent;
1097
1098 --
1099 -- Row does not exist in DT_DELETE table for this table/Id combination, so
1100 -- call the function to check whether row exists in the destination table
1101 -- for the given unique fields.
1102 --
1103 if p_table_info.use_non_pk_col_for_chk_row = 'Y' then
1104 l_proc_body := l_proc_body || l_proc_comment || indent(l_indent) ||
1105 g_chk_row_exists.call_to_proc;
1106 else
1107 l_proc_body := l_proc_body || l_proc_comment || indent(l_indent) ||
1108 'chk_row_exists( ' ||
1109 hr_dm_library.conv_list_to_text(
1110 p_rpad_spaces => l_indent + 15,
1111 p_pad_first_line => 'N',
1112 p_prefix_col => 'p_',
1113 p_columns_tbl => g_pk_columns_tbl,
1114 p_col_length => 28,
1115 p_overide_tbl => g_resolve_pk_columns_tbl,
1116 p_overide_prefix => 'l_') || indent(l_indent + 15) ||
1117 ',l_row_exists)' || ';';
1118 end if;
1119
1120 --
1121 -- Row does not exists in the destination table for the given Id.
1122 -- Construct a call to function to insert a row into dt_deletes table
1123 -- so that other physical records can be uploaded without performing
1124 -- any checks.
1125 --
1126
1127 l_migrate_data := migrate_table_data(p_table_info);
1128
1129 l_proc_body := l_proc_body || indent(l_indent) ||
1130 'if l_row_exists = ''N'' then' || indent(l_indent + 2) ||
1131 format_comment('create a row into dt_deletes table of type ''D'',so the' ||
1132 ' subsequent physical records can be uploaded without performing any ' ||
1133 'checks.',l_indent+2) || indent(l_indent + 2);
1134
1135 if l_migrate_data then
1136 -- call to insert a row in to dt_delete table.
1137 prepare_ins_dt_delete_stmt
1138 ( p_table_info => p_table_info,
1139 p_proc_body => l_proc_body,
1140 p_ins_type => 'D',
1141 p_indent => l_indent + 2);
1142
1143 l_proc_body := l_proc_body || indent(l_indent + 2) ||
1144 'l_insert := TRUE;' || indent(l_indent) || 'else -- row already exists' ||
1145 ' in the database';
1146 else
1147 l_proc_body := l_proc_body || indent(l_indent + 2) ||
1148 'l_insert := FALSE;' || indent(l_indent) || 'else -- row already exists' ||
1149 ' in the database';
1150 end if;
1151
1152 --
1153 -- Row exists in the destination table for the given Id and Migration type
1154 -- is Application Data Migration. Call functionto create a row in DT_DELETE
1155 -- table of type 'D'. Delete the existing rows from the table for the
1156 -- given ID.
1157 --
1158 l_indent := l_indent + 4; -- l_indent := 14
1159 l_proc_body := l_proc_body || indent(l_indent - 2) ||
1160 format_comment('if migration_type is application then update the row',
1161 l_indent,'N') || indent(l_indent - 2);
1162 if not l_migrate_data then
1163
1164 -- call to insert a row in to dt_elete table.
1165 prepare_ins_dt_delete_stmt
1166 ( p_table_info => p_table_info,
1167 p_proc_body => l_proc_body,
1168 p_ins_type => 'P',
1169 p_indent => l_indent);
1170
1171 l_proc_body := l_proc_body || indent(l_indent) ||
1172 'l_insert := FALSE;';
1173 else
1174 l_proc_body := l_proc_body||'if p_migration_type in (''FW'', ''A'', ''SF'') then' ||indent(l_indent) ||
1175 format_comment('create a row into dt_deletes table of type ''D'',so the' ||
1176 ' subsequent physical records can be uploaded without performing any ' ||
1177 'checks.',l_indent,'N') || indent(l_indent) ;
1178
1179 -- call to insert a row in to dt_elete table.
1180 prepare_ins_dt_delete_stmt
1181 ( p_table_info => p_table_info,
1182 p_proc_body => l_proc_body,
1183 p_ins_type => 'D',
1184 p_indent => l_indent);
1185
1186 l_proc_body := l_proc_body || indent(l_indent) ||
1187 format_comment('call delete_dml procedure to delete all existing rows for' ||
1188 ' the given id',l_indent);
1189
1190 hr_dm_utility.message('INFO','use_non_pk_col_for_chk_row '||p_table_info.use_non_pk_col_for_chk_row, 5);
1191 hr_dm_utility.message('INFO','upload_table_name '||p_table_info.upload_table_name, 5);
1192 if p_table_info.use_non_pk_col_for_chk_row = 'Y' then
1193 l_proc_body := l_proc_body || indent(l_indent) || g_delete_dml.call_to_proc;
1194 else
1195 l_proc_body := l_proc_body || indent(l_indent) ||
1196 'delete_dml( ' || hr_dm_library.conv_list_to_text(
1197 p_rpad_spaces => l_indent + 11,
1198 p_pad_first_line => 'N',
1199 p_prefix_col => 'p_',
1200 p_columns_tbl => g_pk_columns_tbl,
1201 p_col_length => 28) || ');';
1202 end if;
1203 l_proc_body := l_proc_body || indent(l_indent) ||
1204 'l_insert := TRUE;';
1205
1206 l_proc_body := l_proc_body|| indent(l_indent - 2) || 'else -- migration type other' ||
1207 ' than application data migration';
1208
1209 --
1210 -- Row exists in the destination table for the given Id and Migration type
1211 -- is other than Application Data Migration. Call functionto create a row
1212 -- in DT_DELETE table of type 'P'.
1213 --
1214
1215 l_proc_body := l_proc_body || indent(l_indent) ||
1216 format_comment('create a row into dt_deletes table of type ''P'',so the' ||
1217 ' subsequent physical records can skip these checks and info will be used' ||
1218 'for reporting.',l_indent,'N') || indent(l_indent);
1219
1220 -- call to insert a row in to dt_delete table.
1221 prepare_ins_dt_delete_stmt
1222 ( p_table_info => p_table_info,
1223 p_proc_body => l_proc_body,
1224 p_ins_type => 'P',
1225 p_indent => l_indent);
1226
1227 l_proc_body := l_proc_body || indent(l_indent) || 'l_insert := FALSE;' ||
1228 indent(l_indent - 2) || 'end if; ' || '--p_migration_type = ''A''';
1229
1230 end if;
1231 --
1232 -- close all the if's statements
1233 --
1234 l_proc_body := l_proc_body || indent(l_indent - 4) ||
1235 'end if; -- l_row_exists = ''N''' || indent(l_indent - 6) ||
1236 'end if; -- l_row_exists = ''Y''' || indent(l_indent - 8) ||
1237 'end if; -- g_old_' || rpad(g_pk_columns_tbl(1),24) ||
1238 ' = p_' || g_pk_columns_tbl(1);
1239
1240 if p_table_info.global_data = 'N' and
1241 p_table_info.chk_row_exists_for_non_glb_tbl = 'N' then
1242 l_indent := 4;
1243 l_proc_body := l_proc_body || indent(l_indent) || 'else -- full migration ' ||
1244 indent(l_indent + 2) || 'l_insert := TRUE;' || indent(l_indent) ||
1245 'end if;';
1246 end if;
1247
1248 p_proc_body := p_proc_body || l_proc_body;
1249 hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.prepare_dt_upload_body',
1250 25);
1251 exception
1252 when others then
1253 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.prepare_dt_upload_body',
1254 '(none)','R');
1255 raise;
1256 end prepare_dt_upload_body;
1257 -- ----------------------- prepare_non_dt_upload_body ----------------------
1258 -- Description:
1259 -- Prepare the procedure body of upload proxcedure for non date tracked table.
1260 -- ------------------------------------------------------------------------
1261 procedure prepare_non_dt_upload_body
1262 (
1263 p_table_info in hr_dm_gen_main.t_table_info,
1264 p_proc_body out nocopy varchar2
1265 )
1266 is
1267 l_proc_comment varchar2(4000);
1268
1269 l_check_row_exists varchar2(1);
1270
1271
1272 -- block body of the procedure i.e between begin and end.
1273 l_proc_body varchar2(32767) := null;
1274 l_func_body varchar2(32767) := null;
1275
1276 -- indentation for the statements.it specifies number of blank spaces
1277 -- after which the staement should start.
1278
1279 l_indent number;
1280 begin
1281 hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.prepare_non_dt_upload_body ', 5);
1282
1283 l_indent := 2;
1284
1285 -- add in SR migration code
1286
1287 l_proc_body := format_comment ('For an SR migration the existance of the' ||
1288 ' logical record is checked and if the id values are ' ||
1289 'different then an entry is made into the resolve pks ' ||
1290 'table.');
1291 l_proc_body := l_proc_body || indent(l_indent) ||
1292 'if p_migration_type = ''SR'' then ' ||
1293 indent(l_indent);
1294
1295 l_proc_comment := format_comment('Call chk_row_exists code to populate' ||
1296 ' the hr_dm_resolve_pks table.');
1297 if p_table_info.use_non_pk_col_for_chk_row = 'Y' then
1298 l_proc_body := l_proc_body || indent(l_indent) || l_proc_comment ||
1299 indent(l_indent) || g_chk_row_exists.call_to_proc;
1300 else
1301 l_proc_body := l_proc_body || indent(l_indent) || l_proc_comment ||
1302 indent(l_indent) || 'chk_row_exists( ' ||
1303 hr_dm_library.conv_list_to_text(
1304 p_rpad_spaces => l_indent + 13,
1305 p_pad_first_line => 'N',
1306 p_prefix_col => 'p_',
1307 p_columns_tbl => g_pk_columns_tbl,
1308 p_col_length => 28,
1309 p_overide_tbl => g_resolve_pk_columns_tbl,
1310 p_overide_prefix => 'l_') || indent(l_indent + 13) ||
1311 ',l_row_exists)' || ';';
1312 end if;
1313
1314 l_proc_body := l_proc_body || indent(l_indent) || 'else';
1315 l_indent := 4;
1316
1317 if (p_table_info.chk_row_exists_for_non_glb_tbl = 'Y')
1318 or (p_table_info.always_check_row = 'Y') then
1319 l_check_row_exists := 'Y';
1320 else
1321 l_check_row_exists := 'N';
1322 end if;
1323
1324
1325 if p_table_info.global_data = 'N' then
1326 -- comment for the logic
1327 l_proc_comment := format_comment (
1328 'if last_migration_date is null then it means a FULL migration for ' ||
1329 'this business group is done, otherwise, it is an ADDITIVE migration.',
1330 l_indent,'N');
1331
1332 --
1333 -- if l_check_row_exists is set to 'Y' then
1334 -- don't put the code for row exists.
1335 --
1336 if l_check_row_exists = 'N' then
1337 l_proc_comment := l_proc_comment || format_comment (
1338 ' For FULL migration no checks are made. For ADDITIVE migration a row is ' ||
1339 'inserted if it does not exist or updated if row exists for a Application' ||
1340 ' data type migration, otherwise, a record is created in DT_DELETES table.',
1341 l_indent) || indent;
1342
1343 l_proc_body := l_proc_body || l_proc_comment || indent(l_indent) ||
1344 'if p_last_migration_date is not null then ' ||
1345 indent(l_indent);
1346
1347 l_indent := 6;
1348 else -- make the check for row exists
1349 l_proc_comment := l_proc_comment || format_comment (
1350 ' For data migration a row is ' ||
1351 'inserted, if it does not exist or updated if row exists for a Application' ||
1352 ' or Full data type migration, otherwise,a record is created in DT_DELETES table.',
1353 l_indent) || indent;
1354 l_proc_body := l_proc_body || l_proc_comment || indent(l_indent);
1355 end if;
1356 else
1357
1358 l_proc_comment := l_proc_comment || format_comment (
1359 ' This table contains Global Data. For data migration a row is ' ||
1360 'inserted, if it does not exist or updated if row exists for a Application' ||
1361 ' or Full data type migration, otherwise,a record is created in DT_DELETES table.',
1362 l_indent) || indent;
1363 l_proc_body := l_proc_body || l_proc_comment || indent(l_indent);
1364 end if;
1365
1366 l_proc_comment := format_comment (p_comment_text =>
1367 'call chk_row_exists procedure to check whether this row already exist ' ||
1368 'in the database or not.',
1369 p_indent_spaces => l_indent) || indent;
1370
1371
1372 l_proc_body := l_proc_body || l_proc_comment || indent(l_indent);
1373
1374 if p_table_info.use_non_pk_col_for_chk_row = 'Y' then
1375 l_proc_body := l_proc_body || g_chk_row_exists.call_to_proc;
1376 else
1377 l_proc_body := l_proc_body ||
1378 'chk_row_exists( ' ||
1379 hr_dm_library.conv_list_to_text(
1380 p_rpad_spaces => l_indent + 15,
1381 p_pad_first_line => 'N',
1382 p_prefix_col => 'p_',
1383 p_columns_tbl => g_pk_columns_tbl,
1384 p_col_length => 28,
1385 p_overide_tbl => g_resolve_pk_columns_tbl,
1386 p_overide_prefix => 'l_') || indent(l_indent + 15) ||
1387 ',l_row_exists)' || ';';
1388 end if;
1389
1390 l_proc_body := l_proc_body || indent(l_indent) ||
1391 'if l_row_exists = ''N'' then' || indent(l_indent + 2) ||'l_insert := TRUE;'
1392 || indent(l_indent + 2) ||'l_update := FALSE;' || indent(l_indent) ||
1393 'else -- row already ' || 'exists in the database' ||
1394 indent(l_indent + 2) ||
1395 format_comment('if migration_type is application then update the row',
1396 l_indent + 2);
1397
1398 l_indent := l_indent + 4; --l_indent := 8;
1399 l_proc_body := l_proc_body || indent(l_indent - 2) ||
1400 'if p_migration_type in (''A'', ''FW'') then' ||
1401 indent(l_indent) || 'l_update := TRUE;'||
1402 indent(l_indent - 2) || 'else' || indent(l_indent) ||'l_update := FALSE;' ||
1403 indent(l_indent) || '-- write into dt_deletes_table' || indent(l_indent);
1404
1405 -- call to insert a row in to dt_delete table.
1406 prepare_ins_dt_delete_stmt
1407 ( p_table_info => p_table_info,
1408 p_proc_body => l_proc_body,
1409 p_ins_type => 'P',
1410 p_indent => l_indent);
1411
1412 l_proc_body := l_proc_body || indent(l_indent - 2)
1413 || 'end if;' || indent(l_indent - 4) || 'end if;';
1414
1415 if p_table_info.global_data = 'N' and
1416 l_check_row_exists = 'N' then
1417 l_indent := 4;
1418 l_proc_body := l_proc_body || indent(l_indent) || 'else -- full migration ' ||
1419 indent(l_indent + 2) || 'l_insert := TRUE;' || indent(l_indent) ||
1420 'end if;';
1421 end if;
1422 p_proc_body := p_proc_body || l_proc_body;
1423 hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.prepare_non_dt_upload_body',
1424 25);
1425 exception
1426 when others then
1427 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.prepare_non_dt_upload_body',
1428 '(none)','R');
1429 raise;
1430 end prepare_non_dt_upload_body;
1431 -- ----------------------- generate_upload --------------------------------
1432 -- Description:
1433 -- Generates the upload procedure of the TUPS
1434 -- ------------------------------------------------------------------------
1435 procedure generate_upload
1436 (
1437 p_table_info in hr_dm_gen_main.t_table_info,
1438 p_header in out nocopy varchar2,
1439 p_body in out nocopy varchar2,
1440 p_call_to_proc_body in varchar2,
1441 p_dev_key_local_var_body in varchar2,
1442 p_fk_to_aol_columns_tbl in hr_dm_gen_main.t_fk_to_aol_columns_tbl
1443 )
1444 is
1445 l_interface varchar2(32767);
1446 l_locals varchar2(32767) := null;
1447 l_cursor varchar2(32767) := null;
1448 l_comment varchar2(4000);
1449 l_proc_comment varchar2(4000);
1450 l_cursor_name varchar2(30) := 'csr_mig_' || p_table_info.alias;
1451
1452 -- block body of the procedure i.e between begin and end.
1453
1454 l_proc_name varchar2(30) := 'u'|| p_table_info.short_name;
1455 l_proc_body varchar2(32767) := null;
1456 l_resolve_pk_local_var varchar2(2000) := null;
1457
1458 -- indentation for the statements.it specifies number of blank spaces
1459 -- after which the staement should start.
1460
1461 l_indent number;
1462 l_parameters_tbl hr_dm_library.t_varchar2_tbl;
1463 l_proc_body_tbl t_varchar2_32k_tbl;
1464 l_proc_index number := 1;
1465 begin
1466 hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.generate_upload', 5);
1467
1468 if p_table_info.fk_to_aol_table = 'N' then
1469 l_parameters_tbl := g_parameters_tbl;
1470 else
1471 l_parameters_tbl := g_aol_parameters_tbl;
1472 end if;
1473 -- input parameters for the procedure
1474
1475 l_interface := indent ||
1476 '(p_last_migration_date in date' || indent ||
1477 ',p_migration_type in varchar2' || indent ||
1478 ',';
1479
1480 -- add the column parameters to the procedure.
1481 l_interface := l_interface ||
1482 hr_dm_library.conv_list_to_text(
1483 p_rpad_spaces => 0,
1484 p_pad_first_line => 'N',
1485 p_columns_tbl => l_parameters_tbl,
1486 p_col_length => 70) ||indent || ')' ;
1487
1488 l_proc_body_tbl(l_proc_index) := l_interface;
1489
1490 l_proc_index := l_proc_index + 1;
1491
1492 -- local variables of the procedure
1493
1494 l_locals := format_comment('Declare cursors and local variables',2)
1495 || indent ||
1496 ' l_proc varchar2(72) := g_package ' ||
1497 '|| ''u' || p_table_info.short_name || ''';' || indent ||
1498 ' l_insert boolean := FALSE;' || indent ||
1499 ' l_update boolean := FALSE;' || indent ||
1500 ' l_row_count number := 0;' || indent ||
1501 ' l_row_exists varchar2(1) := ''N'';' ||indent;
1502
1503
1504 -- if table has a column which have foreign key to the AOL table then create
1505 -- local variable for each corresponding developer key.
1506
1507 if p_table_info.fk_to_aol_table = 'Y' then
1508 l_locals := l_locals || p_dev_key_local_var_body || indent;
1509 -- l_proc_body := l_proc_body || p_call_to_proc_body ;
1510 l_proc_body_tbl(l_proc_index) := p_call_to_proc_body;
1511 l_proc_index := l_proc_index + 1;
1512 end if;
1513
1514 -- if the table has 'L' or 'H' type hierarchy then add the code to get the
1515 -- code to get the ID value of the column used in the destination database.
1516 -- It is done by getting the value from get_resolve_pk function.
1517
1518 if p_table_info.resolve_pk = 'Y' then
1519 prepare_code_for_resolving_pk
1520 ( p_resolve_pk_columns_tbl => g_resolve_pk_columns_tbl,
1521 p_table_info => p_table_info,
1522 p_call_to_proc_body => l_proc_body,
1523 p_local_var_body => l_resolve_pk_local_var
1524 );
1525
1526 l_proc_body_tbl(l_proc_index) := l_proc_body;
1527 l_proc_index := l_proc_index + 1;
1528
1529 l_locals := l_locals || l_resolve_pk_local_var || indent;
1530
1531 end if;
1532
1533 -- if the table is an HR_DMV_% view then add a p_business_group_id
1534 -- column for the chk_row_exists procedure
1535 if (p_table_info.use_non_pk_col_for_chk_row = 'Y') and
1536 (upper(substr(p_table_info.table_name,1,7)) = 'HR_DMV_') then
1537 l_locals := l_locals || ' p_business_group_id number := NULL;'
1538 || indent;
1539 end if;
1540
1541 -- if the table is non date track table then call non date track function
1542 -- to construct the non date track procedure body, otherwise, call
1543 -- date track procedure.
1544 l_proc_body := null;
1545 if p_table_info.datetrack = 'N' then
1546 -- call non date track procedure
1547 prepare_non_dt_upload_body ( p_table_info,
1548 l_proc_body);
1549 else
1550 -- extra local parameters for date track table.
1551 l_locals := l_locals || ' l_ins_type varchar2(1) := ''N'';'
1552 || indent;
1553 -- call date track procedure
1554 prepare_dt_upload_body ( p_table_info,
1555 l_proc_body);
1556 end if;
1557 l_proc_body_tbl(l_proc_index) := l_proc_body;
1558 l_proc_index := l_proc_index + 1;
1559 l_proc_body := null;
1560
1561 -- add the logic of proc body common for date track and non date track table
1562 l_indent := 4;
1563 l_proc_body_tbl(l_proc_index) := indent(l_indent) ||
1564 '-- if l_insert flag is true then insert the row. ' ||indent(l_indent )
1565 || 'if l_insert then ' || indent(l_indent + 2)
1566 || 'insert_dml (' ;
1567
1568 l_proc_index := l_proc_index + 1;
1569
1570
1571 -- if the columns of the table have foreign key to AOL table then call
1572 -- call assignment function which uses the l_ instead of p_ for the
1573 -- that column assignment.
1574 if p_table_info.fk_to_aol_table = 'N' then
1575 l_proc_body_tbl(l_proc_index) := hr_dm_library.get_func_asg (
1576 p_rpad_spaces => l_indent + 14,
1577 p_columns_tbl => g_columns_tbl,
1578 p_omit_business_group_id => 'N',
1579 p_comma_on_first_line => 'N',
1580 p_pad_first_line => 'N',
1581 p_resolve_pk_columns_tbl => g_resolve_pk_columns_tbl ) || ');';
1582 l_proc_index := l_proc_index + 1;
1583 else
1584 l_proc_body_tbl(l_proc_index) :=
1585 hr_dm_library.get_func_asg_with_dev_key (
1586 p_rpad_spaces => l_indent + 14,
1587 p_columns_tbl => g_columns_tbl,
1588 p_omit_business_group_id => 'N',
1589 p_comma_on_first_line => 'N',
1590 p_pad_first_line => 'N',
1591 p_prefix_left_asg_dev_key => 'p_',
1592 p_prefix_right_asg_dev_key => 'l_',
1593 p_use_aol_id_col => 'Y',
1594 p_fk_to_aol_columns_tbl => p_fk_to_aol_columns_tbl,
1595 p_resolve_pk_columns_tbl => g_resolve_pk_columns_tbl ) || ');' ;
1596 l_proc_index := l_proc_index + 1;
1597 end if;
1598
1599
1600 -- if it is a date track table then add the logic of storing the id value
1601 -- of the row uploaded.
1602
1603 if p_table_info.datetrack = 'Y' then
1604 l_proc_body_tbl(l_proc_index) := indent ||
1605 format_comment ( ' Store the primary columns into global variables '
1606 || 'for avoiding the checks for upload of other physical recoprds ' ||
1607 'belonging to this logical record ',4);
1608
1609 l_proc_index := l_proc_index + 1;
1610
1611 l_proc_body_tbl(l_proc_index) := indent(l_indent + 1) ||
1612 hr_dm_library.get_func_asg
1613 ( p_rpad_spaces => l_indent + 2,
1614 p_columns_tbl => g_pk_columns_tbl,
1615 p_prefix_left_asg => 'g_old_',
1616 p_prefix_right_asg => 'p_',
1617 p_omit_business_group_id => 'N',
1618 p_comma_on_first_line => 'N',
1619 p_pad_first_line => 'N',
1620 p_equality_sign => ' := ',
1621 p_start_terminator => null,
1622 p_end_terminator => ';');
1623 l_proc_index := l_proc_index + 1;
1624 end if;
1625
1626 -- close the if statement.
1627 l_proc_body_tbl(l_proc_index) := indent(l_indent) || l_proc_body ||
1628 indent(l_indent) || 'end if;';
1629 l_proc_index := l_proc_index + 1;
1630
1631 -- call update_dml function only in case of non date track table.
1632 if p_table_info.datetrack = 'N' then
1633 l_proc_body_tbl(l_proc_index) := indent(l_indent) ||
1634 '-- if l_update flag is true then update the row. ' ||indent(l_indent)
1635 || 'if l_update then ' || indent(l_indent + 2)
1636 || 'update_dml (' ;
1637
1638 l_proc_index := l_proc_index + 1;
1639 -- if the columns of the table have foreign key to AOL table then call
1640 -- call assignment function which uses the l_ instead of p_ for the
1641 -- that column assignment.
1642 if p_table_info.fk_to_aol_table = 'N' then
1643 l_proc_body_tbl(l_proc_index ) := hr_dm_library.get_func_asg (
1644 p_rpad_spaces => l_indent + 14,
1645 p_columns_tbl => g_columns_tbl,
1646 p_omit_business_group_id => 'N',
1647 p_comma_on_first_line => 'N',
1648 p_pad_first_line => 'N',
1649 p_resolve_pk_columns_tbl => g_resolve_pk_columns_tbl ) || ');';
1650 l_proc_index := l_proc_index + 1;
1651
1652 else
1653 l_proc_body_tbl(l_proc_index) :=
1654 hr_dm_library.get_func_asg_with_dev_key (
1655 p_rpad_spaces => l_indent + 14,
1656 p_columns_tbl => g_columns_tbl,
1657 p_omit_business_group_id => 'N',
1658 p_comma_on_first_line => 'N',
1659 p_pad_first_line => 'N',
1660 p_prefix_left_asg_dev_key => 'p_',
1661 p_prefix_right_asg_dev_key => 'l_',
1662 p_use_aol_id_col => 'Y',
1663 p_fk_to_aol_columns_tbl => p_fk_to_aol_columns_tbl,
1664 p_resolve_pk_columns_tbl => g_resolve_pk_columns_tbl ) || ');' ;
1665 l_proc_index := l_proc_index + 1;
1666
1667 end if;
1668
1669 -- close the if statement.
1670 l_proc_body_tbl(l_proc_index) := indent(l_indent) || l_proc_body ||
1671 indent(l_indent) || 'end if;';
1672 l_proc_index := l_proc_index + 1;
1673 end if;
1674
1675 -- end if for the end of the non-SR code
1676 l_indent := 2;
1677 l_proc_body_tbl(l_proc_index) := format_comment('End of non-SR code.') ||
1678 indent(l_indent) || 'end if;';
1679 l_proc_index := l_proc_index + 1;
1680
1681
1682 l_proc_body_tbl(l_proc_index) := indent || 'end ' || l_proc_name || ';';
1683 l_proc_index := l_proc_index + 1;
1684
1685 l_proc_comment := format_comment('procedure to upload all columns of '
1686 || upper(p_table_info.upload_table_name) || ' from datapump interface table.')||
1687 indent;
1688
1689 -- add the procedure comment defination to the package header and body
1690 p_header := p_header || l_proc_comment ||'procedure ' || l_proc_name ||
1691 l_interface|| ';';
1692
1693 l_proc_body_tbl(1) := l_proc_comment || 'procedure ' || l_proc_name ||
1694 l_proc_body_tbl(1) || ' is';
1695
1696 -- add local variables , cursor and procedure body to complete the procedure
1697 l_proc_body_tbl(1) := l_proc_body_tbl(1) || l_locals || indent || 'begin'
1698 || indent ;
1699
1700 -- add the body of this procedure to the package.
1701 add_to_package_body( l_proc_body_tbl );
1702 hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.generate_upload',
1703 25);
1704 exception
1705 when others then
1706 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.generate_upload',
1707 '(none)','R');
1708 raise;
1709 end generate_upload;
1710 -- ----------------------- get_derive_from_clause -------------------------
1711 -- Description:
1712 -- Uses the derive_sql_source_tables info stored in HR_DM_TABLES to form the
1713 -- 'from clause'.
1714 -- The from clause stored in each derive field will be in the following format :
1715 -- table1 tbl,:table2 tbl2, :table3 tbl3
1716 -- where ':' is the next line indicator i.e : will be replaced with new line.
1717 -- o If 'from' string is not there it puts the from string.
1718 -- Input Parameters :
1719 -- p_table_info - Table information stored in pl/sql table
1720 -- p_derive_from - derive_sql string which stores the from clause
1721 -- p_lpad_spaces - padding
1722 -- Out Parameters
1723 -- p_from_clause - formatted from clause
1724 --
1725 -- ------------------------------------------------------------------------------
1726 procedure get_derive_from_clause
1727 (
1728 p_table_info in hr_dm_gen_main.t_table_info,
1729 p_derive_from in varchar2,
1730 p_from_clause in out nocopy varchar2,
1731 p_lpad_spaces in number default 2
1732 ) is
1733 l_derive_sql hr_dm_tables.derive_sql_download_full%type;
1734 l_start_ptr number;
1735 l_end_ptr number;
1736 l_terminator varchar2(5) := ';';
1737 begin
1738 hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.get_derive_from_clause', 5);
1739 hr_dm_utility.message('PARA','(p_from_clause - ' || p_from_clause ||
1740 ')(p_lpad_spaces - ' || p_lpad_spaces ||
1741 ')', 10);
1742 l_derive_sql := p_derive_from;
1743
1744 -- if 'where' string is not there then add the where string.
1745 if instr(lower(l_derive_sql),'from') <= 0 then
1746 p_from_clause := ' from ';
1747 end if;
1748
1749 l_end_ptr := instr(l_derive_sql,':') - 1;
1750 -- read the where clause string until first ':' . add the new line and chop
1751 -- the where clause string upto ':' character. Continue this process until
1752 -- full where clause is formatted.
1753 loop
1754
1755 p_from_clause := p_from_clause || substr(l_derive_sql,1,
1756 l_end_ptr) || indent(p_lpad_spaces + 5);
1757 -- remove the characters from where clause which have been appended in
1758 -- the where clause.
1759 l_derive_sql := substr(l_derive_sql,l_end_ptr + 2);
1760 --
1761 l_end_ptr := instr(l_derive_sql,':') - 1;
1762
1763 if l_end_ptr <= 0 or l_end_ptr is null then
1764 p_from_clause := p_from_clause || l_derive_sql;
1765 exit;
1766 end if;
1767 end loop;
1768
1769 hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.get_derive_from_clause',
1770 25);
1771 hr_dm_utility.message('PARA','(p_from_clause - ' || p_from_clause || ')',30);
1772 exception
1773 when others then
1774 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.get_derive_from_clause',
1775 '(l_derive_sql - ' || l_derive_sql ||
1776 ')(l_end_ptr - ' || l_end_ptr ||
1777 ')(p_from_clause - ' || p_from_clause || ')'
1778 ,'R');
1779 raise;
1780 end get_derive_from_clause;
1781 -- ----------------------- get_derive_where_clause -------------------------
1782 -- Description:
1783 -- Uses the derive_sql info stored in HR_DM_TABLES for each type of cursors.
1784 -- The where clause stored in each derive field will
1785 -- example derive where clause :
1786 -- where tbl.col1 = tbl2.col1 : and tbl.col2 = tbl1.col2 : and tbl.col3 = tbl3.col3
1787 -- Above derive where clause will be converted as
1788 -- where tbl.col1 = tbl2.col1
1789 -- and tbl.col2 = tbl1.col2
1790 -- and tbl.col3 = tbl3.col3
1791 -- by this procedure. replace ':' by newline feed.
1792 -- o may or may not include the 'where' string. If the 'where' string is not
1793 -- there this procedure will put the 'where' string
1794 -- o may or may not have the where clause terminator ';'. If it is not there
1795 -- it will add it.
1796 -- o the next line indicator will be ':' i.e : will be replaced with new line.
1797 -- Input Parameters :
1798 -- p_table_info - Table information stored in pl/sql table
1799 -- p_derive_sql - derive_sql string which stores the where clause
1800 -- p_lpad_spaces - padding
1801 -- Out Parameters
1802 -- p_where_clause - formatted where clause
1803 --
1804 -- ------------------------------------------------------------------------------
1805 procedure get_derive_where_clause
1806 (
1807 p_table_info in hr_dm_gen_main.t_table_info,
1808 p_derive_sql in varchar2,
1809 p_where_clause in out nocopy varchar2,
1810 p_lpad_spaces in number default 2
1811 ) is
1812 l_derive_sql hr_dm_tables.derive_sql_chk_row_exists%type;
1813 l_start_ptr number;
1814 l_end_ptr number;
1815 l_terminator varchar2(5) := ';';
1816 begin
1817 hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.get_derive_where_clause', 5);
1818
1819 l_derive_sql := p_table_info.derive_sql_chk_row_exists;
1820
1821 -- if terminator ';' is there in derive sql then set the terminator to null.
1822 if instr(l_derive_sql,';') > 0 then
1823 l_terminator := null;
1824 end if;
1825
1826 -- if 'where' string is not there then add the where string.
1827 if instr(lower(l_derive_sql),'where') <= 0 then
1828 p_where_clause := ' where ';
1829 end if;
1830
1831 l_end_ptr := instr(l_derive_sql,':') - 1;
1832 -- read the where clause string until first ':' . add the new line and chop
1833 -- the where clause string upto ':' character. Continue this process until
1834 -- full where clause is formatted.
1835 loop
1836
1837 p_where_clause := p_where_clause || substr(l_derive_sql,1,
1838 l_end_ptr) || indent(p_lpad_spaces);
1839 -- remove the characters from where clause which have been appended in
1840 -- the where clause.
1841 l_derive_sql := substr(l_derive_sql,l_end_ptr + 2);
1842 --
1843 l_end_ptr := instr(l_derive_sql,':') - 1;
1844 if l_end_ptr <= 0 or l_end_ptr is null then
1845 p_where_clause := p_where_clause || l_derive_sql;
1846 exit;
1847 end if;
1848 end loop;
1849
1850 p_where_clause := p_where_clause || l_terminator;
1851 hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.get_derive_where_clause',
1852 25);
1853 hr_dm_utility.message('PARA','(p_where_clause - ' || p_where_clause,30);
1854 exception
1855 when others then
1856 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.get_derive_where_clause',
1857 '(none)','R');
1858 raise;
1859 end get_derive_where_clause;
1860 -- ----------------------- prepare_chk_row_exists_cursor ---------------------
1861 -- Description:
1862 -- Preapre the cursor for chk_row_exists procedure
1863 -- ------------------------------------------------------------------------
1864 procedure prepare_chk_row_exists_cursor
1865 (
1866 p_table_info in hr_dm_gen_main.t_table_info,
1867 p_cursor out nocopy varchar2
1868 )
1869 is
1870 l_cursor_comment varchar2(2000);
1871 l_cursor_defination varchar2(2000);
1872 l_cursor_select_cols varchar2(32767);
1873 l_cursor_select_from varchar2(32767);
1874 l_cursor_select_where varchar2(32767);
1875
1876
1877 begin
1878 hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.prepare_chk_row_exists_cursor', 5);
1879
1880 -- comments about the cursor
1881 l_cursor_comment := format_comment('cursor to get the row from '||
1882 p_table_info.upload_table_name || 'for a given primary key',2);
1883
1884 -- defination of cursor
1885 l_cursor_defination := ' cursor csr_find_row_in_' ||p_table_info.alias ||
1886 ' is ';
1887
1888 -- select dummy column in the cursor as we just want to find that whether a
1889 -- row exist or not.
1890
1891 if p_table_info.surrogate_pk_column_name is not null then
1892 l_cursor_select_cols := ' select ' || p_table_info.surrogate_pk_column_name;
1893 else
1894 l_cursor_select_cols := ' select ''1''' ;
1895 end if;
1896
1897 -- get from clause. dtapump creates a view of the table to be uploaded.
1898 -- view is based on batch_lines table and TUPS load program parameters.
1899
1900 if p_table_info.derive_sql_chk_source_tables is not null then
1901 get_derive_from_clause ( p_table_info => p_table_info,
1902 p_derive_from => p_table_info.derive_sql_chk_source_tables,
1903 p_from_clause => l_cursor_select_from);
1904 else
1905 l_cursor_select_from := ' from ' || p_table_info.upload_table_name || ' ' || p_table_info.alias;
1906 end if;
1907
1908
1909 --
1910 -- Prepare where clause.
1911 --
1912
1913 if p_table_info.derive_sql_chk_row_exists is not null then
1914 get_derive_where_clause ( p_table_info => p_table_info,
1915 p_derive_sql => p_table_info.derive_sql_chk_row_exists,
1916 p_where_clause => l_cursor_select_where);
1917
1918 elsif p_table_info.use_non_pk_col_for_chk_row = 'Y' then
1919 l_cursor_select_where := g_chk_row_exists.where_clause ;
1920
1921 else -- use primary key for where clause
1922
1923 l_cursor_select_where := ' where' || hr_dm_library.get_func_asg (
1924 p_rpad_spaces => 0,
1925 p_columns_tbl => g_pk_columns_tbl,
1926 p_prefix_left_asg => p_table_info.alias || '.' ,
1927 p_prefix_right_asg => 'p_',
1928 p_omit_business_group_id => 'N',
1929 p_comma_on_first_line => 'N',
1930 p_pad_first_line => 'N',
1931 p_equality_sign => ' = ',
1932 p_left_asg_pad_len => 80,
1933 p_right_asg_pad_len => 80,
1934 p_start_terminator => ' and ',
1935 p_test_with_nvl => 'Y',
1936 p_table_name => upper(p_table_info.upload_table_name))
1937 ||';';
1938 end if;
1939
1940 -- finally put the components of where clause together
1941 p_cursor := l_cursor_comment || indent ||
1942 l_cursor_defination || indent ||
1943 l_cursor_select_cols || indent ||
1944 l_cursor_select_from || indent ||
1945 l_cursor_select_where || indent;
1946
1947 hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.prepare_chk_row_exists_cursor',
1948 25);
1949 exception
1950 when others then
1951 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.prepare_chk_row_exists_cursor',
1952 '(none)','R');
1953 raise;
1954 end prepare_chk_row_exists_cursor;
1955
1956 -- ----------------------- generate_chk_row_exists -----------------------
1957 -- Description:
1958 -- Generates the chk_row_exists procedure of the TUPS
1959 -- ------------------------------------------------------------------------
1960 procedure generate_chk_row_exists
1961 (
1962 p_table_info in hr_dm_gen_main.t_table_info
1963 )
1964 is
1965 l_interface varchar2(32767);
1966 l_locals varchar2(32767) := null;
1967 l_cursor varchar2(32767) := null;
1968 l_proc_comment varchar2(4000);
1969 l_cursor_name varchar2(30) := 'csr_find_row_in_' || p_table_info.alias;
1970
1971 -- block body of the procedure i.e between begin and end.
1972 l_proc_body varchar2(32767) := null;
1973
1974 -- indentation for the statements.it specifies number of blank spaces
1975 -- after which the staement should start.
1976
1977 l_indent number;
1978
1979 l_proc_body_tbl t_varchar2_32k_tbl;
1980 l_proc_index number := 1;
1981 l_operand varchar2(30);
1982 l_delete_operand varchar2(30);
1983 l_seperator varchar2(30);
1984 l_delete_seperator varchar2(30);
1985 --l_fk_on_uc_col varchar2(1);
1986 l_fetched_column_name varchar2(30);
1987 l_index number;
1988 l_nvl_left varchar2(100);
1989 l_nvl_right varchar2(100);
1990 l_apps_name varchar2(30);
1991
1992 l_status varchar2(50);
1993 l_industry varchar2(50);
1994 l_per_owner varchar2(30);
1995 l_ben_owner varchar2(30);
1996 l_pay_owner varchar2(30);
1997 l_ff_owner varchar2(30);
1998 l_fnd_owner varchar2(30);
1999
2000 l_ret1 boolean := FND_INSTALLATION.GET_APP_INFO ('PAY', l_status,
2001 l_industry, l_pay_owner);
2002 l_ret2 boolean := FND_INSTALLATION.GET_APP_INFO ('BEN', l_status,
2003 l_industry, l_ben_owner);
2004 l_ret3 boolean := FND_INSTALLATION.GET_APP_INFO ('FF', l_status,
2005 l_industry, l_ff_owner);
2006 l_ret4 boolean := FND_INSTALLATION.GET_APP_INFO ('FND', l_status,
2007 l_industry, l_fnd_owner);
2008 l_ret5 boolean := FND_INSTALLATION.GET_APP_INFO ('PER', l_status,
2009 l_industry, l_per_owner);
2010 cursor csr_apps_name is
2011 select ORACLE_USERNAME
2012 from fnd_oracle_userid
2013 where ORACLE_ID = 900;
2014
2015 --
2016 -- cursor to get the column and data type if non pk columns are
2017 -- used for row exists chk.
2018 --
2019 cursor csr_get_chk_columns is
2020 select lower(hir.column_name) column_name ,
2021 lower(col.data_type) data_type,
2022 hir.hierarchy_type,
2023 lower(dmt.table_alias) table_alias,
2024 tbl.table_name table_name
2025 from all_tab_columns col,
2026 hr_dm_tables tbl,
2027 hr_dm_tables dmt,
2028 hr_dm_hierarchies hir
2029 where tbl.table_name = nvl(dmt.upload_table_name, dmt.table_name)
2030 and dmt.table_id = p_table_info.table_id
2031 and hierarchy_type = 'R'
2032 and tbl.table_id = hir.table_id
2033 and col.table_name = tbl.table_name
2034 and col.column_name = hir.column_name
2035 and col.owner in
2036 (l_apps_name,
2037 l_fnd_owner,
2038 l_ff_owner,
2039 l_ben_owner,
2040 l_pay_owner,
2041 l_per_owner);
2042
2043 begin
2044 hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.generate_chk_row_exists', 5);
2045
2046 -- indentation required for call to chk_row_exists procedure in upload process
2047 -- will vary depending upon the table properties.
2048
2049 open csr_apps_name;
2050 fetch csr_apps_name into l_apps_name;
2051 close csr_apps_name;
2052
2053
2054 if p_table_info.datetrack = 'Y' then
2055 l_indent := 10;
2056 else
2057 if p_table_info.global_data = 'Y' then
2058 l_indent := 4;
2059 else
2060 l_indent := 4;
2061 end if;
2062 end if;
2063
2064 if p_table_info.surrogate_pk_column_name is not null then
2065 l_fetched_column_name := rpad('l_' || p_table_info.surrogate_pk_column_name,30);
2066 else
2067 l_fetched_column_name := 'l_dummy';
2068 end if;
2069
2070 --
2071 -- if non primary column have to be used to check whether row exist or not
2072 -- then the following are created and stored in the pl/sql record
2073 -- o where clause
2074 -- o call to the chk_row_exists procedure from upload procedure.
2075 -- o input parameters for chk_row_exists procedure
2076 --
2077 -- The columns which have 'L' or 'A' type hierarchy will use the local
2078 -- varaibles in the call to chk_row_exist procedure. Other columns will
2079 -- use the normal parameter i.t p_ column_name.
2080 --
2081
2082 if p_table_info.use_non_pk_col_for_chk_row = 'Y' then
2083
2084 g_chk_row_exists.where_clause := ' where ';
2085 g_chk_row_exists.call_to_proc := 'chk_row_exists (';
2086 g_chk_row_exists.proc_parameters := '(';
2087
2088 --
2089 -- the main difference is for date track table we the delete where clause
2090 -- will not contain the columns effective_start_date and effective_end_date.
2091 --
2092 g_delete_dml.where_clause := ' where ';
2093 g_delete_dml.call_to_proc := 'delete_dml (';
2094 g_delete_dml.proc_parameters := '(';
2095
2096 -- for the first fetched record the following var will be null
2097
2098 l_operand := '';
2099 l_delete_operand := '';
2100 l_seperator := '';
2101 l_delete_seperator := '';
2102
2103 for csr_get_chk_columns_rec in csr_get_chk_columns loop
2104
2105 -- if csr_get_chk_columns_rec.hierarchy_type = 'R' then
2106
2107 hr_dm_library.get_nvl_arguement('Y',
2108 csr_get_chk_columns_rec.table_name,
2109 csr_get_chk_columns_rec.column_name,
2110 l_nvl_left,
2111 l_nvl_right);
2112
2113 g_chk_row_exists.where_clause := g_chk_row_exists.where_clause ||
2114 l_operand || ' ' || l_nvl_left ||
2115 csr_get_chk_columns_rec.table_alias || '.' ||
2116 csr_get_chk_columns_rec.column_name || l_nvl_right || ' = ' ||
2117 rpad(l_nvl_left || 'p_' ||
2118 substr(csr_get_chk_columns_rec.column_name,1,28) || l_nvl_right,70)
2119 || indent(2);
2120
2121 l_operand := 'and ';
2122
2123 -- end if;
2124
2125 -- since we need to delete the logical record hence the EST , EDT columns
2126 -- needs to be removed from the where clause
2127
2128 if upper(csr_get_chk_columns_rec.column_name) not in ('EFFECTIVE_START_DATE',
2129 'EFFECTIVE_END_DATE')
2130 then
2131
2132 hr_dm_library.get_nvl_arguement('Y',
2133 csr_get_chk_columns_rec.table_name,
2134 csr_get_chk_columns_rec.column_name,
2135 l_nvl_left,
2136 l_nvl_right);
2137
2138 g_delete_dml.where_clause := g_delete_dml.where_clause ||
2139 l_delete_operand || ' ' || l_nvl_left ||
2140 csr_get_chk_columns_rec.table_alias || '.' ||
2141 substr(csr_get_chk_columns_rec.column_name,1,28) || l_nvl_right || ' = ' ||
2142 rpad(l_nvl_left ||'p_' ||
2143 csr_get_chk_columns_rec.column_name || l_nvl_right,80) || indent(2);
2144
2145
2146 g_delete_dml.proc_parameters := g_delete_dml.proc_parameters
2147 || l_delete_seperator || rpad('p_' || csr_get_chk_columns_rec.column_name,30) || ' ' ||
2148 csr_get_chk_columns_rec.data_type || indent;
2149
2150 l_delete_operand := 'and ';
2151
2152 end if;
2153
2154
2155 -- if the column has a 'L' type hierarchy then we want to use the
2156 -- the local variable in the call so as to use the correct value
2157 -- of the column i.e value of the column in the destination database.
2158
2159 hr_dm_library.check_col_for_fk_on_aol
2160 ( p_fk_to_aol_columns_tbl => g_resolve_pk_columns_tbl,
2161 p_column_name => upper(csr_get_chk_columns_rec.column_name),
2162 p_index => l_index);
2163
2164 -- if the column does not have a 'L' type hierarchy then check if table
2165 -- has a 'A' type hierarchy. If yes then check whether column has a foreign
2166 -- key on AOL table. The column should use the local variable in the call
2167 -- so as to use the correct value of the column i.e l_column_name instead
2168 -- of p_column_name.
2169
2170 if l_index is null and p_table_info.fk_to_aol_table = 'Y' then
2171 hr_dm_library.check_col_for_fk_on_aol
2172 ( p_fk_to_aol_columns_tbl => g_fk_to_aol_columns_tbl,
2173 p_column_name => upper(csr_get_chk_columns_rec.column_name),
2174 p_index => l_index);
2175 end if;
2176
2177 if l_index is null then
2178 g_chk_row_exists.call_to_proc := g_chk_row_exists.call_to_proc
2179 || l_seperator || rpad('p_' || csr_get_chk_columns_rec.column_name,30) ||
2180 indent (l_indent + 15);
2181
2182 if upper(csr_get_chk_columns_rec.column_name) not in ('EFFECTIVE_START_DATE',
2183 'EFFECTIVE_END_DATE')
2184 then
2185 g_delete_dml.call_to_proc := g_delete_dml.call_to_proc
2186 || l_delete_seperator || rpad('p_' || csr_get_chk_columns_rec.column_name,30) ||
2187 indent (l_indent + 15);
2188 l_delete_seperator := ',';
2189 end if;
2190 else
2191 g_chk_row_exists.call_to_proc := g_chk_row_exists.call_to_proc
2192 || l_seperator || rpad('l_' || csr_get_chk_columns_rec.column_name,30) ||
2193 indent (l_indent + 15);
2194 if upper(csr_get_chk_columns_rec.column_name) not in ('EFFECTIVE_START_DATE',
2195 'EFFECTIVE_END_DATE')
2196 then
2197 g_delete_dml.call_to_proc := g_delete_dml.call_to_proc
2198 || l_delete_seperator || rpad('l_' || csr_get_chk_columns_rec.column_name,30) ||
2199 indent (l_indent + 15);
2200 l_delete_seperator := ',';
2201 end if;
2202 end if;
2203
2204
2205 g_chk_row_exists.proc_parameters := g_chk_row_exists.proc_parameters
2206 || l_seperator || rpad('p_' || csr_get_chk_columns_rec.column_name,30) || ' ' ||
2207 csr_get_chk_columns_rec.data_type || indent;
2208 l_seperator := ',';
2209 end loop;
2210
2211 if p_table_info.ins_resolve_pk = 'Y' then
2212
2213 -- add surrogate id as one of the parameters to the call and interface
2214 -- of chk_row_exists procedure.
2215
2216 g_chk_row_exists.call_to_proc := g_chk_row_exists.call_to_proc
2217 || l_seperator || rpad('p_' || p_table_info.surrogate_pk_column_name,30) ||
2218 indent (l_indent + 15);
2219
2220 g_chk_row_exists.proc_parameters := g_chk_row_exists.proc_parameters
2221 || l_seperator || rpad('p_' || p_table_info.surrogate_pk_column_name,30) || ' ' ||
2222 'number' || indent;
2223 end if;
2224
2225 g_chk_row_exists.where_clause := g_chk_row_exists.where_clause || ';';
2226
2227 g_chk_row_exists.call_to_proc := g_chk_row_exists.call_to_proc ||
2228 ',l_row_exists);';
2229 g_chk_row_exists.proc_parameters := g_chk_row_exists.proc_parameters
2230 || ',p_row_exists in out nocopy varchar2)';
2231
2232 g_delete_dml.where_clause := g_delete_dml.where_clause || ';';
2233
2234 g_delete_dml.call_to_proc := g_delete_dml.call_to_proc || ');';
2235 g_delete_dml.proc_parameters := g_delete_dml.proc_parameters || ')';
2236
2237
2238 end if;
2239
2240
2241 if p_table_info.use_non_pk_col_for_chk_row = 'Y' then
2242
2243 l_interface := indent || g_chk_row_exists.proc_parameters || indent;
2244
2245 else -- use primary key for input parameters
2246
2247 l_interface := indent || '(' ||
2248 hr_dm_library.conv_list_to_text(
2249 p_rpad_spaces => 0,
2250 p_pad_first_line => 'N',
2251 p_columns_tbl => g_pk_parameters_tbl,
2252 p_col_length => 70) ||indent ||
2253 ',p_row_exists out nocopy varchar2)' || indent;
2254 end if;
2255
2256 -- local variables of the procedure
2257 l_locals := format_comment('Declare cursors and local variables',2) || indent ||
2258 ' l_proc varchar2(72) := g_package ' ||
2259 '|| ''chk_row_exists'';' || indent ||
2260 ' l_dummy varchar2(1);' || indent;
2261
2262 -- add the variable to store the surrogate id .
2263
2264 if p_table_info.surrogate_pk_column_name is not null then
2265 l_locals := l_locals || ' ' || l_fetched_column_name || ' number;'|| indent ;
2266 end if;
2267
2268 -- cursor to check whether row exists in destination database.
2269
2270 prepare_chk_row_exists_cursor (p_table_info,
2271 l_cursor);
2272
2273 -- add the body of the upload procedure
2274
2275 l_indent := 2;
2276
2277 l_proc_body := l_proc_body || indent(l_indent) ||
2278 'open ' || l_cursor_name || ';' || indent(l_indent) || 'fetch ' ||
2279 l_cursor_name || ' into ' || l_fetched_column_name || ';' || indent(l_indent) || 'if ' ||
2280 l_cursor_name || '%found then' || indent(l_indent + 2) ||
2281 'p_row_exists := ''Y'';' || indent(l_indent + 2) || 'close ' || l_cursor_name
2282 || ';' || indent(l_indent) || 'else ' ||
2283 'p_row_exists := ''N'';' || indent(l_indent + 2) || 'close ' || l_cursor_name
2284 || ';' || indent(l_indent) || 'end if;' ;
2285
2286 -- add the code to store the surrogate id in the hr_dm_pk_resolve table
2287 if p_table_info.ins_resolve_pk = 'Y' then
2288 l_proc_body := l_proc_body || indent(l_indent) || 'if p_row_exists = ''Y'' then' ||
2289 indent (l_indent +2) || 'hr_dm_library.ins_resolve_pks (' ||
2290 'p_table_name => ''' || upper(p_table_info.upload_table_name)
2291 || '''' || indent(l_indent+32) ||
2292 ', p_source_id => ' ||rpad('p_' || p_table_info.surrogate_pk_column_name,30)
2293 || indent(l_indent+32) ||
2294 ', p_destination_id => ' || l_fetched_column_name || ');'
2295 || indent(l_indent) || 'end if;';
2296
2297 end if;
2298
2299 l_proc_body := l_proc_body || indent || 'end chk_row_exists;';
2300
2301
2302 l_proc_comment := format_comment('procedure to check whether a row exist in '
2303 || upper(p_table_info.upload_table_name) || ' for a given primary key.')||
2304 indent;
2305
2306 -- add the procedure comment defination to the package header and body
2307
2308 l_proc_body_tbl(1) := l_proc_comment ||
2309 'procedure chk_row_exists' || l_interface || ' is';
2310
2311 -- add local variables , cursor and procedure body to complete the procedure
2312 l_proc_body_tbl(1) := l_proc_body_tbl(1) || l_locals || l_cursor || indent ||
2313 'begin' ||indent || l_proc_body;
2314
2315 -- add the body of this procedure to the package.
2316 add_to_package_body( l_proc_body_tbl );
2317 hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.generate_chk_row_exists',
2318 25);
2319 exception
2320 when others then
2321 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.generate_chk_row_exists',
2322 '(none)','R');
2323 raise;
2324 end generate_chk_row_exists;
2325
2326 -- ----------------------- generate_insert_dml ---------------------------
2327 -- Description:
2328 -- Generates the insert_dml procedure of the TUPS
2329 -- ------------------------------------------------------------------------
2330 procedure generate_insert_dml
2331 (
2332 p_table_info in hr_dm_gen_main.t_table_info
2333 )
2334 is
2335 l_interface varchar2(32767);
2336 l_locals varchar2(32767) := null;
2337 l_cursor varchar2(32767) := null;
2338 l_temp varchar2(32767);
2339 l_proc_comment varchar2(4000);
2340
2341 -- block body of the procedure i.e between begin and end.
2342 l_proc_body varchar2(32767) := null;
2343
2344 -- indentation for the statements.it specifies number of blank spaces
2345 -- after which the staement should start.
2346
2347 l_indent number := 2;
2348 l_proc_body_tbl t_varchar2_32k_tbl;
2349 l_proc_index number := 1;
2350 l_list_index number;
2351 begin
2352 hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.generate_insert_dml', 5);
2353
2354 -- input parameters for the procedure
2355
2356 l_proc_body_tbl(l_proc_index) := indent || '(' ||
2357 hr_dm_library.conv_list_to_text (
2358 p_rpad_spaces => l_indent + 2,
2359 p_pad_first_line => 'Y',
2360 p_columns_tbl => g_parameters_tbl,
2361 p_col_length => 70) || indent || ')';
2362 l_proc_index := l_proc_index + 1;
2363
2364 -- local variables of the procedure
2365 l_locals := format_comment('Declare cursors and local variables',2)
2366 || indent ||
2367 ' l_proc varchar2(72) := g_package ' ||
2368 '|| ''insert_dml'';' || indent;
2369
2370 -- if we need to check if the id is used
2371 if (p_table_info.sequence_name is not null) then
2372
2373 -- add l_dummy
2374 l_locals := l_locals ||
2375 ' l_dummy number;' || indent ||
2376 ' l_ins_type varchar2(1) := ''N'';' || indent ||
2377 ' l_row_exists varchar2(1) := ''N'';' || indent ||
2378 ' l_row_exists_dt varchar2(1) := ''N'';' || indent ||
2379 ' l_row_exists_cr varchar2(1) := ''N'';'
2380 || indent || indent;
2381
2382 -- add l_ style versions for p_ variables
2383 l_list_index := g_columns_tbl.first;
2384 while l_list_index is not null loop
2385 l_locals := l_locals || ' l_' || g_columns_tbl(l_list_index) ||
2386 ' ' || p_table_info.upload_table_name || '.' ||
2387 g_columns_tbl(l_list_index) ||
2388 '%type;' || indent;
2389 l_list_index := g_columns_tbl.next(l_list_index);
2390 end loop;
2391 l_locals := l_locals || indent;
2392
2393 -- if the table is an HR_DMV_% view then add a p_business_group_id
2394 -- local variable for the update where clause
2395 if (p_table_info.use_non_pk_col_for_chk_row = 'Y') and
2396 (upper(substr(p_table_info.table_name,1,7)) = 'HR_DMV_') then
2397 l_locals := l_locals || ' p_business_group_id number := NULL;'
2398 || indent;
2399 end if;
2400
2401 -- add cursor check if id value has already been used
2402 l_locals := l_locals ||
2403 ' cursor csr_check_if_used is' || indent(l_indent) ||
2404 ' select 1' || indent(l_indent) ||
2405 ' from ' || p_table_info.upload_table_name || indent(l_indent) ||
2406 ' where ' || p_table_info.surrogate_pk_column_name || ' = ' ||
2407 ' p_' || p_table_info.surrogate_pk_column_name || ';'
2408 || indent;
2409
2410 -- add cursor to get new value from sequence
2411 l_locals := l_locals ||
2412 ' cursor csr_get_new_id is'|| indent(l_indent) ||
2413 ' select ' || p_table_info.sequence_name ||
2414 '.nextval' || indent(l_indent) ||
2415 ' from dual;'
2416 || indent || indent;
2417 end if;
2418
2419 -- add the body of the download procedure
2420 l_indent := 2;
2421
2422 -- if we need to check if the id is used
2423 if (p_table_info.sequence_name is null) then
2424
2425 -- normal case
2426
2427 -- prepare insert dml
2428 l_proc_body_tbl(l_proc_index) :=
2429 format_comment('insert the row', l_indent) || indent ||
2430 ' insert into ' || p_table_info.upload_table_name || indent(l_indent)
2431 || '( ' || indent(l_indent) ||
2432 hr_dm_library.conv_list_to_text (
2433 p_rpad_spaces => l_indent + 1,
2434 p_pad_first_line => 'Y',
2435 p_columns_tbl => g_columns_tbl);
2436 l_proc_index := l_proc_index + 1;
2437
2438 l_proc_body_tbl(l_proc_index) := indent(l_indent) || ')' ||
2439 ' values' || indent(l_indent) || '(' || indent(l_indent) ||
2440 hr_dm_library.conv_list_to_text (
2441 p_rpad_spaces => l_indent+ 1,
2442 p_pad_first_line => 'Y',
2443 p_prefix_col => 'p_',
2444 p_columns_tbl => g_columns_tbl,
2445 p_col_length => 28) || indent(l_indent) ||
2446 ');' || indent || 'end insert_dml;';
2447 else
2448
2449 -- extra processing...
2450
2451 -- see if id is used
2452 l_proc_body_tbl(l_proc_index) :=
2453 format_comment('see if id value already used and no DT_DELETE' ||
2454 ' entry exists.', l_indent) || indent;
2455 l_proc_index := l_proc_index + 1;
2456
2457 -- call to procedure to check whether row exists in dt_delete table.
2458 prepare_chk_dt_delete_stmt
2459 (p_table_info => p_table_info,
2460 p_proc_body => l_temp,
2461 p_indent => l_indent);
2462 l_proc_body_tbl(l_proc_index) := l_temp || indent;
2463 l_proc_index := l_proc_index + 1;
2464
2465 l_proc_body_tbl(l_proc_index) := 'l_row_exists_dt := l_row_exists;' || indent;
2466 l_proc_index := l_proc_index + 1;
2467
2468 l_proc_body_tbl(l_proc_index) :=
2469 'open csr_check_if_used;' || indent ||
2470 'fetch csr_check_if_used into l_dummy;' || indent ||
2471 'close csr_check_if_used;' || indent || indent;
2472 l_proc_index := l_proc_index + 1;
2473
2474 -- copy p_ to l_
2475 l_list_index := g_columns_tbl.first;
2476 while l_list_index is not null loop
2477 l_proc_body_tbl(l_proc_index) :=
2478 'l_' || g_columns_tbl(l_list_index) || ' := p_' || g_columns_tbl(l_list_index) ||
2479 ';' || indent;
2480 l_proc_index := l_proc_index + 1;
2481 l_list_index := g_columns_tbl.next(l_list_index);
2482 end loop;
2483
2484
2485 if p_table_info.use_non_pk_col_for_chk_row = 'Y' then
2486 l_proc_body_tbl(l_proc_index) := indent(l_indent) ||
2487 g_chk_row_exists.call_to_proc;
2488 else
2489 l_proc_body_tbl(l_proc_index) := indent(l_indent) ||
2490 'chk_row_exists( ' ||
2491 hr_dm_library.conv_list_to_text(
2492 p_rpad_spaces => l_indent + 15,
2493 p_pad_first_line => 'N',
2494 p_prefix_col => 'p_',
2495 p_columns_tbl => g_pk_columns_tbl,
2496 p_col_length => 28) || indent(l_indent + 15) ||
2497 ',l_row_exists)' || ';';
2498 end if;
2499 l_proc_index := l_proc_index + 1;
2500
2501 l_proc_body_tbl(l_proc_index) := indent || 'l_row_exists_cr := l_row_exists;'
2502 || indent;
2503 l_proc_index := l_proc_index + 1;
2504
2505
2506 -- see if we have done inserted any previous rows from this record
2507 l_proc_body_tbl(l_proc_index) := 'if l_row_exists_dt = ''Y''' || indent ||
2508 ' and l_ins_type = ''D''' || indent ||
2509 ' and l_row_exists_cr =''Y'' then' ||
2510 indent(l_indent) || 'null;' ||
2511 indent || 'else' || indent;
2512 l_proc_index := l_proc_index + 1;
2513
2514 -- if id value used then get new sequence value and write details to resolve_pks
2515 -- otherwise keep original
2516 l_proc_body_tbl(l_proc_index) := indent(l_indent) ||
2517 'if (l_dummy is null) then' || indent(l_indent) ||
2518 ' null;' || indent(l_indent) ||
2519 'else' || indent || indent(l_indent) ||
2520 ' open csr_get_new_id;' || indent(l_indent) ||
2521 ' fetch csr_get_new_id into l_' ||
2522 p_table_info.surrogate_pk_column_name || ';' || indent(l_indent) ||
2523 ' close csr_get_new_id;' || indent;
2524
2525 l_proc_index := l_proc_index + 1;
2526
2527 -- write details to resolve_pks table
2528 l_proc_body_tbl(l_proc_index) :=
2529 ' hr_dm_library.ins_resolve_pks (' ||
2530 'p_table_name => ''' || upper(p_table_info.upload_table_name) || '''' ||
2531 indent(l_indent+30) ||
2532 ' ,p_source_id => ' || rpad('p_' || p_table_info.surrogate_pk_column_name,30)
2533 || indent(l_indent+30) ||
2534 ' ,p_destination_id => l_' || p_table_info.surrogate_pk_column_name || ' );'
2535 || indent(l_indent) ||
2536 'end if;' || indent;
2537 l_proc_index := l_proc_index + 1;
2538
2539 l_proc_body_tbl(l_proc_index) := 'end if;';
2540 l_proc_index := l_proc_index + 1;
2541
2542
2543 -- prepare insert dml
2544 -- need to change to use l_pk_id
2545 l_proc_body_tbl(l_proc_index) :=
2546 format_comment('insert the row', l_indent) || indent ||
2547 ' insert into ' || p_table_info.upload_table_name || indent(l_indent)
2548 || '( ' || indent(l_indent) ||
2549 hr_dm_library.conv_list_to_text (
2550 p_rpad_spaces => l_indent + 1,
2551 p_pad_first_line => 'Y',
2552 p_columns_tbl => g_columns_tbl);
2553 l_proc_index := l_proc_index + 1;
2554
2555 l_proc_body_tbl(l_proc_index) := indent(l_indent) || ')' ||
2556 ' values' || indent(l_indent) || '(' || indent(l_indent) ||
2557 hr_dm_library.conv_list_to_text (
2558 p_rpad_spaces => l_indent+ 1,
2559 p_pad_first_line => 'Y',
2560 p_prefix_col => 'l_',
2561 p_columns_tbl => g_columns_tbl,
2562 p_col_length => 28) || indent(l_indent) ||
2563 ');' || indent || 'end insert_dml;';
2564
2565
2566 end if;
2567
2568
2569 l_proc_comment := format_comment('procedure to insert a row in '
2570 || upper(p_table_info.upload_table_name) || 'table.')||
2571 indent;
2572
2573 -- add the procedure comment defination,local variables, cursor to the
2574 -- procedure body
2575
2576 l_proc_body_tbl(1) := l_proc_comment || 'procedure insert_dml' ||
2577 l_proc_body_tbl(1) || ' is' || l_locals || 'begin' ||indent;
2578
2579 -- add the body of this procedure to the package.
2580 add_to_package_body( l_proc_body_tbl );
2581 hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.generate_insert_dml',
2582 25);
2583 exception
2584 when others then
2585 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.generate_insert_dml',
2586 '(none)','R');
2587 raise;
2588 end generate_insert_dml;
2589 -- ----------------------- generate_update_dml ---------------------------
2590 -- Description:
2591 -- Generates the update_dml procedure of the TUPS
2592 -- ------------------------------------------------------------------------
2593 procedure generate_update_dml
2594 (
2595 p_table_info in hr_dm_gen_main.t_table_info
2596 )
2597 is
2598 l_interface varchar2(32767);
2599 l_locals varchar2(32767) := null;
2600 l_cursor varchar2(32767) := null;
2601 l_proc_comment varchar2(4000);
2602
2603 -- block body of the procedure i.e between begin and end.
2604 l_proc_body varchar2(32767) := null;
2605
2606 -- indentation for the statements.it specifies number of blank spaces
2607 -- after which the staement should start.
2608
2609 l_indent number;
2610 l_proc_body_tbl t_varchar2_32k_tbl;
2611 l_proc_index number := 1;
2612
2613 l_columns_tbl hr_dm_library.t_varchar2_tbl;
2614 begin
2615 hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.generate_update_dml', 5);
2616
2617 -- input parameters for the procedure
2618
2619 l_proc_body_tbl(l_proc_index) := indent || '(' ||
2620 hr_dm_library.conv_list_to_text (
2621 p_rpad_spaces => l_indent + 2,
2622 p_pad_first_line => 'Y',
2623 p_columns_tbl => g_parameters_tbl,
2624 p_col_length => 70) || indent || ')';
2625
2626 l_proc_index := l_proc_index + 1;
2627
2628 -- local variables of the procedure
2629 l_locals := format_comment('Declare cursors and local variables',2)
2630 || indent ||
2631 ' l_proc varchar2(72) := g_package ' ||
2632 '|| ''update_dml'';' || indent;
2633
2634 -- if the table is an HR_DMV_% view then add a p_business_group_id
2635 -- local variable for the update where clause
2636 if (p_table_info.use_non_pk_col_for_chk_row = 'Y') and
2637 (upper(substr(p_table_info.table_name,1,7)) = 'HR_DMV_') then
2638 l_locals := l_locals || ' p_business_group_id number := NULL;'
2639 || indent;
2640 end if;
2641
2642 -- add the body of the download procedure
2643 l_indent := 2;
2644
2645 -- get the list of columns of the table without primary key columns
2646 hr_dm_library.get_cols_list_wo_pk_cols (
2647 p_columns_tbl => g_columns_tbl,
2648 p_pk_columns_tbl => g_pk_columns_tbl ,
2649 p_cols_wo_pk_cols_tbl => l_columns_tbl );
2650
2651 -- if there are columns in the table other than primary key column i.e
2652 -- l_columns_tbl list is not null then create the update script, otherwise,
2653 -- create the dummy update procedure.
2654
2655 if l_columns_tbl.exists(1) then
2656
2657 -- This table contains the column other than primary key columns.
2658
2659 -- prepare update DML statement
2660
2661 l_proc_body_tbl(l_proc_index) := format_comment('update all columns of' ||
2662 ' the row.', l_indent) || indent ||
2663 ' update ' || p_table_info.upload_table_name || ' ' || p_table_info.alias
2664 || indent(l_indent) || 'set ' ||
2665 hr_dm_library.get_func_asg (
2666 p_rpad_spaces => l_indent + 4,
2667 p_columns_tbl => l_columns_tbl,
2668 p_prefix_left_asg => null ,
2669 p_omit_business_group_id => 'N',
2670 p_comma_on_first_line => 'N',
2671 p_pad_first_line => 'N',
2672 p_equality_sign => ' = ');
2673
2674 l_proc_index := l_proc_index + 1;
2675
2676 --
2677 -- if the table has 'R' type hierarchy then use the where clause same as
2678 -- used in the chk_row_exists cursor
2679 --
2680 if p_table_info.use_non_pk_col_for_chk_row = 'Y' then
2681 l_proc_body_tbl(l_proc_index) := indent(l_indent) ||
2682 g_chk_row_exists.where_clause ;
2683 else
2684 l_proc_body_tbl(l_proc_index) := indent(l_indent) ||
2685 ' where' || hr_dm_library.get_func_asg (
2686 p_rpad_spaces => 0,
2687 p_columns_tbl => g_pk_columns_tbl,
2688 p_prefix_left_asg => null ,
2689 p_prefix_right_asg => 'p_',
2690 p_omit_business_group_id => 'N',
2691 p_comma_on_first_line => 'N',
2692 p_pad_first_line => 'N',
2693 p_equality_sign => ' = ',
2694 p_start_terminator => ' and ') ||';';
2695 end if;
2696 else
2697 -- This table does not contains the column other than primary key columns.
2698 -- hence write a dummy procedure.
2699
2700 l_proc_body_tbl(l_proc_index) := format_comment('This table does not' ||
2701 ' contain any columns other than primary key column. Hence no update' ||
2702 ' statement is required.', l_indent) || indent(l_indent) || 'null;';
2703
2704 end if;
2705 l_proc_index := l_proc_index + 1;
2706 l_proc_body_tbl(l_proc_index) := indent || 'end update_dml;';
2707
2708
2709 l_proc_comment := format_comment('procedure to update a row in '
2710 || upper(p_table_info.upload_table_name) || ' table.') || indent;
2711
2712 -- add local variables , cursor and procedure body to complete the procedure
2713 l_proc_body_tbl(1) := l_proc_comment || 'procedure update_dml' ||
2714 l_proc_body_tbl(1) || ' is'|| l_locals || indent ||
2715 'begin' || indent;
2716
2717 -- add the body of this procedure to the package.
2718 add_to_package_body( l_proc_body_tbl );
2719 hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.generate_update_dml',
2720 25);
2721 exception
2722 when others then
2723 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.generate_update_dml',
2724 '(none)','R');
2725 raise;
2726 end generate_update_dml;
2727 -- ----------------------- generate_upload_hierarchy ---------------------------
2728 -- Description:
2729 -- Generates the update hierarchy procedure of the TUPS to update
2730 -- hierarchical columns of the table.
2731 -- For upload of hierarchy columns, we are assuming that there
2732 -- will be no H hierarchy columns where the same column will also
2733 -- have an A hierarchy.
2734 -- ------------------------------------------------------------------------
2735 procedure generate_upload_hierarchy
2736 (
2737 p_table_info in hr_dm_gen_main.t_table_info,
2738 p_header in out nocopy varchar2
2739 )
2740 is
2741 l_interface varchar2(32767);
2742 l_locals varchar2(32767) := null;
2743 l_cursor varchar2(32767) := null;
2744 l_proc_comment varchar2(4000);
2745 l_proc_name varchar2(30) := 'h' || p_table_info.short_name;
2746
2747 -- block body of the procedure i.e between begin and end.
2748 l_proc_body varchar2(32767) := null;
2749 l_resolve_pk_local_var varchar2(2000) := null;
2750 l_prefix_right_asg varchar2(10) := 'p_';
2751
2752 -- indentation for the statements.it specifies number of blank spaces
2753 -- after which the staement should start.
2754
2755 l_indent number;
2756 l_proc_body_tbl t_varchar2_32k_tbl;
2757 l_proc_index number := 1;
2758 begin
2759 hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.generate_upload_hierarchy', 5);
2760
2761 -- input parameters for the procedure
2762
2763 l_interface := indent ||'(p_last_migration_date in date' ||
2764 indent ||',p_migration_type in varchar2'
2765 || indent ||',';
2766
2767 -- add the column parameters to the procedure.
2768 l_interface := l_interface ||
2769 hr_dm_library.conv_list_to_text(
2770 p_rpad_spaces => 0,
2771 p_pad_first_line => 'N',
2772 p_columns_tbl => g_hier_parameters_tbl,
2773 p_col_length => 70) ||indent || ')' ;
2774
2775 l_proc_body_tbl(l_proc_index) := l_interface;
2776 l_proc_index := l_proc_index + 1;
2777
2778 -- local variables of the procedure
2779
2780 l_locals := format_comment('Declare cursors and local variables',2)
2781 || indent ||
2782 ' l_proc varchar2(72) := g_package ' ||
2783 '|| ''' || l_proc_name || ''';' || indent;
2784
2785 -- add the body of the download procedure
2786 l_indent := 2;
2787
2788 l_proc_body_tbl(l_proc_index) := indent(l_indent)||
2789 'if p_migration_type <> ''SR'' then ';
2790 l_proc_index := l_proc_index + 1;
2791
2792 l_indent := 4;
2793
2794 -- since the table has 'H' type hierarchy add the code to get the
2795 -- code to get the ID value of the column used in the destination database.
2796 -- It is done by getting the value from get_resolve_pk function.
2797
2798 if p_table_info.use_non_pk_col_for_chk_row = 'Y' then
2799 prepare_code_for_resolving_pk
2800 (p_pk_columns_tbl => g_pk_columns_tbl,
2801 p_table_info => p_table_info,
2802 p_call_to_proc_body => l_proc_body,
2803 p_local_var_body => l_resolve_pk_local_var
2804 );
2805
2806 l_locals := l_locals || l_resolve_pk_local_var || indent;
2807
2808 l_proc_body_tbl(l_proc_index) := l_proc_body;
2809 l_proc_index := l_proc_index + 1;
2810
2811 l_prefix_right_asg := 'l_';
2812
2813 end if;
2814
2815
2816
2817 l_proc_body_tbl(l_proc_index) :=
2818 indent||format_comment('update all columns of the row.',l_indent);
2819 l_proc_index := l_proc_index + 1;
2820
2821 -- update the row
2822 l_proc_body_tbl(l_proc_index) := indent(l_indent)||'update ' ||
2823 p_table_info.upload_table_name ||
2824 indent(l_indent)||'set ';
2825 l_proc_index := l_proc_index + 1;
2826
2827 l_proc_body_tbl(l_proc_index) :=
2828 hr_dm_library.get_func_asg (
2829 p_rpad_spaces => l_indent + 4,
2830 p_columns_tbl => g_hier_columns_tbl,
2831 p_prefix_left_asg => null ,
2832 p_prefix_right_asg => 'p_',
2833 p_omit_business_group_id => 'N',
2834 p_comma_on_first_line => 'N',
2835 p_pad_first_line => 'N',
2836 p_equality_sign => ' = ',
2837 p_left_asg_pad_len => 30,
2838 p_right_asg_pad_len => 30);
2839 l_proc_index := l_proc_index + 1;
2840
2841 l_proc_body_tbl(l_proc_index) := indent(l_indent)||' where'||
2842 hr_dm_library.get_func_asg (
2843 p_rpad_spaces => 0,
2844 p_columns_tbl => g_pk_columns_tbl,
2845 p_prefix_left_asg => null ,
2846 p_prefix_right_asg => l_prefix_right_asg,
2847 p_omit_business_group_id => 'N',
2848 p_comma_on_first_line => 'N',
2849 p_pad_first_line => 'N',
2850 p_equality_sign => ' = ',
2851 p_start_terminator => ' and ') ||';';
2852 l_proc_index := l_proc_index + 1;
2853
2854 l_indent := 2;
2855 l_proc_body_tbl(l_proc_index) := indent(l_indent)||'end if;'||indent || 'end ' || l_proc_name || ';';
2856
2857 l_proc_comment := format_comment('procedure to upload hierarchy columns of '
2858 || upper(p_table_info.upload_table_name) || ' from datapump interface table.')||
2859 indent;
2860
2861 -- add the procedure comment defination to the package header and body
2862 p_header := p_header || l_proc_comment ||'procedure ' || l_proc_name ||
2863 l_interface|| ';';
2864
2865 l_proc_body_tbl(1) := l_proc_comment || 'procedure ' || l_proc_name ||
2866 l_proc_body_tbl(1) || ' is';
2867
2868 -- add local variables , cursor and procedure body to complete the procedure
2869 l_proc_body_tbl(1) := l_proc_body_tbl(1) || l_locals || indent || 'begin'
2870 || indent ;
2871
2872 -- add the body of this procedure to the package.
2873 add_to_package_body( l_proc_body_tbl);
2874 hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.generate_upload_hierarchy',
2875 25);
2876 exception
2877 when others then
2878 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.generate_upload_hierarchy',
2879 '(none)','R');
2880 raise;
2881 end generate_upload_hierarchy;
2882 -- ----------------------- generate_delete_dml ---------------------------
2883 -- Description:
2884 -- Generates the delete_dml procedure of the TUPS
2885 -- ------------------------------------------------------------------------
2886 procedure generate_delete_dml
2887 (
2888 p_table_info in hr_dm_gen_main.t_table_info
2889 )
2890 is
2891 l_interface varchar2(32767);
2892 l_locals varchar2(32767) := null;
2893 l_cursor varchar2(32767) := null;
2894 l_proc_comment varchar2(4000);
2895
2896 -- block body of the procedure i.e between begin and end.
2897 l_proc_body varchar2(32767) := null;
2898
2899 -- indentation for the statements.it specifies number of blank spaces
2900 -- after which the staement should start.
2901
2902 l_indent number;
2903 l_proc_body_tbl t_varchar2_32k_tbl;
2904 begin
2905 hr_dm_utility.message('ROUT','entry:hr_dm_gen_tups.generate_delete_dml', 5);
2906
2907 -- input parameters for the procedure
2908
2909 if p_table_info.use_non_pk_col_for_chk_row = 'Y' then
2910
2911 l_interface := indent || g_delete_dml.proc_parameters || indent;
2912 else
2913 l_interface := indent || '(' ||
2914 hr_dm_library.conv_list_to_text(
2915 p_rpad_spaces => 0,
2916 p_pad_first_line => 'N',
2917 p_columns_tbl => g_pk_parameters_tbl,
2918 p_col_length => 70) || ') ' || indent;
2919 end if;
2920
2921 -- local variables of the procedure
2922
2923 l_locals := format_comment('Declare cursors and local variables',2)
2924 || indent ||
2925 ' l_proc varchar2(72) := g_package ' ||
2926 '|| ''delete_dml'';' || indent;
2927 -- add the body of the download procedure
2928 l_indent := 2;
2929
2930 l_proc_body := format_comment('delete the logical records for the given id.',
2931 l_indent) || indent;
2932
2933 if upper(p_table_info.upload_table_name) = 'FF_FORMULAS_F' then
2934 -- construct delete dml to delte the logical record
2935 l_proc_body := l_proc_body ||
2936 ' delete ff_compiled_info_f where formula_id in (
2937 select '||p_table_info.alias||'.formula_id
2938 from ff_formulas_f '||p_table_info.alias;
2939
2940 l_proc_body := l_proc_body || indent(l_indent) ||
2941 replace(g_delete_dml.where_clause,';','')||');'||indent(l_indent) ;
2942
2943 end if;
2944
2945 -- construct delete dml to delte the logical record
2946 l_proc_body := l_proc_body ||
2947 ' delete ' || p_table_info.upload_table_name || ' ' ||
2948 p_table_info.alias;
2949
2950 if p_table_info.use_non_pk_col_for_chk_row = 'Y' then
2951 l_proc_body := l_proc_body || indent(l_indent) ||
2952 g_delete_dml.where_clause ;
2953 else
2954 l_proc_body := l_proc_body || indent(l_indent) ||
2955 ' where' || hr_dm_library.get_func_asg (
2956 p_rpad_spaces => 0,
2957 p_columns_tbl => g_pk_columns_tbl,
2958 p_prefix_left_asg => p_table_info.alias || '.' ,
2959 p_prefix_right_asg => 'p_',
2960 p_omit_business_group_id => 'N',
2961 p_comma_on_first_line => 'N',
2962 p_pad_first_line => 'N',
2963 p_equality_sign => ' = ',
2964 p_left_asg_pad_len => 80,
2965 p_right_asg_pad_len => 80,
2966 p_test_with_nvl => 'Y',
2967 p_table_name => upper(p_table_info.upload_table_name),
2968 p_start_terminator => ' and ') ||';';
2969
2970 end if;
2971
2972 l_proc_body := l_proc_body || indent || 'end delete_dml;';
2973
2974
2975 l_proc_comment := format_comment('procedure to delete the logical record for'
2976 || ' a given id in '|| upper(p_table_info.upload_table_name) || ' table.')||
2977 indent;
2978
2979 -- add the procedure comment defination,local variables , cursor and
2980 -- procedure body
2981
2982 l_proc_body_tbl(1) := l_proc_comment || 'procedure delete_dml' ||
2983 l_interface || ' is' || l_locals || 'begin' ||
2984 indent || l_proc_body ;
2985
2986 -- add the body of this procedure to the package.
2987 add_to_package_body( l_proc_body_tbl );
2988 hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.generate_delete_dml',
2989 25);
2990 exception
2991 when others then
2992 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.generate_delete_dml',
2993 '(none)','R');
2994 raise;
2995 end generate_delete_dml;
2996
2997 -- ------------------------- create_tups_pacakge ------------------------
2998 -- Description: Create the TUPS package and relevant procedures for the table.
2999 -- Input Parameters :
3000 -- p_table_info - Information about table for which TUPS to be generated. Info
3001 -- like Datetrack, Global Data, Surrogate Primary key etc about
3002 -- the table is passed as a record type.
3003 -- p_columns_tbl - All the columns of the table stored as a list.
3004 -- p_parameters_tbl - All the columns of the table stored with data type are
3005 -- stored as a list. e.g p_business_group_id number
3006 -- This is used to create the procedure parameter list for
3007 -- TUPS procedure.
3008 -- p_aol_columns_tbl - All the columns of the table which have foreign key to
3009 -- AOL table are stored as a list.
3010 -- p_aol_parameters_tbl - All the columns of the table which have foreign key to
3011 -- AOL table are stored with data type as a list. This is
3012 -- used as a parameter list for the procedure generated to
3013 -- get the ID value for the given AOL developer key.
3014 -- e.g p_user_id number
3015 -- p_fk_to_aol_columns_tbl - It stores the list of all the columns which have
3016 -- foreign on AOL table and corresponding name of the AOL
3017 -- table.
3018 -- ------------------------------------------------------------------------
3019 procedure create_tups_pacakge
3020 (
3021 p_table_info in hr_dm_gen_main.t_table_info ,
3022 p_columns_tbl in hr_dm_library.t_varchar2_tbl,
3023 p_parameters_tbl in hr_dm_library.t_varchar2_tbl,
3024 p_aol_columns_tbl in hr_dm_library.t_varchar2_tbl,
3025 p_aol_parameters_tbl in hr_dm_library.t_varchar2_tbl,
3026 p_fk_to_aol_columns_tbl in hr_dm_gen_main.t_fk_to_aol_columns_tbl
3027 )
3028 is
3029 l_header varchar2(32767);
3030 l_body varchar2(32767);
3031 l_header_comment varchar2(2048);
3032 l_package_name varchar2(30) := 'hrdmu_' || p_table_info.short_name;
3033 l_generator_version hr_dm_tables.generator_version%type;
3034 l_package_version varchar2(200);
3035 l_index number := 1;
3036 l_call_to_aol_proc varchar2(32767);
3037 l_dev_key_local_var varchar2(32767);
3038 l_csr_sql integer;
3039 l_rows number;
3040 l_proc_body_tbl t_varchar2_32k_tbl;
3041 l_proc_index number := 1;
3042 begin
3043 g_table_info := p_table_info;
3044 g_columns_tbl := p_columns_tbl;
3045 g_parameters_tbl := p_parameters_tbl;
3046 g_aol_columns_tbl := p_aol_columns_tbl;
3047 g_aol_parameters_tbl := p_aol_parameters_tbl;
3048 g_fk_to_aol_columns_tbl := p_fk_to_aol_columns_tbl;
3049 g_surrogate_pk_col_param := 'p_' ||
3050 rpad(p_table_info.surrogate_pk_column_name,28);
3051
3052 -- inialize the global package body pl/sql table by deleting all elements.
3053 init_package_body;
3054
3055 -- Get the version of the generator to be appended to the TUPS package
3056 -- generated for a table. This will help in finding out which version
3057 -- of Generator is used to generate the TUPS package.
3058
3059 hr_dm_library.get_generator_version(p_generator_version => l_generator_version,
3060 p_format_output => 'Y');
3061
3062 -- Get the package version of this TDS package body.
3063 hr_dm_library.get_package_version ( p_package_name => 'HR_DM_GEN_TUPS',
3064 p_package_version => l_package_version,
3065 p_version_type => 'FULL');
3066
3067 -- if there is a column hirearchy then store the hierarchy columns list and
3068 -- parameter assignment in pl/sql variable.
3069
3070 if p_table_info.column_hierarchy = 'Y' then
3071 -- get the columns and parameter list. store in pl/sql table.
3072 hr_dm_library.populate_hierarchy_cols_list
3073 (p_table_info => p_table_info,
3074 p_hier_columns_tbl => g_hier_columns_tbl,
3075 p_hier_parameter_tbl => g_hier_parameters_tbl,
3076 p_called_from => 'TUPS' );
3077
3078 end if;
3079
3080 -- if one or more columns have to resolve primary key then store the
3081 -- information about the column and corresponding AOL table e.t.c for each
3082 -- column.
3083 if p_table_info.resolve_pk = 'Y' then
3084 hr_dm_library.populate_fk_to_aol_cols_info
3085 ( p_table_info => p_table_info,
3086 p_fk_to_aol_columns_tbl => g_resolve_pk_columns_tbl,
3087 p_hierarchy_type => 'L');
3088 end if;
3089
3090
3091 -- populate the list with primary key columns.
3092 hr_dm_library.populate_pk_columns_list(p_table_info,
3093 g_pk_columns_tbl,
3094 g_pk_parameters_tbl,
3095 g_no_of_pk_columns);
3096
3097 -- Start the package header and body.
3098 begin
3099 --
3100 -- Set up initial parts of the package header and body.
3101 --
3102 l_header_comment := l_package_version || indent || '/*' || indent ||
3103 ' * Generated by hr_dm_gen_tups at: ' ||
3104 to_char( sysdate, 'YYYY/MM/DD HH24:MM:SS' ) || indent ||
3105 ' * Generated Data Migrator TUPS for : ' || p_table_info.upload_table_name || '.' ||
3106 indent ||
3107 ' * Generator Version Used to generate this TUPS is : ' || indent ||
3108 l_generator_version || indent ||
3109 ' */' || indent || '--' || indent;
3110
3111 l_header :=
3112 'create or replace package ' || l_package_name || ' as' || indent ||
3113 l_header_comment || indent;
3114
3115 -- add in call to hr_dm_upload.set_globals to set the global variables
3116 l_header := l_header || indent || '--' || indent ||
3117 '-- call to hr_dm_upload.set_globals to set the global variables'
3118 || indent || '--' || indent ||
3119 'g_temp_var NUMBER := hr_dm_upload.set_globals;' || indent;
3120
3121
3122 l_proc_body_tbl(1) :=
3123 'create or replace package body ' || l_package_name || ' as' || indent ||
3124 l_header_comment;
3125
3126 -- private package variable
3127 l_proc_body_tbl(1) := l_proc_body_tbl(1) || indent || '--' || indent ||
3128 '-- Package Variables' || indent ||
3129 '--' || indent ||
3130 'g_package varchar2(33) := ''' || l_package_name || ''';' ||
3131 indent;
3132
3133 -- add the body of this procedure to the package.
3134 add_to_package_body( l_proc_body_tbl );
3135
3136 if p_table_info.datetrack = 'Y' then
3137
3138 prepare_glob_var_def_for_dt (p_table_info,
3139 l_header);
3140 end if;
3141
3142 -- if the table has a columns which have a foreign key to AOL table then
3143 -- generate the procedures so as to create the procedures to get the
3144 -- corresponding developer's key for those columns.
3145
3146 if p_table_info.fk_to_aol_table = 'Y' then
3147 generate_get_id_frm_dev_key
3148 ( p_fk_to_aol_columns_tbl => p_fk_to_aol_columns_tbl,
3149 p_table_info => p_table_info,
3150 p_body => l_body,
3151 p_call_to_proc_body => l_call_to_aol_proc,
3152 p_dev_key_local_var_body => l_dev_key_local_var);
3153 end if;
3154 --
3155 -- Generate the procedures and functions.
3156 --
3157
3158 -- generate chk_row_exists procedure to download data into batch_lines.
3159
3160 l_body := l_body || indent || '--' || indent;
3161
3162 -- generate chk_row_exist procedure.
3163 generate_chk_row_exists(p_table_info);
3164
3165 -- generate insert_dml procedure to insert the data into table.
3166 l_body := l_body || indent || '--' || indent;
3167 generate_insert_dml (p_table_info);
3168
3169 -- For non date track table generate update_dml procedure to update the
3170 -- row data. For date track create delete_dml procedure to delete the
3171 -- data.
3172
3173 l_body := l_body || indent || '--' || indent;
3174
3175 if p_table_info.datetrack = 'N' then
3176 generate_update_dml (p_table_info );
3177 else
3178 generate_delete_dml (p_table_info);
3179 end if;
3180
3181 -- if the table has a column hierarchy then create a procedure to update
3182 -- hierarchy columns.
3183 if p_table_info.column_hierarchy = 'Y' then
3184 generate_upload_hierarchy
3185 (p_table_info => p_table_info,
3186 p_header => l_header);
3187 end if;
3188
3189 -- generate upload procedure to upload data into batch_lines.
3190
3191 l_body := l_body || indent || '--' || indent;
3192 generate_upload(p_table_info => p_table_info,
3193 p_header => l_header,
3194 p_body => l_body,
3195 p_call_to_proc_body => l_call_to_aol_proc,
3196 p_dev_key_local_var_body => l_dev_key_local_var,
3197 p_fk_to_aol_columns_tbl => p_fk_to_aol_columns_tbl);
3198
3199
3200 l_header := l_header || indent || '--' || indent;
3201 l_body := l_body || indent || '--' || indent;
3202
3203 --
3204 -- Terminate the package body and header.
3205 --
3206 l_header := l_header || 'end ' || l_package_name || ';';
3207 l_body := l_body || 'end ' || l_package_name || ';';
3208 exception
3209 when plsql_value_error then
3210 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.create_tds_pacakge',
3211 'Either TDS package code size is too big or ' ||
3212 ' a value error)',
3213 'R');
3214 raise;
3215 end;
3216
3217 --
3218 -- Compile the header and body.
3219 --
3220
3221 hr_dm_library.run_sql( l_header );
3222
3223 g_package_index := g_package_index+1;
3224 g_package_body(g_package_index ) := indent || 'end ' ||
3225 l_package_name || ';';
3226
3227 hr_dm_library.run_sql( g_package_body,
3228 g_package_index);
3229
3230 -- check the status of the package
3231 begin
3232 hr_dm_library.check_compile (p_object_name => l_package_name,
3233 p_object_type => 'PACKAGE BODY' );
3234 exception
3235 when others then
3236 hr_dm_utility.error(SQLCODE,'Error in compiling TUPS for ' ||
3237 p_table_info.table_name ,'(none)','R');
3238 raise;
3239 end;
3240
3241 hr_dm_utility.message('ROUT','exit:hr_dm_gen_tups.create_tups_pacakge',
3242 25);
3243 exception
3244 when others then
3245 hr_dm_utility.error(SQLCODE,'hr_dm_gen_tups.create_tups_pacakge',
3246 '(none)','R');
3247 raise;
3248 end create_tups_pacakge ;
3249
3250 end hr_dm_gen_tups;
[Home] [Help]