[Home] [Help]
PACKAGE BODY: APPS.PA_CONTROL_ITEMS_PUB
Source
1 PACKAGE BODY PA_CONTROL_ITEMS_PUB AS
2 --$Header: PACICIPB.pls 120.9.12020000.2 2012/07/19 09:28:29 admarath ship $
3
4
5 procedure getPartyIdFromName(
6 p_project_id IN NUMBER
7 ,p_name IN VARCHAR2
8 ,x_party_id OUT NOCOPY NUMBER
9 ,x_return_status OUT NOCOPY VARCHAR2
10 ,x_msg_count OUT NOCOPY NUMBER
11 ,x_msg_data OUT NOCOPY VARCHAR2
12 ) is
13
14 l_party_id NUMBER := NULL;
15
16 BEGIN
17 x_return_status := 'S';
18 BEGIN
19 SELECT DISTINCT ppp.resource_source_id party_id
20 INTO l_party_id
21 FROM hz_parties hzp,
22 pa_project_parties ppp
23 WHERE hzp.party_name = p_name
24 AND hzp.party_type = 'PERSON'
25 AND ppp.resource_source_id = hzp.party_id
26 AND ppp.resource_type_id = 112
27 AND ppp.project_id = p_project_id
28 AND (TRUNC(SYSDATE) BETWEEN TRUNC(ppp.start_date_active)
29 AND TRUNC(NVL(ppp.end_date_active, SYSDATE)));
30
31
32 EXCEPTION
33 when TOO_MANY_ROWS then
34 PA_UTILS.Add_Message( p_app_short_name => 'PA'
35 ,p_msg_name => 'PA_OWNER_NAME_MULTIPLE');
36 x_return_status := 'E';
37 when NO_DATA_FOUND then
38 l_party_id := NULL;
39 when OTHERS then
40 PA_UTILS.Add_Message( p_app_short_name => 'PA'
41 ,p_msg_name => 'PA_CI_NO_OWNER');
42 x_return_status := 'E';
43 END;
44
45 IF (x_return_status = 'S' AND l_party_id is NULL) THEN
46 SELECT DISTINCT ppf.party_id party_id
47 INTO l_party_id
48 FROM pa_project_parties ppp,
49 per_all_people_f ppf
50 WHERE ppf.full_name = p_name
51 AND (TRUNC(SYSDATE) BETWEEN TRUNC(ppf.effective_start_date)
52 AND TRUNC(ppf.effective_end_date))
53 AND ppp.resource_source_id = ppf.person_id
54 AND ppp.resource_type_id = 101
55 AND ppp.project_id = p_project_id
56 AND (TRUNC(SYSDATE) BETWEEN TRUNC(ppp.start_date_active)
57 AND TRUNC(NVL(ppp.end_date_active, SYSDATE)));
58 END IF;
59
60 x_party_id := l_party_id;
61
62 exception when TOO_MANY_ROWS then
63 PA_UTILS.Add_Message( p_app_short_name => 'PA'
64 ,p_msg_name => 'PA_OWNER_NAME_MULTIPLE');
65 x_return_status := 'E';
66
67 when OTHERS then
68 PA_UTILS.Add_Message( p_app_short_name => 'PA'
69 ,p_msg_name => 'PA_CI_NO_OWNER');
70 x_return_status := 'E';
71
72 end getPartyIdFromName;
73
74
75 procedure ADD_CONTROL_ITEM(
76 p_api_version IN NUMBER := 1.0
77 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
78 ,p_commit IN VARCHAR2 := FND_API.g_false
79 ,p_validate_only IN VARCHAR2 := FND_API.g_true
80 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
81
82 ,p_ci_type_id IN NUMBER
83 ,p_summary IN VARCHAR2
84 ,p_status_code IN VARCHAR2
85 ,p_owner_id IN NUMBER := NULL
86 ,p_owner_name IN VARCHAR2 := NULL
87 ,p_highlighted_flag IN VARCHAR2 := 'N'
88 ,p_progress_status_code IN VARCHAR2 := NULL
89 ,p_progress_as_of_date IN DATE := NULL
90 ,p_classification_code IN NUMBER
91 ,p_reason_code IN NUMBER
92 ,p_project_id IN NUMBER
93 ,p_object_type IN VARCHAR2 := NULL
94 ,p_object_id IN NUMBER := NULL
95 ,p_object_name IN VARCHAR2 := NULL
96 ,p_ci_number IN VARCHAR2 := NULL
97 ,p_date_required IN DATE := NULL
98 ,p_date_closed IN DATE := NULL
99 ,p_closed_by_id IN NUMBER := NULL
100
101
102 ,p_description IN VARCHAR2 := NULL
103 ,p_status_overview IN VARCHAR2 := NULL
104 ,p_resolution IN VARCHAR2 := NULL
105 ,p_resolution_code IN NUMBER := NULL
106 ,p_priority_code IN VARCHAR2 := NULL
107 ,p_effort_level_code IN VARCHAR2 := NULL
108 ,p_open_action_num IN NUMBER := NULL
109
110 ,p_price IN NUMBER := NULL
111 ,p_price_currency_code IN VARCHAR2 := NULL
112 ,p_source_type_code IN VARCHAR2 := NULL
113 ,p_source_comment IN VARCHAR2 := NULL
114 ,p_source_number IN VARCHAR2 := NULL
115 ,p_source_date_received IN DATE := NULL
116 ,p_source_organization IN VARCHAR2 := NULL
117 ,p_source_person IN VARCHAR2 := NULL
118 ,p_attribute_category IN VARCHAR2 := NULL
119
120 ,p_attribute1 IN VARCHAR2 := NULL
121 ,p_attribute2 IN VARCHAR2 := NULL
122 ,p_attribute3 IN VARCHAR2 := NULL
123 ,p_attribute4 IN VARCHAR2 := NULL
124 ,p_attribute5 IN VARCHAR2 := NULL
125 ,p_attribute6 IN VARCHAR2 := NULL
126 ,p_attribute7 IN VARCHAR2 := NULL
127 ,p_attribute8 IN VARCHAR2 := NULL
128 ,p_attribute9 IN VARCHAR2 := NULL
129 ,p_attribute10 IN VARCHAR2 := NULL
130 ,p_attribute11 IN VARCHAR2 := NULL
131 ,p_attribute12 IN VARCHAR2 := NULL
132 ,p_attribute13 IN VARCHAR2 := NULL
133 ,p_attribute14 IN VARCHAR2 := NULL
134 ,p_attribute15 IN VARCHAR2 := NULL
135
136 -- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
137 ,p_PCO_STATUS_CODE IN VARCHAR2 := NULL
138 ,p_APPROVAL_TYPE_CODE IN VARCHAR2 := NULL
139 ,p_LOCKED_FLAG IN VARCHAR2 := 'N'
140 -- end: 26-Jun-2009 cklee Modified for the Bug# 8633676
141
142 ,p_Version_number IN number := null
143 ,p_Current_Version_flag IN varchar2 := 'Y'
144 ,p_Version_Comments IN varchar2 := NULL
145 ,p_Original_ci_id IN number := NULL
146 ,p_Source_ci_id IN number := NULL
147
148 ,px_ci_id IN OUT NOCOPY NUMBER
149 ,x_ci_number OUT NOCOPY VARCHAR2
150 ,x_return_status OUT NOCOPY VARCHAR2
151 ,x_msg_count OUT NOCOPY NUMBER
152 ,x_msg_data OUT NOCOPY VARCHAR2
153 ) is
154
155 l_msg_index_out NUMBER;
156
157 -- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
158 l_last_modified_by_id NUMBER := NVL(PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ), fnd_global.user_id);
159 -- end: 26-Jun-2009 cklee Modified for the Bug# 8633676
160 l_owner_id NUMBER := NULL;
161 l_object_id NUMBER;
162 l_debug_mode VARCHAR2(1);
163 l_debug_level6 CONSTANT NUMBER := 6;
164 g_module_name VARCHAR2(100) := 'pa.plsql.CreateCI,Add_Control_Item';
165 l_classification_code NUMBER; /*Bug 4049588*/
166 l_reason_code NUMBER; /* Bug 4049588*/
167
168 BEGIN
169
170 -- Initialize the Error Stack
171 PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PUB.Add_Control_Item');
172
173 -- Initialize the return status to success
174 x_return_status := FND_API.G_RET_STS_SUCCESS;
175
176 --Clear the global PL/SQL message table
177 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
178 FND_MSG_PUB.initialize;
179 END IF;
180 --get hz_parties.party_id of the logged in user
181 -- l_last_modified_by_id := nvl(PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ), fnd_global.user_id);
182 -- 26-Jun-2009 cklee Modified for the Bug# 8633676
183 l_owner_id := p_owner_id;
184 l_object_id := p_object_id;
185
186
187 -- check mandatory owner_id
188 IF (l_owner_id IS NULL) THEN
189 IF (p_owner_name is not null) then
190 getPartyIdFromName(
191 p_project_id => p_project_id
192 ,p_name => p_owner_name
193 ,x_party_id => l_owner_id
194 ,x_return_status => x_return_status
195 ,x_msg_count => x_msg_count
196 ,x_msg_data => x_msg_data);
197 ELSE
198 PA_UTILS.Add_Message( p_app_short_name => 'PA'
199 ,p_msg_name => 'PA_CI_NO_OWNER');
200 x_return_status := 'E';
201 END IF;
202 END IF;
203
204 --Bug 4049588. Check if Classification Code and Reason Code are null.
205 l_classification_code := p_classification_code ;
206 l_reason_code := p_reason_code;
207 IF (l_classification_code IS NULL OR l_reason_code IS NULL )
208 THEN
209 IF (l_classification_code IS NULL) THEN
210 PA_UTILS.Add_Message( p_app_short_name => 'PA'
211 ,p_msg_name => 'PA_CI_NO_CLASSIFICATION_CODE');
212 x_return_status := 'E';
213 END IF;
214 IF (l_reason_code IS NULL) THEN
215 PA_UTILS.Add_Message( p_app_short_name => 'PA'
216 ,p_msg_name => 'PA_CI_NO_REASON_CODE');
217 x_return_status := 'E';
218 END IF;
219 END IF;
220
221
222 IF (l_object_id IS NULL AND p_object_name is not null) THEN
223 -- try to get object id from name - as of now we're only handling PA_TASKS objects
224 begin
225 select proj_element_id
226 into l_object_id
227 from PA_FIN_LATEST_PUB_TASKS_V
228 where element_name = p_object_name
229 and project_id = p_project_id;
230
231 exception when TOO_MANY_ROWS then
232 PA_UTILS.Add_Message( p_app_short_name => 'PA'
233 ,p_msg_name => 'PA_OBJECT_NAME_MULTIPLE');
234 x_return_status := 'E';
235
236 when OTHERS then
237 PA_UTILS.Add_Message( p_app_short_name => 'PA'
238 ,p_msg_name => 'PA_OBJECT_NAME_INV');
239 x_return_status := 'E';
240 end;
241
242 END IF;
243
244
245 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
246 IF l_debug_mode = 'Y' THEN
247 pa_debug.g_err_stage:= 'About to call the private method';
248 pa_debug.write(g_module_name,pa_debug.g_err_stage,
249 l_debug_level6);
250 END IF;
251
252 IF (x_return_status <> 'E')THEN
253 PA_CONTROL_ITEMS_PVT.ADD_CONTROL_ITEM(
254 p_api_version
255 ,p_init_msg_list
256 ,p_commit
257 ,p_validate_only
258 ,p_max_msg_count
259
260 ,p_ci_type_id
261 ,p_summary
262 ,p_status_code
263 ,l_owner_id
264 ,nvl(p_highlighted_flag,'N')
265 ,p_progress_status_code
266 ,p_progress_as_of_date
267 ,p_classification_code
268 ,p_reason_code
269 ,p_project_id
270 ,l_last_modified_by_id
271 ,p_object_type
272 ,l_object_id
273 ,p_ci_number
274 ,p_date_required
275 ,p_date_closed
276 ,p_closed_by_id
277
278 ,p_description
279 ,p_status_overview
280 ,p_resolution
281 ,p_resolution_code
282 ,p_priority_code
283 ,p_effort_level_code
284 ,p_open_action_num
285
286 ,p_price
287 ,p_price_currency_code
288 ,p_source_type_code
289 ,p_source_comment
290 ,p_source_number
291 ,p_source_date_received
292 ,p_source_organization
293 ,p_source_person
294
295 ,p_attribute_category
296 ,p_attribute1
297
298 ,p_attribute2
299 ,p_attribute3
300 ,p_attribute4
301 ,p_attribute5
302 ,p_attribute6
303 ,p_attribute7
304 ,p_attribute8
305 ,p_attribute9
306 ,p_attribute10
307 ,p_attribute11
308 ,p_attribute12
309 ,p_attribute13
310 ,p_attribute14
311 ,p_attribute15
312
313 -- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
314 ,p_PCO_STATUS_CODE
315 ,p_APPROVAL_TYPE_CODE
316 ,p_LOCKED_FLAG
317 -- end: 26-Jun-2009 cklee Modified for the Bug# 8633676
318
319 ,p_Version_number
320 ,p_Current_Version_flag
321 ,p_Version_Comments
322 ,p_Original_ci_id
323 ,p_Source_ci_id
324 ,px_ci_id
325 ,x_ci_number
326 ,x_return_status
327 ,x_msg_count
328 ,x_msg_data
329
330 );
331 END IF;
332
333 IF l_debug_mode = 'Y' THEN
334 pa_debug.g_err_stage:= 'private method called';
335 pa_debug.write(g_module_name,pa_debug.g_err_stage,
336 l_debug_level6);
337 END IF;
338
339
340 -- IF the number of messages is 1 then fetch the message code from the stack
341 -- and return its text
342 x_msg_count := FND_MSG_PUB.Count_Msg;
343 IF x_msg_count = 1 THEN
344 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
345 ,p_msg_index => 1
346 ,p_data => x_msg_data
347 ,p_msg_index_out => l_msg_index_out
348 );
349 END IF;
350
351 -- Reset the error stack when returning to the calling program
352 PA_DEBUG.Reset_Err_Stack;
353
354
355 EXCEPTION
356 WHEN OTHERS THEN
357 rollback;
358 -- Set the excetption Message and the stack
359 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PUB.ADD_CONTROL_ITEM'
360 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
361
362 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
363 RAISE;
364
365 end ADD_CONTROL_ITEM;
366
367 procedure UPDATE_CONTROL_ITEM (
368 p_api_version IN NUMBER := 1.0
369 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
370 ,p_commit IN VARCHAR2 := FND_API.g_false
371 ,p_validate_only IN VARCHAR2 := FND_API.g_true
372 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
373 ,p_ci_id IN NUMBER
374 ,p_ci_type_id IN NUMBER
375 ,p_summary IN VARCHAR2
376 ,p_status_code IN VARCHAR2 := NULL
377
378 ,p_owner_id IN NUMBER := NULL
379 ,p_owner_name IN VARCHAR2 := NULL
380 ,p_highlighted_flag IN VARCHAR2 := 'N'
381 ,p_progress_status_code IN VARCHAR2
382 ,p_progress_as_of_date IN DATE
383 ,p_classification_code IN NUMBER
384 ,p_reason_code IN NUMBER
385 ,p_record_version_number IN NUMBER
386 ,p_project_id IN NUMBER
387 ,p_object_type IN VARCHAR2 := NULL
388 ,p_object_id IN NUMBER := NULL
389 ,p_object_name IN VARCHAR2 := NULL
390 ,p_ci_number IN VARCHAR2 := NULL
391 ,p_date_required IN DATE := NULL
392 ,p_date_closed IN DATE := NULL
393 ,p_closed_by_id IN NUMBER := NULL
394
395 ,p_description IN VARCHAR2 := NULL
396 ,p_status_overview IN VARCHAR2 := NULL
397 ,p_resolution IN VARCHAR2 := NULL
398 ,p_resolution_code IN NUMBER := NULL
399 ,p_priority_code IN VARCHAR2 := NULL
400 ,p_effort_level_code IN VARCHAR2 := NULL
401 ,p_open_action_num IN NUMBER := NULL
402 ,p_price IN NUMBER := NULL
403 ,p_price_currency_code IN VARCHAR2 := NULL
404 ,p_source_type_code IN VARCHAR2 := NULL
405 ,p_source_comment IN VARCHAR2 := NULL
406 ,p_source_number IN VARCHAR2 := NULL
407 ,p_source_date_received IN DATE := NULL
408 ,p_source_organization IN VARCHAR2 := NULL
409 ,p_source_person IN VARCHAR2 := NULL
410
411 ,p_attribute_category IN VARCHAR2 := NULL
412
413 ,p_attribute1 IN VARCHAR2 := NULL
414 ,p_attribute2 IN VARCHAR2 := NULL
415 ,p_attribute3 IN VARCHAR2 := NULL
416 ,p_attribute4 IN VARCHAR2 := NULL
417 ,p_attribute5 IN VARCHAR2 := NULL
418 ,p_attribute6 IN VARCHAR2 := NULL
419 ,p_attribute7 IN VARCHAR2 := NULL
420 ,p_attribute8 IN VARCHAR2 := NULL
421 ,p_attribute9 IN VARCHAR2 := NULL
422 ,p_attribute10 IN VARCHAR2 := NULL
423 ,p_attribute11 IN VARCHAR2 := NULL
424 ,p_attribute12 IN VARCHAR2 := NULL
425 ,p_attribute13 IN VARCHAR2 := NULL
426 ,p_attribute14 IN VARCHAR2 := NULL
427 ,p_attribute15 IN VARCHAR2 := NULL
428
429 -- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
430 ,p_PCO_STATUS_CODE IN VARCHAR2 := NULL
431 ,p_APPROVAL_TYPE_CODE IN VARCHAR2 := NULL
432 ,p_LOCKED_FLAG IN VARCHAR2 := 'N'
433 -- end: 26-Jun-2009 cklee Modified for the Bug# 8633676
434
435 ,p_Version_number IN number := null
436 ,p_Current_Version_flag IN varchar2 := 'Y'
437 ,p_Version_Comments IN varchar2 := NULL
438 ,p_Original_ci_id IN number := NULL
439 ,p_Source_ci_id IN number := NULL
440 ,p_change_approver IN varchar2 := NULL
441 ,x_return_status OUT NOCOPY VARCHAR2
442 ,x_msg_count OUT NOCOPY NUMBER
443 ,x_msg_data OUT NOCOPY VARCHAR2
444 ) is
445
446 l_msg_index_out NUMBER;
447 -- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
448 l_last_modified_by_id NUMBER := NVL(PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ), fnd_global.user_id);
449 -- end: 26-Jun-2009 cklee Modified for the Bug# 8633676
450 l_owner_id NUMBER;
451 l_object_id NUMBER;
452 l_chgowner_allowed VARCHAR2(1); /* Bug3297238 */
453 l_curr_owner_id NUMBER;
454 l_to_owner_allowed VARCHAR2(1); /* Bug#4050242 */
455 l_classification_code NUMBER; /* Bug 4049588.*/
456 l_reason_code NUMBER; /* Bug 4049588.*/
457
458 cursor c_curr_owner is
459 select owner_id from pa_control_items
460 where ci_id = p_ci_id;
461 begin
462
463 -- Initialize the Error Stack
464 PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PUB.UPDATE_CONTROL_ITEM');
465
466 -- Initialize the return status to success
467 x_return_status := FND_API.G_RET_STS_SUCCESS;
468
469 --Clear the global PL/SQL message table
470 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
471 FND_MSG_PUB.initialize;
472 END IF;
473
474 --get hz_parties.party_id of the logged in user
475 -- l_last_modified_by_id := nvl(PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ), fnd_global.user_id);
476 --26-Jun-2009 cklee Modified for the Bug# 8633676
477 l_owner_id := p_owner_id;
478 l_object_id := p_object_id;
479
480 -- check mandatory owner_id
481 IF (l_owner_id IS NULL) THEN
482 IF (p_owner_name is not null) then
483 getPartyIdFromName(
484 p_project_id => p_project_id
485 ,p_name => p_owner_name
486 ,x_party_id => l_owner_id
487 ,x_return_status => x_return_status
488 ,x_msg_count => x_msg_count
489 ,x_msg_data => x_msg_data);
490 ELSE
491 PA_UTILS.Add_Message( p_app_short_name => 'PA'
492 ,p_msg_name => 'PA_CI_NO_OWNER');
493 x_return_status := 'E';
494 END IF;
495 END IF;
496
497 --Bug 4049588. Check if Classification Code and Reason Code are null.
498 l_classification_code := p_classification_code ;
499 l_reason_code := p_reason_code;
500 IF (l_classification_code IS NULL OR l_reason_code IS NULL )
501 THEN
502 IF (l_classification_code IS NULL) THEN
503 PA_UTILS.Add_Message( p_app_short_name => 'PA'
504 ,p_msg_name => 'PA_CI_NO_CLASSIFICATION_CODE');
505 x_return_status := 'E';
506 END IF;
507 IF (l_reason_code IS NULL) THEN
508 PA_UTILS.Add_Message( p_app_short_name => 'PA'
509 ,p_msg_name => 'PA_CI_NO_REASON_CODE');
510 x_return_status := 'E';
511 END IF;
512 END IF;
513
514 /* Code added for Bug#3297238, starts here */
515 open c_curr_owner;
516 fetch c_curr_owner into l_curr_owner_id;
517 close c_curr_owner;
518
519 if (l_owner_id <> l_curr_owner_id) then
520 l_chgowner_allowed := pa_ci_security_pkg.check_change_owner_access(p_ci_id);
521 if (l_chgowner_allowed <> 'T') then
522 PA_UTILS.Add_Message( p_app_short_name => 'PA'
523 ,p_msg_name => 'PA_CI_OWNER_CHG_NOT_ALLOWED');
524 x_return_status := 'E';
525 /* Code added for Bug#4050242, starts here */
526 else
527 l_to_owner_allowed := pa_ci_security_pkg.is_to_owner_allowed(p_ci_id, l_owner_id);
528 if (l_to_owner_allowed <> 'T') then
529 PA_UTILS.Add_Message( p_app_short_name => 'PA'
530 ,p_msg_name => 'PA_CI_TO_OWNER_NOT_ALLOWED');
531 x_return_status := 'E';
532 end if;
533 /* Code added for Bug#4050242, ends here */
534 end if;
535
536 -- Bug 3650877: Commneted this check for the owner id.
537
538 --elsif (l_owner_id = l_curr_owner_id) then
539 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
540 -- ,p_msg_name => 'PA_CI_CHANGE_OWNER_INVALID');
541 -- x_return_status := 'E';
542 end if;
543 /* Code added for Bug#3297238, ends here */
544
545 IF (l_object_id IS NULL AND p_object_name is not null) THEN
546 -- try to get object id from name - as of now we're only handling PA_TASKS objects
547 begin
548 select proj_element_id
549 into l_object_id
550 from PA_FIN_LATEST_PUB_TASKS_V
551 where element_name = p_object_name
552 and project_id = p_project_id;
553
554 exception when TOO_MANY_ROWS then
555 PA_UTILS.Add_Message( p_app_short_name => 'PA'
556 ,p_msg_name => 'PA_OBJECT_NAME_MULTIPLE');
557 x_return_status := 'E';
558
559 when OTHERS then
560 PA_UTILS.Add_Message( p_app_short_name => 'PA'
561 ,p_msg_name => 'PA_OBJECT_NAME_INV');
562 x_return_status := 'E';
563 end;
564
565 END IF;
566
567
568 IF (x_return_status <> 'E')THEN
569 PA_CONTROL_ITEMS_PVT.UPDATE_CONTROL_ITEM(
570 p_api_version
571 ,p_init_msg_list
572 ,p_commit
573 ,p_validate_only
574 ,p_max_msg_count
575
576 ,p_ci_id
577 ,p_ci_type_id
578 ,p_summary
579 ,p_status_code
580 ,l_owner_id
581 ,nvl(p_highlighted_flag,'N')
582 ,p_progress_status_code
583 ,p_progress_as_of_date
584 ,p_classification_code
585 ,p_reason_code
586 ,p_record_version_number
587 ,p_project_id
588 ,l_last_modified_by_id
589 ,p_object_type
590 ,l_object_id
591 ,p_ci_number
592 ,p_date_required
593 ,p_date_closed
594 ,p_closed_by_id
595
596 ,p_description
597 ,p_status_overview
598 ,p_resolution
599 ,p_resolution_code
600 ,p_priority_code
601 ,p_effort_level_code
602 ,p_open_action_num
603 ,p_price
604 ,p_price_currency_code
605 ,p_source_type_code
606 ,p_source_comment
607 ,p_source_number
608 ,p_source_date_received
609 ,p_source_organization
610 ,p_source_person
611
612 ,p_attribute_category
613
614 ,p_attribute1
615 ,p_attribute2
616 ,p_attribute3
617 ,p_attribute4
618 ,p_attribute5
619 ,p_attribute6
620 ,p_attribute7
621 ,p_attribute8
622 ,p_attribute9
623 ,p_attribute10
624 ,p_attribute11
625 ,p_attribute12
626 ,p_attribute13
627 ,p_attribute14
628 ,p_attribute15
629
630 -- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
631 ,p_PCO_STATUS_CODE
632 ,p_APPROVAL_TYPE_CODE
633 ,p_LOCKED_FLAG
634 -- end: 26-Jun-2009 cklee Modified for the Bug# 8633676
635
636 ,p_Version_number
637 ,p_Current_Version_flag
638 ,p_Version_Comments
639 ,p_Original_ci_id
640 ,p_Source_ci_id
641 ,p_change_approver
642 ,x_return_status
643 ,x_msg_count
644 ,x_msg_data
645 );
646 END IF;
647
648 -- IF the number of messaages is 1 then fetch the message code from the stack
649 -- and return its text
650 x_msg_count := FND_MSG_PUB.Count_Msg;
651 IF x_msg_count = 1 THEN
652 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
653 ,p_msg_index => 1
654 ,p_data => x_msg_data
655 ,p_msg_index_out => l_msg_index_out
656 );
657 END IF;
658
659 -- Reset the error stack when returning to the calling program
660 PA_DEBUG.Reset_Err_Stack;
661
662
663 EXCEPTION
664 WHEN OTHERS THEN
665 rollback;
666 -- Set the excetption Message and the stack
667 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PUB.UPDATE_CONTROL_ITEM'
668 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
669
670 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
671 RAISE;
672 end UPDATE_CONTROL_ITEM;
673
674
675
676 procedure DELETE_CONTROL_ITEM (
677 p_api_version IN NUMBER := 1.0
678 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
679 ,p_commit IN VARCHAR2 := FND_API.g_false
680 ,p_validate_only IN VARCHAR2 := FND_API.g_true
681 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
682
683 ,p_ci_id IN NUMBER
684 ,p_record_version_number IN NUMBER
685 ,x_return_status OUT NOCOPY VARCHAR2
686 ,x_msg_count OUT NOCOPY NUMBER
687 ,x_msg_data OUT NOCOPY VARCHAR2
688
689 ) is
690
691 l_msg_index_out NUMBER;
692 -- l_status_code pa_control_items.status_code%type;
693
694 -- cursor valid_ci is
695 -- select status_code
696 -- from pa_control_items
697 -- where ci_id = p_ci_id;
698
699 begin
700
701 -- Initialize the Error Stack
702 PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PUB.DELETE_CONTROL_ITEM');
703
704 -- Initialize the return status to success
705 x_return_status := FND_API.G_RET_STS_SUCCESS;
706
707 --Clear the global PL/SQL message table
708 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
709 FND_MSG_PUB.initialize;
710 END IF;
711 /*
712 open valid_ci;
713 fetch valid_ci into l_status_code;
714
715 if (valid_ci%notfound) then
716 --- invalid ci_id error
717 PA_UTILS.Add_Message( p_app_short_name => 'PA'
718 ,p_msg_name => 'PA_CI_INVALID_ITEM');
719 x_return_status := FND_API.G_RET_STS_ERROR;
720 else
721 if (l_status_code <> 'CI_DRAFT') then
722 --- invalid status error
723 PA_UTILS.Add_Message( p_app_short_name => 'PA'
724 ,p_msg_name => 'PA_CI_ONLY_DRAFT_DEL');
725 x_return_status := FND_API.G_RET_STS_ERROR;
726 end if;
727 end if;
728 close valid_ci;
729
730
731 if (x_return_status = 'S') then */
732
733 PA_CONTROL_ITEMS_PVT.DELETE_CONTROL_ITEM(
734 p_api_version
735 ,p_init_msg_list
736 ,p_commit
737 ,p_validate_only
738 ,p_max_msg_count
739
740 ,p_ci_id
741 ,p_record_version_number
742 ,x_return_status
743 ,x_msg_count
744 ,x_msg_data
745 );
746 -- end if;
747
748
749 -- IF the number of messaages is 1 then fetch the message code from the stack
750 -- and return its text
751 x_msg_count := FND_MSG_PUB.Count_Msg;
752 IF x_msg_count = 1 THEN
753 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
754 ,p_msg_index => 1
755 ,p_data => x_msg_data
756 ,p_msg_index_out => l_msg_index_out
757 );
758 END IF;
759
760 -- Reset the error stack when returning to the calling program
761 PA_DEBUG.Reset_Err_Stack;
762
763
764 EXCEPTION
765 WHEN OTHERS THEN
766 rollback;
767 -- Set the excetption Message and the stack
768 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PUB.DELETE_CONTROL_ITEM'
769 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
770
771
772 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
773 RAISE;
774 end DELETE_CONTROL_ITEM;
775
776 procedure COPY_CONTROL_ITEM (
777 p_api_version IN NUMBER := 1.0
778 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
779 ,p_commit IN VARCHAR2 := FND_API.g_false
780 ,p_validate_only IN VARCHAR2 := FND_API.g_true
781 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
782
783 ,p_project_id IN NUMBER
784 ,p_ci_id_from IN NUMBER -- copy from this
785 ,p_ci_type_id IN NUMBER -- copy to this
786 ,p_classification_code_id IN NUMBER
787 ,p_reason_code_id IN NUMBER
788 ,p_include IN VARCHAR2 := 'N'
789 ,p_record_version_number_from IN NUMBER
790 ,x_ci_id OUT NOCOPY NUMBER
791 ,p_ci_number IN OUT NOCOPY VARCHAR2
792 ,x_return_status OUT NOCOPY VARCHAR2
793 ,x_msg_count OUT NOCOPY NUMBER
794 ,x_msg_data OUT NOCOPY VARCHAR2
795
796 ) is
797
798 l_msg_index_out NUMBER;
799
800 begin
801
802 -- Initialize the Error Stack
803 PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PUB.COPY_CONTROL_ITEM');
804
805 -- Initialize the return status to success
806 x_return_status := FND_API.G_RET_STS_SUCCESS;
807
808 --Clear the global PL/SQL message table
809 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
810 FND_MSG_PUB.initialize;
811 END IF;
812
813 pa_control_items_pvt.COPY_CONTROL_ITEM (
814 p_commit => p_commit
815 ,p_validate_only => p_validate_only
816
817 ,p_project_id => p_project_id
818 ,p_ci_id_from => p_ci_id_from -- copy from this
819 ,p_ci_type_id => p_ci_type_id -- copy to this
820 ,p_classification_code_id => p_classification_code_id
821 ,p_reason_code_id => p_reason_code_id
822 ,p_include => p_include
823 ,p_record_version_number_from => p_record_version_number_from
824 ,x_ci_id => x_ci_id
825 ,p_ci_number => p_ci_number
826 ,x_return_status => x_return_status
827 ,x_msg_count => x_msg_count
828 ,x_msg_data => x_msg_data);
829
830 -- IF the number of messages is 1 then fetch the message code from the stack
831 -- and return its text
832 x_msg_count := FND_MSG_PUB.Count_Msg;
833 IF x_msg_count = 1 THEN
834 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
835 ,p_msg_index => 1
836 ,p_data => x_msg_data
837 ,p_msg_index_out => l_msg_index_out
838 );
839 end if;
840
841 if (p_commit = 'T' and x_return_status = 'S') then
842 commit;
843 end if;
844
845 -- Reset the error stack when returning to the calling program
846 PA_DEBUG.Reset_Err_Stack;
847
848
849 EXCEPTION
850 WHEN OTHERS THEN
851 rollback;
852 -- Set the excetption Message and the stack
853 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PUB.COPY_CONTROL_ITEM'
854 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
855
856
857 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
858 RAISE;
859 end COPY_CONTROL_ITEM;
860
861 procedure INCLUDE_CONTROL_ITEM(
862 p_api_version IN NUMBER := 1.0
863 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
864 ,p_commit IN VARCHAR2 := FND_API.g_false
865 ,p_validate_only IN VARCHAR2 := FND_API.g_true
866 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
867
868 ,p_from_ci_id IN NUMBER
869 ,p_to_ci_id IN NUMBER
870 ,p_record_version_number_to IN NUMBER
871 ,x_relationship_id OUT NOCOPY NUMBER
872 ,x_return_status OUT NOCOPY VARCHAR2
873 ,x_msg_count OUT NOCOPY NUMBER
874 ,x_msg_data OUT NOCOPY VARCHAR2
875 ) is
876
877 l_relationship_id NUMBER;
878 l_msg_index_out NUMBER;
879
880 begin
881
882 -- Initialize the Error Stack
883 PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PUB.INCLUDE_CONTROL_ITEM');
884
885 -- Initialize the return status to success
886 x_return_status := FND_API.G_RET_STS_SUCCESS;
887
888 --Clear the global PL/SQL message table
889 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
890 FND_MSG_PUB.initialize;
891 END IF;
892
893 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
894 PA_CONTROL_ITEMS_PVT.INCLUDE_CONTROL_ITEM(
895 p_api_version => p_api_version
896 ,p_init_msg_list => p_init_msg_list
897 ,p_commit => p_commit
898 ,p_validate_only => p_validate_only
899 ,p_max_msg_count => p_max_msg_count
900 ,p_from_ci_id => p_from_ci_id
901 ,p_to_ci_id => p_to_ci_id
902 ,p_record_version_number_to => p_record_version_number_to
903 ,x_relationship_id => l_relationship_id
904 ,x_return_status => x_return_status
905 ,x_msg_count => x_msg_count
906 ,x_msg_data => x_msg_data);
907 end if;
908
909 -- IF the number of messaages is 1 then fetch the message code from the stack
910 -- and return its text
911 x_msg_count := FND_MSG_PUB.Count_Msg;
912 IF x_msg_count = 1 THEN
913 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
914 ,p_msg_index => 1
915 ,p_data => x_msg_data
916 ,p_msg_index_out => l_msg_index_out
917 );
918 END IF;
919
920 if (p_commit = 'T' and x_return_status = 'S') then
921 commit;
922 end if;
923
924 -- Reset the error stack when returning to the calling program
925 PA_DEBUG.Reset_Err_Stack;
926
927
928 EXCEPTION
929 WHEN OTHERS THEN
930 rollback;
931 -- Set the excetption Message and the stack
932 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PUB.INCLUDE_CONTROL_ITEM'
933 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
934
935
936 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
937 RAISE;
938 end INCLUDE_CONTROL_ITEM;
939
940
941 function GET_OBJECT_NAME(p_object_id IN NUMBER
942 ,p_object_type IN VARCHAR2
943 ) return VARCHAR2 is
944
945 begin
946 null;
947 end GET_OBJECT_NAME;
948
949 -- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
950 procedure LOCK_CONTROL_ITEM (
951 p_api_version IN NUMBER := 1.0
952 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
953 ,p_commit IN VARCHAR2 := FND_API.g_false
954 ,p_validate_only IN VARCHAR2 := FND_API.g_true
955 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
956 ,p_ci_id IN NUMBER
957 ,x_return_status OUT NOCOPY VARCHAR2
958 ,x_msg_count OUT NOCOPY NUMBER
959 ,x_msg_data OUT NOCOPY VARCHAR2
960 ) is
961
962 l_msg_index_out NUMBER;
963
964 begin
965
966 -- Initialize the Error Stack
967 PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PUB.LOCK_CONTROL_ITEM');
968
969 -- Initialize the return status to success
970 x_return_status := FND_API.G_RET_STS_SUCCESS;
971
972 --Clear the global PL/SQL message table
973 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
974 FND_MSG_PUB.initialize;
975 END IF;
976
977 -- Issue API savepoint if the transaction is to be committed
978 IF p_commit = FND_API.G_TRUE THEN
979 SAVEPOINT lock_control_item;
980 END IF;
981
982
983 PA_CONTROL_ITEMS_PVT.LOCK_CONTROL_ITEM(
984 p_api_version
985 ,p_init_msg_list
986 ,p_commit
987 ,p_validate_only
988 ,p_max_msg_count
989 ,p_ci_id
990 ,x_return_status
991 ,x_msg_count
992 ,x_msg_data
993 );
994
995 -- IF the number of messaages is 1 then fetch the message code from the stack
996 -- and return its text
997 x_msg_count := FND_MSG_PUB.Count_Msg;
998 IF x_msg_count = 1 THEN
999 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
1000 ,p_msg_index => 1
1001 ,p_data => x_msg_data
1002 ,p_msg_index_out => l_msg_index_out
1003 );
1004 END IF;
1005
1006 -- Reset the error stack when returning to the calling program
1007 PA_DEBUG.Reset_Err_Stack;
1008
1009
1010
1011 EXCEPTION
1012 WHEN OTHERS THEN
1013 rollback to lock_control_item;
1014 -- Set the excetption Message and the stack
1015 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PUB.LOCK_CONTROL_ITEM'
1016 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1017
1018
1019 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1020 RAISE;
1021 end LOCK_CONTROL_ITEM;
1022 -- end: 26-Jun-2009 cklee Modified for the Bug# 8633676
1023
1024
1025 END PA_CONTROL_ITEMS_PUB;