1 package body hr_dm_library as
2 /* $Header: perdmlib.pkb 115.29 2004/03/24 08:28:56 mmudigon ship $ */
3 l_status varchar2(50);
4 l_industry varchar2(50);
5 l_per_owner varchar2(30);
6 l_ben_owner varchar2(30);
7 l_pay_owner varchar2(30);
8 l_ff_owner varchar2(30);
9 l_fnd_owner varchar2(30);
10 l_apps_owner varchar2(30);
11
12 l_ret1 boolean := FND_INSTALLATION.GET_APP_INFO ('PAY', l_status,
13 l_industry, l_pay_owner);
14 l_ret2 boolean := FND_INSTALLATION.GET_APP_INFO ('BEN', l_status,
15 l_industry, l_ben_owner);
16 l_ret3 boolean := FND_INSTALLATION.GET_APP_INFO ('FF', l_status,
17 l_industry, l_ff_owner);
18 l_ret4 boolean := FND_INSTALLATION.GET_APP_INFO ('FND', l_status,
19 l_industry, l_fnd_owner);
20 l_ret5 boolean := FND_INSTALLATION.GET_APP_INFO ('PER', l_status,
21 l_industry, l_per_owner);
22 l_ret6 boolean := FND_INSTALLATION.GET_APP_INFO ('APPS', l_status,
23 l_industry, l_apps_owner);
24
25 -- ----------------------- indent -----------------------------------------
26 -- Description:
27 -- returns the 'n' blank spaces on a newline.used to indent the procedure
28 -- statements.
29 --
30 -- ------------------------------------------------------------------------
31
32 function indent
33 (
34 p_indent_spaces in number default 0,
35 p_newline in varchar2 default 'Y'
36 ) return varchar2 is
37 l_spaces varchar2(100);
38 begin
39
40 -- if newline parameter is 'Y' then start the indentation from new line.
41 if p_newline = 'Y' then
42 l_spaces := c_newline || rpad(' ', p_indent_spaces);
43 else
44 l_spaces := rpad(' ', p_indent_spaces);
45 end if;
46 return l_spaces;
47 exception
48 when others then
49 hr_dm_utility.error(SQLCODE,'hr_dm_library.indent',
50 '(p_indent_spaces - ' || p_indent_spaces ||
51 ')(p_newline - ' || p_newline || ')',
52 'R');
53 end indent;
54
55
56 -- ------------------------- get_generator_version ------------------------
57 -- Description:
58 -- It gets the version number of the Genrator by concatenating the arcs
59 -- version of Main Generator package, TUPS Generator package and TDS package.
60 -- It is used by the Main Generator to stamp the generator version for each
61 -- generated TUPS/TDS and by initialisation program to check whether given
62 -- TUPS/TDS had been compiled by the latest generator.
63 -- Input Parameters
64 -- p_format_output - Whether a formatted output is required or not.
65 -- For updating the generator_version field no
66 -- formatting is required. Output will be stored as
67 -- a one large string.But for TUPs/TDS packages output
68 -- string is properly indented.
69 -- It can have two values :
70 -- 'Y' - Formatted output is required
71 -- 'N' - Output string without indentation.
72 -- Output Parameters
73 -- p_package_version - It returns the text string created by the ARCS
74 -- for the package.
75 --
76 -- ------------------------------------------------------------------------
77 procedure get_generator_version
78 (
79 p_generator_version out nocopy varchar2,
80 p_format_output in varchar2 default 'N'
81 )
82 is
83 l_package_version varchar2(1000);
84 l_generator_version hr_dm_tables.generator_version%type;
85
86 -- ----------------------- indent -----------------------------------------
87 -- Description:
88 -- returns the 'n' blank spaces on a newline.used to indent the procedure
89 -- statements.
90 --
91 -- ------------------------------------------------------------------------
92
93 function priv_indent
94 (
95 p_indent_spaces in number default 8
96 ) return varchar2 is
97 l_spaces varchar2(100);
98 begin
99 l_spaces := c_newline || rpad(' ', p_indent_spaces) || '- ' ;
100 return l_spaces;
101 exception
102 when others then
103 hr_dm_utility.error(SQLCODE,'hr_dm_library.priv_indent',
104 '(none)',
105 'R');
106 raise;
107 end priv_indent;
108
109 begin
110
111 hr_dm_utility.message('ROUT','entry:hr_dm_library.get_generator_version'
112 , 5);
113
114 -- get the version of main generator
115 get_package_version ( p_package_name => 'HR_DM_GEN_MAIN',
116 p_package_version => l_package_version);
117
118 if p_format_output = 'Y' then
119 l_generator_version := priv_indent || l_package_version;
120 else
121 l_generator_version := l_package_version;
122 end if;
123
124 -- get the version of Library package
125 get_package_version ( p_package_name => 'HR_DM_LIBRARY',
126 p_package_version => l_package_version);
127
128 if p_format_output = 'Y' then
129 l_generator_version := l_generator_version || priv_indent || l_package_version;
130 else
131 l_generator_version := l_generator_version || ' :: ' || l_package_version;
132 end if;
133
134 -- get the version of TUPS generator
135 get_package_version ( p_package_name => 'HR_DM_GEN_TUPS',
136 p_package_version => l_package_version);
137
138 if p_format_output = 'Y' then
139 l_generator_version := l_generator_version || priv_indent ||
140 l_package_version;
141 else
142 l_generator_version := l_generator_version || ' :: ' || l_package_version;
143 end if;
144
145 -- get the version of package which seed TUPS/TDS into data pump.
146 get_package_version ( p_package_name => 'HR_DM_SEED_DP',
147 p_package_version => l_package_version);
148
149 if p_format_output = 'Y' then
150 l_generator_version := l_generator_version || priv_indent ||
151 l_package_version;
152 else
153 l_generator_version := l_generator_version || ' :: ' || l_package_version;
154 end if;
155
156 -- get the version of TDS generator
157 get_package_version ( p_package_name => 'HR_DM_GEN_TDS',
158 p_package_version => l_package_version);
159
160 if p_format_output = 'Y' then
161 l_generator_version := l_generator_version || priv_indent ||
162 l_package_version;
163 else
164 l_generator_version := l_generator_version || ' :: ' || l_package_version;
165 end if;
166
167 -- get the version of package which gets where clause for implicit
168 -- business group.
169 get_package_version ( p_package_name => 'HR_DM_IMP_BG_WHERE',
170 p_package_version => l_package_version);
171
172 p_generator_version := l_generator_version;
173
174 hr_dm_utility.message('PARA','(p_generator_version - ' ||
175 p_generator_version || ')', 30);
176 hr_dm_utility.message('ROUT','exit:hr_dm_library.get_generator_version'
177 , 25);
178
179 exception
180 when others then
181 hr_dm_utility.error(SQLCODE,'hr_dm_library.get_generator_version',
182 '(p_generator_version - ' || p_generator_version ||
183 ')','R');
184 raise;
185 end get_generator_version;
186 -- ------------------------- get_package_version ------------------------
187 -- Description:
188 -- It gets the version number for the given package. Depending upon the
189 -- version type required it either returns the full header string of the
190 -- package body or concatenate the File name and Version number of package
191 -- header and body of the package.
192 -- Input Parameters :
193 -- p_package_name - Name of the stored package whose version number
194 -- is required.
195 -- p_version_type - It identifies what sort of output version string
196 -- is required. It can have following values
197 -- SUMMARY - concatenate the File name and Version
198 -- number of package header and body of the package.
199 -- the output version string will look as
200 -- ' hrdmgen.pkh 115.1 : hrdmgen.pkb 115.1 '
201 --
202 -- FULL - Full header string from the package body
203 -- is returned.The output version string
204 -- will look as
205 -- /* $Header: perdmlib.pkb 115.29 2004/03/24 08:28:56 mmudigon ship $ */
206 -- Output Parameters
207 -- p_package_version - It returns the text string created by the ARCS
208 -- for the package.
209 --
210 --
211 -- ------------------------------------------------------------------------
212 procedure get_package_version
213 (
214 p_package_name in varchar2,
215 p_package_version out nocopy varchar2,
216 p_version_type in varchar2 default 'SUMMARY'
217 )
218 is
219 --cursor to get the package version string
220 cursor csr_get_package_version is
221 select pkh.text,
222 pkb.text
223 from user_source pkh,
224 user_source pkb
225 where pkh.name = upper(p_package_name)
226 and pkh.type = 'PACKAGE'
227 and pkh.line = 2
228 and pkb.name = upper(p_package_name)
229 and pkb.type = 'PACKAGE BODY'
230 and pkb.line = 2;
231
232 l_pkg_header_ver_text varchar2(2000);
233 l_pkg_body_ver_text varchar2(2000);
234 l_pkg_body_ver_text_det varchar2(2000);
235
236 -- this function extracts the file name and version from the version
237 -- string.
238 -- Input string :
239 -- /* $Header: perdmlib.pkb 115.29 2004/03/24 08:28:56 mmudigon ship $ */
240 -- Output return from function is 'perdmlib.pkb 115.5'
241
242 function priv_get_version_from_string (p_version_string varchar2)
243 return varchar2 is
244 l_start_ptr number := 0;
245 l_end_ptr number := 0;
246 l_return_string varchar2(100);
247 begin
248 l_start_ptr := instr(p_version_string,'per');
249 l_end_ptr := instr(p_version_string,' ',1,4);
250 l_return_string := substr(p_version_string,l_start_ptr ,
251 (l_end_ptr - l_start_ptr));
252 return l_return_string;
253 end priv_get_version_from_string;
254
255 begin
256
257 hr_dm_utility.message('ROUT','entry:hr_dm_library.get_package_version', 5);
258 hr_dm_utility.message('PARA','(p_package_name - ' || p_package_name ||
259 ')', 10);
260
261 open csr_get_package_version;
262 fetch csr_get_package_version into l_pkg_header_ver_text,
263 l_pkg_body_ver_text;
264 close csr_get_package_version;
265
266 l_pkg_body_ver_text_det := l_pkg_body_ver_text;
267 l_pkg_header_ver_text := priv_get_version_from_string(l_pkg_header_ver_text);
268 l_pkg_body_ver_text := priv_get_version_from_string(l_pkg_body_ver_text);
269
270 if p_version_type ='FULL' then
271 p_package_version := l_pkg_body_ver_text_det;
272 else
273 p_package_version := l_pkg_header_ver_text || ' : ' ||
274 l_pkg_body_ver_text;
275 end if;
276
277 hr_dm_utility.message('PARA','(p_package_version - ' ||
278 p_package_version || ')', 30);
279 hr_dm_utility.message('ROUT','exit:hr_dm_library.get_package_version', 25);
280
281 exception
282 when others then
283 hr_dm_utility.error(SQLCODE,'hr_dm_library.get_package_version',
284 '(p_package_name - ' || p_package_name ||
285 ')','R');
286 raise;
287 end get_package_version;
288 -- ------------------------- get_data_type ------------------------------
289 -- Description:
290 -- It gets the data type for a given column of the table.
291 -- Input Parameters :
292 -- p_table_name - Name of the table
293 -- p_column_name - Name of the column.
294 -- Output Parameters
295 -- p_data_type - It returns the data type of the column.
296 --e.g number or date or varchar2.
297 --
298 --
299 -- ------------------------------------------------------------------------
300 procedure get_data_type
301 (
302 p_table_name in varchar2,
303 p_column_name in varchar2,
304 p_data_type out nocopy varchar2
305 )
306 is
307 e_fatal_error exception;
308 l_fatal_error_message varchar2(200);
309 l_apps_name varchar2(30);
310
311 cursor csr_apps_name is
312 select ORACLE_USERNAME
313 from fnd_oracle_userid
314 where ORACLE_ID = 900;
315
316 --cursor to get the data type of column
317 cursor csr_get_data_type is
318 select lower(data_type) data_type
319 from all_tab_columns
320 where table_name = upper(p_table_name)
321 and column_name = upper(p_column_name)
322 and owner in
323 (l_apps_name,
324 l_fnd_owner,
325 l_ff_owner,
326 l_ben_owner,
327 l_pay_owner,
328 l_per_owner);
329
330 begin
331
332 hr_dm_utility.message('ROUT','entry:hr_dm_library.get_data_type', 5);
333 hr_dm_utility.message('PARA','(p_table_name - ' || p_table_name ||
334 ')(p_column_name - ' || p_column_name ||
335 ')', 10);
336
337
338 open csr_apps_name;
339 fetch csr_apps_name into l_apps_name;
340 close csr_apps_name;
341
342 open csr_get_data_type;
343 fetch csr_get_data_type into p_data_type;
344 if csr_get_data_type%notfound then
345 close csr_get_data_type;
346 l_fatal_error_message := 'Could not find data type for (p_table_name - '
347 || p_table_name || ')(p_column_name - ' || p_column_name
348 || ')';
349 raise e_fatal_error;
350 end if;
351 close csr_get_data_type;
352
353 hr_dm_utility.message('PARA','(p_data_type - ' ||
354 p_data_type || ')', 30);
355 hr_dm_utility.message('ROUT','exit:hr_dm_library.get_data_type', 25);
356
357 exception
358 when e_fatal_error then
359 if csr_get_data_type%isopen then
360 close csr_get_data_type;
361 end if;
362 hr_dm_utility.error(SQLCODE,'hr_dm_library.get_data_type',
363 l_fatal_error_message,'R');
364
365 raise;
366 when others then
367 hr_dm_utility.error(SQLCODE,'hr_dm_library.get_data_type',
368 '(p_table_name - ' || p_table_name ||
369 ')(p_column_name - ' || p_column_name ||
370 ')','R');
371 raise;
372 end get_data_type;
373 -- ------------------------- get_table_info ------------------------
374 -- Description:
375 -- It returns the properties of the table for the given id.
376 -- Input Parameters :
377 -- p_table_id - Primary key of the hr_dm_tables.
378 -- Output Parameters
379 -- p_table_info - Various properties of the table is returned in
380 -- pl/sql table. The properties are
381 -- o table_id
382 -- o table_name
383 -- o datetrack
384 -- o surrogate_primary_key (Y/N)
385 -- o surrogate_pk_column_name
386 -- o table_alias
387 -- o short_name of the table
388 --
389 -- ------------------------------------------------------------------------
390 procedure get_table_info
391 (
392 p_table_id in number,
393 p_table_info out nocopy hr_dm_gen_main.t_table_info
394 )
395 is
396
397 --cursor to get the table_info
398 cursor csr_get_table is
399 select tbl.table_id
400 ,lower(tbl.table_name) table_name
401 ,tbl.datetrack
402 ,decode (tbl.surrogate_pk_column_name,NULL,'N',
403 'Y') surrogate_primary_key
404 ,lower(tbl.surrogate_pk_column_name) surrogate_pk_column_name
405 ,lower(tbl.table_alias) table_alias
406 ,lower(tbl.short_name) short_name
407 ,lower(who_link_alias) who_link_alias
408 ,derive_sql_download_full
409 ,derive_sql_download_add
410 ,derive_sql_calc_ranges
411 ,derive_sql_delete_source
412 ,derive_sql_source_tables
413 ,upper(tbl.global_data) global_data
414 ,sequence_name
415 from hr_dm_tables tbl
416 where tbl.table_id = p_table_id;
417
418 begin
419
420 hr_dm_utility.message('ROUT','entry:hr_dm_library.get_table_info', 5);
421 hr_dm_utility.message('PARA','(p_table_id - ' || p_table_id ||
422 ')', 10);
423 for csr_get_table_rec in csr_get_table loop
424 --
425 -- store the information of the table properties into pl/sql record
426 --
427 p_table_info.table_id := csr_get_table_rec.table_id;
428 p_table_info.table_name := csr_get_table_rec.table_name;
429 p_table_info.datetrack := csr_get_table_rec.datetrack;
430 p_table_info.surrogate_primary_key :=
431 csr_get_table_rec.surrogate_primary_key;
432 p_table_info.surrogate_pk_column_name :=
433 csr_get_table_rec.surrogate_pk_column_name;
434 p_table_info.alias := csr_get_table_rec.table_alias;
435 p_table_info.short_name := csr_get_table_rec.short_name;
436 p_table_info.who_link_alias := csr_get_table_rec.who_link_alias;
437 p_table_info.derive_sql_download_full :=
438 csr_get_table_rec.derive_sql_download_full;
439 p_table_info.derive_sql_download_add :=
440 csr_get_table_rec.derive_sql_download_add;
441 p_table_info.derive_sql_calc_ranges :=
442 csr_get_table_rec.derive_sql_calc_ranges;
443 p_table_info.derive_sql_delete_source :=
444 csr_get_table_rec.derive_sql_delete_source;
445 p_table_info.derive_sql_source_tables :=
446 csr_get_table_rec.derive_sql_source_tables;
447 p_table_info.global_data := csr_get_table_rec.global_data;
448 p_table_info.sequence_name := csr_get_table_rec.sequence_name;
449
450 end loop;
451 hr_dm_utility.message('INFO',
452 'HR_DM_LIBARARY - get information about tables',
453 15);
454
455 hr_dm_utility.message('PARA','(p_table_info.table_name - ' ||
456 p_table_info.table_name || ')', 30);
457 hr_dm_utility.message('ROUT','exit:hr_dm_library.get_table_info', 25);
458
459 exception
460 when others then
461 hr_dm_utility.error(SQLCODE,'hr_dm_library.get_table_info','(none)'
462 ,'R');
463 raise;
464 end get_table_info;
465 -- ------------------------- check_col_for_fk_on_aol ------------------------
466 -- Description:
467 -- It checks whether a given column name exists in the pl/sql table which
468 -- contains the list of all the columns which have foreign key on AOL table or
469 -- columns whose id value need to be resolved.
470 --Input parameters
471 -- p_fk_to_aol_columns_tbl - This can contain the list of columns which have
472 -- 'A' type hierarchy or 'L' type hierarchy.
473 -- p_column_name - Name of the column which needs to be searched in
474 -- the above list.
475 -- Out parameters
476 -- p_index - index of list, if it finds the given column in the above
477 -- list.
478 -----------------------------------------------------------------------------
479 procedure check_col_for_fk_on_aol
480 (
481 p_fk_to_aol_columns_tbl in hr_dm_gen_main.t_fk_to_aol_columns_tbl,
482 p_column_name in varchar2,
483 p_index out nocopy number
484 ) is
485 l_index number := p_fk_to_aol_columns_tbl.first;
486 begin
487 hr_dm_utility.message('ROUT','entry:hr_dm_library.check_col_for_fk_on_aol'
488 , 5);
489 hr_dm_utility.message('PARA','(p_column_name - ' || p_column_name || ')'
490 ,10);
491 p_index := null;
492
493 if p_fk_to_aol_columns_tbl.exists(1) then
494 l_index := p_fk_to_aol_columns_tbl.first;
495 else
496 l_index := null;
497 end if;
498 while l_index is not null loop
499 if upper(p_fk_to_aol_columns_tbl(l_index).column_name) = upper(p_column_name) then
500 p_index := l_index;
501 exit;
502 end if;
503 l_index := p_fk_to_aol_columns_tbl.next(l_index);
504 end loop;
505 hr_dm_utility.message('INFO','HR_DM_LIBARARY - checks whether ' ||
506 'column has FK on AOL table',15);
507 -- hr_dm_utility.message('SUMM','HR_DM_LIBARARY - checks whether ' ||
508 -- 'column has FK on AOL table',20);
509 hr_dm_utility.message('PARA','(p_index - ' || l_index || ')', 30);
510 hr_dm_utility.message('ROUT','exit:hr_dm_library.check_col_for_fk_on_aol',
511 25);
512
513 exception
514 when others then
515 hr_dm_utility.error(SQLCODE,'hr_dm_library.check_col_for_fk_on_aol',
516 '(none)','R');
517 raise;
518 end check_col_for_fk_on_aol;
519 -- ------------------------- populate_fk_to_aol_cols -----------------------
520 -- Description:
521 -- initially this procedure is designed to store the details of from hierarchies
522 -- table for AOL type hierarchy i.e hierarchy type 'A'. But we added another
523 -- hierarchy type 'L' for looking up the ID value i.e use the corresponding id
524 -- value of the parent table at destination for a given column.
525 -- It populates the PL/SQL table with all columns details stored in a
526 -- hr_dm_hierarchies table for a given table and hierarchy type.
527 -- Input Parameters
528 -- p_hierarchy_type - 'A' - AOL type hierarchy
529 -- 'L' - lookup type hierarchy.
530 ----------------------------------------------------------------------------
531 procedure populate_fk_to_aol_cols_info
532 (
533 p_table_info in hr_dm_gen_main.t_table_info,
534 p_fk_to_aol_columns_tbl out nocopy hr_dm_gen_main.t_fk_to_aol_columns_tbl,
535 p_hierarchy_type in varchar2 default 'A'
536 ) is
537
538 cursor csr_get_fk_to_aol_cols is
539 select lower(hir.column_name) column_name,
540 hir.parent_table_id parent_table_id,
541 lower(hir.parent_column_name) parent_column_name,
542 lower(hir.parent_id_column_name) parent_id_column_name,
543 lower(tbl.table_name) parent_table_name,
544 lower(tbl.table_alias) parent_table_alias
545 from hr_dm_tables tbl,
546 hr_dm_hierarchies hir
547 where hir.hierarchy_type = p_hierarchy_type
548 and tbl.table_id = hir.parent_table_id
549 and hir.table_id = (
550 select table_id
551 from hr_dm_tables
552 where table_name = (
553 select nvl(upload_table_name, table_name)
554 from hr_dm_tables
555 where table_id = p_table_info.table_id));
556
557 l_index number := 1;
558 begin
559 hr_dm_utility.message('ROUT',
560 'entry:hr_dm_library.populate_fk_to_aol_cols_info', 5);
561 hr_dm_utility.message('PARA','(p_table_id - ' || p_table_info.table_id ||
562 ')(p_table_name - ' || p_table_info.table_name ||
563 ')', 10);
564 for csr_get_fk_to_aol_cols_rec in csr_get_fk_to_aol_cols loop
565 p_fk_to_aol_columns_tbl(l_index).column_name :=
566 csr_get_fk_to_aol_cols_rec.column_name;
567 p_fk_to_aol_columns_tbl(l_index).parent_table_id :=
568 csr_get_fk_to_aol_cols_rec.parent_table_id;
569 p_fk_to_aol_columns_tbl(l_index).parent_column_name :=
570 csr_get_fk_to_aol_cols_rec.parent_column_name;
571 p_fk_to_aol_columns_tbl(l_index).parent_id_column_name :=
572 csr_get_fk_to_aol_cols_rec.parent_id_column_name;
573 p_fk_to_aol_columns_tbl(l_index).parent_table_name :=
574 csr_get_fk_to_aol_cols_rec.parent_table_name;
575 p_fk_to_aol_columns_tbl(l_index).parent_table_alias :=
576 csr_get_fk_to_aol_cols_rec.parent_table_alias;
577 l_index := l_index + 1;
578 end loop;
579 hr_dm_utility.message('INFO','HR_DM_LIBARARY - populate list with ' ||
580 'column who have FK on AOL table',15);
581 --hr_dm_utility.message('SUMM','HR_DM_LIBARARY - populate list with ' ||
582 -- 'column who have FK on AOL table',20);
583 hr_dm_utility.message('PARA','(p_index - ' || l_index || ')', 30);
584 hr_dm_utility.message('ROUT',
585 'exit:hr_dm_library.populate_fk_to_aol_cols_info',
586 25);
587
588 exception
589 when others then
590 hr_dm_utility.error(SQLCODE,
591 'hr_dm_library.populate_fk_to_aol_cols_info',
592 '(none)','R');
593 raise;
594 end populate_fk_to_aol_cols_info;
595
596 -- ------------------------- populate_columns_list ------------------------
597 -- Description:
598 -- It populates the PL/SQL table with the list of column. This is to avoid
599 -- database access getting the column list again.
600 -- e.g : Table T1 has column col1,col2then the out parameter list will be
601 -- populated as
602 -- p_columns_list = col1 | col2
603 -- p_parameter_list = p_col1 in number | p_col2 in varchar2
604 --
605 -- Input Parameters :
606 -- p_table_info - pl/sql table contains info like table name and
607 -- various properties of the table.
608 -- p_fk_to_aol_columns_tbl
609 -- - pl/sql table which contains the information about
610 -- columns which have foreign key to AOL table.
611 -- Output Parameters
612 -- p_columns_tbl - Out pl/sql table type t_varchar2_tbl. It contains
613 -- the list of columns of the table.
614 -- p_parameter_tbl - Out pl/sql table type t_varchar2_tbl. It contains
615 -- the list of column name used as a input arguments
616 -- in the procedure.
617 -- p_aol_columns_tbl Out pl/sql table type t_varchar2_tbl. It contains
618 -- the list of columns of the table but the columns
619 -- which have
620 -- p_aol_parameter_tbl - Out pl/sql table type t_varchar2_tbl. It contains
621 -- the list of column name used as a input arguments
622 -- in the procedure.
623 -- ------------------------------------------------------------------------
624 procedure populate_columns_list
625 (
626 p_table_info in hr_dm_gen_main.t_table_info,
627 p_fk_to_aol_columns_tbl in hr_dm_gen_main.t_fk_to_aol_columns_tbl,
628 p_columns_tbl out nocopy t_varchar2_tbl,
629 p_parameter_tbl out nocopy t_varchar2_tbl,
630 p_aol_columns_tbl out nocopy t_varchar2_tbl,
631 p_aol_parameter_tbl out nocopy t_varchar2_tbl,
632 p_missing_who_info out nocopy varchar2
633 )
634 is
635
636 -- used for indexing of pl/sql table.
637 l_count number;
638 l_index number;
639 l_missing_who_info varchar2(1);
640 l_apps_name varchar2(30);
641
642 cursor csr_apps_name is
643 select ORACLE_USERNAME
644 from fnd_oracle_userid
645 where ORACLE_ID = 900;
646
647 -- cursor to get the column name and data type
648 cursor csr_get_columns is
649 select distinct lower(column_name) col_name,
650 lower(data_type) data_type
651 from all_tab_columns
652 where table_name = upper(p_table_info.table_name)
653 and column_name <> 'BATCH_ID'
654 and data_type <> 'SDO_GEOMETRY'
655 and owner in
656 (l_apps_name,
657 l_fnd_owner,
658 l_ff_owner,
659 l_ben_owner,
660 l_pay_owner,
661 l_per_owner);
662
663 begin
664
665 hr_dm_utility.message('ROUT','entry:hr_dm_library.populate_columns_list'
666 ,5);
667 hr_dm_utility.message('PARA','(p_table_id - ' || p_table_info.table_id ||
668 ')(p_table_name - ' || p_table_info.table_name ||
669 ')', 10);
670
671 open csr_apps_name;
672 fetch csr_apps_name into l_apps_name;
673 close csr_apps_name;
674
675
676 -- initialise the counter.
677 l_count := 1;
678
679 -- assume who info i.e last_update column is missing.
680 p_missing_who_info := 'Y';
681 --
682 -- add the column and parameters to the list
683 --
684 for csr_get_columns_rec in csr_get_columns loop
685 --
686 -- if the column name is 'last_update_date' then it means that table
687 -- contains who column info.
688 --
689 if csr_get_columns_rec.col_name = 'last_update_date' then
690 p_missing_who_info := 'N';
691 end if;
692
693 p_columns_tbl(l_count) := csr_get_columns_rec.col_name;
694
695 p_parameter_tbl(l_count) := rpad('p_' || csr_get_columns_rec.col_name,30)
696 || ' in ' || csr_get_columns_rec.data_type;
697
698 --
699 -- if the table has a foreign key on the aol table then prepare the
700 -- column and parameter list wich will replace the id column which
701 -- has a foreign key to the developer key name.
702 -- e.g user_id column in per_sec_profile_assignments has a foreign key
703 -- on AOL table fnd_user. In this list do not store the user_id column
704 -- but store the developer key of fnd_user table corresponding to user_id
705 -- which is user name in this case.
706 --
707 if p_table_info.fk_to_aol_table = 'Y' then
708
709 check_col_for_fk_on_aol(p_fk_to_aol_columns_tbl,
710 csr_get_columns_rec.col_name,
711 l_index);
712 --
713 -- if l_index is null then it means that the column does not have
714 -- foreign key on the aol table,other wise it passes the index of
715 -- pl/sql table from which the information about aol table and
716 -- developer key.
717 --
718 if l_index is null then
719 p_aol_columns_tbl(l_count) := csr_get_columns_rec.col_name;
720
721 p_aol_parameter_tbl(l_count) :=
722 rpad('p_' || csr_get_columns_rec.col_name
723 ,30) || ' in ' ||
724 csr_get_columns_rec.data_type;
725 else
726 p_aol_columns_tbl(l_count) :=
727 p_fk_to_aol_columns_tbl(l_index).parent_column_name;
728
729 p_aol_parameter_tbl(l_count) := rpad('p_' ||
730 p_fk_to_aol_columns_tbl(l_index).parent_column_name,30)
731 || ' in varchar2';
732 end if;
733 end if;
734 l_count := l_count + 1;
735 end loop;
736 l_missing_who_info := p_missing_who_info;
737 hr_dm_utility.message('INFO','HR_DM_LIBARARY - populate list with ' ||
738 'all columns of table',15);
739 --hr_dm_utility.message('SUMM','HR_DM_LIBARARY - - populate list with ' ||
740 -- 'all columns of table',20);
741
742 hr_dm_utility.message('PARA','(p_missing_who_info - ' || l_missing_who_info
743 || ')( p_columns_tbl - varchar2 record type )'
744 || ')( p_parameter_tbl - varchar2 record type )'
745 || ')( p_aol_columns_tbl - varchar2 record type )'
746 || ')( p_aol_parameter_tbl - varchar2 record type )'
747 ,30);
748 hr_dm_utility.message('ROUT','exit:hr_dm_library.populate_columns_list',
749 25);
750 exception
751 when others then
752 hr_dm_utility.error(SQLCODE,'hr_dm_library.populate_columns_list',
753 '(none)','R');
754 raise;
755 end populate_columns_list;
756
757 -- ------------------------- populate_pk_columns_list -----------------------
758 -- Description:
759 -- It populates the PL/SQL table with the list of primary key column. This is
760 -- to avoid database access getting the column list again.
761 -- e.g : Table T1 has primary key columns pk_col1,pk_col2 then the out
762 -- parameter list will be populated as
763 -- p_columns_list = pk_col1 | pk_col2
764 -- p_parameter_list = p_pk_col1 in number | p_pk_col2 in varchar2
765 --
766 -- Input Parameters :
767 -- p_table_info - pl/sql table contains info like table name and
768 -- various properties of the table.
769 -- Output Parameters
770 -- p_pk_columns_tbl - Out pl/sql table type t_varchar2_tbl. It
771 -- contains the list ofprimary key columns of the
772 -- table.
773 -- p_pk_parameter_tbl - Out pl/sql table type t_varchar2_tbl. It
774 -- contains the list of primary key column name
775 -- used as a input arguments in the procedure.
776 -- p_no_of_pk_columns - Out number of primary key columns in the
777 -- primary key.
778 -- ------------------------------------------------------------------------
779 procedure populate_pk_columns_list
780 (
781 p_table_info in hr_dm_gen_main.t_table_info,
782 p_pk_columns_tbl out nocopy t_varchar2_tbl,
783 p_pk_parameter_tbl out nocopy t_varchar2_tbl,
784 p_no_of_pk_columns out nocopy number
785 )
786 is
787
788 -- used for indexing of pl/sql table.
789 l_count number;
790 l_index number;
791 l_data_type varchar2(30);
792 e_fatal_error exception;
793 l_fatal_error_message varchar2(200);
794 -- cursor to get the primary column name and data type
795 cursor csr_get_pk_columns is
796 select distinct lower(atc.column_name) col_name,
797 lower(atc.data_type) data_type,
798 acc.position
799 from all_tab_columns atc,
800 all_cons_columns acc,
801 all_constraints ac
802 where ac.table_name = upper(p_table_info.table_name)
803 and ac.constraint_type = 'P'
804 and ac.CONSTRAINT_NAME = acc.constraint_name
805 and ac.owner = acc.owner
806 and atc.table_name = acc.table_name
807 and atc.column_name = acc.column_name
808 and atc.owner = acc.owner
809 and ac.owner in
810 (l_apps_owner,
811 l_fnd_owner,
812 l_ff_owner,
813 l_ben_owner,
814 l_pay_owner,
815 l_per_owner)
816 order by acc.position;
817
818 -- get the logical primary key stored in hr_dm_hierarchies table
819 -- with type 'P'
820
821 cursor csr_get_columns is
822 select lower(column_name) col_name
823 from hr_dm_hierarchies
824 where table_id = p_table_info.table_id
825 and hierarchy_type = 'P';
826
827
828 begin
829 hr_dm_utility.message('ROUT',
830 'entry:hr_dm_library.populate_pk_columns_list', 5);
831 hr_dm_utility.message('PARA','(p_table_id - ' || p_table_info.table_id ||
832 ')(p_table_name - ' || p_table_info.table_name ||
833 ')', 10);
834
835
836 --
837 -- If the table has surrogate id then get the details of primary key from
838 -- table info, otherwise, open the cursor to read the columns from the
839 -- primary key constraint. Add the primary key column and parameters to
840 -- the list.
841 --
842 if p_table_info.surrogate_primary_key = 'Y' then
843 -- initialise the counter.
844 l_count := 1;
845 p_pk_columns_tbl(l_count) := p_table_info.surrogate_pk_column_name;
846 p_pk_parameter_tbl(l_count) := rpad('p_' ||
847 p_table_info.surrogate_pk_column_name,30) || ' in ' ||
848 'number';
849 elsif p_table_info.missing_primary_key = 'Y' then
850 l_count := 0;
851 --
852 -- add the logical prinamry key column and parameters to the list
853 --
854 for csr_get_columns_rec in csr_get_columns loop
855 l_count := l_count + 1;
856 -- add logical primary key column to the list.
857 p_pk_columns_tbl(l_count) := csr_get_columns_rec.col_name;
858 -- get the data type of the column
859 get_data_type ( p_table_name => p_table_info.table_name,
860 p_column_name => csr_get_columns_rec.col_name,
861 p_data_type => l_data_type);
862
863 p_pk_parameter_tbl(l_count) :=
864 rpad('p_' || csr_get_columns_rec.col_name ,30)
865 || ' in ' || l_data_type;
866 end loop;
867 else
868 l_count := 0;
869 -- non surrogate id. get primary key columns.
870 for csr_get_pk_columns_rec in csr_get_pk_columns loop
871 l_count := l_count + 1;
872 p_pk_columns_tbl(l_count) := csr_get_pk_columns_rec.col_name;
873 p_pk_parameter_tbl(l_count) :=
874 rpad('p_' || csr_get_pk_columns_rec.col_name,30)
875 || ' in ' || csr_get_pk_columns_rec.data_type;
876 end loop;
877
878 -- no primary keys found
879 if l_count = 0 then
880 l_fatal_error_message := 'Could not find primary key for (' ||
881 'p_table_name - ' || p_table_info.table_name || ').' ||
882 'Define the logical' || ' primary key in ' ||
883 'HR_DM_HIERARCHIES table.';
884 raise e_fatal_error;
885 end if;
886 end if;
887
888 p_no_of_pk_columns := l_count;
889 hr_dm_utility.message('INFO','HR_DM_LIBARARY - populate list with ' ||
890 'primary key columns of table',15);
891 --hr_dm_utility.message('SUMM','HR_DM_LIBARARY - - populate list with ' ||
892 -- 'primary key columns of table',20);
893
894 hr_dm_utility.message('PARA',
895 '(p_no_of_pk_columns - ' || p_no_of_pk_columns
896 || ')( p_pk_columns_tbl - varchar2 record type )'
897 || ')( p_pk_parameter_tbl - varchar2 record type )'
898 ,30);
899 hr_dm_utility.message('ROUT','exit:hr_dm_library.populate_pk_columns_list',
900 25);
901
902 exception
903 when e_fatal_error then
904 hr_dm_utility.error(SQLCODE,'hr_dm_library.populate_pk_columns_list',
905 l_fatal_error_message,'R');
906
907 raise;
908 when others then
909 hr_dm_utility.error(SQLCODE,'hr_dm_library.populate_pk_columns_list',
910 '(none)','R');
911 raise;
912 end populate_pk_columns_list;
913
914
915 -- ------------------------- populate_hierarchy_cols_list ------------------
916 -- Description:
917 -- It populates the PL/SQL table with the list of hierarchy column. This is
918 -- to avoid database access getting the column list again.
919 -- e.g : Table T1 has column col1,col2then the out parameter list will be
920 -- populated as
921 -- p_hier_columns_list = col1 | col2
922 -- p_hier_parameter_list = p_col1 in number | p_col2 in varchar2
923 --
924 -- Input Parameters :
925 -- p_table_info - Information about table in PL/SQL Table.
926 -- Output Parameters
927 -- p_hier_columns_tbl - Out pl/sql table type t_varchar2_tbl. It
928 -- contains the list of hierarchy columns in
929 -- a table. The list content varies depending
930 -- upon the value of p_called_from parameter.
931 -- If it is called from
932 -- TUPS - it contains only the list of hierarchy
933 -- columns
934 -- TDS - it contains the list of hierarchy columns
935 -- and the primary key column names also.
936 -- p_hier_parameter_tbl - Out pl/sql table type t_varchar2_tbl. It
937 -- contains the list of hierarchy columns and primary
938 -- key columns used as a input arguments in the
939 -- procedure.
940 -- p_called_from - It can have following values :
941 -- TUPS - if this function is called from TUPS
942 -- generator package.
943 -- TDS - if this function is called from TDS
944 -- generator package.
945 -- ------------------------------------------------------------------------
946 procedure populate_hierarchy_cols_list
947 (
948 p_table_info in hr_dm_gen_main.t_table_info,
949 p_hier_columns_tbl out nocopy t_varchar2_tbl,
950 p_hier_parameter_tbl out nocopy t_varchar2_tbl,
951 p_called_from in varchar2
952 )
953 is
954
955 -- get all parent tables required to get the business group id, if business
956 -- group id is not there in the table to be downloaded.
957
958 cursor csr_get_columns is
959 select lower(column_name) col_name
960 from hr_dm_hierarchies
961 where table_id = p_table_info.table_id
962 and hierarchy_type = 'H';
963
964 -- cursor to get the primary column name and data type
965 cursor csr_get_pk_columns is
966 select distinct lower(atc.column_name) col_name,
967 lower(atc.data_type) data_type,
968 acc.position
969 from all_tab_columns atc,
970 all_cons_columns acc,
971 all_constraints ac
972 where ac.table_name = upper(p_table_info.table_name)
973 and ac.constraint_type = 'P'
974 and ac.CONSTRAINT_NAME = acc.constraint_name
975 and ac.owner = acc.owner
976 and atc.table_name = acc.table_name
977 and atc.column_name = acc.column_name
978 and atc.owner = acc.owner
979 and ac.owner in
980 (l_apps_owner,
981 l_fnd_owner,
982 l_ff_owner,
983 l_ben_owner,
984 l_pay_owner,
985 l_per_owner)
986 order by acc.position;
987
988 -- used for indexing of pl/sql table.
989 l_count number;
990
991
992 begin
993 hr_dm_utility.message('ROUT',
994 'entry:hr_dm_library.populate_hierarchy_cols_list', 5);
995 hr_dm_utility.message('PARA','(p_table_id - ' || p_table_info.table_id ||
996 ')(p_table_name - ' || p_table_info.table_name ||
997 ')(p_called_from - ' || p_called_from ||
998 ')', 10);
999 -- initialise the counter.
1000 l_count := 1;
1001 --
1002 -- add the column and parameters to the list
1003 --
1004 for csr_get_columns_rec in csr_get_columns loop
1005
1006 p_hier_columns_tbl(l_count) := csr_get_columns_rec.col_name;
1007
1008 p_hier_parameter_tbl(l_count) := rpad('p_' || csr_get_columns_rec.col_name
1009 ,30) || ' in number';
1010 l_count := l_count + 1;
1011 end loop;
1012
1013
1014 -- if the table has a surrogate key then add the surrogate column to the
1015 --list, otherwise, all the primary key columns for non surrogate id table.
1016
1017 if p_table_info.surrogate_primary_key = 'Y' then
1018
1019 -- add primary column to the hierarchy list only if it called from TDS.
1020 if p_called_from = 'TDS' then
1021 p_hier_columns_tbl(l_count) := p_table_info.surrogate_pk_column_name;
1022 end if;
1023 p_hier_parameter_tbl(l_count) := rpad('p_' ||
1024 p_table_info.surrogate_pk_column_name ,30) || ' in number';
1025 else
1026 for csr_get_pk_columns_rec in csr_get_pk_columns loop
1027 -- add primary columns to the hierarchy list only if it called from TDS.
1028 if p_called_from = 'TDS' then
1029 p_hier_columns_tbl(l_count) := csr_get_pk_columns_rec.col_name;
1030 end if;
1031
1032 p_hier_parameter_tbl(l_count) := rpad('p_' ||
1033 csr_get_pk_columns_rec.col_name ,30) || ' in ' ||
1034 csr_get_pk_columns_rec.data_type;
1035 l_count := l_count + 1;
1036 end loop;
1037 end if;
1038
1039 hr_dm_utility.message('INFO','HR_DM_LIBARARY - populate list with ' ||
1040 'all columns who have FK on same table table',15);
1041 --hr_dm_utility.message('SUMM','HR_DM_LIBARARY - - populate list with ' ||
1042 -- 'all columns who have FK on same table table',20);
1043
1044 hr_dm_utility.message('PARA','( p_hier_columns_tbl - varchar2 record type )'
1045 || ')( p_hier_parameter_tbl - varchar2 record type )'
1046 ,30);
1047 hr_dm_utility.message('ROUT',
1048 'exit:hr_dm_library.populate_hierarchy_cols_list',
1049 25);
1050 exception
1051 when others then
1052 hr_dm_utility.error(SQLCODE,'hr_dm_library.populate_hierarchy_cols_list',
1053 '(none)','R');
1054 raise;
1055 end populate_hierarchy_cols_list;
1056
1057 -- ------------------------- get_cols_list_wo_pk_cols -----------------------
1058 -- Description:
1059 -- This procedure returns list of columns of the table which are not the
1060 -- part of primary columns. It is used by TUPS for generating update_dml
1061 -- as we do not want to update the primary key columns.
1062 -- Input parameters :
1063 -- p_columns_tbl - List of all columns of table.
1064 -- p_pk_columns_tbl - List of primary key columns of table
1065 --
1066 -- Output Parameter:
1067 -- p_cols_wo_pk_cols_tbl - List of columns of table which are not the
1068 -- part of primary key.
1069 --
1070 -- It checks whether a given column name exists in the pl/sql table which
1071 -- contains the list of all the columns which have foreign key on AOL table.
1072 -----------------------------------------------------------------------------
1073 procedure get_cols_list_wo_pk_cols
1074 (
1075 p_columns_tbl in hr_dm_library.t_varchar2_tbl,
1076 p_pk_columns_tbl in hr_dm_library.t_varchar2_tbl,
1077 p_cols_wo_pk_cols_tbl out nocopy hr_dm_library.t_varchar2_tbl
1078 ) is
1079
1080 l_index number := p_columns_tbl.first;
1081 l_count number := 1;
1082 --
1083 -- private function to check whether a given element exists in the list.
1084 -- This checks whether a given column exists in the primary key column list.
1085 --
1086
1087 function private_is_pk_column ( p_column_name in varchar2)
1088 return varchar2 is
1089 l_index number := p_pk_columns_tbl.first;
1090 l_return varchar2(1) := 'N';
1091 begin
1092 while l_index is not null loop
1093 if p_pk_columns_tbl(l_index) = p_column_name then
1094 l_return := 'Y';
1095 exit;
1096 end if;
1097 l_index := p_pk_columns_tbl.next(l_index);
1098 end loop;
1099 return l_return;
1100 exception
1101 when others then
1102 hr_dm_utility.error(SQLCODE,'hr_dm_library.private_is_pk_column',
1103 '( p_column_name = ' || p_column_name || ')','R');
1104 raise;
1105 end;
1106 begin
1107 hr_dm_utility.message('ROUT','entry:hr_dm_library.get_cols_list_wo_pk_cols'
1108 , 5);
1109
1110 while l_index is not null loop
1111
1112 -- if the column read is not primary key column then add the
1113 -- column to out list.
1114
1115 if private_is_pk_column (p_columns_tbl(l_index)) = 'N' then
1116 p_cols_wo_pk_cols_tbl(l_count) := p_columns_tbl(l_index);
1117 l_count := l_count + 1;
1118 end if;
1119 l_index := p_columns_tbl.next(l_index);
1120 end loop;
1121
1122 hr_dm_utility.message('ROUT','exit:hr_dm_library.get_cols_list_wo_pk_cols',
1123 25);
1124 exception
1125 when others then
1126 hr_dm_utility.error(SQLCODE,'hr_dm_library.get_cols_list_wo_pk_cols',
1127 '(none)','R');
1128 raise;
1129 end get_cols_list_wo_pk_cols;
1130 -- ------------------------- conv_list_to_text ---------------------------
1131 -- Description:
1132 -- It reads the list elements and converts them to varchar2 text in which
1133 -- each element is separated by comma and put into the next line. Each
1134 -- element is padded with the given number of spaces. e.g
1135 -- A list contains col1,col2,col3 as elements.
1136 -- It will put the output as
1137 -- col1,
1138 -- col2,
1139 -- col3
1140 --
1141 -- Note: There is a non-overloaded version of this function. Changes should
1142 -- be applied to both versions where applicable.
1143 --
1144 -- Input Parameters :
1145 -- p_rpad_spaces - Number of blank spaces added before writing the
1146 -- element on new line.
1147 -- p_pad_first_line - It means whether the spaces should be added to
1148 -- the first element or not.
1149 -- 'Y' - spaces are added to the first element
1150 -- 'N' - spaces are not added to the first element
1151 -- p_prefix_col - Prefix the element with this value. e.g if
1152 -- p_prefix_col is 'p_' then all elements will be
1153 -- prefixed with p_ and our list output will be
1154 -- p_col1,
1155 -- p_col2,
1156 -- p_col3.
1157 -- p_columns_tbl - List of the columns or elements which is required
1158 -- to be changed into text.
1159 -- It is of pl/sql type t_varchar2_tbl,
1160 -- p_col_length - It adds the spaces after the column name so as
1161 -- to make the column name length same for each
1162 -- column by adding the required number of spaces.
1163 -- p_start_terminator This is put at the begning of the assignment from
1164 -- the second element onwards. By default it is ','
1165 -- but in some cases it can be 'and' especially use
1166 -- by TUPS for generating where clause for composite
1167 -- primary keys.
1168 -- p_end_terminator This is put at the end of the assignment. It is
1169 -- null most of the time and is used by Date Track
1170 -- TUPS with composite key to terminate the
1171 -- assignment with ';'.
1172 -- Returns
1173 -- It returns a string by putting each element of the table into a newline.
1174 -- ------------------------------------------------------------------------
1175 function conv_list_to_text
1176 (
1177 p_rpad_spaces in number,
1178 p_pad_first_line in varchar2 default 'N',
1179 p_prefix_col in varchar2 default null,
1180 p_columns_tbl in t_varchar2_tbl,
1181 p_col_length in number default 30,
1182 p_start_terminator in varchar2 default ',',
1183 p_end_terminator in varchar2 default null
1184 )
1185 return varchar2 is
1186
1187 l_out_text varchar2(20000);
1188 l_list_index number;
1189 l_count number;
1190
1191 begin
1192 hr_dm_utility.message('ROUT','entry:hr_dm_library.conv_list_to_text', 5);
1193 hr_dm_utility.message('PARA','(p_rpad_spaces - ' || p_rpad_spaces ||
1194 ')(p_pad_first_line - ' || p_pad_first_line ||
1195 ')(p_prefix_col - ' || p_prefix_col ||
1196 ')(p_col_length - ' || p_col_length ||
1197 ')(p_start_terminator - ' || p_start_terminator ||
1198 ')(p_end_terminator - ' || p_end_terminator ||
1199 ')(p_columns_tbl - varchar2 record type)' , 10);
1200
1201 -- initialise the variables
1202 l_list_index := p_columns_tbl.first;
1203 l_count := 1;
1204 --
1205 -- read all the elements of pl/sql table and append them into text.
1206 --
1207 while l_list_index is not null loop
1208 --
1209 -- seperate the elements by comma and move it to next line. Put comma
1210 -- only after first element.
1211 --
1212 if l_count = 1 then
1213 -- if p_pad_first_line = 'Y' then insert space in the first line,otherwise
1214 -- don't pad the first element.
1215 if p_pad_first_line = 'Y' then
1216 --l_out_text := rpad(' ', p_rpad_spaces + 1) || p_prefix_col ||
1217 l_out_text := rpad(' ', p_rpad_spaces) || p_prefix_col ||
1218 rtrim(rpad(p_columns_tbl(l_list_index), p_col_length)) ||
1219 p_end_terminator;
1220 else
1221 l_out_text := p_prefix_col || rtrim(rpad(p_columns_tbl(l_list_index),
1222 p_col_length)) ||
1223 p_end_terminator;
1224 end if;
1225 else
1226 l_out_text := l_out_text || c_newline || rpad(' ', p_rpad_spaces) ||
1227 p_start_terminator || p_prefix_col ||
1228 rtrim(rpad(p_columns_tbl(l_list_index),
1229 p_col_length)) ||
1230 p_end_terminator;
1231 end if;
1232 l_list_index := p_columns_tbl.next(l_list_index);
1233 l_count := l_count + 1;
1234 end loop;
1235 l_out_text := rtrim(l_out_text);
1236 hr_dm_utility.message('INFO','HR_DM_LIBARARY - convert list elements ' ||
1237 'into formatted text',15);
1238 --hr_dm_utility.message('SUMM','HR_DM_LIBARARY - convert list elements ' ||
1239 -- 'into formatted text',20);
1240
1241 hr_dm_utility.message('PARA','(l_out_text - formatted text)' ,30);
1242
1243 hr_dm_utility.message('ROUT','exit:hr_dm_library.conv_list_to_text',
1244 25);
1245
1246 return l_out_text;
1247 exception
1248 when others then
1249 hr_dm_utility.error(SQLCODE,'hr_dm_library.conv_list_to_text',
1250 '(none)','R');
1251 raise;
1252 end conv_list_to_text ;
1253
1254 -- ------------------------- conv_list_to_text ---------------------------
1255 -- Description:
1256 -- It reads the list elements and converts them to varchar2 text in which
1257 -- each element is separated by comma and put into the next line. Each
1258 -- element is padded with the given number of spaces. e.g
1259 -- A list contains col1,col2,col3 as elements.
1260 -- It will put the output as
1261 -- col1,
1262 -- col2,
1263 -- col3
1264 --
1265 -- Note: There is a non-overloaded version of this function. Changes should
1266 -- be applied to both versions where applicable.
1267 --
1268 -- Input Parameters :
1269 -- p_rpad_spaces - Number of blank spaces added before writing the
1270 -- element on new line.
1271 -- p_pad_first_line - It means whether the spaces should be added to
1272 -- the first element or not.
1273 -- 'Y' - spaces are added to the first element
1274 -- 'N' - spaces are not added to the first element
1275 -- p_prefix_col - Prefix the element with this value. e.g if
1276 -- p_prefix_col is 'p_' then all elements will be
1277 -- prefixed with p_ and our list output will be
1278 -- p_col1,
1279 -- p_col2,
1280 -- p_col3.
1281 -- p_columns_tbl - List of the columns or elements which is required
1282 -- to be changed into text.
1283 -- It is of pl/sql type t_varchar2_tbl,
1284 -- p_col_length - It adds the spaces after the column name so as
1285 -- to make the column name length same for each
1286 -- column by adding the required number of spaces.
1287 -- p_start_terminator This is put at the begning of the assignment from
1288 -- the second element onwards. By default it is ','
1289 -- but in some cases it can be 'and' especially use
1290 -- by TUPS for generating where clause for composite
1291 -- primary keys.
1292 -- p_end_terminator This is put at the end of the assignment. It is
1293 -- null most of the time and is used by Date Track
1294 -- TUPS with composite key to terminate the
1295 -- assignment with ';'.
1296 -- p_overide_tbl A table which lists which columns should be
1297 -- prefixed by an alternative to p_prefix_col
1298 -- p_overide_prefix Prefix to use instead of p_prefix_col
1299 -- Returns
1300 -- It returns a string by putting each element of the table into a newline.
1301 -- ------------------------------------------------------------------------
1302 function conv_list_to_text
1303 (
1304 p_rpad_spaces in number,
1305 p_pad_first_line in varchar2 default 'N',
1306 p_prefix_col in varchar2 default null,
1307 p_columns_tbl in t_varchar2_tbl,
1308 p_col_length in number default 30,
1309 p_start_terminator in varchar2 default ',',
1310 p_end_terminator in varchar2 default null,
1311 p_overide_tbl in hr_dm_gen_main.t_fk_to_aol_columns_tbl,
1312 p_overide_prefix in varchar2 default null
1313 )
1314 return varchar2 is
1315
1316 l_out_text varchar2(20000);
1317 l_list_index number;
1318 l_count number;
1319 l_prefix varchar2(30);
1320 l_exc_index number;
1321
1322 begin
1323 hr_dm_utility.message('ROUT','entry:hr_dm_library.conv_list_to_text', 5);
1324 hr_dm_utility.message('PARA','(p_rpad_spaces - ' || p_rpad_spaces ||
1325 ')(p_pad_first_line - ' || p_pad_first_line ||
1326 ')(p_prefix_col - ' || p_prefix_col ||
1327 ')(p_col_length - ' || p_col_length ||
1328 ')(p_start_terminator - ' || p_start_terminator ||
1329 ')(p_end_terminator - ' || p_end_terminator ||
1330 ')(p_columns_tbl - varchar2 record type' ||
1331 ')(p_overide_tbl - varchar2 record type' ||
1332 ')(p_overide_prefix - ' || p_overide_prefix || ')' , 10);
1333
1334
1335 -- show entries
1336 l_list_index := p_columns_tbl.first;
1337 while l_list_index is not null loop
1338 hr_dm_utility.message('INFO','col_tbl - ' || p_columns_tbl(l_list_index),1);
1339 l_list_index := p_columns_tbl.next(l_list_index);
1340 end loop;
1341 l_exc_index := p_overide_tbl.first;
1342 while l_exc_index is not null loop
1343 hr_dm_utility.message('INFO','exc_tbl - ' || p_overide_tbl(l_exc_index).column_name,1);
1344 l_exc_index := p_overide_tbl.next(l_exc_index);
1345 end loop;
1346
1347 -- initialise the variables
1348 l_list_index := p_columns_tbl.first;
1349 l_count := 1;
1350 --
1351 -- read all the elements of pl/sql table and append them into text.
1352 --
1353 while l_list_index is not null loop
1354 --
1355 -- assume prefix is the default one
1356 l_prefix := p_prefix_col;
1357 -- reset exc index
1358 --
1359 l_exc_index := p_overide_tbl.first;
1360 --
1361 -- Check if this column is in the exception list
1362 --
1363 while l_exc_index is not null loop
1364 if p_overide_tbl(l_exc_index).column_name = p_columns_tbl(l_list_index) then
1365 hr_dm_utility.message('INFO',p_columns_tbl(l_list_index) || ' - ' ||
1366 p_overide_tbl(l_exc_index).column_name,1);
1367 l_prefix := p_overide_prefix;
1368 end if;
1369 l_exc_index := p_overide_tbl.next(l_exc_index);
1370 end loop;
1371 --
1372 --
1373 -- seperate the elements by comma and move it to next line. Put comma
1374 -- only after first element.
1375 --
1376 if l_count = 1 then
1377 -- if p_pad_first_line = 'Y' then insert space in the first line,otherwise
1378 -- don't pad the first element.
1379 if p_pad_first_line = 'Y' then
1380 l_out_text := rpad(' ', p_rpad_spaces) || l_prefix ||
1381 rtrim(rpad(p_columns_tbl(l_list_index), p_col_length)) ||
1382 p_end_terminator;
1383 else
1384 l_out_text := l_prefix || rtrim(rpad(p_columns_tbl(l_list_index),
1385 p_col_length)) ||
1386 p_end_terminator;
1387 end if;
1388 else
1389 l_out_text := l_out_text || c_newline || rpad(' ', p_rpad_spaces) ||
1390 p_start_terminator || l_prefix ||
1391 rtrim(rpad(p_columns_tbl(l_list_index), p_col_length)) ||
1392 p_end_terminator;
1393 end if;
1394 l_list_index := p_columns_tbl.next(l_list_index);
1395 l_count := l_count + 1;
1396 end loop;
1397 l_out_text := rtrim(l_out_text);
1398 hr_dm_utility.message('INFO','HR_DM_LIBARARY - convert list elements ' ||
1399 'into formatted text',15);
1400 --hr_dm_utility.message('SUMM','HR_DM_LIBARARY - convert list elements ' ||
1401 -- 'into formatted text',20);
1402
1403 hr_dm_utility.message('PARA','(l_out_text - formatted text)' ,30);
1404
1405 hr_dm_utility.message('ROUT','exit:hr_dm_library.conv_list_to_text',
1406 25);
1407
1408 return l_out_text;
1409 exception
1410 when others then
1411 hr_dm_utility.error(SQLCODE,'hr_dm_library.conv_list_to_text',
1412 '(none)','R');
1413 raise;
1414 end conv_list_to_text ;
1415
1416
1417 -- ------------------------- get_nvl_arguement ---------------------------
1418 -- Description:
1419 -- It
1420 -- Input Parameters :
1421 -- p_test_with_nvl Flag to indicate if NVL testing is required, if
1422 -- so then return value is ''
1423 -- p_table_name Name of the table
1424 -- p_column_name Name of the column in the table
1425 -- p_nvl_prefix Prefix for nvl variable
1426 -- p_nvl_suffix Suffix for nvl variable
1427 -- Returns
1428 -- The string to be used for the nvl testing
1429 -- ------------------------------------------------------------------------
1430 procedure get_nvl_arguement
1431 (
1432 p_test_with_nvl in varchar2,
1433 p_table_name in varchar2,
1434 p_column_name in varchar2,
1435 p_nvl_prefix out nocopy varchar2,
1436 p_nvl_suffix out nocopy varchar2
1437 ) is
1438
1439 l_data_type varchar2(2000);
1440 l_apps_name varchar2(30);
1441 l_nullable varchar2(1);
1442
1443 cursor csr_apps_name is
1444 select ORACLE_USERNAME
1445 from fnd_oracle_userid
1446 where ORACLE_ID = 900;
1447
1448 cursor csr_data_type is
1449 select data_type,
1450 nullable
1451 from all_tab_columns
1452 where table_name = upper(p_table_name)
1453 and column_name = upper(p_column_name)
1454 and owner in
1455 (l_apps_name,
1456 l_fnd_owner,
1457 l_ff_owner,
1458 l_ben_owner,
1459 l_pay_owner,
1460 l_per_owner);
1461
1462 begin
1463
1464 hr_dm_utility.message('ROUT','entry:hr_dm_library.get_nvl_arguement', 5);
1465 hr_dm_utility.message('PARA','(p_test_with_nvl - ' || p_test_with_nvl ||
1466 ')(p_table_name - ' || p_table_name ||
1467 ')(p_column_name - ' || p_column_name ||
1468 ')', 10);
1469
1470 open csr_apps_name;
1471 fetch csr_apps_name into l_apps_name;
1472 close csr_apps_name;
1473
1474 open csr_data_type;
1475 fetch csr_data_type into l_data_type, l_nullable;
1476 close csr_data_type;
1477
1478 -- do we want to add NVL handling?
1479 if (p_test_with_nvl = 'N' or l_nullable = 'N') then
1480 p_nvl_prefix := '';
1481 p_nvl_suffix := '';
1482 else
1483 -- set default values
1484 p_nvl_prefix := 'NVL(';
1485 p_nvl_suffix := ',''<HRDM null value>'')';
1486 -- test number - one unlikely to be in the database
1487 if (l_data_type = 'NUMBER') then
1488 p_nvl_suffix := ',-9924926578)';
1489 end if;
1490 -- test date - hr_general.start_of_time
1491 if (l_data_type = 'DATE') then
1492 p_nvl_prefix := 'NVL(to_char(';
1493 p_nvl_suffix := ',''YYYY/MM/DD''),''<HRDM null value>'')';
1494 end if;
1495
1496
1497 end if;
1498
1499 hr_dm_utility.message('INFO','HR_DM_LIBARARY - found nvl arguement',15);
1500 --hr_dm_utility.message('SUMM','HR_DM_LIBARARY - found nvl arguement',20);
1501
1502 hr_dm_utility.message('PARA','(p_nvl_prefix - ' || p_nvl_prefix || ')' ,30);
1503 hr_dm_utility.message('PARA','(p_nvl_suffix - ' || p_nvl_suffix || ')' ,30);
1504 hr_dm_utility.message('ROUT','exit:hr_dm_library.get_nvl_arguement', 25);
1505
1506
1507 exception
1508 when others then
1509 hr_dm_utility.error(SQLCODE,'hr_dm_library.get_nvl_arguement',
1510 'Check seed data in Hierarchies / Tables','R');
1511 raise;
1512
1513 end get_nvl_arguement;
1514
1515
1516 -- ------------------------- get_func_asg ---------------------------
1517 -- Description:
1518 -- It reads the list columns and returns the parameters list required for
1519 -- inserting the data into data pump batch_lines table or any other TUPS
1520 -- function
1521 -- e.g p_col1 => p_col2,
1522 -- p_col2 => p_col2...
1523 -- Input Parameters :
1524 -- p_rpad_spaces - Number of blank spaces added before writing the
1525 -- element on new line.
1526 -- p_columns_tbl - List of the columns or elements which is required
1527 -- to be changed into text of parameter assignment.
1528 -- It is of pl/sql type t_varchar2_tbl,
1529 -- p_prefix_left_asg - Prefix the left element with this value. e.g if
1530 -- value is 'p_' then all elements on the left hand
1531 -- side of the assignment will be prefixed with 'p_'
1532 -- prefixed with p_ and our list output will be
1533 -- p_col1 => col1,
1534 -- p_col2 => col2,
1535 -- p_col3 => col3
1536 -- p_prefix_right_asg - Prefix the right element with this value. e.g if
1537 -- value is 'p_' then all elements on the right hand
1538 -- side of the assignment will be prefixed with 'p_'
1539 -- prefixed with p_ and our list output will be
1540 -- col1 => p_col1,
1541 -- col2 => p_col2,
1542 -- col3 => p_col3
1543 -- p_omit_business_group_id - It is whether to exclude the
1544 -- business_group_id assignment from the list.
1545 -- 'Y' - does not include business_group_id column
1546 -- for parameter assignment. (default value)
1547 -- 'N' - includes business_group_id column for
1548 -- parameter assignment.
1549 -- p_comma_on_first_line - Put the comma in the first element or not.
1550 -- 'Y' - puts the comma before the first element
1551 -- parameter assignment.
1552 -- 'N' - does not put comma before the first element
1553 -- parameter assignment.
1554 -- p_equality_sign - By default the equality sign of the parameter
1555 -- assignment is ' => '. But it can be '=' for
1556 -- update statement set column assignment.
1557 -- p_pad_first_line - It means whether the spaces should be added to
1558 -- the first element or not.
1559 -- 'Y' - spaces are added to the first element
1560 -- 'N' - spaces are not added to the first element
1561 -- p_left_asg_pad_len - It means the length of the left hand parameter
1562 -- after prefix. e.g p_prefix_left is 'p_' and
1563 -- column name is 'responsibility_application_id',
1564 -- if the length is 30 the left hand parameter will
1565 -- be 'p_responsibility_application_i'.
1566 -- p_right_asg_pad_len same as above but applied to right hand side
1567 -- parameter.
1568 -- p_start_terminator This is put at the begning of the assignment from
1569 -- the second element onwards. By default it is ','
1570 -- but in some cases it can be 'and' especially use
1571 -- by TUPS for generating where clause for composite
1572 -- primary keys.
1573 -- p_end_terminator This is put at the end of the assignment. It is
1574 -- null most of the time and is used by Date Track
1575 -- TUPS with composite key to terminate the
1576 -- assignment with ';'.
1577 -- p_test_with_nvl This is a flag used with the creation of the
1578 -- chk_row_exists cursor in the TUPS and forces
1579 -- the comparison to use NVL.
1580 -- p_table_name Name of the table
1581 -- Returns
1582 -- It returns a string by putting each element of the table into a newline.
1583 -- and sepearting the element assignment by terminator.
1584 -- ------------------------------------------------------------------------
1585 function get_func_asg
1586 (
1587 p_rpad_spaces in number,
1588 p_columns_tbl in t_varchar2_tbl,
1589 p_prefix_left_asg in varchar2 default 'p_',
1590 p_prefix_right_asg in varchar2 default 'p_',
1591 p_omit_business_group_id in varchar2 default 'Y',
1592 p_comma_on_first_line in varchar2 default 'Y',
1593 p_equality_sign in varchar2 default ' => ',
1594 p_pad_first_line in varchar2 default 'Y' ,
1595 p_left_asg_pad_len in number default 30,
1596 p_right_asg_pad_len in number default 30,
1597 p_start_terminator in varchar2 default ',' ,
1598 p_end_terminator in varchar2 default null,
1599 p_test_with_nvl in varchar2 default 'N',
1600 p_table_name in varchar2 default null
1601 )
1602 return varchar2 is
1603
1604 l_out_text varchar2(32767) := null;
1605 l_list_index number;
1606 l_nvl_left1 varchar2(100);
1607 l_nvl_left2 varchar2(100);
1608 l_nvl_right varchar2(100);
1609 l_nvl_indent varchar2(30);
1610
1611 begin
1612 hr_dm_utility.message('ROUT','entry:hr_dm_library.get_func_asg', 5);
1613 hr_dm_utility.message('PARA','( p_rpad_spaces - ' || p_rpad_spaces ||
1614 ')(p_prefix_left_asg - ' || p_prefix_left_asg ||
1615 ')( p_prefix_right_asg - ' || p_prefix_right_asg ||
1616 ')(p_omit_business_group_id - ' || p_omit_business_group_id ||
1617 ')(p_comma_on_first_line - ' || p_comma_on_first_line ||
1618 ')(p_equality_sign - ' || p_equality_sign ||
1619 ')(p_pad_first_line - ' || p_pad_first_line ||
1620 ')(p_left_asg_pad_len - ' || p_left_asg_pad_len ||
1621 ')(p_right_asg_pad_len - ' || p_right_asg_pad_len ||
1622 ')(p_start_terminator - ' || p_start_terminator ||
1623 ')(p_end_terminator - ' || p_end_terminator||
1624 ')(p_columns_tbl - varchar2 record type contains ' ||
1625 'list of columns' ||
1626 ')(p_test_with_nvl - ' || p_test_with_nvl || ')', 10);
1627
1628 -- initialise the variables
1629 l_list_index := p_columns_tbl.first;
1630
1631 -- if first line has to be padded then add spaces
1632 if p_pad_first_line = 'Y' then
1633 l_out_text := rpad(' ', p_rpad_spaces);
1634 end if;
1635
1636 -- if comma should not be put in the first element
1637 if p_comma_on_first_line = 'Y' then
1638 l_out_text := l_out_text || ',';
1639 else
1640 l_out_text := l_out_text || ' ';
1641 end if;
1642
1643 --
1644 -- if the first column is business group and omit business group flag is
1645 -- 'Y' then we do not want to include business group column hence get the
1646 -- next item.
1647 --
1648 if (upper(p_columns_tbl(l_list_index)) = 'BUSINESS_GROUP_ID' and
1649 p_omit_business_group_id = 'Y')
1650 then
1651 l_list_index := p_columns_tbl.next(l_list_index);
1652 end if;
1653
1654 l_nvl_left1 := '';
1655 -- do we want to add NVL handling?
1656 if (p_test_with_nvl = 'N') then
1657 l_nvl_indent := '';
1658 else
1659 l_nvl_indent := indent(12);
1660 end if;
1661
1662 get_nvl_arguement(p_test_with_nvl,
1663 p_table_name,
1664 p_columns_tbl(l_list_index),
1665 l_nvl_left2,
1666 l_nvl_right);
1667
1668 l_out_text := l_out_text ||
1669 rpad(l_nvl_left1 || l_nvl_left2 || p_prefix_left_asg ||
1670 p_columns_tbl(l_list_index) || l_nvl_right,p_left_asg_pad_len) ||
1671 l_nvl_indent ||
1672 p_equality_sign || rtrim(rpad(l_nvl_left1 || l_nvl_left2 || p_prefix_right_asg ||
1673 p_columns_tbl(l_list_index) || l_nvl_right ,p_right_asg_pad_len))
1674 || p_end_terminator;
1675
1676
1677 l_list_index := p_columns_tbl.next(l_list_index);
1678
1679
1680 --
1681 -- read all the elements of pl/sql table and append them into text.
1682 --
1683 while l_list_index is not null loop
1684 --
1685 -- seperate the elements by comma and move it to next line.Do not assign
1686 -- Business_Group_Id column as data pump knows the value of
1687 -- business_group_id if the parameter p_omit_business_group_id value is 'Y'
1688 --
1689 get_nvl_arguement(p_test_with_nvl,
1690 p_table_name,
1691 p_columns_tbl(l_list_index),
1692 l_nvl_left2,
1693 l_nvl_right);
1694
1695
1696 if (upper(p_columns_tbl(l_list_index)) <> 'BUSINESS_GROUP_ID' or
1697 p_omit_business_group_id = 'N')
1698 then
1699 l_out_text := l_out_text || c_newline || rpad(' ', p_rpad_spaces) ||
1700 p_start_terminator ||
1701 rpad(l_nvl_left1 || l_nvl_left2 || p_prefix_left_asg ||
1702 p_columns_tbl(l_list_index) || l_nvl_right,
1703 p_left_asg_pad_len) || l_nvl_indent || p_equality_sign ||
1704 rtrim(rpad(l_nvl_left1 || l_nvl_left2 || p_prefix_right_asg ||
1705 p_columns_tbl(l_list_index) || l_nvl_right,p_right_asg_pad_len)) ||
1706 p_end_terminator;
1707 end if;
1708 l_list_index := p_columns_tbl.next(l_list_index);
1709 end loop;
1710
1711 l_out_text := rtrim(l_out_text);
1712 hr_dm_utility.message('INFO','HR_DM_LIBARARY - convert list elements ' ||
1713 'into formatted text for call to procedure and ' ||
1714 ' named convention used for parameters ',15);
1715 --hr_dm_utility.message('SUMM','HR_DM_LIBARARY - - convert list elements ' ||
1716 -- 'into formatted text for call to procedure and ' ||
1717 -- ' named convention used for parameters ',20);
1718
1719 hr_dm_utility.message('PARA','(l_out_text - formatted text for funtion ' ||
1720 'assignment )' ,30);
1721 hr_dm_utility.message('ROUT','exit:hr_dm_library.get_func_asg',
1722 25);
1723 return l_out_text;
1724 exception
1725 when others then
1726 hr_dm_utility.error(SQLCODE,'hr_dm_library.get_func_asg',
1727 '( p_rpad_spaces - ' || p_rpad_spaces ||
1728 ')(p_prefix_left_asg - ' || p_prefix_left_asg ||
1729 ')( p_prefix_right_asg - ' || p_prefix_right_asg ||
1730 ')(p_omit_business_group_id - ' || p_omit_business_group_id ||
1731 ')(p_comma_on_first_line - ' || p_comma_on_first_line ||
1732 ')(p_equality_sign - ' || p_equality_sign ||
1733 ')(p_pad_first_line - ' || p_pad_first_line ||
1734 ')(p_left_asg_pad_len - ' || p_left_asg_pad_len ||
1735 ')(p_right_asg_pad_len - ' || p_right_asg_pad_len ||
1736 ')(p_start_terminator - ' || p_start_terminator ||
1737 ')(p_end_terminator - ' || p_end_terminator||
1738 ')(p_columns_tbl - varchar2 record type contains ' ||
1739 'list of columns)','R');
1740 raise;
1741 end get_func_asg;
1742
1743 -- ------------------------- get_func_asg ---------------------------
1744 -- Description:
1745 -- It reads the list columns and returns the parameters list required for
1746 -- inserting the data into data pump batch_lines table or any other TUPS
1747 -- function
1748 -- e.g p_col1 => p_col2,
1749 -- p_col2 => p_col2...
1750 -- Input Parameters :
1751 -- p_rpad_spaces - Number of blank spaces added before writing the
1752 -- element on new line.
1753 -- p_columns_tbl - List of the columns or elements which is required
1754 -- to be changed into text of parameter assignment.
1755 -- It is of pl/sql type t_varchar2_tbl,
1756 -- p_prefix_left_asg - Prefix the left element with this value. e.g if
1757 -- value is 'p_' then all elements on the left hand
1758 -- side of the assignment will be prefixed with 'p_'
1759 -- prefixed with p_ and our list output will be
1760 -- p_col1 => col1,
1761 -- p_col2 => col2,
1762 -- p_col3 => col3
1763 -- p_prefix_right_asg - Prefix the right element with this value. e.g if
1764 -- value is 'p_' then all elements on the right hand
1765 -- side of the assignment will be prefixed with 'p_'
1766 -- prefixed with p_ and our list output will be
1767 -- col1 => p_col1,
1768 -- col2 => p_col2,
1769 -- col3 => p_col3
1770 -- p_omit_business_group_id - It is whether to exclude the
1771 -- business_group_id assignment from the list.
1772 -- 'Y' - does not include business_group_id column
1773 -- for parameter assignment. (default value)
1774 -- 'N' - includes business_group_id column for
1775 -- parameter assignment.
1776 -- p_comma_on_first_line - Put the comma in the first element or not.
1777 -- 'Y' - puts the comma before the first element
1778 -- parameter assignment.
1779 -- 'N' - does not put comma before the first element
1780 -- parameter assignment.
1781 -- p_equality_sign - By default the equality sign of the parameter
1782 -- assignment is ' => '. But it can be '=' for
1783 -- update statement set column assignment.
1784 -- p_pad_first_line - It means whether the spaces should be added to
1785 -- the first element or not.
1786 -- 'Y' - spaces are added to the first element
1787 -- 'N' - spaces are not added to the first element
1788 -- p_left_asg_pad_len - It means the length of the left hand parameter
1789 -- after prefix. e.g p_prefix_left is 'p_' and
1790 -- column name is 'responsibility_application_id',
1791 -- if the length is 30 the left hand parameter will
1792 -- be 'p_responsibility_application_i'.
1793 -- p_right_asg_pad_len same as above but applied to right hand side
1794 -- parameter.
1795 -- p_start_terminator This is put at the begning of the assignment from
1796 -- the second element onwards. By default it is ','
1797 -- but in some cases it can be 'and' especially use
1798 -- by TUPS for generating where clause for composite
1799 -- primary keys.
1800 -- p_end_terminator This is put at the end of the assignment. It is
1801 -- null most of the time and is used by Date Track
1802 -- TUPS with composite key to terminate the
1803 -- assignment with ';'.
1804 -- p_resolve_pk_columns_tbl The column in this pl/sql table should have
1805 -- 'l_' as prefix in the right hand side assignment.
1806 -- Thay are lookup columns whose value is derived
1807 -- from the destination database.
1808 -- p_test_with_nvl This is a flag used with the creation of the
1809 -- chk_row_exists cursor in the TUPS and forces
1810 -- the comparison to use NVL.
1811 -- p_table_name Name of the table
1812 -- Returns
1813 -- It returns a string by putting each element of the table into a newline.
1814 -- and sepearting the element assignment by terminator.
1815 -- ------------------------------------------------------------------------
1816 function get_func_asg
1817 (
1818 p_rpad_spaces in number,
1819 p_columns_tbl in t_varchar2_tbl,
1820 p_prefix_left_asg in varchar2 default 'p_',
1821 p_prefix_right_asg in varchar2 default 'p_',
1822 p_omit_business_group_id in varchar2 default 'Y',
1823 p_comma_on_first_line in varchar2 default 'Y',
1824 p_equality_sign in varchar2 default ' => ',
1825 p_pad_first_line in varchar2 default 'Y' ,
1826 p_left_asg_pad_len in number default 30,
1827 p_right_asg_pad_len in number default 30,
1828 p_start_terminator in varchar2 default ',' ,
1829 p_end_terminator in varchar2 default null,
1830 p_resolve_pk_columns_tbl in hr_dm_gen_main.t_fk_to_aol_columns_tbl,
1831 p_test_with_nvl in varchar2 default 'N',
1832 p_table_name in varchar2 default null
1833 )
1834 return varchar2 is
1835
1836 l_out_text varchar2(32767) := null;
1837 l_list_index number;
1838 l_index number;
1839 l_nvl_left1 varchar2(100);
1840 l_nvl_left2 varchar2(100);
1841 l_nvl_right varchar2(100);
1842 l_nvl_indent varchar2(30);
1843
1844 begin
1845 hr_dm_utility.message('ROUT','entry:hr_dm_library.get_func_asg', 5);
1846 hr_dm_utility.message('PARA','( p_rpad_spaces - ' || p_rpad_spaces ||
1847 ')(p_prefix_left_asg - ' || p_prefix_left_asg ||
1848 ')( p_prefix_right_asg - ' || p_prefix_right_asg ||
1849 ')(p_omit_business_group_id - ' || p_omit_business_group_id ||
1850 ')(p_comma_on_first_line - ' || p_comma_on_first_line ||
1851 ')(p_equality_sign - ' || p_equality_sign ||
1852 ')(p_pad_first_line - ' || p_pad_first_line ||
1853 ')(p_left_asg_pad_len - ' || p_left_asg_pad_len ||
1854 ')(p_right_asg_pad_len - ' || p_right_asg_pad_len ||
1855 ')(p_start_terminator - ' || p_start_terminator ||
1856 ')(p_end_terminator - ' || p_end_terminator||
1857 ')(p_columns_tbl - varchar2 record type contains ' ||
1858 'list of columns' ||
1859 ')(p_test_with_nvl - ' || p_test_with_nvl || ')', 10);
1860
1861 -- initialise the variables
1862 l_list_index := p_columns_tbl.first;
1863
1864 -- if first line has to be padded then add spaces
1865 if p_pad_first_line = 'Y' then
1866 l_out_text := rpad(' ', p_rpad_spaces);
1867 end if;
1868
1869 -- if comma should not be put in the first element
1870 if p_comma_on_first_line = 'Y' then
1871 l_out_text := l_out_text || ',';
1872 else
1873 l_out_text := l_out_text || ' ';
1874 end if;
1875
1876 --
1877 -- if the first column is business group and omit business group flag is
1878 -- 'Y' then we do not want to include business group column hence get the
1879 -- next item.
1880 --
1881 if (upper(p_columns_tbl(l_list_index)) = 'BUSINESS_GROUP_ID' and
1882 p_omit_business_group_id = 'Y')
1883 then
1884 l_list_index := p_columns_tbl.next(l_list_index);
1885 end if;
1886
1887 -- check whether this column has a lookup id columns
1888 check_col_for_fk_on_aol(p_resolve_pk_columns_tbl,
1889 p_columns_tbl(l_list_index),
1890 l_index);
1891
1892
1893 -- do we want to add NVL handling?
1894 l_nvl_left1 := '';
1895 if (p_test_with_nvl = 'N') then
1896 l_nvl_indent := '';
1897 else
1898 l_nvl_indent := indent(12);
1899 end if;
1900
1901 get_nvl_arguement(p_test_with_nvl,
1902 p_table_name,
1903 p_columns_tbl(l_list_index),
1904 l_nvl_left2,
1905 l_nvl_right);
1906
1907 if l_index is not null then
1908 l_out_text := l_out_text ||
1909 rpad(l_nvl_left1 || l_nvl_left2 || p_prefix_left_asg ||
1910 p_columns_tbl(l_list_index) || l_nvl_right,p_left_asg_pad_len)
1911 || l_nvl_indent || p_equality_sign || rtrim(rpad(l_nvl_left1 ||
1912 l_nvl_left2 || 'l_' ||
1913 p_columns_tbl(l_list_index) || l_nvl_right,p_right_asg_pad_len)) ||
1914 p_end_terminator;
1915 else
1916 l_out_text := l_out_text ||
1917 rpad(l_nvl_left1 || l_nvl_left2 || p_prefix_left_asg ||
1918 p_columns_tbl(l_list_index) || l_nvl_right,p_left_asg_pad_len)
1919 || l_nvl_indent || p_equality_sign || rtrim(rpad(l_nvl_left1 ||
1920 l_nvl_left2 || p_prefix_right_asg ||
1921 p_columns_tbl(l_list_index) || l_nvl_right,p_right_asg_pad_len)) ||
1922 p_end_terminator;
1923 end if;
1924
1925
1926 l_list_index := p_columns_tbl.next(l_list_index);
1927
1928
1929 --
1930 -- read all the elements of pl/sql table and append them into text.
1931 --
1932 while l_list_index is not null loop
1933 --
1934 -- seperate the elements by comma and move it to next line.Do not assign
1935 -- Business_Group_Id column as data pump knows the value of
1936 -- business_group_id if the parameter p_omit_business_group_id value is 'Y'
1937 --
1938 get_nvl_arguement(p_test_with_nvl,
1939 p_table_name,
1940 p_columns_tbl(l_list_index),
1941 l_nvl_left2,
1942 l_nvl_right);
1943
1944 if (upper(p_columns_tbl(l_list_index)) <> 'BUSINESS_GROUP_ID' or
1945 p_omit_business_group_id = 'N')
1946 then
1947 -- check whether this column has a lookup id columns
1948 check_col_for_fk_on_aol(p_resolve_pk_columns_tbl,
1949 p_columns_tbl(l_list_index),
1950 l_index);
1951
1952 if l_index is not null then
1953 l_out_text := l_out_text ||c_newline || rpad(' ', p_rpad_spaces) ||
1954 p_start_terminator ||
1955 rpad(l_nvl_left1 || l_nvl_left2 || p_prefix_left_asg ||
1956 p_columns_tbl(l_list_index) || l_nvl_right,
1957 p_left_asg_pad_len) || l_nvl_indent || p_equality_sign ||
1958 rtrim(l_nvl_left1 || l_nvl_left2 || rpad('l_' ||
1959 p_columns_tbl(l_list_index),p_right_asg_pad_len)) ||
1960 p_end_terminator;
1961 else
1962 l_out_text := l_out_text ||c_newline || rpad(' ', p_rpad_spaces) ||
1963 p_start_terminator ||
1964 rpad(l_nvl_left1 || l_nvl_left2 || p_prefix_left_asg ||
1965 p_columns_tbl(l_list_index) || l_nvl_right,
1966 p_left_asg_pad_len) || l_nvl_indent || p_equality_sign ||
1967 rtrim(l_nvl_left1 || l_nvl_left2 || rpad(p_prefix_right_asg ||
1968 p_columns_tbl(l_list_index),p_right_asg_pad_len)) ||
1969 p_end_terminator;
1970 end if;
1971
1972 end if;
1973 l_list_index := p_columns_tbl.next(l_list_index);
1974 end loop;
1975
1976 l_out_text := rtrim(l_out_text);
1977 hr_dm_utility.message('INFO','HR_DM_LIBARARY - convert list elements ' ||
1978 'into formatted text for call to procedure and ' ||
1979 ' named convention used for parameters ',15);
1980 --hr_dm_utility.message('SUMM','HR_DM_LIBARARY - - convert list elements ' ||
1981 -- 'into formatted text for call to procedure and ' ||
1982 -- ' named convention used for parameters ',20);
1983
1984 hr_dm_utility.message('PARA','(l_out_text - formatted text for funtion ' ||
1985 'assignment )' ,30);
1986 hr_dm_utility.message('ROUT','exit:hr_dm_library.get_func_asg',
1987 25);
1988 return l_out_text;
1989 exception
1990 when others then
1991 hr_dm_utility.error(SQLCODE,'hr_dm_library.get_func_asg',
1992 '( p_rpad_spaces - ' || p_rpad_spaces ||
1993 ')(p_prefix_left_asg - ' || p_prefix_left_asg ||
1994 ')( p_prefix_right_asg - ' || p_prefix_right_asg ||
1995 ')(p_omit_business_group_id - ' || p_omit_business_group_id ||
1996 ')(p_comma_on_first_line - ' || p_comma_on_first_line ||
1997 ')(p_equality_sign - ' || p_equality_sign ||
1998 ')(p_pad_first_line - ' || p_pad_first_line ||
1999 ')(p_left_asg_pad_len - ' || p_left_asg_pad_len ||
2000 ')(p_right_asg_pad_len - ' || p_right_asg_pad_len ||
2001 ')(p_start_terminator - ' || p_start_terminator ||
2002 ')(p_end_terminator - ' || p_end_terminator||
2003 ')(p_columns_tbl - varchar2 record type contains ' ||
2004 'list of columns)','R');
2005 raise;
2006 end get_func_asg;
2007
2008 -- ------------------------- get_func_asg_with_dev_key ---------------------------
2009 -- Description:
2010 -- This function is same as the get_func_asg but it replaces the column which
2011 -- have foreign key to AOL table with the corresponding developer key column
2012 -- of the AOL table.
2013 -- It reads the list columns and replaces appropriate column with the developer
2014 -- key column of the AOL table and returns the parameters list required for
2015 -- inserting the data into data pump batch_lines table or any other TUPS
2016 -- function
2017 -- e.g a table has col1,col2 and col1 has a foreign key on aol table and
2018 -- corresponding developer key is col1_dev_key then the output returned is :
2019 -- p_col1_dev_key => l_col1_dev_key,
2020 -- p_col2 => p_col2...
2021 -- Input Parameters :
2022 -- p_rpad_spaces - Number of blank spaces added before writing the
2023 -- element on new line.
2024 -- p_columns_tbl - List of the columns or elements which is required
2025 -- to be changed into text of parameter assignment.
2026 -- It is of pl/sql type t_varchar2_tbl,
2027 -- p_prefix_left_asg - Prefix the left element with this value. e.g if
2028 -- value is 'p_' then all elements on the left hand
2029 -- side of the assignment will be prefixed with 'p_'
2030 -- prefixed with p_ and our list output will be
2031 -- p_col1 => col1,
2032 -- p_col2 => col2,
2033 -- p_col3 => col3
2034 -- p_prefix_right_asg - Prefix the right element with this value. e.g if
2035 -- value is 'p_' then all elements on the right hand
2036 -- side of the assignment will be prefixed with 'p_'
2037 -- prefixed with p_ and our list output will be
2038 -- col1 => p_col1,
2039 -- col2 => p_col2,
2040 -- col3 => p_col3
2041 -- p_prefix_left_asg_dev_key - same as p_prefix_left_asg defined above but is
2042 -- applied only to developer key.
2043 -- p_prefix_right_asg_dev_key - same as p_prefix_right_asg defined above but
2044 -- is applied only to developer key.
2045 -- p_omit_business_group_id - It is whether to exclude the business_group_id
2046 -- assignment from the list.
2047 -- 'Y' - does not include business_group_id column for
2048 -- parameter assignment. (default value)
2049 -- 'N' - includes business_group_id column for
2050 -- parameter assignment.
2051 -- p_comma_on_first_line - Put the comma in the first element or not.
2052 --'Y' - puts the comma before the first element
2053 -- parameter assignment.
2054 --'N' - does not put comma before the first element
2055 -- parameter assignment.
2056 -- p_equality_sign - By default the equality sign of the parameter
2057 -- assignment is ' => '. But it can be '=' for
2058 -- update statement set column assignment.
2059 -- p_pad_first_line - It means whether the spaces should be added to
2060 -- the first element or not.
2061 -- 'Y' - spaces are added to the first element
2062 -- 'N' - spaces are not added to the first element
2063 -- p_left_asg_pad_len - It means the length of the left hand parameter
2064 -- after prefix. e.g p_prefix_left is 'p_' and column
2065 -- name is 'responsibility_application_id', if the
2066 -- length is 30 the the left hand parameter will be
2067 -- 'p_responsibility_application_i'.
2068 -- p_right_asg_pad_len same as above but applied to right hand side
2069 -- parameter.
2070 -- p_use_aol_id_col This function is used by TUPS as well as TDS.
2071 -- TDS uses the developer key for assignment while
2072 -- TUPS uses id value. It can have following values
2073 -- 'N' - use id column for assignment
2074 -- 'Y' - use deveoper key column for assignment
2075 -- p_resolve_pk_columns_tbl The column in this pl/sql table should have
2076 -- 'l_' as prefix in the right hand side assignment.
2077 -- Thay are lookup columns whose value is derived
2078 -- from the destination database.
2079 --
2080 -- Returns
2081 -- It returns a string by putting each element of the table into a newline.
2082 --
2083 -- ------------------------------------------------------------------------
2084 function get_func_asg_with_dev_key
2085 (
2086 p_rpad_spaces in number,
2087 p_columns_tbl in t_varchar2_tbl,
2088 p_prefix_left_asg in varchar2 default 'p_',
2089 p_prefix_right_asg in varchar2 default 'p_',
2090 p_prefix_left_asg_dev_key in varchar2 default 'p_',
2091 p_prefix_right_asg_dev_key in varchar2 default 'l_',
2092 p_omit_business_group_id in varchar2 default 'Y',
2093 p_comma_on_first_line in varchar2 default 'Y',
2094 p_equality_sign in varchar2 default ' => ',
2095 p_pad_first_line in varchar2 default 'Y' ,
2096 p_left_asg_pad_len in number default 30,
2097 p_right_asg_pad_len in number default 30,
2098 p_use_aol_id_col in varchar2,
2099 p_fk_to_aol_columns_tbl in hr_dm_gen_main.t_fk_to_aol_columns_tbl,
2100 p_resolve_pk_columns_tbl in hr_dm_gen_main.t_fk_to_aol_columns_tbl
2101 )
2102 return varchar2 is
2103
2104 l_out_text varchar2(20000) := null;
2105 l_list_index number;
2106 l_left_parameter varchar2(100);
2107 l_right_parameter varchar2(100);
2108 l_index number;
2109 begin
2110 hr_dm_utility.message('ROUT','entry:hr_dm_library.get_func_asg_with_dev_key', 5);
2111 hr_dm_utility.message('PARA','( p_rpad_spaces - ' || p_rpad_spaces ||
2112 ')(p_prefix_left_asg - ' || p_prefix_left_asg ||
2113 ')( p_prefix_right_asg - ' || p_prefix_right_asg ||
2114 ')(p_prefix_left_asg_dev_key - ' || p_prefix_left_asg_dev_key ||
2115 ')( p_prefix_right_asg_dev_key - ' || p_prefix_right_asg_dev_key ||
2116 ')(p_omit_business_group_id - ' || p_omit_business_group_id ||
2117 ')(p_comma_on_first_line - ' || p_comma_on_first_line ||
2118 ')(p_equality_sign - ' || p_equality_sign ||
2119 ')(p_pad_first_line - ' || p_pad_first_line ||
2120 ')(p_left_asg_pad_len - ' || p_left_asg_pad_len ||
2121 ')(p_right_asg_pad_len - ' || p_right_asg_pad_len ||
2122 ')(p_use_aol_id_col - ' || p_use_aol_id_col ||
2123 ')(p_fk_to_aol_columns_tbl - varchar2 record type contains ' ||
2124 'list of columns who have foreign key on AOL table.)' ||
2125 ')(p_columns_tbl - varchar2 record type contains ' ||
2126 'list of columns)', 10);
2127 -- initialise the variables
2128 l_list_index := p_columns_tbl.first;
2129
2130 -- if first line has to be padded then add spaces
2131 if p_pad_first_line = 'Y' then
2132 --l_out_text := rpad(' ', p_rpad_spaces + 1);
2133 l_out_text := rpad(' ', p_rpad_spaces);
2134 end if;
2135
2136 -- if comma should not be put in the first element
2137 if p_comma_on_first_line = 'Y' then
2138 l_out_text := l_out_text || ',';
2139 else
2140 l_out_text := l_out_text || ' ';
2141 end if;
2142
2143 --
2144 -- if the first column is business group and omit business group flag is
2145 -- 'Y' then we do not want to include business group column hence get the
2146 -- next item.
2147 --
2148 if (upper(p_columns_tbl(l_list_index)) = 'BUSINESS_GROUP_ID' and
2149 p_omit_business_group_id = 'Y')
2150 then
2151 l_list_index := p_columns_tbl.next(l_list_index);
2152 end if;
2153
2154 -- check whether this column has a foreign key on aol table
2155 check_col_for_fk_on_aol(p_fk_to_aol_columns_tbl,
2156 p_columns_tbl(l_list_index),
2157 l_index);
2158 --
2159 -- if l_index is null then it means that the column does not have foreign
2160 -- key on the aol table,other wise it passes the index of pl/sql table
2161 -- from which the information about aol table and developer key.
2162 --
2163 if l_index is not null then
2164 if p_use_aol_id_col = 'N' then
2165 l_left_parameter := rpad(p_prefix_left_asg_dev_key ||
2166 p_fk_to_aol_columns_tbl(l_index).parent_column_name,p_left_asg_pad_len);
2167 l_right_parameter := rpad(p_prefix_right_asg_dev_key ||
2168 p_fk_to_aol_columns_tbl(l_index).parent_column_name,p_right_asg_pad_len);
2169 else
2170 l_left_parameter := rpad(p_prefix_left_asg_dev_key ||
2171 p_fk_to_aol_columns_tbl(l_index).column_name,p_left_asg_pad_len);
2172 l_right_parameter := rpad(p_prefix_right_asg_dev_key ||
2173 p_fk_to_aol_columns_tbl(l_index).column_name,p_right_asg_pad_len);
2174 end if;
2175 else
2176 -- check whether this column has a lookup id columns
2177 check_col_for_fk_on_aol(p_resolve_pk_columns_tbl,
2178 p_columns_tbl(l_list_index),
2179 l_index);
2180
2181 if l_index is not null then
2182 l_left_parameter := rpad('p_' ||
2183 p_resolve_pk_columns_tbl(l_index).column_name,p_left_asg_pad_len);
2184 l_right_parameter := rpad('l_' ||
2185 p_resolve_pk_columns_tbl(l_index).column_name,p_right_asg_pad_len);
2186 else
2187 l_left_parameter := rpad(p_prefix_left_asg ||
2188 p_columns_tbl(l_list_index),p_left_asg_pad_len);
2189 l_right_parameter := rpad(p_prefix_right_asg ||
2190 p_columns_tbl(l_list_index),p_right_asg_pad_len);
2191 end if;
2192 end if;
2193
2194 -- if comma should not be put in the first element then add the first element
2195 -- here only.
2196
2197 l_out_text := l_out_text || l_left_parameter || p_equality_sign ||
2198 l_right_parameter;
2199
2200 l_list_index := p_columns_tbl.next(l_list_index);
2201
2202 --
2203 -- read all the elements of pl/sql table and append them into text.
2204 --
2205 while l_list_index is not null loop
2206 --
2207 -- seperate the elements by comma and move it to next line.Do not assign
2208 -- Business_Group_Id column as data pump knows the value of
2209 -- business_group_id if the parameter p_omit_business_group_id value is 'Y'
2210 --
2211 if (upper(p_columns_tbl(l_list_index)) <> 'BUSINESS_GROUP_ID' or
2212 p_omit_business_group_id = 'N')
2213 then
2214
2215 -- check whether this column has a foreign key on aol table
2216 check_col_for_fk_on_aol(p_fk_to_aol_columns_tbl,
2217 p_columns_tbl(l_list_index),
2218 l_index);
2219 --
2220 -- if l_index is null then it means that the column does not have foreign
2221 -- key on the aol table,other wise it passes the index of pl/sql table
2222 -- from which the information about aol table and developer key.
2223 --
2224 if l_index is not null then
2225 if p_use_aol_id_col = 'N' then
2226 l_left_parameter := rpad(p_prefix_left_asg_dev_key ||
2227 p_fk_to_aol_columns_tbl(l_index).parent_column_name,p_left_asg_pad_len);
2228 l_right_parameter := rpad(p_prefix_right_asg_dev_key ||
2229 p_fk_to_aol_columns_tbl(l_index).parent_column_name,p_right_asg_pad_len);
2230 else
2231 l_left_parameter := rpad(p_prefix_left_asg_dev_key ||
2232 p_fk_to_aol_columns_tbl(l_index).column_name,
2233 p_left_asg_pad_len);
2234 l_right_parameter := rpad(p_prefix_right_asg_dev_key ||
2235 p_fk_to_aol_columns_tbl(l_index).column_name,
2236 p_right_asg_pad_len);
2237 end if;
2238 else
2239 -- check whether this column has a lookup id columns
2240 check_col_for_fk_on_aol(p_resolve_pk_columns_tbl,
2241 p_columns_tbl(l_list_index),
2242 l_index);
2243
2244 if l_index is not null then
2245 l_left_parameter := rpad('p_' ||
2246 p_resolve_pk_columns_tbl(l_index).column_name,p_left_asg_pad_len);
2247 l_right_parameter := rpad('l_' ||
2248 p_resolve_pk_columns_tbl(l_index).column_name,p_right_asg_pad_len);
2249 else
2250 l_left_parameter := rpad(p_prefix_left_asg ||
2251 p_columns_tbl(l_list_index),p_left_asg_pad_len);
2252 l_right_parameter := rpad(p_prefix_right_asg ||
2253 p_columns_tbl(l_list_index),p_right_asg_pad_len);
2254 end if;
2255 end if;
2256
2257
2258 l_out_text := l_out_text || c_newline || rpad(' ', p_rpad_spaces) || ',' ||
2259 l_left_parameter || p_equality_sign ||
2260 l_right_parameter;
2261 end if;
2262 l_list_index := p_columns_tbl.next(l_list_index);
2263 end loop;
2264 l_out_text := rtrim(l_out_text);
2265
2266 hr_dm_utility.message('INFO','HR_DM_LIBARARY - convert list columns with ' ||
2267 'fk to aol table into formatted text for call to ' ||
2268 'procedure and named convention used for parameters ',15);
2269
2270 hr_dm_utility.message('PARA','(l_out_text - out nocopy formatted text for function' ||
2271 'assignment )' ,30);
2272 hr_dm_utility.message('ROUT','exit:hr_dm_library.get_func_asg_with_dev_key',
2273 25);
2274 return l_out_text;
2275 exception
2276 when others then
2277 hr_dm_utility.error(SQLCODE,'hr_dm_library.get_func_asg_with_dev_key',
2278 '( p_rpad_spaces - ' || p_rpad_spaces ||
2279 ')(p_prefix_left_asg - ' || p_prefix_left_asg ||
2280 ')( p_prefix_right_asg - ' || p_prefix_right_asg ||
2281 ')(p_prefix_left_asg_dev_key - ' || p_prefix_left_asg_dev_key ||
2282 ')( p_prefix_right_asg_dev_key - ' || p_prefix_right_asg_dev_key ||
2283 ')(p_omit_business_group_id - ' || p_omit_business_group_id ||
2284 ')(p_comma_on_first_line - ' || p_comma_on_first_line ||
2285 ')(p_equality_sign - ' || p_equality_sign ||
2286 ')(p_pad_first_line - ' || p_pad_first_line ||
2287 ')(p_left_asg_pad_len - ' || p_left_asg_pad_len ||
2288 ')(p_right_asg_pad_len - ' || p_right_asg_pad_len ||
2289 ')(p_use_aol_id_col - ' || p_use_aol_id_col ||
2290 ')(p_fk_to_aol_columns_tbl - varchar2 record type contains ' ||
2291 'list of columns who have foreign key on AOL table.)' ||
2292 ')(p_columns_tbl - varchar2 record type contains ' ||
2293 'list of columns)','R');
2294 raise;
2295 end get_func_asg_with_dev_key;
2296
2297 -- ------------------------ ins_resolve_pks ---------------------------------
2298 -- Description:
2299 -- Insert a row into hr_dm_resolve_pks table. It will be used by TUPS.
2300 -- Input Parameters
2301 -- p_table_name - Table name
2302 -- p_source_id - Value of the first primary key column
2303 -- p_destination_id - Value of the second primary key column
2304 -- ------------------------------------------------------------------------
2305 procedure ins_resolve_pks
2306 ( p_table_name in varchar2,
2307 p_source_id in number,
2308 p_destination_id in number
2309 ) is
2310 e_fatal_error EXCEPTION;
2311 l_fatal_error_message VARCHAR2(200);
2312 begin
2313 hr_dm_utility.message('ROUT','entry:hr_dm_library.ins_resolve_pks ', 5);
2314 hr_dm_utility.message('PARA','(p_table_name - ' || p_table_name ||
2315 ')(p_source_id - ' || p_source_id ||
2316 ')(p_destination_id - ' || p_destination_id ||
2317 ')', 10);
2318
2319 if (p_table_name is null) then
2320 l_fatal_error_message := 'Null table name passed to ins_resolve_pks procedure';
2321 raise e_fatal_error;
2322 end if;
2323
2324 --hr_data_pump.message('source db is - ' ||
2325 -- NVL(hr_dm_upload.g_data_migrator_source_db, '<null>'));
2326
2327
2328
2329 if p_source_id <> p_destination_id then
2330
2331 -- insert row if no matching row is already present
2332
2333 insert into hr_dm_resolve_pks
2334 ( resolve_pk_id,
2335 source_database_instance,
2336 table_name,
2337 source_id,
2338 destination_id
2339 )
2340 select
2341 hr_dm_resolve_pks_s.nextval,
2342 hr_dm_upload.g_data_migrator_source_db,
2343 p_table_name,
2344 p_source_id,
2345 p_destination_id
2346 from dual
2347 where not exists (select null
2348 from hr_dm_resolve_pks
2349 where source_database_instance =
2350 hr_dm_upload.g_data_migrator_source_db
2351 and table_name = p_table_name
2352 and source_id = p_source_id);
2353
2354 -- see if a row has been inserted
2355 -- if not, then a row already exists, so we must update it
2356 if sql%rowcount = 0 then
2357 update hr_dm_resolve_pks
2358 set destination_id = p_destination_id
2359 where source_database_instance =
2360 hr_dm_upload.g_data_migrator_source_db
2361 and table_name = p_table_name
2362 and source_id = p_source_id;
2363 end if;
2364
2365 end if;
2366
2367 hr_dm_utility.message('INFO','HR_DM_LIBARARY - insert row into ' ||
2368 'hr_dm_resolve_pks table ',15);
2369 hr_dm_utility.message('ROUT','exit:hr_dm_library.ins_resolve_pks',
2370 25);
2371 exception
2372 when e_fatal_error then
2373 hr_dm_utility.error(SQLCODE,'hr_dm_library.ins_resolve_pks',
2374 l_fatal_error_message,'R');
2375 raise;
2376
2377 when others then
2378 hr_dm_utility.error(SQLCODE,'hr_dm_library.ins_resolve_pks',
2379 '(none)','R');
2380 raise;
2381 end ins_resolve_pks;
2382
2383 -- ------------------------ get_resolved_pk ------------------------------
2384 -- Description: This function is used by TUPS.
2385 -- Checks whether a row exists for a given source id of the table.
2386 -- Input Parameters
2387 -- p_source_id - Value of the surrogate primary key of the table in
2388 -- source database
2389 -- p_table_name - Table name
2390 -- Out Parameters
2391 -- p_destination_id - Value of the surrogate primary key of the table in
2392 -- destination database if different from source database
2393 -- ,otherwise it returns the same id value as source.
2394 --
2395 -- ------------------------------------------------------------------------
2396 procedure get_resolved_pk
2397 ( p_table_name in varchar2,
2398 p_source_id in number,
2399 p_destination_id out nocopy number
2400 ) is
2401
2402 l_data_type hr_dm_dt_deletes.data_type%type;
2403 --
2404 -- cursor to find the row in hr_dm_dt_deletes table for a given id, table and
2405 -- type combination
2406
2407 cursor csr_find_destination_pk is
2408 select destination_id
2409 from hr_dm_resolve_pks
2410 where table_name = upper(p_table_name)
2411 and source_id = p_source_id
2412 and source_database_instance = hr_dm_upload.g_data_migrator_source_db;
2413 begin
2414 hr_dm_utility.message('ROUT','entry:hr_dm_library.get_resolved_pk', 5);
2415 hr_dm_utility.message('PARA','(p_source_id - ' || p_source_id ||
2416 ')(p_table_name - ' || p_table_name ||
2417 ')', 10);
2418
2419 open csr_find_destination_pk;
2420 fetch csr_find_destination_pk into p_destination_id ;
2421 if csr_find_destination_pk%notfound then
2422 p_destination_id := p_source_id;
2423 end if;
2424 close csr_find_destination_pk;
2425 hr_dm_utility.message('INFO','HR_DM_LIBARARY - check whether row exists ' ||
2426 'in get_resolved_pk for a given id of a table ',15);
2427
2428 hr_dm_utility.message('PARA','(p_destination_id - ' || p_destination_id
2429 || ')' ,30);
2430 hr_dm_utility.message('ROUT','exit:hr_dm_library.get_resolved_pk',
2431 25);
2432 exception
2433 when others then
2434 hr_dm_utility.error(SQLCODE,'hr_dm_library.get_resolved_pk',
2435 '(none)','R');
2436 raise;
2437 end get_resolved_pk;
2438 -- ------------------------ ins_dt_delete ---------------------------------
2439 -- Description:
2440 -- Insert a row into hr_dm_deletes table. It will be used by TUPS. If the
2441 -- already exists for date tracked row on uploading it will store the
2442 -- surrogate_id value.
2443 -- Input Parameters
2444 -- p_id - Value of the surrogate primary key of the table.
2445 -- p_table_name - Table name
2446 -- p_ins_type - idetifies the type of operation -
2447 -- 'D' - for date track. created by the first physical record
2448 -- uploaded, so as other physical records belonging to
2449 -- the same logical record can avoid the checks.
2450 -- 'P' - row already exists.
2451 -- p_pk_column_1 - Value of the first primary key column
2452 -- p_pk_column_2 - Value of the second primary key column
2453 -- p_pk_column_3 - Value of the third primary key column
2454 -- p_pk_column_3 - Value of the fourth primary key column
2455 -- ------------------------------------------------------------------------
2456 procedure ins_dt_delete
2457 ( p_id in number default null,
2458 p_table_name in varchar2,
2459 p_ins_type in varchar2 ,
2460 p_pk_column_1 in varchar2 default null,
2461 p_pk_column_2 in varchar2 default null,
2462 p_pk_column_3 in varchar2 default null,
2463 p_pk_column_4 in varchar2 default null
2464 ) is
2465 begin
2466 hr_dm_utility.message('ROUT','entry:hr_dm_library.ins_dt_delete ', 5);
2467 hr_dm_utility.message('PARA','(p_id - ' || p_id ||
2468 ')(p_table_name - ' || p_table_name ||
2469 ')(p_ins_type - ' || p_ins_type ||
2470 ')(p_pk_column_1 - ' || p_pk_column_1 ||
2471 ')(p_pk_column_2 - ' || p_pk_column_2 ||
2472 ')(p_pk_column_3 - ' || p_pk_column_3 ||
2473 ')(p_pk_column_4- ' || p_pk_column_4 ||
2474 ')', 10);
2475
2476 insert into hr_dm_dt_deletes ( dt_delete_id
2477 ,table_name
2478 ,data_type
2479 ,id_value
2480 ,pk_column_1
2481 ,pk_column_2
2482 ,pk_column_3
2483 ,pk_column_4
2484 )
2485 values ( hr_dm_dt_deletes_s.nextval
2486 ,p_table_name
2487 ,p_ins_type
2488 ,p_id
2489 ,p_pk_column_1
2490 ,p_pk_column_2
2491 ,p_pk_column_3
2492 ,p_pk_column_4);
2493
2494 hr_dm_utility.message('INFO','HR_DM_LIBARARY - insert row into ' ||
2495 'hr_dm_dt_deletes table ',15);
2496 --hr_dm_utility.message('SUMM','HR_DM_LIBARARY - - insert row into ' ||
2497 -- 'hr_dm_dt_deletes table ',20);
2498 hr_dm_utility.message('ROUT','exit:hr_dm_library.ins_dt_delete',
2499 25);
2500 exception
2501 when others then
2502 hr_dm_utility.error(SQLCODE,'hr_dm_library.ins_dt_delete',
2503 '(none)','R');
2504 raise;
2505 end ins_dt_delete;
2506
2507 -- ------------------------ chk_row_in_dt_delete ------------------------------
2508 -- Description: This function is used by Date Track TUPS
2509 -- Checks whether a row exists for a given id of the table and type.
2510 -- Input Parameters
2511 -- p_id - Value of the surrogate primary key of the table.
2512 -- p_table_name - Table name
2513 -- Out Parameters
2514 -- p_ins_type - If a row exists for the table/Id combination then one of
2515 -- the following value is returned.
2516 -- 'D' - for date track. created by the first physical record
2517 -- uploaded, so as other physical records belonging to
2518 -- the same logical record can avoid the checks.
2519 -- 'P' - row already exists.
2520 -- p_row_exists - If a row exists for the table/Id combination then it will
2521 -- have 'Y' ,otherwise 'N' value.
2522 -- ------------------------------------------------------------------------
2523 procedure chk_row_in_dt_delete
2524 ( p_id in number,
2525 p_table_name in varchar2,
2526 p_ins_type out nocopy varchar2,
2527 p_row_exists out nocopy varchar2
2528 ) is
2529
2530 l_data_type hr_dm_dt_deletes.data_type%type;
2531 --
2532 -- cursor to find the row in hr_dm_dt_deletes table for a given id, table and
2533 -- type combination
2534 cursor csr_find_dt_deletes is
2535 select data_type
2536 from hr_dm_dt_deletes
2537 where id_value = p_id
2538 and table_name = p_table_name
2539 order by data_type;
2540 begin
2541 hr_dm_utility.message('ROUT','entry:hr_dm_library.chk_row_in_dt_delete', 5);
2542 hr_dm_utility.message('PARA','(p_id - ' || p_id ||
2543 ')(p_table_name - ' || p_table_name ||
2544 ')', 10);
2545
2546 open csr_find_dt_deletes;
2547 fetch csr_find_dt_deletes into l_data_type ;
2548 if csr_find_dt_deletes%found then
2549 p_row_exists := 'Y';
2550 p_ins_type := l_data_type;
2551 close csr_find_dt_deletes;
2552 else
2553 p_row_exists := 'N';
2554 p_ins_type := null;
2555 close csr_find_dt_deletes;
2556 end if;
2557 hr_dm_utility.message('INFO','HR_DM_LIBARARY - check whether row exists ' ||
2558 'in hr_dm_dt_deletes table ',15);
2559 --hr_dm_utility.message('SUMM','HR_DM_LIBARARY - check whether row exists ' ||
2560 -- 'in hr_dm_dt_deletes table ',20);
2561
2562 hr_dm_utility.message('PARA','(p_ins_type - ' || p_ins_type
2563 || ')( p_row_exists - ' ||p_row_exists ,30);
2564 hr_dm_utility.message('ROUT','exit:hr_dm_library.chk_row_in_dt_delete',
2565 25);
2566 exception
2567 when others then
2568 hr_dm_utility.error(SQLCODE,'hr_dm_library.chk_row_in_dt_delete',
2569 '(none)','R');
2570 raise;
2571 end chk_row_in_dt_delete;
2572
2573 -- ------------------------ chk_row_in_dt_delete_1_pkcol ------------------------------
2574 -- Description: This function is used by Date Track table with non surrogate id.
2575 -- The priomary key consists of one column
2576 -- Checks whether a row exists for a given primary key of the table and type.
2577 -- Input Parameters
2578 -- p_pk_column_1 - Value of primary key of the table.
2579 -- p_table_name - Table name
2580 -- Out Parameters
2581 -- p_ins_type - If a row exists for the table/Id combination then one of
2582 -- the following value is returned.
2583 -- 'D' - for date track. created by the first physical record
2584 -- uploaded, so as other physical records belonging to
2585 -- the same logical record can avoid the checks.
2586 -- 'P' - row already exists.
2587 -- p_row_exists - If a row exists for the table/Id combination then it will
2588 -- have 'Y' ,otherwise 'N' value.
2589 -- ------------------------------------------------------------------------
2590 procedure chk_row_in_dt_delete_1_pkcol
2591 ( p_pk_column_1 in number,
2592 p_table_name in varchar2,
2593 p_ins_type out nocopy varchar2,
2594 p_row_exists out nocopy varchar2
2595 ) is
2596
2597 l_data_type hr_dm_dt_deletes.data_type%type;
2598 --
2599 -- cursor to find the row in hr_dm_dt_deletes table for a given id, table and
2600 -- type combination
2601 cursor csr_find_dt_deletes is
2602 select data_type
2603 from hr_dm_dt_deletes
2604 where pk_column_1 = p_pk_column_1
2605 and table_name = p_table_name
2606 order by data_type;
2607 begin
2608 hr_dm_utility.message('ROUT','entry:hr_dm_library.chk_row_in_dt_delete_1_pkcol', 5);
2609 hr_dm_utility.message('PARA','(p_table_name - ' || p_table_name ||
2610 ')(p_pk_column_1 - ' || p_pk_column_1 ||
2611 ')', 10);
2612
2613 open csr_find_dt_deletes;
2614 fetch csr_find_dt_deletes into l_data_type ;
2615 if csr_find_dt_deletes%found then
2616 p_row_exists := 'Y';
2617 p_ins_type := l_data_type;
2618 close csr_find_dt_deletes;
2619 else
2620 p_row_exists := 'N';
2621 p_ins_type := null;
2622 close csr_find_dt_deletes;
2623 end if;
2624 hr_dm_utility.message('INFO','HR_DM_LIBARARY - check whether row exists ' ||
2625 'in hr_dm_dt_deletes table ',15);
2626 --hr_dm_utility.message('SUMM','HR_DM_LIBARARY - check whether row exists ' ||
2627 -- 'in hr_dm_dt_deletes table ',20);
2628
2629 hr_dm_utility.message('PARA','(p_ins_type - ' || p_ins_type
2630 || ')( p_row_exists - ' ||p_row_exists ,30);
2631 hr_dm_utility.message('ROUT','exit:hr_dm_library.chk_row_in_dt_delete_1_pkcol',
2632 25);
2633 exception
2634 when others then
2635 hr_dm_utility.error(SQLCODE,'hr_dm_library.chk_row_in_dt_delete_1_pkcol',
2636 '(none)','R');
2637 raise;
2638 end chk_row_in_dt_delete_1_pkcol;
2639 -- ------------------------ chk_row_in_dt_delete_2_pkcol ------------------------------
2640 -- Description: This function is used by Date Track table with non surrogate id.
2641 -- The primary key consists of two columns
2642 -- Checks whether a row exists for a given primary key columns of the table and type.
2643 -- Input Parameters
2644 -- p_pk_column_1 - Value of primary key column 1 of the table.
2645 -- p_pk_column_2 - Value of primary key column 2 of the table.
2646 -- p_table_name - Table name
2647 -- Out Parameters
2648 -- p_ins_type - If a row exists for the table/Id combination then one of
2649 -- the following value is returned.
2650 -- 'D' - for date track. created by the first physical record
2651 -- uploaded, so as other physical records belonging to
2652 -- the same logical record can avoid the checks.
2653 -- 'P' - row already exists.
2654 -- p_row_exists - If a row exists for the table/Id combination then it will
2655 -- have 'Y' ,otherwise 'N' value.
2656 -- ------------------------------------------------------------------------
2657 procedure chk_row_in_dt_delete_2_pkcol
2658 ( p_pk_column_1 in number,
2659 p_pk_column_2 in number,
2660 p_table_name in varchar2,
2661 p_ins_type out nocopy varchar2,
2662 p_row_exists out nocopy varchar2
2663 ) is
2664
2665 l_data_type hr_dm_dt_deletes.data_type%type;
2666 --
2667 -- cursor to find the row in hr_dm_dt_deletes table for a given id, table and
2668 -- type combination
2669 cursor csr_find_dt_deletes is
2670 select data_type
2671 from hr_dm_dt_deletes
2672 where pk_column_1 = p_pk_column_1
2673 and pk_column_2 = p_pk_column_2
2674 and table_name = p_table_name
2675 order by data_type;
2676 begin
2677 hr_dm_utility.message('ROUT','entry:hr_dm_library.chk_row_in_dt_delete_2_pkcol', 5);
2678 hr_dm_utility.message('PARA','(p_table_name - ' || p_table_name ||
2679 ')(p_pk_column_1 - ' || p_pk_column_1 ||
2680 ')(p_pk_column_2 - ' || p_pk_column_2 ||
2681 ')', 10);
2682 open csr_find_dt_deletes;
2683 fetch csr_find_dt_deletes into l_data_type ;
2684 if csr_find_dt_deletes%found then
2685 p_row_exists := 'Y';
2686 p_ins_type := l_data_type;
2687 close csr_find_dt_deletes;
2688 else
2689 p_row_exists := 'N';
2690 p_ins_type := null;
2691 close csr_find_dt_deletes;
2692 end if;
2693 hr_dm_utility.message('INFO','HR_DM_LIBARARY - check whether row exists ' ||
2694 'in hr_dm_dt_deletes table ',15);
2695 --hr_dm_utility.message('SUMM','HR_DM_LIBARARY - check whether row exists ' ||
2696 -- 'in hr_dm_dt_deletes table ',20);
2697
2698 hr_dm_utility.message('PARA','(p_ins_type - ' || p_ins_type
2699 || ')( p_row_exists - ' ||p_row_exists ,30);
2700 hr_dm_utility.message('ROUT','exit:hr_dm_library.chk_row_in_dt_delete_2_pkcol',
2701 25);
2702 exception
2703 when others then
2704 hr_dm_utility.error(SQLCODE,'hr_dm_library.chk_row_in_dt_delete_2_pkcol',
2705 '(none)','R');
2706 raise;
2707 end chk_row_in_dt_delete_2_pkcol;
2708
2709 -- ------------------------ chk_row_in_dt_delete_3_pkcol -----------------------
2710 -- Description: This function is used by Date Track table with non surrogate id.
2711 -- The primary key consists of three columns
2712 -- Checks whether a row exists for a given primary key columns of the table and
2713 -- type.
2714 -- Input Parameters
2715 -- p_pk_column_1 - Value of primary key column 1 of the table.
2716 -- p_pk_column_2 - Value of primary key column 2 of the table.
2717 -- p_pk_column_3 - Value of primary key column 3 of the table.
2718 -- p_table_name - Table name
2719 -- Out Parameters
2720 -- p_ins_type - If a row exists for the table/Id combination then one of
2721 -- the following value is returned.
2722 -- 'D' - for date track. created by the first physical record
2723 -- uploaded, so as other physical records belonging to
2724 -- the same logical record can avoid the checks.
2725 -- 'P' - row already exists.
2726 -- p_row_exists - If a row exists for the table/Id combination then it will
2727 -- have 'Y' ,otherwise 'N' value.
2728 -- ------------------------------------------------------------------------------
2729 procedure chk_row_in_dt_delete_3_pkcol
2730 ( p_pk_column_1 in number,
2731 p_pk_column_2 in number,
2732 p_pk_column_3 in number,
2733 p_table_name in varchar2,
2734 p_ins_type out nocopy varchar2,
2735 p_row_exists out nocopy varchar2
2736 ) is
2737
2738 l_data_type hr_dm_dt_deletes.data_type%type;
2739 --
2740 -- cursor to find the row in hr_dm_dt_deletes table for a given id, table and
2741 -- type combination
2742 cursor csr_find_dt_deletes is
2743 select data_type
2744 from hr_dm_dt_deletes
2745 where pk_column_1 = p_pk_column_1
2746 and pk_column_2 = p_pk_column_2
2747 and pk_column_3 = p_pk_column_3
2748 and table_name = p_table_name
2749 order by data_type;
2750 begin
2751 hr_dm_utility.message('ROUT','entry:hr_dm_library.chk_row_in_dt_delete_3_pkcol', 5);
2752 hr_dm_utility.message('PARA','(p_table_name - ' || p_table_name ||
2753 ')(p_pk_column_1 - ' || p_pk_column_1 ||
2754 ')(p_pk_column_2 - ' || p_pk_column_2 ||
2755 ')(p_pk_column_3 - ' || p_pk_column_3 ||
2756 ')', 10);
2757 open csr_find_dt_deletes;
2758 fetch csr_find_dt_deletes into l_data_type ;
2759 if csr_find_dt_deletes%found then
2760 p_row_exists := 'Y';
2761 p_ins_type := l_data_type;
2762 close csr_find_dt_deletes;
2763 else
2764 p_row_exists := 'N';
2765 p_ins_type := null;
2766 close csr_find_dt_deletes;
2767 end if;
2768 hr_dm_utility.message('INFO','HR_DM_LIBARARY - check whether row exists ' ||
2769 'in hr_dm_dt_deletes table ',15);
2770 --hr_dm_utility.message('SUMM','HR_DM_LIBARARY - check whether row exists ' ||
2771 -- 'in hr_dm_dt_deletes table ',20);
2772
2773 hr_dm_utility.message('PARA','(p_ins_type - ' || p_ins_type
2774 || ')( p_row_exists - ' ||p_row_exists ,30);
2775 hr_dm_utility.message('ROUT','exit:hr_dm_library.chk_row_in_dt_delete_3_pkcol',
2776 25);
2777 exception
2778 when others then
2779 hr_dm_utility.error(SQLCODE,'hr_dm_library.chk_row_in_dt_delete_3_pkcol',
2780 '(none)','R');
2781 raise;
2782 end chk_row_in_dt_delete_3_pkcol;
2783
2784 -- ------------------------ chk_row_in_dt_delete_4_pkcol ------------------------------
2785 -- Description: This function is used by Date Track table with non surrogate id.
2786 -- The primary key consists of four columns
2787 -- Checks whether a row exists for a given primary key columns of the table and type.
2788 -- Input Parameters
2789 -- p_pk_column_1 - Value of primary key column 1 of the table.
2790 -- p_pk_column_2 - Value of primary key column 2 of the table.
2791 -- p_pk_column_3 - Value of primary key column 3 of the table.
2792 -- p_pk_column_4 - Value of primary key column 4 of the table.
2793 -- p_table_name - Table name
2794 -- Out Parameters
2795 -- p_ins_type - If a row exists for the table/Id combination then one of
2796 -- the following value is returned.
2797 -- 'D' - for date track. created by the first physical record
2798 -- uploaded, so as other physical records belonging to
2799 -- the same logical record can avoid the checks.
2800 -- 'P' - row already exists.
2801 -- p_row_exists - If a row exists for the table/Id combination then it will
2802 -- have 'Y' ,otherwise 'N' value.
2803 -- ------------------------------------------------------------------------
2804 procedure chk_row_in_dt_delete_4_pkcol
2805 ( p_pk_column_1 in number,
2806 p_pk_column_2 in number,
2807 p_pk_column_3 in number,
2808 p_pk_column_4 in number,
2809 p_table_name in varchar2,
2810 p_ins_type out nocopy varchar2,
2811 p_row_exists out nocopy varchar2
2812 ) is
2813
2814 l_data_type hr_dm_dt_deletes.data_type%type;
2815 --
2816 -- cursor to find the row in hr_dm_dt_deletes table for a given id, table and
2817 -- type combination
2818 cursor csr_find_dt_deletes is
2819 select data_type
2820 from hr_dm_dt_deletes
2821 where pk_column_1 = p_pk_column_1
2822 and pk_column_2 = p_pk_column_2
2823 and pk_column_3 = p_pk_column_3
2824 and pk_column_4 = p_pk_column_4
2825 and table_name = p_table_name
2826 order by data_type;
2827 begin
2828 hr_dm_utility.message('ROUT','entry:hr_dm_library.chk_row_in_dt_delete_4_pkcol', 5);
2829 hr_dm_utility.message('PARA','(p_table_name - ' || p_table_name ||
2830 ')(p_pk_column_1 - ' || p_pk_column_1 ||
2831 ')(p_pk_column_2 - ' || p_pk_column_2 ||
2832 ')(p_pk_column_3 - ' || p_pk_column_3 ||
2833 ')(p_pk_column_4 - ' || p_pk_column_4 ||
2834 ')', 10);
2835 open csr_find_dt_deletes;
2836 fetch csr_find_dt_deletes into l_data_type ;
2837 if csr_find_dt_deletes%found then
2838 p_row_exists := 'Y';
2839 p_ins_type := l_data_type;
2840 close csr_find_dt_deletes;
2841 else
2842 p_row_exists := 'N';
2843 p_ins_type := null;
2844 close csr_find_dt_deletes;
2845 end if;
2846 hr_dm_utility.message('INFO','HR_DM_LIBARARY - check whether row exists ' ||
2847 'in hr_dm_dt_deletes table ',15);
2848 --hr_dm_utility.message('SUMM','HR_DM_LIBARARY - check whether row exists ' ||
2849 -- 'in hr_dm_dt_deletes table ',20);
2850
2851 hr_dm_utility.message('PARA','(p_ins_type - ' || p_ins_type
2852 || ')( p_row_exists - ' ||p_row_exists ,30);
2853 hr_dm_utility.message('ROUT','exit:hr_dm_library.chk_row_in_dt_delete_4_pkcol',
2854 25);
2855 exception
2856 when others then
2857 hr_dm_utility.error(SQLCODE,'hr_dm_library.chk_row_in_dt_delete_4_pkcol',
2858 '(none)','R');
2859 raise;
2860 end chk_row_in_dt_delete_4_pkcol;
2861 -- ------------------------ run_sql ---------------------------------------
2862 -- Description:
2863 -- Runs a SQL statement using the dbms_sql package. No bind variables
2864 -- allowed. The SQL command is passed to this procedure as a atrring of
2865 -- varchar2.
2866 --
2867 -- ------------------------------------------------------------------------
2868 procedure run_sql( p_sql in varchar2 )
2869 is
2870 l_csr_sql integer;
2871 l_rows number;
2872 begin
2873 hr_dm_utility.message('ROUT','entry:hr_dm_library.run_sql', 5);
2874 hr_dm_utility.message('PARA','(p_sql - varchar2)', 10);
2875 l_csr_sql := dbms_sql.open_cursor;
2876 dbms_sql.parse( l_csr_sql, p_sql, dbms_sql.native );
2877 l_rows := dbms_sql.execute( l_csr_sql );
2878 dbms_sql.close_cursor( l_csr_sql );
2879 hr_dm_utility.message('INFO','HR_DM_LIBARARY - run SQL command - 1',15);
2880 --hr_dm_utility.message('SUMM','HR_DM_LIBARARY - run SQL command - 1',20);
2881 hr_dm_utility.message('ROUT','exit:hr_dm_library.run_sql',
2882 25);
2883 exception
2884 when others then
2885 hr_dm_utility.error(SQLCODE,'hr_dm_library.run_sql',
2886 '(none)','R');
2887 raise;
2888 end run_sql;
2889 -- ------------------------ run_sql ---------------------------------------
2890 -- Description:
2891 -- Runs a SQL statement using the dbms_sql package. No bind variables
2892 -- allowed. This procedure uses pl/sql table of varchar2 as an input
2893 -- and hence is suitable to compile very large packages i.e more than
2894 -- 32767 char.
2895 -- ------------------------------------------------------------------------
2896 procedure run_sql(p_package_body dbms_sql.varchar2s,
2897 p_package_index number )
2898 is
2899 l_csr_sql integer;
2900 l_rows number;
2901
2902 begin
2903 hr_dm_utility.message('ROUT','entry:hr_dm_library.run_sql', 5);
2904 hr_dm_utility.message('PARA','(p_package_index - ' || p_package_index ||
2905 ')' || '(p_package_index - dbms_sql.varchar2s)'
2906 , 10);
2907 l_csr_sql := dbms_sql.open_cursor;
2908 dbms_sql.parse( l_csr_sql, p_package_body,1,p_package_index,FALSE, dbms_sql.v7 );
2909 l_rows := dbms_sql.execute( l_csr_sql );
2910 dbms_sql.close_cursor( l_csr_sql );
2911 hr_dm_utility.message('INFO','HR_DM_LIBARARY - run SQL command - 2',15);
2912 --hr_dm_utility.message('SUMM','HR_DM_LIBARARY - run SQL command - 2',20);
2913 hr_dm_utility.message('ROUT','exit:hr_dm_library.run_sql',
2914 25);
2915 exception
2916 when others then
2917 hr_dm_utility.error(SQLCODE,'hr_dm_library.run_sql - dbms_sql.varchar2s',
2918 '(none)','R');
2919 raise;
2920 end run_sql;
2921
2922 -- ------------------------ check_compile ---------------------------------
2923 -- Description:
2924 -- Checks whether or not the generated package or view compiled okay.
2925 -- ------------------------------------------------------------------------
2926 procedure check_compile
2927 (
2928 p_object_name in varchar2,
2929 p_object_type in varchar2
2930 )
2931 is
2932 e_invalid_package exception;
2933 cursor csr_check_compile
2934 (
2935 p_object_name in varchar2,
2936 p_object_type in varchar2
2937 ) is
2938 select status
2939 from user_objects
2940 where upper(object_name) = upper(p_object_name)
2941 and upper(object_type) = upper(p_object_type);
2942 l_status varchar2(64);
2943 begin
2944 hr_dm_utility.message('ROUT','entry:hr_dm_library.check_compile', 5);
2945 hr_dm_utility.message('PARA','(p_object_name - ' || p_object_name ||
2946 ')(p_object_type - ' || p_object_type ||
2947 ')', 10);
2948
2949 open csr_check_compile( p_object_name, p_object_type );
2950 fetch csr_check_compile into l_status;
2951 close csr_check_compile;
2952 if upper( l_status ) <> 'VALID' then
2953 raise e_invalid_package;
2954 end if;
2955 hr_dm_utility.message('INFO','HR_DM_LIBARARY - check status of the package ' ||
2956 'in hr_dm_dt_deletes table ',15);
2957 --hr_dm_utility.message('SUMM','HR_DM_LIBARARY - check status of the package ' ||
2958 -- 'in hr_dm_dt_deletes table ',20);
2959 hr_dm_utility.message('ROUT','exit:hr_dm_library.check_compile',
2960 25);
2961
2962 exception
2963 when e_invalid_package then
2964 hr_dm_utility.error(SQLCODE,'hr_dm_library.check_compile',
2965 'Invalid Status For Package ' || p_object_name ,'R');
2966 raise;
2967 when others then
2968 hr_dm_utility.error(SQLCODE,'hr_dm_library.check_compile',
2969 '(none)','R');
2970 raise;
2971 end check_compile;
2972
2973
2974
2975
2976 -- ------------------------- create_view ----------------------
2977 -- Description: Creates a view based on the passed table but excluding
2978 -- the business group id (if this column is defined for a table).
2979 --
2980 -- For tables like HR_DMVP%, do not modify the existing view created
2981 -- by the create_stub_views procedure.
2982 --
2983 --
2984 -- Input Parameters
2985 -- p_table_info - table_info
2986 --
2987 --
2988 -- Output Parameters
2989 -- <none>
2990 --
2991 --
2992 -- ------------------------------------------------------------------------
2993
2994 --
2995 procedure create_view (p_table_info in hr_dm_gen_main.t_table_info) is
2996 --
2997
2998 l_string varchar2(32767);
2999 l_columns1 varchar2(32767);
3000 l_columns2 varchar2(32767);
3001 l_column_name varchar2(30);
3002 l_first varchar2(1);
3003 l_value boolean;
3004 l_out_status varchar2(30);
3005 l_out_industry varchar2(30);
3006 l_out_oracle_schema varchar2(30);
3007 l_cr varchar2(10);
3008 l_where varchar2(1);
3009 l_owner varchar2(30);
3010 l_cursor_select_from varchar2(32767);
3011 l_cursor_select_where varchar2(32767);
3012 l_nonnull_table_id number;
3013 l_parent_table_info hr_dm_gen_main.t_table_info;
3014 l_apps_name varchar2(30);
3015
3016 cursor csr_apps_name is
3017 select ORACLE_USERNAME
3018 from fnd_oracle_userid
3019 where ORACLE_ID = 900;
3020
3021 cursor csr_columns is
3022 select column_name
3023 from all_tab_columns
3024 where table_name = upper(p_table_info.upload_table_name)
3025 and column_name not in ('BUSINESS_GROUP_ID','BATCH_ID')
3026 and data_type <> 'SDO_GEOMETRY'
3027 and not (table_name = 'FF_FORMULAS_F' and column_name = 'FORMULA_TEXT')
3028 and owner in
3029 (l_apps_name,
3030 l_fnd_owner,
3031 l_ff_owner,
3032 l_ben_owner,
3033 l_pay_owner,
3034 l_per_owner)
3035 order by column_id;
3036
3037 cursor csr_sp_columns is
3038 select column_name
3039 from all_tab_columns
3040 where table_name = upper(p_table_info.upload_table_name)
3041 and data_type <> 'SDO_GEOMETRY'
3042 and owner in
3043 (l_apps_name,
3044 l_fnd_owner,
3045 l_ff_owner,
3046 l_ben_owner,
3047 l_pay_owner,
3048 l_per_owner)
3049 order by column_id;
3050
3051 cursor csr_nonnull_id is
3052 select table_id
3053 from hr_dm_tables
3054 where table_name = upper(p_table_info.upload_table_name);
3055
3056 cursor csr_get_table is
3057 select distinct parent_table_id
3058 from (select table_id,parent_table_id
3059 from hr_dm_hierarchies
3060 where hierarchy_type = 'PC')
3061 start with table_id = l_nonnull_table_id
3062 connect by prior parent_table_id = table_id;
3063
3064 --
3065 begin
3066 --
3067
3068 hr_dm_utility.message('ROUT','entry:hr_dm_library.create_view', 5);
3069 hr_dm_utility.message('PARA','(p_table_info - record)', 10);
3070
3071 open csr_apps_name;
3072 fetch csr_apps_name into l_apps_name;
3073 close csr_apps_name;
3074
3075
3076 -- check for HR_DMVP% views
3077 -- only process for non-HR_DMVP% views
3078 if substr(p_table_info.table_name, 1,7) <> 'hr_dmvp' then
3079
3080 -- find out what chr(10) is
3081 l_cr := fnd_global.local_chr(10);
3082
3083 -- build up column list
3084 l_first := 'Y';
3085 hr_dm_utility.message('INFO','creating columns for ' ||
3086 p_table_info.table_name,10);
3087 if p_table_info.table_name <> 'hr_dmvs_hr_locations_all' then
3088 open csr_columns;
3089 loop
3090 fetch csr_columns into l_column_name;
3091 exit when csr_columns%notfound;
3092 if (l_first <> 'Y') then
3093 l_columns1 := l_columns1 || ', ' || l_column_name || l_cr;
3094
3095 l_columns2 := l_columns2 || ', ' || p_table_info.alias || '.' ||
3096 l_column_name || l_cr;
3097 else
3098 l_columns1 := ' ' || l_column_name || l_cr;
3099 l_columns2 := ' ' || p_table_info.alias || '.' || l_column_name || l_cr;
3100 l_first := 'N';
3101 end if;
3102 end loop;
3103 close csr_columns;
3104 else
3105 open csr_sp_columns;
3106 loop
3107 fetch csr_sp_columns into l_column_name;
3108 exit when csr_sp_columns%notfound;
3109 if (l_first <> 'Y') then
3110 l_columns1 := l_columns1 || ', ' || l_column_name || l_cr;
3111
3112 l_columns2 := l_columns2 || ', ' || p_table_info.alias || '.' ||
3113 l_column_name || l_cr;
3114 else
3115 l_columns1 := ' ' || l_column_name || l_cr;
3116 l_columns2 := ' ' || p_table_info.alias || '.' || l_column_name || l_cr;
3117 l_first := 'N';
3118 end if;
3119 end loop;
3120 close csr_sp_columns;
3121 end if;
3122
3123 -- get from clause
3124
3125 l_cursor_select_from := p_table_info.upload_table_name ||
3126 ' ' || p_table_info.alias;
3127
3128 -- find the table_id for the non-null version of the table
3129 open csr_nonnull_id;
3130 fetch csr_nonnull_id into l_nonnull_table_id;
3131 close csr_nonnull_id;
3132
3133 hr_dm_utility.message('INFO','table is ' || upper(p_table_info.upload_table_name), 15);
3134 hr_dm_utility.message('INFO','Parent id is ' || l_nonnull_table_id, 15);
3135
3136 -- if the table to be downloaded has table hierarchy i.e business group id
3137 -- has to be derived from table hierarchy i.e parent tables.
3138 if p_table_info.table_name <> 'hr_dmvs_hr_locations_all' then
3139 if p_table_info.table_hierarchy = 'Y' then
3140 for cst_get_table_rec in csr_get_table loop
3141 -- get the parent table name
3142 hr_dm_library.get_table_info (cst_get_table_rec.parent_table_id,
3143 l_parent_table_info);
3144 l_cursor_select_from := l_cursor_select_from || ' ' || ',' ||
3145 l_parent_table_info.table_name ||' ' || l_parent_table_info.alias || l_cr;
3146 end loop;
3147 end if;
3148
3149 -- get where clause
3150 -- if business_group_id column on table then...
3151 if p_table_info.table_hierarchy = 'N' then
3152 l_cursor_select_where := 'where ' ||p_table_info.alias || '.' ||
3153 'business_group_id is null';
3154 else
3155 hr_dm_imp_bg_where.main (p_table_info => p_table_info,
3156 p_cursor_type => 'VIEW',
3157 p_query_type => 'MAIN_QUERY',
3158 p_where_clause => l_cursor_select_where);
3159 end if;
3160 end if;
3161
3162
3163 -- build up command
3164 l_string := 'create or replace force view ' ||
3165 upper(p_table_info.table_name) || ' (' || l_cr ||
3166 l_columns1 || ')' || l_cr ||
3167 'as select ' || l_cr ||
3168 l_columns2 ||
3169 'from ' || l_cursor_select_from || l_cr ||
3170 l_cursor_select_where;
3171
3172
3173
3174 hr_dm_utility.message('INFO','View created using ' || l_cr || l_string, 15);
3175
3176 -- find the applsys username
3177 l_value := fnd_installation.get_app_info ('FND', l_out_status,
3178 l_out_industry,
3179 l_out_oracle_schema);
3180
3181 ad_ddl.do_ddl(l_out_oracle_schema, 'PER', ad_ddl.create_view,
3182 l_string, upper(p_table_info.table_name));
3183
3184 end if;
3185
3186 hr_dm_utility.message('INFO','View created - ' ||
3187 upper(p_table_info.table_name), 15);
3188 hr_dm_utility.message('SUMM','View created - ' ||
3189 upper(p_table_info.table_name), 20);
3190 hr_dm_utility.message('ROUT','exit:hr_dm_library.create_view', 25);
3191 hr_dm_utility.message('PARA','(none)', 30);
3192
3193 -- error handling
3194 exception
3195 when others then
3196 hr_dm_utility.error(sqlcode,'hr_dm_library.create_view','(none)','R');
3197 raise;
3198
3199 --
3200 end create_view;
3201 --
3202
3203
3204 -- ------------------------- seed_view_who -------------------------------
3205 -- Description: Seeds AOL hierarchy info for the WHO columns for the
3206 -- views created.
3207 --
3208 -- Input Parameters
3209 -- <none>
3210 --
3211 --
3212 --
3213 -- Output Parameters
3214 -- <none>
3215 --
3216 --
3217 -- ------------------------------------------------------------------------
3218
3219
3220 --
3221 procedure seed_view_who is
3222 --
3223
3224 cursor csr_table is
3225 select distinct dm.table_name,
3226 dm.table_id
3227 from hr_dm_tables dm,
3228 all_tab_columns tc
3229 where dm.table_name not like 'FND%'
3230 and dm.table_name like 'HR_DMV%'
3231 and dm.table_name = tc.table_name
3232 and tc.column_name = 'CREATED_BY'
3233 and tc.owner in
3234 (l_apps_owner,
3235 l_fnd_owner,
3236 l_ff_owner,
3237 l_ben_owner,
3238 l_pay_owner,
3239 l_per_owner);
3240
3241 l_table_name varchar2(30);
3242 l_table_id number;
3243 l_created_table varchar2(30);
3244 l_updated_table varchar2(30);
3245
3246 --
3247 begin
3248 --
3249
3250 hr_dm_utility.message('ROUT','entry:hr_dm_library.seed_view_who', 5);
3251
3252
3253 open csr_table;
3254 loop
3255 fetch csr_table into l_table_name, l_table_id;
3256 exit when csr_table%notfound;
3257
3258 -- get parent table ids
3259
3260 select table_id
3261 into l_created_table
3262 from hr_dm_tables
3263 where table_name = 'HR_DM_FND_USERS_V1';
3264
3265 select table_id
3266 into l_updated_table
3267 from hr_dm_tables
3268 where table_name = 'HR_DM_FND_USERS_V2';
3269
3270 -- do inserts, if no data already exists
3271
3272 insert into hr_dm_hierarchies
3273 ( hierarchy_id
3274 ,hierarchy_type
3275 ,sql_order
3276 ,table_id
3277 ,column_name
3278 ,parent_table_id
3279 ,parent_column_name
3280 ,parent_id_column_name)
3281 select hr_dm_hierarchies_s.nextval
3282 ,'A'
3283 ,NULL
3284 ,l_table_id
3285 ,'CREATED_BY'
3286 ,l_created_table
3287 ,'CREATED_NAME'
3288 ,'USER_NAME_ID'
3289 from dual
3290 where not exists (select 'x'
3291 from hr_dm_hierarchies hir
3292 where hir.hierarchy_type = 'A'
3293 and hir.table_id = l_table_id
3294 and nvl(hir.column_name,'X') = 'CREATED_BY'
3295 and nvl(hir.parent_table_id,-99) = l_created_table
3296 and nvl(hir.parent_column_name,'X') = 'CREATED_NAME'
3297 and nvl(hir.parent_id_column_name,'X') =
3298 'USER_NAME_ID'
3299 );
3300
3301 insert into hr_dm_hierarchies
3302 ( hierarchy_id
3303 ,hierarchy_type
3304 ,sql_order
3305 ,table_id
3306 ,column_name
3307 ,parent_table_id
3308 ,parent_column_name
3309 ,parent_id_column_name)
3310 select hr_dm_hierarchies_s.nextval
3311 ,'A'
3312 ,NULL
3313 ,l_table_id
3314 ,'LAST_UPDATED_BY'
3315 ,l_updated_table
3316 ,'UPDATED_NAME'
3317 ,'USER_NAME_ID'
3318 from dual
3319 where not exists (select 'x'
3320 from hr_dm_hierarchies hir
3321 where hir.hierarchy_type = 'A'
3322 and hir.table_id = l_table_id
3323 and nvl(hir.column_name,'X') = 'LAST_UPDATED_BY'
3324 and nvl(hir.parent_table_id,-99) = l_updated_table
3325 and nvl(hir.parent_column_name,'X') = 'UPDATED_NAME'
3326 and nvl(hir.parent_id_column_name,'X') =
3327 'USER_NAME_ID'
3328 );
3329
3330 end loop;
3331 close csr_table;
3332
3333
3334 -- commit data
3335 commit;
3336
3337 hr_dm_utility.message('INFO','Created seed AOL hierarchy info', 15);
3338 hr_dm_utility.message('SUMM','Created seed AOL hierarchy info', 20);
3339 hr_dm_utility.message('ROUT','exit:hr_dm_library.seed_view_who', 25);
3340 hr_dm_utility.message('PARA','(none)', 30);
3341
3342 -- error handling
3343 exception
3344 when others then
3345 hr_dm_utility.error(sqlcode,'hr_dm_library.seed_view_who',
3346 'error seed AOL hierarchy info','r');
3347 raise;
3348
3349 --
3350 end seed_view_who;
3351 --
3352
3353 -- ------------------------- seed_view_null -------------------------------
3354 -- Description: Seeds AOL hierarchy info for the WHO columns for the
3355 -- views created.
3356 --
3357 -- Input Parameters
3358 -- <none>
3359 --
3360 --
3361 --
3362 -- Output Parameters
3363 -- <none>
3364 --
3365 --
3366 -- ------------------------------------------------------------------------
3367
3368
3369 --
3370 procedure seed_view_null is
3371 --
3372
3373 cursor csr_table is
3374 select distinct dm.table_name,
3375 dm.table_id
3376 from hr_dm_tables dm,
3377 all_tab_columns tc1,
3378 all_tab_columns tc2
3379 where dm.table_name not like 'FND%'
3380 and dm.table_name like 'HR_DMV%'
3381 and dm.table_name = tc1.table_name
3382 and dm.table_name = tc2.table_name
3383 and tc1.column_name = 'REQUEST_ID'
3384 and tc2.column_name = 'PROGRAM_APPLICATION_ID'
3385 and tc2.owner = tc1.owner
3386 and tc1.owner in
3387 (l_apps_owner,
3388 l_fnd_owner,
3389 l_ff_owner,
3390 l_ben_owner,
3391 l_pay_owner,
3392 l_per_owner);
3393
3394 l_table_name varchar2(30);
3395 l_table_id number;
3396
3397 --
3398 begin
3399 --
3400
3401 hr_dm_utility.message('ROUT','entry:hr_dm_library.seed_view_null', 5);
3402
3403
3404 open csr_table;
3405 loop
3406 fetch csr_table into l_table_name, l_table_id;
3407 exit when csr_table%notfound;
3408
3409 -- do inserts, if no data already exists
3410
3411 insert into hr_dm_hierarchies
3412 ( hierarchy_id
3413 ,hierarchy_type
3414 ,sql_order
3415 ,table_id
3416 ,column_name
3417 ,parent_table_id
3418 ,parent_column_name
3419 ,parent_id_column_name)
3420 select hr_dm_hierarchies_s.nextval
3421 ,'N'
3422 ,NULL
3423 ,l_table_id
3424 ,'REQUEST_ID'
3425 ,NULL
3426 ,NULL
3427 ,NULL
3428 from dual
3429 where not exists (select 'x'
3430 from hr_dm_hierarchies hir
3431 where hir.hierarchy_type = 'N'
3432 and hir.table_id = l_table_id
3433 and nvl(hir.column_name,'X') = 'REQUEST_ID'
3434 );
3435
3436 insert into hr_dm_hierarchies
3437 ( hierarchy_id
3438 ,hierarchy_type
3439 ,sql_order
3440 ,table_id
3441 ,column_name
3442 ,parent_table_id
3443 ,parent_column_name
3444 ,parent_id_column_name)
3445 select hr_dm_hierarchies_s.nextval
3446 ,'N'
3447 ,NULL
3448 ,l_table_id
3449 ,'PROGRAM_APPLICATION_ID'
3450 ,NULL
3451 ,NULL
3452 ,NULL
3453 from dual
3454 where not exists (select 'x'
3455 from hr_dm_hierarchies hir
3456 where hir.hierarchy_type = 'N'
3457 and hir.table_id = l_table_id
3458 and nvl(hir.column_name,'X') =
3459 'PROGRAM_APPLICATION_ID'
3460 );
3461
3462 end loop;
3463 close csr_table;
3464
3465
3466 -- commit data
3467 commit;
3468
3469 hr_dm_utility.message('INFO','Created seed null hierarchy info', 15);
3470 hr_dm_utility.message('SUMM','Created seed null hierarchy info', 20);
3471 hr_dm_utility.message('ROUT','exit:hr_dm_library.seed_view_null', 25);
3472 hr_dm_utility.message('PARA','(none)', 30);
3473
3474 -- error handling
3475 exception
3476 when others then
3477 hr_dm_utility.error(sqlcode,'hr_dm_library.seed_view_null',
3478 'error seed null hierarchy info','r');
3479 raise;
3480
3481 --
3482 end seed_view_null;
3483 --
3484
3485
3486 -- ------------------------- create_stub_views ------------------------
3487 -- Description: Creates dummy views for the hr_dmv type 'tables' to avoid
3488 -- compilation errors during the generate phase, when the correct form of
3489 -- the views will be created.
3490 --
3491 --
3492 -- Input Parameters
3493 -- p_migration_id - current migration
3494 --
3495 --
3496 --
3497 -- Output Parameters
3498 -- <none>
3499 --
3500 --
3501 -- ------------------------------------------------------------------------
3502
3503
3504 --
3505 procedure create_stub_views(p_migration_id in number) is
3506 --
3507
3508 l_view_name varchar2(30);
3509 l_table_name varchar2(30);
3510 l_string varchar2(32767);
3511 l_value boolean;
3512 l_out_status varchar2(30);
3513 l_out_industry varchar2(30);
3514 l_out_oracle_schema varchar2(30);
3515 l_cr varchar2(10);
3516 l_columns varchar2(32767);
3517 l_column_name varchar2(30);
3518 l_first varchar2(1);
3519 l_apps_name varchar2(30);
3520
3521 cursor csr_apps_name is
3522 select ORACLE_USERNAME
3523 from fnd_oracle_userid
3524 where ORACLE_ID = 900;
3525
3526 cursor csr_view is
3527 select dmt.table_name,
3528 dmt.upload_table_name
3529 from hr_dm_tables dmt,
3530 hr_dm_phase_items pi,
3531 hr_dm_phases p
3532 where p.phase_name = 'G'
3533 and pi.phase_id = p.phase_id
3534 and pi.table_name = dmt.table_name
3535 and pi.status <> 'C'
3536 and dmt.table_name like 'HR_DMV%'
3537 and p.migration_id = p_migration_id;
3538
3539 cursor csr_columns is
3540 select column_name
3541 from all_tab_columns
3542 where table_name = l_table_name
3543 and column_name not in ('BUSINESS_GROUP_ID','BATCH_ID')
3544 and data_type <> 'SDO_GEOMETRY'
3545 and not (table_name = 'FF_FORMULAS_F' and column_name = 'FORMULA_TEXT')
3546 and owner in
3547 (l_apps_name,
3548 l_fnd_owner,
3549 l_ff_owner,
3550 l_ben_owner,
3551 l_pay_owner,
3552 l_per_owner)
3553 order by column_id;
3554
3555 cursor csr_sp_columns is
3556 select column_name
3557 from all_tab_columns
3558 where table_name = l_table_name
3559 and data_type <> 'SDO_GEOMETRY'
3560 and owner in
3561 (l_apps_name,
3562 l_fnd_owner,
3563 l_ff_owner,
3564 l_ben_owner,
3565 l_pay_owner,
3566 l_per_owner)
3567 order by column_id;
3568
3569 cursor csr_sp2_columns is
3570 select column_name
3571 from all_tab_columns
3572 where table_name = l_table_name
3573 and column_name <> 'BATCH_ID'
3574 and owner in
3575 (l_apps_name,
3576 l_fnd_owner,
3577 l_ff_owner,
3578 l_ben_owner,
3579 l_pay_owner,
3580 l_per_owner)
3581 order by column_id;
3582
3583
3584 --
3585 begin
3586 --
3587
3588 hr_dm_utility.message('ROUT','entry:hr_dm_library.create_stub_views', 5);
3589 hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id || ')',
3590 10);
3591
3592 open csr_apps_name;
3593 fetch csr_apps_name into l_apps_name;
3594 close csr_apps_name;
3595
3596 -- find out what chr(10) is
3597 l_cr := fnd_global.local_chr(10);
3598
3599 -- find which views we need to build
3600 open csr_view;
3601 loop
3602 fetch csr_view into l_view_name, l_table_name;
3603 exit when csr_view%notfound;
3604 if csr_view%found then
3605
3606 -- build up column list
3607 l_first := 'Y';
3608 hr_dm_utility.message('INFO','creating columns for ' || l_view_name,10);
3609
3610 if l_view_name = 'HR_DMVS_HR_LOCATIONS_ALL' then
3611 open csr_sp_columns;
3612 loop
3613 fetch csr_sp_columns into l_column_name;
3614 exit when csr_sp_columns%notfound;
3615 if (l_first <> 'Y') then
3616 l_columns := l_columns || ', ' || l_column_name || l_cr;
3617 else
3618 l_columns := ' ' || l_column_name || l_cr;
3619 l_first := 'N';
3620 end if;
3621 end loop;
3622 close csr_sp_columns;
3623 elsif l_view_name = 'HR_DMVP_PER_ABS_ATTNDS' then
3624 l_table_name := 'PER_ABSENCE_ATTENDANCES';
3625 open csr_sp2_columns;
3626 loop
3627 fetch csr_sp2_columns into l_column_name;
3628 exit when csr_sp2_columns%notfound;
3629 if (l_first <> 'Y') then
3630 l_columns := l_columns || ', ' || l_column_name || l_cr;
3631 else
3632 l_columns := ' ' || l_column_name || l_cr;
3633 l_first := 'N';
3634 end if;
3635 end loop;
3636 close csr_sp2_columns;
3637 else
3638 -- normal case
3639 open csr_columns;
3640 loop
3641 fetch csr_columns into l_column_name;
3642 exit when csr_columns%notfound;
3643 if (l_first <> 'Y') then
3644 l_columns := l_columns || ', ' || l_column_name || l_cr;
3645 else
3646 l_columns := ' ' || l_column_name || l_cr;
3647 l_first := 'N';
3648 end if;
3649 end loop;
3650 close csr_columns;
3651 end if;
3652
3653
3654
3655 l_string := 'create or replace force view ' ||
3656 l_view_name || ' as select ' || l_cr ||
3657 l_columns || 'from ' || l_table_name;
3658
3659
3660 hr_dm_utility.message('INFO','Creating view ' || l_view_name, 15);
3661 hr_dm_utility.message('INFO','View created using ' || l_cr || l_string, 15);
3662
3663 -- find the applsys username
3664 l_value := fnd_installation.get_app_info ('FND', l_out_status,
3665 l_out_industry,
3666 l_out_oracle_schema);
3667
3668 ad_ddl.do_ddl(l_out_oracle_schema, 'PER', ad_ddl.create_view,
3669 l_string, l_view_name);
3670
3671 end if;
3672 end loop;
3673 close csr_view;
3674
3675 -- seed AOL hierarchy info for WHO columns
3676 -- for views created which contain WHO columns
3677 seed_view_who;
3678
3679 -- seed null hierarchy info for REQUEST_ID / PROGRAM_APPLICATION_ID columns
3680 -- for views created which contain REQUEST_ID / PROGRAM_APPLICATION_ID columns
3681 seed_view_null;
3682
3683 hr_dm_utility.message('INFO','Created stub views', 15);
3684 hr_dm_utility.message('SUMM','Created stub views', 20);
3685 hr_dm_utility.message('ROUT','exit:hr_dm_library.create_stub_views', 25);
3686 hr_dm_utility.message('PARA','(none)', 30);
3687
3688 -- error handling
3689 exception
3690 when others then
3691 hr_dm_utility.error(sqlcode,'hr_dm_library.create_stub_views',
3692 'error creating stub views','r');
3693 raise;
3694
3695 --
3696 end create_stub_views;
3697 --
3698
3699
3700
3701
3702 end hr_dm_library;
[Home] [Help]