1 Package Body hr_api_user_hooks_utility as
2 /* $Header: hrusrutl.pkb 115.6 2002/12/05 15:53:07 apholt ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_api_user_hooks_utility.';
7 g_number number default 0;
8 g_error_detected boolean default false;
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------------< report_line >-----------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- Inserts one row to the HR_API_USER_HOOK_REPORTS table.
17 --
18 -- Prerequisites:
19 -- None.
20 --
21 -- In Parameters:
22 -- Name Reqd Type Description
23 -- p_text Yes varchar2 Text to insert
24 --
25 -- Post success:
26 -- One row is inserted into the HR_API_USER_HOOK_REPORTS table. No commit
27 -- is issued.
28 --
29 -- Post Failure:
30 -- An Oracle error is raised. No application specific errors as raised from
31 -- this procedure.
32 --
33 -- Access Status:
34 -- Internal Development Use Only.
35 --
36 -- {End Of Comments}
37 --
38 procedure report_line
39 (p_text in varchar2
40 ) is
41 l_proc varchar2(72) := g_package||'report_line';
42 begin
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Insert into reports table
46 --
47 insert into hr_api_user_hook_reports
48 (session_id,
49 line,
50 text)
51 values
52 (userenv('SESSIONID'),
53 g_number,
54 p_text);
55 --
56 g_number := g_number + 1;
57 --
58 hr_utility.set_location(' Leaving:'|| l_proc, 20);
59 end report_line;
60 --
61 -- ----------------------------------------------------------------------------
62 -- |------------------------------< report_title >----------------------------|
63 -- ----------------------------------------------------------------------------
64 -- {Start Of Comments}
65 --
66 -- Description:
67 -- Creates a translated report title in the HR_API_USER_HOOK_REPORTS table.
68 --
69 -- Prerequisites:
70 -- The message name must exist in the message dictionary.
71 --
72 -- In Parameters:
73 -- Name Reqd Type Description
74 -- p_title_message_name Yes varchar2 Name of the HR message, in
75 -- the message dictionary,
76 -- which contains the title for
77 -- this report.
78 --
79 -- Post success:
80 -- The title is created in the HR_API_USER_HOOK_REPORTS table.
81 --
82 -- Post Failure:
83 -- An exception error is raised and no rows are created in the
84 -- HR_API_USER_HOOK_REPORTS table.
85 --
86 -- Access Status:
87 -- Internal Development Use Only.
88 --
89 -- {End Of Comments}
90 --
91 procedure report_title
92 (p_title_message_name in varchar2
93 ) is
94 --
95 -- Cursor to see if a report as already
96 -- been started for the current session
97 --
98 cursor csr_report_started is
99 select count(*)
100 from hr_api_user_hook_reports
101 where session_id = userenv('SESSIONID');
102 --
103 -- Local variables
104 --
105 l_title varchar2(2000);
106 l_created_on varchar2(2000);
107 l_report_lines varchar2(30);
108 l_proc varchar2(72) := g_package||'report_title';
109 begin
110 hr_utility.set_location('Entering:'|| l_proc, 10);
111 --
112 -- Output the report title if a report has not
113 -- already started for the current database session
114 --
115 open csr_report_started;
116 fetch csr_report_started into l_report_lines;
117 close csr_report_started;
118 --
119 if l_report_lines = 0 then
120 --
121 -- Obtain translated report title text from the messages dictionary
122 --
123 fnd_message.set_name('PER', p_title_message_name);
124 l_title := fnd_message.get;
125 fnd_message.set_name('PER', 'HR_51988_AHR_CREATED_ON');
126 l_created_on := fnd_message.get;
127 --
128 -- Output the report header, the translated version of:
129 -- Translated Report Title
130 -- =======================
131 -- Created on YYYY/MM/DD HH:MM:SS (YYYY/MM/DD HH:MM:SS)
132 --
133 report_line('');
134 report_line(l_title);
135 report_line(rpad('=', length(l_title), '='));
136 report_line(l_created_on || ' '
137 || to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')
138 || ' (YYYY/MM/DD HH:MM:SS)');
139 --
140 -- Reset the global variable which indicates at least one
141 -- generation error has been detected with the current set
142 -- of API modules
143 --
144 g_error_detected := false;
145 --
146 end if;
147 hr_utility.set_location(' Leaving:'|| l_proc, 20);
148 end report_title;
149 --
150 -- ----------------------------------------------------------------------------
151 -- |------------------------< get_translated_prompts >------------------------|
152 -- ----------------------------------------------------------------------------
153 -- {Start Of Comments}
154 --
155 -- Description:
156 -- Obtains the translated prompts required for the report outout from the
157 -- message dictionary.
158 --
159 -- This procedure has been designed to be called from the
160 -- write_all_errors_report and write_one_errors_report procedures in
161 -- this package.
162 --
163 -- Prerequisites:
164 -- The message names must exist in the message dictionary.
165 --
166 -- In Parameters:
167 -- None.
168 --
169 -- Post success:
170 -- All the output parameters are set with the translated text for the
171 -- corresponding text prompt.
172 --
173 -- Post Failure:
174 -- An exception error is raised.
175 --
176 -- Access Status:
177 -- Internal Development Use Only.
178 --
179 -- {End Of Comments}
180 --
181 procedure get_translated_prompts
182 (p_module_name_text out nocopy varchar2
183 ,p_module_type_text out nocopy varchar2
184 ,p_hook_text out nocopy varchar2
185 ,p_hook_pkg_text out nocopy varchar2
186 ,p_success_text out nocopy varchar2
187 ) is
188 l_title varchar2(2000);
189 l_created_on varchar2(2000);
190 l_proc varchar2(72) := g_package||'get_translated_prompts';
191 begin
192 hr_utility.set_location('Entering:'|| l_proc, 10);
193 --
194 -- Obtain translated text from the messages dictionary
195 --
196 fnd_message.set_name('PER', 'HR_51989_AHR_MOD_NAME');
197 p_module_name_text := fnd_message.get;
198 fnd_message.set_name('PER', 'HR_51990_AHR_MOD_TYPE');
199 p_module_type_text := fnd_message.get;
200 fnd_message.set_name('PER', 'HR_51991_AHR_HOOK');
201 p_hook_text := fnd_message.get;
202 fnd_message.set_name('PER', 'HR_51992_AHR_HOOK_PKG');
203 p_hook_pkg_text := fnd_message.get;
204 fnd_message.set_name('PER', 'HR_52545_AHR_SUCCESS');
205 p_success_text := fnd_message.get;
206 --
207 hr_utility.set_location(' Leaving:'|| l_proc, 20);
208 end get_translated_prompts;
209 --
210 -- ----------------------------------------------------------------------------
211 -- |------------------------< write_hook_parameter_list >---------------------|
212 -- ----------------------------------------------------------------------------
213 --
214 procedure write_hook_parameter_list is
215 --
216 -- Package does not exist in the database
217 --
218 Package_Not_Exists exception;
219 Pragma Exception_Init(Package_Not_Exists, -6564);
220 --
221 -- Procedure does not exist in the package
222 --
223 Proc_Not_In_Package exception;
224 Pragma Exception_Init(Proc_Not_In_Package, -20001);
225 --
226 -- Object is remote
227 --
228 Remote_Object exception;
229 Pragma Exception_Init(Remote_Object, -20002);
230 --
231 -- Package is invalid
232 --
233 Invalid_Package exception;
234 Pragma Exception_Init(Invalid_Package, -20003);
235 --
236 -- Invalid Object Name
237 --
238 Invalid_Object_Name exception;
239 Pragma Exception_Init(Invalid_Object_Name, -20004);
240 --
241 -- Cursor to return the list of all RH and BP API modules
242 --
243 cursor csr_modules is
244 select amd.api_module_id
245 , hlk.meaning
246 , amd.module_name
247 from hr_lookups hlk
248 , hr_api_modules amd
249 where hlk.lookup_type = 'API_MODULE_TYPE'
250 and hlk.lookup_code = amd.api_module_type
251 and amd.api_module_type in ('BP', 'RH')
252 order by hlk.meaning, amd.module_name;
253 --
254 -- Cursor to return the list of hooks for one API module
255 --
256 cursor csr_hooks (p_module_id number) is
257 select hlk.meaning
258 , ahk.hook_package
259 , ahk.hook_procedure
260 from hr_lookups hlk
261 , hr_api_hooks ahk
262 where hlk.lookup_type = 'API_HOOK_TYPE'
263 and hlk.lookup_code = ahk.api_hook_type
264 and ahk.api_module_id = p_module_id
265 order by hlk.meaning;
266 --
267 -- Cursor to obtain the names of all the API hook packages
268 --
269 cursor cur_hooks is
270 select distinct hook_package
271 from hr_api_hooks;
272 --
273 -- Local variables to catch the values returned from
274 -- hr_general.describe_procedure
275 --
276 l_overload dbms_describe.number_table;
277 l_position dbms_describe.number_table;
278 l_level dbms_describe.number_table;
279 l_argument_name dbms_describe.varchar2_table;
280 l_datatype dbms_describe.number_table;
281 l_default_value dbms_describe.number_table;
282 l_in_out dbms_describe.number_table;
283 l_length dbms_describe.number_table;
284 l_precision dbms_describe.number_table;
285 l_scale dbms_describe.number_table;
286 l_radix dbms_describe.number_table;
287 l_spare dbms_describe.number_table;
288 --
289 -- Other Local variables
290 --
291 l_param_details varchar2(80); -- Used to construct the user descriptions
292 -- for the parameters.
293 l_loop number; -- Loop counter.
294 l_datatype_str varchar2(20); -- String equivalent of the parameter
295 -- datatype.
296 l_mod_name_text varchar2(2000); -- 'Module Name' translated string.
297 l_mod_type_text varchar2(2000); -- 'Module Type' translated string.
298 l_hook_text varchar2(2000); -- 'Hook' translated string.
299 l_no_dtype_text varchar2(2000); -- 'Datatype not recognised' translated str.
300 l_proc varchar2(72) := g_package||'write_hook_parameter_list';
301 c_new_line constant varchar2(1) default '
302 ';
303 begin
304 hr_utility.set_location('Entering:'|| l_proc, 10);
305 --
306 -- Obtain translated text from the message dictionary
307 --
308 fnd_message.set_name('PER', 'HR_51989_AHR_MOD_NAME');
309 l_mod_name_text := fnd_message.get;
310 fnd_message.set_name('PER', 'HR_51990_AHR_MOD_TYPE');
311 l_mod_type_text := fnd_message.get;
312 fnd_message.set_name('PER', 'HR_51991_AHR_HOOK');
313 l_hook_text := fnd_message.get;
314 fnd_message.set_name('PER', 'HR_52038_AHR_DTYPE_NOT_REC');
315 l_no_dtype_text := fnd_message.get;
316 hr_utility.set_location(l_proc, 20);
317 --
318 -- Output report header
319 --
320 report_title
321 (p_title_message_name => 'HR_51985_AHR_PARM_LIST'
322 );
323 hr_utility.set_location(l_proc, 30);
324 --
325 -- Loop for all API Modules
326 --
327 for l_module in csr_modules loop
328 report_line('');
329 report_line(l_mod_name_text || ': ' || l_module.module_name);
330 report_line(l_mod_type_text || ': ' || l_module.meaning);
331 --
332 -- Loop for all hooks in a particular API module
333 --
334 for l_hook in csr_hooks(l_module.api_module_id) loop
335 --
336 -- Output details of the hook
337 --
338 report_line('');
339 report_line(' ' || l_hook_text || ': ' || l_hook.meaning);
340 report_line(' ' || rpad('-', 2 + length(l_hook_text) +
341 length(l_hook.meaning), '-'));
342 --
343 -- Call an RDMS procedure to obtain the list of parameters to the
344 -- hook package procedure. A separate begin ... end block has been
345 -- specified so that errors raised by
346 -- hr_general.describe_procedure can be trapped and handled
347 -- locally.
348 --
349 begin
350 hr_general.describe_procedure
351 (object_name => l_hook.hook_package || '.' ||
352 l_hook.hook_procedure
353 ,reserved1 => null
354 ,reserved2 => null
355 ,overload => l_overload
356 ,position => l_position
357 ,level => l_level
358 ,argument_name => l_argument_name
359 ,datatype => l_datatype
360 ,default_value => l_default_value
361 ,in_out => l_in_out
362 ,length => l_length
363 ,precision => l_precision
364 ,scale => l_scale
365 ,radix => l_radix
366 ,spare => l_spare
367 );
368 --
369 -- Loop through the values which have been returned.
370 --
371 begin
372 --
373 -- There is separate PL/SQL block for reading from the PL/SQL
374 -- tables. We do not know how many parameter exist. So we have to
375 -- keep reading from the tables until PL/SQL finds a row when has
376 -- not been initialised and raises a NO_DATA_FOUND exception.
377 --
378 l_loop := 1;
379 <<step_through_param_list>>
380 loop
381 --
382 -- Work out the string name of the parameter datatype code
383 --
384 if l_datatype(l_loop) = 1 then
385 l_datatype_str := 'VARCHAR2';
386 elsif l_datatype(l_loop) = 2 then
387 l_datatype_str := 'NUMBER';
388 elsif l_datatype(l_loop) = 12 then
389 l_datatype_str := 'DATE';
390 elsif l_datatype(l_loop) = 252 then
391 l_datatype_str := 'BOOLEAN';
392 elsif l_datatype(l_loop) = 8 then
393 l_datatype_str := 'LONG';
394 else
395 l_datatype_str := l_no_dtype_text;
396 end if;
397 --
398 -- Construct parameter details to output
399 --
400 l_param_details := ' ' || rpad(l_argument_name(l_loop), 31) ||
401 l_datatype_str;
402 --
403 report_line(l_param_details);
404 --
405 l_loop := l_loop + 1;
406 end loop step_through_param_list;
407 exception
408 when no_data_found then
409 -- Trap the PL/SQL no_data_found exception. Know we have already
410 -- read the details of the last parameter from the tables.
411 null;
412 end;
413 exception
414 -- Trap errors raised by hr_general.describe_procedure
415 when Package_Not_Exists then
416 -- Error: The hook package header source code cannot be found in the
417 -- database. Either the package header has not been loaded into the
418 -- database or the hook package name specified in the HR_API_HOOKS
419 -- table is incorrect. This API module will not execute until this
420 -- problem has been resolved.
421 hr_utility.set_message(800, 'HR_51960_AHK_HK_PKG_NOT_FOUND');
422 when Proc_Not_In_Package then
423 -- Error: The hook procedure does not exist in the hook package.
424 -- This API module will not execute until this problem has been
425 -- resolved.
426 hr_utility.set_message(800, 'HR_51961_AHK_HK_PRO_NO_EXIST');
427 when Remote_Object then
428 -- Error: Remote objects cannot used for API hook package
429 -- procedures. This API module will not execute until this problem
430 -- has been resolved.
431 hr_utility.set_message(800, 'HR_51962_AHK_HK_REMOTE_OBJ');
432 when Invalid_Package then
433 -- Error: The hook package code in the database is invalid.
434 -- This API module will not execute until this problem has been
435 -- resolved.
436 hr_utility.set_message(800, 'HR_51963_AHK_HK_PKG_INVALID');
437 when Invalid_Object_Name then
438 -- Error: An error has occurred while attempting to parse the name
439 -- of the hook package and hook procedure. Check the package and
440 -- procedure names. This API module will not execute until this
441 -- problem has been resolved.
442 hr_utility.set_message(800, 'HR_51964_AHK_HK_PARSE');
443 end;
444 end loop; -- End Hook Loop
445 end loop; -- End Module Loop
446 hr_utility.set_location(' Leaving:'|| l_proc, 40);
447 end write_hook_parameter_list;
448 --
449 -- ----------------------------------------------------------------------------
450 -- |--------------------------< write_module_errors >-------------------------|
451 -- ----------------------------------------------------------------------------
452 -- {Start Of Comments}
453 --
454 -- Description:
455 -- Writes the hook error details, for one API module, to the
456 -- HR_API_USER_HOOK_REPORTS.
457 --
458 -- This procedure has been designed to be called from the
459 -- write_all_errors_report and write_one_errors_report procedures in
460 -- this package.
461 --
462 -- Prerequisites:
463 -- The API module must be defined in the HR_API_MODULES table.
464 --
465 -- In Parameters:
466 -- Name Reqd Type Description
467 -- p_api_module_id Yes Number ID of API module
468 -- p_module_name Yes Varchar2 Module name
469 -- p_module_type_meaning Yes Varchar2 User module type description
470 -- p_module_name_text Yes Varchar2 'Module Name' translated text
471 -- p_module_type_text Yes Varchar2 'Module Type' translated text
472 -- p_hook_text Yes Varchar2 'Hook' translated text
473 -- p_hook_pkg_text Yes Varchar2 'Hook Package' translated
474 -- text
475 -- p_success_text Yes Varchar2 'Successful' translated text
476 -- p_include_success Yes Boolean Indicates if successfully
477 -- processed modules should be
478 -- included in the report out
479 --
480 -- Post success:
481 -- Details of any hook package compilation and application errors will
482 -- be written to the HR_API_USER_HOOK_REPORTS table.
483 --
484 -- Post Failure:
485 -- An error is raised.
486 --
487 -- Access Status:
488 -- Internal Development Use Only.
489 --
490 -- {End Of Comments}
491 --
492 procedure write_module_errors
493 (p_api_module_id in number
494 ,p_module_name in varchar2
495 ,p_module_type_meaning in varchar2
496 ,p_module_name_text in varchar2
497 ,p_module_type_text in varchar2
498 ,p_hook_text in varchar2
499 ,p_hook_pkg_text in varchar2
500 ,p_success_text in varchar2
501 ,p_include_success in boolean
502 ) is
503 --
504 -- Cursor to list hook packages for a given API module.
505 -- Hook packages will only be listed if at least one of the following exists:
506 -- i) a hook package body compilation error
507 -- ii) An application error against the hook definition
508 -- iii) An application error against a hook call definition
509 -- If there are no errors the hook package will not be listed.
510 --
511 cursor csr_hook_pkgs is
512 select distinct ahk.hook_package
513 from hr_api_hooks ahk
514 where ahk.api_module_id = p_api_module_id
515 and ( (ahk.encoded_error is not null)
516 or exists (select null
517 from hr_api_hook_calls ahc
518 where ahc.api_hook_id = ahk.api_hook_id
519 and ahc.enabled_flag = 'Y'
520 and ahc.status = 'I')
521 or exists (select null
522 from user_errors uer
523 where uer.type = 'PACKAGE BODY'
524 and uer.name = ahk.hook_package)
525 )
526 order by ahk.hook_package;
527 --
528 -- Cursor to list hooks for a given API module and a given hook package.
529 -- Hooks will only be listed if at least one of the following exists:
530 -- ii) An application error against the hook definition
531 -- iii) An application error against a hook call definition
532 -- If there are no errors the hook will not be listed.
533 --
534 cursor csr_hooks (p_api_module_id number
535 ,p_hook_package varchar2) is
536 select hlk.meaning
537 , ahk.api_hook_id
538 from hr_lookups hlk
539 , hr_api_hooks ahk
540 where hlk.lookup_type = 'API_HOOK_TYPE'
541 and hlk.lookup_code = ahk.api_hook_type
542 and ahk.api_module_id = p_api_module_id
543 and ahk.hook_package = p_hook_package
544 and ( (ahk.encoded_error is not null)
545 or exists (select null
546 from hr_api_hook_calls ahc
547 where ahc.api_hook_id = ahk.api_hook_id
548 and ahc.enabled_flag = 'Y'
549 and ahc.status = 'I'
550 ) )
551 order by hlk.meaning;
552 --
553 -- Cursor to obtain package body compilation errors for one given package
554 -- i.e. Similar to "show errors package body <HOOK_PACKAGE>"
555 --
556 cursor cur_pkg_err (p_hook_package varchar2) is
557 select substr(to_char(line) || '/' || to_char(position), 1, 8) line_col
558 , text
559 from user_errors
560 where type = 'PACKAGE BODY'
561 and name = p_hook_package
562 order by sequence;
563 --
564 -- Cursor to obtain hook level application errors for one given hook
565 --
566 cursor cur_ahk_err (p_api_hook_id number) is
567 select encoded_error
568 from hr_api_hooks
569 where api_hook_id = p_api_hook_id
570 and encoded_error is not null;
571 --
572 -- Cursor to obtain hook call level application errors
573 -- for one given hook when the hook call is enabled
574 --
575 cursor cur_ahc_err (p_api_hook_id number) is
576 select encoded_error
577 , call_package
578 , call_procedure
579 from hr_api_hook_calls
580 where api_hook_id = p_api_hook_id
581 and enabled_flag = 'Y'
582 and status = 'I'
583 order by sequence;
584 --
585 -- Local variables
586 --
587 l_mod_name_output boolean default false; -- Indicates if the module name and
588 -- type has already been output.
589 l_encoded_error varchar2(2000); -- Encoded error message from
590 -- HR_API_HOOKS or
591 -- HR_API_HOOK_CALLS.
592 l_read_error varchar2(2000); -- A de-encrypted version of
593 -- l_encoded_error.
594 l_line varchar2(2000); -- Line of text for report.
595 l_err_cur_module boolean default false; -- Indicates if at least one error
596 -- has been detected with the
597 -- current module.
598 l_proc varchar2(72) := g_package||'write_module_errors';
599 begin
600 hr_utility.set_location('Entering:'|| l_proc, 10);
601 --
602 -- Loop for all hook packages in a particular API module
603 --
604 for l_hook_pkgs in csr_hook_pkgs loop
605 --
606 -- Set flag that at least one error has been detected
607 --
608 l_err_cur_module := true;
609 --
610 -- Output details of the module
611 --
612 if not l_mod_name_output then
613 report_line('');
614 report_line(p_module_name_text || ': ' || p_module_name);
615 report_line(p_module_type_text || ': ' || p_module_type_meaning);
616 l_mod_name_output := true;
617 end if;
618 --
619 -- Output details of the hook package
620 --
621 report_line('');
622 report_line(p_hook_pkg_text || ': ' || l_hook_pkgs.hook_package);
623 report_line(rpad('-', 2 + length (p_hook_pkg_text) +
624 length(l_hook_pkgs.hook_package), '-'));
625 report_line('');
626 --
627 -- Output details of any hook package body system errors
628 --
629 for l_pkg_err in cur_pkg_err(l_hook_pkgs.hook_package) loop
630 report_line(l_pkg_err.line_col || ' ' || l_pkg_err.text);
631 end loop;
632 --
633 -- Loop for all hooks in a particular hook package and module
634 --
635 for l_hook in csr_hooks(p_api_module_id, l_hook_pkgs.hook_package) loop
636 --
637 -- Output details of the hook
638 --
639 report_line('');
640 report_line(p_hook_text || ': ' || l_hook.meaning);
641 report_line(rpad('-', 2 + length(p_hook_text) +
642 length(l_hook.meaning), '-'));
643 --
644 -- Output details of any hook level application errors
645 --
646 open cur_ahk_err(l_hook.api_hook_id);
647 fetch cur_ahk_err into l_encoded_error;
648 if cur_ahk_err%found then
649 -- An error has occurred
650 fnd_message.set_encoded(l_encoded_error);
651 l_read_error := fnd_message.get;
652 report_line(l_read_error);
653 end if;
654 close cur_ahk_err;
655 --
656 -- Output details of any hook call level application errors
657 --
658 for l_ahc_err in cur_ahc_err(l_hook.api_hook_id) loop
659 fnd_message.set_encoded(l_ahc_err.encoded_error);
660 l_read_error := fnd_message.get;
661 l_line := '(' || l_ahc_err.call_package || '.' ||
662 l_ahc_err.call_procedure || ') ' ||
663 substr(l_read_error, 1, 1925);
664 report_line(l_line);
665 end loop; -- End hook call level application errors
666 end loop; -- End Hooks in a module and hook package Loop
667 end loop; -- End Hook package loop
668 --
669 -- If at least one error has been detected then update the
670 -- global variable which will indicate to the 'clear_hook_report'
671 -- procedure it needs to raise an error. Otherwise write a
672 -- successful line to the report.
673 --
674 if l_err_cur_module then
675 g_error_detected := true;
676 else
677 if p_include_success then
678 report_line('');
679 report_line(p_module_name || '(' || p_module_type_meaning || ') ' ||
680 p_success_text);
681 end if;
682 end if;
683 hr_utility.set_location(' Leaving:'|| l_proc, 20);
684 end write_module_errors;
685 --
686 -- ----------------------------------------------------------------------------
687 -- |------------------------< write_all_errors_report >-----------------------|
688 -- ----------------------------------------------------------------------------
689 --
690 procedure write_all_errors_report is
691 --
692 -- Cursor to return the list of all RH and BP API modules
693 --
694 cursor csr_modules is
695 select amd.api_module_id
696 , hlk.meaning
697 , amd.module_name
698 from hr_lookups hlk
699 , hr_api_modules amd
700 where hlk.lookup_type = 'API_MODULE_TYPE'
701 and hlk.lookup_code = amd.api_module_type
702 and amd.api_module_type in ('BP', 'RH')
703 order by hlk.meaning, amd.module_name;
704 --
705 l_module_name_text varchar2(2000);
706 l_module_type_text varchar2(2000);
707 l_hook_text varchar2(2000);
708 l_hook_pkg_text varchar2(2000);
709 l_success_text varchar2(2000);
710 l_proc varchar2(72) := g_package||'write_all_errors_report';
711 begin
712 hr_utility.set_location('Entering:'|| l_proc, 10);
713 --
714 -- Output report header
715 --
716 report_title
717 (p_title_message_name => 'HR_51986_AHR_ALL_ERR_TITLE'
718 );
719 hr_utility.set_location(l_proc, 20);
720 --
721 -- Obtain translated text prompts
722 --
723 get_translated_prompts
724 (p_module_name_text => l_module_name_text
725 ,p_module_type_text => l_module_type_text
726 ,p_hook_text => l_hook_text
727 ,p_hook_pkg_text => l_hook_pkg_text
728 ,p_success_text => l_success_text
729 );
730 hr_utility.set_location(l_proc, 30);
731 --
732 -- Loop for all API Modules
733 --
734 for l_module in csr_modules loop
735 write_module_errors
736 (p_api_module_id => l_module.api_module_id
737 ,p_module_name => l_module.module_name
738 ,p_module_type_meaning => l_module.meaning
739 ,p_module_name_text => l_module_name_text
740 ,p_module_type_text => l_module_type_text
741 ,p_hook_text => l_hook_text
742 ,p_hook_pkg_text => l_hook_pkg_text
743 ,p_success_text => l_success_text
744 ,p_include_success => false
745 );
746 end loop; -- End Module Loop
747 hr_utility.set_location(' Leaving:'|| l_proc, 40);
748 end write_all_errors_report;
749 --
750 -- ----------------------------------------------------------------------------
751 -- |------------------------< write_one_errors_report >-----------------------|
752 -- ----------------------------------------------------------------------------
753 --
754 procedure write_one_errors_report
755 (p_api_module_id in number
756 ) is
757 --
758 -- Cursor to validate the API module exists
759 -- and it is not an Alternative Interface or
760 -- a Data Migrator module.
761 --
762 cursor cur_module is
763 select amd.module_name
764 , hlk.meaning
765 from hr_lookups hlk
766 , hr_api_modules amd
767 where amd.api_module_id = p_api_module_id
768 and hlk.lookup_type = 'API_MODULE_TYPE'
769 and hlk.lookup_code = amd.api_module_type
770 and amd.api_module_type in ('BP', 'RH');
771 --
772 l_module_name varchar2(30);
773 l_module_type_meaning varchar2(80);
774 l_module_name_text varchar2(2000);
775 l_module_type_text varchar2(2000);
776 l_hook_text varchar2(2000);
777 l_hook_pkg_text varchar2(2000);
778 l_success_text varchar2(2000);
779 l_proc varchar2(72) := g_package||'write_one_errors_report';
780 begin
781 hr_utility.set_location('Entering:'|| l_proc, 10);
782 --
783 -- Validate this API module actually exists
784 --
785 open cur_module;
786 fetch cur_module into l_module_name, l_module_type_meaning;
787 if cur_module%found then
788 --
789 -- Output report header
790 --
791 report_title
792 (p_title_message_name => 'HR_51987_AHR_ONE_ERR_TITLE'
793 );
794 hr_utility.set_location(l_proc, 20);
795 --
796 -- Obtain translated text prompts
797 --
798 get_translated_prompts
799 (p_module_name_text => l_module_name_text
800 ,p_module_type_text => l_module_type_text
801 ,p_hook_text => l_hook_text
802 ,p_hook_pkg_text => l_hook_pkg_text
803 ,p_success_text => l_success_text
804 );
805 hr_utility.set_location(l_proc, 30);
806 --
807 -- Output report details for the API module
808 --
809 write_module_errors
810 (p_api_module_id => p_api_module_id
811 ,p_module_name => l_module_name
812 ,p_module_type_meaning => l_module_type_meaning
813 ,p_module_name_text => l_module_name_text
814 ,p_module_type_text => l_module_type_text
815 ,p_hook_text => l_hook_text
816 ,p_hook_pkg_text => l_hook_pkg_text
817 ,p_success_text => l_success_text
818 ,p_include_success => true
819 );
820 hr_utility.set_location(l_proc, 40);
821 end if;
822 close cur_module;
823 hr_utility.set_location(' Leaving:'|| l_proc, 50);
824 end write_one_errors_report;
825 --
826 -- ----------------------------------------------------------------------------
827 -- |---------------------------< clear_hook_report >--------------------------|
828 -- ----------------------------------------------------------------------------
829 --
830 procedure clear_hook_report is
831 --
832 -- Cursor to find other sessions
833 --
834 cursor csr_oth_ses is
835 select distinct session_id
836 from hr_api_user_hook_reports
837 where session_id <> userenv('SESSIONID');
838 --
839 -- Cursor to find if a specific session is active
840 --
841 cursor csr_v_ses (p_session_id number) is
842 select null
843 from gv$session
844 where audsid = p_session_id;
845 --
846 c_header_lines constant number := 4;
847 l_exists varchar2(1);
848 l_proc varchar2(72) := g_package||'clear_hook_report';
849 begin
850 hr_utility.set_location('Entering:'|| l_proc, 10);
851 --
852 -- Delete rows in the report table for this session
853 --
854 delete from hr_api_user_hook_reports
855 where session_id = userenv('SESSIONID');
856 --
857 -- Delete rows from the report table for any sessions
858 -- which no longer exist. (Clean-up to ensure unwanted
859 -- rows do not build-up in this table.)
860 --
861 -- Note: Due to wwbug 854170, deliberately using a cursor
862 -- loop instead of an join or sub-query between a
863 -- standard table and a v$ view. These separate cursors
864 -- and PL/SQL code are providing the same result as:
865 -- delete from hr_api_user_hook_reports a
866 -- where not exists (select null
867 -- from v$session s
868 -- where s.audsid = a.session_id);
869 --
870 for l_oth_ses in csr_oth_ses loop
871 --
872 open csr_v_ses(l_oth_ses.session_id);
873 fetch csr_v_ses into l_exists;
874 if csr_v_ses%notfound then
875 -- Session is not active so remove corresponding
876 -- rows from hr_api_user_hook_reports
877 delete from hr_api_user_hook_reports
878 where session_id = l_oth_ses.session_id;
879 end if;
880 close csr_v_ses;
881 --
882 end loop; -- End Module Loop
883 --
884 commit;
885 --
886 -- If the report has output details of an error then raise
887 -- an unhandled exception. So if AutoInstall had started the
888 -- pre-processor it will detect that an error has occurred.
889 --
890 if g_error_detected then
891 raise program_error;
892 end if;
893 --
894 hr_utility.set_location(' Leaving:'|| l_proc, 20);
895 end clear_hook_report;
896 --
897 -- ----------------------------------------------------------------------------
898 -- |------------------------< create_hooks_all_modules >----------------------|
899 -- ----------------------------------------------------------------------------
900 --
901 procedure create_hooks_all_modules is
902 --
903 -- Cursor to obtain the names of all the API hook packages
904 --
905 cursor cur_hooks is
906 select distinct hook_package
907 from hr_api_hooks;
908 --
909 -- Local variables
910 --
911 l_proc varchar2(72) := g_package||'create_hooks_all_modules';
912 begin
913 hr_utility.set_location('Entering:'|| l_proc, 10);
914 --
915 -- Create each hook package which is defined in the HR_API_HOOKS table
916 -- Commit after each package has been created to ensure any error
917 -- information written to the HR_API_HOOKS and HR_API_HOOK_CALLS tables
918 -- is kept.
919 --
920 for l_hook in cur_hooks loop
921 hr_api_user_hooks.create_package_body(l_hook.hook_package);
922 dbms_session.free_unused_user_memory;
923 commit;
924 end loop;
925 hr_utility.set_location(' Leaving:'|| l_proc, 20);
926 end create_hooks_all_modules;
927 --
928 -- ----------------------------------------------------------------------------
929 -- |------------------------< create_hooks_one_module >-----------------------|
930 -- ----------------------------------------------------------------------------
931 --
932 procedure create_hooks_one_module
933 (p_api_module_id in number
934 ) is
935 --
936 -- Cursor to validate the API module exists
937 -- and it is a business process or row handler
938 --
939 cursor cur_module is
940 select 'Y'
941 from hr_api_modules
942 where api_module_id = p_api_module_id
943 and api_module_type in ('BP', 'RH');
944 --
945 -- Cursor to obtain the the API hook package names
946 --
947 cursor cur_hooks is
948 select distinct hook_package
949 from hr_api_hooks
950 where api_module_id = p_api_module_id;
951 --
952 -- Local variables
953 --
954 l_exists varchar2(30);
955 l_proc varchar2(72) := g_package||'create_hooks_one_module';
956 begin
957 hr_utility.set_location('Entering:'|| l_proc, 10);
958 --
959 -- Validate this API module actually exists
960 --
961 open cur_module;
962 fetch cur_module into l_exists;
963 if cur_module%found then
964 --
965 -- For this API module, create each hook package defined in the
966 -- HR_API_HOOKS table. Commit after each package has been created to
967 -- ensure any error information written to the HR_API_HOOKS and
968 -- HR_API_HOOK_CALLS tables is kept.
969 --
970 for l_hook in cur_hooks loop
971 hr_api_user_hooks.create_package_body(l_hook.hook_package);
972 dbms_session.free_unused_user_memory;
973 commit;
974 end loop;
975 end if;
976 close cur_module;
977 hr_utility.set_location(' Leaving:'|| l_proc, 20);
978 end create_hooks_one_module;
979 --
980 -- ----------------------------------------------------------------------------
981 -- |------------------------< create_hooks_add_report >-----------------------|
982 -- ----------------------------------------------------------------------------
983 --
984 procedure create_hooks_add_report
985 (p_api_module_id in number
986 ) is
987 --
988 -- Local variables
989 --
990 l_proc varchar2(72) := g_package||'create_hooks_add_report';
991 begin
992 hr_utility.set_location('Entering:'|| l_proc, 10);
993 --
994 create_hooks_one_module
995 (p_api_module_id => p_api_module_id
996 );
997 --
998 write_one_errors_report
999 (p_api_module_id => p_api_module_id
1000 );
1001 --
1002 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1003 end create_hooks_add_report;
1004 --
1005 end hr_api_user_hooks_utility;
[Home] [Help]