[Home] [Help]
PACKAGE BODY: APPS.ARH_CLASSIFICATION_PKG
Source
1 PACKAGE BODY ARH_CLASSIFICATION_PKG AS
2 /*$Header: ARCLAASB.pls 115.4 2002/12/30 18:21:38 hyu noship $*/
3
4 -----------------------------------
5 -- Local procedure and functions --
6 -----------------------------------
7 /*-----------------------------------------------------+
8 | Init_switch requires for forms |
9 | 3 over loaded structures for VARCHAR2 |
10 | NUMBER |
11 | DATE |
12 +-----------------------------------------------------*/
13 FUNCTION INIT_SWITCH
14 ( p_date IN DATE,
15 p_switch IN VARCHAR2 DEFAULT 'NULL_GMISS')
16 RETURN DATE
17 IS
18 res_date date;
19 BEGIN
20 IF p_switch = 'NULL_GMISS' THEN
21 IF p_date IS NULL THEN
22 res_date := FND_API.G_MISS_DATE;
23 ELSE
24 res_date := p_date;
25 END IF;
26 ELSIF p_switch = 'GMISS_NULL' THEN
27 IF p_date = FND_API.G_MISS_DATE THEN
28 res_date := NULL;
29 ELSE
30 res_date := p_date;
31 END IF;
32 ELSE
33 res_date := TO_DATE('31/12/1800','DD/MM/RRRR');
34 END IF;
35 RETURN res_date;
36 END;
37
38 FUNCTION INIT_SWITCH
39 ( p_char IN VARCHAR2,
40 p_switch IN VARCHAR2 DEFAULT 'NULL_GMISS')
41 RETURN VARCHAR2
42 IS
43 res_char varchar2(2000);
44 BEGIN
45 IF p_switch = 'NULL_GMISS' THEN
46 IF p_char IS NULL THEN
47 return FND_API.G_MISS_CHAR;
48 ELSE
49 return p_char;
50 END IF;
51 ELSIF p_switch = 'GMISS_NULL' THEN
52 IF p_char = FND_API.G_MISS_CHAR THEN
53 return NULL;
54 ELSE
55 return p_char;
56 END IF;
57 ELSE
58 return ('INCORRECT_P_SWITCH');
59 END IF;
60 END;
61
62 FUNCTION INIT_SWITCH
63 ( p_num IN NUMBER,
64 p_switch IN VARCHAR2 DEFAULT 'NULL_GMISS')
65 RETURN NUMBER
66 IS
67 BEGIN
68 IF p_switch = 'NULL_GMISS' THEN
69 IF p_num IS NULL THEN
70 return FND_API.G_MISS_NUM;
71 ELSE
72 return p_num;
73 END IF;
74 ELSIF p_switch = 'GMISS_NULL' THEN
75 IF p_num = FND_API.G_MISS_NUM THEN
76 return NULL;
77 ELSE
78 return p_num;
79 END IF;
80 ELSE
81 return ('9999999999');
82 END IF;
83 END;
84
85
86
87 /*----------------------------------------+
88 | local function compare |
89 | parameter |
90 | date1 date |
91 | date2 date |
92 | description |
93 | if date1 = date2 = NULL then return 2|
94 | if date1 = date2 then return 0 |
95 | if date1 > date2 then return 1 |
96 | if date1 < date2 then return -1 |
97 +----------------------------------------*/
98 FUNCTION compare(
99 date1 DATE,
100 date2 DATE)
101 RETURN NUMBER
102 IS
103 ldate1 date;
104 ldate2 date;
105 BEGIN
106 ldate1 := trunc(date1);
107 ldate2 := trunc(date2);
108 IF (ldate1 IS NULL AND ldate2 IS NULL) THEN
109 RETURN 2;
110 ELSIF (ldate2 IS NULL) THEN
111 RETURN -1;
112 ELSIF (ldate1 IS NULL) THEN
113 RETURN 1;
114 ELSIF ( ldate1 = ldate2 ) THEN
115 RETURN 0;
116 ELSIF ( ldate1 > ldate2 ) THEN
117 RETURN 1;
118 ELSE
119 RETURN -1;
120 END IF;
121 END compare;
122
123
124 ------------------------------------
125 -- Public procedure and functions --
126 ------------------------------------
127 /*-------------------------------------------------------+
128 | Name : is_between |
129 | |
130 | Description : |
131 | Check if datex is between date1 and date2 inclusively|
132 | or exclusive |
133 | INC = inclusive |
134 | EXC = Exclusive |
135 | |
136 | Parameter : |
137 | datex DATE |
138 | date1 DATE |
139 | date2 DATE |
140 | inc_exc1 VARCHAR2 in 'INC','EXC' |
141 | inc_exc2 VARCHAR2 in 'INC','EXC' |
142 | Return : |
143 | 'Y' if datex is between date1 and date2 |
144 | 'N' otherwise |
145 +-------------------------------------------------------*/
146 FUNCTION is_between
147 ( datex IN DATE,
148 date1 IN DATE,
149 date2 IN DATE,
150 inc_exc1 IN VARCHAR2 DEFAULT 'INC',
151 inc_exc2 IN VARCHAR2 DEFAULT 'INC')
152 RETURN VARCHAR2
153 IS
154 l_comp1 NUMBER;
155 l_comp2 NUMBER;
156 lres VARCHAR2(1);
157 BEGIN
158 l_comp1 := compare(datex, date1);
159 l_comp2 := compare(date2, datex);
160 IF l_comp1 = 2 OR l_comp2 = 2 THEN
161 lres := 'Y';
162 ELSIF l_comp1 = 0 THEN
163 IF inc_exc1 = 'INC' THEN
164 lres := 'Y';
165 ELSE
166 lres := 'N';
167 END IF;
168 ELSIF l_comp2 = 0 THEN
169 IF inc_exc2 = 'INC' THEN
170 lres := 'Y';
171 ELSE
172 lres := 'N';
173 END IF;
174 ELSE
175 IF l_comp1 = 1 and l_comp2 = 1 THEN
176 lres := 'Y';
177 ELSE
178 lres := 'N';
179 END IF;
180 END IF;
181 RETURN lres;
182 END is_between;
183
184
185 /*------------------------------------------------------+
186 | Name : is_overlap |
187 | |
188 | Description : |
189 | check if period (s1 e1) overlaps (s2 e2) |
190 | exclusive or inclusively |
191 | This function does not support s1 or s2 NULL |
192 | start_Date null does not have any business meaning |
193 | |
194 | Parameter : |
195 | s1 DATE |
196 | e1 DATE |
197 | s2 DATE |
198 | e2 DATE |
199 | inc_exc VARCHAR2 in 'INC', 'EXC' |
200 | Return : |
201 | 'Y' overlap |
202 | 'N' otherwise |
203 +------------------------------------------------------*/
204 FUNCTION is_overlap
205 (s1 IN DATE,
206 e1 IN DATE,
207 s2 IN DATE,
208 e2 IN DATE,
209 inc_exc IN VARCHAR2)
210 RETURN VARCHAR2
211 IS
212 l_comp NUMBER;
213 lres VARCHAR2(1);
214 BEGIN
215 IF s1 IS NULL OR s2 IS NULL THEN
216 lres := 'B';
217 ELSE
218 l_comp := compare(s1,s2);
219 IF l_comp = 1 THEN
220 IF is_between( s1,s2,e2,'INC',inc_exc) = 'Y' THEN
221 lres := 'Y';
222 ELSE
223 lres := 'N';
224 END IF;
225 ELSIF l_comp = -1 THEN
226 IF is_between( s2,s1,e1,'INC',inc_exc) = 'Y' THEN
227 lres := 'Y';
228 ELSE
229 lres := 'N';
230 END IF;
231 ELSIF l_comp = 0 THEN
232 IF is_between(e1,s2,e2,inc_exc,inc_exc) = 'Y' THEN
233 lres := 'Y';
234 ELSE
235 lres := 'N';
236 END IF;
237 END IF;
238 END IF;
239 RETURN lres;
240 END is_overlap;
241
242
243 /*------------------------------------------------------+
244 | Name : exist_overlap_assignment |
245 | |
246 | Description : |
247 | check if there are any assignment overlapping |
248 | a time period |
249 | |
250 | Parameter : |
251 | p_owner_table_name table using classification |
252 | p_owner_table_id id frm that table |
253 | p_class_category class_category |
254 | p_class_code class code |
255 | p_start_date_active start date of the assignment|
256 | p_end_date_active end date of the assignment |
257 | p_mode INSERT or UPDATE |
258 | p_code_assignment_id assignment id |
259 | Return : |
260 | 'Y' overlap |
261 | 'N' otherwise |
262 +------------------------------------------------------*/
263 FUNCTION exist_overlap_assignment
264 ( p_owner_table_name IN VARCHAR2 DEFAULT NULL,
265 p_owner_table_id IN NUMBER DEFAULT NULL,
266 p_class_category IN VARCHAR2 DEFAULT NULL,
267 p_class_Code IN VARCHAR2 DEFAULT NULL,
268 p_start_date_active IN DATE,
269 p_end_date_active IN DATE,
270 p_mode IN VARCHAR2,
271 p_code_assignment_id IN NUMBER DEFAULT NULL)
272 RETURN VARCHAR2
273 IS
274 CURSOR c_insert IS
275 SELECT 'Y'
276 FROM hz_code_assignments
277 WHERE owner_table_id = p_owner_table_id
278 AND owner_table_name = p_owner_table_name
279 AND class_category = p_class_category
280 AND class_code = p_class_code
281 AND is_overlap(start_date_active,
282 end_date_active,
283 p_start_date_active,
284 p_end_date_active,
285 decode(status,'I','EXC','INC'))='Y';
286
287 CURSOR c_update IS
288 SELECT 'Y'
289 FROM hz_code_assignments a,
290 hz_code_assignments b
291 WHERE a.code_assignment_id = p_code_assignment_id
292 AND a.owner_table_name = b.owner_table_name
293 AND a.owner_table_id = b.owner_table_id
294 AND a.class_category = b.class_category
295 AND a.class_code = b.class_code
296 AND b.code_assignment_id <> a.code_assignment_id
297 AND is_overlap(b.start_date_active,
298 b.end_date_active,
299 p_start_date_active,
300 p_end_date_active,
301 DECODE(b.status,'I','EXC','INC'))='Y';
302
303 lres VARCHAR2(1);
304 BEGIN
305 IF p_mode = 'INSERT' THEN
306 OPEN c_insert;
307 FETCH c_insert INTO lres;
308 IF c_insert%NOTFOUND THEN
309 lres := 'N';
310 END IF;
311 CLOSE c_insert;
312 ELSIF p_mode = 'UPDATE' THEN
313 OPEN c_update;
314 FETCH c_update INTO lres;
315 IF c_update%NOTFOUND THEN
316 lres := 'N';
317 END IF;
318 CLOSE c_update;
319 END IF;
320 RETURN lres;
321 END;
322
323 /*------------------------------------------------------+
324 | Name : is_assignment_active_today |
325 | |
326 | Description : |
327 | Check if there is any assignment today |
328 | |
329 | Parameter : |
330 | p_owner_table_name table using classification |
331 | p_owner_table_id id frm that table |
332 | p_class_category class_category |
333 | p_class_code class code |
334 | Return : |
335 | Y if there are any |
336 | N otherwise |
337 +------------------------------------------------------*/
338 FUNCTION is_assignment_active_today
339 ( p_owner_table_name IN VARCHAR2,
340 p_owner_table_id IN NUMBER,
341 p_class_category IN VARCHAR2,
342 p_class_Code IN VARCHAR2)
343 RETURN VARCHAR2
344 IS
345 CURSOR c IS
346 SELECT 'Y'
347 FROM hz_code_assignments
348 WHERE owner_table_id = p_owner_table_id
349 AND owner_table_name = p_owner_table_name
350 AND class_category = p_class_category
351 AND class_code = p_class_code
352 AND TRUNC(sysdate) >= start_date_active
353 AND TRUNC(sysdate) <= NVL(end_Date_active,sysdate)
354 AND DECODE(end_date_active,
355 TRUNC(sysdate) ,NVL(status,'A'),'A') = 'A';
356 lres VARCHAR2(1);
357 BEGIN
358 OPEN c;
359 FETCH c INTO lres;
360 IF c%NOTFOUND THEN
361 lres := 'N';
362 END IF;
363 CLOSE c;
364 RETURN lres;
365 END;
366
367 /*------------------------------------------------------+
368 | Name : exist_assignment_not_ended |
369 | |
370 | Description : |
371 | Check if there is any assignment without end date |
372 | |
373 | Parameter : |
374 | p_owner_table_name table using classification |
375 | p_owner_table_id id from that table |
376 | p_class_category class_category |
377 | p_class_code class code |
378 | Return : |
379 | Y if there are any |
380 | N otherwise |
381 +------------------------------------------------------*/
382 FUNCTION exist_assignment_not_ended
383 ( p_owner_table_name IN VARCHAR2,
384 p_owner_table_id IN NUMBER,
385 p_class_category IN VARCHAR2,
386 p_class_code IN VARCHAR2)
387 RETURN VARCHAR2
388 IS
389 CURSOR c1 IS
390 SELECT 'Y'
391 FROM hz_code_assignments
392 WHERE owner_table_name = p_owner_table_name
393 AND owner_table_id = p_owner_table_id
394 AND class_category = p_class_category
395 AND class_code = p_class_code
396 AND end_date_active IS NULL;
397 lfound VARCHAR2(1);
398 BEGIN
402 lfound := 'N';
399 OPEN c1;
400 FETCH c1 INTO lfound;
401 IF c1%NOTFOUND THEN
403 END IF;
404 CLOSE c1;
405 RETURN lfound;
406 END;
407
408 /*------------------------------------------------------+
409 | Name : exist_at_least_nb_assig |
410 | |
411 | Description : |
412 | Check if there are more than a number of assignments|
413 | |
414 | Parameter : |
415 | p_owner_table_name table using classification |
416 | p_owner_table_id id frm that table |
417 | p_class_category class_category |
418 | p_class_code class code |
419 | p_nb Number of assignment |
420 | Return : |
421 | Y if there are any |
422 | N otherwise |
423 +------------------------------------------------------*/
424 FUNCTION exist_at_least_nb_assig
425 ( p_owner_table_name IN VARCHAR2,
426 p_owner_table_id IN NUMBER,
427 p_class_category IN VARCHAR2,
428 p_class_code IN VARCHAR2,
429 p_nb IN NUMBER DEFAULT 2)
430 RETURN VARCHAR2
431 IS
432 CURSOR c IS
433 SELECT 'Y'
434 FROM hz_code_assignments
435 WHERE owner_table_name = p_owner_table_name
436 AND owner_table_id = p_owner_table_id
437 AND class_category = p_class_category
438 AND class_code = p_class_code;
439 lcpt NUMBER;
440 lres VARCHAR2(1);
441 tst VARCHAr2(1);
442 BEGIN
443 lcpt := 0;
444 lres := 'N';
445 OPEN c;
446 LOOP
447 FETCH c INTO tst;
448 EXIT WHEN c%NOTFOUND;
449 lcpt := lcpt + 1;
450 IF lcpt >= p_nb THEN
451 lres := 'Y';
452 EXIT;
453 END IF;
454 END LOOP;
455 CLOSE c;
456 RETURN lres;
457 END;
458
459 /*------------------------------------------------------+
460 | Name : Create_Code_assignment |
461 | |
462 | Description : |
463 | Wrapper on the top TCA V2 API for |
464 | Code assignment creation . |
465 | |
466 | Parameter : |
467 | From the record type |
468 | HZ_CLASSIFCIATION_V2PUB.CODE_ASSIGNEMENT_REC_TYPE |
469 | p_owner_table_name table using classification |
470 | p_owner_table_id id frm that table |
471 | p_class_category class_category |
472 | p_class_code class code |
473 | p_start_date_active start date of the assignment|
474 | p_end_date_active end date of the assignment |
475 | p_primary_flag primary Y or N |
476 | p_content_source_type origin of the assugnment |
477 | p_status status |
478 | p_created_by_module creation module |
479 | p_rank for hierarchy assignment |
480 | p_application_id application |
481 | x_code_assignment_id OUT assignment id |
482 | x_return_status OUT status execution |
483 | x_msg_count OUT number of error met |
484 | x_msg_data OUT the error message |
485 +------------------------------------------------------*/
486 PROCEDURE Create_Code_assignment
487 ( p_owner_table_name IN VARCHAR2,
488 p_owner_table_id IN NUMBER,
489 p_class_category IN VARCHAR2,
490 p_class_code IN VARCHAR2,
491 p_start_date_active IN DATE DEFAULT SYSDATE,
492 p_end_date_active IN DATE,
493 p_primary_flag IN VARCHAR2,
494 p_content_source_type IN VARCHAR2,
495 p_status IN VARCHAR2 DEFAULT 'A',
496 p_created_by_module IN VARCHAR2 DEFAULT 'TCA_FORM_WRAPPER',
497 p_rank IN VARCHAR2 DEFAULT NULL,
498 p_application_id IN NUMBER DEFAULT 222,
499 x_code_assignment_id OUT NOCOPY NUMBER,
500 x_return_status OUT NOCOPY VARCHAR2,
501 x_msg_count OUT NOCOPY NUMBER,
502 x_msg_data OUT NOCOPY VARCHAR2 )
503 IS
504 l_code_assignment_rec HZ_CLASSIFICATION_V2PUB.CODE_ASSIGNMENT_REC_TYPE;
505 tmp_var VARCHAR2(2000);
506 i NUMBER;
507 tmp_var1 VARCHAR2(2000);
508 lexception EXCEPTION;
509 lyn VARCHAR2(1);
510 BEGIN
511 arp_standard.debug('Create_Code_assignment(+)');
512
513 x_return_status := FND_API.G_RET_STS_SUCCESS;
514
515 /*
516 -- We allow user to create code assignment prior a start date of another one
517 lyn := arh_classification_pkg.assig_after_this_date
518 (p_owner_table_name,
519 p_owner_table_id,
520 p_class_category,
521 p_class_code,
522 p_start_date_active);
523
524 IF lyn = 'Y' THEN
525 FND_MESSAGE.set_name('AR','AR_CLASS_ASS_BEFORE_START_DATE');
526 FND_MSG_PUB.ADD;
527 RAISE lexception;
528 END IF;
529 */
530
531 lyn := arh_classification_pkg.exist_overlap_assignment
532 (p_owner_table_name,
533 p_owner_table_id,
534 p_class_category,
535 p_class_code,
536 p_start_date_active,
540
537 p_end_date_active,
538 'INSERT',
539 NULL);
541 IF lyn = 'Y' THEN
542 FND_MESSAGE.set_name('AR','AR_OVERLAP_CLASS_ASS_RECORD');
543 FND_MSG_PUB.ADD;
544 RAISE lexception;
545 END IF;
546
547 l_code_assignment_rec.owner_table_name := p_owner_table_name;
548 l_code_assignment_rec.owner_table_id := p_owner_table_id;
549 l_code_assignment_rec.class_category := p_class_category;
550 l_code_assignment_rec.class_code := p_class_code;
551 l_code_assignment_rec.primary_flag := p_primary_flag;
552 l_code_assignment_rec.content_source_type := p_content_source_type;
553 l_code_assignment_rec.start_date_active := p_start_date_active;
554 l_code_assignment_rec.end_date_active := p_end_date_active;
555 l_code_assignment_rec.status := p_status;
556 l_code_assignment_rec.created_by_module := p_created_by_module;
557 l_code_assignment_rec.rank := p_rank;
558 l_code_assignment_rec.application_id := p_application_id;
559
560 -- Now call the stored program
561 hz_classification_v2pub.create_code_assignment
562 ( p_init_msg_list => FND_API.G_FALSE,
563 p_code_assignment_rec => l_code_assignment_rec,
564 x_return_status => x_return_status,
565 x_msg_count => x_msg_count,
566 x_msg_data => x_msg_data,
567 x_code_assignment_id => x_code_assignment_id);
568
569
570 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
571 IF x_msg_count > 1 THEN
572 FOR i IN 1..x_msg_count LOOP
573 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
574 tmp_var1 := tmp_var1 || ' '|| tmp_var;
575 END LOOP;
576 x_msg_data := tmp_var1;
577 END IF;
578 arp_standard.debug(x_msg_data);
579 END IF;
580
581 arp_standard.debug('Create_Code_assignment(-)');
582 EXCEPTION
583 WHEN lexception THEN
584 x_return_status := fnd_api.g_ret_sts_error;
585 FND_MSG_PUB.Count_And_Get(
586 p_encoded => FND_API.G_FALSE,
587 p_count => x_msg_count,
588 p_data => x_msg_data );
589 IF x_msg_count > 1 THEN
590 FOR i IN 1..x_msg_count LOOP
591 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
592 tmp_var1 := tmp_var1 || ' '|| tmp_var;
593 END LOOP;
594 x_msg_data := tmp_var1;
595 END IF;
596 arp_standard.debug
597 ('EXCEPTION lexception: arh_classification_pkg.Create_Code_assignment'||x_msg_data);
598
599 WHEN OTHERS THEN
600 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
601 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
602 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
603 FND_MSG_PUB.ADD;
604 FND_MSG_PUB.Count_And_Get(
605 p_encoded => FND_API.G_FALSE,
606 p_count => x_msg_count,
607 p_data => x_msg_data );
608 arp_standard.debug
609 ('EXCEPTION: arh_classification_pkg.Create_Code_assignment'||x_msg_data);
610 END;
611
612 /*------------------------------------------------------+
613 | Name : Update_Code_assignment |
614 | |
615 | Description : |
616 | Wrapper on the top TCA V2 API for |
617 | Code assignment updation . |
618 | |
619 | Parameter : |
620 | From the record type |
621 | HZ_CLASSIFCIATION_V2PUB.CODE_ASSIGNEMENT_REC_TYPE |
622 | p_class_category class_category |
623 | p_class_code class code |
624 | p_start_date_active start date of the assignment|
625 | p_end_date_active end date of the assignment |
626 | p_primary_flag primary Y or N |
627 | p_content_source_type origin of the assugnment |
628 | p_status status |
629 | p_rank for hierarchy assignment |
630 | x_object_version_number record vesrion |
631 | x_code_assignment_id OUT assignment id |
632 | x_return_status OUT status execution |
633 | x_msg_count OUT number of error met |
634 | x_msg_data OUT the error message |
635 +------------------------------------------------------*/
636 PROCEDURE Update_Code_assignment
637 ( p_code_assignment_id IN NUMBER,
638 p_class_category IN VARCHAR2,
639 p_class_code IN VARCHAR2,
640 p_start_date_active IN DATE,
641 p_end_date_active IN DATE,
642 p_content_source_type IN VARCHAR2,
643 p_primary_flag IN VARCHAR2,
644 p_status IN VARCHAR2,
645 p_rank IN NUMBER,
646 x_object_version_number IN OUT NOCOPY NUMBER,
647 x_return_status OUT NOCOPY VARCHAR2,
648 x_msg_count OUT NOCOPY NUMBER,
649 x_msg_data OUT NOCOPY VARCHAR2 )
650 IS
651 CURSOR cu_code_assig IS
652 SELECT ROWID,
653 START_DATE_ACTIVE,
654 OBJECT_VERSION_NUMBER,
655 LAST_UPDATE_DATE
656 FROM hz_code_assignments
657 WHERE Code_assignment_id = p_code_assignment_id;
658 l_code_assignment_rec HZ_CLASSIFICATION_V2PUB.CODE_ASSIGNMENT_REC_TYPE;
659 tmp_var VARCHAR2(2000);
660 i NUMBER;
661 tmp_var1 VARCHAR2(2000);
662 l_object_version NUMBER;
663 l_rowid ROWID;
667 l_exception EXCEPTION;
664 l_last_update_date DATE;
665 l_start_date DATE;
666 lyn VARCHAR2(1);
668 tca_exception EXCEPTION;
669 BEGIN
670 arp_standard.debug('Update_Code_assignment(+)');
671 x_return_status := FND_API.G_RET_STS_SUCCESS;
672
673 OPEN cu_code_assig;
674 FETCH cu_code_assig INTO l_rowid,
675 l_start_date,
676 l_object_version,
677 l_last_update_date;
678 arp_standard.debug('Last_update_date:'||to_char(l_last_update_date));
679 IF cu_code_assig%NOTFOUND THEN
680 FND_MESSAGE.SET_NAME('AR','HZ_API_NO_RECORD');
681 FND_MESSAGE.SET_TOKEN('RECORD','HZ_CODE_ASSIGNMENT');
682 FND_MESSAGE.SET_TOKEN('ID',p_code_assignment_id);
683 FND_MSG_PUB.ADD;
684 RAISE l_exception;
685 ELSE
686 IF p_start_date_active <> l_start_date THEN
687 FND_MESSAGE.SET_NAME('AR','HZ_API_NONUPDATEABLE_COLUMN');
688 FND_MESSAGE.SET_TOKEN('COLUMN','START_DATE_ACTIVE');
689 FND_MSG_PUB.ADD;
690 RAISE l_exception;
691 END IF;
692 END IF;
693 CLOSE cu_code_assig;
694
695
696 lyn := arh_classification_pkg.exist_overlap_assignment
697 (NULL,
698 NULL,
699 NULL,
700 NULL,
701 p_start_date_active,
702 p_end_date_active,
703 'UPDATE',
704 p_code_assignment_id);
705
706 IF lyn = 'Y' THEN
707 FND_MESSAGE.set_name('AR','AR_OVERLAP_CLASS_RECORD');
708 FND_MSG_PUB.ADD;
709 RAISE l_exception;
710 END IF;
711
712 l_code_assignment_rec.code_assignment_id := p_code_assignment_id;
713 l_code_assignment_rec.class_category := p_class_category;
714 l_code_assignment_rec.class_code := p_class_code;
715 l_code_assignment_rec.primary_flag := INIT_SWITCH(p_primary_flag);
716 l_code_assignment_rec.content_source_type := INIT_SWITCH(p_content_source_type);
717 l_code_assignment_rec.start_date_active := INIT_SWITCH(p_start_date_active);
718 l_code_assignment_rec.end_date_active := INIT_SWITCH(p_end_date_active);
719 l_code_assignment_rec.status := INIT_SWITCH(p_status);
720 l_code_assignment_rec.rank := INIT_SWITCH(p_rank);
721 l_object_version := x_object_version_number ;
722
723
724 hz_classification_v2pub.update_code_assignment(
725 p_code_assignment_rec => l_code_assignment_rec,
726 p_object_version_number => x_object_version_number ,
727 x_return_status => x_return_status,
728 x_msg_count => x_msg_count,
729 x_msg_data => x_msg_data);
730
731 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
732 RAISE tca_exception;
733 END IF;
734
735 arp_standard.debug('update_code_assignment (-)');
736 EXCEPTION
737 WHEN l_exception THEN
738 x_return_status := FND_API.G_RET_STS_ERROR;
739 fnd_msg_pub.count_and_get(
740 p_encoded => fnd_api.g_false,
741 p_count => x_msg_count,
742 p_data => x_msg_data);
743 IF x_msg_count > 1 THEN
744 FOR i IN 1..x_msg_count LOOP
745 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
746 tmp_var1 := tmp_var1 || ' '|| tmp_var;
747 END LOOP;
748 x_msg_data := tmp_var1;
749 END IF;
750 arp_standard.debug('Exception arh_classification_pkg.update_code_assignment:'||x_msg_data);
751
752 WHEN tca_Exception THEN
753 IF x_msg_count > 1 THEN
754 FOR i IN 1..x_msg_count LOOP
755 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
756 tmp_var1 := tmp_var1 || ' '|| tmp_var;
757 END LOOP;
758 x_msg_data := tmp_var1;
759 END IF;
760 arp_standard.debug('Exception arh_classification_pkg.update_code_assignment:'||x_msg_data);
761
762 WHEN OTHERS THEN
763 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
764 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
765 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
766 FND_MSG_PUB.ADD;
767 FND_MSG_PUB.Count_And_Get(
768 p_encoded => FND_API.G_FALSE,
769 p_count => x_msg_count,
770 p_data => x_msg_data );
771 IF x_msg_count > 1 THEN
772 FOR i IN 1..x_msg_count LOOP
773 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
774 tmp_var1 := tmp_var1 || ' '|| tmp_var;
775 END LOOP;
776 x_msg_data := tmp_var1;
777 END IF;
778 arp_standard.debug('OTHER Exception arh_classification_pkg.update_code_assignment:'||
779 x_msg_data);
780
781 END;
782
783
784 /*------------------------------------------------------+
785 | Name : assig_after_this_date |
786 | |
787 | Description : |
788 | check if there are any assignment start after a |
789 | date |
790 | |
791 | Parameter : |
792 | p_owner_table_name table using classification |
793 | p_owner_table_id id frm that table |
794 | p_class_category class_category |
795 | p_class_code class code |
796 | p_start_date_active start date of the assignment|
797 | Return : |
798 | 'Y' if there are any |
799 | 'N' otherwise |
800 +------------------------------------------------------*/
801 FUNCTION assig_after_this_date
802 ( p_owner_table_name IN VARCHAR2 DEFAULT NULL,
803 p_owner_table_id IN NUMBER DEFAULT NULL,
804 p_class_category IN VARCHAR2 DEFAULT NULL,
805 p_class_Code IN VARCHAR2 DEFAULT NULL,
806 p_start_date_active IN DATE)
807 RETURN VARCHAR2
808 IS
809 CURSOR c IS
810 SELECT 'Y'
811 FROM hz_code_assignments
812 WHERE owner_table_name = p_owner_table_name
813 AND owner_table_id = p_owner_table_id
814 AND class_category = p_class_category
815 AND class_code = p_class_code
816 AND start_date_active> p_start_date_active;
817 lyn VARCHAR2(1);
818 BEGIN
819 OPEN c;
820 FETCH c INTO lyn;
821 IF c%NOTFOUND THEN
822 lyn := 'N';
823 END IF;
824 CLOSE c;
825 RETURN lyn;
826 END;
827
828
829 END arh_classification_pkg;