[Home] [Help]
PACKAGE BODY: APPS.PA_CI_TYPES_PVT
Source
1 PACKAGE BODY pa_ci_types_pvt AS
2 /* $Header: PACITYVB.pls 120.2 2005/09/08 01:51:15 raluthra noship $ */
3
4 PROCEDURE create_ci_type (
5 p_api_version IN NUMBER := 1.0,
6 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
7 p_commit IN VARCHAR2 := FND_API.g_false,
8 p_validate_only IN VARCHAR2 := FND_API.g_true,
9 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
10 p_name IN VARCHAR2,
11 p_short_name IN VARCHAR2,
12 p_description IN VARCHAR2,
13 p_ci_type_class_code IN VARCHAR2,
14 p_auto_number_flag IN VARCHAR2,
15 p_resolution_required_flag IN VARCHAR2,
16 p_approval_required_flag IN VARCHAR2,
17 p_source_attrs_enabled_flag IN VARCHAR2,
18 p_allow_all_usage_flag IN VARCHAR2,
19 p_start_date_active IN DATE,
20 p_end_date_active IN DATE,
21 p_classification_category IN VARCHAR2,
22 p_reason_category IN VARCHAR2,
23 p_resolution_category IN VARCHAR2,
24 p_attribute_category IN VARCHAR2,
25 p_attribute1 IN VARCHAR2,
26 p_attribute2 IN VARCHAR2,
27 p_attribute3 IN VARCHAR2,
28 p_attribute4 IN VARCHAR2,
29 p_attribute5 IN VARCHAR2,
30 p_attribute6 IN VARCHAR2,
31 p_attribute7 IN VARCHAR2,
32 p_attribute8 IN VARCHAR2,
33 p_attribute9 IN VARCHAR2,
34 p_attribute10 IN VARCHAR2,
35 p_attribute11 IN VARCHAR2,
36 p_attribute12 IN VARCHAR2,
37 p_attribute13 IN VARCHAR2,
38 p_attribute14 IN VARCHAR2,
39 p_attribute15 IN VARCHAR2,
40 p_created_by IN NUMBER DEFAULT fnd_global.user_id,
41 p_creation_date IN DATE DEFAULT SYSDATE,
42 p_last_update_login IN NUMBER DEFAULT fnd_global.user_id,
43 x_ci_type_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
44 x_dist_list_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
45 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
46 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
47 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
48 p_status_list_id IN NUMBER
49 )
50 IS
51 l_rowid VARCHAR2(30);
52 l_obj_sl_rowid VARCHAR2(30);
53 l_obj_status_list_id NUMBER;
54 l_dist_list_id NUMBER; -- Bug 4565156.
55 BEGIN
56 pa_debug.set_err_stack ('PA_CI_TYPES_PVT.CREATE_CI_TYPE');
57
58 IF p_commit = FND_API.G_TRUE THEN
59 SAVEPOINT create_ci_type;
60 END IF;
61
62 IF p_init_msg_list = FND_API.G_TRUE THEN
63 fnd_msg_pub.initialize;
64 END IF;
65
66 x_return_status := 'S';
67 x_msg_count := 0;
68 x_msg_data := '';
69
70 -- Validate the name and short name uniqueness
71 IF (pa_ci_types_util.check_ci_type_name_exists(p_name, p_short_name)) THEN
72 x_return_status := 'E';
73 fnd_message.set_name('PA', 'PA_CI_TYPE_NAME_NOT_UNIQUE');
74 fnd_msg_pub.add();
75 END IF;
76
77 -- Resolution Category is required when Resolution Required Flag is checked
78 IF p_resolution_required_flag = 'Y' AND
79 p_resolution_category IS NULL THEN
80 x_return_status := 'E';
81 fnd_message.set_name('PA', 'PA_CI_TYPE_RESO_CAT_MISSING');
82 fnd_msg_pub.add();
83 END IF;
84
85 -- End Date Active must be later than Start Date Active
86 IF p_start_date_active > p_end_date_active THEN
87 x_return_status := 'E';
88 fnd_message.set_name('PA', 'PA_CI_TYPE_INVALID_DATES');
89 fnd_msg_pub.add();
90 END IF;
91
92 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
93 SELECT pa_ci_types_b_s.NEXTVAL
94 INTO x_ci_type_id
95 FROM sys.dual;
96
97 pa_ci_types_pkg.insert_row(
98 x_rowid => l_rowid,
99 x_ci_type_id => x_ci_type_id,
100 x_ci_type_class_code => p_ci_type_class_code,
101 x_auto_number_flag => p_auto_number_flag,
102 x_resolution_required_flag => p_resolution_required_flag,
103 x_approval_required_flag => p_approval_required_flag,
104 x_source_attrs_enabled_flag => p_source_attrs_enabled_flag,
105 x_allow_all_usage_flag => p_allow_all_usage_flag,
106 x_record_version_number => 0,
107 x_start_date_active => p_start_date_active,
108 x_end_date_active => p_end_date_active,
109 x_classification_category => p_classification_category,
110 x_reason_category => p_reason_category,
111 x_resolution_category => p_resolution_category,
112 x_attribute_category => p_attribute_category,
113 x_attribute1 => p_attribute1,
114 x_attribute2 => p_attribute2,
115 x_attribute3 => p_attribute3,
116 x_attribute4 => p_attribute4,
117 x_attribute5 => p_attribute5,
118 x_attribute6 => p_attribute6,
119 x_attribute7 => p_attribute7,
120 x_attribute8 => p_attribute8,
121 x_attribute9 => p_attribute9,
122 x_attribute10 => p_attribute10,
123 x_attribute11 => p_attribute11,
124 x_attribute12 => p_attribute12,
125 x_attribute13 => p_attribute13,
126 x_attribute14 => p_attribute14,
127 x_attribute15 => p_attribute15,
128 x_name => p_name,
129 x_short_name => p_short_name,
130 x_description => p_description,
131 x_creation_date => p_creation_date,
132 x_created_by => p_created_by,
133 x_last_update_date => p_creation_date,
134 x_last_updated_by => p_created_by,
135 x_last_update_login => p_last_update_login);
136
137 --Creating the distribution list
138 SELECT pa_distribution_lists_s.NEXTVAL
139 INTO x_dist_list_id
140 FROM sys.dual;
141
142 l_dist_list_id := x_dist_list_id; -- Bug 4565156.
143
144 pa_distribution_lists_pvt.create_dist_list (
145 p_validate_only => p_validate_only,
146 p_list_id => l_dist_list_id, -- Bug 4565156.
147 p_name => x_dist_list_id,
148 p_description => NULL,
149 p_creation_date => p_creation_date,
150 p_created_by => p_created_by,
151 p_last_update_date => p_creation_date,
152 p_last_updated_by => p_created_by,
153 p_last_update_login => p_last_update_login,
154 x_return_status => x_return_status,
155 x_msg_count => x_msg_count,
156 x_msg_data => x_msg_data);
157
158 x_dist_list_id := l_dist_list_id; -- Bug 4565156.
159
160 -- Inserting record in pa_obj_status_lists
161 SELECT pa_obj_status_lists_s.NEXTVAL
162 INTO l_obj_status_list_id
163 FROM sys.dual;
164
165 pa_obj_status_lists_pkg.INSERT_ROW (
166 X_ROWID => l_obj_sl_rowid,
167 X_OBJ_STATUS_LIST_ID => l_obj_status_list_id,
168 X_OBJECT_TYPE => 'PA_CI_TYPES',
169 X_OBJECT_ID => x_ci_type_id,
170 X_STATUS_LIST_ID => p_status_list_id,
171 X_STATUS_TYPE => 'CONTROL_ITEM',
172 X_CREATION_DATE => p_creation_date,
173 X_CREATED_BY => p_created_by,
174 X_LAST_UPDATE_DATE => p_creation_date,
175 X_LAST_UPDATED_BY => p_created_by,
176 X_LAST_UPDATE_LOGIN => p_last_update_login
177 );
178 END IF;
179
180 --Associating the dist list to the CI type
181 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
182 pa_object_dist_lists_pvt.create_object_dist_list (
183 p_validate_only => p_validate_only,
184 p_list_id => x_dist_list_id,
185 p_object_type => 'PA_CI_TYPES',
186 p_object_id => x_ci_type_id,
187 p_creation_date => p_creation_date,
188 p_created_by => p_created_by,
189 p_last_update_date => p_creation_date,
190 p_last_updated_by => p_created_by,
191 p_last_update_login => p_last_update_login,
192 x_return_status => x_return_status,
193 x_msg_count => x_msg_count,
194 x_msg_data => x_msg_data);
195 END IF;
196
197 IF p_commit = fnd_api.g_true THEN
198 IF x_return_status = 'S' THEN
199 COMMIT;
200 ELSE
201 ROLLBACK TO create_ci_type;
202 END IF;
203 END IF;
204
205 fnd_msg_pub.count_and_get(p_count => x_msg_count,
206 p_data => x_msg_data);
207
208 pa_debug.reset_err_stack;
209
210 EXCEPTION
211 WHEN OTHERS THEN
212 IF p_commit = fnd_api.g_true THEN
213 ROLLBACK TO create_ci_type;
214 END IF;
215
216 x_return_status := 'U';
217 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_TYPES_PVT',
218 p_procedure_name => 'CREATE_CI_TYPE',
219 p_error_text => SUBSTRB(SQLERRM,1,240));
220
221 fnd_msg_pub.count_and_get(p_count => x_msg_count,
222 p_data => x_msg_data);
223 END create_ci_type;
224
225
226 PROCEDURE update_ci_type (
227 p_api_version IN NUMBER := 1.0,
228 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
229 p_commit IN VARCHAR2 := FND_API.g_false,
230 p_validate_only IN VARCHAR2 := FND_API.g_true,
231 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
232 p_ci_type_id IN NUMBER,
233 p_name IN VARCHAR2,
234 p_short_name IN VARCHAR2,
235 p_description IN VARCHAR2,
236 p_ci_type_class_code IN VARCHAR2,
237 p_auto_number_flag IN VARCHAR2,
238 p_resolution_required_flag IN VARCHAR2,
239 p_approval_required_flag IN VARCHAR2,
240 p_source_attrs_enabled_flag IN VARCHAR2,
241 p_allow_all_usage_flag IN VARCHAR2,
242 p_start_date_active IN DATE,
243 p_end_date_active IN DATE,
244 p_classification_category IN VARCHAR2,
245 p_reason_category IN VARCHAR2,
246 p_resolution_category IN VARCHAR2,
247 p_attribute_category IN VARCHAR2,
248 p_attribute1 IN VARCHAR2,
249 p_attribute2 IN VARCHAR2,
250 p_attribute3 IN VARCHAR2,
251 p_attribute4 IN VARCHAR2,
252 p_attribute5 IN VARCHAR2,
253 p_attribute6 IN VARCHAR2,
254 p_attribute7 IN VARCHAR2,
255 p_attribute8 IN VARCHAR2,
256 p_attribute9 IN VARCHAR2,
257 p_attribute10 IN VARCHAR2,
258 p_attribute11 IN VARCHAR2,
259 p_attribute12 IN VARCHAR2,
260 p_attribute13 IN VARCHAR2,
261 p_attribute14 IN VARCHAR2,
262 p_attribute15 IN VARCHAR2,
263 p_last_updated_by IN NUMBER DEFAULT fnd_global.user_id,
264 p_last_update_date IN DATE DEFAULT SYSDATE,
265 p_last_update_login IN NUMBER DEFAULT fnd_global.user_id,
266 p_record_version_number IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
267 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
268 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
269 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
270 p_status_list_id IN NUMBER,
271 p_obj_status_list_id IN NUMBER
272 )
273 IS
274 l_temp VARCHAR2(1);
275 l_classification_category VARCHAR2(150);
276 l_reason_category VARCHAR2(150);
277 l_resolution_category VARCHAR2(150);
278
279 BEGIN
280 pa_debug.set_err_stack ('PA_CI_TYPES_PVT.UPDATE_CI_TYPE');
281
282 IF p_commit = FND_API.G_TRUE THEN
283 SAVEPOINT update_ci_type;
284 END IF;
285
286 IF p_init_msg_list = FND_API.G_TRUE THEN
287 fnd_msg_pub.initialize;
288 END IF;
289
290 x_return_status := 'S';
291 x_msg_count := 0;
292 x_msg_data := '';
293
294 -- Validate the name and short name uniqueness
295 IF (pa_ci_types_util.check_ci_type_name_exists(p_name, p_short_name, p_ci_type_id)) THEN
296 x_return_status := 'E';
297 fnd_message.set_name('PA','PA_CI_TYPE_NAME_NOT_UNIQUE');
298 fnd_msg_pub.add();
299 END IF;
300
301
302 -- Validate the record version number
303 BEGIN
304 SELECT classification_category,
305 reason_category,
306 resolution_category
307 INTO l_classification_category,
308 l_reason_category,
309 l_resolution_category
310 FROM pa_ci_types_vl
311 WHERE ci_type_id = p_ci_type_id
312 AND record_version_number = p_record_version_number;
313
314 p_record_version_number := p_record_version_number+1;
315 EXCEPTION
316 WHEN NO_DATA_FOUND THEN
317 x_return_status := 'E';
318 fnd_message.set_name('PA','PA_XC_RECORD_CHANGED');
319 fnd_msg_pub.add();
320 END;
321
322 -- Cannot change the category if there is a control item using the class code
323 IF x_return_status = 'S' AND
324 l_classification_category <> p_classification_category THEN
325 BEGIN
326 SELECT 'X' INTO l_temp
327 FROM PA_CONTROL_ITEMS
328 WHERE ci_type_id = p_ci_type_id
329 AND classification_code_id IS NOT NULL
330 AND ROWNUM < 2;
331
332 x_return_status := 'E';
333 fnd_message.set_name('PA','PA_CI_TYPE_CLASS_CAT_IN_USE');
334 fnd_msg_pub.add();
335 EXCEPTION
336 WHEN NO_DATA_FOUND THEN
337 NULL;
338 END;
339 END IF;
340
341 IF x_return_status = 'S' AND
342 l_reason_category <> p_reason_category THEN
343 BEGIN
344 SELECT 'X' INTO l_temp
345 FROM PA_CONTROL_ITEMS
346 WHERE ci_type_id = p_ci_type_id
347 AND reason_code_id IS NOT NULL
348 AND ROWNUM < 2;
349
350 x_return_status := 'E';
351 fnd_message.set_name('PA','PA_CI_TYPE_REASON_CAT_IN_USE');
352 fnd_msg_pub.add();
353 EXCEPTION
354 WHEN NO_DATA_FOUND THEN
355 NULL;
356 END;
357 END IF;
358
359 IF x_return_status = 'S' AND
360 l_resolution_category <> p_resolution_category THEN
361 BEGIN
362 SELECT 'X' INTO l_temp
363 FROM PA_CONTROL_ITEMS
364 WHERE ci_type_id = p_ci_type_id
365 AND resolution_code_id IS NOT NULL
366 AND ROWNUM < 2;
367
368 x_return_status := 'E';
369 fnd_message.set_name('PA','PA_CI_TYPE_RESO_CAT_IN_USE');
370 fnd_msg_pub.add();
371 EXCEPTION
372 WHEN NO_DATA_FOUND THEN
373 NULL;
374 END;
375 END IF;
376
377 -- Resolution Category is required when Resolution Required Flag is checked
378 IF p_resolution_required_flag = 'Y' AND
379 p_resolution_category IS NULL THEN
380 x_return_status := 'E';
381 fnd_message.set_name('PA', 'PA_CI_TYPE_RESO_CAT_MISSING');
382 fnd_msg_pub.add();
383 END IF;
384
385 -- Cannot change from Manual to Automatic numbering if an item exists
386 IF p_auto_number_flag = 'Y' THEN
387 BEGIN
388 SELECT 'X'
389 INTO l_temp
390 FROM pa_ci_types_b cit,
391 pa_control_items ci
392 WHERE cit.ci_type_id = p_ci_type_id
393 AND cit.auto_number_flag <> 'Y'
394 AND ci.ci_type_id = p_ci_type_id
395 AND ROWNUM=1;
396
397 x_return_status := 'E';
398 fnd_message.set_name('PA', 'PA_CI_TYPE_NO_SWITCH_NUM');
399 fnd_msg_pub.add();
400 EXCEPTION
401 WHEN OTHERS THEN
402 NULL;
403 END;
404 END IF;
405
406 -- End Date Active must be later than Start Date Active
407 IF p_start_date_active > p_end_date_active THEN
408 x_return_status := 'E';
409 fnd_message.set_name('PA', 'PA_CI_TYPE_INVALID_DATES');
410 fnd_msg_pub.add();
411 END IF;
412
413 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
414 pa_ci_types_pkg.update_row(
415 x_ci_type_id => p_ci_type_id,
416 x_ci_type_class_code => p_ci_type_class_code,
417 x_auto_number_flag => p_auto_number_flag,
418 x_resolution_required_flag => p_resolution_required_flag,
419 x_approval_required_flag => p_approval_required_flag,
420 x_source_attrs_enabled_flag => p_source_attrs_enabled_flag,
421 x_allow_all_usage_flag => p_allow_all_usage_flag,
422 x_record_version_number => p_record_version_number,
423 x_start_date_active => p_start_date_active,
427 x_resolution_category => p_resolution_category,
424 x_end_date_active => p_end_date_active,
425 x_classification_category => p_classification_category,
426 x_reason_category => p_reason_category,
428 x_attribute_category => p_attribute_category,
429 x_attribute1 => p_attribute1,
430 x_attribute2 => p_attribute2,
431 x_attribute3 => p_attribute3,
432 x_attribute4 => p_attribute4,
433 x_attribute5 => p_attribute5,
434 x_attribute6 => p_attribute6,
435 x_attribute7 => p_attribute7,
436 x_attribute8 => p_attribute8,
437 x_attribute9 => p_attribute9,
438 x_attribute10 => p_attribute10,
439 x_attribute11 => p_attribute11,
440 x_attribute12 => p_attribute12,
441 x_attribute13 => p_attribute13,
442 x_attribute14 => p_attribute14,
443 x_attribute15 => p_attribute15,
444 x_name => p_name,
445 x_short_name => p_short_name,
446 x_description => p_description,
447 x_last_update_date => p_last_update_date,
448 x_last_updated_by => p_last_updated_by,
449 x_last_update_login => p_last_update_login);
450
451 pa_obj_status_lists_pkg.UPDATE_ROW (
452 X_OBJ_STATUS_LIST_ID => p_obj_status_list_id,
453 X_OBJECT_TYPE => 'PA_CI_TYPES',
454 X_OBJECT_ID => p_ci_type_id,
455 X_STATUS_LIST_ID => p_status_list_id,
456 X_STATUS_TYPE => 'CONTROL_ITEM',
457 X_LAST_UPDATE_DATE => p_last_update_date,
458 X_LAST_UPDATED_BY => p_last_updated_by,
459 X_LAST_UPDATE_LOGIN => p_last_update_login
460 );
461 END IF;
462
463 IF p_commit = fnd_api.g_true THEN
464 IF x_return_status = 'S' THEN
465 COMMIT;
466 ELSE
467 ROLLBACK TO update_ci_type;
468 END IF;
469 END IF;
470
471 fnd_msg_pub.count_and_get(p_count => x_msg_count,
472 p_data => x_msg_data);
473
474 pa_debug.reset_err_stack;
475
476 EXCEPTION
477 WHEN OTHERS THEN
478 IF p_commit = fnd_api.g_true THEN
479 ROLLBACK TO update_ci_type;
480 END IF;
481
482 x_return_status := 'U';
483 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_TYPES_PVT',
484 p_procedure_name => 'UPDATE_CI_TYPE',
485 p_error_text => SUBSTRB(SQLERRM,1,240));
486
487 fnd_msg_pub.count_and_get(p_count => x_msg_count,
488 p_data => x_msg_data);
489 END update_ci_type;
490
491
492 PROCEDURE delete_ci_type (
493 p_api_version IN NUMBER := 1.0,
494 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
495 p_commit IN VARCHAR2 := FND_API.g_false,
496 p_validate_only IN VARCHAR2 := FND_API.g_true,
497 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
498 p_ci_type_id IN NUMBER,
499 p_record_version_number IN NUMBER,
500 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
501 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
502 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
503 p_obj_status_list_id IN NUMBER
504 )
505 IS
506 CURSOR c_ci_type_usage IS
507 SELECT *
508 FROM pa_ci_type_usage
509 WHERE ci_type_id = p_ci_type_id;
510
511 CURSOR c_impact_type_usage IS
512 SELECT *
513 FROM pa_ci_impact_type_usage
514 WHERE ci_type_id = p_ci_type_id;
515
516 CURSOR c_obj_status_lists IS
517 SELECT *
518 FROM pa_obj_status_lists
519 WHERE obj_status_list_id = p_obj_status_list_id;
520
521 l_temp VARCHAR2(1);
522 BEGIN
523 pa_debug.set_err_stack ('PA_CI_TYPES_PVT.DELETE_CI_TYPE');
524
525 IF p_commit = FND_API.G_TRUE THEN
526 SAVEPOINT delete_ci_type;
527 END IF;
528
529 IF p_init_msg_list = FND_API.G_TRUE THEN
530 fnd_msg_pub.initialize;
531 END IF;
532
533 x_return_status := 'S';
534 x_msg_count := 0;
535 x_msg_data := '';
536
537 -- Validate the record version number
538 BEGIN
539 SELECT 'X' INTO l_temp
540 FROM pa_ci_types_vl
541 WHERE ci_type_id = p_ci_type_id
542 AND record_version_number = p_record_version_number;
543 EXCEPTION
544 WHEN NO_DATA_FOUND THEN
545 x_return_status := 'E';
546 fnd_message.set_name('PA','PA_XC_RECORD_CHANGED');
547 fnd_msg_pub.add();
548 END;
549
550 -- Cannot delete CI Type if CI exists
551 BEGIN
552 SELECT 'X' INTO l_temp
553 FROM pa_control_items
554 WHERE ci_type_id = p_ci_type_id
555 AND ROWNUM=1;
556
557 x_return_status := 'E';
558 fnd_message.set_name('PA','PA_CI_TYPE_NO_DELETE_IN_USE');
559 fnd_msg_pub.add();
560 EXCEPTION
561 WHEN OTHERS THEN
562 NULL;
563 END;
564
565 /* Changes for the bug# 3941304 starts here */
566 -- Cannot Delete CI Type if it has some selected statuses
567 -- for financial imapct implementation/inclusion
568 if Pa_Fp_Control_Items_Utils.validate_fp_ci_type_delete( p_ci_type_id) <> 'Y' then
569 x_return_status := 'E';
570 fnd_message.set_name('PA','FP_CI_TYPE_DEL_NOT_ALLOWED');
571 fnd_msg_pub.add();
572 end if;
576 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
573 /* Changes for the bug# 3941304 ends here */
574
575 --Deleting the associated distribution list
577 pa_object_dist_lists_pvt.delete_assoc_dist_lists (
578 p_validate_only => p_validate_only,
579 p_object_type => 'PA_CI_TYPES',
580 p_object_id => p_ci_type_id,
581 x_return_status => x_return_status,
582 x_msg_count => x_msg_count,
583 x_msg_data => x_msg_data);
584 END IF;
585
586 --Deleting the CI type usage
587 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
588 FOR rec IN c_ci_type_usage LOOP
589 pa_ci_type_usage_pvt.delete_ci_type_usage (
590 p_validate_only => p_validate_only,
591 p_ci_type_usage_id => rec.ci_type_usage_id,
592 p_project_type_id => rec.project_type_id,
593 p_ci_type_id => p_ci_type_id,
594 x_return_status => x_return_status,
595 x_msg_count => x_msg_count,
596 x_msg_data => x_msg_data);
597
598 EXIT WHEN x_return_status<>'S';
599 END LOOP;
600 END IF;
601
602 --Deleting the impact type usage
603 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
604 FOR rec IN c_impact_type_usage LOOP
605 pa_ci_impact_type_usage_pvt.delete_ci_impact_type_usage (
606 p_validate_only => p_validate_only,
607 p_ci_impact_type_usage_id => rec.ci_impact_type_usage_id,
608 p_impact_type_code => rec.impact_type_code,
609 p_ci_type_class_code => NULL,
610 p_ci_type_id => p_ci_type_id,
611 x_return_status => x_return_status,
612 x_msg_count => x_msg_count,
613 x_msg_data => x_msg_data);
614
615 EXIT WHEN x_return_status<>'S';
616 END LOOP;
617 END IF;
618
619 --Deleting the CI type itself
620 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
621 pa_ci_types_pkg.delete_row(
622 x_ci_type_id => p_ci_type_id);
623
624 --Deleting the association from pa_obj_status_lists
625 pa_obj_status_lists_pkg.delete_row(
626 x_obj_status_list_id => p_obj_status_list_id);
627 END IF;
628
629 IF p_commit = fnd_api.g_true THEN
630 IF x_return_status = 'S' THEN
631 COMMIT;
632 ELSE
633 ROLLBACK TO delete_ci_type;
634 END IF;
635 END IF;
636
637 fnd_msg_pub.count_and_get(p_count => x_msg_count,
638 p_data => x_msg_data);
639
640 pa_debug.reset_err_stack;
641
642 EXCEPTION
643 WHEN OTHERS THEN
644 IF p_commit = fnd_api.g_true THEN
645 ROLLBACK TO delete_ci_type;
646 END IF;
647
648 x_return_status := 'U';
649 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_TYPES_PVT',
650 p_procedure_name => 'DELETE_CI_TYPE',
651 p_error_text => SUBSTRB(SQLERRM,1,240));
652
653 fnd_msg_pub.count_and_get(p_count => x_msg_count,
654 p_data => x_msg_data);
655 END delete_ci_type;
656
657 END pa_ci_types_pvt;