[Home] [Help]
PACKAGE BODY: APPS.PA_CI_IMPACT_TYPE_USAGE_PVT
Source
1 PACKAGE BODY PA_CI_IMPACT_TYPE_USAGE_pvt AS
2 /* $Header: PACIIMVB.pls 120.0.12010000.4 2009/10/06 21:58:32 cklee ship $ */
3
4 PROCEDURE create_ci_impact_type_usage (
5 p_api_version IN NUMBER := 1.0,
6 p_init_msg_list IN VARCHAR2 := 'T',
7 p_commit IN VARCHAR2 := 'F',
8 p_validate_only IN VARCHAR2 := 'T',
9 p_max_msg_count IN NUMBER := null,
10
11 p_impact_type_code IN VARCHAR2 := null,
12 p_ci_type_class_code IN VARCHAR2 := null,
13 p_CI_TYPE_ID in NUMBER := null,
14
15 p_created_by IN NUMBER DEFAULT fnd_global.user_id,
16 p_creation_date IN DATE DEFAULT SYSDATE,
17 p_last_update_login IN NUMBER DEFAULT fnd_global.login_id,
18 --start:| 16-FEB-2009 cklee R12.1.2 setup ehancement
19 P_IMPACT_TYPE_CODE_ORDER IN NUMBER default null,
20 --end:| 16-FEB-2009 cklee R12.1.2 setup ehancement
21
22 x_ci_impact_type_usage_id OUT NOCOPY NUMBER,
23 x_return_status OUT NOCOPY VARCHAR2,
24 x_msg_count OUT NOCOPY NUMBER,
25 x_msg_data OUT NOCOPY VARCHAR2
26 )
27 IS
28 l_rowid VARCHAR2(30);
29 CURSOR check_exists is
30 SELECT 'Y' FROM dual
31 WHERE exists (SELECT ci_impact_type_usage_id FROM
32 pa_ci_impact_type_usage
33 -- WHERE ci_type_class_code = p_ci_type_class_code
34 WHERE nvl(ci_type_class_code, 1) = nvl(p_ci_type_class_code, 1) -- cklee
35 AND ci_type_id = p_ci_type_id
36 AND impact_type_code = p_impact_type_code);
37
38 l_dummy VARCHAR2(1);
39
40 BEGIN
41 pa_debug.set_err_stack ('PA_CI_IMPACT_TYPE_USAGE_PVT.CREATE_CI_IMPACT_TYPE_USAGE');
42
43 IF p_commit = 'T' THEN
44 SAVEPOINT create_ci_impact_type_usage;
45 END IF;
46
47 IF p_init_msg_list = 'T' THEN
48 fnd_msg_pub.initialize;
49 END IF;
50
51 x_return_status := 'S';
52 x_msg_count := 0;
53 x_msg_data := '';
54
55 OPEN check_exists;
56 FETCH check_exists INTO l_dummy;
57 IF check_exists%found THEN
58 -- record already exists
59 PA_UTILS.Add_Message( p_app_short_name => 'PA'
60 ,p_msg_name => 'PA_CI_IMPACT_TU_EXIST');
61
62 x_return_status := FND_API.G_RET_STS_ERROR;
63
64 END IF;
65 CLOSE check_exists;
66
67 IF (p_validate_only <> 'T' AND x_return_status = 'S') THEN
68 PA_CI_IMPACT_TYPE_USAGE_pkg.insert_row(
69 x_rowid => l_rowid,
70 x_ci_impact_type_usage_id => x_ci_impact_type_usage_id,
71 x_impact_type_code => p_impact_type_code,
72 x_ci_type_class_code => p_ci_type_class_code,
73 x_ci_type_id => p_ci_type_id,
74 x_creation_date => p_creation_date,
75 x_created_by => p_created_by,
76 x_last_update_date => p_creation_date,
77 x_last_updated_by => p_created_by,
78 x_last_update_login => p_last_update_login,
79 --start:| 16-FEB-2009 cklee R12.1.2 setup ehancement
80 X_IMPACT_TYPE_CODE_ORDER => P_IMPACT_TYPE_CODE_ORDER);
81 --end:| 16-FEB-2009 cklee R12.1.2 setup ehancement
82
83 END IF;
84
85 IF p_commit = 'T' THEN
86 IF x_return_status = 'S' THEN
87 COMMIT;
88 ELSE
89 ROLLBACK TO create_ci_impact_type_usage;
90 END IF;
91 END IF;
92
93 fnd_msg_pub.count_and_get(p_count => x_msg_count,
94 p_data => x_msg_data);
95
96 pa_debug.reset_err_stack;
97
98 EXCEPTION
99 WHEN OTHERS THEN
100 IF p_commit = 'T' THEN
101 ROLLBACK TO create_ci_impact_type_usage;
102 END IF;
103
104 x_return_status := 'U';
105 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_IMPACT_TYPE_USAGE_PVT',
106 p_procedure_name => 'CREATE_CI_IMPACT_TYPE_USAGE',
107 p_error_text => SUBSTRB(SQLERRM,1,240));
108
109 fnd_msg_pub.count_and_get(p_count => x_msg_count,
110 p_data => x_msg_data);
111 END create_ci_impact_type_usage;
112
113 --start:| 16-FEB-2009 cklee R12.1.2 setup ehancement
114
115 PROCEDURE update_ci_impact_type_usage (
116 p_api_version IN NUMBER := 1.0,
117 p_init_msg_list IN VARCHAR2 := 'T',
118 p_commit IN VARCHAR2 := 'F',
119 p_validate_only IN VARCHAR2 := 'T',
120 p_max_msg_count IN NUMBER := null,
121
122 P_IMPACT_TYPE_CODE_ORDER IN NUMBER,
123 p_ci_impact_type_usage_id IN NUMBER,
124
125 x_return_status OUT NOCOPY VARCHAR2,
126 x_msg_count OUT NOCOPY NUMBER,
127 x_msg_data OUT NOCOPY VARCHAR2
128 )
129 is
130
131 BEGIN
132 pa_debug.set_err_stack ('PA_CI_IMPACT_TYPE_USAGE_PVT.UPDATE_CI_IMPACT_TYPE_USAGE');
133
134 IF p_commit = 'T' THEN
135 SAVEPOINT update_ci_impact_type_usage;
136 END IF;
137
138 IF p_init_msg_list = 'T' THEN
139 fnd_msg_pub.initialize;
140 END IF;
141
142 x_return_status := 'S';
143 x_msg_count := 0;
144 x_msg_data := '';
145 IF (p_validate_only <> 'T' AND x_return_status = 'S') THEN
146 PA_CI_IMPACT_TYPE_USAGE_pkg.update_row(
147 x_ci_impact_type_usage_id => p_ci_impact_type_usage_id,
148 X_IMPACT_TYPE_CODE_ORDER => P_IMPACT_TYPE_CODE_ORDER);
149
150 END IF;
151
152 IF p_commit = 'T' THEN
153 IF x_return_status = 'S' THEN
154 COMMIT;
155 ELSE
156 ROLLBACK TO update_ci_impact_type_usage;
157 END IF;
158 END IF;
159
160 fnd_msg_pub.count_and_get(p_count => x_msg_count,
161 p_data => x_msg_data);
162
163 pa_debug.reset_err_stack;
164
165 EXCEPTION
166 WHEN OTHERS THEN
167 IF p_commit = 'T' THEN
168 ROLLBACK TO update_ci_impact_type_usage;
169 END IF;
170
171 x_return_status := 'U';
172 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_IMPACT_TYPE_USAGE_PVT',
173 p_procedure_name => 'UPDATE_CI_IMPACT_TYPE_USAGE',
174 p_error_text => SUBSTRB(SQLERRM,1,240));
175
176 fnd_msg_pub.count_and_get(p_count => x_msg_count,
177 p_data => x_msg_data);
178
179 end update_ci_impact_type_usage;
180 --end:| 16-FEB-2009 cklee R12.1.2 setup ehancement
181
182
183 PROCEDURE delete_ci_impact_type_usage (
184 p_api_version IN NUMBER := 1.0,
185 p_init_msg_list IN VARCHAR2 := 'T',
186 p_commit IN VARCHAR2 := 'F',
187 p_validate_only IN VARCHAR2 := 'T',
188 p_max_msg_count IN NUMBER := null,
189
190 p_ci_impact_type_usage_id IN NUMBER := null,
191 p_impact_type_code IN VARCHAR2 := null,
192 p_ci_type_class_code IN VARCHAR2 := null,
193 p_ci_type_id IN NUMBER := null,
194 --start:| 16-FEB-2009 cklee R12.1.2 setup ehancement
195 P_IMPACT_TYPE_CODE_ORDER IN NUMBER default null,
196 --end:| 16-FEB-2009 cklee R12.1.2 setup ehancement
197 x_return_status OUT NOCOPY VARCHAR2,
198 x_msg_count OUT NOCOPY NUMBER,
199 x_msg_data OUT NOCOPY VARCHAR2
200 )
201 IS
202 l_temp VARCHAR2(1);
203 CURSOR check_exists is
204 SELECT 'Y' FROM dual
205 WHERE exists (SELECT ci_impact_type_usage_id FROM
206 pa_ci_impact_type_usage
207 WHERE ci_impact_type_usage_id = p_ci_impact_type_usage_id
208 );
209 --start:| 16-FEB-2009 cklee R12.1.2 setup ehancement
210 CURSOR c_impact_type is
211 SELECT MEANING FROM pa_lookups
212 WHERE LOOKUP_TYPE = 'PA_CI_IMPACT_TYPES'
213 AND LOOKUP_CODE = p_impact_type_code;
214
215 l_impact_type_meaning pa_lookups.MEANING%type;
216 --end:| 16-FEB-2009 cklee R12.1.2 setup ehancement
217
218
219 l_dummy VARCHAR2(1);
220 BEGIN
221 pa_debug.set_err_stack ('PA_CI_IMPACT_TYPE_USAGE_PVT.DELETE_CI_IMPACT_TYPE_USAGE');
222
223 IF p_commit = 'T' THEN
224 SAVEPOINT delete_ci_impact_type_usage;
225 END IF;
226
227 IF p_init_msg_list = 'T' THEN
228 fnd_msg_pub.initialize;
229 END IF;
230
231 x_return_status := 'S';
232 x_msg_count := 0;
233 x_msg_data := '';
234
235 OPEN check_exists;
236 FETCH check_exists INTO l_dummy;
237 IF check_exists%notfound THEN
238 -- record already exists
239 PA_UTILS.Add_Message( p_app_short_name => 'PA'
240 ,p_msg_name => 'PA_CI_IMPACT_TU_NO_EXIST');
241
242 x_return_status := FND_API.G_RET_STS_ERROR;
243
244 END IF;
245 CLOSE check_exists;
246
247 -- bug 2606472
248 -- if the impact is in use in a control item, the user cannot remove the
249 -- impact type usage
250
251 IF p_ci_type_id IS NOT NULL
252 AND p_impact_type_code IS NOT NULL
253 THEN
254
255 l_dummy := pa_ci_impact_type_usage_pub.delete_impact_type_usage_ok
256 (p_impact_type_code, p_ci_type_id);
257
258 IF l_dummy = 'N' THEN
259
260 --start:| 16-FEB-2009 cklee R12.1.2 setup ehancement
261 OPEN c_impact_type;
262 FETCH c_impact_type INTO l_impact_type_meaning;
263 CLOSE c_impact_type;
264
265 PA_UTILS.Add_Message( p_app_short_name => 'PA'
266 ,p_msg_name => 'PA_CI_IMPACT_TU_IN_USE'
267 ,p_token1 => 'IMPACT'
268 ,p_value1 => l_impact_type_meaning);
269
270 --end:| 16-FEB-2009 cklee R12.1.2 setup ehancement
271
272 x_return_status := FND_API.G_RET_STS_ERROR;
273
274 END IF;
275
276
277 END IF;
278
279
280 IF (p_validate_only <> 'T' AND x_return_status = 'S') THEN
281 PA_CI_IMPACT_TYPE_USAGE_pkg.delete_row(
282 x_ci_impact_type_usage_id => p_ci_impact_type_usage_id);
283 END IF;
284
285
286
287 IF p_commit = 'T' THEN
288 IF x_return_status = 'S' THEN
289 COMMIT;
290 ELSE
291 ROLLBACK TO delete_ci_impact_type_usage;
292 END IF;
293 END IF;
294
295 fnd_msg_pub.count_and_get(p_count => x_msg_count,
296 p_data => x_msg_data);
297
298 pa_debug.reset_err_stack;
299
300 EXCEPTION
301 WHEN OTHERS THEN
302 IF p_commit = 'T' THEN
303 ROLLBACK TO delete_ci_impact_type_usage;
304 END IF;
305
306 x_return_status := 'U';
307 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_IMPACT_TYPES_PVT',
308 p_procedure_name => 'DELETE_CI_IMPACT_TYPE_USAGE',
309 p_error_text => SUBSTRB(SQLERRM,1,240));
310
311 fnd_msg_pub.count_and_get(p_count => x_msg_count,
312 p_data => x_msg_data);
313 END delete_ci_impact_type_usage;
314
315 PROCEDURE apply_ci_impact_type_usage (
316 p_api_version IN NUMBER := 1.0,
317 p_init_msg_list IN VARCHAR2 := 'T',
318 p_commit IN VARCHAR2 := 'F',
319 p_validate_only IN VARCHAR2 := 'T',
320 p_max_msg_count IN NUMBER := null,
321
322 p_ui_mode IN VARCHAR2,
323 p_ci_class_code IN VARCHAR2,
324 p_ci_type_id IN NUMBER,
325 p_impact_tbl IN impact_tbl_type,
326
327 -- x_impact_tbl OUT NOCOPY impact_tbl_type,
328 x_return_status OUT NOCOPY VARCHAR2,
329 x_msg_count OUT NOCOPY NUMBER,
330 x_msg_data OUT NOCOPY VARCHAR2
331 )
332 IS
333
334 l_rowid VARCHAR2(30);
335 l_ci_impact_type_usage_id pa_ci_impact_type_usage.ci_impact_type_usage_id%type;
336
337 CURSOR validate_impacts_csr (p_ci_type_id pa_ci_types_b.CI_TYPE_ID%type) is
338 select pc.impact_type_code,
339 luk.meaning impact_type_name
340 from pa_control_items pci,
341 pa_ci_impacts pc,
342 pa_lookups luk
343 where pci.ci_type_id = p_ci_type_id
344 and pci.ci_id = pc.ci_id
345 and pc.impact_type_code = luk.lookup_code
346 and luk.lookup_type = 'PA_CI_IMPACT_TYPES'
347 and pc.impact_type_code <> 'FINPLAN' -- cklee 4/23/09
348 and not exists
349 (select 1
350 from pa_ci_impact_type_usage pcu
351 where pcu.impact_type_code = pc.impact_type_code
352 and pcu.ci_type_id = pci.ci_type_id);
353
354 validate_impacts_rec validate_impacts_csr%ROWTYPE;
355
356 l_cost_exists boolean := false;
357 -- l_rev_exists boolean := false;
358 l_direct_cost_exists boolean := false;
359 l_supplier_cost_exists boolean := false;
360 l_supplier_exists boolean := false;
361
362
363 CURSOR delete_impacts_csr (p_ci_type_id pa_ci_types_b.CI_TYPE_ID%type) is
364 select pu.ci_impact_type_usage_id
365 from pa_ci_impact_type_usage pu
366 where pu.ci_type_id = p_ci_type_id;
367
368 delete_impacts_rec delete_impacts_csr%ROWTYPE;
369 --cklee 5/1/09
370 CURSOR sync_budget_options_csr (p_ci_type_id pa_ci_types_b.CI_TYPE_ID%type) is
371 select ci.IMPACT_BUDGET_TYPE_CODE,
372 ci.COST_COL_FLAG,
373 ci.REV_COL_FLAG,
374 ci.DIR_COST_REG_FLAG,
375 ci.SUPP_COST_REG_FLAG,
376 ci.DIR_REG_REV_COL_FLAG
377 from pa_ci_types_b ci
378 where ci.ci_type_id = p_ci_type_id;
379
380 l_direct_cost_RN_exists boolean := false;
381 sync_cnt number := 0;
382 sync_budget_options_rec sync_budget_options_csr%ROWTYPE;
383
384 l_impact_tbl impact_tbl_type := p_impact_tbl;
385 --Cost, Revenue and Direct Cost are mutually exclusive. Please choose the correct impact sections.
386
387 l_finplan_cost_flag boolean := false; -- cklee 06/18/09
388
389 BEGIN
390
391
392 pa_debug.set_err_stack ('PA_CI_IMPACT_TYPE_USAGE_PVT.APPLY_CI_IMPACT_TYPE_USAGE');
393
394 IF p_commit = 'T' THEN
395 SAVEPOINT apply_ci_impact_type_usage;
396 END IF;
397
398 IF p_init_msg_list = 'T' THEN
399 fnd_msg_pub.initialize;
400 END IF;
401
402 x_return_status := 'S';
403 x_msg_count := 0;
404 x_msg_data := '';
405 /*
406 This is the simplest way to implement the shuttle bean activities. The main reason we put the
407 validation at last because we can by pass the TAPI to validate for each individual impact
408 deletion and we validate after all database transactions (delete and insert new lists) are done;
409 if however, any list is missing from the pa_ci_impacts, we can list all missing list in once
410 so that user can fix on UI in once.
411
412 Also notice that this list is expected to be very small. So no performance concern for the implementation
413 1. delete * from pa_ci_impact_type_usage where ci_type_id = p_ci_type;
414 2: Cost, Revenue and Direct Cost are mutually exclusive. Please choose the correct impact sections.
415 3. insert into pa_ci_impact_type_usage with passed in impact lists
416 4. validate if existing impact list is missing from the new impact list and raise error
417 */
418 --1. delete * from pa_ci_impact_type_usage where ci_type_id = p_ci_type;
419 -- 1.1 user may rearrange the selected list in update control item only
420 -- 1.2 Issue class doesn't have impact region, hence no action needed
421 IF (p_validate_only <> 'T' AND x_return_status = 'S') THEN
422
423 IF p_ui_mode = 'UPDATE' AND p_ci_class_code <> 'ISSUE' THEN
424
425 FOR delete_impacts_rec IN delete_impacts_csr (p_ci_type_id)
426 LOOP
427 PA_CI_IMPACT_TYPE_USAGE_pvt.delete_CI_IMPACT_TYPE_USAGE(
428 --p_api_version => p_api_version,
429 --p_init_msg_list => p_init_msg_list,
430 --p_commit => p_commit,
431 p_validate_only => p_validate_only,
432 --p_max_msg_count => p_max_msg_count,
433
434 p_ci_impact_type_usage_id => delete_impacts_rec.ci_impact_type_usage_id,
435 x_return_status => x_return_status,
436 x_msg_count => x_msg_count,
437 x_msg_data => x_msg_data);
438
439
440 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
441 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
442 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
443 RAISE G_EXCEPTION_ERROR;
444 END IF;
445 END LOOP;
446 --delete from pa_ci_impact_type_usage where ci_type_id = p_ci_type_id;
447 END IF;
448
449 --
450 -- 2: Cost, Revenue and Direct Cost are mutually exclusive. Please choose the correct impact sections.
451 --
452 /****
453 IF p_ui_mode <> 'VIEW' AND p_ci_class_code <> 'ISSUE' THEN
454
455 IF p_impact_tbl.COUNT > 0 THEN
456
457 FOR i in p_impact_tbl.FIRST..p_impact_tbl.LAST LOOP
458
459 IF p_impact_tbl(i).impact_type_code = 'FINPLAN_COST' THEN
460 l_cost_exists := TRUE;
461 ELSIF p_impact_tbl(i).impact_type_code = 'FINPLAN_DIRECT_COST' THEN
462 l_direct_cost_exists := TRUE;
463 ELSIF p_impact_tbl(i).impact_type_code = 'SUPPLIER' THEN
464 l_supplier_exists := TRUE;
465 ELSIF p_impact_tbl(i).impact_type_code = 'FINPLAN_SUPPLIER_COST' THEN
466 l_supplier_cost_exists := TRUE;
467 END IF;
468
469 END LOOP;
470
471 -- check error
472 IF l_supplier_cost_exists = TRUE AND l_supplier_exists = TRUE THEN
473
474 PA_UTILS.Add_Message( p_app_short_name => 'PA'
475 ,p_msg_name => 'PA_CI_IMPACT_SUPP_SUPP_CONFLICT');
476
477 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
478
479 ELSIF l_supplier_cost_exists = TRUE AND l_cost_exists = TRUE THEN
480
481 PA_UTILS.Add_Message( p_app_short_name => 'PA'
482 ,p_msg_name => 'PA_CI_IMPACT_SUPP_COST_CONFLICT');
483
484 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
485
486 ELSIF l_direct_cost_exists = TRUE AND l_supplier_exists = TRUE THEN
487
488 PA_UTILS.Add_Message( p_app_short_name => 'PA'
489 ,p_msg_name => 'PA_CI_IMPACT_DIR_SUPP_CONFLICT');
490
491 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
492
493 ELSIF l_direct_cost_exists = TRUE AND l_cost_exists = TRUE THEN
494
495 PA_UTILS.Add_Message( p_app_short_name => 'PA'
496 ,p_msg_name => 'PA_CI_IMPACT_DIR_COST_CONFLICT');
497
498 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
499
500 END IF;
501
502 END IF;
503 END IF;
504 ***/
505 -- pre-req: UI has already take care of the FINPLAN exists in p_impact_tbl
506 -- so no additional check needed here.
507 --
508 -- note: In order to sync with pa_ci_impacts budget cost and revenue implementation logic
509 -- modify the code logic as below:
510 -- 1. remove add additional new lookup codes for direct cost and supplier region
511 -- 2. in case if it's direct cost or/and supplier cost region user selected, add
512 -- 'FINPLAN_COST' as a lookup code for both -- only one lookup code
513 -- 3. in case if user choose to include revenue for direct or/and supplier cost
514 -- region, add 'FINPLAN_REVENUE' as a lookup code for both -- only one lookup code
515 --
516 IF p_ui_mode <> 'VIEW' AND p_ci_class_code <> 'ISSUE' THEN
517
518 IF l_impact_tbl.COUNT > 0 THEN
519 sync_cnt := l_impact_tbl.LAST + 1;
520 -- only one row will be executed...
521 FOR sync_budget_options_rec IN sync_budget_options_csr (p_ci_type_id)
522 LOOP
523 IF (sync_budget_options_rec.IMPACT_BUDGET_TYPE_CODE = 'DIRECT_COST_ENTRY') THEN
524 IF (sync_budget_options_rec.DIR_COST_REG_FLAG = 'Y') THEN
525 l_direct_cost_RN_exists := true;
526 --l_impact_tbl(sync_cnt).impact_type_code := 'FINPLAN_DIRECT_COST';
527 l_impact_tbl(sync_cnt).impact_type_code := 'FINPLAN_COST';
528 l_finplan_cost_flag := true;
529 sync_cnt := sync_cnt + 1;
530 END IF;
531 IF (sync_budget_options_rec.SUPP_COST_REG_FLAG = 'Y') THEN
532 l_direct_cost_RN_exists := true;
533 --l_impact_tbl(sync_cnt).impact_type_code := 'FINPLAN_SUPPLIER_COST';
534 IF NOT l_finplan_cost_flag THEN
535 l_impact_tbl(sync_cnt).impact_type_code := 'FINPLAN_COST';
536 sync_cnt := sync_cnt + 1;
537 END IF;
538 END IF;
539 IF (sync_budget_options_rec.DIR_REG_REV_COL_FLAG = 'Y' AND l_direct_cost_RN_exists) THEN
540 --l_impact_tbl(sync_cnt).impact_type_code := 'FINPLAN_DIRECT_INC_REV';
541 l_impact_tbl(sync_cnt).impact_type_code := 'FINPLAN_REVENUE';
542 sync_cnt := sync_cnt + 1;
543 END IF;
544
545 ELSIF (sync_budget_options_rec.IMPACT_BUDGET_TYPE_CODE = 'EDIT_PLANNED_AMOUNTS') THEN
546 IF (sync_budget_options_rec.COST_COL_FLAG = 'Y') THEN
547 l_impact_tbl(sync_cnt).impact_type_code := 'FINPLAN_COST';
548 sync_cnt := sync_cnt + 1;
549 END IF;
550 IF (sync_budget_options_rec.REV_COL_FLAG = 'Y') THEN
551 l_impact_tbl(sync_cnt).impact_type_code := 'FINPLAN_REVENUE';
552 --sync_cnt := sync_cnt + 1;
553 END IF;
554
555 END IF;
556
557 END LOOP;
558
559 END IF;
560 END IF;
561
562 --3. insert into pa_ci_impact_type_usage with passed in impact lists
563
564 -- 2.1 user may re-assemble the selected list in create/update control item
565 -- 2.2 Issue class doesn't have impact region, hence no action needed
566 IF p_ui_mode <> 'VIEW' AND p_ci_class_code <> 'ISSUE' THEN
567
568 IF l_impact_tbl.COUNT > 0 THEN
569
570 FOR i in l_impact_tbl.FIRST..l_impact_tbl.LAST LOOP
571
572
573 PA_CI_IMPACT_TYPE_USAGE_pvt.CREATE_CI_IMPACT_TYPE_USAGE(
574 --p_api_version => p_api_version,
575 --p_init_msg_list => p_init_msg_list,
576 --p_commit => p_commit,
577 p_validate_only => p_validate_only,
578 --p_max_msg_count => p_max_msg_count,
579
580 p_impact_type_code => l_impact_tbl(i).impact_type_code,
581 p_ci_type_class_code => NULL,
582 p_ci_type_id => p_ci_type_id,
583
584 p_created_by => fnd_global.user_id,
585 p_creation_date => SYSDATE,
586 p_last_update_login => fnd_global.login_id,
587 P_IMPACT_TYPE_CODE_ORDER => i,
588
589 x_ci_impact_type_usage_id => l_ci_impact_type_usage_id,
590 x_return_status => x_return_status,
591 x_msg_count => x_msg_count,
592 x_msg_data => x_msg_data);
593
594
595 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
596 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
597 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
598 RAISE G_EXCEPTION_ERROR;
599 END IF;
600 END LOOP;
601 END IF;
602 END IF;
603
604 --4 validate if existing impact list is missing from the new impact list and raise error
605 -- | 05-Oct-2009 cklee Fixed bug: 8947080
606 -- move below logic to pa_ci_types_pvt.update_ci_type API
607 /* IF p_ui_mode = 'UPDATE' AND p_ci_class_code <> 'ISSUE' THEN
608
609 FOR validate_impacts_rec IN validate_impacts_csr (p_ci_type_id)
610 LOOP
611
612 PA_UTILS.Add_Message( p_app_short_name => 'PA'
613 ,p_msg_name => 'PA_CI_IMPACT_TU_IN_USE'
614 ,p_token1 => 'IMPACT'
615 ,p_value1 => validate_impacts_rec.impact_type_name);
616
617 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
618 END LOOP;
619 END IF;
620 */
621
622 END IF; -- end of IF (p_validate_only <> 'T' AND x_return_status = 'S') THEN
623
624 IF p_commit = 'T' THEN
625 IF x_return_status = 'S' THEN
626 COMMIT;
627 ELSE
628 ROLLBACK TO apply_ci_impact_type_usage;
629 END IF;
630 END IF;
631
632 -- Get message count and if count is 1, get message info
633 fnd_msg_pub.count_and_get(p_count => x_msg_count,
634 p_data => x_msg_data);
635
636 pa_debug.reset_err_stack;
637
638 EXCEPTION
639 WHEN G_EXCEPTION_ERROR THEN
640
641 IF p_commit = 'T' THEN
642 ROLLBACK TO apply_ci_impact_type_usage;
643 END IF;
644 x_return_status := FND_API.G_RET_STS_ERROR;
645 FND_MSG_PUB.Count_And_Get
646 (p_count => x_msg_count,
647 p_data => x_msg_data);
648
649
650 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
651
652 IF p_commit = 'T' THEN
653 ROLLBACK TO apply_ci_impact_type_usage;
654 END IF;
655 x_return_status := FND_API.G_RET_STS_ERROR;
656 FND_MSG_PUB.Count_And_Get
657 (p_count => x_msg_count,
658 p_data => x_msg_data);
659
660
661 WHEN OTHERS THEN
662
663 IF p_commit = 'T' THEN
664 ROLLBACK TO apply_ci_impact_type_usage;
665 END IF;
666
667 x_return_status := 'U';
668 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_IMPACT_TYPE_USAGE_PVT',
669 p_procedure_name => 'APPLY_CI_IMPACT_TYPE_USAGE',
670 p_error_text => SUBSTRB(SQLERRM,1,240));
671
672 fnd_msg_pub.count_and_get(p_count => x_msg_count,
673 p_data => x_msg_data);
674
675
676
677 END apply_ci_impact_type_usage;
678
679 END PA_CI_IMPACT_TYPE_USAGE_pvt;