[Home] [Help]
PACKAGE BODY: APPS.BEN_DM_GEN_UPLOAD
Source
1 package body BEN_DM_GEN_UPLOAD as
2 /* $Header: benfdmgnup.pkb 120.0 2006/05/04 04:49:21 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_upload.' ;
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_upload --------------------------------
136 -- Description:
137 -- Generates the upload procedure of the upload
138 -- ------------------------------------------------------------------------
139 procedure generate_upload
140 (
141 p_table_info in ben_dm_gen_download.t_ben_dm_table,
142 p_header in out nocopy varchar2,
143 p_body in out nocopy varchar2
144 )
145 is
146 l_interface varchar2(32767);
147 l_locals varchar2(32767) := null;
148 l_cursor varchar2(32767) := null;
149 l_comment varchar2(4000);
150 l_proc_comment varchar2(4000);
151 l_cursor_name varchar2(30) := 'csr_mig_' || p_table_info.table_alias;
152
153 -- block body of the procedure i.e between begin and end.
154
155 l_proc_name varchar2(30) := 'upload';
156 l_proc_body varchar2(32767) := null;
157 l_resolve_pk_local_var varchar2(2000) := null;
158
159
160
161 cursor c_cols_map (c_tbl_id number , c_table_name varchar2) is
162 select a.column_name ,
163 a.entity_result_column_name
164 from ben_dm_column_mappings a ,
165 sys.all_tab_columns b
166 where a.table_id = c_tbl_id
167 and a.column_name = b.column_name
168 and b.table_name = c_table_name
169 order by b.column_id
170 ;
171
172 -- indentation for the statements.it specifies number of blank spaces
173 -- after which the staement should start.
174
175 l_indent number;
176 l_proc_body_tbl t_varchar2_32k_tbl;
177 l_proc_index number := 1;
178 l_proc varchar2(75) ;
179 begin
180 l_proc := g_package|| 'generate_upload' ;
181 hr_utility.set_location('Entering : ' || l_proc , 5 ) ;
182 ben_dm_utility.message('ROUT','entry:' || l_proc , 5);
183
184
185 l_interface := indent ||
186 '(p_migration_id in number' || indent ||
187 ',p_business_group_id in number' || indent ||
188 ',p_business_group_name in varchar2'|| indent ||
189 ',p_group_order in number' || indent ||
190 ',p_delimiter in varchar2'|| 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 || ' is ' || indent (l_indent) ;
198 l_indent := 4 ;
199 l_cursor := l_cursor || 'Select ' || indent (l_indent) ;
200 if p_table_info.SEQUENCE_NAME is not null then
201 l_cursor := l_cursor || rpad(p_table_info.SEQUENCE_NAME || '.NEXTVAL',40)||' As PM_KEY '||indent(l_indent);
202 else
203 l_cursor := l_cursor || rpad('Null',40) ||' As PM_KEY '||indent(l_indent);
204 end if ;
205 for l_map in c_cols_map (p_table_info.table_id, p_table_info.table_name) Loop
206 l_cursor := l_cursor || ','|| rpad(l_map.ENTITY_RESULT_COLUMN_NAME,40) || ' as '||
207 l_map.COLUMN_NAME || indent(l_indent);
208 End Loop ;
209 l_indent := 2 ;
210 l_cursor := l_cursor ||' From BEN_DM_ENTITY_RESULTS ' ||indent (l_indent) ;
211 l_indent := 4 ;
212 l_cursor := l_cursor || 'where table_name = '''|| p_table_info.table_name ||'''' || indent (l_indent) ;
213 l_cursor := l_cursor || ' And GROUP_ORDER = P_GROUP_ORDER ' ||indent (l_indent) ;
214 --l_cursor := l_cursor || ' And MIGRATION_ID = P_MIGRATION_ID ' ||
215 l_cursor := l_cursor || ' ; ' ||indent (l_indent) ;
216
217
218
219
220 l_proc_body_tbl(l_proc_index) := l_interface;
221
222 l_proc_index := l_proc_index + 1;
223
224 -- local variables of the procedure
225
226 l_locals := ben_dm_gen_download.format_comment('Declare cursors and local variables',2)
227 || indent ||
228 ' l_proc varchar2(72) := g_package '|| '|| ''upload'';' || indent ||
229 ' l_fk_map_id number ;' || indent ||
230 ' l_table_name varchar2(72) ;' || indent ||
231 ' l_old_pk_id number ;' || indent ||
232 ' l_pk_key number ;' || indent ||
233 ' l_row_count number := 0;' || indent ||
234 ' l_text varchar2(32767);'||indent||
235 ' l_row_exists varchar2(1) := ''N'';' ||indent || indent;
236
237
238
239 if p_table_info.table_name = 'PER_ALL_PEOPLE_F' then
240
241 l_locals := l_locals || 'Cursor c_target_ssn (p_per_id number) is ' || indent ;
242 l_locals := l_locals || 'select TARGET_NATIONAL_IDENTIFIER ' || indent ;
243 l_locals := l_locals || ' from ben_dm_input_file ' || indent ;
244 l_locals := l_locals || ' where source_person_id = p_per_id ' || indent ;
245 --l_locals := l_locals || ' and migration_id = p_migration_id ' || indent ;
246 l_locals := l_locals || ' and TARGET_BUSINESS_GROUP_NAME = p_BUSINESS_GROUP_NAME ' || indent ;
247 l_locals := l_locals || ' and Group_order = p_group_order ' || indent ;
248 l_locals := l_locals || ' ; ' || indent ;
249 l_locals := l_locals || 'l_new_ssn varchar2(80) ; ' || indent ;
250
251 end if ;
252
253 if p_table_info.table_name = 'BEN_LE_CLSN_N_RSTR' then
254
255 l_locals := l_locals || 'Cursor c_pk_column (p_tbl_name varchar2) is ' || indent ;
256 l_locals := l_locals || 'select SURROGATE_PK_COLUMN_NAME ' || indent ;
257 l_locals := l_locals || ' from ben_dm_tables ' || indent ;
258 l_locals := l_locals || ' where table_name = p_tbl_name ' || indent ;
259 l_locals := l_locals || ' ; ' || indent ;
260 l_locals := l_locals || 'l_pk_column_name varchar2(80) ; ' || indent ;
261
262 end if ;
263
264
265 l_proc_comment := ben_dm_gen_download.format_comment('procedure to upload all columns of '
266 || upper(p_table_info.table_name) || ' from entity results .')||
267 indent;
268
269 -- add the procedure comment defination to the package header and body
270 p_header := p_header || l_proc_comment ||'procedure ' || l_proc_name ||
271 l_interface|| ';';
272
273 l_proc_body_tbl(1) := l_proc_comment || 'procedure ' || l_proc_name ||
274 l_proc_body_tbl(1) || ' is';
275
276 -- add local variables , cursor and procedure body to complete the procedure
277 l_proc_body_tbl(1) := l_proc_body_tbl(1) || l_locals || indent || l_cursor
278 || indent || 'begin' || indent ;
279
280 -- add the body of this procedure to the package.
281 add_to_package_body( l_proc_body_tbl );
282 ben_dm_utility.message('ROUT',l_proc, 25);
283 hr_utility.set_location('Leaving:'||l_proc, 10);
284 exception
285 when others then
286 ben_dm_utility.error(SQLCODE,'hr_dm_gen_tups.generate_upload',
287 '(none)','R');
288 raise;
289 end generate_upload;
290
291 -- ----------------------- generate_insert_dml ---------------------------
292 -- Description:
293 -- Generates the insert_dml procedure of the TUPS
294 -- ------------------------------------------------------------------------
295 procedure generate_insert_dml
296 (
297 p_table_info in ben_dm_gen_download.t_ben_dm_table
298 )
299 is
300 l_locals varchar2(32767) := null;
301 l_cursor varchar2(32767) := null;
302 l_cursor_name varchar2(30) := 'csr_mig_' || p_table_info.table_alias;
303 l_temp varchar2(32767);
304 l_proc_comment varchar2(4000);
305
306 -- block body of the procedure i.e between begin and end.
307 l_proc_body varchar2(32767) := null;
308
309 -- indentation for the statements.it specifies number of blank spaces
310 -- after which the staement should start.
311
312
313 cursor c_pk_hier (c_tbl_id number) is
314 select bdt.table_alias
315 ,bdm.column_name
316 ,bdm.parent_table_name
317 ,bdm.parent_column_name
318 ,bdm.parent_id_column_name
319 from ben_dm_hierarchies bdm , ben_dm_tables bdt
320 where bdm.table_id = c_tbl_id
321 and bdm.parent_table_name = bdt.table_name
322 ;
323
324
325 cursor c_H_hier (c_tbl_id number) is
326 select 'x'
327 from ben_dm_hierarchies bdm , ben_dm_tables bdt
328 where bdm.table_id = c_tbl_id
329 and bdm.parent_table_name = bdt.table_name
330 and bdm.hierarchy_type = 'H'
331 ;
332
333
334
335 cursor c_cols_map (c_tbl_id number) is
336 select decode(col.data_type,'DATE','to_char(l_rslt.'||map.column_name||','||''''||'dd-mon-rrrr'||''''||')','l_rslt.'
337 ||map.column_name) column_name ,
338 map.entity_result_column_name
339 from ben_dm_column_mappings map,
340 ben_dm_tables tab,
341 sys.all_tab_columns col
342 where tab.table_id = c_tbl_id
343 and map.table_id = tab.table_id
344 and col.column_name = map.column_name
345 and col.table_name = tab.table_name
346 order by col.column_id
347 ;
348
349 cursor hier_type (p_col_name varchar2) is
350 select bdh.hierarchy_type ,
351 bdh.parent_table_name,
352 bdh.parent_id_column_name
353 from ben_dm_hierarchies bdh
354 where bdh.table_id = p_table_info.table_id
355 and bdh.column_name= p_col_name
356 ;
357
358
359 l_indent number := 2;
360 l_proc_body_tbl t_varchar2_32k_tbl;
361 l_proc_index number := 1;
362 l_list_index number;
363 l_hierarchy_type ben_dm_hierarchies.hierarchy_type%type ;
364 l_parent_table_name ben_dm_hierarchies.parent_table_name%type ;
365 l_parent_id_column_name ben_dm_hierarchies.parent_id_column_name%type ;
366 l_proc varchar2(75) ;
367 l_h_hierarcy varchar2(1) ;
368 l_dummy varchar2(1) ;
369
370 begin
371 l_proc := g_package || 'generate_insert_dml' ;
372 hr_utility.set_location('Entering : ' || l_proc , 5 ) ;
373 ben_dm_utility.message('ROUT','entry:' || l_proc , 5);
374
375 -- input parameters for the procedure
376
377 l_cursor := indent( l_indent) || 'hr_utility.set_location(''Entering : '' || l_proc,5) ; ' || indent(l_indent) ||
378 ' ben_dm_utility.message(''ROUT'','' Entering '' ||l_proc,5) ; ' || indent(l_indent) ||
379 ' ben_dm_utility.message(''PARA'', '' ( Source BG - '' || p_business_group_id || '')'' , 10) ;'|| indent(l_indent) ||
380 ' ben_dm_utility.message(''PARA'', '' ( Target BG - '' || p_business_group_name || '')'' , 10) ;'|| indent(l_indent)||
381 ' ben_dm_utility.message(''PARA'', '' ( group_order - '' || p_group_order || '')'' , 10) ; '|| indent(l_indent) ;
382
383 -- decide the table has any pig year relation
384 l_h_hierarcy := 'N' ;
385 open c_H_hier(p_table_info.table_id) ;
386 fetch c_H_hier into l_dummy ;
387 if c_H_hier%found then
388 l_h_hierarcy := 'Y' ;
389 end if ;
390 close c_H_hier ;
391
392 l_cursor := l_cursor || '-- open the curosr to fetch the values from result table ' || indent(l_indent) ;
393 l_cursor := l_cursor || 'for l_rslt in ' || l_cursor_name || indent(l_indent) ;
394 l_indent := 4 ;
395 l_cursor := l_cursor || 'Loop ' || indent(l_indent) ;
396 -- Assign the Primary key to the variable
397 -- if the table is date tracked find the key in cache before creating
398
399 hr_utility.set_location('PK column name : ' || p_table_info.SURROGATE_PK_COLUMN_NAME , 5 ) ;
400 if p_table_info.SURROGATE_PK_COLUMN_NAME is not null then
401 l_indent := 6 ;
402 l_cursor := l_cursor ||indent( l_indent) || '-- Assign the Primary key to the variable ' || indent(l_indent) ;
403 l_cursor := l_cursor || 'if ' || 'l_rslt.' || p_table_info.SURROGATE_PK_COLUMN_NAME||' Is not null '||indent(l_indent);
404 l_indent := 9 ;
405 l_cursor := l_cursor || ' and ' || 'l_rslt.PM_KEY is not null then ' ||indent(l_indent);
406 l_cursor := l_cursor|| 'l_fk_map_id := l_rslt.'|| p_table_info.SURROGATE_PK_COLUMN_NAME||' ; '||indent(l_indent);
407 l_cursor := l_cursor|| 'l_old_pk_id := l_rslt.'|| p_table_info.SURROGATE_PK_COLUMN_NAME||' ; '||indent(l_indent);
408 --l_indent := 4 ;
409
410 --- check the pk from cache whne the table is date track
411 hr_utility.set_location('datetrack : ' || p_table_info.DATETRACK , 5 ) ;
412 if p_table_info.DATETRACK = 'Y' or l_h_hierarcy = 'Y' then
413
414 l_indent := 40 ;
415 l_cursor:= l_cursor||'l_pk_key := ben_dm_data_util.get_cache_target('||indent(l_indent);
416 l_cursor:= l_cursor||' p_table_name => '''||p_table_info.table_name||'''' || indent(l_indent) ;
417 l_cursor:= l_cursor||',p_source_id => l_fk_map_id '||indent(l_indent);
418 l_cursor:= l_cursor||',p_source_column => '''||p_table_info.SURROGATE_PK_COLUMN_NAME||'''' || indent(l_indent);
419 l_cursor:= l_cursor||',p_business_group_name => p_business_group_name ' || indent(l_indent);
420 l_cursor:= l_cursor|| ');' || indent(l_indent) ;
421 l_indent:= 9 ;
422 l_cursor:= l_cursor|| indent(l_indent ) ;
423 l_cursor:= l_cursor|| '-- if the l_pk_key is value null or same as source then assign the new value '||indent(l_indent);
424 l_indent := 12 ;
425 l_cursor:= l_cursor|| 'if l_pk_key is null or l_pk_key = l_fk_map_id then '||indent(l_indent);
426 l_cursor := l_cursor || 'l_rslt.' || p_table_info.SURROGATE_PK_COLUMN_NAME || ':= l_rslt.PM_KEY ; '|| indent(l_indent);
427 l_cursor := l_cursor || 'hr_utility.set_location( ''PK OF '||p_table_info.table_name ||' ''|| l_rslt.PM_KEY'||',20) ;'
428 || indent(l_indent) ;
429
430 -- update the prmiary key to mapping table
431 l_cursor := l_cursor || '-- update the primiary key to mapping table ' || indent(l_indent) ;
432 l_indent := 40 ;
433 l_cursor := l_cursor || 'ben_dm_data_util.create_pk_cache( ' || indent(l_indent) ;
434 l_cursor := l_cursor || ' p_target_id => l_rslt.PM_KEY ' || indent(l_indent) ;
435 l_cursor := l_cursor || ',p_table_name => '''||p_table_info.table_name||'''' || indent(l_indent) ;
436 l_cursor := l_cursor || ',p_source_id => l_fk_map_id' || indent(l_indent) ;
437 l_cursor := l_cursor || ',p_source_column => '''||p_table_info.SURROGATE_PK_COLUMN_NAME||'''' ||indent(l_indent);
438 l_cursor := l_cursor || ',p_business_group_name => p_business_group_name' || indent(l_indent) ;
439 l_cursor := l_cursor || ' ); ' || indent(l_indent) ;
440 l_indent:= 9 ;
441 l_cursor:= l_cursor|| indent(l_indent);
442 l_indent:= 12 ;
443 l_cursor:= l_cursor|| 'Else '||indent(l_indent);
444 l_cursor:= l_cursor|| indent(l_indent)||'hr_utility.set_location('' PK Already exisit : '' || l_pk_key,10) ;'
445 ||indent(l_indent);
446 l_cursor:= l_cursor || 'l_rslt.'||p_table_info.SURROGATE_PK_COLUMN_NAME || ':= l_pk_key ; '|| indent(l_indent);
447 l_indent:= 6 ;
448 l_cursor:= l_cursor|| indent(l_indent);
449 l_cursor:= l_cursor|| 'End if ; -- Primary key for date track table '||indent(l_indent);
450 else
451 l_cursor := l_cursor || 'l_rslt.' || p_table_info.SURROGATE_PK_COLUMN_NAME || ':= l_rslt.PM_KEY ; '|| indent(l_indent);
452 l_cursor := l_cursor || 'hr_utility.set_location( ''PK OF '||p_table_info.table_name ||' ''|| l_rslt.PM_KEY' ||',20) ;'
453 || indent(l_indent) ;
454
455 -- update the prmiary key to mapping table
456 l_cursor := l_cursor || '-- update the prmiary key to mapping table ' || indent(l_indent) ;
457 l_indent := 40 ;
458 l_cursor := l_cursor || 'ben_dm_data_util.create_pk_cache( ' || indent(l_indent) ;
459 l_cursor := l_cursor || ' p_target_id => l_rslt.PM_KEY ' || indent(l_indent) ;
460 l_cursor := l_cursor || ',p_table_name => '''||p_table_info.table_name||'''' || indent(l_indent) ;
461 l_cursor := l_cursor || ',p_source_id => l_fk_map_id' || indent(l_indent) ;
462 l_cursor := l_cursor || ',p_source_column => '''||p_table_info.SURROGATE_PK_COLUMN_NAME||'''' ||indent(l_indent);
463 l_cursor := l_cursor || ',p_business_group_name => p_business_group_name' || indent(l_indent) ;
464 l_cursor := l_cursor || ' ); ' || indent(l_indent) ;
465
466 end if ;
467 --l_cursor := l_cursor || ' ben_dm_utility.message(''INFO'','' Primary KEY '' ||l_rslt.PM_KEY ,5) ; ' || indent(l_indent) ;
468 l_indent := 4 ;
469 l_cursor := l_cursor || indent(l_indent) ;
470
471 --l_indent := 6 ;
472 l_cursor := l_cursor || 'End if ; -- primary key handling' ||indent(l_indent);
473
474
475
476 end if ; -- if the surrogate key is not null create pm key
477 hr_utility.set_location('after PK column name : ' || p_table_info.SURROGATE_PK_COLUMN_NAME , 5 ) ;
478
479 l_proc_body_tbl(l_proc_index) := l_cursor ;
480 l_proc_index := l_proc_index + 1;
481
482 l_indent := 4 ;
483
484 -- Assign the foreign key to the variable
485 hr_utility.set_location('before FK column name ' , 5 ) ;
486
487 l_cursor := indent( l_indent) || '-- Assign the Foreign key to the variable ' || indent(l_indent) ;
488 -- download the mapping keys
489 for l_pk_rec in c_pk_hier(p_table_info.table_id ) Loop
490 hr_utility.set_location('IN FK column name '||p_table_info.table_id || l_pk_rec.column_name , 5 ) ;
491
492 l_cursor := l_cursor||indent(l_indent) || '--' || 'Get the Key for '||l_pk_rec.parent_table_name ||'.'||
493 l_pk_rec.parent_column_name || indent(l_indent) ;
494 l_cursor := l_cursor|| 'If l_rslt.'||l_pk_rec.column_name||' IS NOT NULL THEN '||indent(l_indent+3);
495 l_cursor := l_cursor|| 'l_fk_map_id := l_rslt.'||l_pk_rec.column_name||' ; '||indent(l_indent+3);
496 --- decide the function to be called if the hieracry type is 'S' call mapping target else cache_target
497 --- pass the parent table name and parent column name
498 l_parent_table_name := null ;
499 l_parent_id_column_name := null ;
500 l_hierarchy_type := null ;
501 open hier_type(l_pk_rec.column_name) ;
502 fetch hier_type into
503 l_hierarchy_type,
504 l_parent_table_name ,
505 l_parent_id_column_name ;
506 close hier_type ;
507 hr_utility.set_location(' hierarchy_type e '|| l_hierarchy_type , 5 ) ;
508 if l_hierarchy_type is not null then
509 if l_hierarchy_type = 'S' then
510 l_cursor:=l_cursor||'l_rslt.'||l_pk_rec.column_name||':= ben_dm_data_util.get_mapping_target('||indent(l_indent+40);
511 else
512 l_cursor:=l_cursor||'l_rslt.'||l_pk_rec.column_name||':= ben_dm_data_util.get_cache_target('||indent(l_indent+40);
513 end if ;
514 l_cursor := l_cursor||' p_table_name => '''||l_parent_table_name||'''' || indent(l_indent+40) ;
515 l_cursor := l_cursor||',p_source_id => l_fk_map_id '||indent(l_indent+40);
516 l_cursor := l_cursor||',p_source_column => '''||l_parent_id_column_name||'''' || indent(l_indent+40);
517 l_cursor := l_cursor||',p_business_group_name => p_business_group_name ' || indent(l_indent+40);
518 l_cursor := l_cursor|| ');' || indent(l_indent) ;
519 l_cursor := l_cursor|| indent(l_indent +3) ;
520 l_cursor := l_cursor|| '-- if the source value is not null and target value is null ' || indent(l_indent +3) ;
521 l_cursor := l_cursor|| '-- assign back the source value ' || indent(l_indent +3) ;
522 l_cursor := l_cursor|| '-- if the column is pig year then get new id from seq and store in cache ' || indent(l_indent +3) ;
523 l_cursor := l_cursor|| 'hr_utility.set_location(''FK OF '||l_pk_rec.column_name||'''||l_rslt.'||l_pk_rec.column_name
524 ||',20) ; ' || indent(l_indent +3) ;
525 l_cursor := l_cursor|| 'if l_fk_map_id is not null and ( l_rslt.'||l_pk_rec.column_name||
526 ' = l_fk_map_id or l_rslt.'||l_pk_rec.column_name||' is null ) then ' ||indent(l_indent+6);
527 -- handle the hieracy H - Pig year
528 -- get new id from from the sequence and stroe in to cache , when the PK is generated,
529 -- it wil lget it from the cache
530 if l_hierarchy_type = 'H' then
531 l_cursor := l_cursor|| 'Begin ' || indent(l_indent+9);
532 l_cursor := l_cursor|| 'select '||p_table_info.SEQUENCE_NAME ||'.NEXTVAL into l_rslt.'|| l_pk_rec.column_name
533 ||' from dual ;'|| indent(l_indent+9);
534 l_cursor := l_cursor || 'ben_dm_data_util.create_pk_cache( ' || indent(l_indent+9) ;
535 l_cursor := l_cursor || ' p_target_id => l_rslt.'|| l_pk_rec.column_name || indent(l_indent+9) ;
536 l_cursor := l_cursor || ',p_table_name => '''||p_table_info.table_name||'''' || indent(l_indent+9) ;
537 l_cursor := l_cursor || ',p_source_id => l_fk_map_id ' || indent(l_indent+9) ;
538 l_cursor := l_cursor || ',p_source_column => '''||p_table_info.SURROGATE_PK_COLUMN_NAME||'''' ||indent(l_indent+9);
539 l_cursor := l_cursor || ',p_business_group_name => p_business_group_name' || indent(l_indent+9) ;
540 l_cursor := l_cursor || ' ); ' || indent(l_indent+6) ;
541
542
543 l_cursor := l_cursor|| 'Exception ' || indent(l_indent+9);
544 l_cursor := l_cursor|| 'When others then ' || indent(l_indent+9);
545 l_cursor := l_cursor|| 'l_rslt.'||l_pk_rec.column_name|| ' := l_fk_map_id ; ' || indent(l_indent+6);
546 l_cursor := l_cursor|| 'End ; ' || indent(l_indent+3);
547 else
548 l_cursor := l_cursor|| 'l_rslt.'||l_pk_rec.column_name|| ' := l_fk_map_id ; ' || indent(l_indent+3);
549 end if ;
550 l_cursor := l_cursor|| 'End if ;' || indent(l_indent);
551 end if ;
552 hr_utility.set_location('after FK column name '|| l_pk_rec.column_name , 5 ) ;
553 l_cursor := l_cursor|| 'End If ; '||indent(l_indent) ;
554
555 --- whne the cariable length grows beyond
556 if length(l_cursor) > 30000 then
557 hr_utility.set_location('length of cursor '|| length(l_cursor) , 5 ) ;
558 l_proc_body_tbl(l_proc_index) := l_cursor ;
559 l_proc_index := l_proc_index + 1;
560 l_cursor := ' ' ;
561
562 end if ;
563 End Loop ;
564 -- end of call to get the FK values
565
566
567
568 ---special treatment for per_all_people_f
569 if p_table_info.table_name = 'PER_ALL_PEOPLE_F' then
570 l_cursor := l_cursor|| indent(l_indent);
571 l_cursor := l_cursor|| '-- get the new sssn from input file ' || indent(l_indent);
572 l_cursor := l_cursor || 'l_new_ssn := null ; ' || indent(l_indent) ;
573 l_cursor := l_cursor || 'open c_target_ssn (l_old_pk_id) ; ' || indent(l_indent) ;
574 l_cursor := l_cursor || 'fetch c_target_ssn into l_new_ssn ; ' || indent(l_indent) ;
575 l_cursor := l_cursor || 'close c_target_ssn ; ' || indent(l_indent) ;
576 l_cursor := l_cursor || 'if l_new_ssn is not null then ' || indent(l_indent) ;
577 l_cursor := l_cursor || ' l_rslt.NATIONAL_IDENTIFIER := l_new_ssn ; ' || indent(l_indent) ;
578 l_cursor := l_cursor || 'end if ; ' || indent(l_indent) ;
579
580 end if ;
581 l_proc_body_tbl(l_proc_index) := l_cursor ;
582 l_proc_index := l_proc_index + 1;
583
584 ---special treatment for BEN_LE_CLSN_N_RSTR to fix the PK
585 l_cursor := indent(l_indent) ;
586 if p_table_info.table_name = 'BEN_LE_CLSN_N_RSTR' then
587 hr_utility.set_location('special handing BEN_LE_CLSN_N_RSTR ' , 99 ) ;
588 l_cursor := l_cursor|| 'l_table_name := l_rslt.BKUP_TBL_TYP_CD ; '||indent(l_indent);
589 l_cursor := l_cursor|| 'if substr(l_table_name,-8) = ''_CORRECT'' then '||indent(l_indent+3);
590 l_cursor := l_cursor|| 'l_table_name := rtrim(l_table_name, ''_CORRECT'') ; '||indent(l_indent);
591 l_cursor := l_cursor|| 'End if ; '||indent(l_indent);
592
593 l_cursor := l_cursor|| 'if substr(l_table_name,-5) = ''_CORR'' then '||indent(l_indent+3);
594 l_cursor := l_cursor|| 'l_table_name := rtrim(l_table_name, ''_CORR'') ; '||indent(l_indent);
595 l_cursor := l_cursor|| 'End if ; '||indent(l_indent) || indent(l_indent) ;
596 l_cursor := l_cursor|| 'hr_utility.set_location(''table name '' || l_table_name , 90); '||indent(l_indent) ;
597
598 l_cursor := l_cursor|| 'l_pk_column_name := null ; ' || indent(l_indent) ;
599 l_cursor := l_cursor|| 'open c_pk_column (l_table_name) ; ' || indent(l_indent) ;
600 l_cursor := l_cursor|| 'fetch c_pk_column into l_pk_column_name ; ' || indent(l_indent) ;
601 l_cursor := l_cursor|| 'close c_pk_column ; ' || indent(l_indent) ;
602 l_cursor := l_cursor|| 'hr_utility.set_location(''PK name '' || l_pk_column_name , 90); ' || indent(l_indent) ;
603 l_cursor := l_cursor|| 'hr_utility.set_location(''old value '' || l_rslt.BKUP_TBL_ID , 90);' || indent(l_indent) ;
604 l_cursor := l_cursor|| 'if l_pk_column_name is not null then ' || indent(l_indent+3) ;
605 l_cursor := l_cursor|| 'l_fk_map_id := l_rslt.BKUP_TBL_ID ; '||indent(l_indent+3);
606 l_cursor := l_cursor|| 'l_rslt.BKUP_TBL_ID '||':= ben_dm_data_util.get_cache_target('||indent(l_indent+40);
607 l_cursor := l_cursor|| ' p_table_name => l_table_name '||indent(l_indent+40) ;
608 l_cursor := l_cursor|| ',p_source_id => l_rslt.BKUP_TBL_ID '||indent(l_indent+40);
609 l_cursor := l_cursor|| ',p_source_column => l_pk_column_name ' || indent(l_indent+40);
610 l_cursor := l_cursor|| ',p_business_group_name => p_business_group_name ' || indent(l_indent+40);
611 l_cursor := l_cursor|| ');' || indent(l_indent) ;
612 l_cursor := l_cursor|| 'hr_utility.set_location(''new value '' || l_rslt.BKUP_TBL_ID , 90); ' || indent(l_indent) ;
613 l_cursor := l_cursor|| indent(l_indent +3) ;
614 l_cursor := l_cursor|| '-- if the source value is not null and target value is null ' || indent(l_indent +3) ;
615 l_cursor := l_cursor|| '-- assign back the source value ' || indent(l_indent +3) ;
616 l_cursor := l_cursor|| 'hr_utility.set_location(''FK OF BKUP_TBL_ID ''||l_rslt.BKUP_TBL_ID '||',20);'||indent(l_indent +3) ;
617 l_cursor := l_cursor|| 'if l_fk_map_id is not null and l_rslt.BKUP_TBL_ID is null then'||indent(l_indent+6);
618 l_cursor := l_cursor|| 'l_rslt.BKUP_TBL_ID := l_fk_map_id ; ' || indent(l_indent+3);
619 l_cursor := l_cursor|| 'End if ;' || indent(l_indent);
620 l_cursor := l_cursor || 'End if ; ---Spl handling ' || indent(l_indent) ;
621 hr_utility.set_location('EOF BEN_LE_CLSN_N_RSTR ' , 99 ) ;
622 elsif p_table_info.table_name = 'BEN_ELIG_PER_ELCTBL_CHC' then
623 --
624 hr_utility.set_location('special handing BEN_ELIG_PER_ELCTBL_CHC ' , 99 ) ;
625 l_cursor := l_cursor|| 'declare '||indent(l_indent+3);
626
627 l_cursor := l_cursor|| 'l_prev_rslt_id_at number := 0 ;'||indent(l_indent+3);
628 l_cursor := l_cursor|| 'l_prev_prtt_enrt_rslt_id number ;' ||indent(l_indent+3);
629 l_cursor := l_cursor|| 'l_curr_prtt_enrt_rslt_id number ;'||indent(l_indent+3);
630 l_cursor := l_cursor|| 'l_cryfwd_elig_dpnt_cd varchar2(30);' ||indent(l_indent);
631
632 l_cursor := l_cursor|| 'begin '||indent(l_indent+3);
633
634 --
635 l_cursor := l_cursor|| 'l_prev_prtt_enrt_rslt_id := null; '||indent(l_indent+3);
636 l_cursor := l_cursor|| 'l_prev_rslt_id_at := instr(l_rslt.cryfwd_elig_dpnt_cd, ''^'') ;'||indent(l_indent+3);
637
638 --
639 l_cursor := l_cursor|| 'if l_prev_rslt_id_at > 0 then '||indent(l_indent+5);
640 l_cursor := l_cursor|| 'Begin '||indent(l_indent+7);
641 l_cursor := l_cursor|| 'l_prev_prtt_enrt_rslt_id := to_number(substr(l_rslt.cryfwd_elig_dpnt_cd,l_prev_rslt_id_at+1) );'||indent(l_indent+7);
642 l_cursor := l_cursor|| 'IF l_prev_prtt_enrt_rslt_id IS NOT NULL THEN '||indent(l_indent+9);
643 --
644 l_cursor := l_cursor|| 'l_fk_map_id := l_prev_prtt_enrt_rslt_id ; '||indent(l_indent+9);
645 l_cursor := l_cursor|| 'l_curr_prtt_enrt_rslt_id := ben_dm_data_util.get_cache_target( '||indent(l_indent+12);
646 l_cursor := l_cursor|| 'p_table_name => ''BEN_PRTT_ENRT_RSLT_F'' '||indent(l_indent+12);
647 l_cursor := l_cursor|| ',p_source_id => l_fk_map_id '||indent(l_indent+12);
648 l_cursor := l_cursor|| ',p_source_column => ''PRTT_ENRT_RSLT_ID'' '||indent(l_indent+12);
649 l_cursor := l_cursor|| ',p_business_group_name => p_business_group_name '||indent(l_indent+12);
650 l_cursor := l_cursor|| '); '||indent(l_indent+9);
651 l_cursor := l_cursor|| 'hr_utility.set_location(''FK OF PRTT_ENRT_RSLT_ID''||l_curr_prtt_enrt_rslt_id,20) ; '||indent(l_indent+7);
652 l_cursor := l_cursor|| 'End If ; '||indent(l_indent+7);
653 l_cursor := l_cursor|| 'Exception '||indent(l_indent+5);
654 l_cursor := l_cursor|| 'when value_error then '||indent(l_indent+7);
655 l_cursor := l_cursor|| 'l_prev_prtt_enrt_rslt_id := null; '||indent(l_indent+5);
656 l_cursor := l_cursor|| 'End ; '||indent(l_indent+5);
657
658 l_cursor := l_cursor|| 'IF l_curr_prtt_enrt_rslt_id IS NOT NULL THEN '||indent(l_indent+7);
659 l_cursor := l_cursor|| 'l_cryfwd_elig_dpnt_cd := substr(l_rslt.cryfwd_elig_dpnt_cd,1,l_prev_rslt_id_at-1) ; '||indent(l_indent+7);
660 l_cursor := l_cursor|| 'l_rslt.cryfwd_elig_dpnt_cd := l_cryfwd_elig_dpnt_cd||''^''||l_curr_prtt_enrt_rslt_id ;'||indent(l_indent+5);
661 l_cursor := l_cursor|| 'END IF; '||indent(l_indent+3);
662 l_cursor := l_cursor|| ' end if ; '||indent(l_indent);
663 l_cursor := l_cursor|| ' end; '||indent(l_indent);
664 hr_utility.set_location('EOF special handing BEN_ELIG_PER_ELCTBL_CHC ' , 99 ) ;
665 --
666 end if ;
667
668
669 l_proc_body_tbl(l_proc_index) := l_cursor ;
670 l_proc_index := l_proc_index + 1;
671
672 -- insert into table dml statement
673 l_cursor := indent(l_indent) || ' -- Inserting the values into source table ' || indent(l_indent) ;
674 l_indent := 8 ;
675
676 l_locals := 'l_text := '||''''||p_table_info.table_name||''''||'||p_delimiter||';
677 for l_cols_map in c_cols_map(p_table_info.table_id) Loop
678 l_locals := l_locals || l_cols_map.COLUMN_NAME || '||p_delimiter||';
679 end Loop ;
680
681 l_locals := rtrim( l_locals , '||');
682
683 l_indent := 6 ;
684
685 l_proc_body_tbl(l_proc_index) := l_cursor||indent(l_indent)||indent(l_indent)
686 ||l_locals||';'||indent(l_indent)||'utl_file.put_line(ben_dm_utility.g_out_file_handle,l_text);'||indent(l_indent) || indent(l_indent-2)
687 || 'End Loop ; -- for geting record informations ' || indent(l_indent-2) ;
688
689 l_proc_index := l_proc_index + 1;
690
691 l_proc_body_tbl(l_proc_index) := indent( l_indent)|| 'hr_utility.set_location(''Leaving : '' || l_proc,5) ; ' ||
692 indent(l_indent) || ' ben_dm_utility.message(''ROUT'','' Exit '' ||l_proc,5) ; ' ;
693 l_proc_index := l_proc_index + 1;
694
695 add_to_package_body( l_proc_body_tbl );
696
697 ben_dm_utility.message('ROUT','exit:'|| l_proc , 25);
698 hr_utility.set_location('Leaving '|| l_proc , 5 ) ;
699 exception
700 when others then
701 ben_dm_utility.error(SQLCODE,l_proc , '(none)','R');
702 raise;
703 end generate_insert_dml;
704
705 -- ------------------------- main ------------------------
706 -- ------------------------------------------------------------------------
707 procedure main
708 (
709 --p_business_group_id in number ,
710 p_table_alias in varchar2,
711 p_migration_id in number
712 )
713 is
714 l_header varchar2(32767);
715 l_body varchar2(32767);
716 l_header_comment varchar2(2048);
717 l_package_name varchar2(30) ;
718 l_generator_version hr_dm_tables.generator_version%type;
719 l_package_version varchar2(200);
720 l_index number := 1;
721 l_call_to_aol_proc varchar2(32767);
722 l_dev_key_local_var varchar2(32767);
723 l_csr_sql integer;
724 l_rows number;
725 l_proc_body_tbl t_varchar2_32k_tbl;
726 l_proc_index number := 1;
727
728
729 cursor c_tbl is
730 select TABLE_ID
731 ,TABLE_NAME
732 ,UPLOAD_TABLE_NAME
733 ,TABLE_ALIAS
734 ,DATETRACK
735 ,DERIVE_SQL
736 ,SURROGATE_PK_COLUMN_NAME
737 ,SHORT_NAME
738 ,LAST_GENERATED_DATE
739 ,GENERATOR_VERSION
740 ,SEQUENCE_NAME
741 ,LAST_UPDATE_DATE
742 from ben_dm_tables
743 where table_alias = p_table_alias
744 ;
745 l_tbl_rec ben_dm_gen_download.t_ben_dm_table ;
746
747
748
749
750 l_proc varchar2(75) ;
751 begin
752
753 l_proc := g_package||'main' ;
754 hr_utility.set_location('Entering:'||l_proc, 5);
755 ben_dm_utility.message('ROUT','entry:'||l_proc , 5);
756
757
758
759 -- open the table cursor and get the table informatons
760 open c_tbl ;
761 fetch c_tbl into l_tbl_rec ;
762 if c_tbl%NotFound then
763 close c_tbl ;
764 --raise ;
765 end if ;
766 close c_tbl ;
767
768
769 l_package_name := 'ben_dmu' || lower(l_tbl_rec.short_name ) ;
770
771 ben_dm_utility.message('PARA','(Table Name - '||l_tbl_rec.table_name|| ')', 10);
772
773 -- inialize the global package body pl/sql table by deleting all elements.
774 init_package_body;
775
776
777 -- Get the version of the generator to be appended to the TUPS package
778 -- generated for a table. This will help in finding out which version
779 -- of Generator is used to generate the TUPS 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_UPLOAD',
786 p_package_version => l_package_version,
787 p_version_type => 'FULL');
788
789
790
791
792 -- Start the package header and body.
793 begin
794 --
795 -- Set up initial parts of the package header and body.
796 --
797 l_header_comment := l_package_version || indent || '/*' || indent ||
798 ' * Generated by ben_dm_gun_upload at: ' ||
799 to_char( sysdate, 'YYYY/MM/DD HH24:MM:SS' ) || indent ||
800 ' * Generated Data Migrator TUPS for : ' || l_tbl_rec.table_name|| '.' ||
801 indent ||
802 ' * Generator Version Used to generate this TUPS is : ' || indent ||
803 l_generator_version || indent ||
804 ' */' || indent || '--' || indent;
805
806 l_header :=
807 'create or replace package ' || l_package_name || ' as' || indent ||
808 l_header_comment || indent;
809
810 -- add in call to ben_dm_utility.set_globals to set the global variables
811 l_header := l_header || indent || '--' || indent ||
812 '-- call to hr_dm_upload.set_globals to set the global variables'
813 || indent || '--' || indent ||
814 '--g_temp_var NUMBER := hr_dm_upload.set_globals;' || indent;
815
816
817 l_proc_body_tbl(1) :=
818 'create or replace package body ' || l_package_name || ' as' || indent ||
819 l_header_comment;
820
821 -- private package variable
822 l_proc_body_tbl(1) := l_proc_body_tbl(1) || indent || '--' || indent ||
823 '-- Package Variables' || indent ||
824 '--' || indent ||
825 'g_package varchar2(50) := ''' || l_package_name || '.'';' ||
826 indent;
827
828 -- add the body of this procedure to the package.
829 add_to_package_body( l_proc_body_tbl );
830
831
832
833 -- if the table has a columns which have a foreign key to AOL table then
834 -- generate the procedures so as to create the procedures to get the
835 -- corresponding developer's key for those columns.
836 generate_upload(p_table_info => l_tbl_rec,
837 p_header => l_header,
838 p_body => l_body
839 );
840
841
842
843 -- Generate the procedures and functions.
844 --
845
846 -- generate chk_row_exists procedure to download data into batch_lines.
847
848
849 l_body := l_body || indent || '--' || indent;
850 generate_insert_dml (p_table_info => l_tbl_rec );
851
852 -- For non date track table generate update_dml procedure to update the
853 -- row data. For date track create delete_dml procedure to delete the
854 -- data.
855
856
857
858 l_header := l_header || indent || '--' || indent;
859 l_body := l_body || indent || '--' || indent;
860
861 --
862 -- Terminate the package body and header.
863 --
864 l_header := l_header || 'end ' || l_package_name || ';';
865 l_body := l_body || 'end ' || l_package_name || ';';
866 exception
867 when plsql_value_error then
868 ben_dm_utility.error(SQLCODE,l_proc ,
869 'Either TDS package code size is too big or ' ||
870 ' a value error)',
871 'R');
872 raise;
873 end;
874
875 --
876 -- Compile the header and body.
877 --
878
879 hr_utility.set_location( 'calling run_sql for header ' , 99 ) ;
880
881 hr_dm_library.run_sql( l_header );
882
883 g_package_index := g_package_index+1;
884
885 g_package_body(g_package_index ) := indent(2) || 'Exception ' || indent(4) ||
886 'When others then ' ||indent(6) ||
887 ' ben_dm_utility.message(''INFO'','' Error '' ||substr(sqlerrm,1,100) ,5) ; '||
888 indent(6)|| ' Raise ; ' || indent(2) ||
889 'End upload ;' || indent || 'end ' ||
890 l_package_name || ';';
891
892 hr_utility.set_location( 'calling run_sql for body ' , 99 ) ;
893 hr_dm_library.run_sql( g_package_body,
894 g_package_index);
895
896 -- check the status of the package
897 begin
898 hr_dm_library.check_compile (p_object_name => l_package_name,
899 p_object_type => 'PACKAGE BODY' );
900 exception
901 when others then
902
903 hr_utility.set_location('Error :'||l_tbl_rec.table_name, 10);
904 ben_dm_utility.error(SQLCODE,'Error in compiling TUPS for ' ||
905 l_tbl_rec.table_name ,'(none)','R');
906 raise;
907 end;
908
909 hr_utility.set_location( 'Uplaod complteed '|| l_tbl_rec.table_name , 99 ) ;
910 -- update the generated version number
911 -- ben_dm_data_util.update_gen_version (p_table_id => l_tbl_rec.table_id
912 -- ,p_version => l_generator_version
913 -- ) ;
914 --
915
916 ben_dm_utility.message('ROUT','exit:'|| l_proc , 25);
917 hr_utility.set_location('Leaving:'||l_proc, 10);
918
919 exception
920 when others then
921 ben_dm_utility.error(SQLCODE,l_proc, '(none)','R');
922 raise;
923 end main ;
924
925 end BEN_DM_GEN_UPLOAD;