[Home] [Help]
PACKAGE BODY: APPS.BEN_DM_GEN_SELF_REF
Source
1 package body BEN_DM_GEN_SELF_REF as
2 /* $Header: benfdmgnsr.pkb 120.0 2006/06/13 14:58:07 nkkrishn noship $ */
3
4
5 --
6 -- Exception for generated text exceeding the maximum allowable buffer size.
7 --
8 g_package_body dbms_sql.varchar2s;
9 g_package_index number := 0;
10 type t_varchar2_32k_tbl is table of varchar2(32767) index by binary_integer;
11 g_package varchar2(75) := 'ben_dm_gen_self_ref.' ;
12 plsql_value_error exception;
13 pragma exception_init(plsql_value_error, -6502);
14
15
16
17 -- indent call download indent to avoid the duplicate codes
18 -- indent created here to maintain the samm name in the codes
19 function indent
20 (
21 p_indent_spaces in number default 0,
22 p_newline in varchar2 default 'Y'
23 ) return varchar2 is
24 l_spaces varchar2(100);
25 begin
26
27 l_spaces := ben_dm_gen_download.indent(p_indent_spaces => p_indent_spaces,
28 p_newline => p_newline ) ;
29 return l_spaces;
30 end indent;
31
32
33 --------------------- init_package_body----------------------------------------
34 -- This package will delete all the elements from the package body pl/sql table.
35 -------------------------------------------------------------------------------
36 procedure init_package_body is
37 l_index number := g_package_body.first;
38 l_proc varchar2(75) ;
39 begin
40 l_proc := g_package || 'init_package_body' ;
41 hr_utility.set_location('Entering:'||l_proc, 5);
42 ben_dm_utility.message('ROUT','entry:'|| l_proc , 5);
43 -- delete all elements from package body pl/sql table.
44 while l_index is not null loop
45 g_package_body.delete(l_index);
46 l_index := g_package_body.next(l_index);
47 end loop;
48
49
50
51 --initialize the index
52 g_package_index := 0;
53
54
55 ben_dm_utility.message('ROUT','exit:' || l_proc , 25);
56 hr_utility.set_location('Leaving:'||l_proc, 10);
57 exception
58 when others then
59 ben_dm_utility.error(SQLCODE,'hr_dm_gen_tups.init_package_body',
60 '(none)','R');
61 raise;
62 end init_package_body;
63
64 -- -----------------------add_to_package_body; ---------------------------------
65 -- Description:
66 -- This procedure will be called by each procedure to be created by TUPS.
67 -- Each procedure will be stored in the array of varchar2(32767).
68 -- Now the task of this procedure is to split the above array elements into
69 -- array elements of size 256. This is required so as to the package body
70 -- of more than 32 K size can be parsed using dbms_sql procedure.
71 --
72 -- ------------------------------------------------------------------------
73
74 procedure add_to_package_body
75 (
76 p_proc_body_tbl t_varchar2_32k_tbl
77 ) is
78
79 l_proc_index number := p_proc_body_tbl.first;
80 l_string_index number; -- variable to read the string characters
81 l_loop_cnt number;
82 l_proc varchar2(75) ;
83 begin
84
85 l_proc := g_package||'add_to_package_body' ;
86 hr_utility.set_location('Entering:'||l_proc, 5);
87
88 ben_dm_utility.message('ROUT','entry:' || l_proc , 5);
89 ben_dm_utility.message('PARA','(p_proc_body_tbl - table of varchar2',10);
90
91 while l_proc_index is not null loop
92
93 l_string_index := 1;
94 l_loop_cnt := 1;
95 -- read the string of the procedure body and chop it into the array element
96 -- size of 256 and store it into the global package body. Each looping will
97 -- will read the 256 characters from the procedure body and it will go on
98 -- until no more characters to read.
99 loop
100 if substr(p_proc_body_tbl(l_proc_index),l_string_index,256) is null
101 then
102 exit;
103 end if;
104 g_package_index := g_package_index + 1;
105
106 -- add the procedure body to
107 g_package_body (g_package_index) :=
108 substr(p_proc_body_tbl(l_proc_index),
109 l_string_index ,256);
110 l_string_index := 256*l_loop_cnt + 1;
111 l_loop_cnt := l_loop_cnt + 1;
112 end loop;
113
114 l_proc_index := p_proc_body_tbl.next(l_proc_index);
115 end loop;
116 ben_dm_utility.message('INFO',
117 '(l_loop_cnt - ' || l_loop_cnt ||
118 ')(l_string_index - ' ||l_string_index ||
119 ')( g_package_index - ' || g_package_index || ')'
120 ,15);
121 ben_dm_utility.message('ROUT','exit:'|| l_proc , 25);
122 hr_utility.set_location('Leaving:'||l_proc, 5);
123 exception
124 when others then
125 ben_dm_utility.error(SQLCODE,l_proc,
126 '(l_loop_cnt - ' || l_loop_cnt ||
127 ')(l_string_index - ' ||l_string_index ||
128 ')( g_package_index - ' || g_package_index || ')'
129 ,'R');
130 raise;
131 end add_to_package_body;
132
133
134
135 -- ----------------------- generate_reference --------------------------------
136 -- Description:
137 -- Generates the upload procedure of the upload
138 -- ------------------------------------------------------------------------
139 procedure generate_reference
140 (
141 p_header in out nocopy varchar2,
142 p_body in out nocopy varchar2
143 )
144 is
145 l_interface varchar2(32767);
146 l_locals varchar2(32767) := null;
147 l_cursor varchar2(32767) := null;
148 l_comment varchar2(4000);
149 l_proc_comment varchar2(4000);
150 l_cursor_name varchar2(30) := 'csr_self_ref' ;
151
152 -- block body of the procedure i.e between begin and end.
153
154 l_proc_name varchar2(30) := 'main';
155 l_proc_body varchar2(32767) := null;
156 l_resolve_pk_local_var varchar2(2000) := null;
157
158
159
160
161 cursor c_cols_map is
162 select bcm.column_name ,
163 bcm.entity_result_column_name
164 from ben_dm_column_mappings bcm ,
165 ben_dm_hierarchies bdh ,
166 ben_dm_Tables bdt
167 where bcm.table_id = bdh.table_id
168 and bdh.hierarchy_type = 'H'
169 and (bcm.column_name = bdh.column_name or bcm.column_name = bdt.surrogate_pk_column_name)
170 and bdh.table_id = bdt.table_id
171 order by bcm.entity_result_column_name
172 ;
173
174 -- indentation for the statements.it specifies number of blank spaces
175 -- after which the staement should start.
176
177 l_indent number;
178 l_proc_body_tbl t_varchar2_32k_tbl;
179 l_proc_index number := 1;
180 l_proc varchar2(75) ;
181 begin
182 l_proc := g_package|| 'generate_reference' ;
183 hr_utility.set_location('Entering : ' || l_proc , 5 ) ;
184 ben_dm_utility.message('ROUT','entry:' || l_proc , 5);
185
186
187 l_interface := indent ||
188 '(p_migration_id in number' || indent ||
189 ',p_business_group_name in varchar2'|| indent ||
190 ',p_group_order in number'|| indent ||
191 ')' || indent ;
192
193
194 -- build the curosr to get the information from ben_dm_entity_result
195 l_indent := 2 ;
196
197 l_cursor := 'Cursor '|| l_cursor_name || '(c_tbl varchar2 ) is ' || indent (l_indent) ;
198 l_indent := 4 ;
199 l_cursor := l_cursor || 'Select ' ;
200 for l_map in c_cols_map Loop
201 l_cursor := l_cursor || indent(l_indent) || rpad(l_map.ENTITY_RESULT_COLUMN_NAME,40) || ' as '||
202 l_map.COLUMN_NAME || ' ,' ;
203 End Loop ;
204
205 l_cursor := rtrim(l_cursor, ',') || indent(l_indent) ;
206 l_indent := 2 ;
207 l_cursor := l_cursor ||' From BEN_DM_ENTITY_RESULTS ' ||indent (l_indent) ;
208 l_indent := 4 ;
209 l_cursor := l_cursor || 'where table_name = c_tbl ' || indent (l_indent) ;
210 -- l_cursor := l_cursor || ' And MIGRATION_ID = P_MIGRATION_ID ' ||indent (l_indent) ;
211 l_cursor := l_cursor || ' And group_order = P_group_order ; ' ||indent (l_indent) ;
212
213
214
215
216 l_proc_body_tbl(l_proc_index) := l_interface;
217
218 l_proc_index := l_proc_index + 1;
219
220 -- local variables of the procedure
221
222 l_locals := ben_dm_gen_download.format_comment('Declare cursors and local variables',2)
223 || indent ||
224 ' l_proc varchar2(72) := g_package '|| '|| ''main'';' || indent ||
225 ' l_fk_map_id number ;' || indent ||
226 ' l_old_pk_key number ;' || indent ||
227 ' l_new_pk_key number ;' || indent ||
228 ' l_source_id number ;' || indent ||
229 ' l_target_id number ;' || indent ||
230 ' l_row_count number := 0;' || indent ||
231 ' l_row_exists varchar2(1) := ''N'';' ||indent;
232
233
234
235
236
237
238 l_proc_comment := ben_dm_gen_download.format_comment('procedure to upload all columns of '
239 || ' self reference from entity results .')||
240 indent;
241
242 -- add the procedure comment defination to the package header and body
243 p_header := p_header || l_proc_comment ||'procedure ' || l_proc_name ||
244 l_interface|| ';';
245
246 l_proc_body_tbl(1) := l_proc_comment || 'procedure ' || l_proc_name ||
247 l_proc_body_tbl(1) || ' is';
248
249 -- add local variables , cursor and procedure body to complete the procedure
250 l_proc_body_tbl(1) := l_proc_body_tbl(1) || l_locals || indent || l_cursor
251 || indent || 'begin' || indent ;
252
253 -- add the body of this procedure to the package.
254 add_to_package_body( l_proc_body_tbl );
255 ben_dm_utility.message('ROUT',l_proc, 25);
256 hr_utility.set_location('Leaving:'||l_proc, 10);
257 exception
258 when others then
259 ben_dm_utility.error(SQLCODE,l_proc ,
260 '(none)','R');
261 raise;
262 end generate_reference;
263
264 -- ----------------------- generate_insert_dml ---------------------------
265 -- Description:
266 -- Generates the insert_dml procedure of the TUPS
267 -- ------------------------------------------------------------------------
268 procedure generate_update_dml
269 is
270 l_locals varchar2(32767) := null;
271 l_cursor varchar2(32767) := null;
272 l_cursor_name varchar2(30) := 'csr_self_ref' ;
273 l_temp varchar2(32767);
274 l_proc_comment varchar2(4000);
275
276 -- block body of the procedure i.e between begin and end.
277 l_proc_body varchar2(32767) := null;
278
279 -- indentation for the statements.it specifies number of blank spaces
280 -- after which the staement should start.
281
282
283 cursor c_cols is
284 select bdh.parent_table_name,
285 bdh.parent_id_column_name,
286 bdh.column_name,
287 bdt.table_name,
288 bdt.surrogate_pk_column_name
289 from ben_dm_hierarchies bdh ,
290 ben_dm_Tables bdt
291 where bdh.hierarchy_type = 'H'
292 and bdh.table_id = bdt.table_id
293 order by bdh.table_id,bdh.column_name
294 ;
295
296
297
298 l_indent number := 2;
299 l_proc_body_tbl t_varchar2_32k_tbl;
300 l_proc_index number := 1;
301 l_list_index number;
302 l_hierarchy_type ben_dm_hierarchies.hierarchy_type%type ;
303 l_parent_table_name ben_dm_hierarchies.parent_table_name%type ;
304 l_parent_id_column_name ben_dm_hierarchies.parent_id_column_name%type ;
305 l_proc varchar2(75) ;
306
307 begin
308 l_proc := g_package || 'generate_update_dml' ;
309 hr_utility.set_location('Entering : ' || l_proc , 5 ) ;
310 ben_dm_utility.message('ROUT','entry:' || l_proc , 5);
311
312 -- input parameters for the procedure
313
314 l_cursor := indent( l_indent) || 'hr_utility.set_location(''Entering : '' || l_proc,5) ; ' || indent(l_indent) ;
315
316 for i in c_cols Loop
317
318 if i.SURROGATE_PK_COLUMN_NAME is not null then
319 l_cursor := l_cursor ||indent(l_indent)||'hr_utility.set_location(''update : ' ||i.table_name||''''||' ,5) ; '||indent(l_indent);
320 l_cursor := l_cursor || '-- open the curosr to fetch the values from result table ' || indent(l_indent) ;
321 l_cursor := l_cursor || 'for l_rslt in ' || l_cursor_name || '(''' || i.table_name||''')' || indent(l_indent) ;
322 l_indent := 4 ;
323 l_cursor := l_cursor || 'Loop ' ;
324 -- Assign the Primary key to the variable
325 -- if the table is date tracked find the key in cache before creating
326
327 l_indent := 6 ;
328 l_cursor := l_cursor ||indent( l_indent) || '-- Assign the source Primary key to the variable ' || indent(l_indent) ;
329 l_cursor := l_cursor|| 'l_old_pk_key := l_rslt.'|| i.SURROGATE_PK_COLUMN_NAME||' ; '||indent(l_indent);
330 l_cursor := l_cursor || '-- get the target Primary key to the variable ' || indent(l_indent) ;
331 l_indent := 40 ;
332 l_cursor:= l_cursor||'l_new_pk_key := ben_dm_data_util.get_cache_target('||indent(l_indent);
333 l_cursor:= l_cursor||' p_table_name => '''||i.table_name||'''' || indent(l_indent) ;
334 l_cursor:= l_cursor||',p_source_id => l_old_pk_key '||indent(l_indent);
335 l_cursor:= l_cursor||',p_source_column => '''||i.SURROGATE_PK_COLUMN_NAME||'''' || indent(l_indent);
336 l_cursor:= l_cursor||',p_business_group_name => p_business_group_name ' || indent(l_indent);
337 l_cursor:= l_cursor|| ');' || indent(l_indent) ;
338 l_indent := 6 ;
339 l_cursor := l_cursor || indent(l_indent) ;
340
341 l_proc_body_tbl(l_proc_index) := l_cursor ;
342 l_proc_index := l_proc_index + 1;
343
344 -- Assign the foreign key to the variable
345 l_cursor := indent( l_indent) || '-- Assign the old Foreign key to the variable ' || indent(l_indent) ;
346 l_cursor := l_cursor||indent(l_indent) || 'l_source_id := l_rslt.' ||i.column_name ||';' ;
347 l_cursor := l_cursor|| indent( l_indent) || '-- get the target value to the variable ' || indent(l_indent) ;
348 -- download the mapping keys
349 l_cursor := l_cursor||indent(l_indent) || '--' || 'Get the Key for '||i.parent_table_name ||'.'||
350 i.parent_id_column_name || indent(l_indent) ;
351 l_cursor := l_cursor|| 'If l_rslt.'||i.column_name||' IS NOT NULL THEN '||indent(l_indent+3);
352 l_cursor := l_cursor|| 'l_source_id := l_rslt.'||i.column_name||' ; '||indent(l_indent+3);
353 l_cursor := l_cursor|| 'l_target_id := ben_dm_data_util.get_cache_target('||indent(l_indent+40);
354 l_cursor := l_cursor||' p_table_name => '''||i.parent_table_name||'''' || indent(l_indent+40) ;
355 l_cursor := l_cursor||',p_source_id => l_source_id '||indent(l_indent+40);
356 l_cursor := l_cursor||',p_source_column => '''||i.parent_id_column_name||'''' || indent(l_indent+40);
357 l_cursor := l_cursor||',p_business_group_name => p_business_group_name ' || indent(l_indent+40);
358 l_cursor := l_cursor|| ');' || indent(l_indent) ;
359 l_cursor := l_cursor|| indent(l_indent +3) ;
360 l_cursor := l_cursor|| 'if l_target_id is not null and l_source_id <> l_target_id then '||indent(l_indent+6);
361 l_cursor := l_cursor|| 'update '|| i.table_name || ' Set ' || i.column_name || ' = l_target_id ' || indent(l_indent+8)||
362 'where ' || i.column_name || ' = l_source_id and ' ||indent(l_indent+8)||
363 i.SURROGATE_PK_COLUMN_NAME ||' = l_new_pk_key ; ' ||indent(l_indent+3) ;
364
365 l_indent := 6 ;
366 l_cursor := l_cursor|| 'End if ;' || indent(l_indent);
367 l_indent := 4 ;
368 l_cursor := l_cursor|| 'End if ;' || indent(l_indent);
369 l_cursor := l_cursor|| 'End Loop ; -- for geting record informations ' || indent(l_indent) || indent(l_indent);
370 end if ;
371 end loop ;
372
373 l_proc_body_tbl(l_proc_index) := l_cursor||indent(l_indent) ;
374 l_proc_index := l_proc_index + 1;
375
376 l_proc_body_tbl(l_proc_index) := indent( l_indent) || 'hr_utility.set_location(''Leaving : '' || l_proc,5) ; ' ;
377 l_proc_index := l_proc_index + 1;
378
379 add_to_package_body( l_proc_body_tbl );
380
381 ben_dm_utility.message('ROUT','exit:'|| l_proc , 25);
382 hr_utility.set_location('Leaving '|| l_proc , 5 ) ;
383
384 exception
385 when others then
386 ben_dm_utility.error(SQLCODE,l_proc , '(none)','R');
387 raise;
388 end generate_update_dml;
389
390 -- ------------------------- main ------------------------
391 -- ------------------------------------------------------------------------
392 procedure main
393 (
394 p_business_group_id in number ,
395 p_migration_id in number
396 )
397 is
398 l_header varchar2(32767);
399 l_body varchar2(32767);
400 l_header_comment varchar2(2048);
401 l_package_name varchar2(30) ;
402 l_generator_version hr_dm_tables.generator_version%type;
403 l_package_version varchar2(200);
404 l_index number := 1;
405 l_call_to_aol_proc varchar2(32767);
406 l_dev_key_local_var varchar2(32767);
407 l_csr_sql integer;
408 l_rows number;
409 l_proc_body_tbl t_varchar2_32k_tbl;
410 l_proc_index number := 1;
411
412
413 l_proc varchar2(75) ;
414 begin
415
416 l_proc := g_package||'main' ;
417 hr_utility.set_location('Entering:'||l_proc, 5);
418 ben_dm_utility.message('ROUT','entry:'||l_proc , 5);
419
420
421
422 l_package_name := 'ben_dm_resolve_reference' ;
423
424 -- inialize the global package body pl/sql table by deleting all elements.
425 init_package_body;
426
427
428 -- Get the version of the generator to be appended to the TUPS package
429 -- generated for a table. This will help in finding out which version
430 -- of Generator is used to generate the TUPS package.
431
432 ben_dm_data_util.get_generator_version(p_generator_version => l_generator_version,
433 p_format_output => 'Y');
434
435 -- Get the package version of this TDS package body.
436 hr_dm_library.get_package_version ( p_package_name => 'BEN_DM_GEN_SELF_REF',
437 p_package_version => l_package_version,
438 p_version_type => 'FULL');
439
440
441
442
443 -- Start the package header and body.
444 begin
445 --
446 -- Set up initial parts of the package header and body.
447 --
448 l_header_comment := l_package_version || indent || '/*' || indent ||
449 ' * Generated by ben_dm_gen_self_ref at: ' ||
450 to_char( sysdate, 'YYYY/MM/DD HH24:MM:SS' ) || indent ||
451 ' * Generated Data Migrator TUPS for : .' ||
452 indent ||
453 ' * Generator Version Used to generate this TUPS is : ' || indent ||
454 l_generator_version || indent ||
455 ' */' || indent || '--' || indent;
456
457 l_header :=
458 'create or replace package ' || l_package_name || ' as' || indent ||
459 l_header_comment || indent;
460
461 -- add in call to hr_dm_upload.set_globals to set the global variables
462 l_header := l_header || indent || '--' || indent ||
463 '-- call to hr_dm_upload.set_globals to set the global variables'
464 || indent || '--' || indent ||
465 '--g_temp_var NUMBER := hr_dm_upload.set_globals;' || indent;
466
467
468 l_proc_body_tbl(1) :=
469 'create or replace package body ' || l_package_name || ' as' || indent ||
470 l_header_comment;
471
472 -- private package variable
473 l_proc_body_tbl(1) := l_proc_body_tbl(1) || indent || '--' || indent ||
474 '-- Package Variables' || indent ||
475 '--' || indent ||
476 'g_package varchar2(33) := ''' || l_package_name || ''';' ||
477 indent;
478
479 -- add the body of this procedure to the package.
480 add_to_package_body( l_proc_body_tbl );
481
482
483
484 -- if the table has a columns which have a foreign key to AOL table then
485 -- generate the procedures so as to create the procedures to get the
486 -- corresponding developer's key for those columns.
487 generate_reference(p_header => l_header,
488 p_body => l_body
489 );
490
491
492
493 -- Generate the procedures and functions.
494 --
495
496 -- generate chk_row_exists procedure to download data into batch_lines.
497
498
499 l_body := l_body || indent || '--' || indent;
500 generate_update_dml ;
501
502 -- For non date track table generate update_dml procedure to update the
503 -- row data. For date track create delete_dml procedure to delete the
504 -- data.
505
506
507
508 l_header := l_header || indent || '--' || indent;
509 l_body := l_body || indent || '--' || indent;
510
511 --
512 -- Terminate the package body and header.
513 --
514 l_header := l_header || 'end ' || l_package_name || ';';
515 l_body := l_body || 'end ' || l_package_name || ';';
516 exception
517 when plsql_value_error then
518 ben_dm_utility.error(SQLCODE,l_proc ,
519 'Either TDS package code size is too big or ' ||
520 ' a value error)',
521 'R');
522 raise;
523 end;
524
525 --
526 -- Compile the header and body.
527 --
528
529 hr_dm_library.run_sql( l_header );
530
531 g_package_index := g_package_index+1;
532 g_package_body(g_package_index ) := indent(2) || 'End main ;' || indent || 'end ' ||
533 l_package_name || ';';
534
535 hr_dm_library.run_sql( g_package_body,
536 g_package_index);
537
538 -- check the status of the package
539 begin
540 hr_dm_library.check_compile (p_object_name => l_package_name,
541 p_object_type => 'PACKAGE BODY' );
542 exception
543 when others then
544 ben_dm_utility.error(SQLCODE,'Error in compiling '|| l_proc ,'(none)','R');
545 raise;
546 end;
547
548 ben_dm_utility.message('ROUT','exit:'|| l_proc , 25);
549 hr_utility.set_location('Leaving:'||l_proc, 10);
550
551 exception
552 when others then
553 ben_dm_utility.error(SQLCODE,l_proc, '(none)','R');
554 raise;
555 end main ;
556
557 end BEN_DM_GEN_SELF_REF;