1 package hr_dm_library AUTHID CURRENT_USER as
2 /* $Header: perdmlib.pkh 115.11 2003/01/13 19:42:35 mmudigon ship $ */
3 --------------------------------------------------------------
4 -- ERROR MESSAGING AND TRACING STUFF AND CONSTANT DEFINATIONS--
5 ---------------------------------------------------------------
6 --
7 -- Trace levels.
8 --
9 --c_trace_level1 constant number default 1;
10 --c_trace_level2 constant number default 2;
11 --g_trace_level number := 0;
12 --------------------------------
13 -- DATA STRUCTURE DEFINITIONS --
14 --------------------------------
15 -- Migration information structure. Stores the main details of the data
16 -- migration run
17 --
18
19 type t_data_migration is record
20 (
21 migration_id hr_dm_migrations.migration_id%type,
22 business_group_id hr_dm_migrations.business_group_id%type,
23 migration_type hr_dm_migrations.migration_type%type,
24 source_database hr_dm_migrations.source_database_instance%type,
25 destination_database hr_dm_migrations.destination_database_instance%type
26 );
27 --
28 -- List Of PL/SQL varchar2 tables
29 --
30 type t_varchar2_tbl is table of varchar2(100) index by binary_integer;
31
32 --------------------------
33 -- CONSTANT DEFINITIONS --
34 --------------------------
35 c_newline constant varchar2(500) := fnd_global.newline;
36 /*
37 c_newline constant varchar(1) default '
38 ';
39 */
40
41 ---------------------------
42 -- Global PL/SQL tables. --
43 ---------------------------
44 --
45 -- Tables to assist in code generation.
46 --
47 g_columns_tbl t_varchar2_tbl;
48 g_proc_parameter_tbl t_varchar2_tbl;
49
50 -- ----------------------- indent -----------------------------------------
51 -- Description:
52 -- returns the 'n' blank spaces on a newline.used to indent the procedure
53 -- statements.
54 --
55 -- ------------------------------------------------------------------------
56
57 function indent
58 (
59 p_indent_spaces in number default 0,
60 p_newline in varchar2 default 'Y'
61 ) return varchar2;
62
63
64 -- ------------------------- get_generator_version ------------------------
65 -- Description:
66 -- It gets the version number of the Genrator by concatenating the arcs
67 -- version of Main Generator package, TUPS Generator package and TDS package.
68 -- It is used by the Main Generator to stamp the generator version for each
69 -- generated TUPS/TDS and by initialisation program to check whether given
70 -- TUPS/TDS had been compiled by the latest generator.
71 -- Input Parameters
72 -- p_format_output - Whether a formatted output is required or not.
73 -- For updating the generator_version field no
74 -- formatting is required. Output will be stored as a one large string.
75 -- But for TUPs/TDS packages output string is
76 -- properly indented. It can have two values :
77 -- 'Y' - Formatted output is required
78 -- 'N' - Output string without indentation.
79 -- Output Parameters
80 -- p_package_version - It returns the text string created by the ARCS
81 -- for the package.
82 --
83 -- ------------------------------------------------------------------------
84 procedure get_generator_version
85 (
86 p_generator_version out nocopy varchar2,
87 p_format_output in varchar2 default 'N'
88 );
89 -- ------------------------- get_package_version ------------------------
90 -- Description:
91 -- It gets the version number for the given package. Depending upon the
92 -- version type required it either returns the full header string of the
93 -- package body or concatenate the File name and Version number of package
94 -- header and
95 -- body of the package.
96 -- Input Parameters :
97 -- p_package_name - Name of the stored package whose version number
98 -- is required.
99 -- p_version_type - It identifies what sort of output version string is
100 -- required. It can have following values
101 -- SUMMARY - concatenate the File name and Version
102 -- number of package header and body of the
103 -- package.
104 -- the output version string will look as
105 -- ' hrdmgen.pkh 115.1 : hrdmgen.pkb 115.1 '
106 -- FULL - Full header string from the package
107 -- body
108 -- is returned.
109 -- the output version string will look as
110 -- /* $Header: perdmlib.pkh 115.11 2003/01/13 19:42:35 mmudigon ship $ */
111 -- Output Parameters
112 -- p_package_version - It returns the text string created by the ARCS
113 -- for the package.
114 --
115 --
116 -- ------------------------------------------------------------------------
117 procedure get_package_version
118 (
119 p_package_name in varchar2,
120 p_package_version out nocopy varchar2,
121 p_version_type in varchar2 default 'SUMMARY'
122 );
123
124 -- ------------------------- get_table_info ------------------------
125 -- Description:
126 -- It returns the properties of the table for the given id.
127 -- Input Parameters :
128 -- p_table_id - Primary key of the hr_dm_tables.
129 -- Output Parameters
130 -- p_table_info - Various properties of the table is returned in
131 -- pl/sql table. The properties are
132 -- o table_id
133 -- o table_name
134 -- o datetrack
135 -- o surrogate_primary_key (Y/N)
136 -- o surrogate_pk_column_name
137 -- o table_alias
138 -- o short_name of the table
139 --
140 -- ------------------------------------------------------------------------
141
142 procedure get_table_info
143 (
144 p_table_id in number,
145 p_table_info out nocopy hr_dm_gen_main.t_table_info
146 );
147
148 -- ------------------------- check_col_for_fk_on_aol ------------------------
149 -- Description:
150 -- It checks whether a given column name exists in the pl/sql table which
151 -- contains the list of all the columns which have foreign key on AOL table or
152 -- columns whose id value need to be resolved.
153 --Input parameters
154 -- p_fk_to_aol_columns_tbl - This can contain the list of columns which have
155 -- 'A' type hierarchy or 'L' type hierarchy.
156 -- p_column_name - Name of the column which needs to be searched in
157 -- the above list.
158 -- Out parameters
159 -- p_index - index of list, if it finds the given column in the above
160 -- list.
161 --------------------------------------------------------------------------------
162 procedure check_col_for_fk_on_aol
163 (
164 p_fk_to_aol_columns_tbl in hr_dm_gen_main.t_fk_to_aol_columns_tbl,
165 p_column_name in varchar2,
166 p_index out nocopy number
167 ) ;
168
169 -- ------------------------- populate_fk_to_aol_cols -----------------------
170 -- Description:
171 -- initially this procedure is designed to store the details of from hierarchies
172 -- table for AOL type hierarchy i.e hierarchy type 'A'. But we added another
173 -- hierarchy type 'L' for looking up the ID value i.e use the corresponding id
174 -- value of the parent table at destination for a given column.
175 -- It populates the PL/SQL table with all columns details stored in a
176 -- hr_dm_hierarchies table for a given table and hierarchy type.
177 -- Input Parameters
178 -- p_hierarchy_type - 'A' - AOL type hierarchy
179 -- 'L' - lookup type hierarchy.
180 ----------------------------------------------------------------------------
181 procedure populate_fk_to_aol_cols_info
182 (
183 p_table_info in hr_dm_gen_main.t_table_info,
184 p_fk_to_aol_columns_tbl out nocopy hr_dm_gen_main.t_fk_to_aol_columns_tbl,
185 p_hierarchy_type in varchar2 default 'A'
186 );
187 -- ------------------------- populate_columns_list ------------------------
188 -- Description:
189 -- It populates the PL/SQL table with the list of column. This is to avoid
190 -- database access getting the column list again.
191 -- e.g : Table T1 has column col1,col2then the out parameter list will be
192 -- populated as
193 -- p_columns_list = col1 | col2
194 -- p_parameter_list = p_col1 in number | p_col2 in varchar2
195 --
196 -- Input Parameters :
197 -- p_table_name - Table name.
198 -- Output Parameters
199 -- p_columns_tbl - Out pl/sql table type t_varchar2_tbl. It contains
200 -- the list of columns of the table.
201 -- p_parameter_tbl - Out pl/sql table type t_varchar2_tbl. It contains
202 -- the list of column name used as a input arguments
203 -- in the procedure.
204 -- ------------------------------------------------------------------------
205 procedure populate_columns_list
206 (
207 p_table_info in hr_dm_gen_main.t_table_info,
208 p_fk_to_aol_columns_tbl in hr_dm_gen_main.t_fk_to_aol_columns_tbl,
209 p_columns_tbl out nocopy t_varchar2_tbl,
210 p_parameter_tbl out nocopy t_varchar2_tbl,
211 p_aol_columns_tbl out nocopy t_varchar2_tbl,
212 p_aol_parameter_tbl out nocopy t_varchar2_tbl,
213 p_missing_who_info out nocopy varchar2
214 );
215
216 -- ------------------------- populate_pk_columns_list ------------------------
217 -- Description:
218 -- It populates the PL/SQL table with the list of primary key column. This is
219 -- to avoid database access getting the column list again.
220 -- e.g : Table T1 has primary key columns pk_col1,pk_col2then the out
221 -- parameter list will be populated as
222 -- p_columns_list = pk_col1 | pk_col2
223 -- p_parameter_list = p_pk_col1 in number | p_pk_col2 in varchar2
224 --
225 -- Input Parameters :
226 -- p_table_info - pl/sql table contains info like table name and
227 -- various properties of the table.
228 -- Output Parameters
229 -- p_pk_columns_tbl - Out pl/sql table type t_varchar2_tbl.
230 -- It contains the list ofprimary key columns of
231 -- the table.
232 -- p_pk_parameter_tbl - Out pl/sql table type t_varchar2_tbl. It
233 -- contains the list of primary key column name
234 -- used as a input arguments in the procedure.
235 -- p_no_of_pk_columns - Out number of primary key columns in the
236 -- primary key.
237 -- ------------------------------------------------------------------------
238 procedure populate_pk_columns_list
239 (
240 p_table_info in hr_dm_gen_main.t_table_info,
241 p_pk_columns_tbl out nocopy t_varchar2_tbl,
242 p_pk_parameter_tbl out nocopy t_varchar2_tbl,
243 p_no_of_pk_columns out nocopy number
244 );
245
246 -- ------------------------- populate_hierarchy_cols_list ------------------
247 -- Description:
248 -- It populates the PL/SQL table with the list of hierarchy column. This is
249 -- to avoid database access getting the column list again.
250 -- e.g : Table T1 has column col1,col2then the out parameter list will be
251 -- populated as
252 -- p_hier_columns_list = col1 | col2
253 -- p_hier_parameter_list = p_col1 in number | p_col2 in varchar2
254 --
255 -- Input Parameters :
256 -- p_table_info - Information about table in PL/SQL Table.
257 -- Output Parameters
258 -- p_hier_columns_tbl - Out pl/sql table type t_varchar2_tbl. It
259 -- contains the list of hierarchy columns in
260 -- a table. The list content varies depending
261 -- upon the value of p_called_from parameter.
262 -- If it is called from
263 -- TUPS - it contains only the list of hierarchy
264 -- columns
265 -- TDS - it contains the list of hierarchy columns
266 -- and the primary key column names also.
267 -- p_hier_parameter_tbl - Out pl/sql table type t_varchar2_tbl. It
268 -- contains the list of hierarchy columns used
269 -- as a input arguments in the procedure.
270 -- p_called_from - It can have following values :
271 -- TUPS - if this function is called from TUPS
272 -- generator package.
273 -- TDS - if this function is called from TDS
274 -- generator package.
275 -- ------------------------------------------------------------------------
276 procedure populate_hierarchy_cols_list
277 (
278 p_table_info in hr_dm_gen_main.t_table_info,
279 p_hier_columns_tbl out nocopy t_varchar2_tbl,
280 p_hier_parameter_tbl out nocopy t_varchar2_tbl,
281 p_called_from in varchar2
282 );
283
284 -- ------------------------- get_cols_list_wo_pk_cols -----------------------
285 -- Description:
286 -- This procedure returns list of columns of the table which are not the
287 -- part of primary columns. It is used by TUPS for generating update_dml
288 -- as we do not want to update the primary key columns.
289 -- Input parameters :
290 -- p_columns_tbl - List of all columns of table.
291 -- p_pk_columns_tbl - List of primary key columns of table
292 --
293 -- Output Parameter:
294 -- p_cols_wo_pk_cols_tbl - List of columns of table which are not the
295 -- part of primary key.
296 --
297 -- It checks whether a given column name exists in the pl/sql table which
298 -- contains the list of all the columns which have foreign key on AOL table.
299 -----------------------------------------------------------------------------
300 procedure get_cols_list_wo_pk_cols
301 (
302 p_columns_tbl in hr_dm_library.t_varchar2_tbl,
303 p_pk_columns_tbl in hr_dm_library.t_varchar2_tbl,
304 p_cols_wo_pk_cols_tbl out nocopy hr_dm_library.t_varchar2_tbl
305 );
306
307 -- ------------------------- conv_list_to_text ---------------------------
308 -- Description:
309 -- It reads the list elements and converts them to varchar2 text in which
310 -- each element is separated by comma and put into the next line. Each
311 -- element is padded with the given number of spaces. e.g
312 -- A list contains col1,col2,col3 as elements.
313 -- It will put the output as
314 -- col1,
315 -- col2,
316 -- col3
317 --
318 -- Note: There is an overloaded version of this function where we require
319 -- some columns to be prefixed with a different prefix. Changes should
320 -- be applied to both versions where applicable.
321 --
322 -- Input Parameters :
323 -- p_rpad_spaces - Number of blank spaces added before writing the
324 -- element on new line.
325 -- p_pad_first_line - It means whether the spaces should be added to
326 -- the first element or not.
327 -- 'Y' - spaces are added to the first element
328 -- 'N' - spaces are not added to the first element
329 -- p_prefix_col - Prefix the element with this value. e.g if
330 -- p_prefix_col is 'p_' then all elements will be
331 -- prefixed with p_ and our list output will be
332 -- p_col1,
333 -- p_col2,
334 -- p_col3.
335 -- p_columns_tbl - List of the columns or elements which is required
336 -- to be changed into text.
337 -- It is of pl/sql type t_varchar2_tbl,
338 -- p_col_length - It adds the spaces after the column name so as
339 -- to make the column name length same for each
340 -- column by adding the required number of spaces.
341 -- p_start_terminator This is put at the begning of the assignment from
342 -- the second element onwards. By default it is ','
343 -- but in some cases it can be 'and' especially use
344 -- by TUPS for generating where clause for composite
345 -- primary keys.
346 -- p_end_terminator This is put at the end of the assignment. It is
347 -- null most of the time and is used by Date Track
348 -- TUPS with composite key to terminate the
349 -- assignment with ';'.
350 -- Returns
351 -- It returns a string by putting each element of the table into a newline.
352 -- ------------------------------------------------------------------------
353 function conv_list_to_text
354 (
355 p_rpad_spaces in number,
356 p_pad_first_line in varchar2 default 'N',
357 p_prefix_col in varchar2 default null,
358 p_columns_tbl in t_varchar2_tbl,
359 p_col_length in number default 30,
360 p_start_terminator in varchar2 default ',',
361 p_end_terminator in varchar2 default null
362 )
363 return varchar2;
364
365 -- ------------------------- conv_list_to_text ---------------------------
366 -- Description:
367 -- It reads the list elements and converts them to varchar2 text in which
368 -- each element is separated by comma and put into the next line. Each
369 -- element is padded with the given number of spaces. e.g
370 -- A list contains col1,col2,col3 as elements.
371 -- It will put the output as
372 -- col1,
373 -- col2,
374 -- col3
375 --
376 -- Note: There is a non-overloaded version of this function. Changes should
377 -- be applied to both versions where applicable.
378 --
379 -- Input Parameters :
380 -- p_rpad_spaces - Number of blank spaces added before writing the
381 -- element on new line.
382 -- p_pad_first_line - It means whether the spaces should be added to
383 -- the first element or not.
384 -- 'Y' - spaces are added to the first element
385 -- 'N' - spaces are not added to the first element
386 -- p_prefix_col - Prefix the element with this value. e.g if
387 -- p_prefix_col is 'p_' then all elements will be
388 -- prefixed with p_ and our list output will be
389 -- p_col1,
390 -- p_col2,
391 -- p_col3.
392 -- p_columns_tbl - List of the columns or elements which is required
393 -- to be changed into text.
394 -- It is of pl/sql type t_varchar2_tbl,
395 -- p_col_length - It adds the spaces after the column name so as
396 -- to make the column name length same for each
397 -- column by adding the required number of spaces.
398 -- p_start_terminator This is put at the begning of the assignment from
399 -- the second element onwards. By default it is ','
400 -- but in some cases it can be 'and' especially use
401 -- by TUPS for generating where clause for composite
402 -- primary keys.
403 -- p_end_terminator This is put at the end of the assignment. It is
404 -- null most of the time and is used by Date Track
405 -- TUPS with composite key to terminate the
406 -- assignment with ';'.
407 -- p_overide_tbl A table which lists which columns should be
408 -- prefixed by an alternative to p_prefix_col
409 -- p_overide_prefix Prefix to use instead of p_prefix_col
410 -- Returns
411 -- It returns a string by putting each element of the table into a newline.
412 -- ------------------------------------------------------------------------
413 function conv_list_to_text
414 (
415 p_rpad_spaces in number,
416 p_pad_first_line in varchar2 default 'N',
417 p_prefix_col in varchar2 default null,
418 p_columns_tbl in t_varchar2_tbl,
419 p_col_length in number default 30,
420 p_start_terminator in varchar2 default ',',
421 p_end_terminator in varchar2 default null,
422 p_overide_tbl in hr_dm_gen_main.t_fk_to_aol_columns_tbl,
423 p_overide_prefix in varchar2 default null
424 )
425 return varchar2;
426
427 -- ------------------------- get_nvl_arguement ---------------------------
428 -- Description:
429 -- It
430 -- Input Parameters :
431 -- p_test_with_nvl Flag to indicate if NVL testing is required, if
432 -- so then return value is ''
433 -- p_table_name Name of the table
434 -- p_column_name Name of the column in the table
435 -- p_nvl_prefix Prefix for nvl variable
436 -- p_nvl_suffix Suffix for nvl variable
437 -- Returns
438 -- <none>
439 -- ------------------------------------------------------------------------
440 procedure get_nvl_arguement
441 (
442 p_test_with_nvl in varchar2,
443 p_table_name in varchar2,
444 p_column_name in varchar2,
445 p_nvl_prefix out nocopy varchar2,
446 p_nvl_suffix out nocopy varchar2
447 );
448
449
450 -- ------------------------- get_func_asg ---------------------------
451 -- Description:
452 -- It reads the list columns and returns the parameters list required for
453 -- inserting the data into data pump batch_lines table or any other TUPS
454 -- function
455 -- e.g p_col1 => p_col2,
456 -- p_col2 => p_col2...
457 --
458 -- NOTE: This function is overloaded
459 --
460 -- Input Parameters :
461 -- p_rpad_spaces - Number of blank spaces added before writing the
462 -- element on new line.
463 -- p_columns_tbl - List of the columns or elements which is required
464 -- to be changed into text of parameter assignment.
465 -- It is of pl/sql type t_varchar2_tbl,
466 -- p_prefix_left_asg - Prefix the left element with this value. e.g if
467 -- value is 'p_' then all elements on the left hand
468 -- side of the assignment will be prefixed with 'p_'
469 -- prefixed with p_ and our list output will be
470 -- p_col1 => col1,
471 -- p_col2 => col2,
472 -- p_col3 => col3
473 -- p_prefix_right_asg - Prefix the right element with this value. e.g if
474 -- value is 'p_' then all elements on the right hand
475 -- side of the assignment will be prefixed with 'p_'
476 -- prefixed with p_ and our list output will be
477 -- col1 => p_col1,
478 -- col2 => p_col2,
479 -- col3 => p_col3
480 -- p_omit_business_group_id - It is whether to exclude the
481 -- business_group_id assignment from the list.
482 -- 'Y' - does not include business_group_id column
483 -- for parameter assignment. (default value)
484 -- 'N' - includes business_group_id column for
485 -- parameter assignment.
486 -- p_comma_on_first_line - Put the comma in the first element or not.
487 -- 'Y' - puts the comma before the first element
488 -- parameter assignment.
489 -- 'N' - does not put comma before the first element
490 -- parameter assignment.
491 -- p_equality_sign - By default the equality sign of the parameter
492 -- assignment is ' => '. But it can be '=' for
493 -- update statement set column assignment.
494 -- p_pad_first_line - It means whether the spaces should be added to
495 -- the first element or not.
496 -- 'Y' - spaces are added to the first element
497 -- 'N' - spaces are not added to the first element
498 -- p_left_asg_pad_len - It means the length of the left hand parameter
499 -- after prefix. e.g p_prefix_left is 'p_' and column
500 -- name is 'responsibility_application_id', if the
501 -- length is 30 the the left hand parameter will be
502 -- 'p_responsibility_application_i'.
503 -- p_right_asg_pad_len same as above but applied to right hand side
504 -- parameter.
505 -- parameter.
506 -- p_start_terminator This is put at the begning of the assignment from
507 -- the second element onwards. By default it is ','
508 -- but in some cases it can be 'and' especially use
509 -- by TUPS for generating where clause for composite
510 -- primary keys.
511 -- p_end_terminator This is put at the end of the assignment. It is
512 -- null most of the time and is used by Date Track
513 -- TUPS with composite key to terminate the
514 -- assignment with ';'.
515 -- p_test_with_nvl This is a flag used with the creation of the
516 -- chk_row_exists cursor in the TUPS and forces
517 -- the comparison to use NVL.
518 -- p_table_name Name of the table
519 --
520 -- Returns
521 -- It returns a string by putting each element of the table into a newline.
522 -- and sepearting the element assignment by terminator.
523 -- ------------------------------------------------------------------------
524
525 function get_func_asg
526 (
527 p_rpad_spaces in number,
528 p_columns_tbl in t_varchar2_tbl,
529 p_prefix_left_asg in varchar2 default 'p_',
530 p_prefix_right_asg in varchar2 default 'p_',
531 p_omit_business_group_id in varchar2 default 'Y',
532 p_comma_on_first_line in varchar2 default 'Y',
533 p_equality_sign in varchar2 default ' => ',
534 p_pad_first_line in varchar2 default 'Y' ,
535 p_left_asg_pad_len in number default 30,
536 p_right_asg_pad_len in number default 30,
537 p_start_terminator in varchar2 default ',' ,
538 p_end_terminator in varchar2 default null,
539 p_test_with_nvl in varchar2 default 'N',
540 p_table_name in varchar2 default null
541 )
542 return varchar2;
543
544 -- ------------------------- get_func_asg ---------------------------
545 -- Description:
546 -- This function is same as get_func_asg but also except the input
547 -- parameter for pl/sql table which have list of column for resolving
548 -- pk.
549 -- It reads the list columns and returns the parameters list required for
550 -- inserting the data into data pump batch_lines table or any other TUPS
551 -- function
552 -- e.g p_col1 => p_col2,
553 -- p_col2 => p_col2...
554 --
555 -- NOTE: This function is overloaded
556 --
557 -- Input Parameters :
558 -- p_rpad_spaces - Number of blank spaces added before writing the
559 -- element on new line.
560 -- p_columns_tbl - List of the columns or elements which is required
561 -- to be changed into text of parameter assignment.
562 -- It is of pl/sql type t_varchar2_tbl,
563 -- p_prefix_left_asg - Prefix the left element with this value. e.g if
564 -- value is 'p_' then all elements on the left hand
565 -- side of the assignment will be prefixed with 'p_'
566 -- prefixed with p_ and our list output will be
567 -- p_col1 => col1,
568 -- p_col2 => col2,
569 -- p_col3 => col3
570 -- p_prefix_right_asg - Prefix the right element with this value. e.g if
571 -- value is 'p_' then all elements on the right hand
572 -- side of the assignment will be prefixed with 'p_'
573 -- prefixed with p_ and our list output will be
574 -- col1 => p_col1,
575 -- col2 => p_col2,
576 -- col3 => p_col3
577 -- p_omit_business_group_id - It is whether to exclude the
578 -- business_group_id assignment from the list.
579 -- 'Y' - does not include business_group_id column
580 -- for parameter assignment. (default value)
581 -- 'N' - includes business_group_id column for
582 -- parameter assignment.
583 -- p_comma_on_first_line - Put the comma in the first element or not.
584 -- 'Y' - puts the comma before the first element
585 -- parameter assignment.
586 -- 'N' - does not put comma before the first element
587 -- parameter assignment.
588 -- p_equality_sign - By default the equality sign of the parameter
589 -- assignment is ' => '. But it can be '=' for
590 -- update statement set column assignment.
591 -- p_pad_first_line - It means whether the spaces should be added to
592 -- the first element or not.
593 -- 'Y' - spaces are added to the first element
594 -- 'N' - spaces are not added to the first element
595 -- p_left_asg_pad_len - It means the length of the left hand parameter
596 -- after prefix. e.g p_prefix_left is 'p_' and column
597 -- name is 'responsibility_application_id', if the
598 -- length is 30 the the left hand parameter will be
599 -- 'p_responsibility_application_i'.
600 -- p_right_asg_pad_len same as above but applied to right hand side
601 -- parameter.
602 -- parameter.
603 -- p_start_terminator This is put at the begning of the assignment from
604 -- the second element onwards. By default it is ','
605 -- but in some cases it can be 'and' especially use
606 -- by TUPS for generating where clause for composite
607 -- primary keys.
608 -- p_end_terminator This is put at the end of the assignment. It is
609 -- null most of the time and is used by Date Track
610 -- TUPS with composite key to terminate the
611 -- assignment with ';'.
612 -- p_resolve_pk_columns_tbl The column in this pl/sql table should have
613 -- 'l_' as prefix in the right hand side assignment.
614 -- Thay are lookup columns whose value is derived
615 -- from the destination database.
616 -- p_test_with_nvl This is a flag used with the creation of the
617 -- chk_row_exists cursor in the TUPS and forces
618 -- the comparison to use NVL.
619 -- p_table_name Name of the table
620 -- Returns
621 -- It returns a string by putting each element of the table into a newline.
622 -- and sepearting the element assignment by terminator.
623 -- ------------------------------------------------------------------------
624 function get_func_asg
625 (
626 p_rpad_spaces in number,
627 p_columns_tbl in t_varchar2_tbl,
628 p_prefix_left_asg in varchar2 default 'p_',
629 p_prefix_right_asg in varchar2 default 'p_',
630 p_omit_business_group_id in varchar2 default 'Y',
631 p_comma_on_first_line in varchar2 default 'Y',
632 p_equality_sign in varchar2 default ' => ',
633 p_pad_first_line in varchar2 default 'Y' ,
634 p_left_asg_pad_len in number default 30,
635 p_right_asg_pad_len in number default 30,
636 p_start_terminator in varchar2 default ',' ,
637 p_end_terminator in varchar2 default null,
638 p_resolve_pk_columns_tbl in hr_dm_gen_main.t_fk_to_aol_columns_tbl,
639 p_test_with_nvl in varchar2 default 'N',
640 p_table_name in varchar2 default null
641 )
642 return varchar2;
643
644 -- ------------------------- get_func_asg_with_dev_key ---------------------------
645 -- Description:
646 -- This function is same as the get_func_asg but it replaces the column which
647 -- have foreign key to AOL table with the corresponding developer key column
648 -- of the AOL table.
649 -- It reads the list columns and replaces appropriate column with the developer
650 -- key column of the AOL table and returns the parameters list required for
651 -- inserting the data into data pump batch_lines table or any other TUPS
652 -- function
653 -- e.g a table has col1,col2 and col1 has a foreign key on aol table and
654 -- corresponding developer key is col1_dev_key then the output returned is :
655 -- p_col1_dev_key => l_col1_dev_key,
656 -- p_col2 => p_col2...
657 -- Input Parameters :
658 -- p_rpad_spaces - Number of blank spaces added before writing the
659 -- element on new line.
660 -- p_columns_tbl - List of the columns or elements which is required
661 -- to be changed into text of parameter assignment.
662 -- It is of pl/sql type t_varchar2_tbl,
663 -- p_prefix_left_asg - Prefix the left element with this value. e.g if
664 -- value is 'p_' then all elements on the left hand
665 -- side of the assignment will be prefixed with 'p_'
666 -- prefixed with p_ and our list output will be
667 -- p_col1 => col1,
668 -- p_col2 => col2,
669 -- p_col3 => col3
670 -- p_prefix_right_asg - Prefix the right element with this value. e.g if
671 -- value is 'p_' then all elements on the right hand
672 -- side of the assignment will be prefixed with 'p_'
673 -- prefixed with p_ and our list output will be
674 -- col1 => p_col1,
675 -- col2 => p_col2,
676 -- col3 => p_col3
677 -- p_prefix_left_asg_dev_key - same as p_prefix_left_asg defined above but is
678 -- applied only to developer key.
679 -- p_prefix_right_asg_dev_key - same as p_prefix_right_asg defined above but
680 -- is applied only to developer key.
681 -- p_omit_business_group_id - It is whether to exclude the business_group_id
682 -- assignment from the list.
683 -- 'Y' - does not include business_group_id column for
684 -- parameter assignment. (default value)
685 -- 'N' - includes business_group_id column for
686 -- parameter assignment.
687 -- p_comma_on_first_line - Put the comma in the first element or not.
688 --'Y' - puts the comma before the first element
689 -- parameter assignment.
690 --'N' - does not put comma before the first element
691 -- parameter assignment.
692 -- p_equality_sign - By default the equality sign of the parameter
693 -- assignment is ' => '. But it can be '=' for
694 -- update statement set column assignment.
695 -- p_pad_first_line - It means whether the spaces should be added to
696 -- the first element or not.
697 -- 'Y' - spaces are added to the first element
698 -- 'N' - spaces are not added to the first element
699 -- p_left_asg_pad_len - It means the length of the left hand parameter
700 -- after prefix. e.g p_prefix_left is 'p_' and column
701 -- name is 'responsibility_application_id', if the
702 -- length is 30 the the left hand parameter will be
703 -- 'p_responsibility_application_i'.
704 -- p_right_asg_pad_len same as above but applied to right hand side
705 -- parameter.
706 -- p_use_aol_id_col This function is used by TUPS as well as TDS.
707 -- TDS uses the developer key for assignment while
708 -- TUPS uses id value. It can have following values
709 -- 'N' - use id column for assignment
710 -- 'Y' - use deveoper key column for assignment
711 -- p_resolve_pk_columns_tbl The column in this pl/sql table should have
712 -- 'l_' as prefix in the right hand side assignment.
713 --
714 -- Returns
715 -- It returns a string by putting each element of the table into a newline.
716 --
717 -- ------------------------------------------------------------------------
718 function get_func_asg_with_dev_key
719 (
720 p_rpad_spaces in number,
721 p_columns_tbl in t_varchar2_tbl,
722 p_prefix_left_asg in varchar2 default 'p_',
723 p_prefix_right_asg in varchar2 default 'p_',
724 p_prefix_left_asg_dev_key in varchar2 default 'p_',
725 p_prefix_right_asg_dev_key in varchar2 default 'l_',
726 p_omit_business_group_id in varchar2 default 'Y',
727 p_comma_on_first_line in varchar2 default 'Y',
728 p_equality_sign in varchar2 default ' => ',
729 p_pad_first_line in varchar2 default 'Y' ,
730 p_left_asg_pad_len in number default 30,
731 p_right_asg_pad_len in number default 30,
732 p_use_aol_id_col in varchar2,
733 p_fk_to_aol_columns_tbl in hr_dm_gen_main.t_fk_to_aol_columns_tbl,
734 p_resolve_pk_columns_tbl in hr_dm_gen_main.t_fk_to_aol_columns_tbl
735 )
736 return varchar2;
737 -- ------------------------ get_resolved_pk ------------------------------
738 -- Description: This function is used by TUPS.
739 -- Checks whether a row exists for a given source id of the table.
740 -- Input Parameters
741 -- p_source_id - Value of the surrogate primary key of the table in
742 -- source database
743 -- p_table_name - Table name
744 -- Out Parameters
745 -- p_destination_id - Value of the surrogate primary key of the table in
746 -- destination database if different from source database
747 -- ,otherwise it returns the same id value as source.
748 --
749 -- ------------------------------------------------------------------------
750 procedure get_resolved_pk
751 ( p_table_name in varchar2,
752 p_source_id in number,
753 p_destination_id out nocopy number
754 );
755
756 -- ------------------------ ins_resolve_pks ---------------------------------
757 -- Description:
758 -- Insert a row into hr_dm_resolve_pks table. It will be used by TUPS.
759 -- Input Parameters
760 -- p_table_name - Table name
761 -- p_source_id - Value of the first primary key column
762 -- p_destination_id - Value of the second primary key column
763 -- ------------------------------------------------------------------------
764 procedure ins_resolve_pks
765 ( p_table_name in varchar2,
766 p_source_id in number,
767 p_destination_id in number
768 );
769
770 -- ------------------------ ins_dt_delete ---------------------------------
771 -- Description:
772 -- Insert a row into hr_dm_deletes table. It will be used by TUPS. If the
773 -- already exists or for date tracked row on uploading it will store the
774 -- surrogate_id value.
775 -- Input Parameters
776 -- p_id - Value of the surrogate primary key of the table.
777 -- p_table_name - Table name
778 -- p_ins_type - idetifies the type of operation -
779 -- 'D' - for date track. created by the first physical record
780 -- uploaded, so as other physical records belonging to
781 -- the same logical record can avoid the checks.
782 -- 'P' - row already exists.
783 -- p_pk_column_1 - Value of the first primary key column
784 -- p_pk_column_2 - Value of the second primary key column
785 -- p_pk_column_3 - Value of the third primary key column
786 -- p_pk_column_3 - Value of the fourth primary key column
787 -- ------------------------------------------------------------------------
788 procedure ins_dt_delete
789 ( p_id in number default null,
790 p_table_name in varchar2,
791 p_ins_type in varchar2 ,
792 p_pk_column_1 in varchar2 default null,
793 p_pk_column_2 in varchar2 default null,
794 p_pk_column_3 in varchar2 default null,
795 p_pk_column_4 in varchar2 default null
796 );
797
798 -- ------------------------ chk_row_in_dt_delete ----------------------------
799 -- Description: This function is used by Date Track TUPS
800 -- Checks whether a row exists for a given id of the table and type.
801 -- Input Parameters
802 -- p_id - Value of the surrogate primary key of the table.
803 -- p_table_name - Table name
804 -- Out Parameters
805 -- p_ins_type - If a row exists for the table/Id combination then one of
806 -- the following value is returned.
807 -- 'D' - for date track. created by the first physical record
808 -- uploaded, so as other physical records belonging to
809 -- the same logical record can avoid the checks.
810 -- 'P' - row already exists.
811 -- p_row_exists - If a row exists for the table/Id combination then it will
812 -- have 'Y' ,otherwise 'N' value.
813 -- ------------------------------------------------------------------------
814 procedure chk_row_in_dt_delete
815 ( p_id in number,
816 p_table_name in varchar2,
817 p_ins_type out nocopy varchar2,
818 p_row_exists out nocopy varchar2
819 );
820 -- ------------------------ chk_row_in_dt_delete_1_pkcol ----------------------
821 -- Description: This function is used by Date Track table with non surrogate id.
822 -- The priomary key consists of one column
823 -- Checks whether a row exists for a given primary key of the table and type.
824 -- Input Parameters
825 -- p_pk_column_1 - Value of primary key of the table.
826 -- p_table_name - Table name
827 -- Out Parameters
828 -- p_ins_type - If a row exists for the table/Id combination then one of
829 -- the following value is returned.
830 -- 'D' - for date track. created by the first physical record
831 -- uploaded, so as other physical records belonging to
832 -- the same logical record can avoid the checks.
833 -- 'P' - row already exists.
834 -- p_row_exists - If a row exists for the table/Id combination then it will
835 -- have 'Y' ,otherwise 'N' value.
836 -- ---------------------------------------------------------------------------
837 procedure chk_row_in_dt_delete_1_pkcol
838 ( p_pk_column_1 in number,
839 p_table_name in varchar2,
840 p_ins_type out nocopy varchar2,
841 p_row_exists out nocopy varchar2
842 );
843
844 -- ------------------------ chk_row_in_dt_delete_2_pkcol ---------------------
845 -- Description: This function is used by Date Track table with non surrogate
846 -- id.The primary key consists of two columns
847 -- Checks whether a row exists for a given primary key columns of the table and type.
848 -- Input Parameters
849 -- p_pk_column_1 - Value of primary key column 1 of the table.
850 -- p_pk_column_2 - Value of primary key column 2 of the table.
851 -- p_table_name - Table name
852 -- Out Parameters
853 -- p_ins_type - If a row exists for the table/Id combination then one of
854 -- the following value is returned.
855 -- 'D' - for date track. created by the first physical record
856 -- uploaded, so as other physical records belonging to
857 -- the same logical record can avoid the checks.
858 -- 'P' - row already exists.
859 -- p_row_exists - If a row exists for the table/Id combination then it will
860 -- have 'Y' ,otherwise 'N' value.
861 -- ------------------------------------------------------------------------
862 procedure chk_row_in_dt_delete_2_pkcol
863 ( p_pk_column_1 in number,
864 p_pk_column_2 in number,
865 p_table_name in varchar2,
866 p_ins_type out nocopy varchar2,
867 p_row_exists out nocopy varchar2
868 );
869
870 -- ------------------------ chk_row_in_dt_delete_3_pkcol ---------------------
871 -- Description: This function is used by Date Track table with non surrogate
872 -- id. The primary key consists of three columns
873 -- Checks whether a row exists for a given primary key columns of the table
874 -- and type.
875 -- Input Parameters
876 -- p_pk_column_1 - Value of primary key column 1 of the table.
877 -- p_pk_column_2 - Value of primary key column 2 of the table.
878 -- p_pk_column_3 - Value of primary key column 3 of the table.
879 -- p_table_name - Table name
880 -- Out Parameters
881 -- p_ins_type - If a row exists for the table/Id combination then one of
882 -- the following value is returned.
883 -- 'D' - for date track. created by the first physical record
884 -- uploaded, so as other physical records belonging to
885 -- the same logical record can avoid the checks.
886 -- 'P' - row already exists.
887 -- p_row_exists - If a row exists for the table/Id combination then it will
888 -- have 'Y' ,otherwise 'N' value.
889 -- ------------------------------------------------------------------------
890 procedure chk_row_in_dt_delete_3_pkcol
891 ( p_pk_column_1 in number,
892 p_pk_column_2 in number,
893 p_pk_column_3 in number,
894 p_table_name in varchar2,
895 p_ins_type out nocopy varchar2,
896 p_row_exists out nocopy varchar2
897 );
898
899 -- ------------------------ chk_row_in_dt_delete_4_pkcol ---------------------
900 -- Description: This function is used by Date Track table with non surrogate
901 -- id. The primary key consists of four columns
902 -- Checks whether a row exists for a given primary key columns of the table and type.
903 -- Input Parameters
904 -- p_pk_column_1 - Value of primary key column 1 of the table.
905 -- p_pk_column_2 - Value of primary key column 2 of the table.
906 -- p_pk_column_3 - Value of primary key column 3 of the table.
907 -- p_pk_column_4 - Value of primary key column 4 of the table.
908 -- p_table_name - Table name
909 -- Out Parameters
910 -- p_ins_type - If a row exists for the table/Id combination then one of
911 -- the following value is returned.
912 -- 'D' - for date track. created by the first physical record
913 -- uploaded, so as other physical records belonging to
914 -- the same logical record can avoid the checks.
915 -- 'P' - row already exists.
916 -- p_row_exists - If a row exists for the table/Id combination then it will
917 -- have 'Y' ,otherwise 'N' value.
918 -- ------------------------------------------------------------------------
919 procedure chk_row_in_dt_delete_4_pkcol
920 ( p_pk_column_1 in number,
921 p_pk_column_2 in number,
922 p_pk_column_3 in number,
923 p_pk_column_4 in number,
924 p_table_name in varchar2,
925 p_ins_type out nocopy varchar2,
926 p_row_exists out nocopy varchar2
927 );
928 -- ------------------------ run_sql ---------------------------------------
929 -- Description:
930 -- Runs a SQL statement using the dbms_sql package. No bind variables
931 -- allowed. The SQL command is passed to this procedure as a atrring of
932 -- varchar2.
933 --
934 -- ------------------------------------------------------------------------
935 procedure run_sql( p_sql in varchar2 );
936 -- ------------------------ run_sql ---------------------------------------
937 -- Description:
938 -- Runs a SQL statement using the dbms_sql package. No bind variables
939 -- allowed. This procedure uses pl/sql table of varchar2 as an input
940 -- and hence is suitable to compile very large packages i.e more than
941 -- 32767 char.
942 -- ------------------------------------------------------------------------
943 procedure run_sql(p_package_body dbms_sql.varchar2s,
944 p_package_index number );
945
946 -- ------------------------ check_compile ---------------------------------
947 -- Description:
948 -- Checks whether or not the generated package or view compiled okay.
949 -- ------------------------------------------------------------------------
950 procedure check_compile
951 (
952 p_object_name in varchar2,
953 p_object_type in varchar2
954 );
955
956 -- ------------------------- get_data_type ------------------------------
957 -- Description:
958 -- It gets the data type for a given column of the table.
959 -- Input Parameters :
960 -- p_table_name - Name of the table
961 -- p_column_name - Name of the column.
962 -- Output Parameters
963 -- p_data_type - It returns the data type of the column.
964 --e.g number or date or varchar2.
965 --
966 --
967 -- ------------------------------------------------------------------------
968 procedure get_data_type
969 (
970 p_table_name in varchar2,
971 p_column_name in varchar2,
972 p_data_type out nocopy varchar2
973 );
974
975 -- ------------------------- create_view ----------------------
976 -- Description: Creates a view based on the passed table, taking
977 -- into account PC hierarchy.
978 --
979 --
980 -- Input Parameters
981 -- p_view_name - name of view to create
982 --
983 -- p_table_name - source table name
984 --
985 --
986 -- Output Parameters
987 -- <none>
988 --
989 --
990 -- ------------------------------------------------------------------------
991
992 procedure create_view
993 (
994 p_table_info in hr_dm_gen_main.t_table_info
995 );
996
997
998 -- ------------------------- create_stub_views ------------------------
999 -- Description: Creates dummy views for the hr_dmv type 'tables' to avoid
1000 -- compilation errors during the generate phase, when the correct form of
1001 -- the views will be created.
1002 --
1003 --
1004 -- Input Parameters
1005 -- p_migration_id - current migration
1006 --
1007 --
1008 --
1009 -- Output Parameters
1010 -- <none>
1011 --
1012 --
1013 -- ------------------------------------------------------------------------
1014
1015 procedure create_stub_views(p_migration_id in number);
1016
1017
1018 end hr_dm_library;
[Home] [Help]