[Home] [Help]
PACKAGE BODY: APPS.HR_PUMP_META_MAPPER
Source
1 package body hr_pump_meta_mapper as
2 /* $Header: hrpumpmm.pkb 120.4.12010000.1 2008/07/28 03:43:20 appldev ship $ */
3
4 ---------------------------------------
5 -- ERROR MESSAGING AND TRACING STUFF --
6 ---------------------------------------
7 g_debug boolean;
8
9 --------------------------------
10 -- DATA STRUCTURE DEFINITIONS --
11 --------------------------------
12
13 --
14 -- Major parameter information structure.
15 --
16 type t_parameter is record
17 (
18 seqno number,
19 api_seqno number,
20 batch_lines_seqno number,
21 batch_lines_column varchar2(30),
22 parameter_name varchar2(30),
23 datatype number,
24 in_out number,
25 defaultable boolean,
26 default_value varchar2(256),
27 call_default_value varchar2(256),
28 mapping_type hr_pump_module_parameters.mapping_type%type,
29 mapping_definition hr_pump_module_parameters.mapping_definition%type
30 );
31
32 type t_parameter_tbl is table of t_parameter index by binary_integer;
33
34 -- Cache parameter mappings for use by Pump Station
35 g_last_api_id number := -1;
36 g_last_column_count number := 0;
37 g_params t_parameter_tbl;
38 --
39 -- Parameter seed data structure.
40 --
41 type t_seed_parameter is record
42 (
43 parameter_name hr_pump_module_parameters.api_parameter_name%type,
44 mapping_type hr_pump_module_parameters.mapping_type%type,
45 mapping_definition hr_pump_module_parameters.mapping_definition%type,
46 default_value hr_pump_module_parameters.default_value%type,
47 matched boolean
48 );
49
50 type t_seed_parameter_tbl is table of t_seed_parameter index by binary_integer;
51
52 --
53 -- Structure to store parameter counts for code generation.
54 --
55 type t_parameter_counts is record
56 (
57 total_parameters number, -- Total number of parameters.
58 api_parameters number, -- Parameters in API call.
59 batch_lines_parameters number, -- Parameters required in batch lines table.
60 seed_parameters number, -- Parameters for which data is seeded.
61 functions number, -- Functions for 'FUNCTION' mapping_type.
62 distinct_parameters number, -- Distinct parameters for above functions.
63 call_parameters number, -- Total parameters in all function calls.
64 long_parameters number -- Number of long parameters.
65 );
66
67 --
68 -- Structure for 'FUNCTION' mapping_type function information.
69 --
70 type t_function is record
71 (
72 --
73 -- If the ALWAYS_CALL flag is false, the function is only called if
74 -- all its parameters are NOT NULL and not defaulted with HR_API
75 -- default values.
76 --
77 always_call boolean default false,
78 package_name hr_pump_module_parameters.mapping_definition%type,
79 function_name hr_pump_module_parameters.mapping_definition%type,
80 ret_type number, -- Return type.
81 seqno number, -- Sequence in parameter list.
82 index1 number, -- Start index in function call list.
83 index2 number -- End index in function call list.
84 );
85
86 type t_function_tbl is table of t_function index by binary_integer;
87
88 type t_function_parameter is record
89 (
90 mapping_function varchar2(30) default null,
91 parameter_name varchar2(30),
92 function_parameter varchar2(30),
93 datatype number,
94 batch_lines_column varchar2(30),
95 defaultable boolean,
96 default_value varchar2(256)
97 );
98
99 --
100 -- List of function parameters information.
101 --
102 type t_function_parameter_tbl is table of t_function_parameter index by
103 binary_integer;
104
105 --
106 -- List of mapping function packages.
107 --
108 type t_mapping_package is record
109 (
110 --
111 -- Use hr_pump_module_parameters.mapping_definition%type as it's larger
112 -- than hr_pump_mapping_packages.mapping_package%type, and it can be
113 -- the source of a package name.
114 --
115 mapping_package hr_pump_module_parameters.mapping_definition%type
116 );
117
118 type t_mapping_package_tbl is table of t_mapping_package index by
119 binary_integer;
120
121 --------------------------
122 -- CONSTANT DEFINITIONS --
123 --------------------------
124 c_newline constant varchar(1) default '
125 ';
126 --
127 -- dbms_describe values for different data types.
128 --
129 c_dtype_undefined constant number default 0;
130 c_dtype_varchar2 constant number default 1;
131 c_dtype_number constant number default 2;
132 c_dtype_binary_integer constant number default 3;
133 c_dtype_long constant number default 8;
134 c_dtype_date constant number default 12;
135 c_dtype_boolean constant number default 252;
136 --
137 -- dbms_describe values for different parameter passing modes.
138 --
139 c_ptype_in constant number default 0;
140 c_ptype_out constant number default 1;
141 c_ptype_in_out constant number default 2;
142 --
143 -- Defaulting style.
144 --
145 c_default_null constant number default 0;
146 c_default_hr_api constant number default 1;
147 --
148 -- Batch lines column information.
149 --
150 c_max_batch_lines_cols constant number default 230;
151 c_parameter_value_col constant varchar2(30) default 'pval';
152 c_cursor_value_col constant varchar2(30) default 'p';
153 c_def_check_col constant varchar2(30) default 'd';
154 c_long_value_col constant varchar2(30) default 'plongval';
155 --
156 -- Parameter mapping types.
157 --
158 c_mapping_type_aliased constant varchar2(30) default 'ALIASED';
159 c_mapping_type_function constant varchar2(30) default 'FUNCTION';
160 c_mapping_type_lookup constant varchar2(30) default 'LOOKUP';
161 c_mapping_type_normal constant varchar2(30) default 'NORMAL';
162 c_mapping_type_user_key constant varchar2(30) default 'USER_KEY';
163 --
164 -- Package containing mapping functions.
165 --
166 c_get_function_package constant varchar2(30) default 'hr_pump_get';
167 --
168 -- Format for date parameters.
169 --
170 c_date_format constant varchar2(10) default 'YYYY/MM/DD';
171 c_date_format_len constant number default 10;
172 c_date_format1 constant varchar2(30) default 'YYYY/MM/DD HH24:MI:SS';
173 c_date_format1_len constant number default 19;
174
175 -- Signed date formats e.g. for hr_api.g_date.
176 c_date_format2 constant varchar2(30) default 'SYYYY/MM/DD';
177 c_date_format2_len constant number default 11;
178 c_date_format3 constant varchar2(30) default 'SYYYY/MM/DD HH24:MI:SS';
179 c_date_format3_len constant number default 20;
180
181 ---------------------------
182 -- Global PL/SQL tables. --
183 ---------------------------
184
185 type t_tbl_varchar2 is table of varchar2(32) index by binary_integer;
186 --
187 -- Tables to assist in code generation.
188 --
189 g_tbl_default_null t_tbl_varchar2;
190 g_tbl_default_hr_api t_tbl_varchar2;
191 g_tbl_datatype t_tbl_varchar2;
192
193 --
194 -- Tables to allow the use of shortened default values in the call
195 -- procedure.
196 --
197 g_tbl_call_default_null t_tbl_varchar2;
198 g_tbl_call_default_hr_api t_tbl_varchar2;
199
200 --
201 -- Generation Mode Flag.
202 --
203 g_standard_generate boolean;
204
205 ----------------------
206 -- ERROR EXCEPTIONS --
207 ----------------------
208
209 --
210 -- Error exceptions that may be raised by hr_general.describe_procedure.
211 --
212 package_not_exists exception;
213 pragma exception_init(package_not_exists, -6564);
214 --
215 proc_not_in_package exception;
216 pragma exception_init(proc_not_in_package, -20001);
217 --
218 remote_object exception;
219 pragma exception_init(remote_object, -20002);
220 --
221 invalid_package exception;
222 pragma exception_init(invalid_package, -20003);
223 --
224 invalid_object_name exception;
225 pragma exception_init(invalid_object_name, -20004);
226 --
227 -- Exception for generated text exceeding the maximum allowable buffer size.
228 --
229 plsql_value_error exception;
230 pragma exception_init(plsql_value_error, -6502);
231
232 ---------------------------------------------------------------------------
233 -- COMMON CODE --
234 ---------------------------------------------------------------------------
235 -- ------------------------- output_init ---------------------------------
236 -- Description:
237 -- Wrapper procedure for initialising text output mechanism.
238 -- ------------------------------------------------------------------------
239 procedure output_init
240 is
241 begin
242 --
243 -- Set dbms_output buffer to its maximum size.
244 --
245 dbms_output.enable( 1000000 );
246 end output_init;
247
248 -- ------------------------- output_text ----------------------------------
249 -- Description:
250 -- Wrapper procedure for text output.
251 -- ------------------------------------------------------------------------
252 procedure output_text
253 (
254 p_text in varchar2
255 )
256 is
257 begin
258 dbms_output.put_line( p_text );
259 end output_text;
260
261 -- ----------------------- special_parameter -----------------------------
262 -- Description:
263 -- Returns true if the parameter_name is the name of a parameter whose
264 -- value will not be supplied from the batch lines table or a mapping e.g.
265 -- p_validate is always set to false.
266 -- ------------------------------------------------------------------------
267 function special_parameter
268 (
269 p_parameter_name in varchar2
270 )
271 return boolean is
272 l_special boolean;
273 begin
274 l_special := upper( p_parameter_name ) in
275 ( 'P_VALIDATE', 'P_BUSINESS_GROUP_ID',
276 'P_DATA_PUMP_ALWAYS_CALL' );
277 return l_special;
278 end special_parameter;
279
280 -- --------------------- check_special_parameter --------------------------
281 -- Description:
282 -- Checks if p_function_parameter is consistent with the special parameter
283 -- with the same name.
284 -- ------------------------------------------------------------------------
285 procedure check_special_parameter
286 (
287 p_function_parameter in t_function_parameter
288 )
289 is
290 begin
291 if p_function_parameter.parameter_name = 'P_BUSINESS_GROUP_ID' then
292 if p_function_parameter.datatype <> c_dtype_number and
293 p_function_parameter.datatype <> c_dtype_binary_integer
294 then
295 hr_utility.set_message( 800, 'HR_50320_DP_FUN_ARG_USAGE' );
296 hr_utility.set_message_token( 'ARGUMENT', 'P_BUSINESS_GROUP_ID' );
297 hr_utility.raise_error;
298 end if;
299 elsif p_function_parameter.parameter_name = 'P_VALIDATE' then
300 if p_function_parameter.datatype <> c_dtype_boolean then
301 hr_utility.set_message( 800, 'HR_50320_DP_FUN_ARG_USAGE' );
302 hr_utility.set_message_token( 'ARGUMENT', 'P_VALIDATE' );
303 hr_utility.raise_error;
304 end if;
305 elsif p_function_parameter.parameter_name = 'P_DATA_PUMP_ALWAYS_CALL'
306 then
307 if p_function_parameter.datatype <> c_dtype_varchar2 then
308 hr_utility.set_message( 800, 'HR_50320_DP_FUN_ARG_USAGE' );
309 hr_utility.set_message_token( 'ARGUMENT', 'P_DATA_PUMP_ALWAYS_CALL' );
310 hr_utility.raise_error;
311 end if;
312 end if;
313 end check_special_parameter;
314
315 -- ----------------------- match_seed_parameter ---------------------------
316 -- Description:
317 -- Match parameter name against the seeded data set up by get_seed_parameters.
318 -- If a match is made, the matched flag for the matched record is set to
319 -- true, p_match is set to true, and the record is returned. If there is no,
320 -- match then p_match is set to false.
321 -- ------------------------------------------------------------------------
322 procedure match_seed_parameter
323 (
324 p_parameter_name in varchar2,
325 p_no_seed_parameters in number,
326 p_seed_parameter_tbl in out nocopy t_seed_parameter_tbl,
327 p_seed_parameter out nocopy t_seed_parameter,
328 p_match out nocopy boolean
329 )
330 is
331 begin
332 for i in 1 .. p_no_seed_parameters loop
333 --
334 -- For user_key parameters, the actual parameter name is in the
335 -- mapping_definition.
336 --
337 if (p_seed_parameter_tbl(i).parameter_name = p_parameter_name) or
338 (p_seed_parameter_tbl(i).mapping_type = c_mapping_type_user_key and
339 p_seed_parameter_tbl(i).mapping_definition = p_parameter_name)
340 then
341 p_seed_parameter_tbl(i).matched := true;
342 p_seed_parameter := p_seed_parameter_tbl(i);
343 p_match := true;
344 return;
345 end if;
346 end loop;
347 p_match := false;
348 end match_seed_parameter;
349
350 -- ------------------------ get_seed_parameters ---------------------------
351 -- Description:
352 -- Fetches the seeded parameter information from HR_PUMP_MODULE_PARAMETERS.
353 -- ------------------------------------------------------------------------
354 procedure get_seed_parameters( p_module_package in varchar2,
355 p_module_name in varchar2,
356 p_seed_parameter_tbl out nocopy t_seed_parameter_tbl,
357 p_no_seed_parameters out nocopy number )
358 is
359 cursor csr_seed_parameters( p_module_package in varchar2,
360 p_module_name in varchar2 ) is
361 select upper(hrpmp.api_parameter_name),
362 upper(hrpmp.mapping_type),
363 upper(hrpmp.mapping_definition),
364 hrpmp.default_value
365 from hr_api_modules ham,
366 hr_pump_module_parameters hrpmp
367 where upper(ham.module_package) = upper(p_module_package)
368 and upper(ham.module_name) = upper(p_module_name)
369 and upper(ham.api_module_type) in ( 'AI', 'BP','DM' )
370 and upper(hrpmp.module_name) = upper(p_module_name)
371 and upper(hrpmp.api_module_type) = upper(ham.api_module_type);
372 --
373 l_seqno number;
374 --
375 l_parameter_name hr_pump_module_parameters.api_parameter_name%type;
376 l_mapping_type hr_pump_module_parameters.mapping_type%type;
377 l_mapping_definition hr_pump_module_parameters.mapping_definition%type;
378 l_default_value hr_pump_module_parameters.default_value%type;
379 begin
380 l_seqno := 0;
381 open csr_seed_parameters( p_module_package, p_module_name );
382 loop
383 fetch csr_seed_parameters into l_parameter_name,
384 l_mapping_type,
385 l_mapping_definition,
386 l_default_value;
387 exit when csr_seed_parameters%notfound;
388 --
389 -- Check that special parameters such as p_business_group_id don't
390 -- appear in the seeded data.
391 --
392 if special_parameter( l_parameter_name ) or
393 ( ( l_mapping_type = c_mapping_type_aliased or
394 l_mapping_type = c_mapping_type_user_key ) and
395 special_parameter( l_mapping_definition ) )
396 then
397 hr_utility.set_message( 800, 'HR_50328_DP_SEED_SPECIAL_ARG' );
398 hr_utility.set_message_token( 'ARGUMENT', l_parameter_name );
399 hr_utility.raise_error;
400 end if;
401
402 l_seqno := l_seqno + 1;
403 p_seed_parameter_tbl(l_seqno).parameter_name := l_parameter_name;
404 p_seed_parameter_tbl(l_seqno).mapping_type := l_mapping_type;
405 p_seed_parameter_tbl(l_seqno).mapping_definition := l_mapping_definition;
406 p_seed_parameter_tbl(l_seqno).default_value := l_default_value;
407 p_seed_parameter_tbl(l_seqno).matched := false;
408 end loop;
409 close csr_seed_parameters;
410 p_no_seed_parameters := l_seqno;
411 exception
412 when others then
413 p_no_seed_parameters := 0;
414 if csr_seed_parameters%isopen then
415 close csr_seed_parameters;
416 end if;
417 raise;
418 end get_seed_parameters;
419
420 -- ------------------------ get_mapping_packages --------------------------
421 -- Description:
422 -- Fetches the mapping function package information from
423 -- HR_PUMP_MAPPING_PACKAGES.
424 -- ------------------------------------------------------------------------
425 procedure get_mapping_packages
426 (
427 p_module_package in varchar2,
428 p_module_name in varchar2,
429 p_mapping_package_tbl out nocopy t_mapping_package_tbl
430 )
431 is
432 cursor csr_mapping_packages
433 (
434 p_module_package in varchar2,
435 p_module_name in varchar2
436 ) is
437 select mp.mapping_package
438 from hr_pump_mapping_packages mp,
439 hr_api_modules am
440 where (mp.module_name is not null and
441 upper(mp.module_name) = upper(p_module_name) and
442 upper(am.module_name) = upper(p_module_name) and
443 upper(am.api_module_type) = upper(mp.api_module_type))
444 or (mp.module_package is not null and
445 upper(mp.module_package) = upper(p_module_package))
446 or (mp.module_package is null and mp.module_name is null)
447 order by checking_order asc;
448 --
449 l_seqno number;
450 l_mapping_package hr_pump_mapping_packages.mapping_package%type;
451 begin
452 l_seqno := 1;
453 open csr_mapping_packages( p_module_package, p_module_name );
454 loop
455 fetch csr_mapping_packages into l_mapping_package;
456 exit when csr_mapping_packages%notfound;
457 p_mapping_package_tbl(l_seqno).mapping_package := l_mapping_package;
458 l_seqno := l_seqno + 1;
459 end loop;
460 close csr_mapping_packages;
461 --
462 -- Add the default package onto the end of the list of packages.
463 --
464 p_mapping_package_tbl(l_seqno).mapping_package := c_get_function_package;
465 exception
466 when others then
467 p_mapping_package_tbl.delete;
468 if csr_mapping_packages%isopen then
469 close csr_mapping_packages;
470 end if;
471 raise;
472 end get_mapping_packages;
473
474 -- ---------------------------- get_latest_api ----------------------------
475 -- Description:
476 -- Uses the results of the hr_general.describe_procedure call to get
477 -- the latest API overload version. By "latest" we mean the overload with
478 -- the most mandatory parameters as this is the normal case with the
479 -- HRMS API strategy. Of course, the HRMS API strategy will allow APIs
480 -- to be overloaded to remove parameters also in which case the above
481 -- method is incorrect. If more than one interface has a given number
482 -- of mandatory parameters then an arbitrary choice is made. The above
483 -- algorithm was agreed by Peter Attwood (HRMS API strategy).
484 -- ------------------------------------------------------------------------
485 procedure get_latest_api
486 (
487 p_overload in out nocopy dbms_describe.number_table,
488 p_position in out nocopy dbms_describe.number_table,
489 p_level in out nocopy dbms_describe.number_table,
490 p_argument_name in out nocopy dbms_describe.varchar2_table,
491 p_datatype in out nocopy dbms_describe.number_table,
492 p_default_value in out nocopy dbms_describe.number_table,
493 p_in_out in out nocopy dbms_describe.number_table,
494 p_length in out nocopy dbms_describe.number_table,
495 p_precision in out nocopy dbms_describe.number_table,
496 p_scale in out nocopy dbms_describe.number_table,
497 p_radix in out nocopy dbms_describe.number_table,
498 p_spare in out nocopy dbms_describe.number_table,
499 p_apis out nocopy boolean,
500 p_parameters out nocopy boolean
501 ) is
502 i binary_integer;
503 j binary_integer;
504 l_chosen_overload number;
505 l_max_mandatory_args number;
506 l_distinct_overloads dbms_describe.number_table;
507 begin
508 if g_debug then
509 hr_utility.trace('Entered get_latest_api.');
510 end if;
511
512 --
513 -- Want the latest overload for a procedure i.e. the maximum overload
514 -- value and no p_position(i) = 0 entry.
515 --
516 i := p_overload.first;
517 loop
518 exit when not p_position.exists(i);
519
520 --
521 -- Always mark function or procedure with zero arguments in
522 -- distinct overload table.
523 --
524 if p_position(i) = 0 or p_datatype(i) = c_dtype_undefined then
525 l_distinct_overloads( p_overload(i) ) := -1;
526
527 if g_debug then
528 if p_position(i) = 0 then
529 hr_utility.trace
530 ('Unsuitable overload ' || p_overload(i) || ' is a function.');
531 else
532 hr_utility.trace
533 ('Unsuitable overload ' || p_overload(i) || ' has no parameters');
534 end if;
535 end if;
536
537 --
538 -- Speculatively mark a possible procedure in the distinct overload
539 -- table.
540 --
541 elsif not l_distinct_overloads.exists( p_overload(i) ) then
542 l_distinct_overloads( p_overload(i) ) := 0;
543
544 if g_debug then
545 hr_utility.trace
546 ('Overload ' || p_overload(i) || ' is a candidate API.');
547 end if;
548 end if;
549
550 i := p_overload.next(i);
551 end loop;
552
553 --
554 -- Delete the unsuitable overloads from the distinct overload
555 -- list.
556 --
557 i := l_distinct_overloads.first;
558 loop
559 exit when not l_distinct_overloads.exists(i);
560 j := l_distinct_overloads.next(i);
561
562 if l_distinct_overloads(i) = -1 then
563 l_distinct_overloads.delete(i);
564 end if;
565
566 i := j;
567 end loop;
568
569 --
570 -- No procedures found hence not an API by definition.
571 --
572 if l_distinct_overloads.count = 0 then
573 p_parameters := false;
574 p_apis := false;
575
576 if g_debug then
577 hr_utility.trace('No candidate API procedures found.');
578 end if;
579
580 return;
581 else
582 p_parameters := true;
583 p_apis := true;
584 end if;
585
586 --
587 -- Count the mandatory parameters for each overload.
588 --
589 i := p_default_value.first;
590 loop
591 exit when not p_default_value.exists(i);
592
593 if p_default_value(i) = 0 and
594 l_distinct_overloads.exists( p_overload(i) ) then
595 l_distinct_overloads( p_overload(i) ) :=
596 l_distinct_overloads( p_overload(i) ) + 1;
597 end if;
598
599 i := p_default_value.next(i);
600 end loop;
601
602 --
603 -- Now get the overload with the most parameters.
604 --
605 i := l_distinct_overloads.first;
606 l_chosen_overload := i;
607 l_max_mandatory_args := l_distinct_overloads(i);
608 loop
609
610 if g_debug then
611 hr_utility.trace
612 ('Overload: ' || i || ' Mandatory Args: ' || l_distinct_overloads(i)
613 );
614 end if;
615
616 i := l_distinct_overloads.next(i);
617
618 exit when not l_distinct_overloads.exists(i);
619
620 if l_distinct_overloads(i) >= l_max_mandatory_args then
621 l_chosen_overload := i;
622 l_max_mandatory_args := l_distinct_overloads(i);
623 end if;
624
625 end loop;
626
627 if g_debug then
628 hr_utility.trace('Selected overload: ' || l_chosen_overload);
629 end if;
630
631 --
632 -- Delete information that does not belong to the selected overload
633 -- version.
634 --
635 i := p_position.first;
636 loop
637 --
638 -- Exit the loop if nothing to get.
639 --
640 exit when not p_position.exists(i);
641 j := p_position.next(i);
642
643 if p_overload(i) <> l_chosen_overload then
644 p_overload.delete(i);
645 p_position.delete(i);
646 p_level.delete(i);
647 p_argument_name.delete(i);
648 p_datatype.delete(i);
649 p_default_value.delete(i);
650 p_in_out.delete(i);
651 p_length.delete(i);
652 p_precision.delete(i);
653 p_scale.delete(i);
654 p_radix.delete(i);
655 p_spare.delete(i);
656 end if;
657
658 i := j;
659 end loop;
660
661 if g_debug then
662 hr_utility.trace('get_latest_api: deleted unwanted overloads');
663 end if;
664 end get_latest_api;
665
666 -- -------------------------- describe_api --------------------------------
667 -- Description:
668 -- Calls hr_general.describe_procedure on the API specified by
669 -- p_module_package || '.' || p_module_name, and returns an initial
670 -- t_parameter_tbl and a count of the API parameters.
671 -- ------------------------------------------------------------------------
672 procedure describe_api
673 (
674 p_module_package in varchar2,
675 p_module_name in varchar2,
676 p_parameter_tbl out nocopy t_parameter_tbl,
677 p_parameter_counts out nocopy t_parameter_counts
678 )
679 is
680 -- dbms_describe parameters.
681 l_overload dbms_describe.number_table;
682 l_position dbms_describe.number_table;
683 l_level dbms_describe.number_table;
684 l_argument_name dbms_describe.varchar2_table;
685 l_datatype dbms_describe.number_table;
686 l_default_value dbms_describe.number_table;
687 l_in_out dbms_describe.number_table;
688 l_length dbms_describe.number_table;
689 l_precision dbms_describe.number_table;
690 l_scale dbms_describe.number_table;
691 l_radix dbms_describe.number_table;
692 l_spare dbms_describe.number_table;
693 --
694 l_first number;
695 l_last number;
696 l_seqno number; -- Sequence in dbms_describle list.
697 i number;
698 --
699 l_parameters boolean;
700 l_apis boolean;
701 --
702 begin
703 if g_debug then
704 hr_utility.trace('Entered describe_api.');
705 end if;
706
707 begin
708 hr_general.describe_procedure
709 ( object_name => p_module_package || '.' || p_module_name,
710 reserved1 => null,
711 reserved2 => null,
712 overload => l_overload,
713 position => l_position,
714 level => l_level,
715 argument_name => l_argument_name,
716 datatype => l_datatype,
717 default_value => l_default_value,
718 in_out => l_in_out,
719 length => l_length,
720 precision => l_precision,
721 scale => l_scale,
722 radix => l_radix,
723 spare => l_spare );
724 exception
725 when package_not_exists or proc_not_in_package or invalid_object_name then
726 hr_utility.set_message( 800, 'HR_50305_DP_NO_SUCH_API' );
727 hr_utility.set_message_token( 'PACKAGE', p_module_package );
728 hr_utility.set_message_token( 'MODULE', p_module_name );
729 hr_utility.raise_error;
730 when remote_object then
731 hr_utility.set_message( 800, 'HR_50307_DP_REMOTE_OBJECT' );
732 hr_utility.set_message_token
733 ( 'OBJECT', p_module_package || '.' || p_module_name );
734 hr_utility.raise_error;
735 when invalid_package then
736 hr_utility.set_message( 800, 'HR_50306_DP_INVALID_PKG' );
737 hr_utility.set_message_token( 'PACKAGE', p_module_package );
738 hr_utility.raise_error;
739 end;
740
741 if g_debug then
742 hr_utility.trace('Successful dbms_describe.');
743 end if;
744
745 --
746 -- Get the latest overloaded version of the API.
747 --
748 get_latest_api
749 (p_overload => l_overload
750 ,p_position => l_position
751 ,p_level => l_level
752 ,p_argument_name => l_argument_name
753 ,p_datatype => l_datatype
754 ,p_default_value => l_default_value
755 ,p_in_out => l_in_out
756 ,p_length => l_length
757 ,p_precision => l_precision
758 ,p_scale => l_scale
759 ,p_radix => l_radix
760 ,p_spare => l_spare
761 ,p_apis => l_apis
762 ,p_parameters => l_parameters
763 );
764 --
765 -- Check that an API with parameters was found.
766 --
767 if not l_parameters and not l_apis then
768 hr_utility.set_message( 800, 'HR_50308_DP_NOT_AN_API' );
769 hr_utility.set_message_token
770 ( 'OBJECT', p_module_package || '.' || p_module_name );
771 hr_utility.raise_error;
772 end if;
773
774 --
775 -- Process the parameters.
776 --
777 p_parameter_counts.long_parameters := 0;
778 l_seqno := l_position.first;
779 loop
780 --
781 -- Exit when no more parameters to get.
782 --
783 exit when not l_position.exists(l_seqno);
784 --
785 i := l_position(l_seqno);
786
787 if g_debug then
788 hr_utility.trace
789 ('Parameter: ' || l_argument_name(l_seqno) || ' Call Position: ' || i ||
790 ' Table Position: ' || l_seqno
791 );
792 end if;
793
794 --
795 -- Check that parameter names start with 'p_'.
796 --
797 if substr(lower(l_argument_name(l_seqno)), 1, 2) <> 'p_' then
798 hr_utility.set_message( 800, 'HR_50310_DP_BAD_API_ARG_NAME');
799 hr_utility.set_message_token( 'ARGUMENT', l_argument_name(l_seqno));
800 hr_utility.raise_error;
801 end if;
802
803 --
804 -- Check that the data type is supported.
805 --
806 if l_datatype(l_seqno) <> c_dtype_varchar2 and
807 l_datatype(l_seqno) <> c_dtype_number and
808 l_datatype(l_seqno) <> c_dtype_date and
809 l_datatype(l_seqno) <> c_dtype_boolean and
810 l_datatype(l_seqno) <> c_dtype_long
811 then
812 hr_utility.set_message( 800, 'HR_50311_DP_BAD_API_ARG_TYPE' );
813 hr_utility.set_message_token( 'ARGUMENT', l_argument_name(l_seqno) );
814 hr_utility.set_message_token( 'TYPE', l_datatype(l_seqno) );
815 hr_utility.raise_error;
816 end if;
817
818 --
819 -- Set up the long parameter count.
820 --
821 if l_datatype(l_seqno) = c_dtype_long then
822 p_parameter_counts.long_parameters :=
823 p_parameter_counts.long_parameters + 1;
824 if p_parameter_counts.long_parameters > 1 then
825 hr_utility.set_message( 800, 'HR_50016_DP_TOO_MANY_LONGS' );
826 hr_utility.set_message_token
827 ( 'API', p_module_package || '.' || p_module_name );
828 hr_utility.set_message_token( 'ARGUMENT', l_argument_name(l_seqno) );
829 hr_utility.set_message_token( 'FUNCTION', p_module_name );
830 hr_utility.raise_error;
831 end if;
832 end if;
833
834 --
835 -- Set up the rest of the parameter information.
836 --
837 p_parameter_tbl(i).seqno := i;
838 p_parameter_tbl(i).api_seqno := i;
839 p_parameter_tbl(i).in_out := l_in_out( l_seqno );
840 p_parameter_tbl(i).parameter_name :=
841 upper(l_argument_name( l_seqno ));
842 p_parameter_tbl(i).datatype := l_datatype( l_seqno );
843 if l_default_value(l_seqno) = 1 then
844 p_parameter_tbl(i).defaultable := true;
845 else
846 p_parameter_tbl(i).defaultable := false;
847 end if;
848
849 -- Initialise the remaining fields.
850 p_parameter_tbl(i).default_value := null;
851 p_parameter_tbl(i).mapping_type := null;
852 p_parameter_tbl(i).mapping_definition := null;
853
854 l_seqno := l_position.next( l_seqno );
855 end loop;
856
857 --
858 -- Set up counts of API and total parameters.
859 --
860 p_parameter_counts.api_parameters := p_parameter_tbl.count;
861 p_parameter_counts.total_parameters := p_parameter_tbl.count;
862 end describe_api;
863
864 -- ---------------------- get_default_value -------------------------------
865 -- Description:
866 -- Gets the default value for the combination of p_defaulting_style and
867 -- p_datatype.
868 -- ------------------------------------------------------------------------
869 function get_default_value
870 (
871 p_defaulting_style in number,
872 p_datatype in number,
873 p_mapping_type in varchar2
874 )
875 return varchar2 is
876 l_default_value varchar2(64) := 'null';
877 begin
878 --
879 -- User-keys are VARCHAR2 and default NULL.
880 --
881 if p_mapping_type = c_mapping_type_user_key then
882 l_default_value := g_tbl_default_null(c_dtype_varchar2);
883 --
884 -- Set defaults according to defaulting style.
885 --
886 elsif p_defaulting_style = c_default_null then
887 l_default_value := g_tbl_default_null(p_datatype);
888 elsif p_defaulting_style = c_default_hr_api then
889 l_default_value := g_tbl_default_hr_api(p_datatype);
890 end if;
891 return l_default_value;
892 end get_default_value;
893
894 -- ---------------------- get_call_default_value --------------------------
895 -- Description:
896 -- Gets the default value for call procedure. This is to allow shorter
897 -- code to be generated.
898 -- ------------------------------------------------------------------------
899 function get_call_default_value
900 (
901 p_default_value in varchar2,
902 p_datatype in number,
903 p_mapping_type in varchar2
904 )
905 return varchar2 is
906 l_call_default_value varchar2(64) := p_default_value;
907 begin
908 --
909 -- User-keys are VARCHAR2 and default NULL.
910 --
911 if p_mapping_type = c_mapping_type_user_key then
912 l_call_default_value := g_tbl_call_default_null(c_dtype_varchar2);
913 --
914 -- Set other default values according to data type.
915 --
916 elsif p_default_value = g_tbl_default_null(p_datatype) then
917 l_call_default_value := g_tbl_call_default_null(p_datatype);
918 elsif p_default_value = g_tbl_default_hr_api(p_datatype) then
919 l_call_default_value := g_tbl_call_default_hr_api(p_datatype);
920 end if;
921 return l_call_default_value;
922 end get_call_default_value;
923
924 -- ------------------------ default_is_null -------------------------------
925 -- Description:
926 -- Returns true if the default value supplied is null.
927 -- ------------------------------------------------------------------------
928 function default_is_null
929 (p_default_value in varchar2
930 ) return boolean is
931 l_default_value varchar2(2000);
932 begin
933 l_default_value := upper(p_default_value);
934 return l_default_value = 'NULL' or
935 l_default_value = 'D(NULL)' or
936 l_default_value = 'N(NULL)';
937 end default_is_null;
938
939 -- ------------------------ default_is_hr_api -----------------------------
940 -- Description:
941 -- Returns true if the default value supplied is one of the HR_API values.
942 -- ------------------------------------------------------------------------
943 function default_is_hr_api
944 (p_default_value in varchar2
945 ) return boolean is
946 l_default_value varchar2(2000);
947 begin
948 l_default_value := upper(p_default_value);
949 return l_default_value = 'HR_API.G_VARCHAR2' or
950 l_default_value = 'HR_API.G_DATE' or
951 l_default_value = 'HR_API.G_NUMBER';
952 end default_is_hr_api;
953
954 -- ----------------------- gen_batch_lines_column_name --------------------
955 -- Description:
956 -- Generates the batch lines column name, given a sequence number.
957 -- ------------------------------------------------------------------------
958 function gen_batch_lines_column_name
959 (
960 p_seqno in number,
961 p_datatype in number
962 )
963 return varchar2 is
964 l_column_name varchar2(30);
965 begin
966 if p_datatype = c_dtype_long then
967 l_column_name := c_long_value_col;
968 else
969 l_column_name := c_parameter_value_col || ltrim(to_char( p_seqno, '000' ));
970 end if;
971 return l_column_name;
972 end gen_batch_lines_column_name;
973
974 -- ----------------------- gen_def_check_name -----------------------------
975 -- Description:
976 -- Given a name, based on the cursor field name, generates the name
977 -- of the defaulting check column for a defaultable variable.
978 -- ------------------------------------------------------------------------
979 function gen_def_check_name
980 (
981 p_cursor_field_name in varchar2
982 )
983 return varchar2 is
984 l_check_name varchar2(30);
985 begin
986 --
987 -- When checking defaults an additional cursor column is required except
988 -- in the case of long values where the value is passed unchanged because
989 -- the DECODE function cannot handle long values.
990 --
991 if p_cursor_field_name like '%' || c_long_value_col then
992 l_check_name := p_cursor_field_name;
993 else
994 l_check_name :=
995 replace( p_cursor_field_name, c_cursor_value_col, c_def_check_col );
996 end if;
997 return l_check_name;
998 end gen_def_check_name;
999
1000 -- ---------------------- merge_api_and_seed_data -------------------------
1001 -- Description:
1002 -- Merges seed data information into the API parameter list.
1003 -- ------------------------------------------------------------------------
1004 procedure merge_api_and_seed_data
1005 (
1006 p_default_style in number,
1007 p_parameter_tbl in out nocopy t_parameter_tbl,
1008 p_seed_parameter_tbl in out nocopy t_seed_parameter_tbl,
1009 p_function_tbl out nocopy t_function_tbl,
1010 p_parameter_counts in out nocopy t_parameter_counts
1011 )
1012 is
1013 l_seed_parameter t_seed_parameter;
1014 l_match boolean;
1015 l_package_name hr_pump_module_parameters.mapping_definition%type;
1016 l_function_name hr_pump_module_parameters.mapping_definition%type;
1017 l_dotpos number;
1018
1019 l_parameter_tbl t_parameter_tbl;
1020 l_seed_parameter_tbl t_seed_parameter_tbl;
1021 l_parameter_counts t_parameter_counts;
1022 --
1023 l_upper_defval hr_pump_module_parameters.default_value%type;
1024 begin
1025 -- Remember IN OUT parameters.
1026 l_parameter_tbl := p_parameter_tbl;
1027 l_seed_parameter_tbl := p_seed_parameter_tbl;
1028 l_parameter_counts := p_parameter_counts;
1029
1030
1031 p_parameter_counts.batch_lines_parameters := 0;
1032 p_parameter_counts.functions := 0;
1033 for i in 1 .. p_parameter_counts.api_parameters loop
1034 --
1035 -- Do match against seeded data.
1036 --
1037 match_seed_parameter( p_parameter_tbl(i).parameter_name,
1038 p_parameter_counts.seed_parameters,
1039 p_seed_parameter_tbl,
1040 l_seed_parameter,
1041 l_match );
1042 if l_match then
1043 --
1044 -- Got a match.
1045 --
1046 --
1047 -- Check that seed data is set up correctly.
1048 --
1049 if ( l_seed_parameter.mapping_type is not null and
1050 l_seed_parameter.mapping_type <> c_mapping_type_normal and
1051 l_seed_parameter.mapping_definition is null ) or
1052 ( l_seed_parameter.mapping_type is null and
1053 l_seed_parameter.mapping_definition is not null ) then
1054 hr_utility.set_message( 800, 'HR_50312_DP_BAD_MAP_DATA' );
1055 hr_utility.set_message_token
1056 ( 'PARAMETER', l_seed_parameter.parameter_name );
1057 hr_utility.raise_error;
1058 end if;
1059 --
1060 -- Copy values over from the seed data.
1061 --
1062 p_parameter_tbl(i).mapping_type := l_seed_parameter.mapping_type;
1063 p_parameter_tbl(i).mapping_definition :=
1064 l_seed_parameter.mapping_definition;
1065 --
1066 -- Handling default values depends on the data type.
1067 --
1068 if l_seed_parameter.default_value is not null then
1069 if p_parameter_tbl(i).datatype = c_dtype_number
1070 then
1071 p_parameter_tbl(i).default_value := l_seed_parameter.default_value;
1072 elsif p_parameter_tbl(i).datatype = c_dtype_boolean then
1073 if upper(p_parameter_tbl(i).default_value) = 'NULL' then
1074 p_parameter_tbl(i).default_value := 'NULL';
1075 else
1076 p_parameter_tbl(i).default_value :=
1077 '''' || l_seed_parameter.default_value || '''';
1078 end if;
1079 elsif p_parameter_tbl(i).datatype = c_dtype_date then
1080 --
1081 -- Date parameters are converted to dates using the local date
1082 -- generation function.
1083 --
1084 l_upper_defval := upper(l_seed_parameter.default_value);
1085 if l_upper_defval = 'NULL' then
1086 p_parameter_tbl(i).default_value := 'D(NULL)';
1087 elsif l_upper_defval = 'HR_API.G_DATE' then
1088 p_parameter_tbl(i).default_value := l_upper_defval;
1089 else
1090 p_parameter_tbl(i).default_value :=
1091 'd(''' || l_seed_parameter.default_value || ''')';
1092 end if;
1093 else
1094 --
1095 -- varchar2 and long parameters.
1096 --
1097 l_upper_defval := upper(l_seed_parameter.default_value);
1098 if l_upper_defval = 'NULL' or
1099 l_upper_defval = 'HR_API.G_VARCHAR2' then
1100 p_parameter_tbl(i).default_value := l_upper_defval;
1101 else
1102 p_parameter_tbl(i).default_value :=
1103 '''' || l_seed_parameter.default_value || '''';
1104 end if;
1105 end if;
1106 end if;
1107 --
1108 -- For user_key values the mapping_definition is the API parameter
1109 -- name. The parameter_name is the name of the user_key parameter.
1110 --
1111 if l_seed_parameter.mapping_type = c_mapping_type_user_key then
1112 p_parameter_tbl(i).parameter_name := l_seed_parameter.parameter_name;
1113 end if;
1114 end if;
1115 --
1116 -- Set up mapping information for the parameter, if required.
1117 --
1118 if p_parameter_tbl(i).mapping_type is null then
1119 if ( p_parameter_tbl(i).in_out = c_ptype_in or
1120 p_parameter_tbl(i).in_out = c_ptype_in_out ) and
1121 ( lower(p_parameter_tbl(i).parameter_name) like '%_id' ) and
1122 ( lower(p_parameter_tbl(i).parameter_name) <> 'p_business_group_id' )
1123 then
1124 --
1125 -- Generate get_id function for _id parameter.
1126 -- For parameter p_xxx generate the function name get_xxx.
1127 --
1128 p_parameter_tbl(i).mapping_type := c_mapping_type_function;
1129 p_parameter_tbl(i).mapping_definition :=
1130 'get' || substr( lower(p_parameter_tbl(i).parameter_name), 2 );
1131 else
1132 p_parameter_tbl(i).mapping_type := c_mapping_type_normal;
1133 p_parameter_tbl(i).mapping_definition := null;
1134 end if;
1135 end if;
1136
1137 --
1138 -- Set up batch lines column information for the parameter.
1139 --
1140 if ( p_parameter_tbl(i).mapping_type <> c_mapping_type_function ) and
1141 ( not special_parameter( p_parameter_tbl(i).parameter_name ) )
1142 then
1143 p_parameter_counts.batch_lines_parameters :=
1144 p_parameter_counts.batch_lines_parameters + 1;
1145 p_parameter_tbl(i).batch_lines_seqno :=
1146 p_parameter_counts.batch_lines_parameters;
1147 p_parameter_tbl(i).batch_lines_column :=
1148 gen_batch_lines_column_name
1149 (
1150 p_parameter_tbl(i).batch_lines_seqno,
1151 p_parameter_tbl(i).datatype
1152 );
1153 else
1154 p_parameter_tbl(i).batch_lines_seqno := null;
1155 end if;
1156
1157 --
1158 -- Set up the function list entry for function calls.
1159 --
1160 if p_parameter_tbl(i).mapping_type = c_mapping_type_function then
1161 p_parameter_counts.functions := p_parameter_counts.functions + 1;
1162 --
1163 -- Set the package_name, and function_name from the mapping definition.
1164 --
1165 l_dotpos := instr(p_parameter_tbl(i).mapping_definition, '.');
1166 if l_dotpos <> 0 then
1167 l_function_name :=
1168 substr(p_parameter_tbl(i).mapping_definition, l_dotpos + 1);
1169 l_package_name :=
1170 substr(p_parameter_tbl(i).mapping_definition, 1, l_dotpos-1);
1171 else
1172 l_function_name := p_parameter_tbl(i).mapping_definition;
1173 l_package_name := null;
1174 end if;
1175 p_function_tbl(p_parameter_counts.functions).function_name :=
1176 l_function_name;
1177 p_function_tbl(p_parameter_counts.functions).package_name :=
1178 l_package_name;
1179 p_function_tbl(p_parameter_counts.functions).seqno :=
1180 p_parameter_tbl(i).seqno;
1181 --
1182 -- index1 = null means that the function has no parameters.
1183 --
1184 p_function_tbl(p_parameter_counts.functions).index1 := null;
1185 p_function_tbl(p_parameter_counts.functions).index2 := null;
1186 end if;
1187
1188 --
1189 -- Set up default values for defaultable parameters.
1190 --
1191 if p_parameter_tbl(i).defaultable then
1192 if p_parameter_tbl(i).default_value is null then
1193 p_parameter_tbl(i).default_value :=
1194 get_default_value( p_default_style, p_parameter_tbl(i).datatype,
1195 p_parameter_tbl(i).mapping_type );
1196 end if;
1197 end if;
1198 end loop;
1199 exception
1200 when others then
1201 -- Reset IN OUT parameters and set out parameters.
1202 p_parameter_tbl := l_parameter_tbl;
1203 p_seed_parameter_tbl := l_seed_parameter_tbl;
1204 p_parameter_counts := l_parameter_counts;
1205 p_function_tbl.delete;
1206 raise;
1207 end merge_api_and_seed_data;
1208
1209 -- ----------------------- describe_function ------------------------------
1210 -- Description:
1211 -- Calls hr_general.describe_procedure on a parameter mapping function.
1212 -- Sets up the parameter lists for the functions and updates the relevant
1213 -- counts.
1214 -- ------------------------------------------------------------------------
1215 procedure describe_function
1216 (
1217 p_mapping_package_tbl in t_mapping_package_tbl,
1218 p_parameter in t_parameter,
1219 p_function in out nocopy t_function,
1220 p_function_call_tbl in out nocopy t_function_parameter_tbl,
1221 p_distinct_parameter_tbl in out nocopy t_function_parameter_tbl,
1222 p_parameter_counts in out nocopy t_parameter_counts
1223 )
1224 is
1225 -- dbms_describe parameters.
1226 l_overload dbms_describe.number_table;
1227 l_position dbms_describe.number_table;
1228 l_level dbms_describe.number_table;
1229 l_argument_name dbms_describe.varchar2_table;
1230 l_datatype dbms_describe.number_table;
1231 l_default_value dbms_describe.number_table;
1232 l_in_out dbms_describe.number_table;
1233 l_length dbms_describe.number_table;
1234 l_precision dbms_describe.number_table;
1235 l_scale dbms_describe.number_table;
1236 l_radix dbms_describe.number_table;
1237 l_spare dbms_describe.number_table;
1238 --
1239 l_first_overload binary_integer;
1240 l_seqno binary_integer;
1241 l_currpos binary_integer;
1242 -- Package for the mapping function.
1243 l_package hr_pump_module_parameters.mapping_definition%type;
1244 --
1245 l_match boolean;
1246 l_call_params number; -- Index into function call parameters list.
1247 l_distinct_params number; -- Index into distinct parameters list.
1248
1249 l_function t_function;
1250 l_function_call_tbl t_function_parameter_tbl;
1251 l_distinct_parameter_tbl t_function_parameter_tbl;
1252 l_parameter_counts t_parameter_counts;
1253 l_is_a_function boolean := false;
1254 --
1255 begin
1256 --
1257
1258 -- Remember IN OUT parameters.
1259 l_function := p_function;
1260 l_function_call_tbl := p_function_call_tbl;
1261 l_distinct_parameter_tbl := p_distinct_parameter_tbl;
1262 l_parameter_counts := p_parameter_counts;
1263
1264 for i in 1 .. p_mapping_package_tbl.count loop
1265 begin
1266 l_package := p_mapping_package_tbl(i).mapping_package;
1267 hr_general.describe_procedure
1268 ( object_name => l_package || '.' || p_function.function_name,
1269 reserved1 => null,
1270 reserved2 => null,
1271 overload => l_overload,
1272 position => l_position,
1273 level => l_level,
1274 argument_name => l_argument_name,
1275 datatype => l_datatype,
1276 default_value => l_default_value,
1277 in_out => l_in_out,
1278 length => l_length,
1279 precision => l_precision,
1280 scale => l_scale,
1281 radix => l_radix,
1282 spare => l_spare );
1283 --
1284 -- Found a function, so set up the package name and exit the loop.
1285 --
1286 p_function.package_name := l_package;
1287 exit;
1288 exception
1289 when package_not_exists or invalid_package then
1290 hr_utility.set_message( 800, 'HR_50313_DP_NO_MAP_PKG' );
1291 hr_utility.set_message_token( 'PACKAGE', l_package );
1292 hr_utility.raise_error;
1293 when remote_object then
1294 hr_utility.set_message( 800, 'HR_50307_DP_REMOTE_OBJECT' );
1295 hr_utility.set_message_token
1296 ( 'OBJECT', l_package || '.' || p_function.function_name );
1297 hr_utility.raise_error;
1298 when invalid_object_name then
1299 hr_utility.set_message( 800, 'HR_50315_DP_BAD_FUNCTION_NAME' );
1300 hr_utility.set_message_token
1301 ( 'OBJECT', l_package || '.' || p_function.function_name );
1302 hr_utility.raise_error;
1303 when proc_not_in_package then
1304 if i = p_mapping_package_tbl.count then
1305 hr_utility.set_message( 800, 'HR_50314_DP_NO_SUCH_FUNCTION' );
1306 hr_utility.set_message_token( 'FUNCTION', p_function.function_name );
1307 hr_utility.raise_error;
1308 end if;
1309 end;
1310 end loop;
1311
1312 --
1313 -- Build up parameter lists.
1314 --
1315 l_seqno := l_position.first;
1316 l_first_overload := l_overload( l_seqno );
1317 loop
1318 --
1319 -- Exit when no more parameters to get.
1320 --
1321 exit when not l_position.exists( l_seqno );
1322
1323 --
1324 -- Check for overloaded function call as overloading is not allowed.
1325 --
1326 if l_overload(l_seqno) <> l_first_overload then
1327 hr_utility.set_message( 800, 'HR_50318_DP_OVL_FUNCTION' );
1328 hr_utility.set_message_token
1329 ( 'OBJECT', c_get_function_package || '.' || p_function.function_name );
1330 hr_utility.raise_error;
1331 end if;
1332
1333 --
1334 -- Handle the function return value.
1335 --
1336 if l_position( l_seqno ) = 0 then
1337 l_is_a_function := true;
1338
1339 --
1340 -- Check that the function is of the correct type.
1341 --
1342 if l_datatype( l_seqno ) <> p_parameter.datatype and not
1343 ( l_datatype( l_seqno ) = c_dtype_binary_integer and
1344 p_parameter.datatype = c_dtype_number ) and not
1345 ( ( l_datatype( l_seqno ) = c_dtype_number or
1346 l_datatype( l_seqno ) = c_dtype_binary_integer ) and
1347 p_parameter.datatype = c_dtype_varchar2 )
1348 then
1349 hr_utility.set_message( 800, 'HR_50317_DP_BAD_FUNCTION_RET' );
1350 hr_utility.set_message_token
1351 ( 'FUNCTION', c_get_function_package || '.' || p_function.function_name );
1352 hr_utility.set_message_token( 'PARAMETER', p_parameter.parameter_name );
1353 hr_utility.raise_error;
1354 end if;
1355
1356 p_function.ret_type := l_datatype( l_seqno );
1357
1358 --
1359 -- Handle ordinary parameters.
1360 --
1361 else
1362 --
1363 -- Set l_currpos so that parameters appear in the same sequence as
1364 -- in the function call.
1365 --
1366 l_currpos := l_position( l_seqno ) + p_parameter_counts.call_parameters;
1367
1368 --
1369 -- Check that the data type is supported.
1370 --
1371 if l_datatype(l_seqno) <> c_dtype_varchar2 and
1372 l_datatype(l_seqno) <> c_dtype_number and
1373 l_datatype(l_seqno) <> c_dtype_date and
1374 l_datatype(l_seqno) <> c_dtype_boolean and
1375 l_datatype(l_seqno) <> c_dtype_long
1376 then
1377 hr_utility.set_message( 800, 'HR_50319_DP_FUN_BAD_ARG_TYPE' );
1378 hr_utility.set_message_token( 'FUNCTION', p_function.function_name );
1379 hr_utility.set_message_token( 'ARGUMENT', l_argument_name(l_seqno) );
1380 hr_utility.set_message_token( 'TYPE', l_datatype(l_seqno) );
1381 hr_utility.raise_error;
1382 end if;
1383
1384 p_function_call_tbl(l_currpos).parameter_name :=
1385 upper(l_argument_name(l_seqno));
1386 p_function_call_tbl(l_currpos).function_parameter :=
1387 upper(l_argument_name(l_seqno));
1388 p_function_call_tbl(l_currpos).datatype := l_datatype(l_seqno);
1389 p_function_call_tbl(l_currpos).defaultable := p_parameter.defaultable;
1390 p_function_call_tbl(l_currpos).default_value := null;
1391
1392 --
1393 -- Add parameter name to the distinct parameter list if necessary.
1394 --
1395 l_match := false;
1396 for i in 1 .. p_parameter_counts.distinct_parameters loop
1397 if p_distinct_parameter_tbl(i).parameter_name =
1398 upper(l_argument_name(l_seqno))
1399 then
1400 --
1401 -- Got a match. Check that parameter usage types are compatible.
1402 --
1403 if p_distinct_parameter_tbl(i).datatype <> l_datatype(l_seqno) then
1404 hr_utility.set_message( 800, 'HR_50320_DP_FUN_ARG_USAGE' );
1405 hr_utility.set_message_token( 'ARGUMENT', l_argument_name(l_seqno) );
1406 hr_utility.raise_error;
1407 end if;
1408 --
1409 -- If p_parameter is not defaultable then all parameters for its
1410 -- mapping function are not defaultable either. If this mapping
1411 -- function shares parameters with other mapping functions then
1412 -- those parameters must be marked as non-defaultable.
1413 --
1414 if not p_parameter.defaultable then
1415 p_distinct_parameter_tbl(i).defaultable := false;
1416 end if;
1417 --
1418 -- Flag the fact that there is a match and exit.
1419 --
1420 l_match := true;
1421 exit;
1422 end if;
1423 end loop;
1424
1425 --
1426 -- If no match add to distinct parameter list.
1427 --
1428 if not l_match then
1429 l_distinct_params := p_parameter_counts.distinct_parameters + 1;
1430 p_parameter_counts.distinct_parameters := l_distinct_params;
1431 p_distinct_parameter_tbl(l_distinct_params).parameter_name :=
1432 upper(l_argument_name(l_seqno));
1433 p_distinct_parameter_tbl(l_distinct_params).mapping_function :=
1434 p_function.function_name;
1435 p_distinct_parameter_tbl(l_distinct_params).datatype :=
1436 l_datatype(l_seqno);
1437 p_distinct_parameter_tbl(l_distinct_params).defaultable :=
1438 p_parameter.defaultable;
1439 p_distinct_parameter_tbl(l_distinct_params).default_value := null;
1440 --
1441 -- The batch_lines_column field will be set in merge_function_data.
1442 --
1443 p_distinct_parameter_tbl(l_distinct_params).batch_lines_column := null;
1444 end if;
1445
1446 --
1447 -- Look for ALWAYS_CALL flag parameter.
1448 --
1449 if upper(l_argument_name(l_seqno)) = 'P_DATA_PUMP_ALWAYS_CALL' then
1450 p_function.always_call := true;
1451 end if;
1452
1453 end if;
1454
1455 l_seqno := l_position.next(l_seqno);
1456 end loop;
1457
1458 --
1459 -- Verify that this it was actually a function.
1460 --
1461 if not l_is_a_function then
1462 hr_utility.set_message( 800, 'HR_50316_DP_NOT_FUNCTION' );
1463 hr_utility.set_message_token( 'FUNCTION', p_function.function_name );
1464 hr_utility.raise_error;
1465 end if;
1466
1467 --
1468 -- Update indexes in the function table, and the count of function call
1469 -- parameters.
1470 --
1471 if l_position.count <> 1 then
1472 p_function.index1 := p_parameter_counts.call_parameters + 1;
1473 -- Note: l_position.count includes the function return (position 0).
1474 p_function.index2 := p_function.index1 + l_position.count - 2;
1475 p_parameter_counts.call_parameters := p_function.index2;
1476 end if;
1477 exception
1478 when others then
1479 -- Reset IN OUT parameters.
1480 p_function := l_function;
1481 p_function_call_tbl := l_function_call_tbl;
1482 p_distinct_parameter_tbl := l_distinct_parameter_tbl;
1483 p_parameter_counts := l_parameter_counts;
1484 raise;
1485 end describe_function;
1486
1487 -- ---------------------- merge_function_data -----------------------------
1488 -- Description:
1489 -- Builds the function parameter information, and merges it with any remaining
1490 -- seed data into p_parameter_tbl.
1491 -- ------------------------------------------------------------------------
1492 procedure merge_function_data
1493 (
1494 p_module_package in varchar2,
1495 p_module_name in varchar2,
1496 p_defaulting_style in number,
1497 p_mapping_package_tbl in t_mapping_package_tbl,
1498 p_parameter_tbl in out nocopy t_parameter_tbl,
1499 p_function_tbl in out nocopy t_function_tbl,
1500 p_function_call_tbl in out nocopy t_function_parameter_tbl,
1501 p_distinct_parameter_tbl in out nocopy t_function_parameter_tbl,
1502 p_parameter_counts in out nocopy t_parameter_counts
1503 )
1504 is
1505 l_function_parameter t_function_parameter;
1506 l_api_parameter t_parameter;
1507 l_total_parameters number;
1508 l_batch_lines_params number;
1509 l_match boolean;
1510 l_mapping_package_tbl t_mapping_package_tbl;
1511
1512 l_parameter_tbl t_parameter_tbl;
1513 l_function_tbl t_function_tbl;
1514 l_function_call_tbl t_function_parameter_tbl;
1515 l_distinct_parameter_tbl t_function_parameter_tbl;
1516 l_parameter_counts t_parameter_counts;
1517 --
1518 begin
1519 -- Remember IN OUT parameters.
1520 l_parameter_tbl := p_parameter_tbl;
1521 l_function_tbl := p_function_tbl;
1522 l_function_call_tbl := p_function_call_tbl;
1523 l_distinct_parameter_tbl := p_distinct_parameter_tbl;
1524 l_parameter_counts := p_parameter_counts;
1525 --
1526 -- Build up the function parameter lists.
1527 --
1528 p_parameter_counts.distinct_parameters := 0;
1529 p_parameter_counts.call_parameters := 0;
1530 for i in 1 .. p_parameter_counts.functions loop
1531 --
1532 -- Check whether or not the user wanted a specific mapping package.
1533 --
1534 if p_function_tbl(i).package_name is not null then
1535 l_mapping_package_tbl(1).mapping_package :=
1536 p_function_tbl(i).package_name;
1537 else
1538 l_mapping_package_tbl := p_mapping_package_tbl;
1539 end if;
1540 describe_function( l_mapping_package_tbl,
1541 p_parameter_tbl(p_function_tbl(i).seqno),
1542 p_function_tbl(i),
1543 p_function_call_tbl,
1544 p_distinct_parameter_tbl,
1545 p_parameter_counts );
1546 l_mapping_package_tbl.delete;
1547 end loop;
1548 --
1549 -- Function parameter lists are built so check the function arguments
1550 -- against the API parameters.
1551 --
1552 for i in 1 .. p_parameter_counts.distinct_parameters loop
1553 l_match := false;
1554 l_function_parameter := p_distinct_parameter_tbl(i);
1555 for j in 1 .. p_parameter_counts.api_parameters loop
1556 --
1557 -- Handle aliased parameter mapping.
1558 --
1559 l_api_parameter := p_parameter_tbl(j);
1560 if l_api_parameter.mapping_type = c_mapping_type_aliased and
1561 (l_api_parameter.mapping_definition =
1562 l_function_parameter.parameter_name)
1563 then
1564 --
1565 -- Need to overwrite the parameter name with the alias name in the
1566 -- call list and distinct parameter list.
1567 --
1568 for k in 1 .. p_parameter_counts.call_parameters loop
1569 if p_function_call_tbl(k).parameter_name =
1570 l_api_parameter.mapping_definition
1571 then
1572 p_function_call_tbl(k).parameter_name :=
1573 l_api_parameter.parameter_name;
1574 end if;
1575 end loop;
1576 l_function_parameter.parameter_name := l_api_parameter.parameter_name;
1577 l_match := true;
1578 --
1579 -- Handle user_key.
1580 --
1581 elsif l_api_parameter.mapping_type = c_mapping_type_user_key and
1582 (l_api_parameter.parameter_name =
1583 l_function_parameter.parameter_name)
1584 then
1585 l_match := true;
1586 --
1587 -- Check for simple parameter name match.
1588 --
1589 elsif l_api_parameter.parameter_name =
1590 l_function_parameter.parameter_name and
1591 l_api_parameter.mapping_type <> c_mapping_type_function
1592 then
1593 l_match := true;
1594 end if;
1595 --
1596 if l_match then
1597 --
1598 -- At last we can set the function parameter default value.
1599 --
1600 l_function_parameter.default_value := l_api_parameter.default_value;
1601 --
1602 -- If the function parameter is mandatory (i.e. because one of
1603 -- the mapping functions that uses it corresponds to a mandatory
1604 -- API parameter) it is necessary to make the API parameter matched
1605 -- here mandatory also.
1606 --
1607 if not l_function_parameter.defaultable and
1608 l_api_parameter.defaultable
1609 then
1610 p_parameter_tbl(j).defaultable := false;
1611 --
1612 -- If the function parameter is defaultable and the corresponding
1613 -- API parameter is mandatory then mark the function parameter
1614 -- as mandatory also. This is for the code that determines the
1615 -- conditions under which a mapping function should be called.
1616 --
1617 elsif not l_api_parameter.defaultable and
1618 l_function_parameter.defaultable then
1619 l_function_parameter.defaultable := false;
1620 end if;
1621 --
1622 -- Check that parameter types match.
1623 --
1624 if l_api_parameter.datatype <> l_function_parameter.datatype then
1625 hr_utility.set_message( 800, 'HR_50320_DP_FUN_ARG_USAGE' );
1626 hr_utility.set_message_token
1627 ( 'ARGUMENT', l_api_parameter.parameter_name );
1628 hr_utility.raise_error;
1629 end if;
1630 --
1631 -- Get the batch lines column from the parameter list.
1632 --
1633 if not special_parameter( l_function_parameter.parameter_name ) then
1634 l_function_parameter.batch_lines_column :=
1635 l_api_parameter.batch_lines_column;
1636 end if;
1637 --
1638 -- Got a match so exit the loop.
1639 --
1640 exit;
1641 end if;
1642 end loop;
1643 --
1644 -- No match with the API parameters, but parameter name is that of
1645 -- a special parameter. Check that the parameters are consistent.
1646 --
1647 if not l_match and
1648 special_parameter( l_function_parameter.parameter_name )
1649 then
1650 check_special_parameter( l_function_parameter );
1651 end if;
1652 --
1653 -- If no match add to the parameter list. Function parameters (outside of
1654 -- the API) do not have any seed data. Special parameters are not handled
1655 -- here because they are not part of the batch lines information.
1656 --
1657 if not l_match and
1658 not special_parameter( l_function_parameter.parameter_name )
1659 then
1660 --
1661 -- Check for long parameters.
1662 --
1663 if l_function_parameter.datatype = c_dtype_long then
1664 p_parameter_counts.long_parameters :=
1665 p_parameter_counts.long_parameters + 1;
1666 if p_parameter_counts.long_parameters > 1 then
1667 hr_utility.set_message( 800, 'HR_50016_DP_TOO_MANY_LONGS' );
1668 hr_utility.set_message_token
1669 ( 'API', p_module_package || '.' || p_module_name );
1670 hr_utility.set_message_token
1671 ( 'ARGUMENT', l_function_parameter.parameter_name );
1672 hr_utility.set_message_token
1673 ( 'FUNCTION', l_function_parameter.mapping_function );
1674 hr_utility.raise_error;
1675 end if;
1676 end if;
1677
1678 l_total_parameters := p_parameter_counts.total_parameters + 1;
1679 p_parameter_counts.total_parameters := l_total_parameters;
1680
1681 --
1682 -- This is a batch lines parameter, but not an API parameter.
1683 --
1684 l_batch_lines_params := p_parameter_counts.batch_lines_parameters + 1;
1685 p_parameter_counts.batch_lines_parameters := l_batch_lines_params;
1686
1687 p_parameter_tbl(l_total_parameters).seqno := l_total_parameters;
1688 p_parameter_tbl(l_total_parameters).api_seqno := null;
1689 p_parameter_tbl(l_total_parameters).batch_lines_seqno :=
1690 l_batch_lines_params;
1691 p_parameter_tbl(l_total_parameters).datatype :=
1692 l_function_parameter.datatype;
1693 p_parameter_tbl(l_total_parameters).batch_lines_column :=
1694 gen_batch_lines_column_name
1695 (
1696 p_parameter_tbl(l_total_parameters).batch_lines_seqno,
1697 p_parameter_tbl(l_total_parameters).datatype
1698 );
1699 l_function_parameter.batch_lines_column :=
1700 p_parameter_tbl(l_total_parameters).batch_lines_column;
1701 p_parameter_tbl(l_total_parameters).parameter_name :=
1702 l_function_parameter.parameter_name;
1703 if upper(p_parameter_tbl(l_total_parameters).parameter_name) like
1704 '%USER_KEY'
1705 then
1706 p_parameter_tbl(l_total_parameters).mapping_type :=
1707 c_mapping_type_user_key;
1708 p_parameter_tbl(l_total_parameters).mapping_definition := null;
1709 else
1710 p_parameter_tbl(l_total_parameters).mapping_type :=
1711 c_mapping_type_normal;
1712 p_parameter_tbl(l_total_parameters).mapping_definition := null;
1713 end if;
1714 p_parameter_tbl(l_total_parameters).defaultable :=
1715 l_function_parameter.defaultable;
1716 if l_function_parameter.defaultable then
1717 p_parameter_tbl(l_total_parameters).default_value :=
1718 get_default_value( p_defaulting_style, l_function_parameter.datatype,
1719 p_parameter_tbl(l_total_parameters).mapping_type );
1720 l_function_parameter.default_value :=
1721 p_parameter_tbl(l_total_parameters).default_value;
1722 end if;
1723 --
1724 -- Parameter is an in parameter.
1725 --
1726 p_parameter_tbl(l_total_parameters).in_out := c_ptype_in;
1727 end if;
1728 --
1729 -- Update the values in the function call list using the current
1730 -- function parameter.
1731 --
1732 if not special_parameter( l_function_parameter.parameter_name ) then
1733 for j in 1 .. p_parameter_counts.call_parameters loop
1734 if p_function_call_tbl(j).parameter_name =
1735 l_function_parameter.parameter_name
1736 then
1737 p_function_call_tbl(j).defaultable :=
1738 l_function_parameter.defaultable;
1739 p_function_call_tbl(j).batch_lines_column :=
1740 l_function_parameter.batch_lines_column;
1741 p_function_call_tbl(j).default_value :=
1742 l_function_parameter.default_value;
1743 end if;
1744 end loop;
1745 end if;
1746 end loop;
1747 exception
1748 when others then
1749 -- Remember IN OUT parameters.
1750 p_parameter_tbl := l_parameter_tbl;
1751 p_function_tbl := l_function_tbl;
1752 p_function_call_tbl := l_function_call_tbl;
1753 p_distinct_parameter_tbl := l_distinct_parameter_tbl;
1754 p_parameter_counts := l_parameter_counts;
1755 raise;
1756 end merge_function_data;
1757
1758 -- ---------------------- setup_parameter_data ----------------------------
1759 -- Description:
1760 -- Sets up all the parameter data structures for code generation.
1761 -- ------------------------------------------------------------------------
1762 procedure setup_parameter_data
1763 (
1764 p_module_package in varchar2,
1765 p_module_name in varchar2,
1766 p_parameter_counts in out nocopy t_parameter_counts,
1767 p_defaulting_style in out nocopy number,
1768 p_parameter_tbl in out nocopy t_parameter_tbl,
1769 p_function_tbl in out nocopy t_function_tbl,
1770 p_function_call_tbl in out nocopy t_function_parameter_tbl
1771 )
1772 is
1773 cursor csr_defaulting_style( p_module_name in varchar2 ) is
1774 select decode( count(0), 0, c_default_hr_api, c_default_null )
1775 from hr_pump_default_exceptions hrpde,
1776 hr_api_modules ham
1777 where upper(ham.module_package) = upper(p_module_package)
1778 and upper(ham.module_name) = upper(p_module_name)
1779 and upper(ham.api_module_type) in ('AI', 'BP', 'DM')
1780 and upper(hrpde.module_name) = upper(p_module_name)
1781 and upper(hrpde.api_module_type) = upper(ham.api_module_type);
1782 --
1783 l_seed_parameter_tbl t_seed_parameter_tbl;
1784 l_distinct_parameter_tbl t_function_parameter_tbl;
1785 l_mapping_package_tbl t_mapping_package_tbl;
1786
1787 l_parameter_counts t_parameter_counts;
1788 l_defaulting_style number;
1789 l_parameter_tbl t_parameter_tbl;
1790 l_function_tbl t_function_tbl;
1791 l_function_call_tbl t_function_parameter_tbl;
1792 --
1793 begin
1794 -- Remember IN OUT parameters.
1795 l_parameter_counts := p_parameter_counts;
1796 l_defaulting_style := p_defaulting_style;
1797 l_parameter_tbl := p_parameter_tbl;
1798 l_function_tbl := p_function_tbl;
1799 l_function_call_tbl := p_function_call_tbl;
1800
1801
1802 -- Get the defaulting style.
1803 if lower(p_module_name) not like 'create%' then
1804 open csr_defaulting_style( p_module_name );
1805 fetch csr_defaulting_style into p_defaulting_style;
1806 close csr_defaulting_style;
1807 else
1808 p_defaulting_style := c_default_null;
1809 end if;
1810
1811 describe_api( p_module_package, p_module_name, p_parameter_tbl,
1812 p_parameter_counts );
1813
1814 --
1815 -- Original data pump code generation, seed data and mapping functions.
1816 --
1817 if hr_pump_meta_mapper.g_standard_generate then
1818 get_seed_parameters( p_module_package, p_module_name,
1819 l_seed_parameter_tbl,
1820 p_parameter_counts.seed_parameters );
1821
1822 merge_api_and_seed_data( p_defaulting_style, p_parameter_tbl,
1823 l_seed_parameter_tbl, p_function_tbl,
1824 p_parameter_counts );
1825 l_seed_parameter_tbl.delete;
1826
1827 get_mapping_packages
1828 (p_module_package => p_module_package
1829 ,p_module_name => p_module_name
1830 ,p_mapping_package_tbl => l_mapping_package_tbl
1831 );
1832
1833 merge_function_data( p_module_package, p_module_name,
1834 p_defaulting_style, l_mapping_package_tbl,
1835 p_parameter_tbl, p_function_tbl,
1836 p_function_call_tbl, l_distinct_parameter_tbl,
1837 p_parameter_counts );
1838 l_mapping_package_tbl.delete;
1839 l_distinct_parameter_tbl.delete;
1840 ----------------------------------------------------------------------
1841 -- Bare bones wrapper generation. No seed data and basic defaulting --
1842 -- rules. --
1843 ----------------------------------------------------------------------
1844 else
1845 --
1846 -- Zero the parameter counts.
1847 --
1848 p_parameter_counts.batch_lines_parameters := 0;
1849 p_parameter_counts.seed_parameters := 0;
1850 p_parameter_counts.functions := 0;
1851 p_parameter_counts.distinct_parameters := 0;
1852 p_parameter_counts.call_parameters := 0;
1853 --
1854 -- Set up the default values, mapping types, and batch lines
1855 -- sequences for the API parameters.
1856 --
1857 for i in 1 .. p_parameter_counts.total_parameters loop
1858 if p_parameter_tbl(i).defaultable then
1859 p_parameter_tbl(i).default_value :=
1860 get_default_value
1861 (p_defaulting_style => p_defaulting_style
1862 ,p_datatype => p_parameter_tbl(i).datatype
1863 ,p_mapping_type => p_parameter_tbl(i).mapping_type
1864 );
1865 end if;
1866 --
1867 -- Set up batch lines column information for the parameter.
1868 --
1869 if not special_parameter(p_parameter_tbl(i).parameter_name) then
1870 p_parameter_counts.batch_lines_parameters :=
1871 p_parameter_counts.batch_lines_parameters + 1;
1872 p_parameter_tbl(i).batch_lines_seqno :=
1873 p_parameter_counts.batch_lines_parameters;
1874 p_parameter_tbl(i).batch_lines_column :=
1875 gen_batch_lines_column_name
1876 (
1877 p_parameter_tbl(i).batch_lines_seqno,
1878 p_parameter_tbl(i).datatype
1879 );
1880 else
1881 p_parameter_tbl(i).batch_lines_seqno := null;
1882 end if;
1883 --
1884 p_parameter_tbl(i).mapping_type := c_mapping_type_normal;
1885 end loop;
1886 end if;
1887 --
1888 -- Set up the call default parameters.
1889 --
1890 for i in 1 .. p_parameter_counts.total_parameters loop
1891 if p_parameter_tbl(i).defaultable then
1892 p_parameter_tbl(i).call_default_value :=
1893 get_call_default_value(p_parameter_tbl(i).default_value,
1894 p_parameter_tbl(i).datatype,
1895 p_parameter_tbl(i).mapping_type );
1896 end if;
1897 end loop;
1898
1899 if p_parameter_counts.batch_lines_parameters > c_max_batch_lines_cols then
1900 hr_utility.set_message( 800, 'HR_50321_DP_TOO_MANY_ARGS' );
1901 hr_utility.set_message_token
1902 ( 'TOTAL', p_parameter_counts.batch_lines_parameters );
1903 hr_utility.set_message_token( 'MAXIMUM', c_max_batch_lines_cols );
1904 hr_utility.raise_error;
1905 end if;
1906
1907 exception
1908 when others then
1909 -- Reset IN OUT parameters.
1910 p_parameter_counts := l_parameter_counts;
1911 p_defaulting_style := l_defaulting_style;
1912 p_parameter_tbl := l_parameter_tbl;
1913 p_function_tbl := l_function_tbl;
1914 p_function_call_tbl := l_function_call_tbl;
1915 if csr_defaulting_style%isopen then
1916 close csr_defaulting_style;
1917 end if;
1918 raise;
1919 end setup_parameter_data;
1920
1921 -- ------------------------ run_sql ---------------------------------------
1922 -- Description:
1923 -- Runs a SQL statement using the dbms_sql package. No bind variables
1924 -- allowed.
1925 -- ------------------------------------------------------------------------
1926 procedure run_sql( p_sql in varchar2 )
1927 is
1928 l_csr_sql integer;
1929 l_rows number;
1930 begin
1931 l_csr_sql := dbms_sql.open_cursor;
1932 dbms_sql.parse( l_csr_sql, p_sql, dbms_sql.native );
1933 l_rows := dbms_sql.execute( l_csr_sql );
1934 dbms_sql.close_cursor( l_csr_sql );
1935 exception
1936 when others then
1937 raise;
1938 end run_sql;
1939
1940 -- ------------------------ run_sql ---------------------------------------
1941 -- Description:
1942 -- Alternative interface for generating the large PLSQL packages.
1943 -- Table index must begin at 1.
1944 -- ------------------------------------------------------------------------
1945 procedure run_sql( p_sql in dbms_sql.varchar2s )
1946 is
1947 l_csr_sql integer;
1948 l_rows number;
1949 begin
1950 l_csr_sql := dbms_sql.open_cursor;
1951 --
1952 -- Do the parse without inserting linefeeds between each element.
1953 --
1954 dbms_sql.parse( l_csr_sql, p_sql, 1, p_sql.count, false, dbms_sql.native );
1955 l_rows := dbms_sql.execute( l_csr_sql );
1956 dbms_sql.close_cursor( l_csr_sql );
1957 exception
1958 when others then
1959 raise;
1960 end run_sql;
1961
1962 -- ---------------------------- split_sql_text ---------------------------
1963 -- Description
1964 -- Procedure to split the package body into chunks to allow longer
1965 -- generated packages.
1966 -- -----------------------------------------------------------------------
1967 procedure split_sql_text
1968 (p_last in boolean
1969 ,p_text in out nocopy varchar2
1970 ,p_text_pieces in out nocopy dbms_sql.varchar2s
1971
1972 ) is
1973 l_start binary_integer;
1974 j binary_integer;
1975 l_blen number;
1976 l_pieces number;
1977 begin
1978 if p_text is null then
1979 return;
1980 end if;
1981 --
1982 l_pieces := lengthb(p_text) / 256;
1983 if p_last then
1984 if ceil(l_pieces) <> floor(l_pieces) then
1985 l_pieces := ceil(l_pieces);
1986 end if;
1987 else
1988 l_pieces := floor(l_pieces);
1989 end if;
1990 --
1991 l_start := p_text_pieces.count;
1992 j := 1;
1993 for i in 1 .. l_pieces loop
1994 p_text_pieces(i + l_start) := substr(p_text, j, 256);
1995 j := j + 256;
1996 end loop;
1997 --
1998 if not p_last then
1999 p_text := substr(p_text, j);
2000 else
2001 p_text := null;
2002 end if;
2003 end split_sql_text;
2004
2005 ---------------------------------------------------------------------------
2006 -- CODE GENERATION PROCEDURES --
2007 ---------------------------------------------------------------------------
2008 -- ------------------------ check_compile ---------------------------------
2009 -- Description:
2010 -- Checks whether or not the generated package or view compiled okay.
2011 -- ------------------------------------------------------------------------
2012 procedure check_compile
2013 (
2014 p_object_name in varchar2,
2015 p_object_type in varchar2
2016 )
2017 is
2018 cursor csr_check_compile
2019 (
2020 p_object_name in varchar2,
2021 p_object_type in varchar2
2022 ) is
2023 select status
2024 from user_objects
2025 where upper(object_name) = upper(p_object_name)
2026 and upper(object_type) = upper(p_object_type);
2027 l_status varchar2(64);
2028 begin
2029 open csr_check_compile( p_object_name, p_object_type );
2030 fetch csr_check_compile into l_status;
2031 close csr_check_compile;
2032 if upper( l_status ) <> 'VALID' then
2033 hr_utility.set_message( 800, 'HR_50322_DP_COMPILE_FAILED' );
2034 hr_utility.set_message_token( 'OBJECT', p_object_name );
2035 hr_utility.raise_error;
2036 end if;
2037 end check_compile;
2038
2039 -- ------------------------ create_view -----------------------------------
2040 -- Description:
2041 -- Generates and executes the SQL for creating the view.
2042 -- ------------------------------------------------------------------------
2043 procedure create_view
2044 (
2045 p_view_name in varchar2,
2046 p_parameter_tbl in t_parameter_tbl,
2047 p_parameter_counts in t_parameter_counts,
2048 p_api_module_id in number
2049 )
2050 is
2051 l_create_statement varchar2(32767);
2052 begin
2053 l_create_statement :=
2054 'create or replace force view ' || p_view_name || ' as' || c_newline ||
2055 'select ';
2056 --
2057 -- Add the common parameters.
2058 --
2059 l_create_statement := l_create_statement ||
2060 'batch_id batch_id' || c_newline ||
2061 ', batch_line_id batch_line_id' || c_newline ||
2062 ', api_module_id api_module_id' || c_newline ||
2063 ', line_status line_status' || c_newline ||
2064 ', user_sequence user_sequence' || c_newline ||
2065 ', link_value link_value' || c_newline ||
2066 ', business_group_name business_group_name'
2067 ;
2068 --
2069 -- Add the rest the batch lines parameters.
2070 --
2071 for i in 1 .. p_parameter_counts.total_parameters loop
2072 if p_parameter_tbl(i).batch_lines_seqno is not null then
2073 l_create_statement := l_create_statement ||
2074 ',' || c_newline || ' ' ||
2075 p_parameter_tbl(i).batch_lines_column || ' ' ||
2076 p_parameter_tbl(i).parameter_name;
2077 end if;
2078 end loop;
2079 --
2080 -- Terminate the create statement. Use a check option constraint so
2081 -- that the view may not be used to create/update any rows so that
2082 -- it cannot select the created/updated row.
2083 --
2084 l_create_statement :=
2085 l_create_statement || c_newline ||
2086 'from hr_pump_batch_lines' || c_newline ||
2087 'where api_module_id = ' || p_api_module_id || c_newline ||
2088 'with check option constraint HRDPV_CONS_' || p_api_module_id;
2089 --
2090 -- Create the view!
2091 --
2092 run_sql( l_create_statement );
2093 check_compile( p_view_name, 'VIEW' );
2094 end create_view;
2095
2096 -- ----------------------- gen_local_variable -----------------------------
2097 -- Description:
2098 -- For parameter p_xxxx, generates the name l_xxxx.
2099 -- ------------------------------------------------------------------------
2100 function gen_local_variable( p_parameter_name in varchar2 )
2101 return varchar2 is
2102 l_local_variable varchar2(30);
2103 begin
2104 l_local_variable := 'L_' || substr( p_parameter_name, 3 );
2105 return l_local_variable;
2106 end gen_local_variable;
2107
2108 -- ----------------------- generate_insert --------------------------------
2109 -- Description:
2110 -- Generates the insert procedure.
2111 -- ------------------------------------------------------------------------
2112 procedure generate_insert
2113 (
2114 p_api_module_id in number,
2115 p_parameter_tbl in t_parameter_tbl,
2116 p_parameter_counts in t_parameter_counts,
2117 p_header in out nocopy varchar2,
2118 p_body in out nocopy varchar2
2119 )
2120 is
2121 l_interface varchar2(32767);
2122 l_insert_part1 varchar2(32767);
2123 l_insert_part2 varchar2(32767);
2124 l_locals varchar2(32767) := null;
2125 l_pre_insert varchar2(32767) := null;
2126 l_local_var varchar2(30);
2127 l_indicator varchar2(30);
2128 begin
2129 l_locals := c_newline || 'blid number := p_data_pump_batch_line_id;';
2130 --
2131 l_interface := c_newline ||
2132 '(p_batch_id in number' || c_newline ||
2133 ',p_data_pump_batch_line_id in number default null' || c_newline ||
2134 ',p_data_pump_business_grp_name in varchar2 default null' || c_newline ||
2135 ',p_user_sequence in number default null' || c_newline ||
2136 ',p_link_value in number default null';
2137 --
2138 l_insert_part1 := c_newline ||
2139 'if blid is not null then' || c_newline ||
2140 'delete from hr_pump_batch_lines where batch_line_id = blid;' ||
2141 c_newline || 'delete from hr_pump_batch_exceptions' ||
2142 c_newline || 'where source_type = ''BATCH_LINE'' and source_id = blid;' ||
2143 c_newline || 'end if;'
2144 || c_newline ||
2145 'insert into hr_pump_batch_lines' || c_newline ||
2146 '(batch_id' || c_newline ||
2147 ',batch_line_id' || c_newline ||
2148 ',business_group_name' || c_newline ||
2149 ',api_module_id' || c_newline ||
2150 ',line_status' || c_newline ||
2151 ',user_sequence' || c_newline ||
2152 ',link_value';
2153 --
2154 l_insert_part2 := 'values' || c_newline ||
2155 '(p_batch_id' || c_newline ||
2156 ',nvl(blid,hr_pump_batch_lines_s.nextval)' || c_newline ||
2157 ',p_data_pump_business_grp_name' || c_newline ||
2158 ',' || to_char(p_api_module_id) || c_newline ||
2159 ',''U''' || c_newline ||
2160 ',p_user_sequence' || c_newline ||
2161 ',p_link_value';
2162 --
2163 for i in 1 .. p_parameter_counts.total_parameters loop
2164 l_indicator := null;
2165 --
2166 -- Only interested in batch lines parameters, and parameters that
2167 -- are in and in/out.
2168 --
2169 if p_parameter_tbl(i).batch_lines_seqno is not null and
2170 (p_parameter_tbl(i).in_out <> c_ptype_out or
2171 p_parameter_tbl(i).mapping_type = c_mapping_type_user_key )
2172 then
2173 --
2174 -- All parameters are in parameters.
2175 --
2176 l_interface :=
2177 l_interface || c_newline || ',' ||
2178 p_parameter_tbl(i).parameter_name || ' in ';
2179 --
2180 -- user_key parameters must be varchar2.
2181 --
2182 if p_parameter_tbl(i).mapping_type = c_mapping_type_user_key then
2183 l_interface := l_interface || 'varchar2';
2184 else
2185 l_interface :=
2186 l_interface || g_tbl_datatype( p_parameter_tbl(i).datatype );
2187 end if;
2188 --
2189 -- Default defaultable parameters to null so that they may be
2190 -- defaulted in the API call wrapper.
2191 --
2192 if p_parameter_tbl(i).defaultable then
2193 l_interface := l_interface || ' default null';
2194 end if;
2195 --
2196 -- Non-user-key, defaultable NUMBER/DATE parameters need an
2197 -- indicator variable as the '<NULL>' default value is not a
2198 -- valid NUMBER/DATE. This is only necessary if the default value
2199 -- is NOT NULL.
2200 --
2201 if p_parameter_tbl(i).mapping_type <> c_mapping_type_user_key and
2202 p_parameter_tbl(i).defaultable and
2203 not default_is_null(p_parameter_tbl(i).default_value) and
2204 (p_parameter_tbl(i).datatype = c_dtype_number or
2205 p_parameter_tbl(i).datatype = c_dtype_date) then
2206 l_indicator := 'I' || substr(p_parameter_tbl(i).parameter_name, 2);
2207 l_interface :=
2208 l_interface || c_newline || ',' || l_indicator ||
2209 ' in varchar2 default ''N''';
2210 end if;
2211 --
2212 l_insert_part1 :=
2213 l_insert_part1 || c_newline || ',' ||
2214 p_parameter_tbl(i).batch_lines_column;
2215 --
2216 l_insert_part2 :=
2217 l_insert_part2 || c_newline || ',';
2218 --
2219 -- If there is an indicator and the parameter is of type DATE then
2220 -- call the Date Decode function.
2221 --
2222 if l_indicator is not null and
2223 p_parameter_tbl(i).datatype = c_dtype_date then
2224 l_insert_part2 :=
2225 l_insert_part2 || 'dd(' || p_parameter_tbl(i).parameter_name ||
2226 ',' || l_indicator || ')';
2227 --
2228 -- Put in a to_char date format conversion for date parameters.
2229 --
2230 elsif p_parameter_tbl(i).datatype = c_dtype_date then
2231 l_insert_part2 :=
2232 l_insert_part2 || 'dc(' || p_parameter_tbl(i).parameter_name || ')';
2233 --
2234 -- Need to have local variables for boolean->string conversion for
2235 -- boolean parameters.
2236 --
2237 elsif p_parameter_tbl(i).datatype = c_dtype_boolean then
2238 l_local_var := gen_local_variable( p_parameter_tbl(i).parameter_name );
2239 l_locals :=
2240 l_locals || c_newline || ' ' || l_local_var || ' varchar2(5);';
2241 --
2242 l_pre_insert :=
2243 l_pre_insert || c_newline ||
2244 'if ' || p_parameter_tbl(i).parameter_name || ' is null then' ||
2245 c_newline ||
2246 ' ' || l_local_var || ' := null;' || c_newline ||
2247 'elsif ' || p_parameter_tbl(i).parameter_name || ' then' ||
2248 c_newline ||
2249 ' ' || l_local_var || ' := ''TRUE'';' || c_newline ||
2250 'else ' || c_newline ||
2251 ' ' || l_local_var || ' := ''FALSE'';' || c_newline ||
2252 'end if;';
2253 --
2254 l_insert_part2 := l_insert_part2 || l_local_var;
2255 --
2256 -- If there is an indicator and the parameter is of type NUMBER then
2257 -- call the Number Decode function.
2258 --
2259 elsif l_indicator is not null and
2260 p_parameter_tbl(i).datatype = c_dtype_number then
2261 l_insert_part2 :=
2262 l_insert_part2 || 'nd(' || p_parameter_tbl(i).parameter_name ||
2263 ',' || l_indicator || ')';
2264 else
2265 l_insert_part2 := l_insert_part2 || p_parameter_tbl(i).parameter_name;
2266 end if;
2267 end if;
2268 end loop;
2269 l_interface := l_interface || ')';
2270 l_insert_part1 := l_insert_part1 || ')' || c_newline;
2271 l_insert_part2 := l_insert_part2 || ');' || c_newline;
2272 --
2273 p_header := p_header || 'procedure insert_batch_lines' || l_interface || ';';
2274 p_body :=
2275 p_body || 'procedure insert_batch_lines' || l_interface || ' is';
2276 if l_locals is not null then
2277 p_body := p_body || l_locals;
2278 end if;
2279 p_body :=
2280 p_body || c_newline || 'begin';
2281 if l_pre_insert is not null then
2282 p_body := p_body || l_pre_insert;
2283 end if;
2284 p_body :=
2285 p_body || l_insert_part1 || l_insert_part2 || 'end insert_batch_lines;';
2286 end generate_insert;
2287
2288 -- ------------------------- gen_hr_api_vars ------------------------------
2289 -- Description:
2290 -- Generates code for global variables to hold values for hr_api.g_date,
2291 -- hr_api.g_number, and hr_api.g_varchar2. This is to save space in the call
2292 -- procedure.
2293 -- ------------------------------------------------------------------------
2294 procedure gen_hr_api_vars
2295 (p_body in out nocopy varchar2
2296 ) is
2297 begin
2298 p_body := p_body ||
2299 'dh constant date := hr_api.g_date;' || c_newline ||
2300 'nh constant number := hr_api.g_number;' || c_newline ||
2301 'vh constant varchar2(64) := hr_api.g_varchar2;' || c_newline;
2302 end gen_hr_api_vars;
2303
2304 -- ------------------------- gen_null_vars -------------------------------
2305 -- Description:
2306 -- Generates code for global variables to hold values for to_date(null),
2307 -- to_number(null), and to_char(null). This is to save space in the call
2308 -- procedure.
2309 -- ------------------------------------------------------------------------
2310 procedure gen_null_vars
2311 (p_body in out nocopy varchar2
2312 ) is
2313 begin
2314 p_body := p_body ||
2315 'c_sot constant date := to_date(''01010001'',''DDMMYYYY'');' ||
2316 c_newline ||
2317 'cn constant varchar2(32) := ''<NULL>'';' || c_newline ||
2318 'dn constant date := null;' || c_newline ||
2319 'nn constant number := null;' || c_newline ||
2320 'vn constant varchar2(1) := null;';
2321 end gen_null_vars;
2322 -- ------------------------- gen_to_calls ----------------------------------
2323 -- Description:
2324 -- Generates code for the to_date and to_number replacement functions (to
2325 -- save code space).
2326 -- Added function to do the to_char conversion for date also.
2327 -- ------------------------------------------------------------------------
2328 procedure gen_to_calls
2329 ( p_body in out nocopy varchar2, p_header in out nocopy varchar2 )
2330 is
2331 begin
2332 p_body :=
2333 p_body ||
2334 'function dc(p in date) return varchar2 is' || c_newline ||
2335 'begin' || c_newline ||
2336 'if p<c_sot then' || c_newline ||
2337 ' if p<>trunc(p) then' || c_newline ||
2338 ' return to_char(p,'''|| c_date_format3|| ''');' || c_newline ||
2339 ' end if;' || c_newline ||
2340 ' return to_char(p,''' || c_date_format2|| ''');' || c_newline ||
2341 'elsif p<>trunc(p) then' || c_newline ||
2342 ' return to_char(p,'''|| c_date_format1|| ''');' || c_newline ||
2343 'end if;' || c_newline ||
2344 'return to_char(p,''' || c_date_format|| ''');' || c_newline ||
2345 'end dc;' || c_newline;
2346 p_body :=
2347 p_body ||
2348 'function d(p in varchar2) return date is' || c_newline ||
2349 'begin' || c_newline ||
2350 'if length(p)='||c_date_format_len||' then' || c_newline ||
2351 'return to_date(p,''' || c_date_format || ''');' || c_newline ||
2352 'elsif length(p)='||c_date_format1_len||' then' || c_newline ||
2353 'return to_date(p,''' || c_date_format1 || ''');' || c_newline ||
2354 'elsif length(p)=' ||c_date_format2_len|| ' then' || c_newline ||
2355 'return to_date(p,''' || c_date_format2 || ''');' || c_newline ||
2356 'elsif length(p)=' ||c_date_format3_len|| ' then' || c_newline ||
2357 'return to_date(p,''' || c_date_format3 || ''');' || c_newline ||
2358 'end if;' || c_newline ||
2359 '-- Try default format as last resort.' || c_newline ||
2360 'return to_date(p,''' || c_date_format || ''');' || c_newline ||
2361 'end d;' || c_newline;
2362 p_body :=
2363 p_body ||
2364 'function n(p in varchar2) return number is' || c_newline ||
2365 'begin' || c_newline ||
2366 'return to_number(p);' || c_newline ||
2367 'end n;' || c_newline;
2368 p_body :=
2369 p_body ||
2370 'function dd(p in date,i in varchar2)' || c_newline ||
2371 'return varchar2 is' || c_newline ||
2372 'begin' || c_newline ||
2373 'if upper(i) = ''N'' then return dc(p);' || c_newline ||
2374 'else return cn; end if;' || c_newline ||
2375 'end dd;' || c_newline;
2376 p_body :=
2377 p_body ||
2378 'function nd(p in number,i in varchar2)' || c_newline ||
2379 'return varchar2 is' || c_newline ||
2380 'begin' || c_newline ||
2381 'if upper(i) = ''N'' then return to_char(p);' || c_newline ||
2382 'else return cn; end if;' || c_newline ||
2383 'end nd;';
2384 --
2385 -- Wanted to avoid putting this in the header, but the pragma requires
2386 -- that this is the case. Strictly, not necessarily for 8i onwards,
2387 -- but do in case of backport to R11.
2388 --
2389 p_header :=
2390 p_header || c_newline ||
2391 'function dc(p in date) return varchar2;' || c_newline ||
2392 'pragma restrict_references(dc,WNDS);' || c_newline;
2393 p_header :=
2394 p_header || c_newline ||
2395 'function d(p in varchar2) return date;' || c_newline ||
2396 'pragma restrict_references(d,WNDS);' || c_newline;
2397 p_header :=
2398 p_header ||
2399 'function n(p in varchar2) return number;' || c_newline ||
2400 'pragma restrict_references(n,WNDS);' || c_newline;
2401 p_header :=
2402 p_header ||
2403 'function dd(p in date,i in varchar2) return varchar2;' || c_newline ||
2404 'pragma restrict_references(dd,WNDS);' || c_newline;
2405 p_header :=
2406 p_header ||
2407 'function nd(p in number,i in varchar2) return varchar2;' || c_newline ||
2408 'pragma restrict_references(nd,WNDS);';
2409 end gen_to_calls;
2410
2411 -- ----------------------- gen_ins_user_key -------------------------------
2412 -- Description:
2413 -- Generates code for the ins_user_key procedure.
2414 -- ------------------------------------------------------------------------
2415 procedure gen_ins_user_key( p_body in out nocopy varchar2 )
2416 is
2417 begin
2418 p_body :=
2419 p_body ||
2420 'procedure iuk' || c_newline ||
2421 '(p_batch_line_id in number,' || c_newline ||
2422 'p_user_key_value in varchar2,' || c_newline ||
2423 'p_unique_key_id in number)' || c_newline ||
2424 'is' || c_newline ||
2425 'begin' || c_newline ||
2426 'hr_data_pump.entry(''ins_user_key'');' || c_newline ||
2427 'insert into hr_pump_batch_line_user_keys' || c_newline ||
2428 '(user_key_id, batch_line_id,user_key_value,unique_key_id)' ||
2429 c_newline ||
2430 'values' || c_newline ||
2431 '(hr_pump_batch_line_user_keys_s.nextval,' || c_newline ||
2432 'p_batch_line_id,' || c_newline ||
2433 'p_user_key_value,' || c_newline ||
2434 'p_unique_key_id);' || c_newline ||
2435 'hr_data_pump.exit(''ins_user_key'');' || c_newline ||
2436 'end iuk;';
2437 end gen_ins_user_key;
2438
2439 -- ------------------------- gen_cursor_field -----------------------------
2440 -- Description:
2441 -- For parameter p_xxxx, generates the field in the cursor record.
2442 -- ------------------------------------------------------------------------
2443 function gen_cursor_field
2444 (p_parameter_name in varchar2
2445 ,p_prefix in boolean default true
2446 )
2447 return varchar2 is
2448 l_name varchar2(64);
2449 begin
2450 --
2451 -- Shorten the name of the cursor fields.
2452 --
2453 l_name := replace(p_parameter_name, c_parameter_value_col,
2454 c_cursor_value_col);
2455 --
2456 -- Get rid of numbers prefixed by 0 or 00.
2457 --
2458 l_name := replace(l_name, c_cursor_value_col || '00', c_cursor_value_col);
2459 l_name := replace(l_name, c_cursor_value_col || '0', c_cursor_value_col);
2460 --
2461 -- Add any necessary prefix.
2462 --
2463 if p_prefix then
2464 l_name := 'c.' || l_name;
2465 end if;
2466 return l_name;
2467 end gen_cursor_field;
2468
2469 -- ---------------------------- add_to_locals -----------------------------
2470 -- Description:
2471 -- If required, generate a local variable for a parameter and add it to
2472 -- the local variable text. Overloaded for parameters with 'FUNCTION'
2473 -- mapping type.
2474 -- ------------------------------------------------------------------------
2475 procedure add_to_locals
2476 (
2477 p_locals in out nocopy varchar2,
2478 p_parameter in t_parameter,
2479 p_local_variable in out nocopy varchar2
2480 )
2481 is
2482 begin
2483 --
2484 -- Boolean parameters need a local variable so that a string conversion
2485 -- may be performed pre-call or post-call.
2486 --
2487 if p_parameter.datatype = c_dtype_boolean and
2488 p_parameter.batch_lines_seqno is not null and
2489 not special_parameter( p_parameter.parameter_name )
2490 then
2491 p_local_variable := gen_local_variable( p_parameter.parameter_name );
2492 p_locals := p_locals || c_newline || p_local_variable ||
2493 ' boolean;';
2494 return;
2495 end if;
2496 --
2497 -- For API out parameters that are user_key-mapped a local variable is
2498 -- required so that the user_key may be inserted into
2499 -- hr_pump_batch_line_user_keys.
2500 --
2501 if p_parameter.mapping_type = c_mapping_type_user_key and
2502 p_parameter.api_seqno is not null and
2503 (p_parameter.in_out = c_ptype_out or p_parameter.in_out = c_ptype_in_out)
2504 then
2505 p_local_variable := gen_local_variable( p_parameter.mapping_definition );
2506 p_locals := p_locals || c_newline || p_local_variable || ' ' ||
2507 g_tbl_datatype(p_parameter.datatype) || ';';
2508 return;
2509 end if;
2510 --
2511 -- For long parameters a local variable is required to check for the
2512 -- string '<NULL>', and to pass results back (to allow common code for
2513 -- in, in/out, and out parameters).
2514 --
2515 if p_parameter.datatype = c_dtype_long and
2516 p_parameter.batch_lines_seqno is not null
2517 then
2518 p_local_variable := gen_local_variable( p_parameter.parameter_name );
2519 p_locals :=
2520 p_locals || c_newline || p_local_variable || ' varchar2(32767);';
2521 end if;
2522 end add_to_locals;
2523 --
2524 procedure add_to_locals
2525 (
2526 p_locals in out nocopy varchar2,
2527 p_function in t_function,
2528 p_parameter_tbl in t_parameter_tbl,
2529 p_local_variable in out nocopy varchar2
2530 )
2531 is
2532 l_parameter t_parameter;
2533 l_seqno number;
2534 l_datatype number;
2535 begin
2536 --
2537 -- Derive local variable name from the name of the parameter for which
2538 -- the mapping function exists. The parameter type is the return type of
2539 -- the function.
2540 --
2541 l_seqno := p_function.seqno;
2542 l_datatype := p_function.ret_type;
2543 l_parameter := p_parameter_tbl(l_seqno);
2544 p_local_variable := gen_local_variable( l_parameter.parameter_name );
2545 p_locals := p_locals || c_newline || p_local_variable || ' ';
2546 if l_parameter.datatype = c_dtype_varchar2 then
2547 p_locals := p_locals || 'varchar2(2000);';
2548 else
2549 p_locals := p_locals || g_tbl_datatype(l_datatype) || ';';
2550 end if;
2551 end add_to_locals;
2552
2553 -- -------------------------- add_to_precall ------------------------------
2554 -- Description:
2555 -- Adds any code that needs to be executed before the API is called.
2556 -- Overloaded versions for ordinary parameters and parameters with
2557 -- 'FUNCTION' mapping type.
2558 -- ------------------------------------------------------------------------
2559 procedure add_to_precall
2560 (
2561 p_precall in out nocopy varchar2,
2562 p_parameter in t_parameter,
2563 p_local_variable in varchar2
2564 )
2565 is
2566 l_cursor_field varchar2(64);
2567 begin
2568 --
2569 -- For long in parameters, check for '<NULL>'.
2570 --
2571 if p_parameter.datatype = c_dtype_long and
2572 p_parameter.batch_lines_seqno is not null and
2573 ( p_parameter.in_out = c_ptype_in or p_parameter.in_out = c_ptype_in_out )
2574 then
2575 --
2576 -- The local varchar2 variable is set using the long parameter value.
2577 --
2578 l_cursor_field := gen_cursor_field( p_parameter.batch_lines_column );
2579 p_precall :=
2580 p_precall || c_newline || '--' || c_newline ||
2581 p_local_variable || ' := ' || l_cursor_field || ';' || c_newline;
2582 --
2583 -- For a defaultable parameter, the code needs to check for a null
2584 -- value and set the default accordingly.
2585 --
2586 if p_parameter.defaultable and
2587 not default_is_null(p_parameter.default_value)
2588 then
2589 p_precall :=
2590 p_precall ||
2591 'if ' || p_local_variable || ' is null then' || c_newline ||
2592 p_local_variable || ' := ' || p_parameter.call_default_value || ';' ||
2593 c_newline || 'elsif ';
2594 else
2595 p_precall := p_precall || 'if ';
2596 end if;
2597 --
2598 -- If the local variable has the value '<NULL>' then it is set to null.
2599 --
2600 p_precall :=
2601 p_precall || p_local_variable || ' = cn then' || c_newline ||
2602 p_local_variable || ' := null;' || c_newline || 'end if;';
2603 end if;
2604 --
2605 -- For boolean parameters need to convert local variable.
2606 --
2607 if p_parameter.datatype = c_dtype_boolean and
2608 p_parameter.batch_lines_seqno is not null and
2609 not special_parameter( p_parameter.parameter_name ) and
2610 ( p_parameter.in_out = c_ptype_in or p_parameter.in_out = c_ptype_in_out )
2611 then
2612 l_cursor_field := gen_cursor_field( p_parameter.batch_lines_column );
2613 p_precall :=
2614 p_precall || c_newline || '--' || c_newline ||
2615 'if upper(' || l_cursor_field || ') = ''TRUE'' then' ||
2616 c_newline || p_local_variable || ' := true;' || c_newline ||
2617 'elsif upper(' || l_cursor_field || ') = ''FALSE'' then' ||
2618 c_newline || p_local_variable || ' := false;' || c_newline;
2619 if p_parameter.defaultable and
2620 not default_is_null(p_parameter.default_value)
2621 then
2622 p_precall := p_precall ||
2623 'elsif ' || gen_def_check_name(l_cursor_field) || ' is not null then' ||
2624 c_newline;
2625 elsif p_parameter.defaultable then
2626 p_precall := p_precall ||
2627 'elsif ' || l_cursor_field || ' is not null then' || c_newline;
2628 else
2629 p_precall := p_precall || 'else' || c_newline;
2630 end if;
2631 p_precall := p_precall ||
2632 'hr_utility.set_message(800,''HR_50327_DP_TYPE_ERR'');' || c_newline ||
2633 'hr_utility.set_message_token(''TYPE'',''BOOLEAN'');' || c_newline ||
2634 'hr_utility.set_message_token(''PARAMETER'',''' ||
2635 p_parameter.parameter_name || ''');' || c_newline ||
2636 'hr_utility.set_message_token(''VALUE'',' || l_cursor_field ||
2637 ');' || c_newline ||
2638 'hr_utility.set_message_token(''TABLE'',''HR_PUMP_BATCH_LINES'');' ||
2639 c_newline ||
2640 'hr_utility.raise_error;' || c_newline ||
2641 'end if;';
2642 return;
2643 end if;
2644 end add_to_precall;
2645 --
2646 procedure add_to_precall
2647 (
2648 p_precall in out nocopy varchar2,
2649 p_parameter in t_parameter,
2650 p_function in t_function,
2651 p_function_call_tbl in t_function_parameter_tbl,
2652 p_local_variable in varchar2
2653 )
2654 is
2655 --
2656 -- Function call parameter.
2657 --
2658 l_call_param t_function_parameter;
2659 --
2660 -- Function call statement.
2661 --
2662 l_function_call varchar2(32767);
2663 --
2664 -- Parameter name to be used in function call.
2665 --
2666 l_param_call_name varchar2(64);
2667 --
2668 -- Parameter name to be used in checking code.
2669 --
2670 l_param_chk_name varchar2(64);
2671 --
2672 -- Checking statement to set mapped value to NULL.
2673 --
2674 l_null_check varchar2(32767) := null;
2675 --
2676 -- Checking statement to set the mapped value to its default.
2677 --
2678 l_def_check varchar2(32767) := null;
2679 --
2680 -- Procedure to add to one of the checking if-statements that handle
2681 -- NULL or defaulted mapping function parameters.
2682 --
2683 procedure add2chklist
2684 (p_chk_name in varchar2
2685 ,p_is_null in boolean
2686 ,p_check_list in out nocopy varchar2
2687 ) is
2688 begin
2689 --
2690 -- Handle case of NOT NULL check list, each check is add as an
2691 -- OR-condition to an existing statement.
2692 --
2693 if not p_check_list is null then
2694 p_check_list := p_check_list || ' or' || c_newline;
2695 end if;
2696 --
2697 -- Decide whether to add '<name> is null' or '<name>=cn' to
2698 -- the list (cn is the special string <NULL>).
2699 --
2700 if p_is_null then
2701 p_check_list := p_check_list || p_chk_name || ' is null';
2702 else
2703 p_check_list := p_check_list || p_chk_name || '=cn';
2704 end if;
2705 end add2chklist;
2706 --
2707 begin
2708 --
2709 p_precall := p_precall || c_newline || '--' || c_newline;
2710 --------------------------------------
2711 -- Mapping function has parameters. --
2712 --------------------------------------
2713 if p_function.index1 is not null then
2714 --
2715 -- Build up parameter list for function.
2716 --
2717 l_function_call :=
2718 p_local_variable || ' := ' || c_newline ||
2719 p_function.package_name || '.' || p_function.function_name ||
2720 c_newline || '(';
2721 --
2722 -- Build up parameter check code.
2723 --
2724 for i in p_function.index1 .. p_function.index2 loop
2725 l_call_param := p_function_call_tbl(i);
2726 --
2727 -- Only add comma argument separator if this is the second or a later
2728 -- parameter in the function call.
2729 --
2730 if i <> p_function.index1 then
2731 l_function_call := l_function_call || c_newline || ',';
2732 end if;
2733 --
2734 -- Function parameters come from the batch lines table, therefore
2735 -- they are accessed from the c cursor.
2736 --
2737 if l_call_param.parameter_name = 'P_BUSINESS_GROUP_ID' then
2738 l_param_call_name := 'P_BUSINESS_GROUP_ID';
2739 elsif l_call_param.parameter_name = 'P_DATA_PUMP_ALWAYS_CALL'
2740 then
2741 --
2742 -- P_DATA_PUMP_ALWAYS_CALL is a dummy parameter, so just pass in
2743 -- NULL for it.
2744 --
2745 l_param_call_name := 'null';
2746 else
2747 l_param_call_name :=
2748 gen_cursor_field( l_call_param.batch_lines_column );
2749 end if;
2750 l_function_call :=
2751 l_function_call || l_call_param.function_parameter || ' => ' ||
2752 l_param_call_name;
2753
2754 --
2755 -- Perform checks for NULL and HR_API defaulted parameters if
2756 -- ALWAYS_CALL is false. The checks prevent the function being called
2757 -- if a parameter is NULL or HR_API defaulted.
2758 --
2759 -- The P_BUSINESS_GROUP_ID parameter is exempted from these checks.
2760 --
2761 -- Where the user has supplied the value <NULL> for a mapping
2762 -- function parameter, then the mapped parameter is set to NULL.
2763 -- Where the user has supplied a default value of NULL or an HR_API
2764 -- default, the mapped parameter is set to its default value (or
2765 -- NULL if it does not have a default).
2766 ---------------------------------------------------------------------
2767 -- DO NOT ALTER THE FOLLOWING CODE UNLESS YOU FULLY UNDERSTAND HOW --
2768 -- THE MAPPER CODE WORKS. --
2769 ---------------------------------------------------------------------
2770 if not p_function.always_call and
2771 l_param_call_name <> 'P_BUSINESS_GROUP_ID' then
2772 -------------------------------------------------------------------
2773 -- Case 1: The parameter being mapped is not defaultable, or the --
2774 -- parameter being mapped has a default of NULL. --
2775 -------------------------------------------------------------------
2776 if not p_parameter.defaultable or
2777 (p_parameter.defaultable and
2778 default_is_null(p_parameter.default_value)) then
2779 ----------------------------------------
2780 -- 1.1 Call parameter is defaultable. --
2781 ----------------------------------------
2782 if l_call_param.defaultable then
2783 --
2784 -- 1.1.1 NULL default.
2785 --
2786 if default_is_null(l_call_param.default_value) then
2787 add2chklist
2788 (p_chk_name => l_param_call_name
2789 ,p_is_null => true
2790 ,p_check_list => l_null_check
2791 );
2792 --
2793 -- 1.1.2 HR_API default.
2794 --
2795 elsif default_is_hr_api(l_call_param.default_value) then
2796 l_param_chk_name := gen_def_check_name(l_param_call_name);
2797 add2chklist
2798 (p_chk_name => l_param_chk_name
2799 ,p_is_null => true
2800 ,p_check_list => l_null_check
2801 );
2802 add2chklist
2803 (p_chk_name => l_param_chk_name
2804 ,p_is_null => false
2805 ,p_check_list => l_null_check
2806 );
2807 --
2808 -- 1.1.3 Other default value. The default value can be
2809 -- passed to the mapping function.
2810 --
2811 else
2812 add2chklist
2813 (p_chk_name => gen_def_check_name(l_param_call_name)
2814 ,p_is_null => false
2815 ,p_check_list => l_null_check
2816 );
2817 end if;
2818 --------------------------------------------
2819 -- 1.2 Call parameter is not defaultable. --
2820 --------------------------------------------
2821 else
2822 add2chklist
2823 (p_chk_name => l_param_call_name
2824 ,p_is_null => true
2825 ,p_check_list => l_null_check
2826 );
2827 end if;
2828 ------------------------------------------------------------------
2829 -- Case 2: The parameter being mapped is defaultable and has a --
2830 -- NOT NULL default value. --
2831 ------------------------------------------------------------------
2832 else
2833 ----------------------------------------
2834 -- 2.1 Call parameter is defaultable. --
2835 ----------------------------------------
2836 if l_call_param.defaultable then
2837 --
2838 -- 2.1.1 NULL default.
2839 --
2840 if default_is_null(l_call_param.default_value) then
2841 l_param_chk_name := gen_def_check_name(l_param_call_name);
2842 add2chklist
2843 (p_chk_name => l_param_chk_name
2844 ,p_is_null => false
2845 ,p_check_list => l_null_check
2846 );
2847 add2chklist
2848 (p_chk_name => l_param_chk_name
2849 ,p_is_null => true
2850 ,p_check_list => l_def_check
2851 );
2852 --
2853 -- 2.1.2 HR_API default.
2854 --
2855 elsif default_is_hr_api(l_call_param.default_value) then
2856 l_param_chk_name := gen_def_check_name(l_param_call_name);
2857 add2chklist
2858 (p_chk_name => l_param_chk_name
2859 ,p_is_null => false
2860 ,p_check_list => l_null_check
2861 );
2862 add2chklist
2863 (p_chk_name => l_param_chk_name
2864 ,p_is_null => true
2865 ,p_check_list => l_def_check
2866 );
2867 --
2868 -- 2.1.3 Other default value. The default value
2869 -- can be passed to the mapping function.
2870 --
2871 else
2872 add2chklist
2873 (p_chk_name => gen_def_check_name(l_param_call_name)
2874 ,p_is_null => false
2875 ,p_check_list => l_null_check
2876 );
2877 end if;
2878 --------------------------------------------
2879 -- 2.2 Call parameter is not defaultable. --
2880 --------------------------------------------
2881 else
2882 add2chklist
2883 (p_chk_name => l_param_call_name
2884 ,p_is_null => true
2885 ,p_check_list => l_null_check
2886 );
2887 end if;
2888 end if;
2889 end if;
2890 end loop;
2891 --
2892 -- Terminate the function call.
2893 --
2894 l_function_call := l_function_call || ');';
2895 ---------------------------------------------
2896 -- Add the parameter checks, if necessary. --
2897 ---------------------------------------------
2898 if l_null_check is not null or l_def_check is not null then
2899 --
2900 -- NULL check comes first.
2901 --
2902 if l_null_check is not null then
2903 p_precall := p_precall ||
2904 'if ' || l_null_check || ' then' || c_newline ||
2905 p_local_variable || ':=' ||
2906 g_tbl_call_default_null(p_parameter.datatype) || ';' || c_newline;
2907 end if;
2908 --
2909 -- Handle default value check.
2910 --
2911 if l_def_check is not null then
2912 if l_null_check is null then
2913 l_def_check := 'if ' || l_def_check;
2914 else
2915 l_def_check := 'elsif ' || l_def_check;
2916 end if;
2917 p_precall := p_precall ||
2918 l_def_check || ' then ' || c_newline ||
2919 p_local_variable || ':=' || p_parameter.call_default_value || ';'
2920 || c_newline;
2921 end if;
2922 --
2923 -- Need ELSE-part for the function call to go into.
2924 --
2925 p_precall := p_precall || 'else' || c_newline;
2926 end if;
2927 --
2928 -- Add the function call to the precall text.
2929 --
2930 p_precall := p_precall || l_function_call;
2931 --
2932 -- Terminate the precall text by ending the parameter check.
2933 --
2934 if l_null_check is not null or l_def_check is not null then
2935 p_precall := p_precall || c_newline || 'end if;';
2936 end if;
2937 -----------------------------------------
2938 -- Mapping function has no parameters. --
2939 -----------------------------------------
2940 else
2941 --
2942 l_function_call :=
2943 p_local_variable || ' := ' || c_newline ||
2944 p_function.package_name || '.' || p_function.function_name;
2945 --
2946 -- No parameter list, so no brackets.
2947 --
2948 p_precall := p_precall || l_function_call || ';';
2949 end if;
2950 end add_to_precall;
2951
2952 -- ------------------------- add_to_call ----------------------------------
2953 -- Description:
2954 -- Builds up the API call a parameter at a time.
2955 -- ------------------------------------------------------------------------
2956 procedure add_to_call
2957 (
2958 p_call in out nocopy varchar2,
2959 p_parameter in t_parameter,
2960 p_local_variable in varchar2,
2961 p_first_time in out nocopy boolean
2962 )
2963 is
2964 l_local_variable varchar2(30);
2965 begin
2966 --
2967 -- Only API parameters are passed to the API call.
2968 --
2969 if p_parameter.api_seqno is not null then
2970 --
2971 -- Only add comma argument separator if this is the second or a later
2972 -- parameter.
2973 --
2974 if p_first_time then
2975 p_first_time := false;
2976 else
2977 p_call := p_call || c_newline || ',';
2978 end if;
2979 --
2980 -- p_validate maps onto local parameter l_validate.
2981 --
2982 if p_parameter.parameter_name = 'P_VALIDATE' then
2983 p_call := p_call || 'p_validate => l_validate';
2984 return;
2985 end if;
2986 --
2987 -- p_business_group_id is passed in directly from the wrapper.
2988 --
2989 if p_parameter.parameter_name = 'P_BUSINESS_GROUP_ID' then
2990 p_call := p_call || 'p_business_group_id => p_business_group_id';
2991 return;
2992 end if;
2993 --
2994 -- If the parameter has an associated local variable then pass in that
2995 -- local variable.
2996 --
2997 if p_local_variable is not null then
2998 if p_parameter.mapping_type = c_mapping_type_user_key then
2999 p_call :=
3000 p_call || p_parameter.mapping_definition || ' => ' || p_local_variable;
3001
3002 if g_debug then
3003 hr_utility.trace
3004 (p_parameter.parameter_name || ':' ||
3005 p_parameter.mapping_definition || ':' || p_local_variable
3006 );
3007 end if;
3008
3009 else
3010 p_call :=
3011 p_call || p_parameter.parameter_name || ' => ' || p_local_variable;
3012 end if;
3013 return;
3014 end if;
3015 --
3016 -- Generate a local variable for parameters mapped onto functions.
3017 --
3018 if p_parameter.mapping_type = c_mapping_type_function then
3019 l_local_variable := gen_local_variable( p_parameter.parameter_name );
3020 p_call :=
3021 p_call || p_parameter.parameter_name || ' => ' || l_local_variable;
3022 return;
3023 end if;
3024 --
3025 -- Only possible case is a batch lines parameter which can be accessed
3026 -- from the c cursor.
3027 --
3028 p_call :=
3029 p_call || p_parameter.parameter_name || ' => ' ||
3030 gen_cursor_field( p_parameter.batch_lines_column );
3031 end if;
3032 end add_to_call;
3033
3034 -- ------------------------- add_to_cursor --------------------------------
3035 -- Description:
3036 -- Adds a line for a parameter to the c cursor in the API call wrapper.
3037 -- ------------------------------------------------------------------------
3038 procedure add_to_cursor
3039 (
3040 p_cursor in out nocopy varchar2,
3041 p_parameter in t_parameter,
3042 p_effdate_parameter in varchar2 default null,
3043 p_langcode_parameter in varchar2 default null
3044 )
3045 is
3046 l_column_name varchar2(64); -- Holds column name for this parameter.
3047 begin
3048 --
3049 -- Only update cursor text for batch lines parameters.
3050 --
3051 if p_parameter.batch_lines_seqno is not null then
3052 --
3053 l_column_name := 'l.' || p_parameter.batch_lines_column;
3054 --
3055 p_cursor := p_cursor || ',' || c_newline;
3056 --
3057 if p_parameter.datatype = c_dtype_long or
3058 ( p_parameter.in_out = c_ptype_out and
3059 p_parameter.datatype <> c_dtype_date )
3060 then
3061 --
3062 -- long, and non-date out-only parameters are returned
3063 -- unchanged from batch lines table.
3064 --
3065 p_cursor := p_cursor || l_column_name;
3066 else
3067 --
3068 -- Pass the parameter in a decode statement. A parameter value of
3069 -- '<NULL>' results in the returned value being null. date out-only
3070 -- parameters are handled here so that the cursor field has the
3071 -- correct type.
3072 --
3073 p_cursor :=
3074 p_cursor ||
3075 'decode(' || l_column_name || ',cn,';
3076 if p_parameter.datatype = c_dtype_date then
3077 --
3078 -- This needs to be done so that the return type is date, otherwise
3079 -- date value passed to the API depends on the NLS date format.
3080 --
3081 p_cursor := p_cursor || 'dn,';
3082 elsif p_parameter.datatype = c_dtype_number then
3083 --
3084 -- Do proper to_number conversion.
3085 --
3086 p_cursor := p_cursor || 'nn,';
3087 else
3088 p_cursor := p_cursor || 'vn,';
3089 end if;
3090 --
3091 -- If the parameter is defaultable then pass in the default value
3092 -- if the parameter returned from batch lines is null.
3093 --
3094 if p_parameter.defaultable then
3095 p_cursor :=
3096 p_cursor ||
3097 'vn,' || p_parameter.call_default_value || ',';
3098 end if;
3099 --
3100 -- Set up the defaults for the decode statement.
3101 --
3102 if p_parameter.mapping_type = c_mapping_type_lookup then
3103 --
3104 -- If the parameter is a lookup parameter then the default value
3105 -- for the decode statement is a call to hr_pump_get.gl.
3106 --
3107 p_cursor :=
3108 p_cursor || c_newline ||
3109 ' hr_pump_get.gl(' || l_column_name ||
3110 ',''' || p_parameter.mapping_definition || '''';
3111 --
3112 -- Add effective date parameter, if one is available. If such
3113 -- a parameter is not available hr_pump_get.gl uses the system
3114 -- date.
3115 --
3116 if p_effdate_parameter is not null then
3117 p_cursor := p_cursor || ',d(l.' || p_effdate_parameter || ')';
3118 else
3119 p_cursor := p_cursor || ',dn';
3120 end if;
3121 --
3122 -- Add the language code parameter, if one is available. If such
3123 -- a parameter is not available hr_pump_get.gl uses the value
3124 -- from USERENV('LANG').
3125 --
3126 if p_langcode_parameter is not null then
3127 p_cursor := p_cursor || ',l.' || p_langcode_parameter;
3128 else
3129 p_cursor := p_cursor || ',vn';
3130 end if;
3131 p_cursor := p_cursor || ')';
3132 elsif p_parameter.datatype = c_dtype_date then
3133 --
3134 -- For date parameters, it is necessary to do a to_date conversion.
3135 --
3136 p_cursor :=
3137 p_cursor || 'd(' || l_column_name || ')';
3138 elsif p_parameter.datatype = c_dtype_number then
3139 --
3140 -- For number parameters, it is necessary to do a to_number conversion.
3141 --
3142 p_cursor :=
3143 p_cursor || 'n(' || l_column_name || ')';
3144 else
3145 --
3146 -- Pass the column value itself.
3147 --
3148 p_cursor := p_cursor || l_column_name;
3149 end if;
3150 --
3151 -- Terminate the decode call.
3152 --
3153 p_cursor := p_cursor || ')';
3154 end if;
3155 --
3156 -- Cursor line is terminated with the batch lines column name.
3157 --
3158 p_cursor := p_cursor || ' ' ||
3159 gen_cursor_field(p_parameter.batch_lines_column, false);
3160 --
3161 -- If the parameter is defaultable then add the defaulting check column.
3162 -- Do not do this for long parameters because the default checking
3163 -- cannot be done using the decode function.
3164 --
3165 if p_parameter.defaultable and
3166 p_parameter.datatype <> c_dtype_long and
3167 hr_pump_meta_mapper.g_standard_generate
3168 then
3169 p_cursor :=
3170 p_cursor || ',' || c_newline ||
3171 'l.' || p_parameter.batch_lines_column || ' ' ||
3172 gen_def_check_name
3173 (gen_cursor_field(p_parameter.batch_lines_column, false)
3174 );
3175 end if;
3176 end if;
3177 end add_to_cursor;
3178
3179 -- -------------------------- add_to_postcall -----------------------------
3180 -- Description:
3181 -- Does work after the API call e.g. converting boolean values back.
3182 -- ------------------------------------------------------------------------
3183 procedure add_to_postcall
3184 (
3185 p_postcall in out nocopy varchar2,
3186 p_parameter in t_parameter,
3187 p_local_variable in varchar2,
3188 p_first_postcall in out nocopy boolean
3189 )
3190 is
3191 l_cursor_field varchar2(64);
3192 begin
3193 if (p_parameter.in_out = c_ptype_out or p_parameter.in_out = c_ptype_in_out)
3194 and p_parameter.batch_lines_seqno is not null
3195 then
3196 l_cursor_field := gen_cursor_field( p_parameter.batch_lines_column );
3197 --
3198 -- For user_key-mapped parameters need to insert into
3199 -- hr_pump_batch_line_user_keys.
3200 --
3201 if p_parameter.mapping_type = c_mapping_type_user_key then
3202 if p_first_postcall then
3203 p_first_postcall := false;
3204 else
3205 p_postcall := p_postcall || c_newline;
3206 end if;
3207 p_postcall :=
3208 p_postcall || '--' || c_newline ||
3209 'iuk(p_batch_line_id,' || l_cursor_field ||
3210 ',' || p_local_variable || ');';
3211 return;
3212 end if;
3213 --
3214 -- Convert boolean parameters back.
3215 --
3216 if p_parameter.datatype = c_dtype_boolean then
3217 if p_first_postcall then
3218 p_first_postcall := false;
3219 else
3220 p_postcall := p_postcall || c_newline;
3221 end if;
3222 p_postcall :=
3223 p_postcall || '--' || c_newline ||
3224 'if ' || p_local_variable || ' then' || c_newline ||
3225 l_cursor_field || ' := ''TRUE'';' || c_newline ||
3226 'else' || c_newline ||
3227 l_cursor_field || ' := ''FALSE'';' || c_newline ||
3228 'end if;';
3229 return;
3230 end if;
3231 end if;
3232 end add_to_postcall;
3233
3234 -- --------------------------- add_to_update ------------------------------
3235 -- Description:
3236 -- Adds to the update statement to write back OUT parameters to the
3237 -- batch lines table.
3238 -- ------------------------------------------------------------------------
3239 procedure add_to_update
3240 (
3241 p_update in out nocopy varchar2,
3242 p_parameter in t_parameter,
3243 p_local_variable in varchar2,
3244 p_first_update in out nocopy boolean
3245 )
3246 is
3247 l_cursor_field varchar2(64);
3248 l_column varchar2(64);
3249 begin
3250 --
3251 -- Only interested in batch lines out parameters.
3252 --
3253 if p_parameter.batch_lines_seqno is not null and
3254 ( p_parameter.in_out = c_ptype_out or p_parameter.in_out = c_ptype_in_out )
3255 then
3256 if p_first_update then
3257 p_first_update := false;
3258 else
3259 p_update := p_update || ',';
3260 end if;
3261 --
3262 -- For long parameters we use the varchar2 local variable. For other
3263 -- parameters, the cursor field is used.
3264 --
3265 if p_parameter.datatype = c_dtype_long then
3266 l_cursor_field := p_local_variable;
3267 else
3268 l_cursor_field := gen_cursor_field( p_parameter.batch_lines_column );
3269 end if;
3270 l_column := 'l.' || p_parameter.batch_lines_column;
3271 p_update :=
3272 p_update || c_newline ||
3273 l_column || ' = decode(' || l_cursor_field || ',null,cn,';
3274 if p_parameter.datatype = c_dtype_date then
3275 p_update := p_update || 'dc(' || l_cursor_field || ')';
3276 else
3277 p_update := p_update || l_cursor_field;
3278 end if;
3279 p_update := p_update || ')';
3280 end if;
3281 end add_to_update;
3282
3283 -- --------------------------- generate_call ------------------------------
3284 -- Description:
3285 -- Generates the wrapper code for calling the API.
3286 -- ------------------------------------------------------------------------
3287 procedure generate_call
3288 (
3289 p_module_package in varchar2,
3290 p_module_name in varchar2,
3291 p_parameter_tbl in t_parameter_tbl,
3292 p_function_tbl in t_function_tbl,
3293 p_function_call_tbl in t_function_parameter_tbl,
3294 p_parameter_counts in t_parameter_counts,
3295 p_header in out nocopy varchar2,
3296 p_body in out nocopy varchar2,
3297 p_body_pieces in out nocopy dbms_sql.varchar2s
3298 )
3299 is
3300 l_locals varchar2(32767); -- Local variables for call procedure.
3301 l_locals1 varchar2(32767); -- Local variables for call procedure.
3302 l_cursor varchar2(32767); -- Cursor for getting batch lines data.
3303 l_precall varchar2(32767); -- Pre-call code e.g. boolean conversion.
3304 l_precall1 varchar2(32767); -- Pre-call code e.g. boolean conversion.
3305 l_call varchar2(32767); -- The API call itself.
3306 l_call1 varchar2(32767); -- The API call itself.
3307 l_call2 varchar2(32767); -- The API call itself.
3308 l_postcall varchar2(32767); -- Post-call code e.g. boolean conversion.
3309 l_update varchar2(32767); -- Update batch lines with output data.
3310 l_local_variable varchar2(30); -- Local variable for this parameter.
3311 l_parameter t_parameter;
3312 l_first_call boolean := true;
3313 l_first_update boolean := true;
3314 l_first_postcall boolean := true;
3315 l_effdate varchar2(30); -- P_EFFECTIVE_DATE batch lines column.
3316 l_langcode varchar2(30); -- P_LANGUAGE_CODE batch lines column.
3317 begin
3318 --
3319 -- Get the effective date parameter's batch lines column name.
3320 --
3321 l_effdate := null;
3322 for i in 1 .. p_parameter_counts.total_parameters loop
3323 if (upper(p_parameter_tbl(i).parameter_name) = 'P_EFFECTIVE_DATE' or
3324 (p_parameter_tbl(i).mapping_type = c_mapping_type_aliased and
3325 p_parameter_tbl(i).mapping_definition = 'P_EFFECTIVE_DATE')) and
3326 p_parameter_tbl(i).datatype = c_dtype_date
3327 then
3328 l_effdate := p_parameter_tbl(i).batch_lines_column;
3329 exit;
3330 end if;
3331 end loop;
3332 --
3333 -- Get the language code parameter's batch lines column name.
3334 --
3335 l_langcode := null;
3336 for i in 1 .. p_parameter_counts.total_parameters loop
3337 if (upper(p_parameter_tbl(i).parameter_name) = 'P_LANGUAGE_CODE' or
3338 (p_parameter_tbl(i).mapping_type = c_mapping_type_aliased and
3339 p_parameter_tbl(i).mapping_definition = 'P_LANGUAGE_CODE')) and
3340 p_parameter_tbl(i).datatype = c_dtype_varchar2
3341 then
3342 l_langcode := p_parameter_tbl(i).batch_lines_column;
3343 exit;
3344 end if;
3345 end loop;
3346 --
3347 -- Simple header text addition.
3348 --
3349 p_header :=
3350 p_header ||
3351 'procedure call' || c_newline ||
3352 '(p_business_group_id in number,' || c_newline ||
3353 'p_batch_line_id in number);' || c_newline;
3354 --
3355 p_body :=
3356 p_body ||
3357 'procedure call' || c_newline ||
3358 '(p_business_group_id in number,' || c_newline ||
3359 'p_batch_line_id in number) is';
3360 --
3361 -- Generate the body of the call procedure - this is the real work.
3362 --
3363 l_locals := 'c cr%rowtype;' || c_newline ||
3364 'l_validate boolean := false;';
3365 l_cursor := 'cursor cr is' || c_newline || 'select l.rowid myrowid';
3366 l_precall := '';
3367 --
3368 -- Only bracket API call if it has at least one parameter.
3369 --
3370 if p_parameter_counts.api_parameters > 0 then
3371 l_call := p_module_package || '.' || p_module_name ||
3372 c_newline || '(';
3373 else
3374 l_call := p_module_package || '.' || p_module_name || ';';
3375 end if;
3376 l_postcall := null;
3377 l_update := 'update hr_pump_batch_lines l set';
3378 for i in 1 .. p_parameter_counts.total_parameters loop
3379 l_local_variable := null;
3380 --
3381 add_to_locals( l_locals, p_parameter_tbl(i), l_local_variable );
3382 --
3383 add_to_cursor( l_cursor, p_parameter_tbl(i), l_effdate, l_langcode );
3384 --
3385 add_to_precall( l_precall, p_parameter_tbl(i), l_local_variable );
3386 --
3387 if i <= trunc(p_parameter_counts.total_parameters / 3) then
3388 add_to_call( l_call, p_parameter_tbl(i), l_local_variable, l_first_call );
3389 elsif i <= trunc(2 * p_parameter_counts.total_parameters / 3) then
3390 add_to_call( l_call1, p_parameter_tbl(i), l_local_variable, l_first_call );
3391 else
3392 add_to_call( l_call2, p_parameter_tbl(i), l_local_variable, l_first_call );
3393 end if;
3394 --
3395 add_to_postcall( l_postcall, p_parameter_tbl(i), l_local_variable,
3396 l_first_postcall );
3397 --
3398 add_to_update( l_update, p_parameter_tbl(i), l_local_variable,
3399 l_first_update );
3400 end loop;
3401 --
3402 -- Terminate the cursor text.
3403 --
3404 l_cursor :=
3405 l_cursor || c_newline || 'from hr_pump_batch_lines l' || c_newline ||
3406 'where l.batch_line_id = p_batch_line_id;';
3407 --
3408 -- Terminate the call.
3409 --
3410 if p_parameter_counts.api_parameters > 0 then
3411 l_call2 := l_call2 || ');' || c_newline;
3412 end if;
3413 --
3414 -- Terminate the update.
3415 --
3416 if not l_first_update then
3417 l_update :=
3418 l_update || c_newline ||
3419 'where l.rowid = c.myrowid;';
3420 else
3421 l_update := null;
3422 end if;
3423 --
3424 -- Handle any parameter mapping functions.
3425 --
3426 for i in 1 .. p_parameter_counts.functions loop
3427 l_local_variable := null;
3428 --
3429 add_to_locals( l_locals1, p_function_tbl(i), p_parameter_tbl,
3430 l_local_variable );
3431 --
3432 add_to_precall( l_precall1, p_parameter_tbl( p_function_tbl(i).seqno ),
3433 p_function_tbl(i), p_function_call_tbl,
3434 l_local_variable );
3435 end loop;
3436
3437 ---------------------------------------------------------------------
3438 -- At last we can build the package body. This is done by creating --
3439 -- body pieces in stages. --
3440 ---------------------------------------------------------------------
3441 p_body :=
3442 p_body || c_newline || l_cursor || c_newline || '--' || c_newline;
3443 split_sql_text
3444 (p_last => false
3445 ,p_text => p_body
3446 ,p_text_pieces => p_body_pieces
3447 );
3448
3449 p_body :=
3450 p_body || l_locals || l_locals1 || c_newline || '--' || c_newline;
3451 split_sql_text
3452 (p_last => false
3453 ,p_text => p_body
3454 ,p_text_pieces => p_body_pieces
3455 );
3456
3457 p_body := p_body ||
3458 'begin' || c_newline ||
3459 'hr_data_pump.entry(''call'');' || c_newline ||
3460 'open cr;' || c_newline ||
3461 'fetch cr into c;' || c_newline ||
3462 'if cr%notfound then' || c_newline ||
3463 'hr_utility.set_message(800,''HR_50326_DP_NO_ROW'');' || c_newline ||
3464 'hr_utility.set_message_token(''TABLE'',''HR_PUMP_BATCH_LINES'');' ||
3465 c_newline ||
3466 'hr_utility.set_message_token(''COLUMN'',''P_BATCH_LINE_ID'');' ||
3467 c_newline ||
3468 'hr_utility.set_message_token(''VALUE'',p_batch_line_id);' ||
3469 c_newline ||
3470 'hr_utility.raise_error;' || c_newline ||
3471 'end if;';
3472 --
3473 split_sql_text
3474 (p_last => false
3475 ,p_text => p_body
3476 ,p_text_pieces => p_body_pieces
3477 );
3478
3479 p_body := p_body || l_precall;
3480 split_sql_text
3481 (p_last => false
3482 ,p_text => p_body
3483 ,p_text_pieces => p_body_pieces
3484 );
3485
3486 p_body := p_body || l_precall1 || c_newline || '--' || c_newline;
3487 split_sql_text
3488 (p_last => false
3489 ,p_text => p_body
3490 ,p_text_pieces => p_body_pieces
3491 );
3492
3493 p_body :=
3494 p_body || 'hr_data_pump.api_trc_on;' || c_newline || l_call;
3495 split_sql_text
3496 (p_last => false
3497 ,p_text => p_body
3498 ,p_text_pieces => p_body_pieces
3499 );
3500
3501 p_body := p_body || l_call1;
3502 split_sql_text
3503 (p_last => false
3504 ,p_text => p_body
3505 ,p_text_pieces => p_body_pieces
3506 );
3507
3508 p_body := p_body || l_call2 || 'hr_data_pump.api_trc_off;' || c_newline;
3509 split_sql_text
3510 (p_last => false
3511 ,p_text => p_body
3512 ,p_text_pieces => p_body_pieces
3513 );
3514
3515 p_body := p_body || l_postcall || c_newline || '--' || c_newline;
3516 split_sql_text
3517 (p_last => false
3518 ,p_text => p_body
3519 ,p_text_pieces => p_body_pieces
3520 );
3521
3522 p_body := p_body || l_update || c_newline || '--' || c_newline;
3523 split_sql_text
3524 (p_last => false
3525 ,p_text => p_body
3526 ,p_text_pieces => p_body_pieces
3527 );
3528
3529 p_body := p_body ||
3530 'close cr;' || c_newline ||
3531 '--' || c_newline ||
3532 'hr_data_pump.exit(''call'');' || c_newline ||
3533 'exception' || c_newline ||
3534 ' when hr_multi_message.error_message_exist then' || c_newline ||
3535 ' if cr%isopen then' || c_newline ||
3536 ' close cr;' || c_newline ||
3537 ' end if;' || c_newline ||
3538 ' hr_pump_utils.set_multi_msg_error_flag(true);' || c_newline ||
3539 ' when others then' || c_newline ||
3540 ' if cr%isopen then' || c_newline ||
3541 ' close cr;' || c_newline ||
3542 ' end if;' || c_newline ||
3543 ' raise;' || c_newline ||
3544 'end call;' || c_newline;
3545 split_sql_text
3546 (p_last => false
3547 ,p_text => p_body
3548 ,p_text_pieces => p_body_pieces
3549 );
3550 end generate_call;
3551
3552 -- ------------------------- create_package -------------------------------
3553 -- Description:
3554 -- Generates the header and body text for the output packages, and compiles
3555 -- the output packages.
3556 -- ------------------------------------------------------------------------
3557 procedure create_package
3558 (
3559 p_module_package in varchar2,
3560 p_module_name in varchar2,
3561 p_package_name in varchar2,
3562 p_api_module_id in number,
3563 p_parameter_tbl in t_parameter_tbl,
3564 p_function_tbl in t_function_tbl,
3565 p_function_call_tbl in t_function_parameter_tbl,
3566 p_parameter_counts in t_parameter_counts
3567 )
3568 is
3569 l_header varchar2(32767);
3570 l_body varchar2(32767);
3571 l_header_comment varchar2(2048);
3572 l_body_pieces dbms_sql.varchar2s;
3573 begin
3574 -- Start the package header and body.
3575 begin
3576 --
3577 -- Set up initial parts of the package header and body.
3578 --
3579 l_header_comment :=
3580 '/*' || c_newline ||
3581 ' * Generated by hr_pump_meta_mapper at: ' ||
3582 to_char( sysdate, 'YYYY/MM/DD HH24:MM:SS' ) || c_newline ||
3583 ' * Generated for API: ' || p_module_package || '.' || p_module_name ||
3584 c_newline ||
3585 ' */' || c_newline || '--' || c_newline;
3586 l_header :=
3587 'create or replace package ' || p_package_name || ' as' || c_newline ||
3588 l_header_comment ||
3589 'g_generator_version constant varchar2(128) default ' ||
3590 '''$Revision: 120.4.12010000.1 $'';' || c_newline || '--' || c_newline;
3591 l_body :=
3592 'create or replace package body ' || p_package_name || ' as' || c_newline ||
3593 l_header_comment;
3594 --
3595 -- Generate the procedures and functions.
3596 --
3597 gen_hr_api_vars(l_body);
3598 gen_null_vars(l_body);
3599 l_body := l_body || c_newline || '--' || c_newline;
3600 gen_to_calls( l_body, l_header );
3601 l_body := l_body || c_newline || '--' || c_newline;
3602 l_header := l_header || c_newline || '--' || c_newline;
3603 gen_ins_user_key( l_body );
3604 l_body := l_body || c_newline || '--' || c_newline;
3605 generate_insert( p_api_module_id, p_parameter_tbl, p_parameter_counts,
3606 l_header, l_body );
3607 split_sql_text
3608 (p_last => false
3609 ,p_text => l_body
3610 ,p_text_pieces => l_body_pieces
3611 );
3612 l_header := l_header || c_newline || '--' || c_newline;
3613 l_body := l_body || c_newline || '--' || c_newline;
3614 generate_call
3615 (p_module_package => p_module_package
3616 ,p_module_name => p_module_name
3617 ,p_parameter_tbl => p_parameter_tbl
3618 ,p_function_tbl => p_function_tbl
3619 ,p_function_call_tbl => p_function_call_tbl
3620 ,p_parameter_counts => p_parameter_counts
3621 ,p_header => l_header
3622 ,p_body => l_body
3623 ,p_body_pieces => l_body_pieces
3624 );
3625 --
3626 -- Terminate the package body and header.
3627 --
3628 l_header := l_header || 'end ' || p_package_name || ';';
3629 l_body := l_body || 'end ' || p_package_name || ';';
3630 split_sql_text
3631 (p_last => true
3632 ,p_text => l_body
3633 ,p_text_pieces => l_body_pieces
3634 );
3635 exception
3636 when plsql_value_error then
3637 hr_utility.set_message( 800, 'HR_50323_DP_CODE_TOO_BIG' );
3638 hr_utility.raise_error;
3639 end;
3640 --
3641 -- Compile the header and body.
3642 --
3643 run_sql( l_header );
3644 check_compile( p_package_name, 'PACKAGE' );
3645 run_sql( l_body_pieces );
3646 check_compile( p_package_name, 'PACKAGE BODY' );
3647 end create_package;
3648
3649 ---------------------------------------------------------------------------
3650 -- PUBLIC PROCEDURES --
3651 ---------------------------------------------------------------------------
3652 -- -------------------------- generate ------------------------------------
3653 -- Description:
3654 -- Generates a package containing the following:
3655 -- - A wrapper procedure to call the API.
3656 -- - A procedure to insert data for this API in hr_pump_batch_lines.
3657 -- - A procedure to list the view columns and the parameters required
3658 -- for the above data insert function.
3659 -- Generates a view on hr_pump_batch_lines to allow a user an alternative
3660 -- mechanism to insert or update data.
3661 -- ------------------------------------------------------------------------
3662 procedure generate
3663 (
3664 p_module_package in varchar2,
3665 p_module_name in varchar2
3666 ,p_standard_generate in boolean default true
3667 )
3668 is
3669 l_parameter_counts t_parameter_counts;
3670 l_defaulting_style number;
3671 l_parameter_tbl t_parameter_tbl;
3672 l_function_tbl t_function_tbl;
3673 l_function_call_tbl t_function_parameter_tbl;
3674 --
3675 l_api_module_id number;
3676 --
3677 l_view_name varchar2(30);
3678 l_package_name varchar2(30);
3679 --
3680 cursor csr_api_module_id
3681 (p_module_package in varchar2, p_module_name in varchar2) is
3682 select api_module_id
3683 from hr_api_modules
3684 where upper(module_name) = upper(p_module_name)
3685 and upper(module_package) = upper(p_module_package)
3686 and upper(api_module_type) in ('AI', 'BP', 'DM');
3687 begin
3688 g_debug := hr_utility.debug_enabled;
3689
3690 if g_debug then
3691 hr_utility.trace
3692 ('----------- Generate API: ' || p_module_package || '.' ||
3693 p_module_name || '----------');
3694 end if;
3695
3696 hr_pump_meta_mapper.g_standard_generate := p_standard_generate;
3697 setup_parameter_data( p_module_package,
3698 p_module_name,
3699 l_parameter_counts,
3700 l_defaulting_style,
3701 l_parameter_tbl,
3702 l_function_tbl,
3703 l_function_call_tbl );
3704
3705 open csr_api_module_id(p_module_package, p_module_name);
3706 fetch csr_api_module_id into l_api_module_id;
3707 if csr_api_module_id%notfound then
3708 close csr_api_module_id;
3709 hr_utility.set_message(800, 'HR_33156_DP_NOT_IN_API_MODULES');
3710 hr_utility.set_message_token
3711 ( 'API', p_module_package || '.' || p_module_name );
3712 hr_utility.raise_error;
3713 end if;
3714 close csr_api_module_id;
3715
3716 purge( p_module_package, p_module_name );
3717
3718 hr_pump_utils.name( p_module_package, p_module_name, l_package_name,
3719 l_view_name );
3720
3721 if g_debug then
3722 hr_utility.trace('Package: ' || l_package_name);
3723 hr_utility.trace('View: ' || l_view_name);
3724 end if;
3725
3726 create_view( l_view_name, l_parameter_tbl, l_parameter_counts,
3727 l_api_module_id );
3728
3729 create_package( p_module_package, p_module_name, l_package_name,
3730 l_api_module_id, l_parameter_tbl, l_function_tbl,
3731 l_function_call_tbl, l_parameter_counts );
3732 l_parameter_tbl.delete;
3733 l_function_tbl.delete;
3734 l_function_call_tbl.delete;
3735 end generate;
3736
3737 -- ------------------------- generateall ----------------------------------
3738 -- Description:
3739 -- Calls generate on all supported APIs.
3740 -- ------------------------------------------------------------------------
3741 procedure generateall is
3742 begin
3743 generate( 'hr_employee_api', 'create_employee' );
3744 generate( 'hr_employee_api', 'create_gb_employee' );
3745 generate( 'hr_employee_api', 'create_us_employee' );
3746 generate( 'hr_assignment_api', 'activate_emp_asg' );
3747 generate( 'hr_assignment_api', 'actual_termination_emp_asg' );
3748 generate( 'hr_assignment_api', 'create_secondary_emp_asg' );
3749 generate( 'hr_assignment_api', 'create_gb_secondary_emp_asg' );
3750 generate( 'hr_assignment_api', 'create_us_secondary_emp_asg' );
3751 generate( 'hr_assignment_api', 'update_emp_asg' );
3752 generate( 'hr_assignment_api', 'update_emp_asg_criteria' );
3753 generate( 'hr_job_api', 'create_job' );
3754 generate( 'hr_position_api', 'create_position' );
3755 generate( 'hr_position_api', 'update_position' );
3756 generate( 'hr_valid_grade_api', 'create_valid_grade' );
3757 --
3758 generate( 'HR_PERSON_ADDRESS_API', 'CREATE_PERSON_ADDRESS' );
3759 generate( 'HR_PERSON_ADDRESS_API', 'CREATE_US_PERSON_ADDRESS' );
3760 generate( 'HR_PERSON_ADDRESS_API', 'CREATE_GB_PERSON_ADDRESS' );
3761 generate( 'HR_PERSON_ADDRESS_API', 'UPDATE_PERSON_ADDRESS' );
3762 generate( 'HR_PERSON_ADDRESS_API', 'UPDATE_US_PERSON_ADDRESS' );
3763 generate( 'HR_PERSON_ADDRESS_API', 'UPDATE_GB_PERSON_ADDRESS' );
3764 generate( 'HR_CONTACT_API', 'CREATE_PERSON' );
3765 generate( 'HR_CONTACT_REL_API', 'CREATE_CONTACT' );
3766 generate( 'PY_ELEMENT_ENTRY_API', 'CREATE_ELEMENT_ENTRY' );
3767 generate( 'PY_ELEMENT_ENTRY_API', 'UPDATE_ELEMENT_ENTRY' );
3768 generate( 'PY_ELEMENT_ENTRY_API', 'DELETE_ELEMENT_ENTRY' );
3769 --temporarily comment out these lines for 2734761
3770 --WIP on grade rate APIs is done to seed correct data for hr_rate_values_api
3771 -- generate( 'HR_RATE_VALUES_API', 'CREATE_RATE_VALUE' );
3772 -- generate( 'HR_RATE_VALUES_API', 'UPDATE_RATE_VALUE' );
3773 -- generate( 'HR_RATE_VALUES_API', 'DELETE_RATE_VALUE' );
3774 generate( 'HR_PERSONAL_PAY_METHOD_API', 'CREATE_PERSONAL_PAY_METHOD' );
3775 generate( 'HR_PERSONAL_PAY_METHOD_API', 'CREATE_GB_PERSONAL_PAY_METHOD' );
3776 generate( 'HR_PERSONAL_PAY_METHOD_API', 'CREATE_US_PERSONAL_PAY_METHOD' );
3777 generate( 'HR_PERSONAL_PAY_METHOD_API', 'UPDATE_PERSONAL_PAY_METHOD' );
3778 generate( 'HR_PERSONAL_PAY_METHOD_API', 'UPDATE_GB_PERSONAL_PAY_METHOD' );
3779 generate( 'HR_PERSONAL_PAY_METHOD_API', 'UPDATE_US_PERSONAL_PAY_METHOD' );
3780 generate( 'HR_PERSONAL_PAY_METHOD_API', 'DELETE_PERSONAL_PAY_METHOD' );
3781 generate( 'HR_SIT_API', 'CREATE_SIT' );
3782 generate( 'HR_APPLICANT_API', 'CREATE_APPLICANT' );
3783 generate( 'HR_APPLICANT_API', 'CREATE_GB_APPLICANT' );
3784 generate( 'HR_APPLICANT_API', 'CREATE_US_APPLICANT' );
3785 generate( 'HR_JOB_REQUIREMENT_API', 'CREATE_JOB_REQUIREMENT' );
3786 generate( 'HR_POSITION_REQUIREMENT_API', 'CREATE_POSITION_REQUIREMENT' );
3787 generate( 'HR_PERSON_API', 'UPDATE_PERSON' );
3788 generate( 'HR_PERSON_API', 'UPDATE_GB_PERSON' );
3789 generate( 'HR_PERSON_API', 'UPDATE_US_PERSON' );
3790 --temporarily comment out these lines for 2734761
3791 --WIP on these APIs is done to seed correct data for hr_pay_scale_api
3792 -- generate( 'HR_PAY_SCALE_API', 'CREATE_PAY_SCALE_VALUE' );
3793 -- generate( 'HR_PAY_SCALE_API', 'UPDATE_PAY_SCALE_VALUE' );
3794 -- generate( 'HR_PAY_SCALE_API', 'DELETE_PAY_SCALE_VALUE' );
3795 generate( 'HR_EX_EMPLOYEE_API', 'ACTUAL_TERMINATION_EMP' );
3796 generate( 'HR_EX_EMPLOYEE_API', 'FINAL_PROCESS_EMP' );
3797 --
3798 generate( 'HR_ASSIGNMENT_API', 'SUSPEND_EMP_ASG' );
3799 generate( 'HR_ASSIGNMENT_API', 'UPDATE_US_EMP_ASG' );
3800 generate( 'HR_ASSIGNMENT_API', 'UPDATE_GB_EMP_ASG' );
3801 /*
3802 -- Taking out as generateall should not be updated. 3296375.
3803 -- Adnan
3804 --Location
3805 generate('HR_LOCATION_API','CREATE_LOCATION');
3806 --Org Hierarchy Element
3807 generate('HR_HIERARCHY_ELEMENT_API','CREATE_HIERARCHY_ELEMENT');
3808 --Salary Basis
3809 generate('HR_SALARY_BASIS_API','CREATE_SALARY_BASIS');
3810 --Salary Proposal
3811 generate('HR_UPLOAD_PROPOSAL_API','UPLOAD_SALARY_PROPOSAL');
3812 */
3813 end generateall;
3814
3815 -- ---------------------------- purgeall ----------------------------------
3816 -- Description:
3817 -- Calls purge on all supported APIs.
3818 -- ------------------------------------------------------------------------
3819 procedure purgeall is
3820 begin
3821 purge( 'hr_employee_api', 'create_employee' );
3822 purge( 'hr_employee_api', 'create_gb_employee' );
3823 purge( 'hr_employee_api', 'create_us_employee' );
3824 purge( 'hr_assignment_api', 'activate_emp_asg' );
3825 purge( 'hr_assignment_api', 'actual_termination_emp_asg' );
3826 purge( 'hr_assignment_api', 'create_secondary_emp_asg' );
3827 purge( 'hr_assignment_api', 'create_gb_secondary_emp_asg' );
3828 purge( 'hr_assignment_api', 'create_us_secondary_emp_asg' );
3829 purge( 'hr_assignment_api', 'update_emp_asg' );
3830 purge( 'hr_assignment_api', 'update_emp_asg_criteria' );
3831 purge( 'hr_job_api', 'create_job' );
3832 purge( 'hr_position_api', 'create_position' );
3833 purge( 'hr_position_api', 'update_position' );
3834 purge( 'hr_valid_grade_api', 'create_valid_grade' );
3835 --
3836 purge( 'HR_PERSON_ADDRESS_API', 'CREATE_PERSON_ADDRESS' );
3837 purge( 'HR_PERSON_ADDRESS_API', 'CREATE_US_PERSON_ADDRESS' );
3838 purge( 'HR_PERSON_ADDRESS_API', 'CREATE_GB_PERSON_ADDRESS' );
3839 purge( 'HR_PERSON_ADDRESS_API', 'UPDATE_PERSON_ADDRESS' );
3840 purge( 'HR_PERSON_ADDRESS_API', 'UPDATE_US_PERSON_ADDRESS' );
3841 purge( 'HR_PERSON_ADDRESS_API', 'UPDATE_GB_PERSON_ADDRESS' );
3842 purge( 'HR_CONTACT_API', 'CREATE_PERSON' );
3843 purge( 'HR_CONTACT_REL_API', 'CREATE_CONTACT' );
3844 purge( 'PY_ELEMENT_ENTRY_API', 'CREATE_ELEMENT_ENTRY' );
3845 purge( 'PY_ELEMENT_ENTRY_API', 'UPDATE_ELEMENT_ENTRY' );
3846 purge( 'PY_ELEMENT_ENTRY_API', 'DELETE_ELEMENT_ENTRY' );
3847 --temporarily comment out these lines for 2734761
3848 --WIP on grade rate APIs is done to seed correct data for hr_rate_values_api
3849 -- purge( 'HR_RATE_VALUES_API', 'CREATE_RATE_VALUE' );
3850 -- purge( 'HR_RATE_VALUES_API', 'UPDATE_RATE_VALUE' );
3851 -- purge( 'HR_RATE_VALUES_API', 'DELETE_RATE_VALUE' );
3852 purge( 'HR_PERSONAL_PAY_METHOD_API', 'CREATE_PERSONAL_PAY_METHOD' );
3853 purge( 'HR_PERSONAL_PAY_METHOD_API', 'CREATE_GB_PERSONAL_PAY_METHOD' );
3854 purge( 'HR_PERSONAL_PAY_METHOD_API', 'CREATE_US_PERSONAL_PAY_METHOD' );
3855 purge( 'HR_PERSONAL_PAY_METHOD_API', 'UPDATE_PERSONAL_PAY_METHOD' );
3856 purge( 'HR_PERSONAL_PAY_METHOD_API', 'UPDATE_GB_PERSONAL_PAY_METHOD' );
3857 purge( 'HR_PERSONAL_PAY_METHOD_API', 'UPDATE_US_PERSONAL_PAY_METHOD' );
3858 purge( 'HR_PERSONAL_PAY_METHOD_API', 'DELETE_PERSONAL_PAY_METHOD' );
3859 purge( 'HR_SIT_API', 'CREATE_SIT' );
3860 purge( 'HR_APPLICANT_API', 'CREATE_APPLICANT' );
3861 purge( 'HR_APPLICANT_API', 'CREATE_GB_APPLICANT' );
3862 purge( 'HR_APPLICANT_API', 'CREATE_US_APPLICANT' );
3863 purge( 'HR_JOB_REQUIREMENT_API', 'CREATE_JOB_REQUIREMENT' );
3864 purge( 'HR_POSITION_REQUIREMENT_API', 'CREATE_POSITION_REQUIREMENT' );
3865 purge( 'HR_PERSON_API', 'UPDATE_PERSON' );
3866 purge( 'HR_PERSON_API', 'UPDATE_GB_PERSON' );
3867 purge( 'HR_PERSON_API', 'UPDATE_US_PERSON' );
3868 --temporarily comment out these lines for 2734761
3869 --WIP on these APIs is done to seed correct data for hr_pay_scale_api
3870 -- purge( 'HR_PAY_SCALE_API', 'CREATE_PAY_SCALE_VALUE' );
3871 -- purge( 'HR_PAY_SCALE_API', 'UPDATE_PAY_SCALE_VALUE' );
3872 -- purge( 'HR_PAY_SCALE_API', 'DELETE_PAY_SCALE_VALUE' );
3873 purge( 'HR_EX_EMPLOYEE_API', 'ACTUAL_TERMINATION_EMP' );
3874 purge( 'HR_EX_EMPLOYEE_API', 'FINAL_PROCESS_EMP' );
3875 --
3876 purge( 'HR_ASSIGNMENT_API', 'SUSPEND_EMP_ASG' );
3877 purge( 'HR_ASSIGNMENT_API', 'UPDATE_US_EMP_ASG' );
3878 purge( 'HR_ASSIGNMENT_API', 'UPDATE_GB_EMP_ASG' );
3879 end purgeall;
3880
3881 -- ---------------------------- help --------------------------------------
3882 -- Description:
3883 -- Displays the following help text for the API specified by p_module_name
3884 -- and p_module_package:
3885 -- - The generated package and view name.
3886 -- - Batch lines parameter information.
3887 -- ------------------------------------------------------------------------
3888 procedure help
3889 (
3890 p_module_package in varchar2,
3891 p_module_name in varchar2
3892 ,p_standard_generate in boolean default true
3893 )
3894 is
3895 l_parameter_counts t_parameter_counts;
3896 l_defaulting_style number;
3897 l_parameter_tbl t_parameter_tbl;
3898 l_function_tbl t_function_tbl;
3899 l_function_call_tbl t_function_parameter_tbl;
3900 --
3901 l_view_name varchar2(30);
3902 l_package_name varchar2(30);
3903 --
3904 -- Batch lines parameter help information.
3905 --
3906 l_helpstring varchar2(84);
3907 l_parameter_name varchar2(31);
3908 l_parameter_type varchar2(10);
3909 l_in_out varchar2(4);
3910 l_lookup_type varchar2(31);
3911 l_default varchar2(8);
3912 --
3913 l_message varchar2(2048);
3914 begin
3915 hr_pump_meta_mapper.g_standard_generate := p_standard_generate;
3916 setup_parameter_data( p_module_package,
3917 p_module_name,
3918 l_parameter_counts,
3919 l_defaulting_style,
3920 l_parameter_tbl,
3921 l_function_tbl,
3922 l_function_call_tbl );
3923
3924 hr_pump_utils.name( p_module_package, p_module_name, l_package_name,
3925 l_view_name );
3926
3927 --
3928 -- Initialise the output mechanism.
3929 --
3930 output_init;
3931
3932 --
3933 -- Generate and output the messages saying what has been created.
3934 --
3935 hr_utility.set_message( 800, 'HR_50324_DP_GEN_PKG' );
3936 hr_utility.set_message_token( 'PACKAGE', l_package_name );
3937 l_message := hr_utility.get_message;
3938 output_text( l_message);
3939 --
3940 hr_utility.set_message( 800, 'HR_50325_DP_GEN_VIEW' );
3941 hr_utility.set_message_token( 'VIEW', l_view_name );
3942 l_message := hr_utility.get_message;
3943 output_text( l_message);
3944 --
3945 -- Generate the header.
3946 --
3947 output_text( 'Parameter Name Type In/Out Default? Lookup Type' );
3948 output_text( '--------------- ----- ------- -------- ------------' );
3949
3950 for i in 1 .. l_parameter_counts.total_parameters loop
3951 --
3952 -- Only list information for batch lines parameters.
3953 --
3954 if l_parameter_tbl(i).batch_lines_seqno is not null then
3955 --
3956 l_parameter_name := rpad( l_parameter_tbl(i).parameter_name, 31 );
3957 --
3958 -- Only lookup parameters have an associated lookup type.
3959 --
3960 if l_parameter_tbl(i).mapping_type = 'USER_KEY' then
3961 l_parameter_type := 'USER_KEY';
3962 l_lookup_type := rpad( ' ', 31 );
3963 elsif l_parameter_tbl(i).mapping_type = 'LOOKUP' then
3964 l_parameter_type := 'LOOKUP';
3965 l_lookup_type := rpad( l_parameter_tbl(i).mapping_definition, 31 );
3966 else
3967 l_parameter_type := g_tbl_datatype( l_parameter_tbl(i).datatype );
3968 l_lookup_type := rpad( ' ', 31 );
3969 end if;
3970 l_parameter_type := upper( rpad( l_parameter_type, 10 ) );
3971 --
3972 if l_parameter_tbl(i).defaultable then
3973 l_default := rpad( 'DEFAULT', 8 );
3974 else
3975 l_default := rpad( ' ', 8 );
3976 end if;
3977 --
3978 -- Set up the in/out field. For user key parameters we make the in/out
3979 -- 'IN' because the user has to supply the user key.
3980 --
3981 if l_parameter_tbl(i).in_out = c_ptype_out and
3982 l_parameter_tbl(i).mapping_type <> c_mapping_type_user_key
3983 then
3984 l_in_out := rpad( 'OUT', 4 );
3985 else
3986 l_in_out := rpad( 'IN', 4 );
3987 end if;
3988 --
3989 -- Output is almost identical to the output of the desc command.
3990 --
3991 l_helpstring :=
3992 l_parameter_name || l_parameter_type || l_in_out || l_default ||
3993 l_lookup_type;
3994 --
3995 -- Output the help string.
3996 --
3997 output_text( l_helpstring );
3998 end if;
3999 end loop;
4000
4001 l_parameter_tbl.delete;
4002 l_function_tbl.delete;
4003 l_function_call_tbl.delete;
4004 end help;
4005
4006 -- -------------------------- purge ---------------------------------------
4007 -- Description:
4008 -- Purges all data created by a generate call for the API.
4009 -- ------------------------------------------------------------------------
4010 procedure purge( p_module_package in varchar2,
4011 p_module_name in varchar2 )
4012 is
4013 --
4014 cursor csr_find_view( p_view_name in varchar2 ) is
4015 select 1 from user_views
4016 where upper(view_name) = upper(p_view_name);
4017 --
4018 cursor csr_find_pkg( p_pkg_name in varchar2 ) is
4019 select 1 from user_objects
4020 where upper(object_name) = upper(p_pkg_name)
4021 and object_type = 'PACKAGE';
4022 --
4023 l_package_name varchar2(30);
4024 l_view_name varchar2(30);
4025 l_found integer;
4026 begin
4027 hr_pump_utils.name( p_module_package, p_module_name, l_package_name,
4028 l_view_name );
4029
4030 -- Delete the package if it exists.
4031 begin
4032 --
4033 open csr_find_pkg( l_package_name );
4034 fetch csr_find_pkg into l_found;
4035 if csr_find_pkg%notfound then
4036 l_found := 0;
4037 end if;
4038 close csr_find_pkg;
4039 exception
4040 when others then
4041 if csr_find_pkg%isopen then
4042 close csr_find_pkg;
4043 end if;
4044 raise;
4045 end;
4046 --
4047 if l_found <> 0 then
4048 run_sql( 'drop package ' || l_package_name );
4049 end if;
4050
4051 -- Delete the view if it exists.
4052 begin
4053 --
4054 open csr_find_view( l_view_name );
4055 fetch csr_find_view into l_found;
4056 if csr_find_view%notfound then
4057 l_found := 0;
4058 end if;
4059 close csr_find_view;
4060 exception
4061 when others then
4062 if csr_find_view%isopen then
4063 close csr_find_view;
4064 end if;
4065 raise;
4066 end;
4067 --
4068 if l_found <> 0 then
4069 run_sql( 'drop view ' || l_view_name );
4070 end if;
4071 end purge;
4072 --
4073 -- --------------- init_parameter_list ----------------------------
4074 -- Description:
4075 -- Initialise the cached parameter list for subsequent reuse
4076 -- in the Pump Station UI. Only refreshes the parameter list if
4077 -- the API passed in is different to the one currently cached.
4078 -- Returns the total number of parameters accepted by the API,
4079 -- even if the cache isn't refreshed.
4080 -- ----------------------------------------------------------------
4081 function init_parameter_list(p_api_id in number) return number is
4082 --
4083 l_param_counts t_parameter_counts;
4084 l_func t_function_tbl;
4085 l_func_param t_function_parameter_tbl;
4086 l_defl_style number;
4087 --
4088 l_module_package varchar2(30);
4089 l_module_name varchar2(30);
4090 l_parameter_name varchar2(240);
4091 begin
4092 if g_last_api_id <> p_api_id then
4093 --
4094 g_last_api_id := p_api_id;
4095 --
4096 select module_package,module_name
4097 into l_module_package,l_module_name
4098 from hr_api_modules
4099 where api_module_id = p_api_id;
4100 --
4101 hr_pump_meta_mapper.g_standard_generate := true;
4102 setup_parameter_data(
4103 l_module_package,
4104 l_module_name,
4105 l_param_counts,
4106 l_defl_style,
4107 g_params,
4108 l_func,
4109 l_func_param
4110 );
4111 --
4112 g_last_column_count := l_param_counts.total_parameters;
4113 --
4114 end if;
4115 return g_last_column_count;
4116 end init_parameter_list;
4117 --
4118 -- ----------------- get_parameter_info --------------------------
4119 -- Description:
4120 -- Get information about a given parameter to an API. Returns
4121 -- p_success as 1 if p_index is valid for the API specified by
4122 -- p_api_id, in which case the other parameters are also valid,
4123 -- otherwise returns p_success as 0 and everything else as null.
4124 -- ---------------------------------------------------------------
4125 procedure get_parameter_info
4126 (
4127 p_api_id in number,
4128 p_index in number,
4129 p_batch_lines_seqno out nocopy number,
4130 p_batch_lines_column out nocopy varchar2,
4131 p_parameter_name out nocopy varchar2,
4132 p_success out nocopy number
4133 ) is
4134 l_count number := 0;
4135 begin
4136 l_count := init_parameter_list(p_api_id);
4137 --
4138 p_batch_lines_seqno := 0;
4139 p_batch_lines_column := null;
4140 p_parameter_name := null;
4141 p_success := 0;
4142 --
4143 if p_index <= l_count then
4144 if p_batch_lines_seqno is not null then
4145 p_batch_lines_seqno := g_params(p_index).batch_lines_seqno;
4146 p_batch_lines_column := g_params(p_index).batch_lines_column;
4147 p_parameter_name := g_params(p_index).parameter_name;
4148 end if;
4149 p_success := 1;
4150 end if;
4151 end get_parameter_info;
4152 --
4153 -- Initialisation code.
4154 --
4155 begin
4156 --
4157 -- Set up code generation tables.
4158 --
4159 g_tbl_datatype(c_dtype_varchar2) := 'varchar2';
4160 g_tbl_datatype(c_dtype_number) := 'number';
4161 -- Use number instead of binary_integer data type.
4162 g_tbl_datatype(c_dtype_binary_integer) := 'number';
4163 g_tbl_datatype(c_dtype_long) := 'long';
4164 g_tbl_datatype(c_dtype_date) := 'date';
4165 g_tbl_datatype(c_dtype_boolean) := 'boolean';
4166 -------------------------------------------------
4167 g_tbl_default_hr_api(c_dtype_varchar2) := 'hr_api.g_varchar2';
4168 g_tbl_default_hr_api(c_dtype_number) := 'hr_api.g_number';
4169 g_tbl_default_hr_api(c_dtype_date) := 'hr_api.g_date';
4170 -- No hr_api defaults for long and boolean.
4171 g_tbl_default_hr_api(c_dtype_long) := 'null';
4172 g_tbl_default_hr_api(c_dtype_boolean) := 'null';
4173 -------------------------------------------------
4174 g_tbl_default_null(c_dtype_varchar2) := 'null';
4175 g_tbl_default_null(c_dtype_number) := 'n(null)';
4176 g_tbl_default_null(c_dtype_date) := 'd(null)';
4177 g_tbl_default_null(c_dtype_long) := 'null';
4178 g_tbl_default_null(c_dtype_boolean) := 'null';
4179 -------------------------------------------------
4180 g_tbl_call_default_hr_api(c_dtype_varchar2) := 'vh';
4181 g_tbl_call_default_hr_api(c_dtype_number) := 'nh';
4182 g_tbl_call_default_hr_api(c_dtype_date) := 'dh';
4183 -- No hr_api defaults for long and boolean.
4184 g_tbl_call_default_hr_api(c_dtype_long) := 'null';
4185 g_tbl_call_default_hr_api(c_dtype_boolean) := 'null';
4186 -------------------------------------------------
4187 g_tbl_call_default_null(c_dtype_varchar2) := 'vn';
4188 g_tbl_call_default_null(c_dtype_number) := 'nn';
4189 g_tbl_call_default_null(c_dtype_date) := 'dn';
4190 g_tbl_call_default_null(c_dtype_long) := 'null';
4191 g_tbl_call_default_null(c_dtype_boolean) := 'null';
4192 end hr_pump_meta_mapper;