1 PACKAGE BODY csi_extend_attrib_vld_pvt AS
2 /* $Header: csiveavb.pls 120.5 2010/05/27 12:54:47 dsingire ship $ */
3
4 g_pkg_name VARCHAR2(30) := 'csi_extend_attrib_vld_pvt';
5
6 /*----------------------------------------------------------*/
7 /* Function Name : Is_Valid_instance_id */
8 /* */
9 /* Description : This function checks if instance */
10 /* ids are valid */
11 /*----------------------------------------------------------*/
12
13 FUNCTION Is_Valid_instance_id
14 ( p_instance_id IN NUMBER
15 ,p_event IN VARCHAR2
16 ,p_inventory_item_id OUT NOCOPY NUMBER
17 ,p_inv_master_org_id OUT NOCOPY NUMBER
18 ,p_stack_err_msg IN BOOLEAN )
19 RETURN BOOLEAN
20 IS
21 l_instance_id NUMBER;
22 BEGIN
23 -- Verify that instance id is passed
24 IF p_instance_id IS NULL THEN
25 IF ( p_stack_err_msg = TRUE ) THEN
26 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INSTANCE_ID');
27 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id);
28 FND_MSG_PUB.Add;
29 END IF;
30 RETURN FALSE;
31 ELSE
32 BEGIN
33 SELECT inventory_item_id,
34 inv_master_organization_id
35 INTO p_inventory_item_id,
36 p_inv_master_org_id
37 FROM csi_item_instances
38 WHERE instance_id = p_instance_id
39 AND ((active_end_date is NULL) OR (TRUNC(active_end_date,'MI') >= TRUNC(SYSDATE,'MI')));--Changed to trunc for bug 9756224
40
41 RETURN TRUE;
42 EXCEPTION
43 WHEN NO_DATA_FOUND THEN
44 IF p_event = 'INSERT' THEN
45 IF ( p_stack_err_msg = TRUE ) THEN
46 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INSTANCE_ID');
47 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id);
48 FND_MSG_PUB.Add;
52 IF ( p_stack_err_msg = TRUE ) THEN
49 END IF;
50 RETURN FALSE;
51 ELSIF p_event = 'UPDATE' THEN
53 FND_MESSAGE.SET_NAME('CSI','CSI_API_EXP_EXT_INST_ID');
54 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id);
55 FND_MSG_PUB.Add;
56 END IF;
57 RETURN FALSE;
58 END IF;
59 END;
60 END IF;
61 END Is_Valid_instance_id;
62
63
64
65
66 /*----------------------------------------------------------*/
67 /* Function Name : Val_inst_id_for_update */
68 /* */
69 /* Description : This function checks if instance */
70 /* ids can be updated */
71 /*----------------------------------------------------------*/
72
73 FUNCTION Val_inst_id_for_update
74 ( p_instance_id_new IN NUMBER
75 ,p_instance_id_old IN NUMBER
76 ,p_stack_err_msg IN BOOLEAN )
77 RETURN BOOLEAN
78 IS
79 l_instance_id NUMBER;
80 BEGIN
81 -- Verify that instance id is passed
82 IF (p_instance_id_old = p_instance_id_new) THEN
83 RETURN TRUE;
84 ELSE
85 IF ( p_stack_err_msg = TRUE ) THEN
86 FND_MESSAGE.SET_NAME('CSI','CSI_API_CANT_CHANGE_INST_ID');
87 FND_MESSAGE.SET_TOKEN('INSTANCE_ID_OLD',p_instance_id_old);
88 FND_MSG_PUB.Add;
89 END IF;
90 RETURN FALSE;
91 END IF;
92
93 EXCEPTION
94 WHEN OTHERS THEN
95 IF ( p_stack_err_msg = TRUE ) THEN
96 FND_MESSAGE.SET_NAME('CSI','CSI_API_CANT_CHANGE_INST_ID');
97 FND_MESSAGE.SET_TOKEN('INSTANCE_ID_OLD',p_instance_id_old);
98 FND_MSG_PUB.Add;
99 END IF;
100 RETURN FALSE;
101 END Val_inst_id_for_update;
102
103
104 /*----------------------------------------------------------*/
105 /* Function Name : Is_Valid_attribute_id */
106 /* */
107 /* Description : This function checks if attribute */
108 /* ids are valid */
109 /*----------------------------------------------------------*/
110
111 FUNCTION Is_Valid_attribute_id
112 (p_attribute_id IN NUMBER
113 ,p_attribute_level OUT NOCOPY VARCHAR2
114 ,p_master_organization_id OUT NOCOPY NUMBER
115 ,p_inventory_item_id OUT NOCOPY NUMBER
116 ,p_item_category_id OUT NOCOPY NUMBER
117 ,p_instance_id OUT NOCOPY NUMBER
118 ,p_stack_err_msg IN BOOLEAN )
119 RETURN BOOLEAN
120 IS
121 l_attribute_id NUMBER;
122 BEGIN
123 -- Verify that attribute id is passed
124 IF p_attribute_id IS NULL THEN
125 IF ( p_stack_err_msg = TRUE ) THEN
126 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ATTRIBUTE_ID');
127 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_ID',p_attribute_id);
128 FND_MSG_PUB.Add;
129 END IF;
130 RETURN FALSE;
131 ELSE
132 BEGIN
133 SELECT attribute_level
134 ,master_organization_id
135 ,inventory_item_id
136 ,item_category_id
137 ,instance_id
138 INTO p_attribute_level
139 ,p_master_organization_id
140 ,p_inventory_item_id
141 ,p_item_category_id
142 ,p_instance_id
143 FROM csi_i_extended_attribs
144 WHERE attribute_id = p_attribute_id;
145 RETURN TRUE;
146 EXCEPTION
147 WHEN NO_DATA_FOUND THEN
148 IF ( p_stack_err_msg = TRUE ) THEN
149 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ATTRIBUTE_ID');
150 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_ID',p_attribute_id);
151 FND_MSG_PUB.Add;
152 END IF;
153 RETURN FALSE;
154 END;
155 END IF;
156 END Is_Valid_attribute_id;
157
158
159
160 /*----------------------------------------------------------*/
161 /* Function Name : Val_and_get_ext_att_id */
162 /* */
163 /* Description : This function gets attribute values */
164 /* */
165 /*----------------------------------------------------------*/
166
167 FUNCTION Val_and_get_ext_att_id
168 (p_att_value_id IN NUMBER
169 ,p_ext_attrib_rec OUT NOCOPY csi_datastructures_pub.extend_attrib_values_rec
170 ,p_stack_err_msg IN BOOLEAN )
171 RETURN BOOLEAN
172 IS
173 l_attribute_id NUMBER;
174 BEGIN
175 -- Verify that attribute id is passed
176 IF p_att_value_id IS NULL THEN
177 IF ( p_stack_err_msg = TRUE ) THEN
178 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ATT_VAL_ID');
179 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_VALUE_ID',p_att_value_id);
180 FND_MSG_PUB.Add;
181 END IF;
182 RETURN FALSE;
183 ELSE
184 BEGIN
185 SELECT attribute_value_id,
186 attribute_id,
187 instance_id,
188 attribute_value,
189 active_start_date,
190 active_end_date,
194 attribute3,
191 context,
192 attribute1,
193 attribute2,
195 attribute4,
196 attribute5,
197 attribute6,
198 attribute7,
199 attribute8,
200 attribute9,
201 attribute10,
202 attribute11,
203 attribute12,
204 attribute13,
205 attribute14,
206 attribute15,
207 object_version_number
208 INTO p_ext_attrib_rec.attribute_value_id,
209 p_ext_attrib_rec.attribute_id,
210 p_ext_attrib_rec.instance_id,
211 p_ext_attrib_rec.attribute_value,
212 p_ext_attrib_rec.active_start_date,
213 p_ext_attrib_rec.active_end_date,
214 p_ext_attrib_rec.context,
215 p_ext_attrib_rec.attribute1,
216 p_ext_attrib_rec.attribute2,
217 p_ext_attrib_rec.attribute3,
218 p_ext_attrib_rec.attribute4,
219 p_ext_attrib_rec.attribute5,
220 p_ext_attrib_rec.attribute6,
221 p_ext_attrib_rec.attribute7,
222 p_ext_attrib_rec.attribute8,
223 p_ext_attrib_rec.attribute9,
224 p_ext_attrib_rec.attribute10,
225 p_ext_attrib_rec.attribute11,
226 p_ext_attrib_rec.attribute12,
227 p_ext_attrib_rec.attribute13,
228 p_ext_attrib_rec.attribute14,
229 p_ext_attrib_rec.attribute15,
230 p_ext_attrib_rec.object_version_number
231 FROM csi_iea_values
232 WHERE attribute_value_id = p_att_value_id;
233 RETURN TRUE;
234 EXCEPTION
235 WHEN NO_DATA_FOUND THEN
236 IF ( p_stack_err_msg = TRUE ) THEN
237 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ATT_VAL_ID');
238 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_VALUE_ID',p_att_value_id);
239 FND_MSG_PUB.Add;
240 END IF;
241 RETURN FALSE;
242 END;
243 END IF;
244 END Val_and_get_ext_att_id;
245
246 /*----------------------------------------------------------*/
247 /* Function Name : Is_Expire_Op */
248 /* */
249 /* Description : This function checks if it is a */
250 /* ids are valid and returns values */
251 /*----------------------------------------------------------*/
252
253 FUNCTION Is_Expire_Op
254 ( p_ext_attrib_rec IN csi_datastructures_pub.extend_attrib_values_rec
255 ,p_stack_err_msg IN BOOLEAN
256 )
257 RETURN BOOLEAN
258 IS
259 BEGIN
260 IF (p_ext_attrib_rec.attribute_id = FND_API.G_MISS_NUM ) AND
261 (p_ext_attrib_rec.instance_id = FND_API.G_MISS_NUM ) AND
262 (p_ext_attrib_rec.attribute_value = FND_API.G_MISS_CHAR) AND
263 (p_ext_attrib_rec.active_start_date = FND_API.G_MISS_DATE) AND
264 (p_ext_attrib_rec.active_end_date = SYSDATE) AND
265 (p_ext_attrib_rec.context = FND_API.G_MISS_CHAR) AND
266 (p_ext_attrib_rec.attribute1 = FND_API.G_MISS_CHAR) AND
267 (p_ext_attrib_rec.attribute2 = FND_API.G_MISS_CHAR) AND
268 (p_ext_attrib_rec.attribute3 = FND_API.G_MISS_CHAR) AND
269 (p_ext_attrib_rec.attribute4 = FND_API.G_MISS_CHAR) AND
270 (p_ext_attrib_rec.attribute5 = FND_API.G_MISS_CHAR) AND
271 (p_ext_attrib_rec.attribute6 = FND_API.G_MISS_CHAR) AND
272 (p_ext_attrib_rec.attribute7 = FND_API.G_MISS_CHAR) AND
273 (p_ext_attrib_rec.attribute8 = FND_API.G_MISS_CHAR) AND
274 (p_ext_attrib_rec.attribute9 = FND_API.G_MISS_CHAR) AND
275 (p_ext_attrib_rec.attribute10 = FND_API.G_MISS_CHAR) AND
276 (p_ext_attrib_rec.attribute11 = FND_API.G_MISS_CHAR) AND
277 (p_ext_attrib_rec.attribute12 = FND_API.G_MISS_CHAR) AND
278 (p_ext_attrib_rec.attribute13 = FND_API.G_MISS_CHAR) AND
279 (p_ext_attrib_rec.attribute14 = FND_API.G_MISS_CHAR) AND
280 (p_ext_attrib_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
281
282 RETURN TRUE;
283 ELSE
284 RETURN FALSE;
285 END IF;
286
287
288 END Is_Expire_Op;
289
290
291
292 /*----------------------------------------------------------*/
293 /* Function Name : Is_Updatable */
294 /* */
295 /* Description : This function checks if this is a */
296 /* an updatable record */
297 /*----------------------------------------------------------*/
298
299 FUNCTION Is_Updatable
300 (p_old_date IN DATE
301 ,p_new_date IN DATE
302 ,p_stack_err_msg IN BOOLEAN
303 )
304 RETURN BOOLEAN
305 IS
306 BEGIN
307 IF p_old_date < SYSDATE THEN
308 IF p_new_date = FND_API.G_MISS_DATE THEN
309 IF ( p_stack_err_msg = TRUE ) THEN
310 FND_MESSAGE.SET_NAME('CSI','CSI_API_NOT_UPDATABLE');
311 FND_MESSAGE.SET_TOKEN('ACTIVE_END_DATE',p_old_date);
312 FND_MSG_PUB.Add;
313 RAISE FND_API.G_EXC_ERROR;
314 END IF;
315 RETURN FALSE;
319 ELSE
316 ELSE
317 RETURN TRUE;
318 END IF;
320 RETURN TRUE;
321 END IF;
322
323 END Is_Updatable;
324
325
326
327 /*----------------------------------------------------------*/
328 /* Function Name : Alternate_PK_exists */
329 /* */
330 /* Description : This function checks if alternate */
331 /* PK's are valid */
332 /*----------------------------------------------------------*/
333
334 FUNCTION Alternate_PK_exists
335 (p_instance_id IN NUMBER
336 ,p_attribute_id IN NUMBER
337 ,p_stack_err_msg IN BOOLEAN )
338 RETURN BOOLEAN
339 IS
340 l_dummy VARCHAR2(30);
341 BEGIN
342 -- Verify the alternate PK's
343 BEGIN
344 SELECT '1'
345 INTO l_dummy
346 FROM csi_iea_values
347 WHERE instance_id = p_instance_id
348 AND attribute_id = p_attribute_id;
349
350 IF ( p_stack_err_msg = TRUE ) THEN
351 FND_MESSAGE.SET_NAME('CSI','CSI_EXT_INVALID_ALTERNATE_PK');
352 FND_MESSAGE.SET_TOKEN('ALTERNATE_PK',p_instance_id||' '||p_attribute_id);
353 FND_MSG_PUB.Add;
354 END IF;
355 RETURN FALSE;
356
357 EXCEPTION
358 WHEN NO_DATA_FOUND THEN
359 RETURN TRUE;
360 END;
361 END Alternate_PK_exists;
362
363
364
365 /*----------------------------------------------------------*/
366 /* Function Name : Is_StartDate_Valid */
367 /* */
368 /* Description : This function checks if start date */
369 /* is valid */
370 /*----------------------------------------------------------*/
371
372 FUNCTION Is_StartDate_Valid
373 (p_start_date IN OUT NOCOPY DATE,
374 p_end_date IN DATE,
375 p_instance_id IN NUMBER,
376 p_stack_err_msg IN BOOLEAN
377 ) RETURN BOOLEAN IS
378 l_return_value BOOLEAN := TRUE;
379
380 CURSOR c1 IS
381 SELECT active_start_date,
382 active_end_date
383 FROM csi_item_instances
384 WHERE instance_id = p_instance_id;
385 l_date_rec c1%ROWTYPE;
386
387 BEGIN
388 IF ((p_start_date is NULL) OR (p_start_date = FND_API.G_MISS_DATE)) THEN
389 p_start_date := SYSDATE;
390 RETURN l_return_value;
391 END IF;
392
393
394 IF ((p_end_date is NOT NULL)
395 AND
396 (p_end_date <> FND_API.G_MISS_DATE)
397 AND
398 (p_start_date > p_end_date)) THEN
399 l_return_value := FALSE;
400 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVAL_EXT_START_DATE');
401 FND_MESSAGE.SET_TOKEN('START_DATE',p_start_date);
402 FND_MSG_PUB.Add;
403 RETURN l_return_value;
404 END IF;
405
406 OPEN c1;
407 FETCH c1 INTO l_date_rec;
408 IF c1%NOTFOUND THEN
409 l_return_value := FALSE;
410 IF ( p_stack_err_msg = TRUE ) THEN
411 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVAL_INST_START_DATE');
412 FND_MESSAGE.SET_TOKEN('ENTITY','EXTENDED ATTRIBUTES');
413 FND_MSG_PUB.Add;
414 END IF;
415 END IF;
416
417 IF (p_start_date < l_date_rec.active_start_date)
418 OR
419 (p_start_date > NVL(l_date_rec.active_end_date,p_start_date))
420 OR
421 (p_start_date > SYSDATE)
422 THEN
423 l_return_value := FALSE;
424 IF ( p_stack_err_msg = TRUE ) THEN
425 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVAL_EXT_START_DATE');
426 FND_MESSAGE.SET_TOKEN('START_DATE',p_start_date);
427 FND_MSG_PUB.Add;
428 END IF;
429 END IF;
430 CLOSE c1;
431 RETURN l_return_value;
432
433 END Is_StartDate_Valid;
434
435
436 /*----------------------------------------------------------*/
437 /* Function Name : Is_EndDate_Valid */
438 /* */
439 /* Description : This function checks if end date */
440 /* is valid */
441 /*----------------------------------------------------------*/
442
443 FUNCTION Is_EndDate_Valid
444 (
445 p_start_date IN DATE,
446 p_end_date IN DATE,
447 p_instance_id IN NUMBER,
448 p_attr_value_id IN NUMBER,
449 p_txn_id IN NUMBER,
450 p_stack_err_msg IN BOOLEAN
451 ) RETURN BOOLEAN IS
452
453 l_return_value BOOLEAN := TRUE;
454 l_transaction_date date;
455
456 CURSOR c1 IS
457 SELECT active_start_date,
458 active_end_date
459 FROM csi_item_instances
460 WHERE instance_id = p_instance_id;
461
462 l_date_rec c1%ROWTYPE;
463
464 BEGIN
465 IF ((p_attr_value_id IS NULL) OR (p_attr_value_id = FND_API.G_MISS_NUM)) THEN
466 IF ((p_end_date is NOT NULL) AND (p_end_date <> fnd_api.g_miss_date)) THEN
467
468 IF p_end_date < SYSDATE THEN
469 l_return_value := FALSE;
470 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVAL_EXT_END_DATE');
471 FND_MESSAGE.SET_TOKEN('END_DATE',p_end_date);
472 FND_MSG_PUB.Add;
473 l_return_value := FALSE;
477 RETURN l_return_value;
474 RETURN l_return_value;
475 END IF;
476 END IF;
478
479 ELSE
480
481 IF ((p_end_date is NOT NULL) AND (p_end_date <> fnd_api.g_miss_date)) THEN --bug 9301695
482
483 IF p_end_date < sysdate THEN
484 SELECT MAX(t.transaction_date)
485 INTO l_transaction_date
486 FROM csi_iea_values_h s,
487 csi_transactions t
488 WHERE s.attribute_value_id=p_attr_value_id
489 AND s.transaction_id=t.transaction_id
490 AND t.transaction_id <> nvl(p_txn_id, -99999);
491
492 IF l_transaction_date > p_end_date
493 THEN
494 fnd_message.set_name('CSI','CSI_HAS_TXNS');
495 fnd_message.set_token('END_DATE_ACTIVE',p_end_date);
496 fnd_msg_pub.add;
497 l_return_value := FALSE;
498 RETURN l_return_value;
499 END IF;
500 END IF;
501
502 END IF; --bug 9301695
503
504 IF ((p_end_date is not null) and (p_end_date <> fnd_api.g_miss_date)) then
505 OPEN c1;
506 FETCH c1 INTO l_date_rec;
507
508 IF (p_end_date > NVL(l_date_rec.active_end_date, p_end_date))
509 OR
510 (p_end_date < l_date_rec.active_start_date)
511 THEN
512 l_return_value := FALSE;
513 IF ( p_stack_err_msg = TRUE ) THEN
514 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVAL_EXT_END_DATE');
515 FND_MESSAGE.SET_TOKEN('END_DATE',p_end_date);
516 FND_MSG_PUB.Add;
517 END IF;
518 RETURN l_return_value;
519 END IF;
520 CLOSE c1;
521 END IF;
522 END IF;
523 RETURN l_return_value;
524
525 END Is_EndDate_Valid;
526
527
528 /*----------------------------------------------------------*/
529 /* Function Name : Is_Valid_attribute_level_content */
530 /* */
531 /* Description : This function checks if */
532 /* attribute_leve is valid */
533 /*----------------------------------------------------------*/
534
535 FUNCTION Is_Valid_attrib_level_content
536 (p_attribute_level IN VARCHAR2
537 ,p_master_organization_id IN NUMBER
538 ,p_inventory_item_id IN NUMBER
539 ,p_item_category_id IN NUMBER
540 ,p_instance_id IN NUMBER
541 ,p_orig_instance_id IN NUMBER
542 ,p_orig_inv_item_id IN NUMBER
543 ,p_orig_master_org_id IN NUMBER
544 ,p_stack_err_msg IN BOOLEAN )
545 RETURN BOOLEAN
546 IS
547 l_dummy VARCHAR2(30);
548 l_category_set_id NUMBER;
549 BEGIN
550 IF UPPER(p_attribute_level) = 'GLOBAL' THEN
551 RETURN TRUE;
552
553 ELSIF UPPER(p_attribute_level) = 'ITEM' THEN
554
555 IF ((p_inventory_item_id = p_orig_inv_item_id) AND
556 (p_master_organization_id = p_orig_master_org_id)) THEN
557 RETURN TRUE;
558 ELSE
559 IF ( p_stack_err_msg = TRUE ) THEN
560 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_ATT_LEV_ITEM');
561 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_LEVEL_ITEM',p_orig_inv_item_id);
562 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_LEVEL_ORG',p_master_organization_id);
563 FND_MSG_PUB.Add;
564 END IF;
565 RETURN FALSE;
566 END IF;
567
568 ELSIF UPPER(p_attribute_level) = 'INSTANCE' THEN
569 IF p_instance_id = p_orig_instance_id THEN
570 RETURN TRUE;
571 ELSE
572 IF ( p_stack_err_msg = TRUE ) THEN
573 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_ATT_LEV_INST');
574 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_LEVEL_INST_ORIG',p_instance_id );
575 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_LEVEL_INST',p_orig_instance_id );
576 FND_MSG_PUB.Add;
577 END IF;
578 RETURN FALSE;
579 END IF;
580
581 ELSIF UPPER(p_attribute_level) = 'CATEGORY' THEN
582 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
583 csi_gen_utility_pvt.populate_install_param_rec;
584 END IF;
585 --
586 l_category_set_id := csi_datastructures_pub.g_install_param_rec.category_set_id;
587 --
588 IF l_category_set_id IS NULL THEN
589 csi_gen_utility_pvt.put_line('Category Set should be defined to have attributes at CATEGORY Level');
590 IF ( p_stack_err_msg = TRUE ) THEN
591 FND_MESSAGE.SET_NAME('CSI','CSI_API_UNINSTALLED_PARAMETER');
592 FND_MSG_PUB.ADD;
593 END IF;
594 RETURN FALSE;
595 END IF;
596 --
597 BEGIN
598 SELECT '1'
599 INTO l_dummy
600 FROM mtl_item_categories ic
601 WHERE ic.inventory_item_id = p_orig_inv_item_id
602 AND ic.organization_id = p_orig_master_org_id
603 AND ic.category_id = p_item_category_id
604 AND ic.category_set_id = l_category_set_id;
605 RETURN TRUE;
606 EXCEPTION
607 WHEN NO_DATA_FOUND THEN
608 IF ( p_stack_err_msg = TRUE ) THEN
609 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_ATT_LEV_CAT');
610 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_LEVEL_ITEM',p_orig_inv_item_id);
611 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_LEVEL_ORG',p_orig_master_org_id);
612 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_LEVEL_CAT',p_item_category_id);
613 FND_MSG_PUB.Add;
614 END IF;
615 RETURN FALSE;
616 END;
617 ELSE
618 -- Invalid attribute level has been passed
619 IF ( p_stack_err_msg = TRUE ) THEN
620 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ATTRIB_LEVEL');
621 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_LEVEL_CONTENT',p_attribute_level);
622 FND_MSG_PUB.Add;
623 END IF;
624 RETURN FALSE;
625
626 END IF;
627
628 EXCEPTION
629 WHEN OTHERS THEN
630 IF ( p_stack_err_msg = TRUE ) THEN
631 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ATTRIB_LEVEL');
632 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_LEVEL_CONTENT',p_attribute_level
633 ||' inventory_item_id '||p_orig_inv_item_id
634 ||' master_org_id '||p_orig_master_org_id
635 ||' item_category '|| p_item_category_id);
636 FND_MSG_PUB.Add;
637 END IF;
638 RETURN FALSE;
639 END Is_Valid_attrib_level_content;
640
641
642
643 /*----------------------------------------------------------*/
644 /* Function Name : Is_Valid_attribute_value_id */
645 /* */
646 /* Description : This function checks if */
647 /* attribute_value_id is valid */
648 /*----------------------------------------------------------*/
649
650 FUNCTION Is_Valid_attribute_value_id
651 (p_attribute_value_id IN NUMBER
652 ,p_stack_err_msg IN BOOLEAN
653 )
654 RETURN BOOLEAN
655 IS
656 l_attribute_value_id NUMBER;
657 BEGIN
658 -- Verify that attribute_value_id is passed
659 SELECT '1'
660 INTO l_attribute_value_id
661 FROM csi_iea_values
662 WHERE attribute_value_id = p_attribute_value_id ;
663 IF ( p_stack_err_msg = TRUE ) THEN
664 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ATT_VAL_ID');
665 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_VALUE_ID',p_attribute_value_id);
666 FND_MSG_PUB.Add;
667 END IF;
668 RETURN FALSE;
669 EXCEPTION
670 WHEN NO_DATA_FOUND THEN
671 RETURN TRUE;
672 END Is_Valid_attribute_value_id;
673
674
675 /*----------------------------------------------------------*/
676 /* Function Name : Get_attribute_value_id */
677 /* */
678 /* Description : This function generates */
679 /* instance_ou_ids using a sequence */
680 /*----------------------------------------------------------*/
681 FUNCTION Get_attribute_value_id
682 ( p_stack_err_msg IN BOOLEAN
683 )
684 RETURN NUMBER
685 IS
686 l_attribute_value_id NUMBER;
687 BEGIN
688 SELECT csi_iea_values_s.nextval
689 INTO l_attribute_value_id
690 FROM dual;
691 RETURN l_attribute_value_id;
692 EXCEPTION
693 WHEN OTHERS THEN
694 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ATT_VAL_ID');
695 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_VALUE_ID',l_attribute_value_id);
696 FND_MSG_PUB.Add;
697 END Get_attribute_value_id;
698
699
700
701 /*----------------------------------------------------------*/
702 /* Function Name : get_cis_i_org_assign_h_id */
703 /* */
704 /* Description : This function generates */
705 /* cis_i_org_assign_h_id using a sequence */
706 /*----------------------------------------------------------*/
707
708 FUNCTION get_attribute_value_h_id
709 ( p_stack_err_msg IN BOOLEAN
710 )
711 RETURN NUMBER
712 IS
713 l_attribute_value_id NUMBER;
714 BEGIN
715 SELECT csi_iea_values_h_s.nextval
716 INTO l_attribute_value_id
717 FROM dual;
718 RETURN l_attribute_value_id;
719 EXCEPTION
720 WHEN OTHERS THEN
721 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ATT_VAL_H_ID');
722 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_VALUE_H_ID',l_attribute_value_id);
723 FND_MSG_PUB.Add;
724 END get_attribute_value_h_id;
725
726
727
728 /*-------------------------------------------------------------- */
729 /* Function Name : get_object_version_number */
730 /* */
731 /* Description : This function generates object_version_number */
732 /* using previous version numbers */
733 /*---------------------------------------------------------------*/
734
735 FUNCTION get_object_version_number
736 (p_object_version_number IN NUMBER
737 ,p_stack_err_msg IN BOOLEAN
738 )
739 RETURN NUMBER
740 IS
741 l_object_version_number NUMBER;
742 BEGIN
743 l_object_version_number := p_object_version_number + 1;
744 RETURN l_object_version_number;
745 EXCEPTION
746 WHEN OTHERS THEN
747 IF ( p_stack_err_msg = TRUE ) THEN
748 FND_MESSAGE.SET_NAME('CSI','CSI_API_OBJ_VER_MISMATCH');
749 FND_MSG_PUB.Add;
750 END IF;
751 END get_object_version_number ;
752
753
754 /*-------------------------------------------------------------- */
755 /* Function Name : Is_valid_obj_ver_num */
756 /* */
757 /* Description : This function generates object_version_number */
758 /* using previous version numbers */
759 /*---------------------------------------------------------------*/
760
761 FUNCTION Is_valid_obj_ver_num
762 (p_obj_ver_numb_new IN NUMBER
763 ,p_obj_ver_numb_old IN NUMBER
764 ,p_stack_err_msg IN BOOLEAN
765 )
766 RETURN BOOLEAN
767 IS
768 l_object_version_number NUMBER;
769
770 BEGIN
771 IF (p_obj_ver_numb_new = p_obj_ver_numb_old ) THEN
772 RETURN TRUE;
773 ELSE
774 IF ( p_stack_err_msg = TRUE ) THEN
775 FND_MESSAGE.SET_NAME('CSI','CSI_API_OBJ_VER_MISMATCH');
776 FND_MSG_PUB.Add;
777 END IF;
778 RETURN FALSE;
779 END IF;
780
781 EXCEPTION
782 WHEN OTHERS THEN
783 IF ( p_stack_err_msg = TRUE ) THEN
784 FND_MESSAGE.SET_NAME('CSI','CSI_API_OBJ_VER_MISMATCH');
785 FND_MSG_PUB.Add;
786 RETURN FALSE;
787 END IF;
788 END Is_valid_obj_ver_num;
789
790
791 /*-------------------------------------------------------------- */
792 /* Function Name : get_full_dump_frequency */
793 /* */
794 /* Description : This function gets the dump frequency */
795 /* */
796 /*---------------------------------------------------------------*/
797
798 FUNCTION get_full_dump_frequency
799 (p_stack_err_msg IN BOOLEAN
800 )
801 RETURN NUMBER
802 IS
803 l_dump_frequency NUMBER;
804
805 BEGIN
806 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
807 csi_gen_utility_pvt.populate_install_param_rec;
808 END IF;
809 --
810 l_dump_frequency := csi_datastructures_pub.g_install_param_rec.history_full_dump_frequency;
811 --
812 IF ( p_stack_err_msg = TRUE ) THEN
813 IF l_dump_frequency IS NULL THEN
814 FND_MESSAGE.SET_NAME('CSI','CSI_API_GET_FULL_DUMP_FAILED');
815 FND_MSG_PUB.ADD;
816 END IF;
817 END IF;
818
819 RETURN l_dump_frequency;
820 END get_full_dump_frequency;
821
822
823 END csi_extend_attrib_vld_pvt;