1 package body hr_dm_gen_main as
2 /* $Header: perdmgn.pkb 115.21 2004/03/24 08:28:33 mmudigon ship $ */
3 -- ------------------------- chk_long_column ------------------------
4 -- Description:
5 -- It check whether a table has a long column or not. It reurns
6 -- 'Y' - if table has a long column
7 -- 'N' - if table does not have long column
8 -- ------------------------------------------------------------------------
9 l_status varchar2(50);
10 l_industry varchar2(50);
11 l_per_owner varchar2(30);
12 l_ben_owner varchar2(30);
13 l_pay_owner varchar2(30);
14 l_ff_owner varchar2(30);
15 l_fnd_owner varchar2(30);
16
17 l_ret1 boolean := FND_INSTALLATION.GET_APP_INFO ('PAY', l_status,
18 l_industry, l_pay_owner);
19 l_ret2 boolean := FND_INSTALLATION.GET_APP_INFO ('BEN', l_status,
20 l_industry, l_ben_owner);
21 l_ret3 boolean := FND_INSTALLATION.GET_APP_INFO ('FF', l_status,
22 l_industry, l_ff_owner);
23 l_ret4 boolean := FND_INSTALLATION.GET_APP_INFO ('FND', l_status,
24 l_industry, l_fnd_owner);
25 l_ret5 boolean := FND_INSTALLATION.GET_APP_INFO ('PER', l_status,
26 l_industry, l_per_owner);
27 function chk_long_column
28 (
29 p_table_name varchar2
30 )
31 return varchar2 is
32 l_dummy varchar2(1);
33 l_return_flag varchar2(1);
34 l_apps_name varchar2(30);
35
36 cursor csr_apps_name is
37 select ORACLE_USERNAME
38 from fnd_oracle_userid
39 where ORACLE_ID = 900;
40
41 --
42 -- cursor to check whether table has column or table hierarchy by checking
43 -- the entreries in hr_dm_hierarchy table.
44
45 cursor csr_chk_long_column is
46 select 1
47 from all_tab_columns
48 where table_name = p_table_name
49 and data_type = 'LONG'
50 and owner in
51 (l_apps_name,
52 l_fnd_owner,
53 l_ff_owner,
54 l_ben_owner,
55 l_pay_owner,
56 l_per_owner);
57
58 begin
59 hr_dm_utility.message('ROUT','entry:hr_dm_gen_main.chk_long_column', 5);
60 hr_dm_utility.message('PARA','(p_table_name - ' || p_table_name ||
61 ')', 10);
62
63 open csr_apps_name;
64 fetch csr_apps_name into l_apps_name;
65 close csr_apps_name;
66
67
68 open csr_chk_long_column ;
69 fetch csr_chk_long_column into l_dummy;
70 if csr_chk_long_column%found then
71 l_return_flag := 'Y';
72 else
73 l_return_flag := 'N';
74 end if;
75 close csr_chk_long_column;
76
77 hr_dm_utility.message('INFO','HR_DM_GEN_MAIN - check whether table has any long column',15);
78
79 hr_dm_utility.message('ROUT','exit:hr_dm_gen_main.chk_long_column', 25);
80 hr_dm_utility.message('PARA','(l_return_flag - ' || l_return_flag || ')' ,30);
81 return l_return_flag;
82 exception
83 when others then
84 hr_dm_utility.error(SQLCODE,'hr_dm_gen_main.chk_long_column',
85 '(none)','R');
86 raise;
87 end chk_long_column;
88
89 -- ------------------------- get_hierarchy_info ------------------------
90 -- Description:
91 -- It check whether a table has a hierarchy for a given hierarchy type i.e
92 -- column hierarchy ('C') or table hierarchy 'T' or logical primary key ('P')
93 -- It returns 'Y' - if the given hierarchy type exists for the table.
94 -- 'N' - if the given hierarchy type does not exists
95 -- ------------------------------------------------------------------------
96 function get_hierarchy_info
97 (
98 p_table_id number,
99 p_hierarchy_type varchar2
100 )
101 return varchar2 is
102 l_dummy varchar2(1);
103 l_return_flag varchar2(1);
104 --
105 -- cursor to check whether table has column or table hierarchy by checking
106 -- the enteries in hr_dm_hierarchy table.
107
108 cursor csr_get_hierarchy_info (p_table_id number,
109 p_hierarchy_type varchar2) is
110 select 1
111 from hr_dm_hierarchies hir
112 where hir.table_id = p_table_id
113 and hir.hierarchy_type = p_hierarchy_type;
114
115 cursor csr_get_lr_info (p_table_id number,
116 p_hierarchy_type varchar2) is
117 select 1
118 from hr_dm_hierarchies hir,
119 hr_dm_tables t
120 where hir.table_id = t.table_id
121 and t.table_name = (
122 select nvl(upload_table_name, table_name)
123 from hr_dm_tables
124 where table_id = p_table_id)
125 and hir.hierarchy_type = p_hierarchy_type;
126
127
128 begin
129 hr_dm_utility.message('ROUT','entry:hr_dm_gen_main.get_hierarchy_info', 5);
130 hr_dm_utility.message('PARA','(p_table_id - ' || p_table_id ||
131 ')(p_hierarchy_type - ' || p_hierarchy_type ||
132 ')', 10);
133
134 if (p_hierarchy_type = 'R')
135 or (p_hierarchy_type = 'L') then
136
137 open csr_get_lr_info (p_table_id,
138 p_hierarchy_type);
139 fetch csr_get_lr_info into l_dummy;
140 if csr_get_lr_info%found then
141 l_return_flag := 'Y';
142 else
143 l_return_flag := 'N';
144 end if;
145 close csr_get_lr_info;
146
147 else
148
149 open csr_get_hierarchy_info (p_table_id,
150 p_hierarchy_type);
151 fetch csr_get_hierarchy_info into l_dummy;
152 if csr_get_hierarchy_info%found then
153 l_return_flag := 'Y';
154 else
155 l_return_flag := 'N';
156 end if;
157 close csr_get_hierarchy_info;
158
159 end if;
160
161 hr_dm_utility.message('INFO','HR_DM_GEN_MAIN - get whether a given hierarchy type'||
162 ' is valid for table ' ||
163 'in hr_dm_dt_deletes table ',15);
164
165 hr_dm_utility.message('ROUT','exit:hr_dm_gen_main.get_hierarchy_info',
166 25);
167 hr_dm_utility.message('PARA','(l_return_flag - ' || l_return_flag || ')' ,30);
168 return l_return_flag;
169 exception
170 when others then
171 hr_dm_utility.error(SQLCODE,'hr_dm_gen_main.get_hierarchy_info',
172 '(none)','R');
173 raise;
174 end get_hierarchy_info;
175
176 -- ------------------------- chk_ins_resolve_pk ------------------------
177 -- Description:
178 -- It checks whether a table has a child table with hierarchy type 'L'.
179 -- ------------------------------------------------------------------------
180 function chk_ins_resolve_pk
181 (
182 p_table_id in number
183 )
184 return varchar2 is
185 l_dummy varchar2(1);
186 l_return_flag varchar2(1);
187 --
188 -- cursor to check whether table has column or table hierarchy by checking
189 -- the entries in hr_dm_hierarchy table.
190
191 cursor csr_chk_ins_resolve_pk (p_table_id number) is
192 select 1
193 from hr_dm_hierarchies hir
194 where hir.hierarchy_type = 'L'
195 and hir.parent_table_id = (select table_id
196 from hr_dm_tables
197 where table_name = (
198 select nvl(upload_table_name, table_name)
199 from hr_dm_tables
200 where table_id = p_table_id));
201 begin
202 hr_dm_utility.message('ROUT','entry:hr_dm_gen_main.chk_ins_resolve_pk', 5);
203 hr_dm_utility.message('PARA','(p_table_id - ' || p_table_id ||
204 ')', 10);
205
206 open csr_chk_ins_resolve_pk (p_table_id);
207 fetch csr_chk_ins_resolve_pk into l_dummy;
208 if csr_chk_ins_resolve_pk%found then
209 l_return_flag := 'Y';
210 else
211 l_return_flag := 'N';
212 end if;
213 close csr_chk_ins_resolve_pk;
214
215 hr_dm_utility.message('INFO','HR_DM_GEN_MAIN - get whether a table has child table'||
216 ' with hierarchy type L',15);
217
218 hr_dm_utility.message('ROUT','exit:hr_dm_gen_main.chk_ins_resolve_pk',
219 25);
220 hr_dm_utility.message('PARA','(l_return_flag - ' || l_return_flag || ')' ,30);
221 return l_return_flag;
222 exception
223 when others then
224 hr_dm_utility.error(SQLCODE,'hr_dm_gen_main.chk_ins_resolve_pk',
225 '(none)','R');
226 raise;
227 end chk_ins_resolve_pk;
228
229 -- ------------------------- post_generate_validate --------------------------
230 -- Description:
231 -- This function is called immediately after Generate phase is marked as
232 -- completed. It checks following for each table listed in the Generate phase :
233 -- - If the status of TUPS or TDS pakage is invaild then it
234 -- - Generates the TUPS/TDS for the table. If it is still invalid
235 -- i.e TUPS/TDS generator staus is still invalid or any compilation
236 -- error, then it stops the processing.
237 -- - If there is no TUPS/TDS package then it Generates the package.
238 -- - If status of the phase item is other than 'C' then it generates the
239 -- TUPS/TDS for that table.
240 -- ------------------------------------------------------------------------
241 procedure post_generate_validate
242 (p_migration_id in number
243 )
244 is
245
246 l_dummy varchar2(1);
247 l_phase_item_id number;
248 l_tups_package varchar2(30);
249 l_tds_package varchar2(30);
250 l_apps_name varchar2(30);
251 l_text long;
252 l_view_name varchar2(30);
253 e_fatal_error EXCEPTION;
254 l_fatal_error_message VARCHAR2(200);
255 l_view_error varchar2(1);
256
257 -- cursor to select the tables in Generate phase for the given migration
258 cursor csr_get_table is
259 select tbl.table_id
260 ,upper(tbl.short_name) short_name
261 ,upper(tbl.table_name) table_name
262 ,itm.phase_item_id
263 ,phs.phase_id
264 from hr_dm_tables tbl,
265 hr_dm_phase_items itm,
266 hr_dm_phases phs
267 where phs.migration_id = p_migration_id
268 and phs.phase_name = 'R'
269 and phs.phase_id = itm.phase_id
270 and itm.table_name = tbl.table_name;
271
272 -- check whether the staus of the given table in Generate phase is other
273 -- than 'Complete'. If yes then it needs re-generating.
274 cursor csr_chk_generate_status (p_table_name varchar2)is
275 select '1'
276 from hr_dm_phase_items itm,
277 hr_dm_phases phs
278 where phs.migration_id = p_migration_id
279 and phs.phase_name = 'G'
280 and phs.phase_id = itm.phase_id
281 and itm.table_name = p_table_name
282 and itm.status = 'C';
283
284 -- check whether package bodu of TUPS/TDS is valid
285 cursor csr_chk_package_status is
286 select '1'
287 from user_objects tups,
288 user_objects tds
289 where tups.object_name = l_tups_package
290 and tups.object_type = 'PACKAGE BODY'
291 and tups.status = 'VALID'
292 and tds.object_name = l_tds_package
293 and tds.object_type = 'PACKAGE BODY'
294 and tds.status = 'VALID';
295
296 cursor csr_apps_name is
297 select ORACLE_USERNAME
298 from fnd_oracle_userid
299 where ORACLE_ID = 900;
300
301 cursor csr_view_info is
302 select av.view_name,
303 av.text,
304 itm.phase_item_id
305 from all_views av,
306 hr_dm_phase_items itm,
307 hr_dm_phases phs
308 where phs.migration_id = p_migration_id
309 and phs.phase_name = 'G'
310 and phs.phase_id = itm.phase_id
311 and itm.table_name = av.view_name
312 and av.view_name like 'HR_DMV%'
313 and av.view_name not like 'HR_DMVP%'
314 and av.view_name not like 'HR_DMVS%'
315 and av.owner = l_apps_name;
316
317
318
319 begin
320 -- return;
321 hr_dm_utility.message('ROUT','entry:hr_dm_gen_main.post_generate_validate', 5);
322 hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id ||
323 ')', 10);
324
325 for csr_get_table_rec in csr_get_table loop
326 l_phase_item_id := csr_get_table_rec.phase_item_id;
327
328 -- TUPS/TDS package names
329 l_tups_package := 'HRDMD_' || upper(csr_get_table_rec.short_name);
330 l_tds_package := 'HRDMU_' || upper(csr_get_table_rec.short_name);
331
332 -- check for the status of package
333 l_dummy := null;
334 open csr_chk_package_status;
335 fetch csr_chk_package_status into l_dummy;
336 close csr_chk_package_status;
337
338 if l_dummy is null then
339 --
340 -- if the row is not found then it means either TUPS/TDS does not
341 -- exist for this table or if they exist then the status is Invalid
342
343 -- try to recompile TDS and TUPS
344 execute immediate 'alter package ' || l_tds_package || ' compile';
345 execute immediate 'alter package ' || l_tups_package || ' compile';
346
347 -- see if this worked
348
349 open csr_chk_package_status;
350 fetch csr_chk_package_status into l_dummy;
351
352 if csr_chk_package_status%notfound then
353 -- Need to generate TUPS/TDS again.
354 close csr_chk_package_status;
355 -- call the package to generate TUPS/TDS for this table
356 slave_generator_for_tbl
357 ( p_phase_item_id => csr_get_table_rec.phase_item_id);
358 else
359 close csr_chk_package_status;
360 end if;
361
362 end if;
363
364 end loop;
365
366
367 -- now check HR_DMV% views
368 -- for inclusion of null business_group check in where clause
369 hr_dm_utility.message('INFO','checking HR_DMV% views',12);
370 l_view_error := 'N';
371 open csr_apps_name;
372 fetch csr_apps_name into l_apps_name;
373 close csr_apps_name;
374
375 open csr_view_info;
376 loop
377 fetch csr_view_info into l_view_name,
378 l_text,
379 l_phase_item_id;
380 exit when csr_view_info%notfound;
381
382 if instr(l_text, 'business_group_id is null') = 0 then
383 l_view_error := 'Y';
384 hr_dm_utility.message('INFO','The view ' || l_view_name
385 || ' is missing a business_group_id ' ||
386 'is null clause.',15);
387 hr_dm_utility.update_phase_items(p_new_status => 'E',
388 p_id => l_phase_item_id);
389 end if;
390 end loop;
391 close csr_view_info;
392
393 if l_view_error = 'Y' then
394 l_fatal_error_message := 'Errors were found in one or more HR_DMV%' ||
395 'view definitions due to a'
396 || ' missing business_group_id ' ||
397 'is null clause. Check log files for more'
398 || ' details.';
399 raise e_fatal_error;
400 end if;
401
402
403 hr_dm_utility.message('INFO','HR_DM_GEN_MAIN - post generate validation ',15);
404 hr_dm_utility.message('ROUT','exit:hr_dm_gen_main.post_generate_validate',
405 25);
406
407 -- error handling
408 exception
409 when e_fatal_error then
410 hr_dm_utility.error(SQLCODE,'hr_dm_gen_main.post_generate_validate',
411 l_fatal_error_message,'R');
412 hr_dm_utility.error(SQLCODE,'hr_dm_gen_main.post_generate_validate',
413 '(none)','R');
414 when others then
415 -- update status to error
416 hr_dm_utility.update_phase_items(p_new_status => 'E',
417 p_id => l_phase_item_id);
418 hr_dm_utility.error(SQLCODE,'hr_dm_gen_main.post_generate_validate','(none)','R');
419 end post_generate_validate;
420
421 -- ------------------------- slave_generator_for_tbl --------------------
422 -- Description:
423 -- It generates TUPS/TDS for a given table.It calls
424 -- TUPS Generator to generate TUPS for the table
425 -- Seed the data into data pump for TUPS.
426 -- TDS Generator to generate TDS for the table.
427 --
428 -- It is called by the post_generate_validate procedure.
429 --
430 -- PLEASE COPY THE CHANGES MAKE IN THIS PROCEDURE INTO SLAVE_GENERATOR PROCEDURE
434 (
431 -- DEFINED BELOW.
432 -- ------------------------------------------------------------------------
433 procedure slave_generator_for_tbl
435 p_phase_item_id in number
436 )
437 is
438
439 -- used for indexing of pl/sql table.
440 l_count number;
441
442 -- stores table properties or info and is passed to the TDS/TUPS generator.
443 l_table_info t_table_info;
444
445 -- stores columns and column data info.
446 l_columns_tbl hr_dm_library.t_varchar2_tbl;
447 l_parameters_tbl hr_dm_library.t_varchar2_tbl;
448
449 l_aol_columns_tbl hr_dm_library.t_varchar2_tbl;
450 l_aol_parameters_tbl hr_dm_library.t_varchar2_tbl;
451
452 l_fk_to_aol_columns_tbl t_fk_to_aol_columns_tbl;
453 l_phase_item_id hr_dm_phase_items.phase_item_id%type;
454 l_phase_id hr_dm_phases.phase_id%type;
455
456 l_generator_version hr_dm_tables.generator_version%type;
457
458 l_current_phase_status varchar2(30);
459 e_fatal_error exception;
460 l_fatal_error_message varchar2(200);
461 l_missing_who_info varchar2(1);
462
463 -- cursor to get table for which TUPS/TDS have to be genrated
464
465 cursor csr_get_table is
466 select tbl.table_id
467 ,lower(tbl.table_name) table_name
468 ,tbl.datetrack
469 ,decode (tbl.surrogate_pk_column_name,NULL,'N','Y') surrogate_primary_key
470 ,lower(tbl.surrogate_pk_column_name) surrogate_pk_column_name
471 ,lower(tbl.table_alias) table_alias
472 ,lower(tbl.short_name) short_name
473 ,itm.phase_item_id
474 ,lower(tbl.who_link_alias) who_link_alias
475 ,tbl.derive_sql_download_full
476 ,tbl.derive_sql_download_add
477 ,tbl.derive_sql_calc_ranges
478 ,tbl.derive_sql_delete_source
479 ,tbl.derive_sql_source_tables
480 ,tbl.derive_sql_chk_row_exists
481 ,tbl.derive_sql_chk_source_tables
482 ,tbl.use_distinct_download
483 ,tbl.always_check_row
484 ,upper(nvl(tbl.global_data,'N')) global_data
485 ,phs.migration_id
486 ,lower(nvl(tbl.upload_table_name, tbl.table_name)) upload_table_name
487 ,sequence_name
488 from hr_dm_tables tbl,
489 hr_dm_phases phs,
490 hr_dm_phase_items itm
491 where itm.phase_item_id = p_phase_item_id
492 and itm.phase_id = phs.phase_id
493 and itm.table_name = tbl.table_name;
494
495 cursor csr_get_table_hierarchy is
496 select distinct parent_table_id
497 from (select table_id,parent_table_id
498 from hr_dm_hierarchies
499 where hierarchy_type = 'PC')
500 start with table_id = l_table_info.table_id
501 connect by prior parent_table_id = table_id;
502
503 l_csr_get_table_rec csr_get_table%rowtype;
504 l_datetrack varchar2(1);
505 l_datetrack_parent varchar2(1);
506 l_parent_table_id number;
507
508 begin
509
510 hr_dm_utility.message('ROUT','entry:hr_dm_gen_main.slave_generator_for_tbl ', 5);
511 hr_dm_utility.message('PARA','(p_phase_item_id - ' || p_phase_item_id ||
512 ')', 10);
513 -- initialise the counter.
514 l_count := 1;
515 --
516 -- Get the table for which TUPS/TDS has to be generated
517 --
518 open csr_get_table;
519 fetch csr_get_table into l_csr_get_table_rec;
520 hr_dm_utility.message('INFO','Started Generating TUPS/TDS for ' ||
521 l_csr_get_table_rec.table_name || ', Table Id - ' ||
522 l_csr_get_table_rec.table_id,20);
523
524 hr_dm_utility.message('SUMM','Started Generating TUPS/TDS for ' ||
525 l_csr_get_table_rec.table_name || ', Table Id - ' ||
526 l_csr_get_table_rec.table_id,30);
527 l_phase_item_id := l_csr_get_table_rec.phase_item_id;
528 --l_phase_id := l_csr_get_table_rec.phase_id;
529
530 --
531 -- get status of generate phase. If phase has error status set by other slave
532 -- process then we need to stop the processing of this slave.
533 -- if null returned, then assume it is not started.
534 --
535 l_current_phase_status := nvl(hr_dm_utility.get_phase_status('G',
536 l_csr_get_table_rec.migration_id),
537 'NS');
538 -- if status is error, then raise an exception
539 if (l_current_phase_status = 'E') then
540 l_fatal_error_message := 'error in generator phase - slave exiting';
541 raise e_fatal_error;
542 end if;
543 -- update status to started
544 hr_dm_utility.update_phase_items(p_new_status => 'S',
545 p_id => l_phase_item_id);
546 --
547 -- store the information of the table properties into pl/sql record
548 --
549 l_table_info.table_id := l_csr_get_table_rec.table_id;
550 l_table_info.table_name := l_csr_get_table_rec.table_name;
551 l_table_info.datetrack := l_csr_get_table_rec.datetrack;
552 l_table_info.surrogate_primary_key :=
553 l_csr_get_table_rec.surrogate_primary_key;
554 l_table_info.surrogate_pk_column_name :=
555 l_csr_get_table_rec.surrogate_pk_column_name;
556 l_table_info.alias := l_csr_get_table_rec.table_alias;
557 l_table_info.short_name := l_csr_get_table_rec.short_name;
558 l_table_info.who_link_alias := l_csr_get_table_rec.who_link_alias;
559 l_table_info.derive_sql_download_full :=
560 l_csr_get_table_rec.derive_sql_download_full;
561 l_table_info.derive_sql_download_add :=
562 l_csr_get_table_rec.derive_sql_download_add;
566 l_csr_get_table_rec.derive_sql_delete_source;
563 l_table_info.derive_sql_calc_ranges :=
564 l_csr_get_table_rec.derive_sql_calc_ranges;
565 l_table_info.derive_sql_delete_source :=
567 l_table_info.derive_sql_source_tables :=
568 l_csr_get_table_rec.derive_sql_source_tables;
569 l_table_info.derive_sql_chk_source_tables :=
570 l_csr_get_table_rec.derive_sql_chk_source_tables;
571 l_table_info.derive_sql_chk_row_exists :=
572 l_csr_get_table_rec.derive_sql_chk_row_exists;
573 l_table_info.global_data := l_csr_get_table_rec.global_data;
574 l_table_info.upload_table_name := l_csr_get_table_rec.upload_table_name;
575 l_table_info.use_distinct_download := l_csr_get_table_rec.use_distinct_download;
576 l_table_info.always_check_row := l_csr_get_table_rec.always_check_row;
577 l_table_info.sequence_name := l_csr_get_table_rec.sequence_name;
578 --
579 -- get info about column hierarchy for the table
580 --
581 l_table_info.column_hierarchy := get_hierarchy_info(l_table_info.table_id,
582 'H');
583 --
584 -- get info about table hierarchy for the table
585 --
586 l_table_info.table_hierarchy := get_hierarchy_info(l_table_info.table_id,
587 'PC');
588 --
589 -- get info whether any column in table has a foreign key to AOL table
590 --
591 l_table_info.fk_to_aol_table := get_hierarchy_info(l_table_info.table_id,
592 'A');
593 --
594 -- get info whether the table has a primary key.
595 --
596 l_table_info.missing_primary_key := get_hierarchy_info(l_table_info.table_id,
597 'P');
598
599 --
600 -- get info whether to use columns defined in hr_dm_hierarchy table to form where
601 -- cluase of chk_row_exist procedur of TUPS
602 --
603 l_table_info.use_non_pk_col_for_chk_row := get_hierarchy_info(l_table_info.table_id,
604 'R');
605
606 --
607 -- get info whether to add the code in upload procedure of TUPS to check for the existence
608 -- of row in destination data for non global data table.
609 --
610 l_table_info.chk_row_exists_for_non_glb_tbl := get_hierarchy_info(l_table_info.table_id,
611 'C');
612
613 --
614 -- get info whether to add the code in upload procedure of TUPS to resolve the primary key
615 --
616 if (l_table_info.column_hierarchy = 'Y') and
617 (l_table_info.use_non_pk_col_for_chk_row = 'Y') then
618 l_table_info.resolve_pk := 'Y';
619 end if;
620
621 if nvl(l_table_info.resolve_pk,'N') <> 'Y' then
622 l_table_info.resolve_pk := get_hierarchy_info(l_table_info.table_id,
623 'L');
624 end if;
625
626 --
627 -- get the info whether insert into hr_dm_resolve_pk table is allowed or not.
628 -- it is allowed if the table has any child table seeded in the hr_dm_hierarchies
629 -- table with hierarchy type 'L'.
630 --
631 l_table_info.ins_resolve_pk := chk_ins_resolve_pk (l_table_info.table_id);
632
633 -- check whether to use distinct clause in the TDS download cursor. It will be used
634 -- if it satisfies all the below conditions
635 --
636 -- (
637 -- o tables has a AOL hierarchy i.e hierarchy type = 'A'
638 -- OR
639 -- o table has a parent table which is datetracked
640 -- )
641 -- AND
642 -- (
643 -- o has a table hierarchy i.e hierarchy type = 'PC'
644 -- o does not have 'long' data type.
645 -- )
646
647 -- If the first two conditions are met and table has along data type then show the
648 -- error message as distinct clause must be used for table which refrences AOL
649 -- data. At present there are no such cases.
650 -- In such cases use the derive_sql so as to eliminate the use of distinct clause
651
652 l_table_info.use_distinct := 'N';
653
654 -- test for AOL hierarchy
655 if l_table_info.fk_to_aol_table = 'Y' and
656 l_table_info.table_hierarchy = 'Y' and
657 l_table_info.derive_sql_download_full is null then
658
659 -- check whether table has a long column
660 if chk_long_column(l_table_info.table_name) = 'N' then
661 l_table_info.use_distinct := 'Y';
662 else
663 -- cannot use distinct. raise error.
664 l_fatal_error_message := 'This table has a AOL type hierarchy and has a long column.' ||
665 'Define the where clause for this table using derive_sql to ' ||
666 'generate this table.';
667 raise e_fatal_error;
668 end if;
669 end if;
670
671 -- test for datetracked parent case
672 if l_table_info.table_hierarchy = 'Y' and
673 l_table_info.derive_sql_download_full is null then
674
675 -- see if we have a parent which is date tracked
676 -- based on code from hr_dm_gen_tds.get_cursor_from_clause
677 l_datetrack_parent := 'N';
678
679 open csr_get_table_hierarchy;
680 loop
681 fetch csr_get_table_hierarchy into l_parent_table_id;
682 exit when csr_get_table_hierarchy%notfound;
683 select datetrack
684 into l_datetrack
685 from hr_dm_tables
686 where table_id = l_parent_table_id;
687 if (l_datetrack = 'Y') then
688 l_datetrack_parent := 'Y';
689 end if;
690 end loop;
691 close csr_get_table_hierarchy;
692
693 if (l_datetrack_parent = 'Y') then
694
698 else
695 -- check whether table has a long column
696 if chk_long_column(l_table_info.table_name) = 'N' then
697 l_table_info.use_distinct := 'Y';
699 -- cannot use distinct. raise error.
700 l_fatal_error_message := 'This table has a datetracked parent and has a long column.' ||
701 'Define the where clause for this table using derive_sql to ' ||
702 'generate this table.';
703 raise e_fatal_error;
704 end if;
705 end if;
706 end if;
707
708 -- if one or more columns has a foreign key to the AOL table then store the
709 -- information about the column and corresponding AOL table e.t.c for each
710 -- column.
711 if l_table_info.fk_to_aol_table = 'Y' then
712 hr_dm_library.populate_fk_to_aol_cols_info
713 ( p_table_info => l_table_info,
714 p_fk_to_aol_columns_tbl => l_fk_to_aol_columns_tbl);
715 end if;
716
717 -- get the columns and parameter list. store in pl/sql table.
718 hr_dm_library.populate_columns_list(l_table_info,
719 l_fk_to_aol_columns_tbl,
720 l_columns_tbl,
721 l_parameters_tbl,
722 l_aol_columns_tbl,
723 l_aol_parameters_tbl,
724 l_missing_who_info);
725
726 l_table_info.missing_who_info := l_missing_who_info;
727
728 hr_dm_utility.message('INFO',' Information about ' ||
729 'l_table_info.table_name :' ||
730 ')(datetrack - ' || l_table_info.datetrack ||
731 ')(surrogate_primary_key - ' || l_table_info.surrogate_primary_key ||
732 ')(surrogate_pk_column_name - ' || l_table_info.surrogate_pk_column_name ||
733 '(global_data - ' || l_table_info.global_data ||
734 ')(derive_sql_source_tables - ' || l_table_info.derive_sql_source_tables ||
735 ')(who_link_alias - ' || l_table_info.who_link_alias ||
736 ')(missing_who_info - ' || l_table_info.missing_who_info ||
737 ')(fk_to_aol_table - ' || l_table_info.fk_to_aol_table ||
738 ')(column_hierarchy - ' || l_table_info.column_hierarchy ||
739 ')(table_hierarchy - ' || l_table_info.table_hierarchy,40);
740
741
742 -- if who columns are not there in the table then check whether the table
743 -- has either deive_sql or who-link alias for the where clause. If both
744 -- of them are not there then TDS cannot be generated for this table. stop
745 -- processing.
746
747 if (l_missing_who_info = 'Y' and
748 l_table_info.who_link_alias is null and
749 l_table_info.derive_sql_download_full is null and
750 l_table_info.global_data = 'N') then
751 l_fatal_error_message := 'error in slave generator - ' ||
752 l_table_info.table_name || ' does not have WHO column. Either define' ||
753 ' where clause for this table or define the WHO link alias. Exiting ' ||
754 'slave';
755 raise e_fatal_error;
756 end if;
757
758 --
759 -- Call TUPS genrator to create TUPS for the table
760 --
761 hr_dm_utility.message('INFO',' Started Generating TUPS for ' ||
762 l_table_info.table_name,50);
763 hr_dm_gen_tups.create_tups_pacakge (l_table_info,
764 l_columns_tbl,
765 l_parameters_tbl,
766 l_aol_columns_tbl,
767 l_aol_parameters_tbl,
768 l_fk_to_aol_columns_tbl);
769 hr_dm_utility.message('INFO',' Successfully Generated TUPS for ' ||
770 l_table_info.table_name,60);
771 --
772 -- Seed the data for TUPS into data pump table.
773 --
774 hr_dm_utility.message('INFO',' Started seeding data into ' ||
775 'Data Pump tables for ' || l_table_info.table_name,70);
776 hr_dm_seed_dp.main (l_table_info ,
777 l_columns_tbl);
778 hr_dm_utility.message('INFO',' Successfully seeded data into ' ||
779 'Data Pump tables for ' || l_table_info.table_name,80);
780 --
781 -- Call TDS generator to create TDS for the table
782 --
783
784 hr_dm_utility.message('INFO',' Started Generating TDS for ' ||
785 l_table_info.table_name,90);
786 hr_dm_gen_tds.create_tds_pacakge (l_table_info,
787 l_columns_tbl,
788 l_parameters_tbl,
789 l_aol_columns_tbl,
790 l_aol_parameters_tbl,
791 l_fk_to_aol_columns_tbl);
792 hr_dm_utility.message('INFO',' Successfully Generated TDS for ' ||
793 l_table_info.table_name,100);
794
795 l_count := l_count + 1;
796
797 -- get generator version used to generated this TUPS/TDS
798 hr_dm_library.get_generator_version(l_generator_version);
799
800 --
801 -- update the last generated date for TUP/TDS for this table in hr_dm_tables
802 --
803 update hr_dm_tables
804 set last_generated_date = sysdate,
805 generator_version = l_generator_version
806 where table_id = l_csr_get_table_rec.table_id;
807
808 -- update status to completed
809 hr_dm_utility.update_phase_items(p_new_status => 'C',
810 p_id => l_phase_item_id);
811
812 hr_dm_utility.message('INFO','Generated TUPS/TDS succesfully for ' ||
816 hr_dm_utility.message('SUMM','Generated TUPS/TDS successfully for ' ||
813 l_csr_get_table_rec.table_name || ', Table Id - ' ||
814 l_csr_get_table_rec.table_id,110);
815
817 l_csr_get_table_rec.table_name || ', Table Id - ' ||
818 l_csr_get_table_rec.table_id,115);
819 close csr_get_table;
820
821 hr_dm_utility.message('ROUT','exit:hr_dm_gen_main.slave_generator_for_tbl',
822 120);
823
824 -- error handling
825 exception
826 when e_fatal_error then
827 if csr_get_table%isopen then
828 close csr_get_table;
829 end if;
830 hr_dm_utility.error(SQLCODE,'hr_dm_gen_main.slave_generator_for_tbl',
831 l_fatal_error_message,'R');
832 hr_dm_utility.update_phase_items(p_new_status => 'E',
833 p_id => l_phase_item_id);
834
835 hr_dm_utility.error(SQLCODE,'hr_dm_gen_main.slave_generator_for_tbl','(none)','R');
836 when others then
837 if csr_get_table%isopen then
838 close csr_get_table;
839 end if;
840 -- update status to error
841 hr_dm_utility.update_phase_items(p_new_status => 'E',
842 p_id => l_phase_item_id);
843 hr_dm_utility.error(SQLCODE,'hr_dm_gen_main.slave_generator_for_tbl','(none)','R');
844
845 end slave_generator_for_tbl ;
846
847 -- ------------------------- slave_generator --------------------------
848 -- Description:
849 -- It generates the TUPS/TDS for all the tables in Generate phase for a given
850 -- migration Id.
851 -- It reads the unprocessed table from Phase_Item table.It calls
852 -- TUPS Generator to generate TUPS for the table
853 -- Seed the data into data pump for TUPS.
854 -- TDS Generator to generate TDS for the table.
855 -- Input Parameters :
856 -- p_migration_id - ID of the migration. Primary Key of
857 -- HR_DM_MIGRATIONS table.
858 -- p_concurrent_process - Can have following values :
859 -- 'Y' - Migration is run as a concurrent process
860 -- so create a log file.
861 -- 'N' - Migration is not run from concurrent
862 -- process,so don't create a log file.
863 -- p_last_migration_date - This parameter is added so as to have generic
864 -- master program which spawns slave processes.
865 -- This process does not use this parameter.
866 -- p_process_number - To prevent the locking issue each slave process
867 -- will be passed the process number by master.
868 -- Main cursor has been modified so as a row is
869 -- processed by one process only. This is achieve
870 -- by following:
871 -- MOD (primary_key, total_no_of_threads/slave_processes) + 1 = p_process_number
872 --
873 -- Output Parameters
874 -- errbuf - buffer for output message (for CM manager)
875 -- retcode - program return code (for CM manager)
876 --
877 -- PLEASE COPY THE CHANGES MAKE IN THIS PROCEDURE INTO SLAVE_GENERATOR_FOR_TBL PROCEDURE
878 -- DEFINED ABOVE.
879 -- ------------------------------------------------------------------------
880 procedure slave_generator
881 (
882 errbuf out nocopy varchar2,
883 retcode out nocopy number ,
884 p_migration_id in number ,
885 p_concurrent_process in varchar2 default 'Y',
886 p_last_migration_date in date,
887 p_process_number in number
888 )
889 is
890
891 -- used for indexing of pl/sql table.
892 l_count number;
893
894 -- stores table properties or info and is passed to the TDS/TUPS generator.
895 l_table_info t_table_info;
896
897 -- stores columns and column data info.
898 l_columns_tbl hr_dm_library.t_varchar2_tbl;
899 l_parameters_tbl hr_dm_library.t_varchar2_tbl;
900
901 l_generator_version hr_dm_tables.generator_version%type;
902
903 l_aol_columns_tbl hr_dm_library.t_varchar2_tbl;
904 l_aol_parameters_tbl hr_dm_library.t_varchar2_tbl;
905
906 l_fk_to_aol_columns_tbl t_fk_to_aol_columns_tbl;
907 l_phase_item_id hr_dm_phase_items.phase_item_id%type;
908 l_phase_id hr_dm_phases.phase_id%type;
909
910 l_current_phase_status varchar2(30);
911 e_fatal_error exception;
912 e_fatal_error2 exception;
913 l_fatal_error_message varchar2(200);
914 l_missing_who_info varchar2(1);
915 l_aol_counter number;
916 l_business_group_id number;
917 l_no_of_threads number;
918
919 -- get the migration details
920 cursor csr_migration_info is
921 select business_group_id
922 from hr_dm_migrations
923 where migration_id = p_migration_id;
924
925
926 -- cursor to get table for which TUPS/TDS have to be genrated
927
928 cursor csr_get_table is
929 select tbl.table_id
930 ,lower(tbl.table_name) table_name
931 ,tbl.datetrack
932 ,decode (tbl.surrogate_pk_column_name,NULL,'N','Y') surrogate_primary_key
933 ,lower(tbl.surrogate_pk_column_name) surrogate_pk_column_name
934 ,lower(tbl.table_alias) table_alias
935 ,lower(tbl.short_name) short_name
936 ,itm.phase_item_id
937 ,phs.phase_id
938 ,lower(tbl.who_link_alias) who_link_alias
939 ,tbl.derive_sql_download_full
940 ,tbl.derive_sql_download_add
941 ,tbl.derive_sql_calc_ranges
942 ,tbl.derive_sql_delete_source
943 ,tbl.derive_sql_source_tables
944 ,tbl.derive_sql_chk_row_exists
948 ,upper(nvl(tbl.global_data,'N')) global_data
945 ,tbl.derive_sql_chk_source_tables
946 ,tbl.use_distinct_download
947 ,tbl.always_check_row
949 ,lower(nvl(tbl.upload_table_name, tbl.table_name)) upload_table_name
950 ,sequence_name
951 from hr_dm_tables tbl,
952 hr_dm_phase_items itm,
953 hr_dm_phases phs
954 where phs.migration_id = p_migration_id
955 and phs.phase_name = 'G'
956 and phs.phase_id = itm.phase_id
957 and mod(itm.phase_item_id,l_no_of_threads) + 1 = p_process_number
958 and itm.status = 'NS'
959 --and itm.status in ('NS','E')
960 and itm.table_name = tbl.table_name
961 and rownum < 2;
962 -- for update of itm.status;
963 /*
964 for update of itm.status,
965 phs.phase_name,
966 tbl.table_name;
967 */
968
969 l_csr_get_table_rec csr_get_table%rowtype;
970
971
972 cursor csr_get_table_hierarchy is
973 select distinct parent_table_id
974 from (select table_id,parent_table_id
975 from hr_dm_hierarchies
976 where hierarchy_type = 'PC')
977 start with table_id = l_table_info.table_id
978 connect by prior parent_table_id = table_id;
979
980 cursor csr_col_hier
981 (p_table_id number) is
982 select count(*)
983 from hr_dm_hierarchies h
984 where h.table_id = p_table_id
985 and h.hierarchy_type ='A'
986 and h.column_name not in ('CREATED_BY','LAST_UPDATED_BY');
987
988 l_datetrack varchar2(1);
989 l_datetrack_parent varchar2(1);
990 l_parent_table_id number;
991
992
993
994
995 begin
996
997 -- initialize messaging
998 if p_concurrent_process = 'Y' then
999 hr_dm_utility.message_init;
1000 end if;
1001
1002 hr_dm_utility.message('ROUT','entry:hr_dm_gen_main.slave_generator', 5);
1003 hr_dm_utility.message('PARA','(errbuf - ' || errbuf ||
1004 ')(retcode - ' || retcode ||
1005 ')(p_migration_id - ' || p_migration_id ||
1006 ')(p_concurrent_process - ' || p_concurrent_process ||
1007 ')(p_last_migration_date - '|| p_last_migration_date ||
1008 ')', 10);
1009
1010 -- get the business_group_id and migration_type
1011 open csr_migration_info;
1012 fetch csr_migration_info into l_business_group_id;
1013 if csr_migration_info%notfound then
1014 close csr_migration_info;
1015 l_fatal_error_message := 'hr_dm_download.main :- Migration Id ' ||
1016 to_char(p_migration_id) || ' not found.';
1017 raise e_fatal_error2;
1018 end if;
1019 close csr_migration_info;
1020
1021 l_no_of_threads := hr_dm_utility.number_of_threads(l_business_group_id);
1022
1023 -- initialise the counter.
1024 l_count := 1;
1025 --
1026 -- Get the table for which TUPS/TDS has to be generated
1027 --
1028 loop
1029 l_phase_item_id := NULL;
1030
1031 --
1032 -- get status of generate phase. If phase has error status set by other slave
1033 -- process then we need to stop the processing of this slave.
1034 -- if null returned, then assume it is not started.
1035 --
1036 l_current_phase_status := nvl(hr_dm_utility.get_phase_status('G',
1037 p_migration_id),
1038 'NS');
1039
1040 -- if status is error, then raise an exception
1041 if (l_current_phase_status = 'E') then
1042 l_fatal_error_message := 'Encountered error in generator phase caused by ' ||
1043 'another process - slave exiting';
1044 raise e_fatal_error2;
1045 end if;
1046
1047 open csr_get_table;
1048 fetch csr_get_table into l_csr_get_table_rec;
1049 if csr_get_table%notfound then
1050 close csr_get_table;
1051 exit;
1052 end if;
1053
1054 -- update status to started
1055 hr_dm_utility.update_phase_items(p_new_status => 'S',
1056 p_id => l_csr_get_table_rec.phase_item_id);
1057
1058 l_phase_item_id := l_csr_get_table_rec.phase_item_id;
1059 l_phase_id := l_csr_get_table_rec.phase_id;
1060
1061 close csr_get_table;
1062
1063 hr_dm_utility.message('INFO','Started Generating TUPS/TDS for ' ||
1064 l_csr_get_table_rec.table_name || ', Table Id - ' ||
1065 l_csr_get_table_rec.table_id,20);
1066
1067 hr_dm_utility.message('SUMM','Started Generating TUPS/TDS for ' ||
1068 l_csr_get_table_rec.table_name || ', Table Id - ' ||
1069 l_csr_get_table_rec.table_id,30);
1070 --
1071 -- store the information of the table properties into pl/sql record
1072 --
1073 l_table_info.migration_id := p_migration_id;
1074 l_table_info.table_id := l_csr_get_table_rec.table_id;
1075 l_table_info.table_name := l_csr_get_table_rec.table_name;
1076 l_table_info.datetrack := l_csr_get_table_rec.datetrack;
1077 l_table_info.surrogate_primary_key :=
1078 l_csr_get_table_rec.surrogate_primary_key;
1079 l_table_info.surrogate_pk_column_name :=
1080 l_csr_get_table_rec.surrogate_pk_column_name;
1081 l_table_info.alias := l_csr_get_table_rec.table_alias;
1082 l_table_info.short_name := l_csr_get_table_rec.short_name;
1083
1084 l_table_info.who_link_alias := l_csr_get_table_rec.who_link_alias;
1085 l_table_info.derive_sql_download_full :=
1086 l_csr_get_table_rec.derive_sql_download_full;
1090 l_csr_get_table_rec.derive_sql_calc_ranges;
1087 l_table_info.derive_sql_download_add :=
1088 l_csr_get_table_rec.derive_sql_download_add;
1089 l_table_info.derive_sql_calc_ranges :=
1091 l_table_info.derive_sql_delete_source :=
1092 l_csr_get_table_rec.derive_sql_delete_source;
1093 l_table_info.derive_sql_source_tables :=
1094 l_csr_get_table_rec.derive_sql_source_tables;
1095 l_table_info.derive_sql_chk_source_tables :=
1096 l_csr_get_table_rec.derive_sql_chk_source_tables;
1097 l_table_info.derive_sql_chk_row_exists :=
1098 l_csr_get_table_rec.derive_sql_chk_row_exists;
1099
1100 l_table_info.global_data := l_csr_get_table_rec.global_data;
1101 l_table_info.upload_table_name := l_csr_get_table_rec.upload_table_name;
1102 l_table_info.use_distinct_download := l_csr_get_table_rec.use_distinct_download;
1103 l_table_info.always_check_row := l_csr_get_table_rec.always_check_row;
1104 l_table_info.sequence_name := l_csr_get_table_rec.sequence_name;
1105
1106 --
1107 -- get info about column hierarchy for the table
1108 --
1109 l_table_info.column_hierarchy := get_hierarchy_info(l_table_info.table_id,
1110 'H');
1111
1112 --
1113 -- get info about table hierarchy for the table
1114 --
1115 l_table_info.table_hierarchy := get_hierarchy_info(l_table_info.table_id,
1116 'PC');
1117 --
1118 -- get info whether any column in table has a foreign key to AOL table
1119 --
1120 l_table_info.fk_to_aol_table := get_hierarchy_info(l_table_info.table_id,
1121 'A');
1122
1123 --
1124 -- get info whether the table has a primary key.
1125 --
1126 l_table_info.missing_primary_key := get_hierarchy_info(l_table_info.table_id,
1127 'P');
1128 --
1129 -- get info whether to use columns defined in hr_dm_hierarchy table to form where
1130 -- cluase of chk_row_exist procedur of TUPS
1131 --
1132 l_table_info.use_non_pk_col_for_chk_row := get_hierarchy_info(l_table_info.table_id,
1133 'R');
1134 --
1135 -- get info whether to add the code in upload procedure of TUPS to check for the existence
1136 -- of row in destination data for non global data table.
1137 --
1138 l_table_info.chk_row_exists_for_non_glb_tbl := get_hierarchy_info(l_table_info.table_id,
1139 'C');
1140
1141 --
1142 -- get info whether to add the code in upload procedure of TUPS to resolve the primary key
1143 --
1144 if (l_table_info.column_hierarchy = 'Y') and
1145 (l_table_info.use_non_pk_col_for_chk_row = 'Y') then
1146 l_table_info.resolve_pk := 'Y';
1147 end if;
1148
1149 if nvl(l_table_info.resolve_pk,'N') <> 'Y' then
1150 l_table_info.resolve_pk := get_hierarchy_info(l_table_info.table_id,
1151 'L');
1152 end if;
1153 --
1154 -- get the info whether insert into hr_dm_resolve_pk table is allowed or not.
1155 -- it is allowed if the table has any child table seeded in the hr_dm_hierarchies
1156 -- table with hierarchy type 'L'.
1157 --
1158 l_table_info.ins_resolve_pk := chk_ins_resolve_pk(l_table_info.table_id);
1159
1160 -- check whether to use distinct clause in the TDS download cursor. It will be used
1161 -- if it satisfies all the below conditions
1162 --
1163 -- (
1164 -- o tables has a AOL hierarchy i.e hierarchy type = 'A'
1165 -- OR
1166 -- o table has a parent table which is datetracked
1167 -- )
1168 -- AND
1169 -- (
1170 -- o has a table hierarchy i.e hierarchy type = 'PC'
1171 -- o does not have 'long' data type.
1172 -- )
1173
1174 -- If the first two conditions are met and table has along data type then show the
1175 -- error message as distinct clause must be used for table which refrences AOL
1176 -- data. At present there are no such cases.
1177 -- In such cases use the derive_sql so as to eliminate the use of distinct clause
1178
1179 l_table_info.use_distinct := 'N';
1180
1181 if l_table_info.fk_to_aol_table = 'Y' and
1182 l_table_info.table_hierarchy = 'Y' and
1183 l_table_info.derive_sql_download_full is null then
1184
1185 -- check whether table has a long column
1186 if chk_long_column(l_table_info.table_name) = 'N' then
1187 l_table_info.use_distinct := 'Y';
1188 else
1189 -- cannot use distinct. raise error.
1190 l_fatal_error_message := 'This table has a AOL type hierarchy and has a long column.' ||
1191 'Define the where clause for this table using derive_sql to ' ||
1192 'generate this table.';
1193 raise e_fatal_error;
1194 end if;
1195 end if;
1196
1197 -- test for datetracked parent case
1198 if l_table_info.table_hierarchy = 'Y' and
1199 l_table_info.derive_sql_download_full is null then
1200
1201 -- see if we have a parent which is date tracked
1202 -- based on code from hr_dm_gen_tds.get_cursor_from_clause
1203 l_datetrack_parent := 'N';
1204
1205 open csr_get_table_hierarchy;
1206 loop
1207 fetch csr_get_table_hierarchy into l_parent_table_id;
1208 exit when csr_get_table_hierarchy%notfound;
1209 select datetrack
1210 into l_datetrack
1211 from hr_dm_tables
1212 where table_id = l_parent_table_id;
1213 if (l_datetrack = 'Y') then
1214 l_datetrack_parent := 'Y';
1215
1216 end if;
1217 end loop;
1221 -- check whether table has a long column
1218 close csr_get_table_hierarchy;
1219
1220 if (l_datetrack_parent = 'Y') then
1222 if chk_long_column(l_table_info.table_name) = 'N' then
1223 l_table_info.use_distinct := 'Y';
1224 else
1225 -- cannot use distinct. raise error.
1226 l_fatal_error_message := 'This table has a datetracked parent and has a long column.' ||
1227 'Define the where clause for this table using derive_sql to ' ||
1228 'generate this table.';
1229 raise e_fatal_error;
1230 end if;
1231 end if;
1232 end if;
1233
1234 -- if one or more columns has a foreign key to the AOL table then store the
1235 -- information about the column and corresponding AOL table e.t.c for each
1236 -- column.
1237 if l_table_info.fk_to_aol_table = 'Y' then
1238 hr_dm_library.populate_fk_to_aol_cols_info
1239 ( p_table_info => l_table_info,
1240 p_fk_to_aol_columns_tbl => l_fk_to_aol_columns_tbl);
1241
1242 -- if the error below is raised we need to make modifications to
1243 -- generate_upload_hierarchy (perdmgnu.pkb) to handle 'A' hierarchy
1244 if l_table_info.column_hierarchy = 'Y' then
1245 open csr_col_hier
1246 (l_table_info.table_id);
1247 fetch csr_col_hier into l_aol_counter;
1248 close csr_col_hier;
1249 if (l_aol_counter > 0) then
1250 l_fatal_error_message := 'AOL column names must be created_by or last_updated_by for a table with H hierarchy.'||
1251 'Other names are not currently handled. Please contact Oracle Support.';
1252 raise e_fatal_error;
1253 end if;
1254 end if;
1255
1256 end if;
1257
1258 -- get the columns and parameter list. store in pl/sql table.
1259 hr_dm_library.populate_columns_list(l_table_info,
1260 l_fk_to_aol_columns_tbl,
1261 l_columns_tbl,
1262 l_parameters_tbl,
1263 l_aol_columns_tbl,
1264 l_aol_parameters_tbl,
1265 l_missing_who_info);
1266
1267
1268 l_table_info.missing_who_info := l_missing_who_info;
1269
1270 hr_dm_utility.message('INFO',' Information about ' ||
1271 'l_table_info.table_name :' ||
1272 ')(datetrack - ' || l_table_info.datetrack ||
1273 ')(surrogate_primary_key - ' || l_table_info.surrogate_primary_key ||
1274 ')(surrogate_pk_column_name - ' || l_table_info.surrogate_pk_column_name ||
1275 '(global_data - ' || l_table_info.global_data ||
1276 ')(derive_sql_source_tables - ' || l_table_info.derive_sql_source_tables ||
1277 ')(who_link_alias - ' || l_table_info.who_link_alias ||
1278 ')(missing_who_info - ' || l_table_info.missing_who_info ||
1279 ')(fk_to_aol_table - ' || l_table_info.fk_to_aol_table ||
1280 ')(column_hierarchy - ' || l_table_info.column_hierarchy ||
1281 ')(table_hierarchy - ' || l_table_info.table_hierarchy,40);
1282
1283
1284 -- if who columns are not there in the table then check whether the table
1285 -- has either deive_sql or who-link alias for the where clause. If both
1286 -- of them are not there then TDS cannot be generated for this table. stop
1287 -- processing.
1288
1289 if (l_missing_who_info = 'Y' and
1290 l_table_info.who_link_alias is null and
1291 l_table_info.derive_sql_download_full is null and
1292 l_table_info.global_data = 'N') then
1293
1294 l_fatal_error_message := 'error in slave generator - ' ||
1295 l_table_info.table_name || ' does not have WHO column. Either define' ||
1296 ' where clause for this table or define the WHO link alias. Exiting ' ||
1297 'slave';
1298 raise e_fatal_error;
1299 end if;
1300
1301 --
1302 -- if the table has 'R' type and 'A' type hierarchy then the table has a unique
1303 -- constraint and one of the column of the unique constraint has a foreign key
1304 -- on another table which has a unique constraint. In this case the table must
1305 -- have surrggate primary key column.
1306 --
1307 /*
1308
1309 -- check disabled to enable WHO column migration via A type hierarchy
1310
1311 if l_table_info.surrogate_pk_column_name is null and
1312 l_table_info.use_non_pk_col_for_chk_row = 'Y' and
1313 l_table_info.fk_to_aol_table = 'Y' then
1314
1315 l_fatal_error_message := 'error in slave generator - ' ||
1316 l_table_info.table_name || ' does not have surrogate primary column. Define' ||
1317 ' the surrogate primary key column . Exiting ' ||
1318 'slave';
1319 raise e_fatal_error;
1320 end if;
1321
1322 */
1323
1324 -- if the source table is different from destination table then it is assumed
1325 -- source table is a view which needs to be generated.
1326
1327 if l_table_info.upload_table_name <> l_table_info.table_name then
1328 begin
1329
1330 hr_dm_library.create_view(l_table_info);
1331
1332 exception
1333 when others then
1334 l_fatal_error_message := 'error in hr_dm_utility.create_view - for ' ||
1335 l_table_info.table_name || '. Exiting slave';
1336 raise e_fatal_error;
1337 end;
1338 end if;
1339
1340 --
1341 -- Call TUPS genrator to create TUPS for the table
1342 --
1343 hr_dm_utility.message('INFO',' Started Generating TUPS for ' ||
1344 l_table_info.table_name,50);
1345
1346 hr_dm_gen_tups.create_tups_pacakge (l_table_info,
1350 l_aol_parameters_tbl,
1347 l_columns_tbl,
1348 l_parameters_tbl,
1349 l_aol_columns_tbl,
1351 l_fk_to_aol_columns_tbl);
1352
1353 hr_dm_utility.message('INFO',' Successfully Generated TUPS for ' ||
1354 l_table_info.table_name,60);
1355 --
1356 -- Seed the data for TUPS into data pump table.
1357 --
1358 hr_dm_utility.message('INFO',' Started seeding data into ' ||
1359 'Data Pump tables for ' || l_table_info.table_name,70);
1360
1361
1362 hr_dm_seed_dp.main (l_table_info ,
1363 l_columns_tbl);
1364 hr_dm_utility.message('INFO',' Successfully seeded data into ' ||
1365 'Data Pump tables for ' || l_table_info.table_name,80);
1366 --
1367 -- Call TDS generator to create TDS for the table
1368 --
1369 hr_dm_utility.message('INFO',' Started Generating TDS for ' ||
1370 l_table_info.table_name,90);
1371
1372
1373 hr_dm_gen_tds.create_tds_pacakge (l_table_info,
1374 l_columns_tbl,
1375 l_parameters_tbl,
1376 l_aol_columns_tbl,
1377 l_aol_parameters_tbl,
1378 l_fk_to_aol_columns_tbl);
1379 hr_dm_utility.message('INFO',' Successfully Generated TDS for ' ||
1380 l_table_info.table_name,100);
1381
1382 l_count := l_count + 1;
1383
1384 -- get generator version used to generated this TUPS/TDS
1385 hr_dm_library.get_generator_version(l_generator_version);
1386 --
1387 -- update the last generated date for TUP/TDS for this table in hr_dm_tables
1388 --
1389 update hr_dm_tables
1390 set last_generated_date = sysdate,
1391 generator_version = l_generator_version
1392 where table_id = l_csr_get_table_rec.table_id;
1393
1394 -- update status to completed
1395 hr_dm_utility.update_phase_items(p_new_status => 'C',
1396 p_id => l_phase_item_id);
1397
1398 hr_dm_utility.message('INFO','Generated TUPS/TDS succesfully for ' ||
1399 l_csr_get_table_rec.table_name || ', Table Id - ' ||
1400 l_csr_get_table_rec.table_id,110);
1401
1402 hr_dm_utility.message('SUMM','Generated TUPS/TDS successfully for ' ||
1403 l_csr_get_table_rec.table_name || ', Table Id - ' ||
1404 l_csr_get_table_rec.table_id,120);
1405
1406 end loop;
1407
1408 -- set up return values to concurrent manager
1409 retcode := 0;
1410 errbuf := 'No errors - examine logfiles for detailed reports.';
1411
1412 hr_dm_utility.message('ROUT','exit:hr_dm_gen_main.slave_generator',
1413 125);
1414 -- error handling
1415 exception
1416 when e_fatal_error then
1417 if csr_get_table%isopen then
1418 close csr_get_table;
1419 end if;
1420 retcode := 2;
1421 errbuf := 'An error occurred during the migration - examine logfiles for detailed reports.';
1422 hr_dm_utility.error(SQLCODE,'hr_dm_gen_main.slave_generator',
1423 l_fatal_error_message,'R');
1424
1425 -- if the error is caused because the other process has set the generator phase to 'Error'
1426 -- then the phase_item_id is 'NULL' , otherwise, the error is caused within this process
1427 -- while generating TUPS/TDS.
1428
1429 if l_phase_item_id is not null then
1430 hr_dm_utility.update_phase_items(p_new_status => 'E',
1431 p_id => l_phase_item_id);
1432 else
1433 hr_dm_utility.update_phases(p_new_status => 'E',
1434 p_id => l_phase_id);
1435 end if;
1436
1437 hr_dm_utility.error(SQLCODE,'hr_dm_gen_main.slave_generator','(none)','R');
1438 when e_fatal_error2 then
1439 if csr_get_table%isopen then
1440 close csr_get_table;
1441 end if;
1442 retcode := 0;
1443 errbuf := 'An error occurred during the migration - examine logfiles for detailed reports.';
1444 hr_dm_utility.error(SQLCODE,'hr_dm_gen_main.slave_generator',
1445 l_fatal_error_message,'R');
1446
1447 -- if the error is caused because the other process has set the generator phase to 'Error'
1448 -- then the phase_item_id is 'NULL' , otherwise, the error is caused within this process
1449 -- while generating TUPS/TDS.
1450
1451 if l_phase_item_id is not null then
1452 hr_dm_utility.update_phase_items(p_new_status => 'E',
1453 p_id => l_phase_item_id);
1454 else
1455 hr_dm_utility.update_phases(p_new_status => 'E',
1456 p_id => l_phase_id);
1457 end if;
1458
1459 hr_dm_utility.error(SQLCODE,'hr_dm_gen_main.slave_generator','(none)','R');
1460 when others then
1461 if csr_get_table%isopen then
1462 close csr_get_table;
1463 end if;
1464 retcode := 2;
1465 errbuf := 'An error occurred during the migration - examine logfiles for detailed reports.';
1466 -- update status to error
1467 hr_dm_utility.update_phase_items(p_new_status => 'E',
1468 p_id => l_phase_item_id);
1469 hr_dm_utility.error(SQLCODE,'hr_dm_gen_main.slave_generator','(none)','R');
1470
1471
1472 end slave_generator ;
1473 end hr_dm_gen_main;
[Home] [Help]