[Home] [Help]
PACKAGE BODY: APPS.HZ_CLASS_VALIDATE_V2PUB
Source
1 PACKAGE BODY HZ_CLASS_VALIDATE_V2PUB AS
2 /*$Header: ARH2CLVB.pls 120.57.12000000.2 2007/10/01 14:48:20 manjayar ship $ */
3
4
5 /*---------------------
6 -- Local variables --
7 ---------------------*/
8 -- Bug 3962783
9 --g_ex_invalid_param EXCEPTION;
10 l_owner_table_name VARCHAR2(30);
11 l_owner_table_id VARCHAR2(30);
12 l_content_source_type VARCHAR2(30);
13 l_class_code VARCHAR2(30);
14 l_class_code2 VARCHAR2(30);
15 l_class_code3 VARCHAR2(30);
16 l_start_date_active DATE;
17 l_end_date_active DATE;
18 l_start_date_active2 DATE;
19 l_end_date_active2 DATE;
20 l_start VARCHAR2(15);
21 l_end VARCHAR2(15);
22 l_start2 VARCHAR2(15);
23 l_end2 VARCHAR2(15);
24 l_text VARCHAR2(4000);
25 l_column_name VARCHAR2(240);
26
27 -----------------------------------------------------------------
28 -- Private procedures and functions used internally by validation
29 -- process. These are brought from old hz_common_pub.
30 -----------------------------------------------------------------
31
32 procedure check_mandatory_str_col
33 -- Control mandatory column for varchar2 type
34 -- create update flag belongs to [C (creation) ,U (update)]
35 -- Column name
36 -- Column Value
37 -- Allow Null in creation mode flag
38 -- Allow Null in update mode flag
39 -- Control Status
40 ( create_update_flag IN VARCHAR2,
41 p_col_name IN VARCHAR2,
42 p_col_val IN VARCHAR2,
43 p_miss_allowed_in_c IN BOOLEAN,
44 p_miss_allowed_in_u IN BOOLEAN,
45 x_return_status IN OUT NOCOPY VARCHAR2)
46 IS
47 BEGIN
48 IF (p_col_val = FND_API.G_MISS_CHAR) THEN
49 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
50 fnd_message.set_token('COLUMN', p_col_name);
51 fnd_msg_pub.add;
52 x_return_status := fnd_api.g_ret_sts_error;
53 RETURN;
54 END IF;
55
56 IF (create_update_flag = 'C') THEN
57 IF ((NOT p_miss_allowed_in_c) AND
58 p_col_val IS NULL )
59 THEN
60 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
61 fnd_message.set_token('COLUMN', p_col_name);
62 fnd_msg_pub.add;
63 x_return_status := fnd_api.g_ret_sts_error;
64 END IF;
65 ELSE
66 IF ((NOT p_miss_allowed_in_u) AND
67 p_col_val IS NULL )
68 THEN
69 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
70 fnd_message.set_token('COLUMN', p_col_name);
71 fnd_msg_pub.add;
72 x_return_status := fnd_api.g_ret_sts_error;
73 END IF;
74 END IF;
75 END check_mandatory_str_col;
76
77
78 procedure check_mandatory_date_col
79 -- Control mandatory column for date type
80 -- create update flag belongs to [C (creation) ,U (update)]
81 -- Column name
82 -- Column Value
83 -- Allow Null in creation mode flag
84 -- Allow Null in update mode flag
85 -- Control Status
86 ( create_update_flag IN VARCHAR2,
87 p_col_name IN VARCHAR2,
88 p_col_val IN DATE,
89 p_miss_allowed_in_c IN BOOLEAN,
90 p_miss_allowed_in_u IN BOOLEAN,
91 x_return_status IN OUT NOCOPY VARCHAR2)
92 IS
93 BEGIN
94 IF (p_col_val = FND_API.G_MISS_DATE) THEN
95 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
96 fnd_message.set_token('COLUMN', p_col_name);
97 fnd_msg_pub.add;
98 x_return_status := fnd_api.g_ret_sts_error;
99 RETURN;
100 END IF;
101
102 IF (create_update_flag = 'C') THEN
103 IF ((NOT p_miss_allowed_in_c) AND
104 p_col_val IS NULL )
105 THEN
106 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
107 fnd_message.set_token('COLUMN', p_col_name);
108 fnd_msg_pub.add;
109 x_return_status := fnd_api.g_ret_sts_error;
110 END IF;
111 ELSE
112 IF ((NOT p_miss_allowed_in_u) AND
113 p_col_val IS NULL )
114 THEN
115 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
116 fnd_message.set_token('COLUMN', p_col_name);
117 fnd_msg_pub.add;
118 x_return_status := fnd_api.g_ret_sts_error;
119 END IF;
120 END IF;
121 END check_mandatory_date_col;
122
123
124 procedure check_mandatory_num_col
125 -- Control mandatory column for number type
126 -- create update flag belongs to [C (creation) ,U (update)]
127 -- Column name
128 -- Column Value
129 -- Allow Null in creation mode flag
130 -- Allow Null in update mode flag
131 -- Control Status
132 ( create_update_flag IN VARCHAR2,
133 p_col_name IN VARCHAR2,
134 p_col_val IN NUMBER,
135 p_miss_allowed_in_c IN BOOLEAN,
136 p_miss_allowed_in_u IN BOOLEAN,
137 x_return_status IN OUT NOCOPY VARCHAR2)
138 IS
139 BEGIN
140 IF (p_col_val = FND_API.G_MISS_NUM) THEN
141 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
142 fnd_message.set_token('COLUMN', p_col_name);
143 fnd_msg_pub.add;
144 x_return_status := fnd_api.g_ret_sts_error;
145 RETURN;
146 END IF;
147
148 IF (create_update_flag = 'C') THEN
149 IF ((NOT p_miss_allowed_in_c) AND
150 p_col_val IS NULL )
151 THEN
152 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
153 fnd_message.set_token('COLUMN', p_col_name);
154 fnd_msg_pub.add;
155 x_return_status := fnd_api.g_ret_sts_error;
156 END IF;
157 ELSE
158 IF ((NOT p_miss_allowed_in_u) AND
159 p_col_val IS NULL )
160 THEN
161 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
162 fnd_message.set_token('COLUMN', p_col_name);
163 fnd_msg_pub.add;
164 x_return_status := fnd_api.g_ret_sts_error;
165 END IF;
166 END IF;
167 END check_mandatory_num_col;
168
169
170 FUNCTION compare(
171 date1 DATE,
172 date2 DATE) RETURN NUMBER
173 IS
174 ldate1 date;
175 ldate2 date;
176 BEGIN
177 -- Bug 3614582 : Removed TRUNC from the date comparison.
178 -- Also consider fnd_api.g_miss_date in comparison.
179 /* ldate1 := trunc(date1);
180 ldate2 := trunc(date2);*/
181 ldate1 := date1;
182 ldate2 := date2;
183 IF ((ldate1 IS NULL OR ldate1 = FND_API.G_MISS_DATE) AND (ldate2 IS NULL OR ldate2 = FND_API.G_MISS_DATE)) THEN
184 RETURN 0;
185 ELSIF (ldate2 IS NULL OR ldate2 = FND_API.G_MISS_DATE) THEN
186 RETURN -1;
187 ELSIF (ldate1 IS NULL OR ldate1 = FND_API.G_MISS_DATE) THEN
188 RETURN 1;
189 ELSIF ( ldate1 = ldate2 ) THEN
190 RETURN 0;
191 ELSIF ( ldate1 > ldate2 ) THEN
192 RETURN 1;
193 ELSE
194 RETURN -1;
195 END IF;
196 END compare;
197
198
199 FUNCTION is_between
200 ( datex DATE,
201 date1 DATE,
202 date2 DATE) RETURN BOOLEAN
203 IS
204 BEGIN
205 IF compare(datex, date1) >= 0 AND
206 compare(date2, datex) >=0 THEN
207 RETURN TRUE;
208 ELSE
209 RETURN FALSE;
210 END IF;
211 END is_between;
212
213
214 FUNCTION is_overlap
215 -- Returns 'Y' if period [s1,e1] overlaps [s2,e2]
216 -- 'N' otherwise
217 -- NULL indicates infinite for END dates
218 (s1 DATE,
219 e1 DATE,
220 s2 DATE,
221 e2 DATE)
222 RETURN VARCHAR2
223 IS
224 BEGIN
225 IF ( is_between(s1, s2, e2) ) OR ( is_between(s2, s1, e1) ) THEN
226 RETURN 'Y';
227 ELSE
228 RETURN 'N';
229 END IF;
230 END is_overlap;
231
232
233 PROCEDURE validate_fnd_lookup
234 ( p_lookup_type IN VARCHAR2,
235 p_column IN VARCHAR2,
236 p_column_value IN VARCHAR2,
237 x_return_status IN OUT NOCOPY VARCHAR2)
238 IS
239 CURSOR c1
240 IS
241 SELECT 'Y'
242 FROM fnd_lookup_values
243 WHERE lookup_type = p_lookup_type
244 AND lookup_code = p_column_value
245 -- bug 4212585
246 AND enabled_flag = 'Y'
247 AND sysdate between nvl(start_date_active,sysdate) AND nvl(end_date_active,sysdate+1)
248 AND ROWNUM = 1;
249
250 l_exist VARCHAR2(1);
251 BEGIN
252 IF ( p_column_value IS NOT NULL
253 AND p_column_value <> fnd_api.g_miss_char ) THEN
254 OPEN c1;
255 FETCH c1 INTO l_exist;
256 IF c1%NOTFOUND THEN
257 fnd_message.set_name('AR','HZ_API_INVALID_LOOKUP');
258 fnd_message.set_token('COLUMN',p_column);
259 fnd_message.set_token('LOOKUP_TYPE',p_lookup_type);
260 fnd_msg_pub.add;
261 x_return_status := fnd_api.g_ret_sts_error;
262 END IF;
263 CLOSE c1;
264 END IF;
265 END validate_fnd_lookup;
266
267 --Bug 2830772: When the content_source_type not 'USER_ENTERED' and
268 --lookup type is 'NACE', the overloaded procedure, validate_fnd_lookup
269 --will be called.
270 PROCEDURE validate_fnd_lookup
271 ( p_lookup_type IN VARCHAR2,
272 p_column IN VARCHAR2,
273 p_column_value IN VARCHAR2,
274 p_content_source_type IN VARCHAR2,
275 x_return_status IN OUT NOCOPY VARCHAR2)
276 IS
277
278 --Bug 2830772: Added the cursor for 'NACE' lookup type where clause to ignore
279 --the period when comparing the lookup_code.
280 CURSOR c_nace
281 IS
282 SELECT 'Y'
283 FROM fnd_lookup_values
284 WHERE lookup_type = p_lookup_type
285 AND replace(lookup_code, '.', '') = replace(p_column_value, '.', '')
286 -- bug 4212585
287 AND enabled_flag = 'Y'
288 AND sysdate between nvl(start_date_active,sysdate) AND nvl(end_date_active,sysdate+1)
289 AND ROWNUM = 1;
290
291 l_exist VARCHAR2(1);
292 BEGIN
293
294 IF ( p_column_value IS NOT NULL
295 AND p_column_value <> fnd_api.g_miss_char ) THEN
296 OPEN c_nace;
297 FETCH c_nace INTO l_exist;
298 IF c_nace%NOTFOUND THEN
299 fnd_message.set_name('AR','HZ_API_INVALID_LOOKUP');
300 fnd_message.set_token('COLUMN',p_column);
301 fnd_message.set_token('LOOKUP_TYPE', p_lookup_type);
302 fnd_msg_pub.add;
303 x_return_status := fnd_api.g_ret_sts_error;
304 END IF;
305 CLOSE c_nace;
306 END IF;
307 END validate_fnd_lookup;
308
309 /*--------------------------------------------------------
310 -- Function usable in any validation entities sections -
311 --------------------------------------------------------*/
312
313 /*
314 If the delimiter is used for an existing class code meaning. Then this
315 delimiter is not valid */
316 FUNCTION is_valid_delimiter(p_class_category in varchar2, p_delimiter in
317 varchar2) return varchar2 is
318
319 cursor get_invalid_delimiter_csr is
320 select 'x'
321 from fnd_lookup_values_vl
322 where lookup_type = p_class_category
323 and sysdate between start_date_active and nvl(end_date_active,sysdate)
324 and instrb(meaning,p_delimiter)>0;
325
326 l_tmp varchar2(1);
327 begin
328 open get_invalid_delimiter_csr;
329 fetch get_invalid_delimiter_csr into l_tmp;
330 if get_invalid_delimiter_csr%NOTFOUND
331 then
332 close get_invalid_delimiter_csr;
333 return 'Y';
334 else return 'N';
335 end if;
336 close get_invalid_delimiter_csr;
337 end is_valid_delimiter;
338
339 /*
340 If the class code meaning contains the delimiter used for the class category,
341 need to modify the meaning of this class code */
342 FUNCTION is_valid_class_code_meaning(p_class_category in varchar2, p_meaning in
343 varchar2) return varchar2 is
344
345 cursor get_invalid_meaning_csr is
346 select 'x'
347 from hz_class_categories
348 where class_category = p_class_category
349 and instrb(p_meaning,delimiter)>0;
350
351 l_tmp varchar2(1);
352 begin
353 open get_invalid_meaning_csr;
354 fetch get_invalid_meaning_csr into l_tmp;
355 if get_invalid_meaning_csr%NOTFOUND
356 then
357 close get_invalid_meaning_csr;
358 return 'Y';
359 else return 'N';
360 end if;
361 close get_invalid_meaning_csr;
362 end is_valid_class_code_meaning;
363
364 PROCEDURE check_existence_class_category
365 (p_class_category IN VARCHAR2,
366 x_return_status IN OUT NOCOPY VARCHAR2)
367 IS
368 CURSOR c_exist_class_category(p_class_category IN VARCHAR2)
369 IS
370 SELECT 'Y'
371 FROM hz_class_categories
372 WHERE class_category = p_class_category
373 AND ROWNUM = 1;
374 l_exist VARCHAR2(1);
375 BEGIN
376 OPEN c_exist_class_category(p_class_category);
377 FETCH c_exist_class_category INTO l_exist;
378 IF c_exist_class_category%NOTFOUND THEN
379 fnd_message.set_name('AR','HZ_API_INVALID_FK');
380 fnd_message.set_token('FK','class_category');
381 fnd_message.set_token('COLUMN','class_category');
382 fnd_message.set_token('TABLE','hz_class_categories');
383 fnd_msg_pub.add;
384 x_return_status := fnd_api.g_ret_sts_error;
385 END IF;
386 CLOSE c_exist_class_category;
387 END check_existence_class_category;
388
389 /*
390
391 Commented this function
392
393 FUNCTION result_caller
394 (pack VARCHAR2,
395 comp VARCHAR2,
396 code0 VARCHAR2 DEFAULT NULL,
397 code1 VARCHAR2 DEFAULT NULL,
398 code2 VARCHAR2 DEFAULT NULL,
399 code3 VARCHAR2 DEFAULT NULL,
400 code4 VARCHAR2 DEFAULT NULL,
401 code5 VARCHAR2 DEFAULT NULL,
402 code6 VARCHAR2 DEFAULT NULL,
403 code7 VARCHAR2 DEFAULT NULL,
404 code8 VARCHAR2 DEFAULT NULL,
405 code9 VARCHAR2 DEFAULT NULL,
409 date3 DATE DEFAULT NULL,
406 date0 DATE DEFAULT NULL,
407 date1 DATE DEFAULT NULL,
408 date2 DATE DEFAULT NULL,
410 date4 DATE DEFAULT NULL,
411 date5 DATE DEFAULT NULL,
412 date6 DATE DEFAULT NULL,
413 date7 DATE DEFAULT NULL,
414 text VARCHAR2 DEFAULT NULL)
415 RETURN VARCHAR2
416 IS
417 lcode0 VARCHAR2(100);
418 lcode1 VARCHAR2(100);
419 lcode2 VARCHAR2(100);
420 lcode3 VARCHAR2(100);
421 lcode4 VARCHAR2(100);
422 lcode5 VARCHAR2(100);
423 lcode6 VARCHAR2(100);
424 lcode7 VARCHAR2(100);
425 lcode8 VARCHAR2(100);
426 lcode9 VARCHAR2(100);
427 ldate0 DATE;
428 ldate1 DATE;
429 ldate2 DATE;
430 ldate3 DATE;
431 ldate4 DATE;
432 ldate5 DATE;
433 ldate6 DATE;
434 ldate7 DATE;
435 ltext VARCHAR2(4000);
436 result VARCHAR2(50);
437 BEGIN
438 IF upper(pack) = 'HZ_CLASSIFICATION_VALIDATE' THEN
439 IF upper(comp) = 'INSTANCE_ALREADY_ASSIGNED' THEN
440 result := HZ_CLASSIFICATION_VALIDATE.INSTANCE_ALREADY_ASSIGNED
441 (date0, date1,
442 code0, code1, code2, code3,
443 lcode0,
444 ldate0,ldate1);
445 RETURN result;
446 ELSIF upper(comp) = 'PARENT_CODE' THEN
447 result := HZ_CLASSIFICATION_VALIDATE.PARENT_CODE
448 (code0, code1,
449 date0, date1,
450 lcode0,
451 ldate0,ldate1);
452 RETURN result;
453
454 ELSIF upper(comp) = 'CHILD_CODE' THEN
455 result := HZ_CLASSIFICATION_VALIDATE.CHILD_CODE
456 (code0, code1,
457 date0, date1,
458 lcode0,
459 ldate0,ldate1);
460 RETURN result;
461
462 ELSIF upper(comp) = 'IS_ALL_INST_LESS_ONE_CODE' THEN
463 result := HZ_CLASSIFICATION_VALIDATE.IS_ALL_INST_LESS_ONE_CODE
464 (code0,
465 lcode0,lcode1,lcode2,lcode3,lcode4,
466 ldate0,ldate1,ldate2,ldate3);
467 RETURN result;
468
469 ELSIF upper(comp) = 'IS_ALL_CODE_ONE_PARENT_ONLY' THEN
470 result := HZ_CLASSIFICATION_VALIDATE.IS_ALL_CODE_ONE_PARENT_ONLY
471 (code0,
472 lcode0,lcode1,lcode2,
473 ldate0,ldate1,ldate2,ldate3);
474 RETURN result;
475
476 ELSIF upper(comp) = 'SQL_VALID' THEN
477 result := HZ_CLASSIFICATION_VALIDATE.SQL_VALID
478 (text,
479 lcode0);
480 RETURN result;
481
482 ELSIF upper(comp) = 'SQL_STR_BUILD' THEN
483 result := HZ_CLASSIFICATION_VALIDATE.SQL_STR_BUILD
484 (code0, code1, code2,
485 lcode0, ltext);
486 RETURN ltext;
487
488 ELSIF upper(comp) = 'EXIST_PK_CODE_ASSIGN' THEN
489 result := HZ_CLASSIFICATION_VALIDATE.EXIST_PK_CODE_ASSIGN
490 (code0, code1, code2, code3, code4,
491 date0,
492 lcode0,ldate0);
493 RETURN result;
494
495 ELSIF upper(comp) = 'EXIST_PRIM_ASSIGN' THEN
496 result := HZ_CLASSIFICATION_VALIDATE.EXIST_PRIM_ASSIGN
497 (code0, code1, code2, code3, code4,
498 date0, date1,
499 lcode0, ldate0, ldate1);
500 RETURN result;
501
502 ELSIF upper(comp) = 'EXIST_SAME_CODE_ASSIGN' THEN
503 result := HZ_CLASSIFICATION_VALIDATE.EXIST_SAME_CODE_ASSIGN
504 (code0, code1, code2, code3, code4, code5,
505 date0, date1,
506 lcode0, ldate0, ldate1);
507 RETURN result;
508
509 ELSIF upper(comp) = 'EXIST_SECOND_ASSIGN_SAME_CODE' THEN
510 result := HZ_CLASSIFICATION_VALIDATE.EXIST_SAME_CODE_ASSIGN
511 (code0, code1, code2, code3, code4, code5,
512 date0, date1,
513 lcode0, ldate0, ldate1);
514 RETURN result;
515
516 END IF;
517 END IF;
518
519 END result_caller;
520
521 */
522
523 PROCEDURE check_start_end_active_dates(
524 p_start_date_active IN DATE,
525 p_end_date_active IN DATE,
526 x_return_status IN OUT NOCOPY VARCHAR2
527 )
528 IS
529 BEGIN
530 --end date must be null or greater than start date
531 IF ( p_end_date_active IS NOT NULL
532 AND p_end_date_active <> fnd_api.G_MISS_DATE )
533 THEN
534 IF ( p_start_date_active IS NOT NULL AND
535 p_start_date_active <> fnd_api.G_MISS_DATE AND
536 p_end_date_active < p_start_date_active )
537 THEN
538 fnd_message.set_name('AR', 'HZ_API_START_DATE_GREATER');
539 fnd_msg_pub.add;
540 x_return_status := fnd_api.g_ret_sts_error;
541 END IF;
542 END IF;
543 END check_start_end_active_dates;
544
545 -- Bug 3962783
546 /*
547 procedure check_err(
548 x_return_status IN VARCHAR2
549 ) IS
550 BEGIN
551 IF x_return_status = fnd_api.g_ret_sts_error
552 THEN
556 */
553 RAISE g_ex_invalid_param;
554 END IF;
555 END;
557
558 /*----------------------------------------
559 -- Validation for Hz_Class_Categories --
560 ----------------------------------------*/
561 FUNCTION exist_code_ass_not_node
562 -- This function answer to the question:
563 -- Return 'Y' if the category has one or more Non-Leaf-node Class Codes associated with instances of entities
564 -- active for to_date
565 -- 'N' otherwise
566 ( p_class_category IN VARCHAR2)
567 RETURN VARCHAR2
568 IS
569 CURSOR c1
570 IS
571 SELECT 'Y'
572 FROM hz_code_assignments a,
573 hz_class_code_relations b
574 WHERE a.class_category = p_class_category
575 AND b.class_category = p_class_category
576 AND a.class_code = b.class_code
577 AND (( a.start_date_active <= SYSDATE
578 AND NVL(a.end_date_active , SYSDATE) >= SYSDATE )
579 OR a.start_date_active > SYSDATE )
580 AND (( b.start_date_active <= SYSDATE
581 AND NVL(b.end_date_active , SYSDATE) >= SYSDATE )
582 OR b.start_date_active > SYSDATE )
583 AND ROWNUM = 1;
584 l_yn VARCHAR2(1);
585 result VARCHAR2(1);
586 BEGIN
587 OPEN c1;
588 FETCH c1 INTO l_yn;
589 IF c1%NOTFOUND THEN
590 -- There is no parent-level class code in this category assigned to an instance of entity.
591 result := 'N';
592 ELSE
593 result := 'Y';
594 END IF;
595 CLOSE c1;
596 RETURN result;
597 END exist_code_ass_not_node;
598
599 FUNCTION exist_reverse_relation
600 -- Return 'Y' if the entered sub-code was defined as the parent-code of the entered class-code within that category
601 -- for active periods
602 -- 'N' otherwise
603 ( p_class_category IN VARCHAR2,
604 p_class_code IN VARCHAR2,
605 p_sub_class_code IN VARCHAR2,
606 p_start_date_active IN DATE,
607 p_end_date_active IN DATE)
608 RETURN VARCHAR2
609 IS
610 CURSOR c0
611 IS
612 SELECT start_date_active,
613 end_date_active
614 FROM hz_class_code_relations
615 WHERE class_category = p_class_category
616 AND class_code = p_sub_class_code
617 AND sub_class_code = p_class_code
618 AND (( NVL(end_date_active , SYSDATE) >= SYSDATE
619 AND NVL(start_date_active, SYSDATE) <= SYSDATE)
620 OR start_date_active > SYSDATE );
621 l_start_date_active DATE;
622 l_end_date_active DATE;
623 result VARCHAR2(1);
624 BEGIN
625 OPEN c0;
626 result := 'N';
627 LOOP
628 FETCH c0 INTO l_start_date_active, l_end_date_active;
629 EXIT WHEN c0%NOTFOUND;
630 IF is_overlap(p_start_date_active, p_end_date_active,
631 l_start_date_active, l_end_date_active) = 'Y'
632 THEN
633 result := 'Y';
634 EXIT;
635 END IF;
636 END LOOP;
637 CLOSE c0;
638 RETURN result;
639 END exist_reverse_relation;
640
641 FUNCTION is_all_code_one_parent_only
642 -- Return Y if all class codes inside a category have no more than one parent for the current and futur period
643 -- N otherwise
644 (p_class_category VARCHAR2,
645 x_class_code IN OUT NOCOPY VARCHAR2,
646 x_class_code2 IN OUT NOCOPY VARCHAR2,
647 x_sub_class_code IN OUT NOCOPY VARCHAR2,
648 x_start_date_active IN OUT NOCOPY DATE,
649 x_end_date_active IN OUT NOCOPY DATE,
650 x_start_date_active2 IN OUT NOCOPY DATE,
651 x_end_date_active2 IN OUT NOCOPY DATE )
652 RETURN VARCHAR2
653 IS
654 CURSOR c0
655 IS
656 SELECT sub_class_code,
657 start_date_active,
658 end_date_active
659 FROM hz_class_code_relations
660 WHERE class_category = p_class_category;
661 CURSOR c1(p_class_category VARCHAR2, p_sub_class_code VARCHAR2)
662 IS
663 SELECT class_code,
664 start_date_active,
665 end_date_active
666 FROM hz_class_code_relations
667 WHERE class_category = p_class_category
668 AND sub_class_code = p_sub_class_code;
669 sub_code_has_two_parents EXCEPTION;
670 l_class_code VARCHAR2(30);
671 l_sub_class_code VARCHAR2(30);
672 l_start_date_active DATE;
673 l_end_date_active DATE;
674 result VARCHAR2(1);
675 l_count NUMBER;
676 BEGIN
677 result := 'Y';
678 l_count := 0;
679 OPEN c0;
680 LOOP
681 FETCH c0 INTO l_sub_class_code, l_start_date_active, l_end_date_active;
682 EXIT WHEN c0%NOTFOUND;
683 IF is_overlap( l_start_date_active, l_end_date_active,
684 SYSDATE , NULL ) = 'Y'
685 THEN
686 x_sub_class_code := l_sub_class_code;
687 l_count := 0;
688 OPEN c1(p_class_category, l_sub_class_code);
689 LOOP
690 FETCH c1 INTO l_class_code, l_start_date_active, l_end_date_active;
691 EXIT WHEN c1%NOTFOUND;
692
693 IF is_overlap( l_start_date_active, l_end_date_active,
697 IF l_count = 1 THEN
694 SYSDATE , NULL ) = 'Y'
695 THEN
696 l_count := l_count + 1;
698 x_class_code := l_class_code;
699 x_start_date_active := l_start_date_active;
700 x_end_date_active := l_end_date_active;
701 ELSIF l_count > 1 THEN
702 RAISE sub_code_has_two_parents;
703 END IF;
704 END IF;
705 END LOOP;
706 CLOSE c1;
707 END IF;
708 END LOOP;
709 CLOSE c0;
710 RETURN result;
711 EXCEPTION
712 WHEN sub_code_has_two_parents THEN
713 result := 'N';
714 x_class_code2 := l_class_code;
715 x_start_date_active2:= l_start_date_active;
716 x_end_date_active2 := l_end_date_active;
717 CLOSE c1;
718 CLOSE c0;
719 RETURN result;
720 END is_all_code_one_parent_only;
721
722 FUNCTION is_all_inst_less_one_code
723 -- Return Y if all the instances of 1 entity has 0 to 1 code assigned
724 -- for 1 category, 1 content active to day or in the futur.
725 -- N otherwise
726
727 -- SSM SST Integration and Extension
728 -- Changed all reference from content_source_type to actual_content_source.
729 ( p_class_category VARCHAR2,
730 x_owner_table IN OUT NOCOPY VARCHAR2,
731 x_owner_table_id IN OUT NOCOPY VARCHAR2,
732 x_content_source_type IN OUT NOCOPY VARCHAR2,
733 x_class_code IN OUT NOCOPY VARCHAR2,
734 x_class_code2 IN OUT NOCOPY VARCHAR2,
735 x_start_date_active IN OUT NOCOPY DATE,
736 x_end_date_active IN OUT NOCOPY DATE,
737 x_start_date_active2 IN OUT NOCOPY DATE,
738 x_end_date_active2 IN OUT NOCOPY DATE )
739 RETURN VARCHAR2
740 IS
741 -- Bug 4942316
742 CURSOR c0
743 IS
744 SELECT DISTINCT actual_content_source,
745 owner_table_name,
746 owner_table_id
747 FROM hz_code_assignments ca, fnd_lookup_values_vl lv
748 WHERE ca.class_category = p_class_category
749 AND ca.class_category = lv.lookup_type
750 AND ca.class_code = lv.lookup_code;
751
752 CURSOR c1( l_content_source_type IN VARCHAR2, l_owner_table_name IN VARCHAR2, l_owner_table_id IN VARCHAR2)
753 IS
754 SELECT class_code,
755 start_date_active,
756 end_date_active
757 FROM hz_code_assignments
758 WHERE class_category = p_class_category
759 AND actual_content_source = l_content_source_type
760 AND owner_table_name = l_owner_table_name
761 AND owner_table_id = l_owner_table_id;
762
763 l_class_code VARCHAR2(30);
764 l_content_source_type VARCHAR2(30);
765 l_owner_table_name VARCHAR2(30);
766 l_owner_table_id VARCHAR2(30);
767 l_start_date_active DATE;
768 l_end_date_active DATE;
769 lcount NUMBER;
770 result VARCHAR2(1);
771 exist_id_multi_parent EXCEPTION;
772
773 BEGIN
774 result := 'Y';
775 OPEN c0;
776 LOOP
777 FETCH c0 INTO l_content_source_type, l_owner_table_name, l_owner_table_id;
778 EXIT WHEN c0%NOTFOUND;
779 OPEN c1( l_content_source_type, l_owner_table_name, l_owner_table_id);
780 lcount := 0;
781 LOOP
782 FETCH c1 INTO l_class_code, l_start_date_active, l_end_date_active;
783 EXIT WHEN c1%NOTFOUND;
784 IF is_overlap( l_start_date_active, l_end_date_active,
785 SYSDATE , NULL ) = 'Y'
786 THEN
787 lcount := lcount + 1;
788 IF lcount = 1 THEN
789 x_class_code := l_class_code;
790 x_start_date_active := l_start_date_active;
791 x_end_date_active := l_end_date_active;
792 ELSIF lcount > 1 THEN
793 result := 'N';
794 x_start_date_active2 := l_start_date_active;
795 x_end_date_active2 := l_end_date_active;
796 x_class_code2 := l_class_code;
797 x_owner_table := l_owner_table_name;
798 x_owner_table_id := l_owner_table_id;
799 x_content_source_type:= l_content_source_type;
800 x_start_date_active := l_start_date_active;
801 x_end_date_active := l_end_date_active;
802 RAISE exist_id_multi_parent;
803 END IF;
804 END IF;
805 END LOOP;
806 CLOSE c1;
807 END LOOP;
808 CLOSE c0;
809 RETURN result;
810
811 EXCEPTION
812 WHEN exist_id_multi_parent THEN
813 CLOSE c1;
814 CLOSE c0;
815 RETURN result;
816 END is_all_inst_less_one_code;
817
818
819 FUNCTION exist_class_category
820 -- Return Y if the class category exists
821 -- N otherwise
822 (p_class_category VARCHAR2 )
823 RETURN VARCHAR2
824 IS
825 CURSOR c0
826 IS
827 SELECT 'Y'
828 FROM hz_class_categories
829 WHERE class_category = p_class_category;
830 l_yn VARCHAR2(1);
831 result VARCHAR2(1);
832 BEGIN
833 OPEN c0;
834 FETCH c0 INTO l_yn;
835 IF c0%NOTFOUND THEN
836 result := 'N';
837 ELSE
838 result := 'Y';
839 END IF;
840 CLOSE c0;
841 RETURN result;
845 --columns like owner_table_name, owner_table_id, and owner_table_key_1 to 5.
842 END exist_class_category;
843
844 --Bug 2825328: Added over_loaded procedures for validating the non-updatable
846 PROCEDURE validate_nonupdateable (
847 p_column IN VARCHAR2,
848 p_column_value IN VARCHAR2,
849 p_old_column_value IN VARCHAR2,
850 p_restricted IN VARCHAR2 DEFAULT 'Y',
851 x_return_status IN OUT NOCOPY VARCHAR2,
852 p_raise_error IN VARCHAR2 := 'Y'
853 ) IS
854
855 l_error BOOLEAN := FALSE;
856
857 BEGIN
858
859 IF p_column_value IS NOT NULL THEN
860 IF p_restricted = 'Y' THEN
861 IF (p_column_value <> fnd_api.g_miss_char OR
862 p_old_column_value IS NOT NULL) AND
863 (p_old_column_value IS NULL OR
864 p_column_value <> p_old_column_value)
865 THEN
866 l_error := TRUE;
867 END IF;
868 ELSE
869 IF (p_old_column_value IS NOT NULL AND -- Bug 3439053.
870 p_old_column_value <> FND_API.G_MISS_CHAR)
871 AND
872 (p_column_value = fnd_api.g_miss_char OR
873 p_column_value <> p_old_column_value)
874 THEN
875 l_error := TRUE;
876 END IF;
877 END IF;
878 END IF;
879
880 IF l_error THEN
881 IF p_raise_error = 'Y' THEN
882 fnd_message.set_name('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
883 fnd_message.set_token('COLUMN', p_column);
884 fnd_msg_pub.add;
885 END IF;
886 x_return_status := fnd_api.g_ret_sts_error;
887 END IF;
888
889 END validate_nonupdateable;
890
891 --Bug 2825328: Added over_loaded procedures for validating the non-updatable
892 --columns like owner_table_name, owner_table_id, and owner_table_key_1 to 5.
893 PROCEDURE validate_nonupdateable (
894 p_column IN VARCHAR2,
895 p_column_value IN NUMBER,
896 p_old_column_value IN NUMBER,
897 p_restricted IN VARCHAR2 DEFAULT 'Y',
898 x_return_status IN OUT NOCOPY VARCHAR2,
899 p_raise_error IN VARCHAR2 := 'Y'
900 ) IS
901
902 l_error BOOLEAN := FALSE;
903
904 BEGIN
905
906 IF p_column_value IS NOT NULL THEN
907 IF p_restricted = 'Y' THEN
908 IF (p_column_value <> fnd_api.g_miss_num OR
909 p_old_column_value IS NOT NULL) AND
910 (p_old_column_value IS NULL OR
911 p_column_value <> p_old_column_value)
912 THEN
913 l_error := TRUE;
914 END IF;
915 ELSE
916 IF (p_old_column_value IS NOT NULL AND -- Bug 3439053.
917 p_old_column_value <> FND_API.G_MISS_NUM)
918 AND
919 (p_column_value = fnd_api.g_miss_num OR
920 p_column_value <> p_old_column_value)
921 THEN
922 l_error := TRUE;
923 END IF;
924 END IF;
925 END IF;
926
927 IF l_error THEN
928 IF p_raise_error = 'Y' THEN
929 fnd_message.set_name('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
930 fnd_message.set_token('COLUMN', p_column);
931 fnd_msg_pub.add;
932 END IF;
933 x_return_status := fnd_api.g_ret_sts_error;
934 END IF;
935
936 END validate_nonupdateable;
937
938 procedure validate_class_category(
939 p_class_cat_rec IN HZ_CLASSIFICATION_V2PUB.CLASS_CATEGORY_REC_TYPE,
940 create_update_flag IN VARCHAR2,
941 x_return_status IN OUT NOCOPY VARCHAR2
942 ) IS
943 l_count NUMBER;
944 l_created_by_module hz_class_categories.created_by_module%TYPE := NULL;
945
946 CURSOR cu_lookup_type IS
947 SELECT 1
948 FROM fnd_lookup_types
949 WHERE lookup_type = p_class_cat_rec.class_category
950 AND rownum = 1;
951
952 CURSOR c_categories IS
953 select created_by_module
954 from hz_class_categories
955 where class_category = p_class_cat_rec.class_category;
956
957 BEGIN
958
959 IF create_update_flag = 'U' THEN
960 OPEN c_categories;
961 FETCH c_categories INTO l_created_by_module;
962 CLOSE c_categories;
963 END IF;
964
965 --Check for mandatory columns
966 check_mandatory_str_col(create_update_flag, 'class_category',
967 p_class_cat_rec.class_category,
968 FALSE,
969 FALSE, -- cannot be missing: PK
970 x_return_status);
971
972 check_mandatory_str_col(create_update_flag, 'allow_multi_assign_flag',
973 p_class_cat_rec.allow_multi_assign_flag,
974 FALSE,
975 TRUE,
976 x_return_status);
977
978 check_mandatory_str_col(create_update_flag, 'allow_multi_parent_flag',
982 x_return_status);
979 p_class_cat_rec.allow_multi_parent_flag,
980 FALSE,
981 TRUE,
983
984
985
986
987 --{HYU:bug
988 check_mandatory_str_col(create_update_flag, 'allow_leaf_node_only_flag',
989 p_class_cat_rec.allow_leaf_node_only_flag,
990 FALSE,
991 TRUE,
992 x_return_status);
993 --}
994
995
996 --Bug 2890671: created_by_module column is mandatory
997 -- created_by_module is non-updateable, lookup
998
999 hz_utility_v2pub.validate_created_by_module(
1000 p_create_update_flag => create_update_flag,
1001 p_created_by_module => p_class_cat_rec.created_by_module,
1002 p_old_created_by_module => l_created_by_module,
1003 x_return_status => x_return_status);
1004
1005 --check_err( x_return_status );
1006
1007 --Check for lookup type validations.
1008 OPEN cu_lookup_type;
1009 FETCH cu_lookup_type INTO l_count;
1010 IF cu_lookup_type%NOTFOUND THEN
1011 fnd_message.set_name('AR', 'HZ_API_INVALID_LOOKUP');
1012 fnd_message.set_token('COLUMN', 'class_category');
1013 fnd_message.set_token('LOOKUP_TYPE', p_class_cat_rec.class_category);
1014 fnd_msg_pub.add;
1015 x_return_status := fnd_api.g_ret_sts_error;
1016 END IF;
1017 CLOSE cu_lookup_type;
1018
1019 validate_fnd_lookup(
1020 'YES/NO',
1021 'allow_multi_assign_flag',
1022 p_class_cat_rec.allow_multi_assign_flag,
1023 x_return_status);
1024 validate_fnd_lookup(
1025 'YES/NO',
1026 'allow_multi_parent_flag',
1027 p_class_cat_rec.allow_multi_parent_flag,
1028 x_return_status);
1029 validate_fnd_lookup(
1030 'YES/NO',
1031 'allow_leaf_node_only_flag',
1032 p_class_cat_rec.allow_leaf_node_only_flag,
1033 x_return_status);
1034
1035 --check_err( x_return_status );
1036
1037 -- Check PK
1038 IF create_update_flag = 'C' THEN
1039 IF exist_class_category(p_class_cat_rec.class_category) = 'Y' THEN
1040 fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
1041 fnd_message.set_token('COLUMN', p_class_cat_rec.class_category);
1042 fnd_msg_pub.add;
1043 x_return_status := fnd_api.g_ret_sts_error;
1044 END IF;
1045 END IF;
1046
1047
1048 --{HYU Bug : 1607680 allow_leaf_node_only_flag
1049 IF create_update_flag = 'U' THEN
1050
1051 IF ( (p_class_cat_rec.allow_leaf_node_only_flag = 'Y' )
1052 AND (exist_code_ass_not_node(p_class_cat_rec.class_category) = 'Y' ) )
1053 THEN
1054 fnd_message.set_name('AR', 'HZ_API_LEAF_ONLY_NOT_ALLOWED');
1055 fnd_message.set_token('CLASS_CATEGORY', p_class_cat_rec.class_category);
1056 fnd_msg_pub.add;
1057 x_return_status := fnd_api.g_ret_sts_error;
1058 END IF;
1059
1060 IF ( (p_class_cat_rec.allow_multi_parent_flag = 'N' )
1061 AND (is_all_code_one_parent_only
1062 (p_class_cat_rec.class_category,
1063 l_class_code,
1064 l_class_code2,
1065 l_class_code3,
1066 l_start_date_active,
1067 l_end_date_active,
1068 l_start_date_active2,
1069 l_end_date_active) = 'N' ) )
1070
1071 THEN
1072 l_start := TO_CHAR(l_start_date_active, 'DD-MON-RRRR');
1073 IF l_end_date_active IS NULL THEN
1074 l_end := 'Unspecified';
1075 ELSE
1076 l_end := TO_CHAR(l_end_date_active, 'DD-MON-RRRR');
1077 END IF;
1078
1079 l_start2 := TO_CHAR(l_start_date_active2, 'DD-MON-RRRR');
1080 IF l_end_date_active2 IS NULL THEN
1081 l_end2 := 'Unspecified';
1082 ELSE
1083 l_end2 := TO_CHAR(l_end_date_active2, 'DD-MON-RRRR');
1084 END IF;
1085
1086 fnd_message.set_name('AR', 'HZ_API_SIN_PAR_NOT_ALLOWED');
1087 fnd_message.set_token('CLASS_CATEGORY', p_class_cat_rec.class_category);
1088 fnd_message.set_token('CLASS_CODE1' , l_class_code);
1089 fnd_message.set_token('CLASS_CODE2' , l_class_code2);
1090 fnd_message.set_token('CLASS_CODE3' , l_class_code3);
1091 fnd_message.set_token('START1' , l_start);
1092 fnd_message.set_token('END1' , l_end);
1093 fnd_message.set_token('START2' , l_start2);
1094 fnd_message.set_token('END2' , l_end2);
1095 fnd_msg_pub.add;
1096 x_return_status := fnd_api.g_ret_sts_error;
1097 END IF;
1098
1099 IF ( (p_class_cat_rec.allow_multi_assign_flag = 'N' )
1100 AND (is_all_inst_less_one_code(p_class_cat_rec.class_category,
1101 l_owner_table_name,
1102 l_owner_table_id,
1103 l_content_source_type,
1104 l_class_code,
1105 l_class_code2,
1106 l_start_date_active,
1110 THEN
1107 l_end_date_active,
1108 l_start_date_active2,
1109 l_end_date_active2 ) )='N' )
1111 l_start := TO_CHAR(l_start_date_active, 'DD-MON-RRRR');
1112 IF l_end_date_active IS NULL THEN
1113 l_end := 'Unspecified';
1114 ELSE
1115 l_end := TO_CHAR(l_end_date_active, 'DD-MON-RRRR');
1116 END IF;
1117
1118 l_start2 := TO_CHAR(l_start_date_active2, 'DD-MON-RRRR');
1119 IF l_end_date_active2 IS NULL THEN
1120 l_end2 := 'Unspecified';
1121 ELSE
1122 l_end2 := TO_CHAR(l_end_date_active2, 'DD-MON-RRRR');
1123 END IF;
1124
1125 fnd_message.set_name('AR', 'HZ_API_SIN_ASS_NOT_ALLOWED');
1126 fnd_message.set_token('CLASS_CATEGORY' , p_class_cat_rec.class_category);
1127 fnd_message.set_token('OWNER_TABLE' , l_owner_table_name);
1128 fnd_message.set_token('OWNER_TABLE_ID' , l_owner_table_id);
1129 fnd_message.set_token('CONTENT_SOURCE_TYPE', l_content_source_type);
1130 fnd_message.set_token('CLASS_CODE1' , l_class_code);
1131 fnd_message.set_token('CLASS_CODE2' , l_class_code2);
1132 fnd_message.set_token('START1' , l_start_date_active);
1133 fnd_message.set_token('END1' , l_end_date_active);
1134 fnd_message.set_token('START2' , l_start_date_active2);
1135 fnd_message.set_token('END2' , l_end_date_active2);
1136 fnd_msg_pub.add;
1137 x_return_status := fnd_api.g_ret_sts_error;
1138 END IF;
1139
1140 END IF;
1141 --check_err( x_return_status );
1142 --}
1143 /* -- Bug 3962783
1144 EXCEPTION
1145 WHEN OTHERS THEN
1146 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1147 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1148 FND_MSG_PUB.ADD;
1149 x_return_status := fnd_api.G_RET_STS_ERROR;
1150 */
1151 END validate_class_category;
1152
1153
1154
1155 /*-------------------------------------------
1156 -- Validation for Hz_Class_Category_Uses --
1157 -------------------------------------------*/
1158 FUNCTION existence_couple_clacat_owntab
1159 ( p_create_update_flag IN VARCHAR2,
1160 p_class_category IN VARCHAR2,
1161 p_owner_table IN VARCHAR2 )
1162 RETURN VARCHAR2
1163 IS
1164 CURSOR c_nb(
1165 p_class_category IN VARCHAR2,
1166 p_owner_table IN VARCHAR2)
1167 IS
1168 SELECT COUNT(1)
1169 FROM hz_class_category_uses
1170 WHERE class_category = p_class_category
1171 AND owner_table = p_owner_table;
1172 l_count NUMBER;
1173 result VARCHAR2(1);
1174 BEGIN
1175 OPEN c_nb(p_class_category, p_owner_table);
1176 FETCH c_nb INTO l_count;
1177 CLOSE c_nb;
1178 -- In creation mode the concatenated PK should not exist
1179 -- In updating mode the concatenated PK can exist
1180 IF ( (p_create_update_flag = 'C' AND l_count <> 0 )
1181 OR (p_create_update_flag = 'U' AND l_count > 1 ))
1182 THEN
1183 result := 'Y';
1184 ELSE
1185 result := 'N';
1186 END IF;
1187 RETURN result;
1188 END existence_couple_clacat_owntab;
1189
1190 PROCEDURE validate_class_category_use(
1191 p_in_rec IN hz_classification_V2PUB.class_category_use_rec_type,
1192 create_update_flag IN VARCHAR2,
1193 x_return_status IN OUT NOCOPY VARCHAR2 )
1194 IS
1195 l_end_date DATE := NULL;
1196 l_count NUMBER := 0;
1197 l_yn VARCHAR2(1);
1198 xx_obj varchar2(1) := NULL;
1199 l_created_by_module hz_class_category_uses.created_by_module%TYPE;
1200
1201 CURSOR c_uses IS
1202 select created_by_module
1203 from hz_class_category_uses
1204 where class_category = p_in_rec.class_category
1205 and owner_table = p_in_rec.owner_table;
1206
1207 BEGIN
1208
1209 IF create_update_flag = 'U' THEN
1210 OPEN c_uses;
1211 FETCH c_uses INTO l_created_by_module;
1212 CLOSE c_uses;
1213 END IF;
1214
1215 -- class_category is a mandatory column
1216 check_mandatory_str_col(
1217 create_update_flag,
1218 'class_category',
1219 p_in_rec.class_category,
1220 FALSE,
1221 FALSE,
1222 x_return_status);
1223
1224 --check_err(x_return_status);
1225
1226 -- owner_table is a mandatory column
1227 check_mandatory_str_col(
1228 create_update_flag,
1229 'owner_table',
1230 p_in_rec.owner_table,
1231 FALSE,
1232 FALSE,
1233 x_return_status);
1234
1235 --check_err(x_return_status);
1236
1237 --Bug 2861251: Column name should accept null. In classification UI,
1238 --the column_name is getting removed.
1239 -- column_name is a conditional mandatory column
1240 -- For HZ_PARTIES, the column_name column is mandatory
1241 --IF UPPER(p_in_rec.owner_table) = 'HZ_PARTIES' THEN
1242 --check_mandatory_str_col(
1243 -- create_update_flag,
1244 -- 'column_name',
1245 -- p_in_rec.column_name,
1246 -- FALSE,
1247 -- FALSE,
1248 -- x_return_status);
1249 --END IF;
1250
1251 --Bug 2890671: created_by_module is a mandatory column
1252 -- created_by_module is non-updateable, lookup
1253
1257 p_old_created_by_module => l_created_by_module,
1254 hz_utility_v2pub.validate_created_by_module(
1255 p_create_update_flag => create_update_flag,
1256 p_created_by_module => p_in_rec.created_by_module,
1258 x_return_status => x_return_status);
1259
1260 --check_err(x_return_status);
1261
1262
1263 -------------- Changes made as per HTML Admin Project ----------------
1264 -- Check to make sure table name exists in fnd_objects, pk1 is valid,
1265 -- and pk2 is null (assumption: subsequent pks are all null if pk2 is null)
1266
1267 -- Check valid lookup within the correct lookup_type
1268 -- validate_fnd_lookup(
1269 -- 'CODE_ASSIGN_OWNER_TABLE',
1270 -- 'owner_table',
1271 -- p_in_rec.owner_table,
1272 -- x_return_status);
1273
1274
1275 begin
1276
1277 --Bug 2861251: Column name should accept null. Added or condition to accept
1278 --null value to column_name
1279 select '1' into xx_obj
1280 from fnd_objects
1281 where obj_name = p_in_rec.owner_table --Bug NO.:4942331 SQLID:14450613
1282 and ( p_in_rec.column_name is null
1283 or nvl(pk1_column_name,'-999') = nvl(p_in_rec.column_name,'-999') );
1284
1285 exception
1286 when no_data_found then
1287 --Bug 2861251: Changed the message name from HZ_ADMIN_SQL_VALID_ERR
1288 --to HZ_API_INVALID_OBJ_NAME.
1289 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_OBJ_NAME');
1290 FND_MSG_PUB.ADD;
1291 x_return_status := fnd_api.G_RET_STS_ERROR;
1292 when others then
1293 null;
1294 end;
1295 ---------------- End of changes for HTML Admin Project ----------------
1296
1297 -- Check FK validation class_category on the hz_class_category
1298 check_existence_class_category(
1299 p_in_rec.class_category,
1300 x_return_status);
1301
1302 --check_err(x_return_status);
1303
1304 -- Check concatenated PK uniqueness (class_category, owner_table )
1305 --HYU
1306 IF ( existence_couple_clacat_owntab( create_update_flag,
1307 p_in_rec.class_category,
1308 p_in_rec.owner_table) = 'Y' ) THEN
1309 fnd_message.set_name('AR','HZ_API_USE_ONCE_OWNER_TABLE');
1310 fnd_message.set_token('CLASS_CATEGORY',p_in_rec.class_category);
1311 fnd_message.set_token('OWNER_TABLE' ,p_in_rec.owner_table);
1312 fnd_msg_pub.add;
1313 x_return_status := fnd_api.g_ret_sts_error;
1314 END IF;
1315
1316 --check_err(x_return_status);
1317 /* -- Bug 3962783
1318 EXCEPTION
1319 WHEN OTHERS THEN
1320 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1321 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1322 FND_MSG_PUB.ADD;
1323 x_return_status := fnd_api.G_RET_STS_ERROR;
1324 */
1325 END validate_class_category_use;
1326
1327
1328
1329
1330
1331 /*---------------------------------------------
1332 -- Validation for Hz_Class_Code_Assignments--
1333 ---------------------------------------------*/
1334 FUNCTION date_betw_value_dates
1335 -- Return 'Y' if p_date_active is between the active dates of the particular Class Code
1336 -- 'N' otherwise
1337 ( p_class_category IN VARCHAR2,
1338 p_class_code IN VARCHAR2,
1339 p_start_date_active IN DATE )
1340 RETURN VARCHAR2
1341 IS
1342 CURSOR cu0
1343 IS
1344 SELECT 'Y'
1345 FROM fnd_lookup_values
1346 WHERE lookup_type = p_class_category
1347 AND lookup_code = p_class_code
1348 AND NVL(end_date_active, p_start_date_active) >= p_start_date_active
1349 AND start_date_active <= p_start_date_active;
1350 l_yn VARCHAR2(1);
1351 result VARCHAR2(1);
1352 BEGIN
1353 OPEN cu0;
1354 FETCH cu0 INTO l_yn;
1355 IF cu0%NOTFOUND THEN
1356 result := 'N';
1357 ELSE
1358 result := 'Y';
1359 END IF;
1360 CLOSE cu0;
1361 RETURN result;
1362 END date_betw_value_dates;
1363
1364 FUNCTION instance_already_assigned
1365 -- Return 'Y' If for ( 1 entity, 1 instance, 1 category , 1 content source, 1 period ),
1366 -- we find at least 1 code different
1367 -- Return 'N' otherwise
1368
1369 -- SSM SST Integration and Extension
1370 -- Changed all reference from content_source_type to actual_content_source.
1371
1372 ( p_start_date_active DATE,
1373 p_end_date_active DATE,
1374 p_owner_table_name VARCHAR2,
1375 p_owner_table_id VARCHAR2,
1376 p_class_category VARCHAR2,
1377 p_content_source_type VARCHAR2,
1378 x_class_code IN OUT NOCOPY VARCHAR2,
1379 x_start_date_active IN OUT NOCOPY DATE,
1380 x_end_date_active IN OUT NOCOPY DATE)
1381 RETURN VARCHAR2
1382 IS
1383 CURSOR c0
1384 IS
1385 SELECT class_code,
1386 start_date_active,
1387 end_date_active
1388 FROM hz_code_assignments
1389 WHERE owner_table_name = p_owner_table_name
1390 AND owner_table_id = p_owner_table_id
1391 AND class_category = p_class_category
1392 AND actual_content_source = p_content_source_type
1393 AND ( NVL(end_date_active, p_start_date_active) >= p_start_date_active
1394 OR start_date_active <= NVL(p_end_date_active, start_date_active) )
1395 AND ROWNUM = 1;
1396 l_class_code VARCHAR2(30);
1400 BEGIN
1397 l_start_date_active DATE;
1398 l_end_date_active DATE;
1399 result VARCHAR2(1);
1401 result := 'N';
1402 OPEN c0;
1403 FETCH c0 INTO l_class_code, l_start_date_active, l_end_date_active;
1404 IF c0%NOTFOUND THEN
1405 result := 'N';
1406 ELSE
1407 x_class_code := l_class_code;
1408 x_start_date_active := l_start_date_active;
1409 x_end_date_active := l_end_date_active;
1410 result := 'Y';
1411 END IF;
1412 CLOSE c0;
1413 RETURN result;
1414 END instance_already_assigned;
1415
1416 FUNCTION is_leaf_node_category
1417 -- Return 'Y' if the Class Category entered has its ALLOW_LEAF_NODE_ONLY_FLAG to Y
1418 -- 'N' otherwise
1419 ( p_class_category IN VARCHAR2)
1420 RETURN VARCHAR2
1421 IS
1422 CURSOR c0
1423 IS
1424 SELECT allow_leaf_node_only_flag
1425 FROM hz_class_categories
1426 WHERE class_category = p_class_category;
1427 l_yn VARCHAR2(1);
1428 result VARCHAR2(1);
1429 BEGIN
1430 OPEN c0;
1431 FETCH c0 INTO l_yn;
1432 IF l_yn = 'Y' THEN
1433 result := 'Y';
1434 ELSE
1435 result := 'N';
1436 END IF;
1437 CLOSE c0;
1438 return result;
1439 END is_leaf_node_category;
1440
1441 FUNCTION is_categ_multi_assig
1442 -- Return 'Y' if the category has its allow_multi_assign_flag to Y
1443 -- 'N' otherwise
1444 ( p_class_category VARCHAR2)
1445 RETURN VARCHAR2
1446 IS
1447 CURSOR c0
1448 IS
1449 SELECT allow_multi_assign_flag
1450 FROM hz_class_categories
1451 WHERE class_category = p_class_category;
1452 result VARCHAR2(1);
1453 l_flag VARCHAR2(1);
1454 BEGIN
1455 OPEN c0;
1456 FETCH c0 INTO l_flag;
1457 IF l_flag = 'Y' THEN
1458 result := 'Y';
1459 ELSE
1460 result := 'N';
1461 END IF;
1462 CLOSE c0;
1463 RETURN result;
1464 END is_categ_multi_assig;
1465
1466 FUNCTION is_assig_record_id_valid
1467 -- Returns Y If the Record ID in the owner table associated with the category is valid
1468 -- and x_reason will content 'Table.column=value is valid against category.'
1469 -- Otherwise N and x_reason will content the message name to display
1470 -- HZ_API_USE_TAB_CAT if there is no usage between the category and the table
1471 -- HZ_API_CLA_CAT_WHERE if the value cannot be validate against the where_clause
1472 -- Standard Oracle error message otherwise
1473 ( p_owner_table_name IN VARCHAR2,
1474 p_owner_table_id IN VARCHAR2,
1475 p_class_category IN VARCHAR2,
1476 x_reason IN OUT NOCOPY VARCHAR2,
1477 x_column_name IN OUT NOCOPY VARCHAR2)
1478 RETURN VARCHAR2
1479 IS
1480 l_statement VARCHAR2(4000);
1481 l_text VARCHAR2(1000);
1482 l_result VARCHAR2(1);
1483 result VARCHAR2(1);
1484 BEGIN
1485 -- 1 Build the select statement
1486 l_result := sql_str_build( p_owner_table_name,
1487 p_owner_table_id ,
1488 p_class_category ,
1489 x_column_name ,
1490 l_statement );
1491 IF l_result = 'N' THEN
1492 result := 'N';
1493 x_reason := l_statement;
1494
1495 ELSE
1496 -- 2 Validation for the sql statement
1497 l_result := sql_valid( l_statement,
1498 l_text );
1499 IF l_result = 'N' THEN
1500 result := 'N';
1501 IF l_text = 'NON_VALUE' THEN
1502 x_reason := 'HZ_API_CLA_CAT_WHERE';
1503 -- Msg : p_owner_table_name.l_column_name = p_owner_table_id cannot be validate
1504 -- against the additional_where_clause of the usage between p_owner_table_name and p_class_category.
1505 ELSE
1506 x_reason := l_text;
1507 -- SQL Statement formed is wrong
1508 END IF;
1509 ELSE
1510 result := 'Y';
1511 x_reason := p_owner_table_name ||'.'||l_column_name||'='||p_owner_table_id||
1512 ' is valid against the '||p_class_category||' category.';
1513 END IF;
1514 END IF;
1515 RETURN result;
1516 END is_assig_record_id_valid;
1517
1518 FUNCTION sql_valid
1519 ( i_str IN VARCHAR2,
1520 x_result IN OUT NOCOPY VARCHAR2 )
1521 RETURN VARCHAR2
1522 IS
1523 i INTEGER;
1524 result VARCHAR2(1);
1525 row_proc INTEGER;
1526 row_fetch INTEGER;
1527 BEGIN
1528 i := DBMS_SQL.OPEN_CURSOR;
1529 DBMS_SQL.PARSE(i, i_str, DBMS_SQL.NATIVE);
1530 DBMS_SQL.DEFINE_COLUMN(i, 1, x_result, 1000 );
1531 row_proc := DBMS_SQL.EXECUTE(i);
1532 IF row_proc = 0 THEN
1533 row_fetch := DBMS_SQL.FETCH_ROWS(i);
1534 IF row_fetch <> 0 THEN
1535 DBMS_SQL.COLUMN_VALUE(i,1,x_result);
1536 result := 'Y';
1537 ELSE
1538 x_result := 'NON_VALUE';
1539 result := 'N';
1540 END IF;
1541 END IF;
1542 DBMS_SQL.CLOSE_CURSOR(i);
1543 RETURN(result);
1544 EXCEPTION
1545 WHEN OTHERS THEN
1546 x_result:= SUBSTRB(SQLERRM,1,100) ;
1547 result := 'N';
1548 DBMS_SQL.CLOSE_CURSOR(i);
1549 RETURN(result);
1550 END sql_valid;
1551
1552 FUNCTION sql_str_build
1553 ( p_owner_table_name IN VARCHAR2,
1554 p_owner_table_id IN VARCHAR2,
1558 RETURN VARCHAR2
1555 p_class_category IN VARCHAR2,
1556 x_column_name IN OUT NOCOPY VARCHAR2,
1557 x_statement IN OUT NOCOPY VARCHAR2)
1559 IS
1560 CURSOR c0
1561 IS
1562 SELECT column_name,
1563 additional_where_clause
1564 FROM hz_class_category_uses
1565 WHERE upper(class_category) = upper(p_class_category)
1566 AND upper(owner_table) = upper(p_owner_table_name);
1567 l_column_name VARCHAR2(240);
1568 l_add_where_clause VARCHAR2(4000);
1569 result VARCHAR2(1);
1570 BEGIN
1571 OPEN c0;
1572 FETCH c0 INTO l_column_name, l_add_where_clause;
1573 IF c0%NOTFOUND THEN
1574 result := 'N';
1575 x_statement := 'HZ_API_USE_TAB_CAT';
1576 -- Msg: HZ_API_USE_TAB_CAT = 'There is no usage for '||p_owner_table_name||' table in '||p_class_category||' category.'
1577 ELSE
1578 result := 'Y';
1579 x_column_name := l_column_name;
1580 l_add_where_clause := RTRIM(l_add_where_clause,FND_GLOBAL.LOCAL_CHR(10));
1581 l_add_where_clause := RTRIM(l_add_where_clause,FND_GLOBAL.LOCAL_CHR(32));
1582 l_add_where_clause := RTRIM(l_add_where_clause,';');
1583 x_statement := 'SELECT ' || l_column_name || ' FROM ' || p_owner_table_name || FND_GLOBAL.LOCAL_CHR(10);
1584 IF l_add_where_clause IS NOT NULL THEN
1585 x_statement := x_statement || l_add_where_clause || ' AND ';
1586 ELSE
1587 x_statement := x_statement || ' WHERE ';
1588 END IF;
1589 x_statement := x_statement || l_column_name || ' = ''' || p_owner_table_id || '''';
1590 END IF;
1591 CLOSE c0;
1592 --dbms_output.put_line(result);
1593 RETURN result;
1594 END sql_str_build;
1595
1596
1597 function exist_pk_code_assign
1598 -- Return 'Y' if one code_assignment_id is found for
1599 -- 1 owner_table,
1600 -- 1 owner_table_id
1601 -- 1 category
1602 -- 1 code
1603 -- 1 source content type
1604 -- 1 start date active
1605 -- 'N' otherwise
1606
1607 -- SSM SST Integration and Extension
1608 -- Changed references from content_source_type to actual_content_source
1609 (p_owner_table_name varchar2,
1610 p_owner_table_id varchar2,
1611 p_class_category varchar2,
1612 p_class_code varchar2,
1613 p_content_source_type varchar2,
1614 p_start_date_active date,
1615 x_id in out NOCOPY varchar2,
1616 x_end_date in out NOCOPY date)
1617 return varchar2
1618 is
1619 cursor c0
1620 is
1621 SELECT code_assignment_id,
1622 end_date_active
1623 FROM hz_code_assignments
1624 WHERE owner_table_name = p_owner_table_name
1625 AND owner_table_id = p_owner_table_id
1626 AND class_category = p_class_category
1627 AND class_code = p_class_code
1628 AND actual_content_source = p_content_source_type
1629 AND start_date_active = p_start_date_active;
1630 result varchar2(1);
1631 begin
1632 open c0;
1633 fetch c0 into x_id, x_end_date;
1634 if c0%found then
1635 result := 'Y';
1636 else
1637 result := 'N';
1638 end if;
1639 close c0;
1640 return result;
1641 end exist_pk_code_assign;
1642
1643 -- SSM SST Integration and Extension
1644 -- Changed references from content_source_type to actual_content_source
1645
1646 function exist_prim_assign
1647 ( create_update_flag varchar2,
1648 p_class_category varchar2,
1649 p_owner_table_name varchar2,
1650 p_owner_table_id varchar2,
1651 p_content_source_type varchar2,
1652 p_start_date_active date,
1653 p_end_date_active date,
1654 x_class_code in out NOCOPY varchar2,
1655 x_start_date in out NOCOPY date,
1656 x_end_date in out NOCOPY date )
1657 return varchar2
1658 is
1659 cursor c_create
1660 is
1661 SELECT class_code,
1662 start_date_active,
1663 end_date_active
1664 FROM hz_code_assignments
1665 WHERE owner_table_name = p_owner_table_name
1666 AND owner_table_id = p_owner_table_id
1667 AND class_category = p_class_category
1668 AND actual_content_source = p_content_source_type
1669 AND primary_flag = 'Y'
1670 AND hz_class_validate_v2pub.is_overlap(start_date_active, end_date_active,
1671 p_start_date_active, p_end_date_active) = 'Y';
1672 cursor c_update
1673 is
1674 SELECT class_code,
1675 start_date_active,
1676 end_date_active
1677 FROM hz_code_assignments
1678 WHERE owner_table_name = p_owner_table_name
1679 AND owner_table_id = p_owner_table_id
1680 AND class_category = p_class_category
1681 AND actual_content_source = p_content_source_type
1682 AND primary_flag = 'Y'
1683 AND start_date_active <> p_start_date_active
1684 AND hz_class_validate_v2pub.is_overlap(start_date_active, end_date_active,
1685 p_start_date_active, p_end_date_active) = 'Y';
1686 result varchar2(1);
1687 begin
1688 result := 'Y';
1689 if create_update_flag = 'C' then
1690 open c_create;
1691 fetch c_create into x_class_code, x_start_date, x_end_date;
1692 if c_create%notfound then
1693 result := 'N';
1694 end if;
1695 close c_create;
1696 elsif create_update_flag = 'U' then
1697 open c_update;
1698 fetch c_update into x_class_code, x_start_date, x_end_date;
1702 close c_update;
1699 if c_update%notfound then
1700 result := 'N';
1701 end if;
1703 end if;
1704 return result;
1705 end exist_prim_assign;
1706
1707 -- SSM SST Integration and Extension
1708 -- Changed references from content_source_type to actual_content_source
1709
1710 function exist_same_code_assign
1711 ( create_update_flag varchar2,
1712 p_class_category varchar2,
1713 p_class_code varchar2,
1714 p_owner_table_name varchar2,
1715 p_owner_table_id varchar2,
1716 p_content_source_type varchar2,
1717 p_start_date_active date,
1718 p_end_date_active date,
1719 x_class_code in out NOCOPY varchar2,
1720 x_start_date in out NOCOPY date,
1721 x_end_date in out NOCOPY date )
1722 return varchar2
1723 is
1724 cursor c_create
1725 is
1726 SELECT class_code,
1727 start_date_active,
1728 end_date_active
1729 FROM hz_code_assignments
1730 WHERE owner_table_name = p_owner_table_name
1731 AND owner_table_id = p_owner_table_id
1732 AND class_category = p_class_category
1733 AND class_code = p_class_code
1734 AND actual_content_source = p_content_source_type
1735 AND hz_class_validate_v2pub.is_overlap(start_date_active, end_date_active,
1736 p_start_date_active, p_end_date_active) = 'Y';
1737 cursor c_update
1738 is
1739 SELECT class_code,
1740 start_date_active,
1741 end_date_active
1742 FROM hz_code_assignments
1743 WHERE owner_table_name = p_owner_table_name
1744 AND owner_table_id = p_owner_table_id
1745 AND class_category = p_class_category
1746 AND class_code = p_class_code
1747 AND actual_content_source = p_content_source_type
1748 AND start_date_active <> p_start_date_active
1749 AND hz_class_validate_v2pub.is_overlap(start_date_active, end_date_active,
1750 p_start_date_active, p_end_date_active) = 'Y';
1751 result varchar2(1);
1752 begin
1753 result := 'Y';
1754 if create_update_flag = 'C' then
1755 open c_create;
1756 fetch c_create into x_class_code, x_start_date, x_end_date;
1757 if c_create%notfound then
1758 result := 'N';
1759 end if;
1760 close c_create;
1761 elsif create_update_flag = 'U' then
1762 open c_update;
1763 fetch c_update into x_class_code, x_start_date, x_end_date;
1764 if c_update%notfound then
1765 result := 'N';
1766 end if;
1767 close c_update;
1768 end if;
1769 return result;
1770 end exist_same_code_assign;
1771
1772 -- SSM SST Integration and Extension
1773 -- Changed references from content_source_type to actual_content_source
1774
1775 function exist_second_assign_same_code
1776 ( create_update_flag varchar2,
1777 p_class_category varchar2,
1778 p_class_code varchar2,
1779 p_owner_table_name varchar2,
1780 p_owner_table_id varchar2,
1781 p_content_source_type varchar2,
1782 p_start_date_active date,
1783 p_end_date_active date,
1784 x_class_code in out NOCOPY varchar2,
1785 x_start_date in out NOCOPY date,
1786 x_end_date in out NOCOPY date )
1787 return varchar2
1788 is
1789 cursor c_create
1790 is
1791 SELECT class_code,
1792 start_date_active,
1793 end_date_active
1794 FROM hz_code_assignments
1795 WHERE owner_table_name = p_owner_table_name
1796 AND owner_table_id = p_owner_table_id
1797 AND class_category = p_class_category
1798 AND class_code = p_class_code
1799 AND actual_content_source = p_content_source_type
1800 AND primary_flag = 'N'
1801 AND hz_class_validate_v2pub.is_overlap(start_date_active, end_date_active,
1802 p_start_date_active, p_end_date_active) = 'Y';
1803 cursor c_update
1804 is
1805 SELECT class_code,
1806 start_date_active,
1807 end_date_active
1808 FROM hz_code_assignments
1809 WHERE owner_table_name = p_owner_table_name
1810 AND owner_table_id = p_owner_table_id
1811 AND class_category = p_class_category
1812 AND class_code = p_class_code
1813 AND actual_content_source = p_content_source_type
1814 AND start_date_active <> p_start_date_active
1815 AND primary_flag = 'N'
1816 AND hz_class_validate_v2pub.is_overlap(start_date_active, end_date_active,
1817 p_start_date_active, p_end_date_active) = 'Y';
1818 result varchar2(1);
1819 begin
1820 result := 'Y';
1821 if create_update_flag = 'C' then
1822 open c_create;
1823 fetch c_create into x_class_code, x_start_date, x_end_date;
1824 if c_create%notfound then
1825 result := 'N';
1826 end if;
1827 close c_create;
1828 elsif create_update_flag = 'U' then
1829 open c_update;
1830 fetch c_update into x_class_code, x_start_date, x_end_date;
1831 if c_update%notfound then
1832 result := 'N';
1833 end if;
1834 close c_update;
1835 end if;
1836 return result;
1837 end exist_second_assign_same_code;
1838
1839 PROCEDURE cre_upd_code_ass_com
1840 ( p_create_update_flag varchar2,
1844 p_owner_table_id varchar2,
1841 p_class_category varchar2,
1842 p_class_code varchar2,
1843 p_owner_table_name varchar2,
1845 p_content_source_type varchar2,
1846 p_primary_flag varchar2,
1847 p_start_date_active date,
1848 p_end_date_active date,
1849 x_return_status IN OUT NOCOPY VARCHAR2 )
1850 IS
1851 l_class_code varchar2(30);
1852 l_start_date date;
1853 l_end_date date;
1854
1855 begin
1856
1857 IF p_primary_flag = 'Y' THEN
1858
1859 -- For (owner_table, table_id, source_content_type, category)
1860 -- just 1 tplet can have Primay_flag = 'Y' for 1 period
1861 if exist_prim_assign
1862 ( p_create_update_flag,
1863 p_class_category ,
1864 p_owner_table_name,
1865 p_owner_table_id ,
1866 p_content_source_type,
1867 p_start_date_active,
1868 p_end_date_active ,
1869 l_class_code ,
1870 l_start_date ,
1871 l_end_date ) = 'Y' then
1872
1873 fnd_message.set_name('AR' , 'HZ_API_DUP_COL_PRIM');
1874 fnd_message.set_token('CLASS_CODE', l_class_code);
1875 fnd_message.set_token('START_DATE_ACTIVE', l_start_date);
1876 fnd_message.set_token('END_DATE_ACTIVE' , l_end_date);
1877 fnd_msg_pub.add;
1878 x_return_status := fnd_api.g_ret_sts_error;
1879
1880 -- For (owner_table, table_id, source_content_type, category, code)
1881 -- the assignment cannot be primary and secondary in the same time
1882 elsif exist_second_assign_same_code
1883 ( p_create_update_flag,
1884 p_class_category ,
1885 p_class_code ,
1886 p_owner_table_name,
1887 p_owner_table_id ,
1888 p_content_source_type,
1889 p_start_date_active,
1890 p_end_date_active ,
1891 l_class_code ,
1892 l_start_date ,
1893 l_end_date ) = 'Y'
1894 then
1895 fnd_message.set_name('AR', 'HZ_API_DUP_COD_PRIM_SECOND');
1896 fnd_message.set_token('CLASS_CODE', l_class_code);
1897 fnd_message.set_token('START_DATE_ACTIVE', l_start_date);
1898 fnd_message.set_token('END_DATE_ACTIVE' , l_end_date);
1899 fnd_msg_pub.add;
1900 x_return_status := fnd_api.g_ret_sts_error;
1901 end if;
1902
1903 ELSIF p_primary_flag = 'N' THEN
1904
1905 if exist_same_code_assign
1906 ( p_create_update_flag,
1907 p_class_category,
1908 p_class_code ,
1909 p_owner_table_name,
1910 p_owner_table_id ,
1911 p_content_source_type,
1912 p_start_date_active,
1913 p_end_date_active ,
1914 l_class_code ,
1915 l_start_date ,
1916 l_end_date ) = 'Y'
1917 then
1918
1919 fnd_message.set_name('AR', 'HZ_API_DUP_COD_SECOND');
1920 fnd_message.set_token('CLASS_CODE', l_class_code);
1921 fnd_message.set_token('START_DATE_ACTIVE', l_start_date);
1922 fnd_message.set_token('END_DATE_ACTIVE' , l_end_date);
1923 fnd_msg_pub.add;
1924 x_return_status := fnd_api.g_ret_sts_error;
1925 end if;
1926
1927 END IF;
1928
1929 END cre_upd_code_ass_com;
1930
1931 -- SSM SST Integration and Extension
1932 -- Changed references from content_source_type to actual_content_source
1933
1934 procedure validate_code_assignment(
1935 p_in_rec IN HZ_CLASSIFICATION_V2PUB.CODE_ASSIGNMENT_REC_TYPE,
1936 create_update_flag IN VARCHAR2,
1937 x_return_status IN OUT NOCOPY VARCHAR2
1938 ) IS
1939 --l_date_active DATE;
1940 l_end_date DATE := NULL;
1941 l_count NUMBER := 0;
1942 l_id NUMBER;
1943 bool VARCHAR2(1);
1944 allow_leaf_error VARCHAR2(1);
1945
1946 -- bug 3077574 : Added two local variables used in validation sql statement
1947
1948 l_count_multi NUMBER := 0;
1949 l_allow_multi_assign_flag VARCHAR2(1);
1950
1951 -- bug 3077574 : Added a variable to store concatenated values for owner_table_key 1 to 5
1952
1953 l_owner_table_keys varchar2(2000);
1954
1955
1956
1957 l_class_category hz_code_assignments.class_category%TYPE;
1958 l_class_code hz_code_assignments.class_code%TYPE;
1959 l_status hz_code_assignments.status%TYPE;
1960 l_start_date_active hz_code_assignments.start_date_active%TYPE;
1961 l_end_date_active hz_code_assignments.end_date_active%TYPE;
1962 l_owner_table_name hz_code_assignments.owner_table_name%TYPE;
1963 l_owner_table_id hz_code_assignments.owner_table_id%TYPE;
1964 l_owner_table_key_1 hz_code_assignments.owner_table_key_1%TYPE;
1965 l_owner_table_key_2 hz_code_assignments.owner_table_key_2%TYPE;
1966 l_owner_table_key_3 hz_code_assignments.owner_table_key_3%TYPE;
1967 l_owner_table_key_4 hz_code_assignments.owner_table_key_4%TYPE;
1968 l_owner_table_key_5 hz_code_assignments.owner_table_key_5%TYPE;
1969 l_content_source_type hz_code_assignments.content_source_type%TYPE;
1970 l_actual_content_source hz_code_assignments.actual_content_source%TYPE;
1974 l_rec hz_code_assignments%ROWTYPE;
1971 l_created_by_module hz_code_assignments.created_by_module%TYPE;
1972
1973 l_primary_flag hz_code_assignments.primary_flag%TYPE;
1975
1976 -- Bug 3293069 - Added local variable to store end_date_active
1977
1978 l_date DATE;
1979
1980
1981 CURSOR c_code_assign(
1982 p_owner_table_name VARCHAR2,
1983 p_owner_table_id NUMBER,
1984 p_class_category VARCHAR2,
1985 --p_class_code VARCHAR2,
1986 p_content_source_type VARCHAR2
1987 )
1988 IS
1989 SELECT * FROM hz_code_assignments
1990 WHERE owner_table_name = p_owner_table_name AND
1991 owner_table_id = p_owner_table_id AND
1992 class_category = p_class_category AND
1993 status = 'A' AND
1994 --class_code = p_class_code AND
1995 actual_content_source = p_content_source_type;
1996
1997 /* Bug 3293069 - Commented the extra where clause so that the cursor picks up
1998 * past records also
1999 *
2000 AND
2001 (
2002 (end_date_active IS NULL) OR
2003 ( (end_date_active > SYSDATE)
2004 AND (end_date_active >= start_date_active)
2005 )
2006 );
2007
2008 */
2009 BEGIN
2010
2011 IF create_update_flag = 'U'
2012 THEN
2013
2014 SELECT class_category,
2015 class_code,
2016 status,
2017 start_date_active,
2018 end_date_active,
2019 owner_table_name,
2020 owner_table_id,
2021 owner_table_key_1,
2022 owner_table_key_2,
2023 owner_table_key_3,
2024 owner_table_key_4,
2025 owner_table_key_5,
2026 content_source_type,
2027 actual_content_source,
2028 created_by_module
2029 INTO l_class_category,
2030 l_class_code,
2031 l_status,
2032 l_start_date_active,
2033 l_end_date_active,
2034 l_owner_table_name,
2035 l_owner_table_id,
2036 l_owner_table_key_1,
2037 l_owner_table_key_2,
2038 l_owner_table_key_3,
2039 l_owner_table_key_4,
2040 l_owner_table_key_5,
2041 l_content_source_type,
2042 l_actual_content_source,
2043 l_created_by_module
2044 FROM hz_code_assignments
2045 WHERE code_assignment_id = p_in_rec.code_assignment_id
2046 AND rownum=1;
2047 END IF;
2048
2049 --Check for mandatory columns
2050 -- SHOULD ALLOW NULL?
2051 check_mandatory_num_col(
2052 create_update_flag,
2053 'code_assignment_id',
2054 p_in_rec.code_assignment_id,
2055 TRUE,
2056 FALSE, -- update needs this to select row
2057 x_return_status);
2058
2059 check_mandatory_str_col(
2060 create_update_flag,
2061 'owner_table_name',
2062 p_in_rec.owner_table_name,
2063 FALSE,
2064 TRUE,
2065 x_return_status);
2066
2067 --Commenting out as one and only one of the following is mandatory:
2068 --owner_table_id or owner_table_key_1
2069 --check_mandatory_num_col(
2070 -- create_update_flag,
2071 -- 'owner_table_id',
2072 -- p_in_rec.owner_table_id,
2073 -- FALSE,
2074 -- TRUE,
2075 -- x_return_status);
2076
2077 check_mandatory_str_col(
2078 create_update_flag,
2079 'class_category',
2080 p_in_rec.class_category,
2081 FALSE,
2082 TRUE,
2083 x_return_status);
2084
2085 check_mandatory_str_col(
2086 create_update_flag,
2087 'class_code',
2088 p_in_rec.class_code,
2089 FALSE,
2090 TRUE,
2091 x_return_status);
2092
2093 check_mandatory_str_col(
2094 create_update_flag,
2095 'primary_flag',
2096 p_in_rec.primary_flag,
2097 FALSE,
2098 TRUE,
2099 x_return_status);
2100
2101 /* check_mandatory_str_col(
2102 create_update_flag,
2103 'content_source_type',
2104 p_in_rec.content_source_type,
2105 FALSE,
2106 TRUE,
2107 x_return_status);*/
2108
2109 --Bug 2890671: created_by_module is manadatory
2110 -- created_by_module is non-updateable, lookup
2111
2112 hz_utility_v2pub.validate_created_by_module(
2113 p_create_update_flag => create_update_flag,
2117
2114 p_created_by_module => p_in_rec.created_by_module,
2115 p_old_created_by_module => l_created_by_module,
2116 x_return_status => x_return_status);
2118 -- Bug 3070461. Make start_date_active as non mandatory column.
2119 -- comment the code that checks mandatory column.
2120
2121 /***
2122 check_mandatory_date_col(
2123 create_update_flag,
2124 'start_date_active',
2125 p_in_rec.start_date_active,
2126 FALSE,
2127 TRUE,
2128 x_return_status);
2129
2130 ***/
2131
2132 --check_err( x_return_status );
2133
2134 --check for non-updatable columns
2135 --Bug 2825328: columns like owner_table_name, owner_table_id, and
2136 --owner_table_key_1 to 5 are non-updatable.
2137 -- owner_table_name is non-updateable field
2138 IF create_update_flag = 'U' AND
2139 p_in_rec.owner_table_name IS NOT NULL
2140 THEN
2141 validate_nonupdateable (
2142 p_column => 'owner_table_name',
2143 p_column_value => p_in_rec.owner_table_name,
2144 p_old_column_value => l_owner_table_name,
2145 x_return_status => x_return_status);
2146
2147 END IF;
2148 -- owner_table_id is non-updateable field
2149 IF create_update_flag = 'U' AND
2150 p_in_rec.owner_table_id IS NOT NULL
2151 THEN
2152 validate_nonupdateable (
2153 p_column => 'owner_table_id',
2154 p_column_value => p_in_rec.owner_table_id,
2155 p_old_column_value => l_owner_table_id,
2156 x_return_status => x_return_status);
2157
2158 END IF;
2159 -- owner_table_key_1 is non-updateable field
2160 IF create_update_flag = 'U' AND
2161 p_in_rec.owner_table_key_1 IS NOT NULL
2162 THEN
2163 validate_nonupdateable (
2164 p_column => 'owner_table_key_1',
2165 p_column_value => p_in_rec.owner_table_key_1,
2166 p_old_column_value => l_owner_table_key_1,
2167 x_return_status => x_return_status);
2168
2169 END IF;
2170 -- owner_table_name is non-updateable field
2171 IF create_update_flag = 'U' AND
2172 p_in_rec.owner_table_key_2 IS NOT NULL
2173 THEN
2174 validate_nonupdateable (
2175 p_column => 'owner_table_key_2',
2176 p_column_value => p_in_rec.owner_table_key_2,
2177 p_old_column_value => l_owner_table_key_2,
2178 x_return_status => x_return_status);
2179
2180 END IF;
2181 -- owner_table_name is non-updateable field
2182 IF create_update_flag = 'U' AND
2183 p_in_rec.owner_table_key_3 IS NOT NULL
2184 THEN
2185 validate_nonupdateable (
2186 p_column => 'owner_table_key_3',
2187 p_column_value => p_in_rec.owner_table_key_3,
2188 p_old_column_value => l_owner_table_key_3,
2189 x_return_status => x_return_status);
2190
2191 END IF;
2192 -- owner_table_name is non-updateable field
2193 IF create_update_flag = 'U' AND
2194 p_in_rec.owner_table_key_4 IS NOT NULL
2195 THEN
2196 validate_nonupdateable (
2197 p_column => 'owner_table_key_4',
2198 p_column_value => p_in_rec.owner_table_key_4,
2199 p_old_column_value => l_owner_table_key_4,
2200 x_return_status => x_return_status);
2201
2202 END IF;
2203 -- owner_table_name is non-updateable field
2204 IF create_update_flag = 'U' AND
2205 p_in_rec.owner_table_key_5 IS NOT NULL
2206 THEN
2207 validate_nonupdateable (
2208 p_column => 'owner_table_key_5',
2209 p_column_value => p_in_rec.owner_table_key_5,
2210 p_old_column_value => l_owner_table_key_5,
2211 x_return_status => x_return_status);
2212
2213 END IF;
2214
2215
2216
2217 -- Removing this validation as per discussions with Dylan
2218 -- Also, code assignment UI should look against HZ_CLASS_CATEGORY_USES
2219 -- and not go against this lookup.
2220
2221 --Check for lookup type validations.
2222 -- validate_fnd_lookup(
2223 -- 'CODE_ASSIGN_OWNER_TABLE',
2224 -- 'owner_table_name',
2225 -- p_in_rec.OWNER_TABLE_NAME,
2226 -- x_return_status);
2227 --- End of commenting ----------------------------
2228
2229 validate_fnd_lookup(
2230 'YES/NO',
2231 'primary_flag',
2232 p_in_rec.primary_flag,
2233 x_return_status);
2234
2235 /* SSM SST Integration and Extension
2236 * New Column actual_content_source is added.
2237 * Validations for both content_source_type and actual_content_source will be handled
2238 * in HZ_MIXNM_UTILITY.ValidateContentSource
2239
2240 validate_fnd_lookup(
2241 'CONTENT_SOURCE_TYPE',
2242 'content_source_type',
2243 p_in_rec.CONTENT_SOURCE_TYPE,
2244 x_return_status);
2245 */
2246
2250 ------------------------------------------------------------------
2247 ------------------------------------------------------------------
2248 -- Validation for content_source_type and actual_content_source --
2249 -- (SSM SST Integration and Extension) --
2251 HZ_MIXNM_UTILITY.ValidateContentSource(
2252 p_api_version => 'V2',
2253 p_create_update_flag => create_update_flag,
2254 p_check_update_privilege => 'N',
2255 p_content_source_type => p_in_rec.content_source_type,
2256 p_old_content_source_type => l_content_source_type,
2257 p_actual_content_source => p_in_rec.actual_content_source,
2258 p_old_actual_content_source => l_actual_content_source,
2259 p_entity_name => 'HZ_CODE_ASSIGNMENTS',
2260 x_return_status => x_return_status);
2261
2262 --IF l_actual_content_source <> p_in_rec.actual_content_source
2263 -- Bug 4226199 : call for update and for all ACS otehr than UE
2264 IF create_update_flag = 'U' and l_actual_content_source <> 'USER_ENTERED'
2265 THEN
2266 DECLARE
2267 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2268 BEGIN
2269
2270 validate_nonupdateable(
2271 p_column => 'CLASS_CATEGORY',
2272 p_column_value => p_in_rec.class_category,
2273 p_old_column_value => l_class_category,
2274 x_return_status => l_return_status,
2275 p_raise_error => 'N');
2276
2277 validate_nonupdateable(
2278 p_column => 'CLASS_CODE',
2279 p_column_value => p_in_rec.class_code,
2280 p_old_column_value => l_class_code,
2281 x_return_status => l_return_status,
2282 p_raise_error => 'N');
2283 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2284 HZ_MIXNM_UTILITY.CheckUserUpdatePrivilege(
2285 p_actual_content_source => l_actual_content_source,
2286 p_new_actual_content_source => p_in_rec.actual_content_source,
2287 p_entity_name => 'HZ_CODE_ASSIGNMENTS',
2288 x_return_status => x_return_status);
2289 -- Bug 4693719 : set global variable to Y
2290 HZ_UTILITY_V2PUB.G_UPDATE_ACS := 'Y';
2291 END IF;
2292 END;
2293 END IF;
2294 -- Status Validation
2295 hz_utility_v2pub.validate_lookup('status','AR_LOOKUPS','REGISTRY_STATUS',p_in_rec.status,x_return_status);
2296
2297 --check_err( x_return_status );
2298
2299 --Check FK validations.
2300 --Bug 2825247: The following condition should be checked only when create_update_flag is 'C'
2301
2302 /* Bug 3941471. Commented this code since the existence of class category is checked
2303 checking for ALLOW_MULTI_ASSIGN_FLAG */
2304
2305 /*
2306 IF create_update_flag = 'C'
2307 THEN
2308 SELECT COUNT(1) INTO l_count
2309 FROM hz_class_categories
2310 WHERE class_category = p_in_rec.class_category;
2311
2312 IF l_count = 0 THEN
2313 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
2314 FND_MESSAGE.SET_TOKEN('FK', 'class_category');
2315 FND_MESSAGE.SET_TOKEN('COLUMN', 'class_category');
2316 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_class_categories');
2317 FND_MSG_PUB.ADD;
2318 x_return_status := FND_API.G_RET_STS_ERROR;
2319
2320 RAISE G_EX_INVALID_PARAM;
2321 END IF;
2322 END IF;
2323 */
2324
2325 -- Bug 3077574 : Added validation for ALLOW_MULTI_ASSIGN_FLAG = 'N'
2326 -- Start of validation
2327
2328 Begin
2329
2330 -- Check if the flag is set to 'N'
2331
2332 select ALLOW_MULTI_ASSIGN_FLAG into l_allow_multi_assign_flag
2333 from hz_class_categories where
2334 -- Bug 3941471
2335 class_category = nvl(p_in_rec.class_category,l_class_category);
2336
2337 Exception
2338 When no_data_found then
2339 fnd_message.set_name('AR','HZ_API_INVALID_FK');
2340 fnd_message.set_token('FK','class_category');
2341 fnd_message.set_token('COLUMN','class_category');
2342 fnd_message.set_token('TABLE','hz_class_categories');
2343 fnd_msg_pub.add;
2344 x_return_status := fnd_api.g_ret_sts_error;
2345 end;
2346
2347 -- If the flag is set to 'N", do the validation
2348
2349 if(l_allow_multi_assign_flag = 'N') then
2350
2351 -- condition to handle null for start and end date while creating
2352
2353 if(create_update_flag = 'C') then
2354 l_end_date_active := nvl(p_in_rec.end_date_active,to_date('4712/12/31','YYYY/MM/DD'));
2355 l_start_date_active := nvl(p_in_rec.start_date_active,sysdate);
2356 end if;
2357
2358 l_owner_table_keys := nvl(p_in_rec.owner_table_key_1, l_owner_table_key_1) ||
2359 nvl(p_in_rec.owner_table_key_2, l_owner_table_key_2) ||
2360 nvl(p_in_rec.owner_table_key_3, l_owner_table_key_3) ||
2364 l_owner_table_id := nvl(p_in_rec.owner_table_id, l_owner_table_id);
2361 nvl(p_in_rec.owner_table_key_4, l_owner_table_key_4) ||
2362 nvl(p_in_rec.owner_table_key_5, l_owner_table_key_5);
2363 -- Bug 3455217 : Added to use in sql below.
2365 -- Bug 3455217 : Chagne the OR condition between owner_table_id and combination
2366 -- of owner_table_key_1 to owner_table_key_5 to union so that it
2367 -- uses index on these columns. Also add NVL conditions to
2368 -- preserve the functionality that only one of these two can be
2369 -- present for any code assignment.
2370 if(l_owner_table_id is not null) then
2371 select count(1) into l_count_multi
2372 from hz_code_assignments
2373 where class_category = p_in_rec.class_category
2374 AND status='A'
2375 AND code_Assignment_id <> nvl(p_in_rec.code_assignment_id, fnd_api.g_miss_num)
2376 AND owner_table_name = nvl(p_in_rec.owner_table_name, l_owner_table_name)
2377 /*
2378 AND ( owner_table_id = nvl(p_in_rec.owner_table_id, l_owner_table_id)
2379 OR
2380 ( owner_table_key_1 || owner_table_key_2 ||
2381 owner_table_key_3 || owner_table_key_4 ||
2382 owner_table_key_5 = l_owner_table_keys
2383 )
2384 )
2385 */
2386 AND ( owner_table_id = l_owner_table_id
2387 AND
2388 ( owner_table_key_1 || owner_table_key_2 ||
2389 owner_table_key_3 || owner_table_key_4 ||
2390 owner_table_key_5 is null
2391 )
2392 )
2393 -- Bug 3614582 : Removed TRUNC from the date comparison.
2394 AND is_overlap(nvl(p_in_rec.start_date_active, l_start_Date_active),
2395 nvl(p_in_rec.end_date_active,l_end_date_Active),
2396 START_DATE_ACTIVE, END_DATE_ACTIVE) = 'Y'
2397 /*
2398 AND ((START_DATE_ACTIVE) between (nvl(p_in_rec.start_date_active, l_start_Date_active)) and
2399 (decode(p_in_rec.end_date_active,
2400 fnd_api.g_miss_date,to_date('4712/12/31','YYYY/MM/DD'),
2401 NULL,l_end_date_active,p_in_rec.end_date_active)) OR
2402 (END_DATE_ACTIVE) between (nvl(p_in_rec.start_date_active,l_start_Date_active)) and
2403 (decode(p_in_rec.end_date_active,
2404 fnd_api.g_miss_date,to_date('4712/12/31','YYYY/MM/DD'),
2405 NULL,l_end_date_active,p_in_rec.end_date_active)) OR
2406 (nvl(p_in_rec.start_date_active,l_start_Date_active)) between (START_DATE_ACTIVE) and
2407 (nvl(END_DATE_ACTIVE, to_date('4712/12/31','YYYY/MM/DD'))) OR
2408 (nvl(p_in_rec.end_date_active,l_end_date_Active)) between (START_DATE_ACTIVE) and
2409 (nvl(END_DATE_ACTIVE, to_date('4712/12/31','YYYY/MM/DD')))
2410 )*/;
2411 else
2412 select count(1) into l_count_multi
2413 from hz_code_assignments
2414 where class_category = p_in_rec.class_category
2415 AND status='A'
2416 AND code_Assignment_id <> nvl(p_in_rec.code_assignment_id, fnd_api.g_miss_num)
2417 AND owner_table_name = nvl(p_in_rec.owner_table_name, l_owner_table_name)
2418 AND ( owner_table_id is null
2419 AND
2420 ( nvl(owner_table_key_1 || owner_table_key_2 ||
2421 owner_table_key_3 || owner_table_key_4 ||
2422 owner_table_key_5, fnd_api.g_miss_char) = nvl(l_owner_table_keys, fnd_api.g_miss_char)
2423 )
2424 )
2425 -- Bug 3614582 : Removed TRUNC from the date comparison.
2426 AND is_overlap(nvl(p_in_rec.start_date_active, l_start_Date_active),
2427 nvl(p_in_rec.end_date_active,l_end_date_Active),
2428 START_DATE_ACTIVE, END_DATE_ACTIVE) = 'Y'
2429 /*
2430 AND ((START_DATE_ACTIVE) between (nvl(p_in_rec.start_date_active, l_start_Date_active)) and
2431 (decode(p_in_rec.end_date_active,
2432 fnd_api.g_miss_date,to_date('4712/12/31','YYYY/MM/DD'),
2433 NULL,l_end_date_active,p_in_rec.end_date_active))
2434 OR
2435 (END_DATE_ACTIVE) between (nvl(p_in_rec.start_date_active,l_start_Date_active)) and
2436 (decode(p_in_rec.end_date_active,
2437 fnd_api.g_miss_date,to_date('4712/12/31','YYYY/MM/DD'),
2438 NULL,l_end_date_active,p_in_rec.end_date_active))
2439 OR
2443 (nvl(END_DATE_ACTIVE, to_date('4712/12/31','YYYY/MM/DD')))
2440 (nvl(p_in_rec.start_date_active,l_start_Date_active)) between (START_DATE_ACTIVE) and
2441 (nvl(END_DATE_ACTIVE, to_date('4712/12/31','YYYY/MM/DD'))) OR
2442 (nvl(p_in_rec.end_date_active,l_end_date_Active)) between (START_DATE_ACTIVE) and
2444 )*/
2445 ;
2446 end if;
2447
2448 if l_count_multi > 0 then
2449 FND_MESSAGE.SET_NAME('AR', 'HZ_API_ALLOW_MUL_ASSIGN_FG');
2450 FND_MSG_PUB.ADD;
2451 x_return_status := FND_API.G_RET_STS_ERROR;
2452 --Bug 3962783
2453 --RAISE G_EX_INVALID_PARAM;
2454 end if;
2455 end if;
2456
2457 -- end of validation
2458
2459
2460 --The following FK Validation check should be commented out, as this can be validated
2461 --from is_valid_category
2462 --IF UPPER(p_in_rec.owner_table_name) = 'HZ_PARTIES'
2463 --THEN
2464 --
2465 -- SELECT COUNT(1) INTO l_count
2466 -- FROM hz_parties
2467 -- WHERE party_id = p_in_rec.owner_table_id;
2468 --
2469 -- IF l_count = 0 THEN
2470 -- FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
2471 -- FND_MESSAGE.SET_TOKEN('FK', p_in_rec.owner_table_name);
2472 -- FND_MESSAGE.SET_TOKEN('COLUMN', p_in_rec.owner_table_id);
2473 -- FND_MESSAGE.SET_TOKEN('TABLE', 'hz_parties');
2474 -- FND_MSG_PUB.ADD;
2475 -- x_return_status := FND_API.G_RET_STS_ERROR;
2476 --
2477 -- RAISE G_EX_INVALID_PARAM;
2478 -- END IF;
2479 --END IF;
2480 --Bug 2830772: When the content_source_type not 'USER_ENTERED' and
2481 --lookup type is 'NACE', call the overloaded validate_fnd_lookup.
2482 IF( p_in_rec.actual_content_source <> 'USER_ENTERED'
2483 AND
2484 nvl(p_in_rec.class_category, l_class_category) = 'NACE'
2485 )
2486 THEN
2487 validate_fnd_lookup(
2488 nvl(p_in_rec.class_category, l_class_category),
2489 'class_code',
2490 p_in_rec.class_code,
2491 p_in_rec.actual_content_source,
2492 x_return_status);
2493 ELSE
2494 validate_fnd_lookup(
2495 nvl(p_in_rec.class_category, l_class_category),
2496 'class_code',
2497 p_in_rec.class_code,
2498 x_return_status);
2499 END IF;
2500
2501 --check_err( x_return_status );
2502
2503 IF create_update_flag = 'C' THEN
2504 -- Check start/end active dates
2505 check_start_end_active_dates(
2506 p_in_rec.start_date_active,
2507 p_in_rec.end_date_active,
2508 x_return_status);
2509 --check_err( x_return_status );
2510 END IF;
2511
2512 -- Assign Leafnode only Flag
2513 -- Bug 2689655. commented the previos code and added Validation for class_code based on allow_leaf_node_only_flag
2514
2515 /**
2516 BEGIN
2517 select decode(sign(count(*)-1),0,null,'Y')
2518 into allow_leaf_error
2519 from hz_class_code_relations c_rel
2520 , hz_class_categories c_cate
2521 where c_cate.class_category=c_rel.class_category and
2522 allow_leaf_node_only_flag = 'Y' and
2523 --owner_table_name = nvl(p_in_rec.owner_table_name, l_owner_table_name) AND
2524 --owner_table_id = nvl(p_in_rec.owner_table_id, l_owner_table_id) AND
2525 c_cate.class_category = nvl(p_in_rec.class_category, l_class_category) AND
2526 class_code = nvl(p_in_rec.class_code, l_class_code) AND
2527 --primary_flag = p_in_rec.primary_flag AND
2528 start_date_active = nvl(p_in_rec.start_date_active, l_start_date_active) AND
2529 (
2530 (p_in_rec.end_date_active IS NULL) OR
2531 ( (nvl(p_in_rec.end_date_active, l_end_date_active) > SYSDATE)
2532 AND (nvl(p_in_rec.end_date_active, l_end_date_active) >= start_date_active)
2533 )
2534 );
2535 if allow_leaf_error = 'Y' THEN
2536 -- update would produce duplicate records
2537 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
2538 FND_MESSAGE.SET_TOKEN('COLUMN', 'code_assignment_id');
2539 FND_MSG_PUB.ADD;
2540 x_return_status := FND_API.G_RET_STS_ERROR;
2541 RAISE G_EX_INVALID_PARAM;
2542 END IF;
2543 EXCEPTION
2544 WHEN TOO_MANY_ROWS THEN
2545 NULL; -- should not happen here
2546 WHEN NO_DATA_FOUND THEN
2547 l_count := 0;
2548 END;
2549
2550 **/
2551
2555 from hz_class_code_relations c_rel, hz_class_categories c_cate
2552 -- START validation
2553 Begin
2554 select 'Y' into allow_leaf_error
2556 where c_cate.class_category=c_rel.class_category and
2557 allow_leaf_node_only_flag = 'Y' and
2558 c_cate.class_category = nvl(p_in_rec.class_category, l_class_category) AND
2559 class_code = nvl(p_in_rec.class_code, l_class_code) AND
2560 ( start_date_active between nvl(p_in_rec.start_date_active, l_start_date_active) and nvl (p_in_rec.end_date_active, l_end_date_active)
2561 OR
2562 nvl(p_in_rec.start_date_active, l_start_date_active) between start_date_active and nvl(end_date_active, to_date('4712/12/31','YYYY/MM/DD'))
2563 ) and
2564 rownum = 1;
2565 if allow_leaf_error = 'Y' THEN
2566 FND_MESSAGE.SET_NAME('AR', 'HZ_API_LEAFNODE_FLAG');
2567 FND_MSG_PUB.ADD;
2568 x_return_status := FND_API.G_RET_STS_ERROR;
2569 --Bug 3962783
2570 --RAISE G_EX_INVALID_PARAM;
2571 end if;
2572 exception
2573 when no_data_found then
2574 l_count := 0;
2575 when others then
2576 NULL;
2577 end;
2578
2579 -- End validation
2580
2581
2582 -- Check uniqueness and updateable
2583
2584 BEGIN
2585
2586 SELECT code_assignment_id, end_date_active
2587 INTO l_id, l_end_date
2588 FROM hz_code_assignments
2589 WHERE owner_table_name = nvl(p_in_rec.owner_table_name, l_owner_table_name) AND
2590 owner_table_id = nvl(p_in_rec.owner_table_id, l_owner_table_id) AND
2591 class_category = nvl(p_in_rec.class_category, l_class_category) AND
2592 class_code = nvl(p_in_rec.class_code, l_class_code) AND
2593 --primary_flag = p_in_rec.primary_flag AND
2594 actual_content_source = nvl(p_in_rec.actual_content_source, l_actual_content_source) AND
2595 status ='A' AND
2596 -- Bug 3614582 : Removed TRUNC from the date comparison.
2597 (start_date_active) = (nvl(p_in_rec.start_date_active, l_start_date_active)) AND--Bug no 3053541
2598 (
2599 (
2600 (p_in_rec.end_date_active) IS NULL--Bug no 3053541
2601 )
2602 OR
2603 (
2604 (nvl(p_in_rec.end_date_active, l_end_date_active)) --Bug no 3053541
2605 > SYSDATE
2606 AND
2607 (nvl(p_in_rec.end_date_active, l_end_date_active))--Bug no 3053541
2608 >= start_date_active
2609 )
2610 )
2611 AND --Bug no 3053541
2612 (
2613 code_assignment_id <> p_in_rec.code_assignment_id
2614 OR
2615 create_update_flag='C'
2616 );
2617
2618 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
2619 FND_MESSAGE.SET_TOKEN('COLUMN', 'owner_table_name-owner_table_id-class_category-class_code-actual_content_source-start_date_active');
2620 FND_MSG_PUB.ADD;
2621 x_return_status := FND_API.G_RET_STS_ERROR;
2622 --Bug 3962783
2623 --RAISE G_EX_INVALID_PARAM;
2624
2625 --Commented the code below in the fix for Bug number 3053541
2626 /*
2627 l_count := 1;
2628 --Bug 2977428 : Changed the condition for unique combination columns
2629 --to check only when create_update_flag = 'C'.
2630 IF create_update_flag = 'C'
2631 THEN
2632 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
2633 FND_MESSAGE.SET_TOKEN('COLUMN', 'owner_table_name-owner_table_id-class_category-class_code-content_source_type-start_date_active');
2634 FND_MSG_PUB.ADD;
2635 x_return_status := FND_API.G_RET_STS_ERROR;
2636 RAISE G_EX_INVALID_PARAM;
2637 ELSIF l_id <>p_in_rec.code_assignment_id
2638 THEN
2639 -- update would produce duplicate records
2640 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
2641 FND_MESSAGE.SET_TOKEN('COLUMN', 'code_assignment_id');
2642 FND_MSG_PUB.ADD;
2643 x_return_status := FND_API.G_RET_STS_ERROR;
2644 RAISE G_EX_INVALID_PARAM;
2645 END IF;*/
2646 --End of code commented in the fix for Bug number 3053541.
2647
2648 EXCEPTION
2649 WHEN TOO_MANY_ROWS THEN
2650 NULL; -- should not happen here
2651 WHEN NO_DATA_FOUND THEN
2652 l_count := 0;
2653 END;
2654
2655 IF create_update_flag = 'U' THEN
2659 /*
2656 -- updating "end_date_active" allowed if:
2657 -- (1) it terminates the relation, OR
2658 -- (2) the current end_date_active is NULL
2660 -- (2) it does NOT revive a terminated relation AND
2661 -- the resulted (start_date_active, end_date_active)
2662 -- does not overlap with thoese of existing relations
2663 */
2664
2665 SELECT primary_flag
2666 INTO l_primary_flag
2667 FROM hz_code_assignments
2668 WHERE code_assignment_id = p_in_rec.code_assignment_id;
2669
2670 --
2671 --- Bugfix:2154581
2672 --
2673 -- Check start/end active dates
2674 check_start_end_active_dates(
2675 nvl(p_in_rec.start_date_active, l_start_date_active),
2676 nvl(p_in_rec.end_date_active, l_end_date_active),
2677 x_return_status);
2678 --check_err( x_return_status );
2679 --
2680 IF p_in_rec.end_date_active <> FND_API.G_MISS_DATE AND
2681 -- Bug 3293069 - Added bracket around the "AND" condition
2682 (
2683 (nvl(p_in_rec.end_date_active, l_end_date_active) <=
2684 nvl(p_in_rec.start_date_active, l_start_date_active) ) OR
2685 (nvl(p_in_rec.end_date_active, l_end_date_active) <= SYSDATE)
2686 )
2687 THEN
2688 -- terminating, allowed
2689 NULL;
2690 ELSE
2691 -- end_date_active is either NULL or > SYSDATE
2692 /* Bug 2450637 : Adding the validation for
2693 overlap */
2694
2695 /* IF ( l_count>0 ) AND (l_end_date IS NOT NULL)
2696 AND (l_end_date <> p_in_rec.end_date_active)
2697 THEN
2698 -- cannot update
2699 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
2700 FND_MESSAGE.SET_TOKEN('COLUMN', 'end_date_active');
2701 FND_MSG_PUB.ADD;
2702 x_return_status := FND_API.G_RET_STS_ERROR;
2703 RAISE G_EX_INVALID_PARAM;
2704 END IF;
2705 */
2706 -- While debugging for Bug No 3053541,found that if the API is called to
2707 -- update a record and either or all of the below values(owner_table_name,
2708 -- owner_table_id,class_category,sontent_source_type)are not passed to the
2709 -- API ,then the CURSOR is called with NULL as some parameters and as such
2710 -- the cursor does not return any value.
2711 -- Commented the cursor call and replaced it with the code below it.
2712 /* FOR v_rec IN c_code_assign(
2713 p_in_rec.owner_table_name,
2714 p_in_rec.owner_table_id,
2715 p_in_rec.class_category,
2716 p_in_rec.content_source_type
2717 )
2718 */
2719 FOR v_rec IN c_code_assign(
2720 nvl(p_in_rec.owner_table_name,l_owner_table_name),
2721 nvl(p_in_rec.owner_table_id,l_owner_table_id),
2722 nvl(p_in_rec.class_category,l_class_category),
2723 nvl(p_in_rec.actual_content_source,l_actual_content_source)
2724 )
2725 LOOP
2726 IF (v_rec.PRIMARY_FLAG = 'Y') AND
2727 (p_in_rec.primary_flag = 'Y' OR
2728 (l_primary_flag = 'Y' and p_in_rec.primary_flag is null)) AND
2729 (v_rec.code_assignment_id <> p_in_rec.code_assignment_id) AND
2730 is_overlap(nvl(p_in_rec.start_date_active, l_start_date_active),
2731 nvl(p_in_rec.end_date_active, l_end_date_active),
2732 v_rec.start_date_active,
2733 v_rec.end_date_active)='Y'
2734 THEN
2735 -- AN overlapping ONE EXISTS
2736
2737 -- Bug 3021505.
2738 --FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
2739 --FND_MESSAGE.SET_TOKEN('COLUMN', 'end_date_active');
2740 FND_MESSAGE.SET_NAME('AR','HZ_API_PRI_CODE_OVERLAP');
2741
2742 FND_MSG_PUB.ADD;
2743 x_return_status := FND_API.G_RET_STS_ERROR;
2744 --Bug 3962783
2745 --RAISE G_EX_INVALID_PARAM;
2746 EXIT;
2747 END IF;
2748
2749 -- Bug 3293069 : Check if the end_date_active is fnd_api.g_miss_date.
2750 -- If yes, use to_date('4712/12/31','YYYY/MM/DD') for checking overlap
2751
2752 if(p_in_rec.end_date_active = fnd_api.g_miss_date) then
2753 l_date := to_date('4712/12/31','YYYY/MM/DD');
2754 else
2758 IF (v_rec.class_code=p_in_rec.class_code) AND --Bug no 3053541
2755 l_date := p_in_rec.end_date_active;
2756 end if;
2757
2759 (v_rec.code_assignment_id <> p_in_rec.code_assignment_id) AND
2760
2761 -- Bug 3293069 : Use l_date instead of p_in_rec.end_date_active for checking overlap
2762
2763 is_overlap(nvl(p_in_rec.start_date_active, l_start_date_active),
2764 nvl(l_date, l_end_date_active),
2765 v_rec.start_date_active,
2766 v_rec.end_date_active)='Y'
2767 THEN
2768 FND_MESSAGE.SET_NAME('AR', 'HZ_IMP_CODE_ASSG_DATE_OVERLAP');
2769 FND_MSG_PUB.ADD;
2770 x_return_status := FND_API.G_RET_STS_ERROR;
2771 --Bug 3962783
2772 --RAISE G_EX_INVALID_PARAM;
2773 END IF;--Bug No 3053541.
2774 END LOOP;
2775
2776 /*
2777 * FOR v_rec IN c_code_assign(
2778 * p_in_rec.owner_table_name,
2779 * p_in_rec.owner_table_id,
2780 * p_in_rec.class_category,
2781 * p_in_rec.class_code,
2782 * p_in_rec.content_source_type
2783 * )
2784 * LOOP
2785 * IF (v_rec.start_date_active = p_in_rec.start_date_active )
2786 * THEN
2787 * -- reviving?
2788 * IF (v_rec.end_date_active <= v_rec.start_date_active ) OR
2789 * (v_rec.end_date_active <= l_now )
2790 * THEN
2791 * FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
2792 * FND_MESSAGE.SET_TOKEN('COLUMN', 'end_date_active');
2793 * FND_MSG_PUB.ADD;
2794 * x_return_status := FND_API.G_RET_STS_ERROR;
2795 * RAISE G_EX_INVALID_PARAM;
2796 * END IF;
2797 * ELSIF is_between(v_rec.start_date_active, p_in_rec.start_date_active, p_in_rec.end_date_active ) OR
2798 * is_between(p_in_rec.start_date_active, v_rec.start_date_active, v_rec.end_date_active )
2799 * THEN
2800 * -- overlaps with this existing relation
2801 * FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
2802 * FND_MESSAGE.SET_TOKEN('COLUMN', 'end_date_active');
2803 * FND_MSG_PUB.ADD;
2804 * x_return_status := FND_API.G_RET_STS_ERROR;
2805 * RAISE G_EX_INVALID_PARAM;
2806 * END IF;
2807 * END LOOP;
2808 * */
2809 END IF;
2810 ELSE
2811 -- create
2812 IF (p_in_rec.primary_flag = 'Y')
2813 THEN
2814 -- create primary code assignment
2815 FOR v_rec IN c_code_assign(
2816 p_in_rec.owner_table_name,
2817 p_in_rec.owner_table_id,
2818 p_in_rec.class_category,
2819 p_in_rec.actual_content_source
2820 )
2821 LOOP
2822 IF (v_rec.PRIMARY_FLAG = 'Y') AND
2823 (v_rec.class_code = p_in_rec.class_code) AND
2824 is_overlap(p_in_rec.start_date_active,
2825 p_in_rec.end_date_active,
2826 v_rec.start_date_active,
2827 v_rec.end_date_active)='Y'
2828 THEN
2829 -- AN overlapping ONE EXISTS
2830
2831 /* Bug 3289620.
2832 | FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
2833 | FND_MESSAGE.SET_TOKEN('COLUMN', 'class_category-class_code');
2834 */
2835 FND_MESSAGE.SET_NAME('AR', 'HZ_IMP_CODE_ASSG_DATE_OVERLAP');
2836
2837 FND_MSG_PUB.ADD;
2838 x_return_status := FND_API.G_RET_STS_ERROR;
2839 --Bug 3962783
2840 --RAISE G_EX_INVALID_PARAM;
2841 EXIT;
2842 END IF;
2843
2844 -- Bug 3293069 - Added end_date_active condition so that history records are not
2845 -- updated
2846 IF (v_rec.PRIMARY_FLAG = 'Y' AND
2850 THEN
2847 ((v_rec.end_date_active is NULL) OR
2848 (v_rec.end_date_active >= sysdate)) AND
2849 v_rec.class_code <> p_in_rec.class_code)
2851 -- terminate original primary assignment
2852 UPDATE HZ_CODE_ASSIGNMENTS SET
2853 end_date_active = SYSDATE
2854 WHERE code_assignment_id = v_rec.code_assignment_id;
2855 END IF;
2856
2857 -- Bug 3293069 - Added end_date_active condition so that history records are not
2858 -- updated
2859 IF (v_rec.PRIMARY_FLAG = 'N' AND
2860 ((v_rec.end_date_active is NULL) OR
2861 (v_rec.end_date_active >= sysdate)) AND
2862 v_rec.class_code = p_in_rec.class_code)
2863 THEN
2864 -- terminate original non-primary assignment
2865 UPDATE HZ_CODE_ASSIGNMENTS SET
2866 end_date_active = SYSDATE
2867 WHERE code_assignment_id = v_rec.code_assignment_id;
2868 END IF;
2869 END LOOP;
2870 ELSE
2871 -- create non-primary code assignment
2872 FOR v_rec IN c_code_assign(
2873 p_in_rec.owner_table_name,
2874 p_in_rec.owner_table_id,
2875 p_in_rec.class_category,
2876 p_in_rec.actual_content_source
2877 )
2878 LOOP
2879 IF (v_rec.class_code = p_in_rec.class_code)
2880 THEN
2881 -- Bug 3293069 - Added end_date_active condition so that history records are not
2882 -- updated
2883 IF (v_rec.PRIMARY_FLAG = 'Y'
2884 And ((v_rec.end_date_active is NULL) OR
2885 (v_rec.end_date_active >= sysdate))
2886 )
2887 THEN
2888 -- AN PRIMARY ONE EXISTS, terminate it
2889 UPDATE HZ_CODE_ASSIGNMENTS SET
2890 end_date_active = SYSDATE
2891 WHERE code_assignment_id = v_rec.code_assignment_id;
2892 ELSE
2893 -- AN NON-PRIMARY ONE EXISTS
2894 IF is_overlap(p_in_rec.start_date_active,
2895 p_in_rec.end_date_active,
2896 v_rec.start_date_active,
2897 v_rec.end_date_active) = 'Y'
2898 THEN
2899 -- overlaps with this existing one
2900
2901 /* Bug 3289620.
2902 | FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
2903 | FND_MESSAGE.SET_TOKEN('COLUMN', 'class_category-class_code');
2904 */
2905 FND_MESSAGE.SET_NAME('AR', 'HZ_IMP_CODE_ASSG_DATE_OVERLAP');
2906
2907
2908 FND_MSG_PUB.ADD;
2909 x_return_status := FND_API.G_RET_STS_ERROR;
2910 --Bug 3962783
2911 --RAISE G_EX_INVALID_PARAM;
2912 END IF;
2913 END IF;
2914 END IF;
2915 END LOOP;
2916 END IF;
2917 END IF;
2918
2919 --Validations for owner_table_id and owner_table_key_1 to owner_table_key_5
2920 --Bug 2825247: The following condition should be checked only when create_update_flag is 'C'
2921 IF create_update_flag = 'C'
2922 THEN
2923 IF ((p_in_rec.owner_table_id IS NOT NULL AND
2924 p_in_rec.owner_table_id <> FND_API.G_MISS_NUM) AND
2925 (p_in_rec.owner_table_key_1 IS NOT NULL AND
2926 p_in_rec.owner_table_key_1 <> FND_API.G_MISS_CHAR)) OR
2927 ((p_in_rec.owner_table_id IS NULL OR
2928 p_in_rec.owner_table_id = FND_API.G_MISS_NUM) AND
2929 (p_in_rec.owner_table_key_1 IS NULL OR
2930 p_in_rec.owner_table_key_1 = FND_API.G_MISS_CHAR))
2931 THEN
2932 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_COMBINATION2');
2933 FND_MESSAGE.SET_TOKEN('COLUMN1', 'owner_table_id');
2934 FND_MESSAGE.SET_TOKEN('COLUMN2', 'owner_table_key_1');
2938 --RAISE G_EX_INVALID_PARAM;
2935 FND_MSG_PUB.ADD;
2936 x_return_status := FND_API.G_RET_STS_ERROR;
2937 --Bug 3962783
2939 END IF;
2940 --If owner_table_key_1 is not supplied, then owner_table_key_2 cannot be supplied.
2941 IF ((p_in_rec.owner_table_key_1 IS NULL OR
2942 p_in_rec.owner_table_key_1 = FND_API.G_MISS_CHAR) AND
2943 (p_in_rec.owner_table_key_2 IS NOT NULL AND
2944 p_in_rec.owner_table_key_2 <> FND_API.G_MISS_CHAR))
2945 THEN
2946 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_COMBINATION1');
2947 FND_MESSAGE.SET_TOKEN('COLUMN1', 'owner_table_key_1');
2948 FND_MESSAGE.SET_TOKEN('COLUMN2', 'owner_table_key_2');
2949 FND_MSG_PUB.ADD;
2950 x_return_status := FND_API.G_RET_STS_ERROR;
2951 --Bug 3962783
2952 --RAISE G_EX_INVALID_PARAM;
2953 END IF;
2954
2955 --If owner_table_key_2 is not supplied, then owner_table_key_3 cannot be supplied.
2956 IF ((p_in_rec.owner_table_key_2 IS NULL OR
2957 p_in_rec.owner_table_key_2 = FND_API.G_MISS_CHAR) AND
2958 (p_in_rec.owner_table_key_3 IS NOT NULL AND
2959 p_in_rec.owner_table_key_3 <> FND_API.G_MISS_CHAR))
2960 THEN
2961 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_COMBINATION1');
2962 FND_MESSAGE.SET_TOKEN('COLUMN1', 'owner_table_key_2');
2963 FND_MESSAGE.SET_TOKEN('COLUMN2', 'owner_table_key_3');
2964 FND_MSG_PUB.ADD;
2965 x_return_status := FND_API.G_RET_STS_ERROR;
2966 --Bug 3962783
2967 --RAISE G_EX_INVALID_PARAM;
2968 END IF;
2969
2970 --If owner_table_key_3 is not supplied, then owner_table_key_4 cannot be supplied.
2971 IF ((p_in_rec.owner_table_key_3 IS NULL OR
2972 p_in_rec.owner_table_key_3 = FND_API.G_MISS_CHAR) AND
2973 (p_in_rec.owner_table_key_4 IS NOT NULL AND
2974 p_in_rec.owner_table_key_4 <> FND_API.G_MISS_CHAR))
2975 THEN
2976 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_COMBINATION1');
2977 FND_MESSAGE.SET_TOKEN('COLUMN1', 'owner_table_key_3');
2978 FND_MESSAGE.SET_TOKEN('COLUMN2', 'owner_table_key_4');
2979 FND_MSG_PUB.ADD;
2980 x_return_status := FND_API.G_RET_STS_ERROR;
2981 --Bug 3962783
2982 --RAISE G_EX_INVALID_PARAM;
2983 END IF;
2984
2985 --If owner_table_key_4 is not supplied, then owner_table_key_5 cannot be supplied.
2986 IF ((p_in_rec.owner_table_key_4 IS NULL OR
2987 p_in_rec.owner_table_key_4 = FND_API.G_MISS_CHAR) AND
2988 (p_in_rec.owner_table_key_5 IS NOT NULL AND
2989 p_in_rec.owner_table_key_5 <> FND_API.G_MISS_CHAR))
2990 THEN
2991 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_COMBINATION1');
2992 FND_MESSAGE.SET_TOKEN('COLUMN1', 'owner_table_key_4');
2993 FND_MESSAGE.SET_TOKEN('COLUMN2', 'owner_table_key_5');
2994 FND_MSG_PUB.ADD;
2995 x_return_status := FND_API.G_RET_STS_ERROR;
2996 --Bug 3962783
2997 --RAISE G_EX_INVALID_PARAM;
2998 END IF;
2999
3000 bool := HZ_CLASSIFICATION_V2PUB.IS_VALID_CATEGORY(
3001 p_owner_table => p_in_rec.owner_table_name,
3002 p_class_category => p_in_rec.class_category,
3003 p_id => p_in_rec.owner_table_id,
3004
3005 -- Bug 3077574 : Added p_key_1 parameter in the function call
3006
3007 p_key_1 => p_in_rec.owner_table_key_1,
3008 p_key_2 => p_in_rec.owner_table_key_2,
3009 p_key_3 => p_in_rec.owner_table_key_3,
3010 p_key_4 => p_in_rec.owner_table_key_4,
3011 p_key_5 => p_in_rec.owner_table_key_5
3012 );
3013
3014 IF bool='F' THEN
3015 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_PRIMARY_KEY');
3016 FND_MSG_PUB.ADD;
3017 x_return_status := FND_API.G_RET_STS_ERROR;
3018 --Bug 3962783
3019 --RAISE G_EX_INVALID_PARAM;
3020 END IF;
3021 END IF;
3022 /* -- Bug 3962783
3023 EXCEPTION
3024 WHEN OTHERS THEN
3025 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3026 -- Loop through to put the other error messages in fnd stack
3027 FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP
3028 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3029 FND_MSG_PUB.ADD;
3030 END LOOP;
3031 x_return_status := fnd_api.G_RET_STS_ERROR;
3032 */
3033 END;
3034
3035
3036
3037
3038 /*---------------------------------------
3039 -- Validate Hz_Class_Code_Relations --
3040 ---------------------------------------*/
3041 FUNCTION child_code
3042 -- Return Y if the p_class_code in the p_class_category for that period has one or more parent
3043 -- N otherwise
3044 (p_class_category VARCHAR2,
3045 p_class_code VARCHAR2,
3046 p_start_date_active DATE,
3047 p_end_date_active DATE,
3048 x_parent_code IN OUT NOCOPY VARCHAR2,
3049 x_start_date_active IN OUT NOCOPY DATE,
3050 x_end_date_active IN OUT NOCOPY DATE)
3051 RETURN VARCHAR2
3052 IS
3053 CURSOR c_par
3054 IS
3055 SELECT class_code,
3056 start_date_active,
3057 end_date_active
3058 FROM hz_class_code_relations
3059 WHERE class_category = p_class_category
3060 AND sub_class_code = p_class_code
3064 result VARCHAR2(1);
3061 AND hz_class_validate_v2pub.is_overlap(start_date_active, end_date_active,
3062 p_start_date_active, p_end_date_active)='Y'
3063 AND ROWNUM = 1;
3065 BEGIN
3066 OPEN c_par;
3067 FETCH c_par INTO x_parent_code, x_start_date_active, x_end_date_active;
3068 IF c_par%NOTFOUND THEN
3069 result := 'N';
3070 ELSE
3071 result := 'Y';
3072 END IF;
3073 CLOSE c_par;
3074 RETURN result;
3075 END child_code;
3076
3077 FUNCTION parent_code
3078 -- Return Y if the class code in the class category has already one parent
3079 -- N otherwise
3080 ( p_class_category VARCHAR2,
3081 p_class_code VARCHAR2,
3082 p_start_date_active DATE,
3083 p_end_date_active DATE,
3084 x_child_code IN OUT NOCOPY VARCHAR2,
3085 x_start_date_active IN OUT NOCOPY DATE,
3086 x_end_date_active IN OUT NOCOPY DATE)
3087 RETURN VARCHAR2
3088 IS
3089 result VARCHAR2(1);
3090 CURSOR c0
3091 IS
3092 SELECT start_date_active,
3093 end_date_active ,
3094 sub_class_code
3095 FROM hz_class_code_relations
3096 WHERE class_category = p_class_category
3097 AND class_code = p_class_code
3098 AND ( NVL(end_date_active, p_start_date_active) >= p_start_date_active
3099 OR start_date_active <= NVL(p_end_date_active, start_date_active) )
3100 AND ROWNUM = 1 ;
3101 BEGIN
3102 result := 'N';
3103 OPEN c0;
3104 FETCH c0 INTO x_start_date_active ,x_end_date_active, x_child_code ;
3105 IF c0%NOTFOUND THEN
3106 result := 'Y';
3107 ELSE
3108 result := 'N';
3109 END IF;
3110 close c0;
3111 RETURN result;
3112 END parent_code;
3113
3114 FUNCTION is_categ_multi_parent
3115 -- Return 'Y' if the category has its allow_multi_parent_flag to Y
3116 -- 'N' otherwise
3117 ( p_class_category VARCHAR2)
3118 RETURN VARCHAR2
3119 IS
3120 CURSOR c0
3121 IS
3122 SELECT allow_multi_parent_flag
3123 FROM hz_class_categories
3124 WHERE class_category = p_class_category;
3125 result VARCHAR2(1);
3126 l_flag VARCHAR2(1);
3127 BEGIN
3128 OPEN c0;
3129 FETCH c0 INTO l_flag;
3130 IF l_flag = 'Y' THEN
3131 result := 'Y';
3132 ELSE
3133 result := 'N';
3134 END IF;
3135 CLOSE c0;
3136 RETURN result;
3137 END is_categ_multi_parent;
3138
3139 FUNCTION previous_generation
3140 (in_tab in gen_list,
3141 in_class_category in varchar2,
3142 in_date_start in date,
3143 in_date_end in date default null,
3144 in_generation in number)
3145 return gen_list
3146 IS
3147 cursor c0(in_class_category in varchar2,
3148 l_class_code in varchar2,
3149 in_date_start in date,
3150 in_date_end in date)
3151 is
3152 select class_code,
3153 sub_class_code,
3154 start_date_active,
3155 end_date_active
3156 from hz_class_code_relations
3157 where class_category = in_class_category
3158 and sub_class_code = l_class_code
3159 and (hz_class_validate_v2pub.is_overlap
3160 (in_date_start, in_date_end,
3161 start_date_active, end_date_active)= 'Y');
3162
3163 ltab gen_list;
3164 lstart_date date;
3165 lend_date date;
3166 result gen_list;
3167 lrec c0%rowtype;
3168 cpt number;
3169 i number;
3170 j number;
3171
3172 begin
3173 i := 0;
3174 j := 0;
3175 cpt := in_tab.count;
3176
3177 -- initial dates
3178 lstart_date := in_date_start;
3179 lend_date := in_date_end;
3180
3181 loop
3182 i := i + 1;
3183 exit when i > cpt;
3184
3185 -- Use the narrowest interval of time
3186 if in_tab(i).end_date_active IS NOT NULL then
3187 if lend_date IS NULL then
3188 lend_date := in_tab(i).end_date_active;
3189 else
3190 if in_tab(i).end_date_active < lend_date then
3191 lend_date := in_tab(i).end_date_active;
3192 end if;
3193 end if;
3194 end if;
3195
3196 if in_tab(i).start_date_active IS NOT NULL then
3197 if in_tab(i).start_date_active > lstart_date then
3198 lstart_date := in_tab(i).start_date_active;
3199 end if;
3200 end if;
3201
3202 open c0(in_class_category,
3203 in_tab(i).class_code,
3204 lstart_date,
3205 lend_date);
3206 loop
3207 fetch c0 into lrec;
3208 exit when c0%notfound;
3209 j := j + 1;
3210 result(j).class_code := lrec.class_code;
3211 result(j).sub_class_code := lrec.sub_class_code;
3212 result(j).start_date_active := lrec.start_date_active;
3213 result(j).end_date_active := lrec.end_date_active;
3214 result(j).generation := in_generation;
3215 end loop;
3216
3217 close c0;
3218 end loop;
3219 return result;
3220
3221 end previous_generation;
3222
3223 FUNCTION next_generation
3224 (in_tab in gen_list,
3225 in_class_category in varchar2,
3229 return gen_list
3226 in_date_start in date,
3227 in_date_end in date default null,
3228 in_generation in number)
3230 IS
3231 cursor c0(in_class_category in varchar2,
3232 l_sub_class_code in varchar2,
3233 in_date_start in date,
3234 in_date_end in date)
3235 is
3236 select class_code,
3237 sub_class_code,
3238 start_date_active,
3239 end_date_active
3240 from hz_class_code_relations
3241 where class_category = in_class_category
3242 and class_code = l_sub_class_code
3243 and (hz_class_validate_v2pub.is_overlap
3244 (in_date_start, in_date_end,
3245 start_date_active, end_date_active)= 'Y');
3246
3247 ltab gen_list;
3248 lstart_date date;
3249 lend_date date;
3250 result gen_list;
3251 lrec c0%rowtype;
3252 cpt number;
3253 i number;
3254 j number;
3255
3256 begin
3257 i := 0;
3258 j := 0;
3259 cpt := in_tab.count;
3260
3261 -- initial dates
3262 lstart_date := in_date_start;
3263 lend_date := in_date_end;
3264
3265 loop
3266 i := i + 1;
3267 exit when i > cpt;
3268
3269 -- Use the narrowest interval of time
3270 if in_tab(i).end_date_active IS NOT NULL then
3271 if lend_date IS NULL then
3272 lend_date := in_tab(i).end_date_active;
3273 else
3274 if in_tab(i).end_date_active < lend_date then
3275 lend_date := in_tab(i).end_date_active;
3276 end if;
3277 end if;
3278 end if;
3279
3280 if in_tab(i).start_date_active IS NOT NULL then
3281 if in_tab(i).start_date_active > lstart_date then
3282 lstart_date := in_tab(i).start_date_active;
3283 end if;
3284 end if;
3285
3286 open c0(in_class_category,
3287 in_tab(i).sub_class_code,
3288 lstart_date,
3289 lend_date);
3290 loop
3291 fetch c0 into lrec;
3292 exit when c0%notfound;
3293 j := j + 1;
3294 result(j).class_code := lrec.class_code;
3295 result(j).sub_class_code := lrec.sub_class_code;
3296 result(j).start_date_active := lrec.start_date_active;
3297 result(j).end_date_active := lrec.end_date_active;
3298 result(j).generation := in_generation;
3299 end loop;
3300
3301 close c0;
3302 end loop;
3303 return result;
3304
3305 end next_generation;
3306
3307 FUNCTION tab_concatenated
3308 ( in_tab1 in gen_list,
3309 in_tab2 in gen_list)
3310 RETURN gen_list
3311 is
3312 result gen_list;
3313 i NUMBER;
3314 k NUMBER;
3315 j NUMBER;
3316 BEGIN
3317 i := in_tab1.count;
3318 k := in_tab2.count;
3319 j := 0;
3320 result := in_tab1;
3321 LOOP
3322 i := i + 1;
3323 j := j + 1;
3324 exit when j > k;
3325 result(i).class_code := in_tab2(j).class_code;
3326 result(i).sub_class_code := in_tab2(j).sub_class_code;
3327 result(i).start_date_active := in_tab2(j).start_date_active;
3328 result(i).end_date_active := in_tab2(j).end_date_active;
3329 result(i).generation := in_tab2(j).generation;
3330 END LOOP;
3331
3332 RETURN result;
3333 END tab_concatenated;
3334
3335 FUNCTION exist_rec_in_list_poc
3336 (in_tab in gen_list,
3337 in_rec in gen_rec,
3338 in_poc in VARCHAR2)
3339 RETURN VARCHAR2
3340 is
3341 i NUMBER;
3342 k NUMBER;
3343 test NUMBER;
3344 result VARCHAR2(1);
3345 BEGIN
3346 result := 'N';
3347 k := in_tab.count;
3348 i := 0;
3349 LOOP
3350 i := i + 1;
3351 exit when i > k;
3352 IF in_poc = 'CODE' THEN
3353 -- Code used for parents set
3354 IF ( (in_tab(i).class_code ) = (in_rec.class_code)
3355 -- and nvl(in_tab(i).sub_class_code,'@') = nvl(in_rec.sub_class_code,'@')
3356 -- and in_tab(i).start_date_active = in_rec.start_date_active
3357 )
3358 THEN
3359 result := 'Y';
3360 exit;
3361 END IF;
3362 ELSIF in_poc ='SUB' THEN
3363 -- Code used for children set
3364 IF (
3365 in_tab(i).sub_class_code = in_rec.sub_class_code
3366 )
3367 THEN
3368 result := 'Y';
3369 exit;
3370 END IF;
3371 END IF;
3372 END LOOP;
3373 RETURN result;
3374 END exist_rec_in_list_poc;
3375
3376 FUNCTION tab_normal_poc
3377 (in_tab in gen_list,
3378 in_poc in VARCHAR2)
3379 RETURN gen_list
3380 is
3381 i NUMBER;
3382 j NUMBER;
3383 k NUMBER;
3384 lrec gen_rec;
3385 result gen_list;
3386 BEGIN
3387 k := in_tab.count;
3388 i := 0;
3389 j := 0;
3390 LOOP
3391 i := i + 1;
3392 exit when i > k;
3393 IF in_poc = 'CODE' THEN
3394 -- Used for Parents set
3395 IF exist_rec_in_list_poc( result, in_tab(i),'CODE') = 'N' THEN
3396 j := j + 1;
3397 result(j) := in_tab(i);
3398 END IF;
3399 ELSIF in_poc = 'SUB' THEN
3403 result(j) := in_tab(i);
3400 -- Used for children set
3401 IF exist_rec_in_list_poc( result, in_tab(i),'SUB') = 'N' THEN
3402 j := j + 1;
3404 END IF;
3405 END IF;
3406 END LOOP;
3407 RETURN result;
3408 END tab_normal_poc;
3409
3410 FUNCTION set_of_parents
3411 (in_class_category in varchar2,
3412 in_class_code in varchar2,
3413 in_date_start in date,
3414 in_date_end in date default null)
3415 return gen_list
3416 IS
3417 result gen_list;
3418 ltab gen_list;
3419 i number;
3420 lcpt number;
3421 begin
3422 -- initialize ltab
3423 ltab(1).class_code := in_class_code;
3424 --dbms_output.put_line(ltab(1).class_code);
3425 i := 0;
3426 loop
3427 i := i + 1;
3428 exit when ltab.count = 0;
3429 ltab := previous_generation
3430 (ltab,
3431 in_class_category,
3432 in_date_start,
3433 in_date_end,
3434 i);
3435 ltab := tab_normal_poc(ltab,'CODE');
3436 result := tab_concatenated(result,ltab);
3437 end loop;
3438 result := tab_normal_poc(result,'CODE');
3439 return result;
3440 end set_of_parents;
3441
3442 FUNCTION set_of_children
3443 (in_class_category in varchar2,
3444 in_sub_class_code in varchar2,
3445 in_date_start in date,
3446 in_date_end in date default null)
3447 return gen_list
3448 IS
3449 result gen_list;
3450 ltab gen_list;
3451 i number;
3452 lcpt number;
3453 l_date_start date;
3454 l_date_end date;
3455 begin
3456 -- initialize ltab
3457 ltab(1).sub_class_code := in_sub_class_code;
3458 --dbms_output.put_line(ltab(1).sub_class_code);
3459 i := 0;
3460 loop
3461 i := i + 1;
3462 exit when ltab.count = 0;
3463 ltab := next_generation
3464 (ltab,
3465 in_class_category,
3466 in_date_start,
3467 in_date_end,
3468 i);
3469 ltab := tab_normal_poc(ltab,'SUB');
3470 result := tab_concatenated(result,ltab);
3471 end loop;
3472 result := tab_normal_poc(result,'SUB');
3473 return result;
3474 end set_of_children;
3475
3476 FUNCTION is_cod1_ancest_cod2
3477 (in_class_category in varchar2,
3478 in_class_code_1 in varchar2,
3479 in_class_code_2 in varchar2,
3480 in_date_start in date,
3481 in_date_end in date default null)
3482 return varchar2
3483 is
3484 ltab gen_list;
3485 result varchar2(1);
3486 i number;
3487 cpt number;
3488 begin
3489 result := 'N';
3490 ltab := set_of_parents
3491 (in_class_category,
3492 in_class_code_2 ,
3493 in_date_start ,
3494 in_date_end );
3495 i := 0;
3496 cpt := ltab.count;
3497 loop
3498 i := i + 1;
3499 exit when i > cpt;
3500 if ltab(i).class_code = in_class_code_1 then
3501 result := 'Y';
3502 exit;
3503 end if;
3504 end loop;
3505 return result;
3506 end is_cod1_ancest_cod2;
3507
3508 FUNCTION is_cod1_descen_cod2
3509 (in_class_category in varchar2,
3510 in_class_code_1 in varchar2,
3511 in_class_code_2 in varchar2,
3512 in_date_start in date,
3513 in_date_end in date default null)
3514 return varchar2
3515 is
3516 ltab gen_list;
3517 result varchar2(1);
3518 i number;
3519 cpt number;
3520 begin
3521 result := 'N';
3522 ltab := set_of_children
3523 (in_class_category,
3524 in_class_code_2 ,
3525 in_date_start ,
3526 in_date_end );
3527 i := 0;
3528 cpt := ltab.count;
3529 loop
3530 i := i + 1;
3531 exit when i > cpt;
3532 if ltab(i).sub_class_code = in_class_code_1 then
3533 result := 'Y';
3534 exit;
3535 end if;
3536 end loop;
3537 return result;
3538 end is_cod1_descen_cod2;
3539
3540 Function exist_pk_relation
3541 -- Return 'Y' if the relation Already exists
3542 -- 'N' otherwise
3543 ( p_class_category varchar2,
3544 p_class_code varchar2,
3545 p_sub_class_code varchar2,
3546 p_start_date_active date,
3547 x_end_date_active in out NOCOPY date)
3548 return varchar2
3549 IS
3550 cursor c0
3551 IS
3552 select end_date_active
3553 from hz_class_code_relations
3554 where class_category = p_class_category
3555 and class_code = p_class_code
3556 and sub_class_code = p_sub_class_code
3557 and start_date_active = p_start_date_active
3558 and rownum = 1;
3559 result varchar2(1);
3560 begin
3561 open c0;
3562 fetch c0 into x_end_date_active;
3563 if c0%notfound then
3564 result := 'N';
3565 else
3566 result := 'Y';
3567 end if;
3568 close c0;
3569 return result;
3570 end exist_pk_relation;
3571
3572 Function exist_overlap_relation
3573 -- returns 'Y' if it exists a relation which overlap the one we entered
3574 -- 'N' otehrwise
3575 ( p_create_update_flag varchar2,
3579 p_start_date_active date,
3576 p_class_category varchar2,
3577 p_class_code varchar2,
3578 p_sub_class_code varchar2,
3580 p_end_date_active date,
3581 x_start_date_active in out NOCOPY date,
3582 x_end_date_active in out NOCOPY date )
3583 Return varchar2
3584 is
3585 cursor c_create
3586 is
3587 select start_date_active,
3588 end_date_active
3589 from hz_class_code_relations
3590 where class_category = p_class_category
3591 and class_code = p_class_code
3592 and sub_class_code = p_sub_class_code
3593 and hz_class_validate_v2pub.is_overlap(start_date_active , end_date_active,
3594 p_start_date_active, p_end_date_active )= 'Y';
3595 cursor c_update
3596 is
3597 select start_date_active,
3598 end_date_active
3599 from hz_class_code_relations
3600 where class_category = p_class_category
3601 and class_code = p_class_code
3602 and sub_class_code = p_sub_class_code
3603 and start_date_active <> p_start_date_active
3604 and hz_class_validate_v2pub.is_overlap(start_date_active , end_date_active,
3605 p_start_date_active, p_end_date_active ) = 'Y';
3606 result varchar2(1);
3607 begin
3608 if p_create_update_flag = 'C' then
3609 open c_create;
3610 fetch c_create into x_start_date_active, x_end_date_active;
3611 if c_create%notfound then
3612 result := 'N';
3613 else
3614 result := 'Y';
3615 end if;
3616 close c_create;
3617 elsif p_create_update_flag = 'U' then
3618 open c_update;
3619 fetch c_update into x_start_date_active, x_end_date_active;
3620 if c_update%notfound then
3621 result := 'N';
3622 else
3623 result := 'Y';
3624 end if;
3625 close c_update;
3626 end if;
3627 return result;
3628 end exist_overlap_relation;
3629
3630 procedure validate_class_code_relation(
3631 p_in_rec IN HZ_CLASSIFICATION_V2PUB.CLASS_CODE_RELATION_REC_TYPE,
3632 create_update_flag IN VARCHAR2,
3633 x_return_status IN OUT NOCOPY VARCHAR2
3634 ) IS
3635 l_end_date DATE := NULL;
3636 l_count NUMBER := 0;
3637 l_end VARCHAR2(12);
3638 l_created_by_module hz_class_code_relations.created_by_module%TYPE;
3639 --Bug 4897711
3640 la_start DATE := to_date(NULL);
3641 la_end DATE := to_date(NULL);
3642 CURSOR c_code_rel(
3643 p_class_category VARCHAR2,
3644 p_class_code VARCHAR2,
3645 p_sub_class_code VARCHAR2)
3646 IS
3647 SELECT created_by_module
3648 FROM hz_class_code_relations
3649 WHERE class_category = p_class_category
3650 AND class_code = p_class_code
3651 AND sub_class_code = p_sub_class_code;
3652
3653 BEGIN
3654
3655 IF create_update_flag = 'U' THEN
3656 OPEN c_code_rel(
3657 p_in_rec.class_category, p_in_rec.class_code, p_in_rec.sub_class_code);
3658 FETCH c_code_rel INTO l_created_by_module;
3659 CLOSE c_code_rel;
3660 END IF;
3661
3662 --Check for mandatory columns
3663 check_mandatory_str_col(
3664 create_update_flag,
3665 'class_category',
3666 p_in_rec.class_category,
3667 FALSE,
3668 FALSE, -- cannot be missing: part of PK
3669 x_return_status);
3670
3671 check_mandatory_str_col(
3672 create_update_flag,
3673 'class_code',
3674 p_in_rec.class_code,
3675 FALSE,
3676 FALSE, -- cannot be missing: part of PK
3677 x_return_status);
3678
3679 check_mandatory_str_col(
3680 create_update_flag,
3681 'sub_class_code',
3682 p_in_rec.sub_class_code,
3683 FALSE,
3684 FALSE, -- cannot be missing: part of PK
3685 x_return_status);
3686
3687 -- Bug 3816590
3688 /*
3689 check_mandatory_date_col(
3690 create_update_flag,
3691 'start_date_active',
3692 p_in_rec.start_date_active,
3693 FALSE,
3694 FALSE, -- cannot be missing: part of PK
3695 x_return_status);
3696 */
3697
3698 --Bug 2890671: created_by_module field is mandatory
3699 -- created_by_module is non-updateable, lookup
3700
3701 hz_utility_v2pub.validate_created_by_module(
3702 p_create_update_flag => create_update_flag,
3703 p_created_by_module => p_in_rec.created_by_module,
3704 p_old_created_by_module => l_created_by_module,
3705 x_return_status => x_return_status);
3706
3707 --Check for lookup type validations.
3708 validate_fnd_lookup(p_in_rec.class_category,
3709 'class_code',
3710 p_in_rec.class_code,
3711 x_return_status);
3712 validate_fnd_lookup(p_in_rec.class_category,
3716
3713 'sub_class_code',
3714 p_in_rec.sub_class_code,
3715 x_return_status);
3717 --check_err( x_return_status );
3718
3719 ------------------------------------------------------
3720 ---- Validation for class code and sub clas code ----
3721 ------------------------------------------------------
3722 IF p_in_rec.class_code = p_in_rec.sub_class_code THEN
3723 fnd_message.set_name('AR', 'HZ_API_CLASS_CODE_VAL');
3724 fnd_msg_pub.add;
3725 x_return_status := fnd_api.g_ret_sts_error;
3726 --Bug 3962783
3727 --RAISE g_ex_invalid_param;
3728 END IF;
3729
3730
3731 --Check FK validations.
3732 --{HYU
3733 -- Existance of Class Category
3734 check_existence_class_category
3735 ( p_in_rec.class_category,
3736 x_return_status );
3737
3738 --check_err( x_return_status );
3739 --}
3740
3741 --{ HYU
3742 -- Recursive relations is not allowed
3743 -- Relation "Parent Code A " associated with "Child Code B"
3744 -- Code A should not have any descendent equals to Code B
3745 -- Code B should not have any ascendant equals to Code A
3746 IF (is_cod1_ancest_cod2( p_in_rec.class_category,
3747 p_in_rec.sub_class_code,
3748 p_in_rec.class_code ,
3749 p_in_rec.start_date_active,
3750 p_in_rec.end_date_active) = 'Y') THEN
3751 IF p_in_rec.end_date_active is null then
3752 l_end := 'Unspecified';
3753 ELSE
3754 l_end := to_char(p_in_rec.end_date_active,'DD-MON-RRRR');
3755 END IF;
3756 -- fnd_message.set_string( p_in_rec.sub_class_code ||
3757 -- ' has already been defined as ascendant of ' || p_in_rec.class_code ||
3758 -- ' for a period that overlaps the period started from ' || to_char(p_in_rec.start_date_active,'DD-MON-RRRR') ||
3759 -- ' to ' || l_end);
3760 -- fnd_msg_pub.add;
3761 --Bug 4897711 : Added error message
3762 fnd_message.set_name('AR', 'HZ_API_CIRCULAR_CODE_RELATION');
3763 fnd_message.set_token('CLASS_CODE1', p_in_rec.class_code);
3764 fnd_message.set_token('CLASS_CODE2', p_in_rec.sub_class_code);
3765 fnd_msg_pub.add;
3766 x_return_status := fnd_api.g_ret_sts_error;
3767 --Bug 3962783
3768 --RAISE g_ex_invalid_param;
3769
3770 ELSIF (is_cod1_descen_cod2( p_in_rec.class_category ,
3771 p_in_rec.class_code ,
3772 p_in_rec.sub_class_code ,
3773 p_in_rec.start_date_active,
3774 p_in_rec.end_date_active ) = 'Y') THEN
3775 IF p_in_rec.end_date_active is null then
3776 l_end := 'Unspecified';
3777 ELSE
3778 l_end := to_char(p_in_rec.end_date_active,'DD-MON-RRRR');
3779 END IF;
3780 -- fnd_message.set_string( p_in_rec.class_code ||
3781 -- ' has already been defined as descendant of ' || p_in_rec.sub_class_code ||
3782 -- ' for a period that overlaps the period started from ' || to_char(p_in_rec.start_date_active,'DD-MON-RRRR') ||
3783 -- ' to ' || l_end);
3784 -- fnd_msg_pub.add;
3785 --Bug 4897711 : Added error message
3786 fnd_message.set_name('AR', 'HZ_API_CIRCULAR_CODE_RELATION');
3787 fnd_message.set_token('CLASS_CODE1', p_in_rec.class_code);
3788 fnd_message.set_token('CLASS_CODE2', p_in_rec.sub_class_code);
3789 fnd_msg_pub.add;
3790 x_return_status := fnd_api.g_ret_sts_error;
3791 --Bug 3962783
3792 --RAISE g_ex_invalid_param;
3793 END IF;
3794 --}
3795
3796 if create_update_flag = 'C' then
3797 -- Check PK
3798 if (exist_pk_relation( p_in_rec.class_category,
3799 p_in_rec.class_code ,
3800 p_in_rec.sub_class_code,
3801 p_in_rec.start_date_active,
3802 l_end_date ) = 'Y' ) then
3803 fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
3804 fnd_message.set_token('COLUMN', 'class_category-class_code-sub_class_code-start_date_active');
3805 fnd_msg_pub.add;
3806 x_return_status := fnd_api.g_ret_sts_error;
3807 --Bug 3962783
3808 --RAISE g_ex_invalid_param;
3809 end if;
3810
3811 -- Check Date actives
3812 check_start_end_active_dates(
3813 p_in_rec.start_date_active,
3814 p_in_rec.end_date_active,
3815 x_return_status);
3816
3817 --check_err(x_return_status);
3818
3819 if exist_overlap_relation('C',
3820 p_in_rec.class_category,
3821 p_in_rec.class_code ,
3822 p_in_rec.sub_class_code,
3823 p_in_rec.start_date_active ,
3824 p_in_rec.end_date_active ,
3825 -- Bug 4897711
3826 la_start ,
3827 la_end ) = 'Y' then
3828 fnd_message.set_name('AR', 'HZ_API_CLASS_REL_OVERLAP');
3829 fnd_msg_pub.add;
3830 x_return_status := fnd_api.g_ret_sts_error;
3831 --Bug 3962783
3832 --RAISE g_ex_invalid_param;
3833 end if;
3834
3838 p_in_rec.start_date_active,
3835 if ( (is_categ_multi_parent(p_in_rec.class_category) = 'N' )
3836 AND (child_code(p_in_rec.class_category,
3837 p_in_rec.sub_class_code,
3839 p_in_rec.end_date_active ,
3840 l_class_code,
3841 l_start_date_active,
3842 l_end_date_active) = 'Y' ) ) then
3843 -- If Allowed_Multi_Parent_Flag = 'N' Then check that sub_code can only have one parent
3844 l_start := TO_CHAR(l_start_date_active, 'DD-MON-RRRR');
3845 IF l_end_date_active IS NULL THEN
3846 l_end := 'Unspecified';
3847 ELSE
3848 l_end := TO_CHAR(l_end_date_active, 'DD-MON-RRRR');
3849 END IF;
3850 fnd_message.set_name('AR', 'HZ_API_MULTI_PARENT_FORBID');
3851 fnd_message.set_token('CLASS_CATEGORY', p_in_rec.class_category);
3852 fnd_message.set_token('CLASS_CODE3' , p_in_rec.sub_class_code);
3853 fnd_message.set_token('CLASS_CODE2' , p_in_rec.class_code);
3854 fnd_message.set_token('CLASS_CODE1' , l_class_code);
3855 fnd_message.set_token('START1' , l_start_date_active);
3856 fnd_message.set_token('END1' , l_end_date_active );
3857 fnd_msg_pub.add;
3858 x_return_status := fnd_api.g_ret_sts_error;
3859 --Bug 3962783
3860 --RAISE g_ex_invalid_param;
3861 end if;
3865 -- Updating
3862
3863 end if;
3864
3866 -- Check end_date_active
3867 if create_update_flag = 'U' then
3868 if ( exist_pk_relation( p_in_rec.class_category,
3869 p_in_rec.class_code ,
3870 p_in_rec.sub_class_code,
3871 p_in_rec.start_date_active,
3872 l_end) = 'N') then
3873 -- Relation does not exist
3874 fnd_message.set_name('AR', 'HZ_API_REL_NOT_EXIST');
3875 fnd_message.set_token('COLUMN', 'start_date_active-end_date_active');
3876 fnd_msg_pub.add;
3877 x_return_status := fnd_api.g_ret_sts_error;
3878 --Bug 3962783
3879 --RAISE g_ex_invalid_param;
3880 end if;
3881
3882 -- Check Date actives
3883 check_start_end_active_dates(
3884 p_in_rec.start_date_active,
3885 p_in_rec.end_date_active,
3886 x_return_status);
3887
3888 --check_err(x_return_status);
3889
3890 if (exist_overlap_relation('U',
3891 p_in_rec.class_category,
3892 p_in_rec.class_code ,
3893 p_in_rec.sub_class_code,
3894 p_in_rec.start_date_active,
3895 p_in_rec.end_date_active,
3896 -- Bug 4897711
3897 la_start,
3898 la_end ) = 'Y') then
3899 -- Overlap relations are not allowed
3900 fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
3901 fnd_message.set_token('COLUMN', 'class_category-class_code-sub_class_code-start_date_active');
3902 fnd_msg_pub.add;
3903 x_return_status := fnd_api.g_ret_sts_error;
3904 --Bug 3962783
3905 --RAISE g_ex_invalid_param;
3906 end if;
3907 end if;
3908 /* -- Bug 3962783
3909 EXCEPTION
3910 WHEN OTHERS THEN
3911 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3912 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3913 FND_MSG_PUB.ADD;
3914 x_return_status := fnd_api.G_RET_STS_ERROR;
3915 */
3916 END validate_class_code_relation;
3917
3918 -----------------------------------------------------------------
3919 /**
3920 * PROCEDURE chk_exist_cls_catgry_type_code
3921 *
3922 * DESCRIPTION
3923 * This procedure is used to check existing record for class category type,
3924 * class coding, security group id, application id, and language combination
3925 * which are difined in FND_LOOKUP_VALUES_U1.
3926 *
3927 * ARGUMENTS
3928 * IN:
3929 * p_class_category_type Related to class category type column
3930 * p_class_category_code Related to class code column
3931 * p_security_group_id Rleated to security group id column
3932 * p_view_application_id Related to application id column
3933 *
3934 * IN/OUT:
3935 * x_return_status Return status after the call. The status can
3936 * be FND_API.G_RET_STS_ERROR (error)
3937 *
3938 * NOTES
3939 *
3940 * CREATION/MODIFICATION HISTORY
3941 *
3942 * 09-20-2007 Manivannan J o Created for Bug 6158794.
3943 */
3944 -----------------------------------------------------------------
3945
3946 PROCEDURE chk_exist_cls_catgry_type_code
3947 (p_class_category_type IN VARCHAR2,
3948 p_class_category_code IN VARCHAR2,
3949 p_security_group_id IN NUMBER,
3950 p_view_application_id IN NUMBER,
3951 x_return_status IN OUT NOCOPY VARCHAR2)
3952 IS
3953
3954 CURSOR c_exist_class_catgry_type_code(l_class_category_type VARCHAR2, l_class_category_code VARCHAR2, l_security_group_id NUMBER, l_view_application_id NUMBER)
3955 IS
3956 SELECT 'Y'
3957 FROM FND_LOOKUP_VALUES
3958 WHERE LOOKUP_TYPE = l_class_category_type
3959 AND LOOKUP_CODE = l_class_category_code
3960 AND SECURITY_GROUP_ID =l_security_group_id
3961 AND VIEW_APPLICATION_ID = l_view_application_id
3962 AND LANGUAGE = userenv('LANG')
3963 AND ROWNUM = 1;
3964
3965 l_exist VARCHAR2(1);
3966
3967 BEGIN
3968
3969 OPEN c_exist_class_catgry_type_code(p_class_category_type, p_class_category_code, p_security_group_id, p_view_application_id);
3970 FETCH c_exist_class_catgry_type_code INTO l_exist;
3971 IF c_exist_class_catgry_type_code%FOUND THEN
3972 fnd_message.set_name('AR','HZ_API_DUP_CLASS');
3973 fnd_msg_pub.add;
3974 x_return_status := fnd_api.g_ret_sts_error;
3975 END IF;
3976 CLOSE c_exist_class_catgry_type_code;
3977
3978 END chk_exist_cls_catgry_type_code;
3979
3980 -----------------------------------------------------------------
3981 /**
3982 * PROCEDURE chk_exist_clas_catgry_typ_mng
3983 *
3984 * DESCRIPTION
3985 * This procedure is used to check existing record for class category type,
3986 * class meaning, security group id, application id, and language combination
3987 * which are difined in FND_LOOKUP_VALUES_U2.
3988 *
3989 * ARGUMENTS
3990 * IN:
3991 * p_class_category_type Related to class category type column
3992 * p_class_category_meaning Related to class meaning column
3993 * p_security_group_id Rleated to security group id column
3994 * p_view_application_id Related to application id column
3995 *
3996 * IN/OUT:
3997 * x_return_status Return status after the call. The status can
3998 * be FND_API.G_RET_STS_ERROR (error)
3999 *
4000 * NOTES
4001 *
4002 * CREATION/MODIFICATION HISTORY
4003 *
4004 * 09-20-2007 Manivannan J o Created for Bug 6158794.
4005 */
4006 -----------------------------------------------------------------
4007
4008
4009 PROCEDURE chk_exist_clas_catgry_typ_mng
4010 (p_class_category_type IN VARCHAR2,
4011 p_class_category_meaning IN VARCHAR2,
4012 p_security_group_id IN NUMBER,
4013 p_view_application_id IN NUMBER,
4014 x_return_status IN OUT NOCOPY VARCHAR2)
4015 IS
4016
4017 CURSOR c_exist_clas_catgry_typ_mng(l_class_category_type VARCHAR2, l_class_category_meaning VARCHAR2, l_security_group_id NUMBER, l_view_application_id NUMBER)
4018 IS
4019 SELECT 'Y'
4020 FROM FND_LOOKUP_VALUES
4021 WHERE LOOKUP_TYPE = l_class_category_type
4022 AND MEANING = l_class_category_meaning
4023 AND SECURITY_GROUP_ID =l_security_group_id
4024 AND VIEW_APPLICATION_ID = l_view_application_id
4025 AND LANGUAGE = userenv('LANG')
4026 AND ROWNUM = 1;
4027
4028 l_exist VARCHAR2(1);
4029
4030 BEGIN
4031
4032 OPEN c_exist_clas_catgry_typ_mng(p_class_category_type, p_class_category_meaning, p_security_group_id, p_view_application_id);
4033 FETCH c_exist_clas_catgry_typ_mng INTO l_exist;
4034 IF c_exist_clas_catgry_typ_mng%FOUND THEN
4035 fnd_message.set_name('AR','HZ_API_DUP_CLASS_TYPE_MEANING');
4036 fnd_msg_pub.add;
4037 x_return_status := fnd_api.g_ret_sts_error;
4038 END IF;
4039 CLOSE c_exist_clas_catgry_typ_mng;
4040
4041 END chk_exist_clas_catgry_typ_mng;
4042
4043
4044 END HZ_CLASS_VALIDATE_V2PUB;