1 PACKAGE BODY csi_extend_attrib_vld_pvt AS
2 /* $Header: csiveavb.pls 120.1 2005/07/12 17:17:12 srramakr noship $ */
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 (active_end_date >= SYSDATE));
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;
49 END IF;
50 RETURN FALSE;
51 ELSIF p_event = 'UPDATE' THEN
52 IF ( p_stack_err_msg = TRUE ) 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,
191 context,
192 attribute1,
193 attribute2,
194 attribute3,
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;
316 ELSE
317 RETURN TRUE;
318 END IF;
319 ELSE
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;
474 RETURN l_return_value;
475 END IF;
476 END IF;
477 RETURN l_return_value;
478
479 ELSE
480
481 IF p_end_date < sysdate THEN
482 SELECT MAX(t.transaction_date)
483 INTO l_transaction_date
484 FROM csi_iea_values_h s,
485 csi_transactions t
486 WHERE s.attribute_value_id=p_attr_value_id
487 AND s.transaction_id=t.transaction_id
488 AND t.transaction_id <> nvl(p_txn_id, -99999);
489
490 IF l_transaction_date > p_end_date
491 THEN
492 fnd_message.set_name('CSI','CSI_HAS_TXNS');
493 fnd_message.set_token('END_DATE_ACTIVE',p_end_date);
494 fnd_msg_pub.add;
495 l_return_value := FALSE;
496 RETURN l_return_value;
497 END IF;
498 END IF;
499
500 IF ((p_end_date is not null) and (p_end_date <> fnd_api.g_miss_date)) then
501 OPEN c1;
502 FETCH c1 INTO l_date_rec;
503
504 IF (p_end_date > NVL(l_date_rec.active_end_date, p_end_date))
505 OR
506 (p_end_date < l_date_rec.active_start_date)
507 THEN
508 l_return_value := FALSE;
509 IF ( p_stack_err_msg = TRUE ) THEN
510 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVAL_EXT_END_DATE');
511 FND_MESSAGE.SET_TOKEN('END_DATE',p_end_date);
512 FND_MSG_PUB.Add;
513 END IF;
514 RETURN l_return_value;
515 END IF;
516 CLOSE c1;
517 END IF;
518 END IF;
519 RETURN l_return_value;
520
521 END Is_EndDate_Valid;
522
523
524 /*----------------------------------------------------------*/
525 /* Function Name : Is_Valid_attribute_level_content */
526 /* */
527 /* Description : This function checks if */
528 /* attribute_leve is valid */
529 /*----------------------------------------------------------*/
530
531 FUNCTION Is_Valid_attrib_level_content
532 (p_attribute_level IN VARCHAR2
533 ,p_master_organization_id IN NUMBER
534 ,p_inventory_item_id IN NUMBER
535 ,p_item_category_id IN NUMBER
536 ,p_instance_id IN NUMBER
537 ,p_orig_instance_id IN NUMBER
538 ,p_orig_inv_item_id IN NUMBER
539 ,p_orig_master_org_id IN NUMBER
540 ,p_stack_err_msg IN BOOLEAN )
541 RETURN BOOLEAN
542 IS
543 l_dummy VARCHAR2(30);
544 l_category_set_id NUMBER;
545 BEGIN
546 IF UPPER(p_attribute_level) = 'GLOBAL' THEN
547 RETURN TRUE;
548
549 ELSIF UPPER(p_attribute_level) = 'ITEM' THEN
550
551 IF ((p_inventory_item_id = p_orig_inv_item_id) AND
552 (p_master_organization_id = p_orig_master_org_id)) THEN
553 RETURN TRUE;
554 ELSE
555 IF ( p_stack_err_msg = TRUE ) THEN
556 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_ATT_LEV_ITEM');
557 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_LEVEL_ITEM',p_orig_inv_item_id);
558 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_LEVEL_ORG',p_master_organization_id);
559 FND_MSG_PUB.Add;
560 END IF;
561 RETURN FALSE;
562 END IF;
563
564 ELSIF UPPER(p_attribute_level) = 'INSTANCE' THEN
565 IF p_instance_id = p_orig_instance_id THEN
566 RETURN TRUE;
567 ELSE
568 IF ( p_stack_err_msg = TRUE ) THEN
569 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_ATT_LEV_INST');
570 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_LEVEL_INST_ORIG',p_instance_id );
571 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_LEVEL_INST',p_orig_instance_id );
572 FND_MSG_PUB.Add;
573 END IF;
574 RETURN FALSE;
575 END IF;
576
577 ELSIF UPPER(p_attribute_level) = 'CATEGORY' THEN
578 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
579 csi_gen_utility_pvt.populate_install_param_rec;
580 END IF;
581 --
582 l_category_set_id := csi_datastructures_pub.g_install_param_rec.category_set_id;
583 --
584 IF l_category_set_id IS NULL THEN
585 csi_gen_utility_pvt.put_line('Category Set should be defined to have attributes at CATEGORY Level');
586 IF ( p_stack_err_msg = TRUE ) THEN
587 FND_MESSAGE.SET_NAME('CSI','CSI_API_UNINSTALLED_PARAMETER');
588 FND_MSG_PUB.ADD;
589 END IF;
590 RETURN FALSE;
591 END IF;
592 --
593 BEGIN
594 SELECT '1'
595 INTO l_dummy
596 FROM mtl_item_categories ic
597 WHERE ic.inventory_item_id = p_orig_inv_item_id
598 AND ic.organization_id = p_orig_master_org_id
599 AND ic.category_id = p_item_category_id
600 AND ic.category_set_id = l_category_set_id;
601 RETURN TRUE;
602 EXCEPTION
603 WHEN NO_DATA_FOUND THEN
604 IF ( p_stack_err_msg = TRUE ) THEN
605 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_ATT_LEV_CAT');
606 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_LEVEL_ITEM',p_orig_inv_item_id);
607 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_LEVEL_ORG',p_orig_master_org_id);
608 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_LEVEL_CAT',p_item_category_id);
609 FND_MSG_PUB.Add;
610 END IF;
611 RETURN FALSE;
612 END;
613 ELSE
614 -- Invalid attribute level has been passed
615 IF ( p_stack_err_msg = TRUE ) THEN
616 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ATTRIB_LEVEL');
617 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_LEVEL_CONTENT',p_attribute_level);
618 FND_MSG_PUB.Add;
619 END IF;
620 RETURN FALSE;
621
622 END IF;
623
624 EXCEPTION
625 WHEN OTHERS THEN
626 IF ( p_stack_err_msg = TRUE ) THEN
627 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ATTRIB_LEVEL');
628 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_LEVEL_CONTENT',p_attribute_level
629 ||' inventory_item_id '||p_orig_inv_item_id
630 ||' master_org_id '||p_orig_master_org_id
631 ||' item_category '|| p_item_category_id);
632 FND_MSG_PUB.Add;
633 END IF;
634 RETURN FALSE;
635 END Is_Valid_attrib_level_content;
636
637
638
639 /*----------------------------------------------------------*/
640 /* Function Name : Is_Valid_attribute_value_id */
641 /* */
642 /* Description : This function checks if */
643 /* attribute_value_id is valid */
644 /*----------------------------------------------------------*/
645
646 FUNCTION Is_Valid_attribute_value_id
647 (p_attribute_value_id IN NUMBER
648 ,p_stack_err_msg IN BOOLEAN
649 )
650 RETURN BOOLEAN
651 IS
652 l_attribute_value_id NUMBER;
653 BEGIN
654 -- Verify that attribute_value_id is passed
655 SELECT '1'
656 INTO l_attribute_value_id
657 FROM csi_iea_values
658 WHERE attribute_value_id = p_attribute_value_id ;
659 IF ( p_stack_err_msg = TRUE ) THEN
660 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ATT_VAL_ID');
661 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_VALUE_ID',p_attribute_value_id);
662 FND_MSG_PUB.Add;
663 END IF;
664 RETURN FALSE;
665 EXCEPTION
666 WHEN NO_DATA_FOUND THEN
667 RETURN TRUE;
668 END Is_Valid_attribute_value_id;
669
670
671 /*----------------------------------------------------------*/
672 /* Function Name : Get_attribute_value_id */
673 /* */
674 /* Description : This function generates */
675 /* instance_ou_ids using a sequence */
676 /*----------------------------------------------------------*/
677 FUNCTION Get_attribute_value_id
678 ( p_stack_err_msg IN BOOLEAN
679 )
680 RETURN NUMBER
681 IS
682 l_attribute_value_id NUMBER;
683 BEGIN
684 SELECT csi_iea_values_s.nextval
685 INTO l_attribute_value_id
686 FROM dual;
687 RETURN l_attribute_value_id;
688 EXCEPTION
689 WHEN OTHERS THEN
690 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ATT_VAL_ID');
691 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_VALUE_ID',l_attribute_value_id);
692 FND_MSG_PUB.Add;
693 END Get_attribute_value_id;
694
695
696
697 /*----------------------------------------------------------*/
698 /* Function Name : get_cis_i_org_assign_h_id */
699 /* */
700 /* Description : This function generates */
701 /* cis_i_org_assign_h_id using a sequence */
702 /*----------------------------------------------------------*/
703
704 FUNCTION get_attribute_value_h_id
705 ( p_stack_err_msg IN BOOLEAN
706 )
707 RETURN NUMBER
708 IS
709 l_attribute_value_id NUMBER;
710 BEGIN
711 SELECT csi_iea_values_h_s.nextval
712 INTO l_attribute_value_id
713 FROM dual;
714 RETURN l_attribute_value_id;
715 EXCEPTION
716 WHEN OTHERS THEN
717 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ATT_VAL_H_ID');
718 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_VALUE_H_ID',l_attribute_value_id);
719 FND_MSG_PUB.Add;
720 END get_attribute_value_h_id;
721
722
723
724 /*-------------------------------------------------------------- */
725 /* Function Name : get_object_version_number */
726 /* */
727 /* Description : This function generates object_version_number */
728 /* using previous version numbers */
729 /*---------------------------------------------------------------*/
730
731 FUNCTION get_object_version_number
732 (p_object_version_number IN NUMBER
733 ,p_stack_err_msg IN BOOLEAN
734 )
735 RETURN NUMBER
736 IS
737 l_object_version_number NUMBER;
738 BEGIN
739 l_object_version_number := p_object_version_number + 1;
740 RETURN l_object_version_number;
741 EXCEPTION
742 WHEN OTHERS THEN
743 IF ( p_stack_err_msg = TRUE ) THEN
744 FND_MESSAGE.SET_NAME('CSI','CSI_API_OBJ_VER_MISMATCH');
745 FND_MSG_PUB.Add;
746 END IF;
747 END get_object_version_number ;
748
749
750 /*-------------------------------------------------------------- */
751 /* Function Name : Is_valid_obj_ver_num */
752 /* */
753 /* Description : This function generates object_version_number */
754 /* using previous version numbers */
755 /*---------------------------------------------------------------*/
756
757 FUNCTION Is_valid_obj_ver_num
758 (p_obj_ver_numb_new IN NUMBER
759 ,p_obj_ver_numb_old IN NUMBER
760 ,p_stack_err_msg IN BOOLEAN
761 )
762 RETURN BOOLEAN
763 IS
764 l_object_version_number NUMBER;
765
766 BEGIN
767 IF (p_obj_ver_numb_new = p_obj_ver_numb_old ) THEN
768 RETURN TRUE;
769 ELSE
770 IF ( p_stack_err_msg = TRUE ) THEN
771 FND_MESSAGE.SET_NAME('CSI','CSI_API_OBJ_VER_MISMATCH');
772 FND_MSG_PUB.Add;
773 END IF;
774 RETURN FALSE;
775 END IF;
776
777 EXCEPTION
778 WHEN OTHERS THEN
779 IF ( p_stack_err_msg = TRUE ) THEN
780 FND_MESSAGE.SET_NAME('CSI','CSI_API_OBJ_VER_MISMATCH');
781 FND_MSG_PUB.Add;
782 RETURN FALSE;
783 END IF;
784 END Is_valid_obj_ver_num;
785
786
787 /*-------------------------------------------------------------- */
788 /* Function Name : get_full_dump_frequency */
789 /* */
790 /* Description : This function gets the dump frequency */
791 /* */
792 /*---------------------------------------------------------------*/
793
794 FUNCTION get_full_dump_frequency
795 (p_stack_err_msg IN BOOLEAN
796 )
797 RETURN NUMBER
798 IS
799 l_dump_frequency NUMBER;
800
801 BEGIN
802 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
803 csi_gen_utility_pvt.populate_install_param_rec;
804 END IF;
805 --
806 l_dump_frequency := csi_datastructures_pub.g_install_param_rec.history_full_dump_frequency;
807 --
808 IF ( p_stack_err_msg = TRUE ) THEN
809 IF l_dump_frequency IS NULL THEN
810 FND_MESSAGE.SET_NAME('CSI','CSI_API_GET_FULL_DUMP_FAILED');
811 FND_MSG_PUB.ADD;
812 END IF;
813 END IF;
814
815 RETURN l_dump_frequency;
816 END get_full_dump_frequency;
817
818
819 END csi_extend_attrib_vld_pvt;