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