[Home] [Help]
PACKAGE BODY: APPS.BEN_DM_GEN_DOWNLOAD
Source
1 package body BEN_DM_GEN_DOWNLOAD as
2 /* $Header: benfdmgndn.pkb 120.0 2006/05/04 04:48:57 nkkrishn noship $ */
3
4 g_package varchar2(100) := 'ben_dm_gen_download.' ;
5 type t_varchar2_32k_tbl is table of varchar2(32767) index by binary_integer;
6
7 -- to store the package body in to array so as to overcome the limit of 32767
8 -- character the global variable is defined.
9 g_package_body dbms_sql.varchar2s;
10 g_package_index number := 0;
11 g_columns_tbl hr_dm_library.t_varchar2_tbl;
12 --
13 -- Exception for generated text exceeding the maximum allowable buffer size.
14 --
15 plsql_value_error exception;
16 pragma exception_init(plsql_value_error, -6502);
17
18 -- ----------------------- indent -----------------------------------------
19 -- Description:
20 -- returns the 'n' blank spaces on a newline.used to indent the procedure
21 -- statements.
22 -- if newline parameter is 'Y' then start the indentation from new line.
23 -- ------------------------------------------------------------------------
24
25 function indent
26 (
27 p_indent_spaces in number default 0,
28 p_newline in varchar2 default 'Y'
29 ) return varchar2 is
30 l_spaces varchar2(100);
31 l_proc varchar2(100);
32 begin
33 l_proc := g_package||'indent' ;
34
35 l_spaces := hr_dm_library.indent(p_indent_spaces => p_indent_spaces,
36 p_newline => p_newline);
37 return l_spaces;
38 exception
39 when others then
40 ben_dm_utility.error(SQLCODE,l_proc ,
41 '(p_indent_spaces - ' || p_indent_spaces ||
42 ')(p_newline - ' || p_newline || ')',
43 'R');
44 end indent;
45
46
47 --------------------- init_package_body----------------------------------------
48 -- This package will delete all the elements from the package body pl/sql table.
49 -------------------------------------------------------------------------------
50 procedure init_package_body is
51 l_index number := g_package_body.first;
52 l_proc varchar2(75) ;
53 begin
54 l_proc := g_package|| 'init_package_body' ;
55 hr_utility.set_location('Entering : ' || l_proc , 5 ) ;
56
57 ben_dm_utility.message('ROUT','entry: '|| l_proc , 5);
58
59 -- delete all elements from package body pl/sql table.
60 while l_index is not null loop
61 g_package_body.delete(l_index);
62 l_index := g_package_body.next(l_index);
63 end loop;
64 --initialize the index
65 g_package_index := 0;
66 ben_dm_utility.message('ROUT','exit :' || l_proc , 25);
67 hr_utility.set_location('Leaving : ' || l_proc , 10 ) ;
68 exception
69 when others then
70 ben_dm_utility.error(SQLCODE,l_proc, '(none)','R');
71 raise;
72 end init_package_body;
73
74
75 -- ----------------------- format_comment ---------------------------------
76 -- Description:
77 -- formats the comments to be written into the procedure body
78 -- e.g comment string ' This is a example comment text' will be converted t
79 -- --
80 -- -- This is a example comment text.
81 -- --
82 -- ------------------------------------------------------------------------
83
84 function format_comment
85 (
86 p_comment_text in varchar2,
87 p_indent_spaces in number default 0,
88 p_ins_blank_lines in varchar2 default 'Y'
89 ) return varchar2 is
90
91 l_comment_text varchar2(20000);
92 l_comment_length number := length(p_comment_text);
93
94 --
95 -- maximum chracters for single comment text line ensuring the single
96 -- comment line cannot be more than 77 characters long excluding 3
97 -- characters ('-- ') at the begning of comment.
98 --
99
100 l_max_comment_line_len number := 77 - p_indent_spaces;
101 l_comment_line_len number;
102 l_comment_line_txt varchar2(80);
103
104 -- start and end pointer of comment line to be copied from comment text.
105 l_start_ptr number := 1;
106 l_end_ptr number;
107
108 -- used for wrapping
109 l_last_space_ptr number;
110 begin
111
112 if p_ins_blank_lines = 'Y' then
113 l_comment_text := indent(p_indent_spaces) || '--';
114 end if;
115
116 loop
117 l_end_ptr := l_start_ptr + l_max_comment_line_len - 1;
118
119 l_comment_line_txt := substr(p_comment_text,l_start_ptr,(l_end_ptr - l_start_ptr + 1));
120
121 l_comment_line_len := length(l_comment_line_txt);
122
123 -- comment line is less than the maximum text that come then it is ok,
124 -- otherwise do word wrapping.If the next character is a space there is
125 -- no need for wrapping
126
127 if l_comment_line_len >= l_max_comment_line_len and
128 substr(p_comment_text,l_end_ptr + 1,1) <> ' '
129 then
130
131 -- this function ensures the wrapping of the word. last word will come
132 -- either full or move to the next line.This gives the position of the
133 -- last space in the comment line text.
134
135 l_last_space_ptr := instr(l_comment_line_txt,' ',-1);
136
137 -- adjust the end pointer as we want to copy the string upto the last
138 -- space only, the remaining word should go into next line.
139
140 l_end_ptr := l_end_ptr - (length(l_comment_line_txt) - l_last_space_ptr);
141
142 end if;
143
144 -- now the end_ptr gives the length of the comment line that can be copied
145 -- with a space in the end.
146
147 l_comment_text := l_comment_text || indent(p_indent_spaces) || '-- ';
148
149
150 l_comment_text := l_comment_text || substr(p_comment_text,l_start_ptr,
151 (l_end_ptr - l_start_ptr + 1));
152
153 l_start_ptr := l_end_ptr +1;
154
155 if l_start_ptr > l_comment_length then
156 exit;
157 end if;
158 end loop;
159
160
161 if p_ins_blank_lines = 'Y' then
162 l_comment_text := l_comment_text || indent(p_indent_spaces) || '--';
163 end if;
164 return l_comment_text;
165 exception
166 when others then
167 ben_dm_utility.error(SQLCODE,'hr_dm_gen_tds.format_comment',
168 '(p_ins_blank_lines - ' || p_ins_blank_lines ||
169 ')(p_indent_spaces - ' || p_indent_spaces ||
170 ')(p_comment_text - ' || p_comment_text || ')'
171 ,'R');
172 end format_comment;
173
174
175
176
177 -- -----------------------add_to_package_body; ---------------------------------
178 -- Description:
179 -- This procedure will be called by each procedure to be created by TUPS.
180 -- Each procedure will be stored in the array of varchar2(32767).
181 -- The input to this procedure is pl/sql table i.e array of string.
182 -- Now the task of this procedure is to split the above array elements into
183 -- array elements of size 256. This is required so as to the package body
184 -- of more than 32 K size can be parsed using dbms_sql procedure.
185 --
186 -- ------------------------------------------------------------------------
187
188 procedure add_to_package_body
189 (
190 p_proc_body_tbl t_varchar2_32k_tbl
191 ) is
192
193 l_proc_index number := p_proc_body_tbl.first;
194 l_string_index number; -- variable to read the string characters
195 l_loop_cnt number;
196 begin
197
198 ben_dm_utility.message('ROUT','entry:hr_dm_gen_tds.add_to_package_body-1', 5);
199 ben_dm_utility.message('PARA','(p_proc_body_tbl - table of varchar2' ,10);
200
201 while l_proc_index is not null loop
202
203 l_string_index := 1;
204 l_loop_cnt := 1;
205 -- read the string of the procedure body and chop it into the array element
206 -- size of 256 and store it into the global package body. Each looping will
207 -- will read the 256 characters from the procedure body and it will go on
208 -- until no more characters to read.
209 loop
210 if substr(p_proc_body_tbl(l_proc_index),l_string_index,256) is null
211 then
212 exit;
213 end if;
214 g_package_index := g_package_index + 1;
215
216 -- add the procedure body to
217 g_package_body (g_package_index) :=
218 substr(p_proc_body_tbl(l_proc_index),
219 l_string_index ,256);
220 l_string_index := 256*l_loop_cnt + 1;
221 l_loop_cnt := l_loop_cnt + 1;
222 end loop;
223 l_proc_index := p_proc_body_tbl.next(l_proc_index);
224 end loop;
225 ben_dm_utility.message('INFO',
226 '(l_loop_cnt - ' || l_loop_cnt ||
227 ')(l_string_index - ' ||l_string_index ||
228 ')( g_package_index - ' || g_package_index || ')'
229 ,15);
230 ben_dm_utility.message('ROUT','exit:hr_dm_gen_tds.add_to_package_body -1',
231 25);
232 exception
233 when others then
234 ben_dm_utility.error(SQLCODE,'hr_dm_gen_tds.add_to_package_body-1',
235 '(l_loop_cnt - ' || l_loop_cnt ||
236 ')(l_string_index - ' ||l_string_index ||
237 ')( g_package_index - ' || g_package_index || ')'
238 ,'R');
239 raise;
240 end add_to_package_body;
241 -- ----------------------- get_derive_from_clause -------------------------
242 -- Description:
243 -- Uses the derive_sql_source_tables info stored in HR_DM_TABLES to form the
244 -- 'from clause'.
245 -- The from clause stored in each derive field will be in the following format :
246 -- table1 tbl,:table2 tbl2, :table3 tbl3
247 -- where ':' is the next line indicator i.e : will be replaced with new line.
248 -- o If 'from' string is not there it puts the from string.
249 -- ------------------------------------------------------------------------------
250 procedure get_derive_from_clause
251 (
252 p_table_info in t_ben_dm_table,
253 p_from_clause in out nocopy varchar2,
254 p_lpad_spaces in number default 2
255 ) is
256 l_derive_sql ben_dm_tables.derive_sql%type;
257 l_start_ptr number;
258 l_end_ptr number;
259 l_where_string varchar2(25) := 'where';
260 l_terminator varchar2(5) := ';';
261 l_proc varchar2(75) ;
262 begin
263 l_proc := g_package||'get_derive_from_clause' ;
264
265 hr_utility.set_location('Entering '|| l_proc, 5) ;
266
267 ben_dm_utility.message('ROUT','entry: '|| l_proc , 5);
268 ben_dm_utility.message('PARA','(p_from_clause - ' || p_from_clause ||
269 ')(p_lpad_spaces - ' || p_lpad_spaces ||
270 ')', 10);
271
272 l_derive_sql := p_table_info.derive_sql;
273
274
275 -- if 'where' string is not there then add the where string.
276 if instr(lower(l_derive_sql),'from') <= 0 then
277 p_from_clause := ' from ';
278 end if;
279
280 l_end_ptr := instr(l_derive_sql,':') - 1;
281 -- read the where clause string until first ':' . add the new line and chop
282 -- the where clause string upto ':' character. Continue this process until
283 -- full where clause is formatted.
284 loop
285
286 p_from_clause := p_from_clause || substr(l_derive_sql,1,
287 l_end_ptr) || indent(p_lpad_spaces + 5);
288 -- remove the characters from where clause which have been appended in
289 -- the where clause.
290 l_derive_sql := substr(l_derive_sql,l_end_ptr + 2);
291 --
292 l_end_ptr := instr(l_derive_sql,':') - 1;
293
294 if l_end_ptr <= 0 or l_end_ptr is null then
295 p_from_clause := p_from_clause || l_derive_sql;
296 exit;
297 end if;
298 end loop;
299
300 ben_dm_utility.message('ROUT','exit: ' || l_proc , 25);
301 ben_dm_utility.message('PARA','(p_from_clause - ' || p_from_clause || ')',30);
302 hr_utility.set_location('Leaving '|| l_proc, 5) ;
303 exception
304 when others then
305 ben_dm_utility.error(SQLCODE,'hr_dm_gen_tds.get_derive_from_clause',
306 '(l_derive_sql - ' || l_derive_sql ||
307 ')(l_end_ptr - ' || l_end_ptr ||
308 ')(p_from_clause - ' || p_from_clause || ')'
309 ,'R');
310 raise;
311 end get_derive_from_clause;
312 ---
313
314
315 -- ------------------------------------------------------------------------
316 procedure get_dt_cursor_where_clause
317 (
318 p_table_info in t_ben_dm_table,
319 p_where_clause out nocopy varchar2,
320 p_lpad_spaces in number default 2
321 ) is
322
323 l_start_ptr number;
324 l_end_ptr number;
325 l_where_string varchar2(25) := 'where';
326 l_terminator varchar2(5) := ';';
327 l_derive_sql ben_dm_tables.DERIVE_SQL%type ;
328 l_proc varchar2(75);
329
330
331 begin
332
333 l_proc := g_package|| 'get_dt_cursor_where_clause' ;
334 hr_utility.set_location(' Entering '|| l_proc, 5 ) ;
335
336 ben_dm_utility.message('ROUT','entry:' || l_proc , 5);
337
338 l_derive_sql := p_table_info.derive_sql ;
339 if l_derive_sql is not null then
340
341
342 -- if terminator ';' is there in derive sql then set the terminator to null.
343 if instr(l_derive_sql,';') > 0 then
344 l_terminator := null;
345 end if;
346
347 -- if 'where' string is not there then add the where string.
348 if instr(lower(l_derive_sql),'where') <= 0 then
349 p_where_clause := ' where ';
350 end if;
351
352 l_end_ptr := instr(l_derive_sql,':') - 1;
353 loop
354
355 p_where_clause := p_where_clause || substr(l_derive_sql,1,
356 l_end_ptr) || indent(p_lpad_spaces);
357 -- remove the characters from where clause which have been appended in
358 -- the where clause.
362 if l_end_ptr <= 0 or l_end_ptr is null then
359 l_derive_sql := substr(l_derive_sql,l_end_ptr + 2);
360 --
361 l_end_ptr := instr(l_derive_sql,':') - 1;
363 p_where_clause := p_where_clause || l_derive_sql;
364 exit;
365 end if;
366 end loop;
367
368 p_where_clause := p_where_clause || l_terminator;
369
370 return;
371 end if;
372 p_where_clause := lpad(' ',p_lpad_spaces) || ' ';
373
374
375 ben_dm_utility.message('ROUT','exit:' || l_proc , 25);
376 hr_utility.set_location('Leaving '|| l_proc, 10 ) ;
377 exception
378 when others then
379 ben_dm_utility.error(SQLCODE,'hr_dm_gen_tds.get_dt_cursor_where_clause',
380 '(none)','R');
381 raise;
382 end get_dt_cursor_where_clause;
383
384
385 -- ----------------------- prepare_download_cursor --------------------------------
386 -- ------------------------------------------------------------------------
387 procedure prepare_download_cursor
388 (
389 p_table_info in t_ben_dm_table,
390 p_cursor out nocopy varchar2
391 --p_person_id in number
392 )
393 is
394 l_cursor_comment varchar2(2000);
395 l_cursor_defination varchar2(2000);
396 l_cursor_select_cols varchar2(32767);
397 l_cursor_select_from varchar2(32767);
398 l_cursor_select_where varchar2(32767);
399 l_columns_tbl hr_dm_library.t_varchar2_tbl;
400 l_prefix_col varchar2(30);
401 l_proc varchar2(75) ;
402 begin
403 l_proc := g_package || 'prepare_download_cursor' ;
404 hr_utility.set_location('Entering '|| l_proc, 5 ) ;
405 ben_dm_utility.message('ROUT','entry: ' || l_proc , 5);
406
407
408
409 l_columns_tbl := g_columns_tbl;
410
411
412 -- comments about the cursor
413 l_cursor_comment := indent || '--' || indent || '-- cursor to select the data from the ' ||
414 p_table_info.table_name || ' table to be migrated.'|| indent || '--';
415
416 l_cursor_comment := format_comment(' cursor to select the data from the '
417 || p_table_info.table_name ,2 );
418 l_cursor_defination := ' cursor csr_ben_mig' || p_table_info.table_alias || ' is ';
419 --
420 -- for normal main query the column name will be alias.col1,alias.col2 but
421 -- for sub query it will be alias1.col1,alias1.col2
422 --
423 l_prefix_col := p_table_info.table_alias || '1.';
424
425
426 -- select all the column from the table
427 if p_table_info.datetrack = 'Y' then
428 l_cursor_select_cols := ' Select distinct ' || p_table_info.table_alias || '.*' ;
429 else
430 l_cursor_select_cols := ' Select ' || p_table_info.table_alias || '.*' ;
431 end if;
432
433
434 --l_cursor_select_from := ' from ' || p_table_info.table_name || ' ' || p_table_info.table_alias ;
435
436 -- get where clause for date track
437 get_dt_cursor_where_clause (p_table_info => p_table_info,
438 p_where_clause => l_cursor_select_where);
439
440
441
442 -- finally put the components of where clause together
443 p_cursor := l_cursor_comment || indent ||
444 l_cursor_defination || indent ||
445 l_cursor_select_cols || indent ||
446 /* l_cursor_select_from || indent || */
447 l_cursor_select_where || indent;
448
449 ben_dm_utility.message('ROUT','exit:'|| l_proc , 25);
450 ben_dm_utility.message('PARA','(p_cursor - ' || p_cursor || ')' ,30);
451 hr_utility.set_location('Leaving '|| l_proc, 5 ) ;
452 exception
453 when others then
454 ben_dm_utility.error(SQLCODE,'hr_dm_gen_tds.prepare_download_cursor',
455 '(none)','R');
456 raise;
457 end prepare_download_cursor;
458
459
460
461
462 -- ----------------------- generate_download --------------------------------
463 -- Description:
464 -- Generates the download procedure of the TDS
465 -- ------------------------------------------------------------------------
466 procedure generate_download
467 (
468 p_table_info in t_ben_dm_table,
469 p_migration_id in number,
470 --p_group_order in number,
471 --p_person_id in number,
472 --p_business_group_name in varchar2,
473 p_header in out nocopy varchar2
474 )
475 is
476 l_interface varchar2(32767);
477 l_locals varchar2(32767) := null;
478 l_cursor varchar2(32767) := null;
479 l_adt_cursor varchar2(32767) := null;
480 l_comment varchar2(4000);
481 l_proc_comment varchar2(4000);
482 l_cursor_name varchar2(30) := 'csr_ben_mig' || p_table_info.table_alias;
483 l_func_name varchar2(30) := 'Download';
484 l_dp_func_name varchar2(100);
485 l_null_col varchar2(30);
486
487
488 -- block body of the procedure i.e between begin and end.
489 l_proc_body varchar2(32767) := null;
490 l_debug_message_text varchar2(32767) := null;
491 l_func_body varchar2(32767) := null;
492
493 -- block body to store private procedures
494 l_prv_proc_body varchar2(32767) := null;
495
496 -- indentation for the statements.it specifies number of blank spaces
497 -- after which the staement should start.
498
499 l_indent number;
500 l_columns_tbl hr_dm_library.t_varchar2_tbl;
501
502 l_proc_body_tbl t_varchar2_32k_tbl;
503 l_proc_index number := 1;
504
505
506
510 ,bdm.parent_table_name
507 cursor c_pk_hier (c_tbl_id number) is
508 select bdt.table_alias
509 ,bdm.column_name
511 ,bdm.parent_column_name
512 ,bdm.parent_id_column_name
513 from ben_dm_hierarchies bdm , ben_dm_tables bdt
514 where bdm.HIERARCHY_TYPE = 'S'
515 and bdm.table_id = c_tbl_id
516 and bdm.parent_table_name = bdt.table_name
517 ;
518
519 cursor c_cols_map (c_tbl_id number , c_table_name varchar2) is
520 select a.column_name ,
521 a.entity_result_column_name
522 from ben_dm_column_mappings a ,
523 sys.all_tab_columns b
524 where a.table_id = c_tbl_id
525 and a.column_name = b.column_name
526 and b.table_name = c_table_name
527 order by a.entity_result_column_name
528 ;
529
530
531 l_proc varchar2(75) ;
532 begin
533 l_proc := g_package||'generate_download' ;
534
535 hr_utility.set_location('Entering '|| l_proc , 5 ) ;
536 ben_dm_utility.message('ROUT','entry:' || l_proc , 5);
537
538 l_columns_tbl := g_columns_tbl;
539 l_func_name := 'download';
540
541 -- input parameters for the procedure
542
543 l_interface := indent ||
544 '(p_migration_id in number,' || indent ||
545 ' p_business_group_id in number,' || indent ||
546 ' p_business_group_name in varchar2,' || indent ||
547 ' p_person_id in number,' || indent ||
548 ' p_group_order in number,' || indent ||
549 ' p_rec_downloaded out nocopy number'|| indent||
550 ' )' || indent;
551
552
553 l_proc_body_tbl(l_proc_index) := l_interface;
554 l_proc_index := l_proc_index + 1;
555
556
557
558 -- call prepare_down_load procedure to create the cursor.
559 prepare_download_cursor ( p_table_info => p_table_info,
560 p_cursor => l_cursor
561 -- p_person_id => p_person_id
562 );
563 -- local variables of the procedure
564
565 l_locals := indent ||
566 ' -- Declare local variables' || indent ||
567 ' l_proc varchar2(72) := g_package ' ||
568 '|| ''' || l_func_name || ''' ;' || indent ||
569 ' l_link_value number;' || indent ||
570 ' l_rec_inserted_cnt number := 0;' || indent ||
571 ' l_result_id number ;' || indent ||
572 ' l_return_fk_id number ;' || indent ||
573 ' l_row_fetched boolean := FALSE;' || indent;
574 --end if;
575
576 -- message (' l_cursor = ' || l_cursor);
577 -- add the body of the download procedure
578
579 l_indent := 3;
580 l_proc_body_tbl(l_proc_index) := indent(l_indent) ||
581 'l_rec_inserted_cnt := 0;' || indent(l_indent) || indent(l_indent) ||
582 'hr_utility.set_location(''Entering : '' || l_proc,5) ; ' || indent(l_indent) ||
583 'ben_dm_utility.message(''ROUT'','' Entering '' ||l_proc,5) ; ' || indent(l_indent) ||
584 'ben_dm_utility.message(''PARA'', '' ( Person - '' || p_person_id || '')'' , 10) ;'|| indent(l_indent) ||
585 'ben_dm_utility.message(''PARA'', '' ( Source BG - '' || p_business_group_id || '')'' , 10) ;'|| indent(l_indent) ||
586 'ben_dm_utility.message(''PARA'', '' ( Target BG - '' || p_business_group_name || '')'' , 10) ;'|| indent(l_indent)||
587 'ben_dm_utility.message(''PARA'', '' ( group_order - '' || p_group_order || '')'' , 10) ; '|| indent(l_indent) ;
588
589 l_proc_index := l_proc_index + 1;
590 /*
591 add_debug_messages (p_table_info => p_table_info,
592 p_procedure_name => l_func_name,
593 p_message_location => 'START',
594 p_proc_body => l_debug_message_text);
595 */
596 l_proc_body_tbl(l_proc_index) := l_debug_message_text;
597 l_proc_index := l_proc_index + 1;
598 --l_indent := 4;
599
600 -- open the cursor in for loop so u dont have to check found
601 l_proc_body_tbl(l_proc_index) := indent(l_indent) ||
602 'For l_table_rec in ' || l_cursor_name || indent(l_indent) || 'loop';
603 l_proc_index := l_proc_index + 1;
604 l_indent := 6;
605
606 l_proc_body := indent(l_indent) || '--Call procedure to download all Mapping Key of ' ||
607 upper(p_table_info.table_name) || indent(l_indent) ;
608
609 -- download the mapping keys
610 for l_pk_rec in c_pk_hier(p_table_info.table_id ) Loop
611
612 l_proc_body := l_proc_body||indent(l_indent) || '--' || 'Get the Key for '||l_pk_rec.parent_table_name ||'.'||
613 l_pk_rec.parent_column_name || indent(l_indent) ;
614 l_proc_body := l_proc_body|| 'If l_table_rec.'||l_pk_rec.column_name||' IS NOT NULL THEN '||indent(l_indent+3);
615 l_proc_body := l_proc_body|| 'ben_dm_download_dk.get_dk_frm_'||l_pk_rec.table_alias||' ( '
616 || indent(l_indent+3) ;
617 l_proc_body := l_proc_body||'p_business_group_name => p_business_group_name , ' || indent(l_indent+3);
618 l_proc_body := l_proc_body||'p_resolve_mapping_id => l_return_fk_id , ' || indent(l_indent+3);
619 l_proc_body := l_proc_body||'p_source_id => l_table_rec.'||l_pk_rec.column_name||indent(l_indent+3);
620 l_proc_body := l_proc_body|| ');' || indent(l_indent) ;
621 l_proc_body := l_proc_body|| 'End If ; '||indent(l_indent) ;
622 End Loop ;
623 -- end of call to get the FK values
624
625 l_proc_body_tbl(l_proc_index) := l_proc_body;
626 l_proc_index := l_proc_index + 1;
627
628
629 --- call the procedure to upload the values into result table
633 l_indent := 10;
630 l_proc_body := indent ||
631 indent||indent(l_indent)||
632 '-- Insert the values into result entity table ' || indent(l_indent) ;
634 l_proc_body := l_proc_body|| 'ben_dm_data_util.create_entity_result(' || indent(l_indent) ;
635 l_proc_body := l_proc_body|| ' p_entity_result_id => l_result_id ' || indent(l_indent) ;
636 l_proc_body := l_proc_body|| ',p_migration_id => p_migration_id ' || indent(l_indent) ;
637 l_proc_body := l_proc_body|| ',p_table_name => '||'''' ||p_table_info.table_name||''''|| indent(l_indent) ;
638 l_proc_body := l_proc_body|| ', p_group_order => p_group_order '|| indent(l_indent) ;
639 for l_cols_map in c_cols_map(p_table_info.table_id, p_table_info.table_name ) Loop
640 l_proc_body := l_proc_body|| ',p_'||rpad(l_cols_map.ENTITY_RESULT_COLUMN_NAME,30 )|| ' => l_table_rec.'||
641 l_cols_map.COLUMN_NAME || indent(l_indent);
642 end Loop ;
643 l_proc_body := l_proc_body|| ');' || indent(l_indent) ;
644 l_proc_body := l_proc_body|| 'l_rec_inserted_cnt := l_rec_inserted_cnt + 1 ; ' ;
645 l_proc_body_tbl(l_proc_index) := l_proc_body;
646 l_proc_index := l_proc_index + 1;
647 l_proc_body := null ;
648
649 l_indent := 3;
650 l_proc_body_tbl(l_proc_index) := indent(l_indent) ||
651 'end loop;' || indent(l_indent ) /*|| 'commit;' */ || indent(l_indent ) ||
652 'p_rec_downloaded := l_rec_inserted_cnt;' || indent(l_indent) ||
653 'ben_dm_utility.message(''INFO'','' Record Inserterd '' ||l_rec_inserted_cnt,5) ; '
654 || indent(l_indent) ;
655 l_proc_index := l_proc_index + 1;
656
657 --l_indent := 0;
658 l_debug_message_text := indent(l_indent) || 'hr_utility.set_location(''Leaving : '' || l_proc,5) ; '||
659 indent(l_indent) ||' ben_dm_utility.message(''ROUT'','' Exit '' ||l_proc,5) ; ' ||
660 indent || 'End ' || l_func_name || ' ; ' ;
661 l_proc_body_tbl(l_proc_index) := l_debug_message_text;
662 l_proc_index := l_proc_index + 1;
663
664
665 -- add the procedure comment defination to the package header and body
666 p_header := p_header || l_proc_comment ||'procedure ' || l_func_name ||
667 l_proc_body_tbl(1) || ';';
668 l_proc_body_tbl(1) := l_proc_comment || 'procedure ' || l_func_name ||
669 l_proc_body_tbl(1) || 'is';
670
671 -- add local variables , cursor and procedure body to complete the procedure
672 l_proc_body_tbl(1) := l_proc_body_tbl(1) || l_cursor || /* l_adt_cursor || */
673 l_locals || l_prv_proc_body || indent ||'begin' || indent ;
674
675 -- add the body of this procedure to the package.
676 add_to_package_body( l_proc_body_tbl );
677
678 ben_dm_utility.message('ROUT','exit:'||l_proc, 25);
679 hr_utility.set_location('Leaving '|| l_proc , 5 ) ;
680 exception
681 when others then
682 ben_dm_utility.error(SQLCODE,l_proc, '(none)','R');
683 raise;
684 end generate_download;
685
686
687 -- ------------------------- main ------------------------
688 -- Description: Create the TDS package and relevant procedures for the table.
689 --
690 -- Input Parameters :
691 -- p_table_info - Information about table for which TDS to be generated. Info
692 -- like Datetrack, Global Data, Surrogate Primary key etc about
693 -- the table is passed as a record type.
694 -- p_columns_tbl - All the columns of the table stored as a list.
695 -- p_parameters_tbl - All the columns of the table stored with data type are
696 -- stored as a list. e.g p_business_group_id number
697 -- This is used to create the procedure parameter list for
698 -- TDS procedure.
699 -- p_aol_columns_tbl - All the columns of the table which have foreign key to
700 -- AOL table are stored as a list.
701 -- p_aol_parameters_tbl - All the columns of the table which have foreign key to
702 -- AOL table are stored with data type as a list. This is
703 -- used as a parameter list for the procedure generated to
704 -- get the AOL developer key for the given ID value
705 -- e.g p_user_id number
706 -- p_fk_to_aol_columns_tbl - It stores the list of all the columns which have
707 -- foreign on AOL table and corresponding name of the AOL
708 -- table.
709 -- ------------------------------------------------------------------------
710 procedure main
711 (
712 --p_business_group_id in number,
713 -- p_person_id in number,
714 -- p_group_order in number,
715 -- p_business_group_name in varchar2,
716 p_table_alias in varchar2,
717 p_migration_id in number
718 )
719 is
720 l_header varchar2(32767);
721 l_body varchar2(32767);
722 l_header_comment varchar2(2048);
723 l_package_name varchar2(30) ;
724 l_generator_version hr_dm_tables.generator_version%type;
725 l_package_version varchar2(200);
726 l_index number := 1;
727 l_proc_body_tbl t_varchar2_32k_tbl;
728 l_proc_index number := 1;
729 l_proc varchar2(75) ;
730
731
732 cursor c_tbl is
733 select TABLE_ID
734 ,TABLE_NAME
735 ,UPLOAD_TABLE_NAME
736 ,TABLE_ALIAS
737 ,DATETRACK
738 ,DERIVE_SQL
739 ,SURROGATE_PK_COLUMN_NAME
740 ,SHORT_NAME
741 ,LAST_GENERATED_DATE
742 ,GENERATOR_VERSION
743 ,SEQUENCE_NAME
744 ,LAST_UPDATE_DATE
745 from ben_dm_tables
746 where table_alias = p_table_alias
747 ;
748 l_tbl_rec t_ben_dm_table ;
752
749
750
751
753 begin
754
755 l_proc := g_package || 'main' ;
756 hr_utility.set_location('Entering:'||l_proc, 5);
757
758 ben_dm_utility.message('ROUT','entry:'||l_proc , 5);
759
760
761 -- opne the tabl curso and get the table informatons
762 open c_tbl ;
763 fetch c_tbl into l_tbl_rec ;
764 if c_tbl%NotFound then
765 close c_tbl ;
766 --raise ;
767 end if ;
768 close c_tbl ;
769
770
771 ben_dm_utility.message('PARA','(Table Name - '||l_tbl_rec.table_name|| ')', 10);
772
773 l_package_name := 'ben_dmd' || lower(l_tbl_rec.short_name );
774 -- inialize the global package body pl/sql table by deleting all elements.
775 init_package_body;
776
777 -- Get the version of the generator to be appended to the TDS package
778 -- generated for a table. This will help in finding out which version
779 -- of Generator is used to generate the TDS package.
780
781 ben_dm_data_util.get_generator_version(p_generator_version => l_generator_version,
782 p_format_output => 'Y');
783
784 -- Get the package version of this TDS package body.
785 hr_dm_library.get_package_version ( p_package_name => 'BEN_DM_GEN_DOENLOAD',
786 p_package_version => l_package_version,
787 p_version_type => 'FULL');
788
789
790
791 -- Start the package header and body.
792 begin
793 --
794 -- Set up initial parts of the package header and body.
795 --
796 l_header_comment := l_package_version || indent || '/*' || indent ||
797 ' * Generated by ' || l_proc ||': '||to_char(sysdate,'YYYY/MM/DD HH24:MM:SS')||indent||
798 ' * Generated Person Migrator TDS for : ' || l_tbl_rec.table_name || '.'||indent ||
799 ' * Generator Version Used to generate this TDS is : ' || indent ||
800 l_generator_version || indent ||
801 ' */' || indent || '--' || indent;
802
803 l_header :=
804 'create or replace package ' || l_package_name||' as'||indent||l_header_comment ||
805 'g_generator_version constant varchar2(128) default ' ||
806 '''$Revision: 120.0 $'';' || indent || '--' || indent;
807
808 l_proc_body_tbl(1) :=
809 'create or replace package body ' || l_package_name || ' as' || indent ||
810 l_header_comment;
811
812 -- private package variable
813 l_proc_body_tbl(1) := l_proc_body_tbl(1) || indent || '--' ||
814 indent || '-- Package Variables' || indent ||
815 '--' || indent ||
816 'g_package varchar2(50) := ''' || l_package_name || '.'';' ||
817 indent;
818
819 -- add the body of this procedure to the package.
820 add_to_package_body( l_proc_body_tbl );
821 --
822 -- Generate the procedures and functions.
823 --
824
825
826
827 -- if the table has a columns which have a foreign key table then
828 -- generate call in body to get the value in bn_dm_column_maping table
829 -- th following look create code to call the proceurde get_dk_frm_<alias>
830 -- corresponding developer's key for those columns.
831
832 -- l_body := l_body || indent || '--' || indent;
833
834
835
836 -- down load procedure to download all columns.
837 generate_download(
838 p_table_info => l_tbl_rec,
839 p_migration_id => p_migration_id,
840 --p_group_order => p_group_order,
841 --p_person_id => p_person_id,
842 --p_business_group_name => p_business_group_name,
843 p_header => l_header ) ;
844
845 l_header := l_header || indent || '--' || indent;
846 l_header := l_header || indent || '--' || indent;
847 l_header := l_header || 'end ' || l_package_name || ';';
848 -- l_body := l_body || 'end ' || l_package_name || ';';
849 exception
850 when plsql_value_error then
851 ben_dm_utility.error(SQLCODE,'hr_dm_gen_tds.create_tds_pacakge',
852 'Either TDS package code size is too big or ' ||
853 ' a value error)',
854 'R');
855 raise;
856 end;
857
858 g_package_index := g_package_index+1;
859 g_package_body(g_package_index ) := indent || 'end ' ||
860 l_package_name || ';';
861
862 hr_utility.set_location('PACKAGE BODY :'||l_package_name, 5);
863 --
864 -- Compile the header and body.
865 --
866
867 hr_dm_library.run_sql( l_header );
868 hr_dm_library.run_sql( g_package_body,
869 g_package_index);
870
871
872 -- check the status of the package
873 hr_utility.set_location('PACKAGE BODY :'||l_package_name, 5);
874 -- check the status of the package
875 begin
876 hr_dm_library.check_compile (p_object_name => l_package_name,
877 p_object_type => 'PACKAGE BODY' );
878 exception
879 when others then
880 ben_dm_utility.error(SQLCODE,'Error in compiling TDS for ' ||
881 l_tbl_rec.table_name ,'(none)','R');
882 raise;
883 end;
884
885 ben_dm_utility.message('ROUT','exit:'|| l_proc , 25);
886 hr_utility.set_location('Leaving:'||l_proc, 10);
887 exception
888 when others then
889 ben_dm_utility.error(SQLCODE,'hr_dm_gen_tds.create_tds_pacakge ',
890 '(none)','R');
891 raise;
892 end main ;
893
894 end BEN_DM_GEN_DOWNLOAD;