[Home] [Help]
PACKAGE BODY: APPS.FND_NEW_MESSAGES_PKG
Source
1 package body FND_NEW_MESSAGES_PKG as
2 /* $Header: AFMDMSGB.pls 120.4.12000000.1 2007/01/18 13:20:55 appldev ship $ */
3
4 PROCEDURE CHECK_COMPATIBILITY is
5 sqlbuf VARCHAR2(1000);
6 v_catg VARCHAR2(10);
7 v_sev VARCHAR2(10);
8 v_log_sev NUMBER;
9
10 COL_NOT_FOUND EXCEPTION;
11
12 PRAGMA EXCEPTION_INIT(COL_NOT_FOUND, -904);
13 BEGIN
14
15 sqlbuf := 'SELECT category, severity, fnd_log_severity
16 FROM fnd_new_messages
17 WHERE ROWNUM < 2';
18
19 begin
20
21 execute immediate sqlbuf into v_catg, v_sev, v_log_sev;
22
23 ADDN_COLS := 'Y';
24
25 exception
26 when COL_NOT_FOUND then
27 ADDN_COLS := 'N';
28 when NO_DATA_FOUND then
29 ADDN_COLS := 'Y';
30 end;
31
32 END CHECK_COMPATIBILITY;
33
34
35 procedure ADD_LANGUAGE
36 is
37 sql_string varchar2(6000);
38 begin
39
40 /***** Commented Update Statement
41
42 update FND_NEW_MESSAGES T set (
43 MESSAGE_TEXT
44 ) = (select
45 B.MESSAGE_TEXT
46 from FND_NEW_MESSAGES B
47 where B.APPLICATION_ID = T.APPLICATION_ID
48 and B.LANGUAGE_CODE = T.LANGUAGE_CODE
49 and B.MESSAGE_NAME = T.MESSAGE_NAME
50 and B.LANGUAGE = T.SOURCE_LANG)
51 where (
52 T.APPLICATION_ID,
53 T.LANGUAGE_CODE,
54 T.MESSAGE_NAME,
55 T.LANGUAGE
56 ) in (select
57 SUBT.APPLICATION_ID,
58 SUBT.LANGUAGE_CODE,
59 SUBT.MESSAGE_NAME,
60 SUBT.LANGUAGE
61 from FND_NEW_MESSAGES SUBB, FND_NEW_MESSAGES SUBT
62 where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
63 and SUBB.LANGUAGE_CODE = SUBT.LANGUAGE_CODE
64 and SUBB.MESSAGE_NAME = SUBT.MESSAGE_NAME
65 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
66 and (SUBB.MESSAGE_TEXT <> SUBT.MESSAGE_TEXT
67 ));
68
69 insert into FND_NEW_MESSAGES (
70 FND_LOG_SEVERITY,
71 APPLICATION_ID,
72 LANGUAGE_CODE,
73 MESSAGE_NUMBER,
74 MESSAGE_NAME,
75 MESSAGE_TEXT,
76 CREATION_DATE,
77 CREATED_BY,
78 LAST_UPDATE_DATE,
79 LAST_UPDATED_BY,
80 LAST_UPDATE_LOGIN,
81 DESCRIPTION,
82 TYPE,
83 MAX_LENGTH,
84 CATEGORY,
85 SEVERITY
86 ) select -- Dropped ORDERED hint here
87 B.FND_LOG_SEVERITY,
88 B.APPLICATION_ID,
89 L.LANGUAGE_CODE,
90 B.MESSAGE_NUMBER,
91 B.MESSAGE_NAME,
92 B.MESSAGE_TEXT,
93 B.CREATION_DATE,
94 B.CREATED_BY,
95 B.LAST_UPDATE_DATE,
96 B.LAST_UPDATED_BY,
97 B.LAST_UPDATE_LOGIN,
98 B.DESCRIPTION,
99 B.TYPE,
100 B.MAX_LENGTH,
101 B.CATEGORY,
102 B.SEVERITY
103 from FND_NEW_MESSAGES B, FND_LANGUAGES L
104 where L.INSTALLED_FLAG in ('I', 'B')
105 and B.LANGUAGE_CODE = userenv('LANG')
106 and not exists
107 (select NULL
108 from FND_NEW_MESSAGES T
109 where T.APPLICATION_ID = B.APPLICATION_ID
110 and T.LANGUAGE_CODE = L.LANGUAGE_CODE
111 and T.MESSAGE_NAME = B.MESSAGE_NAME);
112 ******************/
113
114 -- Above Insert Statement is commented and the below line code is written
115 -- which executes the insert statement according to which DB
116 -- (category/severity present or not present) is being used.
117 -- This is backward compatible for upgrades.
118
119 fnd_new_messages_pkg.check_compatibility;
120
121 sql_string := 'insert into FND_NEW_MESSAGES (
122 APPLICATION_ID,
123 LANGUAGE_CODE,
124 MESSAGE_NUMBER,
125 MESSAGE_NAME,
126 MESSAGE_TEXT,
127 CREATION_DATE,
128 CREATED_BY,
129 LAST_UPDATE_DATE,
130 LAST_UPDATED_BY,
131 LAST_UPDATE_LOGIN,
132 DESCRIPTION,
133 TYPE,
134 MAX_LENGTH ';
135
136 if (FND_NEW_MESSAGES_PKG.ADDN_COLS = 'Y') then
137 sql_string := sql_string || ',CATEGORY,
138 SEVERITY,
139 FND_LOG_SEVERITY ';
140 end if;
141
142 sql_string := sql_string ||
143 ') select /*+ ORDERED */
144 B.APPLICATION_ID,
145 L.LANGUAGE_CODE,
146 B.MESSAGE_NUMBER,
147 B.MESSAGE_NAME,
148 B.MESSAGE_TEXT,
149 B.CREATION_DATE,
150 B.CREATED_BY,
151 B.LAST_UPDATE_DATE,
152 B.LAST_UPDATED_BY,
153 B.LAST_UPDATE_LOGIN,
154 B.DESCRIPTION,
155 B.TYPE,
156 B.MAX_LENGTH ';
157
158 if (FND_NEW_MESSAGES_PKG.ADDN_COLS = 'Y') then
159 sql_string := sql_string ||
160 ', B.CATEGORY,
161 B.SEVERITY,
162 B.FND_LOG_SEVERITY ';
163 end if;
164
165 sql_string := sql_string ||
166 ' from FND_NEW_MESSAGES B, FND_LANGUAGES L
167 where L.INSTALLED_FLAG in (''I'', ''B'')
168 and B.LANGUAGE_CODE = ''' || userenv('LANG') || '''' ||
169 ' and not exists
170 (select NULL
171 from FND_NEW_MESSAGES T
172 where T.APPLICATION_ID = B.APPLICATION_ID
173 and T.LANGUAGE_CODE = L.LANGUAGE_CODE
174 and T.MESSAGE_NAME = B.MESSAGE_NAME)';
175
176 execute immediate sql_string;
177 end ADD_LANGUAGE;
178
179
180 /* Overloaded Version Below */
181 procedure LOAD_ROW (
182 X_APPLICATION_ID in NUMBER,
183 X_MESSAGE_NAME in VARCHAR2,
184 X_MESSAGE_NUMBER in VARCHAR2,
185 X_MESSAGE_TEXT in VARCHAR2,
186 X_DESCRIPTION in VARCHAR2,
187 X_TYPE in VARCHAR2,
188 X_MAX_LENGTH in VARCHAR2,
189 X_CATEGORY in VARCHAR2,
190 X_SEVERITY in VARCHAR2,
191 X_FND_LOG_SEVERITY in VARCHAR2,
192 X_OWNER in VARCHAR2,
193 X_CUSTOM_MODE in VARCHAR2
194 ) is
195 begin
196 fnd_new_messages_pkg.LOAD_ROW (
197 X_APPLICATION_ID => X_APPLICATION_ID,
198 X_MESSAGE_NAME => X_MESSAGE_NAME,
199 X_MESSAGE_NUMBER => X_MESSAGE_NUMBER,
200 X_MESSAGE_TEXT => X_MESSAGE_TEXT,
201 X_DESCRIPTION => X_DESCRIPTION,
202 X_TYPE => X_TYPE,
203 X_MAX_LENGTH => X_MAX_LENGTH,
204 X_CATEGORY => X_CATEGORY,
205 X_SEVERITY => X_SEVERITY,
206 X_FND_LOG_SEVERITY => X_FND_LOG_SEVERITY,
207 X_OWNER => X_OWNER,
208 X_CUSTOM_MODE => X_CUSTOM_MODE,
209 X_LAST_UPDATE_DATE => null
210 );
211 end LOAD_ROW;
212
213 /* Overloaded Version Above */
214 procedure LOAD_ROW (
215 X_APPLICATION_ID in NUMBER,
216 X_MESSAGE_NAME in VARCHAR2,
217 X_MESSAGE_NUMBER in VARCHAR2,
218 X_MESSAGE_TEXT in VARCHAR2,
219 X_DESCRIPTION in VARCHAR2,
220 X_TYPE in VARCHAR2,
221 X_MAX_LENGTH in VARCHAR2,
222 X_CATEGORY in VARCHAR2,
223 X_SEVERITY in VARCHAR2,
224 X_FND_LOG_SEVERITY in VARCHAR2,
225 X_OWNER in VARCHAR2,
226 X_CUSTOM_MODE in VARCHAR2,
227 X_LAST_UPDATE_DATE in VARCHAR2
228 ) is
229 f_luby number; -- entity owner in file
230 f_ludate date; -- entity update date in file
231 db_luby number; -- entity owner in db
232 db_ludate date; -- entity update date in db
233
234 app_id number := x_application_id;
235
236 sql_string varchar2(6000);
237
238 --bug3331476 modified and added variables for binds
239 --fnd_log_severity number := X_FND_LOG_SEVERITY;
240 --max_length number := X_MAX_LENGTH;
241 --message_number number := X_MESSAGE_NUMBER;
242
243 fnd_log_severity number;
244 max_length number;
245 message_number number;
246
247 msg_type varchar2(30);
248 category varchar2(10);
249 severity varchar2(10);
250
251 description varchar2(240);
252 begin
253 -- Translate owner to file_last_updated_by
254 f_luby := fnd_load_util.owner_id(x_owner);
255
256 -- Translate char last_update_date to date
257 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
258
259 --select application_id into app_id
260 --from fnd_application
261 --where application_short_name = X_APPLICATION_SHORT_NAME;
262
263 --if (X_MAX_LENGTH IS NULL) then
264 --max_length := NULL;
265 --end if;
266
267 --if (X_MESSAGE_NUMBER IS NULL) then
268 --message_number := NULL;
269 --end if;
270
271 --if (X_FND_LOG_SEVERITY is NULL) then
272 --fnd_log_severity := NULL;
273 --end if;
274
275 select decode(X_MESSAGE_NUMBER, fnd_load_util.null_value, null,
276 null, X_MESSAGE_NUMBER,
277 TO_NUMBER(X_MESSAGE_NUMBER)),
278 decode(X_TYPE, fnd_load_util.null_value, null,
279 null, X_TYPE,
280 X_TYPE),
281 decode(X_DESCRIPTION, fnd_load_util.null_value, null,
282 null, X_DESCRIPTION,
283 X_DESCRIPTION),
284 decode(X_MAX_LENGTH, fnd_load_util.null_value, null,
285 null, X_MAX_LENGTH,
286 TO_NUMBER(X_MAX_LENGTH)),
287 decode(X_CATEGORY, fnd_load_util.null_value, null,
288 null, X_CATEGORY,
289 X_CATEGORY),
290 decode(X_SEVERITY, fnd_load_util.null_value, null,
291 null, X_SEVERITY,
292 X_SEVERITY),
293 decode(X_FND_LOG_SEVERITY, fnd_load_util.null_value, null,
294 null, X_FND_LOG_SEVERITY,
295 TO_NUMBER(X_FND_LOG_SEVERITY))
296 into message_number,
297 msg_type,
298 description,
299 max_length,
300 category,
301 severity,
302 fnd_log_severity
303 from dual;
304
305 begin
306 select LAST_UPDATED_BY, LAST_UPDATE_DATE
307 into db_luby, db_ludate
308 from FND_NEW_MESSAGES
309 where application_id = app_id
310 and language_code = userenv('LANG')
311 and message_name = X_MESSAGE_NAME;
312
313 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
314 db_ludate, X_CUSTOM_MODE)) then
315 -- bug 2747318
316 -- According to which DB (category/severity present or not present)
317 -- is being used, the correct section will execute for update.
318 -- This is backward compatible for upgrades.
319
320 -- bug3331476 Modified the following update and insert sql
321 -- statements to use bind values instead of all concatenation.
322
323 -- bug 3562652 Removed language_code from update sql_string so
324 -- non-translatable values in all language rows will be updated.
325 -- Moved message_text to sql_string2 so message_text will be
326 -- updated for the current language only. NLS mode handles
327 -- updating message_text for translations.
328
329 sql_string := 'update fnd_new_messages set
330 message_number = :1,
331 description = :2,
332 type = :3,
333 max_length = :4,';
334
335 if (FND_NEW_MESSAGES_PKG.ADDN_COLS = 'Y') then
336
337 sql_string := sql_string ||
338 'category = :5,
339 severity = :6,
340 fnd_log_severity = :7,';
341
342 end if;
343
344 sql_string := sql_string ||
345 'last_updated_by = :8,
346 last_update_date = :9,
347 last_update_login = 0
348 where application_id = :10
349 and message_name = :11';
350
351 if (FND_NEW_MESSAGES_PKG.ADDN_COLS = 'Y') then
352
353 execute immediate sql_string USING message_number,
354 description, msg_type, max_length,
355 category, severity, fnd_log_severity,
356 f_luby, f_ludate, app_id, X_MESSAGE_NAME;
357
358 else
359 execute immediate sql_string USING message_number,
360 description, msg_type, max_length,
361 f_luby, f_ludate, app_id, X_MESSAGE_NAME;
362
363 end if;
364
365 -- bug 3562652 Added to handle TRANS attribute
366 sql_string := 'update fnd_new_messages set
367 message_text = :1
368 where application_id = :2
369 and message_name = :3
370 and language_code = ''' || userenv('LANG') || '''';
371
372 execute immediate sql_string USING X_MESSAGE_TEXT,
373 app_id, X_MESSAGE_NAME;
374 end if;
375 exception
376 when no_data_found then
377 -- bug 2747318
378 -- According to which DB (category/severity present or not present)
379 -- is being used, the correct section will execute for insertion.
380 -- This is backward compatible for upgrades.
381
382 sql_string := 'insert into fnd_new_messages
383 (application_id,
384 language_code,
385 message_number,
386 message_name,
387 message_text,
388 creation_date,
389 created_by,
390 last_update_date,
391 last_updated_by,
392 last_update_login,
393 description,
394 type,
395 max_length ';
396
397 if (FND_NEW_MESSAGES_PKG.ADDN_COLS = 'Y') then
398 sql_string := sql_string || ',category,
399 severity,
400 fnd_log_severity ';
401 end if;
402
403 sql_string := sql_string ||
404 ') values (
405 :1,
406 ''' || userenv('LANG') || ''', ' ||
407 ':2,
408 :3,
409 :4,
410 :5,
411 :6,
412 :7,
413 :8,
414 0,
415 :9,
416 :10,
417 :11';
418
419 if (FND_NEW_MESSAGES_PKG.ADDN_COLS = 'Y') then
420 sql_string := sql_string ||
421 ', :12,
422 :13,
423 :14) ';
424 else
425 sql_string := sql_string || ')';
426 end if;
427
428 if (FND_NEW_MESSAGES_PKG.ADDN_COLS = 'Y') then
429
430 execute immediate sql_string USING app_id, message_number,
431 X_MESSAGE_NAME, X_MESSAGE_TEXT,
432 f_ludate, f_luby, f_ludate, f_luby,
433 description, msg_type, max_length,
434 category, severity, fnd_log_severity;
435 else
436 execute immediate sql_string USING app_id, message_number,
437 X_MESSAGE_NAME, X_MESSAGE_TEXT,
438 f_ludate, f_luby, f_ludate, f_luby,
439 description, msg_type, max_length;
440 end if;
441 end;
442 end LOAD_ROW;
443
444 /* Overloaded Version Below */
445 procedure TRANSLATE_ROW (
446 X_APPLICATION_ID in NUMBER,
447 X_MESSAGE_NAME in VARCHAR2,
448 X_MESSAGE_TEXT in VARCHAR2,
449 X_OWNER in VARCHAR2,
450 X_CUSTOM_MODE in VARCHAR2
451 ) is
452 begin
453 fnd_new_messages_pkg.TRANSLATE_ROW (
454 X_APPLICATION_ID => X_APPLICATION_ID,
455 X_MESSAGE_NAME => X_MESSAGE_NAME,
456 X_MESSAGE_TEXT => X_MESSAGE_TEXT,
457 X_OWNER => X_OWNER,
458 X_CUSTOM_MODE => X_CUSTOM_MODE,
459 X_LAST_UPDATE_DATE => null
460 );
461 end TRANSLATE_ROW;
462
463
464 /* Overloaded Version Above */
465 procedure TRANSLATE_ROW (
466 X_APPLICATION_ID in NUMBER,
467 X_MESSAGE_NAME in VARCHAR2,
468 X_MESSAGE_TEXT in VARCHAR2,
469 X_OWNER in VARCHAR2,
470 X_CUSTOM_MODE in VARCHAR2,
471 X_LAST_UPDATE_DATE in VARCHAR2
472 ) is
473 f_luby number; -- entity owner in file
474 f_ludate date; -- entity update date in file
475 db_luby number; -- entity owner in db
476 db_ludate date; -- entity update date in db
477 app_id number := x_application_id;
478
479 sql_string varchar2(6000);
480 begin
481 -- Translate owner to file_last_updated_by
482 f_luby := fnd_load_util.owner_id(x_owner);
483
484 -- Translate char last_update_date to date
485 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
486
487 --select application_id into app_id
488 --from fnd_application
489 --where application_short_name = X_APPLICATION_SHORT_NAME;
490
491 begin
492 select LAST_UPDATED_BY, LAST_UPDATE_DATE
493 into db_luby, db_ludate
494 from FND_NEW_MESSAGES
495 where application_id = app_id
496 and language_code = userenv('LANG')
497 and message_name = X_MESSAGE_NAME;
498
499 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
500 db_ludate, X_CUSTOM_MODE)) then
501 update fnd_new_messages set
502 message_text = nvl(X_MESSAGE_TEXT, message_text),
503 last_updated_by = f_luby,
504 last_update_date = f_ludate,
505 last_update_login = 0
506 where application_id = app_id
507 and language_code = userenv('LANG')
508 and message_name = X_MESSAGE_NAME;
509 end if;
510 exception
511 when no_data_found then
512
513 -- According to which DB (category/severity present or not present)
514 -- is being used, the correct section will execute for insertion.
515 -- This is backward compatible for upgrades.
516
517 -- If row is not found during NLS mode, then just default the data from
518 -- any language, with a preference for US first, then Base,
519 -- then anything else
520
521 sql_string := 'insert into fnd_new_messages
522 (application_id,
523 language_code,
524 message_number,
525 message_name,
526 message_text,
527 creation_date,
528 created_by,
529 last_update_date,
530 last_updated_by,
531 last_update_login,
532 description,
533 type,
534 max_length ';
535
536 if (FND_NEW_MESSAGES_PKG.ADDN_COLS = 'Y') then
537 sql_string := sql_string || ',category,
538 severity,
539 fnd_log_severity ';
540 end if;
541
542 sql_string := sql_string ||
543 ') select
544 application_id,
545 ''' || userenv('LANG') || ''', ' ||
546 'message_number,
547 message_name,
548 :1,
549 :2,
550 :3,
551 :4,
552 :5,
553 0,
554 description,
555 type,
556 max_length';
557
558 if (FND_NEW_MESSAGES_PKG.ADDN_COLS = 'Y') then
559 sql_string := sql_string ||
560 ', category,
561 severity,
562 fnd_log_severity ';
563 end if;
564
565 sql_string := sql_string ||
566 ' from (select
567 application_id,
568 language_code,
569 message_number,
570 message_name,
571 message_text,
572 creation_date,
573 created_by,
574 last_update_date,
575 last_updated_by,
576 last_update_login,
577 description,
578 type,
579 max_length ';
580
581 if (FND_NEW_MESSAGES_PKG.ADDN_COLS = 'Y') then
582 sql_string := sql_string || ',category,
583 severity,
584 fnd_log_severity ';
585 end if;
586
587
588 sql_string := sql_string ||
589 ' from fnd_new_messages
590 where application_id = :6
591 and message_name = :7
592 order by decode(language_code, ''US'', 1,
593 (select L.language_code from fnd_languages L
594 where L.installed_flag = ''B''), 2,
595 3)
596 )
597 where rownum = 1';
598
599 execute immediate sql_string USING X_MESSAGE_TEXT,
600 f_ludate, f_luby, f_ludate, f_luby,
601 app_id, X_MESSAGE_NAME;
602
603 end;
604 end TRANSLATE_ROW;
605
606
607 procedure DELETE_ROW (
608 X_APPLICATION_ID in NUMBER,
609 X_LANGUAGE_CODE in VARCHAR2,
610 X_MESSAGE_NAME in VARCHAR2
611 ) is
612 begin
613
614 delete from FND_NEW_MESSAGES
615 where APPLICATION_ID = X_APPLICATION_ID
616 and LANGUAGE_CODE = X_LANGUAGE_CODE
617 and MESSAGE_NAME = X_MESSAGE_NAME;
618
619 if (sql%notfound) then
620 raise no_data_found;
621 end if;
622
623 end DELETE_ROW;
624
625 ---------------VALIDATION PROCEDURE BEGIN-----------------------
626
627
628 procedure CHECK_MESSAGE_TYPE (
629 X_MESSAGE_NAME in VARCHAR2,
630 X_TYPE in VARCHAR2
631 ) is
632 begin
633
634 -- ******************************
635 -- Check for invalid Types
636 -- ******************************
637
638 if X_TYPE is not NULL
639 and X_TYPE not in
640 ('ERROR', 'NOTE', 'HINT', 'TITLE',
641 '30_PCT_EXPANSION_PROMPT',
642 '50_PCT_EXPANSION_PROMPT',
643 '100_PCT_EXPANSION_PROMPT',
644 'MENU', 'TOKEN', 'OTHER') then
645 fnd_message.set_name('FND', 'AFDICT_VAL_INVALID_TYPE');
646 fnd_message.set_token('MESSAGE_NAME', X_MESSAGE_NAME);
647 fnd_message.set_token('TYPE', X_TYPE);
648 app_exception.raise_exception();
649 end if;
650
651 end CHECK_MESSAGE_TYPE;
652
653
654 procedure CHECK_MESSAGE_DESCRIPTION (
655 X_MESSAGE_NAME in VARCHAR2,
656 X_TYPE in VARCHAR2,
657 X_DESCRIPTION in VARCHAR2
658 ) is
659 begin
660
661 -- ******************************
662 -- Check for Descriptions against Types
663 -- ******************************
664
665 if X_TYPE in ('OTHER', 'TOKEN')
666 and X_DESCRIPTION is null then
667 fnd_message.set_name('FND', 'AFDICT_VAL_NEED_DESCR');
668 fnd_message.set_token('MESSAGE_NAME', X_MESSAGE_NAME);
669 fnd_message.set_token('TYPE', X_TYPE);
670 app_exception.raise_exception();
671 end if;
672
673 end CHECK_MESSAGE_DESCRIPTION;
674
675
676 procedure CHECK_MAX_LENGTH_TYPE (
677 X_MESSAGE_NAME in VARCHAR2,
678 X_TYPE in VARCHAR2,
679 X_MAX_LENGTH in NUMBER
680 ) is
681 begin
682
683 -- ******************************
684 -- Check for Max Length against Types
685 -- ******************************
686 if(X_TYPE not in ('ERROR', 'NOTE', 'TOKEN', 'OTHER')
687 and X_TYPE is not null
688 and X_MAX_LENGTH is not null) then
689 fnd_message.set_name('FND', 'AFDICT_VAL_MAX_LEN_NOTNULL');
690 fnd_message.set_token('MESSAGE_NAME', X_MESSAGE_NAME);
691 fnd_message.set_token('TYPE', X_TYPE);
692 app_exception.raise_exception();
693 end if;
694
695 end CHECK_MAX_LENGTH_TYPE;
696
697
698 /* OverLoaded */
699 procedure CHECK_MAX_LEN_MSG_LEN (
700 X_MESSAGE_NAME in VARCHAR2,
701 X_MESSAGE_TEXT in VARCHAR2,
702 X_MAX_LENGTH in NUMBER
703 ) is
704 begin
705 CHECK_MAX_LEN_MSG_LEN (
706 X_MESSAGE_NAME => X_MESSAGE_NAME,
707 X_MESSAGE_TEXT => X_MESSAGE_TEXT,
708 X_MAX_LENGTH => X_MAX_LENGTH,
709 X_VALIDATION => null);
710 end CHECK_MAX_LEN_MSG_LEN;
711
712
713 procedure CHECK_MAX_LEN_MSG_LEN (
714 X_MESSAGE_NAME in VARCHAR2,
715 X_MESSAGE_TEXT in VARCHAR2,
716 X_MAX_LENGTH in NUMBER,
717 X_VALIDATION in VARCHAR2
718 ) is
719
720 limit_length number:=0;
721 trans_length integer := 0;
722 actual_length number:=0;
723
724 begin
725
726 -- ******************************
727 -- Check whether message exceeds Max Length bytes
728 -- ******************************
729 if(X_MAX_LENGTH is not null) then
730 actual_length := lengthb(X_MESSAGE_TEXT);
731 limit_length := X_MAX_LENGTH;
732 trans_length := limit_length/1.3;
733 if (actual_length > limit_length) then
734 if ((X_VALIDATION is not null) and (X_VALIDATION='POST_TRANSLATE'))
735 or (X_VALIDATION is null) then
736 fnd_message.set_name('FND', 'AFDICT_VAL_MAXLEN_SMALL');
737 fnd_message.set_token('MESSAGE_NAME', X_MESSAGE_NAME);
738 fnd_message.set_token('MESSAGE_TEXT_LENGTH', actual_length);
739 fnd_message.set_token('MAXIMUM_LENGTH', limit_length);
740 app_exception.raise_exception();
741 end if;
742 end if;
743 if (actual_length > trans_length) then
744 if ((X_VALIDATION is not null) and (X_VALIDATION='STRICT'))
745 then
746 fnd_message.set_name('FND', 'AFDICT_VAL_MAXLEN_SMALL_TRN');
747 fnd_message.set_token('MESSAGE_NAME', X_MESSAGE_NAME);
748 fnd_message.set_token('MESSAGE_TEXT_LENGTH', actual_length);
749 fnd_message.set_token('MAXIMUM_LENGTH', limit_length);
750 fnd_message.set_token('TRANSLATED_MAXIMUM_LENGTH',
751 trans_length);
752 app_exception.raise_exception();
753 end if;
754 end if;
755 end if;
756
757 end CHECK_MAX_LEN_MSG_LEN;
758
759
760
761 procedure CHECK_TOKENS_ACCESS_KEYS (
762 X_MESSAGE_NAME in VARCHAR2,
763 X_MESSAGE_TEXT in VARCHAR2
764 ) is
765 line varchar2(2000);
766 token varchar2(2000);
767 i number:=0;
768 j number:=0;
769 l_start number:=0;
770 l_end number:=0;
771 punctuation varchar2(80);
772 begin
773
774
775 -- ******************************
776 -- Check for Tokens and Access Keys
777 -- ******************************
778
779 punctuation := fnd_global.newline||'`~!@#$%^*()-=+|][{}\";:,.<>/?''';
780 i:=99999;
781 j:=1;
782 while (i <> 0) loop
783 line := ltrim(rtrim(translate(X_MESSAGE_TEXT,
784 punctuation,' ')));
785
786 i := instr(line, '&', 1, j);
787
788 if i=0 then
789 exit;
790 end if;
791
792 if i=1 then
793 l_start:=1;
794 else
795 l_start := instr(substr(line,1,i), ' ', -1);
796 if l_start=0 then
797 l_start:=1;
798 end if;
799 end if;
800
801 l_end:=instr(substr(line,i), ' ', 1);
802
803 if l_end=0 then
804 l_end := length(line);
805 token := substr(line, l_start);
806 else
807 l_end := l_end + i;
808 token := substr(line, l_start, l_end-l_start);
809 end if;
810
811 token := ltrim(rtrim(token));
812
813 if (substr(token,1,1) <> '&') and (instr(token,'&&',1)=0) then
814 fnd_message.set_name('FND', 'AFDICT_VAL_SINGLE_AMP_MIDDLE');
815 fnd_message.set_token('MESSAGE_NAME', X_MESSAGE_NAME);
816 fnd_message.set_token('WORD', token);
817 app_exception.raise_exception();
818 end if;
819
820 if upper(token)=token and instr(token,'&&',1)>0 then
821 fnd_message.set_name('FND', 'AFDICT_VAL_DOUBLE_AMP_UPPER');
822 fnd_message.set_token('MESSAGE_NAME', X_MESSAGE_NAME);
823 fnd_message.set_token('WORD', token);
824 app_exception.raise_exception();
825 end if;
826
827 if upper(token)<>token and substr(token,1,1)='&'
828 and substr(token,2,1)<>'&' then
829 fnd_message.set_name('FND', 'AFDICT_VAL_SINGLE_AMP_MIXED');
830 fnd_message.set_token('MESSAGE_NAME', X_MESSAGE_NAME);
831 fnd_message.set_token('WORD', token);
832 app_exception.raise_exception();
833 end if;
834
835 if substr(line, i+1,1)='&' then
836 j := j + 2;
837 else
838 j := j + 1;
839 end if;
840
841 end loop;
842
843 end CHECK_TOKENS_ACCESS_KEYS;
844
845 /* OverLoaded */
846 procedure CHECK_TYPE_RULES (
847 X_MESSAGE_NAME in VARCHAR2,
848 X_TYPE in VARCHAR2,
849 X_MESSAGE_TEXT in VARCHAR2
850 ) is
851 begin
852 CHECK_TYPE_RULES (
853 X_MESSAGE_NAME => X_MESSAGE_NAME,
854 X_TYPE => X_TYPE,
855 X_MESSAGE_TEXT => X_MESSAGE_TEXT,
856 X_VALIDATION => null);
857 end CHECK_TYPE_RULES;
858
859
860 procedure CHECK_TYPE_RULES (
861 X_MESSAGE_NAME in VARCHAR2,
862 X_TYPE in VARCHAR2,
863 X_MESSAGE_TEXT in VARCHAR2,
864 X_VALIDATION in VARCHAR2
865 ) is
866 limit_length number:=0;
867 trans_length integer := 0;
868 actual_length number:=0;
869 trans_ratio number:=0;
870 begin
871
872 -- ******************************
873 -- Messages not following Type Rules
874 -- ******************************
875 trans_ratio:=1.3;
876 if X_TYPE in ('50_PCT_EXPANSION_PROMPT') then
877 trans_ratio := 1.5;
878 elsif X_TYPE in ('100_PCT_EXPANSION_PROMPT') then
879 trans_ratio := 2.0;
880 end if;
881
882 limit_length := 1800;
883 if X_TYPE in ('ERROR', 'NOTE', 'OTHER') then
884 limit_length := 1800;
885 elsif X_TYPE in ('HINT') then
886 limit_length := 250;
887 elsif X_TYPE in ('TITLE') then
888 limit_length := 80;
889 elsif X_TYPE in ('MENU') then
890 limit_length := 60;
891 elsif X_TYPE is NULL then
892 limit_length := 1800;
893 elsif X_TYPE in ('30_PCT_EXPANSION_PROMPT',
894 '50_PCT_EXPANSION_PROMPT', '100_PCT_EXPANSION_PROMPT') then
895 limit_length := 1800;
896
897 end if;
898
899 actual_length := lengthb(X_MESSAGE_TEXT);
900 trans_length := limit_length/trans_ratio;
901 if (actual_length > limit_length) then
902 if ((X_VALIDATION is not null) and (X_VALIDATION='POST_TRANSLATE'))
903 or (X_VALIDATION is null) then
904 fnd_message.set_name('FND', 'AFDICT_VAL_TYPELEN_SMALL');
905 fnd_message.set_token('MESSAGE_NAME', X_MESSAGE_NAME);
906 fnd_message.set_token('MESSAGE_TEXT_LENGTH', actual_length);
907 fnd_message.set_token('MAXIMUM_LENGTH', limit_length);
908 fnd_message.set_token('TYPE', X_TYPE);
909 app_exception.raise_exception();
910 end if;
911 end if;
912 if lengthb(X_MESSAGE_TEXT) > trans_length then
913 if ((X_VALIDATION is not null) and (X_VALIDATION='STRICT'))
914 then
915 fnd_message.set_name('FND', 'AFDICT_VAL_TYPELEN_SMALL_TRN');
916 fnd_message.set_token('MESSAGE_NAME', X_MESSAGE_NAME);
917 fnd_message.set_token('MESSAGE_TEXT_LENGTH', actual_length);
918 fnd_message.set_token('MAXIMUM_LENGTH', limit_length);
919 fnd_message.set_token('TRANSLATED_MAXIMUM_LENGTH', trans_length);
920 fnd_message.set_token('TYPE', X_TYPE);
921 app_exception.raise_exception();
922 end if;
923 end if;
924
925 end CHECK_TYPE_RULES;
926
927
928 procedure CHECK_MAXIMUM_LENGTH_RANGE (
929 X_MAX_LENGTH in NUMBER,
930 X_MESSAGE_NAME in VARCHAR2
931 ) is
932 begin
933
934 -- ******************************
935 -- Check for Max Length range
936 -- ******************************
937 if(X_MAX_LENGTH is not null
938 and (X_MAX_LENGTH < 10 or X_MAX_LENGTH > 1800)) then
939 fnd_message.set_name('FND', 'AFDICT_VAL_MAXLEN_RANGE');
940 fnd_message.set_token('MESSAGE_NAME', X_MESSAGE_NAME);
941 fnd_message.set_token('MAXIMUM_LENGTH', X_MAX_LENGTH);
942 fnd_message.set_token('MINIMUM', 10);
943 fnd_message.set_token('MAXIMUM', 1800);
944 app_exception.raise_exception();
945 end if;
946
947
948 end CHECK_MAXIMUM_LENGTH_RANGE;
949
950
951 procedure CHECK_CATEGORY_SEVERITY (
952 X_CATEGORY in VARCHAR2,
953 X_SEVERITY in VARCHAR2,
954 X_FND_LOG_SEVERITY in NUMBER,
955 X_MESSAGE_NAME in VARCHAR2
956 ) is
957 -- counters to determine checking of columns in POST-TRANS/STRICT check
958 count_category number:=0;
959 count_severity number:=0;
960 begin
961
962 -- bug 2747318
963 -- If no data is present for category or severity because no lookup_type
964 -- exist, then skip the validation; otherwise, continue on to check if
965 -- there are values present.
966 -- This is backward compatible for upgrades.
967 -- ******************************
968 -- Check for valid Category
969 -- ******************************
970 if (X_CATEGORY is not NULL) then
971 select count(*) into count_category from fnd_lookups
972 where lookup_type = 'FND_KBF_CATEGORY';
973 if ( count_category > 0 ) then
974 select count(*) into count_category from fnd_lookups
975 where lookup_type = 'FND_KBF_CATEGORY' AND
976 lookup_code = X_CATEGORY;
977 if ( count_category = 0 ) then
978 fnd_message.set_name('FND', 'AFDICT_CATEGORY_NOT_VALID');
979 fnd_message.set_token('MESSAGE_NAME', X_MESSAGE_NAME);
980 fnd_message.set_token('CATEGORY', X_CATEGORY);
981 app_exception.raise_exception();
982 end if;
983 end if;
984 end if;
985 -- ******************************
986 -- Check for valid Severity
987 -- ******************************
988 if (X_SEVERITY is not NULL) then
989 select count(*) into count_severity from fnd_lookups
990 where lookup_type = 'FND_KBF_SEVERITY';
991 if ( count_severity > 0 ) then
992 select count(*) into count_severity from fnd_lookups
993 where lookup_type = 'FND_KBF_SEVERITY' AND
994 lookup_code = X_SEVERITY;
995 if ( count_severity = 0 ) then
996 fnd_message.set_name('FND', 'AFDICT_SEVERITY_NOT_VALID');
997 fnd_message.set_token('MESSAGE_NAME', X_MESSAGE_NAME);
998 fnd_message.set_token('SEVERITY', X_SEVERITY);
999 app_exception.raise_exception();
1000 end if;
1001 end if;
1002 end if;
1003 -- ******************************
1004 -- Check for valid Fnd_Log_Severity
1005 -- ******************************
1006 if (X_FND_LOG_SEVERITY is not NULL) then
1007 select count(*) into count_severity from fnd_lookups
1008 where lookup_type = 'AFLOG_LEVELS';
1009 if ( count_severity > 0 ) then
1010 select count(*) into count_severity from fnd_lookups
1011 where lookup_type = 'AFLOG_LEVELS' AND
1012 lookup_code = X_FND_LOG_SEVERITY;
1013 if ( count_severity = 0 ) then
1014 fnd_message.set_name('FND', 'AFDICT_SEVERITY_NOT_VALID');
1015 fnd_message.set_token('MESSAGE_NAME', X_MESSAGE_NAME);
1016 fnd_message.set_token('SEVERITY', X_FND_LOG_SEVERITY);
1017 app_exception.raise_exception();
1018 end if;
1019 end if;
1020 end if;
1021
1022 end CHECK_CATEGORY_SEVERITY;
1023
1024 end FND_NEW_MESSAGES_PKG;