1 package body hr_dm_imp_bg_where as
2 /* $Header: perdmwer.pkb 120.2 2006/03/23 10:29:42 mmudigon noship $ */
3
4
5 --
6 -- PL/SQL record to store the hierarchy information. It stores the join
7 -- condition to join immediate parent table.
8 --
9
10
11 type t_hierarchy_rec is record
12 ( table_name varchar2(30),
13 parent_table_name varchar2(30),
14 join_condition varchar2(1000),
15 logical_delete varchar2(1),
16 parent_table_alias varchar2(30)
17 );
18
19 type t_hierarchy_tbl is table of t_hierarchy_rec index by binary_integer;
20
21 type t_where_clause_tbl is table of varchar2(6000) index by binary_integer;
22
23 g_hierarchy_info_tbl t_hierarchy_tbl;
24
25 -- this table will store the where clause for each chain in the hierarchy.
26
27 g_where_clause_tbl t_where_clause_tbl;
28 g_where_clause_index number := 0;
29
30
31
32 -- c_newline constant varchar(1) default '
33 --';
34
35 -- ----------------------- indent -----------------------------------------
36 -- Description:
37 -- returns the 'n' blank spaces on a newline.used to indent the procedure
38 -- statements.
39 -- if newline parameter is 'Y' then start the indentation from new line.
40 -- ------------------------------------------------------------------------
41
42 function indent
43 (
44 p_indent_spaces in number default 0,
45 p_newline in varchar2 default 'Y'
46 ) return varchar2 is
47 l_spaces varchar2(100);
48 begin
49
50 l_spaces := hr_dm_library.indent(p_indent_spaces => p_indent_spaces,
51 p_newline => p_newline);
52 return l_spaces;
53 exception
54 when others then
55 hr_dm_utility.error(SQLCODE,'hr_dm_imp_bg_where.indent',
56 '(p_indent_spaces - ' || p_indent_spaces ||
57 ')(p_newline - ' || p_newline || ')',
58 'R');
59 end indent;
60
61 ----------------------------------------------------------------------------
62 -- populate the PL/SQL table with the hierarchy information for deriving the
63 -- business group id.
64 -----------------------------------------------------------------------------
65 procedure populate_table
66 ( p_table_info in hr_dm_gen_main.t_table_info ) is
67
68 --
69 -- This cursor will get the information of all the parent tables to be
70 -- used in deriving the business group id.
71 --
72 cursor csr_tab_rel is
73 select distinct level,
74 table_id,
75 lower(column_name) column_name,
76 parent_table_id,
77 lower(parent_column_name) parent_column_name,
78 'N' logical_delete
79 from ( select table_id,
80 column_name,
81 parent_table_id,
82 parent_column_name,
83 'N' logical_delete
84 from hr_dm_hierarchies
85 where hierarchy_type = 'PC')
86 start with table_id = p_table_info.table_id
87 connect by prior parent_table_id = table_id
88 order by level desc;
89
90 l_child_table_info hr_dm_gen_main.t_table_info;
91 l_parent_table_info hr_dm_gen_main.t_table_info;
92
93 l_index number := 1;
94 begin
95 hr_dm_utility.message('ROUT','entry:hr_dm_imp_bg_where.populate_table', 5);
96 for csr_tab_rel_rec in csr_tab_rel loop
97 -- get the child_table details such as alias, name etc.
98 hr_dm_library.get_table_info (csr_tab_rel_rec.table_id,
99 l_child_table_info);
100
101 -- get the child_table details such as alias, name etc.
102 hr_dm_library.get_table_info (csr_tab_rel_rec.parent_table_id,
103 l_parent_table_info);
104
105 -- child table name
106 g_hierarchy_info_tbl(l_index).table_name :=
107 l_child_table_info.table_name;
108
109 -- parent table name
110 g_hierarchy_info_tbl(l_index).parent_table_name :=
111 l_parent_table_info.table_name;
112
113 -- join condition with immediate parent
114 g_hierarchy_info_tbl(l_index).join_condition :=
115 l_child_table_info.alias || '.' || csr_tab_rel_rec.column_name ||
116 ' = ' || l_parent_table_info.alias || '.' ||
117 csr_tab_rel_rec.parent_column_name ;
118
119 -- logical delete is for internal use only.
120 g_hierarchy_info_tbl(l_index).logical_delete := 'N';
121
122 g_hierarchy_info_tbl(l_index).parent_table_alias :=
123 l_parent_table_info.alias;
124 l_index := l_index +1;
125 end loop;
126 hr_dm_utility.message('ROUT','exit:hr_dm_imp_bg_where.populate_table', 25);
127
128
129 exception
130 when others then
131 hr_dm_utility.error(SQLCODE,'hr_dm_imp_bg_where.populate_table',
132 '(none)','R');
133 raise;
134 end populate_table;
135 ----------------------initialize_tables-------------------------
136 --
137 -- Initialize the PL/SQL tables namely hierarchy_info and
138 -- where clause tables
139 ----------------------------------------------------------------
140 procedure initialize_tables is
141 l_index number := g_hierarchy_info_tbl.last;
142 begin
143
144 hr_dm_utility.message('ROUT','entry:hr_dm_imp_bg_where.initialize_tables', 5);
145 -- delete all the elements in hierarchy info table.
146 while l_index is not null loop
147 g_hierarchy_info_tbl.delete(l_index);
148 l_index := g_hierarchy_info_tbl.prior(l_index);
149 end loop;
150
151 -- delete all the elements in where clause table.
152 l_index := g_where_clause_tbl.last;
153 while l_index is not null loop
154 g_where_clause_tbl.delete(l_index);
155 l_index :=g_where_clause_tbl.prior(l_index);
156 end loop;
157 hr_dm_utility.message('ROUT','exit:hr_dm_imp_bg_where.initialize_tables', 25);
158
159
160 exception
161 when others then
162 hr_dm_utility.error(SQLCODE,'hr_dm_imp_bg_where.initialize_tables',
163 '(none)','R');
164 raise;
165 end initialize_tables;
166
167 --------------------- clear_logical_deletes---------------------
168 -- It sets the logical_delete field in the hierarchy_info table
169 -- to 'N' where it is set to 'Y'.
170 ----------------------------------------------------------------
171 procedure clear_logical_deletes is
172 l_index number := g_hierarchy_info_tbl.last;
173 begin
174 hr_dm_utility.message('ROUT','entry:hr_dm_imp_bg_where.clear_logical_deletes', 5);
175 while l_index is not null loop
176 if g_hierarchy_info_tbl(l_index).logical_delete = 'Y' then
177 g_hierarchy_info_tbl(l_index).logical_delete := 'N';
178 end if;
179 l_index := g_hierarchy_info_tbl.prior(l_index);
180 end loop;
181 hr_dm_utility.message('ROUT','exit:hr_dm_imp_bg_where.clear_logical_deletes', 25);
182
183
184 exception
185 when others then
186 hr_dm_utility.error(SQLCODE,'hr_dm_imp_bg_where.clear_logical_deletes',
187 '(none)','R');
188 raise;
189 end clear_logical_deletes;
190
191
192 -----------------search_table_for_par_chld-----------------------
193 -- Search table row where parent_table_name or table_name matches
194 -- the table name parameter value depending upon search_type.
195 -- It is used only if the parent and child has more than one column
196 -- join. This finds out the other columns in the join condition.
197 --
198 -- Input Parameter :
199 -- p_parent_search_value - Parent table name.
200 -- p_child_search_value - Child table name.
201 -- p_original_index - Index of the row with the above values
202 -- in g_hierarchy_info_tbl list.
203 --
204 -- Output Parameter :
205 -- p_search_index - Index of the row with the for the same
206 -- combination of parent and child table
207 -- in g_hierarchy_info_tbl list. More than
208 -- one row exists in the list if parent an
209 -- child table has join condition based on
210 -- more than one column.
211 -- If no row is found then it cintails NULL
212 -- value.
213 ----------------------------------------------------------------
214 procedure search_table_for_par_chld
215 ( p_parent_search_value in varchar2,
216 p_child_search_value in varchar2,
217 p_original_index in number,
218 p_search_index in out nocopy number) is
219
220 l_index number := g_hierarchy_info_tbl.first;
221 l_original_row_index number := p_original_index ;
222 begin
223 hr_dm_utility.message('ROUT','entry:hr_dm_imp_bg_where.search_table_for_par_chld ', 5);
224 hr_dm_utility.message('PARA','(p_parent_search_value - ' || p_parent_search_value ||
225 ')(p_child_search_value - ' || p_child_search_value || ')' ||
226 ')(p_original_index - ' || p_original_index ||
227 ')', 10);
228 while l_index is not null loop
229 if g_hierarchy_info_tbl(l_index).parent_table_name = p_parent_search_value
230 and g_hierarchy_info_tbl(l_index).table_name = p_child_search_value
231 and g_hierarchy_info_tbl(l_index).logical_delete = 'N'
232 and l_index <> l_original_row_index then
233 p_search_index := l_index;
234 exit;
235 end if;
236 l_index := g_hierarchy_info_tbl.next(l_index);
237 p_search_index := l_index;
238 end loop;
239 hr_dm_utility.message('PARA','(p_search_index - ' || p_search_index ||
240 ')', 20);
241
242 hr_dm_utility.message('ROUT','exit:hr_dm_imp_bg_where.search_table_for_par_chld', 25);
243
244
245 exception
246 when others then
247 hr_dm_utility.error(SQLCODE,'hr_dm_imp_bg_where.search_table_for_par_chld',
248 '(p_parent_search_value - ' || p_parent_search_value ||
249 ')(p_child_search_value - ' || p_child_search_value || ')' ||
250 ')(p_original_index - ' || p_original_index ||
251 ')','R');
252 raise;
253 end search_table_for_par_chld;
254
255 ----------------------search_table -------------------------
256 -- Get the index of table row where parent_table_name or table_name
257 -- matches the given table name parameter value depending upon
258 -- search_type.
259 -- Input Parameters :
260 -- p_search_type - Type of match. Can have following values :
261 -- 'P' get the row where parent_table_name
262 -- matches the table name parameter value.
263 -- Other Values get the row where child table_name
264 -- matches the table name parameter value.
265 -- Output Parameters :
266 -- p_search_index - Returns the index of the row matched in
267 -- g_hierarchy_info_tbl list.
268 --
269 ----------------------------------------------------------------
270 procedure search_table ( p_search_type in varchar2,
271 p_table_name in varchar2,
272 p_search_index out nocopy number) is
273 l_index number := g_hierarchy_info_tbl.first;
274 begin
275
276 hr_dm_utility.message('ROUT','entry:hr_dm_imp_bg_where.search_table', 5);
277 hr_dm_utility.message('PARA','(p_search_type - ' || p_search_type ||
278 ')(p_table_name - ' || p_table_name || ')' ||
279 ')', 10);
280
281 while l_index is not null loop
282 -- if serach type is for parent table.
283 if p_search_type = 'P' then
284 if g_hierarchy_info_tbl(l_index).parent_table_name = p_table_name
285 then
286 p_search_index := l_index;
287 exit;
288 end if;
289 else
290 if g_hierarchy_info_tbl(l_index).table_name = p_table_name then
291 p_search_index := l_index;
292 exit;
293 end if;
294 end if;
295 l_index := g_hierarchy_info_tbl.next(l_index);
296 p_search_index := l_index;
297 end loop;
298 hr_dm_utility.message('PARA','(p_search_index - ' || p_search_index ||
299 ')', 20);
300
301 hr_dm_utility.message('ROUT','exit:hr_dm_imp_bg_where.search_table', 25);
302
303
304 exception
305 when others then
306 hr_dm_utility.error(SQLCODE,'hr_dm_imp_bg_where.search_table',
307 '(p_search_type - ' || p_search_type ||
308 ')(p_table_name - ' || p_table_name || ')' ||
309 ')','R');
310 raise;
311 end search_table;
312
313
314 ---------------------delete_table_rows---------------------------
315 -- Delete table row where the given child table exists as a parent
316 -- table in any row but does not exists as a child table in any other
317 -- row.
318 -- Above is applied to all the rows processed in a current where clause
319 -- chain. Seed row will change.
320 -- Processing Logic :
321 -- - get the Child table for the given index : 'C'
322 -- - First a check is made whether this table exists as a
323 -- child table in any other row.
324 -- If it exists then skip the processing.
325 -- If it does not exists then
326 -- check if it exists as a Parent table in any other row
327 -- if yes then
328 -- store the index number of this row.
329 -- get the child table for this index.
330 -- delete the row.
331 -- else
332 -- exit.
333 -- end if
334 -- end if.
335 -- repeat above process in loop.
336 -- Example :
337 -- A ---> B ---> C ---> D
338 -- Row Number Parent Table Child Table Join Condition
339 -- 1 D C D.colf = C.colf
340 -- 2 C B C.cole = B.cole
341 -- 3 B A B.colc = A.colc
342 --
343 -- index of row number 1 is passed.
344 -- Child table : 'C'.
345 -- Found row 2 where it exist as parent table. Table 'C' does
346 -- not have any child table.
347 -- store the index of row 2.
348 -- get the child table of row 2 i.e 'B'
349 -- delete row 2.
350 --
351 -- Second iteration of the loop
352 -- Child table : 'B'. from index of row 2
353 -- Found row 3 where it exist as parent table. Table 'B' does
354 -- not have any child table.
355 -- store the index of row 3.
356 -- get the child table of row 3 i.e 'A'
357 -- delete row 3.
358 --
359 -- No parent table exists for table 'A'.
360 -- Stop processing.
361 --
362 -- Input Parameters :
363 -- p_search_type - 'P' Delete the row where parent_table_name
364 -- matches the table name parameter value.
365 -- Other Values Delete the row where child table_name
366 -- matches the table name parameter value.
367 --
368 -----------------------------------------------------------------
369 procedure delete_table_rows
370 ( p_child_table in varchar2) is
371
372 -- l_index number := p_index;
373 l_parent_table_index number;
374 l_child_table_index number;
375 l_child_table varchar2(30);
376 begin
377 hr_dm_utility.message('ROUT','entry:hr_dm_imp_bg_where.delete_table_rows', 5);
378 hr_dm_utility.message('PARA','( p_child_table - ' || p_child_table ||
379 ')', 10);
380 -- get child table
381 l_child_table := p_child_table;
382
383 loop
384
385 l_child_table_index := null;
386 l_parent_table_index := null;
387
388 -- check whether this table exists as a child table in any other row.
389
390 search_table('C',
391 l_child_table,
392 l_child_table_index);
393
394 -- if child table also exists as a child table in another row then skip
395 -- processing.
396
397 if l_child_table_index is not null then
398 exit;
399 else
400 -- check whether this table exists as a parent table in any other row.
401
402 search_table('P',
403 l_child_table,
404 l_parent_table_index);
405
406 if l_parent_table_index is null then
407 exit;
408 else
409 --
410 -- get the child table for this parent table row for further
411 -- processing.
412 --
413 l_child_table :=
414 g_hierarchy_info_tbl(l_parent_table_index).table_name;
415
416 -- delete this row
417 g_hierarchy_info_tbl.delete(l_parent_table_index);
418 end if; -- if l_parent_table_index is null then
419 end if; -- if l_child_table_index is not null then
420 end loop;
421
422 hr_dm_utility.message('ROUT','exit:hr_dm_imp_bg_where.delete_table_rows', 25);
423
424 exception
425 when others then
426 hr_dm_utility.error(SQLCODE,'hr_dm_imp_bg_where.delete_table_rows',
427 '( p_child_table - ' || p_child_table ||
428 ')','R');
429 raise;
430 end delete_table_rows;
431
432 -----------------prepare_where_clause_for_chain-------------------------
433 -- It prepares where clause for a chain by joining parent and child
434 -- table with operand 'AND'.
435 -- The starting parent table and child table of the chain are passed as
436 -- a parameter. The remaining part of the chain is derived by finding out
437 -- the parent table for the given child table and so on until child table
438 -- is reached which does not have any child or is not parent to any table.
439 -- Input Parameter :
440 -- p_cursor_type - The cursor for which where clause needs to be formed.
441 -- It can have following values :
442 -- 'DOWNLOAD' - where clause for download procedure
443 -- 'DELETE_SOURCE' - where clause for delete source
444 -- procedure
445 -- 'CALCULATE_RANGES' - where clause for calculate_ranges
446 -- procedure
447 -- 'VIEW' - where clause for view creation
448 --
449 -- p_query_type - It defines whether the where clause to be build up is
450 -- for main query or sub query (used for date track table
451 -- additive migration).
452 -- It can have following values :
453 -- 'MAIN_QUERY' - For Non date track download cursor and
454 -- Date track full migration cursor.
455 -- 'SUB_QUERY' - Download cursor sub query where clause
456 -- Additive migration of date track table.
457 -- p_table_index - Index of the g_hierarchy_info_tbl list. Index is used
458 -- get the starting parent and table name.
459 ---------------------------------------------------------------------
460 procedure prepare_where_clause_for_chain
461 (p_table_info in hr_dm_gen_main.t_table_info,
462 p_table_index in number,
463 p_cursor_type in varchar2,
464 p_query_type in varchar2
465 ) is
466
467 l_table_index number := p_table_index;
468 l_pc_table_index number := p_table_index;
469 l_parent_table varchar2(30);
470 l_child_table varchar2(30);
471 l_orig_parent_table varchar2(30);
472 l_orig_child_table varchar2(30);
473 l_operand varchar2(30) := ' ';
474 l_indent number;
475 l_who_info_alias hr_dm_tables.table_alias%type;
476 begin
477 hr_dm_utility.message('ROUT','entry:hr_dm_imp_bg_where.prepare_where_clause_for_chain ', 5);
478 hr_dm_utility.message('PARA','(p_cursor_type - ' || p_cursor_type ||
479 ')(p_query_type - ' || p_query_type || ')' ||
480 ')(p_table_index - ' || p_table_index || ')' ||
481 ')', 10);
482 --
483 -- if who column info is missing from the table then use the alias of the table
484 -- whose who info should be used for additive migration, otherwise, use the
485 -- table alias.
486
487 if p_table_info.missing_who_info = 'Y' then
488 l_who_info_alias := p_table_info.who_link_alias;
489 else
490 l_who_info_alias := p_table_info.alias;
491 end if;
492
493 if p_query_type = 'MAIN_QUERY' then
494 l_indent := 2;
495 else
496 l_indent := 17;
497 end if;
498 -- stores the parent and child table name into local variables.
499 l_parent_table :=
500 g_hierarchy_info_tbl(l_table_index).parent_table_name;
501
502 l_child_table := g_hierarchy_info_tbl(l_table_index).table_name;
503
504 l_orig_parent_table :=
505 g_hierarchy_info_tbl(l_table_index).parent_table_name;
506
507 l_orig_child_table := g_hierarchy_info_tbl(l_table_index).table_name;
508
509 -- if it is a sub query where clause then add the join condition for id.
510 if p_query_type = 'SUB_QUERY' then
511 g_where_clause_tbl(g_where_clause_index) := lpad(' ',l_indent + 5) ||
512 p_table_info.alias || '1.'||
513 p_table_info.surrogate_pk_column_name || ' = '|| p_table_info.alias
514 || '.'|| p_table_info.surrogate_pk_column_name ;
515 l_operand := ' and ';
516 end if;
517
518 --
519 -- This loop is to traverse through the hierarchy info table and build up
520 -- chain using parent table and child table passed as a starting point.
521 --
522 loop
523
524 -- g_where_clause_tbl(g_where_clause_index) is null for the first time
525 -- for the MAIN_QUERY so we do not want to insert the blank line but for
526 -- SUB_QUERY the where clause is already populated and hence next line
527 -- should start at new line.
528
529 if g_where_clause_tbl(g_where_clause_index) is not null then
530 g_where_clause_tbl(g_where_clause_index) :=
531 g_where_clause_tbl(g_where_clause_index) ||indent(l_indent) ||
532 l_operand || g_hierarchy_info_tbl(l_table_index).join_condition ;
533 else
534 g_where_clause_tbl(g_where_clause_index) :=
535 g_where_clause_tbl(g_where_clause_index) ||
536 l_operand || g_hierarchy_info_tbl(l_table_index).join_condition ;
537 end if;
538
539 l_pc_table_index := l_table_index;
540
541 --
542 -- This loop builds up the where clause for the parent and child table
543 -- if they are joined by more than one column. For a single column
544 -- join it does not do anything.
545 loop
546
547 l_pc_table_index := null;
548 --
549 -- find out if there is another row exists for the given parent and
550 -- child table combination. This will be the case when the parent
551 -- and child table will have join condition based on more than one
552 -- column.
553 -- l_pc_index will be null for a single column join and will return
554 -- the table index of the row containing the other column joins for
555 -- parent and child table join.
556 --
557
558 search_table_for_par_chld ( l_parent_table,
559 l_child_table,
560 l_table_index,
561 l_pc_table_index );
562
563
564 if l_pc_table_index is not null then
565
566 -- add the conditionof the multiple columns to this chain of where
567 -- clause.
568 --
569
570 g_where_clause_tbl(g_where_clause_index) :=
571 g_where_clause_tbl(g_where_clause_index) || indent(l_indent) ||
572 ' and ' || g_hierarchy_info_tbl(l_pc_table_index).join_condition;
573
574 --
575 -- if the parent table and child table are same as starting point of
576 -- chain i.e parameters values passed then delete the row as it is
577 -- not required any longer. Otherwise, just set the logical delete
578 -- flag to 'Y' so as to ignore this row in the further processing of
579 -- this chain.
580 --
581
582 if g_hierarchy_info_tbl(l_pc_table_index).parent_table_name =
583 l_orig_parent_table and
584 g_hierarchy_info_tbl(l_pc_table_index).table_name =
585 l_orig_child_table then
586 g_hierarchy_info_tbl.delete(l_pc_table_index);
587 else
588 -- do logical delete
589 g_hierarchy_info_tbl(l_pc_table_index).logical_delete := 'Y';
590 end if;
591 else
592 exit;
593 end if;
594 end loop;
595
596
597 -- find out another table in the chain by looking for the row where the
598 -- given child table appears as aparent table for another table.
599 -- l_table_index is returned as null if this child table is the last one
600 -- for the given chain.
601
602 search_table ('P',
603 l_child_table,
604 l_table_index);
605
606 if l_table_index is null then
607 exit;
608 else
609 l_parent_table := g_hierarchy_info_tbl(l_table_index).parent_table_name;
610 l_child_table := g_hierarchy_info_tbl(l_table_index).table_name;
611 l_operand := ' and ';
612 end if;
613
614 end loop;
615
616 -- add id between start_id and end_id clause
617 -- if p_cursor_type = 'DOWNLOAD' and p_query_type = 'MAIN_QUERY' then
618 if p_cursor_type in ('DOWNLOAD', 'DELETE_SOURCE') and
619 p_query_type = 'MAIN_QUERY' then
620 if p_table_info.surrogate_primary_key = 'Y' then
621 g_where_clause_tbl(g_where_clause_index) :=
622 g_where_clause_tbl(g_where_clause_index) || indent(l_indent) ||
623 ' and ' || p_table_info.alias || '.' ||
624 p_table_info.surrogate_pk_column_name || ' between p_start_id ' ||
625 'and p_end_id' ;
626 end if;
627 end if;
628
629 -- add last update on clause
630 if p_cursor_type in ('DOWNLOAD','CALCULATE_RANGES') and
631 p_query_type = 'MAIN_QUERY' and
632 p_table_info.datetrack = 'N' then
633
634 g_where_clause_tbl(g_where_clause_index) :=
635 g_where_clause_tbl(g_where_clause_index) || indent(l_indent) ||
636 ' and ' || l_who_info_alias
637 || '.last_update_date >= nvl(p_last_migration_date,'|| l_who_info_alias
638 || '.last_update_date)';
639 end if;
640
641 -- add business group id clause
642 if p_cursor_type = 'VIEW' then
643 g_where_clause_tbl(g_where_clause_index) :=
644 g_where_clause_tbl(g_where_clause_index) || indent(l_indent) ||
645 ' and ' ||
646 g_hierarchy_info_tbl(p_table_index).parent_table_alias ||
647 '.business_group_id is null' ;
648 else
649 g_where_clause_tbl(g_where_clause_index) :=
650 g_where_clause_tbl(g_where_clause_index) || indent(l_indent) ||
651 ' and ' ||
652 g_hierarchy_info_tbl(p_table_index).parent_table_alias ||
653 '.business_group_id = p_business_group_id' ;
654 end if;
655
656
657 hr_dm_utility.message('ROUT','exit:hr_dm_imp_bg_where.prepare_where_clause_for_chain',
658 25);
659
660
661 exception
662 when others then
663 hr_dm_utility.error(SQLCODE,'hr_dm_imp_bg_where. prepare_where_clause_for_chain',
664 '(p_cursor_type - ' || p_cursor_type ||
665 ')(p_query_type - ' || p_query_type || ')' ||
666 ')(p_table_index - ' || p_table_index || ')' ||
667 ')','R');
668 raise;
669 end prepare_where_clause_for_chain;
670
671 ----------------------------- prepare_where_clause --------------------------
672 -- prepare the where clause involves the following step
673 -- - read the pl/sql table rows one by one which contains the hierarchy info
674 -- For each row develop the where clause for the chain
675 -- delete the entries from table which no longer required.
676 -- - prepare the where clause by joining the where clause elements of chains
677 -- with 'OR' operand.
678 -- Input Parameter :
679 -- p_cursor_type - The cursor for which where clause needs to be formed.
680 -- It can have following values :
681 -- 'DOWNLOAD' - where clause for download procedure
682 -- 'CALCULATE_RANGES' - where clause for calculate_ranges
683 -- procedure
684 --
685 -- p_query_type - It defines whether the where clause to be build up is
686 -- for main query or sub query (used for date track table
687 -- additive migration).
688 -- It can have following values :
689 -- 'MAIN_QUERY' - For Non date track download cursor and
690 -- Date track full migration cursor.
691 -- 'SUB_QUERY' - Download cursor sub query where clause
692 -- Additive migration of date track table.
693 ------------------------------------------------------------------------------
694 procedure prepare_where_clause
695 (p_table_info in hr_dm_gen_main.t_table_info,
696 p_cursor_type in varchar2,
697 p_query_type in varchar2,
698 p_where_clause out nocopy varchar2
699 ) is
700
701 l_table_index number := g_hierarchy_info_tbl.first;
702 l_child_table varchar2(30);
703 l_pc_table_index number;
704 l_index number;
705 l_indent number;
706 l_where_clause varchar2(32767);
707 l_sub_from_clause varchar2(32767);
708 l_sub_where_clause varchar2(32767);
709 begin
710 hr_dm_utility.message('ROUT','entry:hr_dm_imp_bg_where.prepare_where_clause', 5);
711 hr_dm_utility.message('PARA','(p_cursor_type - ' || p_cursor_type ||
712 ')(p_query_type - ' || p_query_type || ')' ||
713 ')', 10);
714
715 g_where_clause_index := 1;
716 while l_table_index is not null loop
717
718 l_child_table := g_hierarchy_info_tbl(l_table_index).table_name;
719 g_where_clause_tbl(g_where_clause_index) := null;
720
721 -- call procedure to prepare the where clause of the chain.
722 prepare_where_clause_for_chain (p_table_info,
723 l_table_index,
724 p_cursor_type,
725 p_query_type);
726
727 -- clear all the records which have been marked deleted, by setting
728 -- the logical delete field to 'N'.
729 clear_logical_deletes;
730
731 -- delete the row which is read
732 g_hierarchy_info_tbl.delete(l_table_index);
733
734 -- if the child table does not exist as child column in any other row then
735 -- delete all the rows where it exists as parent table.
736 -- repeat the process for the whole where clause chain.
737 delete_table_rows(l_child_table);
738
739
740 g_where_clause_index := g_where_clause_index + 1;
741 --display_table;
742 l_table_index := g_hierarchy_info_tbl.next(l_table_index);
743
744 end loop;
745
746 -- prepare the final where clause by joining all the chains i.e all the
747 -- elements of where_clause table.
748
749 if p_query_type = 'MAIN_QUERY' then
750 l_indent := 2;
751 l_where_clause := ' where ' ;
752
753 l_table_index := g_where_clause_tbl.first;
754
755 -- join the each where clause chain with 'OR' clause.
756
757 while l_table_index is not null loop
758 l_where_clause := l_where_clause || indent(l_indent) || '( ' ||
759 indent(l_indent) || g_where_clause_tbl(l_table_index) ||
760 indent(l_indent) || ') ';
761 l_table_index := g_where_clause_tbl.next(l_table_index);
762 if l_table_index is not null then
763 l_where_clause := l_where_clause || indent(l_indent) || 'OR';
764 end if;
765 end loop;
766
767 /*
768 -- add the order by clause for datetrack table
769 if p_table_info.datetrack = 'Y' then
770 l_where_clause := l_where_clause || indent(2) ||
771 'order by ' || p_table_info.surrogate_pk_column_name ;
772 end if;
773 */
774
775 p_where_clause := l_where_clause;
776
777 else
778 l_indent := 2;
779 l_where_clause := ' where ';
780
781 -- put the search condition of id between start and end id.
782
783 if p_table_info.surrogate_primary_key = 'Y' then
784 l_where_clause := l_where_clause || p_table_info.alias || '1.' ||
785 p_table_info.surrogate_pk_column_name || ' between p_start_id and ' ||
786 'p_end_id';
787 end if;
788
789 l_where_clause := l_where_clause || indent(l_indent) || 'and ';
790
791
792 -- prepare the sub query.
793 l_where_clause := l_where_clause || 'exists ( select 1';
794
795 l_indent := 17;
796
797 -- get the from clause for sub query
798 hr_dm_gen_tds.get_cursor_from_clause (p_table_info => p_table_info,
799 p_from_clause => l_sub_from_clause,
800 p_lpad_spaces => l_indent);
801
802 -- get the where clause for sub query
803
804 l_indent := 17;
805 l_sub_where_clause := lpad(' ',l_indent) || 'where ';
806
807 l_table_index := g_where_clause_tbl.first;
808
809 -- join the each where clause chain with 'OR' clause.
810 while l_table_index is not null loop
811 l_sub_where_clause := l_sub_where_clause || indent(l_indent) || '( ' ||
812 indent || g_where_clause_tbl(l_table_index) || indent(l_indent) || ') ';
813 l_table_index := g_where_clause_tbl.next(l_table_index);
814 if l_table_index is not null then
815 l_sub_where_clause := l_sub_where_clause ||indent(l_indent) ||'OR';
816 end if;
817 end loop;
818
819 p_where_clause := l_where_clause || indent ||
820 l_sub_from_clause || indent ||
821 l_sub_where_clause || indent(15) || ')' ;
822
823 if p_table_info.surrogate_primary_key = 'Y' and
824 p_table_info.datetrack = 'Y' and
825 p_cursor_type = 'DOWNLOAD' then
826 p_where_clause := p_where_clause || indent(2) || 'order by ' ||
827 p_table_info.alias || '1.' ||p_table_info.surrogate_pk_column_name || ';';
828 else
829 p_where_clause := p_where_clause || ';';
830 end if;
831
832 end if;
833 hr_dm_utility.message('ROUT','exit:hr_dm_imp_bg_where.prepare_where_clause', 25);
834
835 exception
836 when others then
837 hr_dm_utility.error(SQLCODE,'hr_dm_imp_bg_where.prepare_where_clause',
838 '(p_cursor_type - ' || p_cursor_type ||
839 ')(p_query_type - ' || p_query_type || ')' ||
840 ')','R');
841 raise;
842 end prepare_where_clause;
843
844 -------------------------------------------------------------------------
845 -- Main Procedure
846 -- This function creates the where clause for the table which do not have
847 -- business group id but the parent tables relationship is stored in
848 -- hr_dm_hierarchies table is used to derive the 'where' clause.
849 -- Input Parameter :
850 -- p_cursor_type - The cursor for which where clause needs to be formed.
851 -- It can have following values :
852 -- 'DOWNLOAD' - where clause for download procedure
853 -- 'CALCULATE_RANGES' - where clause for calculate_ranges
854 -- procedure
855 -- 'VIEW' - where clause for view creation
856 --
857 -- p_query_type - It defines whether the where clause to be build up is
858 -- for main query or sub query (used for date track table
859 -- additive migration).
860 -- It can have following values :
861 -- 'MAIN_QUERY' - For Non date track download cursor and
862 -- Date track full migration cursor.
863 -- 'SUB_QUERY' - Download cursor sub query where clause
864 -- Additive migration of date track table.
865 --
866 -- Output Parameters :
867 -- p_where_clause - Formatted where clause.
868 --
869 -------------------------------------------------------------------------
870 procedure main
871 (p_table_info in hr_dm_gen_main.t_table_info,
872 p_cursor_type in varchar2,
873 p_query_type in varchar2,
874 p_where_clause out nocopy varchar2) is
875
876 l_where_clause varchar2(32767);
877 begin
878
879 hr_dm_utility.message('ROUT','entry:hr_dm_imp_bg_where.main', 5);
880 hr_dm_utility.message('PARA','(p_cursor_type - ' || p_cursor_type ||
881 ')(p_query_type - ' || p_query_type || ')' ||
882 ')', 10);
883 initialize_tables;
884 populate_table (p_table_info);
885 prepare_where_clause (p_table_info,
886 p_cursor_type,
887 p_query_type,
888 p_where_clause);
889 l_where_clause := p_where_clause;
890 hr_dm_utility.message('PARA','( p_where_clause - ' || p_where_clause ||
891 ')', 20);
892 hr_dm_utility.message('ROUT','exit:hr_dm_imp_bg_where.get_generator_version', 25);
893
894 exception
895 when others then
896 hr_dm_utility.error(SQLCODE,'hr_dm_imp_bg_where.main',
897 '(p_cursor_type - ' || p_cursor_type ||
898 ')(p_query_type - ' || p_query_type || ')' ||
899 ')','R');
900 raise;
901 end main;
902 end hr_dm_imp_bg_where;
[Home] [Help]