[Home] [Help]
PACKAGE BODY: APPS.FUN_RULE_VALIDATE_PKG
Source
1 PACKAGE BODY FUN_RULE_VALIDATE_PKG AS
2 /*$Header: FUNXTMRULGENVLB.pls 120.4 2006/02/22 10:51:00 ammishra noship $ */
3
4
5 /*---------------------
6 -- Local variables --
7 ---------------------*/
8 g_ex_invalid_param EXCEPTION;
9 l_owner_table_name VARCHAR2(30);
10 l_owner_table_id VARCHAR2(30);
11 l_text VARCHAR2(4000);
12 l_column_name VARCHAR2(240);
13
14 g_special_string CONSTANT VARCHAR2(4):= '%#@*';
15 G_LENGTH CONSTANT NUMBER := LENGTHB(g_special_string);
16
17 TYPE val_tab_type IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
18
19 ---------------------------------------------------
20 -- define the internal table that will cache values
21 ---------------------------------------------------
22
23 VAL_TAB VAL_TAB_TYPE; -- the table of values
24 TABLE_SIZE BINARY_INTEGER := 2048; -- the size of above tables
25
26 -----------------------------------------------------------------
27 -- Private procedures and functions used internally by validation
28 -- process.
29 -----------------------------------------------------------------
30
31 FUNCTION get_index (
32 p_val IN VARCHAR2
33 ) RETURN BINARY_INTEGER;
34
35 PROCEDURE put (
36 p_val IN VARCHAR2
37 );
38
39 FUNCTION search (
40 p_val IN VARCHAR2,
41 p_category IN VARCHAR2
42 ) RETURN BOOLEAN;
43
44
45 FUNCTION get_index (
46 p_val IN VARCHAR2
47 ) RETURN BINARY_INTEGER IS
48
49 l_table_index BINARY_INTEGER;
50 l_found BOOLEAN := FALSE;
51 l_hash_value NUMBER;
52
53 BEGIN
54
55 l_table_index := DBMS_UTILITY.get_hash_value(p_val, 1, TABLE_SIZE);
56
57 IF VAL_TAB.EXISTS(l_table_index) THEN
58 IF VAL_TAB(l_table_index) = p_val THEN
59 RETURN l_table_index;
60 ELSE
61 l_hash_value := l_table_index;
62 l_table_index := l_table_index + 1;
63 l_found := FALSE;
64
65 WHILE (l_table_index < TABLE_SIZE) AND (NOT l_found) LOOP
66 IF VAL_TAB.EXISTS(l_table_index) THEN
67 IF VAL_TAB(l_table_index) = p_val THEN
68 l_found := TRUE;
69 ELSE
70 l_table_index := l_table_index + 1;
71 END IF;
72 ELSE
73 RETURN TABLE_SIZE + 1;
74 END IF;
75 END LOOP;
76
77 IF NOT l_found THEN -- Didn't find any till the end
78 l_table_index := 1; -- Start from the beginning
79
80 WHILE (l_table_index < l_hash_value) AND (NOT l_found) LOOP
81 IF VAL_TAB.EXISTS(l_table_index) THEN
82 IF VAL_TAB(l_table_index) = p_val THEN
83 l_found := TRUE;
84 ELSE
85 l_table_index := l_table_index + 1;
86 END IF;
87 ELSE
88 RETURN TABLE_SIZE + 1;
89 END IF;
90 END LOOP;
91 END IF;
92
93 IF NOT l_found THEN
94 RETURN TABLE_SIZE + 1; -- Return a higher value
95 END IF;
96 END IF;
97 ELSE
98 RETURN TABLE_SIZE + 1;
99 END IF;
100
101 RETURN l_table_index;
102
103 EXCEPTION
104 WHEN OTHERS THEN -- The entry doesn't exists
105 RETURN TABLE_SIZE + 1;
106
107 END get_index;
108
109 PROCEDURE put (
110 p_val IN VARCHAR2
111 ) IS
112
113 l_table_index BINARY_INTEGER;
114 l_stored BOOLEAN := FALSE;
115 l_hash_value NUMBER;
116
117 BEGIN
118
119 l_table_index := DBMS_UTILITY.get_hash_value(p_val, 1, TABLE_SIZE);
120
121 IF VAL_TAB.EXISTS(l_table_index) THEN
122 IF VAL_TAB(l_table_index) <> p_val THEN --Collision
123 l_hash_value := l_table_index;
124 l_table_index := l_table_index + 1;
125
126 WHILE (l_table_index < TABLE_SIZE) AND (NOT l_stored) LOOP
127 IF VAL_TAB.EXISTS(l_table_index) THEN
128 IF VAL_TAB(l_table_index) <> p_val THEN
129 l_table_index := l_table_index + 1;
130 END IF;
131 ELSE
132 VAL_TAB(l_table_index) := p_val;
133 l_stored := TRUE;
134 END IF;
135 END LOOP;
136
137 IF NOT l_stored THEN --Didn't find any free bucket till the end
138 l_table_index := 1;
139
140 WHILE (l_table_index < l_hash_value) AND (NOT l_stored) LOOP
141 IF VAL_TAB.EXISTS(l_table_index) THEN
142 IF VAL_TAB(l_table_index) <> p_val THEN
143 l_table_index := l_table_index + 1;
144 END IF;
145 ELSE
146 VAL_TAB(l_table_index) := p_val;
147 l_stored := TRUE;
148 END IF;
149 END LOOP;
150 END IF;
151
152 END IF;
153 ELSE
154 VAL_TAB(l_table_index) := p_val;
155 END IF;
156
157 EXCEPTION
158 WHEN OTHERS THEN
159 NULL;
160
161 END put;
162
163 FUNCTION search (
164 p_val IN VARCHAR2,
165 p_category IN VARCHAR2
166 ) RETURN BOOLEAN IS
167
168 l_table_index BINARY_INTEGER;
169 l_return BOOLEAN;
170
171 l_dummy VARCHAR2(1);
172 l_position1 NUMBER;
173 l_position2 NUMBER;
174
175 l_lookup_table VARCHAR2(30);
176 l_lookup_type AR_LOOKUPS.lookup_type%TYPE;
177 l_lookup_code AR_LOOKUPS.lookup_code%TYPE;
178
179 BEGIN
180
181 -- search for the value
182 l_table_index := get_index(p_val || G_SPECIAL_STRING || p_category);
183
184 IF l_table_index < table_size THEN
185 l_return := TRUE;
186 ELSE
187
188 --Can't find the value in the table; look in the database
189 IF p_category = 'LOOKUP' THEN
190
191 l_position1 := INSTRB(p_val, G_SPECIAL_STRING, 1, 1);
192 l_lookup_table := SUBSTRB(p_val, 1, l_position1 - 1);
193 l_position2 := INSTRB(p_val, G_SPECIAL_STRING, 1, 2);
194 l_lookup_type := SUBSTRB(p_val, l_position1 + G_LENGTH,
195 l_position2 - l_position1 - G_LENGTH);
196 l_lookup_code := SUBSTRB(p_val, l_position2 + G_LENGTH);
197
198 IF UPPER(l_lookup_table) = 'FUN_LOOKUPS' THEN
199 BEGIN
200 SELECT 'Y' INTO l_dummy
201 FROM FUN_LOOKUPS
202 WHERE LOOKUP_TYPE = l_lookup_type
203 AND LOOKUP_CODE = l_lookup_code
204 AND (ENABLED_FLAG = 'Y' AND
205 TRUNC(SYSDATE) BETWEEN
206 TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND
207 TRUNC(NVL(END_DATE_ACTIVE,SYSDATE))
208 );
209
210 l_return := TRUE;
211 EXCEPTION
212 WHEN NO_DATA_FOUND THEN
213 l_return := FALSE;
214 END;
215 ELSIF UPPER(l_lookup_table) = 'SO_LOOKUPS' THEN
216 BEGIN
217 SELECT 'Y' INTO l_dummy
218 FROM SO_LOOKUPS
219 WHERE LOOKUP_TYPE = l_lookup_type
220 AND LOOKUP_CODE = l_lookup_code
221 AND (ENABLED_FLAG = 'Y' AND
222 TRUNC(SYSDATE) BETWEEN
223 TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND
224 TRUNC(NVL(END_DATE_ACTIVE,SYSDATE))
225 );
226
227 l_return := TRUE;
228 EXCEPTION
229 WHEN NO_DATA_FOUND THEN
230 l_return := FALSE;
231 END;
232 ELSIF UPPER(l_lookup_table) = 'OE_SHIP_METHODS_V' THEN
233 BEGIN
234 SELECT 'Y' INTO l_dummy
235 FROM OE_SHIP_METHODS_V
236 WHERE LOOKUP_TYPE = l_lookup_type
237 AND LOOKUP_CODE = l_lookup_code
238 AND (ENABLED_FLAG = 'Y' AND
239 TRUNC(SYSDATE) BETWEEN
240 TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND
241 TRUNC(NVL(END_DATE_ACTIVE,SYSDATE))
242 )
243 AND ROWNUM = 1;
244
245 l_return := TRUE;
246 EXCEPTION
247 WHEN NO_DATA_FOUND THEN
248 l_return := FALSE;
249 END;
250 ELSIF UPPER(l_lookup_table) = 'FND_LOOKUP_VALUES' THEN
251 BEGIN
252 SELECT 'Y' INTO l_dummy
253 FROM FND_LOOKUP_VALUES
254 WHERE LOOKUP_TYPE = l_lookup_type
255 AND LOOKUP_CODE = l_lookup_code
256 AND (ENABLED_FLAG = 'Y' AND
257 TRUNC(SYSDATE) BETWEEN
258 TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND
259 TRUNC(NVL(END_DATE_ACTIVE,SYSDATE))
260 )
261 AND ROWNUM = 1;
262
263 l_return := TRUE;
264 EXCEPTION
265 WHEN NO_DATA_FOUND THEN
266 l_return := FALSE;
267 END;
268 ELSE
269 l_return := FALSE;
270 END IF;
271 END IF;
272
273 --Cache the value
274 IF l_return THEN
275 put(p_val || G_SPECIAL_STRING || p_category);
276 END IF;
277 END IF;
278 RETURN l_return;
279
280 END search;
281
282 procedure check_mandatory_str_col
283 -- Control mandatory column for varchar2 type
284 -- create update flag belongs to [C (creation) ,U (update)]
285 -- Column name
286 -- Column Value
287 -- Allow Null in creation mode flag
288 -- Allow Null in update mode flag
289 -- Control Status
290 ( create_update_flag IN VARCHAR2,
291 p_col_name IN VARCHAR2,
292 p_col_val IN VARCHAR2,
293 p_miss_allowed_in_c IN BOOLEAN,
294 p_miss_allowed_in_u IN BOOLEAN,
295 x_return_status IN OUT NOCOPY VARCHAR2)
296 IS
297 BEGIN
298 IF (p_col_val = FND_API.G_MISS_CHAR) THEN
299 fnd_message.set_name('FUN', 'FUN_RULE_API_MISSING_COLUMN');
300 fnd_message.set_token('COLUMN', p_col_name);
301 fnd_msg_pub.add;
302 x_return_status := fnd_api.g_ret_sts_error;
303 RETURN;
304 END IF;
305
306 IF (create_update_flag = 'C') THEN
307 IF ((NOT p_miss_allowed_in_c) AND
308 p_col_val IS NULL )
309 THEN
310 fnd_message.set_name('FUN', 'FUN_RULE_API_MISSING_COLUMN');
311 fnd_message.set_token('COLUMN', p_col_name);
312 fnd_msg_pub.add;
313 x_return_status := fnd_api.g_ret_sts_error;
314 END IF;
315 ELSE
316 IF ((NOT p_miss_allowed_in_u) AND
317 p_col_val IS NULL )
318 THEN
319 fnd_message.set_name('FUN', 'FUN_RULE_API_MISSING_COLUMN');
320 fnd_message.set_token('COLUMN', p_col_name);
321 fnd_msg_pub.add;
322 x_return_status := fnd_api.g_ret_sts_error;
323 END IF;
324 END IF;
325 END check_mandatory_str_col;
326
327
328 procedure check_mandatory_date_col
329 -- Control mandatory column for date type
330 -- create update flag belongs to [C (creation) ,U (update)]
331 -- Column name
332 -- Column Value
333 -- Allow Null in creation mode flag
334 -- Allow Null in update mode flag
335 -- Control Status
336 ( create_update_flag IN VARCHAR2,
337 p_col_name IN VARCHAR2,
338 p_col_val IN DATE,
339 p_miss_allowed_in_c IN BOOLEAN,
340 p_miss_allowed_in_u IN BOOLEAN,
341 x_return_status IN OUT NOCOPY VARCHAR2)
342 IS
343 BEGIN
344 IF (p_col_val = FND_API.G_MISS_DATE) THEN
345 fnd_message.set_name('FUN', 'FUN_RULE_API_MISSING_COLUMN');
346 fnd_message.set_token('COLUMN', p_col_name);
347 fnd_msg_pub.add;
348 x_return_status := fnd_api.g_ret_sts_error;
349 RETURN;
350 END IF;
351
352 IF (create_update_flag = 'C') THEN
353 IF ((NOT p_miss_allowed_in_c) AND
354 p_col_val IS NULL )
355 THEN
356 fnd_message.set_name('FUN', 'FUN_RULE_API_MISSING_COLUMN');
357 fnd_message.set_token('COLUMN', p_col_name);
358 fnd_msg_pub.add;
359 x_return_status := fnd_api.g_ret_sts_error;
360 END IF;
361 ELSE
362 IF ((NOT p_miss_allowed_in_u) AND
363 p_col_val IS NULL )
364 THEN
365 fnd_message.set_name('FUN', 'FUN_RULE_API_MISSING_COLUMN');
366 fnd_message.set_token('COLUMN', p_col_name);
367 fnd_msg_pub.add;
368 x_return_status := fnd_api.g_ret_sts_error;
369 END IF;
370 END IF;
371 END check_mandatory_date_col;
372
373
374 procedure check_mandatory_num_col
375 -- Control mandatory column for number type
376 -- create update flag belongs to [C (creation) ,U (update)]
377 -- Column name
378 -- Column Value
379 -- Allow Null in creation mode flag
380 -- Allow Null in update mode flag
381 -- Control Status
382 ( create_update_flag IN VARCHAR2,
383 p_col_name IN VARCHAR2,
384 p_col_val IN NUMBER,
385 p_miss_allowed_in_c IN BOOLEAN,
386 p_miss_allowed_in_u IN BOOLEAN,
387 x_return_status IN OUT NOCOPY VARCHAR2)
388 IS
389 BEGIN
390 IF (p_col_val = FND_API.G_MISS_NUM) THEN
391 fnd_message.set_name('FUN', 'FUN_RULE_API_MISSING_COLUMN');
392 fnd_message.set_token('COLUMN', p_col_name);
393 fnd_msg_pub.add;
394 x_return_status := fnd_api.g_ret_sts_error;
395 RETURN;
396 END IF;
397
398 IF (create_update_flag = 'C') THEN
399 IF ((NOT p_miss_allowed_in_c) AND
400 p_col_val IS NULL )
401 THEN
402 fnd_message.set_name('FUN', 'FUN_RULE_API_MISSING_COLUMN');
403 fnd_message.set_token('COLUMN', p_col_name);
404 fnd_msg_pub.add;
405 x_return_status := fnd_api.g_ret_sts_error;
406 END IF;
407 ELSE
408 IF ((NOT p_miss_allowed_in_u) AND
409 p_col_val IS NULL )
410 THEN
411 fnd_message.set_name('FUN', 'FUN_RULE_API_MISSING_COLUMN');
412 fnd_message.set_token('COLUMN', p_col_name);
413 fnd_msg_pub.add;
414 x_return_status := fnd_api.g_ret_sts_error;
415 END IF;
416 END IF;
417 END check_mandatory_num_col;
418
419
420 FUNCTION compare(
421 date1 DATE,
422 date2 DATE) RETURN NUMBER
423 IS
424 ldate1 date;
425 ldate2 date;
426 BEGIN
427 ldate1 := date1;
428 ldate2 := date2;
429 IF ((ldate1 IS NULL OR ldate1 = FND_API.G_MISS_DATE) AND (ldate2 IS NULL OR ldate2 = FND_API.G_MISS_DATE)) THEN
430 RETURN 0;
431 ELSIF (ldate2 IS NULL OR ldate2 = FND_API.G_MISS_DATE) THEN
432 RETURN -1;
433 ELSIF (ldate1 IS NULL OR ldate1 = FND_API.G_MISS_DATE) THEN
434 RETURN 1;
435 ELSIF ( ldate1 = ldate2 ) THEN
436 RETURN 0;
437 ELSIF ( ldate1 > ldate2 ) THEN
438 RETURN 1;
439 ELSE
440 RETURN -1;
441 END IF;
442 END compare;
443
444
445 FUNCTION is_between
446 ( datex DATE,
447 date1 DATE,
448 date2 DATE) RETURN BOOLEAN
449 IS
450 BEGIN
451 IF compare(datex, date1) >= 0 AND
452 compare(date2, datex) >=0 THEN
453 RETURN TRUE;
454 ELSE
455 RETURN FALSE;
456 END IF;
457 END is_between;
458
459
460 FUNCTION is_overlap
461 -- Returns 'Y' if period [s1,e1] overlaps [s2,e2]
462 -- 'N' otherwise
463 -- NULL indicates infinite for END dates
464 (s1 DATE,
465 e1 DATE,
466 s2 DATE,
467 e2 DATE)
468 RETURN VARCHAR2
469 IS
470 BEGIN
471 IF ( is_between(s1, s2, e2) ) OR ( is_between(s2, s1, e1) ) THEN
472 RETURN 'Y';
473 ELSE
474 RETURN 'N';
475 END IF;
476 END is_overlap;
477
478
479 PROCEDURE validate_fnd_lookup
480 ( p_lookup_type IN VARCHAR2,
481 p_column IN VARCHAR2,
482 p_column_value IN VARCHAR2,
483 x_return_status IN OUT NOCOPY VARCHAR2)
484 IS
485 CURSOR c1
486 IS
487 SELECT 'Y'
488 FROM fnd_lookup_values
489 WHERE lookup_type = p_lookup_type
490 AND lookup_code = p_column_value
491 AND ROWNUM = 1;
492
493 l_exist VARCHAR2(1);
494 BEGIN
495 IF ( p_column_value IS NOT NULL
496 AND p_column_value <> fnd_api.g_miss_char ) THEN
497 OPEN c1;
498 FETCH c1 INTO l_exist;
499 IF c1%NOTFOUND THEN
500 fnd_message.set_name('FUN','FUN_RULE_API_INVALID_LOOKUP');
501 fnd_message.set_token('COLUMN',p_column);
502 fnd_message.set_token('LOOKUP_TYPE',p_lookup_type);
503 fnd_msg_pub.add;
504 x_return_status := fnd_api.g_ret_sts_error;
505 END IF;
506 CLOSE c1;
507 END IF;
508 END validate_fnd_lookup;
509
510
511 /*--------------------------------------------------------
512 -- Function usable in any validation entities sections -
513 --------------------------------------------------------*/
514
515 PROCEDURE check_existence_rules_object
516 (p_rule_object_name IN VARCHAR2,
517 p_application_id IN NUMBER,
518 x_return_status IN OUT NOCOPY VARCHAR2)
519 IS
520 CURSOR c_exist_rules_object(p_rule_object_name IN VARCHAR2,p_application_id IN NUMBER )
521 IS
522 SELECT 'Y'
523 FROM FUN_RULE_OBJECTS_B
524 WHERE RULE_OBJECT_NAME = p_rule_object_name
525 AND APPLICATION_ID = p_application_id
526 AND ROWNUM = 1;
527 l_exist VARCHAR2(1);
528 BEGIN
529 OPEN c_exist_rules_object(p_rule_object_name,p_application_id);
530 FETCH c_exist_rules_object INTO l_exist;
531 IF c_exist_rules_object%NOTFOUND THEN
532 fnd_message.set_name('FUN','FUN_RULE_API_INVALID_FK');
533 fnd_message.set_token('FK','CUSTOM_OBJECT_NAME');
534 fnd_message.set_token('COLUMN','CUSTOM_OBJECT_NAME');
535 fnd_message.set_token('TABLE','FUN_RULES_OBJECTS');
536 fnd_msg_pub.add;
537 x_return_status := fnd_api.g_ret_sts_error;
538 END IF;
539 CLOSE c_exist_rules_object;
540 END check_existence_rules_object;
541
542
543 procedure check_err(
544 x_return_status IN VARCHAR2
545 ) IS
546 BEGIN
547 IF x_return_status = fnd_api.g_ret_sts_error
548 THEN
549 RAISE g_ex_invalid_param;
550 END IF;
551 END;
552
553 /**
554 * PROCEDURE validate_rule_objects
555 *
556 * DESCRIPTION
557 * Validates rule_object record. Checks for
558 * uniqueness
559 * lookup types
560 * mandatory columns
561 * non-updateable fields
562 * foreign key validations
563 * other validations
564 *
565 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
566 *
567 * ARGUMENTS
568 * IN:
569 * p_create_update_flag Create update flag. 'C' = create. 'U' = update.
570 * p_location_rec Location record.
571 * p_rowid Rowid of the record (used only in update mode).
572 * IN/OUT:
573 * x_return_status Return status after the call. The status can
574 * be FND_API.G_RET_STS_SUCCESS (success),
575 * fnd_api.g_ret_sts_error (error),
576 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
577 *
578 * NOTES
579 *
580 * MODIFICATION HISTORY
581 *
582 * 17-Sep-2004 Amulya Mishra Created.
583 */
584
585 PROCEDURE validate_rule_objects(
586 p_create_update_flag IN VARCHAR2,
587 p_rule_objects_rec IN FUN_RULE_OBJECTS_PUB.rule_objects_rec_type,
588 p_rowid IN ROWID ,
589 x_return_status IN OUT NOCOPY VARCHAR2
590 ) IS
591
592 l_dummy VARCHAR2(1);
593 l_rule_object_name FUN_RULE_OBJECTS_B.RULE_OBJECT_NAME%TYPE;
594 l_user_rule_object_name FUN_RULE_OBJECTS_TL.USER_RULE_OBJECT_NAME%TYPE;
595 l_description FUN_RULE_OBJECTS_TL.DESCRIPTION%TYPE;
596 l_result_type FUN_RULE_OBJECTS_B.RESULT_TYPE%TYPE;
597 l_required_flag FUN_RULE_OBJECTS_B.REQUIRED_FLAG%TYPE;
598 l_multi_rule_result_flag FUN_RULE_OBJECTS_B.MULTI_RULE_RESULT_FLAG%TYPE;
599 l_default_value FUN_RULE_OBJ_ATTRIBUTES.DEFAULT_VALUE%TYPE;
600 l_created_by_module FUN_RULE_OBJECTS_B.CREATED_BY_MODULE%TYPE;
601 l_use_instance_flag FUN_RULE_OBJECTS_B.USE_INSTANCE_FLAG%TYPE;
602 l_present NUMBER := 0;
603 l_dataType VARCHAR2(10);
604
605 l_return_status VARCHAR2(1);
606 l_isFlexFieldValid BOOLEAN := FALSE;
607
608 BEGIN
609
610
611 -- select columns needed to be checked from table during update
612
613 IF (p_create_update_flag = 'U') THEN
614 SELECT b.RULE_OBJECT_NAME,
615 b.RESULT_TYPE,
616 b.REQUIRED_FLAG,
617 A.DEFAULT_VALUE,
618 b.created_by_module,
619 b.use_instance_flag
620 INTO l_rule_object_name,
621 l_result_type,
622 l_required_flag,
623 l_default_value,
624 l_created_by_module,
625 l_use_instance_flag
626 FROM FUN_RULE_OBJECTS_B B, FUN_RULE_OBJ_ATTRIBUTES A
627 WHERE B.ROWID = p_rowid
628 AND B.RULE_OBJECT_ID = A.RULE_OBJECT_ID;
629 END IF;
630
631
632 ----------------------------------------------------
633 --validate If the combination RULE_OBJECT_NAME
634 --AND APPLICATION_ID for non instance already exists.
635 ----------------------------------------------------
636 BEGIN
637 IF (p_create_update_flag = 'C') THEN
638 IF (p_rule_objects_rec.instance_label IS NULL AND
639 p_rule_objects_rec.org_id IS NULL AND
640 p_rule_objects_rec.parent_rule_object_id IS NULL) THEN
641 SELECT COUNT(1)
642 INTO l_present
643 FROM FUN_RULE_OBJECTS_B
644 WHERE RULE_OBJECT_NAME = p_rule_objects_rec.rule_object_name
645 AND APPLICATION_ID = p_rule_objects_rec.application_id;
646 ELSE
647 SELECT COUNT(1)
648 INTO l_present
649 FROM FUN_RULE_OBJECTS_B
650 WHERE RULE_OBJECT_NAME = p_rule_objects_rec.rule_object_name
651 AND APPLICATION_ID = p_rule_objects_rec.application_id
652 AND
653 ( (INSTANCE_LABEL IS NULL AND p_rule_objects_rec.instance_label IS NULL) OR
654 (INSTANCE_LABEL IS NOT NULL AND p_rule_objects_rec.instance_label IS NOT NULL AND INSTANCE_LABEL = p_rule_objects_rec.instance_label))
655 AND
656 ( (ORG_ID IS NULL AND p_rule_objects_rec.org_id IS NULL) OR
657 (ORG_ID IS NOT NULL AND p_rule_objects_rec.org_id IS NOT NULL AND ORG_ID = p_rule_objects_rec.org_id))
658 AND PARENT_RULE_OBJECT_ID IS NOT NULL;
659
660 END IF;
661 END IF;
662 EXCEPTION
663 WHEN NO_DATA_FOUND THEN
664 l_present := 0;
665 WHEN OTHERS THEN
666 l_present := 0;
667 END;
668
669 IF l_present > 0 THEN
670 fnd_message.set_name('FUN', 'FUN_RULE_API_ALREADY_EXISTING');
671 fnd_message.set_token('OBJECT', p_rule_objects_rec.rule_object_name);
672 fnd_msg_pub.add;
673 x_return_status := fnd_api.g_ret_sts_error;
674 END IF;
675
676 --------------------
677 -- validate RULE_OBJECT_NAME
678 --------------------
679
680 -- RULE_OBJECT_NAME is mandatory
681 validate_mandatory (
682 p_create_update_flag => p_create_update_flag,
683 p_column => 'RULE_OBJECT_NAME',
684 p_column_value => p_rule_objects_rec.rule_object_name,
685 x_return_status => x_return_status);
686
687
688 --------------------
689 -- validate USER_RULE_OBJECT_NAME
690 --------------------
691
692 -- USER_RULE_OBJECT_NAME is mandatory
693 validate_mandatory (
694 p_create_update_flag => p_create_update_flag,
695 p_column => 'USER_RULE_OBJECT_NAME',
696 p_column_value => p_rule_objects_rec.user_rule_object_name,
697 x_return_status => x_return_status);
698
699
700
701 --------------------
702 -- validate RESULT_TYPE
703 --------------------
704
705 -- RESULT_TYPE is mandatory
706 validate_mandatory (
707 p_create_update_flag => p_create_update_flag,
708 p_column => 'RESULT_TYPE',
709 p_column_value => p_rule_objects_rec.result_type,
710 x_return_status => x_return_status);
711
712
713 -- RESULT_TYPE is lookup code in lookup type FUN_RULE_RESULT_TYPE
714 validate_lookup (
715 p_column => 'RESULT_TYPE',
716 p_lookup_table => 'FUN_LOOKUPS',
717 p_lookup_type => 'FUN_RULE_RESULT_TYPE',
718 p_column_value => p_rule_objects_rec.result_type,
719 x_return_status => x_return_status);
720
721 --------------------
722 -- validate USE_INSTANCE_FLAG
723 --------------------
724
725 -- USE_INSTANCE_FLAG is mandatory
726 validate_mandatory (
727 p_create_update_flag => p_create_update_flag,
728 p_column => 'USE_INSTANCE_FLAG',
729 p_column_value => p_rule_objects_rec.use_instance_flag,
730 x_return_status => x_return_status);
731
732
733 --------------------
734 -- validate MULTI_RULE_RESULT_FLAG
735 --------------------
736
737 -- MULTI_RULE_RESULT_FLAG is mandatory
738 validate_mandatory (
739 p_create_update_flag => p_create_update_flag,
740 p_column => 'MULTI_RULE_RESULT_FLAG',
741 p_column_value => p_rule_objects_rec.multi_rule_result_flag,
742 x_return_status => x_return_status);
743
744
745 -- If RESULT_TYPE is DFF, then user must provide flexfield_name and flexfield_app_short_name
746
747 IF (p_rule_objects_rec.result_type = 'MULTIVALUE') THEN
748 IF(p_rule_objects_rec.flexfield_name IS NULL OR p_rule_objects_rec.flexfield_name = '' OR
749 p_rule_objects_rec.flexfield_app_short_name IS NULL OR p_rule_objects_rec.flexfield_app_short_name = '')
750 THEN
751 fnd_message.set_name('FUN', 'FUN_RULE_NO_DFF_INFO');
752 fnd_msg_pub.add;
753 x_return_status := fnd_api.g_ret_sts_error;
754 ELSE
755 l_isFlexFieldValid := isFlexFieldValid(p_rule_objects_rec.flexfield_name , p_rule_objects_rec.flexfield_app_short_name);
756 IF(NOT l_isFlexFieldValid) THEN
757 fnd_message.set_name('FUN', 'FUN_RULE_INVALID_DFF_NAME');
758 fnd_msg_pub.add;
759 x_return_status := fnd_api.g_ret_sts_error;
760 END IF;
761 END IF;
762 END IF;
763
764
765 -- If RESULT_TYPE is VALUESET, then user must provide flex_value_set_id
766 IF (p_rule_objects_rec.result_type = 'VALUESET'
767 AND (p_rule_objects_rec.flex_value_set_id IS NULL OR p_rule_objects_rec.flex_value_set_id = '')
768 ) THEN
769 fnd_message.set_name('FUN', 'FUN_RULE_NO_VALUESET_INFO');
770 fnd_msg_pub.add;
771 x_return_status := fnd_api.g_ret_sts_error;
772 ELSE
773 --Validate if the flex_value_set_id is Valid or not.
774 IF (p_rule_objects_rec.result_type = 'VALUESET' AND
775 NOT validate_flex_value_set_id(p_rule_objects_rec.flex_value_set_id)) THEN
776 fnd_message.set_name('FUN', 'FUN_RULE_INVALID_VALUESET');
777 fnd_msg_pub.add;
778 x_return_status := fnd_api.g_ret_sts_error;
779 ELSE
780 --This call is to validate the DataType of the Valueset.
781 --Valueset should not be of TIME format.
782 l_dataType := FUN_RULE_UTILITY_PKG.getValueSetDataType(p_rule_objects_rec.flex_value_set_id);
783 END IF;
784 END IF;
785
786 -------------------------
787 -- validate default_value and default_application_id
788 -- should not be Null, if required_flag is checked 'Y'
789 -- Do the checking only if use_default_value_flag is 'Y'.
790 -------------------------
791
792 /*We should not check this for MULTIVALUE result type. For MULTIVALUE, the values will be
793 *populated through the UI always.
794 */
795
796 IF(p_rule_objects_rec.result_type <> 'MULTIVALUE' AND
797 p_rule_objects_rec.use_default_value_flag = 'Y') THEN
798 IF NVL(p_rule_objects_rec.required_flag, 'N') = 'Y' THEN
799 validate_mandatory (
800 p_create_update_flag => p_create_update_flag,
801 p_column => 'DEFAULT_VALUE',
802 p_column_value => p_rule_objects_rec.default_value,
803 x_return_status => x_return_status);
804
805 validate_mandatory (
806 p_create_update_flag => p_create_update_flag,
807 p_column => 'DEFAULT_APPLICATION_ID',
808 p_column_value => p_rule_objects_rec.default_application_id,
809 x_return_status => x_return_status);
810
811 END IF;
812 END IF;
813
814 --------------------------------------
815 -- validate created_by_module
816 --------------------------------------
817
818 -- created_by_module is mandatory field
819 -- Since created_by_module is non-updateable, we only need to check mandatory
820 -- during creation.
821
822 IF p_create_update_flag = 'C' THEN
823 validate_mandatory (
824 p_create_update_flag => p_create_update_flag,
825 p_column => 'created_by_module',
826 p_column_value => p_rule_objects_rec.created_by_module,
827 x_return_status => x_return_status);
828 END IF;
829
830 -- created_by_module is non-updateable field. But it can be updated from NULL to
831 -- some value.
832
833 IF p_create_update_flag = 'U' AND
834 p_rule_objects_rec.created_by_module IS NOT NULL
835 THEN
836 validate_nonupdateable (
837 p_column => 'created_by_module',
838 p_column_value => p_rule_objects_rec.created_by_module,
839 p_old_column_value => l_created_by_module,
840 p_restricted => 'N',
841 x_return_status => x_return_status);
842 END IF;
843
844 -- use_instance_flag is non-updateable field.
845
846 IF p_create_update_flag = 'U'
847 THEN
848 validate_nonupdateable_atall (
849 p_column => 'use_instance_flag',
850 p_column_value => p_rule_objects_rec.use_instance_flag,
851 p_old_column_value => l_use_instance_flag,
852 p_restricted => 'N',
853 x_return_status => x_return_status);
854 END IF;
855
856 --Validate Application Id
857
858 IF(p_rule_objects_rec.application_id IS NOT NULL) THEN
859 IF(NOT validate_application_id (p_rule_objects_rec.application_id)) THEN
860 fnd_message.set_name('FUN', 'FUN_RULE_INVALID_APPL_ID');
861 fnd_msg_pub.add;
862 x_return_status := fnd_api.g_ret_sts_error;
863 END IF;
864 END IF;
865
866 --Validate Default Application Id
867 IF(p_rule_objects_rec.default_application_id IS NOT NULL) THEN
868 IF(NOT validate_application_id (p_rule_objects_rec.default_application_id)) THEN
869 fnd_message.set_name('FUN', 'FUN_RULE_INVALID_APPL_ID');
870 fnd_msg_pub.add;
871 x_return_status := fnd_api.g_ret_sts_error;
872 END IF;
873 END IF;
874
875 END validate_rule_objects;
876
877 /**
878 * PROCEDURE validate_rule_object_instance
879 *
880 * DESCRIPTION
881 * Validates rule_object instance record. Checks for
882 * uniqueness
883 * lookup types
884 * mandatory columns
885 * non-updateable fields
886 * foreign key validations
887 * other validations
888 *
889 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
890 *
891 * ARGUMENTS
892 * IN:
893 * p_create_update_flag Create update flag. 'C' = create. 'U' = update.
894 * p_location_rec Location record.
895 * p_rowid Rowid of the record (used only in update mode).
896 * IN/OUT:
897 * x_return_status Return status after the call. The status can
898 * be FND_API.G_RET_STS_SUCCESS (success),
899 * fnd_api.g_ret_sts_error (error),
900 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
901 *
902 * NOTES
903 *
904 * MODIFICATION HISTORY
905 *
906 * 27-Dec-2005 Amulya Mishra Created.
907 */
908
909 PROCEDURE validate_rule_object_instance(
910 p_create_update_flag IN VARCHAR2,
911 p_rule_object_instance_rec IN FUN_RULE_OBJECTS_PUB.rule_objects_rec_type,
912 p_rowid IN ROWID ,
913 x_return_status IN OUT NOCOPY VARCHAR2
914 ) IS
915
916 l_present number;
917 BEGIN
918
919 ----------------------------------------------------
920 --validate the uniqueness of RULE_OBJECT_NAME,
921 --APPLICATION_ID and not null INSTANCE_LABEL.
922 ----------------------------------------------------
923
924 BEGIN
925 IF (p_create_update_flag = 'C') THEN
926 IF (p_rule_object_instance_rec.instance_label IS NOT NULL) THEN
927 SELECT COUNT(1)
928 INTO l_present
929 FROM FUN_RULE_OBJECTS_B
930 WHERE RULE_OBJECT_NAME = p_rule_object_instance_rec.rule_object_name
931 AND APPLICATION_ID = p_rule_object_instance_rec.application_id
932 AND
933 ( (INSTANCE_LABEL IS NULL AND p_rule_object_instance_rec.instance_label IS NULL) OR
934 (INSTANCE_LABEL IS NOT NULL AND p_rule_object_instance_rec.instance_label IS NOT NULL AND INSTANCE_LABEL = p_rule_object_instance_rec.instance_label))
935 AND
936 ( (ORG_ID IS NULL AND p_rule_object_instance_rec.org_id IS NULL) OR
937 (ORG_ID IS NOT NULL AND p_rule_object_instance_rec.org_id IS NOT NULL AND ORG_ID = p_rule_object_instance_rec.org_id))
938 AND PARENT_RULE_OBJECT_ID IS NOT NULL;
939 END IF;
940 END IF;
941 EXCEPTION
942 WHEN NO_DATA_FOUND THEN
943 l_present := 0;
944 WHEN OTHERS THEN
945 l_present := 0;
946 END;
947
948 IF l_present > 0 THEN
949 fnd_message.set_name('FUN', 'FUN_RULE_API_ALREADY_EXISTING');
950 fnd_message.set_token('OBJECT', p_rule_object_instance_rec.instance_label);
951 fnd_msg_pub.add;
952 x_return_status := fnd_api.g_ret_sts_error;
953 END IF;
954
955 --Now validate for the Rule Object Instance COlumns.
956
957 /***********************************************************************
958 Rule Object Instance Enhancement. Validations done for following.
959 1)-USE_INSTANCE_FLAG should be always N or Y.
960 2)-if USE_INSTANCE_FLAG is Y, then INSTANCE_LABEL should be not null.
961 3)-Validate the org_id passes from the Host Application.
962 ***********************************************************************/
963
964 -- USE_INSTANCE_FLAG is mandatory , either Y or N.
965 validate_mandatory (
966 p_create_update_flag => p_create_update_flag,
967 p_column => 'USE_INSTANCE_FLAG',
968 p_column_value => p_rule_object_instance_rec.use_instance_flag,
969 x_return_status => x_return_status);
970
971 -- PARENT_RULE_OBJECT_ID is mandatory For Rule Object Instancein Update Mode.
972 IF (p_create_update_flag = 'U') THEN
973 validate_mandatory (
974 p_create_update_flag => 'U',
975 p_column => 'PARENT_RULE_OBJECT_ID',
976 p_column_value => p_rule_object_instance_rec.parent_rule_object_id,
977 x_return_status => x_return_status);
978
979 END IF;
980
981
982 IF(p_rule_object_instance_rec.org_id IS NOT NULL) THEN
983 IF(NOT validate_org_id (p_rule_object_instance_rec.org_id)) THEN
984 fnd_message.set_name('FUN', 'FUN_RULE_INVALID_ORG_ID');
985 fnd_msg_pub.add;
986 x_return_status := fnd_api.g_ret_sts_error;
987 END IF;
988 END IF;
989
990 -- validate the input record for Rule Object Data
991 FUN_RULE_VALIDATE_PKG.validate_rule_objects(
992 p_create_update_flag,
993 p_rule_object_instance_rec,
994 p_rowid,
995 x_return_status
996 );
997
998 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
999 RAISE FND_API.G_EXC_ERROR;
1000 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1001 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1002 END IF;
1003 END validate_rule_object_instance;
1004
1005
1006 /**
1007 * PROCEDURE validate_rule_criteria_params
1008 *
1009 * DESCRIPTION
1010 * Validates rule_criteria_params record. Checks for
1011 * uniqueness
1012 * lookup types
1013 * mandatory columns
1014 * non-updateable fields
1015 * foreign key validations
1016 * other validations
1017 *
1018 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1019 *
1020 * ARGUMENTS
1021 * IN:
1022 * p_create_update_flag Create update flag. 'C' = create. 'U' = update.
1023 * p_rule_crit_params_rec Location record.
1024 * p_rowid Rowid of the record (used only in update mode).
1025 * IN/OUT:
1026 * x_return_status Return status after the call. The status can
1027 * be FND_API.G_RET_STS_SUCCESS (success),
1028 * fnd_api.g_ret_sts_error (error),
1029 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1030 *
1031 * NOTES
1032 *
1033 * MODIFICATION HISTORY
1034 *
1035 * 17-Sep-2004 Amulya Mishra Created.
1036 */
1037
1038 PROCEDURE validate_rule_criteria_params(
1039 p_create_update_flag IN VARCHAR2,
1040 p_rule_crit_params_rec IN FUN_RULE_CRIT_PARAMS_PUB.rule_crit_params_rec_type,
1041 p_rowid IN ROWID ,
1042 x_return_status IN OUT NOCOPY VARCHAR2
1043 ) IS
1044
1045 l_dummy VARCHAR2(1);
1046 l_param_name FUN_RULE_CRIT_PARAMS_B.PARAM_NAME%TYPE;
1047 l_user_param_name FUN_RULE_CRIT_PARAMS_TL.USER_PARAM_NAME%TYPE;
1048 l_description FUN_RULE_CRIT_PARAMS_TL.DESCRIPTION%TYPE;
1049 l_data_type FUN_RULE_CRIT_PARAMS_B.DATA_TYPE%TYPE;
1050 l_created_by_module FUN_RULE_CRIT_PARAMS_B.CREATED_BY_MODULE%TYPE;
1051 l_return_status VARCHAR2(1);
1052 l_rule_object_id FUN_RULE_OBJECTS_B.RULE_OBJECT_ID%TYPE;
1053
1054 BEGIN
1055
1056 -- select columns needed to be checked from table during update
1057
1058 IF (p_create_update_flag = 'U') THEN
1059 SELECT PARAM_NAME,
1060 DATA_TYPE,
1061 created_by_module
1062 INTO l_param_name,
1063 l_data_type,
1064 l_created_by_module
1065 FROM FUN_RULE_CRIT_PARAMS_B
1066 WHERE ROWID = p_rowid;
1067 END IF;
1068
1069 -- Validate if a valid rule object id is passed or not
1070
1071 BEGIN
1072 SELECT RULE_OBJECT_ID INTO l_rule_object_id
1073 FROM FUN_RULE_OBJECTS_B WHERE RULE_OBJECT_ID = p_rule_crit_params_rec.rule_object_id;
1074 EXCEPTION
1075 WHEN NO_DATA_FOUND THEN
1076 fnd_message.set_name('FUN', 'FUN_RULE_INVALID_ROB');
1077 fnd_msg_pub.add;
1078 x_return_status := fnd_api.g_ret_sts_error;
1079 END;
1080
1081
1082 --------------------
1083 -- validate PARAM_NAME
1084 --------------------
1085
1086 -- PARAM_NAME is mandatory
1087 validate_mandatory (
1088 p_create_update_flag => p_create_update_flag,
1089 p_column => 'PARAM_NAME',
1090 p_column_value => p_rule_crit_params_rec.param_name,
1091 x_return_status => x_return_status);
1092
1093
1094 --------------------
1095 -- validate USER_PARAM_NAME
1096 --------------------
1097
1098 -- USER_PARAM_NAME is mandatory
1099 validate_mandatory (
1100 p_create_update_flag => p_create_update_flag,
1101 p_column => 'USER_PARAM_NAME',
1102 p_column_value => p_rule_crit_params_rec.user_param_name,
1103 x_return_status => x_return_status);
1104
1105
1106
1107 --------------------
1108 -- validate DATA_TYPE
1109 --------------------
1110
1111 -- DATA_TYPE is mandatory
1112 validate_mandatory (
1113 p_create_update_flag => p_create_update_flag,
1114 p_column => 'DATA_TYPE',
1115 p_column_value => p_rule_crit_params_rec.data_type,
1116 x_return_status => x_return_status);
1117
1118 -- DATA_TYPE is lookup code in lookup type FUN_RULE_DATA_TYPE
1119 validate_lookup (
1120 p_column => 'DATA_TYPE',
1121 p_lookup_table => 'FUN_LOOKUPS',
1122 p_lookup_type => 'FUN_RULE_DATA_TYPE',
1123 p_column_value => p_rule_crit_params_rec.data_type,
1124 x_return_status => x_return_status);
1125
1126
1127 --------------------------------------
1128 -- validate created_by_module
1129 --------------------------------------
1130
1131 -- created_by_module is mandatory field
1132 -- Since created_by_module is non-updateable, we only need to check mandatory
1133 -- during creation.
1134
1135 IF p_create_update_flag = 'C' THEN
1136 validate_mandatory (
1137 p_create_update_flag => p_create_update_flag,
1138 p_column => 'created_by_module',
1139 p_column_value => p_rule_crit_params_rec.created_by_module,
1140 x_return_status => x_return_status);
1141 END IF;
1142
1143
1144 -- created_by_module is non-updateable field. But it can be updated from NULL to
1145 -- some value.
1146
1147 IF p_create_update_flag = 'U' AND
1148 p_rule_crit_params_rec.created_by_module IS NOT NULL
1149 THEN
1150 validate_nonupdateable (
1151 p_column => 'created_by_module',
1152 p_column_value => p_rule_crit_params_rec.created_by_module,
1153 p_old_column_value => l_created_by_module,
1154 p_restricted => 'N',
1155 x_return_status => x_return_status);
1156 END IF;
1157
1158 END validate_rule_criteria_params;
1159
1160
1161 /**
1162 * PROCEDURE validate_rule_details
1163 *
1164 * DESCRIPTION
1165 * Validates rule_details record. Checks for
1166 * uniqueness
1167 * lookup types
1168 * mandatory columns
1169 * non-updateable fields
1170 * foreign key validations
1171 * other validations
1172 *
1173 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1174 *
1175 * ARGUMENTS
1176 * IN:
1177 * p_create_update_flag Create update flag. 'C' = create. 'U' = update.
1178 * p_rule_details_rec Location record.
1179 * p_rowid Rowid of the record (used only in update mode).
1180 * IN/OUT:
1181 * x_return_status Return status after the call. The status can
1182 * be FND_API.G_RET_STS_SUCCESS (success),
1183 * fnd_api.g_ret_sts_error (error),
1184 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1185 *
1186 * NOTES
1187 *
1188 * MODIFICATION HISTORY
1189 *
1190 * 17-Sep-2004 Amulya Mishra Created.
1191 */
1192
1193 PROCEDURE validate_rule_details(
1194 p_create_update_flag IN VARCHAR2,
1195 p_rule_details_rec IN FUN_RULE_DETAILS_PUB.rule_details_rec_type,
1196 p_rowid IN ROWID ,
1197 x_return_status IN OUT NOCOPY VARCHAR2
1198 ) IS
1199
1200 l_dummy VARCHAR2(1);
1201 l_rule_name FUN_RULE_DETAILS.RULE_NAME%TYPE;
1202 l_seq FUN_RULE_DETAILS.SEQ%TYPE;
1203 l_operator FUN_RULE_DETAILS.OPERATOR%TYPE;
1204 l_enabled_flag FUN_RULE_DETAILS.ENABLED_FLAG%TYPE;
1205 l_result_application_id FUN_RULE_DETAILS.RESULT_APPLICATION_ID%TYPE;
1206 l_result_value FUN_RULE_DETAILS.RESULT_VALUE%TYPE;
1207 l_created_by_module FUN_RULE_DETAILS.CREATED_BY_MODULE%TYPE;
1208
1209 l_return_status VARCHAR2(1);
1210
1211 BEGIN
1212
1213
1214 -- select columns needed to be checked from table during update
1215
1216 IF (p_create_update_flag = 'U') THEN
1217 SELECT RULE_NAME,
1218 SEQ,
1219 OPERATOR,
1220 ENABLED_FLAG,
1221 RESULT_APPLICATION_ID,
1222 RESULT_VALUE,
1223 CREATED_BY_MODULE
1224 INTO l_rule_name,
1225 l_seq,
1226 l_operator,
1227 l_enabled_flag,
1228 l_result_application_id,
1229 l_result_value,
1230 l_created_by_module
1231 FROM FUN_RULE_DETAILS
1232 WHERE ROWID = p_rowid;
1233 END IF;
1234
1235
1236 --------------------
1237 -- validate RULE_NAME
1238 --------------------
1239
1240 -- RULE_NAME is mandatory
1241 validate_mandatory (
1242 p_create_update_flag => p_create_update_flag,
1243 p_column => 'RULE_NAME',
1244 p_column_value => p_rule_details_rec.rule_name,
1245 x_return_status => x_return_status);
1246
1247 --------------------
1248 -- validate SEQ
1249 --------------------
1250
1251 -- SEQ is mandatory
1252 validate_mandatory (
1253 p_create_update_flag => p_create_update_flag,
1254 p_column => 'SEQ',
1255 p_column_value => p_rule_details_rec.seq,
1256 x_return_status => x_return_status);
1257
1258 --------------------
1259 -- validate OPERATOR
1260 --------------------
1261
1262 -- OPERATOR is mandatory
1263 validate_mandatory (
1264 p_create_update_flag => p_create_update_flag,
1265 p_column => 'OPERATOR',
1266 p_column_value => p_rule_details_rec.operator,
1267 x_return_status => x_return_status);
1268
1269 -- OPERATOR is lookup code in lookup type FUN_RULE_OPERATORS
1270 validate_lookup (
1271 p_column => 'OPERATOR',
1272 p_lookup_table => 'FUN_LOOKUPS',
1273 p_lookup_type => 'FUN_RULE_OPERATORS',
1274 p_column_value => p_rule_details_rec.operator,
1275 x_return_status => x_return_status);
1276 --------------------
1277 -- validate ENABLED_FLAG
1278 --------------------
1279
1280 -- ENABLED_FLAG is mandatory
1281 validate_mandatory (
1282 p_create_update_flag => p_create_update_flag,
1283 p_column => 'ENABLED_FLAG',
1284 p_column_value => p_rule_details_rec.enabled_flag,
1285 x_return_status => x_return_status);
1286
1287
1288 -- RULE_OBJECT_ID has foreign key FUN_RULE_OBJECTS.RULE_OBJECT_ID
1289 IF p_rule_details_rec.rule_object_id IS NOT NULL
1290 AND
1291 p_rule_details_rec.rule_object_id <> fnd_api.g_miss_num
1292 THEN
1293 BEGIN
1294 SELECT 'Y'
1295 INTO l_dummy
1296 FROM FUN_RULE_OBJECTS_B
1297 WHERE RULE_OBJECT_ID = p_rule_details_rec.rule_object_id;
1298 EXCEPTION
1299 WHEN NO_DATA_FOUND THEN
1300 fnd_message.set_name('FUN', 'FUN_RULE_API_INVALID_RULE');
1301 fnd_msg_pub.add;
1302 x_return_status := fnd_api.g_ret_sts_error;
1303 END;
1304 END IF;
1305
1306 --------------------------------------
1307 -- validate created_by_module
1308 --------------------------------------
1309
1310 -- created_by_module is mandatory field
1311 -- Since created_by_module is non-updateable, we only need to check mandatory
1312 -- during creation.
1313
1314 IF p_create_update_flag = 'C' THEN
1315 validate_mandatory (
1316 p_create_update_flag => p_create_update_flag,
1317 p_column => 'created_by_module',
1318 p_column_value => p_rule_details_rec.created_by_module,
1319 x_return_status => x_return_status);
1320 END IF;
1321
1322 -- created_by_module is non-updateable field. But it can be updated from NULL to
1323 -- some value.
1324 /*
1325 IF p_create_update_flag = 'U' AND
1326 p_rule_details_rec.created_by_module IS NOT NULL
1327 THEN
1328 validate_nonupdateable (
1329 p_column => 'created_by_module',
1330 p_column_value => p_rule_details_rec.created_by_module,
1331 p_old_column_value => l_created_by_module,
1332 p_restricted => 'N',
1333 x_return_status => x_return_status,
1334 p_raise_error => 'N');
1335 END IF;
1336 */
1337
1338 --Validate Default Application Id
1339 IF(p_rule_details_rec.result_application_id IS NOT NULL) THEN
1340 IF(NOT validate_application_id (p_rule_details_rec.result_application_id)) THEN
1341 fnd_message.set_name('FUN', 'FUN_RULE_INVALID_APPL_ID');
1342 fnd_msg_pub.add;
1343 x_return_status := fnd_api.g_ret_sts_error;
1344 END IF;
1345 END IF;
1346
1347
1348
1349 END validate_rule_details;
1350
1351 /**
1352 * PROCEDURE validate_rule_criteria
1353 *
1354 * DESCRIPTION
1355 * Validates rule_object record. Checks for
1356 * uniqueness
1357 * lookup types
1358 * mandatory columns
1359 * non-updateable fields
1360 * foreign key validations
1361 * other validations
1362 *
1363 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1364 *
1365 * ARGUMENTS
1366 * IN:
1367 * p_create_update_flag Create update flag. 'C' = create. 'U' = update.
1368 * p_criteria_rec Location record.
1369 * p_rowid Rowid of the record (used only in update mode).
1370 * IN/OUT:
1371 * x_return_status Return status after the call. The status can
1372 * be FND_API.G_RET_STS_SUCCESS (success),
1373 * fnd_api.g_ret_sts_error (error),
1374 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1375 *
1376 * NOTES
1377 *
1378 * MODIFICATION HISTORY
1379 *
1380 * 17-Sep-2004 Amulya Mishra Created.
1381 */
1382
1383 PROCEDURE validate_rule_criteria(
1384 p_create_update_flag IN VARCHAR2,
1385 p_rule_criteria_rec IN FUN_RULE_CRITERIA_PUB.rule_criteria_rec_type,
1386 p_rowid IN ROWID ,
1387 x_return_status IN OUT NOCOPY VARCHAR2
1388 ) IS
1389
1390 l_dummy VARCHAR2(1);
1391 l_criteria_param_id FUN_RULE_CRITERIA.CRITERIA_PARAM_ID%TYPE;
1392 l_condition FUN_RULE_CRITERIA.CONDITION%TYPE;
1393 l_param_value FUN_RULE_CRITERIA.PARAM_VALUE%TYPE;
1394 l_case_sensitive_flag FUN_RULE_CRITERIA.CASE_SENSITIVE_FLAG%TYPE;
1395 l_created_by_module FUN_RULE_CRITERIA.CREATED_BY_MODULE%TYPE;
1396
1397 l_return_status VARCHAR2(1);
1398
1399 BEGIN
1400
1401
1402 -- select columns needed to be checked from table during update
1403
1404 IF (p_create_update_flag = 'U') THEN
1405 SELECT CRITERIA_PARAM_ID,
1406 CONDITION,
1407 PARAM_VALUE,
1408 CASE_SENSITIVE_FLAG,
1409 created_by_module
1410 INTO l_criteria_param_id,
1411 l_condition,
1412 l_param_value,
1413 l_case_sensitive_flag,
1414 l_created_by_module
1415 FROM FUN_RULE_CRITERIA
1416 WHERE ROWID = p_rowid;
1417 END IF;
1418
1419 --------------------
1420 -- validate CRITERIA_PARAM_NAME
1421 --------------------
1422
1423 -- CRITERIA_PARAM_NAME is mandatory
1424 validate_mandatory (
1425 p_create_update_flag => p_create_update_flag,
1426 p_column => 'CRITERIA_PARAM_ID',
1427 p_column_value => p_rule_criteria_rec.criteria_param_id,
1428 x_return_status => x_return_status);
1429
1430 --------------------
1431 -- validate CONDITION
1432 --------------------
1433
1434 -- CONDITION is mandatory
1435 validate_mandatory (
1436 p_create_update_flag => p_create_update_flag,
1437 p_column => 'CONDITION',
1438 p_column_value => p_rule_criteria_rec.condition,
1439 x_return_status => x_return_status);
1440
1441 -- CONDITION is lookup code in lookup type FUN_RULE_OPERATORS
1442 validate_lookup (
1443 p_column => 'CONDITION',
1444 p_lookup_table => 'FUN_LOOKUPS',
1445 p_lookup_type => 'FUN_RULE_MATCHING_CONDITIONS',
1446 p_column_value => p_rule_criteria_rec.condition,
1447 x_return_status => x_return_status);
1448
1449 -- CASE_SENSITIVE is lookup code in lookup type FUN_RULE_OPERATORS
1450 IF(p_rule_criteria_rec.case_sensitive_flag IS NOT NULL) THEN
1451 validate_lookup (
1452 p_column => 'CASE_SENSITIVE',
1453 p_lookup_table => 'FUN_LOOKUPS',
1454 p_lookup_type => 'FUN_RULE_YES_NO',
1455 p_column_value => p_rule_criteria_rec.case_sensitive_flag,
1456 x_return_status => x_return_status);
1457 END IF;
1458
1459 --------------------------------------
1460 -- validate created_by_module
1461 --------------------------------------
1462
1463 -- created_by_module is mandatory field
1464 -- Since created_by_module is non-updateable, we only need to check mandatory
1465 -- during creation.
1466
1467 IF p_create_update_flag = 'C' THEN
1468 validate_mandatory (
1469 p_create_update_flag => p_create_update_flag,
1470 p_column => 'created_by_module',
1471 p_column_value => p_rule_criteria_rec.created_by_module,
1472 x_return_status => x_return_status);
1473 END IF;
1474
1475 -- created_by_module is non-updateable field. But it can be updated from NULL to
1476 -- some value.
1477 /*
1478 IF p_create_update_flag = 'U' AND
1479 p_rule_criteria_rec.created_by_module IS NOT NULL
1480 THEN
1481 validate_nonupdateable (
1482 p_column => 'created_by_module',
1483 p_column_value => p_rule_criteria_rec.created_by_module,
1484 p_old_column_value => l_created_by_module,
1485 p_restricted => 'N',
1486 x_return_status => x_return_status);
1487 END IF;
1488 */
1489 END validate_rule_criteria;
1490
1491
1492 /**
1493 * PROCEDURE validate_rich_messages
1494 *
1495 * DESCRIPTION
1496 * Validates rich_messages record record. Checks for
1497 * uniqueness
1498 * mandatory columns
1499 * non-updateable fields
1500 * foreign key validations
1501 * other validations
1502 *
1503 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1504 *
1505 * ARGUMENTS
1506 * IN:
1507 * p_create_update_flag Create update flag. 'C' = create. 'U' = update.
1508 * p_rich_messages_rec Rich Messages record.
1509 * p_rowid Rowid of the record (used only in update mode).
1510 * IN/OUT:
1511 * x_return_status Return status after the call. The status can
1512 * be FND_API.G_RET_STS_SUCCESS (success),
1513 * fnd_api.g_ret_sts_error (error),
1514 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1515 *
1516 * NOTES
1517 *
1518 * MODIFICATION HISTORY
1519 *
1520 * 17-Sep-2004 Amulya Mishra Created.
1521 */
1522 /*
1523 PROCEDURE validate_rich_messages(
1524 p_create_update_flag IN VARCHAR2,
1525 p_rich_messages_rec IN FUN_RICH_MESSAGES_PUB.rich_messages_rec_type,
1526 p_rowid IN ROWID ,
1527 x_return_status IN OUT NOCOPY VARCHAR2
1528 ) IS
1529
1530 l_dummy VARCHAR2(1);
1531 l_message_name VARCHAR2(30);
1532 l_language_code VARCHAR2(4);
1533 l_message_text CLOB;
1534 l_application_id NUMBER;
1535 l_created_by_module VARCHAR2(150);
1536 l_present NUMBER := 0;
1537
1538 l_return_status VARCHAR2(1);
1539
1540 BEGIN
1541
1542
1543 -- select columns needed to be checked from table during update
1544
1545 IF (p_create_update_flag = 'U') THEN
1546 SELECT message_name,
1547 language_code,
1548 application_id,
1549 created_by_module
1550 INTO l_message_name,
1551 l_language_code,
1552 l_application_id,
1553 l_created_by_module
1554 FROM FUN_RICH_MESSAGES_B
1555 WHERE ROWID = p_rowid;
1556 END IF;
1557
1558
1559 --------------------------------------------
1560 --validate If the combination already exists.
1561 --------------------------------------------
1562
1563
1564 BEGIN
1565 IF (p_create_update_flag = 'C') THEN
1566 SELECT COUNT(1)
1567 INTO l_present
1568 FROM FUN_RICH_MESSAGES_B
1569 WHERE MESSAGE_NAME = p_rich_messages_rec.message_name
1570 AND APPLICATION_ID = p_rich_messages_rec.application_id
1571 AND LANGUAGE_CODE = p_rich_messages_rec.language_code;
1572 END IF;
1573 EXCEPTION
1574 WHEN NO_DATA_FOUND THEN
1575 l_present := 0;
1576 WHEN OTHERS THEN
1577 l_present := 0;
1578 END;
1579
1580 IF l_present > 0 THEN
1581 fnd_message.set_name('FUN', 'FUN_RULE_API_ALREADY_EXISTING');
1582 fnd_message.set_token('OBJECT', p_rich_messages_rec.message_name);
1583 fnd_msg_pub.add;
1584 x_return_status := fnd_api.g_ret_sts_error;
1585 END IF;
1586
1587
1588 --------------------
1589 -- validate MESSAGE_NAME
1590 --------------------
1591
1592 -- MESSAGE_NAME is mandatory
1593 validate_mandatory (
1594 p_create_update_flag => p_create_update_flag,
1595 p_column => 'MESSAGE_NAME',
1596 p_column_value => p_rich_messages_rec.message_name,
1597 x_return_status => x_return_status);
1598
1599
1600 --------------------
1601 -- validate LANGUAGE_CODE
1602 --------------------
1603
1604 -- LANGUAGE_CODE is mandatory
1605 validate_mandatory (
1606 p_create_update_flag => p_create_update_flag,
1607 p_column => 'LANGUAGE_CODE',
1608 p_column_value => p_rich_messages_rec.language_code,
1609 x_return_status => x_return_status);
1610
1611
1612
1613 --------------------
1614 -- validate APPLICATION_ID
1615 --------------------
1616
1617 -- APPLICATION_ID is mandatory
1618 validate_mandatory (
1619 p_create_update_flag => p_create_update_flag,
1620 p_column => 'APPLICATION_ID',
1621 p_column_value => p_rich_messages_rec.application_id,
1622 x_return_status => x_return_status);
1623
1624
1625 --------------------------------------
1626 -- validate created_by_module
1627 --------------------------------------
1628
1629 -- created_by_module is mandatory field
1630 -- Since created_by_module is non-updateable, we only need to check mandatory
1631 -- during creation.
1632
1633 IF p_create_update_flag = 'C' THEN
1634 validate_mandatory (
1635 p_create_update_flag => p_create_update_flag,
1636 p_column => 'created_by_module',
1637 p_column_value => p_rich_messages_rec.created_by_module,
1638 x_return_status => x_return_status);
1639 END IF;
1640
1641 -- created_by_module is non-updateable field. But it can be updated from NULL to
1642 -- some value.
1643 IF p_create_update_flag = 'U' AND
1644 p_rich_messages_rec.created_by_module IS NOT NULL
1645 THEN
1646 validate_nonupdateable (
1647 p_column => 'created_by_module',
1648 p_column_value => p_rich_messages_rec.created_by_module,
1649 p_old_column_value => l_created_by_module,
1650 p_restricted => 'N',
1651 x_return_status => x_return_status,
1652 p_raise_error => 'N');
1653 END IF;
1654
1655
1656 END validate_rich_messages;
1657 */
1658
1659
1660 PROCEDURE validate_mandatory (
1661 p_create_update_flag IN VARCHAR2,
1662 p_column IN VARCHAR2,
1663 p_column_value IN VARCHAR2,
1664 p_restricted IN VARCHAR2 DEFAULT 'N',
1665 x_return_status IN OUT NOCOPY VARCHAR2
1666 ) IS
1667
1668 l_error BOOLEAN := FALSE;
1669
1670 BEGIN
1671
1672 IF p_restricted = 'N' THEN
1673 IF (p_create_update_flag = 'C' AND
1674 (p_column_value IS NULL OR
1675 p_column_value = FND_API.G_MISS_CHAR)) OR
1676 (p_create_update_flag = 'U' AND
1677 p_column_value = FND_API.G_MISS_CHAR)
1678 THEN
1679 l_error := TRUE;
1680 END IF;
1681 ELSE
1682 IF (p_column_value IS NULL OR
1683 p_column_value = FND_API.G_MISS_CHAR)
1684 THEN
1685 l_error := TRUE;
1686 END IF;
1687 END IF;
1688
1689 IF l_error THEN
1690 fnd_message.set_name('FUN', 'FUN_RULE_API_MISSING_COLUMN');
1691 fnd_message.set_token('COLUMN', p_column);
1692 fnd_msg_pub.add;
1693 x_return_status := fnd_api.g_ret_sts_error;
1694 END IF;
1695
1696 END validate_mandatory;
1697
1698 PROCEDURE validate_mandatory (
1699 p_create_update_flag IN VARCHAR2,
1700 p_column IN VARCHAR2,
1701 p_column_value IN NUMBER,
1702 p_restricted IN VARCHAR2 DEFAULT 'N',
1703 x_return_status IN OUT NOCOPY VARCHAR2
1704 ) IS
1705
1706 l_error BOOLEAN := FALSE;
1707
1708 BEGIN
1709
1710 IF p_restricted = 'N' THEN
1711 IF (p_create_update_flag = 'C' AND
1712 (p_column_value IS NULL OR
1713 p_column_value = FND_API.G_MISS_NUM)) OR
1714 (p_create_update_flag = 'U' AND
1715 p_column_value = FND_API.G_MISS_NUM)
1716 THEN
1717 l_error := TRUE;
1718 END IF;
1719 ELSE
1720 IF (p_column_value IS NULL OR
1721 p_column_value = FND_API.G_MISS_NUM)
1722 THEN
1723 l_error := TRUE;
1724 END IF;
1725 END IF;
1726
1727 IF l_error THEN
1728 fnd_message.set_name('FUN', 'FUN_RULE_API_MISSING_COLUMN');
1729 fnd_message.set_token('COLUMN', p_column);
1730 fnd_msg_pub.add;
1731 x_return_status := fnd_api.g_ret_sts_error;
1732 END IF;
1733
1734 END validate_mandatory;
1735
1736
1737 PROCEDURE validate_mandatory (
1738 p_create_update_flag IN VARCHAR2,
1739 p_column IN VARCHAR2,
1740 p_column_value IN DATE,
1741 p_restricted IN VARCHAR2 DEFAULT 'N',
1742 x_return_status IN OUT NOCOPY VARCHAR2
1743 ) IS
1744
1745 l_error BOOLEAN := FALSE;
1746
1747 BEGIN
1748
1749 IF p_restricted = 'N' THEN
1750 IF (p_create_update_flag = 'C' AND
1751 (p_column_value IS NULL OR
1752 p_column_value = FND_API.G_MISS_DATE)) OR
1753 (p_create_update_flag = 'U' AND
1754 p_column_value = FND_API.G_MISS_DATE)
1755 THEN
1756 l_error := TRUE;
1757 END IF;
1758 ELSE
1759 IF (p_column_value IS NULL OR
1760 p_column_value = FND_API.G_MISS_DATE)
1761 THEN
1762 l_error := TRUE;
1763 END IF;
1764 END IF;
1765
1766 IF l_error THEN
1767 fnd_message.set_name('FUN', 'FUN_RULE_API_MISSING_COLUMN');
1768 fnd_message.set_token('COLUMN', p_column);
1769 fnd_msg_pub.add;
1770 x_return_status := fnd_api.g_ret_sts_error;
1771 END IF;
1772
1773 END validate_mandatory;
1774
1775 PROCEDURE validate_nonupdateable (
1776 p_column IN VARCHAR2,
1777 p_column_value IN VARCHAR2,
1778 p_old_column_value IN VARCHAR2,
1779 p_restricted IN VARCHAR2 DEFAULT 'Y',
1780 x_return_status IN OUT NOCOPY VARCHAR2,
1781 p_raise_error IN VARCHAR2 := 'Y'
1782 ) IS
1783
1784 l_error BOOLEAN := FALSE;
1785
1786 BEGIN
1787
1788 IF p_column_value IS NOT NULL THEN
1789 IF p_restricted = 'Y' THEN
1790 IF (p_column_value <> fnd_api.g_miss_char OR
1791 p_old_column_value IS NOT NULL) AND
1792 (p_old_column_value IS NULL OR
1793 p_column_value <> p_old_column_value)
1794 THEN
1795 l_error := TRUE;
1796 END IF;
1797 ELSE
1798 IF (p_old_column_value IS NOT NULL AND -- BUG 3367582.
1799 p_old_column_value <> FND_API.G_MISS_CHAR)
1800 AND
1801 (p_column_value = fnd_api.g_miss_char OR
1802 p_column_value <> p_old_column_value)
1803 THEN
1804 l_error := TRUE;
1805 END IF;
1806 END IF;
1807 END IF;
1808 IF l_error THEN
1809 IF p_raise_error = 'Y' THEN
1810 fnd_message.set_name('FUN', 'FUN_RULE_API_NONUPDATE_COLUMN');
1811 fnd_message.set_token('COLUMN', p_column);
1812 fnd_msg_pub.add;
1813 END IF;
1814 x_return_status := fnd_api.g_ret_sts_error;
1815 END IF;
1816
1817 END validate_nonupdateable;
1818
1819
1820 PROCEDURE validate_nonupdateable_atall (
1821 p_column IN VARCHAR2,
1822 p_column_value IN VARCHAR2,
1823 p_old_column_value IN VARCHAR2,
1824 p_restricted IN VARCHAR2 DEFAULT 'Y',
1825 x_return_status IN OUT NOCOPY VARCHAR2,
1826 p_raise_error IN VARCHAR2 := 'Y'
1827 ) IS
1828
1829 l_error BOOLEAN := FALSE;
1830
1831 BEGIN
1832 IF p_restricted = 'Y' THEN
1833 IF (p_column_value <> fnd_api.g_miss_char OR
1834 p_old_column_value IS NOT NULL) AND
1835 (p_old_column_value IS NULL OR
1836 p_column_value <> p_old_column_value)
1837 THEN
1838 l_error := TRUE;
1839 END IF;
1840 ELSE
1841 IF (p_old_column_value IS NOT NULL AND -- BUG 3367582.
1842 p_old_column_value <> FND_API.G_MISS_CHAR)
1843 AND
1844 (p_column_value = fnd_api.g_miss_char OR
1845 p_column_value <> p_old_column_value)
1846 THEN
1847 l_error := TRUE;
1848 END IF;
1849 END IF;
1850
1851 IF l_error THEN
1852 IF p_raise_error = 'Y' THEN
1853 fnd_message.set_name('FUN', 'FUN_RULE_API_NONUPDATE_COLUMN');
1854 fnd_message.set_token('COLUMN', p_column);
1855 fnd_msg_pub.add;
1856 END IF;
1857 x_return_status := fnd_api.g_ret_sts_error;
1858 END IF;
1859
1860 END validate_nonupdateable_atall;
1861
1862
1863 PROCEDURE validate_lookup (
1864 p_column IN VARCHAR2,
1865 p_lookup_table IN VARCHAR2 DEFAULT 'FND_LOOKUP_VALUES',
1866 p_lookup_type IN VARCHAR2,
1867 p_column_value IN VARCHAR2,
1868 x_return_status IN OUT NOCOPY VARCHAR2
1869 ) IS
1870
1871 l_error BOOLEAN := FALSE;
1872
1873 BEGIN
1874
1875 IF p_column_value IS NOT NULL AND
1876 p_column_value <> fnd_api.g_miss_char THEN
1877
1878 IF p_lookup_type = 'YES/NO' THEN
1879 IF p_column_value NOT IN ('Y', 'N') THEN
1880 l_error := TRUE;
1881 END IF;
1882 ELSE
1883 IF NOT search(p_lookup_table || G_SPECIAL_STRING ||
1884 p_lookup_type || G_SPECIAL_STRING || p_column_value,
1885 'LOOKUP')
1886 THEN
1887 l_error := TRUE;
1888 END IF;
1889 END IF;
1890
1891 IF l_error THEN
1892 fnd_message.set_name('FUN', 'FUN_RULE_API_INVALID_LOOKUP');
1893 fnd_message.set_token('COLUMN', p_column);
1894 fnd_message.set_token('LOOKUP_TYPE', p_lookup_type);
1895 fnd_msg_pub.add;
1896 x_return_status := fnd_api.g_ret_sts_error;
1897 END IF;
1898 END IF;
1899
1900 END validate_lookup;
1901
1902
1903 /**
1904 * PROCEDURE validate_rich_messages
1905 *
1906 * DESCRIPTION
1907 * Validates rich_messages record record. Checks for
1908 * uniqueness
1909 * mandatory columns
1910 * non-updateable fields
1911 * foreign key validations
1912 * other validations
1913 *
1914 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1915 *
1916 * ARGUMENTS
1917 * IN:
1918 * p_create_update_flag Create update flag. 'C' = create. 'U' = update.
1919 * p_rich_messages_rec Rich Messages record.
1920 * p_rowid Rowid of the record (used only in update mode).
1921 * IN/OUT:
1922 * x_return_status Return status after the call. The status can
1923 * be FND_API.G_RET_STS_SUCCESS (success),
1924 * fnd_api.g_ret_sts_error (error),
1925 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1926 *
1927 * NOTES
1928 *
1929 * MODIFICATION HISTORY
1930 *
1931 * 17-Sep-2004 Amulya Mishra Created.
1932 */
1933
1934 PROCEDURE validate_rich_messages(
1935 p_create_update_flag IN VARCHAR2,
1936 p_rich_messages_rec IN FUN_RICH_MESSAGES_PUB.rich_messages_rec_type,
1937 p_rowid IN ROWID ,
1938 x_return_status IN OUT NOCOPY VARCHAR2
1939 ) IS
1940
1941 l_dummy VARCHAR2(1);
1942 l_message_name VARCHAR2(30);
1943 l_message_text CLOB;
1944 l_application_id NUMBER;
1945 l_created_by_module VARCHAR2(150);
1946 l_present NUMBER := 0;
1947
1948 l_return_status VARCHAR2(1);
1949
1950 BEGIN
1951
1952
1953 -- select columns needed to be checked from table during update
1954
1955 IF (p_create_update_flag = 'U') THEN
1956 SELECT message_name,
1957 application_id,
1958 created_by_module
1959 INTO l_message_name,
1960 l_application_id,
1961 l_created_by_module
1962 FROM FUN_RICH_MESSAGES_B
1963 WHERE ROWID = p_rowid;
1964 END IF;
1965
1966
1967 --------------------------------------------
1968 --validate If the combination already exists.
1969 --------------------------------------------
1970
1971
1972 BEGIN
1973 IF (p_create_update_flag = 'C') THEN
1974 SELECT COUNT(1)
1975 INTO l_present
1976 FROM FUN_RICH_MESSAGES_B
1977 WHERE MESSAGE_NAME = p_rich_messages_rec.message_name
1978 AND APPLICATION_ID = p_rich_messages_rec.application_id;
1979 END IF;
1980 EXCEPTION
1981 WHEN NO_DATA_FOUND THEN
1982 l_present := 0;
1983 WHEN OTHERS THEN
1984 l_present := 0;
1985 END;
1986
1987 IF l_present > 0 THEN
1988 fnd_message.set_name('FUN', 'FUN_RULE_API_ALREADY_EXISTING');
1989 fnd_message.set_token('OBJECT', p_rich_messages_rec.message_name);
1990 fnd_msg_pub.add;
1991 x_return_status := fnd_api.g_ret_sts_error;
1992 END IF;
1993
1994
1995 --------------------
1996 -- validate MESSAGE_NAME
1997 --------------------
1998
1999 -- MESSAGE_NAME is mandatory
2000 validate_mandatory (
2001 p_create_update_flag => p_create_update_flag,
2002 p_column => 'MESSAGE_NAME',
2003 p_column_value => p_rich_messages_rec.message_name,
2004 x_return_status => x_return_status);
2005
2006
2007 --------------------
2008 -- validate APPLICATION_ID
2009 --------------------
2010
2011 -- APPLICATION_ID is mandatory
2012 validate_mandatory (
2013 p_create_update_flag => p_create_update_flag,
2014 p_column => 'APPLICATION_ID',
2015 p_column_value => p_rich_messages_rec.application_id,
2016 x_return_status => x_return_status);
2017
2018
2019 --------------------------------------
2020 -- validate created_by_module
2021 --------------------------------------
2022
2023 -- created_by_module is mandatory field
2024 -- Since created_by_module is non-updateable, we only need to check mandatory
2025 -- during creation.
2026
2027 IF p_create_update_flag = 'C' THEN
2028 validate_mandatory (
2029 p_create_update_flag => p_create_update_flag,
2030 p_column => 'created_by_module',
2031 p_column_value => p_rich_messages_rec.created_by_module,
2032 x_return_status => x_return_status);
2033 END IF;
2034
2035 -- created_by_module is non-updateable field. But it can be updated from NULL to
2036 -- some value.
2037 IF p_create_update_flag = 'U' AND
2038 p_rich_messages_rec.created_by_module IS NOT NULL
2039 THEN
2040 validate_nonupdateable (
2041 p_column => 'created_by_module',
2042 p_column_value => p_rich_messages_rec.created_by_module,
2043 p_old_column_value => l_created_by_module,
2044 p_restricted => 'N',
2045 x_return_status => x_return_status,
2046 p_raise_error => 'N');
2047 END IF;
2048
2049
2050 END validate_rich_messages;
2051
2052 FUNCTION isFlexFieldValid(p_FlexFieldName IN VARCHAR2, p_FlexFieldAppShortName IN VARCHAR2)
2053 RETURN BOOLEAN IS
2054 source_cursor INTEGER;
2055 num_rows_processed INTEGER;
2056 l_num NUMBER;
2057
2058 l_DFFSql VARCHAR2(1000) := 'SELECT count(1) FROM FND_DESCRIPTIVE_FLEXS FDF
2059 WHERE FDF.DESCRIPTIVE_FLEXFIELD_NAME = :1
2060 AND APPLICATION_ID IN (
2061 SELECT APPLICATION_ID
2062 FROM FND_APPLICATION_VL
2063 WHERE APPLICATION_SHORT_NAME = :2)';
2064
2065
2066 BEGIN
2067
2068 source_cursor := DBMS_SQL.OPEN_CURSOR;
2069 DBMS_SQL.PARSE(source_cursor, l_DFFSql,DBMS_SQL.native);
2070 dbms_sql.bind_variable(source_cursor , '1' , p_FlexFieldName);
2071 dbms_sql.bind_variable(source_cursor , '2' , p_FlexFieldAppShortName);
2072
2073 dbms_sql.define_column(source_cursor, 1, l_num);
2074
2075 num_rows_processed := DBMS_SQL.EXECUTE(source_cursor);
2076
2077 IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN
2078 -- get column values of the row
2079 DBMS_SQL.COLUMN_VALUE(source_cursor, 1, l_num);
2080 END IF;
2081
2082 DBMS_SQL.CLOSE_CURSOR(source_cursor);
2083
2084 IF(l_num > 0) THEN RETURN TRUE;
2085 ELSE RETURN FALSE;
2086 END IF;
2087
2088 EXCEPTION
2089 WHEN OTHERS THEN
2090 IF DBMS_SQL.IS_OPEN(source_cursor) THEN
2091 DBMS_SQL.CLOSE_CURSOR(source_cursor);
2092 END IF;
2093 RETURN FALSE;
2094 END isFlexFieldValid;
2095
2096 FUNCTION validate_org_id (
2097 p_org_id IN NUMBER) RETURN BOOLEAN
2098 IS
2099
2100 source_cursor INTEGER;
2101 num_rows_processed INTEGER;
2102 l_num NUMBER;
2103
2104 l_DFFSql VARCHAR2(1000) := 'SELECT count(1) FROM HR_OPERATING_UNITS
2105 WHERE NVL(ORGANIZATION_ID,-1) = NVL(:1,-1)';
2106
2107
2108 BEGIN
2109
2110 source_cursor := DBMS_SQL.OPEN_CURSOR;
2111 DBMS_SQL.PARSE(source_cursor, l_DFFSql,DBMS_SQL.native);
2112 dbms_sql.bind_variable(source_cursor , '1' , p_org_id);
2113
2114 dbms_sql.define_column(source_cursor, 1, l_num);
2115
2116 num_rows_processed := DBMS_SQL.EXECUTE(source_cursor);
2117
2118 IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN
2119 -- get column values of the row
2120 DBMS_SQL.COLUMN_VALUE(source_cursor, 1, l_num);
2121 END IF;
2122
2123 DBMS_SQL.CLOSE_CURSOR(source_cursor);
2124
2125 IF(l_num > 0) THEN RETURN TRUE;
2126 ELSE RETURN FALSE;
2127 END IF;
2128
2129 EXCEPTION
2130 WHEN OTHERS THEN
2131 IF DBMS_SQL.IS_OPEN(source_cursor) THEN
2132 DBMS_SQL.CLOSE_CURSOR(source_cursor);
2133 END IF;
2134 RETURN FALSE;
2135 END validate_org_id;
2136
2137 FUNCTION validate_application_id (
2138 p_application_id IN NUMBER) RETURN BOOLEAN
2139 IS
2140
2141 source_cursor INTEGER;
2142 num_rows_processed INTEGER;
2143 l_num NUMBER;
2144
2145 l_DFFSql VARCHAR2(1000) := 'SELECT count(1) FROM FND_APPLICATION
2146 WHERE APPLICATION_ID = :1';
2147
2148
2149 BEGIN
2150 source_cursor := DBMS_SQL.OPEN_CURSOR;
2151 DBMS_SQL.PARSE(source_cursor, l_DFFSql,DBMS_SQL.native);
2152 dbms_sql.bind_variable(source_cursor , '1' , p_application_id);
2153
2154 dbms_sql.define_column(source_cursor, 1, l_num);
2155
2156 num_rows_processed := DBMS_SQL.EXECUTE(source_cursor);
2157
2158 IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN
2159 -- get column values of the row
2160 DBMS_SQL.COLUMN_VALUE(source_cursor, 1, l_num);
2161 END IF;
2162
2163 DBMS_SQL.CLOSE_CURSOR(source_cursor);
2164
2165 IF(l_num > 0) THEN RETURN TRUE;
2166 ELSE RETURN FALSE;
2167 END IF;
2168
2169 EXCEPTION
2170 WHEN OTHERS THEN
2171 IF DBMS_SQL.IS_OPEN(source_cursor) THEN
2172 DBMS_SQL.CLOSE_CURSOR(source_cursor);
2173 END IF;
2174 RETURN FALSE;
2175 END validate_application_id;
2176
2177
2178 FUNCTION validate_flex_value_set_id(p_flex_value_set_id IN NUMBER)
2179 RETURN BOOLEAN
2180 IS
2181
2182 source_cursor INTEGER;
2183 num_rows_processed INTEGER;
2184 l_num NUMBER;
2185
2186 l_DFFSql VARCHAR2(1000) := 'SELECT count(1) FROM FND_FLEX_VALUE_SETS
2187 WHERE FLEX_VALUE_SET_ID = :1';
2188
2189 BEGIN
2190
2191 source_cursor := DBMS_SQL.OPEN_CURSOR;
2192 DBMS_SQL.PARSE(source_cursor, l_DFFSql,DBMS_SQL.native);
2193 dbms_sql.bind_variable(source_cursor , '1' , p_flex_value_set_id);
2194
2195 dbms_sql.define_column(source_cursor, 1, l_num);
2196
2197 num_rows_processed := DBMS_SQL.EXECUTE(source_cursor);
2198
2199 IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN
2200 -- get column values of the row
2201 DBMS_SQL.COLUMN_VALUE(source_cursor, 1, l_num);
2202 END IF;
2203
2204 DBMS_SQL.CLOSE_CURSOR(source_cursor);
2205
2206 IF(l_num > 0) THEN RETURN TRUE;
2207 ELSE RETURN FALSE;
2208 END IF;
2209
2210 EXCEPTION
2211 WHEN OTHERS THEN
2212 IF DBMS_SQL.IS_OPEN(source_cursor) THEN
2213 DBMS_SQL.CLOSE_CURSOR(source_cursor);
2214 END IF;
2215 RETURN FALSE;
2216 END validate_flex_value_set_id;
2217
2218 END FUN_RULE_VALIDATE_PKG;