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