[Home] [Help]
PACKAGE BODY: APPS.PA_CI_TYPES_PVT
Source
1 PACKAGE BODY pa_ci_types_pvt AS
2 /* $Header: PACITYVB.pls 120.2.12010000.11 2009/10/28 19:34:13 cklee ship $ */
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 --start:| 16-FEB-2009 cklee R12.1.2 setup ehancement
44 P_APPROVAL_TYPE_CODE IN VARCHAR2 DEFAULT 'STANDARD',
45 P_SUBCONTRACTOR_REPORTING_FLAG IN VARCHAR2 DEFAULT 'N',
46 P_PREFIX_AUTO_NUMBER IN VARCHAR2 DEFAULT NULL,
47 --end:| 16-FEB-2009 cklee R12.1.2 setup ehancement
48 --|start 29-APR-2009 cklee R12.1.2 setup ehancement v2
49 P_IMPACT_BUDGET_TYPE_CODE IN VARCHAR2 DEFAULT 'NA',
50 P_COST_COL_FLAG IN VARCHAR2 DEFAULT 'N',
51 P_REV_COL_FLAG IN VARCHAR2 DEFAULT 'N',
52 P_DIR_COST_REG_FLAG IN VARCHAR2 DEFAULT 'N',
53 P_SUPP_COST_REG_FLAG IN VARCHAR2 DEFAULT 'N',
54 P_DIR_REG_REV_COL_FLAG IN VARCHAR2 DEFAULT 'N',
55 --|end 29-APR-2009 cklee R12.1.2 setup ehancement v2
56 x_ci_type_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
57 x_dist_list_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
58 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
59 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
60 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
61 p_status_list_id IN NUMBER
62 )
63 IS
64 l_rowid VARCHAR2(30);
65 l_obj_sl_rowid VARCHAR2(30);
66 l_obj_status_list_id NUMBER;
67 l_dist_list_id NUMBER; -- Bug 4565156.
68 l_approval_required_flag varchar2(1) := p_approval_required_flag; --28-oct-2009 cklee fxied bug: 9063248
69
70 BEGIN
71 pa_debug.set_err_stack ('PA_CI_TYPES_PVT.CREATE_CI_TYPE');
72
73 IF p_commit = FND_API.G_TRUE THEN
74 SAVEPOINT create_ci_type;
75 END IF;
76
77 IF p_init_msg_list = FND_API.G_TRUE THEN
78 fnd_msg_pub.initialize;
79 END IF;
80
81 x_return_status := 'S';
82 x_msg_count := 0;
83 x_msg_data := '';
84
85 -- start: 28-oct-2009 cklee fxied bug: 9063248
86 IF P_APPROVAL_TYPE_CODE = 'AUTOMATIC_APPROVAL' THEN
87 l_approval_required_flag := 'N';
88 ELSE
89 l_approval_required_flag := 'Y';
90 END IF;
91 -- end: 28-oct-2009 cklee fxied bug: 9063248
92 -- Validate the name and short name uniqueness
93 IF (pa_ci_types_util.check_ci_type_name_exists(p_name, p_short_name)) THEN
94 x_return_status := 'E';
95 fnd_message.set_name('PA', 'PA_CI_TYPE_NAME_NOT_UNIQUE');
96 fnd_msg_pub.add();
97 END IF;
98
99 -- Resolution Category is required when Resolution Required Flag is checked
100 IF p_resolution_required_flag = 'Y' AND
101 p_resolution_category IS NULL THEN
102 x_return_status := 'E';
103 fnd_message.set_name('PA', 'PA_CI_TYPE_RESO_CAT_MISSING');
104 fnd_msg_pub.add();
105 END IF;
106
107 -- End Date Active must be later than Start Date Active
108 IF p_start_date_active > p_end_date_active THEN
109 x_return_status := 'E';
110 fnd_message.set_name('PA', 'PA_CI_TYPE_INVALID_DATES');
111 fnd_msg_pub.add();
112 END IF;
113
114 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
115 SELECT pa_ci_types_b_s.NEXTVAL
116 INTO x_ci_type_id
117 FROM sys.dual;
118
119 pa_ci_types_pkg.insert_row(
120 x_rowid => l_rowid,
121 x_ci_type_id => x_ci_type_id,
122 x_ci_type_class_code => p_ci_type_class_code,
123 x_auto_number_flag => p_auto_number_flag,
124 x_resolution_required_flag => p_resolution_required_flag,
125 x_approval_required_flag => l_approval_required_flag, --28-oct-2009 cklee fxied bug: 9063248--p_approval_required_flag,
126 x_source_attrs_enabled_flag => p_source_attrs_enabled_flag,
127 x_allow_all_usage_flag => p_allow_all_usage_flag,
128 x_record_version_number => 0,
129 x_start_date_active => p_start_date_active,
130 x_end_date_active => p_end_date_active,
131 x_classification_category => p_classification_category,
132 x_reason_category => p_reason_category,
133 x_resolution_category => p_resolution_category,
134 x_attribute_category => p_attribute_category,
135 x_attribute1 => p_attribute1,
136 x_attribute2 => p_attribute2,
137 x_attribute3 => p_attribute3,
138 x_attribute4 => p_attribute4,
139 x_attribute5 => p_attribute5,
140 x_attribute6 => p_attribute6,
141 x_attribute7 => p_attribute7,
142 x_attribute8 => p_attribute8,
143 x_attribute9 => p_attribute9,
144 x_attribute10 => p_attribute10,
145 x_attribute11 => p_attribute11,
146 x_attribute12 => p_attribute12,
147 x_attribute13 => p_attribute13,
148 x_attribute14 => p_attribute14,
149 x_attribute15 => p_attribute15,
150 x_name => p_name,
151 x_short_name => p_short_name,
152 x_description => p_description,
153 x_creation_date => p_creation_date,
154 x_created_by => p_created_by,
155 x_last_update_date => p_creation_date,
156 x_last_updated_by => p_created_by,
157 x_last_update_login => p_last_update_login,
158 --start:| 16-FEB-2009 cklee R12.1.2 setup ehancement
159 X_APPROVAL_TYPE_CODE => P_APPROVAL_TYPE_CODE,
160 X_SUBCONTRACTOR_REPORTING_FLAG => P_SUBCONTRACTOR_REPORTING_FLAG,
161 X_PREFIX_AUTO_NUMBER => P_PREFIX_AUTO_NUMBER,
162 --end:| 16-FEB-2009 cklee R12.1.2 setup ehancement
163 --|start 29-APR-2009 cklee R12.1.2 setup ehancement v2
164 X_IMPACT_BUDGET_TYPE_CODE => P_IMPACT_BUDGET_TYPE_CODE,
165 X_COST_COL_FLAG => P_COST_COL_FLAG,
166 X_REV_COL_FLAG => P_REV_COL_FLAG,
167 X_DIR_COST_REG_FLAG => P_DIR_COST_REG_FLAG,
168 X_SUPP_COST_REG_FLAG => P_SUPP_COST_REG_FLAG,
169 X_DIR_REG_REV_COL_FLAG => P_DIR_REG_REV_COL_FLAG);
170 --|end 29-APR-2009 cklee R12.1.2 setup ehancement v2
171
172
173 --Creating the distribution list
174 SELECT pa_distribution_lists_s.NEXTVAL
175 INTO x_dist_list_id
176 FROM sys.dual;
177
178 l_dist_list_id := x_dist_list_id; -- Bug 4565156.
179
180 pa_distribution_lists_pvt.create_dist_list (
181 p_validate_only => p_validate_only,
182 p_list_id => l_dist_list_id, -- Bug 4565156.
183 p_name => x_dist_list_id,
184 p_description => NULL,
185 p_creation_date => p_creation_date,
186 p_created_by => p_created_by,
187 p_last_update_date => p_creation_date,
188 p_last_updated_by => p_created_by,
189 p_last_update_login => p_last_update_login,
190 x_return_status => x_return_status,
191 x_msg_count => x_msg_count,
192 x_msg_data => x_msg_data);
193
194 x_dist_list_id := l_dist_list_id; -- Bug 4565156.
195
196 -- Inserting record in pa_obj_status_lists
197 SELECT pa_obj_status_lists_s.NEXTVAL
198 INTO l_obj_status_list_id
199 FROM sys.dual;
200
201 pa_obj_status_lists_pkg.INSERT_ROW (
202 X_ROWID => l_obj_sl_rowid,
203 X_OBJ_STATUS_LIST_ID => l_obj_status_list_id,
204 X_OBJECT_TYPE => 'PA_CI_TYPES',
205 X_OBJECT_ID => x_ci_type_id,
206 X_STATUS_LIST_ID => p_status_list_id,
207 X_STATUS_TYPE => 'CONTROL_ITEM',
208 X_CREATION_DATE => p_creation_date,
209 X_CREATED_BY => p_created_by,
210 X_LAST_UPDATE_DATE => p_creation_date,
211 X_LAST_UPDATED_BY => p_created_by,
212 X_LAST_UPDATE_LOGIN => p_last_update_login
213 );
214 END IF;
215
216 --Associating the dist list to the CI type
217 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
218 pa_object_dist_lists_pvt.create_object_dist_list (
219 p_validate_only => p_validate_only,
220 p_list_id => x_dist_list_id,
221 p_object_type => 'PA_CI_TYPES',
222 p_object_id => x_ci_type_id,
223 p_creation_date => p_creation_date,
224 p_created_by => p_created_by,
225 p_last_update_date => p_creation_date,
226 p_last_updated_by => p_created_by,
227 p_last_update_login => p_last_update_login,
228 x_return_status => x_return_status,
229 x_msg_count => x_msg_count,
230 x_msg_data => x_msg_data);
231 END IF;
232
233 IF p_commit = fnd_api.g_true THEN
234 IF x_return_status = 'S' THEN
235 COMMIT;
236 ELSE
237 ROLLBACK TO create_ci_type;
238 END IF;
239 END IF;
240
241 fnd_msg_pub.count_and_get(p_count => x_msg_count,
242 p_data => x_msg_data);
243
244 pa_debug.reset_err_stack;
245
246 EXCEPTION
247
248 WHEN G_EXCEPTION_ERROR THEN
249
250 IF p_commit = 'T' THEN
251 ROLLBACK TO create_ci_type;
252 END IF;
253 x_return_status := FND_API.G_RET_STS_ERROR;
254 FND_MSG_PUB.Count_And_Get
255 (p_count => x_msg_count,
256 p_data => x_msg_data);
257
258
259 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
260
261 IF p_commit = 'T' THEN
262 ROLLBACK TO create_ci_type;
263 END IF;
264 x_return_status := FND_API.G_RET_STS_ERROR;
265 FND_MSG_PUB.Count_And_Get
266 (p_count => x_msg_count,
267 p_data => x_msg_data);
268 WHEN OTHERS THEN
269 IF p_commit = fnd_api.g_true THEN
270 ROLLBACK TO create_ci_type;
271 END IF;
272
273 x_return_status := 'U';
274 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_TYPES_PVT',
275 p_procedure_name => 'CREATE_CI_TYPE',
276 p_error_text => SUBSTRB(SQLERRM,1,240));
277
278 fnd_msg_pub.count_and_get(p_count => x_msg_count,
279 p_data => x_msg_data);
280 END create_ci_type;
281
282
283 PROCEDURE update_ci_type (
284 p_api_version IN NUMBER := 1.0,
285 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
286 p_commit IN VARCHAR2 := FND_API.g_false,
287 p_validate_only IN VARCHAR2 := FND_API.g_true,
288 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
289 p_ci_type_id IN NUMBER,
290 p_name IN VARCHAR2,
291 p_short_name IN VARCHAR2,
292 p_description IN VARCHAR2,
293 p_ci_type_class_code IN VARCHAR2,
294 p_auto_number_flag IN VARCHAR2,
295 p_resolution_required_flag IN VARCHAR2,
296 p_approval_required_flag IN VARCHAR2,
297 p_source_attrs_enabled_flag IN VARCHAR2,
298 p_allow_all_usage_flag IN VARCHAR2,
299 p_start_date_active IN DATE,
300 p_end_date_active IN DATE,
301 p_classification_category IN VARCHAR2,
302 p_reason_category IN VARCHAR2,
303 p_resolution_category IN VARCHAR2,
304 p_attribute_category IN VARCHAR2,
305 p_attribute1 IN VARCHAR2,
306 p_attribute2 IN VARCHAR2,
307 p_attribute3 IN VARCHAR2,
308 p_attribute4 IN VARCHAR2,
309 p_attribute5 IN VARCHAR2,
310 p_attribute6 IN VARCHAR2,
311 p_attribute7 IN VARCHAR2,
312 p_attribute8 IN VARCHAR2,
313 p_attribute9 IN VARCHAR2,
314 p_attribute10 IN VARCHAR2,
315 p_attribute11 IN VARCHAR2,
316 p_attribute12 IN VARCHAR2,
317 p_attribute13 IN VARCHAR2,
318 p_attribute14 IN VARCHAR2,
319 p_attribute15 IN VARCHAR2,
320 p_last_updated_by IN NUMBER DEFAULT fnd_global.user_id,
321 p_last_update_date IN DATE DEFAULT SYSDATE,
322 p_last_update_login IN NUMBER DEFAULT fnd_global.user_id,
323 --start:| 16-FEB-2009 cklee R12.1.2 setup ehancement
324 P_APPROVAL_TYPE_CODE IN VARCHAR2 DEFAULT 'STANDARD',
325 P_SUBCONTRACTOR_REPORTING_FLAG IN VARCHAR2 DEFAULT 'N',
326 P_PREFIX_AUTO_NUMBER IN VARCHAR2 DEFAULT NULL,
327 --end:| 16-FEB-2009 cklee R12.1.2 setup ehancement
328 --|start 29-APR-2009 cklee R12.1.2 setup ehancement v2
329 P_IMPACT_BUDGET_TYPE_CODE IN VARCHAR2 DEFAULT 'NA',
330 P_COST_COL_FLAG IN VARCHAR2 DEFAULT 'N',
331 P_REV_COL_FLAG IN VARCHAR2 DEFAULT 'N',
332 P_DIR_COST_REG_FLAG IN VARCHAR2 DEFAULT 'N',
333 P_SUPP_COST_REG_FLAG IN VARCHAR2 DEFAULT 'N',
334 P_DIR_REG_REV_COL_FLAG IN VARCHAR2 DEFAULT 'N',
335 --|end 29-APR-2009 cklee R12.1.2 setup ehancement v2
336 p_record_version_number IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
337 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
338 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
339 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
340 p_status_list_id IN NUMBER,
341 p_obj_status_list_id IN NUMBER
342 )
343 IS
344 l_temp VARCHAR2(1);
345 l_classification_category VARCHAR2(150);
346 l_reason_category VARCHAR2(150);
347 l_resolution_category VARCHAR2(150);
348
349 -- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
350 -- if the current change request approval type code is EXTERNAL_APPROVAL and control item's pco status
351 -- has been recorded, then user is not allowed to change the approval type.
352 cursor c_pco_st_exists is
353 select 1
354 from pa_control_items ci,
355 pa_ci_types_b cip
356 where ci.ci_type_id = cip.ci_type_id
357 and cip.ci_type_class_code = 'CHANGE_REQUEST'
358 and cip.ci_type_id = p_ci_type_id
359 and cip.approval_type_code = 'EXTERNAL_APPROVAL'
360 and ci.pco_status_code is not null;
361 l_pco_st_exists number;
362 -- end: 26-Jun-2009 cklee Modified for the Bug# 8633676
363
364 -- start: cklee 09/30/09 bug: 8974414
365 -- get budget update method before user make changes
366 CURSOR budget_options_csr (p_ci_type_id pa_ci_types_b.CI_TYPE_ID%type) is
367 select ci.IMPACT_BUDGET_TYPE_CODE,
368 ci.COST_COL_FLAG,
369 ci.REV_COL_FLAG,
370 ci.DIR_COST_REG_FLAG,
371 ci.SUPP_COST_REG_FLAG,
372 ci.DIR_REG_REV_COL_FLAG
373 from pa_ci_types_b ci
374 where ci.ci_type_id = p_ci_type_id;
375
376 budget_options_rec budget_options_csr%ROWTYPE;
377
378 -- check if impacts exists for control item type
379 CURSOR validate_impacts_csr (p_ci_type_id pa_ci_types_b.CI_TYPE_ID%type) is
380 select 1
381 from pa_control_items pci,
382 pa_ci_impacts pc
383 where pci.ci_type_id = p_ci_type_id
384 and pci.ci_id = pc.ci_id;
385
386 l_impacts_exists boolean := false;
387 l_impacts_dummy number;
388
389 CURSOR budget_method(p_lookup_code varchar2) is
390 select meaning
391 from pa_lookups
392 where lookup_type = 'PA_CI_IMPACT_BUDGET_TYPES'
393 and lookup_code = p_lookup_code;
394
395 l_update_budget_method varchar2(30);
396 --EDIT_PLANNED_AMOUNTS
397 --'DIRECT_COST_ENTRY'
398
399
400 -- end: cklee 09/30/09 bug: 8974414
401
402 CURSOR val_impacts_csr (p_ci_type_id pa_ci_types_b.CI_TYPE_ID%type,
403 p_impact_type_code varchar2) is
404 select pc.impact_type_code,
405 luk.meaning impact_type_name
406 from pa_control_items pci,
407 pa_ci_impacts pc,
408 pa_lookups luk
409 where pci.ci_type_id = p_ci_type_id
410 and pci.ci_id = pc.ci_id
411 and pc.impact_type_code = luk.lookup_code
412 and luk.lookup_type = 'PA_CI_IMPACT_TYPES'
413 and pc.impact_type_code = p_impact_type_code;
414
415 val_impacts_rec val_impacts_csr%ROWTYPE;
416 /* future use
417 CURSOR val_Dir_impacts_csr (p_ci_type_id pa_ci_types_b.CI_TYPE_ID%type,
418 p_impact_type_code varchar2) is
419 select pc.impact_type_code,
420 luk.meaning impact_type_name
421 from pa_control_items pci,
422 pa_ci_impacts pc,
423 pa_lookups luk,
424 pa_budget_versions pbv,
425 pa_resource_assignments pra
426 where pci.ci_type_id = p_ci_type_id
427 and pci.ci_id = pc.ci_id
428 and pc.impact_type_code = luk.lookup_code
429 and luk.lookup_type = 'PA_CI_IMPACT_TYPES'
430 and pc.impact_type_code = p_impact_type_code
431 and pbv.ci_id = pci.ci_id
432 and pra.budget_version_id = pbv.budget_version_id
433 and total_plan_raw_cost is not null
434 and not exists (select 1
435 from pa_ci_supplier_details sup_det
436 where sup_det.ci_id=pbv.ci_id
437 and sup_det.task_id=pra.task_id
438 and sup_det.resource_list_member_id=pra.resource_list_member_id);
439
440 val_Dir_impacts_rec val_Dir_impacts_csr%ROWTYPE;
441
442 CURSOR val_Supp_impacts_csr (p_ci_type_id pa_ci_types_b.CI_TYPE_ID%type,
443 p_impact_type_code varchar2) is
444 select pc.impact_type_code,
445 luk.meaning impact_type_name
446 from pa_control_items pci,
447 pa_ci_impacts pc,
448 pa_lookups luk,
449 pa_budget_versions pbv,
450 pa_resource_assignments pra
451 where pci.ci_type_id = p_ci_type_id
452 and pci.ci_id = pc.ci_id
453 and pc.impact_type_code = luk.lookup_code
454 and luk.lookup_type = 'PA_CI_IMPACT_TYPES'
455 and pc.impact_type_code = p_impact_type_code
456 and pbv.ci_id = pci.ci_id
457 and pra.budget_version_id = pbv.budget_version_id
458 and total_plan_raw_cost is not null
459 and exists (select 1
460 from pa_ci_supplier_details sup_det
461 where sup_det.ci_id=pbv.ci_id
462 and sup_det.task_id=pra.task_id
463 and sup_det.resource_list_member_id=pra.resource_list_member_id);
464
465 val_Supp_impacts_rec val_Supp_impacts_csr%ROWTYPE;
466 */
467
468 -- Check if direct/supplier cost regions have been created in budgets?
469 CURSOR new_fin_impacts_csr (p_ci_type_id pa_ci_types_b.CI_TYPE_ID%type) is
470 select 1
471 from pa_ci_types_v pct,
472 pa_control_items pci,
473 pa_budget_versions pbv
474 where pct.ci_type_id = pci.ci_type_id
475 and pci.ci_id = pbv.ci_id
476 and pct.ci_type_id = p_ci_type_id;
477
478 l_new_fin_imp_exists boolean := false;
479 l_new_fin_imp_dummy number;
480
481 CURSOR imp_code_csr (p_ci_type_id pa_ci_types_b.CI_TYPE_ID%type,
482 p_impact_type_code varchar2) is
483 select luk.meaning impact_type_name
484 from pa_lookups luk
485 where luk.lookup_type = 'PA_CI_IMPACT_TYPES'
486 and luk.lookup_code = p_impact_type_code;
487
488 imp_code_rec imp_code_csr%ROWTYPE;
489 l_approval_required_flag varchar2(1) := p_approval_required_flag; --28-oct-2009 cklee fxied bug: 9063248
490
491 BEGIN
492 pa_debug.set_err_stack ('PA_CI_TYPES_PVT.UPDATE_CI_TYPE');
493
494 IF p_commit = FND_API.G_TRUE THEN
495 SAVEPOINT update_ci_type;
496 END IF;
497
498 IF p_init_msg_list = FND_API.G_TRUE THEN
499 fnd_msg_pub.initialize;
500 END IF;
501
502 x_return_status := 'S';
503 x_msg_count := 0;
504 x_msg_data := '';
505
506 -- start: 28-oct-2009 cklee fxied bug: 9063248
507 IF P_APPROVAL_TYPE_CODE = 'AUTOMATIC_APPROVAL' THEN
508 l_approval_required_flag := 'N';
509 ELSE
510 l_approval_required_flag := 'Y';
511 END IF;
512 -- end: 28-oct-2009 cklee fxied bug: 9063248
513
514 -- start: cklee 09/30/09 bug: 8974414
515 -- 1. get budget update method before user make changes
516 -- 2. check if impacts exists for control item type
517 -- 3. raise error if impact exists and user try to change
518 -- the update budget method
519
520 -- 1. get budget update method before user make changes
521
522 FOR budget_options_rec IN budget_options_csr (p_ci_type_id)
523 LOOP
524 -- user try to switch update budget method
525 -- 1.1 Direct Cost (budget and forecast) -> NA
526 -- 1.2 Direct Cost (budget and forecast) -> Edit plan amounts
527 IF (budget_options_rec.IMPACT_BUDGET_TYPE_CODE = 'DIRECT_COST_ENTRY') THEN
528 IF (P_IMPACT_BUDGET_TYPE_CODE <> 'DIRECT_COST_ENTRY') THEN
529 OPEN validate_impacts_csr(p_ci_type_id);
530 FETCH validate_impacts_csr INTO l_impacts_dummy;
531 IF validate_impacts_csr%FOUND THEN
532 l_impacts_exists := TRUE;
533 END IF;
534 CLOSE validate_impacts_csr;
535
536 IF l_impacts_exists THEN
537
538 OPEN budget_method(budget_options_rec.IMPACT_BUDGET_TYPE_CODE);
539 FETCH budget_method INTO l_update_budget_method;
540 CLOSE budget_method;
541
542 PA_UTILS.Add_Message( p_app_short_name => 'PA'
543 ,p_msg_name => 'PA_CI_IMPACT_TU_IN_USE'
544 ,p_token1 => 'IMPACT'
545 ,p_value1 => l_update_budget_method);
546
547 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
548
549 END IF;
550 --END IF;
551 -- start:| 05-Oct-2009 cklee Fixed bug: 8947080
552 ELSIF (P_IMPACT_BUDGET_TYPE_CODE = 'DIRECT_COST_ENTRY') THEN
553 -- validation for supplier/direct cost region:
554 -- 1. if ci_type_id exists in pa_budget_versions then
555 -- -- user cannot remove any options
556 -- -- user cannot add options
557 -- 2. if ci_type_id not exists in pa_budget_versions then
558 -- -- user free to make changes
559 OPEN new_fin_impacts_csr(p_ci_type_id);
560 FETCH new_fin_impacts_csr INTO l_new_fin_imp_dummy;
561 IF new_fin_impacts_csr%FOUND THEN
562 l_new_fin_imp_exists := TRUE;
563 END IF;
564 CLOSE new_fin_impacts_csr;
565
566 IF l_new_fin_imp_exists THEN
567
568 -- Revenue modification:
569 IF (budget_options_rec.DIR_REG_REV_COL_FLAG = 'Y' AND P_DIR_REG_REV_COL_FLAG = 'N') THEN
570
571 FOR imp_code_rec IN imp_code_csr (p_ci_type_id, 'FINPLAN_REVENUE')
572 LOOP
573 PA_UTILS.Add_Message( p_app_short_name => 'PA'
574 ,p_msg_name => 'PA_CI_IMPACT_TU_IN_USE'
575 ,p_token1 => 'IMPACT'
576 ,p_value1 => imp_code_rec.impact_type_name);
577
578 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
579 END LOOP;
580
581 ELSIF (budget_options_rec.DIR_REG_REV_COL_FLAG = 'N' AND P_DIR_REG_REV_COL_FLAG = 'Y') THEN
582 PA_UTILS.Add_Message( p_app_short_name => 'PA'
583 ,p_msg_name => 'PA_CI_NO_REV_IMPACT');
584
585 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
586 END IF;
587
588 -- Supplier cost region modification
589 IF (budget_options_rec.SUPP_COST_REG_FLAG = 'Y' AND P_SUPP_COST_REG_FLAG = 'N') THEN
590 FOR imp_code_rec IN imp_code_csr (p_ci_type_id, 'FINPLAN_SUPPLIER_COST')
591 LOOP
592 PA_UTILS.Add_Message( p_app_short_name => 'PA'
593 ,p_msg_name => 'PA_CI_IMPACT_TU_IN_USE'
594 ,p_token1 => 'IMPACT'
595 ,p_value1 => imp_code_rec.impact_type_name);
596
597 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
598 END LOOP;
599 ELSIF (budget_options_rec.SUPP_COST_REG_FLAG = 'N' AND P_SUPP_COST_REG_FLAG = 'Y') THEN
600 PA_UTILS.Add_Message( p_app_short_name => 'PA'
601 ,p_msg_name => 'PA_CI_NO_COST_IMPACT');
602
603 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
604 END IF;
605
606 -- Direct cost region modification
607 IF (budget_options_rec.DIR_COST_REG_FLAG = 'Y' AND P_DIR_COST_REG_FLAG = 'N') THEN
608 FOR imp_code_rec IN imp_code_csr (p_ci_type_id, 'FINPLAN_DIRECT_COST')
609 LOOP
610 PA_UTILS.Add_Message( p_app_short_name => 'PA'
611 ,p_msg_name => 'PA_CI_IMPACT_TU_IN_USE'
612 ,p_token1 => 'IMPACT'
613 ,p_value1 => imp_code_rec.impact_type_name);
614
615 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
616 END LOOP;
617 ELSIF (budget_options_rec.DIR_COST_REG_FLAG = 'N' AND P_DIR_COST_REG_FLAG = 'Y') THEN
618 PA_UTILS.Add_Message( p_app_short_name => 'PA'
619 ,p_msg_name => 'PA_CI_NO_COST_IMPACT');
620
621 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
622 END IF;
623
624 END IF;
625
626 /*
627 -- User try to remove revenue option
628 IF (budget_options_rec.DIR_REG_REV_COL_FLAG = 'Y' AND P_DIR_REG_REV_COL_FLAG = 'N') THEN
629 FOR val_impacts_rec IN val_impacts_csr (p_ci_type_id, 'FINPLAN_REVENUE')
630 LOOP
631
632 PA_UTILS.Add_Message( p_app_short_name => 'PA'
633 ,p_msg_name => 'PA_CI_IMPACT_TU_IN_USE'
634 ,p_token1 => 'IMPACT'
635 ,p_value1 => val_impacts_rec.impact_type_name);
636
637 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
638 END LOOP;
639
640 END IF;
641
642 -- User try to remove supplier cost region option
643 IF (budget_options_rec.SUPP_COST_REG_FLAG = 'Y' AND P_SUPP_COST_REG_FLAG = 'N') THEN
644 FOR val_supp_impacts_rec IN val_supp_impacts_csr (p_ci_type_id, 'FINPLAN_COST')
645 LOOP
646
647 PA_UTILS.Add_Message( p_app_short_name => 'PA'
648 ,p_msg_name => 'PA_CI_IMPACT_TU_IN_USE'
649 ,p_token1 => 'IMPACT'
650 ,p_value1 => val_supp_impacts_rec.impact_type_name);
651
652 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
653 END LOOP;
654
655 END IF;
656
657 -- User try to remove direct cost region option
658 IF (budget_options_rec.DIR_COST_REG_FLAG = 'Y' AND P_DIR_COST_REG_FLAG = 'N') THEN
659 FOR val_dir_impacts_rec IN val_dir_impacts_csr (p_ci_type_id, 'FINPLAN_COST')
660 LOOP
661
662 PA_UTILS.Add_Message( p_app_short_name => 'PA'
663 ,p_msg_name => 'PA_CI_IMPACT_TU_IN_USE'
664 ,p_token1 => 'IMPACT'
665 ,p_value1 => val_dir_impacts_rec.impact_type_name);
666
667 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
668 END LOOP;
669
670 END IF;
671
672
673 -- Original setting Dir=Y or/and Supp=Y
674 IF (P_DIR_COST_REG_FLAG = 'N' AND P_SUPP_COST_REG_FLAG = 'N') THEN
675
676 FOR val_impacts_rec IN val_impacts_csr (p_ci_type_id, 'FINPLAN_COST')
677 LOOP
678
679 PA_UTILS.Add_Message( p_app_short_name => 'PA'
680 ,p_msg_name => 'PA_CI_IMPACT_TU_IN_USE'
681 ,p_token1 => 'IMPACT'
682 ,p_value1 => val_impacts_rec.impact_type_name);
683
684 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
685 END LOOP;
686
687 END IF;
688 */
689 END IF;
690 -- end:| 05-Oct-2009 cklee Fixed bug: 8947080
691 -- user try to switch update budget method
692 -- 2.1 Edit plan amounts -> NA
693 -- 2.2 Edit plan amounts -> Direct Cost (budget and forecast)
694 ELSIF (budget_options_rec.IMPACT_BUDGET_TYPE_CODE = 'EDIT_PLANNED_AMOUNTS') THEN
695 IF (P_IMPACT_BUDGET_TYPE_CODE <> 'EDIT_PLANNED_AMOUNTS') THEN
696 OPEN validate_impacts_csr(p_ci_type_id);
697 FETCH validate_impacts_csr INTO l_impacts_dummy;
698 IF validate_impacts_csr%FOUND THEN
699 l_impacts_exists := TRUE;
700 END IF;
701 CLOSE validate_impacts_csr;
702
703 IF l_impacts_exists THEN
704
705 OPEN budget_method(budget_options_rec.IMPACT_BUDGET_TYPE_CODE);
706 FETCH budget_method INTO l_update_budget_method;
707 CLOSE budget_method;
708
709 PA_UTILS.Add_Message( p_app_short_name => 'PA'
710 ,p_msg_name => 'PA_CI_IMPACT_TU_IN_USE'
711 ,p_token1 => 'IMPACT'
712 ,p_value1 => l_update_budget_method);
713
714 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
715
716 END IF;
717 --END IF;
718 -- user try to switch update budget method
719 -- no impact for the following scenario
720 -- 3.1 NA -> Edit plan amounts
721 -- 3.2 NA -> Direct Cost (budget and forecast)
722 -- | 05-Oct-2009 cklee Fixed bug: 8947080
723 -- start:| 05-Oct-2009 cklee Fixed bug: 8947080
724 ELSIF (P_IMPACT_BUDGET_TYPE_CODE = 'EDIT_PLANNED_AMOUNTS') THEN
725 -- User try to remove revenue option
726 IF (budget_options_rec.REV_COL_FLAG = 'Y' AND P_REV_COL_FLAG = 'N') THEN
727
728 FOR val_impacts_rec IN val_impacts_csr (p_ci_type_id, 'FINPLAN_REVENUE')
729 LOOP
730
731 PA_UTILS.Add_Message( p_app_short_name => 'PA'
732 ,p_msg_name => 'PA_CI_IMPACT_TU_IN_USE'
733 ,p_token1 => 'IMPACT'
734 ,p_value1 => val_impacts_rec.impact_type_name);
735
736 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
737 END LOOP;
738
739 END IF;
740 IF (budget_options_rec.COST_COL_FLAG = 'Y' AND P_COST_COL_FLAG = 'N') THEN
741
742 FOR val_impacts_rec IN val_impacts_csr (p_ci_type_id, 'FINPLAN_COST')
743 LOOP
744
745 PA_UTILS.Add_Message( p_app_short_name => 'PA'
746 ,p_msg_name => 'PA_CI_IMPACT_TU_IN_USE'
747 ,p_token1 => 'IMPACT'
748 ,p_value1 => val_impacts_rec.impact_type_name);
749
750 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
751 END LOOP;
752
753 END IF;
754 -- end:| 05-Oct-2009 cklee Fixed bug: 8947080
755
756 END IF;
757 END IF;
758
759 END LOOP;
760
761
762 -- end: cklee 09/30/09 bug: 8974414
763
764
765 -- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
766 -- if the current change request approval type code is EXTERNAL_APPROVAL and control item's pco status
767 -- has been recorded, then user is not allowed to change the approval type.
768 OPEN c_pco_st_exists;
769 FETCH c_pco_st_exists INTO l_pco_st_exists;
770 if c_pco_st_exists%FOUND then
771 if (p_ci_type_class_code = 'CHANGE_REQUEST' and
772 p_approval_type_code <> 'EXTERNAL_APPROVAL') then
773
774 x_return_status := 'E';
775 fnd_message.set_name('PA', 'PA_CI_PCO_APP_METHOD_IN_USE');
776 fnd_msg_pub.add();
777 end if;
778 end if ;
779 close c_pco_st_exists;
780 -- end: 26-Jun-2009 cklee Modified for the Bug# 8633676
781
782 -- Validate the name and short name uniqueness
783 IF (pa_ci_types_util.check_ci_type_name_exists(p_name, p_short_name, p_ci_type_id)) THEN
784 x_return_status := 'E';
785 fnd_message.set_name('PA','PA_CI_TYPE_NAME_NOT_UNIQUE');
786 fnd_msg_pub.add();
787 END IF;
788
789
790 -- Validate the record version number
791 BEGIN
792 SELECT classification_category,
793 reason_category,
794 resolution_category
795 INTO l_classification_category,
796 l_reason_category,
797 l_resolution_category
798 FROM pa_ci_types_vl
799 WHERE ci_type_id = p_ci_type_id
800 AND record_version_number = p_record_version_number;
801
802 p_record_version_number := p_record_version_number+1;
803 EXCEPTION
804 WHEN NO_DATA_FOUND THEN
805 x_return_status := 'E';
806 fnd_message.set_name('PA','PA_XC_RECORD_CHANGED');
807 fnd_msg_pub.add();
808 END;
809
810 -- Cannot change the category if there is a control item using the class code
811 IF x_return_status = 'S' AND
812 l_classification_category <> p_classification_category THEN
813 BEGIN
814 SELECT 'X' INTO l_temp
815 FROM PA_CONTROL_ITEMS
816 WHERE ci_type_id = p_ci_type_id
817 AND classification_code_id IS NOT NULL
818 AND ROWNUM < 2;
819
820 x_return_status := 'E';
821 fnd_message.set_name('PA','PA_CI_TYPE_CLASS_CAT_IN_USE');
822 fnd_msg_pub.add();
823 EXCEPTION
824 WHEN NO_DATA_FOUND THEN
825 NULL;
826 END;
827 END IF;
828
829 IF x_return_status = 'S' AND
830 l_reason_category <> p_reason_category THEN
831 BEGIN
832 SELECT 'X' INTO l_temp
833 FROM PA_CONTROL_ITEMS
834 WHERE ci_type_id = p_ci_type_id
835 AND reason_code_id IS NOT NULL
836 AND ROWNUM < 2;
837
838 x_return_status := 'E';
839 fnd_message.set_name('PA','PA_CI_TYPE_REASON_CAT_IN_USE');
840 fnd_msg_pub.add();
841 EXCEPTION
842 WHEN NO_DATA_FOUND THEN
843 NULL;
844 END;
845 END IF;
846
847 IF x_return_status = 'S' AND
848 l_resolution_category <> p_resolution_category THEN
849 BEGIN
850 SELECT 'X' INTO l_temp
851 FROM PA_CONTROL_ITEMS
852 WHERE ci_type_id = p_ci_type_id
853 AND resolution_code_id IS NOT NULL
854 AND ROWNUM < 2;
855
856 x_return_status := 'E';
857 fnd_message.set_name('PA','PA_CI_TYPE_RESO_CAT_IN_USE');
858 fnd_msg_pub.add();
859 EXCEPTION
860 WHEN NO_DATA_FOUND THEN
861 NULL;
862 END;
863 END IF;
864
865 -- Resolution Category is required when Resolution Required Flag is checked
866 IF p_resolution_required_flag = 'Y' AND
867 p_resolution_category IS NULL THEN
868 x_return_status := 'E';
869 fnd_message.set_name('PA', 'PA_CI_TYPE_RESO_CAT_MISSING');
870 fnd_msg_pub.add();
871 END IF;
872
873 -- Cannot change from Manual to Automatic numbering if an item exists
874 IF p_auto_number_flag = 'Y' THEN
875 BEGIN
876 SELECT 'X'
877 INTO l_temp
878 FROM pa_ci_types_b cit,
879 pa_control_items ci
880 WHERE cit.ci_type_id = p_ci_type_id
881 AND cit.auto_number_flag <> 'Y'
882 AND ci.ci_type_id = p_ci_type_id
883 AND ROWNUM=1;
884
885 x_return_status := 'E';
886 fnd_message.set_name('PA', 'PA_CI_TYPE_NO_SWITCH_NUM');
887 fnd_msg_pub.add();
888 EXCEPTION
889 WHEN OTHERS THEN
890 NULL;
891 END;
892 END IF;
893
894 -- End Date Active must be later than Start Date Active
895 IF p_start_date_active > p_end_date_active THEN
896 x_return_status := 'E';
897 fnd_message.set_name('PA', 'PA_CI_TYPE_INVALID_DATES');
898 fnd_msg_pub.add();
899 END IF;
900
901 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
902 pa_ci_types_pkg.update_row(
903 x_ci_type_id => p_ci_type_id,
904 x_ci_type_class_code => p_ci_type_class_code,
905 x_auto_number_flag => p_auto_number_flag,
906 x_resolution_required_flag => p_resolution_required_flag,
907 x_approval_required_flag => l_approval_required_flag,----28-oct-2009 cklee fxied bug: 9063248p_approval_required_flag,
908 x_source_attrs_enabled_flag => p_source_attrs_enabled_flag,
909 x_allow_all_usage_flag => p_allow_all_usage_flag,
910 x_record_version_number => p_record_version_number,
911 x_start_date_active => p_start_date_active,
912 x_end_date_active => p_end_date_active,
913 x_classification_category => p_classification_category,
914 x_reason_category => p_reason_category,
915 x_resolution_category => p_resolution_category,
916 x_attribute_category => p_attribute_category,
917 x_attribute1 => p_attribute1,
918 x_attribute2 => p_attribute2,
919 x_attribute3 => p_attribute3,
920 x_attribute4 => p_attribute4,
921 x_attribute5 => p_attribute5,
922 x_attribute6 => p_attribute6,
923 x_attribute7 => p_attribute7,
924 x_attribute8 => p_attribute8,
925 x_attribute9 => p_attribute9,
926 x_attribute10 => p_attribute10,
927 x_attribute11 => p_attribute11,
928 x_attribute12 => p_attribute12,
929 x_attribute13 => p_attribute13,
930 x_attribute14 => p_attribute14,
931 x_attribute15 => p_attribute15,
932 x_name => p_name,
933 x_short_name => p_short_name,
934 x_description => p_description,
935 x_last_update_date => p_last_update_date,
936 x_last_updated_by => p_last_updated_by,
937 x_last_update_login => p_last_update_login,
938 --start:| 16-FEB-2009 cklee R12.1.2 setup ehancement
939 X_APPROVAL_TYPE_CODE => P_APPROVAL_TYPE_CODE,
940 X_SUBCONTRACTOR_REPORTING_FLAG => P_SUBCONTRACTOR_REPORTING_FLAG,
941 X_PREFIX_AUTO_NUMBER => P_PREFIX_AUTO_NUMBER,
942 --end:| 16-FEB-2009 cklee R12.1.2 setup ehancement
943 --|start 29-APR-2009 cklee R12.1.2 setup ehancement v2
944 X_IMPACT_BUDGET_TYPE_CODE => P_IMPACT_BUDGET_TYPE_CODE,
945 X_COST_COL_FLAG => P_COST_COL_FLAG,
946 X_REV_COL_FLAG => P_REV_COL_FLAG,
947 X_DIR_COST_REG_FLAG => P_DIR_COST_REG_FLAG,
948 X_SUPP_COST_REG_FLAG => P_SUPP_COST_REG_FLAG,
949 X_DIR_REG_REV_COL_FLAG => P_DIR_REG_REV_COL_FLAG);
950 --|end 29-APR-2009 cklee R12.1.2 setup ehancement v2
951
952
953 pa_obj_status_lists_pkg.UPDATE_ROW (
954 X_OBJ_STATUS_LIST_ID => p_obj_status_list_id,
955 X_OBJECT_TYPE => 'PA_CI_TYPES',
956 X_OBJECT_ID => p_ci_type_id,
957 X_STATUS_LIST_ID => p_status_list_id,
958 X_STATUS_TYPE => 'CONTROL_ITEM',
959 X_LAST_UPDATE_DATE => p_last_update_date,
960 X_LAST_UPDATED_BY => p_last_updated_by,
961 X_LAST_UPDATE_LOGIN => p_last_update_login
962 );
963 END IF;
964
965 IF p_commit = fnd_api.g_true THEN
966 IF x_return_status = 'S' THEN
967 COMMIT;
968 ELSE
969 ROLLBACK TO update_ci_type;
970 END IF;
971 END IF;
972
973 fnd_msg_pub.count_and_get(p_count => x_msg_count,
974 p_data => x_msg_data);
975
976 pa_debug.reset_err_stack;
977
978 EXCEPTION
979 WHEN G_EXCEPTION_ERROR THEN
980
981 IF p_commit = 'T' THEN
982 ROLLBACK TO update_ci_type;
983 END IF;
984 x_return_status := FND_API.G_RET_STS_ERROR;
985 FND_MSG_PUB.Count_And_Get
986 (p_count => x_msg_count,
987 p_data => x_msg_data);
988
989
990 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
991
992 IF p_commit = 'T' THEN
993 ROLLBACK TO update_ci_type;
994 END IF;
995 x_return_status := FND_API.G_RET_STS_ERROR;
996 FND_MSG_PUB.Count_And_Get
997 (p_count => x_msg_count,
998 p_data => x_msg_data);
999
1000 WHEN OTHERS THEN
1001 IF p_commit = fnd_api.g_true THEN
1002 ROLLBACK TO update_ci_type;
1003 END IF;
1004
1005 x_return_status := 'U';
1006 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_TYPES_PVT',
1007 p_procedure_name => 'UPDATE_CI_TYPE',
1008 p_error_text => SUBSTRB(SQLERRM,1,240));
1009
1010 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1011 p_data => x_msg_data);
1012 END update_ci_type;
1013
1014
1015 PROCEDURE delete_ci_type (
1016 p_api_version IN NUMBER := 1.0,
1017 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
1018 p_commit IN VARCHAR2 := FND_API.g_false,
1019 p_validate_only IN VARCHAR2 := FND_API.g_true,
1020 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
1021 p_ci_type_id IN NUMBER,
1022 p_record_version_number IN NUMBER,
1023 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1024 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1025 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1026 p_obj_status_list_id IN NUMBER
1027 )
1028 IS
1029 CURSOR c_ci_type_usage IS
1030 SELECT *
1031 FROM pa_ci_type_usage
1032 WHERE ci_type_id = p_ci_type_id;
1033
1034 CURSOR c_impact_type_usage IS
1035 SELECT *
1036 FROM pa_ci_impact_type_usage
1037 WHERE ci_type_id = p_ci_type_id;
1038
1039 CURSOR c_obj_status_lists IS
1040 SELECT *
1041 FROM pa_obj_status_lists
1042 WHERE obj_status_list_id = p_obj_status_list_id;
1043
1044 l_temp VARCHAR2(1);
1045 BEGIN
1046 pa_debug.set_err_stack ('PA_CI_TYPES_PVT.DELETE_CI_TYPE');
1047
1048 IF p_commit = FND_API.G_TRUE THEN
1049 SAVEPOINT delete_ci_type;
1050 END IF;
1051
1052 IF p_init_msg_list = FND_API.G_TRUE THEN
1053 fnd_msg_pub.initialize;
1054 END IF;
1055
1056 x_return_status := 'S';
1057 x_msg_count := 0;
1058 x_msg_data := '';
1059
1060 -- Validate the record version number
1061 BEGIN
1062 SELECT 'X' INTO l_temp
1063 FROM pa_ci_types_vl
1064 WHERE ci_type_id = p_ci_type_id
1065 AND record_version_number = p_record_version_number;
1066 EXCEPTION
1067 WHEN NO_DATA_FOUND THEN
1068 x_return_status := 'E';
1069 fnd_message.set_name('PA','PA_XC_RECORD_CHANGED');
1070 fnd_msg_pub.add();
1071 END;
1072
1073 -- Cannot delete CI Type if CI exists
1074 BEGIN
1075 SELECT 'X' INTO l_temp
1076 FROM pa_control_items
1077 WHERE ci_type_id = p_ci_type_id
1078 AND ROWNUM=1;
1079
1080 x_return_status := 'E';
1081 fnd_message.set_name('PA','PA_CI_TYPE_NO_DELETE_IN_USE');
1082 fnd_msg_pub.add();
1083 EXCEPTION
1084 WHEN OTHERS THEN
1085 NULL;
1086 END;
1087
1088 /* Changes for the bug# 3941304 starts here */
1089 -- Cannot Delete CI Type if it has some selected statuses
1090 -- for financial imapct implementation/inclusion
1091 if Pa_Fp_Control_Items_Utils.validate_fp_ci_type_delete( p_ci_type_id) <> 'Y' then
1092 x_return_status := 'E';
1093 fnd_message.set_name('PA','FP_CI_TYPE_DEL_NOT_ALLOWED');
1094 fnd_msg_pub.add();
1095 end if;
1096 /* Changes for the bug# 3941304 ends here */
1097
1098 --Deleting the associated distribution list
1099 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
1100 pa_object_dist_lists_pvt.delete_assoc_dist_lists (
1101 p_validate_only => p_validate_only,
1102 p_object_type => 'PA_CI_TYPES',
1103 p_object_id => p_ci_type_id,
1104 x_return_status => x_return_status,
1105 x_msg_count => x_msg_count,
1106 x_msg_data => x_msg_data);
1107 END IF;
1108
1109 --Deleting the CI type usage
1110 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
1111 FOR rec IN c_ci_type_usage LOOP
1112 pa_ci_type_usage_pvt.delete_ci_type_usage (
1113 p_validate_only => p_validate_only,
1114 p_ci_type_usage_id => rec.ci_type_usage_id,
1115 p_project_type_id => rec.project_type_id,
1116 p_ci_type_id => p_ci_type_id,
1117 x_return_status => x_return_status,
1118 x_msg_count => x_msg_count,
1119 x_msg_data => x_msg_data);
1120
1121 EXIT WHEN x_return_status<>'S';
1122 END LOOP;
1123 END IF;
1124
1125 --Deleting the impact type usage
1126 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
1127 FOR rec IN c_impact_type_usage LOOP
1128 pa_ci_impact_type_usage_pvt.delete_ci_impact_type_usage (
1129 p_validate_only => p_validate_only,
1130 p_ci_impact_type_usage_id => rec.ci_impact_type_usage_id,
1131 p_impact_type_code => rec.impact_type_code,
1132 p_ci_type_class_code => NULL,
1133 p_ci_type_id => p_ci_type_id,
1134 x_return_status => x_return_status,
1135 x_msg_count => x_msg_count,
1136 x_msg_data => x_msg_data);
1137
1138 EXIT WHEN x_return_status<>'S';
1139 END LOOP;
1140 END IF;
1141
1142 --Deleting the CI type itself
1143 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
1144 pa_ci_types_pkg.delete_row(
1145 x_ci_type_id => p_ci_type_id);
1146
1147 --Deleting the association from pa_obj_status_lists
1148 pa_obj_status_lists_pkg.delete_row(
1149 x_obj_status_list_id => p_obj_status_list_id);
1150 END IF;
1151
1152 IF p_commit = fnd_api.g_true THEN
1153 IF x_return_status = 'S' THEN
1154 COMMIT;
1155 ELSE
1156 ROLLBACK TO delete_ci_type;
1157 END IF;
1158 END IF;
1159
1160 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1161 p_data => x_msg_data);
1162
1163 pa_debug.reset_err_stack;
1164
1165 EXCEPTION
1166 WHEN G_EXCEPTION_ERROR THEN
1167
1168 IF p_commit = 'T' THEN
1169 ROLLBACK TO delete_ci_type;
1170 END IF;
1171 x_return_status := FND_API.G_RET_STS_ERROR;
1172 FND_MSG_PUB.Count_And_Get
1173 (p_count => x_msg_count,
1174 p_data => x_msg_data);
1175
1176
1177 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1178
1179 IF p_commit = 'T' THEN
1180 ROLLBACK TO delete_ci_type;
1181 END IF;
1182 x_return_status := FND_API.G_RET_STS_ERROR;
1183 FND_MSG_PUB.Count_And_Get
1184 (p_count => x_msg_count,
1185 p_data => x_msg_data);
1186
1187 WHEN OTHERS THEN
1188 IF p_commit = fnd_api.g_true THEN
1189 ROLLBACK TO delete_ci_type;
1190 END IF;
1191
1192 x_return_status := 'U';
1193 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_TYPES_PVT',
1194 p_procedure_name => 'DELETE_CI_TYPE',
1195 p_error_text => SUBSTRB(SQLERRM,1,240));
1196
1197 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1198 p_data => x_msg_data);
1199 END delete_ci_type;
1200
1201 END pa_ci_types_pvt;