[Home] [Help]
PACKAGE BODY: APPS.CSI_GENERIC_GRP
Source
1 PACKAGE BODY CSI_GENERIC_GRP AS
2 /* $Header: csiggenb.pls 120.1 2006/06/06 23:29:09 sguthiva noship $ */
3
4 FUNCTION CONFIG_ROOT_NODE (p_instance_id IN NUMBER ,
5 p_relationship_type_code IN VARCHAR2
6 )
7 RETURN NUMBER IS
8 l_object_id NUMBER:= -1;
9 BEGIN
10 SELECT object_id
11 INTO l_object_id
12 FROM csi_ii_relationships
13 WHERE LEVEL = ( SELECT MAX(LEVEL)
14 FROM csi_ii_relationships
15 START WITH subject_id = p_instance_id
16 CONNECT BY subject_id = PRIOR object_id
17 )
18 AND relationship_type_code = p_relationship_type_code
19 START WITH subject_id = p_instance_id
20 CONNECT BY subject_id = PRIOR object_id;
21 RETURN l_object_id;
22 EXCEPTION
23 WHEN OTHERS THEN
24 RETURN l_object_id;
25 END CONFIG_ROOT_NODE;
26
27 -- This function is used only by the form CSIMEDIT.fmb
28
29 FUNCTION R_COUNT ( L_SELECT IN VARCHAR2)
30 RETURN Number IS
31 l_count Number := 0;
32 BEGIN
33 EXECUTE IMMEDIATE l_select
34 Into l_count;
35 RETURN l_count ;
36 Exception
37 When Others then
38 l_count := 0;
39 RETURN l_count ;
40 END R_COUNT;
41
42
43 PROCEDURE validate_ext_attribs(
44 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
45 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
46 p_validation_mode IN VARCHAR2 ,
47 p_ext_attrib_rec IN csi_datastructures_pub.ext_attrib_rec,
48 x_return_status OUT NOCOPY VARCHAR2 ,
49 x_msg_count OUT NOCOPY NUMBER ,
50 x_msg_data OUT NOCOPY VARCHAR2
51 );
52
53 PROCEDURE validate_attribute_level (
54 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
55 p_validation_mode IN VARCHAR2 ,
56 p_ext_attrib_rec IN csi_datastructures_pub.ext_attrib_rec,
57 x_return_status OUT NOCOPY VARCHAR2 ,
58 x_msg_count OUT NOCOPY NUMBER ,
59 x_msg_data OUT NOCOPY VARCHAR2
60 );
61
62
63 PROCEDURE validate_attribute_code (
64 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
65 p_validation_mode IN VARCHAR2 ,
66 p_attribute_code IN VARCHAR2 ,
67 x_return_status OUT NOCOPY VARCHAR2 ,
68 x_msg_count OUT NOCOPY NUMBER ,
69 x_msg_data OUT NOCOPY VARCHAR2
70 );
71
72
73 PROCEDURE validate_attribute_category (
74 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
75 p_validation_mode IN VARCHAR2 ,
76 p_attribute_category IN VARCHAR2 ,
77 x_return_status OUT NOCOPY VARCHAR2 ,
78 x_msg_count OUT NOCOPY NUMBER ,
79 x_msg_data OUT NOCOPY VARCHAR2
80 );
81
82
83 PROCEDURE validate_attribute_name (
84 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
85 p_validation_mode IN VARCHAR2 ,
86 p_attribute_name IN VARCHAR2 ,
87 x_return_status OUT NOCOPY VARCHAR2 ,
88 x_msg_count OUT NOCOPY NUMBER ,
89 x_msg_data OUT NOCOPY VARCHAR2
90 );
91
92
93 PROCEDURE Create_extended_attrib(
94 p_api_version IN NUMBER,
95 p_commit IN VARCHAR2 := fnd_api.g_false,
96 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
97 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
98 p_ext_attrib_rec IN csi_datastructures_pub.ext_attrib_rec,
99 x_attribute_id OUT NOCOPY NUMBER,
100 x_return_status OUT NOCOPY VARCHAR2,
101 x_msg_count OUT NOCOPY NUMBER,
102 x_msg_data OUT NOCOPY VARCHAR2)
103 IS
104 l_api_name CONSTANT VARCHAR2(30) := 'Create_extended_attrib';
105 l_api_version CONSTANT NUMBER := 1.0;
106 l_debug_level VARCHAR2(1);
107 l_master_org_id NUMBER;
108 l_inv_item_id NUMBER;
109 l_item_category_id NUMBER;
110 l_instance_id NUMBER;
111 l_validation_flag VARCHAR2(1);
112
113 BEGIN
114 SAVEPOINT Create_extended_attrib_grp;
115
116 IF NOT fnd_api.compatible_api_call ( l_api_version,
117 p_api_version,
118 l_api_name,
119 g_pkg_name)
120 THEN
121 RAISE fnd_api.g_exc_unexpected_error;
122 END IF;
123
124
125 IF fnd_api.to_boolean( p_init_msg_list )
126 THEN
127 fnd_msg_pub.initialize;
128 END IF;
129
130 x_return_status := fnd_api.g_ret_sts_success;
131
132 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
133 IF (l_debug_level > 0) THEN
134 csi_gen_utility_pvt.put_line( 'Create_extended_attrib');
135 END IF;
136
137 IF (l_debug_level > 1) THEN
138 csi_gen_utility_pvt.put_line(
139 p_api_version ||'-'||
140 p_Commit ||'-'||
141 p_Init_Msg_list ||'-'||
142 p_Validation_level
143 );
144 --csi_gen_utility_pvt.dump_ext_attrib_rec(p_ext_attrib_rec);
145 END IF;
146 IF x_return_status<>fnd_api.g_ret_sts_success THEN
147 RAISE fnd_api.g_exc_error;
148 END IF;
149
150 IF ( (p_ext_attrib_rec.attribute_level IS NULL
151 OR p_ext_attrib_rec.attribute_level = fnd_api.g_miss_char )
152 OR (p_ext_attrib_rec.attribute_code IS NULL
153 OR p_ext_attrib_rec.attribute_code = fnd_api.g_miss_char) )
154 THEN
155 l_validation_flag:='Y';
156 ELSE
157 IF (p_ext_attrib_rec.master_organization_id IS NULL
158 OR p_ext_attrib_rec.master_organization_id = fnd_api.g_miss_num)
159 THEN
160 l_master_org_id := NULL;
161 ELSE
162 l_master_org_id := p_ext_attrib_rec.master_organization_id;
163 END IF;
164
165 IF (p_ext_attrib_rec.inventory_item_id IS NULL
166 OR p_ext_attrib_rec.inventory_item_id = fnd_api.g_miss_num)
167 THEN
168 l_inv_item_id := NULL;
169 ELSE
170 l_inv_item_id := p_ext_attrib_rec.inventory_item_id;
171 END IF;
172
173 IF (p_ext_attrib_rec.item_category_id IS NULL
174 OR p_ext_attrib_rec.item_category_id = fnd_api.g_miss_num)
175 THEN
176 l_item_category_id := NULL;
177 ELSE
178 l_item_category_id := p_ext_attrib_rec.item_category_id;
179 END IF;
180
181 IF (p_ext_attrib_rec.instance_id IS NULL
182 OR p_ext_attrib_rec.instance_id=fnd_api.g_miss_num)
183 THEN
184 l_instance_id := NULL;
185 ELSE
186 l_instance_id := p_ext_attrib_rec.instance_id;
187 END IF;
188
189 BEGIN
190 SELECT attribute_id
191 INTO x_attribute_id
192 FROM csi_i_extended_attribs
193 WHERE attribute_level=p_ext_attrib_rec.attribute_level
194 AND (l_master_org_id IS NULL
195 OR
196 ( l_master_org_id IS NOT NULL
197 AND master_organization_id =l_master_org_id) )
198 AND (l_inv_item_id IS NULL
199 OR
200 ( l_inv_item_id IS NOT NULL
201 AND inventory_item_id =l_inv_item_id) )
202 AND (l_item_category_id IS NULL
203 OR
204 ( l_item_category_id IS NOT NULL
205 AND item_category_id =l_item_category_id) )
206 AND (l_instance_id IS NULL
207 OR
208 ( l_instance_id IS NOT NULL
209 AND instance_id =l_instance_id) )
210 AND attribute_code =p_ext_attrib_rec.attribute_code;
211
212 l_validation_flag:='N';
213 EXCEPTION
214 WHEN NO_DATA_FOUND THEN
215 l_validation_flag:='Y';
216 END;
217
218 END IF;
219
220 IF l_validation_flag = 'Y' THEN
221 validate_ext_attribs(
222 p_init_msg_list => fnd_api.g_false,
223 p_validation_level => p_validation_level,
224 p_validation_mode => 'CREATE',
225 p_ext_attrib_rec => p_ext_attrib_rec,
226 x_return_status => x_return_status,
227 x_msg_count => x_msg_count,
228 x_msg_data => x_msg_data);
229
230 IF x_return_status<>fnd_api.g_ret_sts_success THEN
231 RAISE fnd_api.g_exc_error;
232 END IF;
233
234
235 IF x_return_status = fnd_api.g_ret_sts_success THEN
236 -- invoke table handler(csi_systems_b_pkg.insert_row)
237
238 csi_i_ext_attrib_pkg.insert_row(
239 px_attribute_id => x_attribute_id ,
240 p_attribute_level => p_ext_attrib_rec.attribute_level,
241 p_master_organization_id => p_ext_attrib_rec.master_organization_id,
242 p_inventory_item_id => p_ext_attrib_rec.inventory_item_id,
243 p_item_category_id => p_ext_attrib_rec.item_category_id,
244 p_instance_id => p_ext_attrib_rec.instance_id,
245 p_attribute_code => p_ext_attrib_rec.attribute_code,
246 p_attribute_name => p_ext_attrib_rec.attribute_name,
247 p_attribute_category => p_ext_attrib_rec.attribute_category,
248 p_description => p_ext_attrib_rec.description,
249 p_active_start_date => p_ext_attrib_rec.active_start_date,
250 p_active_end_date => p_ext_attrib_rec.active_end_date,
251 p_context => p_ext_attrib_rec.context,
252 p_attribute1 => p_ext_attrib_rec.attribute1,
253 p_attribute2 => p_ext_attrib_rec.attribute2,
254 p_attribute3 => p_ext_attrib_rec.attribute3,
255 p_attribute4 => p_ext_attrib_rec.attribute4,
256 p_attribute5 => p_ext_attrib_rec.attribute5,
257 p_attribute6 => p_ext_attrib_rec.attribute6,
258 p_attribute7 => p_ext_attrib_rec.attribute7,
259 p_attribute8 => p_ext_attrib_rec.attribute8,
260 p_attribute9 => p_ext_attrib_rec.attribute9,
261 p_attribute10 => p_ext_attrib_rec.attribute10,
262 p_attribute11 => p_ext_attrib_rec.attribute11,
263 p_attribute12 => p_ext_attrib_rec.attribute12,
264 p_attribute13 => p_ext_attrib_rec.attribute13,
265 p_attribute14 => p_ext_attrib_rec.attribute14,
266 p_attribute15 => p_ext_attrib_rec.attribute15,
267 p_created_by => fnd_global.user_id,
268 p_creation_date => SYSDATE,
269 p_last_updated_by => fnd_global.user_id,
270 p_last_update_date => SYSDATE,
271 p_last_update_login => fnd_global.conc_login_id,
272 p_object_version_number => 1
273 );
274
275
276 END IF;
277
278 IF x_return_status <> fnd_api.g_ret_sts_success THEN
279 RAISE fnd_api.g_exc_error;
280 END IF;
281 END IF; --End of End If for l_validation_flag = 'Y'
282 IF fnd_api.to_boolean( p_commit )
283 THEN
284 COMMIT WORK;
285 END IF;
286
287 fnd_msg_pub.count_and_get
288 ( p_count => x_msg_count,
289 p_data => x_msg_data
290 );
291
292 EXCEPTION
293 WHEN fnd_api.g_exc_error THEN
294 ROLLBACK TO Create_extended_attrib_grp;
295 x_return_status := fnd_api.g_ret_sts_error ;
296 fnd_msg_pub.count_and_get
297 (p_count => x_msg_count ,
298 p_data => x_msg_data
299 );
300
301 WHEN fnd_api.g_exc_unexpected_error THEN
302 ROLLBACK TO Create_extended_attrib_grp;
303 x_return_status := fnd_api.g_ret_sts_unexp_error ;
304 fnd_msg_pub.count_and_get
305 (p_count => x_msg_count ,
306 p_data => x_msg_data
307 );
308
309 WHEN OTHERS THEN
310 ROLLBACK TO Create_extended_attrib_grp;
311 x_return_status := fnd_api.g_ret_sts_unexp_error ;
312 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
313 fnd_msg_pub.add_exc_msg(g_pkg_name ,l_api_name);
314 END IF;
315 fnd_msg_pub.count_and_get
316 (p_count => x_msg_count ,
317 p_data => x_msg_data
318 );
319
320 END Create_extended_attrib;
321
322 -- This function is used by systems form UI.
323
324 FUNCTION ui_system_rec RETURN csi_datastructures_pub.system_rec
325 IS
326 l_system_rec csi_datastructures_pub.system_rec;
327 BEGIN
328 RETURN l_system_rec;
329 END ui_system_rec;
330
331 -- This function is used by form UI.
332
333 FUNCTION ui_transaction_rec RETURN csi_datastructures_pub.transaction_rec
334 IS
335 l_transaction_rec csi_datastructures_pub.transaction_rec;
336 BEGIN
337 RETURN l_transaction_rec;
338 END ui_transaction_rec;
339
340 -- This function is used by form UI.
341
342 FUNCTION ui_ext_attrib_query_rec RETURN csi_datastructures_pub.extend_attrib_query_rec
343 IS
344 l_extend_attrib_query_rec csi_datastructures_pub.extend_attrib_query_rec;
345 BEGIN
346 RETURN l_extend_attrib_query_rec;
347 END ui_ext_attrib_query_rec;
348
349 FUNCTION ui_relationship_query_rec RETURN csi_datastructures_pub.relationship_query_rec
350 IS
351 l_relationship_query_rec csi_datastructures_pub.relationship_query_rec;
352 BEGIN
353 RETURN l_relationship_query_rec;
354 END ui_relationship_query_rec;
355
356 -- ---------------------------------------------------------------------------------------------------
357 -- Validate attribute_level:
358 -- If 'GLOBAL' is passed then values for master_organization_id, inventory_item_id,
359 -- item_category_id and instance_id should be passed as null else raise an error
360 -- If 'CATEGORY' is passed then values for master_organization_id, inventory_item_id
361 -- and instance_id should be passed as null else raise an error.
362 -- If 'ITEM" is passed then values for item_category_id and instance_id
363 -- should be passed as null else raise an error
364 -- If 'INSTANCE' is passed then values for master_organization_id ,inventory_item_id
365 -- and item_category_id should be passed as null else raise an error.
369 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
366 -- --------------------------------------------------------------------------------------------------
367
368 PROCEDURE validate_attribute_level (
370 p_validation_mode IN VARCHAR2 ,
371 p_ext_attrib_rec IN csi_datastructures_pub.ext_attrib_rec,
372 x_return_status OUT NOCOPY VARCHAR2 ,
373 x_msg_count OUT NOCOPY NUMBER ,
374 x_msg_data OUT NOCOPY VARCHAR2
375 )
376 IS
377 l_dummy VARCHAR2(1);
378 l_lookup_code VARCHAR2(30):=NULL;
379 l_ext_lookup_type VARCHAR2(30):= 'CSI_IEA_LEVEL_CODE';
380 BEGIN
381 IF fnd_api.to_boolean( p_init_msg_list )
382 THEN
383 fnd_msg_pub.initialize;
384 END IF;
385 x_return_status := fnd_api.g_ret_sts_success;
386
387 IF p_validation_mode='CREATE' THEN
388 IF ( (p_ext_attrib_rec.attribute_level IS NOT NULL) AND (p_ext_attrib_rec.attribute_level<>fnd_api.g_miss_char) )
389 THEN
390 BEGIN
391 SELECT 'x'
392 INTO l_dummy
393 FROM csi_lookups
394 WHERE lookup_type = l_ext_lookup_type
395 AND lookup_code = p_ext_attrib_rec.attribute_level;
396 IF p_ext_attrib_rec.attribute_level = 'GLOBAL'
397 THEN
398 IF ( (p_ext_attrib_rec.master_organization_id IS NOT NULL
399 AND p_ext_attrib_rec.master_organization_id <> fnd_api.g_miss_num)
400 OR (p_ext_attrib_rec.inventory_item_id IS NOT NULL
401 AND p_ext_attrib_rec.inventory_item_id <> fnd_api.g_miss_num)
402 OR (p_ext_attrib_rec.item_category_id IS NOT NULL
403 AND p_ext_attrib_rec.item_category_id <> fnd_api.g_miss_num)
404 OR (p_ext_attrib_rec.instance_id IS NOT NULL
405 AND p_ext_attrib_rec.instance_id <> fnd_api.g_miss_num)
406 )
407 THEN
408 fnd_message.set_name('CSI', 'CSI_PASS_NULL_PARAMS');
409 fnd_msg_pub.add;
410 x_return_status := fnd_api.g_ret_sts_error;
411 END IF;
412 END IF; --End if Global
413
414 IF p_ext_attrib_rec.attribute_level = 'CATEGORY'
415 THEN
416 IF ( (p_ext_attrib_rec.master_organization_id IS NOT NULL
417 AND p_ext_attrib_rec.master_organization_id <> fnd_api.g_miss_num)
418 OR (p_ext_attrib_rec.inventory_item_id IS NOT NULL
419 AND p_ext_attrib_rec.inventory_item_id <> fnd_api.g_miss_num)
420 OR (p_ext_attrib_rec.instance_id IS NOT NULL
421 AND p_ext_attrib_rec.instance_id <> fnd_api.g_miss_num)
422 )
423 THEN
424 fnd_message.set_name('CSI', 'CSI_PASS_CAT_PARAMS');
425 fnd_msg_pub.add;
426 x_return_status := fnd_api.g_ret_sts_error;
427 ELSIF ( (p_ext_attrib_rec.item_category_id IS NULL)
428 OR (p_ext_attrib_rec.item_category_id = fnd_api.g_miss_num) )
429 THEN
430 fnd_message.set_name('CSI', 'CSI_MISSING_CAT_PARAMETER');
431 fnd_msg_pub.add;
432 x_return_status := fnd_api.g_ret_sts_error;
433 ELSE
434 l_dummy := NULL;
435 BEGIN
436 SELECT 'x'
437 INTO l_dummy
438 FROM mtl_category_set_valid_cats
439 WHERE category_id = p_ext_attrib_rec.item_category_id
440 AND category_set_id = ( SELECT category_set_id
441 FROM csi_install_parameters );
442 EXCEPTION
443 WHEN NO_DATA_FOUND THEN
444 fnd_message.set_name('CSI', 'CSI_INVALID_PARAMETER');
445 fnd_message.set_token('PARAMETER',p_ext_attrib_rec.item_category_id);
446 fnd_msg_pub.add;
447 x_return_status := fnd_api.g_ret_sts_error;
448 END;
449 END IF;
450 END IF; --End If Category
451
452 IF p_ext_attrib_rec.attribute_level = 'ITEM'
453 THEN
454 IF ( (p_ext_attrib_rec.item_category_id IS NOT NULL
455 AND p_ext_attrib_rec.item_category_id <> fnd_api.g_miss_num)
456 OR (p_ext_attrib_rec.instance_id IS NOT NULL
457 AND p_ext_attrib_rec.instance_id <> fnd_api.g_miss_num)
458 )
459 THEN
460 fnd_message.set_name('CSI', 'CSI_PASS_ITEM_PARAMS');
461 fnd_msg_pub.add;
462 x_return_status := fnd_api.g_ret_sts_error;
463 ELSIF ( (p_ext_attrib_rec.inventory_item_id IS NULL
464 OR p_ext_attrib_rec.inventory_item_id = fnd_api.g_miss_num)
465 OR (p_ext_attrib_rec.master_organization_id IS NULL
469 fnd_msg_pub.add;
466 OR p_ext_attrib_rec.master_organization_id = fnd_api.g_miss_num) )
467 THEN
468 fnd_message.set_name('CSI', 'CSI_MISSING_ITEM_PARAMETER');
470 x_return_status := fnd_api.g_ret_sts_error;
471 ELSE
472 l_dummy := NULL;
473 BEGIN
474 SELECT 'x'
475 INTO l_dummy
476 FROM mtl_system_items
477 WHERE organization_id = p_ext_attrib_rec.master_organization_id
478 AND inventory_item_id = p_ext_attrib_rec.inventory_item_id;
479 EXCEPTION
480 WHEN NO_DATA_FOUND THEN
481 fnd_message.set_name('CSI', 'CSI_INVALID_ITEM_PARAMETER');
482 fnd_msg_pub.add;
483 x_return_status := fnd_api.g_ret_sts_error;
484 END;
485 END IF;
486 END IF; --End If Item
487
488 IF p_ext_attrib_rec.attribute_level = 'INSTANCE'
489 THEN
490 IF ( (p_ext_attrib_rec.master_organization_id IS NOT NULL
491 AND p_ext_attrib_rec.master_organization_id <> fnd_api.g_miss_num)
492 OR (p_ext_attrib_rec.inventory_item_id IS NOT NULL
493 AND p_ext_attrib_rec.inventory_item_id <> fnd_api.g_miss_num)
494 OR (p_ext_attrib_rec.item_category_id IS NOT NULL
495 AND p_ext_attrib_rec.item_category_id <> fnd_api.g_miss_num)
496 )
497 THEN
498 fnd_message.set_name('CSI', 'CSI_PASS_INS_PARAMS');
499 fnd_msg_pub.add;
500 x_return_status := fnd_api.g_ret_sts_error;
501 ELSIF ( (p_ext_attrib_rec.instance_id IS NULL
502 OR p_ext_attrib_rec.instance_id = fnd_api.g_miss_num) )
503 THEN
504 fnd_message.set_name('CSI', 'CSI_MISSING_INS_PARAMETER');
505 fnd_msg_pub.add;
506 x_return_status := fnd_api.g_ret_sts_error;
507 ELSE
508 l_dummy := NULL;
509 BEGIN
510 SELECT 'x'
511 INTO l_dummy
512 FROM csi_item_instances
513 WHERE instance_id = p_ext_attrib_rec.instance_id;
514 EXCEPTION
515 WHEN NO_DATA_FOUND THEN
516 fnd_message.set_name('CSI', 'CSI_INVALID_INS_PARAMETER');
517 fnd_message.set_token('PARAMETER',p_ext_attrib_rec.instance_id);
518 fnd_msg_pub.add;
519 x_return_status := fnd_api.g_ret_sts_error;
520 END;
521 END IF;
522 END IF; -- end if instance
523 EXCEPTION
524 WHEN no_data_found THEN
525 fnd_message.set_name('CSI', 'CSI_INVALID_AL_PARAMETER');
526 fnd_message.set_token('PARAMETER',p_ext_attrib_rec.attribute_level);
527 fnd_msg_pub.add;
528 x_return_status := fnd_api.g_ret_sts_error;
529 END;--End for csi_lookups
530 ELSE -- Else if p_ext_attrib_rec.attribute_level IS NULL
531 fnd_message.set_name('CSI', 'CSI_MISSING_PARAMETER');
532 fnd_message.set_token('PARAMETER','ATTRIBUTE_LEVEL');
533 fnd_msg_pub.add;
534 x_return_status := fnd_api.g_ret_sts_error;
535 END IF;--End if p_ext_attrib_rec.attribute_level IS NOT NULL
536 END IF; --End if p_validation_mode='CREATE'
537
538 fnd_msg_pub.count_and_get
539 ( p_count => x_msg_count,
540 p_data => x_msg_data
541 );
542 END;
543
544 PROCEDURE validate_attribute_code (
545 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
546 p_validation_mode IN VARCHAR2 ,
547 p_attribute_code IN VARCHAR2 ,
548 x_return_status OUT NOCOPY VARCHAR2 ,
549 x_msg_count OUT NOCOPY NUMBER ,
550 x_msg_data OUT NOCOPY VARCHAR2
551 )
552 IS
553 l_dummy VARCHAR2(1);
554 l_attrib_lookup_type VARCHAR2(30):= 'CSI_EXTEND_ATTRIB_POOL';
555 BEGIN
556 IF fnd_api.to_boolean( p_init_msg_list )
557 THEN
558 fnd_msg_pub.initialize;
559 END IF;
560 x_return_status := fnd_api.g_ret_sts_success;
561
562 IF p_validation_mode='CREATE' THEN
563 IF ( (p_attribute_code IS NOT NULL) AND (p_attribute_code<>fnd_api.g_miss_char) ) THEN
564 BEGIN
565 SELECT 'x'
566 INTO l_dummy
567 FROM csi_lookups
568 WHERE lookup_type=l_attrib_lookup_type
569 AND lookup_code=p_attribute_code;
570 EXCEPTION
571 WHEN NO_DATA_FOUND THEN
572 fnd_message.set_name('CSI', 'CSI_INVALID_PARAMETER');
576 END;
573 fnd_message.set_token('PARAMETER',p_attribute_code);
574 fnd_msg_pub.add;
575 x_return_status := fnd_api.g_ret_sts_error;
577 ELSE
578 fnd_message.set_name('CSI', 'CSI_MISSING_PARAMETER');
579 fnd_message.set_token('PARAMETER','ATTRIBUTE_CODE');
580 fnd_msg_pub.add;
581 x_return_status := fnd_api.g_ret_sts_error;
582 END IF;
583 END IF;
584 END;
585
586 PROCEDURE validate_attribute_category (
587 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
588 p_validation_mode IN VARCHAR2 ,
589 p_attribute_category IN VARCHAR2 ,
590 x_return_status OUT NOCOPY VARCHAR2 ,
591 x_msg_count OUT NOCOPY NUMBER ,
592 x_msg_data OUT NOCOPY VARCHAR2
593 )
594 IS
595 l_dummy VARCHAR2(1);
596 l_cat_lookup_type VARCHAR2(30) := 'CSI_IEA_CATEGORY';
597 BEGIN
598 IF fnd_api.to_boolean( p_init_msg_list )
599 THEN
600 fnd_msg_pub.initialize;
601 END IF;
602 x_return_status := fnd_api.g_ret_sts_success;
603
604 IF p_validation_mode='CREATE' THEN
605 IF ( (p_attribute_category IS NOT NULL) AND (p_attribute_category<>fnd_api.g_miss_char) ) THEN
606 BEGIN
607 SELECT 'x'
608 INTO l_dummy
609 FROM csi_lookups
610 WHERE lookup_type= l_cat_lookup_type
611 AND lookup_code=p_attribute_category;
612 EXCEPTION
613 WHEN NO_DATA_FOUND THEN
614 fnd_message.set_name('CSI', 'CSI_INVALID_PARAMETER');
615 fnd_message.set_token('PARAMETER',p_attribute_category);
616 fnd_msg_pub.add;
617 x_return_status := fnd_api.g_ret_sts_error;
618 END;
619 END IF;
620 END IF;
621 END;
622
623 PROCEDURE validate_attribute_name (
624 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
625 p_validation_mode IN VARCHAR2 ,
626 p_attribute_name IN VARCHAR2 ,
627 x_return_status OUT NOCOPY VARCHAR2 ,
628 x_msg_count OUT NOCOPY NUMBER ,
629 x_msg_data OUT NOCOPY VARCHAR2
630 )
631 IS
632 l_dummy VARCHAR2(1);
633 BEGIN
634 IF fnd_api.to_boolean( p_init_msg_list )
635 THEN
636 fnd_msg_pub.initialize;
637 END IF;
638 x_return_status := fnd_api.g_ret_sts_success;
639
640 IF p_validation_mode='CREATE' THEN
641 IF ( (p_attribute_name IS NULL) OR (p_attribute_name = fnd_api.g_miss_char) ) THEN
642 fnd_message.set_name('CSI', 'CSI_MISSING_PARAMETER');
643 fnd_message.set_token('PARAMETER','ATTRIBUTE_NAME');
644 fnd_msg_pub.add;
645 x_return_status := fnd_api.g_ret_sts_error;
646 END IF;
647 END IF;
648 END;
649
650
651 PROCEDURE validate_ext_attribs(
652 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
653 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
654 p_validation_mode IN VARCHAR2 ,
655 p_ext_attrib_rec IN csi_datastructures_pub.ext_attrib_rec,
656 x_return_status OUT NOCOPY VARCHAR2 ,
657 x_msg_count OUT NOCOPY NUMBER ,
658 x_msg_data OUT NOCOPY VARCHAR2
659 )
660 IS
661 l_api_name CONSTANT VARCHAR2(30) := 'validate_ext_attribs';
662 BEGIN
663
664 --dmsg('inside validate_systems');
665
666 -- initialize api RETURN status to success
667 x_return_status := fnd_api.g_ret_sts_success;
668
669 IF (p_validation_level >= fnd_api.g_valid_level_full) THEN
670
671 validate_attribute_level(
672 p_init_msg_list => fnd_api.g_false,
673 p_validation_mode => p_validation_mode,
674 p_ext_attrib_rec => p_ext_attrib_rec,
675 x_return_status => x_return_status,
676 x_msg_count => x_msg_count,
677 x_msg_data => x_msg_data);
678 IF x_return_status <> fnd_api.g_ret_sts_success THEN
679 RAISE fnd_api.g_exc_error;
680 END IF;
681
682 validate_attribute_code(
683 p_init_msg_list => fnd_api.g_false,
684 p_validation_mode => p_validation_mode,
685 p_attribute_code => p_ext_attrib_rec.attribute_code,
686 x_return_status => x_return_status,
687 x_msg_count => x_msg_count,
688 x_msg_data => x_msg_data);
689 IF x_return_status <> fnd_api.g_ret_sts_success THEN
690 RAISE fnd_api.g_exc_error;
691 END IF;
692
693 validate_attribute_category(
694 p_init_msg_list => fnd_api.g_false,
695 p_validation_mode => p_validation_mode,
696 p_attribute_category => p_ext_attrib_rec.attribute_category,
697 x_return_status => x_return_status,
698 x_msg_count => x_msg_count,
702 END IF;
699 x_msg_data => x_msg_data);
700 IF x_return_status <> fnd_api.g_ret_sts_success THEN
701 RAISE fnd_api.g_exc_error;
703
704 validate_attribute_name(
705 p_init_msg_list => fnd_api.g_false,
706 p_validation_mode => p_validation_mode,
707 p_attribute_name => p_ext_attrib_rec.attribute_name,
708 x_return_status => x_return_status,
709 x_msg_count => x_msg_count,
710 x_msg_data => x_msg_data);
711 IF x_return_status <> fnd_api.g_ret_sts_success THEN
712 RAISE fnd_api.g_exc_error;
713 END IF;
714 END IF;
715
716 END validate_ext_attribs;
717
718 PROCEDURE terminate_instances(
719 errbuf OUT NOCOPY VARCHAR2,
720 retcode OUT NOCOPY NUMBER,
721 p_status_id IN NUMBER)
722 IS
723
724 CURSOR exp_inst_cur(p_expired_status_id IN NUMBER) IS
725 SELECT cii.instance_id,
726 cii.instance_number,
727 cii.active_end_date,
728 cii.object_version_number
729 FROM csi_item_instances cii
730 WHERE nvl(cii.active_end_date , sysdate) < sysdate
731 AND cii.instance_status_id <> p_expired_status_id
732 AND not exists ( SELECT 'X' from csi_instance_statuses cis
733 WHERE cis.instance_status_id = cii.instance_status_id
734 AND cis.terminated_flag = 'Y');
735
736 l_exp_instance_rec csi_datastructures_pub.instance_rec;
737 l_exp_inst_ids_list csi_datastructures_pub.id_tbl;
738 l_exp_txn_rec csi_datastructures_pub.transaction_rec;
739 l_expired_status_id NUMBER;
740 l_exp_instances_count NUMBER;
741 l_parent_found CHAR := 'N';
742
743 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
744 l_msg_count NUMBER;
745 l_msg_data VARCHAR2(2000);
746
747 l_error_flag char := 'N';
748 l_error_message varchar2(2000);
749 v_commit_counter number := 0;
750
751 BEGIN
752
753 SAVEPOINT terminate_instances;
754
755 fnd_file.put_line(fnd_file.log, 'Begining of Terminate Expired Instances.');
756
757 --l_expired_status_id := fnd_profile.value('CSI_INST_EXPIRED_STATUS');
758
759 IF p_status_id is NOT NULL Then
760 l_expired_status_id := p_status_id;
761 ELSE
762 l_expired_status_id := fnd_profile.value('CSI_SYS_TERM_INST_STATUS_ID');
763 END iF;
764
765 IF l_expired_status_id is NOT NULL THEN
766
767 FOR exp_inst_rec in exp_inst_cur(l_expired_status_id)
768 LOOP
769
770 fnd_file.put_line(fnd_file.log,'Processing instance '||exp_inst_rec.instance_number); --to_char(exp_inst_rec.instance_id));
771
772 BEGIN
773
774 /* check if this instance is a child of some instance , if yes do not terminate it*/
775
776 BEGIN
777
778 SELECT 'Y'
779 INTO l_parent_found
780 FROM csi_ii_relationships
781 WHERE subject_id = exp_inst_rec.instance_id
782 AND relationship_type_code = 'COMPONENT_OF'
783 AND sysdate between nvl(active_start_date, sysdate -1)
784 and nvl(active_end_date, sysdate + 1);
785
786 EXCEPTION
787 WHEN no_data_found THEN
788 l_parent_found := 'N';
789 WHEN too_many_rows THEN
790 l_parent_found := 'Y';
791 END;
792
793 IF l_parent_found <> 'Y' THEN
794
795 l_exp_txn_rec.transaction_id := fnd_api.g_miss_num;
796 l_exp_txn_rec.transaction_type_id := 5;
797 l_exp_txn_rec.transaction_date := sysdate;
798 l_exp_txn_rec.source_transaction_date := sysdate;
799 l_exp_txn_rec.source_header_ref_id := fnd_api.g_miss_num;
800 l_exp_txn_rec.source_header_ref := fnd_global.conc_request_id; --'TERMINATE_INSTANCES';
801 l_exp_txn_rec.source_line_ref_id := fnd_api.g_miss_num;
802 l_exp_txn_rec.source_line_ref := fnd_api.g_miss_char;
803
804 l_exp_instance_rec.instance_id := exp_inst_rec.instance_id;
805 l_exp_instance_rec.active_end_date := exp_inst_rec.active_end_date;
806 l_exp_instance_rec.object_version_number := exp_inst_rec.object_version_number;
807 l_exp_instance_rec.instance_status_id := l_expired_status_id;
808
809 fnd_file.put_line(fnd_file.log,'Calling csi_item_instance_pub.expire item instance.');
810
811 csi_item_instance_pub.expire_item_instance(
812 p_api_version => 1.0,
813 p_commit => fnd_api.g_false,
814 p_init_msg_list => fnd_api.g_true,
815 p_validation_level => fnd_api.g_valid_level_full,
816 p_instance_rec => l_exp_instance_rec,
817 p_expire_children => fnd_api.g_true,
818 p_txn_rec => l_exp_txn_rec,
819 x_instance_id_lst => l_exp_inst_ids_list,
820 x_return_status => l_return_status,
821 x_msg_count => l_msg_count,
822 x_msg_data => l_msg_data);
823
824 IF l_return_status <> fnd_api.g_ret_sts_success THEN
825 l_error_flag := 'Y';
826 l_error_message := csi_t_gen_utility_pvt.dump_error_stack;
827 raise fnd_api.g_exc_error;
828 END IF;
829
830 fnd_file.put_line(fnd_file.log,'Processed successfully.');
831
832 ELSE
833 fnd_file.put_line(fnd_file.log,'Parent found, so did not qualify.');
834 END IF;
835
836 EXCEPTION
837 WHEN fnd_api.g_exc_error THEN
838 fnd_file.put_line(fnd_file.log, l_error_message);
839 END;
840
841 v_commit_counter := v_commit_counter + 1;
842 IF v_commit_counter = 100 THEN
843 v_commit_counter := 0;
844 commit;
845 END IF;
846
847 END LOOP;
848 commit;
849
850 ELSE
851 fnd_file.put_line(fnd_file.log,'Profile CSI_SYS_TERM_INST_STATUS_ID is not set.');
852 l_error_flag := 'Y';
853 END IF;
854
855 IF l_error_flag = 'Y' THEN
856 retcode := 1;
857 ELSE
858 retcode := 0;
859 errbuf := 'Instances Terminated Successfully.';
860 END IF;
861
862 fnd_file.put_line(fnd_file.log,'End of Terminate Expired Instances.');
863
864 EXCEPTION
865 WHEN fnd_api.g_exc_error THEN
866 ROLLBACK TO terminate_instances;
867 retcode := 1;
868 errbuf := csi_t_gen_utility_pvt.dump_error_stack;
869 WHEN others THEN
870 ROLLBACK TO terminate_instances;
871 retcode := -1;
872 errbuf := substr(sqlerrm, 1, 300);
873 END terminate_instances;
874
875 END CSI_GENERIC_GRP;