1 PACKAGE HR_MULTI_MESSAGE AUTHID CURRENT_USER AS
2 /* $Header: hrmulmes.pkh 115.1 2002/01/03 01:38:48 pkm ship $ */
3 --
4 -- Global Constants
5 --
6 -- Message Types
7 G_ERROR_MSG constant varchar2(1) := 'E';
8 G_WARNING_MSG constant varchar2(1) := 'W';
9 G_INFORMATION_MSG constant varchar2(1) := 'I';
10 G_DEPENDENCY_MSG constant varchar2(1) := 'D';
11 --
12 -- Exceptions
13 --
14 -- Used to indicate to calling code that at least one
15 -- message exists in the Multiple Message List.
16 Error_Message_Exist Exception;
17 --
18 -- ----------------------------------------------------------------------------
19 -- |-------------------------< enable_message_list >--------------------------|
20 -- ----------------------------------------------------------------------------
21 -- {Start Of Comments}
22 --
23 -- Description:
24 -- Switches on the Multiple Message Detection feature. Initialises this
25 -- feature and any existing list entries are cleared. Should be called by
26 -- interfaces which require Multiple Message Detection, instead of stop at
27 -- first error found, behaviour prior to calling the PL/SQL API.
28 --
29 -- Prerequisites:
30 -- Should not be called by the API or row handler validation procedures.
31 --
32 -- In Parameters:
33 -- None
34 --
35 -- Post Success:
36 -- The Multiple Error Message Detection feature will be initialised and
37 -- switched on. Messages, if any, still in the list from a previous API call
38 -- will be cleared.
39 --
40 -- Post Failure:
41 -- None expected. An ORA or PL/SQL exception will be raised.
42 --
43 -- Access Status:
44 -- Internal Development Use Only.
45 --
46 -- {End Of Comments}
47 --
48 procedure enable_message_list;
49 --
50 -- ----------------------------------------------------------------------------
51 -- |-------------------------< disable_message_list >-------------------------|
52 -- ----------------------------------------------------------------------------
53 -- {Start Of Comments}
54 --
55 -- Description:
56 -- Switches off the Multiple Error Message Detection feature. Disabling
57 -- the list prevents new messages from being added to the list and the
58 -- "verify" functions (no_all_inclusive_error, no_exclusive_error and
59 -- no_error_message) from returning FALSE.
60 --
61 -- The current contents of the message list will not be cleared until the
62 -- list is enabled again. Thus allowing the current message list contents
63 -- to be retrieved by user interface programs.
64 --
65 -- Prerequisites:
66 -- The program which enabled the Multiple Message Detection should call
67 -- this function after the PL/SQL APIs.
68 --
69 -- In Parameters:
70 -- None
71 --
72 -- Post Success:
73 -- The Multiple Message Detection feature will be switched off.
74 --
75 -- Post Failure:
76 -- None expected. An ORA or PL/SQL exception will be raised.
77 --
78 -- Access Status:
79 -- Internal Development Use Only.
80 --
81 -- {End Of Comments}
82 --
83 procedure disable_message_list;
84 --
85 -- ----------------------------------------------------------------------------
86 -- |------------------------< is_message_list_enabled >-----------------------|
87 -- ----------------------------------------------------------------------------
88 -- {Start Of Comments}
89 --
90 -- Description:
91 -- Returns the current status of the Multiple Message Detection feature.
92 --
93 -- Prerequisites:
94 -- None.
95 --
96 -- In Parameters:
97 -- None
98 --
99 -- Post Success:
100 -- Returns TRUE if the Multiple Message Detection is currently enabled.
101 -- Otherwise FALSE is returned and caller can assume that stop at first
102 -- error found behaviour is required.
103 --
104 -- Post Failure:
105 -- None expected. An ORA or PL/SQL exception will be raised.
106 --
107 -- Access Status:
108 -- Internal Development Use Only.
109 --
110 -- {End Of Comments}
111 --
112 function is_message_list_enabled
113 return boolean;
114 --
115 -- ----------------------------------------------------------------------------
116 -- |------------------------< no_all_inclusive_error >------------------------|
117 -- ----------------------------------------------------------------------------
118 -- {Start Of Comments}
119 --
120 -- Description:
121 -- When row handler data validation procedures have prerequisites this
122 -- function can be used to decided if the checks should proceed. Useful
123 -- for ensuring dependent validation is only executed when the previous
124 -- individual or combination column validation has actually passed.
125 -- Looks for error message which includes any reference to the check
126 -- column.
127 --
128 -- When Multiple Message Detection is disabled:
129 -- Always returns TRUE.
130 --
131 -- When Multiple Message Detection is enabled:
132 -- Returns TRUE when there are no errors in the Multiple Message List
133 -- matching any of the given check TABLE.COLUMN names or API control
134 -- parameter names. Each individual p_check_column1 to 5 parameter
135 -- value is checked, not the set of column names.
136 --
137 -- FALSE is returned when an entry already exists in the message list
138 -- which is associated with at least one of the check column names.
139 -- Returning FALSE indicates to the current data validation procedure
140 -- that validation should not be attempted because a more serious
141 -- error has already been found.
142 --
143 -- For example, say the message list contains two entries:
144 -- Message_1 associated with TABLE_A.COLUMN_1
145 -- Message_2 associated with TABLE_A.COLUMN_2 and TABLE_A.COLUMN_3
146 --
147 -- Calls to this function and results:
148 -- a) p_check_column1 parameter set to 'TABLE_A.COLUMN_4'
149 -- TRUE will be returned.
150 -- b) p_check_column1 parameter set to 'TABLE_A.COLUMN_1'
151 -- FALSE will be returned.
152 -- c) p_check_column1 parameter set to 'TABLE_A.COLUMN_2'
153 -- FALSE will be returned.
154 -- d) p_check_column1 parameter set to 'TABLE_A.COLUMN_1' and
155 -- p_check_column2 parameter set to 'TABLE_A.COLUMN_2'
156 -- FALSE will be returned.
157 -- e) p_check_column1 parameter set to 'TABLE_A.COLUMN_2' and
158 -- p_check_column2 parameter set to 'TABLE_A.COLUMN_3'
159 -- FALSE will be returned.
160 -- f) p_check_column1 parameter set to 'TABLE_A.COLUMN_2' and
161 -- p_check_column2 parameter set to 'TABLE_A.COLUMN_4'
162 -- FALSE will be returned.
163 --
164 -- This function is one of the Multiple Message list "verify" functions.
165 -- All of the "verify" functions can also handle validation dependency
166 -- chains. When a matching message is found then any additional
167 -- internal/dummy message can be added to the list, using the
168 -- p_associated_column1 to 5 parameter values. Therefore following data
169 -- attribute validation only needs to perform prerequisite verification
170 -- for the most adjacent columns in any validation dependency chain.
171 --
172 -- So when calling this function set the p_associated_column1 to 5
173 -- parameters to the list of TABLE.COLUMN or API parameter names
174 -- which would have been validated by your data validation if no
175 -- matching error message had been found.
176 --
177 -- Prerequisites:
178 -- None.
179 --
180 -- In Parameters:
181 -- Name Reqd Type Description
182 -- p_check_column1 Yes varchar2 Column or API control
183 -- parameter name to
184 -- verify in the message
185 -- list.
186 -- p_check_column2 No varchar2 Column or API control
187 -- parameter name to
188 -- verify in the message
189 -- list.
190 -- p_check_column3 No varchar2 Column or API control
191 -- parameter name to
192 -- verify in the message
193 -- list.
194 -- p_check_column4 No varchar2 Column or API control
195 -- parameter name to
196 -- verify in the message
197 -- list.
198 -- p_check_column5 No varchar2 Column or API control
199 -- parameter name to
200 -- verify in the message
201 -- list.
202 -- p_associated_column1 No varchar2 Dependent database
203 -- column or API
204 -- control parameter
205 -- name. Only used if
206 -- an existing message
207 -- is found.
208 -- p_associated_column2 No varchar2 Dependent database
209 -- column or API
210 -- control parameter
214 -- p_associated_column3 No varchar2 Dependent database
211 -- name. Only used if
212 -- an existing message
213 -- is found.
215 -- column or API
216 -- control parameter
217 -- name. Only used if
218 -- an existing message
219 -- is found.
220 -- p_associated_column4 No varchar2 Dependent database
221 -- column or API
222 -- control parameter
223 -- name. Only used if
224 -- an existing message
225 -- is found.
226 -- p_associated_column5 No varchar2 Dependent database
227 -- column or API
228 -- control parameter
229 -- name. Only used if
230 -- an existing message
231 -- is found.
232 --
233 -- Post Success:
234 -- Returns TRUE when Multiple Error Detection is disabled or
235 -- when all of p_check_column1 to 5 parameter values are NOT
236 -- associated with an existing message in the list. A TRUE
237 -- value indicates the API validation should be performed.
238 --
239 -- Returns FALSE when at least one of the columns ARE matched
240 -- with an existing message in the list. If the
241 -- p_assoicated_column1 to 5 parameters have also been set then
242 -- a dummy message will be added to the list and will be
243 -- associated with the p_assoicated_column1 to 5 values.
244 -- A FALSE value indicates then API validation should not
245 -- be performed.
246 --
247 -- Post Failure:
248 -- None expected. An ORA or PL/SQL exception will be raised.
249 --
250 -- Access Status:
251 -- Internal Development Use Only.
252 --
253 -- {End Of Comments}
254 --
255 function no_all_inclusive_error
256 (p_check_column1 in varchar2
257 ,p_check_column2 in varchar2 default null
258 ,p_check_column3 in varchar2 default null
259 ,p_check_column4 in varchar2 default null
260 ,p_check_column5 in varchar2 default null
261 ,p_associated_column1 in varchar2 default null
262 ,p_associated_column2 in varchar2 default null
263 ,p_associated_column3 in varchar2 default null
264 ,p_associated_column4 in varchar2 default null
265 ,p_associated_column5 in varchar2 default null
266 ) return boolean;
267 --
268 -- ----------------------------------------------------------------------------
269 -- |--------------------------< no_exclusive_error >--------------------------|
270 -- ----------------------------------------------------------------------------
271 -- {Start Of Comments}
272 --
273 -- Description:
274 -- When row handler data validation procedures have prerequisites this
275 -- function can be used to decided if the checks should proceed. Useful
276 -- for ensuring dependent combination column validation is only executed
277 -- when the individual columns are valid and other combination errors can
278 -- be ignored.
279 --
280 -- When Multiple Message Detection is disabled:
281 -- Always returns TRUE.
282 --
283 -- When Multiple Message Detection is enabled:
284 -- Returns TRUE when there are no errors in the Multiple Message List
285 -- exclusively matching any of the given check TABLE.COLUMN names or
286 -- API control parameter names.
287 --
288 -- FALSE is returned when an entry already exists in the message list
289 -- which is exclusively associated with one of the check column names. Each
290 -- individual p_check_column1 to 5 parameter value is checked, not the set
291 -- of column names. This means that messages in the list which are
292 -- associated with more than one column will be ignored by this function
293 -- and cannot lead to a FALSE value being returned. Only messages
294 -- associated with a single column name, or API control parameter name,
295 -- can lead to a FALSE value being returned. Returning FALSE indicates to
296 -- the current data validation procedure that validation should not be
297 -- attempted because a more serious individual column error has already
298 -- been found.
299 --
300 -- For example, say the message list contains two entries:
301 -- Message_1 associated with TABLE_A.COLUMN_1
302 -- Message_2 associated with TABLE_A.COLUMN_2 and TABLE_A.COLUMN_3
303 --
304 -- Calls to this function and results:
305 -- a) p_check_column1 parameter set to 'TABLE_A.COLUMN_4'
306 -- TRUE will be returned.
307 -- b) p_check_column1 parameter set to 'TABLE_A.COLUMN_1'
308 -- FALSE will be returned.
309 -- c) p_check_column1 parameter set to 'TABLE_A.COLUMN_2'
310 -- TRUE will be returned.
311 -- d) p_check_column1 parameter set to 'TABLE_A.COLUMN_1' and
312 -- p_check_column2 parameter set to 'TABLE_A.COLUMN_2'
313 -- FALSE will be returned.
314 -- e) p_check_column1 parameter set to 'TABLE_A.COLUMN_2' and
315 -- p_check_column2 parameter set to 'TABLE_A.COLUMN_3'
316 -- TRUE will be returned.
317 -- f) p_check_column1 parameter set to 'TABLE_A.COLUMN_2' and
318 -- p_check_column2 parameter set to 'TABLE_A.COLUMN_4'
319 -- TRUE will be returned.
320 --
324 -- internal/dummy message can be added to the list, using the
321 -- This function is one of the Multiple Message list "verify" functions.
322 -- All of the "verify" functions can also handle validation dependency
323 -- chains. When a matching message is found then any additional
325 -- p_associated_column1 to 5 parameter values. Therefore following data
326 -- attribute validation only needs to perform prerequisite verification
327 -- for the most adjacent columns in any validation dependency chain.
328 --
329 -- So when calling this function set the p_associated_column1 to 5
330 -- parameters to the list of TABLE.COLUMN or API parameter names
331 -- which would have been validated by your data validation if no
332 -- matching error message had been found.
333 --
334 -- Prerequisites:
335 -- None.
336 --
337 -- In Parameters:
338 -- Name Reqd Type Description
339 -- p_check_column1 Yes varchar2 Column or API control
340 -- parameter name to
341 -- verify in the message
342 -- list.
343 -- p_check_column2 No varchar2 Column or API control
344 -- parameter name to
345 -- verify in the message
346 -- list.
347 -- p_check_column3 No varchar2 Column or API control
348 -- parameter name to
349 -- verify in the message
350 -- list.
351 -- p_check_column4 No varchar2 Column or API control
352 -- parameter name to
353 -- verify in the message
354 -- list.
355 -- p_check_column5 No varchar2 Column or API control
356 -- parameter name to
357 -- verify in the message
358 -- list.
359 -- p_associated_column1 No varchar2 Dependent database
360 -- column or API
361 -- control parameter
362 -- name. Only used if
363 -- an existing message
364 -- is found.
365 -- p_associated_column2 No varchar2 Dependent database
366 -- column or API
367 -- control parameter
368 -- name. Only used if
369 -- an existing message
370 -- is found.
371 -- p_associated_column3 No varchar2 Dependent database
372 -- column or API
373 -- control parameter
374 -- name. Only used if
375 -- an existing message
376 -- is found.
377 -- p_associated_column4 No varchar2 Dependent database
378 -- column or API
379 -- control parameter
380 -- name. Only used if
381 -- an existing message
382 -- is found.
383 -- p_associated_column5 No varchar2 Dependent database
384 -- column or API
385 -- control parameter
386 -- name. Only used if
387 -- an existing message
388 -- is found.
389 --
390 -- Post Success:
391 -- Returns TRUE when Multiple Error Detection is disabled or
392 -- when all of p_check_column1 to 5 parameter values are NOT
393 -- exclusively associated with an existing message in the list.
394 -- A TRUE value indicates the API validation should be performed.
395 --
396 -- Returns FALSE when at least one of the columns IS exclusively
397 -- matched with an existing message in the list. If the
398 -- p_assoicated_column1 to 5 parameters have also been set then
399 -- a dummy message will be added to the list and will be
400 -- associated with the p_assoicated_column1 to 5 values.
401 -- A FALSE value indicates then API validation should not
402 -- be performed.
403 --
404 -- Post Failure:
405 -- None expected. An ORA or PL/SQL exception will be raised.
406 --
407 -- Access Status:
408 -- Internal Development Use Only.
409 --
410 -- {End Of Comments}
411 --
412 function no_exclusive_error
413 (p_check_column1 in varchar2
414 ,p_check_column2 in varchar2 default null
415 ,p_check_column3 in varchar2 default null
416 ,p_check_column4 in varchar2 default null
417 ,p_check_column5 in varchar2 default null
418 ,p_associated_column1 in varchar2 default null
419 ,p_associated_column2 in varchar2 default null
420 ,p_associated_column3 in varchar2 default null
421 ,p_associated_column4 in varchar2 default null
422 ,p_associated_column5 in varchar2 default null
423 ) return boolean;
427 -- ----------------------------------------------------------------------------
424 --
425 -- ----------------------------------------------------------------------------
426 -- |---------------------------< no_error_message >---------------------------|
428 -- {Start Of Comments}
429 --
430 -- Description:
431 -- When row handler data validation procedures have prerequisites this
432 -- function can be used to decided if the checks should proceed. Useful for
433 -- ensuring dependent validation is only executed when previous validation
434 -- has not already identified an particular error.
435 --
436 -- The preferred way of verifying prerequisites is to call the
437 -- no_all_inclusive_error or no_exclusive_error functions. As they check
438 -- the Multiple Message List using TABLE.COLUMN names or API control
439 -- parameter names. Those searches are preferred because some columns can
440 -- be associated with many different error messages and the exact validation
441 -- message names can change over time.
442 --
443 -- When Multiple Message Detection is disabled:
444 -- Always returns TRUE.
445 --
446 -- When Multiple Message Detection is enabled:
447 -- Returns TRUE when there are no errors in the Multiple Message List
448 -- matching any of the given check message names.
449 --
450 -- FALSE is returned when at least one of the check message names has
451 -- a matching entry in the message list. Returning FALSE indicates to
452 -- the current data validation procedure that validation should not be
453 -- attempted because a more serious error has already been found.
454 --
455 -- For example, say the message list contains two entries:
456 -- Message_1 associated with TABLE_A.COLUMN_1
457 -- Message_2 associated with TABLE_A.COLUMN_2 and TABLE_A.COLUMN_3
458 --
459 -- Calls to this function and results:
460 -- a) p_check_message_name1 parameter set to 'Message_3'
461 -- TRUE will be returned.
462 -- b) p_check_message_name1 parameter set to 'Message_1'
463 -- FALSE will be returned.
464 -- c) p_check_message_name1 parameter set to 'Message_1' and
465 -- p_check_message_name2 parameter set to 'Message_2'
466 -- FALSE will be returned.
467 -- d) p_check_message_name1 parameter set to 'Message_2' and
468 -- p_check_message_name2 parameter set to 'Message_3'
469 -- FALSE will be returned.
470 --
471 -- This function is one of the Multiple Message list "verify" functions.
472 -- All of the "verify" functions can also handle validation dependency
473 -- chains. When a matching message is found then any additional
474 -- internal/dummy message can be added to the list, using the
475 -- p_associated_column1 to 5 parameter values. Therefore following data
476 -- attribute validation only needs to perform prerequisite verification
477 -- for the most adjacent columns in any validation dependency chain.
478 --
479 -- So when calling this function set the p_associated_column1 to 5
480 -- parameters to the list of TABLE.COLUMN or API parameter names
481 -- which would have been validated by your data validation if no
482 -- matching error message had been found.
483 --
484 -- Prerequisites:
485 -- None.
486 --
487 -- In Parameters:
488 -- Name Reqd Type Description
489 -- p_check_message_name1 Yes varchar2 Application Error
490 -- Message Name to
491 -- verify in the list.
492 -- p_check_message_name2 No varchar2 Application Error
493 -- Message Name to
494 -- verify in the list.
495 -- p_check_message_name3 No varchar2 Application Error
496 -- Message Name to
497 -- verify in the list.
498 -- p_check_message_name4 No varchar2 Application Error
499 -- Message Name to
500 -- verify in the list.
501 -- p_check_message_name5 No varchar2 Application Error
502 -- Message Name to
503 -- verify in the list.
504 -- p_associated_column1 No varchar2 Dependent database
505 -- column or API
506 -- control parameter
507 -- name. Only used if
508 -- an existing error
509 -- is found.
510 -- p_associated_column2 No varchar2 Dependent database
511 -- column or API
512 -- control parameter
513 -- name. Only used if
514 -- an existing error
515 -- is found.
516 -- p_associated_column3 No varchar2 Dependent database
517 -- column or API
518 -- control parameter
519 -- name. Only used if
520 -- an existing error
521 -- is found.
522 -- p_associated_column4 No varchar2 Dependent database
523 -- column or API
524 -- control parameter
525 -- name. Only used if
526 -- an existing error
530 -- control parameter
527 -- is found.
528 -- p_associated_column5 No varchar2 Dependent database
529 -- column or API
531 -- name. Only used if
532 -- an existing error
533 -- is found.
534 --
535 -- Post Success:
536 -- Returns TRUE when Multiple Error Detection is disabled or when
537 -- none of p_check_message_name1 to 5 parameter values match any
538 -- existing error message in the list. A TRUE value indicates the
539 -- API validation should be performed.
540 --
541 -- Returns FALSE when at least one of the message names does match
542 -- an existing error message in the list. If the p_assoicated_column1
543 -- to 5 parameters have also been set then a dummy message will be
544 -- added to the list and will be associated with the
545 -- p_assoicated_column1 to 5 values. A FALSE value indicates the API
546 -- validation should not be performed.
547 --
548 -- Post Failure:
549 -- None expected. An ORA or PL/SQL exception will be raised.
550 --
551 -- Access Status:
552 -- Internal Development Use Only.
553 --
554 -- {End Of Comments}
555 --
556 function no_error_message
557 (p_check_message_name1 in varchar2
558 ,p_check_message_name2 in varchar2 default null
559 ,p_check_message_name3 in varchar2 default null
560 ,p_check_message_name4 in varchar2 default null
561 ,p_check_message_name5 in varchar2 default null
562 ,p_associated_column1 in varchar2 default null
563 ,p_associated_column2 in varchar2 default null
564 ,p_associated_column3 in varchar2 default null
565 ,p_associated_column4 in varchar2 default null
566 ,p_associated_column5 in varchar2 default null
567 ) return boolean;
568 --
569 -- ----------------------------------------------------------------------------
570 -- |------------------------------< exception_add >---------------------------|
571 -- ----------------------------------------------------------------------------
572 -- {Start Of Comments}
573 --
574 -- Description:
575 -- When row handler data validation procedures have raised an Application
576 -- specific exception this function can be used to decide if the message
577 -- should be added to the Multiple Message List or raised as an individual
578 -- exception. Designed for use from an exception handler which has only
579 -- caught Application specific exceptions.
580 --
581 -- When Multiple Message Detection is disabled:
582 -- Always returns TRUE.
583 --
584 -- When Multiple Message Detection is enabled:
585 -- Returns FALSE and the error message has been added to FND_MSG_PUB
586 -- package, by copying the current message name and token details from
587 -- the FND_MESSAGE package.
588 --
589 -- When the error can be associated with specific database column, or API
590 -- control parameter, name(s) then either the p_associated_column1 to 5
591 -- parameters should be populated or the p_same_associated_columns
592 -- parameter should be explicitly set to 'Y'.
593 --
594 -- The p_same_associated_columns parameter is defaulted to 'N' indicating
595 -- the p_associated_column1 to 5 parameter set in this function call
596 -- should be used. To use the p_associated_column1 to 5 parameter values
597 -- past into the most recent "verify" function call
598 -- (no_all_inclusive_error, no_exclusive_error and no_error_message)
599 -- then explicitly set p_same_associated_columns to 'Y'. Where a "verify"
600 -- function has been called, setting p_same_associated_columns to 'Y'
601 -- will avoid having to maintain the associated column information more
602 -- than once.
603 --
604 -- No error will be raised if p_same_associated_columns is 'Y' and
605 -- p_associated_column1 to 5 parameters have also been set. The
606 -- p_associated_column1 to 5 values will just be ignored in this scenario.
607 --
608 -- Prerequisites:
609 -- An Application specific exception has been raised, by calling
610 -- FND_MESSAGE.RAISE_ERROR, and details of an error message have been set
611 -- in the FND_MESSAGE package. Where possible the p_same_associated_columns
612 -- parameter should be set to 'Y'. When set to 'N' then the associated
613 -- column values should be provided by setting the p_associated_column1 to 5
614 -- parameters.
615 --
616 -- In Parameters:
617 -- Name Reqd Type Description
618 -- p_associated_column1 No varchar2 Database column or
619 -- API control parameter
620 -- name.
621 -- p_associated_column2 No varchar2 Database column or
622 -- API control parameter
623 -- name.
624 -- p_associated_column3 No varchar2 Database column or
625 -- API control parameter
626 -- name.
627 -- p_associated_column4 No varchar2 Database column or
628 -- API control parameter
629 -- name.
630 -- p_associated_column5 No varchar2 Database column or
631 -- API control parameter
632 -- name.
633 -- p_same_associated_columns No varchar2 Must be 'Y' or 'N'.
634 --
635 -- Post Success:
636 -- Returns TRUE when Multiple Message Detection is disabled. Indicating
640 --
637 -- the exception handler should not catch the exception. The exception
638 -- handler should raise the exception providing stop at first error
639 -- found behaviour.
641 -- Returns FALSE when Multiple Message Detection is enabled. An error
642 -- message will have been added to FND_MSG_PUB package by copying the
643 -- current message name and token details from the FND_MESSAGE package.
644 -- The FND_MESSAGE package will be cleared to allow for the next message
645 -- to be set. If no message details had been registered with FND_MESSAGE
646 -- no message will be added to the FND_MSG_PUB package.
647 --
648 -- Post Failure:
649 -- None expected. An ORA or PL/SQL exception will be raised.
650 --
651 -- Access Status:
652 -- Internal Development Use Only.
653 --
654 -- {End Of Comments}
655 --
656 function exception_add
657 (p_associated_column1 in varchar2 default null
658 ,p_associated_column2 in varchar2 default null
659 ,p_associated_column3 in varchar2 default null
660 ,p_associated_column4 in varchar2 default null
661 ,p_associated_column5 in varchar2 default null
662 ,p_same_associated_columns in varchar2 default 'N'
663 ) return boolean;
664 --
665 -- ----------------------------------------------------------------------------
666 -- |-----------------------------------< add >--------------------------------|
667 -- ----------------------------------------------------------------------------
668 -- {Start Of Comments}
669 --
670 -- Description:
671 -- When row handler data validation procedures are about to raise a message
672 -- this procedure can be called to decide how the message should be
673 -- processed. This procedure will either add the message to the Multiple
674 -- Message List or raise an individual Application Specific exception.
675 --
676 -- Usually data checks in the same row handler validation procedure are
677 -- dependent on each other. Hence the preferred mechanism for processing
678 -- messages is to use the exception_add function. Calls to this add
679 -- procedure should only be made from validation procedures which
680 -- perform multiple independent checks or associated column information
681 -- can vary depending on the raised error message. Can also be used by
682 -- WEB wrapper procedures to add warning and information messages to the
683 -- message list.
684 --
685 -- This add procedure provides similar behaviour to the exception_add
686 -- function, except program control should not be inside an exception
687 -- handler. This procedure can be called from the procedure body as a
688 -- replacement to calling FND_MESSAGE.RAISE_ERROR. By default this procedure
689 -- assumes the message is an error message. When Multiple Message Detection
690 -- is enabled it can also be used to add warning and information type
691 -- messages.
692 --
693 -- When Multiple Message Detection is disabled:
694 -- For error messages calls FND_MESSAGE.RAISE_ERROR causing a PL/SQL
695 -- Application Specific exception to be raised. Thus aborting any
696 -- following processing in row handler validation procedure. Any warning
697 -- or information messages will be ignored and the contents of FND_MESSAGE
698 -- package cleared. So program control will continue with the next line in
699 -- the row handler validation procedure.
700 --
701 -- When Multiple Message Detection is enabled:
702 -- All message types will be added to the FND_MSG_PUB package, by copying
703 -- the current message name and token details from the FND_MESSAGE package.
704 -- No exception will be raised, so program control will continue with
705 -- the next line in the row handler validation procedure.
706 --
707 -- When an error can be associated with specific database column, or API
708 -- control parameter, name(s) then either the p_associated_column1 to 5
709 -- parameters should be populated or the p_same_associated_columns
710 -- parameter should be explicitly set to 'Y'.
711 --
712 -- The p_same_associated_columns parameter is defaulted to 'N' indicating
713 -- the p_associated_column1 to 5 parameter set in this procedure call
714 -- should be used. To use the p_associated_column1 to 5 parameter values
715 -- past into the most recent "verify" function call
716 -- (no_all_inclusive_error, no_exclusive_error and no_error_message)
717 -- then explicitly set p_same_associated_columns to 'Y'. Where a "verify"
718 -- function has been called, setting p_same_associated_columns to 'Y'
719 -- will avoid having to maintain the associated column information more
720 -- than once.
721 --
722 -- No error will be raised if p_same_associated_columns is 'Y' and
723 -- p_associated_column1 to 5 parameters have also been set. The
724 -- p_associated_column1 to 5 values will just be ignored in this scenario.
725 --
726 -- When the message type is not error no associated column information
727 -- is set. The p_same_associated_columns and p_associated_column1 to 5
728 -- parameters values are ignored.
729 --
730 -- Prerequisites:
731 -- Details of a message have been set in the FND_MESSAGE package. Where
732 -- possible the p_same_associated_columns parameter should be set to 'Y'.
733 -- When set to 'N' then the associated column values should be provided by
734 -- setting the p_associated_column1 to 5 parameters.
735 --
736 -- In Parameters:
737 -- Name Reqd Type Description
738 -- p_associated_column1 No varchar2 Database column or
739 -- API control parameter
740 -- name.
741 -- p_associated_column2 No varchar2 Database column or
742 -- API control parameter
743 -- name.
747 -- p_associated_column4 No varchar2 Database column or
744 -- p_associated_column3 No varchar2 Database column or
745 -- API control parameter
746 -- name.
748 -- API control parameter
749 -- name.
750 -- p_associated_column5 No varchar2 Database column or
751 -- API control parameter
752 -- name.
753 -- p_same_associated_columns No varchar2 Must be 'Y' or 'N'.
754 -- p_message_type No varhcar2 Set to one of the
755 -- HR_MULTI_MESSAGE package
756 -- message type constants.
757 --
758 -- Post Success:
759 -- When Multiple Message Detection is disabled and the message type is error
760 -- then FND_MESSAGE.RAISE_ERROR is called to raise an Application specific
761 -- exception. Thus providing stop at first error found behaviour. Other
762 -- message types are ignored and the FND_MESSAGE package will be cleared to
763 -- allow for the next message to be set.
764 --
765 -- When Multiple Message Detection is enabled. A message will have been
766 -- added to FND_MSG_PUB package by copying the current message name and
767 -- token details from the FND_MESSAGE package. The FND_MESSAGE package will
768 -- be cleared to allow for the next message to be set. If no message details
769 -- had been registered with FND_MESSAGE no message will be added to the
770 -- FND_MSG_PUB package. Associated column information will only be set for
771 -- error messages.
772 --
773 -- Post Failure:
774 -- None expected. An ORA or PL/SQL exception will be raised.
775 --
776 -- Access Status:
777 -- Internal Development Use Only.
778 --
779 -- {End Of Comments}
780 --
781 procedure add
782 (p_associated_column1 in varchar2 default null
783 ,p_associated_column2 in varchar2 default null
784 ,p_associated_column3 in varchar2 default null
785 ,p_associated_column4 in varchar2 default null
786 ,p_associated_column5 in varchar2 default null
787 ,p_same_associated_columns in varchar2 default 'N'
788 ,p_message_type in varchar2 default
789 hr_multi_message.g_error_msg
790 );
791 --
792 -- ----------------------------------------------------------------------------
793 -- |-------------------------< unexpected_error_add >-------------------------|
794 -- ----------------------------------------------------------------------------
795 -- {Start Of Comments}
796 --
797 -- Description:
798 -- Allows Application specific and unexpected exceptions to be added to the
799 -- Multiple Message List. For use from PL/SQL WEB wrapper procedures which
800 -- need to catch any Application specific or other RDBMS exceptions, such
801 -- as ORA- or PLS- errors, prior the procedure call ending.
802 --
803 -- Handles the following scenarios:
804 -- (1) Application specific exception raised and message details exist in
805 -- the FND_MESSAGE package.
806 -- (2) Application specific exception raised and message details do not
807 -- exist in the FND_MESSAGE package.
808 -- (3) Other ORA or PLS exceptions.
809 --
810 -- When Multiple Message Detection is disabled:
811 -- Always returns TRUE.
812 --
813 -- When Multiple Message Detection is enabled:
814 -- Returns FALSE and an error message is added to Multiple Message List
815 -- held in the FND_MSG_PUB package.
816 --
817 -- Prerequisites:
818 -- None.
819 --
820 -- In Parameters:
821 -- Name Reqd Type Description
822 -- p_procedure_name Yes varchar2 Name of the calling
823 -- package and procedure.
824 --
825 -- Post Success:
826 -- Returns TRUE when Multiple Message Detection is disabled. Indicating
827 -- the exception handler should not catch the exception. The exception
828 -- handler should raise the exception.
829 --
830 -- Returns FALSE when Multiple Message Detection is enabled. For scenario
831 -- (1) details of the message are read and cleared from the FND_MESSAGE
832 -- package and an error message will be created in the Multiple Message
833 -- list. For scenarios (2) and (3) an 'FND_AS_UNEXPECTED_ERROR' application
834 -- error message with SQLERRM details will have been added to the Multiple
835 -- Message List.
836 --
837 -- Post Failure:
838 -- None expected. An ORA or PL/SQL exception will be raised.
839 --
840 -- Access Status:
841 -- Internal Development Use Only.
842 --
843 -- {End Of Comments}
844 --
845 function unexpected_error_add
846 (p_procedure_name in varchar2
847 ) return boolean;
848 --
849 -- ----------------------------------------------------------------------------
850 -- |--------------------------< end_validation_set >--------------------------|
851 -- ----------------------------------------------------------------------------
852 -- {Start Of Comments}
853 --
854 -- Description:
855 -- Raises the Hr_Multi_Message.Error_Message_Exist exception when Multiple
859 -- This procedure has no affect when Multiple Message Detection is disabled
856 -- Message Detection is enabled and at least one error message exists in the
857 -- list.
858 --
860 -- or when non-error messages exist in the list.
861 --
862 -- Should be called when all independent row handler data validation checks
863 -- have been completed and it not safe to continue processing to the next
864 -- stage inside the row handler, such as DML.
865 --
866 -- On some tables there can be a small set of important attributes. When
867 -- those attributes are invalid they can affect the ability to perform any
868 -- other attribute validation. For example, when the BUSINESS_GROUP_ID
869 -- column is invalid then often it is not worth attempting further validation
870 -- as so many other attributes depend on it. Hence this procedure call also
871 -- be called after important attributes have been validated to abort
872 -- further validation.
873 --
874 -- Prerequisites:
875 -- None.
876 --
877 -- In Parameters:
878 -- None.
879 --
880 -- Post Success:
881 -- When Multiple Message Detection is disabled or only non-error messages
882 -- exist in the list this procedure will end normally. Allowing the calling
883 -- program to continue it's normal program flow.
884 --
885 -- Post Failure:
886 -- When Multiple Message Detection is enabled and at least one error message
887 -- exists in the list then the Hr_Multi_Message.Error_Message_Exist
888 -- exception is raised.
889 --
890 -- Access Status:
891 -- Internal Development Use Only.
892 --
893 -- {End Of Comments}
894 --
895 procedure end_validation_set;
896 --
897 -- ----------------------------------------------------------------------------
898 -- |---------------------------< get_return_status >--------------------------|
899 -- ----------------------------------------------------------------------------
900 -- {Start Of Comments}
901 --
902 -- Description:
903 -- Returns a status value which indicates when messages of any type exist in
904 -- the Message List. The WEB Wrapper can pass this value to the middle-tier,
905 -- indicating to the OAFramework Exception Helper classes when it is
906 -- necessary to retrieve messages from the list.
907 --
908 -- This function performs the same behaviour regardless of whether the
909 -- Multiple Message Detection is disabled or enabled.
910 --
911 -- Prerequisites:
912 -- None.
913 --
914 -- In Parameters:
915 -- None.
916 --
917 -- Post Success:
918 -- When the Multiple Message List is empty this function returns the value
919 -- 'S' (same as FND_API.G_RET_STS_SUCCESS). If any messages of any type
920 -- exist then 'E' (same as FND_API.G_RET_STS_ERROR) is returned.
921 --
922 -- Post Failure:
923 -- None expected. An ORA or PL/SQL exception will be raised.
924 --
925 -- Developer/Implementation Notes:
926 -- Currently it is unnecessary for this function to make a distinction
927 -- between error and just warning/information messages existing. The
928 -- returned status is just used by the OAFramework Exception Helper
929 -- classes to workout when it is necessary to obtain messages from the
930 -- FND_MSG_PUB package. A separate internal Java row_status is derived in
931 -- the middle-tier from the retrieved message types.
932 --
933 -- Access Status:
934 -- Internal Development Use Only.
935 --
936 -- {End Of Comments}
937 --
938 function get_return_status return varchar2;
939 --
940 -- ----------------------------------------------------------------------------
941 -- |-----------------------< get_return_status_disable >----------------------|
942 -- ----------------------------------------------------------------------------
943 -- {Start Of Comments}
944 --
945 -- Description:
946 -- Disables Multiple Message Detection and returns a status value which
947 -- indicates when messages of any type exist in the Message List. The WEB
948 -- Wrapper can pass this value to the middle-tier, indicating to the
949 -- OAFramework Exception Helper classes when it is necessary to retrieve
950 -- messages from the list.
951 --
952 -- When Multiple Message Detection is disabled:
953 -- Returns a status to indicate if any messages, of any type, exist
954 -- in the message list.
955 --
956 -- When Multiple Message Detection is enabled:
957 -- Provides the same functionality as disable_message_list and also
958 -- returns a status to indicate if any messages, of any type, exist
959 -- in the message list.
960 --
961 -- Calling this function provides the same behaviour as calling the
965 -- None.
962 -- get_return_status function and disable_message_list procedure separately.
963 --
964 -- Prerequisites:
966 --
967 -- In Parameters:
968 -- None.
969 --
970 -- Post Success:
971 -- When the Multiple Message List is empty this function returns the value
972 -- 'S' (same as FND_API.G_RET_STS_SUCCESS). If any messages of any type
973 -- exist then 'E' (same as FND_API.G_RET_STS_ERROR) is returned.
974 --
975 -- Post Failure:
976 -- None expected. An ORA or PL/SQL exception will be raised.
977 --
978 -- Developer/Implementation Notes:
979 -- Currently it is unnecessary for this function to make a distinction
980 -- between error and just warning/information messages existing. The
981 -- returned status is just used by the OAFramework Exception Helper
982 -- classes to workout when it is necessary to obtain messages from the
983 -- FND_MSG_PUB package. A separate internal Java row_status is derived in
984 -- the middle-tier from the retrieved message types.
985 --
986 -- Access Status:
987 -- Internal Development Use Only.
988 --
989 -- {End Of Comments}
990 --
991 function get_return_status_disable return varchar2;
992 --
993 -- ----------------------------------------------------------------------------
994 -- |---------------------< Detailed Package Description >---------------------|
995 -- ----------------------------------------------------------------------------
996 --
997 -- Diagram of HRMS API Code Calling for Error Messages
998 -- ===================================================
999 --
1000 -- ----------------- ---------------------
1001 -- | BC4J style VO | | OAExceptionHelper |
1002 -- | or EO | | Java Class |
1003 -- ----------------- ---------------------
1004 -- Middle tier ^ ^
1005 -- - - - - - - - - - - - | - - - - - - - - - - - - - -|- - - - -
1006 -- Server tier V |
1007 -- ------------------ |
1008 -- | HRMS PL/SQL | |
1009 -- | Self-S Wrapper | |
1010 -- ------------------ |
1011 -- | |
1012 -- V |
1013 -- ----------------- |
1014 -- | HRMS PL/SQL | |
1015 -- | API and | |
1016 -- | row handler | |
1017 -- ----------------- |
1018 -- | | |
1019 -- | V |
1020 -- Always | --------------------- |
1021 -- set error | | HRMS Specific | |
1022 -- details in | | Infrastructure to | |
1023 -- fnd_message | | decide single or | |
1024 -- | | Multiple Error | |
1025 -- | --------------------- |
1026 -- | single | multiple | |
1027 -- V V V V
1028 -- ----------------- -----------------
1029 -- | FND_MESSAGE | | FND_MSG_PUB |
1030 -- | Package | | Package |
1031 -- ----------------- -----------------
1032 --
1033 -- This package provides the HRMS Specific Infrastructure to decide how
1034 -- an individual data validation message should be processed. Either:
1035 -- a) "Stop at First Error Found"
1036 -- Behaviour required by existing batch data upload programs and
1037 -- Form style user interfaces.
1038 -- b) "Multiple Message Detection"
1039 -- Behaviour required by OAFramework style user interfaces where
1040 -- for a single API call multiple validation messages can be
1041 -- returned, for independent validation checks.
1042 --
1043 -- When a HRMS API or row handler PL/SQL code raises an error message
1044 -- it should be set by calling the FND_MESSAGE package and raising a
1045 -- PL/SQL exception. Then the HR_MULTI_MESSAGE package can be called
1046 -- to decide how the message should be processed.
1047 --
1048 -- The assumption is that "Stop at First Error Found" behaviour is
1049 -- required. The HR_MULTI_MESSAGE package will not catch the
1050 -- PL/SQL exception. The error will be left to propagate back to the
1051 -- program which called the PL/SQL API.
1052 --
1053 -- If "Multiple Message Detection" has been explicitly enabled then
1054 -- the HR_MULTI_MESSAGE package will catch the PL/SQL exception and
1055 -- copy the message details from the FND_MESSAGE to FND_MSG_PUB package.
1056 -- Details of which table columns or API control parameters the message
1060 --
1057 -- is associated with should be provided. As the exception has been caught
1058 -- the row handler data validation procedure will complete normally,
1059 -- allowing the program flow to continue to the next validation procedure.
1061 -- Row handler data validation procedures should call the
1062 -- HR_MULTI_MESSAGE package to find out if previous attributes in a
1063 -- dependency chain are already known to be in error. Otherwise there
1064 -- is a risk of generating a meaningless list of messages, where the
1065 -- end user can only trust the first message to be accurate. If multiple
1066 -- messages are raised for the same root cause then this will be more
1067 -- confusing to the end user than receiving messages one at a time.
1068 --
1069 -- The HR_MULTI_MESSAGE package can also be thought of as a cover to
1070 -- the FND_MSG_PUB package. FND_MSG_PUB always provides Multiple Message
1071 -- Detection behaviour.
1072 --
1073 END HR_MULTI_MESSAGE;