[Home] [Help]
PACKAGE BODY: APPS.PA_CONTROL_ITEMS_PVT
Source
1 PACKAGE BODY PA_CONTROL_ITEMS_PVT AS
2 --$Header: PACICIVB.pls 120.4 2007/02/08 11:46:15 sukhanna ship $
3
4
5 procedure ADD_CONTROL_ITEM(
6 p_api_version IN NUMBER := 1.0
7 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
8 ,p_commit IN VARCHAR2 := FND_API.g_false
9 ,p_validate_only IN VARCHAR2 := FND_API.g_true
10 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
11 ,p_ci_type_id IN NUMBER
12 ,p_summary IN VARCHAR2
13 ,p_status_code IN VARCHAR2
14 ,p_owner_id IN NUMBER
15 ,p_highlighted_flag IN VARCHAR2 := 'N'
16 ,p_progress_status_code IN VARCHAR2 := NULL
17 ,p_progress_as_of_date IN DATE := NULL
18 ,p_classification_code IN NUMBER
19 ,p_reason_code IN NUMBER
20 ,p_project_id IN NUMBER
21 ,p_last_modified_by_id IN NUMBER
22 ,p_object_type IN VARCHAR2 := NULL
23 ,p_object_id IN NUMBER := NULL
24 ,p_ci_number IN VARCHAR2 := NULL
25 ,p_date_required IN DATE := NULL
26 ,p_date_closed IN DATE := NULL
27 ,p_closed_by_id IN NUMBER := NULL
28 ,p_description IN VARCHAR2 := NULL
29 ,p_status_overview IN VARCHAR2 := NULL
30 ,p_resolution IN VARCHAR2 := NULL
31 ,p_resolution_code IN NUMBER := NULL
32 ,p_priority_code IN VARCHAR2 := NULL
33 ,p_effort_level_code IN VARCHAR2 := NULL
34 ,p_open_action_num IN NUMBER := NULL
35 ,p_price IN NUMBER := NULL
36 ,p_price_currency_code IN VARCHAR2 := NULL
37 ,p_source_type_code IN VARCHAR2 := NULL
38 ,p_source_comment IN VARCHAR2 := NULL
39 ,p_source_number IN VARCHAR2 := NULL
40 ,p_source_date_received IN DATE := NULL
41 ,p_source_organization IN VARCHAR2 := NULL
42 ,p_source_person IN VARCHAR2 := NULL
43 ,p_attribute_category IN VARCHAR2 := NULL
44 ,p_attribute1 IN VARCHAR2 := NULL
45 ,p_attribute2 IN VARCHAR2 := NULL
46 ,p_attribute3 IN VARCHAR2 := NULL
47 ,p_attribute4 IN VARCHAR2 := NULL
48 ,p_attribute5 IN VARCHAR2 := NULL
49 ,p_attribute6 IN VARCHAR2 := NULL
50 ,p_attribute7 IN VARCHAR2 := NULL
51 ,p_attribute8 IN VARCHAR2 := NULL
52 ,p_attribute9 IN VARCHAR2 := NULL
53 ,p_attribute10 IN VARCHAR2 := NULL
54 ,p_attribute11 IN VARCHAR2 := NULL
55 ,p_attribute12 IN VARCHAR2 := NULL
56 ,p_attribute13 IN VARCHAR2 := NULL
57 ,p_attribute14 IN VARCHAR2 := NULL
58 ,p_attribute15 IN VARCHAR2 := NULL
59 ,px_ci_id IN OUT NOCOPY NUMBER
60 ,x_ci_number OUT NOCOPY VARCHAR2
61 ,x_return_status OUT NOCOPY VARCHAR2
62 ,x_msg_count OUT NOCOPY NUMBER
63 ,x_msg_data OUT NOCOPY VARCHAR2
64 ) is
65
66 l_ci_number_num NUMBER(15) := NULL;
67 -- l_ci_number_char VARCHAR2(30) := NULL;
68 l_ci_number_char PA_CONTROL_ITEMS.ci_number%type := NULL;
69 l_ci_number number;
70
71 l_system_number_id NUMBER(15) := NULL;
72 cursor c_system_stat is
73 Select project_system_status_code
74 From PA_PROJECT_STATUSES
75 Where project_status_code = p_status_code;
76 cp_stat_code c_system_stat%ROWTYPE;
77
78 cursor c_item_type is
79 Select ci_type_class_code, auto_number_flag,
80 start_date_active,end_date_active
81 From PA_CI_TYPES_B
82 Where ci_type_id = p_ci_type_id;
83
84 cp_type c_item_type%ROWTYPE;
85 l_type_class_code PA_CI_TYPES_B.CI_TYPE_CLASS_CODE%TYPE;
86 l_system_status_code pa_project_statuses.project_system_status_code%TYPE;
87 DRAFT_STATUS pa_project_statuses.project_system_status_code%TYPE := 'CI_DRAFT';
88
89 API_ERROR EXCEPTION;
90 l_auto_number VARCHAR2(1);
91 l_type_start_date DATE;
92 l_type_end_date DATE;
93 l_rowid ROWID;
94
95 -- Bug 3297238. FP M changes.
96 l_item_key pa_wf_processes.item_key%TYPE;
97 l_debug_mode VARCHAR2(1);
98 l_debug_level6 CONSTANT NUMBER := 6;
99 g_module_name VARCHAR2(100) := 'pa.plsql.CreateCI,Add_Control_Item';
100
101 BEGIN
102
103 -- Initialize the Error Stack
104 PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.Add_Control_Item');
105 x_return_status := FND_API.G_RET_STS_SUCCESS;
106
107 -- Issue API savepoint if the transaction is to be committed
108 IF p_commit = FND_API.G_TRUE THEN
109 SAVEPOINT add_control_item;
110 END IF;
111
112
113 IF (p_price_currency_code is not null) THEN
114 begin
115 select ROWID
116 into l_rowid
117 from fnd_currencies_tl
118 where currency_code = p_price_currency_code
119 AND language = USERENV('LANG');
120 exception when OTHERS then
121 PA_UTILS.Add_Message( p_app_short_name => 'PA'
122 ,p_msg_name => 'PA_CURRENCY_CODE_INV');
123 x_return_status := 'E';
124 end;
125 END IF;
126
127 IF has_null_data(
128 p_ci_type_id
129 ,p_project_id
130 ,p_status_code
131 ,p_owner_id
132 -- ,l_ci_number_char
133 ,p_summary) THEN
134 x_return_status := FND_API.G_RET_STS_ERROR;
135 PA_DEBUG.Reset_Err_Stack;
136 RETURN;
137 END IF;
138
139
140 -- verify type is valid, get numbering options and code
141 open c_item_type;
142 fetch c_item_type into cp_type;
143 if (c_item_type%notfound) then
144 close c_item_type;
145 PA_UTILS.Add_Message( p_app_short_name => 'PA'
146 ,p_msg_name => 'PA_CI_INVALID_TYPE_ID');
147 x_return_status := FND_API.G_RET_STS_ERROR;
148 --PA_DEBUG.RESET_ERR_STACK;
149 -- return;
150 end if;
151 l_type_class_code := cp_type.ci_type_class_code;
152 l_auto_number := cp_type.auto_number_flag;
153 l_type_start_date := cp_type.start_date_active;
154 l_type_end_date := cp_type.end_date_active;
155
156 close c_item_type;
157
158 open c_system_stat;
159 fetch c_system_stat into cp_stat_code;
160 if (c_system_stat%notfound) then
161 close c_system_stat;
162 PA_UTILS.Add_Message( p_app_short_name => 'PA'
163 ,p_msg_name => 'PA_CI_INVALID_STATUS_CODE');
164 x_return_status := FND_API.G_RET_STS_ERROR;
165 -- PA_DEBUG.RESET_ERR_STACK;
166 -- return;
167 end if;
168 l_system_status_code := cp_stat_code.project_system_status_code;
169 close c_system_stat;
170
171 IF (x_return_status <> 'S') THEN
172 PA_DEBUG.RESET_ERR_STACK;
173 return;
174 END IF;
175
176
177 IF l_auto_number = 'Y' and l_system_status_code <> DRAFT_STATUS THEN
178 LOOP
179 PA_SYSTEM_NUMBERS_PKG.GET_NEXT_NUMBER (
180 p_object1_pk1_value => p_project_id
181 ,p_object1_type => 'PA_PROJECTS'
182 ,p_object2_pk1_value => p_ci_type_id
183 ,p_object2_type => l_type_class_code
184 ,x_system_number_id => l_system_number_id
185 ,x_next_number => l_ci_number_num
186 ,x_return_status => x_return_status
187 ,x_msg_count => x_msg_count
188 ,x_msg_data => x_msg_data);
189
190 IF x_return_status <> FND_API.g_ret_sts_success THEN
191 PA_DEBUG.Reset_Err_Stack;
192 raise API_ERROR;
193 END IF;
194 l_ci_number_char := TO_CHAR(l_ci_number_num);
195
196 -- call Client Extension here
197 PA_CI_NUMBER_CLIENT_EXTN.GET_NEXT_NUMBER (
198 p_object1_pk1_value => p_project_id
199 ,p_object1_type => 'PA_PROJECTS'
200 ,p_object2_pk1_value => p_ci_type_id
201 ,p_object2_type => l_type_class_code
202 ,p_next_number => l_ci_number_char
203 ,x_return_status => x_return_status
204 ,x_msg_count => x_msg_count
205 ,x_msg_data => x_msg_data);
206
207 EXIT WHEN ci_number_exists(p_project_id, l_ci_number_char
208 ,p_ci_type_id) = FALSE;
209 END LOOP;
210 ELSE
211 l_ci_number_char := p_ci_number;
212
213 if ci_number_exists(p_project_id, l_ci_number_char ,p_ci_type_id) = TRUE then
214 PA_UTILS.Add_Message( p_app_short_name => 'PA'
215 ,p_msg_name => 'PA_CI_DUPLICATE_CI_NUMBER');
216 x_return_status := FND_API.G_RET_STS_ERROR;
217 PA_DEBUG.RESET_ERR_STACK;
218 return;
219
220 end if;
221 END IF;
222
223
224 IF l_ci_number_char is NULL and l_system_status_code <> DRAFT_STATUS THEN
225 PA_UTILS.Add_Message( p_app_short_name => 'PA'
226 ,p_msg_name => 'PA_CI_NO_CI_NUMBER');
227 x_return_status := FND_API.G_RET_STS_ERROR;
228 PA_DEBUG.Reset_Err_Stack;
229 RETURN;
230
231 END IF;
232
233 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
234 IF l_debug_mode = 'Y' THEN
235 pa_debug.g_err_stage:= 'About to call the table handler';
236 pa_debug.write(g_module_name,pa_debug.g_err_stage,
237 l_debug_level6);
238 END IF;
239
240
241 --Validate all PA_LOOKUPS values
242
243 IF (x_return_status <> 'E') THEN
244 PA_CONTROL_ITEMS_PKG.INSERT_ROW (
245 p_ci_type_id
246 ,p_summary
247 ,p_status_code
248 ,p_owner_id
249 ,p_highlighted_flag
250 ,NVL(p_progress_status_code, 'PROGRESS_STAT_ON_TRACK')
251 ,NVL(p_progress_as_of_date,sysdate)
252 ,p_classification_code
253 ,p_reason_code
254 ,p_project_id
255 -- ,sysdate
256 ,p_last_modified_by_id
257 ,p_object_type
258 ,p_object_id
259 ,l_ci_number_char
260 ,p_date_required
261 ,p_date_closed
262 ,p_closed_by_id
263 ,p_description
264 ,p_status_overview
265 ,p_resolution
266 ,p_resolution_code
267 ,p_priority_code
268 ,p_effort_level_code
269 ,nvl(p_open_action_num,0)
270 ,p_price
271 ,p_price_currency_code
272 ,p_source_type_code
273 ,p_source_comment
274 ,p_source_number
275 ,p_source_date_received
276 ,p_source_organization
277 ,p_source_person
278
279 ,p_attribute_category
280
281 ,p_attribute1
282 ,p_attribute2
283 ,p_attribute3
284 ,p_attribute4
285 ,p_attribute5
286 ,p_attribute6
287 ,p_attribute7
288 ,p_attribute8
289 ,p_attribute9
290 ,p_attribute10
291 ,p_attribute11
292 ,p_attribute12
293 ,p_attribute13
294 ,p_attribute14
295 ,p_attribute15
296 ,px_ci_id
297 ,x_return_status
298 ,x_msg_count
299 ,x_msg_data
300 );
301 END IF;
302
303 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
304 IF l_debug_mode = 'Y' THEN
305 pa_debug.g_err_stage:= 'Table handler called';
306 pa_debug.write(g_module_name,pa_debug.g_err_stage,
307 l_debug_level6);
308 END IF;
309
310 -- Launch the workflow notification if it is not validate only mode and no errors occured till now.
311 -- Bug 3297238. FP M changes.
312 IF ( p_validate_only = FND_API.G_FALSE AND x_return_status = FND_API.g_ret_sts_success )THEN
313 pa_control_items_workflow.START_NOTIFICATION_WF
314 ( p_item_type => 'PAWFCISC'
315 ,p_process_name => 'PA_CI_OWNER_CHANGE_FYI'
316 ,p_ci_id => px_ci_id
317 ,p_action_id => NULL
318 ,x_item_key => l_item_key
319 ,x_return_status => x_return_status
320 ,x_msg_count => x_msg_count
321 ,x_msg_data => x_msg_data );
322
323 IF x_return_status <> FND_API.g_ret_sts_success THEN
324 PA_DEBUG.Reset_Err_Stack;
325 raise API_ERROR;
326 END IF;
327 END IF;
328
329 -- Commit if the flag is set and there is no error
330 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
331 COMMIT;
332 END IF;
333
334
335 -- Reset the error stack when returning to the calling program
336 PA_DEBUG.Reset_Err_Stack;
337
338
339 EXCEPTION
340 WHEN API_ERROR THEN
341 x_return_status := x_return_status;
342
343 WHEN OTHERS THEN
344 IF p_commit = FND_API.G_TRUE THEN
345 ROLLBACK TO add_control_item;
346 END IF;
347
348 -- Set the excetption Message and the stack
349 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.add_control_item'
350 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
351
352
353 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
354 RAISE;
355
356 end ADD_CONTROL_ITEM;
357
358 procedure UPDATE_CONTROL_ITEM (
359 p_api_version IN NUMBER := 1.0
360 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
361 ,p_commit IN VARCHAR2 := FND_API.g_false
362 ,p_validate_only IN VARCHAR2 := FND_API.g_true
363 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
364
365 ,p_ci_id IN NUMBER
366 ,p_ci_type_id IN NUMBER
367 ,p_summary IN VARCHAR2
368 ,p_status_code IN VARCHAR2 := NULL
369 ,p_owner_id IN NUMBER
370 ,p_highlighted_flag IN VARCHAR2 := 'N'
371 ,p_progress_status_code IN VARCHAR2
372 ,p_progress_as_of_date IN DATE := NULL
373 ,p_classification_code IN NUMBER
374 ,p_reason_code IN NUMBER
375 ,p_record_version_number IN NUMBER
376 ,p_project_id IN NUMBER
377 ,p_last_modified_by_id IN NUMBER
378 ,p_object_type IN VARCHAR2 := NULL
379 ,p_object_id IN NUMBER := NULL
380 ,p_ci_number IN VARCHAR2 := NULL
381 ,p_date_required IN DATE := NULL
382 ,p_date_closed IN DATE := NULL
383 ,p_closed_by_id IN NUMBER := NULL
384
385 ,p_description IN VARCHAR2 := NULL
386 ,p_status_overview IN VARCHAR2 := NULL
387 ,p_resolution IN VARCHAR2 := NULL
388 ,p_resolution_code IN NUMBER := NULL
389 ,p_priority_code IN VARCHAR2 := NULL
390 ,p_effort_level_code IN VARCHAR2 := NULL
391 ,p_open_action_num IN NUMBER := NULL
392 ,p_price IN NUMBER := NULL
393 ,p_price_currency_code IN VARCHAR2 := NULL
394 ,p_source_type_code IN VARCHAR2 := NULL
395 ,p_source_comment IN VARCHAR2 := NULL
396 ,p_source_number IN VARCHAR2 := NULL
397 ,p_source_date_received IN DATE := NULL
398 ,p_source_organization IN VARCHAR2 := NULL
399 ,p_source_person IN VARCHAR2 := NULL
400
401 ,p_attribute_category IN VARCHAR2 := NULL
402
403 ,p_attribute1 IN VARCHAR2 := NULL
404 ,p_attribute2 IN VARCHAR2 := NULL
405 ,p_attribute3 IN VARCHAR2 := NULL
406 ,p_attribute4 IN VARCHAR2 := NULL
407 ,p_attribute5 IN VARCHAR2 := NULL
408 ,p_attribute6 IN VARCHAR2 := NULL
409 ,p_attribute7 IN VARCHAR2 := NULL
410 ,p_attribute8 IN VARCHAR2 := NULL
411 ,p_attribute9 IN VARCHAR2 := NULL
412 ,p_attribute10 IN VARCHAR2 := NULL
413 ,p_attribute11 IN VARCHAR2 := NULL
414 ,p_attribute12 IN VARCHAR2 := NULL
415 ,p_attribute13 IN VARCHAR2 := NULL
416 ,p_attribute14 IN VARCHAR2 := NULL
417 ,p_attribute15 IN VARCHAR2 := NULL
418 ,x_return_status OUT NOCOPY VARCHAR2
419 ,x_msg_count OUT NOCOPY NUMBER
420 ,x_msg_data OUT NOCOPY VARCHAR2
421 ) is
422 CURSOR curr_row is
423 SELECT *
424 FROM pa_control_items
425 WHERE ci_id = p_ci_id;
426
427 cp curr_row%rowtype;
428 l_ROWID ROWID;
429
430 cursor C is select ROWID from PA_CONTROL_ITEMS
431 where project_id = p_project_id
432 and ci_number = p_ci_number
433 and ci_id <> p_ci_id
434 and ci_type_id = p_ci_type_id;
435
436 l_as_of_date DATE := sysdate;
437 l_status_code pa_control_items.status_code%TYPE;
438 l_new_status_code pa_control_items.status_code%TYPE; /* Bug#5676037: Code changes for AMG APIs */
439 l_ci_system_status pa_project_statuses.project_system_status_code%TYPE := NULL ;
440 l_auto_numbers VARCHAR2(1) := 'N';
441 l_ci_number pa_control_items.ci_number%TYPE := NULL;
442
443 --bug 3297238
444 l_item_key pa_wf_processes.item_key%TYPE;
445 l_prev_owner_id pa_control_items.owner_id%TYPE;
446
447 cursor c_auto_num is
448 Select type.auto_number_flag
449 From PA_CI_TYPES_B type
450 ,pa_control_items ci
451 Where ci.ci_id = p_ci_id
452 AND ci.ci_type_id = type.ci_type_id;
453
454 begin
455
456 -- Initialize the Error Stack
457 PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.Update_Control_Item');
458
459 x_return_status := FND_API.G_RET_STS_SUCCESS;
460
461 -- Issue API savepoint if the transaction is to be committed
462 IF p_commit = FND_API.G_TRUE THEN
463 SAVEPOINT update_control_item;
464 END IF;
465
466 OPEN curr_row;
467 FETCH curr_row INTO cp;
468 if curr_row%NOTFOUND then
469 close curr_row;
470 PA_UTILS.Add_Message( p_app_short_name => 'PA'
471 ,p_msg_name => 'PA_CI_INVALID_ITEM');
472 x_return_status := FND_API.G_RET_STS_ERROR;
473 return;
474 PA_DEBUG.Reset_Err_Stack;
475 end if;
476
477 /*Commenting for 4065728
478 IF cp.progress_status_code is NULL
479 OR cp.progress_status_code <> p_progress_status_code THEN
480 l_as_of_date := sysdate;
481 ELSE
482 IF cp.progress_as_of_date is NOT NULL THEN
483 l_as_of_date := cp.progress_as_of_date;
484 END IF;
485 END IF;
486 */
487
488 /*Added for Bug 4065728 */
489
490 IF p_progress_as_of_date is NOT NULL THEN
491 l_as_of_date := p_progress_as_of_date;
492 END IF;
493
494 /* End for Bug 4065728 */
495
496 --bug 3297238.
497 l_prev_owner_id := cp.owner_id;
498
499 --separate API to update status
500 l_status_code := cp.status_code;
501 l_new_status_code := p_status_code; --Bug 5676037
502 l_ci_number := cp.ci_number;
503 close curr_row;
504
505 IF (p_price_currency_code is not null) THEN
506 begin
507 select ROWID
508 into l_ROWID
509 from fnd_currencies_tl
510 where currency_code = p_price_currency_code
511 AND language = USERENV('LANG');
512 exception when OTHERS then
513 PA_UTILS.Add_Message( p_app_short_name => 'PA'
514 ,p_msg_name => 'PA_CURRENCY_CODE_INV');
515 x_return_status := 'E';
516 end;
517 END IF;
518
519 OPEN c_auto_num;
520 FETCH c_auto_num INTO l_auto_numbers;
521 if c_auto_num%NOTFOUND then
522 PA_UTILS.Add_Message(
523 p_app_short_name => 'PA'
524 ,p_msg_name => 'PA_CI_INVALID_TYPE_ID');
525 x_return_status := 'E';
526 close c_auto_num;
527 PA_DEBUG.Reset_Err_Stack;
528 return;
529 end if ;
530 close c_auto_num;
531
532
533 if l_auto_numbers is NOT NULL and l_auto_numbers <> 'Y' then
534 if p_ci_number is NOT NULL then
535 l_ci_number := p_ci_number;
536 open C;
537 fetch C into l_ROWID;
538 if (C%notfound) then
539 close C;
540 else
541 close C;
542 PA_UTILS.Add_Message( p_app_short_name => 'PA'
543 ,p_msg_name => 'PA_CI_DUPLICATE_CI_NUMBER');
544 x_return_status := FND_API.G_RET_STS_ERROR;
545 PA_DEBUG.Reset_Err_Stack;
546 return;
547 end if;
548 else
549 -- ci number may not be NULL in non CI_DRAFT status
550 l_ci_system_status := PA_CONTROL_ITEMS_UTILS.getSystemStatus(l_status_code);
551 if l_ci_system_status is NULL then
552 PA_UTILS.Add_Message( p_app_short_name => 'PA'
553 ,p_msg_name => 'PA_CI_NO_STATUS');
554 x_return_status := FND_API.G_RET_STS_ERROR;
555 PA_DEBUG.Reset_Err_Stack;
556 return;
557 end if;
558 if 'CI_DRAFT' <> l_ci_system_status then
559 PA_UTILS.Add_Message( p_app_short_name => 'PA'
560 ,p_msg_name => 'PA_CI_NO_CI_NUMBER');
561 x_return_status := FND_API.G_RET_STS_ERROR;
562 PA_DEBUG.Reset_Err_Stack;
563 return;
564 end if;
565 end if;
566 end if; --if manual numbers
567
568
569 IF (x_return_status <> 'E') THEN
570 PA_CONTROL_ITEMS_PKG.UPDATE_ROW(
571 p_ci_id
572 ,p_ci_type_id
573 ,p_summary
574 ,l_new_status_code
575 ,p_owner_id
576 ,p_highlighted_flag
577 ,p_progress_status_code
578 ,l_as_of_date --p_progress_as_of_date
579 ,p_classification_code
580 ,p_reason_code
581 ,p_record_version_number
582 ,p_project_id
583 ,p_last_modified_by_id
584 ,p_object_type
585 ,p_object_id
586 ,l_ci_number --p_ci_number
587 ,p_date_required
588 ,p_date_closed
589 ,p_closed_by_id
590 ,p_description
591 ,p_status_overview
592 ,p_resolution
593 ,p_resolution_code
594 ,p_priority_code
595 ,p_effort_level_code
596 ,p_open_action_num
597 ,p_price
598 ,p_price_currency_code
599 ,p_source_type_code
600 ,p_source_comment
601 ,p_source_number
602 ,p_source_date_received
603 ,p_source_organization
604 ,p_source_person
605
606 ,p_attribute_category
607
608 ,p_attribute1
609 ,p_attribute2
610 ,p_attribute3
611 ,p_attribute4
612 ,p_attribute5
613 ,p_attribute6
614 ,p_attribute7
615 ,p_attribute8
616 ,p_attribute9
617 ,p_attribute10
618 ,p_attribute11
619 ,p_attribute12
620 ,p_attribute13
621 ,p_attribute14
622 ,p_attribute15
623 ,x_return_status
624 ,x_msg_count
625 ,x_msg_data
626 );
627 END IF;
628
629 -- Launch the workflow notification if it is not validate only mode and no errors occured till now and
630 -- the owner is getting changed.
631 -- Bug 3297238. FP M Changes.
632 IF ( p_validate_only = FND_API.G_FALSE AND
633 x_return_status = FND_API.g_ret_sts_success AND
634 l_prev_owner_id <> p_owner_id )THEN -- owner id cannot be null as it is validated in public API.
635
636 pa_control_items_workflow.START_NOTIFICATION_WF
637 ( p_item_type => 'PAWFCISC'
638 ,p_process_name => 'PA_CI_OWNER_CHANGE_FYI'
639 ,p_ci_id => p_ci_id
640 ,p_action_id => NULL
641 ,x_item_key => l_item_key
642 ,x_return_status => x_return_status
643 ,x_msg_count => x_msg_count
644 ,x_msg_data => x_msg_data );
645
646 END IF;
647
648 -- Commit if the flag is set and there is no error
649 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
650 COMMIT;
651 END IF;
652
653
654 -- Reset the error stack when returning to the calling program
655 PA_DEBUG.Reset_Err_Stack;
656
657
658 EXCEPTION
659 WHEN OTHERS THEN
660 IF p_commit = FND_API.G_TRUE THEN
661 ROLLBACK TO update_control_item;
662 END IF;
663
664 -- Set the excetption Message and the stack
665 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.update_control_item'
666 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
667
668
669 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
670 RAISE;
671 end UPDATE_CONTROL_ITEM;
672
673
674
675 procedure DELETE_CONTROL_ITEM (
676 p_api_version IN NUMBER := 1.0
677 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
678 ,p_commit IN VARCHAR2 := FND_API.g_false
679 ,p_validate_only IN VARCHAR2 := FND_API.g_true
680 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
681
682 ,p_ci_id IN NUMBER
683 ,p_record_version_number IN NUMBER
684 ,x_return_status OUT NOCOPY VARCHAR2
685 ,x_msg_count OUT NOCOPY NUMBER
686 ,x_msg_data OUT NOCOPY VARCHAR2
687
688 ) is
689 l_status_code pa_project_statuses.project_system_status_code%type;
690 cursor valid_ci is
691 select pps.project_system_status_code --status_code
692 from pa_control_items ci
693 ,pa_project_statuses pps
694 where ci.ci_id = p_ci_id
695 and ci.status_code = pps.project_status_code;
696 begin
697
698 -- Initialize the Error Stack
699 PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.Delete_Control_Item');
700
701 x_return_status := FND_API.G_RET_STS_SUCCESS;
702 -- Issue API savepoint if the transaction is to be committed
703 IF p_commit = FND_API.G_TRUE THEN
704 SAVEPOINT delete_control_item;
705 END IF;
706
707 IF p_ci_id is NULL THEN
708 PA_UTILS.Add_Message( p_app_short_name => 'PA'
709 ,p_msg_name => 'PA_CI_INVALID_ITEM');
710 x_return_status := FND_API.G_RET_STS_ERROR;
711 ELSE
712 open valid_ci;
713 fetch valid_ci into l_status_code;
714 if (valid_ci%notfound) then
715 --- invalid ci_id error
716 PA_UTILS.Add_Message( p_app_short_name => 'PA'
717 ,p_msg_name => 'PA_CI_INVALID_ITEM');
718 x_return_status := FND_API.G_RET_STS_ERROR;
719 else
720 if (l_status_code <> 'CI_DRAFT') then
721 --- invalid status error
722 PA_UTILS.Add_Message( p_app_short_name => 'PA'
723 ,p_msg_name => 'PA_CI_ONLY_DRAFT_DEL');
724 x_return_status := FND_API.G_RET_STS_ERROR;
725 end if;
726 end if;
727 close valid_ci;
728 END IF;
729 IF x_return_status = FND_API.g_ret_sts_success THEN
730 --- delete all actions
731 pa_ci_actions_pvt.delete_all_actions(p_validate_only => 'F',
732 p_init_msg_list => 'F',
733 p_ci_id => p_ci_id,
734 x_return_status => x_return_status,
735 x_msg_count => x_msg_count,
736 x_msg_data => x_msg_data);
737 --- delete all impacts
738 pa_ci_impacts_util.delete_All_impacts(p_validate_only => 'F',
739 p_init_msg_list => 'F',
740 p_ci_id => p_ci_id,
741 x_return_status => x_return_status,
742 x_msg_count => x_msg_count,
743 x_msg_data => x_msg_data);
744
745 --- change status for any included 'CR' to 'APPROVED'
746 --- call procedure change_included_cr_status
747 change_included_cr_status(p_ci_id => p_ci_id
748 ,x_return_status => x_return_status
749 ,x_msg_count => x_msg_count
750 ,x_msg_data => x_msg_data);
751
752 --- delete all related items
753 delete_all_related_items (p_validate_only => 'F',
754 p_init_msg_list => 'F',
755 p_ci_id => p_ci_id,
756 x_return_status => x_return_status,
757 x_msg_count => x_msg_count,
758 x_msg_data => x_msg_data);
759
760 --- delete all included crs
761 delete_all_included_crs (p_validate_only => 'F',
762 p_init_msg_list => 'F',
763 p_ci_id => p_ci_id,
764 x_return_status => x_return_status,
765 x_msg_count => x_msg_count,
766 x_msg_data => x_msg_data);
767
768 --- delete doc attachments
769 pa_ci_doc_attach_pkg.delete_all_attachments (p_validate_only => 'F',
770 p_init_msg_list => 'F',
771 p_ci_id => p_ci_id,
772 x_return_status => x_return_status,
773 x_msg_count => x_msg_count,
774 x_msg_data => x_msg_data);
775
776 --- delete control_item
777 PA_CONTROL_ITEMS_PKG.DELETE_ROW(
778 p_ci_id
779 ,p_record_version_number
780 ,x_return_status
781 ,x_msg_count
782 ,x_msg_data
783 );
784 END IF;
785 -- Commit if the flag is set and there is no error
786 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
787 COMMIT;
788 END IF;
789
790 -- Reset the error stack when returning to the calling program
791 PA_DEBUG.Reset_Err_Stack;
792
793
794 EXCEPTION
795 WHEN OTHERS THEN
796 IF p_commit = FND_API.G_TRUE THEN
797 ROLLBACK TO delete_control_item;
798 END IF;
799
800 -- Set the excetption Message and the stack
801 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.delete_control_item'
802 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
803
804
805 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
806 RAISE;
807 end DELETE_CONTROL_ITEM;
808
809 procedure DELETE_ALL_CONTROL_ITEMS(
810 p_api_version IN NUMBER := 1.0
811 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
812 ,p_commit IN VARCHAR2 := FND_API.g_false
813 ,p_validate_only IN VARCHAR2 := FND_API.g_true
814 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
815
816 ,p_project_id IN NUMBER
817 ,x_return_status OUT NOCOPY VARCHAR2
818 ,x_msg_count OUT NOCOPY NUMBER
819 ,x_msg_data OUT NOCOPY VARCHAR2
820
821 ) is
822 l_msg_index_out NUMBER;
823
824 BEGIN
825
826 -- Initialize the Error Stack
827 PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.Delete_ALL_Control_Items');
828 x_return_status := FND_API.G_RET_STS_SUCCESS;
829
830 -- Issue API savepoint if the transaction is to be committed
831 IF p_commit = FND_API.G_TRUE THEN
832 SAVEPOINT delete_all_control_items;
833 END IF;
834
835 --Clear the global PL/SQL message table
836 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
837 FND_MSG_PUB.initialize;
838 END IF;
839
840 FOR ci_rec IN( SELECT ci_id, record_version_number
841 FROM pa_control_items
842 WHERE project_id = p_project_id ) LOOP
843 DELETE_CONTROL_ITEM(
844 p_api_version
845 ,'F'
846 ,'F'
847 ,'F'
848 ,p_max_msg_count
849
850 ,ci_rec.ci_id
851 ,ci_rec.record_version_number
852 ,x_return_status
853 ,x_msg_count
854 ,x_msg_data );
855
856 EXIT WHEN x_return_status <> FND_API.g_ret_sts_success;
857 END LOOP;
858
859 -- IF the number of messaages is 1 then fetch the message code from the stack
860 -- and return its text
861 x_msg_count := FND_MSG_PUB.Count_Msg;
862 IF x_msg_count = 1 THEN
863 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
864 ,p_msg_index => 1
865 ,p_data => x_msg_data
866 ,p_msg_index_out => l_msg_index_out
867 );
868 END IF;
869 -- Commit if the flag is set and there is no error
870 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
871 COMMIT;
872 END IF;
873
874 -- Reset the error stack when returning to the calling program
875 PA_DEBUG.Reset_Err_Stack;
876
877 EXCEPTION
878 WHEN NO_DATA_FOUND THEN
879 x_return_status := FND_API.G_RET_STS_SUCCESS;
880
881 WHEN OTHERS THEN
882 IF p_commit = FND_API.G_TRUE THEN
883 ROLLBACK TO delete_all_control_items;
884 END IF;
885
886 -- Set the excetption Message and the stack
887 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.delete_all_control_items'
888 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
889
890
891 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
892 RAISE;
893 end DELETE_ALL_CONTROL_ITEMS;
894
895 procedure COPY_CONTROL_ITEM (
896 p_api_version IN NUMBER := 1.0
897 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
898 ,p_commit IN VARCHAR2 := FND_API.g_false
899 ,p_validate_only IN VARCHAR2 := FND_API.g_true
900 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
901
902 ,p_project_id IN NUMBER
903 ,p_ci_id_from IN NUMBER -- copy from this
904 ,p_ci_type_id IN NUMBER -- copy to this
905 ,p_classification_code_id IN NUMBER
906 ,p_reason_code_id IN NUMBER
907
908 ,p_include IN VARCHAR2 := 'N'
909 ,p_record_version_number_from IN NUMBER
910 ,x_ci_id OUT NOCOPY NUMBER
911 ,x_ci_number OUT NOCOPY VARCHAR2
912 ,x_return_status OUT NOCOPY VARCHAR2
913 ,x_msg_count OUT NOCOPY NUMBER
914 ,x_msg_data OUT NOCOPY VARCHAR2
915
916 ) is
917
918 l_reason NUMBER := NULL; -- mwxx VARCHAR2(30):= NULL;
919 l_class_code NUMBER := NULL; -- mwxx VARCHAR2(30):= NULL;
920 p_reason NUMBER := NULL; -- mwxx VARCHAR2(30):= NULL;
921 p_class_code NUMBER := NULL; -- mwxx VARCHAR2(30):= NULL;
922 l_msg_index_out NUMBER;
923 l_from_type_id NUMBER;
924 l_relationship_id NUMBER;
925 l_commit VARCHAR2(1) := 'N';
926 copy_from_row pa_control_items%ROWTYPE;
927
928 CURSOR c_from_item
929 is
930 SELECT * FROM pa_control_items
931 WHERE ci_id = p_ci_id_from;
932
933 /* mwxx
934 CURSOR c_from_classification
935 is
936 SELECT 'Y'
937 FROM pa_ci_types_b pctb, pa_class_codes pcc
938 WHERE pctb.ci_type_id = p_ci_type_id
939 AND pctb.classification_category = pcc.class_category
940 AND pcc.class_code = p_class_code;
941 */
942
943
944 CURSOR c_from_classification
945 is
946 SELECT class_code_id
947 FROM pa_class_codes pcc,pa_ci_types_b pctb
948 WHERE pctb.ci_type_id = p_ci_type_id
949 AND pctb.classification_category = pcc.class_category
950 AND pcc.class_code in (select pcc1.class_code
951 from pa_class_codes pcc1
952 where pcc1.class_code_id = p_class_code);
953
954 CURSOR c_from_reason
955 is
956 SELECT class_code_id
957 FROM pa_class_codes pcc,pa_ci_types_b pctb
958 WHERE pctb.ci_type_id = p_ci_type_id
959 AND pctb.reason_category = pcc.class_category
960 AND pcc.class_code in (select pcc1.class_code
961 from pa_class_codes pcc1
962 where pcc1.class_code_id = p_reason);
963 /* mwxx
964
965 CURSOR c_from_reason
966 is
967 SELECT 'Y'
968 FROM pa_ci_types_b pctb, pa_class_codes pcc
969 WHERE pctb.ci_type_id = p_ci_type_id
970 AND pctb.reason_category = pcc.class_category
971 AND pcc.class_code = p_reason;
972 */
973
974 copy_class c_from_classification%ROWTYPE;
975 copy_reason c_from_reason%ROWTYPE;
976 l_ci_id number := null;
977 begin
978
979 -- Initialize the Error Stack
980 PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PUB.COPY_CONTROL_ITEM');
981
982 -- Initialize the return status to success
983 x_return_status := FND_API.G_RET_STS_SUCCESS;
984
985 --Clear the global PL/SQL message table
986 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
987 FND_MSG_PUB.initialize;
988 END IF;
989
990 OPEN c_from_item;
991 FETCH c_from_item INTO copy_from_row;
992 if c_from_item%NOTFOUND then
993 close c_from_item;
994 PA_UTILS.Add_Message( p_app_short_name => 'PA'
995 ,p_msg_name => 'PA_CI_NO_FROM_ITEM');
996 x_return_status := FND_API.G_RET_STS_ERROR;
997 end if;
998
999 ---- check that impacts of source ci_id_from should be less than or equalto the
1000 ---- impacts of the destination ci_type_id. (i.e. new ci)
1001 if (x_return_status = 'S' and p_include = 'Y') then
1002 if (pa_control_items_utils.IsImpactOkToInclude(p_ci_type_id, null, p_ci_id_from) <> 'Y') then
1003 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1004 ,p_msg_name => 'PA_CI_NO_IMP_INCLUDE');
1005 x_return_status := FND_API.G_RET_STS_ERROR;
1006 end if;
1007 end if;
1008
1009 if p_validate_only=fnd_api.g_false AND
1010 x_return_status = FND_API.G_RET_STS_SUCCESS then
1011
1012 --- only copy clasification and reason if the source classification and reason
1013 --- code is in classificationa and reason category of the destination ci
1014 l_from_type_id := copy_from_row.ci_type_id;
1015 p_reason := copy_from_row.reason_code_id;
1016 p_class_code := copy_from_row.classification_code_id;
1017
1018 if p_ci_type_id = copy_from_row.ci_type_id then
1019 l_reason := copy_from_row.reason_code_id;
1020 l_class_code := copy_from_row.classification_code_id;
1021 else
1022 open c_from_classification;
1023 fetch c_from_classification into copy_class;
1024 if c_from_classification%notfound then
1025 l_class_code := p_classification_code_id;
1026 else
1027 l_class_code := copy_class.class_code_id; --p_class_code;
1028 end if;
1029 close c_from_classification;
1030
1031 open c_from_reason;
1032 fetch c_from_reason into copy_reason;
1033 if c_from_reason%notfound then
1034 l_reason := p_reason_code_id;
1035 else
1036 l_reason := copy_class.class_code_id; --p_reason;
1037 end if;
1038 close c_from_reason;
1039
1040 end if;
1041
1042 PA_CONTROL_ITEMS_PVT.ADD_CONTROL_ITEM(
1043 p_api_version => p_api_version
1044 ,p_init_msg_list => p_init_msg_list
1045 ,p_commit => FND_API.g_false
1046 ,p_validate_only => p_validate_only
1047 ,p_max_msg_count => p_max_msg_count
1048
1049 ,p_ci_type_id => p_ci_type_id
1050 ,p_summary => copy_from_row.summary
1051
1052 -- ,p_status_code => pa_control_items_utils.get_initial_ci_status(p_ci_type_id)
1053
1054 -- set the initial status to Draft. When numbers are assigned manually, there is no way
1055 -- to enter the number when an item is copied. The Number (ci_number) is a required field
1056 -- when a control item is is any status other than "Draft".
1057 ,p_status_code => 'CI_DRAFT' --because of manual numbering
1058
1059 ,p_owner_id => copy_from_row.owner_id
1060 ,p_highlighted_flag => copy_from_row.highlighted_flag
1061 ,p_progress_status_code => NULL
1062 ,p_progress_as_of_date => SYSDATE
1063 ,p_classification_code => l_class_code
1064 ,p_reason_code => l_reason
1065 ,p_project_id => p_project_id
1066 ,p_last_modified_by_id => PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id )
1067 ,p_object_type => copy_from_row.object_type
1068 ,p_object_id => copy_from_row.object_id
1069 ,p_ci_number => NULL
1070 ,p_date_required => to_date(NULL)
1071 ,p_date_closed => copy_from_row.date_closed
1072 ,p_closed_by_id => copy_from_row.closed_by_id
1073 ,p_description => copy_from_row.description
1074 ,p_status_overview => NULL --copy_from_row.status_overview
1075 ,p_resolution => NULL --p_resolution
1076 ,p_resolution_code => NULL --p_resolution_code
1077 ,p_priority_code => copy_from_row.priority_code
1078 ,p_effort_level_code => copy_from_row.effort_level_code
1079 ,p_open_action_num => 0
1080
1081 ,p_price => copy_from_row.price
1082 ,p_price_currency_code => copy_from_row.price_currency_code
1083 ,p_source_type_code => copy_from_row.source_type_code
1084 ,p_source_comment => copy_from_row.source_comment
1085 ,p_source_number => copy_from_row.source_number
1086 ,p_source_date_received => copy_from_row.source_date_received
1087 ,p_source_organization => copy_from_row.source_organization
1088 ,p_source_person => copy_from_row.source_person
1089
1090 ,p_attribute_category => copy_from_row.attribute_category
1091 ,p_attribute1 => copy_from_row.attribute1
1092 ,p_attribute2 => copy_from_row.attribute2
1093 ,p_attribute3 => copy_from_row.attribute3
1094 ,p_attribute4 => copy_from_row.attribute4
1095 ,p_attribute5 => copy_from_row.attribute5
1096 ,p_attribute6 => copy_from_row.attribute6
1097 ,p_attribute7 => copy_from_row.attribute7
1098 ,p_attribute8 => copy_from_row.attribute8
1099 ,p_attribute9 => copy_from_row.attribute9
1100 ,p_attribute10 => copy_from_row.attribute10
1101 ,p_attribute11 => copy_from_row.attribute11
1102 ,p_attribute12 => copy_from_row.attribute12
1103 ,p_attribute13 => copy_from_row.attribute13
1104 ,p_attribute14 => copy_from_row.attribute14
1105 ,p_attribute15 => copy_from_row.attribute15
1106 ,px_ci_id => l_ci_id
1107 ,x_ci_number => x_ci_number
1108 ,x_return_status => x_return_status
1109 ,x_msg_count => x_msg_count
1110 ,x_msg_data => x_msg_data);
1111
1112 x_ci_id := l_ci_id;
1113 close c_from_item;
1114
1115 ------- copy impacts
1116 if (x_return_status = FND_API.g_ret_sts_success and p_include = 'N') then
1117 pa_ci_impacts_util.copy_impact(p_validate_only => 'F',
1118 p_init_msg_list => 'F',
1119 P_DEST_CI_ID => x_ci_id,
1120 P_SOURCE_CI_ID => p_ci_id_from,
1121 P_INCLUDE_FLAG => 'N',
1122 x_return_status => x_return_status,
1123 x_msg_count => x_msg_count,
1124 x_msg_data => x_msg_data);
1125 end if;
1126
1127 if x_return_status = FND_API.g_ret_sts_success and p_include = 'Y' THEN
1128 PA_CONTROL_ITEMS_PVT.INCLUDE_CONTROL_ITEM(
1129 p_api_version => p_api_version
1130 ,p_init_msg_list => p_init_msg_list
1131 ,p_commit => 'F'
1132 ,p_validate_only => p_validate_only
1133 ,p_max_msg_count => p_max_msg_count
1134 ,p_from_ci_id => x_ci_id
1135 ,p_to_ci_id => p_ci_id_from
1136 ,p_record_version_number_to => p_record_version_number_from
1137 ,x_relationship_id => l_relationship_id
1138 ,x_return_status => x_return_status
1139 ,x_msg_count => x_msg_count
1140 ,x_msg_data => x_msg_data);
1141 end if;
1142
1143 --Copying document attachments
1144 IF x_return_status = 'S' THEN
1145 pa_ci_doc_attach_pkg.copy_attachments(
1146 p_init_msg_list => 'F',
1147 p_validate_only => 'F',
1148 p_from_ci_id => p_ci_id_from,
1149 p_to_ci_id => x_ci_id,
1150 x_return_status => x_return_status,
1151 x_msg_count => x_msg_count,
1152 x_msg_data => x_msg_data);
1153 END IF;
1154
1155 --Copying related items
1156 IF x_return_status = 'S' THEN
1157 copy_related_items(
1158 p_init_msg_list => 'F',
1159 p_validate_only => 'F',
1160 p_from_ci_id => p_ci_id_from,
1161 p_to_ci_id => x_ci_id,
1162 x_return_status => x_return_status,
1163 x_msg_count => x_msg_count,
1164 x_msg_data => x_msg_data);
1165 END IF;
1166
1167
1168 end if;
1169
1170 -- IF the number of messages is 1 then fetch the message code from the stack
1171 -- and return its text
1172 x_msg_count := FND_MSG_PUB.Count_Msg;
1173 IF x_msg_count = 1 THEN
1174 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
1175 ,p_msg_index => 1
1176 ,p_data => x_msg_data
1177 ,p_msg_index_out => l_msg_index_out
1178 );
1179 end if;
1180
1181 if (p_commit = 'T' and x_return_status = 'S') then
1182 commit;
1183 end if;
1184
1185 -- Reset the error stack when returning to the calling program
1186 PA_DEBUG.Reset_Err_Stack;
1187
1188
1189 EXCEPTION
1190 WHEN OTHERS THEN
1191 rollback;
1192 -- Set the excetption Message and the stack
1193 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PUB.COPY_CONTROL_ITEM'
1194 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1195
1196
1197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1198 RAISE;
1199 end COPY_CONTROL_ITEM;
1200
1201 procedure INCLUDE_CONTROL_ITEM(
1202 p_api_version IN NUMBER := 1.0
1203 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
1204 ,p_commit IN VARCHAR2 := FND_API.g_false
1205 ,p_validate_only IN VARCHAR2 := FND_API.g_true
1206 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
1207
1208 ,p_from_ci_id IN NUMBER
1209 ,p_to_ci_id IN NUMBER
1210 ,p_record_version_number_to IN NUMBER
1211 ,x_relationship_id OUT NOCOPY NUMBER
1212 ,x_return_status OUT NOCOPY VARCHAR2
1213 ,x_msg_count OUT NOCOPY NUMBER
1214 ,x_msg_data OUT NOCOPY VARCHAR2
1215 )
1216
1217 IS
1218 l_relationship_type VARCHAR2(30) := 'CI_INCLUDED_ITEM'; --- relationship type for included items
1219 l_rowid ROWID;
1220 l_ci_id NUMBER;
1221 l_project_id NUMBER;
1222 l_status_code VARCHAR2(30);
1223 l_ci_type_id_to NUMBER;
1224 l_ci_type_id_from NUMBER;
1225 l_record_version_number NUMBER;
1226 l_open_actions_num NUMBER;
1227
1228 CURSOR check_params is
1229 SELECT pci.ci_type_id, pctb.ci_type_class_code,
1230 pci.project_id, pps.project_system_status_code
1231 FROM pa_control_items pci, pa_ci_types_b pctb, pa_project_statuses pps
1232 WHERE pci.ci_id = l_ci_id
1233 and pci.ci_type_id = pctb.ci_type_id
1234 and pci.status_code = pps.project_status_code(+);
1235
1236 cp check_params%rowtype;
1237 begin
1238
1239 -- Initialize the Error Stack
1240 PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.Include_Control_Item');
1241
1242 x_return_status := FND_API.G_RET_STS_SUCCESS;
1243 -- Issue API savepoint if the transaction is to be committed
1244 IF p_commit = FND_API.G_TRUE THEN
1245 SAVEPOINT include_control_item;
1246 END IF;
1247
1248 -------Included in
1249 l_ci_id := p_from_ci_id;
1250 OPEN check_params;
1251 FETCH check_params INTO cp;
1252 if check_params%NOTFOUND then
1253 PA_UTILS.Add_Message(
1254 p_app_short_name => 'PA'
1255 ,p_msg_name => 'PA_CI_INVALID_ITEM');
1256 else
1257 l_project_id := cp.project_id;
1258 if (cp.ci_type_class_code <> 'CHANGE_ORDER') then
1259 PA_UTILS.Add_Message(
1260 p_app_short_name => 'PA'
1261 ,p_msg_name => 'PA_CI_INCL_CR_IN_CO');
1262 end if;
1263 l_ci_type_id_to := cp.ci_type_id;
1264 end if ;
1265 close check_params;
1266
1267 ------- To be included
1268 l_ci_id := p_to_ci_id;
1269 OPEN check_params;
1270 FETCH check_params INTO cp;
1271 if check_params%NOTFOUND then
1272 PA_UTILS.Add_Message(
1273 p_app_short_name => 'PA'
1274 ,p_msg_name => 'PA_CI_NO_INCLUDE_ITEM');
1275 else
1276 if (l_project_id <> cp.project_id) then
1277 PA_UTILS.Add_Message(
1278 p_app_short_name => 'PA'
1279 ,p_msg_name => 'PA_CI_INC_DIFF_PROJ');
1280 x_return_status := 'E';
1281 end if;
1282
1283 if (cp.project_system_status_code <> 'CI_APPROVED') then
1284 PA_UTILS.Add_Message(
1285 p_app_short_name => 'PA'
1286 ,p_msg_name => 'PA_CI_INC_STAT_INV');
1287 x_return_status := 'E';
1288 end if;
1289
1290 if (cp.ci_type_class_code <> 'CHANGE_REQUEST') then
1291 PA_UTILS.Add_Message(
1292 p_app_short_name => 'PA'
1293 ,p_msg_name => 'PA_CI_INCL_CR_IN_CO');
1294 end if;
1295 l_ci_type_id_from := cp.ci_type_id;
1296 end if ;
1297 close check_params;
1298
1299 if (x_return_status = 'S') then
1300 ----- include impacts
1301 pa_ci_impacts_util.copy_impact(p_validate_only => 'F',
1302 p_init_msg_list => 'F',
1303 P_DEST_CI_ID => p_from_ci_id,
1304 P_SOURCE_CI_ID => p_to_ci_id,
1305 P_INCLUDE_FLAG => 'Y',
1306 x_return_status => x_return_status,
1307 x_msg_count => x_msg_count,
1308 x_msg_data => x_msg_data);
1309
1310
1311 end if;
1312
1313 if (x_return_status = 'S') then
1314 PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
1315 p_user_id => fnd_global.user_id,
1316 p_object_type_from => 'PA_CONTROL_ITEMS',
1317 p_object_id_from1 => to_char(p_from_ci_id),
1318 p_object_id_from2 => NULL,
1319 p_object_id_from3 => NULL,
1320 p_object_id_from4 => NULL,
1321 p_object_id_from5 => NULL,
1322 p_object_type_to => 'PA_CONTROL_ITEMS',
1323 p_object_id_to1 => to_char(p_to_ci_id),
1324 p_object_id_to2 => NULL,
1325 p_object_id_to3 => NULL,
1326 p_object_id_to4 => NULL,
1327 p_object_id_to5 => NULL,
1328 p_relationship_type => l_relationship_type,
1329 p_relationship_subtype => NULL,
1330 p_lag_day => NULL,
1331 p_imported_lag => NULL,
1332 p_priority => NULL,
1333 p_pm_product_code => NULL,
1334 x_object_relationship_id => x_relationship_id,
1335 x_return_status => x_return_status);
1336 end if;
1337
1338
1339 if (x_return_status = 'S') then
1340 --------- change the status of CR from 'APPROVED' to 'CLOSED'
1341 SELECT record_version_number
1342 INTO l_record_version_number
1343 FROM PA_CONTROL_ITEMS
1344 WHERE ci_id = p_to_ci_id;
1345
1346 PA_CONTROL_ITEMS_UTILS.ChangeCIStatus (
1347 p_init_msg_list => FND_API.G_TRUE
1348 ,p_validate_only => FND_API.G_FALSE
1349 ,p_ci_id => p_to_ci_id
1350 ,p_status => 'CI_CLOSED'
1351 ,p_record_version_number => l_record_version_number
1352 ,x_num_of_actions => l_open_actions_num
1353 ,x_return_status => x_return_status
1354 ,x_msg_count => x_msg_count
1355 ,x_msg_data => x_msg_data);
1356
1357 end if;
1358
1359
1360 -- add code to copy the supplier information
1361 -- bug 2622062
1362 IF x_return_status = 'S' THEN
1363 PA_CI_SUPPLIER_UTILS.Merge_suppliers
1364 ( p_from_ci_item_id => p_to_ci_id
1365 ,p_to_ci_item_id => p_from_ci_id
1366 ,x_return_status => x_return_status
1367 ,x_error_msg => x_msg_data
1368 );
1369
1370 END IF;
1371
1372
1373 -- Commit if the flag is set and there is no error
1374 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
1375 COMMIT;
1376 END IF;
1377
1378 -- Reset the error stack when returning to the calling program
1379 PA_DEBUG.Reset_Err_Stack;
1380
1381
1382 EXCEPTION
1383 WHEN OTHERS THEN
1384 IF p_commit = FND_API.G_TRUE THEN
1385 ROLLBACK TO include_control_item;
1386 END IF;
1387
1388 -- Set the excetption Message and the stack
1389 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.include_control_item'
1390 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1391
1392
1393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1394 RAISE;
1395 end INCLUDE_CONTROL_ITEM;
1396
1397 procedure UPDATE_NUMBER_OF_ACTIONS (
1398 p_api_version IN NUMBER := 1.0
1399 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
1400 ,p_commit IN VARCHAR2 := FND_API.g_false
1401 ,p_validate_only IN VARCHAR2 := FND_API.g_true
1402 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
1403
1404 ,p_ci_id IN NUMBER
1405 ,p_num_of_actions IN NUMBER
1406 ,p_record_version_number IN NUMBER
1407
1408 ,x_num_of_actions OUT NOCOPY NUMBER
1409 ,x_return_status OUT NOCOPY VARCHAR2
1410 ,x_msg_count OUT NOCOPY NUMBER
1411 ,x_msg_data OUT NOCOPY VARCHAR2
1412 ,p_last_updated_by in NUMBER default fnd_global.user_id --Added the parameter for bug# 3877985
1413 ,p_last_update_date in DATE default sysdate --Added the parameter for bug# 3877985
1414 ,p_last_update_login in NUMBER default fnd_global.user_id --Added the parameter for bug# 3877985
1415 )IS
1416 l_nof_actions NUMBER(15) := 0;
1417
1418 cp pa_control_items%ROWTYPE;
1419
1420 CURSOR curr_number
1421 is
1422 SELECT * FROM pa_control_items
1423 WHERE ci_id = p_ci_id;
1424
1425 BEGIN
1426
1427 -- Initialize the Error Stack
1428 PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.UPDATE_NUMBER_OF_ACTIONS');
1429
1430 x_return_status := FND_API.G_RET_STS_SUCCESS;
1431 -- Issue API savepoint if the transaction is to be committed
1432 IF p_commit = FND_API.G_TRUE THEN
1433 SAVEPOINT UPDATE_NUMBER_OF_ACTIONS;
1434 END IF;
1435
1436 x_return_status := 'S';
1437 OPEN curr_number;
1438 FETCH curr_number INTO cp;
1439 if curr_number%NOTFOUND then
1440 PA_UTILS.Add_Message(
1441 p_app_short_name => 'PA'
1442 ,p_msg_name => 'PA_CI_INVALID_ITEM');
1443 x_return_status := 'E';
1444 else
1445 l_nof_actions := cp.open_action_num;
1446 end if ;
1447 close curr_number;
1448
1449 if (x_return_status = 'S') then
1450 if l_nof_actions is NULL or l_nof_actions < 0 then
1451 l_nof_actions := 0;
1452 end if;
1453 l_nof_actions := l_nof_actions + p_num_of_actions;
1454 if (l_nof_actions <0 ) then
1455 l_nof_actions := 0;
1456 end if;
1457 x_num_of_actions := l_nof_actions;
1458
1459 PA_CONTROL_ITEMS_PKG.UPDATE_ROW(
1460 p_ci_id
1461 ,cp.ci_type_id
1462 ,cp.summary
1463 ,cp.status_code
1464 ,cp.owner_id
1465 ,cp.highlighted_flag
1466 ,cp.progress_status_code
1467 ,cp.progress_as_of_date
1468 ,cp.classification_code_id
1469 ,cp.reason_code_id
1470 ,p_record_version_number
1471 ,cp.project_id
1472 ,cp.last_modified_by_id
1473 ,cp.object_type
1474 ,cp.object_id
1475 ,cp.ci_number
1476 ,cp.date_required
1477 ,cp.date_closed
1478 ,cp.closed_by_id
1479 ,cp.description
1480 ,cp.status_overview
1481 ,cp.resolution
1482 ,cp.resolution_code_id
1483 ,cp.priority_code
1484 ,cp.effort_level_code
1485 ,l_nof_actions --open_action_num
1486 ,cp.price
1487 ,cp.price_currency_code
1488 ,cp.source_type_code
1489 ,cp.source_comment
1490 ,cp.source_number
1491 ,cp.source_date_received
1492 ,cp.source_organization
1493 ,cp.source_person
1494
1495 ,cp.attribute_category
1496
1497 ,cp.attribute1
1498 ,cp.attribute2
1499 ,cp.attribute3
1500 ,cp.attribute4
1501 ,cp.attribute5
1502 ,cp.attribute6
1503 ,cp.attribute7
1504 ,cp.attribute8
1505 ,cp.attribute9
1506 ,cp.attribute10
1507 ,cp.attribute11
1508 ,cp.attribute12
1509 ,cp.attribute13
1510 ,cp.attribute14
1511 ,cp.attribute15
1512 ,x_return_status
1513 ,x_msg_count
1514 ,x_msg_data
1515 ,p_last_updated_by --Added for bug# 3877985
1516 ,p_last_update_date --Added for bug# 3877985
1517 ,p_last_update_login --Added for bug# 3877985
1518 );
1519 end if;
1520
1521
1522 -- Commit if the flag is set and there is no error
1523 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
1524 COMMIT;
1525 END IF;
1526
1527 -- Reset the error stack when returning to the calling program
1528 PA_DEBUG.Reset_Err_Stack;
1529
1530
1531 EXCEPTION
1532 WHEN OTHERS THEN
1533 IF p_commit = FND_API.G_TRUE THEN
1534 ROLLBACK TO UPDATE_NUMBER_OF_ACTIONS;
1535 END IF;
1536
1537 -- Set the excetption Message and the stack
1538 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.UPDATE_NUMBER_OF_ACTIONS'
1539 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1540
1541
1542 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1543 RAISE;
1544 end UPDATE_NUMBER_OF_ACTIONS;
1545
1546 procedure UPDATE_CONTROL_ITEM_STATUS (
1547 p_api_version IN NUMBER := 1.0
1548 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
1549 ,p_commit IN VARCHAR2 := FND_API.g_false
1550 ,p_validate_only IN VARCHAR2 := FND_API.g_true
1551 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
1552
1553 ,p_ci_id IN NUMBER
1554 ,p_status_code IN VARCHAR2
1555 ,p_record_version_number IN NUMBER
1556
1557 ,x_return_status OUT NOCOPY VARCHAR2
1558 ,x_msg_count OUT NOCOPY NUMBER
1559 ,x_msg_data OUT NOCOPY VARCHAR2
1560
1561 ) IS
1562
1563 API_ERROR EXCEPTION;
1564 cp pa_control_items%ROWTYPE;
1565
1566 CURSOR c_curr_item
1567 is
1568 SELECT * FROM pa_control_items
1569 WHERE ci_id = p_ci_id;
1570 l_curr_system_status_code pa_project_statuses.project_system_status_code%TYPE := NULL;
1571 l_new_system_status_code pa_project_statuses.project_system_status_code%TYPE := NULL;
1572 l_closed_date pa_control_items.date_closed%TYPE;
1573 l_closed_by pa_control_items.closed_by_id%TYPE;
1574 l_ci_number pa_control_items.ci_number%TYPE;
1575 l_ci_number_num NUMBER(15) := NULL;
1576 -- l_ci_number_char VARCHAR2(30) := NULL;
1577 l_ci_number_char PA_CONTROL_ITEMS.ci_number%type := NULL;
1578 l_auto_numbers VARCHAR2(1) := 'N';
1579 l_type_id PA_CI_TYPES_B.ci_type_id%TYPE;
1580 l_type_class PA_CI_TYPES_B.ci_type_class_code%TYPE;
1581 l_project_id PA_CONTROL_ITEMS.project_Id%TYPE;
1582 l_ci_id NUMBER;
1583
1584
1585 cursor c_auto_num is
1586 Select type.auto_number_flag, type.ci_type_id,type.ci_type_class_code
1587 From PA_CI_TYPES_B type
1588 ,pa_control_items ci
1589 Where ci.ci_id = p_ci_id
1590 AND ci.ci_type_id = type.ci_type_id;
1591
1592 BEGIN
1593
1594 -- Initialize the Error Stack
1595 PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.UPDATE_CONTROL_ITEM_STATUS');
1596
1597 x_return_status := FND_API.G_RET_STS_SUCCESS;
1598 -- Issue API savepoint if the transaction is to be committed
1599 IF p_commit = FND_API.G_TRUE THEN
1600 SAVEPOINT UPDATE_CONTROL_ITEM_STATUS;
1601 END IF;
1602
1603 x_return_status := 'S';
1604
1605 OPEN c_curr_item ;
1606 FETCH c_curr_item INTO cp;
1607 if c_curr_item%NOTFOUND then
1608 PA_UTILS.Add_Message(
1609 p_app_short_name => 'PA'
1610 ,p_msg_name => 'PA_CI_INVALID_ITEM');
1611 x_return_status := 'E';
1612 close c_curr_item;
1613 PA_DEBUG.Reset_Err_Stack;
1614 return;
1615 end if ;
1616 close c_curr_item;
1617
1618 OPEN c_auto_num;
1619 FETCH c_auto_num INTO l_auto_numbers, l_type_id, l_type_class;
1620 if c_auto_num%NOTFOUND then
1621 PA_UTILS.Add_Message(
1622 p_app_short_name => 'PA'
1623 ,p_msg_name => 'PA_CI_INVALID_TYPE_ID');
1624 x_return_status := 'E';
1625 close c_auto_num;
1626 PA_DEBUG.Reset_Err_Stack;
1627 return;
1628 end if ;
1629 close c_auto_num;
1630
1631
1632 l_closed_by := cp.closed_by_id;
1633 l_closed_date := cp.date_closed;
1634 l_ci_number_char := cp.ci_number;
1635 l_project_id := cp.project_id;
1636
1637 l_new_system_status_code := PA_CONTROL_ITEMS_UTILS.getSystemStatus(p_status_code);
1638 l_curr_system_status_code := PA_CONTROL_ITEMS_UTILS.getCISystemStatus(p_ci_id);
1639
1640 --Bug 4618856 Changes start here
1641
1642 if l_new_system_status_code is not NULL and l_new_system_status_code = 'CI_WORKING' then
1643 if l_curr_system_status_code is not null and l_curr_system_status_code = 'CI_DRAFT' then
1644 if l_ci_number_char is NULL AND l_auto_numbers <> 'Y' then
1645 PA_UTILS.Add_Message(
1646 p_app_short_name => 'PA'
1647 ,p_msg_name => 'PA_CI_NO_CI_NUMBER');
1648 x_return_status := 'E';
1649 PA_DEBUG.Reset_Err_Stack;
1650 return;
1651 end if;
1652 end if;
1653 end if;
1654 --Bug 4618856 Changes end here
1655
1656 if l_new_system_status_code is not NULL and l_new_system_status_code = 'CI_WORKING' then
1657 if l_curr_system_status_code is not null and l_curr_system_status_code = 'CI_DRAFT' then
1658 if l_ci_number_char is NULL AND l_auto_numbers = 'Y' then
1659 LOOP
1660 PA_SYSTEM_NUMBERS_PKG.GET_NEXT_NUMBER (
1661 p_object1_pk1_value => l_project_id
1662 ,p_object1_type => 'PA_PROJECTS'
1663 ,p_object2_pk1_value => l_type_id
1664 ,p_object2_type => l_type_class
1665 ,x_system_number_id => l_ci_id
1666 ,x_next_number => l_ci_number_num
1667 ,x_return_status => x_return_status
1668 ,x_msg_count => x_msg_count
1669 ,x_msg_data => x_msg_data);
1670
1671 IF x_return_status <> FND_API.g_ret_sts_success THEN
1672 PA_DEBUG.Reset_Err_Stack;
1673 raise API_ERROR;
1674 END IF;
1675 l_ci_number_char := TO_CHAR(l_ci_number_num);
1676 -- call Client Extension here
1677 PA_CI_NUMBER_CLIENT_EXTN.GET_NEXT_NUMBER (
1678 p_object1_pk1_value => l_project_id
1679 ,p_object1_type => 'PA_PROJECTS'
1680 ,p_object2_pk1_value => l_type_id
1681 ,p_object2_type => l_type_class
1682 ,p_next_number => l_ci_number_char
1683 ,x_return_status => x_return_status
1684 ,x_msg_count => x_msg_count
1685 ,x_msg_data => x_msg_data);
1686
1687
1688 EXIT WHEN ci_number_exists(l_project_id, l_ci_number_char
1689 ,l_type_id) = FALSE;
1690 END LOOP;
1691
1692 if l_ci_number_char is NULL THEN
1693 PA_UTILS.Add_Message(
1694 p_app_short_name => 'PA'
1695 ,p_msg_name => 'PA_CI_NO_CI_NUMBER');
1696 x_return_status := 'E';
1697 PA_DEBUG.Reset_Err_Stack;
1698 return;
1699 end if;
1700 end if ;
1701 end if;
1702 end if;
1703
1704 if l_new_system_status_code is not NULL and l_new_system_status_code = 'CI_CLOSED' then
1705 if l_curr_system_status_code is not null and l_curr_system_status_code <> 'CI_CLOSED' then
1706 -- IF PA_CI_ACTIONS_UTILS.CHECK_OPEN_ACTIONS_EXIST = 'Y' then
1707 -- PA_UTILS.Add_Message(
1708 -- p_app_short_name => 'PA'
1709 -- ,p_msg_name => 'PA_CI_OPEN_ACTION_EXISTS');--for SUBMIT!!!
1710 -- x_return_status := 'E';
1711 -- PA_DEBUG.Reset_Err_Stack;
1712 -- return;
1713 -- END IF;
1714 l_closed_by := PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id );
1715 l_closed_date := sysdate;
1716 end if;
1717 end if;
1718
1719 PA_CONTROL_ITEMS_PKG.UPDATE_ROW(
1720 p_ci_id
1721 ,cp.ci_type_id
1722 ,cp.summary
1723 ,p_status_code
1724 ,cp.owner_id
1725 ,cp.highlighted_flag
1726 ,cp.progress_status_code
1727 ,cp.progress_as_of_date
1728 ,cp.classification_code_id
1729 ,cp.reason_code_id
1730 ,p_record_version_number
1731 ,cp.project_id
1732 ,PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ) --cp.last_modified_by_id
1733 ,cp.object_type
1734 ,cp.object_id
1735 ,l_ci_number_char --cp.ci_number
1736 ,cp.date_required
1737 ,l_closed_date --cp.date_closed
1738 ,l_closed_by --cp.closed_by_id
1739 ,cp.description
1740 ,cp.status_overview
1741 ,cp.resolution
1742 ,cp.resolution_code_id
1743 ,cp.priority_code
1744 ,cp.effort_level_code
1745 ,cp.open_action_num
1746 ,cp.price
1747 ,cp.price_currency_code
1748 ,cp.source_type_code
1749 ,cp.source_comment
1750 ,cp.source_number
1751 ,cp.source_date_received
1752 ,cp.source_organization
1753 ,cp.source_person
1754
1755 ,cp.attribute_category
1756
1757 ,cp.attribute1
1758 ,cp.attribute2
1759 ,cp.attribute3
1760 ,cp.attribute4
1761 ,cp.attribute5
1762 ,cp.attribute6
1763 ,cp.attribute7
1764 ,cp.attribute8
1765 ,cp.attribute9
1766 ,cp.attribute10
1767 ,cp.attribute11
1768 ,cp.attribute12
1769 ,cp.attribute13
1770 ,cp.attribute14
1771 ,cp.attribute15
1772 ,x_return_status
1773 ,x_msg_count
1774 ,x_msg_data
1775 );
1776
1777
1778 -- Commit if the flag is set and there is no error
1779 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
1780 COMMIT;
1781 END IF;
1782
1783 -- Reset the error stack when returning to the calling program
1784 PA_DEBUG.Reset_Err_Stack;
1785
1786 EXCEPTION
1787 WHEN API_ERROR THEN
1788 x_return_status := x_return_status;
1789
1790 WHEN OTHERS THEN
1791 IF p_commit = FND_API.G_TRUE THEN
1792 ROLLBACK TO UPDATE_CONTROL_ITEM_STATUS;
1793 END IF;
1794
1795 -- Set the excetption Message and the stack
1796 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.UPDATE_CONTROL_ITEM_STATUS'
1797 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1798
1799
1800 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1801 RAISE;
1802 end UPDATE_CONTROL_ITEM_STATUS;
1803
1804 FUNCTION ASSIGN_CONTROL_ITEM_NUMBER(
1805 p_project_id IN NUMBER
1806 ,p_ci_type_id IN NUMBER
1807 ) RETURN VARCHAR2
1808
1809 IS
1810 l_new_number NUMBER(15) := NULL;
1811 l_rowid ROWID;
1812 l_ci_id NUMBER;
1813
1814 /*
1815 cursor C is select ROWID from PA_SYSTEM_NUMBERS
1816 where object1_pk1_value = p_project_id
1817 and nvl(object2_pk1_value,0) = nvl(p_ci_type_id,0)
1818 and object1_type = p_object_type
1819 and object2_type = p_ci_type_code;
1820 */
1821 BEGIN
1822 NULL;-- RETURN PA_SYSTEM_NUMBERS_PKG.get_next_number(;
1823 end ASSIGN_CONTROL_ITEM_NUMBER;
1824 --
1825 FUNCTION has_null_data (
1826 p_ci_type_id IN NUMBER
1827 ,p_project_id IN NUMBER
1828 ,p_status_code IN VARCHAR2
1829 ,p_owner_id IN NUMBER
1830 ,p_summary IN VARCHAR2
1831
1832 )
1833 RETURN BOOLEAN
1834 IS
1835 l_null_data BOOLEAN := FALSE;
1836 BEGIN
1837
1838 IF p_ci_type_id is NULL THEN
1839 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1840 ,p_msg_name => 'PA_CI_NO_TYPE');
1841 l_null_data := TRUE;
1842 END IF;
1843 IF p_project_id is NULL THEN
1844 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1845 ,p_msg_name => 'PA_CI_NO_PROJECT_ID');
1846 l_null_data := TRUE;
1847 END IF;
1848 IF p_status_code is NULL THEN
1849 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1850 ,p_msg_name => 'PA_CI_NO_STATUS');
1851 l_null_data := TRUE;
1852 END IF;
1853 IF p_owner_id is NULL THEN
1854 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1855 ,p_msg_name => 'PA_CI_NO_OWNER');
1856 l_null_data := TRUE;
1857 END IF;
1858
1859 IF p_summary is NULL THEN
1860 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1861 ,p_msg_name => 'PA_CI_NO_SUMMARY');
1862 l_null_data := TRUE;
1863 END IF;
1864
1865 RETURN l_null_data;
1866
1867 EXCEPTION
1868 WHEN OTHERS THEN
1869 -- Set the exception Message and the stack
1870 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.has_null_data'
1871 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1872
1873 RAISE;
1874
1875 END has_null_data;
1876
1877 -- checks whether a control item number already exists for this project/control item type
1878
1879 FUNCTION ci_number_exists(p_project_id IN NUMBER
1880 ,p_ci_number IN VARCHAR2
1881 ,p_ci_type_id IN NUMBER)
1882
1883 RETURN BOOLEAN
1884 IS
1885 l_ROWID ROWID;
1886 cursor C is select ROWID from PA_CONTROL_ITEMS
1887 where project_id = p_project_id
1888 and ci_number = p_ci_number
1889 and ci_type_id = p_ci_type_id;
1890
1891 BEGIN
1892 if p_ci_number is NULL then
1893 return FALSE;
1894 end if;
1895
1896 open C;
1897 fetch C into l_ROWID;
1898 if (C%notfound) then
1899 close C;
1900 return FALSE;
1901 else
1902 close C;
1903 return TRUE;
1904 end if;
1905
1906 EXCEPTION
1907 WHEN OTHERS THEN
1908 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1909 RAISE;
1910 END ci_number_exists;
1911
1912 -- Validates pa_lookups which has p_lookup_type and p_lookup_code
1913 --
1914 FUNCTION is_lookup_valid (p_lookup_type IN VARCHAR2
1915 ,p_lookup_code IN VARCHAR2)
1916 RETURN BOOLEAN
1917 IS
1918 l_meaning VARCHAR2(80);
1919 BEGIN
1920
1921 SELECT meaning
1922 INTO l_meaning
1923 FROM pa_lookups
1924 WHERE lookup_type = p_lookup_type
1925 AND lookup_code = p_lookup_code;
1926
1927 return TRUE;
1928
1929 EXCEPTION
1930 WHEN NO_DATA_FOUND THEN
1931 return FALSE;
1932 WHEN OTHERS THEN
1933 -- Set the exception Message and the stack
1934 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.is_lookup_valid'
1935 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1936
1937 RAISE;
1938
1939 END is_lookup_valid;
1940
1941 PROCEDURE change_included_cr_status(p_ci_id IN NUMBER
1942 ,x_return_status OUT NOCOPY VARCHAR2
1943 ,x_msg_count OUT NOCOPY NUMBER
1944 ,x_msg_data OUT NOCOPY VARCHAR2)
1945 IS
1946 l_open_actions_num NUMBER;
1947
1948 CURSOR items_c IS
1949 SELECT obj.object_id_to1 included_ci_id
1950 ,ci.record_version_number record_version_number
1951 FROM pa_object_relationships obj, pa_control_items ci
1952 WHERE obj.object_type_from = 'PA_CONTROL_ITEMS'
1953 AND obj.object_type_to = 'PA_CONTROL_ITEMS'
1954 AND obj.relationship_type = 'CI_INCLUDED_ITEM'
1955 AND obj.object_id_from1 = p_ci_id
1956 AND obj.object_id_to1 = ci.ci_id;
1957 BEGIN
1958
1959 x_return_status := FND_API.G_RET_STS_SUCCESS;
1960
1961 FOR cur in items_c LOOP
1962 PA_CONTROL_ITEMS_UTILS.ChangeCIStatus (
1963 p_init_msg_list => FND_API.G_TRUE
1964 ,p_validate_only => FND_API.G_FALSE
1965 ,p_ci_id => cur.included_ci_id
1966 ,p_status => 'CI_APPROVED'
1967 ,p_record_version_number => cur.record_version_number
1968 ,x_num_of_actions => l_open_actions_num
1969 ,x_return_status => x_return_status
1970 ,x_msg_count => x_msg_count
1971 ,x_msg_data => x_msg_data);
1972
1973 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1974 PA_UTILS.Add_Message (p_app_short_name => 'PA'
1975 ,p_msg_name => x_msg_data);
1976 RAISE FND_API.G_EXC_ERROR;
1977 END IF;
1978 END LOOP;
1979
1980 END change_included_cr_status;
1981
1982
1983 PROCEDURE add_related_item (
1984 p_api_version IN NUMBER := 1.0,
1985 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
1986 p_commit IN VARCHAR2 := FND_API.g_false,
1987 p_validate_only IN VARCHAR2 := FND_API.g_true,
1988 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
1989 p_ci_id IN NUMBER,
1990 p_related_ci_id IN NUMBER,
1991 x_return_status OUT NOCOPY VARCHAR2,
1992 x_msg_count OUT NOCOPY NUMBER,
1993 x_msg_data OUT NOCOPY VARCHAR2
1994 )
1995 IS
1996 l_object_relationship_id NUMBER;
1997 BEGIN
1998 pa_debug.set_err_stack ('PA_CONTROL_ITEMS_PVT.ADD_RELATED_ITEM');
1999
2000 IF p_commit = FND_API.G_TRUE THEN
2001 SAVEPOINT add_related_item;
2002 END IF;
2003
2004 IF p_init_msg_list = FND_API.G_TRUE THEN
2005 fnd_msg_pub.initialize;
2006 END IF;
2007
2008 x_return_status := 'S';
2009 x_msg_count := 0;
2010 x_msg_data := '';
2011
2012 IF p_validate_only = FND_API.G_TRUE THEN
2013 RETURN;
2014 END IF;
2015
2016 PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
2017 p_user_id => fnd_global.user_id,
2018 p_object_type_from => 'PA_CONTROL_ITEMS',
2019 p_object_id_from1 => to_char(p_ci_id),
2020 p_object_id_from2 => NULL,
2021 p_object_id_from3 => NULL,
2022 p_object_id_from4 => NULL,
2023 p_object_id_from5 => NULL,
2024 p_object_type_to => 'PA_CONTROL_ITEMS',
2025 p_object_id_to1 => to_char(p_related_ci_id),
2026 p_object_id_to2 => NULL,
2027 p_object_id_to3 => NULL,
2028 p_object_id_to4 => NULL,
2029 p_object_id_to5 => NULL,
2030 p_relationship_type => 'CI_REFERENCED_ITEM',
2031 p_relationship_subtype => NULL,
2032 p_lag_day => NULL,
2033 p_imported_lag => NULL,
2034 p_priority => NULL,
2035 p_pm_product_code => NULL,
2036 x_object_relationship_id => l_object_relationship_id,
2037 x_return_status => x_return_status);
2038
2039 IF p_commit = fnd_api.g_true THEN
2040 IF x_return_status = 'S' THEN
2041 COMMIT;
2042 ELSE
2043 ROLLBACK TO add_related_item;
2044 END IF;
2045 END IF;
2046
2047 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2048 p_data => x_msg_data);
2049
2050 pa_debug.reset_err_stack;
2051
2052 EXCEPTION
2053 WHEN OTHERS THEN
2054 IF p_commit = fnd_api.g_true THEN
2055 ROLLBACK TO add_related_item;
2056 END IF;
2057
2058 x_return_status := 'U';
2059 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_PVT',
2060 p_procedure_name => 'ADD_RELATED_ITEM',
2061 p_error_text => SUBSTRB(SQLERRM,1,240));
2062
2063 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2064 p_data => x_msg_data);
2065 END add_related_item;
2066
2067
2068 PROCEDURE delete_related_item (
2069 p_api_version IN NUMBER := 1.0,
2070 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
2071 p_commit IN VARCHAR2 := FND_API.g_false,
2072 p_validate_only IN VARCHAR2 := FND_API.g_true,
2073 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
2074 p_ci_id IN NUMBER,
2075 p_related_ci_id IN NUMBER,
2076 x_return_status OUT NOCOPY VARCHAR2,
2077 x_msg_count OUT NOCOPY NUMBER,
2078 x_msg_data OUT NOCOPY VARCHAR2
2079 )
2080 IS
2081 l_object_relationship_id NUMBER;
2082 l_record_version_number NUMBER;
2083 BEGIN
2084 pa_debug.set_err_stack ('PA_CONTROL_ITEMS_PVT.DELETE_RELATED_ITEM');
2085
2086 IF p_commit = FND_API.G_TRUE THEN
2087 SAVEPOINT delete_related_item;
2088 END IF;
2089
2090 IF p_init_msg_list = FND_API.G_TRUE THEN
2091 fnd_msg_pub.initialize;
2092 END IF;
2093
2094 x_return_status := 'S';
2095 x_msg_count := 0;
2096 x_msg_data := '';
2097
2098 SELECT object_relationship_id, record_version_number
2099 INTO l_object_relationship_id, l_record_version_number
2100 FROM pa_object_relationships
2101 WHERE object_type_from = 'PA_CONTROL_ITEMS'
2102 AND relationship_type = 'CI_REFERENCED_ITEM'
2103 AND object_type_to = 'PA_CONTROL_ITEMS'
2104 AND ( ( object_id_to1 = p_related_ci_id
2105 AND object_id_from1 = p_ci_id)
2106 OR ( object_id_to1 = p_ci_id
2107 AND object_id_from1 = p_related_ci_id));
2108
2109 IF p_validate_only = FND_API.G_TRUE THEN
2110 RETURN;
2111 END IF;
2112
2113 pa_object_relationships_pkg.delete_row(
2114 p_object_relationship_id => l_object_relationship_id,
2115 p_object_type_from => 'PA_CONTROL_ITEMS',
2116 p_object_id_from1 => to_char(p_ci_id),
2117 p_object_id_from2 => NULL,
2118 p_object_id_from3 => NULL,
2119 p_object_id_from4 => NULL,
2120 p_object_id_from5 => NULL,
2121 p_object_type_to => 'PA_CONTROL_ITEMS',
2122 p_object_id_to1 => to_char(p_related_ci_id),
2123 p_object_id_to2 => NULL,
2124 p_object_id_to3 => NULL,
2125 p_object_id_to4 => NULL,
2126 p_object_id_to5 => NULL,
2127 p_pm_product_code => NULL,
2128 p_record_version_number => l_record_version_number,
2129 x_return_status => x_return_status);
2130
2131 IF p_commit = fnd_api.g_true THEN
2132 IF x_return_status = 'S' THEN
2133 COMMIT;
2134 ELSE
2135 ROLLBACK TO delete_related_item;
2136 END IF;
2137 END IF;
2138
2139 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2140 p_data => x_msg_data);
2141
2142 pa_debug.reset_err_stack;
2143
2144 EXCEPTION
2145 WHEN OTHERS THEN
2146 IF p_commit = fnd_api.g_true THEN
2147 ROLLBACK TO delete_related_item;
2148 END IF;
2149
2150 x_return_status := 'U';
2151 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_PVT',
2152 p_procedure_name => 'DELETE_RELATED_ITEM',
2153 p_error_text => SUBSTRB(SQLERRM,1,240));
2154
2155 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2156 p_data => x_msg_data);
2157 END delete_related_item;
2158
2159 PROCEDURE delete_all_related_items (
2160 p_api_version IN NUMBER := 1.0,
2161 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
2162 p_commit IN VARCHAR2 := FND_API.g_false,
2163 p_validate_only IN VARCHAR2 := FND_API.g_true,
2164 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
2165 p_ci_id IN NUMBER,
2166 x_return_status OUT NOCOPY VARCHAR2,
2167 x_msg_count OUT NOCOPY NUMBER,
2168 x_msg_data OUT NOCOPY VARCHAR2
2169 )
2170 IS
2171 CURSOR items_c IS
2172 SELECT object_id_to1 related_ci_id
2173 FROM pa_object_relationships
2174 WHERE object_type_from = 'PA_CONTROL_ITEMS'
2175 AND object_type_to = 'PA_CONTROL_ITEMS'
2176 AND relationship_type = 'CI_REFERENCED_ITEM'
2177 AND object_id_from1 = p_ci_id
2178 UNION ALL
2179 SELECT object_id_from1 related_ci_id
2180 FROM pa_object_relationships
2181 WHERE object_type_from = 'PA_CONTROL_ITEMS'
2182 AND object_type_to = 'PA_CONTROL_ITEMS'
2183 AND relationship_type = 'CI_REFERENCED_ITEM'
2184 AND object_id_to1 = p_ci_id;
2185
2186
2187
2188 BEGIN
2189 pa_debug.set_err_stack ('PA_CONTROL_ITEMS_PVT.DELETE_ALL_RELATED_ITEMS');
2190
2191 IF p_commit = FND_API.G_TRUE THEN
2192 SAVEPOINT delete_all_related_items;
2193 END IF;
2194
2195 IF p_init_msg_list = FND_API.G_TRUE THEN
2196 fnd_msg_pub.initialize;
2197 END IF;
2198
2199 x_return_status := 'S';
2200 x_msg_count := 0;
2201 x_msg_data := '';
2202
2203 IF p_validate_only = FND_API.G_TRUE THEN
2204 RETURN;
2205 END IF;
2206
2207 FOR cur in items_c LOOP
2208 delete_related_item(
2209 p_init_msg_list => FND_API.G_FALSE,
2210 p_commit => FND_API.G_FALSE,
2211 p_validate_only => FND_API.G_FALSE,
2212 p_max_msg_count => p_max_msg_count,
2213 p_ci_id => p_ci_id,
2214 p_related_ci_id => cur.related_ci_id,
2215 x_return_status => x_return_status,
2216 x_msg_count => x_msg_count,
2217 x_msg_data => x_msg_data);
2218 END LOOP;
2219
2220 IF p_commit = fnd_api.g_true THEN
2221 IF x_return_status = 'S' THEN
2222 COMMIT;
2223 ELSE
2224 ROLLBACK TO delete_all_related_items;
2225 END IF;
2226 END IF;
2227
2228 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2229 p_data => x_msg_data);
2230
2231 pa_debug.reset_err_stack;
2232
2233 EXCEPTION
2234 WHEN OTHERS THEN
2235 IF p_commit = fnd_api.g_true THEN
2236 ROLLBACK TO delete_all_related_items;
2237 END IF;
2238
2239 x_return_status := 'U';
2240 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_PVT',
2241 p_procedure_name => 'DELETE_ALL_RELATED_ITEMS',
2242 p_error_text => SUBSTRB(SQLERRM,1,240));
2243
2244 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2245 p_data => x_msg_data);
2246 END delete_all_related_items;
2247
2248
2249 PROCEDURE delete_all_included_crs (
2250 p_api_version IN NUMBER := 1.0,
2251 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
2252 p_commit IN VARCHAR2 := FND_API.g_false,
2253 p_validate_only IN VARCHAR2 := FND_API.g_true,
2254 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
2255 p_ci_id IN NUMBER,
2256 x_return_status OUT NOCOPY VARCHAR2,
2257 x_msg_count OUT NOCOPY NUMBER,
2258 x_msg_data OUT NOCOPY VARCHAR2
2259 )
2260 IS
2261 CURSOR items_c IS
2262 SELECT object_relationship_id, object_id_to1, record_version_number
2263 FROM pa_object_relationships
2264 WHERE object_type_from = 'PA_CONTROL_ITEMS'
2265 AND object_type_to = 'PA_CONTROL_ITEMS'
2266 AND relationship_type = 'CI_INCLUDED_ITEM'
2267 AND object_id_from1 = p_ci_id;
2268
2269 BEGIN
2270 pa_debug.set_err_stack ('PA_CONTROL_ITEMS_PVT.DELETE_ALL_INCLUDED_CRS');
2271
2272 IF p_commit = FND_API.G_TRUE THEN
2273 SAVEPOINT delete_all_included_crs;
2274 END IF;
2275
2276 IF p_init_msg_list = FND_API.G_TRUE THEN
2277 fnd_msg_pub.initialize;
2278 END IF;
2279
2280 x_return_status := 'S';
2281 x_msg_count := 0;
2282 x_msg_data := '';
2283
2284 IF p_validate_only = FND_API.G_TRUE THEN
2285 RETURN;
2286 END IF;
2287
2288 FOR cur in items_c LOOP
2289
2290 pa_object_relationships_pkg.delete_row(
2291 p_object_relationship_id => cur.object_relationship_id,
2292 p_object_type_from => 'PA_CONTROL_ITEMS',
2293 p_object_id_from1 => to_char(p_ci_id),
2294 p_object_id_from2 => NULL,
2295 p_object_id_from3 => NULL,
2296 p_object_id_from4 => NULL,
2297 p_object_id_from5 => NULL,
2298 p_object_type_to => 'PA_CONTROL_ITEMS',
2299 p_object_id_to1 => to_char(cur.object_id_to1),
2300 p_object_id_to2 => NULL,
2301 p_object_id_to3 => NULL,
2302 p_object_id_to4 => NULL,
2303 p_object_id_to5 => NULL,
2304 p_pm_product_code => NULL,
2305 p_record_version_number => cur.record_version_number,
2306 x_return_status => x_return_status);
2307
2308 END LOOP;
2309
2310 IF p_commit = fnd_api.g_true THEN
2311 IF x_return_status = 'S' THEN
2312 COMMIT;
2313 ELSE
2314 ROLLBACK TO delete_all_included_crs;
2315 END IF;
2316 END IF;
2317
2318 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2319 p_data => x_msg_data);
2320
2321 pa_debug.reset_err_stack;
2322
2323 EXCEPTION
2324 WHEN OTHERS THEN
2325 IF p_commit = fnd_api.g_true THEN
2326 ROLLBACK TO delete_all_included_crs;
2327 END IF;
2328
2329 x_return_status := 'U';
2330 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_PVT',
2331 p_procedure_name => 'DELETE_ALL_INCLUDED_CRS',
2332 p_error_text => SUBSTRB(SQLERRM,1,240));
2333
2334 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2335 p_data => x_msg_data);
2336 END delete_all_included_crs;
2337
2338 PROCEDURE copy_related_items (
2339 p_api_version IN NUMBER := 1.0,
2340 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
2341 p_commit IN VARCHAR2 := FND_API.g_false,
2342 p_validate_only IN VARCHAR2 := FND_API.g_true,
2343 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
2344 p_from_ci_id IN NUMBER,
2345 p_to_ci_id IN NUMBER,
2346 x_return_status OUT NOCOPY VARCHAR2,
2347 x_msg_count OUT NOCOPY NUMBER,
2348 x_msg_data OUT NOCOPY VARCHAR2
2349 )
2350 IS
2351 CURSOR items_c IS
2352 SELECT object_id_to1 related_ci_id
2353 FROM pa_object_relationships
2354 WHERE object_type_from = 'PA_CONTROL_ITEMS'
2355 AND object_type_to = 'PA_CONTROL_ITEMS'
2356 AND relationship_type = 'CI_REFERENCED_ITEM'
2357 AND object_id_from1 = p_from_ci_id
2358 UNION ALL
2359 SELECT object_id_from1 related_ci_id
2360 FROM pa_object_relationships
2361 WHERE object_type_from = 'PA_CONTROL_ITEMS'
2362 AND object_type_to = 'PA_CONTROL_ITEMS'
2363 AND relationship_type = 'CI_REFERENCED_ITEM'
2364 AND object_id_to1 = p_from_ci_id;
2365
2366
2367 BEGIN
2368 pa_debug.set_err_stack ('PA_CONTROL_ITEMS_PVT.COPY_RELATED_ITEMS');
2369
2370 IF p_commit = FND_API.G_TRUE THEN
2371 SAVEPOINT copy_related_items;
2372 END IF;
2373
2374 IF p_init_msg_list = FND_API.G_TRUE THEN
2375 fnd_msg_pub.initialize;
2376 END IF;
2377
2378 x_return_status := 'S';
2379 x_msg_count := 0;
2380 x_msg_data := '';
2381
2382 IF p_validate_only = FND_API.G_TRUE THEN
2383 RETURN;
2384 END IF;
2385
2386 FOR cur in items_c LOOP
2387 add_related_item(
2388 p_init_msg_list => FND_API.G_FALSE,
2389 p_commit => FND_API.G_FALSE,
2390 p_validate_only => FND_API.G_FALSE,
2391 p_max_msg_count => p_max_msg_count,
2392 p_ci_id => p_to_ci_id,
2393 p_related_ci_id => cur.related_ci_id,
2394 x_return_status => x_return_status,
2395 x_msg_count => x_msg_count,
2396 x_msg_data => x_msg_data);
2397 END LOOP;
2398
2399 IF p_commit = fnd_api.g_true THEN
2400 IF x_return_status = 'S' THEN
2401 COMMIT;
2402 ELSE
2403 ROLLBACK TO copy_related_items;
2404 END IF;
2405 END IF;
2406
2407 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2408 p_data => x_msg_data);
2409
2410 pa_debug.reset_err_stack;
2411
2412 EXCEPTION
2413 WHEN OTHERS THEN
2414 IF p_commit = fnd_api.g_true THEN
2415 ROLLBACK TO copy_related_items;
2416 END IF;
2417
2418 x_return_status := 'U';
2419 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_PVT',
2420 p_procedure_name => 'COPY_RELATED_ITEMS',
2421 p_error_text => SUBSTRB(SQLERRM,1,240));
2422
2423 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2424 p_data => x_msg_data);
2425 END copy_related_items;
2426
2427
2428 END PA_CONTROL_ITEMS_PVT;