1 PACKAGE BODY FND_MSG_PUB AS
2 /* $Header: AFASMSGB.pls 120.2 2005/11/03 15:48:27 tmorrow ship $ */
3
4 -- Constants used as tokens for unexpected error messages.
5
6 G_PKG_NAME CONSTANT VARCHAR2(15):= 'FND_MSG_PUB';
7
8 -- Global constant for number of associated columns
9
10 G_max_cols constant NUMBER := 5;
11
12 -- Record type created to store message_type and associated
13 -- column in addition to message
14
15 TYPE Col_Tbl_Type IS TABLE OF varchar2(62)
16 INDEX BY BINARY_INTEGER;
17
18
19 TYPE Msg_Rec_Type is RECORD
20 (encoded_message varchar2(2000)
21 ,message_type varchar2(1) default FND_MSG_PUB.G_DEPENDENCY_MSG
22 ,associated_column1 varchar2(62) default null
23 ,associated_column2 varchar2(62) default null
24 ,associated_column3 varchar2(62) default null
25 ,associated_column4 varchar2(62) default null
26 ,associated_column5 varchar2(62) default null
27 );
28
29 -- record to store token name value pairs
30
31 TYPE Msg_Token_Type is RECORD
32 (name varchar2(30)
33 ,value varchar2(2000)
34 );
35
36 TYPE Msg_Token_Tbl IS TABLE OF Msg_Token_Type
37 INDEX BY BINARY_INTEGER;
38
39 -- API message table type
40 --
41 -- PL/SQL table of Msg_Rec_Type
42 -- changed from PL/SQL table of VARCHAR2(2000)
43 -- This is the datatype of the API message list
44
45 TYPE Msg_Tbl_Type IS TABLE OF Msg_Rec_Type
46 INDEX BY BINARY_INTEGER;
47
48 -- Global message table variable.
49 -- this variable is global to the FND_MSG_PUB package only.
50
51 G_msg_tbl Msg_Tbl_Type;
52
53 -- Global dependency chain table variable.
54
55 G_dep_tbl Msg_Tbl_Type;
56
57 -- Global variable holding the message count.
58
59 G_msg_count NUMBER := 0;
60
61 -- Global variable holding the dependency message count.
62
63 G_dep_count NUMBER := 0;
64
65 -- Index used by the Get function to keep track of the last fetched
66 -- message.
67
68 G_msg_index NUMBER := 0;
69
70 -- Index used by the Get function to keep track of the last fetched
71 -- dependency message.
72
73 G_dep_index NUMBER := 0;
74
75 -- Global to remember the associated column value between the calls to
76 -- no_all_inclusive_error, no_error_message and add
77
78 G_associated_column Col_Tbl_Type;
79
80 -- Global variables to remember the values
81 -- passed into Set_Search_Name and Set_Search_Token procedures.
82
83 G_ser_msgname varchar2(30);
84 G_ser_msgdata varchar2(2000);
85 G_ser_msgset boolean := FALSE;
86 G_ser_msgapp varchar2(50);
87
88 -- FUNCTION Get_Associated_Col
89 --
90
91 FUNCTION Get_Associated_Col
92 ( p_message IN NUMBER
93 ,p_column IN NUMBER
94 ) return varchar2
95 IS
96 l_associated_column varchar2(62);
97 BEGIN
98 if (p_column = 1) then
99 l_associated_column := G_msg_tbl(p_message).associated_column1;
100 elsif (p_column = 2) then
101 l_associated_column := G_msg_tbl(p_message).associated_column2;
102 elsif (p_column = 3) then
103 l_associated_column := G_msg_tbl(p_message).associated_column3;
104 elsif (p_column = 4) then
105 l_associated_column := G_msg_tbl(p_message).associated_column4;
106 elsif (p_column = 5) then
107 l_associated_column := G_msg_tbl(p_message).associated_column5;
108 end if;
109
110 return l_associated_column;
111
112 EXCEPTION
113
114 WHEN NO_DATA_FOUND THEN
115
116 -- No more messages return NULL;
117
118 return null;
119
120 END Get_Associated_Col;
121
122 -- PROCEDURE Set_Associated_Col
123 --
124
125 PROCEDURE Set_Associated_Col
126 ( p_message IN NUMBER
127 ,p_column IN NUMBER
128 ,p_value IN VARCHAR2
129 )
130 IS
131 BEGIN
132 if (p_column = 1) then
133 G_msg_tbl(p_message).associated_column1 := p_value;
134 elsif (p_column = 2) then
135 G_msg_tbl(p_message).associated_column2 := p_value;
136 elsif (p_column = 3) then
137 G_msg_tbl(p_message).associated_column3 := p_value;
138 elsif (p_column = 4) then
139 G_msg_tbl(p_message).associated_column4 := p_value;
140 elsif (p_column = 5) then
141 G_msg_tbl(p_message).associated_column5 := p_value;
142 end if;
143 EXCEPTION
144
145 WHEN NO_DATA_FOUND THEN
146
147 null;
148
149 END Set_Associated_Col;
150
151 -- FUNCTION Get_Dependency_Col
152 --
153
154 FUNCTION Get_Dependency_Col
155 ( p_message IN NUMBER
156 ,p_column IN NUMBER
157 ) return varchar2
158 IS
159 l_associated_column varchar2(62);
160 BEGIN
161 if (p_column = 1) then
162 l_associated_column := G_dep_tbl(p_message).associated_column1;
163 elsif (p_column = 2) then
164 l_associated_column := G_dep_tbl(p_message).associated_column2;
165 elsif (p_column = 3) then
166 l_associated_column := G_dep_tbl(p_message).associated_column3;
167 elsif (p_column = 4) then
168 l_associated_column := G_dep_tbl(p_message).associated_column4;
169 elsif (p_column = 5) then
170 l_associated_column := G_dep_tbl(p_message).associated_column5;
171 end if;
172
173 return l_associated_column;
174
175 EXCEPTION
176
177 WHEN NO_DATA_FOUND THEN
178
179 -- No more messages return NULL;
180
181 return null;
182
183 END Get_Dependency_Col;
184
185 -- PROCEDURE Set_Dependency_Col
186 --
187
188 PROCEDURE Set_Dependency_Col
189 ( p_message IN NUMBER
190 ,p_column IN NUMBER
191 ,p_value IN VARCHAR2
192 )
193 IS
194 BEGIN
195 if (p_column = 1) then
196 G_dep_tbl(p_message).associated_column1 := p_value;
197 elsif (p_column = 2) then
198 G_dep_tbl(p_message).associated_column2 := p_value;
199 elsif (p_column = 3) then
200 G_dep_tbl(p_message).associated_column3 := p_value;
201 elsif (p_column = 4) then
202 G_dep_tbl(p_message).associated_column4 := p_value;
203 elsif (p_column = 5) then
204 G_dep_tbl(p_message).associated_column5 := p_value;
205 end if;
206 EXCEPTION
207
208 WHEN NO_DATA_FOUND THEN
209
210 null;
211
212 END Set_Dependency_Col;
213
214 -- Procedure Initialize
215 --
216 -- Usage Used by API callers and developers to intialize the
217 -- global message table.
218 -- Desc Clears the G_msg_tbl and resets all its global
219 -- variables. Except for the message level threshold.
220 --
221
222 PROCEDURE Initialize
223 IS
224 BEGIN
225
226 G_msg_tbl.DELETE;
227 G_msg_count := 0;
228 G_msg_index := 0;
229
230 G_dep_tbl.DELETE;
231 G_dep_count := 0;
232 G_dep_index := 0;
233
234 FOR I IN 1..G_max_cols LOOP
235 G_associated_column(I) := null;
236 END LOOP;
237
238 G_ser_msgname := null;
239 G_ser_msgdata := null;
240 G_ser_msgset := FALSE;
241 G_ser_msgapp := null;
242
243 END;
244
245 -- FUNCTION Count_Msg
246 --
247 -- Usage Used by API callers and developers to find the count
248 -- of messages in the message list.
249 -- Desc Returns the value of G_msg_count
250 --
251 -- Parameters None
252 --
253 -- Return NUMBER
254
255 FUNCTION Count_Msg RETURN NUMBER
256 IS
257 BEGIN
258
259 RETURN G_msg_Count;
260
261 END Count_Msg;
262
263 -- PROCEDURE Count_And_Get
264 --
265
266 PROCEDURE Count_And_Get
267 ( p_encoded IN VARCHAR2 := 'T' ,
268 p_count OUT NOCOPY NUMBER ,
269 p_data OUT NOCOPY VARCHAR2
270 )
271 IS
272 l_msg_count NUMBER;
273 BEGIN
274
275 l_msg_count := Count_Msg;
276
277 IF l_msg_count = 1 THEN
278
279 p_data := Get ( p_msg_index => G_FIRST ,
280 p_encoded => p_encoded );
281
282 END IF;
283
284 p_count := l_msg_count ;
285
286 END Count_And_Get;
287
288 -- PROCEDURE Add
289 --
290 -- Usage Used to add messages to the global message table.
291 --
292 -- Desc Reads a message off the message dictionary stack and
293 -- writes it in an encoded format to the global PL/SQL
294 -- message table.
295 -- The message is appended at the bottom of the message
296 -- table.
297 --
298
299 PROCEDURE Add
300 IS
301 BEGIN
302
303 -- Increment message count
304
305 G_msg_count := G_msg_count + 1;
306
307 -- Write message.
308
309 G_msg_tbl(G_msg_count).message_type := FND_MSG_PUB.G_ERROR_MSG;
310 G_msg_tbl(G_msg_count).encoded_message := FND_MESSAGE.GET_ENCODED;
311
312 FOR I IN 1..G_max_cols LOOP
313 G_associated_column(I) := null;
314 END LOOP;
315
316
317 END; -- Add
318
319 -- PROCEDURE Delete_Msg
320 --
321 -- Usage Used to delete a specific message from the message
322 -- list, or clear the whole message list.
323 --
324 -- Desc If instructed to delete a specific message, the
325 -- message is removed from the message table and the
326 -- table is compressed by moving the messages coming
327 -- after the deleted messages up one entry in the message
328 -- table.
329 -- If there is no entry found the Delete procedure does
330 -- nothing, and no exception is raised.
331 -- If delete is passed no parameters it deletes the whole
332 -- message table.
333 --
334 -- Prameters p_msg_index IN NUMBER := null Optional
335 -- holds the index of the message to be deleted.
336 --
337
338 PROCEDURE Delete_Msg
339 ( p_msg_index IN NUMBER := NULL
340 )
341 IS
342 l_var varchar2(62);
343 BEGIN
344
345 IF p_msg_index IS NULL THEN
346
347 -- Delete the whole table.
348
349 G_msg_tbl.DELETE;
350 G_msg_count := 0;
351 G_msg_index := 0;
352
353 ELSE
354
355 -- Check if entry exists
356
357 IF G_msg_tbl.EXISTS(p_msg_index) THEN
358
359 IF p_msg_index <= G_msg_count THEN
360
361 -- Move all messages up 1 entry.
362
363 FOR I IN p_msg_index..G_msg_count-1 LOOP
364
365 FOR J IN 1..G_max_cols LOOP
366 Set_Associated_Col(I,J,Get_Associated_Col(I + 1,J));
367 END LOOP;
368 G_msg_tbl( I ).message_type := G_msg_tbl( I + 1 ).message_type;
369 G_msg_tbl( I ).encoded_message := G_msg_tbl( I + 1 ).encoded_message;
370
371 END LOOP;
372
373 -- Delete the last message table entry.
374
375 G_msg_tbl.DELETE(G_msg_count) ;
376 G_msg_count := G_msg_count - 1 ;
377
378 END IF;
379
380 END IF;
381
382 END IF;
383
384 END Delete_Msg;
385
386 -- PROCEDURE Get
387 --
388
389 PROCEDURE Get
390 ( p_msg_index IN NUMBER := G_NEXT ,
391 p_encoded IN VARCHAR2 := 'T' ,
392 p_data OUT NOCOPY VARCHAR2 ,
393 p_msg_index_out OUT NOCOPY NUMBER
394 )
395 IS
396 l_msg_index NUMBER := G_msg_index;
397 BEGIN
398
399 IF p_msg_index = G_NEXT THEN
400 G_msg_index := G_msg_index + 1;
401 ELSIF p_msg_index = G_FIRST THEN
402 G_msg_index := 1;
403 ELSIF p_msg_index = G_PREVIOUS THEN
404 G_msg_index := G_msg_index - 1;
405 ELSIF p_msg_index = G_LAST THEN
406 G_msg_index := G_msg_count ;
407 ELSE
408 G_msg_index := p_msg_index ;
409 END IF;
410
411
412 IF FND_API.To_Boolean( p_encoded ) THEN
413
414 p_data := G_msg_tbl( G_msg_index ).encoded_message;
415
416 ELSE
417
418 FND_MESSAGE.SET_ENCODED ( G_msg_tbl( G_msg_index ).encoded_message );
419 p_data := FND_MESSAGE.GET;
420
421 END IF;
422
423 p_msg_index_out := G_msg_index ;
424
425 EXCEPTION
426
427 WHEN NO_DATA_FOUND THEN
428
429 -- No more messages, revert G_msg_index and return NULL;
430
431 G_msg_index := l_msg_index;
432
433 p_data := NULL;
434 p_msg_index_out := NULL;
435
436 END Get;
437
438 -- FUNCTION Get
439 --
440
441 FUNCTION Get
442 ( p_msg_index IN NUMBER := G_NEXT ,
443 p_encoded IN VARCHAR2 := 'T'
444 )
445 RETURN VARCHAR2
446 IS
447 l_data VARCHAR2(2000) ;
448 l_msg_index_out NUMBER ;
449 BEGIN
450
451 Get
452 ( p_msg_index ,
453 p_encoded ,
454 l_data ,
455 l_msg_index_out
456 );
457
458 RETURN l_data ;
459
460 END Get;
461
462 -- PROCEDURE Reset
463 --
464 -- Usage Used to reset the message table index used in reading
465 -- messages to point to the top of the message table or
466 -- the botom of the message table.
467 --
468 -- Desc Sets G_msg_index to 0 or G_msg_count+1 depending on
469 -- the reset mode.
470 --
471 -- Parameters p_mode IN NUMBER := G_FIRST Optional
472 -- possible values are :
473 -- G_FIRST resets index to the begining of msg tbl
474 -- G_LAST resets index to the end of msg tbl
475 --
476
477 PROCEDURE Reset ( p_mode IN NUMBER := G_FIRST )
478 IS
479 l_procedure_name CONSTANT VARCHAR2(15):='Reset';
480 BEGIN
481
482 IF p_mode = G_FIRST THEN
483
484 G_msg_index := 0;
485
489
486 ELSIF p_mode = G_LAST THEN
487
488 G_msg_index := G_msg_count + 1 ;
490 ELSE
491
492 -- Invalid mode.
493
494 FND_MSG_PUB.Add_Exc_Msg
495 ( p_pkg_name => G_PKG_NAME ,
496 p_procedure_name => l_procedure_name ,
497 p_error_text => 'Invalid p_mode: '||p_mode
498 );
499
500 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
501
502 END IF;
503
504 END Reset;
505
506 -- FUNCTION Check_Msg_Level
507 --
508 -- Usage Used by API developers to check if the level of the
509 -- message they want to write to the message table is
510 -- higher or equal to the message level threshold or not.
511 -- If the function returns TRUE the developer should go
512 -- ahead and write the message to the message table else
513 -- he/she should skip writing this message.
514 -- Desc Accepts a message level as input fetches the value of
515 -- the message threshold profile option and compares it
516 -- to the input level.
517 -- Return TRUE if the level is equal to or higher than the
518 -- threshold. Otherwise, it returns FALSE.
519 --
520
521 FUNCTION Check_Msg_Level
522 ( p_message_level IN NUMBER := G_MSG_LVL_SUCCESS
523 ) RETURN BOOLEAN
524 IS
525 BEGIN
526
527 IF G_msg_level_threshold = 9.99E125 THEN
528
529 -- Read the Profile option value.
530
531 G_msg_level_threshold :=
532 TO_NUMBER ( FND_PROFILE.VALUE('FND_AS_MSG_LEVEL_THRESHOLD') );
533
534 IF G_msg_level_threshold IS NULL THEN
535
536 G_msg_level_threshold := G_MSG_LVL_SUCCESS;
537
538 END IF;
539
540 END IF;
541
542 RETURN p_message_level >= G_msg_level_threshold ;
543
544 END; -- Check_Msg_Level
545
546 PROCEDURE Build_Exc_Msg
547 ( p_pkg_name IN VARCHAR2 :=null ,
548 p_procedure_name IN VARCHAR2 :=null ,
549 p_error_text IN VARCHAR2 :=null
550 )
551 IS
552 l_error_text VARCHAR2(240) := p_error_text ;
553 BEGIN
554
555 -- If p_error_text is missing use SQLERRM.
556
557 IF p_error_text is null THEN
558
559 l_error_text := SUBSTRB (SQLERRM , 1 , 240);
560
561 END IF;
562
563 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
564
565 IF p_pkg_name is not null and p_pkg_name <> chr(0) THEN
566 FND_MESSAGE.SET_TOKEN('PKG_NAME',p_pkg_name);
567 END IF;
568
569 IF p_procedure_name is not null and p_procedure_name <> chr(0) THEN
570 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME',p_procedure_name);
571 END IF;
572
573 IF l_error_text is not null and l_error_text <> chr(0) THEN
574 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_error_text);
575 END IF;
576
577 END; -- Build_Exc_Msg
578
579 PROCEDURE Add_Exc_Msg
580 ( p_pkg_name IN VARCHAR2 :=null ,
581 p_procedure_name IN VARCHAR2 :=null ,
582 p_error_text IN VARCHAR2 :=null
583 )
584 IS
585 BEGIN
586
587 Build_Exc_Msg
588 ( p_pkg_name ,
589 p_procedure_name ,
590 p_error_text
591 );
592
593 Add;
594
595 END Add_Exc_Msg ;
596
597 -- PROCEDURE Dump_Msg
598 --
599
600 PROCEDURE Dump_Msg
601 ( p_msg_index IN NUMBER )
602 IS
603 l_msg varchar2(2000);
604 BEGIN
605 l_msg := G_msg_tbl(p_msg_index).encoded_message;
606
607 /* Commented these out because dbms_output. put_line is illegal in our */
608 /* shipping code. If someone really needs to use this routine to debug */
609 /* a problem, the workaround is to copy this package, uncomment these lines*/
610 /* and apply that to the database in question. */
611
612 /* dbms_ou#tput.pu#t_line('Dumping Message number : '||p_msg_index); */
613
614 /* dbms_ou#tput.pu#t_line('DATA = '||replace(l_msg, chr(0), ' '));*/
615
616
617 END Dump_Msg;
618
619 -- PROCEDURE Dump_List
620 --
621
622 PROCEDURE Dump_List
623 ( p_messages IN BOOLEAN := FALSE
624 )
625 IS
626 BEGIN
627
628 /* Commented these out because dbms_output. put_line is illegal in our */
629 /* shipping code. If someone really needs to use this routine to debug */
630 /* a problem, the workaround is to copy this package, uncomment these lines*/
631 /* and apply that to the database in question. */
632
633 /* dbms_ou#tput.pu#t_line('Dumping Message List :');*/
634 /* dbms_ou#tput.pu#t_line('G_msg_tbl.COUNT = '||G_msg_tbl.COUNT);*/
635 /* dbms_ou#tput.pu#t_line('G_msg_count = '||G_msg_count);*/
636 /* dbms_ou#tput.pu#t_line('G_msg_index = '||G_msg_index);*/
637
638 IF p_messages THEN
639
640 FOR I IN 1..G_msg_tbl.COUNT LOOP
641
642 dump_Msg (I);
643
644 END LOOP;
645
646 END IF;
647
648 END Dump_List;
649
650 -- PROCEDURE Add_Detail
651 --
652
653 PROCEDURE Add_Detail
654 ( p_associated_column1 IN VARCHAR2 default null
658 ,p_associated_column5 IN VARCHAR2 default null
655 ,p_associated_column2 IN VARCHAR2 default null
656 ,p_associated_column3 IN VARCHAR2 default null
657 ,p_associated_column4 IN VARCHAR2 default null
659 ,p_same_associated_columns IN VARCHAR2 default 'F'
660 ,p_message_type IN VARCHAR2 default FND_MSG_PUB.G_ERROR_MSG
661 )
662 IS
663 l_procedure_name CONSTANT VARCHAR2(15):='Add_Detail';
664
665 BEGIN
666
667 -- validate message type
668 if p_message_type not in (g_error_msg,
669 g_warning_msg,
670 g_information_msg,
671 g_dependency_msg) then
672
673 -- Invalid mode.
674
675 FND_MSG_PUB.Add_Exc_Msg
676 ( p_pkg_name => G_PKG_NAME ,
677 p_procedure_name => l_procedure_name ,
678 p_error_text => 'Invalid p_message_type: '||p_message_type
679 );
680
681 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
682 end if;
683
684 -- store the associated column parameters in the global array
685 -- if the previously saved columns are not to be used
686
687 if p_same_associated_columns <> 'T' then
688 g_associated_column(1) := p_associated_column1;
689 g_associated_column(2) := p_associated_column2;
690 g_associated_column(3) := p_associated_column3;
691 g_associated_column(4) := p_associated_column4;
692 g_associated_column(5) := p_associated_column5;
693 end if;
694
695
696 -- Write message.
697
698 if p_message_type <> FND_MSG_PUB.G_DEPENDENCY_MSG then
699
700 G_msg_count := G_msg_count + 1;
701 G_msg_tbl(G_msg_count).message_type := p_message_type;
702 G_msg_tbl(G_msg_count).encoded_message := fnd_message.get_encoded;
703
704 if p_message_type = FND_MSG_PUB.G_ERROR_MSG then
705 FOR J IN 1..G_max_cols LOOP
706 Set_Associated_Col(G_msg_count,J,g_associated_column(J));
707 END LOOP;
708 end if;
709
710 else
711
712 G_dep_count := G_dep_count + 1;
713 G_dep_tbl(G_dep_count).message_type := FND_MSG_PUB.G_DEPENDENCY_MSG;
714 G_dep_tbl(G_dep_count).encoded_message := null;
715
716 FOR J IN 1..G_max_cols LOOP
717 Set_Dependency_Col(G_dep_count,J,g_associated_column(J));
718 END LOOP;
719
720 end if;
721
722 -- set g_associated_columns to null
723
724 FOR J IN 1..G_max_cols LOOP
725 g_associated_column(J) := null;
726 END LOOP;
727
728 END Add_Detail;
729
730
731 -- FUNCTION No_All_Inclusive_Error
732 --
733
734 FUNCTION No_All_Inclusive_Error
735 ( p_check_column1 IN VARCHAR2
736 ,p_check_column2 IN VARCHAR2 default null
737 ,p_check_column3 IN VARCHAR2 default null
738 ,p_check_column4 IN VARCHAR2 default null
739 ,p_check_column5 IN VARCHAR2 default null
740 ,p_associated_column1 IN VARCHAR2 default null
741 ,p_associated_column2 IN VARCHAR2 default null
742 ,p_associated_column3 IN VARCHAR2 default null
743 ,p_associated_column4 IN VARCHAR2 default null
744 ,p_associated_column5 IN VARCHAR2 default null
745 ) return varchar2
746 IS
747 l_check_column Col_Tbl_Type;
748 BEGIN
749
750 -- set up an array of check columns
751
752 l_check_column(1) := p_check_column1;
753 l_check_column(2) := p_check_column2;
754 l_check_column(3) := p_check_column3;
755 l_check_column(4) := p_check_column4;
756 l_check_column(5) := p_check_column5;
757
758 -- save associated columns to global array
759
760 g_associated_column(1) := p_associated_column1;
761 g_associated_column(2) := p_associated_column2;
762 g_associated_column(3) := p_associated_column3;
763 g_associated_column(4) := p_associated_column4;
764 g_associated_column(5) := p_associated_column5;
765
766 -- Checks done for p_check_column1 then p_check_column2 etc... since
767 -- we are more likely to find a match for p_check_column1
768
769 -- check p_check_column1 against all associated columns in message list
770
771 FOR J IN 1..G_max_cols LOOP
772 if l_check_column(J) is not null then
773
774 -- check columns against all associated columns in message list
775
776 FOR I IN 1..G_msg_tbl.COUNT LOOP
777 if G_msg_tbl(I).message_type = FND_MSG_PUB.G_ERROR_MSG then
778 FOR K IN 1..G_max_cols LOOP
779 if l_check_column(J) = Get_Associated_Col(I,K) then
780 -- Now add dummy messages to the dependency list
781 fnd_msg_pub.Add_Detail
782 (p_associated_column1 => p_associated_column1
783 ,p_associated_column2 => p_associated_column2
784 ,p_associated_column3 => p_associated_column3
785 ,p_associated_column4 => p_associated_column4
786 ,p_associated_column5 => p_associated_column5
787 ,p_same_associated_columns => 'F'
788 ,p_message_type => FND_MSG_PUB.G_DEPENDENCY_MSG
789 );
790 return 'F';
791 end if;
795
792 END LOOP;
793 end if;
794 END LOOP;
796 -- check columns against all associated columns in dependency list
797
798 FOR I IN 1..G_dep_tbl.COUNT LOOP
799 -- NOTE the associated columns can never take a null value
800 -- so no need to check for nulls
801 FOR K IN 1..G_max_cols LOOP
802 if l_check_column(J) = Get_Dependency_Col(I,K) then
803 -- Now add dummy messages to the dependency list
804 fnd_msg_pub.Add_Detail
805 (p_associated_column1 => p_associated_column1
806 ,p_associated_column2 => p_associated_column2
807 ,p_associated_column3 => p_associated_column3
808 ,p_associated_column4 => p_associated_column4
809 ,p_associated_column5 => p_associated_column5
810 ,p_same_associated_columns => 'F'
811 ,p_message_type => FND_MSG_PUB.G_DEPENDENCY_MSG
812 );
813 return 'F';
814 end if;
815 END LOOP;
816 END LOOP;
817 end if;
818 END LOOP;
819
820 -- no errors found matching check columns so return true
821
822 return 'T';
823
824 END No_All_Inclusive_Error;
825
826 -- FUNCTION Is_Exclusive_Msg_Error
827 -- evaluates if message has more than
828 -- one associated column
829
830 FUNCTION Is_Exclusive_Msg_Error ( p_message IN NUMBER
831 ) return boolean
832 IS
833 l_msg_count NUMBER := 0;
834 BEGIN
835
836 -- Checks done for p_check_column1 then p_check_column2 etc... since
837 -- we are more likely to find a match for p_check_column1
838
839 FOR K IN 1..G_max_cols LOOP
840 if Get_Associated_Col(p_message,K) is not null then
841 l_msg_count := l_msg_count + 1;
842 if l_msg_count > 1 then
843 return false;
844 end if;
845 end if;
846 END LOOP;
847
848 -- count is less than 2 so return true
849
850 return true;
851
852
853 END Is_Exclusive_Msg_Error;
854
855 -- FUNCTION Is_Exclusive_Dep_Error
856 -- evaluates if dependency message has more than
857 -- one associated column
858
859 FUNCTION Is_Exclusive_Dep_Error ( p_message IN NUMBER
860 ) return boolean
861 IS
862 l_msg_count NUMBER := 0;
863 BEGIN
864
865 -- Checks done for p_check_column1 then p_check_column2 etc... since
866 -- we are more likely to find a match for p_check_column1
867
868 FOR K IN 1..G_max_cols LOOP
869 if Get_Dependency_Col(p_message,K) is not null then
870 l_msg_count := l_msg_count + 1;
871 if l_msg_count > 1 then
872 return false;
873 end if;
874 end if;
875 END LOOP;
876
877 -- count is less than 2 so return true
878
879 return true;
880
881
882 END Is_Exclusive_Dep_Error;
883
884 -- FUNCTION No_Exclusive_Error
885 --
886
887 FUNCTION No_Exclusive_Error
888 ( p_check_column1 IN VARCHAR2
889 ,p_check_column2 IN VARCHAR2 default null
890 ,p_check_column3 IN VARCHAR2 default null
891 ,p_check_column4 IN VARCHAR2 default null
892 ,p_check_column5 IN VARCHAR2 default null
893 ,p_associated_column1 IN VARCHAR2 default null
894 ,p_associated_column2 IN VARCHAR2 default null
895 ,p_associated_column3 IN VARCHAR2 default null
896 ,p_associated_column4 IN VARCHAR2 default null
897 ,p_associated_column5 IN VARCHAR2 default null
898 ) return varchar2
899 IS
900 l_check_column Col_Tbl_Type;
901 BEGIN
902
903 -- set up an array of check columns
904
905 l_check_column(1) := p_check_column1;
906 l_check_column(2) := p_check_column2;
907 l_check_column(3) := p_check_column3;
908 l_check_column(4) := p_check_column4;
909 l_check_column(5) := p_check_column5;
910
911 -- save associated columns to global array
912
913 g_associated_column(1) := p_associated_column1;
914 g_associated_column(2) := p_associated_column2;
915 g_associated_column(3) := p_associated_column3;
916 g_associated_column(4) := p_associated_column4;
917 g_associated_column(5) := p_associated_column5;
918
919 -- Checks done for p_check_column1 then p_check_column2 etc... since
920 -- we are more likely to find a match for p_check_column1
921
922 -- check p_check_column1 against all associated columns in message list
923
924 FOR J IN 1..G_max_cols LOOP
925 if l_check_column(J) is not null then
926
927 -- check columns against all associated columns in message list
928 -- assume number of check columns is equal to the number of
929 -- associated columns
930
931 FOR I IN 1..G_msg_tbl.COUNT LOOP
932 if G_msg_tbl(I).message_type = FND_MSG_PUB.G_ERROR_MSG and
933 Is_Exclusive_Msg_Error(I) then
934 FOR K IN 1..G_max_cols LOOP
935 if l_check_column(J) = Get_Associated_Col(I,K) then
936 -- Now add dummy message to the dependency list
937 fnd_msg_pub.Add_Detail
938 (p_associated_column1 => p_associated_column1
939 ,p_associated_column2 => p_associated_column2
940 ,p_associated_column3 => p_associated_column3
944 ,p_message_type => FND_MSG_PUB.G_DEPENDENCY_MSG
941 ,p_associated_column4 => p_associated_column4
942 ,p_associated_column5 => p_associated_column5
943 ,p_same_associated_columns => 'F'
945 );
946 return 'F';
947 end if;
948 END LOOP;
949 end if;
950 END LOOP;
951
952 -- check columns against all associated columns in dependency list
953
954 FOR I IN 1..G_dep_tbl.COUNT LOOP
955 if Is_Exclusive_Dep_Error(I) then
956 FOR K IN 1..G_max_cols LOOP
957 if l_check_column(J) = Get_Dependency_Col(I,K) then
958 -- Now add dummy message to the dependency list
959 fnd_msg_pub.Add_Detail
960 (p_associated_column1 => p_associated_column1
961 ,p_associated_column2 => p_associated_column2
962 ,p_associated_column3 => p_associated_column3
963 ,p_associated_column4 => p_associated_column4
964 ,p_associated_column5 => p_associated_column5
965 ,p_same_associated_columns => 'F'
966 ,p_message_type => FND_MSG_PUB.G_DEPENDENCY_MSG
967 );
968 return 'F';
969 end if;
970 END LOOP;
971 end if;
972 END LOOP;
973 end if;
974 END LOOP;
975
976 -- no errors found matching check columns so return true
977
978 return 'T';
979
980 END No_Exclusive_Error;
981
982 -- FUNCTION No_Error_Message
983 --
984
985 FUNCTION No_Error_Message
986 ( p_check_message_name1 IN VARCHAR2
987 ,p_check_message_name2 IN VARCHAR2 default null
988 ,p_check_message_name3 IN VARCHAR2 default null
989 ,p_check_message_name4 IN VARCHAR2 default null
990 ,p_check_message_name5 IN VARCHAR2 default null
991 ,p_associated_column1 IN VARCHAR2 default null
992 ,p_associated_column2 IN VARCHAR2 default null
993 ,p_associated_column3 IN VARCHAR2 default null
994 ,p_associated_column4 IN VARCHAR2 default null
995 ,p_associated_column5 IN VARCHAR2 default null
996 ) return varchar2
997 IS
998
999 l_message_name varchar2(30);
1000 l_application varchar2(50);
1001
1002 BEGIN
1003
1004 -- save associated columns to global array
1005
1006 g_associated_column(1) := p_associated_column1;
1007 g_associated_column(2) := p_associated_column2;
1008 g_associated_column(3) := p_associated_column3;
1009 g_associated_column(4) := p_associated_column4;
1010 g_associated_column(5) := p_associated_column5;
1011
1012 FOR I IN 1..G_msg_tbl.COUNT LOOP
1013 -- only check error messages
1014 if G_msg_tbl(I).message_type = FND_MSG_PUB.G_ERROR_MSG then
1015
1016 -- get the message name from the encoded message
1017
1018 fnd_message.parse_encoded
1019 (encoded_message => G_msg_tbl(I).encoded_message
1020 ,app_short_name => l_application
1021 ,message_name => l_message_name);
1022
1023 if l_message_name = p_check_message_name1 or
1024 l_message_name = p_check_message_name2 or
1025 l_message_name = p_check_message_name3 or
1026 l_message_name = p_check_message_name4 or
1027 l_message_name = p_check_message_name5 then
1028
1029 fnd_msg_pub.Add_Detail
1030 (p_associated_column1 => p_associated_column1
1031 ,p_associated_column2 => p_associated_column2
1032 ,p_associated_column3 => p_associated_column3
1033 ,p_associated_column4 => p_associated_column4
1034 ,p_associated_column5 => p_associated_column5
1035 ,p_same_associated_columns => 'F'
1036 ,p_message_type => FND_MSG_PUB.G_DEPENDENCY_MSG
1037 );
1038 return 'F';
1039
1040 end if;
1041
1042 end if;
1043 END LOOP;
1044
1045 -- no errors found matching so return true
1046
1047 return 'T';
1048
1049 END No_Error_Message;
1050
1051 -- PROCEDURE Set_Search_Name
1052 --
1053
1054 PROCEDURE Set_Search_Name
1055 ( p_application IN VARCHAR2
1056 ,p_message_name IN VARCHAR2
1057 )
1058 IS
1059 BEGIN
1060
1061 G_ser_msgname := p_message_name;
1062 G_ser_msgdata := '';
1063 G_ser_msgset := TRUE;
1064 G_ser_msgapp := p_application;
1065
1066 END Set_Search_Name;
1067
1068 -- PROCEDURE Set_Search_Token
1069 --
1070
1071 PROCEDURE Set_Search_Token
1072 ( p_token in varchar2
1073 ,p_value in varchar2
1074 ,p_translate in boolean default false
1075 )
1076 IS
1077 l_flag varchar2(1);
1078 BEGIN
1079
1080 if p_translate then
1081 l_flag := 'Y';
1082 else
1083 l_flag := 'N';
1084 end if;
1088
1085 /* Note that we are intentionally using chr(0) rather than */
1086 /* FND_GLOBAL.LOCAL_CHR() for a performance bug (982909) */
1087 G_ser_msgdata := G_ser_msgdata||l_flag||chr(0)||p_token||chr(0)||p_value||chr(0);
1089 END Set_Search_Token;
1090
1091 -- FUNCTION Match_Msg
1092 --
1093 -- Usage: Details of the message to search for must first
1094 -- be set by calling Set_Search_Message and
1095 -- optionally calling Set_Search_Token. This
1096 -- function will return 0 if the message
1097 -- is not found or the message number if it was found
1098 --
1099 --
1100 --
1101
1102 FUNCTION Match_Msg return number
1103 IS
1104
1105 l_token Msg_Token_Tbl;
1106 l_search_token Msg_Token_Tbl;
1107 l_count number;
1108 l_message_name varchar2(30);
1109 l_application varchar2(50);
1110 l_start_position number;
1111 l_end_position number;
1112 l_length number;
1113
1114 BEGIN
1115
1116 -- first tokenize search tokens
1117 l_start_position := 3;
1118 l_count := 1;
1119 l_length := length(G_ser_msgdata);
1120
1121 while l_start_position < l_length loop
1122
1123 -- get token name and value from search string
1124
1125 l_end_position := instr(G_ser_msgdata,chr(0),l_start_position);
1126 l_search_token(l_count).name := substrb(G_ser_msgdata
1127 ,l_start_position
1128 ,l_end_position-l_start_position);
1129
1130 -- now get token value
1131 -- first encode the search message so we can call get_token
1132
1133 fnd_message.set_encoded(G_ser_msgapp||chr(0)||G_ser_msgname||chr(0)||G_ser_msgdata);
1134 l_search_token(l_count).value := fnd_message.get_token(l_search_token(l_count).name);
1135 fnd_message.clear;
1136
1137 l_count := l_count + 1;
1138
1139 -- set start position to begining of next token
1140 l_start_position := instr(G_ser_msgdata,chr(0),l_end_position+1)+3;
1141
1142 end loop;
1143
1144
1145 -- Loop through all messages
1146
1147 for I in 1..G_msg_tbl.count loop
1148
1149 -- get message name and app from encoded message
1150
1151 fnd_message.parse_encoded
1152 (encoded_message => G_msg_tbl(I).encoded_message
1153 ,app_short_name => l_application
1154 ,message_name => l_message_name);
1155
1156 -- check if message name/app matches
1157
1158 if l_application = G_ser_msgapp and l_message_name = G_ser_msgname then
1159
1160 -- check if any search tokens have been set - if so
1161 -- tokenize name/value pairs and see if there's a match
1162 -- check if message tokens match all those set in G_ser_msgdata
1163
1164 -- first tokenize message tokens
1165 -- place start postion at begining of first token name
1166 l_start_position := instr(G_msg_tbl(I).encoded_message,chr(0),1,2) + 3;
1167 l_count := 1;
1168 l_length := length(G_msg_tbl(I).encoded_message);
1169
1170 -- set the encoded message
1171 fnd_message.set_encoded(G_msg_tbl(I).encoded_message);
1172
1173 while l_start_position < l_length loop
1174
1175 -- get token name and value from encoded message
1176
1177 l_end_position := instr(G_msg_tbl(I).encoded_message,chr(0),l_start_position);
1178 l_token(l_count).name := substrb(G_msg_tbl(I).encoded_message
1179 ,l_start_position
1180 ,l_end_position-l_start_position);
1181
1182 -- now get token value
1183 l_token(l_count).value := fnd_message.get_token(l_token(l_count).name);
1184
1185 l_count := l_count + 1;
1186
1187 -- set start position to begining of next token
1188 l_start_position := instr(G_msg_tbl(I).encoded_message,chr(0),l_end_position+1)+3;
1189
1190 end loop;
1191
1192 -- now check if the search tokens match
1193 l_count := 0;
1194 for J in 1..l_search_token.count loop
1195 for K in 1..l_token.count loop
1196 if l_token(K).name = l_search_token(J).name and
1197 l_token(K).value = l_search_token(J).value then
1198 l_count := l_count + 1;
1199 end if;
1200 end loop;
1201 end loop;
1202
1203 -- check if all the search tokens matched
1204 -- if a match is found for all the search tokens that were set
1205 -- or if no search tokens were set then
1206 -- the message will be deleted
1207 if l_count = l_search_token.count or l_search_token.count = 0 then
1208 return I;
1209 end if;
1210
1211 end if;
1212 -- clear the message before moving to the next
1213 fnd_message.clear;
1214
1215 end loop;
1216
1217 -- no match found, therefore, return false
1218 -- reset search globals
1219
1220 return 0;
1221
1222 END Match_Msg;
1223
1224 -- FUNCTION Delete_Msg
1225 --
1229 -- message but not the position in the list.
1226 -- Usage: Provide similar functionality to the existing
1227 -- Delete_Msg procedure, except allows removal of
1228 -- a message when the caller knows the name of the
1230 -- Details of the message to search for must first
1231 -- be set by calling Set_Search_Message and
1232 -- optionally calling Set_Search_Token. This
1233 -- function will return 'T' for TRUE or 'F' for
1234 -- FALSE to indicate if the specified message had
1235 -- actually been found and removed.
1236 --
1237 --
1238 --
1239
1240 FUNCTION Delete_Msg return varchar2
1241 IS
1242
1243 l_message_number number;
1244
1245 BEGIN
1246
1247 -- return if message has not been set
1248 if G_ser_msgset = FALSE then
1249 return 'F';
1250 end if;
1251
1252 -- Find the number of the matching message
1253 l_message_number := Match_Msg;
1254
1255 -- clear the globals
1256 G_ser_msgname := null;
1257 G_ser_msgdata := null;
1258 G_ser_msgset := FALSE;
1259 G_ser_msgapp := null;
1260
1261 if l_message_number <> 0 then
1262 Delete_Msg(l_message_number);
1263 return 'T';
1264 else
1265 return 'F';
1266 end if;
1267
1268
1269 END Delete_Msg;
1270
1271 -- FUNCTION Change_Msg
1272 --
1273 -- Usage: Allows an existing message in the Multiple
1274 -- Message list to be changed for a different
1275 -- message name. Provides a useful utility,
1276 -- so the caller does have to explicitly delete a
1277 -- message and create a new entry. Details of the
1278 -- message to search for must first be set by
1279 -- calling Set_Search_Message and optionally
1280 -- Set_Search_Token. Details of the replacement
1281 -- message must first be set by calling
1282 -- fnd_message.set_message and optionally
1283 -- fnd_message.set_token. This function will return
1284 -- 'T' for TRUE or 'F' for FALSE to indicate if the
1285 -- specified message had actually been found and
1286 -- changed. Any associated_column and error type
1287 -- details will not be altered. If the message to
1288 -- be replaced is not found in the list no error is
1289 -- raised.
1290 --
1291 --
1292
1293 FUNCTION Change_Msg return varchar2
1294 IS
1295
1296 l_save_new_message varchar2(2000);
1297 l_message_number number;
1298
1299 BEGIN
1300
1301 -- return if message has not been set
1302 if G_ser_msgset = FALSE then
1303 return 'F';
1304 end if;
1305
1306 -- save new message to local variable, return if not set
1307 l_save_new_message := fnd_message.get_encoded;
1308 if l_save_new_message = '' then
1309 return 'F';
1310 end if;
1311
1312 -- Find the number of the matching message
1313 l_message_number := Match_Msg;
1314
1315 -- clear the globals
1316 G_ser_msgname := null;
1317 G_ser_msgdata := null;
1318 G_ser_msgset := FALSE;
1319 G_ser_msgapp := null;
1320
1321 if l_message_number <> 0 then
1322 G_msg_tbl(l_message_number).encoded_message := l_save_new_message;
1323 fnd_message.clear;
1324 return 'T';
1325 else
1326 return 'F';
1327 end if;
1328
1329 END Change_Msg;
1330
1331 -- FUNCTION Get_Detail
1332 --
1333
1334 FUNCTION Get_Detail
1335 ( p_msg_index IN NUMBER := G_NEXT
1336 ,p_encoded IN VARCHAR2 := 'T'
1337 ) return varchar2
1338 IS
1339 l_msg_index NUMBER := G_msg_index;
1340 l_col_string VARCHAR2(2000) := '';
1341 l_separator VARCHAR2(1) :='';
1342 l_check boolean;
1343 BEGIN
1344
1345 IF p_msg_index = G_NEXT THEN
1346 G_msg_index := G_msg_index + 1;
1347 ELSIF p_msg_index = G_FIRST THEN
1348 G_msg_index := 1;
1349 ELSIF p_msg_index = G_PREVIOUS THEN
1350 G_msg_index := G_msg_index - 1;
1351 ELSIF p_msg_index = G_LAST THEN
1352 G_msg_index := G_msg_count ;
1353 ELSE
1354 G_msg_index := p_msg_index ;
1355 END IF;
1356
1357 -- generate a colon delimited string of associated columns
1358 for J IN 1..G_max_cols loop
1359 if Get_Associated_Col(G_msg_index,J) is not null then
1360 l_col_string := l_col_string || l_separator || Get_Associated_Col(G_msg_index,J);
1361 end if;
1362 if J = 1 then
1363 l_separator := ':';
1364 end if;
1365 end loop;
1366
1367 -- add the string as a token
1368
1369 FND_MESSAGE.SET_ENCODED ( G_msg_tbl( G_msg_index ).encoded_message );
1370 FND_MESSAGE.SET_TOKEN ( G_associated_cols_token_name, l_col_string );
1371
1372 -- add the message type as a token
1373
1374 FND_MESSAGE.SET_TOKEN ( G_message_type_token_name, G_msg_tbl( G_msg_index ).message_type );
1375
1376
1377 IF ( FND_API.To_Boolean( p_encoded ) ) THEN
1378
1379 return FND_MESSAGE.GET_ENCODED;
1380
1381 ELSE
1382
1383 return FND_MESSAGE.GET;
1384
1385 END IF;
1386
1387
1388 EXCEPTION
1389
1390 WHEN NO_DATA_FOUND THEN
1391
1392 -- No more messages, revert G_msg_index and return NULL;
1393
1394 G_msg_index := l_msg_index;
1395
1396 return null;
1397
1398 END Get_Detail;
1399
1400 END FND_MSG_PUB ;