1 Package Body hr_api_user_hooks as
2 /* $Header: hrusrhok.pkb 120.2 2012/01/05 09:14:34 avarri ship $ */
3 --
4 -- Type Definitions
5 --
6 type tbl_parameter_name is table of varchar2(30) index by binary_integer;
7 type tbl_parameter_datatype is table of number index by binary_integer;
8 --
9 -- Error Exceptions which can be raised by dbms_describe.describe_procedure
10 --
11 --
12 -- Package does not exist in the database
13 --
14 Package_Not_Exists exception;
15 Pragma Exception_Init(Package_Not_Exists, -6564);
16 --
17 -- Procedure does not exist in the package
18 --
19 Proc_Not_In_Package exception;
20 Pragma Exception_Init(Proc_Not_In_Package, -20001);
21 --
22 -- Object is remote
23 --
24 Remote_Object exception;
25 Pragma Exception_Init(Remote_Object, -20002);
26 --
27 -- Package is invalid
28 --
29 Invalid_Package exception;
30 Pragma Exception_Init(Invalid_Package, -20003);
31 --
32 -- Invalid Object Name
33 --
34 Invalid_Object_Name exception;
35 Pragma Exception_Init(Invalid_Object_Name, -20004);
36 --
37 -- Other Error Exceptions
38 --
39 Plsql_Value_Error exception;
40 Pragma Exception_Init(Plsql_Value_Error, -6502);
41 --
42 -- Package Variables
43 --
44 g_package varchar2(33) := ' hr_api_user_hooks.';
45 --
46 g_source varchar2(32767);
47 g_error_expected boolean;
48 --
49 -- Oracle Internal DataType, Parameter, Default Codes and New Line Constants
50 --
51 c_dtype_undefined constant number default 0;
52 c_dtype_varchar2 constant number default 1;
53 c_dtype_number constant number default 2;
54 c_dtype_long constant number default 8;
55 c_dtype_date constant number default 12;
56 c_dtype_clob constant number default 112;
57 c_dtype_boolean constant number default 252;
58 --
59 c_ptype_in constant number default 0;
60 --
61 c_default_defined constant number default 1;
62 --
63 c_new_line constant varchar2(1) default '
64 ';
65 --
66 -- ----------------------------------------------------------------------------
67 -- |------------------------------< clear_source >----------------------------|
68 -- ----------------------------------------------------------------------------
69 -- {Start Of Comments}
70 --
71 -- Description:
72 -- Clears the source code store and when a package body creation error
73 -- is expected. Should be called when starting to define a new package body.
74 --
75 -- Prerequisites:
76 -- None
77 --
78 -- In Parameters:
79 -- None
80 --
81 -- Post Success:
82 -- The internal source and expected error stores are set to reset.
83 --
84 -- Post Failure:
85 -- The internal source and expected error stores are set to reset.
86 --
87 -- Access Status:
88 -- Internal Development Use Only.
89 --
90 -- {End Of Comments}
91 --
92 procedure clear_source is
93 l_proc varchar2(72) := g_package||'clear_source';
94 begin
95 hr_utility.set_location('Entering:'|| l_proc, 10);
96 g_source := null;
97 g_error_expected := false;
98 hr_utility.set_location(' Leaving:'|| l_proc, 20);
99 end clear_source;
100 --
101 -- ----------------------------------------------------------------------------
102 -- |------------------------------< add_to_source >---------------------------|
103 -- ----------------------------------------------------------------------------
104 -- {Start Of Comments}
105 --
106 -- Description:
107 -- Appends the specified source code to the end of the existing source code.
108 --
109 -- Prerequisites:
110 -- None
111 --
112 -- In Parameters:
113 -- Name Reqd Type Description
114 -- p_text Yes varchar2 Source code to add to the
115 -- existing store.
116 --
117 -- Post Success:
118 -- The extra source code is added to the existing code.
119 --
120 -- Post Failure:
121 -- If the source code size limit is exceeded then an application error
122 -- message is raised.
123 --
124 -- Access Status:
125 -- Internal Development Use Only.
126 --
127 -- {End Of Comments}
128 --
129 procedure add_to_source
130 (p_text in varchar2
131 ) is
132 l_proc varchar2(72) := g_package||'add_to_source';
133 begin
134 hr_utility.set_location('Entering:'|| l_proc, 10);
135 g_source := g_source || p_text;
136 hr_utility.set_location(' Leaving:'||l_proc, 20);
137 exception
138 when Plsql_Value_Error then
139 -- Trap attempts to create more than 32K of package body source code.
140 --
141 -- Error: ORA-06502: PL/SQL: numeric or value error. Check whether you
142 -- are attempting to create API hook package source code greater than 32K
143 -- in size. If so, reduce the number of procedures which need to be
144 -- called for this API module. The module will not execute until this
145 -- problem is resolved.
146 --
147 hr_utility.set_message(800, 'HR_51940_AHC_PACK_TOO_LARGE');
148 hr_utility.raise_error;
149 end add_to_source;
150 --
151 -- ----------------------------------------------------------------------------
152 -- |-----------------------------< error_expected >---------------------------|
153 -- ----------------------------------------------------------------------------
154 -- {Start Of Comments}
155 --
156 -- Description:
157 -- Notes an important user hook error has already occurred. The
158 -- "INVALID_SEE_COMMENT_IN_SOURCE" text has been added to the source code
159 -- to deliberately stop the package body from compiling.
160 --
161 -- Prerequisites:
162 -- None
163 --
164 -- In Parameters:
165 -- None
166 --
167 -- Post Success:
168 -- Notes an error at execution time is expected.
169 --
170 -- Post Failure:
171 -- Notes an error at execution time is expected.
172 --
173 -- Access Status:
174 -- Internal Development Use Only.
175 --
176 -- {End Of Comments}
177 --
178 procedure error_expected
179 is
180 l_proc varchar2(72) := g_package||'error_expected';
181 begin
182 hr_utility.set_location('Entering:'|| l_proc, 10);
183 g_error_expected := true;
184 hr_utility.set_location(' Leaving:'||l_proc, 20);
185 end error_expected;
186 --
187 -- ----------------------------------------------------------------------------
188 -- |----------------------------< execute_source >----------------------------|
189 -- ----------------------------------------------------------------------------
190 -- {Start Of Comments}
191 --
192 -- Description:
193 -- Executes the 'create or replace package body...' statement which has
194 -- been built-up in source code store.
195 --
196 -- Prerequisites:
197 -- The complete valid package body source code has been placed in the source
198 -- store by calling the 'add_to_source' procedure one or more times.
199 --
200 -- In Parameters:
201 -- None
202 --
203 -- Post Success:
204 -- The extra source code created in the database.
205 --
206 -- Post Failure:
207 -- The extra source code will not be created in the database. In some cases
208 -- the package body source code will be created in the database, but will be
209 -- marked as invalid.
210 --
211 -- Access Status:
212 -- Internal Development Use Only.
213 --
214 -- {End Of Comments}
215 --
216 procedure execute_source is
217 l_dynamic_cursor integer; -- Dynamic sql cursor
218 l_execute integer; -- Value returned by
219 -- dbms_sql.execute
220 l_proc varchar2(72) := g_package||'execute_source';
221 begin
222 hr_utility.set_location('Entering:'|| l_proc, 10);
223 --
224 -- The whole of the new package body code has now been built,
225 -- use dynamic SQL to execute the create or replace package statement
226 --
227 l_dynamic_cursor := dbms_sql.open_cursor;
228 dbms_sql.parse(l_dynamic_cursor, g_source, dbms_sql.v7);
229 l_execute := dbms_sql.execute(l_dynamic_cursor);
230 dbms_sql.close_cursor(l_dynamic_cursor);
231 hr_utility.set_location(' Leaving:'|| l_proc, 20);
232 exception
233 --
234 -- In case of an unexpected error close the dynamic cursor
235 -- if it was successfully opened.
236 --
237 when others then
238 if (dbms_sql.is_open(l_dynamic_cursor)) then
239 dbms_sql.close_cursor(l_dynamic_cursor);
240 end if;
241 --
242 -- If a compilation error is expected then sliently trap the error.
243 -- A user hook specific error has already been logged in the
244 -- hr_api_hook table.
245 --
246 if not g_error_expected then
247 raise;
248 end if;
249 end execute_source;
250 --
251 -- ----------------------------------------------------------------------------
252 -- |---------------------< chk_param_in_hook_proc_call >----------------------|
253 -- ----------------------------------------------------------------------------
254 -- {Start Of Comments}
255 --
256 -- Description:
257 -- Checks that a parameter exists in the hook package procedure, the
258 -- parameter has the same datatype and there are no overloaded versions.
259 -- If the parameter should be on a procedure checks the call is not to a
260 -- function. If an error is found AOL error details are set but a PL/SQL
261 -- exception is not raised. This function should be used when checking
262 -- hook procedure calls. 'chk_param_in_hook_leg_func' should be
263 -- used when checking return_legislation_code function parameters.
264 --
265 -- Prerequisites:
266 -- p_number_of_parameters, p_hook_parameter_names and
267 -- p_hook_parameter_datatypes are set with details of the hook package
268 -- procedure parameter details.
269 --
270 -- In Parameters:
271 -- Name Reqd Type Description
272 -- p_call_parameter_name Yes varchar2 Parameter in the procedure to
273 -- be called.
274 -- p_call_parameter_datatype Yes number The internal code for the
275 -- parameter datatype.
276 -- p_call_parameter_in_out Yes number The internal code for the
277 -- parameter IN/OUT type.
278 -- p_call_parameter_overload Yes number The overload number for the
279 -- call procedure parameter.
280 -- p_previous_overload Yes number The overload number for the
281 -- previous parameter on the
282 -- call procedure.
283 -- p_number_of_parameters Yes number The number of parameters to
284 -- the hook package procedure.
285 -- p_hook_parameter_names Yes Table When the number of hook
286 -- procedure parameters is
287 -- greater than zero, lists the
288 -- parameter names.
289 -- p_hook_parameter_datatypes Yes Table When the number of hook
290 -- procedure parameters is
291 -- greater than zero, lists the
292 -- parameter data types.
293 --
294 -- Post Success:
295 -- Returns true.
296 --
297 -- Post Failure:
298 -- Details of the error are added to the AOL message stack. When this
299 -- function returns false the error has not been raised. It is up to the
300 -- calling logic to raise or process the error.
301 --
302 -- Access Status:
303 -- Internal Development Use Only.
304 --
305 -- {End Of Comments}
306 --
307 function chk_param_in_hook_proc_call
308 (p_call_parameter_name in varchar2
309 ,p_call_parameter_datatype in number
310 ,p_call_parameter_in_out in number
311 ,p_call_parameter_overload in number
312 ,p_previous_overload in number
313 ,p_number_of_parameters in number
314 ,p_hook_parameter_names in tbl_parameter_name
315 ,p_hook_parameter_datatypes in tbl_parameter_datatype
316 ) return boolean is
317 l_loop number; -- Loop counter
318 l_para_found boolean; -- Indicates if the parameter has been
319 -- found in the hook parameter list.
320 l_para_valid boolean; -- Indicates if parameter is valid.
321 l_proc varchar2(72) := g_package||'chk_param_in_hook_proc_call';
322 begin
323 hr_utility.set_location('Entering:'|| l_proc, 10);
324 --
325 -- Assume the parameter is valid until an error is found
326 --
327 l_para_valid := true;
328 --
329 -- Validate the call does not have any overload versions by
330 -- checking that the overload number for the current parameter is the
331 -- same as the previous parameter.
332 --
333 if p_call_parameter_overload <> p_previous_overload then
334 -- Error: A call package procedure cannot have any PL/SQL overloaded
335 -- versions. Code to carry out this hook call has not been created.
336 hr_utility.set_message(800, 'HR_51941_AHC_CALL_NO_OVER');
337 hr_utility.set_location(l_proc, 20);
338 l_para_valid := false;
339 --
340 -- Check the argument name has been set. If it is not set the entry
341 -- returned from hr_general.describe_procedure is for a function
342 -- return value. Package functions should not be called.
343 --
344 elsif p_call_parameter_name is null then
345 -- Error: A package function cannot be called. Only package procedures
349 l_para_valid := false;
346 -- can be called. Code to carry out this hook call has not been created.
347 hr_utility.set_message(800, 'HR_51942_AHC_NO_FUNCTIONS');
348 hr_utility.set_location(l_proc, 30);
350 else
351 --
352 l_para_found := false;
353 l_loop := 0;
354 hr_utility.set_location(l_proc, 40);
355 --
356 -- Keep searching through the parameter names table until the parameter
357 -- name is found or the end of the list has been reached.
358 --
359 while (not l_para_found) and (l_loop < p_number_of_parameters) loop
360 l_loop := l_loop + 1;
361 if p_hook_parameter_names(l_loop) = p_call_parameter_name then
362 l_para_found := true;
363 end if;
364 end loop; -- end of while loop
365 hr_utility.set_location(l_proc, 50);
366 --
367 -- If the parameter has been found carry out further parameter checks
368 --
369 if l_para_found then
370 --
371 -- Check the datatype of the parameter is the same
372 -- as the parameter in the hook package.
373 --
374 if p_hook_parameter_datatypes(l_loop) <> p_call_parameter_datatype then
375 -- When the hook parameter is CLOB and the call parameter is LONG ignore
376 -- error otherwise.
377 if ( p_hook_parameter_datatypes(l_loop) = 112 and p_call_parameter_datatype = 8) then
378 l_para_valid := true;
379 else
380 -- Error: The *PARAMETER parameter to the call procedure must
381 -- have the same datatype as the value available at the hook.
382 -- Code to carry out this hook call has not been created.
383 hr_utility.set_message(800, 'HR_51943_AHC_CALL_PARA_D_TYPE');
384 hr_utility.set_message_token('PARAMETER', p_call_parameter_name);
385 hr_utility.set_location(l_proc, 60);
386 l_para_valid := false;
387 end if;
388 --
389 -- Check that the parameter to the call
390 -- package procedure is of type IN
391 --
392 elsif p_call_parameter_in_out <> 0 then
393 -- Error: At least one OUT or IN/OUT parameter has been specified
394 -- on the call procedure. You can only use IN parameters. Code to
395 -- carry out this hook call has not been created.
396 hr_utility.set_message(800, 'HR_51944_AHC_CALL_ONLY_IN_PARA');
397 hr_utility.set_location(l_proc, 70);
398 l_para_valid := false;
399 end if;
400 else
401 --
402 -- The parameter in the call package procedure could not be
403 -- found in the hook package procedure parameter list.
404 --
405 -- Error: There is a parameter to the call procedure which is not
406 -- available at this hook. Check your call procedure parameters.
407 -- Code to carry out this hook call has not been created.
408 hr_utility.set_message(800, 'HR_51945_AHC_CALL_NO_PARA');
409 hr_utility.set_location(l_proc, 80);
410 l_para_valid := false;
411 end if;
412 end if;
413 --
414 -- Return the parameter status
415 --
416 hr_utility.set_location(' Leaving:'||l_proc, 90);
417 return l_para_valid;
418 end chk_param_in_hook_proc_call;
419 --
420 -- ----------------------------------------------------------------------------
421 -- |---------------------< chk_param_in_hook_leg_func >-----------------------|
422 -- ----------------------------------------------------------------------------
423 -- {Start Of Comments}
424 --
425 -- Description:
426 -- Checks that a parameter exists in the hook package procedure, the
427 -- parameter has the same datatype and there are no overloaded versions.
428 -- If the parameter should be on a procedure checks the call is not to a
429 -- function. If an error is found AOL error details are set but a PL/SQL
430 -- exception is not raised. This procedure should be used when checking
431 -- return_legislation_code function parameters. The
432 -- 'chk_param_in_hook_proc_call' function should be used when checking
433 -- hook procedure call parameters.
434 --
435 -- Prerequisites:
436 -- p_number_of_parameters, p_hook_parameter_names and
437 -- p_hook_parameter_datatypes are set with details of the hook package
438 -- procedure parameter details.
439 --
440 -- In Parameters:
441 -- Name Reqd Type Description
442 -- p_call_parameter_name Yes varchar2 Parameter in the procedure to
443 -- be called.
444 -- p_call_parameter_datatype Yes number The internal code for the
445 -- parameter datatype.
446 -- p_call_parameter_in_out Yes number The internal code for the
447 -- parameter IN/OUT type.
448 -- p_call_parameter_overload Yes number The overload number for the
449 -- call procedure parameter.
450 -- p_previous_overload Yes number The overload number for the
451 -- previous parameter on the
452 -- call procedure.
453 -- p_number_of_parameters Yes number The number of parameters to
454 -- the hook package procedure.
455 -- p_hook_parameter_names Yes Table When the number of hook
456 -- procedure parameters is
457 -- greater than zero, lists the
458 -- parameter names.
459 -- p_hook_parameter_datatypes Yes Table When the number of hook
460 -- procedure parameters is
461 -- greater than zero, lists the
465 -- Name Type Description
462 -- parameter data types.
463 --
464 -- Post Success:
466 -- p_parameter_valid boolean Set to TRUE a parameter name
467 -- match was found and other checks
468 -- were valid.
469 -- p_hook_parameter_name varchar2 Set to the name of the parameter
470 -- which was matched. Either the
471 -- same as p_call_parameter_name or
472 -- the p_call_parameter_name _O
473 -- version.
474 --
475 -- Post Failure:
476 -- Name Type Description
477 -- p_parameter_valid boolean Set to FALSE when a parameter
478 -- name match could not be found or
479 -- when other parameter validation
480 -- failed. Details of the error are
481 -- added to the AOL message stack,
482 -- but no PL/SQL exception is raised.
483 -- It is up to the calling logic to
484 -- raise or process the error.
485 -- p_hook_parameter_name varchar2 Set to the NULL.
486 --
487 -- Access Status:
488 -- Internal Development Use Only.
489 --
490 -- {End Of Comments}
491 --
492 procedure chk_param_in_hook_leg_func
493 (p_call_parameter_name in varchar2
494 ,p_call_parameter_datatype in number
495 ,p_call_parameter_in_out in number
496 ,p_call_parameter_overload in number
497 ,p_previous_overload in number
498 ,p_number_of_parameters in number
499 ,p_hook_parameter_names in tbl_parameter_name
500 ,p_hook_parameter_datatypes in tbl_parameter_datatype
501 ,p_parameter_valid out nocopy boolean
502 ,p_hook_parameter_name out nocopy varchar2
503 ) is
504 l_loop number; -- Loop counter
505 l_find_parameter_name varchar2(32); -- Name of the parameter to search
506 -- for in the hook procedure parameter
507 -- list. Deliberately created as 32
508 -- characters to allow for appending
509 -- '_O'.
510 l_para_found boolean; -- Indicates if the parameter has been
511 -- found in the hook parameter list.
512 l_para_valid boolean; -- Indicates if parameter is valid.
513 l_proc varchar2(72) := g_package||'chk_param_in_hook_leg_func';
514 begin
515 hr_utility.set_location('Entering:'|| l_proc, 10);
516 --
517 -- Assume the parameter is valid until an error is found
518 --
519 l_para_valid := true;
520 --
521 -- Validate the call does not have any overload versions by
522 -- checking that the overload number for the current parameter is the
523 -- same as the previous parameter.
524 --
525 if p_call_parameter_overload <> p_previous_overload then
526 -- Error: A legislation package function cannot have any PL/SQL
527 -- overloaded versions. This API module will not execute until this
528 -- problem has been resolved.
529 hr_utility.set_message(800, 'HR_51946_AHK_LEG_NO_OVER');
530 hr_utility.set_location(l_proc, 20);
531 l_para_valid := false;
532 else
533 --
534 l_find_parameter_name := p_call_parameter_name;
535 l_para_found := false;
536 l_loop := 0;
537 hr_utility.set_location(l_proc, 30);
538 --
539 -- Keep searching through the parameter names table until the parameter
540 -- name is found or the end of the list has been reached.
541 --
542 while (not l_para_found) and (l_loop < p_number_of_parameters) loop
543 l_loop := l_loop + 1;
544 if p_hook_parameter_names(l_loop) = l_find_parameter_name then
545 l_para_found := true;
546 end if;
547 end loop; -- end of while loop
548 hr_utility.set_location(l_proc, 40);
549 --
550 -- If the parameter was not found attempt to search through the list
551 -- again. Except this time looking for the parameter with _O on the
552 -- end. There is no need to handle the case where the parameter name
553 -- is already greater than 28 characters in length. The local
554 -- variable is 32 characters in length, the search will fail and the
555 -- HR_51949_AHK_LEG_NO_PARA error will be correctly raised later in
556 -- this procedure.
557 --
558 if not l_para_found then
559 hr_utility.set_location(l_proc, 50);
560 l_find_parameter_name := p_call_parameter_name || '_O';
561 l_loop := 0;
562 while (not l_para_found) and (l_loop < p_number_of_parameters) loop
563 l_loop := l_loop + 1;
564 if p_hook_parameter_names(l_loop) = l_find_parameter_name then
565 l_para_found := true;
566 end if;
567 end loop; -- end of while loop
568 end if;
569 hr_utility.set_location(l_proc, 60);
570 --
571 -- If the parameter has been found carry out further parameter checks
572 --
573 if l_para_found then
574 --
575 -- Check the datatype of the parameter is the same as the parameter
576 -- in the hook package.
577 --
578 if p_hook_parameter_datatypes(l_loop) <> p_call_parameter_datatype then
579 -- Error: The *PARAMETER parameter to the legislation function must
583 hr_utility.set_message_token('PARAMETER', p_call_parameter_name);
580 -- have the same datatype as the value available at the hook. This
581 -- API module will not execute until this problem has been resolved.
582 hr_utility.set_message(800, 'HR_51947_AHK_LEG_PARA_D_TYPE');
584 hr_utility.set_location(l_proc, 70);
585 l_para_valid := false;
586 --
587 -- Check that the parameter to the call package function is
588 -- of type IN
589 --
590 elsif p_call_parameter_in_out <> 0 then
591 -- Error: All the parameters to the legislation function must be
592 -- IN parameters. OUT or IN/OUT parameters are not allowed. This
593 -- API module will not execute until this problem has been resolved.
594 hr_utility.set_message(800, 'HR_51948_AHK_LEG_ONLY_IN_PARA');
595 hr_utility.set_location(l_proc, 80);
596 l_para_valid := false;
597 end if;
598 else
599 --
600 -- The parameter in the call function could not be
601 -- found in the hook package procedure parameter list.
602 --
603 -- Error: There is a parameter to the legislation function which
604 -- is not available at this hook. This API module will not execute
605 -- until this problem has been resolved.
606 hr_utility.set_message(800, 'HR_51949_AHK_LEG_NO_PARA');
607 hr_utility.set_location(l_proc, 90);
608 l_para_valid := false;
609 end if;
610 end if;
611 hr_utility.set_location(l_proc, 100);
612 --
613 -- Pass out the parameter status values
614 --
615 if l_para_valid then
616 p_hook_parameter_name := l_find_parameter_name;
617 else
618 p_hook_parameter_name := null;
619 end if;
620 p_parameter_valid := l_para_valid;
621 hr_utility.set_location(' Leaving:'||l_proc, 110);
622 end chk_param_in_hook_leg_func;
623 --
624 -- ----------------------------------------------------------------------------
625 -- |--------------------------< make_procedure_call >-------------------------|
626 -- ----------------------------------------------------------------------------
627 -- {Start Of Comments}
628 --
629 -- Description:
630 -- Makes the source code to call another procedure.
631 --
632 -- Prerequisites:
633 -- p_number_of_parameters, p_hook_parameter_names and
634 -- p_hook_parameter_datatypes are set with details of the hook package
635 -- procedure parameter details.
636 --
637 -- In Parameters:
638 -- Name Reqd Type Description
639 -- p_api_hook_call_id Yes number ID of the hook call.
640 -- p_object_version_number Yes number OVN of the hook call.
641 -- p_call_package Yes varchar2 Name of the package to call.
642 -- p_call_procedure Yes varchar2 Name of the procedure within
643 -- p_call_package to call.
644 -- p_when_error_invalid_code Yes boolean When an error is detected
645 -- this parameter indicates
646 -- if invalid code should be
647 -- deliberately created. Set
648 -- to true for application
649 -- and legislation calls.
650 -- p_number_of_parameters Yes number The number of parameters to
651 -- the hook package procedure.
652 -- p_hook_parameter_names Yes Table When the number of hook
653 -- procedure parameters is
654 -- greater than zero, lists the
655 -- parameter names.
656 -- p_hook_parameter_datatypes Yes Table When the number of hook
657 -- procedure parameters is
658 -- greater than zero, lists the
659 -- parameter data types.
660 --
661 -- Post Success:
662 -- Creates source code for one package procedure call.
663 --
664 -- Post Failure:
665 -- Details of any application errors and some Oracle errors are written
666 -- to the HR_API_HOOK_CALLS table.
667 --
668 -- Access Status:
669 -- Internal Development Use Only.
670 --
671 -- {End Of Comments}
672 --
673 procedure make_procedure_call
674 (p_api_hook_call_id in number
675 ,p_object_version_number in number
676 ,p_call_package in varchar2
677 ,p_call_procedure in varchar2
678 ,p_when_error_invalid_code in boolean
679 ,p_number_of_parameters in number
680 ,p_hook_parameter_names in tbl_parameter_name
681 ,p_hook_parameter_datatypes in tbl_parameter_datatype
682 ) is
683 --
684 -- Local variables to catch the values returned from
685 -- hr_general.describe_procedure
686 --
687 l_overload dbms_describe.number_table;
688 l_position dbms_describe.number_table;
689 l_level dbms_describe.number_table;
690 l_argument_name dbms_describe.varchar2_table;
691 l_datatype dbms_describe.number_table;
692 l_default_value dbms_describe.number_table;
693 l_in_out dbms_describe.number_table;
694 l_length dbms_describe.number_table;
695 l_precision dbms_describe.number_table;
696 l_scale dbms_describe.number_table;
697 l_radix dbms_describe.number_table;
698 l_spare dbms_describe.number_table;
699 --
700 -- Other local variables
701 --
705 l_param_valid boolean := true; -- Indicates if the current
702 l_loop binary_integer; -- Loop counter.
703 l_pre_overload number; -- Overload number for the previous
704 -- parameter.
706 -- parameter is valid for this hook.
707 l_describe_error boolean := false; -- Indicates if the hr_general.
708 -- describe_procedure raised an
709 -- error for the call package
710 -- procedure.
711 l_encoded_err_text varchar2(2000); -- Set to the encoded error text
712 -- when an error is written to the
713 -- HR_API_HOOK_CALLS table.
714 l_call_code varchar2(32767) := null;
715 l_proc varchar2(72) := g_package||'make_procedure_call';
716 begin
717 hr_utility.set_location('Entering:'|| l_proc, 10);
718 --
719 -- Call an RDMS procedure to obtain the list of parameters to the call
720 -- package procedure. A separate begin ... end block has been specified so
721 -- that errors raised by hr_general.describe_procedure can be trapped and
722 -- handled locally.
723 --
724 begin
725 hr_general.describe_procedure
726 (object_name => p_call_package || '.' || p_call_procedure
727 ,reserved1 => null
728 ,reserved2 => null
729 ,overload => l_overload
730 ,position => l_position
731 ,level => l_level
732 ,argument_name => l_argument_name
733 ,datatype => l_datatype
734 ,default_value => l_default_value
735 ,in_out => l_in_out
736 ,length => l_length
737 ,precision => l_precision
738 ,scale => l_scale
739 ,radix => l_radix
740 ,spare => l_spare
741 );
742 exception
743 when Package_Not_Exists then
744 -- Error: The call_package does not exist in the database. Code to
745 -- carry out this hook call has not been created.
746 hr_utility.set_message(800, 'HR_51950_AHC_CALL_PKG_NO_EXIST');
747 l_describe_error := true;
748 hr_utility.set_location(l_proc, 20);
749 when Proc_Not_In_Package then
750 -- Error: The call_procedure does not exist in the call_package.
751 -- Code to carry out this hook call has not been created.
752 hr_utility.set_message(800, 'HR_51951_AHC_CALL_PRO_NO_EXIST');
753 l_describe_error := true;
754 hr_utility.set_location(l_proc, 30);
755 when Remote_Object then
756 -- Error: Remote objects cannot be called from API User Hooks.
757 -- Code to carry out this hook call has not been created.
758 hr_utility.set_message(800, 'HR_51952_AHC_CALL_REMOTE_OBJ');
759 l_describe_error := true;
760 hr_utility.set_location(l_proc, 40);
761 when Invalid_Package then
762 -- Error: The call_package code in the database is invalid.
763 -- Code to carry out this hook call has not been created.
764 hr_utility.set_message(800, 'HR_51953_AHC_CALL_PKG_INVALID');
765 l_describe_error := true;
766 hr_utility.set_location(l_proc, 50);
767 when Invalid_Object_Name then
768 -- Error: An error has occurred while attempting to parse the name of
769 -- the call package and call procedure. Check the package and procedure
770 -- names. Code to carry out this hook call has not been created.
771 hr_utility.set_message(800, 'HR_51954_AHC_CALL_PARSE');
772 l_describe_error := true;
773 hr_utility.set_location(l_proc, 60);
774 end;
775 hr_utility.set_location(l_proc, 70);
776 --
777 -- Only carry out the parameter validation if
778 -- hr_general.describe_procedure did not raise an error.
779 --
780 if not l_describe_error then
781 --
782 -- If the first parameter in the list has a data type of 'UNDEFINED'
783 -- then there are no parameters to the calling procedure.
784 --
785 if l_datatype(1) = c_dtype_undefined then
786 --
787 -- Build calling code with no parameters
788 --
789 l_call_code := p_call_package || '.' || p_call_procedure || ';';
790 l_call_code := l_call_code || c_new_line;
791 hr_utility.set_location(l_proc, 80);
792 else
793 --
794 -- Build calling code with parameters
795 --
796 l_call_code := p_call_package || '.' || p_call_procedure || c_new_line;
797 hr_utility.set_location(l_proc, 90);
798 --
799 -- Search through the tables returned to create the parameter list
800 --
801 l_loop := 1;
802 l_pre_overload := l_overload(1);
803 begin
804 --
805 -- There is separate PL/SQL block for reading from the PL/SQL tables.
806 -- We do not know how many parameters exist. So we have to keep reading
807 -- from the tables until PL/SQL finds a row when has not been
808 -- initialised and raises a NO_DATA_FOUND exception or an invalid
809 -- parameter is found.
810 --
811 while l_param_valid loop
812 --
813 -- Check that the parameter to the package procedure to be
814 -- called exists on the hook package procedure, it is of the same
815 -- datatype, the code to call is not a function and there are no
816 -- overload versions.
817 --
818 l_param_valid := chk_param_in_hook_proc_call
819 (p_call_parameter_name => l_argument_name(l_loop)
820 ,p_call_parameter_datatype => l_datatype(l_loop)
821 ,p_call_parameter_in_out => l_in_out(l_loop)
822 ,p_call_parameter_overload => l_overload(l_loop)
826 ,p_hook_parameter_datatypes => p_hook_parameter_datatypes
823 ,p_previous_overload => l_pre_overload
824 ,p_number_of_parameters => p_number_of_parameters
825 ,p_hook_parameter_names => p_hook_parameter_names
827 );
828 --
829 -- If the parameter is valid add it to the calling code
830 --
831 if l_param_valid then
832 if l_loop = 1 then
833 -- This is the first parameter prefix with an opening bracket
834 l_call_code := l_call_code || '(';
835 else
836 -- Have already processed the first parameter. Separate this
837 -- parameter from the previous parameter with a ,
838 l_call_code := l_call_code || ',';
839 end if;
840 l_call_code := l_call_code || l_argument_name(l_loop) || ' => ';
841 l_call_code := l_call_code || l_argument_name(l_loop);
842 l_call_code := l_call_code || c_new_line;
843 --
844 -- Remember the overload number for the next loop iteration.
845 --
846 l_pre_overload := l_overload(l_loop);
847 end if;
848 --
849 -- Prepare loop variables for the next iteration
850 --
851 l_pre_overload := l_overload(l_loop);
852 l_loop := l_loop + 1;
853 end loop; -- end of while loop
854 hr_utility.set_location(l_proc, 100);
855 exception
856 when no_data_found then
857 -- Trap the PL/SQL no_data_found exception. Know we have already
858 -- read the details of the last parameter from the tables.
859 if l_loop > 1 then
860 -- There must have been at least one parameter in the list. End the
861 -- parameter list with a closing bracket. The bracket should not be
862 -- included when there are zero parameters.
863 l_call_code := l_call_code || ');' || c_new_line;
864 end if;
865 end;
866 end if;
867 hr_utility.set_location(l_proc, 110);
868 --
869 if l_param_valid then
870 --
871 -- If the last parameter processed was valid then all the parameters must
872 -- be valid. Add the calling code which has been built-up to the rest of
873 -- the source code. Update the HR_API_HOOK_CALLS table to note that the
874 -- call has been successfully created.
875 --
876 add_to_source(l_call_code);
877 -- Change the following update statement to
878 -- call the row handler, when it is available.
879 update hr_api_hook_calls
880 set pre_processor_date = sysdate
881 , encoded_error = null
882 , status = 'V'
883 , object_version_number = object_version_number + 1
884 where api_hook_call_id = p_api_hook_call_id
885 and object_version_number = p_object_version_number;
886 end if;
887 end if;
888 --
889 -- If hr_general.describe_procedure raised an error or the last parameter
890 -- processed was invalid then the calling code which has been built-up is
891 -- not complete and it should not be added to the rest of the source code.
892 -- Either this procedure (for a hr_general.describe_procedure error) or
893 -- the chk_param_in_hook_proc_call function (for a parameter error) has set
894 -- the AOL message stack will details of the error. The error details should
895 -- be written to the HR_API_HOOK_CALLS using the encoded format. Some comment
896 -- text and a 'null' statement should be added to the hook package body
897 -- source code to show there is a problem.
898 --
899 if l_describe_error or (not l_param_valid) then
900 --
901 -- Get the encoded error text
902 --
903 l_encoded_err_text := fnd_message.get_encoded;
904 -- Change the following update DML statement to
905 -- call the API when it is available.
906 update hr_api_hook_calls
907 set pre_processor_date = sysdate
908 , encoded_error = l_encoded_err_text
909 , status = 'I'
910 , object_version_number = object_version_number + 1
911 where api_hook_call_id = p_api_hook_call_id
912 and object_version_number = p_object_version_number;
913 --
914 -- Add comment and null; statement to the hook package source code
915 --
916 add_to_source('-- The call to ' || p_call_package || '.');
917 add_to_source(p_call_procedure || c_new_line);
918 add_to_source('-- has not been created due to an error.' || c_new_line);
919 add_to_source('-- Details of the error, in FND encoded format, can ');
920 add_to_source('be obtained' || c_new_line);
921 add_to_source('-- with the following sql statement:' || c_new_line);
922 add_to_source('-- select c.encoded_error' || c_new_line);
923 add_to_source('-- from hr_api_hook_calls c' || c_new_line);
924 add_to_source('-- where c.api_hook_call_id = ');
925 add_to_source(to_char(p_api_hook_call_id) || ';' || c_new_line);
926 add_to_source('null;' || c_new_line);
927 --
928 -- When required add invalid code to force investigation of the problem
929 --
930 if p_when_error_invalid_code then
931 error_expected;
932 add_to_source('-- The following invalid code has been deliberately ');
933 add_to_source('created to force' || c_new_line);
934 add_to_source('-- investigation and resolution of this problem.');
935 add_to_source(c_new_line);
936 add_to_source('INVALID_SEE_COMMENT_IN_SOURCE;' || c_new_line);
937 end if;
938 hr_utility.set_location(l_proc, 120);
939 end if;
940 hr_utility.set_location(' Leaving:'|| l_proc, 130);
941 end make_procedure_call;
942 --
946 -- {Start Of Comments}
943 -- ----------------------------------------------------------------------------
944 -- |-------------------------< make_leg_function_call >-----------------------|
945 -- ----------------------------------------------------------------------------
947 --
948 -- Description:
949 -- Makes the source code to populate the l_legislation_code variable with
950 -- a call to a specified legislation package function.
951 --
952 -- Prerequisites:
953 -- The p_legislation_package and p_legislation_function parameters must both
954 -- contain not null values when this procedure is called.
955 -- The start of the hook procedure source code has already been created.
956 -- The if statement to enable legislation hook calls to be switched
957 -- off has been opened but not closed. This function assumes the
958 -- l_legislation_code variable has been declared in the hook package
959 -- procedure.
960 --
961 -- In Parameters:
962 -- Name Reqd Type Description
963 -- p_api_hook_id Yes number ID of the hook details from
964 -- the HR_API_HOOKS table.
965 -- p_legislation_package Yes varchar2 The legislation_package as
966 -- specified in the HR_API_HOOKS
967 -- table.
968 -- p_legislation_function Yes varchar2 The legislation_function as
969 -- specified in the HR_API_HOOKS
970 -- table.
971 -- p_number_of_parameters Yes number The number of parameters to
972 -- the hook package procedure.
973 -- p_hook_parameter_names Yes Table When the number of hook
974 -- procedure parameters is
975 -- greater than zero, lists the
976 -- parameter names.
977 -- p_hook_parameter_datatypes Yes Table When the number of hook
978 -- procedure parameters is
979 -- greater than zero, lists the
980 -- parameter data types.
981 -- Post Success:
982 -- Creates source code to populate the l_legislation_code variable with the
983 -- legislation package function return value. Then this function returns
984 -- true to indicate the code was successfully created.
985 --
986 -- Post Failure:
987 -- An application error message is placed on the AOL message stack, but
988 -- a PL/SQL exception is not raised. This function then returns false to
989 -- indicate the code was not created.
990 --
991 -- Access Status:
992 -- Internal Development Use Only.
993 --
994 -- {End Of Comments}
995 --
996 function make_leg_function_call
997 (p_api_hook_id in number
998 ,p_legislation_package in varchar2
999 ,p_legislation_function in varchar2
1000 ,p_number_of_parameters in number
1001 ,p_hook_parameter_names in tbl_parameter_name
1002 ,p_hook_parameter_datatypes in tbl_parameter_datatype
1003 ) return boolean is
1004 --
1005 -- Local variables to catch the values returned from
1006 -- hr_general.describe_procedure
1007 --
1008 l_overload dbms_describe.number_table;
1009 l_position dbms_describe.number_table;
1010 l_level dbms_describe.number_table;
1011 l_argument_name dbms_describe.varchar2_table;
1012 l_datatype dbms_describe.number_table;
1013 l_default_value dbms_describe.number_table;
1014 l_in_out dbms_describe.number_table;
1015 l_length dbms_describe.number_table;
1016 l_precision dbms_describe.number_table;
1017 l_scale dbms_describe.number_table;
1018 l_radix dbms_describe.number_table;
1019 l_spare dbms_describe.number_table;
1020 --
1021 -- Other local variables
1022 --
1023 l_loop number; -- Loop counter
1024 l_err_found boolean := false; -- Indicates if an error has been
1025 -- found and a message has been
1026 -- placed on the AOL message stack.
1027 l_param_valid boolean; -- Indicates if the
1028 -- chk_param_in_hook_leg_func
1029 -- procedure found the current
1030 -- l_loop parameter was valid.
1031 l_hook_parameter_name varchar2(30); -- Parameter matched by
1032 -- chk_param_in_hook_leg_func.
1033 -- Either exactly the same parameter
1034 -- name or the _O version.
1035 l_call_code varchar2(1000); -- The code to call the legislation
1036 -- package function.
1037 l_pre_overload number; -- Overload number for the previous
1038 -- parameter.
1039 l_proc varchar2(72) := g_package||'make_leg_function_call';
1040 begin
1041 hr_utility.set_location('Entering:'|| l_proc, 10);
1042 --
1043 -- Build the code to call the specified legislation package function.
1044 -- Call an RDMS procedure to obtain the list of parameters to the
1045 -- legislation package function. A separate begin ... end block has been
1046 -- specified so that errors raised by hr_general.describe_procedure can
1047 -- be trapped and handled locally.
1048 --
1049 begin
1050 hr_general.describe_procedure
1054 ,reserved2 => null
1051 (object_name => p_legislation_package || '.' ||
1052 p_legislation_function
1053 ,reserved1 => null
1055 ,overload => l_overload
1056 ,position => l_position
1057 ,level => l_level
1058 ,argument_name => l_argument_name
1059 ,datatype => l_datatype
1060 ,default_value => l_default_value
1061 ,in_out => l_in_out
1062 ,length => l_length
1063 ,precision => l_precision
1064 ,scale => l_scale
1065 ,radix => l_radix
1066 ,spare => l_spare
1067 );
1068 exception
1069 when Package_Not_Exists then
1070 -- Error: The legislation_package does not exist in the database.
1071 -- This API module will not execute until this problem has been resolved.
1072 hr_utility.set_message(800, 'HR_51955_AHK_LEG_PKG_NO_EXIST');
1073 l_err_found := true;
1074 hr_utility.set_location(l_proc, 20);
1075 when Proc_Not_In_Package then
1076 -- Error: The legislation_function does not exist in the
1077 -- legislation_package. This API module will not execute until this
1078 -- problem has been resolved.
1079 hr_utility.set_message(800, 'HR_51956_AHK_LEG_FUN_NO_EXIST');
1080 l_err_found := true;
1081 hr_utility.set_location(l_proc, 30);
1082 when Remote_Object then
1083 -- Error: Remote objects cannot be called to find out the legislation
1084 -- code. This API module will not execute until this problem has been
1085 -- resolved.
1086 hr_utility.set_message(800, 'HR_51957_AHK_LEG_REMOTE_OBJ');
1087 l_err_found := true;
1088 hr_utility.set_location(l_proc, 40);
1089 when Invalid_Package then
1090 -- Error: The legislation_package code in the database is invalid.
1091 -- This API module will not execute until this problem has been resolved.
1092 hr_utility.set_message(800, 'HR_51958_AHK_LEG_PKG_INVALID');
1093 l_err_found := true;
1094 hr_utility.set_location(l_proc, 50);
1095 when Invalid_Object_Name then
1096 -- Error: An error has occurred while attempting to parse the name of
1097 -- the legislation package and legislation function. Check the package
1098 -- and function names. This API module will not execute until this
1099 -- problem has been resolved.
1100 hr_utility.set_message(800, 'HR_51959_AHK_LEG_PARSE');
1101 l_err_found := true;
1102 hr_utility.set_location(l_proc, 60);
1103 end;
1104 hr_utility.set_location(l_proc, 70);
1105 --
1106 -- Only carry out the parameter validation if
1107 -- hr_general.describe_procedure did not raise an error.
1108 --
1109 if not l_err_found then
1110 --
1111 -- Ensure the legislation package function is really a function, and not
1112 -- a procedure, by checking the first parameter name returned by
1113 -- hr_general.describe_procedure is blank.
1114 --
1115 if l_argument_name(1) is not null then
1116 -- Error: The legislation function can only be a function. It cannot be
1117 -- a procedure. This API module will not execute until this problem has
1118 -- been resolved.
1119 hr_utility.set_message(800, 'HR_51965_AHK_LEG_MUST_FUN');
1120 l_err_found := true;
1121 hr_utility.set_location(l_proc, 80);
1122 --
1123 -- Ensure the function return datatype is varchar2.
1124 --
1125 elsif l_datatype(1) <> c_dtype_varchar2 then
1126 -- Error: The legislation function must return a varchar2 value. This
1127 -- API module will not execute until this problem has been resolved.
1128 hr_utility.set_message(800, 'HR_51966_AHK_LEG_RTN_VARCHAR');
1129 l_err_found := true;
1130 hr_utility.set_location(l_proc, 90);
1131 end if;
1132 hr_utility.set_location(l_proc, 100);
1133 if not l_err_found then
1134 --
1135 -- Build the function call and parameter list. Checking that the
1136 -- required parameters are available in the hook package procedure.
1137 -- (Details of the first parameter returned by
1138 -- hr_general.describe_procedure are not passed to the
1139 -- chk_param_in_hook_leg_func procedure. If it was passed
1140 -- across an error would be raised.
1141 --
1142 l_call_code := 'l_legislation_code := ' || p_legislation_package || '.';
1143 l_call_code := l_call_code || p_legislation_function;
1144 hr_utility.set_location(l_proc, 110);
1145 --
1146 -- Search through the tables returned to create the parameter list
1147 --
1148 l_loop := 2;
1149 l_pre_overload := l_overload(2);
1150 l_param_valid := true;
1151 begin
1152 --
1153 -- There is separate PL/SQL block for reading from the PL/SQL tables.
1154 -- We do not know how many parameters exist. So we have to keep reading
1155 -- from the tables until PL/SQL finds a row which has not been
1156 -- initialised and raises a NO_DATA_FOUND exception or an invalid
1157 -- parameter is found.
1158 --
1159 while l_param_valid loop
1160 --
1161 -- Check that the parameter to the legislation function exists
1162 -- in the hook package procedure, it is of the same datatype
1163 -- and there are no overloaded versions.
1164 --
1165 chk_param_in_hook_leg_func
1166 (p_call_parameter_name => l_argument_name(l_loop)
1167 ,p_call_parameter_datatype => l_datatype(l_loop)
1168 ,p_call_parameter_in_out => l_in_out(l_loop)
1169 ,p_call_parameter_overload => l_overload(l_loop)
1170 ,p_previous_overload => l_pre_overload
1171 ,p_number_of_parameters => p_number_of_parameters
1172 ,p_hook_parameter_names => p_hook_parameter_names
1176 );
1173 ,p_hook_parameter_datatypes => p_hook_parameter_datatypes
1174 ,p_parameter_valid => l_param_valid
1175 ,p_hook_parameter_name => l_hook_parameter_name
1177 if l_param_valid then
1178 --
1179 -- If the parameter is valid add it to the calling code
1180 --
1181 if l_loop = 2 then
1182 -- This is the first parameter prefix with an opening bracket
1183 l_call_code := l_call_code || '(';
1184 else
1185 -- Have already processed the first parameter. Separate this
1186 -- parameter from the previous parameter with a ,
1187 l_call_code := l_call_code || ',';
1188 end if;
1189 l_call_code := l_call_code || l_argument_name(l_loop) || ' => ';
1190 l_call_code := l_call_code || l_hook_parameter_name;
1191 l_call_code := l_call_code || c_new_line;
1192 else
1193 --
1194 -- If the parameter is invalid remember than an error has occurred
1195 -- Note: When this occurs the chk_param_in_hook function will
1196 -- have already placed an error message on the AOL message stack.
1197 --
1198 l_err_found := true;
1199 end if;
1200 --
1201 -- Prepare loop variables for the next iteration
1202 --
1203 l_pre_overload := l_overload(l_loop);
1204 l_loop := l_loop + 1;
1205 end loop; -- end of while loop
1206 hr_utility.set_location(l_proc, 120);
1207 exception
1208 when no_data_found then
1209 -- Trap the PL/SQL no_data_found exception. Know we have already
1210 -- read the details of the last parameter from the tables.
1211 if l_loop > 2 then
1212 -- There must have been at least one parameter in the list. End the
1213 -- parameter list with a closing bracket. The bracket should not be
1214 -- included when there are zero parameters. Note: The loop counter
1215 -- check is for 2 and not 1 because the first entry in the
1216 -- parameter list details the function return datatype.
1217 l_call_code := l_call_code || ');' || c_new_line;
1218 end if;
1219 end;
1220 end if;
1221 hr_utility.set_location(l_proc, 130);
1222 --
1223 -- If no errors have been found then all the parameters must be valid.
1224 -- Add the find legislation source code to the rest of the hook package
1225 -- source code.
1226 --
1227 if not l_err_found then
1228 add_to_source(l_call_code);
1229 end if;
1230 hr_utility.set_location(l_proc, 140);
1231 end if;
1232 hr_utility.set_location(' Leaving:'|| l_proc, 150);
1233 return not l_err_found;
1234 end make_leg_function_call;
1235 --
1236 -- ----------------------------------------------------------------------------
1237 -- |--------------------------< make_leg_bus_grp_call >-----------------------|
1238 -- ----------------------------------------------------------------------------
1239 -- {Start Of Comments}
1240 --
1241 -- Description:
1242 -- Makes the source code to populate the l_legislation_code variable with
1243 -- a call to hr_api.return_legislation_code. Only generates the code if
1244 --- the p_business_group_id or p_business_group_id_o parameter, with a
1245 -- number datatype, is available at the current hook.
1246 --
1247 -- Prerequisites:
1248 -- This procedure must NOT be called when the data for this API module is
1249 -- outside the context of a business_group_id.
1250 -- The start of the hook procedure source code has already been created.
1251 -- The if statement to enable legislation hook calls to be switched
1252 -- off has been opened but not closed. This function assumes the
1253 -- l_legislation_code variable has been declared in the hook package
1254 -- procedure.
1255 --
1256 -- In Parameters:
1257 -- Name Reqd Type Description
1258 -- p_number_of_parameters Yes number The number of parameters to
1259 -- the hook package procedure.
1260 -- p_hook_parameter_names Yes Table When the number of hook
1261 -- procedure parameters is
1262 -- greater than zero, lists the
1263 -- parameter names.
1264 -- p_hook_parameter_datatypes Yes Table When the number of hook
1265 -- procedure parameters is
1266 -- greater than zero, lists the
1267 -- parameter data types.
1268 --
1269 -- Post Success:
1270 -- Creates source code to populate the hook package, l_legislation_code
1271 -- variable with the hr_api.return_legislation_code return value. Then this
1272 -- function returns true to indicate the code was successfully created.
1273 --
1274 -- Post Failure:
1275 -- An application error message is placed on the AOL message stack, but
1276 -- a PL/SQL exception is not raised. This function then returns false to
1277 -- indicate the code was not created.
1278 --
1279 -- Access Status:
1280 -- Internal Development Use Only.
1281 --
1282 -- {End Of Comments}
1283 --
1284 function make_leg_bus_grp_call
1285 (p_number_of_parameters in number
1286 ,p_hook_parameter_names in tbl_parameter_name
1287 ,p_hook_parameter_datatypes in tbl_parameter_datatype
1288 ) return boolean is
1289 --
1290 l_loop number := 0; -- Loop counter
1291 l_bus_grp_found boolean := false; -- Indicates if the
1292 -- p_business_group_id or
1296 l_find_parameter varchar2(30); -- Name of the parameter to find.
1293 -- p_business_group_id_o parameter
1294 -- has been found in the hook
1295 -- parameter list.
1297 -- Either p_business_group_id or
1298 -- p_business_group_id_o.
1299 l_proc varchar2(72) := g_package||'make_leg_bus_grp_call';
1300 begin
1301 hr_utility.set_location('Entering:'|| l_proc, 10);
1302 --
1303 -- Check that the p_business_group_id parameter actually exists in the
1304 -- hook package procedure parameter list. Search through the parameter
1305 -- names table until the p_business_group_id parameter is found or the
1306 -- end of the list has been reached.
1307 --
1308 l_find_parameter := 'P_BUSINESS_GROUP_ID';
1309 while (not l_bus_grp_found) and (l_loop < p_number_of_parameters) loop
1310 l_loop := l_loop + 1;
1311 if p_hook_parameter_names(l_loop) = l_find_parameter then
1312 -- Check the datatype is NUMBER
1313 if p_hook_parameter_datatypes(l_loop) = c_dtype_number then
1314 l_bus_grp_found := true;
1315 end if;
1316 end if;
1317 end loop;
1318 hr_utility.set_location(l_proc, 20);
1319 --
1320 -- If the p_business_group_id parameter could not be found then search
1321 -- through the parameter list again for p_business_group_id_o.
1322 --
1323 if not l_bus_grp_found then
1324 l_find_parameter := 'P_BUSINESS_GROUP_ID_O';
1325 l_loop := 0;
1326 hr_utility.set_location(l_proc, 30);
1327 while (not l_bus_grp_found) and (l_loop < p_number_of_parameters) loop
1328 l_loop := l_loop + 1;
1329 if p_hook_parameter_names(l_loop) = l_find_parameter then
1330 -- Check the datatype is NUMBER
1331 if p_hook_parameter_datatypes(l_loop) = c_dtype_number then
1332 l_bus_grp_found := true;
1333 end if;
1334 end if;
1335 end loop;
1336 end if;
1337 hr_utility.set_location(l_proc, 40);
1338 --
1339 -- If the p_business_group_id or p_business_group_id_o number parameter
1340 -- has been found then generate a call to the hr_api.return_legislation_code
1341 -- function. Otherwise place an error message on the AOL message stack.
1342 --
1343 if l_bus_grp_found then
1344 add_to_source('l_legislation_code := hr_api.return_legislation_code');
1345 add_to_source('(p_business_group_id => ' || l_find_parameter);
1346 add_to_source(');' || c_new_line);
1347 hr_utility.set_location(l_proc, 50);
1348 else
1349 -- Error: The legislation specific code cannot be called from this hook.
1350 -- The legislation package function has not been specified in the
1351 -- HR_API_HOOKS table, and the business_group_id value is not available
1352 -- at this hook. This API module will not execute until this problem has
1353 -- been resolved.
1354 hr_utility.set_message(800, 'HR_51967_AHK_LEG_NO_SPECIFIC');
1355 hr_utility.set_location(l_proc, 60);
1356 end if;
1357 hr_utility.set_location(' Leaving:'|| l_proc, 70);
1358 return l_bus_grp_found;
1359 end make_leg_bus_grp_call;
1360 --
1361 -- ----------------------------------------------------------------------------
1362 -- |-------------------------< make_find_legislation >------------------------|
1363 -- ----------------------------------------------------------------------------
1364 -- {Start Of Comments}
1365 --
1366 -- Description:
1367 -- Makes the source code to populate the l_legislation_code variable with
1368 -- the current legislation code.
1369 --
1370 -- Prerequisites:
1371 -- This procedure must NOT be called when the data for this API module is
1372 -- outside the context of a business_group_id.
1373 -- The start of the hook procedure source code has already been created.
1374 -- The if statement to enable legislation hook calls to be switched
1375 -- off has been opened but not closed. This function assumes at least one
1376 -- legislation specific hook call exists and the l_legislation_code variable
1377 -- has been declared in the hook package procedure.
1378 --
1379 -- In Parameters:
1380 -- Name Reqd Type Description
1381 -- p_api_hook_id Yes number ID of the hook details from
1382 -- the HR_API_HOOKS table.
1383 -- p_legislation_package Yes varchar2 The legislation_package as
1384 -- specified in the HR_API_HOOKS
1385 -- table.
1386 -- p_legislation_function Yes varchar2 The legislation_function as
1387 -- specified in the HR_API_HOOKS
1388 -- table.
1389 -- p_number_of_parameters Yes number The number of parameters to
1390 -- the hook package procedure.
1391 -- p_hook_parameter_names Yes Table When the number of hook
1392 -- procedure parameters is
1393 -- greater than zero, lists the
1394 -- parameter names.
1395 -- p_hook_parameter_datatypes Yes Table When the number of hook
1396 -- procedure parameters is
1397 -- greater than zero, lists the
1398 -- parameter data types.
1399 --
1400 -- Post Success:
1401 -- Creates source code to populate the l_legislation_code variable with
1402 -- the current legislation code.
1403 --
1404 -- Post Failure:
1405 -- Comment text and invalid code is added to the hook package source code.
1409 --
1406 -- Invalid code is deliberately included to ensure that the package body
1407 -- does not compile. This will force investigation and resolution of the
1408 -- problem. Details of the error are also written to the HR_API_HOOKS table.
1410 -- Access Status:
1411 -- Internal Development Use Only.
1412 --
1413 -- {End Of Comments}
1414 --
1415 procedure make_find_legislation
1416 (p_api_hook_id in number
1417 ,p_legislation_package in varchar2
1418 ,p_legislation_function in varchar2
1419 ,p_number_of_parameters in number
1420 ,p_hook_parameter_names in tbl_parameter_name
1421 ,p_hook_parameter_datatypes in tbl_parameter_datatype
1422 ) is
1423 l_code_created boolean; -- Indicates if the make_leg_function_call
1424 -- or make_leg_bus_grp_call function has
1425 -- successfully created the code to derive
1426 -- the legislation_code.
1427 l_encoded_err_text varchar2(2000); -- When an error has occurred set to the
1428 -- AOL encoded error message text.
1429 l_proc varchar2(72) := g_package||'make_find_legislation';
1430 begin
1431 hr_utility.set_location('Entering:'|| l_proc, 10);
1432 --
1433 -- Build the source code which will find out the current legislation code
1434 --
1435 if (p_legislation_package is not null) and
1436 (p_legislation_function is not null) then
1437 --
1438 -- Build the code to call the specified legislation package function.
1439 --
1440 l_code_created := make_leg_function_call
1441 (p_api_hook_id => p_api_hook_id
1442 ,p_legislation_package => p_legislation_package
1443 ,p_legislation_function => p_legislation_function
1444 ,p_number_of_parameters => p_number_of_parameters
1445 ,p_hook_parameter_names => p_hook_parameter_names
1446 ,p_hook_parameter_datatypes => p_hook_parameter_datatypes
1447 );
1448 hr_utility.set_location(l_proc, 20);
1449 else
1450 --
1451 -- Otherwise the legislation_package and legislation_function has not
1452 -- been specified. Attempt to build the code which will use the
1453 -- p_business_group_id or p_business_group_id_o parameter to find out
1454 -- the current legislation code.
1455 --
1456 l_code_created := make_leg_bus_grp_call
1457 (p_number_of_parameters => p_number_of_parameters
1458 ,p_hook_parameter_names => p_hook_parameter_names
1459 ,p_hook_parameter_datatypes => p_hook_parameter_datatypes
1460 );
1461 hr_utility.set_location(l_proc, 30);
1462 end if;
1463 --
1464 if not l_code_created then
1465 --
1466 -- The code to derive the legislation code could not be generated then
1467 -- place details of the error in the HR_API_HOOKS table.
1468 -- Also generate some invalid code in the hook package body to prevent
1469 -- the package from compiling. This will force somebody to investigate the
1470 -- problem and will prevent legislation specific logic from being
1471 -- by-passed.
1472 --
1473 -- It is not necessary to clear hr_api_hooks.encoded_error, from previous
1474 -- generates because that will have already been done by the
1475 -- make_parameter_list procedure.
1476 --
1477 -- Create comment text in the package body source code
1478 --
1479 error_expected;
1480 add_to_source('-- Code to derive the legislation code could not be ');
1481 add_to_source('created due to an error.' || c_new_line);
1482 if (p_legislation_package is not null) and
1483 (p_legislation_function is not null) then
1484 add_to_source('-- The call to ' || p_legislation_package || '.');
1485 add_to_source(p_legislation_function || ' has not been created.');
1486 add_to_source(c_new_line);
1487 end if;
1488
1489 add_to_source('-- Details of the error, in FND encoded format, can ');
1490 add_to_source('be obtained' || c_new_line);
1491 add_to_source('-- with the following sql statement:' || c_new_line);
1492 add_to_source('-- select h.encoded_error' || c_new_line);
1493 add_to_source('-- from hr_api_hooks h' || c_new_line);
1494 add_to_source('-- where h.api_hook_id = ' || to_char(p_api_hook_id));
1495 add_to_source(';' || c_new_line);
1496
1497 add_to_source('-- The following invalid code has been deliberately ');
1498 add_to_source('created to force' || c_new_line);
1499 add_to_source('-- investigation and resolution of this problem.');
1500 add_to_source(c_new_line);
1501 add_to_source('INVALID_SEE_COMMENT_IN_SOURCE;' || c_new_line);
1502 hr_utility.set_location(l_proc, 40);
1503 --
1504 -- Write details of the error to the HR_API_HOOKS table
1505 --
1506 l_encoded_err_text := fnd_message.get_encoded;
1507 --
1508 -- Change the following update statement to
1509 -- call the row handler, when it is available.
1510 --
1511 update hr_api_hooks
1512 set encoded_error = l_encoded_err_text
1513 where api_hook_id = p_api_hook_id;
1514 hr_utility.set_location(l_proc, 50);
1515 end if;
1516 hr_utility.set_location(' Leaving:'|| l_proc, 60);
1517 end make_find_legislation;
1518 --
1519 -- ----------------------------------------------------------------------------
1520 -- |------------------------< make_legislation_calls >------------------------|
1521 -- ----------------------------------------------------------------------------
1522 -- {Start Of Comments}
1523 --
1524 -- Description:
1525 -- Makes the source code to carry out the legislation specific hook calls
1526 -- or just call the return_legislation_code function.
1527 --
1528 -- Prerequisites:
1529 -- The start of the hook procedure source code has already been created.
1533 -- context of a business_group_id calls are included in an if elsif ladder.
1530 -- Up to or after the 'begin' statement. Assumes at least one legislation
1531 -- specific hook call exists or the return_legislation_code function call
1532 -- needs to be created. When data for this module is held within the
1534 --
1535 -- In Parameters:
1536 -- Name Reqd Type Description
1537 -- p_api_hook_id Yes number ID of the hook details from
1538 -- the HR_API_HOOKS table.
1539 -- p_legislation_package Yes varchar2 The legislation_package as
1540 -- specified in the HR_API_HOOKS
1541 -- table.
1542 -- p_legislation_function Yes varchar2 The legislation_function as
1543 -- specified in the HR_API_HOOKS
1544 -- table.
1545 -- p_data_within_business_group Yes varchar2 Indicates if the data for
1546 -- this module is held within
1547 -- the context of a
1548 -- business_group_id. From the
1549 -- HR_API_MODULES table.
1550 -- p_number_of_parameters Yes number The number of parameters to
1551 -- the hook package procedure.
1552 -- p_hook_parameter_names Yes Table When the number of hook
1553 -- procedure parameters is
1554 -- greater than zero, lists the
1555 -- parameter names.
1556 -- p_hook_parameter_datatypes Yes Table When the number of hook
1557 -- procedure parameters is
1558 -- greater than zero, lists the
1559 -- parameter data types.
1560 --
1561 -- Post Success:
1562 -- Creates source code to carry out legislation specific hook calls.
1563 --
1564 -- Post Failure:
1565 -- Any application errors and some system errors are written to the
1566 -- corresponding row in the HR_API_HOOKS or HR_API_HOOK_CALLS tables.
1567 --
1568 -- Access Status:
1569 -- Internal Development Use Only.
1570 --
1571 -- {End Of Comments}
1572 --
1573 procedure make_legislation_calls
1574 (p_api_hook_id in number
1575 ,p_legislation_package in varchar2
1576 ,p_legislation_function in varchar2
1577 ,p_data_within_business_group in varchar2
1578 ,p_number_of_parameters in number
1579 ,p_hook_parameter_names in tbl_parameter_name
1580 ,p_hook_parameter_datatypes in tbl_parameter_datatype
1581 ) is
1582 --
1583 -- Cursor to obtain the legislation hook call details
1584 --
1585 cursor csr_leg_calls is
1586 select api_hook_call_id
1587 , object_version_number
1588 , legislation_code
1589 , call_package
1590 , call_procedure
1591 from hr_api_hook_calls
1592 where legislation_code is not null
1593 and enabled_flag = 'Y'
1594 and api_hook_id = p_api_hook_id
1595 order by legislation_code, sequence;
1596 --
1597 l_first_leg_call boolean; -- Indicates if the first legislation
1598 -- specific call has been processed
1599 -- for the current hook package
1600 -- procedure.
1601 l_last_legislation varchar2(30); -- Remembers which legislation the
1602 -- last specific call was for.
1603 l_proc varchar2(72) := g_package||'make_legislation_calls';
1604 begin
1605 hr_utility.set_location('Entering:'|| l_proc, 10);
1606 --
1607 -- Add support call to allow for legislation hook switch off
1608 --
1609 add_to_source('if hr_api.call_leg_hooks then' || c_new_line);
1610 hr_utility.set_location(l_proc, 20);
1611 --
1612 -- Build the source code which will find out the current legislation code
1613 -- when the data is held within the context of a business_group_id
1614 --
1615 if p_data_within_business_group = 'Y' then
1616 make_find_legislation
1617 (p_api_hook_id => p_api_hook_id
1618 ,p_legislation_package => p_legislation_package
1619 ,p_legislation_function => p_legislation_function
1620 ,p_number_of_parameters => p_number_of_parameters
1621 ,p_hook_parameter_names => p_hook_parameter_names
1622 ,p_hook_parameter_datatypes => p_hook_parameter_datatypes
1623 );
1624 end if;
1625 hr_utility.set_location(l_proc, 30);
1626 --
1627 -- Build the list of legislation procedure calls
1628 --
1629 l_first_leg_call := true;
1630 for l_leg_calls in csr_leg_calls loop
1631 if l_first_leg_call then
1632 --
1633 -- If this is the first legislation specific call and data for this
1634 -- module is held within the context of a business_group_id then start
1635 -- the 'if' statement. Otherwise include some comment text that all
1636 -- legislation hook calls will be executed regardless of the legislation
1637 -- code.
1638 --
1639 if p_data_within_business_group = 'Y' then
1640 add_to_source('if l_legislation_code = ' || '''');
1641 add_to_source(l_leg_calls.legislation_code|| '''' || ' then');
1642 add_to_source(c_new_line);
1643 else
1644 add_to_source('-- Note: All legislation hook calls will be executed ');
1645 add_to_source('regardless of the' || c_new_line);
1649 end if;
1646 add_to_source('-- legislation code because the data for this API ');
1647 add_to_source('module is not held within' || c_new_line);
1648 add_to_source('-- the context of a business_group_id.' || c_new_line);
1650 l_last_legislation := l_leg_calls.legislation_code;
1651 l_first_leg_call := false;
1652 else
1653 --
1654 -- If this is not the first legislation specific call, the legislation
1655 -- has changed since the last call and the data is held within the
1656 -- context of a business_group_id then create an 'elsif' statement.
1657 --
1658 if l_leg_calls.legislation_code <> l_last_legislation then
1659 if p_data_within_business_group = 'Y' then
1660 add_to_source('elsif l_legislation_code = ' || '''');
1661 add_to_source(l_leg_calls.legislation_code|| '''' || ' then');
1662 add_to_source(c_new_line);
1663 end if;
1664 l_last_legislation := l_leg_calls.legislation_code;
1665 end if;
1666 end if;
1667 --
1668 -- Build the actual procedure call
1669 --
1670 make_procedure_call
1671 (p_api_hook_call_id => l_leg_calls.api_hook_call_id
1672 ,p_object_version_number => l_leg_calls.object_version_number
1673 ,p_call_package => l_leg_calls.call_package
1674 ,p_call_procedure => l_leg_calls.call_procedure
1675 ,p_when_error_invalid_code => true
1676 ,p_number_of_parameters => p_number_of_parameters
1677 ,p_hook_parameter_names => p_hook_parameter_names
1678 ,p_hook_parameter_datatypes => p_hook_parameter_datatypes
1679 );
1680 end loop;
1681 hr_utility.set_location(l_proc, 40);
1682 --
1683 -- Close the legislation if elsif ladder when the data is held within the
1684 -- context of a business_group_id and at least one legislation specific
1685 -- hook call has been made. (The if elsif ladder will not have started when
1686 -- only the return_legislation_code function is called.)
1687 --
1688 if p_data_within_business_group = 'Y' and (not l_first_leg_call) then
1689 add_to_source('end if;' || c_new_line);
1690 end if;
1691 --
1692 -- Close the support if statement
1693 --
1694 add_to_source('end if;' || c_new_line);
1695 hr_utility.set_location(' Leaving:'|| l_proc, 50);
1696 end make_legislation_calls;
1697 --
1698 -- ----------------------------------------------------------------------------
1699 -- |------------------------< make_application_calls >------------------------|
1700 -- ----------------------------------------------------------------------------
1701 -- {Start Of Comments}
1702 --
1703 -- Description:
1704 -- Makes the source code to carry out the application specific hook calls.
1705 --
1706 -- Prerequisites:
1707 -- The start of the hook procedure source code has already been created.
1708 -- Up to or after the 'begin' statement. Assumes at least one application
1709 -- specific hook call exists.
1710 --
1711 -- In Parameters:
1712 -- Name Reqd Type Description
1713 -- p_api_hook_id Yes number ID of the hook details from
1714 -- the HR_API_HOOKS table.
1715 -- p_number_of_parameters Yes number The number of parameters to
1716 -- the hook package procedure.
1717 -- p_hook_parameter_names Yes Table When the number of hook
1718 -- procedure parameters is
1719 -- greater than zero, lists the
1720 -- parameter names.
1721 -- p_hook_parameter_datatypes Yes Table When the number of hook
1722 -- procedure parameters is
1723 -- greater than zero, lists the
1724 -- parameter data types.
1725 --
1726 -- Post Success:
1727 -- Creates source code to carry out application specific hook calls.
1728 --
1729 -- Post Failure:
1730 -- Any application errors and some system errors are written to the
1731 -- corresponding row in the HR_API_HOOKS or HR_API_HOOK_CALLS tables.
1732 --
1733 -- Access Status:
1734 -- Internal Development Use Only.
1735 --
1736 -- {End Of Comments}
1737 --
1738 procedure make_application_calls
1739 (p_api_hook_id in number
1740 ,p_number_of_parameters in number
1741 ,p_hook_parameter_names in tbl_parameter_name
1742 ,p_hook_parameter_datatypes in tbl_parameter_datatype
1743 ) is
1744 --
1745 -- Cursor to obtain the application hook call details
1746 --
1747 cursor csr_app_calls is
1748 select ahc.api_hook_call_id
1749 , ahc.object_version_number
1750 , ahc.call_package
1751 , ahc.call_procedure
1752 from hr_api_hook_calls ahc
1753 , fnd_product_installations fpi
1754 where ahc.api_hook_id = p_api_hook_id
1755 and ahc.enabled_flag = 'Y'
1756 and ahc.application_id is not null
1757 and ahc.application_id = fpi.application_id
1758 and ( ( ahc.app_install_status IN ('I', 'S')
1759 and ahc.app_install_status = fpi.status)
1760 or ( ahc.app_install_status = 'I_OR_S'
1761 and fpi.status IN ('I', 'S')))
1762 order by ahc.sequence;
1763 --
1764 l_proc varchar2(72) := g_package||'make_application_calls';
1765 begin
1766 hr_utility.set_location('Entering:'|| l_proc, 10);
1767 --
1768 -- Add support call to allow for application hook switch off
1769 --
1770 add_to_source('if hr_api.call_app_hooks then' || c_new_line);
1771 hr_utility.set_location(l_proc, 20);
1772 --
1776 --
1773 -- Build the list of legislation procedure calls
1774 --
1775 for l_app_calls in csr_app_calls loop
1777 -- Build the actual procedure call
1778 --
1779 make_procedure_call
1780 (p_api_hook_call_id => l_app_calls.api_hook_call_id
1781 ,p_object_version_number => l_app_calls.object_version_number
1782 ,p_call_package => l_app_calls.call_package
1783 ,p_call_procedure => l_app_calls.call_procedure
1784 ,p_when_error_invalid_code => true
1785 ,p_number_of_parameters => p_number_of_parameters
1786 ,p_hook_parameter_names => p_hook_parameter_names
1787 ,p_hook_parameter_datatypes => p_hook_parameter_datatypes
1788 );
1789 end loop;
1790 --
1791 -- Close the support if statement
1792 --
1793 add_to_source('end if;' || c_new_line);
1794 hr_utility.set_location(' Leaving:'|| l_proc, 50);
1795 end make_application_calls;
1796 --
1797 -- ----------------------------------------------------------------------------
1798 -- |--------------------------< make_customer_calls >-------------------------|
1799 -- ----------------------------------------------------------------------------
1800 -- {Start Of Comments}
1801 --
1802 -- Description:
1803 -- Makes the source code to do the customer specific hook calls.
1804 --
1805 -- Prerequisites:
1806 -- The start of the hook procedure source code has already been created.
1807 -- Up to or after the 'begin' statement. p_sequence_number_range must be set
1808 -- to 'LOW', 'HIGH' or 'ALL'.
1809 --
1810 -- In Parameters:
1811 -- Name Reqd Type Description
1812 -- p_api_hook_id Yes number ID of the hook details from
1813 -- the HR_API_HOOKS table.
1814 -- p_number_of_parameters Yes number The number of parameters to
1815 -- the hook package procedure.
1816 -- p_hook_parameter_names Yes Table When the number of hook
1817 -- procedure parameters is
1818 -- greater than zero, lists the
1819 -- parameter names.
1820 -- p_hook_parameter_datatypes Yes Table When the number of hook
1821 -- procedure parameters is
1822 -- greater than zero, lists the
1823 -- parameter data types.
1824 -- p_sequence_number_range Yes varchar2 Affects which rows are
1825 -- selected from the
1826 -- HR_API_HOOK_CALLS table.
1827 -- Must be set to 'LOW', 'HIGH'
1828 -- or 'ALL'.
1829 --
1830 -- Post Success:
1831 -- Creates source code to carry out customer specific hook calls within the
1832 -- specified sequence number range.
1833 --
1834 -- Post Failure:
1835 -- Any application errors and some Oracle errors are written to the
1836 -- corresponding row in the HR_API_HOOK_CALLS table.
1837 --
1838 -- Access Status:
1839 -- Internal Development Use Only.
1840 --
1841 -- {End Of Comments}
1842 --
1843 procedure make_customer_calls
1844 (p_api_hook_id in number
1845 ,p_number_of_parameters in number
1846 ,p_hook_parameter_names in tbl_parameter_name
1847 ,p_hook_parameter_datatypes in tbl_parameter_datatype
1848 ,p_sequence_number_range in varchar2
1849 ) is
1850 l_dynamic_sql varchar2(2000); -- Dynamic SQL statement.
1851 l_dynamic_cursor integer; -- Dynamic SQL cursor identifier.
1852 l_first_cus_call boolean; -- Indicates if the first customer
1853 -- specific call has been processed
1854 -- for the current hook package
1855 -- procedure and sequence number
1856 -- range.
1857 l_execute integer; -- Value from dbms_sql.execute
1858 l_api_hook_call_id number(15); -- Value from Dynamic cursor
1859 l_object_version_number number(15); -- Value from Dynamic cursor
1860 l_call_package varchar2(30); -- Value from Dynamic cursor
1861 l_call_procedure varchar2(30); -- Value from Dynamic cursor
1862 l_proc varchar2(72) := g_package||'make_customer_calls';
1863 begin
1864 hr_utility.set_location('Entering:'|| l_proc, 10);
1865 --
1866 -- Construct the SQL statement to be used. Dynamic SQL is being used
1867 -- because the rows to be processed depending on the sequence number
1868 -- range.
1869 --
1870 l_dynamic_sql := 'select api_hook_call_id' ||
1871 ' , object_version_number' ||
1872 ' , call_package' ||
1873 ' , call_procedure' ||
1874 ' from hr_api_hook_calls' ||
1875 ' where legislation_code is null' ||
1876 ' and application_id is null' ||
1877 ' and enabled_flag = ' || '''' || 'Y' || '''' ||
1878 ' and api_hook_id = :p_api_hook_id ' ||
1879 ' {sequence_range} ' ||
1880 ' order by legislation_code, sequence';
1881 --
1882 -- Set the sequence range
1883 --
1884 if p_sequence_number_range = 'ALL' then
1885 l_dynamic_sql := replace (l_dynamic_sql, '{sequence_range}', null);
1889 );
1886 elsif p_sequence_number_range = 'LOW' then
1887 l_dynamic_sql := replace (l_dynamic_sql, '{sequence_range}'
1888 , 'and sequence < 1000'
1890 elsif p_sequence_number_range = 'HIGH' then
1891 l_dynamic_sql := replace (l_dynamic_sql, '{sequence_range}'
1892 , 'and sequence > 1999'
1893 );
1894 else
1895 --
1896 -- The p_sequence_number_range parameter
1897 -- has been set to an invalid value.
1898 --
1899 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1900 hr_utility.set_message_token('PROCEDURE', l_proc);
1901 hr_utility.set_message_token('STEP','20');
1902 hr_utility.raise_error;
1903 end if;
1904 --
1905 -- Execute the Dynamic SQL statement which has been created
1906 --
1907 -- Open dynamic cursor
1908 l_dynamic_cursor := dbms_sql.open_cursor;
1909 hr_utility.set_location(l_proc, 30);
1910 --
1911 -- Parse dynamic SQL
1912 dbms_sql.parse(l_dynamic_cursor, l_dynamic_sql, dbms_sql.v7);
1913 hr_utility.set_location(l_proc, 40);
1914 --
1915 -- Bind dynamic SQL variable
1916 dbms_sql.bind_variable(l_dynamic_cursor, ':p_api_hook_id', p_api_hook_id);
1917 hr_utility.set_location(l_proc, 50);
1918 --
1919 -- Define dynamic SQL columns
1920 dbms_sql.define_column(l_dynamic_cursor, 1, l_api_hook_call_id);
1921 dbms_sql.define_column(l_dynamic_cursor, 2, l_object_version_number);
1922 dbms_sql.define_column(l_dynamic_cursor, 3, l_call_package, 30);
1923 dbms_sql.define_column(l_dynamic_cursor, 4, l_call_procedure, 30);
1924 hr_utility.set_location(l_proc, 60);
1925 --
1926 -- Execute and fetch dynamic SQL
1927 --
1928 l_first_cus_call := true;
1929 l_execute := dbms_sql.execute(l_dynamic_cursor);
1930 hr_utility.set_location(l_proc, 70);
1931 while dbms_sql.fetch_rows(l_dynamic_cursor) > 0 loop
1932 --
1933 -- Get the column values for the current row
1934 --
1935 dbms_sql.column_value(l_dynamic_cursor, 1, l_api_hook_call_id);
1936 dbms_sql.column_value(l_dynamic_cursor, 2, l_object_version_number);
1937 dbms_sql.column_value(l_dynamic_cursor, 3, l_call_package);
1938 dbms_sql.column_value(l_dynamic_cursor, 4, l_call_procedure);
1939 if l_first_cus_call then
1940 --
1941 -- Add support call to allow for customer hook switch off
1942 --
1943 add_to_source('if hr_api.call_cus_hooks then' || c_new_line);
1944 hr_utility.set_location(l_proc, 80);
1945 l_first_cus_call := false;
1946 end if;
1947 --
1948 -- Build the actual procedure call
1949 --
1950 make_procedure_call
1951 (p_api_hook_call_id => l_api_hook_call_id
1952 ,p_object_version_number => l_object_version_number
1953 ,p_call_package => l_call_package
1954 ,p_call_procedure => l_call_procedure
1955 ,p_when_error_invalid_code => false
1956 ,p_number_of_parameters => p_number_of_parameters
1957 ,p_hook_parameter_names => p_hook_parameter_names
1958 ,p_hook_parameter_datatypes => p_hook_parameter_datatypes
1959 );
1960 end loop;
1961 hr_utility.set_location(l_proc, 90);
1962 --
1963 -- Close Dynamic Cursor
1964 --
1965 dbms_sql.close_cursor(l_dynamic_cursor);
1966 --
1967 -- Close the support if statement
1968 --
1969 if not l_first_cus_call then
1970 add_to_source('end if;' || c_new_line);
1971 end if;
1972 hr_utility.set_location(' Leaving:'|| l_proc, 100);
1973 exception
1974 --
1975 -- In case of an unexpected error ensure
1976 -- that the Dynamic Cursor is closed.
1977 --
1978 when others then
1979 if dbms_sql.is_open(l_dynamic_cursor) then
1980 dbms_sql.close_cursor(l_dynamic_cursor);
1981 end if;
1982 raise;
1983 end make_customer_calls;
1984 --
1985 -- ----------------------------------------------------------------------------
1986 -- |-------------------------< update_disabled_calls >------------------------|
1987 -- ----------------------------------------------------------------------------
1988 -- {Start Of Comments}
1989 --
1990 -- Description:
1991 -- Any disabled hook calls are set with the pre-processor date and any error
1992 -- details are cleared. A developer could switch the enabled flag between
1993 -- pre-processor runs. It would be misleading to leave any old error details
1994 -- because:
1995 -- i) the call_procedure may have also changed.
1996 -- ii) the pre-processor has successfully executed the hook call,
1997 -- from the latest version of the hook package body.
1998 --
1999 -- Prerequisites:
2000 -- This hook is known to exist in the HR_API_HOOKS table.
2001 --
2002 -- In Parameters:
2003 -- Name Reqd Type Description
2004 -- p_api_hook_id Yes number ID of the hook details from
2005 -- the HR_API_HOOKS table.
2006 --
2007 -- Post Success:
2008 -- Updates all the rows in the HR_API_HOOK_CALLS table which match the
2009 -- api_hook_id and the enabled_flag is 'N'. Customer and legislation
2010 -- hook calls are updated. The encoded_error, status and pre_processor_date
2011 -- columns are populated.
2012 --
2013 -- Post Failure:
2014 -- Any errors are raised as a PL/SQL exception.
2015 --
2016 -- Access Status:
2017 -- Internal Development Use Only.
2018 --
2019 -- {End Of Comments}
2020 --
2021 procedure update_disabled_calls
2022 (p_api_hook_id in number
2023 ) is
2024 --
2025 -- Cursor to find all the disabled, hook calls for one hook.
2026 --
2027 cursor csr_disabled is
2028 select api_hook_call_id
2032 and api_hook_id = p_api_hook_id;
2029 , object_version_number
2030 from hr_api_hook_calls
2031 where enabled_flag = 'N'
2033 --
2034 l_proc varchar2(72) := g_package||'update_disabled_calls';
2035 begin
2036 hr_utility.set_location('Entering:'|| l_proc, 10);
2037 --
2038 -- Update all disabled, hook calls for a particular hook
2039 --
2040 for l_disabled in csr_disabled loop
2041 -- Change the following update statement to
2042 -- call the row handler, when it is available.
2043 update hr_api_hook_calls
2044 set pre_processor_date = sysdate
2045 , encoded_error = null
2046 , status = 'V'
2047 , object_version_number = object_version_number + 1
2048 where api_hook_call_id = l_disabled.api_hook_call_id
2049 and object_version_number = l_disabled.object_version_number;
2050 end loop; -- End Disabled Loop
2051 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2052 end update_disabled_calls;
2053 --
2054 -- ----------------------------------------------------------------------------
2055 -- |----------------------------< procedure_code >----------------------------|
2056 -- ----------------------------------------------------------------------------
2057 -- {Start Of Comments}
2058 --
2059 -- Description:
2060 -- Makes the source code after the parameter list for one hook package
2061 -- procedure.
2062 --
2063 -- Prerequisites:
2064 -- The procedure name and parameter list has already been created, up to
2065 -- and including the 'is' statement.
2066 --
2067 -- In Parameters:
2068 -- Name Reqd Type Description
2069 -- p_hook_package Yes varchar2 Name of the hook package.
2070 -- p_hook_procedure Yes varchar2 Name of the hook procedure
2071 -- within the hook package.
2072 -- p_api_hook_type Yes varchar2 Type of Hook.
2073 -- p_api_hook_id Yes number ID of the hook details from
2074 -- the HR_API_HOOKS table.
2075 -- p_legislation_package Yes varchar2 The legislation_package as
2076 -- specified in the HR_API_HOOKS
2077 -- table.
2078 -- p_legislation_function Yes varchar2 The legislation_function as
2079 -- specified in the HR_API_HOOKS
2080 -- table.
2081 -- p_module_name Yes varchar2 API Module name from the
2082 -- HR_API_MODULES table.
2083 -- p_data_within_business_group Yes varchar2 Indicates if the data for
2084 -- this module is held within
2085 -- the context of a
2086 -- business_group_id. From the
2087 -- HR_API_MODULES table.
2088 -- p_number_of_parameters Yes number The number of parameters to
2089 -- the hook package procedure.
2090 -- p_hook_parameter_names Yes Table When the number of hook
2091 -- procedure parameters is
2092 -- greater than zero, lists the
2093 -- parameter names.
2094 -- p_hook_parameter_datatypes Yes Table When the number of hook
2095 -- procedure parameters is
2096 -- greater than zero, lists the
2097 -- parameter data types.
2098 --
2099 -- Post Success:
2100 -- Creates source code for the hook procedure in the source store.
2101 --
2102 -- Post Failure:
2103 -- Most application and some Oracle errors are written to the corresponding
2104 -- rows in the HR_API_HOOKS or HR_API_HOOK_CALLS tables. Some application
2105 -- or Oracle errors are raised from this procedure as PL/SQL exceptions.
2106 --
2107 -- Access Status:
2108 -- Internal Development Use Only.
2109 --
2110 -- {End Of Comments}
2111 --
2112 procedure procedure_code
2113 (p_hook_package in varchar2
2114 ,p_hook_procedure in varchar2
2115 ,p_api_hook_type in varchar2
2116 ,p_api_hook_id in number
2117 ,p_legislation_package in varchar2
2118 ,p_legislation_function in varchar2
2119 ,p_module_name in varchar2
2120 ,p_data_within_business_group in varchar2
2121 ,p_number_of_parameters in number
2122 ,p_hook_parameter_names in tbl_parameter_name
2123 ,p_hook_parameter_datatypes in tbl_parameter_datatype
2124 ) is
2125 --
2126 -- Cursor to find out if any enabled, customer specific
2127 -- hook calls exist for this hook.
2128 --
2129 cursor csr_cus_call_exist is
2130 select 1
2131 from hr_api_hook_calls
2132 where enabled_flag = 'Y'
2133 and legislation_code is null
2134 and application_id is null
2135 and api_hook_id = p_api_hook_id;
2136 --
2137 -- Cursor to find out if any enabled, legislation specific
2138 -- hook calls exist for this hook.
2139 --
2140 cursor csr_leg_call_exist is
2141 select 1
2142 from hr_api_hook_calls
2143 where enabled_flag = 'Y'
2144 and legislation_code is not null
2145 and api_hook_id = p_api_hook_id;
2146 --
2147 -- Cursor to find out if any enabled, application specific
2148 -- hook calls exist which match the called Application
2152 select 1
2149 -- install status for this hook.
2150 --
2151 cursor csr_app_call_exist is
2153 from hr_api_hook_calls ahc
2154 , fnd_product_installations fpi
2155 where ahc.api_hook_id = p_api_hook_id
2156 and ahc.enabled_flag = 'Y'
2157 and ahc.application_id is not null
2158 and ahc.application_id = fpi.application_id
2159 and ( ( ahc.app_install_status IN ('I', 'S')
2160 and ahc.app_install_status = fpi.status)
2161 or ( ahc.app_install_status = 'I_OR_S'
2162 and fpi.status IN ('I', 'S')));
2163 --
2164 -- For before_process hooks find out if any enabled, legislation
2165 -- specific hook calls exist for the corresponding after_process
2166 -- hook.
2167 --
2168 cursor csr_ap_leg_call_exist is
2169 select 1
2170 from hr_api_hook_calls ahc
2171 , hr_api_hooks ahk_ap
2172 , hr_api_modules amk
2173 , hr_api_hooks ahk_bp
2174 where ahc.enabled_flag = 'Y'
2175 and ahc.legislation_code is not null
2176 and ahc.api_hook_id = ahk_ap.api_hook_id
2177 and ahk_ap.api_hook_type = 'AP'
2178 and ahk_ap.api_module_id = amk.api_module_id
2179 and amk.api_module_id = ahk_bp.api_module_id
2180 and ahk_bp.api_hook_type = 'BP'
2181 and ahk_bp.api_hook_id = p_api_hook_id;
2182 --
2183 l_cus_call_exist boolean; -- Indicates if at least one customer
2184 -- specific hook call exists from this
2185 -- hook.
2186 l_leg_call_exist boolean; -- Indicates if at least one legislation
2187 -- specific hook call exists from this
2188 -- hook.
2189 l_app_call_exist boolean; -- Indicates if at least one application
2190 -- specific hook call exists from this
2191 -- hook and the called application install
2192 -- status matches with the hook call
2193 -- install status.
2194 l_ap_leg_call_exist boolean; -- Indicates this is a before_process
2195 -- hook (BP) and at least one legislation
2196 -- specific hook call exists from the
2197 -- corresponding after_process (AP) hook.
2198 -- Always set to false if this is not a
2199 -- before_process hook.
2200 l_exists number(15);
2201 l_proc varchar2(72) := g_package||'procedure_code';
2202 begin
2203 hr_utility.set_location('Entering:'|| l_proc, 10);
2204 --
2205 -- Work out the status of the all the types of hook call. These values
2206 -- affect how much code should be created in the hook procedure.
2207 --
2208 -- Find out if any enabled, customer hook calls exist for the
2209 -- current hook package procedure
2210 --
2211 open csr_cus_call_exist;
2212 fetch csr_cus_call_exist into l_exists;
2213 l_cus_call_exist := csr_cus_call_exist%found;
2214 close csr_cus_call_exist;
2215 hr_utility.set_location(l_proc, 20);
2216 --
2217 -- Find out if any enabled, legislation hook calls exist for the
2218 -- current hook package procedure
2219 --
2220 open csr_leg_call_exist;
2221 fetch csr_leg_call_exist into l_exists;
2222 l_leg_call_exist := csr_leg_call_exist%found;
2223 close csr_leg_call_exist;
2224 hr_utility.set_location(l_proc, 30);
2225 --
2226 -- Find out if any enabled, application hook calls exist which
2227 -- match the called Application install status for the
2228 -- current hook package procedure
2229 --
2230 open csr_app_call_exist;
2231 fetch csr_app_call_exist into l_exists;
2232 l_app_call_exist := csr_app_call_exist%found;
2233 close csr_app_call_exist;
2234 hr_utility.set_location(l_proc, 35);
2235 --
2236 -- For before_process hooks where data is held within the context
2237 -- of a business group find out if any enabled, legislation
2238 -- specific hook calls exist for the corresponding after_process
2239 -- hook.
2240 --
2241 if p_api_hook_type = 'BP' and p_data_within_business_group = 'Y' then
2242 open csr_ap_leg_call_exist;
2243 fetch csr_ap_leg_call_exist into l_exists;
2244 l_ap_leg_call_exist := csr_ap_leg_call_exist%found;
2245 close csr_ap_leg_call_exist;
2246 hr_utility.set_location(l_proc, 40);
2247 else
2248 l_ap_leg_call_exist := false;
2249 hr_utility.set_location(l_proc, 50);
2250 end if;
2251 --
2252 -- Create the procedure code. Include or exclude sections depending
2253 -- on the existence of the different types of hook.
2254 --
2255 if l_cus_call_exist or l_leg_call_exist or
2256 l_app_call_exist or l_ap_leg_call_exist then
2257 --
2258 -- At least one hook call exists or just a call to a
2259 -- return_legislation_code function. Define all the possible local
2260 -- variables. (Even though all of them may not be used.) Also the
2261 -- create the begin statement, the 'entering' set_location.
2262 --
2263 -- Local variables
2264 add_to_source('l_commit_unit_number number;' || c_new_line);
2265 add_to_source('l_legislation_code varchar2(30);' || c_new_line);
2266 -- Begin
2267 add_to_source('begin' || c_new_line);
2268 -- 'Entering' set location
2269 add_to_source('hr_utility.set_location(' || '''' || 'Entering: ');
2270 add_to_source(p_hook_package || '.' || p_hook_procedure || '''');
2271 add_to_source(', 10);' || c_new_line);
2272 hr_utility.set_location(l_proc, 60);
2273 --
2274 if l_cus_call_exist or l_leg_call_exist or l_app_call_exist then
2278 hr_utility.set_location(l_proc, 70);
2275 -- When a hook call will be made obtain the commit unit number
2276 add_to_source('l_commit_unit_number := hr_api.return_commit_unit;');
2277 add_to_source(c_new_line);
2279 end if;
2280 --
2281 if l_leg_call_exist or l_app_call_exist or l_ap_leg_call_exist then
2282 --
2283 -- When a legislation specific call exists or
2284 -- an application specific call exists or
2285 -- just calling a return_legilsation_code function:
2286 -- 1st) Process any customer specific calls with a low sequence number.
2287 -- 2nd) Process any application specific calls.
2288 -- 3nd) Process any legislation specific calls.
2289 -- 4th) Process any customer specific calls with a high sequence
2290 -- number.
2291 --
2292 -- If this is a before_process hook where data is held within the
2293 -- context of a business group and there is a legislation
2294 -- specific call from the corresponding after_process hook then
2295 -- the return_legislation_code function is called. Even if there are
2296 -- no legislation specific hook calls from this hook. This is done
2297 -- to ensure the return_legislation_code function package global
2298 -- variables are set, before a row is deleted from the database.
2299 -- If this was not done the legislation code could be derived just
2300 -- from the after_process hook, as the row will not exist.
2301 --
2302 -- If this is an after_process hook and there is at least one
2303 -- legislation or application specific hook call then include a
2304 -- call to hr_multi_message.end_validation_set. This is required
2305 -- to support Multiple Message Detection.
2306 --
2307 hr_utility.set_location(l_proc, 80);
2308 if l_cus_call_exist then
2309 --
2310 -- Process any customer specific calls with a low sequence number.
2311 --
2312 make_customer_calls
2313 (p_api_hook_id => p_api_hook_id
2314 ,p_number_of_parameters => p_number_of_parameters
2315 ,p_hook_parameter_names => p_hook_parameter_names
2316 ,p_hook_parameter_datatypes => p_hook_parameter_datatypes
2317 ,p_sequence_number_range => 'LOW'
2318 );
2319 end if;
2320 --
2321 if l_app_call_exist then
2322 --
2323 -- Process any application specific calls if they exist.
2324 --
2325 make_application_calls
2326 (p_api_hook_id => p_api_hook_id
2327 ,p_number_of_parameters => p_number_of_parameters
2328 ,p_hook_parameter_names => p_hook_parameter_names
2329 ,p_hook_parameter_datatypes => p_hook_parameter_datatypes
2330 );
2331 end if;
2332 --
2333 if l_leg_call_exist or l_ap_leg_call_exist then
2334 --
2335 -- Process any legislation specific calls or
2336 -- just make the return_legislation_code function call.
2337 --
2338 make_legislation_calls
2339 (p_api_hook_id => p_api_hook_id
2340 ,p_legislation_package => p_legislation_package
2341 ,p_legislation_function => p_legislation_function
2342 ,p_data_within_business_group => p_data_within_business_group
2343 ,p_number_of_parameters => p_number_of_parameters
2344 ,p_hook_parameter_names => p_hook_parameter_names
2345 ,p_hook_parameter_datatypes => p_hook_parameter_datatypes
2346 );
2347 end if;
2348 --
2349 if l_cus_call_exist then
2350 --
2351 -- Process any customer specific calls with a high sequence number.
2352 --
2353 make_customer_calls
2354 (p_api_hook_id => p_api_hook_id
2355 ,p_number_of_parameters => p_number_of_parameters
2356 ,p_hook_parameter_names => p_hook_parameter_names
2357 ,p_hook_parameter_datatypes => p_hook_parameter_datatypes
2358 ,p_sequence_number_range => 'HIGH'
2359 );
2360 hr_utility.set_location(l_proc, 110);
2361 end if;
2362 --
2363 if p_api_hook_type = 'AP' and
2364 (l_leg_call_exist or l_app_call_exist) then
2365 --
2366 -- For After_process user hooks which contain legislation
2367 -- or Application hook calls include a call to
2368 -- hr_multi_message.end_validation_set.
2369 --
2370 add_to_source('hr_multi_message.end_validation_set;' || c_new_line);
2371 end if;
2372 --
2373 else
2374 --
2375 -- When there are no legislation or application specific work,
2376 -- process all the customer specific calls together. This keeps
2377 -- the number of if statements in the generated source code to
2378 -- a minimum.
2379 --
2380 make_customer_calls
2381 (p_api_hook_id => p_api_hook_id
2382 ,p_number_of_parameters => p_number_of_parameters
2383 ,p_hook_parameter_names => p_hook_parameter_names
2384 ,p_hook_parameter_datatypes => p_hook_parameter_datatypes
2385 ,p_sequence_number_range => 'ALL'
2386 );
2387 hr_utility.set_location(l_proc, 120);
2388 end if;
2389 --
2390 if l_cus_call_exist or l_leg_call_exist or l_app_call_exist then
2391 -- When a hook call has be made check that commit unit
2392 -- number has not changed since the start of the hook.
2393 --
2394 add_to_source('hr_api.validate_commit_unit(l_commit_unit_number, ');
2395 add_to_source('''' || p_module_name || '''' || ', ' || '''');
2396 add_to_source(p_api_hook_type || '''' || ');' || c_new_line);
2397 hr_utility.set_location(l_proc, 130);
2398 end if;
2399 else
2403 --
2400 --
2401 -- No enabled hook calls exist. Do not define any local variables.
2402 -- Just define the begin statement and the 'entering' set_location.
2404 add_to_source('begin' || c_new_line);
2405 add_to_source('hr_utility.set_location(' || '''' || 'Entering: ');
2406 add_to_source(p_hook_package || '.' || p_hook_procedure || '''');
2407 add_to_source(', 10);' || c_new_line);
2408 hr_utility.set_location(l_proc, 140);
2409 end if;
2410 --
2411 -- Any hook calls which have been disabled ensure the encoded_error,
2412 -- status and pre_processor_date columns are updated.
2413 -- (This has to be done here because the make_legislation_calls and
2414 -- make_customer_calls procedures are only called when corresponding
2415 -- enabled hook calls exists.)
2416 --
2417 update_disabled_calls
2418 (p_api_hook_id => p_api_hook_id
2419 );
2420 hr_utility.set_location(' Leaving:'|| l_proc, 150);
2421 end procedure_code;
2422 --
2423 -- ----------------------------------------------------------------------------
2424 -- |-------------------------< make_parameter_list >--------------------------|
2425 -- ----------------------------------------------------------------------------
2426 -- {Start Of Comments}
2427 --
2428 -- Description:
2429 -- Makes the list of parameters for one hook package procedure.
2430 --
2431 -- Prerequisites:
2432 -- The start of the procedure code has already been created and added
2433 -- to the source store. The hook_procedure is known to exist in the
2434 -- hook_package.
2435 --
2436 -- In Parameters:
2437 -- Name Reqd Type Description
2438 -- p_hook_package Yes varchar2 Name of the hook package.
2439 -- p_hook_procedure Yes varchar2 Name of the hook procedure
2440 -- within the hook package.
2441 -- p_api_hook_id Yes number ID of the hook details from
2442 -- the HR_API_HOOKS table.
2443 --
2444 -- Post Success:
2445 -- Creates the list of parameters in the source store.
2446 -- p_param_list_error is set to false. p_number_of_parameters is set to the
2447 -- number of parameters which exists in the hook package procedure. When
2448 -- p_number_of_parameters is greater than zero then p_hook_parameter_names
2449 -- is populated with the list of parameter names to hook package procedure.
2450 -- p_hook_parameter_datatypes is set to the corresponding parameter
2451 -- datatypes. The internal Oracle datatypes number codes are used, not the
2452 -- text string representations. hr_api_hooks.encoded_error is updated to
2453 -- null.
2454 --
2455 -- Post Failure:
2456 -- When a hook package procedure header or parameter error occurs the
2457 -- parameter list is not constructed. p_param_list_error is set to true.
2458 -- Details of the error are written to the hr_api_hooks.encoded_error
2459 -- column. A comment and invalid code is deliberately added to the source
2460 -- code to force the problem to be investigated and resolved. The
2461 -- p_number_of_parameters, p_hook_parameter_names and
2462 -- p_hook_parameter_datatypes parameters contain undefined values and no
2463 -- attempt should be made to use them. Details of the error are written to
2464 -- hr_api_hooks.encoded_error.
2465 --
2466 -- Access Status:
2467 -- Internal Development Use Only.
2468 --
2469 -- {End Of Comments}
2470 --
2471 procedure make_parameter_list
2472 (p_hook_package in varchar2
2473 ,p_hook_procedure in varchar2
2474 ,p_api_hook_id in number
2475 ,p_number_of_parameters out nocopy number
2476 ,p_hook_parameter_names out nocopy tbl_parameter_name
2477 ,p_hook_parameter_datatypes out nocopy tbl_parameter_datatype
2478 ,p_param_list_error out nocopy boolean
2479 ) is
2480 --
2481 -- Local variables to catch the values returned from
2482 -- hr_general.describe_procedure
2483 --
2484 l_overload dbms_describe.number_table;
2485 l_position dbms_describe.number_table;
2486 l_level dbms_describe.number_table;
2487 l_argument_name dbms_describe.varchar2_table;
2488 l_datatype dbms_describe.number_table;
2489 l_default_value dbms_describe.number_table;
2490 l_in_out dbms_describe.number_table;
2491 l_length dbms_describe.number_table;
2492 l_precision dbms_describe.number_table;
2493 l_scale dbms_describe.number_table;
2494 l_radix dbms_describe.number_table;
2495 l_spare dbms_describe.number_table;
2496 --
2497 -- Other local variables
2498 --
2499 l_loop binary_integer; -- Loop counter
2500 l_error boolean := false; -- Indicates if the
2501 -- hr_general.
2502 -- describe_procedure raised an
2503 -- error for the hook package
2504 -- procedure. Or shows there is
2505 -- a problem with a parameter
2506 -- to the hook package
2507 -- procedure.
2508 l_pre_overload number; -- Previous parameter overload
2509 -- number.
2510 l_datatype_str varchar2(20); -- String equivalent of
2511 -- l_datatype number.
2512 l_param_code varchar2(20000) := null; -- The parameter list code.
2513 l_encoded_err_text varchar2(2000); -- Set to the encoded error text
2514 -- when an error is written to
2518 hr_utility.set_location('Entering:'|| l_proc, 10);
2515 -- the HR_API_HOOKS table.
2516 l_proc varchar2(72) := g_package||'make_parameter_list';
2517 begin
2519 --
2520 -- Call an RDMS procedure to obtain the list of parameters to the hook
2521 -- package procedure. A separate begin ... end block has been specified so
2522 -- that errors raised by hr_general.describe_procedure can be trapped and
2523 -- handled locally.
2524 --
2525 begin
2526 hr_general.describe_procedure
2527 (object_name => p_hook_package || '.' || p_hook_procedure
2528 ,reserved1 => null
2529 ,reserved2 => null
2530 ,overload => l_overload
2531 ,position => l_position
2532 ,level => l_level
2533 ,argument_name => l_argument_name
2534 ,datatype => l_datatype
2535 ,default_value => l_default_value
2536 ,in_out => l_in_out
2537 ,length => l_length
2538 ,precision => l_precision
2539 ,scale => l_scale
2540 ,radix => l_radix
2541 ,spare => l_spare
2542 );
2543 exception
2544 when Package_Not_Exists then
2545 -- Error: The hook package header source code cannot be found in the
2546 -- database. Either the package header has not been loaded into the
2547 -- database or the hook package name specified in the HR_API_HOOKS
2548 -- table is incorrect. This API module will not execute until this
2549 -- problem has been resolved.
2550 hr_utility.set_message(800, 'HR_51960_AHK_HK_PKG_NOT_FOUND');
2551 l_error := true;
2552 hr_utility.set_location(l_proc, 20);
2553 when Proc_Not_In_Package then
2554 -- Error: The hook procedure does not exist in the hook package.
2555 -- This API module will not execute until this problem has been
2556 -- resolved.
2557 hr_utility.set_message(800, 'HR_51961_AHK_HK_PRO_NO_EXIST');
2558 l_error := true;
2559 hr_utility.set_location(l_proc, 30);
2560 when Remote_Object then
2561 -- Error: Remote objects cannot used for API hook package procedures.
2562 -- This API module will not execute until this problem has been
2563 -- resolved.
2564 hr_utility.set_message(800, 'HR_51962_AHK_HK_REMOTE_OBJ');
2565 l_error := true;
2566 hr_utility.set_location(l_proc, 40);
2567 when Invalid_Package then
2568 -- Error: The hook package code in the database is invalid.
2569 -- This API module will not execute until this problem has been
2570 -- resolved.
2571 hr_utility.set_message(800, 'HR_51963_AHK_HK_PKG_INVALID');
2572 l_error := true;
2573 hr_utility.set_location(l_proc, 50);
2574 when Invalid_Object_Name then
2575 -- Error: An error has occurred while attempting to parse the name of
2576 -- the hook package and hook procedure. Check the package and procedure
2577 -- names. This API module will not execute until this problem has been
2578 -- resolved.
2579 hr_utility.set_message(800, 'HR_51964_AHK_HK_PARSE');
2580 l_error := true;
2581 hr_utility.set_location(l_proc, 60);
2582 end;
2583 hr_utility.set_location(l_proc, 70);
2584 --
2585 -- Only continue with the individual parameter validation if
2586 -- hr_general.describe_procedure did not raise an error.
2587 --
2588 if not l_error then
2589 --
2590 -- If the first parameter in the list has a data type of 'UNDEFINED'
2591 -- then there are no parameters to the procedure. The parameter
2592 -- list only has to be built if first parameter is not 'UNDEFINED'.
2593 --
2594 l_loop := 1;
2595 if l_datatype(1) <> c_dtype_undefined then
2596 --
2597 -- Search through the tables returned to create the parameter list
2598 --
2599 l_pre_overload := l_overload(1);
2600 begin
2601 --
2602 -- There is separate PL/SQL block for reading from the PL/SQL tables.
2603 -- We do not know how many parameters exist. So we have to keep reading
2604 -- from the tables until PL/SQL finds a row which has not been
2605 -- initialised and raises a NO_DATA_FOUND exception.
2606 --
2607 <<step_through_param_list>>
2608 loop
2609 --
2610 -- Check the parameter data type is VARCHAR2(1), NUMBER(2), DATE(12),
2611 -- BOOLEAN(252) or LONG(8). Record, table and all other datatypes
2612 -- are not allowed.
2613 --
2614 if l_datatype(l_loop) <> c_dtype_varchar2 and
2615 l_datatype(l_loop) <> c_dtype_number and
2616 l_datatype(l_loop) <> c_dtype_date and
2617 l_datatype(l_loop) <> c_dtype_boolean and
2618 l_datatype(l_loop) <> c_dtype_clob and
2619 l_datatype(l_loop) <> c_dtype_long then
2620 -- Error: In a hook package procedure all the parameter datatypes
2621 -- must be VARCHAR2, NUMBER, DATE, BOOLEAN or LONG. This API
2622 -- module will not execute until this problem has been resolved.
2623 hr_utility.set_message(800, 'HR_51968_AHK_HK_PARA_D_TYPE');
2624 l_error := true;
2625 hr_utility.set_location(l_proc, 80);
2626 else
2627 -- Set the datatype string with the corresponding word value
2628 if l_datatype(l_loop) = c_dtype_varchar2 then
2629 l_datatype_str := 'VARCHAR2';
2630 elsif l_datatype(l_loop) = c_dtype_number then
2631 l_datatype_str := 'NUMBER';
2632 elsif l_datatype(l_loop) = c_dtype_date then
2633 l_datatype_str := 'DATE';
2634 elsif l_datatype(l_loop) = c_dtype_boolean then
2635 l_datatype_str := 'BOOLEAN';
2636 elsif l_datatype(l_loop) = c_dtype_clob then
2637 l_datatype_str := 'CLOB';
2641 end if;
2638 else
2639 l_datatype_str := 'LONG';
2640 end if;
2642 --
2643 -- Check the parameter is an IN parameter.
2644 -- OUT and IN/OUT is not allowed.
2645 --
2646 if l_in_out(l_loop) <> c_ptype_in then
2647 -- Error: In a hook package procedure all the parameters must IN
2648 -- parameters. OUT or IN/OUT parameters are not allowed. This API
2649 -- module will not execute until this problem has been resolved.
2650 hr_utility.set_message(800, 'HR_51969_AHK_HK_ONLY_IN_PARA');
2651 l_error := true;
2652 hr_utility.set_location(l_proc, 90);
2653 --
2654 -- Check the parameter does not have a default value.
2655 --
2656 elsif l_default_value(l_loop) = c_default_defined then
2657 -- Error: You cannot define default values for parameters to a
2658 -- hook package procedure. Ensure no defaults are defined. This
2659 -- API module will not execute until this problem has been
2660 -- resolved.
2661 hr_utility.set_message(800, 'HR_51970_AHK_HK_NO_DEFLT_PARA');
2662 l_error := true;
2663 hr_utility.set_location(l_proc, 100);
2664 --
2665 -- Check the overload number has not changed. More than one PL/SQL
2666 -- version of the same procedure is not allowed.
2667 --
2668 elsif l_pre_overload <> l_overload(l_loop) then
2669 -- Error: A hook package procedure cannot have any PL/SQL
2670 -- overloaded versions. This API module will not execute until
2671 -- this problem has been resolved.
2672 hr_utility.set_message(800, 'HR_51971_AHK_HK_NO_OVER');
2673 l_error := true;
2674 hr_utility.set_location(l_proc, 110);
2675 --
2676 -- Check the argument name has been set. If it is not set entry
2677 -- returned from hr_general.describe_procedure is for a function
2678 -- return value. Hook package functions should not be called.
2679 --
2680 elsif l_argument_name(l_loop) is null then
2681 -- Error: The hook package procedure can only be a procedure. It
2682 -- cannot be a function. This API module will not execute until
2683 -- this problem has been resolved.
2684 hr_utility.set_message(800, 'HR_51972_AHK_HK_NO_FUNCTIONS');
2685 l_error := true;
2686 hr_utility.set_location(l_proc, 120);
2687 end if;
2688 --
2689 if not l_error then
2690 --
2691 -- The parameter has passed all the validation. Add it to the
2692 -- source code.
2693 --
2694 if l_loop = 1 then
2695 -- This is the first parameter prefix with an opening bracket
2696 l_param_code := '(';
2697 else
2698 -- Have already processed the first parameter. Separate this
2699 -- parameter from the previous parameter with a ,
2700 l_param_code := l_param_code || ',';
2701 end if;
2702 hr_utility.set_location(l_proc, 130);
2703 --
2704 l_param_code := l_param_code || l_argument_name(l_loop) ||
2705 ' in ' || l_datatype_str || c_new_line;
2706 --
2707 -- Remember details of the parameters so the hook call parameters
2708 -- can be validated later. i.e. Set the out parameter tables.
2709 --
2710 p_hook_parameter_names(l_loop) := l_argument_name(l_loop);
2711 p_hook_parameter_datatypes(l_loop) := l_datatype(l_loop);
2712 --
2713 -- Prepare loop variables for the next iteration
2714 --
2715 l_pre_overload := l_overload(l_loop);
2716 l_loop := l_loop + 1;
2717 else
2718 --
2719 -- The parameter has failed a validation check. Exit out of the
2720 -- loop as there is no point is validating the rest of the
2721 -- parameters.
2722 --
2723 exit;
2724 end if;
2725 end loop step_through_param_list;
2726 hr_utility.set_location(l_proc, 140);
2727 exception
2728 when no_data_found then
2729 -- Trap the PL/SQL no_data_found exception. Know we have already
2730 -- read the details of the last parameter from the tables.
2731 if l_loop > 0 then
2732 -- There must have been at least one parameter in the list. End the
2733 -- parameter list with a closing bracket. The bracket should not be
2734 -- included when there are zero parameters.
2735 l_param_code := l_param_code || ')';
2736 end if;
2737 end;
2738 end if;
2739 end if;
2740 hr_utility.set_location(l_proc, 150);
2741 --
2742 -- Set the out parameters for this procedure and update
2743 -- the error details in the hr_api_hooks table.
2744 --
2745 if not l_error then
2746 --
2747 -- Remember how many parameters exist to the hook package procedure
2748 p_number_of_parameters := l_loop - 1;
2749 --
2750 -- Indicate that no hook package procedure or parameter list errors
2751 -- where found.
2752 p_param_list_error := false;
2753 --
2754 -- Ensure any error details placed in the HR_API_HOOKS table, from previous
2755 -- generates, are cleared. When the make_find_legislation procedure is
2756 -- executed, sometimes an error will be written to HR_API_HOOKS. In certain
2757 -- cases the 'clearing' update done here will be a waste of time. It is not
2758 -- possible to detect here when make_find_legislation will find an error.
2762 -- Change the following update statement to
2759 -- Also make_find_legislation will not be called if there are any hook
2760 -- calls. So the 'clearing' update is always done here.
2761 --
2763 -- call the row handler, when it is available.
2764 --
2765 update hr_api_hooks
2766 set encoded_error = null
2767 where api_hook_id = p_api_hook_id;
2768 hr_utility.set_location(l_proc, 160);
2769 else
2770 --
2771 -- The parameter list code for this hook procedure could not be generated
2772 -- due to an error. Write details of the error to the HR_API_HOOKS table.
2773 -- Also deliberately create some invalid code in the hook package body
2774 -- to prevent the package from compiling. This will force somebody to
2775 -- investigate the problem.
2776 --
2777 p_number_of_parameters := null;
2778 p_param_list_error := true;
2779 --
2780 -- Create comment text in the package body source code
2781 --
2782 error_expected;
2783 l_param_code := '-- The parameter list for this hook procedure could ' ||
2784 'not be created due to' || c_new_line ||
2785 '-- an error. Details of the error, in FND encoded ' ||
2786 'format, can be obtained' || c_new_line ||
2787 '-- with the following sql statement:' || c_new_line ||
2788 '-- select h.encoded_error' || c_new_line ||
2789 '-- from hr_api_hooks h' || c_new_line ||
2790 '-- where h.api_hook_id = ' || to_char(p_api_hook_id) ||
2791 ';' || c_new_line ||
2792 '-- The following invalid code has been deliberately ' ||
2793 'created to force' || c_new_line ||
2794 '-- investigation and resolution of this problem.' ||
2795 c_new_line || 'INVALID_SEE_COMMENT_IN_SOURCE;' ||
2796 c_new_line;
2797 hr_utility.set_location(l_proc, 170);
2798 --
2799 -- Write details of the error to the HR_API_HOOKS table
2800 --
2801 l_encoded_err_text := fnd_message.get_encoded;
2802 --
2803 -- Change the following update statement to
2804 -- call the row handler, when it is available.
2805 --
2806 update hr_api_hooks
2807 set encoded_error = l_encoded_err_text
2808 where api_hook_id = p_api_hook_id;
2809 hr_utility.set_location(l_proc, 180);
2810 end if;
2811 --
2812 -- Add the parameter list or error comment text
2813 -- to the rest of the hook package source code
2814 --
2815 add_to_source(l_param_code);
2816 hr_utility.set_location(' Leaving:'|| l_proc, 190);
2817 end make_parameter_list;
2818 --
2819 -- ----------------------------------------------------------------------------
2820 -- |-------------------------< make_hook_procedure >--------------------------|
2821 -- ----------------------------------------------------------------------------
2822 -- {Start Of Comments}
2823 --
2824 -- Description:
2825 -- Makes the source code for one hook package procedure.
2826 --
2827 -- Prerequisites:
2828 -- The start of the package body code has already been created and added
2829 -- to the source store. The hook_procedure is known to exist in the
2830 -- hook_package.
2831 --
2832 -- In Parameters:
2833 -- Name Reqd Type Description
2834 -- p_hook_package Yes varchar2 Name of the hook package.
2835 -- p_hook_procedure Yes varchar2 Name of the hook procedure
2836 -- within the hook package.
2837 -- p_api_hook_type Yes varchar2 Type of Hook.
2838 -- p_api_hook_id Yes number ID of the hook details from
2839 -- the HR_API_HOOKS table.
2840 -- p_legislation_package Yes varchar2 The legislation_package as
2841 -- specified in the HR_API_HOOKS
2842 -- table.
2843 -- p_legislation_function Yes varchar2 The legislation_function as
2844 -- specified in the HR_API_HOOKS
2845 -- table.
2846 -- p_module_name Yes varchar2 API Module name from the
2847 -- HR_API_MODULES table.
2848 -- p_data_within_business_group Yes varchar2 Indicates if the data for
2849 -- this module is held within
2850 -- the context of a
2851 -- business_group_id. From the
2852 -- HR_API_MODULES table.
2853 --
2854 -- Post Success:
2855 -- Creates source code for the hook procedure in the source store.
2856 --
2857 -- Post Failure:
2858 -- Most application and some Oracle errors are written to the corresponding
2859 -- rows in the HR_API_HOOKS or HR_API_HOOK_CALLS tables. Some application
2860 -- or Oracle errors are raised from this procedure as PL/SQL exceptions.
2861 --
2862 -- Access Status:
2863 -- Internal Development Use Only.
2864 --
2865 -- {End Of Comments}
2866 --
2867 procedure make_hook_procedure
2868 (p_hook_package in varchar2
2869 ,p_hook_procedure in varchar2
2870 ,p_api_hook_type in varchar2
2871 ,p_api_hook_id in number
2872 ,p_legislation_package in varchar2
2873 ,p_legislation_function in varchar2
2874 ,p_module_name in varchar2
2875 ,p_data_within_business_group in varchar2
2876 ) is
2877 --
2878 -- Local variables
2879 --
2880 -- Indicates if there is an error with the hook package procedure header
2884 -- If l_param_list_error is false contains the number of parameters to the
2881 -- or one of it's parameters.
2882 l_param_list_error boolean;
2883 --
2885 -- hook procedure. If l_param_list_error is true then the value is undefined
2886 -- and should not be used.
2887 l_number_of_parameters number;
2888 --
2889 -- If l_param_list_error is false contains the names of all the parameters
2890 -- on the hook procedure. If l_param_list_error is true the values are
2891 -- undefined and should not be used.
2892 l_hook_parameter_names tbl_parameter_name;
2893 --
2894 -- If l_param_list_error is false contains the datatype codes for the hook
2895 -- procedure parameters. If l_param_list_error is true the values are
2896 -- undefined and should not be used.
2897 l_hook_parameter_datatypes tbl_parameter_datatype;
2898 --
2899 l_proc varchar2(72) := g_package||'make_hook_procedure';
2900 begin
2901 hr_utility.set_location('Entering:'|| l_proc, 10);
2902 --
2903 -- Start of the procedure code
2904 --
2905 add_to_source('procedure ' || p_hook_procedure || c_new_line);
2906 --
2907 -- Make the hook procedure parameter list
2908 --
2909 make_parameter_list
2910 (p_hook_package => p_hook_package
2911 ,p_hook_procedure => p_hook_procedure
2912 ,p_api_hook_id => p_api_hook_id
2913 ,p_number_of_parameters => l_number_of_parameters
2914 ,p_hook_parameter_names => l_hook_parameter_names
2915 ,p_hook_parameter_datatypes => l_hook_parameter_datatypes
2916 ,p_param_list_error => l_param_list_error
2917 );
2918 hr_utility.set_location(l_proc, 20);
2919 --
2920 -- When there are no problems with the hook package procedure header and
2921 -- parameters then build the remainder of the hook procedure source code.
2922 --
2923 if not l_param_list_error then
2924 add_to_source('is' || c_new_line);
2925 --
2926 -- Main procedure code
2927 --
2928 procedure_code
2929 (p_hook_package => p_hook_package
2930 ,p_hook_procedure => p_hook_procedure
2931 ,p_api_hook_type => p_api_hook_type
2932 ,p_api_hook_id => p_api_hook_id
2933 ,p_legislation_package => p_legislation_package
2934 ,p_legislation_function => p_legislation_function
2935 ,p_module_name => p_module_name
2936 ,p_data_within_business_group => p_data_within_business_group
2937 ,p_number_of_parameters => l_number_of_parameters
2938 ,p_hook_parameter_names => l_hook_parameter_names
2939 ,p_hook_parameter_datatypes => l_hook_parameter_datatypes
2940 );
2941 hr_utility.set_location(l_proc, 30);
2942 --
2943 -- End of the procedure code
2944 --
2945 add_to_source('hr_utility.set_location(' || '''' || ' Leaving: ');
2946 add_to_source(p_hook_package || '.' || p_hook_procedure || '''');
2947 add_to_source(', 20);' || c_new_line ||'end ' || p_hook_procedure || ';');
2948 add_to_source(c_new_line);
2949 end if;
2950 --
2951 hr_utility.set_location(' Leaving:'|| l_proc, 40);
2952 end make_hook_procedure;
2953 --
2954 -- ----------------------------------------------------------------------------
2955 -- |--------------------------< create_procedures >---------------------------|
2956 -- ----------------------------------------------------------------------------
2957 -- {Start Of Comments}
2958 --
2959 -- Description:
2960 -- Creates all the procedures in one package body.
2961 --
2962 -- Prerequisites:
2963 -- The start of the package body code has already been created and added
2964 -- to the source store.
2965 --
2966 -- In Parameters:
2967 -- Name Reqd Type Description
2968 -- p_hook_package Yes varchar2 Name of the hook package.
2969 --
2970 -- Post Success:
2971 -- Creates all the procedure bodies for the hook package. Only creates
2972 -- those procedures which have been listed in the the HR_API_HOOKS table.
2973 --
2974 -- Post Failure:
2975 -- Most application and some Oracle errors are written to the corresponding
2976 -- rows in the HR_API_HOOKS or HR_API_HOOK_CALLS tables. Some application
2977 -- or Oracle errors are raised from this procedure as PL/SQL exceptions.
2978 --
2979 -- Access Status:
2980 -- Internal Development Use Only.
2981 --
2982 -- {End Of Comments}
2983 --
2984 procedure create_procedures
2985 (p_hook_package in varchar2
2986 ) is
2987 --
2988 -- Cursor to select all the procedure names in a given hook package
2989 --
2990 cursor csr_procs is
2991 select ahk.hook_procedure
2992 , ahk.api_hook_type
2993 , ahk.api_hook_id
2994 , ahk.legislation_package
2995 , ahk.legislation_function
2996 , amd.module_name
2997 , amd.data_within_business_group
2998 from hr_api_modules amd
2999 , hr_api_hooks ahk
3000 where amd.api_module_id = ahk.api_module_id
3001 and ahk.hook_package = p_hook_package;
3002 --
3003 l_proc varchar2(72) := g_package||'create_procedures';
3004 begin
3005 hr_utility.set_location('Entering:'|| l_proc, 10);
3006 --
3007 -- For each procedure listed in the current hook package make
3008 -- the hook procedure call.
3009 --
3010 for l_proc in csr_procs loop
3011 make_hook_procedure
3012 (p_hook_package => p_hook_package
3013 ,p_hook_procedure => l_proc.hook_procedure
3014 ,p_api_hook_type => l_proc.api_hook_type
3015 ,p_api_hook_id => l_proc.api_hook_id
3016 ,p_legislation_package => l_proc.legislation_package
3020 );
3017 ,p_legislation_function => l_proc.legislation_function
3018 ,p_module_name => l_proc.module_name
3019 ,p_data_within_business_group => l_proc.data_within_business_group
3021 end loop;
3022 --
3023 hr_utility.set_location(' Leaving:'|| l_proc, 20);
3024 end create_procedures;
3025 --
3026 -- ----------------------------------------------------------------------------
3027 -- |-------------------------< create_header_line >---------------------------|
3028 -- ----------------------------------------------------------------------------
3029 -- {Start Of Comments}
3030 --
3031 -- Description:
3032 -- Creates the first line after the create or replace package statement
3033 -- with the Header information.
3034 --
3035 -- Prerequisites:
3036 -- The create or replace statement has already been added to the source
3037 -- store.
3038 --
3039 -- In Parameters:
3040 -- Name Reqd Type Description
3041 -- p_hook_package Yes varchar2 Name of the hook package.
3042 --
3043 -- Post Success:
3044 -- The Header line information has been added to the source code source.
3045 --
3046 -- Post Failure:
3047 -- If the text could not been found then an error is not raised here.
3048 -- When the hook package header does not exist in the database the
3049 -- 'make_parameter_list' procedure will raise an error and save the details
3050 -- to the HR_API_HOOKS table. The 'make_parameter_list' procedure is called
3051 -- as part of the 'create_procedures' work.
3052 --
3053 -- Access Status:
3054 -- Internal Development Use Only.
3055 --
3056 -- {End Of Comments}
3057 --
3058 procedure create_header_line
3059 (p_hook_package in varchar2
3060 ) is
3061 --
3062 -- Cursor to obtain the arcs header line from the hook package header
3063 --
3064 cursor csr_second_line (p_pkg_name VARCHAR2, p_pkg_type VARCHAR2) is
3065 select text
3066 from user_source
3067 where name = p_pkg_name
3068 and type = p_pkg_type
3069 and line = 2;
3070 --
3071 l_source_line varchar2(2000); -- Source header line which was
3072 -- derived from the user_source.
3073 l_header_line varchar2(2000); -- Header line string which is being
3074 -- built for inclusion into the
3075 -- user hook package body code.
3076 l_hook_pkg_type varchar2(30); -- The third and second last letters
3077 -- of the hook package name.
3078 l_pkg_find_name varchar2(30); -- Name of the package to search
3079 -- for the header line.
3080 l_pkg_find_type varchar2(30); -- Type of package code to find,
3081 -- either the package header or
3082 -- body.
3083 l_head_pos number; -- Position of dollar Header in the
3084 -- l_source_line.
3085 l_author_str number; -- Position of the arcs author name
3086 -- in l_source_line.
3087 l_author_end number; -- The last character position of the
3088 -- arcs author name in l_source_line.
3089 l_dplace_str number; -- Position of the decimal place in
3090 -- the revision number inside
3091 -- l_source_line.
3092 l_dplace_end number; -- The last character position of the
3093 -- revision number inside
3094 -- l_source_line.
3095 l_pkg_find_found boolean; -- Indicates if using the
3096 -- csr_second_line cursor found a
3097 -- row.
3098 l_non_standard_hook_pkg boolean; -- Indicates if a non-standard hook
3099 -- package name has been defined.
3100 -- i.e. The third and second last
3101 -- letters do not equal 'RK' or 'BK'.
3102 l_proc varchar2(72) := g_package||'create_header_line';
3103 begin
3104 hr_utility.set_location('Entering:'|| l_proc, 10);
3105 --
3106 -- Primary Method
3107 -- --------------
3108 -- Where possible the filename and revision number for the hook package
3109 -- body should be derived from a package which is actually shipped in
3110 -- generated file name.
3111 -- i.e. Look at the third and second last letters of the hook package name.
3112 -- Where coding standards have been followed this should be like 'RK' or
3113 -- 'BK'.
3114 -- If 'RK' then this is a row handler hook package, so derive the
3115 -- filename and revision number from the corresponding '_SHD' package body.
3116 -- If 'BK' then this is a business process hook package, so dervie the
3117 -- filename and revision number from the corresponding '_API' package body.
3118 --
3119 -- Alternative Method
3120 -- ------------------
3121 -- If the coding standards have not been followed then either the third
3122 -- and second last letters of the hook package body name will not match
3123 -- 'RK'/'BK' or the '_SHD'/'_API' package body does not exist.
3124 -- In either of these cases use the alternative derivation method.
3125 -- Attempt to derive the dollar header line from the hook package header.
3126 -- Change 'pkh' to 'pkb' and change the revision number to end with '.0'.
3127 --
3128 -- Extract the third and second last letters of the package name
3129 l_hook_pkg_type := upper(substr(substr(p_hook_package, -3), 1, 2));
3130 l_non_standard_hook_pkg := false;
3134 -- This is a row handler user hook package so attempt to find
3131 l_pkg_find_found := false;
3132 --
3133 if l_hook_pkg_type = 'RK' then
3135 -- details of the corresponding '_SHD' package body.
3136 l_pkg_find_name :=
3137 upper(substr(p_hook_package, 1, length(p_hook_package) -3) || 'SHD');
3138 l_pkg_find_type := 'PACKAGE BODY';
3139 --
3140 elsif l_hook_pkg_type = 'BK' then
3141 -- This is a business process user hook package so attempt to
3142 -- find details of the corresponding '_API' package body.
3143 l_pkg_find_name :=
3144 upper(substr(p_hook_package, 1, length(p_hook_package) -3) || 'API');
3145 l_pkg_find_type := 'PACKAGE BODY';
3146 --
3147 else
3148 -- Non-standard hook package name has been defined.
3149 -- Set flag so alternative strategy will be used.
3150 l_non_standard_hook_pkg := true;
3151 --
3152 end if;
3153 --
3154 -- Attempt to find the corresponding package body header line information.
3155 --
3156 if not l_non_standard_hook_pkg then
3157 open csr_second_line(l_pkg_find_name, l_pkg_find_type);
3158 fetch csr_second_line into l_source_line;
3159 l_pkg_find_found := csr_second_line%found;
3160 close csr_second_line;
3161 end if;
3162 --
3163 if l_non_standard_hook_pkg or (not l_pkg_find_found) then
3164 --
3165 -- If a non-standard hook package name exists or the package
3166 -- name for the primary method could not be found then attempt
3167 -- to obtain the hook package header line information.
3168 --
3169 l_pkg_find_name := upper(p_hook_package);
3170 l_pkg_find_type := 'PACKAGE';
3171 open csr_second_line(l_pkg_find_name, l_pkg_find_type);
3172 fetch csr_second_line into l_source_line;
3173 l_pkg_find_found := csr_second_line%found;
3174 close csr_second_line;
3175 end if;
3176 --
3177 -- If the text could not been found then do not raise an error here.
3178 -- When the hook package header does not exist in the database the
3179 -- 'make_parameter_list' procedure will raise an error and save the
3180 -- details to the HR_API_HOOKS table. The 'make_parameter_list'
3181 -- procedure is called as part of the 'create_procedures' work.
3182 --
3183 if l_pkg_find_found then
3184 --
3185 -- Build the second line of the hook package body, changing the arcs
3186 -- author name to 'generated'. If the alternative strategy is being
3187 -- used also change 'pkh' to 'pkb' and alter the revision number to
3188 -- end with '.0'.
3189 --
3190 -- Find the character position of the dollar header string
3191 l_head_pos := instr(l_source_line, '$Header', 1, 1);
3192 --
3193 -- Only continue with the string replacement if the second line of the hook
3194 -- package body contains a dollar header comment. If the dollar header
3195 -- does not exist then just assume the coder as forgotten to include it,
3196 -- don't create a dollar header line in the hook package body, but
3197 -- continue with the rest of the body generation.
3198 --
3199 if l_head_pos <> 0 then
3200 --
3201 -- Find the first character position of the author's name. Actually
3202 -- looking for the space immediately before the name.
3203 l_author_str := instr(l_source_line, ' ', l_head_pos, 5);
3204 --
3205 -- Only continue with the string replacement if the space immediately
3206 -- before the name was found. If the space cound not be found it could
3207 -- indicate that the hook package header has not been placed in arcs yet.
3208 -- i.e. This comment line has not been fully constructed yet.
3209 --
3210 if l_author_str <> 0 then
3211 --
3212 -- Find the last character position of the author's name. Actually
3213 -- looking for the space immediately after the name.
3214 l_author_end := instr(l_source_line, ' ', l_head_pos, 6);
3215 --
3216 -- Only continue with the string replacement if the space immediately
3217 -- after the name could be found.
3218 --
3219 if l_author_end <> 0 then
3220 --
3221 if l_pkg_find_type = 'PACKAGE' then
3222 --
3223 -- When the alternative method is being used also need to locate
3224 -- starting and ending position of the revision decimal place
3225 -- digits so they can be replaced with '0'.
3226 --
3227 -- Find the character position of the decimal place which is
3228 -- inside the revision number.
3229 l_dplace_str := instr(l_source_line, '.', l_head_pos, 2);
3230 --
3231 -- Only continue with the string replacement if the decimal place
3232 -- could be found.
3233 --
3234 if l_dplace_str <> 0 then
3235 --
3236 -- Find the last character position of the place number.
3237 -- Actually looking for the space immediately after the
3238 -- number.
3239 l_dplace_end := instr(l_source_line, ' ', l_head_pos, 3);
3240 --
3241 -- Only continue with the string replacement if the space
3242 -- immediately after the revision number could be found.
3243 --
3244 if l_dplace_end <> 0 then
3245 --
3246 -- Set the package body line to the same as the package header
3247 -- line, but changing the revision decimal package to .0,
3248 -- the author's name with the string 'generated' and 'pkh'
3249 -- to 'pkb'.
3250 l_header_line := substr(l_source_line, 1, l_dplace_str) ||
3251 '0' ||
3252 substr(l_source_line, l_dplace_end,
3253 l_author_str - l_dplace_end) ||
3254 ' generated' ||
3258 -- Add modified dollar header line to the package body source
3255 substr(l_source_line, l_author_end,
3256 length(l_source_line) - l_author_end);
3257 l_header_line := replace(l_header_line, 'pkh', 'pkb');
3259 add_to_source(l_header_line || c_new_line);
3260 end if;
3261 end if;
3262 else -- l_pkg_find_type = 'PACKAGE BODY'
3263 -- Primary method is being used so the revision number in the
3264 -- found package body Header line can be used directly. Only
3265 -- need to replace the author's name with the string 'generated'.
3266 l_header_line := substr(l_source_line, 1, l_author_str) ||
3267 'generated' ||
3268 substr(l_source_line, l_author_end,
3269 length(l_source_line) - l_author_end);
3270 -- Add modified dollar header line to the package body source
3271 add_to_source(l_header_line || c_new_line);
3272 end if;
3273 end if;
3274 end if;
3275 end if;
3276 end if;
3277 --
3278 hr_utility.set_location(' Leaving:'||l_proc, 140);
3279 end create_header_line;
3280 --
3281 -- ----------------------------------------------------------------------------
3282 -- |-------------------------< create_package_body >--------------------------|
3283 -- ----------------------------------------------------------------------------
3284 --
3285 procedure create_package_body
3286 (p_hook_package in varchar2
3287 ) is
3288 --
3289 l_proc varchar2(72) := g_package||'create_package_body';
3290 begin
3291 hr_utility.set_location('Entering:'|| l_proc, 10);
3292 clear_source;
3293 --
3294 -- Build code for the start of the hook package
3295 --
3296 add_to_source('create or replace package body ' || p_hook_package);
3297 add_to_source(' as' || c_new_line);
3298 --
3299 -- Build the dollar Header text for the hook package body.
3300 --
3301 create_header_line(p_hook_package);
3302 --
3303 -- Build the comments at the start of the package body
3304 --
3305 add_to_source('-- Code generated by the Oracle HRMS API Hook Pre-processor');
3306 add_to_source(c_new_line);
3307 -- add_to_source('-- Created on ' || to_char(sysdate, 'YY/MM/DD HH24:MI:SS'));
3308 -- add_to_source(' (YY/MM/DD HH:MM:SS)' || c_new_line);
3309 --
3310 -- Fix for bug 3315199
3311 add_to_source('-- Created on ' || fnd_date.date_to_canonical(sysdate));
3312 add_to_source(' (' || fnd_date.canonical_DT_mask || ')' || c_new_line);
3313 --
3314 -- Create all the procedures in this hook package
3315 --
3316 create_procedures(p_hook_package);
3317 --
3318 -- Build code for the end of the hook package
3319 --
3320 add_to_source('end ' || p_hook_package || ';' || c_new_line);
3321 --
3322 -- Execute the create or replace package body
3323 -- source code which has been built up.
3324 --
3325 execute_source;
3326 --
3327 hr_utility.set_location(' Leaving:'||l_proc, 60);
3328 end create_package_body;
3329
3330 end hr_api_user_hooks;
[Home] [Help]